SQL ODBC And SQL OLEDB
Feb 6, 2007I want to know the difference between SQL ODBC and SQL OLEDB.
View 1 Replies
ADVERTISEMENT
ODBC Vs OLEDB
Aug 8, 2002Is anyone using OLEDB instead of ODBC? If so why did you choose to use OLEDB vs ODBC? Our application is written in VB and we have a developer that believes switching to OLEDB from ODBC would be beneficial to us. Please give any information that you have! Thanks.
View 1 Replies View RelatedADO Using ODBC Vs. OLEDB
Jan 13, 2004I am working on a large ASP application(s) which uses SQL Server 2K on the back end, ADO 2.7 for Data Access. The current connection strings are setup to use older ODBC drivers. I wanted to change them to OLEDB for performance gains and better support in the future.
The problem encountered is that once the driver is changed the pages become riddled with errors. There are many piece of ADO code that are supported using the new driver. Many are cursor issues or code dealing with identity columns.
Has anyone else gone through this process?
Is there a comprehensive listing of those methods that are not compliant between these two drivers?
Any input would be appreciated....Thanks
ODBC And OLEDB
Feb 6, 2007What are ODBC and OLEDB
1)libraries or
2)standards
OLEDB, ODBC Connectivity To DB2 400
Jan 12, 1999We are trying to extract data from DB2 on an as400 machine. Can we simply use SQL statements to extract the proper data that we need from as400 using the SQL Server 7.0 functionality? How do we do that?
We think this needs to be done via DTS(Data Transformation Service).
Thanks in advance.
Shaikh & Sande.
What Is Difference Between Odbc, Oledb, Sql And Oracle
Mar 6, 2006i notice that in toolbox panel, there are these kind of different dataadapter and dataset, what is difference between them, and under which situation we use which one?
View 2 Replies View RelatedLocal Temp Tables And OLEDB/ODBC
May 5, 2008I have recently switched from ODBC to OLEDB and I have noticed a difference in the way these two handle local temp tables. With ODBC the scope of the local temp tables is the connection in which they were created (unless they are created in a stored proc, in which case the scope is the stored proc). That means you can create the temp table in one statement, fill it with data in another and retrieve the data in the third, and everything works fine as long as these statements are bound to the same connection. I have used this in a few places in my applications as a way to store connection specific data and a way to simplify some other things.
But now I have a problem. In OLEDB the scope of the temp tables is the session it was created in and that is not good for me since I can not store connection specific data in temp tables any more.
Any ideas?
Thank you!
Performance Issues With Linked Server's With OLEDB && ODBC
Jan 6, 2004Hello,
I have a SQL Server instance on my local computer and an Oracle
Database on a remote server. I want to run queries from tables
within both databases and am using linked servers to accomplish
this.
I configure my linked server in SQL Server using the Microsoft
OLE DB Provider for Oracle and can run queries using sql server
tables and oracle tables. However, even the simplest queries
take more than 10 minutes to run.
I have the Oracle 9 Client Installed and MDAC 2.7. I configured
my registry settings to match oracle 9's settings.
However nothing i do improves the performance of the queries
through the Microsoft OLEDB Provider for Oracle.
When I use MS Access, or use an ASP page with the following
string:
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "dsn=Oberon;uid=mfs;pwd=mfs;"
I implement the ODBC driver that I configured in my system DSN
and both run the same queries very fast. The data comes back
without a problem.
So i believe I have narrowed down my problem to the OLEDB
Provider. However, SQL Server does not give me a choice to use
the ORACLE native ODBC Provider.
So then I tried using Pass-Through Queries and this worked alot
faster in SQL Server...I am completely confused as to whats
going on.
Linked Server Query that takes over 10 minutes:
SELECT * FROM OBERON..LOGS.DATA_PHOTO ldp where Machine=301 AND C3='I051097';
Pass-Through Query that works faster:
select * from Openquery(OBERON, 'SELECT * FROM LOGS.DATA_PHOTO ldp where MACHINE=301 AND C3=''I051097''')
From researching pass through queries, my understanding is that
it actually uses ODBC to give the whole query to the remote
database where the query is then run and the results are passed
back as a table, thats why you say select * from (query)...however if my understanding is correct, then you cant combine tables in different databases very easily. And will it work with .asp and .aspx pages?
Anybody have any insight as to whats going on?
Convert Query From OLEDB To ODBC (Receiving Error)
Jan 10, 2008I have the following query for my site which was functioning great using the OLEDB driver. Unfortunately, my client's web host forced us to use ODBC and it is no longer working Any suggestions for what I should do?
Here is the query:
SELECT t.*
FROM (SELECT TOP 10 itemnum, itemnum FROM (
SELECT TOP 100 itemnum, itemnum
FROM(CatView)
WHERE [CATALOG PAGE] LIKE 'SM%'
ORDER BY itemnum ASC) AS foo
ORDER BY itemnum DESC) AS bar INNER JOIN CatView AS t ON bar.itemnum = t.itemnum
ORDER BY bar.itemnum;
Here is the error i'm now receiving:
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The specified field 'itemnum' could refer to more than one table listed in the FROM clause of your SQL statement.
I know it was working great before, so i'm not sure what to do.
Michael
ODBC/OLEDB Failure Accessing MS Access Attachment Field In Query
Mar 12, 2008We are experiencing failures when accessing a datatype="Attachments" field in a query in an MS Access 2007 database using ACE ODBC or OLEDB drivers.
We are using an MS Access 2007 database
ACE ODBC/OLEDB drivers installed (i.e. Office 2007, which installs these drivers)
DB contains a table with a field of type "Attachments" (which is new in MS Access 2007)
DB contains a query that selects the fields of the above table
Using ACE ODBC or ACE OLEDB drivers to display the table works fine. The "Attachments" field is displayed as the file name of the attachment(s).
However, using either ACE ODBC or ACE OLEDB to display the query (i.e. NOT the table) results in either incorrect results or unexpected failures of the ODBC/OLEDB drivers.
Error using ODBC (using "ODBC Test"):
=============================
select * from Query1 does not give an error, but displays a "1" for the Attachment field.
select Attachments from Query1 gives the following error:
stmt: szSqlState = "HY000", *pfNativeError = -3087, *pcbErrorMsg = 97,
*ColumnNumber = -2, *RowNumber = -2
MessageText = "[Microsoft][ODBC Microsoft Access Driver] Reserved error (|);
there is no message for this error."
Error using OLEDB (using "RowSetViewer"):
================================
select * from Query1 gives the following error:
Interface: Unknown
Result: 0x0004001 = E_NOTIMPL
FormatMessage: €œNot implemented
File: F:DepotSQLVaultmdac28sdkSamplesoledb
owsetviewerSDKobji386CRowset.cpp
Line: 616
select Attachments from Query1 gives the following error:
Interface: IID_ICommand
Result: 0x0004005 = E_FAIL
IErrorInfo: [0x0000f3f1] €œUnspecified error€?
File: F:DepotSQLVaultmdac28sdkSamplesoledb
owsetviewerSDKobji386CCommand.cpp
Line: 439
If it would help to analyze the problem, I have a folder containing all pertinent files (bare-bone database, tools, instructions) to reproduce this. I could attach it as a ZIP file, if requested.
Thanks,
Joe
Error When An OLEDB Source Points To An OLEDB Destination.
Oct 10, 2006Hi all,
I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.
But i got the below error:
Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.
Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow TaSK: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
Integration Services :: Unable To See 64bit ODBC Driver From SSIS Odbc Connection?
Jun 1, 2015I am using SSIS 2014 with the below .net framework version and installed in Windows server 2012 R2 . I have installed my client's odbc drivers (both 32 bit and 64 bit) in my production server and created ODBC system DSNs for 32 bit and 64 bit.
When i open SSIS 2014 and tried to create the odbc connection but i can able to see only the 32 bit system DSN connection ,i can't able to see my 64 bit odbc system dsn connection.
Microsoft Visual Studio 2012 Shell (Integrated)
Version 11.0.50727.1 RTMREL
Microsoft .NET Framework
Version 4.5.51650
SQL Server Integration Services  Â
Microsoft SQL Server Integration Services Designer
Version 12.0.1524.0
And i installed my client odbc drivers(32,64 bit) and created ODBC system DSNs in my local system and when i open ssis 2014 and i can able to see both the ODBC system DSNS(32,64) connections from SSIS ODBC connection.
I am using below version of .net framework in my local system which was installed in windows 7 and i have SSIS 2012 also installed in my system and i can able to see both ODBC connections using 2012 as well in my local system.
Microsoft Visual Studio 2012 Shell (Integrated)
Version 11.0.50727.1 RTMREL
Microsoft .NET Framework
Version 4.5.50938
SQL Server Integration Services  Â
Microsoft SQL Server Integration Services Designer
Version 12.0.1524.0
why i can not see the ODBC 64 bit system DSN connection from SSIS in my production server ?
How Can I Use Create Or Alter Statements With ODBC And Microsoft Access ODBC Driver (*mdb)?
May 13, 2007
Hi,
I am using VB.NET 2005 and set up an ODBC connection via ODBC.ODBCConnection to a MDB database. Therefor, I use the "Microsoft Access ODBC Driver (*.mdb)".
When I set up a ODBCCommand like "ALTER DATABASE..." or "CREATE TABLE..." and issue it with the com.ExecuteNonQuery() command, I get an error from ODBC driver, that a SQL statement has to begin with SELECT, INSERT, UPDATE or DELETE.
How can I use DDL statements via ODBC?
I would appreciate if you could help me to use ODBC for that - no OLE, no ADO.
Thanks for help!
Regards,
Stefan D.
Can't Find SQL Native Client In ODBC Connection Manager In SQL Server Open Database Connectivity (ODBC)
Feb 13, 2007I apologize if this is not the correct forum for this posting. Looking at the descriptions, it appeared to be the best choice.
I am running Windows XP Pro SP2. I have installed the SQL Native Client for
XP. However, when I try to add a new data source through ODBC Connection
Manager, SQL Native Client is not listed as an option. I have followed this procedure on three other systems with no problems. What would be causing the
SQL Native Client to not show up in the list of available ODBC data sources?
MS OLE DB For ODBC With Oracle ODBC Source Headaches
Dec 25, 2005Hi all,I am having trouble getting linked Oracle 9 server in MS SQL Server2005 Express to work properly. My machine is running Windows XP.The Microsoft and Oracle OLE DB Providers have problems dealing withOracle's Numeric Data Type, so I decided to use Microsoft's OLE DB forODBC Provider and an Oracle ODBC source. When using the Microsoft ODBCfor Oracle Driver in my ODBC source I have inconsistent behavior.Sometimes my queries are processed properly, then other times I get thefollowing errorOLE DB provider "MSDASQL" for linked server "ODBCBEAST" returnedmessage "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttrfailed".OLE DB provider "MSDASQL" for linked server "ODBCBEAST" returnedmessage "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttrfailed".OLE DB provider "MSDASQL" for linked server "ODBCBEAST" returnedmessage "[Microsoft][ODBC driver for Oracle][Oracle]".Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "MSDASQL"for linked server "ODBCBEAST".I have no idea why sometimes I can connect to the linked server with noproblems andwhy other times it performs like this. I'm not changing anything aboutthe system I can think of. When I use an Oracle client (PL/SQL) I haveabsolutely no problems connecting. TNSPING returns that the connectionis good.This is unacceptable so I decided to try my luck with the Oracle 10gODBC driver. However when I use this and perform an openquery selectagainst the linked server I get back only 11 rows, when I know that thedatabase has over 100 rows (in fact when using the Microsoft ODBCdriver and it works that's what I get). I figured maybe the buffersetting needed to be raised in the ODBC configuration so I took it from64000 to 600000 (a magnitude of 10) but I still get back only 11 rows.I'm at my wit's end.Any suggestions on resolving one or the other problem would be muchappreciated.Thanks much
View 2 Replies View RelatedCompatible Versions Of Ms SQL Server, ODBC Driver, Driver Manager And ODBC API Spec
Oct 28, 2004Hi,
Please help share with me if you know the version compatibility matrix of Ms SQL Server, ODBC driver (sqlsrv32.dll), Driver Manager (odbc32.dll) and ODBC API spec. For instance, how can I know Ms SQL Server 2000 can work with which version of sqlsrv32.dll, a particular version of sqlsrv32.dll can work with which version of odbc32.dll and a certain version of sqlsrv32.dll/odbc32.dll conforms to which version of ODBC API spec (e.g. 3.5).
Any help will be appreciated.
Thanks,
vtluu.
Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Dec 10, 2003Hi Everybody,
On localhost this application works fine but when I put on remote server. I am getting following errors. For both localhost and server, I am using same remote sql 2000. I will appreciate any help.
Thanks,
Arif
Server Error in '/' Application.
--------------------------------------------------------------------------------
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.]
Microsoft.Data.Odbc.OdbcConnection.HandleError(IntPtr hHandle, SQL_HANDLE hType, RETCODE retcode) +27
Microsoft.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method) +838
Microsoft.Data.Odbc.OdbcCommand.ExecuteNonQuery() +80
Calgary.venues.Page_Load(Object sender, EventArgs e) in c:inetpubwwwrootCalgarySitevenuesvenues.aspx.vb:32
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
Jet OLEDB
Feb 13, 2008I'am sure this is an old quesiton but I've spent the whole morning trying to find the answer
What are the string functions suported by Jet in OLEDB queries? In particular, I am desperately looking for a replace function to remove thousands separators from imported text file
SqlClient Vs. OleDb
Mar 6, 2005Hi.
Somewhere around I find a message, which affirmed that for perfonmance it is better to use OleDb for SQL 6.5. Is this true?.
Then el SqlClient, only have perfomance in SQL 2000?
OLEDB Provider
Jul 25, 2000Hi,
Does anyone know a good OLEDB provider for Oracle 7 other than the one of Microsoft and Oracle.
Thank
Martin
OLEDB Providers
Jul 7, 2004Hi,
I have a DTS package which is using OLEBD provider for Oracle, it was working fine. I tried to open this package and I got this error message "There was a problem retrieving the list of OLE DB Providers"
I tried to intall MDAC8.2, it didn't help, should I reinstall SQL Server?
Thanks!
OLEDB Providers
Nov 19, 2004can any one please give a technical defenition for OLEDB providers
View 1 Replies View RelatedUpdate && Oledb
Feb 26, 2004Dear gurus,
My problem: I have developed an application based on sqloledb to access a SQLserver application.
One of the operations is to update certain column of a certain table. This works fine. But with a specific catalog (database) of a specific customer the update hangs for ever on the execution of that sql sentence. Size of the mdb file is around 1 Gb.
The funny thing is that if I execute the same sql sentence (same record and same catalog) from the Query Analyzer ( that I belive it uses odbc ) the operation is done.
The update sql sentence does an update on a non-indexed column with the criteria ( where ...) using a index column (non-clustered)
I have tried the sqlmaint to rebuild indexes and check integrity and no special error report is given.
Also another funny behaviour is that if I stop and I restart sqlserver, the update of that specific record fails, but it continues of the following records to be updated.
This is not executed inside a transaction. Candidate records to be updated are stored in memory (maximum 1000), and one by one are updated.
Does anyone know an specific tool to integrity or to monitor what is going on?
I have tried the profiler , what can I monitor to detect this lock?
Best regards,
Jose
Need Help Regarding OLEDB. RetainSameConnection.
Apr 16, 2008Hi,
I'm having two For Each Loops, which allow me to dynamically connect to a list of servers.
ForEach 1
ForEach2
DataFlow.
Now for ForEach2 i want to OLEDB Connection to RetainSameConnection, however i want it to Disconnect-Reconnect while looping in ForEach1.
How can i do that?
Can i write a script which will can access OLEDB connection object ??
Thanks
Sandeep.
OLEDB Provider For SAS
Jun 6, 2007Is there an OLEDB provider for SAS? Does anyone know where I could get one? Does it ship with the SAS installation? Thanks!
View 1 Replies View RelatedIs It A Bug In SSCE OLEDB ?
May 18, 2007i'm use this code ,in SQL2005 std and ACCESS database, it work
but if i use SSCE ,it's throw a OleDbException in ExecuteScalar()
Exception : OleDbException
0x80040E30L
DB_E_BADTYPENAME
Code Snippet
OleDbConnection od = new OleDbConnection("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=db.sdf;SSCE:Database Password=");
od.Open();
OleDbCommand og = new OleDbCommand("INSERT INTO [bills] ([billno],[checkouttime],[finalprice],[handle],[ischeckout],[memo],[paymode],[trick]) VALUES (@billno,@checkouttime,@finalprice,@handle,@ischeckout,@memo,@paymode,@trick)", od);
og.Parameters.Add("@billno", OleDbType.VarWChar).Value = "2007051800000000";
og.Parameters.Add("@checkouttime",OleDbType.DBTimeStamp).Value="2007-5-18 11:55:40";
og.Parameters.Add("@finalprice", OleDbType.Single).Value = 0.0;
og.Parameters.Add("@handle", OleDbType.VarWChar).Value = "admin";
og.Parameters.Add("@ischeckout", OleDbType.SmallInt).Value = 0;
og.Parameters.Add("@memo", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@paymode", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@trick", OleDbType.VarWChar).Value = "";
og.ExecuteScalar();
od.Close();
why the same code is not work? i'm find all MSDN ,but there is no answer
Who can help me,Thanks
OLEDB For DM Express!!!
Apr 4, 2007I am pursuing a course on Data Warehousing & Data Mining as part of my Post-Graduation degree. I got an introduction to Microsoft's OLEDB for DM as part of the course, which got me interested for experiencing it hands-on.
I developed a traditional Database application for my college's library using the .NET 2.0 framework, which is running fine.
Now, I want to see the power of Data Mining in general & OLEDB for DM in particular, to help me mine information & patterns from the mostly transactional data generated by the library daily, like the rush hours, the average issue duration for a book, books that are issued more frequently, books that are issued to students from multiple streams etc. etc.
Now, I have heard MSOLAP provider, ADOMD data provider, SQL Server Analytical Services, OLEDB for DM libraries etc. are required for Data Mining.
Now, what exactly is each of these tools? Are they independent servers for DW & DM like SQL Server for DBs, or they are libraries for extending SQL Server itself?
Moreover, are separate libraries required for OLAP & Mining?
I have SQL Server Express Edition. I believe that they are extensions for SQL Server. So, will they work with Express Edition? If not, are their Express Editions available (like OLEDB for DM Express!!!!) that are compatible with SQL Server Express.
Thanx in advance if you take time to answer so many questions...
Version Of OLEDB / ADO
Feb 27, 2006I want to use SQL 2005 to import data from Visual Foxpro 6.0 free table / database into SQL 2005. Whethter I should install the latest Microsoft OLE DB Provider for Visual FoxPro 9.0 SP1 or previous mdac ???
Please advice and many thanks.
OLEDB Access PB
Jul 6, 2007Hi
I definite a source OLEDB MS Access and my fields of the type €œText€? are seen in type of field DT_WSTR (Unicode) instead of DT_STR.
I do not include/understand why? and like then, I must integrate them in fields varchar and not nVarchar, I do not find the solution?
thank for your solution
OLEDB Bookmarks
Sep 4, 2007I generated an ATL OLEDB Consumer with the VS 2005 wizard. The table is very simple and is located on my local SQL Server. It has two varchar columns and no keys or constraints. I can modify, insert and delete rows with no problem.
Now I want to use bookmarks. I added the following to the accessor class:
1. CBookmark<4> m_bookmark; // member variable
2. pPropSet->AddProperty(DBPROP_IRowsetLocate, true); // rowset property
3. BOOKMARK_ENTRY(m_bookmark) // column map entry
Now I can delete but I can no longer modify or insert. The error is DB_E_ERRORSOCCURED and the status of the two columns after a setdata or insert is DBSTATUS_E_UNAVAILABLE. This is the only error information available.
When I comment out the bookmark entry from the column map, I can modify, insert and delete again. The problem appears to be in how IRowsetChange handles bookmarks. I tried adding bookmarks to other tables using the above 3 steps and they also refuse to modify or insert with bookmarks enabled.
The bookmarks appear to be correct, as I can do a successful MoveToBookmark for any row in the table.
My environment: Windows XP SP2, VC++ 2005, SQL Server 2005 Express, WTL 8.0
Any ideas?
How To Verify A DB Via OLEDB?
Jul 30, 2007I cannot find any OLE DB reference material for how to perform a "Verify" DB as advertised in the Documentation for SQL Server Mobile Edition.
The docs have a sample in C#, but nothing for OLD DB in C++.
Does anyone know how to do this?
Oledb For Oracle
Jan 24, 2007hi,
can i use the acquireconnection method to an oledb for oracle connection manager?
Issue Oledb
Mar 26, 2007I use oledb to insert the data to respective tables..
then i have a look up where i check for unique field of the data i inserted which shows up with an error No matter what i do...