I have a dts package with an execute sql task containing the code below.
DTS refuses to let me use ? following the FROM. Tried placing the code w/in
a variable and executing. Still no go. Suggestions anyone?
Thanks much.

SET @Tri_Select = 'SELECT firstname, middleinitial, lastname,
address1,address2, city, state, zipcode, INTO ' + @TableName + ' FROM ' + ?
+ ' WHERE LTRIM(ISNULL(zip4,"")) <> "" AND lastname NOT LIKE "%,%" AND
address1 <> "" AND filecode <> "6" AND donotmailcode = "N"'

EXEC(@Tri_Select)

I tried your code and this works.

DECLARE @Tri_Select varchar(255)
,@TableName varchar(50)

SELECT @TableName = 'TestTable'
SET @Tri_Select = 'SELECT COL1, COL2, COL3, COL4 INTO ' +
@TableName + ' FROM TableName'

EXEC(@Tri_Select)

You have an extra comma zipcode, and it is giving an error
on the ISNULL(zip4,"")) - doen't like the double quotes.

Hope this helps

Thanks for the assistance Mark, but I'm still generating errors.
Error I get when I try your method is:
"Cannot use empty object or column names. Use a single space if necessary."

You would think MS would make it easy to pass a tablename in as a
variable...

"Mark" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Hey all,

First, if anyone would care to help me out in a post a few lines down
(subject is "can't use ? after FROM") I'd greatly appreciate it.

But my main question is how does one dynamically set tablenames as
parameters after certain commands (alter, from, drop, etc...)? Is it even
possible? I know I can't be the first to hit this barrier...

For instance, a line in my execute sql task reads "DROP TABLE @TableVar"

Naturally, it doesn't work. Suggestions anyone? Thanks.

You would need to do this dynamically and the best article I can point you
to is this one

http://www.sommarskog.se/dynamic_sql.html

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"J McDermond" <nospam (AT) hotmail (DOT) com> wrote

Thanks for the link Al. I solved my issues...sort of...

My sql runs fine in query analyzer, both parses and executes with no
problems. However, when placed in my execute sql task, it shoots up this
enigmatic error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Could not find prepared statement with handle 1.

Anyone else ever encounter this error?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Hi J McDermond,

This error seems strange, Have you upgraed to SQL Server SP3a? How about trying a restart
of machine? It is highly recommand if you could make all latest service pack done on you box,
including for Windows, for SQL Server and for MDAC, and then make a restart to see whether
this issue still exists. If so, would you please so kind as to paste so detailed of your codes in
task so that it might be possible for me to reproduce it on my box.

Thank you for your patience and cooperation. If you have any questions or concerns, don't
hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!