How to avoid SQL cursors

Good article on sqlbook.com about how to avoid cursors. In short, there are two ways which the article suggests

1. Using temporary table with auto generated ID column and loop it through a while loop
2. Use SQL functions when possible to do calculation on a column

Read it here

I saw different articles on sites about the performance comparison between cursors and using while loop. Some places it made sense to use a cursor but the drawback is that it locks the table where while loops using temporary tables won't.

Due to its syntax and de-allocation procedure, I have never really tried using cursors which, not using them seems like a good practice according to some SQL experts. Read today on a site that one interviewer would ask people the syntax of using cursors and if they knew then he would consider it as negative since he wouldn't want people on his team to use them and knowing the syntax means that you use them :)

OLE DB Connection string issue using Excel

Issue:
I came across two issues while trying to load a excel file in asp.net. I'll explain both of them separately.

1). Exception: The Microsoft Jet database engine could not find the object
This exception happens when the path is not fully qualified to the excel file used in the oledb connection string. Without path, its not going to through an exception while creating connection.

Before: Dim dsn As String = "provider=Microsoft.Jet.OLEDB.4.0;data source='MyFile.xls'; Extended Properties=""Excel 8.0;HDR=NO;"""

After: Dim dsn As String = "provider=Microsoft.Jet.OLEDB.4.0;data source='" & Server.MapPath("MyFile.xls") & "'; Extended Properties=""Excel 8.0;HDR=NO;"""

Solution: use Server.MaptPath to get fully qualified path to a file to avoid this exception.

2). Exception: Could not find installable ISAM.
When using extended properties be careful with the syntax

Before: Dim dsn As String = "provider=Microsoft.Jet.OLEDB.4.0;data source='MyFile.xls'; Extended Properties=Excel 8.0;HDR=NO;"

After: Dim dsn As String = "provider=Microsoft.Jet.OLEDB.4.0;data source='" & Server.MapPath("MyFile.xls") & "'; Extended Properties=""Excel 8.0;HDR=NO;"""

Solution: use quotes around the value of extended properties. If the syntax is wrong, it tries to look for another driver.

Problem:

Export excel file from SQL Server 2005. In order to do SQL gave the attached error.

Solution:

Quote: (MSDN)

“By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this option is not set or is set to 0, SQL Server does not allow ad hoc access.”

MSDN Link: http://msdn.microsoft.com/en-us/library/ms187569.aspx

In order to turn this option on follow the link below

http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx