March 1, 2012 at 2:09 pm
I apologize in advance if this is answered somewhere already... I've searched, but have not found a solution that works for what i'm trying to do.
I have a stored procedure - basically, via ASP, I create a temp table with data, then call this stored procedure to dump the contents to excel. the temp table is named with a unique identifier, as there can be multiple users doing this process at the same time - and each can have different criteria for the report.
If I hard code in the temp table name - the process works fine. Obviously, with a unique table name each time, I cannot do that.
I can post the whole code if need be - for breviety, however, here's the part I'm struggling with (I already have a template excel sheet out there with the headers):
If I do this - it works fine:
[font="Courier New"]INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\partner_files\Templates\open_call_reporttemp.xls;',
'SELECT * FROM [Sheet1$]')
Select * from ##TempOpenCallData_312012145014[/font]
However, If I try to pass the unique table name as a variable, it does not.
[font="Courier New"]set @sql = 'SELECT * FROM ##TempOpenCallData_' + RTRIM(@IdNbr)
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\partner_files\Templates\open_call_reporttemp.xls;',
'SELECT * FROM [Sheet1$]')
exec @sql[/font]
this returns the following error:
[font="Courier New"]Msg 7390, Level 16, State 2, Procedure export_open_calls, Line 66
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not support the required transaction interface.[/font]
I've already verified that the value of @sql is "[font="Courier New"]Select * from ##TempOpenCallData_312012145014[/font]".
Any suggestions?
Thanks in advance...
March 1, 2012 at 2:31 pm
Cory
I think this may have been the subject of a QOTD question (http://www.sqlservercentral.com/questions/T-SQL/88163/) from a couple of weeks ago.
What happens if you change
exec @sql
to
exec (@sql)
Dave
March 1, 2012 at 3:56 pm
If memory serves me correctly, I remember this from a long time ago.
Openrowset() (or any ad-hoc function) does not accept variable parameter.
They only take literal string.
Afterall, these are pass-through functions
And search as I might I could not locate the reference. But give it a try and if that is the solution please post back so others main learn.
March 2, 2012 at 6:05 am
Dave Brooking (3/1/2012)
CoryI think this may have been the subject of a QOTD question (http://www.sqlservercentral.com/questions/T-SQL/88163/) from a couple of weeks ago.
What happens if you change
exec @sqlto
exec (@sql)Dave
Thanks for the reply... I just tried this, but I get the same error message.
March 2, 2012 at 6:13 am
Cory,
Ron's explanation is probably correct and openrowset() doesn't support variables only literals.
I thought the brackets worth a go.
Dave
March 2, 2012 at 6:14 am
bitbucket-25253 (3/1/2012)
If memory serves me correctly, I remember this from a long time ago.Openrowset() (or any ad-hoc function) does not accept variable parameter.
They only take literal string.
Afterall, these are pass-through functions
And search as I might I could not locate the reference. But give it a try and if that is the solution please post back so others main learn.
This seems to be the case - in my original example, if I do build the procedure and hard code the select from the temp table - it works fine. My problem is that I need to have a variable for the temp table, to keep the results unique - there could be several people running this at once, and I need some way to keep their results separate.
March 2, 2012 at 6:17 am
do you have to use a ## table, could you not just use a # table?
March 2, 2012 at 6:26 am
anthony.green (3/2/2012)
do you have to use a ## table, could you not just use a # table?
So far, I've not been able to get that to work. Heres the workflow:
1) user makes a selection of criteria for the open ticket report (by user, by date range, by product, by version, or any combination of)
2) based on the selection, a web report displays (ASP) that lists the results. Within this page, the select gets built, and the results loaded into the ##temp table. on this page then, is an optional link to create an excel sheet from the results
3) If the user select to generate the xls, this launches another page that runs the stored procedure, then displays the contents of the directory where the spreadsheets are saved, so the user can open their file.
With a #temptable, the table is not available to the stored procedure, since its a different session. The ##temptable was working, but then I have the issue with the different users all writing to the same global temp table.
thanks for all the responses so far...
March 2, 2012 at 6:29 am
Use permanent table instead of ##table, tag each row with UserId and, if you have any, session token.
March 2, 2012 at 6:40 am
Eugene Elutin (3/2/2012)
Use permanent table instead of ##table, tag each row with UserId and, if you have any, session token.
Interesting... thanks for the suggestion! will test with this, and report back if that works.
March 2, 2012 at 7:11 am
If you go with the route with having permanent table, don't forget to implement some sort of clearing mechanism to keep the table size under control.
Also, as you will probably insert/delete a lot of rows into/from this table, you will most likely benefit from updating this table statistics after such operations...
March 2, 2012 at 7:45 am
Eugene Elutin (3/2/2012)
Use permanent table instead of ##table, tag each row with UserId and, if you have any, session token.
Still have some fine tuning to the process to do - but overall, this seems to be working! I am able to use a variable in the select - I just cannot pass a variable to the OPENROWSET, or set from a variable tablename - so this works:
[font="Courier New"]INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\ASGWeb\partner_files\Templates\open_call_reporttemp.xls;',
'SELECT * FROM [Sheet1$]')
Select Call_Num, OrgName, ReportedBy, Cust_Type, Reseller, Product, Version, Issue, Status, OpenDate, OpenBy, AssignTo, Summary from TempOpenCallData where IdNbr = @IdNbr[/font]
When you generate the xls sheet - the last thing that particular ASP page does is a delete from that "permanent" TempOpenCallData table where the IdNbr = that ID, so it cleans the data up after generating the xls sheet.
March 2, 2012 at 8:04 am
Now I have not had the opportunity to check this suggestion, but it might be worth testing for your use
--Creating the command string
DECLARE @IdNbr VARCHAR(50)
SET @IdNbr = '312012145014'
set @sql = 'SELECT * FROM ##TempOpenCallData_' + RTRIM(@IdNbr)
--To check
SELECT @sql
Returns:
SELECT * FROM ##TempOpenCallData_312012145014
Then test as
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\partner_files\Templates\open_call_reporttemp.xls;',
'SELECT * FROM [Sheet1$]')
exec @sql
Let us know if it works or does not work.
March 2, 2012 at 8:20 am
bitbucket-25253 (3/2/2012)
Now I have not had the opportunity to check this suggestion, but it might be worth testing for your use
--Creating the command stringDECLARE @IdNbr VARCHAR(50)
SET @IdNbr = '312012145014'
set @sql = 'SELECT * FROM ##TempOpenCallData_' + RTRIM(@IdNbr)
--To check
SELECT @sql
Returns:
SELECT * FROM ##TempOpenCallData_312012145014
Then test as
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\partner_files\Templates\open_call_reporttemp.xls;',
'SELECT * FROM [Sheet1$]')
exec @sql
Let us know if it works or does not work.
unless I'm missing something (entirely possible) - that looks like what i was trying to do...
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\partner_files\Templates\open_call_reporttemp.xls;',
'SELECT * FROM [Sheet1$]')
exec @sql
did not work, but
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\partner_files\Templates\open_call_reporttemp.xls;',
'SELECT * FROM [Sheet1$]')
select * from ##TempOpenCallData_312012145014
did.
March 2, 2012 at 8:25 am
thanks everyone for the responses and help!
I ended up going the route of a permanent table (instead of the global temp table), and adding columns for the date, and a unique identifier for each query that runs.
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\ASGWeb\partner_files\Templates\open_call_reporttemp.xls;',
'SELECT * FROM [Sheet1$]')
Select Call_Num, OrgName, ReportedBy, Cust_Type, Reseller, Product, Version, Issue,
Status, OpenDate, OpenBy, AssignTo, Summary from TempOpenCallData
where IdNbr = @IdNbr
By using a literal string for the select (rather than the "exec @sql") - it seems to work fine. I have a process built in now that removes the rows for the current IdNbr once the excel file has been generated, to keep the table size small.
Again - thanks to all that took the time to read and respond; much appreciated!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply