Problem occurs when migrating a SQL Server 2000 database to a SQL Server 2005 server

When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;

Performing operation...

- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)

When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;

InnerException-->An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).

How do I solve this problem?

Go into the Surface Area Configuration tool and select Configuration for Services and Connections. On your server, under Database Engine/Remote Connections make sure the Local and Remote Connections radio button is selected.|||See more about that in my screencast section on my site.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Alan,

The Local and Remote Connections radio button is already selected on my Windows SQL 2005 server.

KevinDSE

|||Any firewall enabled on the server ? Are you running on a non default port ? The default one is 1433. Are you running SQL Server Browser service ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens,

The standard Windows firewall on my SQL 2005 server (with Windows Server 2003 Standard Edition) is disabled.
I am also running the default port 1433 for TCP/IP etc.
The SQL Server Browser service is also enabled on this machine.

|||I dont know how familiar you are wih named instances, or if you even have a named instance, but if you want to connect to a named instance, you will have to call it like the following syntax: Servername\InstanceName. Just to quite sure (wheter you are or not using SQL Server named instances, I would try putting a ,1433 after the server / server\InstanceName like Servername\InstaneName,Portnumber to explicitly call the sevrer on this port. make also sure that the services was started probably and bound the TCP port to the service.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I am having the exact same issue and was wondering what the exact solution is.

Thanks, Kyle

|||Dear kyle,

This reply may be late. But this may be helpful for those who happen to get to this topic now ;)

In the Copy wizard, at source server, make sure you connect with sa/password [ dont go with "Windows Auth"] and same with destination server. Make sure firewall at both source and destination are switched off.

Regards,
Vipin Venu

Problem occurs when migrating a SQL Server 2000 database to a SQL Server 2005 server

When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;

Performing operation...

- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)

When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;

InnerException-->An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).

How do I solve this problem?

Go into the Surface Area Configuration tool and select Configuration for Services and Connections. On your server, under Database Engine/Remote Connections make sure the Local and Remote Connections radio button is selected.|||See more about that in my screencast section on my site.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Alan,

The Local and Remote Connections radio button is already selected on my Windows SQL 2005 server.

KevinDSE

|||Any firewall enabled on the server ? Are you running on a non default port ? The default one is 1433. Are you running SQL Server Browser service ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens,

The standard Windows firewall on my SQL 2005 server (with Windows Server 2003 Standard Edition) is disabled.
I am also running the default port 1433 for TCP/IP etc.
The SQL Server Browser service is also enabled on this machine.

|||I dont know how familiar you are wih named instances, or if you even have a named instance, but if you want to connect to a named instance, you will have to call it like the following syntax: Servername\InstanceName. Just to quite sure (wheter you are or not using SQL Server named instances, I would try putting a ,1433 after the server / server\InstanceName like Servername\InstaneName,Portnumber to explicitly call the sevrer on this port. make also sure that the services was started probably and bound the TCP port to the service.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I am having the exact same issue and was wondering what the exact solution is.

Thanks, Kyle

|||Dear kyle,

This reply may be late. But this may be helpful for those who happen to get to this topic now ;)

In the Copy wizard, at source server, make sure you connect with sa/password [ dont go with "Windows Auth"] and same with destination server. Make sure firewall at both source and destination are switched off.

Regards,
Vipin Venu

Problem occurs when migrating a SQL Server 2000 database to a SQL Server 2005 server

When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;

Performing operation...

- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)

When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;

InnerException-->An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).

How do I solve this problem?

Go into the Surface Area Configuration tool and select Configuration for Services and Connections. On your server, under Database Engine/Remote Connections make sure the Local and Remote Connections radio button is selected.|||See more about that in my screencast section on my site.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Alan,

The Local and Remote Connections radio button is already selected on my Windows SQL 2005 server.

KevinDSE

|||Any firewall enabled on the server ? Are you running on a non default port ? The default one is 1433. Are you running SQL Server Browser service ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens,

The standard Windows firewall on my SQL 2005 server (with Windows Server 2003 Standard Edition) is disabled.
I am also running the default port 1433 for TCP/IP etc.
The SQL Server Browser service is also enabled on this machine.

|||I dont know how familiar you are wih named instances, or if you even have a named instance, but if you want to connect to a named instance, you will have to call it like the following syntax: Servername\InstanceName. Just to quite sure (wheter you are or not using SQL Server named instances, I would try putting a ,1433 after the server / server\InstanceName like Servername\InstaneName,Portnumber to explicitly call the sevrer on this port. make also sure that the services was started probably and bound the TCP port to the service.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I am having the exact same issue and was wondering what the exact solution is.

Thanks, Kyle

|||Dear kyle,

This reply may be late. But this may be helpful for those who happen to get to this topic now ;)

In the Copy wizard, at source server, make sure you connect with sa/password [ dont go with "Windows Auth"] and same with destination server. Make sure firewall at both source and destination are switched off.

Regards,
Vipin Venu

Problem migrating SQL Server 2000 database to 2005

I have an application that is running on Windows server 2003 and SQL 2000
Server. This application was written in VB so it has both windows client
interface and Web client interface. Due to the age of this server we
purchased a new server and installed windows server 2003 R2 SP1 and SQL
Server 2005 standard edition. To migrate the SQL Server 2000 database I
backed up the SQL 2000 server database and then restore it to the SQL 2005
server. I then run the sp_revlogin per the KB - I migrated the logins with
their SIDs and password. I tested it by login into management studio and
running queries in the database with the migrated logins. I then run update
statistics and DBCC updateusage.
However, when the users try to open the application they get the error
message that, " Login failed for AppUser" Appuser is the login that the
application used to authenticate to the database. I have tried the operation
allover again a few times to ensure that I did not miss anything in the
process. I even tried upgrade compatibility level to 9.0 but the same error
is still coming. I have also tried detach/reattach but I got the same error.
I tried the copy database wizard and got the same error.
Any assistance will be greatly appreciated.
Thanks!
ODOD wrote:
> I have an application that is running on Windows server 2003 and SQL 2000
> Server. This application was written in VB so it has both windows client
> interface and Web client interface. Due to the age of this server we
> purchased a new server and installed windows server 2003 R2 SP1 and SQL
> Server 2005 standard edition. To migrate the SQL Server 2000 database I
> backed up the SQL 2000 server database and then restore it to the SQL 2005
> server. I then run the sp_revlogin per the KB - I migrated the logins with
> their SIDs and password. I tested it by login into management studio and
> running queries in the database with the migrated logins. I then run updat
e
> statistics and DBCC updateusage.
> However, when the users try to open the application they get the error
> message that, " Login failed for AppUser" Appuser is the login that the
> application used to authenticate to the database. I have tried the operati
on
> allover again a few times to ensure that I did not miss anything in the
> process. I even tried upgrade compatibility level to 9.0 but the same erro
r
> is still coming. I have also tried detach/reattach but I got the same erro
r.
> I tried the copy database wizard and got the same error.
> Any assistance will be greatly appreciated.
> Thanks!
> OD
>
Did you try connecting via Management Studio using this "AppUser" login?
Verify remote connectivity to the server? Confirm that AppUser
exists, reset the password?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy:
I connected to the database via Management studio using the AppUser login
and password. AppUser exists on the database. Connectivity is good since the
server also host CRM and its database for our company. Any ideas?
OD
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45017AD2.8050402@.realsqlguy.com...
> OD wrote:
> Did you try connecting via Management Studio using this "AppUser" login?
> Verify remote connectivity to the server? Confirm that AppUser exists,
> reset the password?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||OD wrote:
> Tracy:
> I connected to the database via Management studio using the AppUser login
> and password. AppUser exists on the database. Connectivity is good since t
he
> server also host CRM and its database for our company. Any ideas?
>
Verify that the connection string or ODBC connection that the app uses
is configured properly. If you can connect as AppUser from Management
Studio, then the problem is not on the database side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy:
That is correct. The problem was in the application. The developer gave me
the correct password that was hard coded in the application and it works.
Thanks!
OD
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:450182C2.2070001@.realsqlguy.com...
> OD wrote:
> Verify that the connection string or ODBC connection that the app uses is
> configured properly. If you can connect as AppUser from Management
> Studio, then the problem is not on the database side.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Problem migrating SQL Server 2000 database to 2005

I have an application that is running on Windows server 2003 and SQL 2000
Server. This application was written in VB so it has both windows client
interface and Web client interface. Due to the age of this server we
purchased a new server and installed windows server 2003 R2 SP1 and SQL
Server 2005 standard edition. To migrate the SQL Server 2000 database I
backed up the SQL 2000 server database and then restore it to the SQL 2005
server. I then run the sp_revlogin per the KB - I migrated the logins with
their SIDs and password. I tested it by login into management studio and
running queries in the database with the migrated logins. I then run update
statistics and DBCC updateusage.
However, when the users try to open the application they get the error
message that, " Login failed for AppUser" Appuser is the login that the
application used to authenticate to the database. I have tried the operation
allover again a few times to ensure that I did not miss anything in the
process. I even tried upgrade compatibility level to 9.0 but the same error
is still coming. I have also tried detach/reattach but I got the same error.
I tried the copy database wizard and got the same error.
Any assistance will be greatly appreciated.
Thanks!
ODOD wrote:
> I have an application that is running on Windows server 2003 and SQL 2000
> Server. This application was written in VB so it has both windows client
> interface and Web client interface. Due to the age of this server we
> purchased a new server and installed windows server 2003 R2 SP1 and SQL
> Server 2005 standard edition. To migrate the SQL Server 2000 database I
> backed up the SQL 2000 server database and then restore it to the SQL 2005
> server. I then run the sp_revlogin per the KB - I migrated the logins with
> their SIDs and password. I tested it by login into management studio and
> running queries in the database with the migrated logins. I then run update
> statistics and DBCC updateusage.
> However, when the users try to open the application they get the error
> message that, " Login failed for AppUser" Appuser is the login that the
> application used to authenticate to the database. I have tried the operation
> allover again a few times to ensure that I did not miss anything in the
> process. I even tried upgrade compatibility level to 9.0 but the same error
> is still coming. I have also tried detach/reattach but I got the same error.
> I tried the copy database wizard and got the same error.
> Any assistance will be greatly appreciated.
> Thanks!
> OD
>
Did you try connecting via Management Studio using this "AppUser" login?
Verify remote connectivity to the server? Confirm that AppUser
exists, reset the password?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy:
I connected to the database via Management studio using the AppUser login
and password. AppUser exists on the database. Connectivity is good since the
server also host CRM and its database for our company. Any ideas?
OD
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45017AD2.8050402@.realsqlguy.com...
> OD wrote:
>> I have an application that is running on Windows server 2003 and SQL 2000
>> Server. This application was written in VB so it has both windows client
>> interface and Web client interface. Due to the age of this server we
>> purchased a new server and installed windows server 2003 R2 SP1 and SQL
>> Server 2005 standard edition. To migrate the SQL Server 2000 database I
>> backed up the SQL 2000 server database and then restore it to the SQL
>> 2005 server. I then run the sp_revlogin per the KB - I migrated the
>> logins with their SIDs and password. I tested it by login into management
>> studio and running queries in the database with the migrated logins. I
>> then run update statistics and DBCC updateusage.
>> However, when the users try to open the application they get the error
>> message that, " Login failed for AppUser" Appuser is the login that the
>> application used to authenticate to the database. I have tried the
>> operation allover again a few times to ensure that I did not miss
>> anything in the process. I even tried upgrade compatibility level to 9.0
>> but the same error is still coming. I have also tried detach/reattach but
>> I got the same error. I tried the copy database wizard and got the same
>> error.
>> Any assistance will be greatly appreciated.
>> Thanks!
>> OD
> Did you try connecting via Management Studio using this "AppUser" login?
> Verify remote connectivity to the server? Confirm that AppUser exists,
> reset the password?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||OD wrote:
> Tracy:
> I connected to the database via Management studio using the AppUser login
> and password. AppUser exists on the database. Connectivity is good since the
> server also host CRM and its database for our company. Any ideas?
>
Verify that the connection string or ODBC connection that the app uses
is configured properly. If you can connect as AppUser from Management
Studio, then the problem is not on the database side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy:
That is correct. The problem was in the application. The developer gave me
the correct password that was hard coded in the application and it works.
Thanks!
OD
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:450182C2.2070001@.realsqlguy.com...
> OD wrote:
>> Tracy:
>> I connected to the database via Management studio using the AppUser login
>> and password. AppUser exists on the database. Connectivity is good since
>> the server also host CRM and its database for our company. Any ideas?
> Verify that the connection string or ODBC connection that the app uses is
> configured properly. If you can connect as AppUser from Management
> Studio, then the problem is not on the database side.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Problem migrating RS 2000 database to RS 2005

Hi all,
I have RS 2000 SP2 (8.00.1038.00) installed on one server. RS 2005 is
installed on another server and I followed the steps from Migrating Reporting
Services (SQL Server 2005 Books Online).
I managed to copy the MDF files ReportServer and ReportServerTempDB and
their associated log files from the SQL 2000 server to the SQL 2005 server.
They were attached in SQL 2005 successfully.
However error occurred when I tried to upgrade the database from the RS
Configuration Manager.
"There was a problem applying the database upgrade script."
----
System.Data.SqlClient.SqlException: Cannot find the user 'RSExecRole',
because it does not exist or you do not have permission.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String
methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String
connectionString, String script)
The weird thing is when I repeated the procedure, I didn't see the above
error again and it managed to proceed but with errors.
"Creating a Grant Rights script for NT AUTHORITY\NetworkService
The grant rights script was generated successfully."
"The Reporting Services rights were not applied properly. The user may
still not have appropriate access to Reporting Services resources.
----
System.Data.SqlClient.SqlException: The role 'RSExecRole' does not exist in
the current database.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String
methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String
connectionString, String script)
The creation of grant right script was repeated.
"Creating a Grant Rights script for NT Authority\NetworkService
The grant rights script was generated successfully."
"The Reporting Services rights were not applied properly. The user may
still not have appropriate access to Reporting Services resources.
-----
System.Data.SqlClient.SqlException: The role 'RSExecRole' does not exist in
the current database.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String
methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String
connectionString, String script)
It looks like the main culprit is 'RSExecRole'. Any idea how to correct this?
Thanks.
--
Best regards,
JudeHi all,
After some investigations, I managed to get it to work and thought I'd share
it here for the benefit of all.
To migrate a RS 2000 database from a SQL Server 2000 server to another
server running SQL Server 2005, follow the steps in SQL Server 2005 Books
Online under Migrating Reporting Services.
As mentioned in my previous post, it failed at the step where the database
was upgraded. The workaround is: create a new RS database in the RS
Configuration Manager before upgrading. Do not give it the same name as the
RS database.
When this is done, you can upgrade the RS 2000 database. I managed to
migrate the RS 2000 database to RS 2005 successfully using this workaround.
Good luck.
Best regards,
Jude
"Jude Wong" wrote:
> Hi all,
> I have RS 2000 SP2 (8.00.1038.00) installed on one server. RS 2005 is
> installed on another server and I followed the steps from Migrating Reporting
> Services (SQL Server 2005 Books Online).
> I managed to copy the MDF files ReportServer and ReportServerTempDB and
> their associated log files from the SQL 2000 server to the SQL 2005 server.
> They were attached in SQL 2005 successfully.
> However error occurred when I tried to upgrade the database from the RS
> Configuration Manager.
> "There was a problem applying the database upgrade script."
> ----
> System.Data.SqlClient.SqlException: Cannot find the user 'RSExecRole',
> because it does not exist or you do not have permission.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String
> methodName, Boolean async)
> at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String
> connectionString, String script)
> The weird thing is when I repeated the procedure, I didn't see the above
> error again and it managed to proceed but with errors.
> "Creating a Grant Rights script for NT AUTHORITY\NetworkService
> The grant rights script was generated successfully."
> "The Reporting Services rights were not applied properly. The user may
> still not have appropriate access to Reporting Services resources."
> ----
> System.Data.SqlClient.SqlException: The role 'RSExecRole' does not exist in
> the current database.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String
> methodName, Boolean async)
> at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String
> connectionString, String script)
> The creation of grant right script was repeated.
> "Creating a Grant Rights script for NT Authority\NetworkService
> The grant rights script was generated successfully."
> "The Reporting Services rights were not applied properly. The user may
> still not have appropriate access to Reporting Services resources."
> -----
> System.Data.SqlClient.SqlException: The role 'RSExecRole' does not exist in
> the current database.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String
> methodName, Boolean async)
> at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String
> connectionString, String script)
> It looks like the main culprit is 'RSExecRole'. Any idea how to correct this?
> Thanks.
>
> --
> Best regards,
> Jude

Problem migrating NonEmptyCrossJoin query to AS2005

Hi,

In AS2000 we were using following query as a basis for product report:

select {[Measures].[Stock], [Measures].[Sales Qty YTD], [Measures].[Sales Qty LW]} on columns,
NonEmptyCrossJoin([Hierarchy].[Sub Category].Members, [SKU].[SKU Name].Members, [Supplier].[Short Name].Members) on rows
from eSalesActStock
where [DOp Calendar].[OpYear].[2006].[08.February].[Week 32]

It was used to show all products either on stock, or sold this year or both.
It was getting approx 9k rows as result
The cube used was a virtual one, merging stock and sales cubes.
The where clause is only to put calculated members from sales cube in context.
Dimensions are crossjoined only because we need them for grouping and on report layout. Every SKU has a supplier and is attached to assortment hierarchy.

In AS2005 we tried to recreate the dataset, and this is what we ended up with:

SELECT NON EMPTY { [Measures].[Sales Qty YTD], [Measures].[Stock],
[Measures].[Sales Qty LW]} ON COLUMNS,
NonEmpty ([DHierarchy].[DHierarchy].[Division].ALLMEMBERS * [DSKU].[DSKU Name].[DSKU Name].ALLMEMBERS *
[DSupplier].[DSupplier].[Supplier].ALLMEMBERS ) ON ROWS
FROM [SalesStock]
WHERE ( [DCalendar].[DOp Calendar].[OpYear].&[2006].&[08.February].&[Week 32] )

Unfortunately this query only results in 1.1k rows result, with the same data and parameters.
We have played a lot around with it, even trying to use nonemptycrossjoin but the results remained the same.
We did find out, that after removing where clause, the query retrieves 9k rows, which seems to be correct amount, but the data are useless, as calculated members do not have date context.

The only thing that differs from the AS2000 is that in AS2000 we had stock figures artificially tied to a date, to see the stock figures if you chose any date dimension filters. In AS2005 this is no longer necessary, so we just left date dimension and stock measure group unrelated. Can this be the cause of problem ? It seems unlikely, as crossjoin is made on different dimensions...

Are we missing something ? It used to work as the result of crossjoin was not related to where clause on non crossjoined dimension, now it seems that date dimension actually influences the results of the cross join...

Maybe there is a way to solve or walk around this problem ?

Any feedback appreciated :)

Thanks,

Marcin

Hi Marcin,

The role of the date wrt. stock data isn't clear to me; but since the NonEmpty() returns the right results without the where clause, try adding the default date as the filter:

>>

SELECT NON EMPTY { [Measures].[Sales Qty YTD], [Measures].[Stock],
[Measures].[Sales Qty LW]} ON COLUMNS,
NonEmpty ([DHierarchy].[DHierarchy].[Division].ALLMEMBERS * [DSKU].[DSKU Name].[DSKU Name].ALLMEMBERS *
[DSupplier].[DSupplier].[Supplier].ALLMEMBERS,

{[DCalendar].[DOp Calendar].DefaultMember} ) ON ROWS
FROM [SalesStock]
WHERE ( [DCalendar].[DOp Calendar].[OpYear].&[2006].&[08.February].&[Week 32] )
>>

|||Thanks a lot for your answer, it did solve the problem :)

Problem migrating from sql7 to sql2000 - connection failure

We have an inhouse app written in Delphi which connects to a local server
running SQL7. When we migrated the database to a server running SQL2000 we
ran into problems.
When a user uses the Delphi app to browse data, it seems ok, but when the
user insert records, the app responded "Connection failure".
In the application log of the event viewer, we noticed the following:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
I did a search on support.microsoft.com for this message. It seems there is
some information regarding this on some complicated cause which will be
addressed in service pack 4.
I wonder should I wait for service pack 4 to solve it, or is there something
I can do?LL
From Erland
The message indicates a protocol error in the dialog between client and
server. There are several possible causes for this. The most likely
causes are 1) network problems 2) bug in the client library.
"LL" <LL@.discussions.microsoft.com> wrote in message
news:8A487A41-DE7C-4A5A-B7B7-0397322AD79D@.microsoft.com...
> We have an inhouse app written in Delphi which connects to a local server
> running SQL7. When we migrated the database to a server running SQL2000 we
> ran into problems.
> When a user uses the Delphi app to browse data, it seems ok, but when the
> user insert records, the app responded "Connection failure".
> In the application log of the event viewer, we noticed the following:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> I did a search on support.microsoft.com for this message. It seems there
is
> some information regarding this on some complicated cause which will be
> addressed in service pack 4.
> I wonder should I wait for service pack 4 to solve it, or is there
something
> I can do?|||I dont know how it relates to network problem, because the error condition
only happens when we use a user account to insert/change data via the app.
The only thing that i can say is that the sql 2000 database resides on the
domain controller server, whereas, before the sql 7 database resides on a
workgroup server. i will need to follow up with the programmer to see if
there exist some hardcoded machine name inside the app that causes the
failure.
As for 'bug in client library', i dont know why a failure only happens
during write. but it sounds like i can only try to get the programmer to
check the app or see if there are other compatible versions of delphi that
works with sql2000.
thanks for your reply anyways...
"Uri Dimant" wrote:
> LL
> From Erland
> The message indicates a protocol error in the dialog between client and
> server. There are several possible causes for this. The most likely
> causes are 1) network problems 2) bug in the client library.
>
> "LL" <LL@.discussions.microsoft.com> wrote in message
> news:8A487A41-DE7C-4A5A-B7B7-0397322AD79D@.microsoft.com...
> > We have an inhouse app written in Delphi which connects to a local server
> > running SQL7. When we migrated the database to a server running SQL2000 we
> > ran into problems.
> >
> > When a user uses the Delphi app to browse data, it seems ok, but when the
> > user insert records, the app responded "Connection failure".
> >
> > In the application log of the event viewer, we noticed the following:
> >
> > Error: 17805, Severity: 20, State: 3
> > Invalid buffer received from client.
> >
> > I did a search on support.microsoft.com for this message. It seems there
> is
> > some information regarding this on some complicated cause which will be
> > addressed in service pack 4.
> >
> > I wonder should I wait for service pack 4 to solve it, or is there
> something
> > I can do?
>
>|||Dear LL :
I also got this error when using delphi 7 to update or changing the
record. My database is also migrating from sql7 to sql2k, please send me
some information if you can solve this problem , tkx so much
George

Problem migrating from sql7 to sql2000 - connection failure

We have an inhouse app written in Delphi which connects to a local server
running SQL7. When we migrated the database to a server running SQL2000 we
ran into problems.
When a user uses the Delphi app to browse data, it seems ok, but when the
user insert records, the app responded "Connection failure".
In the application log of the event viewer, we noticed the following:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
I did a search on support.microsoft.com for this message. It seems there is
some information regarding this on some complicated cause which will be
addressed in service pack 4.
I wonder should I wait for service pack 4 to solve it, or is there something
I can do?
LL
From Erland
The message indicates a protocol error in the dialog between client and
server. There are several possible causes for this. The most likely
causes are 1) network problems 2) bug in the client library.
"LL" <LL@.discussions.microsoft.com> wrote in message
news:8A487A41-DE7C-4A5A-B7B7-0397322AD79D@.microsoft.com...
> We have an inhouse app written in Delphi which connects to a local server
> running SQL7. When we migrated the database to a server running SQL2000 we
> ran into problems.
> When a user uses the Delphi app to browse data, it seems ok, but when the
> user insert records, the app responded "Connection failure".
> In the application log of the event viewer, we noticed the following:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> I did a search on support.microsoft.com for this message. It seems there
is
> some information regarding this on some complicated cause which will be
> addressed in service pack 4.
> I wonder should I wait for service pack 4 to solve it, or is there
something
> I can do?
|||I dont know how it relates to network problem, because the error condition
only happens when we use a user account to insert/change data via the app.
The only thing that i can say is that the sql 2000 database resides on the
domain controller server, whereas, before the sql 7 database resides on a
workgroup server. i will need to follow up with the programmer to see if
there exist some hardcoded machine name inside the app that causes the
failure.
As for 'bug in client library', i dont know why a failure only happens
during write. but it sounds like i can only try to get the programmer to
check the app or see if there are other compatible versions of delphi that
works with sql2000.
thanks for your reply anyways...
"Uri Dimant" wrote:

> LL
> From Erland
> The message indicates a protocol error in the dialog between client and
> server. There are several possible causes for this. The most likely
> causes are 1) network problems 2) bug in the client library.
>
> "LL" <LL@.discussions.microsoft.com> wrote in message
> news:8A487A41-DE7C-4A5A-B7B7-0397322AD79D@.microsoft.com...
> is
> something
>
>
|||Dear LL :
I also got this error when using delphi 7 to update or changing the
record. My database is also migrating from sql7 to sql2k, please send me
some information if you can solve this problem , tkx so much
George

Problem migrating from sql7 to sql2000 - connection failure

We have an inhouse app written in Delphi which connects to a local server
running SQL7. When we migrated the database to a server running SQL2000 we
ran into problems.
When a user uses the Delphi app to browse data, it seems ok, but when the
user insert records, the app responded "Connection failure".
In the application log of the event viewer, we noticed the following:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
I did a search on support.microsoft.com for this message. It seems there is
some information regarding this on some complicated cause which will be
addressed in service pack 4.
I wonder should I wait for service pack 4 to solve it, or is there something
I can do?LL
From Erland
The message indicates a protocol error in the dialog between client and
server. There are several possible causes for this. The most likely
causes are 1) network problems 2) bug in the client library.
"LL" <LL@.discussions.microsoft.com> wrote in message
news:8A487A41-DE7C-4A5A-B7B7-0397322AD79D@.microsoft.com...
> We have an inhouse app written in Delphi which connects to a local server
> running SQL7. When we migrated the database to a server running SQL2000 we
> ran into problems.
> When a user uses the Delphi app to browse data, it seems ok, but when the
> user insert records, the app responded "Connection failure".
> In the application log of the event viewer, we noticed the following:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> I did a search on support.microsoft.com for this message. It seems there
is
> some information regarding this on some complicated cause which will be
> addressed in service pack 4.
> I wonder should I wait for service pack 4 to solve it, or is there
something
> I can do?|||I dont know how it relates to network problem, because the error condition
only happens when we use a user account to insert/change data via the app.
The only thing that i can say is that the sql 2000 database resides on the
domain controller server, whereas, before the sql 7 database resides on a
workgroup server. i will need to follow up with the programmer to see if
there exist some hardcoded machine name inside the app that causes the
failure.
As for 'bug in client library', i dont know why a failure only happens
during write. but it sounds like i can only try to get the programmer to
check the app or see if there are other compatible versions of delphi that
works with sql2000.
thanks for your reply anyways...
"Uri Dimant" wrote:

> LL
> From Erland
> The message indicates a protocol error in the dialog between client and
> server. There are several possible causes for this. The most likely
> causes are 1) network problems 2) bug in the client library.
>
> "LL" <LL@.discussions.microsoft.com> wrote in message
> news:8A487A41-DE7C-4A5A-B7B7-0397322AD79D@.microsoft.com...
> is
> something
>
>|||Dear LL :
I also got this error when using delphi 7 to update or changing the
record. My database is also migrating from sql7 to sql2k, please send me
some information if you can solve this problem , tkx so much
George

problem migrating db

I have an access database project that is having problems
since I have migrated the database it connects to. It
connects fine, but all of the server objects show (dbo)
after them. I think the (dbo) issue is related to the
sql user account setup. The user account no longer maps
to a domain account as it had been with the previous
server.
Here is some background information about the migration
and installation of sql server:
I am trying to migrate a database to a new server that is
running windows 2003 and sql server 2000 sp3. I have
copied a backup from the old server to the new server,
and performed a restore. However, when I restore the
database, the users in the database no longer have the
login name (the domain account associated with the user)
mapped. I can delete the accounts and recreate them, all
except the dbo user, which has a lot of objects owned by
it. Is there a way to remap this dbo user to the domain
account that I previously had it mapped to?
During the installation of sql server 2k, I do get an
error stating that sql server sp2 and less is not
supported on the version of windows I am running. I
doubt this is the problem, because I have updated it to
sp3.
Any help you can provide would be greatly appreciated.
Thanks,
Jason
Hi
dbo is the database owner if you do
sp_helpdb dbname
Then it will show the database owner. In general it is usually better to
have dbo as the owner of the objects in the database.
If you want to change the database owner look up the stored procedure
sp_changedbowner in Books Online.
For granting access to the database server check out sp_grantlogin and for
the database see sp_grantdbaccess. It is usually better to grant access to
Windows Groups (which your users belong to) rather than to individual
accounts.
The error message regarding SP3 is normal, upgrading straight away to SP3
means that you will be ok.
John
"jason" <anonymous@.discussions.microsoft.com> wrote in message
news:d9a701c43abb$9b48c770$a601280a@.phx.gbl...
> I have an access database project that is having problems
> since I have migrated the database it connects to. It
> connects fine, but all of the server objects show (dbo)
> after them. I think the (dbo) issue is related to the
> sql user account setup. The user account no longer maps
> to a domain account as it had been with the previous
> server.
> Here is some background information about the migration
> and installation of sql server:
> I am trying to migrate a database to a new server that is
> running windows 2003 and sql server 2000 sp3. I have
> copied a backup from the old server to the new server,
> and performed a restore. However, when I restore the
> database, the users in the database no longer have the
> login name (the domain account associated with the user)
> mapped. I can delete the accounts and recreate them, all
> except the dbo user, which has a lot of objects owned by
> it. Is there a way to remap this dbo user to the domain
> account that I previously had it mapped to?
> During the installation of sql server 2k, I do get an
> error stating that sql server sp2 and less is not
> supported on the version of windows I am running. I
> doubt this is the problem, because I have updated it to
> sp3.
> Any help you can provide would be greatly appreciated.
> Thanks,
> Jason

problem migrating db

I have an access database project that is having problems
since I have migrated the database it connects to. It
connects fine, but all of the server objects show (dbo)
after them. I think the (dbo) issue is related to the
sql user account setup. The user account no longer maps
to a domain account as it had been with the previous
server.
Here is some background information about the migration
and installation of sql server:
I am trying to migrate a database to a new server that is
running windows 2003 and sql server 2000 sp3. I have
copied a backup from the old server to the new server,
and performed a restore. However, when I restore the
database, the users in the database no longer have the
login name (the domain account associated with the user)
mapped. I can delete the accounts and recreate them, all
except the dbo user, which has a lot of objects owned by
it. Is there a way to remap this dbo user to the domain
account that I previously had it mapped to?
During the installation of sql server 2k, I do get an
error stating that sql server sp2 and less is not
supported on the version of windows I am running. I
doubt this is the problem, because I have updated it to
sp3.
Any help you can provide would be greatly appreciated.
Thanks,
JasonHi
dbo is the database owner if you do
sp_helpdb dbname
Then it will show the database owner. In general it is usually better to
have dbo as the owner of the objects in the database.
If you want to change the database owner look up the stored procedure
sp_changedbowner in Books Online.
For granting access to the database server check out sp_grantlogin and for
the database see sp_grantdbaccess. It is usually better to grant access to
Windows Groups (which your users belong to) rather than to individual
accounts.
The error message regarding SP3 is normal, upgrading straight away to SP3
means that you will be ok.
John
"jason" <anonymous@.discussions.microsoft.com> wrote in message
news:d9a701c43abb$9b48c770$a601280a@.phx.gbl...
> I have an access database project that is having problems
> since I have migrated the database it connects to. It
> connects fine, but all of the server objects show (dbo)
> after them. I think the (dbo) issue is related to the
> sql user account setup. The user account no longer maps
> to a domain account as it had been with the previous
> server.
> Here is some background information about the migration
> and installation of sql server:
> I am trying to migrate a database to a new server that is
> running windows 2003 and sql server 2000 sp3. I have
> copied a backup from the old server to the new server,
> and performed a restore. However, when I restore the
> database, the users in the database no longer have the
> login name (the domain account associated with the user)
> mapped. I can delete the accounts and recreate them, all
> except the dbo user, which has a lot of objects owned by
> it. Is there a way to remap this dbo user to the domain
> account that I previously had it mapped to?
> During the installation of sql server 2k, I do get an
> error stating that sql server sp2 and less is not
> supported on the version of windows I am running. I
> doubt this is the problem, because I have updated it to
> sp3.
> Any help you can provide would be greatly appreciated.
> Thanks,
> Jason

problem migrating db

I have an access database project that is having problems
since I have migrated the database it connects to. It
connects fine, but all of the server objects show (dbo)
after them. I think the (dbo) issue is related to the
sql user account setup. The user account no longer maps
to a domain account as it had been with the previous
server.
Here is some background information about the migration
and installation of sql server:
I am trying to migrate a database to a new server that is
running windows 2003 and sql server 2000 sp3. I have
copied a backup from the old server to the new server,
and performed a restore. However, when I restore the
database, the users in the database no longer have the
login name (the domain account associated with the user)
mapped. I can delete the accounts and recreate them, all
except the dbo user, which has a lot of objects owned by
it. Is there a way to remap this dbo user to the domain
account that I previously had it mapped to?
During the installation of sql server 2k, I do get an
error stating that sql server sp2 and less is not
supported on the version of windows I am running. I
doubt this is the problem, because I have updated it to
sp3.
Any help you can provide would be greatly appreciated.
Thanks,
JasonHi
dbo is the database owner if you do
sp_helpdb dbname
Then it will show the database owner. In general it is usually better to
have dbo as the owner of the objects in the database.
If you want to change the database owner look up the stored procedure
sp_changedbowner in Books Online.
For granting access to the database server check out sp_grantlogin and for
the database see sp_grantdbaccess. It is usually better to grant access to
Windows Groups (which your users belong to) rather than to individual
accounts.
The error message regarding SP3 is normal, upgrading straight away to SP3
means that you will be ok.
John
"jason" <anonymous@.discussions.microsoft.com> wrote in message
news:d9a701c43abb$9b48c770$a601280a@.phx.gbl...
> I have an access database project that is having problems
> since I have migrated the database it connects to. It
> connects fine, but all of the server objects show (dbo)
> after them. I think the (dbo) issue is related to the
> sql user account setup. The user account no longer maps
> to a domain account as it had been with the previous
> server.
> Here is some background information about the migration
> and installation of sql server:
> I am trying to migrate a database to a new server that is
> running windows 2003 and sql server 2000 sp3. I have
> copied a backup from the old server to the new server,
> and performed a restore. However, when I restore the
> database, the users in the database no longer have the
> login name (the domain account associated with the user)
> mapped. I can delete the accounts and recreate them, all
> except the dbo user, which has a lot of objects owned by
> it. Is there a way to remap this dbo user to the domain
> account that I previously had it mapped to?
> During the installation of sql server 2k, I do get an
> error stating that sql server sp2 and less is not
> supported on the version of windows I am running. I
> doubt this is the problem, because I have updated it to
> sp3.
> Any help you can provide would be greatly appreciated.
> Thanks,
> Jason