Dear all,
Do you people know how to connect MSSQL 2005 express edition with Vb6
ADODB? As I know MSSQL 2005 express connection string is specific the
database path. How do I write the connection string for ADODB?
Thanks in advance,
Goh
Hi Goh,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to connect to SQL
Server 2005 Express via VB6. If I have misunderstood your concern, please
feel free to point it out.
We have a MSDN article that describes how to connect SQL Server 2005
Express from VB6.
Using SQL Express from Visual Basic 6
http://msdn.microsoft.com/vbrun/vbfu...ingsqlexpress/
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label specific. Show all posts
Showing posts with label specific. Show all posts
Friday, February 24, 2012
Connection String MSSQL2005
Dear all,
Do you people know how to connect MSSQL 2005 express edition with Vb6
ADODB? As I know MSSQL 2005 express connection string is specific the
database path. How do I write the connection string for ADODB?
Thanks in advance,
GohHi Goh,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to connect to SQL
Server 2005 Express via VB6. If I have misunderstood your concern, please
feel free to point it out.
We have a MSDN article that describes how to connect SQL Server 2005
Express from VB6.
Using SQL Express from Visual Basic 6
http://msdn.microsoft.com/vbrun/vbf...singsqlexpress/
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Do you people know how to connect MSSQL 2005 express edition with Vb6
ADODB? As I know MSSQL 2005 express connection string is specific the
database path. How do I write the connection string for ADODB?
Thanks in advance,
GohHi Goh,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to connect to SQL
Server 2005 Express via VB6. If I have misunderstood your concern, please
feel free to point it out.
We have a MSDN article that describes how to connect SQL Server 2005
Express from VB6.
Using SQL Express from Visual Basic 6
http://msdn.microsoft.com/vbrun/vbf...singsqlexpress/
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Tuesday, February 14, 2012
Connection String
Can I write the connection string that uses a specific Windows user account
and not SQL user account?Hi
You can't.
NT Integrated security uses the context of the logged on user/service to
pass to the SQL Server. You can not change that.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Yuliaan" <Yuliaan@.discussions.microsoft.com> wrote in message
news:B1982B11-48CF-4E91-BC13-554982AD8E41@.microsoft.com...
> Can I write the connection string that uses a specific Windows user
> account
> and not SQL user account?
and not SQL user account?Hi
You can't.
NT Integrated security uses the context of the logged on user/service to
pass to the SQL Server. You can not change that.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Yuliaan" <Yuliaan@.discussions.microsoft.com> wrote in message
news:B1982B11-48CF-4E91-BC13-554982AD8E41@.microsoft.com...
> Can I write the connection string that uses a specific Windows user
> account
> and not SQL user account?
Connection String
Can I write the connection string that uses a specific Windows user account
and not SQL user account?
Hi
You can't.
NT Integrated security uses the context of the logged on user/service to
pass to the SQL Server. You can not change that.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Yuliaan" <Yuliaan@.discussions.microsoft.com> wrote in message
news:B1982B11-48CF-4E91-BC13-554982AD8E41@.microsoft.com...
> Can I write the connection string that uses a specific Windows user
> account
> and not SQL user account?
and not SQL user account?
Hi
You can't.
NT Integrated security uses the context of the logged on user/service to
pass to the SQL Server. You can not change that.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Yuliaan" <Yuliaan@.discussions.microsoft.com> wrote in message
news:B1982B11-48CF-4E91-BC13-554982AD8E41@.microsoft.com...
> Can I write the connection string that uses a specific Windows user
> account
> and not SQL user account?
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...
to
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...
ALTER
(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...
table
when
get
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...
avoid
were
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...
the
will
the
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:|||There are certain routines within my vb app which need to skip certain
> 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
>
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:|||Not that I recommend the following solution, but we implemented it to avoid
> 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]
>
(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:|||Hi Jacco,
> 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
>
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:|||> Only thing is how do i check for the presence of this temporary table?
> 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]
>
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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Connection question about using impersonate in webpage
Hi,
I create a webservice and impersonate a specific user only when you run a
connection to sql server to insert the data.
My connection string is as follows:
Integrated Security=SSPI;Persist Security Info=false;Initial
Catalog=cmcMail;Data Source=MASDD1;Network Library=dbmssocn;Packet Size=1024;
I got the error during debugging which is "Error: 18456, Severity: 14,
State: 11" . It means that "Valid login but server access failure"
After running some testings, I have a few questions about the inpersonate
issue:
1. If I user anohter domain user, who has access right to server and
database even to execute the store procedure as same as the specific user.
The data is inserted without problem. Why one can work and another cann't?
2. I tried to user the user is not my database user but can access another
database on the same SQL server.
It didn't raise error, and the data is insert on the table. Why this user
without premission can execute the store procedure.
Thanks
"BMW" wrote:
> Hi,
> I create a webservice and impersonate a specific user only when you run a
> connection to sql server to insert the data.
> My connection string is as follows:
> Integrated Security=SSPI;Persist Security Info=false;Initial
> Catalog=Mail;Data Source=MAS;Network Library=dbmssocn;Packet Size=1024;
> I got the error during debugging which is "Error: 18456, Severity: 14,
> State: 11" . It means that "Valid login but server access failure"
>
> After running some testings, I have a few questions about the inpersonate
> issue:
> 1. If I user anohter domain user, who has access right to server and
> database even to execute the store procedure as same as the specific user.
> The data is inserted without problem. Why one can work and another cann't?
> 2. I tried to user the user is not my database user but can access another
> database on the same SQL server.
> It didn't raise error, and the data is insert on the table. Why this user
> without premission can execute the store procedure.
> Thanks
>
I create a webservice and impersonate a specific user only when you run a
connection to sql server to insert the data.
My connection string is as follows:
Integrated Security=SSPI;Persist Security Info=false;Initial
Catalog=cmcMail;Data Source=MASDD1;Network Library=dbmssocn;Packet Size=1024;
I got the error during debugging which is "Error: 18456, Severity: 14,
State: 11" . It means that "Valid login but server access failure"
After running some testings, I have a few questions about the inpersonate
issue:
1. If I user anohter domain user, who has access right to server and
database even to execute the store procedure as same as the specific user.
The data is inserted without problem. Why one can work and another cann't?
2. I tried to user the user is not my database user but can access another
database on the same SQL server.
It didn't raise error, and the data is insert on the table. Why this user
without premission can execute the store procedure.
Thanks
"BMW" wrote:
> Hi,
> I create a webservice and impersonate a specific user only when you run a
> connection to sql server to insert the data.
> My connection string is as follows:
> Integrated Security=SSPI;Persist Security Info=false;Initial
> Catalog=Mail;Data Source=MAS;Network Library=dbmssocn;Packet Size=1024;
> I got the error during debugging which is "Error: 18456, Severity: 14,
> State: 11" . It means that "Valid login but server access failure"
>
> After running some testings, I have a few questions about the inpersonate
> issue:
> 1. If I user anohter domain user, who has access right to server and
> database even to execute the store procedure as same as the specific user.
> The data is inserted without problem. Why one can work and another cann't?
> 2. I tried to user the user is not my database user but can access another
> database on the same SQL server.
> It didn't raise error, and the data is insert on the table. Why this user
> without premission can execute the store procedure.
> Thanks
>
Connection question about using impersonate in webpage
Hi,
I create a webservice and impersonate a specific user only when you run a
connection to sql server to insert the data.
My connection string is as follows:
Integrated Security=SSPI;Persist Security Info=false;Initial
Catalog=cmcMail;Data Source=MASDD1;Network Library=dbmssocn;Packet Size=1024
;
I got the error during debugging which is "Error: 18456, Severity: 14,
State: 11" . It means that "Valid login but server access failure"
After running some testings, I have a few questions about the inpersonate
issue:
1. If I user anohter domain user, who has access right to server and
database even to execute the store procedure as same as the specific user.
The data is inserted without problem. Why one can work and another cann't?
2. I tried to user the user is not my database user but can access another
database on the same SQL server.
It didn't raise error, and the data is insert on the table. Why this user
without premission can execute the store procedure.
Thanks"BMW" wrote:
> Hi,
> I create a webservice and impersonate a specific user only when you run a
> connection to sql server to insert the data.
> My connection string is as follows:
> Integrated Security=SSPI;Persist Security Info=false;Initial
> Catalog=Mail;Data Source=MAS;Network Library=dbmssocn;Packet Size=1024;
> I got the error during debugging which is "Error: 18456, Severity: 14,
> State: 11" . It means that "Valid login but server access failure"
>
> After running some testings, I have a few questions about the inpersonate
> issue:
> 1. If I user anohter domain user, who has access right to server and
> database even to execute the store procedure as same as the specific user.
> The data is inserted without problem. Why one can work and another cann't?
> 2. I tried to user the user is not my database user but can access another
> database on the same SQL server.
> It didn't raise error, and the data is insert on the table. Why this user
> without premission can execute the store procedure.
> Thanks
>
I create a webservice and impersonate a specific user only when you run a
connection to sql server to insert the data.
My connection string is as follows:
Integrated Security=SSPI;Persist Security Info=false;Initial
Catalog=cmcMail;Data Source=MASDD1;Network Library=dbmssocn;Packet Size=1024
;
I got the error during debugging which is "Error: 18456, Severity: 14,
State: 11" . It means that "Valid login but server access failure"
After running some testings, I have a few questions about the inpersonate
issue:
1. If I user anohter domain user, who has access right to server and
database even to execute the store procedure as same as the specific user.
The data is inserted without problem. Why one can work and another cann't?
2. I tried to user the user is not my database user but can access another
database on the same SQL server.
It didn't raise error, and the data is insert on the table. Why this user
without premission can execute the store procedure.
Thanks"BMW" wrote:
> Hi,
> I create a webservice and impersonate a specific user only when you run a
> connection to sql server to insert the data.
> My connection string is as follows:
> Integrated Security=SSPI;Persist Security Info=false;Initial
> Catalog=Mail;Data Source=MAS;Network Library=dbmssocn;Packet Size=1024;
> I got the error during debugging which is "Error: 18456, Severity: 14,
> State: 11" . It means that "Valid login but server access failure"
>
> After running some testings, I have a few questions about the inpersonate
> issue:
> 1. If I user anohter domain user, who has access right to server and
> database even to execute the store procedure as same as the specific user.
> The data is inserted without problem. Why one can work and another cann't?
> 2. I tried to user the user is not my database user but can access another
> database on the same SQL server.
> It didn't raise error, and the data is insert on the table. Why this user
> without premission can execute the store procedure.
> Thanks
>
Subscribe to:
Posts (Atom)