August 12, 2010 at 8:30 am
My query runs fine when I just run it in a query window in SQL 2008 mgmt studio, but if I try to use it inside a function it fails with a "The multi-part identifier could not be bound" error. Here is the query and function. Any ideas why?
thanks
--------------------------
function
--------------------------
ALTER FUNCTION [dbo].[GetCustAmtStatus] (@PeriodToCalc varchar(7),
@ProductCategory varchar(16),
@ExistNewStatus varchar(5),
@Billto varchar(12),
@Shipto varchar(12)) returns varchar(255)
as
begin
declare @return_value varchar(255)
declare @sixmonth datetime
declare @twelvemonth datetime
declare @PeriodDate as datetime
declare @test-2 as varchar(32)
DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
set @PeriodDate = cast(@PeriodToCalc + '-01' as date)
set @sixmonth = (select DATEADD(MONTH, -6,@PeriodDate))
set @twelvemonth = (select DATEADD(MONTH, -11,@PeriodDate))
set @sixmonth = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@sixmonth))),DATEADD(mm,1,@sixmonth)),101))
set @test-2 = (
select top 1
cast (sol.[CuryTotInvc] as varchar(32)) as 'Amount'
FROM
[sdb-dc1].[CORE].[dbo].[SOShipHeader] soh
inner join
[sdb-dc1].[CORE].[dbo].[SOShipLine] sol
on
soh.[ShipperID]=sol.[ShipperID]
left outer join
[sdb-dc1].[CORE].[dbo].[Customer]
on
soh.[custID] = [Customer].[custID]
left outer join
[dbo].[SL_ProductGroups] pg
on
case
when sol.[AlternateID] = '' then sol.[InvtID]
else sol.[AlternateID]
end = pg.[SL_DisplayID]
WHERE
rtrim(soh.[CustID]) = @Billto
and
rtrim(soh.[ShiptoID]) = @Shipto
and
soh.[ShipDateAct] < @sixmonth
and
soh.[ShipDateAct] > @twelvemonth
AND
pg.[ProductGroup] = @ProductCategory
)
if (@test is null) and (@ExistNewStatus = 'New')
set @return_value = 'Yes it is a new customer'
else
set @return_value = 'No it is an existing customer'
return @return_value
end
-----------------------------------------
returns
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1003.CuryTotInvc" could not be bound.
------------------------------------------
If I run the query like this:
declare @sixmonth as datetime
declare @twelvemonth as datetime
declare @ProductCategory as varchar(12)
declare @Billto as varchar(9)
declare @Shipto as varchar(9)
set @twelvemonth = '2009-01-01'
set @sixmonth = '2010-01-01'
set @ProductCategory = 'Accessory'
set @Billto = '1177'
set @Shipto = '1178'
select top 1
sol.[CuryTotInvc] as 'Amount'
FROM
[sdb-dc1].[CORE].[dbo].[SOShipHeader] soh
inner join
[sdb-dc1].[CORE].[dbo].[SOShipLine] sol
on
soh.[ShipperID]=sol.[ShipperID]
left outer join
[sdb-dc1].[CORE].[dbo].[Customer]
on
soh.[custID] = [Customer].[custID]
left outer join
[dbo].[SL_ProductGroups] pg
on
case
when sol.[AlternateID] = '' then sol.[InvtID]
else sol.[AlternateID]
end = pg.[SL_DisplayID]
WHERE
rtrim(soh.[CustID]) = @Billto
and
rtrim(soh.[ShiptoID]) = @Shipto
and
soh.[ShipDateAct] < @sixmonth
and
soh.[ShipDateAct] > @twelvemonth
AND
pg.[ProductGroup] = @ProductCategory
It works fine.
result = 71
------------------------------------------------------
August 12, 2010 at 9:07 am
That is very strange. There is no direct reference to Tbl1003 in the code. Is 'sol' a synonym for Tbl1003, prehaps?
If you could provide a script to reproduce the DB schema including synonyms that would be most helpful.
August 12, 2010 at 9:09 am
The query is joining tables from different servers. Somehow the context between the different threads of the query is not available when executed as a function (remember that some of the query execution takes place on the other server).
Maybe if you re-arranged the query somehow. For myself, I would not do linked server joins in a function like that, that is not what functions are for. Use replication or something so you can do this with local tables.
Just my .02
The probability of survival is inversely proportional to the angle of arrival.
August 12, 2010 at 9:10 am
Looking at this again I noticed that you are casting the CuryTotInvc value to a varchar in the function, but not in the straight query that runs properly.
Try making the function code completely identical and see if that fixes it.
August 12, 2010 at 9:29 am
Took out the cast statement and that made no difference.
Tried rearranging the joins but that did not help either.
thanks
August 12, 2010 at 11:18 am
Going to throw this out, although the error is not recorded here. When using linked servers in stored procedures or functions the RPC must be enabled on it. But you should get a different error if that was the case, just a thought.
The other thing I would recommend to check is the CuryTotInvc doesn't have any table constraints on it or such that are not proper qualified that cannot handle linked server.
But also as someone already recommended having linked server in function can be disastrous for performace, use it cautiously.
Cheers.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply