|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
question on sqlDear 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" <k**@discussions.microsoft.com> wrote in message --Do you want to just create a Stored Procedure via Code and not have to news:DD1922C8-2915-41B1-834D-DB17BC8020CD@microsoft.com... > Dear all > 1.)How can i save the stored procedure in sql 2005 server, into server and > NOT in file location 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 > --I'm not sure I follow you here. Are you looking for some sort of folder > 2.)How can create folder in stored procedure to separate categories? 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). > --I believe you can check the system catalog and look at Sysobjects for > 3.)How can view the created date and modify date of stored pro 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 Show quote > > > > 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. -- Show quote____________________________________ 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" <WilliamRyan@nospam.gmail.com> wrote in message news:uKOWe8oIGHA.3936@TK2MSFTNGP10.phx.gbl... > > "ken" <k**@discussions.microsoft.com> wrote in message > news:DD1922C8-2915-41B1-834D-DB17BC8020CD@microsoft.com... >> 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 >> >> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics