How to pass .CMD file parameters to an OSQL query

[TheBugSlayer]

Hello good people.

I am struggling with a minor situation:

I need to pass either local variables or parameters of the batch/command file to an OSQL call inside the file. Here is a snippet of my CMD file:

...
if "%1" == "DEV" goto setDEV
...
:setDEVL
set srv = DEVELOP
set target = \\FSRVDEV\in\
set source = \\FSRVDEV\out\
goto runImport
...
:runImport
osql -S%srv% -E -dPDX_MAIN -Q"EXEC p_Load_CCHR2_Tables %target% , %source%"

If parameters were passed where 2 is source and 3 target, I would like to use a call like this:

osql -S%srv% -E -dPDX_MAIN -Q"EXEC p_Load_CCHR2_Tables %2% , %3%"

But none is working so for now I hard code all the paths. When I add ECHO statements to the CMD file to see the variables I see their name instead of their content. What is the correct way to pass those values to the OSQL command?

Your help is appreciated.

 

[hmckillop]

reply here is a sample of a file I use

REM @echo off
SET CURDIR=%1

SET DBNAME=ichobs
SET MyPassword=2difficult4u2c
SET SQLSERVER=mymachinename
SET SA_USER=sa
rem remember to use quotes round things which have spaces or other non alpha characters


ECHO %SQLSERVER%
ECHO %DBNAME%

echo %curdir%


ECHO **********BEGIN PATCHING DATABASE***********


echo Create DB Version Table.

OSQL -S %SQLSERVER% -U %SA_USER% -P %MyPassword% -d %DBNAME% -i "mysqlScriptWithTSQL.sql" -o "mysqlScriptWithTSQL.out"



ECHO **********END PATCHING DATABASE***********

This works fine . the file is called mydb.bat and i execute via command prompt "mydb.bat ."


"I'm living so far beyond my income that we may almost be said to be living apart

 

[TheBugSlayer]

Thanks hmckillop, notice that my SQL statement is a call to a stored procedure that takes two parameters. Not sure how to pass them. Will try your method. Maybe I should concatenate the SQL string, i.e. build it so that parameters are outside...Will let you know.

 

[TheBugSlayer]

The OSQL command line just does not take the parameters...

set srv = DEVELOP
echo %srv
[\code]

prints nothing. That is the same reason
osql -S%srv% -E... is executed as osql_S -E... resulting in an "SQL Server does not exist or access denied" error. Is this a version issue?

 

[hmckillop]

You can do it just need to get creative

REM @echo off
SET CURDIR=%1

SET DBNAME=mydb
SET MyPassword=Pa55w0rd
SET SQLSERVER=mymachinename
SET Parm1='stringvalue'
SET PARM2=12 
SET SA_USER=sa
rem remember to use quotes round things which have spaces or other non alpha characters


ECHO %SQLSERVER%
ECHO %DBNAME%

echo %curdir%


ECHO **********BEGIN PATCHING DATABASE***********


echo Create DB Version Table.

OSQL -S %SQLSERVER% -U %SA_USER% -P %MyPassword% -d %DBNAME% -i "mysqlScriptWithTSQL.sql" -o "mysqlScriptWithTSQL.out"
osql -S%SQLSERVER% -E -d%DBNAME% -Q"EXECUTE spname @parm1='%parm1%', @parm=%parm2%"

"I'm living so far beyond my income that we may almost be said to be living apart

 

[TheBugSlayer]

hmckillop,

My problem was only the missing single quotes around the parameters. How silly is that? We fail to see the simplest things.

osql -S%srv% -E -dPDX_MAIN -Q"EXEC p_Load_CCHR2_Tables '%target%' , '%source%'"

(the syntax in your second example) did the trick.

Thank you for your time in any case.

 

[hmckillop]

No worries, only too glad to help

"I'm living so far beyond my income that we may almost be said to be living apart