Problem with EXEC command

I am try to populate a table based on a variable (it holds the table name).
I have managed to create the table using the EXEC command but am having
problems populating it with the following command. I receive "Error 156:
Incorrect syntax near the keyword 'CONVERT'". Could anyone offer a solution
.
Thanks.
BEGIN
EXEC ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
CONVERT(DATETIME, @.EndDate, 102) + ')')
END
However the following works fine into a regular table.
BEGIN
INSERT INTO SubsList (SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = @.PubCode) AND (DateEntered >= CONVERT(DATETIME,
@.StartDate, 102)) AND (DateEntered <= CONVERT(DATETIME, @.EndDate, 102))
ENDHi pete
try this:
BEGIN
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
CONVERT(DATETIME, @.EndDate, 102) + ')')
END
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:

> I am try to populate a table based on a variable (it holds the table name)
.
> I have managed to create the table using the EXEC command but am having
> problems populating it with the following command. I receive "Error 156:
> Incorrect syntax near the keyword 'CONVERT'". Could anyone offer a soluti
on.
> Thanks.
> BEGIN
> EXEC ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
> CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
> CONVERT(DATETIME, @.EndDate, 102) + ')')
> END
>
> However the following works fine into a regular table.
> BEGIN
> INSERT INTO SubsList (SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = @.PubCode) AND (DateEntered >= CONVERT(DATETIME,
> @.StartDate, 102)) AND (DateEntered <= CONVERT(DATETIME, @.EndDate, 102))
> END|||Thanks Chandra but still no joy.
Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
"Chandra" wrote:
> Hi pete
> try this:
> BEGIN
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
> CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
> CONVERT(DATETIME, @.EndDate, 102) + ')')
> END
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||Hi pete
try now:
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
CONVERT(DATETIME,'+ @.EndDate +', 102)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Thanks Chandra but still no joy.
> Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
>
> "Chandra" wrote:
>|||Hi pete
just observed and found that, "(" was missing before "DateEntered"
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Thanks Chandra but still no joy.
> Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
>
> "Chandra" wrote:
>|||'Fraid not. Same error.
"Chandra" wrote:
> Hi pete
> try now:
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
> CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
> CONVERT(DATETIME,'+ @.EndDate +', 102)
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||my mistake:
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
CONVERT(DATETIME,'+ @.EndDate +', 102)'
i missed a ' at the end.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> 'Fraid not. Same error.
> "Chandra" wrote:
>|||Nope :-(
"Chandra" wrote:
> Noe then,
> this should work
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
> CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
> CONVERT(DATETIME,'+ @.EndDate +', 102)')
> too many quotes and brackets, these are confusing..
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||Why don't you try printing your query instead of executing it?
Then you'll see the error yourself. With what you're doing
now, you make a change, ask the parse "is this right yet?", and
listen to the parser's particular complaint, over and over.
If you've never looked at the query yourself, are you going to
assume it's correct once the parser stops complaining about it?
Once you get it right, then you can execute it instead of print
it.
Steve Kass
Drew University
Pete wrote:
>Getting closer..
>The following gives "Syntax error converting datetime from character string
"
>SET @.SqlString = N'INSERT INTO ' + @.NewSubsList + ' (SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND (DateEntered >= ' +
>CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
>CONVERT(DATETIME, @.EndDate, 102) + ')'
>EXECUTE sp_executesql @.SqlString
>
>"Pete" wrote:
>
>|||hi
or do one thing,
first create the string that you want to execute.
just see if it clear in the syntax. and then pass it to sp_executesql
does this make sence.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Nope :-(
> "Chandra" wrote:
>