Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

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.
Avatar of yo_bee
yo_bee
Flag of United States of America image

I am looking at both statements and it looks like there an additional join in your view
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

Avatar of bfuchs

ASKER

@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
try creating the view in PlacementNP DB and see if you can access the same from Access.
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.
Avatar of bfuchs

ASKER

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
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.
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.
Avatar of bfuchs

ASKER

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
Please add distinct in the SQL Server Query , looks like you have duplicate rows.
Avatar of bfuchs

ASKER

Hi,
Okay, but that would not solve the issue as both are opening the same view.

Thanks,
Ben
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.
Avatar of bfuchs

ASKER

Hi Vitor,

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

Thanks,
Ben
Avatar of bfuchs

ASKER

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
as seen in attached above, there are a few users besides mine.
Yes but aren't they all using the same MS Access file?
Avatar of bfuchs

ASKER

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
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

Avatar of bfuchs

ASKER

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
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.
Avatar of bfuchs

ASKER

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
SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of bfuchs

ASKER

@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
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.
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.
Avatar of bfuchs

ASKER

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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

Thank you experts!