How can I use variables in this TSQL Statement

[Greg Hines]

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('MSD ASQL', 'Driver={Micros oft Text
Driver (*.txt; *.csv)}; DefaultDir=c:\t emp;','select * from "emails.csv "')

However, my attempts have not been successful.

Any ideas appreciated, and TIA.

Greg

[Plamen Ratchev]

Re: How can I use variables in this TSQL Statement

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(''MS DASQL'',
''Driver={Micro soft 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

[Greg Hines]

Re: How can I use variables in this TSQL Statement

Many thanks, works like a dream.