August 9, 2007 at 5:41 am
Hi,
I have a database on a 2000 server which is storing numeric data using the float datatype, all numeric values in the table are negative.
I have another database on a 2005 server, from here I query the 2000 box (via a standard linked server using MS oledb drivers), when the results are displayed however all returned values are positive!
I don't know why querying via a linked server is resulting in the signage of the data being dropped, can anyone help me out with this please?
Thanks,
Ian
August 10, 2007 at 6:58 am
Hi Ian,
It sounds like some sort of data type converstion might be going on that screws up the values. Try running the query using SELECT ... INTO to create a table containing the returned data (wouldn't recommend using SELECT ... INTO in general though since it can cause performance bottlenecks) and then check the data type of the offending column. This will tell you if a conversion is happening, although not why.
Can you post up your query as well please? It might give us something more to go on.
Thanks,
Bart
August 13, 2007 at 2:39 am
Hi Bart,
Thanks for looking at this. As suggested I ran a "select into" to a # table, the datatype assigned to the columns in the resulting table is numeric 26,3. This is the same as the datataype in the source table, no there seems to be no datatype conversion, though all values which were negative in the source were positive in the # table.
Whilst looking into this problem I also created the following table on the source server:
CREATE TABLE [dbo].[test](
[n1] [int],
[n2] [float] ,
[n3] [money] ,
[n4] [numeric](26, 3) )
And populated it with the one row having a value of -1 in each column.
Oddly when I query this table from the linked server all of the values returned are shown as negative. So this problem seems to be a combination of the source table and the linked server. The table definition of the problematic source table is as below, the one of the columns showing this problem is posted_base_amt:
CREATE TABLE [dbo].[ps_data](
[BUSINESS_UNIT] [varchar](15) NULL,
[LEDGER] [varchar](30) NULL,
[ACCOUNT] [varchar](30) NULL,
[ALTACCT] [varchar](30) NULL,
[OPERATING_UNIT] [varchar](24) NULL,
[DEPTID] [varchar](30) NULL,
[PRODUCT] [varchar](18) NULL,
[FT_YEAR] [varchar](12) NULL,
[FT_MCC] [varchar](21) NULL,
[FT_GEOCODE] [varchar](15) NULL,
[FT_FUNCTION] [varchar](6) NULL,
[PROJECT_ID] [varchar](45) NULL,
[AFFILIATE] [varchar](15) NULL,
[CURRENCY_CD] [varchar](9) NULL,
[STATISTICS_CODE] [varchar](9) NULL,
[FISCAL_YEAR] [float] NULL,
[ACCOUNTING_PERIOD] [float] NULL,
[POSTED_TOTAL_AMT] [numeric](26, 3) NULL,
[POSTED_BASE_AMT] [numeric](26, 3) NULL,
[POSTED_TRAN_AMT] [numeric](26, 3) NULL,
[BASE_CURRENCY] [varchar](9) NULL,
[DTTM_STAMP_SEC] [datetime] NULL,
[PROCESS_INSTANCE] [numeric](10, 0) NULL
) ON [PRIMARY]
The linked server I'm using is scripted below:
EXEC master.dbo.sp_addlinkedserver @server = N'UK01DFIN001', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'UK01DFIN001',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'UK01DFIN001', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'UK01DFIN001', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'UK01DFIN001', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'UK01DFIN001', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'UK01DFIN001', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'UK01DFIN001', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'UK01DFIN001', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'UK01DFIN001', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'UK01DFIN001', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'UK01DFIN001', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'UK01DFIN001', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'UK01DFIN001', @optname=N'use remote collation', @optvalue=N'true'
Any help with this would be greatly appreciated as I don't have a clue as to what's going on here.
Thanks,
Iain
August 13, 2007 at 3:30 am
Forgot to incude this, its the query I'm using to bring back the data from the linked server:
select
*
from uk01dfin001.spin_up_testing.dbo.ps_data
where posted_base_amt < 0
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply