How to use Openrowset function in SQL Server:
Openrowset is a function which can be referenced after the "FROM" Clause of T-SQL.
This function is used to pull data from different databases by utlising the ODBC drivers installed in the particular windows system.
How to Pull the data from other SQL Server :
Syntax:| select * FROM OPENROWSET('SQLNCLI','Server=SERVERNAME;Trusted_Connection=yes;','SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N''SQLAgent - Generic Refresher''') In the above command, we are entering into the server "SERVERNAME" and checking where SQL Server is up or not. What if we are providing the User and Password? Syntax: select * from openrowset('SQLNCLI','SERVER=SERVERNAME;USER ID=sarmadh;PASSWORD=sarmadh;TRUSTED_CONNECTION=YES','Select * from test_123.dbo.sqldb_info') |
Hope the above information helps
