Footer rows to skip: application

Showing posts with label application. Show all posts

Tuesday, March 27, 2012

Field Definition report results no record

Hi guys, sorry my poor english,
I'm trying to create a simple example of a report that receive the recordset from my VB6 application. The recordset have only one field like the rpt and the ttx file.

My project use these references:
Microsoft Remote Data Object 2.0
Crystal Reports ActiveX Designer Run Time Library 10.0
Crystal Report Viewer Control

To reproduce the example in VB, create a new project, include the references, create a command button and a CRViewer and after paste the code inside the form.

--- Code ---
Option Explicit
Dim cnFin As RDO.rdoConnection
Dim Crystal As CRAXDRT.Application

Private Sub Command1_Click()
Dim rsFin As RDO.rdoResultset
Dim rpFin As Report

'Opening table
Set rsFin = cnFin.OpenResultset( _
"SELECT ALL FinCPSequencia " & _
"FROM FinMovtoPagar " & _
"WHERE CodEstab = 5", RDO.rdOpenKeyset, RDO.rdConcurRowVer)
If rsFin.RowCount > 0 Then
'Opens report
Set rpFin = Crystal.OpenReport(App.Path & "\Test.rpt")
'Defines parameters
rpFin.ParameterFields.Item(1).AddCurrentValue "Title"
'Set DataSoruce
rpFin.Database.SetDataSource rsFin
'Shows report
With CRViewer1
.ReportSource = rpFin
.ViewReport
End With
Else
MsgBox "No record"
End If
End Sub
Private Sub Form_Load()
'Connecting with database
Set cnFin = RDO.rdoEnvironments(0).OpenConnection("", , , _
"DRIVER=SQL Server;SERVER=127.0.0.1;UID=BNP;PWD=password;APP=BNPGest;WSID=NBNIETTO;DATABASE=BNPGEST_DESENV;LANGUAGE=us_english;Network=DBMSSOCN;Address=127.0.0.1,1433")
'Initiating Crystal
Set Crystal = New CRAXDRT.Application
End SubThe same example using ADO works fine, but I have to keep the project with RDO.
I found this article: http://support.businessobjects.com/library/kbase/articles/c2016624.asp?ref=devzone_xiresources_tipsandtricks , But the report yet results no records even using the batchclient. Does anyone can help me?

Friday, March 23, 2012

Feature Request: 2005 Beta 2

Greetings,
I have a feature request for the Enterprise Studio.
We have about 500+ stored procedures in our CRM application and opening up
Enterprise Manager (in 2000) or Enterprise Studio (2005) I have to look at a
huge list of stored procedures and find the one I'm specifically looking
for. Granted, in 2005 there is a "filter" it is rather unintuitive.
I'd like to request the abililty to add my own "folder" to the list where I
can create categories to group my stored procedures. for example, I can
create a "Invoices" group where all my invoice related SP's go, and a
"Policies" group where all my policies related SP's will go. In addition to
this, we have more tables than SPs, so we should be able to group any
object, Table, SP, View, etc.
Not sure if you've considered this already but the filtering isn't as
intuitive. It requires me to have to constantly enter in new filters to
filter the list before I can browse around. I'd like to browse directly to
what I'm looking for.
Thanks,
Shawn
Hi Shawn,
Have you considered using Schemas for this? (Introduced in SQL Server 2005
also).
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Shawn B." <leabre@.html.com> wrote in message
news:OLcOBc6zEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Greetings,
> I have a feature request for the Enterprise Studio.
> We have about 500+ stored procedures in our CRM application and opening up
> Enterprise Manager (in 2000) or Enterprise Studio (2005) I have to look at
> a
> huge list of stored procedures and find the one I'm specifically looking
> for. Granted, in 2005 there is a "filter" it is rather unintuitive.
> I'd like to request the abililty to add my own "folder" to the list where
> I
> can create categories to group my stored procedures. for example, I can
> create a "Invoices" group where all my invoice related SP's go, and a
> "Policies" group where all my policies related SP's will go. In addition
> to
> this, we have more tables than SPs, so we should be able to group any
> object, Table, SP, View, etc.
> Not sure if you've considered this already but the filtering isn't as
> intuitive. It requires me to have to constantly enter in new filters to
> filter the list before I can browse around. I'd like to browse directly
> to
> what I'm looking for.
>
> Thanks,
> Shawn
>
|||What are schemas in this context? after a cursory google and help topics
lookup, I have no clue what they can do for me.
Thanks,
Shawn
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:e8NiNJ8zEHA.2528@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi Shawn,
> Have you considered using Schemas for this? (Introduced in SQL Server 2005
> also).
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Shawn B." <leabre@.html.com> wrote in message
> news:OLcOBc6zEHA.1564@.TK2MSFTNGP09.phx.gbl...
up[vbcol=seagreen]
at[vbcol=seagreen]
where[vbcol=seagreen]
addition
>
|||I believe this has been suggested before but to make sure please send the
details of your request to sqlwish@.microsoft.com
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Shawn B." <leabre@.html.com> wrote in message
news:OLcOBc6zEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Greetings,
> I have a feature request for the Enterprise Studio.
> We have about 500+ stored procedures in our CRM application and opening up
> Enterprise Manager (in 2000) or Enterprise Studio (2005) I have to look at
> a
> huge list of stored procedures and find the one I'm specifically looking
> for. Granted, in 2005 there is a "filter" it is rather unintuitive.
> I'd like to request the abililty to add my own "folder" to the list where
> I
> can create categories to group my stored procedures. for example, I can
> create a "Invoices" group where all my invoice related SP's go, and a
> "Policies" group where all my policies related SP's will go. In addition
> to
> this, we have more tables than SPs, so we should be able to group any
> object, Table, SP, View, etc.
> Not sure if you've considered this already but the filtering isn't as
> intuitive. It requires me to have to constantly enter in new filters to
> filter the list before I can browse around. I'd like to browse directly
> to
> what I'm looking for.
>
> Thanks,
> Shawn
>
|||Hi Shawn,
Take a look at the Northwind sample database in SQL Server 2005 Beta if you
have access to it from the MSDN site. Whereas Northwind's tables, procs,
etc. before were just one big list, now there are separate schemas for
HumanResources, etc. with only those objects relevant to that part of the
app in them.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Shawn B." <leabre@.html.com> wrote in message
news:OWVSG5A0EHA.1192@.tk2msftngp13.phx.gbl...
> What are schemas in this context? after a cursory google and help topics
> lookup, I have no clue what they can do for me.
>
> Thanks,
> Shawn
>
> "Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
> news:e8NiNJ8zEHA.2528@.TK2MSFTNGP10.phx.gbl...
> up
> at
> where
> addition
>
|||Sorry, brain explosion - meant to point you to the Adventureworks sample,
not Northwind.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:OLy2UoU0EHA.3244@.TK2MSFTNGP10.phx.gbl...
> Hi Shawn,
> Take a look at the Northwind sample database in SQL Server 2005 Beta if
> you have access to it from the MSDN site. Whereas Northwind's tables,
> procs, etc. before were just one big list, now there are separate schemas
> for HumanResources, etc. with only those objects relevant to that part of
> the app in them.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Shawn B." <leabre@.html.com> wrote in message
> news:OWVSG5A0EHA.1192@.tk2msftngp13.phx.gbl...
>

Wednesday, March 21, 2012

FDA Compliance and SQL 2000

We have an application using SQL 2000 database that must be FDA compliant. Anyone with FDA compliance experience? Know any products that will audit database changes? How about validation of backups and restores.

Thanks!

I beleive that Quest Software has products to audit schema changes to the database.

SQL Server can validate backups nativly. Quest may have a third party production which can do this as well.

|||For auditing, Idera has a really good product called compliance manager that will enable you to track pretty much whatever you need to track. If you are only tracking data changes, you can always write you own custom data auditing solution without too much grief. Hope this helps.
Tim|||

Thanks for all your help. This gets me going in the right direction.

GDM

faulting module

Hello everyone! This is my first post so please bear with me. We have
recently re-vamped an Access application such that in each of our 5
locations
a local SQL server receives published updates from our main office server.
Remote servers also send their local updates to the master and they are in
turn propogated to all of the other remote servers. The new design works
great except for one annoying bug we have been chasing for two weeks:
"Faulting application msaccess.exe, version 9.0.0.2719, faulting module
mso9.dll, version 9.0.0.2720, fault address 0x0007ee2d." When a user gets
the
error, they hit close and it terminates Access. If they move the error off
the screen and continue working the application continues. They have
discovered this and have been doing so but the fact that the error appears
must be fixed. If anyone can point me in the right direction it would be
muchly appreciated. Thanks in advance!

- Mark
mweber_at_warrenlumber_dot_comMark A. Weber (mweber@.warrenlumber.com) writes:
> Hello everyone! This is my first post so please bear with me. We have
> recently re-vamped an Access application such that in each of our 5
> locations
> a local SQL server receives published updates from our main office server.
> Remote servers also send their local updates to the master and they are in
> turn propogated to all of the other remote servers. The new design works
> great except for one annoying bug we have been chasing for two weeks:
> "Faulting application msaccess.exe, version 9.0.0.2719, faulting module
> mso9.dll, version 9.0.0.2720, fault address 0x0007ee2d." When a user gets
> the
> error, they hit close and it terminates Access. If they move the error off
> the screen and continue working the application continues. They have
> discovered this and have been doing so but the fact that the error appears
> must be fixed. If anyone can point me in the right direction it would be
> muchly appreciated. Thanks in advance!

The one pointer I can give you is to post in comp.databases.ms-access
instead. This group is about SQL Server, and I can't see any relation to
SQL Server in your post.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Fatal exception while firing a rule

Hi,

I am having a problem when moving our NS application from dev environment to testing environment. What happens, is that when the rule is fired, a fatal exception is generated.

Any hints how I can start hunting down what the source of the problem is. The thing is, this works in dev environment, but not in test. Further, if I try to execute the stored proc manually which fails (NS_MyAppApplication].[NSFire3]) I get the same error: A user request from the session with SPID 63 generated a fatal exception. SQL Server is terminating this session.

Both dev and test environments use same ADF and ICF files. The ADF file is pretty much like in the InventoryTracker sample, using ConditionAction element like this:

<ConditionAction>
<SqlLogin>NSRulesEvaluator</SqlLogin>
<SqlUser>NSRulesEvaluator</SqlUser>
<InputName>InventoryTrackerEvents</InputName>
<InputSchema>NS_InventoryTrackerApplication</InputSchema>
<SqlExpression>

INSERT INTO [NS_InventoryTrackerApplication].[InventoryTrackerNotifications]
(
[SubscriberId],
[DeviceName],
[SubscriberLocale],
[ProductId],
[ProductName],
[ProductNumber],
[Quantity],
[LocationName]
)

(
SELECT
r.[Subscription.SubscriberId],
r.[Subscription.DeviceName],
r.[Subscription.SubscriberLocale],
r.[Input.ProductId],
r.[Input.ProductName],
r.[Input.ProductNumber],
r.[Input.Quantity],
r.[Input.LocationName]

FROM [NS_InventoryTrackerApplication].InventoryTrackerRule AS r
)
</SqlExpression>
</ConditionAction>

I tried changing ADF to little simple version, using action element instead of conditionaction. This time I got it working (and before that normal error messages and not just a stack dump).

Any hints how I can find out what is the problem here, or am I possibly missing something totally?

//sami

Both servers are as below:

Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

These are entries from application log (in reverse order):

--

Description: The running of the rule failed. Please check the rule.
EventParameters:
Application Name: MyApp
Quantum ID: 4513
Rule Firing ID: 17
Rule Name: MyAppRule
Notification Throttle: 1000
Event Class ID: 1
Subscription Class ID: 1
Rule ID: 3
Rule Action: INSERT INTO [NS_MyAppApplication].[MyAppNotifications]
(
[SubscriberId],
[DeviceName],
[SubscriberLocale],
[SomeData]
)

(
SELECT
r.[Subscription.SubscriberId],
r.[Subscription.DeviceName],
r.[Subscription.SubscriberLocale],
r.[Input.SomeData]
FROM [NS_MyAppApplication].MyAppRule AS r

)
Stored Procedure Name: [NS_MyAppApplication].[NSFire3]

SqlServerError:
Error Number: 0
Source: .Net SqlClient Data Provider
Number: 0
State: 0
Class: 11
Message: A severe error occurred on the current command. The results, if any, should be discarded.
Procedure:
Line Number: 0

Error Number: 1
Source: .Net SqlClient Data Provider
Number: 0
State: 0
Class: 20
Message: A severe error occurred on the current command. The results, if any, should be discarded.
Procedure:
Line Number: 0

Description: A severe error occurred on the current command. The results, if any, should be discarded.
A severe error occurred on the current command. The results, if any, should be discarded.
InstanceName: MyAppInstance
ApplicationName: MyApp
Component: Generator
Thread: 4

--

Notification Services performed a quantum status update.
EventParameters:
Application Name: MyApp
Quantum ID: 4513
Status Code: 128
Quantum Status Code: 135

InstanceName: MyAppInstance
ApplicationName: MyApp
Component: Generator
Thread: 4

--

A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session.

--

The generator has started firing a rule.
EventParameters:
Application Name: MyApp
Quantum ID: 4513
Rule Firing ID: 17
Rule Name: MyAppRule
Notification Throttle: 1000

InstanceName: MyAppInstance
ApplicationName: MyApp
Component: Generator
Thread: 4

I have been banging my head with this one. I tried creating the NS app from scracth. I managed to get it working again with <Action> element, using STMP notifications and XsltFormatter. After that I modified to solution, taking example from Shyams books userdefinedlogic sample. Got everything up and running, but when rule starts firing same thing happens again.

After that, I disabled the generator and did the debugging procedures. All went fine up to
NPPrepareRuleFiring. When I executed NSExecuteRuleFiring, bang! Fatal exception.. no additional information, sigh.

What I was wondering was that in the preparerulefiring resultset I got EventBatchId as null. Is this correct in this scenario?|||Hi,
have the same problem you have... did you find an answer?

Thanks

Ricardo Carvalho|||Have not had any luck with this. As a workaround I am using <action> element instead of <conditionaction>. I don't like it, but at the moment I have no other solution.

Fatal exception while firing a rule

Hi,

I am having a problem when moving our NS application from dev environment to testing environment. What happens, is that when the rule is fired, a fatal exception is generated.

Any hints how I can start hunting down what the source of the problem is. The thing is, this works in dev environment, but not in test. Further, if I try to execute the stored proc manually which fails (NS_MyAppApplication].[NSFire3]) I get the same error:A user request from the session with SPID 63 generated a fatal exception. SQL Server is terminating this session.

Both dev and test environments use same ADF and ICF files. The ADF file is pretty much like in the InventoryTracker sample, using ConditionAction element like this:

FROM [NS_InventoryTrackerApplication].InventoryTrackerRule AS r
)
</SqlExpression>
</ConditionAction>

I tried changing ADF to little simple version, using action element instead of conditionaction. This time I got it working (and before that normal error messages and not just a stack dump).

Any hints how I can find out what is the problem here, or am I possibly missing something totally?

//sami

Both servers are as below:

These are entries from application log (in reverse order):

What I was wondering was that in the preparerulefiring resultset I got EventBatchId as null. Is this correct in this scenario?|||Hi,
have the same problem you have... did you find an answer?

Thanks

Ricardo Carvalho
|||Have not had any luck with this. As a workaround I am using <action> element instead of <conditionaction>. I don't like it, but at the moment I have no other solution.

Fatal exception while firing a rule

Hi,

I am having a problem when moving our NS application from dev environment to testing environment. What happens, is that when the rule is fired, a fatal exception is generated.

Both dev and test environments use same ADF and ICF files. The ADF file is pretty much like in the InventoryTracker sample, using ConditionAction element like this:

FROM [NS_InventoryTrackerApplication].InventoryTrackerRule AS r
)
</SqlExpression>
</ConditionAction>

I tried changing ADF to little simple version, using action element instead of conditionaction. This time I got it working (and before that normal error messages and not just a stack dump).

Any hints how I can find out what is the problem here, or am I possibly missing something totally?

//sami

Both servers are as below:

These are entries from application log (in reverse order):

What I was wondering was that in the preparerulefiring resultset I got EventBatchId as null. Is this correct in this scenario?|||Hi,
have the same problem you have... did you find an answer?

Thanks

Ricardo Carvalho|||Have not had any luck with this. As a workaround I am using <action> element instead of <conditionaction>. I don't like it, but at the moment I have no other solution.

Monday, March 19, 2012

Fatal Exception error

I have an application that has to work in a merge replicated environment. To
facilitate this we use @.@.Servername to put the name of the server that the
data is changed onto the table. I have an issue now with this problem
http://support.microsoft.com/defaul...B;en-us;270061&
and am wondering when it will be resolved if there is a better workaround; o
r a solution besides dropping the server and reinstalling it ?
Thanks
ChrisHi Chris:
According the KB article you provide, there is no need for reinstall,
what we should do is follow the workaround.
Is there a misunderstanding by me?
Best Wishes
Wei Ci Zhou|||Hi Chris,
Thank you for using the newsgroup and it is my pleasure to answer you
question.
From the knowledge base article
http://support.microsoft.com/defaul...B;en-us;270061&
There is a workaround to avoid it by:
exec sp_addserver 'WindowsNT_Machine_Name', 'local'
I will notify you as soon as possible to you any information about the fix
on this problem. If you still have questions about it, please feel free to
post message here and I am glad to help you. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Wei,
Is there a timescale on the hotfix : i have 20 servers that i have to go thr
ough change control to try the fix : i dont have the issue with my dev / tes
t servers so am finding it hard to resolve.
Also i am interested to know what might cause the issue to have occured in t
eh first place !
many thanks|||Hi Chris,
Thank you for using the newsgroup and it is my pleasure to answer you
question.
The product group is aware of this issue. I cannot guarentee it.
Thanks!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Fatal Exception error

I have an application that has to work in a merge replicated environment. To facilitate this we use @.@.Servername to put the name of the server that the data is changed onto the table. I have an issue now with this problem
http://support.microsoft.com/default.aspx?scid=KB;en-us;270061&
and am wondering when it will be resolved if there is a better workaround; or a solution besides dropping the server and reinstalling it ?
Thanks
ChrisHi Chris:
According the KB article you provide, there is no need for reinstall,
what we should do is follow the workaround.
Is there a misunderstanding by me?
Best Wishes
Wei Ci Zhou|||Hi Chris,
Thank you for using the newsgroup and it is my pleasure to answer you
question.
From the knowledge base article
http://support.microsoft.com/default.aspx?scid=KB;en-us;270061&
There is a workaround to avoid it by:
exec sp_addserver 'WindowsNT_Machine_Name', 'local'
I will notify you as soon as possible to you any information about the fix
on this problem. If you still have questions about it, please feel free to
post message here and I am glad to help you. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Wei
Is there a timescale on the hotfix : i have 20 servers that i have to go through change control to try the fix : i dont have the issue with my dev / test servers so am finding it hard to resolve
Also i am interested to know what might cause the issue to have occured in teh first place
many thanks|||Hi Chris,
Thank you for using the newsgroup and it is my pleasure to answer you
question.
The product group is aware of this issue. I cannot guarentee it.
Thanks!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

fatal exception c0000005 EXCEPTION_ACCESS_VIOLAT

Hi
We are running SQL Server 2000 sp3 on a W2k3 Dell server.
Application has been running for years. In the middle of the morning
(today) we started getting an error at the workstation. At the server, we
see the following two errors in the application event viewer:
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
and
Error: 0, Severity: 19, State: 0
language_exec: Process 55 generated an access violation. SQL Server is
terminating this process.
Anyone have an idea as to what might be going on?
Virus scan is clean and we weren't doing anything to the server at the time
the errors started appearing.
Thanks
Jim
Call PSS and they will help you. The call should be free as AVs are usually
bugs in SQL Server.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Pattison" <Jim Pattison@.discussions.microsoft.com> wrote in message
news:8F6414A7-2EE9-4C8B-945E-BCD913331C5F@.microsoft.com...
> Hi
> We are running SQL Server 2000 sp3 on a W2k3 Dell server.
> Application has been running for years. In the middle of the morning
> (today) we started getting an error at the workstation. At the server, we
> see the following two errors in the application event viewer:
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> and
> Error: 0, Severity: 19, State: 0
> language_exec: Process 55 generated an access violation. SQL Server is
> terminating this process.
> Anyone have an idea as to what might be going on?
> Virus scan is clean and we weren't doing anything to the server at the
> time
> the errors started appearing.
> Thanks
> Jim
|||Jim,
Have you looked into the SQL Server error log to see if you have a 'dump' in
the error log. Last week I had an issue very similar after installing 3rd
party performance tools for evaluation. Nothing was installed on the server,
but the application was attempting to get performance information from the
server and was causing an Access Violation in SQL Server. I found more
information in the error log to search the web with. My solution was to
uninstall the 3rd party software, co that calls to the performance counters
would stop.
"Jim Pattison" wrote:

> Hi
> We are running SQL Server 2000 sp3 on a W2k3 Dell server.
> Application has been running for years. In the middle of the morning
> (today) we started getting an error at the workstation. At the server, we
> see the following two errors in the application event viewer:
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> and
> Error: 0, Severity: 19, State: 0
> language_exec: Process 55 generated an access violation. SQL Server is
> terminating this process.
> Anyone have an idea as to what might be going on?
> Virus scan is clean and we weren't doing anything to the server at the time
> the errors started appearing.
> Thanks
> Jim

fatal exception c0000005 EXCEPTION_ACCESS_VIOLAT

Hi
We are running SQL Server 2000 sp3 on a W2k3 Dell server.
Application has been running for years. In the middle of the morning
(today) we started getting an error at the workstation. At the server, we
see the following two errors in the application event viewer:
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
and
Error: 0, Severity: 19, State: 0
language_exec: Process 55 generated an access violation. SQL Server is
terminating this process.
Anyone have an idea as to what might be going on?
Virus scan is clean and we weren't doing anything to the server at the time
the errors started appearing.
Thanks
JimCall PSS and they will help you. The call should be free as AVs are usually
bugs in SQL Server.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Pattison" <Jim Pattison@.discussions.microsoft.com> wrote in message
news:8F6414A7-2EE9-4C8B-945E-BCD913331C5F@.microsoft.com...
> Hi
> We are running SQL Server 2000 sp3 on a W2k3 Dell server.
> Application has been running for years. In the middle of the morning
> (today) we started getting an error at the workstation. At the server, we
> see the following two errors in the application event viewer:
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> and
> Error: 0, Severity: 19, State: 0
> language_exec: Process 55 generated an access violation. SQL Server is
> terminating this process.
> Anyone have an idea as to what might be going on?
> Virus scan is clean and we weren't doing anything to the server at the
> time
> the errors started appearing.
> Thanks
> Jim|||Jim,
Have you looked into the SQL Server error log to see if you have a 'dump' in
the error log. Last week I had an issue very similar after installing 3rd
party performance tools for evaluation. Nothing was installed on the server
,
but the application was attempting to get performance information from the
server and was causing an Access Violation in SQL Server. I found more
information in the error log to search the web with. My solution was to
uninstall the 3rd party software, co that calls to the performance counters
would stop.
"Jim Pattison" wrote:

> Hi
> We are running SQL Server 2000 sp3 on a W2k3 Dell server.
> Application has been running for years. In the middle of the morning
> (today) we started getting an error at the workstation. At the server, we
> see the following two errors in the application event viewer:
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> and
> Error: 0, Severity: 19, State: 0
> language_exec: Process 55 generated an access violation. SQL Server is
> terminating this process.
> Anyone have an idea as to what might be going on?
> Virus scan is clean and we weren't doing anything to the server at the tim
e
> the errors started appearing.
> Thanks
> Jim

fatal exception c0000005 EXCEPTION_ACCESS_VIOLAT

Hi
We are running SQL Server 2000 sp3 on a W2k3 Dell server.
Application has been running for years. In the middle of the morning
(today) we started getting an error at the workstation. At the server, we
see the following two errors in the application event viewer:
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
and
Error: 0, Severity: 19, State: 0
language_exec: Process 55 generated an access violation. SQL Server is
terminating this process.
Anyone have an idea as to what might be going on?
Virus scan is clean and we weren't doing anything to the server at the time
the errors started appearing.
Thanks
JimCall PSS and they will help you. The call should be free as AVs are usually
bugs in SQL Server.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Pattison" <Jim Pattison@.discussions.microsoft.com> wrote in message
news:8F6414A7-2EE9-4C8B-945E-BCD913331C5F@.microsoft.com...
> Hi
> We are running SQL Server 2000 sp3 on a W2k3 Dell server.
> Application has been running for years. In the middle of the morning
> (today) we started getting an error at the workstation. At the server, we
> see the following two errors in the application event viewer:
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> and
> Error: 0, Severity: 19, State: 0
> language_exec: Process 55 generated an access violation. SQL Server is
> terminating this process.
> Anyone have an idea as to what might be going on?
> Virus scan is clean and we weren't doing anything to the server at the
> time
> the errors started appearing.
> Thanks
> Jim|||Jim,
Have you looked into the SQL Server error log to see if you have a 'dump' in
the error log. Last week I had an issue very similar after installing 3rd
party performance tools for evaluation. Nothing was installed on the server,
but the application was attempting to get performance information from the
server and was causing an Access Violation in SQL Server. I found more
information in the error log to search the web with. My solution was to
uninstall the 3rd party software, co that calls to the performance counters
would stop.
"Jim Pattison" wrote:
> Hi
> We are running SQL Server 2000 sp3 on a W2k3 Dell server.
> Application has been running for years. In the middle of the morning
> (today) we started getting an error at the workstation. At the server, we
> see the following two errors in the application event viewer:
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> and
> Error: 0, Severity: 19, State: 0
> language_exec: Process 55 generated an access violation. SQL Server is
> terminating this process.
> Anyone have an idea as to what might be going on?
> Virus scan is clean and we weren't doing anything to the server at the time
> the errors started appearing.
> Thanks
> Jim

Friday, March 9, 2012

Faster SQL query

Hi!
I M basically an application developer & use simple sql queries in my programmings. I do not have much idea abt tuning/auditing part & thatswhy i m unable to answer them properly in my interviews. Can anybody give me some tips???

Question 1:
In a stored procedure, One SELECT stmt is there & depending upon the @.rowcount, it updates around 14000 records which is also written inside this stored procedure. Instead of writing this way, there is some other way which is faster than this. Can anybody tell me the correct way?

Question 2:Can anybody give me few examples like this?? I need them desparetly.

Thanx. Bye.::I M basically an application developer & use simple sql queries in my programmings. I do
::not have much idea abt tuning/auditing part & thatswhy i m unable to answer them
::properly in my interviews. Can anybody give me some tips???

Get a good beginner book into SQL, like "SQL for Dummies" (no pun intended, the dummies books are nowmally very nice).

::In a stored procedure, One SELECT stmt is there & depending upon the @.rowcount, it
::updates around 14000 records which is also written inside this stored procedure. Instead of
::writing this way, there is some other way which is faster than this. Can anybody tell me the
::correct way?

No. Because you do not give enough information to advice on a better way.

::Question 2:Can anybody give me few examples like this?? I need them desparetly.

I really suggest some introductory books about how SQL works. Simple queries do not cut it.|||Let me give the skeleton of the store proc:

select * from tab1
if @.rowcount>1
update table2 set col1="asadsasasa"
end if

That person was asking me "by updating 40000 records in this manner, it will be quite slow". So, what is the faster way?

thanx for ur advice... i w'll go thru one such book.

Bye.|||Another rubbish question, the original question that is. What does "updating 40K records" mean? Are they saying you'd call this proc 40K times? I assume they're talking about an update with a join, but the question is just awful. The proc shown here would always just update the same thing so an update join would be no better? Madness!|||::That person was asking me "by updating 40000 records in this manner, it will be quite
::slow". So, what is the faster way?

The issue is the

::select * from tab1

which retrieves the rows.

a better way is along the lines

select count(*) from tab1 (hm, how do syou select into a variable- i Havenot done sp's for more than a year)
if (variable) > 1

the select count(*) does not retrieve the values.

You should really get:

* SQL for dummies

THEN

SQL for Smarties from Joe Celko|||Well done thona for translating the question! If you're right then you'd be better of with an "exists"

If exists(select * from tab1)
update table2 set col1="asadsasasa"

Although I still think the wording of "update 40000..." is mighty odd.|||::If you're right then you'd be better of with an "exists"

Yes, I realized this, too, once it was posted.

Bout someone else was right - the original question is not formulated good.|||Thanx everybody & extremely sorry for these type of questions. But hope u all understand my problem. Actually all these questions are asked by somebodyelse. So, thanx for ur cooperation.

Yes, Exists & Count are better option for the "SELECT" stmt. But he was stressing more on the "UPDATE" stmt which is updating 14K records after getting the @.ROWCOUNT.

SO KINDLY COOPERATE AGAIN.

THNX EVERYBODY.|||If you really want to change the same col in 40K records there really isn't much else you can do. Perhaps turn off the indexing and put it back at the end or something but I think that's reaching a bit. If that represented the entire table then I 'spose you could argue that truncating the table and then inserting might be a tad quicker but again that sounds a mighty complicated answer for such a strange question. Plus you'd get into trouble if you wanted to rollback. Are you sure there isn't something missing from the original question?|||Hi PKR!
thnx for ur cooperation. There is no other clue for u/me. He was only stressing on that point only.(i.e. updating 14k records in this manner will take lot of time).

Do u have any idea abt the followings:
1. Is there any possibility to fire a trigger for updation?
2. Is a nested stored proc will be a better option?

Thanx again.

Waiting for reply...|||I already said once, I repeast it now:

get an introducotry book into SQL. Most of your questions are simply a result of not really having a deep understanding of the language. You need to get into set oriented mood to achieve anything in SQL in a fast way.

::1. Is there any possibility to fire a trigger for updation?

Did you look at the documentation of the CRATE TRIGGER statement? It would answer this question in less time than posting.

::2. Is a nested stored proc will be a better option?

This sentence makes no sense grammar wise, sadly.

Nested SP's should be used when necessary. Calling a nested SP instead of making a set operation is a beginner error, though - a proper SQL Server (count oracle out of this for certain things) works SET oriented. Replacing a set command with a nested SP called in a loop is NOT a good way to achieve something.|||Hi!
Sorry ... & thanx for ur valuable advice. I'll do as u said.

Bye|||Hello Thomas!

Sorry for disturbing u again. AS per ur advice, i went thru the fundas.

It seems that fellow wanted to ask me the alternative of "Runtime Query Handling" in a stored proc. As it will be slow if we use the runtime queries ....

So, cud u pls. tell me if any alternative approach is there. I think only alternative approach is to do these things in our application program instead of stored proc.

Kindly advice.

Reagrds,

Lita.|||::It seems that fellow wanted to ask me the alternative of "Runtime Query Handling" in a
::stored proc.

Alternative of WHAT?

Puting three terms noone knows besides you into paranthesis does not make it common knowledge.

What do you mean?|||Interesting that neither "Run-time Query Handling" nor "Runtime Query Handling" return a single hit from Google. Sounds like an interviewer who doesn't have a clue and is trying to throw a curve.

Wednesday, March 7, 2012

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest SP
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying problem
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText)See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText)
>|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest SP
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying problem
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
connectionString, CommandType commandType, String commandText)
See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
> connectionString, CommandType commandType, String commandText)
>
|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest S
P
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying proble
m
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText, SqlParameter&
#91;]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText)See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[
]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText)
>|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

Fast question

Hello!
I want to build an application (in visual basic) using the MSDE, and sell it
to thirds. What should i do for make it legally? I have licenses for visual
basic, but as far i know, I can redistribute the MSDE for free. Or I should
sign some agreement, like the Microsoft Agent?
Thank you for your answers and sorry my poor english
Lirn
Liran Marino
__________
Este mensaje se proporciona "TAL COMO ESTA", sin garantias y no otorga
ningun
derecho
__________
(Gua de netiquette del foro)
http://www.uyssoft.com/Netiquette/
__________
Las listas de News son como el Tetris, para la gente que aun recuerda como
leer...
Hi
You need a licence for Visual Basic Development Environment, and MSDE can be
distributed freely with a VB application.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Liran Marino" wrote:

> Hello!
> I want to build an application (in visual basic) using the MSDE, and sell it
> to thirds. What should i do for make it legally? I have licenses for visual
> basic, but as far i know, I can redistribute the MSDE for free. Or I should
> sign some agreement, like the Microsoft Agent?
> Thank you for your answers and sorry my poor english
> Lirán
> --
> Liran Marino
> __________
> Este mensaje se proporciona "TAL COMO ESTA", sin garantias y no otorga
> ningun
> derecho
> __________
> (GuXa de netiquette del foro)
> http://www.uyssoft.com/Netiquette/
> __________
> Las listas de News son como el Tetris, para la gente que aun recuerda como
> leer...
>
>

Fast Printing from Crystal report 9.0

Hi,
We have created an application in VB 6, MS ACCESS 2000 and all the reports we created in CRYSTAL REPORT 9.0. Now our customer is complaining that printing speed is very slow. IS there any way to fasten the printing speed of Crystal report ?Instal Generic Printer and adjust the report accordingly. in this case you will not able to print graphics line etc. but printing is very very fast. The printer driver exists in windows. i have also applied this thing in my projects (with crystal 8.0).|||Originally posted by jimman
Hi,
We have created an application in VB 6, MS ACCESS 2000 and all the reports we created in CRYSTAL REPORT 9.0. Now our customer is complaining that printing speed is very slow. IS there any way to fasten the printing speed of Crystal report ?

Hi,

Use Printer support fonts, you can find out the printer icon contains in the front of the font name.|||HI,

I think the best way to achieve fast printing is to print in DOS format; for this u will have to export the report into text file and using Bat file print this report..

try and see..

swapnil

Sunday, February 26, 2012

False "LIKE" hits

Hi All... We have an application where a table has a field of type text that contains readable text, but some of it may have an HTML tag in it - specifically an HTML <img> tag. For example, it may be something like:

BLAH BLAH BLAH <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'> BLAH BLAH BLAH.

The problem we're seeing is that when searching that field using LIKE, it's returning records that do in fact satisfy the SQL query, but we'd like it not to. That is, we'd like to exclude those HTML tags from the search.

For example:

SELECT ... WHERE TextField LIKE '%retr%'

is returning records that have those HTML tags. Yes, I know SQL is only doing its job... Anyone have any ideas to exclude those tags from the LIKE search? Thanks! -- Curt

Curt, I'm not clear on if you want to filter out the rows that have ANY html tags? or you're saying that part of a single column has a mixture of data AND html and you'd want to search only the non-html part of the column?

So, if you search this string below for the word "White" you want the row, but if you search on the word "Image" you do NOT want the row?

White Horse <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'>

so, you want to filter out everything between the "<" and ">" ?

You could write a function that parses each line, and searches for your string...

Bruce

|||

If I understand you correctly, you want the return to be:

BLAH BLAH BLAH ... BLAH BLAH BLAH

If that is a correct interpretation, it's not going to be either easy or pretty. By that I mean you will have to parse the data fields on the search which is really going to increase the time required to search. Indexes will not be used.

If this is the path you wish to take, you will need to create a User Defined Function that will take the entire field and strip out all characters between paired angle brackets.

|||

Thanks for the replies, Bruce and Arnie. I'm sorry for not making the issue very clear - believe it or not, it took me some time to figure out the wording I did manage to get down...

Using the statement SELECT * From TheTable WHERE TheField LIKE '%RETR%'

I would want the following record to be included:

BLAH RETR BLAH <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'> BLAH BLAH BLAH

But I would NOT want the following record included:

BLAH BLAH BLAH <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'> BLAH BLAH BLAH

In any records that are included, I would want the text returned as it appears - that is nothing filtered out.

You both pointed in the direction of writing a function to filter out that "<....>" data before applying a search. And yeah, that just adds to the overhead of the search... And quite frankly, I'm a bit nervous about that anyway - there's gonna be alot of these records and that LIKE just seems expensive. I've also been looking at indexing the text and using CONTAINS (that sound right?). We're also considering a sort of application-specific index of the text before we put it in the table as alot of the searchs are somewhat predictable.

|||

If there is only a single tag per row, you could have a WHERE clause that includes BOTH the substring that precedes "<" and the substring that follows ">". That should not require a function.

If there are multiple "<...>" entries per row, this method would not work as desired.

Dan

|||

IF, and that is a big IF in my opinion, the data is consistant and your sample correctly reflects the search value, this could work:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
Comment varchar(max)
)

INSERT INTO @.MyTable VALUES ( 'BLAH RETR BLAH <img src='http://pics.10026.com/?src='RetrieveImage.aspx?ImageId=1234''> BLAH BLAH BLAH' )
INSERT INTO @.MyTable VALUES ( 'BLAH BLAH BLAH <img src='http://pics.10026.com/?src='RetrieveImage.aspx?ImageId=1234''> BLAH BLAH BLAH' )

SELECT Comment
FROM @.MyTable
WHERE ( Comment LIKE '% RETR %'
AND Comment NOT LIKE '%=''RETR'
)

And of course, you could 'build up' the search values using parameters and constants.

This feels so 'unclean' that now I have to go take a shower...

|||

slightly shorter version.

Code Snippet

SELECT Comment
FROM @.MyTable
WHERE (Comment LIKE '%[ ]RETR[ ]%')

|||This problem was born for regular expressions:
http://msdn.microsoft.com/msdnmag/issues/07/02/SQLRegex/default.aspx

you could also use charindex:

Code Snippet

SELECT *
From TheTable
where charindex('RETR', TheField)

not between charindex('<', TheField)

and charindex('>', TheField)

|||Spent far too long on this but here goes.

The following allows for any number of HTML Tags in the field.

Tested with the following:

Code Snippet

SELECT * into TheTable
From(
select 'BLAH RETR BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH' as TheField
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH'
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH RETR BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAHRETRBLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> RETR BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
) as t1

create a table Numbers with a single field Num populated 1 to x where x = a number larger than the longest field you expect to work with:

Code Snippet

select * into numbers from (
select 1 as Num union all
select 2 as Num union all
select 3 as Num union all
...
select 2999 as Num union all
select 3000 as Num) as T1

Then use this query:

Code Snippet

select distinct TheField
from (
select
TheField,
case
when substring(TheField, num,1) = '>'

or num = 1

then substring(TheField, Num+1, charindex('<',TheField+'<', Num) - Num -1 )
else ''
end as c3
from TheTable, Numbers
) as T1
where c3 like '%RETR%'

Friday, February 24, 2012

Failure to open SQL Server with given connect string

hello,

im working with VB 2005 and i have this error when triyng to connecting to the server database from my pocket pc application.

"Failure to open SQL Server with given connect string."

this is part of my code:

rdaOleDbConnectString = "Provider=sqloledb;Data Source=localhost;Persist Security Info=SSPI;User ID=id;Password=password;Initial Catalog=praia"

rdaUrl = "http://server/SqlMobile/sqlcesa30.dll"
localConnection = "Data Source=\My Documents\PraiaDB6.sdf"

If File.Exists("\My Documents\PraiaDB6.sdf") Then
File.Delete("\My Documents\PraiaDB6.sdf")
End If

Dim engine As New SqlCeEngine
engine.LocalConnectionString = localConnection
engine.CreateDatabase()
engine.Dispose()
MsgBox("DB Mobil empty created!")

'Init RDA Object
Dim rda As SqlCeRemoteDataAccess = Nothing

rda = New SqlCeRemoteDataAccess(rdaUrl, localConnection)
rda.InternetUrl = rdaUrl
rda.LocalConnectionString = localConnection
rda.Pull("Reserva", "select * from Reserva", rdaOleDbConnectString, RdaTrackOption.TrackingOn)
rda.Dispose()

the problem is in rda.Pull(...)
it can not connect to rdaOleDbConnectString

please help me
Thanks!

Jo?o
It is not likely that the datasource is "localhost", as this refers to the Pocket PC device, it should probably be the machine name or the ip address of the machine running SQL Server|||

Hi ErikEJ, thanks for the reply

i also tried the connectString with the machine name, like : Data Source=machinename; and i got the same error

also tried the ip adress of the server..

i dont understand whats supose the connect string look like...

anybody? thanks!

|||

Can you connect via Internet Explorer on the device to: http://server/SqlMobile/sqlcesa30.dll
If that is not possible, this may be the cause of the error.

|||

yes i can!

i read somewhere that when connecting via device (ppc) its need it to put ip adress instead server name.

i've found this link and im going to try now with this connecting string. ill post the result. thanks in advance for your help

http://www.connectionstrings.com/?carrier=sqlserver2005

Connect via an IP address

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Jo?o.

|||Same error...

:(
|||solution:

rdaOleDbConnectString = "Provider=SQLOLEDB.1;Data Source=machineName,1433;Persist Security Info=False;Initial Catalog=praiateste;User ID=teste;Password=password;"
|||hello,

i was olso working on RDA sync and with the same connection string but in C# and i'm having the same problem the error message i'm getting is:

Failure to open SQL Server with given connect string. [connect string = Provider=SQLOLEDB.1;Data Source=localhost; Persist Security Info= True; User ID=MobileUser;Password=password;Initial Catalog=MobileDemo; ]

i'd tryed out all the alternatives posted on the forum but i'm still getting the same error the intial connection string was:

@."Provider=SQLOLEDB.1;Data Source=localhost;Persist Security Info=True;User ID=MobileUser;Password=password;Initial Catalog=MobileDemo";

hope you can help me thanks|||rdaOleDbConnectString = "Provider=SQLOLEDB.1;Data Source=machineName,1433;Persist Security Info=False;Initial Catalog=praiateste;User ID=teste;Password=password;"

try using the actual server name and open the port 1433 at SQL server

Tuesday, March 27, 2012

Field Definition report results no record

Friday, March 23, 2012

Feature Request: 2005 Beta 2

Wednesday, March 21, 2012

FDA Compliance and SQL 2000

faulting module

Fatal exception while firing a rule

Fatal exception while firing a rule

Fatal exception while firing a rule

Monday, March 19, 2012

Fatal Exception error

Fatal Exception error

fatal exception c0000005 EXCEPTION_ACCESS_VIOLAT

fatal exception c0000005 EXCEPTION_ACCESS_VIOLAT

fatal exception c0000005 EXCEPTION_ACCESS_VIOLAT

Friday, March 9, 2012

Faster SQL query

Wednesday, March 7, 2012

Fast SP is timing out

Fast SP is timing out

Fast SP is timing out

Fast question

Fast Printing from Crystal report 9.0

Sunday, February 26, 2012

False "LIKE" hits

Friday, February 24, 2012

Failure to open SQL Server with given connect string

Footer rows to skip

Blog Archive

About Me