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 ?

 

[Bob Barrows [MVP]]

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.

 

[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
.....
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

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.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]

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

 

[Daniel Crichton]

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

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

 

[Bob Barrows [MVP]]

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.vbscript/msg/61fedf4e1efd63a6

 

[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