Create Table

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

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

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

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

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

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

If you want table structure and data then

select * into newtable from oldtable

else

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

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
Ganesh
Hi
If I understand
http://msdn.microsoft.com/library/de.../sql2k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefinition. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:

> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements of
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
GaneshHi
If I understand
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefinition. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:
> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="">http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements of
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="">http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

CREATE SCHEMA COLLECTION

I am defining a schema as below that basically defines a custom type.
*************
create xml schema collection IntTypeDefinition as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="inttype">
<xs:restriction base="xs:positiveInteger"/>
</xs:simpleType>
</xs:schema>'
*************
No problem. However, when I create another schema that contains elements of
this type "IntTypeDefinition", then how would I say in that new schema to
refer this schema.? For example, the following command
******************
create xml schema collection Another_Schema_That_Uses_IntTypeDefiniti
on as
N'<?xml version="1.0" encoding="UTF-16" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="itemtype">
<xs:sequence>
<xs:element name="title" type="xs:positiveInteger"/>
<xs:element name="quantity" type="inttype"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'
********************************
fails as
Msg 2307, Level 16, State 1, Line 1
Reference to an undefined name 'inttype'
I do not want to redefine the type here in this schema also as that will
defeat my purpose.
Thanks,
GaneshHi
If I understand
http://msdn.microsoft.com/library/d...r />
k5xml.asp
correctly you need to specify a target namespace and reference it in the
definition of Another_Schema_That_Uses_IntTypeDefiniti
on. You code does not
seem to do either.
I am not sure why you are not creating both types in the same namespace.
John
"Ganesh Muthuvelu" wrote:

> I am defining a schema as below that basically defines a custom type.
> *************
> create xml schema collection IntTypeDefinition as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:simpleType name="inttype">
> <xs:restriction base="xs:positiveInteger"/>
> </xs:simpleType>
> </xs:schema>'
> *************
> No problem. However, when I create another schema that contains elements o
f
> this type "IntTypeDefinition", then how would I say in that new schema to
> refer this schema.? For example, the following command
> ******************
> create xml schema collection Another_Schema_That_Uses_IntTypeDefiniti
on as
> N'<?xml version="1.0" encoding="UTF-16" ?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="itemtype">
> <xs:sequence>
> <xs:element name="title" type="xs:positiveInteger"/>
> <xs:element name="quantity" type="inttype"/>
> </xs:sequence>
> </xs:complexType>
> </xs:schema>'
> ********************************
> fails as
> Msg 2307, Level 16, State 1, Line 1
> Reference to an undefined name 'inttype'
> I do not want to redefine the type here in this schema also as that will
> defeat my purpose.
> Thanks,
> Ganesh

CREATE GLOBAL CUBE in dataset MDX query.

Hello,
I am attempting to use a CREATE GLOBAL CUBE command when defining a data set in MSRS. The intent is to use the MSRS security and scheduling capabilities to dump local cube files from our data warehouse to a file share, then use MSRS's delivery mechanisms to deliver the cubes to the intended recipient(s). The problem (I believe) is that the command does not return any data, so when rendering the report I bound the dataset to, the server just stops responding. I assume it is waiting for data that will never come. The report itself only contains a text box with a string literal. The good news is that when I try to render the report, the local cube file is generated...I just need the server to finish rendering the "dummy" report. Any ideas? Thanks.
-Rob HoffmanRob, did you look at DTS as an alternative solution?
Reporting Services isn't really meant to be used in the way you're trying to
use it. One area of concern is that you mention the server stops
responding. It it only this particular report that doesn't get rendered, or
does the server stop responding to any request?
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Hoffman" <Rob Hoffman@.discussions.microsoft.com> wrote in message
news:6E2F8425-C095-4F36-9A40-C1CD6ADD73DD@.microsoft.com...
> Hello,
> I am attempting to use a CREATE GLOBAL CUBE command when defining a data
> set in MSRS. The intent is to use the MSRS security and scheduling
> capabilities to dump local cube files from our data warehouse to a file
> share, then use MSRS's delivery mechanisms to deliver the cubes to the
> intended recipient(s). The problem (I believe) is that the command does
> not return any data, so when rendering the report I bound the dataset to,
> the server just stops responding. I assume it is waiting for data that
> will never come. The report itself only contains a text box with a string
> literal. The good news is that when I try to render the report, the local
> cube file is generated...I just need the server to finish rendering the
> "dummy" report. Any ideas? Thanks.
> -Rob Hoffman|||Lukasz,
Yes, but RS looked like a better choice out of the box because of its built-in security model, job scheduling, delivery mechanisms and extensibility. The requirements of the project I'm working on make it an expedient alternative to writing code that allows users to define, schedule and deliver local OLAP cubes from our data warehouse source.
To answer your question, when I view (view tab) my dummy report in Report Manager, the report never renders, and my aspnet_wp process consumes all processing power on the server. When I open a second browser window and try to access Report Manager, the page never loads. It looks like there is some sort of infinite loop occurring. I have never observed a timeout, but I have waited several minutes for the process to complete. I ended up having to close the first browser window (stop rendering the dummy report) to get RS to return to normal operation. When I do that, the second browser window finishes rendering my Report Manager screen almost immediately. I'd appreciate any help you or anyone else can provide in resolving this.
-Rob
"Lukasz Pawlowski [MSFT]" wrote:
> Rob, did you look at DTS as an alternative solution?
> Reporting Services isn't really meant to be used in the way you're trying to
> use it. One area of concern is that you mention the server stops
> responding. It it only this particular report that doesn't get rendered, or
> does the server stop responding to any request?
> -Lukasz
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Rob Hoffman" <Rob Hoffman@.discussions.microsoft.com> wrote in message
> news:6E2F8425-C095-4F36-9A40-C1CD6ADD73DD@.microsoft.com...
> > Hello,
> >
> > I am attempting to use a CREATE GLOBAL CUBE command when defining a data
> > set in MSRS. The intent is to use the MSRS security and scheduling
> > capabilities to dump local cube files from our data warehouse to a file
> > share, then use MSRS's delivery mechanisms to deliver the cubes to the
> > intended recipient(s). The problem (I believe) is that the command does
> > not return any data, so when rendering the report I bound the dataset to,
> > the server just stops responding. I assume it is waiting for data that
> > will never come. The report itself only contains a text box with a string
> > literal. The good news is that when I try to render the report, the local
> > cube file is generated...I just need the server to finish rendering the
> > "dummy" report. Any ideas? Thanks.
> >
> > -Rob Hoffman
>
>