SSIS on SQL Server 2008 R2

Hi all,

These days I had a chance to take a look at SQL Server Integration Service 2008 R2 (SSIS) and do you know what? it is a really good thing when you need to Extract, Transform and Load (ETL) data from a DataBase to another.

However, unfortunately it is very buggy and is still like something that is not fully implemented, like not all the functionality was implemented till the end.
What I've found during several days of investigations on SSIS is:
1) it is very hard to maintain;

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count = %ld, Current count = %ld

Such an error has appeared these days to me in SQL using SPROC in another SPROC where these are using transactions:


Msg 266, Level 16, State 2, Procedure SPROC_NAME2, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

So what is that???
I will explain.

SQL: Concatinate Rows into a string without using variables

Hi All,

Here is a small SQL script which allows you to concatenate the values from all rows into a single row/value.


SELECT T.value + ',' AS [text()]
FROM myTable T
FOR XML PATH ('')

SQL: Rename Column/Table Name

Hi All,

We all sometimes need to rename a column/table from our database, however we do not want to drop all our records from the table where the column is renamed or the name of that table is changed.

For that there is such a nice SPROC in MS SQL: sp_rename.

This SPROC can be used to rename any user-created object in current DB.

However there is also a caution:

SQL Performance Tips 5

Hello,

Another SQL Performance tip, which will help you to determine the exact execution time of your sql statements. The simplest way of doing that is to save the time before running your statement and then compare that value with current time. Here is a sample SQL statement:

SQL - trigger fired when table is not updated

Hi,

First of all what microsoft tells us about triggers:

"A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected."

            Taken from: http://msdn.microsoft.com/en-us/library/ms189799.aspx

There is something interesting in above sentences, I suppose many of us would not understand correctly the last sentence: "These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected". I have also walked through these problems some days before.

Code 128 C - SQL Function

The Code 128 barcode is a high-density linear symbology that encodes text, numbers, numerous functions and the entire 128 ASCII character set (from ASCII 0 to ASCII 128.) It is commonly used for several implementations; and is also referred to as ISBT-128, GS1-128, UCC-128, EAN-128 and USS Code 128.

Code 128 contains 106 different printed barcode patterns. Each printed barcode may have one of three different meanings depending upon which of the character sets are being used, with the availability of three different Code 128 start characters to program the initial character set. Functions are also provided in the barcode symbology to switch between character sets and encode Application Identifiers. The Code 128 barcode may be complex to use because of the different character sets, which is the primary reason IDAutomation provides the Code 128 auto function, "Code128( )", in several of the Font Tools, Components and Applications.
The complete Code 128 barcode consists of a start character, data digits, a modulo 103 check digit and a stop character.

Many developers are searching a way of how to correctly encode the string using SQL.
I had a need to dispaly a 10 characters long string (containing numbers only) as a barcode, this is why I had first to encode it correctly in Code 128 C format.

MS SQL RowID equivalent

Hello everyone,

Many MS SQL developers ask if there is an equivalent to RowID from ORACLE in MS SQL.

The answers is NO.

But there is a workaround. You can use ROW_NUMBER() instead and where you will need to specify based on which columns to generate RowID, the order in which the ROW_NUMBER value is assigned to the rows in a partition.

Here is an example:

SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode
FROM Sales.vSalesPerson

You can see more details on Microsoft's page about ROW_NUMBER() function: http://msdn.microsoft.com/en-us/library/ms186734.aspx

Simple, isn't it?

SQL - How to check if a string has uppercase / lowercase characters

Hello,

When you need to compare two strings in SQL to check if this string contains uppercase or lowercase values it is always a problem, because in sql "ExTremeDev.blogSpot.com23" is equal to "extremedev.blogspot.com23".

I also needed to do such thing, in order to make some rules for a password, so that it will contain numbers, uppercase characters and lowercase characters.

Here I will provide an example like that: