Long Queries Hang VS; Other Productivity Killers

Whenever I run long SQL in VS whether it's in the Data tab or in Preview
mode, I can't seem to get a report out of it. I'm talking SQL in excess of
200 lines.
I can run the SQL fine in Query Analyzer but running it in the Data tab, it
freezes VS, and when I run the Preview, the report never renders. I have even
tried hardcoding all parameters, creating a stored procedure, and running the
report off a view. None have resolved the issue.
On a possibly related note, my connection to the database times out
intermittently, saying:
--
Microsoft Development Environment
--
An error occurred while executing the query.
General network error. Check your network documentation.
--
OK
--
This absolutely kills me over the course of a day. I have to shut down VS
and restart it.
Is my Reporting Services VS package install corrupt? What do I do?How many fields are being returned? How many rows of data?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> Whenever I run long SQL in VS whether it's in the Data tab or in Preview
> mode, I can't seem to get a report out of it. I'm talking SQL in excess of
> 200 lines.
> I can run the SQL fine in Query Analyzer but running it in the Data tab,
it
> freezes VS, and when I run the Preview, the report never renders. I have
even
> tried hardcoding all parameters, creating a stored procedure, and running
the
> report off a view. None have resolved the issue.
> On a possibly related note, my connection to the database times out
> intermittently, saying:
> --
> Microsoft Development Environment
> --
> An error occurred while executing the query.
> General network error. Check your network documentation.
> --
> OK
> --
> This absolutely kills me over the course of a day. I have to shut down VS
> and restart it.
> Is my Reporting Services VS package install corrupt? What do I do?|||For one report in particular, about 727 rows, and today it's taking just over
4 minutes to run. The other has over 60,000 rows and I'm just going to have
to work on limiting that severely.
"Bruce L-C [MVP]" wrote:
> How many fields are being returned? How many rows of data?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > Whenever I run long SQL in VS whether it's in the Data tab or in Preview
> > mode, I can't seem to get a report out of it. I'm talking SQL in excess of
> > 200 lines.
> >
> > I can run the SQL fine in Query Analyzer but running it in the Data tab,
> it
> > freezes VS, and when I run the Preview, the report never renders. I have
> even
> > tried hardcoding all parameters, creating a stored procedure, and running
> the
> > report off a view. None have resolved the issue.
> >
> > On a possibly related note, my connection to the database times out
> > intermittently, saying:
> >
> > --
> > Microsoft Development Environment
> > --
> > An error occurred while executing the query.
> >
> > General network error. Check your network documentation.
> > --
> > OK
> > --
> >
> > This absolutely kills me over the course of a day. I have to shut down VS
> > and restart it.
> >
> > Is my Reporting Services VS package install corrupt? What do I do?
>
>|||The 727 rows should not be a problem. Is that one causing an issue from the
data tab?
It doesn't surprise me that the 60,000 row one has problems.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> For one report in particular, about 727 rows, and today it's taking just
over
> 4 minutes to run. The other has over 60,000 rows and I'm just going to
have
> to work on limiting that severely.
> "Bruce L-C [MVP]" wrote:
> > How many fields are being returned? How many rows of data?
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > Whenever I run long SQL in VS whether it's in the Data tab or in
Preview
> > > mode, I can't seem to get a report out of it. I'm talking SQL in
excess of
> > > 200 lines.
> > >
> > > I can run the SQL fine in Query Analyzer but running it in the Data
tab,
> > it
> > > freezes VS, and when I run the Preview, the report never renders. I
have
> > even
> > > tried hardcoding all parameters, creating a stored procedure, and
running
> > the
> > > report off a view. None have resolved the issue.
> > >
> > > On a possibly related note, my connection to the database times out
> > > intermittently, saying:
> > >
> > > --
> > > Microsoft Development Environment
> > > --
> > > An error occurred while executing the query.
> > >
> > > General network error. Check your network documentation.
> > > --
> > > OK
> > > --
> > >
> > > This absolutely kills me over the course of a day. I have to shut down
VS
> > > and restart it.
> > >
> > > Is my Reporting Services VS package install corrupt? What do I do?
> >
> >
> >|||For the most part, Mr. 727 will run on the data tab, but clocks endlessly on
Preview. The report does consist of about 20 fields, but I would still expect
it to render.
Sometimes I just lose connectivity and have to close out VS. I'm not sure if
it's related, but it could be I suppose.
Any ideas?
"Bruce L-C [MVP]" wrote:
> The 727 rows should not be a problem. Is that one causing an issue from the
> data tab?
> It doesn't surprise me that the 60,000 row one has problems.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > For one report in particular, about 727 rows, and today it's taking just
> over
> > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> have
> > to work on limiting that severely.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > How many fields are being returned? How many rows of data?
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> Preview
> > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> excess of
> > > > 200 lines.
> > > >
> > > > I can run the SQL fine in Query Analyzer but running it in the Data
> tab,
> > > it
> > > > freezes VS, and when I run the Preview, the report never renders. I
> have
> > > even
> > > > tried hardcoding all parameters, creating a stored procedure, and
> running
> > > the
> > > > report off a view. None have resolved the issue.
> > > >
> > > > On a possibly related note, my connection to the database times out
> > > > intermittently, saying:
> > > >
> > > > --
> > > > Microsoft Development Environment
> > > > --
> > > > An error occurred while executing the query.
> > > >
> > > > General network error. Check your network documentation.
> > > > --
> > > > OK
> > > > --
> > > >
> > > > This absolutely kills me over the course of a day. I have to shut down
> VS
> > > > and restart it.
> > > >
> > > > Is my Reporting Services VS package install corrupt? What do I do?
> > >
> > >
> > >
>
>|||Sometimes it runs from the data tab. Sometimes (like now), it hoses VS. The
query is 309 lines long and is pretty complex...Still, I'm having a heck of a
time. Could it be my RS client install, or could it be my machine?
"Bruce L-C [MVP]" wrote:
> The 727 rows should not be a problem. Is that one causing an issue from the
> data tab?
> It doesn't surprise me that the 60,000 row one has problems.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > For one report in particular, about 727 rows, and today it's taking just
> over
> > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> have
> > to work on limiting that severely.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > How many fields are being returned? How many rows of data?
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> Preview
> > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> excess of
> > > > 200 lines.
> > > >
> > > > I can run the SQL fine in Query Analyzer but running it in the Data
> tab,
> > > it
> > > > freezes VS, and when I run the Preview, the report never renders. I
> have
> > > even
> > > > tried hardcoding all parameters, creating a stored procedure, and
> running
> > > the
> > > > report off a view. None have resolved the issue.
> > > >
> > > > On a possibly related note, my connection to the database times out
> > > > intermittently, saying:
> > > >
> > > > --
> > > > Microsoft Development Environment
> > > > --
> > > > An error occurred while executing the query.
> > > >
> > > > General network error. Check your network documentation.
> > > > --
> > > > OK
> > > > --
> > > >
> > > > This absolutely kills me over the course of a day. I have to shut down
> VS
> > > > and restart it.
> > > >
> > > > Is my Reporting Services VS package install corrupt? What do I do?
> > >
> > >
> > >
>
>|||Did you say you tried it as a stored procedure and you still had problems.
20 fields should not be a problem. 309 lines of a query could be a problem
in the IDE (not necessarily when deployed). I suggest try deploying it and
see if it works to determine if it is an IDE only problem. Next, try having
it in a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
The
> query is 309 lines long and is pretty complex...Still, I'm having a heck
of a
> time. Could it be my RS client install, or could it be my machine?
> "Bruce L-C [MVP]" wrote:
> > The 727 rows should not be a problem. Is that one causing an issue from
the
> > data tab?
> >
> > It doesn't surprise me that the 60,000 row one has problems.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > For one report in particular, about 727 rows, and today it's taking
just
> > over
> > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > have
> > > to work on limiting that severely.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > How many fields are being returned? How many rows of data?
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > Preview
> > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > excess of
> > > > > 200 lines.
> > > > >
> > > > > I can run the SQL fine in Query Analyzer but running it in the
Data
> > tab,
> > > > it
> > > > > freezes VS, and when I run the Preview, the report never renders.
I
> > have
> > > > even
> > > > > tried hardcoding all parameters, creating a stored procedure, and
> > running
> > > > the
> > > > > report off a view. None have resolved the issue.
> > > > >
> > > > > On a possibly related note, my connection to the database times
out
> > > > > intermittently, saying:
> > > > >
> > > > > --
> > > > > Microsoft Development Environment
> > > > > --
> > > > > An error occurred while executing the query.
> > > > >
> > > > > General network error. Check your network documentation.
> > > > > --
> > > > > OK
> > > > > --
> > > > >
> > > > > This absolutely kills me over the course of a day. I have to shut
down
> > VS
> > > > > and restart it.
> > > > >
> > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > >
> > > >
> > > >
> >
> >
> >|||Yes, I did give it a shot as a stored procedure, with no luck.
Well, tomorrow's another day. I'll try deploying it and seeing what happens.
Thanks for the help.
Mike
"Bruce L-C [MVP]" wrote:
> Did you say you tried it as a stored procedure and you still had problems.
> 20 fields should not be a problem. 309 lines of a query could be a problem
> in the IDE (not necessarily when deployed). I suggest try deploying it and
> see if it works to determine if it is an IDE only problem. Next, try having
> it in a stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> > Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
> The
> > query is 309 lines long and is pretty complex...Still, I'm having a heck
> of a
> > time. Could it be my RS client install, or could it be my machine?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > The 727 rows should not be a problem. Is that one causing an issue from
> the
> > > data tab?
> > >
> > > It doesn't surprise me that the 60,000 row one has problems.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > > For one report in particular, about 727 rows, and today it's taking
> just
> > > over
> > > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > > have
> > > > to work on limiting that severely.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > How many fields are being returned? How many rows of data?
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > > Preview
> > > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > > excess of
> > > > > > 200 lines.
> > > > > >
> > > > > > I can run the SQL fine in Query Analyzer but running it in the
> Data
> > > tab,
> > > > > it
> > > > > > freezes VS, and when I run the Preview, the report never renders.
> I
> > > have
> > > > > even
> > > > > > tried hardcoding all parameters, creating a stored procedure, and
> > > running
> > > > > the
> > > > > > report off a view. None have resolved the issue.
> > > > > >
> > > > > > On a possibly related note, my connection to the database times
> out
> > > > > > intermittently, saying:
> > > > > >
> > > > > > --
> > > > > > Microsoft Development Environment
> > > > > > --
> > > > > > An error occurred while executing the query.
> > > > > >
> > > > > > General network error. Check your network documentation.
> > > > > > --
> > > > > > OK
> > > > > > --
> > > > > >
> > > > > > This absolutely kills me over the course of a day. I have to shut
> down
> > > VS
> > > > > > and restart it.
> > > > > >
> > > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Have you tried changing the connection string of the data source to increase
the time out?
try
"data source=ServerName;initial catalog=DatabaseName;Connection Timeout=30"
The connection timeout has a default of 15.
"Bassist695" wrote:
> Yes, I did give it a shot as a stored procedure, with no luck.
> Well, tomorrow's another day. I'll try deploying it and seeing what happens.
> Thanks for the help.
> Mike
> "Bruce L-C [MVP]" wrote:
> > Did you say you tried it as a stored procedure and you still had problems.
> > 20 fields should not be a problem. 309 lines of a query could be a problem
> > in the IDE (not necessarily when deployed). I suggest try deploying it and
> > see if it works to determine if it is an IDE only problem. Next, try having
> > it in a stored procedure.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> > > Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
> > The
> > > query is 309 lines long and is pretty complex...Still, I'm having a heck
> > of a
> > > time. Could it be my RS client install, or could it be my machine?
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > The 727 rows should not be a problem. Is that one causing an issue from
> > the
> > > > data tab?
> > > >
> > > > It doesn't surprise me that the 60,000 row one has problems.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > > > For one report in particular, about 727 rows, and today it's taking
> > just
> > > > over
> > > > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > > > have
> > > > > to work on limiting that severely.
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > How many fields are being returned? How many rows of data?
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > > > Preview
> > > > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > > > excess of
> > > > > > > 200 lines.
> > > > > > >
> > > > > > > I can run the SQL fine in Query Analyzer but running it in the
> > Data
> > > > tab,
> > > > > > it
> > > > > > > freezes VS, and when I run the Preview, the report never renders.
> > I
> > > > have
> > > > > > even
> > > > > > > tried hardcoding all parameters, creating a stored procedure, and
> > > > running
> > > > > > the
> > > > > > > report off a view. None have resolved the issue.
> > > > > > >
> > > > > > > On a possibly related note, my connection to the database times
> > out
> > > > > > > intermittently, saying:
> > > > > > >
> > > > > > > --
> > > > > > > Microsoft Development Environment
> > > > > > > --
> > > > > > > An error occurred while executing the query.
> > > > > > >
> > > > > > > General network error. Check your network documentation.
> > > > > > > --
> > > > > > > OK
> > > > > > > --
> > > > > > >
> > > > > > > This absolutely kills me over the course of a day. I have to shut
> > down
> > > > VS
> > > > > > > and restart it.
> > > > > > >
> > > > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||No, I haven't tried that. I don't believe the queries are timing out.
Wouldn't I get an error message to indicate that?
At present, there is no timeout set, which I believe means that the query
will not time out.
I'm going to try the deploy-and-see method...Let's hope it's my IDE.
Mike
"Carl Finnegan" wrote:
> Have you tried changing the connection string of the data source to increase
> the time out?
> try
> "data source=ServerName;initial catalog=DatabaseName;Connection Timeout=30"
> The connection timeout has a default of 15.
> "Bassist695" wrote:
> > Yes, I did give it a shot as a stored procedure, with no luck.
> >
> > Well, tomorrow's another day. I'll try deploying it and seeing what happens.
> > Thanks for the help.
> >
> > Mike
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Did you say you tried it as a stored procedure and you still had problems.
> > > 20 fields should not be a problem. 309 lines of a query could be a problem
> > > in the IDE (not necessarily when deployed). I suggest try deploying it and
> > > see if it works to determine if it is an IDE only problem. Next, try having
> > > it in a stored procedure.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> > > > Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
> > > The
> > > > query is 309 lines long and is pretty complex...Still, I'm having a heck
> > > of a
> > > > time. Could it be my RS client install, or could it be my machine?
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > The 727 rows should not be a problem. Is that one causing an issue from
> > > the
> > > > > data tab?
> > > > >
> > > > > It doesn't surprise me that the 60,000 row one has problems.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > > > > For one report in particular, about 727 rows, and today it's taking
> > > just
> > > > > over
> > > > > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > > > > have
> > > > > > to work on limiting that severely.
> > > > > >
> > > > > > "Bruce L-C [MVP]" wrote:
> > > > > >
> > > > > > > How many fields are being returned? How many rows of data?
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Bruce Loehle-Conger
> > > > > > > MVP SQL Server Reporting Services
> > > > > > >
> > > > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > > > > Preview
> > > > > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > > > > excess of
> > > > > > > > 200 lines.
> > > > > > > >
> > > > > > > > I can run the SQL fine in Query Analyzer but running it in the
> > > Data
> > > > > tab,
> > > > > > > it
> > > > > > > > freezes VS, and when I run the Preview, the report never renders.
> > > I
> > > > > have
> > > > > > > even
> > > > > > > > tried hardcoding all parameters, creating a stored procedure, and
> > > > > running
> > > > > > > the
> > > > > > > > report off a view. None have resolved the issue.
> > > > > > > >
> > > > > > > > On a possibly related note, my connection to the database times
> > > out
> > > > > > > > intermittently, saying:
> > > > > > > >
> > > > > > > > --
> > > > > > > > Microsoft Development Environment
> > > > > > > > --
> > > > > > > > An error occurred while executing the query.
> > > > > > > >
> > > > > > > > General network error. Check your network documentation.
> > > > > > > > --
> > > > > > > > OK
> > > > > > > > --
> > > > > > > >
> > > > > > > > This absolutely kills me over the course of a day. I have to shut
> > > down
> > > > > VS
> > > > > > > > and restart it.
> > > > > > > >
> > > > > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >