Consultor Eletrônico

Status: Verified

FACT(s) (Environment):

MS SQL DataServer

SYMPTOM(s):

Error 6227 occurs when running a query against a table, followed by a GPF

SYSTEM ERROR: _type_sqlc: Bad datatype -11 (6227)

Data type "uniqueidentifier" present within a field in the table queried within SQL Server Database

CAUSE:

The data type Globally Unique Identification (GUID) is not supported by MS SQL Server DataServer

FIX:

There are two workarounds:

1. Create a view in the SQL Server Database excluding the uniqueidentifier (GUID) columns so only supported data types are in the view. Then pull the views instead of the tables containing the unsupported data type in the schema holder.

OR,

2. Another possible workaround to manipulate (view, insert, update, delete) the data in the Microsoft SQL database is to use RUN STORED-PROCEDURE statement with the send-sql-statement option to pass SQL statements to the MS SQL server directly.

If you have uniqueidentifier data type, most probably, it was setup as a default value with NEWID function (NEWID()) in the MS SQL database. This way the hexadecimal value of this data type is assigned automatically by the system. Since this data type is not supported in the SQL Server DataServer, you need to find other field(s) to identify a particular record for data manipulation purposes.

Although the value of the uniqueidentifier data type being a hexadecimal may not be a meaningful data to the end user if displayed, the following example includes the display of that field's data.

The illustration shown below is a simple MS SQL test database with a customer table with just three fields:

You can use SQL Server query analyzer tool to execute the following SQL to create customer table with three records:

This example is taken and modified from referenced Microsoft site.

-- Creating a customer table using NEWID function for uniqueidentifier data type.

CREATE TABLE customer ( custid uniqueidentifier NOT NULL DEFAULT newid(), company varchar(30) NULL, contactName varchar(60) NULL
)
GO

-- Inserting data into customer table.

INSERT customer (custid, company, contactName) VALUES(newid(), 'Progress', 'John Dow')
INSERT customer (custid, company, contactName) VALUES(newid(), 'Sonic MQ', 'Rob Lowe')
INSERT customer (custid, company, contactName) VALUES(newid(), 'NuSphere', 'Jim Dowson')
GO

Create a schemaholder for the above SQL database. While connected to the schemaholder and SQL database, use the procedure editor to run the following code. You can also copy and paste all the code below in the procedure editor to run the code assuming you created a table in MS SQL Server database as stated above.

/******************************************************************/
/*To display the record use the following code: */
/******************************************************************/

DEFINE VAR handle1 AS INTEGER.
RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE

("select CONVERT(VARCHAR(256), custid), company, contactName from customer where company = 'Progress'").

FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
DISPLAY proc-text-buffer.
END.

CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

/*****************************************************************/
/*To maintain a record use the following code: */
/*****************************************************************/

/* To insert the data using Procedure editor use the following syntax: */

/* ("insert customer (company, contactName) VALUES('Progress', 'John Dow') "). */

/* OR */

/* ("insert customer (custid, company, contactName) VALUES(newid(), 'Progress', 'John Dow') "). */


/*This example will display a record and then update the same record*/

DEFINE VAR handle1 AS INTEGER.
DEF TEMP-TABLE bproc LIKE proc-text-buffer.

RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE

("select CONVERT(VARCHAR(256), custid), company, contactName from customer
WHERE company = 'progress'").

FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1 :
CREATE bproc.
ASSIGN bproc.proc-text = proc-text-buffer.proc-text.
END.

CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE .= handle1.

FOR EACH bproc:

MESSAGE
NUM-ENTRIES( bproc.proc-text, "'" ) SKIP
/* proc-text */
/* entry( 1,bproc.proc-text, "'" ) skip */
entry( 2, bproc.proc-text, "'" ) skip
/* entry( 3, bproc.proc-text, "'" ) SKIP */
entry( 4, bproc.proc-text, "'" ) skip
/* entry( 5, bproc.proc-text, "'" ) SKIP */
entry( 6, bproc.proc-text, "'" ) SKIP

VIEW-AS ALERT-BOX
.

RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE

( "update customer " +
"set customer.company = '" + "my test" + "'" +
"where customer.company = '" + entry( 4, bproc.proc-text, "'" )
+ "'" +
"and customer.contactName = '" + entry( 6, bproc.proc-text, "'" ) + "'"
).

/* To delete the record use the following syntax: */

/* ( "delete from customer " + */
/* "where customer.company = */
/* '" + entry( 4, bproc.proc-text, "'" ) + "'" + */
/* "and customer.contactName = */
/* '" + entry( 6, bproc.proc-text, "'" ) + "'" */
/* ). */


CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

END..