Re: ActiveX script works in DTS but not in job, why?
- From: "Peter W. DeBetta" <debettap@xxxxxxxxxxx>
- Date: Wed, 2 Aug 2006 08:35:41 -0500
Ah, I figured it out! I had to reproduce it here to see what was really
going on.
The flags argument constant DTSSQLStgFlag_UseTrustedConnection (256) is not
known outside the dev environment, so it's using a default value of zero (0)
for the flags argument which is causing the login to be attempted using SQL
Authentication, which of course fails since there are no credentials.
So replace this...
MyDTS.SaveToSQLServer
"(local)",,,DTSSQLStgFlag_UseTrustedConnection,,,,,True
with this...
MyDTS.SaveToSQLServer "(local)",,,256,,,,,True
and it should work.
--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"RJ" <rjbook@xxxxxxxxxxxxxxxx> wrote in message
news:7E26B918-A4D0-4E45-B95E-7C262F2425F4@xxxxxxxxxxxxxxxx
Hello Peter,
I would not expect permissions to be an issue since the xx\xxx is the
Domain
Account that SQL Agent runs under and it is a Local Admin on the box. Just
as
a test though I logged on to my machine as the xx\xxx User and was able to
run the ActiveX script in a DTS package same as when I was using my
Windows
Account.
As another test I saved the script as a .vbs file and tried running it, I
get the same error message as when the script is run from the job, since I
can't paste the screen shot here is the error from the dialog box;
Windows Script Host
Script: C:\Temp\DB_Build\Load_DTS_PAckages.vbs
Line: 11
Char: 8
Error: Invalid authourization specification
Code: 8004E4D
Source: Microsoft OLE DB Provider for SQL Server
So again it would seem that the ActiveX Script Task within the DTS package
is providing some sort of "support" or "default authorization" that is not
available when running the script directly. I am trying to figure out what
that would be since I need to be able to load DTS packages from either a
SQL
Job or directly from Script.
Ultimately what I am trying to do is fully automate the Deployment of SQL
2000 databases including DTS packages. I have come up with a "home grown"
solution for the databases but installing the DTS packages is still an
issue.
I have about 2 dozen packages I need to deploy and I have used the 3rd
party
tool DTSBackup 2000 which works well but it is still a manual process of
selecting and moving the .dts files.
Does anyone have a workable solution that can deploy multiple DTS packages
as part of an Installer package or Custom script? The other requirement is
the DTS packages may already exist on the Target Server so I need to be
able
to drop them before reloading from Script from the local file system.
Thanks.
-RJ
"Peter W. DeBetta" wrote:
When you execute the package, are you logged in as the same user under
which
the job is executing? If not, have you tried to log in as the xx\xxx user
(from the job error) and run the DTS package?
--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"RJ" <rjbook@xxxxxxxxxxxxxxxx> wrote in message
news:9129C242-E9B5-4D29-848C-D8242FA86745@xxxxxxxxxxxxxxxx
I have created a small ActiveX script which can load multiple DTS
packages
from the File System up to a SQL 2000 Server. The script works just
great
when executed as an ActiveX script in a DTS package like so;
Dim fso, f, f1, fc
Dim s
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("C:\Temp\DB_Build\DTS")
Set fc = f.Files
For Each f1 in fc
Dim MyDTS
Set MyDTS = CreateObject("DTS.Package")
s = "C:\Temp\DB_Build\DTS\" & CStr( f1.Name )
MyDTS.LoadFromStorageFile s,""
MyDTS.SaveToSQLServer
"(local)",,,DTSSQLStgFlag_UseTrustedConnection,,,,,True
Next
set fso = nothing
set f = nothing
set f1 = nothing
set fc = nothing
set MyDTS = nothing
set s = nothing
I would like to be able to run this Script directly in a SQL job but
when
I
do I get the following error message;
Executed as user: xx\xxx. Error Code: 0 Error Source= Microsoft OLE DB
Provider for SQL Server Error Description: Invalid authorization
specification Error on Line 10. The step failed.
It seems to "not like" the LoadFromStorageFile instruction when saved
in a
job but again the exact code works just fine when run within a DTS.
My question, if anyone knows, is what is the difference when running an
ActiveX script within a DTS versus running the script directly in a
job?
I'm
trying to figure out if I am wasting my time trying to do it this way.
Any
help is appreciated. Thanks.
-RJ
.
- Follow-Ups:
- References:
- Re: ActiveX script works in DTS but not in job, why?
- From: Peter W. DeBetta
- Re: ActiveX script works in DTS but not in job, why?
- From: RJ
- Re: ActiveX script works in DTS but not in job, why?
- Prev by Date: Re: ActiveX script works in DTS but not in job, why?
- Next by Date: SSIS - combine two rows into one
- Previous by thread: Re: ActiveX script works in DTS but not in job, why?
- Next by thread: Re: ActiveX script works in DTS but not in job, why?
- Index(es):
Relevant Pages
|
