Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_addlinkedserver in Stored Procedure Expand / Collapse
Author
Message
Posted Wednesday, February 04, 2004 2:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 26, 2009 10:39 AM
Points: 14, Visits: 4

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!




Post #98741
Posted Wednesday, February 04, 2004 2:55 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 02, 2005 11:49 AM
Points: 2,611, Visits: 1

"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

 




Post #98746
Posted Friday, December 05, 2008 3:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 14, 2012 3:37 PM
Points: 34, Visits: 110
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.
Post #615009
Posted Monday, August 29, 2011 2:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 30, 2011 1:45 AM
Points: 2, Visits: 1
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!!
Post #1166808
Posted Monday, August 29, 2011 2:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 30, 2011 1:45 AM
Points: 2, Visits: 1
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!!
Post #1166810
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse