Results 1 to 3 of 3

Thread: Dynamic SQL

  1. #1
    Join Date
    Mar 2006
    Posts
    127

    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?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Print out @tsql and @ins before exec, double check those 2 statements.

  3. #3
    Join Date
    Sep 2011
    Posts
    1
    Hi.
    Do you solve this problem?

    To solve this you have to change the following code:

    Quote Originally Posted by inet View Post

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


    Hope this helps .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •