sql server dts: can't use ? after FROM -- sql server dts

can't use ? after FROM

Mark
7/20/2004 1:34:54 PM

sql server dts: 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

[quoted text, click to view]

can't use ? after FROM

J McDermond
7/20/2004 3:15:20 PM

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)

Re: can't use ? after FROM

J McDermond
7/21/2004 10:55:11 AM

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...

[quoted text, click to view]

Parameters after "FROM" & "DROP"

J McDermond
7/21/2004 2:38:11 PM

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.

Re: Parameters after "FROM" & "DROP"

J McDermond
7/21/2004 4:19:38 PM

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?

[quoted text, click to view]

Re: Parameters after "FROM" & "DROP"

Allan Mitchell
7/21/2004 7:55:39 PM

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

[quoted text, click to view]

Re: Parameters after "FROM" & "DROP"

v-mingqc NO[at]SPAM online.microsoft.com (
7/22/2004 2:58:22 AM

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!

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.