Archive for ‘SQL’

February 20, 2012

SQL Server 2005 Management Studio : Cannot parse script.

I was trying to run a script which i got it from our one of live server. So as usual i tried to run the scripts (file size 112 MB) which i got with the data. Ohhh what the hell, was getting the error  “Cannot parse script.” 😦


was finding a way to sort out this matter. As per that in some of the forum posts it says the SQL Server Management studio is having some limitations with the Query Parser. Just refer this post for more details.

Finally found out we can execute using the sqlcmd prompt as below:

you can find the SQLCMD.exe in the following path:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn

and after that execute the command as below:

sqlcmd -S YOURSQLSERVER\INSTANCENAME -d DataBaseName -i C:\Your Script.sql

By the way the SQLCMD also uses the usual SQL Server 2005 query parser. So it means the above mentioned error is not related to the SQL Server 2005 Parser instead its related to some bug in SQL Server 2005 Management Studio. 🙂

January 16, 2012

Denaaali is out..

Microsoft has released SQL Server 2012 RC 0 (RC – Release Candidate).  The product will come in 3 main editions: Standard, Business Intelligence and Enterprise.

New TSQL Features in SQL Server 2012

For the moment i can mention following features in SQL Server 2012 (Denali),

Sequence

Most of time we used to create auto generated sequence numbers in our tables most of time for primary key fields. In other cases we had to store the generated sequence numbers in a table and then had to increase one by one and as well as we had to store the number generated at last. With this edition we can use our own sequence numbers as below:

CREATE SEQUENCE DemoSequence
START WITH 1
INCREMENT BY 3;

the NEXT VALUE FOR key word is used to get the next sequence number from a sequence.

Paged Data

Most of time we use the paging functionality within a DataGrid when there are lots of records in our database. In this scenario we use the paging functionality in DataGrids.  Now we can use the paging functinality from the database end as below in SQL Server 2012.

SELECT *
FROM Employees
ORDER BY EmployeeID
OFFSET 25 ROWS
FETCH NEXT 25 ROWS ONLY;

Exception Handling

We can throw exception within a SQL Code block.

 -- EXCEPTION HANDLING using THROW in SQL Server 2012 BEGIN TRY DECLARE @VALUE INT SET @VALUE = 1/0 END TRY BEGIN CATCH THROW END CATCH 

Once you execute the above command, you will get the error as below:

Msg 8134, Level 3, State 1, Line 5 Divide by zero error encountered. 

You can download sample databases for SQL Server 2012 RC 0 from here.

SQL Server 2012 RC0 – SSMS Review

SQL Server 2012 editions

August 21, 2011

SQL Server 2008 Evaluation Edition Expired

Once you upgrade an existing SQL Server 2008 evaluation edition to any of registered version you will prompt with the following error when you tried to open the Management Studio.

There is a workaround to sort out this issue : its for your to hack registry keys.

Set CommonFiles registry value to 3 in HKLM\SOFTWARE\\Microsoft\Microsoft SQL Server\100\ConfigurationState Registry.

After that you have to reinstall (if the tools are not installed currently) or run edition Upgrade.

This is identified as a known issue where SQL Server Management Studio is not upgraded when you do any of  SQL Server 2008 edition upgrade.

Reference.

version and edition upgrades.

January 13, 2011

Auto Increment field value by using UPDATE statement.

When creating tables in SQL Server we can specify particular field to be auto incremented, specifically the primary key field. For this purpose MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. By default, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

But if you need to change the value to be started with a specific value, then you can just simply specify as below:

IDENTITY(10,5)

in this case the value will start from 10 and incremented by 5 each time when a record is being getting inserted in to the table.

Ex:

CREATE TABLE Persons(
EmployeeId int PRIMARY KEY IDENTITY(10,5),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255))

I was in a situation where i had to do the same thing as mentioned above to an existing table where do some data exist.

What i did was just inserted the records to the table except the primary key. It means temporarily i just allowed NULL to be inserted to the primary key column. After that just update that primary key values by simply running a update statement as below setting the value the primary key should start with.

DECLARE @counter int
SET @counter = 50
UPDATE [Test].[dbo].[Test]
SET @counter = [EmployeeId] = @counter + 1
January 15, 2010

Error creating package – Failed to save package file “\path” with error 0x80040154 "Class not registered"

When I tried to create a new SSIS package in the BIDS, I received an error saying : “Failed to save package file “\path of the file” with error 0x80040154 “Class not registered”.

It was due to MSXML6 setup package did not get installed in my machine properly or due to some reason it should have corrupted. So you have ‘MSXML 6.0 Parser’ in Add/Remove Programs, click Change and then Repair. If it does not exist in your Add/Remove Programs – rerun the SQL Setup, then it should install it.

November 25, 2008

How do I loop through a record set in a stored procedure?

i came across a situation where i had to loop through a recordset, have to pick some column values and need to insert it to another table.

A sample snippet as below :

DataTable newTable = new DataTable("Sorted");
DECLARE MyCursor CURSOR READONLY FORWARD SELECT id FROM mainNav ORDER BY sort,id 

OPEN MyCursor
READ NEXT FROM MyCursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT id,label,url,sort,mainnavid FROM SubNav

WHERE mainNavID=@id ORDER BY sort,id
READ NEXT FROM MyCursor INTO @id
END
CLOSE MyCursor
DEALLOCATE MyCursor

attached the file script that i have used for the project.

 

-- ========================
-- Inserting Notifications –
-- =========================

ALTER PROCEDURE [dbo].[NotificationJob] AS
declare @TransId Int
declare @ToUserId Int
declare @RequestId Int
declare @FileId Int
declare @NoOfDays int
declare @TransType nvarchar(50)
declare @TransTypeId int
declare @NotificationType nvarchar(50)
declare @Active Int
declare @Description nVarchar (max)
declare @CreatedBy nvarchar(50)
declare @RecordCount Int

set @CreatedBy='DB_JOB'

--opening a cursor to select records from the Transaction table

DECLARE CUR_Notification
CURSOR FOR
SELECT
FileTransaction.TransId,
FileTransaction.UserId,
FileTransaction.FlowID,
FileTransaction.FileId,
FLOOR(CONVERT(real, GETDATE()) - FileTransaction.TransDate),
FileTransactionType.Name,
FileTransactionType.TransTypeId
FROM
FileTransaction
INNER JOIN
FileTransactionType
ON
FileTransaction.TransTypeId = FileTransactionType.TransTypeId
where
FLOOR(CONVERT(real, GETDATE()) - FileTransaction.TransDate) > 12
AND
FileTransactionType.TransTypeId= dbo.GetTransStatusId('Issue')
Or
FileTransactionType.TransTypeId= dbo.GetTransStatusId('Forward')
Or
FileTransactionType.TransTypeId= dbo.GetTransStatusId('AcceptReturn')

-- Opening the Cursor

OPEN CUR_Notification
FETCH NEXT FROM CUR_Notification
INTO @TransId, @ToUserId, @RequestId,@FileId, @NoOfDays, @TransType, @TransTypeId

declare @NotificationDate real

set @NotificationDate=(select CONVERT(real, GetDate()))

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @RecordCount=COUNT(*) FROM FRSNotification
WHERE
FRSNotification.TransID=@TransId
AND
FRSNotification.ToUserID=@ToUserId
AND
FRSNotification.RequestID=@RequestId

-- checking whether is there are any records exists

if @RecordCount=0
exec FRSNotification_Insert @TransId, @ToUserId,
@RequestId, @FileId, @TransTypeId, 1,
@NotificationDate, null, @TransType,
@NotificationDate, @CreatedBy

FETCH NEXT FROM CUR_Notification
INTO @TransId, @ToUserId, @RequestId,@FileId,
@NoOfDays, @TransType, @TransTypeId

END

--cleaning up operations
CLOSE CUR_Notification
DEALLOCATE CUR_Notification
October 29, 2008

SQL Doc™

Document SQL Server 2000, 2005 and 2008 databases without any hassle.

  • Fast, simple database documentation
  • Document one or multiple databases, down to object level
  • Write, edit, and export documentation in easy-to-read HTML files or .chm files for team distribution
  • Includes all cross-database dependencies

download trial version :
SQL Doc

October 24, 2008

How to identify your SQL Server 2005 version and edition

To determine which version of Microsoft SQL Server 2005 is running, connect to SQL Server 2005 by using SQL Server Management Studio, and then run the following Transact-SQL statement:

SELECT SERVERPROPERTY('productversion') as productversion,
SERVERPROPERTY ('productlevel') as productlevel, 
SERVERPROPERTY ('edition') as edition

The results are:

* The product version (for example, “9.00.1399.06”).
* The product level (for example, “RTM”).
* The edition (for example, “Enterprise Edition”).

sample results :

9.00.1399.06 RTM DeveloperEdition

August 21, 2008

Working with System Tables in SQL 2005

Find ALL tables that have columns with an identity property

SQL SERVER 2000:

SELECT OBJECT_NAME(ID),NAME FROM SYSCOLUMNS WHERE COLUMNPROPERTY(ID,name,'IsIdentity')=1

SQLSERVER 2005:

SELECT OBJECT_NAME(OBJECT_ID), NAME FROM SYS.IDENTITY_COLUMNS

Note: SQL Server 2005 stores the last value of the identity property that was generated. To query the last value execute the following query.

SELECT NAME, LAST_VALUE FROM SYS.IDENTITY_COLUMNS

Find ALL columns in a table that are computed columns

SQL SERVER 2000:

SELECT NAME FROM SYSCOLUMNS WHERE ID = OBJECT_ID('TableName') AND ISCOMPUTED=1

SQL SERVER 2005:

SELECT NAME FROM SYS.COMPUTED_COLUMNS WHERE OBJECT_ID = OBJECT_ID'TableName')

Note: The computed column in SQL Server 2005 may be persisted. To narrow down the result set, you could execute the following query:

SELECT * FROM SYS.COMPUTED_COLUMNS WHERE IS_PERSISTED=0

Find ALL database names in a SQL Server instance

SQL SERVER 2000:

SELECT NAME FROM MASTER..SYSDATABASES

SQL SERVER 2005:

SELECT NAME FROM SYS.DATABASES

Note: Many enhancements were made to the database. Query all of the columns in sys.databases to understand the new enhancements like snapshot, etc.

Find ALL Procedures in a Database

SQL SERVER 2000:

SELECT NAME FROM SYSOBJECTS WHERE TYPE='P'

SQL SERVER 2005:

SELECT NAME FROM SYS.PROCEDURES

Note: You can find whether the stored procedure execution is used in replication or if the stored procedure is a startup procedure. Execute the following queries:

SELECT NAME FROM SYS.PROCEDURES WHERE IS_EXECUTION_REPLICATED=1
SELECT NAME FROM SYS.PROCEDURES WHERE IS_AUTO_EXECUTED=0

Find ALL tables in a Database

SQL SERVER 2000:

SELECT NAME FROM SYSOBJECTS WHERE TYPE='U'

SQL SERVER 2005:

SELECT NAME FROM SYS.TABLES

Note: In SQL Server 2005, you can find whether a table is replicated. Execute the following query.

SELECT * FROM SYS.TABLES  WHERE IS_REPLICATED =1 

Find ALL views in a Database

SQL SERVER 2000:

SELECT NAME FROM SYSOBJECTS WHERE TYPE='V'

SQL SERVER 2005:

SELECT NAME FROM SYS.VIEWS

Note: In SQL Server 2005, you can find whether a view is replicated. Execute the following query.

SELECT * FROM SYS.VIEWS WHERE IS_REPLICATED =1

Find ALL Triggers in a Database

SQL SERVER 2000:

SELECT NAME FROM SYSOBJECTS WHERE TYPE='TR'

SQL SERVER 2005:

SELECT NAME FROM SYS.TRIGGERS WHERE PARENT_CLASS=1 

Note: In SQL Server 2005, the triggers can be Assembly trigger (CLR) or a SQL trigger. In addition, we can find whether the trigger is an AFTER trigger or INSTEAD of trigger. Execute the following query:

SELECT NAME FROM SYS.TRIGGERS WHERE TYPE='TA'
SELECT NAME FROM SYS.TRIGGERS WHERE TYPE='TR'
SELECT NAME FROM SYS.TRIGGERS WHERE IS_INSTEAD_OF_TRIGGER=1
 

Find ALL SQL logins in a server

SQL SERVER 2000:

SELECT * FROM MASTER..SYSLOGINS WHERE ISNTGROUP=0 AND ISNTNAME=0 

SQL SERVER 2005:

[SOURCECODE LANGUAGE=”SQL”]SELECT * FROM SYS.SQL_LOGINS [/SOURCECODE]

Find ALL dependencies of the SQL Object in a Database

SQL SERVER 2000:

SELECT * FROM SYSDEPENDS

SQL SERVER 2005:

SELECT * FROM SYS.SQL_DEPENDENCIES 

Find ALL data types in SQL server

SQL SERVER 2000:

SELECT * FROM SYSTYPES 

SQL SERVER 2005:

SELECT * FROM SYS.SYSTYPES 

Find ALL error messages in SQL server

SQL SERVER 2000:

SELECT * FROM MASTER..SYSMESSAGES 

SQL SERVER 2005:

SELECT * FROM SYS.MESSAGES 

Find ALL the database files of the current database

SQL SERVER 2000:

SELECT NAME,FILENAME FROM SYSFILES 

SQL SERVER 2005:

SELECT NAME, PHYSICAL_NAME FROM SYS.DATABASE_FILES 

Find the type of index

SQL SERVER 2000: We have to use indid column to determine the type of index from 0,1 or 255.

SQL SERVER 2005:

SELECT OBJECT_NAME(OBJECT_ID),NAME, TYPE_DESC  FROM SYS.INDEXES WHERE TYPE_DESC ='CLUSTERED'
SELECT OBJECT_NAME(OBJECT_ID),NAME, TYPE_DESC  FROM SYS.INDEXES WHERE TYPE_DESC ='HEAP'
SELECT OBJECT_NAME(OBJECT_ID),NAME, TYPE_DESC  FROM SYS.INDEXES WHERE TYPE_DESC ='NONCLUSTERED'
SELECT OBJECT_NAME(OBJECT_ID),NAME, TYPE_DESC  FROM SYS.INDEXES WHERE TYPE_DESC ='XML'