SQL Server Error: 9001, Severity: 21, State: 5 – The log for database `database_name` is not available. Check the event log for related error messages. Resolve any errors and restart the database. | Error: 9001, Severity: 21, State: 5. | Matched: %Error%,

Upon being greeted with the following logs, I started investigating the issue.

I found the issue to be caused by the database being closed, and not any major underlying disk issue.

Check if your database has auto_close enabled.


select @@SERVERNAME AS server_name,getutcdate() as report_date_utc,name as database_name, is_auto_close_on , state_desc, user_access_desc
from sys.databases
where is_auto_close_on = 1
order by name asc

If autoclose is on, switch this database to no longer use auto_close.

Before making any changes, check the integrity of the database. If there are no errors generated by this command, then  move on to changing the auto_close option.

dbcc checkdb('database_name')

alter database [database_name] set offline with rollback immediate;
go
alter database [database_name] set online;

alter database [database_name] set AUTO_CLOSE OFF;

Advertisements

sql server create or replace procedure

Oracle has great syntax to CREATE OR REPLACE a stored procedure.

When I have to sync up multiple servers or deploy a specific procedure across my entire SQL Server Environment, I like to have a single script which can create the procedure where it needs to be created, and alter the procedure if it needs to be altered.

To make my code runnable on a server that may / may not already have my stored procedure I create a code stub, then replace it with the correct code.

Here is an example of code I use for database backups with Red Gate

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE 'usp_BackupLogs' AND ROUTINE_TYPE LIKE 'PROCEDURE' )
BEGIN
EXEC sp_executesql N'CREATE PROCEDURE [dbo].[usp_BackupLogs] AS SELECT CONVERT(VARCHAR(20),getdate() ,120)  + '' - Code Stub to Be Replaced by Alter Script '' as [Stub]'
END
GO
ALTER PROCEDURE [dbo].[usp_BackupLogs]
AS
DECLARE @emailMessage NVARCHAR(4000)
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXEC MASTER..sqlbackup N'-SQL "BACKUP LOGS [*] TO DISK = ''\\BACKUPS\LOGS\'' WITH NAME = '''', DESCRIPTION = '''', INIT, PASSWORD = ''mypassword'', MAXDATABLOCK = 65536,KEYSIZE = 256, COMPRESSION = 1, THREADS = 1"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <>  0) OR (@sqlerrorcode <> 0)
BEGIN
SET @emailMessage = 'FAILED LOG BACKUP | ' + @@SERVERNAME + ' | ' + SUSER_SNAME() + ' | '+ DB_NAME() + ' | ' + CONVERT(VARCHAR(20),getdate(),120)
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'DBAS@mydomain.com',
@importance = 'High',
@subject = @emailMessage,
@body= @emailMessage
END
-- COMMENTED OUT INTENTIONALLY - DO NOT WANT EMAILS ON SUCCESSFUL LOG BACKUPS (esp. if scheduled every 15 minutes/ frequently)
--ELSE
-- BEGIN
-- SET @emailMessage = 'SUCCESSFUL LOG BACKUP | ' + @@SERVERNAME + ' | ' + SUSER_SNAME() + ' | '+ DB_NAME() + ' | ' + CONVERT(VARCHAR(20),getdate(),120)
-- EXEC msdb.dbo.sp_send_dbmail
-- @recipients=N'DBAS@mydomain.com',
-- @subject = @emailMessage,
-- @body= @emailMessage ;
-- End

Following this example if I want to deploy all the code from a server to another machine, I generate the CREATE Statements using the following code:

This prevents the need for 2 separate scripts (CREATE AND ALTER).

----------------------------------------------------------------
-- CREATE OR REPLACE ALL STORED PROCEDURES
----------------------------------------------------------------
SELECT
ROUTINE_NAME
,'EXEC sp_executesql N'''+REPLACE(N'CREATE PROCEDURE ['+ROUTINE_SCHEMA+'].['+ROUTINE_NAME+'] AS SELECT CONVERT(VARCHAR(20),getdate() ,120) + '' - Code Stub to Be Replaced by Alter Script '' as [Stub]','''','''''') + '''' as cmd
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE LIKE 'PROCEDURE'
ORDER BY ROUTINE_NAME ASC

Subsequently I script all objects as ALTERS and append to the CREATE script.

I create a single Script File with the following convention
1. CREATE scripts (code stubs) ( with if not exists )
2. ALTER scripts (real code)

Windows 7 – 64 Bit – The ‘Microsoft.Jet.OLEDB.4.0’ provider is not registered on the local machine.

When attempting to export data from an Access database I encountered the following exception.

System.InvalidOperationException was unhandled
Message="The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine."
Source="System.Data"

This error came about when I switched from a 32 bit Vista Machine to a new 64 bit Windows 7 machine.

To fix the issue simply change your build options so that the project is compiled to run as 32-bit.

There is no 64 Bit Jet Driver

SSIS Error Handling with Email Alerts and Details.

Presented with the need for a consistent method of alerting users of Errors or Warning Conditions in SQL Server Integration Services; I went about the creation of a standard methodology to use in all packages across the enterprise.

The requirements were:

  • Alert Specific Users of Errors within a Package
  • Alerts are to provide sufficient detail for rapid troubleshooting
  • Alert Process is to be consistent across all servers

I therefore set about using the SSIS Script task, so that I could increment my counters and customize my error messages as each task in the package completed.
I implemented 2 counters, ones for warnings and one for errors as well as an error message for tracking each step of my package.
The User Defined Variables for error handling are

  • alertsubscribers – string
  • errorcount – integer
  • errormessage – string
  • mailhost – string
  • warningcount – integer

Read Only Variables (All System Provided)


System::LocaleID
,System::TaskID
,System::TaskName
,System::TaskTransactionOption
,System::MachineName
,System::PackageID
,System::PackageName
,System::ProductVersion
,System::VersionBuild
,System::VersionComments
,System::VersionGUID
,System::VersionMajor
,System::VersionMinor

Read Write Variables (User Variables at Package)


User::errorcount
,User::errormessage
,User::warningcount
,User::alertsubscribers
,User::mailhost

Script Task at Begining of Package to Initialize Variables


String errormessage = "";
errormessage = errormessage + "MachineName: " + Dts.Variables["MachineName"].Value.ToString() + System.Environment.NewLine;
errormessage = errormessage + "PackageID: " + Dts.Variables["PackageID"].Value.ToString() + System.Environment.NewLine;
errormessage = errormessage + "PackageName: " + Dts.Variables["PackageName"].Value.ToString() + System.Environment.NewLine;
errormessage = errormessage + "ProductVersion: " + Dts.Variables["ProductVersion"].Value.ToString() + System.Environment.NewLine;
Dts.Variables["errormessage"].Value = errormessage;
Dts.Variables["errorcount"].Value = 0;
Dts.Variables["warningcount"].Value = 0;

For each Error In a Script Task, if there is an Exception I simply increment the errorcount or the warning count via a line of code such as:

String errormessage = "";
int errorcount = 0;
int warningcount = 0;
errorcount = errorcount + 1;
errormessage = errormessage + "My Custom Error or Warning Message " + System.Environment.NewLine;

Dts.Variables["errormessage"].Value = Dts.Variables["errormessage"].Value.toString() + errormessage;
Dts.Variables["errorcount"].Value = (int)Dts.Variables["errorcount"].Value + errorcount;
Dts.Variables["warningcount"].Value = (int)Dts.Variables["warningcount"].Value + warningcount;

If I am running a Component, and want to track the error. I use an On Failure Script Task.

Subsequently at the end of the package there are two options.
1. Log the results to table (My preferred method)
2. Send an Email to the requisite parties. (the method I describe below)


using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text;
using System.Net.Mail;

int x = 0
x = (int)Dts.Variables["errorcount"].Value + (int)Dts.Variables["warningcount"].Value ;

// send alert only if errorcount + warning count > 0
if (x >0) {

string mailhost ="mailservername";
string alertsubscribers =Dts.Variables["alertsubscribers"].Value.ToString() ;
string subject = "" ;
string body = Dts.Variables["errormessage"].Value;

subject = Dts.Variables["PackageName"].Value.ToString() + " " + Dts.Variables["errorcount"].Value.ToString() + " Errors Encountered, " + Dts.Variables["warningcount"].Value.ToString() + " Warnings Encountered at "+ System.Datetime.Now.toString("yyyy-MM-dd HH:mm:ss")

//create the mail message
MailMessage mail = new MailMessage();

//set the addresses
mail.From = new MailAddress("genericEmailAddress@domain.com","SSIS Alert System");
mail.To.Add(alertsubscribers);

//set the content
mail.Subject = subject;
mail.Body = body;
mail.IsBodyHtml = false;
//send the message
SmtpClient smtp = new SmtpClient();
smtp.Host = Dts.Variables["mailhost"].Value.ToString() ;
smtp.Port = 25;
smtp.UseDefaultCredentials = true;
smtp.Send(mail);
}

Creating Parameters on the fly in SQL Server Reporting Services 2008 (SSRS 2008)

SQL Server Reporting Services is relatively easy to use, however for the first time user it is a little tricky how to report parameters that correspond to parameters/values in your query / stored procedure which generates your dataset.

When prompted to enter a query, simply use a standard t-sql variable without declaring the variable, and SSRS auto-wires the parameters for you.


SELECT
[LoginID]
,[OrganizationNode]
,[OrganizationLevel]
,[HireDate]
,[SalariedFlag]
FROM
[AdventureWorks2008].[HumanResources].[Employee]
WHERE
-- do not declare these variables ahead of time, to see how the report parameters are generated for you
[HireDate] >=  @start_date
AND [HireDate] < @end_date

If you paste the code above in the query editor, the parameters @start_date and @end_date will automatically be created for you as report parameters.

The tricky bit is that when the code is parsed in the Report Wizard, the parameters are initially defined as strings. This creates issues when you need to do operations such as DateAdd on the parameters you are passing.

To handle this slight nuance, I simply declare variables within my queries which use the values from the automatically generated parameters as follows:

DECLARE @start_date DATETIME
DECLARE @end_date DATETIME
-- by passing the report parameter(undefined variable) to a query parameter (defined variable, the type conversions are handled before the query is parsed)
SET @start_date = @rpt_param_start_date
SET @end_date = @rpt_param_end_date


SELECT
[LoginID]
,[OrganizationNode]
,[OrganizationLevel]
,[HireDate]
,[SalariedFlag]
FROM
[AdventureWorks2008].[HumanResources].[Employee]
WHERE
[HireDate] >= @start_date
AND [HireDate] < @end_date

By doing this, when the query is initially parsed, and the system passes in the values as strings, the declarations within your code handles the type casting.  After getting these to work, I then go into each individual report parameter and appropriately modify the data types.

While it is somewhat redundant, it allows you to more rigidly follow the defined work flow in the Report Wizard.

How to: Disable or Enable a SQL Server Job Programatically

How to: Disable or Enable a SQL Server Job

Step 1: Find the JobId 

SELECT
job_id,name,description,enabled
FROM
msdb.dbo.sysjobs
ORDER BY
name ASC

Step 2: Update the enabled flag

— Disable SQL Job

DECLARE @my_job_id UNIQUEIDENTIFIER
SET @my_job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
EXEC sp_update_job @job_id = @my_job_id , @enabled = 0

— Enable SQL Job

DECLARE @my_job_id UNIQUEIDENTIFIER
SET @my_job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
EXEC sp_update_job @job_id = @my_job_id , @enabled = 1

 

Consider:

Create a cursor to loop through all SQL Jobs whenever you want to quickly enable/disable SQL Jobs

Consider creating a log of what jobs are enabled on a given day via bcp or SELECT INTO. This way you can store a log of what jobs are active on a given day, and roll back to that state when necessary.

 

DECLARE @sql NVARCHAR(4000)
DECLARE @cmd NVARCHAR(4000)
DECLARE @timestamp VARCHAR(50)
SELECT @timestamp = CAST(datediff(s,'1970-01-01',getutcdate()) AS VARCHAR)

SET @sql = 'SELECT job_id,name,description,enabled INTO dbo.sysjobs_'+ @timestamp + ' FROM msdb.dbo.sysjobs ORDER BY name ASC ' 
PRINT @sql 

-- EXEC sp_executesql @sql 

 

 

SET @sql = 'SELECT job_id,name,description,enabled FROM msdb.dbo.sysjobs ORDER BY name ASC ' 
SET @cmd = 'bcp "'+@sql + '" out "C:\cya\sysjobs.'+@timestamp+'.dat" -n -S server_name -T'
PRINT @cmd

-- EXEC master..xp_cmdshell @cmd 

 

I had one specific job which I wanted to be able to enable/disable during testing. So i created a batch file that accepts 3 parameters

  • action
  • username
  • password

 
Create a batch file called myjobmanager.bat as follows:


REM ## EXPECTS PARAMETERS -- 1. ENABLE/DISABLE 2. Username 3. Password
osql -S servername -d msdb -i %1_myjob.sql -U %2 -P %3 -n >> %1_myjob.log

Create 2 script files

—- disable_myjob.sql —-

DECLARE @job_id UNIQUEIDENTIFIER
SET @job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
-- DISABLE JOB
EXEC sp_update_job @job_id = @job_id , @enabled = 0

—- enable_myjob.sql —-

DECLARE @job_id UNIQUEIDENTIFIER
SET @job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
-- ENABLE JOB
EXEC sp_update_job @job_id = @job_id , @enabled = 1

To enable or disable the job run the script from the command line.

myjobmanager.bat enable username password

myjobmanager.bat disable username password

 

Of course you can then extend this to better suit your own uses by using SQLCMD and more substitution paramaters, such as dynamically putting server name, jobid, or job name….

 

For more ideas, go into your database and look into what other parameters you may want to change. Possibilities are endless. Change the name of the job, the owner, etc.

EXEC msdb.dbo.sp_helptext 'sp_update_job'

Installing Sample Databases on SQL Server 2008

Installing the Samples for SQL Server 2008 is one of the worst experience I have had with SQL Server to date.

While not the hardest thing in the world to accomplish, accurate documentation and a website with easy to follow instructions would have saved me a lot of time. It took me the better part of 2 hours going through the site and running in circles, only to realize that I could be fully setup in about 15 minutes, had there been easier instructions to follow.

Step 1. 

Download the SQL2008.AdventureWorks_All_DB_Scripts.x86.msi file from Codeplex.

After running the setup, go to the C:\Program Files\Microsoft SQL Server\100\Tools\Samples directory.

Step 2.

Run the following commands from your command line 

cd "C:\Program Files\Microsoft SQL Server\100\Tools\Samples"
buildadventureworks.cmd "%PROGRAMFILES%\Microsoft SQL Server\100\" "." "AdventureWorks"
buildadventureworks.cmd "%PROGRAMFILES%\Microsoft SQL Server\100\" "." "AdventureWorks2008"
buildadventureworks.cmd "%PROGRAMFILES%\Microsoft SQL Server\100\" "." "AdventureWorksDW"
buildadventureworks.cmd "%PROGRAMFILES%\Microsoft SQL Server\100\" "." "AdventureWorksDW2008"
buildadventureworks.cmd "%PROGRAMFILES%\Microsoft SQL Server\100\" "." "AdventureWorksLT"
buildadventureworks.cmd "%PROGRAMFILES%\Microsoft SQL Server\100\" "." "AdventureWorksLT2008"

Use (local) if you have the default instance installed, or for a named instance use “.\INSTANCENAME”

Step 3. 

Get to work…