Developing Matt

My Technical Journal

Collating my thoughts on collating (linked server collation issues)

leave a comment »

It’s nice to be able to query one table in one server and join that against another table on another server.  It’s nice until you realize that the collation settings are different.

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_BIN” in the equal to operation.

To resolve you will want to know the collation of each of the columns you are joining.  Most of the time it will be at the database level, but it’s worth checking anyway.

SELECT SO.NAME AS “Table Name”, SC.NAME AS “Column Name”, SC.collation AS “Collation”
FROM dbo.sysobjects SO
INNER JOIN dbo.syscolumns SC ON =
LEFT JOIN dbo.syscomments SM ON SC.cdefault =
WHERE SO.xtype = ‘U’
and SO.NAME = ‘TableName’
ORDER BY SO.[name], SC.colid

Mine turned out to be SQL_Latin1_General_CP1_CI_AS vs. Latin1_General_BIN

So I added a collation setting at the end of the columns I referenced to force a collation to help the join actually work

FROM linked_server.database.table.dbo U
JOIN linked_server2.database.table.dbo P ON
P.col1 = U.col1 COLLATE SQL_Latin1_General_CP1_CI_AS



Written by matches

March 24, 2020 at 3:42 pm

Posted in Sql Server

SQL Transactions within SSIS. It’s like a prenuptial with your anonymous friend you met online.

leave a comment »

In ssis you can set up an object (container, loop, etc) to be a transaction….you know.  I mean a sql transaction, which is really helpful when you want to be a responsible member of your organization.  So I was testing it out to see how it worked. It’s pretty simple and an easy way to make your objects perform together.

So on the container object you would set the Transaction Option to Required

And all the objects within the transaction you would set the Transaction Option to Supported (default)

The problem with this new approach is that it requires MSDTC ( or DTC to the cool kids in the know) to be turned on within the SQL Server.  This enables the server to establish a transaction that crosses over multiple databases.  Your DB kinfolk might not want this as they might want to sleep an extra 10 minutes at night.

You can also do this by just creating a sql task with a simple ‘BEGIN TRANSACTION;’ statement.

And then run your container object, followed by a ‘good’ path and an ‘evil’ path.

The good has a ‘COMMIT;’ The evil has a ‘ROLLBACK;’

You do have to set the connection property to the database connection to retain same connection


Written by matt

January 31, 2020 at 9:43 am

Posted in Sql Server, SSIS

Tagged with , ,

Special Characters

leave a comment »

It might be hard to work with special characters in your data.  For instance, doing a replace on a field that looks like it has a blank (but is actually is carriage return) won’t yield the results you are seeking.  Instead, replace your special characters using the char function:

char(9) : tab

char(10): line feed

char(13) carriage return

If you want to find the others use this query:

SELECT ASCII( SUBSTRING(column_char,PATINDEX(‘%[^A-Z]%’,column_char),50))
,ASCII( SUBSTRING(column_char,PATINDEX(‘%[^A-Z]%’,column_char)+1,50))
FROM @tbl

Or if you are looking for a function I found this better approach from here by Christian d’Heureuse:

— Removes special characters from a string value.
— All characters except 0-9, a-z and A-Z are removed and
— the remaining characters are returned.
— Author: Christian d’Heureuse,
create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256)
   with schemabinding
   if @s is null
      return null
   declare @s2 varchar(256)
   set @s2 = ”
   declare @l int
   set @l = len(@s)
   declare @p int
   set @p = 1
   while @p <= @l begin
      declare @c int
      set @c = ascii(substring(@s, @p, 1))
      if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122
         set @s2 = @s2 + char(@c)
      set @p = @p + 1
   if len(@s2) = 0
      return null
   return @s2

Thank you Christian for this tremendous help!  (script from

(I only copy the script here in case Christian decides to take down his website as this has happened to me in the past)

Written by matt

October 1, 2012 at 3:09 am

Posted in T-Sql

SSIS file name with time

leave a comment »

There is often a need to add a time stamp on your file.  On your file connection (in connection manager section of ssis) pull up the properties for your file (F4 or right click…properties).  Expand expressions and add in a new expression of ‘Connection String.”  From there you should be able to get to your expression builder.  This uses regular expressions. 

From there add your file location, file name and then you can add the parts of your dates with

(DT_WSTR,20) DATEPART("Hh", GETDATE())  +"_" +  (DT_WSTR,20)DATEPART("n", GETDATE()) +"_" +  (DT_WSTR,20)DATEPART("s", GETDATE()) +".txt"


DT_WSTR is the casting function and DATEPART pulls out the part of your date.

Result: 8_23_31.txt

Written by matt

August 17, 2012 at 7:24 am

Posted in Uncategorized

batch-stamp current time and date

leave a comment »

Alas, I’ve broken my rule to not use batch. 

The way I create a file stamping the current date and time is as follows

FOR /F "tokens=*" %%A IN (‘TIME/T’) DO SET Now=%%A
set strFile=%date:~10,4%%date:~4,2%%date:~7,2%_%Now%
set strFile=%strFile%
set strFile=%strFile::=%
set strFile=%strFile: =%

dir >>c:\%strFile%_CUSTOMgetfromsvn.txt

Assistance received from….

Written by matt

August 14, 2012 at 12:24 pm

Posted in Batch

Package Event Handler

leave a comment »

A package level event handler is not just an event handler for the package. It is also an event raised for each executable inside the package.  Restated: if you place an event handler and place it at the top node (at the package level) this will occur for your package and every executable inside of it.

The problem: I thought that I could throw an insert to a table to discover when my package completed. To do this …at the package level……I set the ‘on post execute’ handler to include an insert statement with the end time. However, it inserted twice (when there was only one object). Once for the object, once for the package.

The solution: I created a constraint (from an empty container) with a test on the variable System::SourceParentGuid. When it is empty, I know that it is not coming from a parent executable, and from the package only.  The constraint was @SourceParentGUID==""     Now my insert will only execute on post execute when there isn’t a source parent GUID. 



Written by matt

August 10, 2012 at 8:19 am

Posted in SSIS

Tagged with

Single Quotes in Outlook to SQL. subtitle: how to go from curly to strait

leave a comment »

I’ve long wished that the single quotes in outlook and word would easily translate to SSMS. On my blog, in my emails, in word, on my bathroom wall, all over the place, you can find the single quote that doesn’t work in the query window. Copy and paste and replace the quotes with quotes. I’m not sure who had the bright idea to make single quotes fancy. Possibly someone with a little more time on their hands. They probably had a mustache.

Regardless, all my whining took a nose dive when I discovered that there is actually an option to not use those fancy curly single quotes.


Yes. Deep in the cavities of options you can find a way to keep outlook, word, and others from replacing the normal single quotes to fancy ones.

Written by matt

March 15, 2012 at 1:07 pm

Posted in Sql Server

SSIS Variables Can be changed in Job Step

leave a comment »

I wanted to make note that if you want to set values to variables one way to do this is via the sql job. You do this in the Set Values Tab (job properties, step properties, set values tab).

Set the property path to your custom VARIABLE_NAME

Set the value to your custom value. The advantage of doing this, of course, is being able to dynamically change a value instead of changing the source. For example, I like to set up email messaging here instead of hard-coding inside the application:
Property Path:

Written by matt

February 21, 2012 at 4:15 pm

Posted in SSIS

Prescedent Constraint Missing Prescedent Constraint

leave a comment »

For some reason my expression on my prescedent constraint wasn’t passing successfully. Restated: I have a constraint on a data flow item to test an expression. I have it testing a string value that I set in the script editor. If it is not empty I send an email. The expression I was using (@[User::EmailRowErrors] !=”” or @EmailRowErrors !=”” Either one was working ) .

For some reason it stopped working. Worked before. Doesn’t work now. After some time I learned that the expression editor cannot handle > 4k characters, so it was just bombing because my text was simply too long. I never received an error of any kind, fyi.

The end result is that I created another boolean variable that I also set in the script editor and I test for it and now it works like a champ. (@[User::SendEmail] ==true) Thanks for reading.

Written by matt

February 21, 2012 at 3:34 pm

Posted in SSIS

32 bit Twice Shy

leave a comment »


If you sql server is running 64 bit and your ssis is using something other than a 64 bit provider you might receive an error with your job. 

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.  The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered — perhaps no 64-bit provider is available Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".  End Error  Error: 2012-01-18 11:58:16.53     Code: 0xC020801C     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. 

Change the step on your job (the one executing your ssis package) to Use 32 bit runtime (Execution options tab).  Jet is not 64 bit, therefore you have to change your step to run it as a 32 bit application.


Written by matt

January 18, 2012 at 5:37 pm

Posted in SSIS