plz help me to upload my sqlserver data base with query analyzer method

plz help me to upload my sqlserver data base with query analyzer method
plz write the code i should write in query analyzer box to uplode
my database(db) in my server(h_server)
and is it neccessary to uplode log file or not
,or guide me with intruducing a sitewhat xactly are you trying to do ? just load up query analyzer to test some queries ? its in the menu under microsoft sql server under your programs menu. or you can open it from tools - > query analyzer from sql server xplorer.

hth|||i understand what you ae saying but i dont know who i replace suitable elements in this code

CREATE DATABASE [co] ON (NAME = N'co_Data', FILENAME = N'e:\sqlserver 2000\MSSQL\data\co_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'co_Log', FILENAME = N'e:\sqlserver 2000\MSSQL\data\co_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'co', N'autoclose', N'true'
GO

exec sp_dboption N'co', N'bulkcopy', N'false'
GO

exec sp_dboption N'co', N'trunc. log', N'true'
GO

exec sp_dboption N'co', N'torn page detection', N'true'
GO

exec sp_dboption N'co', N'read only', N'false'
GO

exec sp_dboption N'co', N'dbo use', N'false'
GO

exec sp_dboption N'co', N'single', N'false'
GO

exec sp_dboption N'co', N'autoshrink', N'true'
GO

exec sp_dboption N'co', N'ANSI null default', N'false'
GO

exec sp_dboption N'co', N'recursive triggers', N'false'
GO

exec sp_dboption N'co', N'ANSI nulls', N'false'
GO

exec sp_dboption N'co', N'concat null yields null', N'false'
GO

exec sp_dboption N'co', N'cursor close on commit', N'false'
GO

exec sp_dboption N'co', N'default to local cursor', N'false'
GO

exec sp_dboption N'co', N'quoted identifier', N'false'
GO

exec sp_dboption N'co', N'ANSI warnings', N'false'
GO

exec sp_dboption N'co', N'auto create statistics', N'true'
GO

exec sp_dboption N'co', N'auto update statistics', N'true'
GO

use [co]
GO

CREATE TABLE [dbo].[users] (
[u_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[u_username] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_joindate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_prof] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_fname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_lname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_coname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_location] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_nation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_website] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_account] [float] NULL
) ON [PRIMARY]
GO

Pls Help With JOIN query...

I'm trying to write a stored proc...

Basically, I have a tblItems table which contains a list of every item
available. One of the columns in this table is the brand... for test
purposes, I hardcoded the BrandID=1...

tblItems also contains a category column (int) which contains a categoryID
of 0..3...

I then have a category table which has CategoryID, Name, and DisplayOrder...

So basically what I'm trying to do is return a list of Category NAMES that
have items in them for a specifc brand... but I want to sort the returned
categories by the DisplayOrder column...

this is what I have now:

select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by tblCategories.DisplayOrder

this does what I want it to do, but its returning TWO columns... Name AND
DisplayOrder... I only want to return Name, but if I take the DisplayOrder
out of the select portion, it errors out because it can't order by that...

Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
the same category name.Just remove the order by ie:

select DISTINCT tblCategories.Name, tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1
/* order by tblCategories.DisplayOrder */

Nobody wrote:

Quote:

Originally Posted by

I'm trying to write a stored proc...
>
Basically, I have a tblItems table which contains a list of every item
available. One of the columns in this table is the brand... for test
purposes, I hardcoded the BrandID=1...
>
tblItems also contains a category column (int) which contains a categoryID
of 0..3...
>
I then have a category table which has CategoryID, Name, and DisplayOrder...
>
So basically what I'm trying to do is return a list of Category NAMES that
have items in them for a specifc brand... but I want to sort the returned
categories by the DisplayOrder column...
>
this is what I have now:
>
>
select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by tblCategories.DisplayOrder
>
this does what I want it to do, but its returning TWO columns... Name AND
DisplayOrder... I only want to return Name, but if I take the DisplayOrder
out of the select portion, it errors out because it can't order by that...
>
Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
the same category name.

|||Okay my mistake. This will do the job:

select a.tblCategories.Name
from (select DISTINCT top 100 percent tblCategories.Name,
tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by
tblCategories.Name,tblCategories.DisplayOrder) a

Unlike other databases, SQL Server does not allow 'order by' within
derived tables, so had to use top etc...

othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

Just remove the order by ie:
>
select DISTINCT tblCategories.Name, tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1
/* order by tblCategories.DisplayOrder */
>
Nobody wrote:

Quote:

Originally Posted by

I'm trying to write a stored proc...

Basically, I have a tblItems table which contains a list of every item
available. One of the columns in this table is the brand... for test
purposes, I hardcoded the BrandID=1...

tblItems also contains a category column (int) which contains a categoryID
of 0..3...

I then have a category table which has CategoryID, Name, and DisplayOrder...

So basically what I'm trying to do is return a list of Category NAMES that
have items in them for a specifc brand... but I want to sort the returned
categories by the DisplayOrder column...

this is what I have now:

select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by tblCategories.DisplayOrder

this does what I want it to do, but its returning TWO columns... Name AND
DisplayOrder... I only want to return Name, but if I take the DisplayOrder
out of the select portion, it errors out because it can't order by that...

Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
the same category name.

|||Nobody (nobody@.cox.net) writes:

Quote:

Originally Posted by

I'm trying to write a stored proc...
>
Basically, I have a tblItems table which contains a list of every item
available. One of the columns in this table is the brand... for test
purposes, I hardcoded the BrandID=1...
>
tblItems also contains a category column (int) which contains a categoryID
of 0..3...
>
I then have a category table which has CategoryID, Name, and
DisplayOrder...
>
So basically what I'm trying to do is return a list of Category NAMES that
have items in them for a specifc brand... but I want to sort the returned
categories by the DisplayOrder column...
>
this is what I have now:
>
>
select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by tblCategories.DisplayOrder
>
this does what I want it to do, but its returning TWO columns... Name AND
DisplayOrder... I only want to return Name, but if I take the DisplayOrder
out of the select portion, it errors out because it can't order by that...
>
Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies
of the same category name.

No, you don't need DISTINCT. You need to learn to use EXISTS:

SELECT C.Name
FROM tblCategories C
WHERE EXISTS (SELECT *
FROM tblItems I
WHERE I.CategoryID = C.CategoryID
AND I.BrandID = @.brandid)
ORDER BY C.DisplayOrder

--
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 Wed, 22 Nov 2006 16:59:17 -0800, Nobody wrote:

Quote:

Originally Posted by

>I'm trying to write a stored proc...
>
>Basically, I have a tblItems table which contains a list of every item
>available. One of the columns in this table is the brand... for test
>purposes, I hardcoded the BrandID=1...
>
>tblItems also contains a category column (int) which contains a categoryID
>of 0..3...
>
>I then have a category table which has CategoryID, Name, and DisplayOrder...
>
>So basically what I'm trying to do is return a list of Category NAMES that
>have items in them for a specifc brand... but I want to sort the returned
>categories by the DisplayOrder column...
>
>this is what I have now:
>
>
>select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
>tblCategories
>INNER JOIN tblItems
>on tblCategories.CategoryID = tblItems.CategoryID
>where BrandID=1 order by tblCategories.DisplayOrder
>
>this does what I want it to do, but its returning TWO columns... Name AND
>DisplayOrder... I only want to return Name, but if I take the DisplayOrder
>out of the select portion, it errors out because it can't order by that...
>
>Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
>the same category name.
>

Hi Nobody,

Since you don't display any columns from tblItems, the only reason to
use it in this query is obviously to check for existance of at least one
row with BrandID equal to 1. That means that you can rewrite your query
as

SELECT c.Name --, c.DisplayOrder
FROM Categories AS c
WHERE EXISTS
(SELECT *
FROM Items AS i
WHERE i.CategoryID = c.CategoryID
AND i.BrandID = 1)
ORDER BY c.DisplayOrder;

You'll probably see a performance increase as well.

--
Hugo Kornelis, SQL Server MVP|||On 23 Nov 2006 01:48:32 -0800, othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

>Okay my mistake. This will do the job:
>
>select a.tblCategories.Name
>from (select DISTINCT top 100 percent tblCategories.Name,
>tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by
>tblCategories.Name,tblCategories.DisplayOrder) a
>
>Unlike other databases, SQL Server does not allow 'order by' within
>derived tables, so had to use top etc...

Hi othellomy,

Though you can use ORDER BY in a subquery if you also use TOP, the ORDER
BY will only be used to determins which rows meat the TOP criterium;
there is no guarantee that the actual order of the query will be the
same. In fact, SQL Server 2005 will ignore both TOP 100 PERCENT and the
accomanying ORDER BY, since it is essentially a no-op to restrict the
output to 100 percent of the regular output.

If you really want to move the DISTINCT to a subquery (which in this
case is NOT needed - see my reply to Nobody), you could use

SELECT a.Name
FROM (SELECT DISTINCT c.Name, c.DisplayOrder
FROM Categories AS c
INNER JOIN Items AS i
ON i.CategoriID = c.CategoryID
WHERE i.BrandID = 1) AS a
ORDER BY a.DisplayOrder;

(untested)

--
Hugo Kornelis, SQL Server MVP

please tell me how to know when a deadlock occurs in sp

can i write a trigger which tells me when a deadlock occursDo it on the client
The error number is 1204 if I remember well.
"raghu veer" <raghuveer@.discussions.microsoft.com> wrote in message
news:3EEECEDC-C8D4-4F76-8C44-AE290DF52D95@.microsoft.com...
> can i write a trigger which tells me when a deadlock occurs|||Raghu
when ever deadlock occurs it throughs an error no :1205
so you can have a mechanism to set something like this for further
investigation through trigger.
IF @.@.ERROR = 1205
begin
-- EITHER NOTIFY OR INSERT INTO A TEMP TABLE
--RUN PROCEDURE AGAIN TO DO THE WORK.
END
Regards
R.D
"raghu veer" wrote:

> can i write a trigger which tells me when a deadlock occurs|||Uri
1204 is used to trace TRACEFLAG(1204)
while error is 1205, I think
Regards
R.D
"Uri Dimant" wrote:

> Do it on the client
> The error number is 1204 if I remember well.
>
> "raghu veer" <raghuveer@.discussions.microsoft.com> wrote in message
> news:3EEECEDC-C8D4-4F76-8C44-AE290DF52D95@.microsoft.com...
>
>|||When a transaction is chosen as the deadlock victim, that transaction is
rolled back and a 1205 error is returned on the connection. The batch may
or may not be terminated, so you should add error handling code after every
statement in a transaction. If a rollback occurs within a trigger, that
trigger continues executing the balance of its body and then the batch is
terminated. This means that error handling is needed within the body of a
trigger as well. No additional triggers are fired and no statements
following the statement that caused the trigger to fire execute. Because
the batch may also be terminated by a 1205 error, you must add code to
detect it in the client app.
Error handling code must exist in both the stored procedure and the client
app. This is extremely important, because if you fail to check for errors
after every statement within a transaction, the statements following the
error will execute in autocommit mode, which can introduce inconsistency
into the database. In addition, if you have multiple data modification
statements within a trigger, you must also add error handling code after
each statement in the trigger. This cannot be stressed enough. Tracking
down the resultant data corruption is extremely difficult to do.
It is not possible to write a trigger that will detect all deadlocks and
inform you when they occur. The trigger containing the detection and
notification code may not fire if the deadlock occurs while another trigger
on the same table is executing.
"raghu veer" <raghuveer@.discussions.microsoft.com> wrote in message
news:3EEECEDC-C8D4-4F76-8C44-AE290DF52D95@.microsoft.com...
> can i write a trigger which tells me when a deadlock occurs|||Raghu
Follow these steps in dev node.
1)DBCC TRACEON(3604)
2)DBCC TRACEON(1204)
3) RUN SPs that are raising deadlocks
4) you can analyse the results and find out which object is becoming dead
lock victim and even which line of sp is causing deadlock
5) attack query
If you have problem in analysing and finding the object Post results here
Regards
R.D
"Brian Selzer" wrote:
> When a transaction is chosen as the deadlock victim, that transaction is
> rolled back and a 1205 error is returned on the connection. The batch may
> or may not be terminated, so you should add error handling code after ever
y
> statement in a transaction. If a rollback occurs within a trigger, that
> trigger continues executing the balance of its body and then the batch is
> terminated. This means that error handling is needed within the body of a
> trigger as well. No additional triggers are fired and no statements
> following the statement that caused the trigger to fire execute. Because
> the batch may also be terminated by a 1205 error, you must add code to
> detect it in the client app.
> Error handling code must exist in both the stored procedure and the client
> app. This is extremely important, because if you fail to check for errors
> after every statement within a transaction, the statements following the
> error will execute in autocommit mode, which can introduce inconsistency
> into the database. In addition, if you have multiple data modification
> statements within a trigger, you must also add error handling code after
> each statement in the trigger. This cannot be stressed enough. Tracking
> down the resultant data corruption is extremely difficult to do.
> It is not possible to write a trigger that will detect all deadlocks and
> inform you when they occur. The trigger containing the detection and
> notification code may not fire if the deadlock occurs while another trigge
r
> on the same table is executing.
> "raghu veer" <raghuveer@.discussions.microsoft.com> wrote in message
> news:3EEECEDC-C8D4-4F76-8C44-AE290DF52D95@.microsoft.com...
>
>|||i executed both now there is no errror
tomorrow i will execute them and let u know
thankssssssssssssssssssssssssssss
"R.D" wrote:
> Raghu
> Follow these steps in dev node.
> 1)DBCC TRACEON(3604)
> 2)DBCC TRACEON(1204)
> 3) RUN SPs that are raising deadlocks
> 4) you can analyse the results and find out which object is becoming dead
> lock victim and even which line of sp is causing deadlock
> 5) attack query
> If you have problem in analysing and finding the object Post results here
> Regards
> R.D
>
> "Brian Selzer" wrote:

pLEASE HELP:URGENT please

I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
NikiIt automatically fires. Here's a rough example:
create trigger tru_MyTable on MyTable
after update
as
if @.@.ROWCOUNT = 0
return
insert MyOtherTable
select
*
from
inserted
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"nikila" <nikilav@.yahoo.com> wrote in message
news:O%232LMtoVFHA.1384@.TK2MSFTNGP09.phx.gbl...
I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
Niki|||Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored procedu
re
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/d... />
2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"nikila" wrote:

> I am new to sql server . I have to write a trigger to insert the data int
o
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when firs
t
> table gets updated?
> Thanks in advance
> Niki
>
>|||Uh, this trigger would insert the entire contents of TABLE-1 into TABLE-2
every time TABLE-1 was updated.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:A4F71FF9-CD9F-45B7-A95F-C728E89B156D@.microsoft.com...
Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored
procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/d... />
2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"nikila" wrote:

> I am new to sql server . I have to write a trigger to insert the data
> into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when
> first
> table gets updated?
> Thanks in advance
> Niki
>
>

pLEASE HELP:URGENT please

I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
NikiIt automatically fires. Here's a rough example:
create trigger tru_MyTable on MyTable
after update
as
if @.@.ROWCOUNT = 0
return
insert MyOtherTable
select
*
from
inserted
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"nikila" <nikilav@.yahoo.com> wrote in message
news:O%232LMtoVFHA.1384@.TK2MSFTNGP09.phx.gbl...
I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
Niki|||Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"nikila" wrote:
> I am new to sql server . I have to write a trigger to insert the data into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when first
> table gets updated?
> Thanks in advance
> Niki
>
>|||Uh, this trigger would insert the entire contents of TABLE-1 into TABLE-2
every time TABLE-1 was updated.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:A4F71FF9-CD9F-45B7-A95F-C728E89B156D@.microsoft.com...
Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored
procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"nikila" wrote:
> I am new to sql server . I have to write a trigger to insert the data
> into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when
> first
> table gets updated?
> Thanks in advance
> Niki
>
>

pLEASE HELP:URGENT please

I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
Niki
It automatically fires. Here's a rough example:
create trigger tru_MyTable on MyTable
after update
as
if @.@.ROWCOUNT = 0
return
insert MyOtherTable
select
*
from
inserted
go
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"nikila" <nikilav@.yahoo.com> wrote in message
news:O%232LMtoVFHA.1384@.TK2MSFTNGP09.phx.gbl...
I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
Niki
|||Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/de...eate2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
"nikila" wrote:

> I am new to sql server . I have to write a trigger to insert the data into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when first
> table gets updated?
> Thanks in advance
> Niki
>
>
|||Uh, this trigger would insert the entire contents of TABLE-1 into TABLE-2
every time TABLE-1 was updated.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:A4F71FF9-CD9F-45B7-A95F-C728E89B156D@.microsoft.com...
Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored
procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/de...eate2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
"nikila" wrote:

> I am new to sql server . I have to write a trigger to insert the data
> into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when
> first
> table gets updated?
> Thanks in advance
> Niki
>
>

Please help: SQL Server Saving performance problem.

Dear Sir,
I have a program to write 30000 record to the database every minute
during daily operation. The program complete to save 30000 records in 30
sconds under normal sitaution. At night, all store data will be backup to
another table and truncate the table. But after running the program several
days, I find that the program need take about 3-5 minutes to save 30000
records completely even the table is empty. Actaully, all external
sitautions is unchanged such as number of user to access the table, the
amount of read data...etc. The performance is down so much. Then I need
shut down the SQK Server Service and then start it again. Then , it will
fine again.
WHy? How can I make sure that the saving can finish withthin 1 minute?
Regards,
Anthony Lam
Just a thought...
Could it be an autogrow kicking in, which leads to the load wait?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> Dear Sir,
> I have a program to write 30000 record to the database every minute
> during daily operation. The program complete to save 30000 records in 30
> sconds under normal sitaution. At night, all store data will be backup to
> another table and truncate the table. But after running the program several
> days, I find that the program need take about 3-5 minutes to save 30000
> records completely even the table is empty. Actaully, all external
> sitautions is unchanged such as number of user to access the table, the
> amount of read data...etc. The performance is down so much. Then I need
> shut down the SQK Server Service and then start it again. Then , it will
> fine again.
> WHy? How can I make sure that the saving can finish withthin 1 minute?
> --
> Regards,
> Anthony Lam
>
|||yes, autogrow for the db and log without restrict.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> bl
news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl g...
> Just a thought...
> Could it be an autogrow kicking in, which leads to the load wait?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AA" <anthony@.jadeflex.com> wrote in message
news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
to[vbcol=seagreen]
several[vbcol=seagreen]
need
>
|||So that could potentially be the reason. Growing a file takes time, and new inserts are blocked until the
autogrow is finished. I'd consider pre-allocating storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:epXeBhgPEHA.1048@.tk2msftngp13.phx.gbl...
> yes, autogrow for the db and log without restrict.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> bl
> news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl g...
> news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> to
> several
> need
>

Please help: SQL Server Saving performance problem.

Dear Sir,
I have a program to write 30000 record to the database every minute
during daily operation. The program complete to save 30000 records in 30
sconds under normal sitaution. At night, all store data will be backup to
another table and truncate the table. But after running the program several
days, I find that the program need take about 3-5 minutes to save 30000
records completely even the table is empty. Actaully, all external
sitautions is unchanged such as number of user to access the table, the
amount of read data...etc. The performance is down so much. Then I need
shut down the SQK Server Service and then start it again. Then , it will
fine again.
WHy? How can I make sure that the saving can finish withthin 1 minute?
--
Regards,
Anthony LamJust a thought...
Could it be an autogrow kicking in, which leads to the load wait?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> Dear Sir,
> I have a program to write 30000 record to the database every minute
> during daily operation. The program complete to save 30000 records in 30
> sconds under normal sitaution. At night, all store data will be backup to
> another table and truncate the table. But after running the program several
> days, I find that the program need take about 3-5 minutes to save 30000
> records completely even the table is empty. Actaully, all external
> sitautions is unchanged such as number of user to access the table, the
> amount of read data...etc. The performance is down so much. Then I need
> shut down the SQK Server Service and then start it again. Then , it will
> fine again.
> WHy? How can I make sure that the saving can finish withthin 1 minute?
> --
> Regards,
> Anthony Lam
>|||yes, autogrow for the db and log without restrict.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l¥ó
news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl ¤¤¼¶¼g...
> Just a thought...
> Could it be an autogrow kicking in, which leads to the load wait?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AA" <anthony@.jadeflex.com> wrote in message
news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > Dear Sir,
> > I have a program to write 30000 record to the database every minute
> > during daily operation. The program complete to save 30000 records in 30
> > sconds under normal sitaution. At night, all store data will be backup
to
> > another table and truncate the table. But after running the program
several
> > days, I find that the program need take about 3-5 minutes to save 30000
> > records completely even the table is empty. Actaully, all external
> > sitautions is unchanged such as number of user to access the table, the
> > amount of read data...etc. The performance is down so much. Then I
need
> > shut down the SQK Server Service and then start it again. Then , it will
> > fine again.
> > WHy? How can I make sure that the saving can finish withthin 1 minute?
> >
> > --
> > Regards,
> >
> > Anthony Lam
> >
> >
>|||So that could potentially be the reason. Growing a file takes time, and new inserts are blocked until the
autogrow is finished. I'd consider pre-allocating storage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:epXeBhgPEHA.1048@.tk2msftngp13.phx.gbl...
> yes, autogrow for the db and log without restrict.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l¥ó
> news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl ¤¤¼¶¼g...
> > Just a thought...
> >
> > Could it be an autogrow kicking in, which leads to the load wait?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "AA" <anthony@.jadeflex.com> wrote in message
> news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > > Dear Sir,
> > > I have a program to write 30000 record to the database every minute
> > > during daily operation. The program complete to save 30000 records in 30
> > > sconds under normal sitaution. At night, all store data will be backup
> to
> > > another table and truncate the table. But after running the program
> several
> > > days, I find that the program need take about 3-5 minutes to save 30000
> > > records completely even the table is empty. Actaully, all external
> > > sitautions is unchanged such as number of user to access the table, the
> > > amount of read data...etc. The performance is down so much. Then I
> need
> > > shut down the SQK Server Service and then start it again. Then , it will
> > > fine again.
> > > WHy? How can I make sure that the saving can finish withthin 1 minute?
> > >
> > > --
> > > Regards,
> > >
> > > Anthony Lam
> > >
> > >
> >
> >
>

Please help: SQL Server Saving performance problem.

Dear Sir,
I have a program to write 30000 record to the database every minute
during daily operation. The program complete to save 30000 records in 30
sconds under normal sitaution. At night, all store data will be backup to
another table and truncate the table. But after running the program several
days, I find that the program need take about 3-5 minutes to save 30000
records completely even the table is empty. Actaully, all external
sitautions is unchanged such as number of user to access the table, the
amount of read data...etc. The performance is down so much. Then I need
shut down the SQK Server Service and then start it again. Then , it will
fine again.
WHy? How can I make sure that the saving can finish withthin 1 minute?
Regards,
Anthony LamJust a thought...
Could it be an autogrow kicking in, which leads to the load wait?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...[v
bcol=seagreen]
> Dear Sir,
> I have a program to write 30000 record to the database every minute
> during daily operation. The program complete to save 30000 records in 30
> sconds under normal sitaution. At night, all store data will be backup to
> another table and truncate the table. But after running the program severa
l
> days, I find that the program need take about 3-5 minutes to save 30000
> records completely even the table is empty. Actaully, all external
> sitautions is unchanged such as number of user to access the table, the
> amount of read data...etc. The performance is down so much. Then I need
> shut down the SQK Server Service and then start it again. Then , it will
> fine again.
> WHy? How can I make sure that the saving can finish withthin 1 minute?
> --
> Regards,
> Anthony Lam
>[/vbcol]|||yes, autogrow for the db and log without restrict.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> bl
news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl g...
> Just a thought...
> Could it be an autogrow kicking in, which leads to the load wait?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AA" <anthony@.jadeflex.com> wrote in message
news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
to[vbcol=seagreen]
several[vbcol=seagreen]
need[vbcol=seagreen]
>|||So that could potentially be the reason. Growing a file takes time, and new
inserts are blocked until the
autogrow is finished. I'd consider pre-allocating storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AA" <anthony@.jadeflex.com> wrote in message news:epXeBhgPEHA.1048@.tk2msftngp13.phx.gbl...[v
bcol=seagreen]
> yes, autogrow for the db and log without restrict.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> bl
> news:uJJf6AYPEHA.272@.TK2MSFTNGP12.phx.gbl g...
> news:OrolLuXPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> to
> several
> need
>[/vbcol]

Please help, Dynamic search stored proc.

Please help, I am trying to write a dynamic search stored procedure using three fields.
I use the same logic on the front end using VB to SQL server which works fine, but never tried a stored procedure with the logic.
Can you please help me, construct a stored procedure.
User can choose any of the three(progno, projno, contractno) fields as a where condition.

I ma using asp.net as front end with sql server backend.

CREATE PROCEDURE dbo.USP_Searchrecords
(@.ProgNO nvarchar(50),
 @.ProjNOnvarchar(50) ,
 @.ContractNOnvarchar(50))
AS
DECLARE @.myselect nvarchar(2000)
DECLARE @.psql nvarchar(2000)
DECLARE @.strsql nvarchar(2000)
SET NOCOUNT ON
@.psql = "SELECT * FROM Mytable"
IF @.ProgNO <> '' then
strsql = WHERE ProgNO = @.ProgNO
end if
If @.ProjNO <> '' then
if strsql <> '' then
strsql = strsql & " and ProjNO =@.ProjNO
ELSE
strsql = wHERE ProjNO =@.ProjNO
END IF
END IF
If @.ContractNO <> '' then
if strsql <> '' then
strsql = strsql & " and ContractNO =@.ContractNO
ELSE
strsql = wHERE ContractNO =@.ContractNO
END IF
END IF
@.myselect = @.psql + @.strsql
EXEC(@.myselect)

Please help. Thank you very much.CREATE PROCEDURE dbo.USP_Searchrecords

@.ProgNO nvarchar(50) = null,
@.ProjNOnvarchar(50) = null ,
@.ContractNOnvarchar(50) = null

AS
select * from mytable
where
(
((@.ProgNO is null) or (progNo = @.ProgNO))
AND
((@.ProjNO is null) or (ProjNo = @.ProjNO))
AND
((@.ContractNO is null) or (ContractNo = @.ContractNO))
)|||Do NOT do this.

::CREATE PROCEDURE dbo.USP_Searchrecords

should have a WITH RECOMPILE option here.

Problem is: Depending on the parameters you will get totally different access paths. Without indicating these should be evaluated EVERY TIME, the FIRST access path will be reused, EVEN if it is totally ridiculous given the exact parameters. This will result in awfully slow queries.|||>>should have a WITH RECOMPILE option here

Thona points out a great reason NOT to use Dynamic SQL if you dont have to.

Having an sp precompiled is one of the main performance benefits of using stored procedures - Unless you make changes to the table structure, introduce new indexes, or use Dynamic SQL you should'nt need to recompile your stored procedures.

Please help! SQL Server crash dump

Help,
A server became unresponsive yesterday, starting with disk write errors that
we've seen before,
but then around 9am this morning a crash dump was written.
Can anybody help me to chase this down. I think the best resolution is PSS,
but I'd like to get
as much info as I can before making a call.
2003-07-30 16:40:40.90 server Error: 17883, Severity: 1, State:
2003-07-30 16:42:19.68 spid54 Time out occurred while waiting for buffer
latch type 2,bp 0x278e640, page 1:4425087), stat 0x40d, object ID
46:517576882:0, EC 0x5973F580 : 0, waittime 300. Not continuing to wait.
2003-07-30 16:40:40.90 server Process 0:0 (b18) UMS Context 0x12863740
appears to be non-yielding on Scheduler
2003-07-31 09:00:35.96 spid54 Waiting for type 0x2, current count
0x100022, current owning EC 0x5973F580.
2003-07-31 09:00:37.64 server Sleeping until external dump process
completes.
2003-07-31 09:00:46.53 server Resuming after waiting on external debug
process for 2 seconds.
2003-07-31 09:00:46.53 server Stack Signature for the dump is 0x00000000
TIA.Have a look at
PRB: Common Causes of Error Message 844 or Error Message 845 (Buffer Latch
Time Out Errors)
http://support.microsoft.com/default.aspx?scid=kb;en-us;310834
and also
INF: New Concurrency and Scheduling Diagnostics Added to SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;319892
which contains this note about 17883 errors
"Microsoft tries to keep all content up-to-date with the latest 17883
conditions. However, the 17883 error message is a health detection message
that can be triggered for many reasons. Microsoft has not only corrected
known issues with the SQL Server software product but has also encountered
the 17883 error in a variety of situations that are unrelated to the SQL
Server software. For example, the error has occurred with external
application CPU consumption and hardware failures. You must determine the
root cause of the 17883 error message if you want to avoid an unwanted
reoccurrence of the error. "
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Stressed" <k@.c.co.uk> wrote in message
news:uQ4C7a0VDHA.2156@.TK2MSFTNGP11.phx.gbl...
Help,
A server became unresponsive yesterday, starting with disk write errors that
we've seen before,
but then around 9am this morning a crash dump was written.
Can anybody help me to chase this down. I think the best resolution is PSS,
but I'd like to get
as much info as I can before making a call.
2003-07-30 16:40:40.90 server Error: 17883, Severity: 1, State:
2003-07-30 16:42:19.68 spid54 Time out occurred while waiting for buffer
latch type 2,bp 0x278e640, page 1:4425087), stat 0x40d, object ID
46:517576882:0, EC 0x5973F580 : 0, waittime 300. Not continuing to wait.
2003-07-30 16:40:40.90 server Process 0:0 (b18) UMS Context 0x12863740
appears to be non-yielding on Scheduler
2003-07-31 09:00:35.96 spid54 Waiting for type 0x2, current count
0x100022, current owning EC 0x5973F580.
2003-07-31 09:00:37.64 server Sleeping until external dump process
completes.
2003-07-31 09:00:46.53 server Resuming after waiting on external debug
process for 2 seconds.
2003-07-31 09:00:46.53 server Stack Signature for the dump is 0x00000000
TIA.