Results 1 to 4 of 4

Thread: Question about creating data file from SQL query

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2013
    Location
    Overland Park Kansas
    Posts
    183

    Question about creating data file from SQL query

    I have a query which creates a temp table, inserts data into that temp table then selects data from that temp table.



    Code:
    connection.Open "Provider=SQLOLEDB.1;Data Source=server;" & _
    	            "Initial Catalog=database;Integrated Security=SSPI;"
    
    set recordset = WScript.CreateObject("ADODB.RecordSet")
    set recordset = connection.execute(sqltext)
    It won't let me set the recordset because the query is creating a temp table (which returns no records) and inserting data into that temp table (which returns no records)


    how can I execute the three sql commands and export it to a csv file if I can't use a recordset.

    Code:
    set recordset = connection.execute(sqltext)
    The line of code above gives the error operation not allowed when the object is closed
    Last edited by iamcpc; Jun 16th, 2020 at 10:55 AM.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Re: Question about creating data file from SQL query

    Correct, creating a temp table doesn't create a recordset. Correct, inserting into the temp table doesn't create a recordset. But selecting from the temp table does... so what's the problem?
    And if you're just selecting the data you dumped into a temp table.... why are you bothering to do that? Just select the data?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jan 2013
    Location
    Overland Park Kansas
    Posts
    183

    Re: Question about creating data file from SQL query

    Quote Originally Posted by techgnome View Post
    Correct, creating a temp table doesn't create a recordset. Correct, inserting into the temp table doesn't create a recordset. But selecting from the temp table does... so what's the problem?
    And if you're just selecting the data you dumped into a temp table.... why are you bothering to do that? Just select the data?

    -tg

    I had thought about that but the query also has variables. Declaring variables does not make a recordset either. My current workaround is to make two separate SQL statements and put the select statement to the recordset.


    Code:
    connection.execute(SQLTextInsert)
    set recordset = connection.execute(SQLTextSelect)
    1.I've seen online where people are able to use something like set nocount on set nocount off which some people say this fixed the problem for them. How would that impact how this VBscript is executing SQL and returning a recordset? I've tried this and had no luck.
    2. Why would it fix this issue for some people and not for me?
    3. Is there any way to send one SQL string with things like SQL variables inserts and updates and end with a select statement and have it return a recordset?

    If the sql statement i'm executing has multiple commands being executed and the last command is a select statement shouldn't it return a recordset?


    https://p2p.wrox.com/classic-asp-bas...ct-closed.html

    I found this code which someone said helped but I don't really understand exactly what it's doing but it uses the temp variable as another recordset.

    Code:
    
    On Error Resume Next
        Do
            Set temp = recordset
            Set recordset = recordset.NextRecordset
        Loop Until recordsetIS Nothing
        Set recordset= temp
    On Error GoTo 0
    
    'after this the other person said they were able to read the recordset
    If recordset.EOF Then
    end if
    Last edited by iamcpc; Jun 16th, 2020 at 03:48 PM.

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Re: Question about creating data file from SQL query

    Ah, ha... perhaps if you had also provided the query, it would have been a bit more obvious what was going on. What's happening is that the query returns things like "x rows affected" when a query is run... even when no records are actually returned (such as when you insert records) ... That's output... when there is no other output, ADO will use that as your recordset output. If, at the beginning of your query you put SET NOCOUNT ON it suppresses those messages so they are not streamed back to you and the only output is the final select, which is then put into your recordset.

    Regarding the code you found. You can tell you connection that your going to recieve muplitple recordsets .... look up ado mars (Multiple ADO RecordSets I think it stands for) ... that will then loop through the recordsets until there aren't any.... which isn't quite what you want... you want to discard recordsets until you get to the one you want. This technique is only if the SET NOCOUNT ON doesn't work for you for what ever reason.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width