Month: June 2008

SQL SSIS, insert only new rows.

SSIS workflow is really amazing when you have to add some automatism in your data flow processes. But sometimes is better to make same checks before start with a really dangerous BULK operation.

One of the most noise stuff I have to do is to launch insert of new records when I receive updates from web (web services, XML data). But when I do this I’m always afraid about duplicated rows.

Ho to check if a row exist, in SSIS?

SSIS Package contains a component called lookup. Very intuitive name. So if you need to insert only some new rows in a table you can build a workflow like this:

image

So we have three components:

  1. A OLEDB Datasource that reads data from the source table
  2. A LookUp component that check every row, if exists in the destination table. If doesn’t it redirects the rows in the error event.
  3. A Destination SQL that receive only the new rows, the rows that are in error in the lookup component.

Easy and automatic.

Bermuda Driving license.

Premise: if you come from Europe you will find this post very strange. The reason is that in Europe you can take an International driving license and use it where you want.

But not in Bermuda!! If you want to take a Bike (Motorbike) or a Car you have to get the local Driving license. In addition you have to take 4 steps to drive everything. The first one is for the 50cc bike, then 100cc, then Automatic car and finally manual car.

How to do it?

First you have to go to the TCD (Transport Control Department) and get the book (for only 3 USD) and a Form for your doctor (the local one …).

After this you have to pay around 60 USD to take the write exam on a computer, but the good thing is that if you mistake the exam you can retake it without paying anymore.

Now, at this point, you have to take the driving exam, but beware, because here is not like Europe, there aren’t driving school, so you need some local that give you his bike for a while to take practice and also, the driving test.

I will let you know when I will take my.

Write intelligent query with if exist … drop.

Usually when we build a query we start the .sql file, or command in the query editor with a simple command … CREATE PROCEDURE … bla bla bla.

Ok but if I’m building a database from scratch is very probably that my procedure will be modified a lot of time. So every time we have to launch the command DROP PROCEDURE, or ALTER PROCEDURE.

This sounds good if you are building your object. But in case of you want to store as a BACKUP PROCEDURE some scripts in the network, is better to produce this scripts as ATOMIC and AUTOMATIC.

To accomplish this task, we can use a function really useful in SQL SERVER, IF [OBJECT] EXIST, DROP IT!

So now you can run the script as much time as you want.

SQL 2005 and Linked server (Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO”)

If you have a SQL 2005 machine with a (x64) installation, some of the system queries have a different name, like [some_command]sys_64. So when you try to connect your powerfull SQL2005 to an old remote SQL2000, probably in x32 version, you can receive a strange error like Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO”.

In many forums you can find a link to a microsoft KB that explains to you that you have to install the SP4 in the old SQL and maybe everything will done.
Usually I like to know why something doesn’t run …

So, when you execute from SQL2005 a query like
select * from sql2000.mybase.dbo.mytable

SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N’mytable’, N’dbo’, NULL

So you can also try to add this stored in the Remote SQL2000 server, in the master database:

create procedure sp_tables_info_rowset_64
     @table_name sysname,
     @table_schema     sysname = null,  
     @table_type nvarchar(255) = null
as
declare @Result int set @Result = 0

exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

It works and you don’t need to run strange Package on your critical machine.

Microsoft SQL Server 2005 Certifications Roadmap.

This year is a new year for me for everything, why?

New location (Bermuda), new job (IT Manager) and new technologies (no more NET dev but SQL Dev). So why don’t put in my life another new interest in different certifications? I have decided to became a MCITP (Microsoft Certified IT Professional). Here you can find a detailed description of all the seminaries.

For MS SQL 2005 there are 3 ways. All the ways have the same startup. Exam 70-431  TS: Microsoft SQL Server 2005 – Implementation and Maintenance. With this exam you gain the MCTS SQL 2005. Now you can choose one of the three available profiles.

MCTIP Database Developer.
Microsoft Certified IT Professional: Database Developer certification demonstrates that you can design a secure, stable, enterprise database solution by using Microsoft SQL Server 2005.

Exams

70-441 – PRO: Designing Database Solutions by Using Microsoft SQL Server 2005

70-442 – PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005

MCTIP Database Administrator.
Microsoft Certified IT Professional: Database Administrator (MCITP: Database Administrator) is the premier certification for database server administrators. This certification demonstrates that you can keep up with your enterprise business solutions 24 hours a day, 7 days a week.

Exams

70-443 – PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005

70-444 – PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005

MCTIP Business Intelligence.
Microsoft Certified IT Professional: Business Intelligence Developer certification demonstrates that you can design analysis solutions, data transformations, and reports. Business intelligence developers design and implement multi-dimensional database models (logical and physical), data marts, data warehousing, data transforms, data analytics, and reporting solutions. This includes programming and customizing servers that use Multidimensional Expressions (MDX), customer transforms, and custom reporting solutions. Business intelligence developers are typically employed by medium-sized to large organizations.

Exams

70-445 – TS: Microsoft SQL Server 2005 Business Intelligence – Implementation and Maintenance

70-446 – PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005

I would like to get in first step the Database Administrator, that is the most hard to get. And in a second time I will start with the Database Developer. But if I have to honest what I love is the ETL process, so for me the dream is to get also the complete knowledge of how to build a strong Data warehouse.

Starting from now stay tuned because I will post a lot of info about SQL.