Exporting Database Name to Text File

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Exporting Database Name to Text File

Author

Topic

cant2ny
Starting Member

20 Posts

Posted - 2009-10-08 : 09:35:33

Hi, I'm hoping that someone can help with an issue that I can't think of a way to resolve. I'm trying to export the active database name to a text file for use in another program. I have confirmed that I have the proper permissions to use the xp_cmdshell as another part of this code is working that exports more data to a text file. Any help would be greatly appreciated.

I have 2 thoughts on this but neither one seems to be working correctly with the xp_cmdshell. The first script that I wrote was:

exec master.dbo.xp_cmdshell 'bcp "Select DB_Name()" queryout "C:\Documents and Settings\UserX\My Documents\Database.txt" -T -c -t, '

However, this just exported the name "Master", I'm assuming because xp_cmdshell is a master db SP. Using the -S switch did not help either.

The other script that I wrote that is working, but I'm generating an error message when using the XP_cmdshell that states my @DatabaseName variable needs to be declared. If I use a Print @Database without the XP_cmdshell it works fine.

declare @InitialDatabaseID int
set @InitialDatabaseID = 1

declare @DatabaseID int
set @DatabaseID = db_id()

while @DatabaseID <> @InitialDatabaseID
Begin
set @InitialDatabaseID = @InitialDatabaseID + 1
End

declare @DatabaseName varchar(20)
Set @DatabaseName = DB_Name(@InitialDatabaseID)
--Print @DatabaseName

exec master.dbo.xp_cmdshell 'bcp "@DatabaseName" queryout "C:\Documents and Settings\canthony\My Documents\Bg_Info_Text\NG55_77_Database.txt" -T -c -t, '

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-08 : 09:54:59

You just need to move your exec statement into the loop, otherwise it will just have whatever the last dbname was

declare @InitialDatabaseID int
set @InitialDatabaseID = 1

declare @DatabaseID int
set @DatabaseID = db_id()

while @DatabaseID <> @InitialDatabaseID
Begin
set @InitialDatabaseID = @InitialDatabaseID + 1
declare @DatabaseName varchar(20)
Set @DatabaseName = DB_Name(@InitialDatabaseID)
exec master.dbo.xp_cmdshell 'bcp "@DatabaseName" queryout "C:\Documents and Settings\canthony\My Documents\Bg_Info_Text\NG55_77_Database.txt" -T -c -t, '
End

Jim

Everyday I learn something that somebody else already knew

cant2ny
Starting Member

20 Posts

Posted - 2009-10-08 : 10:00:33

Thanks Jim - unfortunately, I'm still getting the
"Must declare the scalar variable "@DatabaseName"" error message.

-I've just confirmed that it's something that's happening from within the xp_cmdshell since anyway I set it up with a Print statement, it works fine. Ass soon as the xp_cmdshell attempts to execute it generates the above error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources