0

I am trying to run a large query set up at work, maybe a stored procedure.?. I am unfamiliar with large queries but i think that is the issue. I can use pyodbc to connect and run a simple "SELECT * FROM db;" and it works fine, and if i run the full query in MSSMS and it works fine, but not when I copy the large query into the query variable I have found a few articles that say to add "SET NOCOUNT NO;" I tried that and it didn't work either.

the python error is:

"No results. Previous SQL is not a query"

Once again the query works fine in MSSMS so any guidance would be appreciated.

thanks

ryan muir
  • 21
  • 5
  • Did you try to simplify your query as much as possible, when you still get the error, but the next step removes the error? This is the way to go: you'll get a minimal reproducible example or locate the statement, which causes the error – astentx Jun 29 '21 at 20:57
  • When you run the "query" in SSMS you probably see the results in the "Results" tab, but do you see any messages in the "Messages" tab other than "Commands completed successfully." and "Completion time: …"? – Gord Thompson Jun 29 '21 at 21:29
  • Please post SQL query for us to help. I think you had an earlier question that you probably deleted with same issue of working in SSMS but not Python. I recall commenters found syntax errors and unreferenced columns that could not have run successfully in SQL Server. – Parfait Jun 29 '21 at 22:32
  • @astentx Honestly I have not, that is a good suggestion. I am unfamiliar with the some of the syntax. I have worked with normal selects, joins some cte...this contains declares @ and at the end has an exec(SQL) does that mean its a stored procedure. sorry for the vagueness. – ryan muir Jun 30 '21 at 00:07
  • @GordThompson the messsage does contain content. I cant copy and paste as it is on a work computer and not sure what is appropriate to share. it has several rows affected and a SELECT and JOIN statement. – ryan muir Jun 30 '21 at 01:06
  • "the messsage does contain content" - then that is what is getting in the way of retrieving the result set, instead giving you "Previous SQL is not a query". Adding `SET NOCOUNT ON;` to the beginning of the anonymous code block is the usual way to fix that. – Gord Thompson Jun 30 '21 at 12:14
  • @GordThompson Placing ```SET NOCOUNT ON;``` at the very top returned 42000 error...incorrect syntax near 'GO' – ryan muir Jun 30 '21 at 18:59
  • https://stackoverflow.com/a/49140908/2144390 – Gord Thompson Jun 30 '21 at 19:38
  • @GordThompson I believe it is a stored procedure but the ```create procedure ``` statement is commented out, which I guess you do after the first run? but then there is other content in the query that is using part of the stored procedure with other table calls? i thought i read i may need to execute the stored procedure then follow it by the rest of the SQL..then it asked for the params of the SP and couldn't find what those would be....thanks for giving your thoughts – ryan muir Jun 30 '21 at 20:14
  • @GordThompson unfortunately, adding ```SET NOCOUNT ON``` and removing ```GO``` didn't work. the pyodbc query still returns ```No Results. Previous SQL was not a query.``` – ryan muir Jun 30 '21 at 23:19
  • @GordThompson the query says it uses stored procedures by names that i cant find in the database when querying ```select * from sys.parameters where name like %name%;``` the query is at least 300 lines long...just doesn't make sense... – ryan muir Jun 30 '21 at 23:26

0 Answers0