SQL Works As Query But Not View (1 Viewer)

Kheribus · Sep 17, 2019

Hello all,

The following SQL statement works in SQL Server Management Studio as a query on my server:

Code:

select p.stateID, p.personID, hm.memberID, hm.startDate as householdMemberStartDate, hm.endDate as householdMemberEndDate, hm.secondary as householdMemberSecondary, hl.locationID, hl.startDate as householdLocationStartDate, hl.endDate as householdLocationEndDate, hl.secondary as householdLocationSecondary, hl.mailing as householdLocationMailing, hl.physical as householdLocationPhysical, a.addressID, a.number, a.street, a.tag, a.prefix, a.dir, a.apt, a.city, a.state, a.zip, a.postOfficeBox
from campus.Person p
inner join campus.Enrollment e on p.personID=e.personID
left join campus.HouseholdMember hm on p.personID=hm.personID
left join campus.Household hh on hm.householdID=hh.householdID
left join campus.HouseholdLocation hl on hm.householdID=hl.householdID
left join campus.Address a on hl.addressID=a.addressID
where e.calendarID=5160

However, when I try to save it as a view, I get the following error:

"Object reference not set to an instance of an object."

Does anyone know what I could be doing wrong?

Thanks!
Kher

Kheribus · Sep 17, 2019

I understand that it may be necessary to share my underlying table structure - but the fact that it executes as a query leads me to believe it's not a systemic issue in the query, but just that i'm missing some important detail.

pbaldy · Sep 17, 2019

I'm curious about the syntax, though it may just be my ignorance. What is "Campus"? If it's the name of the database, I'd expect Campus.dbo.TableName. I get an error with just database.table, even in just an SSMS query.

Kheribus · Sep 18, 2019

pbaldy said:
I'm curious about the syntax, though it may just be my ignorance. What is "Campus"? If it's the name of the database, I'd expect Campus.dbo.TableName. I get an error with just database.table, even in just an SSMS query.

Campus is a schema that is coming over from another server, so I'm prefixing the table names with that schema.

I just think it's strange this works as a query but not a view.

Galaxiom · Sep 18, 2019

Kheribus said:
Campus is a schema that is coming over from another server, so I'm prefixing the table names with that schema.

I just think it's strange this works as a query but not a view.

Your SSMS query must be connected to the other server or it wouldn't work. You are then trying to create the view in a different database on a different server.

A table reference to another server must be in the format:
[server].[database].[schema].

Moreover the servers must be linked.
This can be done in SSMS under Server Objects > Linked Servers.

Logins need to be set up on the other server or mapped across.

Then the really tricky part if you are using Windows logins is to set up for passing Kerberos authentication between the servers. It is really easy to do this badly and compromise the security of the server.

Kheribus · Sep 18, 2019

Galaxiom said:
Your SSMS query must be connected to the other server or it wouldn't work. You are then trying to create the view in a different database on a different server.

A table reference to another server must be in the format:
[server].[database].[schema].

Moreover the servers must be linked.
This can be done in SSMS under Server Objects > Linked Servers.

Logins need to be set up on the other server or mapped across.

Then the really tricky part if you are using Windows logins is to set up for passing Kerberos authentication between the servers. It is really easy to do this badly and compromise the security of the server.

I don't think so in this case. The data (sensitive) is being transferred to my server through another intermediary server. I have copies of the tables, not references to the tables on an external server.

I created the schema campus because that's how the upstream data people wanted to send it.

Galaxiom · Sep 18, 2019

Maybe the problem is with the CREATE VIEW section of the query not the AS part which you posted?

It shouldn't matter but is the view being created in the Campus schema?

sonic8 · Sep 18, 2019

Kheribus said:
However, when I try to save it as a view, I get the following error:

"Object reference not set to an instance of an object."

Does anyone know what I could be doing wrong?

Possibly nothing at all and it's just SSMS being stupid here for whatever reason.

Try:

Code:

CREATE VIEW yourViewName
AS
select p.stateID ...

If this also fails, you should at least get a more comprehensible error message.

kevlray · Sep 18, 2019

I did a quick google search on the error (I have not seen it before). It appears to be a somewhat generic error. It could be a rights issue.<br>

sonic8 · Sep 19, 2019

kevlray said:
I did a quick google search on the error (I have not seen it before). It appears to be a somewhat generic error. It could be a rights issue.

Did I miss something? On which error?

The original "Object reference not set to an instance of an object." error is pretty much meaningless and irrelevant. It's either a small bug in SSMS or there is a real error underlying with a completely different message.

Kheribus · Sep 19, 2019

sonic8 said:
Possibly nothing at all and it's just SSMS being stupid here for whatever reason.

Try:

Code:

CREATE VIEW yourViewName AS select p.stateID ...

If this also fails, you should at least get a more comprehensible error message.

So this was the correct solution. So strange, but writing Create view viewname AS <query> worked. However, when I went to the view design and simply wrote the query text in the query box, it gave me the aforementioned error.

Does anyone know why?

Anyways, thank you everyone!!!!

pbaldy · Sep 19, 2019

I don't know why but I recall years ago having a view I couldn't get to save with the design grid but finally did get created using the CREATE VIEW method. Sadly a poor memory prevents me from remembering the specifics.

CJ_London · Sep 19, 2019

with ssms you are writing code which instructs the server to do something - i.e. 'create view'

if you want to modify the view, you use 'alter view'

it is the same for stored procedures

pbaldy · Sep 19, 2019

That's one way, there's also the design grid to create/modify views.

CJ_London · Sep 19, 2019

agreed - but the OP was talking about writing the sql, not using the design grid

SQL Works As Query But Not View (1 Viewer)

Kheribus

Registered User.

Kheribus

Registered User.

pbaldy

Wino Moderator

Kheribus

Registered User.

Galaxiom

Super Moderator

Kheribus

Registered User.

Galaxiom

Super Moderator

sonic8

AWF VIP

kevlray

Registered User.

sonic8

AWF VIP

Kheribus

Registered User.

pbaldy

Wino Moderator

CJ_London

Super Moderator

pbaldy

Wino Moderator

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread