Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elger
Starting Member
7 Posts |
 Posted - 2003-04-25 : 10:14:23
|
| Hi all,I want to execute a query from a .sql filesay the file select1.sql contains: select * from sometable The reason that I cannot use stored procedures is thatI want users to alter queries to their likings.Does anyone know if it is possible to execute .sql files?Thanks,Elger |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-25 : 10:20:01
|
Perhaps a statement like:DROP DATABSE Master ????????????????????????Brett8-) |
 |
|
|
elger
Starting Member
7 Posts |
Posted - 2003-04-25 : 10:28:34
|
| I tried that, now nothing works? ;-)but seriously, I have a set of profiles per customer whocan get a hitlist out of a complex query. These query's aretoo large for sp_executesql (some of them are beyond 8000 characters).So I thought I can build a dynamic sql query from a website and store that into a file, and after that executing it. |
|
|
|
mtomeo
Starting Member
30 Posts |
Posted - 2003-04-25 : 11:15:55
|
| I'm really reaching here, but I guess you could use ISQL (command line utility). Check BOL for the syntax, but it's pretty much like this:Trusted:ISQL /E /SDBSERVER /dDBNAME /iFile.sqlUser/Pwd:ISQL /UUSER /PPASS /SDBSERVER /dDBNAME /iFile.sqlBut I've never tried this in a web app (only batch scripts), so I'm not sure of the exact logistics of it. Personally, I would have tge web app exec xp_cmdshell and pass it the ISQL statement (if your web app can run a batch command, even better). I would also make sure the .sql file is somewhere SQL Server has permission to see it. I'm not sure where the output would go, so you would probably write the output to a file, return the location and name (which you may already know cuz you are passing the ISQL statement), and then read that back into your web app. Am I crazy? Would this work? It's probably overkill and there's a real simple way to execute a SQL file, but like I said, I've never tried it. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-25 : 11:16:03
|
You could use DAO oops ADO and to execute a text command. Not sure what the limit is, though.greater than 8000 characters ? have you considered writing some views in your database -- those will really cut down on those long SELECT's.can you explain this further or give an example:quote:
I have a set of profiles per customer who can get a hitlist out of a complex query
I'm not following that ....- JeffEdited by - jsmith8858 on 04/25/2003 11:30:24 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-25 : 12:27:26
|
quote:
I'm really reaching here, but I guess you could use ISQL (command line utility). Check BOL for the syntax, but it's pretty much like this:Trusted:ISQL /E /SDBSERVER /dDBNAME /iFile.sqlUser/Pwd:ISQL /UUSER /PPASS /SDBSERVER /dDBNAME /iFile.sqlBut I've never tried this in a web app (only batch scripts), so I'm not sure of the exact logistics of it. Personally, I would have tge web app exec xp_cmdshell and pass it the ISQL statement (if your web app can run a batch command, even better). I would also make sure the .sql file is somewhere SQL Server has permission to see it. I'm not sure where the output would go, so you would probably write the output to a file, return the location and name (which you may already know cuz you are passing the ISQL statement), and then read that back into your web app. Am I crazy? Would this work? It's probably overkill and there's a real simple way to execute a SQL file, but like I said, I've never tried it.
Instead of ISQL, use OSQL. OSQL is the replacement for ISQL although ISQL is still available in SQL2k.Tara |
|
|
|
elger
Starting Member
7 Posts |
Posted - 2003-04-25 : 12:58:54
|
| -mtomeo: thanx, ill try that on monday (It's european time here ;-))-jsmith8858: Yes, I know, I'm stuck with old query's that have been rebuilt to a new database.. I'ts mainly searching for keywords. Using like '%car% OR like '%seat%' etc. But then over many different tables. I am currently using views, but the query's are still rediculously long. I'm looking at ways to reduce them, but still they remain very long.-tduggan: ok, osql it is :PThanks for the replys all and have a nice weekend. |
|
|
|
|
|
|
|
|
How to execute a query from .sql file?