By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,261 Members | 1,658 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,261 IT Pros & Developers. It's quick & easy.

How can I use variables in this TSQL Statement

P: n/a
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
Mar 4 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Mar 4 '08 #2

P: n/a
Many thanks, works like a dream.
Mar 4 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.