| Author |
Topic |
|
vishalchowdhary
Starting Member
9 Posts |
 Posted - 2008-08-29 : 18:50:19
|
| 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
38200 Posts |
Posted - 2008-08-29 : 19:05:49
|
| 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.txtAnd then in SomeScriptFile.sql would be EXEC dbo.StoredProcName param1 = @var1...Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-08-29 : 19:08:22
|
or:sqlcmd -Q "exec myproc" -SMYSERVER -dMYDATABASE -E -oE:\Data\Output.txt
elsasoft.org |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-08-29 : 19:20:28
|
| For the db connection see www.connectionstrings.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-29 : 19:20:51
|
quote:
Originally posted by afrika
For the db connection see www.connectionstrings.com
That doesn't apply to this situation.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-08-29 : 19:28:09
|
quote:
Originally posted by vishalchowdhary
Can anyone please explain me how to write a batch file starting with the db connection
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-29 : 19:50:21
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
vishalchowdhary
Starting Member
9 Posts |
Posted - 2008-08-29 : 20:19:54
|
| 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 |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-08-29 : 23:54:36
|
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 |
|
|
|
vishalchowdhary
Starting Member
9 Posts |
Posted - 2008-08-30 : 20:27:05
|
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? |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
vishalchowdhary
Starting Member
9 Posts |
Posted - 2008-09-01 : 03:19:44
|
| Thanks Guys, My problem got solved by use of a batch file. |
|
|
|
ricksteele
Starting Member
6 Posts |
Posted - 2009-10-19 : 15:21:20
|
| 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? |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-10-19 : 17:22:23
|
| What is the exact error message?Which user is connecting from the command? Is it different to the one in SSMS? |
|
|
|
ricksteele
Starting Member
6 Posts |
Posted - 2009-10-20 : 13:50:22
|
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 3Cannot 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ricksteele
Starting Member
6 Posts |
Posted - 2009-10-20 : 13:56:31
|
| 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? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ricksteele
Starting Member
6 Posts |
Posted - 2009-10-20 : 14:02:54
|
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. |
|
|
|
ricksteele
Starting Member
6 Posts |
Posted - 2009-10-20 : 14:34:45
|
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_ITMMAS_BASE, Line 15Cannot 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 view 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. |
|
|
|
ricksteele
Starting Member
6 Posts |
Posted - 2009-10-28 : 09:29:31
|
| 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. |
|
|
|
Next Page
|
Batch file to execute SQL stored procedure