7 Replies Latest reply on Sep 3, 2018 3:01 AM by Armando Santos

    help with OPENQUERY with a string parameter

    Armando Santos

      Hello

       

      I need to declare a string variable, like I easily do within SSMS, within the Custom SQL connection.

       

      Basically, I want to be able to reproduce the below SSMS statement:

       

       

       

      DECLARE @TestString CHAR(100) = 'Hello world';

       

      Within a Custom SQL connection with a string parameter.

       

       

      Parameter:

       

      Tableau Custom SQL:

      SELECT * FROM OPENQUERY([myServer], 'DECLARE @TestString2 CHAR(100); set @TestString2 = <Parameters.Parameter>')

       

       

      But this statement is always throwing this error:

       

       

       

      So, how can I declare a string variable by using a string parameter in Tableau Custom SQL connection?

       

       

      NOTE:

           I am using Tableau Desktop 10.5.7 version linked with MS SQL Server 14.0

       

       

      Regards

      Armando Santos

       

       

       

       

       

        • 1. Re: help with OPENQUERY with a string parameter
          Ken Flerlage

          Is there a specific reason you're using OPENQUERY?

          • 2. Re: help with OPENQUERY with a string parameter
            Armando Santos

            Thanks by the feedback Ken.

             

            I need to use OPENQUERY because I want use "set context_info" statement in SSMS side but with a string parameter instead of a float.

             

            If I use:

                 SELECT * FROM OPENQUERY([myServer], 'set context_info <Parameters.Customer ID (Key)>; ... ')

            And the tableau parameter (<Parameters.Customer ID (Key)>) is a float then the mentioned error is NOT appearing at all.

             

            So, my problem is to pass a string variable by using a string parameter in Tableau Custom SQL connection.

                 DECLARE @TestString CHAR(100) = 'Hello world';

             

            Any idea or suggestion?

             

            Regards

            Armando Santos

            • 3. Re: help with OPENQUERY with a string parameter
              Ken Flerlage

              I have a lot of experience with SQL Server, but I can't say I've ever used set context_info. However, after reading about it, it seems that it expects to be passed a varbinary. If you run the following in SSMS, it works fine because it is SQL is able to implicitly convert the float to a varbinary:

               

              set context_info 19283.3

               

              However, when you pass a string like the following, it fails with the error, "CONTEXT_INFO option requires varbinary (128) NOT NULL parameter."

               

              set context_info 'Hello World'

               

              The following doesn't work either. It returns an error similar to what you are getting, "Incorrect syntax near 'Hello World'."

               

              set context_info CAST('Hello World' AS varbinary(128))

               

              But, if you do the following, it works:

               

              Declare @Bin varbinary(128)

              Set @Bin = CAST('Hello World' as varbinary(128))

              set context_info @Bin

               

              So, I think you're going to need to find a way to convert your string to a varbinary before passing it into OPENQUERY command. Does that make sense?

               

              One additional thought I had was to create a stored procedure on the SQL Server side. That would allow you to push all the logic and syntax over to SQL. Then you could simply call that SP, passing the parameter.

              • 4. Re: help with OPENQUERY with a string parameter
                Armando Santos

                Thanks a million Ken for your help.

                 

                I also tried your code in SQL side:

                Declare @Bin varbinary(128)

                Set @Bin = CAST('Hello World' as varbinary(128))

                set context_info @Bin

                 

                And you are right saying that "CONTEXT_INFO option requires varbinary (128)" that's the reason I need the a string variable by using a string parameter in Tableau Custom SQL connection.

                 

                As per requirement I cannot use functions or store procedures in SQL side, and this makes this task more difficult.

                 

                As far as I see my issue is on Tableau side. I am NOT able to set a string variable. Why? This should be possible.

                The below simple code is throwing the mentioned error.

                     SELECT * FROM OPENQUERY([myServer], 'DECLARE @TestString2 CHAR(100); set @TestString2 = <Parameters.Parameter>')

                 

                Any suggestion is more than welcome?!

                 

                Thanks in advance.

                 

                Armando Santos

                • 5. Re: help with OPENQUERY with a string parameter
                  Armando Santos

                  Good morning Ken

                   

                  If I try your logic:

                  Declare @Bin varbinary(128)

                  Set @Bin = CAST('Hello World' as varbinary(128))

                  set context_info @Bin

                   

                  In Tableau Custom SQL connection I still get the same error message.

                   

                  I kindly ask you guys to assist me on this matter .

                   

                  My problem is to pass a string variable by using a string parameter in Tableau Custom SQL connection (as shown bellow).

                   

                  Regards

                  Armando Santos

                   

                  • 6. Re: help with OPENQUERY with a string parameter
                    Armando Santos

                    Guys I really need your help on this issue.

                    I kindly request your assistance in finding a solution or workaround for this topic.

                     

                    Thanks in advance

                    Armando Santos

                    • 7. Re: help with OPENQUERY with a string parameter
                      Armando Santos

                      I do not want to believe that this problem on the Tableau side has no solution or alternative.

                       

                      I kindly request help me in this problem. I urgently need help to find a good/valid solution.

                       

                      Thanks in advance

                      Armando Santos