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.

[code language="sql"]
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
[/code]

Above dynamic query can be replaced by the following code.

[code language="sql"]
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
[/code]

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:

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

2. RUN In 64 Bit mode:

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

Interesting: fnArabToEnglish(convert from Arabian number to english)

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

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 - How to convert float to varchar

Sql Server Script: Create\Drop Primary Key

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

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."

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.

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/

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!!!

QUICKBLOG: Fixing - Can’t debug Script Task using 64bit SSIS…

Sql Server 2008: Careful with the Merge Hazards!

Do check the best approach to use Merge Statement in the end of the article http://www.sqlservercentral.com/articles/MERGE/97867/

Find Table Or Stored Procedure in all Databases on a Server

Inspired by the Pinal Dave's Post

Following is the SP to find the Stored Procedure or Table in all databases on a given server.

[code language="sql"]
CREATE PROCEDURE usp_FindTableOrStoredProcNameInAllDatabase
@Name VARCHAR(256)
AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR
SET @getDBName = CURSOR FOR
SELECT QUOTENAME([name])
FROM sys.databases
CREATE TABLE #TmpTable (
DBName VARCHAR(256),
SchemaName VARCHAR(256),
StoredProcTableName VARCHAR(256),
SpOrTable VARCHAR(10)
)
OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = 'USE ' + @DBName + ';
INSERT INTO #TmpTable
SELECT '''+ @DBName + ''' AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS StoredProcTableName,
''Table'' AS SpOrTable
FROM sys.tables
WHERE name LIKE ''%' + @Name + '%'''
EXEC (@varSQL)

SET @varSQL = 'USE ' + @DBName + ';
INSERT INTO #TmpTable
SELECT '''+ @DBName + ''' AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS StoredProcTableName,
''SP'' AS SpOrTable
FROM sys.procedures
WHERE name LIKE ''%' + @Name + '%'''
EXEC (@varSQL)

FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *
FROM #TmpTable Order By SpOrTable
DROP TABLE #TmpTable
GO
EXEC usp_FindTableOrStoredProcNameInAllDatabase 'Address'
GO
[/code]

following is how the out put would look like:

SQL SERVER - Find Table in Every Database of SQL Server

Super!!!

Cross Apply vs Outer Apply

CROSS APPLY is equivalent to an INNER JOIN expression and OUTER APPLY is equivalent to a LEFT OUTER JOIN expression.

http://sqlwithmanoj.wordpress.com/2010/12/11/cross-apply-outer-apply/

Sql Server: Get Random Time in given range : Function

[code language="sql"]
--Requirement to get a random time of day between @startTime and @endTime
Create Function dbo.GetRandomTimeInRange
(
@startTime Time = '00:00:00',
@endTime TIME = '01:30:00'
)
-- Select dbo.GetRandomTimeInRange ('23:00:00', '02:01:00')
RETURNS Time
BEGIN

-- Get the number of seconds between the specified time
-- (eg. there are 5400 seconds between 12 AM and 1:30 AM)
DECLARE @MaxSec int = DATEDIFF(ss, @startTime, @endTime)
-- Get a random number of seconds between 0 and the number of
-- seconds between @startTime and @endTime (@maxSeconds)
DECLARE @randomSec int = (@MaxSec + 1) *
(DATEPART(Ms, Getdate())/1000.0)
/* Add the random number of seconds to @startTime and
return that random time of day */
/* In the above code Milli Seconds is divided by 1000 to make the
value < 1 or in other words extreact a few percent of @randomSec
so as to get the value less than @endTime
*/
RETURN (convert(Time, DateAdd(second, @randomSec, @startTime)))

END
GO

[/code]

Get Random Time in a given range: Sql Server

[code language="sql"]
DECLARE @startTime Time = '01:00:00'
DECLARE @endTime TIME = '02:30:00'
-- Get the number of seconds between these two times
-- (eg. there are 5400 seconds between 1 AM and 2.30 AM)
DECLARE @maxSeconds int = DATEDIFF(ss, @startTime, @endTime)
-- Get a random number of seconds between 0 and the number of
-- seconds between @startTime and @endTime (@maxSeconds)
DECLARE @randomSeconds int = (@maxSeconds + 1)
* RAND(convert(varbinary, newId() ))
-- Add the random number of seconds to @startTime and return that random time of day
SELECT (convert(Time, DateAdd(second, @randomSeconds, @startTime)))
AS RandomTime

--Note: Above code cannot be used inside the function as it would give following errors
--Invalid use of a side-effecting operator 'newid' within a function.
--Invalid use of a side-effecting operator 'rand' within a function.

[/code]