Sql-server – Affected rows after update with OpenQuery

linked-serversql serverupdate

Is there a way to get the affected row count, when updating through an connection server?

I'm using MSSQL connected to MySQL with ODBC.

UPDATE OPENQUERY (MyConnection, 'UPDATE recordid, status FROM table WHERE status=0') SET status=1

Best Answer

Use a modified Linked Server Naming Convention Instead with @@rowcount

UPDATE  [linkedServerName]...[databaseName.TableName] 
set status=1 WHERE status=0
select @@rowcount

Note:

Follow these steps to get Microsoft SQL Server linked server naming convention to work with MySQL:

https://stackoverflow.com/questions/31968343/select-from-mysql-linked-server-using-sql-server-without-openquery

you can use the statement below

select * from [linkedServerName]...[databaseName.TableName]

but before executing the code above ,, you have to do some changes ..

in the SQL Server Management Studio , go to "linked servers" Folder , open Providers Folder , find MSDASQL and gets it's propertythen check "Level Zero Only" press Ok ... then execute above query and Enjoy it !!!

Related Solutions

SQL Server – OPENQUERY and Server Permissions

You asked for ideas--here's mine.

We had similar issues. On SQL Servers where the issue exists try changing the SQL Server service account to a Windows account instead of a built-in account, restart SQL Server, and retry the query.

If you're running these queries with SQL Agent, you may need to change the SQL Agent account to an actual Windows account.

My thought is that build-in accounts can't access Active Directory properly.

MS Access – How to Reach Linked Server on MSSQL

As Sean Lange mentioned, MS Access cannot use a linked server which is only accessible from within the SQL Server. But MS Access is external to SQL Server.

If you think of MS Access as the Client in a 2-tier Client/Server project, then it should be clear that you can use the 3-part naming convention (ServerName.Schema.Object) to access the SQL Server.

If you have sufficient rights, you can update data in SQL Server from MS Access through the common use of T-SQL to manipulate SQL Server data.

If you want the SQL Server itself to fetch data from a MS Access database, then you could create a Linked Server in SQL Server that would reach out through a provider to get data from the MS Access.

https://msdn.microsoft.com/en-us/library/ms190479.aspx gives a sample provide setup from the Northwind database.

EXEC sp_addlinkedserver 
   @server = N'SEATTLE Payroll', 
   @srvproduct = N'',
   @provider = N'MSDASQL', 
   @datasrc = N'LocalServer';
GO

Best Answer

Related Solutions

SQL Server – OPENQUERY and Server Permissions

MS Access – How to Reach Linked Server on MSSQL

Related Question