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
SteenI 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...
SQL[vbcol=seagreen]
> Where
last[vbcol=seagreen]
> <>
understanding[vbcol=seagreen]
> of
but[vbcol=seagreen]
of[vbcol=seagreen]
>|||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...
SQL[vbcol=seagreen]
> Where
last[vbcol=seagreen]
> <>
understanding[vbcol=seagreen]
> of
but[vbcol=seagreen]
of[vbcol=seagreen]
>|||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 meddelels
e
> news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> string
> '+@.db_name_dest+'.dbo.'+@.table_name+'
WHERE[vbcol=seagreen]
COALESCE(RTRIM(Type),[vbcol=seagreen]
putting[vbcol=seagreen]
must[vbcol=seagreen]
> SQL
> last
> understanding
> but
still[vbcol=seagreen]
some[vbcol=seagreen]
> of
statement?[vbcol=seagreen]
>|||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...
> about
value).[vbcol=seagreen]
the[vbcol=seagreen]
> WHERE
> COALESCE(RTRIM(Type),
> putting
> must
dynamic[vbcol=seagreen]
'+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'[vbcol=seagreen]
task,[vbcol=seagreen]
> still
> some
> statement?
>|||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...
SQL[vbcol=seagreen]
> Where
last[vbcol=seagreen]
> <>
understanding[vbcol=seagreen]
> of
but[vbcol=seagreen]
of[vbcol=seagreen]
>|||> 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...
> SHOW US!
>
> 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.)
>