[AccessD] Calling a stored procedure from Access

Hmmm...how are you running the sproc?

Are you running the sproc in the SQL query window? Or are you calling it
from inside MS Access? Can you verify the following for me?

1. Where did you create the sproc? I have to assume that you created it in
the Infutor database? In SQL server query window it is easy to create the
sproc in the master database.
2. On SQL Management Studio click on the Infutor database and then click New
Query on the toolbar and type this...

EXEC BulkInsertInfutor 'NAR_MT1.csv'

Are you getting an error or is it executing properly?

3. Change exec @SQL to exec (@SQL)

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

Eric,

Thanks so much for your patience.  

Infutor is the database name, as well as the table name.  I just changed the
table to be called tblInfutor.

I corrected the syntax error to get the '\n'. 
As soon as I put the Use statement in there I get a syntax error:

A Use database statement is not allowed in a procedure, function or trigger.

What I have now is:

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 dbo.tblinfutor	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) + '\n' + char(39) + ')'
	exec @SQL
END


I now get the error:

Could not locate entry in SysDatabases for 'Bulk INSERT dbo.' ...

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Eric Barro
Sent: Thursday, May 03, 2007 11:01 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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


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