I have an SSIS package using an exec sql task being past a variable.
The variable is mapped to a parameter that is then passed to a ##
stored procedure within the sql task, and I am getting the following
error.

Unable to derive parameter information for a temporary stored
procedure.

The reason for wanting to use tempdb is that I need to dynamically
create a database given the database name.

Ideas?


if object_id('tempdb..##buildConstants ','U') is not null
DROP TABLE ##buildConstants
create table ##buildConstants
(
constantName varchar(128) Primary Key,
constantValue varchar(128)
)

GO

IF OBJECT_ID('Tempdb..##proc_insertBuildConstants','P ') is not null
drop procedure ##proc_insertBuildConstants

GO
CREATE PROCEDURE ##proc_insertBuildConstants
@constantName varchar(128),
@constantValue varchar(128)
AS
INSERT INTO ##buildConstants (constantName, constantValue)

VALUES
(
@constantName ,
@constantValue
)

The exec sql task looks like
exec ##proc_insertBuildConstants 'dbname', ?

This all works if I create a non-temporary stored procedure.

Thanks

While you may desire to use TempDb, the stored procedure, from what I see,
does not need to be a ##TempStored Procedure. (##buildConstants is a
'global' temp table, so if it exists, drop it, and create a new one.

So go with what you know works, a "non-temporary stored procedure".

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"Marty" <frank0288 (AT) yahoo (DOT) com> wrote

Thanks Arnie, the problem is that my target db does not exist at the
begining of the package. I'm probably going to go with creating a
"non-temporary stored procedure" in master or temp. The real issue is
that I can't assign a parameter direct to a variable or do something
like

CREATE DATABASE ?
within the exec sql task.

I as looking for something a bit less brutish but I'll adapt.


Thanks again.


Arnie Rowland wrote: