question on sql

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
ken said:
Dear all
1.)How can i save the stored procedure in sql 2005 server, into server and
NOT in file location
Click to expand...
--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?
Click to expand...
--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
Click to expand...
--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
 
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.

--
____________________________________
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.
__________________________________

W.G. Ryan - MVP said:
ken said:
Dear all
1.)How can i save the stored procedure in sql 2005 server, into server
and
NOT in file location
Click to expand...
--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?
Click to expand...
--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
Click to expand...
--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
Click to expand...
 
Back
Top