| Author |
 Topic  |
|
vishalchowdhary
Starting Member
9 Posts |
 Posted - 08/29/2008 : 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
USA
28965 Posts |
Posted - 08/29/2008 : 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.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
|
 |
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2615 Posts |
Posted - 08/29/2008 : 19:08:22
|
or:
sqlcmd -Q "exec myproc" -SMYSERVER -dMYDATABASE -E -oE:\Data\Output.txt
elsasoft.org |
|
|
|
afrika
Flowing Fount of Yak Knowledge
Nigeria
2659 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
28965 Posts |
|
|
afrika
Flowing Fount of Yak Knowledge
Nigeria
2659 Posts |
Posted - 08/29/2008 : 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
USA
28965 Posts |
Posted - 08/29/2008 : 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Subscribe to my blog
|
|
|
|
vishalchowdhary
Starting Member
9 Posts |
Posted - 08/29/2008 : 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
Flowing Fount of Yak Knowledge
USA
2615 Posts |
Posted - 08/29/2008 : 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 - 08/30/2008 : 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
Flowing Fount of Yak Knowledge
USA
2615 Posts |
|
|
vishalchowdhary
Starting Member
9 Posts |
Posted - 09/01/2008 : 03:19:44
|
Thanks Guys,
My problem got solved by use of a batch file. |
|
|
|
ricksteele
Starting Member
USA
6 Posts |
Posted - 10/19/2009 : 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
United Kingdom
603 Posts |
Posted - 10/19/2009 : 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
USA
6 Posts |
Posted - 10/20/2009 : 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 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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
28965 Posts |
|
|
ricksteele
Starting Member
USA
6 Posts |
Posted - 10/20/2009 : 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
USA
28965 Posts |
|
|
ricksteele
Starting Member
USA
6 Posts |
Posted - 10/20/2009 : 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. |
Edited by - ricksteele on 10/28/2009 09:36:05 |
|
|
|
ricksteele
Starting Member
USA
6 Posts |
Posted - 10/20/2009 : 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_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.
|
|
|
|
ricksteele
Starting Member
USA
6 Posts |
Posted - 10/28/2009 : 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. |
Edited by - ricksteele on 10/28/2009 09:36:57 |
|
|
|
Topic |
|
Batch file to execute SQL stored procedure
