Connections

I've got a satellite group that acts as their own dba's on
a 7.0 server. They claim that they cannot kill certain
connections, i.e. they typed in kill and nothing
happened. I have seen that happen with web-based
connection pooling apps but not with a regular app that
they're working with.
Anyway, what I wanted to ask is: is the kill command the
only way to kill a connection besides the winner-take-all
methods of taking the database offline or stopping and
restarting services? Just wondering if there's some magic
bullet that I hadn't heard of.Kill can only be used to terminate user processes (typically spid>50) that
is not executing an extended procedure. Perhaps, that's what they're seeing.
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
> I've got a satellite group that acts as their own dba's on
> a 7.0 server. They claim that they cannot kill certain
> connections, i.e. they typed in kill and nothing
> happened. I have seen that happen with web-based
> connection pooling apps but not with a regular app that
> they're working with.
> Anyway, what I wanted to ask is: is the kill command the
> only way to kill a connection besides the winner-take-all
> methods of taking the database offline or stopping and
> restarting services? Just wondering if there's some magic
> bullet that I hadn't heard of.|||Thx but from what they're saying, it was definitely a user
process.
>--Original Message--
>Kill can only be used to terminate user processes
(typically spid>50) that
>is not executing an extended procedure. Perhaps, that's
what they're seeing.
>
>"NTel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
>> I've got a satellite group that acts as their own dba's
on
>> a 7.0 server. They claim that they cannot kill certain
>> connections, i.e. they typed in kill and nothing
>> happened. I have seen that happen with web-based
>> connection pooling apps but not with a regular app that
>> they're working with.
>> Anyway, what I wanted to ask is: is the kill command
the
>> only way to kill a connection besides the winner-take-
all
>> methods of taking the database offline or stopping and
>> restarting services? Just wondering if there's some
magic
>> bullet that I hadn't heard of.
>
>.
>|||Sometimes you get "ghost connections". There are some reasons this can happen, the connection
executing an extended stored procedure is one such possibility. There can be other things as well,
but this has been getting better with versions and service packs.
Note that the connection might be in the middle of a large rollback, and it will not die until the
rollback is done.
No magic bullets here. Wait for a possible rollback to complete, if you feel certain you have waited
long enough, recycling the SQL Server is the way to go.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3c8901c48fae$fcf54370$a301280a@.phx.gbl...
> Thx but from what they're saying, it was definitely a user
> process.
> >--Original Message--
> >Kill can only be used to terminate user processes
> (typically spid>50) that
> >is not executing an extended procedure. Perhaps, that's
> what they're seeing.
> >
> >
> >"NTel" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
> >> I've got a satellite group that acts as their own dba's
> on
> >> a 7.0 server. They claim that they cannot kill certain
> >> connections, i.e. they typed in kill and nothing
> >> happened. I have seen that happen with web-based
> >> connection pooling apps but not with a regular app that
> >> they're working with.
> >>
> >> Anyway, what I wanted to ask is: is the kill command
> the
> >> only way to kill a connection besides the winner-take-
> all
> >> methods of taking the database offline or stopping and
> >> restarting services? Just wondering if there's some
> magic
> >> bullet that I hadn't heard of.
> >
> >
> >.
> >

Connection String Options ...Urgent .. plz reply

Hi

In my project , we are using Dsn and DSN less connection, for certain functionality

we are providing users to select tables and views .

we don't want that all system tables and views are listed for selecting , how can we achieve this functionality?

Is there any options in the connection string for restricting system tables and views?

Any help is much appriciated

Thanks

Saurabh

Book mark the following site;

http://www.connectionstrings.com/

Connection specific variable?

Hi,
i have several triggers which under certain circumstances i dont want to
run. I would like to be able to set a variable against the sql connection
which i can test for within my trigger and then prevent running if certain
conditions apply. Is this possible? without using connection specific
tables? is it even possible with them?
--
TimBHi Tim,
Can you explain why you don't want the triggers to run? If it is for a
maintenance or administration task you can turn the triggers off with ALTER
TABLE <table name> DISABLE TRIGGER ALL, and enable them later with ALTER
TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable the
triggers for all users, not just the current connection, and that you need
to be a member of the sysadmin, db_ddladmin or db_owner roles to use it.
If you want to do it in a different scenario, triggers might not be the
right solution and you can be better off putting your code in a stored
procedure. Can you give some more information in that case?
Jacco Schalkwijk
SQL Server MVP
"timb" <timb@.test.com> wrote in message
news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi,
> i have several triggers which under certain circumstances i dont want

---

to

quote:

---

> run. I would like to be able to set a variable against the sql connection
> which i can test for within my trigger and then prevent running if certain
> conditions apply. Is this possible? without using connection specific
> tables? is it even possible with them?
> --
> --
> TimB
>

---

|||There are certain routines within my vb app which need to skip certain
sections of a trigger as they cater for the code elsewhere.
I have tried to use a temporatry table however how do i check that the table
exists befreo selecting from it. i.e. to prevent an error being raised when
the table is not present.
I have also found that you can create a @.@. variable but cannot currently get
it to persist (if i am using it correctly) and there is no help about
creating these.
Is it possible to create a connection specific variable?
thanks in advance
TimB
--
TimB[490134]
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OA0TQHN4DHA.2348@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Tim,
> Can you explain why you don't want the triggers to run? If it is for a
> maintenance or administration task you can turn the triggers off with

---

ALTER

quote:

---

> TABLE <table name> DISABLE TRIGGER ALL, and enable them later with ALTER
> TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable the
> triggers for all users, not just the current connection, and that you need
> to be a member of the sysadmin, db_ddladmin or db_owner roles to use it.
> If you want to do it in a different scenario, triggers might not be the
> right solution and you can be better off putting your code in a stored
> procedure. Can you give some more information in that case?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "timb" <timb@.test.com> wrote in message
> news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...
want[QUOTE]
> to
connection[QUOTE]
certain[QUOTE]
>

---

|||Not that I recommend the following solution, but we implemented it to avoid
(temporarily) having to rewrite a rather large chunk of the application:
CREATE TABLE dbo.session_role (
spid int NOT NULL ,
role_id int NOT NULL ,
CONSTRAINT PK_session_context PRIMARY KEY CLUSTERED ( spid ),
CONSTRAINT FK_session_context_role FOREIGN KEY ( role_id ) REFERENCES
dbo.role_definitions ( id )
)
END
GO
CREATE PROCEDURE SetSessionRole @.nRoleID INT
AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM session_role WHERE spid = @.@.spid)
UPDATE session_role
SET role_id = @.nRoleID
WHERE spid = @.@.spid
ELSE
INSERT INTO session_role (spid, role_id)
VALUES (@.@.spid, @.nRoleID)
GO
You can now check in stored procedures, views and triggers for the role_id
with SELECT role_id FROM session_role WHERE spid = @.@.spid.
As I said before, this is not a good structural solution, but deadlines were
looming etc. Rewrite your application code at the earliest opportunity
possible.
Jacco Schalkwijk
SQL Server MVP
"timb" <timb@.test.com> wrote in message
news:%23bTatMN4DHA.2440@.TK2MSFTNGP09.phx.gbl...

quote:

---

> There are certain routines within my vb app which need to skip certain
> sections of a trigger as they cater for the code elsewhere.
> I have tried to use a temporatry table however how do i check that the

---

table

quote:

---

> exists befreo selecting from it. i.e. to prevent an error being raised

---

when

quote:

---

> the table is not present.
> I have also found that you can create a @.@. variable but cannot currently

---

get

quote:

---

> it to persist (if i am using it correctly) and there is no help about
> creating these.
> Is it possible to create a connection specific variable?
> thanks in advance
> TimB
> --
> --
> TimB[490134]
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OA0TQHN4DHA.2348@.TK2MSFTNGP09.phx.gbl...
> ALTER
need[QUOTE]
> want
> connection
> certain
>

---

|||Hi Jacco,
thanks for the help. I was thinking of something similar however
using temporary tables i.e. #
what i was going to do was create the temporary table in the area where the
first transaction occurs and then delete the table or other such logic in
the specific part of the trigger which may be called as a result of the
first transaction.
Only thing is how do i check for the presence of this temporary table?
i.e.
Create Table #Stocktake
(
stocktake bit
)
insert into #stocktake values (1)
if exists (select * from tempdb.dbo.sysobjects where id =
object_id(N'[dbo].[#stocktake]')) print 'table exists'
i cannot get the statement to print and i want to trap the error which will
occur in the trigger when it is called from transactions which dont have the
code to create the temporary table.
thanks in advance
TimB
--
TimB[490134]
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23CmZHiN4DHA.1948@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Not that I recommend the following solution, but we implemented it to

---

avoid

quote:

---

> (temporarily) having to rewrite a rather large chunk of the application:
> CREATE TABLE dbo.session_role (
> spid int NOT NULL ,
> role_id int NOT NULL ,
> CONSTRAINT PK_session_context PRIMARY KEY CLUSTERED ( spid ),
> CONSTRAINT FK_session_context_role FOREIGN KEY ( role_id ) REFERENCES
> dbo.role_definitions ( id )
> )
> END
> GO
> CREATE PROCEDURE SetSessionRole @.nRoleID INT
> AS
> SET NOCOUNT ON
> IF EXISTS(SELECT * FROM session_role WHERE spid = @.@.spid)
> UPDATE session_role
> SET role_id = @.nRoleID
> WHERE spid = @.@.spid
> ELSE
> INSERT INTO session_role (spid, role_id)
> VALUES (@.@.spid, @.nRoleID)
> GO
> You can now check in stored procedures, views and triggers for the role_id
> with SELECT role_id FROM session_role WHERE spid = @.@.spid.
> As I said before, this is not a good structural solution, but deadlines

---

were

quote:

---

> looming etc. Rewrite your application code at the earliest opportunity
> possible.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "timb" <timb@.test.com> wrote in message
> news:%23bTatMN4DHA.2440@.TK2MSFTNGP09.phx.gbl...
> table
> when
> get
ALTER[QUOTE]
> need
it.[QUOTE]
the[QUOTE]
specific[QUOTE]
>

---

|||> Only thing is how do i check for the presence of this temporary table?
Try:
IF OBJECT_ID('tempdb..#Stocktake') IS NOT NULL
PRINT 'table exists'
Hope this helps.
Dan Guzman
SQL Server MVP
"timb" <timb@.test.com> wrote in message
news:O8NWaqP4DHA.3596@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi Jacco,
> thanks for the help. I was thinking of something similar however
> using temporary tables i.e. #
> what i was going to do was create the temporary table in the area where

---

the

quote:

---

> first transaction occurs and then delete the table or other such logic in
> the specific part of the trigger which may be called as a result of the
> first transaction.
> Only thing is how do i check for the presence of this temporary table?
> i.e.
>
> Create Table #Stocktake
> (
> stocktake bit
> )
> insert into #stocktake values (1)
>
> if exists (select * from tempdb.dbo.sysobjects where id =
> object_id(N'[dbo].[#stocktake]')) print 'table exists'
>
> i cannot get the statement to print and i want to trap the error which

---

will

quote:

---

> occur in the trigger when it is called from transactions which dont have

---

the

quote:

---

> code to create the temporary table.
>
> thanks in advance
> TimB
>
> --
> --
> TimB[490134]
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23CmZHiN4DHA.1948@.TK2MSFTNGP12.phx.gbl...
> avoid
role_id[QUOTE]
> were
raised[QUOTE]
currently[QUOTE]
a[QUOTE]
with[QUOTE]
> ALTER
the[QUOTE]
you[QUOTE]
> it.
> the
stored[QUOTE]
dont[QUOTE]
> specific
>

---

Connection specific variable?

Hi,
i have several triggers which under certain circumstances i dont want to
run. I would like to be able to set a variable against the sql connection
which i can test for within my trigger and then prevent running if certain
conditions apply. Is this possible? without using connection specific
tables? is it even possible with them?
--
--
TimBHi Tim,
Can you explain why you don't want the triggers to run? If it is for a
maintenance or administration task you can turn the triggers off with ALTER
TABLE <table name> DISABLE TRIGGER ALL, and enable them later with ALTER
TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable the
triggers for all users, not just the current connection, and that you need
to be a member of the sysadmin, db_ddladmin or db_owner roles to use it.
If you want to do it in a different scenario, triggers might not be the
right solution and you can be better off putting your code in a stored
procedure. Can you give some more information in that case?
--
Jacco Schalkwijk
SQL Server MVP
"timb" <timb@.test.com> wrote in message
news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...
> Hi,
> i have several triggers which under certain circumstances i dont want
to
> run. I would like to be able to set a variable against the sql connection
> which i can test for within my trigger and then prevent running if certain
> conditions apply. Is this possible? without using connection specific
> tables? is it even possible with them?
> --
> --
> TimB
>|||There are certain routines within my vb app which need to skip certain
sections of a trigger as they cater for the code elsewhere.
I have tried to use a temporatry table however how do i check that the table
exists befreo selecting from it. i.e. to prevent an error being raised when
the table is not present.
I have also found that you can create a @.@. variable but cannot currently get
it to persist (if i am using it correctly) and there is no help about
creating these.
Is it possible to create a connection specific variable?
thanks in advance
TimB
--
--
TimB[490134]
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OA0TQHN4DHA.2348@.TK2MSFTNGP09.phx.gbl...
> Hi Tim,
> Can you explain why you don't want the triggers to run? If it is for a
> maintenance or administration task you can turn the triggers off with
ALTER
> TABLE <table name> DISABLE TRIGGER ALL, and enable them later with ALTER
> TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable the
> triggers for all users, not just the current connection, and that you need
> to be a member of the sysadmin, db_ddladmin or db_owner roles to use it.
> If you want to do it in a different scenario, triggers might not be the
> right solution and you can be better off putting your code in a stored
> procedure. Can you give some more information in that case?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "timb" <timb@.test.com> wrote in message
> news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > i have several triggers which under certain circumstances i dont
want
> to
> > run. I would like to be able to set a variable against the sql
connection
> > which i can test for within my trigger and then prevent running if
certain
> > conditions apply. Is this possible? without using connection specific
> > tables? is it even possible with them?
> >
> > --
> > --
> > TimB
> >
> >
>|||Not that I recommend the following solution, but we implemented it to avoid
(temporarily) having to rewrite a rather large chunk of the application:
CREATE TABLE dbo.session_role (
spid int NOT NULL ,
role_id int NOT NULL ,
CONSTRAINT PK_session_context PRIMARY KEY CLUSTERED ( spid ),
CONSTRAINT FK_session_context_role FOREIGN KEY ( role_id ) REFERENCES
dbo.role_definitions ( id )
)
END
GO
CREATE PROCEDURE SetSessionRole @.nRoleID INT
AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM session_role WHERE spid = @.@.spid)
UPDATE session_role
SET role_id = @.nRoleID
WHERE spid = @.@.spid
ELSE
INSERT INTO session_role (spid, role_id)
VALUES (@.@.spid, @.nRoleID)
GO
You can now check in stored procedures, views and triggers for the role_id
with SELECT role_id FROM session_role WHERE spid = @.@.spid.
As I said before, this is not a good structural solution, but deadlines were
looming etc. Rewrite your application code at the earliest opportunity
possible.
Jacco Schalkwijk
SQL Server MVP
"timb" <timb@.test.com> wrote in message
news:%23bTatMN4DHA.2440@.TK2MSFTNGP09.phx.gbl...
> There are certain routines within my vb app which need to skip certain
> sections of a trigger as they cater for the code elsewhere.
> I have tried to use a temporatry table however how do i check that the
table
> exists befreo selecting from it. i.e. to prevent an error being raised
when
> the table is not present.
> I have also found that you can create a @.@. variable but cannot currently
get
> it to persist (if i am using it correctly) and there is no help about
> creating these.
> Is it possible to create a connection specific variable?
> thanks in advance
> TimB
> --
> --
> TimB[490134]
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OA0TQHN4DHA.2348@.TK2MSFTNGP09.phx.gbl...
> > Hi Tim,
> >
> > Can you explain why you don't want the triggers to run? If it is for a
> > maintenance or administration task you can turn the triggers off with
> ALTER
> > TABLE <table name> DISABLE TRIGGER ALL, and enable them later with ALTER
> > TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable the
> > triggers for all users, not just the current connection, and that you
need
> > to be a member of the sysadmin, db_ddladmin or db_owner roles to use it.
> >
> > If you want to do it in a different scenario, triggers might not be the
> > right solution and you can be better off putting your code in a stored
> > procedure. Can you give some more information in that case?
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "timb" <timb@.test.com> wrote in message
> > news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > > i have several triggers which under certain circumstances i dont
> want
> > to
> > > run. I would like to be able to set a variable against the sql
> connection
> > > which i can test for within my trigger and then prevent running if
> certain
> > > conditions apply. Is this possible? without using connection specific
> > > tables? is it even possible with them?
> > >
> > > --
> > > --
> > > TimB
> > >
> > >
> >
> >
>|||Hi Jacco,
thanks for the help. I was thinking of something similar however
using temporary tables i.e. #
what i was going to do was create the temporary table in the area where the
first transaction occurs and then delete the table or other such logic in
the specific part of the trigger which may be called as a result of the
first transaction.
Only thing is how do i check for the presence of this temporary table?
i.e.
Create Table #Stocktake
(
stocktake bit
)
insert into #stocktake values (1)
if exists (select * from tempdb.dbo.sysobjects where id =object_id(N'[dbo].[#stocktake]')) print 'table exists'
i cannot get the statement to print and i want to trap the error which will
occur in the trigger when it is called from transactions which dont have the
code to create the temporary table.
thanks in advance
TimB
--
TimB[490134]
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23CmZHiN4DHA.1948@.TK2MSFTNGP12.phx.gbl...
> Not that I recommend the following solution, but we implemented it to
avoid
> (temporarily) having to rewrite a rather large chunk of the application:
> CREATE TABLE dbo.session_role (
> spid int NOT NULL ,
> role_id int NOT NULL ,
> CONSTRAINT PK_session_context PRIMARY KEY CLUSTERED ( spid ),
> CONSTRAINT FK_session_context_role FOREIGN KEY ( role_id ) REFERENCES
> dbo.role_definitions ( id )
> )
> END
> GO
> CREATE PROCEDURE SetSessionRole @.nRoleID INT
> AS
> SET NOCOUNT ON
> IF EXISTS(SELECT * FROM session_role WHERE spid = @.@.spid)
> UPDATE session_role
> SET role_id = @.nRoleID
> WHERE spid = @.@.spid
> ELSE
> INSERT INTO session_role (spid, role_id)
> VALUES (@.@.spid, @.nRoleID)
> GO
> You can now check in stored procedures, views and triggers for the role_id
> with SELECT role_id FROM session_role WHERE spid = @.@.spid.
> As I said before, this is not a good structural solution, but deadlines
were
> looming etc. Rewrite your application code at the earliest opportunity
> possible.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "timb" <timb@.test.com> wrote in message
> news:%23bTatMN4DHA.2440@.TK2MSFTNGP09.phx.gbl...
> > There are certain routines within my vb app which need to skip certain
> > sections of a trigger as they cater for the code elsewhere.
> >
> > I have tried to use a temporatry table however how do i check that the
> table
> > exists befreo selecting from it. i.e. to prevent an error being raised
> when
> > the table is not present.
> >
> > I have also found that you can create a @.@. variable but cannot currently
> get
> > it to persist (if i am using it correctly) and there is no help about
> > creating these.
> >
> > Is it possible to create a connection specific variable?
> >
> > thanks in advance
> >
> > TimB
> >
> > --
> > --
> > TimB[490134]
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > news:OA0TQHN4DHA.2348@.TK2MSFTNGP09.phx.gbl...
> > > Hi Tim,
> > >
> > > Can you explain why you don't want the triggers to run? If it is for a
> > > maintenance or administration task you can turn the triggers off with
> > ALTER
> > > TABLE <table name> DISABLE TRIGGER ALL, and enable them later with
ALTER
> > > TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable the
> > > triggers for all users, not just the current connection, and that you
> need
> > > to be a member of the sysadmin, db_ddladmin or db_owner roles to use
it.
> > >
> > > If you want to do it in a different scenario, triggers might not be
the
> > > right solution and you can be better off putting your code in a stored
> > > procedure. Can you give some more information in that case?
> > >
> > > --
> > > Jacco Schalkwijk
> > > SQL Server MVP
> > >
> > >
> > > "timb" <timb@.test.com> wrote in message
> > > news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > > i have several triggers which under certain circumstances i dont
> > want
> > > to
> > > > run. I would like to be able to set a variable against the sql
> > connection
> > > > which i can test for within my trigger and then prevent running if
> > certain
> > > > conditions apply. Is this possible? without using connection
specific
> > > > tables? is it even possible with them?
> > > >
> > > > --
> > > > --
> > > > TimB
> > > >
> > > >
> > >
> > >
> >
> >
>|||> Only thing is how do i check for the presence of this temporary table?
Try:
IF OBJECT_ID('tempdb..#Stocktake') IS NOT NULL
PRINT 'table exists'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"timb" <timb@.test.com> wrote in message
news:O8NWaqP4DHA.3596@.TK2MSFTNGP11.phx.gbl...
> Hi Jacco,
> thanks for the help. I was thinking of something similar however
> using temporary tables i.e. #
> what i was going to do was create the temporary table in the area where
the
> first transaction occurs and then delete the table or other such logic in
> the specific part of the trigger which may be called as a result of the
> first transaction.
> Only thing is how do i check for the presence of this temporary table?
> i.e.
>
> Create Table #Stocktake
> (
> stocktake bit
> )
> insert into #stocktake values (1)
>
> if exists (select * from tempdb.dbo.sysobjects where id => object_id(N'[dbo].[#stocktake]')) print 'table exists'
>
> i cannot get the statement to print and i want to trap the error which
will
> occur in the trigger when it is called from transactions which dont have
the
> code to create the temporary table.
>
> thanks in advance
> TimB
>
> --
> --
> TimB[490134]
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23CmZHiN4DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Not that I recommend the following solution, but we implemented it to
> avoid
> > (temporarily) having to rewrite a rather large chunk of the application:
> >
> > CREATE TABLE dbo.session_role (
> > spid int NOT NULL ,
> > role_id int NOT NULL ,
> > CONSTRAINT PK_session_context PRIMARY KEY CLUSTERED ( spid ),
> > CONSTRAINT FK_session_context_role FOREIGN KEY ( role_id ) REFERENCES
> > dbo.role_definitions ( id )
> > )
> > END
> > GO
> > CREATE PROCEDURE SetSessionRole @.nRoleID INT
> > AS
> > SET NOCOUNT ON
> >
> > IF EXISTS(SELECT * FROM session_role WHERE spid = @.@.spid)
> > UPDATE session_role
> > SET role_id = @.nRoleID
> > WHERE spid = @.@.spid
> > ELSE
> > INSERT INTO session_role (spid, role_id)
> > VALUES (@.@.spid, @.nRoleID)
> >
> > GO
> >
> > You can now check in stored procedures, views and triggers for the
role_id
> > with SELECT role_id FROM session_role WHERE spid = @.@.spid.
> >
> > As I said before, this is not a good structural solution, but deadlines
> were
> > looming etc. Rewrite your application code at the earliest opportunity
> > possible.
> >
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "timb" <timb@.test.com> wrote in message
> > news:%23bTatMN4DHA.2440@.TK2MSFTNGP09.phx.gbl...
> > > There are certain routines within my vb app which need to skip certain
> > > sections of a trigger as they cater for the code elsewhere.
> > >
> > > I have tried to use a temporatry table however how do i check that the
> > table
> > > exists befreo selecting from it. i.e. to prevent an error being
raised
> > when
> > > the table is not present.
> > >
> > > I have also found that you can create a @.@. variable but cannot
currently
> > get
> > > it to persist (if i am using it correctly) and there is no help about
> > > creating these.
> > >
> > > Is it possible to create a connection specific variable?
> > >
> > > thanks in advance
> > >
> > > TimB
> > >
> > > --
> > > --
> > > TimB[490134]
> > > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > > news:OA0TQHN4DHA.2348@.TK2MSFTNGP09.phx.gbl...
> > > > Hi Tim,
> > > >
> > > > Can you explain why you don't want the triggers to run? If it is for
a
> > > > maintenance or administration task you can turn the triggers off
with
> > > ALTER
> > > > TABLE <table name> DISABLE TRIGGER ALL, and enable them later with
> ALTER
> > > > TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable
the
> > > > triggers for all users, not just the current connection, and that
you
> > need
> > > > to be a member of the sysadmin, db_ddladmin or db_owner roles to use
> it.
> > > >
> > > > If you want to do it in a different scenario, triggers might not be
> the
> > > > right solution and you can be better off putting your code in a
stored
> > > > procedure. Can you give some more information in that case?
> > > >
> > > > --
> > > > Jacco Schalkwijk
> > > > SQL Server MVP
> > > >
> > > >
> > > > "timb" <timb@.test.com> wrote in message
> > > > news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...
> > > > > Hi,
> > > > > i have several triggers which under certain circumstances i
dont
> > > want
> > > > to
> > > > > run. I would like to be able to set a variable against the sql
> > > connection
> > > > > which i can test for within my trigger and then prevent running if
> > > certain
> > > > > conditions apply. Is this possible? without using connection
> specific
> > > > > tables? is it even possible with them?
> > > > >
> > > > > --
> > > > > --
> > > > > TimB
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>