X-Received: by 10.99.168.73 with SMTP id i9mr17452128pgp.27.1495652705161; Wed, 24 May 2017 12:05:05 -0700 (PDT)
X-Received: by 10.157.17.23 with SMTP id g23mr260592ote.4.1495652705117; Wed, 24 May 2017 12:05:05 -0700 (PDT)
Path: csiph.com!weretis.net!feeder6.news.weretis.net!news.glorb.com!67no231690itx.0!news-out.google.com!v18ni930ita.0!nntp.google.com!67no229107itx.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: microsoft.public.sqlserver.programming
Date: Wed, 24 May 2017 12:05:04 -0700 (PDT)
In-Reply-To: <XnsA77FD356C55D4Yazorman@127.0.0.1>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=198.2.80.94; posting-account=rsX0uAoAAADMq849M91UBIF7DbcgDbGB
NNTP-Posting-Host: 198.2.80.94
References: <14264424-14c3-48c6-828b-4c6a15cb903b@googlegroups.com> <1f6c1673-dc58-4650-8223-858c574ebb43@googlegroups.com> <XnsA77FD356C55D4Yazorman@127.0.0.1>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <5a85e03e-358d-4d2b-974e-bc9d528ea684@googlegroups.com>
Subject: Re: logging stored procedure fails when inserting via linked server
From: lachlann@gmail.com
Injection-Date: Wed, 24 May 2017 19:05:05 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Xref: csiph.com microsoft.public.sqlserver.programming:31321

Thanks for the quick reply! I continued testing and stumbled on the same so=
lution you described above, if i called the logger stored proc via the link=
ed server rather than calling the logger locally but doing the insert remot=
ely it works.

Because i have parameterized both the linked server name and the database i=
t 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 procedure=
s can directly reference the synonym and avoids the need for dynamic sql wh=
en 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=3DGETUTCDATE()
			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 ini=
tialized, this creation can be wrapped into a stored proc for parameterizat=
ion
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=20
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