Hi:
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
I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong.
As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.
Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?
If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?
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 RelatedJust 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)
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
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
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
Hi all,
I have a stored proc that uses xp_cmdshell to boot off a batch file on the NT side of the box (box OS is Windows 2000 Advanced Server).
Here is the pertinent code:/*----- Kick off the NT bat job to suck over the data through the web service pipe*/
SELECT @NTCommand = 'D:TradeAnalysisWondaDataStoreJobsPullFromWONDA _InstitutionalRankings.bat ' + CONVERT(varchar(10), @ReqDate, 101)
EXECUTE @e_error = master.dbo.xp_cmdshell @NTCommand
SELECT @m_error = CASE WHEN ISNULL(@e_error, 0) <> 0 THEN (@e_error + 50000) ELSE @@Error END
IF @m_error <> 0 GOTO ErrorHandlerThe trouble is that the batch file is failing (soft error, caught internally to the batch file, which then kills itself, screaming loudly all the way).
The batch file is using the following "voice" in which to scream in pain as it dies (a.k.a., using this code to terminate itself, which kills the cmd shell and returns 13 as an error code)REM WonDBService.exe says we failed
Date /T
Time /T
EXIT /B 13
Meanwhile, back at the ranch (errr...back in stored procedure), what is being returned is a ZERO (in the first code block, @e_error is being set to ZERO when the xp_cmdshell returns from the bat file.
So, now that I have ruled out the obvious *LOL* how can I get my xp_cmdshell to realize it has failed miserably at the one, tiny, simple, not-too-much-to-ask, job that it is designed to do?
Is there some example of how i can add a return status into my stored procedure to have an extra field where I can verify if it runs successfully
1 if success
-1 if error (server, etc)
0 if empty record return
Thanks
I have an ssis package with three tasks. I want to force the execution result of the package to success all the time. I execute the package from a .net application. I tried to place my tasks in the package inside a sequence container and created a new script task which would execute when the any of the tasks in the container failed. I am setting the result of the package to success in this script task. This is not correctly working for me. Even if one task in the package failed and the last script task went to success, the .net app is receiving a failure status. Is there any way to handle this in the ssis package than in the .net app. There might be an easy way, but I am unable to find it.
Thanks in advance!!
hi all,
please help,
i using Execute DTS 2000 Package Task to create a package.dtsx, and execute successful in VS2005
but when i created it as a job and it fail to run.
the package.dtsx is calling a package2000.dts, which having 2 steps to run.
i execute the job by step, and the i found that the 1st job is running successful but the 2nd job was fail.
may i know where can i found the error log? or any where to debug it?
please help......
thanks 1st.....
I have a number of stored procedures that run one after the other. How do you code to get the success or failure so that some logic can be applied accordingly? I've heard of the TRY CATCH structure, but I new and have yet to use it. How many different ways can success or failure be handled in code?
View 4 Replies View RelatedI created a SSIS package that imports a file then moves the file to a folder called processed.
I created a job that runs the package. The pack works fine when I run it from the SQL Storage Area. When I run the job I get the following message:
Started: 9:58:15 AM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 9:58:15 AM Finished: 9:58:21 AM Elapsed: 6.047 seconds. The package executed successfully. The step succeeded.
showing that the job supposedly completed successfully.
I know that the package the not run because the data did not get imported and the file did not move.
Can anyone explain the package may not run but we still get a job completion status of success?
Hello,
When I run my package, a task will fail, however, the package will claim that it was successful. Why is this, and how can I trigger a failed package when one task fails?
Thanks in advance.
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies View Related
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
Hi,Say I have a stored procedure which does two INSERT operation.How can I check if the first INSERT succeeded in order to know if Ishould carry on and do the second one ?Regards
View 1 Replies View Related