Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
darius_sutherland
Starting Member
17 Posts |
 Posted - 2008-09-12 : 07:09:12
|
| Hi.I don't write SP's that often and Im getting some syntaxing problems on the below. I think you can see what Im trying to do but this method is not correctSET @strSQL = 'SELECT name from' +@database + '.dbo.sysobjects WHERE xtype = U'DECLARE SOURCE CURSOR LOCAL FAST_FORWARD FOR @strSQL Thanx in advanceDwww.state-of-mind.co.uk | www.photographersinbrighton.org.uk | www.sussexweddingphotographers.org.uk |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-09-12 : 08:25:36
|
| Looks like 2 problems. you need a <space> between "from" and @database. But the bigger problem is sql expects a SELECT statement not a string variable for the cursor definition. You need to do away with the cursor or include it as part of the dynamic statement.Be One with the OptimizerTG |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-09-12 : 08:29:35
|
quote:
Originally posted by darius_sutherland
Hi.I don't write SP's that often and Im getting some syntaxing problems on the below. I think you can see what Im trying to do but this method is not correctSET @strSQL = 'SELECT name from' +@database + '.dbo.sysobjects WHERE xtype = U'DECLARE SOURCE CURSOR LOCAL FAST_FORWARD FOR @strSQL Thanx in advanceDState Of Mind Photography - It's This, It's That, It's The Otherwww.state-of-mind.co.uk
What are you trying to do with all user defined tables?MadhivananFailing to plan is Planning to fail |
|
|
|
darius_sutherland
Starting Member
17 Posts |
Posted - 2008-09-15 : 10:02:07
|
| I have a SP that exports all the headers and data of a database out to CSV, problem is it only runs withing the current database. I want to have the SP installed in one DB and just pass the database name, it zips through all the tables and exports them.I think the problem I am having is when I run it, it get the table names of the current database I am in, which is the sysobjects part, I want to get the objects of the database name I pass.Tawww.state-of-mind.co.uk | www.photographersinbrighton.org.uk | www.sussexweddingphotographers.org.uk |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-15 : 12:41:28
|
quote:
Originally posted by darius_sutherland
I have a SP that exports all the headers and data of a database out to CSV, problem is it only runs withing the current database. I want to have the SP installed in one DB and just pass the database name, it zips through all the tables and exports them.I think the problem I am having is when I run it, it get the table names of the current database I am in, which is the sysobjects part, I want to get the objects of the database name I pass.TaState Of Mind Photography - It's This, It's That, It's The Otherwww.state-of-mind.co.uk
not sure why you want to do it via procedure. I would have used a ssis package to achieve the same result. just set a variable for holding db name and then pass dbname as a parameter to ssis package. the package will have a task to get list of tables and save it as a recordset and then a for each loop which loops throu\gh recordset and for each table does export by means of data flow task. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-11-09 : 04:33:44
|
| Do you still have this problem?MAke sure to read this www.sommarskog.sq/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
SP Syntaxing