Using sqlcmd to call an sp from a batch

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 2008 Forums

Transact-SQL (2008)

Using sqlcmd to call an sp from a batch

Author

Topic

tscribner
Starting Member

4 Posts

Posted - 2010-11-03 : 13:21:53

Hi all,

Thanks in advance....

I am trying to call a stored procedure from a batch file (vbs). The thing that is hanging me up, I think is that there is an output variable that has to be used. Here's the current line as it is supplied by the script:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn
\sqlcmd.exe" -d MyDB -Q "Exec Import_EX_FILE @ImportFile='\\my-server\SQL_FileS
tore\ex100521.471', @StatusOut=Output"

The call appears to work, but then it does not do anything. If I call the sp from query analyzer in the management studio, it looks like this:

USE [MyDB]
GO

DECLARE @return_value int,
@StatusOut varchar(4000)

EXEC @return_value = [dbo].[Import_EX_FILE]
@ImportFile = N'\\my-server\SQL_FileStore\ex_files\ex100521.470',
@StatusOut = @StatusOut OUTPUT

SELECT @StatusOut as N'@StatusOut'

SELECT 'Return Value' = @return_value

GO

This works just fine.

Any help?

Question with boldness even the existence of a God; because, if there be one, he must more approve of the homage of reason, than that of blind-folded fear. - Thomas Jefferson

tscribner
Starting Member

4 Posts

Posted - 2010-11-03 : 13:26:53

Can't believe I didn't see this before - while I was reading the posting I made, I realized the pathing was different for the @importFile Variable.

ooops!

Thanks to anyone checking this out though. Now it works great!

Question with boldness even the existence of a God; because, if there be one, he must more approve of the homage of reason, than that of blind-folded fear. - Thomas Jefferson

tscribner
Starting Member

4 Posts

Posted - 2010-11-03 : 14:33:16

OK -- I spoke too soon - that line runs correctly, the problem now is passing it to Wscript.Shell.Run.

The problem is that when I call -

WshShell.Run(exetxt, 0, True)

The problem is that in order to pass the -Q switch, I have to use quotes to encap the query -

So sqlcmd runs great if, from the command prompt, I run:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn
\sqlcmd.exe" -d MyDB -Q "Exec Import_EX_FILE @ImportFile='\\my-server\SQL_FileStore\ex_files\ex100521.475', @StatusOut=Output"

The problem is that WshShell.Run wants the arguments pass with quotes around them:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" "-d MyDB" "-Q "Exec Import_EX_FILE @ImportFile='\\my-server\SQL_FileStore\ex_files\ex100521.475', @StatusOut=Output""

You can see the problem is the double-double quotes that occur because of the -Q switch. So hopefully someone knows of a better way to pass the arguments to sqlcmd, or a way of getting around the double-double issue.

Once again, thanks in advance!

Question with boldness even the existence of a God; because, if there be one, he must more approve of the homage of reason, than that of blind-folded fear. - Thomas Jefferson

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-11-03 : 14:44:25

sounds more like a script question than a sql question

http://forums.devshed.com/asp-programming-51/vbscript-executing-cmd-commands-does-nto-produce-a-log-file-551736.html

If you don't have the passion to help people, you have no passion

tscribner
Starting Member

4 Posts

Posted - 2010-11-03 : 15:10:57

You're right -- thanks.

Question with boldness even the existence of a God; because, if there be one, he must more approve of the homage of reason, than that of blind-folded fear. - Thomas Jefferson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources