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

Hi and thanks,

How do you create a view???

I have this view called "Claims" it works in one Database, but not in another.

When I "Select the first 1000 rows" it works fine in one database.

BUT
when I "Select the first 1000 rows" in another database I get:
Msg 208, Level 16, State 1, Procedure CLAIM, Line 1
Invalid object name 'CLAIM_2013_07'.
Msg 4413, Level 16, State 1, Line 57
Could not use view or function 'OPID_POSTPAY_WELLPOINT_CENTRAL_EDWARD.dbo.CLAIM' because of binding errors.

I am guessing that the object is located in one database but not the other...

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

Thanks
Amour22015Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Ah, so the working view uses table CLAIM_2012_06, which exists.

The non-working view uses table CLAIM_2013_07, which does not exist, and therefore can't be read.

Since those views are specific to the databases they are in, if you need to use a view from a different db than the one you are in, you must qualify that view with the db name; that is, the code below will (should) work:

USE [EDWARD]
SELECT *
FROM [PROCLAIM]..CLAIM
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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.
0
 
plusone3055Commented:
rats too slow
untitled.png
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Amour22015Author Commented:
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
0
 
Scott PletcherSenior DBACommented:
>> 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
0
 
Scott PletcherSenior DBACommented:
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.
0
 
Amour22015Author Commented:
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
0
 
Scott PletcherSenior DBACommented:
>> 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]%'
0
 
Amour22015Author Commented:
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
0
 
Amour22015Author Commented:
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
0
 
Amour22015Author Commented:
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
0
 
Scott PletcherSenior DBACommented:
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.
0
 
Amour22015Author Commented:
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
0
 
Amour22015Author Commented:
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
0
 
Scott PletcherSenior DBACommented:
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
0
 
Amour22015Author Commented:
Great thanks...
0
 
Amour22015Author Commented:
Great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.