Adding a column to SQLTraceListener - "must declare the scalar variable"

Jan 6, 2011 at 3:17 PM
Edited Jan 6, 2011 at 3:35 PM

I wanted to add one new column to my SQLTraceListener. 

I added one new column to my database table called "UserId", and changed my config as follows:

Before (works fine):

        <sqlTraceListeners>
            <sqlTraceListener name="sqlTraceListenerSettings"
                        connectionStringName="log"
                        commandText="INSERT INTO LogStore VALUES(@Source, @ActivityId, @ProcessId, @ThreadId, @EventType, @Message, @Timestamp)"
                        commandType="Text">
                <parameters>
                    <parameter name="@Source" propertyToken="{Source}"/>
                    <parameter name="@ActivityId" propertyToken="{ActivityId}"/>
                    <parameter name="@ProcessId" propertyToken="{ProcessId}"/>
                    <parameter name="@ThreadId" propertyToken="{ThreadId}"/>
                    <parameter name="@EventType" propertyToken="{EventType}" callToString="true"/>
                    <parameter name="@Message" propertyToken="{Message}"/>
                    <parameter name="@Timestamp" propertyToken="{DateTime}"/>
        </parameters>
            </sqlTraceListener>
        </sqlTraceListeners>

After:

        <sqlTraceListeners>
            <sqlTraceListener name="sqlTraceListenerSettings"
                        connectionStringName="log"
                        commandText="INSERT INTO LogStore VALUES(@Source, @ActivityId, @ProcessId, @ThreadId, @EventType, @Message, @Timestamp, @UserId)"
                        commandType="Text">
                <parameters>
                    <parameter name="@Source" propertyToken="{Source}"/>
                    <parameter name="@ActivityId" propertyToken="{ActivityId}"/>
                    <parameter name="@ProcessId" propertyToken="{ProcessId}"/>
                    <parameter name="@ThreadId" propertyToken="{ThreadId}"/>
                    <parameter name="@EventType" propertyToken="{EventType}" callToString="true"/>
                    <parameter name="@Message" propertyToken="{Message}"/>
                    <parameter name="@Timestamp" propertyToken="{DateTime}"/>
                   <parameter name="@UserId" propertyToken="{WindowsIdentity}"/>
        </parameters>
            </sqlTraceListener>
        </sqlTraceListeners>

 

Get SQL error: "must declare the scalar variable @UserId"

Also, if I want to capture an additional column with a user defined token, are there specific steps?
I've read about dynamic property reader.  But I'm also unclear exactly how to set the value for that new column in my web service.


Is there any reason that method-name is not a built-in token? We currently are enhancing some webservices we inherited.

We made this enhancement yesterday:

                using (tracer.ProfileOperation("Method:" + MethodBase.GetCurrentMethod().Name +
                    " User:" + ServiceSecurityContext.Current.WindowsIdentity.Name))
                 {

                         // method code                

                 }

This works great, but we are stuffing two or more data items into the "Message" column of our database table.
Would be better for SQL queries to have these in separate columns.

I'd like to play with putting the userid and methodName as separate columns in the database. 
This is for a WCF web service hosted in IIS, and we are using impersonation,
so I wasn't sure if I needed WindowsIdentity or PrincipalName as the token in the above SQL scenario.
The value ServiceSecurityContext.Current.WindowsIdentity.Name) in the above C# code is giving me the user that I want.

Thanks,
Neal Walters

 

Coordinator
Jan 10, 2011 at 3:38 AM

Hi,

I've just tried to repro your problem but can't. The code that showed this working OK, is here: TestingSqlListener2.zip

If you still can't get your sample working I'll need a repro, similar to that provided above so I can investigate.

The script to create the database table is here:

USE [Ukadc.Diagnostics]
GO

/****** Object:  Table [dbo].[LogStore]    Script Date: 01/09/2011 20:38:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[LogStore](
 [LogId] [int] IDENTITY(1,1) NOT NULL,
 [Source] [varchar](50) NULL,
 [ActivityId] [uniqueidentifier] NOT NULL,
 [ProcessId] [int] NOT NULL,
 [ThreadId] [varchar](50) NOT NULL,
 [EventType] [varchar](50) NOT NULL,
 [Message] [varchar](8000) NULL,
 [Timestamp] [datetime] NOT NULL,
 [UserId] [nvarchar](255) NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
 [LogId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 Thanks

Josh

Coordinator
Jan 12, 2011 at 7:02 PM

I'd love to know if you got this working?

Jan 12, 2011 at 11:23 PM

Was focused on the ActivityId "smuggling" today.

As to the above, we already have a SQL database and tracing is working fine.  What I tried to do was add one of the standard "built-in" token (hope that is what they are called), i.e. WindowsIdentity to our existing config file and database, and got the error above.  I changed only the config file, no C# code. 

Then what about a user-defined column, that I mentioned in the original question: 

QUOTED from Above: Also, if I want to capture an additional column with a user defined token, are there specific steps?
I've read about dynamic property reader.  But I'm also unclear exactly how to set the value for that new column in my web service.

I will try the userid again shortly.

Thanks for the help!
Neal

 

Jan 13, 2011 at 12:39 AM

Just did some tests.  The sample program you sent works fine, but when we try it in our webservice, we get the error that I reported above. Both are going to the same SQL Server.

At first I thought maybe WindowsIdentity wasn't defined, because I couldn't find any Utils\PropertyReader that had that in the name, but I'm guessing the StringReader handles that?

Just for grins, we tried putting in @UserId5 as shown below:

<sqlTraceListeners>
            <sqlTraceListener name="sqlTraceListenerSettings"
                        connectionStringName="log"
                        commandText="INSERT INTO LogStore VALUES(@Source, @ActivityId, @ProcessId, @ThreadId, @EventType, @Message, @Timestamp, @UserId5)"
                        commandType="Text">
                <parameters>
                    <parameter name="@Source" propertyToken="{Source}"/>
                    <parameter name="@ActivityId" propertyToken="{ActivityId}"/>
                    <parameter name="@ProcessId" propertyToken="{ProcessId}"/>
                    <parameter name="@ThreadId" propertyToken="{ThreadId}"/>
                    <parameter name="@EventType" propertyToken="{EventType}" callToString="true"/>
                    <parameter name="@Message" propertyToken="{Message}"/>
                    <parameter name="@Timestamp" propertyToken="{DateTime}"/>
                   <parameter name="@UserId5" propertyToken="{WindowsIdentity}"/>
        </parameters>
            </sqlTraceListener>
        </sqlTraceListeners>

Get error: "must declare the scalar variable @UserId5"

Read some blogs, nothing clear.

We set breakpoints on the web service, and ran with debugger.  We can see that the sql Command.Parameters.count is only 7, not 8. 
We set a breakpoint where the parameters are built and we see it only being executed 7 times, and none of them has UserId in it.

We still have some more work to do to get our trace to flow better.  We keep getting some of our code doesn't match, so we have not been able to trace from end-to-end, only isolated portions.

I'm wondering if maybe WindowsIdentity is null, you don't call the add.Parameter???  Or maybe there is some try/catch around some error that keeps it from getting called.

So we will try more with the debugger, just wanted to report-in what was happening.

Thanks,
Neal

 

 

Coordinator
Jan 13, 2011 at 1:23 AM
Can you run SQL profiler and send me the command that gets executed?

Sent from my Windows Phone

From: nealwalters
Sent: Wednesday, January 12, 2011 5:40 PM
To: Josh Twist
Subject: Re: Adding a column to SQLTraceListener - "must declare the scalar variable" [UkadcDiagnostics:240713]

From: nealwalters

Just did some tests. The sample program you sent works fine, but when we try it in our webservice, we get the error that I reported above. Both are going to the same SQL Server.

At first I thought maybe WindowsIdentity wasn't defined, because I couldn't find any Utils\PropertyReader that had that in the name, but I'm guessing the StringReader handles that?

Just for grins, we tried putting in @UserId5 as shown below:

<sqlTraceListeners>
<sqlTraceListener name="sqlTraceListenerSettings"
connectionStringName="log"
commandText="INSERT INTO LogStore VALUES(@Source, @ActivityId, @ProcessId, @ThreadId, @EventType, @Message, @Timestamp, @UserId5)"
commandType="Text">
<parameters>
<parameter name="@Source" propertyToken="{Source}"/>
<parameter name="@ActivityId" propertyToken="{ActivityId}"/>
<parameter name="@ProcessId" propertyToken="{ProcessId}"/>
<parameter name="@ThreadId" propertyToken="{ThreadId}"/>
<parameter name="@EventType" propertyToken="{EventType}" callToString="true"/>
<parameter name="@Message" propertyToken="{Message}"/>
<parameter name="@Timestamp" propertyToken="{DateTime}"/>
<parameter name="@UserId5" propertyToken="{WindowsIdentity}"/>
</parameters>
</sqlTraceListener>
</sqlTraceListeners>

Get error: "must declare the scalar variable @UserId5"

Read some blogs, nothing clear.

We set breakpoints on the web service, and ran with debugger. We can see that the sql Command.Parameters.count is only 7, not 8.
We set a breakpoint where the parameters are built and we see it only being executed 7 times, and none of them has UserId in it.

We still have some more work to do to get our trace to flow better. We keep getting some of our code doesn't match, so we have not been able to trace from end-to-end, only isolated portions.

I'm wondering if maybe WindowsIdentity is null, you don't call the add.Parameter??? Or maybe there is some try/catch around some error that keeps it from getting called.

So we will try more with the debugger, just wanted to report-in what was happening.

Thanks,
Neal

Jan 13, 2011 at 1:24 AM
Edited Jan 13, 2011 at 1:26 AM

More info - were able to debug better now.

In file CombinedPropertyReaderFactor.cs, the debug on _readerFactory.TokenNames shows a list that includes Message, Id, ThreadId, ProcessId, CallStack, DateTime, EventType, Source, ActivityId, RelatedActivityId, MachineName, TimeStamp. 

Thus, I'm guessing that since "WindowsIdentity" is not in that list, maybe you don't call "AddParameter" for the SQL parms?

On the other hand, in the Windows program you sent, I dropped the reference you had to Ukc.Diagnostics, and changed to our copy FRB.Diagnostics, and it still works. [For some reason, the prorammer (no longere here) who implemented your code made a copy and changed the namespace and project name.]
I tried the same debug, and the Step-Into seemed to be doing a Step-Over so I couldn't drill down into the same code we saw in the webservice.

Another test we tried is this,
          <parameter    name="@UserId"        propertyToken="{EventType}"         callToString="true" />

and the web service worked, i.e. it put the EventType in the UserId column of the database.  So this seems to prove that it is an issue with WindowsIdentity only, but for some reason it works in your little Win App (with our version of your code), but not in the web service.

We do have a lot of tight firewall rules.  I don't suppose there are WMI calls or anything to lookup this userid?

Any reason there isn't a WindowsIdentityPropertyReader.cs?  Does it use one of the other propertyReaders?

Thanks,

Neal

 

 

Jan 18, 2011 at 3:23 PM

Checking back - did you see the last post?

Thanks,
Neal

Jan 24, 2011 at 4:46 PM
Edited Jan 24, 2011 at 4:46 PM

Checking in again - no word?   Our release window is this week if we can add this feature.

Thanks,
Neal

 

Coordinator
Jan 24, 2011 at 5:00 PM

No - firewall rules wouldn't cause you any issues (unless it prevented you from talking to the SQL box).

There is a WindowsIdentityPropertyReader: http://ukadcdiagnostics.codeplex.com/SourceControl/changeset/view/61230#180324. The WindowsIdentity token was added later than some of the other tokens so you may have an earlier version. I'm afraid that's why forking the source (especially changing namespaces) isn't often a very good idea.

If you want to continue using your old source you could simply register your own {WindowsIdentity} token and create your own WindowsIdentityPropertyReader.

FWIW, I think there have been a few changes since your colleague forked the code (that must have been very early on). The SQL listener tells you if you try to use an invalid property by throwing a Configuration Exception 'The token '{WindowsIdentity}' was not available in the token dictionary'.

Josh

Jan 27, 2011 at 10:04 PM

Thanks.  We may look at upgrading your code in a future release.  I've really no idea why she forked the code other than to comply with our naming convention.  She kind of had a "don't ask, don't tell" policy.   It might take a few hours to figure what the impact might be (for example, I know she changed the name of the config sections). 

Neal