Getting a store procedure

Results 1 to 2 of 2

Thread: Getting a store procedure

  1. #1
    Join Date
    Dec 1969

    Default Getting a store procedure

    Hello, I want to retrive and edit a store procedure. My problem is this, I have no access to sql server. I am going through a web interface where you can simply type sql commands.<BR><BR>Is it possible to retrive a stored procedure and edit it?<BR><BR>Thanks

  2. #2
    Join Date
    Dec 1969

    Default RE: Getting a store procedure

    I was able to execute the following from the VI sql window:<BR><BR>SELECT, syscomments.text<BR>FROM sysobjects <BR>JOIN syscomments <BR>ON =<BR>WHERE sysobjects.type = &#039;P&#039;<BR><BR>note: syscomments.text is the actual text of each stored procedure returned.<BR><BR>To update the stored procedure you have to use ALTER instead of CREATE command in the procedure definition. I don&#039;t see why you can&#039;t just define a string with the procedure text and then use,<BR><BR>conn.execute(sql) to update the procedure. <BR><BR>If you have a text box or text area into which to enter sql, simply paste the procedure text in there. I&#039;m going to get into this quite soon, so until then, you&#039;ll need to experiement.<BR><BR>I was looking into getting the above info using system stored procedures (sp_stored_procedures) but it didn&#039;t return the procedure text.<BR><BR>I also tried somehing like,<BR><BR>select *<BR>from information_schema.routines<BR><BR>but in VI, I got an error saying information_schema.routines is not a valid object. On the other hand, this works:<BR><BR>select *<BR>from information_schema.columns<BR><BR>If anyone had functher info, I&#039;ll greatly appreciate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts