Connect to different servers thru stored procedure
-
I have a 10 stored procedures, which has to be transferred to a different database on a different server. I want to do it in a stored procedure.
I will pass ServerName & DatabaseName as parameters to a wrapper procedure, which will in turn connect to the server (servername), database (databasename) and copy the 10 procedures there.
My problem is to how to CONNECT to the specified server.
i have used "CONNECT TO" but it gives error that: 'TO' is not recognized.
-
Short answer (based on complexity) you can't. Why not use tried and true methods such as OSQL i.e (inside a BAT file)
SET ChangeRecord=ABC123
SET LOGDIR="C:\Temp\%ChangeRecord%"
IF NOT EXIST %LOGDIR% MKDIR %LOGDIR%
SET LOG=%LOGDIR%\ProdInstall_%ChangeRecord%_runsql.log
SET PREFIX=%LOGDIR%\prd.
SET SRVNAME=YourServerNameHere
SET DB=YourDBNameHere
ECHO. > %LOG%
osql -S%SRVNAME% -E -n -d%DB% -i "C:\Temp\PROD\Database\Stored Procedures\SPNAMEHERE.sql" -o %PREFIX%SPNAMEHERE.sql.err >> %LOG%
OR a 3rd party (CHEAP) tool like SQL Compare by Red Gate???
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Hi Vasant
You can try using linked server concept.
i.e., create the list of servers which you want to access in the linked server and then you can pass that server name and database name as parameter into stored procedure.
Balaji -
thnx,
i tried that sp_linkedserver
it is registering the server on my computer and that's all it is doing..
How do i access that server? Is there any method?
My problem is to connect to different SERVER using a stored procedure...
The logic for coyping has been written.. but how to copy it from my server to another is a problem...
I used CONNECT TO but it gives error...
Is there any other way thru which i can connect to different server using Stored Procedure...
-
hi vasant,
try execute this command from the query analyser
Select * from linkedserver..databasename.table_name
or linked servername.databasename.dbo.tablename.
if u able to get the result set, then u can use the same in the stored procedure, but before using that in stored procedure, u have to use the set ansi_null command on.
good luck.
Balaji -
Heres a SP I wrote that connects to a linked server. It may be of some use as an example.
The fully qualified connection is: StaffQueue.MDADirectory_Test.dbo.StaffQueue
---------------------------------
CREATE PROCEDURE usp_InsertIntoStaffQueue
--Declare the variables used in the Procedure
@EMPLID VARCHAR (6), --This is used as insert AND to match (where clause) records for insert from PS_PERSONAL_DATA table
@EFFDT DATETIME,
@EFFSEQ INT,
@DEPTID VARCHAR (10),
@JOBCODE VARCHAR (6), --This is used as insert AND to match (where clause) records for insert from PS_JOBCODE_TBL table
@EMPL_STATUS VARCHAR (1),
@ACTION VARCHAR (3), --This is used as insert AND to match (where clause) records for insert from PS_JOBCODE_TBL table
@ACTION_DT DATETIME,
@FULL_PART_TIME CHAR(1),
@REG_TEMP CHAR (1)
AS
--Declare the variables for the following set
DECLARE @FIRST_NAME VARCHAR (30)
DECLARE @MIDDLE_NAME VARCHAR (30)
DECLARE @LAST_NAME VARCHAR (40)
DECLARE @DESCR VARCHAR (30)
DECLARE @DESCRSHORT VARCHAR (10)
DECLARE @ACTIONLONG VARCHAR (30)
DECLARE @CreateStaffID uniqueidentifier
DECLARE @IsNewHire Bit
IF
@ACTION <> 'HIR'
BEGIN
SET @IsNewHire = 0
END
--Set the variables acording to the where clause
Set @FIRST_NAME =
(Select FIRST_NAME From PS_PERSONAL_DATA
Where EMPLID = @EMPLID)
Set @MIDDLE_NAME =
(Select MIDDLE_NAME From PS_PERSONAL_DATA
Where EMPLID = @EMPLID)
Set @LAST_NAME =
(Select LAST_NAME From PS_PERSONAL_DATA
Where EMPLID = @EMPLID)
Set @DESCR =
(Select DESCR From PS_JOBCODE_TBL
Where JOBCODE = @JOBCODE)
Set @DESCRSHORT =
(Select DESCRSHORT From PS_JOBCODE_TBL
Where JOBCODE = @JOBCODE)
Set @ACTIONLONG = --Select from Lookup Table
(Select XLATLONGNAME From EmployeeSecurityXlattable_VW
Where FIELDVALUE = @ACTION)
Set @CreateStaffID = --Select from Lookup Table
(Select * From StaffQueue.MDADirectory_Test.dbo.ImportStaff_VW)
--lastly, Perform the insert into the fully qualified link table
INSERT INTO
StaffQueue.MDADirectory_Test.dbo.StaffQueue
(
EmployeeID,
FirstName,
MiddleName,
LastName,
EffectiveDate,
EffectiveSequence,
DepartmentID,
PositionAbbreviation,
Region,
Division,
ORGCD,
PositionDescription,
EmployeeStatus,
ActionCode,
[Action],
ActionDate,
CreateStaffID,
FullPartTime,
IsNewHire,
RegularTemporary
 
VALUES
(
RTRIM (@EMPLID),
RTRIM (@FIRST_NAME),
RTRIM (@MIDDLE_NAME),
RTRIM (@LAST_NAME),
RTRIM (@EFFDT),
RTRIM (@EFFSEQ),
RTRIM (@DEPTID),
RTRIM (@DESCRSHORT),
SUBSTRING (@DEPTID, 3,2),
SUBSTRING (@DEPTID, 1,2),
SUBSTRING (@DEPTID, 5,3),
RTRIM (@DESCR),
RTRIM (@EMPL_STATUS),
RTRIM (@ACTION),
RTRIM (@ACTIONLONG),
RTRIM (@ACTION_DT),
RTRIM (@CreateStaffID),
RTRIM (@FULL_PART_TIME),
RTRIM (@IsNewHire),
RTRIM (@REG_TEMP)
 
GO
-Isaiah -
I think that the replies here are for HOW to query a different server and the original question was HOWTO install code on MULTIPLE servers. I still think that my way is the easiest and can be modified quickly. Granted NOT via a SP but then again DOS still does have it's uses....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
INSERT/SELECT/UPDATE is possible...
How would is create a new procedure or say a table on different server...????
I m able to do it by maintaining different connections in the application.(ie., source & destination).
I just want to connect to a server thru a procedure.. so that i only call the proc. and my task (of copying the procs.) will be done in one call only..
-
Have you set up a linked server?
-Isaiah
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply