Annoying problem with SQL Server 2000 Query Analyzer

When I open a saved sql file it opens fine, but when I try to load a
different file in the same query window, QA hangs on "Loading SQL Query...".
Does anybody have any ideas on why this is happening? I already removed and
re-installed SQL Server (client tools only) and applied SP3.
Thanks
Dan
Have a look at
http://support.microsoft.com/default.aspx?kbid=830767
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Daniel Reber" <nospam@.nospam.com> wrote in message
news:eFtkS1SKEHA.2144@.TK2MSFTNGP10.phx.gbl...
> When I open a saved sql file it opens fine, but when I try to load a
> different file in the same query window, QA hangs on "Loading SQL
Query...".
> Does anybody have any ideas on why this is happening? I already removed
and
> re-installed SQL Server (client tools only) and applied SP3.
> Thanks
> Dan
>
|||Thanks for the link...I am on hold with MS right now to get the hotfix.
Terrible music...
Dan Reber
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:O7c6naTKEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Have a look at
> http://support.microsoft.com/default.aspx?kbid=830767
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Daniel Reber" <nospam@.nospam.com> wrote in message
> news:eFtkS1SKEHA.2144@.TK2MSFTNGP10.phx.gbl...
> Query...".
> and
>
|||Hi Daniel,
It seems you request the hotfix mentioned in the article:
830767 FIX: SQL Query Analyzer May Stop Responding When You Close a Query
http://support.microsoft.com/?id=830767
Please contact Microsoft Product Support Services to obtain the hotfix. For
a complete list of Microsoft Product Support Services phone numbers and
information about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||The hotfix corrected my issue, thanks.
Dan Reber
""Yuan Shao"" <v-yshao@.online.microsoft.com> wrote in message
news:jWUPnlUKEHA.2360@.cpmsftngxa10.phx.gbl...
> Hi Daniel,
> It seems you request the hotfix mentioned in the article:
> 830767 FIX: SQL Query Analyzer May Stop Responding When You Close a Query
> http://support.microsoft.com/?id=830767
> Please contact Microsoft Product Support Services to obtain the hotfix.
For
> a complete list of Microsoft Product Support Services phone numbers and
> information about support costs, visit the following Microsoft Web site:
> http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>
|||What number do you call? I'm not spending 99 bucks for this hotfix!
BigMac
"Daniel Reber" <nospam@.nospam.com> wrote in message
news:OiS5GsTKEHA.1192@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Thanks for the link...I am on hold with MS right now to get the hotfix.
> Terrible music...
> Dan Reber
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:O7c6naTKEHA.1192@.TK2MSFTNGP11.phx.gbl...
removed
>
|||How much did it cost, Daniel?
BigMac
"Daniel Reber" <nospam@.nospam.com> wrote in message
news:%23wkc66UKEHA.3472@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> The hotfix corrected my issue, thanks.
> Dan Reber
> ""Yuan Shao"" <v-yshao@.online.microsoft.com> wrote in message
> news:jWUPnlUKEHA.2360@.cpmsftngxa10.phx.gbl...
Query[vbcol=seagreen]
> For
rights.
>
|||Why is MS not providing easier access to this fix? Can people share hotfixes
to avoid customer support? (I'm legit, just hate dealing with them).
"Daniel Reber" wrote:

> The hotfix corrected my issue, thanks.
> Dan Reber
> ""Yuan Shao"" <v-yshao@.online.microsoft.com> wrote in message
> news:jWUPnlUKEHA.2360@.cpmsftngxa10.phx.gbl...
> For
>
>
|||In general, we make you call in and talk to a support engineer because we
want to track who is using which hotfix. These things aren't tested to the
same degree as service packs and if we run into a serious problem with one
of them, it's nice to be able to figure out who we need to inform. In
theory. :-) I say that because I'm not on the support team and I'm not sure
how stringently that is followed. We also want to make sure you're actually
running into the specific problem that the hotfix corrects. Again, because
they're not tested as thoroughly and we don't advise running them without
need.
Once the support engineer has confirmed that you're hitting a bug fixed by
the hotfix, they'll refund your money.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian T" <Brian T@.discussions.microsoft.com> wrote in message
news:E70AD0EA-1241-41E1-A951-2206780E15B4@.microsoft.com...[vbcol=seagreen]
> Why is MS not providing easier access to this fix? Can people share
> hotfixes
> to avoid customer support? (I'm legit, just hate dealing with them).
> "Daniel Reber" wrote:

ANALYZING SQL SERVICE HANGUP

The SQL Service hangs up at peak time once a day. What is the best way to
analyze the problem
Hi
Run sp_who2 from Query Analyser to see if blocking occurs.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:

> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>
|||Posting twice the same question in 70 minutes does not guarantee a quicker
answer.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:

> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>

ANALYZING SQL SERVICE HANGUP

The SQL Service hangs up at peak time once a day. What is the best way to
analyze the problemHi
Run sp_who2 from Query Analyser to see if blocking occurs.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>|||Posting twice the same question in 70 minutes does not guarantee a quicker
answer.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>

ANALYZING SQL SERVICE HANGUP

The SQL Service hangs up at peak time once a day. What is the best way to
analyze the problemHi
Run sp_who2 from Query Analyser to see if blocking occurs.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:

> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>|||Posting twice the same question in 70 minutes does not guarantee a quicker
answer.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:

> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>

Analysis Services service hangs cube build / timeout on process cu

We have two apparent issues with Analysis Services (AS) as outlined below:
1.We have a rebuild of the nightly cube build and quite often the first
step would hang (just run without processing). We would have to stop the
job, restart Analysis Services and then start the job again, resulting in
loss of processing time and an impact to our Production Reporting. As a
workaround for this intermittent problem, we have adjusted the nightly job to
include a stop and start of AS before the cube build begins. An outline of
the current steps:
?net stop MSSQLServerOLAPService
?net start MSSQLServerOLAPService
?dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
?process cub files
?backup cube
We need to know why AS does not process cubes as it should from time to
time. Do we have a configuration problem? Is our Production Server not
sufficient enough for AS and the size of the cube? We have more than the
minimum requirements but possibly we need more due to our expectation of the
cubes and data.
Server Specifications -->
Production
CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
Memory = 20476 MB
OS – Windows 2003. SP1
SQL Server 2005
64 bit based on the x64 platform
2.We have been unable to deploy a copy of the Production cubes to a new
server. We even tried deploying a smaller cube, to include a large
dimension (ex: Account). We continually experience time outs with the larger
dimensions.
a.Tried deploying from Visual Studio 2005
b.Attempted to Process the cube through SQL Server Management Studio
c.Attempted to Process the larger dimension only from SQL Server Management
Studio
d.Attempted Parallel processing, Sequential – One Transaction, and
Sequential – Separate Transaction.
e.Also tried changing the default timeout for the AS server.
ForceCommitTimeout property was 30000, increased to 40000 and then again to
50000
f.Attempted to change the ExternalCommandTimeout property from 3600 to
7200. This was successful in the Deisaster Recovery server which has nothing
running (no users reporting). Although it did take over 5 hours to process.
At that rate, it might take 2 days to rebuild the large cube that we have,
with several partitions.
Disaster Recovery -->
CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
Memory = 9728 MB
OS – Windows 2003 SP 1
SQL Server 2005
32bit
Errors:
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.
do you have any proactive caching partitions?
I see this error when I'm using the proactive caching feature.
"Shari" <Shari@.discussions.microsoft.com> wrote in message
news:4D39C9AF-58CF-4639-BA8B-DF4E936D62F1@.microsoft.com...
> We have two apparent issues with Analysis Services (AS) as outlined below:
> 1. We have a rebuild of the nightly cube build and quite often the first
> step would hang (just run without processing). We would have to stop the
> job, restart Analysis Services and then start the job again, resulting in
> loss of processing time and an impact to our Production Reporting. As a
> workaround for this intermittent problem, we have adjusted the nightly job
> to
> include a stop and start of AS before the cube build begins. An outline
> of
> the current steps:
> ? net stop MSSQLServerOLAPService
> ? net start MSSQLServerOLAPService
> ? dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
> ? process cub files
> ? backup cube
> We need to know why AS does not process cubes as it should from time to
> time. Do we have a configuration problem? Is our Production Server not
> sufficient enough for AS and the size of the cube? We have more than the
> minimum requirements but possibly we need more due to our expectation of
> the
> cubes and data.
> Server Specifications -->
> Production
> CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
> Memory = 20476 MB
> OS – Windows 2003. SP1
> SQL Server 2005
> 64 bit based on the x64 platform
>
> 2. We have been unable to deploy a copy of the Production cubes to a new
> server. We even tried deploying a smaller cube, to include a large
> dimension (ex: Account). We continually experience time outs with the
> larger
> dimensions.
> a. Tried deploying from Visual Studio 2005
> b. Attempted to Process the cube through SQL Server Management Studio
> c. Attempted to Process the larger dimension only from SQL Server
> Management
> Studio
> d. Attempted Parallel processing, Sequential – One Transaction, and
> Sequential – Separate Transaction.
> e. Also tried changing the default timeout for the AS server.
> ForceCommitTimeout property was 30000, increased to 40000 and then again
> to
> 50000
> f. Attempted to change the ExternalCommandTimeout property from 3600 to
> 7200. This was successful in the Deisaster Recovery server which has
> nothing
> running (no users reporting). Although it did take over 5 hours to
> process.
> At that rate, it might take 2 days to rebuild the large cube that we
> have,
> with several partitions.
>
> Disaster Recovery -->
> CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
> Memory = 9728 MB
> OS – Windows 2003 SP 1
> SQL Server 2005
> 32bit
> Errors:
> OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
> OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.
>
|||Thanks Jeje for your quick response.
I browsed the setting of the partitions and proactive caching is not
enabled. Any other ideas?
Thanks,
Shari
"Jeje" wrote:
[vbcol=seagreen]
> do you have any proactive caching partitions?
> I see this error when I'm using the proactive caching feature.
>
> "Shari" <Shari@.discussions.microsoft.com> wrote in message
> news:4D39C9AF-58CF-4639-BA8B-DF4E936D62F1@.microsoft.com...

Analysis Services service hangs cube build / timeout on process cu

We have two apparent issues with Analysis Services (AS) as outlined below:
1. We have a rebuild of the nightly cube build and quite often the first
step would hang (just run without processing). We would have to stop the
job, restart Analysis Services and then start the job again, resulting in
loss of processing time and an impact to our Production Reporting. As a
workaround for this intermittent problem, we have adjusted the nightly job t
o
include a stop and start of AS before the cube build begins. An outline of
the current steps:
? net stop MSSQLServerOLAPService
? net start MSSQLServerOLAPService
? dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
? process cub files
? backup cube
We need to know why AS does not process cubes as it should from time to
time. Do we have a configuration problem? Is our Production Server not
sufficient enough for AS and the size of the cube? We have more than the
minimum requirements but possibly we need more due to our expectation of the
cubes and data.
Server Specifications -->
Production
CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
Memory = 20476 MB
OS – Windows 2003. SP1
SQL Server 2005
64 bit based on the x64 platform
2. We have been unable to deploy a copy of the Production cubes to a new
server. We even tried deploying a smaller cube, to include a large
dimension (ex: Account). We continually experience time outs with the larger
dimensions.
a. Tried deploying from Visual Studio 2005
b. Attempted to Process the cube through SQL Server Management Studio
c. Attempted to Process the larger dimension only from SQL Server Management
Studio
d. Attempted Parallel processing, Sequential – One Transaction, and
Sequential – Separate Transaction.
e. Also tried changing the default timeout for the AS server.
ForceCommitTimeout property was 30000, increased to 40000 and then again to
50000
f. Attempted to change the ExternalCommandTimeout property from 3600 to
7200. This was successful in the Deisaster Recovery server which has nothin
g
running (no users reporting). Although it did take over 5 hours to process.
At that rate, it might take 2 days to rebuild the large cube that we have,
with several partitions.
Disaster Recovery -->
CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
Memory = 9728 MB
OS – Windows 2003 SP 1
SQL Server 2005
32bit
Errors:
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.do you have any proactive caching partitions?
I see this error when I'm using the proactive caching feature.
"Shari" <Shari@.discussions.microsoft.com> wrote in message
news:4D39C9AF-58CF-4639-BA8B-DF4E936D62F1@.microsoft.com...
> We have two apparent issues with Analysis Services (AS) as outlined below:
> 1. We have a rebuild of the nightly cube build and quite often the first
> step would hang (just run without processing). We would have to stop the
> job, restart Analysis Services and then start the job again, resulting in
> loss of processing time and an impact to our Production Reporting. As a
> workaround for this intermittent problem, we have adjusted the nightly job
> to
> include a stop and start of AS before the cube build begins. An outline
> of
> the current steps:
> ? net stop MSSQLServerOLAPService
> ? net start MSSQLServerOLAPService
> ? dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
> ? process cub files
> ? backup cube
> We need to know why AS does not process cubes as it should from time to
> time. Do we have a configuration problem? Is our Production Server not
> sufficient enough for AS and the size of the cube? We have more than the
> minimum requirements but possibly we need more due to our expectation of
> the
> cubes and data.
> Server Specifications -->
> Production
> CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
> Memory = 20476 MB
> OS – Windows 2003. SP1
> SQL Server 2005
> 64 bit based on the x64 platform
>
> 2. We have been unable to deploy a copy of the Production cubes to a new
> server. We even tried deploying a smaller cube, to include a large
> dimension (ex: Account). We continually experience time outs with the
> larger
> dimensions.
> a. Tried deploying from Visual Studio 2005
> b. Attempted to Process the cube through SQL Server Management Studio
> c. Attempted to Process the larger dimension only from SQL Server
> Management
> Studio
> d. Attempted Parallel processing, Sequential – One Transaction, and
> Sequential – Separate Transaction.
> e. Also tried changing the default timeout for the AS server.
> ForceCommitTimeout property was 30000, increased to 40000 and then again
> to
> 50000
> f. Attempted to change the ExternalCommandTimeout property from 3600 to
> 7200. This was successful in the Deisaster Recovery server which has
> nothing
> running (no users reporting). Although it did take over 5 hours to
> process.
> At that rate, it might take 2 days to rebuild the large cube that we
> have,
> with several partitions.
>
> Disaster Recovery -->
> CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
> Memory = 9728 MB
> OS – Windows 2003 SP 1
> SQL Server 2005
> 32bit
> Errors:
> OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
> OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.
>

Analysis Services Processing Freezes or Hangs

Currently I have multiple Analysis Services databases on multiple servers. On occassion, when processing these databases it will freeze or hang. Sometime the problem is the database structure, sometimes it is the back end source tables being locked, etc. The only way I can terminate the processing is to kill the dev environment or management studio. This causes problems because the next time the database is opened, it will not process or save. The only solution I found is restarting Analysis Services. Is there some way to force stop processing without damaging the database and leaving connections open?

It might be possible to open a second instance of SSMS and use XMLA to discover the sessions or connections and then cancel the one that it stuck. But sometimes the server can take a while to respond to these commands if it is "stuck".

You can also use the xmlaDiscover class from the Analysis Services Stored Procedure project http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover&referringTitle=Home which lets you manage this a bit simpler by running a command like:

CALL ASSP.DiscoverConnections()

which displays the current connections in a grid, and then:

CALL ASSP.CancelConnection(<ConnectionID>)

You should probably be aware that there is an issue with the Cancel command in SP2 that I read about in a KB article and sometimes it does not function correctly. Apparently there is a hotfix available if this issue affects you.