sql.variable broken in denali ctp3

Question

on sql denali enterprise, this moring the following part of a stored procedure would no longer compile.

It complains that sql is not deinfed in a namespace for sql:variable

A few hours later it did, and a few hours after that it would not, and so on.

Previous do this no problem (this is a sproc that has run fun for years)

We also have a 2008R2 instance and that continues to work perfectlly.

If i comment this out, it works fine

,History.modify('insert <otherCompLogon>sql:variable("@CurrentDate")</otherCompLogon>

as last into (/history/activities)[1] '

Another sproc using sql:variable had the same sudden problem as well.

Any Ideas

On windows Server 2008R2 with latest patches

---

Michael V Khalsa Silver Earth Inc

Answer 1

I could not reproduce this on my Denali CTP3 VPC.

I did note your example does not have curly brackets; is this just a typo?

DECLARE @yourTable TABLE ( History XML )
DECLARE @CurrentDate DATETIME = GETDATE()

INSERT INTO @yourTable ( History )
SELECT '<history>
  <activities></activities>
</history>'

UPDATE @yourTable
SET History.modify('insert <otherCompLogon>{sql:variable("@CurrentDate")}</otherCompLogon> as last into (/history/activities)[1]')

SELECT *
FROM @yourTable

Consider raising a question in this forum if you can reliably reproduce the issue:

http://social.msdn.microsoft.com/Forums/en-US/category/sqlserverprerelease/

 

Answer 2

Thanks for looking into this.

Yes, the curly brackets missing was a typo

The whole point is that i could not reproduce it reliably.

I picked up the error in our error logs after a server reboot, and when i opened up the sproc in sql manager (using Modify Sproc) and save again with no actual changes it would not compile. Previously i have not had any trouble with it. With no changes, a few hours later it would compile, and then later it would not. Really odd!

This is not something that changed by the minute, but would toggle over server hours.

Wondering if there was some kind of switch somewhere being set?

This is the full sproc (having some trouble cut and pasting so hope it looks readable)
when i would comment out history.modify it would always compile.

 

Here is the error in full:

Msg 2229, Level 16, State 1, Procedure secom_RegisterLogon, Line 55
XQuery [dbo.AdminUsersHistory.History.modify()]: The name "sql" does not denote a namespace.

And this is what i comment out to get rid of it

 ,History.modify('insert <otherCompLogon>{sql:variable("@CurrentDate")}</otherCompLogon>

as last into (/history/activities)[1] '

 

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Silver Earth Inc (c) All Rights Reserved

-- Create date: July 16, 2010

-- SeCommerce

-- =============================================

ALTER

PROCEDURE [dbo].[secom_RegisterLogon]

@AdminUserId

int,

@SecHash

bigint,

@CompGuid

uniqueidentifier,

@CompName

nvarchar (100),

@IsLocal

bit,

@UserDisplayName

NVARCHAR (50),

@IpAddress

nvarchar(30),

@MacAddress

nvarchar (30),

@LocalAdminVer

varchar(25),

@ServerCoreVer

varchar(25),

@History

xml,

@CompInfo

xml,

@SessionId

int OUTPUT

AS

SET

NOCOUNT ON;

DECLARE

@CurrentDate datetime

SET

@CurrentDate = GetUtcDate();

UPDATE

dbo.InternalCleanup SET LastRunDate = @CurrentDate WHERE OpId = 2;

DECLARE

@CutOffDate as DateTime

SET

@CutOffDate = DateAdd(HH, -18, @CurrentDate);

DELETE

FROM dbo.AdminMessageQueue WHERE @CutOffDate > MesDate;

DECLARE

@StartTaskId bigint

SET

@StartTaskId = (SELECT IDENT_Current('dbo.AdminMessageQueue'))

IF

@StartTaskId IS NULL SET @StartTaskId = 0;

DECLARE

@CompId int

SET

@CompId = (SELECT CompId FROM dbo.AdminComputers WHERE CompGuid = @CompGuid );

IF

(@CompId IS NULL) BEGIN

INSERT INTO dbo.AdminComputers (CompGuid, CompName, FriendlyName) VALUES (@CompGuid, @CompName, @CompName );

SET @CompId = @@IDENTITY;

END

UPDATE

h

SET h.LogOffUtcDate = CASE WHEN h.LogOffUtcDate is null THEN @CurrentDate ELSE h.LogOffUtcDate END,

LogOffReason

= 6,

h

.TaskCount = u.TaskCount,

ConnectSpan

= CASE WHEN u.MyStatus = 2 OR u.MyStatus = 4 OR h.LogOffUtcDate is not null THEN ConnectSpan ELSE ConnectSpan + DATEDIFF(s, u.StartContact, u.LastContact) END,

History

.modify('insert <otherCompLogon>{sql:variable("@CurrentDate")}</otherCompLogon>

as last into (/history/activities)[1] '

)

FROM AdminUsersHistory h

INNER JOIN dbo.AdminUsersOn u ON u.SessionId = h.SessionId

WHERE u.CompId = @CompId AND u.CompId > 0;

DELETE

FROM dbo.AdminUsersOn WHERE CompId = @CompId AND CompId > 0;

INSERT

INTO dbo.AdminUsersOn

([AdminUserId]

,[StartTaskId]

,[SecHash]

,[CompId])

VALUES

(@AdminUserId

,@StartTaskId

,@SecHash

,@CompId);

SET

@SessionId = @@IDENTITY;

INSERT

INTO [dbo].[AdminUsersHistory]

([AdminUserId]

,[CompId]

,[IsLocal]

,[IpAddress]

,[MacAddress]

,UserDisplayName

,[LocalAdminVer]

,[ServerCoreVer]

,[History]

,[CompInfo])

VALUES

(@AdminUserId

,@CompId

,@IsLocal

,@IpAddress

,@MacAddress

,@UserDisplayName

,@LocalAdminVer

,@ServerCoreVer

,@History

,@CompInfo);

 

 

---

Michael V Khalsa Silver Earth Inc