RelativeTime

Hi,

I have a traditional Time dimension, MatchDate (Year - Qtr - Month). I have created a dummy dimension RelativeTime in trying to copy the Cognos functionality. I have tried to created a calculated member:

SUM(PeriodsToDate([MatchDate].[Year]),([RelativeTime].[Relative Time].[Current]))

but it results in a errormess:

Formula error - cannot find dimension member

("[RelativeTime].[Relative Time].[Current]")) in a name-binding function.

Any idea anyone?

Hi

After modifying the MDX syntax its working fine:

SUM(PeriodsToDate([MatchDate].[Year]),([RelativeTime].[Relative Time].&[1]))

This is giving me the YTD total, how do I create a Previous YTD ? I will try using ParallelPeriod

Larra

RelativeTime

Hi,

I have a traditional Time dimension, MatchDate (Year - Qtr - Month). I have created a dummy dimension RelativeTime in trying to copy the Cognos functionality. I have tried to created a calculated member:

SUM(PeriodsToDate([MatchDate].[Year]),([RelativeTime].[Relative Time].[Current]))

but it results in a errormess:

Formula error - cannot find dimension member

("[RelativeTime].[Relative Time].[Current]")) in a name-binding function.

Any idea anyone?

Hi

After modifying the MDX syntax its working fine:

SUM(PeriodsToDate([MatchDate].[Year]),([RelativeTime].[Relative Time].&[1]))

This is giving me the YTD total, how do I create a Previous YTD ? I will try using ParallelPeriod

Larra

Relative path for child packages

hello again!, this time I'm trying to run a Master Package from the SQL Server Agent but I can't set relative paths to the connections for all the child packages that the master package contains.
It only finishes execution when I set absolute paths for all connections in the connection manager within the SSIS Project.

Is there any property in the SQL Server Agent or mayby a workaround to solve this?
Use absolute paths. Any reason you need relative paths?|||

Hi Santiago,

Unfortunately you cannot pass parameters to SQL Agent jobs. Workarounds usually involve:

1. Package configurations: set the path in a variable and use Expressions in the Package Connection Managers to dynamically apply the variable value.

2. Call the package dynamically: use a stored procedure to build a "dtexec" command-line and execute it via xp_cmdshell.

There are other ways to accomplish this as well. Personally, I recommend package configurations. I use xp_cmdshell less and less these days and when I do I enable it, do what I need to do, and disable it.

Hope this helps,

Andy

|||You can use a variable and expression-based connection strings if you need the paths to be dynamic at runtime. I usually store a root path in a variable, then use expressions to prefix that onto the name of the child package before calling it.|||

jwelch wrote:

You can use a variable and expression-based connection strings if you need the paths to be dynamic at runtime. I usually store a root path in a variable, then use expressions to prefix that onto the name of the child package before calling it.

Same here. This is (one of) the reason(s) that I always use the same folder structure on all of my projects.

Common folder structure

(http://blogs.conchango.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Common-folder-structure.aspx)

I also include the root path variable in my package template.

SSIS: Package Template

(http://blogs.conchango.com/jamiethomson/archive/2007/03/11/SSIS_3A00_-Package-Template.aspx)

-Jamie

relative location chart and image or text

I have a chart, image and text side by side. At design time they all look
good (relative positions). My chart has min and max Y axis, so the height is
always constant. At run time chart, image and text are at different heights.
How can I make them all aligned vertically to the top. Thanks.Why don't you give the Location of These objects
Go to properties >location>top of the Objects to 0
hope this will help
Regards
Raj Deep.A
bhanoji wrote:
> I have a chart, image and text side by side. At design time they all look
> good (relative positions). My chart has min and max Y axis, so the height is
> always constant. At run time chart, image and text are at different heights.
> How can I make them all aligned vertically to the top. Thanks.

Relative Dates

I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
Thanks
Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||i am not sure how i would use thet for waht i want, basically to start with i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:

> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...[vbcol=seagreen]
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
sql

Relative Dates

I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
ThanksHave a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||i am not sure how i would use thet for waht i want, basically to start with i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:
> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
>> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000
>> BOL:
>> USE pubs
>> GO
>> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
>> FROM titles
>> GO
>> GetDate() will allow you to compare to the time the query was run.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>>

Relative Dates

I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
ThanksHave a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||i am not sure how i would use thet for waht i want, basically to start with
i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:

> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BO
L:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...[vbcol=seagreen]
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
>

Relational Model and XML

Hi,

This question has been bothering me for some time. A lot of people
seem to "think" XML is the king of data problems, and I've heard that
next version of SQL Server is going to have a strong XML flavor,
meantime, I seem to get the impression that a large number of
hard-core relational model gurus do not seem to be that impressed with
XML (technical value of this extra layer seems to be limited while
business value might be substantial for instance, more software work,
more disk space requirement etc. etc.). What's your take on this?
Generality or specifics, all welcome. One specific question is, how
can XML supplement relational model?

Thanks.Doug Baroter (qwert12345@.boxfrog.com) writes:
> This question has been bothering me for some time. A lot of people
> seem to "think" XML is the king of data problems, and I've heard that
> next version of SQL Server is going to have a strong XML flavor,
> meantime, I seem to get the impression that a large number of
> hard-core relational model gurus do not seem to be that impressed with
> XML (technical value of this extra layer seems to be limited while
> business value might be substantial for instance, more software work,
> more disk space requirement etc. etc.). What's your take on this?
> Generality or specifics, all welcome. One specific question is, how
> can XML supplement relational model?

A broad question, but XML seems to have its place. The point where XML
is really meaningful is data exchange. A database is a bunch of
tables, but they just sit in one place, and don't travel around. But
the data in them do. While XML may be bulky and lot of overhead in
bytes, it has the nice property that it defines a standard framework
that you can put your data into.

A nice side benefit of this, is that with XML we suddenly have
gotten a method of inserting lots of data into SQL Server with
just one roundtrip on the network: send down the XML document,
and then say INSERT ... OPENXML.

Then in Yukon they are taking it even further with adding Xquery,
you have systemm functions that return XML data etc. Maybe MS is
taking it a bit too far, but XML is here to stay.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> What's your take on this? Generality or specifics, all welcome. <<

There is lot of research work (esp. papers at SIGMOD, DBPL etc on XML
suggests ) going on in the field of XML, but I am not sure if anything
positive has been established to consider XML as a data model alternative.

Vendors provide the consumers what they ask for, not what is empirical
and/or principled. When competition and customer retention become deciding
factors, often technologies that are obsolete, gets revived with minor
changes, under new terminologies. That is how the all profit-based
industries, including the IT industry, work.

>> One specific question is, how can XML supplement relational model? <<

The fundamentals of data management clearly tell me that Relational model
needs no supplementation by "XML" for anything relevant. However, it is
confusing that so many press articles on the topic are filled with
exaggerations and techno-tyros, who cannot distinguish a data exchange
technology from a data model, opining about XML going to change the world
overnight.

--
- Anith
( Please reply to newsgroups only )|||Yes, this is really nice because the user still only has to have
permission to execute the stored procedure. :)

Actually I found a problem with OPENXML that I've been meaning to
post. We have legacy flat-file systems (which of course we want to
replace, but we can't just snap our fingers and bingo it's done) that
use the equivalent of a char field for various numeric-appearing
identifiers such as our contract numbers. So for example, the
contract number field in the legacy system is 10 characters, but right
now we're still in the 700000's so it shows up as four spaces followed
by six numeric characters. OPENXML eats all the leading spaces
(Before you ask, yes, I am enclosing each attribute in quotes), so
then afterwards I have to reinsert them with an UPDATE statement if I
want the systems to talk to each other.

Any thoughts?

On Sun, 28 Dec 2003 23:47:25 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>A nice side benefit of this, is that with XML we suddenly have
>gotten a method of inserting lots of data into SQL Server with
>just one roundtrip on the network: send down the XML document,
>and then say INSERT ... OPENXML.|||Erland Sommarskog <sommar@.algonet.se> wrote in message
> The point where XML is really meaningful is data exchange.
My point as well or agree whole-heartedly.

> A nice side benefit of this, is that with XML we suddenly have
> gotten a method of inserting lots of data into SQL Server with
> just one roundtrip on the network: send down the XML document,
> and then say INSERT ... OPENXML.
Wouldn't that require a lot of parsing especially considering
one-to-many relationships, binary objects etc. So, in that sense, not
necessarily "nice", IMHO.

> Then in Yukon they are taking it even further with adding Xquery,
> you have systemm functions that return XML data etc.
That's my sentiment as well.

>Maybe MS is
> taking it a bit too far, but XML is here to stay.
Thanks for your opinion. BTW, I forgot to add quotes for "business
value" in my original posting which could be misleading.|||I did not phrase my question correctly, which should have been "Would
XML add substantial value to data management?" I guess you would say
"No" while many other so-called forward-looking people may say "Yes".
I share your sentiment.

> The fundamentals of data management clearly tell me that Relational model
> needs no supplementation by "XML" for anything relevant. However, it is
> confusing that so many press articles on the topic are filled with
> exaggerations and techno-tyros, who cannot distinguish a data exchange
> technology from a data model, opining about XML going to change the world
> overnight.|||Ellen K. (72322.enno.esspeeayem.1016@.compuserve.com) writes:
> So for example, the contract number field in the legacy system is 10
> characters, but right now we're still in the 700000's so it shows up as
> four spaces followed by six numeric characters. OPENXML eats all the
> leading spaces (Before you ask, yes, I am enclosing each attribute in
> quotes), so then afterwards I have to reinsert them with an UPDATE
> statement if I want the systems to talk to each other.

I would guess that is part of the XML specification, but I'm not very
well versed in XML. However, this little snippet may help you to restore
the spaces directly:

create table #h (g char(10) NOT NULL)
go
declare @.xml nvarchar(4000), @.d int
select @.xml = '<H><B g=" 123456"/><B g="1234567890"/></H>'
exec sp_xml_preparedocument @.d output, @.xml
insert #h(g)
SELECT replicate(' ', 10 - len(g)) + g
FROM OPENXML(@.d, '/H/B', 1) WITH (g char(10)) AS x
exec sp_xml_removedocument @.d
select '<' + g + '>' FROM #h
go
drop table #h

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Doug Baroter (qwert12345@.boxfrog.com) writes:
> Wouldn't that require a lot of parsing especially considering
> one-to-many relationships, binary objects etc. So, in that sense, not
> necessarily "nice", IMHO.

Of course the parsing requires some CPU. But with today's CPU's I don't
think is a major issue. Network round-trips can easily be more expensive,
not the least on a busy network. And not the least 5000 individual
INSERT statements for each row rather than one for each table.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9460F25E5D928Yazorman@.127.0.0.1>...
> Of course the parsing requires some CPU. But with today's CPU's I don't
> think is a major issue. Network round-trips can easily be more expensive,
> not the least on a busy network. And not the least 5000 individual
> INSERT statements for each row rather than one for each table.

But with today's network speed, it used to be 10 MBPS, nowdays
standard 100 MBPS, in some environment, much greater than 100 MBPS;
simultaneous 1000 INSERTs into the same table at a given second would
seem to be rare even for a Fortune 100 company, hence, transaction
speed improvement does not seem to be that a big deal. Having said
that, I agree your arguement has a valid point.
Now, a totally separate question if you don't mind, probably you've
designed a stock trading system of your own, and I would think you've
been quite sucessful in doing that, do you take a student or two
occasionally?|||I have a batch process that runs every night which creates and then
inserts between 2000 and 5000 rows to a particular table, and I do it
with XML for exactly the reason Erland states, I don't want to make
all those roundtrips. We are nowhere NEAR Fortune 100, revenues are
less than $100MM.

On 29 Dec 2003 19:13:42 -0800, qwert12345@.boxfrog.com (Doug Baroter)
wrote:

>simultaneous 1000 INSERTs into the same table at a given second would
>seem to be rare even for a Fortune 100 company, hence, transaction
>speed improvement does not seem to be that a big deal.|||I'm not well-versed either, in fact I create the XML document by
<red-face emoticon> building a giant string. However, I sure like
being able to get 5000 rows across the network and into my table in
ONE SECOND. :)

I'm going to send myself the below and experiment with it at work,
thanks very much. :)

On Mon, 29 Dec 2003 22:48:36 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>Ellen K. (72322.enno.esspeeayem.1016@.compuserve.com) writes:
>> So for example, the contract number field in the legacy system is 10
>> characters, but right now we're still in the 700000's so it shows up as
>> four spaces followed by six numeric characters. OPENXML eats all the
>> leading spaces (Before you ask, yes, I am enclosing each attribute in
>> quotes), so then afterwards I have to reinsert them with an UPDATE
>> statement if I want the systems to talk to each other.
>I would guess that is part of the XML specification, but I'm not very
>well versed in XML. However, this little snippet may help you to restore
>the spaces directly:
> create table #h (g char(10) NOT NULL)
> go
> declare @.xml nvarchar(4000), @.d int
> select @.xml = '<H><B g=" 123456"/><B g="1234567890"/></H>'
> exec sp_xml_preparedocument @.d output, @.xml
> insert #h(g)
> SELECT replicate(' ', 10 - len(g)) + g
> FROM OPENXML(@.d, '/H/B', 1) WITH (g char(10)) AS x
> exec sp_xml_removedocument @.d
> select '<' + g + '>' FROM #h
> go
> drop table #h|||Doug Baroter (qwert12345@.boxfrog.com) writes:
> But with today's network speed, it used to be 10 MBPS, nowdays
> standard 100 MBPS, in some environment, much greater than 100 MBPS;
> simultaneous 1000 INSERTs into the same table at a given second would
> seem to be rare even for a Fortune 100 company, hence, transaction
> speed improvement does not seem to be that a big deal.

Oh, there is a lot more to it that you imagine!

First of all, network delays comes in two flavours: transfer speed
and latency. Even a if powerful network can have significant latency.
To take an extreme example: for a while, at least, the fastest Internet
connection that was an option for private persons was satellite links.
They can give you great download speeds. But the latency is longer than
on a 9600 modem line. A crowded network can also have significant
latency. If you send many INSERT operations, then you make many routing
requests. And since communication is synchrounous, you have to wait
for respnnse too.

But there are also things to consider on the SQL Server side. If you
send singular INSERT statements, you really lose. Here is a case where
you win big with stored procedures; SQL Server does not have to parse
each statement, but can resue the cached plan.

> Now, a totally separate question if you don't mind, probably you've
> designed a stock trading system of your own, and I would think you've
> been quite sucessful in doing that, do you take a student or two
> occasionally?

I have not designed any stock-trading system on my own, but I work
for a company that provides such a product. (And which is really the
work of many knowledgable persons, not only me.) Currently, we do
not have any openings, I'm sorry.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message >
> I have not designed any stock-trading system on my own, but I work
> for a company that provides such a product. (And which is really the
> work of many knowledgable persons, not only me.)
Thanks for the info. BTW, I was introduced to a CS professor who
designed such a system on his own, and the "word" goes around, he's
doing well with that and he himself indicates so as well, and implied
to help me get started with that too. Too bad, later on for some
weird reason he was offended.sql

Relating records with date ranges to Server Time dimension

I have a table whose records have a date range defined by a start_date and end_date. I would like to create a server time dimension so that I can relate each record in the table to a date in the time dimension when it is 'active' (i.e. the dimension date falls between the start_date and end_date inclusive). I am lost on how to accomplish this and any help is appreciated.

DW

DW, I would consider adding a column called "is_active" on the fact table. The SQL query would then include this column in the where clause. I would not limit the time dimension to just the active date range.
Vincent

related rooms query

hi, im working on this for a long time. i'm using MSsql-server2000
i have a table [visits] that records users visits to rooms. the columns are
room_id, user_id, visits.
i want to write a query that can calculate the top 10 rooms that are related
to any given room. i was thinking of firstly making a function that counts
how many users visited both room A and room B, and then running this
function on A and all other rooms, and order by the result. i keep getting
weird errors when doing that. please elaborate.Hi

Please post DDL (Create table statements you can use the generate SQL script
option in EM), example data (as insert statements), expected output and your
current queries. That will remove any ambiguity

It is not clear how you relate a users movement from one room to another,
what if the user has two browsers or shortcuts to specific rooms?

John

"Uri Lazar" <arielazar@.bezeqint.net> wrote in message
news:3f89b64e@.news.bezeqint.net...
> hi, im working on this for a long time. i'm using MSsql-server2000
> i have a table [visits] that records users visits to rooms. the columns
are
> room_id, user_id, visits.
> i want to write a query that can calculate the top 10 rooms that are
related
> to any given room. i was thinking of firstly making a function that counts
> how many users visited both room A and room B, and then running this
> function on A and all other rooms, and order by the result. i keep getting
> weird errors when doing that. please elaborate.|||Without DDL and example data I'm not sure I've fully understood your
requirement. Here's some assumed DDL and sample data:

CREATE TABLE RoomVisits (roomid INTEGER NOT NULL /* REFERENCES Rooms
(roomid) */, userid INTEGER NOT NULL /* REFERENCES Users (userid) */, visits
INTEGER NOT NULL CHECK (visits>0), PRIMARY KEY (roomid, userid))

INSERT INTO RoomVisits VALUES (1,100,1)
INSERT INTO RoomVisits VALUES (2,100,1)
INSERT INTO RoomVisits VALUES (3,100,4)
INSERT INTO RoomVisits VALUES (4,100,2)
INSERT INTO RoomVisits VALUES (1,222,2)
INSERT INTO RoomVisits VALUES (2,222,4)

Apparently for each room "A" you want the top 10 related rooms "B", ordered
by total number of visits to B. Rooms are deemed related if any user has
visited both - is that correct? If so, it seems a slightly artificial
requirement. Surely by that definition if users are making tours of rooms
then every room will inevitably become related to every other, unless there
are many more rooms than users.

Anyway, here's the query. First create a view which lists each related A-B
combination and the corresponding total number of visits to B.

CREATE VIEW Related_Room_Visits (room_A, room_B, visits_to_B)
AS
SELECT A.roomid, B.roomid, MAX(C.tot_visits)
FROM RoomVisits AS A
JOIN RoomVisits AS B
ON A.userid = B.userid AND A.roomid <> B.roomid
JOIN
(SELECT roomid, SUM(visits) AS tot_visits
FROM RoomVisits
GROUP BY roomid) AS C
ON B.roomid = C.roomid
GROUP BY A.roomid, B.roomid

Now display just the Top N for each room A. For my example data I've just
specified TOP 2 but you can change this as required:

SELECT R1.room_A, R1.room_B, R1.visits_to_B
FROM Related_Room_Visits AS R1
JOIN Related_Room_Visits AS R2
ON R1.room_A=R2.room_A AND R1.visits_to_B <= R2.visits_to_B
GROUP BY R1.room_A, R1.room_B, R1.visits_to_B
HAVING COUNT(*) <= 2 /* Top 2 for each Room_A */
ORDER BY R1.room_A, R1.room_B, R1.visits_to_b DESC

If this doesn't help then please post DDL, post some sample data as INSERT
statements and give an example of your required result.

--
David Portas
----
Please reply only to the newsgroup
--

Reinstalling of Sample database( AdventureWorks - OLTP) in SQL 2005

Hi All,
I was successfully installing sample database AdventureWork on my server the
first time, But realized that I didn't install with all the features with it
.
I delete the DB and try to reinstall it with all the features. it goes
through all the steps but DB don't install or attach. There isn't any error
messages.
I'm trying to install it so that I can use or test the Partition script that
come with it (PartitionAW.sql). Since I'm trying to partition my database.
How do I fix this problem or resolve it?
Thanks for your help.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1I was able to reinstall Adventureworks Sample database using instawdb.sql bu
t
can't find PartitionAW.sql. The version of Sql2005 that I have is a
developer's version. I'm wandering if it doesn't come with the partitioning
script.
Does anyone knows how I can get this script to run or test it in Sql2005.
Thanks.
Naana wrote:
>Hi All,
>I was successfully installing sample database AdventureWork on my server th
e
>first time, But realized that I didn't install with all the features with i
t.
>I delete the DB and try to reinstall it with all the features. it goes
>through all the steps but DB don't install or attach. There isn't any error
>messages.
>I'm trying to install it so that I can use or test the Partition script tha
t
>come with it (PartitionAW.sql). Since I'm trying to partition my database.
>How do I fix this problem or resolve it?
>Thanks for your help.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1

Reinstalling MS DTC

Here I go for last time, I promise.
I uninstalled MS DTC (in an stupid attemp to avoid a distributed transaction
to start), by doing :
net stop MSDTC
MSDTC - uninstall
After some charitative aim told me what I wanted to do is not possible, I
decided to reinstall :
MSDTC -install
net start MSDTC
I can see the "Distributed Transaction Coordinator" item, in the service
manager, it is running for sure.
Also, the Services console shows it as "started".
But I still getting the message MSDTC on server 'myserver' is unavailable'.
I've repeated the operation a couple of times, to be sure (deinstall and
reinstall) and still not working.
Actually, I recall yesterday I was able to stop and restart MSDTC without
problem, several times, but I was just doing that : stop & restart.
Today it happens that I "uninstall" it and then it is not like before any
more.
I'm using an account with Administrator privilegies (Windows 2000 Advanced
Server / SQL Server 2000 Enterprise)
Is there something else I need to do to ? should I need to restart the SQL
Server ? SQL Agent ?
Any help is greatly appreciated.
Regards,
Refer to the following MSKB article:
How To Reinstall MS DTC for a Nonclustered Windows 2000 Server
http://support.microsoft.com/default...b;EN-US;279786
Michael D. Long
Microsoft MVP - Windows SDK
"Craig Kenisston" <craigkenisston@.hotmail.com> wrote in message
news:%23c9KX9IhEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Here I go for last time, I promise.
> I uninstalled MS DTC (in an stupid attemp to avoid a distributed
transaction
> to start), by doing :
> net stop MSDTC
> MSDTC - uninstall
>
> After some charitative aim told me what I wanted to do is not possible, I
> decided to reinstall :
> MSDTC -install
> net start MSDTC
> I can see the "Distributed Transaction Coordinator" item, in the service
> manager, it is running for sure.
> Also, the Services console shows it as "started".
> But I still getting the message MSDTC on server 'myserver' is
unavailable'.
> I've repeated the operation a couple of times, to be sure (deinstall and
> reinstall) and still not working.
> Actually, I recall yesterday I was able to stop and restart MSDTC without
> problem, several times, but I was just doing that : stop & restart.
> Today it happens that I "uninstall" it and then it is not like before any
> more.
> I'm using an account with Administrator privilegies (Windows 2000 Advanced
> Server / SQL Server 2000 Enterprise)
> Is there something else I need to do to ? should I need to restart the SQL
> Server ? SQL Agent ?
> Any help is greatly appreciated.
>
> Regards,
>
sql

Reinstalling MS DTC

Here I go for last time, I promise.
I uninstalled MS DTC (in an stupid attemp to avoid a distributed transaction
to start), by doing :
net stop MSDTC
MSDTC - uninstall
After some charitative aim told me what I wanted to do is not possible, I
decided to reinstall :
MSDTC -install
net start MSDTC
I can see the "Distributed Transaction Coordinator" item, in the service
manager, it is running for sure.
Also, the Services console shows it as "started".
But I still getting the message MSDTC on server 'myserver' is unavailable'.
I've repeated the operation a couple of times, to be sure (deinstall and
reinstall) and still not working.
Actually, I recall yesterday I was able to stop and restart MSDTC without
problem, several times, but I was just doing that : stop & restart.
Today it happens that I "uninstall" it and then it is not like before any
more.
I'm using an account with Administrator privilegies (Windows 2000 Advanced
Server / SQL Server 2000 Enterprise)
Is there something else I need to do to ? should I need to restart the SQL
Server ? SQL Agent ?
Any help is greatly appreciated.
Regards,
If you remove and then re-install MSDTC, you may need to restart SQL Server
for it to be able to use it. MSDTC has to be running when SQL Server starts
or it cannot use it. I am not sure this will resolve the problem, but I
would stop MSDTC and SQL Server. Then start MSDTC and verify that it is
running. Then start SQL Server.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Refer to the following MSKB article:
How To Reinstall MS DTC for a Nonclustered Windows 2000 Server
http://support.microsoft.com/default...b;EN-US;279786
Michael D. Long
Microsoft MVP - Windows SDK
"Craig Kenisston" <craigkenisston@.hotmail.com> wrote in message
news:%23c9KX9IhEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Here I go for last time, I promise.
> I uninstalled MS DTC (in an stupid attemp to avoid a distributed
transaction
> to start), by doing :
> net stop MSDTC
> MSDTC - uninstall
>
> After some charitative aim told me what I wanted to do is not possible, I
> decided to reinstall :
> MSDTC -install
> net start MSDTC
> I can see the "Distributed Transaction Coordinator" item, in the service
> manager, it is running for sure.
> Also, the Services console shows it as "started".
> But I still getting the message MSDTC on server 'myserver' is
unavailable'.
> I've repeated the operation a couple of times, to be sure (deinstall and
> reinstall) and still not working.
> Actually, I recall yesterday I was able to stop and restart MSDTC without
> problem, several times, but I was just doing that : stop & restart.
> Today it happens that I "uninstall" it and then it is not like before any
> more.
> I'm using an account with Administrator privilegies (Windows 2000 Advanced
> Server / SQL Server 2000 Enterprise)
> Is there something else I need to do to ? should I need to restart the SQL
> Server ? SQL Agent ?
> Any help is greatly appreciated.
>
> Regards,
>

Reinstalling MS DTC

Here I go for last time, I promise.
I uninstalled MS DTC (in an stupid attemp to avoid a distributed transaction
to start), by doing :
net stop MSDTC
MSDTC - uninstall
After some charitative aim told me what I wanted to do is not possible, I
decided to reinstall :
MSDTC -install
net start MSDTC
I can see the "Distributed Transaction Coordinator" item, in the service
manager, it is running for sure.
Also, the Services console shows it as "started".
But I still getting the message MSDTC on server 'myserver' is unavailable'.
I've repeated the operation a couple of times, to be sure (deinstall and
reinstall) and still not working.
Actually, I recall yesterday I was able to stop and restart MSDTC without
problem, several times, but I was just doing that : stop & restart.
Today it happens that I "uninstall" it and then it is not like before any
more.
I'm using an account with Administrator privilegies (Windows 2000 Advanced
Server / SQL Server 2000 Enterprise)
Is there something else I need to do to ? should I need to restart the SQL
Server ? SQL Agent ?
Any help is greatly appreciated.
Regards,Refer to the following MSKB article:
How To Reinstall MS DTC for a Nonclustered Windows 2000 Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;279786
Michael D. Long
Microsoft MVP - Windows SDK
"Craig Kenisston" <craigkenisston@.hotmail.com> wrote in message
news:%23c9KX9IhEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Here I go for last time, I promise.
> I uninstalled MS DTC (in an stupid attemp to avoid a distributed
transaction
> to start), by doing :
> net stop MSDTC
> MSDTC - uninstall
>
> After some charitative aim told me what I wanted to do is not possible, I
> decided to reinstall :
> MSDTC -install
> net start MSDTC
> I can see the "Distributed Transaction Coordinator" item, in the service
> manager, it is running for sure.
> Also, the Services console shows it as "started".
> But I still getting the message MSDTC on server 'myserver' is
unavailable'.
> I've repeated the operation a couple of times, to be sure (deinstall and
> reinstall) and still not working.
> Actually, I recall yesterday I was able to stop and restart MSDTC without
> problem, several times, but I was just doing that : stop & restart.
> Today it happens that I "uninstall" it and then it is not like before any
> more.
> I'm using an account with Administrator privilegies (Windows 2000 Advanced
> Server / SQL Server 2000 Enterprise)
> Is there something else I need to do to ? should I need to restart the SQL
> Server ? SQL Agent ?
> Any help is greatly appreciated.
>
> Regards,
>

Reinstalling MS DTC

Here I go for last time, I promise.
I uninstalled MS DTC (in an stupid attemp to avoid a distributed transaction
to start), by doing :
net stop MSDTC
MSDTC - uninstall
After some charitative aim told me what I wanted to do is not possible, I
decided to reinstall :
MSDTC -install
net start MSDTC
I can see the "Distributed Transaction Coordinator" item, in the service
manager, it is running for sure.
Also, the Services console shows it as "started".
But I still getting the message MSDTC on server 'myserver' is unavailable'.
I've repeated the operation a couple of times, to be sure (deinstall and
reinstall) and still not working.
Actually, I recall yesterday I was able to stop and restart MSDTC without
problem, several times, but I was just doing that : stop & restart.
Today it happens that I "uninstall" it and then it is not like before any
more.
I'm using an account with Administrator privilegies (Windows 2000 Advanced
Server / SQL Server 2000 Enterprise)
Is there something else I need to do to ? should I need to restart the SQL
Server ? SQL Agent ?
Any help is greatly appreciated.
Regards,If you remove and then re-install MSDTC, you may need to restart SQL Server
for it to be able to use it. MSDTC has to be running when SQL Server starts
or it cannot use it. I am not sure this will resolve the problem, but I
would stop MSDTC and SQL Server. Then start MSDTC and verify that it is
running. Then start SQL Server.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Refer to the following MSKB article:
How To Reinstall MS DTC for a Nonclustered Windows 2000 Server
http://support.microsoft.com/defaul...kb;EN-US;279786
Michael D. Long
Microsoft MVP - Windows SDK
"Craig Kenisston" <craigkenisston@.hotmail.com> wrote in message
news:%23c9KX9IhEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Here I go for last time, I promise.
> I uninstalled MS DTC (in an stupid attemp to avoid a distributed
transaction
> to start), by doing :
> net stop MSDTC
> MSDTC - uninstall
>
> After some charitative aim told me what I wanted to do is not possible, I
> decided to reinstall :
> MSDTC -install
> net start MSDTC
> I can see the "Distributed Transaction Coordinator" item, in the service
> manager, it is running for sure.
> Also, the Services console shows it as "started".
> But I still getting the message MSDTC on server 'myserver' is
unavailable'.
> I've repeated the operation a couple of times, to be sure (deinstall and
> reinstall) and still not working.
> Actually, I recall yesterday I was able to stop and restart MSDTC without
> problem, several times, but I was just doing that : stop & restart.
> Today it happens that I "uninstall" it and then it is not like before any
> more.
> I'm using an account with Administrator privilegies (Windows 2000 Advanced
> Server / SQL Server 2000 Enterprise)
> Is there something else I need to do to ? should I need to restart the SQL
> Server ? SQL Agent ?
> Any help is greatly appreciated.
>
> Regards,
>

Reinstalling MS DTC

Here I go for last time, I promise.
I uninstalled MS DTC (in an stupid attemp to avoid a distributed transaction
to start), by doing :
net stop MSDTC
MSDTC - uninstall
After some charitative aim told me what I wanted to do is not possible, I
decided to reinstall :
MSDTC -install
net start MSDTC
I can see the "Distributed Transaction Coordinator" item, in the service
manager, it is running for sure.
Also, the Services console shows it as "started".
But I still getting the message MSDTC on server 'myserver' is unavailable'.
I've repeated the operation a couple of times, to be sure (deinstall and
reinstall) and still not working.
Actually, I recall yesterday I was able to stop and restart MSDTC without
problem, several times, but I was just doing that : stop & restart.
Today it happens that I "uninstall" it and then it is not like before any
more.
I'm using an account with Administrator privilegies (Windows 2000 Advanced
Server / SQL Server 2000 Enterprise)
Is there something else I need to do to ? should I need to restart the SQL
Server ? SQL Agent ?
Any help is greatly appreciated.
Regards,Refer to the following MSKB article:
How To Reinstall MS DTC for a Nonclustered Windows 2000 Server
http://support.microsoft.com/defaul...kb;EN-US;279786
Michael D. Long
Microsoft MVP - Windows SDK
"Craig Kenisston" <craigkenisston@.hotmail.com> wrote in message
news:%23c9KX9IhEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Here I go for last time, I promise.
> I uninstalled MS DTC (in an stupid attemp to avoid a distributed
transaction
> to start), by doing :
> net stop MSDTC
> MSDTC - uninstall
>
> After some charitative aim told me what I wanted to do is not possible, I
> decided to reinstall :
> MSDTC -install
> net start MSDTC
> I can see the "Distributed Transaction Coordinator" item, in the service
> manager, it is running for sure.
> Also, the Services console shows it as "started".
> But I still getting the message MSDTC on server 'myserver' is
unavailable'.
> I've repeated the operation a couple of times, to be sure (deinstall and
> reinstall) and still not working.
> Actually, I recall yesterday I was able to stop and restart MSDTC without
> problem, several times, but I was just doing that : stop & restart.
> Today it happens that I "uninstall" it and then it is not like before any
> more.
> I'm using an account with Administrator privilegies (Windows 2000 Advanced
> Server / SQL Server 2000 Enterprise)
> Is there something else I need to do to ? should I need to restart the SQL
> Server ? SQL Agent ?
> Any help is greatly appreciated.
>
> Regards,
>

Re-Installation of SQLExpress on XP SP2 Chinese Tra Version

When I installed SQL Express first time a few days ago, it work ok. But, after removing all sql server componets through 'control panel'->'add or remove programs,' something seems wrong in the registry because the re-installation of SQLExpress will never success with an error

-

- Performance Monitor Counter Requirement (Error)
Messages
Performance Monitor Counter Requirement
The System Configuration Check for Performance Monitor counter registry value has failed. For details, see How to: Increment the Counter Registry Key for Setup in SQL Server 2005, in the readme file or in SQL Server Books Online.

in setup configuration checker. I follow the instructions in help page and try to set the values "Last Counter" and "Last Help" in '[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib]' as same as the values "Counter" and "Help" in '[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib]'.........but the data type are dword and string resp. So, setup checker will think the values are different and no way to reinstall the SQLExpress.

Can anyone tell me how to fix it or just let me know SQLExpress not for Chinese XP SP2?

Thank you

Hi Andrew,

I've looked over the instructions that you're reading and they are a bit difficult to follow, but they do work once you figure it out. The issue is that you need to find the maximum value for the Counter and Help keys, but because of the data type of the key, the maximum value is not necesarily the last one in the list because they are not sorted numerically. Try to follow the instructions again, but this time when you're looking up the values, be sure to examine all the numbers to get the highest one. Remember they are sorted as strings, so the value list may look like...

1, 10, 11, 12, 13, 14, 2, 3, 4, 5

In this case the highest number if 14, which is in the middle of the list, not the 5 at the end. The first time I went though this myself I automactically picked the last number in the value list assuming it was numerically sorted; I was wrong. Hopefully this will solve the issue for you.

Mike

|||Thanks for your explanation and the problem is solved. sql

Reinstall SQL Server 2005 Problem (cannot connect to <server>)

I had installed SQL Server 2005 for the first time in order to study for the SQL SERVER 2005 Implementation and Maintenance exam. Everything was going ok until I got to the section on Partitions. When I attempted to follow the lessons on partitions, I received an error indicating that the Enterprise Edition was needed for Partitions.

I uninstalled SQL Server and re-installed the Enterprise Edition (MSDN disk). When I re-installed, I was not promopted for a new/default instance name). The old instance that had used for the original installation is presented when I attempt to "Connect to Server." When I select that instance and click Connect, I receive the "Cannot connect to <server name>" error message.

How do I re-establish that instance name or create another default instance?

Regards - Tony

Would you check Control Panel -> Add/Remove Programs? If there is still SQL Server 2005 entry, please choose it and click change, then click Report button to get all information about SQL Server 2005. The report will tell you which components of SQL Server 2005 are still existing on your machine.

Based on your description, maybe the previous SQL Server 2005 has not been uninstalled successfully.

If you do not mind, you can type in a new instance name to install another instance as multiple instacnes are supported provided that you have enough resourses such as free disk space.

re-indexing job takes more time

We have a re-indexing all DBs schedule job in our SQL 2000 box,
normally it took 7 hours to complete but all of the sudden now it
takes more than 20 hours.
What do you think it cause this problem? We have no clue."Frank" <soal6570@.yahoo.com> wrote in message
news:42601b2.0405052143.66cdbe69@.posting.google.co m...
> We have a re-indexing all DBs schedule job in our SQL 2000 box,
> normally it took 7 hours to complete but all of the sudden now it
> takes more than 20 hours.
> What do you think it cause this problem? We have no clue.

Have you checked general CPU and I/O activity on the server, using Perfmon?
There may be another process which is taking up resources, or perhaps it
could be a symptom of Slammer infection, if the MSSQL service has a very
high CPU load. You might also want to use Profiler to check if there's any
unusual activity within MSSQL itself, which might be creating problems.

Simon|||It could also be caused by other activity blocking the rebuilds, or badly
fragmented freespace if the indexes have grown.

On a seperate note, here's a link to a whitepaper you may want to read to
determine if reindexing everything is the right thing to do for your
particular situation.

http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx

Regards

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

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

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:409a67f2$1_1@.news.bluewin.ch...
> "Frank" <soal6570@.yahoo.com> wrote in message
> news:42601b2.0405052143.66cdbe69@.posting.google.co m...
> > We have a re-indexing all DBs schedule job in our SQL 2000 box,
> > normally it took 7 hours to complete but all of the sudden now it
> > takes more than 20 hours.
> > What do you think it cause this problem? We have no clue.
> Have you checked general CPU and I/O activity on the server, using
Perfmon?
> There may be another process which is taking up resources, or perhaps it
> could be a symptom of Slammer infection, if the MSSQL service has a very
> high CPU load. You might also want to use Profiler to check if there's any
> unusual activity within MSSQL itself, which might be creating problems.
> Simon

Registry entries for SQL Server/MSDE

Where can I find a listing of all the registry entries made at install time - or, better yet - what entries I need to elminate on uninstall? I've installed and uninstalled MSDE a number of times on my development machine, and now I can't get MSDE 2000 Rel A to install at all. I think there is one or more key blocking the installer. I appreciate your input!HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer