Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Stored Procedure using Parameters from Excel

Author  Topic 

JezLisle
Posting Yak Master

132 Posts
Posted - 2008-10-31 : 06:50:38
I am trying to find out how I can create a Stored Procedure which would import data from a Sheet within Excel into an existing table in SQL Server.

The range of data has set columns but there maybe an undetermined number of rows,

How could I do something like this?

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2008-10-31 : 06:55:44
SQL Server Integration Services (SSIS) would probably be able to pull this off...

- Lumbago
Go to Top of Page

JezLisle
Posting Yak Master

132 Posts
Posted - 2008-10-31 : 07:07:46
What is that?
I was looking at trying to do this via code so that I can have a command button on my Excel form that automatically sends the data from Excel into the SQL table
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-10-31 : 07:10:53
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

JezLisle
Posting Yak Master

132 Posts
Posted - 2008-10-31 : 07:26:02
Thanks, I have written this code in Excel taking lines from the link in the above thread.

This is what I have got...

Sub SendData()
Dim cnn As ADODB.Connection
Dim sQRY As String
Dim strFilePath As String
Dim IngRecsAff As Long

Set cnn = New ADODB.Connection
strFilePath = "Driver={SQL Native Client};" & _
"Server=CISSQL1;" & _
"Database=CORPINFO;" & _
"Trusted_Connection=Yes"
cnn.Open strFilePath
sQRY = _
"INSERT INTO jez.HospAtHomeActivityReport " & vbCrLf & _
"SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & vbCrLf & _
"'Excel 8.0;Database=F:\HospAtHome.xls;HDR=YES', " & vbCrLf & _
"'SELECT * FROM [ToSend$]') "
Debug.Print sQRY
cnn.Execute sQRY, IngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & IngRecsAff
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub

I get a run time error '-2147217900 (80040e14)'
[Microsoft][SQL Native Client][SQL Server] SQL Blocked access to STATEMENT 'OpenRowset/OpenDataSource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security confiuration for this server. A system administrator can enable the use if 'Ad Hoc Distributed Queries', see "Surface Are Configuration" in SQL Server Bookes Online.

What does this mean in a simple way and how can I get around it?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-10-31 : 08:34:55
http://blog.sqlauthority.com/2008/01/02/sql-server-fix-error-15281-sql-server-blocked-access-to-statement-openrowsetopendatasource-of-component-ad-hoc-distributed-queries-because-this-component-is-turned-off-as-part-of-the-se/

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2008-10-31 : 11:18:03
You can just use this. There is really no reason to write code in excel. Just make sure the Excel File is closed prior to running, otherwise it will error out.

create proc Sp_GetMyDataFromExcel
as


exec sp_configure 'Ad Hoc Distributed Queries', 1 

Insert Into MYTABLE(COL1,COL2,COL3)
select a.myCOL1,a.myCOL2,a.myCOL3
from
OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;DATABASE=F:\HospAtHome.xls', 'Select * from [ToSend$]') a
--add a where clause if needed i.e.
/*Where a.myCOL1 = 200*/

exec sp_configure 'Ad Hoc Distributed Queries', 0


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

JezLisle
Posting Yak Master

132 Posts
Posted - 2008-11-04 : 06:25:42
Thanks for that, I have tried what you said in the thread above...

The Stored Procedure is below, but this is the error message, but dont understand what its saying

Msg 7415, Level 16, State 1, Procedure sp_GetDataFromExcel, Line 4
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.


What does this mean?

CREATE PROCEDURE sp_GetDataFromExcel
AS
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
INSERT INTO HospAtHomeActivityReport (HospAtHome, DateofReferral1, SourceofReferral1,
ReasonforReferral1, NewFollowUp1, MinutesSpentPatient1, PredictedLengthStay1,
NumDayswithHAH1, Outcome1, DateofReferral2, SourceofReferral2, ReasonforReferral2,
NewFollowUp2, MinutesSpentPatient2, PredictedLengthStay2, NumDayswithHAH2, Outcome2,
DateofReferral3, SourceofReferral3, ReasonforReferral3, NewFollowUp3, MinutesSpentPatient3,
PredictedLengthStay3, NumDayswithHAH3, Outcome3, DateofReferral4, SourceofReferral4, ReasonforReferral4,
NewFollowUp4, MinutesSpentPatient4, PredictedLengthStay4, NumDayswithHAH4, Outcome4, DateofReferral5,
SourceofReferral5, NewFollowUp5, ReasonforReferral5, MinutesSpentPatient5, PredictedLengthStay5,
NumDayswithHAH5, Outcome5, DateofReferral6, SourceofReferral6, ReasonforReferral6, NewFollowUp6,
MinutesSpentPatient6, PredictedLengthStay6, NumDayswithHAH6, Outcome6, DateofReferral7, SourceofReferral7,
ReasonforReferral7, NewFollowUp7, MinutesSpentPatient7, PredictedLengthStay7, NumDayswithHAH7, Outcome7,
DateofReferral8, SourceofReferral8, ReasonforReferral8, NewFollowUp8, MinutesSpentPatient8, PredictedLengthStay8,
NumDayswithHAH8, Outcome8, DateofReferral9, SourceofReferral9, ReasonforReferral9, NewFollowUp9,
MinutesSpentPatient9, PredictedLengthStay9, NumDayswithHAH9, Outcome9, DateofReferral10, SourceofReferral10,
ReasonforReferral10, NewFollowUp10, MinutesSpentPatient10, PredictedLengthStay10, NumDayswithHAH10, Outcome10,
InputUser, InputDateTime, InputFlag, ImportDate)
SELECT HospAtHome, xl.DateofReferral1, xl.SourceofReferral1, xl.ReasonforReferral1, xl.NewFollowUp1, xl.MinutesSpentPatient1, xl.PredictedLengthStay1,
xl.NumDayswithHAH1, xl.Outcome1, xl.DateofReferral2, xl.SourceofReferral2, xl.ReasonforReferral2, xl.NewFollowUp2, xl.MinutesSpentPatient2,
xl.PredictedLengthStay2, xl.NumDayswithHAH2, xl.Outcome2, xl.DateofReferral3, xl.SourceofReferral3, xl.ReasonforReferral3, xl.NewFollowUp3,
xl.MinutesSpentPatient3, xl.PredictedLengthStay3, xl.NumDayswithHAH3, xl.Outcome3, xl.DateofReferral4, xl.SourceofReferral4, xl.ReasonforReferral4,
xl.NewFollowUp4, xl.MinutesSpentPatient4, xl.PredictedLengthStay4, xl.NumDayswithHAH4, xl.Outcome4, xl.DateofReferral5, xl.SourceofReferral5,
xl.ReasonforReferral5, xl.NewFollowUp5, xl.MinutesSpentPatient5, xl.PredictedLengthStay5, xl.NumDayswithHAH5, xl.Outcome5, xl.DateofReferral6,
xl.SourceofReferral6, xl.ReasonforReferral6, xl.NewFollowUp6, xl.MinutesSpentPatient6, xl.PredictedLengthStay6, xl.NumDayswithHAH6, xl.Outcome6,
xl.DateofReferral7, xl.SourceofReferral7, xl.ReasonforReferral7, xl.NewFollowUp7, xl.MinutesSpentPatient7, xl.PredictedLengthStay7, xl.NumDayswithHAH7,
xl.Outcome7, xl.DateofReferral8, xl.SourceofReferral8, xl.ReasonforReferral8, xl.NewFollowUp8, xl.MinutesSpentPatient8, xl.PredictedLengthStay8,
xl.NumDayswithHAH8, xl.Outcome8, xl.DateofReferral9, xl.SourceofReferral9, xl.ReasonforReferral9, xl.NewFollowUp9, xl.MinutesSpentPatient9,
xl.PredictedLengthStay9, xl.NumDayswithHAH9, xl.Outcome9, xl.DateofReferral10, xl.SourceofReferral10, xl.ReasonforReferral10, xl.NewFollowUp10,
xl.MinutesSpentPatient10, xl.PredictedLengthStay10, xl.NumDayswithHAH10, xl.Outcome10, xl.InputUser, xl.InputDateTime, xl.InputFlag, GETDATE()
FROM
OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=F:\HospAtHomeDatabase.xls', 'Select * from [Database$]') xl
EXEC sp_configure 'Ad Hoc Distributed Queries', 0
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2008-11-04 : 16:40:54
The account running the procedure does not have access to change adhoc.

omit the lines

exec sp_configure 'Ad Hoc Distributed Queries', 1
and
exec sp_configure 'Ad Hoc Distributed Queries', 0

Then from a sa account run

 
sp_configure 'show advanced options', 1
RECONFIGURE
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO


that should allow you to run the query.

If you want to turn off the ability afterwards run 

sp_configure 'Ad Hoc Distributed Queries', 0
RECONFIGURE




Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site