synonym for StProc prevents ADO.Parameters to Refresh()

[keyser soze]

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

[Bob Barrows [MVP]]

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.

[keyser soze]

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
.....
MyLocalStoredPr oc
....

in a simple case, when i invoke this:
cmd.commandText = "MyLocalStoredP roc"
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.MyStored Proc" -or- "s_MyStoredProc "

then, method ADO.Command.Par ameters.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]" <re******@NOyah oo.SPAMcomescri bió en el mensaje
news:%2******** **********@TK2M SFTNGP03.phx.gb l...

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.

[Bob Barrows [MVP]]

Well, I have yet to use this "synonym" functionality and, as I said, I
strictly avoid using Parameters.Refr esh 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/Clas..._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
....
MyLocalStoredPr oc
...

--
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"

[keyser soze]

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomescri bió en el mensaje
news:u0******** ******@TK2MSFTN GP03.phx.gbl...

Well, I have yet to use this "synonym" functionality and, as I said, I
strictly avoid using Parameters.Refr esh 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/Clas..._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
....
MyLocalStoredPr oc
...

--
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"

[Daniel Crichton]

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

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomescri bió en el mensaje
news:u0******** ******@TK2MSFTN GP03.phx.gbl...

>Well, I have yet to use this "synonym" functionality and, as I said, I
strictly avoid using Parameters.Refr esh 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.Refr esh 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

[Bob Barrows [MVP]]

keyser soze wrote:

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomescri bió en el mensaje
news:u0******** ******@TK2MSFTN GP03.phx.gbl...

>Well, I have yet to use this "synonym" functionality and, as I said,
I strictly avoid using Parameters.Refr esh 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/micro...fedf4e1efd63a6
--
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"

[keyser soze]

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" <ba*********@ho tmail.comescrib ió en el mensaje
news:e6******** ******@TK2MSFTN GP05.phx.gbl...

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomescri bió en el mensaje
news:u0******** ******@TK2MSFTN GP03.phx.gbl...

Well, I have yet to use this "synonym" functionality and, as I said, I
strictly avoid using Parameters.Refr esh 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/Clas..._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
....
MyLocalStoredPr oc
...
>

--
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"