SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating database,stored procedures from batch file


Creating database,stored procedures from batch file

Author
Message
Mishal
Mishal
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 168
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
UconnDBA
UconnDBA
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 444
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
Mishal
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 168
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
UconnDBA
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 444
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
Mishal
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 168
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....
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1727 Visits: 5485
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 :w00tSmile

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 uSmile 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

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search