Linked server with column Name gives error
-
hi
SQl is a Linked server on which following query created
UPDATE [HTable2] SET [HTable2].Marks = ( SELECT TOP 1 sql.northwind.dbo.Orders.ShipVia FROM sql.northwind.dbo.Orders INNER JOIN [HTable2] ON ORDER BY sql.northwind.dbo.Orders.ShipVia ) FROM [HTable2]
but it gives error as
The number name 'sql.northwind.dbo.Orders' contains more than the maximum number of prefixes. The maximum is 3.
is it possible to use with fully qualified name , we cannot use a alias as its dynamic stored procedure created at runtime through code
thanks
Amrita
-
I think that you have syntax problems in your query (nothing after the "ON" clause)
To simplify a little you can create a view with the linked server qualification encapsulated in it:
create view RemoteOrders as
select OrderID,ShipVia from sql.northwind.dbo.Orders
the simplified query looks like this:
Update T Set Marks = R.SVia
From HTable2 T join (Select OrderID, Min(ShipVia) SVia from RemoteOrders) R
On R.OrderID = T.OrderId
Let me warn you that this seems like a very expensive operation
* Noel -
thanks for the reply Noel
that syntax problem is my copig wrong query
apart from that , we cant create a view because we are trying to create a stored proc at a runtime through code which creates some linked server on runtime , does some operation and then deletes the linked server
my question is can we do something with the query so that i dont have to use alias for a linked server and can use a fully qualified name for a column name.
thnx again for all the help
Amrita
-
If the linked server is temporary then you are using the wrong tool for the job. You need to use either OPENROWSET or OPENDATABASE instead!
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply