-
Dynamic SQL
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|