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.
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 intset @InitialDatabaseID = 1 declare @DatabaseID intset @DatabaseID = db_id()while @DatabaseID <> @InitialDatabaseIDBegin set @InitialDatabaseID = @InitialDatabaseID + 1Enddeclare @DatabaseName varchar(20)Set @DatabaseName = DB_Name(@InitialDatabaseID)--Print @DatabaseNameexec 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 intset @InitialDatabaseID = 1declare @DatabaseID intset @DatabaseID = db_id()while @DatabaseID <> @InitialDatabaseIDBeginset @InitialDatabaseID = @InitialDatabaseID + 1declare @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, 'EndJimEveryday 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. |
|
|
|
|
|