C# Code Example for Configuring SQL 2005 Reporting Services?

Question

I have found the MSDN articles describing the classes, public methods, etc. for configuring SQL Server 2005 Reporting Services via WMI. I need to initially configure them and just do this:

- Report Server Virtual Directory
- Windows Service Identity
- Web Service Identity
- Database Setup
- Email Settings
- Execution Account

Is there a C# code example that already exists putting together all of this? I didn't want to reinvent the wheel if I didn't need to.

---

-Ed

Answer 1

Hi Ed,

As far as I know, there does not have a project that putting the code together.

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 2

Thanks Jin.

Do you know of any other complete examples then in VB, or maybe just scripting that show how each one of those items is configured in Reporting Services?

---

-Ed

Answer 3

Hi Ed,

I have implemented some WMI script file on:
http://cid-3c7e963ff6ccd974.skydrive.live.com/self.aspx/.Public/WMI.rar


To use the sample, please unpake the file. In each script file, please add the input parameters under the remark "Add the input parameters.".

For any other methods, please see in SQL Server Books Online:
http://msdn.microsoft.com/en-us/library/ms154070(SQL.90).aspx

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 4

Jin,

Thanks so much! This is great! Your work is awesome!

I have a couple of issues that came up...

1) CreateVirtualDirectory.vbs creates the virtual directory for the Report Server just fine. Now how do I distinguish a script to create the virtual directory for the Report Manager?


2) I decided to go ahead and use what you did for scripting CreateApplicationPool (using http://msdn.microsoft.com/en-us/library/ms155349(SQL.90).aspx). I found the syntax is wrong here when I kept getting an error, so I put PowerShell on the server and pulled the following info:

Name       : CreateApplicationPool
MemberType : Method
Definition : System.Management.ManagementBaseObject CreateApplicationPool(System.String Name, System.Boolean UseBuiltIn
             Account, System.String Account, System.String Password)

However, I'd like to use "NT Authority\NetworkService" or "Network Service" as the account (which is an option for the Builtin accounts when I us RSConfigTool.exe). This is the syntax I used:

objInParam.Properties_.Item(

"Name") = "ReportServer"
objInParam.Properties_.Item("UseBuiltInAccount") = true
objInParam.Properties_.Item("Account") = "NT Authority\NetworkService"
objInParam.Properties_.Item("Password") = ""

But I always get an error with a negative number for the HRESULT.

Any ideas?

---

-Ed

Answer 5

Hi Ed,

 

1.For Report Manager, we need to change the follow code to be ReportManager class:

Set objShare = objWMIService.Get("MSReportServer_ConfigurationSetting.InstanceName='MSSQLSERVER'")

To

Set objShare = objWMIService.Get("MSReportManager_ConfigurationSetting.InstanceName='MSSQLSERVER'")

 

 

2. If the status number is: -2147220957, that the cause should be that we have a application pool with the same name in the Internet Information Server (IIS). To solve the issue, please change the objInParam.Properties_.Item("Name") =  "ReportServer2"  to another one.

 

Many thanks for your remind. That is sure the document is invalid. I would suggest you submit a feedback at http://connect.microsoft.com

 

Thanks,

Jin Chen

---

Jin Chen - MSFT

Answer 6

Jin,

1. That did it, thanks!

2. That is the negative status number I was getting, but renaming the AppPool wasn't doing it. I instead used this code to make it work:

objInParam.Properties_.Item(

"Name") = "ReportServer"
objInParam.Properties_.Item("UseBuiltInAccount") = false
objInParam.Properties_.Item("Account") = "SRV\administrator"
objInParam.Properties_.Item("Password") = "password"

Unfortunately I don't want it to be setup with the Administrator account, but I figured I would try. I couldn't find something showing me the correct syntax to make it "NT Authority\Network Service" and I did try "Network Service" with and without a space, and also without "NT Authority."

3. I was wondering which methods go with which to create the database and initialize it. Looks like GenerateDatabaseCreationScript, GenerateDatabaseRightsScript, InitializeReportServer...?

---

-Ed

Answer 7

Hi Ed,

2.What about using ".\LocalSystem"?

3. Yes. We just have GenerateDatabaseCreationScript and GenerateDatabaseRightsScript to generate the script for create the report server catalog database and assign rights for the user. We can generate the script to a shared folder, and then use the SQLCMD to execute the script.

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 8

Jin,

2. Will try that.

3. I was busy coding this earlier and then noticed both methods don't seem to have a string for the script as described here for example: http://msdn.microsoft.com/en-us/library/ms152823(SQL.90).aspx

Instead I found the following:

TypeName   : System.Management.ManagementObject#root\Microsoft\SqlServer\ReportServer\v9\Admin\MSReportServer_Configura
             tionSetting
Name       : GenerateDatabaseCreationScript
MemberType : Method
Definition : System.Management.ManagementBaseObject GenerateDatabaseCreationScript(System.String DatabaseName, System.I
             nt32 Lcid)

(Not even sure what Lcid would represent... )

TypeName   : System.Management.ManagementObject#root\Microsoft\SqlServer\ReportServer\v9\Admin\MSReportServer_Configura
             tionSetting
Name       : GenerateDatabaseRightsScript
MemberType : Method
Definition : System.Management.ManagementBaseObject GenerateDatabaseRightsScript(System.String UserName, System.String
             DatabaseName, System.Boolean IsRemote, System.Boolean IsWindowsUser)

So I am a little stumped now...

---

-Ed

Answer 9

Hi Ed,

I have uploaded a new package file.
http://cid-3c7e963ff6ccd974.skydrive.live.com/self.aspx/.Public/WMI%5E_New.rar

The package includes GenerateDatabaseCreationScript.vbs, GenerateDatabaseRightsScript.vbs, CreateApplicationpool.vbs and InitializeReportServer.vbs.

For LCID, please see:
http://msdn.microsoft.com/en-us/goglobal/bb964664.aspx

Please use the "LCID Dec" value.

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 10

Thanks again Jin! Your help has been greatly appreciated.

I wish I could say all is good now, but it looks like there is some minor stuff happening. Because both of those .SQL scripts generate just fine, but the database isn't getting created and doesn't exist when I check.

And InitializeReportServer.vbs gives me an HRESULT of -2146233088

---

-Ed

Answer 11

Hi Ed,

I just forgot to inform you, we need to change the following code in the GenerateDatabaseCreationScript.vbs:
WshShell.run "cmd /c sqlcmd -E -S v-jc -i """ & filePath & """", 1, true

Here, we use the sqlcmd to run the sql script.
sqlcmd -E -S <servername>

Please replace the <servername> with your SQL Server database engine machine name.

Or using: sqlcmd -U <username> -P <password> -S <servername> -i <filepath>

In this case, please make the SQL Server Script is generated at:
C:\GenerateDatabaseCreationScript.sql

If you have any more questions, please feel free to ask.

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 12

Well tried just: sqlcmd -E -S SERVER -i C:\GenerateDatabaseCreationScript.sql

from the Command Line to ensure it was going to work. I was also tried the previous sqlcmd, too. I got the following error, and was getting one like it with the previous sqlcmd, too:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

I followed http://support.microsoft.com/kb/914277 to start the SQL Client and Browser services again, but still got the same error.

---

-Ed

Answer 13

Jin,

Figured it out:

sqlcmd -E -S SERVER\MSSQLSERVER -i C:\GenerateDatabaseCreationScript.sql

or

sqlcmd -E -S .\MSSQLSERVER -i C:\GenerateDatabaseCreationScript.sql (since it is the local server)


Had to give the SQL database instance, ha!

However the InitializeReportServer.vbs still gives an HRESULT -2146233088. And when I run RSConfigTool.exe to check the database is setup, just from the scripts maybe, it isn't setup under Database Connection at all.

---

-Ed

Answer 14

Hi Ed,

We need to set database connection before running InitializeReportServer.vbs.

Please see "SetDatabaseConnection" method for more information:
http://msdn.microsoft.com/en-us/library/ms155102(SQL.90).aspx

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 15

Thanks Jin. That procedure helped and the database is connecting up now.

The one issue I am running into now, while debugging the scripts over and over, is that with the SetWebServiceIdentity.vbs sets the AppPool for Report Server just fine, but not for Report Manager.

I thought it might have to do with the Virtual Directory not being set for the Report Manager earlier, but that wasn't it.

---

-Ed

Answer 16

Jin,

And along with my last question there, initializing the database still yields an HRESULT of -2146233088 after running the SetDatabaseConnection.vbs script before it.

---

-Ed