I am not clear on the relationship between statistics being updated and
stored procedures being recomipled. I beleive that when when auto stats
is on then a stored procedure will be recompiled when statistics are
updated on a table that the stored procedure accesses.
However I have auto stats turned off for a number of tables and instead
update the statistics on a regular bases depending on certain data
characteristics of the table.
So I am not clear on whether the recompilation is triggered by the
updating of the statistics or by the data modification in the table. In
other words will a stored prodedure still be recompiled when are
certain number of modifications are made to the table even if I have
auto stats turned off or will it only be recompiled when I manually
update the statistics?
Thanks!There is a very good white paper that covers both SQL 2000 and SQL 2005
compilation/recompilation at
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Hope this helps.
Dan Guzman
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1158954729.540099.201400@.i3g2000cwc.googlegroups.com...
>I am not clear on the relationship between statistics being updated and
> stored procedures being recomipled. I beleive that when when auto stats
> is on then a stored procedure will be recompiled when statistics are
> updated on a table that the stored procedure accesses.
> However I have auto stats turned off for a number of tables and instead
> update the statistics on a regular bases depending on certain data
> characteristics of the table.
> So I am not clear on whether the recompilation is triggered by the
> updating of the statistics or by the data modification in the table. In
> other words will a stored prodedure still be recompiled when are
> certain number of modifications are made to the table even if I have
> auto stats turned off or will it only be recompiled when I manually
> update the statistics?
> Thanks!
>
Showing posts with label relationship. Show all posts
Showing posts with label relationship. Show all posts
Wednesday, March 7, 2012
Question about auto update statistics and stored proc recompilation
I am not clear on the relationship between statistics being updated and
stored procedures being recomipled. I beleive that when when auto stats
is on then a stored procedure will be recompiled when statistics are
updated on a table that the stored procedure accesses.
However I have auto stats turned off for a number of tables and instead
update the statistics on a regular bases depending on certain data
characteristics of the table.
So I am not clear on whether the recompilation is triggered by the
updating of the statistics or by the data modification in the table. In
other words will a stored prodedure still be recompiled when are
certain number of modifications are made to the table even if I have
auto stats turned off or will it only be recompiled when I manually
update the statistics?
Thanks!There is a very good white paper that covers both SQL 2000 and SQL 2005
compilation/recompilation at
http://www.microsoft.com/technet/pr...005/recomp.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1158954729.540099.201400@.i3g2000cwc.googlegroups.com...
>I am not clear on the relationship between statistics being updated and
> stored procedures being recomipled. I beleive that when when auto stats
> is on then a stored procedure will be recompiled when statistics are
> updated on a table that the stored procedure accesses.
> However I have auto stats turned off for a number of tables and instead
> update the statistics on a regular bases depending on certain data
> characteristics of the table.
> So I am not clear on whether the recompilation is triggered by the
> updating of the statistics or by the data modification in the table. In
> other words will a stored prodedure still be recompiled when are
> certain number of modifications are made to the table even if I have
> auto stats turned off or will it only be recompiled when I manually
> update the statistics?
> Thanks!
>
stored procedures being recomipled. I beleive that when when auto stats
is on then a stored procedure will be recompiled when statistics are
updated on a table that the stored procedure accesses.
However I have auto stats turned off for a number of tables and instead
update the statistics on a regular bases depending on certain data
characteristics of the table.
So I am not clear on whether the recompilation is triggered by the
updating of the statistics or by the data modification in the table. In
other words will a stored prodedure still be recompiled when are
certain number of modifications are made to the table even if I have
auto stats turned off or will it only be recompiled when I manually
update the statistics?
Thanks!There is a very good white paper that covers both SQL 2000 and SQL 2005
compilation/recompilation at
http://www.microsoft.com/technet/pr...005/recomp.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1158954729.540099.201400@.i3g2000cwc.googlegroups.com...
>I am not clear on the relationship between statistics being updated and
> stored procedures being recomipled. I beleive that when when auto stats
> is on then a stored procedure will be recompiled when statistics are
> updated on a table that the stored procedure accesses.
> However I have auto stats turned off for a number of tables and instead
> update the statistics on a regular bases depending on certain data
> characteristics of the table.
> So I am not clear on whether the recompilation is triggered by the
> updating of the statistics or by the data modification in the table. In
> other words will a stored prodedure still be recompiled when are
> certain number of modifications are made to the table even if I have
> auto stats turned off or will it only be recompiled when I manually
> update the statistics?
> Thanks!
>
Question about auto update statistics and stored proc recompilation
I am not clear on the relationship between statistics being updated and
stored procedures being recomipled. I beleive that when when auto stats
is on then a stored procedure will be recompiled when statistics are
updated on a table that the stored procedure accesses.
However I have auto stats turned off for a number of tables and instead
update the statistics on a regular bases depending on certain data
characteristics of the table.
So I am not clear on whether the recompilation is triggered by the
updating of the statistics or by the data modification in the table. In
other words will a stored prodedure still be recompiled when are
certain number of modifications are made to the table even if I have
auto stats turned off or will it only be recompiled when I manually
update the statistics?
Thanks!
There is a very good white paper that covers both SQL 2000 and SQL 2005
compilation/recompilation at
http://www.microsoft.com/technet/pro...05/recomp.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1158954729.540099.201400@.i3g2000cwc.googlegro ups.com...
>I am not clear on the relationship between statistics being updated and
> stored procedures being recomipled. I beleive that when when auto stats
> is on then a stored procedure will be recompiled when statistics are
> updated on a table that the stored procedure accesses.
> However I have auto stats turned off for a number of tables and instead
> update the statistics on a regular bases depending on certain data
> characteristics of the table.
> So I am not clear on whether the recompilation is triggered by the
> updating of the statistics or by the data modification in the table. In
> other words will a stored prodedure still be recompiled when are
> certain number of modifications are made to the table even if I have
> auto stats turned off or will it only be recompiled when I manually
> update the statistics?
> Thanks!
>
stored procedures being recomipled. I beleive that when when auto stats
is on then a stored procedure will be recompiled when statistics are
updated on a table that the stored procedure accesses.
However I have auto stats turned off for a number of tables and instead
update the statistics on a regular bases depending on certain data
characteristics of the table.
So I am not clear on whether the recompilation is triggered by the
updating of the statistics or by the data modification in the table. In
other words will a stored prodedure still be recompiled when are
certain number of modifications are made to the table even if I have
auto stats turned off or will it only be recompiled when I manually
update the statistics?
Thanks!
There is a very good white paper that covers both SQL 2000 and SQL 2005
compilation/recompilation at
http://www.microsoft.com/technet/pro...05/recomp.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1158954729.540099.201400@.i3g2000cwc.googlegro ups.com...
>I am not clear on the relationship between statistics being updated and
> stored procedures being recomipled. I beleive that when when auto stats
> is on then a stored procedure will be recompiled when statistics are
> updated on a table that the stored procedure accesses.
> However I have auto stats turned off for a number of tables and instead
> update the statistics on a regular bases depending on certain data
> characteristics of the table.
> So I am not clear on whether the recompilation is triggered by the
> updating of the statistics or by the data modification in the table. In
> other words will a stored prodedure still be recompiled when are
> certain number of modifications are made to the table even if I have
> auto stats turned off or will it only be recompiled when I manually
> update the statistics?
> Thanks!
>
Monday, February 20, 2012
Question
I am trying to delete a row from a table that has a FK to
another table. I delete that relationship, delete the row
but when i want to create the relationship again i get
this error:
- Unable to create relationship 'FK_TAI00812_TAI00804'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
collation as referencing column 'TAI00812.C_APL_SBCX' in
foreign key 'FK_TAI00812_TAI00804'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not
create constraint. See previous errors.
Can anyone help me please?
Hi Claudy,
From the error message it looks like the collation of primary key and the
referencing foreign key is not same.
use sp_help on both the tables and see the Collation of these two columns.
You can change the collation using ALTER TABLE, but before that you may want
to understand the implication of the collation change at the column level.
Thanks
Yogish
|||I'm having the same problem. On my development database, I have no problem in creating the foreign key constraint, but on the production databases it doesn't work!
albert@.vanbiljon.com
[vbcol=seagreen]
> I am trying to delete a row from a table that has a FK to
> another table. I delete that relationship, delete the row
> but when i want to create the relationship again i get
> this error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
> collation as referencing column 'TAI00812.C_APL_SBCX' in
> foreign key 'FK_TAI00812_TAI00804'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
> create constraint. See previous errors.
User submitted from AEWNET (http://www.aewnet.com/)
|||Guest
It is a good practice to create a relationship PK\FK between columns defined
as INT,SMALLINT.... and not VARCHAR(n)
It seems that you have a different collation in two tables.
"Guest" <Guest@.aew_nospam.com> wrote in message
news:O8Y154dOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> I'm having the same problem. On my development database, I have no problem
in creating the foreign key constraint, but on the production databases it
doesn't work!
> albert@.vanbiljon.com
>
> User submitted from AEWNET (http://www.aewnet.com/)
another table. I delete that relationship, delete the row
but when i want to create the relationship again i get
this error:
- Unable to create relationship 'FK_TAI00812_TAI00804'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
collation as referencing column 'TAI00812.C_APL_SBCX' in
foreign key 'FK_TAI00812_TAI00804'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not
create constraint. See previous errors.
Can anyone help me please?
Hi Claudy,
From the error message it looks like the collation of primary key and the
referencing foreign key is not same.
use sp_help on both the tables and see the Collation of these two columns.
You can change the collation using ALTER TABLE, but before that you may want
to understand the implication of the collation change at the column level.
Thanks
Yogish
|||I'm having the same problem. On my development database, I have no problem in creating the foreign key constraint, but on the production databases it doesn't work!
albert@.vanbiljon.com
[vbcol=seagreen]
> I am trying to delete a row from a table that has a FK to
> another table. I delete that relationship, delete the row
> but when i want to create the relationship again i get
> this error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
> collation as referencing column 'TAI00812.C_APL_SBCX' in
> foreign key 'FK_TAI00812_TAI00804'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
> create constraint. See previous errors.
User submitted from AEWNET (http://www.aewnet.com/)
|||Guest
It is a good practice to create a relationship PK\FK between columns defined
as INT,SMALLINT.... and not VARCHAR(n)
It seems that you have a different collation in two tables.
"Guest" <Guest@.aew_nospam.com> wrote in message
news:O8Y154dOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> I'm having the same problem. On my development database, I have no problem
in creating the foreign key constraint, but on the production databases it
doesn't work!
> albert@.vanbiljon.com
>
> User submitted from AEWNET (http://www.aewnet.com/)
Question
I am trying to delete a row from a table that has a FK to
another table. I delete that relationship, delete the row
but when i want to create the relationship again i get
this error:
- Unable to create relationship 'FK_TAI00812_TAI00804'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
collation as referencing column 'TAI00812.C_APL_SBCX' in
foreign key 'FK_TAI00812_TAI00804'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not
create constraint. See previous errors.
Can anyone help me please?Hi Claudy,
From the error message it looks like the collation of primary key and the
referencing foreign key is not same.
use sp_help on both the tables and see the Collation of these two columns.
You can change the collation using ALTER TABLE, but before that you may want
to understand the implication of the collation change at the column level.
--
Thanks
Yogish|||I'm having the same problem. On my development database, I have no problem in creating the foreign key constraint, but on the production databases it doesn't work!
albert@.vanbiljon.com
> I am trying to delete a row from a table that has a FK to
> another table. I delete that relationship, delete the row
> but when i want to create the relationship again i get
> this error:
> > - Unable to create relationship 'FK_TAI00812_TAI00804'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
> collation as referencing column 'TAI00812.C_APL_SBCX' in
> foreign key 'FK_TAI00812_TAI00804'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
> create constraint. See previous errors.
> > Can anyone help me please?
User submitted from AEWNET (http://www.aewnet.com/)|||Guest
It is a good practice to create a relationship PK\FK between columns defined
as INT,SMALLINT.... and not VARCHAR(n)
It seems that you have a different collation in two tables.
"Guest" <Guest@.aew_nospam.com> wrote in message
news:O8Y154dOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> I'm having the same problem. On my development database, I have no problem
in creating the foreign key constraint, but on the production databases it
doesn't work!
> albert@.vanbiljon.com
> > I am trying to delete a row from a table that has a FK to
> > another table. I delete that relationship, delete the row
> > but when i want to create the relationship again i get
> > this error:
> > > - Unable to create relationship 'FK_TAI00812_TAI00804'.
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> > Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
> > collation as referencing column 'TAI00812.C_APL_SBCX' in
> > foreign key 'FK_TAI00812_TAI00804'.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
> > create constraint. See previous errors.
> > > Can anyone help me please?
> User submitted from AEWNET (http://www.aewnet.com/)
another table. I delete that relationship, delete the row
but when i want to create the relationship again i get
this error:
- Unable to create relationship 'FK_TAI00812_TAI00804'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
collation as referencing column 'TAI00812.C_APL_SBCX' in
foreign key 'FK_TAI00812_TAI00804'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not
create constraint. See previous errors.
Can anyone help me please?Hi Claudy,
From the error message it looks like the collation of primary key and the
referencing foreign key is not same.
use sp_help on both the tables and see the Collation of these two columns.
You can change the collation using ALTER TABLE, but before that you may want
to understand the implication of the collation change at the column level.
--
Thanks
Yogish|||I'm having the same problem. On my development database, I have no problem in creating the foreign key constraint, but on the production databases it doesn't work!
albert@.vanbiljon.com
> I am trying to delete a row from a table that has a FK to
> another table. I delete that relationship, delete the row
> but when i want to create the relationship again i get
> this error:
> > - Unable to create relationship 'FK_TAI00812_TAI00804'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
> collation as referencing column 'TAI00812.C_APL_SBCX' in
> foreign key 'FK_TAI00812_TAI00804'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
> create constraint. See previous errors.
> > Can anyone help me please?
User submitted from AEWNET (http://www.aewnet.com/)|||Guest
It is a good practice to create a relationship PK\FK between columns defined
as INT,SMALLINT.... and not VARCHAR(n)
It seems that you have a different collation in two tables.
"Guest" <Guest@.aew_nospam.com> wrote in message
news:O8Y154dOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> I'm having the same problem. On my development database, I have no problem
in creating the foreign key constraint, but on the production databases it
doesn't work!
> albert@.vanbiljon.com
> > I am trying to delete a row from a table that has a FK to
> > another table. I delete that relationship, delete the row
> > but when i want to create the relationship again i get
> > this error:
> > > - Unable to create relationship 'FK_TAI00812_TAI00804'.
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> > Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
> > collation as referencing column 'TAI00812.C_APL_SBCX' in
> > foreign key 'FK_TAI00812_TAI00804'.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
> > create constraint. See previous errors.
> > > Can anyone help me please?
> User submitted from AEWNET (http://www.aewnet.com/)
Question
I am trying to delete a row from a table that has a FK to
another table. I delete that relationship, delete the row
but when i want to create the relationship again i get
this error:
- Unable to create relationship 'FK_TAI00812_TAI00804'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
collation as referencing column 'TAI00812.C_APL_SBCX' in
foreign key 'FK_TAI00812_TAI00804'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not
create constraint. See previous errors.
Can anyone help me please?Hi Claudy,
From the error message it looks like the collation of primary key and the
referencing foreign key is not same.
use sp_help on both the tables and see the Collation of these two columns.
You can change the collation using ALTER TABLE, but before that you may want
to understand the implication of the collation change at the column level.
Thanks
Yogish|||I'm having the same problem. On my development database, I have no problem i
n creating the foreign key constraint, but on the production databases it do
esn't work!
albert@.vanbiljon.com
[vbcol=seagreen]
> I am trying to delete a row from a table that has a FK to
> another table. I delete that relationship, delete the row
> but when i want to create the relationship again i get
> this error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
> collation as referencing column 'TAI00812.C_APL_SBCX' in
> foreign key 'FK_TAI00812_TAI00804'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
> create constraint. See previous errors.
User submitted from AEWNET (http://www.aewnet.com/)|||Guest
It is a good practice to create a relationship PK\FK between columns defined
as INT,SMALLINT.... and not VARCHAR(n)
It seems that you have a different collation in two tables.
"Guest" <Guest@.aew_nospam.com> wrote in message
news:O8Y154dOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> I'm having the same problem. On my development database, I have no problem
in creating the foreign key constraint, but on the production databases it
doesn't work!
> albert@.vanbiljon.com
>
> User submitted from AEWNET (http://www.aewnet.com/)
another table. I delete that relationship, delete the row
but when i want to create the relationship again i get
this error:
- Unable to create relationship 'FK_TAI00812_TAI00804'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
collation as referencing column 'TAI00812.C_APL_SBCX' in
foreign key 'FK_TAI00812_TAI00804'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not
create constraint. See previous errors.
Can anyone help me please?Hi Claudy,
From the error message it looks like the collation of primary key and the
referencing foreign key is not same.
use sp_help on both the tables and see the Collation of these two columns.
You can change the collation using ALTER TABLE, but before that you may want
to understand the implication of the collation change at the column level.
Thanks
Yogish|||I'm having the same problem. On my development database, I have no problem i
n creating the foreign key constraint, but on the production databases it do
esn't work!
albert@.vanbiljon.com
[vbcol=seagreen]
> I am trying to delete a row from a table that has a FK to
> another table. I delete that relationship, delete the row
> but when i want to create the relationship again i get
> this error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Column 'dbo.TAI00804.C_APL_SBCX' is not of same
> collation as referencing column 'TAI00812.C_APL_SBCX' in
> foreign key 'FK_TAI00812_TAI00804'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
> create constraint. See previous errors.
User submitted from AEWNET (http://www.aewnet.com/)|||Guest
It is a good practice to create a relationship PK\FK between columns defined
as INT,SMALLINT.... and not VARCHAR(n)
It seems that you have a different collation in two tables.
"Guest" <Guest@.aew_nospam.com> wrote in message
news:O8Y154dOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> I'm having the same problem. On my development database, I have no problem
in creating the foreign key constraint, but on the production databases it
doesn't work!
> albert@.vanbiljon.com
>
> User submitted from AEWNET (http://www.aewnet.com/)
Subscribe to:
Posts (Atom)