help emergency: sqlserver

Showing posts with label sqlserver. Show all posts

Friday, March 30, 2012

ICF, SQL SERVER 2000 and Windows 2003

Is it possible to configure a W2K3 server's ICF (internet connection
firewall) to allow remote connection of EM (enterprise manager) to SQL
Server 2000? I tried creating 2 new "applications" on port 1433 TCP and
1433 UDP, and was unable to connect. Disabling ICF does allow EM to
connect and function.
Tia - MarkCheck your firewall log to see what is being blocked.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Wednesday, March 28, 2012

I/O size

Hello,
Following is my understanding when sql needs anythings reads from disk it
request to Windows which do 64 k read even though sqlserver has only
requested single record? Am I right?
how sql handle the I/O request when it reads from RAM (data cache)?
Thanks.
Farhan
I/O refers to an actual read from the I/O device. So if a data page is
in the SQL Server Data Cache, then there is no I/O.
So really, it is the other way 'round. During execution, data is
requested from the Storage Engine. The Storage Engine will check if the
required data page is in cache. If it is not, then the page is read from
disk (the I/O device) which will almost certainly cause I/O. It could
also trigger the storage engine into reading multiple consecutive pages
(Read Ahead) from disk and (temporarily) stored in the Data Cache.
Gert-Jan
Farhan wrote:
> Hello,
> Following is my understanding when sql needs anythings reads from disk it
> request to Windows which do 64 k read even though sqlserver has only
> requested single record? Am I right?
> how sql handle the I/O request when it reads from RAM (data cache)?
> Thanks.
> --
> Farhan
|||Gert,
I am looking for the size of I/O id sqlserver misses the cache hit?
Here is what I got from search
"
IO size depends on what the operations is and we try to do a good job
of
balacing various facotrs to achieve optimal performance. The following
is
not a complete list but I hope it helps explain the situation:
Operation Random / Sequential Read / Write Size Range (per I/O)
========= =================== ============ ====================
OLTP - Log Sequential Write 512 bytes - 64KB
OLTP - Data Random Read/Write 8K
Bulk Insert Sequential Write 8~128 KB
Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
We use non-buffered IO on files so that means the windows operating
system
pass those directly to the disk subsystem. If you use RAID, then it
depends
on configuration parameters such as stripe size.
Wei Xiao
SQL Server Storage Engine Development
"
"Gert-Jan Strik" wrote:

> I/O refers to an actual read from the I/O device. So if a data page is
> in the SQL Server Data Cache, then there is no I/O.
> So really, it is the other way 'round. During execution, data is
> requested from the Storage Engine. The Storage Engine will check if the
> required data page is in cache. If it is not, then the page is read from
> disk (the I/O device) which will almost certainly cause I/O. It could
> also trigger the storage engine into reading multiple consecutive pages
> (Read Ahead) from disk and (temporarily) stored in the Data Cache.
> Gert-Jan
>
> Farhan wrote:
>
|||I don't think there are exact numbers, because it will depend on the
situation, the logic is internal (to the Storage Engine) and might
change with every hotfix or service pack.
Why do you want to know the I/O size?
Because it is even more 'unpredictable' then the story below suggests,
because a data cache miss can trigger multiple Read Aheads. According to
Inside SQL Server 2000 (a book I can recommend)
" Up to 32 extents of read ahead are outstanding at a time.
Four extents (32 pages) at a time are read with a single 256-KB
scatter read. "
The book also describes the read ahead behavior with respect to multiple
files, and the enhancements of Enterprise Edition (versus Standard
Edition) such as the "Merry-Go-Round" optimization.
Gert-Jan
Farhan wrote:[vbcol=seagreen]
> Gert,
> I am looking for the size of I/O id sqlserver misses the cache hit?
> Here is what I got from search
> "
> IO size depends on what the operations is and we try to do a good job
> of
> balacing various facotrs to achieve optimal performance. The following
> is
> not a complete list but I hope it helps explain the situation:
> Operation Random / Sequential Read / Write Size Range (per I/O)
> ========= =================== ============ ====================
> OLTP - Log Sequential Write 512 bytes - 64KB
> OLTP - Data Random Read/Write 8K
> Bulk Insert Sequential Write 8~128 KB
> Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
> We use non-buffered IO on files so that means the windows operating
> system
> pass those directly to the disk subsystem. If you use RAID, then it
> depends
> on configuration parameters such as stripe size.
> --
> Wei Xiao
> SQL Server Storage Engine Development
> "
> "Gert-Jan Strik" wrote:

I/O size

Hello,
Following is my understanding when sql needs anythings reads from disk it
request to Windows which do 64 k read even though sqlserver has only
requested single record' Am I right?
how sql handle the I/O request when it reads from RAM (data cache)?
Thanks.
--
FarhanI/O refers to an actual read from the I/O device. So if a data page is
in the SQL Server Data Cache, then there is no I/O.
So really, it is the other way 'round. During execution, data is
requested from the Storage Engine. The Storage Engine will check if the
required data page is in cache. If it is not, then the page is read from
disk (the I/O device) which will almost certainly cause I/O. It could
also trigger the storage engine into reading multiple consecutive pages
(Read Ahead) from disk and (temporarily) stored in the Data Cache.
Gert-Jan
Farhan wrote:
> Hello,
> Following is my understanding when sql needs anythings reads from disk it
> request to Windows which do 64 k read even though sqlserver has only
> requested single record' Am I right?
> how sql handle the I/O request when it reads from RAM (data cache)?
> Thanks.
> --
> Farhan|||Gert,
I am looking for the size of I/O id sqlserver misses the cache hit?
Here is what I got from search
"
IO size depends on what the operations is and we try to do a good job
of
balacing various facotrs to achieve optimal performance. The following
is
not a complete list but I hope it helps explain the situation:
Operation Random / Sequential Read / Write Size Range (per I/O)
========= =================== ============ ====================
OLTP - Log Sequential Write 512 bytes - 64KB
OLTP - Data Random Read/Write 8K
Bulk Insert Sequential Write 8~128 KB
Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
We use non-buffered IO on files so that means the windows operating
system
pass those directly to the disk subsystem. If you use RAID, then it
depends
on configuration parameters such as stripe size.
Wei Xiao
SQL Server Storage Engine Development
"
"Gert-Jan Strik" wrote:

> I/O refers to an actual read from the I/O device. So if a data page is
> in the SQL Server Data Cache, then there is no I/O.
> So really, it is the other way 'round. During execution, data is
> requested from the Storage Engine. The Storage Engine will check if the
> required data page is in cache. If it is not, then the page is read from
> disk (the I/O device) which will almost certainly cause I/O. It could
> also trigger the storage engine into reading multiple consecutive pages
> (Read Ahead) from disk and (temporarily) stored in the Data Cache.
> Gert-Jan
>
> Farhan wrote:
>|||I don't think there are exact numbers, because it will depend on the
situation, the logic is internal (to the Storage Engine) and might
change with every hotfix or service pack.
Why do you want to know the I/O size?
Because it is even more 'unpredictable' then the story below suggests,
because a data cache miss can trigger multiple Read Aheads. According to
Inside SQL Server 2000 (a book I can recommend)
" Up to 32 extents of read ahead are outstanding at a time.
Four extents (32 pages) at a time are read with a single 256-KB
scatter read. "
The book also describes the read ahead behavior with respect to multiple
files, and the enhancements of Enterprise Edition (versus Standard
Edition) such as the "Merry-Go-Round" optimization.
Gert-Jan
Farhan wrote:[vbcol=seagreen]
> Gert,
> I am looking for the size of I/O id sqlserver misses the cache hit?
> Here is what I got from search
> "
> IO size depends on what the operations is and we try to do a good job
> of
> balacing various facotrs to achieve optimal performance. The following
> is
> not a complete list but I hope it helps explain the situation:
> Operation Random / Sequential Read / Write Size Range (per I/O)
> ========= =================== ============ ====================
> OLTP - Log Sequential Write 512 bytes - 64KB
> OLTP - Data Random Read/Write 8K
> Bulk Insert Sequential Write 8~128 KB
> Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
> We use non-buffered IO on files so that means the windows operating
> system
> pass those directly to the disk subsystem. If you use RAID, then it
> depends
> on configuration parameters such as stripe size.
> --
> Wei Xiao
> SQL Server Storage Engine Development
> "
> "Gert-Jan Strik" wrote:
>

I/O size

Hello,
Following is my understanding when sql needs anythings reads from disk it
request to Windows which do 64 k read even though sqlserver has only
requested single record' Am I right?
how sql handle the I/O request when it reads from RAM (data cache)?
Thanks.
--
FarhanI/O refers to an actual read from the I/O device. So if a data page is
in the SQL Server Data Cache, then there is no I/O.
So really, it is the other way 'round. During execution, data is
requested from the Storage Engine. The Storage Engine will check if the
required data page is in cache. If it is not, then the page is read from
disk (the I/O device) which will almost certainly cause I/O. It could
also trigger the storage engine into reading multiple consecutive pages
(Read Ahead) from disk and (temporarily) stored in the Data Cache.
Gert-Jan
Farhan wrote:
> Hello,
> Following is my understanding when sql needs anythings reads from disk it
> request to Windows which do 64 k read even though sqlserver has only
> requested single record' Am I right?
> how sql handle the I/O request when it reads from RAM (data cache)?
> Thanks.
> --
> Farhan|||Gert,
I am looking for the size of I/O id sqlserver misses the cache hit?
Here is what I got from search
"
IO size depends on what the operations is and we try to do a good job
of
balacing various facotrs to achieve optimal performance. The following
is
not a complete list but I hope it helps explain the situation:
Operation Random / Sequential Read / Write Size Range (per I/O)
========= =================== ============ ====================OLTP - Log Sequential Write 512 bytes - 64KB
OLTP - Data Random Read/Write 8K
Bulk Insert Sequential Write 8~128 KB
Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
We use non-buffered IO on files so that means the windows operating
system
pass those directly to the disk subsystem. If you use RAID, then it
depends
on configuration parameters such as stripe size.
--
Wei Xiao
SQL Server Storage Engine Development
"
"Gert-Jan Strik" wrote:
> I/O refers to an actual read from the I/O device. So if a data page is
> in the SQL Server Data Cache, then there is no I/O.
> So really, it is the other way 'round. During execution, data is
> requested from the Storage Engine. The Storage Engine will check if the
> required data page is in cache. If it is not, then the page is read from
> disk (the I/O device) which will almost certainly cause I/O. It could
> also trigger the storage engine into reading multiple consecutive pages
> (Read Ahead) from disk and (temporarily) stored in the Data Cache.
> Gert-Jan
>
> Farhan wrote:
> >
> > Hello,
> > Following is my understanding when sql needs anythings reads from disk it
> > request to Windows which do 64 k read even though sqlserver has only
> > requested single record' Am I right?
> > how sql handle the I/O request when it reads from RAM (data cache)?
> > Thanks.
> > --
> > Farhan
>|||I don't think there are exact numbers, because it will depend on the
situation, the logic is internal (to the Storage Engine) and might
change with every hotfix or service pack.
Why do you want to know the I/O size?
Because it is even more 'unpredictable' then the story below suggests,
because a data cache miss can trigger multiple Read Aheads. According to
Inside SQL Server 2000 (a book I can recommend)
" Up to 32 extents of read ahead are outstanding at a time.
Four extents (32 pages) at a time are read with a single 256-KB
scatter read. "
The book also describes the read ahead behavior with respect to multiple
files, and the enhancements of Enterprise Edition (versus Standard
Edition) such as the "Merry-Go-Round" optimization.
Gert-Jan
Farhan wrote:
> Gert,
> I am looking for the size of I/O id sqlserver misses the cache hit?
> Here is what I got from search
> "
> IO size depends on what the operations is and we try to do a good job
> of
> balacing various facotrs to achieve optimal performance. The following
> is
> not a complete list but I hope it helps explain the situation:
> Operation Random / Sequential Read / Write Size Range (per I/O)
> ========= =================== ============ ====================> OLTP - Log Sequential Write 512 bytes - 64KB
> OLTP - Data Random Read/Write 8K
> Bulk Insert Sequential Write 8~128 KB
> Read Ahead Sequential Read 8KB - any multiple of 8KB up to 256K
> We use non-buffered IO on files so that means the windows operating
> system
> pass those directly to the disk subsystem. If you use RAID, then it
> depends
> on configuration parameters such as stripe size.
> --
> Wei Xiao
> SQL Server Storage Engine Development
> "
> "Gert-Jan Strik" wrote:
> > I/O refers to an actual read from the I/O device. So if a data page is
> > in the SQL Server Data Cache, then there is no I/O.
> >
> > So really, it is the other way 'round. During execution, data is
> > requested from the Storage Engine. The Storage Engine will check if the
> > required data page is in cache. If it is not, then the page is read from
> > disk (the I/O device) which will almost certainly cause I/O. It could
> > also trigger the storage engine into reading multiple consecutive pages
> > (Read Ahead) from disk and (temporarily) stored in the Data Cache.
> >
> > Gert-Jan
> >
> >
> > Farhan wrote:
> > >
> > > Hello,
> > > Following is my understanding when sql needs anythings reads from disk it
> > > request to Windows which do 64 k read even though sqlserver has only
> > > requested single record' Am I right?
> > > how sql handle the I/O request when it reads from RAM (data cache)?
> > > Thanks.
> > > --
> > > Farhan
> >

Monday, March 26, 2012

I/O error (Torn page)

I run a sp in sqlserver 2000 and got this error message.
(1 row affected)
Msg 823, Level 24, State 2, Server DBINT02, Procedure
KundAvpris_Insert, Line 13
I/O error (torn page) detected during read at offset
0x0000013a29a000 in file
'E:\Program Files\Microsoft SQL
Server\MSSQL\data\MARKISDATA_Data.MDF'.
Does anybody know how i do to correct this error?I suggest you perform a log backup. Then restore the latest clean database backup and all subsequent
log backups (including this last one). This will most probably give you zero data loss.
If you don't have log backups in place, then just go for the last clean database backups.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Robert Johansson" <rbjoh@.wmdata.com> wrote in message
news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> I run a sp in sqlserver 2000 and got this error message.
> (1 row affected)
> Msg 823, Level 24, State 2, Server DBINT02, Procedure
> KundAvpris_Insert, Line 13
> I/O error (torn page) detected during read at offset
> 0x0000013a29a000 in file
> 'E:\Program Files\Microsoft SQL
> Server\MSSQL\data\MARKISDATA_Data.MDF'.
> Does anybody know how i do to correct this error?|||Robert
Ask your system
administrator to check for disk corruption.You should make sure to run DBCC
CHECKDB or DBCC CHECKTABLE on that table.
"Robert Johansson" <rbjoh@.wmdata.com> wrote in message
news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> I run a sp in sqlserver 2000 and got this error message.
> (1 row affected)
> Msg 823, Level 24, State 2, Server DBINT02, Procedure
> KundAvpris_Insert, Line 13
> I/O error (torn page) detected during read at offset
> 0x0000013a29a000 in file
> 'E:\Program Files\Microsoft SQL
> Server\MSSQL\data\MARKISDATA_Data.MDF'.
> Does anybody know how i do to correct this error?|||Torn pages does most likely occur because a partially performed write operation. CHECKDB or
CHECKTABLE does not help here, as it will only confirm what we already know: a corruption in the
database. Also, this does, unfortunately, temp some to try the repair options (which in most cases
doesn't help), and possibly hinder the ability to do the vital last log backups.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uhUFzm4dDHA.2340@.TK2MSFTNGP09.phx.gbl...
> Robert
> Ask your system
> administrator to check for disk corruption.You should make sure to run DBCC
> CHECKDB or DBCC CHECKTABLE on that table.
> "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > I run a sp in sqlserver 2000 and got this error message.
> >
> > (1 row affected)
> > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > KundAvpris_Insert, Line 13
> > I/O error (torn page) detected during read at offset
> > 0x0000013a29a000 in file
> > 'E:\Program Files\Microsoft SQL
> > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> >
> > Does anybody know how i do to correct this error?
>|||That might not all be necessary. If the torn page is in a non-clustered
index you can just rebuild the index and everything will be fine. If it is
in a clustered index or it is a page that is used by SQL Server internally,
Tibor's method is the safest way to go.
DBCC CHECKDB will tell you in which object the torn page is located. You can
run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the process,
which can otherwise take a long time.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> I suggest you perform a log backup. Then restore the latest clean database
backup and all subsequent
> log backups (including this last one). This will most probably give you
zero data loss.
> If you don't have log backups in place, then just go for the last clean
database backups.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > I run a sp in sqlserver 2000 and got this error message.
> >
> > (1 row affected)
> > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > KundAvpris_Insert, Line 13
> > I/O error (torn page) detected during read at offset
> > 0x0000013a29a000 in file
> > 'E:\Program Files\Microsoft SQL
> > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> >
> > Does anybody know how i do to correct this error?
>|||> That might not all be necessary. If the torn page is in a non-clustered
> index you can just rebuild the index and everything will be fine.
Does above apply to torn pages as well?
I thought that torn pages are "corrupted beyond repair", even if a page can, technically, be dropped
as part of an index...
I.e., a torn page marks a "hands off - something is fishy here" to SQL Server.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> That might not all be necessary. If the torn page is in a non-clustered
> index you can just rebuild the index and everything will be fine. If it is
> in a clustered index or it is a page that is used by SQL Server internally,
> Tibor's method is the safest way to go.
> DBCC CHECKDB will tell you in which object the torn page is located. You can
> run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the process,
> which can otherwise take a long time.
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > I suggest you perform a log backup. Then restore the latest clean database
> backup and all subsequent
> > log backups (including this last one). This will most probably give you
> zero data loss.
> >
> > If you don't have log backups in place, then just go for the last clean
> database backups.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > I run a sp in sqlserver 2000 and got this error message.
> > >
> > > (1 row affected)
> > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > KundAvpris_Insert, Line 13
> > > I/O error (torn page) detected during read at offset
> > > 0x0000013a29a000 in file
> > > 'E:\Program Files\Microsoft SQL
> > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > >
> > > Does anybody know how i do to correct this error?
> >
> >
>|||Only thing a torn page tells you as far as I understand it, is that it was
written to disk partially but not completely, i.e. the check bits for all
the 512 byte sectors is the page are not the same, which means that some of
the sectors have changed the last time the page was written and some
haven't. It's a "logical" rather than a physical error, it doesn't tell you
anything about the current physical state of the page only about the current
logical state of the page (inconsistent) and that the last write operation
on that page didn't succeed completely. The page being torn in itself
doesn't make the harddisk space where it is located unusable. (The torn page
can ofcourse be caused by a harddisk problem which makes the disk space
unusable, but that's a separate issue.)
If the torn page has been cause by a power failure or a similar problem,
that is not a permanent hardware problem, like a bad sector on a disk, I see
no reason why you could not reuse the page?
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ugzTOx4dDHA.3356@.TK2MSFTNGP09.phx.gbl...
> > That might not all be necessary. If the torn page is in a non-clustered
> > index you can just rebuild the index and everything will be fine.
> Does above apply to torn pages as well?
> I thought that torn pages are "corrupted beyond repair", even if a page
can, technically, be dropped
> as part of an index...
> I.e., a torn page marks a "hands off - something is fishy here" to SQL
Server.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> > That might not all be necessary. If the torn page is in a non-clustered
> > index you can just rebuild the index and everything will be fine. If it
is
> > in a clustered index or it is a page that is used by SQL Server
internally,
> > Tibor's method is the safest way to go.
> >
> > DBCC CHECKDB will tell you in which object the torn page is located. You
can
> > run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the
process,
> > which can otherwise take a long time.
> >
> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > > I suggest you perform a log backup. Then restore the latest clean
database
> > backup and all subsequent
> > > log backups (including this last one). This will most probably give
you
> > zero data loss.
> > >
> > > If you don't have log backups in place, then just go for the last
clean
> > database backups.
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > > I run a sp in sqlserver 2000 and got this error message.
> > > >
> > > > (1 row affected)
> > > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > > KundAvpris_Insert, Line 13
> > > > I/O error (torn page) detected during read at offset
> > > > 0x0000013a29a000 in file
> > > > 'E:\Program Files\Microsoft SQL
> > > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > > >
> > > > Does anybody know how i do to correct this error?
> > >
> > >
> >
> >
>|||I agree, Jacco. My point as only the SQL Server code (design of-). Whether SQL Server will never
re-uses/repairs a torn page or not, even though it can safely drop the page (because the HW might be
OK). I guess the answer is inside the SQL Server code, which I don't have access to... ;-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Bj5HQ5dDHA.1044@.TK2MSFTNGP10.phx.gbl...
> Only thing a torn page tells you as far as I understand it, is that it was
> written to disk partially but not completely, i.e. the check bits for all
> the 512 byte sectors is the page are not the same, which means that some of
> the sectors have changed the last time the page was written and some
> haven't. It's a "logical" rather than a physical error, it doesn't tell you
> anything about the current physical state of the page only about the current
> logical state of the page (inconsistent) and that the last write operation
> on that page didn't succeed completely. The page being torn in itself
> doesn't make the harddisk space where it is located unusable. (The torn page
> can ofcourse be caused by a harddisk problem which makes the disk space
> unusable, but that's a separate issue.)
> If the torn page has been cause by a power failure or a similar problem,
> that is not a permanent hardware problem, like a bad sector on a disk, I see
> no reason why you could not reuse the page?
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ugzTOx4dDHA.3356@.TK2MSFTNGP09.phx.gbl...
> > > That might not all be necessary. If the torn page is in a non-clustered
> > > index you can just rebuild the index and everything will be fine.
> >
> > Does above apply to torn pages as well?
> > I thought that torn pages are "corrupted beyond repair", even if a page
> can, technically, be dropped
> > as part of an index...
> > I.e., a torn page marks a "hands off - something is fishy here" to SQL
> Server.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> > > That might not all be necessary. If the torn page is in a non-clustered
> > > index you can just rebuild the index and everything will be fine. If it
> is
> > > in a clustered index or it is a page that is used by SQL Server
> internally,
> > > Tibor's method is the safest way to go.
> > >
> > > DBCC CHECKDB will tell you in which object the torn page is located. You
> can
> > > run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the
> process,
> > > which can otherwise take a long time.
> > >
> > > --
> > > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > > Database Administrator
> > > Eurostop Ltd.
> > >
> > >
> > > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > > > I suggest you perform a log backup. Then restore the latest clean
> database
> > > backup and all subsequent
> > > > log backups (including this last one). This will most probably give
> you
> > > zero data loss.
> > > >
> > > > If you don't have log backups in place, then just go for the last
> clean
> > > database backups.
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at: http://groups.google.com/groups?oi=djq&as
> > > ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > > > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > > > I run a sp in sqlserver 2000 and got this error message.
> > > > >
> > > > > (1 row affected)
> > > > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > > > KundAvpris_Insert, Line 13
> > > > > I/O error (torn page) detected during read at offset
> > > > > 0x0000013a29a000 in file
> > > > > 'E:\Program Files\Microsoft SQL
> > > > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > > > >
> > > > > Does anybody know how i do to correct this error?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sorry Tibor, your post is not really clear to me...
I don't think that you can't drop an index because it contains a torn page,
if that is what you mean? (Too bad the problem is quite difficult to
replicate). Dropping an index only deallocates the index pages and deletes
the rows from the system tables and doesn't do anything to the actual pages,
so whether they are torn or not should not make any difference.
> I guess the answer is inside the SQL Server code, which I don't have
access to... ;-)
But you have access to people who have access (or at least have access to
people who have access) ;-)
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eyZU7n5dDHA.1448@.TK2MSFTNGP12.phx.gbl...
> I agree, Jacco. My point as only the SQL Server code (design of-). Whether
SQL Server will never
> re-uses/repairs a torn page or not, even though it can safely drop the
page (because the HW might be
> OK). I guess the answer is inside the SQL Server code, which I don't have
access to... ;-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23Bj5HQ5dDHA.1044@.TK2MSFTNGP10.phx.gbl...
> > Only thing a torn page tells you as far as I understand it, is that it
was
> > written to disk partially but not completely, i.e. the check bits for
all
> > the 512 byte sectors is the page are not the same, which means that some
of
> > the sectors have changed the last time the page was written and some
> > haven't. It's a "logical" rather than a physical error, it doesn't tell
you
> > anything about the current physical state of the page only about the
current
> > logical state of the page (inconsistent) and that the last write
operation
> > on that page didn't succeed completely. The page being torn in itself
> > doesn't make the harddisk space where it is located unusable. (The torn
page
> > can ofcourse be caused by a harddisk problem which makes the disk space
> > unusable, but that's a separate issue.)
> >
> > If the torn page has been cause by a power failure or a similar problem,
> > that is not a permanent hardware problem, like a bad sector on a disk, I
see
> > no reason why you could not reuse the page?
> >
> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:ugzTOx4dDHA.3356@.TK2MSFTNGP09.phx.gbl...
> > > > That might not all be necessary. If the torn page is in a
non-clustered
> > > > index you can just rebuild the index and everything will be fine.
> > >
> > > Does above apply to torn pages as well?
> > > I thought that torn pages are "corrupted beyond repair", even if a
page
> > can, technically, be dropped
> > > as part of an index...
> > > I.e., a torn page marks a "hands off - something is fishy here" to SQL
> > Server.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > > news:%23q9BDv4dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> > > > That might not all be necessary. If the torn page is in a
non-clustered
> > > > index you can just rebuild the index and everything will be fine. If
it
> > is
> > > > in a clustered index or it is a page that is used by SQL Server
> > internally,
> > > > Tibor's method is the safest way to go.
> > > >
> > > > DBCC CHECKDB will tell you in which object the torn page is located.
You
> > can
> > > > run DBCC CHECKDB with the WITH PHYSICAL_ONLY option to speed up the
> > process,
> > > > which can otherwise take a long time.
> > > >
> > > > --
> > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > > > Database Administrator
> > > > Eurostop Ltd.
> > > >
> > > >
> > > > "Tibor Karaszi"
> > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > > wrote in message news:OuN3Hl4dDHA.3592@.tk2msftngp13.phx.gbl...
> > > > > I suggest you perform a log backup. Then restore the latest clean
> > database
> > > > backup and all subsequent
> > > > > log backups (including this last one). This will most probably
give
> > you
> > > > zero data loss.
> > > > >
> > > > > If you don't have log backups in place, then just go for the last
> > clean
> > > > database backups.
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > Archive at: http://groups.google.com/groups?oi=djq&as
> > > > ugroup=microsoft.public.sqlserver
> > > > >
> > > > >
> > > > > "Robert Johansson" <rbjoh@.wmdata.com> wrote in message
> > > > > news:60f401c37788$1cbdbc40$a501280a@.phx.gbl...
> > > > > > I run a sp in sqlserver 2000 and got this error message.
> > > > > >
> > > > > > (1 row affected)
> > > > > > Msg 823, Level 24, State 2, Server DBINT02, Procedure
> > > > > > KundAvpris_Insert, Line 13
> > > > > > I/O error (torn page) detected during read at offset
> > > > > > 0x0000013a29a000 in file
> > > > > > 'E:\Program Files\Microsoft SQL
> > > > > > Server\MSSQL\data\MARKISDATA_Data.MDF'.
> > > > > >
> > > > > > Does anybody know how i do to correct this error?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>sql

Friday, March 23, 2012

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagramssql

Wednesday, March 21, 2012

i want to create database

Hi
i have installed sql server and i didn't find any GUI tool like sqlserver 2000 to create database or list the avilable databases or run sql statements ,,,

can you help me

thanks in advance.

Hi Seco,

You can download Management Studio Express from the SQL Express download page.

Mike

|||Thanks mike for reply

is this tool come with workgroup edition? what it's name or the one you gave me can work with all versions of sql server?

2- how can i make relation between 2 tables ?

thanks in advance.|||

hi,

seco wrote:
Thanks mike for reply
is this tool come with workgroup edition? what it's name or the one you gave me can work with all versions of sql server?

quite, but not really... the "full" editions of SQL Server 2005 provides "SQL Server Management Studio", including futher "wizards" and the like being "the full tool" implementation...

SQL Server Management Studio Express is the "lite" version, available for free...

2- how can i make relation between 2 tables ?

you can use the "Diagram" feature of SQL Server Management Studio Express or, (and you should go that way for learning purpose), you should dig into the Data Definition Language statements like CREATE TABLE or ALTER TABLE statements where you can define this kind of constraint..

regards

|||

You might be interested in the learning resources that I posted in this thread, there are a bunch of videos that teach you how to use the tools and how to create the standard database objects such as tables, views and stored procedures.

Mike

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

Monday, March 19, 2012

i want to ask some sqlserver question?

hello:
i want to ask some sqlserver question?Hi
Try searching sites like Google http://groups-beta.google.com/ to see if you
can match up with previous posts using key wordsfor instance an error
message number.... You may also want to search www.microsoft.com for these
as well, and take a look at http://www.microsoft.com/sql/default.mspx to see
if anything there may shed some light.
This news group is related to security, if the subject is not to do with
security you should post into newsgroup that describes your problem best.
These are a few newsgroups
http://www.microsoft.com/sql/commun...ps/default.mspx
The following is a list of other places you may wish to look
http://www.aspfaq.com/show.asp?id=2423
If you can't find the solution then you may want to post. If it is relating
to code, then you will need to post the code in question this will help if
you are having problems with SQL http://www.aspfaq.com/etiquette.asp?id=5006
John
"wanxia" <wanxiaruge@.sohu.com> wrote in message
news:cqiktv$uso$1@.news.yaako.com...
> hello:
> i want to ask some sqlserver question?
>

Monday, March 12, 2012

I think this is a bug

We have a test system on which we installed Sql Server 2005, then Sql Server
2005 Express.
We then deleted SqlServer 2005 leaving just express.
The problem is when we call
DbProviderFactory.CreateDataSourceEnumerator().Get DataSources(), the row
returned has the server name in column [0] and nothing in the other 3 columns.
So our code then assumes that to connect the server is CONUNDRUM (server
name) instead of CONUNDRUM\SQLEXPRESS and that fails. How can we get the
correct name?
ps - I know we can re-install SqlServer 2005 or uninstall and reinstall
Express to fix this. But I want to make sure our code works properly when
someone does this.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
Hi David,
From your description, I understand that:
You found that the function
DbProviderFactory.CreateDataSourceEnumerator().Get DataSources() failed to
get all matched values after you uninstalled SQL Server 2005 from your
machine. You found that your application could only get the server name in
the first column but nothing in the other three columns.
If I have misunderstood, please let me know.
I just wrote a simple test application with the following code:
DbProviderFactory factory = SqlClientFactory.Instance;
DataTable sources =
factory.CreateDataSourceEnumerator().GetDataSource s();
this.textBox1.Text = "SQL Instances:" + sources.Rows.Count +
"\r\n";
textBox1.Text += "Attributes:" + sources.Columns.Count + "\r\n";
for (int r = 0; r < sources.Rows.Count; ++r)
{
textBox1.Text += "Instance " + r + ":\r\n";
for (int i = 0; i < sources.Columns.Count; ++i)
{
textBox1.Text += "[" + r + "," + i + "]: " +
sources.Rows[r][i] + "\r\n";
}
textBox1.Text += "====================\r\n";
}
I first ran it under my computer with SQL Server 2005 Ent and Express
Editions, then ran it under a computer with only SQL Server 2005 Express
edition, but it worked fine on the two conditions.
To reproduce your issue, I moved it to a test machine with both SQL Server
2005 Ent and Express Editions, then I manually uninstalled the SQL Server
2005 Ent Edition, and ran it again, however it still worked fine.
I also used Reflector to check the source code, the internal logical should
be no problem. However I found that there was a timeout setting by a
private variable timeoutTime. Per my test, I found sometimes at the first
time I could not retrieve all the SQL Server instances information, but if
I tried a second time, I could get the information. I think that the
half-baked information at my side is related to the timeout setting. Not
sure if you encounter the similar issue and you may try again if you could
not get the correct information at the first time.
I recommend that you create a simple C# project and copy my code into it
and see if this issue still exists.
If the issue only exists in your application, you my extract your code into
a simple .net project and mail it to me (changliw@.microsoft.com) so that I
can reproduce your issue for further research.
Thanks for using Microsoft Online Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Here is the result of your test program. But the only server on conundrum is
conundrum/sqlexpress.
SQL Instances:7
Attributes:4
Instance 0:
[0,0]: BELLE
[0,1]:
[0,2]: No
[0,3]: 8.00.194
====================
Instance 1:
[1,0]: BELLE
[1,1]: SQLEXPRESS
[1,2]: No
[1,3]: 9.00.1399.06
====================
Instance 2:
[2,0]: CONUNDRUM
[2,1]:
[2,2]:
[2,3]:
====================
Instance 3:
[3,0]: JASMINE
[3,1]:
[3,2]:
[3,3]:
====================
Instance 4:
[4,0]: LUMIERE
[4,1]:
[4,2]:
[4,3]:
====================
Instance 5:
[5,0]: MINNIE
[5,1]:
[5,2]:
[5,3]:
====================
Instance 6:
[6,0]: PICARD
[6,1]:
[6,2]:
[6,3]:
====================
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Charles Wang[MSFT]" wrote:

> Hi David,
> From your description, I understand that:
> You found that the function
> DbProviderFactory.CreateDataSourceEnumerator().Get DataSources() failed to
> get all matched values after you uninstalled SQL Server 2005 from your
> machine. You found that your application could only get the server name in
> the first column but nothing in the other three columns.
> If I have misunderstood, please let me know.
> I just wrote a simple test application with the following code:
> DbProviderFactory factory = SqlClientFactory.Instance;
> DataTable sources =
> factory.CreateDataSourceEnumerator().GetDataSource s();
> this.textBox1.Text = "SQL Instances:" + sources.Rows.Count +
> "\r\n";
> textBox1.Text += "Attributes:" + sources.Columns.Count + "\r\n";
> for (int r = 0; r < sources.Rows.Count; ++r)
> {
> textBox1.Text += "Instance " + r + ":\r\n";
> for (int i = 0; i < sources.Columns.Count; ++i)
> {
> textBox1.Text += "[" + r + "," + i + "]: " +
> sources.Rows[r][i] + "\r\n";
> }
> textBox1.Text += "====================\r\n";
> }
> I first ran it under my computer with SQL Server 2005 Ent and Express
> Editions, then ran it under a computer with only SQL Server 2005 Express
> edition, but it worked fine on the two conditions.
> To reproduce your issue, I moved it to a test machine with both SQL Server
> 2005 Ent and Express Editions, then I manually uninstalled the SQL Server
> 2005 Ent Edition, and ran it again, however it still worked fine.
> I also used Reflector to check the source code, the internal logical should
> be no problem. However I found that there was a timeout setting by a
> private variable timeoutTime. Per my test, I found sometimes at the first
> time I could not retrieve all the SQL Server instances information, but if
> I tried a second time, I could get the information. I think that the
> half-baked information at my side is related to the timeout setting. Not
> sure if you encounter the similar issue and you may try again if you could
> not get the correct information at the first time.
> I recommend that you create a simple C# project and copy my code into it
> and see if this issue still exists.
> If the issue only exists in your application, you my extract your code into
> a simple .net project and mail it to me (changliw@.microsoft.com) so that I
> can reproduce your issue for further research.
> Thanks for using Microsoft Online Managed Newsgroup. If you have any other
> questions or concerns, please feel free to let me know.
> Sincerely yours,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
|||Hi David,
This is indeed a strange behavior, but unfortunately I could not reproduce
your issue. In my local network area, there are about 60 SQL Server
instances, when I ran my application to detect the instances, I could got
all the information of the 60 instances. I also ran the test program on
other 3 machines and still could not reproduce your issue.
After discussing with our tech lead, I recommend that you contact Microsoft
Customer Support Services (CSS) via telephone so that a dedicated Support
Professional can assist you in a more efficient manner. Please be advised
that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
|||

Quote:

Originally Posted by Charles Wang[MSFT] View Post

I am experiencing exactly the same behavior.
This code returns the Server and Instance names correctly on my local box (I have three instances installed locally, all with instance names).
However, it returns blank fields for every server on the network (there are a great many servers on the network). I know for a fact that many of these have instance names, but the instance name comes back as null in this code.
So this is definitely something that is not isolated to just the first poster who reported this behavior.
Are there ANY other ideas here? Any suggestions of what might be going on? It sure looks like a bug from my end, but could it be anything in the internal firewalls or any settings in SQL Server? Or the fact that I'm going over VPN? Could there be any VPN or network issues?
Our environment is a mix of SQL Server 2000 and SQL Server 2005. Again, everything is fine on my local box, but the remote servers report no instance names, or versions, or anything else, ever. Just the server name comes back, exactly as the grandparent indicated.|||

Quote:

Originally Posted by Charles Wang[MSFT] View Post

Hi David,
This is indeed a strange behavior, but unfortunately I could not reproduce
your issue. In my local network area, there are about 60 SQL Server
instances, when I ran my application to detect the instances, I could got
all the information of the 60 instances. I also ran the test program on
other 3 machines and still could not reproduce your issue.
After discussing with our tech lead, I recommend that you contact Microsoft
Customer Support Services (CSS) via telephone so that a dedicated Support
Professional can assist you in a more efficient manner. Please be advised
that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Charles Wang
Microsoft Online Community Support

Is there any more information on this?
I've verified that it's definitely related to networking or VPN.
When I run the code from my VPN'd machine, I don't get back
anything but the server name (no instance name, no version,
just as originally reported by David). But if I take the same
EXE and copy it to a machine inside the actual network and
run it, it sees everything fine.
Can you verify if there are some additional ports that need
opening, or some additional settings that need to be made?
Surely someone ELSE can reproduce this problem, since two
of us experience it so reproducably...
Anyone? Bueller?

I think this is a bug

We have a test system on which we installed Sql Server 2005, then Sql Server
2005 Express.
We then deleted SqlServer 2005 leaving just express.
The problem is when we call
DbProviderFactory.CreateDataSourceEnumerator().GetDataSources(), the row
returned has the server name in column [0] and nothing in the other 3 co
lumns.
So our code then assumes that to connect the server is CONUNDRUM (server
name) instead of CONUNDRUM\SQLEXPRESS and that fails. How can we get the
correct name?
ps - I know we can re-install SqlServer 2005 or uninstall and reinstall
Express to fix this. But I want to make sure our code works properly when
someone does this.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htmHi David,
From your description, I understand that:
You found that the function
DbProviderFactory.CreateDataSourceEnumerator().GetDataSources() failed to
get all matched values after you uninstalled SQL Server 2005 from your
machine. You found that your application could only get the server name in
the first column but nothing in the other three columns.
If I have misunderstood, please let me know.
I just wrote a simple test application with the following code:
DbProviderFactory factory = SqlClientFactory.Instance;
DataTable sources =
factory.CreateDataSourceEnumerator().GetDataSources();
this.textBox1.Text = "SQL Instances:" + sources.Rows.Count +
"\r\n";
textBox1.Text += "Attributes:" + sources.Columns.Count + "\r\n";
for (int r = 0; r < sources.Rows.Count; ++r)
{
textBox1.Text += "Instance " + r + ":\r\n";
for (int i = 0; i < sources.Columns.Count; ++i)
{
textBox1.Text += "[" + r + "," + i + "]: " +
sources.Rows[r][i] + "\r\n";
}
textBox1.Text += "====================\r\n";
}
I first ran it under my computer with SQL Server 2005 Ent and Express
Editions, then ran it under a computer with only SQL Server 2005 Express
edition, but it worked fine on the two conditions.
To reproduce your issue, I moved it to a test machine with both SQL Server
2005 Ent and Express Editions, then I manually uninstalled the SQL Server
2005 Ent Edition, and ran it again, however it still worked fine.
I also used Reflector to check the source code, the internal logical should
be no problem. However I found that there was a timeout setting by a
private variable timeoutTime. Per my test, I found sometimes at the first
time I could not retrieve all the SQL Server instances information, but if
I tried a second time, I could get the information. I think that the
half-baked information at my side is related to the timeout setting. Not
sure if you encounter the similar issue and you may try again if you could
not get the correct information at the first time.
I recommend that you create a simple C# project and copy my code into it
and see if this issue still exists.
If the issue only exists in your application, you my extract your code into
a simple .net project and mail it to me (changliw@.microsoft.com) so that I
can reproduce your issue for further research.
Thanks for using Microsoft Online Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Here is the result of your test program. But the only server on conundrum is
conundrum/sqlexpress.
SQL Instances:7
Attributes:4
Instance 0:
[0,0]: BELLE
[0,1]:
[0,2]: No
[0,3]: 8.00.194
====================
Instance 1:
[1,0]: BELLE
[1,1]: SQLEXPRESS
[1,2]: No
[1,3]: 9.00.1399.06
====================
Instance 2:
[2,0]: CONUNDRUM
[2,1]:
[2,2]:
[2,3]:
====================
Instance 3:
[3,0]: JASMINE
[3,1]:
[3,2]:
[3,3]:
====================
Instance 4:
[4,0]: LUMIERE
[4,1]:
[4,2]:
[4,3]:
====================
Instance 5:
[5,0]: MINNIE
[5,1]:
[5,2]:
[5,3]:
====================
Instance 6:
[6,0]: PICARD
[6,1]:
[6,2]:
[6,3]:
====================
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Charles Wang[MSFT]" wrote:

> Hi David,
> From your description, I understand that:
> You found that the function
> DbProviderFactory.CreateDataSourceEnumerator().GetDataSources() failed to
> get all matched values after you uninstalled SQL Server 2005 from your
> machine. You found that your application could only get the server name in
> the first column but nothing in the other three columns.
> If I have misunderstood, please let me know.
> I just wrote a simple test application with the following code:
> DbProviderFactory factory = SqlClientFactory.Instance;
> DataTable sources =
> factory.CreateDataSourceEnumerator().GetDataSources();
> this.textBox1.Text = "SQL Instances:" + sources.Rows.Count +
> "\r\n";
> textBox1.Text += "Attributes:" + sources.Columns.Count + "\r\n
";
> for (int r = 0; r < sources.Rows.Count; ++r)
> {
> textBox1.Text += "Instance " + r + ":\r\n";
> for (int i = 0; i < sources.Columns.Count; ++i)
> {
> textBox1.Text += "[" + r + "," + i + "]: " +
> sources.Rows[r][i] + "\r\n";
> }
> textBox1.Text += "====================\r\n";
> }
> I first ran it under my computer with SQL Server 2005 Ent and Express
> Editions, then ran it under a computer with only SQL Server 2005 Express
> edition, but it worked fine on the two conditions.
> To reproduce your issue, I moved it to a test machine with both SQL Server
> 2005 Ent and Express Editions, then I manually uninstalled the SQL Server
> 2005 Ent Edition, and ran it again, however it still worked fine.
> I also used Reflector to check the source code, the internal logical shoul
d
> be no problem. However I found that there was a timeout setting by a
> private variable timeoutTime. Per my test, I found sometimes at the first
> time I could not retrieve all the SQL Server instances information, but if
> I tried a second time, I could get the information. I think that the
> half-baked information at my side is related to the timeout setting. Not
> sure if you encounter the similar issue and you may try again if you could
> not get the correct information at the first time.
> I recommend that you create a simple C# project and copy my code into it
> and see if this issue still exists.
> If the issue only exists in your application, you my extract your code int
o
> a simple .net project and mail it to me (changliw@.microsoft.com) so that I
> can reproduce your issue for further research.
> Thanks for using Microsoft Online Managed Newsgroup. If you have any other
> questions or concerns, please feel free to let me know.
> Sincerely yours,
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>
>|||Hi David,
This is indeed a strange behavior, but unfortunately I could not reproduce
your issue. In my local network area, there are about 60 SQL Server
instances, when I ran my application to detect the instances, I could got
all the information of the 60 instances. I also ran the test program on
other 3 machines and still could not reproduce your issue.
After discussing with our tech lead, I recommend that you contact Microsoft
Customer Support Services (CSS) via telephone so that a dedicated Support
Professional can assist you in a more efficient manner. Please be advised
that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Charles Wang
Microsoft Online Community Support

I smell a big, fat compiler bug

Last two or three years now I've been spending a lot of time doing
SQLServer performance tuning. The last couple of weeks I've been
doing this at a place using a whole lot of inline table-valued UDFs,
an issue both in itself and because the database does a LOT of
joining, and in this place, I've been finding one technique is working
for me just a little too often.
Time and again, there is some complex query that looks like it should
be OK, but is running 100k's of logical reads and taking ten, twenty,
thirty seconds to return a handfull of records. No doubt someone who
can really, really read execution plans might be able to glance at the
plan and see what's wrong, but I haven't reached quite that point yet.
Time and again, what I find is that if I break out a chunk of code and
run it stand-alone in QA, suddenly it runs "correctly", that is, with
99% fewer reads and 80-99% faster execution. OK, we've all seen that,
we understand how much more the optimizer knows with explicit
constants and such, and how SPs have to live with "parameter sniffing"
and such. But I've gone the next step, wrapped ten or twenty or fifty
lines in a relatively small SP or UDF, and darned if it doesn't still
run fast, with all the same parameters and variables that it had in
the original big SP.
So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
large SP, it is not properly resetting the flags or counters or other
internal doodads it uses to compute optimizations.
Suggestion to speed freaks: break apart large queries inside the SP,
often that works, but when that doesn't work, try moving chunks into
separately compiled SPs, and you may find significantly better
performance falls your way.
Microsoft, take a look, wouldya?
J.Hi
Without seeing the procedures then it is hard to comment on this. You may
want to read Kalens articles on Reuse of Query Plans and Conditional
Recompilation in SQL Server Magazine also
http://www.sql-server-performance.com/mw_sql_server_udfs.asp may be useful
reading.
John
"JXStern" wrote:
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Hi
Well, can you post some data that we will be able to reprocude the
problem/bug?
Also, at least, can you show an execution plan of the query?
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||On Thu, 11 Aug 2005 23:31:16 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Without seeing the procedures then it is hard to comment on this. You may
>want to read Kalens articles on Reuse of Query Plans and Conditional
>Recompilation in SQL Server Magazine also
>http://www.sql-server-performance.com/mw_sql_server_udfs.asp may be useful
>reading.
Yes, I'm aware of the threat and menace of scalar UDFs in where
clauses and the like, but that isn't what I'm talking about here.
I'm not going to try to post actual code and plans from the app, cuz
(even if the code were not proprietary) it would take the schema and
stats as well to make any sense out of it. What I'm fishing for here
are similar stories and suspicions, or any official or unofficial
further descriptions of the problem or workarounds.
If I find myself with lots of time, I may try to synthesize an entire
duplicatable scenario, but it's liable to require a bunch of tables
and data to get the thing off the ground.
J.|||Hi
You may want to look at
http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp and
subsequent links if you are getting recompiles.
John
"AM" wrote:
>
> Yes , I also experienced this. I have a sp 1000 lines long and when I run it
> took too much time and when I devided it in 4 small sps it run about 150%
> faster.
> Even execution plan is almost same I can not find what causes the sp to run
> faster when it is in small code.
> May be locks are not released when they should be ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Hi
> > Well, can you post some data that we will be able to reprocude the
> > problem/bug?
> >
> > Also, at least, can you show an execution plan of the query?
> >
> >
> >
> >
> >
> > "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> > news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> > > Last two or three years now I've been spending a lot of time doing
> > > SQLServer performance tuning. The last couple of weeks I've been
> > > doing this at a place using a whole lot of inline table-valued UDFs,
> > > an issue both in itself and because the database does a LOT of
> > > joining, and in this place, I've been finding one technique is working
> > > for me just a little too often.
> > >
> > > Time and again, there is some complex query that looks like it should
> > > be OK, but is running 100k's of logical reads and taking ten, twenty,
> > > thirty seconds to return a handfull of records. No doubt someone who
> > > can really, really read execution plans might be able to glance at the
> > > plan and see what's wrong, but I haven't reached quite that point yet.
> > > Time and again, what I find is that if I break out a chunk of code and
> > > run it stand-alone in QA, suddenly it runs "correctly", that is, with
> > > 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> > > we understand how much more the optimizer knows with explicit
> > > constants and such, and how SPs have to live with "parameter sniffing"
> > > and such. But I've gone the next step, wrapped ten or twenty or fifty
> > > lines in a relatively small SP or UDF, and darned if it doesn't still
> > > run fast, with all the same parameters and variables that it had in
> > > the original big SP.
> > >
> > > So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> > > large SP, it is not properly resetting the flags or counters or other
> > > internal doodads it uses to compute optimizations.
> > >
> > > Suggestion to speed freaks: break apart large queries inside the SP,
> > > often that works, but when that doesn't work, try moving chunks into
> > > separately compiled SPs, and you may find significantly better
> > > performance falls your way.
> > >
> > > Microsoft, take a look, wouldya?
> > >
> > > J.
> > >
> >
> >
>
>|||I had much the same problem on a project a couple of years ago. It
usually turned out that a less than optimal plan was cached for the
original SP - mostly due to the SP being run the first time with
non-typical params. I used DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS to get a more accurate benchmark.
Good luck.
Payson|||Thanks for your feedback.
I will try to give you a few pieces of information that may help explain
what you are seeing (which, as others have mentioned, would be more
definitive with the plan + additional information in question) - so, I am
guessing.
User latency for ad hoc queries is generally a function of compilation time
+ execution time. If your query is cached and run multiple times, it is
likely a function of execution time alone.
In either case, there are a number of algorithmic problems that can impact
plan choice and thus user latency, even if the query is run multiple times.
For example, if you join N tables together, there are a large number of ways
to actually execute this query based on the join order picked. Once you
have a sufficiently large number of tables, it actually becomes
algorithmically intractible to search the complete set of possible cases (in
a reasonable time - however, eventually it just becomes impossible). So,
heuristics are used to try to find a good join order relatively quickly.
This may or may not be the exact, optimal plan for a particular machine.
Alternatives are considered from this "spot" in the universe of possible
join orders. In some cases, it may be that the initial heuristic order is
not "close" to the optimal choice and significant searching is required to
get to the right plan. Additionally, it may be that the optimal plan is so
far away from the optimal choice as to make it impossible to consider that
choice in a reasonable amount of time using the heuristics I mentioned.
If your query is one of these cases, manually rewriting the query to remove
some of the joins could possibly make things run faster for your query +
data. It's not impossible to have such a case. I'll suggest that you can
also use query hints, in many cases, to force the join order into one that
may be more optimal for your query.
Root causes for picking an initial, heuristic order that is suboptimal
include missing statistics, statistics with a sample rate that is too low,
physical grouping of values on disk that causes problems in sampled
statistics interpolation, correlations between columns that cause the
optimizer to over- or under-estimate cardinality for a join or filter, or
others. In some cases, the technique you describe (moving part of the query
into a temporary result) can correct errors because part of the query
becomes materialized - the cardinality of that intermediate result thus does
not contain some of the errors and the rest of the query may have enough
information to pick a good join order.
Join order is one way in which this condition can happen - others also
exist, but the basic impact is generally the same.
I hope that this gives you some insight into the complexities involved in
this process.
Sincerely,
Conor Cunningham
SQL Server Query Optimization Development Lead
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Connor,
Thank you for your response. I'll comment on a few of your notes
below. However, my accusation remains in place, that I am seeing too
many cases where simply removing a lump of code to a separate SP gets
a totally different (and better) result from the compiler/optimizer,
such that I suspect it is not properly finding cutpoints in the text
of large SPs.
Joshua Stern
On Fri, 12 Aug 2005 13:01:35 -0700, "Conor Cunningham [MS]"
<conorc_removeme@.online.microsoft.com> wrote:
>Thanks for your feedback.
>I will try to give you a few pieces of information that may help explain
>what you are seeing (which, as others have mentioned, would be more
>definitive with the plan + additional information in question) - so, I am
>guessing.
>User latency for ad hoc queries is generally a function of compilation time
>+ execution time. If your query is cached and run multiple times, it is
>likely a function of execution time alone.
I make certain to null that out through reruns, which also generally
ends up caching the data and getting down to zero physical reads.
Most of the SPs I'm talking about here have runtimes of at least a
minute when I begin to work on them, and still take five or ten
seconds after improvements are in place, so the compilation is not
likely a huge factor in any case (well, except where there are a lot
of temp tables involved, but even there it's a fairly minor point,
which I realize I could check further with the profiler and such).
>In either case, there are a number of algorithmic problems that can impact
>plan choice and thus user latency, even if the query is run multiple times.
>For example, if you join N tables together, there are a large number of ways
>to actually execute this query based on the join order picked. Once you
>have a sufficiently large number of tables, it actually becomes
>algorithmically intractible to search the complete set of possible cases (in
>a reasonable time - however, eventually it just becomes impossible). So,
>heuristics are used to try to find a good join order relatively quickly.
>This may or may not be the exact, optimal plan for a particular machine.
>Alternatives are considered from this "spot" in the universe of possible
>join orders. In some cases, it may be that the initial heuristic order is
>not "close" to the optimal choice and significant searching is required to
>get to the right plan. Additionally, it may be that the optimal plan is so
>far away from the optimal choice as to make it impossible to consider that
>choice in a reasonable amount of time using the heuristics I mentioned.
All true in theory and practice, yet I wonder if there isn't some room
for improvement here. Where I have had success breaking a join of six
or sixteen tables into multiple queries, it hasn't seemed all that
difficult for me to guess how to decompose them best, and I do wonder
at how much better the heuristics could be.
>If your query is one of these cases, manually rewriting the query to remove
>some of the joins could possibly make things run faster for your query +
>data. It's not impossible to have such a case. I'll suggest that you can
>also use query hints, in many cases, to force the join order into one that
>may be more optimal for your query.
Never had much luck with hints.
What I'd like are optimizer hints, like "hey, this temp table is going
to only hold ten rows, so please use it as the root of your plan!")
>Root causes for picking an initial, heuristic order that is suboptimal
>include missing statistics, statistics with a sample rate that is too low,
>physical grouping of values on disk that causes problems in sampled
>statistics interpolation, correlations between columns that cause the
>optimizer to over- or under-estimate cardinality for a join or filter, or
>others. In some cases, the technique you describe (moving part of the query
>into a temporary result) can correct errors because part of the query
>becomes materialized - the cardinality of that intermediate result thus does
>not contain some of the errors and the rest of the query may have enough
>information to pick a good join order.
I don't believe I've had much of the cardinality issue, fwiw.
>Join order is one way in which this condition can happen - others also
>exist, but the basic impact is generally the same.
Any hints here, should the major tables be listed *last*? I think
most developers tend to list them first. I recall back in 6.x days
there was some order-dependency in where clauses (?) that I believe
went away as of SQL7.
>I hope that this gives you some insight into the complexities involved in
>this process.
Yes, it does. Until I started to specialize in this area, I had no
proper appreciation for just what it takes to turn a couple of lines
of SQL into an appropriate plan. I guess I would now wish for some
additional "transparency" on just WHY the optimizer chooses what it
does, so that I could use that as a basis for reorganizing code as
necessary. No doubt there are all sorts of additional topics to
explore in the area. But all in all, I *still* think there's a big,
fat bug in the compiler right now that is preventing it from doing
proper optimization of (logically independent) chunks of code in large
SPs.
Joshua Stern

Friday, March 9, 2012

I Need Your Help,About SQL2000 DataInsert

I want to insert some data into my SQLServer,For example：insert into xcjl(zch,xcsj) values('027741',getdate())

but ,the "zch" maybe have a few data,I want to insert these step by step,how can I do?

Can you explain in more detail what you are trying to accomplish?

Wednesday, March 7, 2012

I need to connect to SQLServer 7.0 from a Linux box using php

Has anyone got experience connecting to a MS SQL Server 7.0 over the
Internet from a Linux Web Server using PHP4.3.x ?
This for a Real Estate MLS IDX feed.
I have heard I can use ADODB and another said FreeTDS (Tabular
DataStream) protocol can be used. Not sure if these people had any
experience with it.
use ODBTP (http://odbtp.sf.net).
imho it's by far the best way to get PHP and MSSQL together (on both,
Windows and Unix/Linux boxes)
"ftw" wrote:

> Has anyone got experience connecting to a MS SQL Server 7.0 over the
> Internet from a Linux Web Server using PHP4.3.x ?
> This for a Real Estate MLS IDX feed.
> I have heard I can use ADODB and another said FreeTDS (Tabular
> DataStream) protocol can be used. Not sure if these people had any
> experience with it.
>
|||Why do you like ODBTP better than ADODB?
|||Well, ADODB is a library, ODBTP a PHP extension you can't compare those two,
in fact you can use ADODB with ODBTP
The biggest plus for ODBTP compared to other extensions is it's great
Unicode support and it's easier to handle than freeTDS
"ftw" wrote:

> Why do you like ODBTP better than ADODB?
>
|||Thanks for the input Sascha!

Friday, March 30, 2012

ICF, SQL SERVER 2000 and Windows 2003

Wednesday, March 28, 2012

I/O size

I/O size

I/O size

Monday, March 26, 2012

I/O error (Torn page)

Friday, March 23, 2012

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Wednesday, March 21, 2012

i want to create database

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Monday, March 19, 2012

i want to ask some sqlserver question?

Monday, March 12, 2012

I think this is a bug

I think this is a bug

I smell a big, fat compiler bug

Friday, March 9, 2012

I Need Your Help,About SQL2000 DataInsert

Wednesday, March 7, 2012

I need to connect to SQLServer 7.0 from a Linux box using php

help emergency

Blog Archive

About Me