how to create batch file for a sql script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to create batch file for a sql script

Author

Topic

Ronesh
Starting Member

33 Posts

Posted - 2009-09-08 : 06:40:44

Dear all,

Actually i am working in one of the software company.And sometime i need to send update script to clients(sql script). And i have to suppot them in phone for a long time just to run the script properly.
And this is the case for almost all clients.
So it is very tidious.

So i was wondering if i can make a batch file for a sql script.
And the client has to just click that batch file and the processing is done.

The batch file must have the parameters like
server name
database name

looking forward for the positive response.

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-08 : 06:45:25

Search for SQLCMD utility

Rahul Shinde

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-08 : 06:48:39

Sqlcmd [-U login id] [-P password] [-S server] [-v var = "value"...] [-i inputfile]

Rahul Shinde

Ronesh
Starting Member

33 Posts

Posted - 2009-09-08 : 07:01:08

Dear Rahul,
how do i write sqlcmd utility command as you have suggested?

Sqlcmd [-U login id] [-P password] [-S server] [-v var = "value"...] [-i inputfile]
i got login id and password and server.
what about this -v and -i.
Acutally i have a update script like as below
update table1 set col1=123 where col1 =234
and i want this to be update just by clicking the batch file.

don't get irritate if this question annoys you.
even if the solution is very easy.
actually i am new to this sql field.

thanks for your response.

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-08 : 07:27:31

inputfile is fullpath of your .sql file.
So, you can write your sql statement in script file(.sql).
-V is for providing a value for tokans used in script file.
i.e. you can write statement like "update $(dbname).dbo.table1 set col1=123 where col1 =234" in script file.
while executing SQLCMD, you can pass -v dbname = <your dbname>. the utility will replace @dbname with your dbname.

You have to just write this cmd in batchfile.

Sqlcmd [-U login id] [-P password] [-S server] [-v var = "value"...] [-i inputfile]
pause

For executing, this batch file from any path, you have to add this path "C:\Program Files\Microsoft SQL Server\90\Tools\Binn" to enviroment variable "PATH".

Rahul Shinde

Ronesh
Starting Member

33 Posts

Posted - 2009-09-08 : 08:51:49

i created a batch file as

sqlcmd -U sa -P hello@123 -S DBSERVER\SQL2K5 -V AXIS -i d:\script\udpate.sql
pause

here AXIS is my database name in server DBSERVER\SQL2K5

update.sql script is as under

update table1 set col1=123 where col1=234

And when i run the batch,
the error is as under
sqlcmd: '-V AXIS': Severity level has to be a number between 1 and 25

what do you suggest Rahul?

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-08 : 09:07:37

You are not using any tokan(variable) for dbname in update.sql script.
it should be 'update $(dbname).dbo.table1 set col1=123 where col1=234'.
Use this in batch file. I made one mistake above. in -v, v should be small case.
sqlcmd -U sa -P hello@123 -S DBSERVER\SQL2K5 -v dbname = "AXIS" -i d:\script\udpate.sql
pause.

OR

keep your update.sql as it is. simply remove "-v AXIS" from SQLCMD. This option is optional.

Rahul Shinde

Ronesh
Starting Member

33 Posts

Posted - 2009-09-09 : 02:17:34

Dear Rahul,
i created the batch file as
SQLCMD -U sa -P hello@123 -S DBSERVER\SQL2K5 -d AXIS -i C:\script\update.sql
Pause

and update.sql as
update Table1 set col1=123 where col1=234

i have put both update and batch file both in
c:\script.

when i run the batch file
the error is shown as
Sqlcmd: 'update.sql': Invalid filename.

what to do now?

i was wondering if i can write the update script in the batch file only.is it possible?
looking for the positive response.

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-09 : 02:32:18

try using double quotes around file name.

Rahul Shinde

Ronesh
Starting Member

33 Posts

Posted - 2009-09-09 : 02:33:50

same error

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-09 : 02:46:00

I am not sure, why this is happening on your end. I use this method almost every day.
just check your filename in batch file and actual file name.

Rahul Shinde

Ronesh
Starting Member

33 Posts

Posted - 2009-09-09 : 07:36:55

i got through the sqlcmd utility of sql server 2005.

and the script also work fine.
the script is like this as you suggested.
SQLCMD -U sa -P hello@123 -S DBSERVER\SQL2K5 -q 'upate table1 set col1=123 where col1=234'
but when i tried to use the argument -i c:\script\update.sql
the error occured as i stated earlier
Sqlcmd: 'c:\script\update.sql':Invalid filename

what do you suggest?
so far you gave me nice suggestion.
Looking forward for positive response.

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-09 : 09:50:28

When you use the SQL script file, you can use PL/SQL blocks in it. But if you are OK with single statement at a time, you can use -q option. Mean while, I will try to find the issue regarding invalid file name. I will get back to you, if found some thing.

Rahul Shinde

Ronesh
Starting Member

33 Posts

Posted - 2009-09-10 : 00:26:31

ok.
i got it correct.
when i tried that very script in my home it worked correctly.but i dont know why it didn't work when i tried in my office.

yes i saw the - q and -Q options as well.

is it possible to write the whole script of stored procedure within that -q option.
when i tried there was error.
the stored procedure script is very long about more than 100's of line.
yes i can call the script by using -i option.
but i was wondering if i can write the whole script within the batch file itself.

Looking forward for the positive response.

ilaughforu
Starting Member

3 Posts

Posted - 2009-12-09 : 14:56:25

I am having similar problem

I am trying to create a script that runs all the sql scripts in a folder but I am getting error

I tried using sqlcmd (Error: Incorrect syntax near S)
and xp_cmd_shell (Error: Incorrect syntax near -p 123 -u BVS)
but both gives error

--Turn on Advanced features
EXEC sp_configure 'show advanced options', 1
reconfigure
go

--Turn on xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
reconfigure
go

--exec master.dbo.xp_cmdshell '"C:\Projects\*.sql"' -p 123 -u BVS
--exec master.dbo.xp_cmdshell '"C:\Projects\*.sql"' '-p 123 -u BVS

sqlcmd -S SERVER -d database -e -U BVS -P 123 -i C:\Projects\*.sql

go
--Turn off xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
reconfigure
go
--Turn off advanced options
EXEC sp_configure 'show advanced options', 0
reconfigure
go

Please help

Pooja

Stevie
Starting Member

2 Posts

Posted - 2011-07-07 : 06:17:23

Hi all

I have the same probblem can one of you guys just help me out pls...this is my Batch file "SQLCMD -U STEVIE-PC -P 68791 -S SQL Server10.0.1600-STEVIE-PC\Admin -v dbname = LINKTEC -i C:\Program Files\SQLQuery2.sql"
pause.

Pls i need to get this rite...

Kind regards Stevie

STM

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-07 : 08:54:14

If STEVIE-PC is your login name, and the SQL instance is called SQL Server10.0.1600-STEVIE-PC\Admin...

SQLCMD -U [STEVIE-PC] -P 68791 -S [SQL Server10.0.1600-STEVIE-PC\Admin] -v dbname = LINKTEC -i C:\Program Files\SQLQuery2.sql

ROLASHISH
Starting Member

3 Posts

Posted - 2012-01-12 : 06:36:32

How I create batchfile using sqlscript file.

ashishn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources