Modifying a MSSQL stored procedure?

Forum Moderators: open

Message Too Old, No Replies

Modifying a MSSQL stored procedure?

I know basically nothing about stored procedures

beniro

10:20 pm on Dec 6, 2006 (gmt 0)

Hello.

I have a stored procedure that pulls a specified recordset from the DB. However, I added a field to the table and this field is not being returned in the recordset.

I don't know how to modify the stored procedure or even go in and see exactly what it is that the stored procedure is doing.

Any help would be greatly appreciated.

Thanks very much.

Ben

LifeinAsia

10:49 pm on Dec 6, 2006 (gmt 0)

Assuming you have access to Enterprise Manager, navigate to the database, then the Stored Procedures, then right-click on the stored procedure in question and select Properties. This will let you take a look at the stored procedure.

If all that's in there is a Select statement, then it should be a simple matter to just add the field to the select statement like you would with any query. (Before you start making any changes, make a backup of the original procedure- cut & paste everything into a text file as a minimum so you can quickly paste it back if you've mucked things up badly.)

If it's more complictaed than that, then contract with a SQL programmer or spend some time going through SQL Server Books Online (always a GREAT resource!).

FWIW, a little less than a year ago I didn't have a clue about stored procedures. Now I use them daily.

[edited by: LifeinAsia at 10:50 pm (utc) on Dec. 6, 2006]

beniro

4:09 pm on Dec 7, 2006 (gmt 0)

I do have the enterprise manager, however the DB is on a remote server, and I'm trying to find out how to add a remote server to the enterprise manager...so far, no luck.

beniro

4:24 pm on Dec 7, 2006 (gmt 0)

Hmmm, I tried using the Client Network Utility to config the connection, but when I subsequently try to add the connection to the enterprise manage,r it says I need to install SQL Server Manager or SMOS to connection. I'm still struggling with this...

beniro

4:32 pm on Dec 7, 2006 (gmt 0)

Well, what I ended up doing was to install MS SQL Server 2005 Express. That allowed me to connect and go to the stored procedure...right click and "modify".

Great! Plus, the app is free.

Thanks for pointing me in the right direction!

LifeinAsia

4:38 pm on Dec 7, 2006 (gmt 0)

Hmm, now that you mention it, I do seem to remember that adding connetions to remote servers is not very intuitive in the begining... It's been a while since I had to add one on a new install of Enterprise Manager, so I'd forgotten about that.

I haven't used 2005 yet, but it sounds like they've made things much easier. Thanks for the update!

So now that you've got access, how difficult does the actual code look?

beniro

5:11 pm on Dec 7, 2006 (gmt 0)

It was a fairly simple select statement, so I was able to update it and it works great. Next time around, I'll be able to work with these stored procedures much more easily.

I would recommend SQL Server 2005 Express if you're looking to try something new. It's interface is a bit...odd at first, but it gives quite an array of tools in one place.

Thanks again for your help.