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 SO.id = SC.id
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id
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

SELECT COUNT(*)
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, http://www.source-code.biz
create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256)
   with schemabinding
begin
   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
      end
   if len(@s2) = 0
      return null
   return @s2
   end

Thank you Christian for this tremendous help!  (script from http://www.source-code.biz/)

(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

@ECHO OFF
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: =%
@ECHO ON

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

Assistance received from….

http://www.robvanderwoude.com/datetimentexamples.php

http://www.dostips.com/DtTipsStringManipulation.php

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. 

 

constraintonParent

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.

WHAT?

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.

http://office.microsoft.com/en-us/word-help/change-curly-quotes-to-straight-quotes-and-vice-versa-HA010173242.aspx

Written by matt

March 15, 2012 at 1:07 pm

Posted in Sql Server