Problem with dynamic SQL syntax

I' m having a problem with the syntax when I'm trying to run a dynamic SQL
statement.
The code -
set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' Where
Date_ >='+'2001-01-01'+'
And CompanyNo Is Not NULL And Type <>'
exec sp_executesql @.sql
- gives me the error "Incorrect syntax near '>'." The purpose of the last <>
is to find the records where this field is empty (that's my understanding of
it...). The basic structure of the query is from a DTS Transform task, but
I'm trying to "convert" this whole task to TSql.
I've tried all sorts of different combinations of <> and ' but it still
won't do it. If I just prins the @.sql var. it looks fine.
The CompanyNo field is int(4) and the Type field is varchar(30).
Is there any other ways to check for an empty varchar field of can some of
you guide me to what it is I'm missing in my "set @.sql...." statement?
Best Regards
SteenWhat exactly do you mean by an empty field? Generally if
your field allows null and you don't set a value into
that field for a row, the field is set to NULL and you
can test for this by using "type IS NULL" in your where
clause.
"<>" is the operator for not equals and it is not a
singleton operator, it is a comparison operator. You
need something on both sides of the "<>" to compare to
each other.
For instance, if you are looking for rows where the type
field is not equal to a space, you could try "type <> ' '"
I hope that this helps.
Matthew Bando
bandoM@.CSCTechnologies-dot-com
>--Original Message--
>I' m having a problem with the syntax when I'm trying to
run a dynamic SQL
>statement.
>The code -
>set @.sql = 'SELECT *
INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
>
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
le_name+' Where
>Date_ >='+'2001-01-01'+'
> And CompanyNo Is Not NULL And Type <>'
>exec sp_executesql @.sql
>- gives me the error "Incorrect syntax near '>'." The
purpose of the last <>
>is to find the records where this field is empty (that's
my understanding of
>it...). The basic structure of the query is from a DTS
Transform task, but
>I'm trying to "convert" this whole task to TSql.
>I've tried all sorts of different combinations of <>
and ' but it still
>won't do it. If I just prins the @.sql var. it looks fine.
>The CompanyNo field is int(4) and the Type field is
varchar(30).
>Is there any other ways to check for an empty varchar
field of can some of
>you guide me to what it is I'm missing in my "set
@.sql...." statement?
>Best Regards
>Steen
>
>.
>|||Hi
Sorry if I wasn't very clear. I assume that the purpose is to check for an
empty field. The "original" code that's being used in the DTS Transform task
is "...AND CompanyNo is not NULL and Type <>'' ". It's not me that have
written the transform task, but I assume that this last piece checks if
there're any empty fields. It might be my understanding of it that's wrong,
but then I'd be happy to hear about it.
This SQL statement runs fine in the DST task and also when I run it in Query
analyser using fixed values, but when I do it with variables/dynamic SQL it
seems to fail and not accept this last bit.
Regards
Steen
"Matthew Bando" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:071c01c46e4c$74405700$a501280a@.phx.gbl...
> What exactly do you mean by an empty field? Generally if
> your field allows null and you don't set a value into
> that field for a row, the field is set to NULL and you
> can test for this by using "type IS NULL" in your where
> clause.
> "<>" is the operator for not equals and it is not a
> singleton operator, it is a comparison operator. You
> need something on both sides of the "<>" to compare to
> each other.
> For instance, if you are looking for rows where the type
> field is not equal to a space, you could try "type <> ' '"
> I hope that this helps.
> Matthew Bando
> bandoM@.CSCTechnologies-dot-com
> >--Original Message--
> >I' m having a problem with the syntax when I'm trying to
> run a dynamic SQL
> >statement.
> >
> >The code -
> >
> >set @.sql = 'SELECT *
> INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> >
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
> le_name+' Where
> >Date_ >='+'2001-01-01'+'
> > And CompanyNo Is Not NULL And Type <>'
> >exec sp_executesql @.sql
> >
> >- gives me the error "Incorrect syntax near '>'." The
> purpose of the last <>
> >is to find the records where this field is empty (that's
> my understanding of
> >it...). The basic structure of the query is from a DTS
> Transform task, but
> >I'm trying to "convert" this whole task to TSql.
> >
> >I've tried all sorts of different combinations of <>
> and ' but it still
> >won't do it. If I just prins the @.sql var. it looks fine.
> >The CompanyNo field is int(4) and the Type field is
> varchar(30).
> >Is there any other ways to check for an empty varchar
> field of can some of
> >you guide me to what it is I'm missing in my "set
> @.sql...." statement?
> >
> >Best Regards
> >Steen
> >
> >
> >.
> >|||I think you're having a comprehension problem between an empty/blank string
(which is a valid value) and a NULL (which is an unknown/missing value).
Anyway, your syntax is wrong. It ends at ' which is the closing of the
string, so of course the EXEC call will break.
DECLARE @.sql NVARCHAR(2000)
SET @.sql = N'SELECT <column_list> INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' WHERE
Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND COALESCE(RTRIM(Type),
'') != ''
You might try getting it working as a normal statement first, then putting
it into dynamic SQL. Remember that anytime you have a literal ' you must
escape it so it isn't interpreted as a string terminator.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> I' m having a problem with the syntax when I'm trying to run a dynamic SQL
> statement.
> The code -
> set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
Where
> Date_ >='+'2001-01-01'+'
> And CompanyNo Is Not NULL And Type <>'
> exec sp_executesql @.sql
> - gives me the error "Incorrect syntax near '>'." The purpose of the last
<>
> is to find the records where this field is empty (that's my understanding
of
> it...). The basic structure of the query is from a DTS Transform task, but
> I'm trying to "convert" this whole task to TSql.
> I've tried all sorts of different combinations of <> and ' but it still
> won't do it. If I just prins the @.sql var. it looks fine.
> The CompanyNo field is int(4) and the Type field is varchar(30).
> Is there any other ways to check for an empty varchar field of can some of
> you guide me to what it is I'm missing in my "set @.sql...." statement?
> Best Regards
> Steen
>|||Hi Araron
I think the ' was a remicense for my playing around with the statement, so
that's not the cause of the problem- sorry for the confusion.
I do know the difference between an empty field and NULL (or at least I hope
I know...:-)...) so I'm sorry if I've made some confusions about this. The
statement actually works when I'm running it without the variables and
without the dynamic SQL. It's not until I add the variable and dyn. SQL
part, I have problems getting it to accept the ' ' in the end. I'm not a all
experienced in using dynamic SQL so I thought that it was just something
simple I was missing in the syntax.
I'll take a closer look at your suggestion to see if that will do the trick.
Thanks
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I think you're having a comprehension problem between an empty/blank
string
> (which is a valid value) and a NULL (which is an unknown/missing value).
> Anyway, your syntax is wrong. It ends at ' which is the closing of the
> string, so of course the EXEC call will break.
> DECLARE @.sql NVARCHAR(2000)
> SET @.sql = N'SELECT <column_list> INTO
'+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' WHERE
> Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND COALESCE(RTRIM(Type),
> '') != ''
> You might try getting it working as a normal statement first, then putting
> it into dynamic SQL. Remember that anytime you have a literal ' you must
> escape it so it isn't interpreted as a string terminator.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > I' m having a problem with the syntax when I'm trying to run a dynamic
SQL
> > statement.
> >
> > The code -
> >
> > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> Where
> > Date_ >='+'2001-01-01'+'
> > And CompanyNo Is Not NULL And Type <>'
> > exec sp_executesql @.sql
> >
> > - gives me the error "Incorrect syntax near '>'." The purpose of the
last
> <>
> > is to find the records where this field is empty (that's my
understanding
> of
> > it...). The basic structure of the query is from a DTS Transform task,
but
> > I'm trying to "convert" this whole task to TSql.
> >
> > I've tried all sorts of different combinations of <> and ' but it still
> > won't do it. If I just prins the @.sql var. it looks fine.
> > The CompanyNo field is int(4) and the Type field is varchar(30).
> > Is there any other ways to check for an empty varchar field of can some
of
> > you guide me to what it is I'm missing in my "set @.sql...." statement?
> >
> > Best Regards
> > Steen
> >
> >
>|||Hi Aaron
While reading your post once more, I stumbled over your comment "..that
anytime you have a literal ' you must escape it...". What do you mean about
escape it? Do you just mean that I need a "start" and a "stop" '?
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I think you're having a comprehension problem between an empty/blank
string
> (which is a valid value) and a NULL (which is an unknown/missing value).
> Anyway, your syntax is wrong. It ends at ' which is the closing of the
> string, so of course the EXEC call will break.
> DECLARE @.sql NVARCHAR(2000)
> SET @.sql = N'SELECT <column_list> INTO
'+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' WHERE
> Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND COALESCE(RTRIM(Type),
> '') != ''
> You might try getting it working as a normal statement first, then putting
> it into dynamic SQL. Remember that anytime you have a literal ' you must
> escape it so it isn't interpreted as a string terminator.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > I' m having a problem with the syntax when I'm trying to run a dynamic
SQL
> > statement.
> >
> > The code -
> >
> > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> Where
> > Date_ >='+'2001-01-01'+'
> > And CompanyNo Is Not NULL And Type <>'
> > exec sp_executesql @.sql
> >
> > - gives me the error "Incorrect syntax near '>'." The purpose of the
last
> <>
> > is to find the records where this field is empty (that's my
understanding
> of
> > it...). The basic structure of the query is from a DTS Transform task,
but
> > I'm trying to "convert" this whole task to TSql.
> >
> > I've tried all sorts of different combinations of <> and ' but it still
> > won't do it. If I just prins the @.sql var. it looks fine.
> > The CompanyNo field is int(4) and the Type field is varchar(30).
> > Is there any other ways to check for an empty varchar field of can some
of
> > you guide me to what it is I'm missing in my "set @.sql...." statement?
> >
> > Best Regards
> > Steen
> >
> >
>|||Anytime a string contains ' you need to 'escape' it by doubling it. This
tells the engine that your ' is part of the string, and should not be
interpreted as an end-of-string marker.
Imagine your string looks like this:
Bob's Bait Shack
When you put it into a string,
SET @.string = 'Bob's Bait Shack'
Well, where does the string end? Between the b and the s, so the rest of
the string is lost. Except you will get an unclosed character string error
because other stuff follows the termination of the string.
Try these in Query Analyzer to see what I mean:
SELECT 'Bob's bait shack'
GO
SELECT 'Bob''s bait shack'
GO
SELECT 'Bob''s bait shack = ''''?'
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ucbpAImbEHA.252@.TK2MSFTNGP10.phx.gbl...
> Hi Aaron
> While reading your post once more, I stumbled over your comment "..that
> anytime you have a literal ' you must escape it...". What do you mean
about
> escape it? Do you just mean that I need a "start" and a "stop" '?
> Steen
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
> news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > I think you're having a comprehension problem between an empty/blank
> string
> > (which is a valid value) and a NULL (which is an unknown/missing value).
> >
> > Anyway, your syntax is wrong. It ends at ' which is the closing of the
> > string, so of course the EXEC call will break.
> >
> > DECLARE @.sql NVARCHAR(2000)
> > SET @.sql = N'SELECT <column_list> INTO
> '+@.db_name_dest+'.dbo.'+@.table_name+'
> > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
WHERE
> > Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND
COALESCE(RTRIM(Type),
> > '') != ''
> >
> > You might try getting it working as a normal statement first, then
putting
> > it into dynamic SQL. Remember that anytime you have a literal ' you
must
> > escape it so it isn't interpreted as a string terminator.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > > I' m having a problem with the syntax when I'm trying to run a dynamic
> SQL
> > > statement.
> > >
> > > The code -
> > >
> > > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> > Where
> > > Date_ >='+'2001-01-01'+'
> > > And CompanyNo Is Not NULL And Type <>'
> > > exec sp_executesql @.sql
> > >
> > > - gives me the error "Incorrect syntax near '>'." The purpose of the
> last
> > <>
> > > is to find the records where this field is empty (that's my
> understanding
> > of
> > > it...). The basic structure of the query is from a DTS Transform task,
> but
> > > I'm trying to "convert" this whole task to TSql.
> > >
> > > I've tried all sorts of different combinations of <> and ' but it
still
> > > won't do it. If I just prins the @.sql var. it looks fine.
> > > The CompanyNo field is int(4) and the Type field is varchar(30).
> > > Is there any other ways to check for an empty varchar field of can
some
> of
> > > you guide me to what it is I'm missing in my "set @.sql...."
statement?
> > >
> > > Best Regards
> > > Steen
> > >
> > >
> >
> >
>|||Sorry. It was the missing second single quote I was
referring to as missing.
Aaron is correct. You need to repeat the single quotes
since they are inside of a quoted expression.
>--Original Message--
>Hi
>Sorry if I wasn't very clear. I assume that the purpose
is to check for an
>empty field. The "original" code that's being used in
the DTS Transform task
>is "...AND CompanyNo is not NULL and Type <>'' ". It's
not me that have
>written the transform task, but I assume that this last
piece checks if
>there're any empty fields. It might be my understanding
of it that's wrong,
>but then I'd be happy to hear about it.
>This SQL statement runs fine in the DST task and also
when I run it in Query
>analyser using fixed values, but when I do it with
variables/dynamic SQL it
>seems to fail and not accept this last bit.
>Regards
>Steen
>"Matthew Bando" <anonymous@.discussions.microsoft.com>
skrev i en meddelelse
>news:071c01c46e4c$74405700$a501280a@.phx.gbl...
>> What exactly do you mean by an empty field? Generally
if
>> your field allows null and you don't set a value into
>> that field for a row, the field is set to NULL and you
>> can test for this by using "type IS NULL" in your where
>> clause.
>> "<>" is the operator for not equals and it is not a
>> singleton operator, it is a comparison operator. You
>> need something on both sides of the "<>" to compare to
>> each other.
>> For instance, if you are looking for rows where the
type
>> field is not equal to a space, you could try "type
<> ' '"
>> I hope that this helps.
>> Matthew Bando
>> bandoM@.CSCTechnologies-dot-com
>> >--Original Message--
>> >I' m having a problem with the syntax when I'm trying
to
>> run a dynamic SQL
>> >statement.
>> >
>> >The code -
>> >
>> >set @.sql = 'SELECT *
>> INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
>> >
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
>> le_name+' Where
>> >Date_ >='+'2001-01-01'+'
>> > And CompanyNo Is Not NULL And Type <>'
>> >exec sp_executesql @.sql
>> >
>> >- gives me the error "Incorrect syntax near '>'." The
>> purpose of the last <>
>> >is to find the records where this field is empty
(that's
>> my understanding of
>> >it...). The basic structure of the query is from a DTS
>> Transform task, but
>> >I'm trying to "convert" this whole task to TSql.
>> >
>> >I've tried all sorts of different combinations of <>
>> and ' but it still
>> >won't do it. If I just prins the @.sql var. it looks
fine.
>> >The CompanyNo field is int(4) and the Type field is
>> varchar(30).
>> >Is there any other ways to check for an empty varchar
>> field of can some of
>> >you guide me to what it is I'm missing in my "set
>> @.sql...." statement?
>> >
>> >Best Regards
>> >Steen
>> >
>> >
>> >.
>> >
>
>.
>|||Thanks Aaron - that was also the understanding I had about using ' . I just
wanted to make sure that there wasn't anything basic about it that I had
misunderstood.
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:eWVwRMmbEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Anytime a string contains ' you need to 'escape' it by doubling it. This
> tells the engine that your ' is part of the string, and should not be
> interpreted as an end-of-string marker.
> Imagine your string looks like this:
> Bob's Bait Shack
> When you put it into a string,
> SET @.string = 'Bob's Bait Shack'
> Well, where does the string end? Between the b and the s, so the rest of
> the string is lost. Except you will get an unclosed character string
error
> because other stuff follows the termination of the string.
> Try these in Query Analyzer to see what I mean:
> SELECT 'Bob's bait shack'
> GO
> SELECT 'Bob''s bait shack'
> GO
> SELECT 'Bob''s bait shack = ''''?'
> GO
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ucbpAImbEHA.252@.TK2MSFTNGP10.phx.gbl...
> > Hi Aaron
> >
> > While reading your post once more, I stumbled over your comment "..that
> > anytime you have a literal ' you must escape it...". What do you mean
> about
> > escape it? Do you just mean that I need a "start" and a "stop" '?
> >
> > Steen
> >
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
> > news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > > I think you're having a comprehension problem between an empty/blank
> > string
> > > (which is a valid value) and a NULL (which is an unknown/missing
value).
> > >
> > > Anyway, your syntax is wrong. It ends at ' which is the closing of
the
> > > string, so of course the EXEC call will break.
> > >
> > > DECLARE @.sql NVARCHAR(2000)
> > > SET @.sql = N'SELECT <column_list> INTO
> > '+@.db_name_dest+'.dbo.'+@.table_name+'
> > > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> WHERE
> > > Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND
> COALESCE(RTRIM(Type),
> > > '') != ''
> > >
> > > You might try getting it working as a normal statement first, then
> putting
> > > it into dynamic SQL. Remember that anytime you have a literal ' you
> must
> > > escape it so it isn't interpreted as a string terminator.
> > >
> > > --
> > > http://www.aspfaq.com/
> > > (Reverse address to reply.)
> > >
> > >
> > >
> > >
> > > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > > news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > > > I' m having a problem with the syntax when I'm trying to run a
dynamic
> > SQL
> > > > statement.
> > > >
> > > > The code -
> > > >
> > > > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > > > FROM
'+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> > > Where
> > > > Date_ >='+'2001-01-01'+'
> > > > And CompanyNo Is Not NULL And Type <>'
> > > > exec sp_executesql @.sql
> > > >
> > > > - gives me the error "Incorrect syntax near '>'." The purpose of the
> > last
> > > <>
> > > > is to find the records where this field is empty (that's my
> > understanding
> > > of
> > > > it...). The basic structure of the query is from a DTS Transform
task,
> > but
> > > > I'm trying to "convert" this whole task to TSql.
> > > >
> > > > I've tried all sorts of different combinations of <> and ' but it
> still
> > > > won't do it. If I just prins the @.sql var. it looks fine.
> > > > The CompanyNo field is int(4) and the Type field is varchar(30).
> > > > Is there any other ways to check for an empty varchar field of can
> some
> > of
> > > > you guide me to what it is I'm missing in my "set @.sql...."
> statement?
> > > >
> > > > Best Regards
> > > > Steen
> > > >
> > > >
> > >
> > >
> >
> >
>|||I've now tried to play around with both my own code and the suggestion from
Aaron - but it still wont work.
When I run the code as a regular SQL statemen with fixed values, it works
fine - both my own as well as Aaron's suggestion.
When I then try to use the variables and run it as dynamic SQL, it fails
with the syntax error around the '' in the end.
I've tried as good as I can to debug the code, and it looks like when
running it as dynamic SQL it doesn't like to do the "<> '' " comparison in
the end of the statement. If I e.g. add a value so it looks like "...And Art
<> 1 " then it works. My conclusion is that when running it as dynamic SQL,
then it doesnt like to have a '' as an "indicator" of an empty field in the
end (if you get my point...).
My question is of ocurse now if any of you can suggest another way of doing
the last bit with the check of the "art" field or if there's a way to "wrap"
the Dynamic SQL statement so it understand the last '' as wanted?
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I think you're having a comprehension problem between an empty/blank
string
> (which is a valid value) and a NULL (which is an unknown/missing value).
> Anyway, your syntax is wrong. It ends at ' which is the closing of the
> string, so of course the EXEC call will break.
> DECLARE @.sql NVARCHAR(2000)
> SET @.sql = N'SELECT <column_list> INTO
'+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' WHERE
> Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND COALESCE(RTRIM(Type),
> '') != ''
> You might try getting it working as a normal statement first, then putting
> it into dynamic SQL. Remember that anytime you have a literal ' you must
> escape it so it isn't interpreted as a string terminator.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > I' m having a problem with the syntax when I'm trying to run a dynamic
SQL
> > statement.
> >
> > The code -
> >
> > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> Where
> > Date_ >='+'2001-01-01'+'
> > And CompanyNo Is Not NULL And Type <>'
> > exec sp_executesql @.sql
> >
> > - gives me the error "Incorrect syntax near '>'." The purpose of the
last
> <>
> > is to find the records where this field is empty (that's my
understanding
> of
> > it...). The basic structure of the query is from a DTS Transform task,
but
> > I'm trying to "convert" this whole task to TSql.
> >
> > I've tried all sorts of different combinations of <> and ' but it still
> > won't do it. If I just prins the @.sql var. it looks fine.
> > The CompanyNo field is int(4) and the Type field is varchar(30).
> > Is there any other ways to check for an empty varchar field of can some
of
> > you guide me to what it is I'm missing in my "set @.sql...." statement?
> >
> > Best Regards
> > Steen
> >
> >
>|||> When I then try to use the variables and run it as dynamic SQL, it fails
> with the syntax error around the '' in the end.
SHOW US!
> it doesn't like to do the "<> '' " comparison
Stop making assumptions about T-SQL's preferences, likes and dislikes,
dating habits, etc. Show us your code and we'll show you how to fix it. We
can't fix what we can't see!
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hi
Here's the code - which I actually now have got working ...:-).
set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' Where
Date_ >='+'2001-01-01'+'
And CompanyNo Is Not NULL And Type <>''
exec sp_executesql @.sql
What I was missing, was the last two ' ( which I think was what Aaron was
indicating...). I was rest assured that I had tried that earlier without
getting it working, but I must be wrong.
Thanks for all your efforts in helping me....At least all this lead me to
the article written by Erland Sommerskog about Dynamic SQL plus what Aaron
has written about it on aspfaq.com. That helps understanding and knowing
Dynamic SQL a bit better.
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:uMoRcGybEHA.252@.TK2MSFTNGP10.phx.gbl...
> > When I then try to use the variables and run it as dynamic SQL, it fails
> > with the syntax error around the '' in the end.
> SHOW US!
> > it doesn't like to do the "<> '' " comparison
> Stop making assumptions about T-SQL's preferences, likes and dislikes,
> dating habits, etc. Show us your code and we'll show you how to fix it.
We
> can't fix what we can't see!
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>