SQL Server Performance

BCP command - Relative path in bcp command

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by anonymous2009, Nov 14, 2011.

  1. anonymous2009 New Member

    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!
  2. anonymous2009 New Member

    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!
  3. FrankKalis Moderator

  4. anonymous2009 New Member

    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!
  5. anonymous2009 New Member

    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!
  6. FrankKalis Moderator

    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.
  7. anonymous2009 New Member

    Thanks FrankKalis. Good to finally hear that there is a way to get this information.

    How do I identify this using SQLCMD?
    Thanks!
  8. anonymous2009 New Member

    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!
  9. FrankKalis Moderator

    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.
  10. FrankKalis Moderator

    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.

Share This Page