bfuchs
asked on
Getting diff results from same view.
Hi Experts,
2- I have a view on my DB which consists of the following
2- How can I have the following as a view?
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, ':', '')
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
Thanks in Advance.
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.
>> 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
ASKER
@Yoo,
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,
Thanks,
Ben
I am looking at both statements and it looks like there an additional join in your viewSorry 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.
ASKER
Hi,
Thanks,
Ben
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.
or may be yous Db account have more security access then from the user you are running via access app.
ASKER
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&anchorAnswerId=42357175#a42357175
Thanks,
Ben
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&anchorAnswerId=42357175#a42357175
Thanks,
Ben
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
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.
ASKER
Hi,
Okay, but that would not solve the issue as both are opening the same view.
Thanks,
Ben
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:
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.
INNER JOIN dbo.Initialstbl ON sys.sysprocesses.net_address = REPLACE(dbo.Initialstbl.SQLName, ':', '')
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.
ASKER
Hi Vitor,
Sorry got distracted with something else..
Will test it on Sunday when I get back to work.
Thanks,
Ben
Sorry got distracted with something else..
Will test it on Sunday when I get back to work.
Thanks,
Ben
ASKER
Hi Vitor,
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
This seems to filter the results on the processes initiated from the current machineNot 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?
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
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:
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, ':', '')
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
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
@Pawan,
So buttom line, what do I have to change to have my previously working SQL continue to work as expected (in Access)?
Thanks,
Ben
SELECT p.net_address
FROM master.sys.sysprocesses p
This returns only one in Access and many in SQL.SELECT REPLACE(I.SQLName, ':', '') SQLName
FROM PlacementNP.dbo.Initialstbl I
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
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.
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.
ASKER
Hi Experts,
So what should I do in order to have one Access user access this info?
Thanks,
Ben
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 processesWell 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you experts!