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

 
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 [...]

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 [...]

How to Disable Or Enable SQL Server Jobs Programmatically

To Enable a Job: 
 
 
UPDATE
 MSDB.dbo.sysjobs
SET
    Enabled = 1
WHERE Name = ‘JAB_NAME’;

 
To Disable a Job: 
 
 
UPDATE
MSDB.dbo.sysjobs
SET
    Enabled = 0
WHERE Name = ‘JAB_NAME’;

SQL – Check If One String Contains Another String (CHARINDEX)

The CHARINDEX() function returns the location of a search string within another string.
For Example: 
 
  Will return 8, which equates to the position of ’sentance’ within the string ‘This is a sentance’.
 
select charindex(’sentance’,‘This is a sentance’) 
 
 
This can be used to check if string A is contained in String B by using the following command: 
 
  The above command will [...]

SQL / T-SQL CASE SENSITIVE SELECT

A very simple method to force case sensitivity in a select statement:

SELECT * FROM DBusers WHERE username = ‘AGENT1′ AND password = ‘paSSworD’ COLLATE SQL_Latin1_General_Cp1_CS_AS

The above statement will only return rows where the password value is exactly paSSworD, case specific.

How To Enable xp_cmdshell On SQL Server 2005

The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations and can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure as shown [...]

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 [...]

How To Allow Incoming Connections In SQL Server 2005

 
How to allow SQL server to allow incoming connections
 
Open SQL server service area configuration
 
 
Start à all programs à SQL server 2005 à configuration tools à SQL server service area configuration
 
Select: Surface Area Configuration for Services and Connections.

 
Under the Database Engine tree, select Remote connections.
 
Select Local and remote connections.
 
I use Using both TCP/IP and named pipes, [...]

SQL Server 2005 Management Studio – How To View Line Numbers

How to view line numbers in SQL 2005 management studio.
YES it is possible and very simple to switch on.
From the main menu bar go to;
Tools –> Options;
Select/Expand the Text Editor tree;
Select the All Languages tree;
Under the Display header, check the Line Numbers box.
Click the OK button and line numbers will immediately be shown on the left of [...]