dosql

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

Additional information:

->An exception occurred while executing a Transact-SQL statement or batch.

    (Microsoft.SqlServer.ConnectionInfo)

–>Cannot resolve the collation conflict between “Latin1_General_CS_AS” and “SQL_Latin1_General_CP850_CI_AS” in the UNION operation. (Microsoft SQL Server, Error: 468)

Server collation is: SQL_Latin1_General_CP850_CI_AS 

Database collation: 

select cast(name as varchar(50)), cast(collation_name as varchar(40)) from sys.databases

This is useful when the web service is running on a service account which authenticates to SQL Server, and users are not authenticated with SQL Server.  In order to track user’s action, we need to store user information in the session.

To Set:

declare @ContextInfo varbinary(128)

select @ContextInfo = ContextInfo from ApplicationUser where UserName = ‘User1′

– where User1 is the application user name

SET CONTEXT_INFO @ContextInfo

To retrieve the context

http://search.techrepublic.com.com/search/data+warehouse+and+microsoft+sql+server+2008.html

If you want to lock a file that has been checked out by a specific user who left the project, you can unlock the specific file or delete the unwanted workspace.

Table variables have the following advantages over temporary tables:

• Table variables have a well defined scope at the end of which they are automatically cleared. 
• Table variables result in fewer recompilations of a stored procedure as compared to temporary tables. 
• Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.

These are some of the drawbacks for table variables as compared to temporary tables:

• Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query performance when compared to a temporary table with non-clustered indexes.
• Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.
• The table definition cannot be changed after the initial DECLARE statement.
• Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
• CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
• You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was created outside the EXEC statement or the sp_executesql stored procedure. Because table variables can be referenced in their local scope only, an EXEC statement and a sp_executesql stored procedure would be outside the scope of the table variable. However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table variables local scope is in the EXEC statement or the sp_executesql stored procedure. 

Note:

• TempDB use. Both temp table and table variable uses tempdb for storage.

Microsoft has a new certificate for SQL pro.

http://www.microsoft.com/learning/mcp/master/products/default.mspx#EZC