Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
jsomsen
Contributor III

Script problems converting from SQL2005 to SQL2008

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!

14 Replies
Not applicable

Script problems converting from SQL2005 to SQL2008

Have you tried using OLEDB instead of ODBC?

Best regards

Stefan

jsomsen
Contributor III

Script problems converting from SQL2005 to SQL2008

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!

Not applicable

Script problems converting from SQL2005 to SQL2008

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

jsomsen
Contributor III

Script problems converting from SQL2005 to SQL2008

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.

Not applicable

Script problems converting from SQL2005 to SQL2008

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

jsomsen
Contributor III

Script problems converting from SQL2005 to SQL2008

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?

Not applicable

Script problems converting from SQL2005 to SQL2008

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

Not applicable

Script problems converting from SQL2005 to SQL2008

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

Not applicable

Script problems converting from SQL2005 to SQL2008

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