OLEDB connection to SQL Server - 0 lines fetched

[#1]

Hi all

We have Microsoft Dynamics CRM and we report on the data using QlikView, which involves creating a .qvd layer of the data stored in the CRM SQL Server.

Recently, we have encountered problems creating a .qvd file from dbo.FilteredOpportunity, a view that has 588 fields and nearly 13,000 records.  Every so often, the .qvd created from this view is empty ie. 0 lines fetched.  I have attached a portion of the log file that illustrates this.

To provide some background, if you SELECT * FROM dbo.FilteredOpportunity in SSMS then the query takes around 4 mins to complete.

Is there a timeout occurring on the SQL query?

Is there a maximum number of fields allowed in a .qvd?

Is it a memory issue?

Any help or advice would be greatly appreciated.

Thanks

[marcus_sommer]

I think there is an issue with the oledb-driver. It's not a qvd problem then if the query fetched 0 lines then it couldn't be stored data. Also the amount of data shouldn't struck any kind of limitations. By the way are you sure that you really all 588 fields needed in qv?

To check I would load only a few fields and/or apply some where-condition. Then I would extend these step by step notice the load-times.

- Marcus

[#3]

Hi Marcus

Thanks for the reply.

I have arranged for the SQL Server log file to be sent to me so that I can check what happened at that end around the time the query was run.

You are right, we don't need all 588 fields, but the CRM data loading routine in QlikView only started to fail when we started including the filtered views (500+ fields) rather than the regular views (200+ fields).  We want to understand why.

[#4]

I would be interested in finding out what has caused this issue for you.

We currently report out of CRM and have a similar issue. 1 of our user, using the same OLEDB connection string and SQL* from a view pulls down all the data, however another user using the same qlikview file can run it and only see the fields and no data.

[Colin-Albert]

Could it be a record locking issue?

Can you try adding a "nolock" hint to the sql query.

    select * from tablename with nolock