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 youI tried with OSQL with GO between the two proc creation scripts , it works fine.|||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|||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..

EXEC master..xp_cmdshell 'osql -S Servername -U sa -P -ic:\SQLScript.sql'|||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|||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...|||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|||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/library/microsoft.sqlserver.management.common.serverconnec tion.executenonquery.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/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en (search for SMO on that page.)

SMO will work against any 2000 or 2005 server.|||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