locked
how to call Dynamic Sql Statement in Excel sting connection RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi ,

    I have a stored procedure created in Sql server

    Create procedure n as
    Begin
    Declare @P nvarchar(1000)
    Declare @q nvarchar(1000)


    SET @Q='Select * '+'From  T1'
    exec  (@q)
    end

    i am trying to call this Stored procedure in Excel connection to display the result in Excel table format , but it is displaying error as "The Query did not run, or the database table could not be opened"

    please help on this..

    Regards,

    Chinna

    Friday, January 9, 2015 11:15 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi vgvchinna,

    How do you want to call store procedure in excel? Through VBA or excel UI directly (Data)?

    For the VBA code, it would be like this:

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=[your table];Data Source=[your server];U" _
        , _
        "se Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XXX;Use Encryption for Data=False;Tag wi" _
        , "th column collation when possible=False"), Destination:=Range("$I$4")). _
        QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("[your store procedure]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "XXX"
        .Refresh BackgroundQuery:=False
    End With

    If you want to achieve that through excel UI directly, please refer to these steps below (excel 2013):

    1. Select Data tab
    2. Click From Other Sources=>From SQL Server
    3. Type the necessary information=>Next
    4. Select the database and tables=>Next=>Finish
    5. After that the Import Data window will be displayed
    6. Click Properties
    7. Select Definition tab
    8. Change Command type to SQL
    9. Type your store procedure name in the Command text

    Base on that error message, I think the reason is that you don’t specify SQL as the Command type. If you are using Table as the Command type, it will throws that exception.

    On the other hand, this thread may benefit you:

    # MS Excel- Creating Dynamic SQL statement

    https://social.msdn.microsoft.com/Forums/en-US/6f0a354c-d186-4d61-9fd9-074a18823424/ms-excel-creating-dynamic-sql-statement?forum=exceldev

    Regards

    Starain

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, January 12, 2015 6:12 AM