SQL Server Q&A

Symptoms
Microsoft distributes Microsoft SQL Server 2008 R2 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release.
Resolution
Consider the following scenario:You drop a column of a partitioned table in SQL Server 2008 R2.You create a new partition for the table. You try to insert data into the new partition of the table.In this scenario, an access violation may occur.

Symptoms
When using SQLDMO BULKCOPY object in a Visual Basic application, BULKCOPY.ImportData fails if BULKCOPY.SuspendIndexing = True. The symptoms are different depending on whether or not there are indexes on the destination table.
With no index on the destination table, the application terminates with the following exception access violation:

Run time error ‘-2147221499 (80040005):
[SQL-DMO]Code Execution Exception:
EXCEPTION_ACCESS_VIOLATIONWith an existing index, the application terminates with the following message:

Run time error ‘-2147221296 (800400d0):
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘table’where ‘table’ is the name of the destination table into which the data is imported.
Resolution
Set BULKCOPY.SuspendIndexing = False. Then in the Visual Basic code, explicitly drop and recreate the indexes on the table.

Symptoms
This article describes the following about this hotfix release:The issues that are fixed by this hotfix packageThe prerequisites for installing the hotfix packageInformation about whether you must restart the computer after you install the hotfix packageInformation about whether the hotfix package is replaced by any other hotfix packageInformation about whether you must make any registry changesThe files that are contained in the hotfix package
Resolution
In SQL Server 2005, an error message that resembles the following is logged in the SQL Server error log after you roll back transactions on a table:

Error: 3315, Severity: 21, State: 1
During rollback, the following process did not hold an expected lock: process 51 with mode 8 at level 2 for row Rid pageid is (1:73) and row num is 0×0 in database ‘DatabaseName’ under transaction (0:546). Restore a backup of the database, or repair the database.This problem occurs when page locks are disabled on the table before the transactions are rolled back.
NotesIn the error message, DatabaseName represents the database that contains the table.You may have run the sp_indexoption stored procedure todisable the page locks on the table.For more information about how to view the SQL Server error log, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms187885.aspx(http://msdn2.microsoft.com/en-us/library/ms187885.aspx)

Symptoms
When you use transactional replication in a republishing topology, the Distribution Agent may experience an Access Violation (AV), which leads to an abnormal termination of the Distribution Agent.
When the Access Violation occurs on a server that is running Microsoft SQL Server 2000 Service Pack 2, the following stack dump is generated:

*******************************************************An exception was caughtin DISTRIB.EXE* Exception Address = 7800209a* Exception Code = c0000005********************************************************Module Name Return Address LocationMSVCRT 00403a45 wcsncpy + 14distrib 0040104c 00403a45distrib 77e8d326 0040104cKERNEL32 00000000 CreateProcessW + 900*******************************************************
Resolution
With transactional replication, the Logreader Agent invokes the sp_MSadd_replcommands27hp stored procedure to insert the log records from the publisher to the distribution database. The Distribution Agent then takes these log records and distributes them to the subscriber.
In a republishing topology, the sp_MSadd_replcommands27hp stored procedure may incorrectly insert an empty command (0x) in the MSRepl_commands system table in the distribution database. As a result, the Distributor Agent may experience the Access Violation mentioned in the “Symptoms” section when it tries to handle the empty command.
Logreader Agent
The Logreader might send a transaction with no command to distribution, with the intent being to update the MSrepl_transactions system table properly so that the next scan does not repeat the same section of the log.
The Distribution Agent does not expect an empty command; therefore, the Logreader checks the length of “command” in the sp_MSadd_repl_commands27hp stored procedure to make sure that the xact_seqno column is inserted in the MSRepl_transactions system table, but not in the MSRepl_commands system table.
The problem is that the “length checking” logic does not take into account a republishing or bi-directional scenario, where the originator srv and the db fields are not empty. It only checks to see if datalength(@data) > 39 to determine if the command is empty. However, it does not test the actual command data length(@cmd_data_len) before it inserts data in the MSRepl_commands system table.
Distribution Agent
The following data was gathered from the command that was causing the Access Violation:

xact_seqnotypearticle_id originator_id command_id commandcmd_data_len———————————- ———– ———- ————- ———- ————————-0×000770C1000000F6000100×00000000 NULL0×00000001 0×0 The Distribution Agent uses the sp_MSget_repl_commands stored procedure to select the commands that it needs to process. The sp_MSget_repl_commands stored procedure only picks up a command with article_id=0 when all of the following conditions are true: There are no inactive subscriptions.Loopback detection is not turned on for any articles.The number of articles in the MSsubscriptions system table is the same as the number of articles in the MSarticles system table for this particular publication. When the record with article_id=0 is picked up, the command (0x) is passed to the Distribution Agent; however, it is not handled gracefully and the Access Violation occurs.
To summarize, the cause of the problem is that:
The Logreader inserts a command of 0x in the MSRepl_commands system table in the distribution database. -and-
The Distribution Agent picks up commands with article_id=0 from the MSRepl_commands system table in the Distribution database.

Symptoms
When you use the DB-Library API server cursors, if the table name parameter that is passed to the sp_cursor stored procedure includes a bracketed identifier, the execution of sp_cursor fails with the following error message:

SQL Server message 16933, state 2, severity 16:
The cursor does not include the table being modified or the table is not updatable through the cursor.Refer to the “More Information” section of this article for an example and additional symptoms.
Resolution
SQL Server is unable to successfully parse the bracketed table name with spaces.
DB-Library binds the table name as a SQLCHAR data type, which has a fixed length of 255 characters (the maximum length of this data type). The buffer holds the table name, including the brackets, with the remaining bytes padded with spaces.

Symptoms
If you execute an INSERT of a RESTORE HEADERONLY result into a temporary table, a handled Access Violation (AV) occurs if you incorrectly define the temporary table.
Resolution
At least one of the columns in the table you defined is too small for the data returned by the EXECUTE statement.