How to call SQL Server stored procedures that have references to linked servers.

[#1]

Hi.

I am having trouble calling stored procedures that have references to linked servers.

Some quick points about my report:

• I have a "Refresh" button that calls a macro that executes stored procedures in my SQL Server DB (local) - am using SQL Server 2012 express
• I have tested this button-- it can correctly connect to my DB and execute any commands or statements I pass on to it
• If the SQL command(s) I pass references objects in the local db only, it works fine. However, if the stored proc references a linked server, such as the proc below, it does not push through:

CREATE PROCEDURE

  [dbo].[TriggerDataRefresh]

AS

  insert into [MyDatabase].[dbo].[MyTable]

  SELECT * FROM [LinkedServerName].[RemoteDatabase].[dbo].[RemoteTable]

• Executing the stored procedure via Sql Server Management Studio or my IDE (DBeaver) using the credentials I use in my QV report works fine too

Currently, I am still using the personal edition so not sure if this is a limitation.

[#2]

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.

[#3]

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.

[#4]

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.

Solution:

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

go

reconfigure with override

go

sp_configure 'remote query timeout', 3600

go

reconfigure with override

go

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.

SUB PRC_TriggerDataRefresh

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

strSQL="execute [MyDatabase].[dbo].[TriggerDataRefresh]"

objADO.Open
objADO.BeginTrans
objADO.Execute strSQL, iAffected
objADO.CommitTrans

objADO.Close

END SUB

[#5]

Hi,

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.

Tom

[bumin]

Hi

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?

thanks

Bumin

[#7]

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.

[Anonymous]

Hi, 
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.