Muhammad Abbas - DB & BI Professional

[Informatica] How to execute one or more mappings from workflow

2012-01-23T10:26:00.000-08:00

There may be scenarios that you need to execute one or more mapping from eniter workflow which may contains number of mappings.

Here is the way of doing that

dos2unix olapjoblist.properties
cd $INFA_HOME/bin/
while read line
do

   test=$( ./pmcmd starttask -sv $INTEGRATION_SERVICE -d $DOMAIN_NAME -uv INFA_USER -pv INFA_PASS -f $FOLDER_NAME -paramfile $parameterFile -w wf_olapconsolidation -wait $line)


   if [ "$?" != 0 ]; then
    # handle error
    echo "Summary: Could not execute task."
   else
    # no error
    echo "Summary: Task executed successfully."
   fi
   echo "Error Code:$?"
   echo "Description: $test"

done < $currentpath/olapjoblist.properties

so olapjoblist.properties if a file will all the session names with it

[Oracle] How to optimize queries - for Beginners

2012-01-12T00:49:00.001-08:00

D.1 Optimizing Single-Table Queries

To improve the performance of a query that selects rows of a table based on a specific column value, create an index on that column. For example, the following query performs better if the NAME column of the EMP table has an index.

SELECT * 
FROM EMP 
WHERE NAME = 'Smith';

D.2 Optimizing Join Queries

The following can improve the performance of a join query (a query with more than one table reference in the FROM clause).

D.2.1 Create an Index on the Join Column(s) of the Inner Table

In the following example, the inner table of the join query is DEPT and the join column of DEPT is DEPT#. An index on DEPT.DEPT# improves the performance of the query. In this example, since DEPT# is the primary key of DEPT, an index is implicitly created for it. The optimizer will detect the presence of the index and decide to use DEPT as the inner table. In case there is also an index on EMP.WORKS_IN column the optimizer evaluates the cost of both orders of execution;DEPT followed by EMP (where EMP is the inner table) and EMP followed by DEPT (where DEPT is the inner table) and picks the least expensive execution plan.

SELECT e.SS#, e.NAME, d.BUDGET
FROM EMP e, DEPT d 
WHERE e.WORKS_IN = DEPT.DEPT# 
AND e.JOB_TITLE = 'Manager';

D.2.2 Bypassing the Query Optimizer

Normally optimizer picks the best execution plan, an optimal order of tables to be joined. In case the optimizer is not producing a good execution plan you can control the order of execution using the HINTS feature SQL. For more information see the Oracle9i Lite SQL Reference.
For example, if you want to select the name of each department along with the name of its manager, you can write the query in one of two ways. In the first example which follows, the hint /++ordered++/ says to do the join in the order the tables appear in the FROM clause with attempting to optimize the join order.

SELECT /++ordered++/ d.NAME, e.NAME
FROM DEPT d, EMP e
WHERE d.MGR = e.SS#

or:

SELECT /++ordered++/ d.NAME, e.NAME 
FROM EMP e, DEPT d 
WHERE d.MGR = e.SS#

Suppose that there are 10 departments and 1000 employees, and that the inner table in each query has an index on the join column. In the first query, the first table produces 10 qualifying rows (in this case, the whole table). In the second query, the first table produces 1000 qualifying rows. The first query will access the EMP table 10 times and scan the DEPT table once. The second query will scan the EMP table once but will access the DEPT table 1000 times. Therefore the first query will perform much better. As a rule of thumb, tables should be arranged from smallest effective number rows to largest effective number of rows. The effective row size of a table in a query is obtained by applying the logical conditions that are resolved entirely on that table.
In another example, consider a query to retrieve the social security numbers and names of employees in a given location, such as New York. According to the sample schema, the query would have three table references in the FROM clause. The three tables could be ordered in six different ways. Although the result is the same regardless of which order you choose, the performance could be quite different.
Suppose the effective row size of the LOCATION table is small, for example select count(*) from LOCATION where LOC_NAME = 'New York' is a small set. Based on the above rules, the LOCATION table should be the first table in the FROM clause. There should be an index on LOCATION.LOC_NAME. Since LOCATION must be joined with DEPT, DEPT should be the second table and there should be an index on the LOC column of DEPT. Similarly, the third table should be EMP and there should be an index on EMP#. You could write this query as:

SELECT /++ordered++/ e.SS#, e.NAME 
FROM LOCATION l, DEPT d, EMP e 
WHERE l.LOC_NAME = 'New York' AND 
l.LOC# = d.LOC AND 
d.DEPT# = e.WORKS_IN;

D.3 Optimizing with Order By and Group By Clauses

Various performance improvements have been made so that SELECT statements run faster and consume less memory cache. Group by and Order by clauses attempt to avoid sorting if a suitable index is available.

D.3.1 IN subquery conversion

Converts IN subquery to a join when the select list in the subquery is uniquely indexed.
For example, the following IN subquery statement is converted to its corresponding join statement. This assumes that c1 is the primary key of table t2:

SELECT c2 FROM t1 WHERE 
c2 IN (SELECT c1 FROM t2);

becomes:

SELECT c2 FROM t1, t2 WHERE t1.c2 = t2.c1;

D.3.2 ORDER BY optimization with no GROUP BY

This eliminates the sorting step for an ORDER BY clause in a select statement if ALL of the following conditions are met:

All ORDER BY columns are in ascending order or in descending order.
Only columns appear in the ORDER BY clause. That is, no expressions are used in the ORDER BY clause.
ORDER BY columns are a prefix of some base table index.
The cost of accessing by the index is less than sorting the result set.

D.3.3 GROUP BY optimization with no ORDER BY

This eliminates the sorting step for the grouping operation if GROUP BY columns are the prefix of some base table index.

D.3.4 ORDER BY optimization with GROUP BY

When ORDER BY columns are the prefix of GROUP BY columns, and all columns are sorted in either ascending or in descending order, the sorting step for the query result is eliminated. If GROUP BY columns are the prefix of a base table index, the sorting step in the grouping operation is also eliminated.

D.3.5 Cache subquery results

If the optimizer determines that the number of rows returned by a subquery is small and the query is non-correlated, then the query result will be cached in memory for better performance. Currently the number of rows is set at 2000. For example:

select * from t1 where 
t1.c1 = (select sum(salary) 
from t2 where t2.deptno = 100);

[Oracle] While creating a sequence, what does cache and nocache options mean?

2011-12-12T08:18:00.000-08:00

One common question about sequences is:
Question: While creating a sequence, what does cache and nocache options mean? For example, you could create a sequence with a cache of 20 as follows:

CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;

Or you could create the same sequence with the nocache option:

CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;

Answer: With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.
The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values. When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values.

Note: To recover the lost sequence values, you can always execute an ALTER SEQUENCE command to reset the counter to the correct value.

Nocache means that none of the sequence values are stored in memory. This option may sacrifice some performance, however, you should not encounter a gap in the assigned sequence values.

[Oracle] Enable / Disable all triggers in Schema

2011-12-12T05:20:00.000-08:00

Disable Triggers

BEGIN
FOR i IN (SELECT trigger_name
FROM user_triggers) LOOP

EXECUTE IMMEDIATE 'ALTER TRIGGER ' || i.trigger_name || ' DISABLE';

END LOOP;
END;
/

Enable Triggers

BEGIN
FOR i IN (SELECT trigger_name
FROM user_triggers) LOOP

EXECUTE IMMEDIATE 'ALTER TRIGGER ' || i.trigger_name || ' ENABLE';

END LOOP;
END;
/

[Oracle] How to see last sql statements executed

2011-11-29T04:27:00.000-08:00

Recently we get into scenario where we need to see that which sql statements are being executed by activity upload.

So here is an easy way to do this:

select sql_text from v$sql where rownum<=100 and parsing_schema_name='NC_SDA_1_301';

Parsing Schema Name ; should be your schema name for which you need to see the sql statements executed on.

[Oracle] Enable / Disable all constraints in DB

2011-11-24T09:42:00.000-08:00

Disable Constraints:

BEGIN

FOR c IN

(SELECT c.owner, c.table_name, c.constraint_name

FROM user_constraints c, user_tables t

WHERE c.table_name = t.table_name

AND c.status = 'ENABLED'

ORDER BY c.constraint_type DESC)

LOOP

dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);

END LOOP;

END;

Enable Constraints:

BEGIN

FOR c IN

(SELECT c.owner, c.table_name, c.constraint_name

FROM user_constraints c, user_tables t

WHERE c.table_name = t.table_name

AND c.status = 'DISABLED'

ORDER BY c.constraint_type)

LOOP

dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);

END LOOP;

END;

[T-SQL] Change SQL Server instance name

2011-10-25T22:23:00.000-07:00

EXEC sp_dropserver 'Old Server Name'

EXEC sp_addserver 'New Server Name','local'

GO

Then restart the SQL service and confirm the new server name with @@SERVERNAME.

[Oracle] Update Data in Table trough Joins

2011-09-20T22:50:00.000-07:00

create table source_table

(source_id    number primary key

,s_col1       number

,s_col2       number

,s_col3       number

);

create table target_table

(target_id    number primary key

,t_col1       number

,t_col2       number

,t_col3       number

);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (101,111,1.01,-1);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (102,222,2.02,-2);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (103,333,3.03,-3);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (104,444,4.04,-4);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (105,555,5.05,-5);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (101,null,null,null);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (102,null,null,null);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (103,null,null,null);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (104,null,null,null);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (105,null,null,null);

select * from source_table;
select * from target_table;

update
(
   select t_col1,t_col2,t_col3,s_col1,s_col2,s_col3
   from   target_table t
         ,source_table s
   where t.target_id = s.source_id
)
set t_col1 = s_col1
   ,t_col2 = s_col2
   ,t_col3 = s_col3
;
select * from source_table;
select * from target_table;

[SQLServer] Why SQL Server is better than any other RDBMS Applications? Part 2

2011-08-29T23:25:00.000-07:00

I like SQL Server because of its SIMPLICITY. I learned SQL commands over Oracle (ah! it was too long that I used oracle and never touched it again after being FRIENDLY with SQL :) ).

I like following attributes of SQL Server hence I loved this one… and as I am trying to deep dive into technology I am being passionate lover… :)

1. First thing I liked is as said its SIMPLICITY.

2. Easy Availability– one don’t have to pay huge bucks to make SQL Available.. he can use Express edition available free.
3. Uncomplicated installation process – follows the same standard as of other MS Product like MS-Office etc… that no one need special knowledge of installation process to get ready to use SQL.
4. Can be install on Average system – like few other RDMS, SQL do not have special hardware requirement. so one can be benefited without having his/her system upgraded.

5. User friendly UI. again abide to MS standard for all other application so anyone feel friendly on first visit.

6. Easy navigation through common shortcuts and tool box. I don’t have to remember multiple keys to navigate.. what works in MS-Office / VS etc.. almost works in SQL as well.
7. BOL – help documentation is easily available and more friendly to browse to get the correct help.
8. Perfect match for all level of organizations… small firms to big enterprise to data store.
9. Perfect suite of application – good clubbing and packaging of Database engine, Agent Service, Notification Service, Reporting Service, Analysis Service, Integration Service.
10. Support for Multi- Instance and side by side installation of multiple versions.
11. Enhanced Security
12. Enhanced Optimizer
13. availability for various technologies to support high availability like Log Shipping, Replication, Clustering, Mirroring etc..
14. Easy to tune up, one can easily tune up the performance with help of wizards and additional tool like Profiler etc litle knowledge.
15. Advancement in the technology.. like SQL Azure, CLR integration, DAC, Intellisense (though I didnt like current intellisense but it helps often).
16. Broader community – I like the SQL community, in Fact I have sharpened my skill through this community.. where you can easily catch up with SQL MVP or MS SQL Team. I love dedication you guys shown up in communities. I believe that if someone is in crisis, this community will never let him get down with SQL.
17. non ANSI functions in SQL are similar to other programming languages hence feels comfortable and don’t have to dig more to find the right function that is equivalent in something like in .net.

SOURCE: COPIED FROM A CONTEST - 1 RUNNER UP POST

[SQLServer] Why SQL Server is better than any other RDBMS Applications? Part 1

2011-08-29T23:22:00.000-07:00

SQL Server is like most RDMS systems, a database engine but what make it better than other RDMS systems (especially SQL 2008 onwards) are new features and other bells and whistles it comes with.
Here are the few:

1. Easy integration with Microsoft Operating systems.

2. East integration with world’s most common database: Spreadsheet, Microsoft excel in particular and power pivot has significantly enhanced its value.

3. User friendly interface

4. Easy to create maintenance plans.

5. Integrated Security (windows authentication): This definitely help streamline server access based on Active directory policies and groups.

6. Mixed Authentication: Not only it gives the option to use windows authentication, it allows other applications to authenticate based on username and password

7. Disaster recovery: SQL Server is very easy to restore after a disaster (proper backup and recovery strategy needs to be implemented) as compared to other RDMS. All you need is File system restore followed by system state and then restore databases from backup. I have been involved in DR for a lot systems and doing DR on a SQL Server is much easier than any other RDMS system. Other benefits include Failover Clustering and Transaction Replication

8. Licensing: The licensing structure of SQL Server is much better as compared to other RDMS systems. Other RDMS systems have a very complex licensing structure which comes out to be much costly than SQL Server.

9. SQL Server Management Studio (SSMS): As compare to other RDMS systems tools, SSMS is the best tool for a developer or a DBA.

10. SQL Server Business Intelligence: Business Intelligence in SQL Server has come a long way and has evolved so much. It is one of the best if not the best in the market at the moment.

Since SQL Server Integration Services, SQL Server Analysis Services and SQL Server Reporting Services comes with the SQL Server license, customer doesn’t need to spend extra money on these additional tools, instead that money can be spend on something else e.g. upgrade SAN, user training.

11. Visualization

12. Administering and Monitoring: SQL Server 2008 R2 has really scored high in its new administration and monitoring tools. It has made the life of a DBA (even accidental DBA) a lot easier.

13. Backup Compression: Even though it is a new feature in SQL Server 2008, it has really helped companies to upgrade to the new version and speeding a lot (if not all) of the backup operations.

14. Data Encryption: SQL Server 2008 provides a lot of encryption options to encrypt data and code. Transparent Data Encryption has really safe guarded some of the crucial data movement operations our company.

SOURCE: COPIED FROM A CONTEST - WINNER POST

[SQLServer] SQL Server Value Calculator

2011-08-26T11:14:00.000-07:00

Yes, you have data management options.

Considering a better data management solution? Consider SQL Server 2008 Enterprise to efficiently manage your business-critical applications. And with Oracle raising their prices, you can still get a solution based on SQL Server for around a third of their cost.* Do the math on SQL Server value and you’ll see the efficiencies add up.

SQL Server 2008 Value Calculator

[T-SQL] Contained Database

2011-08-17T02:43:00.000-07:00

A Contained Database is a database which contains all the necessary settings and metadata, making database easily portable to another server. This database will contain all the necessary details and will not have to depend on any server where it is installed for anything. You can take this database and move it to another server without having any worries.

The real question is, “What about users who are connecting to this database?” Once the contained database is moved, the users are moved as well, and users who belong to the contained database will have no access outside the contained database.

In summary, “Database is now self-contained. Database which is ’contained’ will not depend on anything on the server where it is installed.”

Let us try out this feature on SQL Server Denali. We will do the following steps:

Enable Contained Database
Create Contained Database
Create User in Contained Database
Try if the user can access outside Contained Database

We can do various tests on this subject; however, in this blog post we will limit out exercise to the above four points.

Enable Contained Database

Run the following code on SQL Server Denali. This code will enable the settings for the contained database.

sp_configure 'show advanced options',1
GORECONFIGURE WITH OVERRIDE
GOsp_configure 'contained database authentication', 1
GORECONFIGURE WITH OVERRIDE
GO

Create Contained Database

CREATE DATABASE [ContainedDatabase]
CONTAINMENT = PARTIALON PRIMARY( NAME = N'ContainedDatabase', FILENAME = N'C:\ContainedDatabase.mdf')LOG ON( NAME = N'ContainedDatabase_log', FILENAME = N'C:\ContainedDatabase_log.ldf')GO

Create User in Contained Database

USE [ContainedDatabase]
GOCREATE USER ContainedUserWITH PASSWORD = 'pass@word';GO

Try if this user can access out side Contained Database

To test this, we will attempt to login in the database with default settings (where login database is the master).

When we attempt this, we will be not able to login in the server simply because the user does not exist at the server level.

Now, let us try to login in the system using the username which was created in the Contained Database.

You will notice that the login would be successful in the server. When expanded it, the user will have access to the contained database only, and not to any other database.

We will tackle more about this interesting subject in the future.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

[T-SQL] Wildcard

2011-08-02T07:20:00.000-07:00

Wildcard Basics Recap

Lets start off with something most of us know already. Most SQL folks understand the usefulness and power of the basic uses of wildcards. Using wildcards allows you to do pattern matches in a column. In this case our criteria does not want to use the = sign to find a pattern match. The operator that allows you to do approximate predicates is LIKE. The LIKE operator allows you to do special relative searches to filter your result set.

--Find all LastNames that start with the letter ASELECT *FROM EmployeeWHERE LastName LIKE 'A%'

To find everyone whose last name starts with the letter B, you need “B” to be the first letter. After the letter B you can have any number of characters. Using B% in single quotes after the LIKE operator gets all last names starting with the letter B.

--Find all LastNames that start with the letter BSELECT *FROM EmployeeWHERE LastName LIKE 'B%'

Wildcard ranges or set specifiers

If you want to find all LastName values starting with the letters A or B you can use two predicates in your WHERE clause. You need to separate them with the OR operator.

--Find all LastNames that start with the letter BSELECT *FROM EmployeeWHERE LastName LIKE 'A%'OR LastName LIKE 'B%'

Finding names beginning with A or B is easy. How about the registration desk example where want the names ranging from A-K? This works well until you want a range of A-K as in the example below:

--Find all LastNames ranging from A-KSELECT *FROM EmployeeWHERE LastName LIKE 'A%'OR LastName LIKE 'B%'OR LastName LIKE 'C%'OR LastName LIKE 'D%'OR LastName LIKE 'E%'OR LastName LIKE 'F%'OR LastName LIKE 'G%'OR LastName LIKE 'H%'OR LastName LIKE 'I%'OR LastName LIKE 'J%'OR LastName LIKE 'K%'

The previous query does find LastName values starting from A-K. However, if you need a range of letters, the LIKE operator has many better options. We only really care about the first letter of the last name and there a several first letters that fit with what were looking for. The first letter of the last name can be A,B,C,D,E,F,G,H,I,J or K. Simply list all the choices you want for the first letter inside a set of square brackets.

--LastNames ranging from A to K using a set of 11 lettersSELECT *FROM EmployeeWHERE LastName LIKE '[ABCDEFGHIJK]%'

Square brackets with wildcards enclose ranges or sets for 1 position. In this case the first position is a set of 11 different possible letters. This is not a series of letter but a multiple choice of letters. For example this works regardless of the order you put your letters in. This code sample below does the exact same thing.

--LastNames ranging from A to K using a set of 11 lettersSELECT *FROM EmployeeWHERE LastName LIKE '[KBCDEFGHIJA]%'

Again the set is how many letters you put in the square brackets. The code below is a logical mistake where you won’t get A to K but you just get A or K for the first letter.

--Find all LastNames starting with A or K (MistakeSELECT *FROM EmployeeWHERE LastName LIKE '[AK]%'

Since we’re looking for the first letter to be within a range from A to K, we specify that range in square brackets. This is even easier than using a set. The wildcard after the brackets allows any number of characters after the range.

--LastNames ranging from A to K using a rangeSELECT *FROM EmployeeWHERE LastName LIKE '[A-K]%'

Note: this range will not work if your LIKE was changed to an equal (=) sign. The following code will not return any records to your result set:

--Bad query (it won’t error but returns no records)SELECT *FROM EmployeeWHERE LastName = '[A-K]%'

[Informatica] Tutorial - Working with text files

2011-07-11T23:47:00.000-07:00

Here is the link of video in which i have created a mapping , workflow for how to take data from one csv and transform and create another csv.

http://www.youtube.com/watch?v=kbwbafnlvoc

informatica_tutorial_textfiles

[SQL Server] 10 reasons why SQL Server 2008 is going to rock

2011-06-27T05:12:00.000-07:00

10. Plug-in model for SSMS. SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind. Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom.

9. Inline variable assignment. I often wondered why, as a language, SQL languishes behind the times. I mean, it has barely any modern syntactic sugar. Well, in this version, they are at least scratching the the tip of the iceberg.

Instead of:

DECLARE @myVar int 
SET @myVar = 5

you can do it in one line:

DECLARE @myVar int = 5

Sweet.

8. C like math syntax. SET @i += 5. Enough said. They finally let a C# developer on the SQL team.

7. Auditing. It's a 10 dollar word for storing changes to your data for later review, debugging or in response to regulatory laws. It's a thankless and a mundane task and no one is ever excited by the prospect of writing triggers to handle it. SQL Server 2008 introduces automatic auditing, so we can now check one thing off our to do list.

6. Compression. You may think that this feature is a waste of time, but it's not what it sounds like. The release will offer row-level and page-level compression. The compression mostly takes place on the metadata. For instance, page compression will store common data for affected rows in a single place.

The metadata storage for variable length fields is going to be completely crazy: they are pushing things into bits (instead of bytes). For instance, length of the varchar will be stored in 3 bits.

Anyway, I don't really care about space savings - storage is cheap. What I do care about is that the feature promised (key word here "promises") to reduce I/O and RAM utilization, while increasing CPU utilization. Every single performance problem I ever dealt with had to do with I/O overloading. Will see how this plays out. I am skeptical until I see some real world production benchmarks.

5. Filtered Indexes. This is another feature that sounds great - will have to see how it plays out. Anyway, it allows you to create an index while specifying what rows are not to be in the index. For example, index all rows where Status != null. Theoretically, it'll get rid of all the dead weight in the index, allowing for faster queries.

4. Resource governor. All I can say is FINALLY. Sybase has had it since version 12 (that's last millennium, people). Basically it allows the DBA to specify how much resources (e.g. CPU/RAM) each user is entitled to. At the very least, it'll prevent people, with sparse SQL knowledge from shooting off a query with a Cartesian product and bringing down the box.

Actually Sybase is still ahead of MS on this feature. Its ASE server allows you to prioritize one user over another - a feature that I found immensely useful.

3. Plan freezing. This is a solution to my personal pet peeve. Sometimes SQL Server decides to change its plan on you (in response to data changes, etc...). If you've achieved your optimal query plan, now you can stick with it. Yeah, I know, hints are evil, but there are situations when you want to take a hammer to SQL Server - well, this is the chill pill.

2. Processing of delimited strings. This is awesome and I could have used this feature...well, always. Currently, we pass in delimited strings in the following manner:

exec sp_MySproc 'murphy,35;galen,31;samuels,27;colton,42'

Then the stored proc needs to parse the string into a usable form - a mindless task.

In 2008, Microsoft introduced Table Value Parameters (TVP).

CREATE TYPE PeepsType AS TABLE (Name varchar(20), Age int) 
DECLARE @myPeeps PeepsType 
INSERT @myPeeps SELECT 'murphy', 35 
INSERT @myPeeps SELECT 'galen', 31 
INSERT @myPeeps SELECT 'samuels', 27 
INSERT @myPeeps SELECT 'colton', 42

exec sp_MySproc2 @myPeeps

And the sproc would look like this:

CREATE PROCEDURE sp_MySproc2(@myPeeps PeepsType READONLY) ...

The advantage here is that you can treat the Table Type as a regular table, use it in joins, etc. Say goodbye to all those string parsing routines.

1. Intellisense in the SQL Server Management Studio (SSMS). This has been previously possible in SQL Server 2000 and 2005 with use of 3rd party add-ins like SQL Prompt ($195). But these tools are a horrible hack at best (e.g. they hook into the editor window and try to interpret what the application is doing).

Built-in intellisense is huge - it means new people can easily learn the database schema as they go.

[Personal] Visit to Iran

2011-05-27T05:18:00.000-07:00

This month i was on vacations and during vacations i visit Iran for ziarat and recreational purpose. Had an excellent time with my whole family(MashAllah).

I visited following cities:
1. Tehran
2. Meshad
3. Qum
4. Miami

This was my 7th visit to Iran and like always i enjoyed this trip as well, the speciality of this trip was that this time my parents were also with me.

Iran is a very peaceful and beautiful country. I visited many holy shrines there some of which include Imam Reza(a.s) , Bibi Masooma Qum (s.a) , Shah Abol Azim , Hazrat Zaid shaheed and many more.

[Informatica] Tutorial Part 5

2011-04-20T03:18:00.001-07:00

Here is the last session of informatica tutorial:

http://www.youtube.com/watch?v=8zdPvAkMv90

[T-SQL] Find current location of data and log file of all database

2011-04-14T23:58:00.000-07:00

In general we see that some times we go trough very small queries but it has really a big impact during ongoing scenario.

Here is one of the query by which you can see the location of data and log file of all the databases.

SELECT name, physical_name AS current_file_location
FROM sys.master_files

[T-SQL] Add new column [Not Null] column in a table already having data - Part 2

2011-04-12T00:24:00.000-07:00

Previously i wrote an article with a method that how can we include not null column in a table already having data.

But that method has number of scripts to execute. SQL Server handle it in a very good way.

If you execute the script

ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO

Then SQL Server will itself insert the default value 0 for the rows which are already present in database.

[SQLServer] TempDB Recommendations

2011-04-12T00:15:00.000-07:00

I was going trough a nice article , so sharing apart from it regarding the recommendations for TempDB.

Source: http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

Pre-size TempDB appropriately. Leave autogrow on with instant file initialization enabled, but try to configure the database so that it never hits an autogrow event. Make sure the autogrow growth increment is appropriate.

· Follow general IO recommendations for fast IO.

· If your TempDB experiences metadata contention (waitresource = 2:1:1 or 2:1:3), you should split out your data onto multiple files. Generally you will want somewhere between 1/4 and 1 file per physical core. If you don't want to wait to see if any metadata contention occurs you may want to start out with around 1/4 to 1/2 the number of data files as CPUs up to about 8 files. If you think you might need more than 8 files we should do some testing first to see what the impact is. For example, if you have 8 physical CPUs you may want to start with 2-4 data files and monitor for metadata contention.

· All TempDB data files should be of equal size.

· As with any database, your TempDB performance may improve if you spread it out over multiple drives. This only helps if each drive or mount point is truly a separate IO path. Whether each TempDB will have a measurable improvement from using multiple drives depends on the specific system.

· In general you only need one log file. If you need to have multiple log files because you don't have enough disk space on one drive that is fine, but there is no direct benefit from having the log on multiple files or drives.

· On SQL Server 2000 and more rarely on SQL Server 2005 or later you may want to enable trace flag -T1118.

· Avoid shrinking TempDB (or any database) files unless you are very certain you will never need the space again.

[Oracle] Account Expiry

2011-03-28T03:48:00.000-07:00

Question: How can we auto lock a schema or user in oracle.
Ans: You can not directly lock a schema but you can lock user trough profile. First create a profile with the method defined below and then while creating user give that profile name instead of default.

CREATE PROFILE prof LIMIT
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME 30
PASSWORD_LIFE_TIME 90;

ALTER USER mabbas PROFILE prof;

User mabbas can use his password for 90 days before the user (or DBA) is forced to change the password.

[T-SQL] Change SQL Server 2005 SA Password

2011-03-25T05:37:00.001-07:00

Changing the password in SQL Server was much easier. You just type the following thing and password for the account is changed:

sp_password 'Old','New', Account

But this dont work for Sql Server 2005. Initially I thought whats worng with the SQL Server, but later I realized that it may be different for the two versions. At last I found it and here it is:

ALTER LOGIN Account_Name WITH
PASSWORD = 'New_Password'
OLD_PASSWORD = 'Old_Password';
GO

[Informatica] Tutorial Part 4

2011-03-17T06:25:00.000-07:00

Here is the third part of Informatica Tutorial Part 4.

http://www.youtube.com/watch?v=AuCVmPpRvy4

[T-SQL] Concurrency Issues and Isolation Level

2011-03-08T10:14:00.000-08:00

There are four types of concurrency problems visible in the normal programming.

1) Lost Update – This problem occurs when there are two transactions involved and both are unaware of each other. The transaction which occurs later overwrites the transactions created by the earlier update.

2) Dirty Reads – This problem occurs when a transactions selects data that isn’t committed by another transaction leading to read the data which may not exists when transactions are over.

Example: Transaction 1 changes the row. Transaction 2 changes the row. Transaction 1 rolls back the changes. Transaction 2 has selected the row which does not exist.

3) Nonrepeatable Reads – This problem occurs when two SELECT statements of the same data results in different values because another transactions has updated the data between the two SELECT statements.

Example: Transaction 1 selects a row, which is later on updated by Transaction 2. When Transaction A later on selects the row it gets different value.

4) Phantom Reads – This problem occurs when UPDATE/DELETE is happening on one set of data and INSERT/UPDATE is happening on the same set of data leading inconsistent data in earlier transaction when both the transactions are over.

Example: Transaction 1 is deleting 10 rows which are marked as deleting rows, during the same time Transaction 2 inserts row marked as deleted. When Transaction 1 is done deleting rows, there will be still rows marked to be deleted.

When two or more transactions are updating the data, concurrency is the biggest issue. I commonly see people toying around with isolation level or locking hints (e.g. NOLOCK) etc, which can very well compromise your data integrity leading to much larger issue in future.

Here is the quick mapping of the isolation level with concurrency problems:

Isolation Dirty Reads Lost Update Nonrepeatable Reads Phantom Reads
Read Uncommitted Yes Yes Yes Yes
Read Committed No Yes Yes Yes
Repeatable Read No No No Yes
Snapshot No No No No
Serializable No No No No

Source: sqlauthority.com

[Informatica] Tutorial Part 3

2011-03-05T10:17:00.000-08:00

Here is the third part of Informatica Tutorial Part 3.

http://www.youtube.com/watch?v=upwtGPn4pDI

[Informatica] Tutorial Part 2

2011-03-02T04:41:00.001-08:00

Informatica Tutorial Part 2 for two beginners:

http://www.youtube.com/watch?v=oM2d-IHfRUw

[Informatica] Tutorial Part 1

2011-02-28T21:20:00.001-08:00

Here is the link if Informatica Tutorial Part 1 for beginners:

http://www.youtube.com/watch?v=ufH_n5exxQw

[Informatica] Configure Power Center Integration Service

2011-02-18T04:54:00.000-08:00

Finally we need to configure Integration Service through which we can execute our workflows.
1. In the Administrator tool, click the Domain tab.
2. On the Navigator Actions menu, click New > PowerCenter Integration Service.
The New Integration Service dialog box appears.
3. Enter the Service Name, License, And Primary Node.
4. Now provide the repository information which you just created, its included repository name, username and password.
5. Click Finish.

[Informatica] Configure Power Center Repository Service

2011-02-18T04:52:00.000-08:00

After the successful installation of Informatica we need to configure repository service. These steps need to be done from windows operating system. Any browser should be installed for performing these steps.
1. Open up the browser and type in below format http://DomainName:PortNo/administrator/index.html
Sample: http://informatica-test:6007/administrator/index.html
2. Enter the username and password provided during the installation.
3. In the Administrator tool, click the Domain tab.
4. In the Navigator, select the folder where you want to create the PowerCenter Repository Service. Note: If you do not select a folder, you can move the PowerCenter Repository Service into a folder after you create it.
5. In the Domain Actions menu, click New > PowerCenter Repository Service. The Create New Repository Service dialog box appears.
6. Provide the Repository Service Name and all other database parameters.
7. Click the option to create repository contents.
8. Select the license, node and all other information that need to be provided.
9. Select “UTF-8 encoding of Unicode” in Code page combo box.
10. Click Ok.

[Informatica] Server Installation 9.0.1

2011-02-18T04:49:00.000-08:00

Here are steps to install informatica server 9.0.1:

1. Verify that your environment meets the minimum system requirements, and complete the pre-installation tasks as completed in steps 3 and 4 of this guide.
2. Log in to the machine with a system user account.
3. Close all other applications.
4. On a shell command line, run the install.sh file located in the root directory. The installer displays the message to verify that the locale environment variables are set.
5. If the environment variables are not set, enter N to exit the installer and set them as required. If the environment variables are set, enter Y to continue.
6. Enter C for console mode installation.
7. Press Enter.
8. Enter 1 to install Informatica 9.0.1. Review the system requirements and pre-installation tasks. Verify that all installation requirements are met before you continue the installation.
9. Press Enter.
10. Enter the path and file name of the Informatica license key.
11. Enter the path for the installation directory or press Enter to use the default directory. The directory names in the path must not contain spaces or the following especial characters: @|* $ # ! % ( ) { } [ ] , ; '
12. Review the installation information, and press Enter to continue. The installer copies the Informatica files to the installation directory.
13. Enter 1 to create a domain. If you create a domain, the node on the current machine becomes a gateway node on the domain. The gateway node contains a Service Manager that manages all domain operations.
14. Select whether to set up a secure connection between the Service Manager and the Administrator tool. Press 1 to Enable HTTPS for Informatica Administrator.
15. Select the database to use for the domain configuration repository. Press 1 to select Oracle.
16. Enter the Database User ID and Password.
17. Select how you want to specify the database connection URL. Press 1 for JDBC URL.
18. Now enter the properties at the prompt.
a. Database Address: Enter HostName:PortNo
b. Database service name: Enter SID
c. Configure JDBC parameters: Select yes, enter the parameters or press enter to accept the default.
19. If the database contains a domain configuration repository for a previous domain, select 2 to overwrite the data or set up another database. Press Continue to overwrite.
20. Enter the following information at the prompt:
a. Domain name: Enter Domain Name
b. Domain host name: Enter Host Name
c. Node name: Press Enter for default Node Name
d. Domain port number: Press Enter for default port name
e. Domain user name: Enter username, this would be used for initial setup of Informatica
f. Domain user password: Enter Password
g. Confirm password: Enter Password again
21. Now it would ask whether to display the default ports for the domain and node components assigned by the installer. Press 1 for No.
22. Installation is now complete.
23. Go to the Informatica installation directory and write the below command to bring up the Informatica service.
infaservice.sh startup

[Informatica] How to import workflow in Informatica PC

2011-02-18T04:43:00.000-08:00

Here are the steps to import workflow in Informatica Power Centre:

1. Verify that xml file is present in source folder.
2. Open Informatica PC Repository Manager.
3. Connect to the repository by providing relevant username and password.
4. Create a new folder in repository.
5. Go to that folder and then under Repository menu at the top click import objects.
6. Select the file that is present in informaticarepository folder and click next.
7. In this screen click button add all as you would need all the objects to execute workflow which are currently present in xml file and click next.
8. Select the folder which was created recently and click next.
9. Now click next one more time and click import. It would show you all the objects import status.
10. Now you can verify in the folder that workflow exists.

[T-SQL] Database backup procedure - advanced

2011-02-15T23:44:00.001-08:00

I have got this script from one of the blogs:

if exists (select * from sysobjects where id = object_id(N'[dbo].[spBackupAllDatabases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spBackupAllDatabases]
go

Create Procedure spBackupAllDatabases
@Path varchar(128) ,
@Type varchar(4) -- Full / Log
as
/*
Backup file format
_Full_yyyymmdd_hhmmss.bak
_Log_yyyymmdd_hhmmss.bak

exec spBackupAllDatabases 'c:\SQLBackups\', 'Full'
*/
/*
drop table DatabaseBackup

Create table DatabaseBackup
(
Name varchar(128) primary key nonclustered ,
BackupFlagFull varchar(1) not null check (BackupFlagFull in ('Y','N')) ,
BackupFlagLog varchar(1) not null check (BackupFlagLog in ('Y','N')) ,
RetentionPeriodFull datetime not null ,
RetentionPeriodLog datetime not null
)
*/
set nocount on
declare @sql varchar(1000)

-- Get all database names
create table #DBName
(
ID int identity (1,1) ,
Name varchar(128) not null ,
RetentionPeriod datetime null
)

insert #DBName
(Name)
select name
from master..sysdatabases

-- Include any new databases in the backup
insert DatabaseBackup
(
Name ,
BackupFlagFull ,
BackupFlagLog ,
RetentionPeriodFull ,
RetentionPeriodLog
)
select #DBName.Name ,
'Y' ,
'N' ,
'7 jan 1900' , -- default one week
'1 jan 1900'
from #DBName
left outer join DatabaseBackup
on DatabaseBackup.Name = #DBName.Name
where DatabaseBackup.Name is null
and lower(#DBName.Name) <> 'tempdb'

-- Remove any non-existant databases
delete DatabaseBackup
where not exists
(
select *
from #DBName
where #DBName.Name = DatabaseBackup.Name
)

delete #DBName

create table #ExistingBackups
(
Name varchar(128) ,
ID int identity (1,1)
)

-- loop through databases
declare @Name varchar(128) ,
@RetentionPeriod datetime ,
@LastBackupToKeep varchar(8) ,
@ID int ,
@MaxID int

insert #DBName
(Name, RetentionPeriod)
select Name, case when @Type = 'Full' then RetentionPeriodFull else RetentionPeriodLog end
from DatabaseBackup
where (@Type = 'Full' and BackupFlagFull = 'Y')
or (@Type = 'Log' and BackupFlagLog = 'Y')

select @MaxID = max(ID) ,
@ID = 0
from #DBName

while @ID < @MaxID begin -- get next database to backup select @ID = min(ID) from #DBName where ID > @ID

select @Name = Name ,
@RetentionPeriod = RetentionPeriod
from #DBName
where ID = @ID

-- Delete old backups
delete #ExistingBackups
select @sql = 'dir /B ' + @Path
select @sql = @sql + @Name + '_' + @Type + '*.*'

insert #ExistingBackups exec master..xp_cmdshell @sql

if exists (select * from #ExistingBackups where Name like '%File Not Found%')
delete #ExistingBackups

select @LastBackupToKeep = convert(varchar(8),getdate() - @RetentionPeriod,112)
delete #ExistingBackups where Name > @Name + '_' + @Type + '_' + @LastBackupToKeep

declare @eID int ,
@eMaxID int ,
@eName varchar(128)

-- loop round all the out of date backups
select @eID = 0 ,
@eMaxID = coalesce(max(ID), 0)
from #ExistingBackups

while @eID < @eMaxID begin select @eID = min(ID) from #ExistingBackups where ID > @eID
select @eName = Name from #ExistingBackups where ID = @eID

select @sql = 'del ' + @Path + @eName
exec master..xp_cmdshell @sql
end
delete #ExistingBackups

-- now do the backup
select @sql = @Path + @Name + '_' + @Type + '_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','') + '.bak'
if @Type = 'Full'
backup database @Name
to disk = @sql
else
backup log @Name
to disk = @sql
end

go

[T-SQL] Database backup script

2011-02-15T23:37:00.001-08:00

Today i got a request to provide a database backup script in such a way that every time it generated new name for database. So here is the script:

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),114)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
Print @filename
FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

[Informatica] Software Architecture illustrated

2011-01-28T03:39:00.000-08:00

Informatica >> Beginners >> Informatica System Architecture

Informatica Software Architecture illustrated
Informatica ETL product, known as Informatica Power Center consists of 3 main components.

1. Informatica PowerCenter Client Tools:
These are the development tools installed at developer end. These tools enable a developer to

•Define transformation process, known as mapping. (Designer)
•Define run-time properties for a mapping, known as sessions (Workflow Manager)
•Monitor execution of sessions (Workflow Monitor)
•Manage repository, useful for administrators (Repository Manager)
•Report Metadata (Metadata Reporter)

2. Informatica PowerCenter Repository:
Repository is the heart of Informatica tools. Repository is a kind of data inventory where all the data related to mappings, sources, targets etc is kept. This is the place where all the metadata for your application is stored. All the client tools and Informatica Server fetch data from Repository. Informatica client and server without repository is same as a PC without memory/harddisk, which has got the ability to process data but has no data to process. This can be treated as backend of Informatica.

3. Informatica PowerCenter Server:
Server is the place, where all the executions take place. Server makes physical connections to sources/targets, fetches data, applies the transformations mentioned in the mapping and loads the data in the target system.

[SQLServer] Scaling Up Your Data Warehouse with SQL Server 2008 R2

2011-01-28T02:34:00.000-08:00

Abstract from the Microsoft Official site:

SQL Server 2008 introduced many new functional and performance improvements for data warehousing, and SQL Server 2008 R2 includes all these and more. This paper discusses how to use SQL Server 2008 R2 to get great performance as your data warehouse scales up. We present lessons learned during extensive internal data warehouse testing on a 64-core HP Integrity Superdome during the development of the SQL Server 2008 release, and via production experience with large-scale SQL Server customers. Our testing indicates that many customers can expect their performance to nearly double on the same hardware they are currently using, merely by upgrading to SQL Server 2008 R2 from SQL Server 2005 or earlier, and compressing their fact tables. We cover techniques to improve manageability and performance at high-scale, encompassing data loading (extract, transform, load), query processing, partitioning, index maintenance, indexed view (aggregate) management, and backup and restore.

[T-SQL] DB_ID() and DB_NAME() - PURPOSE

2011-01-18T03:03:00.000-08:00

-- Get Current DatabaseID
SELECT DB_ID() DatabaseID;
-- Get Current DatabaseName
SELECT DB_NAME() DatabaseName;
-- Get DatabaseName from DatabaseID
SELECT DB_NAME(4) DatabaseID;
-- Get DatabaseID from DatabaseName
SELECT DB_ID('tempdb') DatabaseName;
-- Get all DatabaseName and DBID
SELECT name,database_id
FROM sys.databases;

[T-SQL] Different methods to find leap year

2011-01-18T02:26:00.000-08:00

Method 1 : General method

declare @year int
set @year=2000

select
case
when @year%400=0 then 1
when @year%100=0 then 0
when @year%4=0 then 1
else 0
end as is_leap_year
Method 2 : Find if the day difference between Jan 01 of this year to Jan 01 of next year is 366

declare @year int
set @year=2000

select
case
when datediff(day,DATEADD(year,@year-1900,0),DATEADD(year,@year-1900+1,0))=366 then 1
else 0
end as is_leap_year
Method 3 : Find if the day difference between Feb 01 to Mar 01 of this year is 29

select
case
when datediff(day,dateadd(month,1,DATEADD(year,@year-1900,0)),dateadd(month,2,DATEADD(year,@year-1900,0)))=29 then 1
else 0
end as is_leap_year
Method 4 : Find if the 60th day of this year falls in February last day (Feb 29)

declare @year int
set @year=2000

select
case
when day(dateadd(day,59,DATEADD(year,@year-1900,0)))=29 then 1
else 0
end as is_leap_year

[T-SQL] Delete vs Truncate

2011-01-04T01:23:00.000-08:00

Here is good comparision between delete and truncate

DELETE:

DELETE supports a WHERE clause
DELETE removes rows from a table, row-by-row
Because DELETE moves row-by-row, it acquires a row-level lock
Depending upon the recovery model of the database, DELETE is a fully-logged operation.
Because DELETE moves row-by-row, it can fire off triggers

TRUNCATE:

TRUNCATE does not support a WHERE clause
TRUNCATE works by directly removing the individual data pages of a table
TRUNCATE directly occupies a table-level lock.
(Because a lock is acquired, and because TRUNCATE can also participate in a transaction, it has to be a logged operation)
TRUNCATE is, therefore, a minimally-logged operation; again, this depends upon the recovery model of the database
Triggers are not fired when TRUNCATE is used (because individual row deletions are not logged)

[T-SQL] Data Cache in Memory

2011-01-04T01:15:00.001-08:00

Here is the query by which you can see data cache in memory:

USE AdventureWorks
GO
SELECT COUNT(*) AS cached_pages_count,
name AS BaseTableName, IndexName,
IndexTypeDesc
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT s_obj.name, s_obj.index_id,
s_obj.allocation_unit_id, s_obj.OBJECT_ID,
i.name IndexName, i.type_desc IndexTypeDesc
FROM
(
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id ,allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.TYPE = 1 OR au.TYPE = 3)
UNION ALL
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id, allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.TYPE = 2
) AS s_obj
LEFT JOIN sys.indexes i ON i.index_id = s_obj.index_id
AND i.OBJECT_ID = s_obj.OBJECT_ID ) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id, IndexName, IndexTypeDesc
ORDER BY cached_pages_count DESC;
GO

[T-SQL] Plan Cache in Memory

2011-01-04T01:14:00.001-08:00

Here is the query by which you can see plan cache in memory:

USE AdventureWorks
GO
SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
ORDER BY cp.size_in_bytes DESC
GO

[T-SQL] DBCC LOGINFO

2011-01-04T01:11:00.000-08:00

Do you know that there are multiple small log files in the large log file (LDF). So now your next question would be how can we know that.

Veri simple method

DBCC LOGINFO

You would find the file size and other relevant information in the resultant.

[ORACLE] How to import dump file to different table space

2010-12-24T09:12:00.000-08:00

1.With your .DMP file, create a SQL file containing the structure (Tables):
imp evolvrepo/password@ORCL file=evolvrepo.dmp indexfile=index.sql full=y

2.Open the indexfile (index.sql) in a text editor that can do find and replace over an entire file, and issue the following find and replace statements IN ORDER (ignore the single quotes.. '):

a.Find: 'REM' Replace:

b.Find: '""' Replace: '"#tablespace#"'

c.Find: '...' Replace: 'REM ...'

d.Find: 'CONNECT' Replace: 'REM CONNECT'

3.Save the indexfile, then run it at command prompt
echo exit|sqlplus evolvrepo/password@ORCL @index.sql

4.Finally run the same .DMP file you created the indexfile with against the same account to import the data, stored procedures, views etc:
imp evolvrepo/password@ORCL full=y file=evolvrepo.dmp buffer=326000 log=evolvrepo.log

Note: Replace #tablespace# with table space name and #TEMP1# with temporary tablespace name.

[T-SQL] Paging in SQL Server 2011

2010-12-14T22:27:00.001-08:00

So here is now the new feature of sql server in its latest edition , so here is how you can use paging , i mean server side paging for fetching rows or result set in pages.

USE AdventureWorks2008R2
GO
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 5
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET @PageNumber*@RowsPerPage ROWS
FETCH NEXT 10 ROWS ONLY
GO

[Informatica] Powermart Command Prompt pmcmd

2010-12-08T23:37:00.000-08:00

Here is the way how you can execute the workflow from command prompt:

./pmcmd
pmcmd> connect -sv TestingRepoIntegrationService1 -d Primatics_corvus.primaticsfinancial.com -u uname -p password
pmcmd> setfolder "Demo Mapping"
pmcmd> startworkflow Main_Model_Bank

---- Description
sv: Integration Service Name
d: Domain Name
u: username
p: password
setfolder: tell the folder name where workflow has been placed
startworkflow : tell the workflow name

[T-SQL] Find Space Used For Any Particular Table

2010-12-08T02:30:00.000-08:00

Use Temp;
Go
EXEC sp_spaceused 'Temp1';
GO

You will get the following details:

1. Name
2. Rows
3. Reserved
4. Data
5. Index Size
6. Unused

[Informatica] Tutorials

2010-12-06T22:32:00.001-08:00

Here are some initial tutorials for how to work with informatica.

How to create mapping , session , workflow using Informatica Powercenter:

http://www.youtube.com/watch?v=zkJoet5jBtE

Workflow Properties:

http://www.youtube.com/watch?v=2FiFLaNkwnY

Let me know if you have any queries regarding any steps that are defined in between.

[Informatica] New Label

2010-12-06T02:23:00.000-08:00

I have started working on Informatica , so now i would also start posting some good material realated to Informatica with the label [Informatica]

I am working on the following tools:

Informatica 9.0.1
=> Power Center Designer
=> Power Center Workflow Manager
=> Power Center Workflow Monitor

For any queries , do let me know.

[T-SQL] What to do , if mdf file get corrupted

2010-11-28T01:20:00.000-08:00

Today one of my old friend asked help that he has got mdf file corrupted so what should he do.

So normally there are different excersies which one can do if he knows about the structure of sql server but i am suggesting two tool which you can download and get the task done.:

http://www.mdfrepair.com/
http://www.nucleustechnologies.com/sql-recovery.html

Download it and there are simple steps what you can do to get your db repaired.

Different versions of SQL Server 2008

2010-11-28T00:48:00.000-08:00

Choosing the right SQL Server 2008 Edition is something that requires a bit of a research. The first step is of course to become familiar with the available options. We have seen this question pop up a lot of times at client sites as well as in newsgroups and the e-mails that we get from the readers. Microsoft already has very good, concise and to the point information on the different editions, their capabilities as well as a comparison matrix between Enterprise & Standard Edition as well as a comparison matrix between Compact & Express Edition. There are a lot of choices available for you and depending upon your needs, you can choose from (all the details on these can be obtained from the link above):

•Enterprise Edition
•Standard Edition
•Workgroup Edition
•Web Edition
•Compact Edition
•Express Edition
◦SQL Server 2008 Express Edition.
◦SQL Server 2008 Express with Tools.
◦SQL Server 2008 Express with Advanced Tools.

Full comparison sheet link is given below:

http://download.microsoft.com/download/2/d/f/2df66c0c-fff2-4f2e-b739-bf4581cee533/SQLServer%202008CompareEnterpriseStandard.pdf

Comparison between prices of SQL Server and Oracle

2010-11-26T07:30:00.001-08:00

SQL Server is a feature-rich and economical choice compared to Oracle. The base product of Oracle is expensive and to add all the features that are offered by the SQL Server, it requires many more different add-ons. These extra add-ons further increase the price to make SQL Server much more affordable than Oracle, which is ridiculously expensive.

I suggest that instead of me taking the side of SQL Server, you should compare the prices of the products yourself. The prices that are listed here are as per the process.

SQL Server Standard: $7,171
SQL Server Enterprise: $27,495

Oracle Standard One: $5,800
Oracle Standard: $17,500
Oracle Enterprise: $47,500

Additionally, in case of Oracle, you will have to purchase many additional features which are already available at NO COST in SQL Server Enterprise Edition. For example, in the SQL Server Enterprise edition, partitioning is a part of the package, whereas in the case of Oracle, you will have to pay additional 11,500 per processor along with the enterprise edition purchase. If you have a four-socket CPU, you end up paying almost the same price as Oracle Enterprise. Again this is just one feature we talked about. There are many additional add-ons available for Oracle, which are free in the SQL Server Enterprise or Standard edition (e.g. BI Tools).

*Prices listed are taken from the official pdf that were available on both companies website on july-2010.

[T-SQL] How to create new error log file

2010-11-21T20:39:00.000-08:00

There are total two methods to do this:

1. Restart the server
2. EXEC sp_cycle_errorlog

First option is not a suitable one as we restart the server every time.

Hope this post is helpful to many of the dba's.

[T-SQL] Drop Database - Common Error

2010-11-13T22:16:00.000-08:00

Normally when you try to drop the database , you get common error that its current in use. So here is proper script for how you should drop the database. But please make sure what you know what you are doing , as dropping database on Production can be a big issue.

USE MASTER
GO
ALTER DATABASE Mabbas_DB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE Mabbas_DB
GO

[T-SQL] Last Backup time for all DBs

2010-11-10T02:32:00.001-08:00

One more script to share

SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

[T-SQL] DB Backup History

2010-11-10T02:28:00.000-08:00

Today i was visiting one blog(Pinal Dave) and found a very interesting query for getting database backup history.

SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

[T-SQL] Delete duplicate without ID

2010-11-08T02:50:00.000-08:00

Here is a query by which you can delete duplicate rows without using identity column

/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

[T-SQL] Union vs. Union All

2010-11-05T00:30:00.000-07:00

Now a days , i am taking interview for the post of DWH Engineer and a common question that i am asking is the above one , but i dont know why people dont know this.

So here is the basic definition:

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

If we talk about the performance , then it should be known that Union All is much faster then Union as Union involves distinct clause which itself is a heavy operation.

[T-SQL] Find Busiest Database(s)

2010-11-04T04:50:00.000-07:00

DMV sys.dm_exec_query_stats contained columns total_logical_reads, total_logical_writes, sql_handle. Column sql_handle can help to to determine the original query by CROSS JOINing DMF sys.dm_exec_sql_text. From DMF sys.dm_exec_sql_text Database ID and from Database ID can be figured out very quickly.

SELECT SUM(deqs.total_logical_reads) TotalPageReads,
SUM(deqs.total_logical_writes) TotalPageWrites,
CASE
WHEN DB_NAME(dest.dbid) IS NULL THEN 'AdhocSQL'
ELSE DB_NAME(dest.dbid) END Databasename
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
GROUP BY DB_NAME(dest.dbid)

[T-SQL] Taking backup at two places same time

2010-11-01T02:00:00.000-07:00

If you want to take backup of any DB at two places same time then following tsql need to be executed.

BACKUP DATABASE [AdventureWorks] TO
DISK = N'D:\AdventureWorks.bak'
MIRROR TO DISK = N'E:\AdventureWorks.bak'
WITH FORMAT, INIT,
NAME = N'AdventureWorks-Full Database Backup'
GO

Dont use the below tsql as it would break your db backup into two file and it would not serve the purpose. I mean you would be needing both of these files when you are restoring the database.

BACKUP DATABASE [AdventureWorks] TO
DISK = N'D:\AdventureWorks.bak'
, DISK = N'E:\AdventureWorks.bak'
WITH FORMAT, INIT,
NAME = N'AdventureWorks-Full Database Backup'
GO

[T-SQL] Difference between @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT

2010-10-27T03:25:00.000-07:00

SELECT @@IDENTITYIt returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

[T-SQL] Difference between Index Scan and Index Seek

2010-10-25T23:31:00.000-07:00

Index Scan:Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

[T-SQL] How to Rename a Column Name or Table Name

2010-10-21T22:25:00.000-07:00

This morning i needed to rename column name and table name itself , so tought to share it on blog.

The script for renaming any column :
sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

The script for renaming any object (table, sp etc) :
sp_RENAME 'OldTableName' , 'NewTableName'

This example renames the customers table to custs.

EXEC sp_rename 'customers', 'custs'

This example renames the contact title column in the customers table to title.

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

[T-SQL] Add new column [Not Null] column in a table already having data

2010-10-20T00:05:00.000-07:00

This is a very simple question and asked many times, and answer is much simpler then the question.

Here is one example that we have to add a Transactiondate column into table CommissionEarned.

So here are the steps:

1. Add column without specifying not null
ALTER TABLE COMMISSIONEARNED ADD [CALCULATEDDATE] [datetime]
2. Add default constraint if its your business requirement
ALTER TABLE COMMISSIONEARNED ADD CONSTRAINT [DF_CE_CALCULATEDDATE] DEFAULT (getdate()) FOR [CALCULATEDDATE]
3. Update field with the data according to your business logic
UPDATE COMMISSIONEARNED SET CALCULATEDDATE=GETDATE()
4. Update field so that it should not entertain not null any more
ALTER TABLE COMMISSIONEARNED ALTER COLUMN [CALCULATEDDATE] [datetime] NOT NULL

Let me know for any questions

[T-SQL] My favourite SQL shortcuts

2010-10-18T02:53:00.000-07:00

Today i would tell you my favourite sql shortcuts and the way to setup.

In SSMS , go to Tools and then Options.

In the tab of Options , you can setup what sql statements you run often.

You can execute them very simply , just highligh the table and press what ever key you have configured.

[T-SQL] Stored Procedure Advantage

2010-10-13T03:02:00.000-07:00

One of the SQL Expert was asked this question and which one is the best advantage, so here is the answer:

There are many advantages of Stored Procedures. I was once asked what do I think is the most important feature of Stored Procedure? I have to pick only ONE. It is tough question.
I answered : Execution Plan Retention and Reuse (SP are compiled and their execution plan is cached and used again to when the same SP is executed again)

Not to mentioned I received the second question following my answer : Why? Because all the other advantage known (they are mentioned below) of SP can be achieved without using SP. Though Execution Plan Retention and Reuse can only be achieved using Stored Procedure only.

Execution plan retention and reuse
Query auto-parameterization
Encapsulation of business rules and policies
Application modularization
Sharing of application logic between applications
Access to database objects that is both secure and uniform
Consistent, safe data modification
Network bandwidth conservation
Support for automatic execution at system start-up
Enhanced hardware and software capabilities
Improved security
Reduced development cost and increased reliability
Centralized security, administration, and maintenance for common routines

[T-SQL] Move TempDB from one drive to another drive if TempDB is full.

2010-10-13T02:42:00.000-07:00

If you come across following errors in log file, please follow the direction below.

Source: MSSQLSERVER
Event ID: 17052
Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
up SOME LOG SPACE

Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to autogrow.

Move TempDB from one drive to another drive. There are major two reasons why TempDB needs to move from one drive to other drive.
1) TempDB grows big and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.

Follow direction below exactly to move database and log from one drive (c:) to another drive (d:) and (e:).

Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB.
USE TempDB
GO
EXEC sp_helpfile
GO

Results will be something like:
name fileid filename filegroup size
——- —— ————————————————————– ———- ——-
tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB
along with other information related to the database. The names of the files are usually tempdev and demplog by default. These names will be used in next statement. Run following code, to move mdf and ldf files.
USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:datatemplog.ldf')
GO

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

[T-SQL] How to remove duplicate data

2010-10-13T02:32:00.000-07:00

One of the very common question that is asked many times , tell the way how can we delete the duplicate data or an easiest way or fastest way.

This method has one limitation that the table should have a primary key.

DELETE
FROM TableName
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TableName
GROUP BY Col1, Col2, Col3)

*TableName should be replaced with Table Name
*Col1 , Col2 and Col3 must be replaced with the criteria on which records will qualify as duplicate. It can be increase and decrease.

[T-SQL] How to generate CSV from Table Data

2010-10-13T02:24:00.000-07:00

Here is a very simple code:

DECLARE @Str VARCHAR(MAX)
SELECT @Str = COALESCE(@Str+',' , '') + FieldName
FROM TableName
SELECT @Str

*Field Name should be replaced with Field Name
*Table Name should be replaced with Table Name

[T-SQL] Query Analyzer Shortcuts

2010-10-13T02:20:00.000-07:00

Shortcut	Function	Shortcut	Function
ALT+BREAK	Cancel a query	CTRL+SHIFT+F2	Clear all bookmarks
ALT+F1	Database object information	CTRL+SHIFT+INSERT	Insert a template
ALT+F4	Exit	CTRL+SHIFT+L	Make selection lowercase
CTRL+A	Select all	CTRL+SHIFT+M	Replace template parameters
CTRL+B	Move the splitter	CTRL+SHIFT+P	Open
CTRL+C	Copy	CTRL+SHIFT+R	Remove comment
CTRL+D	Display results in grid format	CTRL+SHIFT+S	Show client statistics
CTRL+Delete	Delete through the end of the line	CTRL+SHIFT+T	Show server trace
CTRL+E	Execute query	CTRL+SHIFT+U	Make selection uppercase
CTRL+F	Find	CTRL+T	Display results in text format
CTRL+F2	Insert/remove bookmark	CTRL+U	Change database
CTRL+F4	Disconnect	CTRL+V	Paste
CTRL+F5	Parse query and check syntax	CTRL+W	Window selector
CTRL+G	Go to line	CTRL+X	Delete
CTRL+H	Replace	CTRL+Z	Undo
CTRL+I	Index Tuning Wizard	F1	Help for Query Analyzer
CTRL+K	Display/hide execution plan	F2	Move to next bookmark
CTRL+L	Display execution plan	F3	Repeat last search
CTRL+N	New query window	F4	Object Search
CTRL+O	Connect	F5	Execute a query
CTRL+P	Print	F6	Switch between query and result panes
CTRL+R	Show/Hide results pane	F8	Show/hide Object Browser
CTRL+S	Save	SHIFT+F1	Transact-SQL help
CTRL+SHIFT+0	Show options	SHIFT+F2	Move to previous bookmark
CTRL+SHIFT+C	Comment out code	SHIFT+TAB	Decrease indent
CTRL+SHIFT+DEL	Clear the active Editor pane	SHIFT+F6	Switch panes
CTRL+SHIFT+F	Save results to file	TAB	Increase indent

[T-SQL] What is – DML, DDL, DCL and TCL – Introduction and Examples

2010-10-11T22:20:00.001-07:00

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT statements

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Examples: GRANT, REVOKE statements

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

[My-Exp] Job Description for DBA/DB Developer

2010-10-11T22:13:00.000-07:00

This JD is copied from one of the SQL Server Experts:

Job Description of Database Administrator (DBA) or Database Developer

Develop standards and guidelines to guide the use and acquisition of software and to protect vulnerable information.

Modify existing databases and database management systems or direct programmers and analysts to make changes.

Test programs or databases, correct errors and make necessary modifications.

Plan, coordinate and implement security measures to safeguard information in computer files against accidental or unauthorized damage, modification or disclosure.

Approve, schedule, plan, and supervise the installation and testing of new products and improvements to computer systems, such as the installation of new databases.

Train users and answer questions.

Establish and calculate optimum values for database parameters, using manuals and calculator.

Specify users and user access levels for each segment of database.

Develop data model describing data elements and how they are used, following procedures and using pen, template or computer software.

Develop methods for integrating different products so they work properly together, such as customizing commercial databases to fit specific needs.

Review project requests describing database user needs to estimate time and cost required to accomplish project.

Review procedures in database management system manuals for making changes to database.

Work as part of a project team to coordinate database development and determine project scope and limitations.

Select and enter codes to monitor database performance and to create production database.

Identify and evaluate industry trends in database systems to serve as a source of information and advice for upper management.

Write and code logical and physical database descriptions and specify identifiers of database to management system or direct others in coding descriptions.

Review workflow charts developed by programmer analyst to understand tasks computer will perform, such as updating records.

Revise company definition of data as defined in data dictionary.

[T-SQL] Create and Encrypted Stored Procedure

2010-10-11T22:06:00.000-07:00

An answer to a very simple question , how to create a stored procedure any problem if we create it.

Here is the script

USE AdventureWorks
GO
-- Create Encyrpted SP
CREATE PROCEDURE uspEnc
WITH ENCRYPTION
AS
SELECT *
FROM Sales.SalesOrderDetail
UNION
SELECT *
FROM Sales.SalesOrderDetail
GO
-- Execute SP
EXEC uspEnc
GO

Question: if any stored procedure is encrypted can we see its definition in Activity Monitor.
Answer: No

Question: Any tool to decrypt the SP
Answer: SQL Decryptor, Decrypt SQL

Question: Should a DBA recommend it
Answer: Encrypted stored procedures are horrible. You can’t see what queries are executed, you don’t have a clue what the execution plan is. So if users are complaining about the slowness of the system, and everything is encrypted… then it will not be possible to look into it.

[T-SQL] Get Running Query from the Sessions

2010-10-11T21:56:00.000-07:00

If you want to get the query running of one of the session then you can get from the query below , just answering that if you want to know the running sessions then you can use sp_who and sp_who2.

Before executing this statement please change the option from result to grid to result to text. For this you can use shortcut CTRL+T.

Here is the script, 55 can be replaced from your desired session id.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 55
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

[T-SQL] Get Numbers from Alpha Numeric Value

2010-10-11T21:40:00.000-07:00

Here is the procedure i just saw from one of the sql expert blog that will rreturn Numbers from Alpha Numeric Values.

CREATE PROCEDURE [dbo].[GetNumbers]
@alpha VARCHAR(50),
@decimal DECIMAL(14, 5) OUTPUT
AS BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMsg VARCHAR(50)
DECLARE @Pos INT
DECLARE @CommaPos INT
DECLARE @ZeroExists INT
DECLARE @alphaReverse VARCHAR(50)
DECLARE @NumPos INT
DECLARE @Len INT
-- 1 Reverse the alpha in order to get the last position of a numeric value
SET @alphaReverse = REVERSE(@alpha)
-- 2 Get the last position of a numeric figure
SET @NumPos = PATINDEX('%[0-9]%', @alphaReverse)
-- 3 Get the lenght of the string
SET @Len = LEN(@alpha)
-- 4 Add a comma after the numeric data in case it's no decimal number
SET @alpha = SUBSTRING(@alpha, 1, (@Len - @NumPos + 1))
+ ','
+ SUBSTRING(@alpha, (@Len - @NumPos + 2), 50)
-- Check if there is a zero (0) in the @alpha, then we later set the @decimal to 0
-- if it's 0 after the handling, else we set @decimal to NULL
-- If 0 no match, else there is a match
SET @ZeroExists = CHARINDEX ( '0' , @alpha ,1 )
-- Find position of , (comma)
SET @CommaPos = 1
SET @CommaPos = PATINDEX('%,%', @alpha)
IF (@CommaPos = '') BEGIN
SET @CommaPos = 20
END
SET @Pos = PATINDEX('%[^0-9]%',@alpha)
-- Replaces any aplha with '0' since we otherwice can't keep track of where the decimal
-- should be put in. We assume the numeric number has no aplhe inside. The regular way
-- to solve this is to replace with ”, but then we miss the way to find the place to
-- put in the decimal.
WHILE (@Pos > 0) BEGIN
SET @alpha = STUFF(@alpha, @pos, 1, '0')
SET @Pos = PATINDEX('%[^0-9]%',@alpha)
END
IF (@alpha IS NOT NULL AND @alpha != '') BEGIN
SET @decimal = CONVERT(DECIMAL(14, 5), SUBSTRING(@alpha, 1, (@CommaPos - 1))
+ '.'
+ SUBSTRING(@alpha, (@CommaPos + 1), 20))
END
-- Since we in this case don't want to set 0 if where is no numeric value, we set NULL to be safe
IF (@decimal = 0 AND @ZeroExists = 0) BEGIN
SET @decimal = NULL
END
END
GO

If you run above SP as shown below it will work
DECLARE @myVal DECIMAL(14,5)
EXEC [GetNumbers] 'ABC455,88ghf', @myVal OUTPUT
SELECT @myVal RValue

RValue
---------------------------------------
455.88000

[My-Exp] Query Optimized

2010-10-08T00:55:00.000-07:00

Few days back i blogged that my manager said to me to optimize an sp , so here is the story for this:

The sp was not running for one day data even in 45 mins, but now its running in 4 mins for one month data. So here are the tips of what i did.

1. Added "with recompile" in sp.
2. Added "SET NOCOUNT ON; "
3. Applied Parameter sniffing
4. Created two Non clustered indexes on one of the table.

[T-SQL] Clear Cache and Buffer of Stored Procedure

2010-10-07T04:05:00.000-07:00

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

[T-SQL] Index rebuild or reorganize , which one to use ?

2010-10-06T23:23:00.001-07:00

Index Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO

Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

[T-SQL] How to find index fragmentation

2010-10-06T22:38:00.000-07:00

This script will tell you the index fragmentation stats:

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID

[T-SQL] Identify Blocking Query / Session

2010-10-06T21:57:00.000-07:00

One of the most common problem that DBA faces is that one or many sessions get blocked by some sessions due to long execution or deadlock. I have many times faced this problem and also read the same problem which is faced by many DBAs every day.

Here is the query by which you can kill the blocking session:
Kill 3 ( 3 is the blocking session )

But now your question would be how to identify the blocking session , so here we go...

SELECT

db.name DBName,

tl.request_session_id,

wt.blocking_session_id,

OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,

tl.resource_type,

h1.TEXT AS RequestingText,

h2.TEXT AS BlockingTest,

tl.request_mode

FROM sys.dm_tran_locks AS tl

INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id

INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address

INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id

INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id

INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id

CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1

CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

GO

[T-SQL] Optimize Stored Procedure

2010-10-05T22:58:00.000-07:00

Today in morning when i came , i was having an email of my boss telling that there is an high priority issue which need to solved on immediate basis. And it was to optimize sp as it was creating a huge problem. I am currently working on it but here are some tips to get the job done:

1. Include SET NOCOUNT ON statement:

With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.
CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

2. Use schema name with object name:

The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like
SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method

3. Do not use the prefix “sp_” in the stored procedure name:

If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:
IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')
Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:
DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

4. Try to avoid using SQL Server cursors whenever possible:

Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.

5. Use TRY-Catch for error handling:

Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:
BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

[T-SQL] Restore database trough mdf file only , ldf missing

2010-10-05T02:41:00.000-07:00

One of my friend just call me and told that he has an mdf file but no ldf file , so if want to restore db then what to do , i searched over internet and found two fastest methods for it

USE [master]
GO
-- Method 1: I use this method
EXEC sp_attach_single_file_db @dbname='TestDb',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf'
GO
-- Method 2:
CREATE DATABASE TestDb ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH_REBUILD_LOG
GO

[T-SQL] Kill All Process in Database

2010-10-05T02:20:00.000-07:00

I was just going trough one query and find out that it was giving one error , but its really useful script for killing all process. One thing just be sure that you are not killing the process from the db instance from where you are running the query.

CREATE TABLE #TmpWho
(spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150),
hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150), rquest_id INT)
INSERT INTO #TmpWho
EXEC sp_who
DECLARE @spid INT
DECLARE @tString VARCHAR(15)
DECLARE @getspid CURSOR
SET @getspid = CURSOR FOR
SELECT spid
FROM #TmpWho
WHERE dbname = 'mydb'OPEN @getspid
FETCH NEXT FROM @getspid INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(5))
EXEC(@tString)
FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid
DEALLOCATE @getspid
DROP TABLE #TmpWho
GO

[T-SQL] Renaming the Physical Filename for Datafiles in SQL Server 2008

2010-10-04T22:03:00.000-07:00

I just got a question in morning , that how can one rename the physical filename for data files in sql server 2008. So here is the link and then some of the methods which ever suits you

http://technet.microsoft.com/en-us/library/ms174269.aspx

There are several ways to make this change, however to rename the physical database files at operating system level you will have to take the database offline

1. Use SSMS to take the database Offline (right-click on Database, select Tasks, Take Offline), change the name of the files at the OS level and then Bring it Online.

2. You could Detach the database, rename the files and then Attach the database pointing to the renamed files to do so.

3. You could Backup the database and then restore, changing the file location during the restore process.

4. using T SQL

ALTER DATABASE databaseName SET OFFLINE
GO

ALTER DATABASE databaseNAme MODIFY FILE (NAME =db, FILENAME = 'C:\Program
Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.mdf')
GO

--if changing log file name
ALTER DATABASE databaseNAme MODIFY FILE (NAME = db_log, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.ldf')
GO

ALTER DATABASE databaseName SET ONLINE
GO

Differnces between a RDBMS schema and a data warehouse schema

2010-09-30T01:45:00.000-07:00

RDBMS Schema
* Main Purpose: Record
* Used for OLTP systems
* Highly Normalized
* Difficult to understand and navigate
* Difficult to extract and solve complex problems

DWH Schema
* Main Purpose: Respond
* Used for OLAP systems
* De-normalized
* Easy to understand and navigate
* Relatively easier in extracting the data and solving complex problems

Differences between OLTP and OLAP

2010-09-30T01:42:00.001-07:00

Main Differences between OLTP and OLAP are:-
1. User and System Orientation
OLTP: customer-oriented, used for data analysis and querying by clerks, clients and IT professionals.
OLAP: market-oriented, used for data analysis by knowledge workers( managers, executives, analysis).
2. Data Contents
OLTP: manages current data, very detail-oriented.
OLAP: manages large amounts of historical data, provides facilities for summarization and aggregation, stores information at different levels of granularity to support decision making process.
3. Database Design
OLTP: adopts an entity relationship(ER) model and an application-oriented database design.
OLAP: adopts star, snowflake or fact constellation model and a subject-oriented database design.
4. View
OLTP: focuses on the current data within an enterprise or department.
OLAP: spans multiple versions of a database schema due to the evolutionary process of an organization; integrates information from many organizational locations and data stores

Difference between Database and Data Warehouse

2010-09-27T04:43:00.000-07:00

I was just searching on net for basic difference between the two of my core expertise and one of the best extract I found is pasted below:

So how is a data warehouse different from you regular database? After all, both are databases, and both have some tables containing data. If you look deeper, you'd find that both have indexes, keys, views, and the regular jing-bang. So is that 'Data warehouse' really different from the tables in you application? And if the two aren't really different, maybe you can just run your queries and reports directly from your application databases!

Well, to be fair, that may be just what you are doing right now, running some EOD (end-of-day) reports as complex SQL queries and shipping them off to those who need them. And this scheme might just be serving you fine right now. Nothing wrong with that if it works for you.

But before you start patting yourself on the back for having avoided a data warehouse altogether, do spend a moment to understand the differences, and to appreciate the pros and cons of either approach.

The primary difference betwen you application database and a data warehouse is that while the former is designed (and optimized) to record , the latter has to be designed (and optimized) to respond to analysis questions that are critical for your business.

Application databases are OLTP (On-Line Transaction Processing) systems where every transaction has to be recorded, and super-fast at that. Consider the scenario where a bank ATM has disbursed cash to a customer but was unable to record this event in the bank records. If this started happening frequently, the bank wouldn't stay in business for too long. So the banking system is designed to make sure that every trasaction gets recorded within the time you stand before the ATM machine. This system is write-optimized, and you shouldn't crib if your analysis query (read operation) takes a lot of time on such a system.

A Data Warehouse (DW) on the other end, is a database (yes, you are right, it's a database) that is designed for facilitating querying and analysis. Often designed as OLAP (On-Line Analytical Processing) systems, these databases contain read-only data that can be queried and analysed far more efficiently as compared to your regular OLTP application databases. In this sense an OLAP system is designed to be read-optimized.

Separation from your application database also ensures that your business intelligence solution is scalable (your bank and ATMs don't go down just because the CFO asked for a report), better documented and managed (god help the novice who is given the application database diagrams and asked to locate the needle of data in the proverbial haystack of table proliferation), and can answer questions far more efficietly and frequently.

Creation of a DW leads to a direct increase in quality of analyses as the table structures are simpler (you keep only the needed information in simpler tables), standardized (well-documented table structures), and often denormalized (to reduce the linkages between tables and the corresponding complexity of queries). A DW drastically reduces the 'cost-per-analysis' and thus permits more analysis per FTE. Having a well-designed DW is the foundation successful BI/Analytics initiatives are built upon.

[T-SQL] How to Stop Growing Log File Too Big

2010-09-26T23:25:00.000-07:00

In continuation of my last post "http://muhammadabbas.blogspot.com/2010/09/t-sql-truncate-log-file.html" , i am writing this article.

Today i was reading an article and this article highlighted that how could we stop the log file to grow. Here is the text:

Two different solutions for it:

1) Convert the Recovery Model to Simple Recovery

If you are truncating the transaction logs, this means you are breaking the T-Log LSN (Log Sequence Numbers). This follows that if disaster comes, you would not be able to restore your T-Logs and there would be no option for you to do point in time recovery. If you are fine with this situation and there is nothing to worry, I suggest that you change your recovery model to Simple Recovery Model. This way, you will not have extra ordinary growth of your log file.

2) Start Taking Transaction Log Backup

If your business does not support loss of data or requires having point in time recovery, you cannot afford anything less than Full Recovery Model. In Full Recovery Model, your transaction log will grow until you take a backup of it. You need to take the T-Log Backup at a regular interval. This way, your log would not grow beyond some limits. If you are taking an hourly T-Log backup, your T-Log would grow until one hour but after this the T-Log backup would truncate all the ‘committed’ transactions once you take it. Doing this would lead the size of the T-Log not to go down much, but it would rather be marked as empty for the next hour’s T-Log to populate.

With this method, you can restore your database at Point of Time if a disaster ever happens at your server.

Let us run an example to demonstrate this. In this case, I have done the following steps:

1.Create Sample Database in FULL RECOVERY Model
2.Take Full Backup (full backup is must for taking subsequent backup)
3.Repeat Following Operation
1.Take Log Backup
2.Insert Some rows
3.Check the size of Log File
4.Clean Up
After a short while, you will notice that the Log file (ldf) will stop increasing but the size of the backup will increase.

If you have an issue with your log file growth, I suggest that you follow either of the above solutions instead of truncating it.

/* FULL Recovery and Log File Growth */
USE [master]
GO
-- Create Database SimpleTran
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SimpleTran')
BEGIN
ALTER DATABASE [SimpleTran] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [SimpleTran]
END
GO
CREATE DATABASE [SimpleTran]
GO
-- Set Database backup model to FULL
ALTER DATABASE [SimpleTran] SET RECOVERY FULL
GO
BACKUP DATABASE [SimpleTran] TO DISK = N'D:\SimpleTran.bak' WITH NOFORMAT, NOINIT, NAME = N'SimpleTran-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Check Database Log File Size
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SimpleTran'
GO
-- Create Table in Database with Transaction
USE SimpleTran
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[RealTempTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[RealTempTable]
GO
CREATE TABLE RealTempTable (ID INT)
INSERT INTO RealTempTable (ID)
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the size of the Database
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SimpleTran'
GO
-- Take Full Backup
BACKUP LOG [SimpleTran] TO DISK = N'D:\SimpleTran.bak' WITH NOFORMAT, NOINIT, NAME = N'SimpleTran-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Run following transaction multiple times and check the size of T-Log
INSERT INTO RealTempTable (ID)
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the size of the Database
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SimpleTran'
GO
/*
Now run following code multiple times.
You will notice that it will not increase the size of .ldf file but will for sure
increasethe size of the log backup.
*/
-- Second Time
-- START
BACKUP LOG [SimpleTran] TO DISK = N'D:\SimpleTran.log' WITH NOFORMAT, NOINIT, NAME = N'SimpleTran-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Run following transaction and check the size of T-Log
INSERT INTO RealTempTable (ID)
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the size of the Database
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SimpleTran'
GO
-- END
--Clean Up
USE MASTER
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SimpleTran')
BEGIN
ALTER DATABASE [SimpleTran] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [SimpleTran]
END

[T-SQL] Truncate Log File

2010-09-26T23:19:00.000-07:00

Few days back , i was called by my friend who was working on community database with a problem that log file of his db was growing bigger and he was worried about it. So i went there and saw that its not a big issue and told him that i would prepare a script and give him so that he could run that once in a month:

USE TEST;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE TEST
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (TEST_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE TEST
SET RECOVERY FULL;
GO

[T-SQL] Generate Delete script for all tables

2010-09-23T02:24:00.001-07:00

You can use this script for generate delete script or even trncate

select 'delete from ' + o.name + ';'
from sysobjects o
where o.type = 'U';

[T-SQL] Delete all data in database (when you have FKs)

2010-09-22T23:22:00.001-07:00

SET NOCOUNT ON
GO

SELECT 'USE [' + db_name() +']';

SELECT 'ALTER TABLE ' +
'[' + s.name + '].[' + t.name + ']' +
' DROP CONSTRAINT [' + f.name +']'
FROM sys.foreign_keys f
INNER JOIN sys.TABLES t ON f.parent_object_id=t.object_id
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0;

SELECT 'TRUNCATE TABLE ' + '[' + s.name + '].[' + t.name + ']'
FROM sys.TABLES t
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0;

SELECT 'ALTER TABLE ' +
'[' + s.name + '].[' + t.name + ']' +
' ADD CONSTRAINT [' + f.name + ']' +
' FOREIGN KEY (' +
Stuff( (SELECT ', ['+col_name(fk.parent_object_id, fk.parent_column_id) +']'
FROM sys.foreign_key_columns fk
WHERE constraint_object_id = f.object_id
ORDER BY constraint_column_id
FOR XML Path('')
), 1,2,'') + ')' +
' REFERENCES [' +
object_schema_name(f.referenced_object_id)+'].['+object_name(f.referenced_object_id) + '] (' +
Stuff((SELECT ', ['+col_name(fc.referenced_object_id, fc.referenced_column_id)+']'
FROM sys.foreign_key_columns fc
WHERE constraint_object_id = f.object_id
ORDER BY constraint_column_id
FOR XML Path('')),
1,2,'') +
')' +
' ON DELETE ' + REPLACE(f.delete_referential_action_desc, '_', ' ') +
' ON UPDATE ' + REPLACE(f.update_referential_action_desc , '_', ' ') collate database_default
FROM sys.foreign_keys f
INNER JOIN sys.TABLES t ON f.parent_object_id=t.object_id
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0;

[T-SQL] Row Count in Tables

2010-09-20T02:23:00.001-07:00

Below is the query trough which you can get row counts from all the tables:

SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

[T-SQL] Find Auto Created Stats and Delete query for them

2010-09-20T02:06:00.001-07:00

Query for getting them:

SELECT name, OBJECT_NAME(OBJECT_ID)
FROM sys.stats
WHERE auto_created = 1;

Delete query for one of the table stats:

DROP STATISTICS TableName.StatisticsName

[Personal] Blessed by a baby boy

2010-09-16T04:27:00.000-07:00

On 23rd Ramazan , i was blessed by a baby boy. Too cuteeeee.

His grand father has kept his name "Ali Imran".

[T-SQL] Transport Level Error

2010-09-16T03:38:00.000-07:00

This is the error which i encountered now a days very much:

A transport-level error has occurred when sending the request to the
server.

Here is the reason for it:

You used to have an open connection to a server but you don’t any more. It was closed or killed from the server side. Maybe the server rebooted (or failed-over to another cluster node). You’re talking into one tin can, but nobody’s on the tin can on the other end of the string.

Annoying, but not a problem… open a new connection and try again. If you see this error message from inside SQL Server Management Studio, just hit F5 again (or Alt-X or Ctrl-E depending on preference).

[T-SQL] Very Nice Article Regarding T-SQL

2010-09-16T03:12:00.000-07:00

A very nice article regarding Dont's of T-SQL:

http://michaeljswart.com/?p=823

[T-SQL] Function to Retrieve First Word of Sentence

2010-08-23T23:58:00.000-07:00

--This code/function can help to get you first word from a sentence.
--TEST SCRIPT
DECLARE @StringVar VARCHAR(100)
SET @StringVar = ' anything '
SELECT CASE CHARINDEX(' ', LTRIM(@StringVar), 1)
WHEN 0 THEN LTRIM(@StringVar)
ELSE SUBSTRING(LTRIM(@StringVar), 1, CHARINDEX(' ',LTRIM(@StringVar), 1) - 1)
END FirstWordofSentence

--FUNCTION CODE
CREATE FUNCTION GetFirstWord (@StringVar VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @rtStringVar VARCHAR(MAX)
SELECT @rtStringVar = CASE CHARINDEX(' ', LTRIM(@StringVar), 1)
WHEN 0 THEN LTRIM(@StringVar)
ELSE SUBSTRING(LTRIM(@StringVar), 1, CHARINDEX(' ',LTRIM(@StringVar), 1) - 1)
END
RETURN @rtStringVar
END

[T-SQL] To concatenate the values of a column in all rows into one row.

2010-08-18T22:20:00.001-07:00

USE pubs
GO
DECLARE @title_ids varchar(150), @delimiter char
SET @delimiter = ','
SELECT @title_ids = COALESCE(@title_ids + @delimiter, '') + title_id FROM titles
SELECT @title_ids AS [List of Title IDs]

[T-SQL] To convert a given string to proper case

2010-08-18T22:18:00.001-07:00

CREATE FUNCTION PROPERCASE
(
--The string to be converted to proper case
@input varchar(8000)
)
--This function returns the proper case string of varchar type
RETURNS varchar(8000)
AS
BEGIN
IF @input IS NULL
BEGIN
--Just return NULL if input string is NULL
RETURN NULL
END

--Character variable declarations
DECLARE @output varchar(8000)
--Integer variable declarations
DECLARE @ctr int, @len int, @found_at int
--Constant declarations
DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int

--Variable/Constant initializations
SET @ctr = 1
SET @len = LEN(@input)
SET @output = ''
SET @LOWER_CASE_a = 97
SET @LOWER_CASE_z = 122
SET @Delimiter = ' ,-'
SET @UPPER_CASE_A = 65
SET @UPPER_CASE_Z = 90

WHILE @ctr <= @len BEGIN --This loop will take care of reccuring white spaces WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
SET @ctr = @ctr + 1
END

IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
BEGIN
--Converting the first character to upper case
SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END

SET @ctr = @ctr + 1

WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
BEGIN
IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
BEGIN
SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END
SET @ctr = @ctr + 1
END

END
RETURN @output
END

[T-SQL] Get Biggest Table on Your Production DB

2010-08-18T22:16:00.000-07:00

USE master
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'sp_show_huge_tables' AND type = 'P')
DROP PROC sp_show_huge_tables
GO

CREATE PROC sp_show_huge_tables
(
@top int = NULL,
@include_system_tables bit = 0
)
AS

BEGIN
IF @top > 0
SET ROWCOUNT @top

SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U') AND
((@include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))

) as a
ORDER BY [Total space used (MB)] DESC

SET ROWCOUNT 0
END

GO

GRANT EXEC ON sp_show_huge_tables TO Public

[T-SQL] Procedure to script your data (to generate INSERT statements from the existing data)

2010-08-18T22:11:00.000-07:00

SET NOCOUNT ON
GO

PRINT 'Using Master database'
USE master
GO

PRINT 'Checking for the existence of this procedure'
IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL --means, the procedure already exists
BEGIN
PRINT 'Procedure already exists. So, dropping it'
DROP PROC sp_generate_inserts
END
GO

CREATE PROC sp_generate_inserts
(
@table_name varchar(776), -- The table/view for which the INSERT statements will be generated using the existing data
@target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted
@include_column_list bit = 1, -- Use this parameter to include/ommit column list in the generated INSERT statement
@from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE)
@include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement
@debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
@owner varchar(64) = NULL, -- Use this parameter if you are not the owner of the table
@ommit_images bit = 0, -- Use this parameter to generate INSERT statements by omitting the 'image' columns
@ommit_identity bit = 0, -- Use this parameter to ommit the identity columns
@top int = NULL, -- Use this parameter to generate INSERT statements only for the TOP n rows
@cols_to_include varchar(8000) = NULL, -- List of columns to be included in the INSERT statement
@cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the INSERT statement
@disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the INSERT statements
@ommit_computed_cols bit = 0 -- When 1, computed columns will not be included in the INSERT statement

)
AS
BEGIN

/***********************************************************************************************************
Procedure: sp_generate_inserts (Build 22)
(Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.)

Purpose: To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some other location.
This procedure is also useful to create a database setup, where in you can
script your data along with your table definitions.

Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com

Acknowledgements:
Divya Kalra -- For beta testing
Mark Charsley -- For reporting a problem with scripting uniqueidentifier columns with NULL values
Artur Zeygman -- For helping me simplify a bit of code for handling non-dbo owned tables
Joris Laperre -- For reporting a regression bug in handling text/ntext columns

Tested on: SQL Server 7.0 and SQL Server 2000 and SQL Server 2005

Date created: January 17th 2001 21:52 GMT

Date modified: May 1st 2002 19:50 GMT

Email: vyaskn@hotmail.com

NOTE: This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL Server 2000's sql_variant data types
Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results
IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed
you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts
like nchar and nvarchar

ALSO NOTE THAT THIS PROCEDURE IS NOT UPDATED TO WORK WITH NEW DATA TYPES INTRODUCED IN SQL SERVER 2005 / YUKON

Example 1: To generate INSERT statements for table 'titles':

EXEC sp_generate_inserts 'titles'

Example 2: To ommit the column list in the INSERT statement: (Column list is included by default)
IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below,
to avoid erroneous results

EXEC sp_generate_inserts 'titles', @include_column_list = 0

Example 3: To generate INSERT statements for 'titlesCopy' table from 'titles' table:

EXEC sp_generate_inserts 'titles', 'titlesCopy'

Example 4: To generate INSERT statements for 'titles' table for only those titles
which contain the word 'Computer' in them:
NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that you are good with T-SQL if you are using this parameter

EXEC sp_generate_inserts 'titles', @from = "from titles where title like '%Computer%'"

Example 5: To specify that you want to include TIMESTAMP column's data as well in the INSERT statement:
(By default TIMESTAMP column's data is not scripted)

EXEC sp_generate_inserts 'titles', @include_timestamp = 1

Example 6: To print the debug information:

EXEC sp_generate_inserts 'titles', @debug_mode = 1

Example 7: If you are not the owner of the table, use @owner parameter to specify the owner name
To use this option, you must have SELECT permissions on that table

EXEC sp_generate_inserts Nickstable, @owner = 'Nick'

Example 8: To generate INSERT statements for the rest of the columns excluding images
When using this otion, DO NOT set @include_column_list parameter to 0.

EXEC sp_generate_inserts imgtable, @ommit_images = 1

Example 9: To generate INSERT statements excluding (ommiting) IDENTITY columns:
(By default IDENTITY columns are included in the INSERT statement)

EXEC sp_generate_inserts mytable, @ommit_identity = 1

Example 10: To generate INSERT statements for the TOP 10 rows in the table:

EXEC sp_generate_inserts mytable, @top = 10

Example 11: To generate INSERT statements with only those columns you want:

EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"

Example 12: To generate INSERT statements by omitting certain columns:

EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"

Example 13: To avoid checking the foreign key constraints while loading data with INSERT statements:

EXEC sp_generate_inserts titles, @disable_constraints = 1

Example 14: To exclude computed columns from the INSERT statement:
EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1
***********************************************************************************************************/

SET NOCOUNT ON

--Making sure user only uses either @cols_to_include or @cols_to_exclude
IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))
BEGIN
RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)
RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
END

--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))
BEGIN
RAISERROR('Invalid use of @cols_to_include property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'
RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property
END

IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))
BEGIN
RAISERROR('Invalid use of @cols_to_exclude property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'
RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property
END

--Checking to see if the database name is specified along wih the table name
--Your database context should be local to the table for which you want to generate INSERT statements
--specifying the database name is not allowed
IF (PARSENAME(@table_name,3)) IS NOT NULL
BEGIN
RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)
RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed
END

--Checking for the existence of 'user table' or 'view'
--This procedure is not written to work on system tables
--To script the data in system tables, just create a view on the system tables and script the view instead

IF @owner IS NULL
BEGIN
IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL))
BEGIN
RAISERROR('User table or view not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table or view.'
RETURN -1 --Failure. Reason: There is no user table or view with this name
END
END
ELSE
BEGIN
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @owner)
BEGIN
RAISERROR('User table or view not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table or view.'
RETURN -1 --Failure. Reason: There is no user table or view with this name
END
END

--Variable declarations
DECLARE @Column_ID int,
@Column_List varchar(8000),
@Column_Name varchar(128),
@Start_Insert varchar(786),
@Data_Type varchar(128),
@Actual_Values varchar(8000), --This is the string that will be finally executed to generate INSERT statements
@IDN varchar(128) --Will contain the IDENTITY column's name in the table

--Variable Initialization
SET @IDN = ''
SET @Column_ID = 0
SET @Column_Name = ''
SET @Column_List = ''
SET @Actual_Values = ''

IF @owner IS NULL
BEGIN
SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'
END
ELSE
BEGIN
SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'
END

--To get the first column's ID

SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)

--Loop through all the columns of the table, to get the column names and their data types
WHILE @Column_ID IS NOT NULL
BEGIN
SELECT @Column_Name = QUOTENAME(COLUMN_NAME),
@Data_Type = DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE ORDINAL_POSITION = @Column_ID AND
TABLE_NAME = @table_name AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)

IF @cols_to_include IS NOT NULL --Selecting only user specified columns
BEGIN
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0
BEGIN
GOTO SKIP_LOOP
END
END

IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
BEGIN
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0
BEGIN
GOTO SKIP_LOOP
END
END

--Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1
BEGIN
IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column
SET @IDN = @Column_Name
ELSE
GOTO SKIP_LOOP
END

--Making sure whether to output computed columns or not
IF @ommit_computed_cols = 1
BEGIN
IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1
BEGIN
GOTO SKIP_LOOP
END
END

--Tables with columns of IMAGE data type are not supported for obvious reasons
IF(@Data_Type in ('image'))
BEGIN
IF (@ommit_images = 0)
BEGIN
RAISERROR('Tables with image columns are not supported.',16,1)
PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'
PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.'
RETURN -1 --Failure. Reason: There is a column with image data type
END
ELSE
BEGIN
GOTO SKIP_LOOP
END
END

--Determining the data type of the column and depending on the data type, the VALUES part of
--the INSERT statement is generated. Care is taken to handle columns with NULL values. Also
--making sure, not to lose any data from flot, real, money, smallmomey, datetime columns
SET @Actual_Values = @Actual_Values +
CASE
WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')
THEN
'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ('datetime','smalldatetime')
THEN
'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'
WHEN @Data_Type IN ('uniqueidentifier')
THEN
'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ('text','ntext')
THEN
'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ('binary','varbinary')
THEN
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
WHEN @Data_Type IN ('timestamp','rowversion')
THEN
CASE
WHEN @include_timestamp = 0
THEN
'''DEFAULT'''
ELSE
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
END
WHEN @Data_Type IN ('float','real','money','smallmoney')
THEN
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ',2)' + ')),''NULL'')'
ELSE
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ')' + ')),''NULL'')'
END + '+' + ''',''' + ' + '

--Generating the column list for the INSERT statement
SET @Column_List = @Column_List + @Column_Name + ','

SKIP_LOOP: --The label used in GOTO

SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
ORDINAL_POSITION > @Column_ID AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)

--Loop ends here!
END

--To get rid of the extra characters that got concatenated during the last run through the loop
SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)

IF LTRIM(@Column_List) = ''
BEGIN
RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)
RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
END

--Forming the final string that will be executed, to output the INSERT statements
IF (@include_column_list <> 0)
BEGIN
SET @Actual_Values =
'SELECT ' +
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END + '''' + RTRIM(@Start_Insert) + ' ''+' + '''(' + RTRIM(@Column_List) + '''+' + ''')''' + ' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' + COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)') ENDELSE IF (@include_column_list = 0) BEGIN SET @Actual_Values = 'SELECT ' + CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END + '''' + RTRIM(@Start_Insert) + ' '' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' + COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)') END --Determining whether to ouput any debug informationIF @debug_mode =1 BEGIN PRINT '/*****START OF DEBUG INFORMATION*****' PRINT 'Beginning of the INSERT statement:' PRINT @Start_Insert PRINT '' PRINT 'The column list:' PRINT @Column_List PRINT '' PRINT 'The SELECT statement executed to generate the INSERTs' PRINT @Actual_Values PRINT '' PRINT '*****END OF DEBUG INFORMATION*****/' PRINT '' END PRINT '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas'PRINT '--Build number: 22'PRINT '--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com'PRINT '--http://vyaskn.tripod.com'PRINT ''PRINT 'SET NOCOUNT ON'PRINT ''--Determining whether to print IDENTITY_INSERT or notIF (@IDN <> '')
BEGIN
PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON'
PRINT 'GO'
PRINT ''
END

IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)
BEGIN
IF @owner IS NULL
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
END
ELSE
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
END

PRINT 'GO'
END

PRINT ''
PRINT 'PRINT ''Inserting values into ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + ''''

--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes!
EXEC (@Actual_Values)

PRINT 'PRINT ''Done'''
PRINT ''

IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)
BEGIN
IF @owner IS NULL
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
END
ELSE
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
END

PRINT 'GO'
END

PRINT ''
IF (@IDN <> '')
BEGIN
PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF'
PRINT 'GO'
END

PRINT 'SET NOCOUNT OFF'

SET NOCOUNT OFF
RETURN 0 --Success. We are done!
END

GO

PRINT 'Created the procedure'
GO

--Mark procedure as system object
EXEC sys.sp_MS_marksystemobject sp_generate_inserts
GO

PRINT 'Granting EXECUTE permission on sp_generate_inserts to all users'
GRANT EXEC ON sp_generate_inserts TO public

SET NOCOUNT OFF
GO

PRINT 'Done'

/*
Usage:

Example 1: To generate INSERT statements for table 'titles':
EXEC sp_generate_inserts 'titles'
Example 2: To ommit the column list in the INSERT statement: (Column list is included by default)
NOTE: If you have too many columns, you are advised to ommit column list, as shown below, to avoid erroneous results
EXEC sp_generate_inserts 'titles', @Include_Column_List = 0
Example 3: To generate INSERT statements for 'titlesCopy' table from 'titles' table:
EXEC sp_generate_inserts 'titles', 'titlesCopy'
Example 4: To generate INSERT statements for 'titles' table for only those titles which contain the word 'Computer' in them:
EXEC sp_generate_inserts 'titles', @From = "from titles where title like '%Computer%'"
Example 5: To specify that you want to include TIMESTAMP column's data as well in the INSERT statement:
NOTE: By default TIMESTAMP column's data is not scripted
EXEC sp_generate_inserts 'titles', @Include_Timestamp = 1
Example 6: To print the debug information:
EXEC sp_generate_inserts 'titles', @debug_mode = 1
Example 7: If you are not the owner of the table, use @owner parameter to specify the owner name:
NOTE: To use this option, you must have SELECT permissions on that table
EXEC sp_generate_inserts Nickstable, @owner = 'Nick'
Example 8: To generate INSERT statements for the rest of the columns excluding images:
NOTE: When using this otion, DO NOT set @include_column_list parameter to 0
EXEC sp_generate_inserts imgtable, @ommit_images = 1
Example 9: To generate INSERT statements for the rest of the columns excluding IDENTITY column:
EXEC sp_generate_inserts mytable, @ommit_identity = 1
Example 10: To generate INSERT statements for the top 10 rows in the table:
EXEC sp_generate_inserts mytable, @top = 10
Example 11: To generate INSERT statements only with the columns you want:
EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"
Example 12: To generate INSERT statements by ommitting some columns:
EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"
Example 13: To avoid checking the foreign key constraints while loading data with INSERT statements:
NOTE: The @disable_constraints option will disable foreign key constraints, by assuming that the source data is valid and referentially sound
EXEC sp_generate_inserts titles, @disable_constraints = 1
Example 14: To avoid scripting data from computed columns:
EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1

*/

[T-SQL] Script for Split Function

2010-08-18T00:51:00.001-07:00

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
--how to use
select top 10 * from dbo.split('Karachi , Lahore , Isb',',')