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

http://blogs.technet.com/dataplatforminsider/archive/2008/07/09/sql-server-2008-available-on-august-price-list.aspx

Tried to install VSTS Database Edition GDR June CTP as mentioned on Data Dude’s blog.

• Installed Microsoft Visual Studio 2008 Service pack 1 Beta

This took quite some time to finish. On one computer I started the installation before went for lunch, but was still installing when I came back. On another computer, the installation was hanging after finished download. However the installation was able to finish once I restarted the installation.

During the initialization phase, the install process complained the SQL Server CE was newer than the version on the installation pack. So I removed the SQL CE to let the installation continue.

• Install VSTS Database Edition GDR

This didn’t take long and finished without any problem.

• Verification 

Started up Visual Studio 2008, and got an error “System.Data.SqlServerCe null pointer”, “Unknown connection option in connection string”, etc. Had to reinstall Microsoft SQL Server Compact 3.5 SP1 Beta and Synchronization Services for ADO.Net v1.0 SP1 Beta to get rid of the error.

Encounted this error when setting up SQL Server 2008 Release Candidate 0: 

The following error has occurred:

This access control list is not in canonical form and therefore cannot be modified.

The SQL Server 2008 installation was on “Installation Progress” -> “Running post-Windows Installer configuration timing actions.” -> ASConfigAction_install_postmsi_Cpu32.

This seems to be a permission issue. I tried to grant Admin permission to SQL Service account however it didn’t help.

After hitting ignore several times, the installation Progress shows Full-Text Search failed to install but all other components were successfuly installed.

I got a package load error when I start up Visual Studio 2008:

The Microsoft.VisualStudio.Xaml ({E58C2A8B-BCC4-4559-AD59-D62EB6D58A22}) did not load because of previous errors.

Now this file “DisableAjaxPolicy.cmd” seems to fix the VS2008 issue and this article explains the reason:

http://weblogs.asp.net/scottgu/archive/2007/07/26/vs-2008-and-net-3-5-beta-2-released.aspx

Retried the installation and this is in the log file “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20080708_195940\Detail.txt”:

2008-07-08 20:21:10 Slp: Sco: Attempting to set directory full path
2008-07-08 20:21:10 Slp: Sco: Attempting to normalize directory path c:\Program Files\Microsoft SQL Server\100\Shared\
2008-07-08 20:21:10 Slp: Sco: Attempting to check if directory c:\Program Files\Microsoft SQL Server\100\Shared\ exists
2008-07-08 20:21:10 Slp: Sco: Attempting to set security descriptor for directory c:\Program Files\Microsoft SQL Server\100\Shared\, security descriptor D:(A;OICI;FRFX;;;S-1-5-21-3603644326-1044473691-3390758382-1089)
2008-07-08 20:21:10 Slp: Sco: Attempting to check if directory c:\Program Files\Microsoft SQL Server\100\Shared\ exists
2008-07-08 20:21:10 Slp: Sco: Attempting to normalize security descriptor D:(A;OICI;FRFX;;;S-1-5-21-3603644326-1044473691-3390758382-1089)
2008-07-08 20:21:10 Slp: Sco: Attempting to replace account with sid in security descriptor D:(A;OICI;FRFX;;;S-1-5-21-3603644326-1044473691-3390758382-1089)
2008-07-08 20:21:10 Slp: ReplaceAccountWithSidInSddl — SDDL to be processed:  D:(A;OICI;FRFX;;;S-1-5-21-3603644326-1044473691-3390758382-1089)
2008-07-08 20:21:10 Slp: ReplaceAccountWithSidInSddl — SDDL to be returned:  D:(A;OICI;FRFX;;;S-1-5-21-3603644326-1044473691-3390758382-1089)
2008-07-08 20:21:10 Slp: Sco: Attempting to set directory full path
2008-07-08 20:21:10 Slp: Sco: Attempting to normalize directory path c:\Program Files\Microsoft SQL Server\100\COM\
2008-07-08 20:21:10 Slp: Sco: Attempting to check if directory c:\Program Files\Microsoft SQL Server\100\COM\ exists
2008-07-08 20:21:10 Slp: Sco: Attempting to set security descriptor for directory c:\Program Files\Microsoft SQL Server\100\COM\, security descriptor D:(A;OICI;FRFX;;;S-1-5-21-3603644326-1044473691-3390758382-1089)
2008-07-08 20:21:10 Slp: Sco: Attempting to check if directory c:\Program Files\Microsoft SQL Server\100\COM\ exists
2008-07-08 20:21:10 Slp: Sco: Attempting to normalize security descriptor D:(A;OICI;FRFX;;;S-1-5-21-3603644326-1044473691-3390758382-1089)
2008-07-08 20:21:10 Slp: Sco: Attempting to replace account with sid in security descriptor D:(A;OICI;FRFX;;;S-1-5-21-3603644326-1044473691-3390758382-1089)
2008-07-08 20:21:10 Slp: ReplaceAccountWithSidInSddl — SDDL to be processed:  D:(A;OICI;FRFX;;;S-1-5-21-3603644326-1044473691-3390758382-1089)
2008-07-08 20:21:10 Slp: ReplaceAccountWithSidInSddl — SDDL to be returned:  D:(A;OICI;FRFX;;;S-1-5-21-3603644326-1044473691-3390758382-1089)
2008-07-08 20:21:10 Slp: Prompting user if they want to retry this action

Right click on “C:\Program Files\Microsoft SQL Server\100\COM” and select Properties -> Security. Windows reported this error:

“The permissions on bin are incorrectly ordered, which may cause some entries to be ineffective. Press OK to continue and sort the permissions correctly, or Cancel to reset the permissions.” Click “OK” to fix the permission issue.

Then another error occurred:

2008-07-08 20:52:22 Slp: Sco: Attempting to set directory full path
2008-07-08 20:52:22 Slp: Sco: Attempting to normalize directory path C:\Program Files\Microsoft SQL Server\MSAS10.SQL2K8I1\OLAP\bin\
2008-07-08 20:52:22 AS: Setting permision on folder C:\Program Files\Microsoft SQL Server\MSAS10.SQL2K8I1\OLAP\bin\.
2008-07-08 20:52:22 Slp: Sco: Attempting to check if directory C:\Program Files\Microsoft SQL Server\MSAS10.SQL2K8I1\OLAP\bin\ exists
2008-07-08 20:52:22 Slp: Sco: Attempting to set security descriptor for directory C:\Program Files\Microsoft SQL Server\MSAS10.SQL2K8I1\OLAP\bin\, security descriptor (A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;0×1200a9;;;S-1-5-21-3603644326-1044473691-3390758382-1090)
2008-07-08 20:52:22 Slp: Sco: Attempting to check if directory C:\Program Files\Microsoft SQL Server\MSAS10.SQL2K8I1\OLAP\bin\ exists
2008-07-08 20:52:22 Slp: Sco: Attempting to normalize security descriptor (A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;0×1200a9;;;S-1-5-21-3603644326-1044473691-3390758382-1090)
2008-07-08 20:52:22 Slp: Sco: Adding ‘D:’ DACL identifier to the input security descriptor (A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;0×1200a9;;;S-1-5-21-3603644326-1044473691-3390758382-1090)
2008-07-08 20:52:22 Slp: Sco: Attempting to replace account with sid in security descriptor D:(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;0×1200a9;;;S-1-5-21-3603644326-1044473691-3390758382-1090)
2008-07-08 20:52:22 Slp: ReplaceAccountWithSidInSddl — SDDL to be processed:  D:(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;0×1200a9;;;S-1-5-21-3603644326-1044473691-3390758382-1090)
2008-07-08 20:52:22 Slp: ReplaceAccountWithSidInSddl — SDDL to be returned:  D:(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;0×1200a9;;;S-1-5-21-3603644326-1044473691-3390758382-1090)
2008-07-08 20:52:22 Slp: Prompting user if they want to retry this action
2008-07-08 21:01:35 Slp: Sco: Attempting to check if directory C:\Program Files\Microsoft SQL Server\MSAS10.SQL2K8I1\OLAP exists
2008-07-08 21:01:35 Slp: Sco: Attempting to normalize security descriptor D:(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)
2008-07-08 21:01:35 Slp: Sco: Attempting to replace account with sid in security descriptor D:(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)
2008-07-08 21:01:35 Slp: ReplaceAccountWithSidInSddl — SDDL to be processed:  D:(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)
2008-07-08 21:01:35 Slp: ReplaceAccountWithSidInSddl — SDDL to be returned:  D:(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)
2008-07-08 21:01:35 Slp: Prompting user if they want to retry this action
2008-07-08 21:03:17 Slp: Sco: Attempting to set directory full path
2008-07-08 21:03:17 Slp: Sco: Attempting to normalize directory path C:\Program Files\Microsoft SQL Server\100\DTS\
2008-07-08 21:03:17 Slp: Sco: Attempting to check if directory C:\Program Files\Microsoft SQL Server\100\DTS\ exists
2008-07-08 21:03:17 Slp: Sco: Attempting to get security descriptor for directory C:\Program Files\Microsoft SQL Server\100\DTS\
2008-07-08 21:03:17 Slp: Sco: Attempting to check if directory C:\Program Files\Microsoft SQL Server\100\DTS\ exists
2008-07-08 21:03:17 Slp: Sco: Returning security descriptor O:BAG:SYD:(A;;0×1200a9;;;BU)(A;OICIIOID;GXGR;;;BU)(A;;0×1301bf;;;PU)(A;OICIIOID;SDGXGWGR;;;PU)(A;;FA;;;BA)(A;OICIIOID;GA;;;BA)(A;;FA;;;SY)(A;OICIIOID;GA;;;SY)(A;;FA;;;BA)(A;OICIIOID;GA;;;CO)(A;;0×1301bf;;;S-1-5-13)(A;OICIIOID;SDGXGWGR;;;S-1-5-13)
2008-07-08 21:03:17 SSIS: Adding permissions on folder ‘C:\Program Files\Microsoft SQL Server\100\DTS\’ - ACL: ‘(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;0×1200a9;;;BU)’
2008-07-08 21:03:17 Slp: Sco: Attempting to set security descriptor for directory C:\Program Files\Microsoft SQL Server\100\DTS\, security descriptor O:BAG:SYD:(A;;0×1200a9;;;BU)(A;OICIIOID;GXGR;;;BU)(A;;0×1301bf;;;PU)(A;OICIIOID;SDGXGWGR;;;PU)(A;;FA;;;BA)(A;OICIIOID;GA;;;BA)(A;;FA;;;SY)(A;OICIIOID;GA;;;SY)(A;;FA;;;BA)(A;OICIIOID;GA;;;CO)(A;;0×1301bf;;;S-1-5-13)(A;OICIIOID;SDGXGWGR;;;S-1-5-13)(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;0×1200a9;;;BU)
2008-07-08 21:03:17 Slp: Sco: Attempting to check if directory C:\Program Files\Microsoft SQL Server\100\DTS\ exists
2008-07-08 21:03:17 Slp: Sco: Attempting to normalize security descriptor O:BAG:SYD:(A;;0×1200a9;;;BU)(A;OICIIOID;GXGR;;;BU)(A;;0×1301bf;;;PU)(A;OICIIOID;SDGXGWGR;;;PU)(A;;FA;;;BA)(A;OICIIOID;GA;;;BA)(A;;FA;;;SY)(A;OICIIOID;GA;;;SY)(A;;FA;;;BA)(A;OICIIOID;GA;;;CO)(A;;0×1301bf;;;S-1-5-13)(A;OICIIOID;SDGXGWGR;;;S-1-5-13)(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;0×1200a9;;;BU)
2008-07-08 21:03:17 Slp: Sco: Attempting to replace account with sid in security descriptor O:BAG:SYD:(A;;0×1200a9;;;BU)(A;OICIIOID;GXGR;;;BU)(A;;0×1301bf;;;PU)(A;OICIIOID;SDGXGWGR;;;PU)(A;;FA;;;BA)(A;OICIIOID;GA;;;BA)(A;;FA;;;SY)(A;OICIIOID;GA;;;SY)(A;;FA;;;BA)(A;OICIIOID;GA;;;CO)(A;;0×1301bf;;;S-1-5-13)(A;OICIIOID;SDGXGWGR;;;S-1-5-13)(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;0×1200a9;;;BU)
2008-07-08 21:03:17 Slp: ReplaceAccountWithSidInSddl — SDDL to be processed:  O:BAG:SYD:(A;;0×1200a9;;;BU)(A;OICIIOID;GXGR;;;BU)(A;;0×1301bf;;;PU)(A;OICIIOID;SDGXGWGR;;;PU)(A;;FA;;;BA)(A;OICIIOID;GA;;;BA)(A;;FA;;;SY)(A;OICIIOID;GA;;;SY)(A;;FA;;;BA)(A;OICIIOID;GA;;;CO)(A;;0×1301bf;;;S-1-5-13)(A;OICIIOID;SDGXGWGR;;;S-1-5-13)(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;0×1200a9;;;BU)
2008-07-08 21:03:17 Slp: ReplaceAccountWithSidInSddl — SDDL to be returned:  O:BAG:SYD:(A;;0×1200a9;;;BU)(A;OICIIOID;GXGR;;;BU)(A;;0×1301bf;;;PU)(A;OICIIOID;SDGXGWGR;;;PU)(A;;FA;;;BA)(A;OICIIOID;GA;;;BA)(A;;FA;;;SY)(A;OICIIOID;GA;;;SY)(A;;FA;;;BA)(A;OICIIOID;GA;;;CO)(A;;0×1301bf;;;S-1-5-13)(A;OICIIOID;SDGXGWGR;;;S-1-5-13)(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;0×1200a9;;;BU)
2008-07-08 21:03:17 Slp: Prompting user if they want to retry this action

2008-07-08 21:07:21 Slp: SetValue: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\ConfigurationState, Name = Connectivity_Full
2008-07-08 21:07:21 Slp: Action “ConfigEvent_SQL_BIDS_Full_sql_bids_Cpu32_Install_Finalize_finalize” will return false due to the following conditions:
2008-07-08 21:07:21 Slp: Condition “Feature dependency condition for action: ConfigEvent_SQL_BIDS_Full_sql_bids_Cpu32_Install_Finalize_finalize The condition tests feature: SQL_BIDS_Full_sql_bids_Cpu32. There are 8 dependant features. The feature is tested for results: ValidateResult, Result, CleanupResult.” did not pass as it returned false and true was expected.
2008-07-08 21:07:21 Slp: Condition is false because the required feature ALL_VS_Shell_Cpu32 failed in result Result
2008-07-08 21:07:21 Slp: Error: Action “ConfigEvent_SQL_BIDS_Full_sql_bids_Cpu32_Install_Finalize_finalize” failed during execution.
2008-07-08 21:07:21 Slp: Completed Action: FinalizeTimingConfigAction, returned True
2008-07-08 21:07:21 Slp: ———————————————————————-
2008-07-08 21:07:21 Slp: Running Action: NotifyProgressComplete
2008-07-08 21:07:21 Slp: Completed Action: NotifyProgressComplete, returned True
2008-07-08 21:07:21 Slp: ———————————————————————-
2008-07-08 21:07:21 Slp: Running Action: ProduceStatusLogsBeforeFinishPage
2008-07-08 21:07:22 Slp: Completed Action: ProduceStatusLogsBeforeFinishPage, returned True
2008-07-08 21:07:22 Slp: ———————————————————————-
2008-07-08 21:07:22 Slp: Running Action: FinalizeProgressStatus
2008-07-08 21:07:22 Slp: Completed Action: FinalizeProgressStatus, returned True
2008-07-08 21:07:22 Slp: ———————————————————————-
2008-07-08 21:07:22 Slp: Running Action: RebootMessageAction
2008-07-08 21:07:22 Slp: Completed Action: RebootMessageAction, returned True
2008-07-08 21:07:22 Slp: ———————————————————————-
2008-07-08 21:07:22 Slp: Running Action: CloseUI
2008-07-08 21:07:22 Slp: Stop action skipped in UI Mode Full
2008-07-08 21:07:22 Slp: Completed Action: CloseUI, returned True
2008-07-08 21:07:33 Slp:
2008-07-08 21:07:33 Slp:
2008-07-08 21:07:33 Slp: ———————————————————————-
2008-07-08 21:07:33 Slp:
2008-07-08 21:07:33 Slp: Error result: -2068643839
2008-07-08 21:07:33 Slp: Result facility code: 1203
2008-07-08 21:07:33 Slp: Result error code: 1
2008-07-08 21:07:33 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine
2008-07-08 21:07:33 Slp: Sco: Attempting to open registry subkey Software\Microsoft\PCHealth\ErrorReporting\DW\Installed
2008-07-08 21:07:33 Slp: Sco: Attempting to get registry value DW0200
2008-07-08 21:08:15 Slp: Submitted 1 of 1 failures to the Watson data repository