Modifying the database SP in Patch

[sara_23apr]

Hello Friends

1) How do i write code/script to modify the Stored procedure once the database is up and running in client place.

2 )I want to write a patch so that the user double clicks it and the changes happen.

3) I know how to modify the database fields using SQL queries.... but how do i include this in the script ?

4 ) Can i do this is ASP.NET ?

Please Help
Sara

[kaffenils]

Hello Friends

1) How do i write code/script to modify the Stored procedure once the database is up and running in client place.

2 )I want to write a patch so that the user double clicks it and the changes happen.

3) I know how to modify the database fields using SQL queries.... but how do i include this in the script ?

4 ) Can i do this is ASP.NET ?

Please Help
Sara

1. Use the ALTER statement. You can generate ALTER scripts with SQL Server Management Studio. Just right-click a stored procedure and select Script As->Alter To->New Query Window.

2. You can create a program that the user can use to open and execute sql scripts, or you can simply teach him/her to use SQL Server Management Studio to execute the sql scripts.

3. You can include any kind of SQL statements in a script. UPDATE, INSERT, DELETE, CREATE, ALTER etc. The only thing you must keep in mind is that some statements, like CREATE and ALTER, cannot be combined with other statements in one batch. You must therefore separate each CREATE/ALTER script in a script file with the word GO

4. Sure it can. You can create a web page that allows you to upload a sql-script file and have the server execute it.

[szlamany]

We script all our SPROC creation - something that looks like this (and it's stored in a TEXT file ending with a .SQL extension).

Use Funds
 
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetImageInfo_P]') 
    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetImageInfo_P]
GO
 
Create Procedure GetImageInfo_P
as
 
Select ConfItem,ConfData From Funds_T Where Left(ConfItem,5)='Image'
 
GO
GRANT EXEC ON GetImageInfo_P to FundsUser
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

These .SQL files can be executed many ways - one way is to run them manually in a QUERY window (Query Analyzer or Management Studio).

Also - there is a command line OSQL command that will load the SPROC into the database - you can place these commands in a .BAT file and the user can execute that file:

osql /E /S SERVER03 /i GetImageInfo_P.sql /o log_GetImageInfo_P.sql.txt

You can also run a statement like this from within your APP - after finding all the .SQL files that need to be re-loaded in a staging folder, for instance.

[sara_23apr]

Hi kaffenils and szlamany

Thanks for the reply

I am off the work today... will try this tommarrow and get back

Regards
Sara

[sara_23apr]

Hi szlamany and kaffenils
Your replies were very helpful and informative.

I have created the .Sql file and now working on executing the .sql file from asp.net using ExecuteNonQuery()

Thank you

Sara