The database may be offline. Wait a few minutes and try again

The database may be offline. Wait a few minutes and try again
The above error is coming while accessing the Database. But before 10 minutes it was working fine.


To resolve this problem you have to check the database properties to see if the AutoClose parameter is not set to On. 
When this parameter is On SQL closes the database in auto and then when a new connection tries to get or add data to this database SQL turn it on again.


This parameter can be found If you open the SSMS then right click on the database - click on Properties in the properties window click on the options menu you will find the Auto Close parameter in there.

Drop All Indexes and Stats in one Script

SQL Server 2005/2008

DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000) DECLARE dropindexes CURSOR FOR

SELECT indexes.name, objects.name, schemas.name FROM sys.indexes JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID JOIN sys.schemas ON objects.schema_id = schemas.schema_id WHERE indexes.index_id > 0
  AND indexes.index_id < 255
  AND objects.is_ms_shipped = 0
  AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = indexes.name) ORDER BY objects.OBJECT_ID, indexes.index_id DESC


SELECT * FROM sys.stats OPEN dropindexes FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN
  SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
  PRINT @sql
  EXEC sp_executesql @sql 
  FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername END
CLOSE dropindexes DEALLOCATE dropindexes

GO DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000) DECLARE dropstats CURSOR FOR

SELECT stats.name, objects.name, schemas.name FROM sys.stats JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID JOIN sys.schemas ON objects.schema_id = schemas.schema_id WHERE stats.stats_id > 0
  AND stats.stats_id < 255
  AND objects.is_ms_shipped = 0 ORDER BY objects.OBJECT_ID, stats.stats_id DESC

OPEN dropstats FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN
  SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname)
  EXEC sp_executesql @sql 
  --PRINT @sql
  FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername END
CLOSE dropstats DEALLOCATE dropstats 

SQL Server 2000 Code

DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000) DECLARE dropindexes CURSOR FOR


SELECT sysindexes.name, sysobjects.name, sysusers.name FROM sysindexes JOIN sysobjects ON sysindexes.id = sysobjects.id JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE indid > 0
  AND indid < 255
  AND INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 0
  AND sysobjects.TYPE = N'U'
  AND NOT EXISTS (SELECT 1 FROM sysobjects WHERE sysobjects.name = sysindexes.name) ORDER BY sysindexes.id, indid DESC


OPEN dropindexes FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN
  SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
  PRINT @sql
  EXEC sp_executesql @sql  
  FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername END
CLOSE dropindexes DEALLOCATE dropindexes


GO DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000) DECLARE dropstats CURSOR FOR


SELECT sysindexes.name, sysobjects.name, sysusers.name FROM sysindexes JOIN sysobjects ON sysindexes.id = sysobjects.id JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE indid > 0
  AND indid < 255
  AND INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 1
  AND sysobjects.TYPE = N'U';
OPEN dropstats FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN
  SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname)
  EXEC sp_executesql @sql  
  --PRINT @sql
  FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername END
CLOSE dropstats 
DEALLOCATE dropstats 

List all the users of a particular database.

Run below query to get list of all the users of a particular Database for SQL Server 2005 and later
USE <Database_Name>


SELECT suser_sname(SID) AS ServerLevelLogin, 
       Name AS  DatabaseLevelUser, 
       Principal_ID, 
       Type_Desc, 
       Create_Date, 
       Modify_Date 
FROM sys.database_principals 
WHERE Principal_ID >= 5 and IS_Dixed_Role <> 1

In SQL 2000 run below query:

SELECT ISNULL(suser_sname(SID), 'No Mapping') AS ServerLevelLogin, 
       Name AS DatabaseLevelUser, 
       CASE
       WHEN islogin = 1 then 'Login'
       ELSE 'Role'
       END AS Type_Desc, 
       CASE
       WHEN issqluser = 1 then 'SQL User'
       ELSE 'Windows User\Group'
       END AS Type_Desc2,
       CreateDate, 
       UpdateDate 
FROM SysUsers
WHERE Name Not Like '[dpg][bu][_obe]%'

How to attach MDF without LDF into MS SQL

SQL Server 2005 does not support DBCC REBUILD_LOG. So, we cannot use it to rebuild the LDF. Here is the process to attach a database only using MDF without LDF.


VS 2005 doesn't had this facility so we use VS 2008. This method can also wok with Visual Web Developer 2008 Express Edition.


Open VS.2008 and do the followings:

1. Open a new Web site or use the existing one.
2. Add the file to the project's App_Data folder.
3. Right click this folder in the Solution Explorer and select Add Existing Item.
4. Locate the MDF file you want to use and click on Add.
5. Double-click on the MDF file that you just added; it will open Server Explorer for you.
6. Locate and right-click the MDF file data connection on Server Explorer.
7. Select Publish to provider...
8. Go through the wizard; when the wizard asks you where to publish, and select Script to file. It will generate a SQL file that contains all the database schema and its data.

On the SQL 2005 Server (mine is Express version),

1. Create a database with the same name as your MDF.
2. Run the SQL file you just created by Server Explorer in VS.2008.

Your database should be revived now!


If you are only interested in attaching MDF to the SQL 2005 server without data extraction and replication.


Here is another way to attach MDF file to the SQL 2005 sever without LDF. In this approach, you don't need to install Visual Studio but you may need another tool for help depending on your result by running the following SQL statement. 
In the following example, we will use AdventureWorks.mdf as an example.
Execute the following statement to attach your MDF on SQL Management Studio:

USE master
GO
CREATE DATABASE AdventureWorks
ON PRIMARY (FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf')
FOR ATTACH_REBUILD_LOG
GO 

If everything works fine, you may get the similar message below and have your database attached after execution:

File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf" may be incorrect. New log file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.LDF' was created.

Otherwise, you may have the following similar error:

File activation failure. The physical file name "D:\Users\<SomeUserName>\Documents\MyCode\Test\App_Data\AdventureWorks_log.ldf" may be incorrect. The log cannot be rebuilt when the primary file is read-only. Msg 1813, Level 16, State 2, Line 3 Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.

Depending on how or where you get your MDF file (because your MDF may not be detached properly), your LDF rebuilt may fail and so may your database attach command. In this case, you need to download and use the free tool SQL Server Express Utility (SSEUtil) to interact with the server and create the LDF file for you. This tool also works well with the full version of SQL Server 2005. There are two (2) big steps you need to perform in order to make your MDF finally available on SQL Management Studio.


Step 1: Use SSEUtil to attach MDF into the database server

First, you can copy the MDF to the location where you usually store for your SQL server if you like, e.g.,C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\, or keep wherever it is now.
Then at the command prompt, run sseutil's attach database command. Before doing it, please be sure to add your path to sseutil.exe. Otherwise, you need to use its absolute path name,., e.g., c:\util\sseutil.exe.

sseutil -s .\SQLExpress -a "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf" AdverureWorks

where

• -s to specify the instance of SQL server.
• -a to attach the database by specifying its MDF file path name.
• The last parameter is optional. It is the name of database you want to call (e.g., AdvantureWorks).

There are several options that you can use with sseutil, like -user and -pwd to specify the usr name and its password for connection. Please see the readme file of SSEUtil for details.


or


You can use sseutil console to interact with the server.

• To start a sseutil console, at the command prompt, type
  sseutil -c
• You will see
  Console mode. Type 'help' for more information.
  1>
• To attach a MDF, type
  !attach "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf"AdventureWorks
• Type quit when you're done. This step is necessary to enable you to add the database into the sysdabases in the next step by removing the lock on file.

Step 2: Add MDF into sysdatabases

Now you have MDF attached to the server and have LDF recreated. But the database will still remain hidden from SQL Server Management Studio. In order to make it visible in SQL Server Management Studio, you need to go through the database attach wizard to add the database back to sysdatabases; or in the management studio, run either one of the following SQL statements:

• USE master
  GO
  EXEC SP_ATTACH_DB
  @filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf',
  @dbname=N'AdventureWorks
  
  or
  
• USE master
  GO
  CREATE DATABASE AdventureWorks
  ON PRIMARY (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf')
  FOR ATTACH
  GO 

I hope that you find this guide useful.
Ref: http://blog-rat.blogspot.com

Unable to connect to SQL , “Error executing extended stored procedure: Invalid Parameter (Microsoft SQL Server, Error: 22001”

When I try to connect to a SQL instance from SQL server Management studio I get the following error :

TITLE: Microsoft SQL Server Management Studio

------------------------------

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

------------------------------

ADDITIONAL INFORMATION:

Error executing extended stored procedure: Invalid Parameter

Error executing extended stored procedure: Invalid Parameter (Microsoft SQL Server, Error: 22001)

· I get the same error irrespective of Windows /SQL authentication mode

· The detailed error from management studio was as follows :

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader()

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)

· Although I was not able to expand the databases ,I Was able to open a new query window (from management studio) and run select @@version

· I checked to see if I had adequate privileges on the registry and found something very peculiar :

· The registry key HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names was grayed out

· Since it was locked nothing else, including Management Studio, could read it

· I tried to view it in regedit and I got the error :Cannot open Instance Names: Error while opening key.

Resolution

Restarting the "WMI" (Windows Management Instrumentation) service fixes this issue.

(Understandably a reboot of the Server and Failover in case of a clustered instance will also fix the issue)

INFORMATION

This issue is very specific to Windows Server 2000

This occurs when the number of handles to SQL becomes exceedingly large . To confirm if I am running into this issue I can use process explorer

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

In the Menu I need to go to "Find Handle or DLL" and Type in the word "Instance Names" and hit "search"

This would yield the number of handles on our instance. Once you have this you can find out which process owns these handles and troubleshoot further. Based on our experience with such issues I have seen in the past, the WMIPRVSE.exe (Windows Management Instrumentation aka WMI) was one of the services that had a lot of handles open causing this issue to occur. It is possible that in your case it is another service than WMI. To identify this follow steps given above using Process Explorer.

SQL Management Studio requires to query the registry key HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names

If for some reason it cannot do this using xp_regread you will see this error message.

When some application/service has too many handles open for a single object, then it is possible that new requests fail. Now handles are windows objects, and there is no hard limit as such but purely dependent on the resources available to the Operation System. But specifically for Win2K, the maximum number of handles for a program is 10,000. (Reference: http://support.microsoft.com/kb/326591

However, you can increase this number if you have the fix in this KB applied http://support.microsoft.com/kb/326591. After you install this hotfix, follow the instructions in the KB to create the registry keys.

Associated forum link : http://social.msdn.microsoft.com/forums/en-us/sqlgetstarted/thread/193EC2F0-125D-4141-B867-8A3349BB0606