Author |
Topic |
EGoetz
Starting Member
7 Posts |
Posted - 2013-03-29 : 12:24:25
|
Hello all, I'm new to SQL. I have been teaching myself as I go trying to figure out how to do things. I have a bulk insert script. I have a stored procedure. I saved the stored procedure as a .sql file in another folder too. I have another program that can "run external program" and the only files it will run is a .bat or .exe. I want my other program to be able to trigger the stored procedure to run. I think this means I need a .bat file. The problem is... I have no idea how to start with making that. Can anyone help me?Here is my stored procedure:USE [EricaTraining]GO/****** Object: StoredProcedure [dbo].[LoadDailyAdjReport] Script Date: 03/29/2013 10:56:42 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER Procedure [dbo].[LoadDailyAdjReport] AS Bulk Insert EricaTraining.dbo.cust_adj From 'C:\TEST\importformat.txt' With ( FieldTerminator= '|', Rowterminator= '\n' ) |
|
EGoetz
Starting Member
7 Posts |
Posted - 2013-03-29 : 12:59:37
|
Ok... I'm learning some... I tried:@echo offsqlcmd -S Server\database -i C:Pathto.sqlThis doesn't seem to work... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-29 : 13:53:35
|
trysqlcmd -Q "exec yourProcName" -S YourServername -d YourDatabasename -o outputfilename------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
EGoetz
Starting Member
7 Posts |
Posted - 2013-03-29 : 16:45:32
|
Ok, on the SQL Server this .bat file works:sqlcmd -S MyServerName -E -d MyDatabase -Q "EXEC MyStoredProcedure"The server is let's say: \\Server1My program to run the bat file is on another server. The file is shared, the user has security to SQL, to Folder, to program, etc. How would I change the .bat file to be on the server of the program? So, on Server2 I want to have the bat file that accesses and opens the .bat on Server1. To do that, do I need to change the .bat file to have the path of \\Server1\PathName\File.bat? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-29 : 21:49:58
|
Since you have specified the server name in the sqlcmd parameters, assuming proper permissions, network access etc., the command should work as it is.So take the bat file exactly as it is to server2 and run it from there. If that displays an error message, post the error message. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-30 : 01:50:15
|
quote: Originally posted by EGoetz Ok, on the SQL Server this .bat file works:sqlcmd -S MyServerName -E -d MyDatabase -Q "EXEC MyStoredProcedure"The server is let's say: \\Server1My program to run the bat file is on another server. The file is shared, the user has security to SQL, to Folder, to program, etc. How would I change the .bat file to be on the server of the program? So, on Server2 I want to have the bat file that accesses and opens the .bat on Server1. To do that, do I need to change the .bat file to have the path of \\Server1\PathName\File.bat?
are the two servers in same domain?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
EGoetz
Starting Member
7 Posts |
Posted - 2013-04-01 : 09:12:27
|
Yes, the two servers are in the same domain. The error I get on the program server is regarding slqcmd. 'sqlcmd' is not recognized as an internal or external command, operable program or batch file. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-01 : 09:26:15
|
See if you have sqlcmd installed on the server. If not install the client tools. SqlCmd would be in C:\Program Files\Microsoft SQL Server\100\Tools\Binn (for SQL 2008) if you installed it in the default location. I think you can also choose to install only subsets of the client tools - see here: http://www.microsoft.com/en-us/download/details.aspx?id=16978 |
|
|
EGoetz
Starting Member
7 Posts |
Posted - 2013-04-01 : 10:06:18
|
My SQL Server has the sqlcmd utility because I can run the .bat file just fine that has the sqlcmd in it. The program server does not have sqlcmd... it doesn't have sql on it. I'm going to try and put just the sqlcmd utility on that program server. The SQL server is express, I don't think I can use SSIS with express. They don't have VB on the program server. So I'm thinking if I can get sqlcmd to work it will take care of my problems. Unless, you guys have a different set of advice... is there any problem having the sql utilities for sqlcmd on a machine that does not have sql on it? |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2013-12-12 : 11:40:37
|
When i run thissqlcmd -S MyServerName -E -d MyDatabase -Q "EXEC MyStoredProcedure"include switches-h -1 -o c:\test.txt -eI get the test.txt consists ofEXEC MyStoredProcedureDATA CONTENTI don't want the sql statement in my text fileAny ideas |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-12 : 12:01:57
|
-e will echo input scriptsremove it and see------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|