Borge Hansen
pcs.accessd at gmail.com
Sat Feb 5 18:58:43 CST 2011
Follow up to this issue: I have these DBs: RMS_ADMIN and RMS_DATA I have created a view call vAppUsers in both RMS_ADMIN and RMS_DATA based on tables in RMS_ADMIN. The view created in RMS_DATA references the RMS_ADMIN. In RMS_DATA I can create a SP that joins the vAppUsers view; I can use the local vAppUsers created in RMS_DATA or I can use the RMS_ADMIN.dbo.vAppUsers. However, in both SPs whenever I hover over the vAppUsers object, the intellisense responds with: Invalid Object Name 'RMS_ADMIN.dbo.vAppUsers' or Invalid Object Name 'dbo.vAppUsers' (when using the local RMS_DATA.dbo.vAppUsers) and when hovering over a column: The Multi-part identifier "RMS_ADMIN.dbo.vAppUsers.User_ID" could not be bound or The Multi-part identifier "dbo.vAppUsers.User_ID" could not be bound (when using the local RMS_DATA.dbo.vAppUsers) The SPs executes OK Is it just a limitation on the part of the intellisense that I can just disregard. Or do I need to do something here? One of the SPs looks like that references the view in RMS_ADMIN looks like this: SELECT dbo.tlkpRS_CCAreas.*, dbo.tlkpAreaGroup.AreaGroupDescription, dbo. tlkpAreaGroup.DesignatedUserID, RMS_ADMIN.dbo.vAppUsers.User_LoginName FROM dbo.tlkpRS_CCAreas INNER JOIN dbo.tlkpAreaGroup ON dbo.tlkpRS_CCAreas.AreaGroup = dbo.tlkpAreaGroup.AreaGroupID INNER JOIN RMS_ADMIN.dbo.vAppUsers ON dbo.tlkpAreaGroup.DesignatedUserID = RMS_ADMIN. dbo.vAppUsers.User_ID --- regards borge On Sat, Feb 5, 2011 at 7:22 PM, Borge Hansen <pcs.accessd at gmail.com> wrote: > Yes, I am referencing the Db. > I have created viewDb1 in Db2. > The view executes ok in Db2 > When I try and use viewDb1 in a join in another select SP in Db2 I > can create and save the SP but it will not execute. It does not > recognize the viwDb1. > ?? > I will now create the ViewDb1 in Db1 and on the SP in question in Db2 > try and join up the Db1.viewDb1 ... We'll see. > Thanks for the input. > borge > > On Saturday, February 5, 2011, Paul Hartland > <paul.hartland at googlemail.com> wrote: > > Have you got an example of the view and SP, if you want to run a view or > SP > > in DB2 that uses tables/views in DB1 then you have to reference the > database > > (which I am assuming that you are doing), but if not example is below: > > > > in DB1 you have a view call it say ViewInDB1, to reference the view in > DB1 > > from DB2 you would use something like SELECT * FROM DB1.dbo.ViewInDB1 > > > > Paul > > > > On 5 February 2011 08:14, Borge Hansen <pcs.accessd at gmail.com> wrote: > > > >> Hi all, > >> I have created both a view and a SP based on tables in another Db > >> (call it Db1) on the same instance of the SQL Server (2008). I created > >> the view and SP in say Db2. A simple Select ... > >> When I want to use either the View or the SP in a join with other > >> tables in Db2 I am stuck. The SP containing the join to either view or > >> SP based on Db1 tables and saved in Db2 will save Ok but not execute. > >> Both the view and the SP that I am trying to join up will execute ok > >> when running on their own. > >> Funny thing is that in the Access FE I can create a pass through query > >> based on the view and join the pass through query to an odbc linked > >> table from Db2. > >> Any one who can shed light on this? > >> borge > >> -- > >> AccessD mailing list > >> AccessD at databaseadvisors.com > >> http://databaseadvisors.com/mailman/listinfo/accessd > >> Website: http://www.databaseadvisors.com > >> > > > > > > > > -- > > Paul Hartland > > paul.hartland at googlemail.com > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > >