I am running a DTS package from an Access Project. The
package seems to execute just fine, however the step that
runs the stored procedure is having a slight problem - it
errors out telling me that it can not find the stored
procedure. I have double checked to make sure that it
actually exist in the sql server database - it does! So,
what could the problem be?

Any thoughts are greatly appreciated.
Cathi

In article <065d01c3725d$bc123260$a101280a (AT) phx (DOT) gbl>, Cathi
<cathi10 (AT) comcast (DOT) net> writes
Obvious ones, are you connecting to the correct server? More likely, are
you connecting the correct DB? Try qualifying the procedure with a three
part name, database.owner.proc_name.

If you are still confused by the problem try running a SQL Profiler
Trace to watch the command be issued against the server and this may
help spot the error.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

oops,should have explained better. here is the code that
I am running, the database is assigned via a combo box
within access.

Private Sub cmdExecutePkg_Enter()

Dim oPKG As DTS.Package, oStep As DTS.Step
Set oPKG = New DTS.Package

Dim sServer As String, sUsername As String, sPassword
As String
Dim sPackageName As String, sMessage As String
Dim lErr As Long, sSource As String, sDesc As String

'Set Global Variable
oPKG.GlobalVariables("DBName").Value =
Me.cboDBName.Value

' Set Parameter Values
sServer = "ldcsql03"
sUsername = "test"
sPassword = "test2"
sPackageName = "trk2_elig_import_source"


' Load Package
oPKG.LoadFromSQLServer sServer, sUsername, sPassword, _
DTSSQLStgFlag_Default, , , , sPackageName

' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next

' Execute
oPKG.Execute

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult =
DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource,
sDesc
sMessage = sMessage & "Step """ & oStep.Name &
_
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf &
vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name &
_
""" Succeeded" & vbCrLf & vbCrLf

' Display Results
msgbox sMessage

End If
Next

oPKG.UnInitialize

Set oStep = Nothing
Set oPKG = Nothing


End Sub

You are setting a global variable, before you load the package. You need to
load the package, then set the global variable value, then execute.


--
Darren Green
http://www.sqldts.com

"Cathi" <cathi (AT) us1 (DOT) net> wrote