newbie question

Hi,
i've just finished MOC 2779B and i would like to play with the "monster"...
at work we have 2 MS SQL server 2005 RDBMS installed on 2 PCs.
let's say that DB-PC1 is the source DB where a certain application runs
on PC1.
DB-PC2 is a test DB on PC2.
I would like to do the following things :
1- create a view on DB-PC1 to retrieve only the necessary data for DB-PC2.
2- everytime this data view is updated (deleted, inserted or updated
records) i would like to send the complete vdata view to DB-PC2 (test DB)
3- in this test DB, everytime that it receives data from DB-PC1, it
should split data records (fields, ...) into several parts and insert
them into tables availables on DB-PC2.
Like that we totally split data and risks to damage them.
I want to do that because i'm preparing a web portal on test DB-PC2
which should use a partial data information from DB-PC1.
So i do not want this web portal (in case of mistake into code) to
destroy partially DB-PC1.
so i though that for point 1 i could create a view and triggers to
detects update, inserts or delete actions.
However, how can i send this information (data view) from DB-PC1 tp DB-PC2 ?
Do you have any suggestion ?
thanks a lot,
R.A.F.You want me to send my resume' where? What your asking for is not a
small task and is probably out of scope for one posting. Try breaking
your project above down in to individual tasks, do your homework
first, and then post if you can't find the answer.
If you are the one that's going to be doing this...I would start with
Linked Servers and go from there. Also, in my opinion, wipe Views
complete from your chain of thought. Think Stored Procedures!
You have one nice project in front of you! Good Luck!
On Dec 1, 4:34 am, "R.A.F." <noem...@.nospam.com> wrote:
> Hi,
> i've just finished MOC 2779B and i would like to play with the "monster"...
> at work we have 2 MS SQL server 2005 RDBMS installed on 2 PCs.
> let's say that DB-PC1 is the source DB where a certain application runs
> on PC1.
> DB-PC2 is a test DB on PC2.
> I would like to do the following things :
> 1- create a view on DB-PC1 to retrieve only the necessary data for DB-PC2.
> 2- everytime this data view is updated (deleted, inserted or updated
> records) i would like to send the complete vdata view to DB-PC2 (test DB)
> 3- in this test DB, everytime that it receives data from DB-PC1, it
> should split data records (fields, ...) into several parts and insert
> them into tables availables on DB-PC2.
> Like that we totally split data and risks to damage them.
> I want to do that because i'm preparing a web portal on test DB-PC2
> which should use a partial data information from DB-PC1.
> So i do not want this web portal (in case of mistake into code) to
> destroy partially DB-PC1.
> so i though that for point 1 i could create a view and triggers to
> detects update, inserts or delete actions.
> However, how can i send this information (data view) from DB-PC1 tp DB-PC2 ?
> Do you have any suggestion ?
> thanks a lot,
> R.A.F.|||thanks Dave.
this is the kind of help i needed : linked servers.
how ? :-)
are there some requirements, or suggestions from MS ?
Dave wrote:
> You want me to send my resume' where? What your asking for is not a
> small task and is probably out of scope for one posting. Try breaking
> your project above down in to individual tasks, do your homework
> first, and then post if you can't find the answer.
> If you are the one that's going to be doing this...I would start with
> Linked Servers and go from there. Also, in my opinion, wipe Views
> complete from your chain of thought. Think Stored Procedures!
> You have one nice project in front of you! Good Luck!
>
>
> On Dec 1, 4:34 am, "R.A.F." <noem...@.nospam.com> wrote:
>> Hi,
>> i've just finished MOC 2779B and i would like to play with the "monster"...
>> at work we have 2 MS SQL server 2005 RDBMS installed on 2 PCs.
>> let's say that DB-PC1 is the source DB where a certain application runs
>> on PC1.
>> DB-PC2 is a test DB on PC2.
>> I would like to do the following things :
>> 1- create a view on DB-PC1 to retrieve only the necessary data for DB-PC2.
>> 2- everytime this data view is updated (deleted, inserted or updated
>> records) i would like to send the complete vdata view to DB-PC2 (test DB)
>> 3- in this test DB, everytime that it receives data from DB-PC1, it
>> should split data records (fields, ...) into several parts and insert
>> them into tables availables on DB-PC2.
>> Like that we totally split data and risks to damage them.
>> I want to do that because i'm preparing a web portal on test DB-PC2
>> which should use a partial data information from DB-PC1.
>> So i do not want this web portal (in case of mistake into code) to
>> destroy partially DB-PC1.
>> so i though that for point 1 i could create a view and triggers to
>> detects update, inserts or delete actions.
>> However, how can i send this information (data view) from DB-PC1 tp DB-PC2 ?
>> Do you have any suggestion ?
>> thanks a lot,
>> R.A.F.
>|||SQL Server 2005 Books Online (September 2007)
Linking Servers:
http://msdn2.microsoft.com/en-us/library/ms188279.aspx
--
Ekrem Önsoy
"R.A.F." <noemail@.nospam.com> wrote in message
news:OQncU$FNIHA.5360@.TK2MSFTNGP03.phx.gbl...
> thanks Dave.
> this is the kind of help i needed : linked servers.
> how ? :-)
> are there some requirements, or suggestions from MS ?
> Dave wrote:
>> You want me to send my resume' where? What your asking for is not a
>> small task and is probably out of scope for one posting. Try breaking
>> your project above down in to individual tasks, do your homework
>> first, and then post if you can't find the answer.
>> If you are the one that's going to be doing this...I would start with
>> Linked Servers and go from there. Also, in my opinion, wipe Views
>> complete from your chain of thought. Think Stored Procedures!
>> You have one nice project in front of you! Good Luck!
>>
>>
>> On Dec 1, 4:34 am, "R.A.F." <noem...@.nospam.com> wrote:
>> Hi,
>> i've just finished MOC 2779B and i would like to play with the
>> "monster"...
>> at work we have 2 MS SQL server 2005 RDBMS installed on 2 PCs.
>> let's say that DB-PC1 is the source DB where a certain application runs
>> on PC1.
>> DB-PC2 is a test DB on PC2.
>> I would like to do the following things :
>> 1- create a view on DB-PC1 to retrieve only the necessary data for
>> DB-PC2.
>> 2- everytime this data view is updated (deleted, inserted or updated
>> records) i would like to send the complete vdata view to DB-PC2 (test
>> DB)
>> 3- in this test DB, everytime that it receives data from DB-PC1, it
>> should split data records (fields, ...) into several parts and insert
>> them into tables availables on DB-PC2.
>> Like that we totally split data and risks to damage them.
>> I want to do that because i'm preparing a web portal on test DB-PC2
>> which should use a partial data information from DB-PC1.
>> So i do not want this web portal (in case of mistake into code) to
>> destroy partially DB-PC1.
>> so i though that for point 1 i could create a view and triggers to
>> detects update, inserts or delete actions.
>> However, how can i send this information (data view) from DB-PC1 tp
>> DB-PC2 ?
>> Do you have any suggestion ?
>> thanks a lot,
>> R.A.F.

newbie query help

hello all,i am developing an application which displays available pcs in our lab.all activites are stored in a sql server table.Sql server 2000

Table structure is field name datatype null values
logon varchar(6) canbe null (values = login or logoff)
username varchar(20) canbe null (eg user01,B023)
computername varchar(20) canbenull (if its in lab1 name will be lab1-01,lab1-02..,if its in lab2 name will be lab2-01,lab2-02.)
logontime datetime(8) canbenull (26/11/2002 11:17:52)

table sample is

Logon computername username logontime



Login lab1-01 x 2003-07-09 11:17:00.000

Login lab1-03 y 2003-07-09 11:19:00.000

Logoff lab1-01 x 2003-07-09 11:20:00.000

with these details is it possible to see currently how many machines r occupied in each lab.if so how?i am planning to write an asp page which will display currently available machines,so tat user can go to particular lab .i am a sql newbie .if any 1 can guide me tat wuld be helpful for me .thx in advance

-regards
aravindas far as i can see (im finding it hard to understand exactly what fields will be in your database) all you should need to extract the info is something along the lines of:

select from tableA computername
where (logon = ' ')

presuming the table is called 'tableA'

you might be better off having another field of type boolean which simply shows whether anyone is logged into the machine, maybe 0 for vacant or 1 for in-use.

hope this helps|||assume that a computer is "occupied" when the latest logon for it is "Login"

this query will list the individual machines:
select computername
, logontime as LastLoginTime
from yourtable as TT
where logontime
= ( select max(logontime)
from yourtable
where logontime = TT.logontime )
and logon = 'Login'
order
by computername
this query will count them by lab and list them in order by the fewest occupied to the most:
select left(computername,4) as Lab
, count(*) as OccupiedCount
from yourtable as TT
where logontime
= ( select max(logontime)
from yourtable
where logontime = TT.logontime )
and logon = 'Login'
group
by left(computername,4)
order
by 2
rudy|||How can you tell if they are occupied if all you have is the last logon time? You need a field that indicates their logoff time as well. With that, you can not only tell which machines are occupied (at least one associated logon has a null logoff time), but also other valuable information such as usage patterns.

blindman|||blindman, the logoffs are there, as separate rows

that's why my query checks for when the latest logon for the machine is "Login"

:)|||hello all thx for ur replies ,i didt read ur query.i created a new table with computername,status (which can be login or logoff) ,when old table gets inserted it triggers an event which sets the status of particular machine in new table .now i can query the new table to see the availability...is it a good practice? its my first sql prg .gimme some inputs

-regards
aravind|||Ahh...I see your logoff rows now. I've worked tables that way before, but I prefer storing logoffs as a separate field rather than a separate record. That way each row represents a single session and it makes calculations easier. By using a separate field you don't need the Max subquery, and finding the total amount of time for a particular user across sessions is as simple as sum(LogoffTime - LogonTime).

blindman