Hi all,

I would like to replace the default directory location (c:\temp) and the
filename (emails.csv) with variables like @FileDir and @FileName in the
statement below.

SELECT @cnt = COUNT(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)}; DefaultDir=c:\temp;','select * from "emails.csv"')

However, my attempts have not been successful.

Any ideas appreciated, and TIA.

Greg

OPENROWSET does not accept variables for its arguments and you have to use
dynamic SQL. It may look like this:

DECLARE @FileDir NVARCHAR(80);
DECLARE @FileName NVARCHAR(80);
DECLARE @sql NVARCHAR(500);
DECLARE @params NVARCHAR(50);
DECLARE @cnt INT;

SET @FileDir = N'C:\Temp';
SET @FileName = N'emails.csv';
SET @params = N'@cnt_out INT OUTPUT';

SET @sql =
N'SELECT @cnt_out = COUNT(*)
FROM OPENROWSET(''MSDASQL'',
''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir= ' + @FileDir +
''', ''SELECT * FROM ' + @FileName + ''');';

EXEC sp_executesql @sql, @params, @cnt_out=@cnt OUTPUT;
SELECT @cnt;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Many thanks, works like a dream.