Format:
jdbc:sqlserver://xxx.xxx.xxx.xxxx;databaseName=MyDatabase;applicationName=Myclient;responseBuffering=adaptive;loginTimeout=5;lockTimeout=60000
Now the problem was that we had some other 'Connection String' information under the 'Advanced Settings' for this connection that we had configured to turn Unicode characters OFF for this dsn (thus using asii characters and varchar vs. unicode and nvarchar). This was for performance as we had varchar fields on columns, and when we use cfquery param, and cf_sql_varchar, the dsn connection setting drives whether you are sending 'varchar(8000)' (non-unicode) or a' nvarchar(4000)' parameter to the database.
Database Indexes: If your index has to convert this nvarchar type to a varchar type of the column, then it will more than likely skip using the index all together and you loose the benefit of having an index on that column in your database (i.e. an email address field).
With Profiling ON we were able to watch the requests come through by manipulating the jdbc URL connection string as follows:
sendStringParametersAsUnicode=false will send the cfquery param data through as a varchar(8000) field
sendStringParametersAsUnicode =true (which is the default) will send it through as a nvarchar(4000) field
What we noticed is that those types of queries with:
<cfqueryparam value="#arguments.email#" cfsqltype="cf_sql_varchar">
would not use the index.
Another thing we noticed was that we had specified sendStringParametersAsUnicode=false in the 'Connection String' area under Advanced Settings. This was NOT being taken into effect when sending parameterized queries to the db. We HAD to move this string into the JDBC URL portion in order for it to work. Bug in ColdFusion?? We're not sure, but we hope this helps someone out there.
This is how the dsn should look:
