How to pass an Excel range as an argument to a SQL Server stored Procedure

  • Thread starter Thread starter Belinda
  • Start date Start date
B

Belinda

Hello All

I want to pass an Excel range data to a SQL Server stored procedure. I
am trying to pass the data in a Excel range in form of some VB array
to a stored procedure.

Can you kindly provide a simple sample code in VBA and in T-SQL stored
proc how I can do this.

Thanks
Belinda
 
Belinda,

Are you reading an Excel worksheet? If so, this is usually like

SELECT * FROM [Sheet1$];

If you want to get a specific range, it is best to create a workbook name
for that range, and then it acts as a pseudo-table

SELECT * FROM myExcelRange;
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
In order to do as Bob suggests, you must add the Excel spreadsheet as a
linked server ( this is doc'd in books on line.). THen use the 4 part name

select * from myexcel...namedrange


--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
 
Wayne & Bob

Many thanks for your tip on reading a Excel range. Please note I have
a number of users using Excel and will need to writeback to SQL Server
creating a linked server using OpenDataSource will need the Excel file
to be on the server where SQL Server is running. So that means I must
ensure all these Excel updates from Clients must be saved onto a Excel
file on the SQL Server or on a network share to be available to SQL
Server this will greatly reduce concurrency of users running this
Excel to SQL Server writeback app and also they would need a network
access to a shared drive or to the server where SQL Server is located.
I would like to read the range in VBA and pass it to a stored proc
through ADO that way my application will have concurrency and
scalability.

Can you please suggest how I can pass a Excel range from VBA to a
T-SQL stored proc.

Thanks
Belinda
 
Belinda,

What does the range hold, a set of dates, text, numbers, or what? What does
the SP expect, one at a time, an array? I have an example of reading from a
Sybase database using SPs, SQL Server uses Transact-SQL same as Sybase,
writing would be the same principle. Only change would be the connection
string.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I don't think you can pass an array as a parameter of a stored
procedure. The usual advice is to use a delimited string i.e.
concatenate the range values using appropriate characters as
delimiters for column and row e.g.

"a1,b1,c1;a2,b2,c2;a3,b3,c3"

where , delimits columns and ; delimits rows (assumes there are no ,
nor ; in your data). Your stored proc should then parse the string to
retireve the cell values.
 
Can I do the reverse? INSERT INTO myexcel...namedrange SELECT x, y, z, FROM
mytable?
 
George Hutto said:
Can I do the reverse? INSERT INTO myexcel...namedrange SELECT x, y, z, FROM
mytable?
Click to expand...

Sure can. Run this query against a MS Jet connection:

INSERT INTO
[Excel 8.0;database=C:\myexcel.xls;].[namedrange]
SELECT x, y, z
FROM
[ODBC;Driver={SQL
Server};SERVER=MYSERVER;DATABASE=MyDB;UID=;Pwd=;].mytable

Note myexcel.xls should be closed to avoid the memory leak bug
(Q319998).

You can do something similar in SQL on the server side but I can't
remember the exact syntax off the top of my head.

--
 
Back
Top