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

  • 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!

  • 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

  • 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?

  • 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

  • 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.

  • 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

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

  • 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

  • I support the idea of using SSIS to have more options. You can start by reading the Stairway to Integration Services[/url].

    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
  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply