Question

MSSQL로 다른 MSSQL 서버를 연결했어요.

연결된서버(Linked Server)에 특정 데이터베이스에 프로시저를 생성하고 싶다면

어떻게 해야 하나요?

Answer 1

DECLARE @SqlStatement nvarchar(max)

SET @SqlStatement = N'
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE dbo.usp_test
  @SiteNo smallint
AS
BEGIN
  SET NOCOUNT ON;
  RETURN 0;
END
';

--PRINT @SqlStatement
BEGIN TRY
  exec('연결된서버명.데이터베이스명.dbo.sp_executesql @statement=N''' + @SqlStatement + '''');
END TRY BEGIN CATCH
  PRINT ERROR_MESSAGE();
END CATCH;