Need Help: Create Stored Procedure for Bulk Insert

Question

First, I'm new. I'm on SQL Server 2008 R2. I have a bulk insert script that works. I tried to create a stored procedure and then save it as a storedprocedure.sql. Ultimately, I need a .bat file to launch the bulk insert. I have another program that can run external programs but it only sees .bat or .exe.

So, here is my bulk insert script:

Bulk Insert MyDatabase.dbo.cust_adj
From 'C:\path\importformat.txt' 
With 
(
   FieldTerminator= '|', 
   Rowterminator= '\n' 
) 
Go

(this works)

I then tried to create the stored procedure:

Create Procedure LoadDailyAdjReport
AS
    Bulk Insert MyDatabase.dbo.cust_adj 
    From 'C:\path\importformat.txt' 
    With 
    ( 
       FieldTerminator= '|', 
       Rowterminator= '\n' 
    ) 
Go

It said it made it successfully... but I don't know how to check it.

So then I tried making a .bat file and tried:

@echo off
sqlcmd -S MyServer\MyDataBase -i C:\mypath\Load_Daily_Adjustment.sql

It doesn't work. At first I thought it was my .bat instructions (which probably aren't right) but then I wondered if my stored procedure was right. The problem is that I really don't know enough to troubleshoot this. Could someone please offer some advice or pointers on what this should be like?

Thank you ever so much.

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?

score 0 · Accepted Answer · edited May 23 '17 at 10:32

0

The SQL to execute an SP LoadDailyAdjReport would be EXEC LoadDailyAdjReport - so try this in ypur batch file SQLCMD :

sqlcmd -S YourServerName -E -d YourDataBaseName -Q "EXEC LoadDailyAdjReport"

(-E uses trusted connection (Windows login) more details here
http://msdn.microsoft.com/en-us/library/ms162773.aspx )

If you want to dabble with passing in the .txt filename as a parameter, see
How do I call a stored procedure with arguments using sqlcmd.exe?

edited May 23 '17 at 10:32

Community

1
1

answered Mar 29 '13 at 18:31

AjV Jsy

5,799
4
34
30

Excellent that .bat file works, now I just have to make sure my other program will successfully run that. Thank you so much! – SQL_Noob Mar 29 '13 at 19:34
Ok, I have another question now... I need to run the bat file from a different server... I edited my original question, but here it is: – SQL_Noob Mar 29 '13 at 20:47
Whatever the server specified in the sqlcmd, it will execute that SP on that server. – AjV Jsy Mar 29 '13 at 20:54
When I run the .bat file I get the following in my command prompt window: 'sqlcmd' is not recognized as an internal or external command, operable program or batch file. – SQL_Noob Mar 29 '13 at 21:35
see http://stackoverflow.com/questions/2290903/run-sqlcmd-without-having-sql-server-installed – AjV Jsy Mar 29 '13 at 21:37

Need Help: Create Stored Procedure for Bulk Insert

1 Answers1