MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
identical table TESTTBL. I need to make sure that tables TESTTBL in
both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
following C# code snippet fails. Is it possible to execute command
across two servers or am I wasting my time? Any help on this will be
appreciated.
string sSql= "delete SERVER-2.TESTDB.TESTTBL where
SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
from SERVER-1.TESTDB.TESTTBL )";
SqlConnection thisConnection1 = null;
SqlConnection thisConnection2 = null;
SqlCommand thisCommand1 = null;
SqlCommand thisCommand2 = null;
thisConnection1 = new SqlConnection(connectionString1);
thisConnection1.Open();
thisConnection2 = new SqlConnection(connectionString2);
thisConnection2.Open();
thisCommand1 = new SqlCommand(sSql, thisConnection1);
try
{
thisCommand1.ExecuteNonQuery();
}
catch (SqlException oE)
{
MessageBox.Show(oE.Message.ToString());
}Have you consider using REPLICATION between tow servers?
<bkasmai@.gmail.com> wrote in message
news:1144070684.346122.72030@.v46g2000cwv.googlegroups.com...
> Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
> identical table TESTTBL. I need to make sure that tables TESTTBL in
> both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
> which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
> would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
> make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
> following C# code snippet fails. Is it possible to execute command
> across two servers or am I wasting my time? Any help on this will be
> appreciated.
> string sSql= "delete SERVER-2.TESTDB.TESTTBL where
> SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
> from SERVER-1.TESTDB.TESTTBL )";
> SqlConnection thisConnection1 = null;
> SqlConnection thisConnection2 = null;
> SqlCommand thisCommand1 = null;
> SqlCommand thisCommand2 = null;
> thisConnection1 = new SqlConnection(connectionString1);
> thisConnection1.Open();
> thisConnection2 = new SqlConnection(connectionString2);
> thisConnection2.Open();
> thisCommand1 = new SqlCommand(sSql, thisConnection1);
> try
> {
> thisCommand1.ExecuteNonQuery();
> }
> catch (SqlException oE)
> {
> MessageBox.Show(oE.Message.ToString());
> }
>|||Yes. Past experience prevent me from using replication.
SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.|||Well, another option is to BACKUP LOG file on the source SERVER and
RESTORE it on destination. But I see the problem here because if I remember
well the destination SERVER must be read only....
<bkasmai@.gmail.com> wrote in message
news:1144073079.759225.46870@.e56g2000cwe.googlegroups.com...
> Yes. Past experience prevent me from using replication.
> SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
> SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.
>|||The sync needs to be done every hour through a application written in
c# !

MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
identical table TESTTBL. I need to make sure that tables TESTTBL in
both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
following C# code snippet fails. Is it possible to execute command
across two servers or am I wasting my time? Any help on this will be
appreciated.
string sSql= "delete SERVER-2.TESTDB.TESTTBL where
SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
from SERVER-1.TESTDB.TESTTBL )";
SqlConnection thisConnection1 = null;
SqlConnection thisConnection2 = null;
SqlCommand thisCommand1 = null;
SqlCommand thisCommand2 = null;
thisConnection1 = new SqlConnection(connectionString1);
thisConnection1.Open();
thisConnection2 = new SqlConnection(connectionString2);
thisConnection2.Open();
thisCommand1 = new SqlCommand(sSql, thisConnection1);
try
{
thisCommand1.ExecuteNonQuery();
}
catch (SqlException oE)
{
MessageBox.Show(oE.Message.ToString());
}Have you consider using REPLICATION between tow servers?
<bkasmai@.gmail.com> wrote in message
news:1144070684.346122.72030@.v46g2000cwv.googlegroups.com...
> Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
> identical table TESTTBL. I need to make sure that tables TESTTBL in
> both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
> which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
> would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
> make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
> following C# code snippet fails. Is it possible to execute command
> across two servers or am I wasting my time? Any help on this will be
> appreciated.
> string sSql= "delete SERVER-2.TESTDB.TESTTBL where
> SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
> from SERVER-1.TESTDB.TESTTBL )";
> SqlConnection thisConnection1 = null;
> SqlConnection thisConnection2 = null;
> SqlCommand thisCommand1 = null;
> SqlCommand thisCommand2 = null;
> thisConnection1 = new SqlConnection(connectionString1);
> thisConnection1.Open();
> thisConnection2 = new SqlConnection(connectionString2);
> thisConnection2.Open();
> thisCommand1 = new SqlCommand(sSql, thisConnection1);
> try
> {
> thisCommand1.ExecuteNonQuery();
> }
> catch (SqlException oE)
> {
> MessageBox.Show(oE.Message.ToString());
> }
>|||Yes. Past experience prevent me from using replication.
SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.|||Well, another option is to BACKUP LOG file on the source SERVER and
RESTORE it on destination. But I see the problem here because if I remember
well the destination SERVER must be read only....
<bkasmai@.gmail.com> wrote in message
news:1144073079.759225.46870@.e56g2000cwe.googlegroups.com...
> Yes. Past experience prevent me from using replication.
> SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
> SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.
>|||The sync needs to be done every hour through a application written in
c# !sql

MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
identical table TESTTBL. I need to make sure that tables TESTTBL in
both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
following C# code snippet fails. Is it possible to execute command
across two servers or am I wasting my time? Any help on this will be
appreciated.
string sSql= "delete SERVER-2.TESTDB.TESTTBL where
SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
from SERVER-1.TESTDB.TESTTBL )";
SqlConnection thisConnection1 = null;
SqlConnection thisConnection2 = null;
SqlCommand thisCommand1 = null;
SqlCommand thisCommand2 = null;
thisConnection1 = new SqlConnection(connectionString1);
thisConnection1.Open();
thisConnection2 = new SqlConnection(connectionString2);
thisConnection2.Open();
thisCommand1 = new SqlCommand(sSql, thisConnection1);
try
{
thisCommand1.ExecuteNonQuery();
}
catch (SqlException oE)
{
MessageBox.Show(oE.Message.ToString());
}
Have you consider using REPLICATION between tow servers?
<bkasmai@.gmail.com> wrote in message
news:1144070684.346122.72030@.v46g2000cwv.googlegro ups.com...
> Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
> identical table TESTTBL. I need to make sure that tables TESTTBL in
> both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
> which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
> would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
> make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
> following C# code snippet fails. Is it possible to execute command
> across two servers or am I wasting my time? Any help on this will be
> appreciated.
> string sSql= "delete SERVER-2.TESTDB.TESTTBL where
> SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
> from SERVER-1.TESTDB.TESTTBL )";
> SqlConnection thisConnection1 = null;
> SqlConnection thisConnection2 = null;
> SqlCommand thisCommand1 = null;
> SqlCommand thisCommand2 = null;
> thisConnection1 = new SqlConnection(connectionString1);
> thisConnection1.Open();
> thisConnection2 = new SqlConnection(connectionString2);
> thisConnection2.Open();
> thisCommand1 = new SqlCommand(sSql, thisConnection1);
> try
> {
> thisCommand1.ExecuteNonQuery();
> }
> catch (SqlException oE)
> {
> MessageBox.Show(oE.Message.ToString());
> }
>
|||Yes. Past experience prevent me from using replication.
SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.
|||Well, another option is to BACKUP LOG file on the source SERVER and
RESTORE it on destination. But I see the problem here because if I remember
well the destination SERVER must be read only....
<bkasmai@.gmail.com> wrote in message
news:1144073079.759225.46870@.e56g2000cwe.googlegro ups.com...
> Yes. Past experience prevent me from using replication.
> SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
> SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.
>
|||The sync needs to be done every hour through a application written in
c# !

MS SQL: syntax error in create table sql

Using Management Studio (for MS SQL server 8) I had created a table, given a primary key and created a unique key.
I then had the management studio "script 'create' to file" and it saved an SQL statement (below).
I then dropped/deleted the table and tried to execute the creation statement.
It says "Incorrect syntax near '('. " and references the block connected with the "WITH" statement. (Actually both of them cause the error and removing them gets rid of it.
Is there a way to keep the information contained in the WITH statement in my table creation SQL? Is it even required info?

Code: ( sql )

USE [MyProducts]GO/****** Object: Table [dbo].[Category] Script Date: 10/12/2007 11:22:52 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Category]( [CategoryID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, [CategoryNumber] [int] NOT NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED ( [CategoryID] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY], CONSTRAINT [IX_UniqueCategoryNumber] UNIQUE NONCLUSTERED ( [CategoryNumber] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]) ON [PRIMARY] GOSET ANSI_PADDING OFF

Usually you never use With statement while creating a table.

Remove this statement and create table without it then go to table definition and see it should be picked by default.

Good Luck.|||it "appeared" to create correctly without the WITH statements.
I was just bothered by the management studio auto-createing the statement then telling my there were syntax errors. The msdn help on the T-SQL for it shows WITH statements in the smae style.
All well, I'll just remove them.

Thanks.|||You can change some settings but not all.
Most of them are server level settings and can not be changed and just read only.

For example you see ON [PRIMARY] and it allows you to put data on one device and indexes on different devices or even spread tables among several devices but server should see your other devices first before you are trying to create something on them. So it is available but for people who know what they doing
You are doing grate so good luck.

MS SQL(2000) table export to excel

I′m looking for a script (sql transaction, or function) to export MS
SQL(2000) Table to formated excel sheet (cells width, text format, ...).how about using the DTS
to export that to excel
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Milo?" wrote:

> I′m looking for a script (sql transaction, or function) to export MS
> SQL(2000) Table to formated excel sheet (cells width, text format, ...).|||Milo?,
You also can use BCP command. See Books Online.
Regards,
"Milo?" wrote:

> I′m looking for a script (sql transaction, or function) to export MS
> SQL(2000) Table to formated excel sheet (cells width, text format, ...).|||Thank You Jose,
DTS export ist very easy, for my situation is the best, but it has litlle
problem with text encoding.
Milo?
?Jose G. de Jesus Jr MCP, MCDBA" nap_sal (nap_sala):
> how about using the DTS
> to export that to excel
>
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Milo?" wrote:
>sql

MS SQL Table JOIN with Oracle Table

I've got an Oracle table that I need to JOIN with a SQL table, and I've created the two datasets to pull the data I need. Is there any way Reporting Services can put them together for me?

No, SSRS cannot join data in two datasets.

Why not link your Oracle table into SQL, then write a view JOINing the data there...you could then provide the view to SSRS as a data source...

|||We tried it, but it was way slow. It also means installing the Oracle client on the SQL box and we're trying to avoid that if we can for security reasons.|||Not too much else you can do -- Although you could store your Oracle data in an SSRS cube...as long as the data wasn't too dynamic could get a good response that way at the cost of having to copy data over in the first place

MS sql sever connection string

Hi everybody
Can anybody write a code and tell me how can i access a table called
KF_STATUS which is stored in MS SQL server2000. and it thas 3 fields called
KF_ID ,KF_DATE, and KF_STATUS and how can i display contents of those fields
--
Message posted via http://www.sqlmonster.comHi,
Access the table:-
1. Login to QUERY analyzer by providing the user name password
2. From the database pane, select the database where the KF_STATUS table
resides
3. Write the below query
select KF_ID ,KF_DATE, KF_STATUS FROM KF_STATUS
4. Execute the query by pressing Control and E simulteneously. This will
give u result.
THnaks
Hari
SQL Server MVP
"gurvinder gill via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:f68623ec640847c497a4e72817032580@.SQLMonster.com...
> Hi everybody
> Can anybody write a code and tell me how can i access a table called
> KF_STATUS which is stored in MS SQL server2000. and it thas 3 fields
> called
> KF_ID ,KF_DATE, and KF_STATUS and how can i display contents of those
> fields
> --
> Message posted via http://www.sqlmonster.com

MS sql sever connection string

Hi everybody
Can anybody write a code and tell me how can i access a table called
KF_STATUS which is stored in MS SQL server2000. and it thas 3 fields called
KF_ID ,KF_DATE, and KF_STATUS and how can i display contents of those fields
Message posted via http://www.droptable.comHi,
Access the table:-
1. Login to QUERY analyzer by providing the user name password
2. From the database pane, select the database where the KF_STATUS table
resides
3. Write the below query
select KF_ID ,KF_DATE, KF_STATUS FROM KF_STATUS
4. Execute the query by pressing Control and E simulteneously. This will
give u result.
THnaks
Hari
SQL Server MVP
"gurvinder gill via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:f68623ec640847c497a4e72817032580@.SQ
droptable.com...
> Hi everybody
> Can anybody write a code and tell me how can i access a table called
> KF_STATUS which is stored in MS SQL server2000. and it thas 3 fields
> called
> KF_ID ,KF_DATE, and KF_STATUS and how can i display contents of those
> fields
> --
> Message posted via http://www.droptable.comsql

MS sql sever connection string

Hi everybody
Can anybody write a code and tell me how can i access a table called
KF_STATUS which is stored in MS SQL server2000. and it thas 3 fields called
KF_ID ,KF_DATE, and KF_STATUS and how can i display contents of those fields
Message posted via http://www.droptable.com
Hi,
Access the table:-
1. Login to QUERY analyzer by providing the user name password
2. From the database pane, select the database where the KF_STATUS table
resides
3. Write the below query
select KF_ID ,KF_DATE, KF_STATUS FROM KF_STATUS
4. Execute the query by pressing Control and E simulteneously. This will
give u result.
THnaks
Hari
SQL Server MVP
"gurvinder gill via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:f68623ec640847c497a4e72817032580@.droptable.co m...
> Hi everybody
> Can anybody write a code and tell me how can i access a table called
> KF_STATUS which is stored in MS SQL server2000. and it thas 3 fields
> called
> KF_ID ,KF_DATE, and KF_STATUS and how can i display contents of those
> fields
> --
> Message posted via http://www.droptable.com

MS SQL Server Indentity Jumps

Hi All

I have a table in SQL Server with ID having indentity inrement by one.
Table has not any trigger. Frequently ID in the table jumps.

Any help !!!

ThanksHi

If the transaction fails and rolls back the identity value will not be
reused and can therefore jump. Identities are not guaranteed to be
contiguous.

There is an example in Books online on how to fill non-contiguous identity
values.

John

"Jashan" <Jashan101@.hotmail.com> wrote in message
news:db88da90.0310131559.6d511798@.posting.google.c om...
> Hi All
> I have a table in SQL Server with ID having indentity inrement by one.
> Table has not any trigger. Frequently ID in the table jumps.
> Any help !!!
> Thanks|||Jashan101@.hotmail.com (Jashan) wrote in message news:<db88da90.0310131559.6d511798@.posting.google.com>...
> Hi All
> I have a table in SQL Server with ID having indentity inrement by one.
> Table has not any trigger. Frequently ID in the table jumps.
> Any help !!!
> Thanks

IDENTITY columns are not guaranteed to maintain a sequence of numbers
with no gaps. The new identity value is assigned before the INSERT is
executed, but if the INSERT fails or is rolled back in a transaction,
then the value will not be reused. Books Online also says that the
value may jump if there are a lot of deletions happening on the table
- see the "IDENTITY (property)" topic.

Another possibility is that someone simply inserted the values you see
with IDENTITY_INSERT, perhaps as part of a bulk load operation.

If you need to ensure that there are no gaps in the values in your
column, then you'll need to code your own solution. You could search
Google for "mssql and sequences" to get some ideas.

Simon

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 Server - User Rights

Does anyone know if it possible to make a specific table invisible to a
user when they are in Enterprise Manager?Hi

This would not be possible as you are denying them access to the system
catalogs. If you profiled EM you would see the statement used, and if they
had QA they could always run this sort of query themselves. Just because you
see table does not mean they can see the structure or contents.

John
"code2live" <akorol@.gmail.com> wrote in message
news:1109889106.584853.170100@.f14g2000cwb.googlegr oups.com...
> Does anyone know if it possible to make a specific table invisible to a
> user when they are in Enterprise Manager?

MS SQL Script Optimisation

Still a bit of a newbie to SQL, I've written a solution to a problem but I'm sure there is a neater fix.

I have two tables,

Table 1 (sales) which indexes product_id, customers and the date when the customer bought the product.
contains the columns Prod_Id (int), Customers (char), DateNo (int)

Table 2 (products) contains a list of all products in the system
contains the columns Prod_Id(int), ProductDescription(char) and Price (int)

I want to display a list to the top 10 most popular products.
Listing their description, price and the number of times they have been purchased in 2006. Does not matter who bought them.

My current approach is:
----------------------

CREATE TABLE #temptable
(
Prod_IdINTNOT NULL,
CounterINTNOT NULL
)

INSERT INTO #temptable
SELECT TOP 10 WITH TIES Prod_Id, Count(*)
FROM Sales
WHERE DateNo >= '20060101'
AND DateNo < '20060516'
GROUP BY Prod_Id
ORDER BY COUNT(*) DESC;

SELECT t.Counter, p.ProductDescription, p.Price
FROM Products AS p, #temptable AS t
WHERE t.Prod_Id = p.Prod_Id;

DROP TABLE #temptable

---------------

Any help would be appriciated.
Thank you.I havent tested this, as I'm lasy, but it should give the same result but a bit faster as its not creating and dropping tables

SELECT TOP 10 WITH TIES p.ProductDescription, p.Price ,Count(s.Prod_Id)
FROM Sales as s
INNER JOIN Products AS p
ON s.Prod_Id = p.Prod_Id
WHERE s.DateNo >= '20060101'
AND s.DateNo < '20060516'
GROUP BY p.ProductDescription, p.Price
ORDER BY Count(s.Prod_Id) DESC

Hope it works

MS SQL Record Limit

Quick question, is there a record limit in MSSQL assuming that we don't use a identity key. I am going to be using a table to sava mail server logs. It will create about 5000 records a minute. Also are there any perfomrance issues once you reach a certain numberof records, assuiming i am indexing one of the columns.limited by available storage

[Books Online] Maximum Capacity Specifications

look for rows per table

or LINK (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp)

now 5000 rows a minute for how long??|||drop any index before inserting data.
when transaction complished,recreate index.|||Also plan to archive the data in order to manage the database efficiently without any issues. say archive the data after a week or month or so.

This way you can ease the administration of the database on the terms of performance etc.

Refer to http://www.sql-server-performance.com for tips and tricks on performance issues.|||Thanks for all your help, I am going to do some testing and see what happens... the best way to learn is to do.