Excel File Connection Manager Connection String Problem

Today I encountered a following error on Sql Server 2012 while creating a basic SSIS package to load multiple Excel Files in database.

[Connection manager "Excel Connection Manager"] Error: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

I spent a couples of minutes to identify issue ,finally found solution:

Choose "Excel File Path" instead of "ConnectionString"while setting expression on Excel Connection Manager .


Hope this full help others who are facing the same problem.

Thanks,
Randhir

How to list Stored procedure parameters ?

One of the simple way to use sp_Help system stored procedure to get all parameters properties for a particular stored procedure but what if we have to list all stored procedures parameters using TSQL .
Here is a simple query :

Select OBJECT_NAME(SP.OBJECT_ID) AS ProcedureName,
PR.name AS Parameters, TY.name AS DataType, TY.max_length AS Length, TY.precision AS Precision
From sys.procedures SP
Inner Join sys.parameters PR On SP.object_id = PR.object_id
Inner Join sys.types TY On PR.user_type_id= TY.user_type_id
----Where OBJECT_NAME(SP.OBJECT_ID)  = ?

Cheer...

Correlated Subquery SQL Server

Correlated subquery is also known as repeating subquery where subquery depends on outer query of its values. Correlated subquery executes repeatedly once for each row that are selected by outer query.

e.g.
Select *
From Employees E
Where E.EmpID In (Select EmpID From EmployeeSalary Where EmpID = E.EmpID)

Database mail log using TSQL (Just Learned)

SYSMAIL_ALLITEMS is a view in MSDB database is used to check database mail log .
Contains one row for each message processed by Database Mail. This is really helpfull view to troubleshoot notifications .

Count String Occurrence SQL Server

Sometime we have to count the occurrence of any specific string like :

Declare @Input NVarchar(Max)
Set @Input = 'A92GPGCatherineGeorgeB2VZ9VCARMENNAVARROARAUZ'

Here we have to count the occurrence.
so we can get this by using simple script :

Select (LEN(@Input) - LEN(REPLACE(@Input, '', ''))) / LEN('')

Thanks...

Null value is eliminated by an aggregate or other SET operation

This warning comes usually while using aggregated function on column containing one or more NULL values.

To understand run following code :

Declare @R Int

Set @R = (Select SUM(N) From (Select NULL AS N UNION Select 1) aa)

---- OUTPUT
----- Warning: Null value is eliminated by an aggregate or other SET operation.
 
There are two solutions to get rid of this warning :
1. SET ANSI_WARNINGS OFF
Turn Off the warning using command.
 
2. ISNULL
Use ISNULL function on column.
e.g :
 
Declare @R Int

Set @R = (Select SUM(ISNULL(N,0)) From (Select NULL AS N UNION Select 1) aa)

--- OUTPUT
--- Command(s) completed successfully.

I like to use second method always , what you use ?
Note : Make sure to use the ISNULL function before using aggregate function otheriwe you will face the same warning again.

 

How to increase progarmmer productivity

http://www.blogya.in/how-to-increase-programmers-productivity