Thx for all the quick responses. Actually I kinda lost my plot here....I knew the issue..Here is how it goes...I think every Exec() or SP_ExceuteSQL statement has a scope of...

I dont think u can use optional parameters but a way to achieve this would be to use variables with default values of Null. But you need to check for...

I am creating the #temp table in the Dynamic Query which is executed using sp_executesql. Can i not work this way....if so whats the constraint in it?

Cheers,

As far as i see this the major advantages of using table variables in sps are

1) They result in less recompilations

2) These variables when used in transactions involve less...

As far as i see this the major advantages of using table variables in sps are

1) They result in less recompilations

2) These variables when used in transactions involve less...

Sorry missed part abt value returned by DTSTaskExecResult_Success.

This value merely indicates if the package call succeeded or failed.

Usually its used like below:

*************************************

If Err.Count = 0 Then   

Main = DTSTaskExecResult_Success

Else

Main...

This variable is usually used to return a value from the function by assigning it to the Main Method like

Main = DTSTaskExecResult_Success

So the value returned by the second package in...

I dont understand what you are trying to do. Simply try this.

Put the 3 lines in a new step in a job under master db selection.

I have scripted a similar...

Hi,

Check the value of this Internal Variable that is provided in DTS itself

DTSTaskExecResult_Success

Lemme know if this helps

Cheers,

Hi,

What task are you using to do the transformation..?

Cheers

Hi,

Try this instead..this shud work fine!

declare @nam varchar(100)

set @nam = 'C:\xyz_'+ convert(varchar(10),getdate(),112) + '.bak'

backup database xyz to disk = @nam

Cheers

That one is a good one M8!! But doesnt exactly work as i require it to in my case.

Since there is a possibility of multiple status changes in the...

xp_availablemedia only gives u the details of the backup devices that are connected to the server. Which in this case happen to be the Disk Drives. To get the drive...

Try this

master..xp_fixeddrives

But remember you need to have appropriate rights. I guess u already do.

Cheers!

You can also use "With Log" option of RaiseError (....).

And if you want you can also customize the Error Message i.e create your own.

Cheers,