SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 Batch file to execute SQL stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

vishalchowdhary
Starting Member

9 Posts

Posted - 08/29/2008 :  18:50:19  Show Profile  Reply with Quote
Hi,
I want to create a batch file which will internally execute a stored procedure for Windows OS. Can anyone please explain me how to write a batch file starting with the db connection and ending with the stored procedure execution. The batch file is supposed to run at scheduled intervals.

Which is the best way to achieve this task?


Thanks a ton.

Vishal

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 08/29/2008 :  19:05:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can use sqlcmd.exe for SQL Server 2005, osql.exe for SQL Server 2000, and isql.exe for earlier versions.

Here's an example sqlcmd call:

sqlcmd -Sserver1\instance1 -E -iE:\Data\SomeScriptFile.sql -oE:\Data\Output.txt

And then in SomeScriptFile.sql would be EXEC dbo.StoredProcName param1 = @var1...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 08/29/2008 :  19:08:22  Show Profile  Visit jezemine's Homepage  Reply with Quote
or:

sqlcmd -Q "exec myproc" -SMYSERVER -dMYDATABASE -E -oE:\Data\Output.txt


elsasoft.org
Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 08/29/2008 :  19:20:28  Show Profile  Reply with Quote
For the db connection see www.connectionstrings.com
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 08/29/2008 :  19:20:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by afrika

For the db connection see www.connectionstrings.com



That doesn't apply to this situation.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 08/29/2008 :  19:28:09  Show Profile  Reply with Quote
quote:
Originally posted by vishalchowdhary

Can anyone please explain me how to write a batch file starting with the db connection

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 08/29/2008 :  19:50:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by afrika

quote:
Originally posted by vishalchowdhary

Can anyone please explain me how to write a batch file starting with the db connection





That still doesn't apply here. Connection strings are for applications and such.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

vishalchowdhary
Starting Member

9 Posts

Posted - 08/29/2008 :  20:19:54  Show Profile  Reply with Quote
Hey Tara,
But I still don't understand how would I write the batch file. This file will then be run on a scheduled interval which again I don't know how to do since I'm new to all this.

Pls help.

Thanks
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 08/29/2008 :  23:54:36  Show Profile  Visit jezemine's Homepage  Reply with Quote
if all you want to do is execute a proc on some interval, it makes more sense to create an agent job and forget about sqlcmd and batch files.


elsasoft.org
Go to Top of Page

vishalchowdhary
Starting Member

9 Posts

Posted - 08/30/2008 :  20:27:05  Show Profile  Reply with Quote
quote:
Originally posted by jezemine

if all you want to do is execute a proc on some interval, it makes more sense to create an agent job and forget about sqlcmd and batch files.


elsasoft.org




Hey. Can you explain in a bit more detailed fashion how I could achieve the purpose with agent job?
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 08/31/2008 :  00:31:25  Show Profile  Visit jezemine's Homepage  Reply with Quote
there is lots of detail on this in SQL Server Books Online: http://msdn.microsoft.com/en-us/library/ms187880.aspx


elsasoft.org
Go to Top of Page

vishalchowdhary
Starting Member

9 Posts

Posted - 09/01/2008 :  03:19:44  Show Profile  Reply with Quote
Thanks Guys,
My problem got solved by use of a batch file.
Go to Top of Page

ricksteele
Starting Member

USA
6 Posts

Posted - 10/19/2009 :  15:21:20  Show Profile  Reply with Quote
Hi,

I was hoping to use this batchfile technique to refresh data in my database on a regular schedule. I'm using SQL Express 2008.

I'm tryign to execute a stored procedure in my database using the command line. I'm getting an error about a linked server, an Access database that's stored on a network drive. The sp runs fine within SSMS. Any idea why the linked server would be failing?
Go to Top of Page

YellowBug
Aged Yak Warrior

United Kingdom
616 Posts

Posted - 10/19/2009 :  17:22:23  Show Profile  Reply with Quote
What is the exact error message?

Which user is connecting from the command? Is it different to the one in SSMS?
Go to Top of Page

ricksteele
Starting Member

USA
6 Posts

Posted - 10/20/2009 :  13:50:22  Show Profile  Reply with Quote
quote:
Originally posted by YellowBug

What is the exact error message?

Which user is connecting from the command? Is it different to the one in SSMS?


C:\>sqlcmd -S mlk98\sqlexpress -d BomAudit -Q "EXEC RefreshFromPDC_PRDSTR"

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server 
"PDCPASSTHRUACCESS" returned message "'J:\Innovations\ENGADMIN
\PDI\PdcDataAudit\PdcPassthru.mdb' is not a valid path.  Make sure 
that the path name is spelled correctly and that you are connected to
 the server on which the file resides.".
Msg 7303, Level 16, State 1, Server MLK98\SQLEXPRESS, Procedure 
vewReadPdc_part_bom, Line 3
Cannot initialize the data source object of OLE DB provider 
"Microsoft.Jet.OLEDB.4.0" for linked server "PDCPASSTHRUACCESS".

That is my command line and the resulting error message. I am running it as myself both in the cmd shell and within SSMS.

Edited by - ricksteele on 10/20/2009 13:53:23
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 10/20/2009 :  13:55:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
Login to the database server using the SQL Server service account. I mean directly into the server such as with RDP or through the console. Validate that the path exists and that the account has permissions to read the ending directory.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

ricksteele
Starting Member

USA
6 Posts

Posted - 10/20/2009 :  13:56:31  Show Profile  Reply with Quote
I wonder, is it because when executing from SQLCMD, it is in the context of a local machine user that does not have the network drive mapped?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 10/20/2009 :  13:59:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes! That's what I was referring to in my last post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Edited by - tkizer on 10/20/2009 14:00:08
Go to Top of Page

ricksteele
Starting Member

USA
6 Posts

Posted - 10/20/2009 :  14:02:54  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Login to the database server using the SQL Server service account. I mean directly into the server such as with RDP or through the console. Validate that the path exists and that the account has permissions to read the ending directory.


I don't know if I can do that. I work in engineering, not IT, I don't have administrative rights except locally on my PC, which is also where the SQL server is.
I was thinking to try copying the MDB to my local HD, redefining the linked server to read from there, then try it again.

OK, that was dumb, the SQL server I'm trying to run against IS on my local machine. Must have been thinking about too many things at once.

So... did you mean that I should log on as the same username that the SQL Server service is running under? That would be LocalSystem (as shown in services under "log on as"). I get an "Unknown User name or bad password" error when I try it without a password.

Edited by - ricksteele on 10/28/2009 09:36:05
Go to Top of Page

ricksteele
Starting Member

USA
6 Posts

Posted - 10/20/2009 :  14:34:45  Show Profile  Reply with Quote
OK, I got it set up as described above. Now the error message is different:
quote:
Msg 7306, Level 16, State 2, Server MLK98\SQLEXPRESS, Procedure RefreshFromPDC_I
TMMAS_BASE, Line 15
Cannot open the table "read_dbo_part_master_cmp" from OLE DB provider "Microsoft
.Jet.OLEDB.4.0" for linked server "PDCPASSTHRUACCESS". The specified table or vi
ew does not exist or contains errors.


Again, if I EXEC the same stored procedure from within SSMS, it works fine.
"read_dbo_part_master_cmp" is a query that reads an ODBC pass-thru query which in turn reads the source data from another SQL server. (I was not able to use a direct link from my SQL server to the corporate SQL server, nor replication.) I double-checked that the ODBC pass-thru is using an ODBC source in the system DSN, not the user DSN.

Go to Top of Page

ricksteele
Starting Member

USA
6 Posts

Posted - 10/28/2009 :  09:29:31  Show Profile  Reply with Quote
I think I know what's wrong. SQL Server's account (LocalSystem) probably does not have authority to connect to the remote SQL server being linked to thru the linked server definition. Can I launch commands using my own credentials using SQLCMD?

And I just realized I'm probably in the wrong forum, as my issue is with 2008 and this forum is 2005. Sorry.

Edited by - ricksteele on 10/28/2009 09:36:57
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000