How can I make connection to a Solid DB?

Question

How can I make connection to a Solid DB?

LoorD

SSC Veteran

Points: 229
More actions
April 14, 2004 at 3:04 am

#84166

Hi all!
I think this is a tricky one, although I've heard that it's possible somehow..
I want to do a timed procedure, which starts once a day and gets data from a Solid server db and puts it to SQL Server db. How can I do this? I have no clue..
I already made a program in C language and ODBC library, but it would be nicer if I didn't use any "third party" programs here.
I'm just guessing, but I have the odbc driver for Solid server, and I've made the dsn for it. I think I have to get a hold on that dsn from SQL Server, am I right? Please, if anyone could help me on this!
-- Arto Kainu

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65143 More actions · Answer 1

I have never heard of Solid, but...

Since you have the ODBC driver and DSN setup then use DTS to transfer the data, it will be the most efficient. You can create job to run the the DTS and schedule it to run once a day.

If you want to do this via a store procedure then create a linked server and use OPENQUERY. Providing the ODBC driver is compliant and able to support this.

I transfer data from a 3rd party non SQL server database using both methods.

Far away is close at hand in the images of elsewhere.
Anon.

LoorD SSC Veteran Points: 229 More actions · Answer 2

Ok, thanks David!

Nex question will be, what is DTS? And how can I make a job? And where do I put the dsn of my non SQL server db? I'm REALLY newbie..

- Arto Kainu

David Burrows SSC Guru Points: 65143 More actions · Answer 3

OK some simple instructions to get you foing

DTS is Data Transformation Services. It allows the transfer and transformation of data to/from SQL server.

In EM (Enterprise Manager), expand your server

Select your target database

Right click on the target database, select All Tasks, Import Data

You will then see the DTS Wizard which will take you through the steps

Choose a data source

use the drop downm list to select your Solid ODBC driver

Select the DSN you set up enter appropriate username and password

Click Next

Choose a destination

Leave the destination as Microsoft OLE DB Provider for SQL Server

The server and database will be selected already

Change the authentication type is required

Click Next

Specify table copy or query

Here you select whether to pick a table to transfer (all columns)

or to enter a query is you want selected columns

Click Next

Click on Transform

Here is where you can create destination table if it does not exist, delete rows in destination table first or append data and map columns (you may have different name for input and output) and and special transformation for specific columns (you script columns to do data validation or translation)

Eventually you will see a screen that will ask if you want to run the package immediately, save the package and or schedule it. I always save the package first with a reasonable name. Once saved you can load it by expanding the Data Transformations Services folder and double clicking on the package name.

VERY IMPORTANT - make sure you get the source and destination the right way round, DTS can transfer data BOTH ways.

This is not complete but will get you started.

BOL (Books Online) will give you more information.

Experiment on a test server first so that you get use to it.

Far away is close at hand in the images of elsewhere.
Anon.

LoorD SSC Veteran Points: 229 More actions · Answer 4

Wow!!! THANKYOU!

That must be the best answer I've ever had in any Forum, thanks a lot, David!!

I understood that quite fast. This helped me a lot!

-- Arto Kainu

Anthony Ivins SSC Enthusiast Points: 122 More actions · Answer 5

Another possible approach is using OpenRowset if you decide not to use a DTS for the import. Note that you will need to determine the OLDB driver and connection string for your Solid database.

I have never used a solid database. However using openrowset is a simple as the examples outlined below:

-- OpenRowset for Access Database

SELECT A.*

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)

AS A

GO

-- OpenRowset for OLAP Server

SELECT a.* FROM OpenRowset('MSOLAP','DATASOURCE=myOlapServer; USER ID=; PASSWORD=;Initial Catalog=FoodMart;',

'SELECT Measures.members ON ROWS,

[Product Category].members ON COLUMNS

FROM [Sales]') as a

GO

I quickly reviewed the Solid Programmers Guide (Version 4) and noted that OLE DB is supported. For further information in relation to openrowset, refer to the SQL Server books on line.