topic Script problems converting from SQL2005 to SQL2008 in QlikView

Script problems converting from SQL2005 to SQL2008

Anonymous — Wed, 14 Oct 2009 04:01:12 GMT

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!

Script problems converting from SQL2005 to SQL2008

Wed, 14 Oct 2009 04:34:03 GMT

Have you tried using OLEDB instead of ODBC?

Best regards

Stefan

Script problems converting from SQL2005 to SQL2008

Anonymous — Wed, 14 Oct 2009 19:30:05 GMT

Yes. We have tried OLE DB and can not seem to get it to work with the old SQL server. We tried on the new server, but we must not have the proper settings as we get the following erro: "OLEDB read failed" when we try to read a table in QlikView. The connection tests alright, but QV will not read the table.

I am not an expert on ODBC and OLE DB but this Microsoft statement "Microsoft SQL Server 2008 Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver." that both ODBC and OLE DB is in the same driver. Am I correct in my thinking?

Thanks for you suggestion!

Script problems converting from SQL2005 to SQL2008

Wed, 14 Oct 2009 20:35:22 GMT

if your suspecting a driver issue have you tried reading the 2008 warehouse with the 2005 drivers which may have more compatability with both versions

Script problems converting from SQL2005 to SQL2008

Anonymous — Wed, 14 Oct 2009 20:44:09 GMT

I should note that the new SQL2008 server is 64-bit whereas the old is 32 bit and a SQL2000 server not SQL2005 as noted in the title. Too many server versions to keep track of. I don't believe that I can use the old driver on a 64 bit server, correct?

Thanks again for everyones help.

Script problems converting from SQL2005 to SQL2008

Wed, 14 Oct 2009 23:26:43 GMT

Im assuming your QV is on a seperate server to the SQL server Database (You dont want both competing for memory)

This means you can install the sql client tools on the QV server, its this driver that is doing the data access as a client

Colin

Script problems converting from SQL2005 to SQL2008

Anonymous — Wed, 14 Oct 2009 23:42:11 GMT

Correct. We have QV and SQL on different Servers. We have installed the SQL Native Client 10.0, is this the same thing as SQL Client Tools?

Script problems converting from SQL2005 to SQL2008

Thu, 15 Oct 2009 00:06:49 GMT

its essentially the same the client tools upgrades the sql client.

I believe it always maintains the highest version so probably wont go down to the 2005 version without an uninstall of version 10, and it will also be affected by service packs etc

Im suggesting you may get better compatability of both sql2000 and sql2008 from an interim driver the sql2005 version !

its worth a try if it removes the requirement to edit Load scripts

Script problems converting from SQL2005 to SQL2008

Mon, 02 Nov 2009 23:52:52 GMT

Hi All,

I had the same issue until now !

I just succeed connecting to MS SQL 2008 (x64) using the 2005 provider SQLOLEDB.1...

Below is the connection string:

CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=MyDataBase;Data Source=MyInstance;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ServerName;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is MyPasswordEncrypted);

Regards

Ylan

Script problems converting from SQL2005 to SQL2008

Thu, 05 Nov 2009 13:54:34 GMT

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

Script problems converting from SQL2005 to SQL2008

Thu, 05 Nov 2009 13:55:23 GMT

Hi,

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

Greetz
Robbie

Script problems converting from SQL2005 to SQL2008

Thu, 26 Nov 2009 22:53:12 GMT

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

Script problems converting from SQL2005 to SQL2008

Mon, 07 Dec 2009 18:28:37 GMT

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

Script problems converting from SQL2005 to SQL2008

Mon, 27 Feb 2012 17:34:15 GMT

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?

Script problems converting from SQL2005 to SQL2008

Tue, 28 Feb 2012 19:25:54 GMT

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