Collating my thoughts on collating (linked server collation issues)
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
SQL Transactions within SSIS. It’s like a prenuptial with your anonymous friend you met online.
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
Special Characters
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)
SSIS file name with time
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
batch-stamp current time and date
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….
Package Event Handler
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.
Single Quotes in Outlook to SQL. subtitle: how to go from curly to strait
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.
-
- curlystrait1
-
- curlystrait2
-
- curlystrait3
SSIS Variables Can be changed in Job Step
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
\Package.Variables[User::VARIABLE_NAME].Properties[Value]
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:
\Package.Variables[User::EmailErrorsTo].Properties[Value]
Value:
user@company.com
Prescedent Constraint Missing Prescedent Constraint
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.
32 bit Twice Shy
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.
