BCP in SQL Server - Passing DB Name dynamically

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

SQL Server 2008 Forums

Transact-SQL (2008)

BCP in SQL Server - Passing DB Name dynamically

Author

Topic

reddy_vam
Starting Member

43 Posts

Posted - 2012-09-06 : 18:07:21

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-06 : 19:44:40

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.

reddy_vam
Starting Member

43 Posts

Posted - 2012-09-06 : 21:44:11

I am running it from command prompt

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-07 : 07:42:35

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

reddy_vam
Starting Member

43 Posts

Posted - 2012-09-07 : 10:21:17

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-07 : 10:36:39

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

reddy_vam
Starting Member

43 Posts

Posted - 2012-09-07 : 11:18:00

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-07 : 11:32:07

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%

reddy_vam
Starting Member

43 Posts

Posted - 2012-09-26 : 12:39:00

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
)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-26 : 13:25:44

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?

reddy_vam
Starting Member

43 Posts

Posted - 2012-09-26 : 14:55:21

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
)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-26 : 15:55:07

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.

reddy_vam
Starting Member

43 Posts

Posted - 2012-09-26 : 18:10:17

Perfect..That one worked

Thanks Sunita

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources