i'm trying to find peak connections for each day
select max(connections)as peak from peak_connections
where account_id ='1909' and incident_time between '2004-05-25' and '2004-05-26'
-- the above query returns only the peak for 5/25.
how do I write it so that when i execute the query it will return the peak for all dates.
some sample data is provided below.
thanks for your help.
May 25, 2004 1
May 25, 2004 0
May 25, 2004 1
May 25, 2004 0
May 25, 2004 1
May 25, 2004 2
May 25, 2004 2
May 25, 2004 3
May 25, 2004 4
May 25, 2004 5
May 25, 2004 4
May 25, 2004 5
May 25, 2004 6
May 25, 2004 7
May 25, 2004 8
May 25, 2004 7
May 25, 2004 8
May 25, 2004 7
May 25, 2004 8
May 25, 2004 7
May 25, 2004 8
May 25, 2004 9
May 25, 2004 10
May 25, 2004 11
May 25, 2004 11
May 25, 2004 12
May 25, 2004 13
May 25, 2004 14
May 25, 2004 13
May 25, 2004 12
May 25, 2004 10
May 25, 2004 9
May 25, 2004 8
May 25, 2004 7
May 25, 2004 6
May 25, 2004 5
May 25, 2004 4
May 25, 2004 3
May 25, 2004 3
May 25, 2004 3
May 25, 2004 4
May 25, 2004 3
May 25, 2004 4
May 25, 2004 5
May 25, 2004 6
May 25, 2004 7
May 25, 2004 8
May 25, 2004 8
May 25, 2004 9
May 25, 2004 8
May 25, 2004 7
May 25, 2004 6
May 25, 2004 7
May 25, 2004 6
May 25, 2004 7
May 25, 2004 6
May 25, 2004 7
May 25, 2004 6
May 25, 2004 5
May 25, 2004 5
May 25, 2004 4
May 25, 2004 3
May 25, 2004 2
May 25, 2004 1
May 26, 2004 1
May 26, 2004 0
May 26, 2004 1
May 26, 2004 2
May 26, 2004 1
May 26, 2004 1
May 26, 2004 1
May 26, 2004 2
May 26, 2004 3
May 26, 2004 4
May 26, 2004 3
May 26, 2004 4
May 26, 2004 3
May 26, 2004 4
May 26, 2004 5
May 26, 2004 6
May 26, 2004 7
May 26, 2004 8
May 26, 2004 9
May 26, 2004 9
May 26, 2004 8
May 26, 2004 7
May 26, 2004 6
May 26, 2004 5
May 26, 2004 4
May 26, 2004 3
May 26, 2004 4
May 26, 2004 3
May 26, 2004 4
May 26, 2004 4
May 26, 2004 5
May 26, 2004 3
May 26, 2004 2
May 26, 2004 1
May 27, 2004 1
May 27, 2004 1
May 27, 2004 2
May 27, 2004 1
May 27, 2004 2
May 27, 2004 2
May 27, 2004 1
May 27, 2004 2
May 27, 2004 3
May 27, 2004 2select incident_time, max(connections)as peak from peak_connections
where account_id ='1909' and incident_time between '2004-05-25' and '2004-05-26'
group by incident_time
Books Online, anyone?
Showing posts with label connections. Show all posts
Showing posts with label connections. Show all posts
Wednesday, March 28, 2012
Monday, February 27, 2012
Help with Multiple connections in a CLR stored procedure
Here's what I'm trying to accomplish:
1. Open a connection and retrieve a datareader, using the context connection.
2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection.
The problem I have is that I can't open the second connection. Here's some sample code:
public partial class StoredProcedures {
[Microsoft.SqlServer.Server.SqlProcedure]
public static void up_TestClr() {
//Use the current context connection
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 100 AccountId FROM Account";
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {
SqlConnection conn2 = new SqlConnection();
conn2.ConnectionString = "Server=localhost;Database=TestDb;Integrated Security=SSPI";
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "up_TestProc";
int serviceFilterId = Convert.ToInt32(reader["accountId"]);
SqlParameter parm = new SqlParameter("@.accountId", serviceFilterId);
cmd2.Parameters.Add(parm);
conn2.Open();
cmd2.ExecuteNonQuery();
cmd2.Dispose();
}
reader.Close();
conn.Close();
}
};
When I attempt to execute the procedure, I get the following:
Msg 6549, Level 16, State 1, Procedure up_TestClr, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'up_TestClr':
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at StoredProcedures.up_TestClr(String& dbName)
. User transaction, if any, will be rolled back.
Any suggestions? I've tried unsuccessfully to create an Asymetric key to mark my assembly for External Access:
USE master
GO
CREATE ASYMMETRIC KEY SN FROM EXECUTABLE FILE = 'C:\Shc\SqlServerProject1.dll'
CREATE LOGIN TestLogin FROM ASYMMETRIC KEY SN
GRANT EXTERNAL ACCESS ASSEMBLY TO TestLogin
GO
Resulted in:
Msg 15208, Level 16, State 1, Line 2
The certificate, asymmetric key, or private key file does not exist or has invalid format.
Msg 15151, Level 16, State 1, Line 3
Cannot find the asymmetric key 'SN', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 4
Cannot find the login 'TestLogin', because it does not exist or you do not have permission.
ANY IDEAS/SUGGESTIONS?
That should work fine. Thx.|||
Not sure how using a DataSet helps, but...
He is not opening a 2nd context connection. He is opening a 2nd connection that may happen to point to the same server/database as the context connection.
This may be needed for example to persist auditing information that needs to survive even if the context connection's transaction is later rolled back.
Are you saying this is not allowed?
My code attempts to create a 2nd connection like this, and I'm getting the same error when I attempt to .Open() it later:
<code>
public static SqlConnection NewConnection()
{
SqlConnection connection2 = new SqlConnection("context connection=true");
connection2.Open();
SqlCommand command = new SqlCommand("select @.@.servername, db_name()", connection2);
SqlDataReader sdr = command.ExecuteReader();
sdr.Read();
string serverName = sdr.GetString(0);
string dbName = sdr.GetString(1);
return new SqlConnection("Server=" + serverName + ";Database=" + dbName + ";Trusted_Connection=yes;Enlist=false");
}
</code>
|||I receive the same error when attempting to connect to another db instance. What was the solution for this problem?Help with Multiple connections in a CLR stored procedure
Here's what I'm trying to accomplish:
1. Open a connection and retrieve a datareader, using the context connection.
2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection.
The problem I have is that I can't open the second connection. Here's some sample code:
public partial class StoredProcedures {
[Microsoft.SqlServer.Server.SqlProcedure]
public static void up_TestClr() {
//Use the current context connection
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 100 AccountId FROM Account";
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {
SqlConnection conn2 = new SqlConnection();
conn2.ConnectionString = "Server=localhost;Database=TestDb;Integrated Security=SSPI";
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "up_TestProc";
int serviceFilterId = Convert.ToInt32(reader["accountId"]);
SqlParameter parm = new SqlParameter("@.accountId", serviceFilterId);
cmd2.Parameters.Add(parm);
conn2.Open();
cmd2.ExecuteNonQuery();
cmd2.Dispose();
}
reader.Close();
conn.Close();
}
};
When I attempt to execute the procedure, I get the following:
Msg 6549, Level 16, State 1, Procedure up_TestClr, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'up_TestClr':
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at StoredProcedures.up_TestClr(String& dbName)
. User transaction, if any, will be rolled back.
Any suggestions? I've tried unsuccessfully to create an Asymetric key to mark my assembly for External Access:
USE master
GO
CREATE ASYMMETRIC KEY SN FROM EXECUTABLE FILE = 'C:\Shc\SqlServerProject1.dll'
CREATE LOGIN TestLogin FROM ASYMMETRIC KEY SN
GRANT EXTERNAL ACCESS ASSEMBLY TO TestLogin
GO
Resulted in:
Msg 15208, Level 16, State 1, Line 2
The certificate, asymmetric key, or private key file does not exist or has invalid format.
Msg 15151, Level 16, State 1, Line 3
Cannot find the asymmetric key 'SN', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 4
Cannot find the login 'TestLogin', because it does not exist or you do not have permission.
ANY IDEAS/SUGGESTIONS?
That should work fine. Thx.|||
Not sure how using a DataSet helps, but...
He is not opening a 2nd context connection. He is opening a 2nd connection that may happen to point to the same server/database as the context connection.
This may be needed for example to persist auditing information that needs to survive even if the context connection's transaction is later rolled back.
Are you saying this is not allowed?
My code attempts to create a 2nd connection like this, and I'm getting the same error when I attempt to .Open() it later:
<code>
public static SqlConnection NewConnection()
{
SqlConnection connection2 = new SqlConnection("context connection=true");
connection2.Open();
SqlCommand command = new SqlCommand("select @.@.servername, db_name()", connection2);
SqlDataReader sdr = command.ExecuteReader();
sdr.Read();
string serverName = sdr.GetString(0);
string dbName = sdr.GetString(1);
return new SqlConnection("Server=" + serverName + ";Database=" + dbName + ";Trusted_Connection=yes;Enlist=false");
}
</code>
|||I receive the same error when attempting to connect to another db instance. What was the solution for this problem?
Subscribe to:
Posts (Atom)