MS SQL Server Merge Replication

Hi, I have two databases on MS. SQL Server 200 the first one is the
main DB and the second one is a replica of it, both of them in
different territories .I connect them using merge replication just
when I need to merge the data.

The problem is when I make import data or export data from any
location (MS access ,MS SQL server ) to the second one (subscriber DB)
or the main DB(publisher and distributor ) ,the imported data does
not replicated to the other DB ?

Normal transaction replicated smoothly.

I hope you can help me.

Thanks in advance.

Mahmoud Khebaisa.mahmod79@.hotmail.com (Mahmoud KHEBAISA) wrote in message news:<ce543867.0408190341.2cd2244@.posting.google.com>...
> Hi, I have two databases on MS. SQL Server 200 the first one is the
> main DB and the second one is a replica of it, both of them in
> different territories .I connect them using merge replication just
> when I need to merge the data.
> The problem is when I make import data or export data from any
> location (MS access ,MS SQL server ) to the second one (subscriber DB)
> or the main DB(publisher and distributor ) ,the imported data does
> not replicated to the other DB ?
> Normal transaction replicated smoothly.
> I hope you can help me.
> Thanks in advance.
> Mahmoud Khebaisa.

You might want to post this in microsoft.public.sqlserver.replication
to get a better answer. You should probably also mention exactly how
you are importing the data which are not replicated, eg. DTS, bcp.exe,
a custom tool etc.

Simon

MS Sql Server and Windows 2000/XP

Hi, I have a problem.

I have two computers (clients), one with Windows 2000 and second with
Windows XP. And a third computer (server) with a Windows 2000 and MS Sql
Server 2000.

I have a TABLE with two rows: 'row' and 'row ' (space).

And SQL: 'select * from TABLE where row = 'row'

If I run this SQL on computer with Windows 2000 it works ok (return only one
row: 'row').
If I run this SQL on computer with Windows XP it works really strange
(return two rows: 'row' and 'row ' ) - it can't see this spaces.

Is there differences between MS Sql Server on W2000 and WXP?

Thanks for the help

Magdaslonk0 (slonk0_usun_to@.wp.pl) writes:
> I have two computers (clients), one with Windows 2000 and second with
> Windows XP. And a third computer (server) with a Windows 2000 and MS Sql
> Server 2000.
> I have a TABLE with two rows: 'row' and 'row ' (space).
> And SQL: 'select * from TABLE where row = 'row'
> If I run this SQL on computer with Windows 2000 it works ok (return only
> one row: 'row').
> If I run this SQL on computer with Windows XP it works really strange
> (return two rows: 'row' and 'row ' ) - it can't see this spaces.
> Is there differences between MS Sql Server on W2000 and WXP?

No, there isn't. There are however settings that you can change with
the SET command that may affect this. However, I am at loss to see
how you ever could get only one row back. You should always get two
back, because SQL does not care about trailing spaces when comparing.

Could you post a repro which demonstrates the problem? Such a repro
would have to create the table, insert the data, and then select.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> how you ever could get only one row back. You should always get two
> back, because SQL does not care about trailing spaces when comparing.

Sorry, it was my mistake. You are right, SQL does not care about trailing
spaces when comparing.

Thanks for your help.
Magda

MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or two

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
ID - nvarchar length-9
COMPANY - nvarchar length-30
NOTES - nvarchar length-250
Select * from database
where NOTES like '%something%'
Is there a way to get results from this query in less then 1-2 second
and how?
Maybe. If possible begin the % with a leading character and try creating a
NC index on the NOTES column. Or might also consider creating a Full-Text
index on the NOTES column and then use CONTAINS or FREETEXT.
HTH
Jerry
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306144.751022.186010@.o13g2000cwo.googlegr oups.com...
>
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>
|||Thanks.

MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or two

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
ID - nvarchar lenth-9
COMPANY - nvarchar lenth-30
NOTES - nvarchar length-250

Select * from database
where NOTES like '%something%'

Is there a way to get results from this query in less then 1-2 second
and how?Hello
I think that using like it would be impossible.
You will get better results when you use full text search (read about it in
books online), however I have not experience with looking for a phrase, but
with single words it works fast.
Alwik

> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ID - nvarchar lenth-9
> COMPANY - nvarchar lenth-30
> NOTES - nvarchar length-250
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?|||On a Athon3200+ (32 bits) home computer
it takes 1692ms to search something like '%RRIDA%' on 393951 rows
table. The maximum length of a row is 3576 bytes. So you only need a
faster CPU and faster memory controler and enough memory to hold the
data pages in memory to achive subsecond time. But IMHO I think this
kind of search is a nonsense for this number of rows.|||So what kind of search are you recommend?|||Am 14 Oct 2005 12:33:10 -0700 schrieb nydefender:

> So what kind of search are you recommend?

What hardware do you use? And how long does it last to get the result? Have
you tried it with an index on NOTES? And i think, a second search should be
much faster then the first one. If you always search on NOTES maybe you can
hold a second table with only PK and field NOTES, which is redundant
(managed by triggers) but can be pinned into memory (DBCC PINTABLE() -
maybe a silly idea, only brainstorming).
Sometimes i have the same problem to find some records out of a big table
where it lasts up to 30 seconds. At first the user knows from
training/docu, that this could need a "long" time to proceed, second i show
a window with a wait-message and something blinking in it, so the user has
not the feeling that the program hangs.

bye,
Helmut|||helmut woess (hw@.iis.at) writes:
> (managed by triggers) but can be pinned into memory (DBCC PINTABLE() -
> maybe a silly idea, only brainstorming).

Yes, DBCC PINTABLE was really a silly idea of Microsoft/Sybase. (Don't
really know who came up with it.) So silly, that in fact in SQL 2005, the
command DBCC PINTABLE is a no-op that performs nothing.

If a table is referenced often enough, it will be in cache anyway, so
PINTABLE has no effect. But if you pin a large table of which only portions
are referenced with some frequency, this means that you are wasting memory
that could have been used for other table, and thus degrade performance.

The only point I can see with PINTABLE is that you have table that you
query so rarely, that it will fall out of the cache. But when you need to
query it, you need the answers snap.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You want subsecond performance for your query. And the query can return
thousands of rows. How many time the clerk will spend searching for the
correct row?. subsecond querys are needed for routine operations and
they return only the necessary information to do the task, if not, the
worker is wasting his time. When you look for %something%, do you
really know what you are looking for?

In an hospitalizaton patient table, if I look for %seropositive% in the
observations field or even for %positive% I'm pretty sure its for a
report or an adhoc decission suport query and this doesn't need
subsecond response time. SQL Server is an OLTP system, designed for a
lot of small transactions, and this kind of queries is an incorrect use
of the system in my opinion.

You sould use something like Microsoft Search Service or a similar
product.|||Maybe this kind of query is "incorect" but is necessary. Now this query
takes for about 15-20 secs. I try to find a better way. I will try with
full text search.
Thanks to all of you.

MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or two

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
ID - nvarchar length-9
COMPANY - nvarchar length-30
NOTES - nvarchar length-250
Select * from database
where NOTES like '%something%'
Is there a way to get results from this query in less then 1-2 second
and how?
Full Text index this table and run a full population.
The query would look like this
select * from database where contains(NOTES,'something')
Use the wizard to build the FTS index on your table and make sure you run a
full population.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<scgwebmaster@.yahoo.com> wrote in message
news:1129307133.782947.259880@.f14g2000cwb.googlegr oups.com...
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>

MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or two

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
---
ID - nvarchar length-9
COMPANY - nvarchar length-30
NOTES - nvarchar length-250
----
Select * from database
where NOTES like '%something%'
Is there a way to get results from this query in less then 1-2 second
and how?Maybe. If possible begin the % with a leading character and try creating a
NC index on the NOTES column. Or might also consider creating a Full-Text
index on the NOTES column and then use CONTAINS or FREETEXT.
HTH
Jerry
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306144.751022.186010@.o13g2000cwo.googlegroups.com...
>
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ---
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>|||Thanks.

MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or two

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
---
ID - nvarchar length-9
COMPANY - nvarchar length-30
NOTES - nvarchar length-250
----
Select * from database
where NOTES like '%something%'
Is there a way to get results from this query in less then 1-2 second
and how?Maybe. If possible begin the % with a leading character and try creating a
NC index on the NOTES column. Or might also consider creating a Full-Text
index on the NOTES column and then use CONTAINS or FREETEXT.
HTH
Jerry
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306144.751022.186010@.o13g2000cwo.googlegroups.com...
>
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ---
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>|||Thanks.sql

MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or tw

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
---
ID - nvarchar length-9
COMPANY - nvarchar length-30
NOTES - nvarchar length-250
----
Select * from database
where NOTES like '%something%'
Is there a way to get results from this query in less then 1-2 second
and how?You need to use a Full Text Index to do that kind of search quickly. Look
it up in Books Online.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306366.597973.143180@.g43g2000cwa.googlegroups.com...
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ---
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>|||Pattern matched searching when the wild card is prefixed to the parameter
cannot use an index and so in most cases, a table scan in employed. If this
is something critical, you might want to look into full text indexing
options.
If you know the pattern upfront, one trick you can use like create a
computed column representing the part of the string and indexing the column.
Anith|||To begin with, insure that NOTES is indexed.
http://www.microsoft.com/technet/pr...s/c0618260.mspx
Performing a LIKE search on '%something%' will not efficeintly utilize an
index on NOTES, however, 'something%' would.
http://msdn.microsoft.com/library/d...dcharacters.asp
If you need to perform fast 'wildcard' type searches, then consider
implemeting Index Server and full-text search. It is a service that runs
along side SQL Server. Just remember that the predicates CONTAINS and
FREETEXT are used for free-text searches, so it will involve making
revisions to some of your queries.
http://msdn.microsoft.com/library/d...r />
_3rqg.asp
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306366.597973.143180@.g43g2000cwa.googlegroups.com...
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ---
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>|||Thanks

MS SQL Server 2000 - Search a table with 250,000+ records in less then a second

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
----
ID - nvarchar length-9
COMPANY - nvarchar length-30
NOTES - nvarchar length-250
----
Select * from database
where NOTES like '%something%'
----
Is there a way to get results from this query in less then 1-2 second
and how?A few posts below is a remarkably similar question, only the table has
300,000+ rows. I think it applies to your case as well.
<scgwebmaster@.yahoo.com> wrote in message
news:1129305896.814659.100590@.g43g2000cwa.googlegroups.com...
>I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ----
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> ----
> Is there a way to get results from this query in less then 1-2 second
> and how?
>

MS SQL 2005, .NET, logins/sec and page faults

Hi, all.

We have a couple of pathological sql servers that have lots and lots of
page faults per second, up to 4000. Our client programs are written in
C#/.NET 1.1 and utilizes connection pooling.

Some of the client programs seems to log in hundred of times per
second, as reported by perfmon->.SQLServer:General
Statistics->Logins/sec. Stopping the client programs reduces that
number significantly.

We've done code reviews of the client programs and they look OK.
Monitoring .NET connections&pools does not show anything suspicicous.

We're currently rewriting the clients to use one db connection instead
of the pools, but that takes some time and may introduce bugs. Does
anyone know why we have these problems and/or why logins/sec is so
high? I'm thinking "bugs in the .NET client", but really have no
idea...

One thought I had was that the Page Faults reported for sqlsrv.exe is
related to memory mapped IO and therefore can be ignored. Right or
wrong?

Any thoughs/pointers/ideas, even wild guesses, are most welcome.

Bjrn

PS: The server memory is fixed at 1.5GB out of 2GB physical ram,
clients run on the same machine and use TCP/IP comm.(I know...) The
host itself is not paging.bjorn.augestad@.gmail.com (bjorn.augestad@.gmail.com) writes:

Quote:

Originally Posted by

We have a couple of pathological sql servers that have lots and lots of
page faults per second, up to 4000. Our client programs are written in
C#/.NET 1.1 and utilizes connection pooling.
>
Some of the client programs seems to log in hundred of times per
second, as reported by perfmon->.SQLServer:General
Statistics->Logins/sec. Stopping the client programs reduces that
number significantly.
>
We've done code reviews of the client programs and they look OK.
Monitoring .NET connections&pools does not show anything suspicicous.
>
We're currently rewriting the clients to use one db connection instead
of the pools, but that takes some time and may introduce bugs. Does
anyone know why we have these problems and/or why logins/sec is so
high? I'm thinking "bugs in the .NET client", but really have no
idea...

I would use Profiler to see what these clients are up to. If they are
generating tons of Audit:Login events, there is something fishy. Either
they don't use pooling, or misbehave so that pooling is cannot be used.
I think a common error is to fail to close the connections.

If on the other hand they generate a lot of sp_reset_connection, then
at least connection pooling is in order. (sp_reset_connection is
executed when a connection is reused from the pool.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

bjorn.augestad@.gmail.com (bjorn.augestad@.gmail.com) writes:

Quote:

Originally Posted by

We have a couple of pathological sql servers that have lots and lots of
page faults per second, up to 4000. Our client programs are written in
C#/.NET 1.1 and utilizes connection pooling.

Some of the client programs seems to log in hundred of times per
second, as reported by perfmon->.SQLServer:General
Statistics->Logins/sec. Stopping the client programs reduces that
number significantly.

We've done code reviews of the client programs and they look OK.
Monitoring .NET connections&pools does not show anything suspicicous.

We're currently rewriting the clients to use one db connection instead
of the pools, but that takes some time and may introduce bugs. Does
anyone know why we have these problems and/or why logins/sec is so
high? I'm thinking "bugs in the .NET client", but really have no
idea...

>
I would use Profiler to see what these clients are up to. If they are
generating tons of Audit:Login events, there is something fishy. Either
they don't use pooling, or misbehave so that pooling is cannot be used.
I think a common error is to fail to close the connections.
>
If on the other hand they generate a lot of sp_reset_connection, then
at least connection pooling is in order. (sp_reset_connection is
executed when a connection is reused from the pool.)
>

I just sampled all statements for one hour, and we had ~450.000 calls
to sp_reset_connection, or 125 calls per second. Seems way too high to
me, even if the documentation describes it as lightweight.

I guess we'll continue to rewrite the clients to use just one
connection wherever possible. The changes made so far has improved the
situation a lot. :-)

Thanks.
Bjrn|||bjorn.augestad@.gmail.com (bjorn.augestad@.gmail.com) writes:

Quote:

Originally Posted by

I just sampled all statements for one hour, and we had ~450.000 calls
to sp_reset_connection, or 125 calls per second. Seems way too high to
me, even if the documentation describes it as lightweight.

Al least connection pooling is working!

Quote:

Originally Posted by

I guess we'll continue to rewrite the clients to use just one
connection wherever possible. The changes made so far has improved the
situation a lot. :-)

Yes, with that connection rate, he disconnected model is not very good.
Connection pooling is particular useful in things like web applications,
when you have many users that connect through the same middleware (the
web server), and that run queries very infrequently.

The model is usually fine for Windows applications too. But if you have some
service-type of thing which does not have to wait for a user that goes for a
coffee, it may be better to stay connected.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

bjorn.augestad@.gmail.com (bjorn.augestad@.gmail.com) writes:

Quote:

Originally Posted by

I just sampled all statements for one hour, and we had ~450.000 calls
to sp_reset_connection, or 125 calls per second. Seems way too high to
me, even if the documentation describes it as lightweight.

>
Al least connection pooling is working!

More or less.
We also have a tiny ASP app that writes one row to the db for each HTTP
request it receives. The ASP app used to use connection pools with
default settings. After changing Min and Max pool size to 300,
everything is suddenly more normal, now the server is so fast my
profiler traces has to be changed to make them report rows at all :-)

Stuff that used to take between 1200 and 7000ms now finishes in 1ms, so
I guess the problem has been solved.

Have a great weekend.
Bjrn

[snip]