Create, Mapping Linked Server in sql

Script to Create the Linked Server
EXEC master.dbo.sp_addlinkedserver @server = N’dsql2k’, @srvproduct=N”, @provider=N’SQLOLEDB’, @datasrc=N’testserver’
GO

Mapping of Users
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’dsql2k’, @locallogin = N’sa’, @useself = N’False’, @rmtuser = N’sa’, @rmtpassword = N’XXXXXX’
GO

get linked server list in sql

--------------------------------------------------

Get list of Linked Server in SQL

SELECT * FROM sys.Servers 

------------------------------------------------------- 

ADD Linked Server in SQL

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ] 

For more information 

https://msdn.microsoft.com/en-us/library/ms190479.aspx 

Example:

sp_addlinkedserver  @server=  'LINK_Database',  @srvproduct='' 
     , @provider=  'SQLNCLI'
    , @datasrc=  'server ip' 

ADD Linked Server in SQL

ADD Linked Server in SQL

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ] 

For more information 

https://msdn.microsoft.com/en-us/library/ms190479.aspx 

Example:

sp_addlinkedserver  @server=  'LINK_Database',  @srvproduct='' 
     , @provider=  'SQLNCLI'
    , @datasrc=  'server ip' 

--------------------------------------------------

Get list of Linked Server in SQL

SELECT * FROM sys.Servers 

Add a column, with a default value, to an existing table in SQL Server

Add a column, with a default value, to an existing table in SQL Server

Declare @SQL VarChar(1000)

SELECT @SQL = 'ALTER TABLE my_table ADD ' + @column + ' INT'

Exec (@SQL)

Sql temp table with except

Sql temp table with except

Create table #TableName (Column1 datatype1, Column2 datatype2)

insert into #TableName 

Select Column1, Column2 from Table1

Except

Select Column1, Column2 from Table2

Select * from  #TableName

Drop table #TableName

Query to get field name with datatype and size

Query to get field name with datatype and size

SELECT column_name as 'Column Name', data_type as 'Data Type', 

character_maximum_length as 'Max Length' 

FROM information_schema.columns 

FROM information_schema.columns

 

check whether a column has any letters or not

SELECT *
FROM    myTabet
WHERE   ISNUMERIC (myColumn) = 0