**linked server**

Hi
I'm working with MS sql server 2000 and I want to add a linked server. but
there is an empty "provider name" combo box, with following message at the
bottom "Provider Name is one of the providers installed on the server"
what programs should I install in order to get a complete names of
providers and be able to continue?
(I have no problem on my personal client(WinXP) in our LAN, but this is a
problem in our server.)
my second question:
Is it possible to have a remote server throught VPN by a linked server?
(now I have the remote SQL server through a new SQL server registration
but I want to have it in a linked server in order to easily join its table
with my local network tables and ...)
Any help would be greatly appreciated.
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/1) Do you try to create a linked server to another SQL Server? Check SQL
Server under Server Type option
2) I think it is permission issue, I have never tried it. Have you tried to
create a linked server?
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tk5uxesen9ig5y@.system109.parskhazar.net...
> Hi
> I'm working with MS sql server 2000 and I want to add a linked server. but
> there is an empty "provider name" combo box, with following message at the
> bottom "Provider Name is one of the providers installed on the server"
> what programs should I install in order to get a complete names of
> providers and be able to continue?
> (I have no problem on my personal client(WinXP) in our LAN, but this is a
> problem in our server.)
>
> my second question:
> Is it possible to have a remote server throught VPN by a linked server?
> (now I have the remote SQL server through a new SQL server registration
> but I want to have it in a linked server in order to easily join its table
> with my local network tables and ...)
> Any help would be greatly appreciated.
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||Hi
Try adding the linked server through T-SQL using something like:
EXEC sp_addlinkedserver @.server='RemoteSQLServer', @.srvproduct='',
@.provider='SQLOLEDB',
@.datasrc='RemoteSvr\SQLInstance'
If you can register the remote server you should be ok to define it as a
linked server. Response over the VPN may be quite slow.
John
"M" wrote:
> Hi
> I'm working with MS sql server 2000 and I want to add a linked server. but
> there is an empty "provider name" combo box, with following message at the
> bottom "Provider Name is one of the providers installed on the server"
> what programs should I install in order to get a complete names of
> providers and be able to continue?
> (I have no problem on my personal client(WinXP) in our LAN, but this is a
> problem in our server.)
>
> my second question:
> Is it possible to have a remote server throught VPN by a linked server?
> (now I have the remote SQL server through a new SQL server registration
> but I want to have it in a linked server in order to easily join its table
> with my local network tables and ...)
> Any help would be greatly appreciated.
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
>

**** Need Help in setting OPENQUERY() with linked servers ****

Hello!
I'm a newbie running queries against linked servers. Someone had
already
setup the linked servers in SQL2000 to go access AS400/DB2 and ORACLE
tables.
Let's say there is linked server in SQL2000 called "MyLinked_AS400" and
I have
the following tables in which I need to retrieve data. These are
ficticious data.
MyAS400_DB.Table01, with these columns PRODUCT_ID, PROD_DESC, PROD_QTY
MyAS400_DB.Table02, with these columns PRODUCT_ID, ORG_DESC, ORG_QTY
and I am trying to setup a tsql statement to join these two tables and
pass
it to OPENQUERY() but getting errors. I don't remember the error right
now but it
said something about "The provider did not provide any additional
information"
The error seems to be that of when you misspelled a column name.
I had something like this:
SELECT * OPENQUERY(MyLinked_AS400,
'SELECT
Table01.PROD_DESC,
Table02.ORG_DESC
FROM MyAS400_DB.Table1 INNER JOIN
MyAS400_DB.Table2 ON
Table01.PRODUCT_ID = Table02.PRODUCT_ID
WHERE Table01.PRODUCT_ID = ''123''
FOR FETCH ONLY WITH UR
') AS dt
--
I have been getting all kinds of strange problems today partly because
I am new in
setting up these type of statements. What's wrong with the above code?
Is there
a better way to write this and still using "MyLinked_AS400"?
I had another statement like this:
SELECT * OPENQUERY(MyLinked_AS400,
'SELECT *
Table01.ZIP
FROM MyAS400_DB.Table1
WHERE Table01.ZIP = ''21157''
FOR FETCH ONLY WITH UR
') AS dt
--
It produced one record. But if I had ''19020'', it failed with the
same exact
error produced in the above statement. Does it mean if there is no row
matching
this condition, I need to code it differently?
I need you, Gurus, out there to help me. I'd greatly appreciate if you
can include a copy to my work email in below.
Thank you in advance!
Sydney
sluu@.nfiinteractive.comHi
The most obvious problem is that you are missing FROM in the SELECT * FROM
OPENQUERY(...)
I am not sure of your AS400 queries, have you checked that they work
directly on the AS400?
If you posted the error message and number it may be clearer what the errors
are without having your systems!
John
"sydney.luu@.gmail.com" wrote:

> Hello!
> I'm a newbie running queries against linked servers. Someone had
> already
> setup the linked servers in SQL2000 to go access AS400/DB2 and ORACLE
> tables.
> Let's say there is linked server in SQL2000 called "MyLinked_AS400" and
> I have
> the following tables in which I need to retrieve data. These are
> ficticious data.
> MyAS400_DB.Table01, with these columns PRODUCT_ID, PROD_DESC, PROD_QTY
> MyAS400_DB.Table02, with these columns PRODUCT_ID, ORG_DESC, ORG_QTY
> and I am trying to setup a tsql statement to join these two tables and
> pass
> it to OPENQUERY() but getting errors. I don't remember the error right
> now but it
> said something about "The provider did not provide any additional
> information"
> The error seems to be that of when you misspelled a column name.
> I had something like this:
> --
> SELECT * OPENQUERY(MyLinked_AS400,
> 'SELECT
> Table01.PROD_DESC,
> Table02.ORG_DESC
> FROM MyAS400_DB.Table1 INNER JOIN
> MyAS400_DB.Table2 ON
> Table01.PRODUCT_ID = Table02.PRODUCT_ID
> WHERE Table01.PRODUCT_ID = ''123''
> FOR FETCH ONLY WITH UR
> ') AS dt
> --
> I have been getting all kinds of strange problems today partly because
> I am new in
> setting up these type of statements. What's wrong with the above code?
> Is there
> a better way to write this and still using "MyLinked_AS400"?
> I had another statement like this:
> --
> SELECT * OPENQUERY(MyLinked_AS400,
> 'SELECT *
> Table01.ZIP
> FROM MyAS400_DB.Table1
> WHERE Table01.ZIP = ''21157''
> FOR FETCH ONLY WITH UR
> ') AS dt
> --
> It produced one record. But if I had ''19020'', it failed with the
> same exact
> error produced in the above statement. Does it mean if there is no row
> matching
> this condition, I need to code it differently?
> I need you, Gurus, out there to help me. I'd greatly appreciate if you
> can include a copy to my work email in below.
> Thank you in advance!
> Sydney
> sluu@.nfiinteractive.com
>

(SS2000) question about distributed queries

I need to write some distributed SQL. I'm accessing databases outside my local instance through Linked Servers.

My problem is: this SQL will run at many customer sites and the database names will be different at each.

So to refer to a table in another instance I of course have to use <LinkedServerName>.<dbname>.<owner>.<object>.

Problem: the <dbname> will be different depending on the customer site. So hardwiring the <dbname> into my scripts is a pretty major problem.

I want to avoid dynamically generating the SQL and executing through sp_executesql.

Is it possible to define a Linked Server in such a way that I can skip over the database name (refered to as "catalog" in the definition). I have a one to one mapping between databases and Linked Servers, so per Linked Server I can assume access to one and only one database.

I'm using SS2000 so synonyms are out of the picture.

In a nutshell: I need to write sciprts that can easily adapt to different database names in all the distributed queries I'm writing. Is it possible to do this in SS2000?

Please help!

Best Regards,

Mike

Use the OPENROWSET function (see BOL topic OPENROWSET (Transact-SQL)).

This will allow you to specify a connection provider (oledb maybe) and connection string, then a query to run on the target server/db.

EXAMPLE:

SELECT a.*

FROM OPENROWSET(<provider name>, <connection string>,

<query>) AS a;

So you could run the same query on different server and different DBs just by adjusting the connection string.

(SS2000) question about distributed queries

I need to write some distributed SQL. I'm accessing databases outside my local instance through Linked Servers.

My problem is: this SQL will run at many customer sites and the database names will be different at each.

So to refer to a table in another instance I of course have to use <LinkedServerName>.<dbname>.<owner>.<object>.

Problem: the <dbname> will be different depending on the customer site. So hardwiring the <dbname> into my scripts is a pretty major problem.

I want to avoid dynamically generating the SQL and executing through sp_executesql.

Is it possible to define a Linked Server in such a way that I can skip over the database name (refered to as "catalog" in the definition). I have a one to one mapping between databases and Linked Servers, so per Linked Server I can assume access to one and only one database.

I'm using SS2000 so synonyms are out of the picture.

In a nutshell: I need to write sciprts that can easily adapt to different database names in all the distributed queries I'm writing. Is it possible to do this in SS2000?

Please help!

Best Regards,

Mike

Use the OPENROWSET function (see BOL topic OPENROWSET (Transact-SQL)).

This will allow you to specify a connection provider (oledb maybe) and connection string, then a query to run on the target server/db.

EXAMPLE:

SELECT a.*

FROM OPENROWSET(<provider name>, <connection string>,

<query>) AS a;

So you could run the same query on different server and different DBs just by adjusting the connection string.