We created an ODBC datasource on a SQL Server in the cluster. Then there was
a hardware failover and subsequently unrelated DTS processes failed because
the data source wasn't defined on the failover machine. Even though those
processes did not use the ODBC source, in fact nothing used the ODBC source,
it was just created to be used in testing.
So, how do you create an ODBC datasouce that will be cluster-wide and not
just machine specific? Do you have to do it through the Cluster Management
console?
Thanks,
Bob
ODBC DSN's are not services and not applications; they are specifications,
typically stored in the registry. If you need it, just like mapped
networked drives, then you will need to create them separately on each
cluster node that requires them. There is no way to cluster them.
However, if you switched to using Data Links or ODBC file-based DSNs, then
you could store them on one of the shared clustered drives. In this case,
they would failover with the group, retain the same drive letter and path;
so, they would always be present with the virtual server.
On to another topic. Although we allow retaining the DTS package in the
MSDB repository and logging to that package, we do not allow the execution
of DTSRun on the DBMS servers. We require our clients to execute those on
an Application Server.
We try to minimize the number of external processes that run alongside the
DBMS memory space in order to maximize performance and system stability.
The last thing we want is for an external process to foul connected to an
internal SQL Server thread.
Sincerely,
Anthony Thomas
"Bob C" <BobC@.discussions.microsoft.com> wrote in message
news:65CC8979-C05A-43A9-92D6-9B156958E8CD@.microsoft.com...
> We created an ODBC datasource on a SQL Server in the cluster. Then there
was
> a hardware failover and subsequently unrelated DTS processes failed
because
> the data source wasn't defined on the failover machine. Even though those
> processes did not use the ODBC source, in fact nothing used the ODBC
source,
> it was just created to be used in testing.
> So, how do you create an ODBC datasouce that will be cluster-wide and not
> just machine specific? Do you have to do it through the Cluster Management
> console?
> Thanks,
> Bob
Showing posts with label failed. Show all posts
Showing posts with label failed. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
ODBC connection to remote SQL srver failed
Hi all,
Here is the scenario.
I installed SQL server 2000 on a NT server which already has SQL
server 6.5. This is because I want to upgrade the 6.5 to 2K. The 6.5
is the default instance, and the 2K is in a named instance.
Everything went well up to this point. I can bring up both instances
and transfer database over.
Now, the 6.5 server has been brought down due to not in use. I have a
Access mdb file with linked tables to the SQL 2K server by using a
created file DSN in ODBC manager. Everything works fine. I can open
tables and see data. But if I try to open the same Access mdb file
from remote machines, Win 95s & Win 98s, (using shortcuts points to it
on the NT machine), I get error "ODBC connection to SQL server
<my2Kserver>\<my2Kinstance> failed".
So, opening it from same machine works, opening it from remote does
not. Any ideas? By the way, I tested the network connection and it
works. Becuase I can see the shared drive and files of NT server from
other remote machines.
HansHello Hans,
H> So, opening it from same machine works, opening it from remote does
H> not. Any ideas? By the way, I tested the network connection and it
H> works. Becuase I can see the shared drive and files of NT server
H> from other remote machines.
I think, your 95's or 98's account have not enough access rights for file
operations with MDB files on the server.
Igor Shekalev, http://www.sqledit.com, SQL editor with ODBC support|||As noted above, since you are using an ODBC file DSN, check to make sure you
r client systems have read permissions on the file DSN folder.
Another thing you might try doing is updating to the latest version of MDAC
on all of your client systems that will be using the SQL Server. I know tha
t I've had serious connectivity issues with Win95 and Win98 systems to my SQ
L 2K server before installing the latest updates, particularly after the app
earance of the SQL Slammer virus last year, since MSSQL SP3 significantly ch
anged the connection protocol that SQL Server uses.
You can find the installation information for MDAC 2.7 here:
http://support.microsoft.com/defaul...10&Product=mdac
Note that MDAC 2.7 is the version that is installed with SQL Server 2000 SP3
.
If you want the latest and greatest, MDAC 2.8 is also available.
http://www.microsoft.com/downloads/...&displaylang=en
Hope this helps.
Jimsql
Here is the scenario.
I installed SQL server 2000 on a NT server which already has SQL
server 6.5. This is because I want to upgrade the 6.5 to 2K. The 6.5
is the default instance, and the 2K is in a named instance.
Everything went well up to this point. I can bring up both instances
and transfer database over.
Now, the 6.5 server has been brought down due to not in use. I have a
Access mdb file with linked tables to the SQL 2K server by using a
created file DSN in ODBC manager. Everything works fine. I can open
tables and see data. But if I try to open the same Access mdb file
from remote machines, Win 95s & Win 98s, (using shortcuts points to it
on the NT machine), I get error "ODBC connection to SQL server
<my2Kserver>\<my2Kinstance> failed".
So, opening it from same machine works, opening it from remote does
not. Any ideas? By the way, I tested the network connection and it
works. Becuase I can see the shared drive and files of NT server from
other remote machines.
HansHello Hans,
H> So, opening it from same machine works, opening it from remote does
H> not. Any ideas? By the way, I tested the network connection and it
H> works. Becuase I can see the shared drive and files of NT server
H> from other remote machines.
I think, your 95's or 98's account have not enough access rights for file
operations with MDB files on the server.
Igor Shekalev, http://www.sqledit.com, SQL editor with ODBC support|||As noted above, since you are using an ODBC file DSN, check to make sure you
r client systems have read permissions on the file DSN folder.
Another thing you might try doing is updating to the latest version of MDAC
on all of your client systems that will be using the SQL Server. I know tha
t I've had serious connectivity issues with Win95 and Win98 systems to my SQ
L 2K server before installing the latest updates, particularly after the app
earance of the SQL Slammer virus last year, since MSSQL SP3 significantly ch
anged the connection protocol that SQL Server uses.
You can find the installation information for MDAC 2.7 here:
http://support.microsoft.com/defaul...10&Product=mdac
Note that MDAC 2.7 is the version that is installed with SQL Server 2000 SP3
.
If you want the latest and greatest, MDAC 2.8 is also available.
http://www.microsoft.com/downloads/...&displaylang=en
Hope this helps.
Jimsql
Wednesday, March 21, 2012
ODBC connection failed, but...
Running a database using sql server. User, rights, password set up on the server side and is functioning fine on the users normal desktop. But here is the problem. When the user goes to a different desktop they have never been on before and a new user profile is created on that desktop, they are unable to access the database. When using the front end (access), an odbc connection failure popup occurs when trying to retrieve data. The wierd part is that the odbc connection was set up under admin tools just like it is setup on the working desktop. Also the connection tests successfully. This leads me to believe that it is a desktop side problem and not server side. It is only a problem when a new user profile is setup on a desktop that the user has never been on before.
Any help would be appreciated.
Barry
What is the error the user is getting from the failing desktop - does it say "SQL Server does not exist or access denied"?
Is the SQL Server installed as a default instance listening on the default TCP port 1433 (as opposed to a named instance or a non-default port)?
If the answers are "yes" and "yes", try running
telnet <serverName> 1433
If it reports an error there is an underlying network connectivity problem between the desktop and the server.
If it gives a blank screen the netowkr connectivity works, and further information about your environment and the error may be needed.
You can also follow the checklist posted at
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1
|||
Under odbc datasource manager sql setup the libraries is named pipes and the pipes are Serv01and server is serv01, and alias is serv01 as they should be. TCP/IP is Server Serv01 and port is dynamically determined. The exact same setup works under my windows login for myself to access the databse, but will not work when logging in my secretary on my laptop. By the way her name and password are setup correct in the odbc datasource manager. She is able to access the database from her normal workstation though.
The only error we get is odbc connection failure when accessing from the front end at my station. By the way, if I login to her station and go through the sql setup under the odbc manager, I have the same problem at her station that she has at mine.
I'll follow the checklist you posted to see if it helps, but it has to be a local software problem. We'll see though.
Thanks
|||Nothing like solving your own problem. Well it appears that the naming under the odbc setup manager is highly specific and is not just a name. The problem was that when it asked for a name during setup, "Training Manager" was entered instead of "TrainingManager" without the space. I did not realize that there was connectivity value associated with the name at that point in the setup. The "test connectivity" at the end of the setup will say "successful" with the name written either way, but actually connection to and retrieval of info from the database from front end software will fail.
Hope this helps someone else down the road.
|||I got the same problem, I am using 2003 server with SQL server 2000. With the same user, I can logon into server but can't create odbc connection beside "SA" login with blank password. This problem happened on our desktop too. Moveover, this problem extend to the application server "Great Plain - Dynamics 9.0". If the user was created on the server side, user can not login properly, unless user's password to be re-entered on the desktop(Windows XP).
I am woundering is the problem caused by encoding issue (Unicode or ASCII). Is there anyone can help?
sql
ODBC -Connection failed
I create a Test database in SQL server, also I add new user in security folder. Also I add this user in Test database, the permission are: Public, and db_owner. But when I let the user try to open the database, it is ODBC Connection failed. I add another user a few months ago, she works fine. But the new user I added in didnt work, did you know why. I am sure I add the same permission as the later user. Thanks.Even though you added the user at the database level and gave it the different access roles (public and db_owner) - make sure you gave the user Database Access to from the Security/Logins dialog box. On the 3rd tab (Database Access) make sure the Test database is checked.
That might be your problem.
Alex|||Alex, it's impossible to have the user be enrolled into db_owner while not having the user to have a Database Access. Something else is fishy/not clear...
That might be your problem.
Alex|||Alex, it's impossible to have the user be enrolled into db_owner while not having the user to have a Database Access. Something else is fishy/not clear...
ODBC Connection failed
I am having an issue connecting to a SQL server under
windows Xp and office 2003. This is the error it gives.
****
Connection Failed
SQL State : 'HY000'
SQL Server error : 0
[Microsoft][ODBC SQL server driver]Cannot generate SSPI
content
****
Connecting to the same Database on the SQL server works
perfect on a Windows 2000 machine, running Office XP.
Any Ideas?
Thanks,
AndyWinXP Pro to SQL Server 2000 on Win2000 server: I'm having this problem also
, after about an hour. It works fine for that long then fails.
Connection failed:
SQLState 'HY000
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver] Cannot generate SSPI context
Logout and back in has no effect, only rebooting clears the problem which th
en works fine for another hour or so. Not much time to get any serious codin
g and testing done.
windows Xp and office 2003. This is the error it gives.
****
Connection Failed
SQL State : 'HY000'
SQL Server error : 0
[Microsoft][ODBC SQL server driver]Cannot generate SSPI
content
****
Connecting to the same Database on the SQL server works
perfect on a Windows 2000 machine, running Office XP.
Any Ideas?
Thanks,
AndyWinXP Pro to SQL Server 2000 on Win2000 server: I'm having this problem also
, after about an hour. It works fine for that long then fails.
Connection failed:
SQLState 'HY000
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver] Cannot generate SSPI context
Logout and back in has no effect, only rebooting clears the problem which th
en works fine for another hour or so. Not much time to get any serious codin
g and testing done.
odbc connection call failed
I moved a sql 2000 database to a sql 2005 server. I have a front end in access 2003. I manually created an ODBC data source system DSN using the ODBC Data Source Administrator just like it was on the other server. I can connect fine but users are saying their getting an odbc call fail. I created the connection and relinked the tables to the new server with the moved database. I completed the same task with another database and the user can connect fine. What could be the problem?
You will have to look at the additional information of the ODBC call / error message. I guess they are not priviledged to access the database or even connct to the server.Jens K. Suessmeyer.
http://www.sqlserver2005.de
Monday, March 19, 2012
ODBC Connect Failed in SQL Job
I am running SQL Server 2000 sp4 in Win Server 2003. I have a DTS
packages which connects to FileMaker 6 files via the ODBC driver
installed with FielMaker.
The DTS packages work correctly when
executed directly. However, when executed from a SQL Server Agent job,
the same DTS packages fail. Always with the same message:
Error: -2147467259 (80004005);
Provider Error: 16394 (400A)
Error string: [FileMaker][ODBC FileMaker Pro driver][FileMaker Pro]Connect failed
Error source: Microsoft OLE DB Provider for ODBC Drivers
Searching
for info on the above message seems to indicate a permissions problem.
However, I am running the jobs with an full administrator account. All
connections and the FielMaker files reside on the one server.
Any help on this would be greatly appreciated.
Thank you.
The error you saw is very general, the message indicated a connection fail, so please provide your connection string, and try to connect to your sql instance by simply use osql.exe, if failed, that means the basic connection did not work on your server, otherwise, the error triggered during data operation, then you need sql profile to track which client operation cause server close connection.
Thanks!
Ming.
ODBC-- call failed when using VPN
Hello all.
Please excuse my lack of poper terminology when describing my issue to you, as I am very new to databases.
I have set up a SQL Server database which connects via MS Access. The application works fine, except when trying to connect through VPN.
Through System DSN, I changed the server name (FSERVER) to the server IP address, and test completed successfully. I am also able to ping the server when connected via VPN. However, when I try to start the application, I receive the following error after several seconds:
ODBC-- call failed.
That's it. No other information about the error. I read somewhere to make sure the MDAC versions are the same, which they are.
As I say, I am quite new to this, but would really appreciate any guidance to help me through.
Thanks in advance!
NilsWithout knowing quite a bit about your network, I can't give you a 100% sure-fire answer. My first guess would be that the DSN is set to use some protocol other than TCP/IP in the DSN Client Configuration and your VPN doesn't route that protocol. My next guess would be that your Network/VPN aren't configured to cooperate for name resolution.
Getting more error information than just "ODBC-- call failed." would help. That's not much for me to go on!
-PatP|||Thank you for the reply, Pat. Do you know of a way to receive a more verbose error than just "call failed"?
Thanks!
Nils|||That's a hard question to answer, it just begs for a smart-alec answer and that wouldn't help you at all.
The problem is most likely in the application/script itself. It apparently does a very poor job of reporting errors, and fails to give you critical information that would help you diagnose and fix this kind of problem.
I'd suggest that you start by solving a simpler problem, then use what yo learn from that process to help solve this problem. Use another application (with better diagnostic information) to open the ODBC DSN while using VPN. It should give you more information about the failure, which will help you (or us) to resolve the actual problem.
I'd start by using Excel, and use the Data | Import External Data | New Database Query menu item. This will allow you to open the DSN, which will probably throw some kind of error immediately. Just work through the process until you get an error message that tells you why the connection fails. I'd bet that the same problem is affecting your custom application, and once you fix the problem for one application, you'll fix it for them all.
-PatP
Please excuse my lack of poper terminology when describing my issue to you, as I am very new to databases.
I have set up a SQL Server database which connects via MS Access. The application works fine, except when trying to connect through VPN.
Through System DSN, I changed the server name (FSERVER) to the server IP address, and test completed successfully. I am also able to ping the server when connected via VPN. However, when I try to start the application, I receive the following error after several seconds:
ODBC-- call failed.
That's it. No other information about the error. I read somewhere to make sure the MDAC versions are the same, which they are.
As I say, I am quite new to this, but would really appreciate any guidance to help me through.
Thanks in advance!
NilsWithout knowing quite a bit about your network, I can't give you a 100% sure-fire answer. My first guess would be that the DSN is set to use some protocol other than TCP/IP in the DSN Client Configuration and your VPN doesn't route that protocol. My next guess would be that your Network/VPN aren't configured to cooperate for name resolution.
Getting more error information than just "ODBC-- call failed." would help. That's not much for me to go on!
-PatP|||Thank you for the reply, Pat. Do you know of a way to receive a more verbose error than just "call failed"?
Thanks!
Nils|||That's a hard question to answer, it just begs for a smart-alec answer and that wouldn't help you at all.
The problem is most likely in the application/script itself. It apparently does a very poor job of reporting errors, and fails to give you critical information that would help you diagnose and fix this kind of problem.
I'd suggest that you start by solving a simpler problem, then use what yo learn from that process to help solve this problem. Use another application (with better diagnostic information) to open the ODBC DSN while using VPN. It should give you more information about the failure, which will help you (or us) to resolve the actual problem.
I'd start by using Excel, and use the Data | Import External Data | New Database Query menu item. This will allow you to open the DSN, which will probably throw some kind of error immediately. Just work through the process until you get an error message that tells you why the connection fails. I'd bet that the same problem is affecting your custom application, and once you fix the problem for one application, you'll fix it for them all.
-PatP
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!
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?
|||
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, 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!
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?
|||
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 - weird behavior
Hello,
I have been working on updating an Access 2000 application that uses linked
ODBC tables to connect to an SQL Server 2000. My development machine is
connected to the server over a Microsoft LAN, at 100Mbits.
Both the Office 2000 on my development machine and the SQL server has all
the latest SP's
Everything worked fine for months until today. I am not aware of any changes
to ether machine since yesterday.
Now, there is one table that can not be opened any more.
I can access all the other tables ecxept that one. When I try to open it I
get the message "ODBC call failed". The table is displayed but with #Name?
in all columns for all records.
Once that has happened I get the same message for all tables. In addition to
"ODBC call failed" there is also
"[Microsoft][ODBC SQL Server Driver][Communication link failure
(#0)".
When I close down and reopen the app I can again access all tables except
the bad one.
At first, I thought the table had gotten corrupted, but I can open the table
just fine on the server. If I set up an ODBC on the server with the same
name I and run my application there. the problem table opens fine.
I removed the ODBC and set it up again with the same properties, client uses
TCP/IP network library, and I am using SQL Server authentication.
I am using MDAC 2.6 SP2, and I did a refresh, but it did not solve problem.
I ran
I am wondering if I should update to 2.8
If anyone has a suggestion I would appreciate it very much.
RagnarIt could be an MDAC issue. You could run the MDAC component
checker on a machine where the app works and another machine
where the app gets the ODBC error and see if there are any
differences in MDAC versions. You can get component checker
from:
http://msdn.microsoft.com/data/down...ls/default.aspx
You could also turn on ODBC tracing then reproduce your
error and then turn tracing off again. You could open up the
ODBC trace log, look for specifics of what's failing and try
to track down the issue from there.
-Sue
On Wed, 10 Mar 2004 19:05:29 -0500, "Ragnar Midtskogen"
<ragnar_ng@.optonline.net> wrote:
>Hello,
>I have been working on updating an Access 2000 application that uses linked
>ODBC tables to connect to an SQL Server 2000. My development machine is
>connected to the server over a Microsoft LAN, at 100Mbits.
>Both the Office 2000 on my development machine and the SQL server has all
>the latest SP's
>Everything worked fine for months until today. I am not aware of any change
s
>to ether machine since yesterday.
>Now, there is one table that can not be opened any more.
>I can access all the other tables ecxept that one. When I try to open it I
>get the message "ODBC call failed". The table is displayed but with #Name?
>in all columns for all records.
>Once that has happened I get the same message for all tables. In addition t
o
>"ODBC call failed" there is also
>"[Microsoft][ODBC SQL Server Driver][Communication link failure
(#0)".
>When I close down and reopen the app I can again access all tables except
>the bad one.
>At first, I thought the table had gotten corrupted, but I can open the tabl
e
>just fine on the server. If I set up an ODBC on the server with the same
>name I and run my application there. the problem table opens fine.
>I removed the ODBC and set it up again with the same properties, client use
s
>TCP/IP network library, and I am using SQL Server authentication.
>I am using MDAC 2.6 SP2, and I did a refresh, but it did not solve problem.
>I ran
>I am wondering if I should update to 2.8
>If anyone has a suggestion I would appreciate it very much.
>Ragnar
>|||Thank you Sue,
I had run ComponentChecker on my development machine and it now has 2.6 SP2
Refresh wih a couple of files being slightly newer than expected, ODBC32.dll
and ODBCCP32.dll.
The Server had 2.7 SP1 Refresh with about 8 files slightly newer than
expected, including the ODBC32.dll and ODBCCP32.dll.
I thought I had run a trace, but I did not realize I had to switch it on. I
ran a trace and found these error lines toward the end. This trace was only
running while I opened the problem table in the DB window.
MQ_CLEAN 878-58c EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 09022400
DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP
Sockets]ConnectionRead (recv()). (10054)
DIAG [08S01] [Microsoft][ODBC SQL Server Driver][TCP/IP Sock
ets]General
network error. Check your network documentation. (11)
I am not familiar with ODBC calls, but I see there were two error messages
I have done some more testing and found that I can open a form that displays
the contents of this table in a listbox with no errors. The RowSource for
the listbox is a select query that selects all fields of the table.
Yet, if I create a stand alone query that does the same, it fails.
I am beginning to suspect network problems, possibly the NIC on the server.
We had problems with that before and it was replaced. The connection into
the main network from my machine also goes through a switch, which could be
acting up.
I still think I am going to update MDAC to the same as the server, or do you
think 2.8 would be better?
Ragnar|||You'd definitely want to be on 2.7 SP1 at least due to all
of the bug fixes for it since 2.6 SP2. I'd try to go that
route first being that it works on your server.
2.8 has a lot of security changes and it should be tested in
another environment if possible before installing. It's the
base mdac version for Windows 2003 but I don't think
anything other products, service packs, etc are installing
it at this point.
The list box testing you did is odd, otherwise I'd suspect
network issues as well (or wouldn't rule them out anyway).
Were both of the connections, data source properties for the
list box and the stand alone query exactly the same?
-Sue
On Thu, 11 Mar 2004 13:13:12 -0500, "Ragnar Midtskogen"
<ragnar_ng@.optonline.net> wrote:
>Thank you Sue,
>I had run ComponentChecker on my development machine and it now has 2.6 SP2
>Refresh wih a couple of files being slightly newer than expected, ODBC32.dl
l
>and ODBCCP32.dll.
>The Server had 2.7 SP1 Refresh with about 8 files slightly newer than
>expected, including the ODBC32.dll and ODBCCP32.dll.
>I thought I had run a trace, but I did not realize I had to switch it on. I
>ran a trace and found these error lines toward the end. This trace was only
>running while I opened the problem table in the DB window.
>MQ_CLEAN 878-58c EXIT SQLExecute with return code -1 (SQL_ERROR)
> HSTMT 09022400
> DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP
>Sockets]ConnectionRead (recv()). (10054)
> DIAG [08S01] [Microsoft][ODBC SQL Server Driver][TCP/IP S
ockets]General
>network error. Check your network documentation. (11)
>I am not familiar with ODBC calls, but I see there were two error messages
>I have done some more testing and found that I can open a form that display
s
>the contents of this table in a listbox with no errors. The RowSource for
>the listbox is a select query that selects all fields of the table.
>Yet, if I create a stand alone query that does the same, it fails.
>I am beginning to suspect network problems, possibly the NIC on the server.
>We had problems with that before and it was replaced. The connection into
>the main network from my machine also goes through a switch, which could be
>acting up.
>I still think I am going to update MDAC to the same as the server, or do yo
u
>think 2.8 would be better?
>Ragnar
>|||Thank you Sue,
> You'd definitely want to be on 2.7 SP1 at least due to all
> of the bug fixes for it since 2.6 SP2. I'd try to go that
> route first being that it works on your server.
That is what I did. Unfortunately the problem is still there.
> The list box testing you did is odd, otherwise I'd suspect
> network issues as well (or wouldn't rule them out anyway).
> Were both of the connections, data source properties for the
> list box and the stand alone query exactly the same?
They both use the same linked table.
I suspect the network connection now, I tried running my app on another
machine on the network and the problem disappeared.
Ragnar|||Hi, I had a similar issue with an Oracle database connection. I got around
it by removing the linked tables, compacting/repairing the Access database a
nd recreating the links. I suspect that Access is maintaining some informat
ion about the back-end tables that is not visible and is corrupt. If so, th
is information is removed by the repair if the link is gone. Anyway, hopefu
lly this will also work for you.
... Bob
I have been working on updating an Access 2000 application that uses linked
ODBC tables to connect to an SQL Server 2000. My development machine is
connected to the server over a Microsoft LAN, at 100Mbits.
Both the Office 2000 on my development machine and the SQL server has all
the latest SP's
Everything worked fine for months until today. I am not aware of any changes
to ether machine since yesterday.
Now, there is one table that can not be opened any more.
I can access all the other tables ecxept that one. When I try to open it I
get the message "ODBC call failed". The table is displayed but with #Name?
in all columns for all records.
Once that has happened I get the same message for all tables. In addition to
"ODBC call failed" there is also
"[Microsoft][ODBC SQL Server Driver][Communication link failure
(#0)".
When I close down and reopen the app I can again access all tables except
the bad one.
At first, I thought the table had gotten corrupted, but I can open the table
just fine on the server. If I set up an ODBC on the server with the same
name I and run my application there. the problem table opens fine.
I removed the ODBC and set it up again with the same properties, client uses
TCP/IP network library, and I am using SQL Server authentication.
I am using MDAC 2.6 SP2, and I did a refresh, but it did not solve problem.
I ran
I am wondering if I should update to 2.8
If anyone has a suggestion I would appreciate it very much.
RagnarIt could be an MDAC issue. You could run the MDAC component
checker on a machine where the app works and another machine
where the app gets the ODBC error and see if there are any
differences in MDAC versions. You can get component checker
from:
http://msdn.microsoft.com/data/down...ls/default.aspx
You could also turn on ODBC tracing then reproduce your
error and then turn tracing off again. You could open up the
ODBC trace log, look for specifics of what's failing and try
to track down the issue from there.
-Sue
On Wed, 10 Mar 2004 19:05:29 -0500, "Ragnar Midtskogen"
<ragnar_ng@.optonline.net> wrote:
>Hello,
>I have been working on updating an Access 2000 application that uses linked
>ODBC tables to connect to an SQL Server 2000. My development machine is
>connected to the server over a Microsoft LAN, at 100Mbits.
>Both the Office 2000 on my development machine and the SQL server has all
>the latest SP's
>Everything worked fine for months until today. I am not aware of any change
s
>to ether machine since yesterday.
>Now, there is one table that can not be opened any more.
>I can access all the other tables ecxept that one. When I try to open it I
>get the message "ODBC call failed". The table is displayed but with #Name?
>in all columns for all records.
>Once that has happened I get the same message for all tables. In addition t
o
>"ODBC call failed" there is also
>"[Microsoft][ODBC SQL Server Driver][Communication link failure
(#0)".
>When I close down and reopen the app I can again access all tables except
>the bad one.
>At first, I thought the table had gotten corrupted, but I can open the tabl
e
>just fine on the server. If I set up an ODBC on the server with the same
>name I and run my application there. the problem table opens fine.
>I removed the ODBC and set it up again with the same properties, client use
s
>TCP/IP network library, and I am using SQL Server authentication.
>I am using MDAC 2.6 SP2, and I did a refresh, but it did not solve problem.
>I ran
>I am wondering if I should update to 2.8
>If anyone has a suggestion I would appreciate it very much.
>Ragnar
>|||Thank you Sue,
I had run ComponentChecker on my development machine and it now has 2.6 SP2
Refresh wih a couple of files being slightly newer than expected, ODBC32.dll
and ODBCCP32.dll.
The Server had 2.7 SP1 Refresh with about 8 files slightly newer than
expected, including the ODBC32.dll and ODBCCP32.dll.
I thought I had run a trace, but I did not realize I had to switch it on. I
ran a trace and found these error lines toward the end. This trace was only
running while I opened the problem table in the DB window.
MQ_CLEAN 878-58c EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 09022400
DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP
Sockets]ConnectionRead (recv()). (10054)
DIAG [08S01] [Microsoft][ODBC SQL Server Driver][TCP/IP Sock
ets]General
network error. Check your network documentation. (11)
I am not familiar with ODBC calls, but I see there were two error messages
I have done some more testing and found that I can open a form that displays
the contents of this table in a listbox with no errors. The RowSource for
the listbox is a select query that selects all fields of the table.
Yet, if I create a stand alone query that does the same, it fails.
I am beginning to suspect network problems, possibly the NIC on the server.
We had problems with that before and it was replaced. The connection into
the main network from my machine also goes through a switch, which could be
acting up.
I still think I am going to update MDAC to the same as the server, or do you
think 2.8 would be better?
Ragnar|||You'd definitely want to be on 2.7 SP1 at least due to all
of the bug fixes for it since 2.6 SP2. I'd try to go that
route first being that it works on your server.
2.8 has a lot of security changes and it should be tested in
another environment if possible before installing. It's the
base mdac version for Windows 2003 but I don't think
anything other products, service packs, etc are installing
it at this point.
The list box testing you did is odd, otherwise I'd suspect
network issues as well (or wouldn't rule them out anyway).
Were both of the connections, data source properties for the
list box and the stand alone query exactly the same?
-Sue
On Thu, 11 Mar 2004 13:13:12 -0500, "Ragnar Midtskogen"
<ragnar_ng@.optonline.net> wrote:
>Thank you Sue,
>I had run ComponentChecker on my development machine and it now has 2.6 SP2
>Refresh wih a couple of files being slightly newer than expected, ODBC32.dl
l
>and ODBCCP32.dll.
>The Server had 2.7 SP1 Refresh with about 8 files slightly newer than
>expected, including the ODBC32.dll and ODBCCP32.dll.
>I thought I had run a trace, but I did not realize I had to switch it on. I
>ran a trace and found these error lines toward the end. This trace was only
>running while I opened the problem table in the DB window.
>MQ_CLEAN 878-58c EXIT SQLExecute with return code -1 (SQL_ERROR)
> HSTMT 09022400
> DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP
>Sockets]ConnectionRead (recv()). (10054)
> DIAG [08S01] [Microsoft][ODBC SQL Server Driver][TCP/IP S
ockets]General
>network error. Check your network documentation. (11)
>I am not familiar with ODBC calls, but I see there were two error messages
>I have done some more testing and found that I can open a form that display
s
>the contents of this table in a listbox with no errors. The RowSource for
>the listbox is a select query that selects all fields of the table.
>Yet, if I create a stand alone query that does the same, it fails.
>I am beginning to suspect network problems, possibly the NIC on the server.
>We had problems with that before and it was replaced. The connection into
>the main network from my machine also goes through a switch, which could be
>acting up.
>I still think I am going to update MDAC to the same as the server, or do yo
u
>think 2.8 would be better?
>Ragnar
>|||Thank you Sue,
> You'd definitely want to be on 2.7 SP1 at least due to all
> of the bug fixes for it since 2.6 SP2. I'd try to go that
> route first being that it works on your server.
That is what I did. Unfortunately the problem is still there.
> The list box testing you did is odd, otherwise I'd suspect
> network issues as well (or wouldn't rule them out anyway).
> Were both of the connections, data source properties for the
> list box and the stand alone query exactly the same?
They both use the same linked table.
I suspect the network connection now, I tried running my app on another
machine on the network and the problem disappeared.
Ragnar|||Hi, I had a similar issue with an Oracle database connection. I got around
it by removing the linked tables, compacting/repairing the Access database a
nd recreating the links. I suspect that Access is maintaining some informat
ion about the back-end tables that is not visible and is corrupt. If so, th
is information is removed by the repair if the link is gone. Anyway, hopefu
lly this will also work for you.
... Bob
quote:
Originally posted by Ragnar Midtskogen
Thank you Sue,
> You'd definitely want to be on 2.7 SP1 at least due to all
> of the bug fixes for it since 2.6 SP2. I'd try to go that
> route first being that it works on your server.
That is what I did. Unfortunately the problem is still there.
> The list box testing you did is odd, otherwise I'd suspect
> network issues as well (or wouldn't rule them out anyway).
> Were both of the connections, data source properties for the
> list box and the stand alone query exactly the same?
They both use the same linked table.
I suspect the network connection now, I tried running my app on another
machine on the network and the problem disappeared.
Ragnar
ODBC call failed
Hello,
I use Linked Tables to connect to my SQL Server database.
I have a number of reports which worked fine when I was
using test data (few thousand records).
I just loaded in a million records and ran a report, and
received the following error:
ODBC call failed
when running this query:
SELECT regionorder, Regions3, 1 As Quarter, January as
A, February as B, March as C FROM
RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 2 As Quarter, April, May,
June FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 3 As Quarter, July, August,
September FROM RegionofResidenceCrosstab
UNION ALL SELECT regionorder, Regions3, 4 As Quarter,
October, November, December FROM
RegionofResidenceCrosstab;
RegionofResidenceCrosstab looks like this:
TRANSFORM "~" & Count(IIf(DatePart('yyyy',
& #91;F_ARRV_DAT],1,0)=forms!DateRangePrev
iousCurrentYear!
PrevYear,[Final_Clean Tourists Query].[TRAV-KEY])) & "~"
& Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=forms!
DateRangePreviousCurrentYear!CurrYear,[Final_Clean
Tourists Query].[TRAV-KEY])) AS Expr1
SELECT [Final_Clean Tourists Query].REGIONS3,
[Final_Clean Tourists Query].regionorder
FROM [Final_Clean Tourists Query]
WHERE [Final_Clean Tourists Query].REGIONS3<>''
GROUP BY [Final_Clean Tourists Query].regionorder,
[Final_Clean Tourists Query].REGIONS3
PIVOT Format([F_ARRV_DAT],"mmmm") in
("January", "February", "March", "April","May","June","Jul
y","August","September","October","November","December");
1. I am wondering if the query is not simply timing out.
2. Given that these queries were written for MS Access
maybe I need to convert them into stored procedures to be
called by the report.
Any other suggestions?Your particular query is way too much for Access to handle. The UNION
clauses are causing all rows to be fetched locally into Access for
processing. Then Access tries to run the IIf and other functions on
each row, one row at a time. So the answer is, yes indeedy, you need
to convert this monster query to a stored procedure, which you can
then call from a pass-through query that you can base the report on.
One other point -- Keep all format functions and the like in the
report itself, don't put those in the stored procedure. You'll just
slow down the execution of the stored procedure, and the report has to
process each line in the result set one at a time anyway. What you
want to do is let the stored procedure do all the data processing,
hand the result set back to Access, and let Access do the
presentation.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Fri, 19 Mar 2004 09:20:34 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:
>Hello,
>I use Linked Tables to connect to my SQL Server database.
>I have a number of reports which worked fine when I was
>using test data (few thousand records).
>I just loaded in a million records and ran a report, and
>received the following error:
>ODBC call failed
>when running this query:
>SELECT regionorder, Regions3, 1 As Quarter, January as
>A, February as B, March as C FROM
>RegionofResidenceCrosstab
>UNION ALL
>SELECT regionorder, Regions3, 2 As Quarter, April, May,
>June FROM RegionofResidenceCrosstab
>UNION ALL
>SELECT regionorder, Regions3, 3 As Quarter, July, August,
>September FROM RegionofResidenceCrosstab
>UNION ALL SELECT regionorder, Regions3, 4 As Quarter,
>October, November, December FROM
>RegionofResidenceCrosstab;
>RegionofResidenceCrosstab looks like this:
>TRANSFORM "~" & Count(IIf(DatePart('yyyy',
>& #91;F_ARRV_DAT],1,0)=forms!DateRangePrev
iousCurrentYear!
>PrevYear,[Final_Clean Tourists Query].[TRAV-KEY])) & "~"
>& Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=forms!
>DateRangePreviousCurrentYear!CurrYear,[Final_Clean
>Tourists Query].[TRAV-KEY])) AS Expr1
>SELECT [Final_Clean Tourists Query].REGIONS3,
>[Final_Clean Tourists Query].regionorder
>FROM [Final_Clean Tourists Query]
>WHERE [Final_Clean Tourists Query].REGIONS3<>''
>GROUP BY [Final_Clean Tourists Query].regionorder,
>[Final_Clean Tourists Query].REGIONS3
>PIVOT Format([F_ARRV_DAT],"mmmm") in
>("January", "February", "March", "April","May","June","Jul
>y","August","September","October","November","December");
>
>1. I am wondering if the query is not simply timing out.
>2. Given that these queries were written for MS Access
>maybe I need to convert them into stored procedures to be
>called by the report.
>Any other suggestions?
>|||Hi,
Thanks for the pointers, guess I have my work cut out for
me. I may also try upsizing to an ADP to try to improve
performance.
>--Original Message--
>Your particular query is way too much for Access to
handle. The UNION
>clauses are causing all rows to be fetched locally into
Access for
>processing. Then Access tries to run the IIf and other
functions on
>each row, one row at a time. So the answer is, yes
indeedy, you need
>to convert this monster query to a stored procedure,
which you can
>then call from a pass-through query that you can base the
report on.
>One other point -- Keep all format functions and the like
in the
>report itself, don't put those in the stored procedure.
You'll just
>slow down the execution of the stored procedure, and the
report has to
>process each line in the result set one at a time anyway.
What you
>want to do is let the stored procedure do all the data
processing,
>hand the result set back to Access, and let Access do the
>presentation.
>-- Mary
>Microsoft Access Developer's Guide to SQL Server
>http://www.amazon.com/exec/obidos/ASIN/0672319446
>On Fri, 19 Mar 2004 09:20:34 -0800, "blinton25"
><blinton25@.hotmail.com> wrote:
>
database.
August,
("January", "February", "March", "April","May","June","Jul
be
>.
>|||You will gain nothing in performance simply from upsizing to an ADP.
There's nothing magical about an ADP vs. an mbd with linked tables --
you're going to need to rewrite your app no matter which way you slice
it. You'll still need to re-do that query as a proc (since there's no
such thing as a saved query in an ADP anyway). What you give up in an
ADP is a lot of flexibility (local storage, queries, etc.). Also,
forms work differently by fetching snapshots, not dynasets. There's
simply no easy way to upsize a large, complex Jet app that doesn't
involve a re-write.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sat, 20 Mar 2004 10:24:00 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:
>Hi,
>Thanks for the pointers, guess I have my work cut out for
>me. I may also try upsizing to an ADP to try to improve
>performance.
>
>handle. The UNION
>Access for
>functions on
>indeedy, you need
>which you can
>report on.
>in the
>You'll just
>report has to
>What you
>processing,
>database.
>August,
>("January", "February", "March", "April","May","June","Jul
>be|||Hello,
Sorry, I am looking at performance in terms of moving
processing to the server versus the client and reducing
network traffic. Understood that I will have to rewrite
the queries as stored procedures, and initially will do
this and then pass through to the stored Proc.
Doing some reading at:
http://www.upsizewizard.com/Reference_Page.htm
http://www.granite.ab.ca/access/sqlserverupsizing.htm
to help me understand some approaches to achieving my
goals.
Thanks again for the guidance.
>--Original Message--
>You will gain nothing in performance simply from
upsizing to an ADP.
>There's nothing magical about an ADP vs. an mbd with
linked tables --
>you're going to need to rewrite your app no matter which
way you slice
>it. You'll still need to re-do that query as a proc
(since there's no
>such thing as a saved query in an ADP anyway). What you
give up in an
>ADP is a lot of flexibility (local storage, queries,
etc.). Also,
>forms work differently by fetching snapshots, not
dynasets. There's
>simply no easy way to upsize a large, complex Jet app
that doesn't
>involve a re-write.
>-- Mary
>Microsoft Access Developer's Guide to SQL Server
>http://www.amazon.com/exec/obidos/ASIN/0672319446
>On Sat, 20 Mar 2004 10:24:00 -0800, "blinton25"
><blinton25@.hotmail.com> wrote:
>
for
into
the
like
procedure.
the
anyway.
the
was
and
as
May,
Quarter,
& "~"
("January", "February", "March", "April","May","June","Jul
");
out.
Access
to
>.
>|||I understood that -- I was just saying that you gain nothing in terms
of performance from an ADP that you can't also achieve with an mdb
front-end. The key is fetching only needed data on the client, and an
mdb gives you a lot more options for caching static data locally, etc.
The first link you reference in your post does not answer the question
it poses "why ADP" other than to spout generalities about
client-server being faster. What the Access team itself is saying (at
recent conferences) is that they are recommending mdb over adp because
you give up so much flexibility with an adp. Performance is very much
dependent on factors such as efficient database design, avoidance of
concurrency conflicts, fetching less data, network speed and other
factors that have nothing to do with the client software. It's good
that you're doing reading up on the subject before leaping in, just be
aware that not everything you read is necessarily true or accurate.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sun, 21 Mar 2004 09:12:30 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:
>Hello,
>Sorry, I am looking at performance in terms of moving
>processing to the server versus the client and reducing
>network traffic. Understood that I will have to rewrite
>the queries as stored procedures, and initially will do
>this and then pass through to the stored Proc.
>Doing some reading at:
>http://www.upsizewizard.com/Reference_Page.htm
>http://www.granite.ab.ca/access/sqlserverupsizing.htm
>to help me understand some approaches to achieving my
>goals.
>Thanks again for the guidance.
>
>upsizing to an ADP.
>linked tables --
>way you slice
>(since there's no
>give up in an
>etc.). Also,
>dynasets. There's
>that doesn't
>for
>into
>the
>like
>procedure.
>the
>anyway.
>the
>was
>and
>as
>May,
>Quarter,
>& "~"
>("January", "February", "March", "April","May","June","Jul
>");
>out.
>Access
>to
I use Linked Tables to connect to my SQL Server database.
I have a number of reports which worked fine when I was
using test data (few thousand records).
I just loaded in a million records and ran a report, and
received the following error:
ODBC call failed
when running this query:
SELECT regionorder, Regions3, 1 As Quarter, January as
A, February as B, March as C FROM
RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 2 As Quarter, April, May,
June FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 3 As Quarter, July, August,
September FROM RegionofResidenceCrosstab
UNION ALL SELECT regionorder, Regions3, 4 As Quarter,
October, November, December FROM
RegionofResidenceCrosstab;
RegionofResidenceCrosstab looks like this:
TRANSFORM "~" & Count(IIf(DatePart('yyyy',
& #91;F_ARRV_DAT],1,0)=forms!DateRangePrev
iousCurrentYear!
PrevYear,[Final_Clean Tourists Query].[TRAV-KEY])) & "~"
& Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=forms!
DateRangePreviousCurrentYear!CurrYear,[Final_Clean
Tourists Query].[TRAV-KEY])) AS Expr1
SELECT [Final_Clean Tourists Query].REGIONS3,
[Final_Clean Tourists Query].regionorder
FROM [Final_Clean Tourists Query]
WHERE [Final_Clean Tourists Query].REGIONS3<>''
GROUP BY [Final_Clean Tourists Query].regionorder,
[Final_Clean Tourists Query].REGIONS3
PIVOT Format([F_ARRV_DAT],"mmmm") in
("January", "February", "March", "April","May","June","Jul
y","August","September","October","November","December");
1. I am wondering if the query is not simply timing out.
2. Given that these queries were written for MS Access
maybe I need to convert them into stored procedures to be
called by the report.
Any other suggestions?Your particular query is way too much for Access to handle. The UNION
clauses are causing all rows to be fetched locally into Access for
processing. Then Access tries to run the IIf and other functions on
each row, one row at a time. So the answer is, yes indeedy, you need
to convert this monster query to a stored procedure, which you can
then call from a pass-through query that you can base the report on.
One other point -- Keep all format functions and the like in the
report itself, don't put those in the stored procedure. You'll just
slow down the execution of the stored procedure, and the report has to
process each line in the result set one at a time anyway. What you
want to do is let the stored procedure do all the data processing,
hand the result set back to Access, and let Access do the
presentation.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Fri, 19 Mar 2004 09:20:34 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:
>Hello,
>I use Linked Tables to connect to my SQL Server database.
>I have a number of reports which worked fine when I was
>using test data (few thousand records).
>I just loaded in a million records and ran a report, and
>received the following error:
>ODBC call failed
>when running this query:
>SELECT regionorder, Regions3, 1 As Quarter, January as
>A, February as B, March as C FROM
>RegionofResidenceCrosstab
>UNION ALL
>SELECT regionorder, Regions3, 2 As Quarter, April, May,
>June FROM RegionofResidenceCrosstab
>UNION ALL
>SELECT regionorder, Regions3, 3 As Quarter, July, August,
>September FROM RegionofResidenceCrosstab
>UNION ALL SELECT regionorder, Regions3, 4 As Quarter,
>October, November, December FROM
>RegionofResidenceCrosstab;
>RegionofResidenceCrosstab looks like this:
>TRANSFORM "~" & Count(IIf(DatePart('yyyy',
>& #91;F_ARRV_DAT],1,0)=forms!DateRangePrev
iousCurrentYear!
>PrevYear,[Final_Clean Tourists Query].[TRAV-KEY])) & "~"
>& Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=forms!
>DateRangePreviousCurrentYear!CurrYear,[Final_Clean
>Tourists Query].[TRAV-KEY])) AS Expr1
>SELECT [Final_Clean Tourists Query].REGIONS3,
>[Final_Clean Tourists Query].regionorder
>FROM [Final_Clean Tourists Query]
>WHERE [Final_Clean Tourists Query].REGIONS3<>''
>GROUP BY [Final_Clean Tourists Query].regionorder,
>[Final_Clean Tourists Query].REGIONS3
>PIVOT Format([F_ARRV_DAT],"mmmm") in
>("January", "February", "March", "April","May","June","Jul
>y","August","September","October","November","December");
>
>1. I am wondering if the query is not simply timing out.
>2. Given that these queries were written for MS Access
>maybe I need to convert them into stored procedures to be
>called by the report.
>Any other suggestions?
>|||Hi,
Thanks for the pointers, guess I have my work cut out for
me. I may also try upsizing to an ADP to try to improve
performance.
>--Original Message--
>Your particular query is way too much for Access to
handle. The UNION
>clauses are causing all rows to be fetched locally into
Access for
>processing. Then Access tries to run the IIf and other
functions on
>each row, one row at a time. So the answer is, yes
indeedy, you need
>to convert this monster query to a stored procedure,
which you can
>then call from a pass-through query that you can base the
report on.
>One other point -- Keep all format functions and the like
in the
>report itself, don't put those in the stored procedure.
You'll just
>slow down the execution of the stored procedure, and the
report has to
>process each line in the result set one at a time anyway.
What you
>want to do is let the stored procedure do all the data
processing,
>hand the result set back to Access, and let Access do the
>presentation.
>-- Mary
>Microsoft Access Developer's Guide to SQL Server
>http://www.amazon.com/exec/obidos/ASIN/0672319446
>On Fri, 19 Mar 2004 09:20:34 -0800, "blinton25"
><blinton25@.hotmail.com> wrote:
>
database.
August,
("January", "February", "March", "April","May","June","Jul
be
>.
>|||You will gain nothing in performance simply from upsizing to an ADP.
There's nothing magical about an ADP vs. an mbd with linked tables --
you're going to need to rewrite your app no matter which way you slice
it. You'll still need to re-do that query as a proc (since there's no
such thing as a saved query in an ADP anyway). What you give up in an
ADP is a lot of flexibility (local storage, queries, etc.). Also,
forms work differently by fetching snapshots, not dynasets. There's
simply no easy way to upsize a large, complex Jet app that doesn't
involve a re-write.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sat, 20 Mar 2004 10:24:00 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:
>Hi,
>Thanks for the pointers, guess I have my work cut out for
>me. I may also try upsizing to an ADP to try to improve
>performance.
>
>handle. The UNION
>Access for
>functions on
>indeedy, you need
>which you can
>report on.
>in the
>You'll just
>report has to
>What you
>processing,
>database.
>August,
>("January", "February", "March", "April","May","June","Jul
>be|||Hello,
Sorry, I am looking at performance in terms of moving
processing to the server versus the client and reducing
network traffic. Understood that I will have to rewrite
the queries as stored procedures, and initially will do
this and then pass through to the stored Proc.
Doing some reading at:
http://www.upsizewizard.com/Reference_Page.htm
http://www.granite.ab.ca/access/sqlserverupsizing.htm
to help me understand some approaches to achieving my
goals.
Thanks again for the guidance.
>--Original Message--
>You will gain nothing in performance simply from
upsizing to an ADP.
>There's nothing magical about an ADP vs. an mbd with
linked tables --
>you're going to need to rewrite your app no matter which
way you slice
>it. You'll still need to re-do that query as a proc
(since there's no
>such thing as a saved query in an ADP anyway). What you
give up in an
>ADP is a lot of flexibility (local storage, queries,
etc.). Also,
>forms work differently by fetching snapshots, not
dynasets. There's
>simply no easy way to upsize a large, complex Jet app
that doesn't
>involve a re-write.
>-- Mary
>Microsoft Access Developer's Guide to SQL Server
>http://www.amazon.com/exec/obidos/ASIN/0672319446
>On Sat, 20 Mar 2004 10:24:00 -0800, "blinton25"
><blinton25@.hotmail.com> wrote:
>
for
into
the
like
procedure.
the
anyway.
the
was
and
as
May,
Quarter,
& "~"
("January", "February", "March", "April","May","June","Jul
");
out.
Access
to
>.
>|||I understood that -- I was just saying that you gain nothing in terms
of performance from an ADP that you can't also achieve with an mdb
front-end. The key is fetching only needed data on the client, and an
mdb gives you a lot more options for caching static data locally, etc.
The first link you reference in your post does not answer the question
it poses "why ADP" other than to spout generalities about
client-server being faster. What the Access team itself is saying (at
recent conferences) is that they are recommending mdb over adp because
you give up so much flexibility with an adp. Performance is very much
dependent on factors such as efficient database design, avoidance of
concurrency conflicts, fetching less data, network speed and other
factors that have nothing to do with the client software. It's good
that you're doing reading up on the subject before leaping in, just be
aware that not everything you read is necessarily true or accurate.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sun, 21 Mar 2004 09:12:30 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:
>Hello,
>Sorry, I am looking at performance in terms of moving
>processing to the server versus the client and reducing
>network traffic. Understood that I will have to rewrite
>the queries as stored procedures, and initially will do
>this and then pass through to the stored Proc.
>Doing some reading at:
>http://www.upsizewizard.com/Reference_Page.htm
>http://www.granite.ab.ca/access/sqlserverupsizing.htm
>to help me understand some approaches to achieving my
>goals.
>Thanks again for the guidance.
>
>upsizing to an ADP.
>linked tables --
>way you slice
>(since there's no
>give up in an
>etc.). Also,
>dynasets. There's
>that doesn't
>for
>into
>the
>like
>procedure.
>the
>anyway.
>the
>was
>and
>as
>May,
>Quarter,
>& "~"
>("January", "February", "March", "April","May","June","Jul
>");
>out.
>Access
>to
Monday, March 12, 2012
ODBC -- Call Failed with error # 547
[Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
statement conflicted with COLUMN FOREIGN KEY
constraint 'Attachment_FK00'. The conflict occured in
database 'assay', table 'suborders', column 'suborderid'.
(#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
statement has been terminated (#3621)
when user try to upload the document thought Access 2002
to SQL database, occasionally user could get this error.
did someone experience this error before'
thanks,
wilsonHi
You need to look at the data or insert order/method as your FK constraint is
not being satisfied.
John
"Wilson" <anonymous@.discussions.microsoft.com> wrote in message
news:1a51b01c44e4d$4543dc20$a501280a@.phx
.gbl...
> [Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
> statement conflicted with COLUMN FOREIGN KEY
> constraint 'Attachment_FK00'. The conflict occured in
> database 'assay', table 'suborders', column 'suborderid'.
> (#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
> statement has been terminated (#3621)
> when user try to upload the document thought Access 2002
> to SQL database, occasionally user could get this error.
> did someone experience this error before'
> thanks,
> wilson|||Hi john,
thanks for your quick response.
why it is working sometimes, but sometimes it is not.
wilson
>--Original Message--
>Hi
>You need to look at the data or insert order/method as
your FK constraint is
>not being satisfied.
>John
>"Wilson" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1a51b01c44e4d$4543dc20$a501280a@.phx
.gbl...
column 'suborderid'.[vbcol=seagreen]
The[vbcol=seagreen]
2002[vbcol=seagreen]
error.[vbcol=seagreen]
>
>.
>|||Hi
That is a feature of the data you are inserting. Try loading this into a
copy of the table (i.e. without the FK) and then check the data with a
statement similar to:
SELECT * FROM TmpTable
WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1a63301c44e5b$c829ce70$a401280a@.phx
.gbl...[vbcol=seagreen]
> Hi john,
> thanks for your quick response.
> why it is working sometimes, but sometimes it is not.
>
> wilson
> your FK constraint is
> message
> column 'suborderid'.
> The
> 2002
> error.|||thanks, John.
But now we got another error:
Syntax error (missing operator) in query
expression '[SubOrderID]='
any ideas'
thanks,
wilson
>--Original Message--
>Hi
>That is a feature of the data you are inserting. Try
loading this into a
>copy of the table (i.e. without the FK) and then check
the data with a
>statement similar to:
>SELECT * FROM TmpTable
>WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
>John
><anonymous@.discussions.microsoft.com> wrote in message
> news:1a63301c44e5b$c829ce70$a401280a@.phx
.gbl...
in[vbcol=seagreen]
INSERT[vbcol=seagreen]
in[vbcol=seagreen]
Server][vbcol=seagreen]
>
>.
>|||Hi
You may want to start ODBC logging and see what the statements are. At a
guess you either are creating the statements dynamically and that is not
working correctly e.g. as missing 'AND' or alternatively that your data has
a character that needs escaping in it e.g as a quote.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1aa3301c44f0b$bc9fcb80$a301280a@.phx
.gbl...[vbcol=seagreen]
> thanks, John.
> But now we got another error:
> Syntax error (missing operator) in query
> expression '[SubOrderID]='
> any ideas'
> thanks,
> wilson
> loading this into a
> the data with a
> in
> INSERT
> in
> Server]
statement conflicted with COLUMN FOREIGN KEY
constraint 'Attachment_FK00'. The conflict occured in
database 'assay', table 'suborders', column 'suborderid'.
(#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
statement has been terminated (#3621)
when user try to upload the document thought Access 2002
to SQL database, occasionally user could get this error.
did someone experience this error before'
thanks,
wilsonHi
You need to look at the data or insert order/method as your FK constraint is
not being satisfied.
John
"Wilson" <anonymous@.discussions.microsoft.com> wrote in message
news:1a51b01c44e4d$4543dc20$a501280a@.phx
.gbl...
> [Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
> statement conflicted with COLUMN FOREIGN KEY
> constraint 'Attachment_FK00'. The conflict occured in
> database 'assay', table 'suborders', column 'suborderid'.
> (#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
> statement has been terminated (#3621)
> when user try to upload the document thought Access 2002
> to SQL database, occasionally user could get this error.
> did someone experience this error before'
> thanks,
> wilson|||Hi john,
thanks for your quick response.
why it is working sometimes, but sometimes it is not.
wilson
>--Original Message--
>Hi
>You need to look at the data or insert order/method as
your FK constraint is
>not being satisfied.
>John
>"Wilson" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1a51b01c44e4d$4543dc20$a501280a@.phx
.gbl...
column 'suborderid'.[vbcol=seagreen]
The[vbcol=seagreen]
2002[vbcol=seagreen]
error.[vbcol=seagreen]
>
>.
>|||Hi
That is a feature of the data you are inserting. Try loading this into a
copy of the table (i.e. without the FK) and then check the data with a
statement similar to:
SELECT * FROM TmpTable
WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1a63301c44e5b$c829ce70$a401280a@.phx
.gbl...[vbcol=seagreen]
> Hi john,
> thanks for your quick response.
> why it is working sometimes, but sometimes it is not.
>
> wilson
> your FK constraint is
> message
> column 'suborderid'.
> The
> 2002
> error.|||thanks, John.
But now we got another error:
Syntax error (missing operator) in query
expression '[SubOrderID]='
any ideas'
thanks,
wilson
>--Original Message--
>Hi
>That is a feature of the data you are inserting. Try
loading this into a
>copy of the table (i.e. without the FK) and then check
the data with a
>statement similar to:
>SELECT * FROM TmpTable
>WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
>John
><anonymous@.discussions.microsoft.com> wrote in message
> news:1a63301c44e5b$c829ce70$a401280a@.phx
.gbl...
in[vbcol=seagreen]
INSERT[vbcol=seagreen]
in[vbcol=seagreen]
Server][vbcol=seagreen]
>
>.
>|||Hi
You may want to start ODBC logging and see what the statements are. At a
guess you either are creating the statements dynamically and that is not
working correctly e.g. as missing 'AND' or alternatively that your data has
a character that needs escaping in it e.g as a quote.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1aa3301c44f0b$bc9fcb80$a301280a@.phx
.gbl...[vbcol=seagreen]
> thanks, John.
> But now we got another error:
> Syntax error (missing operator) in query
> expression '[SubOrderID]='
> any ideas'
> thanks,
> wilson
> loading this into a
> the data with a
> in
> INSERT
> in
> Server]
ODBC -- Call Failed with error # 547
[Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
statement conflicted with COLUMN FOREIGN KEY
constraint 'Attachment_FK00'. The conflict occured in
database 'assay', table 'suborders', column 'suborderid'.
(#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
statement has been terminated (#3621)
when user try to upload the document thought Access 2002
to SQL database, occasionally user could get this error.
did someone experience this error before?
thanks,
wilson
Hi
You need to look at the data or insert order/method as your FK constraint is
not being satisfied.
John
"Wilson" <anonymous@.discussions.microsoft.com> wrote in message
news:1a51b01c44e4d$4543dc20$a501280a@.phx.gbl...
> [Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
> statement conflicted with COLUMN FOREIGN KEY
> constraint 'Attachment_FK00'. The conflict occured in
> database 'assay', table 'suborders', column 'suborderid'.
> (#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
> statement has been terminated (#3621)
> when user try to upload the document thought Access 2002
> to SQL database, occasionally user could get this error.
> did someone experience this error before?
> thanks,
> wilson
|||Hi john,
thanks for your quick response.
why it is working sometimes, but sometimes it is not.
wilson
>--Original Message--
>Hi
>You need to look at the data or insert order/method as
your FK constraint is
>not being satisfied.
>John
>"Wilson" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1a51b01c44e4d$4543dc20$a501280a@.phx.gbl...
column 'suborderid'.[vbcol=seagreen]
The[vbcol=seagreen]
2002[vbcol=seagreen]
error.
>
>.
>
|||Hi
That is a feature of the data you are inserting. Try loading this into a
copy of the table (i.e. without the FK) and then check the data with a
statement similar to:
SELECT * FROM TmpTable
WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1a63301c44e5b$c829ce70$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi john,
> thanks for your quick response.
> why it is working sometimes, but sometimes it is not.
>
> wilson
> your FK constraint is
> message
> column 'suborderid'.
> The
> 2002
> error.
|||thanks, John.
But now we got another error:
Syntax error (missing operator) in query
expression '[SubOrderID]='
any ideas?
thanks,
wilson
>--Original Message--
>Hi
>That is a feature of the data you are inserting. Try
loading this into a
>copy of the table (i.e. without the FK) and then check
the data with a[vbcol=seagreen]
>statement similar to:
>SELECT * FROM TmpTable
>WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
>John
><anonymous@.discussions.microsoft.com> wrote in message
>news:1a63301c44e5b$c829ce70$a401280a@.phx.gbl...
in[vbcol=seagreen]
INSERT[vbcol=seagreen]
in[vbcol=seagreen]
Server]
>
>.
>
|||Hi
You may want to start ODBC logging and see what the statements are. At a
guess you either are creating the statements dynamically and that is not
working correctly e.g. as missing 'AND' or alternatively that your data has
a character that needs escaping in it e.g as a quote.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1aa3301c44f0b$bc9fcb80$a301280a@.phx.gbl...[vbcol=seagreen]
> thanks, John.
> But now we got another error:
> Syntax error (missing operator) in query
> expression '[SubOrderID]='
> any ideas?
> thanks,
> wilson
> loading this into a
> the data with a
> in
> INSERT
> in
> Server]
statement conflicted with COLUMN FOREIGN KEY
constraint 'Attachment_FK00'. The conflict occured in
database 'assay', table 'suborders', column 'suborderid'.
(#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
statement has been terminated (#3621)
when user try to upload the document thought Access 2002
to SQL database, occasionally user could get this error.
did someone experience this error before?
thanks,
wilson
Hi
You need to look at the data or insert order/method as your FK constraint is
not being satisfied.
John
"Wilson" <anonymous@.discussions.microsoft.com> wrote in message
news:1a51b01c44e4d$4543dc20$a501280a@.phx.gbl...
> [Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
> statement conflicted with COLUMN FOREIGN KEY
> constraint 'Attachment_FK00'. The conflict occured in
> database 'assay', table 'suborders', column 'suborderid'.
> (#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
> statement has been terminated (#3621)
> when user try to upload the document thought Access 2002
> to SQL database, occasionally user could get this error.
> did someone experience this error before?
> thanks,
> wilson
|||Hi john,
thanks for your quick response.
why it is working sometimes, but sometimes it is not.
wilson
>--Original Message--
>Hi
>You need to look at the data or insert order/method as
your FK constraint is
>not being satisfied.
>John
>"Wilson" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1a51b01c44e4d$4543dc20$a501280a@.phx.gbl...
column 'suborderid'.[vbcol=seagreen]
The[vbcol=seagreen]
2002[vbcol=seagreen]
error.
>
>.
>
|||Hi
That is a feature of the data you are inserting. Try loading this into a
copy of the table (i.e. without the FK) and then check the data with a
statement similar to:
SELECT * FROM TmpTable
WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1a63301c44e5b$c829ce70$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi john,
> thanks for your quick response.
> why it is working sometimes, but sometimes it is not.
>
> wilson
> your FK constraint is
> message
> column 'suborderid'.
> The
> 2002
> error.
|||thanks, John.
But now we got another error:
Syntax error (missing operator) in query
expression '[SubOrderID]='
any ideas?
thanks,
wilson
>--Original Message--
>Hi
>That is a feature of the data you are inserting. Try
loading this into a
>copy of the table (i.e. without the FK) and then check
the data with a[vbcol=seagreen]
>statement similar to:
>SELECT * FROM TmpTable
>WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
>John
><anonymous@.discussions.microsoft.com> wrote in message
>news:1a63301c44e5b$c829ce70$a401280a@.phx.gbl...
in[vbcol=seagreen]
INSERT[vbcol=seagreen]
in[vbcol=seagreen]
Server]
>
>.
>
|||Hi
You may want to start ODBC logging and see what the statements are. At a
guess you either are creating the statements dynamically and that is not
working correctly e.g. as missing 'AND' or alternatively that your data has
a character that needs escaping in it e.g as a quote.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1aa3301c44f0b$bc9fcb80$a301280a@.phx.gbl...[vbcol=seagreen]
> thanks, John.
> But now we got another error:
> Syntax error (missing operator) in query
> expression '[SubOrderID]='
> any ideas?
> thanks,
> wilson
> loading this into a
> the data with a
> in
> INSERT
> in
> Server]
Friday, March 9, 2012
ODBC
I am extracting data from SQL Server to another data form, and i have a tabl
e which is of 5 GB , when ODBC reads the table a error comes "failed to crea
te recordset from table".
Can someone suggest what might be the reason, 1 reason i found was that the
jet.temp it creates in windows has a limit of 2 GB..
can anyone tell something to do this.
From http://www.developmentnow.com/g/111...server-odbc.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.comI don't completely understand what the scenario is here but I'll take a stab
at it. I'm assuming that your getting this error somewhere in the middle of
the export. If your extracting data from Sql Server to a Jet database
(Microsoft.Jet.OLEDB.4.0 provider) then the problem most likely is the size
of your Sql table. .TMP files are used for processing Jet transactions and
are limited to the max Jet database size, which is 2gb. Even if you exporte
d
the Sql data to a text file first, if the data exceeds 2gb, it would still b
e
too much data for a Jet database.
--
-Curtis
"Anshul Kumar" wrote:
> I am extracting data from SQL Server to another data form, and i have a ta
ble which is of 5 GB , when ODBC reads the table a error comes "failed to cr
eate recordset from table".
> Can someone suggest what might be the reason, 1 reason i found was that th
e jet.temp it creates in windows has a limit of 2 GB..
> can anyone tell something to do this.
> From http://www.developmentnow.com/g/111...server-odbc.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>
e which is of 5 GB , when ODBC reads the table a error comes "failed to crea
te recordset from table".
Can someone suggest what might be the reason, 1 reason i found was that the
jet.temp it creates in windows has a limit of 2 GB..
can anyone tell something to do this.
From http://www.developmentnow.com/g/111...server-odbc.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.comI don't completely understand what the scenario is here but I'll take a stab
at it. I'm assuming that your getting this error somewhere in the middle of
the export. If your extracting data from Sql Server to a Jet database
(Microsoft.Jet.OLEDB.4.0 provider) then the problem most likely is the size
of your Sql table. .TMP files are used for processing Jet transactions and
are limited to the max Jet database size, which is 2gb. Even if you exporte
d
the Sql data to a text file first, if the data exceeds 2gb, it would still b
e
too much data for a Jet database.
--
-Curtis
"Anshul Kumar" wrote:
> I am extracting data from SQL Server to another data form, and i have a ta
ble which is of 5 GB , when ODBC reads the table a error comes "failed to cr
eate recordset from table".
> Can someone suggest what might be the reason, 1 reason i found was that th
e jet.temp it creates in windows has a limit of 2 GB..
> can anyone tell something to do this.
> From http://www.developmentnow.com/g/111...server-odbc.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>
ODBC
I am extracting data from SQL Server to another data form, and i have a table which is of 5 GB , when ODBC reads the table a error comes "failed to create recordset from table".
Can someone suggest what might be the reason, 1 reason i found was that the jet.temp it creates in windows has a limit of 2 GB..
can anyone tell something to do this.
From http://www.developmentnow.com/g/111_0_0_0_0_0/sql-server-odbc.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
I don't completely understand what the scenario is here but I'll take a stab
at it. I'm assuming that your getting this error somewhere in the middle of
the export. If your extracting data from Sql Server to a Jet database
(Microsoft.Jet.OLEDB.4.0 provider) then the problem most likely is the size
of your Sql table. .TMP files are used for processing Jet transactions and
are limited to the max Jet database size, which is 2gb. Even if you exported
the Sql data to a text file first, if the data exceeds 2gb, it would still be
too much data for a Jet database.
-Curtis
"Anshul Kumar" wrote:
> I am extracting data from SQL Server to another data form, and i have a table which is of 5 GB , when ODBC reads the table a error comes "failed to create recordset from table".
> Can someone suggest what might be the reason, 1 reason i found was that the jet.temp it creates in windows has a limit of 2 GB..
> can anyone tell something to do this.
> From http://www.developmentnow.com/g/111_0_0_0_0_0/sql-server-odbc.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>
Can someone suggest what might be the reason, 1 reason i found was that the jet.temp it creates in windows has a limit of 2 GB..
can anyone tell something to do this.
From http://www.developmentnow.com/g/111_0_0_0_0_0/sql-server-odbc.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
I don't completely understand what the scenario is here but I'll take a stab
at it. I'm assuming that your getting this error somewhere in the middle of
the export. If your extracting data from Sql Server to a Jet database
(Microsoft.Jet.OLEDB.4.0 provider) then the problem most likely is the size
of your Sql table. .TMP files are used for processing Jet transactions and
are limited to the max Jet database size, which is 2gb. Even if you exported
the Sql data to a text file first, if the data exceeds 2gb, it would still be
too much data for a Jet database.
-Curtis
"Anshul Kumar" wrote:
> I am extracting data from SQL Server to another data form, and i have a table which is of 5 GB , when ODBC reads the table a error comes "failed to create recordset from table".
> Can someone suggest what might be the reason, 1 reason i found was that the jet.temp it creates in windows has a limit of 2 GB..
> can anyone tell something to do this.
> From http://www.developmentnow.com/g/111_0_0_0_0_0/sql-server-odbc.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>
OCIEnvNlsCreate failed with return code -1
Hi. We're experiencing the exact same problem described in this post: http://tinyurl.com/ynqv7o
We've tried the 11 steps described there and it didn't fix the problem -- just as it didn't fix it for that person.
We've tried the 11 steps described there and it didn't fix the problem -- just as it didn't fix it for that person.
Help!! Thank you.Anyone have any thoughts on what's causing this problem? Your help is much appreciated 
Wednesday, March 7, 2012
Obtaining Hotfix
We're experiencing the problem describe in MS Knowledge
Base Article - 812915 "Performance monitor shared memory
setup failed: -1" error message when you start SQL
Server. The article states there is a support hotfix
available through Microsoft Product Support Services.
We're currently evaulating SQL server so we're using the
Enterprise Evaluation Edition. We like to obtain the
hotfix ( preferably for free ), but since we have
evaluation edition we don't know how to go about this.
Can we get the hotfix?
(a) I don't think you can apply patches and hotfixes to the evaluation
edition, but I'm not 100% positive. Did you install the latest release of
the evaluation edition, or have you been running it for a while?
(b) you can't just bypass product support and download a hotfix "for
free"... if it's a bug, and you can demonstrate to PSS that you are affected
by it, then you will be provided the file and refunded the cost of the call.
http://www.aspfaq.com/
(Reverse address to reply.)
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:886b01c49606$7e287f90$a501280a@.phx.gbl...
> We're experiencing the problem describe in MS Knowledge
> Base Article - 812915 "Performance monitor shared memory
> setup failed: -1" error message when you start SQL
> Server. The article states there is a support hotfix
> available through Microsoft Product Support Services.
> We're currently evaulating SQL server so we're using the
> Enterprise Evaluation Edition. We like to obtain the
> hotfix ( preferably for free ), but since we have
> evaluation edition we don't know how to go about this.
> Can we get the hotfix?
Base Article - 812915 "Performance monitor shared memory
setup failed: -1" error message when you start SQL
Server. The article states there is a support hotfix
available through Microsoft Product Support Services.
We're currently evaulating SQL server so we're using the
Enterprise Evaluation Edition. We like to obtain the
hotfix ( preferably for free ), but since we have
evaluation edition we don't know how to go about this.
Can we get the hotfix?
(a) I don't think you can apply patches and hotfixes to the evaluation
edition, but I'm not 100% positive. Did you install the latest release of
the evaluation edition, or have you been running it for a while?
(b) you can't just bypass product support and download a hotfix "for
free"... if it's a bug, and you can demonstrate to PSS that you are affected
by it, then you will be provided the file and refunded the cost of the call.
http://www.aspfaq.com/
(Reverse address to reply.)
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:886b01c49606$7e287f90$a501280a@.phx.gbl...
> We're experiencing the problem describe in MS Knowledge
> Base Article - 812915 "Performance monitor shared memory
> setup failed: -1" error message when you start SQL
> Server. The article states there is a support hotfix
> available through Microsoft Product Support Services.
> We're currently evaulating SQL server so we're using the
> Enterprise Evaluation Edition. We like to obtain the
> hotfix ( preferably for free ), but since we have
> evaluation edition we don't know how to go about this.
> Can we get the hotfix?
Subscribe to:
Posts (Atom)