Objects related to DB: app

Showing posts with label app. Show all posts

Friday, March 30, 2012

ODBC Error

I get the following error (from my front-end Access app AND Query Analyzer)
when accessing a particular table. It is a rather large table, ~1,750,000
rows in it. I tried looking through "limitations" in BOL and couldn't find
anything. Is this a server setting or ODBC setting or... ?
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionChe
ckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection BrokenWhat are you trying to do with that much data? Access isn't designed
to handle scrolling through millions of rows, although you may be
having network issues, as the error message suggests. Restrict the
data fetched with a WHERE clause, or if it's a report, write a stored
procedure and call it through a pass-through query.
--mary
On Thu, 15 Apr 2004 17:03:47 -0700, "Ron Hinds"
<__NoSpam@.__NoSpamramac.com> wrote:

>I get the following error (from my front-end Access app AND Query Analyzer)
>when accessing a particular table. It is a rather large table, ~1,750,000
>rows in it. I tried looking through "limitations" in BOL and couldn't find
>anything. Is this a server setting or ODBC setting or... ?
>[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionCh
eckForData
>(CheckforData()).
>Server: Msg 11, Level 16, State 1, Line 0
>General network error. Check your network documentation.
>Connection Broken
>|||What I'm trying to do is populate a local table (BackOrders) that's actually
used in the form from a server table (SalesDetail). There is a constraint
(WHERE Customer=x AND QtyAvailable>0) but using DAO it still tries to fetch
the entire table. Here is the original SQL (pure Access 97 app):
INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered,
QtyAvailable, Description) SELECT SalesDetail.InvoiceNumber,
SalesDetail.InvoiceDate, SalesDetail.Part, SalesDetail.Price,
SalesDetail.QtyOrdered, Inventory.QtyAvailable, Inventory.Description FROM
SalesDetail INNER JOIN Inventory ON SalesDetail.Part = Inventory.Part WHERE
SalesDetail.Customer=x AND SalesDetail.QtyOrdered > SalesDetail.QtyShipped
AND Inventory.Qty - Inventory.QtyCommitted > 0 AND SalesDetail.BackOrder > 0
ORDER BY SalesDetail.invoiceDate DESC
Even if this worked as-is with SQL Server (it doesn't - gives the same
error), it would be horribly slow, so I wanted to optimize it by creating a
View of the server tables. But I don't know the value of 'x' (Customer)
until run time. So I used the following DDL to create the View on SQL
Server:
USE Prototype
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'vBackOrder')
DROP VIEW vBackOrder
GO
CREATE VIEW vBackOrder AS
SELECT TOP 100 PERCENT SalesDetail.invoiceNumber, SalesDetail.invoiceDate,
SalesDetail.Part, SalesDetail.Price, SalesDetail.QtyOrdered, Inventory.Qty -
Inventory.QtyCommitted AS QtyAvailable, Inventory.Description
SalesDetail.Customer FROM SalesDetail INNER JOIN Inventory ON
SalesDetail.itemID = Inventory.invItemID WHERE SalesDetail.QtyOrdered >
SalesDetail.QtyShipped AND Inventory.Qty - Inventory.QtyCommitted > 0 AND
SalesDetail.BackOrder > 0 ORDER BY SalesDetail.invoiceDate DESC
GO
I linked vBackOrder in Access then used this DAO code to populate local
table BackOrders:
INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered,
QtyAvailable, Description) SELECT vBackOrder.InvoiceNumber,
vBackOrder.InvoiceDate, vBackOrder.Part, vBackOrder.Price,
vBackOrder.QtyOrdered, vBackOrder.QtyAvailable, vBackOrder.Description FROM
vBackOrder WHERE vBackOrder.Customer=x
This also gives the same error, as does attempting to open the linked
vBackOrder in Access. I then went to QA and tried just the SELECT portion of
the CREATE VIEW with the same result. To narrow it down, I tried SELECTing *
FROM Inventory - a little slow but no problem. I then tried the same thing
with SalesDetail and again get the same error. I have >300 server tables of
varying sizes in this app. SalesDetail is by far the largest, and it is the
*only* one I get the error on. So I'm assuming it has something to do with
the size.
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:pflv701qqge68qf7u94r6bnetfkb1iuc53@.
4ax.com...
> What are you trying to do with that much data? Access isn't designed
> to handle scrolling through millions of rows, although you may be
> having network issues, as the error message suggests. Restrict the
> data fetched with a WHERE clause, or if it's a report, write a stored
> procedure and call it through a pass-through query.
> --mary
> On Thu, 15 Apr 2004 17:03:47 -0700, "Ron Hinds"
> <__NoSpam@.__NoSpamramac.com> wrote:
>
Analyzer)[vbcol=seagreen]
find[vbcol=seagreen]
>|||DAO is the problem. You're loading the Jet engine and using it for SQL
Server data operations, something it was never designed or optimized
to do. Create a stored procedure instead of a buinch of views. Stored
procedures support parameters and complex logic, and return a
read-only result set which you can use to populate your local table.
Call the stored procedure from a pass-through query where you set the
SQL syntax to something like this in your code (you can use DAO to set
properties of a QueryDef object and execute it):
qdef.SQL = "EXEC myproc 'paramvalue1', val2" etc.
qdef.Execute
Pass-through queries bypass the Jet engine when they're executed and
are the most efficient way of getting back large result sets since all
of the processing takes place on the server, not in Jet. You then
create either an Insert or Update query that selects from your
pass-through query into the local table. When you call the
insert/update query it will automatically execute the pass-through
query to get the records.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Fri, 16 Apr 2004 17:09:15 -0700, "Ron Hinds"
<__NoSpam@.__NoSpamramac.com> wrote:

>What I'm trying to do is populate a local table (BackOrders) that's actuall
y
>used in the form from a server table (SalesDetail). There is a constraint
>(WHERE Customer=x AND QtyAvailable>0) but using DAO it still tries to fetch
>the entire table. Here is the original SQL (pure Access 97 app):
>INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered
,
>QtyAvailable, Description) SELECT SalesDetail.InvoiceNumber,
>SalesDetail.InvoiceDate, SalesDetail.Part, SalesDetail.Price,
>SalesDetail.QtyOrdered, Inventory.QtyAvailable, Inventory.Description FROM
>SalesDetail INNER JOIN Inventory ON SalesDetail.Part = Inventory.Part WHERE
>SalesDetail.Customer=x AND SalesDetail.QtyOrdered > SalesDetail.QtyShipped
>AND Inventory.Qty - Inventory.QtyCommitted > 0 AND SalesDetail.BackOrder >
0
>ORDER BY SalesDetail.invoiceDate DESC
>Even if this worked as-is with SQL Server (it doesn't - gives the same
>error), it would be horribly slow, so I wanted to optimize it by creating a
>View of the server tables. But I don't know the value of 'x' (Customer)
>until run time. So I used the following DDL to create the View on SQL
>Server:
>USE Prototype
>GO
>IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
> WHERE TABLE_NAME = 'vBackOrder')
> DROP VIEW vBackOrder
>GO
>CREATE VIEW vBackOrder AS
>SELECT TOP 100 PERCENT SalesDetail.invoiceNumber, SalesDetail.invoiceDate,
>SalesDetail.Part, SalesDetail.Price, SalesDetail.QtyOrdered, Inventory.Qty
-
>Inventory.QtyCommitted AS QtyAvailable, Inventory.Description
>SalesDetail.Customer FROM SalesDetail INNER JOIN Inventory ON
>SalesDetail.itemID = Inventory.invItemID WHERE SalesDetail.QtyOrdered >
>SalesDetail.QtyShipped AND Inventory.Qty - Inventory.QtyCommitted > 0 AND
>SalesDetail.BackOrder > 0 ORDER BY SalesDetail.invoiceDate DESC
>GO
>I linked vBackOrder in Access then used this DAO code to populate local
>table BackOrders:
>INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered
,
>QtyAvailable, Description) SELECT vBackOrder.InvoiceNumber,
>vBackOrder.InvoiceDate, vBackOrder.Part, vBackOrder.Price,
>vBackOrder.QtyOrdered, vBackOrder.QtyAvailable, vBackOrder.Description FROM
>vBackOrder WHERE vBackOrder.Customer=x
>This also gives the same error, as does attempting to open the linked
>vBackOrder in Access. I then went to QA and tried just the SELECT portion o
f
>the CREATE VIEW with the same result. To narrow it down, I tried SELECTing
*
>FROM Inventory - a little slow but no problem. I then tried the same thing
>with SalesDetail and again get the same error. I have >300 server tables of
>varying sizes in this app. SalesDetail is by far the largest, and it is the
>*only* one I get the error on. So I'm assuming it has something to do with
>the size.
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:pflv701qqge68qf7u94r6bnetfkb1iuc53@.
4ax.com...
>Analyzer)
>find
>|||Hi Mary,
BTW I have your book and it is very good. Unfortunately, for this project I
am stuck with Access 97 and the book seems to reference Access 2000 +.
Thanks for your help and that not only works but it is *much* faster! Looks
like I'll be retrofitting all of those Views I created! Thanks again!
Regards,
Ron Hinds
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:c7d280ledkacc75n7ugk5jgd8v7bibuepa@.
4ax.com...
> DAO is the problem. You're loading the Jet engine and using it for SQL
> Server data operations, something it was never designed or optimized
> to do. Create a stored procedure instead of a buinch of views. Stored
> procedures support parameters and complex logic, and return a
> read-only result set which you can use to populate your local table.
> Call the stored procedure from a pass-through query where you set the
> SQL syntax to something like this in your code (you can use DAO to set
> properties of a QueryDef object and execute it):
> qdef.SQL = "EXEC myproc 'paramvalue1', val2" etc.
> qdef.Execute
> Pass-through queries bypass the Jet engine when they're executed and
> are the most efficient way of getting back large result sets since all
> of the processing takes place on the server, not in Jet. You then
> create either an Insert or Update query that selects from your
> pass-through query into the local table. When you call the
> insert/update query it will automatically execute the pass-through
> query to get the records.
> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446
> On Fri, 16 Apr 2004 17:09:15 -0700, "Ron Hinds"
> <__NoSpam@.__NoSpamramac.com> wrote:
>
actually[vbcol=seagreen]
fetch[vbcol=seagreen]
QtyOrdered,[vbcol=seagreen]
FROM[vbcol=seagreen]
WHERE[vbcol=seagreen]
SalesDetail.QtyShipped[vbcol=seagreen]
> 0
a[vbcol=seagreen]
SalesDetail.invoiceDate,[vbcol=seagreen]
Inventory.Qty -[vbcol=seagreen]
QtyOrdered,[vbcol=seagreen]
FROM[vbcol=seagreen]
of[vbcol=seagreen]
SELECTing *[vbcol=seagreen]
thing[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
with[vbcol=seagreen]
~1,750,000[vbcol=seagreen]
Sockets]ConnectionCheckForData[vbcol=sea
green]
>|||Although the book is for a newer version, the basic concepts remain
the same, and always will, which are: fetch only needed data and
perform as much data processing on the back end. Let the FE do
presentation tasks like formatting, etc. All of the code in the
chapters for linked tables will work pretty much as-is in Access 97.
--Mary
On Mon, 19 Apr 2004 14:08:04 -0700, "Ron Hinds"
<__NoSpam@.__NoSpamramac.com> wrote:

>Hi Mary,
>BTW I have your book and it is very good. Unfortunately, for this project I
>am stuck with Access 97 and the book seems to reference Access 2000 +.
>Thanks for your help and that not only works but it is *much* faster! Looks
>like I'll be retrofitting all of those Views I created! Thanks again!
>Regards,
>Ron Hinds
>
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:c7d280ledkacc75n7ugk5jgd8v7bibuepa@.
4ax.com...
>actually
>fetch
>QtyOrdered,
>FROM
>WHERE
>SalesDetail.QtyShipped
>a
>SalesDetail.invoiceDate,
>Inventory.Qty -
>QtyOrdered,
>FROM
>of
>SELECTing *
>thing
>of
>the
>with
>~1,750,000
>Sockets]ConnectionCheckForData
>|||Ron,
Switch to an Access 2002 data project and dump the mdb. You are looking at
a complete rewrite. Access 2002 ADPs are VERY EASY to use. Access linked t
ables create multiple connections and are prone to creating deadlocks and mu
ltitudes of other problems.
Linked tables in Access 97 worked 'OK', linked tables in 2000 and on are not
usable in a production system.
"Ron Hinds" wrote:

> I get the following error (from my front-end Access app AND Query Analyzer
)
> when accessing a particular table. It is a rather large table, ~1,750,000
> rows in it. I tried looking through "limitations" in BOL and couldn't find
> anything. Is this a server setting or ODBC setting or... ?
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionC
heckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
>

ODBC drivers for sql server 2005, 64 bit

Hi,
I have a client server app that connects to sql server through ODBC, it
works fine when connecting to sql server 2005 32 bit, but when connecting to
sql server 64 bit from the client on Windows XP it behaves strangely, are
there separate set of odbc drivers that one has to use to connect to sql
server 2005 64 bit, even from 32 bit machine.
Thank you
VadimIt should be transparent to remote clients that the SQL Server is 64-bit.
The same client drivers can be used to connect to a 32 or 64-bit server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vadim" <vadim@.dontsend.com> wrote in message
news:%23MyV57ECHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a client server app that connects to sql server through ODBC, it
> works fine when connecting to sql server 2005 32 bit, but when connecting
> to sql server 64 bit from the client on Windows XP it behaves strangely,
> are there separate set of odbc drivers that one has to use to connect to
> sql server 2005 64 bit, even from 32 bit machine.
> Thank you
> Vadim
>|||Thank you, Dan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:452AD768-B04F-4CD1-A54B-0C15E260ED3A@.microsoft.com...
> It should be transparent to remote clients that the SQL Server is 64-bit.
> The same client drivers can be used to connect to a 32 or 64-bit server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23MyV57ECHHA.5068@.TK2MSFTNGP02.phx.gbl...
>

ODBC drivers for sql server 2005, 64 bit

Hi,
I have a client server app that connects to sql server through ODBC, it
works fine when connecting to sql server 2005 32 bit, but when connecting to
sql server 64 bit from the client on windows xp it behaves strangely, are
there separate set of odbc drivers that one has to use to connect to sql
server 2005 64 bit, even from 32 bit machine.
Thank you
Vadim
It should be transparent to remote clients that the SQL Server is 64-bit.
The same client drivers can be used to connect to a 32 or 64-bit server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vadim" <vadim@.dontsend.com> wrote in message
news:%23MyV57ECHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a client server app that connects to sql server through ODBC, it
> works fine when connecting to sql server 2005 32 bit, but when connecting
> to sql server 64 bit from the client on windows xp it behaves strangely,
> are there separate set of odbc drivers that one has to use to connect to
> sql server 2005 64 bit, even from 32 bit machine.
> Thank you
> Vadim
>
|||Thank you, Dan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:452AD768-B04F-4CD1-A54B-0C15E260ED3A@.microsoft.com...
> It should be transparent to remote clients that the SQL Server is 64-bit.
> The same client drivers can be used to connect to a 32 or 64-bit server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23MyV57ECHHA.5068@.TK2MSFTNGP02.phx.gbl...
>

ODBC drivers for sql server 2005, 64 bit

Hi,
I have a client server app that connects to sql server through ODBC, it
works fine when connecting to sql server 2005 32 bit, but when connecting to
sql server 64 bit from the client on windows xp it behaves strangely, are
there separate set of odbc drivers that one has to use to connect to sql
server 2005 64 bit, even from 32 bit machine.
Thank you
VadimIt should be transparent to remote clients that the SQL Server is 64-bit.
The same client drivers can be used to connect to a 32 or 64-bit server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Vadim" <vadim@.dontsend.com> wrote in message
news:%23MyV57ECHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a client server app that connects to sql server through ODBC, it
> works fine when connecting to sql server 2005 32 bit, but when connecting
> to sql server 64 bit from the client on windows xp it behaves strangely,
> are there separate set of odbc drivers that one has to use to connect to
> sql server 2005 64 bit, even from 32 bit machine.
> Thank you
> Vadim
>|||Thank you, Dan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:452AD768-B04F-4CD1-A54B-0C15E260ED3A@.microsoft.com...
> It should be transparent to remote clients that the SQL Server is 64-bit.
> The same client drivers can be used to connect to a 32 or 64-bit server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23MyV57ECHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have a client server app that connects to sql server through ODBC, it
>> works fine when connecting to sql server 2005 32 bit, but when connecting
>> to sql server 64 bit from the client on windows xp it behaves strangely,
>> are there separate set of odbc drivers that one has to use to connect to
>> sql server 2005 64 bit, even from 32 bit machine.
>> Thank you
>> Vadim
>sql

Friday, March 23, 2012

ODBC connection to SQL server DB

I'm writing a VB app to read and write data to an SQL server database.

I have successfully done so using ADO however I now need to do it using ODBC.

I have set up the appropriate DSN and have no trouble reading data from the database but when I try to write to it I get a message indicating that the database is open for read only access.

I'm no sure whether the restriction is and the VB, ODBC, or database level.

Here's the code which opens the database and recordset.

Set dbsWarehouseServer = OpenDatabase(ODBCDSName, _
dbDriverNoPrompt, False, _
"DSN=" & ODBCDSName)

Set rstRailSet = dbsWarehouseServer.OpenRecordset("Select Store_date, SLN From " & TBName & " where Rail_set_ID = '" & RailID & "'", dbOpenDynaset)

Any help is greatly appreciated.What if you changed this to your statement:

Set rstRailSet = dbsWarehouseServer.OpenRecordset("Select Store_date, SLN From " & TBName & " where Rail_set_ID = '" & RailID & "'", adOpenDynamic, adLockOptimistic)

Just a thought.|||Thank's for the idea. No luck though.
Not sure but maybe the restriction is at the ODBC or SQL level although I have no trouble with ADO so I suspect ODBC.|||Just wondering...is RailID an integer value?

If so, wouldn't you use:

Set rstRailSet = dbsWarehouseServer.OpenRecordset("Select Store_date, SLN From " & TBName & " where Rail_set_ID = " & RailID & "", dbOpenDynaset)

instead of:

Set rstRailSet = dbsWarehouseServer.OpenRecordset("Select Store_date, SLN From " & TBName & " where Rail_set_ID = '" & RailID & "'", dbOpenDynaset)

Again, just another thought but probably not on the right path.|||When you create the ODBC connection on your pc, which username are you using, I believe that is not 'sa'. You must be using another username created in the SQL server user login.
Check the right for that username you have created. Does it have the right to write to that specify database. If you put it to db owner, you can do anything with that database.

ODBC Connection SQL Server Error

Hello, my VB app fails to connects to a SQL Server.
Server side info
SQL Server is installed in a W2000 PC configured for IIS use. It is not a PDC.
Client side info
W2000, no Access app installed.
Situation:
System DSN created with ODBC config utility works fine and connects succesfully to the server. But when a connection is tried to be made through app it fail: "Login failed, ... user(null)... Not associated with a trusted SQL Server connection"
Any suggestions ?
Thanks
Arnaldo
Arnaldo,
See if this helps
'PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'"'
http://support.microsoft.com/?id=307002
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Arnaldo" <anonymous@.discussions.microsoft.com> wrote in message
news:ADBEE679-C4F6-486C-96CB-1588892D7283@.microsoft.com...
> Hello, my VB app fails to connects to a SQL Server.
> Server side info
> SQL Server is installed in a W2000 PC configured for IIS use. It is not a
PDC.
> Client side info
> W2000, no Access app installed.
> Situation:
> System DSN created with ODBC config utility works fine and connects
succesfully to the server. But when a connection is tried to be made through
app it fail: "Login failed, ... user(null)... Not associated with a trusted
SQL Server connection"
> Any suggestions ?
> Thanks
> Arnaldo
>
>
>

ODBC Connection SQL Server Error

Hello, my VB app fails to connects to a SQL Server.
Server side info
SQL Server is installed in a W2000 PC configured for IIS use. It is not a PD
C.
Client side info
W2000, no Access app installed.
Situation:
System DSN created with ODBC config utility works fine and connects succesfu
lly to the server. But when a connection is tried to be made through app it
fail: "Login failed, ... user(null)... Not associated with a trusted SQL Ser
ver connection"
Any suggestions ?
Thanks
ArnaldoArnaldo,
See if this helps
'PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'"'
http://support.microsoft.com/?id=307002
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Arnaldo" <anonymous@.discussions.microsoft.com> wrote in message
news:ADBEE679-C4F6-486C-96CB-1588892D7283@.microsoft.com...
> Hello, my VB app fails to connects to a SQL Server.
> Server side info
> SQL Server is installed in a W2000 PC configured for IIS use. It is not a
PDC.
> Client side info
> W2000, no Access app installed.
> Situation:
> System DSN created with ODBC config utility works fine and connects
succesfully to the server. But when a connection is tried to be made through
app it fail: "Login failed, ... user(null)... Not associated with a trusted
SQL Server connection"
> Any suggestions ?
> Thanks
> Arnaldo
>
>
>

Wednesday, March 21, 2012

ODBC CONNECTION

I have an app which using Access front end and SQL server backend and ODBC
connection.
My app may access the SQL server backend database without ODBC
configuration.
Is it possible? If yes, where I can investigate the issue.
Any information is great appreciated,
Souris,
By "without ODBC" configuration, do you mean without a DSN?
Yes that's possible. You can find a lot of articles if you
search on dsn-less. This is what's returned from the
Knowledge Base:
http://support.microsoft.com/search/...px?qu=dsn-less
You can find examples of connection strings at:
http://www.carlprothman.net/Default.aspx?tabid=80
-Sue
On Thu, 12 May 2005 00:16:45 -0400, "souris"
<soukkris@.viddotron.com> wrote:

>I have an app which using Access front end and SQL server backend and ODBC
>connection.
>My app may access the SQL server backend database without ODBC
>configuration.
>Is it possible? If yes, where I can investigate the issue.
>Any information is great appreciated,
>Souris,
>

ODBC CONNECTION

I have an app which using Access front end and SQL server backend and ODBC
connection.
My app may access the SQL server backend database without ODBC
configuration.
Is it possible? If yes, where I can investigate the issue.
Any information is great appreciated,
Souris,By "without ODBC" configuration, do you mean without a DSN?
Yes that's possible. You can find a lot of articles if you
search on dsn-less. This is what's returned from the
Knowledge Base:
http://support.microsoft.com/search...spx?qu=dsn-less
You can find examples of connection strings at:
http://www.carlprothman.net/Default.aspx?tabid=80
-Sue
On Thu, 12 May 2005 00:16:45 -0400, "souris"
<soukkris@.viddotron.com> wrote:

>I have an app which using Access front end and SQL server backend and ODBC
>connection.
>My app may access the SQL server backend database without ODBC
>configuration.
>Is it possible? If yes, where I can investigate the issue.
>Any information is great appreciated,
>Souris,
>

Monday, March 19, 2012

ODBC --Call Failed exception in application?

Hi, everyone! I hope I'm putting this in the correct forum... forgive me if I haven't!
I've written a neat little app in Visual C# that connects to an MSAccess 2003 database. Because of the really great functionality of ADO.NET, I was able to circumvent a lot of the concurrent connection limitations of Access by using DataSets - alas, I was only delaying the inevitable transition to SQL Server 2000
Since the internal dataset fills in my C# app are written for OLE, and since I've already configured it to connect to the Access database, I was really hopingthat I could just use a linked table to the exported data on SQL Server. However, when I did just this, I got a "ODBC --Call Failed" unhandled exception in my application when I tried to make a simple data change and save it back to the database - no other descriptive error numbers or anything. It appears to be connecting, as I can navigate records, I just cannot make a single change to them, or add new records.
- The changes I made do not propogate, so the MSKB regarding ODBC and cursors isn't the solution
- I did remember to set a PK when I exported from Access to SQL Server 2000
- It's {most likely?} not a permissions issue; I'm in as Administrator, with the ODBC connection set up to use NT Authentication
- I've got SQL Server 2000 running SP3a as well as the latest version of JET running on my 2k3 Server
Any help would be greatly appreciated!

Hi,

as long as Access doenst have a primry key defined on the table it isn′t able to do an update / insert. Try to open the access database and insert a new row in th linked table, i guess it is greyed. Define a PK on the appriate columns and you′ll be fine.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Jens,
Thanks for the response. Unfortunately, I had already defined the PK, and it's showing up in Access. I can add records/edit changes in the database using the MSAccess UI... it's just when my C# ADO.NET app tries to do connect to the MS Access file that the problems start manifesting.
Any other ideas?
|||

http://support.microsoft.com/?scid=kb;en-us;303257&spid=2509&sid=49

HTH

ODBC --Call Failed exception in application?

Hi,

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

http://support.microsoft.com/?scid=kb;en-us;303257&spid=2509&sid=49

HTH

ODBC and XML

Hi all,
I'm writing an app to export my ODBC connection information to XML format.
There is already a standard File .DSN format, but was wondering if there's
already an XML DTD or XML Schema for DSN's out there. I definitely don't
want to reinvent the wheel, and if I'm able to export my info in a format
that would (presumably) be compatible with other applications in the future,
so much the better.
Thanks,
Michael C.I know that Microsoft has not defined anything in this area, and I
haven't heard of any other ISVs doing this.
Brannon
Michael C wrote:

> Hi all,
> I'm writing an app to export my ODBC connection information to XML format.
> There is already a standard File .DSN format, but was wondering if there's
> already an XML DTD or XML Schema for DSN's out there. I definitely don't
> want to reinvent the wheel, and if I'm able to export my info in a format
> that would (presumably) be compatible with other applications in the futur
e,
> so much the better.
> Thanks,
> Michael C.
>

ODBC and XML

Hi all,
I'm writing an app to export my ODBC connection information to XML format.
There is already a standard File .DSN format, but was wondering if there's
already an XML DTD or XML Schema for DSN's out there. I definitely don't
want to reinvent the wheel, and if I'm able to export my info in a format
that would (presumably) be compatible with other applications in the future,
so much the better.
Thanks,
Michael C.
I know that Microsoft has not defined anything in this area, and I
haven't heard of any other ISVs doing this.
Brannon
Michael C wrote:

> Hi all,
> I'm writing an app to export my ODBC connection information to XML format.
> There is already a standard File .DSN format, but was wondering if there's
> already an XML DTD or XML Schema for DSN's out there. I definitely don't
> want to reinvent the wheel, and if I'm able to export my info in a format
> that would (presumably) be compatible with other applications in the future,
> so much the better.
> Thanks,
> Michael C.
>

ODBC and SQL 2000

We have a MFC app that uses ODBC to connect to the DB. Now let me get down
to the problem. We had a client which keeps saying Invalid object name
'Docs_Main'. And we have another client which works fine. Good ole SQL
profiler time to figure out what's going on. So we run our query and we get
this for the working client:
declare @.P1 int
set @.P1=84
exec sp_prepexec @.P1 output, NULL, N'SELECT
"Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID like
''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
select @.P1
And for the client not working we get
declare @.P1 int
set @.P1=NULL
exec sp_prepexec @.P1 output, NULL, N'SELECT
"Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID like
''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
select @.P1
The only difference appears to be in the set @.P1 = x statement. If that's
set to NULL does that cause problems, and why is that being set to NULL.
The program it's coming from is the same one.
Lance JohnsonWhy should the program explicitly set an output parameter in either case?
You would think it is an output parameter because you're interested in
seeing the output, not because you want to control the input.
"Lance Johnson" <ljohnson@.docs.com> wrote in message
news:eKS5FpMnDHA.644@.TK2MSFTNGP11.phx.gbl...
> We have a MFC app that uses ODBC to connect to the DB. Now let me get
down
> to the problem. We had a client which keeps saying Invalid object name
> 'Docs_Main'. And we have another client which works fine. Good ole SQL
> profiler time to figure out what's going on. So we run our query and we
get
> this for the working client:
> declare @.P1 int
> set @.P1=84
> exec sp_prepexec @.P1 output, NULL, N'SELECT
> "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
like
> ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> select @.P1
>
> And for the client not working we get
> declare @.P1 int
> set @.P1=NULL
> exec sp_prepexec @.P1 output, NULL, N'SELECT
> "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
like
> ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> select @.P1
>
> The only difference appears to be in the set @.P1 = x statement. If that's
> set to NULL does that cause problems, and why is that being set to NULL.
> The program it's coming from is the same one.
>
> Lance Johnson
>|||Well I don't really know what that @.P1 is for, but shouldn't it be the same
for all clients, meaning if one client has a number, another client would
have a number and not NULL. I've looked around for info on these prepare
statements so I could learn what's happening, but these aren't easily
findable and there's no mention in sql books online of them.
Lance Johnson
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:egz6PuMnDHA.3288@.tk2msftngp13.phx.gbl...
> Why should the program explicitly set an output parameter in either case?
> You would think it is an output parameter because you're interested in
> seeing the output, not because you want to control the input.
>
>
> "Lance Johnson" <ljohnson@.docs.com> wrote in message
> news:eKS5FpMnDHA.644@.TK2MSFTNGP11.phx.gbl...
> > We have a MFC app that uses ODBC to connect to the DB. Now let me get
> down
> > to the problem. We had a client which keeps saying Invalid object name
> > 'Docs_Main'. And we have another client which works fine. Good ole SQL
> > profiler time to figure out what's going on. So we run our query and we
> get
> > this for the working client:
> >
> > declare @.P1 int
> > set @.P1=84
> > exec sp_prepexec @.P1 output, NULL, N'SELECT
> > "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
> like
> > ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> > select @.P1
> >
> >
> >
> > And for the client not working we get
> >
> > declare @.P1 int
> > set @.P1=NULL
> > exec sp_prepexec @.P1 output, NULL, N'SELECT
> > "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
> like
> > ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> > select @.P1
> >
> >
> >
> > The only difference appears to be in the set @.P1 = x statement. If
that's
> > set to NULL does that cause problems, and why is that being set to NULL.
> > The program it's coming from is the same one.
> >
> >
> >
> > Lance Johnson
> >
> >
>|||> Well I don't really know what that @.P1 is for, but shouldn't it be the
same
> for all clients, meaning if one client has a number, another client would
> have a number and not NULL.
I really don't know enough about your design to agree with you.
> I've looked around for info on these prepare
> statements so I could learn what's happening, but these aren't easily
> findable and there's no mention in sql books online of them.
You could always google;
http://groups.google.com/groups?hl=en&lr=&ie=ISO-8859-1&q=sp_prepexec&btnG=Google+Search|||Hi Lance,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.
From your conservation with Aaron, I understand that you would like to know what the stored
procedure sp_prepexec is and why it cannot appear with the same value on the same
parameter @.p1. Moreover, you want to avoid this and then make both your clients work fine.
Have I fully understood you, Lance? If there is anything I misunderstood, please feel free to
post it in the newsgroup to let me know.
In fact, SP_PREPEXEC is an internal Extended Stored Procedures (XP) that provides server
support for combining an execution plan preparation and executing it into one call. If you set
"Prepared" property in Application, the application might send this command into SQL Server.
From your description, however, I'm unsure of your SQL Server and Service Pack' s version,
whether or not you used some local temp tables and the data types in your table. Detailed
information that you can provide will make things clear and help us move closer to the causes
and resolutions.
However, I'm still eager to assist you with this issue according to my experience on the
SP_PREPEXEC.
First of all, to SQL Server's 7.0, I recommend you apply SP4 that fixed the "Preparing a
Statement That References a Missing Object Incorrectly" problem. You can download this
service pack via:
http://www.microsoft.com/sql/downloads/sp4.asp
Additionally, if you use some local temp tables, the Prepare will fail since we cannot perform
the SELECT statement because the object doesn't exist yet. In this case, you should use
global temp table or a permanent table.
If you use SQL 2000 (with MDAC 2.6 or higher), based on my experience, it will start using
sp_prepexec instead of sp_prepare & sp_execute to implement SQLPrepare and
SQLExecute commands from ODBC, which was done to eliminate round trips to the server
whenever possible.
This is not documented anywhere as this is an implementation done at the server level and
should be transparent to the user. If Prepare first and Execute later were somehow forced, the
Prepare would fail in SQL 2000 as well. In this case, please make sure that your server is able
to Prepare and Execute AT THE SAME TIME.
Lance, does this answer your question? Please feel free to post in the group if this solves your
problem or if you would like further assistance.
Best regards,
Billy Yao
Microsoft Online Partner 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.|||We are currently using SQL 2000 with SP3a. We are not using temp tables.
The tables are regular tables. Let me know if you need more info in this
area. Following is the code that we are using. Some of it is not
explicitly obvious and this is not my code, so I'm just passing it along.
Let me know what else you might need to know.
BOOL CSW_UsersCtrl::DoWeTurnOnSecurity()
{
BOOL SecurityOn = FALSE;
try
{
CSOAPwareSettings theDB(g_DB_USERS);
theDB.m_strFilter = "Item_ID like '";
theDB.m_strFilter += DOCS_SECURITY_COUNT_ID;
theDB.m_strFilter += "'";
try{
theDB.Open();
}catch(CException* e){
DisplayErrorMessage("DoWeTurnOnSecurity #1",e);
return FALSE;
}
...more code but it's failing above
}
So the g_DB_Users is just the access to our database where the table exists.
And the error is coming from the catch statement so an error is being
generated from the call to open theDB.
Lance Johnson
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:HwZRGjTnDHA.1804@.cpmsftngxa06.phx.gbl...
> Hi Lance,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
> From your conservation with Aaron, I understand that you would like to
know what the stored
> procedure sp_prepexec is and why it cannot appear with the same value on
the same
> parameter @.p1. Moreover, you want to avoid this and then make both your
clients work fine.
> Have I fully understood you, Lance? If there is anything I misunderstood,
please feel free to
> post it in the newsgroup to let me know.
> In fact, SP_PREPEXEC is an internal Extended Stored Procedures (XP) that
provides server
> support for combining an execution plan preparation and executing it into
one call. If you set
> "Prepared" property in Application, the application might send this
command into SQL Server.
> From your description, however, I'm unsure of your SQL Server and Service
Pack' s version,
> whether or not you used some local temp tables and the data types in your
table. Detailed
> information that you can provide will make things clear and help us move
closer to the causes
> and resolutions.
> However, I'm still eager to assist you with this issue according to my
experience on the
> SP_PREPEXEC.
> First of all, to SQL Server's 7.0, I recommend you apply SP4 that fixed
the "Preparing a
> Statement That References a Missing Object Incorrectly" problem. You can
download this
> service pack via:
> http://www.microsoft.com/sql/downloads/sp4.asp
> Additionally, if you use some local temp tables, the Prepare will fail
since we cannot perform
> the SELECT statement because the object doesn't exist yet. In this case,
you should use
> global temp table or a permanent table.
> If you use SQL 2000 (with MDAC 2.6 or higher), based on my experience, it
will start using
> sp_prepexec instead of sp_prepare & sp_execute to implement SQLPrepare and
> SQLExecute commands from ODBC, which was done to eliminate round trips to
the server
> whenever possible.
> This is not documented anywhere as this is an implementation done at the
server level and
> should be transparent to the user. If Prepare first and Execute later were
somehow forced, the
> Prepare would fail in SQL 2000 as well. In this case, please make sure
that your server is able
> to Prepare and Execute AT THE SAME TIME.
> Lance, does this answer your question? Please feel free to post in the
group if this solves your
> problem or if you would like further assistance.
>
> Best regards,
>
> Billy Yao
> Microsoft Online Partner 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.
>|||Hello Lance,
Thank you for your feedback! Now it's clear to me that you are using SQL Server 2000 SP3a,
not using any temp tables and there are different users accessing to your DB.
Based on my knowledge, the issue is not related to the code you are using. I agree with you
we can pass it along. In my opinion, it's also not a server side issue.
As I mentioned before, SP_PREPEXEC first PREPARE for the execution plan (can be re-used
later) and then perform the EXECUTE in the future. All the parameters passing processed are
on the client.
That means the provider on the client produces the code (you trace in the original message)
and sends it to the server provider. Therefore, the problem may be located in provider
mismatch between client and server.
According to my experience, you should first check both clients' Microsoft Data Access
Components (MDAC) version to see if it matched the server's MDAC version. I think the
working client can be suited, but it's hard to make sure if the failing client's MDAC is also ok.
Here I provide you a tool from Microsoft to check the MDAC's version for your convenience:
http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-BF53-
14332EF092C9&displaylang=en
Please download it and I provide you step-by-step directions to help check the clients' MDAC
version:
1. Extract the executable file to the WORKING client you want to check
2. In the extracted folder, click cc.exe to run the MDAC check tool
3. In the "Component Check Dialog", select "perform analysis of machine and automatically
determine the release version". It's the default selected option.
4. Click OK on the Dialog and let the tool to check the client's MDAC version
5. Extract the executable file to the FAILING client you want to check and then follow the 2-4
steps above to check another MDAC version on your failing client.
After that, you can compare the version to see if the two clients had different MDAC.
To workaround the issue, I provide you with the following suggestion:
1. Download the working client's MDAC and apply it to the failing client to see if the problem
can be resolved
2. On the other hand, note that your server is SQL Server 2000 SP3, you can also apply MDAC
2.7 SP1 Refresh on your client to meet the provider match.
3. For downloading the MDAC, please reference the following address for detailed
information:
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp
Lance, please apply my suggestion above and let me know if it helps you resolve your
problem. If there is anything more I can assist you with, please feel free to post it in the group.
Thank you for using MSDN newsgroup!
Best regards,
Billy Yao
Microsoft Online Partner 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.|||This was actually one of our first suspicions and we
installed MDAC 2.8 on this machine to make sure it was up
to date. I proceeded to run component checker on this
machine and all was OK it seemed. One more thing to help
in understanding this. The machine this runs on has MSDE
installed and it does the same thing when working with
our DB on that local MSDE or another MSDE/SQL from
another computer. So it's not between 1 particular
server, but for any MSDE server. Thanks for the
information you're provided so far. I hope we can
somehow figure out what's causing this.
Lance Johnson
>--Original Message--
>Hello Lance,
>Thank you for your feedback! Now it's clear to me that
you are using SQL Server 2000 SP3a,
>not using any temp tables and there are different users
accessing to your DB.
>Based on my knowledge, the issue is not related to the
code you are using. I agree with you
>we can pass it along. In my opinion, it's also not a
server side issue.
>As I mentioned before, SP_PREPEXEC first PREPARE for the
execution plan (can be re-used
>later) and then perform the EXECUTE in the future. All
the parameters passing processed are
>on the client.
>That means the provider on the client produces the code
(you trace in the original message)
>and sends it to the server provider. Therefore, the
problem may be located in provider
>mismatch between client and server.
>According to my experience, you should first check both
clients' Microsoft Data Access
>Components (MDAC) version to see if it matched the
server's MDAC version. I think the
>working client can be suited, but it's hard to make sure
if the failing client's MDAC is also ok.
>Here I provide you a tool from Microsoft to check the
MDAC's version for your convenience:
>http://www.microsoft.com/downloads/details.aspx?
FamilyId=8F0A8DF6-4A21-4B43-BF53-
>14332EF092C9&displaylang=en
>Please download it and I provide you step-by-step
directions to help check the clients' MDAC
>version:
>1. Extract the executable file to the WORKING client you
want to check
>2. In the extracted folder, click cc.exe to run the MDAC
check tool
>3. In the "Component Check Dialog", select "perform
analysis of machine and automatically
>determine the release version". It's the default
selected option.
>4. Click OK on the Dialog and let the tool to check the
client's MDAC version
>5. Extract the executable file to the FAILING client you
want to check and then follow the 2-4
>steps above to check another MDAC version on your
failing client.
>After that, you can compare the version to see if the
two clients had different MDAC.
>To workaround the issue, I provide you with the
following suggestion:
>1. Download the working client's MDAC and apply it to
the failing client to see if the problem
>can be resolved
>2. On the other hand, note that your server is SQL
Server 2000 SP3, you can also apply MDAC
>2.7 SP1 Refresh on your client to meet the provider
match.
>3. For downloading the MDAC, please reference the
following address for detailed
>information:
>http://msdn.microsoft.com/library/default.asp?
url=/downloads/list/dataaccess.asp
>Lance, please apply my suggestion above and let me know
if it helps you resolve your
>problem. If there is anything more I can assist you
with, please feel free to post it in the group.
>Thank you for using MSDN newsgroup!
>
>Best regards,
>Billy Yao
>Microsoft Online Partner 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.
>
>.
>|||Hi Lance,
Thank you for your update!
From your description, I know that you have already installed MDAC 2.8 on this machine.
However, I'm unsure what here "this machine" represents for. Does it mean the machine your
server and DB located, or mean the machine your problematic client located?
Looking through your original message in this post, I notice that there were two clients
connecting to the server and fetch the data from your DB. One worked fine and the other failed
with "Invalid object name 'Docs_Main' " error message.
As we discussed before, the provider issue might be not on the server side machine but on
the client side machine. Therefore, I provided the MDAC check tool for your convenience to
distinguish the MDAC version between these two clients. If the versions are different, this may
be the cause that one was successful and the other was failed.
It was recommended that you applied suitable (not latest) MDAC on the problematic client
side machine. This "suitable MDAC" means you can apply "the MDAC version on the working
client" to the failing client.
Another workaround you can perform is to force sp_unprepare explicitly. In this way,
SP_PREPEXEC will not prepare the execution plan and execute the script directly.
Lance, does this answer your question? Please feel free to post in the group if this solves your
problem or if you would like further assistance.
Best regards,
Billy Yao
Microsoft Online Partner 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.|||Sorry. Let me clarify what I mean by that machine. Our problematic client
(let's call it Laptop) has the MDAC 2.8 and we have MSDE installed on it.
It was having this problem, so we tried connecting it to another machine's
MSDE (let's call it Desktop). The same result occurred. So on Desktop we
connected to the 2 different MSDE's and it worked on both. So if the client
is connecting to a local MSDE the MDAC shouldn't (I believe) be getting in
the way. We connected to another machine's SQL just so we could run SQL
Profiler and get more info. Thanks much.
Lance Johnson
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:XIC4jsjnDHA.2012@.cpmsftngxa06.phx.gbl...
> Hi Lance,
> Thank you for your update!
> From your description, I know that you have already installed MDAC 2.8 on
this machine.
> However, I'm unsure what here "this machine" represents for. Does it mean
the machine your
> server and DB located, or mean the machine your problematic client
located?
> Looking through your original message in this post, I notice that there
were two clients
> connecting to the server and fetch the data from your DB. One worked fine
and the other failed
> with "Invalid object name 'Docs_Main' " error message.
> As we discussed before, the provider issue might be not on the server side
machine but on
> the client side machine. Therefore, I provided the MDAC check tool for
your convenience to
> distinguish the MDAC version between these two clients. If the versions
are different, this may
> be the cause that one was successful and the other was failed.
> It was recommended that you applied suitable (not latest) MDAC on the
problematic client
> side machine. This "suitable MDAC" means you can apply "the MDAC version
on the working
> client" to the failing client.
> Another workaround you can perform is to force sp_unprepare explicitly. In
this way,
> SP_PREPEXEC will not prepare the execution plan and execute the script
directly.
> Lance, does this answer your question? Please feel free to post in the
group if this solves your
> problem or if you would like further assistance.
>
> Best regards,
>
> Billy Yao
> Microsoft Online Partner 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.
>|||Hi Lance,
Thank you for your detailed explanation.
Now it's clear that the problem is addressed on the client provider- Laptop as all the clients
could connect the MSDEs except for this problematic one.
Although you apply the MDAC 2.8 on the problematic client, the symptom may also occur with
unexpected causes. The better way to work around this issue is to apply the same MDAC
version of the WORKING client to the f problematic client (Laptop), which ensures the Laptop's
provider to use the working MDAC that can connect to the server successfully.
Please re-check your working client's MDAC version and download THIS MDAC from:
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp
Then uninstall the MDAC 2.8 on the problematic client (Laptop), and install the working client's
MDAC you've downloaded. Moreover, make sure the problematic client's
configuration/environment (mainly related to provider) is the same one as the working client.
Enable them identical if possible.
To uninstall the MDAC and reconfigure it, please refer to the following article:
307255 INFO: Component Checker: Diagnose Problems and Reconfigure MDAC
http://support.microsoft.com/?id=307255
Another workaround I mentioned in the previous message is to force sp_unprepare explicitly.
In this way, SP_PREPEXEC will not prepare the execution plan and perform execution directly.
Lance, please help narrow down the problem and apply my recommendation above to see if
this solves your problem. If there is anything more I can assist you with, please feel free to post
in the group.
Best regards,
Billy Yao
Microsoft Online Partner 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.|||Hey Lance,
For more information, you should close all applications that use ODBC in order for the MDAC
installation to succeed, as all later version of ODBC components will be installed when
applying new MDAC. Applications that use ODBC include Microsoft Internet Information Server
(IIS), Microsoft Systems Management Server, Microsoft Access, and Oracle database
applications.
The following articles help you troubleshoot the MDAC setup and list various versions of
MDAC for the SQL Server ODBC driver:
232060 HOWTO: MDAC Setup Troubleshooting Guide
http://support.microsoft.com/?id=232060
219293 INFO: ODBC, SQL Server, and Jet Versions Shipped with MDAC
http://support.microsoft.com/?id=219293
Please feel free to post in the group if this solves your problem or if you would like further
assistance.
Best regards,
Billy Yao
Microsoft Online Partner 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 can't seem to uninstall the current MDAC. This must refer to an older
Component Checker functionality because when I run my current one with that
/d flag it doesn't let me reconfigure or anything. Since I couldn't do
that, I installer 2.8 MDAC on one of my other machines and it seems to work
fine with my application. I'm a little bit hazy on this sp_unprepare stuff.
Do you have any links about this. We have never had to force this before so
we're not accustomed to doing this.
Lance
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:PcqgbEqnDHA.2088@.cpmsftngxa06.phx.gbl...
> Hi Lance,
> Thank you for your detailed explanation.
> Now it's clear that the problem is addressed on the client provider-
Laptop as all the clients
> could connect the MSDEs except for this problematic one.
> Although you apply the MDAC 2.8 on the problematic client, the symptom may
also occur with
> unexpected causes. The better way to work around this issue is to apply
the same MDAC
> version of the WORKING client to the f problematic client (Laptop), which
ensures the Laptop's
> provider to use the working MDAC that can connect to the server
successfully.
> Please re-check your working client's MDAC version and download THIS MDAC
from:
>
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp
> Then uninstall the MDAC 2.8 on the problematic client (Laptop), and
install the working client's
> MDAC you've downloaded. Moreover, make sure the problematic client's
> configuration/environment (mainly related to provider) is the same one as
the working client.
> Enable them identical if possible.
> To uninstall the MDAC and reconfigure it, please refer to the following
article:
> 307255 INFO: Component Checker: Diagnose Problems and Reconfigure MDAC
> http://support.microsoft.com/?id=307255
> Another workaround I mentioned in the previous message is to force
sp_unprepare explicitly.
> In this way, SP_PREPEXEC will not prepare the execution plan and perform
execution directly.
> Lance, please help narrow down the problem and apply my recommendation
above to see if
> this solves your problem. If there is anything more I can assist you with,
please feel free to post
> in the group.
>
> Best regards,
> Billy Yao
> Microsoft Online Partner 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.
>|||Hi Lance
Thank you for your update. Your feedback proves again that the cause of the problem is on
the problematic machine, not on any other machines.
The MDAC Component Checker worked fine on my side and could uninstall MDAC 2.8
according to KB 307255. It's really strange that it didn't let you reconfigure the version. Well, as
MDAC 2.8 did work fine on another machine, I suggest you re-install the same MDAC 2.8 on
the machine. The reason why we should do this is the pervious MDAC 2.8 may not be applied
properly on the client. Please re-apply it.
As to sp_unprepare, it is an internal Extended Stored Procedures (XPROC) like sp_prepexec,
which the latter provides server support for combining an execution plan preparation and
executing it into one call.
There is no outer resource you can refer to because it's interal. Here I provide you a simple
sample to picture how to apply sp_unprepare explicitly:
/************************* Using sp_prepare/sp_execute
Note applications would not call these directly, but would only get these when opening a
default recordset (client cursor): forward only, read only, rowset size = 1 They must also use
SQLPrepare/SQLExecute (ODBC) or ICommandText::Prepare/ICommandText::Execute
(OLEDB)
If the calls are not prepared, the query will simply be executed normally.
***************************/
use pubs
go
declare @.P1 int set @.P1=NULL exec sp_prepare @.P1 output, N'@.P1 varchar(8)', N'select *
from titles where title_id = @.P1' exec sp_execute @.P1, 'MC2222' exec sp_unprepare @.P1
go
Please run the script above on the problematic client machine and the server machine to see
if it can work fine. If it works fine, you can apply this explicit sp_prepare, sp_execute and
sp_unprepare instead of sp_prepexec.
Please feel free to post in the group if this solves your problem or if you would like further
assistance.
Best regards,
Billy Yao
Microsoft Online Partner 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.|||Re-post the untidy sample
======================
/*************************
Using sp_prepare/sp_execute
Note applications would not call these directly, but would only get these
when opening a default recordset (client cursor): forward only, read only,
rowset size = 1
They must also use SQLPrepare/SQLExecute (ODBC) or
ICommandText::Prepare/ICommandText::Execute
(OLEDB)
If the calls are not prepared, the query will simply be executed normally.
***************************/
use pubs
go
declare @.P1 int
set @.P1=NULL
exec sp_prepare @.P1 output, N'@.P1 varchar(8)', N'select * from titles where
title_id = @.P1'
exec sp_execute @.P1, 'MC2222'
exec sp_unprepare @.P1
go|||According to this link on component checker
Special Considerations for Windows 2000, Windows Me, and Later Versions of
Windows
You can use Component Checker on Windows 2000, Windows Me, and later
versions of Windows to identify the version of MDAC and identify problems
with the installation. However, the Reconfigure MDAC components menu option
does not appear when you run Component Checker on these versions of Windows,
even if you use the /d switch. Because MDAC is installed as part of the core
functionality in Windows 2000, Windows Me, and later versions of Windows, it
is impossible to remove MDAC from the operating system. It is also
impossible to reinstall the current version of MDAC without reinstalling the
entire operating system.
So since I'm running XP Pro on this laptop, I can't do this reconfigure. I
have re-run the MDAC installation and it hasn't seemed to help.
One more note. I couldn't get the query you specified to work but I was
able to get it to work using the sp_prepexec. So what does that mean. ODBC
is throwing the error and we have no idea why? The app is actually
connecting to another db before this one and that seems to work fine. So
where do we go from here. We really can't practically redo all of our
queries as you specified since we have a ton of those and this is only
happening on one client's machine.
Thanks for the info you have provided so far.
Lance
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:8XXFcB7nDHA.2624@.cpmsftngxa06.phx.gbl...
> Hi Lance
> Thank you for your update. Your feedback proves again that the cause of
the problem is on
> the problematic machine, not on any other machines.
> The MDAC Component Checker worked fine on my side and could uninstall MDAC
2.8
> according to KB 307255. It's really strange that it didn't let you
reconfigure the version. Well, as
> MDAC 2.8 did work fine on another machine, I suggest you re-install the
same MDAC 2.8 on
> the machine. The reason why we should do this is the pervious MDAC 2.8 may
not be applied
> properly on the client. Please re-apply it.
> As to sp_unprepare, it is an internal Extended Stored Procedures (XPROC)
like sp_prepexec,
> which the latter provides server support for combining an execution plan
preparation and
> executing it into one call.
> There is no outer resource you can refer to because it's interal. Here I
provide you a simple
> sample to picture how to apply sp_unprepare explicitly:
> /************************* Using sp_prepare/sp_execute
> Note applications would not call these directly, but would only get these
when opening a
> default recordset (client cursor): forward only, read only, rowset size =1 They must also use
> SQLPrepare/SQLExecute (ODBC) or
ICommandText::Prepare/ICommandText::Execute
> (OLEDB)
> If the calls are not prepared, the query will simply be executed normally.
> ***************************/
> use pubs
> go
> declare @.P1 int set @.P1=NULL exec sp_prepare @.P1 output, N'@.P1
varchar(8)', N'select *
> from titles where title_id = @.P1' exec sp_execute @.P1, 'MC2222' exec
sp_unprepare @.P1
> go
> Please run the script above on the problematic client machine and the
server machine to see
> if it can work fine. If it works fine, you can apply this explicit
sp_prepare, sp_execute and
> sp_unprepare instead of sp_prepexec.
>
> Please feel free to post in the group if this solves your problem or if
you would like further
> assistance.
>
> Best regards,
> Billy Yao
> Microsoft Online Partner 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 found my problem. We use system DSNs. However there was an errant entry
in the registry for User DSNs with that same DSN name. This entry did not
show up in User DSNs tab for ODBC so I didn't know it existed. There wasn't
much to it except the default DB, which was set to master. That'll teach me
to not look in profiler at what db is being queried.
Lance Johnson
"Lance Johnson" <ljohnson@.docs.com> wrote in message
news:uJxL659nDHA.2068@.TK2MSFTNGP09.phx.gbl...
> According to this link on component checker
> Special Considerations for Windows 2000, Windows Me, and Later Versions of
> Windows
> You can use Component Checker on Windows 2000, Windows Me, and later
> versions of Windows to identify the version of MDAC and identify problems
> with the installation. However, the Reconfigure MDAC components menu
option
> does not appear when you run Component Checker on these versions of
Windows,
> even if you use the /d switch. Because MDAC is installed as part of the
core
> functionality in Windows 2000, Windows Me, and later versions of Windows,
it
> is impossible to remove MDAC from the operating system. It is also
> impossible to reinstall the current version of MDAC without reinstalling
the
> entire operating system.
> So since I'm running XP Pro on this laptop, I can't do this reconfigure.
I
> have re-run the MDAC installation and it hasn't seemed to help.
> One more note. I couldn't get the query you specified to work but I was
> able to get it to work using the sp_prepexec. So what does that mean.
ODBC
> is throwing the error and we have no idea why? The app is actually
> connecting to another db before this one and that seems to work fine. So
> where do we go from here. We really can't practically redo all of our
> queries as you specified since we have a ton of those and this is only
> happening on one client's machine.
> Thanks for the info you have provided so far.
> Lance
>
> "Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
> news:8XXFcB7nDHA.2624@.cpmsftngxa06.phx.gbl...
> > Hi Lance
> >
> > Thank you for your update. Your feedback proves again that the cause of
> the problem is on
> > the problematic machine, not on any other machines.
> >
> > The MDAC Component Checker worked fine on my side and could uninstall
MDAC
> 2.8
> > according to KB 307255. It's really strange that it didn't let you
> reconfigure the version. Well, as
> > MDAC 2.8 did work fine on another machine, I suggest you re-install the
> same MDAC 2.8 on
> > the machine. The reason why we should do this is the pervious MDAC 2.8
may
> not be applied
> > properly on the client. Please re-apply it.
> >
> > As to sp_unprepare, it is an internal Extended Stored Procedures (XPROC)
> like sp_prepexec,
> > which the latter provides server support for combining an execution plan
> preparation and
> > executing it into one call.
> >
> > There is no outer resource you can refer to because it's interal. Here I
> provide you a simple
> > sample to picture how to apply sp_unprepare explicitly:
> >
> > /************************* Using sp_prepare/sp_execute
> > Note applications would not call these directly, but would only get
these
> when opening a
> > default recordset (client cursor): forward only, read only, rowset size
=> 1 They must also use
> > SQLPrepare/SQLExecute (ODBC) or
> ICommandText::Prepare/ICommandText::Execute
> > (OLEDB)
> > If the calls are not prepared, the query will simply be executed
normally.
> > ***************************/
> > use pubs
> > go
> >
> > declare @.P1 int set @.P1=NULL exec sp_prepare @.P1 output, N'@.P1
> varchar(8)', N'select *
> > from titles where title_id = @.P1' exec sp_execute @.P1, 'MC2222' exec
> sp_unprepare @.P1
> > go
> > Please run the script above on the problematic client machine and the
> server machine to see
> > if it can work fine. If it works fine, you can apply this explicit
> sp_prepare, sp_execute and
> > sp_unprepare instead of sp_prepexec.
> >
> >
> > Please feel free to post in the group if this solves your problem or if
> you would like further
> > assistance.
> >
> >
> > Best regards,
> >
> > Billy Yao
> > Microsoft Online Partner 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.
> >
> >
> >
> >
>|||Thank you Lance for your feedback.
DSNs settings are actually out of my consideration as it seemed from the trace of profile that
the problem appeared with provider issue. The same operation resulted in different value
passing to the same parameter @.p1 and this script was sent by client provider to server
provider.
I'm glad that you yourself find the cause and solve the problem. We learnt a lot from this
specific issue. Thank you for participating newgroup and sharing us so many information and
your own experience on the issue.
Best regards,
Billy Yao
Microsoft Online Partner Support

Monday, March 12, 2012

ODBC and DB-Lib bcp

Question - I have a little app that uses the ODBC Bulk Operations and DB-Lib
bcp functions to load data into a table from variables (bcp_sendrow). It
works great on SQL 2000, but I'm encountering some problems on SQL 6.5.
Basically on the SQL 6.5 box the bcp_init function is returning FAIL instead
of SUCCEED. It works on SQL 2000, so it doesn't appear to be my code, but
I'm not 100% on that either. Anyone know why this might be happening and
what could be done to fix it?
ThanksFigured out the problem. MDAC versioning.
"Mike C#" <xyz@.xyz.com> wrote in message
news:e7s8QiKvGHA.3552@.TK2MSFTNGP03.phx.gbl...
> Question - I have a little app that uses the ODBC Bulk Operations and
> DB-Lib bcp functions to load data into a table from variables
> (bcp_sendrow). It works great on SQL 2000, but I'm encountering some
> problems on SQL 6.5. Basically on the SQL 6.5 box the bcp_init function is
> returning FAIL instead of SUCCEED. It works on SQL 2000, so it doesn't
> appear to be my code, but I'm not 100% on that either. Anyone know why
> this might be happening and what could be done to fix it?
> Thanks
>

ODBC Administrator app fails only on SQL Server

Make sure this isn't a rights-issue
You could reinstall mdac 2.8 or sql server client.

>--Original Message--
>Hello,
>I am on an XP SP1 system and when I use the ODBC
Administrator application
>to establish a new SQL Server DSN the process fails.
>After launching the ODBC Administrator I push the "Add"
button and select
>"SQL Server" from the list of available drivers. When I
then push the
>"Finish" button I am immediately returned to the initial
ODBC Administrator
>window. That is, I am never presented with the dialog
box for configuring
>the SQL Server DSN. If I select a Sybase, MS Access or
Oracle DSN I do get
>the expected configuration dialog box. Under W2000 I get
a dialog box for
>configuring a SQL Server DSN and on a different XP SP1
system I also get the
>configuration dialog box. All of this suggets that
something has gone wrong
>on this particular XP SP1 box. Can anyone tell me what's
happened and how
>to fix this?
>Thanks very much for the help.
>Al Koch
>AlKoch@.MyRealBoxREMOVEALLTHESECHARS.com
>
>.
>
Thanks for the reply. It wasn't a right issue since I was logged in as an
Admin. However your suggestion to reinstall MDAC fixed the problem I a
curious though, are you aware of what might have triggered the loss of
(only) the SQL Server ODBC facility?
Thanks,
Al
|||I can only guess
-Sql2000 has been updated to SP3a
(Perhaps the old driver was one for sql server 7)
-WindowsXP had a fair share of updates as well.
-Perhaps a anti-virusprogram was running during install of
mdac/updates

>--Original Message--
>Thanks for the reply. It wasn't a right issue since I
was logged in as an
>Admin. However your suggestion to reinstall MDAC fixed
the problem I a
>curious though, are you aware of what might have
triggered the loss of
>(only) the SQL Server ODBC facility?
>Thanks,
>Al
>
>.
>
|||Thanks for the possible causes and thanks again for the help!
Al
"gandalf" <anonymous@.discussions.microsoft.com> wrote in message
news:307801c4b03d$6f50dac0$a301280a@.phx.gbl...
> I can only guess
> -Sql2000 has been updated to SP3a
> (Perhaps the old driver was one for sql server 7)
> -WindowsXP had a fair share of updates as well.
> -Perhaps a anti-virusprogram was running during install of
> mdac/updates
>

ODBC Administrator app fails only on SQL Server

Make sure this isn't a rights-issue
You could reinstall mdac 2.8 or sql server client.

>--Original Message--
>Hello,
>I am on an XP SP1 system and when I use the ODBC
Administrator application
>to establish a new SQL Server DSN the process fails.
>After launching the ODBC Administrator I push the "Add"
button and select
>"SQL Server" from the list of available drivers. When I
then push the
>"Finish" button I am immediately returned to the initial
ODBC Administrator
>window. That is, I am never presented with the dialog
box for configuring
>the SQL Server DSN. If I select a Sybase, MS Access or
Oracle DSN I do get
>the expected configuration dialog box. Under W2000 I get
a dialog box for
>configuring a SQL Server DSN and on a different XP SP1
system I also get the
>configuration dialog box. All of this suggets that
something has gone wrong
>on this particular XP SP1 box. Can anyone tell me what's
happened and how
>to fix this?
>Thanks very much for the help.
>Al Koch
>AlKoch@.MyRealBoxREMOVEALLTHESECHARS.com
>
>.
>Thanks for the reply. It wasn't a right issue since I was logged in as an
Admin. However your suggestion to reinstall MDAC fixed the problem I a
curious though, are you aware of what might have triggered the loss of
(only) the SQL Server ODBC facility?
Thanks,
Al|||I can only guess
-Sql2000 has been updated to SP3a
(Perhaps the old driver was one for sql server 7)
-WindowsXP had a fair share of updates as well.
-Perhaps a anti-virusprogram was running during install of
mdac/updates

>--Original Message--
>Thanks for the reply. It wasn't a right issue since I
was logged in as an
>Admin. However your suggestion to reinstall MDAC fixed
the problem I a
>curious though, are you aware of what might have
triggered the loss of
>(only) the SQL Server ODBC facility?
>Thanks,
>Al
>
>.
>|||Thanks for the possible causes and thanks again for the help!
Al
"gandalf" <anonymous@.discussions.microsoft.com> wrote in message
news:307801c4b03d$6f50dac0$a301280a@.phx.gbl...
> I can only guess
> -Sql2000 has been updated to SP3a
> (Perhaps the old driver was one for sql server 7)
> -WindowsXP had a fair share of updates as well.
> -Perhaps a anti-virusprogram was running during install of
> mdac/updates
>

ODBC & Multi-threading

I'm working on a C# (2005) app that requires shooting a large number of queries via Odbc, wait until the last one is done and then compute an overall result.

Since the queries are not interdependent, I would like to get them to run concurrently on multiple threads.

I did manage to get several queries run on different threads but the response time for the same query varies dramatically when another query is running concurently (even on a different ODBC connection). All is as if something was queing those queries regardless of the threads and/or the connection objects being used.

Basically when I run a very simple query q1 (thread 1 connection1) on its own, the result is instantaneous, but if I first start a big query q2 (thread 2, connection 2) and then q1 while q2 is running q1 takes forever.

Is it possible at all (for example, does the Odbc driver support being used that way)

Any suggestion as to how to handle that problem would be appreciated.

I do not see why it could not be done. Miltithreading is basically the same as multiple connections from different clients at same time and it is definitely allowed. First it is quite possible that your first thread blocks another one until it finishes execution. In this case second block is doing nothing and just waits. Another potential issue is your big query is so *heavy* for the server that sever just uses 100% of its resources for this query. But I am doubt that this is the case|||

Are you using (nolocks) on your tables?

Select * from Customers (nolock)

Where yada yada yada

If you are not, one query is locking the table until the connection closes.

This is how it works:

1. Query1 --> Select * from Customers

Customers Table is locked until connection closes|||

Yes, I have no doubt that from a server standpoint there is no problem.

The question was more whether multiple queries running simultaneously from the same client machine may be a problem.

For example what if the ODBC driver queues the queries.

I read somewhere that depending on the provider some drivers may or may not support multithreading but have no ide whether it's true or if it has anything to do with my problem.

Thanks anyway,

|||

Adamus,

Good thought, it may be the cause of my problem.

I did try shooting the queries at different tables and it seemed to work fine.

a- Now, for the (nolock); I believe this is a SQL Server specific syntax, do you know if there's a generic way to issue a 'dirty read' through ODBC without being tied to the syntax of particular vendor/DB ?

Would starting a transaction from the ODBCConnection used for the command and set its isolationlevel to 'UncommitedRead' work ?

b- Assuming there's a way of doing a dirty read. Any suggestion as to how best handle the following ? :

- Do first select and get a list of values

- For each of the values issue a scalar command and get the result

- Once all the results are collected, do something with them and display the final result.

The trick of course is to create as many threads are scalar commands to be issued since it would improve perfs to run them in parallel. But it has to be done dynamically since we don't know how many we'll get. Then we need the ability to tie everything back together. Not exactly obvious to do, at least to me.

Thanks,

|||

ODBC? Kind of unfamiliar ground for me unfortunately. (I'm more of a SQL guy)The first thing that comes to mind is .LockType

...but good luck :)

Adamus

|||

.Locktype ? Where did you find that property ? On which class ?

I can't find it in the doc.

Thanks,

|||

LockType = adLockOptimistic

google will give you some good material on this.

http://support.microsoft.com/default.aspx/kb/281998

Adamus

ODBC & Multi-threading

I'm working on a C# (2005) app that requires shooting a large number of queries via Odbc, wait until the last one is done and then compute an overall result.

Since the queries are not interdependent, I would like to get them to run concurrently on multiple threads.

Is it possible at all (for example, does the Odbc driver support being used that way)

Any suggestion as to how to handle that problem would be appreciated.

Are you using (nolocks) on your tables?

Select * from Customers (nolock)

Where yada yada yada

If you are not, one query is locking the table until the connection closes.

This is how it works:

1. Query1 --> Select * from Customers

Customers Table is locked until connection closes|||

Yes, I have no doubt that from a server standpoint there is no problem.

The question was more whether multiple queries running simultaneously from the same client machine may be a problem.

For example what if the ODBC driver queues the queries.

I read somewhere that depending on the provider some drivers may or may not support multithreading but have no ide whether it's true or if it has anything to do with my problem.

Thanks anyway,

|||

Adamus,

Good thought, it may be the cause of my problem.

I did try shooting the queries at different tables and it seemed to work fine.

Would starting a transaction from the ODBCConnection used for the command and set its isolationlevel to 'UncommitedRead' work ?

b- Assuming there's a way of doing a dirty read. Any suggestion as to how best handle the following ? :

- Do first select and get a list of values

- For each of the values issue a scalar command and get the result

- Once all the results are collected, do something with them and display the final result.

Thanks,

|||

ODBC? Kind of unfamiliar ground for me unfortunately. (I'm more of a SQL guy)The first thing that comes to mind is .LockType

...but good luck :)

Adamus

|||

.Locktype ? Where did you find that property ? On which class ?

I can't find it in the doc.

Thanks,

|||

LockType = adLockOptimistic

google will give you some good material on this.

http://support.microsoft.com/default.aspx/kb/281998

Adamus

Friday, March 9, 2012

Occasionally slow to connect to a sql2005 database

We have a VB app talking to SQL 2000 and SQL2005 databases. Most of our clients use SQL2000, but the new ones we are deploying with SQL2005

Sometimes the app is slow in establishing a connection to the database when it is SQL2005. The provider is sqloledb. We never get this delay in SQL2000. Sometimes it takes seconds to establish a connection - this is even the case when the app is on the same machine as the database.

We are using SQL authentication - always with the same connect string.

Any ideas ?

thanks
Bruce

Any chance of a reply on this one ?

Thanks

Bruce

|||

I have the same problem, Win32::SqlServer perl-module to connect to MS Sql Server 2005 in some cgi-scripts.

They run on the same server as the DBMS.

It must be related to the authentication method -- it is slow when the username/password is set explicitly and hence SQL authentication is forced.

If I run the scripts manually with Integrated Security (default) it connects in no time (give or take a few microsec.). Unfortunately this is not possible when run through IIS which I guess is because it doesn′t have the required permissions.

So... is it possible to give permission on a file-basis (in this case a cgi-script) so that an IIS-initiated process can access MS Sql Server with integrated seciruty?

This may not be the solution to original posters problem but it might give a hint in the right direction.

|||Same here, VB app connects to SQL Express using SQL Authentication. Using an identical connection string each time I get intermittent occurence of this slow/not responding symptoms.

It can sit and wait for a simple query to execute for maybe 30 secs or more in many cases. I had thought the Visual Studio Process had been hogging resources but this has now occured in our release builds (But not as often). This problem is replicated across our development machines.

AutoClose is set to False
AutoShrink is set to False

The query has only failed to execute on one occasion, unfortunately on a customers machine where presumably the query had timed-out. In most other cases the query has executed fine but after an impossibly long wait.

Is the server idling? If so how can I best test for the idle condition or alter the parameters for idle condition.

Cheers
R

Friday, March 30, 2012

ODBC Error

ODBC drivers for sql server 2005, 64 bit

ODBC drivers for sql server 2005, 64 bit

ODBC drivers for sql server 2005, 64 bit

Friday, March 23, 2012

ODBC connection to SQL server DB

ODBC Connection SQL Server Error

ODBC Connection SQL Server Error

Wednesday, March 21, 2012

ODBC CONNECTION

ODBC CONNECTION

Monday, March 19, 2012

ODBC --Call Failed exception in application?

ODBC --Call Failed exception in application?

ODBC and XML

ODBC and XML

ODBC and SQL 2000

Monday, March 12, 2012

ODBC and DB-Lib bcp

ODBC Administrator app fails only on SQL Server

ODBC Administrator app fails only on SQL Server

ODBC & Multi-threading

ODBC & Multi-threading

Friday, March 9, 2012

Occasionally slow to connect to a sql2005 database

Objects related to DB

Blog Archive

About Me