Hi All,

I have a code that suppose insert records into temp table.

Here is the code:

DECLARE @TSQL NVARCHAR(MAX);
DECLARE @ins nvarchar(max);

create table ##userstable
(dbname varchar(130),
username varchar(130))

-- What we'll return back and the first table we'll use, syslogins
SET @TSQL = 'SELECT Database_Name, d.name
COLLATE DATABASE_DEFAULT as ''user''
FROM sys.server_principals s
INNER JOIN ('

-- Building the rest of the T-SQL to run a single query

SELECT @TSQL = @TSQL + 'SELECT ''' + name + ''' AS Database_Name,
sid, name FROM [' + name + '].sys.database_principals UNION ALL '
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

-- Remove the final UNION ALL and completing the join
SET @TSQL = LEFT(@TSQL, LEN(@TSQL) - 10) + ') d ON s.sid = d.sid;'


-- Returning the query
EXECUTE sp_executesql @TSQL


set @ins = 'insert into ##userstable (dbname,username) values ( ' + @tsql + ') '
execute sp_executesql @ins

select * from ##userstable
drop table ##userstable

When I run I get the following error messages:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 66
Incorrect syntax near ')'.

It doesn't make sense because there is no select statement on line 1 and there are only 33 lines of code.

Any idea?