Is it possible to run dynamic queries outside of a stored procedure?

Database

Is it possible to run dynamic queries outside of a stored procedure?

I'm trying to use my SQL Server to extract and store the results of dynamic queries executed on our Teradata server, but ran into technical difficulties - namely an 8k character limit on queries using OpenRowset, about half our queries are bigger than that.

An alternative I've been researching but haven't been able to get to work is to use dynamic queries directly in Teradata, but unfortunately I haven't been able to figure out how to get it to work. I would ideally stored the queries in a table in Teradata, and then execute them remotely from SQL Server (since I not have to send the query across I could bypass the 8k limit). If I can figure out how to run them in Teradata SQL Assistant I can handle the SQL Server side.

I don't have the authority to create stored procedures, but I can create macros. Another option I was considering is BTEQ but I have no experience with that.

I've been wanting to get some good manuals for Teradata but it seems they don't exist. Also I strongly recommend updating your web server - it can take 5 minutes to load a simple page.
Tags (1)
1 REPLY

Re: Is it possible to run dynamic queries outside of a stored procedure?

If you can generate the SQL into a file, you can run the SQL from within BTEQ using ".run" command.

If you're trying to run this process in a batch mode rather than interactively, BTEQ would probably work better for you than SQL Assistant.

You can get to all of the manuals here:

www.info.teradata.com

For the BTEQ manual, select "Teradata Tools and Utilities" and you should see the "Basic Teradata Query (BTEQ)" manual listed.