December 20, 2002 at 2:03 pm
Windows2000 Advanced Server Service Pack 2, SQL 2000 EE with Service Pack 2
I have a developer attempting to run code across two linked servers. He is able to use a User-Defined Function that contains a cursor on the same server(Lets call it HOME) when his code is running. But he just created a new UDF that queries data from another SQL Server (Let's call it DISTANT) (Same specs). When he starts it the SQL Service on the HOME stops unexpectedly. My question is why?
Working UDF: dbo.GLD
Problem UDF: dbo.GIDOH
Problem UDF:
Declare Item_Detail Cursor
for select top 1
avg_qty
from distant.db.dbo.id01
where field1 = @curr_field and
field2 = @curr_field2 and
field3 = @curr_field3
Open ID
Fetch Next From ID into @Temp_Hand
Close ID
Deallocate ID
If @Temp_Hand = NULL
Set @Temp_Hand = 0
Return (@Temp_Hand)
End
Michelle
December 20, 2002 at 2:38 pm
But why a cursor is used here anyway? Cursors should not be used except in some special cases. Using sursors in the linked server environments sounds very scary. Based on the code provided, you could remove the cursor easily.
Michael
December 20, 2002 at 2:41 pm
I realize that this isn't the answer that you would be looking for, but your selecting a single field from a single record and using a cursor to do it. I do not intend to criticize, but in all honesty, that's a terrible use of cursors to start with. Remove the cursor, and simply select the value you want, and I bet you do not have that situation anymore. I hope that since it's not yours, but a developer's, you won't take that badly. You will have MUCH better performance as well.
December 20, 2002 at 2:46 pm
The developer is ok with your criticisms, but would like to know how to do it without a cursor.
He needs the variable to get the value returned from the select statement. Any ideas?
Michelle
December 20, 2002 at 2:50 pm
Set rowcount 1
select @Temp_Hand = avg_qty
from distant.db.dbo.id01
where field1 = @curr_field and
field2 = @curr_field2 and
field3 = @curr_field3
If @Temp_Hand = NULL
Set @Temp_Hand = 0
Set rowcount 0
Return (@Temp_Hand)
Also, even in the case with the cursor, be careful of selecting a single record when there could be multiples unless specifying an order by, as there is no guarantee of getting the same record. I would include an order by to guarantee the expected results every time. I couldn't decide right off whether setting the rowcount would be better than using top 1 or not, so defaulted to what I use. Anyone else want to comment on which would be better?
Edited by - scorpion_66 on 12/20/2002 2:56:56 PM
December 20, 2002 at 2:58 pm
Thanks, he is going to change it.
We didn't do a test execution of the UDF before we used it in the select query. We tried it again on a different server and when we tested first and then tried it in the select query it worked and returned records. And didn't stop mssqlserver.exe
Michelle
December 20, 2002 at 3:03 pm
If the server was identical, that would worry me. I think I would want to know why it acted differently.
December 20, 2002 at 3:22 pm
I prefer using TOp 1 instead of SET ROWCOUNT 1. It is less code and you do not need to set it back. SET ROWCOUNT is an old way, before TOP clause was introduced. Of course, for DML other than SELECT it is a different story.
I also would not return a variable with SP RETURN. A better practice is to return a SP error code (0 through 99 I believe) or just TRUE/FALSE (0/1). A variable should be returned as an OUTPUT parameter.
December 20, 2002 at 3:23 pm
That is why I posted. I am worried. They are identical servers. On the first server he was able to run the select statement 3 times in a row stopping the server service each time. The only error in the logs is ~'SQL Server Service terminated unexpectedly'. I moved the database to another server I had just built but we executed the function first and then tried it in the select statement. It worked fine. I just went back to the server than he repeatedly stopped and executed the function first and then ran the select statement and it returned the same result set! The service didn't stop. Its time to learn much more about user defined functions that I know now. Thanks for the feedback...and support on not using cursors.
Michelle
December 20, 2002 at 3:36 pm
As long as you only want to return 1 record I would use TOP. If you have a situation where you need to use a variable to set the number of records you want to return then SET ROWCOUNT Works better.
As far as cursors are concerned I NEVER use a globally declared cursor. And IF I have to use one it is ALWAYS read only using the following style
DECLARE @cur CURSOR
SET @cur = CURSOR FOR
SELECT X FROM Foo
FOR READ ONLY
...
I've also found that if I have more than about a 1000 records it's faster to dump the records into a temp table with an identity column and then do a while loop using the identity column to select out each record. I have never come across a situation where passing a cursor from one sp to another was a good way to go. Usually I have found a way to do it via set based methods instead.
Another question on this code you displayed is why not put the UDF on the remote machine and pass in the values for fields 1-3 as parameters? Seems more portable that way.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
December 20, 2002 at 4:24 pm
I appreciate the insight on Top. I am about as "Old School" as they get, and a lot of times, revert to "tried and true" as default. I am going to work with it some, and expand my knowledge.
I agree about passing the value back with an output as well. Much better way of going about it as its the intended method. And I also agree with the statement by Gary about placing the UDF on the remote system, as I believe that would give better performance as well.
December 27, 2002 at 3:45 pm
I think I found something. Anyone else had issues or success with the hotfix from Microsoft listed at http://support.microsoft.com/default.aspx?scid=kb;en-us;313005
It appears there is a bug with UDFs if they are in a select statement with a computed column, and there is an implicit data type conversion necessary in one of the UDF parameters.
I will update this topic if this doesn't fix the problem.
Michelle
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply