OpenRowSet with parameters

Question

OpenRowSet with parameters

JMarx

Default port

Points: 1435
More actions
April 17, 2006 at 10:39 am

#112761
Inside a stored procedure, I'm trying to read in a csv file. I'd like to be able to pass both the directory and file names to the sp, but I'm running into a problem with the following code (I've isolated the code for simplicity):
```
declare   @dir varchar(255);
declare   @file varchar(255);
```
```
set @dir = 'C:\temp\';
set @file = 'test.csv';
```
```
select Acct_Nbr, Sol_Code
from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
        DefaultDir=' + @dir + ';',  
        'select top 100 * from ' + @file);
```
I get the following error:
```
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '+'.
```
If I replace the vars with the actual directory and filename, it works correctly. I'm at a loss as to why using variables in OpenRowSet would cause an error. I couldn't find anything in BOL regarding this.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 1

You should build your concatenated string prior to the statement, and use a single variable for it......it doesn't like concatenating the strings within the statement.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

Hi,

I use '''' (4 single quotes) instead of the single quote inside the outer single quote. Here is a beginning of my long statement (for example, this example connects to SQL Server):

...................

@SQLString =

N'SELECT Convert(char(12),GetDate(),111) as QueryDate,a.*

FROM OPENROWSET(' + N'''' + N'SQLOLEDB' + N'''' + N' ,'

+ N'''' + @lServer_Name+ N'''' + N';'+ ...........................

after the string is composed I execute it:

EXEC sp_executesql @SQLString

Regards,Yelena Varsha

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 3

Scorpion_66

SSCertifiable

Points: 7891

April 17, 2006 at 1:55 pm

#632828

That's a way of doing just what I said......

JMarx Default port Points: 1435 More actions · Answer 4

Thanks to both of you, I got the simplified version to work. Learned something new! Here's what I did:

set @openrow1 = 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=' + @dir + ';';
set @openrow2 = 'select top 100 Acct_Nbr from ' + @file;
set @sqlstring = N'select Acct_Nbr from OpenRowset(' + N'''' + N'MSDASQL' + N'''' + N', ' + '''' + @openrow1 + '''' + ', ' + '''' + @openrow2 + '''' + ');';

Works great. However, I wasn't expecting the solution to be an exec, and I need, in the stored procedure, to run this as a cursor. The exec isn't exactly working well with the cursor:

Declare @CrsrVar Cursor;

Set @CrsrVar = Cursor For
 Exec sp_executesql @sqlstring;

gets this:

Server: Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'Exec'.

What would you suggest?

Btw, I'm using a cursor here because ultimately I will be updating an oft-used nextnumber record in a production database. This must be done one record at a time.

Pam Brisjar SSChampion Points: 12094 More actions · Answer 5

Try select into a temp table then run your cursor / update off of that.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 6

That is what I am actually doing: selecting into the temp table, as Pam says. The example that I posted above is a part of my code that inserts into the temp table. Then I select from it. The whole thing works as a stored procedure.

The problem was that my dynamic statements were towards several other servers and they inserted data into the local temp table. When it was just select, it worked. When I did select....insert it stopped working because of Distributed transaction coordinator errors when going towards some of the servers, mostly on Windows 2003. I tried everything including DTC security. In my case the solution was to run the procedure from SQL Server 2005, it works.

Regards,Yelena Varsha

JMarx Default port Points: 1435 More actions · Answer 7

Yeah, a temp table will do the trick. Thanks for all the help!