Archive

Archive for the ‘Logging’ Category

what does NULLIF() do ? how is it different from ISNULL()?

October 7, 2010 3 comments

NULLIF( ) : Returns a null value if the two expressions specified in the function are equal.

The syntax used is

NULLIF ( expression1 ,expression2 )

If expression1 = expression2, then the function will return NULL.

ISNULL( ): The function replaces a null expressions with a specified value.

The syntax used is

Continue Reading …

SSIS – Skipping rows and columns in Excel

October 7, 2010 Leave a comment

Today, I came across a requirement, where the source excel sheet had data starting from row number 6 and column B. This was due to formating in the excel, which was also used as a report by many stake holders.

Although at first it might seem like there are no options in excel source to skip certain rows from the beginning of the sheet, it is not true. Fortunately, excel source provides the feature to select only required records from any sheet.  the OpenRowset property on the Excel Datasource component  allows you to specify the range to be considered by the datasource.

  By specifying a range, I was able to ignore all the titles and data spread all over the sheet . The configuration is simple and is as shown in the screenshot below.

Continue Reading ….

 

Script to List Linked Servers

October 7, 2010 Leave a comment

In a large server , it is very common to create high number of linked server connections. it might be required ( not very often) to use this list in the SPs or other objetcs created. Below is a simple query / script that can provide the list of linked servers used.

Continue Reading ..

What is @@IDENTITY ?

January 25, 2010 Leave a comment

Very often, it is important to check the last identity value inserted into a table. This can be easily done using the system function “@@IDENTITY” . This System function returns the last inserted identity value. 

The function returns an integer value or a NULL. After an INSERT statement the @@IDENTITY function will contain the latest value inserted into the identity field. It will contain NULL, If the insert/ select statement does not affect any records. one Key feature that needs to be noted is that the @@IDENTITY value does not revert to a previous value even if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back. However the Triggers fired doesn’t have an impact on the function. Failed statements and transactions can change the  identity for a table and create gaps in the identity column values.

to check these , Execute the function with out performing an insert in a new session.

SELECT  @@IDENTITY
Result will be "NULL"

 Now insert some value into a table containing an identity field. If you do not have one, the below script might be of use. 


CREATE TABLE [dbo].[TestTable](
[Ident] [int] IDENTITY(100,1) NOT NULL,
[ColUmn_1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

Insert the data by executing the statements below


INSERT INTO TESTTABLE ( COLUMN_1) VALUES ('HELLO');
SELECT  @@IDENTITY AS CURRENT_IDENTITY_VALUE
     , IDENT AS INSERTED_IDENTITY_VALUE
FROM
 TESTTABLE ;

Result:

Current_identity_value    Inserted_Identity_Value
100                       100

The Scope of the function is restricted to the current session. To get the identity value on a different server , we need to use functions / procedures.

Logging using SQL provider in SSIS 2008 and SSIS 2005

August 4, 2009 2 comments

I am starting this post with an assumption that you know to configure logging in SSIS 2005/2008 for SQL Server provider.

There are few differences in the way the information is logged into the table when using SSIS 2008 compared to SSIS 2005.

In 2005 the events are logged into the table by name “sysdtslog90” and in 2008 the events are logged into “sysssislog. By default in 2005 the table is created under the user tables folder, however in 2008 it is created under system tables. You can how ever create a table before the execution of packages  in user tables folder , this will restrict the table from getting created under system tables folder.

A stored proc is created along with the table. In ssis 2005 it is named as “sp_dts_addlogentry” . In 2008 it is named as sp_ssis_addlogentry” and is created under “system SPs ” by default.

You can add additional columns to the tables used for logging and update the values in these columns. This is usually done to customize the logging.

Return a custom delimited result set

June 11, 2009 Leave a comment

Developers very often require special character delimited query results, so that it can be used in other softwares and spread sheets. SQL server 2005 native tool provides a feature to accomplish this task without dropping any sweat. Follow the steps

  • Step 1: In the SSMS, select tools.
  • Step 2: click on options.
  • Step 3: Expand the query result node and Sql server node. Click on result to text.
  • Step 4: Select the column delimiter.
  • Step 5: click OK.

Note* Output format changes will be applied only on the new query window

From now, on executing the query you will get result as given above ( based on the delimiter selected by you )

Categories: Logging, SQL Server