Failed to start jobs in sql agent after restart service

We have clustered sql2005 (sp2) installed in win2003 (sp2). Recently, we
encountered a problem which failed to start jobs in sql agent after restart
mssql server and aql agent services. It failed with exception error in the
job status dialogue.
There is no error message in event viewer and we found an error aqlagent.out
it shows
2007-07-28 02:08:11 - ! [165] ODBC Error: 0, Unable to complete login
process due to delay in opening server connection [SQLSTATE 08001]
Kindly advise and thanks in advanceCheck your service account (SQLAgent) and permissions related to this job.
Plus, check your cluster resources pertaining to SQL Agent service
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:754761F4-D5A9-460F-904A-BA0FC63CFBC6@.microsoft.com...
> We have clustered sql2005 (sp2) installed in win2003 (sp2). Recently, we
> encountered a problem which failed to start jobs in sql agent after
> restart
> mssql server and aql agent services. It failed with exception error in
> the
> job status dialogue.
>
> There is no error message in event viewer and we found an error
> aqlagent.out
> it shows
>
> 2007-07-28 02:08:11 - ! [165] ODBC Error: 0, Unable to complete login
> process due to delay in opening server connection [SQLSTATE 08001]
>
> Kindly advise and thanks in advance
>|||This problem only exist in node A. If failover to Node B , the agent works
normally.
the sql agent service a/c is an administrator. I compared the permission and
sql agent setting in cluster admin. Thery are the same
Kindly advise. Thanks a lot.
"bass_player [SBS-MVP]" wrote:

> Check your service account (SQLAgent) and permissions related to this job.
> Plus, check your cluster resources pertaining to SQL Agent service
>
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:754761F4-D5A9-460F-904A-BA0FC63CFBC6@.microsoft.com...
>
>

Failed to start jobs in sql agent after restart service

We have clustered sql2005 (sp2) installed in win2003 (sp2). Recently, we
encountered a problem which failed to start jobs in sql agent after restart
mssql server and aql agent services. It failed with exception error in the
job status dialogue.
There is no error message in event viewer and we found an error aqlagent.out
it shows
2007-07-28 02:08:11 - ! [165] ODBC Error: 0, Unable to complete login
process due to delay in opening server connection [SQLSTATE 08001]
Kindly advise and thanks in advanceCheck your service account (SQLAgent) and permissions related to this job.
Plus, check your cluster resources pertaining to SQL Agent service
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:754761F4-D5A9-460F-904A-BA0FC63CFBC6@.microsoft.com...
> We have clustered sql2005 (sp2) installed in win2003 (sp2). Recently, we
> encountered a problem which failed to start jobs in sql agent after
> restart
> mssql server and aql agent services. It failed with exception error in
> the
> job status dialogue.
>
> There is no error message in event viewer and we found an error
> aqlagent.out
> it shows
>
> 2007-07-28 02:08:11 - ! [165] ODBC Error: 0, Unable to complete login
> process due to delay in opening server connection [SQLSTATE 08001]
>
> Kindly advise and thanks in advance
>|||This problem only exist in node A. If failover to Node B , the agent works
normally.
the sql agent service a/c is an administrator. I compared the permission and
sql agent setting in cluster admin. Thery are the same
Kindly advise. Thanks a lot.
"bass_player [SBS-MVP]" wrote:
> Check your service account (SQLAgent) and permissions related to this job.
> Plus, check your cluster resources pertaining to SQL Agent service
>
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:754761F4-D5A9-460F-904A-BA0FC63CFBC6@.microsoft.com...
> > We have clustered sql2005 (sp2) installed in win2003 (sp2). Recently, we
> > encountered a problem which failed to start jobs in sql agent after
> > restart
> > mssql server and aql agent services. It failed with exception error in
> > the
> > job status dialogue.
> >
> >
> >
> > There is no error message in event viewer and we found an error
> > aqlagent.out
> >
> > it shows
> >
> >
> >
> > 2007-07-28 02:08:11 - ! [165] ODBC Error: 0, Unable to complete login
> > process due to delay in opening server connection [SQLSTATE 08001]
> >
> >
> >
> > Kindly advise and thanks in advance
> >
>
>

failed to register dll files needed for DTS

I found the information of installing DTS service without installing SQL server on the disc:

INSTALLATION NOTES FOR DATA TRANSFORMATION SERVICES (DTS)
------------------
The following files must be registered using the regsvr32.exe utility:
dtsffile.dll
dtspkg.dll
dtspump.dll
axscphst.dll

Example: regsvr32 80\Tools\Binn\dtsffile.dll

The sqlunirl.dll file should reside in the system folder (i.e., \winnt\system32 or \windows\system).

when I attempted to register the dlls, the message
:"loadLibrary("...\dtsffile.dll")failed - The speified module could not be
found.

is there anything I have to do before I can get the dll registered?

Thanks in advance.problem solved, please consult here:

http://www.sqldts.com/default.aspx?6,105,225,0,1

Failed to create Application Services Database for SQL Server

I have sql 2005 express installed with VS2005. I tried to install the service database using Aspnet_regsql.exe but always ended up with the same error:

Code Snippet

Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallMembership.sql'. The SQL error number is -2 and the SqlException message is: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

-
Details of failure
-

SQL Server:
Database: [aspnetdb]
SQL file loaded:
InstallMembership.sql

Commands failed:

EXEC sp_addrolemember N'aspnet_Membership_BasicAccess', N'aspnet_Membership_FullAccess'
EXEC sp_addrolemember N'aspnet_Membership_ReportingAccess', N'aspnet_Membership_FullAccess'

SQL Exception:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)

The database aspnetdb was always created with just 4 tables and 3 views.

Please can someone help me on this, thanks very much!

If the database still exists, the script might wait for the last database connection to close to be able to drop the database. Is there any DROp statement in the sql file ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

Fail to start SQL Server 2005 Analysis Services Service

Hi, all experts here,

How can we find the storage location of the first partition of a cube originally when the cube is created? I mean after the cube is created with the cube wizard, we can always add new partitions for a cube with the partition wizard which enables us to select the storage location of the partitions. But how about the first partition of a cube which is created automatically along with the creation of a cube? I need to locate the storage locations of all partitions of a cube to be able to put the OLAP database somewhere with enough space.

I am looking forward to hearing from you shortly and thanks a lot in advance for your kind advices and help.

With best regards,

Yours sincerely,

Open SSMS, connect to your instance, right-click it and select Properties. In the General tab, look for a property called DataDir. Your partitions will be created in a subfolder of that folder. If you open the value dialog on this property, you will be given instructions on how to make additional locations available.

Good luck,
Bryan

|||

You can also view and set this from within BIDS for any partion.

Open up the cube, click on the partitions tab, click on a partition and in the properties window (right click on the partition and select "Properties" if this window is not already visible, in my environment the properties window is always visible in the lower right hand corner). There is a property called StoreageLocation, if you click on the button for this property a dialog pops up that lets you see the default storage location and override it, to set it to a given folder.

You can also change this property at the Cube and MeasureGroup objects, but I think at this level the property only works as a default setting for any new partitions that are created.

|||

Why I am not able to start the Analysis Services Engine again as the error message from event viewer as:

Event Type: Error
Event Source: MSSQLServerOLAPService
Event Category: (289)
Event ID: 0
Date: 04/07/2007
Time: 10:45:48
User: N/A
Computer: computer_name
Description:
The service cannot be started: Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. The following system error occurred: The filename, directory name, or volume label syntax is incorrect. . File system error: Error occurred during the creation of directory: '\\?\'.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

On SQL Server, when I start the Analysis services, the error message is : The SQL Server Analysis Services (MSSQLSERVER) service on local computer started and then stopped. Some services stop automatically if they have no work to do, for example, the performance logs and Alerts service.

Why is that? That is really frustrated and please any of you give me any advices or help on it. Thanks a lot in advance.

With best regards,

Yours sincerely,

|||

Hi, Bryan,

Thank you for your kind advices. But another very weird problem occured now. I am not even able to restart the SQL Server 2005 Analysis Services Service now.

Event Type: Error
Event Source: MSSQLServerOLAPService
Event Category: (289)
Event ID: 0
Date: 04/07/2007
Time: 10:45:48
User: N/A
Computer: computer_name
Description:
The service cannot be started: Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. The following system error occurred: The filename, directory name, or volume label syntax is incorrect. . File system error: Error occurred during the creation of directory: '\\?\'.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

On SQL Server, when I start the Analysis services, the error message is : The SQL Server Analysis Services (MSSQLSERVER) service on local computer started and then stopped. Some services stop automatically if they have no work to do, for example, the performance logs and Alerts service.

I have no idea what is going on and that is pretty frustrated.

I am looking forward to hearing from any of you here for your kind advices and help.

Thanks in advance.

With best regards,

Yours sincerely,

|||

Did you change something?

The error message seems similar to the one you posted on June 13 in PostID 463688. Could this be the same issue?

B.

|||

Hi, Bryan,

Thanks for your reply.

I have no idea what did I change. The only thing I did change was DataDir setting of the server property which I changed to other storage location instead of where the Analysis Services Instance was originally installed.

Does that have something to do with this problem?

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

|||

Changing the default DataDir of a server that is in use is a bigger deal than either BOL or the user interface lets on.

Other people have posted about similar issues before: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=500300&SiteID=1

The above posting might give you some ideas as to how to rectify the issue, it might be that the SSAS service account does not have access to the new location.

An alternative approach would be to set the DataDir back to its default setting and to just move specific cubes/partitions to an alternate location.

|||These two threads have been merged as the same question was posted twice

Fail to start Integration Service

As part of Team Foundation Server the Integration Services are used. They are configured to start automatically but it doesn't. Status is "stopped". Trying to start the service manually fails too:

Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7000
Date: 07.11.2006
Time: 11:11:49
User: N/A
Computer: SDOSCARBUILDSRV
Description:
The SQL Server Integration Services service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

It uses default LogOnAs NT Authority\NetworkService. Any idea what might be wrong and what to do?

Thanks

Hans-Peter

Please check if this KB applies:
http://support.microsoft.com/kb/918644|||

SP1 has not been installed yet.

Hans-Peter

|||Is the Sql Server Service started? SSIS is dependent on that service.|||

Yes, the Sql Server Service is started. Also Agent, Analysis Service, Browser, Full Text Search, Reporting Services and VSS Writer. They all are started automatically and they are up.

Hans-Peter

|||

What to do? Any hints?

Hans-Peter

Fail to start Integration Service

As part of Team Foundation Server the Integration Services are used. They are configured to start automatically but it doesn't. Status is "stopped". Trying to start the service manually fails too:

Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7000
Date: 07.11.2006
Time: 11:11:49
User: N/A
Computer: SDOSCARBUILDSRV
Description:
The SQL Server Integration Services service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

It uses default LogOnAs NT Authority\NetworkService. Any idea what might be wrong and what to do?

Thanks

Hans-Peter

Please check if this KB applies:
http://support.microsoft.com/kb/918644|||

SP1 has not been installed yet.

Hans-Peter

|||Is the Sql Server Service started? SSIS is dependent on that service.|||

Yes, the Sql Server Service is started. Also Agent, Analysis Service, Browser, Full Text Search, Reporting Services and VSS Writer. They all are started automatically and they are up.

Hans-Peter

|||

What to do? Any hints?

Hans-Peter

fail to install sql server 7.0 to window2000 server cluster

we have a 2 nodes win2k server cluster which installed sqlsrv 7.0, the
primary one failed, and the secondary take over the service as design.
we do not have system backup for the primary node, and we got a blue screen
for that one. it seams like that some registe records are crashed. we decide
to reinstall that node.
when we install sql server before cluster service, the cluster service will
complain the sql server's exsiting. when we install the cluster before sql
server 7.0, the sql server will complain we should undeploy the cluster.
i can not find any valueable resources for install sql server 7.0 in win2k
cluster. is there any one had this situation before?
Unfortunately, you will have to rebuild your SS70 cluster. This does not
mean you will have to rebuild your installation, just the cluster.
Uncluster the current node.
Add the new node to the Win2K cluster.
Rerun Comclust to cluster the MS DTC on the new node.
Install SS70 on the new node.
Recluster the SS70 installation.
Anthony Thomas

"linbiao" <linbiao@.discussions.microsoft.com> wrote in message
news:2133DDC4-EDDF-4023-B381-9AA778CDC567@.microsoft.com...
> we have a 2 nodes win2k server cluster which installed sqlsrv 7.0, the
> primary one failed, and the secondary take over the service as design.
> we do not have system backup for the primary node, and we got a blue
screen
> for that one. it seams like that some registe records are crashed. we
decide
> to reinstall that node.
> when we install sql server before cluster service, the cluster service
will
> complain the sql server's exsiting. when we install the cluster before sql
> server 7.0, the sql server will complain we should undeploy the cluster.
> i can not find any valueable resources for install sql server 7.0 in win2k
> cluster. is there any one had this situation before?
|||On Sun, 17 Dec 2006 15:21:50 -0800, Anthony Thomas <ALThomas@.kc.rr.com>
wrote:

> Unfortunately, you will have to rebuild your SS70 cluster. This does not
> mean you will have to rebuild your installation, just the cluster.
> Uncluster the current node.
> Add the new node to the Win2K cluster.
> Rerun Comclust to cluster the MS DTC on the new node.
> Install SS70 on the new node.
> Recluster the SS70 installation.
>
> Anthony Thomas
>
thank you very much. we will try it.
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Fail to install reporting service

I need to install the Reporting Service in a production site on the web server.
Both web server and database server are mamber server in the same domain.
When i install the Reporting Service in the web server in keep having below
error
SQL Setup failed to connect to the database service for server
configuration. The error was: Login failed for user '(null)'.
Reason: Not associated with a trusted SQL Server connection.
Refer to the Server error logs and setup logs for more detailed information.
The eviroment configuration as below
DC : NT 4.0 with sp 6a
Web Server : - Windows 2003 standard server with sp 1, SSL Apply, Sharepoint
Services with sp 1 installed, .Net Framework 1.1 with sp 1
Database Server : Windows 2003 standard server with sp 1, MS SQL 2000 with
sp 3a in mix mode, .Net Framework 1.1 with sp 1
User :
SetupAdmin
- Domain User
- Is added to local administrator group on both web server and database
server
- With database server right on 'Security Administrator', 'Process
Administrator' and 'Database Creator'
ReportUser
- Domain User
- Is a database user
ReportSQLUser
- SQL Login Account
I login web server using SetupAdmin, when run setup. I set 'Network\Service
account' to reporting service, when ask for the database connection user I
can use either SetupAdmin, ReportUser or ReportSQLUser it will pass. But the
setup give me an error when going to setup database on the database server,
non off the 3 user account i can use to make the setup success.
Can anyone let me know why? and how to solve it?James,
Go to the SQL Server that you're trying to set up the database on, open it
up in Enterprise Manager, navigate to the Security folder and verify that the
Admin account you are using is mapped to a SQL Server login AND that that
login has the rights to create a new database. You'll need DBOwner database
role and DBCreator server role.
I believe the reason you're unable to complete your setup is a simple
permissions issue.
Hope that helps!
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"James Hong" wrote:
> I need to install the Reporting Service in a production site on the web server.
> Both web server and database server are mamber server in the same domain.
> When i install the Reporting Service in the web server in keep having below
> error
> SQL Setup failed to connect to the database service for server
> configuration. The error was: Login failed for user '(null)'.
> Reason: Not associated with a trusted SQL Server connection.
> Refer to the Server error logs and setup logs for more detailed information.
> The eviroment configuration as below
> DC : NT 4.0 with sp 6a
> Web Server : - Windows 2003 standard server with sp 1, SSL Apply, Sharepoint
> Services with sp 1 installed, .Net Framework 1.1 with sp 1
> Database Server : Windows 2003 standard server with sp 1, MS SQL 2000 with
> sp 3a in mix mode, .Net Framework 1.1 with sp 1
> User :
> SetupAdmin
> - Domain User
> - Is added to local administrator group on both web server and database
> server
> - With database server right on 'Security Administrator', 'Process
> Administrator' and 'Database Creator'
> ReportUser
> - Domain User
> - Is a database user
> ReportSQLUser
> - SQL Login Account
> I login web server using SetupAdmin, when run setup. I set 'Network\Service
> account' to reporting service, when ask for the database connection user I
> can use either SetupAdmin, ReportUser or ReportSQLUser it will pass. But the
> setup give me an error when going to setup database on the database server,
> non off the 3 user account i can use to make the setup success.
> Can anyone let me know why? and how to solve it?|||Thanks for the advice Catadmin.
Need comfirmation on the setting you have advice.
1. The Reporting Services Database name "ReportServer" and
"ReportServerTempDB" have not yet being created so the DBOwner Database Role
should grant to which database? Is the 'master', 'model' or 'msdb'.
2. I have granted the 'Database Creator' server role to SetupAdmin. So, is
it meet the DBCreator server role which you have advice?
--
Best Regard,
James Hong
"Catadmin" wrote:
> James,
> Go to the SQL Server that you're trying to set up the database on, open it
> up in Enterprise Manager, navigate to the Security folder and verify that the
> Admin account you are using is mapped to a SQL Server login AND that that
> login has the rights to create a new database. You'll need DBOwner database
> role and DBCreator server role.
> I believe the reason you're unable to complete your setup is a simple
> permissions issue.
> Hope that helps!
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
>
> "James Hong" wrote:
> > I need to install the Reporting Service in a production site on the web server.
> > Both web server and database server are mamber server in the same domain.
> > When i install the Reporting Service in the web server in keep having below
> > error
> >
> > SQL Setup failed to connect to the database service for server
> > configuration. The error was: Login failed for user '(null)'.
> > Reason: Not associated with a trusted SQL Server connection.
> > Refer to the Server error logs and setup logs for more detailed information.
> >
> > The eviroment configuration as below
> > DC : NT 4.0 with sp 6a
> > Web Server : - Windows 2003 standard server with sp 1, SSL Apply, Sharepoint
> > Services with sp 1 installed, .Net Framework 1.1 with sp 1
> > Database Server : Windows 2003 standard server with sp 1, MS SQL 2000 with
> > sp 3a in mix mode, .Net Framework 1.1 with sp 1
> > User :
> > SetupAdmin
> > - Domain User
> > - Is added to local administrator group on both web server and database
> > server
> > - With database server right on 'Security Administrator', 'Process
> > Administrator' and 'Database Creator'
> > ReportUser
> > - Domain User
> > - Is a database user
> > ReportSQLUser
> > - SQL Login Account
> >
> > I login web server using SetupAdmin, when run setup. I set 'Network\Service
> > account' to reporting service, when ask for the database connection user I
> > can use either SetupAdmin, ReportUser or ReportSQLUser it will pass. But the
> > setup give me an error when going to setup database on the database server,
> > non off the 3 user account i can use to make the setup success.
> >
> > Can anyone let me know why? and how to solve it?

Fact Table/Dimension and Multiple Level Dimension

Hi Guys,

I have two questions on Analysis Service for SQL SERVER 2005:-

a) Is it possible for me to use two tables from my database without Primary Key. As this use to work on my Analysis Service on SQL SERVER 2000.

b) I had a multiple Level Dimension in SQL SERVER 2000 with Key Column as one column in my table and name column as another in my table. I am not able to do the same in SQL SERVER 2005.

Urgent help is requried.

Regards,

Kaushal

a)You do not need primary keys in the source data base but I think you need to set logical primary keys in the data source view.

b)Key and name columns are still separated in Analysis Services 2005.

Regards

Thomas Ivarsson

|||

Hi Thomas,

Thanks for Reply.

a) Yes i do understand that, but then what happens if i want to use the columns used in Primary key as levels in a Dimension?

b) I am still not understanding how to take kare of Key and Name Columns?

Regards,

Kaushal

Fact table changes

Our data mart is medical based type of subjects.
Recently; the government changed how some "hours" of service could be
provided to certain people. The "hours" value is stored in a Fact table.
So for example; Client XYZ used to have 50 hours; it may have 63 now.
So I see how easy it would have been to make these data changes if it had
been in a Dimension - would have been type 2 SCD - but not sure what best
approach is for changes in a Fact table. I'm almost tempted to have a
status column to identify which Fact record is current; but then I would
have much work to do in SSAS to change how the Cubes are put together.
What is the best practice when you have to make data changes to existing
Fact records in a dm/dw?I'd recommend avoiding changes to historic data in a DW unless they were
genuinely erroneous.
Is there a date dimension relationship in your fact table? If so, what if a
user was to pull up a report from the past where the 50 hours was correct
but your warehouse reported 63? Would this be incorrect? If there are no
dates one option as you suggest would be a flag. Then you could construct a
view selecting the same columns as before but only including current rows.
The only change you'd need to make in SSAS would be in the Data Source
View - to change from the table to the view. The disadvantage of this would
be that end-users couldn't report on the previous 'hours' value. This gets
worse if the value changes again.
A better option ('best practice' perhaps) would be to introduce a start and
end date key (related to your date dimension obviously) over which period
the value was current - perhaps seperate to any existing dates. This way end
users could query for the different hours value based on when it was
relevant. A new value generates a new row with new dates. If necessary you
could still construct a view for the most recent value - but you would still
have all of the data in the database to support more sophisticated analysis.
There's more work here clearly - only you can decide whether its worth it.
HTH
--
Phil
http://www.clarity-integration.com
http://www.phil-austin.blogspot.com
"Joe" <hortoristic@.gmail.dot.com> wrote in message
news:0CA262DF-F121-496C-878E-0CDEC387D159@.microsoft.com...
> Our data mart is medical based type of subjects.
> Recently; the government changed how some "hours" of service could be
> provided to certain people. The "hours" value is stored in a Fact table.
> So for example; Client XYZ used to have 50 hours; it may have 63 now.
> So I see how easy it would have been to make these data changes if it had
> been in a Dimension - would have been type 2 SCD - but not sure what best
> approach is for changes in a Fact table. I'm almost tempted to have a
> status column to identify which Fact record is current; but then I would
> have much work to do in SSAS to change how the Cubes are put together.
> What is the best practice when you have to make data changes to existing
> Fact records in a dm/dw?

Extreme performance issues (SQL Server 2000/ADO.NET/C#)

I posted this on the ADO.NET group, and someone there suggested I post
it here too. I appreciate any tips.
I'm using ADO.NET in a windows service application to perform a process on
SQL Server 2000. This process runs very quickly if run through Query
Analyser or Enterprise Manager, but takes an excessively long time when run
through my application. To be more precise, executing stored procedures and
views through Query Analyser take between 10 and 20 seconds to complete. The
same exact stored procedures and views, run in the same exact order, through
my program, take anywhere from 30 minutes to 2 hours to complete, and the
system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical
ram) is pegged at 25% cpu usage (the query uses 100% of a single cpu's worth
of processing power). I am at a complete loss as to why such a vast
difference in execution time would occurr, but here are some details.
The windows service executes on a workstation.
SQL Server 2000 executes on a server different from the workstation through
a 100mbps ethernet network.
Query Analyser/Enterprise Manager run on the same workstation as the windows
service.
The process is as follows:
1) Run a stored procedure to clear temp tables.
2) Import raw text data into a SQL Server table (Reconciliation).
3) Import data from a Microsoft Access database into 3 SQL Server tables
(Accounts, HistoricalPayments, CurrentPayments).
(This takes about 10 - 15minutes to import 70,000 - 100,000 records from
an access database, housed on a network share on a different server.)
4) "Bucketize" the imported data. This process gathers data from the 4
tables stated so far (Reconciliation, Accounts, HistoricalPayments,
CurrentPayments, and places records into another table (Buckets) and
assigned a primary category number to each record through a stored
procedure.
5) Sort buckets of data into subcategories, updating each record in
(Buckets) and assigning a sub category number, through another stored
procedure.
6) Retrieve a summary of the data in (Buckets) (this summary is a count of
rows and summation of monetary values), grouped by the primary category
number. This is a view.
7) Retrieve a summary of the data in (Buckets), grouped by both the primary
and sub category numbers. This is a view.
When I execute these steps manually through query analyser, (save step 3),
each query takes anywhere from 1 second to 20 seconds. The views,
surprisingly, take more time than the fairly complex stored procedures of
step 4 and 5.
When I execute these steps automatically using my windows service (written
in .NET, C#, using ADO.NET), the simple stored procedures like clearing
tables and whatnot execute quickly, but the stored procedures and views from
steps 4-7 take an extremely long time. The stored procedures take at a
minimum 30 minutes to complete, and sometimes nearly an hour. The views are
the worst of all, taking no less than 1 hour to run, and often two hours
(probably longer, actually, since my CommandTimeout is set to 7200 seconds,
or two hours). I have never seen such a drastic difference between the
execution of a query or stored procedure between query analyser and an
application. There should be little or no difference at all, considering
that everything is stored procedures (even the views...I wrap all the views
in a simple stored procedure that calls the view using a SELECT), and as
such executes on the server. Not only that, but Query Analyser is running on
the same exact box that the application is running on, and is connecting ot
the same SQL Server.
I doubt this is a network bandwidth issue, as after calling the stored
procedure from code, there is no network activity except mssql keep-alive
messages, until the procedure completes and returns its result set or return
value (if any), and then its only a momentary blip as the data is sent
accross.
I've followed proper practice when using views and stored procedures. When I
select, I always explicitly name the columns I wish to retrieve. I have
appropriate indexes on the columns in the 4 data tables. The queries that
execute in the stored procedures are fairly complex, involving summations,
count(), group by, and order by. I can understand a moderate difference in
performance between query analyser and an ADO.NET application due to
ADO.NETs extra overhead, but a difference between 20 seconds and 1 hour is
more than can be attributed to .NET overhead.
I greatly appreciate anyone who might have some insight to this offering
some help. I've scanned the net looking for similar situations, but
searching for them is somewhat difficult, considering the nature and volume
of factors. Thanks.
-- Jon Rista
Have you profiled both SQL Server and your application to find out where all
of the time is coming from? You should run SQL Server Profiler to find out
if the stored procedures are really taking 30 minutes (and what parameters
are really getting passed in -- it could be that something is not getting
properly passed in). If it turns out that the stored procedures are not
taking up the time, you should download a copy of Compuware's DevPartner
Profiler, Community Edition ( www.compuware.com ). Then you can profile
your app to find out what's going on.
When you say you're "using ADO.NET", what does that mean? Are you using a
data reader? Data adaptor? What are you doing with the data once it's
loaded into the reader/data set?
"Jon Rista" <jrista@.hotmail.com> wrote in message
news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
> I posted this on the ADO.NET group, and someone there suggested I post
> it here too. I appreciate any tips.
> I'm using ADO.NET in a windows service application to perform a process on
> SQL Server 2000. This process runs very quickly if run through Query
>
|||When you profile your server, if you find that there is a
performance issue with a stored proc, you might want to
include the "Stored Procedure- SP:StmtCompleted" event in
order to see if there is a particular statement within
your stored procs that is takign all the time.
Also, if it isn't already there, add "SET NOCOUNT ON" at
the top of your stored procedures.
We have seen some preformance issue from .NET Web
Services but have not used .NET Windows Services. I have
a feeling that this may actually be your problem.
I hope that this helps somehow.
Matthew Bando
Matthew.Bando@.Remove csctgi.com

>--Original Message--
>Have you profiled both SQL Server and your application
to find out where all
>of the time is coming from? You should run SQL Server
Profiler to find out
>if the stored procedures are really taking 30 minutes
(and what parameters
>are really getting passed in -- it could be that
something is not getting
>properly passed in). If it turns out that the stored
procedures are not
>taking up the time, you should download a copy of
Compuware's DevPartner
>Profiler, Community Edition ( www.compuware.com ). Then
you can profile
>your app to find out what's going on.
>When you say you're "using ADO.NET", what does that
mean? Are you using a
>data reader? Data adaptor? What are you doing with the
data once it's[vbcol=seagreen]
>loaded into the reader/data set?
>
>"Jon Rista" <jrista@.hotmail.com> wrote in message
>news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
suggested I post[vbcol=seagreen]
perform a process on[vbcol=seagreen]
through Query
>
>.
>