How can I change the default identity_range value in Merge replication

I have set up Merge Replication and everything is fine until a subscriber
goes offline for any length of time. The issue has to do with the setting of
the @.identity_range parameter for each article.
I am configuring the publication through the SQL Management Studio (server
is SQL 2005) and realise that I can run scripts and even alter the values
through the IDE.
What I want to do is change the default value that the IDE uses when it adds
the articles. The default seems to be 1000 which isn't really enough. We
would like to change the default to something like 10000
Does anybody know where SQL 2005 would store this value?
Thanks in advance,
Andrew Stanford
It is best that you not change meta data values in system tables. I'll
provide a way to change the indentiy range value for subscribers via the UI.
Since you already defined a merge publication, access the publication
properties by right clicking on the publication name under the
Replication:Local Publications node in SQL 2005 Management Studio. In the
left pane, select Articles. In the right pane, select the article you would
like to change the identity range values for. After highlighting the
article, use the Article Properties button. Scroll down to the identity
range management section and you should be able to change the Subscriber
Range Size value from 1000 to 10000. Hit OK, OK to confirm the changes.
Note that since you have an active merge publication already defined, the
subscriber will continue to use the old identity range values until they are
exhausted. The next time the merge agent requests a new ientitiy range from
the publisher, it will start using the new subscriber range value of 10000.
If you were to create a new publication, when you define the articles that
are in the publication you are able to drill into article properties and set
the subscriber range value to something other than 1000 up front.
Hope this helps,
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Stanford" <andrew.stanford@.bakertilly.co.uk> wrote in message
news:uUeZt4j2FHA.956@.TK2MSFTNGP10.phx.gbl...
>I have set up Merge Replication and everything is fine until a subscriber
>goes offline for any length of time. The issue has to do with the setting
>of the @.identity_range parameter for each article.
> I am configuring the publication through the SQL Management Studio (server
> is SQL 2005) and realise that I can run scripts and even alter the values
> through the IDE.
> What I want to do is change the default value that the IDE uses when it
> adds the articles. The default seems to be 1000 which isn't really enough.
> We would like to change the default to something like 10000
> Does anybody know where SQL 2005 would store this value?
>
> Thanks in advance,
> Andrew Stanford
>

How can I change “Language” setting using “Locale Identifier” of DBPromptInitialize dialog?

Hi,

I use pIDBPromptInitialize interface for establish a connection to MS SQL Server 2005.

SQL Server has “us_english” (LCID=1033) language as default setting.

I use the following part of code to charge LCID (from 1033 to 1049):

CDBPropSet ps3;

ps3.SetGUID(DBPROPSET_DBINIT );

ps3.AddProperty(DBPROP_INIT_LCID, (long)1049);

hr= pIDBProperties->SetProperties(1, &ps3);

hr= pIDBPromptInitialize->PromptDataSource(NULL, GetActiveWindow(),

DBPROMPTOPTIONS_PROPERTYSHEET,

0, NULL, (LPOLESTR)szFilter,

IID_IDBProperties, (IUnknown **)(&pIDBProperties));

...

hr = FDS->Connection->m_spInit->Initialize();

Then I connect to SQL Server successfully.

But SQL Server has LCID=1033 anyway!! Way I didn’t change it by my code? What is wrong?

But later I try to use some feature of LCID=1049 (date conversation)

How can I change “Language” setting to, for example, Russian (LCID=1049) using pIDBPromptInitialize

G.Can you elaborate on what do you do exactly? Which actions are you expecting to be affected by LCID setting?|||

Hi Anton,

I have MS SQL Server 2005 with LCID = 1033 (english).

As result the datetime format is mdy.

I have the table t1 with the following structure:

create table t1 ([Date] datetime not null, [d1] int);

which has the following data:

[Date] [d1]

--

2007-05-12 00:00:00.000 100

2007-05-13 00:00:00.000 200

2007-05-14 00:00:00.000 300

2007-05-15 00:00:00.000 400

Next, I have a OLEDB C++ client application, which makes and executes the following command:

CString cmd;

COleDateTime dt;

dt= COleDateTime::GetCurrentTime();

cmd.Format(L”select * from [t1] where [Date]= ‘%s’”, dt.Format(VAR_DATEVALUEONLY, LOCALE_USER_DEFAULT)

...

For me, the LOCALE_USER_DEFAULT value is 1049.

It is important to pay attention that LCID of MSSQL Server is 1033 and LCID of client application is 1049.

Next...

After formatting, my oledb command looks like this:

select * from [t1] where [Date]= ’14.05.2007’

and result of execution I get SQL Server error: convert is not possible. It is because MS SQL Server parse the ’14.05.2007’ date using not appropriate datetime formar.

My goal is to set LCID of SQL Server oledb connection as I need (in example above to 1049).

PS

I can’t use the ‘set dateformat dmy’ command.

PS2

Of cause, I could use the following command:

FLCID= 1033;

...

cmd.Format(L”select * from [t1] where [Date]= ‘%s’”, dt.Format(VAR_DATEVALUEONLY, FLCID);

...

But it isn’t my goal.

Best regards,

SGN

|||Could you try using a parameterized query? I think in that case the data will be passed to the server in a binary form if you provide a corresponding binding, so you could avoid a conversion to string.|||

Take a look at GetDateFormat function - http://msdn2.microsoft.com/en-us/library/ms776293.aspx

Hope this helps

|||

Hi,

Thank you for your replay!

Regarding usage of the GetDateFormat function I have another question.

It concern not only SQL Server but ALL OLEDB datasources.

How can I know the LCID of OLEDB datasource which I can use as datastorage?

Is it possible to get current LCID of OLEDB datasource using OLEDB functionality only?

Thank you for your help!

Best regards,

SGN

|||

I'm not sure if there is a generic OLEDB way.

Session language for the SQL Server is a provider specific property SSPROP_INIT_CURRENTLANGUAGE.

http://msdn2.microsoft.com/en-us/library/ms142797.aspx

For sqlserver you can also change the language by executing sp_configure, and the list of supported languages can be produced by sp_helplanguage. Default language for the login can alos be overriden at hte session level by executing SET LANGUAGE.

How can I change “Language” setting using “Locale Identifier” of DBPromptInitialize dialog?

Hi,

I use pIDBPromptInitialize interface for establish a connection to MS SQL Server 2005.

SQL Server has “us_english” (LCID=1033) language as default setting.

I use the following part of code to charge LCID (from 1033 to 1049):

CDBPropSet ps3;

ps3.SetGUID(DBPROPSET_DBINIT );

ps3.AddProperty(DBPROP_INIT_LCID, (long)1049);

hr= pIDBProperties->SetProperties(1, &ps3);

hr= pIDBPromptInitialize->PromptDataSource(NULL, GetActiveWindow(),

DBPROMPTOPTIONS_PROPERTYSHEET,

0, NULL, (LPOLESTR)szFilter,

IID_IDBProperties, (IUnknown **)(&pIDBProperties));

...

hr = FDS->Connection->m_spInit->Initialize();

Then I connect to SQL Server successfully.

But SQL Server has LCID=1033 anyway!! Way I didn’t change it by my code? What is wrong?

But later I try to use some feature of LCID=1049 (date conversation)

How can I change “Language” setting to, for example, Russian (LCID=1049) using pIDBPromptInitialize

G.Can you elaborate on what do you do exactly? Which actions are you expecting to be affected by LCID setting?|||

Hi Anton,

I have MS SQL Server 2005 with LCID = 1033 (english).

As result the datetime format is mdy.

I have the table t1 with the following structure:

create table t1 ([Date] datetime not null, [d1] int);

which has the following data:

[Date] [d1]

--

2007-05-12 00:00:00.000 100

2007-05-13 00:00:00.000 200

2007-05-14 00:00:00.000 300

2007-05-15 00:00:00.000 400

Next, I have a OLEDB C++ client application, which makes and executes the following command:

CString cmd;

COleDateTime dt;

dt= COleDateTime::GetCurrentTime();

cmd.Format(L”select * from [t1] where [Date]= ‘%s’”, dt.Format(VAR_DATEVALUEONLY, LOCALE_USER_DEFAULT)

...

For me, the LOCALE_USER_DEFAULT value is 1049.

It is important to pay attention that LCID of MSSQL Server is 1033 and LCID of client application is 1049.

Next...

After formatting, my oledb command looks like this:

select * from [t1] where [Date]= ’14.05.2007’

and result of execution I get SQL Server error: convert is not possible. It is because MS SQL Server parse the ’14.05.2007’ date using not appropriate datetime formar.

My goal is to set LCID of SQL Server oledb connection as I need (in example above to 1049).

PS

I can’t use the ‘set dateformat dmy’ command.

PS2

Of cause, I could use the following command:

FLCID= 1033;

...

cmd.Format(L”select * from [t1] where [Date]= ‘%s’”, dt.Format(VAR_DATEVALUEONLY, FLCID);

...

But it isn’t my goal.

Best regards,

SGN

|||Could you try using a parameterized query? I think in that case the data will be passed to the server in a binary form if you provide a corresponding binding, so you could avoid a conversion to string.|||

Take a look at GetDateFormat function - http://msdn2.microsoft.com/en-us/library/ms776293.aspx

Hope this helps

|||

Hi,

Thank you for your replay!

Regarding usage of the GetDateFormat function I have another question.

It concern not only SQL Server but ALL OLEDB datasources.

How can I know the LCID of OLEDB datasource which I can use as datastorage?

Is it possible to get current LCID of OLEDB datasource using OLEDB functionality only?

Thank you for your help!

Best regards,

SGN

|||

I'm not sure if there is a generic OLEDB way.

Session language for the SQL Server is a provider specific property SSPROP_INIT_CURRENTLANGUAGE.

http://msdn2.microsoft.com/en-us/library/ms142797.aspx

For sqlserver you can also change the language by executing sp_configure, and the list of supported languages can be produced by sp_helplanguage. Default language for the login can alos be overriden at hte session level by executing SET LANGUAGE.

sql

How can client applications know if a table has been changed by another user?

Is there a mechanism in SQL Server 2000 for notifying client apps in a
multiuser setting when a change has occurred in a table (notification
event).
And/Or, is there a way a client application can 'ask' if a table has
changed?
Thanks,
WykDo you want to know whether that table design is changed or records are
added?
Madhivanan|||In both the cases you have to create your own extensions
to handle the requirement.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1109581918.952377.73540@.z14g2000cwz.googlegroups.com...
> Do you want to know whether that table design is changed or records are
> added?
> Madhivanan
>|||It seems like you are describing an optimistic locking strategy. SQL
Server provides a ROWVERSION datatype (also called TIMESTAMP) to track
changes to a row. The client retrieves the data, including timestamp
and then, immediately before saving a change or performing other
actions, the retrieved timestamp is compared to the current one in the
table to determine if the data has changed.
David Portas
SQL Server MVP
--|||> ...The client retrieves the data, including timestamp
> and then, immediately before saving a change or performing other
> actions, the retrieved timestamp is compared to the current one in the
> table to determine if the data has changed.
Or, in the UPDATE, you include the buffered rowversion value in the WHERE cl
ause (with the primary
key value). If the update modifies zero rows, you know that either the row w
as deleted or it was
updated.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109586059.793247.265620@.f14g2000cwb.googlegroups.com...
> It seems like you are describing an optimistic locking strategy. SQL
> Server provides a ROWVERSION datatype (also called TIMESTAMP) to track
> changes to a row. The client retrieves the data, including timestamp
> and then, immediately before saving a change or performing other
> actions, the retrieved timestamp is compared to the current one in the
> table to determine if the data has changed.
> --
> David Portas
> SQL Server MVP
> --
>|||LOL. First time I noticed your signature, Joe, I thought that SQLNS referred
to the SQL-NS API with
which you can re-use dialogs from EM in your client app. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe Webb" <joew@.webbtechsolutions.com> wrote in message
news:uT5aNdaHFHA.3108@.tk2msftngp13.phx.gbl...
> If you're asking about a way in which a client application can be notified
of new or even updated
> records on the server, then you may want to check out Notification Service
s. The provided Realtor
> sample provides a simple example.
>
> HTH...
> Joe Webb
> SQL Server MVP
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/t...il/-/0972688811
>
>
> Tibor Karaszi wrote:

How block the insertion of NULLS in SQL Server 2000?

Hi,
How block the insertion of NULLS in SQL Server 2000'
I know that in Ms-Access 2000 there is this option in the setting options,
but I could not find in SQL Server 2000.
Thanks,
--
Vilmar Brazão de OliveiraVilmar,
Use NOT NULL, as in
CREATE TABLE validvalue
(
value VARCHAR(20) NOT NULL
)
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Vilmar" <xx1122@.x1212x.com.jc> wrote in message
news:OddUs9KKEHA.2024@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How block the insertion of NULLS in SQL Server 2000'
> I know that in Ms-Access 2000 there is this option in the setting options,
> but I could not find in SQL Server 2000.
> Thanks,
> --
>
> Vilmar Brazão de Oliveira
>|||CREATE TABLE <tablename>
(<columnname> <datatype> [NOT] NULL)
So for example, this allows NULLs:
CREATE TABLE A
(B int NULL)
...and this disallows them:
CREATE TABLE A
(B int NOT NULL)
"Vilmar" <xx1122@.x1212x.com.jc> wrote in message
news:OddUs9KKEHA.2024@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How block the insertion of NULLS in SQL Server 2000'
> I know that in Ms-Access 2000 there is this option in the setting options,
> but I could not find in SQL Server 2000.
> Thanks,
> --
>
> Vilmar Brazão de Oliveira
>

How block the insertion of NULLS in SQL Server 2000?

Hi,
How block the insertion of NULLS in SQL Server 2000?
I know that in Ms-Access 2000 there is this option in the setting options,
but I could not find in SQL Server 2000.
Thanks,
Vilmar Brazo de Oliveira
Vilmar,
Use NOT NULL, as in
CREATE TABLE validvalue
(
value VARCHAR(20) NOT NULL
)
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Vilmar" <xx1122@.x1212x.com.jc> wrote in message
news:OddUs9KKEHA.2024@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How block the insertion of NULLS in SQL Server 2000?
> I know that in Ms-Access 2000 there is this option in the setting options,
> but I could not find in SQL Server 2000.
> Thanks,
> --
>
> Vilmar Brazo de Oliveira
>
|||CREATE TABLE <tablename>
(<columnname> <datatype> [NOT] NULL)
So for example, this allows NULLs:
CREATE TABLE A
(B int NULL)
...and this disallows them:
CREATE TABLE A
(B int NOT NULL)
"Vilmar" <xx1122@.x1212x.com.jc> wrote in message
news:OddUs9KKEHA.2024@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How block the insertion of NULLS in SQL Server 2000?
> I know that in Ms-Access 2000 there is this option in the setting options,
> but I could not find in SQL Server 2000.
> Thanks,
> --
>
> Vilmar Brazo de Oliveira
>

How block the insertion of NULLS in SQL Server 2000?

Hi,
How block the insertion of NULLS in SQL Server 2000'
I know that in Ms-Access 2000 there is this option in the setting options,
but I could not find in SQL Server 2000.
Thanks,
--
Vilmar Brazo de OliveiraVilmar,
Use NOT NULL, as in
CREATE TABLE validvalue
(
value VARCHAR(20) NOT NULL
)
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Vilmar" <xx1122@.x1212x.com.jc> wrote in message
news:OddUs9KKEHA.2024@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How block the insertion of NULLS in SQL Server 2000'
> I know that in Ms-Access 2000 there is this option in the setting options,
> but I could not find in SQL Server 2000.
> Thanks,
> --
>
> Vilmar Brazo de Oliveira
>|||CREATE TABLE <tablename>
(<columnname> <datatype> [NOT] NULL)
So for example, this allows NULLs:
CREATE TABLE A
(B int NULL)
...and this disallows them:
CREATE TABLE A
(B int NOT NULL)
"Vilmar" <xx1122@.x1212x.com.jc> wrote in message
news:OddUs9KKEHA.2024@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How block the insertion of NULLS in SQL Server 2000'
> I know that in Ms-Access 2000 there is this option in the setting options,
> but I could not find in SQL Server 2000.
> Thanks,
> --
>
> Vilmar Brazo de Oliveira
>

How are the aggregate details being calculated on a click through report?

Hi -

I need help figuring out what setting I need to tweak to get the correct calculations for the default aggregate attributes for the related entities of the one I am drilling into. Right now it is calculating the total across all for every row and not slicing by sub-customer.

Example:

I have a customer with a one-to-many relationship to incidents. Both have a count aggregate that is part of the default aggregates for the entity. There are 58 rows in my table. If I run a report with CustomerName and #Incidents, I correctly get different sub totals for each customer, totalling to 58 for the grand total. However, if I run a summary report on customers and drill into the customers using click through, the #Incidents is displayed but it is 58 for all customers - every row.

If I go into the defaultDetailAttributes of the Customer and add the #Incidents to it and run the previous test, then the correct number of incidents are shown for the customer, then the incorrect number of incidents follow (from getting the aggregates from the children).

The query generated is huge and I am sure it has something to do with my OptionalMany relationships between the tables, but I can't understand why...

Can anyone help me out?

Thanks in advance,

Toni Fielder

I had a similar issue from before. I'm assuming you are using multiple datasets in this one table? I really struggled to find another way to dish this stuff out correctly, but the only efficient way I could get what I wanted (the same thing you are looking for) was to create a giant stored procedure combining the tables I was planning on using. Another way I found (the less efficient way) was to create subreports for each subcategory and call the information into the subreports that each subcategory was grouped by. This way will really increase the amount of time your report will be rendering for, but if you cannot do a stored procedure then this may be your only choice. Hope I could help.|||

Thanks for the message - I think I have a bit of a different situation.... I am constrained by using models and report builder - but I think custom click-through reports might be the equivalent of what you had to do.... unfortunately this has to be a generic model that is sold to multiple customers so custom click through reports has to be my last choice....

I now have another question that is related. I used my smallest object to try and figure out how this stuff is working. I changed all it's relationships (incorrectly) to One-One so it had no many relationships (or optional) from which to grab other aggregates. And I am getting all manner of aggregates from different entities in my model that really have nothing to do with my base entity. It has to be getting them from some extended relationship (all entities are interrelated - like they all share the users table under the covers to get Insert/Update information) but I am thoroughly confused at the moment.

Thanks for the help everyone.