[AccessD] Calling a stored procedure from Access

So basically this is the command you end up with based on your sproc...

BULK INSERT Infutor.dbo.infutor from 'D:\PSMData\INFUTOR\CSV\NAR_MT1.csv'
WITH (FieldTerminator = '|', ROWTERMINATOR = '\'n)

First off you have a SYNTAX ERROR in that statement

'\'n) should be '\n') as I believe you meant to specify the newline
character (\n) as the ROWTERMINATOR

I'm guessing that's where your previous syntax error in '\' message was
coming from as well...

Just need to verify...

Infutor is your database name?

Infutor is also the table name?

I recommend adding this line after the BEGIN statement in your sproc...

USE databasename

...and make sure that what follows BULK INSERT is the tablename



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Thursday, May 03, 2007 7:38 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Calling a stored procedure from Access

OK, let's try this...

The following is what I have stored. 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[BulkInsertInfutor]
	-- Add the parameters for the stored procedure here
	@FilName	varchar(255)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for procedure here
	DECLARE @FileSpec varchar(1000)
	set @filespec = 'D:\PSMData\INFUTOR\CSV\'	
	set @filespec = @filespec + @FilName

	DECLARE @SQL varchar(1000)
	set @SQL = 'BULK INSERT Infutor.dbo.infutor	from ' + char(39) +
@filespec + char(39)
	set @SQL = @SQL +' WITH	(FieldTerminator = '
	set @SQL = @SQL + char(39) + '|' + char(39) + ', '
	set @SQL = @SQL + 'ROWTERMINATOR = '
	set @SQL = @SQL + char(39) + '\' + char(39) + 'n)'
	exec @SQL
END 

When I try to do:

Exec BulkInsertInfutor 'NAR_MT1.csv'

I get:

Msg 7202, level 11, State 2, line 22

could not find server 'BULK INSERT INFUTOR' in sys servers.

TIA for all assistance,

John W. Colby
Colby Consulting
www.ColbyConsulting.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.467 / Virus Database: 269.6.2/785 - Release Date: 5/2/2007 2:16
PM