Issue: Datagarage / Collect - Some tables fail to load while others are successful
Version: applicable to all versions of DG and Collect
Things to check:
1. Incorrect or mistyped Schema Owner
- Verify that the Schema Owner in CranSoft/DSP matches the Schema Owner in the Source Database.
- Verify that there are no extra spaces, usually at the end, of the Schema Owner in CranSoft/DSP.
Verify that the case of the Schema Owner is identical in CranSoft/DSP and the Source Database.
Example: If the Schema Owner is all capital letters in the Source Database, that is how it should be in CranSoft/DSP.
2. UserID doesn't have correct permissions for the table
The UserID that is set up in CranSoft/DSP as the login for the source database needs to have SELECT access in order to download the table. The following will show all tables the UserID has access to in the source database if using DTS. If a particular table/view is missing here then this UserID is missing permissions to that object.
- In SSMS, select the DTS package.
- Choose Transform data task properties.
- Select radio button table/view.
- Review the list of all the tables that the UserID has access to.