Linked server to ODBC datasource

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Linked server to ODBC datasource

Author

Topic

GeorgeDuckett
Starting Member

1 Post

Posted - 2010-12-08 : 08:00:10

Hi all, i'm trying to create a linked server, pointing to an ODBC data source.

Below is the scripted linked server:

/****** Object:  LinkedServer [CC7]    Script Date: 12/08/2010 12:46:40 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'CC7', @srvproduct=N'MSDASQL', @provider=N'MSDASQL', @datasrc=N'CC7', @provstr=N'DRIVER={Intersystems ODBC};Server=CCMSSRVR;Port=1972;Database=CCMS_STAT'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CC7',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'use remote collation', @optvalue=N'true'

I'm trying to run a simple query such as below:

SELECT * FROM CC7..dbo.iAgentByApplicationStat

On Server 2005 (version 9.0.3042) it fails with the following error:

Msg 7342, Level 16, State 1, Line 1
An unexpected NULL value was returned for column "[CC7]..[dbo].[iAgentByApplicationStat].AgentSurName" from OLE DB provider "MSDASQL" for linked server "CC7". This column cannot be NULL.

Interestingly, i can run the query when the linked server is added to an SQL server 2005 express instance (version 9.0.4053) i don't get the error, but if i try to do the following query:

SELECT * FROM CC7..dbo.iAgentByApplicationStat WHERE AgentSurName IS NOT NULL

it doesn't filter out the NULL values, whereas the code

SELECT * FROM CC7..dbo.iAgentByApplicationStat WHERE LEN(AgentSurName) > 0

does!!

Any help would be very usefull as i'm completely stuck on this one.

Ravish
Starting Member

4 Posts

Posted - 2010-12-13 : 01:13:27

Ravish
Starting Member

4 Posts

Posted - 2010-12-13 : 01:15:04

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources