SQL Server 2008 R2 - Table Views and how they work?

<Potentially stupid answers>

>How do you create a view???
In SSMS click on Views, then right-click:New View, and follow the prompts

>I am guessing that the object is located in one database but not the other...
Very likely yes.

>What is the problem and how do I check to see what the problem is???
In the database where this works identify whether this is a table, view, SP, or function (the name doesn't suggest which one it is), then try to locate it in the database where this does not work.

rats too slow
untitled.png

Ok,

looks like I did not include some items...

I know that there is a VIEW called "Claim" and it is located in both databases.

In the database were the VIEW works, I open the view in SQL:
SELECT TOP 1000 [CLM_KY]
      ,[FILE_ID]
      ,[CLI_PRF_KY]
      ,[CLM_NR_ID]
      ,[CLM_NR_EXT]
      ,[CLM_TYP_CD]
      ,[CLM_RCV_DT]
      ,[CLM_PRSSD_DT]
      ,[EARLY_PRSSD_DT]
      ,[FRST_DT_OF_SRVC_DT]
      ,[LAST_DT_OF_SRVC_DT]
      ,[GRP_NR]
      ,[GRP_ACCT_NR]
      ,[PRVDR_PATNT_ID]
      ,[CLM_PD_AM]
      ,[CLM_CHRG_AM]
      ,[CLM_DISC_APPLD_AM]
      ,[CLM_DSLLW_AM]
      ,[CLM_ALLOW_AM]
      ,[CLM_COPAY_AM]
      ,[CLM_DDCTBL_AM]
      ,[CLM_COINS_AM]
      ,[COB_ALLOW_AM]
      ,[COB_DSLLW_AM]
      ,[COB_PD_AM]
      ,[COB_DISC_AM]
      ,[COB_DDCTBL_AM]
      ,[COB_MBR_LIAB_AM]
      ,[TOT_PLN_LIAB_AM]
      ,[COB_TYP]
      ,[COB_INDC_FG]
      ,[COB_MTHD_CD]
      ,[COB_PRIM_CD]
      ,[CLNT_LOC_CD]
      ,[CLM_OVERR_CD]
      ,[PRVDR_TYP]
      ,[NTWRK_ID]
      ,[NTWRK_CD]
      ,[FUND_CD]
      ,[PAY_CD]
      ,[LGCY_SYS_FG]
      ,[FORM_TYP_CD]
      ,[MEDIA_TYP_CD]
      ,[DOC_TYP_CD]
      ,[CLM_SRVC_CATG]
      ,[SUBSCR_MBR_KY]
      ,[PATNT_MBR_KY]
      ,[PRVDR_GRP_KY]
      ,[MDCR_ASGNMNT_INDC]
      ,[DSPN_FG]
      ,[ADJ_NR]
      ,[GRP_CNTRCT_CD]
      ,[UNDRWT_CO_CD]
      ,[INSRT_TS]
      ,[UPD_TS]
  FROM [EDWARD].[dbo].[CLAIM]

There is no Table called "Claim"

I was told that the VIEW "Claim" was created using:
Tables called CLAIM_CCYY_MM
What the CLAIM view does is pull all of those files together into 1


I do not see:
'CLAIM_2013_07'
in either database

Yet one works and the other does not.

How do I correct the error on my post???

Thanks

>> What is the problem and how do I check to see what the problem is??? <<

Note the specific error message(s) that come out of SQL, in this case:
" Invalid object name 'CLAIM_2013_07'. "

Look for that object name in the view definition and see if it exists where the view thinks it does.

My guess is that object "CLAIM_2013_07" resides in the database where the view works and not in the db where it doesn't.  

If so, the solution is to add the db name to that object name in the view definition.  That is, where the view now contains this:
FROM CLAIM_2013_07
change it to:
FROM <db_name_with_CLAIM_2013_07>..CLAIM_2013_07

Oops, I see we cross-posted.

>> I do not see:
'CLAIM_2013_07'
in either database <<

Check in sys.objects for that name: it might be a table, view or synonym.

Ok,

It was mentioned:
Look for that object name in the view definition and see if it exists where the view thinks it does

How do I look for the "view definition"???

I should also mention that I am very new to SQL Server...

How do I check out the views "definition"???

Were is the "sys.objects"???

Thanks

>> How do I look for the "view definition"??? <<

EXEC [EDWARD]..sp_helptext CLAIM

SELECT *
FROM [EDWARD].sys.objects
WHERE
    name = 'claim' OR
    name LIKE 'CLAIM[_][12][90]%'

Ok,

1.) this was in the Edward database that has the error:
When I did this:
EXEC [EDWARD]..sp_helptext CLAIM

I got this:
create view CLAIM as  SELECT * FROM CLAIM_2013_07 union all SELECT * FROM CLAIM_SAMPLE

When I did this:
SELECT *
FROM [EDWARD].sys.objects
WHERE
    name = 'claim' OR
    name LIKE 'CLAIM[_][12][90]%'

I got this from the one that does not work:
CLAIM      1725249201      NULL      1      0      V       VIEW      2013-07-17 11:27:12.500      2013-07-17 11:27:12.500      0      0      0



2.) This is in the Proclaim database that does work:
When I did this:
EXEC [PROCLAIM]..sp_helptext CLAIM

I got this:
create view CLAIM as  SELECT * FROM CLAIM_2012_06 union all SELECT * FROM CLAIM_SAMPLE

When I did this:
SELECT *
FROM [PROCLAIM].sys.objects
WHERE
    name = 'claim' OR
    name LIKE 'CLAIM[_][12][90]%'

I got this:
CLAIM      644913369      NULL      1      0      V       VIEW      2013-06-14 00:36:38.577      2013-06-14 00:36:38.577      0      0      0
CLAIM      1839345617      NULL      15      0      V       VIEW      2012-12-13 19:54:15.187      2012-12-13 19:54:15.187      0      0      0
CLAIM_2012_06      308912172      NULL      1      0      U       USER_TABLE      2013-05-30 09:01:35.613      2013-09-05 13:10:20.830      0      0      0
CLAIM_2012_06_KY        324912229      NULL      1      308912172      PK      PRIMARY_KEY_CONSTRAINT      2013-05-30 09:01:35.617      2013-05-30 09:01:35.617      0      0      0

Thanks

Or,

maybe I should copy CLAIM_2012_06 from Edward

TO and rename:

CLAIM_2013_07

But were does this table come from???

I noticed that a co-worker mentions a SSIS package maybe it comes from there???
Gets generated by the SSIS Package???

I will have to look closely at the SSIS Package maybe there is one working for EDWARD database but not for PROCLAIM???

But for now so that I can execute the new Stored Procedure in EDWARD maybe I should just Copy/rename the table???
then I can close this posting...

Thanks

Sorry,

meant to say:
maybe I should copy CLAIM_2012_06 from PROCLAIM (the database that is working)

TO and rename:

EDWARD. CLAIM_2013_07

THANKS

That's a specific data q, not a general SQL q.  I don't know your specific data, so I have no idea if that is valid.  But rather than copy the data from one date to a different date, I'd strongly recommend instead that you just qualify the view name with the appropriate db name, as I showed in an earlier post.

Ok, how do I change the VIEW statement in "CLAIM" EDWARD or do I do that in the Newly created Stored Procedure that I am executing? and everytime there is a FROM statement within the SP I change that???

Thanks

Do I use Design for the View to change it???

I go to EDWARD
Right click the ViEW "CLAIM"
Select Design
I get a message about a UNION not able to go into the grid properly.  Click OK
Change to:
USE [EDWARD]
SELECT *
FROM [PROCLAIM]..CLAIM

Plus leave the UNION ALL Statement alone???

SAVE

Thanks

Sorry, I never use "Design" / gui for views, I always create them with a script, like this:


USE [EDWARD]
GO
ALTER VIEW CLAIM
AS
SELECT *
FROM [PROCLAIM]..CLAIM_2012_06
UNION ALL
SELECT *
FROM CLAIM_SAMPLE
GO

Great thanks...

Great