Getting diff results from same view.

bfuchs
bfuchs used Ask the Experts™
on
Hi Experts,

2- I have a view on my DB which consists of the following
SELECT     dbo.Initialstbl.LastName, dbo.Initialstbl.FirstName, sys.sysprocesses.blocked, sys.sysprocesses.waittype, sys.sysprocesses.waittime
FROM         sys.sysprocesses INNER JOIN
                      dbo.Initialstbl ON sys.sysprocesses.net_address = REPLACE(dbo.Initialstbl.SQLName, ':', '')

Open in new window

When I run it in SSMS I see many records, while when I link that view to my Access app I only see a few, why is that?

2- How can I have the following as a view?

use master
SELECT p.spid, p.blocked, r.blocking_session_id,I.LastName, I.FirstName, DB_NAME(p.dbid) dbname, p.loginame, p.login_time, p.last_batch, p.status, p.hostname, p.program_name, p.hostprocess, c.client_net_address, t.text, p.net_address
FROM master.sys.sysprocesses p
	INNER JOIN master.sys.dm_exec_connections c ON p.spid=c.session_id
	INNER JOIN PlacementNP.dbo.Initialstbl I ON p.net_address = REPLACE(I.SQLName, ':', '')
	LEFT JOIN master.sys.dm_exec_requests r ON p.spid=r.session_id
	CROSS APPLY master.sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t

Open in new window

Thanks in Advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
yo_beeDirector of Information Technology

Commented:
I am looking at both statements and it looks like there an additional join in your view
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Hey Ben,

>> When I run it in SSMS I see many records, while when I link that view to my Access app I only see a few, why is that?
I think when you run that from access you will have less/different processing running < master.sys.sysprocesses > thats why different data.

Pls create this view in master database.

CREATE VIEW ViewName 
AS
SELECT p.spid, p.blocked, r.blocking_session_id,I.LastName, I.FirstName, DB_NAME(p.dbid) dbname
, p.loginame, p.login_time, p.last_batch, p.status, p.hostname, p.program_name, p.hostprocess, c.client_net_address, 
t.text, p.net_address
FROM master.sys.sysprocesses p
INNER JOIN master.sys.dm_exec_connections c ON p.spid=c.session_id
INNER JOIN PlacementNP.dbo.Initialstbl I ON p.net_address = REPLACE(I.SQLName, ':', '')
LEFT JOIN master.sys.dm_exec_requests r ON p.spid=r.session_id
CROSS APPLY master.sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t

Open in new window

@Yoo,
I am looking at both statements and it looks like there an additional join in your view
Sorry for not being clear, these are two issues.
The first issue is that the same view returning diff results
The second issue is an error saving that as a view.
(I really need that SQL for the view in question, this is why I included here..)

@Pawan,
Pls create this view in master database.
If that is stored in Master then I will not be able to view it/link it to my Access app.

Thanks,
Ben
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
try creating the view in PlacementNP DB and see if you can access the same from Access.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
When I run it in SSMS I see many records, while when I link that view to my Access app I only see a few, why is that?
Can you provide both results so we can compare them?

By the way, isn't a good idea to create objects in the master database. When you patch a SQL Server instance, master database is usually updated so you can't know what impact user objects in master database can have.
Hi,

try creating the view in PlacementNP DB and see if you can access the same from Access.
This is what I currently have and the results are diff..

Can you provide both results so we can compare them?
It contains names and other personal info of users..are you looking for a particular field/s?

Thanks,
Ben
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I don't know the fields so I'm more interested in the rows. You can mask or remove sensitive data. I just want to see how it looks on both cases so I can compare them.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Multiple people are accessing master and this PlacementNP DB at the same time so you will different data....

or may be yous Db account have more security access then from the user you are running via access app.
Hi Experts,

Just attached pictures from what the data looks like, both taken at the same time, and from my understanding both are hooked up to the same DB..

Any Idea?

Thanks,
Ben
Untitled.png
Untitled.png
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please add distinct in the SQL Server Query , looks like you have duplicate rows.
Hi,
Okay, but that would not solve the issue as both are opening the same view.

Thanks,
Ben
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I think the issue is on the JOIN clause:
INNER JOIN dbo.Initialstbl ON sys.sysprocesses.net_address = REPLACE(dbo.Initialstbl.SQLName, ':', '')

Open in new window


This seems to filter the results on the processes initiated from the current machine, meaning that what you see in MS Access are the process created by MS Access and what you see in SQL Server instance are the rest of the processes.
Hi Vitor,

Sorry got distracted with something else..
Will test it on Sunday when I get back to work.

Thanks,
Ben
Hi Vitor,

This seems to filter the results on the processes initiated from the current machine
Not exactly,
as seen in attached above, there are a few users besides mine.

Regardless, what do you suggest in order to have that view accessible in Access?

Thanks,
Ben
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
as seen in attached above, there are a few users besides mine.
Yes but aren't they all using the same MS Access file?
Hi Vitor,

Each one has they own copy of the Access file, however all linked to same DB.
FYI- Attached screenshot so you can see how its linked to same db.
And always its showing same users..something strange here..

Thanks,
Ben
Untitled.png
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Yes, I think this confirms what I said before.
You can see that JOIN is filtering the users by the machine net address:
INNER JOIN dbo.Initialstbl ON sys.sysprocesses.net_address = REPLACE(dbo.Initialstbl.SQLName, ':', '')

Open in new window

Hi Vitor,

But the exact SQL was working before, since we switched servers and upgraded from 2005 to 2008 it stopped working.
What do you suggest?

Thanks,
Ben
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I'm just commenting with the information that I have. You can try to troubleshoot by removing the INNER JOIN to see if results will still different.
Hi,

1- Tried removing it and see attached error.
2- I need that join to display names of users currently locking the db.
3- As mentioned, this was working perfectly fine with previous sql.

Thanks,
Ben
Untitled.png
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
Hi Ben,

can you compare below two queries from SQL Server and from MSAccess.

SELECT p.net_address
FROM master.sys.sysprocesses p

SELECT REPLACE(I.SQLName, ':', '')  SQLName
FROM PlacementNP.dbo.Initialstbl I

Like this compare all the tables you are using...and then you will get the difference somewhere.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Tried removing it and see attached error.
I didn't say it but I was expecting you to also remove from the SELECT any field related to Initialstbl table.
@Pawan,

SELECT p.net_address
FROM master.sys.sysprocesses p

Open in new window

This returns only one in Access and many in SQL.


SELECT REPLACE(I.SQLName, ':', '')  SQLName
FROM PlacementNP.dbo.Initialstbl I

Open in new window

This one have the same qty in both.



So buttom line, what do I have to change to have my previously working SQL continue to work as expected (in Access)?

use master
SELECT p.spid, p.blocked, r.blocking_session_id,I.LastName, I.FirstName, DB_NAME(p.dbid) dbname, p.loginame, p.login_time, p.last_batch, p.status, p.hostname, p.program_name, p.hostprocess, c.client_net_address, t.text, p.net_address
FROM master.sys.sysprocesses p
	INNER JOIN master.sys.dm_exec_connections c ON p.spid=c.session_id
--	INNER JOIN PlacementNP.dbo.Initialstbl I ON p.net_address = REPLACE(I.SQLName, ':', '')
	LEFT JOIN master.sys.dm_exec_requests r ON p.spid=r.session_id
	CROSS APPLY master.sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t

Open in new window


Thanks,
Ben
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Glad you got it.

>>This returns only one in Access and many in SQL.
This was my first guess< master.sys.sysprocesses > thats why different data.

>>So buttom line, what do I have to change to have my previously working SQL continue to work as expected (in Access)?
yes please change the SQL.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
This returns only one in Access and many in SQL.
That's the expected behaviour. An user can only see his processes unless it has the VIEW SERVER STATE permission.
Hi Experts,
yes please change the SQL.
Can you please guide me to what should I change it to?
That's the expected behaviour. An user can only see his processes
Well it wasn't behaving like this in previous SQL version (2005).
So what should I do in order to have one Access user access this info?

Thanks,
Ben
IT Engineer
Distinguished Expert 2017
Commented:
Well it wasn't behaving like this in previous SQL version (2005).
That's because the user had the necessary permission. Now you need to give the same permission to the user from MS Access:
USE master
GRANT VIEW SERVER STATE  TO LoginNameHere

Open in new window

Thank you experts!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial