March 29, 2013 at 11:09 am
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!
March 29, 2013 at 11:18 am
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.
March 29, 2013 at 2:45 pm
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?
March 29, 2013 at 3:46 pm
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?
March 29, 2013 at 3:56 pm
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.
March 30, 2013 at 5:04 am
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
March 30, 2013 at 1:00 pm
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
Change is inevitable... Change for the better is not.
April 1, 2013 at 7:17 am
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.
April 1, 2013 at 1:51 pm
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
April 1, 2013 at 3:27 pm
elg (4/1/2013)
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.
The only problem that you're having is how you're tring to call the proc, right? While I agree that SSIS is a good thing to learn, if I understood more of why you're trying to call the proc from a .bat file, we could solve the short term problem quickly.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply