ODBC Driver for AS400

Hi,

I am presently using Client Access ODBC driver (32-bit) to connect to the AS400. I have set up a linked server that enables me to run queries against the AS400 using the driver. However I seek to have a driver that could give better performance. Right now I can extract 6 million rows from the AS400 table in like 2 hrs. Now is there an ODBC driver that can do better than that? Also I seek an evaluation edition of the driver if possible. Moreover I am the only developer and so a single user license is what I can have my supervisor budget.

Thanks,

VivekLook at www.hitsw.com. I've never used their drivers, but a friend of mine did and he praised their performance to the sky.

Regards,

hmscott

Hi,

I am presently using Client Access ODBC driver (32-bit) to connect to the AS400. I have set up a linked server that enables me to run queries against the AS400 using the driver. However I seek to have a driver that could give better performance. Right now I can extract 6 million rows from the AS400 table in like 2 hrs. Now is there an ODBC driver that can do better than that? Also I seek an evaluation edition of the driver if possible. Moreover I am the only developer and so a single user license is what I can have my supervisor budget.

Thanks,

Viveksql

ODBC Data Source to a linked SQL Server

I'd like to set up an ODBC Data Source to a table in a linked SQL
Server, via
my local SQL Server.
When I use the ODBC Data Sources program to set up the ODBC DSN, it
only
shows databases in the local SQL Server, not in the linked one. If I
try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
it is an invalid table.
How can I create an ODBC Data Source which uses a database in the
linked server as its default database?
I've got a similar question. In my case I linked an Oracle database to my
SQL Server 2005 Standard database. I also want to see the tables in the
linked server. I hope someone can answer this for both of us...
Randall Arnold
<listrecv@.gmail.com> wrote in message
news:1137940173.907969.215550@.o13g2000cwo.googlegr oups.com...
> I'd like to set up an ODBC Data Source to a table in a linked SQL
> Server, via
> my local SQL Server.
> When I use the ODBC Data Sources program to set up the ODBC DSN, it
> only
> shows databases in the local SQL Server, not in the linked one. If I
> try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
> it is an invalid table.
> How can I create an ODBC Data Source which uses a database in the
> linked server as its default database?
>

ODBC Data Source to a linked SQL Server

I'd like to set up an ODBC Data Source to a table in a linked SQL
Server, via
my local SQL Server.
When I use the ODBC Data Sources program to set up the ODBC DSN, it
only
shows databases in the local SQL Server, not in the linked one. If I
try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
it is an invalid table.
How can I create an ODBC Data Source which uses a database in the
linked server as its default database?I've got a similar question. In my case I linked an Oracle database to my
SQL Server 2005 Standard database. I also want to see the tables in the
linked server. I hope someone can answer this for both of us...
Randall Arnold
<listrecv@.gmail.com> wrote in message
news:1137940173.907969.215550@.o13g2000cwo.googlegroups.com...
> I'd like to set up an ODBC Data Source to a table in a linked SQL
> Server, via
> my local SQL Server.
> When I use the ODBC Data Sources program to set up the ODBC DSN, it
> only
> shows databases in the local SQL Server, not in the linked one. If I
> try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
> it is an invalid table.
> How can I create an ODBC Data Source which uses a database in the
> linked server as its default database?
>

ODBC Connection to a linked database

I'd like to set up an ODBC DSN to a table in a linked SQL Server, via
my local SQL Server.

I'm having a few problems:
1. When I use Enterprise Manager to link the remote SQL Server, it
doesn't allow me to select the database in the remote server. It only
shows one database.

2. When I use the Data Sources program to set up the ODBC DSN, it only
shows databases in the local SQL Server, not in the linked one. If I
try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
it is an invalid table.Thats quite normal, but you are able to use the four part notation,
which will switch to another database if specified.

E.g. ODBC Connection points automatically to Northwind ( as the default
database of the user) you can use

SELECT <columnlist> FROM Linkdservername.Databasename.Objectname

which switches to the other database to select.

HTH, Jens Suessmeyer.|||Jens,

Thank you.

I'd rather not need to change the table names in the MDB. They just
use the default database of the ODBC Data Source. How can I set the
ODBC default database to be a database in a linked server?|||if you are using a DSN, just go in the ODBC Administrator (of Windows)
and change the database to the ones needed.

HTH, jens Suessmeyer.|||Jens,

Thanks - but the ODBC Adminsitrator only shows *local* databases, not
databases on the linked server. If I try to just type it in, it tells
me that it's not a valid db, and refuses to let me do so!!!|||How did you register the linked server ? Is it a DSN bind linked server
or was it specified within a conneciton string ? ODBC Administrator
doesnt show only the local databases, it shows the servers/databases
that were specified within the DSN. Look at the server ODBC Admin to
see if connection to your linked server is specified (some people look
on their local DSN rather than looking on the remote DSNs onthe SQL
Server which uses the linked server)

HTH, Jens Suessmeyer.

ODBC Connection Problem

Hi all
I'm attempting to setup a linked server to a cache database within
Enterprise Manager. I setup an ODBC driver on the server and the
connection worked when tested.
Selected Micorsoft OLE DB Provider for ODBC Driver from the drop down
list.
Entered the data source name in the Data Source field and I keep
getting "error 7399: OLE DB provider 'MSDASQL' reported an error. OLE
DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x800004005;]"
Does the ODBC driver need to be installed on the same drive as SQL?
The driver is a system DNS stored on C: while SQL is installed on
another drive.
Or am I missing some parameters in the setup?
Thanks
mike
Got it to work.
Had to set everything up on the server and not on my machine.

ODBC Connection Problem

Hi all
I'm attempting to setup a linked server to a cache database within
Enterprise Manager. I setup an ODBC driver on the server and the
connection worked when tested.
Selected Micorsoft OLE DB Provider for ODBC Driver from the drop down
list.
Entered the data source name in the Data Source field and I keep
getting "error 7399: OLE DB provider 'MSDASQL' reported an error. OLE
DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x800004005;]"
Does the ODBC driver need to be installed on the same drive as SQL?
The driver is a system DNS stored on C: while SQL is installed on
another drive.
Or am I missing some parameters in the setup?
Thanks
mikeGot it to work.
Had to set everything up on the server and not on my machine.

ODBC call failed

Hello,
I use Linked Tables to connect to my SQL Server database.
I have a number of reports which worked fine when I was
using test data (few thousand records).
I just loaded in a million records and ran a report, and
received the following error:
ODBC call failed
when running this query:
SELECT regionorder, Regions3, 1 As Quarter, January as
A, February as B, March as C FROM
RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 2 As Quarter, April, May,
June FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 3 As Quarter, July, August,
September FROM RegionofResidenceCrosstab
UNION ALL SELECT regionorder, Regions3, 4 As Quarter,
October, November, December FROM
RegionofResidenceCrosstab;
RegionofResidenceCrosstab looks like this:
TRANSFORM "~" & Count(IIf(DatePart('yyyy',
& #91;F_ARRV_DAT],1,0)=forms!DateRangePrev
iousCurrentYear!
PrevYear,[Final_Clean Tourists Query].[TRAV-KEY])) & "~"
& Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=forms!
DateRangePreviousCurrentYear!CurrYear,[Final_Clean
Tourists Query].[TRAV-KEY])) AS Expr1
SELECT [Final_Clean Tourists Query].REGIONS3,
[Final_Clean Tourists Query].regionorder
FROM [Final_Clean Tourists Query]
WHERE [Final_Clean Tourists Query].REGIONS3<>''
GROUP BY [Final_Clean Tourists Query].regionorder,
[Final_Clean Tourists Query].REGIONS3
PIVOT Format([F_ARRV_DAT],"mmmm") in
("January", "February", "March", "April","May","June","Jul
y","August","September","October","November","December");
1. I am wondering if the query is not simply timing out.
2. Given that these queries were written for MS Access
maybe I need to convert them into stored procedures to be
called by the report.
Any other suggestions?Your particular query is way too much for Access to handle. The UNION
clauses are causing all rows to be fetched locally into Access for
processing. Then Access tries to run the IIf and other functions on
each row, one row at a time. So the answer is, yes indeedy, you need
to convert this monster query to a stored procedure, which you can
then call from a pass-through query that you can base the report on.
One other point -- Keep all format functions and the like in the
report itself, don't put those in the stored procedure. You'll just
slow down the execution of the stored procedure, and the report has to
process each line in the result set one at a time anyway. What you
want to do is let the stored procedure do all the data processing,
hand the result set back to Access, and let Access do the
presentation.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Fri, 19 Mar 2004 09:20:34 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:

>Hello,
>I use Linked Tables to connect to my SQL Server database.
>I have a number of reports which worked fine when I was
>using test data (few thousand records).
>I just loaded in a million records and ran a report, and
>received the following error:
>ODBC call failed
>when running this query:
>SELECT regionorder, Regions3, 1 As Quarter, January as
>A, February as B, March as C FROM
>RegionofResidenceCrosstab
>UNION ALL
>SELECT regionorder, Regions3, 2 As Quarter, April, May,
>June FROM RegionofResidenceCrosstab
>UNION ALL
>SELECT regionorder, Regions3, 3 As Quarter, July, August,
>September FROM RegionofResidenceCrosstab
>UNION ALL SELECT regionorder, Regions3, 4 As Quarter,
>October, November, December FROM
>RegionofResidenceCrosstab;
>RegionofResidenceCrosstab looks like this:
>TRANSFORM "~" & Count(IIf(DatePart('yyyy',
>& #91;F_ARRV_DAT],1,0)=forms!DateRangePrev
iousCurrentYear!
>PrevYear,[Final_Clean Tourists Query].[TRAV-KEY])) & "~"
>& Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=forms!
>DateRangePreviousCurrentYear!CurrYear,[Final_Clean
>Tourists Query].[TRAV-KEY])) AS Expr1
>SELECT [Final_Clean Tourists Query].REGIONS3,
>[Final_Clean Tourists Query].regionorder
>FROM [Final_Clean Tourists Query]
>WHERE [Final_Clean Tourists Query].REGIONS3<>''
>GROUP BY [Final_Clean Tourists Query].regionorder,
>[Final_Clean Tourists Query].REGIONS3
>PIVOT Format([F_ARRV_DAT],"mmmm") in
>("January", "February", "March", "April","May","June","Jul
>y","August","September","October","November","December");
>
>1. I am wondering if the query is not simply timing out.
>2. Given that these queries were written for MS Access
>maybe I need to convert them into stored procedures to be
>called by the report.
>Any other suggestions?
>|||Hi,
Thanks for the pointers, guess I have my work cut out for
me. I may also try upsizing to an ADP to try to improve
performance.

>--Original Message--
>Your particular query is way too much for Access to
handle. The UNION
>clauses are causing all rows to be fetched locally into
Access for
>processing. Then Access tries to run the IIf and other
functions on
>each row, one row at a time. So the answer is, yes
indeedy, you need
>to convert this monster query to a stored procedure,
which you can
>then call from a pass-through query that you can base the
report on.
>One other point -- Keep all format functions and the like
in the
>report itself, don't put those in the stored procedure.
You'll just
>slow down the execution of the stored procedure, and the
report has to
>process each line in the result set one at a time anyway.
What you
>want to do is let the stored procedure do all the data
processing,
>hand the result set back to Access, and let Access do the
>presentation.
>-- Mary
>Microsoft Access Developer's Guide to SQL Server
>http://www.amazon.com/exec/obidos/ASIN/0672319446
>On Fri, 19 Mar 2004 09:20:34 -0800, "blinton25"
><blinton25@.hotmail.com> wrote:
>
database.
August,
("January", "February", "March", "April","May","June","Jul
be
>.
>|||You will gain nothing in performance simply from upsizing to an ADP.
There's nothing magical about an ADP vs. an mbd with linked tables --
you're going to need to rewrite your app no matter which way you slice
it. You'll still need to re-do that query as a proc (since there's no
such thing as a saved query in an ADP anyway). What you give up in an
ADP is a lot of flexibility (local storage, queries, etc.). Also,
forms work differently by fetching snapshots, not dynasets. There's
simply no easy way to upsize a large, complex Jet app that doesn't
involve a re-write.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sat, 20 Mar 2004 10:24:00 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:
>Hi,
>Thanks for the pointers, guess I have my work cut out for
>me. I may also try upsizing to an ADP to try to improve
>performance.
>
>handle. The UNION
>Access for
>functions on
>indeedy, you need
>which you can
>report on.
>in the
>You'll just
>report has to
>What you
>processing,
>database.
>August,
>("January", "February", "March", "April","May","June","Jul
>be|||Hello,
Sorry, I am looking at performance in terms of moving
processing to the server versus the client and reducing
network traffic. Understood that I will have to rewrite
the queries as stored procedures, and initially will do
this and then pass through to the stored Proc.
Doing some reading at:
http://www.upsizewizard.com/Reference_Page.htm
http://www.granite.ab.ca/access/sqlserverupsizing.htm
to help me understand some approaches to achieving my
goals.
Thanks again for the guidance.

>--Original Message--
>You will gain nothing in performance simply from
upsizing to an ADP.
>There's nothing magical about an ADP vs. an mbd with
linked tables --
>you're going to need to rewrite your app no matter which
way you slice
>it. You'll still need to re-do that query as a proc
(since there's no
>such thing as a saved query in an ADP anyway). What you
give up in an
>ADP is a lot of flexibility (local storage, queries,
etc.). Also,
>forms work differently by fetching snapshots, not
dynasets. There's
>simply no easy way to upsize a large, complex Jet app
that doesn't
>involve a re-write.
>-- Mary
>Microsoft Access Developer's Guide to SQL Server
>http://www.amazon.com/exec/obidos/ASIN/0672319446
>On Sat, 20 Mar 2004 10:24:00 -0800, "blinton25"
><blinton25@.hotmail.com> wrote:
>
for
into
the
like
procedure.
the
anyway.
the
was
and
as
May,
Quarter,
& "~"
("January", "February", "March", "April","May","June","Jul
");
out.
Access
to
>.
>|||I understood that -- I was just saying that you gain nothing in terms
of performance from an ADP that you can't also achieve with an mdb
front-end. The key is fetching only needed data on the client, and an
mdb gives you a lot more options for caching static data locally, etc.
The first link you reference in your post does not answer the question
it poses "why ADP" other than to spout generalities about
client-server being faster. What the Access team itself is saying (at
recent conferences) is that they are recommending mdb over adp because
you give up so much flexibility with an adp. Performance is very much
dependent on factors such as efficient database design, avoidance of
concurrency conflicts, fetching less data, network speed and other
factors that have nothing to do with the client software. It's good
that you're doing reading up on the subject before leaping in, just be
aware that not everything you read is necessarily true or accurate.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sun, 21 Mar 2004 09:12:30 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:
>Hello,
>Sorry, I am looking at performance in terms of moving
>processing to the server versus the client and reducing
>network traffic. Understood that I will have to rewrite
>the queries as stored procedures, and initially will do
>this and then pass through to the stored Proc.
>Doing some reading at:
>http://www.upsizewizard.com/Reference_Page.htm
>http://www.granite.ab.ca/access/sqlserverupsizing.htm
>to help me understand some approaches to achieving my
>goals.
>Thanks again for the guidance.
>
>upsizing to an ADP.
>linked tables --
>way you slice
>(since there's no
>give up in an
>etc.). Also,
>dynasets. There's
>that doesn't
>for
>into
>the
>like
>procedure.
>the
>anyway.
>the
>was
>and
>as
>May,
>Quarter,
>& "~"
>("January", "February", "March", "April","May","June","Jul
>");
>out.
>Access
>to

ODBC Call fail - Strange behaviour

Hi.
I just installed Sql server (in fact MSDE) and exported my Access 2002
projet to this database instance.
I then linked my Sql server tables within my access project.
I get a strange behaviour for some queries. I receive the message 'ODBC -
Call fail'. It seems to be a timeout pb.
The query involves two tables.
I checked my query. Everithing is ok.
I checked the first table. I can read data and filter it.
I checked the second table. I can read data, but if I want to filter any
column, I get an ODBC call fail. The table contains only 802 records.
Any idea ?
Configuration :
MSDE Rel A on XP home SP1.
Access 2002 client, MDAC 2.8, on XP Pro.
Check your data on the SQL server. You may have some data that is null and
the SQL server table def is not allowing nulls? Just a thought. Usually
these errors are caught during the import process...
I would start by creating a brand new query and only add one column at a
time. See if you can then sort on that column. Carry on like this - adding
new columns each time until the error happens. Then check that columns data
on the SQL server so see if there happens to be null values present...
"Alain" <alain.roghi_nospam@.laposte.net> wrote in message
news:uU5H9BEmEHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi.
> I just installed Sql server (in fact MSDE) and exported my Access 2002
> projet to this database instance.
> I then linked my Sql server tables within my access project.
> I get a strange behaviour for some queries. I receive the message 'ODBC -
> Call fail'. It seems to be a timeout pb.
> The query involves two tables.
> I checked my query. Everithing is ok.
> I checked the first table. I can read data and filter it.
> I checked the second table. I can read data, but if I want to filter any
> column, I get an ODBC call fail. The table contains only 802 records.
> Any idea ?
> Configuration :
> MSDE Rel A on XP home SP1.
> Access 2002 client, MDAC 2.8, on XP Pro.
>

ODBC 3146

Excuse me for my poor english
I have MS access Database linked with SQL SERVER 7.
Only 2 pc not execute a big Query.This query is composed of lot subquery.
i have ODBC ERROR 37000. and this message "Alias and column (MS3) is
incorrect"
With SQL.LOG i read the syntax SQL is not identique with the sql synatx of
good query.
What do you Think of this? the ODBC drivers et Jet DB are the last version.
Thanks for your helpHi
Have you run the query through Query Analyser to check that it will work?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnodbc/html/odbcsql.asp
describes the error 37000 as being possibly due to returning multiple
resultsets or a statement not supported by server side cursors.
Without seeing your SQL Statement it is not possible to know this!
Also
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore98/HTML/_core_error.3a_.syntax_error_or_access_violation.asp
describes an error occurring on SQL Server SP2, you may want to check it out
even though you may be on a higher version
John
"Sql_NeedHelp" wrote:
> Excuse me for my poor english
> I have MS access Database linked with SQL SERVER 7.
> Only 2 pc not execute a big Query.This query is composed of lot subquery.
> i have ODBC ERROR 37000. and this message "Alias and column (MS3) is
> incorrect"
> With SQL.LOG i read the syntax SQL is not identique with the sql synatx of
> good query.
> What do you Think of this? the ODBC drivers et Jet DB are the last version.
> Thanks for your help
>
>

ODBC / Oracle Linked Server Problem

I have a SQL server that I am trying to link to a number of Oracle environments. After much tuning, we managed to achieve this although the four-part naming was not possible and we had to use Openquery and run pass throughs.

Nothing in our configuration has changed and SQL Server is no longer able connect to the linked databases. The Oracle client on the PC is fine and is able tnsping any of the remote databases. I am also able to create ODBC connections to the remote databases on the SQL box that are fine.

Using a datalink in DTS, I can connect to the remote databases. This suggests to me that there is something wrong within the actual database links. I have set them up using the working ODBC DSN's on the SQL box.

If I try and run a query against them in Query Analyser, I get the following error message :

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

If I click on the tables icon in EM to view the remote catalogues I get the following error :

Error 7399: OLE DB provider 'MSDORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

Any help that could be give on this would be greatly appreciated.Can you post the script(s) that you used to create the linked servers on your SQL Server?

Also, I think it would be helpful if you could post the content of the tnsname.ora file.

regards,

hmscott|||Hi, it appears the problem lies somewhere in the Oracle client on the server or the NT build. We have successfully managed to get the links up and alive by bouncing the server once a day, stopping and restrating the SQL Services and refreshing the login details.

We are currently investigating as to whether it not it could be related to the connections created by Terminal Services.


Originally posted by hmscott
Can you post the script(s) that you used to create the linked servers on your SQL Server?

Also, I think it would be helpful if you could post the content of the tnsname.ora file.

regards,

hmscott|||I'm ware of the errmsg "Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name
] "
I think the problem is that u haven't configure sql*net correctly.
See the tnsnames.ora in %ORACLE_HOME%/network/admin.

Obtaining a value from Openquery?

Hi all,

I have an Informix Dynamic Server linked within my MS SQL 7 server. What I want to achieve is to be able to obtain a value from the informix table and then to use this value to update the MS SQL server table. I am doing this within a trigger on SQL Server. I am not doing this from infromix as I cant get informix to see the SQL Server.

My problem is that I dont know how to assign the query result to a variable so I can use it in my Update. Can anyone help me with my syntax?? Below is my variable settings and query within the Insert trigger...(Not sure if its correct)

DECLARE @.TSQL VARCHAR(100)
DECLARE @.NAMEID VARCHAR(10)

SET @.NAMEID = (Select Inserted.NameID from Inserted)

SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'

EXEC (@.TSQL)

How do I set a variable with the nar_num value that I get back from the informix server. Any Help would be great.

Thanks
Anthonyyou would need to fill in the data type for nar_num but give this a try:

DECLARE @.TSQL VARCHAR(100)
, @.NAMEID VARCHAR(10)

create table #tmp(nar_num <data type>)

select @.NAMEID = min(NameID) from Inserted
while (@.NAMEID is not null) begin
SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'

truncate table #tmp
insert into #tmp
EXEC (@.TSQL)

select @.NAMEID = min(NameID) from Inserted where nameid > @.NAMEID
end

Please note that I changed things a bit to handle more than one one record.

obtain sql server login name..

i'm updating an Access 2000 db thats linked to SQL server 2000, and want to
create separate id/passwords for each user, and create an audit trail for
data modifications.
Any suggestions for an audit trail?
How can Access obtain the sql server login for the current user to store on
table?
Thanks!"select system_user" will return the current user.
To track data modification, you either need to have history tables with
triggers on the main tables, or have a trace running. You can research both
in Books Online. Generally, people will maintain a history table with who,
when, and from what system the modification occurred.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"nycdon" wrote:

> i'm updating an Access 2000 db thats linked to SQL server 2000, and want t
o
> create separate id/passwords for each user, and create an audit trail for
> data modifications.
> Any suggestions for an audit trail?
> How can Access obtain the sql server login for the current user to store o
n
> table?
> Thanks!
>

Obscure linked server error

Hello,

I've got 2 SQL servers in a test environment - one is 2000 and the other is MSDE and they are seperate machines. I have added the MSDE server as a linked server to my 2000 box by issuing the following command:

EXEC sp_addlinkedserver '192.168.1.108', N'SQL Server'

I can query the database using an openquery in a select statement like so:

select * from openquery([192.168.1.108], 'select * from remote_test.dbo.products')

but when I try a direct query like the following:

select * from [192.168.1.108].remote_test.dbo.products

I get the following error:

An error occurred while executing batch. Error message is: Processing of results from SQL Server failed because of an invalid multipart name "192.168.1.108.remote_test.dbo.products", the current limit of "4" is insufficient.

I can't seem to figure out anything about this error. Anyone have any ideas?

Thanks,

Jeff Balcerzak

What about choosing another name than the IP ;-) ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--|||That does work if I specify the server name on my local network. If the production server is not going to be in the local network, would I create an alias to it with the public IP address?|||What about using an entry in the lmhost file to the outside IP?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||I actually added an entry in hosts file and created an alias to my public IP to test. I physically moved my test server to a different part of my network so it is not visible to the lan and created a port forward on my router to direct wan traffic on 1433 to the test server. Now when I execute the sp_addlinked server with the server name, it seems to work fine. Thanks for your help.

object_id problem

I can use the following successfully:
SELECT OBJECT_ID('myDB..myTable')
but on a linked server I cannot get this working. Is this the right syntax?
Should it work? The server name is box. This gives me a syntax error near
myDB.
Select * From Openquery(box, 'SELECT OBJECT_ID('myDB..myTable')')Try something like this:
DECLARE @.sql NVARCHAR(2000)
SELECT @.sql = 'SELECT OBJECT_ID(''myDB..myTable'')'
PRINT @.sql
EXEC linkedserver..sp_executesql @.sql
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Derek Hart" wrote:

> I can use the following successfully:
> SELECT OBJECT_ID('myDB..myTable')
> but on a linked server I cannot get this working. Is this the right synta
x?
> Should it work? The server name is box. This gives me a syntax error near
> myDB.
> Select * From Openquery(box, 'SELECT OBJECT_ID('myDB..myTable')')
>
>|||You need to double the inner single quotes:
Select * From Openquery(box, 'SELECT OBJECT_ID(''myDB..myTable'')')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Derek Hart" <derekmhart@.yahoo.com> wrote in message news:uiEB$MieGHA.4304@.TK2MSFTNGP05.phx
.gbl...
>I can use the following successfully:
> SELECT OBJECT_ID('myDB..myTable')
> but on a linked server I cannot get this working. Is this the right synta
x? Should it work? The
> server name is box. This gives me a syntax error near myDB.
> Select * From Openquery(box, 'SELECT OBJECT_ID('myDB..myTable')')
>