[AccessD] Calling a stored procedure from Access

Hi John:

I am not sure if this is the reason for your problem but all my external
requests for files and applications, through Stored Procedures are run like
so:

CREATE PROCEDURE JTBCreateImportAll  AS

/* Build all the import tables */
CREATE TABLE [dbo].[Customer](
 ...

 ...

/* Import the data */
SET QUOTED_IDENTIFIER OFF 
SET ANSI_NULLS OFF
EXEC master..xp_cmdshell 'D:\import\ImportCustomer.exe', no_output
EXEC master..xp_cmdshell 'D:\import\ImportTransactionLog.exe', no_output 
EXEC master..xp_cmdshell 'D:\import\ImportInventory.exe', no_output 
EXEC master..xp_cmdshell 'D:\import\ImportAR.exe', no_output

GO

...through an external xp_cmdshell app in the master directory.

The actual files that run the import routine are in compiled VB code. I can
send you a copy of one of the source files so you can re-design and
re-compile your own. The files are not nearly the size of yours being a
maximum of 80,000 records, in delimitated text format. The creation of
80plus tables, importing the data and creating indexes usually takes about
an hour. One note is there is a maximum size of a SP but by creating an UDF
for each table and calling them from a SP you can easily get around that
linmit. 

HTH
Jim

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