Post reply

SP set "With Encryption" issues

  • March 14, 2019 at 2:19 am

    #416872

    For all my SQL functions (from SQL server 2005--2017), I created them "WITH ENCRYPTION" as the following sample. I have never had any problems with that until this morning when I was preparing to deploy one function after debugging. The function keep looping and it would never return result. When I took away "WITH ENCRYPTION" and rebuilt it, result returned in 1s.
    Can anyone tells what I have missed here? I don't want to deploy my functions without encryption. 

    Thanks in advance.
    Wilson


    CREATE FUNCTION myfuctions (@P1 Char(6))
    RETURNS @BalTable TABLE 
             (col1 varchar(6), col2 varchar(30), etc...)

    WITH ENCRYPTION
    AS
    BEGIN
    ....
    END

     

    This is the first time I encounter

    Together, we can make wonders!

  • March 14, 2019 at 1:25 pm

    #2024491

    Do you seriously expect us to look at nothing and magically see where your problem is?   Seriously?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • March 14, 2019 at 9:08 pm

    #2024520

    Hi Steve,
    I feel your frustration on my previous question, I apologize for that. English is my second language and perhaps I didn't describe the question clearly in my post. What I am curiously to find out is why after I added the option "WITH ENCRYPTION" to the fast working script (1 second ) will cause it halted (keep querying for hours). I was trying to point out where the option is added by the sample only, not to seek for help to rectify the script.

    Together, we can make wonders!

  • March 15, 2019 at 7:18 am

    #2024562

    AhTu_SQL2k+ - Thursday, March 14, 2019 9:08 PM

    Hi Steve,
    I feel your frustration on my previous question, I apologize for that. English is my second language and perhaps I didn't describe the question clearly in my post. What I am curiously to find out is why after I added the option "WITH ENCRYPTION" to the fast working script (1 second ) will cause it halted (keep querying for hours). I was trying to point out where the option is added by the sample only, not to seek for help to rectify the script.

    I'd already validated that your syntax wasn't the problem.   However, with no idea what the function does or the code within, we've got zero chance at helping.   Although there are a couple of things to try.   Force the object to be recompiled by running sp_recompile on it.  Then capture an execution plan when you run it with encryption, and again when you run it without.   Look at the differences.   It might tell you something.   However, without either of those execution plans, we're probably going to be left guessing.

    EDIT:  Be sure to recompile between executions, so you are starting at a known point each time.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • March 16, 2019 at 9:37 pm

    #2024710

    Thanks,Steve for the head up. I will try your suggestions and find out the issue.

    Together, we can make wonders!

  • March 17, 2019 at 12:42 pm

    #2024729

    My recommendation is to stop using "WITH ENCRYPTION".  It does virtually nothing to protect your code.  Even software packages like RedGate SQL Compare automatically breaks the encryption so the user can view the code to compare between two databases.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply