Text File to SQL statement - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Text File to SQL statement

Author

Topic

melody_ph
Starting Member

11 Posts

Posted - 2007-08-30 : 03:57:24

Gud day! I hope someone could help me about this or direct me to what I should research about.

My task is to execute a valid SQL statement contained in a text file. Tried this code:

SET QUOTED_IDENTIFIER OFF
go

DECLARE @PathFileName AS VARCHAR(2000)
SET @PathFileName = 'D:\TEST.TXT'

CREATE TABLE #tblTemp(fldStatement varchar(2000))

DECLARE @SQL varchar(2000)
SET @SQL = "BULK INSERT #tblTemp FROM '"+@PathFileName+"' "

EXEC (@SQL)

DROP TABLE #tblTemp

but got this error msg: "Could not bulk insert because file 'D:\TEST.TXT' could not be opened. Operating system error code 3(The system cannot find the path specified.)."

I could guarantee that the file is in the correct path.And even i get to save the contents of the text file to a temp table how am I supposed to execute it? Any help or direction would be greatly appreciated. Thanks.

-=la lang=-

Kristen
Test

22859 Posts

Posted - 2007-08-30 : 04:01:08

Is it D: from SQL Server's perspective? (rather than from your PC's perspective)

Kristen

Kristen
Test

22859 Posts

Posted - 2007-08-30 : 04:02:43

You could try getting a Directory Listing from SQL Server:

EXEC master.dbo.xp_cmdshell 'DIR x:\Mypath\*.*'

Kristen

melody_ph
Starting Member

11 Posts

Posted - 2007-08-30 : 04:08:01

Did this: EXEC master.dbo.xp_cmdshell 'DIR x:\Mypath\*.*'

output:
The system cannot find the path specified.
NULL

where should i save the files im trying to execute? sorry im sort of lost...

Kristen
Test

22859 Posts

Posted - 2007-08-30 : 04:54:40

Changing "x" and "mypath" would help ...

e.g. what does this give?

EXEC master.dbo.xp_cmdshell 'DIR D:\TEST.TXT'

Kristen

sbalaji
Starting Member

48 Posts

Posted - 2007-08-30 : 04:56:46

U should try like this
EXEC master.dbo.xp_cmdshell 'DIR D:\*.*'

r u getting the test.txt file in the list of output??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 05:44:24

[code]SET QUOTED_IDENTIFIER OFF
go

DECLARE @PathFileName AS VARCHAR(2000)
SET @PathFileName = '\\<your machine unc-name here>\D$\TEST.TXT'

CREATE TABLE #tblTemp(fldStatement varchar(2000))

DECLARE @SQL varchar(2000)
SET @SQL = "BULK INSERT #tblTemp FROM '"+@PathFileName+"' "

EXEC (@SQL)

DROP TABLE #tblTemp[/code]

E 12°55'05.25"
N 56°04'39.16"

melody_ph
Starting Member

11 Posts

Posted - 2007-08-30 : 21:22:22

when i executed EXEC master.dbo.xp_cmdshell 'DIR D:\*.*' , Text.txt was not one of the results and the output files were different from the files i see when i explore D:\ . I am currently working in a terminal server. How could i know the exact location of the file?

Peso, what do you mean by my machine's "unc-name"?

thanks so much!

rcurrey
Starting Member

30 Posts

Posted - 2007-08-31 : 16:11:10

Melody,

When you run a query or execute a query in SQL Server, all paths are relative to the machine where the database resides. In other words, it is showing you the "D:" drive of the server where SQL is running. To get to your drive, you need to know the name of YOUR computer on the network, and that is then your UNC name.

Hope this helps

Thanks,
Rich

melody_ph
Starting Member

11 Posts

Posted - 2007-09-04 : 23:05:39

Thanks guy for helping me out!

Final script for my problem goes:

SET QUOTED_IDENTIFIER OFF
go

DECLARE @PathFileName AS VARCHAR(2000)
SET @PathFileName = '\\172.21.41.18\TEST.TXT'

CREATE TABLE #tblTemp(fldStatement varchar(2000))

DECLARE @SQL varchar(2000)
DECLARE @SCRIPT varchar(2000)

SET @SQL = "BULK INSERT #tblTemp FROM '"+@PathFileName+"' WITH (ROWTERMINATOR = '\0') "
EXEC (@SQL)

SET @SCRIPT = (SELECT FLDSTATEMENT FROM #TBLTEMP)
EXEC (@SCRIPT)

DROP TABLE #tblTemp

Kristen
Test

22859 Posts

Posted - 2007-09-05 : 03:39:50

Glad you got it sorted.

Kristen

timark
Starting Member

11 Posts

Posted - 2007-12-19 : 15:22:56

This is all great! I have a similar requirement - but I'd like to be able to choose the file via standard windows file open dialog. Anybody know how to call standard dialogs and retrieve values from them???

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-12-19 : 15:42:54

You'd have to create an application of some sort, web-app, vb, access...something that has an end user interface. Then upload the file to a server where the sql-server can access it and do the rest from there...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources