CODEWITHSUNDEEP
asp.netsql-serverstored-procedures
user1479393
user1479393

Reputation: 1

SQL stored procedure in ASPX not executing

I have created a stored procedure to convert an Excel file to SQL Server 2008 R2. It works perfectly when I test it in Visual Studio 2008.

However, when I use the stored procedure in an ASPX page using the SqlDataSource control, the stored procedure does not execute and no conversion happens, although there is no error message displayed. Here is the ASPX code:

<%@ Page Title="Home Page" Language="VB" MasterPageFile="~/Site.Master" 
         AutoEventWireup="false" CodeFile="Processing.aspx.vb" Inherits="_Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
         ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" 
         SelectCommand="MyStoredProcedure" 
         SelectCommandType="StoredProcedure">
    </asp:SqlDataSource>
</asp:Content>

The SQL Server stored procedure code:

ALTER PROCEDURE dbo.MyStoredProcedure 
AS

SELECT * 
INTO [dbo].[MySQLtable] 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\MyExceltable;', 
                'SELECT * FROM [Sheet1$]')
RETURN

Any help will be highly appreciated.

Upvotes: 0

Views: 868

Answers (2)

Curtis
Curtis

Reputation: 103358

You have:

SelectCommand="MyStoredProcedure"

Looking at your SQL script, this should be:

SelectCommand="StoredProcedureExcelToSQL"

Upvotes: 1

viclim
viclim

Reputation: 959

Is the aspx page being served from a server? If so the file (MyExceltable) needs to be on the server instead of the user's machine.

Upvotes: 1

Related Questions