MS SQL Server DTS - DTS "Invalid Pointer" error (using table vars?)

Author

DTS "Invalid Pointer" error (using table vars?)

Mike Austin

2005-08-23, 1:23 pm

I'm running two transformation tasks in my DTS package that are using table
variables. Basically, I am using two table variables to pre-filter my data
before the final query (which cuts the execution time by 2/3). This works
fine from Query Analyzer. However, when I put this code in a transformation
task, I receive an "Invalid pointer" error. No additional information is
provided.

Here is the code:

DECLARE @Types TABLE (
id smallint,
typecode smallint,
name varchar(300),
sequence smallint,
description text
)

INSERT INTO @Types (id, typecode, name, sequence, description)
SELECT t.id, t.typecode, t.name, t.sequence, t.description
FROM dbo.types t
WHERE t.typecode = 34

DECLARE @Questionaire TABLE (
id int,
questionaire_type_id
int,
project_id int,
member_id int,
date_updated datetime
)

INSERT INTO @Questionaire (id, questionaire_type_id
, project_id, member_id,
date_updated)
SELECT q.id, q. questionaire_type_id
, q.project_id, q.member_id,
CAST(CONVERT(char(8)
, q.date_updated, 112) AS DateTime) AS date_updated
FROM dbo.questionaire q
WHERE q. questionaire_type_id
= 1

SELECT p.id AS ProjectID, p.company_id AS CompanyID, p.project_lead_id AS
MemberID,
pbc.typecode_id AS SID, pbc.initial_estimate AS Rate,
q.date_updated AS CreationDate, q.id,
q. questionaire_type_id
, t.name
FROM dbo. project_budget_costs
pbc
INNER JOIN dbo.projects p ON (pbc.project_id = p.id)
INNER JOIN @Questionaire q ON (p.id = q.project_id AND p.project_lead_id =
q.member_id)
INNER JOIN @Types t ON (pbc.typecode = t.typecode)

TIA,

Mike

Fred

2005-09-30, 3:23 am

Mike
I am having the same problem transforming a stored proc containing a table
variable used as a prefilter to a query. If I write the result set into a
table, the table transforms, therfore it is not a data related problem. I
wonder if it is a scope problem with the table variable? Have you had any
luck resolving this?
--
Fred

"Mike Austin" wrote:

> I'm running two transformation tasks in my DTS package that are using table
> variables. Basically, I am using two table variables to pre-filter my data
> before the final query (which cuts the execution time by 2/3). This works
> fine from Query Analyzer. However, when I put this code in a transformation
> task, I receive an "Invalid pointer" error. No additional information is
> provided.
>
> Here is the code:
>
> DECLARE @Types TABLE (
> id smallint,
> typecode smallint,
> name varchar(300),
> sequence smallint,
> description text
> )
>
> INSERT INTO @Types (id, typecode, name, sequence, description)
> SELECT t.id, t.typecode, t.name, t.sequence, t.description
> FROM dbo.types t
> WHERE t.typecode = 34
>
> DECLARE @Questionaire TABLE (
> id int,
> questionaire_type_i
d int,
> project_id int,
> member_id int,
> date_updated datetime
> )
>
> INSERT INTO @Questionaire (id, questionaire_type_id
, project_id, member_id,
> date_updated)
> SELECT q.id, q. questionaire_type_id
, q.project_id, q.member_id,
> CAST(CONVERT(char(8)
, q.date_updated, 112) AS DateTime) AS date_updated
> FROM dbo.questionaire q
> WHERE q. questionaire_type_id
= 1
>
> SELECT p.id AS ProjectID, p.company_id AS CompanyID, p.project_lead_id AS
> MemberID,
> pbc.typecode_id AS SID, pbc.initial_estimate AS Rate,
> q.date_updated AS CreationDate, q.id,
> q. questionaire_type_id
, t.name
> FROM dbo. project_budget_costs
pbc
> INNER JOIN dbo.projects p ON (pbc.project_id = p.id)
> INNER JOIN @Questionaire q ON (p.id = q.project_id AND p.project_lead_id =
> q.member_id)
> INNER JOIN @Types t ON (pbc.typecode = t.typecode)
>
> TIA,
>
> Mike

Sponsored Links