Re: DTS Data Pump VBScript runtime error Type mismatch

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: -=JLK=- (jlkguard-newsgroups_at_yahoo.com)
Date: 12/02/04 

Date: Thu, 2 Dec 2004 09:10:36 -0500

While I didn't solve the problem I did come up with a work around. I
replaced the SQL Source Query in the Data Driven Query Object from:

select pk,
       coalesce(pk_CustomerOrganizationDim,0) as pk_CustomerOrganizationDim,
       1 as fk_CustomerIndividualDim,
       coalesce(pk_eventdim,0) as pk_eventdim,
       coalesce(pk_datedim,0) as pk_datedim
from xntisuser x left outer join ebiework..CustomerOrganizationDim c on
(c.customercode = x.c01_depositaccount and c.CustomerCodeSource = 'NTIS')
                  left outer join ebiework..eventdim e on (e.eventsource =
'NTIS' and e.eventname = (Case processaction when 'A' then 'Created' when
'C' then 'Updated' when 'D' then 'Deleted' end))
                  left outer join ebiework..datedim d on (d.Dateyyyymmdd =
convert (varchar (50), getdate(), 112))
where x.processstatus = 'P2'
   and x.batchnumber = ?
   and x.processaction <> 'I'

To:

select *
from xntisuser
where processstatus = 'P2'
   and batchnumber = ?
   and processaction <> 'I'

Each of the Primary Keys needed for the record being created are now looked
up using "Lookup" objects rather than doing it all at once in a single SQL
statement. I don't know if this will help anyone else but at least now the
Active X vb scripts work as expected. Maybe DTS gets confused with the
weird SQL statement, though that statement works fine in Query Analyzer.

Thanks,

James.

"-=JLK=-" <jlkguard-newsgroups@yahoo.com> wrote in message
news:urxFFy61EHA.1564@TK2MSFTNGP09.phx.gbl...
>I have a strange problem. What was working fine on all machines, now does
>not work at all on one of three machines. As far as I can tell all three
>machines have exactly the same configuration and identical hardware. We
>are running:
>
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
> Windows NT 5.1 (Build 2600: Service Pack 2)
>
> (It is really service pack 3, but the select @@version produces the above
> on all three machines).
>
> The machine with the problem can not use "IF" statements that have
> variables in them in ActiveX scripts. I.e.
> IF 1 = 1 THEN --Works Fine
>
> Dim Var1, Var2
> Var1 = 1
> Var 2 = 1
> IF Var1 = Var2 THEN -- Throws the Type MisMatch error.
>
> What's really strange is it doesn't do it in all DTS packages however
> recreating a package with the problem just duplicates the problem, but
> only on one of the three machines.
>
> Has anyone seen or heard of anything like this? I have uninstalled and
> reinstalled SQL Server, changed the packages to talk to a different server
> while running on client but always with the same result.
>
> TIA,
>
> James Knowlton
>

Relevant Pages

  • Re: [Microsoft][ODBC SQL Server Driver]Optional feature not implememted
    ... found that the machines running MDAC 2.7 or above don't raise the exception. ... If I run the same query in the query analyser of SQL I get my ...
    (borland.public.delphi.database.ado)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)