Stuart McLachlan
stuart at lexacorp.com.pg
Thu May 3 21:35:28 CDT 2007
Don't know what you are doing wrong, but:
This works:
CREATE PROCEDURE test
@PATHNAME varchar(50),
@FILENAME varchar(50)
AS
select @pathname + @filename
GO
"test 'c:\testdir\', 'myfile.txt'" in Query Analyser
returns the string "c:\testdir\myfile.txt"
Change the SProc to:
CREATE PROCEDURE test
@FILENAME varchar(50)
AS
declare @pathname varchar(50)
set @pathname = 'C:\TestDir\'
select @pathname + @filename
GO
and then "test 'myfile.txt'"
returns the same as above
On 3 May 2007 at 21:33, JWColby wrote:
> Could you please just give me a valid syntax for setting a variable equal to
> the string C:\MyDir\MyFileName.txt?
>
> It appears that it doesn't like the \ but I cannot figure out how to please
> the SQL gods.
>
> I need a variable
>
> @filePath
>
> I need to set @FilePath = 'C:\MyDir\'
>
> I get an error:
>
> Incorrect syntax near '\'
>
> Once I get that, I need to append the file name in @FileName
>
> SET @FilePath = @FilePath + @FileName
>
> This is the year 2007 for crying out loud and I am getting "invalid syntax
> near '\' .." back from my computer. I feel like Scotty in Star trek - The
> Voyage Home... Trying to use a Macintosh... picks up the mouse, speaking
> into it... Computer, I need...
>
> Thanks,
>
>
> 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 6:30 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [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
>
>
>
>
> --
> 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