SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 BCP in SQL Server - Passing DB Name dynamically
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

reddy_vam
Starting Member

43 Posts

Posted - 09/06/2012 :  18:07:21  Show Profile  Reply with Quote
How can I pass the Database Name dynamically in the below BCP Statement.

I would like to create one bat file and add the below BCP statement to that file and copy that bat file onto nearly 20 production servers. On each server we have 2 databases and their names ends with DWH & ETL but prefix vary on each server. So is there any way to modify the script to pass the dynamic database names ?

bcp "select T1.* from @DWH.dbo.table1 (nolock) as T1 join @ETL.dbo.Table2 (nolock) as T2 on T2.PersonID = T1.PersonID where T1.DateTime >=getdate()-1 " queryout "H:\Log\Table1.txt" -t"|" /T -c

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/06/2012 :  19:44:40  Show Profile  Reply with Quote
Are you running this from T-SQL using xp_cmdshell, or are you running it from the OS in a command window or Powershell window?

If you are running within T-SQL, you can use dynamic SQL to construct the sql statement and then use EXEC or sp_executesql.

If you are running from cmd line or Powershell, you can look up the environment variable (gc env:computername or %COMPUTERNAME%) to get the computer name and then construct the bcp statement.
Go to Top of Page

reddy_vam
Starting Member

43 Posts

Posted - 09/06/2012 :  21:44:11  Show Profile  Reply with Quote
I am running it from command prompt
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/07/2012 :  07:42:35  Show Profile  Reply with Quote
Assuming your DWH and ETL databases were named YOURCOMPUTERNAME_DWH and YOURCOMPUTERNAME_ETL, then you could run a command from the command prompt like this:
bcp "select T1.* from %COMPUTERNAME%_DWH.dbo.table1 (nolock) as T1 join %COMPUTERNAME%_ETL.dbo.Table2 (nolock) as T2 on T2.PersonID = T1.PersonID where T1.DateTime >=getdate()-1 " queryout "H:\Log\Table1.txt" -t"|" /T -c
Go to Top of Page

reddy_vam
Starting Member

43 Posts

Posted - 09/07/2012 :  10:21:17  Show Profile  Reply with Quote
Thanks for your reply. Here is the error message I got when I ran BCP statement from command window

C:\>bcp "select T1.* from %COMPUTERNAME%_DWH.dbo.table1 (nolock) as T1 where DateTime >=getdate()-1 order by DateTime DESC " queryout "H:\mssql\T1.txt" -t"|" /T -c
SQLState = 37000, NativeError = 102
Error = [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '%'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prep
ared.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/07/2012 :  10:36:39  Show Profile  Reply with Quote
Is your table name really the hostname followed by an underscore followed by the string DWH? In my previous posting, I just made that up. Run this command and take a look at the output - that is the command that will be sent to the SQL Server:
echo bcp "select T1.* from %COMPUTERNAME%_DWH.dbo.table1 (nolock) as T1 where DateTime >=getdate()-1 order by DateTime DESC " queryout "H:\mssql\T1.txt" -t"|" /T -c


Also, to be on the safe side, try using the square bracket escape characters - i.e.,
bcp "select T1.* from [%COMPUTERNAME%_DWH].dbo.table1 (nolock) as T1 where DateTime >=getdate()-1 order by DateTime DESC " queryout "H:\mssql\T1.txt" -t"|" /T -c
Go to Top of Page

reddy_vam
Starting Member

43 Posts

Posted - 09/07/2012 :  11:18:00  Show Profile  Reply with Quote
In our test environment Database Names are like Test1_DWH,Test2_DWH…

Here is my first statement with the exact database name


C:\>bcp "select * from Test1_DWH.dbo.Agent (nolock) where DateTime >=getdate()-1 order by DateTime DESC " queryout "H:\mssql\Agent.txt" -t"|" /T -c

Starting copy...

58 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 16 Average : (3625.00 rows per sec.)

Below were the 2 statements I tried with ‘%’ in the database name

C:\>bcp "select * from [%_DWH].dbo.Agent (nolock) where DateTime >=getdate()-1 order by DateTime DESC " queryout "H:\mssql\Agent.txt" -t"|" /T -c
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name '%_DWH.dbo.Agent'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.

C:\>bcp "select * from %_DWH.dbo.Agent (nolock) where DateTime >=getdate()-1 order by DateTime DESC " queryout "H:\mssql\Agent.txt" -t"|" /T -c
SQLState = 37000, NativeError = 102
Error = [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '%'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prep
ared.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/07/2012 :  11:32:07  Show Profile  Reply with Quote
If the database names are prefixed with "Test1" in your test environment and the database names are something different in other environments, what determines the prefix?

From your initial posting, I assumed that the prefix (in this example Test1) is the name of the database server in your test environment.
If that is not the case, what rule do you use to determine the name of the database on each server?

Assuming the server name is Test1 and assuming you are running the BCP command from a command window on the server, the syntax should be
C:\>bcp "select * from [%COMPUTERNAME%_DWH].dbo.Agent (nolock) where ....
Use that exact string - i.e., %COMPUTERNAME%

Edited by - sunitabeck on 09/07/2012 11:32:43
Go to Top of Page

reddy_vam
Starting Member

43 Posts

Posted - 09/26/2012 :  12:39:00  Show Profile  Reply with Quote
Sorry for the late reply.

Not sure what mistake I am doing but below one is not working as expected, it is still looking at the loop part even though the condition not matching up , any idea ?

@echo off

IF %COMPUTERNAME%=="SERVER1" GOTO SERVER1

:SERVER1
(
IF EXIST D:\Temp1\Customer GOTO END
MD D:\Temp1\Customer
IF EXIST D:\Temp1\Path GOTO END
MD D:\Temp1\Path

:END
bcp "select * from db1.dbo.Customer where DateTime >=getdate()-10 " queryout "D:\Temp1\Customer\Customer.txt" -t"|" /T -c
bcp "select * from db2.dbo.path where DateTime >=getdate()-10 " queryout "D:\Temp1\Path\path.txt" -t"|" /T -c
)
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/26/2012 :  13:25:44  Show Profile  Reply with Quote
The logic seems like it will go the very next line whether IF %COMPUTERNAME%=="SERVER1" evaluates to true or false. If it evaluates to false, what do you want it to do?
Go to Top of Page

reddy_vam
Starting Member

43 Posts

Posted - 09/26/2012 :  14:55:21  Show Profile  Reply with Quote
Here is the else part. If the above condition false then it needs to evaluates the below condition


IF %COMPUTERNAME%=="SERVER2" GOTO SERVER2

:SERVER2
(
IF EXIST D:\Temp1\governance GOTO END
MD D:\Temp1\governance
IF EXIST D:\Temp1\service GOTO END
MD D:\Temp1\service

:END
bcp "select * from db1.dbo.governance where DateTime >=getdate()-10 " queryout "D:\Temp1\governance\governance.txt" -t"|" /T -c
bcp "select * from db2.dbo.service where DateTime >=getdate()-10 " queryout "D:\Temp1\service\service.txt" -t"|" /T -c
)

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/26/2012 :  15:55:07  Show Profile  Reply with Quote
I have only passing familiarity with DOS commands, so if what I am posting below is not what you are trying to do, please ignore. It seems to me that you need something like shown below (where I have replaced all the commands with a prefixed "echo" for testing).
IF %COMPUTERNAME%==SERVER1 GOTO SERVER1
GOTO NEXT1

:SERVER1
(
	IF NOT EXIST D:\Temp1\governance (echo MD D:\Temp1\governance)
	IF NOT EXIST D:\Temp1\service (echo MD D:\Temp1\service)
	echo bcp "select * from db1.dbo.governance where DateTime >=getdate()-10 " queryout "D:\Temp1\governance\governance.txt" -t"|" /T -c
	echo bcp "select * from db2.dbo.service where DateTime >=getdate()-10 " queryout "D:\Temp1\service\service.txt" -t"|" /T -c
)

:NEXT1
rem do your other stuff here.
Go to Top of Page

reddy_vam
Starting Member

43 Posts

Posted - 09/26/2012 :  18:10:17  Show Profile  Reply with Quote
Perfect..That one worked

Thanks Sunita
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000