Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need help with a .bat for a stored procedure, please. Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 11:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 4, Visits: 9
Hello all,

I'm new to SQL and teaching myself as I go. I don't know what to do now though. I'm hoping someone can help or get me in the right direction.

I have a script to do a bulk insert. I created a stored procedure for my bulk insert. I saved a copy of my stored procedure.sql in a folder.

I have a program that can run an external program. It will only find .bat and .exe files. I need to save my stored procedure as a .bat so that I can have my other program trigger it.

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= ''
)

Here is what I have tried in a .bat file:

@echo off
sqlcmd -S myservername\databasename -i C:\mypath\thestoredprocedure.sql

that didn't work, so then i tried:

@echo off
sqlcmd -Smyservername\databasename -E -iE C:\mypath\thestoredprocedure.sql

This doesn't seem to work either. The problem is... I don't really know how to use all the aliases or if the .bat file needs to have a closing command or if my stored procedure needs something else...

If any of you could offer some advice, I'd really appreciate it.

Thank you!
Post #1437024
Posted Friday, March 29, 2013 11:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 901, Visits: 7,165
The command file you pass into SQLCMD should not be the stored proc, it should just be the command to execute the proc:

USE databasename
GO
EXEC storedprocname
GO

Since the proc already exists in the database, the above commands will point the connection to the right database and execute the existing proc. Make sure that the account under which SQL Server is running has permissions to read the directory where the input file lives, and you should be good to go.




And then again, I might be wrong ...
David Webb
Post #1437028
Posted Friday, March 29, 2013 2:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 4, Visits: 9
Ok, on the SQL Server this .bat file works:

sqlcmd -S MyServerName -E -d MyDatabase -Q "EXEC MyStoredProcedure"

The server is let's say: \\Server1\
My 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?
Post #1437097
Posted Friday, March 29, 2013 3:46 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 901, Visits: 7,165
That sounds right, as long as the permissions to the share were set up correctly for the account running the .bat file. Did you try it?



And then again, I might be wrong ...
David Webb
Post #1437119
Posted Friday, March 29, 2013 3:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 4, Visits: 9
Well, on the SQL server I have a folder and it is shared with a group. In that folder are subfolders and in one of those subfolders is the file. The user for all pieces is in that group. So is that the "share" you are talking about?

Oh, and in my command prompt on my Program Server when I try to run the stored procedure I see:

Y:\>RootDirectoryFolder\SQLStoreProcedures\Load_Daily_Adjustment.bat

(Y is the mapped network drive for \\Server1, which is the SQL Server)

The response I get is:
'sqlcmd' is not recognized as an internal or external command, operable program or batch file.
Post #1437122
Posted Saturday, March 30, 2013 5:04 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:35 PM
Points: 975, Visits: 3,350
Why not get away from the old fashioned batch files, and use a simple SSIS package? That would be so much more simple, more efficient and much more secure. And everything would be nicely contained in the MSDB database. You can do so much more in the package, like checking to see if your source file actually exists before processing, moving the file to another directory after the processing is complete, or just deleting the file if you do not want to keep it. And you can easily schedule the package to fire off when needed.

Andrew SQLDBA
Post #1437193
Posted Saturday, March 30, 2013 1:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
AndrewSQLDBA (3/30/2013)
Why not get away from the old fashioned batch files, and use a simple SSIS package? That would be so much more simple, more efficient and much more secure. And everything would be nicely contained in the MSDB database. You can do so much more in the package, like checking to see if your source file actually exists before processing, moving the file to another directory after the processing is complete, or just deleting the file if you do not want to keep it. And you can easily schedule the package to fire off when needed.

Andrew SQLDBA


You can do that all through T-SQL as well. Yep... it requires the use of xp_CmdShell to do the moves but everything else can be done without xp_CmdShell. xp_CmdShell can also be used very securely if your system has proper security to begin with. If the system doesn't have proper security, then you might have some folks using it or a work around that you might not expect even if xp_CmdShell is disabled.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1437225
Posted Monday, April 1, 2013 7:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 4, Visits: 9
Hmmm, ok, how do I use SSIS then. I'll have to start learning that one. I am new to SQL so was just mucking my way through things. I'll see if I can't get it figured out.
Post #1437428
Posted Monday, April 1, 2013 1:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:36 PM
Points: 7,097, Visits: 12,600
I am with Andrew on this one. I would probably use SSIS for this as well. This would allow you to get rid of the stored procedure that calls BULK INSERT too.

The simplest way to get started with SSIS is to use the Import/Export Wizard to import your file into your table. The Import/Export Wizard is asking you questions, but in the background it is actually generating an SSIS Package on the fly. Towards the end of the Wizard you can choose to have the Wizard save the Package that is generated to disk so you can later open it in BIDS and see what was actually produced in terms of SSIS. This will give you a quick entry point into seeing what SSIS can do on a basic level and also gives you a starting point to create a more complex Package.

Once you have the basic Package the Wizard generates saved to disk you can open in BIDS and modify it to do additional things, e.g. searching a directory using a wildc*rd for files to import, or moving the file once to another directory after it has been imported...the sky is the limit.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1437597
Posted Monday, April 1, 2013 2:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 3,513, Visits: 7,565
I support the idea of using SSIS to have more options. You can start by reading the Stairway to Integration Services.
However, I wonder why would you use a bat file instead of a SQL Server job if you're only executing a stored procedure?



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1437612
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse