sp_addlinkedserver in Stored Procedure

• jimkiely

  Old Hand

  Points: 350

  More actions

  February 4, 2004 at 2:13 pm

  #358940

  I need to create a stored procedure on my development sql server that does the following;

  1.)  Deletes all records from tblMonthlySales on my Production Server.

  2.)  Inserts records into my tblMonthlySales(Production Server) from tblMonthlySales(Development server).

  This is what I tried to do and it won't work.

  CREATE PROCEDURE [spTransMonthlySales] AS

  EXEC sp_addlinkedservers 'ITCHY', N'SQL Server'

  DELETE

  FROM  Itchy.BreakTheBank3.dbo.tblMonthlySales

  INSERT Itchy.BreakTheBank3.dbo.tblMonthlySales

  SELECT *

  FROM  Scratchy.BreakTheBank3.dbo.tblMonthlySales

  GO

  Any ideas!

• Allen Cui-55137

  SSC Guru

  Points: 51650

  More actions

  February 4, 2004 at 2:55 pm

  #492801

  "EXEC sp_addlinkedservers 'ITCHY', N'SQL Server'"

  There is no "s" in sp_addlinkedserver command.

  Or use openrowset, for example.

  SELECT a.* into Test2

  FROM OPENROWSET('SQLOLEDB','tstsql01'; 'yourloginid' ; 'yourpassword' ,

     'SELECT * FROM northwind..orders') AS a

   

• Anvie

  Ten Centuries

  Points: 1216

  More actions

  December 5, 2008 at 3:46 pm

  #908709

  Hello,

  Make sure you have the rights to access the remote server or the server you are trying to linked. You also should consider creating login for the linked server.

  Query Format:

  -- Create Linked server

  EXEC master.dbo.sp_addlinkedserver

  @server = N'Server Name',

  @srvproduct=N'SQL Server'

  -- Create Login for the linked server

  EXEC master.dbo.sp_addlinkedsrvlogin

  @rmtsrvname=N'Server Name',

  @useself=N'False',

  @locallogin=N'Local User Name',

  @rmtuser=N'Remote User Name',

  @rmtpassword='Remote Password'

  Let me know if you still having problem with this.

• dat.tomytom

  SSC Journeyman

  Points: 90

  More actions

  August 29, 2011 at 2:33 am

  #1374400

  I want connection database remove.

  Following detail information remove server:

  Data source=MyNgan-PC\SQLEXPRESS( Example IP :192.168.186.186)

  Northwind, password=sa; uid=sa

  Help me! How use detail sp_addlinkedserver

  thank!!

• dat.tomytom

  SSC Journeyman

  Points: 90

  More actions

  August 29, 2011 at 2:39 am

  #1374402

  I want connection database remove.

  Following detail information remove server:

  Data source=MyNgan-PC\SQLEXPRESS( Example IP :192.168.186.186),

  database=Northwind, password=sa; uid=sa

  Help me! How use detail sp_addlinkedserver

  thank!!