0

I have SQL Server Management Studio v18.8 looking at a SQL Server 2019 database. When I right-click on a table in a Linked Server Object and choose to "Script table As Select To New Query Editor Window", then many of the columns are duplicated or triplicated.

For example, if my table has these four columns:

UNIT_ID
UNIT_NAME
UNIT_LOCATION
UNIT_LOCATED_DATE

Then the results will look like this:

SELECT [UNIT_ID]
    ,[UNIT_NAME]
    ,[UNIT_NAME]
    ,[UNIT_NAME]
    ,[UNIT_LOCATION]
    ,[UNIT_LOCATION]
    ,[UNIT_LOCATED_DATE]
    ,[UNIT_LOCATED_DATE]
    ,[UNIT_LOCATED_DATE]
    ,[UNIT_LOCATED_DATE]
FROM [MyLinkedObject]..[dbo].[UNIT_LOC]
GO

Is this a known bug? Is there a fix or workaround? I would really like to be able to create and run these scripts quickly, but I'm having to spend a lot of time deleting the repeated copies of the columns.

Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Koughdur
  • 43
  • 1
  • 7
  • "Is this a known bug?" - it's a bug, but I've no idea if it's a _known_ bug. Have you reported it? – Dai Aug 28 '21 at 01:26
  • "Is there a fix or workaround?" - you can use SMO ("SQL Server Management Objects") to run the "Create scripts..." functions from code - you could invoke them from Linqpad and generate to a `string` value and then use a regex to clean-up, I suppose. (Internally SSMS uses SMO). – Dai Aug 28 '21 at 01:27
  • I've seen this happen because of driver issues (notorious if you have a linked server to oracle), but not with sql linked servers. Could you paste your linked server definition (scrub out any sensitive info, but keep the driver related info)? – allmhuran Aug 31 '21 at 01:33
  • Sorry but I got busy around the holiday. The Linked Object is to Oracle, so that's probably the problem. I don't have much selection in drivers. The company gives me what it can and I'm not allowed to update from the web. – Koughdur Sep 09 '21 at 21:15
  • Currently: Provider = Oracle Provider for OLE DB, Product Name = OraOLEDB Oracle; Data Source = Full TNSNAMES entry. The server options are: Collation Compatible = False, Data Access = True, RPC = False, RPC Out = False, Use Remote Collation = True, Distributor/Publisher/Subscriber/Lazy Schema = All False, Enable Promotion of Dist Trans = True. – Koughdur Sep 09 '21 at 21:22

0 Answers0