Sql Create error: creating

Showing posts with label creating. Show all posts

Thursday, March 29, 2012

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 test

Tuesday, March 27, 2012

create table script

I am creating table in sql server 2005, by using the below script,but it
showing "syntax error near (" The below script is created from generate
script option of sql) ,I ll apprecite the solution asap,Thanx guys
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRoles](
[UserRoleID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[ExpiryDate] [datetime] NULL,
[IsTrialUsed] [bit] NULL,
[EffectiveDate] [datetime] NULL,
CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
(
[UserRoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]I assume that you by "it showing "syntax error near ("" Mean that when you execute the script from
some tool you get that error message? If so, can you specify what tool you use to execute the script
and against what version of SQL Server. Also, make sure you do not mark any text when you execute
it, or that you do mark only the text you want to be executed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>I am creating table in sql server 2005, by using the below script,but it
> showing "syntax error near (" The below script is created from generate
> script option of sql) ,I ll apprecite the solution asap,Thanx guys
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[UserRoles](
> [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
> [UserID] [int] NOT NULL,
> [RoleID] [int] NOT NULL,
> [ExpiryDate] [datetime] NULL,
> [IsTrialUsed] [bit] NULL,
> [EffectiveDate] [datetime] NULL,
> CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
> (
> [UserRoleID] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]|||I am not using any kind of tool, i am executing it from query analyzer of sql
server 2005 standard ediion
"Tibor Karaszi" wrote:
> I assume that you by "it showing "syntax error near ("" Mean that when you execute the script from
> some tool you get that error message? If so, can you specify what tool you use to execute the script
> and against what version of SQL Server. Also, make sure you do not mark any text when you execute
> it, or that you do mark only the text you want to be executed.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
> >I am creating table in sql server 2005, by using the below script,but it
> > showing "syntax error near (" The below script is created from generate
> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
> >
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > CREATE TABLE [dbo].[UserRoles](
> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
> > [UserID] [int] NOT NULL,
> > [RoleID] [int] NOT NULL,
> > [ExpiryDate] [datetime] NULL,
> > [IsTrialUsed] [bit] NULL,
> > [EffectiveDate] [datetime] NULL,
> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
> > (
> > [UserRoleID] ASC
> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> > ) ON [PRIMARY]
>
>|||>I am not using any kind of tool, i am executing it from query analyzer of sql
> server 2005 standard ediion
Then that tool would be query analyzer (or perhaps you meant SQL Server Management Studio).
Anyhow, I executed the code you posted and it worked just fine. I'm also on SQL Server 2005. My
guess is that you by mistake maked a part of the text so that only that text were submitted to sQL
Server.-
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>I am not using any kind of tool, i am executing it from query analyzer of sql
> server 2005 standard ediion
> "Tibor Karaszi" wrote:
>> I assume that you by "it showing "syntax error near ("" Mean that when you execute the script
>> from
>> some tool you get that error message? If so, can you specify what tool you use to execute the
>> script
>> and against what version of SQL Server. Also, make sure you do not mark any text when you execute
>> it, or that you do mark only the text you want to be executed.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
>> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>> >I am creating table in sql server 2005, by using the below script,but it
>> > showing "syntax error near (" The below script is created from generate
>> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
>> >
>> > SET ANSI_NULLS ON
>> > GO
>> > SET QUOTED_IDENTIFIER ON
>> > GO
>> > CREATE TABLE [dbo].[UserRoles](
>> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
>> > [UserID] [int] NOT NULL,
>> > [RoleID] [int] NOT NULL,
>> > [ExpiryDate] [datetime] NULL,
>> > [IsTrialUsed] [bit] NULL,
>> > [EffectiveDate] [datetime] NULL,
>> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
>> > (
>> > [UserRoleID] ASC
>> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =>> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> > ) ON [PRIMARY]
>>|||Another posibility is that the server/instance you are running this against
is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
2005. Try running
Select ServerProperty('ProductVersion')
If it returns a value where the first digit is 8, like
8.00.2039
then it is SQL 2000 and that is your problem.
But if it returns a value where the first digit is 8, like
9.00.3042.00
then it is SQL2005 and my guess is incorrect.
But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
and should be removed.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
> >I am not using any kind of tool, i am executing it from query analyzer of
> >sql
>> server 2005 standard ediion
> Then that tool would be query analyzer (or perhaps you meant SQL Server
> Management Studio).
> Anyhow, I executed the code you posted and it worked just fine. I'm also
> on SQL Server 2005. My guess is that you by mistake maked a part of the
> text so that only that text were submitted to sQL Server.-
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>>I am not using any kind of tool, i am executing it from query analyzer of
>>sql
>> server 2005 standard ediion
>> "Tibor Karaszi" wrote:
>> I assume that you by "it showing "syntax error near ("" Mean that when
>> you execute the script from
>> some tool you get that error message? If so, can you specify what tool
>> you use to execute the script
>> and against what version of SQL Server. Also, make sure you do not mark
>> any text when you execute
>> it, or that you do mark only the text you want to be executed.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
>> message
>> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>> >I am creating table in sql server 2005, by using the below script,but
>> >it
>> > showing "syntax error near (" The below script is created from
>> > generate
>> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
>> >
>> > SET ANSI_NULLS ON
>> > GO
>> > SET QUOTED_IDENTIFIER ON
>> > GO
>> > CREATE TABLE [dbo].[UserRoles](
>> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
>> > [UserID] [int] NOT NULL,
>> > [RoleID] [int] NOT NULL,
>> > [ExpiryDate] [datetime] NULL,
>> > [IsTrialUsed] [bit] NULL,
>> > [EffectiveDate] [datetime] NULL,
>> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
>> > (
>> > [UserRoleID] ASC
>> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
>> > =>> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> > ) ON [PRIMARY]
>>
>|||Thanks, u r correct,but is there any other way, means if i can change any
configuration in sql server 2005 so that it works, or any other way or should
i uninstall sql 2000
Thank u
"Tom Cooper" wrote:
> Another posibility is that the server/instance you are running this against
> is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
> 2005. Try running
> Select ServerProperty('ProductVersion')
> If it returns a value where the first digit is 8, like
> 8.00.2039
> then it is SQL 2000 and that is your problem.
> But if it returns a value where the first digit is 8, like
> 9.00.3042.00
> then it is SQL2005 and my guess is incorrect.
> But if it is SQL 2000, then the entire clause WITH (...) is not valid syntax
> and should be removed.
> Tom
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
> > >I am not using any kind of tool, i am executing it from query analyzer of
> > >sql
> >> server 2005 standard ediion
> >
> > Then that tool would be query analyzer (or perhaps you meant SQL Server
> > Management Studio).
> >
> > Anyhow, I executed the code you posted and it worked just fine. I'm also
> > on SQL Server 2005. My guess is that you by mistake maked a part of the
> > text so that only that text were submitted to sQL Server.-
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> > news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
> >>I am not using any kind of tool, i am executing it from query analyzer of
> >>sql
> >> server 2005 standard ediion
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> I assume that you by "it showing "syntax error near ("" Mean that when
> >> you execute the script from
> >> some tool you get that error message? If so, can you specify what tool
> >> you use to execute the script
> >> and against what version of SQL Server. Also, make sure you do not mark
> >> any text when you execute
> >> it, or that you do mark only the text you want to be executed.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
> >> message
> >> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
> >> >I am creating table in sql server 2005, by using the below script,but
> >> >it
> >> > showing "syntax error near (" The below script is created from
> >> > generate
> >> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
> >> >
> >> > SET ANSI_NULLS ON
> >> > GO
> >> > SET QUOTED_IDENTIFIER ON
> >> > GO
> >> > CREATE TABLE [dbo].[UserRoles](
> >> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
> >> > [UserID] [int] NOT NULL,
> >> > [RoleID] [int] NOT NULL,
> >> > [ExpiryDate] [datetime] NULL,
> >> > [IsTrialUsed] [bit] NULL,
> >> > [EffectiveDate] [datetime] NULL,
> >> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
> >> > (
> >> > [UserRoleID] ASC
> >> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> >> > => >> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> >> > ) ON [PRIMARY]
> >>
> >>
> >>
> >
> >
>
>|||If you use the SSMS scripts wizard to generate the script for the table, you
can tell it to generate a script that is compatable with SQL 2000. On the
Choose Scripts Options page there is an option named Script for Server
Version. Set that to SQL 2000 and it will generate a script that will run
on SQL 2000. Of course, if you are using features that are new for SQL
2005, those features won't be included.
Tom
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:8642EF7D-6437-4C7E-8B3F-29A22B330F7A@.microsoft.com...
> Thanks, u r correct,but is there any other way, means if i can change any
> configuration in sql server 2005 so that it works, or any other way or
> should
> i uninstall sql 2000
> Thank u
> "Tom Cooper" wrote:
>> Another posibility is that the server/instance you are running this
>> against
>> is SQL 2000, not SQL 2005. The syntax you are usingis only valid on SSQL
>> 2005. Try running
>> Select ServerProperty('ProductVersion')
>> If it returns a value where the first digit is 8, like
>> 8.00.2039
>> then it is SQL 2000 and that is your problem.
>> But if it returns a value where the first digit is 8, like
>> 9.00.3042.00
>> then it is SQL2005 and my guess is incorrect.
>> But if it is SQL 2000, then the entire clause WITH (...) is not valid
>> syntax
>> and should be removed.
>> Tom
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> message news:%2393tq$SeIHA.3368@.TK2MSFTNGP02.phx.gbl...
>> > >I am not using any kind of tool, i am executing it from query analyzer
>> > >of
>> > >sql
>> >> server 2005 standard ediion
>> >
>> > Then that tool would be query analyzer (or perhaps you meant SQL Server
>> > Management Studio).
>> >
>> > Anyhow, I executed the code you posted and it worked just fine. I'm
>> > also
>> > on SQL Server 2005. My guess is that you by mistake maked a part of the
>> > text so that only that text were submitted to sQL Server.-
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://sqlblog.com/blogs/tibor_karaszi
>> >
>> >
>> > "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
>> > message
>> > news:5A056BBA-0AC2-4BC4-93A3-79D1FD798AC4@.microsoft.com...
>> >>I am not using any kind of tool, i am executing it from query analyzer
>> >>of
>> >>sql
>> >> server 2005 standard ediion
>> >>
>> >> "Tibor Karaszi" wrote:
>> >>
>> >> I assume that you by "it showing "syntax error near ("" Mean that
>> >> when
>> >> you execute the script from
>> >> some tool you get that error message? If so, can you specify what
>> >> tool
>> >> you use to execute the script
>> >> and against what version of SQL Server. Also, make sure you do not
>> >> mark
>> >> any text when you execute
>> >> it, or that you do mark only the text you want to be executed.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:849EDFC3-1CCB-4BDA-B338-E707C2B5F488@.microsoft.com...
>> >> >I am creating table in sql server 2005, by using the below
>> >> >script,but
>> >> >it
>> >> > showing "syntax error near (" The below script is created from
>> >> > generate
>> >> > script option of sql) ,I ll apprecite the solution asap,Thanx guys
>> >> >
>> >> > SET ANSI_NULLS ON
>> >> > GO
>> >> > SET QUOTED_IDENTIFIER ON
>> >> > GO
>> >> > CREATE TABLE [dbo].[UserRoles](
>> >> > [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
>> >> > [UserID] [int] NOT NULL,
>> >> > [RoleID] [int] NOT NULL,
>> >> > [ExpiryDate] [datetime] NULL,
>> >> > [IsTrialUsed] [bit] NULL,
>> >> > [EffectiveDate] [datetime] NULL,
>> >> > CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
>> >> > (
>> >> > [UserRoleID] ASC
>> >> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
>> >> > IGNORE_DUP_KEY
>> >> > =>> >> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> >> > ) ON [PRIMARY]
>> >>
>> >>
>> >>
>> >
>> >
>>

Create table from web application

i am creating a model in which the control panel i am building for a web application, i can write sql statements in a textbox and the query will be done in my application. But i am have a problem creating a table.

this error of "Create table permission not granted in the sql server" pops up whenever i want to execute the create table sql statements from my web page.

How can i permit my sql server to allow creation of tables from my web application.

Thanks

It all depends on the type of authentication that you are using for the app. The users of your web app will have to have an account capable of creating tables or whatever else you want them to do. What ever it is make an user account just for them. Please do not give the the sa account or you may be sorry later.|||

i want to know how i can authenticate the user to create the table from the server. I know i have to authenticate the user but what it is procedure to do that. I want to know how i can achieve that in Sql 2000

Thank you

|||Ok, What you can do now is in your connection string use the user and pass that the end user supplies. This will need to be a username and pass that you have supplied to them that is valid for the database. When the user logs in just add their user and pass to session so that you can call it back whenever you need it. Then when you create a connection for that user in your connection string use (string)Session["UserName"] and (string)Session["Password"] for the user and pass. To add their user and pass to the Session just use Session.Add("UserName",txtThetextboxthattheyentertheirusernamein.Text); and Session.Add("Password",txtThetextboxthattheyenteredtheirpasswordin.Text); I would put my connection properties in a class so that you can call them whenever and make your method so that it will accept the user and pass that you need to through it. Like this:
[pre]Public bool DBConnection(string Username,String Password)
{
bool connected = false;
Try
{
your connection params using the strings UserName and Password
connected = true;
}
Catch
{
connected = false;
}
Return connected;
}[/pre]
Then you can check the return value to make sure that they connected and use the connection for whatever you want to process. To call it up just type DBConnection((string)Session["UserName"],(string)Session["Password"])
if you need to check it set the return value to a bool and check for true or false
[pre]
bool didconnect = DBConnection((string)Session["UserName"],(string)Session["Password"])
if (didconnect != false)
{
Whatever stuff you need to run against the db;
}
else
{
Errormessage to the user;
}
[/pre]sql

create table from query results

Hi everybody need help on the possibility creating a new table from the results of a view or query? below is my table named table1

ID col1 col2

1 a a
2 b d
3 c f

this would be my new table named table2

ID col1 col2 col3

1 a a aa
2 b d bd
3 c f cf

this new table has an additional column by concatenating col1+col2
tried this procedure but is not working

CREATE TABLE AS (SELECT ID, COL1, COL2, COL1+COL2) TABLE2

thanksare you sure you need to store the concatenation in a separate table?SELECT ID, COL1, COL2, COL1+COL2 AS COL3
INTO TABLE2
FROM TABLE1|||thanks Rudy

yes I'll be storing the concatenated field in a new table

will that procedure create automatically the table2 even if i don't use CREATE TABLE?

alex

Sunday, March 25, 2012

Create Table Error

While Creating the Table in SqlServer200 It is givin the Error like

Server: Msg 2749, Level 16, State 2, Line 1
Identity column 'CustID' must be of data type int, bigint, smallint,
tinyint, or decimal or numeric with a scale of 0, and constrained to
be nonnullable.

Can't we create a Identity for character Field?
my Code is follows

CREATE TABLE Rtest (CustID Varchar(10) NOT NULL IDENTITY,
CustName VARCHAR(40) NOT NULL)NO, You cant have an varchar or char field as an identity column

Identity column must be of type numeric ,or the types specified in the error u have given.|||Identity property is to ensure the column value to increase by the defined step. How would a varchar increase?

Thursday, March 22, 2012

create stored procedure in IF-structure

Hi everyone,

I'm currently struggeling in creating some SQL script to create stored procedures. I found the following example on MSDN:

Code Snippet

USE pubs
IF EXISTS (SELECT name FROM sysobjects 
 WHERE name = 'au_info2' AND type = 'P')
 DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
 @.lastname varchar(30) = 'D%',
 @.firstname varchar(18) = '%'
AS 
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
 ON a.au_id = ta.au_id INNER JOIN titles t
 ON t.title_id = ta.title_id INNER JOIN publishers p
 ON t.pub_id = p.pub_id
WHERE au_fname LIKE @.firstname
 AND au_lname LIKE @.lastname
GO

The thing is, I want to change and use it like this:

Code Snippet

USE pubsIF NOT EXISTS (SELECT name FROM sysobjects

CREATE PROCEDURE ...

USE pubs

GO

ALTER PROCEDURE au_info2 ...

But that does not seem to work.. I get the following error:

 
Code Snippet
Incorrect syntax near the keyword 'PROCEDURE'
 Any idea's? Any help is appreciated!
 Kind regards,
 Frederik

The CREATE statement needs to be the first statement in the batch so you can't have it in after an IF clause.

I guess you could get round this by doing the following:

IF NOT EXISTS.....

EXEC('CREATE PROCEDURE au_info2 AS.....')

HTH!

|||Very dirty, but it works! I need it to avoid some errors when

replicating and such.. Thx!

Wednesday, March 21, 2012

Create SP denied ?

We added a user to a database with db_datareader, db_datawriter and Create Procedure Privilages .. while creating stored procedures using dbo. prefix he gets an error

Server: Msg 2760, Level 16, State 1, Procedure test4, Line 3
Specified owner name 'dbo' either does not exist or you do not have permission to use it.

What is the problem. We don't want him to create procedures with his uid as prefix .. Please helpYou need to put him/her in the dbo_owner role...|||Is it necessary ? If he is dbo he doesn't need to specify dbo. while creating objects . I don't want him to be a dbo|||If yo uwant him to vcreate objetcs as dbo (the owner) he has to have the rights to be in the group...

Or...

He can develop in his own db you set up for him/her...make the dbo in that group (or not) and let them create objects without hard coding the owner...

Then have him give you the scripts and you compile them.

What'dya think?sql

Create Second sa Account (sa2) on SQL 6.5?

On the latest SQL 6.5 with the latest SQL 6.5 service pack, what's the
trick to creating an account besides sa that has the power to create
databases? I was thinking of calling this account 'sa2'. Why do this?
Well, my boss asked me to do it because of a SOX audit + SAS-70 audit
kind of thing.

I tried using the GUI to do it, but then when using the GUI as sa2, I
find the "Create Database" has been greyed out.

I have a funny feeling that the only way to get this is to hack the
sys* tables, which is a little risky.

Oh, and trust me, if I had the political weight in my company to get
off SQL 6.5, I would, but I don't. And mgmt already knows that SQL 6.5
is past support timeframe and is a ticking timebomb.Sounds like the SOX audit has given you another excellent reason to upgrade.
Tell the boss.

Sorry, I'm not familiar with 6.5. In 7.0 or 2000 you would create a login
and grant it the dbcreator or sysadmin role. I don't think this was the same
in 6.5 though.

--
David Portas
SQL Server MVP
--|||(googlemike@.hotpop.com) writes:
> On the latest SQL 6.5 with the latest SQL 6.5 service pack, what's the
> trick to creating an account besides sa that has the power to create
> databases? I was thinking of calling this account 'sa2'. Why do this?
> Well, my boss asked me to do it because of a SOX audit + SAS-70 audit
> kind of thing.
> I tried using the GUI to do it, but then when using the GUI as sa2, I
> find the "Create Database" has been greyed out.
> I have a funny feeling that the only way to get this is to hack the
> sys* tables, which is a little risky.

On SQL 6.5, there is one 'sa' and that's 'sa'. You can login with
Windows Authentication, and if you belong to BUILTIN/Administrators
you will be sa, not DOMAIN\User as on later versions.

As for the particular example of CREATE DATABASE, this is something you can
give to other users, by means of the GRANT statement. But you cannot
granr all rights that 'sa' to other people.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Create Script with dependent objects SQL Server 2005

Hi,

I need to script a view but with dependent objects. The view has a lot of user defined functions and manually creating the script is a pain.

There is a way to do it in SQL Server (2000) Enterprise manager where one right clicks the view --> All Tasks --> Generate Script and then Check

"Generate Scripts for all dependent objects" in the Formatting tab.

The db i am using is on SQL Server 2005.

Is there a equivalent way to do this in SQL Server 2005 Management Studio ?

regards,

Rohan Wali

Have you tried the generate script wizard in SSMS?|||You might try using SMO in this case http://www.sqlteam.com/item.asp?ItemID=23185, as I don't see using script wizard.|||

Hi,

Even i dont see a solution using the script wizard. I will try using SMO using the link provided and see how it goes from there.

Thanks for the tip.

regards,

Rohan Wali

sql

CREATE SCHEMA in db A from a stored procedure in db B

Hi All

I have a SP that i create tables and other objects on another database.

Creating table work well.

declare @.s nvarchar(2000)
set @.s = 'use db01'
set @.s = @.s + 'CREATE TABLE ABC (recid int)
exec (@.s)

But if i try to create a schema it gives error :

'CREATE SCHEMA' must be the first statement in a query batch.

declare @.s nvarchar(2000)
set @.s = 'use db01'
set @.s = @.s + 'CREATE SCHEMA AAA
exec (@.s)

How can i solve it?

Thanks.

declare @.s nvarchar(2000)
set @.s = 'use db01'

exec (@.s)
set @.s = 'CREATE SCHEMA AAA
exec (@.s)

|||

Hi Asvin

If i use "use db01" in exec, when exec completes, it gives up using db01, so if i'm running script in db02, 'CREATE SCHEMA AAA' works on db02.

Best regards.

|||

Standard disclaimer. Generally not a good idea to be creating tables and schemas on the fly.

That out of the way, this method should work:

exec('use tempdb; exec sp_executesql N''create schema test''')

|||

You can do the following in SQL Server 2000/2005 (similar approach can be used in SQL70 also):

declare @.sp nvarchar(500)

set @.sp = quotename(N'db01') + N'sys.sp_executesql'

exec @.sp 'CREATE SCHEMA AAA....'

-- or

exec @.sp @.s

Monday, March 19, 2012

Create project in MS Access 2000

Hallo

I am getting the “overflow” message (MS SQL Server Database Wizard) on attempt of creating a project in MS Access using SQL Server 2005 Express Edition.I am actually able to open an existing DB.

I was wandering why is this happening and what can I do to create such a project

Thank you

The visual tools in Access 2000 do not work with SQL Server 2005.|||

Thank you, for your answer.

I do not really need the visual tool with MS Access 2000. What I would like to do is to create a database on the server using MS Access 2000 and I was wondering whether it's possible.

Regards, Greater

|||The only way to create a database on SQL Server 2005 from Access 2000 that I can think of would be to create an ADO Command object and execute a "create database" script

Create Procedure: SQL Server 2005

Hi everyone,

I'm creating a online e-letter registration system. That saves 'temp' values to a 'temp' table, and then sends a automated email to the subscriber, from which he/she can then except, or decline the registration.

bu now i want to create a procedure, or some kind of funciton in T-SQL that will be scheduled to run every 2 or so hours, to check and see if there is any values in the temp table that is older than 24hours, and if that values is greater than 24hours, it must delete that values from the temp table... so that the temp table doesn't become too full of old, outdated data.

Please help.

Thanks

OK, thats an easy thing, but we need more input to see what you will have to do. Which SQL Server are you using ? The delete part would be the easiest, as it would turn out to be something like

DELETE FROM SomeTable Where DATEDIFF(dd,SomeDatecolumn,GETDATE()) >= 1

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||what do you mean, "what SQL server am I using?" SQl Server 2005....|||OK, it would be interesting to know which SKU you are using (in detail if you use Express or not) because with any other edition than Express you would be able to use the SQL Server agent as well to plan the execution of your procedure. You will not need to create a stored procedure as the statement above will run fine using a simple job step with an TSQL execution element. Once the job step is created you will just have to schedule the execution of the job and you are done.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||no, this is not SQL Server Express, It can be either the Developer Edition, or the Enteprise Edition...|||Then go straight ahead by creating a job, defining a schedule and inserting one step for executing the TSQL statement mentioned above.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Create procedure in target servers

I am creating a job in master server where in one step, it creates
stored procedure in target server. The proc text is exceeding the
limit to directly paste in job scheduler. What is the best way to push
procedure to target servers?
You can either split the sproc into smaller ones to bypass the text size
limit, or save the proc in a text file and use osql in the job to call the
input file.
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407130929.62a38b86@.posting.google.c om...
> I am creating a job in master server where in one step, it creates
> stored procedure in target server. The proc text is exceeding the
> limit to directly paste in job scheduler. What is the best way to push
> procedure to target servers?
|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.c om...
|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.c om...
|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.c om...

Sunday, March 11, 2012

Create procedure in target servers

I am creating a job in master server where in one step, it creates
stored procedure in target server. The proc text is exceeding the
limit to directly paste in job scheduler. What is the best way to push
procedure to target servers?You can either split the sproc into smaller ones to bypass the text size
limit, or save the proc in a text file and use osql in the job to call the
input file.
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407130929.62a38b86@.posting.google.com...
> I am creating a job in master server where in one step, it creates
> stored procedure in target server. The proc text is exceeding the
> limit to directly paste in job scheduler. What is the best way to push
> procedure to target servers?|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP1
2.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP1
2.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP1
2.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...

Create procedure in target servers

I am creating a job in master server where in one step, it creates
stored procedure in target server. The proc text is exceeding the
limit to directly paste in job scheduler. What is the best way to push
procedure to target servers?You can either split the sproc into smaller ones to bypass the text size
limit, or save the proc in a text file and use osql in the job to call the
input file.
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407130929.62a38b86@.posting.google.com...
> I am creating a job in master server where in one step, it creates
> stored procedure in target server. The proc text is exceeding the
> limit to directly paste in job scheduler. What is the best way to push
> procedure to target servers?|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...
> > I am creating a job in master server where in one step, it creates
> > stored procedure in target server. The proc text is exceeding the
> > limit to directly paste in job scheduler. What is the best way to push
> > procedure to target servers?|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...
> > I am creating a job in master server where in one step, it creates
> > stored procedure in target server. The proc text is exceeding the
> > limit to directly paste in job scheduler. What is the best way to push
> > procedure to target servers?|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...
> > I am creating a job in master server where in one step, it creates
> > stored procedure in target server. The proc text is exceeding the
> > limit to directly paste in job scheduler. What is the best way to push
> > procedure to target servers?

Create permission

Is there such granularity in SQL 2K security that might allow a user to
create a temp table (as in a stored proc) but restrict them from creating a
permanent user table?
Message posted via http://www.sqlmonster.com
All users can create temp tables by default.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:4b1a04ce369e4b069715c04b3466fdf2@.SQLMonster.c om...
> Is there such granularity in SQL 2K security that might allow a user to
> create a temp table (as in a stored proc) but restrict them from creating
> a
> permanent user table?
> --
> Message posted via http://www.sqlmonster.com

Create permission

Is there such granularity in SQL 2K security that might allow a user to
create a temp table (as in a stored proc) but restrict them from creating a
permanent user table?
Message posted via http://www.droptable.comAll users can create temp tables by default.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:4b1a04ce369e4b069715c04b3466fdf2@.SQ
droptable.com...
> Is there such granularity in SQL 2K security that might allow a user to
> create a temp table (as in a stored proc) but restrict them from creating
> a
> permanent user table?
> --
> Message posted via http://www.droptable.com

Thursday, March 29, 2012

create table test

create table test

create table test

Tuesday, March 27, 2012

create table script

create table script

Create table from web application

create table from query results

Sunday, March 25, 2012

Create Table Error

Thursday, March 22, 2012

create stored procedure in IF-structure

Wednesday, March 21, 2012

Create SP denied ?

Create Second sa Account (sa2) on SQL 6.5?

Create Script with dependent objects SQL Server 2005

CREATE SCHEMA in db A from a stored procedure in db B

Monday, March 19, 2012

Create project in MS Access 2000

Create Procedure: SQL Server 2005

Create procedure in target servers

Sunday, March 11, 2012

Create procedure in target servers

Create procedure in target servers

Create permission

Create permission

Sql Create error

Blog Archive

About Me