USE DATABASE NAME IN SP 2

[kissarmi]

Hi,

Is there any way to gain access to the name of the database that a stored procedure is in? In other words, can I put the database name in a variable within a stored procedure? I would like to store the database name in a table.

Thanks for any help.
Mitch C.

 

[mikeyb540]

If you have access to the Master it is easy

The database names are in master.sysdatabases.name
use master
go
select name from sysdatabases

 

[kissarmi]

At the time the stored procedure is run, I won't know which database I'm working in. How do I choose the database that the stored procedure is in? There are numerous database names in the sysdatabases table.

 

[mikeyb540]

Hmm... how can you run a SP if you dont know the database?
If a DB doesnt have the SP you'll get "Could not find stored procedure".

Are you trying to find the SP in multiple databases?

Do you have the same SP on multiple databases?

Tell me what you are trying to accomplish

 

[kissarmi]

OK, I wasn't very clear. I'm running the stored procedures from a Visual Basic App. I use several databases that are set up identical. They are named YEAR2004, YEAR2005, YEAR2006 ..., in order to keep multiple years of information. The stored procedures in each database are also identical. The VB App uses the database that is chosen when I log into the application. So, I guess what I'm asking, is there a way to dynamically get the name of the database?

 

[mikeyb540]

When you say chosen is it default from the DSN?
If it's defaulted then you have to create a VB login form that has Login, Password, and DBname.

The only way I can think of is by creating a combo box that has all your database names.
Combo1.AddItem "YEAR2004"
Combo1.AddItem "Year2005"

Check all your DBnames from:
Select Name from sysdatabases

On your connection string change the parameters as follows:
Dim strDBname as string
Dim strLogin as string
strLogin = Text1.text
strDBname = Combo1.text
"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & strLogin & ";Initial Catalog=" & strDBName& ";Data Source=SERVERNAME"

After some thinking this is what I came up with.

I hope it makes sense.

Let me know

 

[kissarmi]

I do login using a login form with Login, Password, and DBname. I am trying to avoid sending another parameter to the SP's because it would involve a lot of changes. I need the SP's to write the database name to a table, if there's any way to aquire it withing the SP. The database name needs to be in the table so I can print it on certain reports. Since the databases are identical, this is necessary so the users can be sure they are printing and looking at the right report, not one they printed from a different database (realize that I am dealing with government employees here).
Hope you're not totally confused.

 

[mrdenny]

Is this what your looking for?

declare @DBName varchar(50)
set @DBName = (select db_name())

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[kissarmi]

That's EXACTLY what I'm looking for.

Thanks.
Mitch

 

[mrdenny]

no problem.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[mikeyb540]

Aha... I got puzzled with this one Denny...

I might use this also.

I give you a Star

 

[mrdenny]

thanks


Denny

--Anything is possible. All it takes is a little research. (Me)