|
|
You're in the
sql server odbc
group:Extended Stored Procedure: Get the current db of the client
sql server odbc:
Is there a way to get the current database of the client who calls my Extended Stored Procedure? I have written a DLL in Visual Studion 2005 for the SQL server 2003 in C/C++ using the functions srv_*. Thanks. Hans
What version of SQL Server?
It's a limitation of extended stored procedure programming with SQL Server 2000. Some have tried using svr_rpcdb but it will generally just return master as the database name. And it's no longer supported. -Sue On Mon, 8 May 2006 14:41:59 +0200, "Hans Stoessel" [quoted text, click to view] <hstoessel.list@pm-medici.ch> wrote:
>Hi > >Is there a way to get the current database of the client who calls my >Extended Stored Procedure? > >I have written a DLL in Visual Studion 2005 for the SQL server 2003 in C/C++ >using the functions srv_*. > >Thanks. >Hans >
This never worked correctly, this is not way you can get the database
context from within an XP, easiest work around is to use a wrapper SP that passes the db_name() or db_id() as a parameter. In general using wrapper SP's is a good practice for doing parameter validation, and meta data exposure since XP's do not emit the parameter signatures. GertD@SQLDev.Net [quoted text, click to view] "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message news:feuv521gpm893g6hfi4aja0vpp8vguiun4@4ax.com... > What version of SQL Server? > It's a limitation of extended stored procedure programming > with SQL Server 2000. Some have tried using svr_rpcdb but it > will generally just return master as the database name. And > it's no longer supported. > > -Sue > > On Mon, 8 May 2006 14:41:59 +0200, "Hans Stoessel" > <hstoessel.list@pm-medici.ch> wrote: > >>Hi >> >>Is there a way to get the current database of the client who calls my >>Extended Stored Procedure? >> >>I have written a DLL in Visual Studion 2005 for the SQL server 2003 in >>C/C++ >>using the functions srv_*. >> >>Thanks. >>Hans >> >
Does not matter, an XP does not have a call to retrieve the database
context. GertD@SQLDev.Net [quoted text, click to view] "Mike C#" <xxx@yyy.com> wrote in message news:wzR8g.505$Ut2.124@fe09.lga... > Works for SP's, might work with XP's as well: > > 1. Prefix the name with "sp_" > 2. Mark it as a system object with sp_MS_MarkSystemObject > > This causes the SP to run under the context of the database it was called > from, not the master database where it resides. At the least, you can put > an SP wrapper in the master DB for the XP, and pass in the db_name() as a > parameter to the XP and it will have the correct database context (not > "master"). > > > "Gert E.R. Drapers" <GertD@SQLDev@Net> wrote in message > news:%23ZtUJnzcGHA.3632@TK2MSFTNGP05.phx.gbl... >> This never worked correctly, this is not way you can get the database >> context from within an XP, easiest work around is to use a wrapper SP >> that passes the db_name() or db_id() as a parameter. >> >> In general using wrapper SP's is a good practice for doing parameter >> validation, and meta data exposure since XP's do not emit the parameter >> signatures. >> >> GertD@SQLDev.Net >> >> >> "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message >> news:feuv521gpm893g6hfi4aja0vpp8vguiun4@4ax.com... >>> What version of SQL Server? >>> It's a limitation of extended stored procedure programming >>> with SQL Server 2000. Some have tried using svr_rpcdb but it >>> will generally just return master as the database name. And >>> it's no longer supported. >>> >>> -Sue >>> >>> On Mon, 8 May 2006 14:41:59 +0200, "Hans Stoessel" >>> <hstoessel.list@pm-medici.ch> wrote: >>> >>>>Hi >>>> >>>>Is there a way to get the current database of the client who calls my >>>>Extended Stored Procedure? >>>> >>>>I have written a DLL in Visual Studion 2005 for the SQL server 2003 in >>>>C/C++ >>>>using the functions srv_*. >>>> >>>>Thanks. >>>>Hans >>>> >>> >> >> > >
Works for SP's, might work with XP's as well:
1. Prefix the name with "sp_" 2. Mark it as a system object with sp_MS_MarkSystemObject This causes the SP to run under the context of the database it was called from, not the master database where it resides. At the least, you can put an SP wrapper in the master DB for the XP, and pass in the db_name() as a parameter to the XP and it will have the correct database context (not "master"). [quoted text, click to view] "Gert E.R. Drapers" <GertD@SQLDev@Net> wrote in message news:%23ZtUJnzcGHA.3632@TK2MSFTNGP05.phx.gbl... > This never worked correctly, this is not way you can get the database > context from within an XP, easiest work around is to use a wrapper SP that > passes the db_name() or db_id() as a parameter. > > In general using wrapper SP's is a good practice for doing parameter > validation, and meta data exposure since XP's do not emit the parameter > signatures. > > GertD@SQLDev.Net > > > "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message > news:feuv521gpm893g6hfi4aja0vpp8vguiun4@4ax.com... >> What version of SQL Server? >> It's a limitation of extended stored procedure programming >> with SQL Server 2000. Some have tried using svr_rpcdb but it >> will generally just return master as the database name. And >> it's no longer supported. >> >> -Sue >> >> On Mon, 8 May 2006 14:41:59 +0200, "Hans Stoessel" >> <hstoessel.list@pm-medici.ch> wrote: >> >>>Hi >>> >>>Is there a way to get the current database of the client who calls my >>>Extended Stored Procedure? >>> >>>I have written a DLL in Visual Studion 2005 for the SQL server 2003 in >>>C/C++ >>>using the functions srv_*. >>> >>>Thanks. >>>Hans >>> >> > >
DBLIB, dbname() function.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_2gtz.asp enjoy [quoted text, click to view] "Gert E.R. Drapers" <GertD@SQLDev@Net> wrote in message news:u8i0W6WdGHA.4720@TK2MSFTNGP03.phx.gbl... > Does not matter, an XP does not have a call to retrieve the database > context. > GertD@SQLDev.Net > > "Mike C#" <xxx@yyy.com> wrote in message > news:wzR8g.505$Ut2.124@fe09.lga... >> Works for SP's, might work with XP's as well: >> >> 1. Prefix the name with "sp_" >> 2. Mark it as a system object with sp_MS_MarkSystemObject >> >> This causes the SP to run under the context of the database it was called >> from, not the master database where it resides. At the least, you can >> put an SP wrapper in the master DB for the XP, and pass in the db_name() >> as a parameter to the XP and it will have the correct database context >> (not "master"). >> >> >> "Gert E.R. Drapers" <GertD@SQLDev@Net> wrote in message >> news:%23ZtUJnzcGHA.3632@TK2MSFTNGP05.phx.gbl... >>> This never worked correctly, this is not way you can get the database >>> context from within an XP, easiest work around is to use a wrapper SP >>> that passes the db_name() or db_id() as a parameter. >>> >>> In general using wrapper SP's is a good practice for doing parameter >>> validation, and meta data exposure since XP's do not emit the parameter >>> signatures. >>> >>> GertD@SQLDev.Net >>> >>> >>> "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message >>> news:feuv521gpm893g6hfi4aja0vpp8vguiun4@4ax.com... >>>> What version of SQL Server? >>>> It's a limitation of extended stored procedure programming >>>> with SQL Server 2000. Some have tried using svr_rpcdb but it >>>> will generally just return master as the database name. And >>>> it's no longer supported. >>>> >>>> -Sue >>>> >>>> On Mon, 8 May 2006 14:41:59 +0200, "Hans Stoessel" >>>> <hstoessel.list@pm-medici.ch> wrote: >>>> >>>>>Hi >>>>> >>>>>Is there a way to get the current database of the client who calls my >>>>>Extended Stored Procedure? >>>>> >>>>>I have written a DLL in Visual Studion 2005 for the SQL server 2003 in >>>>>C/C++ >>>>>using the functions srv_*. >>>>> >>>>>Thanks. >>>>>Hans >>>>> >>>> >>> >>> >> >> > >
No, because then you need to connect first! So what database do you
establish your connection to? Please don't answer try to answer questions you do not know the answer to. -GertD [quoted text, click to view] "Mike C#" <xxx@yyy.com> wrote in message news:Ga99g.60$Id.19@fe10.lga... > DBLIB, dbname() function. > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_2gtz.asp > > enjoy > > "Gert E.R. Drapers" <GertD@SQLDev@Net> wrote in message > news:u8i0W6WdGHA.4720@TK2MSFTNGP03.phx.gbl... >> Does not matter, an XP does not have a call to retrieve the database >> context. >> GertD@SQLDev.Net >> >> "Mike C#" <xxx@yyy.com> wrote in message >> news:wzR8g.505$Ut2.124@fe09.lga... >>> Works for SP's, might work with XP's as well: >>> >>> 1. Prefix the name with "sp_" >>> 2. Mark it as a system object with sp_MS_MarkSystemObject >>> >>> This causes the SP to run under the context of the database it was >>> called from, not the master database where it resides. At the least, >>> you can put an SP wrapper in the master DB for the XP, and pass in the >>> db_name() as a parameter to the XP and it will have the correct database >>> context (not "master"). >>> >>> >>> "Gert E.R. Drapers" <GertD@SQLDev@Net> wrote in message >>> news:%23ZtUJnzcGHA.3632@TK2MSFTNGP05.phx.gbl... >>>> This never worked correctly, this is not way you can get the database >>>> context from within an XP, easiest work around is to use a wrapper SP >>>> that passes the db_name() or db_id() as a parameter. >>>> >>>> In general using wrapper SP's is a good practice for doing parameter >>>> validation, and meta data exposure since XP's do not emit the parameter >>>> signatures. >>>> >>>> GertD@SQLDev.Net >>>> >>>> >>>> "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message >>>> news:feuv521gpm893g6hfi4aja0vpp8vguiun4@4ax.com... >>>>> What version of SQL Server? >>>>> It's a limitation of extended stored procedure programming >>>>> with SQL Server 2000. Some have tried using svr_rpcdb but it >>>>> will generally just return master as the database name. And >>>>> it's no longer supported. >>>>> >>>>> -Sue >>>>> >>>>> On Mon, 8 May 2006 14:41:59 +0200, "Hans Stoessel" >>>>> <hstoessel.list@pm-medici.ch> wrote: >>>>> >>>>>>Hi >>>>>> >>>>>>Is there a way to get the current database of the client who calls my >>>>>>Extended Stored Procedure? >>>>>> >>>>>>I have written a DLL in Visual Studion 2005 for the SQL server 2003 in >>>>>>C/C++ >>>>>>using the functions srv_*. >>>>>> >>>>>>Thanks. >>>>>>Hans >>>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
And you plan to what? Put the same "wrapper" stored procedure in every
single database on a server? Don't be a dick Gertrude. [quoted text, click to view] "Gert E.R. Drapers" <GertD@SQLDev@Net> wrote in message news:Ou6TvyjdGHA.3632@TK2MSFTNGP05.phx.gbl... > No, because then you need to connect first! So what database do you > establish your connection to? > Please don't answer try to answer questions you do not know the answer to. > -GertD > > "Mike C#" <xxx@yyy.com> wrote in message news:Ga99g.60$Id.19@fe10.lga... >> DBLIB, dbname() function. >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_2gtz.asp >> >> enjoy >> >> "Gert E.R. Drapers" <GertD@SQLDev@Net> wrote in message >> news:u8i0W6WdGHA.4720@TK2MSFTNGP03.phx.gbl... >>> Does not matter, an XP does not have a call to retrieve the database >>> context. >>> GertD@SQLDev.Net >>> >>> "Mike C#" <xxx@yyy.com> wrote in message >>> news:wzR8g.505$Ut2.124@fe09.lga... >>>> Works for SP's, might work with XP's as well: >>>> >>>> 1. Prefix the name with "sp_" >>>> 2. Mark it as a system object with sp_MS_MarkSystemObject >>>> >>>> This causes the SP to run under the context of the database it was >>>> called from, not the master database where it resides. At the least, >>>> you can put an SP wrapper in the master DB for the XP, and pass in the >>>> db_name() as a parameter to the XP and it will have the correct >>>> database context (not "master"). >>>> >>>> >>>> "Gert E.R. Drapers" <GertD@SQLDev@Net> wrote in message >>>> news:%23ZtUJnzcGHA.3632@TK2MSFTNGP05.phx.gbl... >>>>> This never worked correctly, this is not way you can get the database >>>>> context from within an XP, easiest work around is to use a wrapper SP >>>>> that passes the db_name() or db_id() as a parameter. >>>>> >>>>> In general using wrapper SP's is a good practice for doing parameter >>>>> validation, and meta data exposure since XP's do not emit the >>>>> parameter signatures. >>>>> >>>>> GertD@SQLDev.Net >>>>> >>>>> >>>>> "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message >>>>> news:feuv521gpm893g6hfi4aja0vpp8vguiun4@4ax.com... >>>>>> What version of SQL Server? >>>>>> It's a limitation of extended stored procedure programming >>>>>> with SQL Server 2000. Some have tried using svr_rpcdb but it >>>>>> will generally just return master as the database name. And >>>>>> it's no longer supported. >>>>>> >>>>>> -Sue >>>>>> >>>>>> On Mon, 8 May 2006 14:41:59 +0200, "Hans Stoessel" >>>>>> <hstoessel.list@pm-medici.ch> wrote: >>>>>> >>>>>>>Hi >>>>>>> >>>>>>>Is there a way to get the current database of the client who calls my >>>>>>>Extended Stored Procedure? >>>>>>> >>>>>>>I have written a DLL in Visual Studion 2005 for the SQL server 2003 >>>>>>>in C/C++ >>>>>>>using the functions srv_*. >>>>>>> >>>>>>>Thanks. >>>>>>>Hans >>>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
No, you are incorrect; for an extended stored procedure you have to pass in
the database context as a parameter if you need it, that is the only thing that works. Did you ever write an extended stored procedure? Besides that it does not make sense to call the DB-Lib function dbname() untill you established a loopback connection over DB-Library, which would default to the default database for the user which is not the same as the database context. See the attached example which shows this behavior. The srv_rpc* class methods in the OPENDS60.LIB file are obsolete since they are gateway calls and not longer supported; srv_rpcdb() only gave you a database context when you where a remote procedure, which is something different than an extended stored procedure, so that is not giving you want you want either. So Mike C#, the ONLY solution is to pass it in as a parameter! GertD@SQLDev.Net BTW: Next time you are calling somebody names you might want to check your facts before replying an making a fool out of yourself. [quoted text, click to view] "Mike C#" <xxx@yyy.com> wrote in message news:yin9g.439$Id.106@fe10.lga... > And you plan to what? Put the same "wrapper" stored procedure in every > single database on a server? > > Don't be a dick Gertrude. > > "Gert E.R. Drapers" <GertD@SQLDev@Net> wrote in message > news:Ou6TvyjdGHA.3632@TK2MSFTNGP05.phx.gbl... >> No, because then you need to connect first! So what database do you >> establish your connection to? >> Please don't answer try to answer questions you do not know the answer >> to. >> -GertD >> >> "Mike C#" <xxx@yyy.com> wrote in message news:Ga99g.60$Id.19@fe10.lga... >>> DBLIB, dbname() function. >>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_2gtz.asp >>> >>> enjoy >>> >>> "Gert E.R. Drapers" <GertD@SQLDev@Net> wrote in message >>> news:u8i0W6WdGHA.4720@TK2MSFTNGP03.phx.gbl... >>>> Does not matter, an XP does not have a call to retrieve the database >>>> context. >>>> GertD@SQLDev.Net >>>> >>>> "Mike C#" <xxx@yyy.com> wrote in message >>>> news:wzR8g.505$Ut2.124@fe09.lga... >>>>> Works for SP's, might work with XP's as well: >>>>> >>>>> 1. Prefix the name with "sp_" >>>>> 2. Mark it as a system object with sp_MS_MarkSystemObject >>>>> >>>>> This causes the SP to run under the context of the database it was >>>>> called from, not the master database where it resides. At the least, >>>>> you can put an SP wrapper in the master DB for the XP, and pass in the >>>>> db_name() as a parameter to the XP and it will have the correct >>>>> database context (not "master"). >>>>> >>>>> >>>>> "Gert E.R. Drapers" <GertD@SQLDev@Net> wrote in message >>>>> news:%23ZtUJnzcGHA.3632@TK2MSFTNGP05.phx.gbl... >>>>>> This never worked correctly, this is not way you can get the database >>>>>> context from within an XP, easiest work around is to use a wrapper SP >>>>>> that passes the db_name() or db_id() as a parameter. >>>>>> >>>>>> In general using wrapper SP's is a good practice for doing parameter >>>>>> validation, and meta data exposure since XP's do not emit the >>>>>> parameter signatures. >>>>>> >>>>>> GertD@SQLDev.Net >>>>>> >>>>>> >>>>>> "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message >>>>>> news:feuv521gpm893g6hfi4aja0vpp8vguiun4@4ax.com... >>>>>>> What version of SQL Server? >>>>>>> It's a limitation of extended stored procedure programming >>>>>>> with SQL Server 2000. Some have tried using svr_rpcdb but it >>>>>>> will generally just return master as the database name. And >>>>>>> it's no longer supported. >>>>>>> >>>>>>> -Sue >>>>>>> >>>>>>> On Mon, 8 May 2006 14:41:59 +0200, "Hans Stoessel" >>>>>>> <hstoessel.list@pm-medici.ch> wrote: >>>>>>> >>>>>>>>Hi >>>>>>>> >>>>>>>>Is there a way to get the current database of the client who calls >>>>>>>>my >>>>>>>>Extended Stored Procedure? >>>>>>>> >>>>>>>>I have written a DLL in Visual Studion 2005 for the SQL server 2003 >>>>>>>>in C/C++ >>>>>>>>using the functions srv_*. >>>>>>>> >>>>>>>>Thanks. >>>>>>>>Hans >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > begin 666 xpdbcontext.zip M4$L#!!0````(`(.6K33!Q>&[6@$``'(#```/````>'!D8F-O;G1E>'0N<VQN MG9+!3H- $(;/W81W('BI22$+I0T</ `+>E#32*)G"DNS9F4)+$V3VB?SX"/Y M"AUB6Z%MC/:V_\SL/]_,[M?'IX(>6%J)6N12?69UDW UEDW&A!H+WD@F"C5B MG([42%1O"=30JFZ#KH&Q@JZ.[E@83Q0TJ\0K3>506SM^X :A[^B.;WNZ:1*L M.\0T=8P]'+@F9&UKHUVK-ZJV*K-Y6LB5U$8=82S3$MS:V'KJA+8?$5NWH\C4 M;8MXNN_B*7A/B(7'(1F'>*,I*"RR'8&";KF8)UQ!@^]##$&@'^YG"T21LT53 M):V8\43F,&7=\I05W1?![0&A\V;Q_L**L07)CFJ33Y33I*:'=$]#`0#UVI_@ M['!_H1&U[.+\;1=&A]/PH-62!OGBE/\"-[]A/#/PQ5Z]%?78CI=WF>,/W[\? M8[]G>)225I+1<__ACF4'_2@R"A61=Q^'9QL<(@K:`E!+`P04````" `9K:TT M#G(<X3P!```N! ``#P```'AP9&)C;VYT97AT+G-Q;-5334L#,1!]UPK^A]RZ MA5K0DP@%#_;@1?P!"V6[V7[(;A/:KM8_K[X91^VF" I>),QF,GGOS4R2?7UI ML44%AP8%O1W]#4YQ@@6"SF(KS(FHL*<GF"U7&4V8-:UDS.&<-B<[4,O!8T9O M1,PS+7#U8$AA/V&IF23SBEB',>T0-=5XAOZ!4F1TP7W9E>B4R("U5KWGMX\! M50I&?*(6M:ZH.7>L*%/U(5%W9-U2>Z(*E7(KVKTR2M,<6W\#/8^>YA-E9U7Y M3_VN2C05]\L^TAM(\Q7D^3_+-E3&!%?(B=]P7V9/3$Y$M*]PN\P<-_1F:*G_ M'6;$6,UQW-,9AU.<O"F)>.VQ9G?O+^-:]=9<-[I^U)V2;T?FC#=R@4N[DZ\3 M^GGGQQD">Y'7TEI%W1?>2_!I1Q__DN@W=AHIYC_W^ 902P,$% ````@`H):M M-.FTK)/I````DP$```H```!R97-O=7)C92YH=<[-:H0P$ ?PN^ [#/2XL"KL M"UB3@U23Q2^ZIZ!Q4E(D+AI;R[+OWB M99?N7 ;F_YMA@N!R85P0>J2,4):D MM+Q>?2\((-=R&N=166CTO+0#)+L=O*'!J;78@S9R6'H$I0?<;POU[,;=%ZSG MOI/&KG8_2=_;(H:KA1Y5NPP6/MIAP1G4.('!3QB[=Y1VWISO/6GE',3'LJI) MRD7*&OY"R1:8FZ2@,>$L.XGRE#_SK'3$Y=H@"&<$HZ^5,R6OBX2*)LYJ"C\5 MA=%_..%Y'C-R:^$0AH\XJPJ>W?'H@79/TOO3OY^@Z;7ZZ]]02P,$% ````@` M*:>M-/+?F>3O! ``.A(``! ```!X<&1B8VYT>'0N=F-P<F]J[5AM;]LV$/Z< M`OT/AE ,+0K%BOR:,5XA659JS$X\VVGRP4! 26>;K22J%.78V_K?=WI)'2=Q MJK8#M@(%0H,\WAWOGCO>B3EYLP[\R@I$S'C848X.-:4"H<L]%BXZRB4+/7X3 MJT=Z0U?>_/;\V<D[%B?4G\C$8WPD^'MPY?-G!\5LNHF@H^0LE>[KUPINO;M5 MW3[4M)1P1@-D6D>>X_)0PEHJ6P6G%WVKH_S5;/?JIFW5U;IM'ZEUW3)4\UAK MJFVS8>E:K6?5>MJG5&S,N4SUQ1%U;Y6&,E?Y.VQNN/ R)VIZ>D!*11\.3D8^ ME7,N@CA=;9?IHC ODTGY#ZJ91'5'Y&3*N6\S'_)5=7?9Y>&<+1)!)?I='+%# MNW..!4ZR^'M[VL%Y(J-$6DP@&EQL.LJ+EQ/N)ZD8$E^]>+FC*57R*A?L(Y8B M`(]1"3OB^P1VZ'GD"ANZ2RJHB^HF(#$E<F+F1^YY-BL<>-<="3 3YGN]%80R MW<[YJWL%NDDL>9#)E&&_&@XL*NDIA("V<E%&YA*<"8@5<P'COMY\E>RP;PU* MN3'H\B#"J-_1>G >21:P/S-,.XI64!&B2' 7XI@+"^8L9%EJ8)KUSVHZN;9Z MYL4IN<:5=7XY(=<7D[$U&)"KD65VSZ97T^O>U>A\/)T4ZH:H(*#^&',',>PH M4B10;)DT9NXX"=$*Z"[!_8"'U(J]@CQ@CJ!I:MS2+V)$"=S<&>\M4 _$UO9+ M*D*L!0-8@;^5L4!BAC7K)I,C+B1UF,_D!L%V? CB'9.R%.^'Z>7)8+&S24>I M?S$0-*0+\,80\T2X\!#M_:+?(H,8#%CXH70:I\P[L3<\+XLK]2V((/2PC#) M+'[YF'!)NK_.$)Z%H$$E*Q6S(7,%C_E<5B9_#"IINH*8M;59JC&>6;#*)QBN M62AO/,=GSB&.7%OE^Y7RU,:XJ6V5%GZDCO5#5T" 2%#_<UDX&'(O\6&;P.F1 M.Y7\T(-YP5M<.;@?_9W4F"3.9!-+"+9G3*E8@!Q2=\E"^%Q\]D?!2*TM=:UI MR.80EPKME87L'Z ,JQF[95GM=9='91B-*,*NR>:L7.)BL<.$\_DF*)FY(Q[+ M_27[I+K3&9[N7V/P@<;P0W2PRR7WH;@NNW7Z9XO;X\9C+>X+S>SL*YO9_:YT M6PC^HZY4*U%'?JBN]*":%\G^5#5//Z'_A5I>7#(8PQP$=L.T&=[N]4**0>F>
[quoted text, click to view]
"Gert E.R. Drapers" wrote: > No, you are incorrect; for an extended stored procedure you have to pass in > the database context as a parameter if you need it, that is the only thing > that works. Did you ever write an extended stored procedure? I have written several, several, several extended stored procedures. In fact, I just publicly released about 3 dozen that cover everything from AES, Blowfish, Twofish, DES and TripleDES encryption to regular expressions to recursively reading a local subdirectory listing. In fact, here's a little experiment for you extended procedure maestro: Put this regular stored procedure in the Master database: CREATE PROCEDURE dbo.Test1 AS SELECT db_Name() GO Now run it from within the Model database. Or the Northwind database. What database name comes up? Master, that's what. According to your solution, you need to recreate this exact same stored procedure in every single database you own in order to get the current database context out of it. As I said: changing the name to "sp_..." and marking it as a system object will allow you to use JUST ONE copy of the stored procedure in Master. It will run in the context of the CURRENT DATABASE, no matter what database you invoke it from. But I'm sure you're well aware of that. [quoted text, click to view] > Besides that it does not make sense to call the DB-Lib function dbname() > untill you established a loopback connection over DB-Library, which would > default to the default database for the user which is not the same as the > database context. See the attached example which shows this behavior. And that's all well and good. I was simply pointing out some things that might be tried, and you pointed out that it wouldn't work in your own little snide way. [quoted text, click to view] > The srv_rpc* class methods in the OPENDS60.LIB file are obsolete since they > are gateway calls and not longer supported; srv_rpcdb() only gave you a > database context when you where a remote procedure, which is something > different than an extended stored procedure, so that is not giving you want > you want either. I know srv_rpcdb doesn't work, and didn't suggest it as a solution. I'm sure whoever didn't know that will be happy to hear it from you, however. [quoted text, click to view] > So Mike C#, the ONLY solution is to pass it in as a parameter! Which is fine, and perfectly acceptable. The difference is simply this, if you refer back to my original post: Your method requires the same stored procedure be copied to all 28 of my databases. Alternatively I can put a single copy in the Master database and be done with it. [quoted text, click to view] > BTW: Next time you are calling somebody names you might want to check your > facts before replying an making a fool out of yourself. BTW: You should check your facts before you accuse someone of not having any experience in your little domain over there before making a fool of yourself. http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart2.asp http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart3.asp http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart4.asp Of course I'd love an opportunity to learn at the master's feet. So where does Master Gert keep his extended procedures, that I may immerse myself in
He really shouldn't have to defend himself. Gert has
probably the most used, well known xp that isn't shipped with SQL Server - xp_smtp_sendmail: http://www.sqldev.net/ And then other Microsoft SQL Server extended stored procs he was involved with working on as part of the development team. You can find those in the product. And undoubtedly many more not on his site or in the product. Before you keep going after him, I don't think you were on the Microsoft development team for SQL Server so he probably does know more than you about how these need to work. Might be a good idea for you to trust that he knows what he's talking about. -Sue On Mon, 15 May 2006 07:01:02 -0700, Michael C [quoted text, click to view] <MichaelC@discussions.microsoft.com> wrote:
>"Gert E.R. Drapers" wrote: > >> No, you are incorrect; for an extended stored procedure you have to pass in >> the database context as a parameter if you need it, that is the only thing >> that works. Did you ever write an extended stored procedure? > >I have written several, several, several extended stored procedures. In >fact, I just publicly released about 3 dozen that cover everything from AES, >Blowfish, Twofish, DES and TripleDES encryption to regular expressions to >recursively reading a local subdirectory listing. > >In fact, here's a little experiment for you extended procedure maestro: Put >this regular stored procedure in the Master database: > >CREATE PROCEDURE dbo.Test1 >AS >SELECT db_Name() >GO > >Now run it from within the Model database. Or the Northwind database. What >database name comes up? Master, that's what. According to your solution, >you need to recreate this exact same stored procedure in every single >database you own in order to get the current database context out of it. > >As I said: changing the name to "sp_..." and marking it as a system object >will allow you to use JUST ONE copy of the stored procedure in Master. It >will run in the context of the CURRENT DATABASE, no matter what database you >invoke it from. But I'm sure you're well aware of that. > >> Besides that it does not make sense to call the DB-Lib function dbname() >> untill you established a loopback connection over DB-Library, which would >> default to the default database for the user which is not the same as the >> database context. See the attached example which shows this behavior. > >And that's all well and good. I was simply pointing out some things that >might be tried, and you pointed out that it wouldn't work in your own little >snide way. > >> The srv_rpc* class methods in the OPENDS60.LIB file are obsolete since they >> are gateway calls and not longer supported; srv_rpcdb() only gave you a >> database context when you where a remote procedure, which is something >> different than an extended stored procedure, so that is not giving you want >> you want either. > >I know srv_rpcdb doesn't work, and didn't suggest it as a solution. I'm >sure whoever didn't know that will be happy to hear it from you, however. > >> So Mike C#, the ONLY solution is to pass it in as a parameter! > >Which is fine, and perfectly acceptable. The difference is simply this, if >you refer back to my original post: Your method requires the same stored >procedure be copied to all 28 of my databases. Alternatively I can put a >single copy in the Master database and be done with it. > >> BTW: Next time you are calling somebody names you might want to check your >> facts before replying an making a fool out of yourself. > >BTW: You should check your facts before you accuse someone of not having >any experience in your little domain over there before making a fool of >yourself. > >http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp >http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart2.asp >http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart3.asp >http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart4.asp > >Of course I'd love an opportunity to learn at the master's feet. So where >does Master Gert keep his extended procedures, that I may immerse myself in >the knowledge to be gained?
Thanks for the link Sue. The link for xp_smtp_sendmail came up with a
"HTTP/1.1 404 Object Not Found" from here. To answer your question, Sue, no I was not on the SQL Server development team. And no, I am not going after Gert Sue. Gert does not have to defend his vast quantities of knowledge. I have never questioned his technical abilities or his genius intellect. So far Gert has accused me of not knowing what I'm talking about, for simply throwing out a few ideas. A simple, "no that won't work because ..." is sufficient for most folks, but Gert does appear to want to go that extra mile to put someone down. And some folks just don't take to kindly to that. Of course I can understand that his vast experience on the SQL Server development team puts his ideas far beyond commenting on by a poor little idiot like myself, who is far down the ladder from an experienced genius like Mr. Gert. Gert has not bothered responding to my original suggestion to improve upon the "wrapper SP" he put forth. And as you say, he does not have to respond, as he is far above the ideas put forth by a mere mortal like myself. Although I did suggest possibly trying it with an XP, I had not done so myself and qualified the remark with "MIGHT work for XP's as well." Gert was able to impart a small amount of knowledge to me on this topic, for which I will be forever grateful. I also suggested that, at the very least, marking Gert's wrapper SP a system object would make the task a little easier since you don't have to duplicate the "wrapper SP" in every single database you own. Oops: "Please don't answer[sic] try to answer questions you do not know the answer to." He further asked me if "I had written any XP's myself," so I gave him a small sampling of a few dozen recent ones. I further asked him if I could study at the feet of the master and see some of his work, as he appears to know everything and I obviously know nothing. So, Sue, I can fully accept that Gert knows 500 bazillion times more than I, you, or anyone else does about life, the universe and SQL Server extended procedures. As I said, my only purpose in life is to learn at the feet of the dbo.Master. As far as I'm concerned, Mr. Gert can respond, not respond, defend himself or not. I already know that he's far beyond the likes of a simpleton like myself, and have accepted the fact that I probably should not be using the same air and water that he uses. If someone more skilled than I could be bothered to try my experiment below, I would greatly appreciate knowing the results; as my lack of skill and knowledge obviously makes my results suspect. [quoted text, click to view] "Sue Hoegemeier" wrote:
> He really shouldn't have to defend himself. Gert has > probably the most used, well known xp that isn't shipped > with SQL Server - xp_smtp_sendmail: > http://www.sqldev.net/ > And then other Microsoft SQL Server extended stored procs he > was involved with working on as part of the development > team. You can find those in the product. And undoubtedly > many more not on his site or in the product. > Before you keep going after him, I don't think you were on > the Microsoft development team for SQL Server so he probably > does know more than you about how these need to work. Might > be a good idea for you to trust that he knows what he's > talking about. > > -Sue > > On Mon, 15 May 2006 07:01:02 -0700, Michael C > <MichaelC@discussions.microsoft.com> wrote: > > >"Gert E.R. Drapers" wrote: > > > >> No, you are incorrect; for an extended stored procedure you have to pass in > >> the database context as a parameter if you need it, that is the only thing > >> that works. Did you ever write an extended stored procedure? > > > >I have written several, several, several extended stored procedures. In > >fact, I just publicly released about 3 dozen that cover everything from AES, > >Blowfish, Twofish, DES and TripleDES encryption to regular expressions to > >recursively reading a local subdirectory listing. > > > >In fact, here's a little experiment for you extended procedure maestro: Put > >this regular stored procedure in the Master database: > > > >CREATE PROCEDURE dbo.Test1 > >AS > >SELECT db_Name() > >GO > > > >Now run it from within the Model database. Or the Northwind database. What > >database name comes up? Master, that's what. According to your solution, > >you need to recreate this exact same stored procedure in every single > >database you own in order to get the current database context out of it. > > > >As I said: changing the name to "sp_..." and marking it as a system object > >will allow you to use JUST ONE copy of the stored procedure in Master. It > >will run in the context of the CURRENT DATABASE, no matter what database you > >invoke it from. But I'm sure you're well aware of that. > > > >> Besides that it does not make sense to call the DB-Lib function dbname() > >> untill you established a loopback connection over DB-Library, which would > >> default to the default database for the user which is not the same as the > >> database context. See the attached example which shows this behavior. > > > >And that's all well and good. I was simply pointing out some things that > >might be tried, and you pointed out that it wouldn't work in your own little > >snide way. > > > >> The srv_rpc* class methods in the OPENDS60.LIB file are obsolete since they > >> are gateway calls and not longer supported; srv_rpcdb() only gave you a > >> database context when you where a remote procedure, which is something > >> different than an extended stored procedure, so that is not giving you want > >> you want either. > > > >I know srv_rpcdb doesn't work, and didn't suggest it as a solution. I'm > >sure whoever didn't know that will be happy to hear it from you, however. > > > >> So Mike C#, the ONLY solution is to pass it in as a parameter! > > > >Which is fine, and perfectly acceptable. The difference is simply this, if > >you refer back to my original post: Your method requires the same stored > >procedure be copied to all 28 of my databases. Alternatively I can put a > >single copy in the Master database and be done with it. > > > >> BTW: Next time you are calling somebody names you might want to check your > >> facts before replying an making a fool out of yourself. > > > >BTW: You should check your facts before you accuse someone of not having > >any experience in your little domain over there before making a fool of > >yourself. > > > >http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp > >http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart2.asp > >http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart3.asp
Your approach uses undocumented and unsupported methods.
Microsoft can change the behavior of sp_MS_marksystemobject at any time. Many people understandably prefer not to rely on unsupported methods for production code for this very reason. Seems to be in the best interest of the business. Coding quicker or easier is often not best for business. -Sue On Mon, 15 May 2006 08:41:03 -0700, Michael C [quoted text, click to view] <MichaelC@discussions.microsoft.com> wrote:
>Thanks for the link Sue. The link for xp_smtp_sendmail came up with a >"HTTP/1.1 404 Object Not Found" from here. > >To answer your question, Sue, no I was not on the SQL Server development >team. And no, I am not going after Gert Sue. Gert does not have to defend >his vast quantities of knowledge. I have never questioned his technical >abilities or his genius intellect. > >So far Gert has accused me of not knowing what I'm talking about, for simply >throwing out a few ideas. A simple, "no that won't work because ..." is >sufficient for most folks, but Gert does appear to want to go that extra mile >to put someone down. And some folks just don't take to kindly to that. Of >course I can understand that his vast experience on the SQL Server >development team puts his ideas far beyond commenting on by a poor little >idiot like myself, who is far down the ladder from an experienced genius like >Mr. Gert. > >Gert has not bothered responding to my original suggestion to improve upon >the "wrapper SP" he put forth. And as you say, he does not have to respond, >as he is far above the ideas put forth by a mere mortal like myself. >Although I did suggest possibly trying it with an XP, I had not done so >myself and qualified the remark with "MIGHT work for XP's as well." Gert was >able to impart a small amount of knowledge to me on this topic, for which I >will be forever grateful. I also suggested that, at the very least, marking >Gert's wrapper SP a system object would make the task a little easier since >you don't have to duplicate the "wrapper SP" in every single database you >own. Oops: "Please don't answer[sic] try to answer questions you do not >know the answer to." > >He further asked me if "I had written any XP's myself," so I gave him a >small sampling of a few dozen recent ones. I further asked him if I could >study at the feet of the master and see some of his work, as he appears to >know everything and I obviously know nothing. > >So, Sue, I can fully accept that Gert knows 500 bazillion times more than I, >you, or anyone else does about life, the universe and SQL Server extended >procedures. As I said, my only purpose in life is to learn at the feet of >the dbo.Master. > >As far as I'm concerned, Mr. Gert can respond, not respond, defend himself >or not. I already know that he's far beyond the likes of a simpleton like >myself, and have accepted the fact that I probably should not be using the >same air and water that he uses. > >If someone more skilled than I could be bothered to try my experiment below, >I would greatly appreciate knowing the results; as my lack of skill and >knowledge obviously makes my results suspect. > >"Sue Hoegemeier" wrote: > >> He really shouldn't have to defend himself. Gert has >> probably the most used, well known xp that isn't shipped >> with SQL Server - xp_smtp_sendmail: >> http://www.sqldev.net/ >> And then other Microsoft SQL Server extended stored procs he >> was involved with working on as part of the development >> team. You can find those in the product. And undoubtedly >> many more not on his site or in the product. >> Before you keep going after him, I don't think you were on >> the Microsoft development team for SQL Server so he probably >> does know more than you about how these need to work. Might >> be a good idea for you to trust that he knows what he's >> talking about. >> >> -Sue >> >> On Mon, 15 May 2006 07:01:02 -0700, Michael C >> <MichaelC@discussions.microsoft.com> wrote: >> >> >"Gert E.R. Drapers" wrote: >> > >> >> No, you are incorrect; for an extended stored procedure you have to pass in >> >> the database context as a parameter if you need it, that is the only thing >> >> that works. Did you ever write an extended stored procedure? >> > >> >I have written several, several, several extended stored procedures. In >> >fact, I just publicly released about 3 dozen that cover everything from AES, >> >Blowfish, Twofish, DES and TripleDES encryption to regular expressions to >> >recursively reading a local subdirectory listing. >> > >> >In fact, here's a little experiment for you extended procedure maestro: Put >> >this regular stored procedure in the Master database: >> > >> >CREATE PROCEDURE dbo.Test1 >> >AS >> >SELECT db_Name() >> >GO >> > >> >Now run it from within the Model database. Or the Northwind database. What >> >database name comes up? Master, that's what. According to your solution, >> >you need to recreate this exact same stored procedure in every single >> >database you own in order to get the current database context out of it. >> > >> >As I said: changing the name to "sp_..." and marking it as a system object >> >will allow you to use JUST ONE copy of the stored procedure in Master. It >> >will run in the context of the CURRENT DATABASE, no matter what database you >> >invoke it from. But I'm sure you're well aware of that. >> > >> >> Besides that it does not make sense to call the DB-Lib function dbname() >> >> untill you established a loopback connection over DB-Library, which would >> >> default to the default database for the user which is not the same as the >> >> database context. See the attached example which shows this behavior. >> > >> >And that's all well and good. I was simply pointing out some things that >> >might be tried, and you pointed out that it wouldn't work in your own little >> >snide way. >> > >> >> The srv_rpc* class methods in the OPENDS60.LIB file are obsolete since they >> >> are gateway calls and not longer supported; srv_rpcdb() only gave you a >> >> database context when you where a remote procedure, which is something >> >> different than an extended stored procedure, so that is not giving you want >> >> you want either. >> > >> >I know srv_rpcdb doesn't work, and didn't suggest it as a solution. I'm >> >sure whoever didn't know that will be happy to hear it from you, however. >> > >> >> So Mike C#, the ONLY solution is to pass it in as a parameter! >> > >> >Which is fine, and perfectly acceptable. The difference is simply this, if >> >you refer back to my original post: Your method requires the same stored >> >procedure be copied to all 28 of my databases. Alternatively I can put a >> >single copy in the Master database and be done with it. >> >
So it works, with the caveat that you don't recommend it because Microsoft
can change the behavior at any time. Of course that is approximately 1/2 of the story. We could discuss a cost-benefit analysis of maintaining one copy of a stored procedure versus umpteen of the same stored procedure spread across several databases, and we could discuss the probability (as opposed to possibility) that Microsoft will change the way system stored procedures operate on a platform-by-platform basis to bring the other 50% of the picture into view. But as long as we understand each other, I have no wish to pursue this matter further. Thank you for the independent confirmation that the method does, after all is said and done and with all the qualifiers you care to hang on it, work. [quoted text, click to view] "Sue Hoegemeier" wrote:
> Your approach uses undocumented and unsupported methods. > Microsoft can change the behavior of sp_MS_marksystemobject > at any time. Many people understandably prefer not to rely > on unsupported methods for production code for this very > reason. Seems to be in the best interest of the business. > Coding quicker or easier is often not best for business. > > -Sue > > On Mon, 15 May 2006 08:41:03 -0700, Michael C > <MichaelC@discussions.microsoft.com> wrote: > > >Thanks for the link Sue. The link for xp_smtp_sendmail came up with a > >"HTTP/1.1 404 Object Not Found" from here. > > > >To answer your question, Sue, no I was not on the SQL Server development > >team. And no, I am not going after Gert Sue. Gert does not have to defend > >his vast quantities of knowledge. I have never questioned his technical > >abilities or his genius intellect. > > > >So far Gert has accused me of not knowing what I'm talking about, for simply > >throwing out a few ideas. A simple, "no that won't work because ..." is > >sufficient for most folks, but Gert does appear to want to go that extra mile > >to put someone down. And some folks just don't take to kindly to that. Of > >course I can understand that his vast experience on the SQL Server > >development team puts his ideas far beyond commenting on by a poor little > >idiot like myself, who is far down the ladder from an experienced genius like > >Mr. Gert. > > > >Gert has not bothered responding to my original suggestion to improve upon > >the "wrapper SP" he put forth. And as you say, he does not have to respond, > >as he is far above the ideas put forth by a mere mortal like myself. > >Although I did suggest possibly trying it with an XP, I had not done so > >myself and qualified the remark with "MIGHT work for XP's as well." Gert was > >able to impart a small amount of knowledge to me on this topic, for which I > >will be forever grateful. I also suggested that, at the very least, marking > >Gert's wrapper SP a system object would make the task a little easier since > >you don't have to duplicate the "wrapper SP" in every single database you > >own. Oops: "Please don't answer[sic] try to answer questions you do not > >know the answer to." > > > >He further asked me if "I had written any XP's myself," so I gave him a > >small sampling of a few dozen recent ones. I further asked him if I could > >study at the feet of the master and see some of his work, as he appears to > >know everything and I obviously know nothing. > > > >So, Sue, I can fully accept that Gert knows 500 bazillion times more than I, > >you, or anyone else does about life, the universe and SQL Server extended > >procedures. As I said, my only purpose in life is to learn at the feet of > >the dbo.Master. > > > >As far as I'm concerned, Mr. Gert can respond, not respond, defend himself > >or not. I already know that he's far beyond the likes of a simpleton like > >myself, and have accepted the fact that I probably should not be using the > >same air and water that he uses. > > > >If someone more skilled than I could be bothered to try my experiment below, > >I would greatly appreciate knowing the results; as my lack of skill and > >knowledge obviously makes my results suspect. > > > >"Sue Hoegemeier" wrote: > > > >> He really shouldn't have to defend himself. Gert has > >> probably the most used, well known xp that isn't shipped > >> with SQL Server - xp_smtp_sendmail: > >> http://www.sqldev.net/ > >> And then other Microsoft SQL Server extended stored procs he > >> was involved with working on as part of the development > >> team. You can find those in the product. And undoubtedly > >> many more not on his site or in the product. > >> Before you keep going after him, I don't think you were on > >> the Microsoft development team for SQL Server so he probably > >> does know more than you about how these need to work. Might > >> be a good idea for you to trust that he knows what he's > >> talking about. > >> > >> -Sue > >> > >> On Mon, 15 May 2006 07:01:02 -0700, Michael C > >> <MichaelC@discussions.microsoft.com> wrote: > >> > >> >"Gert E.R. Drapers" wrote: > >> > > >> >> No, you are incorrect; for an extended stored procedure you have to pass in > >> >> the database context as a parameter if you need it, that is the only thing > >> >> that works. Did you ever write an extended stored procedure? > >> > > >> >I have written several, several, several extended stored procedures. In > >> >fact, I just publicly released about 3 dozen that cover everything from AES, > >> >Blowfish, Twofish, DES and TripleDES encryption to regular expressions to > >> >recursively reading a local subdirectory listing. > >> > > >> >In fact, here's a little experiment for you extended procedure maestro: Put > >> >this regular stored procedure in the Master database: > >> > > >> >CREATE PROCEDURE dbo.Test1 > >> >AS > >> >SELECT db_Name() > >> >GO > >> > > >> >Now run it from within the Model database. Or the Northwind database. What > >> >database name comes up? Master, that's what. According to your solution, > >> >you need to recreate this exact same stored procedure in every single > >> >database you own in order to get the current database context out of it. > >> > > >> >As I said: changing the name to "sp_..." and marking it as a system object > >> >will allow you to use JUST ONE copy of the stored procedure in Master. It > >> >will run in the context of the CURRENT DATABASE, no matter what database you > >> >invoke it from. But I'm sure you're well aware of that. > >> > > >> >> Besides that it does not make sense to call the DB-Lib function dbname() > >> >> untill you established a loopback connection over DB-Library, which would > >> >> default to the default database for the user which is not the same as the > >> >> database context. See the attached example which shows this behavior. > >> > > >> >And that's all well and good. I was simply pointing out some things that
Creating a Stored Procedure which starts with sp_ indeed does allow you to
pick up the database context, but that does not work for an Extended Stored Procedure which you name sp_, you would have to create a Stored Procedure wrapper, which does your trick and passes the database context as a parameter. The project that I attached in my previous project demonstrates that behavior for an Extended Stored Procedure which is names with sp_. GertD@SQLDev.Net [quoted text, click to view] "Michael C" <MichaelC@discussions.microsoft.com> wrote in message news:9FFE2201-128E-4B6F-B5A3-775F21AF60D0@microsoft.com... > "Gert E.R. Drapers" wrote: > >> No, you are incorrect; for an extended stored procedure you have to pass >> in >> the database context as a parameter if you need it, that is the only >> thing >> that works. Did you ever write an extended stored procedure? > > I have written several, several, several extended stored procedures. In > fact, I just publicly released about 3 dozen that cover everything from > AES, > Blowfish, Twofish, DES and TripleDES encryption to regular expressions to > recursively reading a local subdirectory listing. > > In fact, here's a little experiment for you extended procedure maestro: > Put > this regular stored procedure in the Master database: > > CREATE PROCEDURE dbo.Test1 > AS > SELECT db_Name() > GO > > Now run it from within the Model database. Or the Northwind database. > What > database name comes up? Master, that's what. According to your solution, > you need to recreate this exact same stored procedure in every single > database you own in order to get the current database context out of it. > > As I said: changing the name to "sp_..." and marking it as a system > object > will allow you to use JUST ONE copy of the stored procedure in Master. It > will run in the context of the CURRENT DATABASE, no matter what database > you > invoke it from. But I'm sure you're well aware of that. > >> Besides that it does not make sense to call the DB-Lib function dbname() >> untill you established a loopback connection over DB-Library, which would >> default to the default database for the user which is not the same as the >> database context. See the attached example which shows this behavior. > > And that's all well and good. I was simply pointing out some things that > might be tried, and you pointed out that it wouldn't work in your own > little > snide way. > >> The srv_rpc* class methods in the OPENDS60.LIB file are obsolete since >> they >> are gateway calls and not longer supported; srv_rpcdb() only gave you a >> database context when you where a remote procedure, which is something >> different than an extended stored procedure, so that is not giving you >> want >> you want either. > > I know srv_rpcdb doesn't work, and didn't suggest it as a solution. I'm > sure whoever didn't know that will be happy to hear it from you, however. > >> So Mike C#, the ONLY solution is to pass it in as a parameter! > > Which is fine, and perfectly acceptable. The difference is simply this, > if > you refer back to my original post: Your method requires the same stored > procedure be copied to all 28 of my databases. Alternatively I can put a > single copy in the Master database and be done with it. > >> BTW: Next time you are calling somebody names you might want to check >> your >> facts before replying an making a fool out of yourself. > > BTW: You should check your facts before you accuse someone of not having > any experience in your little domain over there before making a fool of > yourself. > > http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp > http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart2.asp > http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart3.asp > http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart4.asp > > Of course I'd love an opportunity to learn at the master's feet. So where > does Master Gert keep his extended procedures, that I may immerse myself > in > the knowledge to be gained?
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |