Creating a User Defined Aggregate Function

Does SQL Server allow the user to create user defined aggregate functions?
If so:
What doed the syntax look like?
Where can I find more information on creating user defined aggregate
functions?Sorry, I found the answer.
"Charles" wrote:

> Does SQL Server allow the user to create user defined aggregate functions?
> If so:
> What doed the syntax look like?
> Where can I find more information on creating user defined aggregate
> functions?
>|||In SQL 2005 user-defined functions can be created as CLR functions.
http://msdn2.microsoft.com/en-us/library/ms131051.aspx
ML
http://milambda.blogspot.com/
"Charles" wrote:

> Does SQL Server allow the user to create user defined aggregate functions?
> If so:
> What doed the syntax look like?
> Where can I find more information on creating user defined aggregate
> functions?
>

creating a new user for sp execution say sa (to whom i am in need of creating under my user defi

I am in need of creating a new user for stored procedures execution say sa (to whom i am in need of creating under my user defined db)

I am guessing you are trying to create an explicit user (database principal) for the login SA, correct?

SA and any other member of sysadmin will always be mapped on any database to DBO, even more, SA is a special principal and the DDL will prevent to create a user for it.

If you need this principal for EXECUTE AS in modules, I would recommend either creating a user without login or use digital signatures. You can find some samples on my blog and Laurentiu’s blog:

· http://blogs.msdn.com/raulga/

· http://blogs.msdn.com/lcris/

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Creating a database schema from a DTD?

Assuming a DTD would lead to a well defined schema, isn't
there a tool to map DTDs to relational schemas?
SQL Server does not support DTD since it gives not enough information about
types and cannot be easily annotated as the XML-based schema formats XDR and
W3C's XML Schema.
You can either use a tool to translate your DTD into one of the XML-based
schema formats or look for a third-party tool (I think Ron Bourret had one).
Best regards
Michael
"Bob Smith" <anonymous@.discussions.microsoft.com> wrote in message
news:167901c4e55b$3336f1c0$a601280a@.phx.gbl...
> Assuming a DTD would lead to a well defined schema, isn't
> there a tool to map DTDs to relational schemas?

Creating a database schema from a DTD?

Assuming a DTD would lead to a well defined schema, isn't
there a tool to map DTDs to relational schemas?SQL Server does not support DTD since it gives not enough information about
types and cannot be easily annotated as the XML-based schema formats XDR and
W3C's XML Schema.
You can either use a tool to translate your DTD into one of the XML-based
schema formats or look for a third-party tool (I think Ron Bourret had one).
Best regards
Michael
"Bob Smith" <anonymous@.discussions.microsoft.com> wrote in message
news:167901c4e55b$3336f1c0$a601280a@.phx.gbl...
> Assuming a DTD would lead to a well defined schema, isn't
> there a tool to map DTDs to relational schemas?

Creating a column as NOT NULL with "select into"

I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.Try:
create table t
(
ID int identity primary key
, x char (2) null
)
insert t (x) values ('XX')
insert t (x) values ('YY')
go
select
ID
, isnull (x, '--') as y
into
x
from
t
exec sp_help x
go
drop table t, x
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.|||TJ,
A general recommendation is to create the table using DDL.
CREATE TABLE NewTable
(col1 int,
col2 varchar(100)0
go
INSERT INTO NewTable SELECT col1, COALESCE(col2, '') FROM OldTable
go
SELECT INTO is best used for the temporary quick and dirty operation, not
for when you want something to stick around.
Russell Fields
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:##97VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||Your welcomes. ;-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23pCzI8iWEHA.2852@.TK2MSFTNGP12.phx.gbl...
That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>

Creating a column as NOT NULL with "select into"

I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.Try:
create table t
(
ID int identity primary key
, x char (2) null
)
insert t (x) values ('XX')
insert t (x) values ('YY')
go
select
ID
, isnull (x, '--') as y
into
x
from
t
exec sp_help x
go
drop table t, x
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.|||TJ,
A general recommendation is to create the table using DDL.
CREATE TABLE NewTable
(col1 int,
col2 varchar(100)0
go
INSERT INTO NewTable SELECT col1, COALESCE(col2, '') FROM OldTable
go
SELECT INTO is best used for the temporary quick and dirty operation, not
for when you want something to stick around.
Russell Fields
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:##97VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||Your welcomes. ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23pCzI8iWEHA.2852@.TK2MSFTNGP12.phx.gbl...
That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>

Creating a column as NOT NULL with "select into"

I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.
Try:
create table t
(
ID int identity primary key
, x char (2) null
)
insert t (x) values ('XX')
insert t (x) values ('YY')
go
select
ID
, isnull (x, '--') as y
into
x
from
t
exec sp_help x
go
drop table t, x
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.
|||TJ,
A general recommendation is to create the table using DDL.
CREATE TABLE NewTable
(col1 int,
col2 varchar(100)0
go
INSERT INTO NewTable SELECT col1, COALESCE(col2, '') FROM OldTable
go
SELECT INTO is best used for the temporary quick and dirty operation, not
for when you want something to stick around.
Russell Fields
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:##97VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>
|||That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>
|||Your welcomes. ;-)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23pCzI8iWEHA.2852@.TK2MSFTNGP12.phx.gbl...
That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>