October 17, 2005 at 2:31 pm
Hi everyone,
I'm getting invalid object 'sv' whenever I sp_executesql the following string. The two variables are the server and db name. The prc I'm executing exists in a different db. Can anyone tell me why? I would greatly appreciate it.
SET @sSQLCmd='
update sv
set sv.SupplierInternalKey = t.SupplierInternalKey,
sv.PublicationStatus = 1
from remaserve.host_31012.[dbo].SupplierItemAttributeValue sv, #MainSupplier t, [' + @sServerName + '].[' + @sDbName + '].[dbo].SupplierItemAttributeValue sv, #MainSupplier t,
(select ChangeLink, store
from #emsstores
where [type] = 4086
and store is not null
and store >=0) s
where t.ChangeLink = s.ChangeLink
and t.MatrixMemberId = sv.MatrixMemberId
and t.ItemInternalKey = sv.ItemInternalKey
and s.store = sv.StoreInternalKey'
October 18, 2005 at 12:33 am
At first glance (no coffee yet) I'd guess it has something to do with the fact that you have two tables with the same alias. You have both SupplierItemAttributeValue sv and #MainSupplier t twice in the from clause.
FROM
remaserve.host_31012.[dbo].SupplierItemAttributeValue sv,
#MainSupplier t,
[' + @sServerName + '].[' + @sDbName + '].[dbo].SupplierItemAttributeValue sv,
#MainSupplier t,
Without your tables it's hard to say, but this should work
update sv
set sv.SupplierInternalKey = t.SupplierInternalKey,
sv.PublicationStatus = 1
from [' + @sServerName + '].[' + @sDbName + '].[dbo].SupplierItemAttributeValue sv, #MainSupplier t,
(select ChangeLink, store
from #emsstores
where [type] = 4086
and store is not null
and store >=0) s
where t.ChangeLink = s.ChangeLink
and t.MatrixMemberId = sv.MatrixMemberId
and t.ItemInternalKey = sv.ItemInternalKey
and s.store = sv.StoreInternalKey'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2005 at 6:16 am
Hi Gila,
Thanks for the response. Actually, that is the result of a really bad cut and paste job. The string looks like this:
SET @sSQLCmd='
update sv
set sv.SupplierInternalKey = t.SupplierInternalKey,
sv.PublicationStatus = 1
from [' + @sServerName + '].[' + @sDbName + '].[dbo].SupplierItemAttributeValue sv, #MainSupplier t,
(select ChangeLink, store
from #emsstores
where [type] = 4086
and store is not null
and store >=0) s
where t.ChangeLink = s.ChangeLink
and t.MatrixMemberId = sv.MatrixMemberId
and t.ItemInternalKey = sv.ItemInternalKey
and s.store = sv.StoreInternalKey'
EXEC sp_executesql @sSQLCmd
I have similar code working in other prc's, so I don't understand why this stmnt isn't working. Sorry for the typo.
October 18, 2005 at 6:41 am
No problem.
I can't see anything wrong either. If you replace the sp_executeSQL with Print, what's the resulting statement? (checking to see that nothing strange has snuck into the variables)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2005 at 12:03 pm
SET @sSQLCmd='
update sv
set sv.SupplierInternalKey = t.SupplierInternalKey,
sv.PublicationStatus = 1
from [' + @sServerName + '].[' + @sDbName + '].[dbo].SupplierItemAttributeValue sv, #MainSupplier t,
(select ChangeLink, store
from #emsstores
where [type] = 4086
and store is not null
and store >=0) s
where t.ChangeLink = s.ChangeLink
and t.MatrixMemberId = sv.MatrixMemberId
and t.ItemInternalKey = sv.ItemInternalKey
and s.store = sv.StoreInternalKey'
EXEC sp_executesql @sSQLCmd
* Noel
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply