Can I incorporate scripts setting variables and while loops, etc. in the "QB Query" of the Query Bank?
I have a SQL Server script that works perfectly in my local dev DB but it doesnt play nice with Volusion.
I don't know if I should spend more time figuring it out or just stop because it isn't even possible.
You can't do it in the Custom Queries / Query Bank area. I believe their system will stop executing the script when it encounters certain keywords or punctuation. A work around is to create a .sql file that contains your script and place it in your vspfiles/schema/Generic folder. You'll also need an .xsd file with the same name. The contents of the xsd file aren't very important - you can reuse the contents from an existing one (search their support pages for Developer Resources to find examples). Once the sql and xsd files are in place, you can execute the SQL in the .sql file by using the URL/API method, like this...
http://www.MYWEBSITE.com/net/[email protected]&EncryptedPassword=XXXXXXXXXXXXXXXXX&EDI_Name=Generic\FILENAME (<-- minus the .sql extension)
you'll need to replace several things above of course. But this works well for us. One thing to note, if you automate the creation and execution of these files, it's slow/inefficient on their system and could slow your site down, depending on how often you do it.