Stored Procedures

[YL5956]

Does anyone know how to store a new stored procedured in a particular database through scripts or batch files? We want our RCM tool to handle all our SQL Server updates this includes storing new proceduresm tables. etc. How can I accomplish this for example I have a new procedure test that I want to store into my_database. How can I store this in the appropriate database without going through enterprise manager? I guess in another words how can I envoke SQL Server through our RCM tool? If you have a sample that would be awesome. Thanks for all your help.

 

[tlbroadbent]

Can you execute SQL stements with you RCM tool? If so, you can execute SQL statements that create stored procedures. You just need to open a connection to the database and execute your script. The login must be a DB owner or have DDL Admin permissions.

Example:

Create Procedure TestProc
@varin int
As

Select *
From table1
Where colA=@varin
And ColC<Dateadd(day,-7,getdate()) Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[YL5956]

I'm kind of confused. Let say I want to store the procedure in database A...now I'm not familiar with SQL Server Architecture but where would I store it and how? Thanks.

 

[tlbroadbent]

You create a stored procedure (SP) and SQL handles the storage in its system tables. Every database has a set of system tables where database schema information is stored. Therefore if you want to create SP in database A, you simply open database A and run the Create Procedure statement. SQL Server then updates all the appropriate system tables. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[grahamm]

Put the stored procedure definitions in a file (e.g sp.sql) and use isql or osql. For example in a .bat file you might write:

osql /S %system% /U%user% /P%password% /d%DB% /n /i sp.sql