Creating database,stored procedures from batch file

Question

Creating database,stored procedures from batch file

Mishal

Ten Centuries

Points: 1103
More actions
April 24, 2013 at 4:29 am

#97148

Hello,
I am using below code to execute a sql scripts through batch file.....[passing parameters ].
rem This script executes the scripts to create the database, tables and stored procedures
echo off
echo on
osql -n -m-1 -E -S ADLAKHA\SQLEXPRESS -d HOLDB -i D:\batch\Server.sql -v tbname=employ -o hi.txt
echo off
echo Script execution is complete!
Server.sql : - contains any query using parameter from batch file
select * from $(tbName)
GETTING ERROR : Msg 102, Level 15, State 1, Server MANISHA\SQLEXPRESS, Line 1
Incorrect syntax near '$'
I am new to batch files, Could any one please suggest me how to pass parameters from batch file..?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

UconnDBA Default port Points: 1439 More actions · Answer 1

Hi.

I believe you want to use SQLCMD instead of OSQL. I do not see a -v option for OSQL, but there is one in SQLCMD. I ran the below SQLCMD and worked fine....

SQLCMD -m-1 -E -S Server\Instance -d HOLDB -i D:\batch\Server.sql -v tbname=employ -o hi.txt

Also, as far as OSQL, Microsoft states..."This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use the feature. Use sqlcmd instead. For more information, see sqlcmd Utility."

John

Mishal Ten Centuries Points: 1103 More actions · Answer 2

thanks for quick response john,

I also tried with sqlcmd and written below query in server.sql file

select * from $(tbname)

Need to know what is the exact way to execute batch file, by double clicking it or through command prompt.

I am executing file by dbl clicking, but nothing is displaying in output file [hi.txt] file

UconnDBA Default port Points: 1439 More actions · Answer 3

Can run either way, but when I am debugging something I always run from command line to get messages. Be sure you are looking at the right hi.txt file. I say this because without specifying the full path, the file will be created wherever you execute it from.

You may also want to try just running the SQLCMD from the command line, to see if that works. Sometimes permissions can be an issue.

Mishal Ten Centuries Points: 1103 More actions · Answer 4

Thanks John,

I am able to run [ select * from $(tbName) ] by passing parameter using SQLCMD.

Can we pass any path as parameter to SQLCMD, If yes then how to get it from SQL script.

waiting for your response....

x SSC-Insane Points: 23660 More actions · Answer 5

JUST FOR FUN!

Here's a possibility, but its really rube goldberg like, it would probably be easier with actual programming tools like dot.net or powerscript. The included method is probably low on my list of recommendations because you have programs writing other programs and then executing them within the same programming, you can really get some confusing interactions, so obviously you'd like to test stuff like crazy (and also question why the heck you aren't using better tools and designs :w00t:)

In the example included, I use sqlcmd from within a batch file to call sql that writes another batch file which the first one then executes.

I made a directory, called it u:\testdir1 (I have a drive u:) Also assume I have an sql server instance called mysqlserver\dev and in that server I have a database called 'dev'.

In this directory I have:

testdrive1.bat

SQLCMD -m-1 -h-1 -E -S mysqlserver\dev -d %1 -i %2 -v tbname=%3 -o %4

testdrive1.sql

set nocount on

SELECT 'CALL U:\TESTDIR1\TESTDRIVE1.BAT ' +

(SELECT PARAM1 FROM TESTPARAMS1) + ' ' +

(SELECT PARAM2 FROM TESTPARAMS1) + ' ' +

(SELECT PARAM3 FROM TESTPARAMS1) + ' ' +

(SELECT PARAM4 FROM TESTPARAMS1)

Inside of mysqlserver\dev's database also called dev I have a table:

create table testparams1

(

param1 varchar(100),

param2 varchar(100),

param3 varchar(100),

param4 varchar(100)

)

Inside of that table I have

param1param2param3param4

devU:\TESTDIR1\testscript1.sqltesttable1U:\TESTDIR1\testout1.txt

Obviously I only have one row in my table 'testparams1', so it makes selecting the row of parameters I want simple.

I also have a file called testscript1.sql which is just an example of a script I would like to call as a result of its name being a parameter inside table 'testparams1'.

select * from $(tbname)

I run it all with:

u:\testdir1\test_it_all.bat

CALL U:\TESTDIR1\TESTDRIVE1.BAT DEV U:\TESTDIR1\TESTPARAMS1.SQL NONE U:\TESTDIR1\STEP2.BAT

CALL U:\TESTDIR1\STEP2.BAT

and I then have a result in file u:\testdir\testout1.txt because that file name is what I had in column param4 in table 'testparams1.'

I don't know the wisdom of doing things this way however, but I have at least one blob of scripting that works in a similar fashion and its been running for quite a while. Since it writes and then executes a batch file, its definitely not a reentrant procedure suitable for multiuser applications LOL. Obviously testing and understanding the workflow should be top priority and additionally, you probably need a pretty darn good reason to be doing this sort of coding in the first place.

edit: forgot to make the target script use a parameter passed with -v and also hardwired path for consistency