Tag: T-SQL

Get column length using Col_Length – SQL Server


Col_Length() : This function returns the actual size of the table column in bytes. It gives us a small integer value as function output. This function returns the actual storage bytes for that column. For instance, if the column type is nvarchar(30) then it returns 60 else if smallint then it returns 2 else if varchar(max) then it returns -1. It also returns null if we do have permission to access that object or if we pass invalid argument string.

Function Syntax

COL_LENGTH ( ‘table’ , ‘column’ )

Example

Executing this function using Adventurework database objects

pic1

Simple way to execute T-SQL Statement using SSIS – SQL Server


In the database environment there are lots of stored procedures being created to perform some development task.

In this article I will be showing you how to use Execute T-SQL Statement Task to execute a T-SQL Statement. There are so many task properties to customize this task for our need. For instance, if the T-SQL returns value, it may be single row, rowset, etc… that can be configured using ResultSetType property.

Implementation

Step 1: Create a package and add Execute T-SQL Statement task from tool box

Step 2: Edit the task and create connection to the database instance

Step 3: you write your T-SQL Statement directly in the textbox or by variable or file. See the SqlStatementSourceType property. In this example i have used direct input.

Step 4: Click view T-SQL button to see the code that will be sent to SQL Server for execution

Step 5: click OK now. Though the task does not show the error icon on this task we have not completed our configuration. So, Open the connection from the connection manager and select the database in which you would execute the T-SQL.

Step 6: click OK and execute the package