Multi-page report: error

Showing posts with label error. Show all posts

Monday, March 26, 2012

Multiple INSERTs into a single table scaling / performance problem

Hello all ...
This one has me a bit confused. I'd really appreciate any and all
suggestions. This may be pilot error on my part...
My requirement is to have multiple programatic database applications, each
on a separate database connection insert data into a single table as fast as
possible. My hope is that I'll see some scaling of rows-per-second inserted
by adding additional processes, connections and simultaneous inserts.
I'm not able to use BCP for this as the data is coming from an application
style feed.
I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running on an
AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL Server
tends to get up around 2.9GB at times ... it is the "only" service on the
box.
Here's my table:
CREATE TABLE MyTable
(myID numeric(10,0) not null,
myChar char(8000) null,
myRowversion rowversion)
ON A_TABLE_FILEGROUP
-- No indexes, just a simple heap. I know that each row will require 8K of
-- storage ... about a page each.
My database transaction log is on a different disk/file group on a separate
controller.
I used SQL Query Analyzer for my test. I opened up a single connection and
ran a simple batch along the lines of:
begin tran
while @.i <= 10000
begin
insert into MyTable ...
values ...
select @.i = @.i + 1
end
commit
OK, when I run the above and check the start and finish times I can insert
about 1200 rows per second. I checked the disks with perf mon and I have
very little Average Disk Write Queues on the table and transaction log disks
(on avg less than 1.)
Sooooo.....
When I open up two additional sessions in SQL Query Analyzer ... and kick
off three of the same loops "at the same time" I average ... a sum total of
about 1200 rows per second. No scaling at all. Note that I am running all
three windows from within the same SQL Query Analyzer session, each with a
different connection into the database.
Again, the disks and the whole box are pretty much snoozing.
Any suggestions I might try? I'm sure I'm missing something here.
Thanks so much!!!!
DBwell...
have you try varchar instead of char?
why a numeric and not an integer?
separated disks is not enough.
What is your disk config?
do you use RAID 5 or Raid 0+1?
how many disks on each controller? (do you use SCSI 15krpm disks?)
have you some write cache on your controllers?
does your files are an good initial size? when SQL server need to expand a
file, this takes some ressources, so if your files are larger then your
requirements you'll improve the performance.
have you tested you disks subsystem using the SQLIOStress & SQLIO tools?
Using 4 local IDE drives (SATA; no cache; Raid 0 (strip)) I reach 960io/s
and 60MB/s (SQLIO results):
sqlio -kW -b64 -frandom -LS
with this result I achieve 3333 insert/sec using a script near like you (100
000 rows inserted in 30sec; no transactions)
I insert into a table like yours (int, char(8000), rowversion)
doing all the inserts into 1 transaction reduce the time of the process to
16sec.
and finally adding the set nocount on option, I reduce the process to 7sec
my log & database files are on the same disk
Its an Opteron dual core server with 4Gb and SQL 2005 x64 version.
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:50F8E5B3-2B49-4572-B2AC-89A1E77A55CC@.microsoft.com...
> Hello all ...
> This one has me a bit confused. I'd really appreciate any and all
> suggestions. This may be pilot error on my part...
> My requirement is to have multiple programatic database applications, each
> on a separate database connection insert data into a single table as fast
> as
> possible. My hope is that I'll see some scaling of rows-per-second
> inserted
> by adding additional processes, connections and simultaneous inserts.
> I'm not able to use BCP for this as the data is coming from an application
> style feed.
> I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running on
> an
> AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL Server
> tends to get up around 2.9GB at times ... it is the "only" service on the
> box.
> Here's my table:
> CREATE TABLE MyTable
> (myID numeric(10,0) not null,
> myChar char(8000) null,
> myRowversion rowversion)
> ON A_TABLE_FILEGROUP
> -- No indexes, just a simple heap. I know that each row will require 8K
> of
> -- storage ... about a page each.
> My database transaction log is on a different disk/file group on a
> separate
> controller.
> I used SQL Query Analyzer for my test. I opened up a single connection
> and
> ran a simple batch along the lines of:
> begin tran
> while @.i <= 10000
> begin
> insert into MyTable ...
> values ...
> select @.i = @.i + 1
> end
> commit
> OK, when I run the above and check the start and finish times I can insert
> about 1200 rows per second. I checked the disks with perf mon and I have
> very little Average Disk Write Queues on the table and transaction log
> disks
> (on avg less than 1.)
> Sooooo.....
> When I open up two additional sessions in SQL Query Analyzer ... and kick
> off three of the same loops "at the same time" I average ... a sum total
> of
> about 1200 rows per second. No scaling at all. Note that I am running
> all
> three windows from within the same SQL Query Analyzer session, each with a
> different connection into the database.
> Again, the disks and the whole box are pretty much snoozing.
> Any suggestions I might try? I'm sure I'm missing something here.
> Thanks so much!!!!
> DB
>|||Thank you very much for your comments ... very useful information and ideas!
Char is the requirement I'm stuck with. I'm working on a "worst case
scenario" so a char(8000) is pretty tough. I know this uses a lot more
storage space than Varchar and yes I'm only storing a single row per page.
It is a long story.
I could switch from a numeric to an int, if that would make a tremendous
scaling difference.
I'm not using any RAID or striped disk setup. The two disks I have are on
two separate controllers. One is a SATA controller and the other is a
Firewire 2.0 controller. Both disks have 16 MB of cache turned on. The data
table is on one and the transaction log is on the other. When I look at the
disk IO stats using Perfmon I don't see a lot of heavy disk IO activity.
There's virtually no Average Disk Queue on either one, and the number of
writing I/Os per second is pretty low. Perhaps my controllers are not
pushing the disks fast enough. I'm not sure. Would faster/striped disks
make a difference if Perfmon doesn't show a lot of disk activity? Are there
a couple of other Perfmon settings I should look at? Note that I'm not
having any hard page faults either.
The SQLIO is a good suggestion ... I'll try that as well.
There is a lot of pre-allocated space in both the database and transaction
log files. No problems there.
I also used the NOCOUNT ON and put the work into a single transaction ...
this did double my speed to get to the 1200 per second.
Any more ideas out there? I suppose I am making a BIG assumption that
multiple threads writing into a single table on one disk/file would be
faster. Is this a poor assumption, unless the table is set up across
multiple physical disks/files? Would the relative scaling results be similar
if the rowsize was much smaller, resulting in more rows per disk I/O? If the
disk is overworked, wouldn't the Average Write Disk Queue length be really
high?
I don't see any locking/blocking problems ... perhaps I'm missing something
here.
Thanks so much ...
DB
Doug
"Jeje" wrote:
> well...
> have you try varchar instead of char?
> why a numeric and not an integer?
> separated disks is not enough.
> What is your disk config?
> do you use RAID 5 or Raid 0+1?
> how many disks on each controller? (do you use SCSI 15krpm disks?)
> have you some write cache on your controllers?
> does your files are an good initial size? when SQL server need to expand a
> file, this takes some ressources, so if your files are larger then your
> requirements you'll improve the performance.
> have you tested you disks subsystem using the SQLIOStress & SQLIO tools?
> Using 4 local IDE drives (SATA; no cache; Raid 0 (strip)) I reach 960io/s
> and 60MB/s (SQLIO results):
> sqlio -kW -b64 -frandom -LS
> with this result I achieve 3333 insert/sec using a script near like you (100
> 000 rows inserted in 30sec; no transactions)
> I insert into a table like yours (int, char(8000), rowversion)
> doing all the inserts into 1 transaction reduce the time of the process to
> 16sec.
> and finally adding the set nocount on option, I reduce the process to 7sec
> my log & database files are on the same disk
> Its an Opteron dual core server with 4Gb and SQL 2005 x64 version.
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:50F8E5B3-2B49-4572-B2AC-89A1E77A55CC@.microsoft.com...
> > Hello all ...
> >
> > This one has me a bit confused. I'd really appreciate any and all
> > suggestions. This may be pilot error on my part...
> >
> > My requirement is to have multiple programatic database applications, each
> > on a separate database connection insert data into a single table as fast
> > as
> > possible. My hope is that I'll see some scaling of rows-per-second
> > inserted
> > by adding additional processes, connections and simultaneous inserts.
> >
> > I'm not able to use BCP for this as the data is coming from an application
> > style feed.
> >
> > I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running on
> > an
> > AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL Server
> > tends to get up around 2.9GB at times ... it is the "only" service on the
> > box.
> >
> > Here's my table:
> >
> > CREATE TABLE MyTable
> >
> > (myID numeric(10,0) not null,
> >
> > myChar char(8000) null,
> >
> > myRowversion rowversion)
> >
> > ON A_TABLE_FILEGROUP
> >
> > -- No indexes, just a simple heap. I know that each row will require 8K
> > of
> >
> > -- storage ... about a page each.
> >
> > My database transaction log is on a different disk/file group on a
> > separate
> > controller.
> >
> > I used SQL Query Analyzer for my test. I opened up a single connection
> > and
> > ran a simple batch along the lines of:
> >
> > begin tran
> >
> > while @.i <= 10000
> >
> > begin
> >
> > insert into MyTable ...
> >
> > values ...
> >
> > select @.i = @.i + 1
> >
> > end
> >
> > commit
> >
> > OK, when I run the above and check the start and finish times I can insert
> > about 1200 rows per second. I checked the disks with perf mon and I have
> > very little Average Disk Write Queues on the table and transaction log
> > disks
> > (on avg less than 1.)
> >
> > Sooooo.....
> >
> > When I open up two additional sessions in SQL Query Analyzer ... and kick
> > off three of the same loops "at the same time" I average ... a sum total
> > of
> > about 1200 rows per second. No scaling at all. Note that I am running
> > all
> > three windows from within the same SQL Query Analyzer session, each with a
> > different connection into the database.
> >
> > Again, the disks and the whole box are pretty much snoozing.
> >
> > Any suggestions I might try? I'm sure I'm missing something here.
> >
> > Thanks so much!!!!
> >
> > DB
> >
>
>|||well... what's appends if the you only use the local drive? (log + data)
the latency of an external drive is not good.
and for the price of a drive, add 3 local drives, and, if you don't care
about crash, put the 4 disks in Raid 0 (bad design, but good performance)
or the 3 new drives in raid 0 for data and the current drive for the log.
your bottleneck is at the disk level for sure.
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:055220B6-7A4B-4CF9-976A-71FEA412AECD@.microsoft.com...
> Thank you very much for your comments ... very useful information and
> ideas!
> Char is the requirement I'm stuck with. I'm working on a "worst case
> scenario" so a char(8000) is pretty tough. I know this uses a lot more
> storage space than Varchar and yes I'm only storing a single row per page.
> It is a long story.
> I could switch from a numeric to an int, if that would make a tremendous
> scaling difference.
> I'm not using any RAID or striped disk setup. The two disks I have are on
> two separate controllers. One is a SATA controller and the other is a
> Firewire 2.0 controller. Both disks have 16 MB of cache turned on. The
> data
> table is on one and the transaction log is on the other. When I look at
> the
> disk IO stats using Perfmon I don't see a lot of heavy disk IO activity.
> There's virtually no Average Disk Queue on either one, and the number of
> writing I/Os per second is pretty low. Perhaps my controllers are not
> pushing the disks fast enough. I'm not sure. Would faster/striped disks
> make a difference if Perfmon doesn't show a lot of disk activity? Are
> there
> a couple of other Perfmon settings I should look at? Note that I'm not
> having any hard page faults either.
> The SQLIO is a good suggestion ... I'll try that as well.
> There is a lot of pre-allocated space in both the database and transaction
> log files. No problems there.
> I also used the NOCOUNT ON and put the work into a single transaction ...
> this did double my speed to get to the 1200 per second.
> Any more ideas out there? I suppose I am making a BIG assumption that
> multiple threads writing into a single table on one disk/file would be
> faster. Is this a poor assumption, unless the table is set up across
> multiple physical disks/files? Would the relative scaling results be
> similar
> if the rowsize was much smaller, resulting in more rows per disk I/O? If
> the
> disk is overworked, wouldn't the Average Write Disk Queue length be really
> high?
> I don't see any locking/blocking problems ... perhaps I'm missing
> something
> here.
> Thanks so much ...
> DB
>
> --
> Doug
>
> "Jeje" wrote:
>> well...
>> have you try varchar instead of char?
>> why a numeric and not an integer?
>> separated disks is not enough.
>> What is your disk config?
>> do you use RAID 5 or Raid 0+1?
>> how many disks on each controller? (do you use SCSI 15krpm disks?)
>> have you some write cache on your controllers?
>> does your files are an good initial size? when SQL server need to expand
>> a
>> file, this takes some ressources, so if your files are larger then your
>> requirements you'll improve the performance.
>> have you tested you disks subsystem using the SQLIOStress & SQLIO tools?
>> Using 4 local IDE drives (SATA; no cache; Raid 0 (strip)) I reach 960io/s
>> and 60MB/s (SQLIO results):
>> sqlio -kW -b64 -frandom -LS
>> with this result I achieve 3333 insert/sec using a script near like you
>> (100
>> 000 rows inserted in 30sec; no transactions)
>> I insert into a table like yours (int, char(8000), rowversion)
>> doing all the inserts into 1 transaction reduce the time of the process
>> to
>> 16sec.
>> and finally adding the set nocount on option, I reduce the process to
>> 7sec
>> my log & database files are on the same disk
>> Its an Opteron dual core server with 4Gb and SQL 2005 x64 version.
>>
>> "Doug" <Doug@.discussions.microsoft.com> wrote in message
>> news:50F8E5B3-2B49-4572-B2AC-89A1E77A55CC@.microsoft.com...
>> > Hello all ...
>> >
>> > This one has me a bit confused. I'd really appreciate any and all
>> > suggestions. This may be pilot error on my part...
>> >
>> > My requirement is to have multiple programatic database applications,
>> > each
>> > on a separate database connection insert data into a single table as
>> > fast
>> > as
>> > possible. My hope is that I'll see some scaling of rows-per-second
>> > inserted
>> > by adding additional processes, connections and simultaneous inserts.
>> >
>> > I'm not able to use BCP for this as the data is coming from an
>> > application
>> > style feed.
>> >
>> > I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running
>> > on
>> > an
>> > AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL
>> > Server
>> > tends to get up around 2.9GB at times ... it is the "only" service on
>> > the
>> > box.
>> >
>> > Here's my table:
>> >
>> > CREATE TABLE MyTable
>> >
>> > (myID numeric(10,0) not null,
>> >
>> > myChar char(8000) null,
>> >
>> > myRowversion rowversion)
>> >
>> > ON A_TABLE_FILEGROUP
>> >
>> > -- No indexes, just a simple heap. I know that each row will require
>> > 8K
>> > of
>> >
>> > -- storage ... about a page each.
>> >
>> > My database transaction log is on a different disk/file group on a
>> > separate
>> > controller.
>> >
>> > I used SQL Query Analyzer for my test. I opened up a single connection
>> > and
>> > ran a simple batch along the lines of:
>> >
>> > begin tran
>> >
>> > while @.i <= 10000
>> >
>> > begin
>> >
>> > insert into MyTable ...
>> >
>> > values ...
>> >
>> > select @.i = @.i + 1
>> >
>> > end
>> >
>> > commit
>> >
>> > OK, when I run the above and check the start and finish times I can
>> > insert
>> > about 1200 rows per second. I checked the disks with perf mon and I
>> > have
>> > very little Average Disk Write Queues on the table and transaction log
>> > disks
>> > (on avg less than 1.)
>> >
>> > Sooooo.....
>> >
>> > When I open up two additional sessions in SQL Query Analyzer ... and
>> > kick
>> > off three of the same loops "at the same time" I average ... a sum
>> > total
>> > of
>> > about 1200 rows per second. No scaling at all. Note that I am running
>> > all
>> > three windows from within the same SQL Query Analyzer session, each
>> > with a
>> > different connection into the database.
>> >
>> > Again, the disks and the whole box are pretty much snoozing.
>> >
>> > Any suggestions I might try? I'm sure I'm missing something here.
>> >
>> > Thanks so much!!!!
>> >
>> > DB
>> >
>>|||Thank you so much again for helping me.
With a small investment, I can set up a configuration with two internal SATA
disk drives, both plugged into the computer motherboard. I can place the
table on one, and the transaction log on the other. For this application I
don't have to worry about a disk crash/recovery via RAID.
With a much larger investment (that I can make it this is the real
bottleneck) I suppose I could pick up some type of RAID array. If I
understand you, you're saying a RAID array with the table and transaction log
striped over several disks would run faster?
Thanks again!
DB
--
Doug
"Jeje" wrote:
> well... what's appends if the you only use the local drive? (log + data)
> the latency of an external drive is not good.
> and for the price of a drive, add 3 local drives, and, if you don't care
> about crash, put the 4 disks in Raid 0 (bad design, but good performance)
> or the 3 new drives in raid 0 for data and the current drive for the log.
> your bottleneck is at the disk level for sure.
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:055220B6-7A4B-4CF9-976A-71FEA412AECD@.microsoft.com...
> > Thank you very much for your comments ... very useful information and
> > ideas!
> >
> > Char is the requirement I'm stuck with. I'm working on a "worst case
> > scenario" so a char(8000) is pretty tough. I know this uses a lot more
> > storage space than Varchar and yes I'm only storing a single row per page.
> > It is a long story.
> >
> > I could switch from a numeric to an int, if that would make a tremendous
> > scaling difference.
> >
> > I'm not using any RAID or striped disk setup. The two disks I have are on
> > two separate controllers. One is a SATA controller and the other is a
> > Firewire 2.0 controller. Both disks have 16 MB of cache turned on. The
> > data
> > table is on one and the transaction log is on the other. When I look at
> > the
> > disk IO stats using Perfmon I don't see a lot of heavy disk IO activity.
> > There's virtually no Average Disk Queue on either one, and the number of
> > writing I/Os per second is pretty low. Perhaps my controllers are not
> > pushing the disks fast enough. I'm not sure. Would faster/striped disks
> > make a difference if Perfmon doesn't show a lot of disk activity? Are
> > there
> > a couple of other Perfmon settings I should look at? Note that I'm not
> > having any hard page faults either.
> >
> > The SQLIO is a good suggestion ... I'll try that as well.
> >
> > There is a lot of pre-allocated space in both the database and transaction
> > log files. No problems there.
> >
> > I also used the NOCOUNT ON and put the work into a single transaction ...
> > this did double my speed to get to the 1200 per second.
> >
> > Any more ideas out there? I suppose I am making a BIG assumption that
> > multiple threads writing into a single table on one disk/file would be
> > faster. Is this a poor assumption, unless the table is set up across
> > multiple physical disks/files? Would the relative scaling results be
> > similar
> > if the rowsize was much smaller, resulting in more rows per disk I/O? If
> > the
> > disk is overworked, wouldn't the Average Write Disk Queue length be really
> > high?
> >
> > I don't see any locking/blocking problems ... perhaps I'm missing
> > something
> > here.
> >
> > Thanks so much ...
> >
> > DB
> >
> >
> >
> > --
> > Doug
> >
> >
> > "Jeje" wrote:
> >
> >> well...
> >>
> >> have you try varchar instead of char?
> >> why a numeric and not an integer?
> >>
> >> separated disks is not enough.
> >> What is your disk config?
> >> do you use RAID 5 or Raid 0+1?
> >> how many disks on each controller? (do you use SCSI 15krpm disks?)
> >> have you some write cache on your controllers?
> >>
> >> does your files are an good initial size? when SQL server need to expand
> >> a
> >> file, this takes some ressources, so if your files are larger then your
> >> requirements you'll improve the performance.
> >>
> >> have you tested you disks subsystem using the SQLIOStress & SQLIO tools?
> >> Using 4 local IDE drives (SATA; no cache; Raid 0 (strip)) I reach 960io/s
> >> and 60MB/s (SQLIO results):
> >> sqlio -kW -b64 -frandom -LS
> >>
> >> with this result I achieve 3333 insert/sec using a script near like you
> >> (100
> >> 000 rows inserted in 30sec; no transactions)
> >> I insert into a table like yours (int, char(8000), rowversion)
> >> doing all the inserts into 1 transaction reduce the time of the process
> >> to
> >> 16sec.
> >> and finally adding the set nocount on option, I reduce the process to
> >> 7sec
> >> my log & database files are on the same disk
> >> Its an Opteron dual core server with 4Gb and SQL 2005 x64 version.
> >>
> >>
> >>
> >> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> >> news:50F8E5B3-2B49-4572-B2AC-89A1E77A55CC@.microsoft.com...
> >> > Hello all ...
> >> >
> >> > This one has me a bit confused. I'd really appreciate any and all
> >> > suggestions. This may be pilot error on my part...
> >> >
> >> > My requirement is to have multiple programatic database applications,
> >> > each
> >> > on a separate database connection insert data into a single table as
> >> > fast
> >> > as
> >> > possible. My hope is that I'll see some scaling of rows-per-second
> >> > inserted
> >> > by adding additional processes, connections and simultaneous inserts.
> >> >
> >> > I'm not able to use BCP for this as the data is coming from an
> >> > application
> >> > style feed.
> >> >
> >> > I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running
> >> > on
> >> > an
> >> > AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL
> >> > Server
> >> > tends to get up around 2.9GB at times ... it is the "only" service on
> >> > the
> >> > box.
> >> >
> >> > Here's my table:
> >> >
> >> > CREATE TABLE MyTable
> >> >
> >> > (myID numeric(10,0) not null,
> >> >
> >> > myChar char(8000) null,
> >> >
> >> > myRowversion rowversion)
> >> >
> >> > ON A_TABLE_FILEGROUP
> >> >
> >> > -- No indexes, just a simple heap. I know that each row will require
> >> > 8K
> >> > of
> >> >
> >> > -- storage ... about a page each.
> >> >
> >> > My database transaction log is on a different disk/file group on a
> >> > separate
> >> > controller.
> >> >
> >> > I used SQL Query Analyzer for my test. I opened up a single connection
> >> > and
> >> > ran a simple batch along the lines of:
> >> >
> >> > begin tran
> >> >
> >> > while @.i <= 10000
> >> >
> >> > begin
> >> >
> >> > insert into MyTable ...
> >> >
> >> > values ...
> >> >
> >> > select @.i = @.i + 1
> >> >
> >> > end
> >> >
> >> > commit
> >> >
> >> > OK, when I run the above and check the start and finish times I can
> >> > insert
> >> > about 1200 rows per second. I checked the disks with perf mon and I
> >> > have
> >> > very little Average Disk Write Queues on the table and transaction log
> >> > disks
> >> > (on avg less than 1.)
> >> >
> >> > Sooooo.....
> >> >
> >> > When I open up two additional sessions in SQL Query Analyzer ... and
> >> > kick
> >> > off three of the same loops "at the same time" I average ... a sum
> >> > total
> >> > of
> >> > about 1200 rows per second. No scaling at all. Note that I am running
> >> > all
> >> > three windows from within the same SQL Query Analyzer session, each
> >> > with a
> >> > different connection into the database.
> >> >
> >> > Again, the disks and the whole box are pretty much snoozing.
> >> >
> >> > Any suggestions I might try? I'm sure I'm missing something here.
> >> >
> >> > Thanks so much!!!!
> >> >
> >> > DB
> >> >
> >>
> >>
> >>
>
>|||--
Doug
"Jeje" wrote:
> well... what's appends if the you only use the local drive? (log + data)
> the latency of an external drive is not good.
> and for the price of a drive, add 3 local drives, and, if you don't care
> about crash, put the 4 disks in Raid 0 (bad design, but good performance)
> or the 3 new drives in raid 0 for data and the current drive for the log.
> your bottleneck is at the disk level for sure.
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:055220B6-7A4B-4CF9-976A-71FEA412AECD@.microsoft.com...
> > Thank you very much for your comments ... very useful information and
> > ideas!
> >
> > Char is the requirement I'm stuck with. I'm working on a "worst case
> > scenario" so a char(8000) is pretty tough. I know this uses a lot more
> > storage space than Varchar and yes I'm only storing a single row per page.
> > It is a long story.
> >
> > I could switch from a numeric to an int, if that would make a tremendous
> > scaling difference.
> >
> > I'm not using any RAID or striped disk setup. The two disks I have are on
> > two separate controllers. One is a SATA controller and the other is a
> > Firewire 2.0 controller. Both disks have 16 MB of cache turned on. The
> > data
> > table is on one and the transaction log is on the other. When I look at
> > the
> > disk IO stats using Perfmon I don't see a lot of heavy disk IO activity.
> > There's virtually no Average Disk Queue on either one, and the number of
> > writing I/Os per second is pretty low. Perhaps my controllers are not
> > pushing the disks fast enough. I'm not sure. Would faster/striped disks
> > make a difference if Perfmon doesn't show a lot of disk activity? Are
> > there
> > a couple of other Perfmon settings I should look at? Note that I'm not
> > having any hard page faults either.
> >
> > The SQLIO is a good suggestion ... I'll try that as well.
> >
> > There is a lot of pre-allocated space in both the database and transaction
> > log files. No problems there.
> >
> > I also used the NOCOUNT ON and put the work into a single transaction ...
> > this did double my speed to get to the 1200 per second.
> >
> > Any more ideas out there? I suppose I am making a BIG assumption that
> > multiple threads writing into a single table on one disk/file would be
> > faster. Is this a poor assumption, unless the table is set up across
> > multiple physical disks/files? Would the relative scaling results be
> > similar
> > if the rowsize was much smaller, resulting in more rows per disk I/O? If
> > the
> > disk is overworked, wouldn't the Average Write Disk Queue length be really
> > high?
> >
> > I don't see any locking/blocking problems ... perhaps I'm missing
> > something
> > here.
> >
> > Thanks so much ...
> >
> > DB
> >
> >
> >
> > --
> > Doug
> >
> >
> > "Jeje" wrote:
> >
> >> well...
> >>
> >> have you try varchar instead of char?
> >> why a numeric and not an integer?
> >>
> >> separated disks is not enough.
> >> What is your disk config?
> >> do you use RAID 5 or Raid 0+1?
> >> how many disks on each controller? (do you use SCSI 15krpm disks?)
> >> have you some write cache on your controllers?
> >>
> >> does your files are an good initial size? when SQL server need to expand
> >> a
> >> file, this takes some ressources, so if your files are larger then your
> >> requirements you'll improve the performance.
> >>
> >> have you tested you disks subsystem using the SQLIOStress & SQLIO tools?
> >> Using 4 local IDE drives (SATA; no cache; Raid 0 (strip)) I reach 960io/s
> >> and 60MB/s (SQLIO results):
> >> sqlio -kW -b64 -frandom -LS
> >>
> >> with this result I achieve 3333 insert/sec using a script near like you
> >> (100
> >> 000 rows inserted in 30sec; no transactions)
> >> I insert into a table like yours (int, char(8000), rowversion)
> >> doing all the inserts into 1 transaction reduce the time of the process
> >> to
> >> 16sec.
> >> and finally adding the set nocount on option, I reduce the process to
> >> 7sec
> >> my log & database files are on the same disk
> >> Its an Opteron dual core server with 4Gb and SQL 2005 x64 version.
> >>
> >>
> >>
> >> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> >> news:50F8E5B3-2B49-4572-B2AC-89A1E77A55CC@.microsoft.com...
> >> > Hello all ...
> >> >
> >> > This one has me a bit confused. I'd really appreciate any and all
> >> > suggestions. This may be pilot error on my part...
> >> >
> >> > My requirement is to have multiple programatic database applications,
> >> > each
> >> > on a separate database connection insert data into a single table as
> >> > fast
> >> > as
> >> > possible. My hope is that I'll see some scaling of rows-per-second
> >> > inserted
> >> > by adding additional processes, connections and simultaneous inserts.
> >> >
> >> > I'm not able to use BCP for this as the data is coming from an
> >> > application
> >> > style feed.
> >> >
> >> > I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running
> >> > on
> >> > an
> >> > AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL
> >> > Server
> >> > tends to get up around 2.9GB at times ... it is the "only" service on
> >> > the
> >> > box.
> >> >
> >> > Here's my table:
> >> >
> >> > CREATE TABLE MyTable
> >> >
> >> > (myID numeric(10,0) not null,
> >> >
> >> > myChar char(8000) null,
> >> >
> >> > myRowversion rowversion)
> >> >
> >> > ON A_TABLE_FILEGROUP
> >> >
> >> > -- No indexes, just a simple heap. I know that each row will require
> >> > 8K
> >> > of
> >> >
> >> > -- storage ... about a page each.
> >> >
> >> > My database transaction log is on a different disk/file group on a
> >> > separate
> >> > controller.
> >> >
> >> > I used SQL Query Analyzer for my test. I opened up a single connection
> >> > and
> >> > ran a simple batch along the lines of:
> >> >
> >> > begin tran
> >> >
> >> > while @.i <= 10000
> >> >
> >> > begin
> >> >
> >> > insert into MyTable ...
> >> >
> >> > values ...
> >> >
> >> > select @.i = @.i + 1
> >> >
> >> > end
> >> >
> >> > commit
> >> >
> >> > OK, when I run the above and check the start and finish times I can
> >> > insert
> >> > about 1200 rows per second. I checked the disks with perf mon and I
> >> > have
> >> > very little Average Disk Write Queues on the table and transaction log
> >> > disks
> >> > (on avg less than 1.)
> >> >
> >> > Sooooo.....
> >> >
> >> > When I open up two additional sessions in SQL Query Analyzer ... and
> >> > kick
> >> > off three of the same loops "at the same time" I average ... a sum
> >> > total
> >> > of
> >> > about 1200 rows per second. No scaling at all. Note that I am running
> >> > all
> >> > three windows from within the same SQL Query Analyzer session, each
> >> > with a
> >> > different connection into the database.
> >> >
> >> > Again, the disks and the whole box are pretty much snoozing.
> >> >
> >> > Any suggestions I might try? I'm sure I'm missing something here.
> >> >
> >> > Thanks so much!!!!
> >> >
> >> > DB
> >> >
> >>
> >>
> >>
>
>

Multiple INSERTs into a single table scaling / performance problem

Friday, March 23, 2012

Multiple insert

Hi, I'm trying to insert multiple rows with the following statment and
am getting a syntax error on line 3. The datatypes are varchars except
for status which is numeric.

insert S (S#, SNAME, STATUS, CITY)
values
('S2', 'Jones', 10, 'Paris'),
('S3', 'Blake', 30, 'Paris'),
('S4', 'Clark', 20, 'London'),
('S5', 'Adams', 30, 'Athens');

What's wrong?
Thanks,
SashiYou have to use the INSERT... SELECT form to insert multiple rows.

INSERT INTO S (s#, sname, status, city)
SELECT 'S2', 'Jones', 10, 'Paris' UNION ALL
SELECT 'S3', 'Blake', 30, 'Paris' UNION ALL
SELECT 'S4', 'Clark', 20, 'London' UNION ALL
SELECT 'S5', 'Adams', 30, 'Athens' ;

--
David Portas
SQL Server MVP
--|||The real problem is that SQL Server does not yet support SQL-92 syntax,
in spite of having the power to do so. Your choices are:

1) use a series of INSERT INTO statements (notice that INSERT is a
proprietary shorthand, not Standard SQL).

2) use a proprietary SELECT ..UNION ALL chain to build a table the same
way that the VALUES table constructor would.

Multiple FTP tasks that connect to same server at same time error

I have this job that download 4 files once a month from the same server. The files are sizable and I need to download them in less than 5 hours total. In 2000 I use an active x script to generate the ftp script then execute the script. all four files download at the same time in 4 different tasks with no issues.

I am rewrote the process in 2005 so that it uses the IS FTP function but when all 4 ftp tasks kick off they all fail... instantly. the initially shared the same FTP connection manager so I created different ones for each and still the same result

the error is one that relates to changing directories.... Now if I just run one of the tasks it runs fine it is just when more than one try to run at once. I ended up putting in 10 second delays between each ftp task kicking off and it works just fine...

Does this sound like a bug?

Also... I am on SQL 2005 Enterprise SP1 on Windows 2003 enterprise SP1.

I can also reproduce the problem when multiple FTP tasks share the same FTP connection manager. But everything works fine if I create individual FTP connection manager for each task. This does sound like a bug to me so I am going to log a bug issue. We will investigate the problem and address it ASAP. Thanks you|||Thanks...

Wednesday, March 21, 2012

Multiple DropDowns error

Hi:

I have two drop downs bound to the same data source.. These dropdowns are automatically populated from a database. When I click the button I get some sort of strange query error.

Not sure what I'm doing wrong here.

<%@.ImportNamespace="System.Data" %>

<%@.ImportNamespace="System.Data.SQLClient" %>

<scriptlanguage="VB"runat="server">

Dim sOrderbyasString

Dim sDirectionasString

Dim MySQLAsString

Dim MySQL1AsString

Dim sSubjectAsString

Dim sCategoryAsString

Sub Page_Load(ByVal SourceAsObject,ByVal EAs EventArgs)

IfNot Page.IsPostBackThen

Dim strConnAsString ="server=GAALP-DT-UHABB2\CFW;uid=sa;pwd=removed;database=NetG"

Dim MySQLAsString ="Select DISTINCT [Subject] from dbo_v_netG_courses"

Dim MySQL1AsString ="Select DISTINCT [Category] from dbo_v_netG_courses"

Dim MyConnAsNew SqlConnection(strConn)

Dim objDRAs SqlDataReader

Dim CmdAsNew SqlCommand(MySQL, MyConn)

Dim Cmd1AsNew SqlCommand(MySQL1, MyConn)

MyConn.Open()

objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

ddl.DataSource = objDR

ddl.DataValueField ="Subject"

ddl.DataTextField ="Subject"

ddl.DataBind()

MyConn.Close()

MyConn.Open()

ddlDir.DataSource = Cmd1.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

ddlDir.DataValueField ="Category"

ddlDir.DataTextField ="Category"

ddlDir.DataBind()

MyConn.Close()

ddl.Items.Insert(0,"-- Choose --")

ddlDir.Items.Insert(0,"-- Choose --")

EndIf

'ddl.Items.Insert(0, "-- Choose --")

EndSub

' Sub Page_Change(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs)

' MyDataGrid.CurrentPageIndex = e.NewPageIndex

' BindData()

'Sub GridOne(ByVal Source As Object, ByVal E As EventArgs)

' MyDataGrid.CurrentPageIndex = 0

'End Sub

'Sub GetData(ByVal Source As Object, ByVal E As EventArgs)

' BindData()

' End Sub

Sub BindData(ByVal SourceAsObject,ByVal EAs EventArgs)

sSubject = ddlDir.SelectedItem.Text

sCategory = ddlDir.SelectedItem.Value

Dim strConnAsString ="server=GAALP-DT-UHABB2\CFW;uid=sa;pwd=removed;database=NetG"

If sSubject =""And sCategory =""Then

MySQL ="Select * from dbo_v_netG_courses"

Else ( THIS LINE IS GIVING ME THE ERROR)

MySQL ="Select * from dbo_v_netG_courses where [Subject] = & sSubject"

EndIf

Dim MyConnAsNew SqlConnection(strConn)

Dim dsAs DataSet =New DataSet()

Dim CmdAsNew SqlDataAdapter(MySQL, MyConn)

Cmd.Fill(ds,"dbo_v_netG_courses")

MyDataGrid.DataSource = ds.Tables("dbo_v_netG_courses").DefaultView

MyDataGrid.DataBind()

EndSub

</script>

<html>

<head>

<metaname="GENERATOR"Content="ASP Express 3.0">

<title>Ad Hoc Sorting with a DataGrid</title>

</head>

<body>

<Formid="form1"runat="server">

<table>

<tr>

<tdalign="Left"valign="Top"><b><i>View Employee Data</i></b></td>

<tdalign="right"valign="Top">

Subject:<asp:dropdownlistid="ddl"runat="server">

</asp:dropdownlist>

Category:<asp:dropdownlistid="ddlDir"runat="server">

</asp:dropdownlist><br/>

<br/>

<asp:Buttonid="btn1"Text="View Records"onclick="BindData"runat="server"/><br/>

</td>

</tr>

<tr>

<tdalign="Left"valign="Top"Colspan="2">

<asp:Datagridrunat="server"

Id="MyDataGrid"

GridLines="Both"

cellpadding="0"

cellspacing="0"

Headerstyle-BackColor="#8080C0"

Headerstyle-Font-Bold="True"

Headerstyle-Font-Size="12"

BackColor="#8080FF"

Font-Size="10"

AlternatingItemStyle-BackColor="#EFEFEF"

AlternatingItemStyle-Font-Size="10"

BorderColor="Black">

</asp:DataGrid><br> </td>

</tr>

</table>

</form>

</body>

</html>

This code works now.. One question though.. How can I return all values by default when the page loads..

Thanks

Working code in VB for multiple dropdown selections and then button click to submit values

<%@.ImportNamespace="System.Data" %>

<%@.ImportNamespace="System.Data.SQLClient" %>

<scriptlanguage="VB"runat="server">

Dim sOrderbyasString

Dim sDirectionasString

Dim MySQLAsString

Dim MySQL1AsString

Dim sSubjectAsString

Dim sCategoryAsString

Sub Page_Load(ByVal SourceAsObject,ByVal EAs EventArgs)

IfNot Page.IsPostBackThen

Dim strConnAsString ="server=LAPTOP;uid=sa;pwd=sa;database=NetG"

Dim MySQLAsString ="Select DISTINCT [Subject] from dbo_v_netG_courses"

' Dim MySQL1 As String = "Select DISTINCT [Category] from dbo_v_netG_courses"

Dim MyConnAsNew SqlConnection(strConn)

Dim objDRAs SqlDataReader

Dim CmdAsNew SqlCommand(MySQL, MyConn)

' Dim Cmd1 As New SqlCommand(MySQL1, MyConn)

MyConn.Open()

objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

ddl.DataSource = objDR

ddl.DataValueField ="Subject"

ddl.DataTextField ="Subject"

ddl.DataBind()

MyConn.Close()

' MyConn.Open()

' ddlDir.DataSource = Cmd1.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

'ddlDir.DataValueField = "Category"

' ddlDir.DataTextField = "Category"

' ddlDir.DataBind()

'MyConn.Close()

ddl.Items.Insert(0,"-- Choose --")

'ddlDir.Items.Insert(0, "-- Choose --")

EndIf

'ddl.Items.Insert(0, "-- Choose --")

EndSub

Sub fillModel(ByVal SourceAsObject,ByVal EAs EventArgs)

Dim strConnAsString ="server=LAPTOP;uid=sa;pwd=sa;database=NetG"

If ddl.SelectedItem.Text <>"-- Choose --"Then

sSubject = ddl.SelectedItem.Value

Dim MySQL1AsString ="Select DISTINCT Category from dbo_v_netG_courses where [Subject]=" &"'" & sSubject &"'"

Dim MyConnAsNew SqlConnection(strConn)

'Dim objDR As SqlDataReader

Dim Cmd1AsNew SqlCommand(MySQL1, MyConn)

MyConn.Open()

ddlDir.DataSource = Cmd1.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

ddlDir.DataValueField ="Category"

ddlDir.DataTextField ="Category"

ddlDir.DataBind()

' ddl2.selectedindex=0

' tdmodel.visible = "true"

'lit1.text = "<b>Vehicle Type</b>: " & ddl1.selectedItem.text

' ddl2.items.insert(0, "-- Choose --")

' tdStyle.visible = "false"

MyConn.Close()

EndIf

ddlDir.Items.Insert(0,"-- Choose --")

EndSub

' Sub Page_Change(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs)

' MyDataGrid.CurrentPageIndex = e.NewPageIndex

' BindData()

'Sub GridOne(ByVal Source As Object, ByVal E As EventArgs)

' MyDataGrid.CurrentPageIndex = 0

'End Sub

'Sub GetData(ByVal Source As Object, ByVal E As EventArgs)

' BindData()

' End Sub

Sub BindData(ByVal SourceAsObject,ByVal EAs EventArgs)

sSubject = ddl.SelectedItem.Value

sCategory = ddlDir.SelectedItem.Value

Dim strConnAsString ="server=LAPTOP;uid=sa;pwd=sa;database=NetG"

If sSubject =""And sCategory =""Then

MySQL ="Select * from dbo_v_netG_courses"

ElseIf sSubject ="-- Choose --"And sCategory =""Then

MySQL ="Select * from dbo_v_netG_courses"

'Page.IsPostBack = True

Else

MySQL ="Select * from dbo_v_netG_courses where [Subject]=" &"'" & sSubject &"'" &" and [Category] =" &"'" & sCategory &"'"

Dim MyConnAsNew SqlConnection(strConn)

Dim dsAs DataSet =New DataSet()

Dim CmdAsNew SqlDataAdapter(MySQL, MyConn)

Cmd.Fill(ds,"dbo_v_netG_courses")

MyDataGrid.DataSource = ds.Tables("dbo_v_netG_courses").DefaultView

MyDataGrid.DataBind()

EndIf

EndSub

</script>

<html>

<head>

<metaname="GENERATOR"Content="ASP Express 3.0">

<title>Ad Hoc Sorting with a DataGrid</title>

</head>

<body>

<Formid="form1"runat="server">

<table>

<tr>

<tdalign="Left"valign="Top"><b><i>View Employee Data</i></b></td>

<tdalign="right"valign="Top">

Subject:<asp:dropdownlistid="ddl"

runat="server"

onselectedindexchanged="fillModel"AutoPostBack="True"

</asp:dropdownlist>

Category:<asp:dropdownlistid="ddlDir"runat="server"DataTextField="Model">

</asp:dropdownlist><br/>

<br/>

<asp:Buttonid="btn1"Text="View Records"onclick="BindData"runat="server"/><br/>

</td>

</tr>

<tr>

<tdalign="Left"valign="Top"Colspan="2">

<asp:Datagridrunat="server"

Id="MyDataGrid"

cellpadding="0"

Headerstyle-BackColor="#8080C0"

Headerstyle-Font-Bold="True"

Headerstyle-Font-Size="12"

BackColor="#8080FF"

Font-Size="10pt"

AlternatingItemStyle-BackColor="#EFEFEF"

AlternatingItemStyle-Font-Size="10"

BorderColor="Black"AllowSorting="True">

<AlternatingItemStyleBackColor="#EFEFEF"Font-Size="10pt"/>

<HeaderStyleBackColor="#8080C0"Font-Bold="True"Font-Size="12pt"/>

</asp:DataGrid><br> </td>

</tr>

</table>

</form>

</body>

</html>

|||I assume this is an .NET 1.1 application?

Monday, March 19, 2012

Multiple DataSources on a DataSourceView

I have added multiple tables from different data sources to a dsv. When I try to make a model from that DSV, it throws an error, saying that it can't find any of my tables that are not the primary tables. Is this a bug or am I missing something that I should be doing?

TITLE: Microsoft Visual Studio

An error occurred while executing a command.
Message: Invalid object name 'dbo.CategoryID'.
Command:
SELECT COUNT(*) FROM [dbo].[CategoryID] t

BUTTONS:

Thanks!

Nathan

I have done a bit of research on the issue and I am now thinking it may be a problem w/ my databases, but I am not sure. What I do need is to confirm that someone has used DSV's that contain multiple datasources with success. Thanks!

Nathan

Multiple DataSources on a DataSourceView

TITLE: Microsoft Visual Studio

An error occurred while executing a command.
Message: Invalid object name 'dbo.CategoryID'.
Command:
SELECT COUNT(*) FROM [dbo].[CategoryID] t

BUTTONS:

Thanks!

Nathan

Multiple datasets

I have an area of my report that needs multiple data sets to be used to
populate the area. I keep getting the error "Report item expressions can only
refer to fields wtihin the current data set scope". Does anyone know how to
make the area allow me to use one data set in one row and one in data set in
the other? I have tried a table within a table, a list within a table....I
cannot find the fix to this problem. Please help!Have a look at subreports.
"KimB" <KimB@.discussions.microsoft.com> wrote in message
news:C6D864D3-BED0-48D2-BB39-C4C642A2B9C0@.microsoft.com...
> I have an area of my report that needs multiple data sets to be used to
> populate the area. I keep getting the error "Report item expressions can
only
> refer to fields wtihin the current data set scope". Does anyone know how
to
> make the area allow me to use one data set in one row and one in data set
in
> the other? I have tried a table within a table, a list within a
table....I
> cannot find the fix to this problem. Please help!|||Thank you. I will try.
"AshVsAOD" wrote:
> Have a look at subreports.
> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> news:C6D864D3-BED0-48D2-BB39-C4C642A2B9C0@.microsoft.com...
> > I have an area of my report that needs multiple data sets to be used to
> > populate the area. I keep getting the error "Report item expressions can
> only
> > refer to fields wtihin the current data set scope". Does anyone know how
> to
> > make the area allow me to use one data set in one row and one in data set
> in
> > the other? I have tried a table within a table, a list within a
> table....I
> > cannot find the fix to this problem. Please help!
>
>

Friday, March 9, 2012

Multiple Connections in Managed Trigger

Hi All,

I am trying to open multiple connections in a Managed Trigger but encoutering an error as :

System.Data.SqlClient.SqlException: Transaction context in use by another session.

Below is the sample code:

public partial class Triggers
{

[Microsoft.SqlServer.Server.SqlTrigger (Name="TrgInsertContract", Target="Contracts", Event="FOR INSERT")]
public static void TrgInsertContract()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();

SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * from " + "inserted WHERE Active=1";
SqlDataReader reader;
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
connection.Close();
SqlConnection connection1 = new SqlConnection("Initial Catalog=TestDB;Data Source=SHAIKDEV;User ID=sa;password=****");
connection1.Open();
}

Any help please ?

Thanks...

Hi Nagul!

Most probably, your trigger runs in a transaction. And I think, your second connection is a loopback connection to the same server (which creates a new session). Currently, two sessions cannot share one transaction. You can avoid the problem by placing the second connection in suppress-transaction TransactionScope:

using ( new TransactionScope (TransactionScopeOption.Suppress ) )

{

// work with second connection (open etc.)

}

|||

Hi Vadim,

This solution worked great for me! Thanks

Nick

Multiple Connections in Managed Trigger

Hi All,

I am trying to open multiple connections in a Managed Trigger but encoutering an error as :

System.Data.SqlClient.SqlException: Transaction context in use by another session.

Below is the sample code:

public partial class Triggers
{

Any help please ?

Thanks...

Hi Nagul!

using ( new TransactionScope (TransactionScopeOption.Suppress ) )

{

// work with second connection (open etc.)

}

|||

Hi Vadim,

This solution worked great for me! Thanks

Nick

Saturday, February 25, 2012

Multiple Cascade Paths Error

Can anyone tell me why on earth you can't have two cascade paths to a table
? This essentially means we cannot use RI and have to maintain triggers
for our DB.
I see that they haven't fixed it in 2005 either.Hi
You probably want to use a superset, but without DDL your question not
clear. http://www.aspfaq.com/etiquett_e.asp?id=5006
Also check out:
http://tinyurl.com/486q3
If this does solve your issue then it is a design problem and not a problem
with the RDBMS.
John
"Adrian Parker" wrote:

> Can anyone tell me why on earth you can't have two cascade paths to a tabl
e
> ? This essentially means we cannot use RI and have to maintain triggers
> for our DB.
> I see that they haven't fixed it in 2005 either.
>
>|||Please read the following pages.. you'll understand the problem then
first Microsofts page (I love their workaround)
http://support.microsoft.com/defaul...&NoWebContent=1
Then the WindowsITPro page that explains the problem in more detail
http://www.windowsitpro.com/Article...5520/25520.html
We migrated to SQL Server from sybase and oracle, which both handle the
scenarios correctly.
And no, I doubt we're going to rewrite a 10 year old application with 600
tables just to cope with a limitation in SQL Server,we'll have to continue
using triggers, which is really annoying.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:FD2C115B-CCD3-4864-8CDC-F6402B62A863@.microsoft.com...
> Hi
> You probably want to use a superset, but without DDL your question not
> clear. http://www.aspfaq.com/etiquett_e.asp?id=5006
> Also check out:
> http://tinyurl.com/486q3
> If this does solve your issue then it is a design problem and not a
> problem
> with the RDBMS.
> John
> "Adrian Parker" wrote:
>|||Hi
The example can be re-modelled as
Use tempdb
go
create table table1 (user_ID integer not null primary key, user_name
char(50) not null)
go
create table table2 (author_ID integer not null primary key,
author_name char(50) not null )
go
create table table3 (author_ID integer not null primary key, Action
varchar(20) not null check (Action =3D 'Created' OR Action =3D 'Last
Modified'), User_id integer not null )
alter table table3 add constraint fk_one foreign key (User_id)
references table1 (user_ID) on delete cascade on update cascade
go
Creating a view may remove the need for some of the code changes.
John
Adrian Parker wrote:
> Please read the following pages.. you'll understand the problem then
> first Microsofts page (I love their workaround)
>
http://support.microsoft.com/defaul...rt.microsoft.c=
om:80/support/kb/articles/q321/8/43.asp&NoWebContent=3D1
> Then the WindowsITPro page that explains the problem in more detail
> http://www.windowsitpro.com/Article...5520/25520.html
> We migrated to SQL Server from sybase and oracle, which both handle
the
> scenarios correctly.
> And no, I doubt we're going to rewrite a 10 year old application with
600
> tables just to cope with a limitation in SQL Server,we'll have to
continue
> using triggers, which is really annoying.
>
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:FD2C115B-CCD3-4864-8CDC-F6402B62A863@.microsoft.com...
not
to a
triggers

Monday, February 20, 2012

Multiple accounts with the name MSSQLSvc...

Hi,
Got a KDC Error with the following description:
========================================== Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 28-04-2005
Time: 2:01:01
User: N/A
Computer: server
Description:
There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
type DS_SERVICE_PRINCIPAL_NAME.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
==========================================
The LDP-tool gives the following results:
========================================== ***Searching...
ldap_search_s(ld, "DC=domain,DC=local", 2,
"serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
&msg)
Result <0>: (null)
Matched DNs:
Getting 2 entries:
>> Dn: CN=Administrator,CN=Users,DC=domain,DC=local
4> objectClass: top; person; organizationalPerson; user;
1> cn: Administrator;
1> description: Built-in account for administering the computer/domain;
1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=local;
1> name: Administrator;
1> canonicalName: domain.local/Users/Administrator;
>> Dn: CN=server,OU=Domain Controllers,DC=domain,DC=local
5> objectClass: top; person; organizationalPerson; user; computer;
1> cn: server;
1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
1> name: server;
1> canonicalName: domain.local/Domain Controllers/server;
==========================================
Can anyone explain me what I can do about this? Deleting one of the accounts
is not an option I guess... I read that in some cases a computer or user
should be unregistered en registered again but in this case I'm not so
confident about it re-registring the Server itself or the
administrator-account..
Any help on this is much appreciated.
Michel Schuurman
Omni Trade Automatisering B.V.Somebody setup the SPN for the service account on those machines,
unfortunately the same SPN has been promoted more than one time.
Jens Suessmeyer.
"Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> Got a KDC Error with the following description:
> ==========================================> Event Type: Error
> Event Source: KDC
> Event Category: None
> Event ID: 11
> Date: 28-04-2005
> Time: 2:01:01
> User: N/A
> Computer: server
> Description:
> There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
> type DS_SERVICE_PRINCIPAL_NAME.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ==========================================>
> The LDP-tool gives the following results:
> ==========================================> ***Searching...
> ldap_search_s(ld, "DC=domain,DC=local", 2,
> "serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
> &msg)
> Result <0>: (null)
> Matched DNs:
> Getting 2 entries:
>> Dn: CN=Administrator,CN=Users,DC=domain,DC=local
> 4> objectClass: top; person; organizationalPerson; user;
> 1> cn: Administrator;
> 1> description: Built-in account for administering the computer/domain;
> 1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=local;
> 1> name: Administrator;
> 1> canonicalName: domain.local/Users/Administrator;
>> Dn: CN=server,OU=Domain Controllers,DC=domain,DC=local
> 5> objectClass: top; person; organizationalPerson; user; computer;
> 1> cn: server;
> 1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
> 1> name: server;
> 1> canonicalName: domain.local/Domain Controllers/server;
> ==========================================> Can anyone explain me what I can do about this? Deleting one of the
> accounts is not an option I guess... I read that in some cases a computer
> or user should be unregistered en registered again but in this case I'm
> not so confident about it re-registring the Server itself or the
> administrator-account..
> Any help on this is much appreciated.
>
> Michel Schuurman
> Omni Trade Automatisering B.V.
>|||The SPN should be registered under the account SQL is starting under, and
ONLY that account.
You can use the utility setspn to check for the existence of other spn's,
delete the ones you don't want, and add the one you need.
Please note...you are NOT deleting the ACCOUNT, but the Service Principle
Name, which resides IN that user object.
Here's an article with more info than you ever wanted to know about SQL and
SPN's.:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811889
but there are links to getting setspn in there.
Donna Lambert
"Jens SÃ¼Ã?meyer" wrote:
> Somebody setup the SPN for the service account on those machines,
> unfortunately the same SPN has been promoted more than one time.
> Jens Suessmeyer.
>
> "Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
> news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Got a KDC Error with the following description:
> >
> > ==========================================> > Event Type: Error
> > Event Source: KDC
> > Event Category: None
> > Event ID: 11
> > Date: 28-04-2005
> > Time: 2:01:01
> > User: N/A
> > Computer: server
> > Description:
> > There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
> > type DS_SERVICE_PRINCIPAL_NAME.
> >
> > For more information, see Help and Support Center at
> > http://go.microsoft.com/fwlink/events.asp.
> > ==========================================> >
> >
> > The LDP-tool gives the following results:
> >
> > ==========================================> > ***Searching...
> > ldap_search_s(ld, "DC=domain,DC=local", 2,
> > "serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
> > &msg)
> > Result <0>: (null)
> > Matched DNs:
> > Getting 2 entries:
> >> Dn: CN=Administrator,CN=Users,DC=domain,DC=local
> > 4> objectClass: top; person; organizationalPerson; user;
> > 1> cn: Administrator;
> > 1> description: Built-in account for administering the computer/domain;
> > 1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=local;
> > 1> name: Administrator;
> > 1> canonicalName: domain.local/Users/Administrator;
> >> Dn: CN=server,OU=Domain Controllers,DC=domain,DC=local
> > 5> objectClass: top; person; organizationalPerson; user; computer;
> > 1> cn: server;
> > 1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
> > 1> name: server;
> > 1> canonicalName: domain.local/Domain Controllers/server;
> > ==========================================> >
> > Can anyone explain me what I can do about this? Deleting one of the
> > accounts is not an option I guess... I read that in some cases a computer
> > or user should be unregistered en registered again but in this case I'm
> > not so confident about it re-registring the Server itself or the
> > administrator-account..
> >
> > Any help on this is much appreciated.
> >
> >
> >
> > Michel Schuurman
> >
> > Omni Trade Automatisering B.V.
> >
>
>

Multipe Inserts Causes Error - Please Help

I am currently using ADO to create a recordset, once I have selected my data (1 column called customer - datatype int - about 50,000 of them), I then post a transaction to each cutomer using a stored procedure. I am using a disconnect recordset for the initial ADO recordset and after each run of the store procedure I set everything to closed then nothing. I have plenty of resources available but I still get the following error.

17066 :
SQL Server Assertion: File: <S:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.

Can anyone help, sorry if this is not infomation I can post more if needed only to be honest I don't what to extra to post.

As a matter of interest I have also had this error when importing 1.7 millon records with DTS!!Have since found out this was due to a hardware issue, it seems that it relates to CPU clocking speeds and or Video cards.

Have replaced the cpu and motherboard in my case and the problem has not returned, and I have run extensive test and re-tests

Multipart Identifier??

I am getting an error: Multi-part identifier Ad.IPCode could not be bound.
What does that mean? This is my query:
SELECT Ad.ObjectID, ObjectTypeCode
FROM dbo.ClassifiedAd Ad, dbo.Objects O
JOIN dbo.CommunityProfile CP
ON Ad.IPCode = CP.IPCode AND
CP.StatusCode = 1
WHERE Ad.StatusCode = 1 AND
Ad.Published = 1 AND
Ad.ObjectID = O.ObjectID AND
CONTAINS((SUBJECT, HTMLBody), 'for sale' )Be more axplicit in your FROM clause:
SELECT Ad.ObjectID
,ObjectTypeCode
FROM dbo.ClassifiedAd Ad
inner join dbo.Objects O
on Ad.ObjectID = O.ObjectID
JOIN dbo.CommunityProfile CP
ON Ad.IPCode = CP.IPCode
AND CP.StatusCode = 1
WHERE Ad.StatusCode = 1
AND Ad.Published = 1
AND CONTAINS((SUBJECT, HTMLBody), 'for sale' )
Oh, yes - is there an IPCode yolumn in the dbo.ClassifiedAd table?
If so, then the optimizer might have processed the explicit join (... JOIN
ON ...) before the implicit one (FROM dbo.ClassifiedAd Ad, dbo.Objects O ...
WHERE ...)
ML
http://milambda.blogspot.com/|||Why are you mixing join types (old-style vs. ANSI)? Why do you not use the
alias prefixes on all of your columns? I'll try to re-write this so the
parser understands it, but I have no idea what your table structure looks
like, so I can't fix all the prefixes.
SELECT
Ad.ObjectID,
O.ObjectTypeCode
FROM
dbo.ClassifiedAd Ad
INNER JOIN
dbo.Objects O
ON
Ad.ObjectID = O.ObjectID
INNER JOIN
dbo.CommunityProfile CP
ON
Ad.IPCode = CP.IPCode
AND CP.StatusCode = 1
WHERE
Ad.StatusCode = 1
AND Ad.Published = 1
AND CONTAINS((SUBJECT, HTMLBody), 'for sale' );

>I am getting an error: Multi-part identifier Ad.IPCode could not be bound.
> What does that mean? This is my query:
> SELECT Ad.ObjectID, ObjectTypeCode
> FROM dbo.ClassifiedAd Ad, dbo.Objects O
> JOIN dbo.CommunityProfile CP
> ON Ad.IPCode = CP.IPCode AND
> CP.StatusCode = 1
> WHERE Ad.StatusCode = 1 AND
> Ad.Published = 1 AND
> Ad.ObjectID = O.ObjectID AND
> CONTAINS((SUBJECT, HTMLBody), 'for sale' )|||try this.
Select * from tbl1
where @.searchparam like '%' + email_col + '%'
hope this helps.|||sorry... wrong thread :)
--
"Omnibuzz" wrote:

> try this.
> Select * from tbl1
> where @.searchparam like '%' + email_col + '%'
> hope this helps.
>|||Thanks Aaron. Now I get this: Syntax error near 'sale' in the full-text
search condition 'for sale'.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why are you mixing join types (old-style vs. ANSI)? Why do you not use th
e
> alias prefixes on all of your columns? I'll try to re-write this so the
> parser understands it, but I have no idea what your table structure looks
> like, so I can't fix all the prefixes.
> SELECT
> Ad.ObjectID,
> O.ObjectTypeCode
> FROM
> dbo.ClassifiedAd Ad
> INNER JOIN
> dbo.Objects O
> ON
> Ad.ObjectID = O.ObjectID
> INNER JOIN
> dbo.CommunityProfile CP
> ON
> Ad.IPCode = CP.IPCode
> AND CP.StatusCode = 1
> WHERE
> Ad.StatusCode = 1
> AND Ad.Published = 1
> AND CONTAINS((SUBJECT, HTMLBody), 'for sale' );
>
>
>
>
>
>|||> Thanks Aaron. Now I get this: Syntax error near 'sale' in the full-text
> search condition 'for sale'.
I am not overly familiar with fulltext search, so assumed that syntax was
correct. I would probably write it as a LIKE or PATINDEX condition (again,
still knowing nothing about your table schema, specifically what datatypes
are Subject and HTMLBody, and what table are they in).

Multi-part Identfier could not be found (with SQL Script)

Hi all,

I have attached the following script from which the error message come from.

Please help me out.

use PatientCare

select pr.PatientId, p.FirstName,p.LastName, m.Name

from Prescription as pr inner join Patient as p inner join Medicine as m

on p.PatientId = pr.PatientId

on pr.MedicineCode = m.MedicineCode;

Error message got is as below:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "pr.PatientId" could not be bound.

Thanx in advance.

Ronald

Ronaldlee Ejalu wrote:

use PatientCare
go
select pr.PatientId, p.FirstName,p.LastName, m.Name
from Prescription as pr inner join Patient as p inner join Medicine as m
on p.PatientId = pr.PatientId
on pr.MedicineCode = m.MedicineCode;

SELECT pr.PatientID, p.FirstName, p.LastName, m.Name

FROM Prescription pr

JOIN Patient p ON p.PatientID = pr.PatientID

JOIN Medicine m ON pr.MedicineCode = m.MedicineCode

or you can do it this way

SELECT pr.PatientID, p.FirstName, p.LastName, mName

FROM Prescription pr, Patient p, Medicine m

WHERE p.PatientID = pr.PatientID AND pr.MedicineCode = m.MedicineCode

Adamus

|||

hi ronald

looks like you got a syntax error and adamus is right
i deleted you other post cause it looks identical to this one

ragards

|||

Just to add what to has been said. You have nested joins, which I have bolded here:

select pr.PatientId, p.FirstName,p.LastName, m.Name
from Prescription as pr
inner join Patient as p
inner join Medicine as m
on p.PatientId = pr.PatientId
on pr.MedicineCode = m.MedicineCode;

This nested join has to operate first, then the next join:

select pr.PatientId, p.FirstName,p.LastName, m.Name
from Prescription as pr
inner join PatientMedicineJoinResults
on pr.MedicineCode = m.MedicineCode;

The point of this is to simplify how queries can be written when you are joining together tables, especially for lookup tables where there are outer joins involved:

select <columnlist>
from table
left outer join table2
inner join table2Lookup
on table2.table2LookupId = table2Lookup.table2LookupId
on table.tableKey = table2.tableKey
left outer join table3
inner join table3Lookup
on table3.table3LookupId = table3Lookup.table3LookupId

Not, of course that any of this matches what you need :) In your case, you are joining each of the tables to the prescription table, so the join criteria must match that, and not be nested:

select pr.PatientId, p.FirstName,p.LastName, m.Name
from Prescription as pr
inner join Patient as p
on p.PatientId = pr.PatientId
inner join Medicine as m
on pr.MedicineCode = m.MedicineCode;

|||

Hi all,

I really thank you for this.

It worked.

Monday, March 26, 2012

Multiple INSERTs into a single table scaling / performance problem

Multiple INSERTs into a single table scaling / performance problem

Friday, March 23, 2012

Multiple insert

Multiple FTP tasks that connect to same server at same time error

Wednesday, March 21, 2012

Multiple DropDowns error

Monday, March 19, 2012

Multiple DataSources on a DataSourceView

Multiple DataSources on a DataSourceView

Multiple datasets

Friday, March 9, 2012

Multiple Connections in Managed Trigger

Multiple Connections in Managed Trigger

Saturday, February 25, 2012

Multiple Cascade Paths Error

Monday, February 20, 2012

Multiple accounts with the name MSSQLSvc...

Multiple accounts with the name MSSQLSvc...

Multiple accounts with the name MSSQLSvc...

Multipe Inserts Causes Error - Please Help

Multipart Identifier??

Multi-part Identfier could not be found (with SQL Script)

Multi-page report

Blog Archive

About Me