question on sql

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ken - 26 Jan 2006 09:29 GMT

Dear all
1.)How can i save the stored procedure in sql 2005 server, into server and
NOT in file location

2.)How can create folder in stored procedure to separate categories?

3.)How can view the created date and modify date of stored pro

Reply to this Message

W.G. Ryan - MVP - 26 Jan 2006 15:43 GMT

> Dear all
> 1.)How can i save the stored procedure in sql 2005 server, into server and
> NOT in file location

--Do you want to just create a Stored Procedure via Code and not have to
read it in from a file, and then be able to retrieve it? If so, you can
just set your commandtext property of your command object to the stored proc
code, and you can use sp_helptext '[ @objname = ] 'name' [ , [ @columnname
= ] computed_column_name ] to retrieve the text afterward. If you use a
datareader for instance, you'd just set the CommandType to to
CommandType.StoredProcedure. Then you'd add the @objname parameter which
would be the name of the proc you just wrote. You can use a dataReader and
then use SqlDataReader dr = MyCommandName.ExecuteReader(and a
commandbehavior here as you normally would).

Then while(dr.Read()){
Debug.WriteLine(dr[0].ToString());
}//Or however you want the text, I'm just using WriteLine for illustrative
purposes

> 2.)How can create folder in stored procedure to separate categories?

--I'm not sure I follow you here. Are you looking for some sort of folder
structure with Management Studio? If so, I don't know of a way to
accomplish that (note though that I'm not saying it can't be done, I'm just
personally unaware of such a feature - but would like to know the answer as
well).

> 3.)How can view the created date and modify date of stored pro

--I believe you can check the system catalog and look at Sysobjects for
instance - looking at the crdate field. There is probably a new management
view as well that has this, I don't happen to know of one off the top of my
head. You may want to run this by the Sql Server NG group as well in that
there is probably a newer more elegant way to get at this data, I'm not
sure.

HTH,

Bill

Reply to this Message

William (Bill) Vaughn - 26 Jan 2006 16:58 GMT

1) I have no idea what you're talking about. "File location"?
2) SQL Server Management Studio (the 2005 version) does not support this. I
have asked for this feature but to no avail. It would be nice to be able to
organize SPs into categories.SSMS does support a "filter" setting that can
be used to show stored procedures that qualify for the filter. Another
technique is to use namespaces for the procedures. For example,
business_accounting_yearendprocessing. No, you can't use the "." separator.
You can/should organize your procedures by schema as well.
3) Yes crdate will tell you when the stored procedure was created, but each
object in SQL Server supports Extended Properties. These can be anything you
like. This means you could create a custom object management system to set
create date, date modified, person to blame or anything else as a property.
These can be read programmatically as well. See my articles on extended
stored procedures.

Signature

____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

>> Dear all
>> 1.)How can i save the stored procedure in sql 2005 server, into server
[quoted text clipped - 34 lines]
>
> Bill

Reply to this Message

question on sql

Free Magazines