BCP command – Relative path in bcp command | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

BCP command – Relative path in bcp command

Hello,
I have a bcp command that loads data from a flat file into a table. Currently I’m using the following bcp command for it:
Code:
bcp test.dbo.table1 in "C:\projects\Data_FlatFile.txt" -f "C:\projects\Data_FormatFile.xml" -k -T -Uuser12 -Suser12\TESTINSTANCE 
But I have a requirement to replace the hard coded path to relative path, so that this command works for anybody. Example:
Say User1 drops the flat file and format file in C:\Data\ folder instead of C:\projects\ folder. So when User1 executes the above bcp comand, it will not find the flat file and the format file in that location. So how should I modify this bcp command in such a way that it takes the relative path and looks for the flat file and format in the location from where the bcp command is run from. Thanks!
I’m executing the bcp command from within xp_cmdshell.
The reason why I’m using the xp_cmdshell command is because I want to include bcp command in T-SQL editor. To do this, is xp_cmdshell the only way?
Is there a better/easy way to execute bcp command from within T-SQL editor, Thanks!
I’m not sure I would want to see "normal" users dealing with format files and using bcp at all. I would rather want them to transfer the files to a central location from where it is uploaded or create a small client tool using SqlBulkCopy that deals with that uploads. Anyway, see if this helps: http://www.dbforums.com/microsoft-sql-server/1199652-dynamic-file-name-bulk-insert.html
Code:
use test
go
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
go
EXEC master..xp_cmdshell 'bcp test.dbo.table1 in "Data_FlatFile.txt" -f "Data_FormatFile.xml" -k -T -Uuser12 -Suser12\TESTINSTANCE'
GO
use test
go
sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO
I’m executing the bcp command from within xp_cmdshell.
So when I run the bcp as above (i.e, without specifying the path but with just the file name), it is not working….. Meaning loading the data from flatfile into the table. More Information on what I’m doing….
I’m building a DB project in Visual studio 2010 and have included the bcp command within xp_cmdshell in Post deployment script in that DB project (the whole black of code mentioned below).
So once the database and the tables are created by the db project, the post deployment script kicks off to load the data from flat file into the table. I will have to provide the db proj to other people, and the flat file and the format file will have to be delivered to them as a part of the same Visual Studio 2010 Database Project Post Deployment folder.
So when I sent away the DB project folder, the script in the post deployment folder would also go with it.
I just did a copy paste to put the files in VS2010 DB Project’s Post Deployment folder -> and then opened the post deployment script within DB project and pasted the following (Also mentioned above) So the Flat file and the format file would be already be there when somebody opens the database project file (no matter from which location).
Do you get the idea now? Now, the example shown in the link takes the file path and the file name.
In my case, the location of the file may difference from person to person depending on here they choose to save the database project.
So say I have the database project in D:\DBProj and the Post deployment script would go in D:\DBProj\Post-Deployment.
But another person may choose to save the same DB project that I deliver in a different location, say E:\DBProj and the post deployment script would go in E:\DBProj\Post-Deployment\.
So this is the reason I cannot hardcode the file path or specific the file path within the DB Project’s post deployment script. Is there a different method or some sort of enhancement that I can do to get this working? Thanks!
Another thing I can do is to get the current post deployment script directory (eg: E:\DBProj\Post_Deployment\ ) and use the same path for the flat file and format file.
But how do I get the current physical directory from xp_cmdshell or using something else?
Say I have a editor open which is saved to E:\DBProj\TestEditor.sql.
How do I get the path information? Thanks!
Ok, I understand. xp_cmdshell path specifications are always relative to the location where SQL Server is installed. So, when you specify "D:\DBProj\Data_FlatFile.txt" for example, the procedure tries to load the file from the D: drive of the SQL Server, which may or may not exist. I’m not familiar with VS, but I got told by my C# developers that you should be able to define somewhere in the deployment project some sort of post-deployment actions and in those you can use variables that determine the current directory for you, go to the subfolder where the sql file resides and run this. I would go for SQLCMD because this works with workstation file location rather than server ones. Another reason to switch could be, that you can not rely on the fact that xp_cmdshell access will be allowed on the instance to which you install your database. A lot of DBAs tend to disable it for security reasons.
Thanks FrankKalis. Good to finally hear that there is a way to get this information. How do I identify this using SQLCMD?
Thanks!
I finally figured out where to set the environment variable (Project -> properties ->Deploy ->Sql Command Variable file ->Edit ->Here gave Variable Name as ProjDirpath and Variable Value as ‘D:\DBProj\’. Then I created the environment variable from Computer -> Properties -> Advanced System Settings -> Environment Variables -> NEW -> specified the same variable name and variable value. But now the problem is, it is again harcoding the path.
So how/what should I specify in the sqlcmdvars to get the current working/solution directory path? Thanks!
What I tend to do is something like this: Have a batch file named backup_check.cmd
Code:
@ECHO Off
SETLOCAL EnableDelayedExpansion
SET THESERVER=%1
SET CURRENTDIRECTORY=%CD%
SET THESUBDIRECTORY=%CURRENTDIRECTORY%\SQLCMD-Scripts\Backup-Check.sql
SQLCMD -S "%1" -b -i %THESUBDIRECTORY% -v dbname = "%2"
and call it like this:
Code:
backup_check.cmd SomeServer\SomeInstance SomeDatabaseName
The "only" thing that I don’t know is how to make sure the cmd file is called from the installation directory, but I suspect this must be doable from within Visual Studio.
Oops, got distracted and didn’t realise you have posted another reply in the meantime. My answer was targeted at your second but last post. To be honest, I have no clue how to deal with sqlcmdvars in Visual Studio, because I don’t use VS.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |