x

How to call a stored procedure from command line

I am on windows 2003 and SQL server 2000. I have a stored procedure "XYZ" which accepts input parameters "P", "Q". Can anyone tell me, How can I call this stored procedure from the command prompt (Dos prompt) from another workstation. Is it possible to invoke this procedure from any workstation or do I need to run this on the same server where my database is installed ?

I am new to SQL server 2000/2005. Any help will be greatly appreciated.

With Regards

Novice

more ▼

asked Nov 13 '09 at 03:24 AM in Default

SQLserver_novice gravatar image

SQLserver_novice
113 6 6 7

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Edited to use two-part naming convention for stored procedure (schema.spname).

SQL Server 2000 syntax:

Use osql. Here is an example, but check the link for more options.

osql -E -S server_name -d database_name -Q "EXEC schema.spname param1, param2"

SQL Server 2005 syntax:

Use sqlcmd. Again, this is just a simple example.

sqlcmd -E -S server_name -d database_name -Q "EXEC schema.spname param1, param2" 
more ▼

answered Nov 13 '09 at 05:27 AM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

+1, but remember to owner qualify that proc name... :)
Nov 13 '09 at 05:55 AM Matt Whitfield ♦♦
touche! ... done.
Nov 13 '09 at 01:07 PM Tom Staab
(comments are locked)
10|1200 characters needed characters left

This is a copy of my "do_sqlcmd" (very much the same as osql) batch file. All calls and procedures are text files that this batch file uses.

@echo off @REM -S server name @REM -d database name @REM -E Uses a trusted connection, no password required @REM -i names the input_file with a batch of SQL statements or stored procedures to be executed @REM -o output_file that receives output from OSQL. @REM -n removes numbering and the prompt symbol (>) from output lines. @REM -l 3600 The Number Of Seconds Before The Command Times Out (1 hr) @REM -e echo the source

@REM Description of DOS variables used: @REM %DB_SVR% variable contains server name @REM %DB_NAME% variable contains database name @REM %CRT_DIR% variable contains the name of the folder with the source script file @REM %LOG_DIR% variable contains the name of the folder where OSQL utility will write output message @REM %SCR_NAME% variable contains the name of the script file to be executed @REM %SCR_EXT% variable contains the extension of the script file @REM %LOG_EXT% variable contains the extension of the log file

SET SRC_NAME=%1 if "%SRC_NAME%"=="" goto exit SET DB_SVR=%2 if "%DB_SVR%"=="" SET DB_SVR=testserver SET CRT_DIR=%3 if "%CRT_DIR%"=="" SET CRT_DIR=%RunDir% SET LOG_DIR=%4 if "%LOG_DIR%"=="" SET LOG_DIR=%LogDir% SET DB_NAME=%5 if "%DB_NAME%"=="" SET DB_NAME=%DbName% SET SRC_EXT=%6 if "%SRC_EXT%"=="" SET SRC_EXT=.sql SET LOG_EXT=%7 IF "%LOG_EXT%"=="" SET LOG_EXT=.log SET Pass=%8 IF %Pass%="" goto exit set LOG_NAME=%SRC_NAME% sqlcmd -U %User% -P %Pass% -S %DB_SVR% -d %DB_NAME% -i %CRT_DIR%\%SRC_NAME%%SRC_EXT% -l 3600 >> %LOG_DIR%\%LOG_NAME%%LOG_EXT% :exit
more ▼

answered Nov 13 '09 at 03:00 PM

dvroman gravatar image

dvroman
1.1k 2 2

(comments are locked)
10|1200 characters needed characters left

Yes , lookup the sqlcmd utility in BOL.

more ▼

answered Nov 13 '09 at 04:19 AM

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

sqlcmd is only available in SQL Server 2005 and beyond.
Nov 13 '09 at 05:16 AM Tom Staab
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x472
x401
x7

asked: Nov 13 '09 at 03:24 AM

Seen: 24503 times

Last Updated: Nov 16 '09 at 05:42 PM