HELP! VS .NET 2003 Server explorer

HI, you guys!
I have SQL Server 2k SP3, VS .NET 2003 Enterprise Architucture installed.
When I navigate to SQL server in Server explorer, everything is cool aside
from I can't New/Design a whatever TABLE thru the context menu but can
Create a brand-new DB.
I was told "can't find the resouce in the image file"(Error 1813)
Surfed a variety of materials in the NET already, but still have no ideas
about that.
Who could be of help ?
Thanks a lot!
--PaulIt looks to me like this is a problem with your VS.NET install rather than a
SQL Server issue. I've had no problems doing this with VS 2003.
You might try posting your question to a vsnet forum. The folks here tend
to use the SQL Server client tools. The only other suggestion I have is to
try reinstalling VS.NET.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <paulwang000@.hotmail.com> wrote in message
news:OvSlzxO5EHA.3840@.tk2msftngp13.phx.gbl...
> HI, you guys!
> I have SQL Server 2k SP3, VS .NET 2003 Enterprise Architucture installed.
> When I navigate to SQL server in Server explorer, everything is cool aside
> from I can't New/Design a whatever TABLE thru the context menu but can
> Create a brand-new DB.
> I was told "can't find the resouce in the image file"(Error 1813)
> Surfed a variety of materials in the NET already, but still have no ideas
> about that.
> Who could be of help ?
> Thanks a lot!
> --Paul

HELP! VS .NET 2003 Server explorer

HI, you guys!
I have SQL Server 2k SP3, VS .NET 2003 Enterprise Architucture installed.
When I navigate to SQL server in Server explorer, everything is cool aside
from I can't New/Design a whatever TABLE thru the context menu but can
Create a brand-new DB.
I was told "can't find the resouce in the image file"(Error 1813)
Surfed a variety of materials in the NET already, but still have no ideas
about that.
Who could be of help ?
Thanks a lot!
--Paul
It looks to me like this is a problem with your VS.NET install rather than a
SQL Server issue. I've had no problems doing this with VS 2003.
You might try posting your question to a vsnet forum. The folks here tend
to use the SQL Server client tools. The only other suggestion I have is to
try reinstalling VS.NET.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <paulwang000@.hotmail.com> wrote in message
news:OvSlzxO5EHA.3840@.tk2msftngp13.phx.gbl...
> HI, you guys!
> I have SQL Server 2k SP3, VS .NET 2003 Enterprise Architucture installed.
> When I navigate to SQL server in Server explorer, everything is cool aside
> from I can't New/Design a whatever TABLE thru the context menu but can
> Create a brand-new DB.
> I was told "can't find the resouce in the image file"(Error 1813)
> Surfed a variety of materials in the NET already, but still have no ideas
> about that.
> Who could be of help ?
> Thanks a lot!
> --Paul

Help! production memory issues??

We just started having some issues with our productin
server. We are runnning SQL2k sp3 on Win2k. We have 3 gigs
of memory and after checking the process, we are currently
only running at 1.9 gig.
The applications connecting to the database seem to be ok,
but we are unable to run any jobs via sql agent. We've
tried stopping/starting the agent and flushing the cache..
Were now going to try and reboot the server.
Here's the error messages were getting, any help would be
greatly appreciated. At this point, we can't even monitor
with Profiler because it says it dosen't have enough
memory to run. I just want to figure out the cause and
prevent it from happening again..
Please help
WARNING: Failed to reserve contiguous memory of Size= 131072.
Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
Available=111911
Global Memory Objects: Resource=1981 Locks=168
SQLCache=77 Replication=2
LockBytes=2 ServerGlobal=45
Xact=200
Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
OS Committed=1617
OS In Use=1611
Query Plan=5575 Optimizer=0
General=2391
Utilities=24 Connection=54120
Procedure Cache: TotalProcs=925 TotalPages=5550
InUsePages=3582
Buffer Counts: Commited=208688 Target=208688 Hashed=143148
InternalReservation=645
ExternalReservation=0 Min Free=508
Buffer Distribution: Stolen=55984 Free=4006
Procedures=5550
Inram=0 Dirty=5718 Kept=0
I/O=1, Latched=133, Other=137296Seems like you've configured SQL Server to use a *lot* of memory, more than
available. Can you post the output from:
sp_configure
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"dba4theday@.yahoo.com" <anonymous@.discussions.microsoft.com> wrote in
message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
> We just started having some issues with our productin
> server. We are runnning SQL2k sp3 on Win2k. We have 3 gigs
> of memory and after checking the process, we are currently
> only running at 1.9 gig.
> The applications connecting to the database seem to be ok,
> but we are unable to run any jobs via sql agent. We've
> tried stopping/starting the agent and flushing the cache..
> Were now going to try and reboot the server.
> Here's the error messages were getting, any help would be
> greatly appreciated. At this point, we can't even monitor
> with Profiler because it says it dosen't have enough
> memory to run. I just want to figure out the cause and
> prevent it from happening again..
> Please help
> WARNING: Failed to reserve contiguous memory of Size=> 131072.
> Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
> Available=111911
> Global Memory Objects: Resource=1981 Locks=168
> SQLCache=77 Replication=2
> LockBytes=2 ServerGlobal=45
> Xact=200
> Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
> OS Committed=1617
> OS In Use=1611
> Query Plan=5575 Optimizer=0
> General=2391
> Utilities=24 Connection=54120
> Procedure Cache: TotalProcs=925 TotalPages=5550
> InUsePages=3582
> Buffer Counts: Commited=208688 Target=208688 Hashed=143148
> InternalReservation=645
> ExternalReservation=0 Min Free=508
> Buffer Distribution: Stolen=55984 Free=4006
> Procedures=5550
> Inram=0 Dirty=5718 Kept=0
> I/O=1, Latched=133, Other=137296|||name minimum
maximum config_value run_value
-- -- --
-- --
affinity mask -2147483648
2147483647 0 0
allow updates 0
1 0 0
awe enabled 0
1 0 0
c2 audit mode 0
1 0 0
cost threshold for parallelism 0
32767 5 5
Cross DB Ownership Chaining 0
1 1 1
cursor threshold -1
2147483647 -1 -1
default full-text language 0
2147483647 1033 1033
default language 0
9999 0 0
fill factor (%) 0
100 0 0
index create memory (KB) 704
2147483647 0 0
lightweight pooling 0
1 0 0
locks 5000
2147483647 0 0
max degree of parallelism 0
32 0 0
max server memory (MB) 4
2147483647 2147483647 2147483647
max text repl size (B) 0
2147483647 65536 65536
max worker threads 32
32767 255 255
media retention 0
365 0 0
min memory per query (KB) 512
2147483647 1024 1024
min server memory (MB) 0
2147483647 0 0
nested triggers 0
1 1 1
network packet size (B) 512
65536 4096 4096
open objects 0
2147483647 0 0
priority boost 0
1 0 0
query governor cost limit 0
2147483647 0 0
query wait (s) -1
2147483647 -1 -1
recovery interval (min) 0
32767 0 0
remote access 0
1 1 1
remote login timeout (s) 0
2147483647 20 20
remote proc trans 0
1 0 0
remote query timeout (s) 0
2147483647 600 600
scan for startup procs 0
1 1 1
set working set size 0
1 0 0
show advanced options 0
1 1 1
two digit year cutoff 1753
9999 2049 2049
user connections 0
32767 0 0
user options 0
32767 0 0
>--Original Message--
>Seems like you've configured SQL Server to use a *lot* of
memory, more than
>available. Can you post the output from:
>sp_configure
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"dba4theday@.yahoo.com"
<anonymous@.discussions.microsoft.com> wrote in
>message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
>> We just started having some issues with our productin
>> server. We are runnning SQL2k sp3 on Win2k. We have 3
gigs
>> of memory and after checking the process, we are
currently
>> only running at 1.9 gig.
>> The applications connecting to the database seem to be
ok,
>> but we are unable to run any jobs via sql agent. We've
>> tried stopping/starting the agent and flushing the
cache..
>> Were now going to try and reboot the server.
>> Here's the error messages were getting, any help would
be
>> greatly appreciated. At this point, we can't even
monitor
>> with Profiler because it says it dosen't have enough
>> memory to run. I just want to figure out the cause and
>> prevent it from happening again..
>> Please help
>> WARNING: Failed to reserve contiguous memory of Size=>> 131072.
>> Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
>> Available=111911
>> Global Memory Objects: Resource=1981 Locks=168
>> SQLCache=77 Replication=2
>> LockBytes=2 ServerGlobal=45
>> Xact=200
>> Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
>> OS Committed=1617
>> OS In Use=1611
>> Query Plan=5575 Optimizer=0
>> General=2391
>> Utilities=24 Connection=54120
>> Procedure Cache: TotalProcs=925 TotalPages=5550
>> InUsePages=3582
>> Buffer Counts: Commited=208688 Target=208688
Hashed=143148
>> InternalReservation=645
>> ExternalReservation=0 Min Free=508
>> Buffer Distribution: Stolen=55984 Free=4006
>> Procedures=5550
>> Inram=0 Dirty=5718 Kept=0
>> I/O=1, Latched=133, Other=137296
>
>.
>|||Nothing strange there. Are you sure there isn't anything else that is using
all the memory? Anything in the agent log?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:032201c3bf5a$504b7470$a301280a@.phx.gbl...
> name minimum
> maximum config_value run_value
> -- -- --
> -- --
> affinity mask -2147483648
> 2147483647 0 0
> allow updates 0
> 1 0 0
> awe enabled 0
> 1 0 0
> c2 audit mode 0
> 1 0 0
> cost threshold for parallelism 0
> 32767 5 5
> Cross DB Ownership Chaining 0
> 1 1 1
> cursor threshold -1
> 2147483647 -1 -1
> default full-text language 0
> 2147483647 1033 1033
> default language 0
> 9999 0 0
> fill factor (%) 0
> 100 0 0
> index create memory (KB) 704
> 2147483647 0 0
> lightweight pooling 0
> 1 0 0
> locks 5000
> 2147483647 0 0
> max degree of parallelism 0
> 32 0 0
> max server memory (MB) 4
> 2147483647 2147483647 2147483647
> max text repl size (B) 0
> 2147483647 65536 65536
> max worker threads 32
> 32767 255 255
> media retention 0
> 365 0 0
> min memory per query (KB) 512
> 2147483647 1024 1024
> min server memory (MB) 0
> 2147483647 0 0
> nested triggers 0
> 1 1 1
> network packet size (B) 512
> 65536 4096 4096
> open objects 0
> 2147483647 0 0
> priority boost 0
> 1 0 0
> query governor cost limit 0
> 2147483647 0 0
> query wait (s) -1
> 2147483647 -1 -1
> recovery interval (min) 0
> 32767 0 0
> remote access 0
> 1 1 1
> remote login timeout (s) 0
> 2147483647 20 20
> remote proc trans 0
> 1 0 0
> remote query timeout (s) 0
> 2147483647 600 600
> scan for startup procs 0
> 1 1 1
> set working set size 0
> 1 0 0
> show advanced options 0
> 1 1 1
> two digit year cutoff 1753
> 9999 2049 2049
> user connections 0
> 32767 0 0
> user options 0
> 32767 0 0
>
> >--Original Message--
> >Seems like you've configured SQL Server to use a *lot* of
> memory, more than
> >available. Can you post the output from:
> >sp_configure
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"dba4theday@.yahoo.com"
> <anonymous@.discussions.microsoft.com> wrote in
> >message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
> >> We just started having some issues with our productin
> >> server. We are runnning SQL2k sp3 on Win2k. We have 3
> gigs
> >> of memory and after checking the process, we are
> currently
> >> only running at 1.9 gig.
> >>
> >> The applications connecting to the database seem to be
> ok,
> >> but we are unable to run any jobs via sql agent. We've
> >> tried stopping/starting the agent and flushing the
> cache..
> >> Were now going to try and reboot the server.
> >> Here's the error messages were getting, any help would
> be
> >> greatly appreciated. At this point, we can't even
> monitor
> >> with Profiler because it says it dosen't have enough
> >> memory to run. I just want to figure out the cause and
> >> prevent it from happening again..
> >>
> >> Please help
> >>
> >> WARNING: Failed to reserve contiguous memory of Size=> >> 131072.
> >> Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
> >> Available=111911
> >> Global Memory Objects: Resource=1981 Locks=168
> >> SQLCache=77 Replication=2
> >> LockBytes=2 ServerGlobal=45
> >> Xact=200
> >> Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
> >> OS Committed=1617
> >> OS In Use=1611
> >> Query Plan=5575 Optimizer=0
> >> General=2391
> >> Utilities=24 Connection=54120
> >> Procedure Cache: TotalProcs=925 TotalPages=5550
> >> InUsePages=3582
> >> Buffer Counts: Commited=208688 Target=208688
> Hashed=143148
> >> InternalReservation=645
> >> ExternalReservation=0 Min Free=508
> >> Buffer Distribution: Stolen=55984 Free=4006
> >> Procedures=5550
> >> Inram=0 Dirty=5718 Kept=0
> >> I/O=1, Latched=133, Other=137296
> >
> >
> >.
> >|||Well after further investigation we found that there were
100's of spid dated back to November coming in via SQL DMO.
Those connections were still left open and we believe they
have been the cause as the job calling the sp_oa proc's
started the same time we started seeing the error messages.
It states that there was an issue with those type of
proc's but it was fixed in SP1, were now on SP3...
The server has been running fine since we rebooted and if
we see this issue again, we are going to try and kill the
spid prior to the reboot to see if that fixes the problem.
Do you have any other idea's?
>--Original Message--
>Nothing strange there. Are you sure there isn't anything
else that is using
>all the memory? Anything in the agent log?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:032201c3bf5a$504b7470$a301280a@.phx.gbl...
>> name minimum
>> maximum config_value run_value
>> -- -- --
--
>> -- --
>> affinity mask -2147483648
>> 2147483647 0 0
>> allow updates 0
>> 1 0 0
>> awe enabled 0
>> 1 0 0
>> c2 audit mode 0
>> 1 0 0
>> cost threshold for parallelism 0
>> 32767 5 5
>> Cross DB Ownership Chaining 0
>> 1 1 1
>> cursor threshold -1
>> 2147483647 -1 -1
>> default full-text language 0
>> 2147483647 1033 1033
>> default language 0
>> 9999 0 0
>> fill factor (%) 0
>> 100 0 0
>> index create memory (KB) 704
>> 2147483647 0 0
>> lightweight pooling 0
>> 1 0 0
>> locks 5000
>> 2147483647 0 0
>> max degree of parallelism 0
>> 32 0 0
>> max server memory (MB) 4
>> 2147483647 2147483647 2147483647
>> max text repl size (B) 0
>> 2147483647 65536 65536
>> max worker threads 32
>> 32767 255 255
>> media retention 0
>> 365 0 0
>> min memory per query (KB) 512
>> 2147483647 1024 1024
>> min server memory (MB) 0
>> 2147483647 0 0
>> nested triggers 0
>> 1 1 1
>> network packet size (B) 512
>> 65536 4096 4096
>> open objects 0
>> 2147483647 0 0
>> priority boost 0
>> 1 0 0
>> query governor cost limit 0
>> 2147483647 0 0
>> query wait (s) -1
>> 2147483647 -1 -1
>> recovery interval (min) 0
>> 32767 0 0
>> remote access 0
>> 1 1 1
>> remote login timeout (s) 0
>> 2147483647 20 20
>> remote proc trans 0
>> 1 0 0
>> remote query timeout (s) 0
>> 2147483647 600 600
>> scan for startup procs 0
>> 1 1 1
>> set working set size 0
>> 1 0 0
>> show advanced options 0
>> 1 1 1
>> two digit year cutoff 1753
>> 9999 2049 2049
>> user connections 0
>> 32767 0 0
>> user options 0
>> 32767 0 0
>>
>> >--Original Message--
>> >Seems like you've configured SQL Server to use a *lot*
of
>> memory, more than
>> >available. Can you post the output from:
>> >sp_configure
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"dba4theday@.yahoo.com"
>> <anonymous@.discussions.microsoft.com> wrote in
>> >message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
>> >> We just started having some issues with our productin
>> >> server. We are runnning SQL2k sp3 on Win2k. We have 3
>> gigs
>> >> of memory and after checking the process, we are
>> currently
>> >> only running at 1.9 gig.
>> >>
>> >> The applications connecting to the database seem to
be
>> ok,
>> >> but we are unable to run any jobs via sql agent.
We've
>> >> tried stopping/starting the agent and flushing the
>> cache..
>> >> Were now going to try and reboot the server.
>> >> Here's the error messages were getting, any help
would
>> be
>> >> greatly appreciated. At this point, we can't even
>> monitor
>> >> with Profiler because it says it dosen't have enough
>> >> memory to run. I just want to figure out the cause
and
>> >> prevent it from happening again..
>> >>
>> >> Please help
>> >>
>> >> WARNING: Failed to reserve contiguous memory of
Size=>> >> 131072.
>> >> Query Memory Manager: Grants=0 Waiting=0
Maximum=111911
>> >> Available=111911
>> >> Global Memory Objects: Resource=1981 Locks=168
>> >> SQLCache=77 Replication=2
>> >> LockBytes=2 ServerGlobal=45
>> >> Xact=200
>> >> Dynamic Memory Manager: Stolen=61534 OS
Reserved=1656
>> >> OS Committed=1617
>> >> OS In Use=1611
>> >> Query Plan=5575 Optimizer=0
>> >> General=2391
>> >> Utilities=24 Connection=54120
>> >> Procedure Cache: TotalProcs=925 TotalPages=5550
>> >> InUsePages=3582
>> >> Buffer Counts: Commited=208688 Target=208688
>> Hashed=143148
>> >> InternalReservation=645
>> >> ExternalReservation=0 Min Free=508
>> >> Buffer Distribution: Stolen=55984 Free=4006
>> >> Procedures=5550
>> >> Inram=0 Dirty=5718 Kept=0
>> >> I/O=1, Latched=133, Other=137296
>> >
>> >
>> >.
>> >
>
>.
>|||I think there's a good chance that you've found the problem, I can very well
imagine a memory leak related to sp_OA etc.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:0ba601c3bf6a$62e77380$a001280a@.phx.gbl...
> Well after further investigation we found that there were
> 100's of spid dated back to November coming in via SQL DMO.
> Those connections were still left open and we believe they
> have been the cause as the job calling the sp_oa proc's
> started the same time we started seeing the error messages.
> It states that there was an issue with those type of
> proc's but it was fixed in SP1, were now on SP3...
> The server has been running fine since we rebooted and if
> we see this issue again, we are going to try and kill the
> spid prior to the reboot to see if that fixes the problem.
> Do you have any other idea's?
> >--Original Message--
> >Nothing strange there. Are you sure there isn't anything
> else that is using
> >all the memory? Anything in the agent log?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:032201c3bf5a$504b7470$a301280a@.phx.gbl...
> >> name minimum
> >> maximum config_value run_value
> >> -- -- --
> --
> >> -- --
> >> affinity mask -2147483648
> >> 2147483647 0 0
> >> allow updates 0
> >> 1 0 0
> >> awe enabled 0
> >> 1 0 0
> >> c2 audit mode 0
> >> 1 0 0
> >> cost threshold for parallelism 0
> >> 32767 5 5
> >> Cross DB Ownership Chaining 0
> >> 1 1 1
> >> cursor threshold -1
> >> 2147483647 -1 -1
> >> default full-text language 0
> >> 2147483647 1033 1033
> >> default language 0
> >> 9999 0 0
> >> fill factor (%) 0
> >> 100 0 0
> >> index create memory (KB) 704
> >> 2147483647 0 0
> >> lightweight pooling 0
> >> 1 0 0
> >> locks 5000
> >> 2147483647 0 0
> >> max degree of parallelism 0
> >> 32 0 0
> >> max server memory (MB) 4
> >> 2147483647 2147483647 2147483647
> >> max text repl size (B) 0
> >> 2147483647 65536 65536
> >> max worker threads 32
> >> 32767 255 255
> >> media retention 0
> >> 365 0 0
> >> min memory per query (KB) 512
> >> 2147483647 1024 1024
> >> min server memory (MB) 0
> >> 2147483647 0 0
> >> nested triggers 0
> >> 1 1 1
> >> network packet size (B) 512
> >> 65536 4096 4096
> >> open objects 0
> >> 2147483647 0 0
> >> priority boost 0
> >> 1 0 0
> >> query governor cost limit 0
> >> 2147483647 0 0
> >> query wait (s) -1
> >> 2147483647 -1 -1
> >> recovery interval (min) 0
> >> 32767 0 0
> >> remote access 0
> >> 1 1 1
> >> remote login timeout (s) 0
> >> 2147483647 20 20
> >> remote proc trans 0
> >> 1 0 0
> >> remote query timeout (s) 0
> >> 2147483647 600 600
> >> scan for startup procs 0
> >> 1 1 1
> >> set working set size 0
> >> 1 0 0
> >> show advanced options 0
> >> 1 1 1
> >> two digit year cutoff 1753
> >> 9999 2049 2049
> >> user connections 0
> >> 32767 0 0
> >> user options 0
> >> 32767 0 0
> >>
> >>
> >> >--Original Message--
> >> >Seems like you've configured SQL Server to use a *lot*
> of
> >> memory, more than
> >> >available. Can you post the output from:
> >> >sp_configure
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at:
> >> >http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"dba4theday@.yahoo.com"
> >> <anonymous@.discussions.microsoft.com> wrote in
> >> >message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
> >> >> We just started having some issues with our productin
> >> >> server. We are runnning SQL2k sp3 on Win2k. We have 3
> >> gigs
> >> >> of memory and after checking the process, we are
> >> currently
> >> >> only running at 1.9 gig.
> >> >>
> >> >> The applications connecting to the database seem to
> be
> >> ok,
> >> >> but we are unable to run any jobs via sql agent.
> We've
> >> >> tried stopping/starting the agent and flushing the
> >> cache..
> >> >> Were now going to try and reboot the server.
> >> >> Here's the error messages were getting, any help
> would
> >> be
> >> >> greatly appreciated. At this point, we can't even
> >> monitor
> >> >> with Profiler because it says it dosen't have enough
> >> >> memory to run. I just want to figure out the cause
> and
> >> >> prevent it from happening again..
> >> >>
> >> >> Please help
> >> >>
> >> >> WARNING: Failed to reserve contiguous memory of
> Size=> >> >> 131072.
> >> >> Query Memory Manager: Grants=0 Waiting=0
> Maximum=111911
> >> >> Available=111911
> >> >> Global Memory Objects: Resource=1981 Locks=168
> >> >> SQLCache=77 Replication=2
> >> >> LockBytes=2 ServerGlobal=45
> >> >> Xact=200
> >> >> Dynamic Memory Manager: Stolen=61534 OS
> Reserved=1656
> >> >> OS Committed=1617
> >> >> OS In Use=1611
> >> >> Query Plan=5575 Optimizer=0
> >> >> General=2391
> >> >> Utilities=24 Connection=54120
> >> >> Procedure Cache: TotalProcs=925 TotalPages=5550
> >> >> InUsePages=3582
> >> >> Buffer Counts: Commited=208688 Target=208688
> >> Hashed=143148
> >> >> InternalReservation=645
> >> >> ExternalReservation=0 Min Free=508
> >> >> Buffer Distribution: Stolen=55984 Free=4006
> >> >> Procedures=5550
> >> >> Inram=0 Dirty=5718 Kept=0
> >> >> I/O=1, Latched=133, Other=137296
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

HELP! full text catalogs disapearing!

I have one publisher that pushes out a database to two subscribers, each with
Win 2003 server and SQL 2000 sp3. This Db has two tables who each have one
column full text indexed. The publisher is full text indexed so that QA can
test the data before replication happens.
my problem is that after the replication finishes sync'ing up the
subscribers, My tables are no longer full text enabled and my catalogue
disappears.
Can someone tell me how to avoid this? It takes about 5 ours to drop and
rebuild the catalog.
Thank you in advance!
Carl,
Unfortunately SQL Server 2000 FT Catalogs are not directly supported with
Replication. The one-time snapshot of your table does not re-create the FT
Catalog parameter for the FT-enable table and once the table is created on
your subscriber, you will need to manually re-create the FT Catalog and set
Change Tracking with Update Index in Background and this will run a Full
Population (if the FT Catalog is un-populated).
Could you provide more details on how your subscribers tables are setup?
Thanks,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
> I have one publisher that pushes out a database to two subscribers, each
with
> Win 2003 server and SQL 2000 sp3. This Db has two tables who each have one
> column full text indexed. The publisher is full text indexed so that QA
can
> test the data before replication happens.
> my problem is that after the replication finishes sync'ing up the
> subscribers, My tables are no longer full text enabled and my catalogue
> disappears.
> Can someone tell me how to avoid this? It takes about 5 ours to drop and
> rebuild the catalog.
> Thank you in advance!
|||I am using transactional replication to push the changes from my publisher to
the subscribers. Here is the schema for one subscriber/publisher table:
CREATE TABLE defdba (
UID bigint NOT NULL CONSTRAINT MSrepl_synctran_identity_default_1525580473
DEFAULT (0),
NAME varchar(256) NULL ,
DBAID int NULL ,
JUDGEMENTID int NULL ,
MERLINNAME varchar(300) NULL ,
ADDDATE varchar(8) NOT NULL ,
msrepl_tran_version uniqueidentifier NOT NULL CONSTRAINT
DF__defdba__msrepl_t__5C37ACAD DEFAULT (newid()),
CONSTRAINT PK_defdba PRIMARY KEY CLUSTERED
(
UID
) ON PRIMARY
)
I had set up the FTI to check for changes in the background, and then update
itself when replication push the data changes accross to the subscriber. From
what I am hearing you say, after I replicate the changes over, I must rebuild
the catalog from scratch each time. Is that correct?
"John Kane" wrote:

> Carl,
> Unfortunately SQL Server 2000 FT Catalogs are not directly supported with
> Replication. The one-time snapshot of your table does not re-create the FT
> Catalog parameter for the FT-enable table and once the table is created on
> your subscriber, you will need to manually re-create the FT Catalog and set
> Change Tracking with Update Index in Background and this will run a Full
> Population (if the FT Catalog is un-populated).
> Could you provide more details on how your subscribers tables are setup?
> Thanks,
> John
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
> with
> can
>
>
|||Carl,
No, it's the initial snapshot that is the problem as when the table schema
is created the on the subscribers, the FT Catalogs parameters (FT Catalog
name, FT-enabled columns) are not created and Change Tracking and Update
Index in background is not defined. Assuming that you do the snapshot only
once you will have to re-create this metadata only once, but when ever you
schedule another snapshot, you will have to re-do the FT Catalog metadata.
This may be the source of why your FT Catalogs are disappearing!
Furthermore, depending upon the amount (number of rows) and frequency
(batch/second or batch/hour), you should be able to use "Change Tracking"
and "Update Index in Background" on the Push Subscribers.
Regards,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:ECE0559F-C45A-4F14-A84F-9F5753BD1B1F@.microsoft.com...
> I am using transactional replication to push the changes from my publisher
to
> the subscribers. Here is the schema for one subscriber/publisher table:
> CREATE TABLE defdba (
> UID bigint NOT NULL CONSTRAINT MSrepl_synctran_identity_default_1525580473
> DEFAULT (0),
> NAME varchar(256) NULL ,
> DBAID int NULL ,
> JUDGEMENTID int NULL ,
> MERLINNAME varchar(300) NULL ,
> ADDDATE varchar(8) NOT NULL ,
> msrepl_tran_version uniqueidentifier NOT NULL CONSTRAINT
> DF__defdba__msrepl_t__5C37ACAD DEFAULT (newid()),
> CONSTRAINT PK_defdba PRIMARY KEY CLUSTERED
> (
> UID
> ) ON PRIMARY
> )
> I had set up the FTI to check for changes in the background, and then
update
> itself when replication push the data changes accross to the subscriber.
From
> what I am hearing you say, after I replicate the changes over, I must
rebuild[vbcol=seagreen]
> the catalog from scratch each time. Is that correct?
> "John Kane" wrote:
with[vbcol=seagreen]
FT[vbcol=seagreen]
on[vbcol=seagreen]
set[vbcol=seagreen]
message[vbcol=seagreen]
each[vbcol=seagreen]
one[vbcol=seagreen]
QA[vbcol=seagreen]
catalogue[vbcol=seagreen]
and[vbcol=seagreen]
|||I think your problem is with the way replication modifies tables for
replication.
It looks like you are complaining about the dropping of the full text
indexing on your publisher, correct? I think this is a consequence of using
queued updating which does modify both the publisher and the subscriber
tables.
I am curious as to why you are using queued? You might be able to get away
with using pure bi-directional transactional replication, depending on
certain factors.
Can you tell me exactly what you are trying to do, where do most of your
updates happen and how many subscribers you have? Also have you implemented
any partitioning scheme to avoid conflicts?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
>I have one publisher that pushes out a database to two subscribers, each
>with
> Win 2003 server and SQL 2000 sp3. This Db has two tables who each have one
> column full text indexed. The publisher is full text indexed so that QA
> can
> test the data before replication happens.
> my problem is that after the replication finishes sync'ing up the
> subscribers, My tables are no longer full text enabled and my catalogue
> disappears.
> Can someone tell me how to avoid this? It takes about 5 ours to drop and
> rebuild the catalog.
> Thank you in advance!
|||Hilary, thank you for your help!!!
My publisher server is where I process incoming raw files to update my
delivery servers (subscribers). There are six tables, two of which have one
column (MerlinName) that are FTI-enabled. Each table has between 4-6 million
rows in them.
I have Transactional Replication running once a week (The Publisher is only
updated 1-3 times a week.), and I rebuild the catalog three hours later in a
sql agent job. I rebuild that catalog each time because that only takes about
5 hours, where incremental rebuild can take up to 12 hours. I can run the sql
agent job seperately and it works fine.
Here is the sql agent job code:
EXEC sp_fulltext_catalog @.ftcat = 'db_cat', @.action = 'Rebuild'
EXEC sp_fulltext_catalog @.ftcat = 'db_cat', @.action = 'Start_Full'
When I come back the next day, the Subscribers data is sync'ed, but all FTI
configurations are gone. There is a catalog, but it is empty.
I dont understand how a transactional replication, which is just adding rows
to a table, can remove the FTI configurations.
"Hilary Cotter" wrote:

> I think your problem is with the way replication modifies tables for
> replication.
> It looks like you are complaining about the dropping of the full text
> indexing on your publisher, correct? I think this is a consequence of using
> queued updating which does modify both the publisher and the subscriber
> tables.
> I am curious as to why you are using queued? You might be able to get away
> with using pure bi-directional transactional replication, depending on
> certain factors.
> Can you tell me exactly what you are trying to do, where do most of your
> updates happen and how many subscribers you have? Also have you implemented
> any partitioning scheme to avoid conflicts?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
>
>
|||Were you able to find out anything about this?
"Hilary Cotter" wrote:

> I think your problem is with the way replication modifies tables for
> replication.
> It looks like you are complaining about the dropping of the full text
> indexing on your publisher, correct? I think this is a consequence of using
> queued updating which does modify both the publisher and the subscriber
> tables.
> I am curious as to why you are using queued? You might be able to get away
> with using pure bi-directional transactional replication, depending on
> certain factors.
> Can you tell me exactly what you are trying to do, where do most of your
> updates happen and how many subscribers you have? Also have you implemented
> any partitioning scheme to avoid conflicts?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
>
>
|||Carl,
As I said in my initial posting SQL Server 2000 FT Catalogs are not directly
supported with Replication. So, the replication of textual data & objects
(views, functions etc) containing full-text predicates such as CONTAINS* or
FREETEXT* during the initial snapshot is not supported. However, a
workaround is to manually create and maintain a post-snapshot script that
contains the FTS code as well as the referenced full-text indexes should be
placed in a post-snapshot script instead of being published as articles. You
can setup a SQLServerAgent job step to do this automatically and for some
T-SQL script examples , see KB article 240867 (Q240867) "INF: How to Move,
Copy, and Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867
Regards,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:2CC14822-F27F-44ED-9046-5C72714A8D79@.microsoft.com...[vbcol=seagreen]
> Were you able to find out anything about this?
> "Hilary Cotter" wrote:
using[vbcol=seagreen]
away[vbcol=seagreen]
implemented[vbcol=seagreen]
message[vbcol=seagreen]
each[vbcol=seagreen]
one[vbcol=seagreen]
QA[vbcol=seagreen]
catalogue[vbcol=seagreen]
and[vbcol=seagreen]

Help! [Microsoft][ODBC SQL Server Driver]Timeout expired

Hi all...
Environment
OS : Windows 2000 Advanced Server(SP4)
SQL Server : Microsoft SQL Server 2000(SP3)
DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
firewall
i have four databases, each size are 8G, 400M, 18G, 500M
there is a firewall between COM+ Server and SQL Server
so, i have configured port configuration for MSDTC as recommended by
Microsoft Support site
http://support.microsoft.com/kb/250367
almost for the past 1.5 years. my com+ application has worked 99.9999% fine
but from about a month ago i got this error at every particular NIGHT
time(ie. 22:00)
on particular database
[Microsoft][ODBC SQL Server Driver]Timeout expired
Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expired
MSG-->OR_20001_SN_ReceiptOrder
IDispatch error #3121 Error Count:1 GetLastError Number:0.
once this error occurrs, all other components which are using "transaction"
get the same error over and over and over again till i restart MSDTC service
or
SQL Server Database.
really strange thing is that this error NEVER occurrs at a daytime
i found the fact that sp starts and never completed after tracing by using
query profiler
is this related to network resource or table locking?
has anybody seen this case? or any ideas?
Hi
Sounds like blocking at table level. If network connection issues occur, DTC
notices it and aborts.
Regards
Mike
"Chan" wrote:

> Hi all...
> Environment
> OS : Windows 2000 Advanced Server(SP4)
> SQL Server : Microsoft SQL Server 2000(SP3)
> DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
> firewall
> i have four databases, each size are 8G, 400M, 18G, 500M
> there is a firewall between COM+ Server and SQL Server
> so, i have configured port configuration for MSDTC as recommended by
> Microsoft Support site
> http://support.microsoft.com/kb/250367
> almost for the past 1.5 years. my com+ application has worked 99.9999% fine
> but from about a month ago i got this error at every particular NIGHT
> time(ie. 22:00)
> on particular database
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expired
> MSG-->OR_20001_SN_ReceiptOrder
> IDispatch error #3121 Error Count:1 GetLastError Number:0.
> once this error occurrs, all other components which are using "transaction"
> get the same error over and over and over again till i restart MSDTC service
> or
> SQL Server Database.
> really strange thing is that this error NEVER occurrs at a daytime
> i found the fact that sp starts and never completed after tracing by using
> query profiler
> is this related to network resource or table locking?
> has anybody seen this case? or any ideas?
|||Hi Mike
i am a litlte bit newbie for mssql
just typed "'sp_lock" to see if there is a table level lock in query analyzer
and no table lock information found on the target database which causing
troubles
would you please let me know how you find lock information?
one more thing i did not mention
number of ports opened is 10, not microsofts' recommendation minimum of 15~20
any comments helps me
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Sounds like blocking at table level. If network connection issues occur, DTC
> notices it and aborts.
> Regards
> Mike
> "Chan" wrote:

Help! [Microsoft][ODBC SQL Server Driver]Timeout expired

Hi all...
Environment
OS : Windows 2000 Advanced Server(SP4)
SQL Server : Microsoft SQL Server 2000(SP3)
DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
firewall
i have four databases, each size are 8G, 400M, 18G, 500M
there is a firewall between COM+ Server and SQL Server
so, i have configured port configuration for MSDTC as recommended by
Microsoft Support site
http://support.microsoft.com/kb/250367
almost for the past 1.5 years. my com+ application has worked 99.9999% fine
but from about a month ago i got this error at every particular NIGHT
time(ie. 22:00)
on particular database
[Microsoft][ODBC SQL Server Driver]Timeout expired
Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expire
d
MSG-->OR_20001_SN_ReceiptOrder
IDispatch error #3121 Error Count:1 GetLastError Number:0.
once this error occurrs, all other components which are using "transaction"
get the same error over and over and over again till i restart MSDTC service
or
SQL Server Database.
really strange thing is that this error NEVER occurrs at a daytime
i found the fact that sp starts and never completed after tracing by using
query profiler
is this related to network resource or table locking?
has anybody seen this case? or any ideas?Chan,
You should probably call MS product support and let them take a look at
this. It's hard to determine what can cause these errors without some heavy
duty troubleshooting. It could be a leak in the provider or some other
MSDTC-related problem.
Adrian
"Chan" <Chan@.discussions.microsoft.com> wrote in message
news:E472A035-D0A2-49E1-AD2A-373FE573AB5E@.microsoft.com...
> Hi all...
> Environment
> OS : Windows 2000 Advanced Server(SP4)
> SQL Server : Microsoft SQL Server 2000(SP3)
> DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
> firewall
> i have four databases, each size are 8G, 400M, 18G, 500M
> there is a firewall between COM+ Server and SQL Server
> so, i have configured port configuration for MSDTC as recommended by
> Microsoft Support site
> http://support.microsoft.com/kb/250367
> almost for the past 1.5 years. my com+ application has worked 99.9999%
> fine
> but from about a month ago i got this error at every particular NIGHT
> time(ie. 22:00)
> on particular database
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expi
red
> MSG-->OR_20001_SN_ReceiptOrder
> IDispatch error #3121 Error Count:1 GetLastError Number:0.
> once this error occurrs, all other components which are using
> "transaction"
> get the same error over and over and over again till i restart MSDTC
> service
> or
> SQL Server Database.
> really strange thing is that this error NEVER occurrs at a daytime
> i found the fact that sp starts and never completed after tracing by using
> query profiler
> is this related to network resource or table locking?
> has anybody seen this case? or any ideas?

Help! [Microsoft][ODBC SQL Server Driver]Timeout expired

Hi all...
Environment
OS : Windows 2000 Advanced Server(SP4)
SQL Server : Microsoft SQL Server 2000(SP3)
DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
firewall
i have four databases, each size are 8G, 400M, 18G, 500M
there is a firewall between COM+ Server and SQL Server
so, i have configured port configuration for MSDTC as recommended by
Microsoft Support site
http://support.microsoft.com/kb/250367
almost for the past 1.5 years. my com+ application has worked 99.9999% fine
but from about a month ago i got this error at every particular NIGHT
time(ie. 22:00)
on particular database
[Microsoft][ODBC SQL Server Driver]Timeout expired
Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expired
MSG-->OR_20001_SN_ReceiptOrder
IDispatch error #3121 Error Count:1 GetLastError Number:0.
once this error occurrs, all other components which are using "transaction"
get the same error over and over and over again till i restart MSDTC service
or
SQL Server Database.
really strange thing is that this error NEVER occurrs at a daytime
i found the fact that sp starts and never completed after tracing by using
query profiler
is this related to network resource or table locking?
has anybody seen this case? or any ideas?Chan,
You should probably call MS product support and let them take a look at
this. It's hard to determine what can cause these errors without some heavy
duty troubleshooting. It could be a leak in the provider or some other
MSDTC-related problem.
Adrian
"Chan" <Chan@.discussions.microsoft.com> wrote in message
news:E472A035-D0A2-49E1-AD2A-373FE573AB5E@.microsoft.com...
> Hi all...
> Environment
> OS : Windows 2000 Advanced Server(SP4)
> SQL Server : Microsoft SQL Server 2000(SP3)
> DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
> firewall
> i have four databases, each size are 8G, 400M, 18G, 500M
> there is a firewall between COM+ Server and SQL Server
> so, i have configured port configuration for MSDTC as recommended by
> Microsoft Support site
> http://support.microsoft.com/kb/250367
> almost for the past 1.5 years. my com+ application has worked 99.9999%
> fine
> but from about a month ago i got this error at every particular NIGHT
> time(ie. 22:00)
> on particular database
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expired
> MSG-->OR_20001_SN_ReceiptOrder
> IDispatch error #3121 Error Count:1 GetLastError Number:0.
> once this error occurrs, all other components which are using
> "transaction"
> get the same error over and over and over again till i restart MSDTC
> service
> or
> SQL Server Database.
> really strange thing is that this error NEVER occurrs at a daytime
> i found the fact that sp starts and never completed after tracing by using
> query profiler
> is this related to network resource or table locking?
> has anybody seen this case? or any ideas?

Help! [Microsoft][ODBC SQL Server Driver]Timeout expired

Hi all...
Environment
OS : Windows 2000 Advanced Server(SP4)
SQL Server : Microsoft SQL Server 2000(SP3)
DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
firewall
i have four databases, each size are 8G, 400M, 18G, 500M
there is a firewall between COM+ Server and SQL Server
so, i have configured port configuration for MSDTC as recommended by
Microsoft Support site
http://support.microsoft.com/kb/250367
almost for the past 1.5 years. my com+ application has worked 99.9999% fine
but from about a month ago i got this error at every particular NIGHT
time(ie. 22:00)
on particular database
[Microsoft][ODBC SQL Server Driver]Timeout expired
Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expired
MSG-->OR_20001_SN_ReceiptOrder
IDispatch error #3121 Error Count:1 GetLastError Number:0.
once this error occurrs, all other components which are using "transaction"
get the same error over and over and over again till i restart MSDTC service
or
SQL Server Database.
really strange thing is that this error NEVER occurrs at a daytime
i found the fact that sp starts and never completed after tracing by using
query profiler
is this related to network resource or table locking?
has anybody seen this case? or any ideas?
Chan,
You should probably call MS product support and let them take a look at
this. It's hard to determine what can cause these errors without some heavy
duty troubleshooting. It could be a leak in the provider or some other
MSDTC-related problem.
Adrian
"Chan" <Chan@.discussions.microsoft.com> wrote in message
news:E472A035-D0A2-49E1-AD2A-373FE573AB5E@.microsoft.com...
> Hi all...
> Environment
> OS : Windows 2000 Advanced Server(SP4)
> SQL Server : Microsoft SQL Server 2000(SP3)
> DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
> firewall
> i have four databases, each size are 8G, 400M, 18G, 500M
> there is a firewall between COM+ Server and SQL Server
> so, i have configured port configuration for MSDTC as recommended by
> Microsoft Support site
> http://support.microsoft.com/kb/250367
> almost for the past 1.5 years. my com+ application has worked 99.9999%
> fine
> but from about a month ago i got this error at every particular NIGHT
> time(ie. 22:00)
> on particular database
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expired
> MSG-->OR_20001_SN_ReceiptOrder
> IDispatch error #3121 Error Count:1 GetLastError Number:0.
> once this error occurrs, all other components which are using
> "transaction"
> get the same error over and over and over again till i restart MSDTC
> service
> or
> SQL Server Database.
> really strange thing is that this error NEVER occurrs at a daytime
> i found the fact that sp starts and never completed after tracing by using
> query profiler
> is this related to network resource or table locking?
> has anybody seen this case? or any ideas?

Help! [Microsoft][ODBC SQL Server Driver]Timeout expired

Hi all...
Environment
OS : Windows 2000 Advanced Server(SP4)
SQL Server : Microsoft SQL Server 2000(SP3)
DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
firewall
i have four databases, each size are 8G, 400M, 18G, 500M
there is a firewall between COM+ Server and SQL Server
so, i have configured port configuration for MSDTC as recommended by
Microsoft Support site
http://support.microsoft.com/kb/250367
almost for the past 1.5 years. my com+ application has worked 99.9999% fine
but from about a month ago i got this error at every particular NIGHT
time(ie. 22:00)
on particular database
[Microsoft][ODBC SQL Server Driver]Timeout expired
Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expire
d
MSG-->OR_20001_SN_ReceiptOrder
IDispatch error #3121 Error Count:1 GetLastError Number:0.
once this error occurrs, all other components which are using "transaction"
get the same error over and over and over again till i restart MSDTC service
or
SQL Server Database.
really strange thing is that this error NEVER occurrs at a daytime
i found the fact that sp starts and never completed after tracing by using
query profiler
is this related to network resource or table locking?
has anybody seen this case? or any ideas?Hi
Sounds like blocking at table level. If network connection issues occur, DTC
notices it and aborts.
Regards
Mike
"Chan" wrote:

> Hi all...
> Environment
> OS : Windows 2000 Advanced Server(SP4)
> SQL Server : Microsoft SQL Server 2000(SP3)
> DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
> firewall
> i have four databases, each size are 8G, 400M, 18G, 500M
> there is a firewall between COM+ Server and SQL Server
> so, i have configured port configuration for MSDTC as recommended by
> Microsoft Support site
> http://support.microsoft.com/kb/250367
> almost for the past 1.5 years. my com+ application has worked 99.9999% fin
e
> but from about a month ago i got this error at every particular NIGHT
> time(ie. 22:00)
> on particular database
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expi
red
> MSG-->OR_20001_SN_ReceiptOrder
> IDispatch error #3121 Error Count:1 GetLastError Number:0.
> once this error occurrs, all other components which are using "transaction
"
> get the same error over and over and over again till i restart MSDTC servi
ce
> or
> SQL Server Database.
> really strange thing is that this error NEVER occurrs at a daytime
> i found the fact that sp starts and never completed after tracing by using
> query profiler
> is this related to network resource or table locking?
> has anybody seen this case? or any ideas?|||Hi Mike
i am a litlte bit newbie for mssql
just typed "'sp_lock" to see if there is a table level lock in query analyze
r
and no table lock information found on the target database which causing
troubles
would you please let me know how you find lock information?
one more thing i did not mention
number of ports opened is 10, not microsofts' recommendation minimum of 15~2
0
any comments helps me
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Sounds like blocking at table level. If network connection issues occur, D
TC
> notices it and aborts.
> Regards
> Mike
> "Chan" wrote:
>

Help!

Help,
i have a nt server sp6 with sql sp3 installed and configured by someone
else. in recent weeks our dc/exchange server had a similar problem in where
i had to do a reinstall from scratch and restore from backup.
because of user accounts being recreated i had to go and add the users again
on the sql server.
i decided to reboot the server after completing the task of adding the
users. then the fun began... the server will past the post, no problem, then
when the winnt server logo would appear on the screen with the mouse
cursor...wham the server started to reboot in an endless loop...right at the
same spot, the winnt logo screen and mouse cursor being displayed on the
screen.
I figured i post this here since this happened right after adding the users
in sql.
it looks like a virus at play but none was detected before the server was
rebooted.
has anybody seen this happen before?
can this configuration be saved ?
any help would be greatly appreciated.
thanks
ronaldHi ,
Ask your system administrater to try out this,
I am merely guessing, try this:
get to system using Console Repair Mode and then change
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Wi
ndowsNT\CurrentVersion\Winlogon\Auto
AdminLogon to 0
also if that doesn't help, try to go to your %temp% folder and delete
sit*.tmp files, which the Readme says are used to store the passwords.
Thanks
Hari
MCDBA
"ronald" <ronald_padilla@.msn.com> wrote in message
news:ufR2qMIEEHA.3408@.tk2msftngp13.phx.gbl...
> Help,
> i have a nt server sp6 with sql sp3 installed and configured by someone
> else. in recent weeks our dc/exchange server had a similar problem in
where
> i had to do a reinstall from scratch and restore from backup.
> because of user accounts being recreated i had to go and add the users
again
> on the sql server.
> i decided to reboot the server after completing the task of adding the
> users. then the fun began... the server will past the post, no problem,
then
> when the winnt server logo would appear on the screen with the mouse
> cursor...wham the server started to reboot in an endless loop...right at
the
> same spot, the winnt logo screen and mouse cursor being displayed on the
> screen.
> I figured i post this here since this happened right after adding the
users
> in sql.
> it looks like a virus at play but none was detected before the server was
> rebooted.
> has anybody seen this happen before?
> can this configuration be saved ?
> any help would be greatly appreciated.
> thanks
> ronald
>|||thanks guys.
the server was configured with raid 5 and getting to the registry was a
problem. i needed a way to get to the sql data. i used a independant "hd"
from the raid 5 and was able to back up the data to then restore sql .
again thanks
ronald
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23AOgoRJEEHA.3016@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> Ask your system administrater to try out this,
> I am merely guessing, try this:
> get to system using Console Repair Mode and then change
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Wi
ndowsNT\CurrentVersion\Winlogon\Auto[col
or=darkred]
> AdminLogon to 0
> also if that doesn't help, try to go to your %temp% folder and delete
> sit*.tmp files, which the Readme says are used to store the passwords.
> Thanks
> Hari
> MCDBA
>
> "ronald" <ronald_padilla@.msn.com> wrote in message
> news:ufR2qMIEEHA.3408@.tk2msftngp13.phx.gbl...
> where
> again
> then
> the
> users
was
>

help!

sql2k sp3
I accidentally started restoreing the wrong db. I
cancelled it and now its in a load state. Can I just put
it back!!!>--Original Message--
>sql2k sp3
>I accidentally started restoreing the wrong db. I
>cancelled it and now its in a load state. Can I just put
>it back!!!
>.
>|||Your only recourse to restore from your latest database backup.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:43fa01c47ff4$26cc0870$a301280a@.phx.gbl...
> sql2k sp3
> I accidentally started restoreing the wrong db. I
> cancelled it and now its in a load state. Can I just put
> it back!!!

Help with using Alias Fields in a expressions.

Using MSDE...(8.00.760 SP3)
I have the following query...I taking this from ms-access, but lets just
stick to using the EM here...
SELECT ProductName, SerialNumber, UnitPrice,
(SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID) AS
TotalAddStock,
(SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID) AS
TotalSoldStock
FROM dbo.Products
The problem is that I want to use the alias fields in a expression, and add
one more column to the above like:
(TotalAddStock - TotalSoldStock) as InStock.
In JET sql, I can simple use the above expression, and add it to the above
sql (ie: using those alias fields in expressions in the sql is legal).
However, In the EM, when I try to add the above expression...it says that
TotalAddStock is a not a valid field.
Is there some rule for using Alias fields?
Do I have to repeat the actual sql for those two sub-queries all over again
to make the expression of
( ("sub query repeated again") - ("sub query
repeated again") ) as InStock.
I have noticed that if I save the query (create a view),then I can certainly
write the following:
select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from myCoolView
However, this means I now have two sql views in place of what was one
before. It would be handy if I could throw in that one extra column.
Is creating two views here the recommend approach here? Any enlightenment on
using Alias fields in other column expressions is appreciated.
My best guess seems that you can't use a sub-query as a alias field in other
expressions...is this right?
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn
Albert,
Try nesting the statement this way:
select
ProductName,
SerialNumber,
UnitPrice,
TotalAddStock,
TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from (
SELECT
ProductName,
SerialNumber,
UnitPrice,
(
SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID
) AS TotalAddStock,
(
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID
) AS TotalSoldStock
FROM dbo.Products
) T
This accomplishes what you discovered worked with a view - here you
would be using a derived table, which is basically an ad-hoc view.
Steve Kass
Drew University
Albert D. Kallal wrote:

>Using MSDE...(8.00.760 SP3)
>I have the following query...I taking this from ms-access, but lets just
>stick to using the EM here...
>SELECT ProductName, SerialNumber, UnitPrice,
> (SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID) AS
>TotalAddStock,
> (SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID) AS
>TotalSoldStock
>FROM dbo.Products
>The problem is that I want to use the alias fields in a expression, and add
>one more column to the above like:
> (TotalAddStock - TotalSoldStock) as InStock.
>In JET sql, I can simple use the above expression, and add it to the above
>sql (ie: using those alias fields in expressions in the sql is legal).
>However, In the EM, when I try to add the above expression...it says that
>TotalAddStock is a not a valid field.
>Is there some rule for using Alias fields?
>Do I have to repeat the actual sql for those two sub-queries all over again
>to make the expression of
> ( ("sub query repeated again") - ("sub query
>repeated again") ) as InStock.
>I have noticed that if I save the query (create a view),then I can certainly
>write the following:
>select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from myCoolView
>However, this means I now have two sql views in place of what was one
>before. It would be handy if I could throw in that one extra column.
>Is creating two views here the recommend approach here? Any enlightenment on
>using Alias fields in other column expressions is appreciated.
>My best guess seems that you can't use a sub-query as a alias field in other
>expressions...is this right?
>
>
|||Both of these will, in all likelihood, force correlated subqueries--highly
inefficient. I would check the execution plans genereated to make sure.
Here's an alternative:
SELECT p.ProductName
,p.SerialNumber
,p.UnitPrice
,tas.TotalAddStock
,tss.TotalSoldStock
,InStock = (tas.TotalAddStock - tss.TotalSoldStock)
FROM dbo.Products AS p
INNER JOIN
(SELECT ProductID
,TotalAddStock = SUM(UnitsAdd)
FROM dbo.Inventory
GROUP BY ProductID
) AS tas
ON p.ProductID = tas.ProductID
(SELECT ProductID
,TotalSoldStock = SUM(Quantity)
FROM dbo.OrderDetails
GROUP BY ProductID
) AS tss
ON p.ProductID = tss.ProductID
Now, this too could be expensive in that the Inventory and/or OrderDetails
tables could be very large and creating the aggregates on all Products before
the JOIN restrictions could be CPU wasteful.
However, in this case, it looks like you already have an implicit RI
relationship between the Products, Inventory, and Order Details tables and,
with no restrictions on the final results, would have to calculate all those
aggregates anyway.
So, in this case, as long as the Optimizer would have choosen Lazy Spooling
for the first two examples, the solution provided here, a Set-Based example,
would be orders of magnitude faster.
Sincerely,
Anthony Thomas
"Steve Kass" wrote:

> Albert,
> Try nesting the statement this way:
> select
> ProductName,
> SerialNumber,
> UnitPrice,
> TotalAddStock,
> TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from (
> SELECT
> ProductName,
> SerialNumber,
> UnitPrice,
> (
> SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID
> ) AS TotalAddStock,
> (
> SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID
> ) AS TotalSoldStock
> FROM dbo.Products
> ) T
> This accomplishes what you discovered worked with a view - here you
> would be using a derived table, which is basically an ad-hoc view.
> Steve Kass
> Drew University
> Albert D. Kallal wrote:
>
|||Interesting...but it seems that the query is optimized quite well. (I just
threw it into the Query analyzer).
It runs is less then one second for 125,000 invoice details and 2500
inventory records (stock additions), and 500 products.
Interesting, it also runs Steve's example in the same time..and thus the
query optimizer seems quite smart in this case. (that to me is rather
amazing...since I do have reasonable grasp of sql...and I can't believe
that sql server returns such good times for the kind of problems that don't
look at all that you can resolve those values together).
As for the joins,
I did have to put in a "left" join..since you were joining more then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
Anyway, I am surprised the engine does such a good job, considering you are
using a "group by" here..and that is joined to the "main" table. Often, jet
don't do such a good job in these cases. I should note that using the
sub-quires like I did does run very well in JET, and also sql server.
Anyway...thanks Anthony and Steve...
I am now temped to simply use a view here as per my original idea (I can
more easily "think" of my original syntax). However, I do "get" what you did
with the "group by"...and the fact that sql server seems to be more
forgiving for complex sql then is JET.
I certainly appreciate your answers, and in fact the two answers being so
different solutions also opens my mind up here...
Great stuff guys...and thanks!
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn
|||Glad I could help but you made a comment I'd like to follow up with, if I may.
You said, "I did have to put in a "left" join..since you were joining more
then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
"
The number of tables joined has nothing to do with the join types. They
were coded as INNER JOINS so that you'd only get matching records; that is,
records with ProductID's that existed in each of the joined tables. If you
use LEFT JOINs, you will get Products that do not have corresponding
Inventory or OrderDetail records, but then the values your are computing,
TotalAdd and TotalSold will be NULL for those record types.
The INNER JOINS will use the Primary Keys defined on the child and parent
tables when performing the join operation and will likely use a Cluster Index
Scan if you use the LEFT JOIN systax. Thus, it is more likely that the INNER
JOIN will give you better performance, especially if you want only the
records that will actually compute a value instead returning NULL results.
Also, I'm curious what the execution plan and run time were if you run the
snippet as I originally wrote it. I'm curious if the Optimizer was able to
resolve to the same execution plan. For this to be meaningful, you'll have
to use the multiple INNER JOINs because that was what the two original
queries where doing, in essence.
Thanks for helping me satisfy my curiosity.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:

> Interesting...but it seems that the query is optimized quite well. (I just
> threw it into the Query analyzer).
> It runs is less then one second for 125,000 invoice details and 2500
> inventory records (stock additions), and 500 products.
> Interesting, it also runs Steve's example in the same time..and thus the
> query optimizer seems quite smart in this case. (that to me is rather
> amazing...since I do have reasonable grasp of sql...and I can't believe
> that sql server returns such good times for the kind of problems that don't
> look at all that you can resolve those values together).
> As for the joins,
> I did have to put in a "left" join..since you were joining more then one
> table here. So, I just changed the joins to:
> LEFT OUTER JOIN
> Anyway, I am surprised the engine does such a good job, considering you are
> using a "group by" here..and that is joined to the "main" table. Often, jet
> don't do such a good job in these cases. I should note that using the
> sub-quires like I did does run very well in JET, and also sql server.
> Anyway...thanks Anthony and Steve...
> I am now temped to simply use a view here as per my original idea (I can
> more easily "think" of my original syntax). However, I do "get" what you did
> with the "group by"...and the fact that sql server seems to be more
> forgiving for complex sql then is JET.
> I certainly appreciate your answers, and in fact the two answers being so
> different solutions also opens my mind up here...
> Great stuff guys...and thanks!
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>
|||Ah,...yes...inner join does work!
(and...yes...some products might not have any invoice sold...so, likely I
will stick with left joins).
Anyway..here is the query plans for both:
http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn
|||Glad I could help. Thanks for a peek at the EEP. You'll notice that the
Optimizer picked the same execution steps just changed the order of the joins.
It is concerning that that you are running Table Scans. This happens
because we are not limiting the results but aggregating all records. The
point is, you will have to touch every record. The fact you are doing Table
Scans indicates you have not defined a Clustered Index for any of these three
tables.
Every Table should have a Primary Key defined, for integrity and you are not
even in 1NF if you haven't defined a key, which means you do not have a
relational table. Bad mojo. Second, every Table should have a Clustered
Index defined. The reason being, this defines the actual data sort for
storage. Usually, the Primary Key and the Clustered Index are NOT the same,
at least with how many use them.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:

> Ah,...yes...inner join does work!
> (and...yes...some products might not have any invoice sold...so, likely I
> will stick with left joins).
> Anyway..here is the query plans for both:
> http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>

Help with using Alias Fields in a expressions.

Using MSDE...(8.00.760 SP3)
I have the following query...I taking this from ms-access, but lets just
stick to using the EM here...
SELECT ProductName, SerialNumber, UnitPrice,
(SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID) AS
TotalAddStock,
(SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID) AS
TotalSoldStock
FROM dbo.Products
The problem is that I want to use the alias fields in a expression, and add
one more column to the above like:
(TotalAddStock - TotalSoldStock) as InStock.
In JET sql, I can simple use the above expression, and add it to the above
sql (ie: using those alias fields in expressions in the sql is legal).
However, In the EM, when I try to add the above expression...it says that
TotalAddStock is a not a valid field.
Is there some rule for using Alias fields?
Do I have to repeat the actual sql for those two sub-queries all over again
to make the expression of
( ("sub query repeated again") - ("sub query
repeated again") ) as InStock.
I have noticed that if I save the query (create a view),then I can certainly
write the following:
select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from myCoolView
However, this means I now have two sql views in place of what was one
before. It would be handy if I could throw in that one extra column.
Is creating two views here the recommend approach here? Any enlightenment on
using Alias fields in other column expressions is appreciated.
My best guess seems that you can't use a sub-query as a alias field in other
expressions...is this right?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msnAlbert,
Try nesting the statement this way:
select
ProductName,
SerialNumber,
UnitPrice,
TotalAddStock,
TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from (
SELECT
ProductName,
SerialNumber,
UnitPrice,
(
SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID
) AS TotalAddStock,
(
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID
) AS TotalSoldStock
FROM dbo.Products
) T
This accomplishes what you discovered worked with a view - here you
would be using a derived table, which is basically an ad-hoc view.
Steve Kass
Drew University
Albert D. Kallal wrote:
>Using MSDE...(8.00.760 SP3)
>I have the following query...I taking this from ms-access, but lets just
>stick to using the EM here...
>SELECT ProductName, SerialNumber, UnitPrice,
> (SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID) AS
>TotalAddStock,
> (SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID) AS
>TotalSoldStock
>FROM dbo.Products
>The problem is that I want to use the alias fields in a expression, and add
>one more column to the above like:
> (TotalAddStock - TotalSoldStock) as InStock.
>In JET sql, I can simple use the above expression, and add it to the above
>sql (ie: using those alias fields in expressions in the sql is legal).
>However, In the EM, when I try to add the above expression...it says that
>TotalAddStock is a not a valid field.
>Is there some rule for using Alias fields?
>Do I have to repeat the actual sql for those two sub-queries all over again
>to make the expression of
> ( ("sub query repeated again") - ("sub query
>repeated again") ) as InStock.
>I have noticed that if I save the query (create a view),then I can certainly
>write the following:
>select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from myCoolView
>However, this means I now have two sql views in place of what was one
>before. It would be handy if I could throw in that one extra column.
>Is creating two views here the recommend approach here? Any enlightenment on
>using Alias fields in other column expressions is appreciated.
>My best guess seems that you can't use a sub-query as a alias field in other
>expressions...is this right?
>
>|||Both of these will, in all likelihood, force correlated subqueries--highly
inefficient. I would check the execution plans genereated to make sure.
Here's an alternative:
SELECT p.ProductName
,p.SerialNumber
,p.UnitPrice
,tas.TotalAddStock
,tss.TotalSoldStock
,InStock = (tas.TotalAddStock - tss.TotalSoldStock)
FROM dbo.Products AS p
INNER JOIN
(SELECT ProductID
,TotalAddStock = SUM(UnitsAdd)
FROM dbo.Inventory
GROUP BY ProductID
) AS tas
ON p.ProductID = tas.ProductID
(SELECT ProductID
,TotalSoldStock = SUM(Quantity)
FROM dbo.OrderDetails
GROUP BY ProductID
) AS tss
ON p.ProductID = tss.ProductID
Now, this too could be expensive in that the Inventory and/or OrderDetails
tables could be very large and creating the aggregates on all Products before
the JOIN restrictions could be CPU wasteful.
However, in this case, it looks like you already have an implicit RI
relationship between the Products, Inventory, and Order Details tables and,
with no restrictions on the final results, would have to calculate all those
aggregates anyway.
So, in this case, as long as the Optimizer would have choosen Lazy Spooling
for the first two examples, the solution provided here, a Set-Based example,
would be orders of magnitude faster.
Sincerely,
Anthony Thomas
"Steve Kass" wrote:
> Albert,
> Try nesting the statement this way:
> select
> ProductName,
> SerialNumber,
> UnitPrice,
> TotalAddStock,
> TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from (
> SELECT
> ProductName,
> SerialNumber,
> UnitPrice,
> (
> SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID
> ) AS TotalAddStock,
> (
> SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID
> ) AS TotalSoldStock
> FROM dbo.Products
> ) T
> This accomplishes what you discovered worked with a view - here you
> would be using a derived table, which is basically an ad-hoc view.
> Steve Kass
> Drew University
> Albert D. Kallal wrote:
> >Using MSDE...(8.00.760 SP3)
> >
> >I have the following query...I taking this from ms-access, but lets just
> >stick to using the EM here...
> >
> >SELECT ProductName, SerialNumber, UnitPrice,
> > (SELECT SUM(UnitsAdd)
> > FROM inventory
> > WHERE ProductID = Products.ProductID) AS
> >TotalAddStock,
> > (SELECT SUM(Quantity)
> > FROM OrderDetails
> > WHERE ProductID = Products.ProductID) AS
> >TotalSoldStock
> >FROM dbo.Products
> >
> >The problem is that I want to use the alias fields in a expression, and add
> >one more column to the above like:
> >
> > (TotalAddStock - TotalSoldStock) as InStock.
> >
> >In JET sql, I can simple use the above expression, and add it to the above
> >sql (ie: using those alias fields in expressions in the sql is legal).
> >
> >However, In the EM, when I try to add the above expression...it says that
> >TotalAddStock is a not a valid field.
> >
> >Is there some rule for using Alias fields?
> >
> >Do I have to repeat the actual sql for those two sub-queries all over again
> >to make the expression of
> >
> > ( ("sub query repeated again") - ("sub query
> >repeated again") ) as InStock.
> >
> >I have noticed that if I save the query (create a view),then I can certainly
> >write the following:
> >
> >select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
> > (TotalAddStock - TotalSoldStock) as InStock
> > from myCoolView
> >
> >However, this means I now have two sql views in place of what was one
> >before. It would be handy if I could throw in that one extra column.
> >
> >Is creating two views here the recommend approach here? Any enlightenment on
> >using Alias fields in other column expressions is appreciated.
> >
> >My best guess seems that you can't use a sub-query as a alias field in other
> >expressions...is this right?
> >
> >
> >
>|||Interesting...but it seems that the query is optimized quite well. (I just
threw it into the Query analyzer).
It runs is less then one second for 125,000 invoice details and 2500
inventory records (stock additions), and 500 products.
Interesting, it also runs Steve's example in the same time..and thus the
query optimizer seems quite smart in this case. (that to me is rather
amazing...since I do have reasonable grasp of sql...and I can't believe
that sql server returns such good times for the kind of problems that don't
look at all that you can resolve those values together).
As for the joins,
I did have to put in a "left" join..since you were joining more then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
Anyway, I am surprised the engine does such a good job, considering you are
using a "group by" here..and that is joined to the "main" table. Often, jet
don't do such a good job in these cases. I should note that using the
sub-quires like I did does run very well in JET, and also sql server.
Anyway...thanks Anthony and Steve...
I am now temped to simply use a view here as per my original idea (I can
more easily "think" of my original syntax). However, I do "get" what you did
with the "group by"...and the fact that sql server seems to be more
forgiving for complex sql then is JET.
I certainly appreciate your answers, and in fact the two answers being so
different solutions also opens my mind up here...
Great stuff guys...and thanks!
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn|||Glad I could help but you made a comment I'd like to follow up with, if I may.
You said, "I did have to put in a "left" join..since you were joining more
then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
"
The number of tables joined has nothing to do with the join types. They
were coded as INNER JOINS so that you'd only get matching records; that is,
records with ProductID's that existed in each of the joined tables. If you
use LEFT JOINs, you will get Products that do not have corresponding
Inventory or OrderDetail records, but then the values your are computing,
TotalAdd and TotalSold will be NULL for those record types.
The INNER JOINS will use the Primary Keys defined on the child and parent
tables when performing the join operation and will likely use a Cluster Index
Scan if you use the LEFT JOIN systax. Thus, it is more likely that the INNER
JOIN will give you better performance, especially if you want only the
records that will actually compute a value instead returning NULL results.
Also, I'm curious what the execution plan and run time were if you run the
snippet as I originally wrote it. I'm curious if the Optimizer was able to
resolve to the same execution plan. For this to be meaningful, you'll have
to use the multiple INNER JOINs because that was what the two original
queries where doing, in essence.
Thanks for helping me satisfy my curiosity.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:
> Interesting...but it seems that the query is optimized quite well. (I just
> threw it into the Query analyzer).
> It runs is less then one second for 125,000 invoice details and 2500
> inventory records (stock additions), and 500 products.
> Interesting, it also runs Steve's example in the same time..and thus the
> query optimizer seems quite smart in this case. (that to me is rather
> amazing...since I do have reasonable grasp of sql...and I can't believe
> that sql server returns such good times for the kind of problems that don't
> look at all that you can resolve those values together).
> As for the joins,
> I did have to put in a "left" join..since you were joining more then one
> table here. So, I just changed the joins to:
> LEFT OUTER JOIN
> Anyway, I am surprised the engine does such a good job, considering you are
> using a "group by" here..and that is joined to the "main" table. Often, jet
> don't do such a good job in these cases. I should note that using the
> sub-quires like I did does run very well in JET, and also sql server.
> Anyway...thanks Anthony and Steve...
> I am now temped to simply use a view here as per my original idea (I can
> more easily "think" of my original syntax). However, I do "get" what you did
> with the "group by"...and the fact that sql server seems to be more
> forgiving for complex sql then is JET.
> I certainly appreciate your answers, and in fact the two answers being so
> different solutions also opens my mind up here...
> Great stuff guys...and thanks!
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>|||Ah,...yes...inner join does work!
(and...yes...some products might not have any invoice sold...so, likely I
will stick with left joins).
Anyway..here is the query plans for both:
http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn|||Glad I could help. Thanks for a peek at the EEP. You'll notice that the
Optimizer picked the same execution steps just changed the order of the joins.
It is concerning that that you are running Table Scans. This happens
because we are not limiting the results but aggregating all records. The
point is, you will have to touch every record. The fact you are doing Table
Scans indicates you have not defined a Clustered Index for any of these three
tables.
Every Table should have a Primary Key defined, for integrity and you are not
even in 1NF if you haven't defined a key, which means you do not have a
relational table. Bad mojo. Second, every Table should have a Clustered
Index defined. The reason being, this defines the actual data sort for
storage. Usually, the Primary Key and the Clustered Index are NOT the same,
at least with how many use them.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:
> Ah,...yes...inner join does work!
> (and...yes...some products might not have any invoice sold...so, likely I
> will stick with left joins).
> Anyway..here is the query plans for both:
> http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>

Help with using Alias Fields in a expressions.

Using MSDE...(8.00.760 SP3)
I have the following query...I taking this from ms-access, but lets just
stick to using the EM here...
SELECT ProductName, SerialNumber, UnitPrice,
(SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID) AS
TotalAddStock,
(SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID) AS
TotalSoldStock
FROM dbo.Products
The problem is that I want to use the alias fields in a expression, and add
one more column to the above like:
(TotalAddStock - TotalSoldStock) as InStock.
In JET sql, I can simple use the above expression, and add it to the above
sql (ie: using those alias fields in expressions in the sql is legal).
However, In the EM, when I try to add the above expression...it says that
TotalAddStock is a not a valid field.
Is there some rule for using Alias fields?
Do I have to repeat the actual sql for those two sub-queries all over again
to make the expression of
( ("sub query repeated again") - ("sub query
repeated again") ) as InStock.
I have noticed that if I save the query (create a view),then I can certainly
write the following:
select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from myCoolView
However, this means I now have two sql views in place of what was one
before. It would be handy if I could throw in that one extra column.
Is creating two views here the recommend approach here? Any enlightenment on
using Alias fields in other column expressions is appreciated.
My best guess seems that you can't use a sub-query as a alias field in other
expressions...is this right?
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msnAlbert,
Try nesting the statement this way:
select
ProductName,
SerialNumber,
UnitPrice,
TotalAddStock,
TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from (
SELECT
ProductName,
SerialNumber,
UnitPrice,
(
SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID
) AS TotalAddStock,
(
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID
) AS TotalSoldStock
FROM dbo.Products
) T
This accomplishes what you discovered worked with a view - here you
would be using a derived table, which is basically an ad-hoc view.
Steve Kass
Drew University
Albert D. Kallal wrote:

>Using MSDE...(8.00.760 SP3)
>I have the following query...I taking this from ms-access, but lets just
>stick to using the EM here...
>SELECT ProductName, SerialNumber, UnitPrice,
> (SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID) AS
>TotalAddStock,
> (SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID) AS
>TotalSoldStock
>FROM dbo.Products
>The problem is that I want to use the alias fields in a expression, and add
>one more column to the above like:
> (TotalAddStock - TotalSoldStock) as InStock.
>In JET sql, I can simple use the above expression, and add it to the above
>sql (ie: using those alias fields in expressions in the sql is legal).
>However, In the EM, when I try to add the above expression...it says that
>TotalAddStock is a not a valid field.
>Is there some rule for using Alias fields?
>Do I have to repeat the actual sql for those two sub-queries all over again
>to make the expression of
> ( ("sub query repeated again") - ("sub query
>repeated again") ) as InStock.
>I have noticed that if I save the query (create a view),then I can certainl
y
>write the following:
>select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from myCoolView
>However, this means I now have two sql views in place of what was one
>before. It would be handy if I could throw in that one extra column.
>Is creating two views here the recommend approach here? Any enlightenment o
n
>using Alias fields in other column expressions is appreciated.
>My best guess seems that you can't use a sub-query as a alias field in othe
r
>expressions...is this right?
>
>|||Both of these will, in all likelihood, force correlated subqueries--highly
inefficient. I would check the execution plans genereated to make sure.
Here's an alternative:
SELECT p.ProductName
,p.SerialNumber
,p.UnitPrice
,tas.TotalAddStock
,tss.TotalSoldStock
,InStock = (tas.TotalAddStock - tss.TotalSoldStock)
FROM dbo.Products AS p
INNER JOIN
(SELECT ProductID
,TotalAddStock = SUM(UnitsAdd)
FROM dbo.Inventory
GROUP BY ProductID
) AS tas
ON p.ProductID = tas.ProductID
(SELECT ProductID
,TotalSoldStock = SUM(Quantity)
FROM dbo.OrderDetails
GROUP BY ProductID
) AS tss
ON p.ProductID = tss.ProductID
Now, this too could be expensive in that the Inventory and/or OrderDetails
tables could be very large and creating the aggregates on all Products befor
e
the JOIN restrictions could be CPU wasteful.
However, in this case, it looks like you already have an implicit RI
relationship between the Products, Inventory, and Order Details tables and,
with no restrictions on the final results, would have to calculate all those
aggregates anyway.
So, in this case, as long as the Optimizer would have choosen Lazy Spooling
for the first two examples, the solution provided here, a Set-Based example,
would be orders of magnitude faster.
Sincerely,
Anthony Thomas
"Steve Kass" wrote:

> Albert,
> Try nesting the statement this way:
> select
> ProductName,
> SerialNumber,
> UnitPrice,
> TotalAddStock,
> TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from (
> SELECT
> ProductName,
> SerialNumber,
> UnitPrice,
> (
> SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID
> ) AS TotalAddStock,
> (
> SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID
> ) AS TotalSoldStock
> FROM dbo.Products
> ) T
> This accomplishes what you discovered worked with a view - here you
> would be using a derived table, which is basically an ad-hoc view.
> Steve Kass
> Drew University
> Albert D. Kallal wrote:
>
>|||Interesting...but it seems that the query is optimized quite well. (I just
threw it into the Query analyzer).
It runs is less then one second for 125,000 invoice details and 2500
inventory records (stock additions), and 500 products.
Interesting, it also runs Steve's example in the same time..and thus the
query optimizer seems quite smart in this case. (that to me is rather
amazing...since I do have reasonable grasp of sql...and I can't believe
that sql server returns such good times for the kind of problems that don't
look at all that you can resolve those values together).
As for the joins,
I did have to put in a "left" join..since you were joining more then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
Anyway, I am surprised the engine does such a good job, considering you are
using a "group by" here..and that is joined to the "main" table. Often, jet
don't do such a good job in these cases. I should note that using the
sub-quires like I did does run very well in JET, and also sql server.
Anyway...thanks Anthony and Steve...
I am now temped to simply use a view here as per my original idea (I can
more easily "think" of my original syntax). However, I do "get" what you did
with the "group by"...and the fact that sql server seems to be more
forgiving for complex sql then is JET.
I certainly appreciate your answers, and in fact the two answers being so
different solutions also opens my mind up here...
Great stuff guys...and thanks!
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn|||Glad I could help but you made a comment I'd like to follow up with, if I ma
y.
You said, "I did have to put in a "left" join..since you were joining more
then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
"
The number of tables joined has nothing to do with the join types. They
were coded as INNER JOINS so that you'd only get matching records; that is,
records with ProductID's that existed in each of the joined tables. If you
use LEFT JOINs, you will get Products that do not have corresponding
Inventory or OrderDetail records, but then the values your are computing,
TotalAdd and TotalSold will be NULL for those record types.
The INNER JOINS will use the Primary Keys defined on the child and parent
tables when performing the join operation and will likely use a Cluster Inde
x
Scan if you use the LEFT JOIN systax. Thus, it is more likely that the INNE
R
JOIN will give you better performance, especially if you want only the
records that will actually compute a value instead returning NULL results.
Also, I'm curious what the execution plan and run time were if you run the
snippet as I originally wrote it. I'm curious if the Optimizer was able to
resolve to the same execution plan. For this to be meaningful, you'll have
to use the multiple INNER JOINs because that was what the two original
queries where doing, in essence.
Thanks for helping me satisfy my curiosity.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:

> Interesting...but it seems that the query is optimized quite well. (I just
> threw it into the Query analyzer).
> It runs is less then one second for 125,000 invoice details and 2500
> inventory records (stock additions), and 500 products.
> Interesting, it also runs Steve's example in the same time..and thus the
> query optimizer seems quite smart in this case. (that to me is rather
> amazing...since I do have reasonable grasp of sql...and I can't believe
> that sql server returns such good times for the kind of problems that don'
t
> look at all that you can resolve those values together).
> As for the joins,
> I did have to put in a "left" join..since you were joining more then one
> table here. So, I just changed the joins to:
> LEFT OUTER JOIN
> Anyway, I am surprised the engine does such a good job, considering you ar
e
> using a "group by" here..and that is joined to the "main" table. Often, je
t
> don't do such a good job in these cases. I should note that using the
> sub-quires like I did does run very well in JET, and also sql server.
> Anyway...thanks Anthony and Steve...
> I am now temped to simply use a view here as per my original idea (I can
> more easily "think" of my original syntax). However, I do "get" what you d
id
> with the "group by"...and the fact that sql server seems to be more
> forgiving for complex sql then is JET.
> I certainly appreciate your answers, and in fact the two answers being so
> different solutions also opens my mind up here...
> Great stuff guys...and thanks!
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>|||Ah,...yes...inner join does work!
(and...yes...some products might not have any invoice sold...so, likely I
will stick with left joins).
Anyway..here is the query plans for both:
http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn|||Glad I could help. Thanks for a peek at the EEP. You'll notice that the
Optimizer picked the same execution steps just changed the order of the join
s.
It is concerning that that you are running Table Scans. This happens
because we are not limiting the results but aggregating all records. The
point is, you will have to touch every record. The fact you are doing Table
Scans indicates you have not defined a Clustered Index for any of these thre
e
tables.
Every Table should have a Primary Key defined, for integrity and you are not
even in 1NF if you haven't defined a key, which means you do not have a
relational table. Bad mojo. Second, every Table should have a Clustered
Index defined. The reason being, this defines the actual data sort for
storage. Usually, the Primary Key and the Clustered Index are NOT the same,
at least with how many use them.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:

> Ah,...yes...inner join does work!
> (and...yes...some products might not have any invoice sold...so, likely I
> will stick with left joins).
> Anyway..here is the query plans for both:
> http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>