Hi,

I have Universal U2 server and connecting with linked server from SQL server 2012.

But unable to update data in the linked server.

DECLARE @Sql2 VARCHAR(8000)
			SET @Sql2 = 'SELECT * FROM TABLE_NAME WHERE Z_ID=''P112598'''

	  SET @Sql2 = 'UPDATE OPENQUERY (universelive, ''' + REPLACE(@Sql2, '''', '''''') + ''') SET CreatedBy = ''1'''

			PRINT @Sql2
EXEC (@Sql2)

Output:

UPDATE OPENQUERY (universelive, 'SELECT * FROM MKT_PROSPECTS_MAS_TEST WHERE Z_ID=''P112598''') SET CreatedBy = '1'
OLE DB provider "MSDASQL" for linked server "universelive" returned message "Insufficient base table information for updating or refreshing.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "universelive" could not UPDATE table "[MSDASQL]". 

Please help me to solve this.

Thank you.

You may try with EXEC () AT LNKSERVERNAME (Not tested)

exec('update A
     set CreatedBy = ''1'' TABLE_NAME A WHERE Z_ID=''P112598''') AT universelive

• Edited by Latheesh NKMicrosoft community contributor, Editor 4 hours 21 minutes ago

Narsa,

Try with the below query:

DECLARE @Sql2 VARCHAR(8000)
			SET @Sql2 = 'SELECT * FROM DB_Name.Schema_name.TABLE_NAME WHERE Z_ID=''''P112598'''''

	  SET @Sql2 = 'UPDATE OPENQUERY (universelive, ''' + @sql2 + ''') SET CreatedBy = ''1'''

			PRINT @Sql2
EXEC (@Sql2)

Note that, i have let the quotation marks for created_by to stay assuming tht its a varchar field. If not, pls remove the two -single quotes present on either side of the created_by updation value.

Hi,

Getting an error ..

Could not execute statement on remote server 'universelive'.

Please help me ...