Let me start today's blog post with a direction question. How many of you have ever used Template Browser? Template Browser is a very important and useful feature of SQL Server Management Studio (SSMS). Every time when I am talking about SQL Server there is always someone comes up with the question, why there is no step by step procedure included in SSMS for features.
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”
For Loop is used for looping through a number of tasks for a set number of times.
Simply:-> initialize, verify condition, increment\assign
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
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
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.
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))
Q. What is Fragmentation? How to detect fragmentation and how to eliminate it?
A. Storing data non-contiguously on disk is known as fragmentation. Before learning to eliminate fragmentation, you should have a clear understanding of the types of fragmentation. We can classify fragmentation into two types:
- Internal Fragmentation: When records are stored non-contiguously inside the page, then it is called internal fragmentation.
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.
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:
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.
Continuing in the series on the new features in SQL Server 2008, here is another small little tid-bit. SQL Server 2008 (tested in CTP5) supports the compound assignment operators. Here is a working example of those operators:
declare @i int
set @i = 100
Addition and assignment
set @i += 1
Subtraction and assignment…
Social media is evolving at a rapid pace and every day I keep on getting question from different methods. Here is the latest question which I received on my Facebook page. The question was how to export the data of query into CSV using SQLCMD.
This is indeed very easy process and very simple command to export any query data. For example we will use AdventureWorks2012 database.
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