Focal Point
[Solved] how do I delete a SQL table with a dbo prefix

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/7617090196

February 04, 2019, 11:00 AM
folkie
[Solved] how do I delete a SQL table with a dbo prefix
I recently asked a question in Focal Point asking how - in DataMigrator - to delete or rename tables from a SQL server database. Tsepe gave me the answer I was looking for, by adapting the following DDL script:

ENGINE SQLMSS SET DEFAULT_CONNECTION SQLCONN1
SQL SQLMSS COMMIT
SQL SQLMSS
DROP TABLE dbo.TBL1
-RUN

The above worked fine for most of the test tables I wanted to get rid of. I use an MS Access Project to see the list of SQL tables in my database. An example of what I see in the Access Project for most of the tables looks like this in the navigation pane on the left hand side:

Test1 (dbo)
Test2 (dbo)
Test3 (dbo)

For these tables, the above script worked fine. However, I have a couple tables that look like this in the navigation pane:

dbo.Test_Table_Date1 (dbo)

I'm not sure how these few tables got created, which have a prefix of dbo. But I can't figure out how to adapt the above DDL script to recognize these "dbo" tables. I tried all kinds of things in the DROP TABLE statement, but I keep getting an error, such as:

: Microsoft SQL Server Native Client 11.0: [42S02] Cannot drop the table
: 'dbo.Test_Table_Date1', because it does not exist or you do not have
: permission.

Do you know of a way to delete tables with a prefix of dbo by adapting the DDL script above? Or, of any other way to delete them using DataMigrator?

Thanks,
Mark

This message has been edited. Last edited by: folkie,


WF 8.2.02
App Studio
DataMigrator
ReportCaster
Windows Server 2016
February 04, 2019, 11:55 AM
folkie
I figured it out. I happened to see the tool tip while hovering over the name of one of these funky tables. The tool tip had:

dbo."dbo.Test_Table_Date1"

Thus, I tried:

DROP TABLE dbo."dbo.Test_Table_Date1"

and it worked.

Thanks,
Mark


WF 8.2.02
App Studio
DataMigrator
ReportCaster
Windows Server 2016