Syntax Error in Dynamic Query - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Syntax Error in Dynamic Query

Author

Topic

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-13 : 07:31:30

Any idea, where i miss the syntax in this query.

DECLARE @Sql varchar(8000)
SET @Sql = 'EXEC Master..XP_CmdShell ''bcp "SELECT Routine_Definition FROM DBName.Information_Schema.Routines
WHERE Routine_Type = ''PROCEDURE''" queryout "C:\ScriptProcedures.sql" -c'''
EXEC (@Sql)

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PROCEDURE'.

================================================

When you realize you've made a mistake, take immediate steps to correct it.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-13 : 07:38:10

Need to escape the ' around PROCEDURE as it's in another string

This will work


DECLARE @Sql varchar(8000)
SET @Sql = 'EXEC Master..XP_CmdShell ''bcp "SELECT Routine_Definition FROM DBName.Information_Schema.Routines	
WHERE Routine_Type = ''''PROCEDURE''''" queryout "C:\ScriptProcedures.sql" -c'''
EXEC (@Sql)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-13 : 07:39:14

Top tip -- change the EXEC to PRINT. Easy way to find where you go wrong with annoying things like this.

Sometimes though, you always need another pair of eyes!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-13 : 07:45:55

simplify...

DECLARE @cmd varchar(8000)
SET @cmd= 'bcp "SELECT Routine_Definition FROM DBName.Information_Schema.Routines 
WHERE Routine_Type = ''PROCEDURE''" queryout "C:\ScriptProcedures.sql" -c'
EXEC Master..XP_CmdShell @cmd

E 12°55'05.63"
N 56°04'39.26"

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-13 : 08:16:26

Thanks for your all replies, it works but doesn't create a script file.

When i give this it create script for Procedure & Functions

DECLARE @Sql varchar(8000)
SET @Sql = 'EXEC Master..XP_CmdShell ''bcp "SELECT Routine_Definition FROM DBName.Information_Schema.Routines " queryout "C:\ScriptProcedures.sql" -c'''
EXEC (@Sql)

But, I need to create a separate Script for Procedures only.

================================================

When you realize you've made a mistake, take immediate steps to correct it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-13 : 08:23:25

You can't have linebreaks in your @cmd.
try this

DECLARE @cmd varchar(8000)
SET @cmd= 'bcp "SELECT Routine_Definition FROM DBName.Information_Schema.Routines WHERE Routine_Type = ''PROCEDURE''" queryout "C:\ScriptProcedures.sql" -c'

EXEC Master..XP_CmdShell @cmd

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-13 : 08:24:54

quote:
Originally posted by karthickbabu

But, I need to create a separate Script for Procedures only.

See http://weblogs.sqlteam.com/peterl/archive/2008/10/24/How-to-script-out-all-your-objects-one-per-file.aspx

E 12°55'05.63"
N 56°04'39.26"

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-13 : 23:29:11

Thanks. Nice Article

================================================

When you realize you've made a mistake, take immediate steps to correct it.

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-19 : 07:09:41

Hi Peter

http://weblogs.sqlteam.com/peterl/archive/2008/10/24/How-to-script-out-all-your-objects-one-per-file.aspx

I run your script in Query Analyzer, it doesn't generate any scripts and it shows the following output

isProc isFunc isTrig isView
1095 216 22 227

Whether it runs on query analyzer or sqlcmd

====================================================
you realize you've made a mistake, take immediate steps to correct it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 07:20:02

Have you changed the paths in the beginning of code?

SELECT  @pathBase = '\\Archive\Documents\Projects\Peso\Code\',
        @pathProc = @pathBase + 'Stored Procedures\',
        @pathFunc = @pathBase + 'Functions\',
        @pathTrig = @pathBase + 'Triggers\',
        @pathView = @pathBase + 'Views\'

E 12°55'05.63"
N 56°04'39.26"

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-19 : 07:36:03

yes, I changed like as below

SELECT @pathBase = '\\MyServerName\Sharable\Code\',
@pathProc = @pathBase + 'Stored Procedures\',
@pathFunc = @pathBase + 'Functions\',
@pathTrig = @pathBase + 'Triggers\',
@pathView = @pathBase + 'Views\'

====================================================
you realize you've made a mistake, take immediate steps to correct it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 07:38:09

And the files are not saved at that UNC path?
"\\MyServerName\Sharable\Code\"

Do you have approriate security rights/permissions to that folder?

E 12°55'05.63"
N 56°04'39.26"

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-19 : 08:16:54

Yes, I tried in my machine. I have SQL SERVER 2000 on my machine.

What you mean UNC Path?

====================================================
you realize you've made a mistake, take immediate steps to correct it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 08:33:59

The variable @pathBase holds information about the network path (UNC path) where to save the files.

E 12°55'05.63"
N 56°04'39.26"

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-20 : 04:23:44

Ya ok, But i didnt get the script file. I tried in my machine also.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-21 : 04:18:09

Hi Peso, I am waiting for your response.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 04:25:30

There is no response.
The code works for me, all my coworkers and other people I have talked to about the stored procedure.

As of now, you are the only one I know of that is'nt properly using the code.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 04:27:03

Have you checked the UNC path \\MyServerName\Sharable\Code\ for the existince of present files?
Did you check for errors when running my code, such as permission denied errors (code 5)?

SELECT @pathBase = '\\MyServerName\Sharable\Code\',
@pathProc = @pathBase + 'Stored Procedures\',
@pathFunc = @pathBase + 'Functions\',
@pathTrig = @pathBase + 'Triggers\',
@pathView = @pathBase + 'Views\'

E 12°55'05.63"
N 56°04'39.26"

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-21 : 04:43:36

Just i Copy and Run your code without any changes, its doesn't shows any error.

Same Output whatever i already posted?

isProc isFunc isTrig isView
1095 216 22 227

====================================================
you realize you've made a mistake, take immediate steps to correct it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 05:31:27

You get NO errors for the BCP part?
And the UNC path is created and you have write access to the paths (with sub-paths)?

E 12°55'05.63"
N 56°04'39.26"

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-22 : 01:43:34

Yes, without any errors on BCP part. I give full access permission for that folder. Still doesn't generate any script in that folder.

I dont know, why it occurs.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources