Обсуждение: Re: "Multiple-step OLE DB operation generated errors" in pgAdmin II:frmSQLOutput.LoadGrid

Hi Thomas,

If you run the same query but order by sid, can you inspect the last
record shown, and the next record to see if there are any odd characters
in any of the data?

Also, try adding a "WHERE sid != <sid>" to the end of the query to
exclude the last and then the following record to try to load the entire
table bar the record that is causing the problem. If we can narrow it
down to one record it should be easier to figure out.

Thanks, Dave.

> -----Original Message-----
> From: Thomas Sandford [mailto:thomas@paradisegreen.co.uk]
> Sent: 21 June 2002 22:53
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] "Multiple-step OLE DB operation
> generated errors" in pgAdmin II:frmSQLOutput.LoadGrid
>
>
> I am getting the error message
>
> "Error in pgAdmin II:frmSQLOutput.LoadGrid: -2147217887 -
> Multiple-step OLE DB operation generated errors. Check each
> OLE DB status value, if available. No work was done."
>
> when trying to do a basic query (select * from staff, or
> click the button that has the same effect) on a table with
> the following definition:
>
> CREATE TABLE "staff" (
>   "sid" int4 DEFAULT nextval('"staff_sid_seq"'::text) NOT NULL,
>   "lastname" varchar(128),
>   "firstname" varchar(128),
>   "preferred_contact_method" int4,
>   "initials" varchar(32),
>   "title" varchar(64),
>   "dob" date,
>   "emergency_contact_name" varchar(256),
>   "e_c_relationship" varchar(256),
>   "e_c_address_1" varchar(256),
>   "e_c_address_2" varchar(256),
>   "e_c_city" varchar(256),
>   "e_c_phone_day" varchar(256),
>   "e_c_phone_eve" varchar(256),
>   "e_c_phone_mob" varchar(256),
>   "e_c_email" varchar(256),
>   "sex" varchar(6),
>   "food_hygene_cert" bool,
>   "food_hygene_cert_expiry_date" date,
>   "first_aid_cert" bool,
>   "first_aid_cert_expiry_date" date,
>   "pgp_member" bool,
>   "pgp_member_from" date,
>   "smoker" bool,
>   "medical_notes" text,
>   "general_notes" text,
>   "tech_experience" int4,
>   "house_experience" int4,
>   "cafe_experience" int4,
>   "inactive" bool,
>   "e_c_county" varchar(128),
>   "e_c_post_code" varchar(64),
>   "e_c_country" varchar(128),
>   CONSTRAINT "staff_pkey" PRIMARY KEY ("sid")
> ) WITH OIDS;
> REVOKE ALL ON "staff" FROM PUBLIC;
> GRANT ALL ON "staff" TO "user1";
> GRANT ALL ON "staff" TO "user2";
>
> Here is the pgadmin (1.20) log at logging level "debug".
>
> 21/06/2002 22:42:48 - Counting Records...
> 21/06/2002 22:42:48 - SQL (PGP_Staff): SELECT count(*) AS
> count FROM "staff" 21/06/2002 22:42:48 - Done - 0.05 Secs.
> 21/06/2002 22:42:48 - Executing SQL Query... 21/06/2002
> 22:42:49 - SQL (PGP_Staff): SELECT * FROM "staff" 21/06/2002
> 22:42:49 - Loading Data... 21/06/2002 22:42:49 - Done - 0.5
> Secs. 21/06/2002 22:42:49 - Error in pgAdmin
> II:frmSQLOutput.LoadGrid: -2147217887 - Multiple-step OLE DB
> operation generated errors. Check each OLE DB status value,
> if available. No work was done.
>
> There are 115 records in the "staff" table. After the error
> is reported a display grid comes up, with 70-odd records
> displayed. I was able to view this table before I had as many
> records. Running the query within psql on the server causes
> no problems.
>
> Any suggestions?
> --
> Thomas Sandford
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>

"Dave Page" <dpage@vale-housing.co.uk> wrote:
> Thomas Sandford [mailto:thomas@paradisegreen.co.uk] wrote:
> > I am getting the error message
> >
> > "Error in pgAdmin II:frmSQLOutput.LoadGrid: -2147217887 -
> > Multiple-step OLE DB operation generated errors. Check each
> > OLE DB status value, if available. No work was done."
> >
> > when trying to do a basic query (select * from staff, or
> > click the button that has the same effect) on a table with
> > the following definition:
> > ...
> If you run the same query but order by sid, can you inspect the last
> record shown, and the next record to see if there are any odd characters
> in any of the data?
>
> Also, try adding a "WHERE sid != <sid>" to the end of the query to
> exclude the last and then the following record to try to load the entire
> table bar the record that is causing the problem. If we can narrow it
> down to one record it should be easier to figure out.

OK - I've found the problem  - two records had [valid but] peculiar dates -
we were trying to backfill the database from manual records.

You can replicate the problem by creating a record from random [valid] data,
then running the update query:

update staff set dob = '0001-01-01 BC' where sid = <your record number>

This will reproducibly produce the error noted when the table is queried
(brief experimentation suggests that any BC date will do).

It's no longer a problem for me (I've simply set the dob in the records
concerned to NULL - I don't have many staff over 2000 years old!!) but I
guess you might want to see if the error is Microsofts or in PGAdmin
(someone might have more need to store BC dates than I do).

As stated before - I am working with PGAdmin 1.2.0.
--
Thomas Sandford | thomas@paradisegreen.co.uk

> -----Original Message-----
> From: Thomas Sandford [mailto:thomas@paradisegreen.co.uk]
> Sent: 23 June 2002 10:31
> To: pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] "Multiple-step OLE DB
> operation generated errors" in pgAdmin II:frmSQLOutput.LoadGrid
>
>
> It's no longer a problem for me (I've simply set the dob in
> the records concerned to NULL - I don't have many staff over
> 2000 years old!!)

:-) glad it's working.

> but I guess you might want to see if the
> error is Microsofts or in PGAdmin (someone might have more
> need to store BC dates than I do).

It's almost certainly Microsoft ADO - it's that that raises the error
you got (pgAdmin certainly doesn't) - I would guess as soon as it trys
to parse the BC date that the ODBC driver has just handed it.

:-(

Regards, Dave.