Month: May 2014

Query from different sql server


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

SELECT  *
FROM OPENDATASOURCE('SQLNCLI',
 ' Data Source=******;User ID=******;Password=****** ')
 .[databseName].[dbo].[tableName]
GO

PS: First ‘show advanced options’ and ‘Ad Hoc Distributed Queries’ turn on and then use sql OPENDATASOURCE command first parameter provider and second parameter is connection string that you want to connect.

Advertisements

Sql server remote connection

# Check right click sql server instance Properties>Connections Allow remote connection to this server .

# Open sql server configuration manager > SqlServer Network Configuration > Protocols For Sql Server  then enable TCP/IP and TCP/IP >Ip Address Tcp port must be 1433.

# Go to windows firewall >  Advanced Settings  > Inbounds Rules and add new rule  add tcp port 1433 then enable rule.