EXEC SQL CONNECT TO

We tried to use the embedded “EXEC SQL CONNECT TO servername.dbname USER u
name.password” but we kept getting the error message: “Server: Msg 156,
Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.”
We tried the upper / lower case of the “connect to”, with / without the
" around the server name, database name, user name, but none of them work. W
hat did we do wrong?
Thanks in advance for your help.This does not in any way seem to be valid SQL Server SQL language construct.
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> We tried to use the embedded "EXEC SQL CONNECT TO servername.dbname USER uname.pas
sword" but we
kept getting the error message: "Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'TO'."
> We tried the upper / lower case of the "connect to", with / without the " around t
he server name,
database name, user name, but none of them work. What did we do wrong?
> Thanks in advance for your help.
>|||This is how it documented in the BOL or the URL below:
connect to:
_01_1g1b.asp" target="_blank">http://msdn.microsoft.com/library/d...>
_01_1g1b.asp
set connection:
_01_5pda.asp" target="_blank">http://msdn.microsoft.com/library/d...>
_01_5pda.asp
"Tibor Karaszi" wrote:

> This does not in any way seem to be valid SQL Server SQL language construc
t...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
> news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> kept getting the error message: "Server: Msg 156, Level 15, State 1, Line
1
> database name, user name, but none of them work. What did we do wrong?
>
>|||I see, this is embedded SQL, supposed to be run through a precompiler. I sup
pose that you have below
inside some C-program and you get the error message when you run the c-progr
am through the
precompiler? Is that the case?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:AB147E8A-3A19-4BDD-9B6C-D47F02C1C020@.microsoft.com...[vbcol=seagreen]
> This is how it documented in the BOL or the URL below:
> connect to:
> rf_01_1g1b.asp" target="_blank">http://msdn.microsoft.com/library/d...
rf_01_1g1b.asp
> set connection:
> rf_01_5pda.asp" target="_blank">http://msdn.microsoft.com/library/d...
rf_01_5pda.asp
>
>
> "Tibor Karaszi" wrote:
>
we[vbcol=seagreen]
name,[vbcol=seagreen]sql

EXEC SQL CONNECT TO

We tried to use the embedded “EXEC SQL CONNECT TO servername.dbname USER uname.password” but we kept getting the error message: “Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.”
We tried the upper / lower case of the “connect to”, with / without the " around the server name, database name, user name, but none of them work. What did we do wrong?
Thanks in advance for your help.
This does not in any way seem to be valid SQL Server SQL language construct...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> We tried to use the embedded "EXEC SQL CONNECT TO servername.dbname USER uname.password" but we
kept getting the error message: "Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'TO'."
> We tried the upper / lower case of the "connect to", with / without the " around the server name,
database name, user name, but none of them work. What did we do wrong?
> Thanks in advance for your help.
>
|||This is how it documented in the BOL or the URL below:
connect to:
http://msdn.microsoft.com/library/de...rf_01_1g1b.asp
set connection:
http://msdn.microsoft.com/library/de...rf_01_5pda.asp
"Tibor Karaszi" wrote:

> This does not in any way seem to be valid SQL Server SQL language construct...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
> news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> kept getting the error message: "Server: Msg 156, Level 15, State 1, Line 1
> database name, user name, but none of them work. What did we do wrong?
>
>
|||I see, this is embedded SQL, supposed to be run through a precompiler. I suppose that you have below
inside some C-program and you get the error message when you run the c-program through the
precompiler? Is that the case?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:AB147E8A-3A19-4BDD-9B6C-D47F02C1C020@.microsoft.com...[vbcol=seagreen]
> This is how it documented in the BOL or the URL below:
> connect to:
> http://msdn.microsoft.com/library/de...rf_01_1g1b.asp
> set connection:
> http://msdn.microsoft.com/library/de...rf_01_5pda.asp
>
>
> "Tibor Karaszi" wrote:
we[vbcol=seagreen]
name,[vbcol=seagreen]

Exclusive access could not be obtained because the database is in use.

I have a test database that is automatically restored from the live database each day at 2:30AM. I recently started receiving this message:

Exclusive access could not be obtained because the database is in use.

and the restore is not able to occur.

Any ideas on how I can prevent this from happening?

You can't restore a db when it is in use, meaning that there are connections open to the database. Somehow, somewhere, connections are being left open to your database. Before you leave for the day, run the following:

exec sp_who

This will tell you who (username and machine name) has an open connection to the database.

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

sql

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

Exclamation Message in Security logins

Hi,
Ok maybe I posted this in the wrong thread before.
I restored 4 databases onto another SQL server and added
sql logins. When I click on a login under
Security I get this Exclamation message?
"One or more databases are inaccessible and will not be displayed in the
database access tab"
I don't understand this message and couldn't find anything on it.
thanks
gv
gv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...
|||gv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...
|||Thank You!!!!
:<)
"gv" <viator.gerry@.gmail.com> wrote in message
news:uPoo3Rj7GHA.140@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
>
>

Exclamation Message in Security logins

Hi,
Ok maybe I posted this in the wrong thread before.
I restored 4 databases onto another SQL server and added
sql logins. When I click on a login under
Security I get this Exclamation message?
"One or more databases are inaccessible and will not be displayed in the
database access tab"
I don't understand this message and couldn't find anything on it.
thanks
gvgv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...|||gv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...|||Thank You!!!!
:< )
"gv" <viator.gerry@.gmail.com> wrote in message
news:uPoo3Rj7GHA.140@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
>
>

Exclamation Message in Security logins

Hi,
Ok maybe I posted this in the wrong thread before.
I restored 4 databases onto another SQL server and added
sql logins. When I click on a login under
Security I get this Exclamation message?
"One or more databases are inaccessible and will not be displayed in the
database access tab"
I don't understand this message and couldn't find anything on it.
thanks
gvgv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...|||gv wrote:
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
That means one or more of your databases are unavailable, possibly in
Standby or Suspect mode...|||Thank You!!!!
:<)
"gv" <viator.gerry@.gmail.com> wrote in message
news:uPoo3Rj7GHA.140@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Ok maybe I posted this in the wrong thread before.
> I restored 4 databases onto another SQL server and added
> sql logins. When I click on a login under
> Security I get this Exclamation message?
> "One or more databases are inaccessible and will not be displayed in the
> database access tab"
> I don't understand this message and couldn't find anything on it.
> thanks
> gv
>
>

Exchanging message between two servers

If i want to exchange message between two different instances (or) databases in two different servers, then I have to enable the transport protocol which wil be disabled by default in service broker.I read in some article that some registry settings has to be changed.So can someone suggest on the same.

"Connection attempt failed with error: '10061(No connection could be made because the target machine actively refused it.)'."Im getting this error in the initiating database.The message that i sent still exists in thr Transmission queue fo initiating database.

It will be better if someone sends a sample or the steps to follow to communicate between databases in two different servers.So that i can cross check with mine.

here is a pretty good article on writing distributed service broker applications:

http://www.sqlservercentral.com/columnists/sindukuri/2797.asp

Exchanging message between two servers

If i want to exchange message between two different instances (or) databases in two different servers, then I have to enable the transport protocol which wil be disabled by default in service broker.I read in some article that some registry settings has to be changed.So can someone suggest on the same.

"Connection attempt failed with error: '10061(No connection could be made because the target machine actively refused it.)'." Im getting this error in the initiating database.The message that i sent still exists in thr Transmission queue fo initiating database.

It will be better if someone sends a sample or the steps to follow to communicate between databases in two different servers.So that i can cross check with mine.

here is a pretty good article on writing distributed service broker applications:

http://www.sqlservercentral.com/columnists/sindukuri/2797.asp

ExceptionMessageBox

I am trying to get the Exception Message Box to work in a script task in SSIS.

I am using the example from http://msdn2.microsoft.com/en-us/library/ms166340.aspx almost verbatum.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.MessageBox

Public Sub Main()

'

' Define the message and caption to display.

Dim str As String = "Do you want to push to Production?"

Dim caption As String = "Zip Push"

Dim var As Variables

' Show the exception message box with Yes and No buttons.

Dim box As ExceptionMessageBox = New ExceptionMessageBox(str, caption)

box.DefaultButton = ExceptionMessageBoxDefaultButton.Button2

box.Symbol = ExceptionMessageBoxSymbol.Question

box.Buttons = ExceptionMessageBoxButtons.YesNo

'box.SetButtonText("Yes", "No", "Cancel")

If Windows.Forms.DialogResult.Yes = box.Show(CType(Me,Windows.Forms.IWin32Window)) Then

Dts.Variables("Production").Value = True

End If

'

Dts.TaskResult = Dts.Results.Success

End Sub

And yet all I get is the following error

Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.

Has anyone gotten this to work?

BTW: I am running SQL 2005 SP2

Just use MsgBox("...")

You don't even need to reference any assembly for it.

By the way, what are you trying to do here? SSIS is supposed to be a batch-oriented process, not interactive.

-Jamie

|||MsgBox works, but it's too limited. I wanted the added functionality of ExceptionMessageBox. Besides, my question was not "How do I NOT use ExceptionMessageBox?".|||

S1monk wrote:

MsgBox works, but it's too limited. I wanted the added functionality of ExceptionMessageBox. Besides, my question was not "How do I NOT use ExceptionMessageBox?".

Was it? I've just re-read your original post and didn't see that question anywhere. All I saw was "Has anyone gotten this to work?"

I'm afraid I don't know why your code isn't working. What exactly do you want to do?

-Jamie

|||

Jamie Thomson wrote:

What exactly do you want to do?

I second this question. Also Jamie's comment about SSIS being a batch utility, not an interactive one.

|||

What I would like to do is use ExceptionMessageBox to display a message and allow me to customize the buttons, which I believe you cannot do with MsgBox. The example I used is just a simplest case taken from http://msdn2.microsoft.com/en-us/library/ms166340.aspx to test the functionality. I cannot get it to run. All I get is

Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.

Any help would be greatly appreciated, but comments like use something else and SSIS is a batch utility are not helpfull.

|||

S1monk wrote:

What I would like to do is use ExceptionMessageBox to display a message and allow me to customize the buttons, which I believe you cannot do with MsgBox. The example I used is just a simplest case taken from http://msdn2.microsoft.com/en-us/library/ms166340.aspx to test the functionality. I cannot get it to run. All I get is

I don't know for sure but my guess from the error message is that this simply cannot be done. The script task is not designed to be used interactively as you are attempting to do.

S1monk wrote:

Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.

Any help would be greatly appreciated, but comments like use something else and SSIS is a batch utility are not helpfull.

If something isn't working then I would have thought suggesting an alternative was perfectly good advice. Likewise the advice about SSIS being a batch utility was intended to be helpful and, as I think your error message proves, this advice has been borne out to be true.

The question "What exactly are you trying to do?" was a lead-in to suggesting an alternative that WOULD work. Due to the very nature of SSIS, prompting users for input from INSIDE a package is not an appropriate thing to do - better to prompt them elsewhere and pass that information into the package so that it can act upon it dynamically.

I was trying to proffer some simple advice but obviously that advice is not appreciated hence I won't post on this thread again. I have no desire to help someone that doesn't value that help. Good luck in finding a solution to your problem.

-Jamie

|||

The type you are passing to the Show method is not a window. You have to catch a handle of the top window and pass it to this method. Perhaps, even null could work but it might create a weird effects (like popping up in the backround or something similar).

|||

The error is because Me is not a window, it does not implement IWin32Window, so the cast is invalid - CType(Me,Windows.Forms.IWin32Window)

You do not have a Form, because SSIS is just not aimed at being an interactive tool, which is the point others have tried to highlight.

You could get the same functionality with old MsgBox or System.Windows.Forms.MessageBox (same thing really), it supports Yes/No/Cancel if you wish.

Sorry for going so far off the question, but I thought it might help provide a solution.

|||

I found a way to make it work

Option Strict On

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.MessageBox

Public Class ScriptMain

Public Sub Main()

Dim str As String = "Are you sure you want to delete file 'c:\somefile.txt'?"

Dim caption As String = "Confirm File Deletion"

Dim win As Windows.Forms.IWin32Window

' Show the exception message box with Yes and No buttons.

Dim box As ExceptionMessageBox = New ExceptionMessageBox(str, _

caption, ExceptionMessageBoxButtons.YesNo, _

ExceptionMessageBoxSymbol.Question, _

ExceptionMessageBoxDefaultButton.Button2)

If Windows.Forms.DialogResult.Yes = box.Show(win) Then

' Delete the file.

End If

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

|||[Shaking head]

I just don't understand why you'd want to do this. I just don't understand........ SSIS is not an interactive tool - your solution only works when debugging SSIS.

[/Shaking head]|||I don't understand why you are all so hung up on SSIS being batch or interactive. It is what it is and it works how you use it. We do a lot of data loads on one server and then push the data to different environments (Development, QA, Staging, Production). With DTS we had a seperate package for each push. It leads to a LOT of packages. My goal was to clean this up a little and make the packages a little more versatile. I don't know what flavor of SSIS you are using, but the above code works just fine in my INTERACTIVE production environment.|||

S1monk wrote:

I don't understand why you are all so hung up on SSIS being batch or interactive. It is what it is and it works how you use it. We do a lot of data loads on one server and then push the data to different environments (Development, QA, Staging, Production). With DTS we had a seperate package for each push. It leads to a LOT of packages. My goal was to clean this up a little and make the packages a little more versatile. I don't know what flavor of SSIS you are using, but the above code works just fine in my INTERACTIVE production environment.

HAHAHAHA. Yeah, okay.|||I hope we can agree then, that when developing, testing or maintaining in BIDS, dialogs can be useful.

The use of dialogs IN SSIS packages is not a good idea in general, but even so, that's not a universal truth (dialogs + SSIS = misunderstanding SSIS and its intent) .

As long as the System::InteractiveMode variable is checked in advance, dialogs are fine. SSIS provides this variable since a good amount of time is spent in development and maintenance inside of BIDS, where its nice to make changes to variables without changing package source code.

Its for this same reason ("debugging and maintenance") that many shells and language interpreters have both an interactive mode ( bash, python,ruby, and powershell come to mind ) and may be run interactively as well as non-interactively.

exception_int_divied_by_zero..

Hi all, once I try to change to the design mode of any table, an error
message "Exception_Int_Divided_By_Zero" display, what does this mean? And
alos, when I try to generate SQL script, drop a view, use DTS to export
data, I also receive this error message, any idea how to solve it?
begin 666 Au Yeung.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..D%U(%EE=6YG#0I&3CI!=2!9
M975N9PT*14U!24P[4%)%1CM)3E1%4DY%5#IA<G1H=7)A>4!H;W1M86EL+F-O
D;0T*4D56.C(P,#4P-C$S5# W-#@.R-EH-"D5.1#I60T%21 T*
`
endYou mean, you are getting this error from Enterprise Manager? If so, could
you try the same from Enterprise Manager on another machine?
What exactly are you trying to do? You can probably do the same using
scripts from Query Analyzer.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chung" <chung@.utaxhk.com.hk> wrote in message
news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
Hi all, once I try to change to the design mode of any table, an error
message "Exception_Int_Divided_By_Zero" display, what does this mean? And
alos, when I try to generate SQL script, drop a view, use DTS to export
data, I also receive this error message, any idea how to solve it?|||I found that this error is associates with the database, not with the
Enterprise Management. Because not all of database has these error, only one
database. And even I backup, and restore it, the error still there. Without
EM, how can I generate the SQL script of the database? and copy data from
the database?
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:%23HfYeNAcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> You mean, you are getting this error from Enterprise Manager? If so, could
> you try the same from Enterprise Manager on another machine?
> What exactly are you trying to do? You can probably do the same using
> scripts from Query Analyzer.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Chung" <chung@.utaxhk.com.hk> wrote in message
> news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
> Hi all, once I try to change to the design mode of any table, an error
> message "Exception_Int_Divided_By_Zero" display, what does this mean? And
> alos, when I try to generate SQL script, drop a view, use DTS to export
> data, I also receive this error message, any idea how to solve it?
>
>
>|||Sounds more like either your connection, API, or database environment
settings...like SET NUMERIC_ROUNDABORT or SET ARITHABORT. Check your
database properties.
Another possibility would be that your client tools have been corrupted.
Did you install the Beta 1 for SQL Server 2005? There was an MDAC 9 Beta 1
that was included that caused all sorts of issues with SQL Server 2000
tools. You basically have to do a file replace and registry clean to clear
everything off. A new download of an older MDAC won't fix anything becuase
version 9 is newer, the MDAC will not deploy.
Sincerely,
Anthony Thomas
"Chung" <chung@.utaxhk.com.hk> wrote in message
news:u$cDkLLcFHA.3120@.TK2MSFTNGP12.phx.gbl...
I found that this error is associates with the database, not with the
Enterprise Management. Because not all of database has these error, only one
database. And even I backup, and restore it, the error still there. Without
EM, how can I generate the SQL script of the database? and copy data from
the database?
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com>
¼¶¼g©ó¶l¥ó·s»D:%23HfYeNAcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> You mean, you are getting this error from Enterprise Manager? If so, could
> you try the same from Enterprise Manager on another machine?
> What exactly are you trying to do? You can probably do the same using
> scripts from Query Analyzer.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Chung" <chung@.utaxhk.com.hk> wrote in message
> news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
> Hi all, once I try to change to the design mode of any table, an error
> message "Exception_Int_Divided_By_Zero" display, what does this mean? And
> alos, when I try to generate SQL script, drop a view, use DTS to export
> data, I also receive this error message, any idea how to solve it?
>
>
>

EXCEPTION_FLT_UNDERFLOW

Hi,
I'm getting the message 'SqlDumpExceptionHandler: Process 12 generated fatal
exception c0000093 EXCEPTION_FLT_UNDERFLOW. SQL Server is terminating this
process.' and my connection is terminated.
what causes this error and how is it fixed.
using sql2k with sp3.
thanks.> Hi,
> I'm getting the message 'SqlDumpExceptionHandler: Process 12
> generated fatal exception c0000093 EXCEPTION_FLT_UNDERFLOW. SQL
> Server is terminating this process.' and my connection is terminated.
> what causes this error and how is it fixed.
> using sql2k with sp3.
Try checking the table with DBCC CHECKTABLE. DBCC DBREINDEX sometimes fixes
the problem. If it does not, it looks like a MS PSS case for me
(http://support.microsoft.com/defaul...estion.asp&SD=G
N&FR=0)
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||thanks Sebastian,
I've been messing with the data and it appears that earlier versions of MSSQ
L were not strict with what you entered into a float column - you could over
load the datatype.
thanks for your help though.

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)

I just received this message:

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)

I am completely dead in the water. Cannot connect to SSAS 2005. HELP!

hello,

it is not quite clear what are you trying to do when the exception happens. Is the system really running low on memory?

the comment above seems to suggest that you are trying to connect to SSAS, however the exception seems to come from SMO (?) which is an OM for working with SQL server (relational).

if the issue is that you are unable to connect to MSAS, could you please provide more information as to what are the sequence of actions you take (also how you connect -- from Management Studio or in some different way; if from Management Studio - what do you enter in connection dialog etc.), and more information about when the exception occures and stack trace and other info about the error (in management studio error msg usually has "Show Technical Details" button)?

thanks a lot,

|||

I am trying to do the initial load of SSAS 2005. Just loading the Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe executable to get to the SSAS databases is just stopped. I had to "split" the SSAS databses to get them imported into three splits. The SSAS 2000 has 35 dimensions, 49 cubes, and 4 virtual FACT cubes. This was load #2 out of 3. I had just completed the cube builds on 31 out of 49 cubes. Had just completed 138 million rows of populating the SSAS 2k5 database. I had done tests that put the total row count to 240 to 280 million rows when completed. The size of the total SSAS DB is currently projected at 16-20 GB when completed. I tried to re-impliment the 2nd set of cubes and now SSAS 2k5 will not even start. I had tried the /PAE as well as the /3GB swtiches with no success. I tried each switch individually with full reboot each. No success. . Help.

|||

Update. I took the switch /3GB OUT of the boot.ini and was successful in getting the SS Management Studio to at least start. Now, as I try to open cubes to work with them in Management Studio, it gives me the following error:

Excetion of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)

Is there a 2GB memory limit in SSAS? When you put in the /3GB switch it actually fails to load initially. Is there some caching/hashing algorythm that allows the standard non /3GB switch to be successfule and the /3GB switch enablement to fail in high-memory usage cases? This is a bit perplexing. The 2000 OLAP database runs at about 1.7 GB TOTAL in production today (the source for the migration wizard), and it is stable. Using the Database Migration Wizard (9.0.2047.0) I could NOT get it to migrate the whole 2k OLAP database. It has the memory exception error. I had to "split" it up (Migtration Wizard) to get the the dimensions and cubes to load by chunking it into SSAS 2k5. I was successful on the first half of the 2k OLAP migration to SSAS 2k5 mmigrating 31 out of 49 cubes. I have subsequently loaded all of the dimensions (all 35 from above) with data and cubes (about 31 cubes with about 137 million rows total) with data. I tried to migrate the other outstanding 16 cubes and all errors started happening and SSAS 2k5 Management Studio is failing to even load. My hardware/OS config is as follows:

C drive - 10 GB (RAID 1)

D drive - 8 GB (4095 pagefile is here) (RAID 10)

E drive - 50GB (RAID 10)

RAM - 8GB

OS - Server 2003 Enterprise (Open License Version 5.2 Build 3790.srv_03_sp1_rtm.050324-1447: ServicePack 1)

DB - SQL Server 2005 Enterprise - SP1/041806 installed 042006 - Full install of all components. I have stopped all SQL Services accept SQL Browser and SSAS services.

Does anyone else have this issue with SSAS 2005 and hitting the ceiling-memory limit?

How can 2000 OLAP run at 1.7 GB and be stable and SSAS 2k5 fails to even load the exact same data using Microsoft's own toolset?

I am seeking to find the solution to this issue and are on an extremely aggreesive schedule. HELP!

|||

Okay,

I have tried something to see if it will work and here is where I am at. I cleared the transferred SSAS DB from Management Studio completely. I re-migrated (in three MigratioWizard.exe sessions) most (32 dimensions and 38 cubes) of the existing objects into a new "stand-alone" SSAS 2K5 database. I then "imported" the now almost fully deployed (all that is left are the FACT tables) SSAS 2K5 into BI Studio successfully using the import funtion in BI Studio! I then backed up and deleted the SSAS 2K5 DB from Management Studio. Now the bad news. I was able to get the BI project to "build" with no errors. Now, I am back to the SAME Exception of type

'System.OutOfMemoryException' was thrown

error I had when only "trying" to partially migrate and populate even 50% of the cubes in Management Studio (see previous postings). Now, this same error shows up under the errors bottom-left dialog box in BI studio with no other explanantions. I have been combing the internet trying to get a solution. Can anyone out there help?

|||

We have a defect in RTM and SP1 builds, that OutOfMemoryException occurs when SQL Management Studio connects to AS2005 with large database(s). We are fixing this for SP2.

Meanwhile, the work-around would be to use Visual Studio to connect to AS2005 to administer or process objects.

Or to temporary separate databases on different AS2005 instances (then script each of them and run the scripts to combine them back on a single AS2005 instance).

Adrian Dumitrascu.

|||

The deimensions and cubes are now in ONE instance of BI Studio 2005. There are no more SQL or ANY databases that are in this box or connected to this instance. I am running a DELL 2850 with DUAL 3.8 Ghz procs, 8GB or RAM, and ALL data partitions that SQL 2K5 touches are RAID 10/15k hard drives. This should be "flying" on 2K5 from all of the press. These databases are running perfectly on SQL 2000 AS at 1.7 GB of RAN with no issues. I want to be crystal-clear with my response. The objects are ALL now in BI studio (definitions of only dimensions and cubes) and the SQL 2K5 BI studio throws the SAME error whether I try to use the migration tool OR try to deploy using BI Studio. I need help or this is going to be a Microsoft 2K5 disaster. Is there some work around to data sizes coming from 2000 AS? I cannot be the ONLY person that has seen this complete stoppage. If you wish to continue on this most-excellent quest to actually see the realization of the intent of "Project-REAL" then please give me a ring at 512.845.4950 or reply to this so that we can overcome this very intense (and complete stoppage) road block. Please help. I do not mean to be forward and apologize. This project should have taken approximately 60-80 hours and it is now well into the hundreds of hours. I am merely a fellow MS-er just trying to overcome adversity and see this through to success for us all.

:)

|||

I will investigate this and will try to reproduce it on our side to debug and see if we can provide a work-around or a quick fix.

You migrated the AS2000 database with Migration Wizard ? If yes, can you run the wizard again to generate the migration script ? (there is an option on the first page to either migrate to AS2005 or generate the script). Once you have the script, please archive it as .zip and send it to me at adumitrascu at hotmail dot com.

Adrian Dumitrascu.

|||

I forgot to mention, please double check and remove sensitive meta-data in the AS2000 database and the migration script. Especially verify the connection strings for data sources (for passwords - just change the connection strings in the script to bogus values, I don't need them to be correct). Also, if you have MDX calculations that you want to keep private, please remove them from the script (search for "<Commands>" in the script to review them).

Adrian Dumitrascu.

|||

Adrian,

Thanks again for your help here. You have been great. We do have some issues with running out of memory that I will be posting. Here is the error:

Errors and Warnings from Response
Memory error: Allocation failure : Not enough storage is available to process this command. .
Memory error: Allocation failure : Not enough storage is available to process this command. .
Errors in the metadata manager. An error occurred when loading the AggregationDesign aggregation design, from the file, '\\?\E:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Business Report System ADW.1.db\TA_2003.0.cub\TA_2003.0.det\AggregationDesign.0.agg.xml'.
Error Code = 0x8007000E, External Code = 0x00000000:.

What can I do now about this? We seem to be hitting every roadblock that can be during this POC. Any assistance will be greatly appreciated.

krj

|||

Hi Kjr,

I have the same problem that you had before the last post.

What solution you had about this problem System.OutOfMemoryException with MigrationWizard? You can help me?

tks,

Eder F. Dias

|||

We are actually still working on the issue. The last recommendation was to move UP to the 64-BIT version of SSAS 2k5. As we continue to peice-meal the cubes into the 32-BIT solution there is an entire discussion about maintenace and care of the SSAS DB going forward. Given the application memory "ceiling" that you may be experiencing it may make sense to try the 64-bit version of SSAS 2k5. This would, as I understand it, help to allocate the required memory in a manner that would potentially allow thw application to run more freely.

krj

|||

Thanks Kjr for answer.

Actually my environment is:

- Intel Xeon CPu 3.4 GHZ, 3.93GB RAM

- Windows 2003 Enterprise x64 SP1

- SQL Server 2005 Enterprise x64 (SP1 - build 2153)

How you can see I working in a 64-Bit Environment, but the exception still happen. I don't know why. Maybe my environment isn't the best? What can i do for became it better?

Somethig strange is that the MigrationWizard.exe process work in a 32-Bit even in x64 enviroment. There is another application that work in 64-Bit?

More informations about my server:

OS Name Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name SRVBRASIL31
System Manufacturer HP
System Model ProLiant DL380 G4
System Type x64-based PC
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
BIOS Version/Date HP P51, 10/10/2005
SMBIOS Version 2.3
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Boot Device \Device\HarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone E. South America Standard Time
Total Physical Memory 4,031.28 MB
Available Physical Memory 1.70 GB
Total Virtual Memory 5.69 GB
Available Virtual Memory 3.23 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys

tks a lot

Eder F Dias

|||

Adrian,

You mentioned there is a fix for this coming in SP2. Is this fix available as a Hotfix now or only as a part of SP2?

- Steve

|||

Steve,

I had varying degrees of success and are too trying to keep things running and get them stable. What I had done to extend the envelope is to try give the "system" as much "resources" as I could. First, I had 8 GB of RAM to your 4 GB. This may be a critical point. Remember, in most cases WIndows needs a memory to run as an operating system. In my case on the 32-bit platform this was around 600/700 MB for just the OS. On the 64-bit I am not so sure of the base OS memory requirements. For SQL 2K5 though (on the 32-bit platform) it was recommneded to me to put in the /3GB switch in the boot.ini for SQL 2K5 to allow for higher memory addressing. Also there is an AWE switch in "spconfig" (properties of the server in SQL 2K5) to allow for this memory to be used.

The reason that I am going over this with you is that it seems you are having the EXACT behavior in the 64-bit version that I ma having in the 32-bit version. It seemed logical to assume that I was hitting the wall because of the 32-bit addressing limitations (as told to me by Microsoft). I will continue on the next post.

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)

I just received this message:

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)

I am completely dead in the water. Cannot connect to SSAS 2005. HELP!

hello,

it is not quite clear what are you trying to do when the exception happens. Is the system really running low on memory?

the comment above seems to suggest that you are trying to connect to SSAS, however the exception seems to come from SMO (?) which is an OM for working with SQL server (relational).

if the issue is that you are unable to connect to MSAS, could you please provide more information as to what are the sequence of actions you take (also how you connect -- from Management Studio or in some different way; if from Management Studio - what do you enter in connection dialog etc.), and more information about when the exception occures and stack trace and other info about the error (in management studio error msg usually has "Show Technical Details" button)?

thanks a lot,

|||

I am trying to do the initial load of SSAS 2005. Just loading the Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe executable to get to the SSAS databases is just stopped. I had to "split" the SSAS databses to get them imported into three splits. The SSAS 2000 has 35 dimensions, 49 cubes, and 4 virtual FACT cubes. This was load #2 out of 3. I had just completed the cube builds on 31 out of 49 cubes. Had just completed 138 million rows of populating the SSAS 2k5 database. I had done tests that put the total row count to 240 to 280 million rows when completed. The size of the total SSAS DB is currently projected at 16-20 GB when completed. I tried to re-impliment the 2nd set of cubes and now SSAS 2k5 will not even start. I had tried the /PAE as well as the /3GB swtiches with no success. I tried each switch individually with full reboot each. No success. . Help.

|||

Update. I took the switch /3GB OUT of the boot.ini and was successful in getting the SS Management Studio to at least start. Now, as I try to open cubes to work with them in Management Studio, it gives me the following error:

Excetion of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)

Is there a 2GB memory limit in SSAS? When you put in the /3GB switch it actually fails to load initially. Is there some caching/hashing algorythm that allows the standard non /3GB switch to be successfule and the /3GB switch enablement to fail in high-memory usage cases? This is a bit perplexing. The 2000 OLAP database runs at about 1.7 GB TOTAL in production today (the source for the migration wizard), and it is stable. Using the Database Migration Wizard (9.0.2047.0) I could NOT get it to migrate the whole 2k OLAP database. It has the memory exception error. I had to "split" it up (Migtration Wizard) to get the the dimensions and cubes to load by chunking it into SSAS 2k5. I was successful on the first half of the 2k OLAP migration to SSAS 2k5 mmigrating 31 out of 49 cubes. I have subsequently loaded all of the dimensions (all 35 from above) with data and cubes (about 31 cubes with about 137 million rows total) with data. I tried to migrate the other outstanding 16 cubes and all errors started happening and SSAS 2k5 Management Studio is failing to even load. My hardware/OS config is as follows:

C drive - 10 GB (RAID 1)

D drive - 8 GB (4095 pagefile is here) (RAID 10)

E drive - 50GB (RAID 10)

RAM - 8GB

OS - Server 2003 Enterprise (Open License Version 5.2 Build 3790.srv_03_sp1_rtm.050324-1447: ServicePack 1)

DB - SQL Server 2005 Enterprise - SP1/041806 installed 042006 - Full install of all components. I have stopped all SQL Services accept SQL Browser and SSAS services.

Does anyone else have this issue with SSAS 2005 and hitting the ceiling-memory limit?

How can 2000 OLAP run at 1.7 GB and be stable and SSAS 2k5 fails to even load the exact same data using Microsoft's own toolset?

I am seeking to find the solution to this issue and are on an extremely aggreesive schedule. HELP!

|||

Okay,

I have tried something to see if it will work and here is where I am at. I cleared the transferred SSAS DB from Management Studio completely. I re-migrated (in three MigratioWizard.exe sessions) most (32 dimensions and 38 cubes) of the existing objects into a new "stand-alone" SSAS 2K5 database. I then "imported" the now almost fully deployed (all that is left are the FACT tables) SSAS 2K5 into BI Studio successfully using the import funtion in BI Studio! I then backed up and deleted the SSAS 2K5 DB from Management Studio. Now the bad news. I was able to get the BI project to "build" with no errors. Now, I am back to the SAME Exception of type

'System.OutOfMemoryException' was thrown

error I had when only "trying" to partially migrate and populate even 50% of the cubes in Management Studio (see previous postings). Now, this same error shows up under the errors bottom-left dialog box in BI studio with no other explanantions. I have been combing the internet trying to get a solution. Can anyone out there help?

|||

We have a defect in RTM and SP1 builds, that OutOfMemoryException occurs when SQL Management Studio connects to AS2005 with large database(s). We are fixing this for SP2.

Meanwhile, the work-around would be to use Visual Studio to connect to AS2005 to administer or process objects.

Or to temporary separate databases on different AS2005 instances (then script each of them and run the scripts to combine them back on a single AS2005 instance).

Adrian Dumitrascu.

|||

The deimensions and cubes are now in ONE instance of BI Studio 2005. There are no more SQL or ANY databases that are in this box or connected to this instance. I am running a DELL 2850 with DUAL 3.8 Ghz procs, 8GB or RAM, and ALL data partitions that SQL 2K5 touches are RAID 10/15k hard drives. This should be "flying" on 2K5 from all of the press. These databases are running perfectly on SQL 2000 AS at 1.7 GB of RAN with no issues. I want to be crystal-clear with my response. The objects are ALL now in BI studio (definitions of only dimensions and cubes) and the SQL 2K5 BI studio throws the SAME error whether I try to use the migration tool OR try to deploy using BI Studio. I need help or this is going to be a Microsoft 2K5 disaster. Is there some work around to data sizes coming from 2000 AS? I cannot be the ONLY person that has seen this complete stoppage. If you wish to continue on this most-excellent quest to actually see the realization of the intent of "Project-REAL" then please give me a ring at 512.845.4950 or reply to this so that we can overcome this very intense (and complete stoppage) road block. Please help. I do not mean to be forward and apologize. This project should have taken approximately 60-80 hours and it is now well into the hundreds of hours. I am merely a fellow MS-er just trying to overcome adversity and see this through to success for us all.

:)

|||

I will investigate this and will try to reproduce it on our side to debug and see if we can provide a work-around or a quick fix.

You migrated the AS2000 database with Migration Wizard ? If yes, can you run the wizard again to generate the migration script ? (there is an option on the first page to either migrate to AS2005 or generate the script). Once you have the script, please archive it as .zip and send it to me at adumitrascu at hotmail dot com.

Adrian Dumitrascu.

|||

I forgot to mention, please double check and remove sensitive meta-data in the AS2000 database and the migration script. Especially verify the connection strings for data sources (for passwords - just change the connection strings in the script to bogus values, I don't need them to be correct). Also, if you have MDX calculations that you want to keep private, please remove them from the script (search for "<Commands>" in the script to review them).

Adrian Dumitrascu.

|||

Adrian,

Thanks again for your help here. You have been great. We do have some issues with running out of memory that I will be posting. Here is the error:

Errors and Warnings from Response
Memory error: Allocation failure : Not enough storage is available to process this command. .
Memory error: Allocation failure : Not enough storage is available to process this command. .
Errors in the metadata manager. An error occurred when loading the AggregationDesign aggregation design, from the file, '\\?\E:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Business Report System ADW.1.db\TA_2003.0.cub\TA_2003.0.det\AggregationDesign.0.agg.xml'.
Error Code = 0x8007000E, External Code = 0x00000000:.

What can I do now about this? We seem to be hitting every roadblock that can be during this POC. Any assistance will be greatly appreciated.

krj

|||

Hi Kjr,

I have the same problem that you had before the last post.

What solution you had about this problem System.OutOfMemoryException with MigrationWizard? You can help me?

tks,

Eder F. Dias

|||

We are actually still working on the issue. The last recommendation was to move UP to the 64-BIT version of SSAS 2k5. As we continue to peice-meal the cubes into the 32-BIT solution there is an entire discussion about maintenace and care of the SSAS DB going forward. Given the application memory "ceiling" that you may be experiencing it may make sense to try the 64-bit version of SSAS 2k5. This would, as I understand it, help to allocate the required memory in a manner that would potentially allow thw application to run more freely.

krj

|||

Thanks Kjr for answer.

Actually my environment is:

- Intel Xeon CPu 3.4 GHZ, 3.93GB RAM

- Windows 2003 Enterprise x64 SP1

- SQL Server 2005 Enterprise x64 (SP1 - build 2153)

How you can see I working in a 64-Bit Environment, but the exception still happen. I don't know why. Maybe my environment isn't the best? What can i do for became it better?

Somethig strange is that the MigrationWizard.exe process work in a 32-Bit even in x64 enviroment. There is another application that work in 64-Bit?

More informations about my server:

OS Name Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name SRVBRASIL31
System Manufacturer HP
System Model ProLiant DL380 G4
System Type x64-based PC
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
BIOS Version/Date HP P51, 10/10/2005
SMBIOS Version 2.3
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Boot Device \Device\HarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone E. South America Standard Time
Total Physical Memory 4,031.28 MB
Available Physical Memory 1.70 GB
Total Virtual Memory 5.69 GB
Available Virtual Memory 3.23 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys

tks a lot

Eder F Dias

|||

Adrian,

You mentioned there is a fix for this coming in SP2. Is this fix available as a Hotfix now or only as a part of SP2?

- Steve

|||

Steve,

I had varying degrees of success and are too trying to keep things running and get them stable. What I had done to extend the envelope is to try give the "system" as much "resources" as I could. First, I had 8 GB of RAM to your 4 GB. This may be a critical point. Remember, in most cases WIndows needs a memory to run as an operating system. In my case on the 32-bit platform this was around 600/700 MB for just the OS. On the 64-bit I am not so sure of the base OS memory requirements. For SQL 2K5 though (on the 32-bit platform) it was recommneded to me to put in the /3GB switch in the boot.ini for SQL 2K5 to allow for higher memory addressing. Also there is an AWE switch in "spconfig" (properties of the server in SQL 2K5) to allow for this memory to be used.

The reason that I am going over this with you is that it seems you are having the EXACT behavior in the 64-bit version that I ma having in the 32-bit version. It seemed logical to assume that I was hitting the wall because of the 32-bit addressing limitations (as told to me by Microsoft). I will continue on the next post.

Exception Error while exporting to Excel

Hello,
I have a report that runs ok at the report manager. However, when I try to
export it to excel
I receive the following error message. Any ideas?
Thank you.
CD
Reporting Services Error
----
--
a.. Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
a.. Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
a.. Item has already been added. Key in dictionary: "20" Key being
added: "20"
----
--
Microsoft Reporting ServicesDo you have a matrix in your report? Does the matrix has subtotals?
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"DC" <DC@.yahoo.com> wrote in message
news:eWjCLQAnEHA.2680@.TK2MSFTNGP15.phx.gbl...
>
> Hello,
> I have a report that runs ok at the report manager. However, when I try to
> export it to excel
> I receive the following error message. Any ideas?
> Thank you.
> CD
> Reporting Services Error
> ----
--
> --
> a.. Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> a.. Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> a.. Item has already been added. Key in dictionary: "20" Key being
> added: "20"
> ----
--
> --
> Microsoft Reporting Services
>|||Yes, I do have a matrix report which has subtotals.
CD
"Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
news:uBPuy1AnEHA.3292@.TK2MSFTNGP15.phx.gbl...
> Do you have a matrix in your report? Does the matrix has subtotals?
> --
> Nico Cristache [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "DC" <DC@.yahoo.com> wrote in message
> news:eWjCLQAnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> >
> >
> > Hello,
> > I have a report that runs ok at the report manager. However, when I try
to
> > export it to excel
> > I receive the following error message. Any ideas?
> >
> > Thank you.
> > CD
> >
> > Reporting Services Error
> ----
> --
> > --
> >
> > a.. Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown. (rrRenderingError) Get Online Help
> > a.. Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown.
> > a.. Item has already been added. Key in dictionary: "20" Key being
> > added: "20"
> >
> ----
> --
> > --
> > Microsoft Reporting Services
> >
> >
>|||Here comes another bug. If you have a matrix with subtotals, you might
experience some difficulty while exporting reports to excel.
Here is the procedure I followed:
-Get rid of the total column.
-Deploy the report, check if export works.
-Put it back in.
-Deploy the report, check if export works.
When I apply column color changes it failed, and gave me the error message.
Even without color I am happy.
Regards,
Cem
"DC" <DC@.yahoo.com> wrote in message
news:eki3H7AnEHA.3628@.TK2MSFTNGP09.phx.gbl...
> Yes, I do have a matrix report which has subtotals.
> CD
>
> "Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
> news:uBPuy1AnEHA.3292@.TK2MSFTNGP15.phx.gbl...
> > Do you have a matrix in your report? Does the matrix has subtotals?
> >
> > --
> > Nico Cristache [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "DC" <DC@.yahoo.com> wrote in message
> > news:eWjCLQAnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> > >
> > >
> > > Hello,
> > > I have a report that runs ok at the report manager. However, when I
try
> to
> > > export it to excel
> > > I receive the following error message. Any ideas?
> > >
> > > Thank you.
> > > CD
> > >
> > > Reporting Services Error
> >
> ----
> > --
> > > --
> > >
> > > a.. Exception of type
> > > Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was
> > > thrown. (rrRenderingError) Get Online Help
> > > a.. Exception of type
> > > Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was
> > > thrown.
> > > a.. Item has already been added. Key in dictionary: "20" Key
being
> > > added: "20"
> > >
> >
> ----
> > --
> > > --
> > > Microsoft Reporting Services
> > >
> > >
> >
> >
>

excell export doesn't work

I have a report with multiple tables, when I trie to export this report to
excell
it reporting services returns an error message:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
Index was out of range. Must be non-negative and less than the size of the
collection. Parameter name: index
can anybody help me solve this?"Dirkjan van Groeningen" <DirkjanvanGroeningen@.discussions.microsoft.com>
wrote in message news:F60A2429-8A44-4DE7-B162-D29FB36091E2@.microsoft.com...
>I have a report with multiple tables, when I trie to export this report to
> excell
> it reporting services returns an error message:
>
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> Index was out of range. Must be non-negative and less than the size of the
> collection. Parameter name: index
> can anybody help me solve this?
Hi, I have the same problem. I have post this issue here 3 times with no
meaningful help.
Please post back if you find a solution.
Good luck,
Bryan

Excel Time-Series Addin Problem

Have an Excel Spreadsheet with two columns and 52 rows (retail sales)

Date Qty

1 5

2 8

etc

Tried to run the above Addin. Error message:

"Session mining object (...) can not be created on this instance."

Any ideas?

Thanks,

Sergei.

You need to set the DataMinnig\AllowSessionMiningModels property to true on your Analysis Services server. Run SQL Server Management Studio, right click on the server in the object explorer, and select properties to set it.