problem with synonyms

13 Sep 2006 12:13 PM

bajopalabra

hi
i have a problem when i try to
EXEC a stored procedure thru its synonym
that is : [exec synonym_to_sp]

this only happen when i do that
inside another [sp] in another db
that is
            create proc [sp] as
            exec [synonym_to_sp]
            go

the error i get is :
"Procedure or Function 'sp_test'
expected parameter '@par', that is not specificated"

- - - - - - - - - - - - - - - - - - - - - - - - - -
believe it or not, the solution to this error
is CREATE SYNONYM <<< without >>> the Server_Name
- - - - - - - - - - - - - - - - - - - - - - - - - -

is it a bug ??
what is the reason of this behavior ?
i gone mad trying to figure out ....

--------------------------------------------------------
----
---- THIS SCRIPT REPRODUCES THE BEHAVIOR
----
--------------------------------------------------------
-- in db_A
use db_A
create procedure dbo.sp_test
@par varchar(10),
@out varchar(10) output
as
print @par
set @out= @par
go
-- in db_B
use db_B
create synonym sy_sp_test for      db_A.frba.dbo.sp_test
-- create synonym sy_sp_test for            frba.dbo.sp_test
-- uncomment line above for the script WORK FINE
go
create procedure dbo.sp_test
as
declare @returned varchar(10)
exec sy_sp_test 'hello', @out= @returned output
go

-- NOW , TEST IT ON DATABASE db_B, let's execute
sp_test

--
atte,
Hernán

13 Sep 2006 12:57 PM

Aaron Bertrand [SQL Server MVP]

Show quote Hide quote

> --------------------------------------------------------
> ----
> ---- THIS SCRIPT REPRODUCES THE BEHAVIOR
> ----
> --------------------------------------------------------
> -- in db_A
> use db_A
> create procedure dbo.sp_test
> @par varchar(10),
> @out varchar(10) output
> as
> print @par
> set @out= @par
> go
> -- in db_B
> use db_B
> create synonym sy_sp_test for db_A.frba.dbo.sp_test
> -- create synonym sy_sp_test for frba.dbo.sp_test
> -- uncomment line above for the script WORK FINE
> go
> create procedure dbo.sp_test
> as
> declare @returned varchar(10)
> exec sy_sp_test 'hello', @out= @returned output
> go
>
>
> -- NOW , TEST IT ON DATABASE db_B, let's execute
> sp_test

This script also reproduces the behavior:

use tempdb;
go
create procedure dbo.sp_test
    @par varchar(10),
    @out varchar(10) output
as
    print @par;
    set @out= @par;
go
exec dbo.sp_test;
go
drop procedure dbo.sp_test;
go

Msg 201, Level 16, State 4, Procedure sp_test, Line 0
Procedure or Function 'sp_test' expects parameter '@par', which was not
supplied.

Where you call a stored procedure incorrectly doesn't matter if you call it
directly or through a synonym. Also note that your syntax is slightly
incorrect:

exec sy_sp_test 'hello', @out= @returned output

If you name one parameter, you must name all parameters.

13 Sep 2006 1:15 PM

Roy Harvey

On Wed, 13 Sep 2006 08:57:36 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@dnartreb.noraa> wrote:

>Also note that your syntax is slightly
>incorrect:
>
>exec sy_sp_test 'hello', @out= @returned output
>
>If you name one parameter, you must name all parameters.

That is not my understanding, nor my experience. All un-named
parameters must appear first, and in order without gaps. Named
parameters must appear after un-named, and can appear in any order.
For an extremely silly example, the stored procedure sp_sproc_columns
has five parameters, and this works fine:

exec master..sp_sproc_columns 'sp_sproc_columns', @column_name =
'@column_name'

Roy Harvey
Beacon Falls, CT

13 Sep 2006 1:27 PM

Aaron Bertrand [SQL Server MVP]

>>exec sy_sp_test 'hello', @out= @returned output
>>
>>If you name one parameter, you must name all parameters.
>
> That is not my understanding, nor my experience. All un-named
> parameters must appear first, and in order without gaps.

Thanks for the correction. I'll amend my statement by changing "must" to
"should." I guess I've just been "lucky" that whenever I've forgotten to
name a parameter, it wasn't in the correct order or location.

A

13 Sep 2006 1:22 PM

bajopalabra

thanks aaron
yes, i know, i forget to name all arguments

i named the first argument, as you said :

exec sy_sp_test @par= 'hello', @out= @returned output

but i still get the error
( and it is still avoided with creating
the synonym without "server_name" )

what is wrong ?

--
atte,
Hernán

Show quoteHide quote

14 Sep 2006 2:27 PM

Aaron Bertrand [SQL Server MVP]

I filed a bug here:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=202754

This is definitely not working as it should. The short-term response will
probably be, don't include a local server name. Later, they will either fix
it, or make the CREATE SYNONYM statement fail if it uses 4-part naming and
the first name is actually the same as the local server name.

Until there is a resolution, I suggest that path. Why do you need the local
server name in the synonym definition?

Show quoteHide quote

"bajopalabra" <bajopala***@hotmail.com> wrote in message
news:O7lOgcz1GHA.3476@TK2MSFTNGP04.phx.gbl...
> thanks aaron
> yes, i know, i forget to name all arguments
>
> i named the first argument, as you said :
>
> exec sy_sp_test @par= 'hello', @out= @returned output
>
> but i still get the error
> ( and it is still avoided with creating
> the synonym without "server_name" )
>
> what is wrong ?
>
> --
> atte,
> Hernán
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> escribió en el
> mensaje news:u2zZKQz1GHA.968@TK2MSFTNGP03.phx.gbl...
> | > --------------------------------------------------------
> | > ----
> | > ---- THIS SCRIPT REPRODUCES THE BEHAVIOR
> | > ----
> | > --------------------------------------------------------
> | > -- in db_A
> | > use db_A
> | > create procedure dbo.sp_test
> | > @par varchar(10),
> | > @out varchar(10) output
> | > as
> | > print @par
> | > set @out= @par
> | > go
> | > -- in db_B
> | > use db_B
> | > create synonym sy_sp_test for      db_A.frba.dbo.sp_test
> | > -- create synonym sy_sp_test for            frba.dbo.sp_test
> | > -- uncomment line above for the script WORK FINE
> | > go
> | > create procedure dbo.sp_test
> | > as
> | > declare @returned varchar(10)
> | > exec sy_sp_test 'hello', @out= @returned output
> | > go
> | >
> | >
> | > -- NOW , TEST IT ON DATABASE db_B, let's execute
> | > sp_test
> |
> |
> |
> | This script also reproduces the behavior:
> |
> | use tempdb;
> | go
> | create procedure dbo.sp_test
> |     @par varchar(10),
> |     @out varchar(10) output
> | as
> |     print @par;
> |     set @out= @par;
> | go
> | exec dbo.sp_test;
> | go
> | drop procedure dbo.sp_test;
> | go
> |
> | Msg 201, Level 16, State 4, Procedure sp_test, Line 0
> | Procedure or Function 'sp_test' expects parameter '@par', which was not
> | supplied.
> |
> | Where you call a stored procedure incorrectly doesn't matter if you call
> it
> | directly or through a synonym. Also note that your syntax is slightly
> | incorrect:
> |
> | exec sy_sp_test 'hello', @out= @returned output
> |
> | If you name one parameter, you must name all parameters.
> |
> |
>
>

14 Sep 2006 7:33 PM

bajopalabra

actually
i don't need it
but gone mad trying to figure out
where i had the problem

ok
let's remove the server name (by now)

--
atte,
Hernán

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> escribió en el
mensaje news:u1PWMnA2GHA.4264@TK2MSFTNGP05.phx.gbl...
| I filed a bug here:
|
|

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=202754
Show quoteHide quote

|
| This is definitely not working as it should. The short-term response will
| probably be, don't include a local server name. Later, they will either
fix
| it, or make the CREATE SYNONYM statement fail if it uses 4-part naming and
| the first name is actually the same as the local server name.
|
| Until there is a resolution, I suggest that path. Why do you need the
local
| server name in the synonym definition?
|
|
|
|
|
|
| "bajopalabra" <bajopala***@hotmail.com> wrote in message
| news:O7lOgcz1GHA.3476@TK2MSFTNGP04.phx.gbl...
| > thanks aaron
| > yes, i know, i forget to name all arguments
| >
| > i named the first argument, as you said :
| >
| > exec sy_sp_test @par= 'hello', @out= @returned output
| >
| > but i still get the error
| > ( and it is still avoided with creating
| > the synonym without "server_name" )
| >
| > what is wrong ?
| >
| > --
| > atte,
| > Hernán
| > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> escribió en
el
| > mensaje news:u2zZKQz1GHA.968@TK2MSFTNGP03.phx.gbl...
| > | > --------------------------------------------------------
| > | > ----
| > | > ---- THIS SCRIPT REPRODUCES THE BEHAVIOR
| > | > ----
| > | > --------------------------------------------------------
| > | > -- in db_A
| > | > use db_A
| > | > create procedure dbo.sp_test
| > | > @par varchar(10),
| > | > @out varchar(10) output
| > | > as
| > | > print @par
| > | > set @out= @par
| > | > go
| > | > -- in db_B
| > | > use db_B
| > | > create synonym sy_sp_test for      db_A.frba.dbo.sp_test
| > | > -- create synonym sy_sp_test for            frba.dbo.sp_test
| > | > -- uncomment line above for the script WORK FINE
| > | > go
| > | > create procedure dbo.sp_test
| > | > as
| > | > declare @returned varchar(10)
| > | > exec sy_sp_test 'hello', @out= @returned output
| > | > go
| > | >
| > | >
| > | > -- NOW , TEST IT ON DATABASE db_B, let's execute
| > | > sp_test
| > |
| > |
| > |
| > | This script also reproduces the behavior:
| > |
| > | use tempdb;
| > | go
| > | create procedure dbo.sp_test
| > |     @par varchar(10),
| > |     @out varchar(10) output
| > | as
| > |     print @par;
| > |     set @out= @par;
| > | go
| > | exec dbo.sp_test;
| > | go
| > | drop procedure dbo.sp_test;
| > | go
| > |
| > | Msg 201, Level 16, State 4, Procedure sp_test, Line 0
| > | Procedure or Function 'sp_test' expects parameter '@par', which was
not
| > | supplied.
| > |
| > | Where you call a stored procedure incorrectly doesn't matter if you
call
| > it
| > | directly or through a synonym. Also note that your syntax is slightly
| > | incorrect:
| > |
| > | exec sy_sp_test 'hello', @out= @returned output
| > |
| > | If you name one parameter, you must name all parameters.
| > |
| > |
| >
| >
|
|