Altered SQL table locked for edits in Access 2002 front-end

I have altered SQL tables and then relinked them to a
Access 2002 front-end and receive the following
message "The Microsoft Jet Database engine stopped the
process because you and another user are attempting to
change the same data at the same time"
When the SQL table is altered there are no users currently
connected to any of the SQL or Access Databases.
The SQL table being altered is related to many other SQL
tables.
The alter statements are adding new fields to the table.
Please help!How are you altering the SQLS tables? From Access or some other tool?
Access caches linked table schema locally, so if you are going to make
modifications to the table schema, you need to delete the existing
links in the FE, make your changes on the BE, and then re-link the
tables from scratch.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sat, 20 Dec 2003 13:53:56 -0800, "Bek"
<anonymous@.discussions.microsoft.com> wrote:

quote:

---

>I have altered SQL tables and then relinked them to a
>Access 2002 front-end and receive the following
>message "The Microsoft Jet Database engine stopped the
>process because you and another user are attempting to
>change the same data at the same time"
>When the SQL table is altered there are no users currently
>connected to any of the SQL or Access Databases.
>The SQL table being altered is related to many other SQL
>tables.
>The alter statements are adding new fields to the table.
>Please help!

---

Altered Date for Stored Procedures

Is there a system table that has the date when the strored procedure was
altered?
thanksOnly for SQL Server 2005 (sys.procedures.modify_date)
Earlier versions do not track this information (though there are columns
that *look* like they might, but are never updated).
A
"Dev" <Dev@.discussions.microsoft.com> wrote in message
news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
> Is there a system table that has the date when the strored procedure was
> altered?
> thanks|||Aaron,
thanks for information.
you are right for the earlier versions, there is column LAST_ALTERED in
INFORMATION_SCHEMA.ROUTINES but is same as the created date and does not
change when the procedure is altered.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:

> Only for SQL Server 2005 (sys.procedures.modify_date)
> Earlier versions do not track this information (though there are columns
> that *look* like they might, but are never updated).
> A
>
>
> "Dev" <Dev@.discussions.microsoft.com> wrote in message
> news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
>
>sql

Altered Date for Stored Procedures

Is there a system table that has the date when the strored procedure was
altered?
thanksOnly for SQL Server 2005 (sys.procedures.modify_date)
Earlier versions do not track this information (though there are columns
that *look* like they might, but are never updated).
A
"Dev" <Dev@.discussions.microsoft.com> wrote in message
news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
> Is there a system table that has the date when the strored procedure was
> altered?
> thanks|||Aaron,
thanks for information.
you are right for the earlier versions, there is column LAST_ALTERED in
INFORMATION_SCHEMA.ROUTINES but is same as the created date and does not
change when the procedure is altered.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:
> Only for SQL Server 2005 (sys.procedures.modify_date)
> Earlier versions do not track this information (though there are columns
> that *look* like they might, but are never updated).
> A
>
>
> "Dev" <Dev@.discussions.microsoft.com> wrote in message
> news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
> > Is there a system table that has the date when the strored procedure was
> > altered?
> > thanks
>
>

Alter table in transactional Replication

I have a table that is being used in transactional replication. On publisher
side table is altered and a column is added. I would want the new column also
to be replicated.
Here is what i did-
- drop the subscription
- remove article from publication
- add back article
- modify insert update stored procedures at subscriber
- add the subscription
Is there any other way to do it as I donot want my subscription to be
dropped each time as it has some other tables that are replicated
continuously...
Regards,
Ravi
You can use sp_repladdcolumn.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Alter table changes optimizer query plan

I altered a table that had sever columns defined as float to decimal. Now for some reason instead of using the index for it is using a sequential scan. I have updated the statistics, rebuilt the indexes and about everthing else I can think of. It simply refuses to use the index it did prior to the alter
Anyone have a clue as to what is going on?Is the comparison done against a variable or another column which is of the
float datatype? Float has higher datatype precedence, so the decimal need to
first be converted to float before that comparison can be performed which
prohibits the usage of index. If you code the code, or preferable a
simplified example that displays the behavior we might be able to comment...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...
> I altered a table that had sever columns defined as float to decimal. Now
for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.
> Anyone have a clue as to what is going on?|||Hi Redmund,
Are you comparing the column with a variable of data type float?
In that case, due to the rules of data type precedence, the decimal will be
implicitly converted into a float, and the implicit convert prevents the use
of an index on the column.
Change the variable to decimal as well.
--
Jacco Schalkwijk
SQL Server MVP
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...
> I altered a table that had sever columns defined as float to decimal. Now
for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.
> Anyone have a clue as to what is going on?|||The columns that were altered are NOT part of the index nor are they used in the criteria of the query.|||Hi,
Can you posts your table(s), indexes and query, so that we can study that?
--
Jacco Schalkwijk
SQL Server MVP
"RedMud" <anonymous@.discussions.microsoft.com> wrote in message
news:810E5CE2-29CE-490B-BFFD-5A58EA99048B@.microsoft.com...
> The columns that were altered are NOT part of the index nor are they used
in the criteria of the query.
>

Alter table changes optimizer query plan

I altered a table that had sever columns defined as float to decimal. Now f
or some reason instead of using the index for it is using a sequential scan.
I have updated the statistics, rebuilt the indexes and about everthing els
e I can think of. It simply
refuses to use the index it did prior to the alter.
Anyone have a clue as to what is going on?Is the comparison done against a variable or another column which is of the
float datatype? Float has higher datatype precedence, so the decimal need to
first be converted to float before that comparison can be performed which
prohibits the usage of index. If you code the code, or preferable a
simplified example that displays the behavior we might be able to comment...
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...

quote:

---

> I altered a table that had sever columns defined as float to decimal. Now

---

for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.

quote:

---

> Anyone have a clue as to what is going on?

---

|||Hi Redmund,
Are you comparing the column with a variable of data type float?
In that case, due to the rules of data type precedence, the decimal will be
implicitly converted into a float, and the implicit convert prevents the use
of an index on the column.
Change the variable to decimal as well.
Jacco Schalkwijk
SQL Server MVP
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...

quote:

---

> I altered a table that had sever columns defined as float to decimal. Now

---

for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.

quote:

---

> Anyone have a clue as to what is going on?

---

|||The columns that were altered are NOT part of the index nor are they used in
the criteria of the query.|||Hi,
Can you posts your table(s), indexes and query, so that we can study that?
Jacco Schalkwijk
SQL Server MVP
"RedMud" <anonymous@.discussions.microsoft.com> wrote in message
news:810E5CE2-29CE-490B-BFFD-5A58EA99048B@.microsoft.com...

quote:

---

> The columns that were altered are NOT part of the index nor are they used

---

in the criteria of the query.

quote:

---

>

---