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

Creating Outlook calendar

We have a database in application in SQL 2000 Server (Standard). We want to
configure a table so that when there is add, delete, or update in the table,
an Outlook calendar of a user should automatically be updated. The user is a
member of the same domain as the service account and as the Windows Server
2003 (Standard) that hosts the database. The user's mailbox is hosted by
Exchange Server 2003 Enterprise in the same domain. I am not sure if SQL can
do this, or if we have to use a middleware such as VB.NET or C#.
Please help. Thanks!
ODHi OD,
Its a pitty you aren't using SQL Server 2005, its a perfect job for CLR.
But, yes, you do need to code this outside of SQL Server.
In SQL you will need to catch the 'add', 'delete' or 'update'; this is best
done using a trigger. You can then operator a table like a queue, the
trigger writes into the table and then your VB.NET / C# application/service
reads the queue and does the biz in Exchange.
The VB.NET to do the add appointment bit is below...
Public Sub Exchange_AddAppointment(ByRef page As Page, ByVal iEventID As
Integer)
' Variables
Dim strExchSvrName As String
Dim strMailbox As String
Dim strCalendarUri As String
Dim strApptItem As String
Dim strDomain As String
Dim strUserName As String
Dim strPassword As String
Dim strApptRequest As String
Dim strMailInfo As String
Dim strCalInfo As String
Dim strXMLNSInfo As String
Dim strHeaderInfo As String
Dim PROPPATCHRequest As System.Net.HttpWebRequest
Dim PROPPATCHResponse As System.Net.WebResponse
Dim MyCredentialCache As System.Net.CredentialCache
Dim bytes() As Byte
Dim PROPPATCHRequestStream As System.IO.Stream
' Exchange server name
strExchSvrName = ConfigurationSettings.AppSettings("ExchangeServer")
' Mailbox folder name.
strMailbox = "tonyrogerson"
' Appointment item.
strApptItem = "CRMEvent.eml"
' URI of the user's calendar folder.
strCalendarUri = "http://" & strExchSvrName & "/exchange/" & _
strMailbox & "/Calendar/"
' User name and password of appointment creator.
strUserName =
ConfigurationSettings.AppSettings("CRMExchangeLogonName")
strDomain =
ConfigurationSettings.AppSettings("CRMExchangeLogonDomain")
strPassword =
ConfigurationSettings.AppSettings("CRMExchangeLogonPWD")
' XML namespace info for the WebDAV request.
strXMLNSInfo = "xmlns:g=""DAV:"" " & _
"xmlns:e=""http://schemas.microsoft.com/exchange/"" " & _
"xmlns:mapi=""http://schemas.microsoft.com/mapi/"" " & _
"xmlns:mapit=""http://schemas.microsoft.com/mapi/proptag/"" " & _
"xmlns:x=""xml:"" xmlns:cal=""urn:schemas:calendar:"" " & _
"xmlns:dt=""urn:uuid:c2f41010-65b3-11d1-a29f-00aa00c14882/"" " &
_
"xmlns:header=""urn:schemas:mailheader:"" " & _
"xmlns:mail=""urn:schemas:httpmail:"""
' Set the appointment item properties. See the documentation on the
properties
' in the urn:schemas:calendar: for more information.
strCalInfo = "<cal:location>meetappt Location</cal:location>" & _
"<cal:dtstart
dt:dt=""dateTime.tz"">2004-12-19T23:00:00.000Z</cal:dtstart>" & _
"<cal:dtend
dt:dt=""dateTime.tz"">2004-12-19T23:30:00.000Z</cal:dtend>" & _
"<cal:instancetype dt:dt=""int"">0</cal:instancetype>" & _
"<cal:busystatus>BUSY</cal:busystatus>" & _
"<cal:meetingstatus>CONFIRMED</cal:meetingstatus>" & _
"<cal:alldayevent dt:dt=""boolean"">0</cal:alldayevent>" & _
"<cal:responserequested
dt:dt=""boolean"">1</cal:responserequested>"
' Set the required attendee of the appointment.
strHeaderInfo = "<header:to>" & strMailbox & "</header:to>"
' Set the subject of the appointment.
strMailInfo = "<mail:subject>Test Appointment
Subject</mail:subject>" & _
"<mail:htmldescription>Let's meet here</mail:htmldescription>"
' Build the XML body of the PROPPATCH request.
strApptRequest = "<?xml version=""1.0""?>" & _
"<g:propertyupdate " & strXMLNSInfo & ">" & _
"<g:set><g:prop>" & _
"<g:contentclass>urn:content-classes:appointment</g:contentclass>"
& _
"<e:outlookmessageclass>IPM.Appointment</e:outlookmessageclass>"
& _
strMailInfo & _
strCalInfo & _
strHeaderInfo & _
"<mapi:finvited dt:dt=""boolean"">1</mapi:finvited>" & _
"</g:prop></g:set>" & _
"</g:propertyupdate>"
Try
' Create a new CredentialCache object and fill it with the
network
' credentials required to access the server.
MyCredentialCache = New System.Net.CredentialCache
MyCredentialCache.Add(New System.Uri(strCalendarUri), _
"NTLM", _
New
System.Net.NetworkCredential(strUserName, strPassword, strDomain) _
)
' Create the HttpWebRequest object.
PROPPATCHRequest =
CType(System.Net.HttpWebRequest.Create(strCalendarUri & strApptItem), _
System.Net.HttpWebRequest)
' Add the network credentials to the request.
PROPPATCHRequest.Credentials = MyCredentialCache
' Specify the PROPPATCH method.
PROPPATCHRequest.Method = "PROPPATCH"
' Set the content type header.
PROPPATCHRequest.ContentType = "text/xml"
' Encode the body using UTF-8.
bytes = System.Text.Encoding.UTF8.GetBytes(strApptRequest)
' Set the content header length. This must be
' done before writing data to the request stream.
PROPPATCHRequest.ContentLength = bytes.Length
' Get a reference to the request stream.
PROPPATCHRequestStream = PROPPATCHRequest.GetRequestStream()
' Write the message body to the request stream.
PROPPATCHRequestStream.Write(bytes, 0, bytes.Length)
' Close the Stream object to release the connection
' for further use.
PROPPATCHRequestStream.Close()
' Create the appointment in the Calendar folder of the
' user's mailbox.
PROPPATCHResponse = CType(PROPPATCHRequest.GetResponse(),
System.Net.HttpWebResponse)
page.Response.Write("Appointment successfully created.")
' Clean up.
PROPPATCHResponse.Close()
Catch ex As Exception
' Catch any exceptions. Any error codes from the PROPPATCH
' or MOVE method requests on the server will be caught
' here, also.
page.Response.Write(ex.Message)
End Try
End Sub
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"OD" <oludan@.hotmail.com> wrote in message
news:uwDuOrmJGHA.3224@.TK2MSFTNGP09.phx.gbl...
> We have a database in application in SQL 2000 Server (Standard). We want
> to configure a table so that when there is add, delete, or update in the
> table, an Outlook calendar of a user should automatically be updated. The
> user is a member of the same domain as the service account and as the
> Windows Server 2003 (Standard) that hosts the database. The user's mailbox
> is hosted by Exchange server 2003 Enterprise in the same domain. I am not
> sure if SQL can do this, or if we have to use a middleware such as VB.NET
> or C#.
> Please help. Thanks!
> OD
>|||you can do it in TSQL by refering to the activex of outlook or directly
acessing exchange via activex api if there is any. Or u can make an activex
to perform this task and call it via ur TSQL in trigger. Or u can implement
an extended stored procedure. See u have got too many options. Choose which
looks suitable.
Cheers
"OD" <oludan@.hotmail.com> wrote in message
news:uwDuOrmJGHA.3224@.TK2MSFTNGP09.phx.gbl...
> We have a database in application in SQL 2000 Server (Standard). We want
> to configure a table so that when there is add, delete, or update in the
> table, an Outlook calendar of a user should automatically be updated. The
> user is a member of the same domain as the service account and as the
> Windows Server 2003 (Standard) that hosts the database. The user's mailbox
> is hosted by Exchange server 2003 Enterprise in the same domain. I am not
> sure if SQL can do this, or if we have to use a middleware such as VB.NET
> or C#.
> Please help. Thanks!
> OD
>|||I wouldn't recommend activeX inside a trigger going out of SQL, remember the
trigger is in a transaction and the longer that transaction exists the
higher the chances of blocking; also, if the ActiveX fails it could be
problematic.
Make the trigger write the data out into another table or using a flag on
the base table and use ActiveX as you say.
tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Mubashir Khan" <m@.n.com> wrote in message
news:uJGGLmnJGHA.140@.TK2MSFTNGP12.phx.gbl...
> you can do it in TSQL by refering to the activex of outlook or directly
> acessing exchange via activex api if there is any. Or u can make an
> activex to perform this task and call it via ur TSQL in trigger. Or u can
> implement an extended stored procedure. See u have got too many options.
> Choose which looks suitable.
> Cheers
> "OD" <oludan@.hotmail.com> wrote in message
> news:uwDuOrmJGHA.3224@.TK2MSFTNGP09.phx.gbl...
>|||First consider if you really want to hit Exchange server *every* time one of
these tables are inserted, updated or deleted, and then estimate how many
hits per day this would be. I would reccomend having a trigger insert or
update a row in an audit table whenever a relevent event on the table takes
place. If data modifications are so important that they warrant
automatically scheduling someone's calendar in Outlook, then you may want to
retain an audit history in this table anyway. A DTS package can then poll
this audit table and execute a VBScript task that makes the calls to
Exchange Server, and the DTS package can be scheduled as job to run at
specific internals. There are tons of information on MSDN about how to
script Exchange Server.
http://www.microsoft.com/technet/sc...ap.msp
x
"OD" <oludan@.hotmail.com> wrote in message
news:uwDuOrmJGHA.3224@.TK2MSFTNGP09.phx.gbl...
> We have a database in application in SQL 2000 Server (Standard). We want
> to configure a table so that when there is add, delete, or update in the
> table, an Outlook calendar of a user should automatically be updated. The
> user is a member of the same domain as the service account and as the
> Windows Server 2003 (Standard) that hosts the database. The user's mailbox
> is hosted by Exchange server 2003 Enterprise in the same domain. I am not
> sure if SQL can do this, or if we have to use a middleware such as VB.NET
> or C#.
> Please help. Thanks!
> OD
>|||Great information! I will tryout the code you supplied. This is very
helpful. I hope to migrate to SQL server 2005 in a by summer.
Thanks you again
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uwjS2inJGHA.1760@.TK2MSFTNGP10.phx.gbl...
> Hi OD,
> Its a pitty you aren't using SQL Server 2005, its a perfect job for CLR.
> But, yes, you do need to code this outside of SQL Server.
> In SQL you will need to catch the 'add', 'delete' or 'update'; this is
> best done using a trigger. You can then operator a table like a queue, the
> trigger writes into the table and then your VB.NET / C#
> application/service reads the queue and does the biz in Exchange.
> The VB.NET to do the add appointment bit is below...
> Public Sub Exchange_AddAppointment(ByRef page As Page, ByVal iEventID
> As Integer)
>
> ' Variables
> Dim strExchSvrName As String
> Dim strMailbox As String
> Dim strCalendarUri As String
> Dim strApptItem As String
> Dim strDomain As String
> Dim strUserName As String
> Dim strPassword As String
> Dim strApptRequest As String
> Dim strMailInfo As String
> Dim strCalInfo As String
> Dim strXMLNSInfo As String
> Dim strHeaderInfo As String
> Dim PROPPATCHRequest As System.Net.HttpWebRequest
> Dim PROPPATCHResponse As System.Net.WebResponse
> Dim MyCredentialCache As System.Net.CredentialCache
> Dim bytes() As Byte
> Dim PROPPATCHRequestStream As System.IO.Stream
>
> ' Exchange server name
> strExchSvrName =
> ConfigurationSettings.AppSettings("ExchangeServer")
>
> ' Mailbox folder name.
> strMailbox = "tonyrogerson"
>
> ' Appointment item.
> strApptItem = "CRMEvent.eml"
>
> ' URI of the user's calendar folder.
> strCalendarUri = "http://" & strExchSvrName & "/exchange/" & _
> strMailbox & "/Calendar/"
>
> ' User name and password of appointment creator.
> strUserName =
> ConfigurationSettings.AppSettings("CRMExchangeLogonName")
> strDomain =
> ConfigurationSettings.AppSettings("CRMExchangeLogonDomain")
> strPassword =
> ConfigurationSettings.AppSettings("CRMExchangeLogonPWD")
>
> ' XML namespace info for the WebDAV request.
> strXMLNSInfo = "xmlns:g=""DAV:"" " & _
> "xmlns:e=""http://schemas.microsoft.com/exchange/"" " & _
> "xmlns:mapi=""http://schemas.microsoft.com/mapi/"" " & _
> "xmlns:mapit=""http://schemas.microsoft.com/mapi/proptag/"" " &
> _
> "xmlns:x=""xml:"" xmlns:cal=""urn:schemas:calendar:"" " & _
> "xmlns:dt=""urn:uuid:c2f41010-65b3-11d1-a29f-00aa00c14882/"" " &
> _
> "xmlns:header=""urn:schemas:mailheader:"" " & _
> "xmlns:mail=""urn:schemas:httpmail:"""
>
> ' Set the appointment item properties. See the documentation on
> the properties
> ' in the urn:schemas:calendar: for more information.
> strCalInfo = "<cal:location>meetappt Location</cal:location>" & _
> "<cal:dtstart
> dt:dt=""dateTime.tz"">2004-12-19T23:00:00.000Z</cal:dtstart>" & _
> "<cal:dtend
> dt:dt=""dateTime.tz"">2004-12-19T23:30:00.000Z</cal:dtend>" & _
> "<cal:instancetype dt:dt=""int"">0</cal:instancetype>" & _
> "<cal:busystatus>BUSY</cal:busystatus>" & _
> "<cal:meetingstatus>CONFIRMED</cal:meetingstatus>" & _
> "<cal:alldayevent dt:dt=""boolean"">0</cal:alldayevent>" & _
> "<cal:responserequested
> dt:dt=""boolean"">1</cal:responserequested>"
>
> ' Set the required attendee of the appointment.
> strHeaderInfo = "<header:to>" & strMailbox & "</header:to>"
>
> ' Set the subject of the appointment.
> strMailInfo = "<mail:subject>Test Appointment
> Subject</mail:subject>" & _
> "<mail:htmldescription>Let's meet here</mail:htmldescription>"
>
> ' Build the XML body of the PROPPATCH request.
> strApptRequest = "<?xml version=""1.0""?>" & _
> "<g:propertyupdate " & strXMLNSInfo & ">" & _
> "<g:set><g:prop>" & _
>
> "<g:contentclass>urn:content-classes:appointment</g:contentclass>" & _
> "<e:outlookmessageclass>IPM.Appointment</e:outlookmessageclass>"
> & _
> strMailInfo & _
> strCalInfo & _
> strHeaderInfo & _
> "<mapi:finvited dt:dt=""boolean"">1</mapi:finvited>" & _
> "</g:prop></g:set>" & _
> "</g:propertyupdate>"
>
> Try
> ' Create a new CredentialCache object and fill it with the
> network
> ' credentials required to access the server.
> MyCredentialCache = New System.Net.CredentialCache
> MyCredentialCache.Add(New System.Uri(strCalendarUri), _
> "NTLM", _
> New
> System.Net.NetworkCredential(strUserName, strPassword, strDomain) _
> )
> ' Create the HttpWebRequest object.
> PROPPATCHRequest =
> CType(System.Net.HttpWebRequest.Create(strCalendarUri & strApptItem), _
> System.Net.HttpWebRequest)
>
> ' Add the network credentials to the request.
> PROPPATCHRequest.Credentials = MyCredentialCache
>
> ' Specify the PROPPATCH method.
> PROPPATCHRequest.Method = "PROPPATCH"
>
> ' Set the content type header.
> PROPPATCHRequest.ContentType = "text/xml"
>
> ' Encode the body using UTF-8.
> bytes = System.Text.Encoding.UTF8.GetBytes(strApptRequest)
>
> ' Set the content header length. This must be
> ' done before writing data to the request stream.
> PROPPATCHRequest.ContentLength = bytes.Length
>
> ' Get a reference to the request stream.
> PROPPATCHRequestStream = PROPPATCHRequest.GetRequestStream()
>
> ' Write the message body to the request stream.
> PROPPATCHRequestStream.Write(bytes, 0, bytes.Length)
>
> ' Close the Stream object to release the connection
> ' for further use.
> PROPPATCHRequestStream.Close()
>
> ' Create the appointment in the Calendar folder of the
> ' user's mailbox.
> PROPPATCHResponse = CType(PROPPATCHRequest.GetResponse(),
> System.Net.HttpWebResponse)
>
> page.Response.Write("Appointment successfully created.")
>
> ' Clean up.
> PROPPATCHResponse.Close()
>
> Catch ex As Exception
> ' Catch any exceptions. Any error codes from the PROPPATCH
> ' or MOVE method requests on the server will be caught
> ' here, also.
> page.Response.Write(ex.Message)
>
> End Try
>
> End Sub
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:uwDuOrmJGHA.3224@.TK2MSFTNGP09.phx.gbl...
>|||Thank you very much guys. I know now, that I have a few option. I need to
complete this task in two ws. This solution will be used to schedule our
tecnicians when a user/clients enter a task requests. So it may be necessary
to touch Exchange server at least every time there is an insert or delete.
Thanks again.
OD
"JT" <someone@.microsoft.com> wrote in message
news:uVWETSoJGHA.648@.TK2MSFTNGP14.phx.gbl...
> First consider if you really want to hit Exchange server *every* time one
> of these tables are inserted, updated or deleted, and then estimate how
> many hits per day this would be. I would reccomend having a trigger insert
> or update a row in an audit table whenever a relevent event on the table
> takes place. If data modifications are so important that they warrant
> automatically scheduling someone's calendar in Outlook, then you may want
> to retain an audit history in this table anyway. A DTS package can then
> poll this audit table and execute a VBScript task that makes the calls to
> Exchange Server, and the DTS package can be scheduled as job to run at
> specific internals. There are tons of information on MSDN about how to
> script Exchange Server.
> http://www.microsoft.com/technet/sc...ap.m
spx
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:uwDuOrmJGHA.3224@.TK2MSFTNGP09.phx.gbl...
>|||

quote:

---

Originally posted by Tony Rogerson
Hi OD,
Its a pitty you aren't using SQL Server 2005, its a perfect job for CLR.>

---

Could you help out on how you would acomplish this using the SQL 2005 CLR'
Oz

creating jobs in SQL Server 2005

Hello

I am using SQL Server 2005
I want to delete the data from my table after specific number of days.

This should happen automatically.

After the days I specified the data from the table should automatically get deleted. Can anyone let me know how to achieve this.

If anyone can give an example for this it will be great.

Thanks in advance.

Hi,
As the title of your post suggests you've already identified that you need a job running on the server to facilitate this.

SQL Server Agent will allow jobs to be scheduled and run - read up about this feature of SQL Server if unsure.

As for how to add a job...

In SQL Server Management Studio connect to the server.