I ran your sql and swapped the
EXECUTE
for a
PRINT
and got this
SELECT DISTINCT [Projektdefinition DB] AS Zugänge FROM [xyz]EXCEPT SELECT DISTINCT [Projektdefinition DB] AS Zugänge FROMxyz
so I think this will fix your problem
SET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM [' + @Table_Name + ']' +
' EXCEPT
SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name
Note the extra spaces before EXCEPT and after FROM on the 3rd and 5th lines respectively
EDIT - from OP comment we now know that this is within an SP called by
EXEC spDynamic1 '[dbo].[Per201102]'
so there are extra square brackets being inserted by the
SET
Try this instead:
SET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name +
' EXCEPT SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name
EDIT2 - in response to OP comment - I think the SP needs to change similar to this (note - untested)
ALTER PROC spDynamic1
(
@Table_Name sysname ,@Table_Name2 sysname
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Dynamictb2 nvarchar(255)
SET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM [' + @Table_Name + ']' +
' EXCEPT
SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name2
EXECUTE sp_executesql @Dynamictb2
END
GO