Query Timeout Expired

May 2, 2011

I was getting “Query timeout expired” errors in the code for doing an import from a back-end ERP into SalesLogix.

So I logged into the database server, right-clicked on the connection, Properties, and then opened the Connections items on the Server Properties form, and changed the Remote query timeout from its default of 600 seconds to 0, to disable that.

Re-ran the code … but it was still giving the same (unpredictable, intermittent) error.

Turns out that the objConn in code has a CommandTimeout property, too … which defaults to 30 seconds. So if you set objConn.CommandTimeout = 0 (or just to a really high number), you can disable that, too.

Those timeouts are of course affected by the speed of the server, so they wouldn’t be occurring on a faster machine.

SQL Server Collation

June 25, 2010

When you’re installing SQL Server, and you get to the screen where you have to choose the Collation Settings, you’re probably just accepting the default of “Dictionary order, case-insensitive, for use with 1252 Character set,” right?

Guess what? They’re serious about that being case-insensitive, i.e., any SQL statements you may run against that server and its databases aren’t case-sensitive … so any UPPER or LOWER functions used in your WHERE-clause comparisons are just being ignored.

That’s fine, except for when you want to be doing case-sensitive string comparisons.

So if you’ve set up a server instance/database like that, and you need to run a case-sensitive query (e.g., in the WHERE clause of a user-defined function or a stored procedure), how do you do that? With the T-SQL COLLATE function and a case-sensitive (CS) collation name, like so:

SELECT Account FROM sysdba.Account
WHERE Account LIKE 'a%'
COLLATE SQL_Latin1_General_Cp1_CS_AS

I haven’t tested whether the SLX Provider can handle the COLLATE function—and would be quite pleasantly surprised if it can….