linked server dynamic SQL help

declare @.var varchar(50)
select @.var='ServerA'
select name from @.var.master.dbo.sysobjects -- Need help here
Right now its just one server but eventually i want to probably use a group
of servers and kinda use a cursor.You can do it via dynamic sql
declare @.var varchar(50)
select @.var='ServerA'
exec ('select name from '+ @.var + '.master.dbo.sysobjects')
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23uSu9Y7DFHA.1188@.tk2msftngp13.phx.gbl...
> declare @.var varchar(50)
> select @.var='ServerA'
> select name from @.var.master.dbo.sysobjects -- Need help here
> Right now its just one server but eventually i want to probably use a
group
> of servers and kinda use a cursor.
>|||Yup i should have mentioned that i tried that.. and thats why posted the
other message with some code. Its where I want to get it in a cursor
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:epeI$i7DFHA.3824@.TK2MSFTNGP10.phx.gbl...
> You can do it via dynamic sql
> declare @.var varchar(50)
> select @.var='ServerA'
> exec ('select name from '+ @.var + '.master.dbo.sysobjects')
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23uSu9Y7DFHA.1188@.tk2msftngp13.phx.gbl...
> group
>|||Why do you want to use a cursor? They can be performance hogs.
For static sets of data, I use set operations.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OtHIKr7DFHA.1040@.TK2MSFTNGP09.phx.gbl...
> Yup i should have mentioned that i tried that.. and thats why posted the
> other message with some code. Its where I want to get it in a cursor
>
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:epeI$i7DFHA.3824@.TK2MSFTNGP10.phx.gbl...
>

linked server direct query problem

Can anyone tell me what's wrong when I was successful to use:
select * from openquery(linkedservername,'select * from
catalogname.dbo.tablename')
However, when I used direct query:
select * from linkedservername.catalogname.dbo.tablename
and it failed with this error:
++++++++++++++++++++++++++++++++++++++++
++++++++++++++++
OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface
returned 0x80004005: The provider did not give any information about the
error.].
++++++++++++++++++++++++++++++++++++++++
++++++++++++++++
both dbes are SQL 2000 servers. Linked server was configured to use SQLOLEDB
as the provider...Try deleting the datasource information and just leaving the provider
string.
"bNguyen" <bNguyen@.discussions.microsoft.com> wrote in message
news:44825EF1-0AD5-407C-8E61-AA9234DE2BFA@.microsoft.com...
> Can anyone tell me what's wrong when I was successful to use:
> select * from openquery(linkedservername,'select * from
> catalogname.dbo.tablename')
> However, when I used direct query:
> select * from linkedservername.catalogname.dbo.tablename
> and it failed with this error:
> ++++++++++++++++++++++++++++++++++++++++
++++++++++++++++
> OLE DB provider 'SQLOLEDB' reported an error. The provider did not give
any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterfac
e
> returned 0x80004005: The provider did not give any information about the
> error.].
> ++++++++++++++++++++++++++++++++++++++++
++++++++++++++++
> both dbes are SQL 2000 servers. Linked server was configured to use
SQLOLEDB
> as the provider...|||I am not sure I understood what you meant by deleting data source. When I
used direct query, I believe we must use four-part name in the statement to
reference linked server right? Please elaborate if you can... thanks
"Derrick Leggett" wrote:

> Try deleting the datasource information and just leaving the provider
> string.
>
> "bNguyen" <bNguyen@.discussions.microsoft.com> wrote in message
> news:44825EF1-0AD5-407C-8E61-AA9234DE2BFA@.microsoft.com...
> any
> SQLOLEDB
>
>

linked server direct query problem

Can anyone tell me what's wrong when I was successful to use:
select * from openquery(linkedservername,'select * from
catalogname.dbo.tablename')
However, when I used direct query:
select * from linkedservername.catalogname.dbo.tablename
and it failed with this error:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface
returned 0x80004005: The provider did not give any information about the
error.].
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
both dbes are SQL 2000 servers. Linked server was configured to use SQLOLEDB
as the provider...Try deleting the datasource information and just leaving the provider
string.
"bNguyen" <bNguyen@.discussions.microsoft.com> wrote in message
news:44825EF1-0AD5-407C-8E61-AA9234DE2BFA@.microsoft.com...
> Can anyone tell me what's wrong when I was successful to use:
> select * from openquery(linkedservername,'select * from
> catalogname.dbo.tablename')
> However, when I used direct query:
> select * from linkedservername.catalogname.dbo.tablename
> and it failed with this error:
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> OLE DB provider 'SQLOLEDB' reported an error. The provider did not give
any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface
> returned 0x80004005: The provider did not give any information about the
> error.].
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> both dbes are SQL 2000 servers. Linked server was configured to use
SQLOLEDB
> as the provider...|||I am not sure I understood what you meant by deleting data source. When I
used direct query, I believe we must use four-part name in the statement to
reference linked server right? Please elaborate if you can... thanks
"Derrick Leggett" wrote:
> Try deleting the datasource information and just leaving the provider
> string.
>
> "bNguyen" <bNguyen@.discussions.microsoft.com> wrote in message
> news:44825EF1-0AD5-407C-8E61-AA9234DE2BFA@.microsoft.com...
> > Can anyone tell me what's wrong when I was successful to use:
> >
> > select * from openquery(linkedservername,'select * from
> > catalogname.dbo.tablename')
> >
> > However, when I used direct query:
> >
> > select * from linkedservername.catalogname.dbo.tablename
> >
> > and it failed with this error:
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > OLE DB provider 'SQLOLEDB' reported an error. The provider did not give
> any
> > information about the error.
> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface
> > returned 0x80004005: The provider did not give any information about the
> > error.].
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > both dbes are SQL 2000 servers. Linked server was configured to use
> SQLOLEDB
> > as the provider...
>
>

Linked Server

I have a linked as400 and have successfully executed queries against it.

e.g select HDRID,HDRDSC from openquery(SERVER,'select hdrid,hdrdesc from MMTTHOF.table')

I have a problem however when I try to compare a column in my local database with one on the linked server.

The error received is:
"Cannot resolve collation conflict for equal to operation."

I have played around with the Remote Collation and Collation Name settings in SQL Server but to no avail. I then used DTS to bring the table from the AS400 in to the local database and tried running a query against it linking a Char(3) field to its counterpart in the other table. The same error occured. I solved this by changing the collation on the fields in both tables to Latin_General_CI_AS (Although I understood the default dB collation to be SQL_LAtin_General_CP1_CI_AS, so I thought this would have worked).

Anyhow I'm still unable to run the query against the original AS400 table.

Any thoughts would be much appreciated guy's n gals

JBrute force is sometimes appropriate. When using a linked server, it is sometimes simplest to pick an arbitrary collation that works well, the force both of the columns to use that collation!

-PatP