Posts

Showing posts from 2014

CTE - Limitations

Hi Wanted to explore one of the major limitation of CTE in sql 2012 i.e. it cannot be used inside if else statement . This is something basic as the definition of cte that it must be followed by a select, insert, update or delete statement. That is we cannot have following condition:- with cte ( ) if select cte else select cte end Thanks

Bug In ado.net enumerator in foreach loop in SSIS 2012

Hi Greetings to everyone, Today I am going to report a bug in SSIS 2012. In one of the packages I was using execute sql task and then using ado.net foreach loop enumerator. My execute sql task had a simple select statement like - 'select c1,c2,c3 from table' where c1,c2,c3 were integer columns. Based on the rows returned I was running foreach loop container and assigning values from the columns to the variables of datatype Int32 inside SSIS package. However on executing the package it was returning an error message such as Error: The type of the value (Int32) being assigned to variable "User::result1" differs from the current variable type (Int64). Variables may not change type during execution. Variable types are strict, except for variables of type Object. While researching I found that this is a bug in ssis - http://connect.microsoft.com/SQLServer/feedback/details/732413/foreach-ado-enumerator-returns-error-when-bigint-and-int64-are-used Solution -

PROVIDING LOGGING INFORMATION AT COMMAND LINE

For providing logging information at command line utility use the option /l for example dtexe /f "some path" /l "logging type; connection string" refer to this useful link : - http://technet.microsoft.com/en-us/library/ms162810(v=sql.105).aspx Note : - connection string actually refers to the connection manager name or file path whatever you are using in your package.

ERROR OPENING SSRS REPORT MANAGER IN INTERNET EXPLORER

Writing this post only for internet explorer as not aware of the settings of other browsers such as chrome/mozilla. To be honest I have to dwell down and read so many websites before I was finally able to solve it on my own. Here are steps: 1. Open the internet explorer with 'run as admin' option. 2. Click on internet options on top right corner, click on security tab, click on local intranet tab, click on sites, select the automatically detect intranet network option 3. Go back to security tab, click on custom level, scroll down to last option User Authentication and select automatic logon with current username and password. After applying above settings restart the internet explorer and open your report browser by typing - http://localserver/reports. Hope this helps. Thanks
PROBLEM - ERROR EXECUTING SSIS PACKAGE FROM SQL JOB. Error Message would be something like this - failed to load xml file... SOLUTION - Make sure that your integration services and sql agent services are running under same account, you can check it by typing services.msc at run window and checking for logon options of these 2 services