[AccessD] Calling a stored procedure from Access

John,

Here's how I created the bulk insert statement for my application...

ALTER PROCEDURE dbo.ImportEmployeesFromPeopleSoft
	@DataFileName	varchar(255),
	@ImportFormatFileName	varchar(255)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @sql	varchar(1000)

	-- Create a table to hold the data
	CREATE TABLE #tmpEmployees
	(
		suffix	varchar(5),
		last_name	varchar(30),
		first_name	varchar(20),
		
		...the rest of your fields go in here
		
	)
	-- Read the text file into the temp table
	SET @sql ='BULK INSERT #tmpEmployees FROM ' + CHAR(39) +
@DataFileName + CHAR(39) 
	SET @sql = @sql + ' WITH (FORMATFILE = ' + CHAR(39) +
@ImportFormatFileName + CHAR(39) + ')'

	EXEC (@sql)

	-- Remove the records from the Employees table first
	--NOTE: truncate bypasses transaction log but it is the fastest way
to clear a table and reset identity fields; use DELETE FROM if you want to
recover using t-log
	TRUNCATE TABLE Employees
	-- insert the records into the Employees table
	INSERT INTO Employees
	SELECT
		RTRIM(suffix) as suffix,
		RTRIM(last_name) as last_name,
		RTRIM(first_name) as first_name,

		...the rest of your fields go in here
		
	FROM #tmpEmployees
	-- And then clean up
	DROP TABLE #tmpEmployees
END 

Here's a link that shows how to call the sproc using ADO via Access.

http://www.tek-tips.com/viewthread.cfm?qid=1362565&page=1


--Eric

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

Eric,

I have written an SP to do what I need, but of course...

The sproc looks like:

CREATE PROCEDURE BulkInsertInfutor
	@FilName Char(25)
AS
BEGIN
	SET NOCOUNT ON;
BULK INSERT INFUTOR.dbo.Infutor
	FROM 'D:\PSMData\Infutor\CSV\' + @FilName
	WITH (
		
	)
END 

I am getting a "parse error" 

Incorrect syntax near +
Incorrect syntax near With

I think if I can solve this parse error I will be there.

Then... How do you call it from Access?  I a query?

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 9:17 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Calling a stored procedure from Access

Here's a template to create the sproc.

CREATE PROC sp_readTextFile @filename sysname as


     BEGIN 
     SET nocount ON 
     CREATE TABLE #tempfile (line varchar(8000))
     EXEC ('bulk INSERT #tempfile FROM "' + @filename + '"')
     SELECT * FROM #tempfile
     DROP TABLE #tempfile
 END 

If the sproc already exists change CREATE to ALTER

ALTER PROC sp_readTextFile @filename sysname as


     BEGIN 
     SET nocount ON 
     CREATE TABLE #tempfile (line varchar(8000))
     EXEC ('bulk INSERT #tempfile FROM "' + @filename + '"')
     SELECT * FROM #tempfile
     DROP TABLE #tempfile
 END

-- Assumes:Usage : (if using Query Analyzer in SQL server)  EXEC
sp_readTextFile  'D:\PSMData\infutor\CSV\NAR_ND1.CSV'

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

I am on the final leg of the data import for the 100 million record data
import.  I have about 30 remaining "csv files" in a directory which have to
be imported to SQL Server.  ATM I am manually editing a query which looks
like:
 
BULK INSERT Infutor.dbo.infutor
    FROM 'D:\PSMData\infutor\CSV\NAR_ND1.CSV'
    with (
        yadayada
    )
 
Obviously  'D:\PSMData\infutor\CSV\ is the path to the files and
NAR_ND1.CSV' is the file currently being processed.
 
I have a table of all the files from a preprocessor I wrote that generated
the csv files.  If I could turn query into a stored procedure with a
parameter for the file name, I could then call the stored procedure from
Access and pass in the filenames.  
 
I have never done this before so I need to know how this is done.  I have
created a couple of stored procedures but none with a parameter that has to
be appended to a string etc.  All of my books make a rather huge assumption
that any parameters passed in will be used a filters in where clauses and so
forth, and completely ignore things like constants and variables inside of
the stored procedure.  
 
My ASSUMPTION is that I can create a constant to hold the path, dim a
variable to hold the finished file name with path, then append the path
constant and the passed in parameter and store the results in the string
variable.  Then I can use that to replace the string in the FROM clause.  Or
just do the append directly "in place" in the FROM clause.  At any rate I
end up with something like:
 
    .
    .
    .
    FROM 'D:\PSMData\infutor\CSV\' & @FileName
 
QUESTIONS:
 
1) Having created this stored procedure, how do I get it to save?  Whenever
I try to save it SQL Server tries to save it out as a text file on disk.
2) Having saved it, how do I execute it from Access passing in the file
names.
 
If I can get this concept down I will be a huge step further along in my
automation task.
 
Any help is much appreciated.
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com