Solved

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

Posted on 2014-04-15
17
572 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
17 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
<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
Comment Utility
rats too slow
untitled.png
0
 

Author Comment

by:Amour22015
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> 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:ScottPletcher
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
>> 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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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
Comment Utility
Great thanks...
0
 

Author Closing Comment

by:Amour22015
Comment Utility
Great
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now