I'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
Hi 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)
Hi.
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?
Hi,
Does anyone know a good OLEDB provider for Oracle 7 other than the one of Microsoft and Oracle.
Thank
Martin
Is 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 RelatedHi,
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!
can any one please give a technical defenition for OLEDB providers
View 1 Replies View RelatedI 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
Dear 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
I want to know the difference between SQL ODBC and SQL OLEDB.
View 1 Replies View RelatedWhat are ODBC and OLEDB
1)libraries or
2)standards
Hi,
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.
Is 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 Relatedi'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
I 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...
I 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.
Hi
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
I 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?
I 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?
hi,
can i use the acquireconnection method to an oledb for oracle connection manager?
I 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...
I am porting a VB.NET application using Access as its backend to use SQL Express as its new backend.
The application uses .NET 2.0 OleDbProvider to connect to the database.
The porting of the database itself to Sql Express is nearly complete, now I need to modify the application.
So, does it really make sense to modify it to use Native Sql Client instead of OleDb in terms of effort & benefits???
Moreover, if I continue using OleDb, would I need to make such small changes to embedded Sql statements like using single quotes around strings or dates instead of double quotes or pound symbols that are currently being used for String or date data respectively???
Where can I get "sybase ASE oledb driver" ? I tried to get the source data from Sybase using SSIS. But I don't see an option for "sybase OLEDB driver " under the connection manager. I tried to google and went to sybase.com but still couldn't find it. could someone help?
I am transfering large data.
I use oledb command to insert and update as i need to make some modifications to incoming data.I do my modifications in the procedure.
But the command does not insert as the data is huge at one shot.
if i try to send small data it works fine
Its shows warning(yellow color)
How can i achive inserting huge data effeciently please help.
hi i have another question.. is this connection provider supported on servers running under 64 bits???? if not? which provider should i use instead??? is there any service pack or upgrades for this ???
i think this is the problem running my job.. there's no other option left.. it is not permissions.. the path of the connection manager exists, the file destination does exist as well.. the curious thing is that my dts runs fine from SSIS but fails using the job..
do i need to have installed excel in my server to do this or any other component?????
We 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.
Hi,
i'm a little bit in trouble. before i have syabase 12.x and
successfully linked our sybase database to sqlserver 2k and
2k5. when our company upgraded sybase to 15.x. i followed
the document
http://www.sybase.com/content/1029118/1029118.doc in linking
sybase 12.5 to sqlserver. but when i install the sybase
15.x openclient i can find the SYBASE OLEDB CONFIGURATION.
where i can find it?? or sybase change the SYBASE OLEDB
CONFIGURATION????
please help...
tengtium
I've got an intranet page that connects to my SQL server, and I'm trying to force it to use NT Authentication when it does.
Already the Intranet uses NT Challenge-and-response authentication so that when I go to an intranet page I have to login. But then when I go to the page that connects to SQL, I get:
Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'IUSR_COMPUTERNAME'.
Why isn't it passing MY user id? Why is it trying to use the anonymous IUSR id?
Hi,
I am using WIN XP, SQL Server 2000 database and vb, asp application running on MTS. I have a transaction where it will process 500 items and write to the tables in database. When opening the connection I tried to cache the connection and made use of it when ever the code is called. Here is how I did it.
Public Function GetCachedADOConn(ByVal eConnect As DBConnectVar, Optional ByVal blnUseShapeRS As Boolean = False) _
As ADODB.Connection
Dim cnnADO As ADODB.Connection
On Error GoTo EH
If m_CachedConnection Is Nothing Then
Set cnnADO = New ADODB.Connection
' Check if we need to load the cache first...
If Len(mstrConnect(eConnect - 1)) = 0 Then Call CacheOneConnectionSet(eConnect)
' Now just return the cached data
With cnnADO
If blnUseShapeRS Then
.ConnectionString = "Provider=MSDataShape;Data " & mstrConnect(eConnect - 1)
Else
.ConnectionString = mstrConnect(eConnect - 1)
End If
.CommandTimeout = mintCommandTimeout(eConnect - 1)
.ConnectionTimeout = mintConnectTimeout(eConnect - 1)
.CursorLocation = adUseClient
.Open
End With
Set m_CachedConnection = cnnADO
End If
Set GetCachedADOConn = m_CachedConnection
'Set cnnADO = Nothing
Exit Function
Here the main part is making use of m_CachedConnection as a public variable declared as adodb.connection.
When I run the application it throws me an error
Unable to establish connection(Micorsoft OLEDB Provider for SQL Server , New transaction cannot enlist in the specified transaction coordinator.)
Can some one help me How I can address the above issue.
Thank you,
Nagesh.
I have a small archiving SSIS pacakge that I use to archive off old orders. I was OLEDB and found that I would be deleting about 1 order (plus all its relational records) a second. Try to find some speed I tried the ADO.net connetion and turn pooling on with a min of 10. Now I am getting about 100 order records deleted a second.
Has any one else found that that ADO.net connection is faster than the OLEDB (I would have thought it would be the other way around).
The
Control flow items I am using are the Execute SQL Task and the queries are like this
Insert into arcprod.wh1.pickdetail
Select * from wh1.pickdetail where status='9' and orderkey = @Pram1 and
pickdetailkey not in (select pickdetailkey from arcprod.wh1.pickdetail)
...
delete from wh1.pickdetail where orderkey = @Pram1
etc
How do I write the record set to a .csv file using oledb in vb.net? If anyone has sample code to write to .csv file, I greatly appreciated.
Hi All,
We want to take advantage of the performance benefit provided by SQL server destination in our packages. We are using a configuration variable to specify whether the SQL Server is remote or local to the packages. We are using a conditional split to redirect the process to either SQL Server destination or OLEDB destination based on the value of the variable. Is there any performance benefit in doing such a thing as it seems that the connection is made in both the paths during the runtime instead of in one particular path alone.
Thanks in advance
Kumbs
I have discovered some shortcomings in the way inline table valued function parameters are treated in the OLEDB datasource. You can select the user designed function ine the Generic Query Builder and test it with the required parameters. However when you attempt to set up the parameters for the result ing SQL Command Text you get and error message to the effect that the parameters cannot be retrieved from the datasource. Once again this is disappointing because Report Services seems to deal with the parameters perfectly well.
Dick Campbell