Im going mad trying and failing to figure this out . running SQl server 2000
(sp3). I have had code snippets and examples thrown at me, yet im missing
something fundermental .. if i use the following code in TSQL i get a
sucess on the executon of the DTS
Declare @.Packagename varchar(255) -- Gets most recent Version
Declare @.Userpwd Varchar(255) -- Login Password
Declare @.Intsecurity bit
Declare @.pkgpwd varchar(255)
Declare @.hr int
Declare @.Object int
Set @.Intsecurity = 0
Set @.Userpwd = 'MyPassword'
set @.pkgpwd = NULL
Set @.Packagename = 'TESTDTS'
-- Create the Package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.Object OUTPUT
If @.hr <> 0
Begin
Print 'Error Creating Package'
End
Else
Begin
Print 'Package Created'
End
-- Load the package
Declare @.svr varchar(15)
Declare @.login varchar(100)
Select @.login = 'MyUserName'
Select @.svr = @.@.serverName
Declare @.flag int
Select @.flag = 0
if @.intsecurity = 0
if @.userpwd = Null
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.ServerUserName=@.login, @.PackageName=@.packagename,
@.Flags=@.flag, @.PackagePassword = @.pkgPwd
else
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.ServerUserName=@.login, @.PackageName=@.packagename,
@.Flags=@.flag, @.PackagePassword = @.pkgPwd, @.ServerPassword = @.userpwd
else
begin
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object, 'LoadFromSqlServer',NULL,
@.ServerName=@.svr, @.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword
=
@.pkgPwd
end
If @.hr <> 0
Begin
Print 'Error Loading Package'
End
Else
Begin
Print 'Package loaded'
End
EXEC @.hr = sp_OAMethod @.object, 'Execute'
If @.hr <> 0
Begin
Print 'Error Executing Package'
End
Else
Begin
Print 'Package Executed'
End
-- unitialize the package
EXEC @.hr = sp_OAMethod @.object, 'UnInitialize'
If @.hr <> 0
Begin
Print 'Error UnInitializing Package'
End
Else
Begin
Print 'Package UnInitialized'
End
-- release the package object
EXEC @.hr = sp_OADestroy @.object
If @.hr <> 0
Begin
Print 'Error Releasing Package'
End
Else
Begin
Print 'Package Released'
End
That use's SQL Authentication, however i need to use Wondows Authenticaton.
i can log on query analiser using windows Authentication, yet no matter what
i seem to do to the variables to try and get it to use Windows
Authentication it always fails. The SQL server is currently set for SQl and
Windows Authentication. Can anybody shed any light on what im overlooking ?> if @.userpwd = Null
What's your ansi_nulls setting? Use the standard: "if @.userpwd is Null".
ML|||ML, this code was taken from a help page.. I have amended as you suggested
but the results are still the same . I Log onto the network using my logon
user name and password. I can open a session of T-SQL and log on usoing
windows authentication without a problem.. so i dont understand why the
loadfromsqlserver wont allow me to use windows Authentication
"ML" wrote:
> What's your ansi_nulls setting? Use the standard: "if @.userpwd is Null".
>
> ML|||What about the @.Intsecurity variable? Have you tried setting it to 1 ?
ML|||ML
I have set @.Intsecurity = 1 , @.Userpwd = Null, @.login = SUSER_SNAME() and
@.Flag = 256 . still get an error -2147217843 when loading the package
"ML" wrote:
> What about the @.Intsecurity variable? Have you tried setting it to 1 ?
>
> ML|||Please post the entire error message.
Have you tried contacting the author of the script?
Is there a special reason behind executing the DTS package from T-SQL?
ML|||ML,
Im going to give up on this idea, Ive spent days at this problem with no
solution. I dont seem to be able to get any sort of error message back, onl
y
-2147217843 when i interrigate the value of @.hr. I can not find that error
number in the sql server books online. I have looked in the Server Logs ..
but nothing in there. Im a a total loss as to why this wont work with Window
s
Authentication and more frustrated at the fact that i carnt find out what is
causing the problem. I had origionaly tried to hav this run from a VB6
application that gets fired as an event from a FTP server, but then i was
still having issues on accesss rights across the network when trying to open
a txt file. I will fire a question of to the author of the code... see if he
can help at all .... if not its back to the drawing board
thanks for taking the time out to try and help
"ML" wrote:
> Please post the entire error message.
> Have you tried contacting the author of the script?
> Is there a special reason behind executing the DTS package from T-SQL?
>
> ML
