Create Table

I have created a table already but I want to create another table inside it. Is it even possible?You can't create tables within tables. You should create subtables with foreign key references to the primary key of the parent table instead.|||As blindman said, with the SQL Standards currently in use, you cannot create a table within another table. I've been told that the new SQL:2006 standard (which no database engine yet implements afaik) describes a new feature which accomplishes this task. Unfortunately, I've had to little time to study the SQL:2006 standard so I don't have any details.|||Do you get copies of the standard roac?|||Currently Google is my friend. But I will have my own copies, yes. They can be bought from http://webstore.ansi.org . As many of the upoming standards are drafts, they have a bit nicer price, $30.

CREATE SCHEMA fails Inside an If Block

Hello All,

The below "CREATE SCHEMA" sql statement fails if it is inside an IF block. It runs fine if i run it without the IF block...

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Customer')

BEGIN

CREATE SCHEMA Customer AUTHORIZATION [sys]

END

-

Did anyone encountered this issue before....

Thanks..

Make this as dynamic SQL.|||

Thanks, Bushan.

As the DDL scripts can grow bigger, I feel that it is hard to maintain dynamic sql. But, I was just trying to figure out why this is not possible in this "create schema" scenario alone. It even works for "drop schema".

|||

Like the error message you get back says, CREATE SCHEMA must be the first command in the batch. So to do a CREATE SCHEMA in a script like this, that one statement has to be done dynamically.

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Customer')
BEGIN
exec ('CREATE SCHEMA Customer AUTHORIZATION [sys]')
END
Annoying? Yes? But it is not that much more work than doing it in the way that you (and I) originally expected :)

|||This is such an OBVIOUS shortcoming of TransactSQL. Why hasn't Microsoft corrected this flaw. As far as I know (with the exception of entering the exec ('sneak the CREATE in as a text string') there is no way to conditionally create a SCHEMA or a FUNCTION for that matter.

If it's disallowed for security reasons then why can we sneak it in with an EXEC?

The problem this causes for me (over and over again) is I write a sample function for the user and include it in my upgrade script. If they've already run the script for an earlier version, they already have my example and may have customized it to their specific application. If they have, I don't really want to replace it with my example again. So I'm stuck sneaking it in through the string route. This has been a flaw in T-SQL for a long time....

Sorry for the rant... Sure wish the T-SQL gods were listening |||
Thanks for the clarification..

Create login problem in SQL Server 2005

Hey guys,

I'm having a problem making a new login inside the sql management studio, the problem is, when i create a new login, i selected SQL Authentication, then type a password, then uncheck Enforce password policy.

i then select the database i want the login to be associated with, but once i click ok i get this exception:
Create failed for Login ''. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
"An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. (Microsoft SQL Server, Error: 1038)

I even tried with Northwind and a brand new database with a table and 2 columns but it's the same story every time.
Any ideas?

Thanks a bunchMake sure you have entered "Login Name" in the text box provided at the top of the window

thanks
Anoop

CREATE FULLTEXT CATALOG inside a user transaction.

hi:

I try to create full text for new created tables.

Since all new created tables will have same columns with different table name.

After I run the stored procedure to create table, after i got the new table name, I would like to create full-text on that table in the DDL triger.

But I got error like this:

CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

Any one has idea how to deal with it?

Thanks

This is by design. Full-text catalog cannot be created in nested/embedded transactions.

I dont' think the DDL trigger would work in this case. Perhap, create a store proc that will scan all the tables that do not have full-text index and add them on fly.

Gary

|||

DDL triger not working. I have stored procedure for creating full-text, the SP works when you exec it inside of the Management studio (not being called from other SP, job or triger etc), otherwise, it will return the same error.

anyway, I guess this is the dead end for creating full text on the fly.

CREATE DATABASE from Template?

Hello,
I need to be able toCREATE DATABASE by copying an existing database.
I would be doing this inside of a web app during an event.
How do I set this up on SQL 2005 ?

Thanks!

Here's an article on how to do it in PostgreSQL http://www.enterprisedb.com/documentation/manage-ag-templatedbs.htmlSQL Server 2005 has 'Copy Database Wizard' in Management Studio; you can also copy databases with Backup and Restore. But both methods seems not so easier to be done inside of web app during an event. Anyways you can take a look at 'Copying Databases to Other Servers' topic in SQL2005 Books Online.|||What if i made a backup of my "template" db
then had a stored proc like:

create procedure restoredb
@.dbname sysname
as
restore database @.dbname from disk='c:\backup.bak'
with move 'file_data' to 'd:\mssql\mssql\data\' + @.dbname + '_data.mdf',
move 'file_log' to 'd:\mssql\mssql\data\' + @.dbname + '_log.ldf',
replace

that created the new db