Getting diff results from same view.

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.
LVL 5
bfuchsAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
yo_beeDirector of Information TechnologyCommented:
I am looking at both statements and it looks like there an additional join in your view
0
 
Pawan KumarDatabase ExpertCommented:
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

0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
bfuchsAuthor Commented:
@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
0
 
Pawan KumarDatabase ExpertCommented:
try creating the view in PlacementNP DB and see if you can access the same from Access.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
bfuchsAuthor Commented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
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.
0
 
bfuchsAuthor Commented:
Hi Experts,

Currently stuck with serious issue.
Will resume as things gets resolved..
https://www.experts-exchange.com/questions/29066852/How-to-recover-deleted-folder-by-code.html?anchor=a42357175¬ificationFollowed=199861621#a42357175

Thanks,
Ben
0
 
bfuchsAuthor Commented:
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
0
 
Pawan KumarDatabase ExpertCommented:
Please add distinct in the SQL Server Query , looks like you have duplicate rows.
0
 
bfuchsAuthor Commented:
Hi,
Okay, but that would not solve the issue as both are opening the same view.

Thanks,
Ben
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
bfuchsAuthor Commented:
Hi Vitor,

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

Thanks,
Ben
0
 
bfuchsAuthor Commented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
as seen in attached above, there are a few users besides mine.
Yes but aren't they all using the same MS Access file?
0
 
bfuchsAuthor Commented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
bfuchsAuthor Commented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
bfuchsAuthor Commented:
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
0
 
Pawan KumarDatabase ExpertCommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
bfuchsAuthor Commented:
@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
0
 
Pawan KumarDatabase ExpertCommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
bfuchsAuthor Commented:
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
0
 
bfuchsAuthor Commented:
Thank you experts!
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.