Getting data from a Linked Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting data from a Linked Server

Author

Topic

riley.porter
Starting Member

9 Posts

Posted - 2011-12-28 : 13:56:01

I can't for the life of me figure out how to get data from a database via ODBC and import it to a local DB in SQL '08. I've setup the linked server connection just fine and I can view the tables, along with their rows, but I need to extract data from those tables.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-28 : 14:20:06

INSERT INTO SomeTable (...)
SELECT ... FROM LinkedServerName.DatabaseName.ObjectOwner.ObjectName
WHERE ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

riley.porter
Starting Member

9 Posts

Posted - 2011-12-28 : 14:22:07

It never fails that I post something on a forum and 5 minutes later I'm able to figure it out. :) I did this:

SELECT * INTO IMPORT
FROM OpenQuery (CPSI, 'Select * from OD_IS2')

And it looks like nearly the same that tkizer just posted. Thank you! This table has about 400k rows and is obviously not done yet, but it hasn't bombed out, which is a good sign.

riley.porter
Starting Member

9 Posts

Posted - 2011-12-28 : 16:12:46

Quick other note: What happens when the query comes back and says it can't convert data type? Can I force that through T-SQL to format it correctly?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-28 : 16:25:34

Yes, you can use convert or cast functions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-28 : 16:27:23

yes you can...HOWEVER..load the data to a staging table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

riley.porter
Starting Member

9 Posts

Posted - 2011-12-29 : 12:42:55

Is there a way to dynamically create a staging table so that I don't have to recreate 75,000 columns on a local table to match the remote table?

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-29 : 13:46:02

ok...are you just joshing us...do you columns...or rows

DROP localtable
SELECT * INTO localtable FROM remote table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

riley.porter
Starting Member

9 Posts

Posted - 2011-12-29 : 15:13:46

Yeah, I kind of exaggerated... there's about 150 columns, but still. :) Anyway, I need to clarify more, sorry. I'm definitely n00b. I know what I WANT it to do, it's just knowing how to tell SQL what I'm wanting it to do. I'll start over to make sure I explain what I'm trying to do.

I need to get data from tables on a remote server that I don't control (I just have view/retrieve access). On two of the six, I can do what you put above (just with the OPENQUERY statement I said earlier) and it runs just fine. But, on the 4 others, I get errors as soon as I execute the query, like this:

quote:

Msg 8114, Level 16, State 10, Line 1
Error converting data type DBTYPE_DBDATE to date.

How can I fix this? I don't know how to effectively use CAST or CONVERT. I admit I'm pretty stupid when it comes to actual coding. Thanks for helping me!

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-29 : 15:38:11

OK...here's the thing...

Doing queries against a linked server is bad (somebody tell me differently)

I Suggest that you have a process to drag the data over...non logged if possible to a staging table to your server...THEN do whatever you need to do

OK... minimally logged

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-12-29 : 15:44:46

"How can I fix this?"

That was what Brett was hinting at about using a STAGING table.

Make all the columns in the staging table VARCHAR (rather than specific datatypes). Make them as wide as you like. VARCHAR(MAX) for all of them will do. The import will then succeed regardless of data conversions etc.

We add ErrorNo and ErrorMsg columns, and then we do


UPDATE U
SET ErrorNo=1, ErrorMsg=COALESCE(ErrorMsg+', ', '') + 'Illegal date in COL1'
FROM MyStagingTable
WHERE IsDate(COL1) = 0

repeat for all the "validation tests" you can think of (add more next time the import breaks for any reason

), and then either only import rows where ErrorNo IS NULL or abort the import if ANY rows have an Error recorded.

You can also use UPDATE statement to "clean-up" the data in the staging table - e.g. reformat dates into a more acceptable format - perhaps convert UK format to USA format etc.

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-29 : 15:52:31

"I Started programming in 1978"

I was teaching the computer club I founded in WOHS 1978

I Also started in database work on DB2 V2.3..I'm guessing 1983?

THE WHOLE Idea is that across servers The optimizer can't now what you want....Set up a nightly job to copy the data to a staging table

You can easily go against the catalog and generate the table

Or script it

Or use the statements I gave you above

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

riley.porter
Starting Member

9 Posts

Posted - 2011-12-29 : 15:57:38

The only access I have to this remote database is through a proprietary ODBC driver. That could mean nothing as far as I know, but it seems to really restrict how I can actually get the data. I've tried using BIDS, but I get the same problems with conversion. I can do a simple query to it to view data on a table, but as soon as I try to actually import the data into a local table is when it breaks. I'm not sure how to reference that remote database through the ODBC, other than doing a linked server?

riley.porter
Starting Member

9 Posts

Posted - 2011-12-29 : 17:08:02

At least for this first table, I think I might have found the lone column that's giving me hell. BUT, I'm still having problems! This is what I do:

quote:
SELECT CONVERT(VARCHAR,IS1MR_OKAYED)
FROM OpenQuery (CPSI, 'Select IS1MR_OKAYED from OD_IS1')

and I get this result:

quote:
Msg 8114, Level 16, State 10, Line 1
Error converting data type DBTYPE_DBDATE to date.

Why do I still get conversion errors when I'm telling it to convert?! I get the same result with CAST, coincidentally. I'm guessing it might have something to do with having to use a linked server/this crappy ODBC driver... but I don't know.

riley.porter
Starting Member

9 Posts

Posted - 2011-12-30 : 10:48:53

I really appreciate everyone helping me out! Like I said, I'm a super-n00b when it comes to SQL and scripting, hence the stupid questions. I'll give you all an update later when I do what you suggested. :)

Kristen
Test

22859 Posts

Posted - 2011-12-30 : 12:55:40

I suspect that the datatype of column IS1MR_OKAYED in the CPSI linked server is some sort of date/time type.

Perhaps the actual, physical, data in that column is not valid as that datatype? and thus even trying to select it via the ODBC driver is failing - i.e. the remote system has "loose" data typing at best.

Try:


SELECT *
FROM OpenQuery (CPSI, 'Select IS1MR_OKAYED from OD_IS1')

and see what sort of data "consistency" you get

riley.porter
Starting Member

9 Posts

Posted - 2011-12-30 : 15:52:22

Same result.

quote:
Msg 8114, Level 16, State 10, Line 1
Error converting data type DBTYPE_DBDATE to date.

In talking with another person about it, he also believes that it's an XFD problem, incorrectly mapping data types to fields. The external DB is COBOL, we determined, which apparently has really crappy data typing. His suggestion is to go to that admin and tell them to fix their XFD.

Thanks to everyone for trying to help me out!

Kristen
Test

22859 Posts

Posted - 2011-12-31 : 05:10:43

Bit of a long shot, but you might be able to solve it with some sort of data type conversion at the REMOTE end:


SELECT *
FROM OpenQuery (CPSI, 'Select CONVERT(varchar(50), IS1MR_OKAYED) from OD_IS1')

I doubt very much that that is the right syntax, but someone who knows the syntax for the remote database may be able to give you a suggestion

Also, if you can establish whether its just one row you could do:


SELECT *
FROM OpenQuery (CPSI, 'Select IS1MR_OKAYED from OD_IS1 WHERE SomeID NOT IN (123, 456)')

to exclude just that row(s) from the query - assuming there are only a few of them!

seomert
Starting Member

3 Posts

Posted - 2011-12-31 : 06:48:45

'm needing to get data from an external database that I only have read/retrieve access to via ODBC. I can set it up as a linked server, I can query the daylights out of it and see the results, but I get conversion errors when trying to do an INSERT INTO. I've been posting here about the same thing, but have yet to understand what to do.

Notice: I'm a SQL Coding newbie, be gentle. I might have the answer on the forum linked above that I'm just too stupid to wrap my brain around.

I've tried to do CAST, CONVERT, OpenQuery, OpenDataSource, INSERT, UPDATE... Some of those, like INSERT and UPDATE, I can't get to accept the syntax.

If I try to do an entire table, like this:

SELECT * INTO IMPORT
FROM OpenQuery (CPSI, 'Select * from OD_IS1')
I get this result:

Msg 8114, Level 16, State 10, Line 1
Error converting data type DBTYPE_DBDATE to date.
I found the column that was breaking it and tried to pull it in individually to change the column type...

SELECT CONVERT(VARCHAR,IS1MR_OKAYED)
FROM OpenQuery (CPSI, 'Select IS1MR_OKAYED from OD_IS1')
and get the same error:

Msg 8114, Level 16, State 10, Line 1
Error converting data type DBTYPE_DBDATE to date.
Can I do something like this? These are examples of just trying to get one column.

UPDATE dbo.OD_IS1
SET IS1_PATNUM = SELECT * FROM OpenQuery (CPSI,'SELECT IS1_PATNUM FROM OD_IS1')

//or//

INSERT INTO dbo.OD_IS1 (IS1_PATNUM)
VALUES FROM (OpenQuery (CPSI,'SELECT IS1_PATNUM FROM OD_IS1')
For all I know, this isn't an option to do with an OpenQuery or this ODBC driver. Mind you, the ODBC is the only way for me to access this external database. I manually created a skeleton table to mock the external one I'm trying to retrieve and made all of the columns VARCHAR(MAX), but I don't know how to update the local table with the external table values through this ODBC.

Any help or insight or slaps upside the head are appreciated!

Oh, also: I've tried to use BIDS, but it either crashes or does nothing. I probably don't know how to use BIDS, either, but I do this:

SSIS Import & Export Wizard --> Select ODBC DSN as source --> Select local DB as target --> Put in 'SELECT * FROM OD_IS1' for the query --> On the preview page, I can see the columns and their values --> The package finishes out without errors. I right-click on the new package and tell it to "Execute", but all I get are debug outputs. How can I actually "execute" the command? I know it should take like 30+ minutes because there are a few million rows to pull over.

bakirköy web tasarim

seomert
Starting Member

3 Posts

Posted - 2011-12-31 : 06:50:34

are you understand

http://intermedyaweb.com

Kristen
Test

22859 Posts

Posted - 2011-12-31 : 07:01:46

"Error converting data type DBTYPE_DBDATE to date."

I think this is, most likely, an error in the ODBC Driver at the Remote Database. It thinks the column should be Date (or maybe Date Time) but the data it is finding is not a valid date / date/time.

I have spent days, literally, working around the foibles of ODBC drivers on "legacy ISAM systems" and the like. They aren't / weren't built for the rigours that they are now being used for, so best way is to just pull the data into a Staging Table where ALL Columns are set to VARCHAR(999) or VARCHAR(MAX) even, and then "process" the data from there. If you are lucky enough to be able to pull the data into specific datatype columsn than consider yourself lucky

But in this case I don't think a target column of VARCHAR is going to make any difference

It would be good to know what the value is in the rogue row's column, when you find an example, as it will probably help someone else in the future to be considering that such a thing COULD happen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources