Posted by cabrenner on 01/09/07 12:53

You are correct - Excel has a limit on the number of rows. I thought
about that after I sent the reply. So now I am looking at Access.

Here is my next question. I want to use OPENROWSET in a procedure to
get the data from Access into SSE. My code looks something like this:

INSERT INTO sse_table1 Select GFF.*
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'path to mdb';'admin';'',
'Select * FROM access_table1
) as GFF

This works great. However, the location of the access database is only
known at runtime. I can pass the path as a parameter to the stored
procedure, but using it as a variable in OPENROWSET fails. Code looks
like this

CREATE PROCEDURE [dbo].[spImportBillingFile]
@strTableLocation varchar(255),
@btSuccess bit OUTPUT
AS
BEGIN

DECLARE @strConnect varchar(255)
SET @strConnect = @strTableLocation

INSERT INTO tbl_ups_eInvoice_tmpData Select GFF.*
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
' + @strConnect + ';'admin';'',
'Select * FROM tbl_ups_eInvoice_tmpData'
) as GFF

Set @btSuccess = 1

END

Does OPENROWSET not allow a variable to be used?

Thanks again for the help.

Ed Murphy wrote:
> Erland Sommarskog wrote:
>
> > (cabrenner@optonline.net) writes:
> >> Thank you both for your suggestions. Yes, I was thinking that BULK
> >> INSERT was not going to be able to handle this. I had thought about
> >> dumping the file into an Access table first, but the file could be very
> >> large (200,000+ rows). I am going to try the Excel spreadsheet idea.
> >
> > 200000+ rows? Then Access is probably a better bet. Doesn't Excel stop
> > at 65536 rows?
>
> I think the latest version of Excel may have a higher row limit - which
> only increases the tendency of newbies to misuse Excel as a "database".

[Back to original message]