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

Alignment problem with SQL Server 2000

Greeting
I have more than one language installed on my computer, and I tried to use my Enterprise Manager yesterday... What Im getting is with all the GUI im using Im having everything is Right Aligned....
Any help as to what I should change?
Thanks
This is my first post so would be great to have a qucik and handy answer :D??? :confused:

Can you attach a screenshot?

What country are you in?

blindman|||Here we go
Australia...I have English,Arabic,Japanese,Chinese installed|||Strange, in the Australian version everything is supposed to be upside-down, not reversed left-right.

.
.
.
Couldn't resist. :D

I'll see if I can find anything that might have caused this, but I've never seen it before.|||Dig hard man.. Its realy sucks to work with such alignment.|||I suspect this is a result of server settings on your platform.

What regional options do you have for your Locale and your Default Language?|||You mean like this ... go to control panel ... regional settings ... General tab ... and set english to default. :D ;) :p :).|||Nope,
Checked it now.. Hope it was the case.. I have English(Australia) as the default language... Im running XP if that helps...|||Originally posted by blindman
I suspect this is a result of server settings on your platform.

What regional options do you have for your Locale and your Default Language?
Hi,
Guess what you are right, its because Australia, hence the up side down :D Nah kidding
It wasnt the default language that wasnt set to English it was the Language for non-unicode Programs that wasnt set to English.
Cheers mate !!