It's possible to call a view from a different SQL Server?

dear friends,

It's possible to call a view from a different SQL Server? I need to create a connection inside one view from the first server to call the second server...

Thanks

Create a linked server then use a fully qualified object name: SELECT * FROM Server2.database.dbo.view|||

I did it and the point 1. works, but in point 2. with Inner join, doesnt work:

1. (OK)

SELECT * FROM GCXNCLISM1S301.SMS_B02.dbo._CPY_OfferManager_1005

2. (NOT OK)

SELECT dbo.v_R_System.Netbios_Name0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0,

dbo.v_GS_PC_BIOS.SerialNumber0, dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, dbo.v_R_System.User_Name0

FROM GCXNCLISM1S301.SMS_B02.dbo.v_GS_COMPUTER_SYSTEM INNER JOIN

GCXNCLISM1S301.SMS_B02.dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN

GCXNCLISM1S301.SMS_B02.dbo.v_GS_X86_PC_MEMORY ON dbo.v_R_System.ResourceID = dbo.v_GS_X86_PC_MEMORY.ResourceID INNER JOIN

GCXNCLISM1S301.SMS_B02.dbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = dbo.v_GS_PC_BIOS.ResourceID

Error:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_COMPUTER_SYSTEM.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_X86_PC_MEMORY.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_PC_BIOS.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.Netbios_Name0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_COMPUTER_SYSTEM.Model0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_PC_BIOS.SerialNumber0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.User_Name0" could not be bound.

THE SINTAX IS DIFFERENT?

THANKS!!

|||

hi,

did you try this :

SELECT T2.Netbios_Name0, T1.Manufacturer0, T1.Model0,

T4.SerialNumber0, T3.TotalPhysicalMemory0, T2.User_Name0

FROM [GCXNCLISM1S301.SMS_B02].[dbo].[v_GS_COMPUTER_SYSTEM] T1 INNER JOIN

[GCXNCLISM1S301.SMS_B02].[dbo].[v_R_System] T2 ON T1.ResourceID = T2.ResourceID INNER JOIN

[GCXNCLISM1S301.SMS_B02].[dbo].[v_GS_X86_PC_MEMORY] T3 ON T2.ResourceID = T3.ResourceID INNER JOIN

[GCXNCLISM1S301.SMS_B02].[dbo].[v_GS_PC_BIOS] T4 ON T2.ResourceID = T4.ResourceID

...

|||

Dear Stephane,

Returns the folow error:

Msg 208, Level 16, State 1, Procedure TESTE2, Line 3

Invalid object name 'GCXNCLISM1S301.SMS_B02.dbo.v_GS_COMPUTER_SYSTEM'.

(1 row(s) affected)

Thanks

|||

OK,

I called the view directly to the remote server and it works. And I have other problem when I do a Inner Join with one table in my seever:

ALTER PROCEDURE [dbo].[TESTE4]

AS

SELECT Netbios_Name0 as CN,TotalPhysicalMemory0 as RAM, Model0=

CASE

WHEN TotalPhysicalMemory0 <600000 THEN Model0 + ' 512MB'

WHEN TotalPhysicalMemory0 >600000 THEN Model0 + ' 1GB'

END, SerialNumber0 as NrSerie,

TotalPhysicalMemory0 as RAM, User_Name0 as UserID, MA_MODELO_ID, MA_Nome

FROM GCXNCLISM1S301.SMS_B02.dbo.v_gestao_desktop_balcoes, ModeloPC_ALIAS

WHERE Model0 NOT LIKE '%ProLiant%'

AND Model0 =MA_Nome

Returns the error:

Msg 468, Level 16, State 9, Procedure TESTE4, Line 4

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

IF I substitute the last line of code for this:

AND Model0 COLLATE Latin1_General_CI_AS =MA_Nome COLLATE Latin1_General_CI_AS

It works, but the query dont return rows... And I know that there is values that join the both sources... :-(

Can anyone help me with COLLATE?

THANKS!!