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

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

Re: logging stored procedure fails when inserting via linked server

Show all headers | View raw

Digging further, it appears that it can be boiled down to the fact that the inner procedure (BIGetFormatterColumnList) is being called to insert into a table variable. the error can be reproduced with this snippet. I need to be able to insert the output of the stored procedure into table variable to gather the results for further work but i also want to be able to log the start/end of the called procedure when i have a high enough debugging level.

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 loopback.mydb.dbo.logging(logdate,msg) values (@now,@Message)

END
GO
CREATE proc dbo.pInner as
BEGIN
--uncomment either line to cause it to fail
--EXEC dbo.logger 'inner start'
select 1
--EXEC dbo.logger 'inner end'
END
GO
--exec pInner -- uncomment to see the "inner" stored proc is okay
GO
CREATE proc dbo.pOuter as
BEGIN
exec dbo.logger '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
END
GO
exec pOuter

-- this fails, commenting out the "insert into" line will also allow it to be successful so its the combination of the "insert into" and the inserting into the linked server.

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