Relative path in t sql?

Question

How to get the relative path in t sql? Take for example a .sql file is located in the folder D:\temp, I want to get path of the file hello.txt in the folder D:\temp\App_Data. How to use the relative path reference?

Let's say I am executing the sql file inside the SQL server management studio.

Answer 1

I had a similiar problem, and solved it using sqlcmd variables in conjunction with the %CD% pseudo-variable. Took a bit of trial and error to combine all the pieces. But eventually got it all working. This example expects the script.sql file to be in the same directory as the runscript.bat.

runscript.bat

sqlcmd -S .\SQLINSTANCE -v FullScriptDir="%CD%" -i script.sql -b

script.sql

BULK INSERT [dbo].[ValuesFromCSV]
FROM '$(FullScriptDir)\values.csv'
with
(
    fieldterminator = ',',
    rowterminator = '\n'
)
go

Answer 2

The .sql file is just.... a file. It doesn't have any sense of its own location. It's the thing that excutes it (which you didn't specify) that would have a sense of its location, the file's location.

I notice that you mentioned an App_Data folder, so I guess that ASP.NET is involved. If you want to use relative paths in your web app, see MapPath

http://msdn.microsoft.com/en-us/library/system.web.httpserverutility.mappath.aspx

Answer 3

The server is executing the t-sql. It doesn't know where the client loaded the file from. You'll have to have the path embedded within the script.

DECLARE @RelDir varchar(1000)
SET @RelDir = 'D:\temp\'
...

Perhaps you can programmatically place the path into the SET command within the .sql script file, or perhaps you can use sqlcmd and pass the relative directory in as a variable.

Answer 4

When T-SQL is executing, it is running in a batch on the server, not on the client machine running Management Studio (or any other SQL client). The client just sends the text contents of the .sql file to the server to be executed. So, unless that file is located on the database server, I highly doubt you're going to be able to interact with it from a SQL script.

Answer 5

The t-sql script is first preprocessed by QueryAnalyzer, SSMS or sqlcmd on the client side. These programs are aware of the file localcation and could easily handle relative pathes similar To Oeacle sqlplus.

Obviously this is just a design decision from Microsoft and I dare say a rather stupid one.

Answer 6

I tried method from mateuscb's comments. I found it can not work ,i do not know why,then I managed after several test. It can work with the script below:

runscript.bat

@set FullScriptDir=%CD%
sqlcmd -S .\SQLINSTANCE  -i script.sql

script.sql

BULK INSERT [dbo].[ValuesFromCSV]
FROM '$(FullScriptDir)\values.csv'
with
(
    fieldterminator = ',',
    rowterminator = '\n'
)
go

Just for your information for further discussion.