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
 General SQL Server Forums
 New to SQL Server Programming
 How to make a .bat file of a stored procedure?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

EGoetz
Starting Member

USA
7 Posts

Posted - 03/29/2013 :  12:24:25  Show Profile  Reply with Quote
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

USA
7 Posts

Posted - 03/29/2013 :  12:59:37  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/29/2013 :  13:53:35  Show Profile  Reply with Quote
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

USA
7 Posts

Posted - 03/29/2013 :  16:45:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 03/29/2013 :  21:49:58  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/30/2013 :  01:50:15  Show Profile  Reply with Quote
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

USA
7 Posts

Posted - 04/01/2013 :  09:12:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/01/2013 :  09:26:15  Show Profile  Reply with Quote
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

USA
7 Posts

Posted - 04/01/2013 :  10:06:18  Show Profile  Reply with Quote
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

593 Posts

Posted - 12/12/2013 :  11:40:37  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
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

India
52325 Posts

Posted - 12/12/2013 :  12:01:57  Show Profile  Reply with Quote
-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
  Previous Topic Topic Next Topic  
 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