Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31321

Re: logging stored procedure fails when inserting via linked server

Show all headers | View raw

Thanks for the quick reply! I continued testing and stumbled on the same solution you described above, if i called the logger stored proc via the linked server rather than calling the logger locally but doing the insert remotely it works.

Because i have parameterized both the linked server name and the database it is in is not guaranteed to be static from install to install i decided to create a synonym which links to the loopback server. this way my procedures can directly reference the synonym and avoids the need for dynamic sql when calling the procedure.

here is the final version that worked:


create table dbo.logging (logdate datetime, msg nvarchar(max))
GO
CREATE proc dbo.logger (@Message nvarchar(max)) as
BEGIN
			declare @now datetime; set @now=GETUTCDATE()
			insert into dbo.logging(logdate,msg) values (@now,@Message)

END
GO
-- this avoids the need to know the name of the db or the loopback once initialized, this creation can be wrapped into a stored proc for parameterization
CREATE SYNONYM MyLogger for Loopback.MyDB.dbo.Logger
GO
CREATE  proc dbo.pInner as
BEGIN
	EXEC MyLogger 'inner start'
	select 'mycolumn'
	EXEC MyLogger 'inner end'
END
GO
--exec pInner 
GO
CREATE proc dbo.pOuter as
BEGIN
	exec MyLogger 'outer start'
	begin try
		DECLARE @MatchingColList AS TABLE ( ColumnName NVARCHAR(128) );
		insert into @MatchingColList (ColumnName)
		EXEC dbo.pInner
	end try
	begin catch
		print ERROR_MESSAGE()
	end catch
	exec MyLogger 'outer end'
END
GO
exec pOuter

Back to microsoft.public.sqlserver.programming | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

logging stored procedure fails when inserting via linked server lachlann@gmail.com - 2017-05-24 09:00 -0700
  Re: logging stored procedure fails when inserting via linked server lachlann@gmail.com - 2017-05-24 11:24 -0700
    Re: logging stored procedure fails when inserting via linked server Erland Sommarskog <esquel@sommarskog.se> - 2017-05-24 20:46 +0200
      Re: logging stored procedure fails when inserting via linked server lachlann@gmail.com - 2017-05-24 12:05 -0700
        Re: logging stored procedure fails when inserting via linked server Erland Sommarskog <esquel@sommarskog.se> - 2017-05-24 23:06 +0200
      Re: logging stored procedure fails when inserting via linked server lachlann@gmail.com - 2017-05-24 12:05 -0700
  Re: logging stored procedure fails when inserting via linked server Erland Sommarskog <esquel@sommarskog.se> - 2017-05-24 20:37 +0200

csiph-web