Leeland thanks a lot for your help... there is some progress and I think I am very close in solving this issue with your help...

I created a SYSTEM DSN "TEST_ODBC" using the SQL Server Drive 6.01.7601... in C:\Windows\System32\odbcad.32.exe

I created the Linked Server using the Script below:

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver @server = N'TestLinkedServer', @srvproduct=N'TEST_ODBC', @provider=N'MSDASQL',

@datasrc=N'TEST_ODBC',

@provstr=N'DNS=TEST_ODBC;UID=MYUSERNAME;PWD=MYPASSWORD'

GO

EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'use remote collation', @optvalue=N'true'

GO

USE [master]

GO

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname = N'TestLinkedServer',

@locallogin = N'MyDOMAIN\USERNAME', ---2012 Box account

@useself = N'False',

@rmtuser = N'MYUSERNAME', --remote server login

@rmtpassword = N'MYPASSWORD' --remote server pwd

GO

When I tested the connection of the linked server "TestLinkedServer" it is working fine and I can see the catalogs...

However, when I run this select below:

Select * from [TestLinkedServer].MYDB.dbo.TBLNAME

I get this error now:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "TestLinkedServer" reported an error. The provider did not give any information about the error.

Msg 7312, Level 16, State 1, Line 1

Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "TestLinkedServer". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

HOWEVER when I tried running this instead:

Select * from [TestLinkedServer]...TBLNAME

I CAN SEE THE DATA... seems like configuration issue????

Thanks for all your help Leeland...

Laura