Print in Trigger Statements.....

SQL Server books online has 'print' statements in the trigger examples.
Where exactly the print will happen? How to get benefit of this while
debugging?
Thanks,
Jessy
You can make execution visible in the query analyzer, can print out some
information about filled variables, rowcounts, status of transactions etc.
While debugging you can also watch the value of the variable with the
properties pane.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Jessy Martin" <Jessy_Smith79@.hotmail.com> schrieb im Newsbeitrag
news:%23MweP25ZFHA.616@.TK2MSFTNGP12.phx.gbl...
> SQL Server books online has 'print' statements in the trigger examples.
> Where exactly the print will happen? How to get benefit of this while
> debugging?
> Thanks,
> Jessy
>

Print in Trigger Statements.....

SQL Server books online has 'print' statements in the trigger examples.
Where exactly the print will happen? How to get benefit of this while
debugging?
Thanks,
JessyYou can make execution visible in the query analyzer, can print out some
information about filled variables, rowcounts, status of transactions etc.
While debugging you can also watch the value of the variable with the
properties pane.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jessy Martin" <Jessy_Smith79@.hotmail.com> schrieb im Newsbeitrag
news:%23MweP25ZFHA.616@.TK2MSFTNGP12.phx.gbl...
> SQL Server books online has 'print' statements in the trigger examples.
> Where exactly the print will happen? How to get benefit of this while
> debugging?
> Thanks,
> Jessy
>sql

Print in Trigger Statements.....

SQL Server books online has 'print' statements in the trigger examples.
Where exactly the print will happen? How to get benefit of this while
debugging?
Thanks,
JessyYou can make execution visible in the query analyzer, can print out some
information about filled variables, rowcounts, status of transactions etc.
While debugging you can also watch the value of the variable with the
properties pane.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Jessy Martin" <Jessy_Smith79@.hotmail.com> schrieb im Newsbeitrag
news:%23MweP25ZFHA.616@.TK2MSFTNGP12.phx.gbl...
> SQL Server books online has 'print' statements in the trigger examples.
> Where exactly the print will happen? How to get benefit of this while
> debugging?
> Thanks,
> Jessy
>

Primary key clustered with / without constraint (question)

HI,
I have some difficulty to understand the difference bettween those to
statements.
When do we specify "constraint" and when we don't need to ?
What is the pro and con of specifying "contraint"
Does anyone can guide me ?
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD
PRIMARY KEY CLUSTERED
----
--
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD CONSTRAINT [PK__AB000_field__07C12930]
PRIMARY KEY CLUSTERED
Thank you
DannyHi,
When do we specify "constraint" and when we don't need to ?
If you specifiy CONSTRAINT we can give our own contrain names, otherwise
system will generate its own contrain name starting with (PK_TABLENAME...)
What is the pro and con of specifying "contraint"?
There is no majour diffrence apart form name definition. (NO Pros and cons-
both are same)
Thanks
Hari
MCDBA
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:eAq$ZEGOEHA.3380@.TK2MSFTNGP11.phx.gbl...
> HI,
> I have some difficulty to understand the difference bettween those to
> statements.
> When do we specify "constraint" and when we don't need to ?
> What is the pro and con of specifying "contraint"
> Does anyone can guide me ?
>
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD
> PRIMARY KEY CLUSTERED
>
> ----
--
> --
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD CONSTRAINT [PK__AB000_field__07C12930]
> PRIMARY KEY CLUSTERED
>
> Thank you
>
> Danny
>

Price of using multiple databases in SELECT statements

Hi,

We are discussing possible implementation for sql2005 database(s). This database will serve one web portal. Part of data will get into it by hand, and part will be replicated from internal system.

Some of us are for creating two separate databases, since there are two separate datasources. One, automatic, will change very little over time and requires almost no maintenance. Other datasource will be manual input. Tables and procedures related to this part will change over time.

Some of us are for creating single database, since it will serve one web site. More important this group is concerned about performance issues since almost every select will require join between tables that would be stored in two separate databases. Do these issues exist?

Can you share some insights, comments, links about this?

Hi,

We are using multiple databases in our many application, and have not found an excuse so far that we should terminate that practice. Most of our modules gather data from different databases and operate on them. I think a little performance overhead is there, which can be ignored.

As every approach has its pros and cons. But we think that this segregation provides us opportunity to

- encapsulate different type of data in separate locations,

- which can be backed up easily,

- can be distributed at different locations,

- due to lesser size data would be fetched rather fast,

- unorganized data could be separaterd.

Whats your take on it?

|||

We too use multiple databases (on the same server, of course) and join across them -- there is no performance penalty that we've ever seen.

In our situation, we have a vendor package around which we've written an extended application. The vendor package uses one database and our extension uses another, but the two are intimately linked and we have views in the extention database pointing back to the vendor packages database (making it very easy to code. I recommend that you too use views to make it appear as though it's one big database.

Preventing Deadlock

Trace 1204 has narrowed the cause of deadlock to these delete statements.
There are a total of 10 delete statements in this SP.
create procedure del_emp @.empid int
Delete from tbl_employees
where empid in (Select empid from tbl_location where empid = @.empid)
Delete from tbl_management
where mge_ID in (Select mge_ID from tbl_sunmanagement where empid = @.empid)
......
......
Questions:
1. should i add (nolock) within the select statement? ex: Select empid from
tbl_location WITH (NOLOCK)
2. should i add (updlock) within the delete statement? ex: Delete from
tbl_employees WITH (UPDLOCK)
Any suggestions highly appreciated.Hi
You don't say what is being blocked and what it causing the blocking! You
may be missing an index, which would help, check out the query plan to see if
this is so. Will the empid's in tbl_location be anything other than @.empid?
Do you explicitly start a transaction outside this procedure?
John
"morphius" wrote:
> Trace 1204 has narrowed the cause of deadlock to these delete statements.
> There are a total of 10 delete statements in this SP.
> create procedure del_emp @.empid int
> Delete from tbl_employees
> where empid in (Select empid from tbl_location where empid = @.empid)
> Delete from tbl_management
> where mge_ID in (Select mge_ID from tbl_sunmanagement where empid = @.empid)
> ......
> ......
> Questions:
> 1. should i add (nolock) within the select statement? ex: Select empid from
> tbl_location WITH (NOLOCK)
> 2. should i add (updlock) within the delete statement? ex: Delete from
> tbl_employees WITH (UPDLOCK)
> Any suggestions highly appreciated.
>
>|||Hi John,
Thanks for the reply. The empid in tbl_location is not anything other than
the empid. Yes a transaction is explicitly started outside this procedure and
between 100-300 users could be using the transaction simultaneously.
"John Bell" wrote:
> Hi
> You don't say what is being blocked and what it causing the blocking! You
> may be missing an index, which would help, check out the query plan to see if
> this is so. Will the empid's in tbl_location be anything other than @.empid?
> Do you explicitly start a transaction outside this procedure?
> John
> "morphius" wrote:
> > Trace 1204 has narrowed the cause of deadlock to these delete statements.
> > There are a total of 10 delete statements in this SP.
> >
> > create procedure del_emp @.empid int
> >
> > Delete from tbl_employees
> > where empid in (Select empid from tbl_location where empid = @.empid)
> >
> > Delete from tbl_management
> > where mge_ID in (Select mge_ID from tbl_sunmanagement where empid = @.empid)
> >
> > ......
> > ......
> >
> > Questions:
> > 1. should i add (nolock) within the select statement? ex: Select empid from
> > tbl_location WITH (NOLOCK)
> > 2. should i add (updlock) within the delete statement? ex: Delete from
> > tbl_employees WITH (UPDLOCK)
> >
> > Any suggestions highly appreciated.
> >
> >
> >
> >|||Hi
If I understand you correctly, then there is no need to query table location
and you could just use
Delete from tbl_employees
where empid = @.empid
or is was there a typo or misunderstanding?
If no other code is in the transaction other than the stored procedure call
(i.e. two delete statements) then you would need to look at the query plans.
John
"morphius" wrote:
> Hi John,
> Thanks for the reply. The empid in tbl_location is not anything other than
> the empid. Yes a transaction is explicitly started outside this procedure and
> between 100-300 users could be using the transaction simultaneously.
> "John Bell" wrote:
> > Hi
> >
> > You don't say what is being blocked and what it causing the blocking! You
> > may be missing an index, which would help, check out the query plan to see if
> > this is so. Will the empid's in tbl_location be anything other than @.empid?
> > Do you explicitly start a transaction outside this procedure?
> >
> > John
> >
> > "morphius" wrote:
> >
> > > Trace 1204 has narrowed the cause of deadlock to these delete statements.
> > > There are a total of 10 delete statements in this SP.
> > >
> > > create procedure del_emp @.empid int
> > >
> > > Delete from tbl_employees
> > > where empid in (Select empid from tbl_location where empid = @.empid)
> > >
> > > Delete from tbl_management
> > > where mge_ID in (Select mge_ID from tbl_sunmanagement where empid = @.empid)
> > >
> > > ......
> > > ......
> > >
> > > Questions:
> > > 1. should i add (nolock) within the select statement? ex: Select empid from
> > > tbl_location WITH (NOLOCK)
> > > 2. should i add (updlock) within the delete statement? ex: Delete from
> > > tbl_employees WITH (UPDLOCK)
> > >
> > > Any suggestions highly appreciated.
> > >
> > >
> > >
> > >