Sql Create error: select

Showing posts with label select. Show all posts

Thursday, March 29, 2012

CREATE TABLE/VIEW from stored procedure or SELECT...

Can anyone tell me how can I create a table in (SQL Server 2000) direct from a stored procedure execution or from a SELECT result?

I need something like this: CREATE TABLE < t > FROM <sp_name p1, p2, ...>or like this:

CREATE TABLE < t > FROM SELECT id, name FROM < w > ...

Thank you!

Look at SELECT ... INTO command.|||

use northwind

select * into #tablex from employees

select * from #tablex

|||

Sorry joeydj but your example create a copy of another table! I need to create a table that contains only a few columns from another table, so that why I need to use a SELECT or a stored procedure that build and execute a SELECT.

Can I do that?

Thanks!

|||Sorry gavrilenko_s but I miss your post! You are right! That is the solution! Thanks!|||

hi,

first you have to create a table that has a similar

structure with the Sp

and then you can use

insert into temp

exec sp1

here's a sample snippet

USE NORTHWIND
select 'my name.........................12345' as productname, 10000.00000
as unitprice, 10000.0000 as quantiTY,
10000.0000 as discount, 10000.0000 as extendedprice
into tempx

truncate table tempx

insert into tempx
exec dbo.CustOrdersDetail '10248'

select * from tempx
drop table tempx

also suggest you make use of UDFs

cheers :)

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 from select result

Hi,
I need to create a table which has the columns from the select statement result.
I tried in this way
drop table j9a
SELECT er.* into j9a
FROM caCase c
LEFT OUTER JOIN paPatient pp ON c.caCaseID=pp.caCaseID
Left Outer JOIN paManagementSite pm ON pp.paManagementSiteID=pm.paManagementSiteID
Left Join exexposure ee ON ee.cacaseID=c.caCaseID
LEFT OUTER JOIN exExposureRoute eer ON eer.caCaseID=c.caCaseID
LEFT OUTER JOIN exRoute er ON er.exRouteID=eer.exRouteID
WHERE c.caCallTypeID =0
AND c.Startdate between '1/1/2006' and '12/1/2006'
AND (ee.exMedicalOutcomeID=4 OR ee.exMedicalOutcomeID=10)
AND pp.paSpeciesID=1
AND c.PublicID_adOrganization_secondary is null
declare @.1 int,@.2 int,@.3 int,@.4 int,@.5 int,@.6 int,@.7 int,@.8 int,
@.9 int,@.10 int,@.11 int,@.12 int,@.21 int,@.22 int,@.23 int,@.24 int,@.25 int,
@.26 int,@.27 int,@.28 int,@.29 int,@.30 int,@.31 int,@.32 int
set @.21=(select count(*) from j9a where Ingestion=1)
set @.22=(select count(*) from j9a where Inhalation=1)
set @.23=(select count(*) from j9a where Aspiration=1)
set @.24=(select count(*) from j9a where Ocular=1)
set @.25=(select count(*) from j9a where Dermal=1)
set @.26=(select count(*) from j9a where Bite=1)
set @.27=(select count(*) from j9a where Parenteral=1)
set @.28=(select count(*) from j9a where Otic=1)
set @.29=(select count(*) from j9a where Rectal=1)
set @.30=(select count(*) from j9a where Vaginal=1)
set @.31=(select count(*) from j9a where Other=1)
set @.32=(select count(*) from j9a where Unknown=1)
The exRoute result is like this
70 Ingestion
71 Inhalation/nasal
72 Aspiration (with ingestion)
73 Ocular
74 Dermal
75 Bite/sting
76 Parenteral
77 Other
78 Unknown
524 Otic
525 Rectal
526 Vaginal
The above giving the errors Msg 207, Level 16, State 1, Line 19
Invalid column name 'Route_Ingestion'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'Route_Inhalation'
How to create table j9a.j9a has the columns from select
Thanks in advance
Are the columns [Route_Ingestion] and [Route_Inhalation] in the table exRoute?
|||

Hi,

I modified the post,can you please look at that

|||

With your lastest modification, do you still get the same error?

It would be helpful if you were to post the entire set of code. Your error message indicates that somewhere in the code you are requesting something to do with columns named [Route_Ingestion] and [Route_Inhalation]. Perhaps there is an error with the column names somewhere, but without being able to see the entire bit of code, it is impossible to help you.

Msg 207, Level 16, State 1, Line 19
Invalid column name 'Route_Ingestion'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'Route_Inhalation'

|||

Hi,

My complete code is

drop table j9a

SELECT er.* into j9a

FROM caCase c

LEFT OUTER JOIN paPatient pp ON c.caCaseID=pp.caCaseID

Left Outer JOIN paManagementSite pm ON pp.paManagementSiteID=pm.paManagementSiteID

Left Join exexposure ee ON ee.cacaseID=c.caCaseID

LEFT OUTER JOIN exExposureRoute eer ON eer.caCaseID=c.caCaseID

LEFT OUTER JOIN exRoute er ON er.exRouteID=eer.exRouteID

WHERE c.caCallTypeID =0

AND c.Startdate between '1/1/2006' and '12/1/2006'

AND (ee.exMedicalOutcomeID=4 OR ee.exMedicalOutcomeID=10)

AND pp.paSpeciesID=1

AND c.PublicID_adOrganization_secondary is null

declare @.1 int,@.2 int,@.3 int,@.4 int,@.5 int,@.6 int,@.7 int,@.8 int,

@.9 int,@.10 int,@.11 int,@.12 int,@.21 int,@.22 int,@.23 int,@.24 int,@.25 int,

@.26 int,@.27 int,@.28 int,@.29 int,@.30 int,@.31 int,@.32 int

set @.21=(select count(*) from j9a where Ingestion=1)

set @.22=(select count(*) from j9a where Inhalation=1)

set @.23=(select count(*) from j9a where Aspiration=1)

set @.24=(select count(*) from j9a where Ocular=1)

set @.25=(select count(*) from j9a where Dermal=1)

set @.26=(select count(*) from j9a where Bite=1)

set @.27=(select count(*) from j9a where Parenteral=1)

set @.28=(select count(*) from j9a where Otic=1)

set @.29=(select count(*) from j9a where Rectal=1)

set @.30=(select count(*) from j9a where Vaginal=1)

set @.31=(select count(*) from j9a where Other=1)

set @.32=(select count(*) from j9a where Unknown=1)

Create table table9(Route varchar(30),Fatal int)

insert into table9 values ('Ingestion',@.1,@.21)

insert into table9 values ('Inhalation',@.2,@.22)

insert into table9 values ('Aspiration',@.3,@.23)

insert into table9 values ('Ocular',@.4,@.24)

insert into table9 values ('Dermal',@.5,@.25)

insert into table9 values ('Bite',@.6,@.26)

insert into table9 values ('Parenteral',@.7,@.27)

insert into table9 values ('Otic',@.8,@.28)

insert into table9 values ('Rectal',@.9,@.29)

insert into table9 values ('Vaginal',@.10,@.30)

insert into table9 values ('Other',@.11,@.31)

insert into table9 values ('Unknown',@.12,@.32)

select * from table9

and Select exrouteid,name from exRoute;

Will give results like this

70 Ingestion
71 Inhalation
72 Aspiration 73 Ocular
74 Dermal
75 Bite/sting
76 Parenteral
77 Other
78 Unknown
524 Otic
525 Rectal
526 Vaginal

But still iam getting errors Like

Invalid column name 'Ingestion'.

For all columns this error is coming

Thanks in advance

|||

There is a whole 'mess' of problems here.

First, Table j9a (which comes from exRount) contains does not appear to contain columns named 'Ingestion', 'Inhalation', etc. ( Select exrouteid,name from exRoute ) Those are values stored in the column named 'Name'. Therefore all of the variable assignments using SELECT will fail since they are requesting data from a column named 'Ingestion', etc. ( set @.21=(select count(*) from j9a where Ingestion=1) )

Then you create a table [Table9], which has two (2) columns, (Create table table9(Route varchar(30),Fatal int)) and attempt to INSERT three values into that table. ( insert into table9 values ('Ingestion',@.1,@.21))

There may be additional problems, but I stopped lookiing at this point. Apparantly, you are attempting to so something a bit more complex that your current skills. (Nothing wrong with that, we all have to learn and progress.) But this code cannot work, and is most likely NOT the best way to create a solution.

I suggest that you post the TABLE DDL, some sample data in the form of INSERT Statements, and a description of what you are attempting to accomplish and perhaps we can help you. See this link for help in preparing the information we need in order to help you. http://www.aspfaq.com/5006

|||

Hi,

I found where the problem is.

I modified the stored procedure entirely.

Now iam getting the result

Thanks

create table from output of select sql

I want to create a table with the output of select statement. i.e. create
table tmpk as select *...
this gives an error
Incorrect syntax near the keyword 'as'.
when i am running query alone this gives the result fine.try
SELECT
field1, field 2 etc...
INTO tmpk
FROM
wherever
"Karn Tanwar" <karntanwer@.hotmail.com> wrote in message
news:OtJceVhVFHA.544@.TK2MSFTNGP15.phx.gbl...
> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>|||On Wed, 11 May 2005 16:21:36 +0530, Karn Tanwar wrote:

> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
You have to use EXEC, like this:
DECLARE @.var VARCHAR(8000)
SELECT @.var = 'CREATE TABLE ' + something
FROM somewhere
WHERE somecol = @.somevar
EXEC (@.var)
This is a crude example, but you get the point.
/Andrs Taylor|||Hi Karan
Try this way
SELECT * INTO tmpk FROM ...
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Karn Tanwar" wrote:

> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>|||Unfortunately in the SQL Server enviornment you cannot select into a table
like you can in other legacy enviornments like Foxpro but you can declare a
local temp table with the same field makeup as the select statement outputs
and then use and insert to get the data into a table. You might have to mes
s
with it for a little while before you get it right, but it is possible to ge
t
somewhat the same results.
Declare @.v_sql NVARCHAR(4000)
SELECT @.v_sql =
INSERT #test
select hard_id,
Type_id,
description,
speed,
amount,
cache,
brand
from TU_Hardware
EXEC sp_executesql @.v_sql
"Karn Tanwar" wrote:

> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>|||Correction.. you can do a select into statement but you cant do it the way
you were trying it.
If you go to masters table on any db and type the following:
select * from abc
It will give you an error because no table exist then type
select top 10 *
into abc
from sysobjects
You will have a new table in master db. You can do this with any table in
the from part of the statement.
Hope this helps..
"Karn Tanwar" wrote:

> I want to create a table with the output of select statement. i.e. create
> table tmpk as select *...
> this gives an error
> Incorrect syntax near the keyword 'as'.
> when i am running query alone this gives the result fine.
>
>

Sunday, March 25, 2012

create table from another table

In oracle i can do a 'create table xyz as select * from abc'. How do i
accomplish this with sqlserver?
--
Don> In oracle i can do a 'create table xyz as select * from abc'. How do i
> accomplish this with sqlserver?
SELECT *
INTO xyz
FROM abc
Hope this helps.
Dan Guzman
SQL Server MVP

create table from another table

Hello,

under MSSQL8 doing this "create table tblA as ( select Name from tblB );"
results in error 156 (wrong syntax near AS keyword). What is my mistake?

thanks and regards
MarkSee SELECT in Books Online:

select Name
into dbo.tblA
from dbo.tblB

Simon

Create Table

Can I create a table from another table? If so how. In Oracle the syntax is:
Create table new_table as select * from table
ThanksSELECT * INTONewTable FROM OldTable|||I thought it was that easy. Thanks.|||And if you only want the structure, not the data

select * into newtable from oldtable where 0=1

HTH|||be aware thet using "select * into new table From Table" will create the same structure without indexes defaults and identity fields|||Thanks.sql

Thursday, March 22, 2012

Create Store Procedure to Paging!

Hi All!

I have Store Procedure:

If exists(Select * From sysobjects Where Name like 'Forum_Topic_SelectFromForum')
Drop Procedure Forum_Topic_SelectFromForum
go
CREATE PROCEDURE Forum_Topic_SelectFromForum
(
@.ForumID varchar(10)
)

AS BEGIN TRANSACTION
SELECT * from Forum_Topic whereForumID=@.ForumID Order by Tmp DESC
IF @.@.ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

Now, I want to Add 2 Variables: @.Offset int, @.Count int . With @.Offset: the point of data, @.Count: sum of row will get.

when get data I want it get from @.Offset to Added @.Count.

Help me to rewrite this store procedure. Thanks

Hi duynnh,

Are you using SQL 2005? That makes it really easy. Seethis blog post for an example of how you can use a CTE and the new Row_Number() function.

If you're not using 2005 you can do it by creating dynamic SQL. Check outthis post for a generic 'returnpage' stored procedure. Other options include using a temp table or possibly some trickery involving set rowcount if your schema works nicely with that method. Seehere for a more thorough examination of your options.

I hope that helps.

|||

Try the code below (assuming you are using sql 2005/express). I've tested in my side, it works fine

CREATE PROCEDURE Forum_Topic_SelectFromForum( @.ForumID varchar(10), @.offsetint, @.countint)AS BEGIN TRANSACTIONselect * from(SELECT *,row_number() over( Order by Tmp DESC)as row from Forum_Topic where ForumID=@.ForumID )as testwhere test.row between @.offset and @.offset+@.countIF @.@.ERROR <> 0 ROLLBACK TRANSACTIONELSE COMMIT TRANSACTION

Wednesday, March 21, 2012

Create schema if not exists - problems

Hi,
I have a problem executing this statement on sqlserver2005:[vbcol=seagreen]
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
create schema TestTest AUTHORIZATION testuser;
go
<<<
I get a "Incorrect syntax near the keyword 'schema'", so what am i missing?!
TIA, Troy
Troy,
CREATE SCHEMA must be the first statement in a batch, but the IF is the
first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
are thinking about that.)
Here is a workaround, which is a simple bit of dynamic SQL:
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
EXEC ('create schema TestTest AUTHORIZATION testuser');
go
RLF
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
> Hi,
> I have a problem executing this statement on sqlserver2005:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> create schema TestTest AUTHORIZATION testuser;
> go
> <<<
> I get a "Incorrect syntax near the keyword 'schema'", so what am i
> missing?!
> TIA, Troy
|||Worked like a charm - Thanks

"Russell Fields" wrote:

> Troy,
> CREATE SCHEMA must be the first statement in a batch, but the IF is the
> first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
> are thinking about that.)
> Here is a workaround, which is a simple bit of dynamic SQL:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> EXEC ('create schema TestTest AUTHORIZATION testuser');
> go
> RLF
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
>
>

Create schema if not exists - problems

Hi,
I have a problem executing this statement on sqlserver2005:
>>
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
create schema TestTest AUTHORIZATION testuser;
go
<<<
I get a "Incorrect syntax near the keyword 'schema'", so what am i missing?!
TIA, TroyTroy,
CREATE SCHEMA must be the first statement in a batch, but the IF is the
first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
are thinking about that.)
Here is a workaround, which is a simple bit of dynamic SQL:
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
EXEC ('create schema TestTest AUTHORIZATION testuser');
go
RLF
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
> Hi,
> I have a problem executing this statement on sqlserver2005:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> create schema TestTest AUTHORIZATION testuser;
> go
> <<<
> I get a "Incorrect syntax near the keyword 'schema'", so what am i
> missing?!
> TIA, Troy|||Worked like a charm - Thanks :)
"Russell Fields" wrote:
> Troy,
> CREATE SCHEMA must be the first statement in a batch, but the IF is the
> first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
> are thinking about that.)
> Here is a workaround, which is a simple bit of dynamic SQL:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> EXEC ('create schema TestTest AUTHORIZATION testuser');
> go
> RLF
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
> > Hi,
> >
> > I have a problem executing this statement on sqlserver2005:
> >>
> > if not exists(select 1 from information_schema.schemata where
> > schema_name='testtest')
> > create schema TestTest AUTHORIZATION testuser;
> > go
> > <<<
> >
> > I get a "Incorrect syntax near the keyword 'schema'", so what am i
> > missing?!
> >
> > TIA, Troy
>
>sql

Create SCHEMA - Basic Question - 2005

I am trying to execute the following T-Sql snippet and it gives an
error:
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ExpData')
CREATE SCHEMA [ExpData] AUTHORIZATION [dbo]
Error = Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'SCHEMA'.
I can't for the life of me work out what's worng. Can someone point me
in the right direction please? Thanks.'create schema' must the be the first line in a batch. here is the
workaround.
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ExpData')
Exec('CREATE SCHEMA [ExpData] AUTHORIZATION [dbo]')
-oj
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1150474587.056097.296010@.c74g2000cwc.googlegroups.com...
>
> I am trying to execute the following T-Sql snippet and it gives an
> error:
> IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ExpData')
> CREATE SCHEMA [ExpData] AUTHORIZATION [dbo]
> Error = Msg 156, Level 15, State 1, Line 26
> Incorrect syntax near the keyword 'SCHEMA'.
> I can't for the life of me work out what's worng. Can someone point me
> in the right direction please? Thanks.
>|||> 'create schema' must the be the first line in a batch. here is the
> workaround.
Wouldn't it be nice if the error message were similar to the one you get
when you try CREATE PROCEDURE in the middle of a batch? e.g. why isn't this
error returned instead of incorrect syntax:
Msg 111, Level 15, State 1, Line 2
'CREATE/ALTER SCHEMA' must be the first statement in a query batch.
A|||yeah...you know how to send a bug/wish report, right. ;-)
-oj
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23V9N94WkGHA.3440@.TK2MSFTNGP02.phx.gbl...
> Wouldn't it be nice if the error message were similar to the one you get
> when you try CREATE PROCEDURE in the middle of a batch? e.g. why isn't
> this error returned instead of incorrect syntax:
> Msg 111, Level 15, State 1, Line 2
> 'CREATE/ALTER SCHEMA' must be the first statement in a query batch.
>
> A
>sql

CREATE SCHEMA

CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
According docs, "ross" must be a valid security account in the database,
however I can type in any random string and SQL Server 2005 accepts it nicel
y
reporting "Command(s) completed successfully."
When scripting the database the text "ross" appears nowhere.
What is the purpose of the above Create statement?Hans,
I'm not really clear what you're trying, but the syntax you posted
doesn't make any sense.
In the CREATE SCHEMA statement there's no schema name and then you try
to grant select permissions on a view before you actually create the
view. Unfortunately executing a CREATE SCHEMA statement without a
schema name doesn't cause an error, but simply doesn't do anything.
About "Ross" , it's the database user who becomes owner of the schema.
If you try to execute the following staement in a database with no user
Markus you will receive an error.
CREATE SCHEMA mySchema AUTHORIZATION markus
Markus|||Hi Markus,
thanx for the quick reply.
CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
Above statements come right from BOL. The index shows two entries for CREATE
SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn'
t
make sense to me either, but since SQL2K5 mentioned successful completion I
was curious what was actually happening or that I missed something. If
"ross", or any random string for that matter, is not a user in the database
the create schema statement succeeds nonetheless.
Guess it has put me on the wrong leg...
"MarkusB" wrote:

> Hans,
> I'm not really clear what you're trying, but the syntax you posted
> doesn't make any sense.
> In the CREATE SCHEMA statement there's no schema name and then you try
> to grant select permissions on a view before you actually create the
> view. Unfortunately executing a CREATE SCHEMA statement without a
> schema name doesn't cause an error, but simply doesn't do anything.
> About "Ross" , it's the database user who becomes owner of the schema.
> If you try to execute the following staement in a database with no user
> Markus you will receive an error.
> CREATE SCHEMA mySchema AUTHORIZATION markus
> Markus
>|||Solved my own problem ;-)
In BOL, if you don't define a filter (i.e. "unfiltered"), chances are you
are directed to entries other than SQL2K5 docs. "CREATE SCHEMA" pointed me t
o
SQL2K, and "CREATE SCHEMA statement" to SQL2K5.
"Hans" wrote:
[vbcol=seagreen]
> Hi Markus,
> thanx for the quick reply.
> CREATE SCHEMA AUTHORIZATION ross
> GRANT SELECT on v1 TO public
> CREATE VIEW v1(c1) AS SELECT c1 from t1
> CREATE TABLE t1(c1 int)
> Above statements come right from BOL. The index shows two entries for CREA
TE
> SCHEMA, one WITH a schema name as you mentioned, and the one above. It did
n't
> make sense to me either, but since SQL2K5 mentioned successful completion
I
> was curious what was actually happening or that I missed something. If
> "ross", or any random string for that matter, is not a user in the databas
e
> the create schema statement succeeds nonetheless.
> Guess it has put me on the wrong leg...
> "MarkusB" wrote:
>

CREATE SCHEMA

CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
According docs, "ross" must be a valid security account in the database,
however I can type in any random string and SQL Server 2005 accepts it nicely
reporting "Command(s) completed successfully."
When scripting the database the text "ross" appears nowhere.
What is the purpose of the above Create statement?
Hans,
I'm not really clear what you're trying, but the syntax you posted
doesn't make any sense.
In the CREATE SCHEMA statement there's no schema name and then you try
to grant select permissions on a view before you actually create the
view. Unfortunately executing a CREATE SCHEMA statement without a
schema name doesn't cause an error, but simply doesn't do anything.
About "Ross" , it's the database user who becomes owner of the schema.
If you try to execute the following staement in a database with no user
Markus you will receive an error.
CREATE SCHEMA mySchema AUTHORIZATION markus
Markus
|||Hi Markus,
thanx for the quick reply.
CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
Above statements come right from BOL. The index shows two entries for CREATE
SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn't
make sense to me either, but since SQL2K5 mentioned successful completion I
was curious what was actually happening or that I missed something. If
"ross", or any random string for that matter, is not a user in the database
the create schema statement succeeds nonetheless.
Guess it has put me on the wrong leg...
"MarkusB" wrote:

> Hans,
> I'm not really clear what you're trying, but the syntax you posted
> doesn't make any sense.
> In the CREATE SCHEMA statement there's no schema name and then you try
> to grant select permissions on a view before you actually create the
> view. Unfortunately executing a CREATE SCHEMA statement without a
> schema name doesn't cause an error, but simply doesn't do anything.
> About "Ross" , it's the database user who becomes owner of the schema.
> If you try to execute the following staement in a database with no user
> Markus you will receive an error.
> CREATE SCHEMA mySchema AUTHORIZATION markus
> Markus
>
|||Solved my own problem ;-)
In BOL, if you don't define a filter (i.e. "unfiltered"), chances are you
are directed to entries other than SQL2K5 docs. "CREATE SCHEMA" pointed me to
SQL2K, and "CREATE SCHEMA statement" to SQL2K5.
"Hans" wrote:
[vbcol=seagreen]
> Hi Markus,
> thanx for the quick reply.
> CREATE SCHEMA AUTHORIZATION ross
> GRANT SELECT on v1 TO public
> CREATE VIEW v1(c1) AS SELECT c1 from t1
> CREATE TABLE t1(c1 int)
> Above statements come right from BOL. The index shows two entries for CREATE
> SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn't
> make sense to me either, but since SQL2K5 mentioned successful completion I
> was curious what was actually happening or that I missed something. If
> "ross", or any random string for that matter, is not a user in the database
> the create schema statement succeeds nonetheless.
> Guess it has put me on the wrong leg...
> "MarkusB" wrote:

CREATE SCHEMA

CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
According docs, "ross" must be a valid security account in the database,
however I can type in any random string and SQL Server 2005 accepts it nicely
reporting "Command(s) completed successfully."
When scripting the database the text "ross" appears nowhere.
What is the purpose of the above Create statement?Hans,
I'm not really clear what you're trying, but the syntax you posted
doesn't make any sense.
In the CREATE SCHEMA statement there's no schema name and then you try
to grant select permissions on a view before you actually create the
view. Unfortunately executing a CREATE SCHEMA statement without a
schema name doesn't cause an error, but simply doesn't do anything.
About "Ross" , it's the database user who becomes owner of the schema.
If you try to execute the following staement in a database with no user
Markus you will receive an error.
CREATE SCHEMA mySchema AUTHORIZATION markus
Markus|||Hi Markus,
thanx for the quick reply.
CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
Above statements come right from BOL. The index shows two entries for CREATE
SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn't
make sense to me either, but since SQL2K5 mentioned successful completion I
was curious what was actually happening or that I missed something. If
"ross", or any random string for that matter, is not a user in the database
the create schema statement succeeds nonetheless.
Guess it has put me on the wrong leg...
"MarkusB" wrote:
> Hans,
> I'm not really clear what you're trying, but the syntax you posted
> doesn't make any sense.
> In the CREATE SCHEMA statement there's no schema name and then you try
> to grant select permissions on a view before you actually create the
> view. Unfortunately executing a CREATE SCHEMA statement without a
> schema name doesn't cause an error, but simply doesn't do anything.
> About "Ross" , it's the database user who becomes owner of the schema.
> If you try to execute the following staement in a database with no user
> Markus you will receive an error.
> CREATE SCHEMA mySchema AUTHORIZATION markus
> Markus
>|||Solved my own problem ;-)
In BOL, if you don't define a filter (i.e. "unfiltered"), chances are you
are directed to entries other than SQL2K5 docs. "CREATE SCHEMA" pointed me to
SQL2K, and "CREATE SCHEMA statement" to SQL2K5.
"Hans" wrote:
> Hi Markus,
> thanx for the quick reply.
> CREATE SCHEMA AUTHORIZATION ross
> GRANT SELECT on v1 TO public
> CREATE VIEW v1(c1) AS SELECT c1 from t1
> CREATE TABLE t1(c1 int)
> Above statements come right from BOL. The index shows two entries for CREATE
> SCHEMA, one WITH a schema name as you mentioned, and the one above. It didn't
> make sense to me either, but since SQL2K5 mentioned successful completion I
> was curious what was actually happening or that I missed something. If
> "ross", or any random string for that matter, is not a user in the database
> the create schema statement succeeds nonetheless.
> Guess it has put me on the wrong leg...
> "MarkusB" wrote:
> > Hans,
> >
> > I'm not really clear what you're trying, but the syntax you posted
> > doesn't make any sense.
> > In the CREATE SCHEMA statement there's no schema name and then you try
> > to grant select permissions on a view before you actually create the
> > view. Unfortunately executing a CREATE SCHEMA statement without a
> > schema name doesn't cause an error, but simply doesn't do anything.
> >
> > About "Ross" , it's the database user who becomes owner of the schema.
> > If you try to execute the following staement in a database with no user
> > Markus you will receive an error.
> > CREATE SCHEMA mySchema AUTHORIZATION markus
> >
> > Markus
> >
> >

Sunday, March 11, 2012

Create procedure error on computed column.

I have the following script that was generated using SMO:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_InsertCaseNote]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[proc_InsertCaseNote]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Erin D. Rowley

-- Create date:

-- Description:

-- =============================================

CREATE PROCEDURE [dbo].[proc_InsertCaseNote]

-- Add the parameters for the stored procedure here

@.ReasonCodeSubCategoryID int,

@.OrderGroupID uniqueidentifier,

@.NoteText text,

@.CustomerEmail varchar(75),

@.EmployeeFirstName varchar(255),

@.EmployeeLastName varchar(255)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

insert into CaseNotes (ReasonCodeSubCategoryID, OrderGroupID, NoteText, CustomerEmail, EmployeeFirstName, EmployeeLastName, DateCreated)

values (@.ReasonCodeSubCategoryID, @.OrderGroupID, @.NoteText, @.CustomerEmail, @.EmployeeFirstName, @.EmployeeLastName, GetDate())

return @.@.IDENTITY

END

But when I try to run it (in SQL Management Studio) I get the following error:

Msg 271, Level 16, State 1, Procedure proc_InsertCaseNote, Line 18

The column "DateCreated" cannot be modified because it is either a computed column or is the result of a UNION operator.

Any ideas on how to debug this problem?

Thank you.

Kevin

Please post the table DDL.|||

It seems really odd that it DateCreated would be a computed column, but I would also expect that you would know if it was a result of a Union

You can check to see if it is a computed column like this:

create table test
(
notComputed datetime,
computed as getdate()
)
go

select name, is_computed
from sys.columns
where object_id('dbo.test') = object_id
go

Returns:

name is_computed
- --
notComputed 0
computed 1

If you want to see the definition (and other good stuff) use sys.computed_columns:

select name, definition
from sys.computed_columns
where object_id('dbo.test') = object_id
and name = 'computed'

name definition
--
computed (getdate())

|||

Arnie Rowland wrote:
Please post the table DDL.

Sorry but I am not sure how to do this. The script that I am running is creating a stored procedure not a table that is why the error is so strange.

Kevin

|||

Louis Davidson wrote:

It seems really odd that it DateCreated would be a computed column, but I would also expect that you would know if it was a result of a Union

You can check to see if it is a computed column like this:

create table test
(
notComputed datetime,
computed as getdate()
)
go
select name, is_computed
from sys.columns
where object_id('dbo.test') = object_id
go

Returns:

name is_computed
- --
notComputed 0
computed 1

If you want to see the definition (and other good stuff) use sys.computed_columns:

select name, definition
from sys.computed_columns
where object_id('dbo.test') = object_id
and name = 'computed'

name definition
--
computed (getdate())

Thank you. The stored procedure is "automatically" filling in the data for this column through GetDate(). If you were to create a stored procedure and then try to install it on another computer what would your script look like? I am just relying on the script produced by SMO.

Kevin

|||

Right click the table in SSMS, click "Script table to..."

The error is not really all that strange, it is not letting your procedure do something that won't work.

|||

Without seeing the DDL for the table, this is hard to anwser. My guess is that this column was added to the table like this:

Alter table CaseNotes add DateCreated as (getdate())

This would make DateCreated be a computed column which is always set to the current date, not the date the row was inserted. This would not be what you want. If you don't have access to see the table structure for some reason, look at the data in the table and verify that the dates are not all the same. If they are all exactly the same, then you know this is the issue.

What you really want is for DateCreated to have a default of Getdate(), not be a computed column using this statement:

Alter table CaseNotes add DateCreated datetime default getdate()

-Tom

|||

Tom Werz wrote:

Without seeing the DDL for the table, this is hard to anwser. My guess is that this column was added to the table like this:

Alter table CaseNotes add DateCreated as (getdate())

This would make DateCreated be a computed column which is always set to the current date, not the date the row was inserted. This would not be what you want. If you don't have access to see the table structure for some reason, look at the data in the table and verify that the dates are not all the same. If they are all exactly the same, then you know this is the issue.

What you really want is for DateCreated to have a default of Getdate(), not be a computed column using this statement:

Alter table CaseNotes add DateCreated datetime default getdate()

-Tom

The table looks like:

/****** Object: Table [dbo].[CaseNotes] Script Date: 05/07/2007 20:49:37 ******/
CREATE TABLE [dbo].[CaseNotes](
[CaseNotesID] [int] IDENTITY(1,1) NOT NULL,
[ReasonCodeSubCategoryID] [int] NOT NULL,
[OrderGroupId] [uniqueidentifier] NOT NULL,
[NoteText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomerEmail] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeFirstName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeLastName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_CaseNotes] PRIMARY KEY CLUSTERED
(
[CaseNotesID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CaseNotes] WITH CHECK ADD CONSTRAINT [FK_CaseNotes_ReasonCodeSubCategory] FOREIGN KEY([ReasonCodeSubCategoryID])
REFERENCES [dbo].[ReasonCodeSubCategory] ([ReasonCodeSubCategoryID])
GO
ALTER TABLE [dbo].[CaseNotes] CHECK CONSTRAINT [FK_CaseNotes_ReasonCodeSubCategory]

The stored procedure is written so that when the row is added the DataCreated is set to the current date when the row is added. I am not sure if I understand what you are suggesting. Does this "create" script help? The stored procedure "works" as is. It seems that I am having a hard time creating a script to create it on another SQL server.

Reproduced here for reference.

USE [BuySeasons]
GO
/****** Object: StoredProcedure [dbo].[proc_InsertCaseNote] Script Date: 05/07/2007 20:54:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Erin D. Rowley
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[proc_InsertCaseNote]
-- Add the parameters for the stored procedure here
@.ReasonCodeSubCategoryID int,
@.OrderGroupID uniqueidentifier,
@.NoteText text,
@.CustomerEmail varchar(75),
@.EmployeeFirstName varchar(255),
@.EmployeeLastName varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
insert into CaseNotes (ReasonCodeSubCategoryID, OrderGroupID, NoteText, CustomerEmail, EmployeeFirstName, EmployeeLastName, DateCreated)
values (@.ReasonCodeSubCategoryID, @.OrderGroupID, @.NoteText, @.CustomerEmail, @.EmployeeFirstName, @.EmployeeLastName, GetDate())

return @.@.IDENTITY
END

Thank you for your suggestions.

Kevin

Create procedure

I try to create a very simple stored procedure,

CREATE PROCEDURE Importera_fil

@.fil varchar

INSERT INTO XMLFaktura(Faktura)

SELECT * FROM OPENROWSET

(Bulk '@.fil', SINGLE_CLOB) AS DocumentID

but I get an errormessage when I try to create it,

Msg 4860, Level 16, State 1, Procedure Importera_fil, Line 4

Cannot bulk load. The file "@.fil" does not exist.

So my question is, how can i create the procedure?

Thanks for any help

The parameters of most DDL statements, OPENROWSET, OPENQUERY etc cannot be passed as variables. You have to specify the file name using literals directly. This is a limitation in the language feature. So in order to use @.fil you need to use dynamic SQL to form the SELECT statement and execute it.

Create or Alter a procedure only when necessary

Hi,
I'm using scripts to create stored procedures...
The way I'm currently doing it is the following :
USE tempdb
GO
IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
DROP PROCEDURE test
GO
CREATE PROCEDURE test AS ...
I would like to use the CREATE PROCEDURE statement only if the
procedure does not exist and use ALTER PROCEDURE statement instead if
the procedure exists...
As CREATE PROCEDURE can not be combined with any other Transact-SQL
statement in a single batch, I was wondering if there were any way to
achieve something like this :
USE tempdb
GO
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
CREATE PROCEDURE test AS RETURN 0
GO
ALTER PROCEDURE test...
Thanks for your help
Patrick
On 2 mar, 20:48, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.info> wrote:
> PFI wrote:
> Unfortunately there is no 'CREATEOR REPLACE' statement in SQL Server.
> I think your idea tocreateaprocedureif it doesn't exist and thenalterit to desired form (instead of dropping and creating it) is quite
> reasonable.
> Of course your script has to be modified. I suggest you use something
> like this:
> IF OBJECT_ID('Procedure1') IS NULL
> EXEC ('CREATEPROCEDUREProcedure1 AS SELECT 1')
> GO
> ALTERPROCEDUREProcedure1
> AS
> BEGIN
> SELECT 2
> RETURN 0
> -- (..)
> END
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
Many thanks for this solution, it works perfectly and this is exactly
what I was looking for...

Create or Alter a procedure only when necessary

Hi,
I'm using scripts to create stored procedures...
The way I'm currently doing it is the following :
---
USE tempdb
GO
IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
DROP PROCEDURE test
GO
CREATE PROCEDURE test AS ...
----
I would like to use the CREATE PROCEDURE statement only if the
procedure does not exist and use ALTER PROCEDURE statement instead if
the procedure exists...
As CREATE PROCEDURE can not be combined with any other Transact-SQL
statement in a single batch, I was wondering if there were any way to
achieve something like this :
---
USE tempdb
GO
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
CREATE PROCEDURE test AS RETURN 0
GO
ALTER PROCEDURE test...
---
Thanks for your help
PatrickPFI wrote:
> the procedure exists...
> As CREATE PROCEDURE can not be combined with any other Transact-SQL
> statement in a single batch, I was wondering if there were any way to
> achieve something like this :
> (..)
Unfortunately there is no 'CREATE OR REPLACE' statement in SQL Server.
I think your idea to create a procedure if it doesn't exist and then
alter it to desired form (instead of dropping and creating it) is quite
reasonable.
Of course your script has to be modified. I suggest you use something
like this:
IF OBJECT_ID('Procedure1') IS NULL
EXEC ('CREATE PROCEDURE Procedure1 AS SELECT 1')
GO
ALTER PROCEDURE Procedure1
AS
BEGIN
SELECT 2
RETURN 0
-- (..)
END
Best regards,
Marcin Guzowski
http://guzowski.info|||On 2 mar, 20:48, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.info> wrote:
> PFI wrote:
> Unfortunately there is no 'CREATEOR REPLACE' statement in SQL Server.
> I think your idea tocreateaprocedureif it doesn't exist and thenalterit to
desired form (instead of dropping and creating it) is quite
> reasonable.
> Of course your script has to be modified. I suggest you use something
> like this:
> IF OBJECT_ID('Procedure1') IS NULL
> EXEC ('CREATEPROCEDUREProcedure1 AS SELECT 1')
> GO
> ALTERPROCEDUREProcedure1
> AS
> BEGIN
> SELECT 2
> RETURN 0
> -- (..)
> END
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
Many thanks for this solution, it works perfectly and this is exactly
what I was looking for...

Thursday, March 29, 2012

CREATE TABLE/VIEW from stored procedure or SELECT...

create table test

create table test

create table test

Tuesday, March 27, 2012

create table from select result

create table from output of select sql

Sunday, March 25, 2012

create table from another table

create table from another table

Create Table

Thursday, March 22, 2012

Create Store Procedure to Paging!

Wednesday, March 21, 2012

Create schema if not exists - problems

Create schema if not exists - problems

Create SCHEMA - Basic Question - 2005

CREATE SCHEMA

CREATE SCHEMA

CREATE SCHEMA

Sunday, March 11, 2012

Create procedure error on computed column.

Create procedure

Create or Alter a procedure only when necessary

Create or Alter a procedure only when necessary

Sql Create error

Blog Archive

About Me