bencoffman.com/blog - SQL

Restore Failed for Server -- System.Data.SqlClient.SqlError:Exclusive access could not be obtained because the database is in use

Ben Coffman — Thu, 14 Oct 2010 05:37:27 GMT

Error Readout:
Restore Failed for Server -- System.Data.SqlClient.SqlError:Exclusive access could not be obtained because the database is in use

Fix:
Run this SQL

USE master
GO ALTER DATABASE <database name> SET OFFLINE WITH ROLLBACK IMMEDIATE ALTER DATABASE <database name> SET ONLINE

Explanation:
Are you trying to restore a database. Slow down now buddy, there might be other people doing things to that database right now too. You could be polite, run a sp_who, see who's using your database and ask them if it would be ok to do a restore, but who are we kidding it's much more fun/quick to just kick them off. That's exactly what the above statement does.

You could try all the funny business other sites are telling you to try and switch your database to "single user" mode, but why when you can just run this simple query above and your only constraint is how fast you can hit your restore button before they try and connect again. But in case you want to know how to switch modes you can go here --> http://www.kodyaz.com/articles/alter-single-user-multi-user-mode.aspx

System.IndexOutOfRangeException: with a datareader

Ben Coffman — Wed, 14 Jul 2010 04:14:10 GMT

Error Readout:
System.IndexOutOfRangeException:

Fix:
I encountered the above error when I tried to run the query,

SELECT MAX(expression
)

FROM tables

WHERE predicates;

and then loop through the results using a datareader with code like this,

while(rdr.Read())

{

someLabel.Items.Add(rdr["ColumnName"].ToString());

}

I got the error above, because when you do a "max" sql query it doesn't return any column header.

All you need to do is give the sql result set a column header.

SELECT MAX(expression) as "ColumnName" 


FROM table 


where predicates;

Explanation:
This is another one of my bone head mistakes. It's such a simple little thing I overlook, and gets me so disappointed in myself when I realize I spent time trying to figure this out. It's generally followed by me looking over my shoulder and seeing if any of my peers saw it. I'm mostly blogging this in hopes I won't make a silly mistake like this again.

Why is it always the baby errors that hang me up the most? It can never be something deep in the CLR, or possibly find a mistake in .Net. Nope, noway, not gonna happen .Net is pretty solid...for the most part.

Incorrect syntax near ')'.

Ben Coffman — Wed, 23 Jun 2010 17:41:51 GMT

Error Readout:
Incorrect syntax near ')'

The Problem:
When I was trying to pass in getdate() as a parameter to execute a stored procedure i got the above error.

Example:
exec dbo.storedProcedure
    @param1 = '8D8DF077-7491-491B-AFA2-8F088821A073',
    @param2 = '8D8DF077-7491-491B-AFA2-8F088821A073',
    @param3 = 'Market: SAP 4 Eva',
    @param4 = getdate()

The Solution:
A sql execute statement can't pass a function such as getdate() in through a parameter. To fix this either:

Put getdate() directly in your sql statement that resides in the stored procedure
Pass a null if your table definition allows you to.
Create a temp variable store the value of getdate() in that variable and pass in that variable through the datetime parameter.