Converting exponential number to numeric T-SQL

Following query can be used to convert an exponential number to numeric format.

SELECT convert(numeric(38,0),cast('4.102e+011' AS float))

Fixing "The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine."

If you have 64-bit Excel installed on 64-bit Windows 10 and are trying to import Excel data into 64-bit SQL Server, you may run into this error message

"The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine."

To resolve this issue, you will need to download "Microsoft Access Database Engine 2010 Redistributable"

After running the installer, the import operation should work.

Reference
http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/
http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx

Finding date when message was sent by Service Broker

The sys.conversation_endpoints table has a lifetime column. You can use that to determine the time when a conversation was started as follows.

SELECT DATEADD(SECOND, -2147483647, lifetime) FROM sys.conversation_endpoints e
WHERE e.conversation_id in ('CAAB52B9-C6B3-4DD8-9E1F-7C0CD93D7724', '7AD93224-3700-4054-9BD2-7B52C9430C17')

Load testing Excel Data Transfer to SQL Server 2008

Ever tried to transfer 1 million rows from Excel to SQL Server using the DTS Wizard in SQL Server 2008? Well I did and here is what I observed. My file had 3 columns in it and the total size of the file on disk was 37 MB. After I specified the Excel file as the source of data and clicked the Next button on the DTS Wizard, the memory consumption of DTS Wizard went up and up till it reached 1.5 GB. It was only then that it presented me with the select destination screen and the memory footprint went down. When I was done with all the steps and started the transfer, it again touched 1.5GB at the step named "Source Connection". I waited for it to reach the step named "Copying..." but I lost my patience and clicked on the Stop button. Inspite of that, the DTSWizard continued execution and I ended up killing it from Task Manager. I will try it again some other day when I have enough time to stare at the screen or keep it running overnight. I think it would be better to write my own program that uses SQLBulkCopy with some optimizations.

Just a thought before I hit the sack. Wouldn't it be cool, if an application like DTS Wizard, would tell you "I am going to take this much memory, this much time for completing this task. Are you willing to continue?" :). If I select not to continue, will it present me with a list of alternatives to do the same task and direct me to a site that gets me in touch with the smart minds @ MS. Any takers at Microsoft?

ASP.NET SQLCacheDependency challenging scenarios

There is an ASP.NET application using VirtualPathProvider to return web pages from the database. The VPP uses SQL Server Query Notification (SQLCacheDependency) to get notified if pages change in the database. Hence for every web page request, an entry goes into the database registering w3wp.exe, which handles the page request, as a subsriber to page change notifications.

Lets assume that the application has handled 1 million requests in a day. So there are 1 million subscriptions in the DB registered against the process id of w3wp.exe (pid:1018). Now lets take a look at the list of scenarios which must be considered by the development team.
Case 1: Application pool recycled and then Page is changed in the DB
The subscriber process w3 with pid 1018 disappears. SQL Server will attempt to send a change notification but will fail and log the error message. User is very likely to see an older version of the page. Disk space utilization will increase.

Case 2: SQL Server recycled.
SQL Server will attempt to send out 1 million change notifications to the w3wp.exe process with pid 1018. Memory utilization of SQL Server will shoot up. Users may experience slow applications response.

Case 3: SQL Server and App pool recycled.
SQL Server will error out when trying to send out change notifications to the w3wp.exe process. Memory utilization of SQL Server & disk space utilization will shoot up. Users may experience slow applications response.

All 3 cases present an interesting engineering problem to solve! Will blog more about the possible solutions. Keep watching this space.

SqlBulkCopy vs SSIS Bulk Insert

I recently wrote a program to move data from a CSV file into a SQL Server table using SqlBulkCopy.
The CSV file had data like
0,0,35.6,-122.5,1,
0,1,35.61188,-122.4438,1,
0,2,35.61979,-122.4062,1,
.
.

Below are some of my observations.

Trailing Delimiter Handling
With SqlBulkCopy, the data was successfully moved to SQL Server.

But when I used the SSIS Bulk Insert Task, the following error was reported

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 5
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 5
.
.

The fix for this was to remove the trailing ',' character at the end of each line in the CSV file as shown below and then perform SSIS Bulk Insert.
0,0,35.6,-122.5,1
0,1,35.61188,-122.4438,1
0,2,35.61979,-122.4062,1
.
.

Speed
I did not observe any significant difference in the data load speed of SqlBulkCopy & SSIS Bulk Insert task. I will post performance numbers at some later time.

Troubleshooting a full transaction log.

While trying to create a new TFS project, I was running into issues creating the SharePoint site. I noticed that the SQL Server log had the following message

"The transaction log for the database 'WSS_Content' is full. To find out why the space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases".

So I ran the following query

select [name], [log_reuse_wait], [log_reuse_wait_desc] from sys.databases

and noticed that log_reuse_wait_desc had the value set to LOG_BACKUP.

This meant that a Log backup operation is preventing the truncation of log files and consequently its reuse. So I explicitly performed a Transaction Log backup through Management studio. With that the error message disappeared and the transaction log file became reusable.

Ref:
Troubleshooting a Full Transaction Log (Error 9002)

How to: Back Up a Transaction Log (SQL Server Management Studio)

TFS Backup: Remember the Agent

I scheduled a maintenance job to backup the TFS databases following the steps published on msdn. When I executed the plan, I noticed the following error in the SQL Server logs.

BackupDiskFile::CreateMedia: Backup device 'D:\Backups\WSS_Content_backup_200801111921.bak' failed to create. Operating system error 5(Access is denied.)

After some investigation, I noticed that the account under which SQL Server Agent was running did not have write permissions on the backup folder. After granting the appropriate permissions and re-executing the plan, backups got created and I heaved a sigh of relief!