Default language and dates

I've just moved servers - WK3 to WK3 - installed SQLserver 2005 and uploaded the database. Code base has not changed but now I'm getting

"String was not recognized as a valid DateTime.Couldn't store <21/07/2006>" . I suspect its to do with the default language , but although the default is English (United States) as in my last server, I have set the language in Advanced settings to be British English. I can't see any difference between the settings and my last server. I'm British BTW .

I also ran

EXEC @.ret=sp_defaultlanguage'sa','British English'

as the only login is sa . WK3 is itself set to English (United Kingdom) - I thought SQL 2005 would inherit this setting? Any help would be much appreciated.

ashaig:

"String was not recognized as a valid DateTime.Couldn't store <21/07/2006>" . I suspect its to do with the default language , but although the default is English (United States) as in my last server, I have set the language in Advanced settings to be British English. I can't see any difference between the settings and my last server. I'm British BTW .

Yes it may be related to the language setting. Actually there are some differences between English (United States) and British English, including date format. You can use the following command to check details of all language settings:

EXEC sp_helplanguage

From the result we can see the dateformat of us_en is mdy, while the dateformate of British is dmy.

ashaig:

as the only login is sa . WK3 is itself set to English (United Kingdom) - I thought SQL 2005 would inherit this setting? Any help would be much appreciated.

You can change the default language setting of a SQL instance by using such command:

EXEC sp_configure 'default language',0
reconfigure with override

Where 0 is the id of the language. Here are some useful links about the language setting in SQL Server:

Default Language option:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3xny.asp

sp_configure:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_0put.asp

sql

Default Contraint Problem

Hello,
I have 6 SQL Server in different locations running same applications. In a
table a column has a default contsraint, It works in 3 servers but the other
3 servers it does not work the column gets NULL value.
Any Idea?
Thanks in advance,
Erdal,Can you generate the SQL script for the table that works, the table that
doesn't work, and the insert statement used?
http://www.aspfaq.com/5006
"Erdal Akbulut" <erdalim21@.yahoo.com> wrote in message
news:eMosU6yoFHA.3552@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have 6 SQL Server in different locations running same applications. In
> a
> table a column has a default contsraint, It works in 3 servers but the
> other
> 3 servers it does not work the column gets NULL value.
> Any Idea?
> Thanks in advance,
>
> Erdal,
>
>|||This works
[CostV_LA] [numeric](21, 8) NULL CONSTRAINT [DF_tblOrderLines_CostV_LA]
DEFAULT (0),
This works too
[CostV_LA] [money] NULL CONSTRAINT [DF_tblOrderLines_CostV_LA] DEFAULT (0),
This does not.
[CostV_LA] [money] NULL CONSTRAINT [DF_tblOrderLines_CostV_LA] DEFAULT (0),
The strange thing the one that does not work today was working last month.
There is no sp updating this column.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23PM%23t9yoFHA.2080@.TK2MSFTNGP14.phx.gbl...
> Can you generate the SQL script for the table that works, the table that
> doesn't work, and the insert statement used?
> http://www.aspfaq.com/5006
>
>
> "Erdal Akbulut" <erdalim21@.yahoo.com> wrote in message
> news:eMosU6yoFHA.3552@.TK2MSFTNGP10.phx.gbl...
In
>|||> This works
> [CostV_LA] [numeric](21, 8) NULL CONSTRAINT [DF_tblOrderLines_CostV_LA]
> DEFAULT (0),
> This works too
> [CostV_LA] [money] NULL CONSTRAINT [DF_tblOrderLines_CostV_LA] DEFAULT
> (0),
>
> This does not.
> [CostV_LA] [money] NULL CONSTRAINT [DF_tblOrderLines_CostV_LA] DEFAULT
> (0),
Can you give the whole CREATE TABLE script? Also summarize any differences
between the two servers, e.g. @.@.version, regional settings, DBCC
USEROPTIONS. Also, if you don't want NULL to end up in the table, your
constraints should be:
[CostV_LA] [MONEY] NOT NULL CONSTRAINT [DF_tblOrderLines_CostV_LA] DEFAULT
(0),
Do you see why it's impossible to diagnose the problem from here? Imagine
me telling you, I have two cars in my driveway, and one of them doesn't
work. What's the problem? You ask for more information. I tell you, well,
they're both Chevy Novas. Does that help?
Are you beating your head against a wall yet? I am!

> There is no sp updating this column.
There must be an INSERT statement that causes the default to either work or
not (if you do not insert data into the row, how do you know the constraint
fails to work?).
A

Deciding which Database Server to install a new database

We have 3 Microsoft SQL 2000 servers. All of them have smiliar hardware configuration. I need to identify a database server to install a new database instance. I believe my decision should be based on available memory and processor workload at any given time. I need your help to identify the right server. Thank you.

Well, use perfmon to get a baseline from your servers and see how they are utilized. Make sure that you monitor a timespan which represents a common usage scenario. in addition make sure which licencse type your are using on the servers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Debug DB Merge Replication

I setted a configuration with 2 SQL 2000 servers, 1 publisher and 1
subscriber, to manage a merge replication of a DB, with the wizard of
enterprise manager. It seems that some tables published as arcticles
are not synchronized correctly. I'm a newbie using SQL Server 2000 and
I don't know how to proceed for finding errors and resolve problems;
someone can give me any indication?
Thanks.
Marco - when you say they are not synchronized properly can you please give
more details. Do you have rown existing on the publisher but not subscriber
or vice versa or something different entirely. There are several
posibilities involving bulk loads or compensating changes or the merge agent
failing somehow or conflict resolution. If you provide some more details we
can help narrow down the cause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul Ibison ha scritto:

> Marco - when you say they are not synchronized properly can you please give
> more details. Do you have rown existing on the publisher but not subscriber
> or vice versa or something different entirely. There are several
> posibilities involving bulk loads or compensating changes or the merge agent
> failing somehow or conflict resolution. If you provide some more details we
> can help narrow down the cause.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Hi Paul,
the situation is this: I have in a table 1 row which is different in
Subscriber from Publisher.
|||Assuming you have synchronized recently, there must have been a change
implemented without firing the merge triggers. Usuallt this is as a result
of a bulk insert, but in your case there seems to have been an update which
is odd, unless each record was added separately to the publisher and
subscriber as a part of a bulk process? Anyway, you could resort to
something like Lumigent's Log Explorer to see what is the root cause, and
use sp_mergedummyupdate to resync the row.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul Ibison ha scritto:

> Assuming you have synchronized recently, there must have been a change
> implemented without firing the merge triggers. Usuallt this is as a result
> of a bulk insert, but in your case there seems to have been an update which
> is odd, unless each record was added separately to the publisher and
> subscriber as a part of a bulk process? Anyway, you could resort to
> something like Lumigent's Log Explorer to see what is the root cause, and
> use sp_mergedummyupdate to resync the row.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Paul,
thanks for your suggestions.
Can you write the syntax to use the "sp_mergedummyupdate" ?
|||Hi Paul,
I'm not sure that there is only 1 row not updated. To be sure can I use
the sp_addtablecontents to force the update of all rows in my table?
Can you write an example of sintax for using this sp?
Thanks.
|||Marco,
the syntax is pretty straightforward:
exec sp_addtabletocontents
@.table_name ='yourtable',
@.owner_name = 'your owner eg dbo'
The @.owner_name is optional.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
last question.
Is the same thing to execute the sp in the Publisher or in the
Subscriber DB ?
|||Hi Marco,
I suppose it depends on what the required outcome is. If you have a row that
is different on the publisher and subscriber and which hasn't been logged as
a merge change, then there will be different results depending on where you
run the proc. By default, if you run it on the subscriber and publisher,
there will be a conflict and the publisher's change will win, otherwise
running it just on the pub will mean that the publisher's change gets
propagated and likewise for the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .