December 15, 2016 at 10:14 am
Baffled by this. I'm trying to create a dacpac and I had to add the @LogTable context variable to references of the table being returned by a table-valued function in order to make a hard error disappear; however, when I go into SSMS and make the same change I get a 'must declare scalar variable' error. (This is part of a stored procedure that worked without the context variable.)
It's really annoying. Any thoughts on how to get rid of the error in BOTH places would be welcomed.
Yes, I used a cursor. Shoot me. The function dbo.ParseRowXmlPLog is a CLR table-valued function that is deserializing XML and returning a variable number of rows. If anyone can think of a way to make this set based, I'm all ears or perhaps I should say eyes. Currently, it processes 220,000 parent rows and returns 1.2 million children rows in about a minute and a half.
DECLARE@rowGUID [uniqueidentifier]
,@InfoXML [varbinary](max)
,@InfoXMLPerf [varbinary](max)
,@AppGuid [uniqueidentifier]
DECLARE @LogTable AS TABLE (LogDatetime datetime NULL
,MilliSeconds int NULL
,LogType nvarchar(MAX) NULL
,LogAction nvarchar(MAX) NULL
,LogResult nvarchar(MAX) NULL
,LogResult_i nvarchar(32) NULL
,SequenceNr int NULL);
DECLARE @PerfLogTable AS TABLE (PerfName nvarchar(MAX) NULL
,PerfValue nvarchar(MAX) NULL);
DECLARE GetLogs CURSOR FOR
SELECT [rowGUID]
,[InfoXML] AS InfoXML
,[InfoXML] AS PerfXML
,[AppGuid]
FROM [RES-DW].[ROW].[tblLogs];
OPEN GetLogs
FETCH NEXT FROM GetLogs
INTO @rowGUID
,@InfoXML
,@InfoXMLPerf
,@AppGuid
WHILE @@FETCH_STATUS = 0
BEGIN
IF @InfoXML IS NOT NULL
BEGIN
INSERT INTO [RES-DW].[ROW].LogEntry (LogGUID, LogDatetime, MilliSeconds, LogType, LogAction, LogResult, AppGuid)
SELECT @rowGuid, @LogTable.LogDatetime, @LogTable.Logms, @LogTable.LogType, @LogTable.LogAction, @LogTable.LogResult, @AppGuid
FROM dbo.ParseRowXmlPLog(@InfoXML)
FETCH NEXT FROM GetLogs
INTO @rowGUID
,@InfoXML
,@InfoXMLPerf
,@AppGuid
END ELSE
BEGIN
FETCH NEXT FROM GetLogs
INTO @rowGUID
,@InfoXML
,@InfoXMLPerf
,@AppGuid
END
"Beliefs" get in the way of learning.
December 15, 2016 at 10:31 am
Would this do the same thing?
It's hard to know if there's no sample data and no code for the function.
INSERT INTO [RES-DW].[ROW].LogEntry (LogGUID, LogDatetime, MilliSeconds, LogType, LogAction, LogResult, AppGuid)
SELECT l.[rowGUID],
pxml.LogDatetime,
pxml.Logms,
pxml.LogType,
pxml.LogAction,
pxml.LogResult,
l.[AppGuid]
FROM [RES-DW].[ROW].[tblLogs] l
CROSS APPLY dbo.ParseRowXmlPLog(InfoXML) pxml;
December 15, 2016 at 10:47 am
Here is the code again, this time with IFCode formatting (please do this in future, to aid your readers)
DECLARE @rowGUID [uniqueidentifier]
,@InfoXML [varbinary] (max)
,@InfoXMLPerf [varbinary] (max)
,@AppGuid [uniqueidentifier]
DECLARE @LogTable AS TABLE (
LogDatetime DATETIME NULL
,MilliSeconds INT NULL
,LogType NVARCHAR(MAX) NULL
,LogAction NVARCHAR(MAX) NULL
,LogResult NVARCHAR(MAX) NULL
,LogResult_i NVARCHAR(32) NULL
,SequenceNr INT NULL
);
DECLARE @PerfLogTable AS TABLE (
PerfName NVARCHAR(MAX) NULL
,PerfValue NVARCHAR(MAX) NULL
);
DECLARE GetLogs CURSOR
FOR
SELECT [rowGUID]
,[InfoXML] AS InfoXML
,[InfoXML] AS PerfXML
,[AppGuid]
FROM [RES-DW].[ROW].[tblLogs];
OPEN GetLogs
FETCH NEXT
FROM GetLogs
INTO @rowGUID
,@InfoXML
,@InfoXMLPerf
,@AppGuid
WHILE @@FETCH_STATUS = 0
BEGIN
IF @InfoXML IS NOT NULL
BEGIN
INSERT INTO [RES-DW].[ROW].LogEntry (
LogGUID
,LogDatetime
,MilliSeconds
,LogType
,LogAction
,LogResult
,AppGuid
)
SELECT @rowGuid
,@LogTable.LogDatetime
,@LogTable.Logms
,@LogTable.LogType
,@LogTable.LogAction
,@LogTable.LogResult
,@AppGuid
FROM dbo.ParseRowXmlPLog(@InfoXML)
FETCH NEXT
FROM GetLogs
INTO @rowGUID
,@InfoXML
,@InfoXMLPerf
,@AppGuid
END
ELSE
BEGIN
FETCH NEXT
FROM GetLogs
INTO @rowGUID
,@InfoXML
,@InfoXMLPerf
,@AppGuid
END
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 15, 2016 at 10:57 am
That's actually the way it is in my code except for the insert and select statements which are one line. Of course, I chose the wrong tag...
Sorry about that...
It works fine without any context variable at all in t-sql but SSDT still gives me an error.
IF @InfoXML IS NOT NULL
BEGIN
INSERT INTO [RES-DW].[ROW].LogEntry (LogGUID, LogDatetime, MilliSeconds, LogType, LogAction, LogResult, AppGuid)
SELECT @rowGuid, LogDatetime, Logms, LogType, LogAction, LogResult, @AppGuid
FROM dbo.ParseRowXmlPLog(@InfoXML)
FETCH NEXT FROM GetLogs
INTO @rowGUID
,@InfoXML
,@InfoXMLPerf
,@AppGuid
END ELSE
"Beliefs" get in the way of learning.
December 15, 2016 at 11:00 am
Robert Frasca (12/15/2016)
That's actually the way it is in my code except for the insert and select statements which are one line. Of course, I chose the wrongtag...Sorry about that...
It works fine without any context variable at all in t-sql but SSDT still gives me an error.
IF @InfoXML IS NOT NULL
BEGIN
INSERT INTO [RES-DW].[ROW].LogEntry (LogGUID, LogDatetime, MilliSeconds, LogType, LogAction, LogResult, AppGuid)
SELECT @rowGuid, LogDatetime, Logms, LogType, LogAction, LogResult, @AppGuid
FROM dbo.ParseRowXmlPLog(@InfoXML)
FETCH NEXT FROM GetLogs
INTO @rowGUID
,@InfoXML
,@InfoXMLPerf
,@AppGuid
END ELSE
Your original post states that SSMS is where the error occurs ... so which is it?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 15, 2016 at 11:32 am
If you're confused, I'm even more confused. I made a change in the copy of the stored procedure in my database project and I thought, when I posted, that I had made the error go away because the error messages cleared. (Turns out it was teasing me and it failed the build when I tried to generate a snapshot.)
When I applied the change, I thought had worked, to the stored procedure in SSMS I get the scalar variable error described.
If I remove that change and put it back to using no context variable as depicted above, the proc runs fine but won't build in SSDT due to "invalid reference" errors on the fields being returned by the table-valued function.
Soooo, it appears that my problem is with SSDT and not T-SQL.
My apologies to the thread.
"Beliefs" get in the way of learning.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply