Results 1 to 4 of 4

Thread: Dynamic SQL

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: 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
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    line 1 is presumably within the "exec" statement. Instead of trying to execute the SQL in @TSQL, print it out with either select, or print, and see if you can execute the result.

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    I have added print @tsql before execute sp_executesql @tsql command and commented out

    Execute sp_executesql @tsql

    Here is the result:

    SELECT Database_Name, d.name
    COLLATE DATABASE_DEFAULT as 'user'
    FROM sys.server_principals s
    INNER JOIN (SELECT 'db' AS Database_Name,
    sid, name FROM [db].sys.database_principals UNION ALL SELECT 'anotherdb' AS Database_Name, ...

    etc

    Is that the correct results?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I have no idea if these are the correct results. That is for you to determine.

    Does the query that is printed run and give you the expected results?

Posting Permissions

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