Sql Server 2005: is there someplace to set the global database column type default ?? |
|
Author |
Message |
Hagge
|
Posted: Thu Nov 24 03:53:10 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
New to Sql Server.
Using SSIS packages to migrate flat file delimited data from our current
Unix DB into Sql Server tables.
When migrating integers and dates that have 'blank' or '0 length' values in
the delimited data, integers are appearing as 0 and dates are defaulting to
01/31/1899 (or some such).
Is there some place in Sql Server 2005 that I can tell the database to "for
all tables" set integer, date, decimal, string, etc types to NULL as the
'standard' default??
NULL is the rule in our databases rather than the exception.
When, on occassion, we need to have a column default to something other than
NULL we can define a default value for that specific nonstandard column.
i.e. I'd much rather deal with exceptions than have to specifically set
every column in our db tables to default to NULL.
Thanks in advance.
Barry
in Oregon
SQL Server99
|
|
|
|
|
v-sguo
|
Posted: Thu Nov 24 03:53:10 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Hello,
I have tested the issue on my side, but I didn't reproduce the issue. Can
you post here the detail steps to reproduce the issue?
For your reference, I tested it by performing the following steps:
1. Create a table in a test database which has a datetime column.
2. Enter a null volue into the datetime column.
3. Export the table to a flat file ex.txt using the SSIS export and import
wizard.
4. Import the table into anthter database from the ex.txt file using the
SSIS export and import wizard.
5. Verfiy the value in the table and it is null instead of 01/31/1899.
I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|
|
|
|
|
frostbb
|
Posted: Tue Nov 29 12:41:37 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Sophie,
Thanks for the response. It's very much appreciated.
I'm using a multi-step SSIS package to load the flat file into an existing
Sql Server table.
I'll try to determine where the bogus date is being generated.
Although the bogus date is simply a side issue to my main question about
being able to set 'global' defaults for column types within an SQL Server
2005 instance.
Thanks for any help you may be able to provide.
Barry
in Oregon
> Hello,
>
> I have tested the issue on my side, but I didn't reproduce the issue. Can
> you post here the detail steps to reproduce the issue?
>
> For your reference, I tested it by performing the following steps:
>
> 1. Create a table in a test database which has a datetime column.
> 2. Enter a null volue into the datetime column.
> 3. Export the table to a flat file ex.txt using the SSIS export and import
> wizard.
> 4. Import the table into anthter database from the ex.txt file using the
> SSIS export and import wizard.
> 5. Verfiy the value in the table and it is null instead of 01/31/1899.
>
> I look forward to hearing from you.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|
|
|
|
|
frostbb
|
Posted: Tue Nov 29 15:51:12 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
I've gone thru the SSIS package as best I can and the only thing I can see
is that the final OleDb Connector mapping step insists on mapping datetime
columns from the flatfile (I've set column types for the flat file as
appropriate) to timestamp type columns in the Sql Server table even though
the actual columns in the table are datetime type. I can't find a way to
'change the OleDb destination's automatic column mapping from timestamp to
datetime. Then again this may be a 'red herring' i.e. not the issue at
all.
Would it be possible to zip up the flat file, table create scripts and SSIS
package and send or ftp them to you ??
Thanks in advance.
Barry
in Oregon
> Hello,
>
> I have tested the issue on my side, but I didn't reproduce the issue. Can
> you post here the detail steps to reproduce the issue?
>
> For your reference, I tested it by performing the following steps:
>
> 1. Create a table in a test database which has a datetime column.
> 2. Enter a null volue into the datetime column.
> 3. Export the table to a flat file ex.txt using the SSIS export and import
> wizard.
> 4. Import the table into anthter database from the ex.txt file using the
> SSIS export and import wizard.
> 5. Verfiy the value in the table and it is null instead of 01/31/1899.
>
> I look forward to hearing from you.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|
|
|
|
|
v-sguo
|
Posted: Wed Nov 30 00:26:36 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Hello Barry,
I tested the issue and it works fine on my side.
> Is there some place in Sql Server 2005 that I can tell the database to
"for all tables" set integer, date, decimal, string, etc types to NULL as
the
'standard' default??
You can create a Default object and bind it to a column. When bound to a
column or an alias data type, a default specifies a value to be inserted
into the column to which the object is bound (or into all columns, in the
case of an alias data type), when no value is explicitly supplied during an
insert.
For example, the following sample code create a Default object mydf which
default value is null. Then bind the default object to a column:
--create a table
create table tt (c1 varchar(10),
c2 varchar(10),
c3 int)
--create a default object
create default mydf as null
--bind the default object to the column
sp_bindefault mydf , 'tt.c3'
sp_bindefault mydf , 'tt.c2'
--insert value into the table
insert into tt (c1,c2) values ('a','b')
insert into tt (c1) values ('a')
--verify that the null is the default value
select * from tt
For more information, refer to the "CREATE DEFAULT (Transact-SQL)" topic in
SQL server 2005 books online.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|
|
|
|
|
v-sguo
|
Posted: Wed Nov 30 00:47:57 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Hello,
1.
Based on my test, in the Flat File Source Editor (Connection Manager Page),
there is an option "Retain null values from the source as null values in
the data flow" which specifies whether to keep null values when data is
extracted. If the option is checked, the null value will be kept.
To open the Flat File Source Editor, right-click "Flat File Source" in Data
Flow tab in SSIS, click Edit.
For more information, refer to the "Flat File Source Editor (Connection
Manager Page)" topic in SQL server 2005 books online.
2.
There is a similar option in the OLE DB Destination Editor (Connection
Manager Page). The "Keep nulls" option specifies whether to copy null
values when data is loaded.
For more information, refer to the "OLE DB Destination Editor (Connection
Manager Page)" topic in SQL server 2005 books online.
3.
In the OLE DB Destination Editor (Connection Manager Page) window, you can
create the New button to create a new table. Then import data into the new
created table.
By doing so, you can map the datetime column from the flat file into the
new table.
For example, if the flat file looks like the followings:
datetime,idcol
11/20/2005,1
,
To import the data into a new table in the database, you can create a new
table as followings:
CREATE TABLE mytb(
[dd] datetime,
[idcol] int
)
This will create a new table and the data type of column dd is datetime.
If the issue still exists, you can send the related information to me for
subject as "31403906-- Sql Server 2005: is there someplace to set the
global database column type default ??"
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|
|
|
|
|
v-sguo
|
Posted: Wed Nov 30 01:09:56 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Hello,
"Data Conversion" can convert a column to different data type and add the
column to the dataset. You can use the "Data Conversion Transformation
Editor" dialog box to select the columns to convert, select the data type
to which the column is converted, and set conversion attributes.
DT_DBTIMESTAMP is a Integration Services Data Type. It is a timestamp
structure that consists of year, month, hour, minute, second, and fraction.
On my side, I select "Flat File Source" as data flow source, "Data
Conversion" as data flow transformations, "OLE DB Destination" as the data
flow destinations.
For more information, refer to the "Data Conversion Transformation Editor"
topic in SQL server 2005 books online.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|
|
|
|
|
frostbb
|
Posted: Wed Nov 30 11:09:10 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Sophie,
Again, thanks for your reply ... I think part of the problem here is that
I'm new to SSIS and I'm probably not using the components correctly ...
I created a test to illustrate the flat file blank date => OleDb destination
date import 12/30/1899 problem I'm seeing ...
I created the following test table ...
test_bbf
int_column (int, null)
date_column (datetime, null)
string_column ( varchar(50), null)
I created the following flat file (delimited w/ | )
1|11/1/2005|record nbr 1
2|11/2/2005|record nbr 2
3|11/3/2005|record nbr 3
4||record nbr 4 null date
5|11/4/2005|record nbr 5
6|11/5/2005|record nbr 6
I created a simple SSIS package w/ data flow - [flat file source] => [OleDb
Destination]
i.e. Flat File Source (using flat file above w/ columns identified as int,
datetime & string)
I piped the Flat File Source into an OleDb Destination (pointing to the
test_bbf table)
The results of the import run are as follows ...
int_column | date_column | string_column
-----------------------------------------------------------------
1 | 11/1/2005 12:00:00 AM | record nbr 1
-----------------------------------------------------------------
2 | 11/2/2005 12:00:00 AM | record nbr 2
-----------------------------------------------------------------
3 | 11/3/2005 12:00:00 AM | record nbr 3
-----------------------------------------------------------------
4 | 12/30/1899 12:00:00 AM | record nbr 4 null date <=
bogus date inserted
-----------------------------------------------------------------
5 | 11/4/2005 12:00:00 AM | record nbr 5
-----------------------------------------------------------------
6 | 11/5/2005 12:00:00 AM | record nbr 6
-----------------------------------------------------------------
Null | Null | Null
You should be able to easily duplicate the substitution of the 12/30/1899
12:00:00AM date for the blank date in the input flat file. Works for me
every time.
Thanks for sticking with this problem.
Barry
in Oregon
> Hello,
>
> 1.
>
> Based on my test, in the Flat File Source Editor (Connection Manager
> Page),
> there is an option "Retain null values from the source as null values in
> the data flow" which specifies whether to keep null values when data is
> extracted. If the option is checked, the null value will be kept.
>
> To open the Flat File Source Editor, right-click "Flat File Source" in
> Data
> Flow tab in SSIS, click Edit.
>
> For more information, refer to the "Flat File Source Editor (Connection
> Manager Page)" topic in SQL server 2005 books online.
>
> 2.
> There is a similar option in the OLE DB Destination Editor (Connection
> Manager Page). The "Keep nulls" option specifies whether to copy null
> values when data is loaded.
>
> For more information, refer to the "OLE DB Destination Editor (Connection
> Manager Page)" topic in SQL server 2005 books online.
>
> 3.
> In the OLE DB Destination Editor (Connection Manager Page) window, you can
> create the New button to create a new table. Then import data into the new
> created table.
>
> By doing so, you can map the datetime column from the flat file into the
> new table.
>
>
> For example, if the flat file looks like the followings:
>
> datetime,idcol
> 11/20/2005,1
> ,
>
> To import the data into a new table in the database, you can create a new
> table as followings:
>
> CREATE TABLE mytb(
> [dd] datetime,
> [idcol] int
> )
>
> This will create a new table and the data type of column dd is datetime.
>
> If the issue still exists, you can send the related information to me for
> subject as "31403906-- Sql Server 2005: is there someplace to set the
> global database column type default ??"
>
> I hope the information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>
>
>
>
>
|
|
|
|
|
frostbb
|
Posted: Wed Nov 30 11:22:38 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Sophie,
I think we've now got 2 threads going here ... one concerning column
defaults and one concerning the insertion of bogus12/30/1899 dates. I
addressed the date issue in a 2nd post with an example. Hope it helps to
show what I'm running into.
With respect to Column defaults ... I'm aware that individual columns can be
assigned a default using CREATE TABLE or ALTER TABLE.
What I was really hoping to find out is if there's someway to change the
SYSTEM default for column types at the database level. For instance the
SYSTEM default for timestamp type seems to be 12/30/1899 12:00:00 AM. If
there is no 'user defined' default set on the column using CREATE or ALTER
TABLE then the SYSTEM (or db instance column type) default seems to be the
value of last resort. This system default is what I would like to change
for various column types.
I suspect that changing the SYTEM column type default is not possible, but
it never hurts to ask.
As always, thank you for you prompt reply and sticking with this problem.
I know its difficult to deal with new Sql Server users like me on a steep /
frantic learning curve. Unfortunately, there never seems to be time
anymore to properly learn a software package before we're neck deep in a
huge development project. For the last 7 years it seems I've been in the
mode of breaking the shrink wrap and starting immediately on an enterprise
size development project.
Barry
in Oregon
> Hello Barry,
>
> I tested the issue and it works fine on my side.
>
>> Is there some place in Sql Server 2005 that I can tell the database to
> "for all tables" set integer, date, decimal, string, etc types to NULL as
> the
> 'standard' default??
>
> You can create a Default object and bind it to a column. When bound to a
> column or an alias data type, a default specifies a value to be inserted
> into the column to which the object is bound (or into all columns, in the
> case of an alias data type), when no value is explicitly supplied during
> an
> insert.
>
> For example, the following sample code create a Default object mydf which
> default value is null. Then bind the default object to a column:
>
> --create a table
>
> create table tt (c1 varchar(10),
> c2 varchar(10),
> c3 int)
>
> --create a default object
> create default mydf as null
>
> --bind the default object to the column
> sp_bindefault mydf , 'tt.c3'
>
> sp_bindefault mydf , 'tt.c2'
>
> --insert value into the table
> insert into tt (c1,c2) values ('a','b')
> insert into tt (c1) values ('a')
>
> --verify that the null is the default value
>
> select * from tt
>
> For more information, refer to the "CREATE DEFAULT (Transact-SQL)" topic
> in
> SQL server 2005 books online.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>
|
|
|
|
|
frostbb
|
Posted: Wed Nov 30 15:29:41 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Sophie,
I played with the [Data Conversion Transformation] tool but it seemed to be
of little help. [Data Conversion Transformation] sees the date field
comming out of the [flat file source] as a DT_DATE type ... which is
correct. The bogus date seems to appear somewhere between the [OleDb
Destination] control and the datetime columns in the target sqlserver table.
I ran the import data wizard on the same sample data and sql table I
privided in my earlier post and the file import wizard also placed the
bogus 12/30/1899 date value into the sql server table when the matching
field in the flat file was blank. I need NULL to be inserted into the
database whenever there is missing date, integer, decimal or any other type
in the flat file source.
Is there anyway to get SSIS to place NULLs into the sql server table when
there is missing (blank) data in the flat file import table ??
Thanks in advance.
Barry
in Oregon
> Hello,
>
> "Data Conversion" can convert a column to different data type and add the
> column to the dataset. You can use the "Data Conversion Transformation
> Editor" dialog box to select the columns to convert, select the data type
> to which the column is converted, and set conversion attributes.
> DT_DBTIMESTAMP is a Integration Services Data Type. It is a timestamp
> structure that consists of year, month, hour, minute, second, and
> fraction.
>
> On my side, I select "Flat File Source" as data flow source, "Data
> Conversion" as data flow transformations, "OLE DB Destination" as the data
> flow destinations.
>
> For more information, refer to the "Data Conversion Transformation Editor"
> topic in SQL server 2005 books online.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|
|
|
|
|
frostbb
|
Posted: Wed Nov 30 16:19:42 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Sophie,
Now I'm really alarmed ... I set the "default value or Binding" value to
NULL for all of the columns in one of the larger tables we're loading from a
flat file and setting the individual default column values to NULL didn't
seem to make any difference in the values inserted via SSIS from a flat file
source. Blank integers in the flat file source ended up as 0's, blank
dates ended up as 12/30/1899 12:00:00AM, blank strings ended up as a SINGLE
SPACE! arrrrggggg in the target sql server table.
This is NOT GOOD AT ALL, we depend on NULL values representing missing or
blank data in the flat file source.
I've got to be missing something here. Please tell me I'm missing something
here.
Panic is setting in.
Barry
in Oregon
> Hello Barry,
>
> I tested the issue and it works fine on my side.
>
>> Is there some place in Sql Server 2005 that I can tell the database to
> "for all tables" set integer, date, decimal, string, etc types to NULL as
> the
> 'standard' default??
>
> You can create a Default object and bind it to a column. When bound to a
> column or an alias data type, a default specifies a value to be inserted
> into the column to which the object is bound (or into all columns, in the
> case of an alias data type), when no value is explicitly supplied during
> an
> insert.
>
> For example, the following sample code create a Default object mydf which
> default value is null. Then bind the default object to a column:
>
> --create a table
>
> create table tt (c1 varchar(10),
> c2 varchar(10),
> c3 int)
>
> --create a default object
> create default mydf as null
>
> --bind the default object to the column
> sp_bindefault mydf , 'tt.c3'
>
> sp_bindefault mydf , 'tt.c2'
>
> --insert value into the table
> insert into tt (c1,c2) values ('a','b')
> insert into tt (c1) values ('a')
>
> --verify that the null is the default value
>
> select * from tt
>
> For more information, refer to the "CREATE DEFAULT (Transact-SQL)" topic
> in
> SQL server 2005 books online.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>
|
|
|
|
|
frostbb
|
Posted: Wed Nov 30 19:24:43 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Sophie,
I'm going to move this discussion to a new thread on the new
IntegrationServices list.
I'll use "missing data in [flat file source] spawns bogus data in Sql
Server destination table" as the subject line.
I believe the subject has 'morphed' enough to warrant a new thread.
Thanks for all your help!
Barry
in Oregon
> Hello,
>
> "Data Conversion" can convert a column to different data type and add the
> column to the dataset. You can use the "Data Conversion Transformation
> Editor" dialog box to select the columns to convert, select the data type
> to which the column is converted, and set conversion attributes.
> DT_DBTIMESTAMP is a Integration Services Data Type. It is a timestamp
> structure that consists of year, month, hour, minute, second, and
> fraction.
>
> On my side, I select "Flat File Source" as data flow source, "Data
> Conversion" as data flow transformations, "OLE DB Destination" as the data
> flow destinations.
>
> For more information, refer to the "Data Conversion Transformation Editor"
> topic in SQL server 2005 books online.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|
|
|
|
|
v-sguo
|
Posted: Thu Dec 01 01:22:51 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Hello,
I have tested the issue on my side but I didn't reproduce the issue. The
result of the import is as followings:
int_column date_column string_column
1 2005-11-01 00:00:00.000 record nbr 1
2 2005-11-02 00:00:00.000 record nbr 2
3 2005-11-03 00:00:00.000 record nbr 3
4 NULL record nbr 4 null date
5 2005-11-04 00:00:00.000 record nbr 5
6 2005-11-05 00:00:00.000 record nbr 6
The base date is January 1, 1900 in SQL server 2005. I'd like to confirm
that you are using an English version SQL server 2005 which is installed on
an English version operating system(OS).
Based on my research, some customers encountered the similar issue when the
date format is different from one database to the other, or there are
actually zeros stored in the date_column column instead of NULLs:
http://www.codeguru.com/forum/showthread.php?threadid=365201
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse
_frm/thread/56fef5de46b1f489/0efa4bbb7320e60a?lnk=st&q=datetime+and+1899+and
+null+and+sql&rnum=4&hl=en#0efa4bbb7320e60a
I suggest that you perform the following steps to narrow down the issue:
1. Create a NEW flat txt file as followings on your OS and test the issue
again. DON'T use the one exported from the Unix DB:
1|11/1/2005|record nbr 1
2|11/2/2005|record nbr 2
3|11/3/2005|record nbr 3
4||record nbr 4 null date
5|11/4/2005|record nbr 5
6|11/5/2005|record nbr 6
2. Create a new table in your SQL server 2005 as followings. Export the
data as a txt file and import it again using SSIS. Test if you can
reproduce the issue.
int_column date_column string_column
1 2005-11-01 00:00:00.000 record nbr 1
2 2005-11-02 00:00:00.000 record nbr 2
3 2005-11-03 00:00:00.000 record nbr 3
4 NULL record nbr 4 null date
5 2005-11-04 00:00:00.000 record nbr 5
6 2005-11-05 00:00:00.000 record nbr 6
3. Test the issue on another machine which has English version SQL server
2005 installed on an English version OS.
Please let me know the detailed results. I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|
|
|
|
|
v-sguo
|
Posted: Thu Dec 01 01:33:36 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Hello,
The default specifies a value to be inserted into the column when NO value
is EXPLICITLY supplied during an insert.
For example, it works in the following command:
insert into tt (c1,c2) values ('a','b')
Note: please refer to the sample in my previous post.
However, I think it is not applied for this case as the column value may
not be null in this situation or the DB format is different.
The base date is January 1, 1900 in SQL server 2005. I am afraid that we
are unable to change it as the base date is the system reference date.
For more information, you may want to refer to the "Date and Time
(Transact-SQL)" topic in SQL server 2005 Books Online.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|
|
|
|
|
frostbb
|
Posted: Thu Dec 01 09:24:33 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Sophie,
As always, thanks for you prompt reply.
I've studied your example from you're earlier post and I understand how
default value / binding assignments work. Thank you for taking the time to
work thu the issue with me. I appreciate the effort and time you put into
the example.
I think the original issue is really that SSIS is inventing & stuffing bogus
data into the tables. Not that the base table behavior is incorrect.
I apologize for being so slow to sort this issue out. Best wishes.
Sincerely,
Barry
in Oregon
> Hello,
>
> The default specifies a value to be inserted into the column when NO value
> is EXPLICITLY supplied during an insert.
>
> For example, it works in the following command:
>
> insert into tt (c1,c2) values ('a','b')
>
> Note: please refer to the sample in my previous post.
>
> However, I think it is not applied for this case as the column value may
> not be null in this situation or the DB format is different.
>
> The base date is January 1, 1900 in SQL server 2005. I am afraid that we
> are unable to change it as the base date is the system reference date.
>
> For more information, you may want to refer to the "Date and Time
> (Transact-SQL)" topic in SQL server 2005 Books Online.
>
> I hope the information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|
|
|
|
|
frostbb
|
Posted: Thu Dec 01 11:39:52 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Sophie,
Hummmm, you can't reproduce the error ?? Wow. It happens VERY
consistently for me.
As far as I can tell we ARE running the full English versions of Win2003 and
Sql Server 2005
==============================================================
Our OS is Windows Server 2003 Enterprise Edition
Version 5.2 (Build 3790.svr03_sp1_rtm.050324-1147 : Service Pack 1
Our Sql Server 2005 instance General Properties are as follows ...
Operating System Microsoft SQL Server Standard Edition
Platform Microsoft Windows NT 5.2 (3790)
i.e. Win2003 Enterprise
Version 9.00.1399.06
Language English (United States)
Server Collation SQL_Latin1_General_CP1_Cl_AS
Integration Services is running within
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework Version 2.0.50727
==============================================================
The test file flat file that I created yesterday was created in a text
editor
not derived from a UNIX export flat file
I RECREATED the above test flat file source w/ Notepad
1|11/1/2005|record nbr 1
2|11/2/2005|record nbr 2
3|11/3/2005|record nbr 3
4||record nbr 4 null date
5|11/4/2005|
|11/5/2005|record nbr 6
Then ran the SSIS [flat file source] => [OleDb Destination] test again
The results of the import are (expored from test table)
int_column|date_column|string_column
1|2005-11-01 00:00:00|record nbr 1
2|2005-11-02 00:00:00|record nbr 2
3|2005-11-03 00:00:00|record nbr 3
4|1899-12-30 00:00:00|record nbr 4 null date
5|2005-11-04 00:00:00|
0|2005-11-05 00:00:00|record nbr 6
PLEASE NOTE: I've moved this thread to the new
microsoft.public.sqlserver.integrationsvcs group since this issue is turning
more into an SSIS issue rather than a generic Sql Server issue.
Again thanks for your time! Its very much appreciated.
Barry
in Oregon
> Hello,
>
> I have tested the issue on my side but I didn't reproduce the issue. The
> result of the import is as followings:
>
> int_column date_column string_column
> 1 2005-11-01 00:00:00.000 record nbr 1
> 2 2005-11-02 00:00:00.000 record nbr 2
> 3 2005-11-03 00:00:00.000 record nbr 3
> 4 NULL record nbr 4 null date
> 5 2005-11-04 00:00:00.000 record nbr 5
> 6 2005-11-05 00:00:00.000 record nbr 6
>
> The base date is January 1, 1900 in SQL server 2005. I'd like to confirm
> that you are using an English version SQL server 2005 which is installed
> on
> an English version operating system(OS).
>
> Based on my research, some customers encountered the similar issue when
> the
> date format is different from one database to the other, or there are
> actually zeros stored in the date_column column instead of NULLs:
>
> http://www.codeguru.com/forum/showthread.php?threadid=365201
>
> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse
> _frm/thread/56fef5de46b1f489/0efa4bbb7320e60a?lnk=st&q=datetime+and+1899+and
> +null+and+sql&rnum=4&hl=en#0efa4bbb7320e60a
>
> I suggest that you perform the following steps to narrow down the issue:
>
> 1. Create a NEW flat txt file as followings on your OS and test the issue
> again. DON'T use the one exported from the Unix DB:
>
> 1|11/1/2005|record nbr 1
> 2|11/2/2005|record nbr 2
> 3|11/3/2005|record nbr 3
> 4||record nbr 4 null date
> 5|11/4/2005|record nbr 5
> 6|11/5/2005|record nbr 6
>
>
> 2. Create a new table in your SQL server 2005 as followings. Export the
> data as a txt file and import it again using SSIS. Test if you can
> reproduce the issue.
>
> int_column date_column string_column
> 1 2005-11-01 00:00:00.000 record nbr 1
> 2 2005-11-02 00:00:00.000 record nbr 2
> 3 2005-11-03 00:00:00.000 record nbr 3
> 4 NULL record nbr 4 null date
> 5 2005-11-04 00:00:00.000 record nbr 5
> 6 2005-11-05 00:00:00.000 record nbr 6
>
> 3. Test the issue on another machine which has English version SQL server
> 2005 installed on an English version OS.
>
> Please let me know the detailed results. I look forward to hearing from
> you.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>
>
>
|
|
|
|
|
frostbb
|
Posted: Thu Dec 01 12:16:21 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Sophie,
To address the remainder of your questions. Unfortunately, we don't have
another
system readily available with Win2003 that we can use as a remote test
platform.
Thought its a good idea. Maybe I can find a desktop somewhere that I can
'spin
up' as a test platform ... I'll look into the possibility.
In my other reply I tried to provide as much information about our OS, Sql
Server
and SSIS instances. Hopefully you can tell from the version numbers if
we're
running compatible versions.
WRT to "date format is different from one database to the other, or there
are
actually zeros stored in the date_column ". I'm kind of confused about how
that applies in this instance since we seem to have removed that issue as a
potential cause with the test [flat file source] date formatting.
Thanks.
Barry
in Oregon
BTW can you respond to threads in the new .integrationservices news group
probably should check before I try to move this thread there. :)
> Hello,
>
> I have tested the issue on my side but I didn't reproduce the issue. The
> result of the import is as followings:
>
> int_column date_column string_column
> 1 2005-11-01 00:00:00.000 record nbr 1
> 2 2005-11-02 00:00:00.000 record nbr 2
> 3 2005-11-03 00:00:00.000 record nbr 3
> 4 NULL record nbr 4 null date
> 5 2005-11-04 00:00:00.000 record nbr 5
> 6 2005-11-05 00:00:00.000 record nbr 6
>
> The base date is January 1, 1900 in SQL server 2005. I'd like to confirm
> that you are using an English version SQL server 2005 which is installed
> on
> an English version operating system(OS).
>
> Based on my research, some customers encountered the similar issue when
> the
> date format is different from one database to the other, or there are
> actually zeros stored in the date_column column instead of NULLs:
>
> http://www.codeguru.com/forum/showthread.php?threadid=365201
>
> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse
> _frm/thread/56fef5de46b1f489/0efa4bbb7320e60a?lnk=st&q=datetime+and+1899+and
> +null+and+sql&rnum=4&hl=en#0efa4bbb7320e60a
>
> I suggest that you perform the following steps to narrow down the issue:
>
> 1. Create a NEW flat txt file as followings on your OS and test the issue
> again. DON'T use the one exported from the Unix DB:
>
> 1|11/1/2005|record nbr 1
> 2|11/2/2005|record nbr 2
> 3|11/3/2005|record nbr 3
> 4||record nbr 4 null date
> 5|11/4/2005|record nbr 5
> 6|11/5/2005|record nbr 6
>
>
> 2. Create a new table in your SQL server 2005 as followings. Export the
> data as a txt file and import it again using SSIS. Test if you can
> reproduce the issue.
>
> int_column date_column string_column
> 1 2005-11-01 00:00:00.000 record nbr 1
> 2 2005-11-02 00:00:00.000 record nbr 2
> 3 2005-11-03 00:00:00.000 record nbr 3
> 4 NULL record nbr 4 null date
> 5 2005-11-04 00:00:00.000 record nbr 5
> 6 2005-11-05 00:00:00.000 record nbr 6
>
> 3. Test the issue on another machine which has English version SQL server
> 2005 installed on an English version OS.
>
> Please let me know the detailed results. I look forward to hearing from
> you.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>
>
>
|
|
|
|
|
v-sguo
|
Posted: Fri Dec 02 03:27:33 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Hello Barry,
Thanks for your reply. It's my pleasure to work with you.
I understand that you have moved this thread to the new group. If the issue
still exists after troubleshooting, I recommend that you contact Microsoft
Customer Service and Support (CSS) so that a dedicated Support Professional
can work with you in a more timely and efficient manner.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|
|
|
|
|
frostbb
|
Posted: Tue Dec 06 10:42:55 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Sophie,
You're advice was correct all along. I was simply for some INEXPLICABLE
reason missing the "Retain NULLs" switch on the [flat file source] Editor.
I'll copy the applicable part of the thread from the .integrationservices
group so that this thread will have a 'conclusion' as well. Hopefully
others can learn from my bone head mistake. I must surely be getting too
old for this stuff.
Thanks for your incredible patience and sticking with the issue. Especially
since I was being such a bone head.
Sincerely
Barry
in Oregon
========== Responses to my .integrationservices group thread
===============
Lawrence,
Yup, you're right.
For some INEXPLICABLE reason I've simply been missing the [flat file source]
"Retain null values from the source as null values in the data flow" switch
under the general tab of the "Flat File Source Editor".
I've been checking the "Retain null values" in all the other steps along the
way! Naturally checking retain nulls through out the pipeline steps had no
impact if the bogus data was being inserted by the [flat file source]. My
tendancy has been look at the actual flat file connection source for the
problem rather than go back to the general editor.
Missing the "Retain Nulls" switch in the [flat file source] editor is really
wierding me out ... how could I have possibly missed it after all this time
??? Yearrrreggggggg.
Once the "Retain null values from the source as null values in the data
flow" switch under the general tab of the "Flat File Source Editor". The
bogus data dissappeared. Problem solved.
BONE HEAD ERROR ON MY PART. For what it's worth I've suffered immensley
and lost a huge amount of time trying to track this thing down.
Thanks to all that have responded to my posts and have had the patience to
stick with this issue. Your time and advice is very much appreciated.
Barry
in Oregon [certified MORON]
> In a flat file.....a "blank column" is a NULL.
>
> Consider a column that contains text data. If the presence of characters
> is a valid string, and the presence of a single space is a blank field,
> how do you wish to interpret the complete absence of characters in the
> field? Should it not be NULL, as you've indicated you want?
>
> Consider a column that contains integer data. If the presence of digits is
> a valid number, and the presence of a single zero is the value zero, how
> do you wish to interpret the complete absence of characters in the field?
> Should it not be NULL, as you've indicated you want?
>
> There is /no/ methodology for explicitly indicating a "NULL reference" in
> a flat file except by the complete absence of data.
>
> If the option "Retain nulls from the source..." is not enabled, then your
> "blank columns" are correctly being interpreted as a blank text field or a
> zero value in an integer field, or the default date... 12/30/1899
>
> With the option enabled, as suggested, the "blank column" will always be
> imported as NULL.
>
> The behavior you are observing is /correct/, given that the option to
> "Retain nulls" is disabled. In your examples:
>> 1|11/1/2005|record nbr 1
>> 2|11/2/2005|record nbr 2
>> 3|11/3/2005|record nbr 3
>> 4||record nbr 4 null date
>> 5|11/4/2005|
>> |11/5/2005|record nbr 6
>
> In record 4, there is a NULL in the second field.
> In record 5, there is a NULL in the third field.
> In record 6, there is a NULL in the first field.
>
> If you want to retain those NULLs... you need to enable the option to do
> so.
>
>
>> Allan,
>>
>> As always thanks for your suggestion. Its much appreciated.
>>
>> Unfortnately, no. I'm not really sure that the switch is relavent in
>> this
>> case since there's no NULL reference in the input flat file source,
>> simply
>> blank columns.
>>
>> I'm trying to move a thread on this subject from the sql server group to
>> here
>> since it's more of an SSIS issue than an Sql Server issue ...
>> (see Sql microsoft.public.sqlserver.server - Server 2005: is there
>> someplace to set the global database column type default ??)
>>
>>
>> I recreated the test [flat file source] w/ Notepad
>> to remove any residual Unix taint
>>
>> 1|11/1/2005|record nbr 1
>> 2|11/2/2005|record nbr 2
>> 3|11/3/2005|record nbr 3
>> 4||record nbr 4 null date
>> 5|11/4/2005|
>> |11/5/2005|record nbr 6
>>
>> Ran the SSIS [flat file source] => [OleDb Destination] test again
>> created with straight drag and drop and simply pointing to the
>> source and destination.
>>
>> The results of the SSIS import are ...
>>
>> (actual data expored from test table)
>>
>> int_column|date_column|string_column
>> 1|2005-11-01 00:00:00|record nbr 1
>> 2|2005-11-02 00:00:00|record nbr 2
>> 3|2005-11-03 00:00:00|record nbr 3
>> 4|1899-12-30 00:00:00|record nbr 4 null date
>> 5|2005-11-04 00:00:00|
>> 0|2005-11-05 00:00:00|record nbr 6
>>
>> Note the bogus date in row 4, the space in row 5 and the 0 in
>> row 6.
>>
>> Can anyone else re-produce this behavior ... Microsoft seems to be
>> having a problem reproducing this issue. Its sure consistent on my
>> system !!
>>
>> Gotta get this resolved !!!
>>
>> Barry
>> in Oregon
>>
>>> Hello frostbb,
>>>
>>> There is a setting to keep NULL in the pipeline on the source adapter.
>>> "Retain nulls from the source as null values in the data flow"
>>>
>>>
>>> DOes this help?
>>>
>>> Allan
> Hello Barry,
>
> Thanks for your reply. It's my pleasure to work with you.
>
> I understand that you have moved this thread to the new group. If the
> issue
> still exists after troubleshooting, I recommend that you contact Microsoft
> Customer Service and Support (CSS) so that a dedicated Support
> Professional
> can work with you in a more timely and efficient manner.
>
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
>
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
>
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|
|
|
|
|
v-sguo
|
Posted: Wed Dec 07 01:19:56 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
Hello,
Thanks for sharing the information. Have a good day!
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|
|
|
|
|
AndersonStu
|
Posted: Tue Dec 13 09:47:02 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
May I ask a related question please?
It seems that in the management studio import/export wizard the default
behaviour is to not retain nulls. Is there any way to change this? I have my
main ETL loads in SSIS but I have quite a lot of ad-hoc stuff that the wizard
in 2000 was great for.
Many thanks,
Stuart.
> Hello,
>
> Thanks for sharing the information. Have a good day!
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>
|
|
|
|
|
AndersonStu
|
Posted: Wed Dec 14 09:39:04 CST 2005 |
Top |
SQL Server >> Sql Server 2005: is there someplace to set the global database column type default ??
I have just realised that I can still run dstwiz.exe to import to SQL server
2005. Problem solved.
Stuart.
> May I ask a related question please?
>
> It seems that in the management studio import/export wizard the default
> behaviour is to not retain nulls. Is there any way to change this? I have my
> main ETL loads in SSIS but I have quite a lot of ad-hoc stuff that the wizard
> in 2000 was great for.
>
> Many thanks,
> Stuart.
>
>
> > Hello,
> >
> > Thanks for sharing the information. Have a good day!
> >
> > Sophie Guo
> > Microsoft Online Partner Support
> >
> > Get Secure! - www.microsoft.com/security
> >
> > =====================================================
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> >
|
|
|
|
|
|
|