12/07/13
Problem Displaying the Estimated Execution Plan of a query takes a long time to complete.
Solution Although, the query is pretty complex, displaying the execution plan should not take more than few seconds. In my case it took over 20 minutes to have the estimated execution plan displayed. After trying different things like updating the statistics or dropping the automatically generated system statistics, the only thing that worked for me was to use a custom query that generates the drop and create statements for the _WA_Sys statistics: -- delete and create system statistics for several tables in the database USE DATABASE_NAME --database name SELECT N'DROP STATISTICS [' + schema_name(t.schema_id)+N'].['+ t.name + N'].[' + s.name + N'] CREATE STATISTICS [' + c.name + N'] ON [' + schema_name(t.schema_id)+N'].['+t.name + N'] ([' + c.name + N']) WITH FULLSCAN' FROM sys.stats s (NOLOCK) INNER JOIN sys.stats_columns (NOLOCK)sic ON sic.stats_id=s.stats_id AND sic.object_id=s.object_id INNER JOIN sys.tables t (NOLOCK) on t.object_id = s.object_id INNER JOIN sys.columns c (NOLOCK) on c.column_id = sic.column_id and c.object_id = sic.object_id WHERE s.name like '%_WA_Sys%' and t.name IN ('TABLE_NAME_1', -- specify here the name of the table one 'TABLE_NAME_2', -- specify here the name of the table two 'TABLE_NAME_3') -- specify here the name of the table tree ORDER BY t.name -- delete and create system statistics for all tables in the database USE DATABASE_NAME --database name SELECT N'DROP STATISTICS [' + schema_name(t.schema_id)+N'].['+ t.name + N'].[' + s.name + N'] CREATE STATISTICS [' + c.name + N'] ON [' + schema_name(t.schema_id)+N'].['+t.name + N'] ([' + c.name + N']) WITH FULLSCAN' FROM sys.stats s (NOLOCK) INNER JOIN sys.stats_columns (NOLOCK)sic ON sic.stats_id=s.stats_id AND sic.object_id=s.object_id INNER JOIN sys.tables t (NOLOCK) on t.object_id = s.object_id INNER JOIN sys.columns c (NOLOCK) on c.column_id = sic.column_id and c.object_id = sic.object_id WHERE s.name like '%_WA_Sys%' ORDER BY t.name
12/04/13
Problem When trying to install or uninstall SQL Server, the setup process fails with the following message: Rule "Restart Computer" failed. This happens because there are pending restart operations on the machine and the setup needs a reboot before continuing. The problem is, that sometimes, even if the computer is restarted, the setup process can fail at exactly the same step.
Solution Check if there are pending rename file operations that needs to be done after the computer is restarted. To do this open a registry editor (Start -> Run -> Regedit) and navigate to the following path: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\
Tags: restart computer failed
12/03/13
The following error is received when a user that is not sysadmin is trying to execute the xp_cmdshell stored procedure: exec xp_cmdshell 'dir c:' Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1 The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
Use the bellow code to grand execute permission to that user on the xp_cmdshell stored procedure: USE [master] CREATE USER [username] FOR LOGIN [username] GRANT Execute ON [dbo].[xp_cmdshell] TO [username]
After, trying again to execute the xp_cmdshell as the non-sysadmin user, the following error appears: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
This happens because when the xp_cmdshell stored procedure is executed by a non-sysadmin user, it connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account## which doesn?t exits.
11/28/13
Problem I seem that, you cannot display the table header in an every page of a SSRS report, even if you check the Repeat header columns on each page option in the Tablix properties (to open the table properties in Report Builder, right click on that specific table, and choose Tablix Properties).
Solution To solve this, follow the below steps: Make sure you check the Repeat header columns on each page option in the Tablix properties. Go to the Grouping pane (the bottom section of the report), click the small arrow from the upper-right corner, and click Advance (the static properties are now displayed).
Problem I seem that, when you scroll through a report in SSRS 2008, you cannot keep the column headers or the row headers visible even if you check the Keep Header Visible While Scrolling option in the Tablix properties (to open the table properties in Report Builder, right click on that specific table, and choose Tablix Properties). Solution This problem occurs because the properties used to keep the column or row headers visible on every page have changed from SQL Server 2005 to SQL Server 2008.
|
|