Passing params from .bat into .sql

Hi,

From a batch file I am using the OSQL utility to execute a .sql file.

Is there any way to pass parameters from this batch file into the .sql file?

Thanks & bye,
Jaya.
jayachristinaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ispalenyConnect With a Mentor Commented:
Dear jayachristina,
the answer is that parameters cannot be passed DIRECTLY between BAT and SQL files. You can build dynamic sql code in BAT file, but in W9x it is limited to 256 chars or less, it is OS-dependent. Store data in user table or file (tab delimited, fill #table in SQL by insert #table(Col1,..) exec master.dbo.xp_cmdshell 'type filename').

If you code is really so short, use dynamic sql code in BAT file. I used it in the 2nd osql line of my BAT solution.

Finally, you wrote "it doesn't seem to solve my problem" and "it works fine". Schizo? The question is answered when the solution works fine. You can reward the best fine tuned solution by separate FOR thread.
0
 
Bruce CadizQuality SpecialistCommented:
If you mean "Oracle SQL" by OSQL you can do the following in your bat / cmd file:


%ORACLEHOME%\bin\sqlplus.exe scott/tiger@orcl @sqlfile.sql para1 para2 para3 ...



In the sqlfile.sql you would use syntax &1 &2 &3 ....:

SET VERIFY OFF;

SELECT * FROM EMP
 WHERE HIREDATE BETWEEN '&1' AND '&2'
   AND DEPTNO = &3;
0
 
Bruce CadizQuality SpecialistCommented:
My bad OSQL utility... DUH!
Check out http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp

Based on the above link I would expect the OSQL syntax would work as follows

SET para1 = "1/1/2003"
SET para2 = "2/1/2003"
SET para3 = 10

osql -Usa -P -Q "Select * from emp where hiredate between '%para%' and '%para2%' and deptno=%para3%"
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ispalenyCommented:
For user test, password testpwd with create table perm.
In YourFile.sql, you can use test.Value table columns for params.

REM START BAT FILE
@echo off
osql -Utest -Ptestpwd -q"exit(if object_id('test.Value') is not null drop table test.Value)"
osql -Utest -Ptestpwd -q"exit(select Par1=convert(nvarchar(4000),'%1'), Par2=convert(nvarchar(4000),'%2'), Par3=convert(nvarchar(4000),'%3'), Par4=convert(nvarchar(4000),'%4'), Par5=convert(nvarchar(4000),'%5'), Par6=convert(nvarchar(4000),'%6'), Par7=convert(nvarchar(4000),'%7'), Par8=convert(nvarchar(4000),'%8'), Par9=convert(nvarchar(4000),'%9') into test.Value)"
osql -Utest -Ptestpwd  -i YourFile.sql
osql -Utest -Ptestpwd -q"exit(drop table test.Value)"
REM END BAT FILE

Params are in a crosstable. You can rewrite it.

Good luck!
0
 
jayachristinaAuthor Commented:
c336914 and  ispaleny, thanks a lot for your replies, but it doesn't seem to solve my problem.

ispaleny, your solution works fine, but is there a way to pass params directly without creating the temporary(test.Value) table?

Let me rephrase my question and try to be more clear.

* I have a file 'myFile.sql' whose contents are.,
insert into users(name, password) values ('anand', 'paul')

* I run myFile.sql using a batch file 'myBatch.bat' whose contents are.,
OSQL -Uadmin -PadminPass -SBERGAMO -dtempdb -n -i"C:\temp\eg\myFile.sql" -o"C:\temp\eg\myFile_out.txt"

* Instead of hard coding 'anand' and 'paul' into the insert statement, I would like to pass it as a parameter from myBatch.bat.

* I am using MS SQL 2000 running on Windows 2000 Server.

Thank you & bye,
Jaya.
0
 
jayachristinaAuthor Commented:
c336914 and  ispaleny, thanks a lot for your replies, but it doesn't seem to solve my problem.

ispaleny, your solution works fine, but is there a way to pass params directly without creating the temporary(test.Value) table?

Let me rephrase my question and try to be more clear.

* I have a file 'myFile.sql' whose contents are.,
insert into users(name, password) values ('anand', 'paul')

* I run myFile.sql using a batch file 'myBatch.bat' whose contents are.,
OSQL -Uadmin -PadminPass -SBERGAMO -dtempdb -n -i"C:\temp\eg\myFile.sql" -o"C:\temp\eg\myFile_out.txt"

* Instead of hard coding 'anand' and 'paul' into the insert statement, I would like to pass it as a parameter from myBatch.bat.

* I am using MS SQL 2000 running on Windows 2000 Server.

Thank you & bye,
Jaya.
0
 
Bruce CadizQuality SpecialistCommented:
Jaya,

Try this as a psuedo interactive batch file. It's not fancy but should work.

@cls
@echo off
if "%1"=="" goto end
if "%2"=="" goto end
set outfile="C:\temp\eg\myFile_out.txt"
echo.
echo======================================================
echo NEWUSER INSERT:
echo ADD USER '%1' PASSWORD '%2'?
echo======================================================
echo Hit Enter to continue or ^^C to Abort
echo.
pause
OSQL -Uadmin -PadminPass -SBERGAMO -dtempdb -n -Q"insert into users(name, password) values ('%1', '%2')" -o">"%outfile%"
echo.
echo.
echo ======================================================
echo %outfile% Contents:
echo.--------------------
type %outfile%
echo ======================================================
pause
goto done
:end
echo.
echo ====================================
echo Error No Parameters Passed:
echo ----------------------------
echo.
echo USAGE: myBatch {USERNAME} {PASSWORD}
echo.====================================
pause
:done

Bruce

Note: You can also change "TYPE" command to "notepad.exe" to invoke Notepad to view output file.

You can Also set user, pass in enviroment variables
0
 
jayachristinaAuthor Commented:
ispaleny,

Thanks a lot for your help. Now that I know for sure that params can't be passed directly from .bat to .sql, I've resorted to using your previous answer (ie., creating the temp table, populating it, and reading from it).

Thank you very much for your time and patience in explaining it to me.

Bye,
Jaya.
0
All Courses

From novice to tech pro — start learning today.