I have a scheduled job to run daily at 4 am. the job imports data from client side from text
files and puts data in our sql table.It takes around 1 hour.
the problem is it doesnt stops after the import process completes.so after one hour
i can see the data is imported into my sql table so thats fine but the job keeps running.
I tried observing that job's spid in activity monitor in sql 2005 but after one hour i cant even see
that spid but still job runs. its weird.and after that when i stop the job manually then it stops
saying job completed succcessfully.
that step is a last step and it uses windows cmd.my understanding is the job step doesnt
understands that it got finished. what should i do in here?? any ideas r appreciated
we are running the same job for another servers and its fine.
Ive built an SSIS package which generates a file from a legacy system and then downloads the file into a designated folder on the server. I need the file watcher task to wait for a the file to completely finish loading before it says it is complete. Currently, as soon as the file is created, the WMI step finishes.
I am having a problem importing data into SQL 7 from any type of source. I go through the whole import process no problem. When I click the finish button to start the import, nothing at all happens. Enterprise Manager and the DTS just hang and I must use crtl+alt+delete to end the program. Can anyone give me any suggestions as to what might be happening. Big Thanks in advance, I've been working on this for days.
I am a VoIP phone system using SQL on the back end. I am trying to get a Trigger to fire and email me when a certain number has been dialed.
Create Trigger trg_Emergency_Calls on dbo.CallDetailRecord for Insert
IF @@ROWCOUNT=0 RETURN ---NO rows affected exit proc
IF (SELECT finalcalledpartynumber FROM inserted)='95593684' BEGIN --RAISERROR ('Call Stored Procedure Here',16,10) EXEC WEB_SRVR03.master.dbo.sp_SMTPMail @body='This is a test Email'
END Return
GO
The problem is I have to execute the actually email SP is on another server and has to be that way. The trigger actually runs each time but if the IF statement becomes true then the trigger hangs and never completes. Watching the other server(WEB_SRVR03) there is never a request to execute the sp_SMTPMail. I have been trying to troubleshoot this with profiler but I never see any locks or anything that would give me a problem. Also the insert statement that caused the trigger to fire also never finishes and so the record isn't written to the db. If anyone has any suggestions I would appreciate it. Thanks
I want to finsih the execution of a DTS package from an ActiveX task. If a condition is ok, the package would continue as normal. If not, I want to finish the package without any error. Just without executing the next tasks. Do you have any idea?
I have a Stored Procedure which Ideally should run when a Customer Logs in, the Procedure will check the available stock and create a Temp Table for the Information, which allows many other Queries in the site to run a lot faster, (due to no joins). The Query has taken as much as 30seconds (Lots of Records and 1/2 dozen Joins) to run upon log in and causing a Timeout for the web application.
I want the procedure to run as it is, but for the login method to not be dependent on the Process tried this in .NET cmd.BeginExecuteNonQuery() (cmd=SQLCOmmand) which doesn't do what I want it just allows me to run heaps of QUeries at the same time.
Can anyone help me with getting this procedure to run and not hold up the Web application? Not sure whether I need to do this in .NET, or whether I can get .NET to run a Batch File or something, but someone must have had a similiar problem, please help.
No Error but Export to Excel does not finish When the report has 2 pages with total 500 rows exporting to Excel is not a problem. If it has 100 pages 5000 rows exporting to excel does not end and it does not return any error but the process does not end either. What might the problem be?
I have a database A include five tables, and have more than 1,500,000 rows. There is a replica database of A. First of all, there is no data in the two dbs. When I finish inserting data into A, the replica db seems still work, the log file size still changes. How can I know the replication finished or not? How long it will take to finish replicate 1,500,000 rows of data?
I have been working with this for about a month now, and no similar problems to date. Today I am trying to introduce 4 configuration flags that control whether optional ETL stage feeds are executed. I did this by adding a do-nothing script component. The precedent and constraint is used, and it checks the boolean variable flag. The first package executes fine. But it never returns from there. This precedent has nothing fancy on it either. It simply does not run any more of the package, make any more conditional checks, nor the common completion tasks. It just seems to think it is done.
The optionals all fire execute package tasks. One thing that might be tripping it up is that I attempt to run one package twice, each time with varying parent package variable set to control it to use a different destination database for each run. Should this not be OK to do?
Here's my dilema, I want to run a stored procedure that starts another stored procedure running, but does not wait for the stored procedure to complete execution.
The stored procedure should execute immediately, and leave the other procedure to complete running in the background. Is there any way to do this?
We recently upgraded out SQL version from SQL2008R2 to SQL2014. As such, the compatibility mode changed to SQL2104 (120).
We have several queries that used to run fine that now take forever to bring back results. There are no errors (which surprised me). They just take way too long now. PLus they seem to be causing high I/O and CPU.
If I change the compt level back to SQL2008 - these queries run fine.
QUERY with SQL2008 compt level - finished in 2 minutes. QUERY with SQL2014 compt level - finishes in 3 hours 22 minutes.
same exact query - same server - only thing changed was compatibility level.
WHat do I look for in the queries that could be causing this? (they look fine but obviously I'm missing something here)..
I've setup the option to mail the error to a person. When the option is on I get the error message by mail but the package does not finish (eg. the failing task does not become red and the output windows never says anything about the error) - if I set the option to off the task fails as expected.
Hi, we've got this problem with some particular jobs: they look as they ran correctly, but actually they didn't made it all through their duties. The problem is that this job is calling a sequence of DTS, where there is a DTS with an ActiveX control which modifies another DTS before launching and some other tasks: the error happens there, . Launched from the DTS we get the error, from the job no...any idea how we could get the correct job information? Thank you Daniele
I have this query I need for a report. Originally it was 4 queries to be used in Crystal Reports. Now I want to create the same report with SSRS and therefore I incorporated all queries in one in order not to use subreports [URL].....
Tempdb fills up to nearly 90 GB. I am running SQL Server on a local box, so I am sure there is no other traffic. Here is the query:
SELECT AdHaupt.NSprache_ID ,AdHaupt.mengentext AS mengentextHaupt ,AdHaupt.Einzelpreis ,AdHaupt.Anzeigebezeichnung ,AdHaupt.Gesamtpreis
[Code] ...
I ran it with TOP 10 as well, just to see if it will finish at all, but it never did (ran for an hour now).
I'm running my SSIS packages from a scheduler (WindowsService) that I wrote in .Net. For the logging of the SSIS events I only use te SSIS Log provider for SQL Server. Nevertheless, the package run start and stop events (and execution failures) are still logged in the Windows Event Log. Is there a way to stop SSIS from writing this event-log entries?
How can a tell if a tak completed succesfully from a stored procedure?
I have a task which is executed from a stored procedure. The sp_runtask only returns whether the task started successfully. How can I tell if it completed successfully?
2007-03-22 09:36:52.39 server Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: )
2007-03-22 09:36:52.45 server Copyright (C) 1988-2000 Microsoft Corporation. 2007-03-22 09:36:52.45 server All rights reserved. 2007-03-22 09:36:52.45 server Server Process ID is 1516. 2007-03-22 09:36:52.45 server Logging SQL Server messages in file 'd:Program FilesMicrosoft SQL ServerMSSQLlogERRORLOG'. 2007-03-22 09:36:52.57 server SQL Server is starting at priority class 'normal'(1 CPU detected). 2007-03-22 09:36:53.26 server SQL Server configured for thread mode processing. 2007-03-22 09:36:53.48 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks. 2007-03-22 09:36:54.31 server Attempting to initialize Distributed Transaction Coordinator. 2007-03-22 09:36:56.46 spid3 Warning ****************** 2007-03-22 09:36:56.46 spid3 SQL Server started in single user mode. Updates allowed to system catalogs. 2007-03-22 09:36:56.60 spid3 Starting up database 'master'. 2007-03-22 09:36:57.26 spid3 Server name is 'PRECISE'. 2007-03-22 09:36:57.26 server Using 'SSNETLIB.DLL' version '8.0.194'. 2007-03-22 09:36:57.26 spid5 Starting up database 'model'. 2007-03-22 09:36:57.60 spid5 Clearing tempdb database. 2007-03-22 09:36:57.74 spid7 Starting up database 'msdb'. 2007-03-22 09:36:57.85 server SuperSocket Info: Bind failed on TCP port 1433. 2007-03-22 09:36:57.89 server SuperSocket Info: Bind failed on TCP port 1433. 2007-03-22 09:36:57.89 server SuperSocket Info: Bind failed on TCP port 1433. 2007-03-22 09:36:57.89 server SuperSocket Info: Bind failed on TCP port 1433. 2007-03-22 09:36:58.31 server SQL server listening on TCP, Shared Memory, Named Pipes. 2007-03-22 09:36:58.31 server SQL server listening on 195.195.195.2:1433, 127.0.0.1:1433. 2007-03-22 09:36:58.31 server SQL Server is ready for client connections 2007-03-22 09:36:58.35 spid7 Starting up database 'pubs'. 2007-03-22 09:36:58.64 spid7 Starting up database 'Northwind'. 2007-03-22 09:37:00.18 spid5 Starting up database 'tempdb'. 2007-03-22 09:37:00.32 spid3 Recovery complete. 2007-03-22 09:37:00.32 spid3 Warning: override, autoexec procedures skipped. 2007-03-22 09:37:07.95 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.
hi, i have a message queue system using sql 2005 service broker. the code and setup is the same on both dev and live database. but soon after i restored a live backup to dev. the queue stopped working on dev, live is ok thou. after some trouble shooting, i found that the server is not sending the message at all, but it says "Command(s) completed successfully" without any error messages.
setup:
-----------------------
create message type TestQueryMessage validation = none
create contract TestQueryContract (TestQueryMessage sent by initiator)
create queue TestSenderQueue
create service TestSenderService on queue TestSenderQueue
create queue TestQueueReceiver
create service TestServiceReceiver on queue TestQueueReceiver (TestQueryContract)
send message:
-------------------------
declare @conversationhandle uniqueidentifier;
begin dialog @conversationhandle
from service [TestSenderService]
to service 'TestServiceReceiver'
on contract [TestQueryContract]
with encryption = off;
send on conversation @conversationhandle
message type [TestQueryMessage] ('blah blah blah');
result:
----------------------------------
Command(s) completed successfully.
but when i do "select * from TestQueueReceiver", there's nothing. and i sure nothing else had picked up the messages.
Hi all, There is nothing happen when I finished my checkout process, I expect the data will be saved to order and orderitem table in my SQL database, but no data found on order and orderitem table and no error messages display during operation!!! Below is my checkout.aspx.vb code, the whole code line number around 138, I captured the part from 1~ 64 line number, I suspect line 35 - 48 have a problem, can somebody help me, many thanks. 1 Imports System 2 Imports System.Data.SqlClient 3 Imports SW.Commerce 4 Partial Class CheckOut 5 Inherits System.Web.UI.Page 6 Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 7 If Not Page.IsPostBack Then 8 If Profile.Cart Is Nothing Then 9 NoCartlabel.Visible = True 10 Wizard1.Visible = False 11 End If 12 If User.Identity.IsAuthenticated Then 13 Wizard1.ActiveStepIndex = 1 14 Else 15 Wizard1.ActiveStepIndex = 0 16 End If 17 End If 18 End Sub 19 Sub chkUseProfileAddress_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) 20 21 ' fill the delivery address from the profile, but only if it’s empty 22 ' we don’t want to overwrite the values 23 24 If chkUseProfileAddress.Checked AndAlso txtName.Text.Trim() = "" Then 25 txtName.Text = Profile.Name 26 txtAddress.Text = Profile.Address 27 txtcity.Text = Profile.City 28 txtCountry.Text = Profile.Country 29 End If 30 End Sub 31 Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) 32 33 ' Insert the order and order lines into the database 34 35 Dim conn As SqlConnection = Nothing 36 Dim trans As SqlTransaction = Nothing 37 Dim cmd As SqlCommand 38 Try 39 conn = New SqlConnection(ConfigurationManager.ConnectionStrings("swshop").connectionstring) 40 conn.Open() 41 trans = conn.BeginTransaction 42 cmd = New SqlCommand() 43 cmd.Connection = conn 44 cmd.Transaction = trans 45 46 ' set the order details 47 48 cmd.CommandText = "INSERT INTO Order(MemberName, OrderDate, Name, Address1, Address2, Country, Total) VALUES (@MemberName, @OrderDate, @Name, @Address, @city, @Country, @Total)" 49 cmd.Parameters.Add("@MemberName", Data.SqlDbType.VarChar, 50) 50 cmd.Parameters.Add("@OrderDate", Data.SqlDbType.DateTime) 51 cmd.Parameters.Add("@Name", Data.SqlDbType.VarChar, 50) 52 cmd.Parameters.Add("@Address", Data.SqlDbType.VarChar, 255) 53 cmd.Parameters.Add("@City", Data.SqlDbType.VarChar, 15) 54 cmd.Parameters.Add("@Country", Data.SqlDbType.VarChar, 50) 55 cmd.Parameters.Add("@Total", Data.SqlDbType.Money) 56 cmd.Parameters("@MemberName").Value = User.Identity.Name 57 cmd.Parameters("@OrderDate").Value = DateTime.Now() 58 cmd.Parameters("@Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text 59 cmd.Parameters("@Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text 60 cmd.Parameters("@City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text 61 cmd.Parameters("@Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text 62 cmd.Parameters("@Total").Value = Profile.Cart.Total 63 Dim OrderID As Integer 64 OrderID = Convert.ToInt32(cmd.ExecuteScalar())
SQLServer2005_SSMSEE in my notebook with vista business and nearly the end of installation the process is cancelled by an error. its necessary install some application before? SQL Server Express is installed by default in the notebook.
thaks a lot and excuse for my bad english (bettr more for me is SPANISH)
i searched and all i found is questions, not answers.
maybe it's a silly question, but i really can't find any documention / posts about this.
i have a scheduled job in sqlagent that executes a SSIS package that runs every minute. As a result, my application log in eventviewer gets filled very quickly.
i tried using "/REPORTING E" option with no luck.
i tried enabling logging on the package, and then select only the OnErro Event, no luck.
I'm setting up a Maintenance Plan to do a backup for me in the night of my SQL Databases but before the backup starts I have created a SQL Server Agent Job that runs a DOS batch (.bat) file using CmdExec. The problem is I need this process to finish processing before the rest of the tasks run in the Maintenance Plan. It seems like SQL Server Agent Job Task tells the batch file to start running (and it runs fine) but it just continues to the next task and does not wait for it to finish.
Is there any way I can configure this to wait before proceeding?
I was wondering if anyone knew of a way to disable the following, SQLISPackage start/finish events sent to windows event logs everytime a SSIS package is executed and completed.
Currently, I'm developing an ETL program using Microsoft DTS on SQL Server 2000.
I've completed unit testing & component testing for all inner packages containing various types of executions - SQL task, ActiveX Script task, Error Handler, Transform Data task, Data Driven Query & Dynamic Properties task.
When unit & component tested, all inner package properties-"Limit the maximum number of tasks executed in parallel" is defaulted to 4.
However, the integration testing is failing because I've yet to successfully execute any of the Package tasks in the outer package, which runs & controls all inner packages from the outer package.
The error message encountered was: --------------------------------------------------------------- | Error source: microsoft dts | | Error description: execution was cancelled by another user. | ---------------------------------------------------------------
Please explain what might be the cause to this problem when I'm executing the outer package?
I have a program that is automatically ran through a job. The program gets the most recent files that have been uploaded to a server. I would like to be able to query the database to see when the last time this job was ran successfully and set this date as the date to look for files newer than the last successful run date.
Could someone point me in the right direction to what tables this data is stored in on a 2005 SQL Server Database?
I have this data in a flat file and I'm doing a data dump to a SQL table. I created a SSIS package which ran successfully but only exporting 2 rows out of 4 rows to the SQL table. For some reason my package ignore two other rows where "col4", "col5" and "col6" do not have any data. No conditional component was used in this package to not to load those records.
col1 col2 col3 col4 col4 col5 col6
849833 02/15/06 841 VS 791 49 7 849819 02/15/06 212 NA
I'm in the process of installing Hotfix kb934458 (9.0.3054) on a Windows 2003 R2 SP2 Server. This is for SQL 2005 on a Active/Passive cluster with 2 instances. I'm only getting this error on the Database Engine and Analysis Services. It was successful for Integration Services, Reporting Services and Notification Services. I've tried taking the 2 instances offline as well as tried installing from the Active then again on the Passive nodes. No luck... Any suggestions? Does this work for R2 release of Windows 2003 SP2? This is the log:
Time: 02/29/2008 14:02:38.088 KB Number: KB934458 Machine: OS Version: Microsoft Windows Server 2003 family, Enterprise Edition Service Pack 2 (Build 3790) Package Language: 1033 (ENU) Package Platform: x64 Package SP Level: 2 Package Version: 3054 Command-line parameters specified: Cluster Installation: Yes Log Location on Passive Nodes: C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix ********************************************************************************** Prerequisites Check & Status SQLSupport: Passed ********************************************************************************** Products Detected Language Level Patch Level Platform Edition SQL Server Database Services 2005 (BIZTALK) ENU SP2 2005.090.3050.00 x64 ENTERPRISE SQL Server Analysis Services 2005 (BIZTALK) ENU SP2 2005.090.3050.00 x64 ENTERPRISE SQL Server Reporting Services 2005 (BIZTALK) ENU SP2 9.00.3054.00 x64 ENTERPRISE SQL Server Database Services 2005 (SHAREPOINT) ENU SP2 2005.090.3050.00 x64 ENTERPRISE SQL Server Analysis Services 2005 (SHAREPOINT) ENU SP2 2005.090.3050.00 x64 ENTERPRISE SQL Server Reporting Services 2005 (SHAREPOINT) ENU SP2 9.00.3054.00 x64 ENTERPRISE SQL Server Notification Services 2005 ENU SP2 9.00.3054.00 x64 ENTERPRISE SQL Server Integration Services 2005 ENU SP2 9.00.3054.00 x64 ENTERPRISE SQL Server Tools and Workstation Components 2005 ENU SP2 9.2.3054 x64 ENTERPRISE ********************************************************************************** Products Disqualified & Reason Product Reason ********************************************************************************** Processes Locking Files Process Name Feature Type User Name PID MSSQL$BIZTALK SQL Server Database Services Service 4756 MSSQL$SHAREPOINT SQL Server Database Services Service 4636 MSOLAP$BIZTALK Analysis Services Service 7976 MSOLAP$SHAREPOINT Analysis Services Service 7148 ********************************************************************************** Product Installation Status Product : SQL Server Database Services 2005 (BIZTALK) Product Version (Previous): 3050 Product Version (Final) : Status : Failure Log File : Error Number : 11009 Error Description : No passive nodes were successfully patched ---------------------------------------------------------------------------------- Product : SQL Server Analysis Services 2005 (BIZTALK) Product Version (Previous): 3050 Product Version (Final) : Status : Failure Log File : Error Number : 11009 Error Description : No passive nodes were successfully patched ---------------------------------------------------------------------------------- Product : SQL Server Reporting Services 2005 (BIZTALK) Product Version (Previous): 3054 Product Version (Final) : Status : Not Selected Log File : Error Number : 0 Error Description : ---------------------------------------------------------------------------------- Product : SQL Server Database Services 2005 (SHAREPOINT) Product Version (Previous): 3050 Product Version (Final) : Status : Failure Log File : Error Number : 11009 Error Description : No passive nodes were successfully patched ---------------------------------------------------------------------------------- Product : SQL Server Analysis Services 2005 (SHAREPOINT) Product Version (Previous): 3050 Product Version (Final) : Status : Failure Log File : Error Number : 11009 Error Description : No passive nodes were successfully patched ---------------------------------------------------------------------------------- Product : SQL Server Reporting Services 2005 (SHAREPOINT) Product Version (Previous): 3054 Product Version (Final) : Status : Not Selected Log File : Error Number : 0 Error Description : ---------------------------------------------------------------------------------- Product : SQL Server Notification Services 2005 Product Version (Previous): 3054 Product Version (Final) : Status : Not Selected Log File : Error Description : ---------------------------------------------------------------------------------- Product : SQL Server Integration Services 2005 Product Version (Previous): 3054 Product Version (Final) : Status : Not Selected Log File : Error Description : ---------------------------------------------------------------------------------- Product : SQL Server Tools and Workstation Components 2005 Product Version (Previous): 3054 Product Version (Final) : Status : Not Selected Log File : Error Description : ---------------------------------------------------------------------------------- ********************************************************************************** Summary No passive nodes were successfully patched Exit Code Returned: 11009
I have had a serious issue with a production AlwaysOn cluster whereby the service did not successfully transition to the secondary node and I cannot find the root cause of the issue.
Some details: It is a 2 node cluster (same datacenter) with a shared disk quorum, Windows Server 2012, both are virtual machines running on VMWare vSphere  5.5. SQL Server version is 2012 Enterprise SP2 CU6
The failover occurred because of a network incident (a spanning tree recalculation caused a connection timeout between both nodes). Initial entries in the SQL Log look normal for this event, for example:
05/08/2015 11:18:06:Â A connection timeout has occurred on a previously established connection to availability replica 'FIN-IE-PA078' with id [6910F4A9-87E7-4836-BA79-0F41BE90266D]. Â Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role. 05/08/2015 11:18:06:Â AlwaysOn Availability Groups connection with secondary database terminated for primary database 'UserManagement' on the availability replica with Replica ID: {6910f4a9-87e7-4836-ba79-0f41be90266d}. This is an informational message only. No user action is required.
[code]....
My interpretation of this is that the cluster failover attempts failed, because the network condition still persisted. The network interruption lasted approximately 2 minutes, and I would have expected the cluster to come back online at this point, after the restart delay period as suggested in the last entry in the error log. However this did not happen.
I need to execute a DTS that have a couple of steps and one of them is a process task that simply call an exe file i made that will send an email to warn the user.
What happens here is that the process task executes my exe file but it doesn't wait for it to compete and fires the next task after and finally closes.
There is anyway to make a "while statament" to wait until my exe application finishes?
I have a page that runs a transaction correctly after a button click. I want to allow someone to click a button that rolls back the transaction, after the transaction runs on the first button click. I can also successfully roll back within the first button click. I'm getting a NullReference error when trying to access SqlTransaction.Rollback() outside the button click. If SqlTransaction.Commit() completes without error can SqlTransaction.Rollback() be called after? I tried making 'trans' a more global variable and it still gave me the error. Button Click 1: Dim trans As SqlTransaction trans = connection.BeginTransaction() try 'run SQL Statement trans.Commit() Catch e As Exception trans.Rollback() throw e end try
