Alter table problem

Greetings !
I can not believe that no one had this problem.
I have tested on Windows 2000 Advanced Server, and Windows 2003 Server
(Standard Edition)
If you install Hotfix KB 810185, you will not be able to change design of
your tables on your databases.
After reinstalation of SQL Server EE and applying Service Pack 3a, and
without applying Hotfix this problem disappear.
Regards Gj.What exactly is not working after applying that hotfix? Can you post the
commands that are failing? Any error messages? Or are you trying to alter
tables from Enterprise Manager and getting errors?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dariviko" <dariviko@.hotmail.com> wrote in message
news:c0ttp0$506$1@.sunce.iskon.hr...
Greetings !
I can not believe that no one had this problem.
I have tested on Windows 2000 Advanced Server, and Windows 2003 Server
(Standard Edition)
If you install Hotfix KB 810185, you will not be able to change design of
your tables on your databases.
After reinstalation of SQL Server EE and applying Service Pack 3a, and
without applying Hotfix this problem disappear.
Regards Gj.|||I can not find that KB article on MS website

Alter table problem

Greetings !
I can not believe that no one had this problem.
I have tested on Windows 2000 Advanced Server, and Windows 2003 Server
(Standard Edition)
If you install Hotfix KB 810185, you will not be able to change design of
your tables on your databases.
After reinstalation of SQL Server EE and applying Service Pack 3a, and
without applying Hotfix this problem disappear.
Regards Gj.What exactly is not working after applying that hotfix? Can you post the
commands that are failing? Any error messages? Or are you trying to alter
tables from Enterprise Manager and getting errors?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dariviko" <dariviko@.hotmail.com> wrote in message
news:c0ttp0$506$1@.sunce.iskon.hr...
Greetings !
I can not believe that no one had this problem.
I have tested on Windows 2000 Advanced Server, and Windows 2003 Server
(Standard Edition)
If you install Hotfix KB 810185, you will not be able to change design of
your tables on your databases.
After reinstalation of SQL Server EE and applying Service Pack 3a, and
without applying Hotfix this problem disappear.
Regards Gj.|||I can not find that KB article on MS website

Allowing users to edit database

I'm relatively new to administering SQL Server, hence this basic question.

What are the standard practice tool(s)/methods for allowing users access to a database to edit data? In this case, these are engineers (not programmers) that need to edit some values. The practice in the past has been to install Enterprise Manager on their PC. However, this does not seem proper to me and I was thinking more along the lines of having them use Access instead.

I compare Enterpise Manager in SQL Server as an equilvalent to Enterprise Manager in Oracle - these are tools only for use by database administrators. Is this a correct correlation, or is Enterpise Manager in SQL Server a tool that users (and programmers?) are commonly allowed to use? In the case of common users, I would think not.

Thanks in advance.ideally you want to disallow direct access to the tables and you would have an application that solely uses stored procedures and views to access data and having users doing data entry in the EM is anathema. You can do this with Access although a lot of access developers do not do it well.|||Thanks much! This helps confirm what I thought.|||I have my databases on SQL Server with Access on the front end for users and they get Read only ability, some get read and write (the ones that have been properly trained)
Though I still have allot to learn

Allocate Range of AutoNumber ID's to each user

I have a non standard requirement that about 5 different users from differen
t
departments require sequential numbering for there records, however I want
all records to be in the one central table. This comes about as each
departments records ID's have a 4 letter acronym before the AutoNumber ID.
Is this possible or should I create seperate tables and then join them using
a view with union queries?
Any suggestions would be greatly appreciated.
ThanksHi
You can use INSTEAD OF TRIGGER to achieve this business logic
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:

> I have a non standard requirement that about 5 different users from differ
ent
> departments require sequential numbering for there records, however I want
> all records to be in the one central table. This comes about as each
> departments records ID's have a 4 letter acronym before the AutoNumber ID.
> Is this possible or should I create seperate tables and then join them usi
ng
> a view with union queries?
> Any suggestions would be greatly appreciated.
> Thanks|||Hi,
Not sure how to use this, do you have a link or sample I could work from.
Much appreciated.
"Chandra" wrote:
> Hi
> You can use INSTEAD OF TRIGGER to achieve this business logic
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "MarkCapo" wrote:
>|||Hi,
You can try this way
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE>
INSTEAD OF INSERT
AS
INSERT INTO <TABLE>
SELECT <your logic>, required columns
FROM INSERTED
WHERE INSERTED.Key = Key
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:
> Hi,
> Not sure how to use this, do you have a link or sample I could work from.
> Much appreciated.
> "Chandra" wrote:
>|||Thanks.
So best to set up my 5 tables which fulfills the business requirement and
then uses 'Instead of Triggers' to generate a master table with all results.
The only other query I have is how does the key part of the query work.
Thanks
"Chandra" wrote:
> Hi,
> You can try this way
> CREATE TRIGGER <TRIGGER_NAME>
> ON <TABLE>
> INSTEAD OF INSERT
> AS
> INSERT INTO <TABLE>
> SELECT <your logic>, required columns
> FROM INSERTED
> WHERE INSERTED.Key = Key
> GO
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "MarkCapo" wrote:
>|||Key is the primary key value in the table. If you are sure that you will be
inserting only one row at a time, then you can avoiding the key.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:
> Thanks.
> So best to set up my 5 tables which fulfills the business requirement and
> then uses 'Instead of Triggers' to generate a master table with all result
s.
> The only other query I have is how does the key part of the query work.
> Thanks
> "Chandra" wrote:
>|||First, do not go with Chandra's advice of an INSTEAD OF TRIGGER -- that make
s
no sense whatsoever. This is NOT a "non standard" requirement and is is quit
e
common -- hiding the business logic in an INSTEAD OF TRIGGER will create a
mess of a system. This requirement is known as pre-allocated numbers. Most
banks I know do this.
I don't know what the numbers are for, so I'll assume Orders. Here's how
it's typically done:
TABLE Orders ( Order_Num, Cust_Num, Order_Date, ... )
TABLE Avaiable_Order_Numbers ( Order_Num, Dept_Id )
PROCEDURE Create_New_Order (@.Dept_Id,...) {
BEGIN TRANSACTION
@.Ord_Num =
SELECT Order_Num
FROM Avaiable_Order_Numbers
WHERE Dept_Id = @.Dept_Id
INSERT INTO Orders (@.Ord_Num, ...)
DELETE FROM Avaiable_Order_Numbers
WERE Order_Num = @.Order_Num
COMMIT
}
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MarkCapo" wrote:

> I have a non standard requirement that about 5 different users from differ
ent
> departments require sequential numbering for there records, however I want
> all records to be in the one central table. This comes about as each
> departments records ID's have a 4 letter acronym before the AutoNumber ID.
> Is this possible or should I create seperate tables and then join them usi
ng
> a view with union queries?
> Any suggestions would be greatly appreciated.
> Thanks|||Thanks for your assistance Alex. Looked at the Instead of Trigger option an
d
it seemed messy!
Basically if you use 5 tables one for each department with there own
sequential identity value incrementing concatenated with the Dept. acronym
and then put a trigger on Insert, Update (There is no delete facility) to
build a consolidated/master table for analysis, this will provide a robust
solution.
Any comments appreciated!
"Alex Papadimoulis" wrote:
> First, do not go with Chandra's advice of an INSTEAD OF TRIGGER -- that ma
kes
> no sense whatsoever. This is NOT a "non standard" requirement and is is qu
ite
> common -- hiding the business logic in an INSTEAD OF TRIGGER will create a
> mess of a system. This requirement is known as pre-allocated numbers. Most
> banks I know do this.
> I don't know what the numbers are for, so I'll assume Orders. Here's how
> it's typically done:
> TABLE Orders ( Order_Num, Cust_Num, Order_Date, ... )
> TABLE Avaiable_Order_Numbers ( Order_Num, Dept_Id )
> PROCEDURE Create_New_Order (@.Dept_Id,...) {
> BEGIN TRANSACTION
> @.Ord_Num =
> SELECT Order_Num
> FROM Avaiable_Order_Numbers
> WHERE Dept_Id = @.Dept_Id
> INSERT INTO Orders (@.Ord_Num, ...)
> DELETE FROM Avaiable_Order_Numbers
> WERE Order_Num = @.Order_Num
> COMMIT
> }
>
> --
> Alex Papadimoulis
> http://weblogs.asp.net/Alex_Papadimoulis
>
> "MarkCapo" wrote:
>