When working in an environment that utilises a proxy server you may see the following error when trying to connect to a Report Server using Reporting Services Scripter

"The request failed with HTTP Status 407. Proxy authentication required."

In order to fix this you need to create a configuration file for RSScripter.exe

  1. Create a file called RSScripter.exe.config in the same folder as RSScripter.exe
  2. Copy the following into the file and replace http://yourproxy with your proxy details

<configuration>
    <system.net>
        <defaultProxy enabled="true" useDefaultCredentials="true">
            <proxy bypassonlocal="True" proxyaddress="http://yourproxy"/>
        </defaultProxy>
    </system.net>
</configuration>

You can read more details about this error and how to correct it for Visual Studio here http://support.microsoft.com/kb/910804

 

 

A new version of the Expressmaint database maintenance utility is now available for download. This version adds the ability to specify a custom backup file name format. Please note there are specific version for SQL 2005 (2.0.0.0) and SQL 2008 (2.0.0.1) that differ only in the version of SMO they use.

Download Expressmaint from sqldbatips.com

Download Expressmaint from Codeplex

Posted by sqldbatips | 1 comment(s)
Filed under: , ,

Along with 53 other MVP's I've contributed a chapter to the SQL Server MVP Deep Dives book which is now available for preorder. All author royalties, along with a contribution from Manning Publications, go to support War Child International (visit www.warchild.org for more information). To order go to www.SQLServerMVPDeepDives.com and use discount code sqldeep40 for 40% off!

Description

This is not an ordinary SQL Server Book. SQL Server MVP Deep Dives brings together the world's most highly-regarded SQL Server experts to create a masterful collection of tips, techniques, and experience-driven best practices for SQL Server development and administration. These SQL Server MVPs—53 in all—each selected a topic of great interest to them, and in this unique book, they share their knowledge and passion with you.

SQL Server MVP Deep Dives is organized into five parts: Design and Architecture, Development, Administration, Performance Tuning and Optimization, and Business Intelligence. Within each part, you'll find a collection of brilliantly concise and focused chapters that take on key topics like mobile data strategies, Dynamic Management Views, or query performance. The range of subjects covered is comprehensive, from database design tips to data profiling strategies for BI.

Posted by sqldbatips | with no comments
Filed under: , ,

This post describes step by step how to restore a full database backup taken with Expressmaint (or by any other means) using SQL 2005 Management Studio Express (SSMS). The same steps apply to SQL 2008. To download this free tool for SQL Server Express Edition use the links below

  1. Open SSMS (Start>Programs>Microsoft SQL Server 2005>SQL Server Management Studio Express)

  2. Connect to your SQL Server instance (in this example I am connection to a named instance called SQLExpress using .\SQLExpress as the server name). The default for a SQL Express install is to create a named instance called SQLExpress



  3. By default this will open up Object Explorer in the left pane of SSMS but if not click on View>Object Explorer from the menu or press F8

  4. Right click on the Databases folder in Object Explorer and choose Restore Database from the menu as shown below



  5. This will open the General tab of the Restore Database dialog window. Type in the name of the database you want to restore (in this example DB1). Select the From device radio button and click on the file browse button as shown below



  6. This will bring up the Specify Backup dialog that lets you select the specific backup file you want to restore. Click on the Add button to bring up the Locate Backup file dialog and browse to the backup file. In this example Expressmaint has been used to backup the DB1 database to C:\Backups\DB1 as shown below. Click on the file you want to restore and click OK



  7. The selected backup file will now be selected (as shown below) so click OK on the Specify Backup dialog to return to the Restore Database dialog as shown below



  8. On the Restore Database dialog select the checkbox in the Restore column in the lower part of the dialog next to your selected backup as shown below and then click on the Options tab in the upper left corner of the window as shown below



  9. On the Options tab review the database file locations to confirm they are correct and leave the Recovery State radio button on the default selection (RESTORE WITH RECOVERY) as shown below and then click OK to restore the database



  10. You should then get a pop up dialog box confirming that the restore has completed successfully as shown below. Click OK to close the Restore Database dialog. Congratulations the database has been restored! If you get an error see the troubleshooting section below.

 

Troubleshooting

The most common error you will see when restoring a database is shown below and is caused by there being connection to the database you are trying to restore. In order to restore an existing database there must be no user connections to that database.

As this error indicates, exclusive access is required to the database in order to restore a backup "over the top of it". Make sure any applications that use the database are switched off. To determine what connections there are to a database open a New Query window in SSMS (click on the New Query button in the top left of the tool) and run the following query (replace DB1 with the name of the database you are restoring) by clicking on the Execute button in the toolbar (or pressing F5)

use master
go
select
* from sys.sysprocesses where dbid = db_id('DB1')

In the example below there is just 1 session with id = 52 connected to the DB1 database

I can then go ahead and close this connection by using the KILL command which takes a session id (spid) as a parameter so in this example I would issue the following command in my SSMS query window and click on Execute (or press F5)

KILL 52

If I then rerun the first query there are no results indicating that there are no user connections to the DB1 database and I can proceed with the restore. If there are multiple connections to the database then you can repeate the KILL command with the relavent session id's.

A new version of the Expressmaint database maintenance utility is now available for download. This version fixes an issue with the timeout parameter (-TO) being treated as seconds instead of minutes for the REORG task. Please note there are specific version for SQL 2005 (1.9.0.0) and SQL 2008 (1.9.0.1) that differ only in the version of SMO they use.

Download Expressmaint from sqldbatips.com

Download Expressmaint from Codeplex

 

Posted by sqldbatips | 1 comment(s)
Filed under: , ,

Microsoft SQL Server 2008 Release 2 (R2), formerly known as SQL Server Code-Named “Kilimanjaro”, is the next release of Microsoft SQL Server. Books Online is the primary documentation for SQL Server. Download a pre-release preview of Books Online for SQL Server 2008 R2 here

Important Installing SQL Server 2008 R2 Books Online upgrades your existing SQL Server 2008 version of Books Online. If you upgrade, be aware that the content and help topics now describe tools and features of SQL Server 2008 R2. To reinstall SQL Server 2008 Books Online, you must first uninstall SQL Server 2008 R2 Books Online. Because SQL Server 2008 and SQL Server 2008 R2 share components, you cannot have side-by-side installations of Books Online on the same machine.

Note This pre-release version of SQL Server Books Online may contain topics that are empty, incomplete, or not up to date. Topics for new features may be missing and not all new or updated content has been reviewed for technical accuracy.

Posted by sqldbatips | with no comments
Filed under: ,

It has been up on Technet and MSDN for a few days now for subscribers but the August CTP of SQL Server 2008 R2 is now available to all from Technet. Get it here http://technet.microsoft.com/en-us/evalcenter/ee315247.aspx

SQL Server 2008 R2 expands on the value delivered in SQL Server 2008 to help your organization scale with confidence and improve IT and developer efficiency with new and enhanced tools for application and multi-server management, master data services and complex event processing. The new Self Service BI capabilities will empower end users to access, integrate, analyze and share information using business intelligence tools they already know – Microsoft Office.

The August Customer Technology Preview (CTP) includes Application and Multi-server Management which will help organizations manage database environments efficiently at scale with increased visibility and control across the application lifecycle. Microsoft SQL Server StreamInsight will also be made available for preview in the coming weeks. This new complex event processing technology will help businesses derive better insights by correlating event streams from multiple sources with near-zero latency.

The Fall 2009 CTP will include the remaining SQL Server 2008 R2 features including Master Data Services and Self Service Business Intelligence capabilities delivered in SQL Server Reporting Services and Project “Gemini”.

Posted by sqldbatips | 1 comment(s)
Filed under: , ,

If you install SQL 2005 Service Manager (Vista) on Windows 7 (the Vista version is required as it supports UAC) you will find that the system tray icon is hidden by default as part of the tidy up of the taskbar and notification/system tray area in Windows 7. You can customise the behaviour for a specific application by left clicking on the small triangle in the system tray area and clicking on the Customize... link as highlighted below

This will bring up the Notification Area Icons dialog and allow you to select the behaviour for SQL 2005 Service Manager which you can set to "Show icon and notifications" if you want the system tray icon for SQL 2005 Service Manager to remain in the system tray area at all times as shown below

 

Posted by sqldbatips | with no comments
Filed under:

I keep meaning to document these as I get quite a few emails asking about them so here are the possible return codes from the Expressmaint utility (and no, I don't know what happened to -1!)

 0      Success
-2      Error initialising log file
-3      Error connecting to SQL Server
-4      Error enumerating list of databases
-5      Error performing backup operation
-6      Error performing integrity check operation
-7      Error performing index operation
-8      Error performing stats operation
-9      Errors encountered but continue on error parameter set


 

Posted by sqldbatips | with no comments
Filed under:

One of the most useful tools for anyone involved in day to day support of SQL Servers is being able to keep up with the latest Microsoft Knowledge Base articles for the products you support. There are RSS feeds available for each of the currently supported editions that allow you to keep up to date with bugs and fixes for SQL Server and it generally only takes a few minutes every few days to skim them so that should you come across a problem it will jog your memory and allow you to more quickly address the issue. The following url's are the RSS feeds for the latest KB articles for SQL 2000, 2005 and 2008

2000 : http://support.microsoft.com/common/rss.aspx?rssid=2852 
2005 : http://support.microsoft.com/common/rss.aspx?rssid=2855
2008 : http://support.microsoft.com/common/rss.aspx?rssid=13165

More Posts Next page »