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
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"
Yes , lookup the sqlcmd utility in BOL.
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
