listing contents of Store Procedures?
-
I don't know if the code I'm posting is enough, someone a while ago gave me the following code, basically it prints into a listbox the text from a stored procedure :-
sql = "select" & _
" text" & _
" from" & _
" syscomments where id in (select id from sysdepends where depid=" & lsttables.itemdata(i) & ")" & _
" and" & _
" id not in(select constid from sysconstraints where id =" & lsttables.itemdata(i) & ")"
Set rs5 = cn.Execute(sql)
If rs5.RecordCount > 0 Then
While Not rs5.EOF
List1.AddItem rs5("text")
rs5.MoveNext
wend
unfortunatly it does print all of it instead of getting
CREATE PROCEDURE spAdd_Memo @sMemo as VarChar(200), @sDate as VarChar(200),@sEnddate as varchar(200)
AS
INSERT INTO [Reminder] ([Date], [Reminder],[enddate])
VALUES
(@sDate,@sMemo,@sEnddate)
GO
I get
CREATE PROCEDURE spAdd_Memo @sMemo as VarChar(200), @sDate as VarChar(200),@sEnddate as varchar(200)
AS
INSERT INTO [Reminder] ([Date], [Reminder],[enddate])
VALUES
(@sDate,@sMemo,@sEnddate)
missing the final line ie GO
can someone tell me what's wrong please, I'm still new to SQL, many thanks in advance
-
Look at the query results in QA. The go is NOT SAVED in the table. You'll have to put one manullay if you need it.
-
it's not just Go that get's missed, I tried it again on another SP and I go
CREATE PROCEDURE spDeleteReminder @sMemo as VarChar(200),@sDate as varchar (200)
AS
instead of
CREATE PROCEDURE spDeleteReminder @sMemo as VarChar(200),@sDate as varchar (200)
AS
DELETE FROM [reminder] WHERE [reminder] = @sMemo and [date] = @sDate
GO
-
IS it possible that the text is in th elistbox, but not visisble to you?
Also do you have obejcts that containt more than 4000 characters (they will be saved in 2+ rows in syscomments).
-
I think the short answer to both questions is no, I used a debug.print to see if the text was there but hidden.
-
This works fine for me :
Sub a()
Dim sql As String
Dim rs5 As ADODB.Recordset
Set rs5 = New ADODB.Recordset
sql = "select" & _
" text" & _
" from" & _
" syscomments where id in (select id from sysdepends where depid=object_id('Client'))" & _
" and" & _
" id not in(select constid from sysconstraints where id =object_id('Client'))"
Set rs5 = MyCn.Execute(sql)
If rs5.RecordCount > 0 Then
While Not rs5.EOF
Debug.Print rs5("text")
Debug.Print "GO" & vbCrLf
rs5.MoveNext
Wend
End If
End Sub
Don't know what else to try
! -
I am at a loss, it's not just the fact that "GO" doesn't get printed, it's that sometimes whole lines get missed of too, I've even printed off quite large SP's but always minus 1 or 2 lines off the bottom, I don't think it's a character limit that's doing it.
Thanks anyway for trying
-
What results are you getting if you run the same queries from Query analyser?
-
I've not been able to get it to work at all in the QA
-
Try this :
select text from dbo.syscomments where id in (select id from sysdepends where depid = object_id('OneTableNameHere')) and id not in(select constid from sysconstraints where id =object_id('OneTableNameHere'))
Just supply one of your tables in the 2 spots with ''OneTableNameHere''.
-
That appears to work, so what is the difference? by the way, I'm very grateful for your patience
-
That appears to work, any idea why? thanks again for your patience
-
There are no difference in the select statement part. Only the tools to display the data changed. I wanted to make sure that the query returned the correct results!
Can you try concatenating the data to a big textbox to see if everything gets printed there?
-
I've alread tried that, but no luck
-
Short of going to your workplace I see nothing else I can do.
Good luck finding the solution to that problem
.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply