TSQL Development No comments yet

“Set identity_insert on” errors for more than one table

By Vivek on February 24th, 2009

I was trying to remove the Identity inserts for 4 tables all at once but I keeping getting errors like Cannot remove the Identity insert on table 2 as the table1 already has identity insert on.

So after digging into the MSDN I was surprised to see the following lines

“At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.”

SQL Administration 1 comment

Problem with Maintenance Plan in Sql server 2005 After installing SP3

By Vivek on February 19th, 2009

Problem:

I was trying to configure the backups on Sql Server using the Maintenace Plan.

When I opened it . I got the following error

TITLE: Microsoft SQL Server Management Studio

——————————

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

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

——————————

Invalid column name ‘from_msx’.
Invalid column name ‘has_targets’. (Microsoft SQL Server, Error: 207)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476

——————————
BUTTONS:

OK
——————————

Analysis:

The resource DB has not been Upgraded to  SP2 version 9.00.3042.00.

The following Script should prove it.

SELECT SERVERPROPERTY(‘ResourceVersion’) as ResourceDB, SERVERPROPERTY(‘ResourceLastUpdateDateTime’) as ResourceDBLastUpdate,

SERVERPROPERTY(‘ProductVersion’) as Ver, SERVERPROPERTY (‘ProductLevel’) as SP;

GO

A specific “msx” table found in the master database, that holds details of maintenance jobs, did not have certain additional columns added to it after the upgrade to SP2.

Solution :

There are 2 solutions to the problem depending on the risk .

Solution 1

Creating the jobs instead of Maintenance Plans for important Tasks

Solution 2

Go to the SQL Install Folder

“C:\Program Files\Microsoft SQL Server\<instance-installation -folder>MSSQL\Install”

like

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install

and run the sysdbupg.sql script.

That should add the colums to the maintenance tables and the maintenance tasks can be created.

TSQL Development No comments yet

Assignment Operators in TSQL 2008

By Vivek on February 16th, 2009

TSQL has improved its assignment Operators in the C language convention.

In SQL SERVER 2000 or SQL SERVER 2005 the assignment operators are written as

Declare @i int

Declare @s varchar(100)

set @i = 10

set @s = ‘WORD’

set @i = @i  * 40

set @s = @s + ‘PRESS’

In SQL SERVER 2008

Declare @i int = 10

Declare @s varchar(100) = ‘WORD’

set @i *=  40

set @s += ‘PRESS’

select @i,@s

In the sameway the other operators that can be used are

-= Subtraction

/= Division

%= Modulo

^= Bitwise exclusive OR

|= Bitwise OR

&= Bitwise AND

TSQL Development 2 comments

Single Statement Variable Declaration

By Vivek on February 16th, 2009

A new feature in Sql server 2008 TSQL is the Single Statement Variable Declaration.

What is this?

In Sql Server 2000 or Sql Server 2005 the declare statements are written as follows

Declare @i int,@j int ,@k int

set @i = 100

set @j = 200

set @k = 300

The Above Statement in Sql server 2008 can be written as

Declare @i int = 100

Declare @j int = 200

Declare @k int = 300

So the Declaration and Initialization is in a single Step!!!.

SQL Administration No comments yet

Policy-based Management

By Vivek on February 15th, 2009

Policy-Based Management(PBM) is the expansion of advanced DDL trigger mechanisms on the Server. Think of the following things

a. How do I allow the users to create the table in specific naming conditions like starting only with ‘T’?

b. How about checking the availability of the latest Sql Server backups?

c. How about limiting the number of CPU in a sql server?

Yes there are countless of server management applications with the PBM.Each POLICY is based on a CONDITION and each CONDITION decides the state of a specific target(like databases,tables,stored procedures etc.) with respect to a particular FACET.

There are a list of builtin policies available on the Sql server 2008 by default. They can be imported from the following location C:\Program Files\Microsoft SQL Server\100\Tools\Policies. All the policies can be imported and exported as they are configured in XML.The Developers can themselves create their own facets depending on their Organisation policies.

More on application of policies coming soon..