Get Ancestor and Descendant in a Hierarchy

For a short time I have considered how to get the hierarchy in a self-linked table, incidentally I have found an article about Recursive CTEs in SQL Server Books Online, where an example is very impressive and arouses me to find a way to get the ancestors or descendants of a given node in a self-linked (self-referenced) table.

And now I want to share this method to anyone who has the same problem to resolve or someone like me who has addiction in SQL.

First of all we have to create a table for the following functions and build some test data. The statemens look like:

create table ST_CATEGORY(
CATEGORYID uniqueidentifier not null default NEWID(),
PARENTID uniqueidentifier,
[NAME] varchar(128),
COMMENT varchar(4096),
CONSTRAINT PK_ST_CATEGORY primary key (CATEGORYID)
)
go

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(@.rootoid, NULL, 'ROOT', 'ROOT NODE')

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(NEWID(), @.rootoid, 'Business Application', 'group for all business applications')

declare @.techoid uniqueidentifier
set @.techoid = NEWID()

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(@.techoid, @.rootoid, 'Tech101', 'technical tips')

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(NEWID(), @.techoid, 'C#', 'C# tips')
go

Now that the test table and data are prepared, we can get ancestors or descendants through the following four stored procedures of a given category.

CREATE PROCEDURE spGetAncestor
@.categoryID uniqueidentifier
AS
BEGIN
-- find parents/ancestor
WITH Ancestor( CATEGORYID ) AS
(
SELECT PARENTID
FROM ST_CATEGORY
WHERE CATEGORYID = @.categoryID
UNION ALL
SELECT PARENTID
FROM Ancestor, ST_CATEGORY
WHERE Ancestor.CATEGORYID = ST_CATEGORY.CATEGORYID
)
SELECT * FROM Ancestor
END
GO

CREATE PROCEDURE spGetSelfAndAncestor
@.categoryID uniqueidentifier
AS
BEGIN
-- find self and parents/ancestor
WITH SelfAndAncestor( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE CATEGORYID = @.categoryID
UNION ALL
SELECT PARENTID
FROM SelfAndAncestor, ST_CATEGORY
WHERE SelfAndAncestor.CATEGORYID = ST_CATEGORY.CATEGORYID
)
SELECT * FROM SelfAndAncestor
END
GO

CREATE PROCEDURE spGetDescendant
@.categoryID uniqueidentifier
AS
BEGIN
-- find children/descendant
WITH Descendant( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE PARENTID = @.categoryID
UNION ALL
SELECT ST_CATEGORY.CATEGORYID
FROM Descendant, ST_CATEGORY
WHERE Descendant.CATEGORYID = ST_CATEGORY.PARENTID
)
SELECT * FROM Descendant
END
GO

CREATE PROCEDURE spGetSelfAndDescendant
@.categoryID uniqueidentifier
AS
BEGIN
-- find self and children/descendant
WITH SelfAndDescendant( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE CATEGORYID = @.categoryID
UNION ALL
SELECT ST_CATEGORY.CATEGORYID
FROM SelfAndDescendant, ST_CATEGORY
WHERE SelfAndDescendant.CATEGORYID = ST_CATEGORY.PARENTID
)
SELECT * FROM SelfAndDescendant
END
GOUhm......OK. Thanks for sharing with us information that is available in Books Online...

get a value from a measure

I want to create a new calculated member in a virtul cube.
I have a dimension Time with 3 levels Year Month and Day and a Measure ValSa
l.
I want to get a value from the measure ValSal on day 2004/05/25, but i don't
know how?
With this value i want to fill the calculated member.
Any help is appreciated.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Try ([Measures].[ValSal],[Time].&[2005].&[5].&[25])
, if your dimension
[Time] is Year-month-day
wrote in message news:epEgjy4UEHA.212@.TK2MSFTNGP12.phx.gbl...
> I want to create a new calculated member in a virtul cube.
> I have a dimension Time with 3 levels Year Month and Day and a Measure
ValSal.
> I want to get a value from the measure ValSal on day 2004/05/25, but i
don't know how?
> With this value i want to fill the calculated member.
>
> Any help is appreciated.
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

get a value from a measure

I want to create a new calculated member in a virtul cube.
I have a dimension Time with 3 levels Year Month and Day and a Measure ValSal.
I want to get a value from the measure ValSal on day 2004/05/25, but i don't know how?
With this value i want to fill the calculated member.
Any help is appreciated.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Try ([Measures].[ValSal],[Time].&[2005].&[5].&[25]), if your dimension
[Time] is Year-month-day
wrote in message news:epEgjy4UEHA.212@.TK2MSFTNGP12.phx.gbl...
> I want to create a new calculated member in a virtul cube.
> I have a dimension Time with 3 levels Year Month and Day and a Measure
ValSal.
> I want to get a value from the measure ValSal on day 2004/05/25, but i
don't know how?
> With this value i want to fill the calculated member.
>
> Any help is appreciated.
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
sql

Get a list of changed records in a table

Is there a way to get a list of records that have changed since a specific
Date/Time?
I could create a trigger and a new table and work off that data but I have a
lot of tables I want to query so I don't necessarily want to create triggers
for each one.You cannot get this unless you have a datetime column on the table that you
update when data changes. or a triggering mechanism to store changed data.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Bishop" <nospam@.nospam.com> wrote in message
news:e9zRyVwqIHA.3900@.TK2MSFTNGP05.phx.gbl...
> Is there a way to get a list of records that have changed since a specific
> Date/Time?
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
>|||> Is there a way to get a list of records that have changed since a specific
> Date/Time?
No, SQL Server does not keep this information for you automagically.
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
If you allow ad hoc access to your tables, then this is how it's done, I'm
afraid. If you deny INSERT/UPDATE rights to the table and force those
actions through stored procedures, then you could perform the logging within
a stored procedure, which at least avoids some of the problems with doing
this in a trigger. But for DELETE you would have to log to a separate table
or use a trigger.
While it doesn't help you today, SQL Server 2008 will have several options
to make this easier... from change tracking and change data capture to the
extreme of audit all actions.
A|||You could use the new OUTPUT clause to automatically pump the pre-existing
data into another table and then query that 2nd table
--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/
Atlanta's Business Intelligence and Data Warehouse Experts
"Bishop" <nospam@.nospam.com> wrote in message
news:e9zRyVwqIHA.3900@.TK2MSFTNGP05.phx.gbl...
> Is there a way to get a list of records that have changed since a specific
> Date/Time?
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
>

get a count between a time range

I am trying to figure out if I can do this in a SQL query. I need to
know how many users are active during a given time period. I want to
be able to return the results below. If you look at the row with
User3 I only want to count User2 once even though he was active twice
during the time frame for User3
Start Time End Time User Id Concurrent User Count
06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1 1
06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1 3
06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2 3
06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3 3
06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2 2
06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2 1
I am pretty much stumped as how to proceed.
Thanks TimAssuming you're trying to query users who were active for any part of
the duration @.StartTime...@.EndTime inclusive.
SELECT DISTINCT UserId
FROM Table1
WHERE ( (StartTime <= @.StartTime AND EndTime >= @.StartTime)
OR (StartTime BETWEEN @.StartTime AND @.EndTime) )|||If you look at User3 there were 3 users active during User3's the start
time and end time including User3. I need to figure that count.
Thanks Tim
Lubdha Khandelwal wrote:
> Assuming you're trying to query users who were active for any part of
> the duration @.StartTime...@.EndTime inclusive.
> SELECT DISTINCT UserId
> FROM Table1
> WHERE ( (StartTime <= @.StartTime AND EndTime >= @.StartTime)
> OR (StartTime BETWEEN @.StartTime AND @.EndTime) )|||I'm . Do you want to count all the unique users that were
active during each of the active duration for all the users?
If so, this could get you that count:
SELECT T1.UserId, COUNT(DISTINCT T2.UserId)
FROM UserTable T1 INNER JOIN UserTable T2
ON ( (T2.StartTime <= T1.StartTime AND T2.EndTime >= T1.StartTime)
OR (T2.StartTime BETWEEN T1.StartTime AND T1.EndTime) )
GROUP BY T1.UserId|||TDT (tim.trujillo@.gmd.com) writes:
> I am trying to figure out if I can do this in a SQL query. I need to
> know how many users are active during a given time period. I want to
> be able to return the results below. If you look at the row with
> User3 I only want to count User2 once even though he was active twice
> during the time frame for User3
> Start Time End Time User Id Concurrent User
> Count
> 06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1 1
> 06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1 3
> 06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2 3
> 06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3 3
> 06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2 2
> 06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2 1
> I am pretty much stumped as how to proceed.
I assume that the above is the desired output. But how does the input
look like? It would help to have the CREATE TABLE statement and
INSERT statements for the test data. Then it would be simple to copy-paste
into a query tool to develop a tested query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Lubdha that last query did not work. I was geting resource limit has
been reached and the query was cancelled. But here is script to create
the table and insert rows.
CREATE TABLE [dbo].[UserActivity]
(
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[UserID] [varchar](50) NULL
)
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:38:21 AM', '06/22/2006 6:38:25 AM','User1')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:38:56 AM','06/22/2006 6:39:05 AM','User1')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:03 AM','06/22/2006 6:39:07 AM','User2')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:04 AM','06/22/2006 6:39:08 AM','User3')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:07 AM','06/22/2006 6:39:11 AM','User2')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:22 AM','06/22/2006 6:39:24 AM','User2')
Erland Sommarskog wrote:
> TDT (tim.trujillo@.gmd.com) writes:
> I assume that the above is the desired output. But how does the input
> look like? It would help to have the CREATE TABLE statement and
> INSERT statements for the test data. Then it would be simple to copy-paste
> into a query tool to develop a tested query.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for providing the table and data.
SELECT A.StartTime,
A.EndTime,
A.UserID,
count(distinct B.UserID) as ConcurrentUserCount
FROM UserActivity as A
JOIN UserActivity as B
ON A.StartTime between B.StartTime and B.EndTime
OR A.EndTime between B.StartTime and B.EndTime
or B.StartTime between A.StartTime and A.EndTime
GROUP BY A.StartTime,
A.EndTime,
A.UserID
ORDER BY 1, 2, 3
Roy Harvey
Beacon Falls, CT
On 23 Jun 2006 15:46:23 -0700, "TDT" <tim.trujillo@.gmd.com> wrote:
>Lubdha that last query did not work. I was geting resource limit has
>been reached and the query was cancelled. But here is script to create
>the table and insert rows.
>CREATE TABLE [dbo].[UserActivity]
>(
> [StartTime] [datetime] NULL,
> [EndTime] [datetime] NULL,
> [UserID] [varchar](50) NULL
> )
>
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:38:21 AM', '06/22/2006 6:38:25 AM','User1')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:38:56 AM','06/22/2006 6:39:05 AM','User1')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:03 AM','06/22/2006 6:39:07 AM','User2')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:04 AM','06/22/2006 6:39:08 AM','User3')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:07 AM','06/22/2006 6:39:11 AM','User2')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:22 AM','06/22/2006 6:39:24 AM','User2')
>
>Erland Sommarskog wrote:|||This is not a table; it has no key and no possible way to get a key.
If you knew what a table was, would you have written this? If you knew
either ISO-8601 or SQL, would you have used the proper temporal
formats?
CREATE TABLE UserActivity
(user_id VARCHAR(50) NOT NULL, -- magic data type used by newbies!
start_time DATETIME NOT NULL,
end_time DATETIME, -- null = current
PRIMARY KEY (user_id, start_time))
Where is the DDL for your reporting periods? What is your
grandularity? There are some specs missing here. However, you can get
samples at points in time; Set up a VIEW or TABLE with times in it.
SELECT R.report_time, COUNT(user_id) AS active_user_cnt
FROM ReportPeriods AS R, UserActivity AS U
WHERE R.report_time BETWEEN U.start_time AND U.end_time
GROUP BY R.report_time;
This actually works pretty well with a fine grandularity for simple
time series analysis. But if you wanted to do (n)-minute durations,
then we need a very complex set of rules for logging in and out within
a single duration.

Gererating Dynamic Queries During Run Time

How to Gererating Dynamic Queries During Run Time and execute the results

Thanks in advance

Suresh

You will have to use dynamic SQL.

I highly recommend that you review these articles -they will guide you, and they will explain the cautions and dangers.

Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

sql

Generation of report takes a long time (performance issue)

Hi,

My company is using MS SQL Server 2000 Developer Edition and Report Services.
We have some tables which are have now around 4000000 rows. When we genarating report
SELECT sessionNum, moteID, dbo.MacAddrConv(macAddr) AS macAddr, chID, dbo.TimestampConv(timestamp) AS timestamp, value
FROM NotificationData
ORDER BY timestamp
it takes 15 minute. Our table is located in the Server whic has these parametres:

1- Currently there are about 4.0 million entry in the database that uses 1638.44 MB of memory

2- The new SQL server has 3.39 GHz Intel Xeon processor, 3.00 GB of RAM and 192 GB of hard drive.

Why it takes so long time to generate the reports? Could you help us to improve the performance for our Database?

Thanks a lot,

Alice

The query selects 4 million rows. If you just show all the rows in the report with about 40 rows per page, you would really want to generate a report with 100.000 pages?

If however, you just show aggregated values in the report (but not the detail rows), you should rather perform the aggregations inside the query - because the aggregations will be performed much more efficiently inside the database.

-- Robert

|||i'm not impressed with reporting services' scalability

i have reports that are much smaller than yours that take forever to run

figure out a way to make it smaller, it isn't going to handle it nicely|||1st question, does ur report must show so many record at 1 time?i believe that even you can show up but u still having the printing problem.
2nd question, how you layout ur report?

my suggestion would be try to put more parameter or fixed the output record no for each page, and try to do any math calculation in query instead of report. hope can help|||

Generating ridiculously huge reports is a problem with every reporting system I've seen. However, I think there are a couple of options here.

First, use the new query execution plan tools to make sure the query isnt too complicated.|||Changing the report source to a SQL Server stored procedure also can have dramatic performance boosts. This is mainly due to procs being pre-compiled.sql

Generation of report takes a long time (performance issue)

Hi,

My company is using MS SQL Server 2000 Developer Edition and Report Services.
We have some tables which are have now around 4000000 rows. When we genarating report
SELECT sessionNum, moteID, dbo.MacAddrConv(macAddr) AS macAddr, chID, dbo.TimestampConv(timestamp) AS timestamp, value
FROM NotificationData
ORDER BY timestamp
it takes 15 minute. Our table is located in the Server whic has these parametres:

1- Currently there are about 4.0 million entry in the database that uses 1638.44 MB of memory

2- The new SQL server has 3.39 GHz Intel Xeon processor, 3.00 GB of RAM and 192 GB of hard drive.

Why it takes so long time to generate the reports? Could you help us to improve the performance for our Database?

Thanks a lot,

Alice

The query selects 4 million rows. If you just show all the rows in the report with about 40 rows per page, you would really want to generate a report with 100.000 pages?

If however, you just show aggregated values in the report (but not the detail rows), you should rather perform the aggregations inside the query - because the aggregations will be performed much more efficiently inside the database.

-- Robert

|||i'm not impressed with reporting services' scalability

i have reports that are much smaller than yours that take forever to run

figure out a way to make it smaller, it isn't going to handle it nicely|||1st question, does ur report must show so many record at 1 time?i believe that even you can show up but u still having the printing problem.
2nd question, how you layout ur report?

my suggestion would be try to put more parameter or fixed the output record no for each page, and try to do any math calculation in query instead of report. hope can help|||

Generating ridiculously huge reports is a problem with every reporting system I've seen. However, I think there are a couple of options here.

First, use the new query execution plan tools to make sure the query isnt too complicated.|||Changing the report source to a SQL Server stored procedure also can have dramatic performance boosts. This is mainly due to procs being pre-compiled.

Generation of report takes a long time (performance issue)

Hi,

My company is using MS SQL Server 2000 Developer Edition and Report Services.
We have some tables which are have now around 4000000 rows. When we genarating report
SELECT sessionNum, moteID, dbo.MacAddrConv(macAddr) AS macAddr, chID, dbo.TimestampConv(timestamp) AS timestamp, value
FROM NotificationData
ORDER BY timestamp
it takes 15 minute. Our table is located in the Server whic has these parametres:

1- Currently there are about 4.0 million entry in the database that uses 1638.44 MB of memory

2- The new SQL server has 3.39 GHz Intel Xeon processor, 3.00 GB of RAM and 192 GB of hard drive.

Why it takes so long time to generate the reports? Could you help us to improve the performance for our Database?

Thanks a lot,

Alice

The query selects 4 million rows. If you just show all the rows in the report with about 40 rows per page, you would really want to generate a report with 100.000 pages?

If however, you just show aggregated values in the report (but not the detail rows), you should rather perform the aggregations inside the query - because the aggregations will be performed much more efficiently inside the database.

-- Robert

|||i'm not impressed with reporting services' scalability

i have reports that are much smaller than yours that take forever to run

figure out a way to make it smaller, it isn't going to handle it nicely|||1st question, does ur report must show so many record at 1 time?i believe that even you can show up but u still having the printing problem.
2nd question, how you layout ur report?

my suggestion would be try to put more parameter or fixed the output record no for each page, and try to do any math calculation in query instead of report. hope can help|||

Generating ridiculously huge reports is a problem with every reporting system I've seen. However, I think there are a couple of options here.

First, use the new query execution plan tools to make sure the query isnt too complicated.|||Changing the report source to a SQL Server stored procedure also can have dramatic performance boosts. This is mainly due to procs being pre-compiled.

Generation of estimated execution plan takes a long time

Hello,
I am puzzled by following problem. One of our tables has more than 12
billion rows. Usually, query plan and query itself is executed almost
immediately but occasionally, it take about 2 minutes to generate estimated
query execution plan for following query:
SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
There is a PK clustered index defined on qwEventIx field. I do not observer
any blocking on this object when it takes a long time to generate estimated
query plan. There is a plan cached in syscacheobjects for this query.
I was wondering if anyone can explain why it takes such a long time to
generate estimated query plan on some occasions.
Thanks,
IgorIgor,
Are the statistics current and up-to-date?
HTH
Jerry
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12
> billion rows. Usually, query plan and query itself is executed almost
> immediately but occasionally, it take about 2 minutes to generate
> estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not
> observer any blocking on this object when it takes a long time to
> generate estimated query plan. There is a plan cached in syscacheobjects
> for this query.
> I was wondering if anyone can explain why it takes such a long time to
> generate estimated query plan on some occasions.
> Thanks,
> Igor
>|||It could be that this is when auto-update of statistics occurs. You can catc
h that in Profiler using
Object, Auto Stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12 bill
ion rows. Usually, query
> plan and query itself is executed almost immediately but occasionally, it
take about 2 minutes to
> generate estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not observe
r any blocking on this
> object when it takes a long time to generate estimated query plan. There
is a plan cached in
> syscacheobjects for this query.
> I was wondering if anyone can explain why it takes such a long time to gen
erate estimated query
> plan on some occasions.
> Thanks,
> Igor
>|||Tibor,
You were absolutely correct! SQL Server was updating statistics on 12
billion rows table. It was taking over 2 minutes. I suppose it might make
sense to disable AUTOSTATS on this table with sp_autostats and update
statistics manually during off-peak hours. Would you recommend this
approach?
Thanks,
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
> It could be that this is when auto-update of statistics occurs. You can
> catch that in Profiler using Object, Auto Stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>|||Jerry,
It is indeed statistics related.
Thanks,
Igor
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uQmyqVa2FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Igor,
> Are the statistics current and up-to-date?
> HTH
> Jerry
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>|||Yes, with that long auto.stats time, disabling autostats for that table is r
easonable. Make sure you
schedule manual update statistics with a reasonable sample and at a good tim
e (like not before some
big batch, but after :-) ).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:e1KGDu%232FHA.3420@.TK2MSFTNGP15.phx.gbl...
> Tibor,
> You were absolutely correct! SQL Server was updating statistics on 12 b
illion rows table. It
> was taking over 2 minutes. I suppose it might make sense to disable AUTOST
ATS on this table with
> sp_autostats and update statistics manually during off-peak hours. Would y
ou recommend this
> approach?
> Thanks,
> Igor
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
>

Generation of estimated execution plan takes a long time

Hello,
I am puzzled by following problem. One of our tables has more than 12
billion rows. Usually, query plan and query itself is executed almost
immediately but occasionally, it take about 2 minutes to generate estimated
query execution plan for following query:
SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
There is a PK clustered index defined on qwEventIx field. I do not observer
any blocking on this object when it takes a long time to generate estimated
query plan. There is a plan cached in syscacheobjects for this query.
I was wondering if anyone can explain why it takes such a long time to
generate estimated query plan on some occasions.
Thanks,
Igor
Igor,
Are the statistics current and up-to-date?
HTH
Jerry
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12
> billion rows. Usually, query plan and query itself is executed almost
> immediately but occasionally, it take about 2 minutes to generate
> estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not
> observer any blocking on this object when it takes a long time to
> generate estimated query plan. There is a plan cached in syscacheobjects
> for this query.
> I was wondering if anyone can explain why it takes such a long time to
> generate estimated query plan on some occasions.
> Thanks,
> Igor
>
|||It could be that this is when auto-update of statistics occurs. You can catch that in Profiler using
Object, Auto Stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12 billion rows. Usually, query
> plan and query itself is executed almost immediately but occasionally, it take about 2 minutes to
> generate estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not observer any blocking on this
> object when it takes a long time to generate estimated query plan. There is a plan cached in
> syscacheobjects for this query.
> I was wondering if anyone can explain why it takes such a long time to generate estimated query
> plan on some occasions.
> Thanks,
> Igor
>
|||Tibor,
You were absolutely correct! SQL Server was updating statistics on 12
billion rows table. It was taking over 2 minutes. I suppose it might make
sense to disable AUTOSTATS on this table with sp_autostats and update
statistics manually during off-peak hours. Would you recommend this
approach?
Thanks,
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
> It could be that this is when auto-update of statistics occurs. You can
> catch that in Profiler using Object, Auto Stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>
|||Jerry,
It is indeed statistics related.
Thanks,
Igor
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uQmyqVa2FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Igor,
> Are the statistics current and up-to-date?
> HTH
> Jerry
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>
|||Yes, with that long auto.stats time, disabling autostats for that table is reasonable. Make sure you
schedule manual update statistics with a reasonable sample and at a good time (like not before some
big batch, but after :-) ).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:e1KGDu%232FHA.3420@.TK2MSFTNGP15.phx.gbl...
> Tibor,
> You were absolutely correct! SQL Server was updating statistics on 12 billion rows table. It
> was taking over 2 minutes. I suppose it might make sense to disable AUTOSTATS on this table with
> sp_autostats and update statistics manually during off-peak hours. Would you recommend this
> approach?
> Thanks,
> Igor
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
>

Generation of estimated execution plan takes a long time

Hello,
I am puzzled by following problem. One of our tables has more than 12
billion rows. Usually, query plan and query itself is executed almost
immediately but occasionally, it take about 2 minutes to generate estimated
query execution plan for following query:
SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
There is a PK clustered index defined on qwEventIx field. I do not observer
any blocking on this object when it takes a long time to generate estimated
query plan. There is a plan cached in syscacheobjects for this query.
I was wondering if anyone can explain why it takes such a long time to
generate estimated query plan on some occasions.
Thanks,
IgorIgor,
Are the statistics current and up-to-date?
HTH
Jerry
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12
> billion rows. Usually, query plan and query itself is executed almost
> immediately but occasionally, it take about 2 minutes to generate
> estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not
> observer any blocking on this object when it takes a long time to
> generate estimated query plan. There is a plan cached in syscacheobjects
> for this query.
> I was wondering if anyone can explain why it takes such a long time to
> generate estimated query plan on some occasions.
> Thanks,
> Igor
>|||It could be that this is when auto-update of statistics occurs. You can catch that in Profiler using
Object, Auto Stats.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am puzzled by following problem. One of our tables has more than 12 billion rows. Usually, query
> plan and query itself is executed almost immediately but occasionally, it take about 2 minutes to
> generate estimated query execution plan for following query:
> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
> There is a PK clustered index defined on qwEventIx field. I do not observer any blocking on this
> object when it takes a long time to generate estimated query plan. There is a plan cached in
> syscacheobjects for this query.
> I was wondering if anyone can explain why it takes such a long time to generate estimated query
> plan on some occasions.
> Thanks,
> Igor
>|||Tibor,
You were absolutely correct! SQL Server was updating statistics on 12
billion rows table. It was taking over 2 minutes. I suppose it might make
sense to disable AUTOSTATS on this table with sp_autostats and update
statistics manually during off-peak hours. Would you recommend this
approach?
Thanks,
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
> It could be that this is when auto-update of statistics occurs. You can
> catch that in Profiler using Object, Auto Stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I am puzzled by following problem. One of our tables has more than 12
>> billion rows. Usually, query plan and query itself is executed almost
>> immediately but occasionally, it take about 2 minutes to generate
>> estimated query execution plan for following query:
>> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
>> There is a PK clustered index defined on qwEventIx field. I do not
>> observer any blocking on this object when it takes a long time to
>> generate estimated query plan. There is a plan cached in syscacheobjects
>> for this query.
>> I was wondering if anyone can explain why it takes such a long time to
>> generate estimated query plan on some occasions.
>> Thanks,
>> Igor
>|||Jerry,
It is indeed statistics related.
Thanks,
Igor
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uQmyqVa2FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Igor,
> Are the statistics current and up-to-date?
> HTH
> Jerry
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I am puzzled by following problem. One of our tables has more than 12
>> billion rows. Usually, query plan and query itself is executed almost
>> immediately but occasionally, it take about 2 minutes to generate
>> estimated query execution plan for following query:
>> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
>> There is a PK clustered index defined on qwEventIx field. I do not
>> observer any blocking on this object when it takes a long time to
>> generate estimated query plan. There is a plan cached in syscacheobjects
>> for this query.
>> I was wondering if anyone can explain why it takes such a long time to
>> generate estimated query plan on some occasions.
>> Thanks,
>> Igor
>|||Yes, with that long auto.stats time, disabling autostats for that table is reasonable. Make sure you
schedule manual update statistics with a reasonable sample and at a good time (like not before some
big batch, but after :-) ).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:e1KGDu%232FHA.3420@.TK2MSFTNGP15.phx.gbl...
> Tibor,
> You were absolutely correct! SQL Server was updating statistics on 12 billion rows table. It
> was taking over 2 minutes. I suppose it might make sense to disable AUTOSTATS on this table with
> sp_autostats and update statistics manually during off-peak hours. Would you recommend this
> approach?
> Thanks,
> Igor
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%2311fWFg2FHA.956@.TK2MSFTNGP10.phx.gbl...
>> It could be that this is when auto-update of statistics occurs. You can catch that in Profiler
>> using Object, Auto Stats.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:uIwoFzZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I am puzzled by following problem. One of our tables has more than 12 billion rows. Usually,
>> query plan and query itself is executed almost immediately but occasionally, it take about 2
>> minutes to generate estimated query execution plan for following query:
>> SELECT MAX(qwEventIx) FROM FTE_STREAM_EVENT (NOLOCK)
>> There is a PK clustered index defined on qwEventIx field. I do not observer any blocking on this
>> object when it takes a long time to generate estimated query plan. There is a plan cached in
>> syscacheobjects for this query.
>> I was wondering if anyone can explain why it takes such a long time to generate estimated query
>> plan on some occasions.
>> Thanks,
>> Igor
>>
>sql

Generating script per object

Hi,
I want to generate Script for 4 Stored Procedures at a time in SQL SERVER 2005 as 4 seperate files.

In SQL SERVER 2000 there is a option
-Script file per object, using which we can generate script only once for all the 4 sps as 4 seperate files
Is there any such option in 2005?

Please suggest.

Thanks in advance

Ram

They added this feature to the Scripting Wizard in SP2.|||

Jason Callas wrote:

They added this feature to the Scripting Wizard in SP2.

To elaborate a little bit more... if you use the Generate Script Wizard in SQL Server 2005 SP2, you can choose to create one file per object.

Steps:

1) Right-click on a database -> Tasks -> Generate Scripts...

2) On the "Output Option" screen

a) Choose "Script to file"

b) Choose "File per object"

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

sql

Generating a querry parameters

Hello,

Need a bit of help here. In one table i have my production data on some wheels. Each time the wheel is touched, it gets smaller. Then in another table I have the primary data of that wheel, ie price vendor, make model and scrap diameter.

What I'd like to figure out is, In one month, what was the wheel size to start off, what is the end size to give me a total size used during that month. If i could get that, then I think I would be able to figure out what the price cost for useage of that wheel was for the month.

Any help?

Thanks Brian

Could you please provide a description (preferrably one that's runnable in QA) of the tables involved, how they are related, and also a few rows of data that demonstrates wheels being 'touched'.

It does seem like what you want is to find start and end of month for a given wheel, and then subtract end-size from start-size, though it's pretty hard to know how to do it without knowledge of your tables or the data in them =;o)

/Kenneth

|||

Lets say this is the wheel table

Wheel_NO New_Diameter Scrap_Diameter Current_Diameter Price Date_Purchased

12345678 15.2384 12.2500 14.3484 $19000.00 11/21/2005

12345677 15.2384 12.2500 15.1024 $18500.00 7/8/2005

12345679 15.2384 12.2500 13.8754 $17250.00 2/14/2005

12345673 15.2384 12.2500 14.9824 $16000.00 1/1/2004

This in the production History I have

Wheel_No Start_DT End_Dt E_Diameter Start_Dia

12345678 11/21/2005 15:34 11/21/2005 15:48 15.2225 15.2384

12345678 11/22/2005 15:34 11/22/2005 15:48 15.1055 15.2225

12345678 11/25/2005 15:34 11/25/2005 15:48 15.0054 15.1055

12345678 10/25/2006 15:34 10/25/2006 15:48 14.9654 15.0054

12345678 11/01/2006 15:34 11/01/2006 15:48 14.9561 14.9654

12345678 11/15/2006 15:34 11/15/2006 15:48 14.5466 14.9561

12345678 11/21/2006 15:36 11/21/2006 15:48 14.3484 14.5466

12345677 10/20/2006 14:36 10/20/2006 15:21 15.1024 15.2001

12345677 10/31/2006 14:36 10/31/2006 15:21 14.9856 15.1024

12345677 10/20/2006 14:36 10/20/2006 15:21 14.9548 14.9856

So from the production data table I want to know What was the Start diameter and the end diameter of each wheel that ground for November 2006

Does this help at all?

|||

Brian:

Please take a look and see if this is on the right track:

set nocount on
declare @.wheel table
( wheel_no varchar (10) not null,
new_diameter numeric (9,4) not null,
scrap_diameter numeric (9,4) not null,
current_diameter numeric (9,4) not null,
price numeric (9,2) not null,
primary key (wheel_no)
)
insert into @.wheel values ('12345678', 15.2384, 12.2500, 14.3484, 19000.00)
insert into @.wheel values ('12345677', 15.2384, 12.2500, 15.1024, 18500.00)
insert into @.wheel values ('12345679', 15.2384, 12.2500, 13.8754, 17250.00)
insert into @.wheel values ('12345673', 15.2384, 12.2500, 14.9824, 16000.00)
--select * from @.wheel

declare @.prodHistory table
( wheel_no varchar (10) not null,
start_dt datetime not null,
end_dt datetime not null,
e_diameter numeric (9,4) not null,
start_dia numeric (9,4) not null,
primary key (wheel_no, start_dt, start_dia)
)
insert into @.prodHistory values ('12345678', '11/21/5 15:34', '11/21/5 15:48', 15.2225, 15.2384)
insert into @.prodHistory values ('12345678', '11/22/5 15:34', '11/22/5 15:48', 15.1055, 15.2225)
insert into @.prodHistory values ('12345678', '11/25/5 15:34', '11/25/5 15:48', 15.0054, 15.1055)
insert into @.prodHistory values ('12345678', '10/25/6 15:34', '10/25/6 15:48', 14.9654, 15.0054)
insert into @.prodHistory values ('12345678', '11/1/6 15:34', '11/1/6 15:48', 14.9561, 14.9654)
insert into @.prodHistory values ('12345678', '11/15/6 15:34', '11/15/6 15:48', 14.5466, 14.9561)
insert into @.prodHistory values ('12345678', '11/21/6 15:34', '11/21/6 15:48', 14.3484, 14.5466)
insert into @.prodHistory values ('12345677', '10/20/6 14:36', '10/20/6 15:21', 15.1024, 15.2001)
insert into @.prodHistory values ('12345677', '10/31/6 14:36', '10/31/6 15:21', 14.9856, 14.9856)
insert into @.prodHistory values ('12345677', '10/20/6 14:36', '10/20/6 15:21', 14.9548, 14.9856)
--select * from @.prodHistory

-- -
-- This is the subquery where most of the work takes place
-- I show the output of this separately so that it is easire
-- to gauge whether or not this query is on track.
-- -
/*
select h.wheel_no,
w.price / (w.new_diameter - w.scrap_diameter)
as [Price / Dia],
month (h.start_dt) as month,
max (h.start_dia) as start_diameter,
min (h.e_diameter) as end_diameter,
max (h.start_dia) - min (e_diameter) as diff
from @.prodHistory h
inner join @.wheel w
on h.wheel_no = w.wheel_no
group by h.wheel_no,
w.price / (w.new_diameter - w.scrap_diameter),
month (h.start_dt)
*/

-- wheel_no Price / Dia month start_diameter end_diameter diff
-- - -- -- --
-- 12345677 6190.6036675143889 10 15.2001 14.9548 .2453
-- 12345678 6357.9172801499129 10 15.0054 14.9654 .0400
-- 12345678 6357.9172801499129 11 15.2384 14.3484 .8900

select wheel_no,
month,
convert (numeric (9,2), diff * [Price / Dia]) as Cost
from (

select h.wheel_no,
w.price / (w.new_diameter - w.scrap_diameter)
as [Price / Dia],
month (h.start_dt) as month,
max (h.start_dia) as start_diameter,
min (h.e_diameter) as end_diameter,
max (h.start_dia) - min (e_diameter) as diff
from @.prodHistory h
inner join @.wheel w
on h.wheel_no = w.wheel_no
group by h.wheel_no,
w.price / (w.new_diameter - w.scrap_diameter),
month (h.start_dt)
) x

-- -- Sample Output:

-- wheel_no month Cost
-- - --
-- 12345677 10 1518.56
-- 12345678 10 254.32
-- 12345678 11 5658.55

Generate values between a starting and stopping point (time values).

[RS 2005]

Given the starting and stopping points (time values), how do I generate values between these points.

For example, if I have 08 (representing Hour) as a starting point and 12 as a stopping point.

From this I would like to generate a data sequence like 08, 09, 10, 11, and 12.

So how do I accomplish this? In SQL or in the RS?

The only thing I can think of is using a WHILE loop and a temporary table in SQL (not to keen on doing this).

//H?kan

Use custom code in the report to achieve this. Take the start and end points as input parameters as you might be already doing. Then pass them to the function in custom code and write VB code inside that function to generate all intermediate values. Then store that array in a global variable in the custom code itself which you could refer from anywhere in your report.

Shyam

|||I would either use a function or a stored procedure or a plain query for this.

CREATE FUNCTION dbo.NumberRange

(

@.Starting INT,

@.End INT

)

RETURNS @.Numbers TABLE

(

Number INT

)

AS

BEGIN

WHILE @.Starting <= @.End

BEGIN

INSERT INTO @.Numbers VALUES (@.Starting)

SET @.Starting = @.Starting + 1

END

RETURN

END

SELECT * FROM dbo.NumberRange(1,10)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Hakan is not keen on using SQL and WHILE loops and tables.|||I know, thats why I write the function for him.|||

H?kan,

do you need the data to be included in the dataset so that it can be rendered on the report? If so, you will have to generate it in the SQL as mentioned by Jens. If all you need to do is refer to the intermediate values from other expressions then a global array is fine, but make sure you have null and bounds checks in your custom code before trying to access the array.

|||

I guess the function also has the WHILE loop and tables.

Instead he can have a master table with just one column and values from 00 to 100 and just select the necessary numbers from that table between the given start and end.

Shyam

Generate SCRIPT

Hello,
Using the new "SQL Server Management Studio" for SQL Server 2005, how would
I generate scripts for more than one object at a time. Let us say I want to
generate scripts for all tables, how would I do that?.
Thanks,
Ganesh
You can select multiple objects displayed in the Summary
page - then right click and script.
-Sue
On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
<GaneshMuthuvelu@.discussions.microsoft.com> wrote:

>Hello,
>Using the new "SQL Server Management Studio" for SQL Server 2005, how would
>I generate scripts for more than one object at a time. Let us say I want to
>generate scripts for all tables, how would I do that?.
>Thanks,
>Ganesh
|||Where is this "Summary Page"?. Sorry, could you expalin it a little more?.
Thanks,
Ganesh
"Sue Hoegemeier" wrote:

> You can select multiple objects displayed in the Summary
> page - then right click and script.
> -Sue
> On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
> <GaneshMuthuvelu@.discussions.microsoft.com> wrote:
>
>
|||From Object Explorer...Right-click database...tasks...generate scripts
i think you can figure out the rest.
HTH,
JL
"Ganesh Muthuvelu" <GaneshMuthuvelu@.discussions.microsoft.com> wrote in
message news:B59BAC5D-1499-4F45-919E-1967F02F5382@.microsoft.com...
> Hello,
> Using the new "SQL Server Management Studio" for SQL Server 2005, how
> would
> I generate scripts for more than one object at a time. Let us say I want
> to
> generate scripts for all tables, how would I do that?.
> Thanks,
> Ganesh
|||Under the defaults, It's to the right of registered servers
and object explorer when you first open up Management
Studio. Otherwise, hit F7 to display it. Or from the Menu go
to view and then select Summary.
-Sue
On Fri, 20 Jan 2006 12:57:04 -0800, "Ganesh Muthuvelu"
<GaneshMuthuvelu@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Where is this "Summary Page"?. Sorry, could you expalin it a little more?.
>Thanks,
>Ganesh
>"Sue Hoegemeier" wrote:

Generate SCRIPT

Hello,
Using the new "SQL Server Management Studio" for SQL Server 2005, how would
I generate scripts for more than one object at a time. Let us say I want to
generate scripts for all tables, how would I do that?.
Thanks,
GaneshYou can select multiple objects displayed in the Summary
page - then right click and script.
-Sue
On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
<GaneshMuthuvelu@.discussions.microsoft.com> wrote:

>Hello,
>Using the new "SQL Server Management Studio" for SQL Server 2005, how would
>I generate scripts for more than one object at a time. Let us say I want to
>generate scripts for all tables, how would I do that?.
>Thanks,
>Ganesh|||Where is this "Summary Page"?. Sorry, could you expalin it a little more?.
Thanks,
Ganesh
"Sue Hoegemeier" wrote:

> You can select multiple objects displayed in the Summary
> page - then right click and script.
> -Sue
> On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
> <GaneshMuthuvelu@.discussions.microsoft.com> wrote:
>
>|||From Object Explorer...Right-click database...tasks...generate scripts
i think you can figure out the rest.
HTH,
JL
"Ganesh Muthuvelu" <GaneshMuthuvelu@.discussions.microsoft.com> wrote in
message news:B59BAC5D-1499-4F45-919E-1967F02F5382@.microsoft.com...
> Hello,
> Using the new "SQL Server Management Studio" for SQL Server 2005, how
> would
> I generate scripts for more than one object at a time. Let us say I want
> to
> generate scripts for all tables, how would I do that?.
> Thanks,
> Ganesh|||Under the defaults, It's to the right of registered servers
and object explorer when you first open up Management
Studio. Otherwise, hit F7 to display it. Or from the Menu go
to view and then select Summary.
-Sue
On Fri, 20 Jan 2006 12:57:04 -0800, "Ganesh Muthuvelu"
<GaneshMuthuvelu@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Where is this "Summary Page"?. Sorry, could you expalin it a little more?.
>Thanks,
>Ganesh
>"Sue Hoegemeier" wrote:
>

Generate SCRIPT

Hello,
Using the new "SQL Server Management Studio" for SQL Server 2005, how would
I generate scripts for more than one object at a time. Let us say I want to
generate scripts for all tables, how would I do that?.
Thanks,
GaneshYou can select multiple objects displayed in the Summary
page - then right click and script.
-Sue
On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
<GaneshMuthuvelu@.discussions.microsoft.com> wrote:
>Hello,
>Using the new "SQL Server Management Studio" for SQL Server 2005, how would
>I generate scripts for more than one object at a time. Let us say I want to
>generate scripts for all tables, how would I do that?.
>Thanks,
>Ganesh|||From Object Explorer...Right-click database...tasks...generate scripts
i think you can figure out the rest.
HTH,
JL
"Ganesh Muthuvelu" <GaneshMuthuvelu@.discussions.microsoft.com> wrote in
message news:B59BAC5D-1499-4F45-919E-1967F02F5382@.microsoft.com...
> Hello,
> Using the new "SQL Server Management Studio" for SQL Server 2005, how
> would
> I generate scripts for more than one object at a time. Let us say I want
> to
> generate scripts for all tables, how would I do that?.
> Thanks,
> Ganesh|||Where is this "Summary Page"?. Sorry, could you expalin it a little more?.
Thanks,
Ganesh
"Sue Hoegemeier" wrote:
> You can select multiple objects displayed in the Summary
> page - then right click and script.
> -Sue
> On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
> <GaneshMuthuvelu@.discussions.microsoft.com> wrote:
> >Hello,
> >Using the new "SQL Server Management Studio" for SQL Server 2005, how would
> >I generate scripts for more than one object at a time. Let us say I want to
> >generate scripts for all tables, how would I do that?.
> >
> >Thanks,
> >Ganesh
>|||Under the defaults, It's to the right of registered servers
and object explorer when you first open up Management
Studio. Otherwise, hit F7 to display it. Or from the Menu go
to view and then select Summary.
-Sue
On Fri, 20 Jan 2006 12:57:04 -0800, "Ganesh Muthuvelu"
<GaneshMuthuvelu@.discussions.microsoft.com> wrote:
>Where is this "Summary Page"?. Sorry, could you expalin it a little more?.
>Thanks,
>Ganesh
>"Sue Hoegemeier" wrote:
>> You can select multiple objects displayed in the Summary
>> page - then right click and script.
>> -Sue
>> On Fri, 20 Jan 2006 11:13:02 -0800, "Ganesh Muthuvelu"
>> <GaneshMuthuvelu@.discussions.microsoft.com> wrote:
>> >Hello,
>> >Using the new "SQL Server Management Studio" for SQL Server 2005, how would
>> >I generate scripts for more than one object at a time. Let us say I want to
>> >generate scripts for all tables, how would I do that?.
>> >
>> >Thanks,
>> >Ganesh
>>

Generate relational schema - old tables are always deleted and not recreated

I want to use the top down modelling features and generate tables from the OLAP objects by generating relational schema.

The problem:

First time using the "generate relational schema" - the tables are created.

Next time using this feature: new tables are generated, but all tables created in the past are lost. There is a warning, the schema can't be created, because there are user defined tables.

I found a way to reset this status, if I open the code for a dimension and delete the section from <Source to /Source>:

<Source xsi:type="DataSourceViewBinding" dwd:design-time-name="51a3fe82-65d2-402e-a846-d2b9689b177c">

<DataSourceViewID>LR OLAP</DataSourceViewID>

</Source>

After this I can recreate the tables. But there must be another way. I can't delete the source information from all object before each using of the "generate relational schema".

In the past, when I was evaluating the SSAS 2005, I had no such problems. What happens? Why are the tables deleted each time, if I generate relational schema?

The way you are trying to use Analysis Services is not exactly how it was envisioned originally.

The logic for which tables needs to be created is pretty simple. If object doesnt have bindings, the schema generation wizard will create a table in relational database supporting it.

Now, creating relational database schema is considered one time operation. Analysis Services is not exactly application to use for generating relational schemas. You go through the wizard, it creates all the tables for you and then you expected to keep and maintain set of tables created.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

generate real date time

hi, i need to create a function to do some like this...
update myTable set DateModify = getdate()
... but... i need to get a minimum difference bettwen records...
milisecons... or...
how can i get an array or cursor (SQL server side) with 500 records
with different datetime programatically?Hi,
I assume that you are asking about writing a procedure for this and not
a function. A function cannot update tables nor can it use getdate().
To do this in a procedure you can use the WAITFOR DELAY statement in
SQL.
Regards,
Kart
celerno wrote:
> hi, i need to create a function to do some like this...
> update myTable set DateModify = getdate()
> ... but... i need to get a minimum difference bettwen records...
> milisecons... or...
> how can i get an array or cursor (SQL server side) with 500 records
> with different datetime programatically?|||Hi Kart.. thanks for your response...
I try to use waitfor, but i can't find the way to do it.
sorry, i don't have good programming skills in sql server...
here is my problem:
i retrieve a bunch of data using the DateTime field to get the lastest
data using "select top N... ... where dateTimeNew>myDate"
after download the N rows, myDate gets the biggest dateTime value from
the N rows.
and when we have N+1 data with the same DateTime my new select Top is
equal to the previus sentence and i got the same data bunch.
in order to evade this problem i want to create a routine on store
procedure or any way on the server side to set different datetimes to
all data...
excuse my poor english... thanks in advance...!
On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:[vbcol=seagreen]
> Hi,
> I assume that you are asking about writing a procedure for this and not
> a function. A function cannot update tables nor can it use getdate().
> To do this in a procedure you can use the WAITFOR DELAY statement in
> SQL.
> Regards,
> Kart
> celerno wrote:
>
>
>|||yeah, this query it all that i need... it was so simple.!
reference to:
http://www.sqlteam.com/item.asp?ItemID=765
declare @.intCounter datetime
set @.intCounter = getdate()
update articulo
SET @.intCounter = fechahora = dateadd(s, 1, @.intCounter)
select fechahora from articulo
On 11 nov, 11:06, "celerno" <cele...@.gmail.com> wrote:[vbcol=seagreen]
> Hi Kart.. thanks for your response...
> I try to use waitfor, but i can't find the way to do it.
> sorry, i don't have good programming skills in sql server...
> here is my problem:
> i retrieve a bunch of data using the DateTime field to get the lastest
> data using "select top N... ... where dateTimeNew>myDate"
> after download the N rows, myDate gets the biggest dateTime value from
> the N rows.
> and when we have N+1 data with the same DateTime my new select Top is
> equal to the previus sentence and i got the same data bunch.
> in order to evade this problem i want to create a routine on store
> procedure or any way on the server side to set different datetimes to
> all data...
> excuse my poor english... thanks in advance...!
> On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:
>
>
>
>
>
>
>
>