Stored proc querying across two servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored proc querying across two servers

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2009-08-27 : 08:52:42

Hey guys, I need help with coding a stored procedure. I've never really had to do one before. It needs do two things:
1) Delete all rows from Table A.
2) Query a table on a DIFFERENT server. Grab all its rows and insert them into Table A. Is this possible?

Using Management Studio, I've chosen to create a new procedure and here is what it returned. Am I on the right track?

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Thanks so much for your help.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-27 : 09:26:17

First you need a linked server. When you have this set up you need to write the sql. Does Table A have foreign key constraints? If not then you can truncate the table instead of deleting:

create proc usp_myfirstproc
as
Truncate table A

insert into Table A (<fieldlist>)
Select <fieldlist> from <linkedserver>.<database>.<owner>.A
go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 09:28:37

do you have a linked server setup with other server? if yes, you could use something like


- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	 <Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- deleting records from tableA
DELETE FROM TableA

--inserting records from other table

INSERT INTO TableA (col1,col2,...)
SELECT col1,col2,...
FROM Server2.databasename.dbo.Tablename

END
GO

fralo
Posting Yak Master

161 Posts

Posted - 2009-08-27 : 09:58:02

Thanks guys. That really helps me. Yet in setting up a Linked Server, under Server Type, should I select 'SQL Server' or other 'Other data source'? Just simply choosing the former doesn't work as I get an error. If I choose the latter, I'm unsure of what the syntax of the 'Provider String' should look like.

I'm new to this as well. Thanks for your patience, and please excuse my ignorance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 10:09:46

whats your other server rdbms? is it sql server? if not, you need to select appropriate name. for connection strings look here

http://www.connectionstrings.com/

fralo
Posting Yak Master

161 Posts

Posted - 2009-08-27 : 10:35:54

yes, it's sql server.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 10:40:55

then you can use sql server as type and follow steps below

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

fralo
Posting Yak Master

161 Posts

Posted - 2009-08-27 : 10:51:19

I went to that site earlier and followed the steps. I initially received several errors, until I entered the login/password under the Security Tab. And now it appears my linked server is set up!

Thank you so much for your help. Not as long as the code above works I'll be good to go!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 10:56:58

ok...you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources