Query database on linked server where database has name like [ABC.DEF]

A database exists on a linked server that I would like to query
When I run the following command
SELECT * from [INSTANCE_NAME].[ABC.DEF].dbo.sysfiles
-- Note the name of database has an "." in it (I cannot change this)
I get the error
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object '"ABC.DEF"."dbo"."sysfiles"'. The OLE DB
provider 'SQLOLEDB' 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='SQLOLEDB',
Query="ABC.DEF"."dbo"."sysfiles"'].
This query works on other databases on this instance and other
instances.
Does anybody have any ideas?Does the SQL Server has multiple instances in the same box? I have noticed
that Link Server is unable to recognize\access the second instance when
there are multiple instances..
"eahind@.yahoo.co.uk" wrote:
> A database exists on a linked server that I would like to query
> When I run the following command
> SELECT * from [INSTANCE_NAME].[ABC.DEF].dbo.sysfiles
> -- Note the name of database has an "." in it (I cannot change this)
> I get the error
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object '"ABC.DEF"."dbo"."sysfiles"'. The OLE DB
> provider 'SQLOLEDB' 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='SQLOLEDB',
> Query="ABC.DEF"."dbo"."sysfiles"'].
> This query works on other databases on this instance and other
> instances.
> Does anybody have any ideas?
>|||This is the only instance on the host. I can also query other databases
on this instance - they do not have "." in their name.

query data from the linked foxpro database

hello,guys,

my question is :
how do I query data from the linked foxpro database?

more:
I have linked a visual foxpro server to my sql server database by using the addserver clause.
Two server is in a local network.
My linked foxpro server named 'fox'.
its datasourse is not a dbc file, but a directory of dbf files ,and its full path is d:\foxpro object\.
In the directory ,there are three table,'show2003.dbf','sysu.dbf','szszj.dbf'.
And the .dbf files are not in any database.
They are just three files in the same directory.
There is no dbc file.
Now I can see the table list on the right page of the linked server.
But there is something wrong with my sql clause.

sample:

select * from fox..sysu

then the message is:
server: message 7313，level 16，status 1，row 1
the appointed constructure or directory to the provider is inefficacious 'MSDASQL'

I know I may use 'openquery',or 'openrowset'.
The problem is that variable is not valid in 'openquery' and 'openrowset'.
But I must use variable.
so ,please give me some advice.

Thank you very muchHi marydan & welcome to dbforums

it could be how you've formed the SQL statement but methinks you've prob tried a few variations & considered four part three part naming conventions ??

I've no experience with a linked foxpro server i'm afraid but it looks like your MS OLEDB for ODBC Provider is saying 'lacking the power to produce a desired effect'

Have you tried setting up a system DSN with the ODBC manager & clicked TEST Connection ?.The Linked server could use the System DSN for the Connection String.

Check the version of the provider DLL for the latest version.


You can use variables with OPENQUERY by using Dynamic SQL (Hisss Spit)


DECLARE @.SQL varChar(4000)
SET @.SQL ='SELECT * FROM OPENQUERY([LinkedServerName],
SELECT 1 FROM [table] WHERE [field] = '+ @.ReqVal +''')'
EXEC(@.SQL)


Have Fun with the single quotes it's V frustrating - trial & error doubling them up.

GW|||thank you ,but other questions.

I'm sorry I may not describe the process clearly.

the process of link to foxpro server is as follows:

first,I create a system dsn named 'fortest',the drive option is 'microsoft visual vfp foxpro',the database type is dbf,then I choose the directory of dbf files as 'd:\foxpro object\' in the path option.

then,I create a linked server in sql server ,the data source is the 'fortest'.
The link is successful ,and I can see the table list on the right.

the first question:

you suggest to use a connection string.
But it's ok when I linked dbc files.
for example ,I linked to a dbc file:yuanyuan,then I can use this clause to get the result.
select * from fox.yuanyuan..szszj
I can get the result ,it's right.
but why can't when linked to dbf files?

the second question:
DECLARE @.SQL varChar(4000)
SET @.SQL ='SELECT * FROM OPENQUERY([LinkedServerName],
SELECT 1 FROM [table] WHERE [field] = '+ @.ReqVal +''')'
EXEC(@.SQL)

if @.ReqVal is char type,how can I forum the sql state?
select * from openquery([fox],‘select * from szsz where id = 'juij'’)
it's wrong of course.

so ,may you give me more help about this two question?

thank you very much for your kindness|||Heres a Step by Step Guide for setting up Linked servers & testing them for both Foxpro .DBC & .DBF Files http://support.microsoft.com/kb/207595

note the recommendations on which Providers to use and their potential problems

refselect * from openquery([fox],select * from szsz where id = 'juij')
it's wrong of course.

Answer = doubling them upid = '''' + @.BadChoiceOfValue' + '''')'

PRINT the @.SQL before executing it and you will see the output - it should copy & paste into a new qury window & work on it's own.

Good Luck - sorry I'm not familiar with FoxPro linked Servers

GW|||Thanks ,Gwilliy.That's it.

You are so kind.

best wishes

:-)

Query and Collation problem

I am querying against a Linked Server, the following statement:
SELECT * FROM Table
WHERE name_field LIKE 'José%'
The "name_field" field, is a varchar(50) with SQL_Latin1_General_CP1_CI_AI...
it's Accent Insensitive, what is right... So, when I send this query, I
receive all the 'Jose' and 'José' regs...
I have a site that does the same query, from another server, using linked
server resource... The query is almost the same:
SELECT * FROM [Server].Database.Owner.Table
WHERE name_field LIKE 'José%'
This time, looks like it's using Accent Sensitive, because it don't bring
just the 'José' regs...
It's an Asp app who is querying this way...
Does someone know what can It be?
Thx,
Rafa?
What SQL Server versions are these - the ones being used in the Linked
Server?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Query and Collation problem

I am querying against a Linked Server, the following statement:
SELECT * FROM Table
WHERE name_field LIKE 'José%'
The "name_field" field, is a varchar(50) with SQL_Latin1_General_CP1_CI_AI...
it's Accent Insensitive, what is right... So, when I send this query, I
receive all the 'Jose' and 'José' regs...
I have a site that does the same query, from another server, using linked
server resource... The query is almost the same:
SELECT * FROM [Server].Database.Owner.Table
WHERE name_field LIKE 'José%'
This time, looks like it's using Accent Sensitive, because it don't bring
just the 'José' regs...
It's an Asp app who is querying this way...
Does someone know what can It be?
Thx,
Rafa®What SQL Server versions are these - the ones being used in the Linked
Server?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Query and Collation problem

I am querying against a Linked Server, the following statement:
SELECT * FROM Table
WHERE name_field LIKE 'José%'
The "name_field" field, is a varchar(50) with SQL_Latin1_General_CP1_CI_AI..
.
it's Accent Insensitive, what is right... So, when I send this query, I
receive all the 'Jose' and 'José' regs...
I have a site that does the same query, from another server, using linked
server resource... The query is almost the same:
SELECT * FROM [Server].Database.Owner.Table
WHERE name_field LIKE 'José%'
This time, looks like it's using Accent Sensitive, because it don't bring
just the 'José' regs...
It's an Asp app who is querying this way...
Does someone know what can It be?
Thx,
Rafa?What SQL Server versions are these - the ones being used in the Linked
Server?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.