if you can't use the var to refer directly to an object... could you
backdoor it and just use the var in a subselect? I know it looks a
little redundant: (select name from names where name = 'ken'), but it
seems like you may be able to fake the funk.
(I forget what the 'obj' table is in SQL Server, but...)
Kind of like: drop table (select object_name from obj where object_name
= @crDataTable)
That way it's representing an object indirectly??? I have no idea if
this will work, it's just the first thing that popped to mind after
reading Isaac's post.
I've not worked with SQL Server much in the last couple of years, but
couldn't you do something like that? Also, are you doing this from a web
app? If so, why not just assign the table name to a cf variable and do
the drop table in a cfquery? 2 queries; get the table name in the first;
set it to a cf var and then drop the table in the second.
--Ferg
S. Isaac Dealey wrote:
>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:205654
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
