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