Creating Tables Dynamically

Hello!
I am looking for a simple routine to create and populate tables dynamically.
I get an error message that says @.tn has a problem. What could be the reason
:
declare @.table_name varchar(500)
declare @.query nvarchar(500)
declare @.parmdefinition nvarchar(500)
set @.query = 'create table @.tn (user_id nvarchar,event_id nvarchar)'
set @.table_name = 'test'
set @.parmdefinition = '@.tn varchar(500),@.query varchar(500)'
execute sp_executesql @.query, @.parmdefinition, @.tn = @.table_name
Any clues are very welcome. Thank you very much!
-BahmanYou need to declare @.tn.
Archer
"Bahman" wrote:

> Hello!
> I am looking for a simple routine to create and populate tables dynamicall
y.
> I get an error message that says @.tn has a problem. What could be the reas
on:
> declare @.table_name varchar(500)
> declare @.query nvarchar(500)
> declare @.parmdefinition nvarchar(500)
> set @.query = 'create table @.tn (user_id nvarchar,event_id nvarchar)'
> set @.table_name = 'test'
> set @.parmdefinition = '@.tn varchar(500),@.query varchar(500)'
> execute sp_executesql @.query, @.parmdefinition, @.tn = @.table_name
> Any clues are very welcome. Thank you very much!
> -Bahman
>|||@.tn is redundant. You can't parameterize a table name, even within
sp_executesql. Try:
declare @.table_name varchar(500)
declare @.query nvarchar(500)
declare @.parmdefinition nvarchar(500)
set @.table_name = 'test'
set @.query = 'create table '+@.table_name+' (user_id nvarchar,event_id
nvarchar)'
execute sp_executesql @.query
sp_executesql is redundant as well. You could have used EXEC.
The obvious question is why would you want to create tables
dynamically? Especially since the metadata is static in this example.
David Portas
SQL Server MVP
--|||Bagman3rd:
No, it still won't work. Does sp_executesql look for parameters only in the
where clause?
"bagman3rd" wrote:
> You need to declare @.tn.
> Archer
> "Bahman" wrote:
>|||>> I am looking for a simple routine to create and populate tables
Can you elaborate on why you want to do this? In most cases, such
requirements stem from poorly designed systems or people trying to come up
with short-cuts to avoid coding.
The solution for creating a table dynamically is to build a string the exact
CREATE TABLE script and execute it with EXEC or sp_ExecuteSQL. With
sp_ExecuteSQL you can only substitute variables not literal segments of a
DDL statement, which you seem to be doing here. So one way to write this
would be:
SET @.table_name = 'test'
SET @.parmdefinition = '( tn varchar(500),query varchar(500) )'
SET @.query = 'create table ' + @.table_name + @.parmdefinition
EXEC sp_executesql @.query
Before starting out, consider reading some implications at:
http://www.sommarskog.se/dynamic_sql.html
Anith|||Dave,
You are correct.
What I am looking for is the exact image of one particular table every hour
or so.
I am not looking for transaction logs, or sql statements, or monitor
traffic. All I want to know is what exactly is the content of the table at a
given time.
In MSSql you can backup the database or the trx log. But you cnnot create
images of individual tables that you can revert to on a moment's notice.
In this particular table, users can both add and delete records. Obviously,
they can only delete their own records. But to be safe, I need an image of
the content of the table itself, since add/delete permissions are not
'layered', e.g., once you have the right to 'delete', that right in not
strictly associated with what you entered yourself. That right is also
associated with what everyone else entered. Same with update.
There are other issues here as well. But the above are the basic stuff.
Thank you very much for your help.
-Bahman
"David Portas" wrote:

> @.tn is redundant. You can't parameterize a table name, even within
> sp_executesql. Try:
> declare @.table_name varchar(500)
> declare @.query nvarchar(500)
> declare @.parmdefinition nvarchar(500)
> set @.table_name = 'test'
> set @.query = 'create table '+@.table_name+' (user_id nvarchar,event_id
> nvarchar)'
> execute sp_executesql @.query
> sp_executesql is redundant as well. You could have used EXEC.
> The obvious question is why would you want to create tables
> dynamically? Especially since the metadata is static in this example.
> --
> David Portas
> SQL Server MVP
> --
>|||That's certainly an original one! Seriously, the problem is a common
one and there are various standard solutions. You don't need to copy
tables around dynamically. In fact it's hard to imagine how such a
thing could be practical let alone scalable.
Take a look at the following article on row-level security. More simply
you could just implement "logical" deletes - i.e. use a proc that marks
rows as deleted rather than actually deleting them.
http://vyaskn.tripod.com/ row_level...as
es.htm
David Portas
SQL Server MVP
--|||David,
That solution is pretty much what I am already doing. It is not robust thoug
h.
As you said, when you 'scale up' the database, you cannot create a separate
account for everyone who logs in to your website. So, the same account is
adding and deleting stuff from the same table all over the place. The
'session' variable that you have in the web application is not linked to the
'session' that you have on the Sequel. The two exist in two separate domains
.
Hence, the sequel does not know 'who' is 'on' right now judging from the
calling application, which is dont net.
That is just what our code is for. But some of it should be off-loaded to
the sequel side. The calling application should not be in charge of
everything.
In Windows 2003 you have a feature called 'shadow copy'. That is not the
same as tape backup. To me, it is a form of versioning. Good enough. In
sequel, you have the trx that you can apply after you recreated the database
from the backup. But there is nothing in the trx or the backup that tells yo
u
what the difference is between the state of an individual table that you see
there and the one you backed up earlier (unless you like lots of
programming). There is a 'disconnect' here.
MS Sql is basically treated as a data dump. There are cases where it has
very limited functionality. A simple trigger that I put in increases respons
e
time by a second. The server is not designed to be 'active'. And there is no
fine granule for treating data and data relationships. Hence the need to cod
e
a lot.
Thank you!
"David Portas" wrote:

> That's certainly an original one! Seriously, the problem is a common
> one and there are various standard solutions. You don't need to copy
> tables around dynamically. In fact it's hard to imagine how such a
> thing could be practical let alone scalable.
> Take a look at the following article on row-level security. More simply
> you could just implement "logical" deletes - i.e. use a proc that marks
> rows as deleted rather than actually deleting them.
> http://vyaskn.tripod.com/ row_level...as
es.htm
> --
> David Portas
> SQL Server MVP
> --
>|||>> What I am looking for is the exact image of one particular table every ho
ur or so. <<
I know of one ACCESS system that crashes so often, they do this. It
says that you need a better solution. You will have to do this in
hardware to get the exact image on a second identical disk drive.
Weird request -- most of us are happy with snapshots or generatianl
concurrency control.