sqlserver2000 date: application

Showing posts with label application. Show all posts

Friday, March 30, 2012

Increase Performance with two connections

HI all,
I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
In DB2 I use a view which uses some joins against DB1 like:
select * from MyTable left outer join DB1.dbo.users on ...
This works, but I get a time problem: the view is very slow.
The reason seems to be the connection to DB1.
In SQL Management Studio I can reproduce the situation: when I´m in DB2 and
try to "USE DB1" it takes something like >=2 seconds on my local machine.
After making the "USE DB1" the view works great - very fast (SQL Server
seemed to cache the connection).
But the connection caching works only for some minutes - when "falling back"
the view is slow again.
So, I´m looking for a solution for my problem (I can´t change the
database-concept, some data from DB1 is needed also in DB2). Maybe open the
connection DB1 for ... some hours? Or somebody has better ideas?
Thanks,
TonyI assume you have two databases in the same SQL Server instance (which is what it sounds like from
your description). Check if the database DB1 has the database property autoclose turned on. If so,
turn it off.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
> HI all,
> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
> In DB2 I use a view which uses some joins against DB1 like:
> select * from MyTable left outer join DB1.dbo.users on ...
> This works, but I get a time problem: the view is very slow.
> The reason seems to be the connection to DB1.
> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1" it
> takes something like >=2 seconds on my local machine.
> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
> connection).
> But the connection caching works only for some minutes - when "falling back" the view is slow
> again.
> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data from
> DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody has
> better ideas?
> Thanks,
> Tony
>|||HI Tibor,
thanks for your reply.
Yes, Autoclose=ON is standard.
Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume
it will work).
Does Autoclose=OFF have any other effects...?
Help of SQL2005 says:
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature.
The AutoClose property exposes server behavior for databases not accessed by
a user.
Thanks, Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>I assume you have two databases in the same SQL Server instance (which is
>what it sounds like from your description). Check if the database DB1 has
>the database property autoclose turned on. If so, turn it off.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2
>> and try to "USE DB1" it takes something like >=2 seconds on my local
>> machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server
>> seemed to cache the connection).
>> But the connection caching works only for some minutes - when "falling
>> back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe open
>> the connection DB1 for ... some hours? Or somebody has better ideas?
>> Thanks,
>> Tony
>|||> Yes, Autoclose=ON is standard.
Standard? In your shop or in SQL Server? I believe that some edition of SQL Server has this turned
on by default, which was a bad (IMO) choice. Anyhow, turn this off and see if it fixes your problem.
> Does Autoclose=OFF have any other effects...?
I hope you don't have other programs that tries to do anything with the database files while no user
is connected? If you do, then they will be affected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
> HI Tibor,
> thanks for your reply.
> Yes, Autoclose=ON is standard.
> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume it will work).
> Does Autoclose=OFF have any other effects...?
> Help of SQL2005 says:
> This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature
> in new development work, and plan to modify applications that currently use this feature.
> The AutoClose property exposes server behavior for databases not accessed by a user.
> Thanks, Tony
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
> news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is what it sounds like from
>>your description). Check if the database DB1 has the database property autoclose turned on. If so,
>>turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1" it
>> takes something like >=2 seconds on my local machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
>> connection).
>> But the connection caching works only for some minutes - when "falling back" the view is slow
>> again.
>> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data
>> from DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody
>> has better ideas?
>> Thanks,
>> Tony
>>
>|||HI Tibor,
yes, with Autoclose=OFF the App works perfect!
But I only don´t really understand
> I hope you don't have other programs that tries to do anything with the
> database files while no user is connected? If you do, then they will be
> affected.
Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0)
which all use database DB1 - and some other database-stored procs/views.
Will they be affected?
Or is it ok to simply use Autoclose=OFF (and I don´t have to care about any
side-effects)?
Thanks again,
Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
> Standard? In your shop or in SQL Server? I believe that some edition of
> SQL Server has this turned on by default, which was a bad (IMO) choice.
> Anyhow, turn this off and see if it fixes your problem.
>> Does Autoclose=OFF have any other effects...?
> I hope you don't have other programs that tries to do anything with the
> database files while no user is connected? If you do, then they will be
> affected.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll
>> assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL Server.
>> Avoid using this feature in new development work, and plan to modify
>> applications that currently use this feature.
>> The AutoClose property exposes server behavior for databases not accessed
>> by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is
>>what it sounds like from your description). Check if the database DB1 has
>>the database property autoclose turned on. If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2
>> and try to "USE DB1" it takes something like >=2 seconds on my local
>> machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server
>> seemed to cache the connection).
>> But the connection caching works only for some minutes - when "falling
>> back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe open
>> the connection DB1 for ... some hours? Or somebody has better ideas?
>> Thanks,
>> Tony
>>
>|||The application uses the database *through* SQL Server. I.e., they don't access the database files
directly.
I recommend that you have autoclose set to off.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:44904194-2251-46E3-995E-9616DD6FD563@.microsoft.com...
> HI Tibor,
> yes, with Autoclose=OFF the App works perfect!
> But I only don´t really understand
>> I hope you don't have other programs that tries to do anything with the database files while no
>> user is connected? If you do, then they will be affected.
> Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0) which all use database
> DB1 - and some other database-stored procs/views. Will they be affected?
> Or is it ok to simply use Autoclose=OFF (and I don´t have to care about any side-effects)?
> Thanks again,
> Tony
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
> news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
>> Standard? In your shop or in SQL Server? I believe that some edition of SQL Server has this
>> turned on by default, which was a bad (IMO) choice. Anyhow, turn this off and see if it fixes
>> your problem.
>> Does Autoclose=OFF have any other effects...?
>> I hope you don't have other programs that tries to do anything with the database files while no
>> user is connected? If you do, then they will be affected.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL Server. Avoid using this
>> feature in new development work, and plan to modify applications that currently use this
>> feature.
>> The AutoClose property exposes server behavior for databases not accessed by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
>> news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is what it sounds like
>>from your description). Check if the database DB1 has the database property autoclose turned on.
>>If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1"
>> it takes something like >=2 seconds on my local machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
>> connection).
>> But the connection caching works only for some minutes - when "falling back" the view is slow
>> again.
>> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data
>> from DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody
>> has better ideas?
>> Thanks,
>> Tony
>>
>>
>|||Hi Tibor,
yes, I also did a little reasearch on AutoClose - schould always be off on
server and productive environments!
Thanks again for your hint! This was my solution!
Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:ePINj6U6HHA.5164@.TK2MSFTNGP05.phx.gbl...
> The application uses the database *through* SQL Server. I.e., they don't
> access the database files directly.
>
> I recommend that you have autoclose set to off.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:44904194-2251-46E3-995E-9616DD6FD563@.microsoft.com...
>> HI Tibor,
>> yes, with Autoclose=OFF the App works perfect!
>> But I only don´t really understand
>> I hope you don't have other programs that tries to do anything with the
>> database files while no user is connected? If you do, then they will be
>> affected.
>> Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0)
>> which all use database DB1 - and some other database-stored procs/views.
>> Will they be affected?
>> Or is it ok to simply use Autoclose=OFF (and I don´t have to care about
>> any side-effects)?
>> Thanks again,
>> Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
>> Standard? In your shop or in SQL Server? I believe that some edition of
>> SQL Server has this turned on by default, which was a bad (IMO) choice.
>> Anyhow, turn this off and see if it fixes your problem.
>> Does Autoclose=OFF have any other effects...?
>> I hope you don't have other programs that tries to do anything with the
>> database files while no user is connected? If you do, then they will be
>> affected.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll
>> assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL
>> Server. Avoid using this feature in new development work, and plan to
>> modify applications that currently use this feature.
>> The AutoClose property exposes server behavior for databases not
>> accessed by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which
>>is what it sounds like from your description). Check if the database
>>DB1 has the database property autoclose turned on. If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in
>> DB2 and try to "USE DB1" it takes something like >=2 seconds on my
>> local machine.
>> After making the "USE DB1" the view works great - very fast (SQL
>> Server seemed to cache the connection).
>> But the connection caching works only for some minutes - when
>> "falling back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe
>> open the connection DB1 for ... some hours? Or somebody has better
>> ideas?
>> Thanks,
>> Tony
>>
>>
>

increase in CPU usage on lock and unlock of the system

Ours is a windows based application.
When we open the application the CPU usage is 0% and the Memory Usage
is 54,324Kb
When I open a specific form in a module, the CPU usage is 0% and the
Memory Usage increases accordingly (67,730 Kb).
Now when I lock and unlock the System the CPU usage increases to 50%
with the Memory Usage being 67,730 kb.
I have made use of a .Net memory profiler and had taken snapshots
before locking and unlocking the system.
The following is the result after comparing both the versions.
NameSpace Name Total New Removed Delta Total
Max Min Delta
System WeakRefrence 1681 613 0 613 26896
16 16 9808
System.Reflection RunTimeMethodInfo 1229 8
0 8 29496 24 24 192
System.Windows.Forms NativeMethods.TracKMOU.. 10
5 0 5 240 24 24 120
System Runtimetype 2725 4 0 4 4360
16 16 64
System String 37232 5 1 4 2256 102456
18 458
Can anybody let me know the reason for the increase in CPU usage and
how I can overcome this.
And why is there a drastic increase in the number of instances for
WeakReference class when the operation is just locking and unlocking
and nothing to do with the application.
Thanks in advance
sowmyaIs this question related to SQL Server?. If so, please explain what you
mean by locking/unlocking. Perhaps you intended to post to a .Net forum.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<sowmya.rangineni@.gmail.com> wrote in message
news:1182142975.732292.25730@.m36g2000hse.googlegroups.com...
> Ours is a windows based application.
> When we open the application the CPU usage is 0% and the Memory Usage
> is 54,324Kb
> When I open a specific form in a module, the CPU usage is 0% and the
> Memory Usage increases accordingly (67,730 Kb).
>
> Now when I lock and unlock the System the CPU usage increases to 50%
> with the Memory Usage being 67,730 kb.
>
> I have made use of a .Net memory profiler and had taken snapshots
> before locking and unlocking the system.
>
> The following is the result after comparing both the versions.
>
> NameSpace Name Total New Removed Delta Total
> Max Min Delta
> System WeakRefrence 1681 613 0 613 26896
> 16 16 9808
> System.Reflection RunTimeMethodInfo 1229 8
> 0 8 29496 24 24 192
> System.Windows.Forms NativeMethods.TracKMOU.. 10
> 5 0 5 240 24 24 120
> System Runtimetype 2725 4 0 4 4360
> 16 16 64
> System String 37232 5 1 4 2256 102456
> 18 458
>
> Can anybody let me know the reason for the increase in CPU usage and
> how I can overcome this.
>
> And why is there a drastic increase in the number of instances for
> WeakReference class when the operation is just locking and unlocking
> and nothing to do with the application.
> Thanks in advance
> sowmya
>

Friday, March 23, 2012

Incorrect syntax near '?'

Hello. When I run my application (it's used to place orders) in VB.net I get the following message: "Failed to complete order! Reason: Incorrect syntax near 'champnr'. Incorrect syntax near '?'. At System.Data.Sqlclient.Sqlcommand.ExecuteReader(CommandBehaviour cmdBehaviour, Runbehavior runbehavior, Boolean returnStream)
Here is a part of the code:
Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("beskrivning", System.Data.SqlDbType.NVarChar, 0, "beskrivning"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestallarnr", System.Data.SqlDbType.Int, 0, "bestallarnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestdatum", System.Data.SqlDbType.DateTime, 0, "bestdatum"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bolagsnr", System.Data.SqlDbType.Int, 0, "bolagsnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("champnr", System.Data.SqlDbType.NVarChar, 255, "champnr"))
Any ideas? I'm not using any stored procedures. Maybe I should?
Hi,

Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (@.beskrivning, @.bestallarnr, @.bestdatum, @.bolagsnr, @.champnr, @.costcenter, @.doknr, @.doktypnr, @.ordernr, @.projekt, @.timenr, @.ttkommentar, @.volvokomment)

Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add("@.beskrivning", System.Data.SqlDbType.NVarChar)

and so on...

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Incorrect syntax near ''?''

Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add("@.beskrivning", System.Data.SqlDbType.NVarChar)

and so on...

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

This post was very helpful to me, thanks

incorrect syntax after server up for a few days

Dear All,

My VB.Net application connects to MSSQL. It is always running fine for a few days, but encounters "incorrect syntax" as following unless the server is restarted.

--
Exception occurred System.Runtime.InteropServices.COMException (0x80040E14): Line 1: incorrect syntax near 'CDO'.
at ADODB.ConnectionClass.Execute (String CommandText, Object& RecordsAffected, Int32 Options)
--

There are a few applications in the server. If certain service is stopped, my program continues to run. So I am sure that the MSSQL connections have been taken up, which causes the error. How to prove it? And is there any way to reserve some DB connections to a particular application only?

Thanks for any hint!

I attach my codes below. Anything wrong with the way that I handled the ADODB?

------------------
Public Sub SendAllEmails()
Try
Dim cn As ADODB.Connection = openConn()
Dim rs As ADODB._Recordset
Dim rs2 As ADODB._Recordset
sqlstmt = "select * FROM EMAILTABLE"
rs = cn.Execute(sqlstmt)

While Not rs.EOF

Dim MAIL_ADD_USED As String = rs.Fields("MAIL_ADD_USED").Value.ToString

sqlstmt = "select * from NAMETABLE where EMAIL = '" & MAIL_ADD & "'"
rs2 = cn.Execute(sqlstmt)

If Not rs2.EOF And ErrMsg = "" Then

ErrMsg = SendMail(MAIL_ADD, REPORT_TITLE)

If Not ErrMsg Is Nothing And ErrMsg.Equals("Success") Then
MAIL_DATE_SENT = Now.ToString
MAIL_STATUS = "S"

'wait to make sure the email is sent
System.Threading.Thread.Sleep(1000 * 30)
Else
MAIL_STATUS = "F"
End If

End If

rs2.Close()

sqlstmt = "update EMAILTABLE set " & _
" MAIL_STATUS = " & MAIL_STATUS & "," & _
" MAIL_ERRMSG = null" & _
" where EMAIL = " & MAIL_ADD

cn.Execute(sqlstmt)

rs.MoveNext()

End While

rs.Close()
cn.Close()

Catch e As Exception
EventLog1.WriteEntry("Exception: " & e.ToString)
End Try

End Sub
----------------------Dear All,

My VB.Net application connects to MSSQL. It is always running fine for a few days, but encounters "incorrect syntax" as following unless the server is restarted.

--

Are you using VB.Net? Then how come Recordset come into existence...:S
See the BOL to use VB.net (ADO.Net)|||Well I took over the codes from the programmer. vb.net is quite new to me. thanks for the hint but is there any sample about how to make use of ado.net?

Actually I am more keen to know whether the MSSQL connection can be released and allocated by DBA. There are othere applications that I simply have no control.

Thanks.|||Well I took over the codes from the programmer. vb.net is quite new to me. thanks for the hint but is there any sample about how to make use of ado.net?

Actually I am more keen to know whether the MSSQL connection can be released and allocated by DBA. There are othere applications that I simply have no control.

Thanks.

Check these...
sending mail (http://www.c-sharpcorner.com/UploadFile/sushmita_kumari/SendingMail101062006054220AM/SendingMail1.aspx?ArticleID=91ece6d8-eaaf-41ab-ac6f-533dc215eacf)

Ado.net stored proc use (http://aspalliance.com/673_CodeSnip_Calling_a_Stored_Procedure_from_ASPNE T_20)

All about Ado.net (http://aspalliance.com/articles/LearnADONET.aspx)

And you can always use Profiler to check the status of your server.And to kill process check this
Kill Process (http://msdn2.microsoft.com/en-us/library/ms173730.aspx)

Wednesday, March 21, 2012

Incorrect SET options all of a sudden?

We've been running a SQL Server based application for some time (Access
front-end). Suddenly, the application is reporting an error when running a
stored procedure to insert a new row in a specific table; there's an Exec
statement doing it. Here is the error:
--
Insert failed because the following SET options have incorrect settings:
ANSI_nulls
Quoted_identifier
Arith abort
--
Does anyone know what could have changed in SQL Server to cause this? We can
add a new record manually through Enterprise Manager. Thanks!!Is it possible that something in the app changed that issues different set
statments? Use SQL Profiler to take a look at what's being sent...
or... could it be that someone recompiled the procedure with different set
options in effect?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> We've been running a SQL Server based application for some time (Access
> front-end). Suddenly, the application is reporting an error when running a
> stored procedure to insert a new row in a specific table; there's an Exec
> statement doing it. Here is the error:
> --
> Insert failed because the following SET options have incorrect settings:
> ANSI_nulls
> Quoted_identifier
> Arith abort
> --
> Does anyone know what could have changed in SQL Server to cause this? We
can
> add a new record manually through Enterprise Manager. Thanks!!
>|||Hi,
I don't know if its possible. This is a production system that started to
exhibit this behaviour in the middle of the day. There's just one developer,
but he always works on a separate development database (same server
however). We'll keep checking. Any additional ideas are welcome.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:#TX7M5ydDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Is it possible that something in the app changed that issues different set
> statments? Use SQL Profiler to take a look at what's being sent...
> or... could it be that someone recompiled the procedure with different set
> options in effect?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
> news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> > We've been running a SQL Server based application for some time (Access
> > front-end). Suddenly, the application is reporting an error when running
a
> > stored procedure to insert a new row in a specific table; there's an
Exec
> > statement doing it. Here is the error:
> >
> > --
> > Insert failed because the following SET options have incorrect settings:
> > ANSI_nulls
> > Quoted_identifier
> > Arith abort
> > --
> >
> > Does anyone know what could have changed in SQL Server to cause this? We
> can
> > add a new record manually through Enterprise Manager. Thanks!!
> >
> >
>|||Perhaps someone created an index on a computed column or on a view that
references the table. This will require that the options listed be
turned on during update operations.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> We've been running a SQL Server based application for some time
(Access
> front-end). Suddenly, the application is reporting an error when
running a
> stored procedure to insert a new row in a specific table; there's an
Exec
> statement doing it. Here is the error:
> --
> Insert failed because the following SET options have incorrect
settings:
> ANSI_nulls
> Quoted_identifier
> Arith abort
> --
> Does anyone know what could have changed in SQL Server to cause this?
We can
> add a new record manually through Enterprise Manager. Thanks!!
>sql

Monday, March 12, 2012

Inconsistent stored procedure syntax error

We have a stored procedure that is being called from a vb6 application. The exact line of code is below.

mats_Reports_CSR_OB_Hourly_Percent('11/1/2006 4:51:27 PM','12/1/2006 4:51:27 PM')

when executing this in query analyzer it will always fail due to the parenthesis. However when this same line of code is passed to sql server in the vb6 application it usually works. There are times it fails with a syntax error message similar to:

Incorrect syntax near '{

I fixed the problem last time by removing the parenthesis in the vb6 code. However I can now put the parenthesis back into the code and it works again now.

Prior to my recent intervention the code was not being changed as it will work for days and then randomly stop working. Then without any change start working by itself again.

What is causing this behavior? How do I make it always work?

Calling a sql stored procedure is not like calling a vb function. Since you're calling a stored procedure, consider using Parameters collection to pass into desired inputs.

http://windowssdk.msdn.microsoft.com/en-gb/library/ms675869.aspx|||

Thank you for your response.

Your response seems to imply there is a bug in the way ODBC handles stored procedure calls?

This application was built over 3 years ago. It is not a realistic solution to switch connections from ODBC to ADO, as it would require the entire application to be recoded.

I have spent the better part of the day yesterday doing research on this problem and it seems it has something to do with ODBC escape sequences. What conditions would change how sql server parses a statement? There has to be something the server is automatically deciding on using that changes, because the same code works and then doesn't work (running against the same data).

|||No. What I hinted at is that you shouldn't call a proc like so:

myproc(para1,para2)

Instead you should do:

cmd.CommandText="myproc para1, para2"

or create a parameters collection for your parameters.|||I already know not using the parenthesis will work. I was looking for why it's happening.|||There is a simple answer: Because its the syntax of calling stored procedures that way. Did you ever ask yourself why a vb function is called like SomeFunction('Something') and not like SomeFunction*'Something'* :-) Its simply the syntax that is specified for calling the procedure.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

OK, your missing what I am getting at. I will make an analogy:

Say your using your keyboard, everytime you press the the 'k' key you'd expect the letter k every time you pressed it.

Now if sometimes when you pressed the 'k' key you got the letter q you'd pull the keyboard and replace it with one that works, or you'd send the keyboard in for repair.

Now what I am describing is the same way. If you call a function using the syntax I already described above you'd expect it to work every time you used it. What I am getting though is sometimes the same syntax is sucessfully running, other times it is giving a syntax error. I am asking why is this occuring? What can you think of that would change to cause this behavior?

|||Where is the error thrown ? Could it be that this is some SQL Server error (not actually in the frontend) which is caused by some malformed composed dynamic SQL in the backend, producing error messages like this ? Are you able to start profiler on the server to see what happends behind the scenes and what comamdns are fired against the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Inconsistent Query Performance

Hello!
We are having a problem with one of the queries that an application runs.
This query uses some temp tables (subbed as table variables with no
performance change) and heavily uses some user defined functions. When run
on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
2000 Developer Editions), it returns within a second. When run against a
enterprise class server with 8 GB of RAM and at least four processors, maybe
eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
is nothing else going on with the large server. This will be the only
activity on the system.. I disabled awe and turned off /PAE switch with no
change. The timings are repeatable and the only improvement on the
enterprise class server is shortly after a reboot and even then the results
take 2-4 seconds vs, 1 second on the workstation. This performance quickly
deteriorates on the server. I have tried this with the database both on SAN
or on local disk on the server and there is no measurable difference.
Any ideas on what I should check next?
ThanksSteve,
> Any ideas on what I should check next?
Execution Plans
AMB
"Steve H" wrote:
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When run
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, maybe
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with no
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the results
> take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> deteriorates on the server. I have tried this with the database both on SAN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||First step is to check the execution plans. Same?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When run
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, maybe
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with no
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the results
> take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> deteriorates on the server. I have tried this with the database both on SAN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||Yes, execution plans are very similar. Not exactly identical - but all
components are very similar in costs.
"Tibor Karaszi" wrote:
> First step is to check the execution plans. Same?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> > Hello!
> > We are having a problem with one of the queries that an application runs.
> > This query uses some temp tables (subbed as table variables with no
> > performance change) and heavily uses some user defined functions. When run
> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> > 2000 Developer Editions), it returns within a second. When run against a
> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> > is nothing else going on with the large server. This will be the only
> > activity on the system.. I disabled awe and turned off /PAE switch with no
> > change. The timings are repeatable and the only improvement on the
> > enterprise class server is shortly after a reboot and even then the results
> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> > deteriorates on the server. I have tried this with the database both on SAN
> > or on local disk on the server and there is no measurable difference.
> >
> > Any ideas on what I should check next?
> >
> > Thanks
>
>|||Similar <> same :-)
What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also check
for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
inefficient result, and can be turned off using the MAXDOP hint (set to 1).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> Yes, execution plans are very similar. Not exactly identical - but all
> components are very similar in costs.
> "Tibor Karaszi" wrote:
>> First step is to check the execution plans. Same?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
>> > Hello!
>> > We are having a problem with one of the queries that an application runs.
>> > This query uses some temp tables (subbed as table variables with no
>> > performance change) and heavily uses some user defined functions. When run
>> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>> > 2000 Developer Editions), it returns within a second. When run against a
>> > enterprise class server with 8 GB of RAM and at least four processors, maybe
>> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>> > is nothing else going on with the large server. This will be the only
>> > activity on the system.. I disabled awe and turned off /PAE switch with no
>> > change. The timings are repeatable and the only improvement on the
>> > enterprise class server is shortly after a reboot and even then the results
>> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>> > deteriorates on the server. I have tried this with the database both on SAN
>> > or on local disk on the server and there is no measurable difference.
>> >
>> > Any ideas on what I should check next?
>> >
>> > Thanks
>>|||Stats IO showed very small differences in number of logical reads. Physical
reads were 0 all around - well cached I guess. I had tried to set the server
to have SQL Server use only 1 processor which should have the same affect as
the MAXDOP setting = 1 (there is some looping and several queries involved
in this proc). Funny thing is that this behavior is repeatable on other
large servers and the quick performance occurs on other workstations.
"Tibor Karaszi" wrote:
> Similar <> same :-)
> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also check
> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> > Yes, execution plans are very similar. Not exactly identical - but all
> > components are very similar in costs.
> >
> > "Tibor Karaszi" wrote:
> >
> >> First step is to check the execution plans. Same?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> >> > Hello!
> >> > We are having a problem with one of the queries that an application runs.
> >> > This query uses some temp tables (subbed as table variables with no
> >> > performance change) and heavily uses some user defined functions. When run
> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> >> > 2000 Developer Editions), it returns within a second. When run against a
> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> >> > is nothing else going on with the large server. This will be the only
> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
> >> > change. The timings are repeatable and the only improvement on the
> >> > enterprise class server is shortly after a reboot and even then the results
> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> >> > deteriorates on the server. I have tried this with the database both on SAN
> >> > or on local disk on the server and there is no measurable difference.
> >> >
> >> > Any ideas on what I should check next?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>|||Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is the same (or cost is very
close), you tend to get the same performance (or a higher on the high-end machine). You could try
posting to the group -relationalserver.performance and see if you get some more attention there. Or
perhaps even time to open a ticket with MS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...
> Stats IO showed very small differences in number of logical reads. Physical
> reads were 0 all around - well cached I guess. I had tried to set the server
> to have SQL Server use only 1 processor which should have the same affect as
> the MAXDOP setting = 1 (there is some looping and several queries involved
> in this proc). Funny thing is that this behavior is repeatable on other
> large servers and the quick performance occurs on other workstations.
> "Tibor Karaszi" wrote:
>> Similar <> same :-)
>> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also
>> check
>> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
>> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
>> > Yes, execution plans are very similar. Not exactly identical - but all
>> > components are very similar in costs.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> First step is to check the execution plans. Same?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
>> >> > Hello!
>> >> > We are having a problem with one of the queries that an application runs.
>> >> > This query uses some temp tables (subbed as table variables with no
>> >> > performance change) and heavily uses some user defined functions. When run
>> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>> >> > 2000 Developer Editions), it returns within a second. When run against a
>> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
>> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>> >> > is nothing else going on with the large server. This will be the only
>> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
>> >> > change. The timings are repeatable and the only improvement on the
>> >> > enterprise class server is shortly after a reboot and even then the results
>> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>> >> > deteriorates on the server. I have tried this with the database both on SAN
>> >> > or on local disk on the server and there is no measurable difference.
>> >> >
>> >> > Any ideas on what I should check next?
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>|||Thank you for your help.
"Tibor Karaszi" wrote:
> Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is the same (or cost is very
> close), you tend to get the same performance (or a higher on the high-end machine). You could try
> posting to the group -relationalserver.performance and see if you get some more attention there. Or
> perhaps even time to open a ticket with MS?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...
> > Stats IO showed very small differences in number of logical reads. Physical
> > reads were 0 all around - well cached I guess. I had tried to set the server
> > to have SQL Server use only 1 processor which should have the same affect as
> > the MAXDOP setting = 1 (there is some looping and several queries involved
> > in this proc). Funny thing is that this behavior is repeatable on other
> > large servers and the quick performance occurs on other workstations.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Similar <> same :-)
> >> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also
> >> check
> >> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
> >> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> >> > Yes, execution plans are very similar. Not exactly identical - but all
> >> > components are very similar in costs.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> First step is to check the execution plans. Same?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> >> >> > Hello!
> >> >> > We are having a problem with one of the queries that an application runs.
> >> >> > This query uses some temp tables (subbed as table variables with no
> >> >> > performance change) and heavily uses some user defined functions. When run
> >> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> >> >> > 2000 Developer Editions), it returns within a second. When run against a
> >> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> >> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> >> >> > is nothing else going on with the large server. This will be the only
> >> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
> >> >> > change. The timings are repeatable and the only improvement on the
> >> >> > enterprise class server is shortly after a reboot and even then the results
> >> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> >> >> > deteriorates on the server. I have tried this with the database both on SAN
> >> >> > or on local disk on the server and there is no measurable difference.
> >> >> >
> >> >> > Any ideas on what I should check next?
> >> >> >
> >> >> > Thanks
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||On Wed, 17 May 2006 13:01:02 -0700, Steve H
<SteveH@.discussions.microsoft.com> wrote:
>We are having a problem with one of the queries that an application runs.
>This query uses some temp tables (subbed as table variables with no
>performance change) and heavily uses some user defined functions. When run
>on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>2000 Developer Editions), it returns within a second. When run against a
>enterprise class server with 8 GB of RAM and at least four processors, maybe
>eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>is nothing else going on with the large server. This will be the only
>activity on the system.. I disabled awe and turned off /PAE switch with no
>change. The timings are repeatable and the only improvement on the
>enterprise class server is shortly after a reboot and even then the results
>take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>deteriorates on the server. I have tried this with the database both on SAN
>or on local disk on the server and there is no measurable difference.
>Any ideas on what I should check next?
Dunno, but I think I've got it too, or a close relative.
SP uses some permanent temp tables - tables that I truncate at start
of routine, then fill up with about a million records, perhaps
confusing the optimizer somewhat. Maybe I need to trigger more
recompiles or something? Also uses a simple UDF in the (newest
version) select clause only.
On same machine:
SP runs in ten minutes when it feels like it.
SP runs in twenty minutes other times.
SP runs in about 90 minutes other times.
Data is exactly the same.
Of course, production is seeing the longer times.
Not completely controlled for contending loads, but such as there is
does not explain the 20 to 90 jump in any case. It seems to go CPU
bound for extended periods. Stats IO doesn't show anything outrageous
except for the elapsed time - the time spent in the UDF seems shielded
from stats time, btw, thanks MSFT.
--
Unfortunately we're running this on SQL2K sp2, kind of retro, and
don't ask about the hardware!
But it's very frustrating to have this kind of poor performance and
variability for no apparent reason.
Josh

Inconsistent Performance of Insert

I am experiencing inconsistent performance when running
insert statements.
Our application automatically generates and sequentially
executes SQL statements that build a results table. After
loading data and running this process, all statements
(about 125 in total) will execute in about 10 minutes. If
it is necessary to rerun the statements I truncate the
results table and rerun the job. In some cases this
second job runs in 10 minutes, and in other cases it runs
6 hours. In particular, one statement takes an inordinate
amount of time.
I've used PROFILER (although I'm not too experienced in
this) and can see that when statements are running
normally the log file updates in the 1000+ per second
range, but with the problematic statement updates are in
the 10 per second range. These updates seem to appear
after the commit statement is executed.
In various combinations I've tried restarting SQLServer,
updating statistics, defragging the hard drive, using
query optimizer, all with no luck.
Any ideas?
Here's one of the statements that seems to get hung up:
Table AV has 4 columns including a Record ID (RID), a
record name (FID) and a record value (Value). This table
will ultimately have approximately 4 million rows at the
completion of the job.
Table A contains a Record ID (RID) and a Process ID
(PID). This table has approx. 100,000 records.
INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
(ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
(ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
(ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
= ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
= 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID =
ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
276, 277, 278, 279, 280, 281))
I recognize that the same table AV is being joined
multiple times, but it's my understanding that this should
not be a problem.
Thanks for any guidance on this.
Not much to go on but have you looked to see if you are being blocked when
this is happening? How about disk and cpu queues?
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:16c501c4a62e$f11bbd90$a601280a@.phx.gbl...
> I am experiencing inconsistent performance when running
> insert statements.
> Our application automatically generates and sequentially
> executes SQL statements that build a results table. After
> loading data and running this process, all statements
> (about 125 in total) will execute in about 10 minutes. If
> it is necessary to rerun the statements I truncate the
> results table and rerun the job. In some cases this
> second job runs in 10 minutes, and in other cases it runs
> 6 hours. In particular, one statement takes an inordinate
> amount of time.
> I've used PROFILER (although I'm not too experienced in
> this) and can see that when statements are running
> normally the log file updates in the 1000+ per second
> range, but with the problematic statement updates are in
> the 10 per second range. These updates seem to appear
> after the commit statement is executed.
> In various combinations I've tried restarting SQLServer,
> updating statistics, defragging the hard drive, using
> query optimizer, all with no luck.
> Any ideas?
> Here's one of the statements that seems to get hung up:
> Table AV has 4 columns including a Record ID (RID), a
> record name (FID) and a record value (Value). This table
> will ultimately have approximately 4 million rows at the
> completion of the job.
> Table A contains a Record ID (RID) and a Process ID
> (PID). This table has approx. 100,000 records.
> INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
> (ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
> (ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
> (ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
> = ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
> ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
> JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
> = 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
> ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID =
> ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
> 276, 277, 278, 279, 280, 281))
> I recognize that the same table AV is being joined
> multiple times, but it's my understanding that this should
> not be a problem.
> Thanks for any guidance on this.
>
|||Thanks for replying Andrew.
I did forget to mention: when this occurs CPU usage is 100% with minor
disk access every few seconds. There is never any blocking. These jobs
are being run on a dedicated server with a single user.
The odd thing is that the same data and system conditions produce wildly
different results. In between runs with the same data I truncate the
results table. Is there a possibility that the truncate doesn't manage
indexes the same as a delete would?
Chuck
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Not quite sure why sometimes it takes much longer but it may have something
to do with the query plans.. If you are starting with a small or empty
table the query plan will probably be a scan. As you continue to insert the
rows the scans will get longer and longer. Do you have proper indexes on
the table to ensure an efficient plan? If so then you may want to try and
use an Index hint(s) to force seeks rather than scans if necessary.
Normally I don't advocate using hints but since this is a controlled process
it should not be a problem if you know they are the correct thing to do.
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:ueOQ7otpEHA.3244@.tk2msftngp13.phx.gbl...
> Thanks for replying Andrew.
> I did forget to mention: when this occurs CPU usage is 100% with minor
> disk access every few seconds. There is never any blocking. These jobs
> are being run on a dedicated server with a single user.
> The odd thing is that the same data and system conditions produce wildly
> different results. In between runs with the same data I truncate the
> results table. Is there a possibility that the truncate doesn't manage
> indexes the same as a delete would?
> Chuck
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||I've used query optimizer to create indexes as required.
You're right though, the results table starts empty and fills up at
about 50-100K records per each of the 125+ statements that get executed.
Unfortunately, I don't have a lot of control over the way the queries
are built as a 3rd party tool to us does that. I can, however, control
the execution of batches to some degree. Would it make sense to run 1
or 2 smaller batches before the batch that causes the problem?
Also, is it possible that the number of inserts is causing index
fragmentation? (I haven't run DBCC SHOWCONTIG on the results table.)
Would index frag cause 100% CPU spin? BTW, I don't see an appreciable
change in memory usage or paging while this condition exists.
Andrew, thanks for all of your help.
Chuck
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||What does the actual DDL for the insert table look like, especially the
Clustered index?
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:%23teT4expEHA.3712@.TK2MSFTNGP15.phx.gbl...
> I've used query optimizer to create indexes as required.
> You're right though, the results table starts empty and fills up at
> about 50-100K records per each of the 125+ statements that get executed.
> Unfortunately, I don't have a lot of control over the way the queries
> are built as a 3rd party tool to us does that. I can, however, control
> the execution of batches to some degree. Would it make sense to run 1
> or 2 smaller batches before the batch that causes the problem?
> Also, is it possible that the number of inserts is causing index
> fragmentation? (I haven't run DBCC SHOWCONTIG on the results table.)
> Would index frag cause 100% CPU spin? BTW, I don't see an appreciable
> change in memory usage or paging while this condition exists.
> Andrew, thanks for all of your help.
> Chuck
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi Andrew,
I hope this is what you're looking for. I'd ask your guidance on the
fill factors, along with any other insights. Because each of the 125
SQL statements are inserting, (and I assume but am not sure) that not
all inserts are being placed at the end of the table perhaps the fill
factors should be decreased?
Thanks,
Chuck
/****** Object: Table [dbo].[AccountValues] Script Date: 10/1/2004
9:56:09 AM ******/
CREATE TABLE [dbo].[AccountValues] (
[AccountID] [int] NOT NULL ,
[IFormulaID] [int] NOT NULL ,
[FormulaValue] [decimal](30, 10) NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [AccountValues1] ON
[dbo].[AccountValues]([AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
CREATE INDEX [AccountValues3] ON [dbo].[AccountValues]([IFormulaID],
[AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Andrew,
Two more things:
. The problem I'm experienceing is described almost exactly in MS
KB835864, except that I'm running a single CPU.
. I have noticed that the performance statistic Available Mbytes starts
high after a system reboot, but as I execute procedures or ad-hoc
queries this slowly decreases to 5-10Mb. Is there a way to make SQL
Server free up memory without stopping and restarting the server?
Thanks,
Chuck
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Sounds like you don't have a whole lot of memory to begin with. How much do
you have and are there other processes running on the server other than SQL
Server? You can use the MAX Memory setting to ensure the OS and other apps
always have some memory available.
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:e0FxYv%23pEHA.3396@.tk2msftngp13.phx.gbl...
> Hi Andrew,
> Two more things:
> The problem I'm experienceing is described almost exactly in MS
> KB835864, except that I'm running a single CPU.
> I have noticed that the performance statistic Available Mbytes starts
> high after a system reboot, but as I execute procedures or ad-hoc
> queries this slowly decreases to 5-10Mb. Is there a way to make SQL
> Server free up memory without stopping and restarting the server?
> Thanks,
> Chuck
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Chuck,
Not sure if I understand exactly what you are trying to do with that query.
It seems as if you can get the desired result like this:
SELECT A.RID, 325, SUM(A.Value) AS Value
FROM A
WHERE A.PID IN (275, 276, 277, 278, 279, 280, 281)
AND a.FID IN (496,497,499,502,504
GROUP BY a.RID
In any case you need to determine what the query plan is that this is using
during the slow periods. You can run a trace that will show recompiles and
query plans to see if they change during the operation. You can also try
specifying hints to force the proper plans.
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:eSP09s9pEHA.2696@.TK2MSFTNGP15.phx.gbl...
> Hi Andrew,
> I hope this is what you're looking for. I'd ask your guidance on the
> fill factors, along with any other insights. Because each of the 125
> SQL statements are inserting, (and I assume but am not sure) that not
> all inserts are being placed at the end of the table perhaps the fill
> factors should be decreased?
> Thanks,
> Chuck
> /****** Object: Table [dbo].[AccountValues] Script Date: 10/1/2004
> 9:56:09 AM ******/
> CREATE TABLE [dbo].[AccountValues] (
> [AccountID] [int] NOT NULL ,
> [IFormulaID] [int] NOT NULL ,
> [FormulaValue] [decimal](30, 10) NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [AccountValues1] ON
> [dbo].[AccountValues]([AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> CREATE INDEX [AccountValues3] ON [dbo].[AccountValues]([IFormulaID],
> [AccountID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Friday, March 9, 2012

Inconistent time out error during update operation.

Using our ASP.net application we are getting inconsistent results when we are trying to update a table with more than 15 update queries sequentially.

We are using Merge replication in our SQL server database. On updating one row, it invokes a trigger to update another table and one more trigger for the merge replication. At only few
instances, the update operation fails and it reports "Time out expired" error.

We are not able to simulate this error in the Test environment since it does not have replication. Can any one help me in resolving this issue.

Please advise .

Hi Krithi,

Is this SQL 2000 or SQL 2005?
Would the updates be touch a lot of rows? If so, I can understand the timeout because, it seems like there are multiple triggers here and also replication adds its own triggers on the tables. That bing the case, if the number of rows touched are large, timeouts can occur.

And are you doing the update operations through isqlw/Management Studio?
If so, can you increase the timeout value in the options in isqlw/Management Studio and try again?

Wednesday, March 7, 2012

Including Sql Express DB with Winforms Application

We are thinking of using a Sql Server Express database (~~.mdb~~.mdf) as a data resource in a Windows Forms application that we will be distributing to clients. When the application is built, the mdb file is copied to the bin directory.

In order for clients to access this database, will they need to have an instance of the SQLEXPRESS server installed on their computer? Or since the ~~.mdb~~.mdf file is being accessed from a .Net 2.0 application, there is no need to install a separate db component?

If I do have to separately install the db component on the client end,
is this the way to go about it? This? Or is there a different/better way?

If your file really is .mdb, this is an Access file, and your program will connect to it with out any trouble.

SQL Express files are .mdf

For SQL Express, just drop your data file into your resources directory, and it will be published with your project. (This works with any file you want to include in your project, like graphic files used in your reports, etc.)

Set your Build Action to "Content" and make sure you select "Copy if newer", and not "Copy Always" as this will erase any data you have everytime you test your program.

- Richard.

|||

When you do distribute you can package up the SQL Engine (SQL Express) into the installer... but yes the clients do need the engine installed on there system. Now depending on how you are access the database you have several options. With express you have two ways of connecting to the database, the first is to just install the engine and attach the files to that engine, then you can connect to the database from the client as well as from other clients.... ie remote clients (After turning on the remote Access) using the one database server. The other option is to use a User Instance of the database. With this method the database is attached to the engine by the SQL Native client at runtime. This means that when you start the application that you have developed, the SQL Native client connects using the Connection string you provide (With the Attach option) and attaches the DB.... when the app is finished with it it will detatch the database. One problem with this is that generally it is set to single user and only the security context of the application running the App will be able to access the database.

So in summary if only the client needs to connect to the DB at the one time the best option would be to dist the sql engine and client app seperatly and allow the sql native client to do it's job and attach the db.... but if more then one client needs to access the same database at the one time You should attach the db your self and use the standard client server setup.

Hope this helps..

|||Richard - Thanks, you are right. I meant to say "mdf"|||Thanks Glenn.

In my situation, we will have an instance of SQL Express installed on the client machine, with only that one client connecting to the database. So you think that I should connect to the DB with a connection string that attaches the DB, and when the APP is finished it will detach the DB? What is the format of such a string. Right now I am using a connection string of the following format:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db.mdf;Database=DBName;Integrated Security=False;User Instance=False;User ID=USERNAME;Password=PASSWORD;Connect Timeout=30;

As I understand it, this connection string will attach the DB to the database with the name provided in the "Database" parameter. In the future, the DB will already be attached to the server under that name. How should the connection string be modified so that when the APP is finished it will detach the DB? Will this happen when the application closes? Or does this happen before and after every single database interaction (thus, while the application is open, you could be attaching and detaching the database thousands of times)? If so, what kind of extra overhead does this constant attaching and detaching cause?

Additionally, we would like to prevent any access to the database unless it is with the Username and Password (ie: we do not want them to access the database directly, rather through the application that we provide. In the application itself, the connection string will be encrypted). Is there a way to prevent the user from connecting to the database through the SA account or the Admin account for the computer, to ensure that all connections are done only using the username and password?

Friday, March 30, 2012

Increase Performance with two connections

increase in CPU usage on lock and unlock of the system

Friday, March 23, 2012

Incorrect syntax near '?'

Incorrect syntax near ''?''

incorrect syntax after server up for a few days

Wednesday, March 21, 2012

Incorrect SET options all of a sudden?

Monday, March 12, 2012

Inconsistent stored procedure syntax error

Inconsistent Query Performance

Inconsistent Query Performance

Inconsistent Performance of Insert

Inconsistent Performance of Insert

Friday, March 9, 2012

Inconistent time out error during update operation.

Wednesday, March 7, 2012

Including Sql Express DB with Winforms Application

sqlserver2000 date

Blog Archive

About Me