Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating database,stored procedures from batch file Expand / Collapse
Author
Message
Posted Wednesday, April 24, 2013 4:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 11:39 PM
Points: 50, Visits: 149
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
Post #1445843
Posted Wednesday, April 24, 2013 6:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 5:02 AM
Points: 68, Visits: 423
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



Post #1445894
Posted Wednesday, April 24, 2013 6:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 11:39 PM
Points: 50, Visits: 149
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
Post #1445916
Posted Wednesday, April 24, 2013 7:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 5:02 AM
Points: 68, Visits: 423
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.
Post #1445936
Posted Monday, April 29, 2013 2:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 11:39 PM
Points: 50, Visits: 149
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....

Post #1447400
Posted Tuesday, April 30, 2013 8:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:07 PM
Points: 376, Visits: 2,610
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 )

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
param1	param2	param3	param4
dev U:\TESTDIR1\testscript1.sql testtable1 U:\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
Post #1448092
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse