Dathu, thanks for your reply.
For a moment there I forgot that there are other ways of executing macros in a QV report. I tried your suggestion but was still getting the same error.
After some debugging, I found out that it was my VBScript (I did not include it in the original post) that is at fault, and not Qlikview. WIll be replying to this thread with the solution in case other people encounter a similar problem.
It turns out that Qlikview doesn't have a limitation when it comes to executing SQL Server stored procedures. Whether you're executing them from a macro or from the load script it should work as long as you set the timeouts properly.
For my case above, I was calling a macro from a button in my report. The macro calls on a stored procedure in my local database that references a linked server to another database. Executing the stored procedure in IDEs works fine, but it returns a "query timeout error" when executed inside Qlikview or from a .vbs file in my local system. I observed later on that it takes exactly 30 seconds for the error to appear.
After much debugging, found that it's a problem with the remote database server and my macro itself-- the timeouts were not properly set. Note: The expected runtime of my procedure is around 3 minutes.
1.) Properly set the remote login and query timeout in the remote database:
a.) Change it via script http://support.microsoft.com/kb/314530. Make sure the timeouts are longer than the expected runtime of your stored procedure.
sp_configure 'remote login timeout', 3600
reconfigure with override
sp_configure 'remote query timeout', 3600
reconfigure with override
b.) Change it in SQL Server Management Studio. You can see this setting in the Connections tab of your server setting:
2.) Set the connection timeout in your macro/vbs script. I think the default if you don't set this is 30. Here's a copy of the macro I'm using, slighly edited.
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<mydatabase>;Data Source=<myserver>;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;User ID=<myusername>;Password=<mypassword>"
conn.CommandTimeout = 3600 --> I did not have this line previously
objADO.Execute strSQL, iAffected
I also tried executing Stored procedures containing Link to other SQL server and it dint work, but I found solution for this problem, We have to create 2 Stored procedures to make this thing work and below are the steps
1. Create Table in in Actual DB to hold values from Linked server.
2. Create first Stored procedures to insert data from Linked server to actual DB
3. Create Stored procedures to fetch data from Table of Actual DB
Then execute both the stored procedures sequentially in QlikView to get the result.
Since QliKView can't read data from Stored procedures having Link to other server, This might work.