You need to sign in to do that
Don't have an account?
TJGAG
DBAmp unexpected behaviour with null
Hi all,
I have a query -
SELECT Description,
Id, OpportunityLineItemId,
Quantity, Revenue,
ScheduleDate,
SystemModstamp, Type
FROM salesforce...OpportunityLineItemSchedule
WHERE ScheduleDate < 2006-01-25
AND (Description = null OR Description = '')
This returns an error
Server: Msg 7342, Level 16, State 1, Line 1
Unexpected NULL value returned for column '[salesforce]...[OpportunityLineItemSchedule].Description' from the OLE DB provider 'DBAmp.DBAmp.1'. This column cannot be NULL.
OLE DB error trace [Non-interface error: Unexpected NULL value returned for the column: ProviderName='DBAmp.DBAmp.1', TableName='[salesforce]...[OpportunityLineItemSchedule]', ColumnName='Description'].
If I remove Description from the SELECT list the query works correctly.
Does anyone have a suggestion to work around this so that I can include Description or is this just a feature of DBAmp?
Thanks,
Andrew
I have a query -
SELECT Description,
Id, OpportunityLineItemId,
Quantity, Revenue,
ScheduleDate,
SystemModstamp, Type
FROM salesforce...OpportunityLineItemSchedule
WHERE ScheduleDate < 2006-01-25
AND (Description = null OR Description = '')
This returns an error
Server: Msg 7342, Level 16, State 1, Line 1
Unexpected NULL value returned for column '[salesforce]...[OpportunityLineItemSchedule].Description' from the OLE DB provider 'DBAmp.DBAmp.1'. This column cannot be NULL.
OLE DB error trace [Non-interface error: Unexpected NULL value returned for the column: ProviderName='DBAmp.DBAmp.1', TableName='[salesforce]...[OpportunityLineItemSchedule]', ColumnName='Description'].
If I remove Description from the SELECT list the query works correctly.
Does anyone have a suggestion to work around this so that I can include Description or is this just a feature of DBAmp?
Thanks,
Andrew
Try Description is NULL instead of Description = null
You have to use SQL syntax (not SOQL) when talking to SQL Server.
Bill Emerson
DBAmp Author
Message Edited by qman on 01-26-2006 05:10 AM
I did try your suggestion but received this error instead:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'DBAmp.DBAmp.1' reported an error.
[OLE/DB provider returned message:
( ( Description = null and createddate > 0001-01-01 ) or Description
^
ERROR at Row:1:Column:210
No such column 'createddate' on entity 'OpportunityLineItemSchedule'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.]
OLE DB error trace [OLE/DB Provider 'DBAmp.DBAmp.1' ICommandText::Execute returned 0x80004005: ].
It must be something to do with the particular SalesForce org and/or the OpportunityLineItemSchedule object because I changed the connection and the command worked OK with "Description = null". I do though get the above error using "is null".
I also tried this
SELECT
Id, Name
FROM salesforce...Account
WHERE ([Name] is null OR [Name] = '')
and got no errors at all!
Luckily it is something I can work around.
Andrew
Thanks,
Bill Emerson