Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: Creating Procedures from file and .net

    I am trying to read a SP.sql file where i want to write all the procedures to create them in the database

    CREATE PROCEDURE SP_1
    AS
    BEGIN
    DELETE FROM tb1
    END

    CREATE PROCEDURE SP_2
    AS
    BEGIN
    DELETE FROM tb2
    END


    if there is only one procedure in the file it works, with > 1 procedure it fails
    inserting GO between doesnt work

    how is it possible to run a sql file with many stored procedures in it from a server langages ?

    thank you

  2. #2
    Join Date
    Aug 2006
    Posts
    7
    I tried with OSQL with GO between the two proc creation scripts , it works fine.

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    no it doesnt work for me, from SQL SERVER Management studio of course it works
    but reading/executing a file from a server langage doesnt work in that way

    OSQL is not a server langage

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Hariarul1
    I tried with OSQL with GO between the two proc creation scripts , it works fine.
    Hariarul1 is right,you should look into BOL first to use OSQL utility.
    Check this..
    Code:
    EXEC master..xp_cmdshell 'osql -S Servername -U sa -P -ic:\SQLScript.sql'
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    i just want to run from asp.net application an .SQL file to create stored procedure and views
    is there any way to have in the same sql file many procedures (as my exemple shows) ?

    i am not going to use another tool, if noy possible then i can run them one by one in the code , but will be fine to get all at the same place

    thank you

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by anselme
    i just want to run from asp.net application an .SQL file to create stored procedure and views
    is there any way to have in the same sql file many procedures (as my exemple shows) ?

    i am not going to use another tool, if noy possible then i can run them one by one in the code , but will be fine to get all at the same place

    thank you
    OSql utility is given along with sql server so you need not have to install it seperately,just execute that above sql statement in your ASP.net application,thats all..you will get what you want...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  7. #7
    Join Date
    Dec 2005
    Posts
    266
    ok then i must look how it works

    i am creating all the tables from aspnet aplication and i cannot creature procedures , one yes, 2 not !!!

    that sound crazy


    thanks a lot

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Your query is failing because CREATE PROC must be the first statement in a batch. in osql, the GOs separate batches.

    GO is not a keyword in sql however. it's understood only by osql.exe and query analyzer to separate batches (sqlcmd.exe and ssms in 2005 recognize GO as well). The sql server engine itself does not recognize this keyword though, so you can't have GOs in the sql that you send to the server using SqlClient, for example (I assume you are using SqlClient since you are using .net).

    If you have SMO installed however, you can also use the ServerConnection class to execute scripts from code that are separated by GOs. Use the ExecuteNonQuery() method and friends. If you use this class, you don't have to launch osql.exe as a separate process, and you don't have to use xp_cmdshell (which you should never use if you can get away with it. using it opens up some pretty big security holes).

    See this page for details: http://msdn2.microsoft.com/en-us/lib...enonquery.aspx

    Note that SMO is installed by default if you have the 2005 client tools installed (SSMS). also you can download SMO and install it separately here:

    http://www.microsoft.com/downloads/d...displaylang=en (search for “SMO” on that page.)

    SMO will work against any 2000 or 2005 server.
    Last edited by jezemine; 09-26-06 at 15:58.

  9. #9
    Join Date
    Dec 2005
    Posts
    266
    thank you jezemine
    i run of course all my create procedure one by one from a class and execute command
    but i find very practicle to have them outside, not hard coded
    i am using a xml file

    thank you to every body

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •