Total 3 way to interact with linked server through OpenQuery
1. Directly (Not good from performance point)
Description: Queries run locally and not on the remote servers, which means that the OLE layer may draw the needed row sets across the link to be processed. This can be a performance nightmare with large remote objects.
2. Thru OPenQuery ( good but syntax is difficult..)
Description : This function allows queries to pass through and execute on the linked server.
Syntax : OPENQUERY(linkname, 'SQL statement')
Variables cannot be passed into the OPENQUERY function,so the SQL
statement needs to be constructed as a string and then passed into
the stored procedure sp_executesql as the SQL statement parameter,thats the 3rd type.
3. thru Stored procedure(Good from performace point & also from other aspect)
/* 1st way : Directly*/
/* Select Cmd */
SELECT Uid, Pwd FROM DD.Test.dbo.Login
/* Select Cmd with where clause */
SELECT Uid, Pwd FROM DD.Test.dbo.Login where Uid='Admin'
/* Insert Cmd */
Insert Into DD.Test.dbo.Login Values('AAA','123')
/* Update Query */
update DD.Test.dbo.Login set Pwd ='111' where Pwd='abc'
/* Update query thtu 1nd way (update data come from the local datatable )*/
UPDATE DD.Test.dbo.Login SET Pwd = a.Pwd
FROM Login1 a ,DD.Test.dbo.Login b
WHERE a.uid = b.uid
/* delete also work on the same way as update in the above example*/
************************************************** ***************
/* 2nd way(OpenQuery) */
/* Select Query without where clase */
SELECT * FROM OPENQUERY(DD,'SELECT * FROM Login')
/* Select Query with where clase */
SELECT * FROM OPENQUERY(DD,'SELECT * FROM Login') where Uid = 'Admin'
SELECT * FROM OPENQUERY(DD,'SELECT * FROM Login where Uid = ''Admin''')
/* Insert Query */
INSERT OPENQUERY (DD,'SELECT Uid,Pwd FROM Login') VALUES ('NewTitle','123')
/* Update Query */
i tried but Update & Delete not work with openQuery
Here is the link for ur help that may help you to delete or update thru OPenQuery.
UPDATE Openquery(DD1,'SELECT * FROM Emp WHERE EmpID = 1') set EmpID = EmpID + 1
http://support.microsoft.com/kb/270119/EN-US/
/* Delete Query */
http://support.microsoft.com/kb/270119/EN-US/
Delete OPENQUERY (DD1, 'SELECT * FROM Emp where EmpID=2')
The SQL Server OLE DB provider requires that a unique index exist on the underlying table
for UPDATE or DELETE operations. If no unique index exists on a remote table, the following
error occurs when an UPDATE or DELETE is attempted: Server: Msg 7320, Level 16, State 2, Line
1 Could not execute query against OLE DB provider 'SQLOLEDB'. The provider could not support
a required row lookup interface. The provider indicates that conflicts occurred with other
properties or requirements. [OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work was done.
This applies both to OpenQuery and four-part named UPDATE and DELETE operations. The problem
is resolved by adding a unique index on the remote table.
************************************************** ******************
/* 3rd Type Stored procedure */
I tried lot to pass parameter in the OpenQuery but m not able to pass the parameter.
This is the only way(i think) to pass parameter
DECLARE @TSQL varchar(8000), @VAR char(5),@VAR1 char(5)
SELECT @VAR1 = 'vv34'
SELECT @VAR = 'Admin'
SELECT @TSQL = 'SELECT * FROM OPENQUERY(DD,''SELECT * FROM Login WHERE pwd = ''''' + @VAR1 + ''''' AND Uid = ''''' + @VAR + ''''''')'
EXEC (@TSQL)
Posted By....
Dinesh Kumar...Zed-axis Technologies
MCA
Contact @: 9350534252 for future query
Cheers!!!
Dinesh Kumar
( 9350534252 )
passing parameter to query
