SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

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.

Leave a 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.

Leave a comment »

Compound Assignment Operators in SQL Server 2008

Sql Server 2008: Compound Assignment Operation

Systems Engineering and RDBMS

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
select @i

———–
101

/**************************
Subtraction and assignment
***************************/
set @i -= 1
select @i

———–
100

/**************************
Multiplication and assignment
***************************/
set @i *= 2
select @i

———–
200

/**************************
Division and assignment
***************************/
set @i /= 2
select @i

———–
100

/**************************
Addition and assignment
***************************/
set @i %= 3
select @i

———–
1

/**************************
xor operation and assignment
***************************/
set @i ^= 2
select @i

———–
3

/**************************
Bitwise & operation and assignment
***************************/
set @i &= 2
select @i

———–
2

/**************************
Bitwise | operation and assignment
***************************/
set @i |= 2
select…

View original post 3 more words

Leave a comment »

SQL SERVER – Exporting Query Results to CSV using SQLCMD

Result to CSV file

Leave a comment »

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.

ErrorCount
As the name of the error file is dynamic, we use the EvaluateAsExpression property of the variable.
ErrorFilePathEvaluated
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.

package

Hope it Helps 🙂

 

Leave a comment »

Must Read: Improve productivity with SSMS

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

Leave a comment »

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.

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

Above dynamic query can be replaced by the following code.

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
Leave a comment »

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:

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

2. RUN In 64 Bit mode:

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

Interesting: fnArabToEnglish(convert from Arabian number to english)

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

Leave a comment »

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 Portal

Sometimes, its essential to use dynamic SQL when there are different queries for different scenarios, to build the query dynamically. The error we will discuss in this article is related to dynamic SQL and sp_describe_first_result_set , a new system stored procedure shipped with SQL Server 2012. This error message is available in SQL Server 2012 and above.

Let’s discuss this in detail:
Message Number: 11514

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ in procedure ‘%.*ls’  contains dynamic SQL.  Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Error Generation:
Let me create a sample to demonstrate this error.

errormsg11514.1.1

You can see that the above script executed successfully. Now I would like to view the metadata of the above stored procedure. Given below is the script to view the metadata using sp_describe_first_result_set

Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set…

View original post 114 more words

Leave a comment »