"SQL Server does not exist or access denied", again...

I'm have that darn problem...

Here is our setup:

Remote clustered SQL Server 2000 running on a Windows Server 2003 Enterprise Edition box.

Local IIS 5.1 web server (my development box) running on Windows XP Tablet Edition.

I'm using Visual Studio.Net 2003 and using ASP.Net 1.1.4322.

I have an ASP.Net application that hits the remote SQL Server. When I'm on my development box and viewing my ASP.Net project locally via IE, the application retrieves values from the remote SQL Server with no problems. If I get onto another machine, launch IE, and then hit my development box's web server (which is running the asp.net app), I get the dreaded "SQL Server does not exist or access is denied" msg.

In my ASP.Net app, I have impersonate set to true and the website is set only to windows authentication. The remote SQL Server is set to SQL and Windows authentication.

I'm at a loss on what to do, any help would be greatly appreciated.

Thanks,

ExitusLSU

Hi,

This seems to be firewall issue.
Check it after disabling firewall.
http://bhatiaworld.blogspot.com/2005/12/dot-net-error-aspnet-website-or-web.html|||

I also forgot to mention my connection string. It is:

"Data Source=<server name>;Integrated Security=SSPI;Initial Catalog=<database name>"

I've tried using the IP address, but that didn't work.

Thanks,

ExitusLSU

"Local" Server

All:
I was using a 120 day eval of SQL Server, and then purchased the developers
edition. During the install....and don't ask me exactly how I did it, I
ended up naming my "new" server TVB\TVB - the old one was just TVB, and I
managed to remove the TVB and now have no "local" server, so to speak. I
can't find the setting to rectify this, although the database works
OK...but when using connection strings, I can't use localhost in the code,
I have to use the actual name, fortunately I can still use it in the URL.
This is probably easy to rectify if one knows where to look or what to do,
and I fear this may cause a problem bigger than the minor annoyance it is
now down the line. (Plus I only have a few apps set up , many more to come)
Advice greatly appreciated, thanks in advance.
Tom BThe only way to rectify this is to uninstall the named instance and
reinstall as a default instance. You cannot rename a SQL Server named
instance. If you have databases you want to keep then you can back them up
or detach them before uninstalling and then restore or attach them to your
default instance (that will have the same name as the server)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tom Bernsen" <t.bernsen@.worldnet.att.net> wrote in message
news:%23VGj0hN9DHA.2064@.TK2MSFTNGP11.phx.gbl...
> All:
> I was using a 120 day eval of SQL Server, and then purchased the
developers
> edition. During the install....and don't ask me exactly how I did it, I
> ended up naming my "new" server TVB\TVB - the old one was just TVB, and I
> managed to remove the TVB and now have no "local" server, so to speak. I
> can't find the setting to rectify this, although the database works
> OK...but when using connection strings, I can't use localhost in the
code,
> I have to use the actual name, fortunately I can still use it in the URL.
> This is probably easy to rectify if one knows where to look or what to do,
> and I fear this may cause a problem bigger than the minor annoyance it is
> now down the line. (Plus I only have a few apps set up , many more to
come)
> Advice greatly appreciated, thanks in advance.
> Tom B
>

"Full-Text Index Table" is disabled?

I'm trying to do some local development so I installed Full-Text Indexing as
part of the SQL 2000 Developer Edition on my Windows XP machine.
The MS Search Service is running but the option is still disabled in EM when
I right-click on a table.
Thanks, Dave.
http://www.indexserverfaq.com/tablenotenabled.htm
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BE6EABAB-0F45-40B2-9C9C-1A29A1A8746B@.microsoft.com...
> I'm trying to do some local development so I installed Full-Text Indexing
> as
> part of the SQL 2000 Developer Edition on my Windows XP machine.
> The MS Search Service is running but the option is still disabled in EM
> when
> I right-click on a table.
> Thanks, Dave.

"Full Text Catalogs" Option

Hello, I have SQL Server 2K Developer Edition installed on Win2K Pro. I am trying to prep a DB for Full text Search and, I can't find the option for "Full Text Catalogs" for that or any Database.
Is this option only available for SQL Server 2K Enterprise Edition which - BTW - cannot be installed on Win2K Pro - only Server?
Can some help and Advice as to how can I do this. TIA.
Hi,
Full text search (MS Serach service) is avilable for SQL2000 developer
edition.Usually, in SQL2000, it gets installed by default for even a minimum
installation.Iam not sure why it is missing in your case.Check the
sqlstp.log file. To install FT you can pop in the SQL2000 setup CD and check
the relevant option.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"JSN" <anonymous@.discussions.microsoft.com> wrote in message
news:DFDAF597-EAF9-47CC-99A2-03A579E2E156@.microsoft.com...
> Hello, I have SQL Server 2K Developer Edition installed on Win2K Pro. I
am trying to prep a DB for Full text Search and, I can't find the option for
"Full Text Catalogs" for that or any Database.
> Is this option only available for SQL Server 2K Enterprise Edition which -
BTW - cannot be installed on Win2K Pro - only Server?
> Can some help and Advice as to how can I do this. TIA.
|||Oops..didnt notice the OS. MS Search service will not run on Win2K Pro.
Dinesh
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:#JUo6rgSEHA.808@.tk2msftngp13.phx.gbl...
> Hi,
> Full text search (MS Serach service) is avilable for SQL2000 developer
> edition.Usually, in SQL2000, it gets installed by default for even a
minimum
> installation.Iam not sure why it is missing in your case.Check the
> sqlstp.log file. To install FT you can pop in the SQL2000 setup CD and
check
> the relevant option.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "JSN" <anonymous@.discussions.microsoft.com> wrote in message
> news:DFDAF597-EAF9-47CC-99A2-03A579E2E156@.microsoft.com...
> am trying to prep a DB for Full text Search and, I can't find the option
for[vbcol=seagreen]
> "Full Text Catalogs" for that or any Database.
which -
> BTW - cannot be installed on Win2K Pro - only Server?
>

"Full Text Catalogs" Option

Hello, I have SQL Server 2K Developer Edition installed on Win2K Pro. I am
trying to prep a DB for Full text Search and, I can't find the option for "F
ull Text Catalogs" for that or any Database.
Is this option only available for SQL Server 2K Enterprise Edition which - B
TW - cannot be installed on Win2K Pro - only Server?
Can some help and Advice as to how can I do this. TIA.Hi,
Full text search (MS Serach service) is avilable for SQL2000 developer
edition.Usually, in SQL2000, it gets installed by default for even a minimum
installation.Iam not sure why it is missing in your case.Check the
sqlstp.log file. To install FT you can pop in the SQL2000 setup CD and check
the relevant option.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"JSN" <anonymous@.discussions.microsoft.com> wrote in message
news:DFDAF597-EAF9-47CC-99A2-03A579E2E156@.microsoft.com...
> Hello, I have SQL Server 2K Developer Edition installed on Win2K Pro. I
am trying to prep a DB for Full text Search and, I can't find the option for
"Full Text Catalogs" for that or any Database.
> Is this option only available for SQL Server 2K Enterprise Edition which -
BTW - cannot be installed on Win2K Pro - only Server?
> Can some help and Advice as to how can I do this. TIA.|||Oops..didnt notice the OS. MS Search service will not run on Win2K Pro.
Dinesh
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:#JUo6rgSEHA.808@.tk2msftngp13.phx.gbl...
> Hi,
> Full text search (MS Serach service) is avilable for SQL2000 developer
> edition.Usually, in SQL2000, it gets installed by default for even a
minimum
> installation.Iam not sure why it is missing in your case.Check the
> sqlstp.log file. To install FT you can pop in the SQL2000 setup CD and
check
> the relevant option.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "JSN" <anonymous@.discussions.microsoft.com> wrote in message
> news:DFDAF597-EAF9-47CC-99A2-03A579E2E156@.microsoft.com...
> am trying to prep a DB for Full text Search and, I can't find the option
for
> "Full Text Catalogs" for that or any Database.
which -[vbcol=seagreen]
> BTW - cannot be installed on Win2K Pro - only Server?
>

"Full Text Catalogs" Option

Hello, I have SQL Server 2K Developer Edition installed on Win2K Pro. I am trying to prep a DB for Full text Search and, I can't find the option for "Full Text Catalogs" for that or any Database
Is this option only available for SQL Server 2K Enterprise Edition which - BTW - cannot be installed on Win2K Pro - only Server
Can some help and Advice as to how can I do this. TIA.Hi,
Full text search (MS Serach service) is avilable for SQL2000 developer
edition.Usually, in SQL2000, it gets installed by default for even a minimum
installation.Iam not sure why it is missing in your case.Check the
sqlstp.log file. To install FT you can pop in the SQL2000 setup CD and check
the relevant option.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"JSN" <anonymous@.discussions.microsoft.com> wrote in message
news:DFDAF597-EAF9-47CC-99A2-03A579E2E156@.microsoft.com...
> Hello, I have SQL Server 2K Developer Edition installed on Win2K Pro. I
am trying to prep a DB for Full text Search and, I can't find the option for
"Full Text Catalogs" for that or any Database.
> Is this option only available for SQL Server 2K Enterprise Edition which -
BTW - cannot be installed on Win2K Pro - only Server?
> Can some help and Advice as to how can I do this. TIA.|||Oops..didnt notice the OS. MS Search service will not run on Win2K Pro.
--
Dinesh
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:#JUo6rgSEHA.808@.tk2msftngp13.phx.gbl...
> Hi,
> Full text search (MS Serach service) is avilable for SQL2000 developer
> edition.Usually, in SQL2000, it gets installed by default for even a
minimum
> installation.Iam not sure why it is missing in your case.Check the
> sqlstp.log file. To install FT you can pop in the SQL2000 setup CD and
check
> the relevant option.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "JSN" <anonymous@.discussions.microsoft.com> wrote in message
> news:DFDAF597-EAF9-47CC-99A2-03A579E2E156@.microsoft.com...
> > Hello, I have SQL Server 2K Developer Edition installed on Win2K Pro. I
> am trying to prep a DB for Full text Search and, I can't find the option
for
> "Full Text Catalogs" for that or any Database.
> >
> > Is this option only available for SQL Server 2K Enterprise Edition
which -
> BTW - cannot be installed on Win2K Pro - only Server?
> >
> > Can some help and Advice as to how can I do this. TIA.
>

"Failed to generate a user instance of SQL Server" - Redux

I have SQL Server 2005 SP1 Dev Edition installed on my laptop along with Visual Web Developer 2005. I installed SQL Server Express 2005 SP1. I then created a new web site using the Club Web Site template.

I did the recommended initial build to get the Membership database installed and got an error page (http://localhost:1667/WebSitesVB2/):

SQLExpress database file auto-creation error:
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

There were 4 recomme4ndations from the error page. I have met three of them: (1) the AppDaa folder that was created when I used the Club Web Site template; (2) my machine's ASPNET user set up with full control permissions on the APP Data Folder; (3) SQL Server Express successfully installed on my machine.

The 4th recommendation from the error page is "The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts. ". I have no idea of how to do this or how/where to find the elusive the "readme" file.

I am at a loss as to what to do next. I have spent the day going over forums on this error and none seem to provide consistent or relevant answers.

Try to remove User Instance=true from your connection string, if you don't need to generate a user instance at runtime. For more information about the user instance attribute, you can refer to:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx|||

Changed userinstance = TRUE to userinstance=FALSE. Same result - failure to generate userinstance.

Is it possible that there is some setting in VWD that provides a connection string for the ASPNET db that is to be created? The only connection string in the WebConfig from the original template is the one for the ClubSite DB. There is none for the ASPNET db (because the template does not provide the ASPNET DB.

After 2 tries, I still get this error and when I look at the database explorer, there is only one DB file: Club.MDF.

Anyone else have any answers?

|||

I probably should have included this stack trace:

Stack Trace:

[SqlException (0x80131904): Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +115 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +56 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +1084 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +272 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +355 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +91 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +115 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +122 System.Data.SqlClient.SqlConnection.Open() +229 System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +110[HttpException (0x80004005): Unable to connect to SQL Server database.] System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +181 System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install) +167 System.Web.Management.SqlServices.Install(String database, String dbFileName, String connectionString) +46 System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) +681

---

|||Sorry I forgot to mention: there is a connection string named LocalSqlServer, which is used by .NET 2.0 built-in providers (Profile, Membership, RoleManager, etc.). You can override it and try again, for details you can refer to:
Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

"downgrade" edition ?

Hi
I save SQLServer 7.0 enterprise edition installed on NT
4.0 PDC.
And now i want to install SQLServer 2000 standard edition
and upgrade default instance. When i run SQLServer 2000
setup, the option "Upgrade..." is not available.
I think it happens because different version: enterprise
and standart (or not ?)
How i can "downgrade" SQLServer edition ?
Or i should backup all 7 databases, install SQLServer 2000
and restore all databases from backup,include master (to
preserve logins)?I can point you in the direction of a good article on this subject :-
http://www.sqlteam.com/item.asp?ItemID=9066
--
HTH
Ryan Waight, MCDBA, MCSE
"Slava Melnikov" <melnikov@.pochta.ru> wrote in message
news:0a0201c38eed$a99fee90$a301280a@.phx.gbl...
> Hi
> I save SQLServer 7.0 enterprise edition installed on NT
> 4.0 PDC.
> And now i want to install SQLServer 2000 standard edition
> and upgrade default instance. When i run SQLServer 2000
> setup, the option "Upgrade..." is not available.
> I think it happens because different version: enterprise
> and standart (or not ?)
> How i can "downgrade" SQLServer edition ?
> Or i should backup all 7 databases, install SQLServer 2000
> and restore all databases from backup,include master (to
> preserve logins)?

"Default bound to column

I am running a script against a couple of databases on my SQL Server 2000 Standard Edition Instance and I am getting the following in the results pane:

"Default bound to column" I have searched the MS Knowledge base and found a couple of vague references to this. Does anyone know why I might be getting this? the script 'seems' to run fine.. except for the funky error in the results pane. Script is attached. Thank you!!maybe no one has answered because zip files make people nervous (viruses). Just post the code using the tags. I know I am not opening your zip file.|||IIRC the sp_bindefault will give that message; just run one of them to confirm that.

ALSO never zip up a SQL script as a DOC file and think you are going to get an answer.

Change it to a text file and then only if it is very large zip it up.

doc files are the most common file used to tranmit virii to other people followed by zip files so very few people word open your file.

I have both zip & doc files set to open with non-MS software so I should be safe but I almost did not open the doc.

Tim S

"Data Source=localhost;..." fails with : General network error.

I replaced SQL 2000 with SQL 2005 Developer edition and my application could
not connect
to the local SQL server anymore. When connecting from a .Net 1.1 application
using the
following connection string:
"Data Source=localhost;Database=MM;Integrated Security=SSPI"
I always received the following exception message:
"General network error. Check your network documentation."
I had no problem to connect when using the host name instead of "localhost".
The problem
got resolved when I started "SQL Server Surface Area Configuration" and conf
igured it as
follow:
Surface Area Configuration for Services and Connections -> Database Engine -
> Remote
Connections -> Local and remote connections -> Using TCP/IP only.
The SQL 2005 Developer Edition is configured out of the box with "Local conn
ections only"
and "localhost" for some reason is not recognized as a local connection !!!!Try . or (local) or servername or IP address.
"Gaetan" <someone@.somewhere.com> wrote in message
news:14fhn1dr367kni9qt8vp7j5rb70imtelhl@.
4ax.com...
>I replaced SQL 2000 with SQL 2005 Developer edition and my application
>could not connect
> to the local SQL server anymore. When connecting from a .Net 1.1
> application using the
> following connection string:
> "Data Source=localhost;Database=MM;Integrated Security=SSPI"
> I always received the following exception message:
> "General network error. Check your network documentation."
> I had no problem to connect when using the host name instead of
> "localhost". The problem
> got resolved when I started "SQL Server Surface Area Configuration" and
> configured it as
> follow:
> Surface Area Configuration for Services and Connections -> Database
> Engine -> Remote
> Connections -> Local and remote connections -> Using TCP/IP only.
> The SQL 2005 Developer Edition is configured out of the box with "Local
> connections only"
> and "localhost" for some reason is not recognized as a local connection
> !!!!
>

"Cannot start more transactions on this session" - SQL 2005 via ADO

Hi all,
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim

"Cannot start more transactions on this session" - SQL 2005 via ADO

Hi all,
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.
4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim

"Cannot start more transactions on this session" - SQL 2005 via ADO

Hi all,
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim