A request to establish a connection with transaction manager denie

A user has reported the message "A request to establish a connection with the
transaction manager was denied". The code is at this point attempting to
START a transaction. So far I have been unable to duplicate this, though the
user has been able to get through this code in the past. Of course, no known
changes.
What causes this particular message?
--
Brad AshforthHi Brad
This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
you already have the maximum number of connections.
John
"Brad Ashforth" wrote:
> A user has reported the message "A request to establish a connection with the
> transaction manager was denied". The code is at this point attempting to
> START a transaction. So far I have been unable to duplicate this, though the
> user has been able to get through this code in the past. Of course, no known
> changes.
> What causes this particular message?
> --
> Brad Ashforth|||Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
level? Our application is designed such that we do not make use of nested
transactions. We have a "transaction" routine that is called with either
"Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
(as it was in this case), we first execute a commit (in case a transaction is
pending that was not committed or rolled back) and then do the BEGIN. Of
course, if when we issue the commit if there is no pending transaction, we
get an error ... but that is trapped for and ignored if we are trying to
begin a new transaction.
So, that said ... there should only be one transaction active for the
application at any given time and if by chance we try to start another one
the code automatically commits the first one before starting the 2nd one.
Is there a way to query how many connections there are?
--
Brad Ashforth
"John Bell" wrote:
> Hi Brad
> This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> you already have the maximum number of connections.
> John
> "Brad Ashforth" wrote:
> > A user has reported the message "A request to establish a connection with the
> > transaction manager was denied". The code is at this point attempting to
> > START a transaction. So far I have been unable to duplicate this, though the
> > user has been able to get through this code in the past. Of course, no known
> > changes.
> >
> > What causes this particular message?
> > --
> > Brad Ashforth|||Hi Brad,
Thank you for your posting!
I would like to know some detailed information:
1. Does your customer use Distribute Transaction? If so, does the DTC
services start?
2. Are there any error log in event log? If so, please post it here.
3. What's the SQL Server Version and the Server OS version?
Please let me know the result and so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei ... thank you.
1). No, the customer does not use Distributed Transactions (at least not to
their knowledge)
2) No errors in any of the event logs on the SQL Server box
3) From SQL Query Analyzer @.@.Version returns 8.00.760, from a cmd shell VER
returns 5.2.3790 (Windows Server 2003 Enterprise Edition)
Please note that this error occurred in an acceptance testing phase, not in
production.
--
Brad Ashforth
"Wei Lu [MSFT]" wrote:
> Hi Brad,
> Thank you for your posting!
> I would like to know some detailed information:
> 1. Does your customer use Distribute Transaction? If so, does the DTC
> services start?
> 2. Are there any error log in event log? If so, please post it here.
> 3. What's the SQL Server Version and the Server OS version?
> Please let me know the result and so that I can provide further assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Brad
Checking @.@.TRANCOUNT will tell you whether a transaction is in progress.
Using a commit that when a transaction has not begun will cause an error:
Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Which you can easily avoid.
I don't think this is the cause of the issue! Do you reference any other
databases either in your stored procedures or triggers? Are you using
multipart naming conventions? Are you enquoting your names using square
braces []?
John
"Brad Ashforth" wrote:
> Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
> level? Our application is designed such that we do not make use of nested
> transactions. We have a "transaction" routine that is called with either
> "Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
> (as it was in this case), we first execute a commit (in case a transaction is
> pending that was not committed or rolled back) and then do the BEGIN. Of
> course, if when we issue the commit if there is no pending transaction, we
> get an error ... but that is trapped for and ignored if we are trying to
> begin a new transaction.
> So, that said ... there should only be one transaction active for the
> application at any given time and if by chance we try to start another one
> the code automatically commits the first one before starting the 2nd one.
> Is there a way to query how many connections there are?
> --
> Brad Ashforth
>
> "John Bell" wrote:
> > Hi Brad
> >
> > This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> > you already have the maximum number of connections.
> >
> > John
> >
> > "Brad Ashforth" wrote:
> >
> > > A user has reported the message "A request to establish a connection with the
> > > transaction manager was denied". The code is at this point attempting to
> > > START a transaction. So far I have been unable to duplicate this, though the
> > > user has been able to get through this code in the past. Of course, no known
> > > changes.
> > >
> > > What causes this particular message?
> > > --
> > > Brad Ashforth|||Hi John. Thank you.
We had not known about @.@.TRANCOUNT and agree that it makes more sense to
check this instead of trapping for a known error that is likely to occur (on
the other hand, isn't that what TRY/CATCH is supposed to do?) Anyway, I also
agree that this is not the likely cause of the error I originally posted a
question about.
We do not reference other databases, and in most (99%?) cases do not use
fully qualified table names. This is because most tables are in the DBO
schema and do not get to the user's own schema ... each user temp table (for
reports, etc) are in their own schema. In most cases the object names follow
standard conventions and should not need braces. This is also not an error
that is easily repeatable. It has been seen before but have had trouble
duplicating it.
Thanks,
Brad
--
Brad Ashforth
"John Bell" wrote:
> Hi Brad
> Checking @.@.TRANCOUNT will tell you whether a transaction is in progress.
> Using a commit that when a transaction has not begun will cause an error:
> Server: Msg 3902, Level 16, State 1, Line 1
> The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
> Which you can easily avoid.
> I don't think this is the cause of the issue! Do you reference any other
> databases either in your stored procedures or triggers? Are you using
> multipart naming conventions? Are you enquoting your names using square
> braces []?
>
> John
> "Brad Ashforth" wrote:
> > Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
> > level? Our application is designed such that we do not make use of nested
> > transactions. We have a "transaction" routine that is called with either
> > "Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
> > (as it was in this case), we first execute a commit (in case a transaction is
> > pending that was not committed or rolled back) and then do the BEGIN. Of
> > course, if when we issue the commit if there is no pending transaction, we
> > get an error ... but that is trapped for and ignored if we are trying to
> > begin a new transaction.
> >
> > So, that said ... there should only be one transaction active for the
> > application at any given time and if by chance we try to start another one
> > the code automatically commits the first one before starting the 2nd one.
> >
> > Is there a way to query how many connections there are?
> > --
> > Brad Ashforth
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Brad
> > >
> > > This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> > > you already have the maximum number of connections.
> > >
> > > John
> > >
> > > "Brad Ashforth" wrote:
> > >
> > > > A user has reported the message "A request to establish a connection with the
> > > > transaction manager was denied". The code is at this point attempting to
> > > > START a transaction. So far I have been unable to duplicate this, though the
> > > > user has been able to get through this code in the past. Of course, no known
> > > > changes.
> > > >
> > > > What causes this particular message?
> > > > --
> > > > Brad Ashforth|||Hi Brad
Prefixing with the schema name should improve performance and caching.
If this problem is periodic you are not going to know for certain that you
have cured it!! How often does it occur? Does it mainly occur after a re-boot
or a high load levels? I know you have checked the Event logs what about the
SQL Server log?
If MSDTC is currently running try switching it off, as this should not
effect your application if it is not being used!!
John
"Brad Ashforth" wrote:
> Hi John. Thank you.
> We had not known about @.@.TRANCOUNT and agree that it makes more sense to
> check this instead of trapping for a known error that is likely to occur (on
> the other hand, isn't that what TRY/CATCH is supposed to do?) Anyway, I also
> agree that this is not the likely cause of the error I originally posted a
> question about.
> We do not reference other databases, and in most (99%?) cases do not use
> fully qualified table names. This is because most tables are in the DBO
> schema and do not get to the user's own schema ... each user temp table (for
> reports, etc) are in their own schema. In most cases the object names follow
> standard conventions and should not need braces. This is also not an error
> that is easily repeatable. It has been seen before but have had trouble
> duplicating it.
> Thanks,
> Brad
> --
> Brad Ashforth
>
> "John Bell" wrote:
> > Hi Brad
> >
> > Checking @.@.TRANCOUNT will tell you whether a transaction is in progress.
> > Using a commit that when a transaction has not begun will cause an error:
> >
> > Server: Msg 3902, Level 16, State 1, Line 1
> > The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
> >
> > Which you can easily avoid.
> >
> > I don't think this is the cause of the issue! Do you reference any other
> > databases either in your stored procedures or triggers? Are you using
> > multipart naming conventions? Are you enquoting your names using square
> > braces []?
> >
> >
> > John
> >
> > "Brad Ashforth" wrote:
> >
> > > Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
> > > level? Our application is designed such that we do not make use of nested
> > > transactions. We have a "transaction" routine that is called with either
> > > "Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
> > > (as it was in this case), we first execute a commit (in case a transaction is
> > > pending that was not committed or rolled back) and then do the BEGIN. Of
> > > course, if when we issue the commit if there is no pending transaction, we
> > > get an error ... but that is trapped for and ignored if we are trying to
> > > begin a new transaction.
> > >
> > > So, that said ... there should only be one transaction active for the
> > > application at any given time and if by chance we try to start another one
> > > the code automatically commits the first one before starting the 2nd one.
> > >
> > > Is there a way to query how many connections there are?
> > > --
> > > Brad Ashforth
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Brad
> > > >
> > > > This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> > > > you already have the maximum number of connections.
> > > >
> > > > John
> > > >
> > > > "Brad Ashforth" wrote:
> > > >
> > > > > A user has reported the message "A request to establish a connection with the
> > > > > transaction manager was denied". The code is at this point attempting to
> > > > > START a transaction. So far I have been unable to duplicate this, though the
> > > > > user has been able to get through this code in the past. Of course, no known
> > > > > changes.
> > > > >
> > > > > What causes this particular message?
> > > > > --
> > > > > Brad Ashforth|||Hi John .. Thank you.
As the application is used in multiple environments, we have no control or
knowledge of the deployment and don't code the fully qualified object name,
currently it would take a LOT of rework to do this. Is the performance
improvement enough to make it worthwhile?
Yes, periodic failures are a pain to diagnose. The problem occurs RARELY (I
think it has only happened once or twice in a deployment. Not all deployments
have seen the problem and the total number of occurrences is certainly less
than 6 or 7. Rebooting does not seem to be the issue, nor does load level (no
reboots either before or after the error). I currently do not have access to
the SQL Server log. Will try to obtain it.
MSDTC is not used by our application, but if a deployment decides to house
multiple databases on the SQLServer, wouldn't it be better to leave it on?
--
Brad Ashforth
"John Bell" wrote:
> Hi Brad
> Prefixing with the schema name should improve performance and caching.
> If this problem is periodic you are not going to know for certain that you
> have cured it!! How often does it occur? Does it mainly occur after a re-boot
> or a high load levels? I know you have checked the Event logs what about the
> SQL Server log?
> If MSDTC is currently running try switching it off, as this should not
> effect your application if it is not being used!!
> John
>
>
> "Brad Ashforth" wrote:
> > Hi John. Thank you.
> >
> > We had not known about @.@.TRANCOUNT and agree that it makes more sense to
> > check this instead of trapping for a known error that is likely to occur (on
> > the other hand, isn't that what TRY/CATCH is supposed to do?) Anyway, I also
> > agree that this is not the likely cause of the error I originally posted a
> > question about.
> >
> > We do not reference other databases, and in most (99%?) cases do not use
> > fully qualified table names. This is because most tables are in the DBO
> > schema and do not get to the user's own schema ... each user temp table (for
> > reports, etc) are in their own schema. In most cases the object names follow
> > standard conventions and should not need braces. This is also not an error
> > that is easily repeatable. It has been seen before but have had trouble
> > duplicating it.
> >
> > Thanks,
> > Brad
> > --
> > Brad Ashforth
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Brad
> > >
> > > Checking @.@.TRANCOUNT will tell you whether a transaction is in progress.
> > > Using a commit that when a transaction has not begun will cause an error:
> > >
> > > Server: Msg 3902, Level 16, State 1, Line 1
> > > The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
> > >
> > > Which you can easily avoid.
> > >
> > > I don't think this is the cause of the issue! Do you reference any other
> > > databases either in your stored procedures or triggers? Are you using
> > > multipart naming conventions? Are you enquoting your names using square
> > > braces []?
> > >
> > >
> > > John
> > >
> > > "Brad Ashforth" wrote:
> > >
> > > > Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
> > > > level? Our application is designed such that we do not make use of nested
> > > > transactions. We have a "transaction" routine that is called with either
> > > > "Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
> > > > (as it was in this case), we first execute a commit (in case a transaction is
> > > > pending that was not committed or rolled back) and then do the BEGIN. Of
> > > > course, if when we issue the commit if there is no pending transaction, we
> > > > get an error ... but that is trapped for and ignored if we are trying to
> > > > begin a new transaction.
> > > >
> > > > So, that said ... there should only be one transaction active for the
> > > > application at any given time and if by chance we try to start another one
> > > > the code automatically commits the first one before starting the 2nd one.
> > > >
> > > > Is there a way to query how many connections there are?
> > > > --
> > > > Brad Ashforth
> > > >
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Brad
> > > > >
> > > > > This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> > > > > you already have the maximum number of connections.
> > > > >
> > > > > John
> > > > >
> > > > > "Brad Ashforth" wrote:
> > > > >
> > > > > > A user has reported the message "A request to establish a connection with the
> > > > > > transaction manager was denied". The code is at this point attempting to
> > > > > > START a transaction. So far I have been unable to duplicate this, though the
> > > > > > user has been able to get through this code in the past. Of course, no known
> > > > > > changes.
> > > > > >
> > > > > > What causes this particular message?
> > > > > > --
> > > > > > Brad Ashforth|||Hi Brad
Using the schema name should not effect the deployments as this should be
constant, two part naming will help to varying degrees, depending on what
specification the server has and the load it takes. In general it is good
practice to do this and is usually easier to implement though of coding
standards and QA than retrospectively.
If you share the server with other applications you would need to make sure
that they don't use MSDTC, but any service on the server that is not being
used should be stopped as they will take up resources. If you stop this
service then it may make it easier to highlight something wrong with your own
application.
John
"Brad Ashforth" wrote:
> Hi John .. Thank you.
> As the application is used in multiple environments, we have no control or
> knowledge of the deployment and don't code the fully qualified object name,
> currently it would take a LOT of rework to do this. Is the performance
> improvement enough to make it worthwhile?
> Yes, periodic failures are a pain to diagnose. The problem occurs RARELY (I
> think it has only happened once or twice in a deployment. Not all deployments
> have seen the problem and the total number of occurrences is certainly less
> than 6 or 7. Rebooting does not seem to be the issue, nor does load level (no
> reboots either before or after the error). I currently do not have access to
> the SQL Server log. Will try to obtain it.
> MSDTC is not used by our application, but if a deployment decides to house
> multiple databases on the SQLServer, wouldn't it be better to leave it on?
> --
> Brad Ashforth
>
> "John Bell" wrote:
> > Hi Brad
> >
> > Prefixing with the schema name should improve performance and caching.
> >
> > If this problem is periodic you are not going to know for certain that you
> > have cured it!! How often does it occur? Does it mainly occur after a re-boot
> > or a high load levels? I know you have checked the Event logs what about the
> > SQL Server log?
> >
> > If MSDTC is currently running try switching it off, as this should not
> > effect your application if it is not being used!!
> >
> > John
> >
> >
> >
> >
> >
> > "Brad Ashforth" wrote:
> >
> > > Hi John. Thank you.
> > >
> > > We had not known about @.@.TRANCOUNT and agree that it makes more sense to
> > > check this instead of trapping for a known error that is likely to occur (on
> > > the other hand, isn't that what TRY/CATCH is supposed to do?) Anyway, I also
> > > agree that this is not the likely cause of the error I originally posted a
> > > question about.
> > >
> > > We do not reference other databases, and in most (99%?) cases do not use
> > > fully qualified table names. This is because most tables are in the DBO
> > > schema and do not get to the user's own schema ... each user temp table (for
> > > reports, etc) are in their own schema. In most cases the object names follow
> > > standard conventions and should not need braces. This is also not an error
> > > that is easily repeatable. It has been seen before but have had trouble
> > > duplicating it.
> > >
> > > Thanks,
> > > Brad
> > > --
> > > Brad Ashforth
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Brad
> > > >
> > > > Checking @.@.TRANCOUNT will tell you whether a transaction is in progress.
> > > > Using a commit that when a transaction has not begun will cause an error:
> > > >
> > > > Server: Msg 3902, Level 16, State 1, Line 1
> > > > The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
> > > >
> > > > Which you can easily avoid.
> > > >
> > > > I don't think this is the cause of the issue! Do you reference any other
> > > > databases either in your stored procedures or triggers? Are you using
> > > > multipart naming conventions? Are you enquoting your names using square
> > > > braces []?
> > > >
> > > >
> > > > John
> > > >
> > > > "Brad Ashforth" wrote:
> > > >
> > > > > Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
> > > > > level? Our application is designed such that we do not make use of nested
> > > > > transactions. We have a "transaction" routine that is called with either
> > > > > "Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
> > > > > (as it was in this case), we first execute a commit (in case a transaction is
> > > > > pending that was not committed or rolled back) and then do the BEGIN. Of
> > > > > course, if when we issue the commit if there is no pending transaction, we
> > > > > get an error ... but that is trapped for and ignored if we are trying to
> > > > > begin a new transaction.
> > > > >
> > > > > So, that said ... there should only be one transaction active for the
> > > > > application at any given time and if by chance we try to start another one
> > > > > the code automatically commits the first one before starting the 2nd one.
> > > > >
> > > > > Is there a way to query how many connections there are?
> > > > > --
> > > > > Brad Ashforth
> > > > >
> > > > >
> > > > > "John Bell" wrote:
> > > > >
> > > > > > Hi Brad
> > > > > >
> > > > > > This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> > > > > > you already have the maximum number of connections.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "Brad Ashforth" wrote:
> > > > > >
> > > > > > > A user has reported the message "A request to establish a connection with the
> > > > > > > transaction manager was denied". The code is at this point attempting to
> > > > > > > START a transaction. So far I have been unable to duplicate this, though the
> > > > > > > user has been able to get through this code in the past. Of course, no known
> > > > > > > changes.
> > > > > > >
> > > > > > > What causes this particular message?
> > > > > > > --
> > > > > > > Brad Ashforth|||Hi John, Thank you ... I found that the test server does NOT host other
databases that require MSDTC but the server DID have the service as auto
start. I've set it to manual and stopped the service. We'll keep checking to
see if it happens again.
Thank you,
Brad Ashforth
"John Bell" wrote:
> Hi Brad
> Using the schema name should not effect the deployments as this should be
> constant, two part naming will help to varying degrees, depending on what
> specification the server has and the load it takes. In general it is good
> practice to do this and is usually easier to implement though of coding
> standards and QA than retrospectively.
> If you share the server with other applications you would need to make sure
> that they don't use MSDTC, but any service on the server that is not being
> used should be stopped as they will take up resources. If you stop this
> service then it may make it easier to highlight something wrong with your own
> application.
> John
>
> "Brad Ashforth" wrote:
> > Hi John .. Thank you.
> >
> > As the application is used in multiple environments, we have no control or
> > knowledge of the deployment and don't code the fully qualified object name,
> > currently it would take a LOT of rework to do this. Is the performance
> > improvement enough to make it worthwhile?
> >
> > Yes, periodic failures are a pain to diagnose. The problem occurs RARELY (I
> > think it has only happened once or twice in a deployment. Not all deployments
> > have seen the problem and the total number of occurrences is certainly less
> > than 6 or 7. Rebooting does not seem to be the issue, nor does load level (no
> > reboots either before or after the error). I currently do not have access to
> > the SQL Server log. Will try to obtain it.
> >
> > MSDTC is not used by our application, but if a deployment decides to house
> > multiple databases on the SQLServer, wouldn't it be better to leave it on?
> >
> > --
> > Brad Ashforth
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Brad
> > >
> > > Prefixing with the schema name should improve performance and caching.
> > >
> > > If this problem is periodic you are not going to know for certain that you
> > > have cured it!! How often does it occur? Does it mainly occur after a re-boot
> > > or a high load levels? I know you have checked the Event logs what about the
> > > SQL Server log?
> > >
> > > If MSDTC is currently running try switching it off, as this should not
> > > effect your application if it is not being used!!
> > >
> > > John
> > >
> > >
> > >
> > >
> > >
> > > "Brad Ashforth" wrote:
> > >
> > > > Hi John. Thank you.
> > > >
> > > > We had not known about @.@.TRANCOUNT and agree that it makes more sense to
> > > > check this instead of trapping for a known error that is likely to occur (on
> > > > the other hand, isn't that what TRY/CATCH is supposed to do?) Anyway, I also
> > > > agree that this is not the likely cause of the error I originally posted a
> > > > question about.
> > > >
> > > > We do not reference other databases, and in most (99%?) cases do not use
> > > > fully qualified table names. This is because most tables are in the DBO
> > > > schema and do not get to the user's own schema ... each user temp table (for
> > > > reports, etc) are in their own schema. In most cases the object names follow
> > > > standard conventions and should not need braces. This is also not an error
> > > > that is easily repeatable. It has been seen before but have had trouble
> > > > duplicating it.
> > > >
> > > > Thanks,
> > > > Brad
> > > > --
> > > > Brad Ashforth
> > > >
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Brad
> > > > >
> > > > > Checking @.@.TRANCOUNT will tell you whether a transaction is in progress.
> > > > > Using a commit that when a transaction has not begun will cause an error:
> > > > >
> > > > > Server: Msg 3902, Level 16, State 1, Line 1
> > > > > The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
> > > > >
> > > > > Which you can easily avoid.
> > > > >
> > > > > I don't think this is the cause of the issue! Do you reference any other
> > > > > databases either in your stored procedures or triggers? Are you using
> > > > > multipart naming conventions? Are you enquoting your names using square
> > > > > braces []?
> > > > >
> > > > >
> > > > > John
> > > > >
> > > > > "Brad Ashforth" wrote:
> > > > >
> > > > > > Hi John ... by "maximum # of connections", is this at the SERVER or CLIENT
> > > > > > level? Our application is designed such that we do not make use of nested
> > > > > > transactions. We have a "transaction" routine that is called with either
> > > > > > "Begin", "Commit" or "Rollback", and if the routine is called with "Begin"
> > > > > > (as it was in this case), we first execute a commit (in case a transaction is
> > > > > > pending that was not committed or rolled back) and then do the BEGIN. Of
> > > > > > course, if when we issue the commit if there is no pending transaction, we
> > > > > > get an error ... but that is trapped for and ignored if we are trying to
> > > > > > begin a new transaction.
> > > > > >
> > > > > > So, that said ... there should only be one transaction active for the
> > > > > > application at any given time and if by chance we try to start another one
> > > > > > the code automatically commits the first one before starting the 2nd one.
> > > > > >
> > > > > > Is there a way to query how many connections there are?
> > > > > > --
> > > > > > Brad Ashforth
> > > > > >
> > > > > >
> > > > > > "John Bell" wrote:
> > > > > >
> > > > > > > Hi Brad
> > > > > > >
> > > > > > > This looks like a XACT_E_CONNECTION_DENIED error, which might be thrown if
> > > > > > > you already have the maximum number of connections.
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > > "Brad Ashforth" wrote:
> > > > > > >
> > > > > > > > A user has reported the message "A request to establish a connection with the
> > > > > > > > transaction manager was denied". The code is at this point attempting to
> > > > > > > > START a transaction. So far I have been unable to duplicate this, though the
> > > > > > > > user has been able to get through this code in the past. Of course, no known
> > > > > > > > changes.
> > > > > > > >
> > > > > > > > What causes this particular message?
> > > > > > > > --
> > > > > > > > Brad Ashforth

A problem with the SYSLOGINS view?

Howdy.
While attempting to write a stored proc, on a SQL Server 2000 machine running win2k, that would back up all the user logins, I noticed something a little odd in the syslogins view. I was wondering if anyone else has this problem.

Make sure you have a non NT login with a password on your server and you are viewing your results in query analyzer in grid mode.

do a Select * from master..syslogins

Now, look at the password column. On mine, if the password is not null, then it makes the field blank and bumps the rest of the results over one. So the in the language column, I see the encrypted password, and in the deny login column I see the default language... anyone else noticed this?Can anyone else please just try this so that I know I am not off my rocker? Select * from syslogins, and look at the password column. (do it in grid result mode in query analyzer.) Thanks.|||Q1 Can anyone else please just try this so that I know I am not off my rocker? Select * from syslogins, and look at the password column. (do it in grid result mode in query analyzer.) Thanks.
--A1 This should fix it:
ALTER VIEW syslogins AS SELECT
sid = convert(varbinary(85), sid),
status = convert(smallint, 8 +
CASE WHEN (xstatus & 2)=0 THEN 1 ELSE 2 END),
createdate = convert(datetime, xdate1),
updatedate = convert(datetime, xdate2),
accdate = convert(datetime, xdate1),
totcpu = convert(int, 0),
totio = convert(int, 0),
spacelimit = convert(int, 0),
timelimit = convert(int, 0),
resultlimit = convert(int, 0),
name = convert(sysname, name),
dbname = convert(sysname, db_name(dbid)),
password = convert(varbinary(256), password),
language = convert(sysname, language),
denylogin = convert(int, CASE WHEN (xstatus&1)=1 THEN 1 ELSE 0 END),
hasaccess = convert(int, CASE WHEN (xstatus&2)=2 THEN 1 ELSE 0 END),
isntname = convert(int, CASE WHEN (xstatus&4)=4 THEN 1 ELSE 0 END),
isntgroup = convert(int, CASE WHEN (xstatus&12)=4 THEN 1 ELSE 0 END),
isntuser = convert(int, CASE WHEN (xstatus&12)=12 THEN 1 ELSE 0 END),
sysadmin = convert(int, CASE WHEN (xstatus&16)=16 THEN 1 ELSE 0 END),
securityadmin = convert(int, CASE WHEN (xstatus&32)=32 THEN 1 ELSE 0 END),
serveradmin = convert(int, CASE WHEN (xstatus&64)=64 THEN 1 ELSE 0 END),
setupadmin = convert(int, CASE WHEN (xstatus&128)=128 THEN 1 ELSE 0 END),
processadmin = convert(int, CASE WHEN (xstatus&256)=256 THEN 1 ELSE 0 END),
diskadmin = convert(int, CASE WHEN (xstatus&512)=512 THEN 1 ELSE 0 END),
dbcreator = convert(int, CASE WHEN (xstatus&1024)=1024 THEN 1 ELSE 0 END),
bulkadmin = convert(int, CASE WHEN (xstatus&4096)=4096 THEN 1 ELSE 0 END),
loginname = convert(sysname, name)
FROM sysxlogins WHERE srvid IS NULL|||Yeah, changing the datatype to varbinary did fix it, however, being the system view that syslogins is, it wont let me save the changes. I ended up doing a select from the sysxlogins, decyphering the xstatus bit info, and scripting that out. Bummer that the view is broken though. It's good to know that I am not the only one with the problem.|||Try making sure you are logged in as dba, enable ad hoc system table updates, etc.?|||Yeah, I tried that... logged in with sa, enabled system table modification... I think maybe because it is a view instead of a table, it didnt like it. Anyway, no matter, my work around should function nicely.|||Odd, the syslogins view alteres each time I've attempted it.|||Well, enterprise manager let me alter the view. No idea why I couldnt do it with a script through query analyzer.|||You might want to check the Server connection properties in EM (just to see if you are using the same account you had attempted to use in isqlw, a.k.a. QA).|||This is strictly a visual problem (under very specific circumstances - not windows login using the grid in qa) - not a problem for your original task - making a backup - correct ?|||Well, ok, the original problem was as follows. One of our SQL servers here at work contains back end databases for customers websites. As you can imagine there are quite a few databases. (Over 1000) Each customer has a SQL login with access granted to their appropriate database. That login information is saved, as you know, in the sysxlogins table in the master database. Now in our back up schema, master isnt backed up that often, so we needed a method of backing up the login info. My original thought was to take the information from syslogins and script it into a table in a database that is backed up every 15 minutes. This is needed because on average 3 to 5 new databases can be added per day, and 2 to 3 deleted. When I went to select the records from the syslogins, I realized that the view had the bug and wanted to make sure that it was everywhere and not only on my machines.

As for the solution, I pull the login data directly from sysxlogins table and populate my Login_Info table. And all is happy. :D|||I just wanted to make sure - I have been using this view without problems - it retrieves and stores the data appropriately. Only through qa in grid mode does this problem appear.