servers

I have two servers running SQL Server 2000 SP3 and SP4. The one running SP4
was created recently by restoring all the system and user databases from
another server which has now been retired. Now I have both the servers linked
from the other server and they both show up in the Enterprise Manager.
However, I can only access the server running SP4 from the server running
SP3, not vice versa. It gives an error:
Server: Msg 17, Level 16, State 1, Line 2
SQL Server does not exist or access denied.
Any insight will be greatly appreciated. Thanks.Are you able to ping old server from the new one?
Manu
"sharman" wrote:
> I have two servers running SQL Server 2000 SP3 and SP4. The one running SP4
> was created recently by restoring all the system and user databases from
> another server which has now been retired. Now I have both the servers linked
> from the other server and they both show up in the Enterprise Manager.
> However, I can only access the server running SP4 from the server running
> SP3, not vice versa. It gives an error:
> Server: Msg 17, Level 16, State 1, Line 2
> SQL Server does not exist or access denied.
> Any insight will be greatly appreciated. Thanks.

servers

I have 2 test boxes each running sql server 2000 standard
edition. For both boxes i've created a linked server and
specified the name of the system DSN of the ODBC data
source within the properties of the linked server created.
A drive has been mapped to the Access database i want to
see and the ODBC connections have been created. But
unfortunately only one of my sql boxes can see the tables
while the other generates a Error '7399' message. why,
please advise. thank you.
Error 7399 doesn't tell us much. We'd need more details
regarding the exact error message. Can you access the mapped
drive? Have you tried using a UNC path instead of a mapped
drive? Mapped drives are often unreliable for this type of
situation.
-Sue
On Wed, 15 Sep 2004 13:26:47 -0700, "vincent"
<vincec@.mail.com> wrote:

>I have 2 test boxes each running sql server 2000 standard
>edition. For both boxes i've created a linked server and
>specified the name of the system DSN of the ODBC data
>source within the properties of the linked server created.
>A drive has been mapped to the Access database i want to
>see and the ODBC connections have been created. But
>unfortunately only one of my sql boxes can see the tables
>while the other generates a Error '7399' message. why,
>please advise. thank you.
|||Thank you for responding. I can access the mapped drive and i've tried
using the absolute path, both attempts have been unsuccessful. Because
the access database i'm tring to access is of a relitively small size if
copied it locally and i still wasn't able to access the tables. So I
image the problem would have to be with the OLE DB provider? This is the
stored procedure that trys to execute.
select invoice_num,authorize_code from OPENQUERY(idd3,'select * from
trans_table')
where invoice_num like 'OEPP%'
and (authorize_code is not null
or ltrim(rtrim(authorize_code)) <> '')
When i view my job history logs i receive all of these error messages.
DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147217900
(80040E14)
Error string: OLE DB provider 'MSDASQL' reported an error.
Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0
Error Detail Records: Error: -2147217900 (80040E14);
Provider Error: 7399 (1CE7)
Error: -2147217900 (80040E14);
Provider Error: 7300 (1C84)
Error string: OLE DB error trace [OLE/DB Provider 'MSDASQL'
IDBInitialize::Initialize returned 0x80004005: ].
Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0
Error: -2147217900 (80040E14); Provider Error: 7312 (1C90)
Error string: [OLE/DB provider ret... Process Exit Code 1.
The step failed.
Thanks in advance.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||What happens when you use Query Analyzer and execute:
dbcc traceon(7300, 3604)
and then execute the query in Query Analyzer (not DTS)?
Do you have any additional error messages?
-Sue
On Wed, 22 Sep 2004 06:54:45 -0700, Vincent Cristiano
<vincec@.mail.com> wrote:

>Thank you for responding. I can access the mapped drive and i've tried
>using the absolute path, both attempts have been unsuccessful. Because
>the access database i'm tring to access is of a relitively small size if
>copied it locally and i still wasn't able to access the tables. So I
>image the problem would have to be with the OLE DB provider? This is the
>stored procedure that trys to execute.
>select invoice_num,authorize_code from OPENQUERY(idd3,'select * from
>trans_table')
>where invoice_num like 'OEPP%'
>and (authorize_code is not null
>or ltrim(rtrim(authorize_code)) <> '')
>When i view my job history logs i receive all of these error messages.
>DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147217900
>(80040E14)
>Error string: OLE DB provider 'MSDASQL' reported an error.
>Error source: Microsoft OLE DB Provider for SQL Server
>Help file: Help context: 0
>Error Detail Records: Error: -2147217900 (80040E14);
>Provider Error: 7399 (1CE7)
>Error: -2147217900 (80040E14);
>Provider Error: 7300 (1C84)
>Error string: OLE DB error trace [OLE/DB Provider 'MSDASQL'
>IDBInitialize::Initialize returned 0x80004005: ].
>Error source: Microsoft OLE DB Provider for SQL Server
>Help file: Help context: 0
>Error: -2147217900 (80040E14); Provider Error: 7312 (1C90)
>Error string: [OLE/DB provider ret... Process Exit Code 1.
>The step failed.
>Thanks in advance.
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Did anyone figure out this issue. I'm also getting the exact same error and would really appreciate if someone can help me with this.. I have to get this fixed within the next two days..
I've not yet tried this - dbcc traceon(7300, 3604)
I'll give this a try and let you know what happens
Thanks

server to Sybase 11 problem

Attempting to create a Linked Server to a Sybase 11 db. Did the following

- Used DSEDIT to create a data source. Ping(Test) works
- Created a System DSN using the Sybase System 11 driver
- Created a Linked Server with following options
General Tab
- Provider as - MS OLE DB Provider for ODBC Drivers
- Data source as the DSN name created above
- No provider string
- Put in the Catalog name
Security Tab
- No local login
- Selected "be made using this security context"
- Specified remote login and password as the Sybase "sa" and pwd
Server Options Tab
- Checked only Collation compatible and Data Access checkboxes

On trying to see the list of tables, got the following error

Error 7399: OLE DB Provider 'MSDASQL' reported an error.
The provider reported an unexpected catastrophic failure.
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION.SQL Server is termintating this process.

OLE DB error trace[OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x8000ffff: The provider
reported an unexpected catastrophic failure.].

Any solution to this?

HI Mat,

Any response to you query? Even I am facing the same problem. Can you help me out if you have a solution?..

Pls respond to ansathya@.yahoo.com id..

thanks in advance.

Sathya|||We looked into this at is seems like an issue in the sybase provider itself. I'm trying to get a contact there...you folks should try contacting them as well.|||We have got a solution for this from some sources.
Syabase has a known issue on this.

Here are the steps to resolve the problem.

1. In OBDC, while creating the DSN (with the Sybase server name as DSN name) the database name should not be given.

2. In the Linked server properties only the Data source should be given. Remote login (sa and pwd) and in server options only Collation compatible and Data Access are to be selected.

3. The default database for sybase 'sa' login was taken as the user database for which the tables are to be linked.

The above solution works perfectly without issues.

Hope it is helpful to all.
Regards
Geeta

server to ODBC dsn.....

i have a DSN created on the SQL Server machine.
Using my favorite ODBC sql query tool, i can select the System DSN, specify
a login and password, and type a select statement and it all works.
i go into SQL Server, and want to create a linked server to this same ODBC
DSN
Linked server = "BALLYS"
Provider = "Microsoft OLE DB Provider for ODBC Driver"
Data Source="WC400B Bally CMS Testing"
Security-Be made using this security context:
Remote login= [Login Name]
With Password= [Password is blank]
Then i connect to the SQL Server using QA, and try to run my query (that
works in my favorite generic ODBC query tool):
SELECT * FROM OPENQUERY(BALLYS, 'select * from cspcm')
And i get an error message. Now, the error message really shouldn't matter.
My question is, why does it not work? Why didn't Microsoft get ODBC linked
servers right? If any generic stupid 3rd party tool can login and run
queries fine, why can't SQL Server get it right?
But if you care, the error message is:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]General error.]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]Communication link failure. Comm RC=4 - CWB0999 -
Unexpected error: unexpected return code 4]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
Now obviously the DB2 ODBC driver works fine, since i can use it fine with
ADO in my own programs, as well as any other program that knows how to use
ODBC.
So why can SQL Server figure out how to use ODBC?
Have you tried with the latest version of Client access express odbc driver from IBM?
What is the level of service pack on SQL?
"Ian Boyd" wrote:

> i have a DSN created on the SQL Server machine.
> Using my favorite ODBC sql query tool, i can select the System DSN, specify
> a login and password, and type a select statement and it all works.
> i go into SQL Server, and want to create a linked server to this same ODBC
> DSN
> Linked server = "BALLYS"
> Provider = "Microsoft OLE DB Provider for ODBC Driver"
> Data Source="WC400B Bally CMS Testing"
> Security-Be made using this security context:
> Remote login= [Login Name]
> With Password= [Password is blank]
> Then i connect to the SQL Server using QA, and try to run my query (that
> works in my favorite generic ODBC query tool):
> SELECT * FROM OPENQUERY(BALLYS, 'select * from cspcm')
> And i get an error message. Now, the error message really shouldn't matter.
> My question is, why does it not work? Why didn't Microsoft get ODBC linked
> servers right? If any generic stupid 3rd party tool can login and run
> queries fine, why can't SQL Server get it right?
> But if you care, the error message is:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
> (32-bit)][DB2/400 SQL]General error.]
> [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
> SQLSetConnectAttr failed]
> [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
> SQLSetConnectAttr failed]
> [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
> (32-bit)][DB2/400 SQL]Communication link failure. Comm RC=4 - CWB0999 -
> Unexpected error: unexpected return code 4]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
> returned 0x80004005: ].
>
> Now obviously the DB2 ODBC driver works fine, since i can use it fine with
> ADO in my own programs, as well as any other program that knows how to use
> ODBC.
> So why can SQL Server figure out how to use ODBC?
>
>

server to ODBC DSN

i have a DSN created on the SQL Server machine itself. Using my favorite
ODBC sql query tool, i can select the System DSN, specify a login and
password, and type a select statement and it all works. i go into SQL
Server, and want to create a linked server to this same ODBC DSN:
Linked server = "BALLYS"
Provider = "Microsoft OLE DB Provider for ODBC Driver"
Data Source="WC400B Bally CMS Testing"
Security-Be made using this security context:
Remote login= [Login Name]
With Password= [Password is blank]
Then i connect to the SQL Server using QA, and try to run my query (that
works in my favorite generic ODBC query tool):
SELECT * FROM OPENQUERY(BALLYS, 'select * from cspcm')
And i get an error message. Now, the error message really shouldn't matter.
My question is, why does it not work? Why didn't the linked server work? If
any generic 3rd party tool can login and run queries fine, why isn't SQL
Server?
But if you care, the error message is:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]General error.]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]Communication link failure. Comm RC=4 - CWB0999 -
Unexpected error: unexpected return code 4]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
Now obviously the DB2 ODBC driver works fine, since i can use it fine with
ADO in my own programs, as well as any other program that knows how to use
ODBC.
So what is SQL Server doing wrong?
Here are all the settings for my linked server setup:
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Product name: [blank]
Data source: WC400B Bally CMS Testing
Provider string: [blank]
Location: [blank]
Catalog: [blank]
Provider Options:
Dynamic parameters: Unchecked
Nested queries: Unchecked
Level zero only: Unchecked
Allow InProcess: Checked
Non transacted updates: Unchecked
Index as access path: Unchecked
Disallow adhoc accesses: Unchecked
Be made using this security contect:
Remote Login: [The login]
With password: [blank - the password is empty]
Server Options:
Collation compatibile: Unchecked
Data Access: Checked
RPC: Unchecked
RPC Out: Unchecked
Use remote connection: Checked
Collation Name: [blank]
Connection Timeout: 0
Query Timeout: 0
Everything is defaulted, aside from the OLEDB Provider Name, the DSN, the
login, and password.
Which of these defaults are wrong, and are preventing SQL Server from
connecting to a remote ODBC data source?
Error when i try various things:
Collation Compatible o o x o x o x o x o x o x o x o x
RPC o o o x x o o x x o o x x o o x x
RPC Out o o o o o x x x x o o o o x x x x
Use Remote Connection o o o o o o o o o x x x x x x x x
Data Access o x x x x x x x x x x x x x x x x
================================================== ==========================
=
Error 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
1. Error 7411: Server "BALLYS" is not configured for DATA ACCESS
2. Previous Error

server to ODBC DSN

i have a DSN created on the SQL Server machine itself. Using my favorite
ODBC sql query tool, i can select the System DSN, specify a login and
password, and type a select statement and it all works. i go into SQL
Server, and want to create a linked server to this same ODBC DSN:
Linked server = "BALLYS"
Provider = "Microsoft OLE DB Provider for ODBC Driver"
Data Source="WC400B Bally CMS Testing"
Security-Be made using this security context:
Remote login= [Login Name]
With Password= [Password is blank]
Then i connect to the SQL Server using QA, and try to run my query (that
works in my favorite generic ODBC query tool):
SELECT * FROM OPENQUERY(BALLYS, 'select * from cspcm')
And i get an error message. Now, the error message really shouldn't matter.
My question is, why does it not work? Why didn't the linked server work? If
any generic 3rd party tool can login and run queries fine, why isn't SQL
Server?
But if you care, the error message is:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]General error.]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]Communication link failure. Comm RC=4 - CWB0999 -
Unexpected error: unexpected return code 4]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
Now obviously the DB2 ODBC driver works fine, since i can use it fine with
ADO in my own programs, as well as any other program that knows how to use
ODBC.
So what is SQL Server doing wrong?
Here are all the settings for my linked server setup:
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Product name: [blank]
Data source: WC400B Bally CMS Testing
Provider string: [blank]
Location: [blank]
Catalog: [blank]
Provider Options:
Dynamic parameters: Unchecked
Nested queries: Unchecked
Level zero only: Unchecked
Allow InProcess: Checked
Non transacted updates: Unchecked
Index as access path: Unchecked
Disallow adhoc accesses: Unchecked
Be made using this security contect:
Remote Login: [The login]
With password: [blank - the password is empty]
Server Options:
Collation compatibile: Unchecked
Data Access: Checked
RPC: Unchecked
RPC Out: Unchecked
Use remote connection: Checked
Collation Name: [blank]
Connection Timeout: 0
Query Timeout: 0
Everything is defaulted, aside from the OLEDB Provider Name, the DSN, the
login, and password.
Which of these defaults are wrong, and are preventing SQL Server from
connecting to a remote ODBC data source?Error when i try various things:
Collation Compatible o o x o x o x o x o x o x o x o x
RPC o o o x x o o x x o o x x o o x x
RPC Out o o o o o x x x x o o o o x x x x
Use Remote Connection o o o o o o o o o x x x x x x x x
Data Access o x x x x x x x x x x x x x x x x
=============================================================================Error 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
1. Error 7411: Server "BALLYS" is not configured for DATA ACCESS
2. Previous Error

server to ODBC DSN

i have a DSN created on the SQL Server machine itself. Using my favorite
ODBC sql query tool, i can select the System DSN, specify a login and
password, and type a select statement and it all works. i go into SQL
Server, and want to create a linked server to this same ODBC DSN:
Linked server = "BALLYS"
Provider = "Microsoft OLE DB Provider for ODBC Driver"
Data Source="WC400B Bally CMS Testing"
Security-Be made using this security context:
Remote login= [Login Name]
With Password= [Password is blank]
Then i connect to the SQL Server using QA, and try to run my query (that
works in my favorite generic ODBC query tool):
SELECT * FROM OPENQUERY(BALLYS, 'select * from cspcm')
And i get an error message. Now, the error message really shouldn't matter.
My question is, why does it not work? Why didn't the linked server work? If
any generic 3rd party tool can login and run queries fine, why isn't SQL
Server?
But if you care, the error message is:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express O
DBC Driver
(32-bit)][DB2/400 SQL]General error.]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [IBM][Client Access Express O
DBC Driver
(32-bit)][DB2/400 SQL]Communication link failure. Comm RC=4 - CWB0999 -
Unexpected error: unexpected return code 4]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
Now obviously the DB2 ODBC driver works fine, since i can use it fine with
ADO in my own programs, as well as any other program that knows how to use
ODBC.
So what is SQL Server doing wrong?
Here are all the settings for my linked server setup:
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Product name: [blank]
Data source: WC400B Bally CMS Testing
Provider string: [blank]
Location: [blank]
Catalog: [blank]
Provider Options:
Dynamic parameters: Unchecked
Nested queries: Unchecked
Level zero only: Unchecked
Allow InProcess: Checked
Non transacted updates: Unchecked
Index as access path: Unchecked
Disallow adhoc accesses: Unchecked
Be made using this security contect:
Remote Login: [The login]
With password: [blank - the password is empty]
Server Options:
Collation compatibile: Unchecked
Data Access: Checked
RPC: Unchecked
RPC Out: Unchecked
Use remote connection: Checked
Collation Name: [blank]
Connection Timeout: 0
Query Timeout: 0
Everything is defaulted, aside from the OLEDB Provider Name, the DSN, the
login, and password.
Which of these defaults are wrong, and are preventing SQL Server from
connecting to a remote ODBC data source?Error when i try various things:
Collation Compatible o o x o x o x o x o x o x o x o x
RPC o o o x x o o x x o o x x o o x x
RPC Out o o o o o x x x x o o o o x x x x
Use Remote Connection o o o o o o o o o x x x x x x x x
Data Access o x x x x x x x x x x x x x x x x
========================================
====================================
=
Error 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
1. Error 7411: Server "BALLYS" is not configured for DATA ACCESS
2. Previous Error