SQL Cross-server queries

Directly on the dry goods.

Grammar:

Select *  from OpenDataSource ('SQLOLEDB','Data source=your DB IP; User Id=username; Password=pwd'). DBName.dbo.TableName

Believe in this grammar. You can see at a glance what's going on here.

If error:

' Ad Hoc Distributed Queries ' ' Openrowset/opendatasource ' because this component has been shut down as part of this server's security configuration.
' Ad Hoc Distributed Queries '.
' Ad Hoc Distributed Queries ' For more information, see "Surface area Configurator" in SQL Server Books Online.

You can add a command that modifies the database configuration before the query statement, enabling the Ad Hoc distributed Queries

--Open Ad Hoc distributed Queries--Two reconfigure is a must.execsp_configure'Show advanced Options',1Reconfigureexecsp_configure'Ad Hoc Distributed Queries',1Reconfigure --T_sqlSelect *  from OpenDataSource('SQLOLEDB','Data source=your DB IP; User Id=username; Password=pwd'). DBName.dbo.TableName--re-close the Ad Hoc distributed Queriesexecsp_configure'Ad Hoc Distributed Queries',0Reconfigureexecsp_configure'Show advanced Options',0Reconfigure

This configuration entry requires that your current logged-on user has sufficient permissions: SA can: Local logins can also:

But how many direct uses of SA are there in the general project? In general, the DB_Owner for the database is not possible.

To administer permissions for the server: That is, the server role. You probably need sysadmin permission, right? Try it.

SQL Cross-server queries