create table test

hi,
What is the syntax for creating a new table as that of
existing one with data..
create table test1 as select * from test is not working.
Regards
KrishSELECT * INTO NewTable FROM OldTable
--
Rohtash Kapoor
http://www.sqlmantra.com
<anonymous@.discussions.microsoft.com> wrote in message
news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> hi,
> What is the syntax for creating a new table as that of
> existing one with data..
> create table test1 as select * from test is not working.
>
> Regards
> Krish|||Hi,
To add on, this command just copies the table structure and data. Indexes ,
Constraints and Identity property
needs to be created manually.
--
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> SELECT * INTO NewTable FROM OldTable
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > hi,
> >
> > What is the syntax for creating a new table as that of
> > existing one with data..
> >
> > create table test1 as select * from test is not working.
> >
> >
> > Regards
> > Krish
>|||That's right. However, IDENTITY property will be copied to new table.
--
Rohtash Kapoor
http://www.sqlmantra.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Hi,
> To add on, this command just copies the table structure and data. Indexes
,
> Constraints and Identity property
> needs to be created manually.
> --
> Thanks
> Hari
> MCDBA
> "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > SELECT * INTO NewTable FROM OldTable
> >
> > --
> > Rohtash Kapoor
> > http://www.sqlmantra.com
> >
> >
> >
> > <anonymous@.discussions.microsoft.com> wrote in message
> > news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > > hi,
> > >
> > > What is the syntax for creating a new table as that of
> > > existing one with data..
> > >
> > > create table test1 as select * from test is not working.
> > >
> > >
> > > Regards
> > > Krish
> >
> >
>|||Hi,
Yes, That is correct.
--
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#diRfGMZEHA.556@.tk2msftngp13.phx.gbl...
> That's right. However, IDENTITY property will be copied to new table.
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > To add on, this command just copies the table structure and data.
Indexes
> ,
> > Constraints and Identity property
> > needs to be created manually.
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> > news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > > SELECT * INTO NewTable FROM OldTable
> > >
> > > --
> > > Rohtash Kapoor
> > > http://www.sqlmantra.com
> > >
> > >
> > >
> > > <anonymous@.discussions.microsoft.com> wrote in message
> > > news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > > > hi,
> > > >
> > > > What is the syntax for creating a new table as that of
> > > > existing one with data..
> > > >
> > > > create table test1 as select * from test is not working.
> > > >
> > > >
> > > > Regards
> > > > Krish
> > >
> > >
> >
> >
>

Create table script without drop

Hello all!
I'm currently working on a project where we have several customers with
the same application. The database is constantly being changed and it's
hard to keep track of all the changes from all the versions in the
customers' systems.
Usually I create the changes script every time I alter any of the tables
but there is always a risk of loosing them. I wonder if there is anyway
of creating a script that updates all the tables instead of dropping and
creating them all, so our customers won't loose the database records.
Thanks in advance,
Hugo MadureiraHugo,
You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
TABLE.
eg.
Alter Table MyTable
Add MyColumn varchar(10)
HTH
Barry|||You can get rid of a lot of headaches by using SQL Compare.
www.red-gate.com
"Hugo Madureira" <hugomadureira@.hotmail.com> wrote in message
news:%232216JpJGHA.3696@.TK2MSFTNGP15.phx.gbl...
> Hello all!
> I'm currently working on a project where we have several customers with
> the same application. The database is constantly being changed and it's
> hard to keep track of all the changes from all the versions in the
> customers' systems.
> Usually I create the changes script every time I alter any of the tables
> but there is always a risk of loosing them. I wonder if there is anyway of
> creating a script that updates all the tables instead of dropping and
> creating them all, so our customers won't loose the database records.
>
> Thanks in advance,
> Hugo Madureira|||Of course, this gets more complex than just adding columns. Such as
adding/removing columns with check constraints, foreign key constraints,
primary key constraints, unique constraints, computed columns, changing
datatypes/scale/precision, etc. Not all table changes are adding columns.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1138731694.928227.324210@.z14g2000cwz.googlegroups.com...
> Hugo,
> You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
> TABLE.
> eg.
> Alter Table MyTable
> Add MyColumn varchar(10)
>
> HTH
> Barry
>|||Ahh now I understand what he *actually* wanted... oops!
Barry|||I was looking for a possible way of doing that with Enterprise Manager
manager, in a way that it could be done automatically.
When I use Enterprise Manager to create a table script, it drops the
table and re-creates it. That causes data loss in the database.
If there is no way of doing that, is it possible to easily edit the
script generated by Enterprise Manager to do that?
Barry wrote:
> Hugo,
> You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
> TABLE.
> eg.
> Alter Table MyTable
> Add MyColumn varchar(10)
>
> HTH
> Barry
>

Create table not working...?

I'm trying to create a couple of SQL commands that create and then destory a
database (for unit testing purposes).
I am currently just checking this all out (as my SQL isn't that great) in
Query Analyzer to make sure it works properly and have come across an issue.
This is my code--
BEGIN
CREATE DATABASE CynoNUnitTestDB
CREATE TABLE CynoUser
(
UserID int IDENTITY PRIMARY KEY,
UserName nvarchar(50)
)
END
Pretty simple stuff. However I then go into Enterprise manager or navigate
using the treeview on the left of Query Analyzer to find that my table
"CynoUser" has not been created! The database is there but the table isn't.
However if I then try to create it again it tells me the object already
exists!?!?! If so, then why can't I see it?
I'm assuming i've just got permissions wrong or something, can anyone tell
me what i'm doing wrong here?
Kind Regards
Jax"Simon Tamman {Uchiha Jax}"
< i_am_GETRIDOFTHISJUNKanti_everything@.NOS
PAMhotmail.com> wrote in message
news:5WA1f.18687$DO.13442@.newsfe3-gui.ntli.net...
> I'm trying to create a couple of SQL commands that create and then destory
> a
> database (for unit testing purposes).
> I am currently just checking this all out (as my SQL isn't that great) in
> Query Analyzer to make sure it works properly and have come across an
> issue.
> This is my code--
> BEGIN
> CREATE DATABASE CynoNUnitTestDB
> CREATE TABLE CynoUser
> (
> UserID int IDENTITY PRIMARY KEY,
> UserName nvarchar(50)
> )
> END
>
You created CynoUser in another database, probably Master.
After you create a database, you need to connect to it and then create the
table.
CREATE DATABASE CynoNUnitTestDB
GO
USE CynoNUnitTestDB
GO
CREATE TABLE CynoUser
(
UserID int IDENTITY PRIMARY KEY,
UserName nvarchar(50)
)
This script issues three seperate batches (seperated by 'GO'). The first
batch creates the database, the second switches your connection's current
database context, the third creates the table in the new database context.
David|||You've created the database, so what? The table CynoUser will be created in
the database you're in. How is SQL Server supposed to know that you want
the table to created in some other database than your current context?
Try:
CREATE DATABASE CynoNUnitTestDB
GO
USE CynoNUnitTestDB
GO
CREATE TABLE dbo.CynoUser ...
--or
CREATE DATABASE CynoNUnitTestDB
GO
CREATE TABLE CynoNUnitTestDB.dbo.CynoUser ...
"Simon Tamman {Uchiha Jax}"
< i_am_GETRIDOFTHISJUNKanti_everything@.NOS
PAMhotmail.com> wrote in message
news:5WA1f.18687$DO.13442@.newsfe3-gui.ntli.net...
> I'm trying to create a couple of SQL commands that create and then destory
> a
> database (for unit testing purposes).
> I am currently just checking this all out (as my SQL isn't that great) in
> Query Analyzer to make sure it works properly and have come across an
> issue.
> This is my code--
> BEGIN
> CREATE DATABASE CynoNUnitTestDB
> CREATE TABLE CynoUser
> (
> UserID int IDENTITY PRIMARY KEY,
> UserName nvarchar(50)
> )
> END
> --
> Pretty simple stuff. However I then go into Enterprise manager or
> navigate
> using the treeview on the left of Query Analyzer to find that my table
> "CynoUser" has not been created! The database is there but the table
> isn't.
> However if I then try to create it again it tells me the object already
> exists!?!?! If so, then why can't I see it?
> I'm assuming i've just got permissions wrong or something, can anyone tell
> me what i'm doing wrong here?
> Kind Regards
> Jax
>
>|||Thank you very much David and Aaron.
Works perfectly now.
CREATE DATABASE CynoNUnitTestDB
GO
USE CynoNUnitTestDB
GO
CREATE TABLE CynoUser
(
UserID int IDENTITY PRIMARY KEY,
UserName nvarchar(50)
)
Is all good. :)
You'll have to excuse me Aaron, i'm not used to creating databases and
tables in SQL so the whole issue of scope completely passed me by. Hopefully
i'll get better soon :).
"Simon Tamman {Uchiha Jax}"
< i_am_GETRIDOFTHISJUNKanti_everything@.NOS
PAMhotmail.com> wrote in message
news:5WA1f.18687$DO.13442@.newsfe3-gui.ntli.net...
> I'm trying to create a couple of SQL commands that create and then destory
a
> database (for unit testing purposes).
> I am currently just checking this all out (as my SQL isn't that great) in
> Query Analyzer to make sure it works properly and have come across an
issue.
> This is my code--
> BEGIN
> CREATE DATABASE CynoNUnitTestDB
> CREATE TABLE CynoUser
> (
> UserID int IDENTITY PRIMARY KEY,
> UserName nvarchar(50)
> )
> END
> --
> Pretty simple stuff. However I then go into Enterprise manager or
navigate
> using the treeview on the left of Query Analyzer to find that my table
> "CynoUser" has not been created! The database is there but the table
isn't.
> However if I then try to create it again it tells me the object already
> exists!?!?! If so, then why can't I see it?
> I'm assuming i've just got permissions wrong or something, can anyone tell
> me what i'm doing wrong here?
> Kind Regards
> Jax
>
>|||Thank you very much for your help.
All works fine now!
Thankee.
"Simon Tamman {Uchiha Jax}"
< i_am_GETRIDOFTHISJUNKanti_everything@.NOS
PAMhotmail.com> wrote in message
news:5WA1f.18687$DO.13442@.newsfe3-gui.ntli.net...
> I'm trying to create a couple of SQL commands that create and then destory
a
> database (for unit testing purposes).
> I am currently just checking this all out (as my SQL isn't that great) in
> Query Analyzer to make sure it works properly and have come across an
issue.
> This is my code--
> BEGIN
> CREATE DATABASE CynoNUnitTestDB
> CREATE TABLE CynoUser
> (
> UserID int IDENTITY PRIMARY KEY,
> UserName nvarchar(50)
> )
> END
> --
> Pretty simple stuff. However I then go into Enterprise manager or
navigate
> using the treeview on the left of Query Analyzer to find that my table
> "CynoUser" has not been created! The database is there but the table
isn't.
> However if I then try to create it again it tells me the object already
> exists!?!?! If so, then why can't I see it?
> I'm assuming i've just got permissions wrong or something, can anyone tell
> me what i'm doing wrong here?
> Kind Regards
> Jax
>
>

create table

i have this code: this is working in mysql but not in sql server 2000. could someone help me.
CREATE TABLE Project (ProjectCode varchar(100),MapNumber varchar(100),Status float(3,2));
i've got this message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
there is no float in SQL. Try decimal instead. you can also get rid of the colon at the end.
|||thank you very much.|||

What kind of ProjectCode is a VARCHAR(100)? Ditto for MapNumber?!?
You need to use proper datatypes and domains to ensure you don't have garbage data.

|||

harrha19 wrote:

i have this code: this is working in mysql but not in sql server 2000. could someone help me.
CREATE TABLE Project (ProjectCode varchar(100),MapNumber varchar(100),Status float(3,2));
i've got this message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

There is Float data type in SQL Server what is wrong is you are setting precison which you cannot do with Float in SQL Server You can only set precision and scale in Decimal and Numeric. Float is seldom used in SQL Server but most T-SQL relational algebra and calculus functions are in Float. Hope this helps.

Create SQL database with C#

Hi,

I am trying to:
1. Create a SQL database (I am working with SQL 2005 Express)
2. with a C# code
3. when the user is not the computer administrator.

I have managed to create the database file (code below). I am not sure
it is the right way.
Can you take a look please?
I would like to either create a password for these database or a
special user so only my
software will be able to control it (change data). How do I do that?

tmpConn.ConnectionString = "Data Source=(local); DATABASE =
master;Integrated Security=True; user instance=true";
sqlCreateDBQuery = " CREATE DATABASE " + DBParam.DatabaseName +
" ON
PRIMARY "
+ " (NAME = " +
DBParam.DataFileName +", "
+ " FILENAME = '" +
DBParam.DataPathName +"', "
+ " SIZE = 5MB,"
+ " FILEGROWTH =" +
DBParam.DataFileGrowth +") "
+ " LOG ON (NAME =" +
DBParam.LogFileName +", "
+ " FILENAME = '" +
DBParam.LogPathName + "', "
+ " SIZE = 1MB, "
+ " FILEGROWTH =" +
DBParam.LogFileGrowth +") ";
SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, tmpConn);
try
{
tmpConn.Open();
MessageBox.Show(sqlCreateDBQuery);
myCommand.ExecuteNonQuery();
MessageBox.Show("Database has been created successfully!",
"Create
Database", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "Create Database",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
tmpConn.Close();
}orenbt78 (orenbt78@.googlemail.com) writes:

Quote:

Originally Posted by

I have managed to create the database file (code below). I am not sure
it is the right way.
Can you take a look please?

I guess that if the database gets created that it works. The only thing
to consider is maybe the initial sizes. 5 MB is a quite small database.
Then again, I don't really know what you will put into it.

Quote:

Originally Posted by

I would like to either create a password for these database or a
special user so only my
software will be able to control it (change data). How do I do that?

You could use an application role. You add users to the database without
any privileges, and then the application users sp_setapprole to set the
application role, to which you have granted all necessary rights.
To activate the application role, you need a password.

But note that this does not prevent users from side-stepping your tool,
it just makes it more difficult. If you give users the password, they
can use sp_setapprole from Mgmt Studio. If you don't give them the
password, you need to hide in the application, in which case be found
for anyone who wants. Or the connection can eavesdropped. (The password
can obfusticated on the wire, but that does not help.)

And in any case, anyone can just copy the database files and attach
them on a server where they have admin rights and do whatever you want
with it.

So while you cannot prevent this, the application role can still serve
the purpose to tell people to keep out, or the warranty will be voided.
But this latter is something you also include in a license agreement.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Create procedure to insert records for a project

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul

Quote:

Originally Posted by PRAW

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul

if this is one time and you have many records to insert and happens to be in a file (txt or xls), try DTS|||

Quote:

Originally Posted by ck9663

if this is one time and you have many records to insert and happens to be in a file (txt or xls), try DTS

------
No this is not one time and i have to insert this set of 11 records for each of the 40 projects i.e. 40 times.. so i need to be able to create a procedure where i can increment the value of task_taskid for each record and insert the corresponding field values.|||

Quote:

Originally Posted by PRAW

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul

Try below Logic to create a procedure:

1. Have a Cursor that will hold task_name, task_id, task_budjetdollar, task_budjethours
2. have a counter variable initilized to 1
3. LOOP through 1181..1221 becuase u said u need to add for 40 projectids from 1181,1182 and so on
4. With a FOR LOOP, loop through CURSOR data, and for each record (task_name), insert into table with task_name,task_id and projectid(FOR Loop value) and task_outline_num = counter variable that you have declared before in the procedure
5. COMMIT
6. Increment the counter variable by 1
7. End the Cursor LOOP
8. Reset the counter variable to 1
9. End Outer FOR LOOP
10. End Procedure

Create new database based on a template using SMO

Hi All,

I'm working on a web application where the user needs to be able to create and name new databases that are identical in structure to other existing databases (that is, all tables, stored procedures, functions, indexes, etc.). This is so that they can create a new database for each client and need to be able to do this through the web application. Having hunted around a fair bit, I've established that SMO is capable of doing pretty much everything that I want. The only problem is that everything I do seems to be based on the actual SQL Server and associated databases rather than the ones I have created in the App_Data folder.

The relevant code (so far) is:

Dim sqlServerAs New Server()With sqlServer.ConnectionContext .ServerInstance ="(local)" .Connect() .Disconnect()End WithFor Each dbAs DatabaseIn sqlServer.Databases ListView1.Items.Add(db.Name)NextDim newDatabaseAs New Database(sqlServer, DbName.Text.ToString)newDatabase.Create()

This does actaully create a new database, just not where I want it! Can anyone point me in the right direction as to how I can create a copy of a database in the App_Data folder?

Thanks & regards,

Paul

One general question first, will the server be running nothing but these databases? If so you may well be able to simplify the process by creating a template database within the model database. When a new database is created, it will be populated using objects in model.

The second issue is one of security as effectively sa permissions are required to create a new database. Your security concerns may be insufficient for this to be an issue, however you would be well advised to employ a level of indrection. Instead of letting the users directly trigger the create process, set up a queue table in a suitable location and have a windows service monitor this queue and create a database as required.

To find out what is required in the way of TSQL, just generate a database create script for an existing database inside (Enterprise Manager for SQL2000 and SQL Server Management Studio for SQL2005).

|||

Hi,

Thank you for your reply. I appreciate any help as I've struggled on this whole problem for a couple of days and making very little progress...

Anyway, at the moment the SQL Server is only being used for the client databases in this application, but I don't know how long that will continue to be the case.

As for the security issue, only Administrators on the Active Directory account will ultimately be able to access the page for creating databases. At the moment I'm just using site security, but will be changing this later to Active Directory.

I had already created a script file, but as this was several thousand lines, I'm rather hoping for a more manageable solution!

Thanks again,

Paul

|||Are you able to use multiple SQL Instances on that server? If so create an instance just for this application and you could use the model approach. I am glad that you have already considered security - for many applications, secuirity is an afterthought if it is thought of at all.|||

Hi,

I know that I should know, but I have no idea if I can create multiple instances of the server or not. Assuming that I can, what exactly is the model approach? How do I make fresh copies of the amended 'Model' database?

Thanks again,

Paul

create login

i need to make the following instructions (the name used are for example different than the database that im working)

Create new database 'Company'. Add new login (e.g. 'test_login') for 'Company' with password (e.g. '12345').
Create new user (e.g. 'test_user') for login 'test_login'. Set 'Database role' of user 'test_user' in 'Company' to 'db_owner'.
Create new schema (e.g. 'test_schema'). Set 'Default schema' of 'test_user' in 'Company' to 'test_schema'.
Input sql script 'Company.sql' as login 'test_login' to populate 'Company'.

im looking in the tutorial and for the moment i have this

CREATE LOGIN logs1 WITH PASSWORD = '12345'
CREATE USER luis FOR LOGIN logs1
WITH DEFAULT_SCHEMA;
GO

but i want to make all the steps.

Have a look in books on-line - it should be easy to find the instructions you need.

e.g. search for create database, roles,

|||i had previusly created the database with all the tables and data, then how can i create a schema for that and the functions that I found to set 'Database Role' are for Java, J#, C, etc, not for T-sql
|||

In books on-line filter the results by the database engine and you should get the t-sql help.

Look for create schema and create role.

You can also look for sp_addrole (the v2000 command) and it should link to the create role command.

You can also google "sql server create schema" but you need to know the command to do that

I'm not being awkward - you seem to be able to cope just have a problem with books on-line and you will get a lot more out of that rather than being told the code to write.

CREATE FUNCTION owner_name

Hi,

I have a problem with a function I am creating. I am working on a project for Uni. I coded all my SQL on my home computer, but now I have transferred it all to the schools system and I have a problem with the permissions on the schools SQL Server.

On my home computer I created

CREATE FUNCTION GetDateOnly

It automatically gave it the 'dbo' owner_name which worked fine at home, but on the Uni computers it says I dont have permission to execute it. The owner of all my other Procedures, tables etc. is SCMS/ral6 when I create them at school, which is also the owner_name of my function if I create it at school, but...

If I try and run a function like this:

SCMS/ral6.GetDateOnly()

It does not like the '/'

Nor does it work if I leave that part of, and just go:

ral6.GetDateOnly()

Does anyone know how I can run this? The Uni is not likely to change there whole permissions to run my one project, but without this FUNCTION, my project will not work.

Thankstry [SCMS/ral6].GetDateOnly()|||Cheers for that, I think you just saved my life :)

create dymanic dts

i can writing dynamic dts package . but not working.

code is here :

Public goPackageOld As New Package
Public goPackage As Package2

Public Sub RunDTS()
Dim goPackage As Package2
goPackage = CType(goPackageOld, Package2)
goPackage.Name = "DTS3"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = CType(2, DTSPackagePriorityClass)
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = CType(4096, DTSIsolationLevel)
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0

Dim oConnProperty As OleDBProperty
'

' create package connection information

'

Dim oConnection As Connection2
'- a new connection defined below.

oConnection = CType(goPackage.Connections.New("DTSFlatFile"), Connection2)

oConnection.ConnectionProperties.Item("Data Source").Value = "C:\hede\50.txt"
oConnection.ConnectionProperties.Item("Mode").Value = 1
oConnection.ConnectionProperties.Item("Row Delimiter").Value = "||##"
oConnection.ConnectionProperties.Item("File Format").Value = 1
oConnection.ConnectionProperties.Item("Column Delimiter").Value = "|#$,"
oConnection.ConnectionProperties.Item("File Type").Value = 1
oConnection.ConnectionProperties.Item("Skip Rows").Value = 0
oConnection.ConnectionProperties.Item("First Row Column Name").Value() = True
oConnection.ConnectionProperties.Item("Max characters per delimited column").Value = 8000
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\hede\50.txt"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
goPackage.Connections.Add(CType(oConnection, Connection))

oConnection = CType(goPackage.Connections.New("SQLOLEDB"), Connection2)
oConnection.ConnectionProperties.Item("Integrated Security").Value = "SSPI"
oConnection.ConnectionProperties.Item("Persist Security Info").Value() = True
oConnection.ConnectionProperties.Item("Initial Catalog").Value = "**"
oConnection.ConnectionProperties.Item("Data Source").Value = "(local)"
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS Import/Export Wizard"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.UserID = "**"
oConnection.Password = "**"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "**"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
goPackage.Connections.Add(CType(oConnection, Connection))
oConnection = Nothing

'

' create package steps information

'

Dim oStep As Step2
Dim oPrecConstraint As PrecedenceConstraint

oStep = CType(goPackage.Steps.New, Step2)
oStep.Name = "Copy Data from myTextFile to [(local)].[dbo].[111] Step"
oStep.Description = "Copy Data from myTextFile to [(local)].[dbo].[111] Step"
oStep.ExecutionStatus = CType(1, DTSStepExecStatus)
oStep.TaskName = "Copy Data from myTextFile to [(local)].[dbo].[111] Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = CType(3, DTSStepRelativePriority)
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
goPackage.SaveToSQLServer("(local)", "**", "**", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "")
Try
goPackage.Execute()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

not have error. try clause is running.but not correct result.

thx..

The Package.Execute method will only thow an exception if it cannot run the package at all, virtual impossible to get. It does not throw an exception if the package fails, as that is still a valid execution.

To capture details of any errors that happend within the package, use the package events provider.

HOW TO: Handle Data Transformation Services Package Events in Visual C# .NET
(http://support.microsoft.com/kb/319985/en-us)

|||

ok i done.

but it didnt have error.

created package in sql server.

but double click on package have error =

Error Source: Microsoft Data Transformation Services(DTS) Package
Error Description: Task 'Copy Data from C:\hede\hede.txt to [(local)].[dbo].[111] Task' was not found.

i want to here : can i do writing Transformation Task Name = Task

Create DTS PACKAGE programmatic

Hi guys..!!

i am working on Dynamic creation of DTS-packages in C#.NET 2005(sql server 200)

but i not must create package in Sql Server.
but i cant...

can i do ?
any ideas ?

thx...

DTS has a Save as VB option. This is a great way of getting sample code, so try that on a demo package. VB to VB.Net has some differences, see this link for some tips.

Converting a DTS Package from Visual Basic 6.0 to Visual Basic .Net
(http://www.sqldts.com/default.aspx?264)

VB.Net to C# should not be hard, plenty of tools that do this.

Hopefully when you view the VB sample code you will get a feel for the object model and how it is used, and going forward you can just write the C# directly.

There is a DTS specific newsgroup (microsoft.public.sqlserver.dts) which may be worth search and posting on, as it is DTS not SSIS focused.

|||

but this source not contain code for vb or vb.net
no problem is writing vb or vb.net

problem is "how to create programmatic (dynamic) DTS-Package in dotnet? "

thx...

|||

You appear to be repeating yourself, so can we try again...

Mehmet Metin Altuntas wrote:

problem is "how to create programmatic (dynamic) DTS-Package in dotnet? "

To create a DTS package dynamically in dotnet you need to write some code. DTS uses an object model, have you added a reference?

Mehmet Metin Altuntas wrote:

but this source not contain code for vb or vb.net

What is not source code? I suggested using the Save as VB option to generate some sample code. If you already know how to use the DTS object model in code, what are you asking? If you do not know how to use the DTS object model in code, try generating some sample code as a guide.

Mehmet Metin Altuntas wrote:

no problem is writing vb or vb.net

Sorry, that does not make sense.

The Save as VB option wil give you VB code. The link told you how to convert this to VB.net. There are tools that convert VB.net to C#. It may be a three stage process but it will give you C# code. Once you are familiar with this you can write c# directly, but to HELP you LEARN how to use it in code, Save as VB is a start point.

Surely some code, even VB is easier to learn from than no code at all. There is no Save As C# option, so learn from the VB.

Any help?

CREATE DATABASE permission denied in database master. error

got rid of my error about user login rights, it was all working yesterday. but for some reason i now get this error

 CREATE DATABASE permission deniedin database'master'.An attempt to attach an auto-named databasefor file C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or itis located on UNC share.Description: An unhandled exception occurred during the execution of the current web request. Please review the stack tracefor more information about the error and where it originatedin the code.Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission deniedin database'master'.An attempt to attach an auto-named databasefor file C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or itis located on UNC share.Source Error:An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identifiedusing the exception stack trace below.Stack Trace:[SqlException (0x80131904): CREATE DATABASE permission deniedin database'master'.An attempt to attach an auto-named databasefor file C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or itis located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734995 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.GridView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

i have this in my web.config file

<connectionStrings>
<add name="ConnectionStringTest" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf;Integrated Security=SSPI;Connect Timeout=30;User Instance=False"
providerName="System.Data.SqlClient" />
</connectionStrings>
<appSettings />

<system.web>
<!--
Set compilation debug="true" to insert debugging symbols into the compiled page.
Because this affects performance, set this value to true only during development.
-->
<compilation debug="true" />
<identity impersonate="true"/>

and my asp connection string is

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringTest %>"
SelectCommand="SELECT [entryID], [compID], [emailAddy], [answer] FROM [entry]"></asp:SqlDataSource>

if i set user instance to true i get a user permission error.

it says on sql server management that i have dbo rights on my database, but it wont let me put datareader or write on this login. any ideas? its driving me insane

Looks like SQL server could not connect you file to system

An attempt to attach an auto-named databasefor file C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or itis located on UNC share.

so it automatically switched you to master database which is default for new users and probably you have no rights to create table in it.

Is your database file local to your server?

Does you SQL server user accont has rights to access this database file?

Check it.

Thanks

|||

Is your database file local to your server? - yup

Does you SQL server user accont has rights to access this database file? - i only have 1 user account - funkymp which i log onto my computer which, it says on sql management tool that im the dbo for the database

|||

if i go into databases - databasename - security - users i have dbo (which is the funkymp account), guest, info schema, rob\aspnet and sys.

if i go into securiy - logins, i have funkymp there again, default database is master - should i change this to the database im trying to access? this is driving me up the wall had the same error for 2 das now, are there any sql commands that i can run as a new query to check if i have the rights to access that table with the user funkymp?

|||

Hi,

It seems that you might not be connecting to the correct database.

Please try to add Initial Catalog=<DatabaseName> in your connection string.

HTH.

CREATE DATABASE errors

I am creating my first database using T SQL. I am working form a book and have checked for typos and any other things that may be obvious. I got this directory path

from the address bar where these files are stored. I copied and pasted this to avoid typos.

I get a couple errors when I execute my script. Here is the code that I am using.

CREATE DATABASE Accounting

ON

(NAME = 'Accounting',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\

Data\AccountingData.mdf',

SIZE = 10,

MAXSIZE = 50,

FILEGROWTH = 5)

LOG ON

(NAME = 'AccountingLog',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\

Data\AccountingLog.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB)

GO

These are the errors that I got.

Please help,

Thanks

Msg 5133, Level 16, State 1, Line 1 Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL Data\AccountingData.mdf" failed with the operating system error 123(The filename, directory name, or volume label syntax is incorrect.).

Msg 1802, Level 16, State 1, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

This post is identical to the post you made on Jul 1, located here.

A suggest was supplied then, have you tried it?

|||

Thanks, I just tried it. I also found a typo in the name for the "Accounting.mdf" file. I had "Accouting.mdf" on accident. I think that since I didn't have the typo before that your solution was what fixed it. I am working out of a book that basically shows the same thing. It's strange that it wouldn't work. I guess that is a by product of trying to get it to fit onto the page.

-Thanks for the help

CREATE DATABASE errors

I am creating my first database using T SQL. I am working form a book and have checked for typos and any other things that may be obvious. I got this directory path

from the address bar where these files are stored. I copied and pasted this to avoid typos.

I get a couple errors when I execute my script. Here is the code that I am using.

CREATE DATABASE Accounting

ON

(NAME = 'Accounting',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\

Data\AccountingData.mdf',

SIZE = 10,

MAXSIZE = 50,

FILEGROWTH = 5)

LOG ON

(NAME = 'AccountingLog',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\

Data\AccountingLog.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB)

GO

These are the errors that I got.

Please help,

Thanks

Msg 5133, Level 16, State 1, Line 1 Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL Data\AccountingData.mdf" failed with the operating system error 123(The filename, directory name, or volume label syntax is incorrect.).

Msg 1802, Level 16, State 1, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

This post is identical to the post you made on Jul 1, located here.

A suggest was supplied then, have you tried it?

|||

Thanks, I just tried it. I also found a typo in the name for the "Accounting.mdf" file. I had "Accouting.mdf" on accident. I think that since I didn't have the typo before that your solution was what fixed it. I am working out of a book that basically shows the same thing. It's strange that it wouldn't work. I guess that is a by product of trying to get it to fit onto the page.

-Thanks for the help