7 Replies Latest reply: Jan 27, 2015 12:36 AM by purushotham m RSS

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

      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.

        • Re: How to call SQL Server stored procedures that have references to linked servers.
          Srikanth P

          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.

           

          script_execute.JPG.jpg

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

            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:

            server.png

             

            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

            • Re: How to call SQL Server stored procedures that have references to linked servers.
              purushotham m

              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.