Connect To Oracle 64 Bit using SQL Server 2005 64 Bit

I'm using SQL Server 2005 64 Bit and having a problem

when I try to make a linked server to Oracle 10G 64 Bit (installed on the same

server) from management studio. I'm using "Oracle Provider for OLE DB" to

connect to Oracle 64 Bit database because it is the only provider but from what

I've read in Books Online that the provider doesn't support for Oracle 64 Bit.

When I’m using SSAS 2005 and try to connect to Oracle 64

Bit database, an error message appear when I’m using “Microsoft OLE DB Provider

for Oracle”:

Test connection

failed because of an error in initializing provider. Oracle client and

networking components were not found. These components are supplied by Oracle

Corporation and are part of the Oracle Version 7.3.3 or later client software

installation.

Provider is

unable to function until these components are

installed.

And also when I’m using “OracleClient Data Provider”, an

error message appear:

Test connection

failed because of an error in initializing provider. Attempt to load Oracle

client libraries threw BadImageFormatException. This problem will occur when

running in 64 bit mode with the 32 bit Oracle client components

installed.

Can you please tell me how to fix it ? Thanks in

advance.

Have you installed Oracle client? .NET Oracle provider wouldnt work without it.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I already installed the Oracle Client 10G version 2.0 (x64), but Still I can not find the solution. Help Please..|||

Try contacting customer support and report your problem.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I have the exact same issue. Any update would be appreciated.|||Ditto... so, I'm only interested in the SQLserver linked-server connection via "native" Oracle connection|||

I remember similar problem came up in the past.

Actually it hasnt affected Analysis Server, it only appeared in the BI Dev Studio running on 64bit machine. Try building your project on 32bit machine and deploy to 64bit Analysis Server. See if you get any errors in this case.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I had the same problem. I worked with the low-level Oracle support folks to no avail. I opened a ticket with Microsoft Dev support and, though they did significantly more investigation they were not able to solve the issue either. At this point, I am in a holding pattern for 64bit until this issue can be resolved. If anyone else has had even partial luck, it would be great to hear what you have tried.

|||

Hi,

We've got the same problem with SQL Server 2005 64-bit SP1. The MS hotfix installation brought us to Build 2153. Oracle 64-bit client software and 10.2.0.2 update has been installed at this server.

It's same parameter you have to set as it is within SQL Server 2000. But now it's within the provider settings.

Start MS SQL Server Management Studio. Go to "Server Objects/Linked Servers/Providers". Choose the properties of OraOLEDB.oracle and check "Allow in process".

My query is running now.

Best Regards,

Uli

|||Dear All,

I have found the solution for linked server to connect to Oracle 64 bit server:

If you want to setup a linked server from SQL 2005 64 bit to Oracle 10g 64 bit, you need to use the "OraOleDb.Oracle" which you can get from Oracle.

Instructions to Setup the Linked Server Connection
=========================================
- Open SQL Server Management Studio by going to START -- ALL PROGRAMS -- Microsoft SQL Server 2005

- Provide the Server Name, Authentication, Username and Password to connect to your
SQL Server at the dialog box. This will authenticate you and log you into
Management Studio.

- Expand the Server Objects folder by clicking on the + sign

- Now, expand the Linked Servers folder and you will see Providers folder. Expand
the Providers folder. You will see Oracle Provider for OLEDB. Right click on it and
go to Properties. Check the check boxes for "Dynamic Parameters" and "Allow
InProcess". Click OK and the dialogue box will close.

- Next, right click on the Linked Servers folder and choose New Linked Server

- In the New Linked Server Properties dialogue box, do the following:
-- Type in a Linked Server Name, for this example, we will call it 'Tiger'
-- Under Server Type, select Other Data Sources - for the provider name, select
Oracle Provider for OLEDB
-- In the Data Source field, type in the Oracle Service name (SQL Net Alias)
-- In the Product Name field, type in "Oracle"
-- Leave Provider String blank.

- Now, click on the "Security option" on the left pane:
-- Select the radio button "Be made using this security context" at the bottom
of the box.
-- Type in the User ID (your Oracle login), ie. Scott in the "Remote login"
field and your Password, ie. Tiger in the "With Password" field.

- Go to the "Server Options" tab in the Linked Server Properties:
-- While leaving the default selections selected, also click on "Collation
Compatible". Select "true" from the drop down button.
-- Click OK. This will create your Linked Server named Tiger.

For SSAS 2005 64 bit, I've tried Edward's suggestion that I develop the project in 32 bit server and deploy it to 64 bit server but it doesn't work and the same error still appear because I'm using "Oracle Provider for OLE DB" and SSAS 64 bit doesn't have that provider. I've installed Service Pack 1 and the Hotfix but it doesn't work also. I hope this problem will be solved with Service Pack 2. But is there anyone who knows how to fix this ?

Thanks in advance.|||

Thanks!

I have done that and it works!

|||

---

Thanks

The connection is working for me. But I have one more issue. I cannot view the "nvarchar" column from SQL Server 2005 when I try to see the description of any table from Oracle. I have done the same type of setting with SQL Server 2000 and there are no issues.

Please help.

sqlsql

Connect To Oracle 64 Bit using SQL Server 2005 64 Bit

I'm using SQL Server 2005 64 Bit and having a problem

when I try to make a linked server to Oracle 10G 64 Bit (installed on the same

server) from management studio. I'm using "Oracle Provider for OLE DB" to

connect to Oracle 64 Bit database because it is the only provider but from what

I've read in Books Online that the provider doesn't support for Oracle 64 Bit.

When I’m using SSAS 2005 and try to connect to Oracle 64

Bit database, an error message appear when I’m using “Microsoft OLE DB Provider

for Oracle”:

Test connection

failed because of an error in initializing provider. Oracle client and

networking components were not found. These components are supplied by Oracle

Corporation and are part of the Oracle Version 7.3.3 or later client software

installation.

Provider is

unable to function until these components are

installed.

And also when I’m using “OracleClient Data Provider”, an

error message appear:

Test connection

failed because of an error in initializing provider. Attempt to load Oracle

client libraries threw BadImageFormatException. This problem will occur when

running in 64 bit mode with the 32 bit Oracle client components

installed.

Can you please tell me how to fix it ? Thanks in

advance.

Have you installed Oracle client? .NET Oracle provider wouldnt work without it.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I already installed the Oracle Client 10G version 2.0 (x64), but Still I can not find the solution. Help Please..|||

Try contacting customer support and report your problem.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I have the exact same issue. Any update would be appreciated.|||Ditto... so, I'm only interested in the SQLserver linked-server connection via "native" Oracle connection|||

I remember similar problem came up in the past.

Actually it hasnt affected Analysis Server, it only appeared in the BI Dev Studio running on 64bit machine. Try building your project on 32bit machine and deploy to 64bit Analysis Server. See if you get any errors in this case.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I had the same problem. I worked with the low-level Oracle support folks to no avail. I opened a ticket with Microsoft Dev support and, though they did significantly more investigation they were not able to solve the issue either. At this point, I am in a holding pattern for 64bit until this issue can be resolved. If anyone else has had even partial luck, it would be great to hear what you have tried.

|||

Hi,

We've got the same problem with SQL Server 2005 64-bit SP1. The MS hotfix installation brought us to Build 2153. Oracle 64-bit client software and 10.2.0.2 update has been installed at this server.

It's same parameter you have to set as it is within SQL Server 2000. But now it's within the provider settings.

Start MS SQL Server Management Studio. Go to "Server Objects/Linked Servers/Providers". Choose the properties of OraOLEDB.oracle and check "Allow in process".

My query is running now.

Best Regards,

Uli

|||Dear All,

I have found the solution for linked server to connect to Oracle 64 bit server:

If you want to setup a linked server from SQL 2005 64 bit to Oracle 10g 64 bit, you need to use the "OraOleDb.Oracle" which you can get from Oracle.

Instructions to Setup the Linked Server Connection
=========================================
- Open SQL Server Management Studio by going to START -- ALL PROGRAMS -- Microsoft SQL Server 2005

- Provide the Server Name, Authentication, Username and Password to connect to your
SQL Server at the dialog box. This will authenticate you and log you into
Management Studio.

- Expand the Server Objects folder by clicking on the + sign

- Now, expand the Linked Servers folder and you will see Providers folder. Expand
the Providers folder. You will see Oracle Provider for OLEDB. Right click on it and
go to Properties. Check the check boxes for "Dynamic Parameters" and "Allow
InProcess". Click OK and the dialogue box will close.

- Next, right click on the Linked Servers folder and choose New Linked Server

- In the New Linked Server Properties dialogue box, do the following:
-- Type in a Linked Server Name, for this example, we will call it 'Tiger'
-- Under Server Type, select Other Data Sources - for the provider name, select
Oracle Provider for OLEDB
-- In the Data Source field, type in the Oracle Service name (SQL Net Alias)
-- In the Product Name field, type in "Oracle"
-- Leave Provider String blank.

- Now, click on the "Security option" on the left pane:
-- Select the radio button "Be made using this security context" at the bottom
of the box.
-- Type in the User ID (your Oracle login), ie. Scott in the "Remote login"
field and your Password, ie. Tiger in the "With Password" field.

- Go to the "Server Options" tab in the Linked Server Properties:
-- While leaving the default selections selected, also click on "Collation
Compatible". Select "true" from the drop down button.
-- Click OK. This will create your Linked Server named Tiger.

For SSAS 2005 64 bit, I've tried Edward's suggestion that I develop the project in 32 bit server and deploy it to 64 bit server but it doesn't work and the same error still appear because I'm using "Oracle Provider for OLE DB" and SSAS 64 bit doesn't have that provider. I've installed Service Pack 1 and the Hotfix but it doesn't work also. I hope this problem will be solved with Service Pack 2. But is there anyone who knows how to fix this ?

Thanks in advance.|||

Thanks!

I have done that and it works!

|||

---

Thanks

The connection is working for me. But I have one more issue. I cannot view the "nvarchar" column from SQL Server 2005 when I try to see the description of any table from Oracle. I have done the same type of setting with SQL Server 2000 and there are no issues.

Please help.

Connect To Oracle 64 Bit using SQL Server 2005 64 Bit

I'm using SQL Server 2005 64 Bit and having a problem when I try to make a linked server to Oracle 10G 64 Bit (installed on the same server) from management studio. I'm using "Oracle Provider for OLE DB" to connect to Oracle 64 Bit database because it is the only provider but from what I've read in Books Online that the provider doesn't support for Oracle 64 Bit.

When I’m using SSAS 2005 and try to connect to Oracle 64 Bit database, an error message appear when I’m using “Microsoft OLE DB Provider for Oracle”:

Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.

And also when I’m using “OracleClient Data Provider”, an error message appear:

Test connection failed because of an error in initializing provider. Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.

Can you please tell me how to fix it ? Thanks in advance.

Have you installed Oracle client? .NET Oracle provider wouldnt work without it.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I already installed the Oracle Client 10G version 2.0 (x64), but Still I can not find the solution. Help Please..|||

Try contacting customer support and report your problem.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I have the exact same issue. Any update would be appreciated.|||Ditto... so, I'm only interested in the SQLserver linked-server connection via "native" Oracle connection|||

I remember similar problem came up in the past.

Actually it hasnt affected Analysis Server, it only appeared in the BI Dev Studio running on 64bit machine. Try building your project on 32bit machine and deploy to 64bit Analysis Server. See if you get any errors in this case.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I had the same problem. I worked with the low-level Oracle support folks to no avail. I opened a ticket with Microsoft Dev support and, though they did significantly more investigation they were not able to solve the issue either. At this point, I am in a holding pattern for 64bit until this issue can be resolved. If anyone else has had even partial luck, it would be great to hear what you have tried.

|||

Hi,

We've got the same problem with SQL Server 2005 64-bit SP1. The MS hotfix installation brought us to Build 2153. Oracle 64-bit client software and 10.2.0.2 update has been installed at this server.

It's same parameter you have to set as it is within SQL Server 2000. But now it's within the provider settings.

Start MS SQL Server Management Studio. Go to "Server Objects/Linked Servers/Providers". Choose the properties of OraOLEDB.oracle and check "Allow in process".

My query is running now.

Best Regards,

Uli

|||Dear All,

I have found the solution for linked server to connect to Oracle 64 bit server:

If you want to setup a linked server from SQL 2005 64 bit to Oracle 10g 64 bit, you need to use the "OraOleDb.Oracle" which you can get from Oracle.

Instructions to Setup the Linked Server Connection
=========================================
- Open SQL Server Management Studio by going to START -- ALL PROGRAMS -- Microsoft SQL Server 2005

- Provide the Server Name, Authentication, Username and Password to connect to your
SQL Server at the dialog box. This will authenticate you and log you into
Management Studio.

- Expand the Server Objects folder by clicking on the + sign

- Now, expand the Linked Servers folder and you will see Providers folder. Expand
the Providers folder. You will see Oracle Provider for OLEDB. Right click on it and
go to Properties. Check the check boxes for "Dynamic Parameters" and "Allow
InProcess". Click OK and the dialogue box will close.

- Next, right click on the Linked Servers folder and choose New Linked Server

- In the New Linked Server Properties dialogue box, do the following:
-- Type in a Linked Server Name, for this example, we will call it 'Tiger'
-- Under Server Type, select Other Data Sources - for the provider name, select
Oracle Provider for OLEDB
-- In the Data Source field, type in the Oracle Service name (SQL Net Alias)
-- In the Product Name field, type in "Oracle"
-- Leave Provider String blank.

- Now, click on the "Security option" on the left pane:
-- Select the radio button "Be made using this security context" at the bottom
of the box.
-- Type in the User ID (your Oracle login), ie. Scott in the "Remote login"
field and your Password, ie. Tiger in the "With Password" field.

- Go to the "Server Options" tab in the Linked Server Properties:
-- While leaving the default selections selected, also click on "Collation
Compatible". Select "true" from the drop down button.
-- Click OK. This will create your Linked Server named Tiger.

For SSAS 2005 64 bit, I've tried Edward's suggestion that I develop the project in 32 bit server and deploy it to 64 bit server but it doesn't work and the same error still appear because I'm using "Oracle Provider for OLE DB" and SSAS 64 bit doesn't have that provider. I've installed Service Pack 1 and the Hotfix but it doesn't work also. I hope this problem will be solved with Service Pack 2. But is there anyone who knows how to fix this ?

Thanks in advance.|||

Thanks!

I have done that and it works!

|||

---

Thanks

The connection is working for me. But I have one more issue. I cannot view the "nvarchar" column from SQL Server 2005 when I try to see the description of any table from Oracle. I have done the same type of setting with SQL Server 2000 and there are no issues.

Please help.

Connect To Oracle 64 Bit using SQL Server 2005 64 Bit

I'm using SQL Server 2005 64 Bit and having a problem

when I try to make a linked server to Oracle 10G 64 Bit (installed on the same

server) from management studio. I'm using "Oracle Provider for OLE DB" to

connect to Oracle 64 Bit database because it is the only provider but from what

I've read in Books Online that the provider doesn't support for Oracle 64 Bit.

When I’m using SSAS 2005 and try to connect to Oracle 64

Bit database, an error message appear when I’m using “Microsoft OLE DB Provider

for Oracle”:

Test connection

failed because of an error in initializing provider. Oracle client and

networking components were not found. These components are supplied by Oracle

Corporation and are part of the Oracle Version 7.3.3 or later client software

installation.

Provider is

unable to function until these components are

installed.

And also when I’m using “OracleClient Data Provider”, an

error message appear:

Test connection

failed because of an error in initializing provider. Attempt to load Oracle

client libraries threw BadImageFormatException. This problem will occur when

running in 64 bit mode with the 32 bit Oracle client components

installed.

Can you please tell me how to fix it ? Thanks in

advance.

Have you installed Oracle client? .NET Oracle provider wouldnt work without it.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I already installed the Oracle Client 10G version 2.0 (x64), but Still I can not find the solution. Help Please..|||

Try contacting customer support and report your problem.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I have the exact same issue. Any update would be appreciated.|||Ditto... so, I'm only interested in the SQLserver linked-server connection via "native" Oracle connection|||

I remember similar problem came up in the past.

Actually it hasnt affected Analysis Server, it only appeared in the BI Dev Studio running on 64bit machine. Try building your project on 32bit machine and deploy to 64bit Analysis Server. See if you get any errors in this case.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I had the same problem. I worked with the low-level Oracle support folks to no avail. I opened a ticket with Microsoft Dev support and, though they did significantly more investigation they were not able to solve the issue either. At this point, I am in a holding pattern for 64bit until this issue can be resolved. If anyone else has had even partial luck, it would be great to hear what you have tried.

|||

Hi,

We've got the same problem with SQL Server 2005 64-bit SP1. The MS hotfix installation brought us to Build 2153. Oracle 64-bit client software and 10.2.0.2 update has been installed at this server.

It's same parameter you have to set as it is within SQL Server 2000. But now it's within the provider settings.

Start MS SQL Server Management Studio. Go to "Server Objects/Linked Servers/Providers". Choose the properties of OraOLEDB.oracle and check "Allow in process".

My query is running now.

Best Regards,

Uli

|||Dear All,

I have found the solution for linked server to connect to Oracle 64 bit server:

If you want to setup a linked server from SQL 2005 64 bit to Oracle 10g 64 bit, you need to use the "OraOleDb.Oracle" which you can get from Oracle.

Instructions to Setup the Linked Server Connection
=========================================
- Open SQL Server Management Studio by going to START -- ALL PROGRAMS -- Microsoft SQL Server 2005

- Provide the Server Name, Authentication, Username and Password to connect to your
SQL Server at the dialog box. This will authenticate you and log you into
Management Studio.

- Expand the Server Objects folder by clicking on the + sign

- Now, expand the Linked Servers folder and you will see Providers folder. Expand
the Providers folder. You will see Oracle Provider for OLEDB. Right click on it and
go to Properties. Check the check boxes for "Dynamic Parameters" and "Allow
InProcess". Click OK and the dialogue box will close.

- Next, right click on the Linked Servers folder and choose New Linked Server

- In the New Linked Server Properties dialogue box, do the following:
-- Type in a Linked Server Name, for this example, we will call it 'Tiger'
-- Under Server Type, select Other Data Sources - for the provider name, select
Oracle Provider for OLEDB
-- In the Data Source field, type in the Oracle Service name (SQL Net Alias)
-- In the Product Name field, type in "Oracle"
-- Leave Provider String blank.

- Now, click on the "Security option" on the left pane:
-- Select the radio button "Be made using this security context" at the bottom
of the box.
-- Type in the User ID (your Oracle login), ie. Scott in the "Remote login"
field and your Password, ie. Tiger in the "With Password" field.

- Go to the "Server Options" tab in the Linked Server Properties:
-- While leaving the default selections selected, also click on "Collation
Compatible". Select "true" from the drop down button.
-- Click OK. This will create your Linked Server named Tiger.

For SSAS 2005 64 bit, I've tried Edward's suggestion that I develop the project in 32 bit server and deploy it to 64 bit server but it doesn't work and the same error still appear because I'm using "Oracle Provider for OLE DB" and SSAS 64 bit doesn't have that provider. I've installed Service Pack 1 and the Hotfix but it doesn't work also. I hope this problem will be solved with Service Pack 2. But is there anyone who knows how to fix this ?

Thanks in advance.|||

Thanks!

I have done that and it works!

|||

---

Thanks

The connection is working for me. But I have one more issue. I cannot view the "nvarchar" column from SQL Server 2005 when I try to see the description of any table from Oracle. I have done the same type of setting with SQL Server 2000 and there are no issues.

Please help.

Connect To Oracle 10g 64 Bit using SQL Server Integration Services 2005 64 Bit

I am trying to create a oracle 10g connection in SSIS using connection manager and i am getting the following error,

Test Connection failed because of an error in initializing provider. Oracle client and networking

components were not found. These components are supplied by Oracle corporation and are part of the

Oracle version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.

Can someone please help.

Thanks.

If your operating System and VS2005 are 32bits then you need to download and install the Oracle 10g client for Windows 32bits, when I last downloaded it was more than 1gig install it and create your connection to the database make sure your TNS ORA works before adding SSIS connection to Oracle. The client tools are free but you need an Oracle Technet account which you can create for free. The Developer is also free it let you see Oracle tables and databases. If you are in an employer's network I find connecting with Named Pipes is easier than TCP/IP. One more thing watch the installer. Hope this helps.

http://www.oracle.com/technology/software/index.html

I forgot to add you must compile your code with AnyCPU.

|||

Thanks for your reply Caddre..

But.. i have MS 2003 64 bit OS.. i have installed oracle 10g 64 bit server.. i can successfully connect thru sqlplus.. no problem...

I am missing something on SQL 2005 and i am not sure what it is...

|||

So you are saying you have installed Oracle 10g 64bits client tools in the box? I know connecting through SQL plus does not solve development connection issue you need the client tools in your box this time 64bits. Another thing you are not missing anything but I think VS2005 unless you buy 64bits full version is 32bits that is not all your setup is 64bits, so you need to either make it all 64bits or develop in 32bits and deploy in 64bits because I read there are issues known issues.

|||

yes - as far as i know all are 64bit

I cannot connect thru both providers - OLEDB and Net Oracle client

|||

Try this recent Microsoft support article steps, I will still look for more info.

http://support.microsoft.com/kb/255084

|||

Hi Caddre,

Either you can have a look at summarized article here. or You may look for "Getting SSIS Packages To Run On A 64 Bit Box" in Sutha Thiru's Blog .

Thanks

Subhash Subramanyam

|||

thanks subash.. awesome..

those articles helped me to fix the issue...

appreciate yours and caddre help on this..

|||I am glad to see your problem is resolved and Subhash thanks for the articles much appreciated.

Confusion on Cube Design

Hello everyone. I'm having a bit of trouble getting my head around designing a new cube and I'm hoping this forum can help. I have a database which contains the following simplified structure:

tbl_Panelist:

panelist_id

tbl_Question:

question_id

tbl_Answer:

answer_id

tbl_Result_Set:

panelist_id

question_id

answer_id

I'm trying to design a cube which will allow analyzing of the counts of how many panelists answered each question by each answer. For example if Q1 has possible answers of A,B,C and Q2 has possible answers of X,Y, I'd like to be able to browse the cube and see

X Y

A 10 5

B 3, 2

indicating that 10 people answered Q1 with A and Q2 with X. My current thinking has been to create a view for each question and create a dimension off of that. Is this the correct method in a case like this? Any help would be greatly appreciated. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Hello Alex,

I was able to create an AS solution for your case simply by using the cube wizard. Basically you create 3 dimensions - Panelists, Questions, Answers bound to the respective tables and tell the cube wizard that your fact table is ResultSet, while including the existing dimensions. The resulting cube will provide additional analysis by Panelists, if needed.

If you write me at this address: andrewgaATnetzeroDOTcom i will send you a zip file with the AS solution, which you can deploy onto your server.

Andrew

|||

Thank you for responding. That is exactly what I attempted initially. However, I could not have different Questions act as different dimensions through that technique. I should say that at this point, due to the views, I am not having problems creating the dimensions. My current problem lies with attempting to create measures that get "hit" by every dimension.

To make this even more complicated, a single person can answer some of the questions with multiple answers. Thus, I need to have two different measures, one for a raw count of the answers, and the other with a distinct count of each panelist's answers.

Finally, I should also mention that I am using SQL Server 2005 and SSAS 2005 to do this project. Thanks again for any help that could be provided.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Hello Alex,

> distinct count of each panelist's answers.

Could you please clarify? When you browse this measure and panelists dimension, do you want to see how many *questions* each panelist answered with at least one answer? Also, do you need it as a measure, which should also behave nice when user browses not panelists dimension, or all you need is some MDX query, which would fetch this information (calc measure within one MDX query)?

I am asking because in my test i created a Count measure bound to the row of the fact table. When i browse the panelists dimension and that measure i do get the numbers of answers provided by each panelist. I suppose you gave it "raw answers" name.

Andrew

|||

I'm not exactly sure of the correct terminology here, so perhaps a continuation of my first example will explain what I'm looking for. Using the same table structure mentioned previously, suppose three panelists, p1, p2, and p3. We'll also suppose two questions, q1 and q2. Finally, q1 has possible answers A-E and q2 has possible answers W-Z. Here are the responses of each:

P1 A, C, D, W, Y, Z

P2 A, B, W, Z

P3 A, G, X, Z

Using this data, I would like to be able to build a pivottable where I can have both Q1 and Q2 as seperate dimensions. Keep in mind, in my actual project, there are closer to 40 questions, rather than just 2. For now I'd just like to focus on one measure as there are other factors involved in how the questions are answered. I would expect the results of this pivottable to looks something like:

W X Y Z

A 2 1 1 3

B 1 0 0 1

C 1 0 1 1

D 1 0 1 1

E 0 0 0 0

The trouble I seem to be running into is that when I try and examine dimesions with a granularity based on the answerID, I am getting no "hits". When there is a one to one relationship between the answer and the panelist, I am able to have a dimension with a granularity of the panelistID. In this case I get the results exactly as expected. I can even examine a granularity of panelist against a granularity of answer and get the result I expect. It's the answer to answer where I am currently having problems. Thanks again for your help.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Just to move this along a bit more, the crux of the problem that I am having is that for questions (dimensions) that allow multiple answers by the same person I cannot browse those two dimensions in the pivottable. I have now changed their granularity to be a collection of the answerID and the panelistID. While this seems to be a step closer, I still cannot get "hits" off of them. If this were a SQL query, I would just do an intersect on the results of selects of the panelist per question query. Once again, any help would be greatly appreciated. I'd be happy to further clarify what I've done if any of this is unclear. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Hello Alex,

I have just sent you my version of the solution but that one was based on the first post.

Your second post needs clarifications. In your matrix of the results, which you would like to obtain, let's discuss the left-top cell.

W

A 2

From your description, A and W are the instances of answers. Basically you have put the same dimension "Answers" on columns and rows axes. This can't be.

Anyway, how the value of 2 was computed? From your description the only correlation i can find is "the number of panelists, who gave both answers".

|||

Hello Andrew. Thank you for taking the time to help me. I will attempt to clear this up a bit. A and W are exactly what I need to create a pivottable on as they represent the results of two different questions. I have attempted to overcome this through the use of views containing the results of each question. So, to continue with the example above, I have in my database and data source view:

View_Q1 - panelist_ID, question_ID, answer_ID where question_ID = Q1

View_Q2 - panelist_ID, question_ID, answer_ID where question_ID = Q2

Keep in mind I'm simplifying this as of course you'd want the friendly names and some other information in addition to the ID's for actually building the dimensions. I have then created a dimension from each view table and used my master results table as the Measures table.

This all works fine when comparing questions with a 1-1 (one to one) panelist to answer relationship and even a single 1-M (one to many) question compared to a 1-1 table. My problem comes from comparing two 1-M questions. I've set the granularity in these cases to be the collection of answer and panelist. Perhaps using views is completely the wrong approach, but it certainly seems like I'm pretty close. The example above is of two 1-M questions.

Finally, to answer your second question, 2 is the count of panelists who answered both A and W. In this example that would actually be panelists P1 and P2. I'm only concerned with the count of panelists, and not with actually identifiying the panelists in any way. Does that explain what I am trying to accomplish or is there still something else that I can clear up? Once again, thank you for your help.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Hello Alex,

It might take some time till i will be able to think how to resolve your problem by means of OLAP, but having read your goal i wanted to ask some questions if you were really determined to solve it with OLAP.

The model of the cube should be good enough to resolve not just one report. Although it is also OK to build a cube for the sake of one report, it does not seem people do that.

In your case, what would be the value of your measure for the tuple (AnswersDim1.All, AnswersDim2.All)? Is it the number of panelists answered all questions?

What would be the values for the tuple (AnswersDim1.A, AnswersDim2.All) and (AnswersDim1.All, AnswersDim2.A)?

Suppose you make both dimensions non-aggregatable. You would not need to think about the 2 questions above, but would need to think about the default members for the 2 dimensions. Are you planning to run MDX queries like:

select [measures].yourmeasure on 0,

AnswersDim1.members on 1

from Cube

Notice, that this query would need to fetch tuples like (AnswersDim1.currentmember, AnswersDim2.defaultmember).

Being not advanced in OLAP modeling personally, if my goal was to produce just that matrix report you described, i would have been done in one day by writing a C++ or C# program, which would open forward-only row-set for a view joining your tables and building in-memory structures based on STL (C++) or Collections.Generics (C#), calculating your goal, writing the result as a SQL table and binding the report to that already calculated table.

Of course, i would think more if the number of panelists were huge. I suppose the number of questions and possible answers is not big, because making the report the way you described would make it unusable.

Andrew

|||

Unfortuantely, I fear that I must use OLAP for this project. The business requirements are that analysists must be able to view the responses to any combination of questions in a pivottable in Excel. To give you an idea of exactly how much data we're talking about here, there are currently about 60 questions with about 2-15 answers each. Currently there are a few thousand panelists, but this is expected to eventually reach into the hundreds of thousands. The database structure was designed to allow for this expected increase in data and for the easy ability to change questions and answers as time goes on.

You are correct in noticing that my aggregations are fairly meaningless in this project. There are some questions which have groupings of answers, but for the most part, each question only has one level in its heirarchy. Perhaps I can turn off aggregations in these cases if that will help.

To answer your questions specifically, Q1Dim.All,Q2Dim.All (I assume that's what you meant) would be 15 which represents the total number of responses to Q1 + the total number of responses to Q2. Had these both been 1-1 questions, that total would represent the union of panelists who answered each of the questions. Q1Dim.A, Q2Dim.All would be7 and Q2Dim.A, Q1Dim.All would be 5.

As I said at the outset, this is my first project using Analysis Server so it would not be surprising if I am going about this the wrong way or if I have missed some fundamental underlying concept. I was not expecting to have to write any MDX queries in this phase. What role does the defaultmember property play here?

Finally, as best as I can tell the crux of my problem may rest on being able to create a measure which will find results within a dimension using the collection of answerid and panelistid but find results between dimensions just using the panelistid. Does that sound correct to you based on your understanding of what I am trying to accomplish? Again, thanks for your time and interest on my behalf.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

I think I've moved forward a bit on this problem. As I'm not an expert on MDX, I've written this in SQL code. Hopefully someone out there can help me write an MDX based measure which will be the equivalent:

with a1 as (select distpanelistid from dbo.tbl_Survey_Results where surveyanswerid = 40 intersect select distpanelistid from dbo.tbl_Survey_Results where surveyanswerid = 491)

select count(distpanelistid) from a1

In this case I'm specifically getting the count for answers 40 and 491. I need the MDX to be of whatever answers I am currently browsing in the pivottable. Any MDX experts out there know this one?

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||Yet another person raised similar task (topic: Need help designing the Cube). I did it in MSAccess, while i see that you made it with SQL. I also provided some thoughts why i failed to do it with OLAP.|||

Ok, after much effort and with some amazing help from Matt Burr with the Analysis Server group at Microsoft, I have a solution to this problem. I am going to quote from the wrap up email provided by Matt. If anyone has any questions or would like a copy of the sample cube and db which was created for this, I would be happy to provide them. Thanks to Andrew Garbuzov for his help on this issue as well. From Matt Burr:

Resolution

In the end, the solution involved creating views against your source “fact” table to represent individual query dimensions. Then, we created the different query dimensions from these views. We also created a Panelist dimension that served two purposes: (1) it contains the data that we actually will count and (2) it serves to help join together the various question dimensions so that navigating one dimension effectively navigates the other dimensions.

Next, we created measures based on each of the question dimensions; these simply counted the unique number of participants that provided any given answer to a question, but their primary value is that they serve as a sort of “hook” that we can use to relate the various question dimensions back to the Panelist dimension, and from there out to the other question dimensions.

We then used the Dimension Usage tab to ensure that the Panelist dimension related to the measures that we had created from the question dimensions, and thus related to the question dimensions (so that navigating or “filtering” a given question dimension subsequently navigated/filtered the Panelist dimension), and we also created many-to-many relationships from each of the question dimensions to each of the other question dimensions (their relationship with the Panelist dimension facilitated this), so that navigating/filtering any one of the question dimensions consequently navigated/filtered all of the other question dimensions, based on a shared set of panelists that provided certain answers to both of the questions.

Finally, we created a calculated member that counts the distinct number of participants that exist in the Panelist dimension, which will have been navigated/filtered by your choice of question dimensions. This calculated member tells you what you wished to know: the number of panelists that answered x for one question AND y for another.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Confusion on Cube Design

Hello everyone. I'm having a bit of trouble getting my head around designing a new cube and I'm hoping this forum can help. I have a database which contains the following simplified structure:

tbl_Panelist:

panelist_id

tbl_Question:

question_id

tbl_Answer:

answer_id

tbl_Result_Set:

panelist_id

question_id

answer_id

I'm trying to design a cube which will allow analyzing of the counts of how many panelists answered each question by each answer. For example if Q1 has possible answers of A,B,C and Q2 has possible answers of X,Y, I'd like to be able to browse the cube and see

X Y

A 10 5

B 3, 2

indicating that 10 people answered Q1 with A and Q2 with X. My current thinking has been to create a view for each question and create a dimension off of that. Is this the correct method in a case like this? Any help would be greatly appreciated. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Hello Alex,

I was able to create an AS solution for your case simply by using the cube wizard. Basically you create 3 dimensions - Panelists, Questions, Answers bound to the respective tables and tell the cube wizard that your fact table is ResultSet, while including the existing dimensions. The resulting cube will provide additional analysis by Panelists, if needed.

If you write me at this address: andrewgaATnetzeroDOTcom i will send you a zip file with the AS solution, which you can deploy onto your server.

Andrew

|||

Thank you for responding. That is exactly what I attempted initially. However, I could not have different Questions act as different dimensions through that technique. I should say that at this point, due to the views, I am not having problems creating the dimensions. My current problem lies with attempting to create measures that get "hit" by every dimension.

To make this even more complicated, a single person can answer some of the questions with multiple answers. Thus, I need to have two different measures, one for a raw count of the answers, and the other with a distinct count of each panelist's answers.

Finally, I should also mention that I am using SQL Server 2005 and SSAS 2005 to do this project. Thanks again for any help that could be provided.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Hello Alex,

> distinct count of each panelist's answers.

Could you please clarify? When you browse this measure and panelists dimension, do you want to see how many *questions* each panelist answered with at least one answer? Also, do you need it as a measure, which should also behave nice when user browses not panelists dimension, or all you need is some MDX query, which would fetch this information (calc measure within one MDX query)?

I am asking because in my test i created a Count measure bound to the row of the fact table. When i browse the panelists dimension and that measure i do get the numbers of answers provided by each panelist. I suppose you gave it "raw answers" name.

Andrew

|||

I'm not exactly sure of the correct terminology here, so perhaps a continuation of my first example will explain what I'm looking for. Using the same table structure mentioned previously, suppose three panelists, p1, p2, and p3. We'll also suppose two questions, q1 and q2. Finally, q1 has possible answers A-E and q2 has possible answers W-Z. Here are the responses of each:

P1 A, C, D, W, Y, Z

P2 A, B, W, Z

P3 A, G, X, Z

Using this data, I would like to be able to build a pivottable where I can have both Q1 and Q2 as seperate dimensions. Keep in mind, in my actual project, there are closer to 40 questions, rather than just 2. For now I'd just like to focus on one measure as there are other factors involved in how the questions are answered. I would expect the results of this pivottable to looks something like:

W X Y Z

A 2 1 1 3

B 1 0 0 1

C 1 0 1 1

D 1 0 1 1

E 0 0 0 0

The trouble I seem to be running into is that when I try and examine dimesions with a granularity based on the answerID, I am getting no "hits". When there is a one to one relationship between the answer and the panelist, I am able to have a dimension with a granularity of the panelistID. In this case I get the results exactly as expected. I can even examine a granularity of panelist against a granularity of answer and get the result I expect. It's the answer to answer where I am currently having problems. Thanks again for your help.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Just to move this along a bit more, the crux of the problem that I am having is that for questions (dimensions) that allow multiple answers by the same person I cannot browse those two dimensions in the pivottable. I have now changed their granularity to be a collection of the answerID and the panelistID. While this seems to be a step closer, I still cannot get "hits" off of them. If this were a SQL query, I would just do an intersect on the results of selects of the panelist per question query. Once again, any help would be greatly appreciated. I'd be happy to further clarify what I've done if any of this is unclear. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Hello Alex,

I have just sent you my version of the solution but that one was based on the first post.

Your second post needs clarifications. In your matrix of the results, which you would like to obtain, let's discuss the left-top cell.

W

A 2

From your description, A and W are the instances of answers. Basically you have put the same dimension "Answers" on columns and rows axes. This can't be.

Anyway, how the value of 2 was computed? From your description the only correlation i can find is "the number of panelists, who gave both answers".

|||

Hello Andrew. Thank you for taking the time to help me. I will attempt to clear this up a bit. A and W are exactly what I need to create a pivottable on as they represent the results of two different questions. I have attempted to overcome this through the use of views containing the results of each question. So, to continue with the example above, I have in my database and data source view:

View_Q1 - panelist_ID, question_ID, answer_ID where question_ID = Q1

View_Q2 - panelist_ID, question_ID, answer_ID where question_ID = Q2

Keep in mind I'm simplifying this as of course you'd want the friendly names and some other information in addition to the ID's for actually building the dimensions. I have then created a dimension from each view table and used my master results table as the Measures table.

This all works fine when comparing questions with a 1-1 (one to one) panelist to answer relationship and even a single 1-M (one to many) question compared to a 1-1 table. My problem comes from comparing two 1-M questions. I've set the granularity in these cases to be the collection of answer and panelist. Perhaps using views is completely the wrong approach, but it certainly seems like I'm pretty close. The example above is of two 1-M questions.

Finally, to answer your second question, 2 is the count of panelists who answered both A and W. In this example that would actually be panelists P1 and P2. I'm only concerned with the count of panelists, and not with actually identifiying the panelists in any way. Does that explain what I am trying to accomplish or is there still something else that I can clear up? Once again, thank you for your help.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

Hello Alex,

It might take some time till i will be able to think how to resolve your problem by means of OLAP, but having read your goal i wanted to ask some questions if you were really determined to solve it with OLAP.

The model of the cube should be good enough to resolve not just one report. Although it is also OK to build a cube for the sake of one report, it does not seem people do that.

In your case, what would be the value of your measure for the tuple (AnswersDim1.All, AnswersDim2.All)? Is it the number of panelists answered all questions?

What would be the values for the tuple (AnswersDim1.A, AnswersDim2.All) and (AnswersDim1.All, AnswersDim2.A)?

Suppose you make both dimensions non-aggregatable. You would not need to think about the 2 questions above, but would need to think about the default members for the 2 dimensions. Are you planning to run MDX queries like:

select [measures].yourmeasure on 0,

AnswersDim1.members on 1

from Cube

Notice, that this query would need to fetch tuples like (AnswersDim1.currentmember, AnswersDim2.defaultmember).

Being not advanced in OLAP modeling personally, if my goal was to produce just that matrix report you described, i would have been done in one day by writing a C++ or C# program, which would open forward-only row-set for a view joining your tables and building in-memory structures based on STL (C++) or Collections.Generics (C#), calculating your goal, writing the result as a SQL table and binding the report to that already calculated table.

Of course, i would think more if the number of panelists were huge. I suppose the number of questions and possible answers is not big, because making the report the way you described would make it unusable.

Andrew

|||

Unfortuantely, I fear that I must use OLAP for this project. The business requirements are that analysists must be able to view the responses to any combination of questions in a pivottable in Excel. To give you an idea of exactly how much data we're talking about here, there are currently about 60 questions with about 2-15 answers each. Currently there are a few thousand panelists, but this is expected to eventually reach into the hundreds of thousands. The database structure was designed to allow for this expected increase in data and for the easy ability to change questions and answers as time goes on.

You are correct in noticing that my aggregations are fairly meaningless in this project. There are some questions which have groupings of answers, but for the most part, each question only has one level in its heirarchy. Perhaps I can turn off aggregations in these cases if that will help.

To answer your questions specifically, Q1Dim.All,Q2Dim.All (I assume that's what you meant) would be 15 which represents the total number of responses to Q1 + the total number of responses to Q2. Had these both been 1-1 questions, that total would represent the union of panelists who answered each of the questions. Q1Dim.A, Q2Dim.All would be7 and Q2Dim.A, Q1Dim.All would be 5.

As I said at the outset, this is my first project using Analysis Server so it would not be surprising if I am going about this the wrong way or if I have missed some fundamental underlying concept. I was not expecting to have to write any MDX queries in this phase. What role does the defaultmember property play here?

Finally, as best as I can tell the crux of my problem may rest on being able to create a measure which will find results within a dimension using the collection of answerid and panelistid but find results between dimensions just using the panelistid. Does that sound correct to you based on your understanding of what I am trying to accomplish? Again, thanks for your time and interest on my behalf.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||

I think I've moved forward a bit on this problem. As I'm not an expert on MDX, I've written this in SQL code. Hopefully someone out there can help me write an MDX based measure which will be the equivalent:

with a1 as (select distpanelistid from dbo.tbl_Survey_Results where surveyanswerid = 40 intersect select distpanelistid from dbo.tbl_Survey_Results where surveyanswerid = 491)

select count(distpanelistid) from a1

In this case I'm specifically getting the count for answers 40 and 491. I need the MDX to be of whatever answers I am currently browsing in the pivottable. Any MDX experts out there know this one?

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||Yet another person raised similar task (topic: Need help designing the Cube). I did it in MSAccess, while i see that you made it with SQL. I also provided some thoughts why i failed to do it with OLAP.|||

Ok, after much effort and with some amazing help from Matt Burr with the Analysis Server group at Microsoft, I have a solution to this problem. I am going to quote from the wrap up email provided by Matt. If anyone has any questions or would like a copy of the sample cube and db which was created for this, I would be happy to provide them. Thanks to Andrew Garbuzov for his help on this issue as well. From Matt Burr:

Resolution

In the end, the solution involved creating views against your source “fact” table to represent individual query dimensions. Then, we created the different query dimensions from these views. We also created a Panelist dimension that served two purposes: (1) it contains the data that we actually will count and (2) it serves to help join together the various question dimensions so that navigating one dimension effectively navigates the other dimensions.

Next, we created measures based on each of the question dimensions; these simply counted the unique number of participants that provided any given answer to a question, but their primary value is that they serve as a sort of “hook” that we can use to relate the various question dimensions back to the Panelist dimension, and from there out to the other question dimensions.

We then used the Dimension Usage tab to ensure that the Panelist dimension related to the measures that we had created from the question dimensions, and thus related to the question dimensions (so that navigating or “filtering” a given question dimension subsequently navigated/filtered the Panelist dimension), and we also created many-to-many relationships from each of the question dimensions to each of the other question dimensions (their relationship with the Panelist dimension facilitated this), so that navigating/filtering any one of the question dimensions consequently navigated/filtered all of the other question dimensions, based on a shared set of panelists that provided certain answers to both of the questions.

Finally, we created a calculated member that counts the distinct number of participants that exist in the Panelist dimension, which will have been navigated/filtered by your choice of question dimensions. This calculated member tells you what you wished to know: the number of panelists that answered x for one question AND y for another.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Confused.

Still little bit confused regarding Statistics updates and rebuilding
indexes.... can any one give me one basic difference.
I remember you, you asked few days ago:
http://groups.google.de/group/micros...0354ae6af6fdbc
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
news:%23MNB6NZVFHA.2272@.TK2MSFTNGP15.phx.gbl...
> Still little bit confused regarding Statistics updates and rebuilding
> indexes.... can any one give me one basic difference.
>
|||On Tue, 10 May 2005 15:07:43 -0400, Joh wrote:

>Still little bit confused regarding Statistics updates and rebuilding
>indexes.... can any one give me one basic difference.
>
Hi Joh,
Take a text book, or an SQL manual or something like that. The last
20-50 pages will probably contain an index, that you can use to find
that deadlocks are discussed on pages 574-577 and 893, and that the
SELECT statement is discussed on pages 34-108, 145-160, 254 and 664.
Tear out this index. Then, take a stack of blank paper, scan each page
of the text book and recreate the index. This activity is somewhat
equivalent to rebuilding an index.
Now, take a quick scan over the index. You might note that there are
less (or maybe more?) words in the index that start with 'A' then with
'B'. You'll probably note that there are almost no words with 'X', but
quite a lot starting with 'Q'. Assuming that you don't forget this
information, but use it next time you need to find an entry to locate
the correct page in the index quicker, then you have just performed the
equivalent of updating statistics.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Really appreciated Hugo... thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rd32819sm6p33mfnhk6l62eb03hc8pun25@.4ax.com...
> On Tue, 10 May 2005 15:07:43 -0400, Joh wrote:
>
> Hi Joh,
> Take a text book, or an SQL manual or something like that. The last
> 20-50 pages will probably contain an index, that you can use to find
> that deadlocks are discussed on pages 574-577 and 893, and that the
> SELECT statement is discussed on pages 34-108, 145-160, 254 and 664.
> Tear out this index. Then, take a stack of blank paper, scan each page
> of the text book and recreate the index. This activity is somewhat
> equivalent to rebuilding an index.
> Now, take a quick scan over the index. You might note that there are
> less (or maybe more?) words in the index that start with 'A' then with
> 'B'. You'll probably note that there are almost no words with 'X', but
> quite a lot starting with 'Q'. Assuming that you don't forget this
> information, but use it next time you need to find an entry to locate
> the correct page in the index quicker, then you have just performed the
> equivalent of updating statistics.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Jens... really appreciated.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OwbMTXZVFHA.3716@.TK2MSFTNGP12.phx.gbl...
> I remember you, you asked few days ago:
>
http://groups.google.de/group/micros...ts/browse_frm/
thread/17a8f88d35626ce5/4b0354ae6af6fdbc?q=Jens+S%C3%BC%C3%9Fmeyer+differe nc
e+statistics+index&rnum=1&hl=de#4b0354ae6af6fdbc
> HTH, Jens Suessmeyer.
>
> --
> http://www.sqlserver2005.de
> --
> "Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
> news:%23MNB6NZVFHA.2272@.TK2MSFTNGP15.phx.gbl...
>

Confused over query cost of execution plan

I am a bit with the query cost information displayed by the
execution plan.
First request:
I am using a function to get the required data. The implementation is quite
complex so I am not posting it now. I hope it suffices to mention that the
function has a primary key defined on two of the columns returned. Now my
query to get the data from the function would take the form:
select * -- normally I have a selected list of columns here.
from fn_name (param1, param2, param3)
Another query which uses the primary key would be in the form:
select *
from fn_name (param1, param2, param3)
where pkcolumn > 0 -- the primary key column is made up of positive
integer values.
When I display the execution plan, the "Query cost relative to the batch"
values are 85 and 15 (rounded) for each query respectively. The first is
doing a Clustered scan and the second a Clustered s. However, when I
compare the run times of each query they both fare almost the same (first
averages 105 ms and second 100 ms).
My understanding was that if both queries took say 200 ms to run, then the
first query would account for 170 ms (85% of the batch) and the second one
would take 30 ms. Where am I mistaken?
Second request:
I execute a stored procedure. The execution plan shows 100 queries. The last
two queries are for two select statements from the inserted and deleted
tables from the trigger of a table being updated. No other actions are taken
by the trigger (because conditions not satisfied). However, the last two
trigger queries account for 84% of the batch (42% each). Running the
procedure when trigger is disabled or enabled does not show any significant
difference in run time (around 2 secs). What lesson have I missed?
Thanks,
RajQuery cost does not just take into consideration the time it takes to run a
query. It also looks at the IO required, to either RAM or Disk. On a heavily
loaded system, the IO cost has a more dramatic effect on performance than
CPU or time cost as the queries might wait for IO operation that are queued.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Khooseeraj Moloye" <rkmoloye@.hotmail.com> wrote in message
news:%23vmkYuYXFHA.320@.TK2MSFTNGP10.phx.gbl...
>I am a bit with the query cost information displayed by the
>execution plan.
> First request:
> I am using a function to get the required data. The implementation is
> quite complex so I am not posting it now. I hope it suffices to mention
> that the function has a primary key defined on two of the columns
> returned. Now my query to get the data from the function would take the
> form:
> select * -- normally I have a selected list of columns here.
> from fn_name (param1, param2, param3)
> Another query which uses the primary key would be in the form:
> select *
> from fn_name (param1, param2, param3)
> where pkcolumn > 0 -- the primary key column is made up of positive
> integer values.
> When I display the execution plan, the "Query cost relative to the batch"
> values are 85 and 15 (rounded) for each query respectively. The first is
> doing a Clustered scan and the second a Clustered s. However, when I
> compare the run times of each query they both fare almost the same (first
> averages 105 ms and second 100 ms).
> My understanding was that if both queries took say 200 ms to run, then the
> first query would account for 170 ms (85% of the batch) and the second one
> would take 30 ms. Where am I mistaken?
> Second request:
> I execute a stored procedure. The execution plan shows 100 queries. The
> last two queries are for two select statements from the inserted and
> deleted tables from the trigger of a table being updated. No other actions
> are taken by the trigger (because conditions not satisfied). However, the
> last two trigger queries account for 84% of the batch (42% each). Running
> the procedure when trigger is disabled or enabled does not show any
> significant difference in run time (around 2 secs). What lesson have I
> missed?
> Thanks,
> Raj
>|||Mike,
Thanks for the response. However, I am still left !
It all ought got to go down to time finally, in my humble opinion. That's
the essence of the query optimiser - to save time, isn't it? If it is not,
what IS the query cost really; some sort of value meaningly only to the
optimiser? How to explain that 2 simple select statements in a trigger
outweigh the other 98 complex queries by 84:16? That's where I am perplexed!
There ought to be a reasonable answer for this.
Sorry to have put in more questions here... but some clearer answers are
most welcome.
Cheers,
Raj|||Unfortunately, the cost for the multi-statement table-valued UDF-s is
not taken into account in the execution plan for the calling query.
Instead, it gives you only the cost of executing the query as if the
result of the UDF was a table (with no cost of its own).
If your UDF is performing only a single statement, you may want to
change it to an inline table-valued UDF, which is taken into acount in
the execution plan of the calling query (as if the UDF was a view).
If your UDF is performing more statements and you want to analyze the
execution plan for those statements, you must copy the statements in
Query Analyzer, replace the parameters with variables and analyse those
statements only. If needed, you may want to add a "SELECT * FROM
@.result WHERE ..." at the end, to comparate the cost of those
statements with the cost that was shown in for the calling query.
On the other hand, the query cost is not directly linked with the time
(as Mike already said), although it should be somehow an rough
estimation of time. It is interesting to read the following KB Article:
http://support.microsoft.com/kb/q285996
Razvan|||Thanks for the info, Razvan. The link was interesting reading, although did
not relate to my post.
Given that the udf is table-valued type, running the same query with same
output, only differing on index usage still yields a high cost ratio,
whereas both queries actually took the same time to run. In fact the same
can be observed for a table as well. Maybe I am doing tests with relatively
small datasets (2-5000 records) so there is not much difference. I think the
impact would be huge when doing the same exercise with much much larger
datasets (the execution plan would still remain the same?).
Some of my understanding now about the query cost is that it is not related
to time taken to actually run the query at all (although in finality it has
an impact on time to run). It rathers determines the best way to run the
query with regards to indexes and all. It also seems to me the optimiser is
built around some assumptions (like s is good, scan is bad, generally)
and there are standard cost values associated, such that a clustered index
s has a better cost associated than a clustered index scan.
Thanks again,
Raj|||the cost is not necessarily correlated to Time to execute the query.
Cost is a relative measurement of general "Cost" to execute the query.
this includes the usage of system resources (CPU, RAM, IO, etc).
now, Generally speaking the higher the cost, the longer the execution time.
But you'll not see an exact match to the milliseconds required to execute
the query.
The cost is a better measurement than time as you move from system to
system.
Depending on hardware differences, etc, running a query on Machine A at 2 ms
may lead you to believe that it performs well. Then you move the same query
to a different machine (With more data, etc) and the thing Tanks.
If you focus on SubTree Cost in query plan, you can get more consistent data
to base your decisions on regarding the queries overall performance.
in a nutshell.
If the Subtree cost is high, regardless of how FAST the query is returning,
the thing is likely not optimized.
hope this makes some sense. If you want more info, post back.
GAJ|||GAJ,
Thanks for the very instructive lessons. Yes, I totally agree with what you
explained. I do not deny the merits of cost measurements in comparison to
time measurements (I admit I am still learning more and more on the matter
as I am digging deeper, thanks to the replies on this forum, not only to my
posts but others' as well).
My focus is not on time but rather the relative (I underline, relative)
difference in cost measurements. The ratio seems too big. I only took the
time measurements as a comparison of the two queries. Remember I am not
talking about running the same query on two different machines; it is rather
two slightly different queries returning the same dataset run on the same
machine. I think no matter which machine those two queries were put on, the
relative difference (the ratio) of the cost measurements would not change
much (under correction from the experts!).
And the ratio is much bigger when it comes to the trigger query.
Unexplainable? My next "struggle" then will be to understand how the
optimiser works and renders it's execution plans. That will surely shed more
light.
I hope not to be seen as being rude asking to read my original post again.
Thanks.
Raj|||Raj,
The optimizer must make many guesses about query cost.
One place you can look to understand why the estimates don't
match reality is at the estimated rowcount number in the popup
information from the graphical plan operator. You can compare
these with the actual counts, which will show (along with the
estimated count and estimated, but not actual, costs) if you choose
Show Execution Plan in Query Analyzer, then run the query.
For the trigger query, the optimizer probably can only guess how
many rows will be in the inserted and deleted tables - if the estimated
rowcounts here or elsewhere are far from the actual values, that would
explain some of the inconsistencies.
In addition to inaccurate rowcount estimates (usually because there is
no way to be accurate without running the query), percentage estimates
will differ from reality in other situations, such as:
User-defined function calls other than in-line table-valued
functions (scalar UDF calls are not considered in the cost).
Procedures with program flow logic that can't be evaluated
until runtime (the optimizer will cost out all branches,even
though some will ultimately have zero cost).
Steve Kass
Drew University
Khooseeraj Moloye wrote:

>GAJ,
>Thanks for the very instructive lessons. Yes, I totally agree with what you
>explained. I do not deny the merits of cost measurements in comparison to
>time measurements (I admit I am still learning more and more on the matter
>as I am digging deeper, thanks to the replies on this forum, not only to my
>posts but others' as well).
>My focus is not on time but rather the relative (I underline, relative)
>difference in cost measurements. The ratio seems too big. I only took the
>time measurements as a comparison of the two queries. Remember I am not
>talking about running the same query on two different machines; it is rathe
r
>two slightly different queries returning the same dataset run on the same
>machine. I think no matter which machine those two queries were put on, the
>relative difference (the ratio) of the cost measurements would not change
>much (under correction from the experts!).
>And the ratio is much bigger when it comes to the trigger query.
>Unexplainable? My next "struggle" then will be to understand how the
>optimiser works and renders it's execution plans. That will surely shed mor
e
>light.
>I hope not to be seen as being rude asking to read my original post again.
>Thanks.
>Raj
>
>|||you're not being rude at all.
GAJ

Confused about memory limits

Server is Windows 2000 SP4 Advanced Server with 4gb of RAM. "My Computer"
shows 3,669,532KB of RAM. SQL Server 2000 SP3.
I'm a bit confused as to if SQL server can use more memory or not.
In task manager, I see that sql server is using 1.7 gigs of memory (sounds
like 2gb to me).. which I'm reading is the maximum. Even though the server
properties have the minimum set at 0 and the maximum set at 38xxMB.
All the discussion says that setting "awe enabled" will allow SQL Server to
take advantage of memory on servers with more then 4gb of memory.
Well.. I don't have more then 4gb. I have 4. Can I make it use more memory?
Will setting awe enabled to 1 do this? I'm pretty sure this is one of the
causes of some recent performance issues we've been having.
Note that, I guess because it's Windows 2000 AS, I don't have /3gb or /PAE
in my boot.ini at all, I don't think I need that?
Thanks,
GregI would add /3GB but that is only if I had a memory bottleneck.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"greg" <greg@.discussions.microsoft.com> wrote in message
news:706F9484-62C7-4BDC-8AD5-F3079AA77828@.microsoft.com...
> Server is Windows 2000 SP4 Advanced Server with 4gb of RAM. "My Computer"
> shows 3,669,532KB of RAM. SQL Server 2000 SP3.
> I'm a bit confused as to if SQL server can use more memory or not.
> In task manager, I see that sql server is using 1.7 gigs of memory (sounds
> like 2gb to me).. which I'm reading is the maximum. Even though the server
> properties have the minimum set at 0 and the maximum set at 38xxMB.
> All the discussion says that setting "awe enabled" will allow SQL Server
> to
> take advantage of memory on servers with more then 4gb of memory.
> Well.. I don't have more then 4gb. I have 4. Can I make it use more
> memory?
> Will setting awe enabled to 1 do this? I'm pretty sure this is one of the
> causes of some recent performance issues we've been having.
> Note that, I guess because it's Windows 2000 AS, I don't have /3gb or /PAE
> in my boot.ini at all, I don't think I need that?
>
> Thanks,
> Greg|||If your SQL Server is Standard edition, you are capped at 2gb
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"greg" <greg@.discussions.microsoft.com> wrote in message
news:706F9484-62C7-4BDC-8AD5-F3079AA77828@.microsoft.com...
> Server is Windows 2000 SP4 Advanced Server with 4gb of RAM. "My Computer"
> shows 3,669,532KB of RAM. SQL Server 2000 SP3.
> I'm a bit confused as to if SQL server can use more memory or not.
> In task manager, I see that sql server is using 1.7 gigs of memory (sounds
> like 2gb to me).. which I'm reading is the maximum. Even though the server
> properties have the minimum set at 0 and the maximum set at 38xxMB.
> All the discussion says that setting "awe enabled" will allow SQL Server
> to
> take advantage of memory on servers with more then 4gb of memory.
> Well.. I don't have more then 4gb. I have 4. Can I make it use more
> memory?
> Will setting awe enabled to 1 do this? I'm pretty sure this is one of the
> causes of some recent performance issues we've been having.
> Note that, I guess because it's Windows 2000 AS, I don't have /3gb or /PAE
> in my boot.ini at all, I don't think I need that?
>
> Thanks,
> Greg|||What edition of SQL Server 2000 are you using. If it is Standard Edition, you
are limited to 2GB Max.
"greg" wrote:
> Server is Windows 2000 SP4 Advanced Server with 4gb of RAM. "My Computer"
> shows 3,669,532KB of RAM. SQL Server 2000 SP3.
> I'm a bit confused as to if SQL server can use more memory or not.
> In task manager, I see that sql server is using 1.7 gigs of memory (sounds
> like 2gb to me).. which I'm reading is the maximum. Even though the server
> properties have the minimum set at 0 and the maximum set at 38xxMB.
> All the discussion says that setting "awe enabled" will allow SQL Server to
> take advantage of memory on servers with more then 4gb of memory.
> Well.. I don't have more then 4gb. I have 4. Can I make it use more memory?
> Will setting awe enabled to 1 do this? I'm pretty sure this is one of the
> causes of some recent performance issues we've been having.
> Note that, I guess because it's Windows 2000 AS, I don't have /3gb or /PAE
> in my boot.ini at all, I don't think I need that?
>
> Thanks,
> Greg|||SQL Server 2000, Enterprise Edition.
Just found this option that says I should *not* be using 'awe enabled'...
damn this is confusing!
http://www.sql-server-performance.com/articles/per/awe_memory_sql2000_p1.aspx
Help?|||I don't believe this article does suggest that you disable AWE - what is
your reasoning behind this conclusion?
You'll need AWE enabled if you're wanting SQL Server to see more than a few
GB of memory.
"greg" <greg@.discussions.microsoft.com> wrote in message
news:F3795964-7DA6-46DE-8718-9D5FE1BC99B6@.microsoft.com...
> SQL Server 2000, Enterprise Edition.
> Just found this option that says I should *not* be using 'awe enabled'...
> damn this is confusing!
> http://www.sql-server-performance.com/articles/per/awe_memory_sql2000_p1.aspx
>
> Help?
>|||Added /3gb to boot.ini and rebooted this weekend, now using 2.7 gigs of
memory! Sweet.
CPU usage is up now so I guess that's good. Less time thrashing/waiting on
the disk, more time doing actual work.

Configuring SSIS in an Active\Active Cluster

OK so I am aware that the SSIS service can be clustered relatively easily but my problem is a bit more complex I believe.

I have an Active\Active\Active 3 node cluster running Windows 2003 sp1 all running SSIS packages. What I need is a solution to having an instance fail to another node and still be able to run scheduled SSIS packages through the SQL Agent.

The solution that I have come up with is to have a single network share for all three instances of SSIS running locally on the individual nodes. I have removed the ability for SSIS to look in MSDB by altering the config file and reference only the Packages share on the network for accessability. They all share the same config file on the share as well. The share structure looks something like this simplified example.

\\server1\SSIS\Configuration

\\server1\SSIS\Packages

\\server1\SSIS\Packages\Instance1\

\\server1\SSIS\Packages\Instance2\

\\server1\SSIS\Packages\Instance3\

Once I change the registry to point to the config file all three instances can access the Instance1, 2 or 3 directories.

This works ok for the jobs, and I can access the stored packages under the file system folder only if I'm remotely connected to that particular physical server hosting the service or on the console. The problem I am having though is when users or I attempt to access the SSIS Engine through SSMS from our locale machines. It seems as though SSMS is having trouble with the passthrough of the directory information. On the share I obviously have permissiosn to access the directory structure (full control to be exact) because I can get to it when I access it from SSMS on the server.

This is the error I get...

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

The storage location for the folder "File System\" cannot be accessed. (MsDtsSrvr)

DETAILS:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

Program Location:

at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItemWithQuery(IList nodes, INodeInformation source, INavigableItem sourceItem, String urnQuery, Boolean registerBuilder, Boolean registerBuiltItems)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItem(IList nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.Build(INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.GetChildren()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren()

===================================

The storage location for the folder "File System\" cannot be accessed. (MsDtsSrvr)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String sPackageFolder, String sServerName)
at Microsoft.SqlServer.Dts.SmoEnum.PackageEnum.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)

Any work around or suggestion would be appreciated.

Thanks,

Zach

Anyone have any thoughts on how to make this work?

Thanks for the look,

Zach

Configuring SQL Reporting Services for SSL

I've got an issue that is perhaps a bit different than any other
related posting on this topic.
I have successfully installed and configured SSRS for SQL 2005 and now
I'm at the step where I need to require SSL. So I opened the
Reporting Services Configuration, then selected Report Server Virtual
Directory. Then I select the checkbox Require Secure Socket Layer
(SSL) connections. At this step I am required to enter the name of
the certificate. Here is the problem: The server in on 2 seemingly
different domains and the certificate was issued to domain A, but the
server name is listed under domain B, but both domains resolve to the
same IP address. Here are the specifics:
A) The cert was named according to this domain pattern:
<server_name>.orgA.domainA.com
B) The computer name in system properties is:
<server_name>.orgB.xx.domainB.com
When I run the report server web interface, I get this error "The
request failed with HTTP status 401: Unauthorized."
So my question is: Why isn't the certificate for Domain A working?
Should I request a certificate under the domain of option B above? Is
there a configuration that is required? Note - I've modified the file
rsreportserver.config per the installation instructions.
Thanks in advance for your assistance,
-ScottOn Mar 6, 10:33 am, scottdistef...@.gmail.com wrote:
> I've got an issue that is perhaps a bit different than any other
> related posting on this topic.
> I have successfully installed and configured SSRS for SQL 2005 and now
> I'm at the step where I need to require SSL. So I opened the
> Reporting Services Configuration, then selected Report Server Virtual
> Directory. Then I select the checkbox Require Secure Socket Layer
> (SSL) connections. At this step I am required to enter the name of
> the certificate. Here is the problem: The server in on 2 seemingly
> different domains and the certificate was issued to domain A, but the
> server name is listed under domain B, but both domains resolve to the
> same IP address. Here are the specifics:
> A) The cert was named according to this domain pattern:
> <server_name>.orgA.domainA.com
> B) The computer name in system properties is:
> <server_name>.orgB.xx.domainB.com
> When I run the report server web interface, I get this error "The
> request failed with HTTP status 401: Unauthorized."
> So my question is: Why isn't the certificate for Domain A working?
> Should I request a certificate under the domain of option B above? Is
> there a configuration that is required? Note - I've modified the file
> rsreportserver.config per the installation instructions.
> Thanks in advance for your assistance,
> -Scott
Anybody thoughts?|||On Mar 6, 11:33 am, scottdistef...@.gmail.com wrote:
> I've got an issue that is perhaps a bit different than any other
> related posting on this topic.
> I have successfully installed and configured SSRS for SQL 2005 and now
> I'm at the step where I need to require SSL. So I opened the
> Reporting Services Configuration, then selected Report Server Virtual
> Directory. Then I select the checkbox Require Secure Socket Layer
> (SSL) connections. At this step I am required to enter the name of
> the certificate. Here is the problem: The server in on 2 seemingly
> different domains and the certificate was issued to domain A, but the
> server name is listed under domain B, but both domains resolve to the
> same IP address. Here are the specifics:
> A) The cert was named according to this domain pattern:
> <server_name>.orgA.domainA.com
> B) The computer name in system properties is:
> <server_name>.orgB.xx.domainB.com
> When I run the report server web interface, I get this error "The
> request failed with HTTP status 401: Unauthorized."
> So my question is: Why isn't the certificate for Domain A working?
> Should I request a certificate under the domain of option B above? Is
> there a configuration that is required? Note - I've modified the file
> rsreportserver.config per the installation instructions.
> Thanks in advance for your assistance,
> -Scott
I'm sorry to pester, but I'm running out of things to try on this
issue. Does anyone have thoughts to share on requiring SSL with SQL
2005 Reporting?

configuring reporting services (to work for SCOM2007)

Hello-
I am having a bit of an issue configuring reporting services so that I can
install the System Center Operations Manager 2007 Reporting feature.
On our SQL server we have SQL2005 SP2 installed with the operations manager
database installed and working. I installed SQL reporting services on the
server that runs Operations Manager (OM) which is required to install OM
Reporting. I am however having an issue configuring reporting services.
I go into the reporting services configuration and there are 3 ares which
have red X's, Report Manager Virtual Directory, Web Service Identity and
Database Setup.
First thing I do is set the virtual directory, I click new and it creates
the new virtual directory in the default website however the red X never
turns to a green checkmark.
Next I go to web service identity and create a new app pool called reportsvc
and click apply and get an error saying "There was an error setting the
identity for the web service" If i set it to DefaultAppPool I get the same
thing.
then create the reporting database in the sql2005 server instance which
seems to go fine untill I go to click apply then I get an error saying that
it could not apply it because a virtual directory must be created first.
If i go into inetmgr the virtual directory was created under the default
website but it does not register in reporting servcies for some reason.
Can anyone help!? Much thanks in advance!!!
/EhrenMake sure you have asp.net installed,
run %WINNT%\Microsoft.NET\Framework\%framework version%\aspnet_regiis.exe -i.
"Ehren" wrote:
> Hello-
> I am having a bit of an issue configuring reporting services so that I can
> install the System Center Operations Manager 2007 Reporting feature.
> On our SQL server we have SQL2005 SP2 installed with the operations manager
> database installed and working. I installed SQL reporting services on the
> server that runs Operations Manager (OM) which is required to install OM
> Reporting. I am however having an issue configuring reporting services.
> I go into the reporting services configuration and there are 3 ares which
> have red X's, Report Manager Virtual Directory, Web Service Identity and
> Database Setup.
> First thing I do is set the virtual directory, I click new and it creates
> the new virtual directory in the default website however the red X never
> turns to a green checkmark.
> Next I go to web service identity and create a new app pool called reportsvc
> and click apply and get an error saying "There was an error setting the
> identity for the web service" If i set it to DefaultAppPool I get the same
> thing.
> then create the reporting database in the sql2005 server instance which
> seems to go fine untill I go to click apply then I get an error saying that
> it could not apply it because a virtual directory must be created first.
> If i go into inetmgr the virtual directory was created under the default
> website but it does not register in reporting servcies for some reason.
> Can anyone help!? Much thanks in advance!!!
> /Ehren|||Hi, thanks for your suggestion. I verified that ASP.NET is installed for the
default website and is using version 2.0.50727. I tried switching to v1.xxxx
to see if that would make a difference and it did not.
I also created a new website called "reports" on port 1024 to see if that
would make a difference and then clicked the new button again and told it to
create the reports virtual directory in the reports website. It did add the
virtual directory (as it did in the default site) however it never registers
as "OK" (with the green checkmark)
When i click the "report manager virtual directory" item on the left nav bar
it shows Name: Reports / and Website: Reports (or Default Website) in greyed
out text boxes.
Any other ideas as to why this might not be working?
Thanks
"Alien2_51" wrote:
> Make sure you have asp.net installed,
> run %WINNT%\Microsoft.NET\Framework\%framework version%\aspnet_regiis.exe -i.
> "Ehren" wrote:
> > Hello-
> >
> > I am having a bit of an issue configuring reporting services so that I can
> > install the System Center Operations Manager 2007 Reporting feature.
> >
> > On our SQL server we have SQL2005 SP2 installed with the operations manager
> > database installed and working. I installed SQL reporting services on the
> > server that runs Operations Manager (OM) which is required to install OM
> > Reporting. I am however having an issue configuring reporting services.
> >
> > I go into the reporting services configuration and there are 3 ares which
> > have red X's, Report Manager Virtual Directory, Web Service Identity and
> > Database Setup.
> >
> > First thing I do is set the virtual directory, I click new and it creates
> > the new virtual directory in the default website however the red X never
> > turns to a green checkmark.
> >
> > Next I go to web service identity and create a new app pool called reportsvc
> > and click apply and get an error saying "There was an error setting the
> > identity for the web service" If i set it to DefaultAppPool I get the same
> > thing.
> >
> > then create the reporting database in the sql2005 server instance which
> > seems to go fine untill I go to click apply then I get an error saying that
> > it could not apply it because a virtual directory must be created first.
> >
> > If i go into inetmgr the virtual directory was created under the default
> > website but it does not register in reporting servcies for some reason.
> >
> > Can anyone help!? Much thanks in advance!!!
> > /Ehren