SQL VBA Query Will Only Return Results on Small Dataset

Question

I am in the process of building a macro that will automate reporting for my team. The first part is set up to query SQL (using MS SQL Server Management Studio) based off of unique identifiers that are selected in a dialog box and then feed those results into step 2.

When building the macro, I noticed that the query returned results as expected when the list of unique identifiers is under 9,000. However, if I try to query anything over 9000 rows, the macro will run with no errors, but instead of returning results, it just shows the last active window. It basically looks like the macro has done nothing.

Since the macro works perfectly with a small dataset and there are no errors, I am a bit stumped as to how I can make it work on large datasets. Our reports are usually over 10,000 rows. Could it be my references? Any insight would be greatly appreciated. Please let me know if additional insight is needed.

EDIT:

Current code is below

Sub FilterLov1()
    Dim rng As Range
    Dim workrng As Range
    Dim workrng1 As String
    Dim today As String
    Dim lastRow As Long
    Dim dept As String
    Dim class As String
    Dim subclass As String
    Dim sSQL As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim wsname As String

    wsname = ActiveSheet.Name

    'Set omsid list for SQL query
    On Error GoTo Handler
    Set workrng = Application.InputBox("Range:", Type:=8)
    Application.ScreenUpdating = False

    'Set rng = ActiveSheet.Range(workrng)

    Dim mystring As String
    mystring = RangeToString(workrng)

    'Declare the SQL code here

    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=WAPRCN026A;Initial Catalog= STEP_MVIEWS;Integrated Security=SSPI"

    sSQL = "select NAME, GuidID, DATASTANDARDS_PATH, PRODUCT_NAME_120, MARKETING_COPY, BULLET01, BULLET02, BULLET03, BULLET04, BULLET05, BULLET06, WORKFLOWSTATE, CHANNELSTATUS, THDONLINESTATUS from STEP_MVIEWS.dbo.[OMSID TO DATASTANDARDS] inner join STEP_MVIEWS.dbo.SCORECARD10_APPROVED on name = OMSID where [omsid] in (" & mystring & ")"

    Set rs = New ADODB.Recordset
    rs.Open sSQL, cn

        'Sheets.Add After:=ActiveSheet

    Worksheets.Add.Name = "FiltersLOVRaw"
    Worksheets("FiltersLOVRaw").Cells(1, 1).CopyFromRecordset rs

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

    Cells.Select


    'Add Column Headers here for export file
    Sheets("FiltersLOVRaw").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Cells(1, 1).value = "OMSID"
    Cells(1, 2).value = "PARENT LEAF GUID"
    Cells(1, 3).value = "FILE PATH"
    Cells(1, 4).value = "PRODUCT NAME (120)"
    Cells(1, 5).value = "MARKETING COPY (1500)"
    Cells(1, 6).value = "BULLET 01"
    Cells(1, 7).value = "BULLET 02"
    Cells(1, 8).value = "BULLET 03"
    Cells(1, 9).value = "BULLET 04"
    Cells(1, 10).value = "BULLET 05"
    Cells(1, 11).value = "BULLET 06"
    Cells(1, 12).value = "WORKFLOW STATUS"
    Cells(1, 13).value = "CHANNEL STATUS"
    Cells(1, 14).value = "THD ONLINE STATUS"

    Cells.Select


    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    'Sheets.Add
    'ActiveSheet.Name = "Export"

    'Rows(x - 1).EntireRow.Delete
    'Cells.Select
        Range("A1").Select

Handler:
    Exit Sub
End Sub
Function RangeToString(ByVal MyRange As Range) As String
    RangeToString = ""
    If Not MyRange Is Nothing Then
        Dim myCell As Range
        For Each myCell In MyRange
            RangeToString = RangeToString & ",'" & myCell.value & "'"
        Next myCell
        'Remove extra comma
        RangeToString = Right(RangeToString, Len(RangeToString) - 1)
        'RangeToString = Left(RangeToString, Len(RangeToString) - 1)
    End If
End Function

Got any code snippets to investigate? Do you mean a query that'd return 9000 rows or do you mean querying 9000 rows as ID'd be their unique ID? (I think you mean latter, just want to clarify) — jamheadart, Jul 09 '18 at 19:14
This certainly acts like a memory buffer overflow. How big is the actual query string when it fails - I am currently wagering somewhere around 65,535 bytes long. — Laughing Vergil, Jul 09 '18 at 19:33
The query is a simple select statement used to retrieve data for a list of product IDs that vary in count, but the list always contains over 10,000 product IDs. So far when I query anything over 9,000 rows, the macro fails without any error message. However, when I query directly in SQL, I get results for the full test dataset (12,048 rows) in less than 2 min. I'd like to have the query built into the report to automate it as much as possible. I am about to add the code now. — user10055442, Jul 09 '18 at 20:25

score 0 · Answer 1 · answered Jul 09 '18 at 19:35

I know in Oracle there is a 1,000 value limit when using the SELECT IN clause.

According to this post there is a limit (in SQL Server), somewhere in the thousands. It suggests loading the list of IDs into a table and then doing a query against the table.

J. Chris Compton · Answer 2 · 2018-07-10T19:53:39.113

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632.

Error 8623:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632:

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

Reference Microsoft docs

Example you can run in SQL Server Management Studio
(or directly against the database from Excel if you don't have (they're free) a version of SSMS).
Comment out the DROP statement if you want to leave the tables in place.

--Sample SQL https://stackoverflow.com/questions/51252536/sql-vba-query-will-only-return-results-on-small-dataset/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'CC') 
BEGIN
-- Create a new schema name so it does not interfere with existing tables
EXEC sys.sp_executesql N'CREATE SCHEMA [CC] AUTHORIZATION [dbo]'

-- Create the table that holds the values which your user chooses
CREATE TABLE [CC].[KeyList]([RowKeysYouWant] [int] NOT NULL) ON [PRIMARY]
ALTER AUTHORIZATION ON [CC].[KeyList] TO  SCHEMA OWNER

-- This is the existing table which has your data in it
CREATE TABLE [CC].[TableWithData]([pk] [int] NOT NULL,
    [data1] [char](2) NULL,[data2] [char](2) NULL) ON [PRIMARY]
ALTER AUTHORIZATION ON [CC].[TableWithData] TO  SCHEMA OWNER 

-- here are the rows that you want (put list of chosen items in here)
INSERT [CC].[KeyList] ([RowKeysYouWant]) VALUES (1),(2),(3),(5),(7)
-- this is your data table (fields data1 and data2 become null by default)
INSERT [CC].[TableWithData] ([pk]) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)

-- This is the select you use to grab all the records in table KeyList
SELECT * FROM CC.TableWithData WHERE pk IN (SELECT RowKeysYouWant FROM CC.KeyList)

DROP TABLE [CC].[TableWithData]; DROP TABLE [CC].[KeyList]; DROP SCHEMA [CC]
END
GO

You'll need permissions to create a schema and to create tables to run this.
If you don't have those permissions at work, just install a copy of SQL Server Express edition on your home PC and run the script there.
Get your SQL working first, then worry about how to get it to work in VBA. :-)

Thanks for the feedback. I am unfamiliar with the syntax required for this. Would you mind posting a sample query? I've been trying variations of the query below with no luck. I keep getting error message 2, "Incorrect syntax near '0'. Query: select * into InvalidFilterLOVs from (Select NAME, GuidID, DATASTANDARDS_PATH, PRODUCT_NAME_120, MARKETING_COPY, BULLET01, BULLET02, BULLET03, BULLET04, BULLET05, BULLET06, WORKFLOWSTATE, CHANNELSTATUS, THDONLINESTATUS from aaa.dbo.[ABCDEFG] inner join bbb.dbo.[HIJKLMNOP] on name = OMSID where omsid in ('123456789')) — user10055442, Jul 10 '18 at 13:08
I'll add an example to the answer. In short: You will do an insert of the keys you want into a table (standard or temp table; my example is standard). All you are storing is the list of keys (if you have a composit key, you'll need multiple fields). Then you will use that list of keys to get your actual data rows. — J. Chris Compton, Jul 10 '18 at 18:57

SQL VBA Query Will Only Return Results on Small Dataset

2 Answers2