I am having trouble calling stored procedures that have references to linked servers.
Some quick points about my report:
insert into [MyDatabase].[dbo].[MyTable]
SELECT * FROM [LinkedServerName].[RemoteDatabase].[dbo].[RemoteTable]
Currently, I am still using the personal edition so not sure if this is a limitation.
Create the Batch file and call the Batch file in the script with EXECUTE command.
Please make sure, you need to select below 2 options in the script.
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
Thanks for posting this. Are you able to pass variable values to a SQL stored procedure? I need to pass 4 values to procedure and cant seem to find anything relating to this.
Any help is appreciated.
I tried it in this way
But it works only if put the password directly in .
It doesn't work with encrypted password
Do you have an idea why?
Not in front of my old script, but I'm sure you can..
SQL exec sp_test & ' ' & varParameter1 & ',' & varParameter2
Like I say, not in front of my script, but I'm sure as long as the variable is loaded from the script you can pass it into the exec query.
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.