Create Table with variable name

This should be simple, but...

I want to create a table in a stored proc using a variable name instead of something hard coded. I was hoping to do something like....

CREATE PROCEDURE foo

-- Add the parameters for the stored procedure here

@.TableName char = null

AS

BEGIN

SET NOCOUNT ON;

CREATE TABLE @.TableName (

[HRMONTH] [int] NULL,

[HRYEAR] [int] NULL

) ON [PRIMARY]

But no combination of names '@.'s, etc, allows me to use a variable name that I passed into the procedure. What am I missing? I will either receive a syntax error or the procedure will create a table called TableName rather than whatever TableName really stands for...

Thanks,

Tom

DECLARE @.ExecSQL NVARCHAR(300
SET @.ExecSQL = "CREATE TABLE @.TableName ..."
EXECUTE @.ExecSQL @.TableName

Remember that all variables have to be NVARCHAR and not VARCHAR. Also the exact syntax might be a bit off. In hat case use this as a reference. Hope this helps.|||

>>Remember that all variables have to be NVARCHAR and not VARCHAR

This is only true for sp_executesql, exec dynamic sql works with varchar also take a look at this example

declare @.table varchar(49),@.sql varchar(500)

select @.table ='Orders2006'
select @.sql = 'create table ' + @.table + '(id int)'
exec (@.sql)

exec('insert ' + @.table + ' values(1)')

exec('select * from ' + @.table)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Ahh. One thing to note about the above method is that I believe it may allows for more potentials for sql injections - may not be an issue with this but with queries and etc I believe it shoudl be avoided as opposed to the other method due to these security restrictions related to sql injection/execution.|||

There is always this

The Curse and Blessings of Dynamic SQL

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

It deals with the whole thing, injections, permissions etc etc

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks for the responses. This worked well, until I read the article in the previous post. So, maybe this wasn't such a hot idea...

Thanks again,

Tom

|||

One way is to take below approach which doesn't require dynamic SQL:

create table _tmp (

...

)

exec sp_rename _tmp, @.name_passed_to_proc

Create table problem

I have been trying to create a table since morning but it save as a system
table instead of a user table. anybody has any idea? I tried to create both
GUI(Enterprise) and Analyser prompt but all the the time system made a
system table instead of a user
thanksFarrukh wrote:
> I have been trying to create a table since morning but it save as a system
> table instead of a user table. anybody has any idea? I tried to create bot
h
> GUI(Enterprise) and Analyser prompt but all the the time system made a
> system table instead of a user
> thanks
It looks like somone has executed the sp_MS_upd_sysobj_category
procedure with a parameter value of 1. Execute it with the value 2
as parameter and verify that objects now created will not be
system objects.
I strongly recommend that you search the net and read up on this
procedure before executing it. It is an undocumented proc and should be
avoided.

Create table problem

I have been trying to create a table since morning but it save as a system
table instead of a user table. anybody has any idea? I tried to create both
GUI(Enterprise) and Analyser prompt but all the the time system made a
system table instead of a user
thanksFarrukh wrote:
> I have been trying to create a table since morning but it save as a system
> table instead of a user table. anybody has any idea? I tried to create both
> GUI(Enterprise) and Analyser prompt but all the the time system made a
> system table instead of a user
> thanks
It looks like somone has executed the sp_MS_upd_sysobj_category
procedure with a parameter value of 1. Execute it with the value 2
as parameter and verify that objects now created will not be
system objects.
I strongly recommend that you search the net and read up on this
procedure before executing it. It is an undocumented proc and should be
avoided.

create table => system table

Hallo everybody,
when I create a table or a stored procedure it always becomes a system
object instead of a user object, who can I avoid this? I just want to create
user objects.
I'm using SQL Server 2000 and I have all the service packs installed, the
user I'm using to create the table is DBOwner of the database and is part of
the role "system administrators"
thanks for the help
CristianSomebody has been playing with the sp_MS_upd_sysobj_category procedure. Exec
ute it with the value 2
as parameter and verify that objects create from thereon will not be system
objects.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Cristian" <cristiansuazo@.hotmail.com> wrote in message news:O0iCVmKWGHA.752@.TK2MSFTNGP02.p
hx.gbl...
> Hallo everybody,
> when I create a table or a stored procedure it always becomes a system
> object instead of a user object, who can I avoid this? I just want to crea
te
> user objects.
> I'm using SQL Server 2000 and I have all the service packs installed, the
> user I'm using to create the table is DBOwner of the database and is part
of
> the role "system administrators"
> thanks for the help
> Cristian
>|||what do you mean by system object. How did you find that it was a system
object?
"Cristian" wrote:

> Hallo everybody,
> when I create a table or a stored procedure it always becomes a system
> object instead of a user object, who can I avoid this? I just want to crea
te
> user objects.
> I'm using SQL Server 2000 and I have all the service packs installed, the
> user I'm using to create the table is DBOwner of the database and is part
of
> the role "system administrators"
> thanks for the help
> Cristian
>
>|||What makes you think they're system objects?
*mike hodgson*
http://sqlnerd.blogspot.com
Cristian wrote:

>Hallo everybody,
>when I create a table or a stored procedure it always becomes a system
>object instead of a user object, who can I avoid this? I just want to creat
e
>user objects.
>I'm using SQL Server 2000 and I have all the service packs installed, the
>user I'm using to create the table is DBOwner of the database and is part o
f
>the role "system administrators"
>thanks for the help
>Cristian
>
>|||thanks man! that resolved everything... strange stored procedure, an
undocumented one, anyway thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e8cZjzKWGHA.3972@.TK2MSFTNGP02.phx.gbl...
> Somebody has been playing with the sp_MS_upd_sysobj_category procedure.
Execute it with the value 2
> as parameter and verify that objects create from thereon will not be
system objects.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Cristian" <cristiansuazo@.hotmail.com> wrote in message
news:O0iCVmKWGHA.752@.TK2MSFTNGP02.phx.gbl...
create
the
part of
>|||Perhaps trace flag 1717 is on. You can interrogate with DBCC TRACESTATUS
(1717) and turn off with DBCC TRACEOFF (1717, -1) . Remove it from startup
parameters, if present.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cristian" <cristiansuazo@.hotmail.com> wrote in message
news:O0iCVmKWGHA.752@.TK2MSFTNGP02.phx.gbl...
> Hallo everybody,
> when I create a table or a stored procedure it always becomes a system
> object instead of a user object, who can I avoid this? I just want to
> create
> user objects.
> I'm using SQL Server 2000 and I have all the service packs installed, the
> user I'm using to create the table is DBOwner of the database and is part
> of
> the role "system administrators"
> thanks for the help
> Cristian
>

Create Table

Can i create a table using the structure of an existing table, instead of defining the columns one by one?

e.g.
I already have table1(col1 int,col2 char(3))

I want to create another table with the same structure as table 1 without doing the following:
create table table2(col1 int,col2 char(3))

Is there a command of doing create table2 as table1 let say?Depending on the size of the table you could do the following:

select * into newtable from oldtable|||if you don't want to include data in newtable, you should modify the query as below:

select * into newtable from oldtable where 0=1|||Just what I was going to add:

If you want table structure and data then

select * into newtable from oldtable

else

select * into newtable from oldtable where (statement is false)

Create PerfMon Log and store into SQL DB

Hi.
I want to create a perfmon Logfile and store the Log Data into SQL Database
as offered option instead of CSV or Binary File. I want to use those Data
with Reporting services lateron. But I can't realize it. I have looked for
any articles - without success.
I have proceed as follows:
1. Created a database on test Server to retrieve the log data
2. Created an SQL USER to use this DB
3. Created an ODBC Connection to this SQL Database
4. Created the Log File and defined the ODBC Connection as target for data
After starting I am getting an error message saying sth like: "The Protocol
[name] or warnings have not been started. Refresh the Logfile list to view
the error message. Some protocols or warnings may finish after a few minutes,
especially when using performance indicators."
MarkusMarkus,
The way i did is logged into csv file on all the servers and used DTS to
export data in to the central server.From there we developed custom
procedures to do reports on the data.
Thanks
"MarkusPoehler" wrote:
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few minutes,
> especially when using performance indicators."
> Markus|||It is never a good idea to log directly to a table from perfmon or trace.
Log to a file and use relog.exe or DST to import it into a table.
--
Andrew J. Kelly SQL MVP
"MarkusPoehler" <poehler@.NOSPAMnetpoint-edv.de> wrote in message
news:8840F9F3-9F5F-4C76-8E6A-929600587FF0@.microsoft.com...
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL
> Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The
> Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few
> minutes,
> especially when using performance indicators."
> Markus|||Nice Workaround :)
I have found out: You have to define the DSN Connection for MASTER Database
as default without replacing any settings in the DSN Connection Wizard - then
it works. You can find the data inside master in tabesl named
displaytoid, counterdata, counterdetails
--
Markus Pöhler
netpoint-edv gmbh
Germany
"chinn" wrote:
> Markus,
> The way i did is logged into csv file on all the servers and used DTS to
> export data in to the central server.From there we developed custom
> procedures to do reports on the data.
> Thanks
> "MarkusPoehler" wrote:
> > Hi.
> >
> > I want to create a perfmon Logfile and store the Log Data into SQL Database
> > as offered option instead of CSV or Binary File. I want to use those Data
> > with Reporting services lateron. But I can't realize it. I have looked for
> > any articles - without success.
> > I have proceed as follows:
> >
> > 1. Created a database on test Server to retrieve the log data
> > 2. Created an SQL USER to use this DB
> > 3. Created an ODBC Connection to this SQL Database
> > 4. Created the Log File and defined the ODBC Connection as target for data
> >
> > After starting I am getting an error message saying sth like: "The Protocol
> > [name] or warnings have not been started. Refresh the Logfile list to view
> > the error message. Some protocols or warnings may finish after a few minutes,
> > especially when using performance indicators."
> >
> > Markus

Create PerfMon Log and store into SQL DB

Hi.
I want to create a perfmon Logfile and store the Log Data into SQL Database
as offered option instead of CSV or Binary File. I want to use those Data
with Reporting services lateron. But I can't realize it. I have looked for
any articles - without success.
I have proceed as follows:
1. Created a database on test Server to retrieve the log data
2. Created an SQL USER to use this DB
3. Created an ODBC Connection to this SQL Database
4. Created the Log File and defined the ODBC Connection as target for data
After starting I am getting an error message saying sth like: "The Protocol
[name] or warnings have not been started. Refresh the Logfile list to view
the error message. Some protocols or warnings may finish after a few minutes,
especially when using performance indicators."
Markus
Markus,
The way i did is logged into csv file on all the servers and used DTS to
export data in to the central server.From there we developed custom
procedures to do reports on the data.
Thanks
"MarkusPoehler" wrote:

> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few minutes,
> especially when using performance indicators."
> Markus
|||It is never a good idea to log directly to a table from perfmon or trace.
Log to a file and use relog.exe or DST to import it into a table.
Andrew J. Kelly SQL MVP
"MarkusPoehler" <poehler@.NOSPAMnetpoint-edv.de> wrote in message
news:8840F9F3-9F5F-4C76-8E6A-929600587FF0@.microsoft.com...
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL
> Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The
> Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few
> minutes,
> especially when using performance indicators."
> Markus
|||Nice Workaround
I have found out: You have to define the DSN Connection for MASTER Database
as default without replacing any settings in the DSN Connection Wizard - then
it works. You can find the data inside master in tabesl named
displaytoid, counterdata, counterdetails
Markus P?hler
netpoint-edv gmbh
Germany
"chinn" wrote:
[vbcol=seagreen]
> Markus,
> The way i did is logged into csv file on all the servers and used DTS to
> export data in to the central server.From there we developed custom
> procedures to do reports on the data.
> Thanks
> "MarkusPoehler" wrote:

Create PerfMon Log and store into SQL DB

Hi.
I want to create a perfmon Logfile and store the Log Data into SQL Database
as offered option instead of CSV or Binary File. I want to use those Data
with Reporting services lateron. But I can't realize it. I have looked for
any articles - without success.
I have proceed as follows:
1. Created a database on test Server to retrieve the log data
2. Created an SQL USER to use this DB
3. Created an ODBC Connection to this SQL Database
4. Created the Log File and defined the ODBC Connection as target for data
After starting I am getting an error message saying sth like: "The Protocol
[name] or warnings have not been started. Refresh the Logfile list to vi
ew
the error message. Some protocols or warnings may finish after a few minutes
,
especially when using performance indicators."
MarkusMarkus,
The way i did is logged into csv file on all the servers and used DTS to
export data in to the central server.From there we developed custom
procedures to do reports on the data.
Thanks
"MarkusPoehler" wrote:

> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL Databas
e
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The Protoco
l
> [name] or warnings have not been started. Refresh the Logfile list to
view
> the error message. Some protocols or warnings may finish after a few minut
es,
> especially when using performance indicators."
> Markus|||It is never a good idea to log directly to a table from perfmon or trace.
Log to a file and use relog.exe or DST to import it into a table.
Andrew J. Kelly SQL MVP
"MarkusPoehler" <poehler@.NOSPAMnetpoint-edv.de> wrote in message
news:8840F9F3-9F5F-4C76-8E6A-929600587FF0@.microsoft.com...
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL
> Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The
> Protocol
> [name] or warnings have not been started. Refresh the Logfile list to
view
> the error message. Some protocols or warnings may finish after a few
> minutes,
> especially when using performance indicators."
> Markus|||Nice Workaround
I have found out: You have to define the DSN Connection for MASTER Database
as default without replacing any settings in the DSN Connection Wizard - the
n
it works. You can find the data inside master in tabesl named
displaytoid, counterdata, counterdetails
Markus P?hler
netpoint-edv gmbh
Germany
"chinn" wrote:
[vbcol=seagreen]
> Markus,
> The way i did is logged into csv file on all the servers and used DTS to
> export data in to the central server.From there we developed custom
> procedures to do reports on the data.
> Thanks
> "MarkusPoehler" wrote:
>

Create new schema using management studio

instead of CREATE SCHEMA using T-SQL

Open the database node > Your Database > Security > Schemas (be sure that your database is a SQL Server 2005 database)

Jens K. Suessmeyer

http://www.sqlserver2005.de

create new db

Greetings,
Using a server base database called Respond (fe) and Sql server 2000 as a (be), I was trying to create a new db. Instead of designing from scratch, I was thinking to copy an existing Respond db, delete existing data, and modify it to fit my new requirements. I'm new to sql server db and would like to know
1. How to delete data from the table (be).
2. How to perform an automatic backup daily (or may be every other day) and how to check if the backup is doing what it was suppose to do in sql server 2000.

Thank you in advance,

OCMOCM, this is really basic stuff. Delete data by either issueing a DELETE sql command or a TRUNCATE TABLE sql command. Backups can easily be automated through the Enterprise Manager console.
You need to avail yourself of the wealth of information in Microsoft SQL Server Books Online.|||OCM, this is really basic stuff. Delete data by either issueing a DELETE sql command or a TRUNCATE TABLE sql command. Backups can easily be automated through the Enterprise Manager console.
You need to avail yourself of the wealth of information in Microsoft SQL Server Books Online.|||Backups can easily be automated through the Enterprise Manager console. :)

You just had to tell him that didn't you. :) You can also schedule a job to do them. Check out this weblog: http://weblogs.sqlteam.com/tarad