3197 record deleted error

HI,
i have an access MDB linked by ODBC to SQL2k.
I get a strange behaviour when i do an INSERT INTO to a local Mdb table
from "LEFT OUTER JOINED" SQL tables.
If the related table hasn't related record to the first table,
it get an Error 3167.
This behaviour is not present with MDB tables JOINS.
Who know something about this ?If you are getting an error 3167 in Access then it's likely
an access error - can't tell without the error message you
get. But that's probably the case and you would want to post
your question in an access newsgroup.
-Sue
On 27 Oct 2005 08:05:38 -0700, "merco"
<d.mercanti@.gmail.com> wrote:

>HI,
>i have an access MDB linked by ODBC to SQL2k.
>I get a strange behaviour when i do an INSERT INTO to a local Mdb table
>from "LEFT OUTER JOINED" SQL tables.
>If the related table hasn't related record to the first table,
>it get an Error 3167.
>This behaviour is not present with MDB tables JOINS.
>Who know something about this ?|||well, if i use only mdb tables this error doesn't occour.
So i suppose it's an odbc problem|||Make sure you have a primary key or unique identifier
defined for your tables you are linking. Make sure the
primary key or unique identifier is not an approximate data
type (e.g. float). Try putting on timestamp data type column
on the tables you are linking.
-Sue
On 28 Oct 2005 00:25:17 -0700, "merco"
<d.mercanti@.gmail.com> wrote:

>well, if i use only mdb tables this error doesn't occour.
>So i suppose it's an odbc problem

3197 record deleted error

HI,
i have an access MDB linked by ODBC to SQL2k.
I get a strange behaviour when i do an INSERT INTO to a local Mdb table
from "LEFT OUTER JOINED" SQL tables.
If the related table hasn't related record to the first table,
it get an Error 3167.
This behaviour is not present with MDB tables JOINS.
Who know something about this ?
If you are getting an error 3167 in Access then it's likely
an access error - can't tell without the error message you
get. But that's probably the case and you would want to post
your question in an access newsgroup.
-Sue
On 27 Oct 2005 08:05:38 -0700, "merco"
<d.mercanti@.gmail.com> wrote:

>HI,
>i have an access MDB linked by ODBC to SQL2k.
>I get a strange behaviour when i do an INSERT INTO to a local Mdb table
>from "LEFT OUTER JOINED" SQL tables.
>If the related table hasn't related record to the first table,
>it get an Error 3167.
>This behaviour is not present with MDB tables JOINS.
>Who know something about this ?
|||well, if i use only mdb tables this error doesn't occour.
So i suppose it's an odbc problem
|||Make sure you have a primary key or unique identifier
defined for your tables you are linking. Make sure the
primary key or unique identifier is not an approximate data
type (e.g. float). Try putting on timestamp data type column
on the tables you are linking.
-Sue
On 28 Oct 2005 00:25:17 -0700, "merco"
<d.mercanti@.gmail.com> wrote:

>well, if i use only mdb tables this error doesn't occour.
>So i suppose it's an odbc problem

2-way replication through an outbound only firewall. Is this possible?

We have a client that would like to have their (Remote) web server
SQL2K instance set-up for 2-way replication against their internal
private SQL2K instance (Local). The problem is their network security
department won't allow *any* TCP/IP socket connections to be
initiated from the Remote SQL2K server through the firewall to their
internal Local server. However outbound connections, initiated from the
Local server (i.e. Local -> Firewall -> Remote), are allowed.
Can SQL Server support 2-way replication under this sort of firewall
restriction?
I imagine the Remote web server SQL2K instance would need to be
configured as the Publisher (listening on port 1433) and the Local
SQL2K instance a Subscriber. The Local Subscriber would then be
configured to connect periodically to the Publisher for updates. When
the Subscriber is connected all queued data deltas (on either server)
would be replicated between them to insure both instances are
synchronized. The Subscriber would then disconnect. (or not?)
Would the distributor process need to run on the Remote or Local
server? Would we need to run merge or transactional based replication?
More info:
- The network connection between servers should hardly
ever be down.
- Not all tables in the database schema need to be
replicated, just a subset.
- To begin with the tables will be empty on both servers,
so we don't need to worry about an initial snapshot.
- We hope to use SQL2K's Auto Identity Range Management
to ensure unique keys do not collide.
Thanks for any feedback you can offer.
Good to hear that it can work Markus,
Do you see any problems in running a very short period between data
merge pushes? Say every minute or even less?
Will SQL2K prevent a new merge from running if one is already in
progress?
-Paul

2-node to 4-node

Hi!
I have a customer with a 2-node active/active sql2k cluster hosted on
w2k.
We now want to run this on w2k3 and we also want to build up the
cluster to have a 4-node active/active/active/active sql2k cluster.
Our plan is to do it in two steps as follow. We take the current 2
nodes offline and unplug them from the network and then install the 2
new nodes with w2k3 using the original ipaddresses and netbiosname and
then restoring the sql2k (one virtuel server and one named instance).
This should give us the same cluster as before but with sql2k running
on w2k3.
This is because the customer will be able to unplug the new nodes and
replug the old ones if there is any trouble with the new nodes.
Then after a few days it's time to reinstall the old nodes with w2k3
and new netbiosnames and connect them to the cluster so we get our
4-node. Then install 2 new named sql instances.
We want all 4 sql instances to be able to run in all 4 nodes.
Now some questions!
When I install a new named instance (on node 3) it will be available
in the other 2 nodes, but will the original sql2k on these 2 nodes be
available in node 3? The same question with node 4.
Is there some problems with sql2k and w2k3 because it is no way to
uninstall sql2k under MSCS? In w2k I'm able to remove it under the
control panel.
/Peter
I actually did a 2 to 4 node cluster upgrade. During the transition, I
replaced the original nodes with new hardware, so I went from 2 nodes with 4
procs each to 4 nodes with 8 procs each. I also went from 2 instances to 3
instances of SQL server. Here is how I did it.
Upgrade the existing nodes (1 at a time) to W2K3. Add the new nodes.
Install SQL and Service packs to the new nodes. Fail the cluster over to
the new nodes. Remove the old nodes and replace with new hardware. At this
point, I would drop those nodes and rebuild them from scratch. Upgrad for
2003 is good, but I prefer a clean build. Add the new nodes to SQL and
apply the service packs. Install the third SQL instance and service pack.
As for your two questions, whenever you add or remove a node, you will need
to use the SQL install CD to add or remove the node from the SQL
installation. This is in addition to evicting or adding the node from the
cluster tool. Choose Advanced Options and Maintain Failover Clustering.
Read BOL on failover clustering for more details.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Peter Lindberg" <peter.lindbergTAKE@.AWAYmartinsson.se> wrote in message
news:40641e4b.73299028@.msnews.microsoft.com...
> Hi!
> I have a customer with a 2-node active/active sql2k cluster hosted on
> w2k.
> We now want to run this on w2k3 and we also want to build up the
> cluster to have a 4-node active/active/active/active sql2k cluster.
> Our plan is to do it in two steps as follow. We take the current 2
> nodes offline and unplug them from the network and then install the 2
> new nodes with w2k3 using the original ipaddresses and netbiosname and
> then restoring the sql2k (one virtuel server and one named instance).
> This should give us the same cluster as before but with sql2k running
> on w2k3.
> This is because the customer will be able to unplug the new nodes and
> replug the old ones if there is any trouble with the new nodes.
> Then after a few days it's time to reinstall the old nodes with w2k3
> and new netbiosnames and connect them to the cluster so we get our
> 4-node. Then install 2 new named sql instances.
> We want all 4 sql instances to be able to run in all 4 nodes.
> Now some questions!
> When I install a new named instance (on node 3) it will be available
> in the other 2 nodes, but will the original sql2k on these 2 nodes be
> available in node 3? The same question with node 4.
> Is there some problems with sql2k and w2k3 because it is no way to
> uninstall sql2k under MSCS? In w2k I'm able to remove it under the
> control panel.
> /Peter
>
|||Thank's for your advice Geoff.
The questions comes after a test install I have done and there was a
few odd things that happend.
We use Veritas Volume Manager to allow dynamic disc. I'm not involved
in that part, but the storage techi say's that the veritas code for
w2k can't handle an upgrade of windows to w2k3 and veritas w2k code
can't be upgraded to w2k3 version and still running on w2k. That's why
I want to install w2k3 on new hw with the new codepack from Verirtas.
In our test we run into the following situation:
On node one where we installed the veritas code we connected to disk
and then set up the cluster and then the first sql virtual server. At
that time the other node didn't have the veritas code yet, it only had
w2k3 and was not part of the ckuster. That means all disk was
available to node one. That node was installed with a named instance
where I by the way had to use instruction from KB815431 and sp3a. That
was after the install of sql2k failed once.
After this we set up the veritas code on the other machine with
clustersupport and added it to the cluster. We made an error labeling
one disk with the same name as the other node(cut and paste). When
changing that we had to uninstall the veritas code and break the
cluster. On with veritas code again without clustersupport so we get a
possiblity to chage that label. Then get the node in the cluster
again.
Here I installed a new virtuel server with a new named instance and
sp3a on the second node. This setup was without problem and the only
odd thing was that the dtc service still was automatic.
Next step was to se that the instances could move between the nodes.
The second node could move to the first, but the first could not move
to the second. We found out that the first node have the service for
both instances but the second only the service for the last installed
instance.
I can't se why this happend.
This is why I'm conserned about adding 2 more nodes after a week. Will
the initial two be able to move to new once? If the problem I
described is created by some of my mistake I will still will do this
upgrade in 2 steps, but if there is this problem we have to reinstall
the old nodes to w2k3 at the same time.
I will try to recreate it in vmware workstation, but if someone know
anything about this I will be happy to hear it.
/Peter
On Fri, 26 Mar 2004 10:26:22 -0500, "Geoff N. Hiten"
<SRDBA@.Careerbuilder.com> wrotc:

>I actually did a 2 to 4 node cluster upgrade. During the transition, I
>replaced the original nodes with new hardware, so I went from 2 nodes with 4
>procs each to 4 nodes with 8 procs each. I also went from 2 instances to 3
>instances of SQL server. Here is how I did it.
>Upgrade the existing nodes (1 at a time) to W2K3. Add the new nodes.
>Install SQL and Service packs to the new nodes. Fail the cluster over to
>the new nodes. Remove the old nodes and replace with new hardware. At this
>point, I would drop those nodes and rebuild them from scratch. Upgrad for
>2003 is good, but I prefer a clean build. Add the new nodes to SQL and
>apply the service packs. Install the third SQL instance and service pack.
>As for your two questions, whenever you add or remove a node, you will need
>to use the SQL install CD to add or remove the node from the SQL
>installation. This is in addition to evicting or adding the node from the
>cluster tool. Choose Advanced Options and Maintain Failover Clustering.
>Read BOL on failover clustering for more details.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Peter Lindberg" <peter.lindbergTAKE@.AWAYmartinsson.se> wrote in message
>news:40641e4b.73299028@.msnews.microsoft.com...
>
|||You can determine which nodes can host which instances. Using the Install
CD and the Advanced Options tab, you can add or remove individual nodes to a
particular instance. I added each instance to each new node individually.
I then added the service pack to the new node/instance. Of course, I
rebooted each new node after each step, so there are a lot of reboots.
There is a new feature in W2k3 where you can expand basic cluster disks
without converting them to dynamic disks. That may eliminate the need for
Veritas Volume Manager.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Peter Lindberg" <peter.lindberg@.unigrid.se> wrote in message
news:406873b9.247543388@.msnews.microsoft.com...
> Thank's for your advice Geoff.
> The questions comes after a test install I have done and there was a
> few odd things that happend.
> We use Veritas Volume Manager to allow dynamic disc. I'm not involved
> in that part, but the storage techi say's that the veritas code for
> w2k can't handle an upgrade of windows to w2k3 and veritas w2k code
> can't be upgraded to w2k3 version and still running on w2k. That's why
> I want to install w2k3 on new hw with the new codepack from Verirtas.
> In our test we run into the following situation:
> On node one where we installed the veritas code we connected to disk
> and then set up the cluster and then the first sql virtual server. At
> that time the other node didn't have the veritas code yet, it only had
> w2k3 and was not part of the ckuster. That means all disk was
> available to node one. That node was installed with a named instance
> where I by the way had to use instruction from KB815431 and sp3a. That
> was after the install of sql2k failed once.
> After this we set up the veritas code on the other machine with
> clustersupport and added it to the cluster. We made an error labeling
> one disk with the same name as the other node(cut and paste). When
> changing that we had to uninstall the veritas code and break the
> cluster. On with veritas code again without clustersupport so we get a
> possiblity to chage that label. Then get the node in the cluster
> again.
> Here I installed a new virtuel server with a new named instance and
> sp3a on the second node. This setup was without problem and the only
> odd thing was that the dtc service still was automatic.
> Next step was to se that the instances could move between the nodes.
> The second node could move to the first, but the first could not move
> to the second. We found out that the first node have the service for
> both instances but the second only the service for the last installed
> instance.
> I can't se why this happend.
> This is why I'm conserned about adding 2 more nodes after a week. Will
> the initial two be able to move to new once? If the problem I
> described is created by some of my mistake I will still will do this
> upgrade in 2 steps, but if there is this problem we have to reinstall
> the old nodes to w2k3 at the same time.
> I will try to recreate it in vmware workstation, but if someone know
> anything about this I will be happy to hear it.
> /Peter
>
> On Fri, 26 Mar 2004 10:26:22 -0500, "Geoff N. Hiten"
> <SRDBA@.Careerbuilder.com> wrotc:
with 4
3
this
for
pack.
need
the
>
|||I saw that under Advanced Options but the button was greymarked. It
must have happend something. Now I have done it i vmware and seen it
work.
Next will be the actual go later this week, and then two more nodes a
week later. I'll remember those reboots after each part. I get it that
before starting with node 4 you where up with SP3a on node 3.
/Peter
On Mon, 29 Mar 2004 09:39:03 -0500, "Geoff N. Hiten"
<SRDBA@.Careerbuilder.com> wrotc:

>You can determine which nodes can host which instances. Using the Install
>CD and the Advanced Options tab, you can add or remove individual nodes to a
>particular instance. I added each instance to each new node individually.
>I then added the service pack to the new node/instance. Of course, I
>rebooted each new node after each step, so there are a lot of reboots.
>There is a new feature in W2k3 where you can expand basic cluster disks
>without converting them to dynamic disks. That may eliminate the need for
>Veritas Volume Manager.

2k Replication errs .... Error 2812

Hey,
Having some probs w replication between 2 2k srvs running sql2k.

The dist agent is experiencing errors........

"Could not find stored procedure 'sp_MSins_tester'

Error number: 2812

Initially it gave an error of 18452 on dist agent of Could not connect.
I updated the id/pw for the subscriber and that went away and generated the above.

Any thoughts on what I should try next?

--mattHi Matt ,

from what I have experienced (both errors) you will have to create a stored procedure for you dist agent , that you should solve the problem.you might want to check sql books online there is more info there.

Hope this helps in some way..

Regards
Burner|||What did you end up doing to get by this problem ?|||Which service pack do you have installed for sql server 2000 (on both sql servers) ?

2GB Limit

What happens when an SQL2K MSDE database reaches 2GB? I've actually seen
MSDE databases reach that size and nothing happened.
Thanks,
Ademar.
hi Ademar,
Ademar Nunes wrote:
> What happens when an SQL2K MSDE database reaches 2GB? I've actually
> seen MSDE databases reach that size and nothing happened.
when you exceed that limit, nex time the Storage Engine requires a file
allocation beyond the allocated space, thus trying expanding the data file,
an exception will be raised..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Maybe the 2GB+ installation I'm referring to is actually a Personal edition
and not MSDE.
I'll run the SELECT @.@.VERSION statement on it and I'm assuming that will
tell me if it is MSDE.
Thanks,
Ademar.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3o8jmjF4mu4pU1@.individual.net...
> hi Ademar,
> Ademar Nunes wrote:
> when you exceed that limit, nex time the Storage Engine requires a file
> allocation beyond the allocated space, thus trying expanding the data
> file, an exception will be raised..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Hi,
If it is Personal edition; there is no db size limitation. See details in
below URL
http://msdn.microsoft.com/library/?u...asp?frame=true
Thanks
Hari
SQL Server MVP
"Ademar Nunes" <anunes@.myemail.com> wrote in message
news:%23VOAP38sFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Maybe the 2GB+ installation I'm referring to is actually a Personal
> edition and not MSDE.
> I'll run the SELECT @.@.VERSION statement on it and I'm assuming that will
> tell me if it is MSDE.
> Thanks,
> Ademar.
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3o8jmjF4mu4pU1@.individual.net...
>

2005 vs 2000 performance problem

We have "proc_a" in our production sql2k db, executing it takes 5-10 seconds
if we backup our prod db, restore to sql2k5, rebuild all indexes, on faster
hardware, same proc takes about 2 minutes to run.
Can anyone suggest things to look at? This proc does quite a bit of
creating temp tables and inserting/deleting from them. (a legacy approach
we are looking to change)
I am comparing estimated execution plans and they look similar.
Thanks in advance!
Derrick> if we backup our prod db, restore to sql2k5, rebuild all indexes, on
> faster hardware, same proc takes about 2 minutes to run.
> Can anyone suggest things to look at?
Is compatibility mode set to 80 or 90? How "similar" are the execution
plans? If you do statistics time and statistics i/o, where is all the work
being done and time being spent?
Aaron Bertrand
SQL Server MVP|||Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
syntax again to get the query plan as text, with stats? I am trying :
set statistics time on
go
set statistics io on
go
set showplan_text on
go
exec myproc
The proc does *lots* of things, so eyeballing a compare between plans is
next to impossible.
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OwZMg%234zHHA.1164@.TK2MSFTNGP02.phx.gbl...
> Is compatibility mode set to 80 or 90? How "similar" are the execution
> plans? If you do statistics time and statistics i/o, where is all the
> work being done and time being spent?
> --
> Aaron Bertrand
> SQL Server MVP
>|||> Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
> syntax again to get the query plan as text, with stats? I am trying :
I use this:
SET NOCOUNT ON;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO
SET SHOWPLAN_XML ON;
GO
EXEC dbo.my_procedure;
GO
SET SHOWPLAN_XML OFF;
GO
I don't really get much out of showplan_text. YMMV.

> The proc does *lots* of things, so eyeballing a compare between plans is
> next to impossible.
Then you might want to break the procedure up into chunks to make the
analysis more practical. Or get a bigger (or additional) monitor. :-)
A|||Aaron, thanks again, worked great.
A little more description on the proc, it is creating in table variables
(@.tables instead of #table) and then doing a join to that table, from 'real'
tables.
The rows returned on the join are crazy, millions, where in 2k, only
hundreds
Any idea if there is something with 2005 different than 2000 with respect to
indexes on table variables?
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OM8JTO5zHHA.5800@.TK2MSFTNGP05.phx.gbl...
> I use this:
> SET NOCOUNT ON;
> SET STATISTICS TIME ON;
> SET STATISTICS IO ON;
> GO
> SET SHOWPLAN_XML ON;
> GO
> EXEC dbo.my_procedure;
> GO
> SET SHOWPLAN_XML OFF;
> GO
> I don't really get much out of showplan_text. YMMV.
>
> Then you might want to break the procedure up into chunks to make the
> analysis more practical. Or get a bigger (or additional) monitor. :-)
> A
>

2005 vs 2000 performance problem

We have "proc_a" in our production sql2k db, executing it takes 5-10 seconds
if we backup our prod db, restore to sql2k5, rebuild all indexes, on faster
hardware, same proc takes about 2 minutes to run.
Can anyone suggest things to look at? This proc does quite a bit of
creating temp tables and inserting/deleting from them. (a legacy approach
we are looking to change)
I am comparing estimated execution plans and they look similar.
Thanks in advance!
Derrick> if we backup our prod db, restore to sql2k5, rebuild all indexes, on
> faster hardware, same proc takes about 2 minutes to run.
> Can anyone suggest things to look at?
Is compatibility mode set to 80 or 90? How "similar" are the execution
plans? If you do statistics time and statistics i/o, where is all the work
being done and time being spent?
--
Aaron Bertrand
SQL Server MVP|||Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
syntax again to get the query plan as text, with stats? I am trying :
set statistics time on
go
set statistics io on
go
set showplan_text on
go
exec myproc
The proc does *lots* of things, so eyeballing a compare between plans is
next to impossible.
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OwZMg%234zHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> if we backup our prod db, restore to sql2k5, rebuild all indexes, on
>> faster hardware, same proc takes about 2 minutes to run.
>> Can anyone suggest things to look at?
> Is compatibility mode set to 80 or 90? How "similar" are the execution
> plans? If you do statistics time and statistics i/o, where is all the
> work being done and time being spent?
> --
> Aaron Bertrand
> SQL Server MVP
>|||> Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
> syntax again to get the query plan as text, with stats? I am trying :
I use this:
SET NOCOUNT ON;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO
SET SHOWPLAN_XML ON;
GO
EXEC dbo.my_procedure;
GO
SET SHOWPLAN_XML OFF;
GO
I don't really get much out of showplan_text. YMMV.
> The proc does *lots* of things, so eyeballing a compare between plans is
> next to impossible.
Then you might want to break the procedure up into chunks to make the
analysis more practical. Or get a bigger (or additional) monitor. :-)
A|||Aaron, thanks again, worked great.
A little more description on the proc, it is creating in table variables
(@.tables instead of #table) and then doing a join to that table, from 'real'
tables.
The rows returned on the join are crazy, millions, where in 2k, only
hundreds
Any idea if there is something with 2005 different than 2000 with respect to
indexes on table variables?
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OM8JTO5zHHA.5800@.TK2MSFTNGP05.phx.gbl...
>> Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
>> syntax again to get the query plan as text, with stats? I am trying :
> I use this:
> SET NOCOUNT ON;
> SET STATISTICS TIME ON;
> SET STATISTICS IO ON;
> GO
> SET SHOWPLAN_XML ON;
> GO
> EXEC dbo.my_procedure;
> GO
> SET SHOWPLAN_XML OFF;
> GO
> I don't really get much out of showplan_text. YMMV.
>> The proc does *lots* of things, so eyeballing a compare between plans is
>> next to impossible.
> Then you might want to break the procedure up into chunks to make the
> analysis more practical. Or get a bigger (or additional) monitor. :-)
> A
>

2005 vs 2000 performance problem

We have "proc_a" in our production sql2k db, executing it takes 5-10 seconds
if we backup our prod db, restore to sql2k5, rebuild all indexes, on faster
hardware, same proc takes about 2 minutes to run.
Can anyone suggest things to look at? This proc does quite a bit of
creating temp tables and inserting/deleting from them. (a legacy approach
we are looking to change)
I am comparing estimated execution plans and they look similar.
Thanks in advance!
Derrick
> if we backup our prod db, restore to sql2k5, rebuild all indexes, on
> faster hardware, same proc takes about 2 minutes to run.
> Can anyone suggest things to look at?
Is compatibility mode set to 80 or 90? How "similar" are the execution
plans? If you do statistics time and statistics i/o, where is all the work
being done and time being spent?
Aaron Bertrand
SQL Server MVP
|||Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
syntax again to get the query plan as text, with stats? I am trying :
set statistics time on
go
set statistics io on
go
set showplan_text on
go
exec myproc
The proc does *lots* of things, so eyeballing a compare between plans is
next to impossible.
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OwZMg%234zHHA.1164@.TK2MSFTNGP02.phx.gbl...
> Is compatibility mode set to 80 or 90? How "similar" are the execution
> plans? If you do statistics time and statistics i/o, where is all the
> work being done and time being spent?
> --
> Aaron Bertrand
> SQL Server MVP
>
|||> Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
> syntax again to get the query plan as text, with stats? I am trying :
I use this:
SET NOCOUNT ON;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO
SET SHOWPLAN_XML ON;
GO
EXEC dbo.my_procedure;
GO
SET SHOWPLAN_XML OFF;
GO
I don't really get much out of showplan_text. YMMV.

> The proc does *lots* of things, so eyeballing a compare between plans is
> next to impossible.
Then you might want to break the procedure up into chunks to make the
analysis more practical. Or get a bigger (or additional) monitor. :-)
A
|||Aaron, thanks again, worked great.
A little more description on the proc, it is creating in table variables
(@.tables instead of #table) and then doing a join to that table, from 'real'
tables.
The rows returned on the join are crazy, millions, where in 2k, only
hundreds
Any idea if there is something with 2005 different than 2000 with respect to
indexes on table variables?
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OM8JTO5zHHA.5800@.TK2MSFTNGP05.phx.gbl...
> I use this:
> SET NOCOUNT ON;
> SET STATISTICS TIME ON;
> SET STATISTICS IO ON;
> GO
> SET SHOWPLAN_XML ON;
> GO
> EXEC dbo.my_procedure;
> GO
> SET SHOWPLAN_XML OFF;
> GO
> I don't really get much out of showplan_text. YMMV.
>
> Then you might want to break the procedure up into chunks to make the
> analysis more practical. Or get a bigger (or additional) monitor. :-)
> A
>

2005 Licensing file based db's

I noticed it seems like 2k5 supports file based dbs... and I had a few
quick questions...
1) does it require me to have sql2k server installed on machines I
deploy my apps to, to use the file based dbs?
2) what kind of licensing surrounds the file based dbs?
Thanks in advance
Weston Weems
hi Weston,
Weston Weems wrote:
> I noticed it seems like 2k5 supports file based dbs... and I had a few
> quick questions...
> 1) does it require me to have sql2k server installed on machines I
> deploy my apps to, to use the file based dbs?
> 2) what kind of licensing surrounds the file based dbs?
> Thanks in advance
> Weston Weems
if you mean "User Instances" (AKA RANU) please have a look at
http://tinyurl.com/9obd8
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

2005 Licensing file based db's

I noticed it seems like 2k5 supports file based dbs... and I had a few
quick questions...
1) does it require me to have sql2k server installed on machines I
deploy my apps to, to use the file based dbs?
2) what kind of licensing surrounds the file based dbs?
Thanks in advance
Weston Weemshi Weston,
Weston Weems wrote:
> I noticed it seems like 2k5 supports file based dbs... and I had a few
> quick questions...
> 1) does it require me to have sql2k server installed on machines I
> deploy my apps to, to use the file based dbs?
> 2) what kind of licensing surrounds the file based dbs?
> Thanks in advance
> Weston Weems
if you mean "User Instances" (AKA RANU) please have a look at
http://tinyurl.com/9obd8
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

2005 Licensing file based db's

I noticed it seems like 2k5 supports file based dbs... and I had a few
quick questions...
1) does it require me to have sql2k server installed on machines I
deploy my apps to, to use the file based dbs?
2) what kind of licensing surrounds the file based dbs?
Thanks in advance
Weston Weemshi Weston,
Weston Weems wrote:
> I noticed it seems like 2k5 supports file based dbs... and I had a few
> quick questions...
> 1) does it require me to have sql2k server installed on machines I
> deploy my apps to, to use the file based dbs?
> 2) what kind of licensing surrounds the file based dbs?
> Thanks in advance
> Weston Weems
if you mean "User Instances" (AKA RANU) please have a look at
http://tinyurl.com/9obd8
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply