SQL tool for MSDE: sp2

Showing posts with label sp2. Show all posts

Tuesday, March 27, 2012

Alias not working on some machines

SQL 2005 SP2 on Windows Server 2003 SP2
SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
32-bit. All use 32-bit SQL 2000.
Trying to create an Alias on a SQL 2000 machine to point to a named instance
on a SQL 2005 machine through TCP/IP (for use in replication).
When the alias is created on those SQL 2000 servers running a 32-bit OS, it
works.
When the alias is created on those SQL 2000 servers running a 64-bit OS, it
doesn't.
Instead, when I try to test using isql, I get:
DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
Una
ble to connect: SQL Server does not exist or network access denied.
Net-Library error 53: ConnectionOpen (Connect()).
Any ideas?
Thanks,
Tim CThere seems to be a subtle incompatibility issue. We have started moving our
SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
But if anyone knows of a simpler fix we could implement, I would love to
hear it.
Thanks,
Tim C
"Tim C" wrote:
> SQL 2005 SP2 on Windows Server 2003 SP2
> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
> 32-bit. All use 32-bit SQL 2000.
> Trying to create an Alias on a SQL 2000 machine to point to a named instance
> on a SQL 2005 machine through TCP/IP (for use in replication).
> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
> works.
> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
> doesn't.
> Instead, when I try to test using isql, I get:
> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
> Una
> ble to connect: SQL Server does not exist or network access denied.
> Net-Library error 53: ConnectionOpen (Connect()).
> Any ideas?
> Thanks,
> Tim C|||I recently had a discussion in .tools about this. Paul O'kasick was nice enough to share his
findings. Aparently there's both a 32 and a 64 bit version of cliconfg.exe and these modify
different registry keys. Here's a quote from Paul most recent reply:
"The alias is working on our test machine. It turns out there is a 64 bit
version of cliconfg.exe (C:\WINDOWS\SysWOW64\cliconfg.exe). As soon as I
created the alias with that version, the application started working. I
removed the alias created with the 32 bit version.
Each version maintains a separate list of aliases. The registry key for the
32 bit vs. 64 bit is
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo &
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo,
respectively."
So, my suggestion is that you try to create the alias with both 32 and 64 bit version of
cliconfg.exe to see which it is that is required in your particular case (probably depends on
whether the client app is 32 or 64 bit - meguess).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim C" <TimC@.discussions.microsoft.com> wrote in message
news:098FFFB2-D352-4B16-A32D-CEA42461787C@.microsoft.com...
> There seems to be a subtle incompatibility issue. We have started moving our
> SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
> But if anyone knows of a simpler fix we could implement, I would love to
> hear it.
> Thanks,
> Tim C
> "Tim C" wrote:
>> SQL 2005 SP2 on Windows Server 2003 SP2
>> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
>> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
>> 32-bit. All use 32-bit SQL 2000.
>> Trying to create an Alias on a SQL 2000 machine to point to a named instance
>> on a SQL 2005 machine through TCP/IP (for use in replication).
>> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
>> works.
>> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
>> doesn't.
>> Instead, when I try to test using isql, I get:
>> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
>> Una
>> ble to connect: SQL Server does not exist or network access denied.
>> Net-Library error 53: ConnectionOpen (Connect()).
>> Any ideas?
>> Thanks,
>> Tim C

Monday, March 19, 2012

Aggregation utility for AS 2005

I've heard that an aggregation utility for "manual" aggregation design is available after installing SP2 but I did not find it. How can I run it?Hey Maxim,

Download the latest samples for SQLServer 2k5, install them, broswe to the samples directory and you'll find it. (for me it was C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Administrator\AggregationManager).

Chris.
|||Thank you very much!|||Could you please give a short explanation about how this works?|||Check page 117 of the Performance guide.

Aggregation utility for AS 2005

I've heard that an aggregation utility for "manual" aggregation design is available after installing SP2 but I did not find it. How can I run it?Hey Maxim,

Chris.
|||Thank you very much!|||Could you please give a short explanation about how this works?|||Check page 117 of the Performance guide.

Thursday, March 8, 2012

Aggregate() doesn't aggregate over an expression

We use CTP3 of SQL Server 2005 SP2. We have the following scope expression

Scope
(
[Date].[Calendar Year].[Calendar Year].Members,
[Date].[Month].Members
);

(
{
[Measures].[Profit YTD]
}
) =

Aggregate (
PeriodsToDate([Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember)
, StrToMember(Extensibility.Replace([Measures].CurrentMember.UniqueName, " YTD", "")) -- remove " YTD" suffix to aggregate over the corresponding standard measure

);

Format_String ( This ) = "Currency";
End Scope;

However, Aggregate() essentially bypasses the call to the SSAS stored procedure (Extensibiliy.Replace is a custom SSAS stored procedure). Instead Aggregate() uses the [Profit YTD] measure instead. If I replace Aggregate() with SUM() it works correctly. Is this a bug? A workaround?

Is [Profit YTD] a calculated measure itself ? If this is a case, Aggregate would switch solve orders with it in order to try to determine the correct aggregation function. If Sum is what you want to do, then using Sum directly is probably a right thing to do here.

I also want to note, that the expression inside Aggregate (or Sum) is very inefficient. If you have plenty of calc measures with YTD suffix, and you want to compute year to date over corresponding measure without YTD suffix, there are much more efficient ways of doing it.

|||

Mosha,

Thank you so much for looking into this. I really appreciate your help. Yes, Profit YTD is a calculated member.

Here are our requirements. We have a cube with rather large dimensions, e.g. Customer dimension (some 150,000 customers) and Account dimension (represents a customer bank account) with some 1.5 million members. We need to support additive and semi-additive aggregations (e.g. rolling twelve, weighted averages, etc). Considering the fact that the Report Builder doesn't support dimension-level calculated members (time intelligence), we have no other option but to create measure-level calculated members for each calculation, e.g.: Profit R12 for rolling 12 aggregation of the Profit additive measure, Average Balance R12 for rolling 12 aggregation of the Average Balance weighted average, etc. In addition, we have a requirement to provide YTD and QTD calculations.

The idea behind the the script above was to work universally, i.e. to use the corresponding measure underlying function since additional measures will be added to the scope. For example, if we calculate Profit YTD, we need to base our calculation on the Profit measure (hence, we need to remove the YTD suffix).

Here is an example of our script for R12 aggregations:

Scope
(
[Date].[Month].[Month].Members

);

(
{
[Measures].[Interest Paid R12],
[Measures].[Interest Accrued R12],
...
}
) =
Sum (
{
ParallelPeriod(
[Date].[Calendar].[Month],
11,
[Date].[Calendar].CurrentMember
) : [Date].[Calendar].CurrentMember
}
// current measure
, StrToMember(Extensibility.Replace([Measures].CurrentMember.UniqueName, " R12", "")) -- remove R12 suffix to get to the the corresponding regular measure
);

Format_String ( This ) = "Currency";
End Scope;

What will be the recommended approach from a performance standpoint? Should we introduce multiple scopes (for additive and semi-additive measures) that use the respective aggregation function directly? Should we copy the measure before we start aggregating to avoid StrToMember(), e.g.:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Profit YTD] AS [MEASURES].[Profit] , FORMAT_STRING = "Currency";

|||

Considering the fact that the Report Builder doesn't support dimension-level calculated members (time intelligence)

So the root of your problem is that you cannot use utility attribute in Time dimension or separate utility dimension because of Report Builder limitation ? My advice is to do it right way with utility dimension, but instead of creating calculated members, make R12, YTD etc as real members in this dimension, they just won't be associated with any data. Then, you can put the formulas on them either using custom member formulas or inside MDX script.

|||

Thank you.

1. I'v e read your post about this and and David's article but I need to wrap my head about the utility dimension concept. Would mind eleborating a bit more on the utility dimension approach? Are you saying to add the utility dimension to the cube but don't link this dimension to the measure group at all? What changes need to be made to the Time Intelligence script (if any) to re-purpose it to use the utility dimension?

2. In case the end users find the utility dimension approach confusing from an usability standpoint, can we explore a more efficient approach with standalone calculated members?

|||

Are you saying to add the utility dimension to the cube but don't link this dimension to the measure group at all?

Or you can link the "Normal" or "Current" member of this dimension to the fact table by creating a new calculated column in DSV with constant|||

After a few hours of experimenting and brain crunching I am getting nowhere:

1. Introducing an utility dimension that is not linked to the measure group seems to work in the cube browser/Excel but it doesn't work in the Report Builder because the Report Builder model expects a dmension relationship. Otherwise, there is no navigational path in the model and once you drag and drop the dimension, there is nowhere to go to.

2. The utility dimension approach is causing much grief. Here is what I do.

a) I created a named query DimDateCalculations as follows:

SELECT - 1 AS MemberKey, - 1 AS CurrentPeriodID, 'Current Period' AS CalendarDateCalculations, 'Current Period' AS FiscalDateCalculations
UNION
SELECT 1 AS MemberKey, - 1 AS CurrentPeriodID, 'Rolling 12' AS CalendarDateCalculations, 'Rolling 12' AS FiscalDateCalculations
UNION
SELECT 2 AS MemberKey, - 1 AS CurrentPeriodID, 'YTD' AS CalendarDateCalculations, 'YTD' AS FiscalDateCalculations
UNION
SELECT 3 AS MemberKey, - 1 AS CurrentPeriodID, 'QTD' AS CalendarDateCalculations, 'QTD' AS FiscalDateCalculations

b) I added a calculated column CurrentPeriodID to the fact table and defaulted it to -1. In DSV, I joined DimDateCalculations to the fact table on CurrentPeriodID

c) I created a new dimension (Date Calculations) on top of the DimDateCalculations named query. The dimension key is set to MemberKey and it has two attribute hierarchies (CalendarDateCalculations and FiscalDateCalculations) whose default members are set to the Current Period member.

d) I added the following script to the cube (only changed the dimension name in the script that the Time Intelligence Wizard generates):

Scope(
{
[Measures].[Tax],
[Measures].[Profit],
}
);

(
[Date Calculations].[Calendar Date Calculations].[YTD],
[Date].[Calendar Year].[Calendar Year].Members,
[Date].Month.Members
) =

Aggregate(
{[Date].[Calendar Date Calculations].DefaultMember} *
PeriodsToDate([Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember)

);
End Scope;

e) In the cube browser, I created a report which filters the Calendar Date dimension to a given month. I dropped the Calendar Date Calculations on columns, another dimension on rows, and Profit as data.

However, the YTD column is emtpy (after Show Empty Cells is enabled). What am I missing? Again, our requirement is that the utility dimension cannot have calculated members.

Sunday, February 19, 2012

After SQL 2005 SP2, Query to Apartment Linked Servers fails

We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1 and previous versions.
After applying SP2, we get the following error message when running a query against the linked server.
I cannot find anything in the SP2 documentation that indicates a change of behavior for linked servers.
Any ideas ?

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

TobyHi, I have the same problem with MSDAORA when I try to execute a query using a Linked Server with Oracle Server.
"Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
"

Someone can help us?

Thanks

Denis
|||

I'm not in a position to answer about the change in the linked server code, but perhaps it is possible to reconfigure the appartment model.

What do you have under HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32\ThreadingModel?

Can you try setting it to "Both" and see if you can connect?|||

Ditto,

Did you find a solution?

Thanks.

|||Did you try changing apartment model? Does it not work for you?|||

No, Basically I found out that the 32 bit OLEDB Provider will not work correctly on 64Bit Server 2003 with SQL 2005 SP2 64Bits. I ended up installed the Oracle 64Bit Provider and use OraOLEDB.Oracle provider.

Here's the complete thread:

I'm trying to get SQLServer 2005 to link to an Oracle 10g Database.

The OS is Server 2003 SP2 64Bit.

The Microsoft OLE DB For Oracle driver is installed (MSDAORA) but doesn't appear in SQL Server Providers.

It's a 32 Bit provider.

Since it didn't appear, I used T-SQL sp_addlinkedserver (http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

But the registry shows:

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA"
"OLEDB_SERVICES"=dword:ffffffff

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors]
@.="Extended Error Service"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors\{e8cc4cbf-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA ErrorLookup"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32]
@.="C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\msdaora.dll"
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\OLE DB Provider]
@.="Microsoft OLE DB Provider for Oracle"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ProgID]
@.="MSDAORA.1"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\VersionIndependentProgID]
@.="MSDAORA"

>>>

Looks like SQL 2005 SP2 may have caused the problem.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1433118&SiteID=1

>>>

Use Oracle 64Bit drivers : http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

For 64-bit Distributors, Oracle publishing uses the Oracle OLEDB Provider for Oracle (OraOLEDB.Oracle). SQL Server creates a registry entry to allow the Oracle provider to run in process with SQL Server. If there is a problem reading or writing this registry entry, the following error message is shown:

"Unable to update the registry of distributor '%s' to allow Oracle OLEDB provider OraOLEDB.Oracle to run in process with SQL Server. Make certain that current login is authorized to modify SQL Server owned registry keys."

Oracle publishing requires the registry entry to exist and to be set to 1 for 64 bit Distributors. If the entry does not exist, SQL Server will attempt to create it. If the entry exists, but is set to 0, the setting will not be changed; the configuration of the Oracle Publisher will fail.

To view and modify the registry setting:

1. Click Start, and then click Run.

2. In the Run dialog box, type regedit, and then click OK.

3. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Providers.
Included under Providers should be a folder named OraOLEDB.Oracle. Within this folder should be the DWORD value name AllowInProcess, with a value of 1.

4. If you determine that AllowInProcess is set to 0, update the registry entry to 1:

a. Right-click the entry, and then click Modify.

b. In the Edit String dialog box, type 1 in the Value data field.

So in conclusion:

To create a linked server on SQL Server 2005 SP2 64Bits,

- Install 64Bit Oracle Client

- Install 64Bit Oracle OLEDB Provider

- Set the AllowInProcess value in the registry

After SQL 2005 SP2, Query to Apartment Linked Servers fails

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Someone can help us?

Thanks

Denis
|||

I'm not in a position to answer about the change in the linked server code, but perhaps it is possible to reconfigure the appartment model.

What do you have under HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32\ThreadingModel?

Can you try setting it to "Both" and see if you can connect?|||

Ditto,

Did you find a solution?

Thanks.

|||Did you try changing apartment model? Does it not work for you?|||

Here's the complete thread:

I'm trying to get SQLServer 2005 to link to an Oracle 10g Database.

The OS is Server 2003 SP2 64Bit.

The Microsoft OLE DB For Oracle driver is installed (MSDAORA) but doesn't appear in SQL Server Providers.

It's a 32 Bit provider.

Since it didn't appear, I used T-SQL sp_addlinkedserver (http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

But the registry shows:

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA"
"OLEDB_SERVICES"=dword:ffffffff

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors]
@.="Extended Error Service"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors\{e8cc4cbf-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA ErrorLookup"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32]
@.="C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\msdaora.dll"
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\OLE DB Provider]
@.="Microsoft OLE DB Provider for Oracle"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ProgID]
@.="MSDAORA.1"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\VersionIndependentProgID]
@.="MSDAORA"

>>>

Looks like SQL 2005 SP2 may have caused the problem.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1433118&SiteID=1

>>>

Use Oracle 64Bit drivers : http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

For 64-bit Distributors, Oracle publishing uses the Oracle OLEDB Provider for Oracle (OraOLEDB.Oracle). SQL Server creates a registry entry to allow the Oracle provider to run in process with SQL Server. If there is a problem reading or writing this registry entry, the following error message is shown:

"Unable to update the registry of distributor '%s' to allow Oracle OLEDB provider OraOLEDB.Oracle to run in process with SQL Server. Make certain that current login is authorized to modify SQL Server owned registry keys."

Oracle publishing requires the registry entry to exist and to be set to 1 for 64 bit Distributors. If the entry does not exist, SQL Server will attempt to create it. If the entry exists, but is set to 0, the setting will not be changed; the configuration of the Oracle Publisher will fail.

To view and modify the registry setting:

1. Click Start, and then click Run.

2. In the Run dialog box, type regedit, and then click OK.

3. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Providers.
Included under Providers should be a folder named OraOLEDB.Oracle. Within this folder should be the DWORD value name AllowInProcess, with a value of 1.

4. If you determine that AllowInProcess is set to 0, update the registry entry to 1:

a. Right-click the entry, and then click Modify.

b. In the Edit String dialog box, type 1 in the Value data field.

So in conclusion:

To create a linked server on SQL Server 2005 SP2 64Bits,

- Install 64Bit Oracle Client

- Install 64Bit Oracle OLEDB Provider

- Set the AllowInProcess value in the registry

After SQL 2005 SP2, Query to Apartment Linked Servers fails

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Someone can help us?

Thanks

Denis
|||

I'm not in a position to answer about the change in the linked server code, but perhaps it is possible to reconfigure the appartment model.

What do you have under HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32\ThreadingModel?

Can you try setting it to "Both" and see if you can connect?|||

Ditto,

Did you find a solution?

Thanks.

|||Did you try changing apartment model? Does it not work for you?|||

Here's the complete thread:

I'm trying to get SQLServer 2005 to link to an Oracle 10g Database.

The OS is Server 2003 SP2 64Bit.

The Microsoft OLE DB For Oracle driver is installed (MSDAORA) but doesn't appear in SQL Server Providers.

It's a 32 Bit provider.

Since it didn't appear, I used T-SQL sp_addlinkedserver (http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

But the registry shows:

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA"
"OLEDB_SERVICES"=dword:ffffffff

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors]
@.="Extended Error Service"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors\{e8cc4cbf-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA ErrorLookup"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32]
@.="C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\msdaora.dll"
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\OLE DB Provider]
@.="Microsoft OLE DB Provider for Oracle"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ProgID]
@.="MSDAORA.1"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\VersionIndependentProgID]
@.="MSDAORA"

>>>

Looks like SQL 2005 SP2 may have caused the problem.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1433118&SiteID=1

>>>

Use Oracle 64Bit drivers : http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

For 64-bit Distributors, Oracle publishing uses the Oracle OLEDB Provider for Oracle (OraOLEDB.Oracle). SQL Server creates a registry entry to allow the Oracle provider to run in process with SQL Server. If there is a problem reading or writing this registry entry, the following error message is shown:

"Unable to update the registry of distributor '%s' to allow Oracle OLEDB provider OraOLEDB.Oracle to run in process with SQL Server. Make certain that current login is authorized to modify SQL Server owned registry keys."

Oracle publishing requires the registry entry to exist and to be set to 1 for 64 bit Distributors. If the entry does not exist, SQL Server will attempt to create it. If the entry exists, but is set to 0, the setting will not be changed; the configuration of the Oracle Publisher will fail.

To view and modify the registry setting:

1. Click Start, and then click Run.

2. In the Run dialog box, type regedit, and then click OK.

3. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Providers.
Included under Providers should be a folder named OraOLEDB.Oracle. Within this folder should be the DWORD value name AllowInProcess, with a value of 1.

4. If you determine that AllowInProcess is set to 0, update the registry entry to 1:

a. Right-click the entry, and then click Modify.

b. In the Edit String dialog box, type 1 in the Value data field.

So in conclusion:

To create a linked server on SQL Server 2005 SP2 64Bits,

- Install 64Bit Oracle Client

- Install 64Bit Oracle OLEDB Provider

- Set the AllowInProcess value in the registry

After SQL 2005 SP2, Query to Apartment Linked Servers fails

We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1. After applying SP2, we get the following msg when running a query against the linked server. I cannot find anything in the SP2 doc that indicates a change of behavior for linked servers. Any ideas ?

Bill

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'DBAmp.DBAmp' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

We are having exact same problem

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'xxxx.xxxxxx' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Does anyone have any idea on how to solve this ?

toby

|||

Same here.

Worked fine in SQL Server 2000 (any service pack) and SQL Server 2005 (including SP1) but broke in 2005 with SP2.

Any clues?

Many thanks|||

I confirmed with forceamp support that something in SP2 causes this failure in DBAmp. You need to download the v2.6 beta to resolve this issue or contact support@.forceamp.com and to determine the right build to resolve this issue.

Chad

After SQL 2005 SP2, Query to Apartment Linked Servers fails

Bill

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'DBAmp.DBAmp' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

We are having exact same problem

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'xxxx.xxxxxx' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Does anyone have any idea on how to solve this ?

toby

|||

Same here.

Worked fine in SQL Server 2000 (any service pack) and SQL Server 2005 (including SP1) but broke in 2005 with SP2.

Any clues?

Many thanks|||

Chad

After SQL 2005 SP2, Query to Apartment Linked Servers fails

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Someone can help us?

Thanks

Denis
|||

I'm not in a position to answer about the change in the linked server code, but perhaps it is possible to reconfigure the appartment model.

What do you have under HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32\ThreadingModel?

Can you try setting it to "Both" and see if you can connect?|||

Ditto,

Did you find a solution?

Thanks.

|||Did you try changing apartment model? Does it not work for you?|||

Here's the complete thread:

I'm trying to get SQLServer 2005 to link to an Oracle 10g Database.

The OS is Server 2003 SP2 64Bit.

The Microsoft OLE DB For Oracle driver is installed (MSDAORA) but doesn't appear in SQL Server Providers.

It's a 32 Bit provider.

Since it didn't appear, I used T-SQL sp_addlinkedserver (http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

But the registry shows:

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA"
"OLEDB_SERVICES"=dword:ffffffff

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors]
@.="Extended Error Service"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors\{e8cc4cbf-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA ErrorLookup"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32]
@.="C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\msdaora.dll"
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\OLE DB Provider]
@.="Microsoft OLE DB Provider for Oracle"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ProgID]
@.="MSDAORA.1"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\VersionIndependentProgID]
@.="MSDAORA"

>>>

Looks like SQL 2005 SP2 may have caused the problem.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1433118&SiteID=1

>>>

Use Oracle 64Bit drivers : http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

For 64-bit Distributors, Oracle publishing uses the Oracle OLEDB Provider for Oracle (OraOLEDB.Oracle). SQL Server creates a registry entry to allow the Oracle provider to run in process with SQL Server. If there is a problem reading or writing this registry entry, the following error message is shown:

"Unable to update the registry of distributor '%s' to allow Oracle OLEDB provider OraOLEDB.Oracle to run in process with SQL Server. Make certain that current login is authorized to modify SQL Server owned registry keys."

Oracle publishing requires the registry entry to exist and to be set to 1 for 64 bit Distributors. If the entry does not exist, SQL Server will attempt to create it. If the entry exists, but is set to 0, the setting will not be changed; the configuration of the Oracle Publisher will fail.

To view and modify the registry setting:

1. Click Start, and then click Run.

2. In the Run dialog box, type regedit, and then click OK.

3. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Providers.
Included under Providers should be a folder named OraOLEDB.Oracle. Within this folder should be the DWORD value name AllowInProcess, with a value of 1.

4. If you determine that AllowInProcess is set to 0, update the registry entry to 1:

a. Right-click the entry, and then click Modify.

b. In the Edit String dialog box, type 1 in the Value data field.

So in conclusion:

To create a linked server on SQL Server 2005 SP2 64Bits,

- Install 64Bit Oracle Client

- Install 64Bit Oracle OLEDB Provider

- Set the AllowInProcess value in the registry

Thursday, February 16, 2012

After re-installing SQL server, app did not work

For some reasons, I uninstalled and reinstalled SQL server in local pc. Now, none of my app is working. My pc is XP professional with sp2 and VS.NET is 2003.
I spent a couple of days but no luck. It drived me crazy!
Help!!!did you add the MACHINENAME/ASPNET user account to the db?|||In Web.config file, I use "sa" in connection string, do I need to add ASP.NET account?|||if you are using the useraccount "sa" to connect to the sql server then the user "sa" should have access to the database.

Monday, February 13, 2012

After Intallation

I have a box with SQL Server DataServices with W2K3 SP2 & SQL2005 SP2, then
I have a box with W2K3 with SP2 where I have Reporting Services, at the
configuration page, everything is green and all services started,
initialized and I get this error from:
http://<server>/reports
"Unable to connect to the remote server"
Any Ideas?
Thanks,
VictorOn Jun 18, 12:52 pm, "Victor Rodriguez" <lasals...@.newsgroups.nospam>
wrote:
> I have a box with SQL Server DataServices with W2K3 SP2 & SQL2005 SP2, then
> I have a box with W2K3 with SP2 where I have Reporting Services, at the
> configuration page, everything is green and all services started,
> initialized and I get this error from:
> http://<server>/reports
> "Unable to connect to the remote server"
> Any Ideas?
> Thanks,
> Victor
Have you made sure that IIS is running on your Reporting Services box?
It's turned off by default in Win2003.
rb|||I have 10 different sites on the same box and they're all working.
Victor
"roger.bigger@.good-dog.net" <roger.bigger@.gmail.com> wrote in message
news:1182186112.402444.169070@.m36g2000hse.googlegroups.com...
> On Jun 18, 12:52 pm, "Victor Rodriguez" <lasals...@.newsgroups.nospam>
> wrote:
>> I have a box with SQL Server DataServices with W2K3 SP2 & SQL2005 SP2,
>> then
>> I have a box with W2K3 with SP2 where I have Reporting Services, at the
>> configuration page, everything is green and all services started,
>> initialized and I get this error from:
>> http://<server>/reports
>> "Unable to connect to the remote server"
>> Any Ideas?
>> Thanks,
>> Victor
> Have you made sure that IIS is running on your Reporting Services box?
> It's turned off by default in Win2003.
> rb
>|||Hello Victor,
To know the issue better, please let me know the following:
1. Can you access http://<server>/reportserver properly?
2. Please check if the following ls listed properly in
RSWebApplication.config. If you have SSL enabled for the report server,
please make sure the following is listed
<UrlRoot>https://<server>/ReportServer</UrlRoot>
<ReportServerUrl>https://<server>/ReportServe</ReportServerUrl>
RSWebApplication.config is under
C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting
Services\ReportManager
In addition, this issue could occur if some Antivirus program is installed.
You may want to check if the issue occurs if they are disabled on your
server.
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||When I try to run http://<server>/reportserver, i get:
<server>/ReportServer - /
Microsoft SQL Server Reporting Services Version 9.00.3042.00
I don't have SSL on the website, don't have antivirus on the server.
Anything else I could check?
Thanks,
Victor
""Peter YangMSFT]"" <petery@.online.microsoft.com> wrote in message
news:RzcYHihsHHA.3672@.TK2MSFTNGHUB02.phx.gbl...
> Hello Victor,
> To know the issue better, please let me know the following:
> 1. Can you access http://<server>/reportserver properly?
> 2. Please check if the following ls listed properly in
> RSWebApplication.config. If you have SSL enabled for the report server,
> please make sure the following is listed
> <UrlRoot>https://<server>/ReportServer</UrlRoot>
> <ReportServerUrl>https://<server>/ReportServe</ReportServerUrl>
> RSWebApplication.config is under
> C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting
> Services\ReportManager
> In addition, this issue could occur if some Antivirus program is
> installed.
> You may want to check if the issue occurs if they are disabled on your
> server.
> If you have any update, please feel free to let's know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hello Victor,
From the result, it seems the issue only occurs on report manager. Going
forward, please let me know what's the configuration of <UrlRoot> and
<ReportServerUrl> in the file RSWebApplication.config. Please make sure the
server name is correct, and you may want to use the FQDN such as
server.domain.com if it's a memeber server of a domain.
If the issue persists, please collect the ReportServerWebApp log under the
following folder and let's know the detailed error information:
C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\LogFiles
If you have other related information, please feel free to let's know.
Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your response,
I don't have UrlRoot and the <ReportServerUrl></ReportServerUrl> is like
this.
This is the error I get on the logfile:
w3wp!ui!5!6/20/2007-11:28:57:: e ERROR: Unable to connect to the remote
server
w3wp!ui!5!6/20/2007-11:28:58:: e ERROR: HTTP status code --> 500
--Details--
System.Net.WebException: Unable to connect to the remote server -->
System.Net.Sockets.SocketException: No connection could be made because the
target machine actively refused it
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot,
SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure,
Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState
state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
-- End of inner exception stack trace --
at
Microsoft.SqlServer.ReportingServices2005.RSConnection.GetSecureMethods()
at
Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()
at
Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String
methodname)
at
Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()
at
Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel
level)
at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object
sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Page.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
w3wp!ui!5!6/20/2007-11:29:00:: e ERROR: Exception in ShowErrorPage:
System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean
preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String
errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean
preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String
errMsg)
Victor
""Peter YangMSFT]"" <petery@.online.microsoft.com> wrote in message
news:S20P4KwsHHA.2316@.TK2MSFTNGHUB02.phx.gbl...
> Hello Victor,
> From the result, it seems the issue only occurs on report manager. Going
> forward, please let me know what's the configuration of <UrlRoot> and
> <ReportServerUrl> in the file RSWebApplication.config. Please make sure
> the
> server name is correct, and you may want to use the FQDN such as
> server.domain.com if it's a memeber server of a domain.
> If the issue persists, please collect the ReportServerWebApp log under the
> following folder and let's know the detailed error information:
> C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\LogFiles
> If you have other related information, please feel free to let's know.
> Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hello Victor,
Did you try to change <ReportServerUrl></ReportServerUrl> to
ReportServerUrl>http://servername/ReportServer</ReportServerUrl>?
Also, please check the appliation pool of report manager by running
"inetmgr", and make sure the user of appliation pool has the full
permission of reporting service physical folder.
I look forward to your reply. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your response, I tried all your recommendation and still in the
same place, any other suggestions?
Victor
""Peter YangMSFT]"" <petery@.online.microsoft.com> wrote in message
news:FA1vfM9sHHA.2316@.TK2MSFTNGHUB02.phx.gbl...
> Hello Victor,
> Did you try to change <ReportServerUrl></ReportServerUrl> to
> ReportServerUrl>http://servername/ReportServer</ReportServerUrl>?
> Also, please check the appliation pool of report manager by running
> "inetmgr", and make sure the user of appliation pool has the full
> permission of reporting service physical folder.
> I look forward to your reply. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hello,
Will you post the content of the file RSWebApplication.config? You may want
to check if it has the following lines:
<ReportServerUrl>http://servername/ReportServer</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
Also, please change the <UrlRoot>machine name/reportserver</<UrlRoot> to
<UrlRoot>FQDN/reportserver</<UrlRoot> in RSReportServer.config file.
You may want to restart IIS by running "iisrest" to take effect.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Victor, have you made it by this time?
I still can't figure it out. If I fill the <ReportServerUrl> (which is
blank by default in my environment) I get the following error message:
"The configuration file contains an element that is not valid. The
ReportServerUrl element is not a configuration file element."
I do not have SSL. Does anyone have an idea? Still stuck on this
issue... :-(
Leo Lopes @. L3

After installing SP2 SSRS will not start

Installed SP2 and now SSRS will not start.

Errors:

When I go to the RS Configurator the Database is reported as .40 and the Upgrade Button results in an upgrade success but the Database stays the same version.

Joy....

Much help needed and appreciated....

-p

Since the upgrade went so horribly I created a NEW SSRS database using the SSRS Configuation Tool. I also had to change the permission the '<DRIIVE>\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\RSReportServer.config' file. I have NO idea why the services could r/w from the file before the upgrade and not afterwards that actually scares me the most about this because the SQL team can't fix my box with a QFE. The database upgrader is clearly buggy and that scares me as well, but I bet the SQL team could fix that and repair turfed databases via script.

The impact of doing this meant I had to re-do the following things:

Re-set all Permissions and Site Settings (I had customized the My Reports role) Recreate all Folders and Permissions. (I have top level folders and grant rights there not at the HOME folder). Re-publish all reports (for me and all my "My Users") Re-set passwords on Data Sources which needed them. Inform users all subscriptions were lost

Thursday, February 9, 2012

Advise on hardware upgrade

I would like to have your opinions on how to upgrade the server hardware
here:
Currently:
Windows 2000 Server SP2
Dual Intel Pentium III 1,2 GHz
2 GB RAM
Harddisks are on a SAN, so they are not needed to upgrade
MS-SQL Server 2000 Standard Edition, no other apps run on this machine
The SQL Server hosts a single database, size approx 3 GB, growing by about
10% per month. 60 highly concurrent users, in fact doing the same things at
the same time. The largest table has about 9.000.000 records, daily
increment 9000.
When the users start their tasks, I encounter - due to the intense
concurrency - performance degradation, although the overall performance is
ok. These peaks are beyond the servers capabilities. I monitored using the
performance monitor:
Processor Queue lenght: average 0,58 max 15
% Processor Time Processor 0 average 15,5 max 41,9 (but I have seen peaks up
to 90% as well)
% Processor Time Processor 1 average 19,5 max 61,1
% Privileged Time Processor 0 average 1,5 max 7,1
% Privileged Time Processor 1 average 1,3 max 5,7
I would recommend upgrading to a 4 processor system with Pentium 4 3,2 GHz
What´s your opinion?
Thanks for your input.
BerndStrange that nobody seems to have an opninion on this topic. Is there a
reason why?
TIA
Bernd
"Bernd Maierhofer (dato)" <bernd.maierhofer@.dato.at> schrieb im Newsbeitrag
news:en172$OqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> I would like to have your opinions on how to upgrade the server hardware
> here:
> Currently:
> Windows 2000 Server SP2
> Dual Intel Pentium III 1,2 GHz
> 2 GB RAM
> Harddisks are on a SAN, so they are not needed to upgrade
> MS-SQL Server 2000 Standard Edition, no other apps run on this machine
> The SQL Server hosts a single database, size approx 3 GB, growing by about
> 10% per month. 60 highly concurrent users, in fact doing the same things
at
> the same time. The largest table has about 9.000.000 records, daily
> increment 9000.
> When the users start their tasks, I encounter - due to the intense
> concurrency - performance degradation, although the overall performance is
> ok. These peaks are beyond the servers capabilities. I monitored using the
> performance monitor:
> Processor Queue lenght: average 0,58 max 15
> % Processor Time Processor 0 average 15,5 max 41,9 (but I have seen peaks
up
> to 90% as well)
> % Processor Time Processor 1 average 19,5 max 61,1
> % Privileged Time Processor 0 average 1,5 max 7,1
> % Privileged Time Processor 1 average 1,3 max 5,7
> I would recommend upgrading to a 4 processor system with Pentium 4 3,2 GHz
> What´s your opinion?
> Thanks for your input.
> Bernd
>

Tuesday, March 27, 2012

Alias not working on some machines

Alias not working on some machines

Monday, March 19, 2012

Aggregation utility for AS 2005

Aggregation utility for AS 2005

Thursday, March 8, 2012

Aggregate() doesn't aggregate over an expression

Sunday, February 19, 2012

After SQL 2005 SP2, Query to Apartment Linked Servers fails

After SQL 2005 SP2, Query to Apartment Linked Servers fails

After SQL 2005 SP2, Query to Apartment Linked Servers fails

After SQL 2005 SP2, Query to Apartment Linked Servers fails

After SQL 2005 SP2, Query to Apartment Linked Servers fails

After SQL 2005 SP2, Query to Apartment Linked Servers fails

Thursday, February 16, 2012

After re-installing SQL server, app did not work

Monday, February 13, 2012

After Intallation

After installing SP2 SSRS will not start

Thursday, February 9, 2012

Advise on hardware upgrade

SQL tool for MSDE

Blog Archive

About Me