×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

running a DTS package from Query Analyzer

running a DTS package from Query Analyzer

running a DTS package from Query Analyzer

(OP)
I have a DTS called myDTS wich has one input parameter called inputParam, I am trying to run it from query analyzer and wrote this query

CODE


EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
    print 'error create DTS.Package'
    RETURN
END

EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer', NULL,
  @ServerName='myServer', @PackageName='myDTS', @Flags=256
IF @hr <> 0
BEGIN
    PRINT '***  Load Package failed'
END

what is wrong with the above code and how can excute the package with the input parameter?

Thanks

RE: running a DTS package from Query Analyzer

Please don't cross-post.

You need to think about the DTS object model to do this.

From VB you'd use this to add a global variable =

CODE

objDTS.GlobalVariables.AddGlobalVariable("gvName", "gvValue)

So, I think you'd need to use sp_OAGetProperty to retrieve the GlobalVariables collection, and then use sp_OAMethod to execute the AddGlobalVariable method on this object.

Here's a start (not tested at all, but try adding it after what you've already got)

CODE

--store handle for GlobalVariables collection here
declare @object2 int

EXEC @hr = sp_OAGetProperty @object, 'GlobalVariables', @object2 OUTPUT
IF @hr <> 0
BEGIN
    PRINT 'Cant Get Global Vars'
END

EXEC @hr = sp_OAMethod @object2, 'AddGlobalVariable', @Name = 'myVarName', @Value = 'myVarValue'

IF @hr <> 0
BEGIN
    PRINT 'Cant Add Global Var'
END

Hope this helps,

Alex

----signature below----
Majority rule don't work in mental institutions

My Crummy Web Page

RE: running a DTS package from Query Analyzer

(OP)
THank you Alex

RE: running a DTS package from Query Analyzer

(OP)
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer', NULL,
  @ServerName='myserver', @PackageName='mypackage', @Flags=256
IF @hr <> 0
BEGIN
    PRINT '***  Load Package failed'
END

how do i use my windows login without mentioning my user name and password

(window login has DBO access)

THanks

RE: running a DTS package from Query Analyzer

SQL Server 2000 doesn't really support impersonation.  (SETUSER may work, but its' use is discouraged and to execute users need to be sysadmin or dbo)

Are you intending for this package to actually be executed by users from Query Analyzer, or from a different Client Application (of the home-grown variety)?

----signature below----
Majority rule don't work in mental institutions

My Crummy Web Page

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close