Nulls Versus Blanks PArt 2

How do I configure SQL 2000 to insert <NULLS> when deleting an entry and not
emty strings. On the same Token the server should insert <NULLS> by default
My server was configured like that and for some reason now it is behaving
differently. There has to be some global configuration, I am not looking for
individual constraints and default values. If there is no global
configuration am I looking at some kind of bug?
Thanks
IT PHYTOSAN>>How do I configure SQL 2000 to insert <NULLS> when deleting an entry
I assume by deleting you don't mean deleting the whole row like this
DELETE table where SomeColumn =SomeValue
Are you talking about EM (Enterprise Manager) ?
If so then enter CTRL + 0 in the column and a NULL will be inserted
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||> How do I configure SQL 2000 to insert <NULLS> when deleting an entry and not
> emty strings.
There's no such setting as this never happens. Let me explain:
If you by "deleting an entry" mean deleting a row, using the DELETE SQL command, then the row is
gone and there is no place for NULL nor space.
If you mean changing the value of a column for a row, then you set this using the UPDATE statement:
UPDATE tblname
SET colname = NULL
WHERE ...
So you need to dig in the application to see how it modifies the data.
Possibly you has a default constraint for a column, with NULL and that default is not an empty
string. You can refer to a default for a column in an update like:
UPDATE tblname
SET colname = DEFAULT
WHERE ...
See if you do have any default for this column using sp_help.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:FD708941-A75E-4C00-8D3F-706FFD2E7744@.microsoft.com...
> How do I configure SQL 2000 to insert <NULLS> when deleting an entry and not
> emty strings. On the same Token the server should insert <NULLS> by default
> My server was configured like that and for some reason now it is behaving
> differently. There has to be some global configuration, I am not looking for
> individual constraints and default values. If there is no global
> configuration am I looking at some kind of bug?
> Thanks
> IT PHYTOSAN|||Thanks for your help. Looking at the source of the issue, what I would really
like to accomplish is that varchar and char entries treat empty strings as
<NULL>'s. When deleting a single field (using the delete button on the
keyboard in a table opended enterprise manager for example) I would like to
see a <NULL> inserted rather than an empty string. There must be a simple way
of enforcing meaningfull entries in the tables.
any hints?
IT PHYTOSAN
"Tibor Karaszi" wrote:
> > How do I configure SQL 2000 to insert <NULLS> when deleting an entry and not
> > emty strings.
> There's no such setting as this never happens. Let me explain:
> If you by "deleting an entry" mean deleting a row, using the DELETE SQL command, then the row is
> gone and there is no place for NULL nor space.
> If you mean changing the value of a column for a row, then you set this using the UPDATE statement:
> UPDATE tblname
> SET colname = NULL
> WHERE ...
> So you need to dig in the application to see how it modifies the data.
> Possibly you has a default constraint for a column, with NULL and that default is not an empty
> string. You can refer to a default for a column in an update like:
> UPDATE tblname
> SET colname = DEFAULT
> WHERE ...
> See if you do have any default for this column using sp_help.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
> news:FD708941-A75E-4C00-8D3F-706FFD2E7744@.microsoft.com...
> > How do I configure SQL 2000 to insert <NULLS> when deleting an entry and not
> > emty strings. On the same Token the server should insert <NULLS> by default
> > My server was configured like that and for some reason now it is behaving
> > differently. There has to be some global configuration, I am not looking for
> > individual constraints and default values. If there is no global
> > configuration am I looking at some kind of bug?
> >
> > Thanks
> >
> > IT PHYTOSAN
>|||To create NULL values from Enterprise Manager use CTRL + 0 instead of
delete
Hightlight all the text and then press CTRL + 0
Or switch to Query analyzer and write UPDATE statements like Tibor
showed you that will also give you more control and you will know
what's going on
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I am aware of CTRL + 0 but it does not fix my problem. Right now my client
(InfoPath) will insert empty strings in tables that have no entry and does
not recognize that this field is required but empty. I would like SQL server
to treat empty strings like <NULLS> as mentioned.
Thanks
IT PHYTOSAN
"SQL" wrote:
> To create NULL values from Enterprise Manager use CTRL + 0 instead of
> delete
> Hightlight all the text and then press CTRL + 0
> Or switch to Query analyzer and write UPDATE statements like Tibor
> showed you that will also give you more control and you will know
> what's going on
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||create a trigger on the table|||that won't be recognized by InfoPath as required field. The trigger will
throw an error but for me that's after the fact.
Thanks
IT PHYTOSAN
"SQL" wrote:
> create a trigger on the table
>|||As suggested, this is an application (InfoPath) problem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:0AF6F82F-CC92-4F15-A988-779391530969@.microsoft.com...
>I am aware of CTRL + 0 but it does not fix my problem. Right now my client
> (InfoPath) will insert empty strings in tables that have no entry and does
> not recognize that this field is required but empty. I would like SQL server
> to treat empty strings like <NULLS> as mentioned.
> Thanks
> IT PHYTOSAN
> "SQL" wrote:
>> To create NULL values from Enterprise Manager use CTRL + 0 instead of
>> delete
>> Hightlight all the text and then press CTRL + 0
>> Or switch to Query analyzer and write UPDATE statements like Tibor
>> showed you that will also give you more control and you will know
>> what's going on
>>
>> Denis the SQL Menace
>> http://sqlservercode.blogspot.com/
>>|||Wow I am really stuck now, they can't help me over there either. Any clues?
Thanks anyway
IT PHYTOSAN
"Tibor Karaszi" wrote:
> As suggested, this is an application (InfoPath) problem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
> news:0AF6F82F-CC92-4F15-A988-779391530969@.microsoft.com...
> >I am aware of CTRL + 0 but it does not fix my problem. Right now my client
> > (InfoPath) will insert empty strings in tables that have no entry and does
> > not recognize that this field is required but empty. I would like SQL server
> > to treat empty strings like <NULLS> as mentioned.
> >
> > Thanks
> >
> > IT PHYTOSAN
> >
> > "SQL" wrote:
> >
> >> To create NULL values from Enterprise Manager use CTRL + 0 instead of
> >> delete
> >> Hightlight all the text and then press CTRL + 0
> >> Or switch to Query analyzer and write UPDATE statements like Tibor
> >> showed you that will also give you more control and you will know
> >> what's going on
> >>
> >>
> >> Denis the SQL Menace
> >> http://sqlservercode.blogspot.com/
> >>
> >>
>|||You may use a INSTEAD OF type of trigger to do your data validation.
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:E1BDC16A-E880-4919-95CE-FCEA4DE3EC7C@.microsoft.com...
> that won't be recognized by InfoPath as required field. The trigger will
> throw an error but for me that's after the fact.
> Thanks
> IT PHYTOSAN
> "SQL" wrote:
>> create a trigger on the table
>>|||I suggest you open a case with MS Support. If you don't know what caused the change, or even if the
change was at the database level, it is hard to suggest anything. If the change was at the database
level, and you don't know who did it any what they did and don't know what the database definition
is supposed to look like, it is hard to tell anything. MS Support should be able to track this down
for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:3D54C44E-093D-4A78-997F-7E49AECB4D12@.microsoft.com...
> Wow I am really stuck now, they can't help me over there either. Any clues?
> Thanks anyway
> IT PHYTOSAN
> "Tibor Karaszi" wrote:
>> As suggested, this is an application (InfoPath) problem.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
>> news:0AF6F82F-CC92-4F15-A988-779391530969@.microsoft.com...
>> >I am aware of CTRL + 0 but it does not fix my problem. Right now my client
>> > (InfoPath) will insert empty strings in tables that have no entry and does
>> > not recognize that this field is required but empty. I would like SQL server
>> > to treat empty strings like <NULLS> as mentioned.
>> >
>> > Thanks
>> >
>> > IT PHYTOSAN
>> >
>> > "SQL" wrote:
>> >
>> >> To create NULL values from Enterprise Manager use CTRL + 0 instead of
>> >> delete
>> >> Hightlight all the text and then press CTRL + 0
>> >> Or switch to Query analyzer and write UPDATE statements like Tibor
>> >> showed you that will also give you more control and you will know
>> >> what's going on
>> >>
>> >>
>> >> Denis the SQL Menace
>> >> http://sqlservercode.blogspot.com/
>> >>
>> >>
>>|||That is good adivce. I'll do that and post back if the solution can be found.
Thans again for your help to both of you.
IT PHYTOSAN
"Tibor Karaszi" wrote:
> I suggest you open a case with MS Support. If you don't know what caused the change, or even if the
> change was at the database level, it is hard to suggest anything. If the change was at the database
> level, and you don't know who did it any what they did and don't know what the database definition
> is supposed to look like, it is hard to tell anything. MS Support should be able to track this down
> for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
> news:3D54C44E-093D-4A78-997F-7E49AECB4D12@.microsoft.com...
> > Wow I am really stuck now, they can't help me over there either. Any clues?
> >
> > Thanks anyway
> >
> > IT PHYTOSAN
> >
> > "Tibor Karaszi" wrote:
> >
> >> As suggested, this is an application (InfoPath) problem.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
> >> news:0AF6F82F-CC92-4F15-A988-779391530969@.microsoft.com...
> >> >I am aware of CTRL + 0 but it does not fix my problem. Right now my client
> >> > (InfoPath) will insert empty strings in tables that have no entry and does
> >> > not recognize that this field is required but empty. I would like SQL server
> >> > to treat empty strings like <NULLS> as mentioned.
> >> >
> >> > Thanks
> >> >
> >> > IT PHYTOSAN
> >> >
> >> > "SQL" wrote:
> >> >
> >> >> To create NULL values from Enterprise Manager use CTRL + 0 instead of
> >> >> delete
> >> >> Hightlight all the text and then press CTRL + 0
> >> >> Or switch to Query analyzer and write UPDATE statements like Tibor
> >> >> showed you that will also give you more control and you will know
> >> >> what's going on
> >> >>
> >> >>
> >> >> Denis the SQL Menace
> >> >> http://sqlservercode.blogspot.com/
> >> >>
> >> >>
> >>
> >>
>