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

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
>

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


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

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

DBLIB, dbname() function.
http://msdn.microsoft.com/library/de...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, 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/de...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.

"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/de...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.


"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/de...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
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

"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/colu...olkitpart1.asp
http://www.sqlservercentral.com/colu...olkitpart2.asp
http://www.sqlservercentral.com/colu...olkitpart3.asp
http://www.sqlservercentral.com/colu...olkitpart4.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?