SSIS Designer Freezing Up When Using The Debug Utility !

I am executing a single package that references 180 other packages , after executing the first 90-100 packages , SSIS designer completely freezes and then i have to kill the session using Task Manager . Is this a limitation of SSIS or is it a system constraint ?

If you have any suggestions or workarounds for de same plz do reply

Thanks in Advance
Shailesh



ADVERTISEMENT

SSIS DATA Flow Task Freezing

Hi,

I am experiencing an error where the ssis data flow task would freeze and stop data export from a oledb source to a text file. It doesn't generate any errors the ssis package would just hang. This only happens when I run it in 64 bit mode. When I change the mode to 32 bit the ssis never freezes and runs fine. Has anyone experience this? Is there a fix so I can run my jobs in 64 bit mode?

Thanks,

View Replies View Related

SSIS - Cannot Debug A Script Task

I am unable to step through (in debug mode) a script task.

Have set breakpoint on first line of script in VSA.  Click Start (in debug mode). Nothing happens.

Have also tried to execute task in control flow of visual studio but it will not stop at breakpoint

 

View Replies View Related

SSIS Tutorial Lesson 1: Debug Error

I have followed the instructions for SSIS Lesson 1 exactly but i get these 4 errors when I come to debug at the "Lookup Date Key" lookup transformation. Last step in the lesson.

1. [Lookup Date Key [66]] Error: Row yielded no match during lookup.
2. [Lookup Date Key [66]] Error: The "component "Lookup Date Key" (66)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (68)" specifies failure on error. An error occurred on the specified object of the specified component.
3. [DTS.Pipeline] Error: The ProcessInput method on component "Lookup Date Key" (66) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
4. [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

I have tried this on 2 hardware setups and recreated the package several times on both. The only thing i can think of is that the collations on both servers is SQL_Latin1_CP1_CI_AS and British English (I have huge legacy databases from SQL 7.0 and cannot get my tech support to change language settings of server to UK English). Is it possible that this is causing the lookup failure mentioned above (Q1)? How can I change the collation/language settings within the DTS so that the text file matches the AdventureWorksDW database settings if this is the issue (Q2)?

Are the error codes listed anywhere and if not can they be added BOL (Q3)? I have read other threads and they suggest 0xC0209029 means lookup failed due to differing lengths. Can dates have differing lengths (Q4)?

View Replies View Related

SSIS && Script Transformation (debug Vs Normal)

I just ran across an interesting problem, that makes no sense. I
built
an SSIS package that updates a column, using an transformation
script.
Testing in Debug mode everything runs perfectly, but when I have SQL
sever agent run the package it insert null into the new column.


Any thoughts or suggestions would be greatly appreciated.


r/ Anthony
 

View Replies View Related

Error Running SSIS Pkg From Dtexec Utility

Hi All,

I developed my package in BI studio and tested runnning it through BI. Now when i have to deploy the package to main server it will be executed via batch job. This package requires an input file name too.
Now when i am trying to run the package on my loacal box using following command on command prormpt i get error given below.

COMMAND USED BY ME: DTExec /FILE 'C:ProjectIntegrationServicesSFRIntegrationServicesinpkgSFRLocation.dtsx' /SET Package.Variables[InputParamFileName].Value;'C:DataDumpInputLOC00022'

ERROR MESSAGE:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 1:29:54 PM
Error: 2008-02-22 13:29:54.96
Code: 0xC0011007
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Description: Unable to load the package as XML because of package does not ha
ve a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2008-02-22 13:29:54.96
Code: 0xC0011002
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Description: Failed to open package file "'C:ProjectIntegrationServicesSFR
IntegrationServicesinpkgSFRLocation.dtsx'" due to error 0x800700A1 "The speci
fied path is invalid.". This happens when loading a package and the file cannot
be opened or loaded correctly into the XML document. This can be the result of
either providing an incorrect file name was specified when calling LoadPackage o
r the XML file was specified and has an incorrect format.
End Error
Could not load package "'C:ProjectIntegrationServicesSFRIntegrationServices
inpkgSFRLocation.dtsx'" because of error 0xC0011002.
Description: Failed to open package file "'C:ProjectIntegrationServicesSFRInt
egrationServicesinpkgSFRLocation.dtsx'" due to error 0x800700A1 "The specifie
d path is invalid.". This happens when loading a package and the file cannot be
opened or loaded correctly into the XML document. This can be the result of eit
her providing an incorrect file name was specified when calling LoadPackage or t
he XML file was specified and has an incorrect format.
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Started: 1:29:54 PM
Finished: 1:29:54 PM
Elapsed: 0.047 seconds

PLease let me know solution to creating batch job for executing this pkg with input param.

View Replies View Related

Package Runs Fine In Debug.. Fails Outside Of Debug

I created a package that runs fine while in debug... but when I run outside of debug (just choosing Debug.. Start without debugging) I get an error of:

     Message: The task "Formulate SQL Query and Destination" cannot run on this edition of Integration Services. It requires a higher level edition.

I am on SP1 on my workstation and the only SSIS server I have.  I am not doing anything fancy in my script..  I have created other packages and ran outside of debug mode without issue.  I did uninstall SQL2005 and SSIS from my workstation as it was eating up too many resources.

The script I am running is (removed some of the SQL etc to shorten it):

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Public Class ScriptMain

Public Sub Main()

' Create the SQL

Dim SQL As String

SQL += "SELECT "

SQL += " -- Lots of stuff"

SQL += "FROM "



SQL += CStr(Dts.Variables("gvSourceTableName").Value) & " "

SQL += "WHERE "

SQL += " stuff = '" & CStr(Dts.Variables("gvCurrentSymbol").Value) & "' "

SQL += "ORDER BY date"

Dts.Variables("lvSQLQuery").Value = SQL

' MsgBox(CStr(Dts.Variables("lvSQLQuery").Value))

' Create the filename

Dim Dir As String

Dim FileName As String

Dim FullFileName As String

Dir = CStr(Dts.Variables("gvDestinationRoot").Value) & "" & CStr(Dts.Variables("gvRunID").Value) & ""

FileName = CStr(Dts.Variables("gvCurrentSymbol").Value) & "--.txt"

FullFileName = Dir & FileName

If Not Directory.Exists(Dir) Then

Directory.CreateDirectory(Dir)

End If

Dts.Variables("gvDestinationFile").Value = FullFileName

' MsgBox(CStr(Dts.Variables("gvDestinationFile").Value))

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

View Replies View Related

Error Executing SSIS Package With DTEXEC Utility

 Hi All,

I developed my package in BI studio and tested runnning it through BI. Now when i have to deploy the package to main server it will be executed via batch job. This package requires an input file name too.
Now when i am trying to run the package on my loacal box using following command on command prormpt i get error given below.

COMMAND USED BY ME: DTExec /FILE 'C:ProjectIntegrationServicesSFRIntegrationServicesinpkgSFRLocation.dtsx' /SET Package.Variables[InputParamFileName].Value;'C:DataDumpInputLOC00022'

ERROR MESSAGE:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 1:29:54 PM
Error: 2008-02-22 13:29:54.96
Code: 0xC0011007
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Description: Unable to load the package as XML because of package does not ha
ve a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2008-02-22 13:29:54.96
Code: 0xC0011002
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Description: Failed to open package file "'C:ProjectIntegrationServicesSFR
IntegrationServicesinpkgSFRLocation.dtsx'" due to error 0x800700A1 "The speci
fied path is invalid.". This happens when loading a package and the file cannot
be opened or loaded correctly into the XML document. This can be the result of
either providing an incorrect file name was specified when calling LoadPackage o
r the XML file was specified and has an incorrect format.
End Error
Could not load package "'C:ProjectIntegrationServicesSFRIntegrationServices
inpkgSFRLocation.dtsx'" because of error 0xC0011002.
Description: Failed to open package file "'C:ProjectIntegrationServicesSFRInt
egrationServicesinpkgSFRLocation.dtsx'" due to error 0x800700A1 "The specifie
d path is invalid.". This happens when loading a package and the file cannot be
opened or loaded correctly into the XML document. This can be the result of eit
her providing an incorrect file name was specified when calling LoadPackage or t
he XML file was specified and has an incorrect format.
Source: {9B1FAA88-A6FA-44CF-A2E2-DB3F737FDA65}
Started: 1:29:54 PM
Finished: 1:29:54 PM
Elapsed: 0.047 seconds

PLease let me know solution to creating batch job for executing this pkg with input param.

View Replies View Related

SSIS Deployment Utility - Manifest Doesnt Show Up

When I create a deployment utility I...
- set AllowConfigurationChanges to True.
- set CreateDeploymentUtility to True
- DeploymentOutputPath is set to binDeployment
- I click 'Build' and it creates the binDeployment directory and writes all my DTSX files and my dtsConfig file
But it doesn't create a manifest file. Why not?
Thanks

View Replies View Related

Ssis Designer Immediate Window

Where can I find instructions on how to use it?

View Replies View Related

SSIS Dataflow Designer Bug

When I drop a new component onto the design surface, it appears with dotted lines around it, as it is selected.

But, the F2 key (the hotkey for rename) does not work.

I have to click on some other component, and then back on the new component, and then the F2 key works.

I remember reporting this bug back in the beta cycle, but it is still present even in the release -- I actually think Ã?'m using the SP1 version of 2005, but see version info below to be sure:


Remote terminal services broke my copy buffer again, as is so often does, so pasting in the version info failed -- falling back to manual typing -- SSIS 9.00.2047.00.

View Replies View Related

Enhancement: SSIS Designer

Not quite sure if this is the right place for this comment...

While using SSIS Designer came across a DUE where the package was setup to:

SEQ1 - Truncate staging tables
SEQ2 - Load staging tables (sequence container had been setup to hold multiple data flows)

For some reason one data flow was run before the truncate table sequence.

Looking in the designer it appeared that the dataflow was a member of SEQ2 but looking at the Package Explorer it was outside the sequence container.

It would be good to have some visible indicator  to show that a task is a member/not a member of a container

John.

View Replies View Related

How Install SSIS Designer?

Hi,

I have sql server 2005 express and toolkits installed. However, I only see Report Server projects in my Visual Studio 2005 when I choose a Business Intelligence project.

Will someone please help me download the right file to start using SSIS designer?

Thanks,
Jae

View Replies View Related

Variables In Ssis Designer

Hi there,
 
I have a script which grabs data from a source server. The problem is I have over 50 source servers, so I have to make a script for each one.. EXACTLY the same code but different linked server address.
 
So I was wondering if i can put the server name into a variable so I only need to make one script.
Is that possible?
 
thanks,

View Replies View Related

Visual Studio Output Information In Debug Mode While Running A SSIS Package

Hello

When running a package in VS you can see something like this in the output window:

SSIS package "logging.dtsx" starting.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [1]: The processing of file "C: est ssis loggingad_data1.txt" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Warning: 0x8020200F at Data Flow Task, Flat File Source [1]: There is a partial row at the end of the file.
Information: 0x402090DE at Data Flow Task, Flat File Source [1]: The total number of data rows processed for file "C: est ssis loggingad_data1.txt" is 477.
Information: 0x402090DF at Data Flow Task, OLE DB Destination [1011]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Task, OLE DB Destination [1011]: The final commit for the data insertion has ended.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C: est ssis loggingad_data1.txt" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "DataReaderDest" (87)" wrote 0 rows.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (1011)" wrote 1 rows.
SSIS package "logging.dtsx" finished: Success.

This is exactly when I need when a package is running but I want to be able to see it without using Visual Studio.
I would do it in Reporting Services but I need to find out to get the information. The SSIS logging feature in a package does not provide that kind of info.

Did someone try this already?

Thanks!

Mop

View Replies View Related

SSIS Deployment Utility - Package Configurations Doesnt Show Up??

Hi:

After moving my deployment folder to the Target Server, I run the Installation Wizard. As I move next, I am missing the window which is supposed to allow me to set package config values as stated in MSDN:

"If the package includes configurations, you can edit updatable configurations by updating values in the Value list on the Configure Packages page."

Source: http://msdn2.microsoft.com/en-us/library/ms141802.aspx

Does anyone know why I am not seeing it? In my deployment bundle which I have moved over has currently 3 files:

1) SSIS Deployment Manifest

2) SSIS Package

3) SSIS Config File

Again, I double click on SSIS Deployment Manifest, and it starts fine. I go thru the steps for File System Deployment, and then it prompts for installation folder path. After that, it takes me directly to validation. Why is it not showing me the Configure Packages Page as described in the MSDN Documentation. Please advise. Thanks.

View Replies View Related

Unable To Create A SSIS Deployment Utility For A Solution Which Are Having 2 Packages

Hi All,
 
I have created a solution which contains only 2 packages say Package1.dtsx and Pakage2.dtsx. I want to create a deployment utility to deploy onto other developers machince. I changed the project properties "CreateDeploymentUtility" to TRUE. When I do the build it is not creating the files in "Deployment" folder. It is saying Rebuild All Failed but the error is not showing.
 
For more information the 2 packages have 4 indirect configurations from environment variables which are storing the actual config file path.
 
Am I missing something here?
 
Thanks.
Venkat.

View Replies View Related

[IM001] Error In SSIS Designer

Hi all,

I've been tasked to do some investigation at my workplace as to the best way to move forward with changing from our SQL 2000 DTS' to SQL 2005 SSIS packages.

Our main issue at the minute seems to be that we are using ODBC drivers to pull many of the raw tables which we then manipulate into our own SQL Server tables.

I've had a go at pulling them through the Datareader Source, using the ODBC provider and I recieve the following error;

Error at Data Flow Task [DataReader Source [1]]: System.Data.Odbc.OdbcException: ERROR [IM001]
Driver does not support this function.

Would this be to do with our ODBC drivers being out of date? Does anyone know how to resolve this issue?

View Replies View Related

SQL Server SSIS Designer Installation

How to Install SQL Server SSIS Designer? Is it part of SQL Server Buisness Intelligence?

View Replies View Related

SSIS Project Running In Debug Mode Brings Up The Wrong Script Task In The Debugger

 

This would actually be funny if I weren't under serious time constraints right now. I have an SSIS project with several script tasks in the control flow. I put breakpoints in one of them to allow me to step through and see whats going on. However, when I start the project, the IDE brings up the script editor for a different script task. The breakpoints in this task are actually on the same line numbers as the task I need to debug but the code is all wrong. I checked the task for which the code is being displayed and there are no breakpoints there.
 
Thanks for the assist!
 
Don

View Replies View Related

Removing A Custom Component From SSIS Designer

How do I remove a custom component from SSIS Designer pernamently? I can easily remove it from the tool box, but I want to get rid of it completely.

I tried searching in MSDN for an answer to this question but I could not find it.

Thanks

View Replies View Related

SSIS Package Designer Performance After Installing SP1

Hi,

When I installed SP1 on my SQL Server 2005, the performance of the package designer dropped enormously. I have to wait like 30 seconds after I drag-and-drop a datasource from the toolbox to the design area. Also it takes a very long time to run the very simple package. Not to mention opening some complicated package that was done before applying SP1. The package scheduled in SQL Agent went from 3-4min to 5-6min.

Can anyone address this issue? Maybe there's something I did wrong?

While installing SP1 I ecountered strange behaviour (some messages about file locking, rebooting and stuff), but in the end it said that it was successful

Best Regards
Wojtek

View Replies View Related

Cannot Insert A Variable In SSIS Package Designer

Hello,
This morning I got an odd problem, I just added a new package to my dtproj and I cannot add a variable. Am I missing anything. ?

here is the image

View Replies View Related

SSIS Package Designer Lookup Component GUI Error?

Has anybody seen this issue already?

If you try to "enable memory restriction" from the Lookup component GUI you need to input both 32 and 64 bit size of maximum memory. However when clicking "OK" on the editor you get a message like :

TITLE: Microsoft Visual Studio
------------------------------

Error at Update Execution Logs [Lookup folder path 1 [4429]]: Failed to set property "MaxMemoryUsage64" on "component "Lookup folder path 1" (4429)".

 

------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------


You can only set the properties directly for mthe Properties window (F4).

I think this is a post SP1 bug in Visual studio designer...

 

 

View Replies View Related

SSIS Package Designer Error Displaying The Layout

Our Visual Studio installation suddenly starting producing the error below upon trying to open any .dtsx package; although by "suddenly" I'm sure it was due to some Windows update or other program install, update, or uninstall.  I've spent two days uninstalling, reinstalling, and repairing everything that is seemingly related to VS, but no luck.  I've searched the forums and could not find this specific error mentioned, although I have tried some of the suggestions to fix possibly related 'class not registered' errors, but again no luck.
 
===================================
There was an error displaying the layout. (Microsoft Visual Studio)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=DDSErrorDeserializeDiagram&LinkId=20476
===================================
Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) (msddsp)
------------------------------
Program Location:
   at MSDDS.IDdsDiagram.CreateShape(String ProgID, Boolean Visible, Int32 X, Int32 Y)
   at Microsoft.DataWarehouse.Controls.Interop.AxMSDDS.CreateShape(String progID, Boolean visible, Int32 x, Int32 y)
   at Microsoft.DataWarehouse.Design.ComponentDiagram.OnCreateShape(Object logicalObject, Int32 x, Int32 y, Boolean visible)
   at Microsoft.DataTransformationServices.Design.DtsComponentDiagram.OnCreateShape(Object component, Int32 x, Int32 y, Boolean visible)
   at Microsoft.DataWarehouse.Design.ComponentDiagram.CreateShape(Object logicalObject, Int32 x, Int32 y)
   at Microsoft.DataTransformationServices.Design.DtrControlFlowDiagram.CreateExecutableShape(DtsContainer dtsContainer, Int32 x, Int32 y, Int32 width, Int32 height)
   at Microsoft.DataTransformationServices.Design.DtrControlFlowDiagram.RefreshSequenceExecutables(IDTSSequence sequence)
   at Microsoft.DataTransformationServices.Design.DtrControlFlowDiagram.AfterDeserialize()
 

 
Any help would be greatly appreciated.
 
TIA,
 
Scott

View Replies View Related

Text Qualifier In SSIS Designer For Flat File Source

 
 
Unlike SQL Server 2000 DTS, SSIS Flat File Connection Manager Editor does not provide available list of Text qualifier,
 
i tried
 
-- ""
-- double quote {"}
-- "
--{"}
 
but none of them worked....!
My file sample looks like this
 
"Col1","Col2"
"1234","3456"
"3456","1234"
 
what qualifier should I use then?
 
Many Thanks,
 

View Replies View Related

Running A Large Number Of SSIS Packages (with Dtexec Utility) In Parallel From A SQL Server Agent Job Produces Errors

Hi,
 
I have stumbled on a problem with running a large number of SSIS packages in parallel, using the €œdtexec€? command from inside an SQL Server job.
 
I€™ve described the environment, the goal and the problem below. Sorry if it€™s a bit too long, but I tried to be as clear as possible.
 
The environment:
Windows Server 2003 Enterprise x64 Edition, SQL Server 2005 32bit Enterprise Edition SP2.
 
The goal:
We have a large number of text files that we€™re loading into a staging area of a data warehouse (based on SQL Server 2k5, as said above).
 
We have one €œmain€? SSIS package that takes a list of files to load from an XML file, loops through that list and for each file in the list starts an SSIS package by using €œdtexec€? command. The command is started asynchronously by using system.diagnostics.process.start() method. This means that a large number of SSIS packages are started in parallel. These packages perform the actual loading (with BULK insert).
 
I have successfully run the loading process from the command prompt (using the dtexec command to start the main package) a number of times.
 
In order to move the loading to a production environment and schedule it, we have set up an SQL Server Agent job. We€™ve created a proxy user with the necessary rights (the same user that runs the job from command prompt), created an the SQL Agent job (there is one step of type €œcmdexec€? that runs the €œmain€? SSIS package with the €œdtexec€? command).
 
If the input XML file for the main package contains a small number of files (for example 10), the SQL Server Agent job works fine €“ the SSIS packages are started in parallel and they finish work successfully.
 
The problem:
When the number of the concurrently started SSIS packages gets too big, the packages start to fail. When a large number of SSIS package executions are already taking place, the new dtexec commands fail after 0 seconds of work with an empty error message.
 
Please bear in mind that the same loading still works perfectly from command prompt on the same server with the same user. It only fails when run from the SQL Agent Job.
 
I€™ve tried to understand the limit, when do the packages start to fail, and I believe that the threshold is 80 parallel executions (I understand that it might not be desirable to start so many SSIS packages at once, but I€™d like to do it despite this).
 
Additional information:

The dtexec utility provides an error message where the package variables are shown and the fact that the package ran 0 seconds, but the €œMessage€? is empty (€œMessage: €œ).
Turning the logging on in all the packages does not provide an error message either, just a lot of run-time information.
The try-catch block around the process.start() script in the main package€™s script task also does not reveal any errors.
I€™ve increased the €œmax worker threads€? number for the cmdexec subsystem in the msdb.dbo.syssubsystems table to a safely high number and restarted the SQL Server, but this had no effect either.
 
The request:

Can anyone give ideas what could be the cause of the problem?
If you have any ideas about how to further debug the problem, they are also very welcome.
 Thanks in advance!
 
Eero Ringmäe

View Replies View Related

Report Designer: Copy Result Cells From Generic Query Designer And/or Preview?

Hi,

 

I'm working with reports requiring the Generic Query Designer (complex queries against IBM AS400 via ODBC driver).  I'm using SSRS initially as an ad-hoc query tool, exploring a database and it's data.

 

I've noticed that on the Data tab in Report Designer, when in in Generic Query Designer mode I can't copy the data out from the Result Pane as you can using the Graphical Query Designer.  I find this a bit surprising.  Does anyone now how to achieve this?

 

A viable alternative might be the Preview tab, but I can't see how to copy text from that window either?

 

Do I have to run a Preview and then an Export each time?

 

Mike

View Replies View Related

Query Analyzer Freezing

My 2000 sp3 sql analyzer keeps freezing when closing a window. This is not during a query, and I may not have even edited anything. The entire app will freeze, and after hitting the close button several times, it will get around to an "End Task" popup, and finally close. Doesn't happen every time, doesn't happen with the same number of windows open or the same query open. I have unloaded, reloaded, etc...Still occurs. :confused: Any ideas? TIA.

View Replies View Related

UPDATE Freezing Problem

Hi, i am having a problem with UPDATE.

I have the following table with about 4 million rows:
CREATE TABLE [newauth] (
[authnumber] [int] NULL ,
[batchnumber] [int] NULL ,
[accountnumber] [varchar] (20) NULL,
[authcode] [varchar] (10) NULL ,
[authused] [char] (1) NULL ,
[loaddate] [int] NULL ,
[trandate] [int] NULL ,
[trantime] [int] NULL ,
[cardtype] [char] (1) NULL ,
[mcc] [char] (4) NULL ,
[amount] [int] NULL ,
[transactionnumber] [int] NULL
) ON [PRIMARY]
GO

There is an index on the authnumber field. I am calling the following stored procedure to update the transactionnumber.

CREATE PROCEDURE UpdateAuthWithTrans(@lAuth int, @lTrans int)
AS
UPDATE NewAuth
SET TransactionNumber = @lTrans
WHERE AuthNumber = @lAuth
GO

I need to update about 1 million rows. Approx 50000 calls to this SP work OK. Then the DB freezes. Enterprise mgr shows the following (screen shot attached)

I cant figure out why this is freezing. I have tried update statistics newauth, dbcc checktable, rebuilding the index, rebooting the server.

Any ideas would be appreciated, thanks in advance for any help.

View Replies View Related

JOB FREEZING IN THE EXECUTING STATE

I am having a problem with one of the jobs.The Job freezes in the executing state and takes four to fifteen hours. it only happens sometimes. can anyone suggest the problem i dont seem to get the root of the problem. Kindly give some sugessions.

 

Thanking in Advance

Jacx

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved