Using 'Linked Servers' for SQLServer <--> SQLBase

General discussion forum about all things SqlBase.
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 22
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand

Using 'Linked Servers' for SQLServer <--> SQLBase

Post by Steve Leighton » 13 Mar 2017, 22:09

Using the Gupta OLE provider SQLBASEOLEDB is a good way to extract and /or update data from SQLBase into SqlServer or vice versa .
You can specify a SQL.ini file to use for some of the settings - and easily access data from either sources across remote servers.
Tips and Tricks:
[*]Once you have created a Linked Server in SqlServer , be sure to 'Allow Inprocesses' ( Right click properties of SQLBASEOLEDB under 'Providers' in SSMS)
[*]The SELECT and the UPDATE/INSERT/DELETE from within SqlServer actually have different syntax:

Code: Select all

--Select:
SELECT * FROM OPENQUERY( LINKServerName, 'Select * from TableOwner.TableName where ColumnName = Something') 

--Update ( Change the PK to the appropriate value ):
UPDATE [LINKServerName]..[TableOwner].[TableName ]
   SET [ColumnName] = [Something]
WHERE ColumnName = Something
GO

--Insert :
INSERT INTO [LINKServerName]..[TableOwner].[TableName ]
         ([ColumnName]
		   ,[ColumnName]
		   ,[ColumnName]
		   ,[ColumnName]
		   ,[ColumnName]
		   ,[ColumnName]
		   ,[ColumnName])     VALUES (Something,Something,'Something',Something,Something,Something,Something)
GO

--Delete :
DELETE FROM [LINKServerName]..[TableOwner].[TableName ]
      WHERE [ColumnName ] = Something

GO
Best wishes
Steve
Bankside Systems Ltd. ( New Zealand )
www.banksidesystems.co.uk

Return to “General Discussion”

Who is online

Users browsing this forum: Ccbot [Crawler] and 0 guests