Strange Problem with SQL Server 2005 Reporting Service Database Name

Thursday, 30 April 2009 23:21 by RanjanBanerji

I was setting up SQL Reporting Services today and observed a very strange problem.  Once everything was installed and I was using the Reporting Services Configuration Tool I kept getting an error when trying to create the Reporting Service database.  The error was none specific and said I should look into permissions.  I checked permissions and determined that was not the problem.

I then proceeded to use the generate script option and executed the script in SQL Management Studio and I saw that there was an error.  The error was towards the bottom of the script at the following line:

 

-- END STORED PROCEDURES

USE XYZ[XYZReportServerTempDB]

-- standard script to set the database version, can  be used both the catalog database and the tempdb database

 

The error was that XYZ database did not exist. Indeed it did not. The big question is why did the script say Use XYZ{XYZReport…] to start with? I then set out to try the following:

  1. Try to configure reporting services with a database named XYZReportServer
  2. Try to configure reporting services with a database named XYZ

What needs to be kept in mind is that the default name for a SQL Reporting Services database is ReportServer.  Generating scripts for these two different database names gave some interesting results.  No errors with the database name as XYZ.  But XYZReportServer will generate the error.  Further testing showed that [AnyName]ReportServer generated this error.  Any other database name worked.

So what’s with this?  I briefly searched and did not find anything on this subject.  Is it a bug?  a feature?  Is it something that only I have experienced?  I will have to try and reproduce this error on a different instance of SQL Server 2005 Reporting Services.  In the mean time if you are getting an error creating a reporting services database check the name.

Categories:   SQL Server
Actions:   E-mail | Permalink | Comments (0) | Comment RSSRSS comment feed

SQL Server 2005 Backup a Remote Database

Monday, 15 September 2008 23:17 by RanjanBanerji

So you have a remote SQL Server 2005 database running on a different network than your client machine and you want a backup copy of that database.  Most common scenario, the database is on your web hosting server.  Somehow I never thought this would be a big issue.  I am so used to just using the SQL Management Studio, right clicking on a database, selecting Tasks, and then backup.

But when you backup SQL Server, it is to a local drive or a mapped network drive that the server itself has access to.  Implying that if you are accessing SQL Server on a server on another network on a machine that you do not have access to, you cannot backup SQL Server to your drive.  So how do you get a backup copy?

I found it quite frustrating that I can connect to the database using SQL Management Studio, run queries, make changes etc, but I cannot get a copy of the database to my machine.  Under Tasks there is an option to Copy Database but I just could not get that to work.  I kept getting a security Exception.  One day I will have to look into why.  Until then lets proceed to the so called right way to do it.

Remember DTS from all prior versions of SQL Server?  We it kind of vanished with SQL Server 2005.  Not really, it has a new name, SSIS and is installed when you install Integration Services.  Once installed you will see "SQL Server Business Intelligence Development Studio" on your Start Menu under SQL Server 2005.  Click on it and Visual Studio will launch.  At first I thought this has got to be a mistake.  But no, I was at the right spot.  I created a new Integrated Services Project and under that project by right clicking on solution explorer I selected the import export wizard.

Now the familiar DTS Wizard pops up and you can do what you were used to doing in all prior versions of SQL Server 2005.  Connect to the remote database and get all your data to a local database.  Why couldn't this be a single link on Management Studio as a simple Copy Database?  Because that would make it logical and simple.

By the way, my data transfer failed several times before succeeding, so be patient. :-)

 

 

Categories:   SQL Server
Actions:   E-mail | Permalink | Comments (11) | Comment RSSRSS comment feed