servers

Is it possible to link to an SQL 2005 server db from a SQL 2000 server? Is there a driver for this?

Sure you can. Just open Enterprise Manager and locate your SQL 2000 instance-> go to Security->Linked Servers->Add Linked Server->Choose Server Type as SQL Server->switch to Security tab and configure proper security information to login to the SQL2005.

For more information about Configuring Linked Server in SQL2000, please refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp

server using the OLEDB for ODBC Driver

I have an ODBC driver to a non-SQL database that contains legacy data that I
need to reference in my application/reports. I had originally hoped to use a
linked server using the OLEDB for ODBC provider to access the data and so be
able to create applications/reports using data stored in SQL Server 2000 and
the legacy database via a single connection.
The problem is that, according to the ODBC drive vendor, when I use the 4
part name to refer to my linked server, SQL Server only passes the driver
the base select statement without any where clause resulting in the entire
table being returned to SQL Server which then applies the filter. This gives
me an average time of 8 minutes to return a single record of a 46,000 row
table.
If I use the OPENQUERY function the same select statement takes about 2
seconds. Unfortunately though, OPENQUERY does not accept a variables as a
parameter and so the select statement must be a hard coded string which
makes it unsuitable for any but a static view.
Any suggestions on a workaround for this?You can build the entire SQL string, e.g. select * from
openquery(server, 'etc...') , and pass the string into an
EXEC(). You can find examples in this article:
HOW TO: Pass a Variable to a Linked Server Query
http://support.microsoft.com/?id=314520
-Sue
On Wed, 11 Aug 2004 12:37:48 -0500, "Charles J Ryan"
<charlesryan1@.msn.com> wrote:

>I have an ODBC driver to a non-SQL database that contains legacy data that
I
>need to reference in my application/reports. I had originally hoped to use
a
>linked server using the OLEDB for ODBC provider to access the data and so b
e
>able to create applications/reports using data stored in SQL Server 2000 an
d
>the legacy database via a single connection.
>The problem is that, according to the ODBC drive vendor, when I use the 4
>part name to refer to my linked server, SQL Server only passes the driver
>the base select statement without any where clause resulting in the entire
>table being returned to SQL Server which then applies the filter. This give
s
>me an average time of 8 minutes to return a single record of a 46,000 row
>table.
>If I use the OPENQUERY function the same select statement takes about 2
>seconds. Unfortunately though, OPENQUERY does not accept a variables as a
>parameter and so the select statement must be a hard coded string which
>makes it unsuitable for any but a static view.
>Any suggestions on a workaround for this?
>

server using the OLEDB for ODBC Driver

I have an ODBC driver to a non-SQL database that contains legacy data that I
need to reference in my application/reports. I had originally hoped to use a
linked server using the OLEDB for ODBC provider to access the data and so be
able to create applications/reports using data stored in SQL Server 2000 and
the legacy database via a single connection.
The problem is that, according to the ODBC drive vendor, when I use the 4
part name to refer to my linked server, SQL Server only passes the driver
the base select statement without any where clause resulting in the entire
table being returned to SQL Server which then applies the filter. This gives
me an average time of 8 minutes to return a single record of a 46,000 row
table.
If I use the OPENQUERY function the same select statement takes about 2
seconds. Unfortunately though, OPENQUERY does not accept a variables as a
parameter and so the select statement must be a hard coded string which
makes it unsuitable for any but a static view.
Any suggestions on a workaround for this?
You can build the entire SQL string, e.g. select * from
openquery(server, 'etc...') , and pass the string into an
EXEC(). You can find examples in this article:
HOW TO: Pass a Variable to a Linked Server Query
http://support.microsoft.com/?id=314520
-Sue
On Wed, 11 Aug 2004 12:37:48 -0500, "Charles J Ryan"
<charlesryan1@.msn.com> wrote:

>I have an ODBC driver to a non-SQL database that contains legacy data that I
>need to reference in my application/reports. I had originally hoped to use a
>linked server using the OLEDB for ODBC provider to access the data and so be
>able to create applications/reports using data stored in SQL Server 2000 and
>the legacy database via a single connection.
>The problem is that, according to the ODBC drive vendor, when I use the 4
>part name to refer to my linked server, SQL Server only passes the driver
>the base select statement without any where clause resulting in the entire
>table being returned to SQL Server which then applies the filter. This gives
>me an average time of 8 minutes to return a single record of a 46,000 row
>table.
>If I use the OPENQUERY function the same select statement takes about 2
>seconds. Unfortunately though, OPENQUERY does not accept a variables as a
>parameter and so the select statement must be a hard coded string which
>makes it unsuitable for any but a static view.
>Any suggestions on a workaround for this?
>

server to sybase database

Hi
I created linked server to sybase database using adaptive server odbc driver.
But the performance sucks while loading large tables even after manipulating
performance tab in odbc driver.
Can anyone suggest some tuning work on linked server environment to sybase.
Thanks for your wonderful help
Praveen Mohan
Hi
What do you regard as "suck"?
If you are pulling 1'000'000 records, you are limited by your network
performance.
How many records are you dealing with? is the linked server a part of a JOIN
query?
Regards
Mike
"PraveenMohan" wrote:

> Hi
> I created linked server to sybase database using adaptive server odbc driver.
> But the performance sucks while loading large tables even after manipulating
> performance tab in odbc driver.
> Can anyone suggest some tuning work on linked server environment to sybase.
> Thanks for your wonderful help
> Praveen Mohan
|||Also, it can be really revealing to look at the query plan when you go against a linked server. Some
manual optimization can be applied using OPENQUERY instead of 4-part table naming. For example,
pushing a WHERE predicate inside the OPENQUERY function to you know for sure the filtering is
performed at the remote site.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:3D332524-E849-4449-B2BD-15BB443BE6F1@.microsoft.com...[vbcol=seagreen]
> Hi
> What do you regard as "suck"?
> If you are pulling 1'000'000 records, you are limited by your network
> performance.
> How many records are you dealing with? is the linked server a part of a JOIN
> query?
> Regards
> Mike
> "PraveenMohan" wrote:
|||Praveen
Can you describe how did you do that?
I am having the problem with setting up my connection so your input will be
very helpfull.
Thank you, Gene.
"PraveenMohan" wrote:

> Hi
> I created linked server to sybase database using adaptive server odbc driver.
> But the performance sucks while loading large tables even after manipulating
> performance tab in odbc driver.
> Can anyone suggest some tuning work on linked server environment to sybase.
> Thanks for your wonderful help
> Praveen Mohan

server to sybase database

Hi
I created linked server to sybase database using adaptive server odbc driver.
But the performance sucks while loading large tables even after manipulating
performance tab in odbc driver.
Can anyone suggest some tuning work on linked server environment to sybase.
Thanks for your wonderful help
Praveen MohanAlso, it can be really revealing to look at the query plan when you go against a linked server. Some
manual optimization can be applied using OPENQUERY instead of 4-part table naming. For example,
pushing a WHERE predicate inside the OPENQUERY function to you know for sure the filtering is
performed at the remote site.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:3D332524-E849-4449-B2BD-15BB443BE6F1@.microsoft.com...
> Hi
> What do you regard as "suck"?
> If you are pulling 1'000'000 records, you are limited by your network
> performance.
> How many records are you dealing with? is the linked server a part of a JOIN
> query?
> Regards
> Mike
> "PraveenMohan" wrote:
>> Hi
>> I created linked server to sybase database using adaptive server odbc driver.
>> But the performance sucks while loading large tables even after manipulating
>> performance tab in odbc driver.
>> Can anyone suggest some tuning work on linked server environment to sybase.
>> Thanks for your wonderful help
>> Praveen Mohan|||Hi
What do you regard as "suck"?
If you are pulling 1'000'000 records, you are limited by your network
performance.
How many records are you dealing with? is the linked server a part of a JOIN
query?
Regards
Mike
"PraveenMohan" wrote:
> Hi
> I created linked server to sybase database using adaptive server odbc driver.
> But the performance sucks while loading large tables even after manipulating
> performance tab in odbc driver.
> Can anyone suggest some tuning work on linked server environment to sybase.
> Thanks for your wonderful help
> Praveen Mohan|||Praveen
Can you describe how did you do that?
I am having the problem with setting up my connection so your input will be
very helpfull.
Thank you, Gene.
"PraveenMohan" wrote:
> Hi
> I created linked server to sybase database using adaptive server odbc driver.
> But the performance sucks while loading large tables even after manipulating
> performance tab in odbc driver.
> Can anyone suggest some tuning work on linked server environment to sybase.
> Thanks for your wonderful help
> Praveen Mohan

server to Progress DB error 7399

I am trying to create a linked server, on a SQL 2000
server, to a progress database using the MERANT 3.6 ODBC
driver. I am able to do this successfully on two
different test servers, but when I try it on my live
server I get "error 7399: OLE DB provider 'MSDASQL'
reported an error. OLEDB error trace [OLE/DB
Provider 'MSDASQL' IDBInitialize::Initialize retured
0x80004005:]
Nothing appears in either the MSSQL Logs or the Progress.
Does anyone know anything about this error or how to get
around it.
TIA
Paul LivengoodTry to
SET XACT_ABORT ON
on sql-server 7.0
For further information see bol.
Posted via http://dbforums.com

server To Oracle Causes SQL Server Instance to Crash

When running a linked server to Oracle using the MSDAORA driver using in-process option checked, I'm getting the following error:

"A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)"

This then causes the instance to crash.

In the Event Log, it shows:

SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).

When using the out-of-process, it shows the column names in the query results window, but doesn't show any data then immediately in the message window it shows:

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "MSDAORA" for linked server "smtest" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 2

Cannot get the column information from OLE DB provider "MSDAORA" for linked server "smtest".

We're using:

SQL Server 2005 SP1 with hot fixes (9.0.2153) 32-bit on 64-bit o/s Windows 2003 R2 Standard x64 Edition Sevice Pack 1

We use lots of linked servers to Oracle on other servers, and can't find anything different on this one.

Appreciate any thoughts, insights!

thanks,

Steve

I have the same problem. Microsoft suggested to edit the sqlnet.ora file so onames comes before tnsnames. So far, this alone has not solved my problem, i'm still digging.|||

Emily,

We found that the problem was caused by a permissions issue on the oci.dll (oracle client .dll).

The way we found it was by using FILEMON (sysinternals site) to find access denied to the Oracle files.

Hope this helps.

Steve