Operand type clash: text is incompatible with uniqueidentifier

Question

I've seen similar error messages, but most have to do with comparing int or float to uniqueidenifier, which makes sense why you'd get an error. My error is this:

SQLSTATE[22018]: Invalid character value for cast specification: 206 [Microsoft][SQL Server Native Client 11.0][SQL Server]Operand type clash: text is incompatible with uniqueidentifier (SQLExecute[206] at /usr/src/php-5.4.8/ext/pdo_odbc/odbc_stmt.c:254)

I'm building a PHP ZF2 application and attempting to call a user-defined function with parameters. The first four parameters are UNIQUEIDENTIFIER values. The last four are BIT values. Here is my code:

public function getCustomerInspectionDocuments($fkCustomer) {
    /** @var \Zend\Db\ResultSet\ResultSet $result */
    $result = $adapter->query("
        SELECT
            createUser.FullName AS CreateUser,
            udf.CreateTime,
            udf.CompleteTime,
            modifyUser.FullName AS ModifyUser,
            udf.ModifyTime,
            udf.Source,
            udf.id AS InstanceID
        FROM
            udfDocumentInstances(
                :fkCustomer,
                :fkDocumentQueue,
                :fkDocumentType,
                :fkADUser,
                :Completed,
                :Deleted,
                :LinkByXXX,
                :LinkByOwnership
            ) udf
            LEFT JOIN ADUser createUser
                ON udf.fkCreateUser = createUser.pkid
            LEFT JOIN ADUser deleteUser
                ON udf.fkDeleteUser = deleteUser.pkid
            LEFT JOIN ADUser modifyUser
                ON udf.fkModifyUser = modifyUser.pkid
        ORDER BY
            ModifyTime DESC
    ", array(
        ':fkCustomer' => $fkCustomer,
        ':fkDocumentQueue' => '57B5829B-3EAE-46FF-8130-8A432176DE2A',
        ':fkDocumentType' => '7E5D5187-B38A-E211-B52D-0F1256A21434',
        ':fkADUser' => null,
        ':Completed' => null,
        ':Deleted' => 0,
        ':LinkByXXX' => 0,
        ':LinkByOwnership' => 0,
    ));

    // do some other stuff and return the records
}

This code works absolutely fine when executed against SQL Server 2012, but when I switch it back to 2008, it chokes with that error message. I'm using the same driver, so the only thing that changes is the server.

The problem seems to be a combination of the fact it's executed against a UDF and that I'm binding the parameters via PDO. If I change it to a simple SELECT query (from a normal table), it works. Or if I put the parameter values directly into the query, that also works. But I need to call this UDF, and I would prefer to use the parameterized query.

Is there a way to see exactly what is being sent to the SQL Server (i.e. the completely assembled query)? Or is it possible that it's passed to the server as a parameterized query and it's the SQL Server that's assembling it?

You should be able to use SQL Profiler to see what is arriving at the datbase. — davek, Sep 10 '13 at 13:27
@davek: Thanks. That helped me discover that the query is not assembled on the web server, but is passed to the SQL Server as a parameterized query. Looks like all the parameters are being passed as `TEXT` which is the problem (see my answer below). — Travesty3, Sep 10 '13 at 14:51

Travesty3 · Accepted Answer · 2015-02-25T13:11:27.913

Based on what I found out from SQL Profiler (thanks @davek), it looks like the query is assembled on the SQL Server side, and PDO is passing all of the parameters as TEXT, which cannot be implicitly converted to UNIQUEIDENTIFIER or BIT. I'm not sure how to get it to pass them as CHAR by default. I'm also not sure why it doesn't cause an issue when using a parameterized query with a normal table.

Here is the temporary workaround I've come up with. It's ugly, but it works for now. Hopefully someone else can come up with a better answer.

'...
FROM
    dbo.udfDocumentInstances(
        CAST(:fkCustomer AS CHAR(36)),
        CAST(:fkDocumentQueue AS CHAR(36)),
        CAST(:fkDocumentType AS CHAR(36)),
        CAST(:fkADUser AS CHAR(36)),
        CAST(:Completed AS CHAR(1)),
        CAST(:Deleted AS CHAR(1)),
        CAST(:LinkByDEA AS CHAR(1)),
        CAST(:LinkByOwnership AS CHAR(1))
    ) udf
...'

So I'm casting the values from TEXT to CHAR, and then the SQL Server will implicitly convert them to UNIQUEIDENTIFIER or BIT.

If anyone can come up with a way to pass them as CHAR instead of TEXT, without having to explicitly cast each one, please post your response.

Operand type clash: text is incompatible with uniqueidentifier

1 Answers1