EXEC a Stored Proc 1

[jon24422531]

Hi Guys

I am really struggling with this one and I suspect that I am going round in circles and missing something simple:

On my page I run this Stored Proc:

strsql = "EXEC JSp_DevLogFrameDetail '" & sCode & "'"

rsData.Open strSQL, MyConn
returns = rsData.GetRows(1,0)

And the error I get is:

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
/WishListDevelopment/DevOpenFRAMEDetail.asp, line 46

I have had this working on another page (in a slightly different format, so I am really stumped.

Jonathan

http://www.telecom-stuff.com

 

[g0ste]

try

strsql = "JSp_DevLogFrameDetail '" & sCode & "'"
myConnection.execute(strsql)
[COLOR=green]'assumes you have opened a connection
'called "myConnection"[/color]

________________________________
Top 10 reasons to procrastinate:
1)

 

[jon24422531]

g0ste

Thanks for the quick response. I now have another error:

Response object error 'ASP 0185 : 8002000e'
Missing Default Property
/WishListDevelopment/DevOpenFRAMEDetail.asp, line 0
A default property was not found for the object.

rsData = MyConn.execute(strsql)

I was trying to use the GetRows command too, but one error said it was not supported. I can work round that if only I could get the data returned....

It does work in Query Analyser (with the Print command)

Jonathan

http://www.telecom-stuff.com

 

[g0ste]

I assume you are selecting data using the sp?

If you are, you need to use [tt]set[/tt] your record set, like:

SET rsData = MyConn.execute(strsql)

then you can use [tt]rsData("colName")[/tt] etc where you like.

however, a quick search shows the [tt]8002000e[/tt] error is when you try to [tt]response.write[/tt] an object, or are using a reserved variable name.

give it a whirl and let us know how you get on!

________________________________
Top 10 reasons to procrastinate:
1)

 

[gmmastros]

If run in to this problem many times in the forums. The solution is to modify your stored procedure to include the line:

SET NOCOUNT ON

Put this as the first line within the procedure. Ex:

Create Procedure JSp_DevLogFrameDetail @Code VarChar(10)
AS
[!]SET NOCOUNT ON[/!]
[green]-- The rest of your code here[/green]

When you have a procedure with multiple statements, you actually get multiple recordsets returned. By setting no count on, the first one is suppressed and you get your data exactly as you expect to.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jon24422531]

George

I have added that directly to the Stored Proc (I had added it to the asp code before)

Now I get this:
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
/WishListDevelopment/DevOpenFRAMEDetail.asp, line 28

This is the code:

<%
Dim MyConn
Dim rsData
Dim strSQL
Dim objNTInfo
Set objNTInfo = CreateObject("WinNTSystemInfo")
GetUserName = objNTInfo.UserName
'commented out for testing 'sCode = CInt(Request.QueryString("id"))
set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "Driver={SQL Server};Server=FTLive;Database=WorkLog;Uid=app;Pwd=;"
Set rsData = Server.CreateObject("ADODB.Recordset")
strsql = "EXEC JSp_DevLogFrameDetail '303'"  'commented out for testing '" & sCode & "'"
rsData.Open strSQL, MyConn
'returns = RSData.GetRows(1,0)
%>
<div id = "nav">
Stuff Here
<hr></hr>
<table>
<%
Do While not rsData.EOF
	Response.Write "<tr>"
	for each fieldItem in rsData.fields
		Response.Write "<td>&nbsp;" & fieldItem.value & "</td>"
	next
	Response.Write "</tr>"
	rsData.MoveNext
Loop
%>
</table>

I can post the Stored Proc too if it will help.

I have also tried to use GetRows, as the data returned by the the stored proc is a concatenation of several rows into a single dataset. In the code above I have commented it out too.

This is perplexing me, as I have done this a few times before on the same Intranet Server with the same SQL server database and tables....

Jonathan

http://www.telecom-stuff.com

 

[gmmastros]

I can post the Stored Proc too if it will help.

I think it might help. Please post it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jon24422531]

George

This is the SP. Essentially it is our internal HelpDesk system, and every update by either the user or the IT bod is written to a new row in the _History Table. This SP cursors (I know, not good) through this table to put all the relevant records into one record that I am trying to display in a table.
You can see that I've experimented with a table variable (commented out) to see if that would work....

CREATE PROCEDURE [dbo].[JSp_DevLogFrameDetail] @WorkLogNo INT AS

--Created 2009-04-21 to return a WorkLog's current state data & history in a single record [RB & JS]
SET NOCOUNT ON

--Declare @T Table (HoldText Varchar(8000))

DECLARE @AmendedStamp DATETIME, @UserID VARCHAR(25), @UpdateDesc VARCHAR(8000), @Text VARCHAR(8000)
SET @Text=''

DECLARE CURS CURSOR FOR SELECT AmendedStamp, UserID, UpdateDesc FROM WorkLog_History WHERE WorkLogNo=@WorkLogNo ORDER BY AmendedStamp
OPEN CURS
FETCH CURS INTO @AmendedStamp, @UserID, @UpdateDesc
WHILE @@FETCH_STATUS=0
	BEGIN
	SET @Text=@Text+CHAR(13)+'<span style="font:bold 11px arial;">'+CAST(@AmendedStamp AS VARCHAR(100))+' : '+@UserID+'</span>'+CHAR(13)+CHAR(13)+@UpdateDesc+'<hr width="75%" color="darkblue">'+CHAR(13)
	FETCH CURS INTO @AmendedStamp, @UserID, @UpdateDesc
	END
CLOSE CURS
DEALLOCATE CURS


--Insert into @T Select @Text
--Select HoldText from @T
--PRINT @Text
GO

Jonathan

http://www.telecom-stuff.com

 

[gmmastros]

I don't see where you are returning @Text to the client.

Select @Text As SomeAliasName

Once we get this to work.... I wouldn't mind helping you eliminate the cursor. (if you want).


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jon24422531]

George

Genius.... This is what happens when someone with little knowledge pinches some bits of another SP and then expects it to work!

As regards the cursor, then yes please as I would like to know how it could be done in a more efficient way. This is not a particularly big table as yet, but some of our other tables are huge and any way we can avoid a cursor is a boon.

My colleague who wrote the original SP has an immense amount of SQL knowledge, but he is wedded to the idea of using cursors... (I am sure he doesn't read this site!)

Jonathan

http://www.telecom-stuff.com

 

[gmmastros]

immense amount of SQL knowledge, but he is wedded to the idea of using cursors

That's an oxymoron. You cannot have an immense knowledge of SQL and still prefer to use cursors.

Anyway, try this in a query window. If this works, it should be pretty easy to make it a stored procedure.

Declare @WorkLogNo VarChar(20)
Set @WorkLogNo = '303'


Declare @Text VarChar(8000)
Set @Text = ''
SELECT @Text=@Text+CHAR(13)
             +'<span style="font:bold 11px arial;">'+Coalesce(CAST(AmendedStamp AS VARCHAR(100)), '')
             +' : '+Coalesce(UserID, '')+'</span>'+CHAR(13)+CHAR(13)+Coalesce(UpdateDesc, '')
             +'<hr width="75%" color="darkblue">'+CHAR(13)
FROM   WorkLog_History 
WHERE  WorkLogNo=@WorkLogNo 
ORDER BY AmendedStamp

Select @Text

Also.... there is a slight problem with the code you originally posted. If there is a NULL value in any of the 3 columns for the WorkLogNo... then your result will be NULL. It would be better to wrap the columns in a coalesce function. It could be that there are constraints on your data so that there cannot be NULLS, in which case the coalesce's are not necessary. Personally, I would keep them there because it's safer (in case someone drops that constraint from the database latter on).

Let me know if this works ok for you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jon24422531]

That works well, although with the relatively small table the workload on the server is negligible. However I will adapt it for other uses. Thanks.

The COALESCE function is one we don't really need due to the config of the table, but again I'll bear it in mind. But what is the difference between ISNULL and COALESCE?

Many thanks again George

Jonathan

http://www.telecom-stuff.com

 

[gmmastros]

That works well, although with the relatively small table the workload on the server is negligible.

I'm not surprised. I suspect my version will scale better. I also think it's a lot cleaner to code, and is therefore easier to maintain. A lot of that is personal preference though.

But what is the difference between ISNULL and COALESCE?

There are only minor differences. IsNull and coalesce both return the first Non-NULL parameter. IsNull is limited to 2 parameters where Coalesce can take many parameters.

The data type returned by the 2 functions can be different. IsNull returns the data type of the first parameter where Coalesce will use Data Type Precedence to determine the data type.

For example:

Declare @FloatVar Decimal(10,5)
Declare @IntVar Int

Set @FloatVar = 3.5

Select IsNull(@IntVar,@FloatVar) + 2
Select Coalesce(@IntVar, @FloatVar) + 2

Without running the code, what would you expect the output to be? 3.5 + 2

You would expect the output to be 5.5. Since the first parameter is an int, the output of the IsNull function is an int (with the fractional component truncated).

The IsNull version returns 5, the coalesce version returns 5.5.

I will confess to using IsNull a lot in my earlier work. Once I learned about the differences, I've switched to using Coalesce instead. Like I said... the differences are minimal, but on certain rare occasions, Coalesce is better. So, I've gotten in to the habit of always using it.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom