EXECUTE AS question: sqlconnection

Showing posts with label sqlconnection. Show all posts

Tuesday, March 27, 2012

Executing large result queries

Hi all,
I've developed an app (C#) that connects to SQL Server 2000 without using
thread pooling (using SqlConnection) and I run a query that returns ~4000
tuples each time it runs and displays the results in a listview. The problem
is that after I run it 3-4 times the results are read very slowly from the
DB. The first times the results are displayed in 1-2 secs but the 4,5 time
it takes more than 2 minutes! What am I doing wrong? Are there any
in-between buffers that need to be emptied?
Thanks,
-peterHi Peter,
If you could show us some code we might be able to make a better
diagnosis.
This is a windows forms application?
Any chance you are appending the results from the second query to the
listbox without clearning the list box first?
--
Scott
http://www.OdeToCode.com
On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
wrote:
>Hi all,
>I've developed an app (C#) that connects to SQL Server 2000 without using
>thread pooling (using SqlConnection) and I run a query that returns ~4000
>tuples each time it runs and displays the results in a listview. The problem
>is that after I run it 3-4 times the results are read very slowly from the
>DB. The first times the results are displayed in 1-2 secs but the 4,5 time
>it takes more than 2 minutes! What am I doing wrong? Are there any
>in-between buffers that need to be emptied?
>Thanks,
>-peter
>|||The results are displayed in a listview and its' items are always cleared
before putting in the new ones...
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:llbch0dojiom2vgf88iltuhpngpl4ikllh@.4ax.com...
> Hi Peter,
> If you could show us some code we might be able to make a better
> diagnosis.
> This is a windows forms application?
> Any chance you are appending the results from the second query to the
> listbox without clearning the list box first?
> --
> Scott
> http://www.OdeToCode.com
> On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
> wrote:
> >Hi all,
> >I've developed an app (C#) that connects to SQL Server 2000 without using
> >thread pooling (using SqlConnection) and I run a query that returns ~4000
> >tuples each time it runs and displays the results in a listview. The
problem
> >is that after I run it 3-4 times the results are read very slowly from
the
> >DB. The first times the results are displayed in 1-2 secs but the 4,5
time
> >it takes more than 2 minutes! What am I doing wrong? Are there any
> >in-between buffers that need to be emptied?
> >
> >Thanks,
> >-peter
> >
>|||Start by using Profiler to see if you get the same execution plan between the executions. Depending on whether
you do or not, you can determine whether this is a SQL Server issue or a client app issue.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've developed an app (C#) that connects to SQL Server 2000 without using
> thread pooling (using SqlConnection) and I run a query that returns ~4000
> tuples each time it runs and displays the results in a listview. The problem
> is that after I run it 3-4 times the results are read very slowly from the
> DB. The first times the results are displayed in 1-2 secs but the 4,5 time
> it takes more than 2 minutes! What am I doing wrong? Are there any
> in-between buffers that need to be emptied?
> Thanks,
> -peter
>|||well i checked it with the profiler and while the SQL:BatchCompleted takes
about the same amount of time for each execution (~2200) the Audit Logout is
different and when the program slows down it takes numbers up to ~584263
while a normal execution takes up to ~20296.
What does this mean?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> Start by using Profiler to see if you get the same execution plan between
the executions. Depending on whether
> you do or not, you can determine whether this is a SQL Server issue or a
client app issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Hi all,
> > I've developed an app (C#) that connects to SQL Server 2000 without
using
> > thread pooling (using SqlConnection) and I run a query that returns
~4000
> > tuples each time it runs and displays the results in a listview. The
problem
> > is that after I run it 3-4 times the results are read very slowly from
the
> > DB. The first times the results are displayed in 1-2 secs but the 4,5
time
> > it takes more than 2 minutes! What am I doing wrong? Are there any
> > in-between buffers that need to be emptied?
> >
> > Thanks,
> > -peter
> >
> >
>|||If you check Books Online, you will find following for the "Audit Logout" event:
Duration The approximate amount of time since the user logged in.
But the SQL:BatchCompleted are the same, so this indicates that the query executes in the same time, the added
time for the logout is the time for the client to process the results before it can logout from the SQL
Server.
I.e., the problem is with the client app (at least as far as I can see). You might want to post this (with
appropriate details) to an ADO.NET group...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx.gbl...
> well i checked it with the profiler and while the SQL:BatchCompleted takes
> about the same amount of time for each execution (~2200) the Audit Logout is
> different and when the program slows down it takes numbers up to ~584263
> while a normal execution takes up to ~20296.
> What does this mean?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> > Start by using Profiler to see if you get the same execution plan between
> the executions. Depending on whether
> > you do or not, you can determine whether this is a SQL Server issue or a
> client app issue.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > > Hi all,
> > > I've developed an app (C#) that connects to SQL Server 2000 without
> using
> > > thread pooling (using SqlConnection) and I run a query that returns
> ~4000
> > > tuples each time it runs and displays the results in a listview. The
> problem
> > > is that after I run it 3-4 times the results are read very slowly from
> the
> > > DB. The first times the results are displayed in 1-2 secs but the 4,5
> time
> > > it takes more than 2 minutes! What am I doing wrong? Are there any
> > > in-between buffers that need to be emptied?
> > >
> > > Thanks,
> > > -peter
> > >
> > >
> >
> >
>|||I'll try to post it there... Thanks anyway.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:upyMcDjfEHA.708@.TK2MSFTNGP09.phx.gbl...
> If you check Books Online, you will find following for the "Audit Logout"
event:
> Duration The approximate amount of time since the user logged in.
>
> But the SQL:BatchCompleted are the same, so this indicates that the query
executes in the same time, the added
> time for the logout is the time for the client to process the results
before it can logout from the SQL
> Server.
> I.e., the problem is with the client app (at least as far as I can see).
You might want to post this (with
> appropriate details) to an ADO.NET group...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx.gbl...
> > well i checked it with the profiler and while the SQL:BatchCompleted
takes
> > about the same amount of time for each execution (~2200) the Audit
Logout is
> > different and when the program slows down it takes numbers up to ~584263
> > while a normal execution takes up to ~20296.
> >
> > What does this mean?
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> > > Start by using Profiler to see if you get the same execution plan
between
> > the executions. Depending on whether
> > > you do or not, you can determine whether this is a SQL Server issue or
a
> > client app issue.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> > news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > > > Hi all,
> > > > I've developed an app (C#) that connects to SQL Server 2000 without
> > using
> > > > thread pooling (using SqlConnection) and I run a query that returns
> > ~4000
> > > > tuples each time it runs and displays the results in a listview. The
> > problem
> > > > is that after I run it 3-4 times the results are read very slowly
from
> > the
> > > > DB. The first times the results are displayed in 1-2 secs but the
4,5
> > time
> > > > it takes more than 2 minutes! What am I doing wrong? Are there any
> > > > in-between buffers that need to be emptied?
> > > >
> > > > Thanks,
> > > > -peter
> > > >
> > > >
> > >
> > >
> >
> >
>

Wednesday, March 21, 2012

ExecuteScalar

private void buttonLogin_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirecto ry|\\PEService.mdf;Integrated Security=True;User Instance=True";
conn.Open();
string strSQL = "Select Count(*) as ctr From Cust Where Email=" + textBoxEmail + "and Passwd=" + textBoxPW;

SqlCommand cmd = new SqlCommand(strSQL,conn);
int ctr=(int)cmd.ExecuteScalar();
if (ctr == 1)
MessageBox.Show("Correct");
else
MessageBox.Show("Wrong");
conn.Close();
}

i have this code for my login form. when i remove conn.Open(); in the code
it says... ExecuteScalar requires an open and available Connection. The connection's current state is closed.

and when i put conn.Open();
it says... An attempt to attach an auto-named database for file C:\... failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

what is the problem?Hi,

I think the strSQL should be set as follows. I added the (') characters.

string strSQL = "Select Count(*) as ctr From Cust Where Email='" + textBoxEmail + "'and Passwd='" + textBoxPW + "'";

Eralper
http://www.kodyaz.com|||Is this production code? You know it is pretty well textbook bad practice as regards security yeah?|||besides the serious sql injection issues pootle_flump is referring to, there are some other (less serious) problems:

you should use the "using" keyword around use of SqlCommand and SqlConnection. that way they get disposed properly when they go out of scope.

currently you are not disposing your SqlCommand at all, so it will cause resource leaks.|||now, i added the using keyword as well as the correct sql statement but still, the same problem.

i have also read about SQL Server Management Studio. Has it something to do with the problems occurring? but i don't have it installed with my visual studio 2005! how to have it?

and to pootle flump, can u further explain because im just a newbie. tnx|||i've solved my problem.. it was all about connection failure. tnx!|||Here's what you should do to avoid the sql injection problem:

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

and here's what could happen if you don't fix it (credit pootle for this link, it's a nice little video demonstration):

http://www.rockyh.net/AssemblyHijacking/AssemblyHijacking.htmlsql

Monday, March 19, 2012

ExecuteNonQuery syntax

Hi, I am trying to execute a nonquery as follows (look for bold):

Dim connStringSQLAsNew SqlConnection("Data Source=...***...Trusted_Connection=False")

'// Create the new OLEDB connection to Indexing Service

Dim connIndAsNew System.Data.OleDb.OleDbConnection(connStringInd)

Dim commandIndAsNew System.Data.OleDb.OleDbDataAdapter(strQueryCombined, connInd)

Dim commandSQLAsNew SqlCommand("GetAssetList2", connStringSQL)

commandSQL.CommandType = Data.CommandType.StoredProcedure

Dim resultDSAsNew Data.DataSet()

Dim resultDAAsNew SqlDataAdapter()

'// Fill the dataset with values

commandInd.Fill(resultDS)

'// Get the XML values of the dataset to send to SQL server and run a new query

...

'// Return the number of results

resultCount.Text = source.Count.ToString

results.DataSource = source

results.DataBind()

'// Record the search

commandSQL =New SqlCommand("RecordSearch", connStringSQL)

commandSQL.Parameters.Clear()

commandSQL.Parameters.Add("@.userName", Data.SqlDbType.VarChar, 50).Value = authUser.Text()

commandSQL.Parameters.Add("@.createdDateTime", Data.SqlDbType.DateTime).Value = DateTime.Now()

commandSQL.Parameters.Add("@.numRows", Data.SqlDbType.Int, 1000).Value = resultCount.Text

commandSQL.Parameters.Add("@.searchString", Data.SqlDbType.VarChar, 1000).Value = searchText.Text

connStringSQL.Open()

commandSQL.ExecuteNonQuery()

connStringSQL.Close()

The stored procedure looks like this:

Use GTGAssets

DROPPROC dbo.RecordSearch;

--New Procedure

CREATEPROC dbo.RecordSearch

(

@.userNamevarchar(50),

@.createdDateTimeDateTime,

@.numRowsvarchar(1000),

@.searchStringvarchar(1000)

)

BEGIN

SETNOCOUNTON

INSERTINTO SearchLog(SearchString, CreatedByUser, CreatedDTTM, RowsReturned)VALUES(@.searchString, @.userName, @.createdDateTime, @.numRows)

END

Any ideas as to why this error is appearing?

Incorrect syntax near 'RecordSearch'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'RecordSearch'.

Source Error:

Line 169: commandSQL.Parameters.Add("@.searchString", Data.SqlDbType.VarChar, 1000).Value = searchText.TextLine 170: connStringSQL.Open()Line 171: commandSQL.ExecuteNonQuery()Line 172: connStringSQL.Close()Line 173: End If

Many thanks!

James

Alright, I found this - it's the way the DateTime is being passed in. The DateTime parameter for the last command looks like this:

#9/11/2006 4:01:25 PM#

However, when this procedure is run on SQL Server Query Analyzer:

USE [GTGAssets]
GO
DECLARE @.return_value int
EXEC @.return_value = [dbo].[RecordSearch]
@.userName = N'name',
@.createdDateTime = N'#9/11/2006 4:01:25 PM#',
@.numRows = N'10',
@.searchString = N'soa'
SELECT 'Return Value' = @.return_value
GO

I get this SQL Server error. I tried this insert (notice the lack of pound signs) it actually works.

USE [GTGAssets]
GO
DECLARE @.return_value int
EXEC @.return_value = [dbo].[RecordSearch]
@.userName = N'CVNS\JFogarty',
@.createdDateTime = N'9/11/2006 4:01:25 PM',
@.numRows = N'10',
@.searchString = N'soa'
SELECT 'Return Value' = @.return_value
GO

Why is this? The pound signs should not be added to the DateTime.Now(), should it?

Thanks!|||You need to enclose date with #'s for Access DB not SQL Server.|||

Well the problem is that VB.Net automattical adds the # - this is the code:

commandSQL.Parameters.Add("@.createdDateTime", Data.SqlDbType.DateTime).Value = DateTime.Now()

|||AFAIK it shouldnt be putting any #'s.|||You forgot to set the commandtype

ExecuteNonQuery hangs in Timer event notification

If I call ExecuteNonQuery() in a timer event callback in a console application, it hangs. Why is that?

.B ekiM

class Program
{
static SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NONEOFYOURBISUINESS;Data Source=localhost");

static void Main(string[] args)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);
Console.WriteLine("{0} bytes", mem.ullAvailPhys);

System.Timers.Timer aTimer = new System.Timers.Timer();
// Set the Interval to 2 seconds (2000 milliseconds).
aTimer.Interval = 1000;
aTimer.Enabled = true;

// Hook up the Elapsed event for the timer.
aTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent);

Console.ReadLine();
}

private static void OnTimedEvent(object source, ElapsedEventArgs e)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);

SqlCommand cmd = new SqlCommand(
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (1, @.When, @.AvailPhys);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (2, @.When, @.AvailPageFile);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (3, @.When, @.AvailVirtual);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (4, @.When, @.AvailExtendedVirtual);\n");

DateTime dt = DateTime.Now;
cmd.Parameters.AddWithValue("AvailPhys", mem.ullAvailPhys);
cmd.Parameters.AddWithValue("AvailPageFile", mem.ullAvailPageFile);
cmd.Parameters.AddWithValue("AvailVirtual", mem.ullAvailVirtual);
cmd.Parameters.AddWithValue("AvailExtendedVirtual", mem.ullAvailExtendedVirtual);
cmd.Parameters.AddWithValue("When", dt);

cmd.ExecuteNonQuery();

Console.WriteLine("Inserted {0}", dt);
}

}

A-hah! It's not hanging; it's just throwing an exception that the runtime itself catches, then doesn't report.|||Is the problem solved then, or you you want to elaborate on the error message ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||
The problem is that the runtime catches an exception. It shouldn't: it hasn't published a contract saying it will catch exceptions. It also offers very little indication that it did catch the exception.

These problems are certainly not solved.

ExecuteNonQuery hangs in Timer event notification

If I call ExecuteNonQuery() in a timer event callback in a console application, it hangs. Why is that?

.B ekiM

class Program
{
static SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NONEOFYOURBISUINESS;Data Source=localhost");

static void Main(string[] args)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);
Console.WriteLine("{0} bytes", mem.ullAvailPhys);

System.Timers.Timer aTimer = new System.Timers.Timer();
// Set the Interval to 2 seconds (2000 milliseconds).
aTimer.Interval = 1000;
aTimer.Enabled = true;

// Hook up the Elapsed event for the timer.
aTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent);

Console.ReadLine();
}

private static void OnTimedEvent(object source, ElapsedEventArgs e)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);

cmd.ExecuteNonQuery();

Console.WriteLine("Inserted {0}", dt);
}

}

A-hah! It's not hanging; it's just throwing an exception that the runtime itself catches, then doesn't report.|||Is the problem solved then, or you you want to elaborate on the error message ?

Jens K. Suessmeyer.

These problems are certainly not solved.

Tuesday, March 27, 2012

Executing large result queries

Executing large result queries

Executing large result queries

Wednesday, March 21, 2012

ExecuteScalar

Monday, March 19, 2012

ExecuteNonQuery syntax

Incorrect syntax near 'RecordSearch'.

ExecuteNonQuery hangs in Timer event notification

ExecuteNonQuery hangs in Timer event notification

EXECUTE AS question

Blog Archive

About Me