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