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 make a .bat file of a stored procedure?

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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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 off
sqlcmd -S Server\database -i C:Pathto.sql

This doesn't seem to work...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-29 : 13:53:35
try

sqlcmd -Q "exec yourProcName" -S YourServername -d YourDatabasename -o outputfilename

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2013-12-12 : 11:40:37
When i run this
sqlcmd -S MyServerName -E -d MyDatabase -Q "EXEC MyStoredProcedure"

include switches
-h -1 -o c:\test.txt -e

I get the test.txt consists of
EXEC MyStoredProcedure
DATA CONTENT

I don't want the sql statement in my text file

Any ideas
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-12 : 12:01:57
-e will echo input scripts
remove it and see

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -