David_Wallis

Right...

best give you the background.... we have numerous sql servers (and I aint a dba!) we have SMS on one sql server and our asset management database on another... I want to create a view so we can report on both of these... I cant create a view on db's across servers can I??

So I set up DTS to export the relevant tables to a new Audit Db..

Can DTS be modified, to not create the table?? if so how?

also I am using the following as my view...

CREATE VIEW [Tabs_SMS]
AS
SELECT
[ATAssets].[AssetCode],
[ATAssets].[LocationCode],
[ATAssets].[Manufacturer],
[ATAssets].[Model],
[ATAssets].[Condition],
[ATAssets].[Comments],
[ATAssets].[SerialNumber],
[ATAssets].[DateOfPurchase],
[ATAssets].[WarrantyExpiryDate],
[Computer_System_DATA].[MachineID],
[Computer_System_DATA].[InstanceKey],
[Computer_System_DATA].[RevisionID],
[Computer_System_DATA].[AgentID],
[Computer_System_DATA].[TimeKey],
[Computer_System_DATA].[CurrentTimeZone0],
[Computer_System_DATA].[Description0],
[Computer_System_DATA].[Domain0],
[Computer_System_DATA].[Model0],
[Computer_System_DATA].[Name0],
[Computer_System_DATA].[Roles0],
[Computer_System_DATA].[Status0],
[Computer_System_DATA].[UserName0],
[Computer_System_DATA].[Manufacturer00],
[Computer_System_DATA].[SystemType00]
FROM [Computer_System_DATA] Inner Join
[ATAssets] On
substring(Computer_System_Data.Name0,5,(len(Comput er_System_Data.Name0)-4)) = AtAssets.AssetCode


I also want to include another table which is the location and join it with [ATAssets].[LocationCode] how do I join the asset and location and then match to the sms details??

Im sure its simple, but I have short deadlines.. and dont want to **** up what ive done allready...

Make Sense??

David

GaryK

David,

Yes you just use the format -

[databasename].[owner].[tablename]

why cant you just add the inner join to the location table after the first join? not sure what you mean by matching SMS details, need to see the schema really.

cheers

Gary

David_Wallis

eh?

GaryK

david,

perhaps you could come up with a more constrcutive reply than two letters? if you don't understand then tell me what it is, remember it's your data and you know intimately when asking for help nobody else does so saying I want to join this data with that data is no good, if you dont want the help then fine

Gary

David_Wallis

ffs... forget it.

I said eh, becuase I didnt understand a word of what you said.

Like I said, Im not a DBA, hence havent got a clue what the schema is... I do it support.. not try and help stupid managers out with audit information.

Thanks for trying anyway.

David

GaryK

Im not a DBA either!

anyways the first part regarding the view:

e.g.

CREATE VIEW AView
<<selects here>>
FROM
[database1].[dbo].[ATAssets],
[database2].[dbo].[tablein db2]

will work, the join I need more info.

Gary

David_Wallis

Sorry, now that makes sense... what Info do you need..

David

GaryK

David,

>I also want to include another table which is the location and >join it with [ATAssets].[LocationCode] how do I join the asset >and location and then match to the sms details??

you have done the first join:
FROM [Computer_System_DATA] Inner Join [ATAssets] On
substring(Computer_System_Data.Name0,5,(len(Comput er_System_Data.Name0)-4)) = AtAssets.AssetCode

can you not just now add to the end:
INNER JOIN <locationtable> on AtAssets.Assetcode =
<locationtable>.<fieldname>

Gary

David_Wallis

Cheers...

/* Dwallis 13/08/2002 */

CREATE VIEW [Tabs_SMS]
AS
SELECT
[ATAssets].[AssetCode],
ATAssets.LocationCode as AssetLocationCode,
[ATAssets].[Manufacturer],
[ATAssets].[Model],
[ATAssets].[Condition],
[ATAssets].[Comments],
[ATAssets].[SerialNumber],
[ATAssets].[DateOfPurchase],
[ATAssets].[WarrantyExpiryDate],
[TabsLocations].[LocationCode],
[TabsLocations].[Description],
[TabsLocations].[Floor],
[Computer_System_DATA].[MachineID],
[Computer_System_DATA].[InstanceKey],
[Computer_System_DATA].[RevisionID],
[Computer_System_DATA].[AgentID],
[Computer_System_DATA].[TimeKey],
[Computer_System_DATA].[CurrentTimeZone0],
[Computer_System_DATA].[Description0],
[Computer_System_DATA].[Domain0],
[Computer_System_DATA].[Model0],
[Computer_System_DATA].[Name0],
[Computer_System_DATA].[Roles0],
[Computer_System_DATA].[Status0],
[Computer_System_DATA].[UserName0],
[Computer_System_DATA].[Manufacturer00],
[Computer_System_DATA].[SystemType00]
FROM [Computer_System_DATA] Inner Join
[ATAssets] On
substring(Computer_System_Data.Name0,5,(len(Comput er_System_Data.Name0)-4)) = AtAssets.AssetCode
Inner Join [TabsLocations] on [ATAssets].[LocationCode] = [TabsLocations].[LocationCode]


Sorry its been a stressfull day.

David

GaryK

david,

yes sorry, i know the feeling....

gary