question

SQLserver_novice avatar image
SQLserver_novice asked

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

sql-server-2000stored-procedurescommand-line
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

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"
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1, but remember to owner qualify that proc name... :)
0 Likes 0 ·
touche! ... done.
0 Likes 0 ·
dave ballantyne avatar image
dave ballantyne answered

Yes , lookup the sqlcmd utility in BOL.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dvroman avatar image
dvroman answered

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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.