SQL – How To Restore A Suspect Database SQL Server 2005

Occasionally an SQL database can lock-down in suspect mode. There are many causes, but the main is an expected shot-down or power loss. I had a slightly loose fitting CPU fan (and I mean ightly, one of the pins was a little less secure than the rest) in a server running SQL server 2005. It took a morning of stripping back the chassis to find it. Since re-seating the fan pins all is well.

To cut a long story short, the following script allowed me to restore the suspect database in its entirety. SQL server 2005 comes with the DB status of Emergency. This can be used to alter the status from Suspect mode (in which the database is inaccessible), to Emergency mode where you can gain access to the database and retrieve data from the tables along with those all important stored procedures and other scripts.

Once in Emergency mode you can re-run DBCC checkDB to check the integrity of all the pages and structures that make up the tables.
This script uses the REPAIR_ALLOW_DATA_LOSS argument which tries to repair all reported errors. These repairs can cause some data loss. Hopefully you have a recent back up, if not take backups from the database while you can access it in emergency mode!
REPAIR_ALLOW_DATA_LOSS is used to ensure the database is returned to a structurally and transitionally consistent state

This cannot be undone.

Here is the script:

EXEC sp_resetstatus DATABASENAME;
ALTER DATABASE DATABASENAME SET EMERGENCY
DBCC checkdb(DATABASENAME)
ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (DATABASENAME, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DATABASENAME SET MULTI_USER

SQL – How To Shrink Un-shrinkable Database Log / Database Log Won’t Shrink SQL Server 2005

To shrink the transactional logs in SQL Server 2008 follow this link: http://robbamforth.wordpress.com/2010/06/24/sql-%e2%80%93-how-to-shrink-un-shrinkable-database-log-database-log-won%e2%80%99t-shrink-sql-server-2008/

The following should only be considered as a last resort to force the log file to shrink. I suggest you take a full database back-up first.

The DBCC LOGINFO can be run against the database. This will give you information about your virtual logs inside your transaction log.
In particular look at the Status column. Status = 2 indicates the portions of the log that are in use, status = 0 are not in use.

DBCC LOGINFO (DATABASE_NAME)

Step 1:
Attemp to shrink the problematic log file and back up the database with TRUNCATE_ONLY.

USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N’DATABASE_NAME_LOG’ , 0, TRUNCATEONLY)
GO

BACKUP LOG [DATABASE_NAME]
WITH TRUNCATE_ONLY

Step 2:
Create a temporary table and insert a record.

CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)
GO

Step 3:
Run the following loop to update the table.

SET NOCOUNT ON
DECLARE @Index INT
SELECT @Index = 0
WHILE (@Index < 20000)
BEGIN
   UPDATE MyTable SET MyField = MyField WHERE PK = 1
   SELECT @Index = @Index + 1
END
SET NOCOUNT OFF

The log rows with status 2 should now be available with statis = 0.

Step 4:
Re-run the shrink command.

USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N’DATABASE_NAME_LOG’ , 0, TRUNCATEONLY)
GO

BACKUP LOG [DATABASE_NAME]
WITH TRUNCATE_ONLY

– Drop the temporary table as no longer required
drop table MyTable

The log file should now have shrunk successfully.

Source: http://www.broad-lea.com/sql_server/sql_reduce_log_size.html

SQL – Server blocked access to procedure ‘sys.sp_OACreate’

Error:

SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, see “Surface Area Configuration” in SQL Server Books Online.

A user logged in with administrator privileges can enable the use of ‘Ole Automation Procedures’ b with sp_configure .
Advanced options need to be set before running sp_configure.

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO

You should see the following output:

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Ole Automation Procedures’ changed from 0 to 1. Run the RECONFIGURE statement to install

Alternatively this can be done in SQL server 2005 Surface Area Configuration through the menu options :

surface area configuration for features -> OLE Automation -> Enable OLE automation

You will now be able to re-run the previously blocked procedure with success.

SQL – change the collation of a database

 

The following code shows how to alter the collation of a database.
The database must be set to single user mode to run the update. Remember to set the database back to multiuser mode if required.

ALTER DATABASE [DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DATABASE] COLLATE Latin1_General_CI_AS
ALTER DATABASE [DATABASE] SET MULTI_USER

SQL – Get Exclusive Lock – Fix for The database could not be exclusively locked to perform the operation error

In order to run certain update commands against an SQL database you need to gain exclusive lock. This is done by setting the database to single user mode.
The command is:

ALTER DATABASE [DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

To set the database back to multi user mode you must remember to run the following code once you’ve completed the tasks requiring single user mode:

ALTER DATABASE [DATABASE] SET MULTI_USER

The following code shows how to alter the collation of a database which requires single user mode.

ALTER DATABASE [DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DATABASE] COLLATE Latin1_General_CI_AS
ALTER DATABASE [DATABASE] SET MULTI_USER

 

How To Copy SQL Server 2005 Jobs From One Server To Another

 

This example shows how to copy a scheduled backup job from one instance of SQL server 2005 to another.

In Microsoft SQL Server Management Studio, expand the SQL Server Agent tree.

Expand the jobs tree.

Here you will see a list of all the jobs currently stored on the server.

 

Right-click on the job that you want to copy over to a second server.
Select Script Job as –> Create To –> New Query Editor Window.

 

This will open a new window in the management studio containing auto-generated code.

Connect to the Second server, which will be the destination for the copied job.

Open up a new tab in the management studio – Copy the entire auto-generated code from the previous step and paste it into the new tab.

 

Below is the auto-generated code ready to execute on the new server.
Execute the whole code to continue.

 

Refresh and expand the Jobs tab on the second server, all being well the copied job will be displayed in the job list.

 

Right-click the job and select Properties to view the details.

You will see that the steps, schedules, alerts, notifications etc. will have been copied across. The job will now run on server 2 in the same way as it ran on server 1.

SQL – Output Data from SQL Server To File – BCP

The following BCP command selects data from a table called CUSTOMERS in the DATA database and exports it to a file called FOO_REPORT.CSV:

declare @bcpcommand           varchar(1000)

 

SET @bcpcommand = ‘bcp “SELECT id, title, forename, surname, product FROM DATA.dbo.customers where product = ”FOO”” queryout C:\reports\FOO_REPORT.CSV -c -T”‘

EXEC master..xp_cmdshell @bcpcommand

 You can also output to .XLS and .TXT.

JAVA SQL SERVER 2005 ODBC / JDBC CONNECTION

 

This artice explains how to create a connection to a SQL Server 2005 database from a JAVA application.

Step 1:
Download the JDBC driver from the folloiwing Microsoft site:
http://www.microsoft.com/downloads/details.aspx?FamilyID=e22bc83b-32ff-4474-a44a-22b6ae2c4e17&displaylang=en

Microsoft installation instructions:

1. Download sqljdbc_<version>_enu.exe to a temporary directory.
2. Run sqljdbc_<version>_enu.exe.
3. Enter an installation directory when prompted. We recommend that you unpack this zip file in %ProgramFiles% with the default directory: “Microsoft SQL Server 2005 JDBC Driver”.

 

Step 2:
Locate the file called sqljdbc4.jar within the installation directory for the JDBC driver. Currently the JAR file can be found in %Microsoft SQL Server 2005 JDBC Driver\sqljdbc_2.0\enu.
Copy this JAR file into the class path of your JAVA application.

If using Netbeans, copy sqljdbc4.jar to the project folder in windows explorer.
In Netbeans – right-click the project in the projects window and select properties.
Select the Libraries catagory.
On the Compile tab click Add JAR/Folder.
Select sqljdbc4.jar and click open.
Click OK.
sqljdbc4.jar is now in the class path of the current JAVA application.

 

Step 3: CREATE A CLASS IN YOUR CURRENT APPLICATION CALLED MyConnection
The myConnection class instantiates the connection the SQL Server using the JDBC.

Enter the code exactlyas it appears below omitting your information for the following:

USERNAME = your SQL account username
PASSWORD = your SQL account password
SERVERNAME = your SQL servername e.g. SQL-SERV-1. The IP address of the SQL server can be used instead.
DATABASENAME = the name of the database connecting to.

 

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

import java.sql.Connection;

import java.sql.DriverManager;

 

class MyConnection {

  public static Connection getConnection() throws Exception {

     

      Connection con = null;

      try{

      SQLServerDataSource ds = new SQLServerDataSource();

      ds.setUser(“USERNAME”);

      ds.setPassword(“PASSWORD”);

      ds.setServerName(“SERVERNAME”);

      // ds.setServerName(“xxx.xxx.x.xx”);

      ds.setPortNumber(1433);

      ds.setDatabaseName(“DATABASENAME”);

      con = ds.getConnection();

     

      }catch(Exception e){

          e.printStackTrace();

      }

     

      return con;

 

    }

} 

 

 

STEP 5: Quering the database using a class called JDBCTest. 

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

/**

 *

 * @author Rob.Bamforth

 */

public class JDBCTest {

 

    static Connection theConn;

   

    public JDBCTest(){

       

        try {       

                    theConn = MyConnection.getConnection();

                    ResultSet rs;

                    Statement stmt;

 

String sql =  “select top 100 title, forename, surname from DATA.DBO.CUSTOMERS”;

                    stmt = theConn.createStatement();               

                    rs = stmt.executeQuery(sql);

                    while (rs.next()) {                                                

                                               

                        System.out.println(

                            “Title: ” + rs.getString(1).trim() +

                            ” Forename: ” + rs.getString(2).trim() +

                            ” Surname: ” + rs.getString(3).trim()

                        );                                             

                       

                    }   

                } catch (SQLException ex) {

                    ex.printStackTrace();

                } catch (Exception ex) {

                    ex.printStackTrace();

                }

       

    }           

   

    public static void main(String args[]) {

        new JDBCTest();

    }

       

}

 

The above code selects the top 100 title, forename, surname from the database DATA.DBO.CUSTOMERS.

 

rs.getString(1).trim() returns eliment 1 in the select list.  

rs.getString(2).trim() returns eliment 2 in the select list.

rs.getString(3).trim() returns eliment 3 in the select list etc.

 

 

Select column names and column data from SQL table

In SQL Management Studio Select the database environment that contains the table that you want to query.
The following command displays all the columns in all the table in thje current database:

select * from information_schema.columns

 You will notice that this result set returns usefull data on the table.

To select this information from a specific table in the  database,
use the following command:

select * from information_schema.columns

where TABLE_NAME = ‘YOURTABLE’

  where YOURTABLE is the name of the table you wish to select on.

SQL Server 2005 Database Mail – sp_send_dbmail

STEP 1 Create Email Account in SQL Server:

– CREATE ACCOUNT

EXECUTE msdb.dbo.sysmail_add_account_sp

      @account_name = ‘ACCOUNT1′,

      @description = ‘Mail account for ACCOUNT1 Database Mail’,

      @email_address = ‘EMAIL@ACCOUNT1.co.uk’,

      @display_name = ‘SQL EMAIL’,

@username=‘USER’,

      @password=‘PASS’,

      @mailserver_name = ‘mail.ACCOUNT1.co.uk ‘

 

STEP 2 Creat Email Profile in SQL Server:

– CREATE PROFILE

EXECUTE msdb.dbo.sysmail_add_profile_sp

       @profile_name = ‘ACCOUNT1_PROFILE’,

       @description = ‘Profile used for ACCOUNT1 database mail’

 
STEP 3 Link email account to email profile:

– LINK ACCOUNT TOPROFILE
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp   
@profile_name = ‘ACCOUNT1_PROFILE’,
@account_name = ‘ACCOUNT1′,
@sequence_number = 1

 

STEP 4 Send email:

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = ‘ACCOUNT1_PROFILE’,

    @recipients = ‘email@test.co.uk’,

    @subject = ‘TEST’,

    @body = ‘THIS IS A TEST MESSAGE’,

    @body_format = ‘HTML’;

 

The possible sp_send_dbmail variables arelisted below:

 

sp_send_dbmail [ [ @profile_name = ] ‘profile_name’ ]    
[ , [ @recipients = ] ‘recipients [ ; ...n ]‘ ]    
[ , [ @copy_recipients = ] ‘copy_recipient [ ; ...n ]‘ ]    
[ , [ @blind_copy_recipients = ] ‘blind_copy_recipient [ ; ...n ]‘ ]    
[ , [ @subject = ] ‘subject’ ]    
 [ , [ @body = ] ‘body’ ]    
 [ , [ @body_format = ] ‘body_format’ ]    
[ , [ @importance = ] ‘importance’ ]    
[ , [ @sensitivity = ] ‘sensitivity’ ]    
[ , [ @file_attachments = ] ‘attachment [ ; ...n ]‘ ]    
[ , [ @query = ] ‘query’ ]    
[ , [ @execute_query_database = ] ‘execute_query_database’ ]    
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]    
[ , [ @query_attachment_filename = ] query_attachment_filename ]    
[ , [ @query_result_header = ] query_result_header ]    
[ , [ @query_result_width = ] query_result_width ]    
[ , [ @query_result_separator = ] ‘query_result_separator’ ]    
[ , [ @exclude_query_output = ] exclude_query_output ]    
[ , [ @append_query_error = ] append_query_error ]    
[ , [ @query_no_truncate = ] query_no_truncate ]    
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]