Пытаюсь создать процедуру такого рода:
| Код |
CREATE PROCEDURE copyMasterConfig
@sourceInst varchar(50)= NULL,
@sourceDB varchar(20) = NULL,
@targetDB varchar(20) = NULL
AS
BEGIN
-- DECLARE @sourceInst varchar(255);
-- SELECT @sourceInst='VCHPACEDBDVS1\DEV';
IF @sourceInst IS NULL SELECT @sourceInst='VCHPACEDBDVS1\DEV';
IF @sourceDB IS NULL SELECT @sourceInst='OrmedSMS_QA2';
IF @targetDB IS NULL SELECT @sourceInst='OrmedSMS';
BEGIN TRY
--create linked server
EXEC sp_addlinkedserver
@server = 'target',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc = @sourceInst;
BEGIN TRAN
--SELECT * INTO O_MSG FROM target.OrmedSMS_QA2.dbo.O_MSG;
--truncate the table
TRUNCATE TABLE [@targetDB].dbo.O_MSG;
--SET IDENTITY_INSERT O_MSG ON;
--example
INSERT INTO [@targetDB].dbo.O_MSG SELECT * FROM target.[@sourceDB].dbo.O_MSG;
--SET IDENTITY_INSERT O_MSG OFF;
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'Failed to copy!';
END CATCH
--delete linked server
EXEC sp_dropserver 'target';
END
|
При попытке прогнать этот код полухаю следующее:
| Код |
Msg 7202, Level 11, State 2, Procedure copyMasterConfig, Line 23
Could not find server 'target' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
|
Почему?
При этом 'VCHPACEDBDVS1\DEV' существует.
Вот такое дело:
| Код |
--create linked server as source
EXEC sp_addlinkedserver
@server = 'target',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc = 'VCHPACEDBDVS1\DEV'
GO
BEGIN TRY
BEGIN TRAN
--SELECT * INTO O_MSG FROM target.OrmedSMS_QA2.dbo.O_MSG;
--truncate the table
TRUNCATE TABLE O_MSG;
--SET IDENTITY_INSERT O_MSG ON;
--example
INSERT INTO O_MSG SELECT * FROM target.OrmedSMS_QA2.dbo.O_MSG;
--SET IDENTITY_INSERT O_MSG OFF;
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'Failed to copy!';
END CATCH
--delete linked server
EXEC sp_dropserver 'target';
GO
|
вне SP пашет прекрасно. |