SQL Linked Server - Data Extraction Problem

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

SQL Server 2008 Forums

SQL Server Administration (2008)

SQL Linked Server - Data Extraction Problem

Author

Topic

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2010-10-12 : 16:45:09

I'm new to SQL Server Administration.

I need to create a Data Warehouse in SQL Server 2008 R2 from a SQL Server 2005 database that I only have READONLY access to. I do NOT need the whole database just data from X number of Tables.

I successfully LINKED to the SQL2005 db using the remote login with pswd feature of the READONLY login.

My problem occurs when I try to Select data from the SQL2005 db.

When I use the following query I get the following error(s):
Table.SID (int)
Table.SIDStatus (char(2))

SELECT SID, SIDStatus FROM ServerName.DataBase.dbo.Table where SIDStatus = 'S'

Error:
OLE DB provider "SQLNCLI10" for linked server "ServerName" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "ServerName" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "ServerName". The provider supports the interface, but returns a failure code when it is used.

When I try OPENQUERY I get the Following:

select * from OPENQUERY(ServerName, 'SELECT SID, SIDStatus FROM DataBaseName.dbo.Table where SIDStatus = 'S'')
ERROR:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'S'.

If I use:
select * from OPENQUERY(ServerName, 'SELECT SID, SIDStatus FROM DataBaseName.dbo.Table where SIDStatus = 'S'')
ERROR:
OLE DB provider "SQLNCLI10" for linked server "ServerName" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'S'.

BUT IF I use:
select * from OPENQUERY(ServerName, 'SELECT SID, SIDStatus FROM DataBaseName.dbo.Table where SID = 235')

The Query works fine.

Problem is I need to select off the SIDStatus = 'S'

I assume I just have a syntax error but have been unable to figure it out.

Any suggestions?

Thank You very much.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-12 : 16:51:22

You are missing single quotes:
select * from OPENQUERY(ServerName, 'SELECT SID, SIDStatus FROM DataBaseName.dbo.Table where SIDStatus = ''S''')

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

Subscribe to my blog

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2010-10-12 : 17:01:43

Tara,
Thank You Very Much!

I was using double quotes instead of single.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-12 : 17:08:28

You're welcome, glad to help.

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources