"zomer" <non***@gmail.com> wrote in message
news:1153404235.281843.70210@s13g2000cwa.googlegroups.com...
> Hi all,
> I have a SQL statement that is longer then 8000 characters. So I have
> split it up... store it in two variables.... after Execute
> (@var1+@var2).... i get a select statement with parameters in it and is
> run by SSIS dynamic SQL task. ...which should store it in a string
> variable.... this part is failing. If i use Slect @var1+var2.... this
> runs but I dont get the complete SQL statement (only first 8000
> characters) and the rest is truncated..... that results in error in the
> next step of the package. Is the type output of execute (@var1+@var2)
> not equal to select @var1+@var2???
>
>
> here is code...
>
> DECLARE @DATE VARCHAR(30)
> DECLARE @VAR1 VARCHAR(8000)
> DECLARE @VAR2 VARCHAR(8000) -- I have tried (MAX) but that does not
> work either...
>
> SET @VAR1='SELECT '+'''SELECT DATE_STAMP,TIME_STAMP,VENDOR,...... '
> SET @VAR2='Where A1.START_DATE>=TO_DATE(........'
>
> Execute (@VAR1+@var2)
>
> after execute i get the complete select statement....to be used by SSIS
> Execute SQL Task
>
> SELECT DATE_STAMP,TIME_STAMP,VENDOR....Where
> A1.START_DATE>=TO_DATE(........
>
> This select statement is then assigned to variable ERC..... but I get
> this error.... which is used to query OLE DB source
>
> An error occurred while assigning a value to variable "ERC": "The type
> of the value being assigned to variable "User::ERC" differs from the
> current variable type. Variables may not change type during execution.
> Variable types are strict, except for variables of type Object.
>

> What is the datatype for ERC? Would varchar(max) work?
>
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "zomer" <non***@gmail.com> wrote in message
> news:1153404235.281843.70210@s13g2000cwa.googlegroups.com...
> > Hi all,
> > I have a SQL statement that is longer then 8000 characters. So I have
> > split it up... store it in two variables.... after Execute
> > (@var1+@var2).... i get a select statement with parameters in it and is
> > run by SSIS dynamic SQL task. ...which should store it in a string
> > variable.... this part is failing. If i use Slect @var1+var2.... this
> > runs but I dont get the complete SQL statement (only first 8000
> > characters) and the rest is truncated..... that results in error in the
> > next step of the package. Is the type output of execute (@var1+@var2)
> > not equal to select @var1+@var2???
> >
> >
> > here is code...
> >
> > DECLARE @DATE VARCHAR(30)
> > DECLARE @VAR1 VARCHAR(8000)
> > DECLARE @VAR2 VARCHAR(8000) -- I have tried (MAX) but that does not
> > work either...
> >
> > SET @VAR1='SELECT '+'''SELECT DATE_STAMP,TIME_STAMP,VENDOR,...... '
> > SET @VAR2='Where A1.START_DATE>=TO_DATE(........'
> >
> > Execute (@VAR1+@var2)
> >
> > after execute i get the complete select statement....to be used by SSIS
> > Execute SQL Task
> >
> > SELECT DATE_STAMP,TIME_STAMP,VENDOR....Where
> > A1.START_DATE>=TO_DATE(........
> >
> > This select statement is then assigned to variable ERC..... but I get
> > this error.... which is used to query OLE DB source
> >
> > An error occurred while assigning a value to variable "ERC": "The type
> > of the value being assigned to variable "User::ERC" differs from the
> > current variable type. Variables may not change type during execution.
> > Variable types are strict, except for variables of type Object.
> >