June 21, 2011 at 7:36 am
I have created a stored procedure to render a dataset to our external customers. The stored procedure filters data based on the incoming user name. Whenever the stored procedure is called the username, datetime, & records returned/or not is captured in a audit table, which is inside the stored procedure.
I created a new sql type login for one of our customer, and they created a linked server to call this stored procedure using this sql login. When they execute as a normal 'execute' statement the procedure returns data and a audit record is inserted into the audit table. But, when they try to insert the stored procedure dataset directly into a table they got error but it was resolved when I used "OPEN Query".
But, using "OPEN Query" is causing other problems, the dataset is being returned although the insert statement wrapped inside the remote stored procedure is not inserting records into the audit table. But, the identity column value is incremented for every call through "Open Query" but not captured in the audit table. I even tried without an "identity" column but the record is not inserted into the audit table. All the time the records (dateset) is returned.
This is very odd. I appreciate all your help. Below are the scripts I used.
EXEC master.dbo.sp_addlinkedserver
@server = N'Source_Server-Name',
@srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'Source_Server-Name',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'Source-SQL-LoginName',
@rmtpassword='############'
GO
-- Statement that works fine with a record in audit table
EXEC [Source_Server-Name].Source_Database-Name.dbo.uspDetails
-- Statement that works fine but do not insert a record in audit table
SELECT A.* FROM OPENQUERY([Source_Server-Name], 'Source_Database-Name.dbo.uspDetails
') A
June 22, 2011 at 10:13 am
As nobody have an answer for my problem, I believe...
This problem sounds like a bug. I create a stored procedure and give it to my users with limited permissions to just execute the procedure, which will give them a dataset. Meanwhile, in the background I capture when the request was made by my external users and record other details I want for auditing purpose.
Once I give the user my SP, it is upto them to use any tools they want to execute the procedure. So, when they used "OPENQUERY" to execute the procedure; they get the dataset but I am left out with no audit records because through "OPENQUERY" there cannot be any "data modifications" (insert, update, or i even tried "Queue" - none worked) unless my server activates MSDTC or do other things at the server level.
Since, we use shared server which is used by several different applications, I have very limited power over the server hence activating MSDTC or other things at the server level is not possible.
Why a SP does not work the same way when executed by directly running the SP or its done by other means such as "OPENQUERY"?
This sure is a bug...
Some references...
April 24, 2012 at 5:59 am
I was having the same problem as you and almost gave up on it but have finally found an answer to the problem. Reading an article about sharing data between stored procedures I discovered that OPENQUERY issues an Implicit Transaction and that it was Rolling back my insert. So I had to add an explicit Commit to my stored procedures, in additional I discovered that if I use it in a query that has a Union it has to be Commited twice. Since I'm doing my insert inside a BEGIN TRY I can always just commit twice and not worry about whether it is being used in a UNION. I'm returning different values if there is an error but that was just apart of my debugging.
SELECT TOP 5 *
FROM mm
JOIN OPENQUERY([LOCALSERVER], 'EXEC cms60.dbo.sp_RecordReportLastRun ''LPS'', ''Test''') RptStats ON 1=1
ALTER PROCEDURE [dbo].[sp_RecordReportLastRun]
-- Add the parameters for the stored procedure here
@LibraryName varchar(50),
@ReportName varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
BEGIN TRY
INSERT INTO cms60.dbo.ReportStatistics (LibraryName, ReportName, RunDate) VALUES (@LibraryName, @ReportName, GETDATE())
--
COMMIT; --Needed because OPENQUERY starts an Implicit Transaction but doesn't commit it.
COMMIT; --Need second Commit when used in a UNION and although it throws an error when not used in a UNION doesn't cause a problem.
END TRY
BEGIN CATCH
SELECT 2 Test
END CATCH
SELECT 1 Test
END
April 24, 2012 at 6:12 am
Can you post ddl for your procedure
April 24, 2012 at 6:35 am
I posted the source to the stored procedure in my original post and a sample of it being used in a query. It is just a simple stored procedure that allows me to add it to an existing query without affecting the results but inserts a record to indicate that the query was executed.
April 24, 2012 at 6:59 am
cuirone-ServerCentral (4/24/2012)
I posted the source to the stored procedure in my original post and a sample of it being used in a query. It is just a simple stored procedure that allows me to add it to an existing query without affecting the results but inserts a record to indicate that the query was executed.
That was request to thread OP.
You may try to commit transaction within this stored proc as suggested in previous post, but make sure you check if any is open first...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply