Create view using two databases

[Niki_S]

 CREATE VIEW [MIS].[dbo].[WH_Code]
AS
SELECT tab1.cFtyCD,tab1.cBOICWHCode,tab2.cFtyCD
FROM MAS.dbo.sglFactory tab1
    INNER JOIN MIS.dbo.vInvFinal tab2
        ON tab1.cFtyCD = tab2.cFtyCD  ;

This is my code. But this is not correct.When I executed the view they said,

'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.

What should I do for this?
Thank you

 

[Chris Miller]

I suspect this only means thee name of the view, when you're connected to MIS, you can

CREATE VIEW [dbo].[WH_Code]
AS
SELECT tab1.cFtyCD,tab1.cBOICWHCode,tab2.cFtyCD
FROM MAS.dbo.sglFactory tab1
    INNER JOIN MIS.dbo.vInvFinal tab2
        ON tab1.cFtyCD = tab2.cFtyCD  ;

Again, have you tried this within SSMS?

Chriss

 

[Niki_S]

Yes I'm doing this in SSMS. I tried what you gav me but that is not work. After I tried this,

USE MIS;
CREATE VIEW [dbo].[WH_CODE]
AS

		SELECT tab1.cFtyCD,tab1.cBOICWHCode,tab2.cFtyCD,tab2.cInvNo
FROM MAS.dbo.sglFactory tab1
    INNER JOIN vInvFinal tab2
        ON tab1.cFtyCD = tab2.cFtyCD GROUP BY tab2.cInvNo;

when I execute it says,

'CREATE VIEW' must be the first statement in a query batch.

I'm doing this in my MIS database. How can I fix this?

 

[fredericofonseca]

add a GO on a new line after the USE MIS

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Chris Miller]

Are you intentionally making mistakes?

Why do you add USE MIS; If you connect to MIS you don't need to USE MIS, it's already the default database.

Which is perhaps the easier way than to make use of GO. There's no need to set the database when it's already set.

You can also start a new query window and above it find a combo box for choosing the database. There you can manually pick MIS, even if you initially connect your SSMS to another database or only connect to the server instance and no specific database. It then may show system, master, or temp, but you can pick any database there and then don't need a USE line.

Chriss