stored procedure - 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 2008 Forums

Transact-SQL (2008)

stored procedure

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-04-14 : 22:41:14

I have run this stored procedure successfully in the query analyzer window. Now I have moved it into the stored procedure window and it no longer works.

Here is the procedure:

USE [Hospital_Compare];
GO
/****** Object: StoredProcedure import csv to HQI_FTNT
Script Date: 04/12/2009 JCB******/
BULK INSERT dbo.HQI_FTNT FROM 'C:\Users\John & Tanya\Documents\John\Hospital Compare\Hospital_flatfiles\dbo_vwHQI_FTNT.csv'

WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);
GO

This is the error I am getting:

Msg 4861, Level 16, State 1, Line 3
Cannot bulk load because the file "C:\Users\John & Tanya\Documents\John\Hospital Compare Hospital_flatfiles\dbo_vwHQI_FTNT.csv" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).

Does anyone know what this error is? Are there issues with SQL 2008 and Vista?

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-04-15 : 02:00:14

Try this

You should have Admin permissions on Required Folder also

--STEP 1

CREATE PROCEDURE InsertCSVFile

AS
BEGIN

DECLARE @FileName NVARCHAR (100)
,@FilePath NVARCHAR (250)
,@Command NVARCHAR(2500)

SET @FilePath = 'C:\Users\John & Tanya\Documents\John\Hospital Compare\Hospital_flatfiles\'
SET @FileName = 'dbo_vwHQI_FTNT.csv'

SET @Command = 'BULK INSERT dbo.HQI_FTNT FROM "'+@FilePath + @FileName + '" WITH(ROWTERMINATOR =''\n'', FIELDTERMINATOR='','')'

EXEC (@Command )

END

-- STEP 2

EXEC InsertCSVFile

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 02:07:39

And remember that the path to the file is relative your sql server, not your local machine.
Use UNC path if applicable.

E 12°55'05.63"
N 56°04'39.26"

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-04-15 : 21:25:51

quote:
Originally posted by Peso

And remember that the path to the file is relative your sql server, not your local machine.
Use UNC path if applicable.

E 12°55'05.63"
N 56°04'39.26"

I got it to work, thanks. However, now I have a new problem. I ran this:

USE [Hospital_Compare];

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/******************************************************
Object: StoredProcedure import csv to HQI_FTNT
Script Date: 04/12/2009
JCB
******************************************************/

CREATE PROCEDURE importcsvfile

AS

BULK INSERT dbo.HQI_FTNT FROM 'C:\Users\John & Tanya\Documents\John\Hospital Compare Hospital_flatfiles\dbo_vwHQI_FTNT.csv'
WITH

(DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n');

GO

The message box said that the command completed successfully but when I look at the table, there is no data in it. Do you know why?

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-04-16 : 05:43:04

It will be easy to identity the issue if you Can provide some sample data of your .csv file.

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-04-16 : 15:09:42

quote:
Originally posted by Rajesh Jonnalagadda

It will be easy to identity the issue if you Can provide some sample data of your .csv file.

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

Here is one record from the csv file:

Footnote FootnoteText
1 The number of cases is too small (<25) to reliably tell how well a hospital is performing.

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-04-17 : 03:07:54

Hi,

Your data shows that, fields are tab seperated not comma seperated

so use FIELDTERMINATOR = '\t' instead of FIELDTERMINATOR = ','

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources