Sql Server: Alter Table in Design

Many a time while making changes to the definition of the Sql Server Table we get the following message:
"Saving not permitted. The changes you have made require the following tables to be dropped and re-created or enable option Prevent saving changes that require the table to be re-created. "



To disable the option "Prevent saving changes that require the table to be re-created"
Go to Tools > Options > Designers > "Un-check" "Prevent saving changes that require the table to be re-created"

SSIS Task: For loop Vs For Each Loop

For Loop

For Loop is used for looping through a number of tasks for a set number of times.

Simply:->  initialize, verify condition, increment\assign

For Each

ForEach Loop loops through various things such as files, objects, ADO connections, etc.

Above screen shot show we have task configured for “Foreach file enumerator” which can be used to move\copy\delete file (of given file) in a given folder

Enumerator configuration:

Folder: Specify the folder path from where we need to process the files

Flies: File name (format) \ file extension

• 
  

  Fully qualified: File name along with location is returned .Eg:  C:\Example.txt

  
  


• 
  

  Name and extension: The file name with its extension is returned.Eg: Example.txt

  
  


• 
  

  Name only: The file name without its extension is returned.Eg: Example

  
  

Variable Mapping

Result of the for loop will be assigned to variable and its value can be used further.

• 
  

  Foreach File Enumerator: Enumerates files in a folder

  
  


• 
  

  Foreach Item Enumeration: Enumerates items in a collection, such as the executables specified in an Execute Process task.

  
  


• 
  

  Foreach ADO Enumerator: Enumerates rows in a table, such as the rows in an ADO recordset.

  
  


• 
  

  Foreach ADO.NET Schema Rowset Enumerator: Enumerates schema information about a data source.

  
  


• 
  

  Foreach From Variable Enumerator: Enumerates a list of objects in a variable, such as an array or ADO.NET DataTable.

  
  


• 
  

  Foreach NodeList Enumeration: Enumerates the result set of an XML Path Language (XPath) expression.

  
  


• 
  

  Foreach SMO Enumerator: Enumerates a list of SQL Server Management Objects (SMO) objects, such as a list of views in a database.

  
  

SSIS: Derived column : Conditional Operator + Hardcoding Null

Using Conditional Operator In SSIS:

«boolean_expression» ? «when_true» : «when_false»

To hardcode Null : NULL(DT_WSTR, 50)

Following is the expression to get the sub string and compare to "N" and return NULL if true

else the sub string string itself.

(DT_I4)((SUBSTRING(LTRIM(Answer),1,1) == "N") ? NULL(DT_WSTR, 50) : SUBSTRING(LTRIM(Answer),1,1))

SQL SERVER - Fragmentation - Detect Fragmentation and Eliminate Fragmentation

Fragmentation - De-Fragmentation.. Must Read..

SqlCmd : Error Cannot generate SSPI co ntext

Got the following error while trying to connect to SqlCmd of local machine.

Error Cannot generate SSPI co ntext

Searching online for the solution figured out that it has to do something with the network.

Workaround: Disable the network and try running the same command it works like charm.

Looking for the exact issue and better solution. Feel free to comment.

Sql Server: Using Sqlcmd to export data to text file

A SqlServerCentral friend was looking for a option to export data from Sql Server to a text file on periodic option. Refer the link below for details:

http://www.sqlservercentral.com/Forums/Topic1452185-1550-1.aspx
Adviced him to run the following sqlcmd in the job..
SQLCMD -S YourSqlServer -d YourDatabase -U YourUserName -P YourPassword -Q "Your Query" -s "," -o "C:\YourFileName.txt"
Feel free to post better work around.
I understand SSIS could be another option.

Compound Assignment Operators in SQL Server 2008

Sql Server 2008: Compound Assignment Operation

SQL SERVER - Exporting Query Results to CSV using SQLCMD

Result to CSV file

SSIS: Delete Empty Error Log Files With Dynamic Names

Many a times we have a scenario when we have to read from a file write the dirty row into a text file.
In my case, I had to create the text file for each run hence I created a file with dynamic name.
SSIS would create the file even when there is no error.
Now we need to delete the file in case the file is empty i.e. there are no error rows.

As we all know rowcount task come in hand to calculate the no. of records which error out.


As the name of the error file is dynamic, we use the EvaluateAsExpression property of the variable.

As you can see in the screen short above we have a variable name
DataErrorFilePathEvaluated and the property EvaluateAsExpression is set to true. The expression value is set to append datetime to it.

The variable DataErrorFilePathEvaluated is also used as the ConnectionString of the output error file and same is used as the ConnectionString for the file system task to delete the file in case no records error out.



Hope it Helps :)

 

Must Read: Improve productivity with SSMS

http://www.sqlservercentral.com/blogs/db-newsfeed/2013/04/30/five-ways-to-improve-your-productivity-with-ssms/

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]

Sql Server 2008,2012: Run Sql Script File From Command Line

To Start Sql Command reform following link:
http://msdn.microsoft.com/en-us/library/ms166559(v=sql.105).aspx

Run the following command in the command prompt.

Replace "C:\myScript.sql" with the location of your sql code file location.

[code language="sql"] sqlcmd -S myServer\instanceName -i C:\myScript.sql[/code]

The above command will return the result set in command prompt.

If you prefer the result in a text file use the code below:

[code language="sql"] sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\EmpAdds.txt[/code]

Happy Coding :)

Encryption\Decription in Sql Server 2005

• ENCRYPTION by passphrase


• ENCRYPTION by symmetric keys


• ENCRYPTION by Asymmetric keys


• ENCRYPTION by certificates

http://www.databasejournal.com/features/mssql/article.php/3714031/SQL-Server-2005-Encryption-types.htm

http://dotnetslackers.com/articles/sql/IntroductionToSQLServerEncryptionAndSymmetricKeyEncryptionTutorial.aspx

Must Read: Full Text Catalog, Full Text Index, Noise Words or Stop Words

https://www.simple-talk.com/content/article.aspx?article=621
http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

 

Set showplan_text No: To view the execution plan in text format

http://www.sqlservercurry.com/2008/03/how-to-quickly-analyze-slow-running.html

For further drill down:

http://blogs.msdn.com/b/craigfr/archive/2006/06/13/629615.aspx

SQL SERVER - DBCC CHECKDB Introduction and Explanation - DBCC CHECKDB Errors Solution

Group By vs Distinct : Performance Comparision

SQL SERVER - Stored Procedure - Clean Cache and Clean Buffer

Respect!

SSIS: Awesome post by Matt Masson

http://www.mattmasson.com/2013/03/slides-from-ssis-performance-design-patterns-techdays-hong-kong-2013/

SSIS: Remove duplicate rows from input file

We have an requirement in which there are 40,000,000 (forty million) records in the Input file. We have to load it into the databases after implementing a few business logic transformations. Major part of concern is to remove duplicate rows. Following are the possible solutions.

1. Use Sort task and set "remove duplicate" property to TRUE. Would take quite long as it is a full blocking transaction. 

2. Use Script component. Need to compare. I guess it would again be a full blocking transformation. 

3.Dump data to DB and then reload after selecting distinct records. Looks like the best option. 

Will be back with the stats. Feel free to add your suggestions/stats/comments.

Happy SSISing.. :)

Pinal Dave Fix:SQL SERVER – Fix – Error: 1060 The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer

http://blog.sqlauthority.com/2013/03/22/sql-server-fix-error-1060-the-number-of-rows-provided-for-a-top-or-fetch-clauses-row-count-parameter-must-be-an-integer/

What’s Inside an SSIS 2012 .ispac File?

voila..

SSIS Naming Conventions and Best Practices

http://consultingblogs.emc.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Suggested-Best-Practices-and-naming-conventions.aspx

10 THINGS ABOUT EXPRESSIONS: A Gem of Post

http://www.jasonstrate.com/2011/01/31-days-of-ssis-10-things-about-expressions-1631/

Pivot In Sql Server Simplified

comin soon

Sql Server : Why Use Collation ?

Why Use collation? Brilliant Explanation here:

http://www.dotnetfunda.com/forums/thread3599-what-is-the-use-of-collate-in-sql-server.aspx

for further drill down.. refer:

http://www.sqlserverclub.com/articles/understanding-sql-server-collation-sequences.aspx

Executing SSIS Package via command prompt using multiple config files

Normally we have only one config file and this is how we execute the package via command prompt::

[code]/FILE "F:PackageFolder\Package.dtsx" /CONFIGFILE "F:\ConfigFileLocation\FirstPackageConfigFile.dtsConfig" /CHECKPOINTING OFF  /REPORTING EW [/code]

In case we have multiple config files this is how the command goes:

[code]/FILE "F:PackageFolder\Package.dtsx" /CONFIGFILE "F:\ConfigFileLocation\PackageConfigFile.dtsConfig" /CONFIGFILE  "F:\ConfigFileLocation\SecondPackageConfigFile.dtsConfig" /CHECKPOINTING OFF  /REPORTING EW [/code]

Hope it helps! :)

Interesting: SSIS - Handling File with multiple number of colums

http://agilebi.com/jwelch/2007/05/08/handling-flat-files-with-varying-numbers-of-columns/

SSIS Error Handling

http://agilebi.com/jwelch/2007/05/05/handling-multiple-errors-in-ssis/

http://beyondrelational.com/modules/12/tutorials/24/tutorials/9686/getting-started-with-ssis-part-10-event-handling-and-logging

http://hardik-bhavsar.blogspot.in/2011/08/auditing-and-error-handling-in-ssis.html

Quick Tips

1. GUID Using SQL Server

You can generate a GUID using SQL Server using the following T-SQL statement:

[code] SELECT newid()[/code]

This statement generates a new GUID and displays it in the output window.

2. Get current time after removing special characters

[code]

SELECT REPLACE(CONVERT(VARCHAR, GETDATE(), 14),':','')

[/code]

Above code will return '221320687' for date '2013-03-19 22:13:20.687'

3. Converting strings to dates in derived column component: SSIS

Convert from yyyymmdd to date data type

4. Cmd Prompt : Shortcuts: ssms, sqlwb - did they confused you yet?

Check following link to clerify:

http://www.silverlighthack.com/post/2008/08/02/sql-server-2008-quick-fact-new-ssms-shortcut.aspx

[code]
(DT_DBTIMESTAMP)(SUBSTRING([date string], 1, 4) + "-" SUBSTRING([date string], 5, 2) + "-" + SUBSTRING([date string], 7, 2))
[/code]

4. difference between look up and merge join -- SSIS

http://consultingblogs.emc.com/jamiethomson/archive/2005/10/21/SSIS_3A00_-A-case-study-of-using-LOOKUPs-instead-of-MERGE-JOINs.aspx

http://beyondrelational.com/modules/2/blogs/106/Posts/14438/ssis-playing-around-with-lookup-and-merge-join.aspx

 

Quick Tips: generate GUID in Sql Server

GUID Using SQL Server

You can generate a GUID using SQL Server. All you need to do is open SQL Server Query Analyzer and execute following T-SQL statement:

 SELECT newid()

This statement generates a new GUID and displays it in the output window.

Top with ties and without ties

Top clause with ties

[code language="sql"]--with ties
Select top(4) with ties * from test.dbo.emp order by salary[/code]

- Query will generate rows including additional rows that have the same value based on the SORT column \columns as the last returned rows

- Can only be used in SELECT Statement and if and only if there is an ORDER BY clause specified.

Power of CTE(Common table expression) : Sql Server

http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example

http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

 

Temp table vs Table variable: Sql Server

http://www.codeproject.com/Articles/415184/Table-Variable-V-S-Temporary-Table

major difference:

Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction.

Temp tables behave same as normal tables and are bound by transactions.

Important:

• Both table variables and temp tables are stored in tempdb. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation; temp tables and table variables will by default inherit the collation of the server, causing problems if you want to compare data in them with data in your database.

• Any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

• Table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement.

http://sqlserverplanet.com/tsql/yet-another-temp-tables-vs-table-variables-article

Date functions: Sql Server- DATEPART() , DATEADD() , DATEDIFF() , DATENAME() , DAY() , MONTH() ,YEAR()

V Useful sql date functions:

1. DATEPART()
2. DATEADD()
3. DATEDIFF()
4. DATENAME()
5. DAY()
6. MONTH()
7. YEAR()

http://shawpnendu.blogspot.in/2009/05/datepart-dateadd-datediff-sql-server.html

http://www.mssqltips.com/sqlservertip/2507/determine-sql-server-date-and-time-with-datepart-and-datename-functions/

@@Identity, Scope_Identity() , Ident_Current('table_name') : Get last inserted record in a table - Sql Server

Select @@Identity

Gets the identity value entered into to the table in your current session.Scope of @@identity is not limited. If there is a trigger which would create an entry in another table, you will get the identity created in the last.

in other words:

It returns the last identity value generated for any table in the current session, across all scopes.

Select Scope_Identity()

It returns the last identity value generated for any table in the current session and the current scope.

Hence, if we consider the example given above the scope_identity would give the identity value generated by explicit insertion rather than the trigger.

Select Ident_Current('table_name') 

It returns the last identity value generated for a specific table in any session and any scope.

Scope_Identity equivalent for GUID:

http://stackoverflow.com/questions/1509947/sql-server-scope-identity-for-guids