Solved

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

Posted on 2014-04-15
17
580 Views
Last Modified: 2014-04-15
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
0
Comment
Question by:Amour22015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40002126
<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
 
LVL 22

Expert Comment

by:plusone3055
ID: 40002133
rats too slow
untitled.png
0
 

Author Comment

by:Amour22015
ID: 40002189
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40002199
>> 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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40002207
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
 

Author Comment

by:Amour22015
ID: 40002234
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40002251
>> 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
 

Author Comment

by:Amour22015
ID: 40002335
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 40002399
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
 

Author Comment

by:Amour22015
ID: 40002413
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
 

Author Comment

by:Amour22015
ID: 40002420
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40002432
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
 

Author Comment

by:Amour22015
ID: 40002439
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
 

Author Comment

by:Amour22015
ID: 40002460
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40002484
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
 

Author Comment

by:Amour22015
ID: 40002524
Great thanks...
0
 

Author Closing Comment

by:Amour22015
ID: 40002531
Great
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question