Updating Stored Procs from code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Updating Stored Procs from code

Author

Topic

ward0093
Starting Member

15 Posts

Posted - 2006-02-14 : 09:33:30

I have an SSE system/database with a WinForms (VB.NET) application front end... i am just wondering how I can update a stored procedure in the local SSE database from my application?

This is a real big issue for us and our automated deployment.

Any help would be great!!!!!!

ward0093

mikewa
Microsoft SQL Server Product Team

84 Posts

Posted - 2006-02-14 : 12:26:47

You can update a stored procedure with the ALTER PROCEDURE T-SQL statement. From VB.NET you would likely use an ADO.NET Command object to execute your SQL Statment. Check out http://msdn2.microsoft.com/en-us/library/ms254953(VS.80).aspx for information about the Command object.

Regards,
Mike Wachal
SQL Express

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

ward0093
Starting Member

15 Posts

Posted - 2006-02-14 : 12:38:29

Correct... so are you just saying that I should read in the entire Script contents (everthing from ALTER STORED PROCEDURE... TO THE "END") into the CommandText property? Is that Safe to do? Seems to simple... or maybe I am just reading into this too much!

what do you think?
ward0093

mikewa
Microsoft SQL Server Product Team

84 Posts

Posted - 2006-02-15 : 00:15:19

Hi Ward,

Yep, you pretty much re-write the whole procedure in most cases when you're doing an ALTER.

Regards,
Mike Wachal
SQL Express

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

ward0093
Starting Member

15 Posts

Posted - 2006-02-15 : 10:22:14

Thank you so much!

A few quick questions... isn't there a Char Limit on the CommandText Property of a SqlCommand Object? I mean some of my procedures are pretty large or I would like to wrap them all up inside one file or one command?

Also... do you have any suggestions on how to get the Procedure Contents into the CommandText property? i.e. should I read a file into a stream and place that in the commandtext property?

Thanks for all your help

ward0093

mikewa
Microsoft SQL Server Product Team

84 Posts

Posted - 2006-02-15 : 11:49:15

Hi Ward,

This has pretty much exhausted my knowledge of writing managed code. About all I can suggest is that you search around on MSDN2 for information about how to accomplish what you want. I looked at the topic for the SqlCommand object and it didn't mention what the size limit is.

I can also suggest the .NET Data Access forum on MSDN as a good place to ask questions specific to ADO.NET and the System.Data Namespace.

Regards,
Mike Wachal
SQL Express

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

ward0093
Starting Member

15 Posts

Posted - 2006-02-15 : 12:10:42

Ya... I know what you mean!

I have been searching around but so far our conversation is the most information I could find on it!

I will check out that forum and see if I can gather more information.

Thanks for all your help,
ward0093

ward0093
Starting Member

15 Posts

Posted - 2006-03-01 : 16:10:20

Hey Mike!

IT WORKED!!!

I took my entire Collection of Stored Procs and Functions and "Generated a Create Script" in Visual Studio 2005 from the Development Database I am using... basically it gave me one/single large file with all the scripts to create ALL the SPs and UDFs...

I had to remove all teh "GO" statements (the only issue I ran into)

I created a ADO.NET Connection and a ADO.NET Command Object and set the SqlCommand.CommandText property to the contents of the File!.... and sent it to the database! Worked SLICK!!!

I am sure we can do the same thing with Table Struture Changes (ALTER TABLE) scripts as well!

Anyways... developed some Database versioning technique and a big piece of the puzzle is solved!...

now the only issue is User Security... I might have openned up the user ability/functionality too much... need to look into it more...

Just thought you might want to know..
ward0093

mikewa
Microsoft SQL Server Product Team

84 Posts

Posted - 2006-03-01 : 23:13:14

Glad to hear it Ward! Thanks for confirming your success.

Regards,
Mike Wachal
SQL Express

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources