question

june790 avatar image
0 Votes"
june790 asked TiborKaraszi answered

The object name 'DBSourceServer.SourceDWHTemp.dbo.SourceTable1' contains more than the maximum number of prefixes. The maximum is 2.

Hi Experts,

We have DBServer1 and DBServer2 and want to load the data from DBSourceServer to DBTargetServer in SQL Server stored procedure.
SourceDB and SourceDWHTemp resides in DBServer1.
TargetDB resides in DBServer2.
We are applying ETL techniques in the SourceDB Stored Procedure script and intermediate tables are storing in SourceDWHTemp. Final data will be loaded into TargetDWHDB tables.
We are facing issues to run the script.
---- We are running this script in [TargetDB] database and DBTargetServer machine
Use [TargetDWHDB]
select * from [DBSourceServer].[SourceDWHTemp].[dbo].[SourceTable1] ----- It is successfully executing
DROP TABLE [DBSourceServer].[SourceDWHTemp].[dbo].[SourceTable1] ----- This is throwing the below error message

Error Message:
Msg 117, Level 15, State 1, Line 15
The object name 'DBSourceServer.SourceDWHTemp.dbo.SourceTable1' contains more than the maximum number of prefixes. The maximum is 2.
Completion time: 2021-06-02T18:21:31.6669423+08:00

How to solve this issue.
Thanks in advance.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered

You cannot do DROP TABLE on a linked server. But you can use EXECUTE AT on a linked server. Below example is from the documentation (replace with your requirements, of course):

 EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'  
 GO  
 EXECUTE ( 'CREATE TABLE AdventureWorks2012.dbo.SalesTbl   
 (SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;  
 GO  
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi june790,

Welcome to Microsoft Q&A.
Actually, we can’t use any DDL against linked server. But we can use EXECUTE AT statement, for example:

 EXECUTE ('DROP TABLE SourceDWHTemp.dbo.SourceTable1 ') AT [DBSourceServer]

or

 EXEC sp_executesql N'EXEC (''DROP TABLE SourceDWHTemp.dbo.SourceTable1;'') AT [DBSourceServer];'

102000-image.png

101927-image.png

In addition, please make sure RPC and RPC Out parameters are set to TRUE for linked server.

Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




image.png (7.7 KiB)
image.png (9.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

june790 avatar image
0 Votes"
june790 answered

Hi AliaGu-msft,
It is working fine. I have 3000 lines of code with lot of select,insert,update,drop statments with many columns 50 plus columns.
It is very difficult to update entire script.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi june790,

Thanks for your reply.
The SELECT, INSERT, UPDATE statement (DML) can work fine against linked server, but DROP statement (DDL) cannot use.
102471-image.png

102472-image.png

102423-image.png

102474-image.png

I’m afraid you have to use EXECUTE AT on the DROP statement though it is a hard task.

Best Regards,
Amelia



image.png (18.3 KiB)
image.png (12.0 KiB)
image.png (9.6 KiB)
image.png (12.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

june790 avatar image
0 Votes"
june790 answered AmeliaGu-msft commented

Hi Hi AliaGu-msft,
Please check the data flow and completed details in the attached image.
I can choose TRUNCATE instead of DROP.
But still I am struggling with errors.

Please check the error message in the image..
Msg 117, Level 15, State 1, Line 2
The object name 'XXXX-UAT-DB-01.DWH_TEMP.DBO.X1' contains more than the maximum number of prefixes. The maximum is 2.

Completion time: 2021-06-04T17:59:55.1232726+08:00

Thank you so much for your great support.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi june790,

Thanks for your reply.
It seems there is no image attached.. Could you please attach it again?

I can choose TRUNCATE instead of DROP.

Could you please share the command you run?

Best regards,
Amelia

0 Votes 0 ·
TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered

TRUNCATE TABLE doesn't accept 4-part naming either, as seen from the documentation:
https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver15

I.e., you are left with EXECUTE AT, as I recommended in my earlier reply. Or, you could do DELETE without a WHERE clause, but that takes along time since the data to be deleted will be logged to the transaction log.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.