nvarchar and output to flat file.

Hello,
Im outputting a sql table to a text file. One of the columns in this table
is an nvarchar(1200) but the result in the flat file is truncated to 254
chars....how do ensure that it outputs all chars up to 1200?
thxJohn,
which approach / utility are you using to outputting the sql table to a text
file?
AMB
"John Smith" wrote:

> Hello,
> Im outputting a sql table to a text file. One of the columns in this table
> is an nvarchar(1200) but the result in the flat file is truncated to 254
> chars....how do ensure that it outputs all chars up to 1200?
> thx
>
>|||I think I gave him a suitable answer on a separate post.
Barrysql

NUMBER FORMATTING : round it to nearest whole number

HI,
CAN ANY ONE HELP me for this query.
i want to format the text field which takes numbers.I want to display the number as with no decimal places, round it to nearest whole number. For example,3.1 to 3.4 will round to 3 and 3.5 to 3.9 will round to 4.Have you tried using the Math.Round function?
Ex: Math.Round(YourVar, 0)
I think that'd do it, but it's been awhile since I've played with that function.
- T
"SRIRAM" wrote:
> HI,
> CAN ANY ONE HELP me for this query.
> i want to format the text field which takes numbers.I want to display the number as with no decimal places, round it to nearest whole number. For example,3.1 to 3.4 will round to 3 and 3.5 to 3.9 will round to 4.

Nulling text data

I have a table that contains text data(XML formatted code). We have decided
on a strategy to archive some of this data and null it out. Out of a 220GB
database, this history table is 153GB. After setting the text field to null,
we haven't realized any space improvements. We reindex often. The previous
DBA suggested copying the rows out and renaming the destination table but I'm
sceptical that I can finish that in any conceivable downtime due to the row
count and general query performance when accessing text columns. Any ideas
on how to reclaim this space?On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
On SQL 2005, you could use index defrag to reclaim the space.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
>I have a table that contains text data(XML formatted code). We have
>decided
> on a strategy to archive some of this data and null it out. Out of a 220GB
> database, this history table is 153GB. After setting the text field to
> null,
> we haven't realized any space improvements. We reindex often. The
> previous
> DBA suggested copying the rows out and renaming the destination table but
> I'm
> sceptical that I can finish that in any conceivable downtime due to the
> row
> count and general query performance when accessing text columns. Any
> ideas
> on how to reclaim this space?|||use DBCC SHRINKDATABASE command
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:OMJ0736oFHA.3756@.TK2MSFTNGP09.phx.gbl...
> On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
> On SQL 2005, you could use index defrag to reclaim the space.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
> >I have a table that contains text data(XML formatted code). We have
> >decided
> > on a strategy to archive some of this data and null it out. Out of a
220GB
> > database, this history table is 153GB. After setting the text field to
> > null,
> > we haven't realized any space improvements. We reindex often. The
> > previous
> > DBA suggested copying the rows out and renaming the destination table
but
> > I'm
> > sceptical that I can finish that in any conceivable downtime due to the
> > row
> > count and general query performance when accessing text columns. Any
> > ideas
> > on how to reclaim this space?
>

Nulling text data

I have a table that contains text data(XML formatted code). We have decided
on a strategy to archive some of this data and null it out. Out of a 220GB
database, this history table is 153GB. After setting the text field to null,
we haven't realized any space improvements. We reindex often. The previous
DBA suggested copying the rows out and renaming the destination table but I'm
sceptical that I can finish that in any conceivable downtime due to the row
count and general query performance when accessing text columns. Any ideas
on how to reclaim this space?
On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
On SQL 2005, you could use index defrag to reclaim the space.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
>I have a table that contains text data(XML formatted code). We have
>decided
> on a strategy to archive some of this data and null it out. Out of a 220GB
> database, this history table is 153GB. After setting the text field to
> null,
> we haven't realized any space improvements. We reindex often. The
> previous
> DBA suggested copying the rows out and renaming the destination table but
> I'm
> sceptical that I can finish that in any conceivable downtime due to the
> row
> count and general query performance when accessing text columns. Any
> ideas
> on how to reclaim this space?
|||use DBCC SHRINKDATABASE command
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:OMJ0736oFHA.3756@.TK2MSFTNGP09.phx.gbl...
> On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
> On SQL 2005, you could use index defrag to reclaim the space.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
220GB[vbcol=seagreen]
but
>

Nulling text data

I have a table that contains text data(XML formatted code). We have decided
on a strategy to archive some of this data and null it out. Out of a 220GB
database, this history table is 153GB. After setting the text field to null
,
we haven't realized any space improvements. We reindex often. The previous
DBA suggested copying the rows out and renaming the destination table but I'
m
sceptical that I can finish that in any conceivable downtime due to the row
count and general query performance when accessing text columns. Any ideas
on how to reclaim this space?On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
On SQL 2005, you could use index defrag to reclaim the space.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
>I have a table that contains text data(XML formatted code). We have
>decided
> on a strategy to archive some of this data and null it out. Out of a 220GB
> database, this history table is 153GB. After setting the text field to
> null,
> we haven't realized any space improvements. We reindex often. The
> previous
> DBA suggested copying the rows out and renaming the destination table but
> I'm
> sceptical that I can finish that in any conceivable downtime due to the
> row
> count and general query performance when accessing text columns. Any
> ideas
> on how to reclaim this space?|||use DBCC SHRINKDATABASE command
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:OMJ0736oFHA.3756@.TK2MSFTNGP09.phx.gbl...
> On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
> On SQL 2005, you could use index defrag to reclaim the space.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
220GB[vbcol=seagreen]
but[vbcol=seagreen]
>

NULL values returned when reading values from a text file using Data Reader.

I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?Why aren't you using the flat file source connector?|||

Sorry: I reread my initial posting and it contained some incorrect details. I have modified the message accordingly and the modified content is in italics

I have a DTSX package which reads values from a delimited text file using a Flat File source component (and a Lookup for validating some of the data) and reads the data into a Table in an Oracle database. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. The typical file length is between 300,000 to 500,000 rows of data. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Flat File source component. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?

|||One additional detail which may be pertinent: The DTSX is running inside of a virtual machine.|||Since you are using a lookup, a couple of things to consider:
Lookup matching is case sensitive, so you may have to adjust your data for comparison accordingly|||It appears that memory may be the answer. The VM where the DTSX was running only had 512 MB of RAM. When this was moved to an environment with 1 GB we do not see the same issues.|||Hmmm - seems I spoke too soon. One file which was about 75,000 rows of data / 30 MB of data processed successfully. However another file which was around 300,000 rows of data / 150 MB resulted in the same "false NULL" situation. I'm going to try breaking down the larger file into 4 smaller segments and will process them each individually to see if this makes a difference. I am not aware of any explicit size limitations in SSIS that we should be bumping up against with these file sizes but can anyone tell me if there are thresholds that should not be exceeded as a best practice when dealing with flat files or lookups?|||Ok, running the smaller file also resulted in the same error as before. We're now operating under the theory that this has to do with something being kept in memory after the package initially runs since we usually seem to be able to generate a "clean" result file after the first time we try running a package. To test this theory we will reboot the computer where the DTSX is stored and will then rerun a file which has already generated errors.|||

It appears that we have a solution: We broke up the DTSX package into 4 smaller packages, broke the file up in 4 smaller files, ran the packages on a non-virtual machine with 1GB of RAM, and executed the packages through the command line. When all of these changes were combined we get the expected result without any false NULL issues. Initial testing seems to reveal that omitting any one of these steps may still result in the original error but this isn't conclusive at this point in time as we haven't tried all of the different scenarios. All these changes seem to indicate that the root cause of the issue is related to available memory, and I am interested in anyone else has any insight - thanks.

|||

I am encountering this error. My solution was to uncheck the "Retain null values..." within the Flat File Source. However, I had to change my logic that checked for nulls to check for empty strings.

I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.

|||

Shizelmah wrote:

I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.

So what are you going to do about it? Leaving pithy comments on here won't make the slightest bit of difference I'm afraid. The correct place to submit your bugs and suggestions is http://connect.microsoft.com/sqlserver/feedback

Hope that helps.

Regards

-Jamie

|||I have encountered the same problem. It seems to be a bug in SSIS.

After doing some investigation it turned out it was the Union All component used in the dataflow which caused this situation. When I removed the Union All component the problem disappeared.

I can't explain this weird behaviour of SSIS - I suppose it is a bug related to internal SSIS buffer management.

If you have a Union All component with more than 4 inputs in your dataflow, do try to remove it. Maybe it will help, as it was in my case.

Regards,
Grzegorz

NULL values returned when reading values from a text file using Data Reader.

I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?Why aren't you using the flat file source connector?|||

Sorry: I reread my initial posting and it contained some incorrect details. I have modified the message accordingly and the modified content is in italics

I have a DTSX package which reads values from a delimited text file using a Flat File source component (and a Lookup for validating some of the data) and reads the data into a Table in an Oracle database. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. The typical file length is between 300,000 to 500,000 rows of data. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Flat File source component. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?

|||One additional detail which may be pertinent: The DTSX is running inside of a virtual machine.|||Since you are using a lookup, a couple of things to consider:
Lookup matching is case sensitive, so you may have to adjust your data for comparison accordingly|||It appears that memory may be the answer. The VM where the DTSX was running only had 512 MB of RAM. When this was moved to an environment with 1 GB we do not see the same issues.|||Hmmm - seems I spoke too soon. One file which was about 75,000 rows of data / 30 MB of data processed successfully. However another file which was around 300,000 rows of data / 150 MB resulted in the same "false NULL" situation. I'm going to try breaking down the larger file into 4 smaller segments and will process them each individually to see if this makes a difference. I am not aware of any explicit size limitations in SSIS that we should be bumping up against with these file sizes but can anyone tell me if there are thresholds that should not be exceeded as a best practice when dealing with flat files or lookups?|||Ok, running the smaller file also resulted in the same error as before. We're now operating under the theory that this has to do with something being kept in memory after the package initially runs since we usually seem to be able to generate a "clean" result file after the first time we try running a package. To test this theory we will reboot the computer where the DTSX is stored and will then rerun a file which has already generated errors.|||

It appears that we have a solution: We broke up the DTSX package into 4 smaller packages, broke the file up in 4 smaller files, ran the packages on a non-virtual machine with 1GB of RAM, and executed the packages through the command line. When all of these changes were combined we get the expected result without any false NULL issues. Initial testing seems to reveal that omitting any one of these steps may still result in the original error but this isn't conclusive at this point in time as we haven't tried all of the different scenarios. All these changes seem to indicate that the root cause of the issue is related to available memory, and I am interested in anyone else has any insight - thanks.

|||

I am encountering this error. My solution was to uncheck the "Retain null values..." within the Flat File Source. However, I had to change my logic that checked for nulls to check for empty strings.

I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.

|||

Shizelmah wrote:

I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.

So what are you going to do about it? Leaving pithy comments on here won't make the slightest bit of difference I'm afraid. The correct place to submit your bugs and suggestions is http://connect.microsoft.com/sqlserver/feedback

Hope that helps.

Regards

-Jamie

|||I have encountered the same problem. It seems to be a bug in SSIS.

After doing some investigation it turned out it was the Union All component used in the dataflow which caused this situation. When I removed the Union All component the problem disappeared.

I can't explain this weird behaviour of SSIS - I suppose it is a bug related to internal SSIS buffer management.

If you have a Union All component with more than 4 inputs in your dataflow, do try to remove it. Maybe it will help, as it was in my case.

Regards,
Grzegorz

NULL values returned when reading values from a text file using Data Reader.

I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?Why aren't you using the flat file source connector?|||

Sorry: I reread my initial posting and it contained some incorrect details. I have modified the message accordingly and the modified content is in italics

I have a DTSX package which reads values from a delimited text file using a Flat File source component (and a Lookup for validating some of the data) and reads the data into a Table in an Oracle database. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. The typical file length is between 300,000 to 500,000 rows of data. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Flat File source component. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?

|||One additional detail which may be pertinent: The DTSX is running inside of a virtual machine.|||Since you are using a lookup, a couple of things to consider:
Lookup matching is case sensitive, so you may have to adjust your data for comparison accordingly|||It appears that memory may be the answer. The VM where the DTSX was running only had 512 MB of RAM. When this was moved to an environment with 1 GB we do not see the same issues.|||Hmmm - seems I spoke too soon. One file which was about 75,000 rows of data / 30 MB of data processed successfully. However another file which was around 300,000 rows of data / 150 MB resulted in the same "false NULL" situation. I'm going to try breaking down the larger file into 4 smaller segments and will process them each individually to see if this makes a difference. I am not aware of any explicit size limitations in SSIS that we should be bumping up against with these file sizes but can anyone tell me if there are thresholds that should not be exceeded as a best practice when dealing with flat files or lookups?|||Ok, running the smaller file also resulted in the same error as before. We're now operating under the theory that this has to do with something being kept in memory after the package initially runs since we usually seem to be able to generate a "clean" result file after the first time we try running a package. To test this theory we will reboot the computer where the DTSX is stored and will then rerun a file which has already generated errors.|||

It appears that we have a solution: We broke up the DTSX package into 4 smaller packages, broke the file up in 4 smaller files, ran the packages on a non-virtual machine with 1GB of RAM, and executed the packages through the command line. When all of these changes were combined we get the expected result without any false NULL issues. Initial testing seems to reveal that omitting any one of these steps may still result in the original error but this isn't conclusive at this point in time as we haven't tried all of the different scenarios. All these changes seem to indicate that the root cause of the issue is related to available memory, and I am interested in anyone else has any insight - thanks.

|||

I am encountering this error. My solution was to uncheck the "Retain null values..." within the Flat File Source. However, I had to change my logic that checked for nulls to check for empty strings.

I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.

|||

Shizelmah wrote:

I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.

So what are you going to do about it? Leaving pithy comments on here won't make the slightest bit of difference I'm afraid. The correct place to submit your bugs and suggestions is http://connect.microsoft.com/sqlserver/feedback

Hope that helps.

Regards

-Jamie

|||I have encountered the same problem. It seems to be a bug in SSIS.

After doing some investigation it turned out it was the Union All component used in the dataflow which caused this situation. When I removed the Union All component the problem disappeared.

I can't explain this weird behaviour of SSIS - I suppose it is a bug related to internal SSIS buffer management.

If you have a Union All component with more than 4 inputs in your dataflow, do try to remove it. Maybe it will help, as it was in my case.

Regards,
Grzegorz

Null values or multiple tables

I have a table that stores SNMP Mib values.
These values can be either numeric or text.
I would like to store the text values in a varchar column and the
numeric values in a double column. This of course would help
performance amongs other things.
The question is this:
Is it better to have two tables, one that stores the numeric values
and one that stores the text values.
ex)
CREATE TABLE [dbo].[SnmpMibDataText] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValue] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[SnmpMibDataNumeric] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValue] [float] NOT NULL
) ON [PRIMARY]
Or is it better to have 1 table with two columns, one for text and one
for numeric?
CREATE TABLE [dbo].[SnmpMibData] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValueText] [nvarchar] (255),
[SampleValueNumeric] [float]
) ON [PRIMARY]
Now when the Mib value is numeric the text column would be null, and
when the mib value is text the numeric column would be null.
When i ask what is better, i mean overall better (performance,
usability, extendability, etc)?
Thanks in advance
Mark
The one table is probably the better one. Its row size is only slightly
larger than SnmpMibDataText in the two table approach. You have one less
table to manage.
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Oueis" <markoueis@.hotmail.com> wrote in message
news:b1800bd3.0412131123.7d5e2802@.posting.google.c om...
> I have a table that stores SNMP Mib values.
> These values can be either numeric or text.
> I would like to store the text values in a varchar column and the
> numeric values in a double column. This of course would help
> performance amongs other things.
> The question is this:
> Is it better to have two tables, one that stores the numeric values
> and one that stores the text values.
> ex)
> CREATE TABLE [dbo].[SnmpMibDataText] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [nvarchar] (255) NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[SnmpMibDataNumeric] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [float] NOT NULL
> ) ON [PRIMARY]
>
> Or is it better to have 1 table with two columns, one for text and one
> for numeric?
> CREATE TABLE [dbo].[SnmpMibData] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValueText] [nvarchar] (255),
> [SampleValueNumeric] [float]
> ) ON [PRIMARY]
> Now when the Mib value is numeric the text column would be null, and
> when the mib value is text the numeric column would be null.
>
> When i ask what is better, i mean overall better (performance,
> usability, extendability, etc)?
> Thanks in advance
> Mark
|||I would probably go with a three table design: a master table that had the
common definitions and, then, two 1-to1 or 1-to-many child related tables to
store the partiallly populated information. NULL-valued columns suit some
purposes but not usually the condidtion where you just don't know the
information or it is not applicable for some of the rows. That indicates
that it is not an attribute of the parent entitiy but subordinate entity
altogether.
Sincerely,
Anthony Thomas

"wei xiao[MS]" <weix@.online.microsoft.com> wrote in message
news:41beb22b$1@.news.microsoft.com...
The one table is probably the better one. Its row size is only slightly
larger than SnmpMibDataText in the two table approach. You have one less
table to manage.
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Oueis" <markoueis@.hotmail.com> wrote in message
news:b1800bd3.0412131123.7d5e2802@.posting.google.c om...
> I have a table that stores SNMP Mib values.
> These values can be either numeric or text.
> I would like to store the text values in a varchar column and the
> numeric values in a double column. This of course would help
> performance amongs other things.
> The question is this:
> Is it better to have two tables, one that stores the numeric values
> and one that stores the text values.
> ex)
> CREATE TABLE [dbo].[SnmpMibDataText] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [nvarchar] (255) NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[SnmpMibDataNumeric] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [float] NOT NULL
> ) ON [PRIMARY]
>
> Or is it better to have 1 table with two columns, one for text and one
> for numeric?
> CREATE TABLE [dbo].[SnmpMibData] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValueText] [nvarchar] (255),
> [SampleValueNumeric] [float]
> ) ON [PRIMARY]
> Now when the Mib value is numeric the text column would be null, and
> when the mib value is text the numeric column would be null.
>
> When i ask what is better, i mean overall better (performance,
> usability, extendability, etc)?
> Thanks in advance
> Mark

Null values or multiple tables

I have a table that stores SNMP Mib values.
These values can be either numeric or text.
I would like to store the text values in a varchar column and the
numeric values in a double column. This of course would help
performance amongs other things.
The question is this:
Is it better to have two tables, one that stores the numeric values
and one that stores the text values.
ex)
CREATE TABLE [dbo].[SnmpMibDataText] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValue] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[SnmpMibDataNumeric] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValue] [float] NOT NULL
) ON [PRIMARY]
Or is it better to have 1 table with two columns, one for text and one
for numeric?
CREATE TABLE [dbo].[SnmpMibData] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValueText] [nvarchar] (255),
[SampleValueNumeric] [float]
) ON [PRIMARY]
Now when the Mib value is numeric the text column would be null, and
when the mib value is text the numeric column would be null.
When i ask what is better, i mean overall better (performance,
usability, extendability, etc)?
Thanks in advance
MarkThe one table is probably the better one. Its row size is only slightly
larger than SnmpMibDataText in the two table approach. You have one less
table to manage.
--
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Oueis" <markoueis@.hotmail.com> wrote in message
news:b1800bd3.0412131123.7d5e2802@.posting.google.com...
> I have a table that stores SNMP Mib values.
> These values can be either numeric or text.
> I would like to store the text values in a varchar column and the
> numeric values in a double column. This of course would help
> performance amongs other things.
> The question is this:
> Is it better to have two tables, one that stores the numeric values
> and one that stores the text values.
> ex)
> CREATE TABLE [dbo].[SnmpMibDataText] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [nvarchar] (255) NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[SnmpMibDataNumeric] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [float] NOT NULL
> ) ON [PRIMARY]
>
> Or is it better to have 1 table with two columns, one for text and one
> for numeric?
> CREATE TABLE [dbo].[SnmpMibData] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValueText] [nvarchar] (255),
> [SampleValueNumeric] [float]
> ) ON [PRIMARY]
> Now when the Mib value is numeric the text column would be null, and
> when the mib value is text the numeric column would be null.
>
> When i ask what is better, i mean overall better (performance,
> usability, extendability, etc)?
> Thanks in advance
> Mark|||I would probably go with a three table design: a master table that had the
common definitions and, then, two 1-to1 or 1-to-many child related tables to
store the partiallly populated information. NULL-valued columns suit some
purposes but not usually the condidtion where you just don't know the
information or it is not applicable for some of the rows. That indicates
that it is not an attribute of the parent entitiy but subordinate entity
altogether.
Sincerely,
Anthony Thomas
"wei xiao[MS]" <weix@.online.microsoft.com> wrote in message
news:41beb22b$1@.news.microsoft.com...
The one table is probably the better one. Its row size is only slightly
larger than SnmpMibDataText in the two table approach. You have one less
table to manage.
--
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Oueis" <markoueis@.hotmail.com> wrote in message
news:b1800bd3.0412131123.7d5e2802@.posting.google.com...
> I have a table that stores SNMP Mib values.
> These values can be either numeric or text.
> I would like to store the text values in a varchar column and the
> numeric values in a double column. This of course would help
> performance amongs other things.
> The question is this:
> Is it better to have two tables, one that stores the numeric values
> and one that stores the text values.
> ex)
> CREATE TABLE [dbo].[SnmpMibDataText] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [nvarchar] (255) NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[SnmpMibDataNumeric] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [float] NOT NULL
> ) ON [PRIMARY]
>
> Or is it better to have 1 table with two columns, one for text and one
> for numeric?
> CREATE TABLE [dbo].[SnmpMibData] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValueText] [nvarchar] (255),
> [SampleValueNumeric] [float]
> ) ON [PRIMARY]
> Now when the Mib value is numeric the text column would be null, and
> when the mib value is text the numeric column would be null.
>
> When i ask what is better, i mean overall better (performance,
> usability, extendability, etc)?
> Thanks in advance
> Mark

Null values cause row height to change

I have a fairly simple report with several columns in the detail section. I
have set the row height to 0.15in (text is 6pt) and set both CanGrow = false
and CanShrink = false for all textboxes in the row.
The report always renders perfectly in the Visual Studio preview tab and
when exporting to PDF, but I run into problems when viewing it on the report
server webpage. Any row with a Null value in one of its fields is rendered
twice as high as the nomal rows. If I change my query so that it doesn't
return nulls, all of the rows render with the same height as expected.
Anyone else run inot this behaviour?I found a cheap way to get around the problem by replacing null values in my
report with a single space. I used expressions like the following:
=iif(IsNothing(Fields!MyField.Value)," ",Fields!MyField.Value)
This is a pain to have to enter in every field though. I would still like to
know a way to get it to render properly in HTML without having to go to all
this trouble.|||there are two options in ADVANCED PROPERTIES of the textbox (or cell of
a table) which meant expand the textbox to accomodate the data and
shrink the textbox to accomodate the data i think by unchecking those
options (ie not allowing shrinking/expansion of textbox) will resolve
the problem check that out n let me know what result comes out
thanx

NULL values

I have a text box on a web form in visual studio.net that a user enters a
number in.
If they blank out the text box then I want to put a null value back into the
database field.
I want to return the value to NULL like txtWaterDischargeRate =
System.DBNull.Value instead of Nothing but I get an error
Can't convert NULL to integer
How can I enter a null in the database field on MS SQL Server?
ThanksHi
I am not sure what you are trying to do and I'm not a webform expert, but
you should be able to set may want to try something like checking the field'
s
data length in the application and passing DBNULL.value to the stored
procedure.
Alternatively you may be able to use a special value (say -1) and use
NULLIF(@.parameter,-1) in your insert statement.
John
"Craig" wrote:

> I have a text box on a web form in visual studio.net that a user enters a
> number in.
> If they blank out the text box then I want to put a null value back into t
he
> database field.
> I want to return the value to NULL like txtWaterDischargeRate =
> System.DBNull.Value instead of Nothing but I get an error
> Can't convert NULL to integer
> How can I enter a null in the database field on MS SQL Server?
> Thanks
>
>
>|||Sample code.....
With DataSetLocationsForm1.tblLocations(0)
.WaterDischargeCost = txtWaterDischargeCost.Text
.MoreFields = txtOtherTextboxes.Text
End With
SqlDataAdapter1.Update(DataSetLocationsForm1)
The WaterDischargeCost field in the database is an type real. If they blank
out the text box and click the SAVE button on an existing record I get an
error "Can't convert NULL to integer"
I want to put a null value back into the database field
How do I do this?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9FE6B6DA-00CE-448A-A740-2E950BC1AC57@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not sure what you are trying to do and I'm not a webform expert, but
> you should be able to set may want to try something like checking the
> field's
> data length in the application and passing DBNULL.value to the stored
> procedure.
> Alternatively you may be able to use a special value (say -1) and use
> NULLIF(@.parameter,-1) in your insert statement.
> John
> "Craig" wrote:
>|||Hi Craig,
Thanks for your post.
From your descriptions, I understood you would like to know how to how to
handle NULL with SQL Server in VS.NET. If I have misunderstood your
concern, please feel free to point it out.
To better answer this question, I would recommand you raise the question in
ADO.NET or VS.NET managed newsgroup.
microsoft.public.dotnet.framework.adonet
microsoft.public.dotnet.framework
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
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.|||Hi
This is almost certainly because you are passing as string "NULL" and not
NULL itself.
Try something like (although the syntax may not be correct!)
IF txtWaterDischargeCost.length > 0
.WaterDischargeCost = txtWaterDischargeCost.Text
ELSE
.WaterDischargeCost = System.DBNull
You can use SQL profiler to see the SQL statement sent to the database and
verify what the command actually is.
John
"Craig" wrote:

> Sample code.....
> With DataSetLocationsForm1.tblLocations(0)
> .WaterDischargeCost = txtWaterDischargeCost.Text
> .MoreFields = txtOtherTextboxes.Text
> End With
> SqlDataAdapter1.Update(DataSetLocationsForm1)
> The WaterDischargeCost field in the database is an type real. If they blan
k
> out the text box and click the SAVE button on an existing record I get an
> error "Can't convert NULL to integer"
> I want to put a null value back into the database field
> How do I do this?
> Thanks
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9FE6B6DA-00CE-448A-A740-2E950BC1AC57@.microsoft.com...
>
>|||If the column WaterDischargeCost is nullable (minoccurs = 0), then you can
use:
DataSetLocationsForm1.tblLocations(0).SetWaterDischargeCostNull()
to let the dataset set the value as null.
This works!!!!
"Craig" <NoSpam@.hotmail.com> wrote in message
news:O6UVrbCqFHA.3160@.TK2MSFTNGP14.phx.gbl...
> Sample code.....
> With DataSetLocationsForm1.tblLocations(0)
> .WaterDischargeCost = txtWaterDischargeCost.Text
> .MoreFields = txtOtherTextboxes.Text
> End With
> SqlDataAdapter1.Update(DataSetLocationsForm1)
> The WaterDischargeCost field in the database is an type real. If they
> blank out the text box and click the SAVE button on an existing record I
> get an error "Can't convert NULL to integer"
> I want to put a null value back into the database field
> How do I do this?
> Thanks
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9FE6B6DA-00CE-448A-A740-2E950BC1AC57@.microsoft.com...
>

NULL values

I have a text box on a web form in visual studio.net that a user enters a
number in.
If they blank out the text box then I want to put a null value back into the
database field.
I want to return the value to NULL like txtWaterDischargeRate = System.DBNull.Value instead of Nothing but I get an error
Can't convert NULL to integer
How can I enter a null in the database field on MS SQL Server?
ThanksHi
I am not sure what you are trying to do and I'm not a webform expert, but
you should be able to set may want to try something like checking the field's
data length in the application and passing DBNULL.value to the stored
procedure.
Alternatively you may be able to use a special value (say -1) and use
NULLIF(@.parameter,-1) in your insert statement.
John
"Craig" wrote:
> I have a text box on a web form in visual studio.net that a user enters a
> number in.
> If they blank out the text box then I want to put a null value back into the
> database field.
> I want to return the value to NULL like txtWaterDischargeRate => System.DBNull.Value instead of Nothing but I get an error
> Can't convert NULL to integer
> How can I enter a null in the database field on MS SQL Server?
> Thanks
>
>
>|||Sample code.....
With DataSetLocationsForm1.tblLocations(0)
.WaterDischargeCost = txtWaterDischargeCost.Text
.MoreFields = txtOtherTextboxes.Text
End With
SqlDataAdapter1.Update(DataSetLocationsForm1)
The WaterDischargeCost field in the database is an type real. If they blank
out the text box and click the SAVE button on an existing record I get an
error "Can't convert NULL to integer"
I want to put a null value back into the database field
How do I do this?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9FE6B6DA-00CE-448A-A740-2E950BC1AC57@.microsoft.com...
> Hi
> I am not sure what you are trying to do and I'm not a webform expert, but
> you should be able to set may want to try something like checking the
> field's
> data length in the application and passing DBNULL.value to the stored
> procedure.
> Alternatively you may be able to use a special value (say -1) and use
> NULLIF(@.parameter,-1) in your insert statement.
> John
> "Craig" wrote:
>> I have a text box on a web form in visual studio.net that a user enters a
>> number in.
>> If they blank out the text box then I want to put a null value back into
>> the
>> database field.
>> I want to return the value to NULL like txtWaterDischargeRate =>> System.DBNull.Value instead of Nothing but I get an error
>> Can't convert NULL to integer
>> How can I enter a null in the database field on MS SQL Server?
>> Thanks
>>
>>|||Hi Craig,
Thanks for your post.
From your descriptions, I understood you would like to know how to how to
handle NULL with SQL Server in VS.NET. If I have misunderstood your
concern, please feel free to point it out.
To better answer this question, I would recommand you raise the question in
ADO.NET or VS.NET managed newsgroup.
microsoft.public.dotnet.framework.adonet
microsoft.public.dotnet.framework
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
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.|||Hi
This is almost certainly because you are passing as string "NULL" and not
NULL itself.
Try something like (although the syntax may not be correct!)
IF txtWaterDischargeCost.length > 0
.WaterDischargeCost = txtWaterDischargeCost.Text
ELSE
.WaterDischargeCost = System.DBNull
You can use SQL profiler to see the SQL statement sent to the database and
verify what the command actually is.
John
"Craig" wrote:
> Sample code.....
> With DataSetLocationsForm1.tblLocations(0)
> .WaterDischargeCost = txtWaterDischargeCost.Text
> .MoreFields = txtOtherTextboxes.Text
> End With
> SqlDataAdapter1.Update(DataSetLocationsForm1)
> The WaterDischargeCost field in the database is an type real. If they blank
> out the text box and click the SAVE button on an existing record I get an
> error "Can't convert NULL to integer"
> I want to put a null value back into the database field
> How do I do this?
> Thanks
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9FE6B6DA-00CE-448A-A740-2E950BC1AC57@.microsoft.com...
> > Hi
> >
> > I am not sure what you are trying to do and I'm not a webform expert, but
> > you should be able to set may want to try something like checking the
> > field's
> > data length in the application and passing DBNULL.value to the stored
> > procedure.
> >
> > Alternatively you may be able to use a special value (say -1) and use
> > NULLIF(@.parameter,-1) in your insert statement.
> >
> > John
> >
> > "Craig" wrote:
> >
> >> I have a text box on a web form in visual studio.net that a user enters a
> >> number in.
> >>
> >> If they blank out the text box then I want to put a null value back into
> >> the
> >> database field.
> >>
> >> I want to return the value to NULL like txtWaterDischargeRate => >> System.DBNull.Value instead of Nothing but I get an error
> >>
> >> Can't convert NULL to integer
> >>
> >> How can I enter a null in the database field on MS SQL Server?
> >>
> >> Thanks
> >>
> >>
> >>
> >>
> >>
>
>|||If the column WaterDischargeCost is nullable (minoccurs = 0), then you can
use:
DataSetLocationsForm1.tblLocations(0).SetWaterDischargeCostNull()
to let the dataset set the value as null.
This works!!!!
"Craig" <NoSpam@.hotmail.com> wrote in message
news:O6UVrbCqFHA.3160@.TK2MSFTNGP14.phx.gbl...
> Sample code.....
> With DataSetLocationsForm1.tblLocations(0)
> .WaterDischargeCost = txtWaterDischargeCost.Text
> .MoreFields = txtOtherTextboxes.Text
> End With
> SqlDataAdapter1.Update(DataSetLocationsForm1)
> The WaterDischargeCost field in the database is an type real. If they
> blank out the text box and click the SAVE button on an existing record I
> get an error "Can't convert NULL to integer"
> I want to put a null value back into the database field
> How do I do this?
> Thanks
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9FE6B6DA-00CE-448A-A740-2E950BC1AC57@.microsoft.com...
>> Hi
>> I am not sure what you are trying to do and I'm not a webform expert, but
>> you should be able to set may want to try something like checking the
>> field's
>> data length in the application and passing DBNULL.value to the stored
>> procedure.
>> Alternatively you may be able to use a special value (say -1) and use
>> NULLIF(@.parameter,-1) in your insert statement.
>> John
>> "Craig" wrote:
>> I have a text box on a web form in visual studio.net that a user enters
>> a
>> number in.
>> If they blank out the text box then I want to put a null value back into
>> the
>> database field.
>> I want to return the value to NULL like txtWaterDischargeRate =>> System.DBNull.Value instead of Nothing but I get an error
>> Can't convert NULL to integer
>> How can I enter a null in the database field on MS SQL Server?
>> Thanks
>>
>>
>

NULL values

I have a text box on a web form in visual studio.net that a user enters a
number in.
If they blank out the text box then I want to put a null value back into the
database field.
I want to return the value to NULL like txtWaterDischargeRate =
System.DBNull.Value instead of Nothing but I get an error
Can't convert NULL to integer
How can I enter a null in the database field on MS SQL Server?
Thanks
Hi
I am not sure what you are trying to do and I'm not a webform expert, but
you should be able to set may want to try something like checking the field's
data length in the application and passing DBNULL.value to the stored
procedure.
Alternatively you may be able to use a special value (say -1) and use
NULLIF(@.parameter,-1) in your insert statement.
John
"Craig" wrote:

> I have a text box on a web form in visual studio.net that a user enters a
> number in.
> If they blank out the text box then I want to put a null value back into the
> database field.
> I want to return the value to NULL like txtWaterDischargeRate =
> System.DBNull.Value instead of Nothing but I get an error
> Can't convert NULL to integer
> How can I enter a null in the database field on MS SQL Server?
> Thanks
>
>
>
|||Sample code.....
With DataSetLocationsForm1.tblLocations(0)
.WaterDischargeCost = txtWaterDischargeCost.Text
.MoreFields = txtOtherTextboxes.Text
End With
SqlDataAdapter1.Update(DataSetLocationsForm1)
The WaterDischargeCost field in the database is an type real. If they blank
out the text box and click the SAVE button on an existing record I get an
error "Can't convert NULL to integer"
I want to put a null value back into the database field
How do I do this?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9FE6B6DA-00CE-448A-A740-2E950BC1AC57@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not sure what you are trying to do and I'm not a webform expert, but
> you should be able to set may want to try something like checking the
> field's
> data length in the application and passing DBNULL.value to the stored
> procedure.
> Alternatively you may be able to use a special value (say -1) and use
> NULLIF(@.parameter,-1) in your insert statement.
> John
> "Craig" wrote:
|||Hi Craig,
Thanks for your post.
From your descriptions, I understood you would like to know how to how to
handle NULL with SQL Server in VS.NET. If I have misunderstood your
concern, please feel free to point it out.
To better answer this question, I would recommand you raise the question in
ADO.NET or VS.NET managed newsgroup.
microsoft.public.dotnet.framework.adonet
microsoft.public.dotnet.framework
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
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.
|||Hi
This is almost certainly because you are passing as string "NULL" and not
NULL itself.
Try something like (although the syntax may not be correct!)
IF txtWaterDischargeCost.length > 0
..WaterDischargeCost = txtWaterDischargeCost.Text
ELSE
..WaterDischargeCost = System.DBNull
You can use SQL profiler to see the SQL statement sent to the database and
verify what the command actually is.
John
"Craig" wrote:

> Sample code.....
> With DataSetLocationsForm1.tblLocations(0)
> .WaterDischargeCost = txtWaterDischargeCost.Text
> .MoreFields = txtOtherTextboxes.Text
> End With
> SqlDataAdapter1.Update(DataSetLocationsForm1)
> The WaterDischargeCost field in the database is an type real. If they blank
> out the text box and click the SAVE button on an existing record I get an
> error "Can't convert NULL to integer"
> I want to put a null value back into the database field
> How do I do this?
> Thanks
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9FE6B6DA-00CE-448A-A740-2E950BC1AC57@.microsoft.com...
>
>
|||If the column WaterDischargeCost is nullable (minoccurs = 0), then you can
use:
DataSetLocationsForm1.tblLocations(0).SetWaterDisc hargeCostNull()
to let the dataset set the value as null.
This works!!!!
"Craig" <NoSpam@.hotmail.com> wrote in message
news:O6UVrbCqFHA.3160@.TK2MSFTNGP14.phx.gbl...
> Sample code.....
> With DataSetLocationsForm1.tblLocations(0)
> .WaterDischargeCost = txtWaterDischargeCost.Text
> .MoreFields = txtOtherTextboxes.Text
> End With
> SqlDataAdapter1.Update(DataSetLocationsForm1)
> The WaterDischargeCost field in the database is an type real. If they
> blank out the text box and click the SAVE button on an existing record I
> get an error "Can't convert NULL to integer"
> I want to put a null value back into the database field
> How do I do this?
> Thanks
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9FE6B6DA-00CE-448A-A740-2E950BC1AC57@.microsoft.com...
>

Null value handling in SSIS package

Hi All

I am facing this problem while loading data from text file into Table.

Scenario is -

There are chances of having spaces for null values in text file.

when i m trying to rum my SSIS package this is getting failed.

How can i avoid this problem? i want null values to be inserted if ther is spaces for that field in text file.

Thanks,

Anshu

Hi,

You can use a derived column transformation and check to see if your input column value has all spaces... trim and followed by len and if len == 0, set the column value to null.

Hope this helps...