Solved

MS SQL linked server - error 7399 for MSM-SQL/ODBC 32 bit Driver

Posted on 2004-08-26
8
1,269 Views
Last Modified: 2012-08-13
hi folks,


i have received a driver to connect with a legacy database.
after i have installed the driver on the server box, i managed to import some tables using data transformation services (DTS).  but when it comes to create a connection to the server linked server then it is a different result, it simply does not work.

as i am not an expert in MS SQL 2k, i am asking you for help!

on DTS (on Enterprise Manager), i do the following:

click on data transformation services -> right click on local packages -> choose 'new package'

on the new windows, i do the following:
i choose other connections ->
a pop-up appears asking for connection details and i enter the following details:


new connection: newConnectionName
data source: MSM-SQL/ODBC 32 bit Driver
user/system dsn: MSM-SQL 32 bit
username:myUsername
password: myPassword

but when i try to use the details above in the 'linked server' section i receive an error that says:

error 7399: OLE DB provider 'MSDASQL' reported an error.  Data source name not found and no default driver specified.

could i please ask for assistance in sorting this issue?


many thanks in advance for your input!


nicolas



0
Comment
Question by:nicolasdiogo
  • 3
  • 3
8 Comments
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11904047
you should use the first select from the connection menu
called

"Microsoft OLEDB  Provider for SQL Server"
for an sql server connection

as for the problem with the OTHER connection
the " user/system dsn: MSM-SQL 32 bit "
needs to be the NAME assigned to the ODBC configuration
created in
WINDOWS ODBC DATA SOURCES ADMINISTRATOR

which on windows XP is accessed from
start -> settings-> control panel-> Administrative tools -> Data Sources (ODBC)

Jay
0
 
LVL 5

Author Comment

by:nicolasdiogo
ID: 11904180
hi jltoops,

the system i am tring to connect TO is NOT a MS SQL database, it is a legacy system for which i have received the MSM-SQL/ODBC 32 bit Driver to connecto to it.

the user/system dsn: MSM-SQL 32 bit is an assigned name on the ODBC on the server.

what could i do to link this legacy system to MS SQL?

nicolas
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11904241
OK ...

a) make sure that ODBC driver is installed on your computer
b) go to
WINDOWS ODBC DATA SOURCES ADMINISTRATOR
which on windows XP is accessed from
start -> settings-> control panel-> Administrative tools -> Data Sources (ODBC)

select system DSN
then click Add new select your DRIVER that was installed.
Give this Connection a "NAME"
You will have to specifiy the name of the server
the database your connecting to etc.

in DTS
new connection: newConnectionName
data source: MSM-SQL/ODBC 32 bit Driver
user/system dsn: "NAME" (FROM THE ODBC YOU JUST CREATED"
username:myUsername
password: myPassword

JAY
ps. please do NOT use quotes in the name

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 5

Author Comment

by:nicolasdiogo
ID: 11904356
thanks again,


the dsn i created is called -- MSM-SQL 32 bit -- and is based on the MSM-SQL/ODBC 32 bit Driver.

however, i am looking to create a 'linked server' and not a dts.  i already have a dts that connects through this driver but i need to have a linked server instead.

could you help me with that one?
0
 
LVL 10

Accepted Solution

by:
Jay Toops earned 500 total points
ID: 11905321
go to enterprise manager
go to security
go to linked servers
add new
SELECT THE GENERAL TAB

select  Microsoft OLEDB PROVIDER FOR ODBC DRIVERS

Microsoft OLE DB Provider for ODBC
example for you
NAME  = 'linkeddbname' (your name here)
PRODUCT = 'Sybase',      (your name here)
PROVIDER =  'MSDASQL'  (FOR THE Microsoft OLEDB PROVIDER FOR ODBC DRIVERS)
DATASOURCE  = 'MSM-SQL 32 bit'

NEXT
go to the SECURITY TAB AND DEFINE A USERID AND password

then you would select from it like so ..

SELECT * FROM SybaseLinkedServerName.DatabaseName.Owner.TableName
go
0
 
LVL 5

Author Comment

by:nicolasdiogo
ID: 11911001
thanks jltoops for your assistance,


i have sorted the problem on the server.

it is all about setting up a SYSTEM dsn on the box, and calling that dsn from the provider string.

once again thanks for your input but i will now close this question.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now