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 Server tree structure

Can anyone please help me? I need to create a tree structure of my company's database server. I need to include all the tables and their description in a tree structure. The output can be in any format. Is there some tool in the MSSql that will do this for me, so I don't have to write it out manualy? Thank you very much for any ideas!!
M.

You can loop though the database objects using SMO, the new .NET object model of SQL Server.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Thank you for answering.

I was looking for some tool that I can use on my Ms Sql server via the Enterprize manager or maybe via some function trough the Query Analyzer, if such a function exists? Is there anything like that available?

Honestly, I realy don't see how the SMO could help me create a tree structured diagram of my server.

Thanks
m.
|||thanks Jens K. Suessmeyer
Do you think CTE can be useful?

|||No, besides the fact that I use MS Sql 2000, this tool is not what I'm looking for.

What I need is to make a diagram, for prezentation purposes, of our server and all the databases, tables and entities in it in a tree structure.
Creating such a diagram is only possible via the Enterprize manager, but this diagram cannot be exported. Only moved to and fro databases.

M.
|||

You can do this using Visio by reverse engineering an ERM diagram but i don't think you are able to do this using SQL Server tools.

MS SQL Server tree structure

Can anyone please help me? I need to create a tree structure of my company's database server. I need to include all the tables and their description in a tree structure. The output can be in any format. Is there some tool in the MSSql that will do this for me, so I don't have to write it out manualy? Thank you very much for any ideas!!
M.

You can loop though the database objects using SMO, the new .NET object model of SQL Server.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Thank you for answering.

I was looking for some tool that I can use on my Ms Sql server via the Enterprize manager or maybe via some function trough the Query Analyzer, if such a function exists? Is there anything like that available?

Honestly, I realy don't see how the SMO could help me create a tree structured diagram of my server.

Thanks
m.
|||thanks Jens K. Suessmeyer
Do you think CTE can be useful?

|||No, besides the fact that I use MS Sql 2000, this tool is not what I'm looking for.

What I need is to make a diagram, for prezentation purposes, of our server and all the databases, tables and entities in it in a tree structure.
Creating such a diagram is only possible via the Enterprize manager, but this diagram cannot be exported. Only moved to and fro databases.

M.
|||

You can do this using Visio by reverse engineering an ERM diagram but i don't think you are able to do this using SQL Server tools.

MS sql server query file

Hi All.

Have a nice day.

I have one file and all tables and fields created in this file, this file name is "db1.sql", i downloaded this file from a internet demo. and programmer give this file to use in the asp.net code. but i cannot open this file.

I want to use this file in my sqlepress, how can i import or bring to my sqlexpress server and use it ?

Please can you help me how can i use it?

Thanks.

Zahyea.

The file you download is a script file that contains the database and table creation, insertion of data, and views and procedures if any.

You need to execute the sql script. You can download SQL Server Management Studio for SQL Express that allows you to execute scripts against a SQL instance/database.

MS SQL Server import/export wizard

Hi everyone,
Plese help me out. I import one database from one server to another. This process able to copy all the tables and functions but it copied only few stored procedures.
so tell me how to copy or import remaining stored procedures?You could always right click on the database, go toall tasks and hit generate script and select the sps you need scripted and then run said script on the new server.|||Or better still

try using the Copy Database Wizard instead of the Import/Export Data Wizard

:-) GW

ms sql server enterprise manager

I have a need to create some database and tables on a remote copy of SQL server. (I have been given a password and userid). How do I get a client version of Enterprise Manager? I understand that if I download the 120 day trial of sql server I will get it, but what happens after 120 days? Is there some other free utility for maintaining and creating a database on the hosted SQL Server or do I need to purchase sql 2000 server just to maintain my databases?Someone mentioned to just install the tools from the 120 day package. What will happen to my Enterprise Manager after 120 days??|||Yes it will continue to work after the 120 days, it's the db engine that stops working.

MS SQL Server 2000 / MS Access - ODBC connection question

Hello all!

I have such question to all of you.
I have some tables linked from MS SQL Server 2000. Is time of processing
query
based on these linked tables from MS SQL Server 2000, faster or slower than
the time of processing the same query based on tables, which are not linked
but
imported to MS Access?

Can you answer this question?

Thank you in advance for posts
Marcin from Poland.No idea - it depends how big the tables are, what your queries look
like, how you execute your queries etc. If you're using stored
procedures, for example, then I would expect MSSQL to be faster, as all
the processing happens on the server, and it can use indexes, caching
etc. The best way to get an answer is simply to test it yourself.

Simon

MS SQL Server 2000 & ADP Write Conflict Error

Im new to SQL and am having some difficulty.
I created a DB that has about 5 tables, all of which are related via the
client_id column.
I created a trigger which upon insert into my main table shoots the
client_id into the corresponding column in the other tables.
My front end is a MS ADP project. When I try to insert a new record
with "ALL" the fields in my MS Access ADP Form filled in I get a "Write
COnflict" Error. But when I try to insert a new record with "ONLY"
filling in the client_id filed the record is created without a proble?
Can anyone help me?
*** Sent via Developersdex http://www.examnotes.net ***Two possible things to check...
1. Do any of the other tables have columns that do not permit null?
If so, when the trigger tries to do the insert, you can't complete.
2. Is the ForeignKey in any of the tables you are writing to, .. identity.
If so, you are creating a duplicate.
"Anon" <anonymous@.devdex.com> wrote in message
news:OLHVYmlMGHA.3732@.TK2MSFTNGP10.phx.gbl...
>
> Im new to SQL and am having some difficulty.
> I created a DB that has about 5 tables, all of which are related via the
> client_id column.
> I created a trigger which upon insert into my main table shoots the
> client_id into the corresponding column in the other tables.
> My front end is a MS ADP project. When I try to insert a new record
> with "ALL" the fields in my MS Access ADP Form filled in I get a "Write
> COnflict" Error. But when I try to insert a new record with "ONLY"
> filling in the client_id filed the record is created without a proble?
> Can anyone help me?
>
> *** Sent via Developersdex http://www.examnotes.net ***|||1. I added a default value to the columns in the "other" tables that do
not allow nulls
2. The foreignkeys in all the other tables are the primary keys...if
that makes any sense (Is there a work around?)
Two possible things to check...
1. Do any of the other tables have columns that do not permit null?
If so, when the trigger tries to do the insert, you can't complete.
2. Is the ForeignKey in any of the tables you are writing to, ..
identity.
If so, you are creating a duplicate.
-RS
*** Sent via Developersdex http://www.examnotes.net ***|||1. I added a default value to the columns in the "other" tables that do
not allow nulls
2. The foreignkeys in all the other tables are the primary keys...if
that makes any sense (Is there a work around?)
Two possible things to check...
1. Do any of the other tables have columns that do not permit null?
If so, when the trigger tries to do the insert, you can't complete.
2. Is the ForeignKey in any of the tables you are writing to, ..
identity.
If so, you are creating a duplicate.
-RS
*** Sent via Developersdex http://www.examnotes.net ***sql

MS SQL Server - LOCK Info

Hi

The system tables syslocks,syslockinfo give information on locked resources, spid, object_id,lock mode, lock status etc.

How will i get object_name

for example if a table is being locked above sys tables give object id, how can i get the actual table name ( object_name)

Please let me know
Best Regards
THNQdigitaljoin with sysobjects table|||You can get it using object_name(object_id) in your select query.

Thanks.
Pat

--------
Originally posted by fridays
join with sysobjects table|||Originally posted by fridays
join with sysobjects table

Hi ,

i found lock info for spid 12 using sp_lock 12

got objid = 'numxxxxxx'
type =TAB
mode=Sch-M and Status = WAIT

i tried joining syslockinfo with sysobjects like below

select si.rsc_objid, si.req_spid , so.name from
SYSLOCKINFO si ,SYSOBJECTS so where
si.req_spid =12 and --si.rsc_type =5 and
si.rsc_objid= 736846995 and so.xtype='U'

This doesnot seem to gimme what i am looking for..

My reqirement is what is Object_Name(objid given by splock spid)

Please advise what am i doign wrong.
Thanks
THNQdigital|||use master
select a.name , b.rsc_objid , c.spid
from sysdatabases a , syslockinfo b , syslocks c
where c.spid = b.req_spid and a.dbid = c.dbid
and c.spid = 'xxxx' and ....

u will know database name 'yyyy' and objid 'zzzzz'
then
select name from yyyy.dbo.sysobjects where id = 'zzzzzz'

Originally posted by THNQdigital
Hi ,

i found lock info for spid 12 using sp_lock 12

got objid = 'numxxxxxx'
type =TAB
mode=Sch-M and Status = WAIT

i tried joining syslockinfo with sysobjects like below

select si.rsc_objid, si.req_spid , so.name from
SYSLOCKINFO si ,SYSOBJECTS so where
si.req_spid =12 and --si.rsc_type =5 and
si.rsc_objid= 736846995 and so.xtype='U'

This doesnot seem to gimme what i am looking for..

My reqirement is what is Object_Name(objid given by splock spid)

Please advise what am i doign wrong.
Thanks
THNQdigital

MS SQL Server

Hi ,

I have a lot of tables in a database on MS SQl Sever 7.0
if i want to find out if any tables are not being used by any application...
Like, A table is there and it is no longer is used, but how to find out if that is really not used by any front end applications or any BCP/BULKINSERT statements etc.
Is there any way ? will the profiles help?

Thanks for the answer

Subrahmanya BhatAs far as I know, there is no "Last Accessed" property of a table.

If you want to identify all un-used tables, you may consider to catch all tables with changing record counts in the first place. Make a UNION query of counts of all tables like

SELECT count(*), "Table 1" FROM [Table 1] UNION
SELECT count(*), "Table 2" FROM [Table 2] UNION
...

, make an initial count, and repeat this after a while.

All tables with stable record counts may be unused, or just static like domain tables. To distinguish between them, rename all those tables by adding something like XYZ to the name, and test all your applications. If you are lucky, you will get a list of "missing" tables, which are the static ones.

However, this is a difficult path with an unknown result. Maybe your applications do not report the missing table, but just disfunctioning. Or you don't have a complete list of all applications (including import or export functionality with some auxillary tables). So, my advise would be to let the database as it is.|||Use SQL Profiler to monitor the tables in question.
You will have to run it long enough to be confident that all applications have been active. I'd say at least a month in order to cover typical month-end processing.

blindman|||Thanks for the information provided(Both cunt(*) and profile)
hopefully i find out such tables in my database using these suggestions.

Yeah if there was a "Last Accessed" property that would have been great

i am just planning to use DoktirBlue's method to begin with
with a little different approach liek below,

DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'user table' ( or some 'Tab%')

and open the cursor and from each table get the count(*) and insert in to a temp table

Just thinking in these lines , will see how far i will be successful.

Thanks for the help again
Sub rahmanya Bhat

Originally posted by DoktorBlue
As far as I know, there is no "Last Accessed" property of a table.

If you want to identify all un-used tables, you may consider to catch all tables with changing record counts in the first place. Make a UNION query of counts of all tables like

SELECT count(*), "Table 1" FROM [Table 1] UNION
SELECT count(*), "Table 2" FROM [Table 2] UNION
...

, make an initial count, and repeat this after a while.

All tables with stable record counts may be unused, or just static like domain tables. To distinguish between them, rename all those tables by adding something like XYZ to the name, and test all your applications. If you are lucky, you will get a list of "missing" tables, which are the static ones.

However, this is a difficult path with an unknown result. Maybe your applications do not report the missing table, but just disfunctioning. Or you don't have a complete list of all applications (including import or export functionality with some auxillary tables). So, my advise would be to let the database as it is.|||Its not necessary to use a cursor. For example, this query of the system tables could be used to insert data directly into a rowcount history table:

select sysobjects.name, sysindexes.rows
from sysobjects
inner join sysindexes on sysobjects.id = sysindexes.id and sysindexes.indid < 2
where sysobjects.type <> 'S'

You can expand on this to get a lot more functionality.

blindman|||bm, isn't your profiler option powerful enough? Anyway, thanks for your showing an easy way to get the row count. But, is this count always accurate. Isn't it saver to run a statistics update before like

dbcc updateusage
go

? Also, you should exclude this 'dtproperties' from your query.|||Yes, the Profiler option would be more thorough, but for a quick check against active tables (not lookup or reference tables!) the rowcount should be sufficient.

Update stats does need to be run in order to make 100% sure that the rowcount is accurate, though it should be accurate anyway. I do not use the SCHEMA objects much, since I normally query system tables directly, but I would guess that they reference the system tables too, and are therefor equally subject to rowcount errors if the statistics are out of date.|||along with dtproperties, we can also filter out everything else that comes with the server install by changing the bm's code to look like this:

select sysobjects.name, sysindexes.rows
from sysobjects
inner join sysindexes on sysobjects.id = sysindexes.id and sysindexes.indid < 2
where objectproperty(sysobjects.id, 'IsMSShipped') = 0

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 replication - Primary keys problem

Hi.
I am trying to replicate an MS SQL server but when I do that all my primary keys on the tables on subscriber server are gone and the datebase is unusefull.
Can anyone help me in that situation?

Thanks in advance.

Quote:

Originally Posted by Yordan Yordanov

Hi.
I am trying to replicate an MS SQL server but when I do that all my primary keys on the tables on subscriber server are gone and the datebase is unusefull.
Can anyone help me in that situation?

Thanks in advance.

Set "Copy unique key constraints" property of the articles on the publisher to True if you're on SQL Server 2005.

MS SQL Query statement.

Hi All,
Problem statement:
Group 2 tables.
B(B_ID,B_DES)
C(C_ID,B_ID,C_BY)
select B.*,C.*
from b bx inner join cx on bx.b_id=cx.b_id
group by B column,,C column
Output:
B_ID column | C_BY
1 we
1 xy
2 DF
Above result is not what i desired, instead, i needs:
1 we
xy
2 DF
How to solve it'
thank you in advance..Help will be appreciated.
Best regards,
Gin Lye KhorRepresent the results in a report engine using Group & Detail bands?
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:9B5D30B5-ADFB-404D-97D1-5CC5DB252BC4@.microsoft.com...
> Hi All,
> Problem statement:
> Group 2 tables.
> B(B_ID,B_DES)
> C(C_ID,B_ID,C_BY)
> select B.*,C.*
> from b bx inner join cx on bx.b_id=cx.b_id
> group by B column,,C column
> Output:
> B_ID column | C_BY
> 1 we
> 1 xy
> 2 DF
>
> Above result is not what i desired, instead, i needs:
> 1 we
> xy
> 2 DF
> How to solve it'
> thank you in advance..Help will be appreciated.
> Best regards,
> Gin Lye Khor
>
>

MS SQL Parts Database

I am a newbie to the world of SQL Server and I am trying to develop a parts database for computer components. I need tables for each component type (mobo, cpu, memory, hard drives etc.) but I also want to show relation between compatible components (i.e. sata harddrives for mobos that support sata as an example) in my queries and stored views so that if a user selected a Motherboard they can recieve a list of compatible components with that Motherboard. So far I have setup tables for each component, each table has a comp_id (where comp is the component type) and I have a comp_type_id table that stores all of the component id's. Each component table's comp_id column is the primary key relating to the comp_type_id table (which has the foreign key. Is this the way to go or am I completly out of the ball park. Any suggestions or guidence will be greatly apprieciated.I'd recomment NOT creating separate tables for each component. That would be a nightmare to maintain.

Instead, create a single table for storing all components and assigne each component a unique ID. Then have a second table for storing compatibilities with two fields as the primary key: Comp_ID and Compatible_Comp_ID.

This is an ambitious project, and your real challenge will be getting people to enter data religiously. You may think you are making the database more powerful by adding functionality, but you may end up making it such a pain to use and update that the data is never reliable. Just a word to the wise: keep it simple!

blindman|||I see what you mean. I will work that idea out on paper. What kind of index would you use on a table that massive? Also, how do I indicate two primary keys for one table.

Thank you for your suggestions|||You are not assigning two primary keys. You can only have one primary key (the clustered index) because this dictates the order that the data is actually stored in SQL server. Other keys (non-clustered indexes) are stored as separate invisible tables that contain pointers to your actual data. This is why they are slightly slower than clustered indexes in queries, and they also take up additional space.

What you need to do is make the clustered index contain both fields. You can do this easily in Enterprise manager's table designer by selecting both fields and then clicking on the KEY icon.

Some people create a single compound keyfield by concatenating the data from both fields into a third field, but I would recommend against this for a lot of reasons.

As far as the kind of index to use, I like UniqueIdentifers, which are GUIDs (Globally unique IDs). They aren't the shortest type of ID, and they are no fun to type in, but they are efficient and ensure that if you ever have to merge data from multiple databases (production, test, backup, etc) you won't have to worry about duplicate data.

Since you are creating a parts database, I doubt you are going to be dealing with millions of rows of data, so the efficiency of the index is not nearly as important as the functionality and clarity of the design.

blindman

MS SQL Linked Server Issue

Hi All,
I am have created a linked server in MS SQL 2000 to Sage Line 50v12 using the ODBC drivers in sage.I am able to view all the tables once I clicked on the link server link in the SQL enterprise manager. However when I run the below query I get only one records instead of 28.
SELECT * FROM OPENQUERY(SageL50, 'SELECT * from Sales_Ledger')
I am running SQL 2000 server with SP4
From the SQL online documentation I got to know that OPENQUERY returns only a single record. Is there any other function/command that will enable me to get all the records from a table from a linked server?
Can you let me know I am missing here?
Thanks and Regards,
Saurabh
Hi,
Any updates on the below query.
Thanks and Regards,
Saurabh
"news" <news@.microsoft.com> wrote in message news:%23b2csSXbIHA.484@.TK2MSFTNGP06.phx.gbl...
Hi All,
I am have created a linked server in MS SQL 2000 to Sage Line 50v12 using the ODBC drivers in sage.I am able to view all the tables once I clicked on the link server link in the SQL enterprise manager. However when I run the below query I get only one records instead of 28.
SELECT * FROM OPENQUERY(SageL50, 'SELECT * from Sales_Ledger')
I am running SQL 2000 server with SP4
From the SQL online documentation I got to know that OPENQUERY returns only a single record. Is there any other function/command that will enable me to get all the records from a table from a linked server?
Can you let me know I am missing here?
Thanks and Regards,
Saurabh
|||Did you try:
SELECT * FROM SageL50.<database>.<owner/schema>.Sales_Ledger
Replace <database> with the database that contains the table you want,
and the <owner/schema> with the owner of the table - generally dbo.
Example:
SELECT * FROM SageL50.db1.dbo.SalesLedger;
Jeff
news wrote:
> Hi,
> Any updates on the below query.
> Thanks and Regards,
> Saurabh
>
> "news" <news@.microsoft.com <mailto:news@.microsoft.com>> wrote in
> message news:%23b2csSXbIHA.484@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am have created a linked server in MS SQL 2000 to Sage Line
> 50v12 using the ODBC drivers in sage.I am able to view all the
> tables once I clicked on the link server link in the SQL
> enterprise manager. However when I run the below query I get only
> one records instead of 28.
> SELECT * FROM OPENQUERY(SageL50, 'SELECT * from Sales_Ledger')
> I am running SQL 2000 server with SP4
> From the SQL online documentation I got to know that OPENQUERY
> returns only a single record. Is there any other function/command
> that will enable me to get all the records from a table from a
> linked server?
> Can you let me know I am missing here?
> Thanks and Regards,
> Saurabh
>

MS Sql Database

Hi
I have access to a MS Sql database from my webhost.
I have used MS Acces in the past and created the tables locally then uploaded the MS Access DB.
How & what do I use to connect to the MS SQL DB on my web host and how do I create tables within the DB?
I'm very new to using and trying to connect to MS Sql DB.
Thanks for any help in advance.
Cheers
kefi2927

Hi,

You would either require a SQL Client installation to connect to the Server where SQL Server exists to create Database/Tables etc.,

Else, you would require Remote Desktop Access to logon to the Server and work directly over there.

Thanks.

|||Hi
Thanks for the reply.
What utility would I use for a Remote Desktop Access.
Cheers|||Hi,
Remote Desktop Access is built-in functionality from Windows XP Professional (not sure about the Home Edition).
From START- RUN - Type "mstsc" and give ENTER. You will get the dialog for entering the IP address of the machine.
If Remote Desktop Access is enabled to the server and you are connected to the Internet, you can logon to that machine from your machine.
If you are running older versions or require Remote Desktop Client software, you can check and download fromhttp://www.microsoft.com/windowsxp/downloads/tools/rdclientdl.mspx
Thanks.

MS SQL copy new and modified rows from TABLE1 to TABLE2

Hello,

I have 2 tables, Table1 and Table2. I have copied all data from Table1
to Table2.
However Table1 is dynamic it has new rows added and some old rows
modified everyday or every other day...
How can I continue to keep Table2 up to date without always having to
copy everything from Table1?

Basically from now on I would only like to copy new rows or modified
rows in Table1 to Table2 and skip rows that are already present and
have not been modified in Table1. I would like to not do anything for
any rows that were removed in Table1 and continue to keep a copy of
them in Table2.

Is using a DTS package the best way to automate this update of Table2
to make sure Table2 is always up-to-date with Table1?

Thanks for any help or advise :-)

YasYas (yasar1@.gmail.com) writes:

Quote:

Originally Posted by

I have 2 tables, Table1 and Table2. I have copied all data from Table1
to Table2.
However Table1 is dynamic it has new rows added and some old rows
modified everyday or every other day...
How can I continue to keep Table2 up to date without always having to
copy everything from Table1?
>
Basically from now on I would only like to copy new rows or modified
rows in Table1 to Table2 and skip rows that are already present and
have not been modified in Table1. I would like to not do anything for
any rows that were removed in Table1 and continue to keep a copy of
them in Table2.
>
>
Is using a DTS package the best way to automate this update of Table2
to make sure Table2 is always up-to-date with Table1?

The first question is why do you want to do this in the first place? It
seems funny that you would want to have two identical tables in the same
database? Or ar the tables in different databases on different servers?

If the tables are on the same server, a trigger would be the best way
to do it.

If tbe tables are on different server, triggers are still possible, but
if the remote server is unavailable, this would cause the operation on
the source table to fail. In this case, replication may be a way to go.

--
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|||On 16 Aug, 13:46, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

The first question is why do you want to do this in the first place? It
seems funny that you would want to have two identical tables in the same
database? Or ar the tables in different databases on different servers?

Hi, sorry perhaps I should have been a bit more clear. Well, Table2 is
essentially a Master table that will have a record of all users that
were ever added to Table1. So even if at a later date userA and userB
were removed from Table1, a record of UserA and UserB will always be
there in Table2.

So yes right now Table1 and 2 are identical and that seems
pointless...however soon Table2 will be different in that it will have
a record of rows that are no longer present in Table1. I'm keeping
track of them via another method which checks if a row has been
removed from Table1 if so it adds the date of removal to a column of
that row in Table2. This is why I dont want to update Table2 if a row
is removed in Table1...only if a new row is added or an existing one
modified.

I hope that explains what I'm trying to do :-) can I still use
Triggers to do this?

Quote:

Originally Posted by

If the tables are on the same server, a trigger would be the best way
to do it.

Yes, they are on the same server and in the same Database.|||Yas (yasar1@.gmail.com) writes:

Quote:

Originally Posted by

Hi, sorry perhaps I should have been a bit more clear. Well, Table2 is
essentially a Master table that will have a record of all users that
were ever added to Table1. So even if at a later date userA and userB
were removed from Table1, a record of UserA and UserB will always be
there in Table2.
>
So yes right now Table1 and 2 are identical and that seems
pointless...however soon Table2 will be different in that it will have
a record of rows that are no longer present in Table1. I'm keeping
track of them via another method which checks if a row has been
removed from Table1 if so it adds the date of removal to a column of
that row in Table2. This is why I dont want to update Table2 if a row
is removed in Table1...only if a new row is added or an existing one
modified.
>
I hope that explains what I'm trying to do :-) can I still use
Triggers to do this?

Since the tables are in the same database, triggers is definitely the
way to go.

--
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|||On 16 Aug, 15:16, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Yas (yas...@.gmail.com) writes:

Quote:

Originally Posted by

Hi, sorry perhaps I should have been a bit more clear. Well, Table2 is
essentially a Master table that will have a record of all users that
were ever added to Table1. So even if at a later date userA and userB
were removed from Table1, a record of UserA and UserB will always be
there in Table2.

>

Quote:

Originally Posted by

So yes right now Table1 and 2 are identical and that seems
pointless...however soon Table2 will be different in that it will have
a record of rows that are no longer present in Table1. I'm keeping
track of them via another method which checks if a row has been
removed from Table1 if so it adds the date of removal to a column of
that row in Table2. This is why I dont want to update Table2 if a row
is removed in Table1...only if a new row is added or an existing one
modified.

>

Quote:

Originally Posted by

I hope that explains what I'm trying to do :-) can I still use
Triggers to do this?

>
Since the tables are in the same database, triggers is definitely the
way to go.
>

Thanks. This is what I'm trying to do now... do you know how I can
refer to the row that has just been added or modified?

In Table1 I have...

CREATE TRIGGER (tr_updateMaster) ON dbo.Table2
FOR INSERT, UPDATE
AS
Here I would like to put something like...
Insert into Table2 new row + 2 extra columns (status and date)
AND/OR
Update modified row in dbo.Table2 with different values in Table1

Thanks again :-)|||On Thu, 16 Aug 2007 08:13:47 -0700, Yas <yasar1@.gmail.comwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>Since the tables are in the same database, triggers is definitely the
>way to go.
>>

>
>Thanks. This is what I'm trying to do now... do you know how I can
>refer to the row that has just been added or modified?

Read up on the INSERTED and DELETED virtual tables that are available
to triggers.

Roy Harvey
Beacon Falls, CT|||On 16 Aug, 17:48, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

On Thu, 16 Aug 2007 08:13:47 -0700, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Since the tables are in the same database, triggers is definitely the
way to go.

>

Quote:

Originally Posted by

Thanks. This is what I'm trying to do now... do you know how I can
refer to the row that has just been added or modified?

>
Read up on the INSERTED and DELETED virtual tables that are available
to triggers.

Hi I'm trying the following for INSERT trigger attached to Table1 but
it doesn't seem to work in that it doesn't insert the new rows into
Table2 from Table1

CREATE TRIGGER my_Trigger ON [dbo].[Table2]
FOR INSERT
AS

INSERT INTO
Table2(STATUS,attribute15,email,lastname1,lastname 2,name,company,startDate)
SELECT 'Active' AS STATUS, b.Attribute15, b.email, b.lastname1,
b.lastname2, b.name,
b.company, b.startDate
FROM Inserted b LEFT OUTER JOIN
Table2 a ON b.Attribute15 = a.Attribute15
WHERE a.Attribute15 IS NULL
GO

The syntax according to MS SQL server is correct but nothing happens
when a new row is inserted into Table1.

The idea here is basically when a new row is inserted in Table1, the
above insert command is run and the new row copied over to Table2

Any help?

Thanks in advance|||Yas (yasar1@.gmail.com) writes:

Quote:

Originally Posted by

CREATE TRIGGER my_Trigger ON [dbo].[Table2]
FOR INSERT
AS
>
INSERT INTO
Table2(STATUS,attribute15,email,lastname1,lastname 2,name,company,startDate)
SELECT 'Active' AS STATUS, b.Attribute15, b.email, b.lastname1,
b.lastname2, b.name,
b.company, b.startDate
FROM Inserted b LEFT OUTER JOIN
Table2 a ON b.Attribute15 = a.Attribute15
WHERE a.Attribute15 IS NULL
GO
>
The syntax according to MS SQL server is correct but nothing happens
when a new row is inserted into Table1.

Well, the code you posted is a trigger on Table2, so...

--
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|||On 16 Aug, 22:58, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Yas (yas...@.gmail.com) writes:

Quote:

Originally Posted by

CREATE TRIGGER my_Trigger ON [dbo].[Table2]
FOR INSERT
AS

>

Quote:

Originally Posted by

INSERT INTO
Table2(STATUS,attribute15,email,lastname1,lastname 2,name,company,startDate)
SELECT 'Active' AS STATUS, b.Attribute15, b.email, b.lastname1,
b.lastname2, b.name,
b.company, b.startDate
FROM Inserted b LEFT OUTER JOIN
Table2 a ON b.Attribute15 = a.Attribute15
WHERE a.Attribute15 IS NULL
GO

>

Quote:

Originally Posted by

The syntax according to MS SQL server is correct but nothing happens
when a new row is inserted into Table1.

>
Well, the code you posted is a trigger on Table2, so...
>

DOH!!! what a silly mistake. :-) do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?

Thanks agian|||Yas (yasar1@.gmail.com) writes:

Quote:

Originally Posted by

DOH!!! what a silly mistake. :-)

It's often that when you work with something you are not really confident
that you look for the difficult mistakes and overlook the simple typos.

Quote:

Originally Posted by

do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?

Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
as I think that expresses more clearly what is going on. But that's a matter
of taste.

--
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|||On 17 Aug, 08:11, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Yas(yas...@.gmail.com) writes:

Quote:

Originally Posted by

DOH!!! what a silly mistake. :-)

>
It's often that when you work with something you are not really confident
that you look for the difficult mistakes and overlook the simple typos.
>

Quote:

Originally Posted by

do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?

>
Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
as I think that expresses more clearly what is going on. But that's a matter
of taste.
>

Thanks for all your advise and help! by the way do you if there is a
way to edit/change a Trigger once it has been created in MS SQL?

Yas|||On Fri, 17 Aug 2007 19:47:32 -0700, Yas wrote:

Quote:

Originally Posted by

>On 17 Aug, 08:11, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

>Yas(yas...@.gmail.com) writes:

Quote:

Originally Posted by

DOH!!! what a silly mistake. :-)

>>
>It's often that when you work with something you are not really confident
>that you look for the difficult mistakes and overlook the simple typos.
>>

Quote:

Originally Posted by

do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?

>>
>Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
>as I think that expresses more clearly what is going on. But that's a matter
>of taste.
>>

>
>Thanks for all your advise and help! by the way do you if there is a
>way to edit/change a Trigger once it has been created in MS SQL?

Hi Yas,

Yes. Simply use ALTER TRIGGER instead of CREATE TRIGGER.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 17 Aug, 09:11, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Yas(yas...@.gmail.com) writes:

Quote:

Originally Posted by

DOH!!! what a silly mistake. :-)

>
It's often that when you work with something you are not really confident
that you look for the difficult mistakes and overlook the simple typos.
>

Quote:

Originally Posted by

do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?

>
Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
as I think that expresses more clearly what is going on. But that's a matter
of taste.

Thanks. Just out of curiosity how would you modify the above to use
NOT EXISTS ?

cheers
Yas|||On Aug 16, 2:14 pm, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

On 16 Aug, 13:46, Erland Sommarskog <esq...@.sommarskog.sewrote:
>

Quote:

Originally Posted by

The first question is why do you want to do this in the first place? It
seems funny that you would want to have two identical tables in the same
database? Or ar the tables in different databases on different servers?

>
Hi, sorry perhaps I should have been a bit more clear. Well, Table2 is
essentially a Master table that will have a record of all users that
were ever added to Table1. So even if at a later date userA and userB
were removed from Table1, a record of UserA and UserB will always be
there in Table2.
>
So yes right now Table1 and 2 are identical and that seems
pointless...however soon Table2 will be different in that it will have
a record of rows that are no longer present in Table1. I'm keeping
track of them via another method which checks if a row has been
removed from Table1 if so it adds the date of removal to a column of
that row in Table2. This is why I dont want to update Table2 if a row
is removed in Table1...only if a new row is added or an existing one
modified.
>
I hope that explains what I'm trying to do :-) can I still use
Triggers to do this?
>

Quote:

Originally Posted by

If the tables are on the same server, a trigger would be the best way
to do it.

>
Yes, they are on the same server and in the same Database.

Couldn't you just use one table and add column use as a DELETED flag
to logically delete a user so the physical row is still there?|||On Tue, 21 Aug 2007 01:29:46 -0700, Yas <yasar1@.gmail.comwrote:

Quote:

Originally Posted by

INSERT INTO
Table2(STATUS,attribute15,email,lastname1,lastname 2,name,company,startDate)
SELECT 'Active' AS STATUS, b.Attribute15, b.email, b.lastname1,
b.lastname2, b.name,
b.company, b.startDate
FROM Inserted b LEFT OUTER JOIN
Table2 a ON b.Attribute15 = a.Attribute15
WHERE a.Attribute15 IS NULL

Quote:

Originally Posted by

>Just out of curiosity how would you modify the above to use
>NOT EXISTS ?

INSERT INTO Table2
(STATUS,attribute15,email,
lastname1,lastname2,name,
company,startDate)
SELECT 'Active' AS STATUS, b.Attribute15, b.email,
b.lastname1, b.lastname2, b.name,
b.company, b.startDate
FROM Inserted b
WHERE NOT EXISTS
(SELECT * FROM Table2 a
WHERE b.Attribute15 = a.Attribute15)

Roy Harvey
Beacon Falls, CT

MS -SQL behaviour of INSERTED / DELETED tables --> very strange

Hi All,
We have an odd thing while using the DELETED and INSERTED-
"tables"in triggers.
If my collegue executes and SP in the Query Analizer, a
query in a trigger takes a lot less when I execute the
same SP.
We think the difference is in the the usages of the
DELETED and INSERTED-tables. When my colleque runs it, the
DELETED and INSERTED-tables are being used, but when I run
the SP, a full tables scan is being performed (we
think!!!).
Has anyone seen this behaviour before or does anyone have
a solution for this?
Time difference between my collegue and I is 0.5 sec per
trigger (2 triggers in the SP are executed and lots of SP
executions).
Thanks in Advance,
Jeroen
PS. MS-SQL 2000 with SP3 on WIN 2000 serverJeroen
Can you show us your SP's code?
Have you ran SQL Server Profiler to identify what is going on when you
execute the SP?
"Jeroen Kraij" <jkrai@.kempen.nl> wrote in message
news:080e01c3bfc7$e7334580$a401280a@.phx.gbl...
> Hi All,
> We have an odd thing while using the DELETED and INSERTED-
> "tables"in triggers.
> If my collegue executes and SP in the Query Analizer, a
> query in a trigger takes a lot less when I execute the
> same SP.
> We think the difference is in the the usages of the
> DELETED and INSERTED-tables. When my colleque runs it, the
> DELETED and INSERTED-tables are being used, but when I run
> the SP, a full tables scan is being performed (we
> think!!!).
> Has anyone seen this behaviour before or does anyone have
> a solution for this?
> Time difference between my collegue and I is 0.5 sec per
> trigger (2 triggers in the SP are executed and lots of SP
> executions).
> Thanks in Advance,
> Jeroen
> PS. MS-SQL 2000 with SP3 on WIN 2000 server|||Hi,
The "thing" I would like to focus on is that we have a Pc
with an SQL-client.
My coleque logs in to this PC and connects to SQL-server
and executes the SP.
When I do exactly the same on this PC, The execution of
the SP takes a lot more time.
In the executionplan we can see that the processingtime of
the trigger(s) are the difference...
Is there any logical explanation for this? Or a solution?
Jeroen
>--Original Message--
>Jeroen
>Can you show us your SP's code?
>Have you ran SQL Server Profiler to identify what is
going on when you
>execute the SP?
>
>
>
>"Jeroen Kraij" <jkrai@.kempen.nl> wrote in message
>news:080e01c3bfc7$e7334580$a401280a@.phx.gbl...
>> Hi All,
>> We have an odd thing while using the DELETED and
INSERTED-
>> "tables"in triggers.
>> If my collegue executes and SP in the Query Analizer, a
>> query in a trigger takes a lot less when I execute the
>> same SP.
>> We think the difference is in the the usages of the
>> DELETED and INSERTED-tables. When my colleque runs it,
the
>> DELETED and INSERTED-tables are being used, but when I
run
>> the SP, a full tables scan is being performed (we
>> think!!!).
>> Has anyone seen this behaviour before or does anyone
have
>> a solution for this?
>> Time difference between my collegue and I is 0.5 sec per
>> trigger (2 triggers in the SP are executed and lots of
SP
>> executions).
>> Thanks in Advance,
>> Jeroen
>> PS. MS-SQL 2000 with SP3 on WIN 2000 server
>
>.
>