Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Long Time SSIS Package Loading

I load SSIS package using following code:

Application app = new Application();
Package pac = app.LoadFromSqlServer(packageName, serverName, null, null, null);

For simple package containing 2 tasks this code executes about 20 seconds.
If I load old version (SQL2K) package from SQL2000 then it takes 5 seconds.

Is any way to increase loading speed for SSIS packages?

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
SSIS Package Validation Taking A Long Time
I have SSIS Projects taking a long time to open with packages with a large number of data flows.   Is there a way to turn off validation of metadata when a package opens?   Turn off validation during execution on SSIS Service (after previously validated in dev)?   Or be able to control when validation takes place in general?  
In my one package (1 of 5) I have 43 data flows (with a single source to target mapping) in 4 sequence containers, and it takes approximately 2-3 seconds per source to target mapping and sequence container to validate which will translate to 1 ½ to 2 ½ minutes to open.   When the project with all 100+ tables for the data warehouse goes through validation, I can make coffee in the time it takes to open the project.    I have to delete *.suo file (or verify all packages are closed in the designer and save the project file), and when I open the project, I have to jump immediately to SSISàWork Offline to set it to not validate the metadata to be able to work in a timely fashion.   DelayValidation=TRUE does not help much.   
Running in debug mode, has an effect of causing packages that were not open and validated to go through validation though I am not running those packages.   Validate once during design and run forever.   
Even if I re-open a package that I just closed from designer and had gone through validation, it will go through the validation process again.    
It would be great if there could be an on-demand option off the menu bar to allow one to control when validation can take place for a project, or a more granular validation option for a specific data flow or container.   

View Replies !   View Related
Loading Data Taking A Long Time
I would like your opinion on how to speed up the database loading for a SQL Server Application. The application is designed
to allow smaller sites to load their data into Access 97 databases and larger sites to load data into SQL Server 7.0. The data
is coming from a mainframe legacy database. It is coming to both databases by tcpip and a product named DB2Connect.
I am shocked by how much faster the Access database loads the same amount of data. Time differences are given in the note
below. The tables loaded will have several thousand rows and will be identical on both Access and Sql Server. Access is not
local to the user, it is on a network drive so that is not a possible reason for the speed.
From attending a recent SQL Server class, some of my ideas were to use fixed memory on SQL Server and turn the update
statistics off during loading. Neither change made any difference. What about row level locking and primary keys, is this
causing overhead? Should primary keys be applied after the load? Is this even possible?

A description by a programmer for the application follows:

The numbers are very close to 22 minutes on SQL Server and 1.5 minutes on Access. Same amount of data.
For Access, Jet Engine 3.51 and ADO 2.1 are being used.
For SQL Server, "OLEDB Provider for SQL Server" is being used.
Visual basic is using the "Find" method on the "Recordsets" of data that were populated using SQL.
The Find method is a way of searching through data returned from a database and is known to be a bit slow,
but if it were causing the problem, I would expect to see the slowness on Access as well. All data inserts are done
using recordsets(AddNew and Update calls) instead of SQL because the database can't be updated until everything
is complete and this keeps all the data in local memory.

Any ideas would be appreciated.

View Replies !   View Related
Report Takes Long Time Loading
I have a report which is fairly simple but takes a very long time..

It involves the incidents being counted by categories hence it has several Union All.

Also the report numbers are generetd through 2 tables hence within every Union All tehre is a left or an Inner join.

sample code:

1 Sort_Order,
COUNT(*) AS Call_Count,
'Incident Resolved at Level 1' AS Count_Type
FROM HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1 T1
  AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)


2 Sort_Order,
COUNT(*) AS Call_Count,
'Incidents Resolved at Level 2 or 3' AS Count_Type
FROM HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1 T1
   AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)



could you suggest what might be the reason why teh report churns for so long.




View Replies !   View Related
Package Run Takes A Long.......... Time!
Dear friends,
Our package which at the time of normal execution takes 2-2:30 mins for fetching data on VPN with some select queries. But some times its job runs for hours and hours. What could be the exact reason behind it? I guess its queries are stuch somewhere, but not when we run from the BIDS or run the job manually.
Please help. Thanks.

View Replies !   View Related
SSIS Configuration Not Loading First Time.
Hi guys,
I've got an interesting problem.
I've got a simple web app that allows the user to run a package from a web front-end.
The package configurations are stored in SQL Server, and the package configurations are set up to point to the
correct database where the rest of the configurations are. A call is done through a webservice to run a package.
The webservice uses the DTS Runtime library to load and execute packages (LoadFromDTSServer and Package.Execute).
Now, the problem. On one page I display a list of packages available in the SSIS Repository (by making a call to the webservice which uses GetDTSServerPackageInfos to obtain a list of packages). A particular package is selected and then executed. This works fine.
However, I have another page which doesn't provide the user with a drop down list, and just executes a particular package.
Now for some reason, some times the package fails - indicating that for some reason the package configuration wasn't applied to the package. Now if I go to the page with the list of packages and then come back it works!
It seems like the first time the configuration doesn't get applied to the package, and then when you go and get a list of packages and come back then the configuration gets applied.
Anyone any ideas? Could it be that for some reason before I execute the package I must do a GetDTSServerPackageInfos call?
Any help will be greatly appreciated,

View Replies !   View Related
First Time SSIS Long Time DTS
A few pointers would be appreciated.

I am looking at building multiple SSIS packages. There will be some similarities. Flexibility is of highest importance. The main packages will need to connect to SQL Server1 as a source and SQL Server2 as a destination to transfer over dimenion data from multiple databases. (other SSIS packages may need to use SQL Server2 as a source and SQL Server1 as a destination)

For a single dimension table containing column dim_id on the target server (SQLServer2). I need to pass the results of the following SQL and insert into SQLServer2.database.dim_table

from SQLServer1.database08.dim_table
from SQLServer1.database07.dim_table
from SQLServer1.database06.dim_table

Now next year the names of the databases on SQLServer1 will be database09,database08,database07!

Now so far my best thought is creating views in my destination SQL Server. So I need some way of dropping and recreating the views. Previously in DTS I would expect to see SQL Server connection that I could use as source and destination. Now I can see SQL Server destination but not source? Also How do I just use SSIS to run some SQL. i.e execute a stored procedure, drop and creat views?

Many thanks,
p.s Flexibility is the key, in the last three months all the ip and server names have changed more than once so need to be as flexible as possible.

View Replies !   View Related
SSIS Long Validation Time

I would like to know how to disable valiation of SSIS when the SSIS runs in server.
The SSIS takes about 20% of time to validate the environment.
Are there any configuration in SSIS to tell SSIS the environment is stable and no validation is required?

View Replies !   View Related
Error When Loading SSIS Package
Hi all,

I have problem with SSIS. When I open my packages, I always get an error message which says like this:
There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details.

And, in error list, I get this error message:
Error loading DIM_BUSINESS_TYPES.dtsx: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E4D  Description: "Login failed for user 'user'.".

But the package itself seems doesnt have any errors at all. When I try to run the package, it runs fine.
Btw, I use package configuration which is stored in sql server table, including the password for connectionstring.

So, what's actually wrong with my package?


View Replies !   View Related
Assertion Failed Message Loading SSIS Package In
I have SSIS packages created to import .xls files into sql tables.  I now have code within which I am trying to execute individual packages whenever my code notices the .xls files being deposited within a network folder.  When I try to run my code I get the error message:

Assertion Failed:Abort=Quit, Retry=Debug, Ignore=Continue
at STrace.ReadTraceValues()
at STrace..cctor()
at STrace.Trace(String strComponentName, String strLine)
at ManagedHelper.GetNextManagedInfo(DTS_Managed_INFO&nextManagedInfo)
here is my code:

Sub RunPackage(ByVal pkgCMD As String)


Dim app As New Application

Dim pkg As New Package

Dim pkgResults As DTSExecResult

'Dim pkgevents As IDTSEvents


pkg = app.LoadPackage(pkgCMD, Nothing)

pkgResults = pkg.Execute()

The error occurs during the pkg=app.LoadPackage(pkgCMD, Nothing) statement.  Any idea of how I determine why the SSIS package will not load thru my vb app?  It runs fine if I load the SSIS package in SSBIDS.
Thanks for any help or guidance.

View Replies !   View Related
Problem Loading And Executing An SSIS Package With Too Many Lookups In BIDS
I have an SSIS package with around 25 lookups. Developing the package itself was slow. Now, everytime I try to load the package it takes forever and whenever I execute it I get an error.
Here are my questions:
1. Is there a way I can optimize the package?  
2. Is it abnormal to have so many lookups? I am loading a dimension table with many fields and I need to look up on 25 tables to get the keys. I know one alternative is to use left joins in the source query and get the keys in the Source itself but we can have more visibility of what's happenning with Lookups. I would like to know other possibilities with lookups.


View Replies !   View Related
Error Loading SSIS Package - CPackage::LoadFromXML Fails
When I try to load an SSIS package via C# code I get the following error message:-

"The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.

The code is pretty straightforward:-

Application app = new Application();

PackageEvents evts = new PackageEvents();

Package pkg = new Package();

Package pkg = app.LoadPackage("C:\Documents and Settings\dominic_s\My Documents\Visual Studio 2005\Projects\SSIS_Util\bin\Debug\DTS\Cleanup_Staging.dtsx",evts,true);

return pkg;

What's interesting is this error message only appear's when I Start Debugging (F5 in VS 2005). If I Start without Debuggging (CTRL+F5)  I dont get this error message. I've tried almost everthing suggested by other posts in this forum related to the same issue but nothing seems to work.

Any help will be greatly appreciate...thanks!!!

View Replies !   View Related
Running Ssis Package With Ssis Run Time Compoenents And Sql Server 2000...
running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

If you links or documentation which explain this scenario then it will help me to forward it to our DBAs and do the needful.

thanks a lot


View Replies !   View Related
Running Ssis Package With Ssis Run Time Compoenents And Sql Server 2000...
running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

Please explian with links if possible

thanks a lot

View Replies !   View Related
Load A SSIS Package Via Web Service: The Package Failed To Load Due To Error 0xC0011008 &&"Error Loading From XML.WHAT IS THAT?

I have a big problem and i'm not able to find any hint on the Network.

I have a window2000 pc, VS2005,II5 and SQLServer 2005(dev edition)

I created an SSIS Package (query to DB and the result is loaded into an Excel file) that works fine.

I imported the dtsx file inside my "Stored Packages".

I would like to load and run the package programmatically on a Remote Scenario using the web services.

I created a solution with  web service and web page that invoke the web service.

When my code execute:
Microsoft.SqlServer.Dts.Runtime.Application.LoadFromDtsServer(packagePath, ".", Nothing)

I got the Error:
Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails.

The error message doesn't help so much and there is nothing on the www to give me and advice....

Is it a SSIS problem???

Thank you for any help!!

Marina B.



View Replies !   View Related
SSIS Package Run Time
Hello all, I have a SSIS package that is importing data from a DB2 database.  I am using SSRS (BIDS/VS2005).  I want to be able to access the last time a particular package ran in my report, like in the footer of the page.  I have found the globals.executiontime for grabbing the time the report was ran. 

I want to do the same thing, except I want to call the date/time the last import was made to the database.  Is there some easy way to grab that from SSIS, or will I need to maybe reference the DB creation time?  (The database is dropped and recreated (for now anyway) in the SSIS package.)


Thanks for your help.


View Replies !   View Related
SSIS Package Fails To Load With &&"Error Loading Value &&<DTS:PropertyExpression ...&&"
Good day all,


I am having an interesting SSIS problem where the package fails to load with the following error message:

  Code: 0xC0010018
   Source: {BE86A659-AB44-403A-9C89-3524821879E0}
   Description: Error loading value "" DTS:Name="SqlStatementSource">"Select dbo.fnGetLastOpenExtract('" +  @[User::in_ExtractName] + "') as eh_ID"" from node "DTS: PropertyExpression".

This very same package runs on our test server, but fails to even load on UAT server.

SSIS packages are the same on both Test and UAT servers (I compared not just dates and sizes - they are literally the same: byte-to-byte)
DTExec version is 9.00.3042.00 on both servers.
HKLMSOFTWAREMicrosoftMicrosoft SQL Server90DTSSetupVersion = 9.2.3042.00 on both machines.

This started to happen when the UAT machine was upgraded to Service Pack 2 of SQL Server 2005. Please note that the UAT server only runs SSIS packages and does not have SQL 2005 database engine installed. There is, however, an older installation of SQL Server 2000 on UAT machine (I am not sure if Test machine has it - will check tomorrow).


Any help is greatly appreciated.





Here is the compete output from DTExec:

Code Snippet

D:AM5Jobs>"C:Program FilesMicrosoft SQL Server90DTSBinnDTExec.exe" /File "D:ExtractsGBG_ExtractSSISImport_ExtractStartComplete_03.dtsx"  /Checkp OFF  /Cons MT  /Set Package.Variables[User::in_ExtractName].Properties[Value];SagittaMapping_Replication /Set Package.Variables[User::in_StartComplete].Properties[Value];Start
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  12:33:21 PM
Error: 2007-07-17 12:34:52.98
   Code: 0xC0010018
   Source: {BE86A659-AB44-403A-9C89-3524821879E0}
   Description: Error loading value "<DTS:PropertyExpression xmlns:DTS="" DTS:Name="SqlStatementSource">"Select dbo.fnGetLastOpenExtract('" +  @[User::in_ExtractName] + "') as eh_ID"</DTS:PropertyExpression>" from node "DTS:PropertyExpression".
End Error
Error: 2007-07-17 12:34:52.98
   Code: 0xC0010018
   Source: {BE86A659-AB44-403A-9C89-3524821879E0}
   Description: Error loading a task. The contact information for the task is "Execute SQL Task; Microsoft Corporation; Microsoft SQL Server v9; ? 2004 Microsoft Corporation; All Rights Reserved;;1". This happens when loading a task fails.
End Error
Error: 2007-07-17 12:34:52.98
   Code: 0xC0010021
   Description: Element "{1c66489c-2a3f-4c8a-b9e7-0161875427a2}" does not exist in collection "Executables".
End Error
Error: 2007-07-17 12:34:52.98
   Code: 0xC0010018
   Description: Error loading value "<DTS:Executable xmlns:DTS="" IDREF="{1c66489c-2a3f-4c8a-b9e7-0161875427a2}" DTS:IsFrom="-1"/>" from node "DTS:Executable".
End Error
Error: 2007-07-17 12:34:52.98
   Code: 0xC0010018
   Description: Error loading value "<DTS:PrecedenceConstraint xmlns:DTS=""><DTS:Property DTS:Name="Value">0</DTS:Property><DTS:Property DTS:Name="EvalOp">2</DTS:Property><DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property><DTS:Property DTS:Name="Expression"></" from node "DTS:PrecedenceConstraint".
End Error
Could not load package "D:ExtractsGBG_ExtractSSISImport_ExtractStartComplete_03.dtsx" because of error 0xC0010014.
Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
Started:  12:33:21 PM
Finished: 12:34:53 PM
Elapsed:  91.938 seconds




View Replies !   View Related
SSIS Package Taking Much Time Than DTS

I have a DTS in SQL Server 2000, Where I am importing some data from a remote server (SQL 2000)  to local server (SQL 2000).And this is working fine. it is taking max of 1 min to execute the package.
Now I have created the same DTS in sql server 2005 (SSIS) where the source server is sql server 2000 and the destination server is 2005.and I have created the ssis in that server. The same logic which i have created in sql 2000. But here it is taking almost 10 min to execute the package.
Where as the same in sql server 2000 taking max of 1 min. Why this happening.. Is there any configuration to execute the SSIS package.?

View Replies !   View Related
SSIS Package Level Limiting By Time

Can I limit an SSIS package that should run only for specific time, if it exceeds then should stop.
Ex. mySSIS.dtsx should run only 5 min, if exceeds 5 min it should stop running and exit.

View Replies !   View Related
It Takes A Long Time To Insert The First Record Each Time When The Program Start
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.

The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?

I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.

        sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
        scb = New SqlCeCommandBuilder(sda) 

I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:

1. Start the PPC Program

2. Load DB into dataset

3. Create a ONE new record in dataset

4. Fill back to DB

When I take this four steps everytime, the filling time is almost 1s or even more!

Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)

 However, when I give up the dataset and using the following code:

            Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)

           cmd.CommandType = CommandType.Text
            StartTime = Environment.TickCount

 I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!

View Replies !   View Related
Availability Of SSIS Run-time Support (Redistributable Package)?
Can somebody please help answering this question?


I'm planning to consider writing a SSIS package for a new project that requires downloading large chunk of data and transform into the diverse databases such as MS SQL or Oracle depending on the Client's Datbase.


For the clients having SQL Server installed at their end, i had no issues in deploying this package on their server and run it in their licensed instance.


What should be the case for others having Oracle database? Wouldn't installing the SQL 2005 client tools install the necessary run-time services for running SSIS packages? What i understood from the MSDN library ( is that there's no run-time support available for running the SSIS packages (unlike DTS run-time support) in production environment!


Would that mean that it requires a SQL Standard edition, at minimum, (as Integration Services is OOTB from Standard Edition onwards) to be installed at the production site to run this package?


If so, the client wouldn't be ready (which is fair too) to buy the new license just to run this package. Is there any work-around/suggestions for this case?


If not, can somebody please point me to the right location where i can download the run-time support for running SSIS packages?


Any thoughts on this is highly appreciated.

Thanks for your time.



View Replies !   View Related
SSIS Package (Data Flow) Validation Time Going Exponential
I am using SSIS to populate a star schema. 
The issue is in the data flow for loading and setting the Fact table dimension keys (the dimensions are all loaded fine).  After 16 rather pedestrian Lookup Transformations, I have an escalating problem adding additional Lookup transforms to the Data Flow.  The problem is not in execution; the problem is adding more transforms in design mode.
Lookup #             Fields in Data Flow           Time to validate that lookup
<17                         47                                           Sub-second
17                           48                                           2 sec
18                           49                                           4 sec
19                           50                                           8 sec
20                           51                                           16 sec
21                           52                                           32 sec
22                           53                                           64 sec
While I€™m intrigued by the mathematical progression that is forming here, the issue is that I have at least 6 more Lookups to perform.  I hope you can see my dilemma. 
I have gone to where it takes a little over 4 minutes each to validate the lookup transform and its associated Derived Column transform and Union transform (Total 12 Minutes).  Not only does this add up to many idle minutes to each design step, BUT it breaks the debugger as it pre-validates the ENTIRE data flow before it ever switches into debugging mode.
Some notes:
1.        It doesn€™t matter what order the Lookup transforms occur in, the timings are exactly the same.
2.       I tried many Data Flow execution optimizations, but they don€™t improve the validation times (or even get a chance to improve the execution times!)

I realize this may be somewhat of a unique problem. 


Thanks for any help you are able to lend.



View Replies !   View Related
MS Time Series - Quick To Process The Model But Takes Very Long Time To Open Mining Model Viewer
Hi all,

I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?

View Replies !   View Related
SQL SP Takes Long Time To Run From VB.NET App
Hi all,

I have a stored procedure that is called from a VB.NET application that takes an enormously long time to execute. In the QA it only takes 10sec but in the application it takes ages. The stored procedure is as follows:



The code that runs it is as follows:

Using cnn As New SqlConnection(GetPath)
Dim cmd As New SqlCommand(sSQLString, cnn)
Dim dr As SqlDataReader

With cmd
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 0
.Parameters.Add("@BEGINDATE", SqlDbType.DateTime)
.Parameters.Add("@ENDDATE", SqlDbType.DateTime)
.Parameters("@BEGINDATE").Value = dtpStartDate.Value
.Parameters("@ENDDATE").Value = dtpEndDate.Value
End With
dr = cmd.ExecuteReader

Any help on why this happens would be much appreciated.


View Replies !   View Related
SMO-IsSystemObject Takes Long Time
   I am using SMO objects to iterate stored procedures of a database. My database contains some 200 user stored procedures and some 1300+ system stored procedures. In a for loop, for each procedure i am checking whethere it is a system stored procedure by IsSytemProcedure property. If i use this property it takes too long time to complete the loop. If i remove that line it is fast.

foreach (StoredProcedure sp in db.StoredProcedures) {
     if(sp.IsSystemObject == false) {//this line takes too long time, removing this seems ok

//doing something


View Replies !   View Related
Long Time Database Processing In ASP.NET ?
 I made a website in and using sql server 2005 as database. There is sometime processing data that need long time processing ( about 20 minutes ) and big data. It works fine in dev box, but when I place on shared hosting, and some people access it  crashed. The website can not be accessed. Hosting support told me maybe I need to reprogram my code. So anybody has solution for this problem ? Should I create new thread ?  

View Replies !   View Related
Long Time To Establish A Connection
When I login using QA to my SQL Server database, it takes 15-20 secondsto establish a connection and open a query window. Drilling into adatabase via Enterprise Manager is similar. Once the connection isestablished, the server runs plenty fast however.Can someone tell me why it could take a long time for a connection tobe established?This behavior occurs when I am local on the box.Thanks,John

View Replies !   View Related
Inserting Nothing Takes A Long Time
Hi,I've a strange problem with a INSERT query. It's taking a long time toexecute. The format is like this :INSERT INTO table1SELECT ..FROM table2Executing the SELECT .. FROM table2 is taking 30 seconds. The resultis nothing: no records are selected.When i include the INSERT part it will take 12 hours to completeINSERT INTO table1SELECT ..FROM table2There's is an index on the table and when i delete it, it gives stillthe problem.Keh?Greetz,Hennie

View Replies !   View Related
Long Time To Run A Stored Procedure
Hi There,
We have developed a application in VB and connected to SQL Server 6.5, we have some stored procedures where it brings the data from SQL Server 6.5, this application is running since some months, when we run this application it usually take only one minute to generate the report but since couple of days it is taking 25 Minutes to generate the report, even when I run that stored procedure at backend in Query analyzer at Server it is taking 15-20 Minutes to give the result.
please can any one help in identifying the problem, What all the things I need to check to identify it.
Give me the solution.

Thanks in Advance,


View Replies !   View Related
Help: Scheduled Job Running For Long Time
Hi there!,

Problem: I schedule a job that calls a stored procedure which loads around 1.5 million records. The Job takes 19 hrs to complete. However, if i run that stored procedure manually in Query Analyser it takes only 45 minutes..

Did anyone faced this problem? Is this known problem..Any suggestions/recommendations?

thank you.

View Replies !   View Related
RESTORE Taking A Long Time
Please copy all replies to

Each month, we roll a backup of our production database over our development and test environments. Our database is in the 10-15 GB range. Each environment is on a separate server. In 65, this operation could be accomplished in under an hour by restoring a "disk" backup on server A to a database on server B.

With SQL 70, this has run for over 3 hours. The backup itself didn't take that long. If anyone has any idea of what may be happening, or if there are other factors in 70 that I need to consider, please let me know. Below is my SQL code:

restore database QueryDev
from disk = 'amopquerysqlbackupqueryprodqueryprod_db_1999081 82001.bak'
WITH MOVE 'queryprod_data' TO 'd:sqldataquerydev_data.mdf',
MOVE 'queryprod_data2' TO 'd:sqldataquerydev_data2.ndf',
MOVE 'queryprod_data3' TO 'd:sqldataquerydev_data3.ndf',
MOVE 'queryprod_log' TO 'd:sqldataquerydev_log.ldf',
STATS = 10

Thanks, Buddy

View Replies !   View Related
Database Search Time -too Long
I had a database of electronic resources which had 28000 records earlies and was working fine. Now we have added a whole bunch to make it 800K records which has increased the search time to 14-22 seconds which is not acceptable. I have all the tables indexed.

Please help me how to solve this problem. Let me know what other information I should put up here to make my problem undestandable.
Thanks in advance,

View Replies !   View Related
Help-take A Long Time Run This Stored Procedure 15 Second
help-take a long time run this stored procedure 15 second

Code Snippet

DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP


set @myNum=4

SELECT @WantedDate = CAST(CAST(YEAR(GETDATE()) AS nvarchar)+ '-' +CAST(1 AS nvarchar)+ '-' +CAST(@myNum AS nvarchar) AS SMALLDATETIME), -- User supplied parameter value

@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),

@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))

select @WantedDate

IF @NumDays = 28


SELECT dbo.v_hasot_all.empID, dbo.v_hasot_all.Fname, dbo.v_hasot_all.day1A, dbo.v_hasot_all.nameday, dbo.tb_mis_hsaot.mishs_txt, CONVERT(datetime,

CAST(dbo.v_hasot_all.nameday AS nvarchar) + '/' + CAST(@myNum AS nvarchar) + '/' + CAST(YEAR(GETDATE()) AS nvarchar), 103) AS date_mis

FROM dbo.v_hasot_all INNER JOIN

dbo.tb_mis_hsaot ON dbo.v_hasot_all.day1A = dbo.tb_mis_hsaot.mishs_int

WHERE (NOT (dbo.v_hasot_all.nameday IN (29, 30, 31)))


ELSE IF @Numdays = 29


SELECT ...............

WHERE (NOT (dbo.v_hasot_all.nameday IN ( 30, 31)))


ELSE IF @Numdays = 30


SELECT ......................

WHERE (NOT (dbo.v_hasot_all.nameday IN (31)))


ELSE IF @Numdays = 31


SELECT dbo.v_hasot_all.empID, dbo.v_hasot_all.Fname, dbo.v_hasot_all.day1A, dbo.v_hasot_all.nameday, dbo.tb_mis_hsaot.mishs_txt, CONVERT(datetime,

CAST(dbo.v_hasot_all.nameday AS nvarchar) + '/' + CAST(@myNum AS nvarchar) + '/' + CAST(YEAR(GETDATE()) AS nvarchar), 103) AS date_mis

FROM dbo.v_hasot_all INNER JOIN

dbo.tb_mis_hsaot ON dbo.v_hasot_all.day1A = dbo.tb_mis_hsaot.mishs_int

and whan i run my code separately it run fast
i think it is the ELSE IF @Numdays


View Replies !   View Related
Queries Take A Long Time After Sp2 Installation
Has anyone had thsi issue, if so what was the fix?


We installed service pack2, the day after 2 of our production jobs started taking a long time to complete and causing a ton of blocking.


it went from running in 2 minutes to now taking 3 hours and 29 minutes to run.  Can someone help?

View Replies !   View Related
Restore Taking Very Long Time
i have sql 2000 db of about 120 GB.  its taking about 10 -12 hours to restore on the same disk as new database.


server configuration is good.


when i try to restore another db of about 10 GB size, its restoring in about 5 minutes.

View Replies !   View Related
Dm Query Taking Long Time
I'm running a query (see below) on my development server and its taking around 45 seconds. It hosts 18 user databases ranging from 3 MB to 400 MB. The production server, which is very similar but with only 1 25 MB user database, runs the query in less than 1 second. Both servers have been running on VMWare for almost 1 year with no problems. However last week I applied SP 2 to the development server, and yesterday I applied Critical Update KB934458. The production server is still running SQL Server 2005 Standard SP 1. Other than that, both servers are identical and running Windows 2003 Server Standard SP 1. I'm not seeing this discrepancy with other queries running against user databases.


use MyDatabase


select db_name(database_id) as 'Database', as 'Table',

s.index_id, index_type_desc, alloc_unit_type_desc, index_level, as 'Index Name',

avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages,

page_count, avg_page_space_used_in_percent, record_count,

ghost_record_count, min_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count,

schema_id, create_date, modify_date from sys.dm_db_index_physical_stats (null, null, null, null, 'DETAILED') s

join sys.objects o on s.object_id = o.object_id

join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id

where db_name(database_id) = 'MyDatabase'

order by avg_fragmentation_in_percent desc

--order by avg_fragment_size_in_pages desc

--order by page_count desc

--order by record_count desc

--order by avg_record_size_in_bytes desc

View Replies !   View Related
Long Execution Time When Using Parameter
Hi all,
If I use the following query for a Dataset and the execution takes a few seconds to show results



FROM dbo.ICParameter

WHERE (PatientID = @ID ) AND (LogTime > DATEADD(day, -1, GETDATE()))


If I replace '99010200101' with @ID and enter '99010200101' when prompted for ID, the execution takes forever.  Actually I have never got any results even after waiting for 10 minutes.
Could anyone shed some light on this?
Thanks in advance.

View Replies !   View Related
ExecuteQuery Taking A Long Time

I have a query which returns approximately 50000 records, I am using a linked server to connect to two databases and retrieve data. For some reason it is taking a liitle more than hour to execute the query, but on MS Sql Server query window it comes after few minutes but the query runs for a long time.

How can expediate my query execution process.

Environment details

Database: MS Sql Server 64bit 2005
MS Sql jar file: sqljdbc_1.2.jar
OS: Windows both server and client.

Connect String in java code:


and use PreparedStatement and ResultSet.


View Replies !   View Related
Why Insert Takes A Long Time?
It seems inserting records takes a relatively long time.   My guess is it needs time to allocate disk space for the extra space needed.  Assuming this is true, are there any DB settings that allow auto space allocation in bigger chunk?  I am looking for something like "DB growth factor"  or  " Table growth factor"
Thanks for any info.

View Replies !   View Related
C# Hanging For A Very Long Time When Working With SQL
I am designing a project using C#, SQL Express and VSTO.


Every time I change something in the database XSD display i.e add a query or create a new table adapter or configure an existing query, or just move the position of an existing table adapter, Visual Studio locks intself into a never ending loop with 100% processor activity (or 50% with dual processors).  It stays in this state for about 4-5 hours and then come back to life.


Is there an option that I need to disable/enable to stop the program from doing this. I will appreciate your help.




View Replies !   View Related
Job Running SSIS Package Keeps Failing But The SSIS Package By Itself Runs Perfectly Fine
Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.

Thank you


View Replies !   View Related
Sleeping Processes Take A Long Time To Kill-
Hello all. I have many connections that are sleeping. I can rightclick, kill, up to 2 at a time. It takes about 20 seconds to kill it.Is that normal? I tried to make a stored procedure to kill them but itate all the power of the server, (all users yelled at me at the sametime :o) )Is this length of time normal for killing these processes?ThanksJeff

View Replies !   View Related
EMC Replication Manager Long Recovery Time
Are anybody using EMC Replication Manager on SQL Server 2000?I use this SAN based replication solution to make replicas ofproduction databases, and then attach these database replicas toanother server (serverB) for backup by NetBackup.All is fine except when 1 day, the attachment of one of databases (asmall one) is taking a long time (>1.5 hours) to recover after theattach process and is holding the whole backup operation. Usually ittakes less than 1 minute to attach and recover that database onserverB.I wonder if anyone has the same kind of experience.Tks

View Replies !   View Related
Update Takes Long Time To Complete!?
Hi There,I have an update statement to update a field of a table (~15,000,000records). It took me around 3 hours to finish 2 weeks ago. After thatno one touched the server and no configuration changed. Untilyesterday, I re-ran it again and it took me more than 18hrs and stillnot yet finished!!!What's wrong with it? I can ran it successfully before. I have triedtwo times but the result was still the same.My SQL statement is:update [all_sales] aset a.accounting_month = b.accounting_monthfrom date_map bwhere a.sales_date >= b.start_date and a.sales_date < b.end_date;An index on [all_sales].sales_date is built successfully.A composite index on ([date_map].start_date, [date_map].end_date) isbuilt successfully.My server config is:SQL Server 2000 with Service Pack 3Windows 2000 with Service Pack 4DELL PowerEdge 6650 ServerDUAL XEON 1900MHz Processors2G RAM2G Page File on Drive C2G Page File on Drive DDELL Diagnostics on all SCSI harddisks were all PASSED.Any experts could simly give me a help????Thanks x 1,000,000,000

View Replies !   View Related
MS Access && MS SQL Server: Long Time Answers
I use MS ACCESS and tables linked to MS SQL SERVER tables.
There are indexes defined (they are visible in MS ACCESS too).
Sometimes DLOOKUP functions are taking long time.

View Replies !   View Related
Stored Proc Taking A Very Long Time
I have a stored procedure that normally takes about 5 hours to complete:
DELETE tblX WHERE PROC_DT < dateadd(day, -93 , getdate())

tblX has about 55 million records and has an index on PROC_DT.

I have this running as a scheduled task. Over the weekend, the task executed and it is still running 56+ hours later. Does anybody have any ideas as to where I should look for the problem? I am afraid to kill the process because of the rollback time.

View Replies !   View Related
SQl 6.5(long Query Time Using Dynamic SQL)- Urgent !

In SQL server 6.5(Dynamic SQL)

Select col1 from dbo.table1 where col2 = ?
There is a cluster index on col2. It takes 15 seconds to get the output.

Case2: If the same is run from Isql- it takes less than 1 second.
In SQL server 7.0 the output is faster using Dynamic SQL and ISQL.

PLease advise, what is wrong in SQL 6.5 and is there a way to correct that?


View Replies !   View Related
Update Taking Long Time In 2000 Then SQL 7.0
I have a table with 48 million rows,when i executed following update query it is taking 10 HOURS in SQL SERVER 2000 with SP1.
Where as when i executed same query in SQL SERVER7.0 with same table then it is taking 13 MINUTES. Comming to Machine...SQL 2000 Server has more processors and greater memory than SQL 7.0 m/c.
It looks strange but this is true.Does any one faced such there any bug in SQL 2000?????

Here is Query::

update cus_pay_jan_dist set univ_regdate = b.dayid
from cus_pay_jan_dist a with (nolock), tm_dayids b with (nolock)
where a.univ_regdate = b.dayidnum and a.univ_regdate like '2001%'


View Replies !   View Related
MSSQLSeverOLAPServieces Is Taking A Long Time To Start
Sometime is necessary to stop MSSQLSeverOLAPServieces to do a full backup in my OLAP Server disks. After backup had finished and I tried to star MSSQLSeverOLAPServieces but it takes almost 30 minutes to the services starts.
What can it be causing that?


View Replies !   View Related
SQL7 - MSAccess97 And Date Time (LONG)

Here are the particulars: I have an MSAccess DB acting as a GUI front end to a SQL7 DB using linked tables. MSAccess uses an update command to post form variables to the SQL7 DB. One of the variables in Access is formatted as Date/Time and the field in SQL7 is formatted as smalldatetime. This field is critical as it is part of the primary key.

Here is the problem: MSAcess passes the seconds 07/20/2000 10:20:37 to SQL7 but SQL7 posts 07/20/2000 10:20:00 to the table as if it were rounding to the nearest minute.

Is there something I can do to make SQL7 keep the seconds as they are passed from Access?

Thank you in advance.

Stephen Michl
Senior Web Programer/DBA
ASM Research Inc

View Replies !   View Related

Copyright © 2005-08, All rights reserved