multi step OLE DB error

Hi All,

I have a MS Access DB that I have successfully u/graded to SQL Express 2005. I run my code using a recordset as normal, and all connections to the database work fine, but I receive the multi step OLE DB error when it gets to a line trying to populate an address field which has a datatype of nvarchar(max), null. (Field was Memo in Access version before).

This field in SQL2005 has allow nulls.

I've tried to add an empty string " " to the variable before being saved, but this still doesn't work.

Any ideas?

Hi,

could you please provide the code you are using as well as the exact error message ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

The exact error message received is:

-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

**************************************************************************

The code that is used, (some cut to save space) is below. All is fine, but breaks on the Physical Address line highlighted.

GetDSN gets the DSN in the DSN settings using ODBC. As I said, this worked in Access, but now in SQL, its not working.

**************************************************************************

Public Function mStaffUpdate(StaffID As Long, Title As String, FirstName As String, LastName As String, _
JobTitle As String, Department As String, Phone As String, _
Fax As String, PhoneExt As String, Mobile As String, _
HomePhone As String, Email As String, Website As String, _
CompanyLink As Long, PhysicalAddress As String, _
PhysicalCity As String, PhysicalState As String, PhysicalZipCode As String, _
PhysicalCountry As String, PostalAddress As String, _
PostalCity As String, PostalState As String, _
PostalZipCode As String, PostalCountry As String, _
UserName As String, Password As String, SecurityLevel As String)

' Open a Connection Object to the database
Dim cnnData As ADODB.Connection
Set cnnData = New Connection

cnnData.Open GetDSN

' Open a Recordset Object
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblStaff WHERE StaffID= " & StaffID
rsData.Open strSQL, cnnData, adOpenStatic, adLockPessimistic

'cut some out for less to see here...
rsData.Fields("HomePhone").Value = HomePhone
rsData.Fields("Email").Value = Email
rsData.Fields("Website").Value = Website
rsData.Fields("PhysicalAddress").Value = PhysicalAddress & " "
rsData.Fields("PhysicalCity").Value = PhysicalCity & " "
rsData.Fields("PhysicalState").Value = PhysicalState & " "

rsData.Update

' Close cnn and recordset
rsData.Close
Set rsData = Nothing
cnnData.Close
Set cnnData = Nothing

End Function

|||any ideas?|||Do you exceed the max possible characters eventually ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

No. Actually the address field doesn't need to be that large, it will only have 3-4 lines of an address. But I can't work out why this error is happenning?

|||any suggestions|||

Hi All,

Is there a different group that might be better to help with this question above?

I'm really keen to know what's the cause of this error and how to correct.

|||Moved to Data Access :-)|||can anyone help?|||any ideas?|||

What OLEDB provider are you using?

- Waseem

|||

i have this same problem,

program written in visual basic run properly on access database, but on sql server 2k sp 4 causes errors with

multi step error.

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

best

|||

mrowek wrote:

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

yeah, i just check code in vb with SQLOLEDB.1 provider

when it's saves strings to db it generate error

i put trim(cstr(blah blah)) and now it's write correctly to db without multi step error

[; check code and put this same

it should help

|||

My program occurs this error at the line below:

rs.delete <- occurs "multi-step multi step OLE DB error ......."

I spent two days for finding the resolve way....

then.....I insert "rs.AbsolutePosition = rs.AbsolutePosition" before "rs.delete", like:

.....

rs.AbsolutePosition = rs.AbsolutePosition

rs.delete

rs.update

......

and the error is gone............(maybe this is the most strange thing I have ever seen....@.@.)

multi step OLE DB error

Hi All,

I have a MS Access DB that I have successfully u/graded to SQL Express 2005. I run my code using a recordset as normal, and all connections to the database work fine, but I receive the multi step OLE DB error when it gets to a line trying to populate an address field which has a datatype of nvarchar(max), null. (Field was Memo in Access version before).

This field in SQL2005 has allow nulls.

I've tried to add an empty string " " to the variable before being saved, but this still doesn't work.

Any ideas?

Hi,

could you please provide the code you are using as well as the exact error message ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

The exact error message received is:

-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

**************************************************************************

The code that is used, (some cut to save space) is below. All is fine, but breaks on the Physical Address line highlighted.

GetDSN gets the DSN in the DSN settings using ODBC. As I said, this worked in Access, but now in SQL, its not working.

**************************************************************************

Public Function mStaffUpdate(StaffID As Long, Title As String, FirstName As String, LastName As String, _
JobTitle As String, Department As String, Phone As String, _
Fax As String, PhoneExt As String, Mobile As String, _
HomePhone As String, Email As String, Website As String, _
CompanyLink As Long, PhysicalAddress As String, _
PhysicalCity As String, PhysicalState As String, PhysicalZipCode As String, _
PhysicalCountry As String, PostalAddress As String, _
PostalCity As String, PostalState As String, _
PostalZipCode As String, PostalCountry As String, _
UserName As String, Password As String, SecurityLevel As String)

' Open a Connection Object to the database
Dim cnnData As ADODB.Connection
Set cnnData = New Connection

cnnData.Open GetDSN

' Open a Recordset Object
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblStaff WHERE StaffID= " & StaffID
rsData.Open strSQL, cnnData, adOpenStatic, adLockPessimistic

'cut some out for less to see here...
rsData.Fields("HomePhone").Value = HomePhone
rsData.Fields("Email").Value = Email
rsData.Fields("Website").Value = Website
rsData.Fields("PhysicalAddress").Value = PhysicalAddress & " "
rsData.Fields("PhysicalCity").Value = PhysicalCity & " "
rsData.Fields("PhysicalState").Value = PhysicalState & " "

rsData.Update

' Close cnn and recordset
rsData.Close
Set rsData = Nothing
cnnData.Close
Set cnnData = Nothing

End Function

|||any ideas?|||Do you exceed the max possible characters eventually ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

No. Actually the address field doesn't need to be that large, it will only have 3-4 lines of an address. But I can't work out why this error is happenning?

|||any suggestions|||

Hi All,

Is there a different group that might be better to help with this question above?

I'm really keen to know what's the cause of this error and how to correct.

|||Moved to Data Access :-)|||can anyone help?|||any ideas?|||

What OLEDB provider are you using?

- Waseem

|||

i have this same problem,

program written in visual basic run properly on access database, but on sql server 2k sp 4 causes errors with

multi step error.

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

best

|||

mrowek wrote:

i change provider from mdac to sql native client and now program is running great

and this is only one opportunity to get your program running (i think - updating mdac doesn't help me)

yeah, i just check code in vb with SQLOLEDB.1 provider

when it's saves strings to db it generate error

i put trim(cstr(blah blah)) and now it's write correctly to db without multi step error

[; check code and put this same

it should help

|||

My program occurs this error at the line below:

rs.delete <- occurs "multi-step multi step OLE DB error ......."

I spent two days for finding the resolve way....

then.....I insert "rs.AbsolutePosition = rs.AbsolutePosition" before "rs.delete", like:

.....

rs.AbsolutePosition = rs.AbsolutePosition

rs.delete

rs.update

......

and the error is gone............(maybe this is the most strange thing I have ever seen....@.@.)

multi keyword search SP

Is it possible to write a Stored Procedure that takes a string of
search keywords as argument and returns the recordset? At the mo I am
passing the WHERE String as argument.

I got this technique from an Extreme Ultradev tutorial by Rick Curtis
it looked quite ok:
http://www.princeton.edu/~rcurtis/u...tutorial12.html

I have to admit, one of the main reason for passing the WHERE string is
that I do not know how to do the string splitting / parsing and putting
together in a Stored Procedure. I bet T-SQL would be just as powerful
as VBScript if I just knew it well enough.

What I liked about having built them on the web script was the
flexibility allowing to potentially build an advanced search without
having to change the stored procedure - but this is not crucial I could
always write several stored procedures or add parameters to the SP.

Here is what I have achieved in this way:
User can enter one ore more keywords separated by space.
Search algorithm returns results across a number of fields where ALL
search words are contained in any of these.
Search results will always be formatted a certain way and displayed in
a html table no matter how the search procedure / criteria is varied.

Here is the algorithm (that now works in ASP)
1. split search string into separate keywords
2. build where condition based on single keyword, concatenating all
searched fields (" AND f1+' '+f2+{' '+f<n>} LIKE %<keyword>%")
3. concatenate all these where conditions and pass to stored procedure.
4. stored procedure takes care of all other logic (e.g. Joins, which
fields are searched etc.). It uses a string variable @.SQL to build the
complete search string and then does
execute (@.SQL);
to create the recordset.

I bet there is a way to move 1. 2. and 3. into the SP (and I would feel
better if it was) but I don't have the expertise to do this. If anybody
wants to help me this is very welcome.

I can also post my original code to clarify, just want to avoid too
long posts.

Cheers
AxelThese links might give you some ideas (the first is similar to the
article you linked to, but from a pure TSQL perspective):

http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/arrays-in-sql.html

TSQL string functions are not as powerful as those in other languages,
so if you have lots of string parsing, it would probably be best to do
it in the ASP code rather than in TSQL.

If your requirements become more complex, you could consider some sort
of reporting tool, rather than writing and maintaining code yourself.

Simon|||Thanks Simon

I had actually already read that article but it takes a different
approach - the SP takes a named list of parameters (similar to an
advanced search form) so it presumes the user knows where to look for
the keyword. My approch concatenates all candidate fields into one
large string and then looks for the search string within that long
string. It loops this process with multilpe kewoards and concatenates
using the AND condition.

If for example we assume for simplicities sake there is on table
tblProduct with the fields Name, Desc, Comment, Category, ID and I have
a record with "Iron Screw", "17 inch amalgated Screw - green", "useful
for outdoor use","consumables","1234-23"
this would be caught by passing "screw iron 17 outdoor" to the search
procedure. The user does not need to know in which fields the keywords
are contained but at the same time he will be able to narrow down the
search results very efficiently by adding more search words.
In my little loop I am comparing
'Iron Screw 17 inch amalgated Screw - green useful for outdoor use
consumables 1234-23' Like '%screw%'
AND
'Iron Screw 17 inch amalgated Screw - green useful for outdoor use
consumables 1234-23' Like '%iron%'
AND
'Iron...' Like '%17%'
AND
'Iron...' Like '%outdoor%'

what I need is like a split() and loop in T-SQL; also I would have to
eliminate invalid characters e.g. single quotes for the search string
to avoid any attempts at hacking.

I was told using dynamic SQL the way I have done here is wrong I only
want to get it right. Maybe there is another approach? MOre opinions
welcome...

thx,
Axel|||On 17 Jun 2005 03:56:57 -0700, Axel wrote:

>My approch concatenates all candidate fields into one
>large string and then looks for the search string within that long
>string.
(snip example)

Hi Axel,

The example clarifies what you want to do.

First, revisit this page: http://www.sommarskog.se/arrays-in-sql.html.
It will explain how you can break the collected search words ("screw
iron 17 outdoor" in your example) into a set of rows.

To find the Products that match at least one of the terms given, use

SELECT DISTINCT p.Product
FROM Products AS p
INNER JOIN #SearchWords AS s-- The search words in a table;
-- See link above for details.
ON p.Name + p.Desc + p.Comment + p.Category + p.ID
LIKE '%' + s.Word + '%'

And if you only want the products that match ALL the terms given:

SELECT p.Product
FROM Products AS p
INNER JOIN #SearchWords AS s-- The search words in a table;
-- See link above for details.
ON p.Name + p.Desc + p.Comment + p.Category + p.ID
LIKE '%' + s.Word + '%'
GROUP BY p.Product
HAVING COUNT(*) = (SELECT COUNT(*) FROM #SearchWords)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Axel (realraven2000@.hotmail.com) writes:
> I had actually already read that article but it takes a different
> approach - the SP takes a named list of parameters (similar to an
> advanced search form) so it presumes the user knows where to look for
> the keyword. My approch concatenates all candidate fields into one
> large string and then looks for the search string within that long
> string. It loops this process with multilpe kewoards and concatenates
> using the AND condition.

If I were you, I would look into full-text search. Not that I know
whether this is the answer to your problem, because I have never
used full-text myself. But I would look into it, before I started
to build a complex engine myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp