Microsoft's free command-line LogParser utility has many uses. I find it particularly helpful when I want to comb through IIS logs or Windows event logs for troubleshooting purposes. The SQL-based syntax isn't always easy to master. Whenever I have a helpful LogParser example to share, I'll add it to this post.
Today's example shows how to search the IIS logs. Here's the command line, followed by an explanation and some tips:
G:\Log Parser 2.2>logparser "select date, time, cs-uri-stem, sc-status from g:\logs\www.2l23l.com\W3SVC954531014\ex101110.log where cs-uri-stem like '/hungryboiler%' and time >= '02:00:00' and time < '03:00:00' and cs(User-Agent) like '%Gecko%' and sc-status >= 400"
The fields I wanted to output were date, time, URL (cs-uri-stem) and status (sc-status). Tip: to get a list of all available fields in a log file, use select top 1 * from...
I figured out which log file to search by looking at my website's properties in IIS Manager. In IIS6, on the Web Site tab, under the Logging section, click the Properties button. Then you just need to pick the desired log file according to date. Don't forget: by default, IIS logs use GMT, so something that occurred in the evening Eastern Time will be in the following day's log.
I knew the error occurred on a page whose name started with "hungryboiler", so I searched for cs-uri-stem like '/hungryboiler%'.
I knew a user experienced a "page not found" error, so I searched for sc-status >= 400.
I knew the error occurred between 2:00 and 3:00 AM GMT (10:00 to 11:00 PM the previous day EST), so I search for time >= '02:00:00' and time <= '03:00:00'.
One final tip for LogParser newbies like myself: The query itself goes inside double quotes. Any strings within the query go inside single quotes.
Thursday, November 11, 2010
Monday, November 1, 2010
SQL unions and the text data type
Transact-SQL supports a data type named text. It's deprecated and may not be supported forever. It's similar to nvarchar(max).
I ran into an odd problem related to the text data type. I tried to do a query with a union, something like this:
CREATE TABLE [dbo].[t1](
[c1] [int] NULL,
[c2] [text] NULL
)
CREATE TABLE [dbo].[t2](
[c1] [int] NULL,
[c2] [text] NULL
)
SELECT * FROM t1 UNION SELECT * FROM t2
The SELECT statement fails with this error message: The text data type cannot be selected as DISTINCT because it is not comparable.
A bit odd, because there's no reference to DISTINCT in my query, but SQL Server 2005 must use DISTINCT behind the scenese to perform the UNION.
A workaround is to cast text to nvarchar(max), as in SELECT c1, CAST(c2 AS NVARCHAR(MAX)) AS c2 FROM t1 UNION SELECT c1, CAST(c2 AS NVARCHAR(MAX)) AS c2 FROM t2.
Two drawbacks to this workaround: First, you can't use SELECT *, since you must explicitly name any columns that need to be cast to nvarchar(max). Second, if your text columns happen to hold more data than the capacity of nvarchar(max), I'm not sure what would happen. Either an error message or data loss, I assume.
The real fix would be to change any text columns in your database to nvarchar(max) or some other data type that isn't deprecated and works with unions. That could take a fair amount of work if you have many stored procedures, etc. that refer to the existing text columns.
I ran into an odd problem related to the text data type. I tried to do a query with a union, something like this:
CREATE TABLE [dbo].[t1](
[c1] [int] NULL,
[c2] [text] NULL
)
CREATE TABLE [dbo].[t2](
[c1] [int] NULL,
[c2] [text] NULL
)
SELECT * FROM t1 UNION SELECT * FROM t2
The SELECT statement fails with this error message: The text data type cannot be selected as DISTINCT because it is not comparable.
A bit odd, because there's no reference to DISTINCT in my query, but SQL Server 2005 must use DISTINCT behind the scenese to perform the UNION.
A workaround is to cast text to nvarchar(max), as in SELECT c1, CAST(c2 AS NVARCHAR(MAX)) AS c2 FROM t1 UNION SELECT c1, CAST(c2 AS NVARCHAR(MAX)) AS c2 FROM t2.
Two drawbacks to this workaround: First, you can't use SELECT *, since you must explicitly name any columns that need to be cast to nvarchar(max). Second, if your text columns happen to hold more data than the capacity of nvarchar(max), I'm not sure what would happen. Either an error message or data loss, I assume.
The real fix would be to change any text columns in your database to nvarchar(max) or some other data type that isn't deprecated and works with unions. That could take a fair amount of work if you have many stored procedures, etc. that refer to the existing text columns.
ReportViewer and ToolTips
Some interesting facts about ReportViewer and ToolTips:
- Yes, you can have ToolTips in a report (RDL file) created in SQL Server 2005 Business Intelligence Development Studio (BIDS) and viewed in the .NET ReportViewer control. Just set the ToolTip property for any textbox.
- You can include line breaks in the text of a tooltip. One way to do so is to set the ToolTip property to an expression that uses vbCrLf. Example: ="This is the first line" & vbCrLf & "This is the second line".
- The ToolTips don't show up when you preview the report in BIDS. But they do show up when you run the report in SQL Server Report Services or inside in a ReportViewer control in your .NET program.
Subscribe to:
Posts (Atom)