Hello All,
I am using openrowset to import from a csv file. Here is the
code

INSERT INTO [surveymonkeytest82608].[dbo].[Respondents]

SELECT * FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver
(*.txt; *.csv)};DBQ= h:\surveymonkeyresults\results82608\csv', 'SELECT
* from respondents.csv');

This is in a stored procedure and works fine. But I want to be able to
pass the directory name (DBQ info above) as a parameter, so the user
can provide the directory where the file exists. Can anyone tell me if
it is possible to pass the directory name as a parameter above. or
which method I should use to import csv files using a stored procedure
and have the ability to pass the directory as a parameter.


Thanks
Shri

You cannot use variables as parameters for OPENROWSET. To work around
that you can use dynamic SQL:

DECLARE @folder NVARCHAR(100);

SET @folder = N'h:\surveymonkeyresults\results82608\csv';

DECLARE @sql NVARCHAR(2000);

SET @sql = N'
INSERT INTO [surveymonkeytest82608].[dbo].[Respondents]
SELECT * FROM OPENROWSET (''MSDASQL'', ''Driver={Microsoft Text Driver
(*.txt; *.csv)};DBQ=' + @folder + N''', ''SELECT
* from respondents.csv'');';

EXEC(@sql);

Also, if 'h:' drive is a mapped drive and you run this on a server,
probably you need to use UNC share path, like '\\server\share\folder'.

--
Plamen Ratchev
http://www.SQLStudio.com