SQL error, please help - "Invalid Cursor State"

[mattd17]

Trying to pull monthly data for a portfolio, I have 600k+ rows on 30+ fields. ODBC connection seems fine, but when importing the code I get the following errors - looks like it might be a memory management issue? Would appreciate the help from anyone. (Blacked out server names)

[patrick_mcauliffe]

Are you calling any stored procedures on the SQL?

[mattd17]

No, I just copied my usual query from SQL Server Studio into Alteryx

[patrick_mcauliffe]

@mattd17

This is a little bit of a long shot; but the SQL editor/Input Data tool doesn't handle text that's copied and pasted from other SQL editors well.  Usually the cause is rich text format which most other sql editors have and this one doesn't.  To check for this being the cause, you may have to re-write your sql in the sql editor within Alteryx. 

Sometimes I've heard of users having luck by pasting into Notepad (not Notepad++, Word, or any other application), saving it, closing, re-opening, then copy and paste.  Plain old Notepad doesn't support rich text and this will eliminate the chance of that getting pasted as well.

It's worth a try.  

[mattd17]

I tried pasting into Notepad first, and it looks like it might've cleared the first error but now I'm getting the error message below. Any chance you can help out with that one?

"Error: Input Data (3): Error SQLExecute: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Cursors are not supported on a table which has a clustered columnstore index.[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Statement(s) could not be prepared."

Thanks,

Matt

[patrick_mcauliffe]

@mattd17  I'd have to look at the SQL and know a bit more about the database to say for sure.  Better to check with a DBA.

[jwest · Accepted solution]

I've had this error before and adding a "BEGIN" before your query then "SET NOCOUNT ON END" after solved the problem.

So it should look like this:

BEGIN 

/***Insert Query Here***/

SET NOCOUNT ON

END

[jeanfgomess1]

This works! God bless you, my friend!🙂

[NathanOch]

This worked for me as well. Thank you!

[DeanHoffman]

Kudos, it worked perfectly! Thank you!