Stored Procedures - 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

General SQL Server Forums

New to SQL Server Programming

Stored Procedures

Author

Topic

abuhassan

105 Posts

Posted - 2006-09-07 : 08:39:19

Hi

I am having problem in writing and executing stored procdure the procedure is to be used to upload information for a .txt file to the database table i have written the following procedure but it doesnt seem to work ..

the .txt file contain 4 colunms that are comma deliminated and the table has four colunms.


CREATE PROCEDURE spLoadHistory @path char(300) AS 

BULK INSERT Csv..VW_CsvLoad FROM @path
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '
')

go

it seem to have a problem in getting the @path?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-07 : 08:41:56

What kind of problem you are facing? Did you keep the .txt file on the machine where SQL Server resides (not client)? Do you have permissions for this operation?

Harsh Athalye
India.
"Nothing is Impossible"

abuhassan

105 Posts

Posted - 2006-09-07 : 08:45:40

Hi

I have two partitions sql server is installed on c:\ and the .txt file is on the D:
I have full admin rights

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-07 : 08:53:20

you might have to use dynamic sql to build your bulk insert statement. I think your command is missing single quotes around the @path. the single quotes are required for the bulk insert statement.

EDIT:
or you could try adding single quotes to the beginning and end of the @path variable using concatenation.

-ec

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-07 : 08:53:22

you haven't specified ROWTERMINATOR properly, it should be '\n' if you are using newline character as rowterminator (or don't specify that clause at all, as it is default)

Harsh Athalye
India.
"Nothing is Impossible"

abuhassan

105 Posts

Posted - 2006-09-07 : 09:42:47

hi

when i added the '@path' i still get an error that it cant find the path...

about the ROWTERMINATOR i have a rectangle symbol which is char(13) there, the bulk insert works but when i put it in the stored procedure it can seem to underatnd the @path.
if you look to my code

ROWTERMINATOR = '
')

that why it has put the ') on the next line..

the bulk insert statement as it is worked whe ive tested it.

BULK INSERT Csv..VW_CsvLoad FROM 'd:\test.txt'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '
')

My question is how can i put the same statement in a stored procedure to execute where i can provide a path from where to get the data from?

abuhassan

105 Posts

Posted - 2006-09-07 : 10:39:50

hi i have changed the code so that you can actually see the terminator but i still cant get the stored procedure to work.

BULK INSERT Csv..VW_CsvLoad FROM 'd:\test.txt'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '+CHAR(10)+')

Can any one help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-07 : 10:44:58

ROWTERMINATOR = '\n' )

Peter Larsson
Helsingborg, Sweden

abuhassan

105 Posts

Posted - 2006-09-07 : 10:50:46

Hi the bulk insert statement doesnt have any problems its when i try to insert it into an stored procedure?

CREATE PROCEDURE spLoadHistory @path char(300) AS

BULK INSERT Csv..VW_CsvLoad FROM '@path'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')

go

can any one help?

abuhassan

105 Posts

Posted - 2006-09-07 : 10:53:17

Hi

could it be possible that my approach writing stored procedures is not correct?

CREATE PROCEDURE spLoadHistory @path char(300) AS

BULK INSERT Csv..VW_CsvLoad FROM '@path'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')

go

are the query statements written differently in stored procedures than in a general query analyser for example do i need to change my bulk insert statement syntax etc to make it execute in stored procedure? can any one help?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-07 : 11:22:27

quote:
Originally posted by abuhassan

Hi

could it be possible that my approach writing stored procedures is not correct?

CREATE PROCEDURE spLoadHistory @path char(300) AS

BULK INSERT Csv..VW_CsvLoad FROM '@path'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')

go

are the query statements written differently in stored procedures than in a general query analyser for example do i need to change my bulk insert statement syntax etc to make it execute in stored procedure? can any one help?

Why are you putting quotes around @path? Try this...

CREATE PROCEDURE spLoadHistory @path char(300) AS

BULK INSERT Csv..VW_CsvLoad FROM @path
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')

go

If that doesn't work, use dynamic sql as below:

CREATE PROCEDURE spLoadHistory @path char(300) AS 

exec('BULK INSERT Csv..VW_CsvLoad FROM ''' + @path + '''
WITH (FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n'')')

go

Harsh Athalye
India.
"Nothing is Impossible"

abuhassan

105 Posts

Posted - 2006-09-07 : 11:29:53

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources