Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » TSQL Error using Cursor to loop through...
Add to briefcase

TSQL Error using Cursor to loop through sysdatabases (Syntax Error) Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
john monroe
Posted Monday, November 05, 2007 4:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 10, 2008 10:26 PM
Points: 10, Visits: 16
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
Post #418809
Koji Matsumura
Posted Monday, November 05, 2007 8:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, March 30, 2008 9:53 PM
Points: 311, Visits: 1,918
Although nothing is returned, your script generates no syntax (or any) error.
Post #418841
Jeff Moden
Posted Monday, November 05, 2007 9:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 32,905, Visits: 26,787
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #418861
john monroe
Posted Monday, November 05, 2007 10:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 10, 2008 10:26 PM
Points: 10, Visits: 16
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.
Post #418863
Jeff Moden
Posted Monday, November 05, 2007 10:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 32,905, Visits: 26,787
...and, if you have less that 250 or so databases, the following will beat the pants off your cursor ;)

--===== 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)

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #418864
Koji Matsumura
Posted Monday, November 05, 2007 10:15 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, March 30, 2008 9:53 PM
Points: 311, Visits: 1,918
search this forum on sp_MSforeachdb
Post #418865
Jeff Moden
Posted Monday, November 05, 2007 11:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 32,905, Visits: 26,787
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #418880
john monroe
Posted Tuesday, November 06, 2007 8:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 10, 2008 10:26 PM
Points: 10, Visits: 16
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?
Post #419094
john monroe
Posted Wednesday, November 07, 2007 8:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 10, 2008 10:26 PM
Points: 10, Visits: 16
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.
Post #419896
Jeff Moden
Posted Wednesday, November 07, 2007 9:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 32,905, Visits: 26,787
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #419897
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.