| Author |
Topic |
|
Dannette
Starting Member
6 Posts |
 Posted - 2008-08-28 : 18:33:29
|
| I'm not sure which topic this should go under so I'm hoping here's the place. I'm trying to run a very large query in SQL server 2005; it has 295k lines. I've found that a query larger than 87-93,000 lines will not run -- the Results pane vanishes and there are no messages in the Message pane, but the status bar at the bottom says the query completed with errors. There is no way it ran the whole query, because it took less time than it did to run 85,000 lines when I took out most of them. I tried sending results to a file (with 295,000 lines) but the file is empty. It does the same with 95-100,000 lines.Coworkers have the same problem. My boss has run large queries in SQL server 2000 with no problem. I tried running the queries in SQL developer from Oracle, and it works fine unless there are ampersands in the text (there's no "set define off" in SQL developer as far as I can find online or in the help), in which case it thinks they're substitution variables and I can't seem to turn that off.I'm completely out of ideas on this. If anybody has the slightest bit of information it would help me enormously. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-28 : 18:59:25
|
| You are limited in the number of objects that you can call in a single query. Any idea how many tables/views are in there?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-28 : 19:20:32
|
| Is is a script with 295K lines, or just a single query with 295K lines?Also, why would a query be so large?CODO ERGO SUM |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-28 : 20:26:02
|
| Perhaps you mean there are 295K lines of OUTPUT from your query?Be One with the OptimizerTG |
|
|
|
Dannette
Starting Member
6 Posts |
Posted - 2008-08-29 : 11:22:49
|
| Thanks for the replies! Sorry, I should have given more info. I'm putting in a lot of data via insert into statements. Each line is one statement, and the number of values inserted can be anywhere from 10 to 90 (in this case, it's nowhere near 90). I have a VB script in Access that takes data from one version of a database and generates a .sql file with the data rearranged for the next version. Most of the files are not this big. I know there are probably sixty better ways to do this, but they're probably not as flexible as this way and it has to work for many different clients with only minor adjustments. (I'm obviously a little new to this.)In this query I'm putting data into 7 tables, but even if I cut it down to just the biggest table (176,714 lines) it gives me the problem I gave above. In this table there are 10 attributes for which I'm inserting data. I have found that the fewer the number of values inserted in each statement, the more lines I can put in. I can only guess it's because of a lack of memory, but it doesn't say so (it has said so with some shorter queries when I had a lot of programs running) and if it were Oracle it would work fine -- on other projects we put the data into an Oracle database with SQL+.If anybody has an equivalent command for "set define off" in Oracle's SQL developer or some other way to tell it that ampersands do not indicate substitution variables, that would solve the problem also; I could just run the query in that program instead.Thanks again for your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-29 : 11:44:10
|
| You should be using BULK INSERT/bcp/SSIS/DTS/import and export for this and not INSERT commands. The data to be inserted should be into files as raw data, such as in a csv file.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Dannette
Starting Member
6 Posts |
Posted - 2008-08-29 : 11:57:48
|
| This is the way we do it. I'll make the suggestion, but this is how my boss has it set up and I've only been here a few months. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-29 : 14:32:27
|
Fire your boss.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-29 : 14:43:56
|
quote:
Originally posted by Peso
Fire your boss.
E 12°55'05.63"N 56°04'39.26"
Finally - after over 18K posts - Peso finally gives some worthwhile advice that really makes sense  It's so simple, I should have thought of that years ago. Now if I could just get rid of these pesky clients...Be One with the OptimizerTG |
|
|
|
Dannette
Starting Member
6 Posts |
Posted - 2008-08-29 : 14:44:04
|
| Actually, I talked to him about it and his response was, how about you do that? So I'm currently setting up the VB script to use a .csv file instead of the insert statements. :) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-29 : 14:47:20
|
See, sometimes you can make a difference.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-29 : 14:59:58
|
quote:
Originally posted by Peso
See, sometimes you can make a difference.
E 12°55'05.63"N 56°04'39.26"
Yes, you might get her fired.CODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-29 : 15:07:23
|
For suggesting a better approach than the boss have made?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Dannette
Starting Member
6 Posts |
Posted - 2008-08-29 : 15:15:14
|
| What one does is often less important than how one does it.Anyway, this is a startup, good ideas are rewarded here -- as long as you're willing to be the one to implement them! |
|
|
|
rs
Starting Member
1 Post |
Posted - 2011-05-20 : 07:15:01
|
quote:
You should be using BULK INSERT/bcp/SSIS/DTS/import and export for this and not INSERT commands. The data to be inserted should be into files as raw data, such as in a csv file.
I ran into this problem just now.I agree, there are more optimal ways to do this, but there is nothing wrong with (a lot of) insert statements. In my case, I just received a file to perform a one-time data synchronization.The actual problem here, is that were are spending a lot of time, because SQL Server Management Studio fails to respond with a clear message to an unsupported user action.Why is this always so hard for Microsoft?  |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-05-20 : 10:59:02
|
quote:
Originally posted by rs
quote:
You should be using BULK INSERT/bcp/SSIS/DTS/import and export for this and not INSERT commands. The data to be inserted should be into files as raw data, such as in a csv file.
I ran into this problem just now.I agree, there are more optimal ways to do this, but there is nothing wrong with (a lot of) insert statements. In my case, I just received a file to perform a one-time data synchronization.The actual problem here, is that were are spending a lot of time, because SQL Server Management Studio fails to respond with a clear message to an unsupported user action.Why is this always so hard for Microsoft?
So, everything's fine, except there's a problem, and Microsoft is to blame because you can't figure it out?CODO ERGO SUM |
|
|
|
|
Can a query have too many lines?