Drop all tables in a Database

SQL server is providing a nice option to drop all the tables at a stretch. Below is the query to achieve the same.
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
The above command will traverse each table and drop it.
sp_MSforeachtable is an undocumented stored procedure.
We can delete single table too.
EXEC sp_MSforeachtable @command1 = "drop table aa"
This sp will take a sql query as parameter and execute it.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

Database Log file size in SQL Server

Below is the query used to find the log size for each database.

DBCC SQLPERF ( LOGSPACE )

Regards,

Venkatesan Prabu .J

Substring in a string in SQL Server

Charindex is a nice function used to check the existence of a substring in a string. Below is the sample code to check the existence of string.
declare @str varchar(10)
declare @substr varchar(5)
set @str = 'Venkat'
set @substr='nak'
if charindex(@substr,@str) >0
Print 'String available'
else
Print 'String not available'

Regards,

Venkatesan Prabu .J

Change row name in SQL Server

We know how to change the column in the result set. It can be achieved using alias name. Have you tried to change row name in SQL server. Check this query,
CREATE TABLE VENKAT1(ID INT,NAM VARCHAR(10))
INSERT INTO VENKAT1 VALUES(1,'VENKAT')
INSERT INTO VENKAT1 VALUES(2,'ARUN')
SELECT * FROM VENKAT1
SELECT
CASE WHEN NAM='VENKAT' THEN 'SANTHI'
ELSE NAM
END
FROM VENKAT1 Happy Learning!!!
Regards,
Venkatesan Prabu .J

Compare two tables in SQL Server

I have come through a nice article to compare two tables, whether its same or different.
Simple and Spectacular article and wish to write the same in my blog.
Below code will be used to compare two tables in SQL Server
CREATE TABLE VENKAT1(ID INT,NAM VARCHAR(10))
CREATE TABLE VENKAT2(ID INT,NAM VARCHAR(10))
INSERT INTO VENKAT1 VALUES(1,'VENKAT')
INSERT INTO VENKAT1 VALUES(2,'ARUN')
INSERT INTO VENKAT2 VALUES(1,'VENKAT1')
INSERT INTO VENKAT2 VALUES(2,'ARUN')
SELECT * FROM VENKAT1
SELECT * FROM VENKAT2
SELECT
CASE WHEN COUNT(*) = 0 THEN 'Same' ELSE 'Different' END
FROM (
(
SELECT * FROM VENKAT1
EXCEPT
SELECT * FROM VENKAT2
)
UNION (
SELECT * FROM VENKAT2
EXCEPT
SELECT * FROM VENKAT1
)
) dv

Happy Learning!!!
Regards,
Venkatesan Prabu .J

Collation in SQL Server

Collation in sql server is an importan property to handle the case sensitiveness of the data.
Collation on the server can be forced in two ways either,
1. While installation or
2. During our operation or business logic execution.
You can check the collation details by right clicking your server properties.
If we want to assign a specific collation for your database. In that case, you can achieve it using the below query,
ALTER DATABASE VenkatDB COLLATE Latin1_General_CI_AS
To know the collation types in your server. You can run the below query,
SELECT * FROM fn_helpcollations()
Microsoft have provided a nice function which will fetch all the collations available in your server.

Happy Learning!!!

Regards,

Venkatesan Prabu .J

Disable trigger to fire other triggers

Sometimes, we will use some trigger to perform a particular operation inturn that trigger may fire another trigger. It's casual problem and lets see how to stop trigger to fire other triggers.
1. Right click server properties->Advanced->Allow triggers to fire others property to false.


Else we can achieve it using T-SQL statement too. Lets see the second method to achieve the same.




Execute the below queries to achieve the same,

Code Snippet

EXEC sp_configure 'nested triggers', 0

Code Snippet

RECONFIGURE

Happy Learning!!!
Regards,
Venkatesan Prabu. J

T-SQL Challenges in SQL Server

Question: I have a table with 1 columns, and I would like to change the predefined value 'NULL' to other value, that can be the same for all rows in the table. How can we achieve this.
Solution:

Code Snippet

create table #a (nam varchar(10)) insert into #a values('aa') insert into #a values('ba') insert into #a values('ca') with cte as ( select 1 as id ) update #a set nam = (select * from cte) select * from #a

Happy Learning!!!

Regards,

Venkatesan Prabu. J

Column filteration based on Data type

I got a peculiar question, Is it possible to fetch the columns which are not varchar or any other datatype. OOPs its new to me and sql server 2005 provides a simple feature name Information schemas. Using this, we can fetch the information. Below is the query,

Code Snippet

select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE not in ('varchar') and table_name='venkat'

Regards,

Venkatesan Prabu. J

Columns into rows in sql server

SQL Server 2005 is providing a cool feature Unpivot to convert columns data into rows. But, in our legacy system we dont have this feature. Inturn, we have to create some temporary tables to achieve the same. I have tried it to convert columns into rows. There are thousands of ways to achieve this task. I have tried one among them,

Code Snippet

create table aaaa(id int,nam varchar(10),city varchar(10)) insert into aaaa values(1,'aa','sydney') insert into aaaa values(2,'bb','delhi') insert into aaaa values(3,'cc','chennai') select * from aaaa create table #temp1(id int, nam varchar(10)) insert into #temp1 select id,nam from aaaa create table #temp2(id int, nam varchar(10)) insert into #temp2 select id,city from aaaa

Below is the ouput we are getting for the table aaaa


We are trying to convert it into rows, so the output should resemble similar to the below screenshot.

Code Snippet

select id,nam from #temp1 union all select * from #temp2 order by #temp1.id

Happy Learning!!!.
Regards,
Venkatesan Prabu. J

Rows to Columns in SQL Server

In this article, Lets see how to convert your table rows into column wise. We can achieve it using pivot operation in SQL Server 2005. I achieved the same with some other logics.

Code Snippet

create table VenkatTable (id int, question varchar(20),answer varchar(20)) insert into VenkatTable values(1,'Name','Manoj') insert into VenkatTable values(1,'Qualification','BE') insert into VenkatTable values(1,'Nationality','Indian') insert into VenkatTable values(2,'Name','Venkat') insert into VenkatTable values(2,'Qualification','BE') insert into VenkatTable values(3,'Qualification','BE') select * from VenkatTable

The above queries will create a table as below,



I need a result set as below screen shot. It can be achieved using pivot operator in SQL Server 2005. I have tried with some other logic to achieve the same. Lets see how to achieve the below output.

Code Snippet

SELECT id, [Name]= (SELECT answer FROM VenkatTable WHERE question = 'Name' and id=m.id), [Qualification]= (SELECT answer FROM VenkatTable WHERE question = 'Qualification' and id=m.id) , [Nationality]= (SELECT answer FROM VenkatTable WHERE question = 'Nationality' and id=m.id) FROM VenkatTable m GROUP BY id

Happy Learning!!!
Regards,
Venkatesan Prabu .J

DDL Triggers in SQL Server 2005

DDL Trigger -> Its a new concept introduced in SQL Server 2005.Its used to control and monitor the DDL statements executed in our database.Consider the below example,I have created a DDL trigger for drop statement. Now, if a user tried to drop my tables. It will be automatically throw an error message.

Code Snippet

CREATE TRIGGER DDLTriggertoSaveTable ON DATABASE FOR DROP_TABLE

AS PRINT 'M No way to delete the tables in the Database?'

Now, execute the below statement,

Code Snippet

Drop Table VenkatTestDLLTriggers

You will get a message indicating "Cannot drop the tables in the database.

Happy Learning!!!

Regards,

Venkatesan Prabu .J

Installation problem in SQL Server

I am having SQL Server installed in my machine and i need to uninstall the same for some of the following reasons,

1. To move to an upgraded versions of sql server

2. Problem in SQL Server

In that case, we will find some problem in upgrading or repairing the existing server. In that case, Microsoft have given a nice article to resolve our problems. Please check the below KB URL,

http://support.microsoft.com/kb/909967

Happy Learning!!!
Regards,
Venkatesan prabu. J

To fetch all the constraints in SQL Server

Lets discuss one of the interesting features in SQL Server 2005 - Information_Schema.
Its a cool feature available in SQL Server 2005. Inorder to avoid direct access on the system tables. Microsoft have intoduced a layered structure to access the system related information. They have used views to achieve the same.
In the below query, I am trying to fetch all the Primary key and foreign key constriants available in the database.

Code Snippet

select CONSTRAINT_NAME,TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Happy Learning!!!

Regards,

Venkatesan prabu .J

Retrieve Owner of the database

Let me list down, how to retrieve the database owner of your database.
1. Right click database properties and -> General -> owner you can view the database owner of this particular datbase.

2. Else we can write a T-SQL Statement to acheive the same.

Code Snippet

select suser_sname(owner_sid) from sys.databases

Happy Learning!!!

Regards,

Venkatesan Prabu .J

SSIS error - Part 8

[OLE DB Destination [70]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005
Description: "Unspecified error".
[OLE DB Destination [70]] Error: There was an error with input column "id" (410) on input "OLE DB Destination Input" (83). The column status returned was: "The value
violated the integrity constraints for the column.".
[OLE DB Destination [70]] Error: The "input "OLE DB Destination Input" (83)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB
Destination Input" (83)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (70) failed with error code 0xC0209029. The identified component returned an error from the
ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Solution : The problem is due to insertion of already existing data into the primary key column.
Please delete those existing data or insert some valid data to resolve this problem
Regards,
Venkatesan Prabu .J

SSIS error - Part 7

[OLE DB Destination [1313]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot
insert the value NULL into column 'create_date', table 'Master.dbo.VenkatTable'; column does not allow nulls. INSERT fails.".
Problem: The problem is due to insertion of invalid data into the column.
Solution: Since, the column wont allow null value, either you have to change the type of the column by using alter statement else provide some valid integer/character during data insertion.
Happy learning!!!
Regards,
Venkatesan prabu .J

SSIS Error - Part 6

[Execute SQL Task] Error: Executing the query "Your query " failed with the following error:
"Multiple identity columns specified for table 'Tmp_VenkatTable'. Only one identity column per table is allowed.". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The error is due to the identity column. SQL wont allow more than one identity column in the table.
Solution:
Please remove the excessive identity column from the table.
Happy Learning!!!
Regards,
Venkatesan Prabu .J

SSIS Errors - Part 5

While transferring data from source to destination, we used to see the below warnings in our SSIS packages,
[DTS.Pipeline] Warning: The output column "A" (6643) on output "Union All Output 1" (2177) and component "Joining the Table" (2175) is not subsequently used in the Data Flow
task. Removing this unused output column can increase Data Flow task performance.
Solution: I have used a "Union All" task which will fetch more data and am not using all the data in the destination inturn, am using some selected columns in the destination. SSIS indicating us to remove the unused output column from Union All task. Inturn, this will increase the performance of our SSIS package.
Happy Learning!!!
Regards,
Venkatesan Prabu .J

SSIS Errors - Part 4

If we try to connect Sybase database, we will get following warnings,
[OLE DB Source [1]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
Its because of default code page file which should be available in "C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles" for that particular server.
If the code page file is not available then, your SSIS package will show the warning "Unable to load the file.

Below screen shot shows the default code page available in your machine.










To resolve this problem, Right click your OLEDB Source or Detination(Sybase) and enable the property "AlwaysuseDefaultcodepage" to true.

Happy Learning!!!

Regards,
Venkatesan prabu .J

SSIS errors - Part 3

While transferring data from source to destination, we used this get this error.
[OLE DB Destination [2357]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object
Reason and Solution:
The problem is due to invalid insertion of data which affect the integrity property of the data. Suppose, If the user tries to insert the data which already exists in the column(primary key column). Then we will get the error.
To resolve this, either we need to remove the existing or new data or we need to modify the data before inserting into the primary key column.
Happy Learning!!!
Regards,
Venkatesan prabu .J

Unknown things in Identity_insert statement

Unknown things about Identity Insert:
Considering, am having a requirement to insert the data in the identity column as below,

Code Snippet

create table VenkatTable (id int identity,[name] varchar(100))

insert into VenkatTable values (10,'Santhi')

You will get an error,
"An explicit value for the identity column in table 'VenkatTable' can only be specified when a column list is used and IDENTITY_INSERT is ON."
Solution:
The problem is with the identity column. It won't allow us to insert the data explicitly inturn we need to switch off the identity property to implement our requirement.

Code Snippet

set identity_insert VenkatTable on

insert into VenkatTable values (10,'Santhi')

On Executing the above statement, am getting the same error.. OOOOPS!!!

"An explicit value for the identity column in table 'VenkatTable' can only be specified when a column list is used and IDENTITY_INSERT is ON."

Let's analyze the problem once again. I've tried with the below statement.

Code Snippet

insert into VenkatTable(id,[name]) values(10,'Santhi')

Yahoooo, its inserting the data now. SQL request us to specifically pointout the column names in the insert statement.
After our requirement, we need to Switch off the identity_insert property by using the below statement.
set identity_insert VenkatTable off
Happy Learning!!!
Regards,
Venkatesan prabu .J

SSIS Errors - Part 2

While transfering the data from Source to Destination, if there is data type mismatch we used to get the following error.
Messages
* Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

The problem is with the matching columns between source and destination

Considering am having id column in source as varchar(10) and id column in the destination is int. Then if we try to execute the package.Obviously, we will get an error.

Solution: 1. We need to change the data type in the source table.

2. We need to write some conversion scripts for varchar to int type for the id column. You can prefer Derived column task to do this conversion.

Happy Learning!!!

Regards,

Venkatesan Prabu .J

SSIS Error

Sometimes, On executing Execute SQL task with huge tables as input will throw the following exception,


"The query has been cancelled because the estimated cost of this query (Integer) exceeds the configured threshold of (Integer). Contact the system administrator.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."


Reason:


It's because the current query execution exceeds the threshold server execution time.


Solution:


You can opt different solutions for this problem. Let me explain two types of solutions among them,
1. Setting the Query governor value to higher limit.
2. Right click the database properties - >connections -> use query governor to prevent long running queries.









3. Increase the value if already exists.
4. Else, you can write a T-SQL query to achieve the same,
SET QUERY_GOVERNOR_COST_LIMIT 1000(I have given 1000 as sample)


2. If you dont have administrative right to achieve the same you can opt less number of queries or less number of accessing tables.
Regards,
Venkatesan prabu .J

Microsoft MVP

Wish to share a great news..

On July 1, I have got a nice news from microsoft regarding my MVP award. I have been selected as Microsoft MVP for the year 2008-2009 on SQL Server.

It's really shocking and i am wondering on seeing myself as a great MVP.

Happy learning!!!

Sybase to SQL Server using SQL Server Integration services(SSIS)

While connecting with a remote sybase database, we used to find some problems in connecting the table to retrieve the data. I have tried to connect my sybase database by opening a OLEDB connection manager. Check the screen shot below,

SSIS doesn't allow to preview the data. I have tried to click "preview button. But, am getting a dataflow task error.

I tried to click "Ok" button, am unable to create a OLEDB source task ended up with an error.

Reason for the error: I started exploring the reason for the error and found that the problem is with the "dbo" string which gets appended with my sybase table. While contacting the server, my ssis couldn't find the table like "dbo.tableA". Below is the error string which i have got for this problem.

"Error at Data Flow Task [OLE DB Source [211]]: An OLE DB error has occurred. Error code: 0x80040E37.An OLE DB record is available. Source: "ASE OLE DB Provider" Hresult: 0x80040E37 Description: "[Native Error code: 102][DataDirect ADO Sybase Provider] Incorrect syntax near '.'.".
Error at Data Flow Task [OLE DB Source [211]]: Opening a rowset for ""dbo"."TableA"" failed. Check that the object exists in the database. "

Solution:

Change the dataaccess mode to SQL Command, Just type your queries parsed it.

Click "Ok". It will show a validation error. "No worries".

You SSIS OLEDB source is ready.

Happy Learning!!!

Regards,

Venkatesan Prabu .J