SQL Server 2012 :: Query To Count How Many Sessions Are Active And Remain Active Per Hour

Jan 22, 2015

I have a table with the following columns employeeSessionID, OpDate, OpHour, sessionStartTime, sessionCloseTime. I need to see how many users remain active per hour. I can calculate how many logged in per hour, but I am stumped on how to count how many are active per hour. I have a single table that stores login data. I have created a query that pulls out the only the data needed from the table into a temp table using this query. Also note it is possible that the sessionCloseTime is null if the device has not been logged out this would need to be counted a active.

TABLE NAME #empSessionLog Contains the time stamp data OpDate, sessionStartTime and sessionCloseTime.
OpDatesessionStartTimesessionCloseTime
2015-01-202015-01-20 14:32:59.1302015-01-20 14:33:14.6299166
2015-01-202015-01-20 06:58:33.7302015-01-20 15:27:16.9133442
2015-01-202015-01-20 09:56:22.8402015-01-20 17:56:29.7555853
2015-01-202015-01-20 05:59:18.6132015-01-20 14:05:19.0426707

[code]....

can see how many sessions logged in per hour with the following statement:

SELECT
opDate,
FORMAT(DATEPART(HOUR, sessionStartTime), '00') AS opHour,
Count(*) AS Total
FROM #empSessionLog
Group BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00')
Order BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') ASCResults:
opDateopHourTotal
2015-01-20041

[code]....

Where I am stuck is how do I count the sessions that remain active per hour until the session is closed with the sessionCloseTime.

View 5 Replies


ADVERTISEMENT

Error Trying To Import MS Access 2003 Database Via SQL Server Import And Export Wizard - Too Many Sessions Already Active

Nov 29, 2006

I am trying to simplify a query given to me by one of my collegues written using the query designer of Access. Looking at the query there seem to be some syntax differences, so to see if this was the case I thought I would import the database to my SQL Server Developer edition.

I tried to start the wizard from within SQL Server Management Studio Express as shown in one of the articles on MSDN which did not work, but the manual method also suggested did work.

Trouble is that it gets most of the way through the import until it spews forth the following error messages:

- Prepare for Execute (Error)
Messages
Error 0xc0202009: {332B4EB1-AF51-4FFF-A3C9-3AEE594FCB11}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not start session. Too many sessions already active.".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Source 33 - ATable" (2065) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard).

There does not seem to be any method of specifying a number of sessions, so I don't see how to get round the problem.

Does anyone know how I can get the import to work?

View 2 Replies View Related

Active/Active SQL Server Clustering With Multiple Instances

May 12, 2008

Hi

I am newbie in SQL Clustering. I have set up a Windows Server Cluster with 2 nodes and am having the following problem with Physical Disk resource for cluster groups:

My Default Cluster Group (named Cluster Group) has IP Address, Network Name, Physical Disk and MSDTC resources. In addition to that my Default SQL Server instance resources are also in this group. I had this initial set up for Active/Passive mode.

Now I am trying to set up a SQL Cluster in Active/Active mode. For this I have to install another instance of SQL Server in the existing cluster and make a separate cluster group for its resources. I made a new cluster group (SQL Instance Group) with an IP Address and a Network Name resource for this new instance but I dont have any Physical Disk resource to allocate to it. As such while installing the SQL Server Instance I get stuck when I'm asked to select the quorum disk to be used.

Is it possible to configure two quorum disks, one for each group?
What's the concept of dedicated disks resource for each sql instance in a group? Is this same as the quorum disk? If this is not a shared disk how do I configure a dedicated disk resource for my second cluster group (SQL Instance Group)?

Anyone could please help me out with this?

View 12 Replies View Related

Active/active Sql Server Config; Shared Dbs

Nov 2, 1999

Hi,

We are trying to set up an active/active configuration of a SQL Server
cluster, and we had a few questions.

Initially, we want to have 2 Database Servers that would share the same
Database (both reading/writing to the same tables).
However, from reading the MS docs, we find out that we can have what
they call an "active/active" configuration using a Cluster but they
need to have 2 different disk sets, i.e. having 2 separate databases.
If this assumption is correct, how does the data get synchronised
between the 2 databases (that are on the 2 different disks sets)?

Can anyone help? thanks
Axel

View 1 Replies View Related

ACTIVE/ACTIVE SQL Server 2000 Cluster

Oct 12, 2002

ACTIVE/ACTIVE SQL Server 2000 Cluster

This was my plan.

Physical box - box1,box2
virtual box - virtual1

i have a active/passive cluster virtual1 (primary node box1, secondary box2)

I am creating another virtual box on box2 as primary node by name virtual2)

whenever my installation setup runs my default server name is greyed out. it always requires a instance name.

Is that the way it should work?

i can only use instance for active/active failover?

please advice

View 1 Replies View Related

Active/Active SQL Server Cluster And OLTP

Sep 27, 2005

Hello folks,

have you ever heard of an Active/Active SQL Sever 2000 Cluster deployed in a pure OLTP environment?

Some 8 years ago I have learned about a bespoke solution for the SAP ERP system (not the BW!) with DB2 Parallel Server for a huge German company. Then again, I would expect that Oracle RAC might fit into an OLTP environment, although I never heard about a real world implementation.

All this led me to believe clustering is good for failover purposes, and for decision support services, not quite for OLTP applications.

So if you see a point in Active/Active Clustering and OLTP please come forward and explain.

Cheers,

Johann

P.S: For those of you who want to know: Consultants from www.hiltes.com want us to deploy an Active/Active Cluster for their Fashion 3000 Net stock software.

View 6 Replies View Related

Active/active Sql Server Cluster

Jan 20, 2004

Hello, Can anyone please explain how the failover processes works on a sql server 2000 2 node active/active cluster.

Given the following
You have 2 node active/active cluster NodeA & NodeB

Question
1. How many SQL instances need to be installed on each node?

2. If the answer to question #1 is one instance per node, then
say if NodeA fails NodeB will take over all the resources of NodeA
including the master database, How does that work , how can once instance (in nodeB) handle two master database i.e its own master database and the one taken over from NodeA.

View 1 Replies View Related

There Is Not Enough Disk Space Error While Installing SQL In A Active/Active/Passive Cluster

Mar 5, 2008



Hi

I am having some teething problems while installing SQL on a 3 node cluster. Within the Cluster configuration I have 3 Cluster Groups with each of them having their associated disk resources. All these disk resources physically exist on a SAN.

The actual cluster is running absolutely fine and I can access all the disks from their respective owner node. The problem only starts when I start installing SQL Server 2005 on this cluster. I specify the Cluster group from the Cluster Group Selection and choose the desired partition and then the error message pops up

"There is not enough diskspace on the destination disk for the current SQL Server data files. To proceed, free up disk space to make room for data files, or install the data files to a different drive"

But the disk I am trying to install it on is 264Gb and none of it is used. I have also tried to change it to a different disk within the same Cluster group but to no avail. I have even tried to install it in a different cluster group all together but I get the same error message.

I have googled around havent found anything so far. The disks have got full permissions for the account I am installing SQL with.

Any help will be much appreciated.


Regards

Adnan

View 5 Replies View Related

Recovery :: AlwaysOn Failover Cluster And Active Sessions Failover Scenario

Oct 29, 2015

1. In alwaysON fail over cluster, Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.

2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.

3. After fail over done to 2nd secondary node what mode in production(readonly or read write).

4. How to rollback to production primary ,will change data in secondary will get updated in primary.

View 3 Replies View Related

Are There Any Issues With An Active/Active Cluster With A Remote Mirror For DRP

Nov 30, 2006

Hi folks,

We are going thru the process of scoping an active/active cluster at one site.
I was wondering whether there will be any issues with mirroring (DB by DB) off the cluster into non clustered server at an alternate DRP site.

Regards, Brian
Sutherland

View 1 Replies View Related

Install Active Active Sql 2005 2 Node Cluster

Mar 12, 2008




Hi all, My aim to install active/active 2 node SQL 2005 cluster. I have installed sql cluster on one mode, which automatically installed on node 2.

it works great. However When i tried to install second virtual server, it is not allowing me to install.

it says already install, can anyone tell me how to install active active cluster

View 1 Replies View Related

MSDTC In Active/Active MSCS Cluster

Jun 29, 2004

How do I install MSDTC in an active/active MSCS cluster environment?
If I run comclust.exe, I can only activate MSDTC on one of the nodes at a time.

I need DTC on both nodes.

(Opsys is W2K with SQL 2000)


//Mange :)

View 2 Replies View Related

SQL 2000 Active/active Cluster And Different IP Adresses

Mar 22, 2007

We run several SQL 2000 SP4 instances on IA64 active/active cluster. The OS we run with is Windows Server 2003 SP1. We have different network cards : 2 network cards teamed for production purposes in domain X and 1 network card dedicated for problem solving in domain Y.
First we configured the cluster with only the 2 teamed network cards for production purposes in domain X. Later we introduced the second network card for problem solving in domain Y.
Everything looks fine. The (virtual) SQL instance listens on two different IP adresses on TCP port 1034. If we try to make connection via isql, EM or Query analyzer than we can directly contact the SQL instance from a workstation/server within domain X but this doesn't work form a workstation/server within domain Y. However if we use the specific TCP port in the connection in domain Y the connection is setup. We wish however not to use explicit TCP ports in setting up connections.
Has anyone experienced the same problem before or has anyone an idea how to solve the problem?

View 1 Replies View Related

Applying SP To 2005 Active/active Cluster

Jun 8, 2007

I have a 2005 active/active cluster and want to apply SQL server 2005 SP2 to both node.
I know that for active/passive , the sp can simple be installed on the active node(instance) and everything will be replicated to the other node.
bear in mind what I have SSIS and SSAS and SSRS running on the active/active cluster.

what is the rigth method for applying the SP to activeactive cluster?

Thanks

View 1 Replies View Related

SQL Server Active/Active, Possible?

Aug 2, 2006

Dear all,

I am a newbies in SQL server. One of my client what to using Clustering and SAN Feature to make his SQL server have high avaliabilty. However, since they want to utilize the SQL server, they would like to active/active cluster with network load balancing. However, questions comes:

1. Can I mount a same database in a Active/Active Cluster environment?

2. If I can do that, how will the SQL server handle those instance/how can I set it up?

3. When there have two users using difference node SQL server to insert data, what will happen?

Please help.

Thanks and Regards

Jacky

View 6 Replies View Related

Active / Active Cluster Win2K

Mar 5, 2001

Hi
While configuring an active / active cluster, do I need to run the SQL setup on both nodes?
The SQL2K setup installs binaries on localdisks of both nodes - hence the question.

thanks
Liju

View 3 Replies View Related

Active/Active Clustering Config

Feb 22, 2000

I have setup an active/active clustering environment for SQL Server, however it is 2 seperate virtual servers. How can I set them up to exist as one virtual server containing both active installations? Can this be done? I have two compaq 8500 w/8 processors each. I need to be able to cluster these configurations to take advantage of all 16 processors in one virtual SQL Server. Can it be done?

If you can answer these questions, you will have my undying gratitude.

Thanks in advance.

View 1 Replies View Related

SQL7 In Active/active Cluster

Sep 21, 2000

Hi
I am running some tests on SQL7 in an active/active cluster and have a couple of queries
1. When I create an ODBC DSN, why is the "Use the Failover SQL Server if the primary SQL Server" checkbox disabled
2. To test, I was running SQL queries from Access over the DSN created. When the Primary Server went down, I had to reconnect to re run the query - Is this normal?
3. Can someone point me to any documentation on the above scenario that would shed some more light?

Thanks
Liju

View 5 Replies View Related

Upgrad SQL 6.5 To 7.0 In An Active/active Cluster

Oct 26, 1999

I currently have a two node HP hardware active/active cluster server. Running windows NT 4.0 Enterprise and dSQL server 6.5 sp 3. I want to upgrade the cluster to SQL 7. I would like to know if anyone ran into any problems or has sucessfully attempted this.

Also I read a few months ago about a gottcha involving NT sp4 and SQL sp5a that would prevent a node from failing over. Has this been corrected and does it affect SQL 7.

thanks

View 1 Replies View Related

Active/active SQL 2000 Cluster

Sep 27, 2002

Hi

Is there anyone who knows where I can get some information about
installing an ACTIVE/ACTIVE SQL 2000 Cluster server (MSCS).
I have no problem setting up an active/passive failover cluster.

Where can I get more information? Do I need two instances? Two databases?
What's the difference between active/active and active/passive?

Thanks!

View 2 Replies View Related

RAM Config For Active/active Sql Cluster

May 18, 2008

Hi,
I've just setup an 2 node active/active sql cluster (my first). Both servers have exactly the same specification, 16GB of RAM each and SAN attached. My question is, how do I configure the min and max memory for each sql instance. I've read some where that I need to follow the 20-40-40 rule as, 20% for the OS, 40% for the active sql instance on the node and the other 40% is for the other sql instance if it fails over. Are there any other gotcha that I need to be aware of? Can someone share some light with me in regards to this? Thanks

Ken

View 4 Replies View Related

Detect Active/Active Node

Jul 3, 2007

We have an active/active node setup with SQL 2005. Does anyone have any samples of VBS I could use to see what node is actually taking requests at a certain time? What I want to determine is what the actual active node is.

Thanks.

View 1 Replies View Related

2 Node Active Active SQL Cluster

May 18, 2006

I want to host 2 seperate SQL databases and wanted to know if it would be possible to run this in an active active cluster config which each database running on a different server.

The clustering would be there in the event of failure of one of the servers but for normal operation the two sql instances would be completely seperate.

Is this configuration possible and would this give the best performance and resiliance from a 2 server setup.

What do you think?



View 4 Replies View Related

Active/Active CLuster Can Not Add 2nd Node

Jun 3, 2007

Help, I have an active/active cluster with SQL2k5 Ent with SP2 installed on node1 that is currently running live apps, I need to add the 2nd node to have fail over functionality but if I use the Change Install from Add or Remove Programs on the primary node to add the 2nd node I get the following error.



"The File 'c:....SqlRun_SQL.ms' is not a valid installation package for the product Microsoft SQL Server 2005(SQL2K5VVS01). Try to find the installation package 'SqlRun_SQL.msi' in a folder from which you can install Microsoft SQL Server 2005(SQL2K5VVS01).



No I assume this is because node 1 has had service pack two applied. Does anyone have instructions how I can add node two without having to completely uninstall Node 1, as the doc€™s with SQL2005 SP2 say it can not be removed, a complete uninstall of 2005 must be done???

View 3 Replies View Related

Version Required For Active / Active

Sep 18, 2006

Hi,

I can't seen to find any info on the exact version of OS and SQL one needs to run active / active cluster.

I want to run 2 window server 2003 box with SQL 2005 in an active / active mode. What version of windows 2003 and SQL2005 would I need?



Thanks

Frank

View 1 Replies View Related

1 Node Of Active/Active Failed

Oct 24, 2007

Hi

I have an active/active cluster and the second node failed and could not start all the instances are intact on the first node. Now we have built the second node and joined to the cluster group as well.

I am sure we dont need a install as all the instances are there so anyone know where I need to join the SQL cluster to talk to both the nodes

Thanks in advance

Anup

View 1 Replies View Related

Active/active Vs. Active/passive

Nov 30, 2007

Anyone know of any differences in an active/active cluster and an active/passive cluster that would cause an application to work on an active/passive environment but not an active/active environment?

View 8 Replies View Related

Clustering Active/active/active

Oct 24, 2007

Hi all,

1. is it possible to have both sql server 2000- and 2005 on a 3 node active/active/active cluster
2. if not is is possible to have sql server on an active/active/active cluster

regards,
joey

View 1 Replies View Related

How Do You Determine The Active Node Inside A Stored Proc For An Active/passive 2-node Cluster?

Jan 16, 2008

I invoke xp_cmdshell proc from inside a stored procedure on a 2-node active/passive SQL 2005 SP2 Standard cluster. Depending on which server the xp_cmdshell gets executed on I need to pass different arguments in the shell command. I thought I could use host_name() function to get the runtime process server, however, I am finding that it's not behaving correctly. In one example I know my active node is server2, but the host_name() function is returning server1. The only thing that I could possible explain this is that the MSDTC cluster group is not always on the same active node as the SQL server group and in the case I am talking about the cluster groups are in this mode (differnet nodes). Does the xp_cmdshell get executed by the SQL active node or the MDTC active node? And what is the best way to find out which server is going to run my xp_cmdshell?

Thanks.

Edit:

Perhaps another by product of this is that if I run select host_name() from the Studio Management query window i get different results depending on which server I am running the Studio Management on. On server1 I get server1 and on server 2 I get server 2, all the while server2 is the active node. I need a different function that will always let me determine the correct server that'll be running the xp_cmdshell...

Edit 2: I guess I could determine the running host inside the command shell itself, but I am curious to see if i can do it (cleaner) from SQL.

View 1 Replies View Related

Using Linked Server To Query Active Directory From SQL??

May 28, 2004

I want to create a view in SQL populated with users from our Active Directory. I have learnt that this can be done using linked server. I have tried using the following:

sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
go

sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'false', @locallogin = 'sa', @rmtuser = 'lok_applications', @rmtpassword = '9dfFfG374GoiAo6yxxc8oZ'

SELECT *
FROM OpenQuery( ADSI,
'SELECT * FROM "LDAP://194.22.1.18/DC=lok,DC=net"')

I keep getting this error no matter what I try:
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

Any ideas why??

View 5 Replies View Related

Active/Active Cluster Failure - How Do You Rebuild And Rejoin A Node In The Cluster?

Aug 30, 2006

We have (had) an active/active cluster. 2 physical machines,
each running their own instance, clustered together. Node1/Ins1 and Node2/Ins2.



Node2 failed and Ins2 failed over to Node1 as it should. Node2 required that we
rebuild the server (rebuild = reinstall O/S). Now we need to get Node2 back
into the cluster and get Ins2 failed back over to Node2.



Does anyone know, for certain, the correct steps to accomplish this? Obviously,
we could backup everything and completely destroy Ins2 and recreate it on Node2
then rejoin the cluster. But I'm looking for something less destructive.



Is it possible to reinstall SQL, then rejoin the cluster, and then fail Node2
over? Or will there be registry conflictions?



Any help would be appreciated. Also, if you have any links to some official
documentation, that would be great too.



Thanks

View 4 Replies View Related

Active/Active Clustering

Nov 25, 2005

I am new to clustering and I did found lot of information on internet using google. Only Q I have is, do I have to do Clustering at Windows 2000 (OS level) before I can do it at SQL Server 2000 level.

Thanx

View 1 Replies View Related

Active-to-active Cluster

Mar 18, 2008

Hi,

Do I need 2 quorum for active-to-avtive cluster or just one.


thanx in advanced

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved