March 6, 2023 at 9:40 pm
All,
I have an unusual scenario. A developer sent me some code that has a BEGIN / COMMIT and a rollback right before the commit. It's all wrapped in a reasonable TRY...CATCH. The script builds some dynamic SQL INSERT statements using OPEN_QUERY, dynamic because the WHERE clause depends on some variables.
The INSERT statements they are using don't have column lists and when they try to add them, they get errors about the transaction having no commit statement. But when they tested it without the column list, the stored procedure works fine.
Has anyone ever heard about something like this happening before? Does dynamic SQL using OPEN_QUERY have problems with INSERT column lists?
This sounds dubious to me as I wouldn't think one has anything to do with the other, but I wanted to check to see if anyone had heard anything like this before.
March 7, 2023 at 1:09 pm
Hey Brandie,
Honestly not sure on this one. I just wanted to let you know that someone at least looked at the question.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 7, 2023 at 1:49 pm
Can you post exact error?
Print the dynamic query before execution and share us if possible to see the issue.
Regards
Durai Nagarajan
March 7, 2023 at 4:20 pm
What is OPEN_QUERY? I can find it in relation to Oracle, but are you talking about OPENQUERY?
I tested using a column list insert with OPENQUERY with a linked server. If this is not what you're talking about then sorry for wasting your time.
Is the syntax anything like this.?
DECLARE @LinkedServerName NVARCHAR(100) = N'LINKEDSERVER',
@DatabaseName NVARCHAR(100) = N'DBNAME',
@Where NVARCHAR(1000) = N' WHERE 1=1',
@SQL NVARCHAR(4000)
SET @SQL = CONCAT('INSERT OPENQUERY([', @LinkedServerName, '], ', '''SELECT ID, COL1 from ', @DatabaseName, '.dbo.test''', ' ) select 1,1', @where)
EXEC SP_EXECUTESQL @Query = @SQL
I found that I could not insert into a table directly and it returned an error about not knowing metadata. I had to insert into a query, and the query worked with select * or select columns. When the insert failed (primary key violation) I received a meaningful error message. I had wondered if maybe the addition of the column list made the dynamic sql too long for the variable, or it included an identity or computed column, but I think those would have returned meaningful errors.
March 8, 2023 at 1:26 pm
My apologies for the mistype on OpenQuery. I spent all day querying stuff with a lot of underscores in the name. I think my fingers just defaulted to that without me looking it up.
This is what the code looks like. I scrubbed it so forgive me if some of the table names look duplicated or don't seem to make sense.
ALTER PROCEDURE [dbo].MyProc
AS
BEGIN
DECLARE @LotsOfVariables
--I've scrubbed this
SELECT @Var1 = MyNum FROM dbo.MyTable WHERE TableName = @AnotherVar
SELECT @Var2 = DifferentNum FROM dbo.MyTable WHERE TableName = @Var4
SET @ArchiveDate = CAST(DATEADD(MONTH,(-1 * @Var1),GETDATE()) AS DATE)
BEGIN TRANSACTION Archive
BEGIN TRY
SELECT @IsArchiveComplete = Value FROM MyDB.dbo.Table2 WHERE Code = 'IsArchiveComplete'
Select @ArchiveInitialDate = t.MaxDateTime, @ArchiveStartDate = t.MinDateTime
from (select TOP 1 MAX(DateTime) MaxDateTime, Min(DateTime) as MinDateTime, DATEPART(Year, DateTime) Year, DATEPART(Month, DateTime) Month FROM LinkedServer.MyDB2.dbo.PRt_Archive WITH (NOLOCK)
GROUP BY DATEPART(Year, DateTime), DATEPART(Month, DateTime)
ORDER BY Year, Month) t
IF (@IsArchiveComplete = 0)
BEGIN
IF(@ArchiveInitialDate < @ArchiveDate)
Begin
SET @ArchiveEndDate = (select MaxDateTime from (SELECT top 1 MAX(DateTime) MaxDateTime, DATEPART(Year, DateTime) Year, DATEPART(Month, DateTime) Month
FROM LocalArchiveDB.dbo.PR_Archive WITH (NoLock)
GROUP BY DATEPART(Year, DateTime), DATEPART(Month, DateTime)
ORDER BY Year desc, Month desc)t )
SET @ArchiveDate= @ArchiveStartDate
IF(@ArchiveEndDate = @ArchiveInitialDate)
Begin
SET @ArchiveDate= @ArchiveInitialDate
END
End
SET @ArchiveDateMax = CONVERT(VARCHAR(MAX),@ArchiveDate,102)
-- PendingRequest_Archive
SET @TSql = N'INSERT INTO dbo.PR_Archive
SELECT * FROM OPENQUERY([LinedServer],''SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9
FROM MyDB3.dbo.PR_Archive
WHERE [Datetime] <= ''''' + @ArchiveDateMax + ''''''')'
EXECUTE sp_executesql @TSql;
-- 2 other dynamic SQL queries much the same as above from other tables are here
SET @ArchiveDate = CAST(DATEADD(MONTH,(-1 * @NumDays),GETDATE()) AS DATE)
IF(@ArchiveInitialDate < @ArchiveDate )
Begin
SET @ArchiveEndDate = (select MaxDateTime from (SELECT top 1 MAX(DateTime) MaxDateTime, DATEPART(Year, DateTime) Year, DATEPART(Month, DateTime) Month
FROM LocalArchiveDB.dbo.PR_Archive WITH (NoLock)
GROUP BY DATEPART(Year, DateTime), DATEPART(Month, DateTime)
ORDER BY Year desc, Month desc)t )
SET @ArchiveDate= @ArchiveStartDate
IF(@ArchiveEndDate = @ArchiveInitialDate)
Begin
SET @ArchiveDate= @ArchiveInitialDate
END
End
SET @ArchiveDateMax = CONVERT(VARCHAR(MAX),@ArchiveDate,102)
SET @TSql = N'INSERT INTO dbo.MyFinalTable
SELECT * FROM OPENQUERY([LinkedServer],''SELECT * from MyDB3.dbo.AnotherAnotherTable
WHERE MyDateTime <= ''''' + @ArchiveDateMax + ''''''')'
EXECUTE sp_executesql @TSql;
UPDATE dbo.MyTable2
SET Value = 1
WHERE Code = 'IsArchiveComplete'
END
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION NBMTArchive
END CATCH
COMMIT TRANSACTION NBMTArchive
END
March 8, 2023 at 1:28 pm
I want to point out this part of the code:
SET @TSql = N'INSERT INTO dbo.PR_Archive
SELECT * FROM OPENQUERY([LinedServer],''SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9
FROM MyDB3.dbo.PR_Archive
WHERE [Datetime] <= ''''' + @ArchiveDateMax + ''''''')'
It has no INSERT column list. What I'm asking the dev to do is this:
SET @TSql = N'INSERT INTO dbo.PR_Archive (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9)
SELECT * FROM OPENQUERY([LinedServer],''SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9
FROM MyDB3.dbo.PR_Archive
WHERE [Datetime] <= ''''' + @ArchiveDateMax + ''''''')'
But the Dev keeps saying as soon as he does this, he gets the error about the transaction having no commit. That doesn't make sense to me because the addition of that column list should have no connection to the transaction.
March 8, 2023 at 3:52 pm
More coffee required.
If a transaction is going to be named I suspect it should be consistent.
BEGIN TRANSACTION Archive
ROLLBACK TRANSACTION NBMTArchive
COMMIT TRANSACTION NBMTArchive
March 8, 2023 at 3:56 pm
My test was backwards, inserting into the remote server. Inserting into the local server should be easier.
If you create this table in both places
CREATE TABLE dbo.Test
( ID int, Col1 int)
This seems to work fine for inserting with a column list from a linked server using dynamic sql. ( I agree that it could be paramaterized, but I would get it working first).
DECLARE @LinkedServerName NVARCHAR(100) = N'ABC',
@DatabaseName NVARCHAR(100) = N'DEF',
@TableName NVARCHAR(100) = 'dbo.test',
@ColumnList NVARCHAR(100) = '[ID], [COL1]',
@SQL NVARCHAR(4000)
SET @SQL = CONCAT('INSERT ', @TableName, ' (', @ColumnList, ')', CHAR(10),
'SELECT * FROM OPENQUERY(', @LinkedServerName,', ''SELECT ', @ColumnList, ' FROM ', @DatabaseName, '.DBO.TEST'')')
EXEC SP_EXECUTESQL @Query = @SQL
--PRINT @SQL
Is it possible that there is an error in the dynamic sql that is not caught by the catch? Is the column list for the insert using square brackets? If DATETIME is one of the columns I can see that being a potential issue without brackets. I don't see any reason why it shouldn't work though, it sounds like they need to test their dynamic sql some more (and check the length of the @TSql variable).
March 8, 2023 at 4:40 pm
Can you isolate it down to just the dynamic sql statement? Like if you run just the EXEC sp_executesql on its own outside of a stored procedure and without all the flow control etc.... does it give an error
If so that might indicate some config issue with the linked server.
March 9, 2023 at 7:09 pm
Well, I just encountered victory of a sorts. After I pushed back and told the Dev this didn't make sense that the two issues were connected, he managed to find a way to get those column lists added to the INSERT statements after all.
So, yay! Problem solved by the Dev.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply