Linked server for ODBC data source

Hope someone can help with this:
I have an Alpha box running open VMS which has an ODBC client from easysoft
set up as a system dsn in ODBC data sources. I can select the data source
from Excel/Access/VB asp etc with a connection string of "dsn=dsnname"
How can I set this up as a linked server in SQL 2k ?
I have tried using enterprise manager & creating a linked server for
"Microsoft OLE DB Provider for ODBC Drivers" with the data source as
"dsnname" and the provider string as "dsn=dsnname". Is the the correct way
to use a system dsn as a linked server? If so, are there any other
parameters to set up as I can't see any tables, and get an error : 7399,
OLEDB provider 'MSDASQL' reported an error, provider did not give any
information...IDBinitialize returned 0x80004005
Is this a login or security problem? I just want the login to use my current
credentials.
Did you install the ODBC client on the SQL Server box?
You can use a DSN but you need to create the DSN on the
server. You don't need to use a DSN though - you can also
provide the information used by the DSN in the provider
string and data source parameters of sp_addlinkedserver.
In terms of the 7399 error, that's somewhat of a generic
error. Try turning on trace flag 7300 or use SQL Profiler
to capture the OLEDB Errors event. This should give you to
get more information about the error.
-Sue
On Mon, 7 Jun 2004 12:12:03 +0100, "CCA Dave"
<david@.notreally.co.uk> wrote:

>Hope someone can help with this:
>I have an Alpha box running open VMS which has an ODBC client from easysoft
>set up as a system dsn in ODBC data sources. I can select the data source
>from Excel/Access/VB asp etc with a connection string of "dsn=dsnname"
>How can I set this up as a linked server in SQL 2k ?
>I have tried using enterprise manager & creating a linked server for
>"Microsoft OLE DB Provider for ODBC Drivers" with the data source as
>"dsnname" and the provider string as "dsn=dsnname". Is the the correct way
>to use a system dsn as a linked server? If so, are there any other
>parameters to set up as I can't see any tables, and get an error : 7399,
>OLEDB provider 'MSDASQL' reported an error, provider did not give any
>information...IDBinitialize returned 0x80004005
>Is this a login or security problem? I just want the login to use my current
>credentials.
>

Linked server for ODBC data source

Hope someone can help with this:
I have an Alpha box running open VMS which has an ODBC client from easysoft
set up as a system dsn in ODBC data sources. I can select the data source
from Excel/Access/VB asp etc with a connection string of "dsn=dsnname"
How can I set this up as a linked server in SQL 2k ?
I have tried using enterprise manager & creating a linked server for
"Microsoft OLE DB Provider for ODBC Drivers" with the data source as
"dsnname" and the provider string as "dsn=dsnname". Is the the correct way
to use a system dsn as a linked server? If so, are there any other
parameters to set up as I can't see any tables, and get an error : 7399,
OLEDB provider 'MSDASQL' reported an error, provider did not give any
information...IDBinitialize returned 0x80004005
Is this a login or security problem? I just want the login to use my current
credentials.Did you install the ODBC client on the SQL Server box?
You can use a DSN but you need to create the DSN on the
server. You don't need to use a DSN though - you can also
provide the information used by the DSN in the provider
string and data source parameters of sp_addlinkedserver.
In terms of the 7399 error, that's somewhat of a generic
error. Try turning on trace flag 7300 or use SQL Profiler
to capture the OLEDB Errors event. This should give you to
get more information about the error.
-Sue
On Mon, 7 Jun 2004 12:12:03 +0100, "CCA Dave"
<david@.notreally.co.uk> wrote:

>Hope someone can help with this:
>I have an Alpha box running open VMS which has an ODBC client from easysoft
>set up as a system dsn in ODBC data sources. I can select the data source
>from Excel/Access/VB asp etc with a connection string of "dsn=dsnname"
>How can I set this up as a linked server in SQL 2k ?
>I have tried using enterprise manager & creating a linked server for
>"Microsoft OLE DB Provider for ODBC Drivers" with the data source as
>"dsnname" and the provider string as "dsn=dsnname". Is the the correct way
>to use a system dsn as a linked server? If so, are there any other
>parameters to set up as I can't see any tables, and get an error : 7399,
>OLEDB provider 'MSDASQL' reported an error, provider did not give any
>information...IDBinitialize returned 0x80004005
>Is this a login or security problem? I just want the login to use my curren
t
>credentials.
>sql

Linked Server Error Oracle Database

I'm in the process of upgrading the operating system from
Windows 2000 Advanced Server to Windows 2003 Server.
The server is clustered one is Windows 2000 Advanced
Server and the other Windows 2003 Server.
Before I upgraded the operated system both Linked Server
worked fine.
This is a linked server to an Oracle database. I was able
to connect to the database connection from sqlplus on this
server.
The linked server error is : Error 7399 "OLE DB
provider 'MSDAORA' reported an error.
Plese help me resolve this issue.
Thanks,
Jan K.
MS DTC must be configured as a cluster resource if you are using linked
servers. Please refer to this link for more info.
http://support.microsoft.com/default...&Product=sql2k
As it was working before the upgrade it is likely you have configured MS DTC
correctly.
The 7399 is a generic error message. You might want to set this trace flag
on and rerun your query to get a more meaningful error message
DBCC TRACEON (7300, 3604)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jan K." <anonymous@.discussions.microsoft.com> wrote in message
news:00a601c4a4c1$249d2ba0$a601280a@.phx.gbl...
> I'm in the process of upgrading the operating system from
> Windows 2000 Advanced Server to Windows 2003 Server.
> The server is clustered one is Windows 2000 Advanced
> Server and the other Windows 2003 Server.
> Before I upgraded the operated system both Linked Server
> worked fine.
> This is a linked server to an Oracle database. I was able
> to connect to the database connection from sqlplus on this
> server.
> The linked server error is : Error 7399 "OLE DB
> provider 'MSDAORA' reported an error.
> Plese help me resolve this issue.
> Thanks,
> Jan K.
|||We are having the same error, we are on Win 2000. What was your resolution?
"Hilary Cotter" wrote:

> MS DTC must be configured as a cluster resource if you are using linked
> servers. Please refer to this link for more info.
> http://support.microsoft.com/default...&Product=sql2k
> As it was working before the upgrade it is likely you have configured MS DTC
> correctly.
> The 7399 is a generic error message. You might want to set this trace flag
> on and rerun your query to get a more meaningful error message
> DBCC TRACEON (7300, 3604)
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jan K." <anonymous@.discussions.microsoft.com> wrote in message
> news:00a601c4a4c1$249d2ba0$a601280a@.phx.gbl...
>
>
|||You must set the change permission fot Everyone on the folder C:\program
file\oracle and c:\oracle.
Claudio Riefolo
"Mark" <Mark@.discussions.microsoft.com> ha scritto nel messaggio
news:F7D5E575-9EB4-4DFB-AA44-8907FCE6CFE4@.microsoft.com...[vbcol=seagreen]
> We are having the same error, we are on Win 2000. What was your
> resolution?
> "Hilary Cotter" wrote:

Linked server connection

I use a system with one main server (SQL 2005) and three other remote linked
servers. I need to know if one of remote server is running or not to avoid m
y
procedure fails. My procedure contains remote query and distributred
transaction. If one of the remote server is not running and i know, i can
avoid failure of my procedure.
How can i know remote server state (is running or not)?
ThanksYou could do a simple SELECT on a table on linked server. See if you get a
response
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:A363D6C0-C800-4516-8182-74AE61278D04@.microsoft.com...
> I use a system with one main server (SQL 2005) and three other remote
linked
> servers. I need to know if one of remote server is running or not to avoid
my
> procedure fails. My procedure contains remote query and distributred
> transaction. If one of the remote server is not running and i know, i can
> avoid failure of my procedure.
> How can i know remote server state (is running or not)?
> Thanks|||If i run a SELECT i've got this error:
...
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
I don't want my procedure fails with error if one server is not running.
How can i catch this error or knowing if my server is running or not?
Thanks
"Jack Vamvas" wrote:

> You could do a simple SELECT on a table on linked server. See if you get a
> response
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "checcouno" <checcouno@.discussions.microsoft.com> wrote in message
> news:A363D6C0-C800-4516-8182-74AE61278D04@.microsoft.com...
> linked
> my
>
>|||Trap the exception in a TRY...CATCH block.
You could also design a CLR procedure to check for servers via SMO.
ML
http://milambda.blogspot.com/|||I need to do this all in my SQL stored procedure, not from a C# class or cod
e.
I've got a sp that run query on linked server, begin a distributed
transaction and inf ok commit. If one of the linked server is not running th
e
SP fails. I need to control this and to need to know if the server runs
before run the query and the transaction, but inside my SP.
Tanks.
"ML" wrote:

> Trap the exception in a TRY...CATCH block.
> You could also design a CLR procedure to check for servers via SMO.
>
> ML
> --
> http://milambda.blogspot.com/

Linked Server (Secure Access Database) Problem

hi all;
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
Ansari
Could you please run the following-
dbcc traceon(7300,3604)
and then execute a query. You'll get a more detailed error message.Please
paste it here for us to review.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||hi Vikram
Thanks for your suggestion. I am sorry to say that I have written down some
incorrect information. I was using Microsoft.JET.OLEDB provider instead
Microsoft OLEDB Provider for ODBC Drivers. I have created the linked server
in following way
EXEC sp_addlinkedserver
@.server = 'CostingServer',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'ACCESS',
@.datasrc = 'C:\COSTING2.mdb'
go
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, 'ansari'
go
dbcc traceon(7300,3604)
go
select * from costingServer...materialgroup
here is the error message after runing dbcc traceon(7300,3604)
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Not a valid account name or password.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
and if I unset the database password and run the
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, null
it work fine..
And in last the error i have posted i.e
"Invalid schema or catalog specified for provider 'MSDASQL'."
is due to not supplying catalog name. if i change my query
SELECT * FROM COSTINGSERVER...MaterialGroup
to
SELECT * FROM COSTINGSERVER.[c:\costing]..MaterialGroup
it also work fines
ansari
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:m$2w#s$YEHA.3316@.cpmsftngxa06.phx.gbl...
> Could you please run the following-
> dbcc traceon(7300,3604)
> and then execute a query. You'll get a more detailed error message.Please
> paste it here for us to review.
> Cheers,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Linked Server (Secure Access Database) Problem

hi all;
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
Ansari"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:uOnO$LPWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I m facing problem in setting linked server to Secure Access Database.. I
> have created a system DSN name "Costing" and set the user name and
password
> also. Then I created linked server using enterprise manager with
following
> property values
> Linked Server = CostingServer
> Provide name = Micorsoft OLEDB Provider for ODB Drivers
> Data Source = Costing
> when I press ok and click on "tables" icon it shows all the tables of
secure
> database... it means that connection successfull..
> but when I try to access tables using following query
> SELECT * FROM COSTINGSERVER...MaterialGroup
> it gives the following error
> "Invalid schema or catalog specified for provider 'MSDASQL'."
> but if remove password from the database all work is going fine without
any
> error...
> Any suggestion, help would be higly appreiciated in this regard..
Yes, don't bother with an Access database password. They are not only
insecure, a quick internet search will turn up a few mdb password crackers.
If you need your data secure, migrate it to SQL Server and use appropriate
security.
Steve

Linked Server (Secure Access Database) Problem

hi all;
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
AnsariCould you please run the following-
dbcc traceon(7300,3604)
and then execute a query. You'll get a more detailed error message.Please
paste it here for us to review.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||hi Vikram
Thanks for your suggestion. I am sorry to say that I have written down some
incorrect information. I was using Microsoft.JET.OLEDB provider instead
Microsoft OLEDB Provider for ODBC Drivers. I have created the linked server
in following way
EXEC sp_addlinkedserver
@.server = 'CostingServer',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'ACCESS',
@.datasrc = 'C:\COSTING2.mdb'
go
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, 'ansari'
go
dbcc traceon(7300,3604)
go
select * from costingServer...materialgroup
here is the error message after runing dbcc traceon(7300,3604)
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Not a valid account name or password.
]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
and if I unset the database password and run the
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, null
it work fine..
And in last the error i have posted i.e
"Invalid schema or catalog specified for provider 'MSDASQL'."
is due to not supplying catalog name. if i change my query
SELECT * FROM COSTINGSERVER...MaterialGroup
to
SELECT * FROM COSTINGSERVER.[c:\costing]..MaterialGroup
it also work fines
ansari
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:m$2w#s$YEHA.3316@.cpmsftngxa06.phx.gbl...
> Could you please run the following-
> dbcc traceon(7300,3604)
> and then execute a query. You'll get a more detailed error message.Please
> paste it here for us to review.
> Cheers,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Linked Server (Secure Access Database) Problem

hi all;
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
AnsariCould you please run the following-
dbcc traceon(7300,3604)
and then execute a query. You'll get a more detailed error message.Please
paste it here for us to review.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||hi Vikram
Thanks for your suggestion. I am sorry to say that I have written down some
incorrect information. I was using Microsoft.JET.OLEDB provider instead
Microsoft OLEDB Provider for ODBC Drivers. I have created the linked server
in following way
EXEC sp_addlinkedserver
@.server = 'CostingServer',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'ACCESS',
@.datasrc = 'C:\COSTING2.mdb'
go
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, 'ansari'
go
dbcc traceon(7300,3604)
go
select * from costingServer...materialgroup
here is the error message after runing dbcc traceon(7300,3604)
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Not a valid account name or password.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
and if I unset the database password and run the
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, null
it work fine..
And in last the error i have posted i.e
"Invalid schema or catalog specified for provider 'MSDASQL'."
is due to not supplying catalog name. if i change my query
SELECT * FROM COSTINGSERVER...MaterialGroup
to
SELECT * FROM COSTINGSERVER.[c:\costing]..MaterialGroup
it also work fines
ansari
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:m$2w#s$YEHA.3316@.cpmsftngxa06.phx.gbl...
> Could you please run the following-
> dbcc traceon(7300,3604)
> and then execute a query. You'll get a more detailed error message.Please
> paste it here for us to review.
> Cheers,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>