Ok, here is my dilemma
I have an application that has many sites. Each site has it's own database. The databases have common tables (ie the name and fields are the same) What I want to be able to do is when creating a new database, I want to be able to copy certain common table data from one database to another. I have run into an error because the table have an IDENTITY so this is not working
INSERT INTO Containers SELECT * FROM ADMS2_Indian_Point.dbo.Containers
I also tried
USE ADMS2_RSCS
GO
SET IDENTITY_INSERT Containers ON
GO
INSERT INTO Containers SELECT * FROM ADMS2_Indian_Point.dbo.Containers
GO
SET IDENTITY_INSERT Containers OFF
GO
I got an error saying that I need to have a column list. I am trying to use this for any tables, so my question is this..
Is there any way to get around using a column list or is there a way to dynamically create the column list?
Or, Is there a better way that I should be doing this?
Please keep in mind I am not a dba and everything I have learned about SQL is from my good pal Google :)
Thanks for any helpWhy can't you just write out the column list?
using select * in anything but throw away code is a no-no.|||just write the column list as jezemine suggests
OR
use "select into" instead of "insert into". remember the table will be created fresh (should not already exists) and will be created without index etc.
OR
create necessary common tables with data, index everything in "model" database. every new database u create thereafter will have all those objects ready immediately after creation. no sql needed.|||If your server is dedicated to these databases, then I would suggest copying all of the objects and data that you need to the Model database. After that, every database you create will be a copy of model and will have everything you need.
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Monday, March 19, 2012
[SQL Server 2000] How to Interpret Server Trace / How to Get More Details?
Hello all,
An application that i wrote (that interacts with SQL Server 2000) is causing deadlocks on the server. i have no direct access to the server, but the user have sent me a server trace.
The relevant part of the trace is:
Code Snippet
2007-04-04 11:29:13.01 spid4 End deadlock search 296 ... a deadlock was found.
2007-04-04 11:29:13.01 spid4
2007-04-04 11:29:13.01 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:116 ECID:0 Ec:(0xC62A54F8) Value:0x80
2007-04-04 11:29:13.01 spid4 Victim Resource Owner:
2007-04-04 11:29:13.01 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:122 ECID:0 Ec:(0xC61DF4F8) Value:0xa
2007-04-04 11:29:13.01 spid4 Requested By:
2007-04-04 11:29:13.01 spid4 Input Buf: RPC Event: sp_execute;1
2007-04-04 11:29:13.01 spid4 SPID: 116 ECID: 0 Statement Type: UPDATE Line #: 1
2007-04-04 11:29:13.01 spid4 Owner:0x8849cba0 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:116 ECID:0
2007-04-04 11:29:13.01 spid4 Grant List 0::
2007-04-04 11:29:13.01 spid4 PAG: 12:1:42176 CleanCnt:2 Mode: X Flags: 0x2
2007-04-04 11:29:13.01 spid4 Node:2
2007-04-04 11:29:13.01 spid4
2007-04-04 11:29:13.01 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:116 ECID:0 Ec:(0xC62A54F8) Value:0x80
2007-04-04 11:29:13.01 spid4 Requested By:
2007-04-04 11:29:13.01 spid4 Input Buf: RPC Event: sp_execute;1
2007-04-04 11:29:13.01 spid4 SPID: 122 ECID: 0 Statement Type: INSERT Line #: 1
2007-04-04 11:29:13.01 spid4 Owner:0x800936e0 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2007-04-04 11:29:13.01 spid4 Grant List 2::
2007-04-04 11:29:13.01 spid4 PAG: 12:1:42157 CleanCnt:2 Mode: IX Flags: 0x0
2007-04-04 11:29:13.01 spid4 Node:1
2007-04-04 11:29:13.01 spid4
2007-04-04 11:29:13.01 spid4 Wait-for graph
2007-04-04 11:29:13.01 spid4
2007-04-04 11:29:13.01 spid4 ...
2007-04-04 11:29:13.01 spid4
2007-04-04 11:29:13.01 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode: S SPID:116 ECID:0 Ec:(0xC62A54F8) Va
2007-04-04 11:29:13.01 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode: IX SPID:122 ECID:0 Ec:(0xC61DF4F8) V
2007-04-04 11:29:13.01 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: S SPID:116 ECID:0 Ec:(0xC62A54F8) Va
2007-04-04 11:29:13.01 spid4 Deadlock cycle was encountered .... verifying cycle
2007-04-04 11:29:13.01 spid4
2007-04-04 11:29:13.01 spid4
2007-04-04 11:29:13.01 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode: S SPID:116 ECID:0 Ec:(0xC62A54F8) Va
2007-04-04 11:29:13.01 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode: IX SPID:122 ECID:0 Ec:(0xC61DF4F8) V
2007-04-04 11:29:13.01 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: S SPID:116 ECID:0 Ec:(0xC62A54F8) Va
2007-04-04 11:29:13.01 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:116 ECID:0 Ec:(0xC62A54F8) Value:0x80
2007-04-04 11:29:13.01 spid4 Target Resource Owner:
2007-04-04 11:29:13.01 spid4 Starting deadlock search 296
Having very little experience with SQL Server 2000, all that i learn from the trace is that, well, there is a deadlock, but i had already known that beforehand.
What other useful details can be interpreted from the trace above?
How can i obtain information like the specific statement(s) which cause the deadlock, etc?
Thanks in Advance,
Edwin
Not sure if you have the complete information in your snippet...having a running Profiler trace that captures the relevant time period can be helpful. That will be the only way to see the specific statements.
SPIDs 116 and 122 deadlocked with each other. 116 was running an UPDATE and 122 was trying to INSERT. 122 was chosen as the victim (SQL will generally choose the SPID with the fewest resources to minimize rollback time). Your app should detect and trap error 1205 and re-submit the deadlocked query.
Minimizing deadlocks from within the database will almost always come down to tweaking your indexes. It is hard to comment further without knowing your schema for the table in question. Your open transactions should be kept as short as possible too.
-MichaelThursday, March 8, 2012
[OTP] Multi User Application Database Transaction Stability
I am currently developing a multi user VB.NET 2005 application and this is my first time to involve in multi user application. Anyway, i know that multi-user application is not as easy as standalone application. I need to take care of the database stability for each and every user. My concern is may i know how to build up this stability for each and every transaction in terms of Data insert and update? This database resides on server and the user is able to access the data through the client application.
INSERT
The primary key is generated by application when the user enters into the data insert screen and it is done by counting the total amount of records in database. If two users perform insert at the same time, may i know how to deal with these primary keys? I am sure my application must be able to generate two different primary keys for these two users based on the time gap e.g. 0.001 milli second for the first user who enters into the screen and occupy the first primary key than the second user. But i have no idea on how to translate this to code.
UPDATE
If a particular record is viewed by a user and that user peforms an updates on the record while another user updates it at the same time, i may need to figure out a solution for this else the database will really be crashed. Is there any suggestion on it? May i know how to implement lock and unlock the record? Thank you.
For the INSERT part, you can't really generate keys like that from the client side without locking the entire table on the server. You can look at use of IDENTITY column for example which allows you to generate sequential numbers on the server. The server handles the concurrency part. There are other ways to generate keys on the client like using GUID for example. But all of these depends on the type of application you are writing and your data model.
For the UPDATE part, when one connection is updating a particular row or rows then any other connection that tries to update the same row(s) will be blocked. So the concurrent updates will happen in serial fashion depending on which ran first.
My suggestion would be to look at some of the topics on concurrency, locking, transactions in Books Online to get an idea of what the database engine can do. You should also get a book on SQL Server that describes some of these concepts so you don't try to invent or reinvent elaborate techniques on the client which might be suspect at best.
Tuesday, March 6, 2012
[Microsoft][SQLServer 2000 Driver for JDBC]net dll not initi
We are currently in the middle of piloting a new application using
MSDE, and some of our users are getting the following SQLException
when attempting to query the database. The exception occurs
sporadically, and it only seems to happen after the application is
used for an extended duraton of time. Also, once the exception
occurs, it seems like the only way to obtain a good connection again
is to restart the JVM. Successive calls to get a connection from the
drivermanager fail otherwise.
Our calling application uses JNI to call a java function which is
responsible for creating the database connection and querying the
database.
Any help would be appreciated!
Here's the exception:
[Microsoft][SQLServer 2000 Driver for JDBC]net dll not initialized
(WSAStartup): socket write error
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]net
dll not initialized (WSAStartup): socket write error
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.sub mitRequest(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest. openCursor(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.execute(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.executeQuery Internal(Unknown
Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec uteQuery(Unknown
Source)
Hello,
WSAStartup is a Windows API that is called (typically once) during an
application's startup to start using Winsock calls. Since you have been
using sockets up until the failure, it had to be called prior to that. It
looks like somehow the Winsock got uninitialized or unloaded but the JVM is
unaware of that.
Can you try opening a socket directly when this happens to confirm? You
could try something like:
java.net.Socket conn = new java.net.Socket("localhost", 1433);
and if you get the same failure, I would say this is an issue in the JVM.
Thanks,
Kamil
Kamil Sykora
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/securXity.
| NNTP-Posting-Date: Wed, 13 Jul 2005 00:58:48 -0500
| Subject: [Microsoft][SQLServer 2000 Driver for JDBC]net dll not initi
| From: akraai@.hotmail-dot-com.no-spam.invalid (akraai)
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Mime-Version: 1.0
| Content-Type: text/plain; charset=ISO-8859-15
| Content-Transfer-Encoding: 8bit
| User-Agent: newsSync (SQL Server) 112669
| Message-ID: <foWdneohifEFMEnfRVn_vg@.giganews.com>
| Date: Wed, 13 Jul 2005 00:58:48 -0500
|
| We are currently in the middle of piloting a new application using
MSDE, and some of our users are getting the following SQLException
when attempting to query the database. The exception occurs
sporadically, and it only seems to happen after the application is
used for an extended duraton of time. Also, once the exception
occurs, it seems like the only way to obtain a good connection again
is to restart the JVM. Successive calls to get a connection from the
drivermanager fail otherwise.
Our calling application uses JNI to call a java function which is
responsible for creating the database connection and querying the
database.
Any help would be appreciated!
Here's the exception:
[Microsoft][SQLServer 2000 Driver for JDBC]net dll not initialized
(WSAStartup): socket write error
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]net
dll not initialized (WSAStartup): socket write error
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.sub mitRequest(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest. openCursor(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.execute(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.executeQuery Internal(Unknown
Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec uteQuery(Unknown
Source)
|
MSDE, and some of our users are getting the following SQLException
when attempting to query the database. The exception occurs
sporadically, and it only seems to happen after the application is
used for an extended duraton of time. Also, once the exception
occurs, it seems like the only way to obtain a good connection again
is to restart the JVM. Successive calls to get a connection from the
drivermanager fail otherwise.
Our calling application uses JNI to call a java function which is
responsible for creating the database connection and querying the
database.
Any help would be appreciated!
Here's the exception:
[Microsoft][SQLServer 2000 Driver for JDBC]net dll not initialized
(WSAStartup): socket write error
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]net
dll not initialized (WSAStartup): socket write error
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.sub mitRequest(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest. openCursor(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.execute(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.executeQuery Internal(Unknown
Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec uteQuery(Unknown
Source)
Hello,
WSAStartup is a Windows API that is called (typically once) during an
application's startup to start using Winsock calls. Since you have been
using sockets up until the failure, it had to be called prior to that. It
looks like somehow the Winsock got uninitialized or unloaded but the JVM is
unaware of that.
Can you try opening a socket directly when this happens to confirm? You
could try something like:
java.net.Socket conn = new java.net.Socket("localhost", 1433);
and if you get the same failure, I would say this is an issue in the JVM.
Thanks,
Kamil
Kamil Sykora
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/securXity.
| NNTP-Posting-Date: Wed, 13 Jul 2005 00:58:48 -0500
| Subject: [Microsoft][SQLServer 2000 Driver for JDBC]net dll not initi
| From: akraai@.hotmail-dot-com.no-spam.invalid (akraai)
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Mime-Version: 1.0
| Content-Type: text/plain; charset=ISO-8859-15
| Content-Transfer-Encoding: 8bit
| User-Agent: newsSync (SQL Server) 112669
| Message-ID: <foWdneohifEFMEnfRVn_vg@.giganews.com>
| Date: Wed, 13 Jul 2005 00:58:48 -0500
|
| We are currently in the middle of piloting a new application using
MSDE, and some of our users are getting the following SQLException
when attempting to query the database. The exception occurs
sporadically, and it only seems to happen after the application is
used for an extended duraton of time. Also, once the exception
occurs, it seems like the only way to obtain a good connection again
is to restart the JVM. Successive calls to get a connection from the
drivermanager fail otherwise.
Our calling application uses JNI to call a java function which is
responsible for creating the database connection and querying the
database.
Any help would be appreciated!
Here's the exception:
[Microsoft][SQLServer 2000 Driver for JDBC]net dll not initialized
(WSAStartup): socket write error
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]net
dll not initialized (WSAStartup): socket write error
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.sub mitRequest(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest. openCursor(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.execute(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.executeQuery Internal(Unknown
Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec uteQuery(Unknown
Source)
|
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
I'm using JBuilder X, writing a client application and connecting to
database
SQL Server 2000 with JDBC driver.
when i have Windows Xp Pro with service Pack 1
i don't have a problem
all works
now with Windows Xp Pro and service Pack 2 i have this problem
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
See com.borland.dx.dataset.DataSetException error code: BASE+66
com.borland.dx.dataset.DataSetException: [Microsoft][SQLServer 2000 Driver
for JDBC]Error establishing socket.
at com.borland.dx.dataset.DataSetException.a(Unknown Source)
at com.borland.dx.dataset.DataSetException.throwExcep tion(Unknown Source)
at com.borland.dx.dataset.DataSetException.SQLExcepti on(Unknown Source)
at com.borland.dx.sql.dataset.Database.openConnection (Unknown Source)
at com.borland.dx.sql.dataset.Database.createPrepared Statement(Unknown
Source)
at com.borland.dx.sql.dataset.o.a(Unknown Source)
at com.borland.dx.sql.dataset.o.d(Unknown Source)
at com.borland.dx.sql.dataset.o.f(Unknown Source)
at com.borland.dx.sql.dataset.QueryProvider.e(Unknown Source)
at com.borland.dx.sql.dataset.JdbcProvider.provideDat a(Unknown Source)
at com.borland.dx.dataset.StorageDataSet.refresh(Unkn own Source)
at com.borland.dx.sql.dataset.QueryDataSet.refresh(Un known Source)
at com.borland.dx.sql.dataset.QueryDataSet.executeQue ry(Unknown Source)
at prog.Login.buttonControl1_actionPerformed(Login.ja va:131)
at prog.Login.buttonControl1_keyReleased(Login.java:2 07)
at prog.Login$2.keyReleased(Login.java:79)
at java.awt.AWTEventMulticaster.keyReleased(AWTEventM ulticaster.java:201)
at java.awt.Component.processKeyEvent(Component.java: 3680)
at javax.swing.JComponent.processKeyEvent(JComponent. java:2014)
at java.awt.Component.processEvent(Component.java:355 3)
at java.awt.Container.processEvent(Container.java:116 4)
at java.awt.Component.dispatchEventImpl(Component.jav a:2593)
at java.awt.Container.dispatchEventImpl(Container.jav a:1213)
at java.awt.Component.dispatchEvent(Component.java:24 97)
at java.awt.LightweightDispatcher.processKeyEvent(Con tainer.java:2155)
at java.awt.LightweightDispatcher.dispatchEvent(Conta iner.java:2135)
at java.awt.Container.dispatchEventImpl(Container.jav a:1200)
at java.awt.Window.dispatchEventImpl(Window.java:914)
at java.awt.Component.dispatchEvent(Component.java:24 97)
at java.awt.EventQueue.dispatchEvent(EventQueue.java: 339)
at
java.awt.EventDispatchThread.pumpOneEventForHierar chy
(EventDispatchThread.java:131)
at
java.awt.EventDispatchThread.pumpEventsForHierarch y
(EventDispatchThread.java:98)
at java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThre ad.java:85)
Chained exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error
establishing socket.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSConnection.<in it>(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplConnecti on.open(Unknown
Source)
at com.microsoft.jdbc.base.BaseConnection.getNewImplC onnection(Unknown
Source)
at com.microsoft.jdbc.base.BaseConnection.open(Unknow n Source)
at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager .java:517)
at java.sql.DriverManager.getConnection(DriverManager .java:177)
at com.borland.dx.sql.dataset.Database.openConnection (Unknown Source)
at com.borland.dx.sql.dataset.Database.createPrepared Statement(Unknown
Source)
at com.borland.dx.sql.dataset.o.a(Unknown Source)
at com.borland.dx.sql.dataset.o.d(Unknown Source)
at com.borland.dx.sql.dataset.o.f(Unknown Source)
at com.borland.dx.sql.dataset.QueryProvider.e(Unknown Source)
at com.borland.dx.sql.dataset.JdbcProvider.provideDat a(Unknown Source)
at com.borland.dx.dataset.StorageDataSet.refresh(Unkn own Source)
at com.borland.dx.sql.dataset.QueryDataSet.refresh(Un known Source)
at com.borland.dx.sql.dataset.QueryDataSet.executeQue ry(Unknown Source)
at prog.Login.buttonControl1_actionPerformed(Login.ja va:131)
at prog.Login.buttonControl1_keyReleased(Login.java:2 07)
at prog.Login$2.keyReleased(Login.java:79)
at java.awt.AWTEventMulticaster.keyReleased(AWTEventM ulticaster.java:201)
at java.awt.Component.processKeyEvent(Component.java: 3680)
at javax.swing.JComponent.processKeyEvent(JComponent. java:2014)
at java.awt.Component.processEvent(Component.java:355 3)
at java.awt.Container.processEvent(Container.java:116 4)
at java.awt.Component.dispatchEventImpl(Component.jav a:2593)
at java.awt.Container.dispatchEventImpl(Container.jav a:1213)
at java.awt.Component.dispatchEvent(Component.java:24 97)
at java.awt.LightweightDispatcher.processKeyEvent(Con tainer.java:2155)
at java.awt.LightweightDispatcher.dispatchEvent(Conta iner.java:2135)
at java.awt.Container.dispatchEventImpl(Container.jav a:1200)
at java.awt.Window.dispatchEventImpl(Window.java:914)
at java.awt.Component.dispatchEvent(Component.java:24 97)
at java.awt.EventQueue.dispatchEvent(EventQueue.java: 339)
at
java.awt.EventDispatchThread.pumpOneEventForHierar chy
(EventDispatchThread.java:131)
at
java.awt.EventDispatchThread.pumpEventsForHierarch y
(EventDispatchThread.java:98)
at java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThre ad.java:85)
ava.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error
establishing socket.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSConnection.<in it>(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplConnecti on.open(Unknown
Source)
at com.microsoft.jdbc.base.BaseConnection.getNewImplC onnection(Unknown
Source)
at com.microsoft.jdbc.base.BaseConnection.open(Unknow n Source)
at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager .java:517)
at java.sql.DriverManager.getConnection(DriverManager .java:177)
at com.borland.dx.sql.dataset.Database.openConnection (Unknown Source)
at com.borland.dx.sql.dataset.Database.createPrepared Statement(Unknown
Source)
at com.borland.dx.sql.dataset.o.a(Unknown Source)
at com.borland.dx.sql.dataset.o.d(Unknown Source)
at com.borland.dx.sql.dataset.o.f(Unknown Source)
at com.borland.dx.sql.dataset.QueryProvider.e(Unknown Source)
at com.borland.dx.sql.dataset.JdbcProvider.provideDat a(Unknown Source)
at com.borland.dx.dataset.StorageDataSet.refresh(Unkn own Source)
at com.borland.dx.sql.dataset.QueryDataSet.refresh(Un known Source)
at com.borland.dx.sql.dataset.QueryDataSet.executeQue ry(Unknown Source)
at prog.Login.buttonControl1_actionPerformed(Login.ja va:131)
at prog.Login.buttonControl1_keyReleased(Login.java:2 07)
at prog.Login$2.keyReleased(Login.java:79)
at java.awt.AWTEventMulticaster.keyReleased(AWTEventM ulticaster.java:201)
at java.awt.Component.processKeyEvent(Component.java: 3680)
at javax.swing.JComponent.processKeyEvent(JComponent. java:2014)
at java.awt.Component.processEvent(Component.java:355 3)
at java.awt.Container.processEvent(Container.java:116 4)
at java.awt.Component.dispatchEventImpl(Component.jav a:2593)
at java.awt.Container.dispatchEventImpl(Container.jav a:1213)
at java.awt.Component.dispatchEvent(Component.java:24 97)
at java.awt.LightweightDispatcher.processKeyEvent(Con tainer.java:2155)
at java.awt.LightweightDispatcher.dispatchEvent(Conta iner.java:2135)
at java.awt.Container.dispatchEventImpl(Container.jav a:1200)
at java.awt.Window.dispatchEventImpl(Window.java:914)
at java.awt.Component.dispatchEvent(Component.java:24 97)
at java.awt.EventQueue.dispatchEvent(EventQueue.java: 339)
at
java.awt.EventDispatchThread.pumpOneEventForHierar chy
(EventDispatchThread.java:131)
at
java.awt.EventDispatchThread.pumpEventsForHierarch y
(EventDispatchThread.java:98)
at java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThre ad.java:85)
Thanks very much!
Take a look at the firewall configuration. XP SP2 comes with a firewall
that blocks all ports by default.
Alin,
The jTDS Project.
|||thanks .
the firewall want's one exeption for sql server
database
SQL Server 2000 with JDBC driver.
when i have Windows Xp Pro with service Pack 1
i don't have a problem
all works
now with Windows Xp Pro and service Pack 2 i have this problem
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
See com.borland.dx.dataset.DataSetException error code: BASE+66
com.borland.dx.dataset.DataSetException: [Microsoft][SQLServer 2000 Driver
for JDBC]Error establishing socket.
at com.borland.dx.dataset.DataSetException.a(Unknown Source)
at com.borland.dx.dataset.DataSetException.throwExcep tion(Unknown Source)
at com.borland.dx.dataset.DataSetException.SQLExcepti on(Unknown Source)
at com.borland.dx.sql.dataset.Database.openConnection (Unknown Source)
at com.borland.dx.sql.dataset.Database.createPrepared Statement(Unknown
Source)
at com.borland.dx.sql.dataset.o.a(Unknown Source)
at com.borland.dx.sql.dataset.o.d(Unknown Source)
at com.borland.dx.sql.dataset.o.f(Unknown Source)
at com.borland.dx.sql.dataset.QueryProvider.e(Unknown Source)
at com.borland.dx.sql.dataset.JdbcProvider.provideDat a(Unknown Source)
at com.borland.dx.dataset.StorageDataSet.refresh(Unkn own Source)
at com.borland.dx.sql.dataset.QueryDataSet.refresh(Un known Source)
at com.borland.dx.sql.dataset.QueryDataSet.executeQue ry(Unknown Source)
at prog.Login.buttonControl1_actionPerformed(Login.ja va:131)
at prog.Login.buttonControl1_keyReleased(Login.java:2 07)
at prog.Login$2.keyReleased(Login.java:79)
at java.awt.AWTEventMulticaster.keyReleased(AWTEventM ulticaster.java:201)
at java.awt.Component.processKeyEvent(Component.java: 3680)
at javax.swing.JComponent.processKeyEvent(JComponent. java:2014)
at java.awt.Component.processEvent(Component.java:355 3)
at java.awt.Container.processEvent(Container.java:116 4)
at java.awt.Component.dispatchEventImpl(Component.jav a:2593)
at java.awt.Container.dispatchEventImpl(Container.jav a:1213)
at java.awt.Component.dispatchEvent(Component.java:24 97)
at java.awt.LightweightDispatcher.processKeyEvent(Con tainer.java:2155)
at java.awt.LightweightDispatcher.dispatchEvent(Conta iner.java:2135)
at java.awt.Container.dispatchEventImpl(Container.jav a:1200)
at java.awt.Window.dispatchEventImpl(Window.java:914)
at java.awt.Component.dispatchEvent(Component.java:24 97)
at java.awt.EventQueue.dispatchEvent(EventQueue.java: 339)
at
java.awt.EventDispatchThread.pumpOneEventForHierar chy
(EventDispatchThread.java:131)
at
java.awt.EventDispatchThread.pumpEventsForHierarch y
(EventDispatchThread.java:98)
at java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThre ad.java:85)
Chained exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error
establishing socket.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSConnection.<in it>(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplConnecti on.open(Unknown
Source)
at com.microsoft.jdbc.base.BaseConnection.getNewImplC onnection(Unknown
Source)
at com.microsoft.jdbc.base.BaseConnection.open(Unknow n Source)
at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager .java:517)
at java.sql.DriverManager.getConnection(DriverManager .java:177)
at com.borland.dx.sql.dataset.Database.openConnection (Unknown Source)
at com.borland.dx.sql.dataset.Database.createPrepared Statement(Unknown
Source)
at com.borland.dx.sql.dataset.o.a(Unknown Source)
at com.borland.dx.sql.dataset.o.d(Unknown Source)
at com.borland.dx.sql.dataset.o.f(Unknown Source)
at com.borland.dx.sql.dataset.QueryProvider.e(Unknown Source)
at com.borland.dx.sql.dataset.JdbcProvider.provideDat a(Unknown Source)
at com.borland.dx.dataset.StorageDataSet.refresh(Unkn own Source)
at com.borland.dx.sql.dataset.QueryDataSet.refresh(Un known Source)
at com.borland.dx.sql.dataset.QueryDataSet.executeQue ry(Unknown Source)
at prog.Login.buttonControl1_actionPerformed(Login.ja va:131)
at prog.Login.buttonControl1_keyReleased(Login.java:2 07)
at prog.Login$2.keyReleased(Login.java:79)
at java.awt.AWTEventMulticaster.keyReleased(AWTEventM ulticaster.java:201)
at java.awt.Component.processKeyEvent(Component.java: 3680)
at javax.swing.JComponent.processKeyEvent(JComponent. java:2014)
at java.awt.Component.processEvent(Component.java:355 3)
at java.awt.Container.processEvent(Container.java:116 4)
at java.awt.Component.dispatchEventImpl(Component.jav a:2593)
at java.awt.Container.dispatchEventImpl(Container.jav a:1213)
at java.awt.Component.dispatchEvent(Component.java:24 97)
at java.awt.LightweightDispatcher.processKeyEvent(Con tainer.java:2155)
at java.awt.LightweightDispatcher.dispatchEvent(Conta iner.java:2135)
at java.awt.Container.dispatchEventImpl(Container.jav a:1200)
at java.awt.Window.dispatchEventImpl(Window.java:914)
at java.awt.Component.dispatchEvent(Component.java:24 97)
at java.awt.EventQueue.dispatchEvent(EventQueue.java: 339)
at
java.awt.EventDispatchThread.pumpOneEventForHierar chy
(EventDispatchThread.java:131)
at
java.awt.EventDispatchThread.pumpEventsForHierarch y
(EventDispatchThread.java:98)
at java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThre ad.java:85)
ava.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error
establishing socket.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSConnection.<in it>(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplConnecti on.open(Unknown
Source)
at com.microsoft.jdbc.base.BaseConnection.getNewImplC onnection(Unknown
Source)
at com.microsoft.jdbc.base.BaseConnection.open(Unknow n Source)
at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager .java:517)
at java.sql.DriverManager.getConnection(DriverManager .java:177)
at com.borland.dx.sql.dataset.Database.openConnection (Unknown Source)
at com.borland.dx.sql.dataset.Database.createPrepared Statement(Unknown
Source)
at com.borland.dx.sql.dataset.o.a(Unknown Source)
at com.borland.dx.sql.dataset.o.d(Unknown Source)
at com.borland.dx.sql.dataset.o.f(Unknown Source)
at com.borland.dx.sql.dataset.QueryProvider.e(Unknown Source)
at com.borland.dx.sql.dataset.JdbcProvider.provideDat a(Unknown Source)
at com.borland.dx.dataset.StorageDataSet.refresh(Unkn own Source)
at com.borland.dx.sql.dataset.QueryDataSet.refresh(Un known Source)
at com.borland.dx.sql.dataset.QueryDataSet.executeQue ry(Unknown Source)
at prog.Login.buttonControl1_actionPerformed(Login.ja va:131)
at prog.Login.buttonControl1_keyReleased(Login.java:2 07)
at prog.Login$2.keyReleased(Login.java:79)
at java.awt.AWTEventMulticaster.keyReleased(AWTEventM ulticaster.java:201)
at java.awt.Component.processKeyEvent(Component.java: 3680)
at javax.swing.JComponent.processKeyEvent(JComponent. java:2014)
at java.awt.Component.processEvent(Component.java:355 3)
at java.awt.Container.processEvent(Container.java:116 4)
at java.awt.Component.dispatchEventImpl(Component.jav a:2593)
at java.awt.Container.dispatchEventImpl(Container.jav a:1213)
at java.awt.Component.dispatchEvent(Component.java:24 97)
at java.awt.LightweightDispatcher.processKeyEvent(Con tainer.java:2155)
at java.awt.LightweightDispatcher.dispatchEvent(Conta iner.java:2135)
at java.awt.Container.dispatchEventImpl(Container.jav a:1200)
at java.awt.Window.dispatchEventImpl(Window.java:914)
at java.awt.Component.dispatchEvent(Component.java:24 97)
at java.awt.EventQueue.dispatchEvent(EventQueue.java: 339)
at
java.awt.EventDispatchThread.pumpOneEventForHierar chy
(EventDispatchThread.java:131)
at
java.awt.EventDispatchThread.pumpEventsForHierarch y
(EventDispatchThread.java:98)
at java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThre ad.java:85)
Thanks very much!
Take a look at the firewall configuration. XP SP2 comes with a firewall
that blocks all ports by default.
Alin,
The jTDS Project.
|||thanks .
the firewall want's one exeption for sql server
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID X) was deadloc
My company recently upgraded from SQL 7 to SQL 2000. We also updated
our application servers from Windows NT to Windows 2003. Now when we
get the error [Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID X) was deadlocked, the part that comes
next "on {unprintable" shows up as unprintable characters. The rest of
the error message seems fine. Only the part between the braces is
garbage. Does anyone have any suggestions as to why this is? It
seems to be happening from both C++ and Java apps. We are using ODBC
with the C++ apps and JDBC with the Java apps.
Thanks.
-Jeff-Hi
You may want to run SQL profiler to see what is being sent to the server,
and look at the query plans to see if you are missing indexes or statistics.
You may need to change the order of the SQL in your code/stored procedures
and if you have carried over any hints from the SQL 7 upgrade you may want
to evaluate if they are still necessary.
For ways ways to find blocking check out
http://support.microsoft.com/kb/271509/EN-US/ and general tips on
http://www.sql-server-performance.com/
John
"kludge" <jeff.schuler@.53.com> wrote in message
news:1130515027.782023.128220@.g43g2000cwa.googlegroups.com...
> My company recently upgraded from SQL 7 to SQL 2000. We also updated
> our application servers from Windows NT to Windows 2003. Now when we
> get the error [Microsoft][ODBC SQL Server Driver][SQL
> Server]Transaction (Process ID X) was deadlocked, the part that comes
> next "on {unprintable" shows up as unprintable characters. The rest of
> the error message seems fine. Only the part between the braces is
> garbage. Does anyone have any suggestions as to why this is? It
> seems to be happening from both C++ and Java apps. We are using ODBC
> with the C++ apps and JDBC with the Java apps.
> Thanks.
> -Jeff-
>
our application servers from Windows NT to Windows 2003. Now when we
get the error [Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID X) was deadlocked, the part that comes
next "on {unprintable" shows up as unprintable characters. The rest of
the error message seems fine. Only the part between the braces is
garbage. Does anyone have any suggestions as to why this is? It
seems to be happening from both C++ and Java apps. We are using ODBC
with the C++ apps and JDBC with the Java apps.
Thanks.
-Jeff-Hi
You may want to run SQL profiler to see what is being sent to the server,
and look at the query plans to see if you are missing indexes or statistics.
You may need to change the order of the SQL in your code/stored procedures
and if you have carried over any hints from the SQL 7 upgrade you may want
to evaluate if they are still necessary.
For ways ways to find blocking check out
http://support.microsoft.com/kb/271509/EN-US/ and general tips on
http://www.sql-server-performance.com/
John
"kludge" <jeff.schuler@.53.com> wrote in message
news:1130515027.782023.128220@.g43g2000cwa.googlegroups.com...
> My company recently upgraded from SQL 7 to SQL 2000. We also updated
> our application servers from Windows NT to Windows 2003. Now when we
> get the error [Microsoft][ODBC SQL Server Driver][SQL
> Server]Transaction (Process ID X) was deadlocked, the part that comes
> next "on {unprintable" shows up as unprintable characters. The rest of
> the error message seems fine. Only the part between the braces is
> garbage. Does anyone have any suggestions as to why this is? It
> seems to be happening from both C++ and Java apps. We are using ODBC
> with the C++ apps and JDBC with the Java apps.
> Thanks.
> -Jeff-
>
Saturday, February 25, 2012
[Microsoft][ODBC SQL Server Driver][Named Pipes] ConnectionWrite (WrapperWrite())
Hi,
I got the error message "[Microsoft][ODBC SQL Server Driver][Named Pipes] ConnectionWrite (WrapperWrite())" from my VB6 application that connects to SQL Server 2000.
How to fix this?
Can you post the full error. The posted message is too generic.http://groups.google.com/groups/search?q=ConnectionWrite+%28WrapperWrite%28%29&qt_s=Search
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error
We have built an application (VB 6 with a SQL 2000 backend database)
in-house and from time to time, we get the dbnetlib error message. This erro
r
message seems to happen when the machine is just idle and is driving me nuts
.
The error message doesnt seem to have the issue while the user is actively
using the application. Could this problem have something to do with
connection pooling? If anyone has any ideas, it would be much appreciated.
Thanks in advance!
Mike HartmanWe have seen this from time-to-time. It seems that the connection "ages out"
after a long period of dormancy. The solution I use is to occasionally poll
the server to make sure it knows that the application is still alive. I
don't think it's a connection pooling issue. It looks like the transport
layer is being closed at the server end (in my case).
USE <initial catalog>
is enough to do the job.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:772A11BE-EF7D-4088-8654-9C29D15F1FCC@.microsoft.com...
> We have built an application (VB 6 with a SQL 2000 backend database)
> in-house and from time to time, we get the dbnetlib error message. This
> error
> message seems to happen when the machine is just idle and is driving me
> nuts.
> The error message doesnt seem to have the issue while the user is actively
> using the application. Could this problem have something to do with
> connection pooling? If anyone has any ideas, it would be much appreciated.
> Thanks in advance!
> Mike Hartman
in-house and from time to time, we get the dbnetlib error message. This erro
r
message seems to happen when the machine is just idle and is driving me nuts
.
The error message doesnt seem to have the issue while the user is actively
using the application. Could this problem have something to do with
connection pooling? If anyone has any ideas, it would be much appreciated.
Thanks in advance!
Mike HartmanWe have seen this from time-to-time. It seems that the connection "ages out"
after a long period of dormancy. The solution I use is to occasionally poll
the server to make sure it knows that the application is still alive. I
don't think it's a connection pooling issue. It looks like the transport
layer is being closed at the server end (in my case).
USE <initial catalog>
is enough to do the job.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:772A11BE-EF7D-4088-8654-9C29D15F1FCC@.microsoft.com...
> We have built an application (VB 6 with a SQL 2000 backend database)
> in-house and from time to time, we get the dbnetlib error message. This
> error
> message seems to happen when the machine is just idle and is driving me
> nuts.
> The error message doesnt seem to have the issue while the user is actively
> using the application. Could this problem have something to do with
> connection pooling? If anyone has any ideas, it would be much appreciated.
> Thanks in advance!
> Mike Hartman
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error
We have built an application (VB 6 with a SQL 2000 backend database)
in-house and from time to time, we get the dbnetlib error message. This error
message seems to happen when the machine is just idle and is driving me nuts.
The error message doesnt seem to have the issue while the user is actively
using the application. Could this problem have something to do with
connection pooling? If anyone has any ideas, it would be much appreciated.
Thanks in advance!
Mike Hartman
We have seen this from time-to-time. It seems that the connection "ages out"
after a long period of dormancy. The solution I use is to occasionally poll
the server to make sure it knows that the application is still alive. I
don't think it's a connection pooling issue. It looks like the transport
layer is being closed at the server end (in my case).
USE <initial catalog>
is enough to do the job.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:772A11BE-EF7D-4088-8654-9C29D15F1FCC@.microsoft.com...
> We have built an application (VB 6 with a SQL 2000 backend database)
> in-house and from time to time, we get the dbnetlib error message. This
> error
> message seems to happen when the machine is just idle and is driving me
> nuts.
> The error message doesnt seem to have the issue while the user is actively
> using the application. Could this problem have something to do with
> connection pooling? If anyone has any ideas, it would be much appreciated.
> Thanks in advance!
> Mike Hartman
in-house and from time to time, we get the dbnetlib error message. This error
message seems to happen when the machine is just idle and is driving me nuts.
The error message doesnt seem to have the issue while the user is actively
using the application. Could this problem have something to do with
connection pooling? If anyone has any ideas, it would be much appreciated.
Thanks in advance!
Mike Hartman
We have seen this from time-to-time. It seems that the connection "ages out"
after a long period of dormancy. The solution I use is to occasionally poll
the server to make sure it knows that the application is still alive. I
don't think it's a connection pooling issue. It looks like the transport
layer is being closed at the server end (in my case).
USE <initial catalog>
is enough to do the job.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:772A11BE-EF7D-4088-8654-9C29D15F1FCC@.microsoft.com...
> We have built an application (VB 6 with a SQL 2000 backend database)
> in-house and from time to time, we get the dbnetlib error message. This
> error
> message seems to happen when the machine is just idle and is driving me
> nuts.
> The error message doesnt seem to have the issue while the user is actively
> using the application. Could this problem have something to do with
> connection pooling? If anyone has any ideas, it would be much appreciated.
> Thanks in advance!
> Mike Hartman
[Microsoft][ODBC SQL Server Driver] Invalid Descriptor Index
Hi,
I am using MFC ODBC classes in my VC++ application for database accesss (on
a SQL Server/Access Database).
Some times after opening a Recordset (containing bound columns generated
using the ClassWizard) I have to use the
CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short
nFieldType = DEFAULT_FIELD_TYPE )
method of the CRecordset object to obtain field values. The above method
works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it
throws the following exception
State:S1002,Native:0,Origin:[Microsoft][
ODBC SQL Server Driver] Invalid
Descriptor Index
I don't understand why the Microsoft SQL Server ODBC Driver is throwing this
exception.
Please help me!
SameerHi Sameer,
Thanks for posting in the community.
From your description, I understand that when you use the CRecordset::GetFie
ldValue
method t oobtain the field values after opening a binding columns recordset,
you will
receive the exception of Invalid Descriptor Index for ODBC SQL Server Driver
. However, the
problem doesn't occur with ODBC drivers for Jet.
Before we start, please notice that the issue is related to MFC programming
which is
gracefully handling in this queue. However, it's my pleasure to let me see i
f I can address
your problem and give some solutions. For quickly and efficiently solve your
problem, it's
better to post this issue in the corresponding queues such as ADO/VC/VC++ ne
wsgroup.
I'm not 100% sure why the exception was genereted when using ODBC SQL Server
Drivers.
Based on my research, per the ODBC docs SQLGetData throws that error if it's
called and
all the columns are already bound when it is generated using the ClassWizard
.
So the bottom line is that if you're using a CRecordset that was created wit
h
the AppWizard, don't use GetFieldValue. Instead, go to the record that you
want and then look at the data member of the recordset to get the field valu
e.
Please apply my suggestions above and let me know if this helps resolve your
problem. If
there is anything more I can do to assist you, please feel free to post it i
n the group.
If the problem still persists and you'd like to get the most qualified pool
of respondents,
please repost this issue to the corresponding queues I mentioned above. Addi
tionally,
other partners who read the newsgroups regularly can either share their know
ledge or learn
from your interaction with us. I hope the problem can be resolved quickly.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
I am using MFC ODBC classes in my VC++ application for database accesss (on
a SQL Server/Access Database).
Some times after opening a Recordset (containing bound columns generated
using the ClassWizard) I have to use the
CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short
nFieldType = DEFAULT_FIELD_TYPE )
method of the CRecordset object to obtain field values. The above method
works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it
throws the following exception
State:S1002,Native:0,Origin:[Microsoft][
ODBC SQL Server Driver] Invalid
Descriptor Index
I don't understand why the Microsoft SQL Server ODBC Driver is throwing this
exception.
Please help me!
SameerHi Sameer,
Thanks for posting in the community.
From your description, I understand that when you use the CRecordset::GetFie
ldValue
method t oobtain the field values after opening a binding columns recordset,
you will
receive the exception of Invalid Descriptor Index for ODBC SQL Server Driver
. However, the
problem doesn't occur with ODBC drivers for Jet.
Before we start, please notice that the issue is related to MFC programming
which is
gracefully handling in this queue. However, it's my pleasure to let me see i
f I can address
your problem and give some solutions. For quickly and efficiently solve your
problem, it's
better to post this issue in the corresponding queues such as ADO/VC/VC++ ne
wsgroup.
I'm not 100% sure why the exception was genereted when using ODBC SQL Server
Drivers.
Based on my research, per the ODBC docs SQLGetData throws that error if it's
called and
all the columns are already bound when it is generated using the ClassWizard
.
So the bottom line is that if you're using a CRecordset that was created wit
h
the AppWizard, don't use GetFieldValue. Instead, go to the record that you
want and then look at the data member of the recordset to get the field valu
e.
Please apply my suggestions above and let me know if this helps resolve your
problem. If
there is anything more I can do to assist you, please feel free to post it i
n the group.
If the problem still persists and you'd like to get the most qualified pool
of respondents,
please repost this issue to the corresponding queues I mentioned above. Addi
tionally,
other partners who read the newsgroups regularly can either share their know
ledge or learn
from your interaction with us. I hope the problem can be resolved quickly.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
[Microsoft][ ODBC SQL Server Drive ] Timeout expired
I am running a VB application which makes connection to the SQL server
database using DSN connection using ODBC. This application pulls data using
stored procedure. The application does not insert / delete / update data.
The application was working fine since it has been installed (more than ONE
year). Suddendly it has started generating runtime error '-2147217871
(80040e31)'. I have taken the following actions so far to identify the
problem:
1. Checked user permission set on the database. Its fine. ODBC test
connection is successful.
2. Debugged VB source. It successfully makes connection to the database but
generated error while executing Command object which is using a stored
procedure which pulls the data into the grid.
3. Changed the timeout property of the Command object to 60 seconds. Still
generating error.
4. Copy pasted Query from stored procedure into Query Analyzer. The query
ran within 10 seconds generating desired resultset. Hence, Table index /
fragmentation problem is ruled out.
5. Copy data and Object from Live database into another database on the same
server using DTS wizard. Changed database name in the ODBC configuration
pointing it to the new database having same data & objects (including users &
permissions). The application ran succussefully without generating any
error. Hence database server problem is ruled out.
6. The stored procedure used by the command object in VB code fails to
execute.
Any help on how to fix this problem?
Manish Acharya
Systems Analyst & database developer
Task Transactions Ltd., New Zealand.
Have you considered taking ODBC or SQL Server Profiler traces? These might
give more clues.
"Manish Acharya" <Manish Acharya@.discussions.microsoft.com> wrote in message
news:B4ACB30B-2D2B-4473-A802-6CB2E145BD82@.microsoft.com...
>I am running a VB application which makes connection to the SQL server
> database using DSN connection using ODBC. This application pulls data
> using
> stored procedure. The application does not insert / delete / update data.
> The application was working fine since it has been installed (more than
> ONE
> year). Suddendly it has started generating runtime error '-2147217871
> (80040e31)'. I have taken the following actions so far to identify the
> problem:
> 1. Checked user permission set on the database. Its fine. ODBC test
> connection is successful.
> 2. Debugged VB source. It successfully makes connection to the database
> but
> generated error while executing Command object which is using a stored
> procedure which pulls the data into the grid.
> 3. Changed the timeout property of the Command object to 60 seconds.
> Still
> generating error.
> 4. Copy pasted Query from stored procedure into Query Analyzer. The query
> ran within 10 seconds generating desired resultset. Hence, Table index /
> fragmentation problem is ruled out.
> 5. Copy data and Object from Live database into another database on the
> same
> server using DTS wizard. Changed database name in the ODBC configuration
> pointing it to the new database having same data & objects (including
> users &
> permissions). The application ran succussefully without generating any
> error. Hence database server problem is ruled out.
> 6. The stored procedure used by the command object in VB code fails to
> execute.
> Any help on how to fix this problem?
> Manish Acharya
> Systems Analyst & database developer
> Task Transactions Ltd., New Zealand.
|||Thanks Chris for your response. I have been working on SQL server profiler
traces to identify this problem. My experience on using SQL server profiler
is a bit limited and I see this as an opportunity to hone my skills in this
area. Your reply confirms that I am on the right direction. I will post the
solution in this discussion group once I crack it.
Cheers.
Manish Acharya.
"Chris Lee[MS]" wrote:
> Have you considered taking ODBC or SQL Server Profiler traces? These might
> give more clues.
> "Manish Acharya" <Manish Acharya@.discussions.microsoft.com> wrote in message
> news:B4ACB30B-2D2B-4473-A802-6CB2E145BD82@.microsoft.com...
>
>
|||Hi Manish Acharya,
Had you find the solution for the problem above? I have the same problem
like you, so hope you can shared the solution with us.
Thank you.
database using DSN connection using ODBC. This application pulls data using
stored procedure. The application does not insert / delete / update data.
The application was working fine since it has been installed (more than ONE
year). Suddendly it has started generating runtime error '-2147217871
(80040e31)'. I have taken the following actions so far to identify the
problem:
1. Checked user permission set on the database. Its fine. ODBC test
connection is successful.
2. Debugged VB source. It successfully makes connection to the database but
generated error while executing Command object which is using a stored
procedure which pulls the data into the grid.
3. Changed the timeout property of the Command object to 60 seconds. Still
generating error.
4. Copy pasted Query from stored procedure into Query Analyzer. The query
ran within 10 seconds generating desired resultset. Hence, Table index /
fragmentation problem is ruled out.
5. Copy data and Object from Live database into another database on the same
server using DTS wizard. Changed database name in the ODBC configuration
pointing it to the new database having same data & objects (including users &
permissions). The application ran succussefully without generating any
error. Hence database server problem is ruled out.
6. The stored procedure used by the command object in VB code fails to
execute.
Any help on how to fix this problem?
Manish Acharya
Systems Analyst & database developer
Task Transactions Ltd., New Zealand.
Have you considered taking ODBC or SQL Server Profiler traces? These might
give more clues.
"Manish Acharya" <Manish Acharya@.discussions.microsoft.com> wrote in message
news:B4ACB30B-2D2B-4473-A802-6CB2E145BD82@.microsoft.com...
>I am running a VB application which makes connection to the SQL server
> database using DSN connection using ODBC. This application pulls data
> using
> stored procedure. The application does not insert / delete / update data.
> The application was working fine since it has been installed (more than
> ONE
> year). Suddendly it has started generating runtime error '-2147217871
> (80040e31)'. I have taken the following actions so far to identify the
> problem:
> 1. Checked user permission set on the database. Its fine. ODBC test
> connection is successful.
> 2. Debugged VB source. It successfully makes connection to the database
> but
> generated error while executing Command object which is using a stored
> procedure which pulls the data into the grid.
> 3. Changed the timeout property of the Command object to 60 seconds.
> Still
> generating error.
> 4. Copy pasted Query from stored procedure into Query Analyzer. The query
> ran within 10 seconds generating desired resultset. Hence, Table index /
> fragmentation problem is ruled out.
> 5. Copy data and Object from Live database into another database on the
> same
> server using DTS wizard. Changed database name in the ODBC configuration
> pointing it to the new database having same data & objects (including
> users &
> permissions). The application ran succussefully without generating any
> error. Hence database server problem is ruled out.
> 6. The stored procedure used by the command object in VB code fails to
> execute.
> Any help on how to fix this problem?
> Manish Acharya
> Systems Analyst & database developer
> Task Transactions Ltd., New Zealand.
|||Thanks Chris for your response. I have been working on SQL server profiler
traces to identify this problem. My experience on using SQL server profiler
is a bit limited and I see this as an opportunity to hone my skills in this
area. Your reply confirms that I am on the right direction. I will post the
solution in this discussion group once I crack it.
Cheers.
Manish Acharya.
"Chris Lee[MS]" wrote:
> Have you considered taking ODBC or SQL Server Profiler traces? These might
> give more clues.
> "Manish Acharya" <Manish Acharya@.discussions.microsoft.com> wrote in message
> news:B4ACB30B-2D2B-4473-A802-6CB2E145BD82@.microsoft.com...
>
>
|||Hi Manish Acharya,
Had you find the solution for the problem above? I have the same problem
like you, so hope you can shared the solution with us.
Thank you.
[Microsoft][ ODBC SQL Server Drive ] Timeout expired
I am running a VB application which makes connection to the SQL server
database using DSN connection using ODBC. This application pulls data using
stored procedure. The application does not insert / delete / update data.
The application was working fine since it has been installed (more than ONE
year). Suddendly it has started generating runtime error '-2147217871
(80040e31)'. I have taken the following actions so far to identify the
problem:
1. Checked user permission set on the database. Its fine. ODBC test
connection is successful.
2. Debugged VB source. It successfully makes connection to the database but
generated error while executing Command object which is using a stored
procedure which pulls the data into the grid.
3. Changed the timeout property of the Command object to 60 seconds. Still
generating error.
4. Copy pasted Query from stored procedure into Query Analyzer. The query
ran within 10 seconds generating desired resultset. Hence, Table index /
fragmentation problem is ruled out.
5. Copy data and Object from Live database into another database on the same
server using DTS wizard. Changed database name in the ODBC configuration
pointing it to the new database having same data & objects (including users
&
permissions). The application ran succussefully without generating any
error. Hence database server problem is ruled out.
6. The stored procedure used by the command object in VB code fails to
execute.
Any help on how to fix this problem?
Manish Acharya
Systems Analyst & database developer
Task Transactions Ltd., New Zealand.Have you considered taking ODBC or SQL Server Profiler traces? These might
give more clues.
"Manish Acharya" <Manish Acharya@.discussions.microsoft.com> wrote in message
news:B4ACB30B-2D2B-4473-A802-6CB2E145BD82@.microsoft.com...
>I am running a VB application which makes connection to the SQL server
> database using DSN connection using ODBC. This application pulls data
> using
> stored procedure. The application does not insert / delete / update data.
> The application was working fine since it has been installed (more than
> ONE
> year). Suddendly it has started generating runtime error '-2147217871
> (80040e31)'. I have taken the following actions so far to identify the
> problem:
> 1. Checked user permission set on the database. Its fine. ODBC test
> connection is successful.
> 2. Debugged VB source. It successfully makes connection to the database
> but
> generated error while executing Command object which is using a stored
> procedure which pulls the data into the grid.
> 3. Changed the timeout property of the Command object to 60 seconds.
> Still
> generating error.
> 4. Copy pasted Query from stored procedure into Query Analyzer. The query
> ran within 10 seconds generating desired resultset. Hence, Table index /
> fragmentation problem is ruled out.
> 5. Copy data and Object from Live database into another database on the
> same
> server using DTS wizard. Changed database name in the ODBC configuration
> pointing it to the new database having same data & objects (including
> users &
> permissions). The application ran succussefully without generating any
> error. Hence database server problem is ruled out.
> 6. The stored procedure used by the command object in VB code fails to
> execute.
> Any help on how to fix this problem?
> Manish Acharya
> Systems Analyst & database developer
> Task Transactions Ltd., New Zealand.|||Thanks Chris for your response. I have been working on SQL server profiler
traces to identify this problem. My experience on using SQL server profiler
is a bit limited and I see this as an opportunity to hone my skills in this
area. Your reply confirms that I am on the right direction. I will post th
e
solution in this discussion group once I crack it.
Cheers.
Manish Acharya.
"Chris Lee[MS]" wrote:
> Have you considered taking ODBC or SQL Server Profiler traces? These might
> give more clues.
> "Manish Acharya" <Manish Acharya@.discussions.microsoft.com> wrote in messa
ge
> news:B4ACB30B-2D2B-4473-A802-6CB2E145BD82@.microsoft.com...
>
>|||Hi Manish Acharya,
Had you find the solution for the problem above? I have the same problem
like you, so hope you can shared the solution with us.
Thank you.
database using DSN connection using ODBC. This application pulls data using
stored procedure. The application does not insert / delete / update data.
The application was working fine since it has been installed (more than ONE
year). Suddendly it has started generating runtime error '-2147217871
(80040e31)'. I have taken the following actions so far to identify the
problem:
1. Checked user permission set on the database. Its fine. ODBC test
connection is successful.
2. Debugged VB source. It successfully makes connection to the database but
generated error while executing Command object which is using a stored
procedure which pulls the data into the grid.
3. Changed the timeout property of the Command object to 60 seconds. Still
generating error.
4. Copy pasted Query from stored procedure into Query Analyzer. The query
ran within 10 seconds generating desired resultset. Hence, Table index /
fragmentation problem is ruled out.
5. Copy data and Object from Live database into another database on the same
server using DTS wizard. Changed database name in the ODBC configuration
pointing it to the new database having same data & objects (including users
&
permissions). The application ran succussefully without generating any
error. Hence database server problem is ruled out.
6. The stored procedure used by the command object in VB code fails to
execute.
Any help on how to fix this problem?
Manish Acharya
Systems Analyst & database developer
Task Transactions Ltd., New Zealand.Have you considered taking ODBC or SQL Server Profiler traces? These might
give more clues.
"Manish Acharya" <Manish Acharya@.discussions.microsoft.com> wrote in message
news:B4ACB30B-2D2B-4473-A802-6CB2E145BD82@.microsoft.com...
>I am running a VB application which makes connection to the SQL server
> database using DSN connection using ODBC. This application pulls data
> using
> stored procedure. The application does not insert / delete / update data.
> The application was working fine since it has been installed (more than
> ONE
> year). Suddendly it has started generating runtime error '-2147217871
> (80040e31)'. I have taken the following actions so far to identify the
> problem:
> 1. Checked user permission set on the database. Its fine. ODBC test
> connection is successful.
> 2. Debugged VB source. It successfully makes connection to the database
> but
> generated error while executing Command object which is using a stored
> procedure which pulls the data into the grid.
> 3. Changed the timeout property of the Command object to 60 seconds.
> Still
> generating error.
> 4. Copy pasted Query from stored procedure into Query Analyzer. The query
> ran within 10 seconds generating desired resultset. Hence, Table index /
> fragmentation problem is ruled out.
> 5. Copy data and Object from Live database into another database on the
> same
> server using DTS wizard. Changed database name in the ODBC configuration
> pointing it to the new database having same data & objects (including
> users &
> permissions). The application ran succussefully without generating any
> error. Hence database server problem is ruled out.
> 6. The stored procedure used by the command object in VB code fails to
> execute.
> Any help on how to fix this problem?
> Manish Acharya
> Systems Analyst & database developer
> Task Transactions Ltd., New Zealand.|||Thanks Chris for your response. I have been working on SQL server profiler
traces to identify this problem. My experience on using SQL server profiler
is a bit limited and I see this as an opportunity to hone my skills in this
area. Your reply confirms that I am on the right direction. I will post th
e
solution in this discussion group once I crack it.
Cheers.
Manish Acharya.
"Chris Lee[MS]" wrote:
> Have you considered taking ODBC or SQL Server Profiler traces? These might
> give more clues.
> "Manish Acharya" <Manish Acharya@.discussions.microsoft.com> wrote in messa
ge
> news:B4ACB30B-2D2B-4473-A802-6CB2E145BD82@.microsoft.com...
>
>|||Hi Manish Acharya,
Had you find the solution for the problem above? I have the same problem
like you, so hope you can shared the solution with us.
Thank you.
Sunday, February 19, 2012
[DBNETLIB][ConnectionWrite (send()).] errors
We have a Dell 2950 running Windows 2003 connecting to SQL2005 Database.
Users accessing their SQL application are getting this error message after leaving the application open for a period when they are not accessing the system. We have run the application on two different servers and cannot re-create the errors, which might suggest a hardware problem with our server, but as yet we haven't identified the problem.
Does anyone have any ideas ?
I'm having similar issues with my system (new HP server running Win 2003 & SQL 2005 with VB App running on XP Clients, MDAC 2.81). Seems to affect certain PC's, doesn't affect a version of the VB 6 App running locally on the server, or one of the Clients that continually polls the server once per minute, 24/7.
The error handling in the VB App actually writes a log of the error to a SQL Table, which suggests that the connection error occurs, and by the time the user confirms the msgbox prompt the connection comes back up, allowing the log to be written...
I've tried disabling connection pooling in the ADO connection string & the SynAttackProtect REG setting change as discussed at http://www.eggheadcafe.com/ng/microsoft.public.sqlserver.connect/post22689813.asp
Both to no avail.... 
Was wondering if you'd found a resolution yet?
Best regards
Keith
|||
For your issue, I would try to follow the article http://support.microsoft.com/default.aspx?scid=kb;en-us;899599 to add the registry key. Maybe that will fix your problem. If not, please provide more information on the infrastructure to help us narrow the issue.
HTH
|||umm - our problem appeared to hardware - Dell announced urgent driver updates for the cards in our server, but that didn't entirley solve the problem although it is better. The the support desk for hte application we are using then recomended switching the server to use both TCP/IP and Named Pipes and then adjusting the client config appropriatly. this seems to have resolved our issue
[DBNETLIB][ConnectionWrite (send()).] errors
We have a Dell 2950 running Windows 2003 connecting to SQL2005 Database.
Users accessing their SQL application are getting this error message after leaving the application open for a period when they are not accessing the system. We have run the application on two different servers and cannot re-create the errors, which might suggest a hardware problem with our server, but as yet we haven't identified the problem.
Does anyone have any ideas ?
I'm having similar issues with my system (new HP server running Win 2003 & SQL 2005 with VB App running on XP Clients, MDAC 2.81). Seems to affect certain PC's, doesn't affect a version of the VB 6 App running locally on the server, or one of the Clients that continually polls the server once per minute, 24/7.
The error handling in the VB App actually writes a log of the error to a SQL Table, which suggests that the connection error occurs, and by the time the user confirms the msgbox prompt the connection comes back up, allowing the log to be written...
I've tried disabling connection pooling in the ADO connection string & the SynAttackProtect REG setting change as discussed at http://www.eggheadcafe.com/ng/microsoft.public.sqlserver.connect/post22689813.asp
Both to no avail....
Was wondering if you'd found a resolution yet?
Best regards
Keith
|||
For your issue, I would try to follow the article http://support.microsoft.com/default.aspx?scid=kb;en-us;899599 to add the registry key. Maybe that will fix your problem. If not, please provide more information on the infrastructure to help us narrow the issue.
HTH
|||umm - our problem appeared to hardware - Dell announced urgent driver updates for the cards in our server, but that didn't entirley solve the problem although it is better. The the support desk for hte application we are using then recomended switching the server to use both TCP/IP and Named Pipes and then adjusting the client config appropriatly. this seems to have resolved our issue
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error.
Hi,
I have a classic ASP application that has been running on Win2003,IIS6 and SQL2000 for 3 years without a single error.
We've just moved to SQL2005 enterprise and we're getting the following intermittent error.
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
Any ideas where to start?
this is a common connection problem and without details like your current sql server configuration and connection string, we just can write our guesses,
first, if you have not installed latest service pack for sql serer 2005, please install it before trying the following suggestions,
second, you can check if you have enabled remote connections on sql server, to check this setting go to sql server surface area configuration -> surface area configuration for services and connections -> remote connections and enable remote connections by selection "local and remote connections" and using tcp/ip only.
if the previous step did not solve your problem, you can check your connection string, please examine the following connection strings,
for odbc connections:
- Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
- Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
for ole db connections
- Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
- Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
for .net connections
- Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
or
- Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;
- Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
or
- Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
also if you are connecting with an sql server account, please check if you have set authentication mode of sql server to mixed mode.
and finally if your sql server is on a seperate machine, please be sure that there is no firewall or router is locking the communication of iis and sql server.
if these won't help you to solve the problem, please give us some details like
- your connectionstring
- is your sql server is on another computer
- configuration details of your sql server (like active net libraries, authentication details)
thank-you for the quick reply. i will check all your suggestions and report back.
in the meantime, sql and webserver are all on the same server and the connection string is:-
databaseServer = "PROVIDER=SQLOLEDB;DATA SOURCE=pretty.ucc.usyd.edu.au;UID=xxx;PWD=xxx;DATABASE=xxx"|||hi kadir,
i must confess i'm not an sql server expert.
1. latest service pack and mdac were already installed.
2. allow remote connections is enabled. i couldn't see where (see point 3) using tcp/ip was. i'm using the ms sql management studioto find this information.
3. using sql server configuration manager i could find the following.
sql server 2005 network config - protocols for msqlserver
shared memory enabled
named pipes disabled
tcp/ip enabled
via disabled
sql native client config - client protocols
shared memory enabled
tcp/ip enabled
names pipes enabled
via disabled
4. we are connecting using an sql account. however, i couldn't see the option to allow mixed. i could only choose between windows authentication and sql server authentication.
thanks|||ok, just found the sql server surface area configuration. will check and report back.
|||remote connections is set to "local and remote connections" and using tcp/ip only.|||sql server and windows authentication mode were selected. so it looks like everything was configured they way you wanted.|||
Hi peter, sorry for the delay,
sql server and iis is on same machine so can you please test the following connectionstrings:
Provider=SQLNCLI;Server=(local);Database=xxx;Uid=xxx;Pwd=xxx
or
Provider=SQLNCLI;Server=127.0.0.1;Database=xxx;Uid=xxx;Pwd=xxx
and please inform me about the result.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
We have a classic ASP application which runs fine on NT Server, W2K Server a
nd with SQL7.0 and SQL 2000.
However, we are having problems with IIS6.0 and Windows 2003 Server when con
necting to a second Windows 2003 Server running SQL 2000 Server. IIS6.0 and
SQL on the same server works fine.
We see intermittent connection problems resulting in:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.
We have tried everything we have found trawling the web and still have no id
ea what is causing it.
One ASP script in particular that simply updates a table, fails most of the
time but does VERY rarely run successfully. The same script NEVER failed un
der NT/SQL7.0 or W2K/SQL2000.
We have increased timeout settings in IIS and the ASP scripts themselves. W
e have changed the OLEDB connection to reference the SQL Server IP address i
nstead of its name but nothing tried has made any difference.
Any suggestions would be gratefully received!
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.That error message is too generic to give specific information as to why it
is failing.
Have you checked for locking /blocking on the server?
You state that the ASP page is performing an update.
Otherwise, have you made network traces from the IIS box to the SQL Server?
What protocols are you using?
Can you reproduce the problem /error using SQL Query Analyser or only from
within IIS?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I have this EXACT same problem. My ASP app works fine on Win2000, SQL
Server 7 or 2000.
I get the same intermittent connection problem only when SQL Server
2000 is on a Win2k3 box. It doesn't seem to be restricted to ASP/IIS6
connections. I have problems with Access 2000 and Enterprise Mgr. as
well.
The ASP problem went away when I put SQL Server on the same box as
IIS6, but this can only be a temporary solution.
I've scoured the forums as well and I've seen this problem described
many times, but no solutions yet.
dcase
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message747245.html|||same here... I optimised the scripts to reduce the number of queries and
I've managed to reduce the number of errors, but I haven't been able to
eliminate them.
Windows 2000 used to work fine, but Windows 2003 on upgraded hardware
and with a tenth of the number of queries (or less) gives problems.
Sometimes the problem occurs when I'm trying to get a recordset, and
sometimes it occurs when I'm connecting to the SQL Server 2000 DB.
MUTU
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message747245.html|||We're having the same problems, except with WScript since moving a
server over to Windows 2003 Server. We are connecting to SQL Server 7
on a local network, specifying TCP/IP in the connection string.
The connection failure is intermittent, but is most easily reproduced
by performing a loop where connections are opened and closed. That
will pretty much guarantee that the error will occur at some random
spot in the loop. Sometimes it fails on the first try, sometimes on
the 415th try. It seems to be completely random.
We have eliminated network issues as a possibility by isolating the
servers onto their own switch and have been able to mitigate the
problem by re-using database connections rather than opening new ones.
Probably we will open a ticket with MS soon.
If anybody has info on a fix, it would help us out tremendously to
hear about it.
Pseudo code to reproduce issue...
begin loop
open database connection
execute a sql statement
close database connection
destroy connection
end loop
MUTU <MUTU.18f155@.mail.mcse.ms> wrote in message news:<MUTU.18f155@.mail.mcse.ms>...agreen">
> same here... I optimised the scripts to reduce the number of queries and
> I've managed to reduce the number of errors, but I haven't been able to
> eliminate them.
> Windows 2000 used to work fine, but Windows 2003 on upgraded hardware
> and with a tenth of the number of queries (or less) gives problems.
> Sometimes the problem occurs when I'm trying to get a recordset, and
> sometimes it occurs when I'm connecting to the SQL Server 2000 DB.|||SqlJunkies User wrote:
> *We have a classic ASP application which runs fine on NT Server, W2K
> Server and with SQL7.0 and SQL 2000.
> However, we are having problems with IIS6.0 and Windows 2003 Server
> when connecting to a second Windows 2003 Server running SQL 2000
> Server. IIS6.0 and SQL on the same server works fine.
> We see intermittent connection problems resulting in:
> Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist o
r
> access denied.
> We have tried everything we have found trawling the web and still
> have no idea what is causing it.
> One ASP script in particular that simply updates a table, fails most
> of the time but does VERY rarely run successfully. The same script
> NEVER failed under NT/SQL7.0 or W2K/SQL2000.
> We have increased timeout settings in IIS and the ASP scripts
> themselves. We have changed the OLEDB connection to reference the
> SQL Server IP address instead of its name but nothing tried has made
> any difference.
> Any suggestions would be gratefully received!
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup
> engine supports Post Alerts, Ratings, and Searching. *
jmrosseau
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message747245.html|||In case you haven't been told this already....
The intermittant failures smack strongly of TCP/IP ports being
blocked.
A named instance of a server will begin hunting for a TCP/IP Port
between 1025 to 5000. Many of these ports are typical Trojan ports.
You may have read the following:
http://support.microsoft.com/?id=287932
http://support.microsoft.com/default.aspx?kbid=814130
Lou Arnold
Ottawa Canada
On 28 Jun 2004 07:54:29 -0700, jjimenez1984@.yahoo.com (Joel) wrote:
[vbcol=seagreen]
>We're having the same problems, except with WScript since moving a
>server over to Windows 2003 Server. We are connecting to SQL Server 7
>on a local network, specifying TCP/IP in the connection string.
>The connection failure is intermittent, but is most easily reproduced
>by performing a loop where connections are opened and closed. That
>will pretty much guarantee that the error will occur at some random
>spot in the loop. Sometimes it fails on the first try, sometimes on
>the 415th try. It seems to be completely random.
>We have eliminated network issues as a possibility by isolating the
>servers onto their own switch and have been able to mitigate the
>problem by re-using database connections rather than opening new ones.
>Probably we will open a ticket with MS soon.
>If anybody has info on a fix, it would help us out tremendously to
>hear about it.
>Pseudo code to reproduce issue...
>--
>begin loop
>open database connection
>execute a sql statement
>close database connection
>destroy connection
>end loop
>--
>
>
>MUTU <MUTU.18f155@.mail.mcse.ms> wrote in message news:<MUTU.18f155@.mail.mcs
e.ms>...|||Here are our findings on this issue whereby we were attempting to update a t
able within a loop.
When we run the script, around 2 minutes into the execution, we would receiv
e the error.
Our original code where we saw this problem was as follows:
Dataconnection.open "Provider=SQLOLEDB; Data Source=sourcename; Initial Cata
log=catalogname; User Id=userid; Password=password"
SQLrep = "Select * from rep where deletedflag = 0 order by id"
Set objRSrep = Dataconnection.Execute(SQLrep)
We changed the connection and the opening of our recordsets to:
Set Dataconnection = Server.Createobject ("ADODB.Connection")
Dataconnection.ConnectionTimeout = 300
Dataconnection.open "DRIVER={SQL Server};SERVER=sourcename;DATABASE=dat
abasename;UID=userid;PWD=password"
Dataconnection.CommandTimeout = 300
Set objRSrep = Server.CreateObject("ADODB.Recordset")
objRSrep.Open SQLrep, Dataconnection, , AdLockOptimistic
We have found that this solution works but it takes about 4 times longer to
execute.
We have also found that by simply using:
Dataconnection.ConnectionTimeout = 300
Dataconnection.CommandTimeout = 300
with our original code also does the trick. At the point around 2 minutes w
here it previously failed, there is a stutter in execution of upto 19 second
s but it does manage to continue now. Well 6 successes out of 6 isn't bad w
hen previously it would hav
e been 1 success out of 20!
Hope this helps someone.
Neil H
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||Hi,
I encountered this problem recently out of sudden. The app used to work
fine and sound. However, this happens after a simple function was
deployed. Even we reverted to the previous state the app still
encounters the same problem.
All existing report generation functions which access database thru udl
are not working accordingly. However, other types of report generation
functions which access db thru crystal report are not affected. In
addition, the newly deployed function which access the db thru udl also
working fine.It's very weird.
If the database server is not accessible, why are the rest of functions
which also access db like search functions can work well.
Can somebody enlighten me?
Thanks a lots.
Rgds,
DBfaq
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Did anyone ever find a solution for this?
I have the exact same problem, access denied on a sql connect. Worked fine i
n iis 5.
Thank you...
nd with SQL7.0 and SQL 2000.
However, we are having problems with IIS6.0 and Windows 2003 Server when con
necting to a second Windows 2003 Server running SQL 2000 Server. IIS6.0 and
SQL on the same server works fine.
We see intermittent connection problems resulting in:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.
We have tried everything we have found trawling the web and still have no id
ea what is causing it.
One ASP script in particular that simply updates a table, fails most of the
time but does VERY rarely run successfully. The same script NEVER failed un
der NT/SQL7.0 or W2K/SQL2000.
We have increased timeout settings in IIS and the ASP scripts themselves. W
e have changed the OLEDB connection to reference the SQL Server IP address i
nstead of its name but nothing tried has made any difference.
Any suggestions would be gratefully received!
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.That error message is too generic to give specific information as to why it
is failing.
Have you checked for locking /blocking on the server?
You state that the ASP page is performing an update.
Otherwise, have you made network traces from the IIS box to the SQL Server?
What protocols are you using?
Can you reproduce the problem /error using SQL Query Analyser or only from
within IIS?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I have this EXACT same problem. My ASP app works fine on Win2000, SQL
Server 7 or 2000.
I get the same intermittent connection problem only when SQL Server
2000 is on a Win2k3 box. It doesn't seem to be restricted to ASP/IIS6
connections. I have problems with Access 2000 and Enterprise Mgr. as
well.
The ASP problem went away when I put SQL Server on the same box as
IIS6, but this can only be a temporary solution.
I've scoured the forums as well and I've seen this problem described
many times, but no solutions yet.
dcase
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message747245.html|||same here... I optimised the scripts to reduce the number of queries and
I've managed to reduce the number of errors, but I haven't been able to
eliminate them.
Windows 2000 used to work fine, but Windows 2003 on upgraded hardware
and with a tenth of the number of queries (or less) gives problems.
Sometimes the problem occurs when I'm trying to get a recordset, and
sometimes it occurs when I'm connecting to the SQL Server 2000 DB.
MUTU
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message747245.html|||We're having the same problems, except with WScript since moving a
server over to Windows 2003 Server. We are connecting to SQL Server 7
on a local network, specifying TCP/IP in the connection string.
The connection failure is intermittent, but is most easily reproduced
by performing a loop where connections are opened and closed. That
will pretty much guarantee that the error will occur at some random
spot in the loop. Sometimes it fails on the first try, sometimes on
the 415th try. It seems to be completely random.
We have eliminated network issues as a possibility by isolating the
servers onto their own switch and have been able to mitigate the
problem by re-using database connections rather than opening new ones.
Probably we will open a ticket with MS soon.
If anybody has info on a fix, it would help us out tremendously to
hear about it.
Pseudo code to reproduce issue...
begin loop
open database connection
execute a sql statement
close database connection
destroy connection
end loop
MUTU <MUTU.18f155@.mail.mcse.ms> wrote in message news:<MUTU.18f155@.mail.mcse.ms>...agreen">
> same here... I optimised the scripts to reduce the number of queries and
> I've managed to reduce the number of errors, but I haven't been able to
> eliminate them.
> Windows 2000 used to work fine, but Windows 2003 on upgraded hardware
> and with a tenth of the number of queries (or less) gives problems.
> Sometimes the problem occurs when I'm trying to get a recordset, and
> sometimes it occurs when I'm connecting to the SQL Server 2000 DB.|||SqlJunkies User wrote:
> *We have a classic ASP application which runs fine on NT Server, W2K
> Server and with SQL7.0 and SQL 2000.
> However, we are having problems with IIS6.0 and Windows 2003 Server
> when connecting to a second Windows 2003 Server running SQL 2000
> Server. IIS6.0 and SQL on the same server works fine.
> We see intermittent connection problems resulting in:
> Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist o
r
> access denied.
> We have tried everything we have found trawling the web and still
> have no idea what is causing it.
> One ASP script in particular that simply updates a table, fails most
> of the time but does VERY rarely run successfully. The same script
> NEVER failed under NT/SQL7.0 or W2K/SQL2000.
> We have increased timeout settings in IIS and the ASP scripts
> themselves. We have changed the OLEDB connection to reference the
> SQL Server IP address instead of its name but nothing tried has made
> any difference.
> Any suggestions would be gratefully received!
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup
> engine supports Post Alerts, Ratings, and Searching. *
jmrosseau
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message747245.html|||In case you haven't been told this already....
The intermittant failures smack strongly of TCP/IP ports being
blocked.
A named instance of a server will begin hunting for a TCP/IP Port
between 1025 to 5000. Many of these ports are typical Trojan ports.
You may have read the following:
http://support.microsoft.com/?id=287932
http://support.microsoft.com/default.aspx?kbid=814130
Lou Arnold
Ottawa Canada
On 28 Jun 2004 07:54:29 -0700, jjimenez1984@.yahoo.com (Joel) wrote:
[vbcol=seagreen]
>We're having the same problems, except with WScript since moving a
>server over to Windows 2003 Server. We are connecting to SQL Server 7
>on a local network, specifying TCP/IP in the connection string.
>The connection failure is intermittent, but is most easily reproduced
>by performing a loop where connections are opened and closed. That
>will pretty much guarantee that the error will occur at some random
>spot in the loop. Sometimes it fails on the first try, sometimes on
>the 415th try. It seems to be completely random.
>We have eliminated network issues as a possibility by isolating the
>servers onto their own switch and have been able to mitigate the
>problem by re-using database connections rather than opening new ones.
>Probably we will open a ticket with MS soon.
>If anybody has info on a fix, it would help us out tremendously to
>hear about it.
>Pseudo code to reproduce issue...
>--
>begin loop
>open database connection
>execute a sql statement
>close database connection
>destroy connection
>end loop
>--
>
>
>MUTU <MUTU.18f155@.mail.mcse.ms> wrote in message news:<MUTU.18f155@.mail.mcs
e.ms>...|||Here are our findings on this issue whereby we were attempting to update a t
able within a loop.
When we run the script, around 2 minutes into the execution, we would receiv
e the error.
Our original code where we saw this problem was as follows:
Dataconnection.open "Provider=SQLOLEDB; Data Source=sourcename; Initial Cata
log=catalogname; User Id=userid; Password=password"
SQLrep = "Select * from rep where deletedflag = 0 order by id"
Set objRSrep = Dataconnection.Execute(SQLrep)
We changed the connection and the opening of our recordsets to:
Set Dataconnection = Server.Createobject ("ADODB.Connection")
Dataconnection.ConnectionTimeout = 300
Dataconnection.open "DRIVER={SQL Server};SERVER=sourcename;DATABASE=dat
abasename;UID=userid;PWD=password"
Dataconnection.CommandTimeout = 300
Set objRSrep = Server.CreateObject("ADODB.Recordset")
objRSrep.Open SQLrep, Dataconnection, , AdLockOptimistic
We have found that this solution works but it takes about 4 times longer to
execute.
We have also found that by simply using:
Dataconnection.ConnectionTimeout = 300
Dataconnection.CommandTimeout = 300
with our original code also does the trick. At the point around 2 minutes w
here it previously failed, there is a stutter in execution of upto 19 second
s but it does manage to continue now. Well 6 successes out of 6 isn't bad w
hen previously it would hav
e been 1 success out of 20!
Hope this helps someone.
Neil H
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||Hi,
I encountered this problem recently out of sudden. The app used to work
fine and sound. However, this happens after a simple function was
deployed. Even we reverted to the previous state the app still
encounters the same problem.
All existing report generation functions which access database thru udl
are not working accordingly. However, other types of report generation
functions which access db thru crystal report are not affected. In
addition, the newly deployed function which access the db thru udl also
working fine.It's very weird.
If the database server is not accessible, why are the rest of functions
which also access db like search functions can work well.
Can somebody enlighten me?
Thanks a lots.
Rgds,
DBfaq
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Did anyone ever find a solution for this?
I have the exact same problem, access denied on a sql connect. Worked fine i
n iis 5.
Thank you...
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
We have a classic ASP application which runs fine on NT Server, W2K Server and with SQL7.0 and SQL 2000.
However, we are having problems with IIS6.0 and Windows 2003 Server when connecting to a second Windows 2003 Server running SQL 2000 Server. IIS6.0 and SQL on the same server works fine.
We see intermittent connection problems resulting in:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
We have tried everything we have found trawling the web and still have no idea what is causing it.
One ASP script in particular that simply updates a table, fails most of the time but does VERY rarely run successfully. The same script NEVER failed under NT/SQL7.0 or W2K/SQL2000.
We have increased timeout settings in IIS and the ASP scripts themselves. We have changed the OLEDB connection to reference the SQL Server IP address instead of its name but nothing tried has made any difference.
Any suggestions would be gratefully received!
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
That error message is too generic to give specific information as to why it
is failing.
Have you checked for locking /blocking on the server?
You state that the ASP page is performing an update.
Otherwise, have you made network traces from the IIS box to the SQL Server?
What protocols are you using?
Can you reproduce the problem /error using SQL Query Analyser or only from
within IIS?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||I have this EXACT same problem. My ASP app works fine on Win2000, SQL Server 7 or 2000.
I get the same intermittent connection problem only when SQL Server 2000 is on a Win2k3 box. It doesn't seem to be restricted to ASP/IIS6 connections. I have problems with Access 2000 and Enterprise Mgr. as well.
The ASP problem went away when I put SQL Server on the same box as IIS6, but this can only be a temporary solution.
I've scoured the forums as well and I've seen this problem described many times, but no solutions yet.|||same here... I optimised the scripts to reduce the number of queries and I've managed to reduce the number of errors, but I haven't been able to eliminate them.
Windows 2000 used to work fine, but Windows 2003 on upgraded hardware and with a tenth of the number of queries (or less) gives problems.
Sometimes the problem occurs when I'm trying to get a recordset, and sometimes it occurs when I'm connecting to the SQL Server 2000 DB.|||We're having the same problems, except with WScript since moving a
server over to Windows 2003 Server. We are connecting to SQL Server 7
on a local network, specifying TCP/IP in the connection string.
The connection failure is intermittent, but is most easily reproduced
by performing a loop where connections are opened and closed. That
will pretty much guarantee that the error will occur at some random
spot in the loop. Sometimes it fails on the first try, sometimes on
the 415th try. It seems to be completely random.
We have eliminated network issues as a possibility by isolating the
servers onto their own switch and have been able to mitigate the
problem by re-using database connections rather than opening new ones.
Probably we will open a ticket with MS soon.
If anybody has info on a fix, it would help us out tremendously to
hear about it.
Pseudo code to reproduce issue...
begin loop
open database connection
execute a sql statement
close database connection
destroy connection
end loop
MUTU <MUTU.18f155@.mail.mcse.ms> wrote in message news:<MUTU.18f155@.mail.mcse.ms>...
> same here... I optimised the scripts to reduce the number of queries and
> I've managed to reduce the number of errors, but I haven't been able to
> eliminate them.
> Windows 2000 used to work fine, but Windows 2003 on upgraded hardware
> and with a tenth of the number of queries (or less) gives problems.
> Sometimes the problem occurs when I'm trying to get a recordset, and
> sometimes it occurs when I'm connecting to the SQL Server 2000 DB.
|||We're having the same problems, except with WScript since moving a
server over to Windows 2003 Server. We are connecting to SQL Server 7
on a local network, specifying TCP/IP in the connection string.
The connection failure is intermittent, but is most easily reproduced
by performing a loop where connections are opened and closed. That
will pretty much guarantee that the error will occur at some random
spot in the loop. Sometimes it fails on the first try, sometimes on
the 415th try. It seems to be completely random.
We have eliminated network issues as a possibility by isolating the
servers onto their own switch and have been able to mitigate the
problem by re-using database connections rather than opening new ones.
Probably we will open a ticket with MS soon.
If anybody has info on a fix, it would help us out tremendously to
hear about it.
Pseudo code to reproduce issue...
begin loop
open database connection
execute a sql statement
close database connection
destroy connection
end loop
MUTU <MUTU.18f155@.mail.mcse.ms> wrote in message news:<MUTU.18f155@.mail.mcse.ms>...
> same here... I optimised the scripts to reduce the number of queries and
> I've managed to reduce the number of errors, but I haven't been able to
> eliminate them.
> Windows 2000 used to work fine, but Windows 2003 on upgraded hardware
> and with a tenth of the number of queries (or less) gives problems.
> Sometimes the problem occurs when I'm trying to get a recordset, and
> sometimes it occurs when I'm connecting to the SQL Server 2000 DB.
|||Originally posted by SqlJunkies User
We have a classic ASP application which runs fine on NT Server, W2K Server and with SQL7.0 and SQL 2000.
However, we are having problems with IIS6.0 and Windows 2003 Server when connecting to a second Windows 2003 Server running SQL 2000 Server. IIS6.0 and SQL on the same server works fine.
We see intermittent connection problems resulting in:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
We have tried everything we have found trawling the web and still have no idea what is causing it.
One ASP script in particular that simply updates a table, fails most of the time but does VERY rarely run successfully. The same script NEVER failed under NT/SQL7.0 or W2K/SQL2000.
We have increased timeout settings in IIS and the ASP scripts themselves. We have changed the OLEDB connection to reference the SQL Server IP address instead of its name but nothing tried has made any difference.
Any suggestions would be gratefully received!
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching. Originally posted by jmrosseau
I got my information through the microsoft website: http://support.microsoft.com/default...b;EN-US;328383
Here is what helped me:
Specify the protocol in your connection string. For example: "DSN=DSNName;SERVER=servername;DATABASE=YourDataBa seName;Network=DBMSSOCN;Address=IP_Address,1433;UI D=YourUID;PWD=YourPassword;"
In this example, you specify the network protocol as "DBMSSOCN", which means that you want to use the TCP/IP protocol. If you specify the protocol inside your connection string, Dbnetlib only uses the specified protocol and does not try any other protocol. Similarly, to enable Named Pipe protocol only, use a connection string similar to this: "DSN=DSNName;SERVER=servername;DATABASE=YourDataBa seName;Network=DBNMPNTW;Address=\\.\pipe\sql\query ;UID=YourUID;PWD=YourPassword;"|||In case you haven't been told this already....
The intermittant failures smack strongly of TCP/IP ports being
blocked.
A named instance of a server will begin hunting for a TCP/IP Port
between 1025 to 5000. Many of these ports are typical Trojan ports.
You may have read the following:
http://support.microsoft.com/?id=287932
http://support.microsoft.com/default.aspx?kbid=814130
Lou Arnold
Ottawa Canada
On 28 Jun 2004 07:54:29 -0700, jjimenez1984@.yahoo.com (Joel) wrote:
[vbcol=seagreen]
>We're having the same problems, except with WScript since moving a
>server over to Windows 2003 Server. We are connecting to SQL Server 7
>on a local network, specifying TCP/IP in the connection string.
>The connection failure is intermittent, but is most easily reproduced
>by performing a loop where connections are opened and closed. That
>will pretty much guarantee that the error will occur at some random
>spot in the loop. Sometimes it fails on the first try, sometimes on
>the 415th try. It seems to be completely random.
>We have eliminated network issues as a possibility by isolating the
>servers onto their own switch and have been able to mitigate the
>problem by re-using database connections rather than opening new ones.
>Probably we will open a ticket with MS soon.
>If anybody has info on a fix, it would help us out tremendously to
>hear about it.
>Pseudo code to reproduce issue...
>--
>begin loop
>open database connection
>execute a sql statement
>close database connection
>destroy connection
>end loop
>--
>
>
>MUTU <MUTU.18f155@.mail.mcse.ms> wrote in message news:<MUTU.18f155@.mail.mcse.ms>...
|||In case you haven't been told this already....
The intermittant failures smack strongly of TCP/IP ports being
blocked.
A named instance of a server will begin hunting for a TCP/IP Port
between 1025 to 5000. Many of these ports are typical Trojan ports.
You may have read the following:
http://support.microsoft.com/?id=287932
http://support.microsoft.com/default.aspx?kbid=814130
Lou Arnold
Ottawa Canada
On 28 Jun 2004 07:54:29 -0700, jjimenez1984@.yahoo.com (Joel) wrote:
[vbcol=seagreen]
>We're having the same problems, except with WScript since moving a
>server over to Windows 2003 Server. We are connecting to SQL Server 7
>on a local network, specifying TCP/IP in the connection string.
>The connection failure is intermittent, but is most easily reproduced
>by performing a loop where connections are opened and closed. That
>will pretty much guarantee that the error will occur at some random
>spot in the loop. Sometimes it fails on the first try, sometimes on
>the 415th try. It seems to be completely random.
>We have eliminated network issues as a possibility by isolating the
>servers onto their own switch and have been able to mitigate the
>problem by re-using database connections rather than opening new ones.
>Probably we will open a ticket with MS soon.
>If anybody has info on a fix, it would help us out tremendously to
>hear about it.
>Pseudo code to reproduce issue...
>--
>begin loop
>open database connection
>execute a sql statement
>close database connection
>destroy connection
>end loop
>--
>
>
>MUTU <MUTU.18f155@.mail.mcse.ms> wrote in message news:<MUTU.18f155@.mail.mcse.ms>...
However, we are having problems with IIS6.0 and Windows 2003 Server when connecting to a second Windows 2003 Server running SQL 2000 Server. IIS6.0 and SQL on the same server works fine.
We see intermittent connection problems resulting in:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
We have tried everything we have found trawling the web and still have no idea what is causing it.
One ASP script in particular that simply updates a table, fails most of the time but does VERY rarely run successfully. The same script NEVER failed under NT/SQL7.0 or W2K/SQL2000.
We have increased timeout settings in IIS and the ASP scripts themselves. We have changed the OLEDB connection to reference the SQL Server IP address instead of its name but nothing tried has made any difference.
Any suggestions would be gratefully received!
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
That error message is too generic to give specific information as to why it
is failing.
Have you checked for locking /blocking on the server?
You state that the ASP page is performing an update.
Otherwise, have you made network traces from the IIS box to the SQL Server?
What protocols are you using?
Can you reproduce the problem /error using SQL Query Analyser or only from
within IIS?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||I have this EXACT same problem. My ASP app works fine on Win2000, SQL Server 7 or 2000.
I get the same intermittent connection problem only when SQL Server 2000 is on a Win2k3 box. It doesn't seem to be restricted to ASP/IIS6 connections. I have problems with Access 2000 and Enterprise Mgr. as well.
The ASP problem went away when I put SQL Server on the same box as IIS6, but this can only be a temporary solution.
I've scoured the forums as well and I've seen this problem described many times, but no solutions yet.|||same here... I optimised the scripts to reduce the number of queries and I've managed to reduce the number of errors, but I haven't been able to eliminate them.
Windows 2000 used to work fine, but Windows 2003 on upgraded hardware and with a tenth of the number of queries (or less) gives problems.
Sometimes the problem occurs when I'm trying to get a recordset, and sometimes it occurs when I'm connecting to the SQL Server 2000 DB.|||We're having the same problems, except with WScript since moving a
server over to Windows 2003 Server. We are connecting to SQL Server 7
on a local network, specifying TCP/IP in the connection string.
The connection failure is intermittent, but is most easily reproduced
by performing a loop where connections are opened and closed. That
will pretty much guarantee that the error will occur at some random
spot in the loop. Sometimes it fails on the first try, sometimes on
the 415th try. It seems to be completely random.
We have eliminated network issues as a possibility by isolating the
servers onto their own switch and have been able to mitigate the
problem by re-using database connections rather than opening new ones.
Probably we will open a ticket with MS soon.
If anybody has info on a fix, it would help us out tremendously to
hear about it.
Pseudo code to reproduce issue...
begin loop
open database connection
execute a sql statement
close database connection
destroy connection
end loop
MUTU <MUTU.18f155@.mail.mcse.ms> wrote in message news:<MUTU.18f155@.mail.mcse.ms>...
> same here... I optimised the scripts to reduce the number of queries and
> I've managed to reduce the number of errors, but I haven't been able to
> eliminate them.
> Windows 2000 used to work fine, but Windows 2003 on upgraded hardware
> and with a tenth of the number of queries (or less) gives problems.
> Sometimes the problem occurs when I'm trying to get a recordset, and
> sometimes it occurs when I'm connecting to the SQL Server 2000 DB.
|||We're having the same problems, except with WScript since moving a
server over to Windows 2003 Server. We are connecting to SQL Server 7
on a local network, specifying TCP/IP in the connection string.
The connection failure is intermittent, but is most easily reproduced
by performing a loop where connections are opened and closed. That
will pretty much guarantee that the error will occur at some random
spot in the loop. Sometimes it fails on the first try, sometimes on
the 415th try. It seems to be completely random.
We have eliminated network issues as a possibility by isolating the
servers onto their own switch and have been able to mitigate the
problem by re-using database connections rather than opening new ones.
Probably we will open a ticket with MS soon.
If anybody has info on a fix, it would help us out tremendously to
hear about it.
Pseudo code to reproduce issue...
begin loop
open database connection
execute a sql statement
close database connection
destroy connection
end loop
MUTU <MUTU.18f155@.mail.mcse.ms> wrote in message news:<MUTU.18f155@.mail.mcse.ms>...
> same here... I optimised the scripts to reduce the number of queries and
> I've managed to reduce the number of errors, but I haven't been able to
> eliminate them.
> Windows 2000 used to work fine, but Windows 2003 on upgraded hardware
> and with a tenth of the number of queries (or less) gives problems.
> Sometimes the problem occurs when I'm trying to get a recordset, and
> sometimes it occurs when I'm connecting to the SQL Server 2000 DB.
|||
Quote:
We have a classic ASP application which runs fine on NT Server, W2K Server and with SQL7.0 and SQL 2000.
However, we are having problems with IIS6.0 and Windows 2003 Server when connecting to a second Windows 2003 Server running SQL 2000 Server. IIS6.0 and SQL on the same server works fine.
We see intermittent connection problems resulting in:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
We have tried everything we have found trawling the web and still have no idea what is causing it.
One ASP script in particular that simply updates a table, fails most of the time but does VERY rarely run successfully. The same script NEVER failed under NT/SQL7.0 or W2K/SQL2000.
We have increased timeout settings in IIS and the ASP scripts themselves. We have changed the OLEDB connection to reference the SQL Server IP address instead of its name but nothing tried has made any difference.
Any suggestions would be gratefully received!
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
Quote:
I got my information through the microsoft website: http://support.microsoft.com/default...b;EN-US;328383
Here is what helped me:
Specify the protocol in your connection string. For example: "DSN=DSNName;SERVER=servername;DATABASE=YourDataBa seName;Network=DBMSSOCN;Address=IP_Address,1433;UI D=YourUID;PWD=YourPassword;"
In this example, you specify the network protocol as "DBMSSOCN", which means that you want to use the TCP/IP protocol. If you specify the protocol inside your connection string, Dbnetlib only uses the specified protocol and does not try any other protocol. Similarly, to enable Named Pipe protocol only, use a connection string similar to this: "DSN=DSNName;SERVER=servername;DATABASE=YourDataBa seName;Network=DBNMPNTW;Address=\\.\pipe\sql\query ;UID=YourUID;PWD=YourPassword;"|||In case you haven't been told this already....
The intermittant failures smack strongly of TCP/IP ports being
blocked.
A named instance of a server will begin hunting for a TCP/IP Port
between 1025 to 5000. Many of these ports are typical Trojan ports.
You may have read the following:
http://support.microsoft.com/?id=287932
http://support.microsoft.com/default.aspx?kbid=814130
Lou Arnold
Ottawa Canada
On 28 Jun 2004 07:54:29 -0700, jjimenez1984@.yahoo.com (Joel) wrote:
[vbcol=seagreen]
>We're having the same problems, except with WScript since moving a
>server over to Windows 2003 Server. We are connecting to SQL Server 7
>on a local network, specifying TCP/IP in the connection string.
>The connection failure is intermittent, but is most easily reproduced
>by performing a loop where connections are opened and closed. That
>will pretty much guarantee that the error will occur at some random
>spot in the loop. Sometimes it fails on the first try, sometimes on
>the 415th try. It seems to be completely random.
>We have eliminated network issues as a possibility by isolating the
>servers onto their own switch and have been able to mitigate the
>problem by re-using database connections rather than opening new ones.
>Probably we will open a ticket with MS soon.
>If anybody has info on a fix, it would help us out tremendously to
>hear about it.
>Pseudo code to reproduce issue...
>--
>begin loop
>open database connection
>execute a sql statement
>close database connection
>destroy connection
>end loop
>--
>
>
>MUTU <MUTU.18f155@.mail.mcse.ms> wrote in message news:<MUTU.18f155@.mail.mcse.ms>...
|||In case you haven't been told this already....
The intermittant failures smack strongly of TCP/IP ports being
blocked.
A named instance of a server will begin hunting for a TCP/IP Port
between 1025 to 5000. Many of these ports are typical Trojan ports.
You may have read the following:
http://support.microsoft.com/?id=287932
http://support.microsoft.com/default.aspx?kbid=814130
Lou Arnold
Ottawa Canada
On 28 Jun 2004 07:54:29 -0700, jjimenez1984@.yahoo.com (Joel) wrote:
[vbcol=seagreen]
>We're having the same problems, except with WScript since moving a
>server over to Windows 2003 Server. We are connecting to SQL Server 7
>on a local network, specifying TCP/IP in the connection string.
>The connection failure is intermittent, but is most easily reproduced
>by performing a loop where connections are opened and closed. That
>will pretty much guarantee that the error will occur at some random
>spot in the loop. Sometimes it fails on the first try, sometimes on
>the 415th try. It seems to be completely random.
>We have eliminated network issues as a possibility by isolating the
>servers onto their own switch and have been able to mitigate the
>problem by re-using database connections rather than opening new ones.
>Probably we will open a ticket with MS soon.
>If anybody has info on a fix, it would help us out tremendously to
>hear about it.
>Pseudo code to reproduce issue...
>--
>begin loop
>open database connection
>execute a sql statement
>close database connection
>destroy connection
>end loop
>--
>
>
>MUTU <MUTU.18f155@.mail.mcse.ms> wrote in message news:<MUTU.18f155@.mail.mcse.ms>...
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied - PLEASE
Hi have created an application in asp.net to connect to a sql database.
The reason why I am sooooo very confused is because the connection to the database works if i keep the project on my local machine and connect to the database on the server, but as soon as I upload the project to the server, I get the following error when I try to connect to the database:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied
Can anyone tell me why this is happenning? - PLEASE - Any help will be appreciated
Thanks,
Danielle
you might want to check your connection string and make sure its pointing to the rigtht database server.
The reason why I am sooooo very confused is because the connection to the database works if i keep the project on my local machine and connect to the database on the server, but as soon as I upload the project to the server, I get the following error when I try to connect to the database:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied
Can anyone tell me why this is happenning? - PLEASE - Any help will be appreciated
Thanks,
Danielle
you might want to check your connection string and make sure its pointing to the rigtht database server.
Thursday, February 16, 2012
[ConnectionWrite (send()).]General network error
Hi All
I have a visual basic application, which uses SQL Server 2000(sp3a) as
the database, MDAC 2.8 library. I open one connection to the db on the
application start, and the applicaiton works fine for few days, then
suddenly it gives this error [DBNETLIB][ConnectionWrite
(send()).]General network error. -2147467259 .
I tried to close the connection and open again on this error, and I get
this error, [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
not exist or access denied. -2147467259. If the application is closed
and opened again, it works fine without any problems.
This applicaiton runs on at around 12 different sites.There is no
pattern to when this error
occurs and I really cannot replicate it.
I have checked the SQL server logs, application logs, system logs on
server and client machines, there is nothing useful I could find.
The client running the application, and the server and on the same LAN,
no firewall.
I already posted this message in SQLSERVER.Server group, but didnt get
any help.
Any help is highly appreciated.
Thanksi have seen faulty NICs do weird things. the error simply means the app
can't get to the server. this is definitely _connection_ problem.
-oj
<tech.support.b@.gmail.com> wrote in message
news:1150444943.918481.99820@.g10g2000cwb.googlegroups.com...
> Hi All
> I have a visual basic application, which uses SQL Server 2000(sp3a) as
> the database, MDAC 2.8 library. I open one connection to the db on the
> application start, and the applicaiton works fine for few days, then
> suddenly it gives this error [DBNETLIB][ConnectionWrite
> (send()).]General network error. -2147467259 .
> I tried to close the connection and open again on this error, and I get
> this error, [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied. -2147467259. If the application is closed
> and opened again, it works fine without any problems.
> This applicaiton runs on at around 12 different sites.There is no
> pattern to when this error
> occurs and I really cannot replicate it.
> I have checked the SQL server logs, application logs, system logs on
> server and client machines, there is nothing useful I could find.
> The client running the application, and the server and on the same LAN,
> no firewall.
> I already posted this message in SQLSERVER.Server group, but didnt get
> any help.
> Any help is highly appreciated.
> Thanks
>|||oj wrote:
> i have seen faulty NICs do weird things. the error simply means the app
> can't get to the server. this is definitely _connection_ problem.
> --
> -oj
Thanks for ur reply oj., but i certainly do not think this is a case of
faulty NIC's. One or two or three pcs getting this error, i would
understand., but this happens everywhere, but without a pattern.|||would you by any chance using windows authentication? unable to communicate
with the domain controller would cause issue.
-oj
<tech.support.b@.gmail.com> wrote in message
news:1150451691.161431.42480@.r2g2000cwb.googlegroups.com...
> oj wrote:
> Thanks for ur reply oj., but i certainly do not think this is a case of
> faulty NIC's. One or two or three pcs getting this error, i would
> understand., but this happens everywhere, but without a pattern.
>|||oj wrote:
> would you by any chance using windows authentication? unable to communicat
e
> with the domain controller would cause issue.
> --
> -oj
>
> <tech.support.b@.gmail.com> wrote in message
> news:1150451691.161431.42480@.r2g2000cwb.googlegroups.com...
No, I am not using Windows authentication, I am using SQL server
authentication. No Domain, its a workgroup. Funny part is, the moment
this error happens, if i kill the application and run the application
again, there is no problem., however, if at the time of the error, I
try to close and re-open the connection, it always fails.|||hmm...what is the value for @.@.max_connections. and @.@.version.
can you ping the server once an error occurs. can you connect via <ip,port>.
-oj
<tech.support.b@.gmail.com> wrote in message
news:1150483296.616430.109960@.h76g2000cwa.googlegroups.com...
> No, I am not using Windows authentication, I am using SQL server
> authentication. No Domain, its a workgroup. Funny part is, the moment
> this error happens, if i kill the application and run the application
> again, there is no problem., however, if at the time of the error, I
> try to close and re-open the connection, it always fails.
>|||oj wrote:
> hmm...what is the value for @.@.max_connections. and @.@.version.
> can you ping the server once an error occurs. can you connect via <ip,port
>.
>
> --
> -oj
>
> <tech.support.b@.gmail.com> wrote in message
> news:1150483296.616430.109960@.h76g2000cwa.googlegroups.com...
@.@.version returns
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
@.@.max_connections is 32767
The server is pingable, and other users continue working without any
problems while this problem happens.
I have a visual basic application, which uses SQL Server 2000(sp3a) as
the database, MDAC 2.8 library. I open one connection to the db on the
application start, and the applicaiton works fine for few days, then
suddenly it gives this error [DBNETLIB][ConnectionWrite
(send()).]General network error. -2147467259 .
I tried to close the connection and open again on this error, and I get
this error, [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
not exist or access denied. -2147467259. If the application is closed
and opened again, it works fine without any problems.
This applicaiton runs on at around 12 different sites.There is no
pattern to when this error
occurs and I really cannot replicate it.
I have checked the SQL server logs, application logs, system logs on
server and client machines, there is nothing useful I could find.
The client running the application, and the server and on the same LAN,
no firewall.
I already posted this message in SQLSERVER.Server group, but didnt get
any help.
Any help is highly appreciated.
Thanksi have seen faulty NICs do weird things. the error simply means the app
can't get to the server. this is definitely _connection_ problem.
-oj
<tech.support.b@.gmail.com> wrote in message
news:1150444943.918481.99820@.g10g2000cwb.googlegroups.com...
> Hi All
> I have a visual basic application, which uses SQL Server 2000(sp3a) as
> the database, MDAC 2.8 library. I open one connection to the db on the
> application start, and the applicaiton works fine for few days, then
> suddenly it gives this error [DBNETLIB][ConnectionWrite
> (send()).]General network error. -2147467259 .
> I tried to close the connection and open again on this error, and I get
> this error, [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied. -2147467259. If the application is closed
> and opened again, it works fine without any problems.
> This applicaiton runs on at around 12 different sites.There is no
> pattern to when this error
> occurs and I really cannot replicate it.
> I have checked the SQL server logs, application logs, system logs on
> server and client machines, there is nothing useful I could find.
> The client running the application, and the server and on the same LAN,
> no firewall.
> I already posted this message in SQLSERVER.Server group, but didnt get
> any help.
> Any help is highly appreciated.
> Thanks
>|||oj wrote:
> i have seen faulty NICs do weird things. the error simply means the app
> can't get to the server. this is definitely _connection_ problem.
> --
> -oj
Thanks for ur reply oj., but i certainly do not think this is a case of
faulty NIC's. One or two or three pcs getting this error, i would
understand., but this happens everywhere, but without a pattern.|||would you by any chance using windows authentication? unable to communicate
with the domain controller would cause issue.
-oj
<tech.support.b@.gmail.com> wrote in message
news:1150451691.161431.42480@.r2g2000cwb.googlegroups.com...
> oj wrote:
> Thanks for ur reply oj., but i certainly do not think this is a case of
> faulty NIC's. One or two or three pcs getting this error, i would
> understand., but this happens everywhere, but without a pattern.
>|||oj wrote:
> would you by any chance using windows authentication? unable to communicat
e
> with the domain controller would cause issue.
> --
> -oj
>
> <tech.support.b@.gmail.com> wrote in message
> news:1150451691.161431.42480@.r2g2000cwb.googlegroups.com...
No, I am not using Windows authentication, I am using SQL server
authentication. No Domain, its a workgroup. Funny part is, the moment
this error happens, if i kill the application and run the application
again, there is no problem., however, if at the time of the error, I
try to close and re-open the connection, it always fails.|||hmm...what is the value for @.@.max_connections. and @.@.version.
can you ping the server once an error occurs. can you connect via <ip,port>.
-oj
<tech.support.b@.gmail.com> wrote in message
news:1150483296.616430.109960@.h76g2000cwa.googlegroups.com...
> No, I am not using Windows authentication, I am using SQL server
> authentication. No Domain, its a workgroup. Funny part is, the moment
> this error happens, if i kill the application and run the application
> again, there is no problem., however, if at the time of the error, I
> try to close and re-open the connection, it always fails.
>|||oj wrote:
> hmm...what is the value for @.@.max_connections. and @.@.version.
> can you ping the server once an error occurs. can you connect via <ip,port
>.
>
> --
> -oj
>
> <tech.support.b@.gmail.com> wrote in message
> news:1150483296.616430.109960@.h76g2000cwa.googlegroups.com...
@.@.version returns
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
@.@.max_connections is 32767
The server is pingable, and other users continue working without any
problems while this problem happens.
[C#] Failing to use SCOPY_IDENTITY()
Dear Reader,
Everything else works unless the SCOPE_IDENTITY() things.
I hope someone can help me out fixing this mistake.
Tnx in advanced,
so for some reason it does not want to get the value in the sqlvariabele @.OID.
The user is going to fill in the form and if he presses the "OK" button of that form the query will be generated (for example it has to indiviualy add the different kinds of medicine related to the medical week rapport (that's why I need the SCOPE_IDENTITY() function to give me the internal auto generated increment value of that medical report).|||This is the code that builts the query bye the way:
Currently I am building an application for a theme park where I work as a trainee for school, one project for me is to rebuild all the hundreds of databases into a few sql driven application's. Now I got a problem whit the use of SCOPE_IDENTITY(). Because the data has to be correct before inserting it into the database I use the transact features of .NET and I create 1 SQL string wich I dump in that method. The problem is that I can't be able to use the value of SCOPE_IDENTITY() for some reason, maybe you guys see a mistake in the actual (dynamic) query:
Here is the query built up by my program to write the data (of a single form) into the database:
DECLARE @.OID int;
INSERT INTO Medisch (med_za_ID, med_WeekNr, med_Enen, med_Bijzonderheden, med_AfwijkendGedrag, med_SexGedrag, med_GemAdemhaling, med_GemHoesten, med_Temperatuur, med_Conditie, med_BloedGeprikt, med_Cupje, med_BasisVis, med_Eetlust, med_GemGewicht)VALUES(3,1123,,'','','',,,,'','False','False',45,'',);
SELECT @.OID = SCOPE_IDENTITY();
INSERT INTO Medisch_Medicijnen_Details (mmd_mmt_ID, mmd_med_ID, mmd_Hoeveelheid, mmd_Aantal) VALUES(@.OID, 2,23, 23 );
Everything else works unless the SCOPE_IDENTITY() things.
I hope someone can help me out fixing this mistake.
Tnx in advanced,
Grz.
StefanEXACTLY what is your problem? Does Medisch have an IDENTITY column? Why are you not passing any values for some rows (not sure this syntax would work). Why not just not include the column names in the column list?|||The problem is that SCOPE_IDENTITY() doesn't seem to return any values.
They don't have all valeus, because I did not fill in all the data in the form so it will insert a null value. The string is ok, only the part of returing the identity. (Medisch has an autoincrement column called med_ID).|||To make it clear, you are saying that @.OID is null?|||No, it does not seem to get any value :P. The following error ocurse:
Incorrect syntax near ',' wich occurs in the part
VALUES(@.OID, "+medI
so for some reason it does not want to get the value in the sqlvariabele @.OID.
For clearance, it will put that string in my first post inot the following method:
|||Is this an error when you compile? If so, then it has NOTHING to do with the SQL Syntax, it is an error in how you are building the string in C# code.|||This error occurs when running the application.
public bool SqlNieuweInvoer(string sqlDatabase, string sqlQuery)
{
SqlConnection conData = new SqlConnection("server="+strServerNaam+";" + "database="+sqlDatabase+";Trusted_Connection=yes");
SqlCommand comData = new SqlCommand(sqlQuery, conData);
conData.Open();SqlTransaction TranData = conData.BeginTransaction();
comData.Transaction = TranData;try
{
comData.ExecuteNonQuery();
TranData.Commit();
return true;
}
catch (Exception e)
{
TranData.Rollback();
MessageBox.Show(Convert.ToString(e));
return false;
}
finally
{
conData.Close();
}
}
The user is going to fill in the form and if he presses the "OK" button of that form the query will be generated (for example it has to indiviualy add the different kinds of medicine related to the medical week rapport (that's why I need the SCOPE_IDENTITY() function to give me the internal auto generated increment value of that medical report).|||This is the code that builts the query bye the way:
private string BouwQuery()
{
cstrInvoerQuery = cstrInvoerQuery + "DECLARE @.OID int;";
cstrInvoerQuery = cstrInvoerQuery + "INSERT INTO Medisch (med_za_ID, med_WeekNr, med_Enen, med_Bijzonderheden, med_AfwijkendGedrag, med_SexGedrag, med_GemAdemhaling, med_GemHoesten, med_Temperatuur, med_Conditie, med_BloedGeprikt, med_Cupje, med_BasisVis, med_Eetlust, med_GemGewicht)" +
"VALUES("+this.DierID+","+tbWeekNr.Text+","+tbEnen.Text+",'"+tbBijzonderheden.Text+"','"+tbAfwijkendGedrag.Text+"','"+tbSexGedrag.Text+"',"+tbAdemfrequentie.Text+","+tbHoesten.Text+","+tbTemperatuur.Text+",'"+gcbConditie.Text+"','"+cbBloedgeprikt.Checked+"','"+cbCupje.Checked+"',"+tbBasisVis.Text+",'"+tbEetlust.Text+"',"+tbGewicht.Text+");";
cstrInvoerQuery = cstrInvoerQuery + " SELECT @.OID = SCOPE_IDENTITY();";string[] test = new string[] {};
int medID;
string strDelimiter = "\t[";
char[] delimiter = strDelimiter.ToCharArray();
foreach (string Item in lbMedicijnenSupplementen.Items)
{
if (!Item.StartsWith("Naam:"))
{
test = Item.Split(delimiter, 6);
medID = VerkrijgMedicijnSupplementID(test[0]);
if (medID != 0)
{
cstrInvoerQuery = cstrInvoerQuery + "INSERT INTO Medisch_Medicijnen_Details (mmd_mmt_ID, mmd_med_ID, mmd_Hoeveelheid, mmd_Aantal) VALUES(@.OID, "+medID+","+test[1]+", "+test[2]+");";
}
}
}if(cbBloedgeprikt.Checked)
{
cstrInvoerQuery = cstrInvoerQuery + "UPDATE OmgevingsWaardes SET ow_LaatstBloedGeprikt='"+tbWeekNr.Text+"' WHERE ow_za_ID = "+this.DierID+";";
}tbBijzonderheden.Text = cstrInvoerQuery;
return cstrInvoerQuery;
}
Maybe this helps you find me an answer :).|||Your problem is this. You are calling ExecuteNonQuery. This DOES NOT expect a result set to be returned. However, you ARE returning a result set (that is what SELECT @.OID... does).
Use ExecuteReader and get back a DataReader, or better use ExecuteScaler() and the return from that method can be cast to an integer.|||That might be helpfull, I thought the whole query was being excuted on the SQL server, but they actually being excecuted all @. once? That explaines allot, thanks, I will go and trie the other 2 things :).|||Whoohoo!!! I used the scalar 1 and it finally works, thank you very very much :).
Subscribe to:
Posts (Atom)