Returning a database object in stored procedures

Returning a database object in stored procedures

Post by Brandon Lill » Tue, 07 Dec 1999 04:00:00



Is there any way to resolve a database object from a name?

For example, in TJob, I have PJ_Enterprise which is actually the name
of another database.  I want to perform a join to tables in the
database PJ_Enterprise, such like:

SELECT
  PJ_Enterprise,
  CASE PJ_Type
    WHEN 1 THEN RP_Name
    WHEN 2 THEN MC_Name
  END AS 'Name'
FROM
  TJob
    LEFT OUTER JOIN PJ_Enterprise.dbo.TReport ON PJ_Key = RP_Key
    LEFT OUTER JOIN PJ_Enterprise.dbo.TElectronic_Claim ON PJ_Key =
MC_Key

Of course the query doesn't work because PJ_Enterprise is not a
database, but a field value.  Is there anyway to do something like
this?  I am no SQL wizard, and can see no way to perform this
operation.

Thanks

Brandon

 
 
 

1. stored procedure call returns object error

I am running SQL Server 7.0 SP3 database using DTS Designer version SQL
2000. My package runs perfectly when run from Enterprise manager.  However
when I set it up as a job, it runs for a second and then gives me the
following error:

...ing...   DTSRun:  Executing...   DTSRun OnStart:
DTSStep_DTSActiveScriptTask_5   DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_5   DTSRun OnStart:
DTSStep_DTSActiveScriptTask_1   DTSRun OnError:
DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)      Error
string:  Error Code: 0   Error Source= Microsoft VBScript runtime error
Error Description: Object required      Error on Line 14         Error
source:  Microsoft Data Transformation Services (DTS) Package      Help
file:  sqldts.hlp      Help context:  1100      Error Detail Records:
Error:  -2147220482 (800403FE); Provider Error:  0 (0)      Error string:
Error Code: 0   Error Source= Microsoft VBScript runtime error   Error
Description: Object required      Error on Line 14         Error source:
Microsoft Data Transformation Services (DTS) Package      Help file:
sqldts.hlp      Help context:  1100      DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_1   DTSRun:  Package execution complete.
Process Exit Code 1.  The step failed.

In the package I have 2 activex scripts.  Each activex script instantiates a
dll which automatically connects to a database using ado.  In the first
Activex script, I activate the dll fine which automatically connects to the
database upon initializing.  I call a method (.RetrieveData) of the dll
which takes a SQLstring and then returns a recordset of data.  This all
works fine in the first Activex script of the package.  Now in the second
activex script, I instantiate the dll which again connects to the database
fine.   I call the same method (.RetrieveData), but in the SQLstring, this
time, I am sending a command to execute a stored procedure (This happens to
be Line 14 of the DTSStep_DTSActiveScriptTask_1.

It seems to me that there is a permissions issue, however I am unable to
determine why it is an issue.  The stored procedure explicitly grants
permission to the group - "public".

Any clues?

Many thanks in advance
Lorne

2. pdox3.5 picture

3. SQL Server stored procedure return value - how to access from ADO Command object

4. Running Openroad with an Oracle Database

5. Prblems returning a value from a SQL Stored Procedure to a RecordSet Object

6. ù?aù?a

7. return recordset from a store procedure in atl object

8. SQL Query

9. ADO Command Object - accessing MS SQL Server Stored Procedure return value

10. return select values in stored procedure to a calling stored procedure

11. Returning Rows from a Stored Procedure to another Stored Procedure

12. Returning a query result from a stored procedure into another stored procedure

13. RETURN VALUE in stored procedure not always returning to ADO