I can't seem to use a parameter marker (i.e. the ? character) in my Execute
SQL Tasks Properties and then be able to select [Parameters...] to bring up
the Parameter Mapping dialog because the DTS designer keeps generating the
error “Syntax error or access violation”.
I’ve read more than these (links) that discuss this problem, not
specifically with the DTS designer:
http://support.microsoft.com/kb/330199
http://support.microsoft.com/kb/293790
However, my problem is specifically with the DTS designer with Microsoft OLE
DB Provider for SQL Server, not my own code calling the SQLDescribeParam with
said MDAC version. For the record I’m running SQL Server 2000 v8.00.2050
(w/SP4) and my MDAC is version 2.8 w/SP2 on Windows Server 2003 R2 w/SP2.
So far the only way I can define my parameter mappings is by coding the
DTSTask_DTSExecuteSQLTask_<n> InputGlobalVariableNames using Disconnected
Edit. But of course, I have to be syntactically careful to use a semicolon
to without white spaces to separate more than one global variable name for my
parameter markers. I suppose I could always write some work flow ActiveX
code to set the InputGlobalVariableNames as well. All this is pretty hinky
if you ask me, probably one of many reasons of why DTS was dumped altogether.
This isn’t very maintenance friendly and I was wondering if anyone knows
anything more about this, e.g. other experiences with this, Microsoft known
issue but not being fixed (it is SQL Server 2000 after all), or an actual
honest to goodness fix/resolution for this?
Thanks,
--Craig Schreiner
Austin, TX USA