Delete all content from all tables

I have a relational data structure where I need to delete all data from all
tables. However, constraints are defined on the tables, so I need to delete
from bottom to top to avoid referential constraints.
Do you guys have an idea of how to accomplish this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dkJesper
Who if not you shloud know the database structure
Take a look at OJ's script
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk|||Jesper,
Why don't you simply create the DDL scripts, drop the database, and create a
fresh one using the scripts? BTW, don't forget to bavkup the db first!
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in news:#G4PWDt3FHA.1396@.TK2MSFTNGP12.phx.gbl:

> Jesper,
> Why don't you simply create the DDL scripts, drop the database, and
> create a fresh one using the scripts? BTW, don't forget to bavkup the
> db first!
The reason is that I don't really need to delete all data in the tables -
just the data related to a specific id - that is used in all tables.
The id is a job id and this id is a part of all tables relating to this
job.
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk|||"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk
How about setting up your FK's as FK's ON DELETE CASCADE?
Would this not solve your problem?
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in
news:ukG0Bju3FHA.3952@.TK2MSFTNGP10.phx.gbl:

> "Jesper Stocholm" <j@.stocholm.invalid> wrote in message
> news:Xns970158D95CA57stocholmdk@.207.46.248.16...
[vbcol=seagreen]
> How about setting up your FK's as FK's ON DELETE CASCADE?
> Would this not solve your problem?
It would - if I needed to delete all data for a specific job. But this is
not what I need - I "just" need to delete the content in a number for
tables for a specific job. I do not want to delete the job entirely.
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>sql

Delete all content from all tables

I have a relational data structure where I need to delete all data from all
tables. However, constraints are defined on the tables, so I need to delete
from bottom to top to avoid referential constraints.
Do you guys have an idea of how to accomplish this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk
Jesper
Who if not you shloud know the database structure
Take a look at OJ's script
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk
|||Jesper,
Why don't you simply create the DDL scripts, drop the database, and create a
fresh one using the scripts? BTW, don't forget to bavkup the db first!
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk
|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote
in news:#G4PWDt3FHA.1396@.TK2MSFTNGP12.phx.gbl:

> Jesper,
> Why don't you simply create the DDL scripts, drop the database, and
> create a fresh one using the scripts? BTW, don't forget to bavkup the
> db first!
The reason is that I don't really need to delete all data in the tables -
just the data related to a specific id - that is used in all tables.
The id is a job id and this id is a part of all tables relating to this
job.
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk
|||"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk
How about setting up your FK's as FK's ON DELETE CASCADE?
Would this not solve your problem?
Rick Sawtell
MCT, MCSD, MCDBA
|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in
news:ukG0Bju3FHA.3952@.TK2MSFTNGP10.phx.gbl:
[vbcol=seagreen]
> "Jesper Stocholm" <j@.stocholm.invalid> wrote in message
> news:Xns970158D95CA57stocholmdk@.207.46.248.16...

> How about setting up your FK's as FK's ON DELETE CASCADE?
> Would this not solve your problem?
It would - if I needed to delete all data for a specific job. But this is
not what I need - I "just" need to delete the content in a number for
tables for a specific job. I do not want to delete the job entirely.
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>

Delete all content from all tables

I have a relational data structure where I need to delete all data from all
tables. However, constraints are defined on the tables, so I need to delete
from bottom to top to avoid referential constraints.
Do you guys have an idea of how to accomplish this?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
Findes din kiosk på nettet? Se http://ekiosk.dkJesper
Who if not you shloud know the database structure
Take a look at OJ's script
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk på nettet? Se http://ekiosk.dk|||Jesper,
Why don't you simply create the DDL scripts, drop the database, and create a
fresh one using the scripts? BTW, don't forget to bavkup the db first!
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk på nettet? Se http://ekiosk.dk|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in news:#G4PWDt3FHA.1396@.TK2MSFTNGP12.phx.gbl:
> Jesper,
> Why don't you simply create the DDL scripts, drop the database, and
> create a fresh one using the scripts? BTW, don't forget to bavkup the
> db first!
The reason is that I don't really need to delete all data in the tables -
just the data related to a specific id - that is used in all tables.
The id is a job id and this id is a part of all tables relating to this
job.
--
Jesper Stocholm
http://stocholm.dk
Findes din kiosk på nettet? Se http://ekiosk.dk|||"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk på nettet? Se http://ekiosk.dk
How about setting up your FK's as FK's ON DELETE CASCADE?
Would this not solve your problem?
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in
news:ukG0Bju3FHA.3952@.TK2MSFTNGP10.phx.gbl:
> "Jesper Stocholm" <j@.stocholm.invalid> wrote in message
> news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>>I have a relational data structure where I need to delete all data
>>from all
>> tables. However, constraints are defined on the tables, so I need to
>> delete from bottom to top to avoid referential constraints.
>> Do you guys have an idea of how to accomplish this?
> How about setting up your FK's as FK's ON DELETE CASCADE?
> Would this not solve your problem?
It would - if I needed to delete all data for a specific job. But this is
not what I need - I "just" need to delete the content in a number for
tables for a specific job. I do not want to delete the job entirely.
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>

defining background color in matrix

Hi All
In my report there is a dynamic matrix.
two section inside the data region need to be paint in two different
color.
i defined an Expression in the background properties.
the Expression define two ranges(four cordinates) in order to paint one
section
and more two ranges for the other section.
the problem occour while examine the data come from the DB. in those
junction
that there was no data involve the cell was'nt paint although it is in
the
range
of the painting.
i try to solve this by using the " is nothing" function but without any
successes.
one solution i had which is works was declaring temporary table int he
procedure ,addind rows to the temporary table plus the data it self
while
running
and then in the report to blank those cells by filter them.it work's
paint
those cells but damage the subtotal line(because new rows were adding
to the
table source).
if any knows how to solve this conflic i will very much appreciate
this.
thanks in advanced
Amir KrugliakHi
If this is reporting services then you may be better off posting to the
microsoft.public.sqlserver.reportingsvcs newsgroup.
John
"amir.krugliak@.gmail.com" wrote:

> Hi All
> In my report there is a dynamic matrix.
> two section inside the data region need to be paint in two different
> color.
> i defined an Expression in the background properties.
> the Expression define two ranges(four cordinates) in order to paint one
> section
> and more two ranges for the other section.
> the problem occour while examine the data come from the DB. in those
> junction
> that there was no data involve the cell was'nt paint although it is in
> the
> range
> of the painting.
> i try to solve this by using the " is nothing" function but without any
> successes.
> one solution i had which is works was declaring temporary table int he
> procedure ,addind rows to the temporary table plus the data it self
> while
> running
> and then in the report to blank those cells by filter them.it work's
> paint
> those cells but damage the subtotal line(because new rows were adding
> to the
> table source).
> if any knows how to solve this conflic i will very much appreciate
> this.
> thanks in advanced
> Amir Krugliak
>

default view for OLAP cube when first accessed

Hello,

I am using MS Analysis server for OLAP cubes and viewing these cubes using Cognos. What I want to do is to have defualt view defined for the particular cube whenever it is first accessed. I have been able to set the default month (i.e. current month). But in the default view I want data shown across multiple dimensions. E.g. on first view my client should be able to see data against dimensions Date and Product nested on y-axis and dimensions Destination and Time nested on x-axis.

Is it possible to achieve this using MDX query?

Any ideas will be greatly appreciated.

Thanks,

Akber

Hello!

I think that this can only be achieved if you have a client tool that supports writing MDX queries/selects. You cannot do it directly in AS2000 or SSAS2005, you will need a client. Cubes are not repositories for reports.

Since you are using Cognos you will have to rely of this supplier's tools for creating and saving reports.

HTH

Thomas Ivarsson

Default Values in Queries

Hello, I'm trying not to have NULL values in columns.
So I defined a default value ('n.d.') on a column named DDT.
How can i retrieve the rows with the default value in DDT column ?
I mean:
select OrderID, DateOfOrder
from
OrderTable
where
CustomerID=@.CustID
and
DDT is [DDT Column DEFAULT]
instead of :
select OrderID, DateOfOrder
from
OrderTable
where
CustomerID=@.CustID
and
DDT is NULL (or DDT='n.d.')
How does the query change if i define a Default Value DF_NULL char(4) = 'n.d
.'
and assign the defalut value of DDT column to DF_NULL ?
Thank you for help.
MicheleMichele wrote:
> Hello, I'm trying not to have NULL values in columns.
So, have you set the column to "NOT NULL"? That's the best way to prevent
NULLS from being stored in the column ...

> So I defined a default value ('n.d.') on a column named DDT.
> How can i retrieve the rows with the default value in DDT column ?
> I mean:
> select OrderID, DateOfOrder
> from
> OrderTable
> where
> CustomerID=@.CustID
> and
> DDT is [DDT Column DEFAULT]
> instead of :
> select OrderID, DateOfOrder
> from
> OrderTable
> where
> CustomerID=@.CustID
> and
> DDT is NULL (or DDT='n.d.')
> How does the query change if i define a Default Value DF_NULL char(4)
> = 'n.d.' and assign the defalut value of DDT column to DF_NULL ?
> Thank you for help.
> Michele
If you set the DDT column to "NOT NULL", then it will never contain NULL.
Are you saying that you won't know at runtime what the default value for DDT
is? Why wouldn't you just use:
WHERE DDT='n.d.'
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||> So, have you set the column to "NOT NULL"? That's the best way to prevent
> NULLS from being stored in the column ...
Yes the column DDT is set to NOT NULL.

> Are you saying that you won't know at runtime what the default value for D
DT
> is?
Yes.

>Why wouldn't you just use:
> WHERE DDT='n.d.'
Because for some NOT NULL columns the default is 'n.d.' for others is
'<unknown>' for others '000000', so I'd like to treat the default value of
the column as a parameter for the query (if possible), like a ... where DDT
is NULL (in case of DDT column NULL, but this is not the case)
Thank's.|||Michele wrote:
> Yes the column DDT is set to NOT NULL.
>
> Yes.
>
> Because for some NOT NULL columns the default is 'n.d.' for others is
> '<unknown>' for others '000000', so I'd like to treat the default
> value of the column as a parameter for the query (if possible), like
> a ... where DDT is NULL (in case of DDT column NULL, but this is not
> the case)
>
I've never attempted to do this (I always know what the default values are
in my columns ... <g,d&r> )
I suppose you could create a scaler udf that uses the sp_columns procedure,
or queries the INFORMATION_SCHEMA.Columns table, to retrieve the column's
default value.
WHERE DDT=fColDefault(DDT)
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Default values for a column - From a function

I am trying to set the Default value for a table column to a user defined function . Is this possible?

I have a Function that generates a RandomID as a string and returns that string. I would like that value in the Column to which I am calling the function as the default value.

Thanks

Jawahar

BOL 2000 : "Only a constant value, such as a character string; a system function, such as SYSTEM_USER(); or NULL can be used as a default"|||

Is it true for SQl Server 2005? If not, can you please point me to a sample of using UDF as a default value for a column?

Thanks in advance.

|||

You can use a user-defined function as default but it cannot take any parameters (meaning you can't pass the any column value). Additionally, this will get evaluated only once per statement (INSERT/UPDATE) not for every row.

So can you please explain what you are trying to do by calling a UDF with non-deterministic behavior?

|||

I see, thanks a lot. It actually was not my question, I just found this question on another forum and then I tried to make some research. When I found this thread I decided to post this question for clarification.

You're saying, that it would work, but it would be the same value for all inserted records, correct? Because I'm not sure if default value for the column comes into play on UPDATE.

Thanks again for your help and if you have any more information, I would appreciate it.

Default values for a column - From a function

I am trying to set the Default value for a table column to a user defined function . Is this possible?

I have a Function that generates a RandomID as a string and returns that string. I would like that value in the Column to which I am calling the function as the default value.

Thanks

Jawahar

BOL 2000 : "Only a constant value, such as a character string; a system function, such as SYSTEM_USER(); or NULL can be used as a default"|||

Is it true for SQl Server 2005? If not, can you please point me to a sample of using UDF as a default value for a column?

Thanks in advance.

|||

You can use a user-defined function as default but it cannot take any parameters (meaning you can't pass the any column value). Additionally, this will get evaluated only once per statement (INSERT/UPDATE) not for every row.

So can you please explain what you are trying to do by calling a UDF with non-deterministic behavior?

|||

I see, thanks a lot. It actually was not my question, I just found this question on another forum and then I tried to make some research. When I found this thread I decided to post this question for clarification.

You're saying, that it would work, but it would be the same value for all inserted records, correct? Because I'm not sure if default value for the column comes into play on UPDATE.

Thanks again for your help and if you have any more information, I would appreciate it.

Default values for a column - From a function

I am trying to set the Default value for a table column to a user defined function . Is this possible?

I have a Function that generates a RandomID as a string and returns that string. I would like that value in the Column to which I am calling the function as the default value.

Thanks

Jawahar

BOL 2000 : "Only a constant value, such as a character string; a system function, such as SYSTEM_USER(); or NULL can be used as a default"|||

Is it true for SQl Server 2005? If not, can you please point me to a sample of using UDF as a default value for a column?

Thanks in advance.

|||

You can use a user-defined function as default but it cannot take any parameters (meaning you can't pass the any column value). Additionally, this will get evaluated only once per statement (INSERT/UPDATE) not for every row.

So can you please explain what you are trying to do by calling a UDF with non-deterministic behavior?

|||

I see, thanks a lot. It actually was not my question, I just found this question on another forum and then I tried to make some research. When I found this thread I decided to post this question for clarification.

You're saying, that it would work, but it would be the same value for all inserted records, correct? Because I'm not sure if default value for the column comes into play on UPDATE.

Thanks again for your help and if you have any more information, I would appreciate it.

Default value for Datetime column

Hi
I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
My tables have several columns that are defined as datetime fields which
need to default to the empty date that I have chosen.
I type in 01/01/1753 as the default value of the table designer, however
when I insert a new line into any of the tables, the value that gets
inserted is 01/01/1900.
Does anyone know why this is happening? I am assuming that I have some
configuration value set incorrectly.
I realise that I could simply use 1900 as the empty date. I could also
include the field and a value in the insert statement, but that isn't the
point. I'd rather know why the system behaves like this.
Hope someone can help
Thanks in anticipationDATETIME is just that. It is not date or time, it is both. If you set the
default value of 1753-01-01 then it is 1753-01-01 12:00:00.000 AM if no
value is inserted. If you enter a date or a time, *that* value determines
the other part. If you enter a time only in Query Analyzer, you get
1900-01-01 hh:mm:ss, if you do it in Enterprise Manager, you get 1899-12-31
hh:mm:ss. A value you enter overrides the entire value, even if you only
specify a portion. You can't say the date is x if I enter a time of y. If
you want a specific date to go along with your time, enter that date as
well. You should handle this in a stored procedure so that it is
consistent.
http://www.aspfaq.com/2206
http://www.aspfaq.com/2455
"Mark_S" <Mark_S@.nospam.nospam> wrote in message
news:%23$GRC7$1FHA.2056@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
>
> My tables have several columns that are defined as datetime fields which
> need to default to the empty date that I have chosen.
>
> I type in 01/01/1753 as the default value of the table designer, however
> when I insert a new line into any of the tables, the value that gets
> inserted is 01/01/1900.
>
> Does anyone know why this is happening? I am assuming that I have some
> configuration value set incorrectly.
>
> I realise that I could simply use 1900 as the empty date. I could also
> include the field and a value in the insert statement, but that isn't the
> point. I'd rather know why the system behaves like this.
>
> Hope someone can help
>
> Thanks in anticipation
>|||>> I am using 01/01/1753 to define the "Empty Date" for my datetime fields [sic].
<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. One of the MANY differences in
fields and columns is that a field can be missing or empty while a
column cannot.
Next, the proper format for dates in SQL is '1753-01-01' as per the
ISO-11179 Standards.
That has to do with the internal representation of DATETIME data types
in SQL Server. And Great Britain and its colonies went to the
Gregorian (Common Era) calendar in 1752, nobody started on
'1753-01-01' as far as I can tell.
No, you cannot. That is an integer and not a temporal data element at
all. Temporal data types, like all other SQL data types, can have a
NULL value. You can also add a DEFAULT CURRENT_TIMESTAMP or
DEFAULT '1753-01-01' clause to the DDL.
Exactly what are you trying to do?|||> Exactly what are you trying to do?
Sounds like when he enters a "datetime" value, like '4:30 PM', he wants the
date that goes with that to be 1753-01-01.
A|||Mark_S (Mark_S@.nospam.nospam) writes:
> I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
Why not use NULL?
There are those who think NULL are evil, but just face it. Any in-band
value that is used to mean "no value" is going to cause problem. If
nothing else, there can be confusion about which that value is.
(Although I will have to admit having used 17530101, in one place in our
system. I had a need to distinguish between "it has always been this
way" (which is 17530101) and "it has never been this way" (which is NULL).)

> I type in 01/01/1753 as the default value of the table designer, however
> when I insert a new line into any of the tables, the value that gets
> inserted is 01/01/1900.
When you specify a string for a datetime value in SQL Server, all parts
have a default value which is applied. So an empty string, results in
1900-01-01 00.00.00:000.
Since you get this value, you are apparently explicitly specifying a value.
Had you specified a NULL value, you would have gotten a NULL (or a NOT
NULL violation). The default value is only used when nothing at all
is specified.
This script illustrates:
CREATE TABLE datedemo (a int NOT NULL,
d datetime NULL DEFAULT '17530101')
go
INSERT datedemo (a) VALUES (1)
INSERT datedemo (a, d) VALUES (2, NULL)
INSERT datedemo (a, d) VALUES (3, '')
INSERT datedemo (a, d) VALUES (4, '12:24:34')
go
SELECT * FROM datedemo ORDER BY a
go
DROP TABLE datedemo
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> I type in 01/01/1753 as the default value of the table designer
Without quotes, this is likely being interpreted as 1 divided by 1 divided
by 1753, which is going to round (dur to integer math) to 0, which is the
base date, 1900-01-01.
If you use a proper and unambiguous format for your date, you can avoid this
kind of thing. (And don't use the table designer.)
CREATE TABLE dbo.MyTable
(
i INT IDENTITY(1,1),
dt DATETIME NOT NULL DEFAULT '17530101'
);
GO
SET NOCOUNT ON;
INSERT dbo.MyTable DEFAULT VALUES;
INSERT dbo.MyTable(dt) SELECT '19000101';
INSERT dbo.MyTable(dt) SELECT '20050101';
INSERT dbo.MyTable(dt)
SELECT CONVERT(DATETIME, 01/01/1753);
GO
SELECT i,dt FROM dbo.MyTable ORDER BY i,dt;
GO
DROP TABLE dbo.MyTable;
GO|||The group is on a rampage for some odd reason.
Make sure you didn't accidentally define the data type as SMALLDATETIME,
which has a lower bound of '1900-01-01 00:00:00.000'.
Sincerely,
Anthony Thomas
"Mark_S" <Mark_S@.nospam.nospam> wrote in message
news:%23$GRC7$1FHA.2056@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
>
> My tables have several columns that are defined as datetime fields which
> need to default to the empty date that I have chosen.
>
> I type in 01/01/1753 as the default value of the table designer, however
> when I insert a new line into any of the tables, the value that gets
> inserted is 01/01/1900.
>
> Does anyone know why this is happening? I am assuming that I have some
> configuration value set incorrectly.
>
> I realise that I could simply use 1900 as the empty date. I could also
> include the field and a value in the insert statement, but that isn't the
> point. I'd rather know why the system behaves like this.
>
> Hope someone can help
>
> Thanks in anticipation
>

Default Value

I set up a database using Studio Express. I defined one row to be a date
field with a default value of 9999-12-31. When I go back and look at it it
looks like "(((9999)-(12))-(31))".
When I insert a row into this table and I have not specified a value for
this date field, it show a date of "1927-04-06 00:00:00.000". This does not
resemble anything which was suppose to have been defaulted. What am I doing
wrong?It works goos for me...
Try the below code:-
create table xxxx(i int, j datetime default '9999-12-31')
go
insert into xxxx(i) values(100)
go
select * from xxxx
Thanks
Hari
SQL Server MVP
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:68C415E4-058B-49B6-9A08-AFD78266E306@.microsoft.com...
>I set up a database using Studio Express. I defined one row to be a date
> field with a default value of 9999-12-31. When I go back and look at it
> it
> looks like "(((9999)-(12))-(31))".
> When I insert a row into this table and I have not specified a value for
> this date field, it show a date of "1927-04-06 00:00:00.000". This does
> not
> resemble anything which was suppose to have been defaulted. What am I
> doing
> wrong?|||The result of the following script:
SELECT CAST(9999-12-31 AS datetime)
is 1927-04-06 00:00:00.000 because SQL server takes the result of the
arithmetic expression (9956) and converts it to datetime. The result of:
SELECT CAST('9999-12-31' AS datetime)
is the expected '9999-12-31 00:00:00.000' because SQL parses the string and
properly converts it to datetime. However, many date format strings are
ambiguous and dependent on your dateformat settings. I suggest you specify
dates in 'yyyymmdd' format so that the value is properly interpreted
regardless of your dateformat setting. For example:
ALTER TABLE dbo.Table1
ADD CONSTRAINT DF_Table1_MyDatetime
DEFAULT '99991231' FOR MyDatetime
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:68C415E4-058B-49B6-9A08-AFD78266E306@.microsoft.com...
>I set up a database using Studio Express. I defined one row to be a date
> field with a default value of 9999-12-31. When I go back and look at it
> it
> looks like "(((9999)-(12))-(31))".
> When I insert a row into this table and I have not specified a value for
> this date field, it show a date of "1927-04-06 00:00:00.000". This does
> not
> resemble anything which was suppose to have been defaulted. What am I
> doing
> wrong?

Default Value

I set up a database using Studio Express. I defined one row to be a date
field with a default value of 9999-12-31. When I go back and look at it it
looks like "(((9999)-(12))-(31))".
When I insert a row into this table and I have not specified a value for
this date field, it show a date of "1927-04-06 00:00:00.000". This does not
resemble anything which was suppose to have been defaulted. What am I doing
wrong?It works goos for me...
Try the below code:-
create table xxxx(i int, j datetime default '9999-12-31')
go
insert into xxxx(i) values(100)
go
select * from xxxx
Thanks
Hari
SQL Server MVP
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:68C415E4-058B-49B6-9A08-AFD78266E306@.microsoft.com...
>I set up a database using Studio Express. I defined one row to be a date
> field with a default value of 9999-12-31. When I go back and look at it
> it
> looks like "(((9999)-(12))-(31))".
> When I insert a row into this table and I have not specified a value for
> this date field, it show a date of "1927-04-06 00:00:00.000". This does
> not
> resemble anything which was suppose to have been defaulted. What am I
> doing
> wrong?|||The result of the following script:
SELECT CAST(9999-12-31 AS datetime)
is 1927-04-06 00:00:00.000 because SQL server takes the result of the
arithmetic expression (9956) and converts it to datetime. The result of:
SELECT CAST('9999-12-31' AS datetime)
is the expected '9999-12-31 00:00:00.000' because SQL parses the string and
properly converts it to datetime. However, many date format strings are
ambiguous and dependent on your dateformat settings. I suggest you specify
dates in 'yyyymmdd' format so that the value is properly interpreted
regardless of your dateformat setting. For example:
ALTER TABLE dbo.Table1
ADD CONSTRAINT DF_Table1_MyDatetime
DEFAULT '99991231' FOR MyDatetime
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:68C415E4-058B-49B6-9A08-AFD78266E306@.microsoft.com...
>I set up a database using Studio Express. I defined one row to be a date
> field with a default value of 9999-12-31. When I go back and look at it
> it
> looks like "(((9999)-(12))-(31))".
> When I insert a row into this table and I have not specified a value for
> this date field, it show a date of "1927-04-06 00:00:00.000". This does
> not
> resemble anything which was suppose to have been defaulted. What am I
> doing
> wrong?

Default Template for User Defined Function

How can I restore the original User Defined Function Template? I made
an error and saved one of my UDFs as a template but would like the
original template restored?

TIAThanks. SQL User Defined Functions only allow you to save one
template. Do you know which one is the default installed by SQL Server
2000?|||(jhmosow@.gmail.com) writes:
> Thanks. SQL User Defined Functions only allow you to save one
> template. Do you know which one is the default installed by SQL Server
> 2000?

Not sure that I understand. I found three templates on my machine, which
I assume are the default templates. And it seems reasonable to me that
there are three templates, as there are three types of UDF. Does not
seem like a good idea to one have one template for all three.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 6 Jan 2006 05:54:42 -0800, jhmosow@.gmail.com wrote:

>Thanks. SQL User Defined Functions only allow you to save one
>template. Do you know which one is the default installed by SQL Server
>2000?

Hi jhmosow,

This is not correct. You can store all three (or even more, if you
desire).

The location where templates for user-defined fuinctions are stored
(asssuming that SQL Server is installed in the default directory) is
(warning, path may wrap)

C:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query
Analyzer\Create Function\*.tql

The "...\Create Function\" is a folder; the three .tql files Erland sent
you should be stored there.

--
Hugo Kornelis, SQL Server MVP

default schema for NT-group not possible

We have a new development SQL2005 server.
To implement secutiry on this new server we decided to grant rights to NT-groups.
We defined an NT-group Programmersmembers and gave this group the necessary rights.
The problem now is when a member of this NT-group creates a table (this group is dbo in the development database) everything works fine as expected.
But when a member of this group tries to make a new table through 'Microsoft SQL server management studio' they receive the error that there is no default schema available.
But it seems to be impossible to map a default schema to an NT-Group.

=> Does this means we have no other way than to give each individual programmer the necessary rights and assign them to a default schema.
It is no option to force our programmers to create each table through a script on the development database.

Or is there another option ?

For the general issue of setting a schema for a group, see this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=79418&SiteID=1.

If you have a limited number of operations that you want your users to perform, you could expose them through stored procedures that use the EXECUTE AS to run under a context with a well defined default schema. See http://msdn2.microsoft.com/en-us/library/ms188354.aspx for more information on EXECUTE AS.

Thanks
Laurentiu

|||

Setting a default schema for a group is not supported in SQL Server. The root cause of the problem in this case seems to be a bug in Microsoft SQL Server management studio that is already in our radar.

For more details, you can see the original discussion for this bug at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=79418&SiteID=1 .

I hope this information can help you.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Just to clarify the SQL Server Management Studio bug. If you create the table directly using TSQL, the operation should succeed and create an implicit user. Management Studio's bug is that it requires the user to be already created.

-Raul Garcia

SDE/T

SQL Server Engine

-

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Raul & Laurentiu

thx for the explination and the link to the other thread