Delay in running queries

I have a problem in running queries.

I developed an application uses sqlserver 2005 express edition

I create all queries in storedprocedures.every things work perfect but some times I get long delay in running queries. but after some minutes it comes regular . I coudlnt find any relation between delay and time of work.it comes by chance . also I set timeout for running query for 30 sec. but some times it took more than minutes.

what should I do?

It sounds like you are experience 'blocking' and 'locking' behavior. If you post the complete stored procedure code, we 'may' be able to help you.

And you could also use Profiler to determine what procedures and/or statements in the procedures are causing the blocks.

|||

my data base has so many procudures and this problem may happend in running each one . one simple of them is :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[STPListStock]

AS

SELECT GoodSyscode, Title, Code, BarCode, Type, FstUnit, SecUnit, UnitRate, OrderPoint, SalePrice1, SalePrice2, SalePrice3, SalePrice4, SalePrice5,

VisitorPer, Comment, DiscontPer, UserPrice, GroupName1, GroupName2, Term, Weight, SerialNo, TypeId, GroupId1, GroupId2

FROM VWCompleteGood

|||

try this

EXEC sp_dboption '<db_name>', 'autoclose', 'false';

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
QuentinThe parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or even
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> > Hi group,
> >
> > I am seeing degraded performance after applying SP4 on SQL 2000.
> >
> > My specific queries (they are in stored proc) access linked server. When
> > the process is run, I keep seeing blocks on the linked server side among
> > processes of the same spid spun off from the calling process like this:
> >
> > A. a process (say spid 58) on initiating server runs, some times shows
> > blocked by a process (say spid 111) on the linked server, some times not
> > displaying the block but just continue to run slow;
> >
> > B. a few processes (spid 111) on the linked server running, often showing
> > one or two of them blocked by another spid 111.
> >
> > I have googled and found that there are reports of such slowed down
> > performance after SP4 application, not necessarily related to the use of
> > linked server. There is an explanation by Microsoft why processes could
> > show being blocked by process of the same spid. Problem is that it looks
> > like no solution to the problem and nor explanation why the switch of
> > locks is so slow.
> > From what I read other people report, this blocking among processes of the
> > same spid repeats itself so often and is so slow that you can easily catch
> > the display of
> > such blocks in EM. This is the same case by me: when the process is
> > running, I
> > easily see such blocks with refresh of EM.
> >
> > My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> > only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> > checked on my systems and is set up properly.
> >
> > Wish to hear from you.
> >
> > Quentin
> >
> >
> >|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegroups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>> The parallel process blocking occurred long before you applied SP4.
>> Microsoft added visibility to that blocking in SP4 so DBAs could manage
>> parallelism better. Reduce the degree of parallelism on your query, or
>> even
>> server-wide, and you will see the self-blocking go away.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
>> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
>> > Hi group,
>> >
>> > I am seeing degraded performance after applying SP4 on SQL 2000.
>> >
>> > My specific queries (they are in stored proc) access linked server.
>> > When
>> > the process is run, I keep seeing blocks on the linked server side
>> > among
>> > processes of the same spid spun off from the calling process like this:
>> >
>> > A. a process (say spid 58) on initiating server runs, some times shows
>> > blocked by a process (say spid 111) on the linked server, some times
>> > not
>> > displaying the block but just continue to run slow;
>> >
>> > B. a few processes (spid 111) on the linked server running, often
>> > showing
>> > one or two of them blocked by another spid 111.
>> >
>> > I have googled and found that there are reports of such slowed down
>> > performance after SP4 application, not necessarily related to the use
>> > of
>> > linked server. There is an explanation by Microsoft why processes
>> > could
>> > show being blocked by process of the same spid. Problem is that it
>> > looks
>> > like no solution to the problem and nor explanation why the switch of
>> > locks is so slow.
>> > From what I read other people report, this blocking among processes of
>> > the
>> > same spid repeats itself so often and is so slow that you can easily
>> > catch
>> > the display of
>> > such blocks in EM. This is the same case by me: when the process is
>> > running, I
>> > easily see such blocks with refresh of EM.
>> >
>> > My process used to run about 30 sec on SP3a, now it runs some 15 min.
>> > The
>> > only troubleshoot hint I saw sofar is the correct setup of MS DTC,
>> > which I
>> > checked on my systems and is set up properly.
>> >
>> > Wish to hear from you.
>> >
>> > Quentin
>> >
>> >
>> >
>|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.org

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
Quentin
The parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>
|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic...94&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:[vbcol=seagreen]
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or even
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegr oups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic...94&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>
|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.
|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/archi...02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.org

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
QuentinThe parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic...294&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:[vbcol=seagreen]
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or ev
en
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegroups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic...294&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/arch.../02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.orgsql

Default Values in Queries

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

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

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

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