I'm using data factory and my explorer seems to be frozen. Behind the scenes I am able to create tables but when I request to drop a table it says that I have two columns with similar names and that I need to use SQL commands to rename these columns.

Question

I have a table InstallBase which I have created successfully using a copy data task in Data Factory. When I go to Explorer I can't see the table. When I then do to drop the table using a drop table SQL command it drops the table but reports that I have two columns which almost have the same name but one is capitals and I need to use SQL to rename. How can I get my explorer to reflect the accurate state of my schema

Answer 1

@Peter Carruthers

Welcome to the Microsoft Q&A forum.

It seems like you're facing issues with Azure Data Factory and your SQL schema. Let's tackle them one by one.

Explorer Not Reflecting Table

If your Azure Data Factory Explorer is not showing the table you created, it might be due to a caching issue or a delay in the UI update. Here are a few steps you can try:

• Refresh the Explorer: Sometimes, simply refreshing the Explorer can help.
• Clear Browser Cache: Clearing your browser cache might resolve any UI-related issues.
• Check Permissions: Ensure that you have the necessary permissions to view the table in the Explorer.

Dropping Table with Similar Column Names

The error you're encountering when trying to drop the table suggests that there are columns with names that differ only in case sensitivity. SQL Server is case-insensitive by default, which can cause such issues. Here's how you can resolve it:

Identify the Columns: First, identify the columns with similar names. You can use the following SQL query to list all columns in the InstallBase table:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'InstallBase';

Rename the Columns: Use the ALTER TABLE command to rename one of the columns. For example:

ALTER TABLE InstallBase
RENAME COLUMN oldColumnName TO newColumnName;

Drop the Table: Once the columns are renamed, you should be able to drop the table without any issues:

DROP TABLE InstallBase;

Ensuring Explorer Reflects Schema Changes

To ensure that your Explorer reflects the accurate state of your schema:

• Refresh the Explorer: After making changes, refresh the Explorer.
• Restart Data Factory: If the issue persists, try restarting Azure Data Factory.

I hope the above steps will resolve the issue, please do let us know if issue persists. Thank you