@bobnoordam

Category: SqlServer

SQLServer table variables

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:

Failed to generate a user instance of SQL Server due to failure in retrieving the user’s local application data path

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: The user identity your application is trying to create an instance of the SQL server with, does not have a […]

Resolving coalation conflicts with temporary tables

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 temporary tables in TempDb.. to have a different collation too. To specify the collation your database has during the creation […]

Sql Server sorting with a partial numeric field

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. However, in this case the value in the field COULD also contain additional characters that altough not important for the […]