Function Create - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Function Create

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-08-13 : 12:14:40

Can someone help me out re-writing a SQL 2005 function that will query a linked server and return all rows as one row (result)?

When I call the function below and pass a value of '8' I expect to get one result with three values concatenated together into one. This should look like: '8,8L,8H'.

What I currently get is one value returned from the function.
This looks like '8H'.

This is wrong. Then weird part is that if I query the db using the linked server outside the function, I get the three expected results. The problem only applies when using the function.

Anyone have any thoughts?

ALTER function [dbo].[fn_TESTING]
(
@mid varchar(3)
)
returns
varchar(8000)
as
begin
declare
@v varchar(8000)
select @v = ''
select @v = @v + Orb.mib_symbol + ', '
from LINKED_SERVER.dbo.company c
where (c.home_office_symbol = @mid and c.status = 'Active')
if len(@v ) > 0
select @v = left(@v, len(@v)-1)
return
@v
end

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-13 : 13:55:06

Try changing this line:
select @v = @v + Orb.mib_symbol + ', '

to this:
select @v = @v + convert(varchar(30),Orb.mib_symbol) + ', '

Be One with the Optimizer
TG

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-08-13 : 14:16:30

Thanks for the suggestion.

Unfortunately, that does not seem to have changed anything.
I get the same results...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-13 : 14:23:42

hmm, well I created a function with your exact code (including the linked server) except obviously using a table that exists in my environment. And couldn't reproduce the problem.

so you're saying this is successfull:


declare @mid varchar(3)
set @mid = '8'

declare
@v varchar(8000)
select @v = ''
select @v = @v + Orb.mib_symbol + ', '
from LINKED_SERVER.dbo.company c
where (c.home_office_symbol = @mid and c.status = 'Active')
if len(@v ) > 0
select @v = left(@v, len(@v)-1)
select @v

But this isn't?


select dbo.fn_TESTING('8')

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources