I want to condense several rows of a table using a select
statement, function, or stored procedure
For Example
mytable :
1 a
2 b
2 c
2 d
3 a
so that my select should result in
1 a
2 b,c,d
3 a
Any ideas or suggestions?
While this code references the sample database in my book (CHA2), the
pattern will work for your problem as well. In this example, the @.EventDates
variable is used to gather, or denomalize, the EventDate column:
USE CHA2
DECLARE
@.EventDates VARCHAR(1024)
SET @.EventDates = ''
SELECT @.EventDates = @.EventDates + CONVERT(VARCHAR(15), a.d,107 ) + '; '
FROM (Select DateBegin as [d] FROM Event
JOIN Tour
ON Event.TourID = Tour.TourID
WHERE Tour.[Name] = 'Outer Banks Lighthouses') as a
SELECT Left(@.EventDates, Len(@.EventDates)-1) AS 'Outer Banks Lighthouses
Events'
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:392801c4aa4b$477a6810$a401280a@.phx.gbl...
>I want to condense several rows of a table using a select
> statement, function, or stored procedure
> For Example
> mytable :
> 1 a
> 2 b
> 2 c
> 2 d
> 3 a
> so that my select should result in
> 1 a
> 2 b,c,d
> 3 a
> Any ideas or suggestions?
Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts
Thursday, March 29, 2012
Wednesday, March 7, 2012
Connection Timeout when processing cube
I am getting a connection timeout error when processing a cube with measure group containing 4mil rows or so. It errors after 5 mins and after about 3mil rows have been read so it's probably not the ExternalCommandTimeout issue. The Fact table for the measure group is created using a named query. I would appreciate any insight into why the error is thrown after 5 mins. Here's part of the error:
===================================
The connection was lost. Close all processing dialog boxes and try processing the object again. (Microsoft Visual Studio)
===================================
The connection either timed out or was lost. (Microsoft.AnalysisServices)
Program Location:
at Microsoft.AnalysisServices.XmlaClient.EndRequest()
at Microsoft.AnalysisServices.XmlaClient.SendExecuteAndReadResponse(Boolean skipResults, Boolean throwIfError)
at Microsoft.AnalysisServices.AnalysisServicesClient.Execute(String command)
at Microsoft.AnalysisServices.Server.Execute(String command)
at Microsoft.AnalysisServices.ManagementDialogs.OlapProcessProgressContainer.ProcessObjects()
===================================
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)
Program Location:
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.AnalysisServices.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
at Microsoft.AnalysisServices.DimeRecord.ReadHeader()
at Microsoft.AnalysisServices.DimeRecord..ctor(Stream stream)
at Microsoft.AnalysisServices.DimeReader.ReadRecord()
at Microsoft.AnalysisServices.TcpStream.GetDataType()
===================================
An existing connection was forcibly closed by the remote host (System)
Dont think this has anything to do with ExternalCommandTimeout.
Try processing your cube using SQL Management Studio.
For that first deploy your project and in the project's properties in the deployment tab select Processing option = "Do Not Process".
Then open a SQL Management studio, connect to Analysis Services, navigate to your cube and try to process it. See if you get any errors.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Couplde of things:
Take a look at the msmdsrv.log located in C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log folder.
Do you see there any messages around time you trying to process your cube.
If you havent yet, install SP1 of SQL Server and see if the same behavior persists.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Connection Timeout when processing cube
I am getting a connection timeout error when processing a cube with measure group containing 4mil rows or so. It errors after 5 mins and after about 3mil rows have been read so it's probably not the ExternalCommandTimeout issue. The Fact table for the measure group is created using a named query. I would appreciate any insight into why the error is thrown after 5 mins. Here's part of the error:
===================================
The connection was lost. Close all processing dialog boxes and try processing the object again. (Microsoft Visual Studio)
===================================
The connection either timed out or was lost. (Microsoft.AnalysisServices)
Program Location:
at Microsoft.AnalysisServices.XmlaClient.EndRequest()
at Microsoft.AnalysisServices.XmlaClient.SendExecuteAndReadResponse(Boolean skipResults, Boolean throwIfError)
at Microsoft.AnalysisServices.AnalysisServicesClient.Execute(String command)
at Microsoft.AnalysisServices.Server.Execute(String command)
at Microsoft.AnalysisServices.ManagementDialogs.OlapProcessProgressContainer.ProcessObjects()
===================================
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)
Program Location:
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.AnalysisServices.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
at Microsoft.AnalysisServices.DimeRecord.ReadHeader()
at Microsoft.AnalysisServices.DimeRecord..ctor(Stream stream)
at Microsoft.AnalysisServices.DimeReader.ReadRecord()
at Microsoft.AnalysisServices.TcpStream.GetDataType()
===================================
An existing connection was forcibly closed by the remote host (System)
Dont think this has anything to do with ExternalCommandTimeout.
Try processing your cube using SQL Management Studio.
For that first deploy your project and in the project's properties in the deployment tab select Processing option = "Do Not Process".
Then open a SQL Management studio, connect to Analysis Services, navigate to your cube and try to process it. See if you get any errors.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Couplde of things:
Take a look at the msmdsrv.log located in C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log folder.
Do you see there any messages around time you trying to process your cube.
If you havent yet, install SP1 of SQL Server and see if the same behavior persists.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
connection timeout
Hi there
I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements to
change data in my database, affecting up to 3 million rows of data. If I
issue the statements from Enterprise Manager I get a "timeout expired" error
on large queries, whereas if I execute the queries from Query Analyser, they
perform fine if very slowly. Mostly at the moment I'm happy that they
execute.
Now, if I issue the statements over an ADO connection from Microsoft Excel
(using a VBA procedure, with the statements issued either on-the-fly or via
a stored query), I get the same "timeout expired" error.
Any ideas?
Best regards
Loane
Hi
Make sure that your database and log file have been grown big enough before
you run the update.
Make sure that the Where clause column is indexed.
If possible, break up your update into smaller pieces.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>
|||That is because EM has a default timeout for connections at 30 seconds as do
most drivers. QA on the other hand defaults to 0 which means it never times
out. When ever possible try to avoid doing operations on so many rows all
in one transaction. Most operations such as that can sit in a loop and do
the updates in smaller batches of say 10 or 20 thousand rows at a time.
That way you should never timeout and won't block other users as much or for
as long a period of time. There are plenty of ways to approach a situation
such as that. If you need help then post the DDL for the table and the
update statement you normally would use.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>
I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements to
change data in my database, affecting up to 3 million rows of data. If I
issue the statements from Enterprise Manager I get a "timeout expired" error
on large queries, whereas if I execute the queries from Query Analyser, they
perform fine if very slowly. Mostly at the moment I'm happy that they
execute.
Now, if I issue the statements over an ADO connection from Microsoft Excel
(using a VBA procedure, with the statements issued either on-the-fly or via
a stored query), I get the same "timeout expired" error.
Any ideas?
Best regards
Loane
Hi
Make sure that your database and log file have been grown big enough before
you run the update.
Make sure that the Where clause column is indexed.
If possible, break up your update into smaller pieces.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>
|||That is because EM has a default timeout for connections at 30 seconds as do
most drivers. QA on the other hand defaults to 0 which means it never times
out. When ever possible try to avoid doing operations on so many rows all
in one transaction. Most operations such as that can sit in a loop and do
the updates in smaller batches of say 10 or 20 thousand rows at a time.
That way you should never timeout and won't block other users as much or for
as long a period of time. There are plenty of ways to approach a situation
such as that. If you need help then post the DDL for the table and the
update statement you normally would use.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>
Saturday, February 25, 2012
connection timeout
Hi there
I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements to
change data in my database, affecting up to 3 million rows of data. If I
issue the statements from Enterprise Manager I get a "timeout expired" error
on large queries, whereas if I execute the queries from Query Analyser, they
perform fine if very slowly. Mostly at the moment I'm happy that they
execute.
Now, if I issue the statements over an ADO connection from Microsoft Excel
(using a VBA procedure, with the statements issued either on-the-fly or via
a stored query), I get the same "timeout expired" error.
Any ideas?
Best regards
LoaneHi
Make sure that your database and log file have been grown big enough before
you run the update.
Make sure that the Where clause column is indexed.
If possible, break up your update into smaller pieces.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>|||That is because EM has a default timeout for connections at 30 seconds as do
most drivers. QA on the other hand defaults to 0 which means it never times
out. When ever possible try to avoid doing operations on so many rows all
in one transaction. Most operations such as that can sit in a loop and do
the updates in smaller batches of say 10 or 20 thousand rows at a time.
That way you should never timeout and won't block other users as much or for
as long a period of time. There are plenty of ways to approach a situation
such as that. If you need help then post the DDL for the table and the
update statement you normally would use.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>
I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements to
change data in my database, affecting up to 3 million rows of data. If I
issue the statements from Enterprise Manager I get a "timeout expired" error
on large queries, whereas if I execute the queries from Query Analyser, they
perform fine if very slowly. Mostly at the moment I'm happy that they
execute.
Now, if I issue the statements over an ADO connection from Microsoft Excel
(using a VBA procedure, with the statements issued either on-the-fly or via
a stored query), I get the same "timeout expired" error.
Any ideas?
Best regards
LoaneHi
Make sure that your database and log file have been grown big enough before
you run the update.
Make sure that the Where clause column is indexed.
If possible, break up your update into smaller pieces.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>|||That is because EM has a default timeout for connections at 30 seconds as do
most drivers. QA on the other hand defaults to 0 which means it never times
out. When ever possible try to avoid doing operations on so many rows all
in one transaction. Most operations such as that can sit in a loop and do
the updates in smaller batches of say 10 or 20 thousand rows at a time.
That way you should never timeout and won't block other users as much or for
as long a period of time. There are plenty of ways to approach a situation
such as that. If you need help then post the DDL for the table and the
update statement you normally would use.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>
Subscribe to:
Posts (Atom)