Displaying posts written in

February 2009

Feb
24
2009
TSQL Development By Vivek | No comments yet

“Set identity_insert on” errors for more than one table

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

Feb
19
2009
SQL Administration By Vivek | 1 comment

Problem with Maintenance Plan in Sql server 2005 After installing SP3

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

Feb
16
2009
TSQL Development By Vivek | No comments yet

Assignment Operators in TSQL 2008

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

Feb
16
2009
TSQL Development By Vivek | 2 comments

Single Statement Variable Declaration

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

Feb
15
2009
SQL Administration By Vivek | No comments yet

Policy-based Management

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