?
Solved

CopyFromRecordSet vs. OPENROWSET

Posted on 2005-05-02
10
Medium Priority
?
795 Views
Last Modified: 2012-05-05
I created an Access Application in which I run a Stored Procedure as follows:
This code works fine but it takes a long time to execute because it writes the individual records to an Excel spreadsheet on the local C: drive.
Is there a way to run this on the Server side ?

I've heard about the CopyFromRecordsetMethod that copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range.

I've also heard about using the OPENROWSET in my stored procedure which allows you to write directly to an Excel file on the server (assuming that the driver is installed).

Do you know how I could modify this code to speed up the processing ?


Set com = New ADODB.Command
    With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procFlACS"
       .Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
       .ActiveConnection = cn
       Set rstQueryFS = .Execute
    End With

Set objXL = New Excel.Application
      objXL.Workbooks.Add
      strNextFile = GetNextFileName("C:\ACSFALL1.XLS")
      objXL.ActiveWorkbook.SaveAs strNextFile
      Set objWS = objXL.ActiveSheet
      objXL.ActiveSheet.Protect UserInterfaceOnly:=True
     
      For intCol = 0 To rstQueryFS.Fields.Count - 1
        Set fld = rstQueryFS.Fields(intCol)
        objWS.Cells(1, intCol + 1) = fld.Name
      Next intCol
     
      intRow = 2
      Do Until rstQueryFS.EOF
        For intCol = 0 To rstQueryFS.Fields.Count - 1
           If intCol = 15 Then
              objWS.Cells(intRow, intCol + 1).NumberFormat = "000-00-0000"
           End If
           If intCol = 18 Then
              objWS.Cells(intRow, intCol + 1).NumberFormat = "#0.000"
           End If
           If intCol = 24 Then
              objWS.Cells(intRow, intCol + 1).NumberFormat = "@"
           End If
           objWS.Cells(intRow, intCol + 1) = _
              rstQueryFS.Fields(intCol).Value
           objWS.Cells.EntireColumn.AutoFit

        Next intCol
        rstQueryFS.MoveNext
        intRow = intRow + 1
      Loop
      DoCmd.Hourglass False
       
      objXL.Visible = True
0
Comment
Question by:zimmer9
  • 4
  • 3
  • 3
10 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 13912498
1. CopyFromRecordSet
It is Excel method, and it is run on a client side.
http://www.devx.com/tips/Tip/17303

2.OPENROWSET
It is SQL Server clause, and it is run on a server side. User cannot change file location in SPs.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13913741
Think about the path your data has to take.

If your Excel file is on the SQL server:

-using client side code means the data makes a trip to the client then a trip back to the server (2 trips).
-using server side code means the data stays on the server (0 trips).



If your Excel file is on the client:

-using client side code means the data makes one trip to the client (1 trip).
-using server side code means the data makes one trip to the client (1 trip).



If your Excel file is on neither:

-using client side code means the data makes a trip to the client then a trip to your destination (2 trips).
-using server side code means the data makes a trip to your destination (1 trip).



Depending on your network speed, and how much data you have, the number of trips can make a huge difference.


You can use CopyFromRecordsetMethod on the server but you have to install Excel on your server (not recommended)


I would always recommend doing it on the server because then no one ever needs some funky code installed on their client machine.
0
 
LVL 34

Accepted Solution

by:
arbert earned 2000 total points
ID: 13915133
"User cannot change file location in SPs. "

Sure they can....Just another parm to pass....

I agree with nmcdermaid , you don't want to install any client apps on your server.

I guess my question would be, why are you using an Access application to write records to excel?!?!??!  Thought about using DTS to export the records to excel?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 13

Expert Comment

by:ispaleny
ID: 13916006
User cannot change file location in SPs, unless all ad-hoc queries are enabled on the OLEDB provider. In this scenario, the user is able to read and sometimes also modify any file readable by Jet driver including text files on SQL Server. Some people can think this would be a real security hole, or I missed something?
0
 
LVL 34

Expert Comment

by:arbert
ID: 13917331
"User cannot change file location in SPs, unless all ad-hoc queries are enabled on the OLEDB provider."

You don't have to enable adhoc queries to accomplish something so simple...No security hole.....
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 13918300
...No security hole.....

arbert,
you should look at traffic after one of your old answers http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20554649.html . I think it is self-explaining. Later I opened a question about it, but nobody responded, so it was deleted.

Without ad-hoc queries enabled, a normal user (not sysadmin) is even not able to run OPENROWSET.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13922224
If you blindly pass through a data source, sure its a security issue.

But if you write secure code that checks what is passed thruogh, it's not a security issue.

0
 
LVL 34

Expert Comment

by:arbert
ID: 13922713
"If you blindly pass through a data source, sure its a security issue."

What kind of security hole is an output data source, I guess I don't get the point....
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13923053
I was referring more to the SQL injection thing.

If you pass this directly through as the source file argument

fictional file' GO DROP DATABASE VitalProductionDB GO --

Then he might have a problem.




But thats got nothing to do with adhoc queries, or wether or not you can pass a parameter to the openrowset function.




So in response to the statement that enabling adhoc queries is a security issue, I think it's only a security issue if your security design allows it to be. There are many ways to stop it such as disabling write access to files etc.
0
 
LVL 34

Expert Comment

by:arbert
ID: 13923205
"So in response to the statement that enabling adhoc queries is a security issue, I think it's only a security issue if your security design allows it to be. There are many ways to stop it such as disabling write access to files etc."

yep, agree...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question