ALTER TABLE problem

Hiya all,

Im doing a system tool application. The app has the functionality to edit and change language strings that other applicaction uses.

The table look as following:

STRING_ID English Swedish
----------
100 Cancel Avbryt
101 Apply Verst'a'll

STRING_ID : Int, NOT NULL, Primary key
English : NVARCHAR(512), NOT NULL
Swedish : NVARCHAR(512), NOT NULL

In my tool you're able to add new strings.

Now I want to be able to add languages by adding new columns to the table.

Example:

STRING_ID English Swedish Arabic
------------
100 Cancel Avbryt <Cancel in arabic>
101 Apply Verst'a'll <Apply in arabic>

I've made a Stored Procedure looking like following:

CREATE PROC My_sp_AddNewLanguage
@.NewLanguageName nvarchar(512), @.RetrievalMsg nvarchar(255) OUTPUT
AS
-- Find out how many rows that should be affected when altering the table
DECLARE @.NrOfRows integer
SELECT * FROM String_Resource
SET @.NrOfRows = @.@.ROWCOUNT

ALTER TABLE String_Resource
ADD @.NewLanguageName NVARCHAR(512) NOT NULL
DEFAULT ('')
IF @.@.ROWCOUNT <> @.NrOfRows
BEGIN
SET @.RetrievalMsg = 'Unable to Add New Language'
RETURN 8301 -- 8301 is something I've defined in my code
END
SET @.RetrievalMsg = 'Your new Language has now been added'
RETURN 0
GO

But I cant seem to do this because of the @.NewLanguageName in the following row:

ALTER TABLE String_Resource
ADD @.NewLanguageName NVARCHAR(512) NOT NULL
DEFAULT ('')

So my question is: How can you add a column to the Table using a variable @.Variable that contains the name of the new column?

OR

Can anybody tell me how I can write DEFAULT('') into a NVARCHAR variable since in Store Procedure Strings are using the '-sign and the DEFAULT ('') expression has those signs in it.

I've tried doing @.SQLQuery = N'ALTER TABLE String_Resource ADD ' + @.NewLanguageName + ' NVARCHAR(512) NOT NULL DEFAULT('')'

But it doesnt work since the DEFAULT('') expression screws up the string.

Thanks for your time,
FarekYou've got a really good idea, but I think you are going about it all wrong!

Take a look at the master.dbo.sysmessages table. It is designed to do exactly what you are trying to do. The secret is to have multiple rows with the same message id, but only one row per language. By adding rows to the language table, you can then add new rows to the message table for that language, and you are on your way!

The syntax should go something like:CREATE TABLE tLanguage (
languageId INT IDENTITY
CONSTRAINT XPKtLanguage
PRIMARY KEY (languageId)
, name NVARCHAR(25) NOT NULL
)
GO

CREATE TABLE tMessage (
languageId INT NOT NULL
CONSTRAINT XFK01tMessage
FOREIGN KEY (languageId)
REFERENCES tLanguage (languageId)
, messageId INT NOT NULL
CONSTRAINT XPKtMessage
PRIMARY KEY (languageId, messageId)
, message NVARCHAR(50) NOT NULL
)
GO-PatPsql

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