I'm executing a Stored Procedure (which brings back a result set) via the BCP command and then saving the result set from the Stored Procedure to file using the QueryOut command. When using BCP i have to use the 2 dot method to execute a stored procedure. For instance, FooBar.dbo.SP_SelectFoo will work, but dbo.SP_SelectFoo will not work. Nor will SP_SelectFoo.
This becomes a major hassle when the DB is copied and moved to a new server and for whatever reason it's name changes (We support multiple clients on the same application so quite often the DB is given some form of the clients name). Each time it moves, I have to remember to go in and update the call to the BCP commmand with the new DB name
My BCP command is dynamic sql. So I figured i could set the name of the current DB as a variable, add it to my dynamic sql string and be good to go. this doesn't seem to work.
Here's my code before and after I tried adding the database as a variable:
select @sql = 'bcp "exec Foo.dbo.ExportIndividualImportValidation ''' + @ImportID + '''" queryout ' + @Path + @ImportID + 'Validation.csv -c -t, -T -S'+ @@servername
Declare @DataBase varchar(100)
Set @Database = (Select db_name())
select @sql = 'bcp "exec ''' + @DataBase + '''.dbo.ExportIndividualImportValidation ''' + @ImportID + '''" queryout ' + @Path + @ImportID + 'Validation.csv -c -t, -T -S'+ @@servername
Is there something i'm doing wrong? Is there another solution to my problem that i'm not aware of.