none
Stored Procedure Retrunes Rows to SQLMS But Not To Excel RRS feed

  • Question

  • I have a stored procedure that I have been for some time to populate an Excel spread sheet via VBA/OLEB code.

    Today, changed it to include a statement like "insert into @tablevar exec anotherSP" at the start of the code.

    It still works perfectly in SQLMS but no longer returns data to Excel.

    I have no doubt that I will kick myself when I find out what the problem is, and how to fix it, but would appreciate it if someone could help me.

    Dick Campbell


    R Campbell

    Friday, March 3, 2017 4:33 AM

Answers

  • My suggestion was "SET NOCOUNT ON", not "set rowcount off".

    Try SET NOCOUNT ON.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Dick Campbell Friday, March 3, 2017 11:13 AM
    Friday, March 3, 2017 10:49 AM
    Moderator

All replies

  • Dick

    Let me clarify things

    1.You insert the result of anothgerSP into a table variable  and it works just fine

    2. Where the data goes to EXCEL? 

    3. I do not know VBA but if you show T-SQL of the sp will try to suggest something useful


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, March 3, 2017 4:42 AM
    Answerer
  • Can you expand a bit on your explanation?

    Exactly what do you mean by "I have a stored procedure that I have been for some time to populate an Excel spread sheet via VBA/OLEB code."? Are you using sp_OACreate inside your proc? OR are you executing this procedure from Excel?

    Also: "Today, changed it to include a statement like "insert into @tablevar exec anotherSP" at the start of the code." Start of what code? Your procedure code? The code that calls your procedure?

    Perhaps adding SET NOCOUNT ON in the beginning of the proc code can help?


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, March 3, 2017 8:15 AM
    Moderator
  • Thanks for both replies. I should explain a little further. I need to pivot on sum() and count(). The sum() pivot query (a stored procedure) has been working for a while and I used it as the basis for a second stored procedure that does the counts, to identify any duplicate (or multiple) returns, which would ndicate invalid data. This stored procedure returns just two columns (in the table variable), the ID and and an ErrorFlag. The final select statement joins the two pivot results, on the ID columns, and adds an ErrorFlag column to the orignal rowset which I planned to use in Excel to filter for or highlight suspect rows. This works perfectly in SQLMS but returns no data rows to Excel. I did notice, in the SQLMS message window, that two row counts are returned so I attempted to use "set rowcount off" in the first stored procedure but that seemed cause errors, so I gave up. Perhaps I should have persisted. Thinking that there might have been confusion about the columns retuned, I tried a dummy select statement (within an always false IF stemement) but no luck there yet either. I know that there are other options for a two aggregate pivot, but Query this works, just not in Excel. The VBA uses straight OLEDB not Office Automation (AO).. I can post the code but it is reasonably complex.

    R Campbell

    Friday, March 3, 2017 10:19 AM
  • The above reply WAS broken into paragraphs but somehow that was lost when I posted it. I hope that it's not too difficult to read.

    R Campbell

    Friday, March 3, 2017 10:24 AM
  • My suggestion was "SET NOCOUNT ON", not "set rowcount off".

    Try SET NOCOUNT ON.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Dick Campbell Friday, March 3, 2017 11:13 AM
    Friday, March 3, 2017 10:49 AM
    Moderator
  • My suggestion was "SET NOCOUNT ON", not "set rowcount off".

    Try SET NOCOUNT ON.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thanks Tibor,

    Absolutely correct, I knew that I would want to kick myself when I got the answer. It came to me actually just after my last post. I have fallen for this before, but not for a long time.


    R Campbell

    Friday, March 3, 2017 11:13 AM