CODEWITHSUNDEEP
sql-servert-sql
Graviton
Graviton

Reputation: 83244

Relative path in t sql?

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.

Upvotes: 11

Views: 22556

Answers (7)

water
water

Reputation: 53

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.

Upvotes: 1

mateuscb
mateuscb

Reputation: 10710

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

Upvotes: 14

Kelly Davis
Kelly Davis

Reputation: 364

well it's not a Microsoft thing first off... it's an industry standard thing. second your solution for running T-SQL with a relative path is to use a batch script or something to inject your path statement IE:

@echo OFF
SETLOCAL DisableDelayedExpansion
FOR /F "usebackq delims=" %%a in (`"findstr /n ^^ t-SQL.SQL"`) do (
    set "var=%%a"
    SETLOCAL EnableDelayedExpansion
    set "var=!var:*:=!"
    set RunLocation=%~dp0
    echo(%~dp0!var! > newsql.sql
   ENDLOCAL
)
 sqlcmd newsql.sql

or something like that anyway

Upvotes: 0

bernd_k
bernd_k

Reputation: 11966

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.

Upvotes: 1

GilM
GilM

Reputation: 3761

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.

Upvotes: 5

Tadmas
Tadmas

Reputation: 6358

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.

Upvotes: 4

Corey Trager
Corey Trager

Reputation: 23123

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

Upvotes: 6

Related Questions