Post reply

Problem with SYNONYM and stored procedures that have parameters

  • August 20, 2008 at 8:23 am

    #382339

    Does anyone know if you can use a SYNONYM to call a procedure that has input parameters. It would appear that if you create a SYNONYM referencing a stored proc and that proc has input parameters, then when you execute the SYNONYM passing the parameters, the stored procedure that is actually executed does not get the parameters.

  • August 20, 2008 at 8:26 am

    #860645

    i'm looking for an example i have, so when i find it, i'll post it. but i'm almost positive i'm passing parameters to my SYNONYMed proc through a linked server.

    are you getting any error messages? how are you using the SYNONYM? how is it configured?

  • August 20, 2008 at 8:32 am

    #860652

    Thanks for the quick reply.

    In my case, I have Database #1 with the proc. Database #2 is on the same server and is going to use #1's proc. So in #2 we created a synonym. When I'm in #2 and I do

    EXEC dbo.synMyProc

    And error returns saying that the values for the parameters where not specified. Thus, it makes be think the parameters are not getting passed through. If I modify the proc and give default values, then the proc runs. If I change the , it makes no difference.

  • August 20, 2008 at 8:36 am

    #860658

    maybe i'm totally missing something here, but try passing the values to the SYNONYN:

    EXEC dbo.synMyProc @P1, @P2..., @Pn

  • August 20, 2008 at 8:39 am

    #860664

    Did that, doesn't help.

  • August 20, 2008 at 8:57 am

    #860679

    now that i think about it, are you using a 4 part name in your SYN? i think for SYNs pointing at objects on the same machine, you should use a 3 part name (server.schema.object).

    edit:

    ...you should use a 3 part name (database.schema.object).

  • August 20, 2008 at 8:59 am

    #860680

    Lenny, you are right on. I found this post about this problem. I dropped the syn and added it back using a script without the server name and it worked. Go figure!

    http://www.eggheadcafe.com/software/aspnet/31233403/synonyms--stored-proc-pa.aspx

  • November 16, 2011 at 1:49 pm

    #1408863

    Yeah. Same here. Who woulddov thunk it !?!

    :crazy:

Viewing 8 posts - 1 through 7 (of 7 total)

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