SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Avoid Dynamic Query

Avoid Dynamic queries, Use the alternate mentioned below:
Here the business logic is that the parameter @Param
Can be ‘ALL’ or a given value. In case of All we don’t need to filter on ColumnName where as otherwise we have to filter out based on parameter.

DECLARE @Query VARCHAR(MAX), @Param VARCHAR(10)
SET @Param = 'ALL'
SELECT @Query = 'SELECT * FROM tblName '
IF  @Param <>'ALL'
BEGIN
SELECT @Query = @Query + 'WHERE ColumnName = ' + @Param
END
EXEC @Param

Above dynamic query can be replaced by the following code.

DECLARE @Query VARCHAR(MAX), @Param VARCHAR(10)
SET @Param = 'ALL'
SELECT * FROM tblName
WHERE (@Param = CASE WHEN @Param = 'ALL' THEN @Param
ELSE ColumnName END) -- All\Specific
Advertisements
Leave a comment »

Run Package in 32 Bit\ 64 Bit Mode from Command Prompt

This is an extension to my earlier post

On a 64 bit OS, the default path to the 64 bit would be "c:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"

Still on 64 bit OS, the path to the 32 version would be "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"

Following is the code to run an SSIS package from command prompt:

1. RUN In 32 Bit mode:

"c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /file C:\PackageFolder\Package.dtsx /CONF C:\ConfigFile.dtsConfig

2. RUN In 64 Bit mode:

"c:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /file C:\PackageFolder\Package.dtsx /CONF C:\ConfigFile.dtsConfig
1 Comment »

Interesting: fnArabToEnglish(convert from Arabian number to english)

http://www.sqlservercentral.com/scripts/Arabian+number+to+English/30454/

Leave a comment »

SQL SERVER 2012 – Fix – Error :11514 – The metadata could not be determined because statement in procedure contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

SQL Server Portal

Sometimes, its essential to use dynamic SQL when there are different queries for different scenarios, to build the query dynamically. The error we will discuss in this article is related to dynamic SQL and sp_describe_first_result_set , a new system stored procedure shipped with SQL Server 2012. This error message is available in SQL Server 2012 and above.

Let’s discuss this in detail:
Message Number: 11514

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ in procedure ‘%.*ls’  contains dynamic SQL.  Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Error Generation:
Let me create a sample to demonstrate this error.

errormsg11514.1.1

You can see that the above script executed successfully. Now I would like to view the metadata of the above stored procedure. Given below is the script to view the metadata using sp_describe_first_result_set

Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set…

View original post 114 more words

Leave a comment »

SQL SERVER – How to convert float to varchar

SQL Server Portal

Sometimes, you feel very handicapped when you need to convert some complex data types but due to compatibility, the built-in conversion functions such as Cast, Convert, Try_Cast, Try_Convert cannot aid conversion.

How to convert float to varchar is indeed an old topic. In this article, we will solve this issue via STR function and FORMAT function introduced in SQL Server 2012.

Given below are the two methods to convert float to varchar.

Method 1:
In this method, we will use STRfunction to convert float to varchar and thereafter LTRIMto trim the extra spaces left in it.

Convertfloortovarchar1.1

Method 2:
In the above method, you can notice that we used two different methods (STR, LTRIM) to convert float to varchar and to trim extra spaces. In this method we will use ONLY FORMATfunction to convert float to varchar.

convertfloattovarchar1.2

View original post

Leave a comment »

Sql Server Script: Create\Drop Primary Key

http://social.technet.microsoft.com/wiki/contents/articles/script-to-create-or-drop-all-primary-keys.aspx

Leave a comment »

Back To Basic: Nested Transaction

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/

Point to remember:

ROLLBACK will do two things that’s important to be aware of…
1) It will rollback all work back to the *first* or *outermost* BEGIN TRAN
2) It will also *reset* @@TRANCOUNT to zero (no matter the previous value)

Recall getting the error below when you forget to use @@Transcount>0 before rollback 

“Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.”

Leave a comment »

SSIS Script task: FileNotFoundException: Could not load file or assembly While refering to third party DLL

While referring to a third party DLL in the SSIS Script task we encountered following error:

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target
of an invocation. —> System.IO.FileNotFoundException: Could not load file or assembly
‘xyzzz, Version=5.3.0.26835, Culture=neutral, PublicKeyToken=5b5f377bc08a4d32’ or one
of its dependencies. The system cannot find the file specified.
File name: ‘xyzzz, Version=5.3.0.26835, Culture=neutral,
PublicKeyToken=5b5f377bc08a4d32’
at ST_d83cb23ff54e47979cb61222ad9d6b85.csproj.ScriptMain.CreatePdf(String pstrFileName,
String pstrHtml)
at ST_d83cb23ff54e47979cb61222ad9d6b85.csproj.ScriptMain.GeneratePdf(DataSet pobjDs)
at ST_d83cb23ff54e47979cb61222ad9d6b85.csproj.ScriptMain.Main()

Solution: Both the solutions specified work fine. Either of them can be refereed.

Solution 1: Place the xyzzz.DLL to location “C:\Windows\assembly” (this is where the GAC
will place all the files). The file gets INSTALLED there and you need to UNINSTALL to remove it.

Solution 2 (for 32 bit processor): Place the xyzzz.DLL to location “C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn

Solution 2 (for 64 bit processor): Place the xyzzz.DLL to location “c:\Program Files\Microsoft SQL Server\100\DTS\Binn\”

Hope it Helps 🙂

Feel free to suggest a better option.

11 Comments »

Sql Server – Alter Column Insight

Nice posts by Vishal

http://www.sqlservercentral.com/blogs/sqlandme/2013/04/01/sql-server-saving-changes-not-permitted-in-management-studio/

http://sqlandme.com/2011/08/04/sql-server-alter-column-management-studio-v-t-sql/

Leave a comment »

Preparing for Microsoft Sql Server Certification: MCSA\MCSE Certification

Check this article it surely gona help 🙂

http://www.sqlservercentral.com/blogs/koen-verbeeck/2013/04/04/how-i-prepared-myself-for-the-mcse-certification/

Cheers!!!

Leave a comment »