Mark A Matte
markamatte at hotmail.com
Thu May 3 14:33:20 CDT 2007
John,
I believe I do something similar when I have to run a couple 100 SQL
statements...but I don't recreate the SP everytime. I use the following
SQL(At BOTTOM) from the query window and it passes my criteria(which is
stored in a table) to the SP and executes it for each record. In your
cases...a table listing your @filenames could be passed from a query to the
SP without rewriting or editing the SP each time.
I did run into a few issues with placements of quotes...but after that it
ran like a champ.
Good Luck,
Mark A. Matte
P.S...As an afterthought I included my SP template below as well...good
luck.
***************SQL*************
declare @ID nvarchar(255)
declare MyCursor cursor
for select CriteriaSQL from tblcriteria where requestname = 'KenPro'
open mycursor
fetch next from mycursor
into @ID
while (@@fetch_status =0)
Begin
exec Select_Records_1 @ID
fetch next from mycursor
into @ID
End
Close mycursor
deallocate mycursor
***************SQL*************
***************Stored Procedure Template*************
CREATE PROCEDURE [Select_Records_1] @ID nvarchar(255) AS
declare @sql as varchar(1000)
declare @nsql as nchar(3000)
set @sql='insert into tbltester(case_id,Criteria) SELECT case_id, ' +
char(34)+ at ID+char(34)+' from tblCase where case_id=1482445 and
creation_date>'+char(39)+'9/30/2006'+CHAR(39)+' and ' + at ID
set @nsql=@sql
set nocount on
exec sp_executesql @nsql
set nocount off
***************Stored Procedure Template*************
>From: "JWColby" <jwcolby at colbyconsulting.com>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem
>solving'"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Calling a stored procedure from Access
>Date: Thu, 3 May 2007 15:12:36 -0400
>
>So executing that in the query window CREATES the SP but does not execute
>the SP?
>
>
>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
>
>--
>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
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
_________________________________________________________________
Download Messenger. Join the i’m Initiative. Help make a difference today.
http://im.live.com/messenger/im/home/?source=TAGHM_APR07