synonym for StProc prevents ADO.Parameters to Refresh()

hi
i have a stored proc, pointed by a synonym
i wish to execute it vía:

cmd.commandType= adStoredProc
cmd.commandText= "s_MyStoredProc"
cmd.parameters.refresh ---> to get the collection

the last line, can't retrieve the Parameters[] collection

if i execute the stored proc directly
the Refresh() method works fine

maybe is there any other commandType for this purpose ?
any idea ?

---
thanks
KS

Re: synonym for StProc prevents ADO.Parameters to Refresh()

keyser soze wrote:
> hi
> i have a stored proc, pointed by a synonym

I don't know what you mean by "synonym". You appear to be supplying the
name of a procedure to the commandText

> i wish to execute it vía:
>
> cmd.commandType= adStoredProc

Please show actual code: adStoredProc is not a valid constant. I know
what you meant to type, but it makes it obvious that your code is not
real and we have to guess what it actually looks like.

> cmd.commandText= "s_MyStoredProc"
> cmd.parameters.refresh ---> to get the collection

Don't. This is a very bad idea to make ADO make a second trip to the
database just to retrieve the parameter definitions. Either build the
collection yourself, or use the stored-procedure-as-connection-method
technique to execute the procedure

>
> the last line, can't retrieve the Parameters[] collection
>
> if i execute the stored proc directly
> the Refresh() method works fine

I don't know what you mean by executing it "directly". Provide the code
snippet that allows Refresh(ugh!!) to work.



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: synonym for StProc prevents ADO.Parameters to Refresh()

thanks, Bob

i'm talking about "synonym" of sql server 2005
that is, a pointer to -almost- any object

this is my implementation

------------ -------------
database X database Y
------------ -------------
s_MyStoredProc - - - - - - - - - - - > MyStoredProc
.....
MyLocalStoredProc
....

in a simple case, when i invoke this:
cmd.commandText= "MyLocalStoredProc"
cmd.Parameters.Refresh()
cmd( 1 ) = myPar_1
...
cmd( N ) = myPar_N
(*) where : cmd is an ADODB.Command object

cmd.Parameters.Refresh() retrieves the parameters collection
in this way, i don't have to create each parameter manually

but ( THIS IS MY CONCERN ) when i try to use:
cmd.commandText= "Y.dbo.MyStoredProc" -or- "s_MyStoredProc"

then, method ADO.Command.Parameters.Refresh()
can't retrieve parameters

i know i can simply call the stored proc
connecting directly the app to dabatase Y
but, well, i would like to know
why the way i mention don't works

*** all, using classic asp ***

thanks again
i wait response

friendly, KS

"Bob Barrows [MVP]" escribió en el mensaje
news:%23if%23NkrrHHA.2240@TK2MSFTNGP03.phx.gbl...
> keyser soze wrote:
> > hi
> > i have a stored proc, pointed by a synonym
>
> I don't know what you mean by "synonym". You appear to be supplying the
> name of a procedure to the commandText
>
> > i wish to execute it vía:
> >
> > cmd.commandType= adStoredProc
>
> Please show actual code: adStoredProc is not a valid constant. I know
> what you meant to type, but it makes it obvious that your code is not
> real and we have to guess what it actually looks like.
>
> > cmd.commandText= "s_MyStoredProc"
> > cmd.parameters.refresh ---> to get the collection
>
> Don't. This is a very bad idea to make ADO make a second trip to the
> database just to retrieve the parameter definitions. Either build the
> collection yourself, or use the stored-procedure-as-connection-method
> technique to execute the procedure
>
> >
> > the last line, can't retrieve the Parameters[] collection
> >
> > if i execute the stored proc directly
> > the Refresh() method works fine
>
> I don't know what you mean by executing it "directly". Provide the code
> snippet that allows Refresh(ugh!!) to work.
>
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: synonym for StProc prevents ADO.Parameters to Refresh()

Well, I have yet to use this "synonym" functionality and, as I said, I
strictly avoid using Parameters.Refresh in production code so I will not be
able to help here. you might try posting in a sqlserver group.

I have created a tool to generate the parameter creation code. You can get
it here:
http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator .zip

keyser soze wrote:
> thanks, Bob
>
> i'm talking about "synonym" of sql server 2005
> that is, a pointer to -almost- any object
>
> this is my implementation
>
> ------------ -------------
> database X database Y
> ------------ -------------
> s_MyStoredProc - - - - - - - - - - - > MyStoredProc
> ....
> MyLocalStoredProc
> ...
>


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: synonym for StProc prevents ADO.Parameters to Refresh()

"Bob Barrows [MVP]" escribió en el mensaje
news:u0nK4Z1rHHA.1848@TK2MSFTNGP03.phx.gbl...
> Well, I have yet to use this "synonym" functionality and, as I said, I
> strictly avoid using Parameters.Refresh in production code so I will not
be

what is the technical reason to avoid it ?

> able to help here. you might try posting in a sqlserver group.

yes, i did :-(

>
> I have created a tool to generate the parameter creation code. You can get
> it here:
> http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator .zip
>

i go to read it

> keyser soze wrote:
> > thanks, Bob
> >
> > i'm talking about "synonym" of sql server 2005
> > that is, a pointer to -almost- any object
> >
> > this is my implementation
> >
> > ------------ -------------
> > database X database Y
> > ------------ -------------
> > s_MyStoredProc - - - - - - - - - - - > MyStoredProc
> > ....
> > MyLocalStoredProc
> > ...
> >
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Re: synonym for StProc prevents ADO.Parameters to Refresh()

keyser wrote on Fri, 15 Jun 2007 11:38:58 -0300:

> "Bob Barrows [MVP]" escribió en el mensaje
> news:u0nK4Z1rHHA.1848@TK2MSFTNGP03.phx.gbl...
>> Well, I have yet to use this "synonym" functionality and, as I said, I
>> strictly avoid using Parameters.Refresh in production code so I will not
> be
>
> what is the technical reason to avoid it ?

As Bob mentioned earlier, it causes additional data to be passed back and
forth to the server. Each time you perform Parameters.Refresh ADO will have
to connect to SQL Server, retrieve the proc definition, and then create the
Parameters collection from the retrieved data. By explicitly writing the
Parameter creation in your code you avoid this - in a heavily hit site this
can have a significant impact on performance.

Dan

Re: synonym for StProc prevents ADO.Parameters to Refresh()

keyser soze wrote:
> "Bob Barrows [MVP]" escribió en el mensaje
> news:u0nK4Z1rHHA.1848@TK2MSFTNGP03.phx.gbl...
>> Well, I have yet to use this "synonym" functionality and, as I said,
>> I strictly avoid using Parameters.Refresh in production code so I
>> will not be
>
> what is the technical reason to avoid it ?

Performance, resources and scalability. Again, every time you execute the
stored procedure requires an extra trip to the database, a trip that can
easily be avoided by either building the parameters collection yourself, or
using a different technique to execute your procedure when an explicit
Parameters collection is not needed, such as when you are passing only input
parameters and you don't need to read the Return parameter value. See my
canned response here:

http://groups.google.com/group/microsoft.public.scripting.vb script/msg/61fedf4e1efd63a6


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: synonym for StProc prevents ADO.Parameters to Refresh()

i didn't knew about that "extra-trip"

i think i need to change some things...

your comments was very helpful

Bob, Daniel : thanks
ks

"keyser soze" escribió en el mensaje
news:e6OAsk1rHHA.4020@TK2MSFTNGP05.phx.gbl...
> "Bob Barrows [MVP]" escribió en el mensaje
> news:u0nK4Z1rHHA.1848@TK2MSFTNGP03.phx.gbl...
> > Well, I have yet to use this "synonym" functionality and, as I said, I
> > strictly avoid using Parameters.Refresh in production code so I will not
> be
>
> what is the technical reason to avoid it ?
>
> > able to help here. you might try posting in a sqlserver group.
>
> yes, i did :-(
>
> >
> > I have created a tool to generate the parameter creation code. You can
get
> > it here:
> > http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator .zip
> >
>
> i go to read it
>
> > keyser soze wrote:
> > > thanks, Bob
> > >
> > > i'm talking about "synonym" of sql server 2005
> > > that is, a pointer to -almost- any object
> > >
> > > this is my implementation
> > >
> > > ------------ -------------
> > > database X database Y
> > > ------------ -------------
> > > s_MyStoredProc - - - - - - - - - - - > MyStoredProc
> > > ....
> > > MyLocalStoredProc
> > > ...
> > >
> >
> >
> > --
> > Microsoft MVP - ASP/ASP.NET
> > Please reply to the newsgroup. This email account is my spam trap so I
> > don't check it very often. If you must reply off-line, then remove the
> > "NO SPAM"
> >
> >
>
>