Analysis Server Hangs

Guys,

I have been getting a very peculiar problem while i am processing a dimention with about approx 7 million (68 lakh) rows. The processing wizard shows the rows being read and once the rows are read from SQL server , the Analysis Manager Hangs. Can anyone help me out ? Is this a limitation of Analysis manager not being able to process such huge amount of data or are there some settings that i can tweak.Hi enigma,
Do you meant to say by seeing in'Task Manager/ Application'. showing 'Not Responding' ? You try watch using performance counter/AS:Proc:,AS:Agg.Proc objects, after some time, you will get some values related to that. In my case also, I was getting 'this hanging sort of things' but actually it is not.
HTH
====

Originally posted by Enigma
Guys,

I have been getting a very peculiar problem while i am processing a dimention with about approx 7 million (68 lakh) rows. The processing wizard shows the rows being read and once the rows are read from SQL server , the Analysis Manager Hangs. Can anyone help me out ? Is this a limitation of Analysis manager not being able to process such huge amount of data or are there some settings that i can tweak.|||Thanks Man,

I was doing exactly the thing you said ... well .. i processed the cube using a DTS yesterday and that worked perfectly fine .

Thanks for all your help

an UPDATE inside a SELECT

I was wondering if it is possible to have an UPDATE satement inside a
SELECT statment. What I want to do is select certian rows from a table
then based on the result I want to update another table. Metaphorically
something like the following
SELECT A.QTY,(UPDATE TABLE B SET QTY=A.QTY WHERE B.[ID]=A[ID]) FROM
TABLE A
*** Sent via Developersdex http://www.examnotes.net ***Hi,
Looks like you are trying to Select and Update the contents of the table in
one go.
Try to use a Stored Procedure for this purpose.
Hope this answered your Question.
thanks,
Chandra
"Hussain Al-Dhaheri" wrote:

> I was wondering if it is possible to have an UPDATE satement inside a
> SELECT statment. What I want to do is select certian rows from a table
> then based on the result I want to update another table. Metaphorically
> something like the following
> SELECT A.QTY,(UPDATE TABLE B SET QTY=A.QTY WHERE B.[ID]=A[ID]) FROM
> TABLE A
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi
No, you vannot do it in that way
[Untested]
UPDATE TableA SET col=(SELECT b.col FROM TableB b WHERE b.col=col)
WHERE EXISTS (SELECT * FROM TableB b WHERE b.col=col)
SELECT <column lists> FROM
"Hussain Al-Dhaheri" <hdhaheri@.hotmail.com> wrote in message
news:e4s4e1tSFHA.3672@.TK2MSFTNGP10.phx.gbl...
> I was wondering if it is possible to have an UPDATE satement inside a
> SELECT statment. What I want to do is select certian rows from a table
> then based on the result I want to update another table. Metaphorically
> something like the following
> SELECT A.QTY,(UPDATE TABLE B SET QTY=A.QTY WHERE B.[ID]=A[ID]) FROM
> TABLE A
>
> *** Sent via Developersdex http://www.examnotes.net ***sql

Am I missing something - DBCC SHRINKFILE('NewFile', EMPTYFILE)

I create a filegroup, added a file to it and created a table with 1
varchar(256) column to it. I then added several rows of data. I wanted to
test removing the filegroup and can't. First I ran:
DBCC SHRINKFILE('NewFile', EMPTYFILE)
and received:
DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
UsedPages = 8, EstimatedPages = 8
I then run:
ALTER DATABASE TestDB REMOVE FILE NewFile
and get:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'Newfile' cannot be removed because it is not empty.
The table is not miving to another filegroup.
You have to create another file in the same filegroup to hold the table or
you have to move the table to another filegroup. DBCC SHRINKFILE will not
reassign objects to a different filegroup.
To answer the next question, create a clustered index on the table in the
target file group to move a table.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Charlie" <SQL@.newsgroups.nospam> wrote in message
news:DE1296AC-082F-48D9-97E8-8FC5AF1D5452@.microsoft.com...
>I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>
|||I have no idea why I thought it would magicaly reassign it to another
filegroup.
"Charlie" wrote:

> I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>

Am I missing something - DBCC SHRINKFILE('NewFile', EMPTYFILE)

I create a filegroup, added a file to it and created a table with 1
varchar(256) column to it. I then added several rows of data. I wanted to
test removing the filegroup and can't. First I ran:
DBCC SHRINKFILE('NewFile', EMPTYFILE)
and received:
DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
UsedPages = 8, EstimatedPages = 8
I then run:
ALTER DATABASE TestDB REMOVE FILE NewFile
and get:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'Newfile' cannot be removed because it is not empty.
The table is not miving to another filegroup.You have to create another file in the same filegroup to hold the table or
you have to move the table to another filegroup. DBCC SHRINKFILE will not
reassign objects to a different filegroup.
To answer the next question, create a clustered index on the table in the
target file group to move a table.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Charlie" <SQL@.newsgroups.nospam> wrote in message
news:DE1296AC-082F-48D9-97E8-8FC5AF1D5452@.microsoft.com...
>I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>|||I have no idea why I thought it would magicaly reassign it to another
filegroup.
"Charlie" wrote:

> I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>

Am I missing something - DBCC SHRINKFILE('NewFile', EMPTYFILE)

I create a filegroup, added a file to it and created a table with 1
varchar(256) column to it. I then added several rows of data. I wanted to
test removing the filegroup and can't. First I ran:
DBCC SHRINKFILE('NewFile', EMPTYFILE)
and received:
DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
UsedPages = 8, EstimatedPages = 8
I then run:
ALTER DATABASE TestDB REMOVE FILE NewFile
and get:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'Newfile' cannot be removed because it is not empty.
The table is not miving to another filegroup.You have to create another file in the same filegroup to hold the table or
you have to move the table to another filegroup. DBCC SHRINKFILE will not
reassign objects to a different filegroup.
To answer the next question, create a clustered index on the table in the
target file group to move a table.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Charlie" <SQL@.newsgroups.nospam> wrote in message
news:DE1296AC-082F-48D9-97E8-8FC5AF1D5452@.microsoft.com...
>I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>|||I have no idea why I thought it would magicaly reassign it to another
filegroup.
"Charlie" wrote:
> I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>

Am I close or way off the mark?

I am trying to select rows from a SQL2000 database table and then write a random number back into each row. I have already looked into do it all in a SP but there are well documented limitations for the SQL RAND function when called in the same batch, so I need to somehow do in .Net what I already have working in classic ASP.

I can't get the UPDATE (part two section) to compile. I don't know how to call the stored procedure inside the 'foreach' loop or extract the SP parameters. I have it working in classic asp but am having a lot of trouble converting to .Net 2.0. Is the below even close to working?

// stored procedure to write externally generated random number value into database

PROCEDURE RandomizeLinks
@.L_ID int,
@.L_Rank int
AS
UPDATE Links SET L_Rank = @.L_Rank
WHERE (L_ID = @.L_ID)

// Part One select links that need random number inserted.

public DataTable GetRandLinks()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString);
SqlCommand cmd = new SqlCommand("RandomizerSelect001", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
try
{
da.Fill(ds, "Random001");
return ds.Tables["Random001"];
}
catch
{ throw new ApplicationException("Data error"); }
}

// Part Two I need two write a random number back into each row

protected void Button1_Click(object sender, EventArgs e)
{
GetRandLinks();
int LinkID; // this generates unassigned local variable "LinkID' error
int LRank; // this generates unassigned local variable "LRank' error

SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString);
SqlCommand cmd2 = new SqlCommand("RandomizeLinks", con2);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.AddWithValue("@.L_ID", LinkID);
cmd2.Parameters.AddWithValue("@.L_Rank", LRank);
SqlDataAdapter da2 = new SqlDataAdapter();

int RowIncrement;
RowIncrement = 0;
DataTable dt = GetRandLinks();
foreach (DataRow row in dt.Rows)
{
System.Random myRandom = new System.Random();
int LinkRank = myRandom.Next(25, 250);
LRank = LinkRank;
da2.UpdateCommand = cmd2;
RowIncrement++;
}

}

Question - instead of looping back to the database server for every row, can I pull the data into a DataTable, and then on the .Net web server update each row with it random number using system.Random and then finally read the updated DataTable back to update the SQL Database table? And if so is any of the code I have written useful to that end?

|||

Most of the T-SQL limitations can be gotten around by feeding the rand function the right 4 digits of the newid function.

|||

How would I incorporate that into this stored procedure? I am working on a SQL 2000 server, company hasn't got the money for SQL2007. And is there a way to limit the random number range to > 25 and < 250?

PROCEDURE spA_Random001
AS
DECLARE @.L_ID int
DECLARE @.L_Rank int
DECLARE cur CURSOR FOR
SELECT L_ID , L_Rank
FROM tblLinkInfo_OLD2
OPEN cur;
FETCH NEXT FROM cur
INTO @.L_ID, @.L_Rank
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (@.L_Rank > 10 AND @.L_Rank < 300)
UPDATE tblLinkInfo_OLD2 SET L_Rank = Convert(int, (L_ID)*RAND()) WHERE (L_ID = @.L_ID)
FETCH NEXT FROM cur
INTO @.L_ID, @.L_Rank
END
CLOSE cur;
DEALLOCATE cur;

|||

Thank you for the suggestion, it is an excellent solution for most applications however in my cash I need a strictly limit the random value to integers between 25 and 250. So far - pulling the table data into a DataSet, using the webserver resident System.Random to generate the random number and then writing the changes up to the SQL server seems to be the solution. I have been spoiled by the drag and drop GridViews and DataLists no I have to learn what is really going one. I am going to try to get th above solution working but I am beginning to think I am way of track.

Always selecting at least 10 rows?

Hi,
I use an Identity column to create row numbers displayed in a DataGrid. The
datagrid typically displays row 1-10, 11-20 etc. The datagrid is filled us
ing
SELECT * FROM Tabel WHERE RowID BETWEEN Start AND End.
If rows 1-10 is removed from the table, there are no rows displayed since th
e select statement returns none for this range.
What I am looking for is an elegant way of retrieving at least n rows with r
ow numbers from x or above. Something along the line of
SELECT 10 FROM Tabel WHERE RowID >= 1
Can this be done, or do I need to use cursors? SqlDataReader and loop?
MortenHo Morten,
If you already have an identity column use this to order againt it.
SELECT TOP 10 * FROM Table Order by YouridentColumn
You should also consider to sue the paging machanisam in the datagrid
(if you are using .NET).
HTH, Jens Suessmeyer.|||SELECT TOP 10 * FROM Tabel WHERE RowID >= 1
?
--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Morten Wennevik" <Morten.Wennevik@.email.adr> wrote in message
news:op.sz2fvlfdg1d8xu@.tr023.bouvet.no...
> Hi,
> I use an Identity column to create row numbers displayed in a DataGrid.
> The datagrid typically displays row 1-10, 11-20 etc. The datagrid is
> filled using
> SELECT * FROM Tabel WHERE RowID BETWEEN Start AND End.
> If rows 1-10 is removed from the table, there are no rows displayed since
> the select statement returns none for this range.
> What I am looking for is an elegant way of retrieving at least n rows with
> row numbers from x or above. Something along the line of
> SELECT 10 FROM Tabel WHERE RowID >= 1
> Can this be done, or do I need to use cursors? SqlDataReader and loop?
> Morten|||Ah, I thought that Top 10 would retrieve the top 10 RowIds, meaning the ten
largest ids. Works like a charm :)
Oh, and the paging mechanism is my own since the datagrid paging is too limi
ted.
Morten
On Fri, 11 Nov 2005 08:53:19 +0100, Jens <Jens@.sqlserver2005.de> wrote:

> Ho Morten,
> If you already have an identity column use this to order againt it.
> SELECT TOP 10 * FROM Table Order by YouridentColumn
> You should also consider to sue the paging machanisam in the datagrid
> (if you are using .NET).
>
> HTH, Jens Suessmeyer.
>|||Exactly what I was looking for. I mistook the TOP keyword for largest inste
ad of first.
Thanks,
Morten
On Fri, 11 Nov 2005 08:56:11 +0100, Roji. P. Thomas <thomasroji@.gmail.com> w
rote:

> SELECT TOP 10 * FROM Tabel WHERE RowID >= 1
> ?|||Morten Wennevik wrote:
> Exactly what I was looking for. I mistook the TOP keyword for
> largest instead of first.
> Thanks,
> Morten
> On Fri, 11 Nov 2005 08:56:11 +0100, Roji. P. Thomas
> <thomasroji@.gmail.com> wrote:
TOP requires an ORDER BY clause to guarantee consistent results.
Otherwise, you might get somewhat random data.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes, forgot to add ORDER BY RowID, works fine now :)
On Fri, 11 Nov 2005 15:38:32 +0100, David Gugick <david.gugick-nospam@.quest.
com> wrote:

> Morten Wennevik wrote:
> TOP requires an ORDER BY clause to guarantee consistent results.
> Otherwise, you might get somewhat random data.
>

Alternatives to SQL FTS.

I've been using FTS for 5 years now on 1.5million rows of data. Certain
things have alway annoyed me about it however:
1. I never managed to succesfully move/copy ft catalogs.
2. Noise words/symbols/single characters e.g. c#, c#, "I am not".
3. Search speed and ranking/relevance quality.
3. Population performance.
I know by constantly staying on top of the excellent advice in this forum I
can probably overcome these issues, but frankly I'm not sure I want to take
this route. Not at least before I've explored the alternatives. So my
question is what alternative products would anyone recommend taking a look at
? The ft queries would be made from a C#/.Net/ASP/NET app, and I don't mind
if the text records are stored in a database or maybe an XML file.
I haven't looked at Yukon yet - so that may well be a valid answer.
Any takers ?
I would have a look at Yukon for the following reasons
1) you can now a) detatch and reattach full text catalogs with the database
files
b) your full text catalogs can be backed up with
your back and restored. This is the default behavior
c) you can replicate full text indexes as long as
your subscriber database is full text enabled
2) by default c and j are indexed if they are preceeded by a ++ or a #. SQL
2005 (Yukon) can be made to be noise insensitive - ie your queries will not
break with a noise word
3) Search preformance is greatly improved. The ranking algorithm yeilds
different results but is much the same.
4) radical improvements in indexing performance.
There are other options out there - like SQL Turbo
(http://www.imceda.com/SQLTurbo_Description.htm) and omnidex
(http://www.disc.com/).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"slartyb" <slartyb@.discussions.microsoft.com> wrote in message
news:6532CD01-5506-4B51-AF94-7E2D6C65740E@.microsoft.com...
> I've been using FTS for 5 years now on 1.5million rows of data. Certain
> things have alway annoyed me about it however:
> 1. I never managed to succesfully move/copy ft catalogs.
> 2. Noise words/symbols/single characters e.g. c#, c#, "I am not".
> 3. Search speed and ranking/relevance quality.
> 3. Population performance.
> I know by constantly staying on top of the excellent advice in this forum
I
> can probably overcome these issues, but frankly I'm not sure I want to
take
> this route. Not at least before I've explored the alternatives. So my
> question is what alternative products would anyone recommend taking a look
at
> ? The ft queries would be made from a C#/.Net/ASP/NET app, and I don't
mind
> if the text records are stored in a database or maybe an XML file.
> I haven't looked at Yukon yet - so that may well be a valid answer.
> Any takers ?
>

Alternative to dynamic sql?

I have a procedure that take several paramters and depending of what
values is submitted or not, the procedures shall return different
number of rows. But to simplyfy this my example use just one
parameter, for example Idnr.

If this id is submitted then I will return only the posts with this
idnr, but if this is not submitted, I will return all posts in table.
As I can see I have two options
1. IF @.lcIdNr IS NOT NULL
SELECT *
FROM table
WHERE idnr = @.lcIdNr
ELSE
SELECT *
FROM table

2. Use dynamic SQL.

The first example can work with just one parameter but with a couple
of different input paramters this could be difficult, anyway this is
not a good solution. The second example works fine but as I understand
dynamic sql is not good from the optimizing point of view. So, I don't
want to use either of theese options, so I wonder If there i a way to
work around this with for example a case clause?

Regards
JennyMaybe:

SELECT *
FROM TableX
WHERE idnr = @.lcIdNr OR @.lcIdNr IS NULL

This article explains some of the things you should consider before using
Dynamic SQL:

http://www.algonet.se/~sommar/dynamic_sql.html

--
David Portas
----
Please reply only to the newsgroup
--|||SELECT *
FROM Foobar
WHERE idnr = COALESCE(@.lcIdNr, idnr);|||[posted and mailed, vnligen svara i nys]

Jenny (jenny@.megasol.se) writes:
> If this id is submitted then I will return only the posts with this
> idnr, but if this is not submitted, I will return all posts in table.
> As I can see I have two options
> 1. IF @.lcIdNr IS NOT NULL
> SELECT *
> FROM table
> WHERE idnr = @.lcIdNr
> ELSE
> SELECT *
> FROM table
> 2. Use dynamic SQL.
> The first example can work with just one parameter but with a couple
> of different input paramters this could be difficult, anyway this is
> not a good solution. The second example works fine but as I understand
> dynamic sql is not good from the optimizing point of view.

Actually in this case it's the opposite. For these kind of queries,
dynamic SQL usually gives you the best combination performance and
maintainability.

For a longer discussion on the topic, see this article on my web site:
http://www.algonet.se/~sommar/dyn-search.html. (This is not the same
that David referred you too.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp