Loading Profile...
We are facing a strange issue. We are able to query and see rows from contentdocument using salesforce linked server created with DBAmp. But when we try to dump all the rows into a temp table and try to find some of the rows, those are missing. Not able to identify the root cause.
Here is the Test Case
1. Check number of rows from contentdocument salesforce object
select count(id) From ls_salesforce_envname...contentdocument
--55487 (rows count)
2. Check for some sample ids
select * From ls_salesforce_envname...contentdocument
where
id ('069w0000000o1fXAAQ','069w0000000o1fXAAQ','069w0000000o1kSAAQ')
--We are able to see three record
3. Dump all the rows from linked server into a temp table
select * into dbo.tmpcontentdocument From ls_salesforce_envname...contentdocument
--55487 (rows count)
4. Check for the same sample ids
select count(id) From dbo.tmpcontentdocument
where
id='069w0000000o1fXAAQ'
--we are not able three records. It is missing when queried. tried to search with right collation latin1_General_BIN. But not able to find the records
Spent 5 hours around this and found not able to identify the root cause.
Request your help on this issue.
Thanks and Regards,
Vijay
Help get this topic noticed by sharing it on
Twitter,
Facebook, or email.
Twitter,
Facebook, or email.
Missing Rows on ContentDocument object
-
I am confused. Since Id is the primary key, why would there ever be 3 records with the same primary key in the table ?Comment
-
-
Sorry, Its three different IDs. Test case rewritten here
1. Check number of rows from contentdocument salesforce object
select count(id) From ls_salesforce_envname...contentdocument
--55487 (rows count)
2. Check for some sample ids
select * From ls_salesforce_envname...contentdocument
where
id in ('id1','id2','id3')
--We are able to see three record
3. Dump all the rows from linked server into a temp table
select * into dbo.tmpcontentdocument From ls_salesforce_envname...contentdocument
--55487 (rows count)
4. Check for the same sample ids
select count(id) From dbo.tmpcontentdocument
where
id in ('id1','id2','id3')
--we are not able to see the sample records. -
-
Can you reduce the test case to a single id and rewrite #4 as an Select * where Id = 'fdfdff' . Does that change the result ?
Please post a screenshot of the query and result. -
-
even for a single id, its not working. I gave three ids to indicate that the issue is not related to a single id.
Appreciate your quick responses. Please let me know, if I can provide any further information.
Since the rows count matching between step 1 and step 3. We were wondering, what could go wrong. -
-
"Please post a screenshot of the query and result."
-
-
Please find attached screen shot -
-
Please change the table name to ContentDocument instead of using the column subset suffix for numbers 2,3,4
Then send a screenshot of the result -
-
One more clear finding is that. Without any filter condition, all the ids are not returned. Id is returned only when one id is filtered.
-
-
Please post a complete screenshot of the DBAmp.DBAmp provider options so we can see what items are checked.
-
-
-
-
Wrong screen.
Server objects / linked servers / providers / right click DBAmp.DBAmp choose properties.
Name all items that are checked -
-
-
-
Can you post the Server options page of the linked server ?
-
-
Also, what is the collation of the server AND the collation of the database where you are storing the local table ?
Finally, can you make sure there are not multiple local tables with that same name but a different schema. -
-
We have three environments. All the three environments have the same option as attached picture.
We find the issue in two environments.
Both Server and Database are using collation Latin1_General_CI_AS.
We got closer to the issue now, even without dumping into a temp table, we can check the issue.
here is the test case,
1. Try to select two ids (No rows returned)
select id,ownerid,CreatedDate,IsArchived,IsDeleted,LastModifiedDate,SystemModstamp From ls_salesforce_projectuat...contentdocument
where
id in ('069w0000000o1fXAAQ','069w0000000o1frAAA')
3. Try to select each id - Rows returned
select id,ownerid,CreatedDate,IsArchived,IsDeleted,LastModifiedDate,SystemModstamp From ls_salesforce_projectuat...contentdocument
where
id in ('069w0000000o1frAAA')
3. Try to select each id - Rows returned
select id,ownerid,CreatedDate,IsArchived,IsDeleted,LastModifiedDate,SystemModstamp From ls_salesforce_projectuat...contentdocument
where
id in ('069w0000000o1fXAAQ')
attached screen shot
Please let me know to setup a screen sharing session, so that can discuss the issues -
-
Can you run a "Display Estimated Execution Plan on item #1 and save the SQLPlan to file. Then send the file to support@forceamp.com
-
EMPLOYEE
