Yep, you should always executeSQL over EXEC.
-----Original Message----- From: Jeff Chastain [mailto:[EMAIL PROTECTED] Sent: 05 May 2005 14:28 To: CF-Talk Subject: RE: SQL Drop Table using Variable? Cool. I am just looking for quick and dirty right now, so this syntax did it. You learn something new everyday. Thanks everyone. -----Original Message----- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: Thursday, May 05, 2005 8:17 AM To: CF-Talk Subject: RE: SQL Drop Table using Variable? You can't use variables in SQL server to represent objects (tables, view. etc) -- you can only use them to represent columns. If you need to use one to represent an object, then you have to either use exec() the sp_executeSQL stored procedure or an ad-hoc query in CFML using a CF variable for the table name. exec syntax looks like this: exec('drop table ' + @crDataTable) or exec sp_executeSQL N'drop table ' + @crDataTable' The advantage of sp_executeSQL is that there's a version under .dbo. in each catalog on your server which can be used to perform certain tasks which SQL Server limits to not being allowed to specify a catalog name (in other words,"create view catalog.dbo.vMyView ..." will fail, but "catalog.dbo.sp_executeSQL N'create view '..." works fine). hth > Yep, if I change 'DROP TABLE' to 'PRINT', I get '_crData4012'. > The error message just says 'Line 8: Incorrect syntax near > '@crDataTable'.' > -----Original Message----- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 05, 2005 7:41 AM > To: CF-Talk > Subject: RE: SQL Drop Table using Variable? > Have you outputted @crDataTable to see what the values is? > Also, what is the syntax error? > -----Original Message----- > From: Jeff Chastain [mailto:[EMAIL PROTECTED] > Sent: 05 May 2005 13:35 > To: CF-Talk > Subject: OT: SQL Drop Table using Variable? > Sorry for the off-topic, but I am having a bit of a query issue. I > have a dynamically created table that is named ' _crData123 ' > where the 123 is some > numeric identifier. Now, I need to be able to drop this > table. The > following query finds the table name and stores that name in a > variable, but when I attempt to drop it via the variable, I get a > syntax error. Any suggestions on what is wrong with this or another > way to do this in SQL Server? > ---------- > DECLARE @crDataTable char(30) > SELECT @crDataTable = name > FROM sysobjects > WHERE xtype='U' AND name LIKE '[_]crData%' > DROP TABLE @crDataTable > ---------- > Thanks > -- Jeff > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:205657 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
