Cannot register SQL server

Hi
It is the same old problem again but mine is a bit strange. I have read
other discussion on this problem but still cannot locate the cause.
I have a primary domain controller (PDC) running W2000 and SQL Server 2000
serving my main database. I have another W2k3 server running SQL Desktop
Engine which locates in the DMZ of my network and serves as my mini database
for displaying data for our web site. Of course the PDC is behind the
firewall. I need to create a package copying data from my main DB to my
mini DB regularly. When I create my package in Enterprise Manager on my
PDC, I cannot find my web server (i.e. the W2k3 server in DMZ). The error
is "Server does not exist or access denied". The strange thing is that I
can register the mini DB server from other PCs in the same network. Hence I
think it is not the problem of firewall. Also, the PDC can register other
SQL server as well but just not the one on my web server.
Can you suggest what I can check to solve the problem?
Thanks
Edwin
Verify that you can connect in both directions using ISQL.exe
ISQL -Stcp:ServerNameHere -E
or
ISQL -Stcp:ServerNameHere -Uuserid -Ppassword
If this fails it will give you a more accurate error message.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||I am getting the failure to register SQL server in Enterprise Manager problem in a new named instance of SQL server on a new MOTION tablet which comes with dot net framework installed. The application requires the dot net framework and will run under MSDE or Personal SQL. I have successful connected to the sample .mdb that ships with the application after installing the application and MSDE on the local machine with no network connection. After logging off the local machine and on to the domain (behind my ZYWALL firewall, I am able to company my main .mdf to the local machine. Does the TRUSTED CONNECTION issue apply here since the main .mdf comes from another machine? Does sa for the MOTION tablet have to be the same as sa in the .mdf I hope to be able to attach. After repeated installs/uninstalls of MSDE, personal SQL and the applicaiton itself, I am now not able to even get one connected registration. What are the key issues in this situation? I have not examined the registry. Maybe that is the next step?
I hope this more elementary situation is not too far afield from the original question.
THANKS

Cannot register SQL server

Hi
It is the same old problem again but mine is a bit strange. I have read
other discussion on this problem but still cannot locate the cause.
I have a primary domain controller (PDC) running W2000 and SQL Server 2000
serving my main database. I have another W2k3 server running SQL Desktop
Engine which locates in the DMZ of my network and serves as my mini database
for displaying data for our web site. Of course the PDC is behind the
firewall. I need to create a package copying data from my main DB to my
mini DB regularly. When I create my package in Enterprise Manager on my
PDC, I cannot find my web server (i.e. the W2k3 server in DMZ). The error
is "Server does not exist or access denied". The strange thing is that I
can register the mini DB server from other PCs in the same network. Hence I
think it is not the problem of firewall. Also, the PDC can register other
SQL server as well but just not the one on my web server.
Can you suggest what I can check to solve the problem?
Thanks
EdwinVerify that you can connect in both directions using ISQL.exe
ISQL -Stcp:ServerNameHere -E
or
ISQL -Stcp:ServerNameHere -Uuserid -Ppassword
If this fails it will give you a more accurate error message.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I am getting the failure to register SQL server in Enterprise Manager
problem in a new named instance of SQL server on a new MOTION tablet
which comes with dot net framework installed. The application requires
the dot net framework and will run under MSDE or Personal SQL. I have
successful connected to the sample .mdb that ships with the application
after installing the application and MSDE on the local machine with no
network connection. After logging off the local machine and on to the
domain (behind my ZYWALL firewall, I am able to company my main .mdf to
the local machine. Does the TRUSTED CONNECTION issue apply here since
the main .mdf comes from another machine? Does sa for the MOTION
tablet have to be the same as sa in the .mdf I hope to be able to
attach. After repeated installs/uninstalls of MSDE, personal SQL and
the applicaiton itself, I am now not able to even get one connected
registration. What are the key issues in this situation? I have not
examined the registry. Maybe that is the next step?
I hope this more elementary situation is not too far afield from the
original question.
THANKS
docpsf
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message747244.htmlsql

Cannot open database requested in login

Hi there i'm having a bit of trouble with my asp.net authorisations and was wondering
if anyone would have any idea how I can solve it - as it's driving me nuts!.

On my local machine (I've MDSN) running with Web Matrix - my connection works fine.

However since i've uploaded it to my Hoster - Forms and DB - I get the error

Cannot open database requested in login 'TestDB'. Login fails. Login failed for user AUTHORITY\NET SERVICE'.

Can anyone give me any pointers on where I should be going?

I've been told by my Hosting co. that the server should be set to localhost (as shown)
but thats about it!

My Web.config file looks like:-

<configuration>
<!-- application specific settings -->
<appSettings>
<add key="ConnectionString" value="server=localhost;Trusted_Connection=true;database=TestDB" />
</appSettings
<!-- forms based authentication --
<system.web>
<!-- enable custom errors for the application -->
<customErrors mode="Off"/>
<!--RemoteOnly" defaultRedirect="ErrorPage.aspx" -->
<!-- disable session state for application -->
<sessionState mode="Off" />
</system.web>
</configuration
Thanks in advance for any help.

Dave.Did the host say whether you should use NT Authorization or Sql Authorization? You are using NT Authorization now but perhaps it should be Sql Authorization. If so you'll have to change your connection string to something like:

data source=localhost;initial catalog=TestDb;password=whatever;user id=whatever;packet size=4096

Of course you'll need to find out what username and password to use.|||I know netiquete etc but had to thank you!

Just like to say thanks for that invaluable piece of information!

If anyone else has the problem i've been getting then the solution proposed by McMurdoStation was absolutly spot on.

Thanks again.

Dave|||Your welcome.

Cannot open database "xx" requested by the login. The login failed.

Hi,
we are running a webapplication, using IIS6, Windows 2003 64 bit in classic Asp.
In the asp pages we connect to our SQL server (SQL Server 2005 64bit,windows 2003 64 bit), mirrored on a third sql server (same software setup as the first sql server).

The mirroring is without witness.

This works perfectly but sometimes we get the following error :

Description: [Microsoft][SQL Native Client][SQL Server]Cannot open database

"OBJECTS" requested by the login. The login failed.

In the logs of sql, we see that the offending connection does not go the principal but to the mirror. Which would cause the problem of course..

The name of the database varies, and there is no consistent error. Most of the time there are no problems (95%), but sometimes we get these errors.

Connection from the asp to sql is done using ADO, using following driver in the connectionstring :

Driver="{SQL Native Client}"

Does anyone have an idea what could be the problem ?
We can of course increase timouts for the mirroring, but is this the solution ?

Greets
Tom

Looks like your application attempts to login using "OBJECTS" as the default database and the database does not exist. Is that database supposed to always exist or is it occasionally dropped and recreated? Your application should not attempt to connect to the database if it might not exist.

Thanks
Laurentiu

|||Hi Laurentiu,

the database always exists. So it must always be available for connection.
But for some reason SQL fails to see the database.

Also, it's not one and the same database that has this problems. We have more than 100 databases on the sql server, and the connection error popups randomly with other databases.

Greets
Tom|||

Can you check your server errorlog and post the errors that show up there? Also, does this happen during server startup by any chance? Is your database on a special volume, such that it would take longer to start up, for example, is it on an EFS volume?

Thanks
Laurentiu

|||Hi Laurentiu,

I'll check the correct SQL serverlogs when this error occurs.

The server is not starting up but running as normally. Remember, this is a production server.
No special volumes.

Additional information :

As told the connection is made in our ASP pages , using an ADO connectionstring.

Now it seems that the initial connection setup works :
e.g

set pCn = Server.CreateObject("ADODB.Connection")
pCn.connectionstring=lstr
pCn.Open

And the first executes always seem to work :
e.g.
pcn.Execute ("select * from thetable")

But in the same page, more executes are performed with the same connection (pC).
And after some executes, it fails with the above error.

Again, this does not happen always, and not with the same database !
And we are not starting up the server, or doing special tasks (aka backups,etc) which would add an extra load on the server.
The moments that this error occures are random, as are the databases with which the error occurs.

Greetings
Tom

|||

If you hit the error again, can you please provide the errorlog entries?

Thanks
Laurentiu

|||

Hi Laurentiu,

sorry for the delay in getting back to you.

There are no error entries in the primary sql server error logs
Only an error pops up in the secondary (mirror) server, stating the login failed.
And this of cours makes sense, as the connection does not go to the primary but to the secondary, which is acting as mirror. And therefore can not be accessed.

The question is : why does the connection sometimes jumps in stead from the primary server, to the mirror server ? Nowhere in my code I give a secondary server as reference.

The connection is made through ODBC in an ASP web application (IIS6) , simelar to :

Set oCn = Server.CreateObject("ADODB.Connection")
oCn.CommandTimeout = 180
oCn.Open lconstirng lconstring

where lconstring is a dsn param : e.g. "filedsn=c:\data\dsn\mastercon.dsn"
where the dsn file contains following info :

[ODBC]
DRIVER={SQL Native Client}
UID=myuserid
pwd=mypwd
Address=xxx.yyy.zzz.qqq,myport
Network=DBMSSOCN
DATABASE=MyDatabaseName
WSID=xxx.yyy.zzz.qqq
APP=Microsoft Open Database Connectivity
SERVER=xxx.yyy.zzz.qqq
Description=blabla

I tried the above without the DSN, so a dsnless connection string. Nothing changed.
I tried following drivers :{SQL Native Client} and SQLNCLI
Nothing changed

Any ideas ?

Greets
Tom|||

Hi Tom,

For these questions, I suggest trying the Database Mirroring forum or the SQL Server Data Access forum. These are the forums that address issues related to mirroring and server connections.

Thanks
Laurentiu

Cannot open database "xx" requested by the login. The login failed.

Hi,
we are running a webapplication, using IIS6, Windows 2003 64 bit in classic Asp.
In

the ASP pages we connect to our SQL server (SQL Server 2005

64bit,windows 2003 64 bit), mirrored on a third sql server (same

software /hardware setup as the first sql server).

The mirroring is without witness.

This works but sometimes we get the following ODBC error in our ASP application :

Description: [Microsoft][SQL Native Client][SQL Server]Cannot open database

"OBJECTS" requested by the login. The login failed.

No error can be seen in the principal SQL error logs.
But a failed authorization can be found on the mirror server..
And this of course this makes

sense, as the connection does not go to the primary but to the

secondary, which is acting as mirror. And therefore can not be accessed.

The question is : why

does the connection sometimes jumps in stead from the primary server,

to the mirror server ? Nowhere in the code I give a secondary server as

reference.

Most of

the time there are no problems (97%), but sometimes we get these errors.

The moments that this error occures are random, as are the databases with which the error occurs.

The connection is made through ODBC in an ASP web application (IIS6) , simelar to :

Set oCn = Server.CreateObject("ADODB.Connection")
oCn.CommandTimeout = 180
oCn.Open lconstring

where lconstring is a dsn param : e.g. "filedsn=c:\data\dsn\mastercon.dsn"
where the dsn file contains following info :

[ODBC]
DRIVER={SQL Native Client}
UID=myuserid
pwd=mypwd
Address=xxx.yyy.zzz.qqq,myport
Network=DBMSSOCN
DATABASE=MyDatabaseName
WSID=xxx.yyy.zzz.qqq
APP=Microsoft Open Database Connectivity
SERVER=xxx.yyy.zzz.qqq
Description=blabla

I tried the above without the DSN, so a dsnless connection string. Nothing changed.
I tried following drivers :{SQL Native Client} and SQLNCLI
Nothing changed

Does anyone have an idea what could be the problem ?
We can of course increase timouts for the mirroring, but is this the solution ?
Before we started using SQL 2005 and mirroring this problem did not occur.

Greets

Tom

This will occur if you client has intermittent problems accessing the principal. The mirroring principal will send the name of the mirror server to the client after the first connection. If the client loses connection, it does not know if the principal has failed (it could have been destroyed by a natural disaster for example) so it will try to connect to the mirror to see if there has been a failover to the mirror. Since there was no server side failover in this case, you will get the connection failure logged on the mirror server. I recommend you monitor your network to see if there are intermittent problems causing this behavior.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||At this point we can't find any immediate problems.

What information could help us to identify the reason causing the 'intermittent problems accessing the principal' ?

Are there logs I can consult ?

Greetings

Tom|||

I found an interesting link, which could be the reason for our problem.

But we are unaware that we disbled pooling :

http://groups-beta.google.com/group/microsoft.public.sqlserver.odbc/browse_thread/thread/9c79f6f4e8adce7b/f99b340d2fd76aef

Would this fix help even when pooling is enabled ?

Greetings,
Tom

|||Any solution for this with SP2 for 2005 ?

Cannot open database "xx" requested by the login. The login failed.

Hi,
we are running a webapplication, using IIS6, Windows 2003 64 bit in classic Asp.
In

the ASP pages we connect to our SQL server (SQL Server 2005

64bit,windows 2003 64 bit), mirrored on a third sql server (same

software /hardware setup as the first sql server).

The mirroring is without witness.

This works but sometimes we get the following ODBC error in our ASP application :

Description: [Microsoft][SQL Native Client][SQL Server]Cannot open database

"OBJECTS" requested by the login. The login failed.

No error can be seen in the principal SQL error logs.
But a failed authorization can be found on the mirror server..
And this of course this makes

sense, as the connection does not go to the primary but to the

secondary, which is acting as mirror. And therefore can not be accessed.

The question is : why

does the connection sometimes jumps in stead from the primary server,

to the mirror server ? Nowhere in the code I give a secondary server as

reference.

Most of

the time there are no problems (97%), but sometimes we get these errors.

The moments that this error occures are random, as are the databases with which the error occurs.

The connection is made through ODBC in an ASP web application (IIS6) , simelar to :

Set oCn = Server.CreateObject("ADODB.Connection")
oCn.CommandTimeout = 180
oCn.Open lconstring

where lconstring is a dsn param : e.g. "filedsn=c:\data\dsn\mastercon.dsn"
where the dsn file contains following info :

[ODBC]
DRIVER={SQL Native Client}
UID=myuserid
pwd=mypwd
Address=xxx.yyy.zzz.qqq,myport
Network=DBMSSOCN
DATABASE=MyDatabaseName
WSID=xxx.yyy.zzz.qqq
APP=Microsoft Open Database Connectivity
SERVER=xxx.yyy.zzz.qqq
Description=blabla

I tried the above without the DSN, so a dsnless connection string. Nothing changed.
I tried following drivers :{SQL Native Client} and SQLNCLI
Nothing changed

Does anyone have an idea what could be the problem ?
We can of course increase timouts for the mirroring, but is this the solution ?
Before we started using SQL 2005 and mirroring this problem did not occur.

Greets

Tom

This will occur if you client has intermittent problems accessing the principal. The mirroring principal will send the name of the mirror server to the client after the first connection. If the client loses connection, it does not know if the principal has failed (it could have been destroyed by a natural disaster for example) so it will try to connect to the mirror to see if there has been a failover to the mirror. Since there was no server side failover in this case, you will get the connection failure logged on the mirror server. I recommend you monitor your network to see if there are intermittent problems causing this behavior.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||At this point we can't find any immediate problems.

What information could help us to identify the reason causing the 'intermittent problems accessing the principal' ?

Are there logs I can consult ?

Greetings

Tom|||

I found an interesting link, which could be the reason for our problem.

But we are unaware that we disbled pooling :

http://groups-beta.google.com/group/microsoft.public.sqlserver.odbc/browse_thread/thread/9c79f6f4e8adce7b/f99b340d2fd76aef

Would this fix help even when pooling is enabled ?

Greetings,
Tom

|||Any solution for this with SP2 for 2005 ?

Cannot open database "xx" requested by the login. The login failed.

Hi,
we are running a webapplication, using IIS6, Windows 2003 64 bit in classic Asp.
In

the ASP pages we connect to our SQL server (SQL Server 2005

64bit,windows 2003 64 bit), mirrored on a third sql server (same

software /hardware setup as the first sql server).

The mirroring is without witness.

This works but sometimes we get the following ODBC error in our ASP application :

Description: [Microsoft][SQL Native Client][SQL Server]Cannot open database

"OBJECTS" requested by the login. The login failed.

No error can be seen in the principal SQL error logs.
But a failed authorization can be found on the mirror server..
And this of course this makes

sense, as the connection does not go to the primary but to the

secondary, which is acting as mirror. And therefore can not be accessed.

The question is : why

does the connection sometimes jumps in stead from the primary server,

to the mirror server ? Nowhere in the code I give a secondary server as

reference.

Most of

the time there are no problems (97%), but sometimes we get these errors.

The moments that this error occures are random, as are the databases with which the error occurs.

The connection is made through ODBC in an ASP web application (IIS6) , simelar to :

Set oCn = Server.CreateObject("ADODB.Connection")
oCn.CommandTimeout = 180
oCn.Open lconstring

where lconstring is a dsn param : e.g. "filedsn=c:\data\dsn\mastercon.dsn"
where the dsn file contains following info :

[ODBC]
DRIVER={SQL Native Client}
UID=myuserid
pwd=mypwd
Address=xxx.yyy.zzz.qqq,myport
Network=DBMSSOCN
DATABASE=MyDatabaseName
WSID=xxx.yyy.zzz.qqq
APP=Microsoft Open Database Connectivity
SERVER=xxx.yyy.zzz.qqq
Description=blabla

I tried the above without the DSN, so a dsnless connection string. Nothing changed.
I tried following drivers :{SQL Native Client} and SQLNCLI
Nothing changed

Does anyone have an idea what could be the problem ?
We can of course increase timouts for the mirroring, but is this the solution ?
Before we started using SQL 2005 and mirroring this problem did not occur.

Greets

Tom

This will occur if you client has intermittent problems accessing the principal. The mirroring principal will send the name of the mirror server to the client after the first connection. If the client loses connection, it does not know if the principal has failed (it could have been destroyed by a natural disaster for example) so it will try to connect to the mirror to see if there has been a failover to the mirror. Since there was no server side failover in this case, you will get the connection failure logged on the mirror server. I recommend you monitor your network to see if there are intermittent problems causing this behavior.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||At this point we can't find any immediate problems.

What information could help us to identify the reason causing the 'intermittent problems accessing the principal' ?

Are there logs I can consult ?

Greetings

Tom|||

I found an interesting link, which could be the reason for our problem.

But we are unaware that we disbled pooling :

http://groups-beta.google.com/group/microsoft.public.sqlserver.odbc/browse_thread/thread/9c79f6f4e8adce7b/f99b340d2fd76aef

Would this fix help even when pooling is enabled ?

Greetings,
Tom

|||Any solution for this with SP2 for 2005 ?

Cannot open database "xx" requested by the login. The login failed.

Hi,
we are running a webapplication, using IIS6, Windows 2003 64 bit in classic Asp.
In the asp pages we connect to our SQL server (SQL Server 2005 64bit,windows 2003 64 bit), mirrored on a third sql server (same software setup as the first sql server).

The mirroring is without witness.

This works perfectly but sometimes we get the following error :

Description: [Microsoft][SQL Native Client][SQL Server]Cannot open database

"OBJECTS" requested by the login. The login failed.

In the logs of sql, we see that the offending connection does not go the principal but to the mirror. Which would cause the problem of course..

The name of the database varies, and there is no consistent error. Most of the time there are no problems (95%), but sometimes we get these errors.

Connection from the asp to sql is done using ADO, using following driver in the connectionstring :

Driver="{SQL Native Client}"

Does anyone have an idea what could be the problem ?
We can of course increase timouts for the mirroring, but is this the solution ?

Greets
Tom

Looks like your application attempts to login using "OBJECTS" as the default database and the database does not exist. Is that database supposed to always exist or is it occasionally dropped and recreated? Your application should not attempt to connect to the database if it might not exist.

Thanks
Laurentiu

|||Hi Laurentiu,

the database always exists. So it must always be available for connection.
But for some reason SQL fails to see the database.

Also, it's not one and the same database that has this problems. We have more than 100 databases on the sql server, and the connection error popups randomly with other databases.

Greets
Tom|||

Can you check your server errorlog and post the errors that show up there? Also, does this happen during server startup by any chance? Is your database on a special volume, such that it would take longer to start up, for example, is it on an EFS volume?

Thanks
Laurentiu

|||Hi Laurentiu,

I'll check the correct SQL serverlogs when this error occurs.

The server is not starting up but running as normally. Remember, this is a production server.
No special volumes.

Additional information :

As told the connection is made in our ASP pages , using an ADO connectionstring.

Now it seems that the initial connection setup works :
e.g

set pCn = Server.CreateObject("ADODB.Connection")
pCn.connectionstring=lstr
pCn.Open

And the first executes always seem to work :
e.g.
pcn.Execute ("select * from thetable")

But in the same page, more executes are performed with the same connection (pC).
And after some executes, it fails with the above error.

Again, this does not happen always, and not with the same database !
And we are not starting up the server, or doing special tasks (aka backups,etc) which would add an extra load on the server.
The moments that this error occures are random, as are the databases with which the error occurs.

Greetings
Tom

|||

If you hit the error again, can you please provide the errorlog entries?

Thanks
Laurentiu

|||

Hi Laurentiu,

sorry for the delay in getting back to you.

There are no error entries in the primary sql server error logs
Only an error pops up in the secondary (mirror) server, stating the login failed.
And this of cours makes sense, as the connection does not go to the primary but to the secondary, which is acting as mirror. And therefore can not be accessed.

The question is : why does the connection sometimes jumps in stead from the primary server, to the mirror server ? Nowhere in my code I give a secondary server as reference.

The connection is made through ODBC in an ASP web application (IIS6) , simelar to :

Set oCn = Server.CreateObject("ADODB.Connection")
oCn.CommandTimeout = 180
oCn.Open lconstirng lconstring

where lconstring is a dsn param : e.g. "filedsn=c:\data\dsn\mastercon.dsn"
where the dsn file contains following info :

[ODBC]
DRIVER={SQL Native Client}
UID=myuserid
pwd=mypwd
Address=xxx.yyy.zzz.qqq,myport
Network=DBMSSOCN
DATABASE=MyDatabaseName
WSID=xxx.yyy.zzz.qqq
APP=Microsoft Open Database Connectivity
SERVER=xxx.yyy.zzz.qqq
Description=blabla

I tried the above without the DSN, so a dsnless connection string. Nothing changed.
I tried following drivers :{SQL Native Client} and SQLNCLI
Nothing changed

Any ideas ?

Greets
Tom|||

Hi Tom,

For these questions, I suggest trying the Database Mirroring forum or the SQL Server Data Access forum. These are the forums that address issues related to mirroring and server connections.

Thanks
Laurentiu

Cannot open database "xx" requested by the login. The login failed.

Hi,
we are running a webapplication, using IIS6, Windows 2003 64 bit in classic Asp.
In

the ASP pages we connect to our SQL server (SQL Server 2005

64bit,windows 2003 64 bit), mirrored on a third sql server (same

software /hardware setup as the first sql server).

The mirroring is without witness.

This works but sometimes we get the following ODBC error in our ASP application :

Description: [Microsoft][SQL Native Client][SQL Server]Cannot open database

"OBJECTS" requested by the login. The login failed.

No error can be seen in the principal SQL error logs.
But a failed authorization can be found on the mirror server..
And this of course this makes

sense, as the connection does not go to the primary but to the

secondary, which is acting as mirror. And therefore can not be accessed.

The question is : why

does the connection sometimes jumps in stead from the primary server,

to the mirror server ? Nowhere in the code I give a secondary server as

reference.

Most of

the time there are no problems (97%), but sometimes we get these errors.

The moments that this error occures are random, as are the databases with which the error occurs.

The connection is made through ODBC in an ASP web application (IIS6) , simelar to :

Set oCn = Server.CreateObject("ADODB.Connection")
oCn.CommandTimeout = 180
oCn.Open lconstring

where lconstring is a dsn param : e.g. "filedsn=c:\data\dsn\mastercon.dsn"
where the dsn file contains following info :

[ODBC]
DRIVER={SQL Native Client}
UID=myuserid
pwd=mypwd
Address=xxx.yyy.zzz.qqq,myport
Network=DBMSSOCN
DATABASE=MyDatabaseName
WSID=xxx.yyy.zzz.qqq
APP=Microsoft Open Database Connectivity
SERVER=xxx.yyy.zzz.qqq
Description=blabla

I tried the above without the DSN, so a dsnless connection string. Nothing changed.
I tried following drivers :{SQL Native Client} and SQLNCLI
Nothing changed

Does anyone have an idea what could be the problem ?
We can of course increase timouts for the mirroring, but is this the solution ?
Before we started using SQL 2005 and mirroring this problem did not occur.

Greets

Tom

This will occur if you client has intermittent problems accessing the principal. The mirroring principal will send the name of the mirror server to the client after the first connection. If the client loses connection, it does not know if the principal has failed (it could have been destroyed by a natural disaster for example) so it will try to connect to the mirror to see if there has been a failover to the mirror. Since there was no server side failover in this case, you will get the connection failure logged on the mirror server. I recommend you monitor your network to see if there are intermittent problems causing this behavior.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||At this point we can't find any immediate problems.

What information could help us to identify the reason causing the 'intermittent problems accessing the principal' ?

Are there logs I can consult ?

Greetings

Tom|||

I found an interesting link, which could be the reason for our problem.

But we are unaware that we disbled pooling :

http://groups-beta.google.com/group/microsoft.public.sqlserver.odbc/browse_thread/thread/9c79f6f4e8adce7b/f99b340d2fd76aef

Would this fix help even when pooling is enabled ?

Greetings,
Tom

|||Any solution for this with SP2 for 2005 ?

Cannot index bit field

What's the logic behind that? I have a large table that has a field called
"Deleted" to indicate that a record has been deleted. For various reasons
very few records will be marked Deleted. If I do a select where Deleted = 1
it will need to scan the entire table for those few deleted records when I
would have thought it would be able to look up an index to quickly find
those records.
Regards,
MichaelMichael,
Beginning with SQL Server 2000, indexes can contain bit columns.
Are you using an earlier version, or is the database in question set
to an earlier compatibility level?
Steve Kass
Drew University
Michael C wrote:

>What's the logic behind that? I have a large table that has a field called
>"Deleted" to indicate that a record has been deleted. For various reasons
>very few records will be marked Deleted. If I do a select where Deleted = 1
>it will need to scan the entire table for those few deleted records when I
>would have thought it would be able to look up an index to quickly find
>those records.
>Regards,
>Michael
>
>|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; you have not even learned the
vocabulary.
The use of deleted flags gives me flashbacks to the late 1950's when we
had tape file systems that used flags like that! Your entire approach
to RDBMS is **totally* wrong, at the foundational level.
Kid, listen to me; you are being told this by one of the people who
wrote the SQL standards. You might want to think about that.|||Hi Micheal,
Steve's right. Bit columns are support in indexes in SQL 2000. Perhapes
you are being caught by the issue that EM does allow you to create indexes
with bit columns from within the Table Design form? If you need a graphic
tool to create indexes right click on the table object and use the All Tasks
Manage Indexes option
--
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:%23nneZC7mFHA.2472@.TK2MSFTNGP15.phx.gbl...
> What's the logic behind that? I have a large table that has a field called
> "Deleted" to indicate that a record has been deleted. For various reasons
> very few records will be marked Deleted. If I do a select where Deleted =
> 1 it will need to scan the entire table for those few deleted records when
> I would have thought it would be able to look up an index to quickly find
> those records.
> Regards,
> Michael
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123468040.458991.66810@.z14g2000cwz.googlegroups.com...
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; you have not even learned the
> vocabulary.
> Kid, listen to me; you are being told this by one of the people who
> wrote the SQL standards. You might want to think about that.
No offence joe but you really are an idiot. I'm well aware you helped write
the SQL standards you remind the group on a regular basis. It's possibly why
databases are so behind the rest of computing world. There's one thing I
really dislike when dealing with computer people is those who overuse the
lingo in an attempt to make themselves look smarter. Such as your use of
"RDBMS" instead of database and insistance a column not be called a field.

> The use of deleted flags gives me flashbacks to the late 1950's when we
> had tape file systems that used flags like that! Your entire approach
> to RDBMS is **totally* wrong, at the foundational level.
What do you suggest instead of a deleted field? The record still needs to be
there so historical reports can run. The only thing I can see is that maybe
I should have used a field called DeletedDate but I don't need that
information.
Michael|||"GregO" <grego@.community.nospam> wrote in message
news:%23viqnW8mFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi Micheal,
> Steve's right. Bit columns are support in indexes in SQL 2000. Perhapes
> you are being caught by the issue that EM does allow you to create indexes
> with bit columns from within the Table Design form? If you need a graphic
> tool to create indexes right click on the table object and use the All
> Tasks Manage Indexes option
Cool! That looks like it, I can create the index with query analyser no
problem. Enterprise manager wouldn't let me select it from the dropdown or
type the name. Now I just have to check if there's any improvement in
performance, it seems to "select * from table where deleted = 1" almost
instantly without the index anyway but there may be performance advantages
in more complex queries.
Michael|||"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:O8rUXg9mFHA.2860@.TK2MSFTNGP15.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1123468040.458991.66810@.z14g2000cwz.googlegroups.com...
> No offence joe but you really are an idiot. I'm well aware you helped
> write the SQL standards you remind the group on a regular basis. It's
> possibly why databases are so behind the rest of computing world. There's
> one thing I really dislike when dealing with computer people is those who
> overuse the lingo in an attempt to make themselves look smarter. Such as
> your use of "RDBMS" instead of database and insistance a column not be
> called a field.
>
> What do you suggest instead of a deleted field? The record still needs to
> be there so historical reports can run. The only thing I can see is that
> maybe I should have used a field called DeletedDate but I don't need that
> information.
> Michael
>
You'll get in trouble, you called it a field again ;-)
Your approach of using a bit field (I do it to) is OK. The only alternative
that I know of is using a second table for deleted rows. The idea here is
that the active rows are in MyTable and the deleted rows are in
MyTableHistory. There are several advantages to this method...
1. Less storage. Yep, one bit per row.
2. When you don't need to access the deleted rows there's no need to query
the table. If there are alot of delete's, this can boost the performance.
3. If you need to query, both active and inactive rows use a Union All
query. You could also create a view to do this repeatedly for you.
The only downside is that you need to physically move the row to a different
table, this should be done in a tranaction, but this physical movement could
cause other issues with data integrity.
Regards
Colin Dawson
www.cjdawson.com|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:544444
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:OhCG9j9mFHA.1948@.TK2MSFTNGP12.phx.gbl...
> "GregO" <grego@.community.nospam> wrote in message
> news:%23viqnW8mFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Cool! That looks like it, I can create the index with query analyser no
> problem. Enterprise manager wouldn't let me select it from the dropdown or
> type the name. Now I just have to check if there's any improvement in
> performance, it seems to "select * from table where deleted = 1" almost
> instantly without the index anyway but there may be performance advantages
> in more complex queries.
> Michael
>
You could always use SQL Profiler to see the number of reads needed to
complete the query. Also take a look at the Actual Execution plan, this
will show how the query is processed.
Regards
Colin Dawson
www.cjdawson.com|||> There's one thing I
> really dislike when dealing with computer people is those who overuse the
> lingo in an attempt to make themselves look smarter. Such as your use of
> "RDBMS" instead of database and insistance a column not be called a field
Correct terminology helps when it is used to aid clarity and precision.
Do you really think that "RDBMS" means the same as "database"? If so,
then Joe may have made a valid point. Unlike you, Joe didn't use any
personal abuse to make his point.
In answer to your question, note that although you can index a BIT
column, those indexes tend to be of limited value because of the low
cardinality of BIT. Use Query Analyzer to create the index rather than
BIT.
David Portas
SQL Server MVP
--|||Joe invariably favors encoding schemes over flags. After running into a few
issues with flags, I have come to agree with him - encodings adapt better to
changing business requirements.
Instead of having a column of type Bit that indicates whether a row is
deleted or not, you could simply have a status column of type Char(1) - this
will take the same amount of space (both of them take up one byte of storage
according to Books Online), however the status column can have additional
values added at a later date without changing the schema. You could then
have a "pending" status, a "new" status, etc. Also, you can add an index
from Enterprise Manager (not that that is a big deal, really). And there is
no additional work on your part - instead of writing "WHERE Deleted = 1",
you write " WHERE Status = 'D' " (or whatever). Simple!
The last time I dealt with this was in a security settings table where the
previous developer had started with a simple flag indicating whether a user
was an administrator for the custom application in question. Later, the
business added additional user "types" (supervisor, auditor, etc.), and each
time the developer added a new flag column. However, the types were mutually
exclusive (you could not be an auditor and a supervisor at the same time),
but no check constraints were added. Even if check constraints where added,
they would be ugly. It was far better in the long run to simply change the
table to remove the bit flags.
The point is that there is always a way to turn a flag column into an
encoding, which adds no additional work on the DBA or the app developer, but
still provides all the functionality.
IHTH
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:O8rUXg9mFHA.2860@.TK2MSFTNGP15.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1123468040.458991.66810@.z14g2000cwz.googlegroups.com...
> No offence joe but you really are an idiot. I'm well aware you helped
write
> the SQL standards you remind the group on a regular basis. It's possibly
why
> databases are so behind the rest of computing world. There's one thing I
> really dislike when dealing with computer people is those who overuse the
> lingo in an attempt to make themselves look smarter. Such as your use of
> "RDBMS" instead of database and insistance a column not be called a field.
>
> What do you suggest instead of a deleted field? The record still needs to
be
> there so historical reports can run. The only thing I can see is that
maybe
> I should have used a field called DeletedDate but I don't need that
> information.
> Michael
>