Category / Uncategorized

by Arvind Shyamsundar

Reportviewer and drillthrough

I was doing some testing the other day with a ReportViewer control hosted in a WinForms application to do local mode report processing. As some of my reports had a drillthrough / navigation option set, I had setup a set of DrillthroughEventHandler to ensure that the right datasources are bound to the report when the drillthrough reports are invoked.

The problem in my case was that on specific reports being invoked, the drillthrough would cause an exception inside ReportViewer and it would display:

An error occurred during rendering of the report.
Object reference not set to an instance of an object.

If this issue was random I would have suspected issues like the one in http://support.microsoft.com/kb/959595 but I was able to get this error consistently. On later troubleshooting it was clear that:

  • I had two DrillthroughEventHandler setup, but only one for each report should have been ‘active’.
  • Unfortunately the way you setup these handlers is you use the following construct:

reportViewer1.Drillthrough += new DrillthroughEventHandler(CorrectDrillthroughEventHandler)

  • Due to the way my code was structured, it turned out I would add the 2nd DrillthroughEventHandler to a report which never really needed it. So I ended up making sure that the unwanted handlers were registered first, just before the call to the registration of the correct one.

reportViewer1.Drillthrough -= new DrillthroughEventHandler(UnwantedDrillthroughEventHandler);

Doing this got rid of the exception and things worked fine for me. Of course, be sure that this is not the ONLY reason for the above exception. This was a specific case and I hope it might prove useful for someone who is using Drillthrough and multiple DrillthroughEventHandler.

If you found this useful, please do leave a comment! And if you are logged in, please do rate the post as well.

by Arvind Shyamsundar

Replication and Linked Servers

Problem

I recently hit upon an issue with trying to setup a linked server to an instance which was already a subscriber to a publication. When replication is setup, it actually creates a remote server for the subscriber. However that ‘remote server’ is not configured for data access. So if you try to use that server, you would end up with:

Msg 7411, Level 16, State 1, Line 1
Server ‘foosub’ is not configured for DATA ACCESS.

Also, any attempt to add a similarly named linked server would fail with the error message below:

EXEC master.dbo.sp_addlinkedserver @server = N’foosub’, @srvproduct=N’SQL Server’

Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 82
The server ‘foosub’ already exists.

Troubleshooting

Next, I tried to add a linked server (using the SQL Native Client) but with a different name (MYSRV) but pointing to the right server (foosub). My initial attempt yielded the following error:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI10" for linked server "MYSVR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "MYSVR" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 53)

Solution

The problem turned out that I had not used the right ‘provider’ string. Here is the script which finally worked for me:

EXEC master.dbo.sp_addlinkedserver @server = N’MYSVR’, @srvproduct=N’foosub’, @provider=N’SQLNCLI10′, @provstr=N’Server=foosub;Database=master;Trusted_Connection=yes’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’MYSVR’, @locallogin = NULL , @useself = N’True’
GO

Or if you are more comfortable using the SQLOLEDB provider, here’s a sample:

EXEC master.dbo.sp_addlinkedserver @server = N’MYSRV’, @srvproduct=N’SQLOLEDB’, @provider=N’SQLOLEDB’, @datasrc=N’foosub’, @provstr=N’Data Source=foosub;Initial Catalog=master’, @catalog=N’master’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’MYSRV’, @locallogin = NULL , @useself = N’True’
GO

You can also do this from the SSMS GUI by using ‘‘SQLNCI10’ or ‘SQLOLEDB’ as the Provider.

With this, I can subsequently access remote tables as such:

SELECT * FROM MYSVR.master.sys.tables

Hope this is useful! Please leave a comment if you find it useful.

by Arvind Shyamsundar

My Favorite SQL Server Blogs

At our workshops and during other customer interactions, we are usually asked for links to good blogs and reading materials. Here is a list of my favorite SQL-related blogs, arranged in no specific order.

http://blogs.msdn.com/craigfr: Craig Freedman on Query Processing (QP)

http://blogs.msdn.com/sqlprogrammability: Plan cache, parameterization etc.

http://blogs.msdn.com/sqlserverstorageengine: Storage Engine

http://blogs.msdn.com/repltalk: Replication support team

http://blogs.msdn.com/psssql: CSS SQL Support team

http://www.sqlskills.com/blogs/PAUL: Paul Randal with lots of tips

http://blogs.msdn.com/sqlqueryprocessing/default.aspx: Query Processing again

http://blogs.msdn.com/sqlserverfaq: Other CSS SQL Support team members

http://blogs.msdn.com/davidlean: Good series of posts on SQL Spatial features

http://blogs.msdn.com/sql_pfe_blog: Our global SQL PFE team blog

http://blogs.msdn.com/robertbruckner: Good information on SSRS

http://www.sqlcat.com: The SQL Server Customer Advisory Team (SQLCAT)

http://blogs.msdn.com/sqlreleaseservices: Learn about the latest releases (Service Pack / Cumulative Update) from SQL product team

I will be updating this list as and when I remember / discover other useful blogs. I hope this is useful for the community!

by Arvind Shyamsundar

Hack of the day: Shrink all log files in the instance

Before I proceed, I will add two disclaimers:

  1. This script is provided as-is for knowledge purposes. It is not a recommendation, or in any way intended for production usage. No warranty or guarantee is made about the correctness of the script. Use it at your own risk.
  2. We do not recommend using SHRINK operations on any production databases. This script is provided for demonstration purposes only and that too for test or development servers. For more details, please refer to the series of posts by Paul Randal at http://www.sqlskills.com/BLOGS/PAUL/category/Shrink.aspx

With that behind us, here’s what I would like to share. On my laptop I keep running large queries and many times those will grow the log file. Though my database is in simple recovery mode, a single long running transaction can cause this kind of growth.

So I would sometimes like to reclaim disk space (oh so precious on a laptop!) periodically. I would however not like to shrink the data file, just the log file. (Shrinking data files can cause fragmentation issues to occur.)

The problem is I normally operate on like 5-6 databases on my laptop. I would not want to use the GUI nor script this manually each time. So here is a simple script-generator, which will generate the necessary DBCC SHRINKFILE commands, which you can then execute as per your requirement.

drop table #f
go
create table #f (name sysname, fileid int, filename sysname, filegroup sysname null,
size sysname, maxsize sysname, growth sysname, usage sysname)
go
exec sp_MSforeachdb ‘declare @s varchar(8000); use ?;
truncate table #f; INSERT #f (name, fileid, filename, filegroup, size, maxsize, growth, usage )
exec sp_helpfile
select @s = ”use ?; DBCC SHRINKFILE (” + name + ” )” from #f where usage = ”log only”
print @s ‘

Really crude script, but it does save me some time once in a while.

by Arvind Shyamsundar

Priority boost details – and why it’s not recommended

Some times, we see customer has (accidentally or otherwise) enabled the option ‘boost priority’ for SQL Server worker threads. In general Microsoft does not recommend that you set this option. Why?

First a bit of background. When we set the ‘priority boost’ option using sp_configure what is happening is that after restart the SQL engine will call Win32 API SetPriorityClass() and passes in HIGH_PRIORITY_CLASS (if you are debugger savvy, you can set breakpoints on these APIs and check what is happening – that’s what I did, no source code is required to verify this). From MSDN:

HIGH_PRIORITY_CLASS

0x00000080

Process that performs time-critical tasks that must be executed immediately. The threads of the process preempt the threads of normal or idle priority class processes. An example is the Task List, which must respond quickly when called by the user, regardless of the load on the operating system. Use extreme care when using the high-priority class, because a high-priority class application can use nearly all available CPU time.

It then proceeds to call SetThreadPriority() with priority as THREAD_PRIORITY_HIGHEST. For this combination of Process Priority Class and Thread Priority Level, the base priority level of these worker threads is 15. The only ones higher than this in the hierarchy of the OS are any threads which have process priority class set to REALTIME_PRIORITY_CLASS (which should be a very rare case for any application.) this means that many SQL worker threads are running at a priority level which is close to the highest on the system. Hence, they will tend to be selected frequently by kernel dispatcher to execute on the CPU.

So what is the effect?

There is clear precedent in the support teams of priority boost causing unresponsive servers. Sluggish UI / mouse / keyboard movements are other common symptoms if this setting is interfering with the capability of the OS to give (non-SQL) threads their desired quantum on the CPU. On a cluster, having priority boosted SQL threads can cause other critical threads such as the resource monitor’s IsAlive poll thread to timeout, thereby causing unwanted failover. Therefore we do not recommend to set priority boost to 1, especially in clustered instances.

Reference links:

SetPriorityClass: http://msdn.microsoft.com/en-us/library/ms686219(VS.85).aspx

SetThreadPriority: http://msdn.microsoft.com/en-us/library/ms686277(VS.85).aspx

Effective Base Priority: http://msdn.microsoft.com/en-us/library/ms685100(VS.85).aspx

Windows Priority levels: http://www.microsoft.com/mspress/books/sampchap/4354c.aspx and http://www.microsoft.com/mspress/books/sampchap/4354d.aspx

by Arvind Shyamsundar

WARNING: Stack unwind information not available. Following frames may be wrong.

I’m sure if you have ever used the WinDbg family of debuggers sometime, you must have seen the above message. What does it really mean? How does it affect you?

Quick Background on stack operation

In x86, the way the stack is built up, the entry point of the function (a.k.a. prolog) generated by the compiler contains some key instructions including one which saves the previous value of the EBP register for that thread on the stack. The next instruction sets the EBP register to point to the current stack pointer (ESP).

What am I talking about? If you don’t know what EBP and ESP are, I recommend you take a quick look at the links in the reference section at the end.

Frame Pointer Omission

So in some cases the compiler may choose to omit the setting of the EBP register and may instead directly use ESP to reference locals and parameters. In such cases the EBP register (a.k.a. ‘frame pointer’) is deemed as ‘omitted’ and the generated code is called FPO (Frame Pointer Omission) code.

In such cases the debugger will typically complain that it cannot unwind the stack based on the EBP (unless it has symbol files which match the module whose function has FPO enabled). Under those cases it will emit the warning which is the title of this post.

Recommendation

  • For builds which have /Oy enabled, it is necessary to have symbol files to successfully reconstruct the stack.
  • In the real world the most common reason to encounter the above message is faulty symbol paths. Check your symbol path.
  • In cases where you can tolerate the overhead of setting up the frame pointer, leave FPO off (/Oy-). See the last reference in my post for something which Windows team has supposedly done regarding this.

References

by Arvind Shyamsundar

YADCU – Yet another dump capture utility

The plethora of dump capture tools is amazing and sometimes confusing. But here is one from Mark Russinovich which looks interesting: ProcDump. Some unique capabilities I can see in this tool are things like CPU threshold based triggers, the ability to clone a process so that it is suspended for minimum time when dump is captured, ability to launch another image on the event trigger, etc. Take a look at it, it should add to your debugging toolbox!

by Arvind Shyamsundar

The meaning of CID in output of kernel debugger commands

Recently someone asked me what was the real meaning of the ‘Cid’ field which appears in the output of commands such as !process and !thread in the kernel debugger (kd). Though from a practical perspective I was aware that these represent the Process ID and Thread ID, I was unsure of what Cid stands for. In course of a search, I found a public source which answers the question. Cid is short for CLIENT_ID which in turn is an undocumented structure.

The public source is a free PDF version of the excellent “Undocumented Windows 2000 Secrets: A Programmer’s Cookbook” book, which you can now find at http://undocumented.rawol.com/. I think this resource is a very useful one for all those interested in Windows Internals and debugging as well. Go take a look at it!

by Arvind Shyamsundar

Quick Tip: vfbasics!_AVRF_EXCEPTION_LOG_ENTRY symbol not resolved?

I was debugging some issues with the help of Application Verifier and WinDbg. Since I was onsite I did not have any access to Microsoft’s private symbol servers, so I was using the public symbol server (http://msdl.microsoft.com/download/symbols). On executing the !avrf extension command in WinDbg I was presented with the following error message in WinDbg:

***    Your debugger is not using the correct symbols                 ***
***                                                                   ***
***    In order for this command to work properly, your symbol path   ***
***    must point to .pdb files that have full type information.      ***
***                                                                   ***
***    Certain .pdb files (such as the public OS symbols) do not      ***
***    contain the required information.  Contact the group that      ***
***    provided you with these symbols if you need this command to    ***
***    work.                                                          ***
***                                                                   ***
***    Type referenced: vfbasics!_AVRF_EXCEPTION_LOG_ENTRY                ***

It turns out that my WinDbg symbol path was as follows, and due to it pointing just to the public symbol server it was loading public symbols for vfbasics.dll:

0:001> .sympath
Symbol search path is: SRV*c:localsymbols*http://msdl.microsoft.com/download/symbols
Expanded Symbol search path is: srv*c:localsymbols*http://msdl.microsoft.com/download/symbols
0:001> lml
start             end                 module name
00000000`76dc0000 00000000`76f68000   ntdll      (pdb symbols)          c:localsymbolsntdll.pdbFDAD9EE7D6E44F4F9672ECB401A802192ntdll.pdb
000007fe`f0e50000 000007fe`f0ebe000   verifier   (pdb symbols)          c:localsymbolsverifier.pdb43FCE2D63C4544F9B1C67110EB3406951verifier.pdb
000007fe`f1660000 000007fe`f1693000   vrfcore    (pdb symbols)          c:localsymbolsvrfcore.pdb751D23CCD6504794AF2F18C1E547FE371vrfcore.pdb
000007fe`f28e0000 000007fe`f292a000   vfbasics   (pdb symbols)          c:localsymbolsvfbasics.pdb1ABCDFEFF9F4602A7F055801457A7D61vfbasics.pdb

To resolve the issue, I explicitly pre-pended the path to private symbols for vfbasics (which is c:windowssystem32 or in general %WINDIR%System32:

.sympath c:windowssystem32*SRV*c:localsymbols*http://msdl.microsoft.com/download/symbols

.reload

ld vfbasics

0:001> lml
start             end                 module name
00000000`76dc0000 00000000`76f68000   ntdll      (export symbols)       C:WindowsSYSTEM32ntdll.dll
000007fe`f28e0000 000007fe`f292a000   vfbasics   (private pdb symbols)  C:WindowsSYSTEM32vfbasics.pdb

Then !avrf works just fine!

If you liked this post, please do rate it and try to leave some comments if you can!