Update && Oledb
Feb 26, 2004
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
View 1 Replies
ADVERTISEMENT
Dec 11, 2007
hi, please someone can help me. I get error 0x80040E14L // The command contained one or more errors. I think that the error is in the sql update command.
this is my code:
HRESULT hr = NOERROR;
IDBCreateCommand * pIDBCrtCmd = NULL;
ICommandText * pICmdText = NULL;
IRowset * pIRowset = NULL;
ICommandPrepare * pICmdPrepare = NULL;
ICommandWithParameters * pICmdWParams = NULL;
ULONG ulNumCols;
IColumnsInfo * pIColumnsInfo = NULL;
LONG lNumCols;
IAccessor * pIAccessor = NULL;
ULONG cParams;
DBPARAMINFO * rgParamInfo = NULL;
OLECHAR * pNamesBuffer = NULL;
ULONG ulNumRowsRetrieved;
HROW hRows[5];
HROW * pRows = &hRows[0];
BYTE * pData = NULL;
DBCOLUMNINFO * pDBColumnInfo = NULL;
WCHAR * pStringsBuffer = NULL;
DBBINDING * prgBinding = NULL;
DBBINDING rgBindings[2];
ULONG cbRowSize;
DBPARAMS params;
HACCESSOR hAccessor;
hr = m_pIDBCreateSession->CreateSession(NULL,IID_IDBCreateCommand,(IUnknown**)&pIDBCrtCmd);
if (FAILED(hr))
goto Exit;
//IID_ICommandWithParameters
hr = pIDBCrtCmd->CreateCommand(NULL,IID_ICommandWithParameters, (IUnknown**)&pICmdWParams);
if (FAILED(hr))
goto Exit;
hr = pICmdWParams->QueryInterface( IID_ICommandText,(void**)&pICmdText);
if (FAILED(hr))
goto Exit;
hr = pICmdWParams->QueryInterface( IID_ICommandPrepare,(void**)&pICmdPrepare);
if (FAILED(hr))
goto Exit;
LPCTSTR wSQLString = OLESTR("UPDATE Pendientes SET Pendiente = ? WHERE IDAnimal = ?");
hr = pICmdText->SetCommandText( DBGUID_DBSQL,wSQLString);
if (FAILED(hr))
goto Exit;
pICmdPrepare->Prepare(1);
if (FAILED(hr))
goto Exit;
pICmdWParams->GetParameterInfo(&cParams, &rgParamInfo, &pNamesBuffer);
hr = pICmdText->QueryInterface( IID_IAccessor, (void**)&pIAccessor);
if (FAILED(hr))
goto Exit;
rgBindings[0].iOrdinal = 1;
rgBindings[0].obStatus = 0;
rgBindings[0].obLength = rgBindings[0].obStatus + sizeof(DBSTATUS);
rgBindings[0].obValue = rgBindings[0].obLength + sizeof(ULONG);
rgBindings[0].pTypeInfo = NULL;
rgBindings[0].pObject = NULL;
rgBindings[0].pBindExt = NULL;
rgBindings[0].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
rgBindings[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
rgBindings[0].eParamIO = DBPARAMIO_INPUT;
rgBindings[0].cbMaxLen = sizeof(int);
rgBindings[0].dwFlags = 0;
rgBindings[0].wType = DBTYPE_I4;
rgBindings[0].bPrecision = 0;
rgBindings[0].bScale = 0;
rgBindings[1].iOrdinal = 2;
rgBindings[1].obStatus = rgBindings[0].obValue + rgBindings[0].cbMaxLen;;
rgBindings[1].obLength = rgBindings[1].obStatus + sizeof(DBSTATUS);
rgBindings[1].obValue = rgBindings[1].obLength + sizeof(ULONG);
rgBindings[1].pTypeInfo = NULL;
rgBindings[1].pObject = NULL;
rgBindings[1].pBindExt = NULL;
rgBindings[1].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
rgBindings[1].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
rgBindings[1].eParamIO = DBPARAMIO_INPUT;
rgBindings[1].cbMaxLen = 8;
rgBindings[1].dwFlags = 0;
rgBindings[1].wType = DBTYPE_I8;
rgBindings[1].bPrecision = 0;
rgBindings[1].bScale = 0;
cbRowSize = rgBindings[1].obValue + rgBindings[1].cbMaxLen;
hr = pIAccessor->CreateAccessor(DBACCESSOR_PARAMETERDATA,2,rgBindings,cbRowSize,&hAccessor,NULL);
if (FAILED(hr))
goto Exit;
pData = (BYTE*) malloc(cbRowSize);
if(!(pData))
{
hr = E_OUTOFMEMORY;
goto Exit;
}
memset(pData,0,cbRowSize);
*(DBSTATUS*)(pData + rgBindings[0].obStatus) = DBSTATUS_S_OK;
*(int*)(pData + rgBindings[0].obValue) = 9;
*(ULONG*)(pData + rgBindings[0].obLength) = 8;
*(DBSTATUS*)(pData + rgBindings[1].obStatus) = DBSTATUS_S_OK;
*(int*)(pData + rgBindings[1].obValue) = 0;
*(ULONG*)(pData + rgBindings[1].obLength) = 4;
params.pData = pData;
params.cParamSets = 1;
params.hAccessor = hAccessor;
hr = pICmdText->Execute(NULL, IID_NULL,¶ms,&lNumCols,NULL);
if (FAILED(hr))
goto Exit;
Exit:...
View 1 Replies
View Related
Mar 12, 2008
Is there a limit to the number of fields that can be set in an OleDB Update Statement?
This works with 6 fields:
cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000'"
This Fails with 7 fields:
cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000', F7 = '123'"
The range should be plenty big with A2:AP2. In the end I'm trying to push 42 fields.
The complete segment is:
Dim ExcelConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ExcelFileName & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim conn As New System.Data.OleDb.OleDbConnection(ExcelConnection)
Dim cmd As New System.Data.OleDb.OleDbCommand()
conn.Open()
cmd.Connection = conn
cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000', F7 = '1234'"
cmd.ExecuteNonQuery()
View 1 Replies
View Related
Jun 14, 2006
In SSIS, I have an Execute SQL Task that runs a direct SQL statement as follows to update an MS/SQL database table:
Update auditTable SET PackageName = @pkgname
In the parameters mapping, @pkgname is mapped to the System variable System::PackageName (as input).
This task runs fine if I use ADO.NET as the data source without any error.
But if I use OLEDB as the source it fails miserably and requires that the @pkgname variable be defined (as if it's a user variable, when in fact it was intended as a parm for the update statement only).
Any idea why that happens?
Thanks
Tommy
View 7 Replies
View Related
Nov 15, 2007
HI,
In my mapping i have used one OLEDB command which updates a table.
It updates 70 columns of that table.
The problem is that it is taking long time to execute that(almost 15-20 minutes)
It is updating almost 3k-5k rows.
I tried to put the update statement in SP and called that in OLEDB command.
Still it is taking same time.
Please advice how to solve this problem.
Thanks in Advance.
View 6 Replies
View Related
Dec 11, 2007
We have SS2K5 source and DB2 Target. I downloaded and installed MS OLE DB provider for DB2. It is configured correctly. The data insert works fine. But I am having problem with update/delete on DB2 when passing string parameters.
The OLEDB command works fine when I hard code the values:
delete from TableName where Col1='abc' and Col2='xyz'
But when I use parameters the package executes successfully, but the data is not delete on DB2.
Any one having similar experience or solution for this is really appreciated.
Thanks,
View 7 Replies
View Related
Dec 6, 2007
I am having a problem on updating data in DB2 target table.
I followed BJ Custard's (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1058272&SiteID=1&mode=1)(thanks amillion!) post and configured OLEDB destination to insert data. But I have to also update or delete data from the target table based on flag from source.
I tried using OLEDB command which uses the OLEDB connection created by following the steps posted in above link.
Trail 1real requirement):
When I used the SQL query:
delete from table where Col1=? and Col2=?
I am unable to map to the parameters. When I click refresh button after writing the query, I get "There is a data source column with no name. Each data source column must have a name." message. Added to before message, there are no parameters to map to.
Trail 2:
When I hard code the parameters :
delete from table1 where Col1='abc' and Col2='xyz'
no parameters will come up, so no mapping. So when I execute the mapping I get the following error:
Error: 0xC0202009 at Load .....................................................: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00.
Lookup on above error codes show those are related more to target Db2 database.
I am sure some one might have used the OLEDB command task, not only just insert task.
Any help is really appreciated.
View 14 Replies
View Related
Sep 4, 2006
Hi when i am using OLDB Command for Update Existing Records the Follwing ERror Code I am getting . Any one pls help me on this one.
1)[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Command 1" (9282)
failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method.
The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
2)
[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.
3)
[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.
View 1 Replies
View Related
Oct 10, 2006
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)
View 5 Replies
View Related
Feb 13, 2008
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
View 4 Replies
View Related
Mar 6, 2005
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?
View 1 Replies
View Related
Jul 25, 2000
Hi,
Does anyone know a good OLEDB provider for Oracle 7 other than the one of Microsoft and Oracle.
Thank
Martin
View 1 Replies
View Related
Aug 8, 2002
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 Related
Jul 7, 2004
Hi,
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!
View 4 Replies
View Related
Nov 19, 2004
can any one please give a technical defenition for OLEDB providers
View 1 Replies
View Related
Jan 13, 2004
I 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
View 5 Replies
View Related
Feb 6, 2007
I want to know the difference between SQL ODBC and SQL OLEDB.
View 1 Replies
View Related
Feb 6, 2007
What are ODBC and OLEDB
1)libraries or
2)standards
View 4 Replies
View Related
Apr 16, 2008
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.
View 11 Replies
View Related
Jun 6, 2007
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 Related
May 18, 2007
i'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
View 7 Replies
View Related
Apr 4, 2007
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...
View 4 Replies
View Related
Feb 27, 2006
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.
View 1 Replies
View Related
Jul 6, 2007
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
View 2 Replies
View Related
Sep 4, 2007
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?
View 3 Replies
View Related
Jul 30, 2007
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?
View 1 Replies
View Related
Jan 24, 2007
hi,
can i use the acquireconnection method to an oledb for oracle connection manager?
View 1 Replies
View Related
Mar 26, 2007
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...
View 12 Replies
View Related
Feb 29, 2008
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???
View 4 Replies
View Related
May 12, 2008
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?
View 3 Replies
View Related
Mar 20, 2007
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.
View 4 Replies
View Related
May 8, 2006
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?????
View 6 Replies
View Related
Jan 12, 1999
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.
View 1 Replies
View Related
