I have a scheduled task (a database dump) that shows it is running the Manage Schedule Tasks
window on the running tab but the task is not in sysprocess or current activity.
I checked the msdb database and it is not full, I checked systasks and syshistory and
it shows that the task is no longer running and it completed successfully. The task
did complete successfully, but why is it showing that it is running?
I create a DTS package that copy data from oracle to SQL 2000. In the package, I have 2 connections, 1 transform data task, and 1 execute SQL task to truncate the table before copying the data. The DTS works by right clicking and EXECUTING.
however, it does not work when I run it from a job. The job keeps saying
"Executing...." and it runs for hours when it only takes a few seconds to run using DTS package
DTS package owner is "sa"
Job owner is "sa"
What did I do wrong? Any suggestions are greatly appreciated.
thank you
using SQL7 and we have very occasionally had ascheduled nightly jobhang - it does not fail so we don't get an email it is just"executing" - forever.How could I test for this situation to generate an email if the jobruns longer than say 15 minutes?ThanksBill
View 3 Replies View RelatedI am experiencing on our Sql 7 sp1 database orphaned connections that I cannot kill. Is there another way to kill these connections besides stopping and starting the service. I have been to the microsoft support page and found some information on it but not any relating directly to this problem in sql 7, most in 6.0 and 6.5.
Thanks
Tom
Hello ,
We have a problem running our clean up stored procedure along with our application. When this happens, both the application as well as the job that runs the stored procedure seems to hang.
Here is the explanation of the problem:
The cleanup stored procedure deletes entries from a set of related tables as follows:
1.DELETE FROM TABLE2 WHERE F1 IN ( SELECT F1 from TABLE1 tm WHERE F2 < tm.currentdate AND tm.currentCount = 0);
2.DELETE FROM TABLE3 WHERE F1 IN (SELECT F1 from TABLE1 tm WHERE F2 < tm.curentdate AND tm.currentCount = 0);
3.DELETE FROM TABLE4 WHERE (F1 IN (SELECT F1 from TABLE1 tm WHERE F2 < tm.curentdate AND tm.currentCount = 0) AND CURRENTID IS NOT NULL);
4.DELETE FROM TABLE4 WHERE F1 IN (SELECT F1 from TABLE1 tm WHERE F2 < tm.curentdate AND tm.currentCount = 0)
5.UPDATE T_ TABLE1 SET CURRENTID =null WHERE (CURRENTID IN (SELECT F1 from TABLE1 WHERE F2 < curentdate AND currentCount =0));
6.DELETE FROM TABLE1 WHERE F2 < currentdate AND currentCount = 0;
7.COMMIT;
The application inserts an entry into the 4 tables (TABLE1..TABLE4) in the following order:
1.Invokes a stored proc to insert a row into table TABLE1
2.Invokes a stored proc to insert a row into table TABLE3
3.Invokes a stored proc to insert a row into table TABLE4
4.Invokes a stored proc to insert a row into table TABLE2
The application I refer to is a Java application that uses CallableStatement to invoke the stored procedures and the “java.sql.Connection” has been configured with “auto commit”. Since, the application uses a connection pool, the stored procedures are executed in the context of different “Connections”. The problem happens only with Oracle and not with SQL server.
If you have come across this type of problem, please let me know how you have resolved this problem. Alternatively, if you identified the problem from the description, let me know your suggestions.
Waiting for the precious reply….
Regards
SS
Hello Friends,
A user is calling stating that their application is hung - sitting with an hour glass or with the browser not responding. Assume you know the server and the instance and that the server supports multiple instances.
You need to determine if the database could be contributing to the problem or could be the cause of the problem. Describe at a high level what process you would follow to rule the database out or in as the problem.
Any idea!
Thanks,
Frank
Hello,
I have a process which is hung in Sql Agent 2005. The first step is an SSIS package which loads our data warehouse. The final step of the SSIS package is running a DBCC CHECKDB Task. Looking through the logs I notice that this task ran, and that the package reported Success in the System logs. Sql Agent Monitor shows that it is still executing this step. Moreover, no errors have been reported in the following logs; Sql Agent, Sql Server or the System logs.
We currently have this step executing under a proxy account which has administrative rights. We ran the agent for the first time yesterday as a test and it ran through correctly.
If you need more information I would be happy to provide it.
Any advice would be greatly appreciated!
Best regards,
I am trying to install SP2 and it seems to have hung. The setup screen says that it had success with the setup support files and is now on the database services "in progress" it is installing windows installer file sqlrun_sql.msp and has been here for 45 minutes. Any help would be appreciated.
Thanks
Chris
Inside my dataflow task, I've got 5 OLEDB Source components pulling data in from various SQL Server Instances.
I have an OLEDB Destination pointing to a single table that should receive all of records create by the combination of Merge Joins of these five sources.
Right now I'm working with a set of test data, and I've got 8 records coming through after all of my merge joins filter things out.
The problem I am seeing is that my OLEDB Destination turns yellow and then just sits there. I've looked in my Progress (Execution Results) tab when I run the package, and there's nothing alarming there.
I've run SQL Profiles on the database table I'm trying to insert data into. Nothing.
I've also change the destination from 'Table or view - fast load' to 'Table or view' . I've unchecked the table lock option for fast load. No dice.
The only thing I can say I've seen that looks suspicious is that I have added some data views between data flow components just before the data gets to the OLEDB Destination and a few of the fields in the data view will show <Missing LineageID> in the field rather than the value. I removed all of the columns that were behaving this way from the flow just to see if that was the problem or if, by removing these fields, I was able to get data to write to the table. This did not work. It does leave me wondering though.
Anyone have any idea why this might be happening or have had similar behavior?
Any help/ ideas of things to check would be greatly appreciated.
Thanks in advance.
Isaac
As we are beginning to use sqlserver 2000 more and more on a daily basis, the database appears to be hanging more frequently. The solution so far has been to bring the database down and then restart it, or in some cases backing it up. Is there some log file I can look at that will give me an indication as to what is wrong ?
View 5 Replies View RelatedHi,
Below messages got throwed to sql ErrorLog. I could'nt understand what it means.. any help is appericated.
The Scheduler 2 appears to be hung. SPID 0, ECID 0, UMS Context 0x03CBAA40.
Error: 17883, Severity: 1, State: 0
WARNING: EC 36b9c098, 8 waited 300 sec. on latch 444f3b84. Not a BUF latch.
thanks,
-J
Hello,
The users of my microsoft SQLServer 7 database could not log on to the database after the following message has appeared in the log file;
The Scheduler 0 appears to be hung. PSS 0x5B1630C8, EC 0x5B163278, UMS Context 0x3FC37E88
The problem was solved after I have restarted the database. I would be grateful if you could show me how to avoid such a problem in the near future.
Regards,
Albert
I'm restoring a complete backup of a database from Enterprise Manager. This has been running for 3 hours and shows no sign of progress. The "Restore Progress" dialog is on the screen and the progress bar hasn't showed any progress. The disk is at full capacity in performance monitor. Nothing else is using the system.
The data file is 14 GB. It seems like something is wrong. Anything I can do to check?
SQL Server 2000 SP3a Enterprise Edition
Windows 2000
Hi all,
My SSIS packages seems to run well in test environment.They are scheduled to run as jobs on a daily basis on the
production system.Most of the days they run well,but somedays they keep running and never end,till we manually kill the jobs and re run them.It's really difficult to find the cause as it does not even fill the log file.
If the package starts the first task is to fill in a table,but its not filling it,so i infer that the package has not started itself,
Did anybody of you have come across such a situation?Pls give your inputs.
Any help on this is greatly appreciated.
Thanks
Hi,
Looking through the archives, I didn't see any articles that specifically addressed the problem, so here it is:
SQL 7.0, NT4SP6, 2G ram, 4x
I've got a user process as follows:
Status: ROLLBACK
Command: SELECT
Application: Enterprise Manager
Wait Type: EXCHANGE
Login time: 06/25/00 4:07:05AM
Last batch: 06/25/00 4:07:20 AM
The last TSQL command batch is a correlated subquery with grouping...
Apparently it hung and the user quit ungracefully.
No other processes are blocking it, but periodically it blocks other processes, including some index maintenance I need to perform.
I have tried to kill it with Enterprise Manager and with the KILL [id] command. Neither have worked.
Mgmt is reluctant to bounce the database, as am I.
Any ideas?
Thanks in advance!
--Nassif
Folks,
I have a SSIS package that works fine 90 % of the time. Every once in a while it gets hung and when I notice that the package has been running for several hours I check the job activity monitor and notice that the package is in executing state for several hours..my question is ...Is there a way I can check for this incidence through a c# app ? Does the package name show up in the process task if so I can poll to see if the process corresponding to the package has been executing for > 30 mins then I can kill it.
Any suggestions would be great.
Thanks
I'm attempting to develop a course whose objective is to present the users with several scenarios of broken or hung databases caused by different things that they then have to fix.
Do you have any ideas or examples of how to break a database and the reasons behind it, also how to repair it afterwards !!
Richard
Environment:
Clustered SQL Server 2K sp3a (single instance)
Product version: 8.00.760
Problem:
I end up in error 17883.
I am aware that the solution is to apply this patch:
821277 MS03-031:Security Patch for SQL Server 2000 Service Pack 3.
I am also aware that after you install this cumulative patch you will end up in this problem:
826161 FIX: You are prompted for password after you change a standard SQL Server login.
After reading this one I found that there are 2 possibility to solve this problem, one if you have already installed the security patch from MS03-031 and another one if you do NOT have installed this patch.
Question:
Do I only need to apply this last one (is it also cumulative?) or do I need to first apply MS03-031 and then later the FIX 826161?
Please advise.
Kind regards.
I'm having an issue in SQL Server 2005 with jobs that execute SSIS packages. The jobs run fine for a week or so, then I'll come to find that four or five (of the ten or so jobs) are hung in "executing" status. They seem to hang indefinitely (as some have been "executing" for hours with no end. The schedules of the hung jobs are all different, varying from every 10 minutes to nightly. The packages perform completely diffent tasks, as well. I can't seem to find any common thread with the jobs that get hung, other than they are all executing SSIS packages.
I've tried manually stopping the jobs and restarting the agent and SQL Server but the jobs hang again on there next scheduled run. The only thing that fixes the issue is rebooting the box, and then the jobs hang again in a week or so. Could some sort of memory leak be consuming resources throughout the week and be causing the jobs to eventually hang? I just rebooted the box and the sqlagent90.exe process is currently using about 7mb of memory. I'll keep an eye on it. Any other suggestions?
I've thought of creating another job that stops jobs that are hung, but what's to say that this job won't get hung as well? Plus this seems like a band-aid fix...
I don't recall having these problems until installing SQL Server 2005 SP2. Could this be related? I've searched like crazy and still can't find a resolution to this. It's becoming a big PITA...
Anyway, any suggestions would be very much appreciated!
I am having some problem in running SSIS scheduled job.
The job is processing (updating and inserting) batches of data using Execute SQL Task inside Foreach loop container. But to make sure the batch complete successfully I put the "Begin Tran" Execute SQL Task and "Commit Tran" Execute SQL Task before and after the foreach loop
My workflow is like the following....
- Execute SQL Task "Begin Tran"
- ForEach Loop Container for a batch of records.
- Insert the records into the database
- End ForEach Loop
-Execute SQL Task "Commit Tran" if the above ForEach Loop complete successfully
-Execute SQL Task "Rollback Tran" if the ForEach Loop failed
Normally the insert records are around 10~200 and the job is running very fine...
But if the records are more than 1000s then sometime the job hung (I would say hung because the DTEXEC.exe can be seen inside the windows task manager.) There was no indication what so ever whether the job finished or the job failed or the job else....
Can anybody help to find the cause.....
Rgds,
KyawAM
Have an interesting situation.
I have a NeverFail cluster on which I loaded SQL Server 2005 and SP1.
All was great. Both nodes had no issues with the initial setup or the SP1 update.
When I forced a failover to the inactive node, MOST of the services like SSIS, Full-Text and SQL Browser came up like a champ.
BUT ... the SQL Server service and the SQL Agent did not come up (or shall I say not fully).
I am now looking at the SQL Server Configuration Manager and I see the following for the SQL Server service:
"Name" shows the RED block icon for the SQL Server
"State" shows "Change pending ..."
"Start Mode" is set to Manual
"Log on as" has my domain account listed
"Process ID" has the number 2956 (so it has started somewhat).
I then click open the SQL Service item and it shows that it is stopped.
I am given the option to START the service. When I try to start it, the meter bar comes up and moves slowly to the end and then an error returns of ...
"The request failed or the service did not respond in a timely manner. Consult the event log or other applicable errors logs for details"
If I look under windows Services in the Admin Tools section... I see the services for SSIS, FT and Browser listed and started BUT I see SQL Server says its "starting".
If I click open the service from here, once again it show "starting" and all of the option buttons to Stop-Start-Pause-Resume are grayed out and I am not able to use them.
I have looked in the Windows Event Logs for any events but none are present.
Now I know why the SQL Agent is not running, and of course that is due to is dependency on SQL Server being up and running.
First off WHAT does the "Change pending" message mean?
Then what other logs can I look at for some help or is there someone who has the answers to this dilemma.
Thanks and have a great evening
Greetings,
I am trying to restore a SQL Server 2005 database from a backup file and experiencing a hanging issue after its "finished"
I am doing this in SQL Server Management Studio, generating the following SQL for Restore:
RESTORE DATABASE [AdventureWorks2]
FROM DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLBackupAdventureWorks.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
When I run this on the machine i originated the backup with (creating AdvWorks2) it runs fine in no time.
When I run this command on another SQL Sever 2005 instance on another host. It appears to run fine, and I see progress going up to 100% and it says "Restore Completed Successfully"
BUT, for some reason, the database in object explorer is stuck with a "(Restoring...)" label attached to its tree item and I am unable to perform any activities on that database instance. It claims, it's in the middle of a restore operation! again this is after it had reached 100% on progress and declared successfull completion.
Any ideas what could be causing this?
(Note: Both instances are SQL Server 2005 - Service Pack 2)
Thanks,
-ali
OK. I give up and need help. Hopefully it's something minor ...
I have a dataflow which returns email addresses to a recordset.
I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.
I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.
I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).
The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.
Try
ds = CType(Dts.Variables("VP_EMAIL_RESULTS_RS").Value, DataSet)
My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.
part number leadtime
x 5
y 9
....
Does anyone have any idea what I might be doing wrong?
thanks
John
I'm using SSIS in Visual Studio 2012. My Execute SQL Task calls a Stored Procedure where I have a TRY-CATCH. Last week there was a problem and the CATCH was executed and logged an error to my error table, but for some reason the Execute SQL Task didn't fail. Is there a setting to make the Execute SQL Task fail when an SP encounters a failure?
View 3 Replies View Related
Hi,
I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:
The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.
Then when I try to delete it it gives this other error:
Cannot remove the specified item because it was not found in the specified Collection.
I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.
Any suggestions why this is happening and how to fix it?
I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.
The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.
I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)
Any option anyone knows will help.
Thanks.
I am using SQL 2005 SSIS. I am joining several large tables and then the move result into another table in the same database.
I would like know which method is faster:
Use Execute SQL Task to insert the result set to the target table
Use the Data Flow Task to insert the result set to the target table. (Use OLE DB source to execute SQL command and then use the SQL destination)
Could you tell me why then other is slower?
Thanks.
I have a SQL Task that calls a stored procedure and returns an output parameter. The task fails with error "Value does not fall within the expected range." The Stored Procedure is defined as follows: Create Procedure [dbo].[TestOutputParms] @InParm INT , @OutParm INT OUTPUT as Set @OutParm = @InParm + 5 The task uses an OLEDB connection and has a source type of Direct Input. The SQL Statement is Exec TestOutputParms 7, ? output The parameter mapping is: Variable Name Direction Data Type Parameter Name User::OutParm Output LONG @OutParm
View 7 Replies View RelatedIn the Control flow tab, I have an Execute SQL Task that outputs full Result set into a variable of an object type. Now how can I write the contents of the Full Result Set into a text file using Script Task. I also want to format the following way while I output into a file:
Column Name 1 : Column Value
Column Name 2: Column Value and so on
I tried writing the contents of the Object Variable into a file, but the file had an output of single word: System.__ComObject.
Code for Writing the Full Result Set into a Text File
Dim RSsqloutput as String = Dts.Variables("objVariable").Value.ToString
Dim strVal as String = "File completed on " & Now() & vbCrLf & "------------------------------------------------------" & vbCrLf
oLogFile.WriteAllText("C:MyFile.txt", strValue)
oLogFile.WriteAllText("C:MyFile.txt", rsSQLOutput)
I went through this link that explains how to write XML Result Set into a File, But this doesn't help as it writes in XML format.
Would you please give me a hint of code how I can go upon.
In short, does the €œTransfer SQL Server Objects Task€? support distributed transactions?
In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?.
I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.
Here€™s the error info€¦
SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).
Hi
I have an application like fetching records from the DataBase(MS Access 2000) and results i have to use in Script Task. At present i have used the record fetching query,connection string in Script itself. I would like to use in Independently. Is there any Tools like (Control Flow Tools like Execute SQL Task) are there to fetch the result set from Acccess and can use the fetching results in Script Task....
Thanks & Regards
Deepu M.I
I have a stored procedure that is executed via a sql script task that returns a full result set. I map this result set to a variable or object type. Is there a way to use this variable as a data source in a subsequent data flow task?
A.
I have made one package which extracts data from the source does transformation and submits the data to destination. Subsequently it also updates the required control files.
Now I want to add a functionality :
If the package is executed again it should check the status of previous execution in control file if success mark all tasks disable and stop
if failure mark all tasks at enable and start extracting data and continue further with execution.
I was able to attain similar functionality in SQL Server 2000 using activeX script. What code do I need to write as a part of Script Task in order to attain above functionality.