SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help with a .bat for a stored procedure, please.


Need help with a .bat for a stored procedure, please.

Author
Message
elg
elg
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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!
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1638 Visits: 8586
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
elg
elg
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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?
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1638 Visits: 8586
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
elg
elg
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
AndrewSQLDBA
AndrewSQLDBA
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1874 Visits: 3427
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91094 Visits: 41151
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
elg
elg
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15647 Visits: 14396
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
Luis Cazares
Luis Cazares
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17442 Visits: 19122
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search