Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

xp_cmdshell execute scripts that are on c: Expand / Collapse
Author
Message
Posted Thursday, May 27, 2010 2:21 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:46 AM
Points: 743, Visits: 1,055
Hi,

I need to use xp_cmdshell to execute a stored procedure that is my c:
The name of the stored procedure is Script22.sql

I have seen the following example on the internet:

exec master.dbo.xp_cmdshell 'osql -E -Sserver1 -i c:\tempightly.sql'


But i think that in SQL Server 2005 the OSQL was replace by CMDSQL, am i correct?
How can i alter this command so that it runs the script in cmdSQL and if the script raises any error, i can catch it?

Thank you
Post #928800
Posted Wednesday, July 28, 2010 12:48 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 2:29 PM
Points: 507, Visits: 597
Not sure about catching errors, but as far as running it under SQL 2005 (and 2008), you can substitute "sqlcmd" for "osql". Of course "osql" still works for now (it is deprecated), it is "isql" that was removed.


Have Fun!

Ronzo

Post #960311
Posted Thursday, July 29, 2010 9:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 10:03 AM
Points: 1,106, Visits: 1,334
Havent tried it but would assume that you would pipe to an output file. something like:

sqlcmd -S . -d master -Q "select name from sys.databases" -o "c:\output.csv"

But obviously inside a XP_cmd... statement.

Hope this helps.


Adam Zacks

-------------------------------------------

Be Nice, Or Leave
Post #960782
Posted Thursday, July 29, 2010 9:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 10:03 AM
Points: 1,106, Visits: 1,334
Schadenfreude-Mei (7/29/2010)
Havent tried it but would assume that you would pipe to an output file. something like:

sqlcmd -S . -d master -Q "select name from sys.databases" -o "c:\output.csv"

But obviously inside a XP_cmd... statement.

Hope this helps.


SO in your case:

EXEC xp_cmdshell 'sqlcmd -S . -d master -i "c:\sqlscript.sql" -o "c:\output.csv"'

Or something like that. Simples (squeeky meercat noise!)


Adam Zacks

-------------------------------------------

Be Nice, Or Leave
Post #960792
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse