I'm trying to debug whether or not a SSRS app is leaving open sql connections. I wrote a test app that uses a custom SSRS reporting object (written in VB.net) that calls reports in a loop. Each time the reporting object is destroyed and recreated.
The server is 2008 R2 and the SQL server is 2008 R2. I have Performance Monitor open on the server with the following counters added in:
ReportServer:Service - Active Connections
SQLServer:General Statistics - User Connections
SSRS.ActiveConnections is displaying 97 current connections.
SQL.UserConnections is displaying 38 current connections.
If I open Management Studio and use the Activity Monitor, selecting all databases - there are exactly 38 connections. If I run sp_who2 and remove the lines that are for the master DB and sa account, there are again 38 connections (prior 64)
I'm very confused on the different #s being reported in the counters. Can anyone shed light into this? Is the ReportServer:Service - Active Connections actual DB connections or connections to the service? Should the SQL.UserConnections include SSRS report connections?
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
Hi everyone! I need to get a list of users connected to a database. I want to implement a fuctionality in my application that allows users connected to the database to communicate. For instance one administrator in his office can request or send information to another in an office through the network.
A user executing 'select * from master..sysprocesses' does not show all connected users but only the one executing the command. How do I get users to view list of connected users so that they select the one they need to communicate with? I'm using Sql Server 2005. Thanks in advance.
I'm not sure, but I believe that you need to be a sysadmin member to view which other users are connected. Since you (probably) will not promote every user to an admin, I'd suggest rolling your own. It's quite easy to create a table with a username and a datetime of login. Insert the users' name when he/she logs in, delete it if they logout or are inactive for a specified period of time.
You can look at these commands, but I beleive you need elevated permissions to see other people on the server.
Maybe you could write a web service that connects to the database with Admin privs and returns the list of users for each request. This would allow you current user configuration no to change, just some additional functionality would be published.
I want to secure a database in SQL Server 2008. Here is the situation description:
I am using both 'windows' and 'sa' authentication. When I create a database (logged as 'windows' authenticated) I can see and change it (create tables, stored procedures) from logged as 'sa' authentication or vice versa. So, there is no difference whether I create it logged as 'windows' or 'sa'. But, what I need is to secure a particular database i.e. even if I am logged as 'windows' I want the particular database to be accessed only by providing a username and password.
Do you have any idea how to resolve this?
You didn't provided solution, that is just an article of capabilities.
I don't have to provide a solution. I'm not at work, this 'advice' is being given in my own spare time. I could be doing something productive, like the dishes, but I had chosen to answer this question. Might benefit someone, and I'm sure the dishes do not mind waiting
Kujtim Hyseni wrote:
I need short and precise guide.
You already have - it's called the documentation[^]. Right on the first lines, it states short and precise;
There are two possible modes: Windows Authentication mode and mixed mode.
Kujtim Hyseni wrote:
By the way, I don't use MySQL nor Oracle.
They were provided as alternative solutions to your problem.
I'm not going to provide any further pointers, as doing the dishes seems like a more important task suddenly.
I haven't tried this, however my first try would look like this:
SELECTDISTINCT UserID, StateCount_of_A, StateCount_of_B FROM tablename AS T1
LEFTJOIN (SELECT UserID, COUNT(*) AS StateCount_of_A FROM tablename AS TA WHERE State='A') ON TA.UserID = T1.UserID
LEFTJOIN (SELECT UserID, COUNT(*) AS StateCount_of_B FROM tablename AS TB WHERE State='B') ON TB.UserID = T1.UserID
(Actually I would try a subset of it with a single state first, then expand for the exact requirements).
The fundamental trick is to use the same table more than once, and give each "instance" a different name using the AS keyword.
And then one might have to throw in a couple of COALESCE functions to replace null counts by zeroes.
,CASE WHEN [State]='A' THEN 1 ELSE 0 END StateA
,CASE WHEN [State]='B' THEN 1 ELSE 0 END StateB
GROUP BY UserID
No, my 'base' or don't know how to call it server works fine and I am connecting to it as usual via 'Microsoft SQL Server Management Studio'. But when I enter to 'Microsoft SQL Server Management Studio' and try to register new server i.e. from the menu I choose from menu 'Register...' then appears the form 'New Server Registration' I cannot save this new server but cannot start it.
Testing the registered server failed. Verify the server name, login credntials, and database, and then click Test again.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40- Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
Yes I am using Windows authentication (in fact I can login via 'sa' also).
Right-click the icon for "Sql Management Studio", choose "Run as Administrator". That solved a similar problem I had under Sql Server 2005, should not be required for 2008 though.
Since you can connect using the 'sa', I'd assume that the configuration is correct (TCP/IP being enabled) and that you have the name of the correct instance on the server. Do you have rights to start/stop the Sql Server Service when you open the "Services" list from the Configuration Panel?
If you are doing this in the database you are going to have to write some code, you can't magic it into the multiple row. I would look at the method of inserting the original record and change or enhance that method.
Alternatively you can use an insert trigger spit, I would do this as a last resort.
Never underestimate the power of human stupidity
hiiiiiii, I have to make a lan chatting software for windows using ADO.NET & sqlserver.The criteria is that what we enter in the chatbox should update in database & the other lan user see it & also who is online on lan.plz help & provide code
Sorry but that is not how this forum works. However, if you try a search of the Articles[^] you will find lots of examples of chat programs and database usage that will help in developing your solution.
I am getting the above error on MySQL stored procedure..
here is the error followed by the code:
1064 - You have and error in your SQL syntax. check the manual that corresponds to your MySQL server version for the right syntax touse near '
INSERT INTO salary_slips (salary_slip_month, salary_slip_y'
at line 13
BEGINSET param_slip_released = FALSE;
IFEXISTS (SELECT salary_slip_id FROM salary_slips WHERE salary_slip_month = param_slip_month AND salary_slip_year = param_slip_year) THENBEGINSET param_slip_released = (SELECT salary_slip_released FROM salary_slips WHERE salary_slip_month = param_slip_month AND salary_slip_year = param_slip_year);
IF param_slip_released = FALSE THENBEGINSELECT * FROM salary_slips;
ENDENDELSEBEGININSERTINTO salary_slips (salary_slip_month, salary_slip_year) VALUES (param_slip_month, param_slip_year);
I have a list to populate. I have a query to populate it in mssql 2008. It involves three tables. If the query returns the value then I populate it and it is done. But , if the query doesnot return anything then I want to run another select statement because I do not want to leave the list view empty. I have to populate at least the names of the client. It could be something simple but I am just beginning to use mssql so I am stuck.
Select C.name as Client ,
B.Detail as Detail from tableA A
left join tableB B on B.bID = A.bID
Left join tableC C on C.clientID = B.clientID
where aID= '100';
if does not return anything,
I want to run another sql to atleast get the name of the client even if there is no related detail with that client.
second sql would be
Select C.name as Client from tableA A
Left join tableC C on C.clientID = A.bID
where aID= '100'
[P.S. the bID field of Table A could actually either be bID as is or C.clientID inserted into bID field of Table C.]
I am confident about the queries but its just about extracting the values from the result.