My ssis package errors out because one of the database connection failed. I successfully logged error but also indicated that package finished successfully. My confusion is if a sheduling software schedules this package, what would be return code sent by dtexe... . would it be success or failure? In this scnerio i want it to return failure so that appropriate team can be contacted.
thanks,
kushpaw
I have this on my page
Dim backUpDB2 As SqlClient.SqlCommand
backUpDB2 = New SqlClient.SqlCommand
backUpDB2.CommandType = CommandType.StoredProcedure
backUpDB2.CommandText = "msdb.dbo.SP_RESUMENFAC"
backUpDB2.Connection = SqlConnection1
backUpDB2.ExecuteNonQuery()
The SP has this
CREATE PROCEDURE .[SP_RESUMENFAC] AS
EXEC sp_start_job @job_name = 'TransferirDatos(FACT) '
GO
WHen I execute the page after the SP it fills some datagrid but the data is not updated bacuase the job takes 1 minute or more to finish.
Is there anyway to prevent to show the old data? or to detect when the job has finished?
Thanks
I am trying to create a SQL Job which will report on another job which hasn't finished within its normal completion time. I schedule the new job late enough after the first job that the first should have finished by then.
I would like to have just queried msdb..sysjobhistory.run_status. However, this seems to only report on job STEP status - and after the step is finished! It is always showing run status = 1 (Complete).
Does anyone know the base meta-data table and column I could query? Enterprise Manager shows the current status as running and I want to know where it gets that.
If it is still running, I will raise an error to notify our support group, etc.
PS: My job steps include a combination of DTSRun commands and TSQL commands. I don't think the flavour of commands should matter...
Thanks.
Hi,
I'm a MS Access developer who needs to help someone migrate/convert from a 'finished' SQL application to Access. How do I gain access to the SQL backend so I can examine/export the raw data? For example, in Access, I would hold down the Shift key while opening the program and it would give me editing rights to the database.
Please be very specific because I have zero experience with SQL.
Thanks,
David
Currently we have a process where 4 files are ftp'd down to our server. We have DTS package which tests for the existence of these files and once these exist, the DTS package loads the data from these files into a SQL database. Sometimes, however, though the files exist they haven't finished being downloaded so that when the DTS package tries to process them the package is saying the files are empty (though they're not).
Thanks for any help.
I have a SSIS package which process 12699 files in a folder. After about 20 minutes it looks like the loading is finished (the record count of database table doesn't change any more) and I believe it's finished, but if I check the Progress tab, it's still showing the file name being processed and still moving. So my first question is: is it still loading files or it's just that the progress message is behind?
Then I click on stop debugging, after a while it stopped but then it's frozen, nothing responds no matter what I click. I haven't saved the package yet so I don't want to close it out. Should I just wait? And what is the problem?
Thanks a lot for your help in advance!!
p.s. I'm exploring SSIS and will convert our current DTS packages to SSIS so you'll see me post more and more questions down the road.
I have a job I want to run everyday but before this job starts and I want to check and see if another job has completed before I start this job. i would like to do this in the job steps in SSMS. step 1 is job 'xxxxxxx' running if no go to step 2 if yes exit
View 3 Replies View Related
Hi All,
I have a solution which is synchronised with visualsourcesafe. Now there are some reports present in the solution, I am able to view the preview of the reports but when I am going to view the dataset definition It is not able to retain its defintion and its becoming blank but this happens to only datasets which were developed from cube and its able to retain dataset which are developed from Database.
Any help would be of great use.
Thanks in advance
Regards
Hi,
I want to generate a new snapshot using stored procedures. I want to wait for the snapshot files to be created and then execute a stored procedure. What's the best way to determine that the snapshot has completed successfully? I thought of doing something like:
exec msdb.dbo.sp_help_job
@job_name = @job_name
@job_aspect = 'job',
@execution_status = 1
however I can't put the results of that proc into a temp table because I get this error:
Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.
Any ideas? I'd like a T-SQL solution.
Thanks,
Mark.
Hi everyone! Good day!
I'm not really sure if my question should fall on Service Broker or T-SQL, but I hope someone helps me with this... After activating the stored procedure assigned to the queue, is there any way for me to find out if the stored procedure is already finished executing?
I have successfully sent messages to my queue but I have no way to know if all the processing is already done.
Thanks so much!
I am working with a stored procedure that needs to roll up a week number column once a week - columns are numbered 1-10, 1 being this week, 2 being last week and so forth
Once a week the 10th column is deleted, the 9th becomes 10, the 8th becomes the 9th and so forth and the 1st is calculated the week numbers are getting all screwed up - and we think it's because one statement starts before the one before it completes the statements go like this:
delete theTable where week_num=10;
update theTable set weeknum=10 where weeknum=9;
update theTable set weeknum=9 where weeknum=8;
and so forth
is that the reason? is there any way not to start one statement until the one before it finishes?
Dear Friends,
In the control flow, I have more than one sequence containers, and I have a script component that I want to be executed only when of 2 last sequence finished with sucess... these 2 sequences does not have any relation with each other...
Regards!
Dear All
Please I need an urgent help
After i finished all Transaction Log Shipping Configuration.
I tried to use the database in the secondary database but i couldn't access it
i saw it in SQL Managment Studio as (Restoring......)
i tired to make a database snapshot from it , i had a message
Msg 1822, Level 16, State 1, Line 1
The database must be online to have a database snapshot.
Please urgently
Hello,
We are running Windows Server 2003 SP 1 and trying to upgrade SQL 2000 SP 4 to SQL 2005 using the command line.
The process finishes in under ten minutes. Summary.txt file we have this information:
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup_<ServerName>_SQL.log
Last Action : ValidateUpgrade
Error String : The installer has encountered an unexpected error. The error code is 2259. Database: Table(s) Update failed
Error Number : 2259
In the log file named SQLSetup_ServerName_Core.log I found the following:
Error: Action "LaunchLocalBootstrapAction" threw an exception during execution. Error information reported during run:
"C:Program FilesMicrosoft SQL Server90Setup Bootstrapsetup.exe" finished and returned: 1627
Aborting queue processing as nested installer has completed
Message pump returning: 1627
After receiving this info, I can navigate to the setup.bat for the SQL 2005 upgrade and complete the upgrade without error. We are planning on 500 of these, so manual updates is a very ugly concept.
I'd appreciate any and all ideas on where to go from here.
Most Sincerely.
We've recently upgraded to SQL Server 2014, and are now using SSIS integrated with Visual Studio. We have a SSIS project which contains about 20 packages which are nested in Sequence Containers and executed concurrently. These packages have been set up as project references.
The problem is that when I press the start button to run the packages, they all light up green reporting completion before the data has finished loading into the SQL database. If I press the stop button without waiting a sufficient length of time, then not all of the data gets loaded. i.e. a certain number of rows will be missing from some of the SQL tables.
If I click through to the individual package items and check the data flow progress while running, some of the data flows appear to hang at a certain number of rows without ever reaching completion. The number of rows indicated in the data flow is incorrect - i.e. it will count up to ~150,000 and stay there indefinitely in the running state, when in actual fact there are ~500,000 rows to load.
To clarify, the main package will show all items green and display the "Finished: Success" message in the log window, however when I drill through to certain packages in the set, they'll be stuck in the yellow running state, with no way of knowing whether they've actually completed or not.
My current workaround is to just wait a certain length of time before pressing the stop button. This bug doesn't seem to inhibit rows being loaded - it just incorrectly identifies the point when the load finishes, causing people to terminate the load prematurely.
This issue only occurs if I run the project from the main package container. If I execute the child packages individually, they correctly report the number of rows being loaded and light up green once complete.
oK if anyone remembers the problems with my Update/termination Trigger I found it works best if I do it as a Stored procedure instead of a trigger. Thanks again for your help guys. I really appreciate it
View 3 Replies View RelatedHi:
I create a DTS package in 'Copy SQL Objects Task' to transfer 3 stored procedures from my local SQL msdb to remote testing msdb database.
Execution is 'successful' but the destination server msdb does not have the 3 stored procedures.
Any hint on this 'vanished' copy objects?
thanks
-D
Just a thought.
If an issue is resolved please note that the problem has been resolved. Because there are "many ways to skin a cat" it would be helpful to anyone else with a similar problem (or someone trying to learn) what the solution was.
Hi expert,
I use a SQL Job that makes use of the BACKUP command. How can I check this job success or not?
Thanks
Hey all
Which system variable do I use if I would like to transfer success message into an SQL table? I'm running a task to import data into an SQL table. And I'm using the System::ErrorDescription variable (Execute SQL task) to insert error messages into a table but I would really appreciate it if someone could tell me what system variable would insert 'success' message into an SQL table. Tried a lot of searching on the net but couldn't find anything.
Many thanks,
Rupa
Has anyone here been involved in a successful commercial Data Warehouse project?
If so, what was your role? How big was the project? Did you do it all or was their one guy data modelling, one for ETL, one for Client-side, one project managing, etc...? What tools did you use? Was it in SQL2000? 2005? Did you use DTS/SSIS for your ETL or Sprocs? Did you use SSAS? Were you writing lots of MDX to meet client-side needs? Or did you use a completely different toolset for all or some of this?
Did you use the Kimball approach? Or were you in the other camp?
Was your latency nightly or did you have some type of "real-time" capability?
I would prefer to hear from people who've actually been involved in successful projects (or failed projects if you would like to share with us why you think they failed). I'm not too interested in hearing from people that have never done it but have a theory as to how it should work (like me).
Many Thanks,
Coolerbob
(Or is there some other website forum I should be asking this question on? (like mdxteam, olapteam, datawarehouseteam or something)
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
Is there a parameter allowing to verify successful execution of a stored procedure?
Something that can be run as the last line in a stored procedure to verify its success...?
Thanks!
Hi Guys, basically I have a scheduled job in MSSQL7, which on completion e-mails one of the operators.....
How can I get this job to email more than one operator upon completion???
Cheers....
Trying to delete records associated with the same contact id from all the tables used for that contact. But I'm getting an error. Strange error because the table does exist and I've been able to do single deletes from that table. So, something tells me I'm not getting something right in my SQL statement. How do I delete records from all 3 tables using the same id (as opposed to doing 3 different SQL calls, one for each)?
-----------------
DELETE names, addresses, phones
FROM names AS n, addresses AS a, phones AS p
WHERE n.id = '12' AND a.id = '12' AND p.id = '12'
Error: Unknown table 'names' in MULTI DELETE
-----------------
(BTW, how do tag my code so it shows up in a code div?)
I apologize in advance for what is probably a really dumb question.
I have an SSIS package that is executing a number of data flow tasks simultaneously, and then all are followed by a single script step. I'm trying to get all the constraint arrows to look uniform by having them exit the DFTs on the right side and enter the script step on the left. Unfortunately, BI Dev Studio is putting the exiting arrows on the bottom, which looks awful and is difficult to follow. I can only move them between specific points on the bottom of the step. How do I move those arrows to the right side?
Thanks in advance.
How to use Xquery to get the success value and the result value from the following XML.
This XML is stored in a SQL Table XML field, i want to be able to get all the success and reslut values for all the records:
Here is my XML
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:ns1="https://data.abc.biz/mysoappathtest/?soap:customcalls:v1"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
[Code] ....
I am trying to get the result = 15810 and the Success value = true
Hello again to everyone,
Because of aleatory problems some of our Analysis Services Tasks fails and sometimes success with not know reasons. You run the task and it fails, the next time it fails, the next time is success, and so on aleatorily.
The solution of this is to put those tasks in a loop (ore someting similar) that only ends if all of the Analysis Services Tasks succeeds. If not, those tasks starts again. Do you know what I mean? How can I build this? I have investigated with checkpoints, with For Loops ... but still I dont find the way to do it.
Does anybody can help me? I suppose it's not so difficult, but I'm stuck in finding the solution...
Thankx once again.
JohnSSIS
I have an ActiveX Script Task in SQL Server 2000. It chooses one of two
possible success-paths depending on if a file exist or not. (Part of the old ActiveX Script for choosing next step is below)
I need to rewrite this for a Script Task in SQL Server 2005 but it seems like it
doesn't have this functionality and objects.
Does anyone know how to write the code for choosing the next step in a
Script Task or knows another way to solve my problem?
The package is not suppose to fail if the file is missing, it's must succeed and that's why I need two success-paths.
Regards,
Sara
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main ()
Dim pkg
Dim stpContinuePkg
Dim stpExit
SET pkg = DTSGlobalVariables.Parent
SET stpContinuePkg = pkg.Steps("DTSStep_DTSActiveScriptTask_4")
SET stpExit = pkg.Steps("DTSStep_DTSActiveScriptTask_21")
If ...... Then
Main = DTSStepScriptResult_ExecuteTask
stpContinuePkg.DisableStep = False
stpExit.DisableStep = True
Else
.......
End If
Main = DTSTaskExecResult_Success
End Function
I export all tables from serverA databaseAA to serverB (both SQL2k sp3)databaseAA with 'Success', but without any relationships. In SQL6.5 Tools of Transfer will bring all objects include relationship.
I also tried export "All Objects" which fails with unclear message. I just don't want to do a backup and restore....
thanks
David
Hi
I am writing a package that i want to have email me on two possible success scenarios.
essentially, this is the conditions:
IF @result = 0
BEGIN
EXEC master..xp_cmdshell @copyfile, NO_OUTPUT
PRINT 'Operation Successful'
END
ELSE
BEGIN
RAISERROR ('This operation failed. Error Code:01. The source and destination files are identical.', 0, 1)
END
If the first condition is met, I want to fire off an email stating success.
If the second condition is met (the RAISERROR) then i want to fire off a different email.
Now, the problem is, I am not sure how to flow it. Both conditions are successful, thus it always fires off the success email if i use an "On Success" flow.
How do i capture the RAISERROR before the email to tell it which email to send?
Does this make sense?
Hi
I am writing a package that i want to have email me on two possible success scenarios.
essentially, this is the conditions:
IF @result = 0
BEGIN
EXEC master..xp_cmdshell @copyfile, NO_OUTPUT
PRINT 'Operation Successful'
END
ELSE
BEGIN
RAISERROR ('This operation failed. Error Code:01. The source and destination files are identical.', 0, 1)
END
If the first condition is met, I want to fire off an email stating success.
If the second condition is met (the RAISERROR) then i want to fire off a different email.
Now, the problem is, I am not sure how to flow it. Both conditions are successful, thus it always fires off the success email if i use an "On Success" flow.
How do i capture the RAISERROR before the email to tell it which email to send?
Does this make sense?
I recently upgrade my Enterprise Manager to Sql 2000 from 7.0; the databases are still SQL 7.0. Since then I've noticed that all scheduled jobs that are calling dtsrun.exe's are reporting successful but do not actually run. I think the total process time is 1 sec.The message from View History reports: "The step did not generate any output. Process Exit Code 0. The step succeeded." Every job that uses the dtsrun.exe reports the same message. I've ran the same command from DOS and it runs fine. I've executed some basic batch commands within the Scheduled Jobs and those fail as well. I can open the DTS package and execute those with success, but when these are scheduled and are executed from the Agent Jobs they fail. Can anyone shed some light on this please?
Thanks,
Jeff