Category: SqlServer

Detecting and acting on the database name to change query behaviour for example for different company workflows within the same database. This is BRITTLE because database names can change outside your application. The safe way to handle this is by checking some flag to determine correct processing.

table variables live in tempdb but are created at the declaration and dropped at the end of the batch. This is a nice alternative to temptables if you do no need access to the temporary data from multiple sessions, queries or sources, and frees you from the managent of temptables. Basic format:

When publishing a web application which includes an integrated SQL database you receive the following error: Failed to generate a user instance of SQL Server due to failure in retrieving the user’s local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. Cause: […]

When writing long query’s or stored procedures using temporary tables, you may run into an error like: Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation. The cause of this error is that the tables you are querying against have a different collation then the server default. This causes the […]

Recently i came accross a database that stored customer numbers in a NVARCHAR field and wanted to sort on this value. By default this will not give you the order you want, since you will get a sort order like: 1 10 11 2 20 etc. This can be easily overcome with the CONVERT statement. […]