Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
qman
Constraint Violating Yak Guru
442 Posts |
 Posted - 2009-08-18 : 12:21:49
|
| I have a funtion that returns muliple rows as a single row. I should mention right away that this funtion makes use of a linked server. This function works fine under SQL Server 2000. We recently upgraded to SQL Server 2005. Since then, the function only returns the last record from the linked server that matches the where clause rather than all of them.Lets say that we pass to this function the string 'A'.My function checks the linked server company table for all companies that have a home symbol of 'A'. In this case there are three of them (A, A1, A2). The function is returning 'A2' rather than 'A, A1, A2'.If bring this function into the DB where the linked server resides and remove the reference to the linked server, it works fine.What gives? I can't figure this one out....ThanksALTER FUNCTION [dbo].[fn_Branches](@mid VARCHAR(3))RETURNS VARCHAR(8000)ASBEGINDECLARE @v VARCHAR(8000)SELECT @v = ''SELECT @v = @v + co.symbol + ', ' FROM LINKEDSERVER.DBNAME.dbo.company co WHERE (co.home_symbol = @mid)IF len(@v ) > 0 SELECT @v = left(@v, len(@v)-1)RETURN @vEND |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2009-08-18 : 13:11:55
|
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
 |
|
|
|
|
|
Single Row Help with Function