Error 18456

I got an error of login "sa" with Error 18456 Level 14 . Server A is the MSSQL Server where the database sit and the other Server B is just with MSSQL Server tool which act as a Terminal Service and have the Accounting applications.The Accounting applicat
ion unable to perform its database transfer to another application on the Terminal Service. We use Window 2003 Server. The application programs are fine. The ODBC, MSSQL and application "sa" password are identical. Any idea? Thanks
Hi
Please enable security Audit, and check the detailed error in the security
event log.
To me it looks like a double hop issue, please check the link mentioned
below
http://msdn.microsoft.com/library/de...urity_2gmm.htm
HTH
Regards
Sadeesh
This posting is provided AS IS with no warranties, and confers no rights.
"Li" <Li@.discussions.microsoft.com> wrote in message
news:391C4119-F467-4017-8D97-960644E93B91@.microsoft.com...
>I got an error of login "sa" with Error 18456 Level 14 . Server A is the
>MSSQL Server where the database sit and the other Server B is just with
>MSSQL Server tool which act as a Terminal Service and have the Accounting
>applications.The Accounting application unable to perform its database
>transfer to another application on the Terminal Service. We use Window 2003
>Server. The application programs are fine. The ODBC, MSSQL and application
>"sa" password are identical. Any idea? Thanks
>

Error 18456

I got an error of login "sa" with Error 18456 Level 14 . Server A is the MSSQL Server where the database sit and the other Server B is just with MSSQL Server tool which act as a Terminal Service and have the Accounting applications.The Accounting application unable to perform its database transfer to another application on the Terminal Service. We use Window 2003 Server. The application programs are fine. The ODBC, MSSQL and application "sa" password are identical. Any idea? ThanksHi
Please enable security Audit, and check the detailed error in the security
event log.
To me it looks like a double hop issue, please check the link mentioned
below
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/ad_security_2gmm.htm
HTH
Regards
Sadeesh
--
This posting is provided AS IS with no warranties, and confers no rights.
"Li" <Li@.discussions.microsoft.com> wrote in message
news:391C4119-F467-4017-8D97-960644E93B91@.microsoft.com...
>I got an error of login "sa" with Error 18456 Level 14 . Server A is the
>MSSQL Server where the database sit and the other Server B is just with
>MSSQL Server tool which act as a Terminal Service and have the Accounting
>applications.The Accounting application unable to perform its database
>transfer to another application on the Terminal Service. We use Window 2003
>Server. The application programs are fine. The ODBC, MSSQL and application
>"sa" password are identical. Any idea? Thanks
>sql

Error 18456

I got an error of login "sa" with Error 18456 Level 14 . Server A is the MSS
QL Server where the database sit and the other Server B is just with MSSQL S
erver tool which act as a Terminal Service and have the Accounting applicati
ons.The Accounting applicat
ion unable to perform its database transfer to another application on the Te
rminal Service. We use Window 2003 Server. The application programs are fine
. The ODBC, MSSQL and application "sa" password are identical. Any idea? Tha
nksHi
Please enable security Audit, and check the detailed error in the security
event log.
To me it looks like a double hop issue, please check the link mentioned
below
mm.htm" target="_blank">http://msdn.microsoft.com/library/d...g
mm.htm
HTH
Regards
Sadeesh
--
This posting is provided AS IS with no warranties, and confers no rights.
"Li" <Li@.discussions.microsoft.com> wrote in message
news:391C4119-F467-4017-8D97-960644E93B91@.microsoft.com...
>I got an error of login "sa" with Error 18456 Level 14 . Server A is the
>MSSQL Server where the database sit and the other Server B is just with
>MSSQL Server tool which act as a Terminal Service and have the Accounting
>applications.The Accounting application unable to perform its database
>transfer to another application on the Terminal Service. We use Window 2003
>Server. The application programs are fine. The ODBC, MSSQL and application
>"sa" password are identical. Any idea? Thanks
>

error 17052 : database model cannot be opened, it has been marked as suspected by recovery

Hello,
I have a annoying error with keep me off starting MSSQL Server.
I get a
error 17052 : database model cannot be opened, it has been marked as
suspected by recovery
We made a Vertias MSSQL Backup wished crashed before nd maybe this db is
locked because of this, but in order to recover that model database from a
backup we need the SQL Server in a running condition
Starting it manually with c:\Program Files\Microsft SQL
Server\binn\sqlserver -m doesn't bring it up.
In the log you get this entry:
spid5 Bypassing recovery for database 'model' because it is marked IN
LOAD.
Can anyone help?
Thanks in advance
Wolfgang R.| Hello,
|
| I have a annoying error with keep me off starting MSSQL Server.
| I get a
| error 17052 : database model cannot be opened, it has been marked as
| suspected by recovery
|
| We made a Vertias MSSQL Backup wished crashed before nd maybe this db is
| locked because of this, but in order to recover that model database from a
| backup we need the SQL Server in a running condition
|
| Starting it manually with c:\Program Files\Microsft SQL
| Server\binn\sqlserver -m doesn't bring it up.
| In the log you get this entry:
|
| spid5 Bypassing recovery for database 'model' because it is marked IN
| LOAD.
|
| Can anyone help?
| Thanks in advance
| Wolfgang R.
--
Hi Wolfgang,
Try starting SQL Server with the "-f" option: minimal configuration. That
should bypass your model database problem.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Hello Eric,
No unfortunately this doesn't help. The server isn't starting.
What is the reason for that problem? Is the DB locked or is it damaged?
Wolfgang
"Eric Cardenas" <ecard@.anonymous.com> schrieb im Newsbeitrag
news:3fzivn3wDHA.3384@.cpmsftngxa07.phx.gbl...
> | Hello,
> |
> | I have a annoying error with keep me off starting MSSQL Server.
> | I get a
> | error 17052 : database model cannot be opened, it has been marked as
> | suspected by recovery
> |
> | We made a Vertias MSSQL Backup wished crashed before nd maybe this db is
> | locked because of this, but in order to recover that model database from
a
> | backup we need the SQL Server in a running condition
> |
> | Starting it manually with c:\Program Files\Microsft SQL
> | Server\binn\sqlserver -m doesn't bring it up.
> | In the log you get this entry:
> |
> | spid5 Bypassing recovery for database 'model' because it is marked
IN
> | LOAD.
> |
> | Can anyone help?
> | Thanks in advance
> | Wolfgang R.
> --
> Hi Wolfgang,
> Try starting SQL Server with the "-f" option: minimal configuration. That
> should bypass your model database problem.
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>|||| Hello Eric,
|
| No unfortunately this doesn't help. The server isn't starting.
|
| What is the reason for that problem? Is the DB locked or is it damaged?
|
| Wolfgang
--
Hi Wolfgang,
Add the trace flag "-t3608" in the startup parameters. This trace flag will
bypass recovery of databases except master. You should be able to start SQL
Server after this. Then you can fix the model db problem.
Hope this helps,
--
Eric Cárdenas
SQL Server support

error 10005 dbprocess is dead or not enabled

MsSQL Server 2000
PowerBuilder 8 Application
20 client on LAN ethernet
2 client on 64 K router-to-router LAN

on 2 remote client I have this error.

There are two router Cisco whit idle-timeout on 1800 sec, but evry 1/2
minutes on the 2 remote clients there are the error: 10005 dbprocess
is dead or not enabled.

this is a new problem after migration from SQL Server 6.5-PB7 to Sql
server 2000-PB8.

Thanks
Maxmdalica (mdalica@.libero.it) writes:
> MsSQL Server 2000
> PowerBuilder 8 Application
> 20 client on LAN ethernet
> 2 client on 64 K router-to-router LAN
> on 2 remote client I have this error.
> There are two router Cisco whit idle-timeout on 1800 sec, but evry 1/2
> minutes on the 2 remote clients there are the error: 10005 dbprocess
> is dead or not enabled.
> this is a new problem after migration from SQL Server 6.5-PB7 to Sql
> server 2000-PB8.

Very difficult to diagnose remotely. It's even very difficult to
diagnose locally.

The message itself, is fairly generic. It could be due to a crash on
the SQL Server side, so check the SQL Server errorlog for stack dumps
or other messages. But since you get it so frequently, I would guess
this is a network problem of some sort.

I'm by no means a network knowledgable person, so I can only give
general tips: check the Windows event log, both on client and server.
Trying using different communications protocols. (You can change this
in the Client Network Utility and the Server Network Utility.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93F453D7825DYazorman@.127.0.0.1>...
> mdalica (mdalica@.libero.it) writes:
> > MsSQL Server 2000
> > PowerBuilder 8 Application
> > 20 client on LAN ethernet
> > 2 client on 64 K router-to-router LAN
> > on 2 remote client I have this error.
> > There are two router Cisco whit idle-timeout on 1800 sec, but evry 1/2
> > minutes on the 2 remote clients there are the error: 10005 dbprocess
> > is dead or not enabled.
> > this is a new problem after migration from SQL Server 6.5-PB7 to Sql
> > server 2000-PB8.
> Very difficult to diagnose remotely. It's even very difficult to
> diagnose locally.
> The message itself, is fairly generic. It could be due to a crash on
> the SQL Server side, so check the SQL Server errorlog for stack dumps
> or other messages. But since you get it so frequently, I would guess
> this is a network problem of some sort.
> I'm by no means a network knowledgable person, so I can only give
> general tips: check the Windows event log, both on client and server.
> Trying using different communications protocols. (You can change this
> in the Client Network Utility and the Server Network Utility.)

There are not error on server and client logs, the server run
correctly, the local clients run correctly. I think that the problems
is instable connection, bat I don't know a possible solution.

Erro message during import of access DB...

Hello All!
I am getting an error whle I try to import an Access DB into a MSSQL DB.
Insert Error, ('DATEBIRTH', DBTYPE_DBTIMESTAMP), status 6; Data
Overflow. Invalid character value for cast specification.
Now, during the DTS wizard I tried changing the types to smalldate, or
just plain text but neither work.
any thoughts?
thanks!
Luis
Hi
Usually this is caused by incorrect data. You may want to load into a
staging table and make the date column char or validate the data before
loading.
http://groups-beta.google.com/group/...514b83c7397d2c
John
"Anon" wrote:

> Hello All!
> I am getting an error whle I try to import an Access DB into a MSSQL DB.
> Insert Error, ('DATEBIRTH', DBTYPE_DBTIMESTAMP), status 6; Data
> Overflow. Invalid character value for cast specification.
> Now, during the DTS wizard I tried changing the types to smalldate, or
> just plain text but neither work.
> any thoughts?
> thanks!
> Luis
>
|||hmmm. that stinks since we have almost 100k records in this table...mayb
eI can make some sort of update query to these records. Ok, thanks!
Luis
John Bell wrote:[vbcol=seagreen]
> Hi
> Usually this is caused by incorrect data. You may want to load into a
> staging table and make the date column char or validate the data before
> loading.
> http://groups-beta.google.com/group/...514b83c7397d2c
> John
> "Anon" wrote:
>
|||Hi
It may highlight a deficiency in the current application, therefore any
clean up operation would have to be repeated each time you loaded your
SQL server database. Finding out why you have poor data would be
necessary to ensure a smoother transfer in the future. If you loaded
into staging tables your clean-up code could be used when transforming
that data into the live tables, this would mean you are not depending
on the current data source.
100K records is not actually that many, hopefully not all of them are
duff!!
John
Anon wrote:
> hmmm. that stinks since we have almost 100k records in this
table...mayb[vbcol=seagreen]
> eI can make some sort of update query to these records. Ok, thanks!
> Luis
>
> John Bell wrote:
a[vbcol=seagreen]
before[vbcol=seagreen]
http://groups-beta.google.com/group/...514b83c7397d2c[vbcol=seagreen]
MSSQL DB.[vbcol=seagreen]
or[vbcol=seagreen]

Erro message during import of access DB...

Hello All!
I am getting an error whle I try to import an Access DB into a MSSQL DB.
Insert Error, ('DATEBIRTH', DBTYPE_DBTIMESTAMP), status 6; Data
Overflow. Invalid character value for cast specification.
Now, during the DTS wizard I tried changing the types to smalldate, or
just plain text but neither work.
any thoughts?
thanks!
LuisHi
Usually this is caused by incorrect data. You may want to load into a
staging table and make the date column char or validate the data before
loading.
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/c7514b83c7397d2c
John
"Anon" wrote:
> Hello All!
> I am getting an error whle I try to import an Access DB into a MSSQL DB.
> Insert Error, ('DATEBIRTH', DBTYPE_DBTIMESTAMP), status 6; Data
> Overflow. Invalid character value for cast specification.
> Now, during the DTS wizard I tried changing the types to smalldate, or
> just plain text but neither work.
> any thoughts?
> thanks!
> Luis
>|||hmmm. that stinks since we have almost 100k records in this table...mayb
eI can make some sort of update query to these records. Ok, thanks!
Luis
John Bell wrote:
> Hi
> Usually this is caused by incorrect data. You may want to load into a
> staging table and make the date column char or validate the data before
> loading.
> http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/c7514b83c7397d2c
> John
> "Anon" wrote:
>
>>Hello All!
>>I am getting an error whle I try to import an Access DB into a MSSQL DB.
>>Insert Error, ('DATEBIRTH', DBTYPE_DBTIMESTAMP), status 6; Data
>>Overflow. Invalid character value for cast specification.
>>Now, during the DTS wizard I tried changing the types to smalldate, or
>>just plain text but neither work.
>>any thoughts?
>>thanks!
>>Luis|||Hi
It may highlight a deficiency in the current application, therefore any
clean up operation would have to be repeated each time you loaded your
SQL server database. Finding out why you have poor data would be
necessary to ensure a smoother transfer in the future. If you loaded
into staging tables your clean-up code could be used when transforming
that data into the live tables, this would mean you are not depending
on the current data source.
100K records is not actually that many, hopefully not all of them are
duff!!
John
Anon wrote:
> hmmm. that stinks since we have almost 100k records in this
table...mayb
> eI can make some sort of update query to these records. Ok, thanks!
> Luis
>
> John Bell wrote:
> > Hi
> >
> > Usually this is caused by incorrect data. You may want to load into
a
> > staging table and make the date column char or validate the data
before
> > loading.
> >
> >
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/c7514b83c7397d2c
> >
> > John
> >
> > "Anon" wrote:
> >
> >
> >>Hello All!
> >>I am getting an error whle I try to import an Access DB into a
MSSQL DB.
> >>
> >>Insert Error, ('DATEBIRTH', DBTYPE_DBTIMESTAMP), status 6; Data
> >>Overflow. Invalid character value for cast specification.
> >>
> >>Now, during the DTS wizard I tried changing the types to smalldate,
or
> >>just plain text but neither work.
> >>
> >>any thoughts?
> >>
> >>thanks!
> >>
> >>Luis
> >>

Erro message during import of access DB...

Hello All!
I am getting an error whle I try to import an Access DB into a MSSQL DB.
Insert Error, ('DATEBIRTH', DBTYPE_DBTIMESTAMP), status 6; Data
Overflow. Invalid character value for cast specification.
Now, during the DTS wizard I tried changing the types to smalldate, or
just plain text but neither work.
any thoughts?
thanks!
LuisHi
Usually this is caused by incorrect data. You may want to load into a
staging table and make the date column char or validate the data before
loading.
http://groups-beta.google.com/group...7397d
2c
John
"Anon" wrote:

> Hello All!
> I am getting an error whle I try to import an Access DB into a MSSQL DB.
> Insert Error, ('DATEBIRTH', DBTYPE_DBTIMESTAMP), status 6; Data
> Overflow. Invalid character value for cast specification.
> Now, during the DTS wizard I tried changing the types to smalldate, or
> just plain text but neither work.
> any thoughts?
> thanks!
> Luis
>|||hmmm. that stinks since we have almost 100k records in this table...mayb
eI can make some sort of update query to these records. Ok, thanks!
Luis
John Bell wrote:[vbcol=seagreen]
> Hi
> Usually this is caused by incorrect data. You may want to load into a
> staging table and make the date column char or validate the data before
> loading.
> http://groups-beta.google.com/group...739
7d2c
> John
> "Anon" wrote:
>|||Hi
It may highlight a deficiency in the current application, therefore any
clean up operation would have to be repeated each time you loaded your
SQL server database. Finding out why you have poor data would be
necessary to ensure a smoother transfer in the future. If you loaded
into staging tables your clean-up code could be used when transforming
that data into the live tables, this would mean you are not depending
on the current data source.
100K records is not actually that many, hopefully not all of them are
duff!!
John
Anon wrote:
> hmmm. that stinks since we have almost 100k records in this
table...mayb[vbcol=seagreen]
> eI can make some sort of update query to these records. Ok, thanks!
> Luis
>
> John Bell wrote:
a[vbcol=seagreen]
before[vbcol=seagreen]
http://groups-beta.google.com/group...7514b83c7397d2c[vbcol=s
eagreen]
MSSQL DB.[vbcol=seagreen]
or[vbcol=seagreen]

Erratic Performance on 5,000,000 records

Hi
I have a mssql 2000 database with 5 mil records in a table and the
performance seems to change from one day to the next from 5 sec result set
returns to 30sec. If i change the index sort order or sometimes move the
position of one of the lines in the "where" statement then it improves to 5
sec only to change the next day or two to 30 sec again.
Other variables like loading on the server, db etc are constant - it;s a
test system still so no users hitting DB.
Simple things like changing the position of the "null" condition below
improved things yesterday but today it's slow (3 sec to 30sec).
eg:
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
worked well yesterday after being the other way around but now does not.
(This is for a param that could be passed thru as null or with a value)
Thanks
MikeHi,
According to information you provide i can only say that
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
is not SARGABLE. It will perform Index Scan instead of Index Seek. If you're
index will grow or get fragmented you will have more costs.
Did you run this code in Stored Procedure?
I recommend you to use Dynamic SQL or if this is a SP then use wrapper sp
according to parameter passed to sp .
Many of the programmers using this kind of code but this type of usage is
easy to write for programmers but bad for performance.
Hope this helps.
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to
5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>|||Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to
5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>|||Again this will perform index scan ..
(Assuming you have an index on DEPTCODE column)
"Saket" wrote:
[vbcol=seagreen]
> Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
> "Mike C" wrote:
>|||If column DeptCode does not contain NULLs, then you can use the
following statement. It assumes DeptCode is of datatype varchar. If is
of a different data type, then post back.
WHERE CL.DeptCode LIKE COALESCE(@.DeptCode,'%')
Hope this helps,
Gert-Jan
Mike C wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to
5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike

Erratic Performance on 5,000,000 records

Hi
I have a mssql 2000 database with 5 mil records in a table and the
performance seems to change from one day to the next from 5 sec result set
returns to 30sec. If i change the index sort order or sometimes move the
position of one of the lines in the "where" statement then it improves to 5
sec only to change the next day or two to 30 sec again.
Other variables like loading on the server, db etc are constant - it;s a
test system still so no users hitting DB.
Simple things like changing the position of the "null" condition below
improved things yesterday but today it's slow (3 sec to 30sec).
eg:
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
worked well yesterday after being the other way around but now does not.
(This is for a param that could be passed thru as null or with a value)
Thanks
Mike
Hi,
According to information you provide i can only say that
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
is not SARGABLE. It will perform Index Scan instead of Index Seek. If you're
index will grow or get fragmented you will have more costs.
Did you run this code in Stored Procedure?
I recommend you to use Dynamic SQL or if this is a SP then use wrapper sp
according to parameter passed to sp .
Many of the programmers using this kind of code but this type of usage is
easy to write for programmers but bad for performance.
Hope this helps.
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>
|||Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>
|||Again this will perform index scan ..
(Assuming you have an index on DEPTCODE column)
"Saket" wrote:
[vbcol=seagreen]
> Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
> "Mike C" wrote:
|||If column DeptCode does not contain NULLs, then you can use the
following statement. It assumes DeptCode is of datatype varchar. If is
of a different data type, then post back.
WHERE CL.DeptCode LIKE COALESCE(@.DeptCode,'%')
Hope this helps,
Gert-Jan
Mike C wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike

Erratic Performance on 5,000,000 records

Hi
I have a mssql 2000 database with 5 mil records in a table and the
performance seems to change from one day to the next from 5 sec result set
returns to 30sec. If i change the index sort order or sometimes move the
position of one of the lines in the "where" statement then it improves to 5
sec only to change the next day or two to 30 sec again.
Other variables like loading on the server, db etc are constant - it;s a
test system still so no users hitting DB.
Simple things like changing the position of the "null" condition below
improved things yesterday but today it's slow (3 sec to 30sec).
eg:
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
worked well yesterday after being the other way around but now does not.
(This is for a param that could be passed thru as null or with a value)
Thanks
MikeHi,
According to information you provide i can only say that
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
is not SARGABLE. It will perform Index Scan instead of Index Seek. If you're
index will grow or get fragmented you will have more costs.
Did you run this code in Stored Procedure?
I recommend you to use Dynamic SQL or if this is a SP then use wrapper sp
according to parameter passed to sp .
Many of the programmers using this kind of code but this type of usage is
easy to write for programmers but bad for performance.
Hope this helps.
"Mike C" wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>|||Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
"Mike C" wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>|||Again this will perform index scan ..
(Assuming you have an index on DEPTCODE column)
"Saket" wrote:
> Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
> "Mike C" wrote:
> > Hi
> >
> > I have a mssql 2000 database with 5 mil records in a table and the
> > performance seems to change from one day to the next from 5 sec result set
> > returns to 30sec. If i change the index sort order or sometimes move the
> > position of one of the lines in the "where" statement then it improves to 5
> > sec only to change the next day or two to 30 sec again.
> >
> > Other variables like loading on the server, db etc are constant - it;s a
> > test system still so no users hitting DB.
> >
> > Simple things like changing the position of the "null" condition below
> > improved things yesterday but today it's slow (3 sec to 30sec).
> >
> > eg:
> >
> > WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> >
> > worked well yesterday after being the other way around but now does not.
> > (This is for a param that could be passed thru as null or with a value)
> >
> > Thanks
> >
> > Mike
> >
> >
> >|||If column DeptCode does not contain NULLs, then you can use the
following statement. It assumes DeptCode is of datatype varchar. If is
of a different data type, then post back.
WHERE CL.DeptCode LIKE COALESCE(@.DeptCode,'%')
Hope this helps,
Gert-Jan
Mike C wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike