Retreiving 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

Retreiving data from a linked server

Author

Topic

tleonard
Starting Member

13 Posts

Posted - 2008-09-19 : 11:35:43

I have a linked server configured to an iSeries library.

I can see the name (iSeriesLib) under the Security folder using
Enterprise Manager. When I expand the linked server (iSeriesLib)
I can see the Tables and when clicking I see all the files in
that library.

What I can't seem to get correct is the format for the
Select statement in Query Analyzer to query a data file. I've tried enclosing the LS name in brackets, I've tried without brackets.
ie..Select * from [iSeriesLib].File1
error msg: Invalid object name 'iSeriesLib.File1
Select * from iSeriesLib.Library.File1
error msg: Invalid object name 'iSeriesLib.Library.File1'
Select * from iSeriesLib...File1
error msg: OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='iSeriesLib', TableName='File1']

Can someone assist me with the correct format...

thomas f leonard

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 12:37:05

Does table File1 exists in iSeriesLib?

tleonard
Starting Member

13 Posts

Posted - 2008-09-22 : 11:04:23

Thanks for reply. Yes, the table is there.
I actually have the format for the select now. The follow works using Query Analyzer.

Select * from openquery([iSeriesLib], 'Select * from File1 where stransid = 205')

After executing the above string I get a single row returned.

Now I want to update that row.

Select * from openquery([iSeriesLib], 'Update File1 set sshpwh = 2 where stransid = 205')
results in the following error:

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Update File1 set sshpwh = 2 where stransid = 205'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='MSDASQL', Query=Update File1 set sshpwh = 2 where stransid = 205'].

This is a mystery to me, that the select recoginizes a conditional select based on field stransid,
but the update gets an error that indicates no columns.

Thanks.

tl

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 11:23:10

Try this out

UPDATE openquery([iSeriesLib], 'Select * from File1 where stransid = 205')
SET sshpwh = 2

tleonard
Starting Member

13 Posts

Posted - 2008-09-22 : 13:13:32

After I set up a journaling file on File1 this command is working.
Thank you much for the assistance.

tl

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 13:16:26

quote:
Originally posted by tleonard

After I set up a journaling file on File1 this command is working.
Thank you much for the assistance.

tl

welcome

Glad that i could help you out

tleonard
Starting Member

13 Posts

Posted - 2008-09-22 : 14:40:36

After further reading I have a couple of other questions? It is noted that when the servers are recycled, it may be necessary to link them again. 1)Is there anyway in a stored procedure to identify if the link has been losted? If so, how? 2)If answer to 1 is no, then is it ok to have a stored procedure scheduled to run several times a day to execute the sp_addlinkedserver command?

tl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources