Post reply

TSQL Error using Cursor to loop through sysdatabases (Syntax Error)

  • November 5, 2007 at 4:36 pm

    #199629

    I am trying to loop through sysdatabases to get all databases that meet a naming convention and then for each one found run a select statement on another table in that database.

    I am getting a syntax error on line 24: Incorrect syntax near @dataname. MSG 170 Level 15, state 1, line 24

    Thanks for any help in advance.

    {START SCRIPT}****************************************************************

    SET QUOTED_IDENTIFIER ON

    DECLARE @fillfactor varchar(2)

    DECLARE @tablename varchar(30)

    DECLARE @tablename_header varchar(75)

    DECLARE @dataname varchar(255)

    DECLARE @dataname_header varchar(75)

    DECLARE datanames_cursor CURSOR FOR SELECT name FROM dbo.sysdatabases WHERE name like '%somefilter%'

    Open datanames_cursor

    FETCH NEXT FROM datanames_cursor INTO @dataname

    WHILE (@@fetch_status <> -1)

    BEGIN

    IF (@@fetch_status = -2)

    FETCH NEXT FROM datanames_cursor INTO @dataname

    BEGIN

    --PRINT @dataname

    PRINT @dataname

    SET @dataname = '"'+@dataname+'"'

    PRINT @dataname

    --USE @dataname

    --SELECT UserInfo.tp_Login

    --FROM Sites INNER JOIN

    --UserInfo ON Sites.OwnerID = UserInfo.tp_ID

    FETCH NEXT FROM datanames_cursor INTO @dataname

    END

    END

    DEALLOCATE datanames_cursor

  • November 5, 2007 at 8:14 pm

    #748479

    Although nothing is returned, your script generates no syntax (or any) error.

  • November 5, 2007 at 9:57 pm

    #748497

    Line 24 is the USE statement that you currently have commented out... the operand of the USE statement cannot be a variable. You will need to use dynamic SQL.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 5, 2007 at 10:06 pm

    #748499

    I am not sure how to use dynamic SQL.

    But I found a reference to using CURSOR that does not take an argument as input. So please tell me if there is a different way to do what I need to do.

    Basically I want to use the output of dbo.sysdatabases to run a select statement in each database calling a specific table.

    I cheated by creating a stored procedure in each DB and then using an INSERT to a temp table for the output of each SP and then adding that as the report. But I have to add the SP to each DB and there are hundreds of DB's. It would be much easier if I could just use each DB Name in sysdatabases as the argument.

    Thanks for your help in advance.

  • November 5, 2007 at 10:15 pm

    #748500

    ...and, if you have less that 250 or so databases, the following will beat the pants off your cursor 😉

    [font="Courier New"]--===== Declare local variables

    DECLARE @sql VARCHAR(8000) --Holds the dynamic SQL

    --===== Build the dynamic SQL

    SELECT @sql = ISNULL(@SQL+' UNION ALL'+CHAR(10),'')

    + 'SELECT u.tp_Login,''' + Name + ''' FROM [' + Name + ']..Sites s INNER JOIN ['

    + Name + ']..UserInfo u ON s.OwnerID=u.tp_ID'

    FROM Master.dbo.SysDatabases

    WHERE Name LIKE '%somefilter%'

    --===== Execute the dynamic SQL to get the result set

    EXEC (@SQL)[/font]

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 5, 2007 at 10:15 pm

    #748501

    search this forum on sp_MSforeachdb

  • November 5, 2007 at 11:20 pm

    #748513

    Koji Matsumura (11/5/2007)

    search this forum on sp_MSforeachdb

    Gosh... I wouldn't use that on a bet... it's got a cursor with some steroid rage going on. I suppose it's ok for the casual "what if", but I sure wouldn't use it for production code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 6, 2007 at 8:14 am

    #748691

    I forgot to mention that once I have the code running I will be using SSRS to allow users to pull updated reports. So Cursor's will not work either embedded in SP_FOREACHDB or in another location. I am going to research the dynamic SQL Option. Is there a good reference material?

  • November 7, 2007 at 8:56 pm

    #749331

    Thanks for all the suggestions. Turns out for this application i just had a typo error.

    EXEC ('USE ' + @DataName +

    'INSERT INTO #TMP1 SELECT ' + @DataName + '.dbo.Sites.FullUrl,'

    + @DataName + '.dbo.UserInfo.tp_Login

    FROM ' + @DataName + '.dbo.Sites INNER JOIN

    UserInfo ON ' + @DataName + '.dbo.Sites.OwnerID = '

    + @DataName + '.dbo.UserInfo.tp_ID

    ')

    So putting in single quotes with the use statemnt fixed my problem. Just in case anyone wants to know.

  • November 7, 2007 at 9:00 pm

    #749332

    Thanks for the feedback, John...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply