Script problems converting from SQL2005 to SQL2008

[Anonymous]

BLUF (Bottom Line Up Front): We are converting our Data Warehouse from SQL 2000 over to SQL 2008. We are having problems converting our QVD load scripts over (see example below). We are being required to use the SQL CAST function to read the data from the new 2008 SQL server. We would like to avoid this as we have our QVD scripts programmed with variables so that we could make the switch to the new DW fairly easily. Do you have any ideas why the SQL Server Native Client 10.0 would not be working properly with QlikView?

Here is the load script that we currently use for our qvd build from our old SQL data warehouse (SQL 2000)

sql2:

LOAD ClaimDetailId,

TosNo,

MultiValue,

PlanType,

CptId,

IcesStatus,

IcesErrorCode,

IcesDescription,

IcesBatchId;

SQL SELECT *

FROM "dw_data".HealthTraq."tosdet_ices";

When we point the same reload to our new data ware house, we get the following error.

SQL Error: [Microsoft][SQL Server Native Client 10.0]Numeric value out of range

SQL STATE: 22003

By adding a couple of SQL CAST statements to the script we can get the reload to work on the new 2008 SQL Database. Here is the working script:

SQL3:

LOAD ClaimDetailId,

TosNo,

MultiValue,

PlanType;

SQL SELECT ClaimDetailId,

CAST(TosNo as bigint) as TosNo,

CAST(MultiValue as bigint) as MultiValue,

PlanType

FROM "dw_data_test".dbo."tosdet_ices";

We would like to avoid having to go through all the QVD's and put in the CAST values into the old qvd's. It looks like mostly the INT (both SmallInt and BIGINT), Numeric and the MONEY Data Types are causing us problems.

This is the Table layout for the "tosdet_ices" table:

ClaimDetailId varchar(15) Unchecked

TosNo smallint Unchecked

MultiValue smallint Unchecked

PlanType varchar(15) Checked

Here is the information of our 2008 SQL Server:

Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )

Microsoft Analysis Services Client Tools 2007.0100.1600.022 ((SQL_PreRelease).080709-1414 )

Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)

Microsoft MSXML 2.6 3.0 6.0

Microsoft Internet Explorer 7.0.5730.13

Microsoft .NET Framework 2.0.50727.3053

Operating System 5.2.3790

Here is the ODBC information on the QlikView Server:

SQL2000 ODBC Driver Microsoft SQL Server ODBC Driver Version 03.86.3959

SQL2008 ODBC DRIVER Microsoft SQL Server Native Client Version 10.00.1600

Thanks for all your help in advance!

[#2]

Hi,

We are also upgrading a SQL server 2000 version to version 2008. With the post of Ylan I assume that the connection can be made with the 2005 provider SQLOLEDB.1. Does this OLEDB connection support 32 and 64 bits? And is this driver already installed or do we have to download?

@ Ylan: Do you also have QV en SQL Server installed on separated boxes? Both 64x? And version QV 9 or 8?

Greetz
Robbie

[#3]

Hi Robbie

I have tested oledb connection to SQL 2008 only on 64bits. I used this driver --> http://download.microsoft.com/download/3/1/6/316FADB2-E703-4351-8E9C-E0B36D9D697E/sqlncli_x64.msi.

I'm pretty sure you can connect to SQL 2008 on 32bits using that one --> http://download.microsoft.com/download/3/1/6/316FADB2-E703-4351-8E9C-E0B36D9D697E/sqlncli.msi

Let us know 😉

Regards

Ylan

[#4]

Thanks Ylan! I'll try the suggested drivers. 😉

[#5]

We just updated a SQL server from 2005 to 2008.   I am having the same issues except the 'Numeric value out of range' is only in my test environment and my desktop. My production environment has the same set up as far as drivers and ODBC's yet, I cant conntect to the new SQL server from neither my test server nor my desktop.  Although, my production environment connects and loads just fine.  From my desktop, I am able to CONNECT,  SELECT a table and bring in all of the fields.  It once I reload that I get an error.  the only things that appears different is where the load originates.  Does anyone have any ideas of why I might be getting these errors?

[#6]

Just wanted to update what solution worked for my issue upgrading a server from SQL2005 to SQL 2008.  I had installed the SQL Native Client 10 but the old version was not working.  It wasnt until I updated to the latest release, R2, did everything start working.   So....

These are both SQL Native Client 10

10.0.1600.22 Did not work for me

R2:

10.50.1600.1 Works