Data Dictionary configuration for MSSQL Linked Server

1

I need to access a table on remote SQL server using a Linked Server that I have defined on our primary SQL server. I am able return results using TABLE{PROP:SQL} syntax, but I want to define and access the table via a table definition in the data dictionary. I’m not sure how to configure the OWNER and PATH variables. Anyone successful doing this in Clarion 11.1?

2

Joel,
You can but you have to set the VerifyViaSelect option to true. Otherwise, the driver uses the sp_columns_100 to obtain column information about the table and this fails on a table in another database in a linked server. This example works:


  PROGRAM
                              MAP
                              END

GLO:DBOwner                   CSTRING(512)

!Freight                       FILE,DRIVER('MSSQL'),OWNER(GLO:DBOwner),NAME('dbo.TestLinkedServer'),PRE(FRE),CREATE,BINDABLE,THREAD ! Freight Rates       
Freight                         FILE,DRIVER('MSSQL'),OWNER(GLO:DBOwner),NAME('[MIRKWOOD\SQL2019].LinkedServerTest.dbo.Freight'),PRE(FRE),CREATE,BINDABLE,THREAD ! Freight Rates       
KeyByCode                         KEY(FRE:Code),NOCASE,PRIMARY      ! Key By Code         
KeyByAcctType                     KEY(FRE:AccountType),DUP,NOCASE   ! Key By Account Type 
KeyByAcctWeight                   KEY(FRE:AccountType,FRE:Weight),DUP,NOCASE ! Key By Account and Weight
Record                            RECORD,PRE()
Code                                LONG                           ! Freight Code        
AccountType                         STRING(8)                      ! Account Type        
MinWeight                           DECIMAL(9,2)                   ! Minimum Weight      
MinCharge                           DECIMAL(9,2)                   ! Minimum Charge      
Weight                              DECIMAL(9,2)                   ! Weight              
Rate                                DECIMAL(7,2)                   ! Rate                
                                  END
                                END                       

  
  CODE
  GLO:DBOwner = 'MIRKWOOD\SQL2022,MainDatabaseForLinkedServer'
  SEND(Freight,'/TRUSTEDCONNECTION=TRUE')
  SEND(Freight, '/VERIFYVIASELECT=TRUE' )

  IF ERRORCODE()
    MESSAGE('Failed to SEND: ' & ERROR() & ' ' & FILEERROR())
  end
  OPEN(Freight,42h)
  IF ERRORCODE()
    MESSAGE('Failed to open: ' & ERROR() & ' ' & FILEERROR())
  else
    MESSAGE('Success: ' & RECORDS(Freight))
  end

Another option that works without using the VERIFYVIASELECT, is to create a view in your main database that does a select on the linked server table. Then reference that view in your Clarion dictionary table NAME attribute.

3

Mirkwood??? Don’t know I’d trust anything outta there :rofl:

4

It’s important to stay on the path when working with that server.