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)
SQL SELECT *
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:
SQL SELECT ClaimDetailId,
CAST(TosNo as bigint) as TosNo,
CAST(MultiValue as bigint) as MultiValue,
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!
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!
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.
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
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
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);
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?