I have transactional replication setup with an updatable subscriber. I want
the trigger on the publisher to transfer over to the destination during the
initial snapshot (the drop/create is where I lose the trigger). In the GUI,
properties of the publisher, Articles tab, clicking the elispse then on the
Snapshot tab, there is a checkbox 'User Triggers'. But when I check this and
reinitialize the snapshot and subscriber, the table get dropped and
recreated on the subscriber (as it should) but again WITHOUT the triggers
that are on the publisher.
Any help is greatly appreciated.
Thanks.
Appendage to initial post...
So i discovered the @.schema_option = 0x100 Replicates user triggers on a
table article, if defined.
and when i script out my publisher i see
@.schema_option = 0x000000000000CEF3 and
@.schema_option = 0x000000000000CFF3
(the first one is for a table that does not have the user triggers option
set and the second one does so it makes sense that one has E and the other
F. But this is still not working.
On the publisher I have a trigger:
CREATE TRIGGER tr_CustomerAsset_BreakoutClassifyFieldsAndMatchEqu ipment ON
dbo.CustomerAsset
but after snapshot, this trigger is not at the subscriber.
Thanks everyone!
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:eoOv3bOZHHA.4264@.TK2MSFTNGP05.phx.gbl...
>I have transactional replication setup with an updatable subscriber. I want
>the trigger on the publisher to transfer over to the destination during the
>initial snapshot (the drop/create is where I lose the trigger). In the GUI,
>properties of the publisher, Articles tab, clicking the elispse then on the
>Snapshot tab, there is a checkbox 'User Triggers'. But when I check this
>and reinitialize the snapshot and subscriber, the table get dropped and
>recreated on the subscriber (as it should) but again WITHOUT the triggers
>that are on the publisher.
> Any help is greatly appreciated.
> Thanks.
>
|||This should work. I would try to re-apply them using a post snapshot script.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:eoOv3bOZHHA.4264@.TK2MSFTNGP05.phx.gbl...
>I have transactional replication setup with an updatable subscriber. I want
>the trigger on the publisher to transfer over to the destination during the
>initial snapshot (the drop/create is where I lose the trigger). In the GUI,
>properties of the publisher, Articles tab, clicking the elispse then on the
>Snapshot tab, there is a checkbox 'User Triggers'. But when I check this
>and reinitialize the snapshot and subscriber, the table get dropped and
>recreated on the subscriber (as it should) but again WITHOUT the triggers
>that are on the publisher.
> Any help is greatly appreciated.
> Thanks.
>
Showing posts with label trigger. Show all posts
Showing posts with label trigger. Show all posts
Monday, March 19, 2012
Maintain separate table via trigger vs. indexed view
Hello,
I have a table LargeTable in many columns and many rows. However, I also
need to have a small subset of the rows in LargeTable and only data from
small set of columns; call it SmallTable.
SmallTable gets read very often. So basically I've been using trigger on
LargeTable to watch for any change there to populate the SmallTable.
However, I realized that an indexed view can replace SmallTable.
In general, which one would be a better design in term of performance? I
know that indexed view can be more elegant but I'm interested in
performance. thanks!"Zester" <zeze@.nottospam.com> wrote in message
news:OaN3Tf3BIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table LargeTable in many columns and many rows. However, I also
> need to have a small subset of the rows in LargeTable and only data from
> small set of columns; call it SmallTable.
> SmallTable gets read very often. So basically I've been using trigger on
> LargeTable to watch for any change there to populate the SmallTable.
> However, I realized that an indexed view can replace SmallTable.
> In general, which one would be a better design in term of performance? I
> know that indexed view can be more elegant but I'm interested in
> performance. thanks!
>
>
What makes you think this is a candidate for an indexed view? You could
create an ordinary view and put a relevant nonclustered index on the base
table. That way you won't incur the same write overhead that an indexed view
has. In SQL Server 2005 you can also include non-key columns in a
nonclustered index.
--
David Portas|||the LargeTable gets read even more often than the SmallTable and LargeTable
serves the main feature of our product. Writing to it is not often but does
occur - most likely only to the columns that have nothing to do with the
dataset needed for SmallTable. The feature using SmallTable is minor, so we
don't want it to interfere with the main feature that needs LargeTable.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uKBFF$3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OaN3Tf3BIHA.5360@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> I have a table LargeTable in many columns and many rows. However, I also
>> need to have a small subset of the rows in LargeTable and only data from
>> small set of columns; call it SmallTable.
>> SmallTable gets read very often. So basically I've been using trigger on
>> LargeTable to watch for any change there to populate the SmallTable.
>> However, I realized that an indexed view can replace SmallTable.
>> In general, which one would be a better design in term of performance? I
>> know that indexed view can be more elegant but I'm interested in
>> performance. thanks!
>>
> What makes you think this is a candidate for an indexed view? You could
> create an ordinary view and put a relevant nonclustered index on the base
> table. That way you won't incur the same write overhead that an indexed
> view has. In SQL Server 2005 you can also include non-key columns in a
> nonclustered index.
> --
> David Portas
>
I have a table LargeTable in many columns and many rows. However, I also
need to have a small subset of the rows in LargeTable and only data from
small set of columns; call it SmallTable.
SmallTable gets read very often. So basically I've been using trigger on
LargeTable to watch for any change there to populate the SmallTable.
However, I realized that an indexed view can replace SmallTable.
In general, which one would be a better design in term of performance? I
know that indexed view can be more elegant but I'm interested in
performance. thanks!"Zester" <zeze@.nottospam.com> wrote in message
news:OaN3Tf3BIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table LargeTable in many columns and many rows. However, I also
> need to have a small subset of the rows in LargeTable and only data from
> small set of columns; call it SmallTable.
> SmallTable gets read very often. So basically I've been using trigger on
> LargeTable to watch for any change there to populate the SmallTable.
> However, I realized that an indexed view can replace SmallTable.
> In general, which one would be a better design in term of performance? I
> know that indexed view can be more elegant but I'm interested in
> performance. thanks!
>
>
What makes you think this is a candidate for an indexed view? You could
create an ordinary view and put a relevant nonclustered index on the base
table. That way you won't incur the same write overhead that an indexed view
has. In SQL Server 2005 you can also include non-key columns in a
nonclustered index.
--
David Portas|||the LargeTable gets read even more often than the SmallTable and LargeTable
serves the main feature of our product. Writing to it is not often but does
occur - most likely only to the columns that have nothing to do with the
dataset needed for SmallTable. The feature using SmallTable is minor, so we
don't want it to interfere with the main feature that needs LargeTable.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uKBFF$3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OaN3Tf3BIHA.5360@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> I have a table LargeTable in many columns and many rows. However, I also
>> need to have a small subset of the rows in LargeTable and only data from
>> small set of columns; call it SmallTable.
>> SmallTable gets read very often. So basically I've been using trigger on
>> LargeTable to watch for any change there to populate the SmallTable.
>> However, I realized that an indexed view can replace SmallTable.
>> In general, which one would be a better design in term of performance? I
>> know that indexed view can be more elegant but I'm interested in
>> performance. thanks!
>>
> What makes you think this is a candidate for an indexed view? You could
> create an ordinary view and put a relevant nonclustered index on the base
> table. That way you won't incur the same write overhead that an indexed
> view has. In SQL Server 2005 you can also include non-key columns in a
> nonclustered index.
> --
> David Portas
>
Friday, March 9, 2012
mailing from MS SQL
Hi, is it possible to send an email from MS SQL? I need to have a trigger which will activate the sending an email. Thanksdo not do it from a trigger. What happens if there is a bulk import of 10,000 records. You want 10,000 emails? Otherwise please google or refer to SQL Server Books Online for xp_sendmail or xp_smtpmail.|||A bulk import of 10,000 records is still only going to execute the trigger once. A cursor or loop-based import would stress out your e-mail system, though.
It's just a bad idea for the scope of a trigger's actions to extend outside the database. Don't do it.
An alternative is to have your trigger load messages into a queue table for regularly processing by a scheduled job (which could run as frequently as once per minute).|||I was assuming that we would execute xp_sendmail for every record in inserted smart guy.|||I know you were, and I know you know the difference. Just wanted to make sure the poster did not get the wrong impression.
It's just a bad idea for the scope of a trigger's actions to extend outside the database. Don't do it.
An alternative is to have your trigger load messages into a queue table for regularly processing by a scheduled job (which could run as frequently as once per minute).|||I was assuming that we would execute xp_sendmail for every record in inserted smart guy.|||I know you were, and I know you know the difference. Just wanted to make sure the poster did not get the wrong impression.
Wednesday, March 7, 2012
mail que
hi,
we are having a trigger for update on a table.
this trigger fires the xp_smtp mail session proc.
the problem is that when there are many updates in a short while. (20
updates in a minute) some of the email don't arrive.
the updates are done one by one from a single location.
can it be that the server is too slow, or the proc is too slow to deal with
all the updates?
is there a way to wourkaroun it?
thanks,
prem
Hi
It is best not to call external process from within a trigger as it may hold
resources, is prone to failure and most times you can not handle the error
generated and your batch is rolled back.
Rather, in the trigger, write a row to another table, and have an exernal
process poll the table and send the e-mails.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"prem" wrote:
> hi,
> we are having a trigger for update on a table.
> this trigger fires the xp_smtp mail session proc.
> the problem is that when there are many updates in a short while. (20
> updates in a minute) some of the email don't arrive.
> the updates are done one by one from a single location.
> can it be that the server is too slow, or the proc is too slow to deal with
> all the updates?
> is there a way to wourkaroun it?
> thanks,
> prem
>
>
we are having a trigger for update on a table.
this trigger fires the xp_smtp mail session proc.
the problem is that when there are many updates in a short while. (20
updates in a minute) some of the email don't arrive.
the updates are done one by one from a single location.
can it be that the server is too slow, or the proc is too slow to deal with
all the updates?
is there a way to wourkaroun it?
thanks,
prem
Hi
It is best not to call external process from within a trigger as it may hold
resources, is prone to failure and most times you can not handle the error
generated and your batch is rolled back.
Rather, in the trigger, write a row to another table, and have an exernal
process poll the table and send the e-mails.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"prem" wrote:
> hi,
> we are having a trigger for update on a table.
> this trigger fires the xp_smtp mail session proc.
> the problem is that when there are many updates in a short while. (20
> updates in a minute) some of the email don't arrive.
> the updates are done one by one from a single location.
> can it be that the server is too slow, or the proc is too slow to deal with
> all the updates?
> is there a way to wourkaroun it?
> thanks,
> prem
>
>
mail que
hi,
we are having a trigger for update on a table.
this trigger fires the xp_smtp mail session proc.
the problem is that when there are many updates in a short while. (20
updates in a minute) some of the email don't arrive.
the updates are done one by one from a single location.
can it be that the server is too slow, or the proc is too slow to deal with
all the updates?
is there a way to wourkaroun it?
thanks,
premHi
It is best not to call external process from within a trigger as it may hold
resources, is prone to failure and most times you can not handle the error
generated and your batch is rolled back.
Rather, in the trigger, write a row to another table, and have an exernal
process poll the table and send the e-mails.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"prem" wrote:
> hi,
> we are having a trigger for update on a table.
> this trigger fires the xp_smtp mail session proc.
> the problem is that when there are many updates in a short while. (20
> updates in a minute) some of the email don't arrive.
> the updates are done one by one from a single location.
> can it be that the server is too slow, or the proc is too slow to deal wit
h
> all the updates?
> is there a way to wourkaroun it?
> thanks,
> prem
>
>
we are having a trigger for update on a table.
this trigger fires the xp_smtp mail session proc.
the problem is that when there are many updates in a short while. (20
updates in a minute) some of the email don't arrive.
the updates are done one by one from a single location.
can it be that the server is too slow, or the proc is too slow to deal with
all the updates?
is there a way to wourkaroun it?
thanks,
premHi
It is best not to call external process from within a trigger as it may hold
resources, is prone to failure and most times you can not handle the error
generated and your batch is rolled back.
Rather, in the trigger, write a row to another table, and have an exernal
process poll the table and send the e-mails.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"prem" wrote:
> hi,
> we are having a trigger for update on a table.
> this trigger fires the xp_smtp mail session proc.
> the problem is that when there are many updates in a short while. (20
> updates in a minute) some of the email don't arrive.
> the updates are done one by one from a single location.
> can it be that the server is too slow, or the proc is too slow to deal wit
h
> all the updates?
> is there a way to wourkaroun it?
> thanks,
> prem
>
>
mail que
hi,
we are having a trigger for update on a table.
this trigger fires the xp_smtp mail session proc.
the problem is that when there are many updates in a short while. (20
updates in a minute) some of the email don't arrive.
the updates are done one by one from a single location.
can it be that the server is too slow, or the proc is too slow to deal with
all the updates?
is there a way to wourkaroun it?
thanks,
premHi
It is best not to call external process from within a trigger as it may hold
resources, is prone to failure and most times you can not handle the error
generated and your batch is rolled back.
Rather, in the trigger, write a row to another table, and have an exernal
process poll the table and send the e-mails.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"prem" wrote:
> hi,
> we are having a trigger for update on a table.
> this trigger fires the xp_smtp mail session proc.
> the problem is that when there are many updates in a short while. (20
> updates in a minute) some of the email don't arrive.
> the updates are done one by one from a single location.
> can it be that the server is too slow, or the proc is too slow to deal with
> all the updates?
> is there a way to wourkaroun it?
> thanks,
> prem
>
>
we are having a trigger for update on a table.
this trigger fires the xp_smtp mail session proc.
the problem is that when there are many updates in a short while. (20
updates in a minute) some of the email don't arrive.
the updates are done one by one from a single location.
can it be that the server is too slow, or the proc is too slow to deal with
all the updates?
is there a way to wourkaroun it?
thanks,
premHi
It is best not to call external process from within a trigger as it may hold
resources, is prone to failure and most times you can not handle the error
generated and your batch is rolled back.
Rather, in the trigger, write a row to another table, and have an exernal
process poll the table and send the e-mails.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"prem" wrote:
> hi,
> we are having a trigger for update on a table.
> this trigger fires the xp_smtp mail session proc.
> the problem is that when there are many updates in a short while. (20
> updates in a minute) some of the email don't arrive.
> the updates are done one by one from a single location.
> can it be that the server is too slow, or the proc is too slow to deal with
> all the updates?
> is there a way to wourkaroun it?
> thanks,
> prem
>
>
Saturday, February 25, 2012
Machine Name In Trigger
Is it possible to get the machine name in a trigger so that it can be stored
in a field?
Thanks.Check out HOST_NAME() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Neil" <njones@.pxdy.com> wrote in message
news:N1vTd.5219$873.4670@.newsread3.news.pas.earthlink.net...
Is it possible to get the machine name in a trigger so that it can be stored
in a field?
Thanks.|||Great. Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...
> Check out HOST_NAME() in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Neil" <njones@.pxdy.com> wrote in message
> news:N1vTd.5219$873.4670@.newsread3.news.pas.earthlink.net...
> Is it possible to get the machine name in a trigger so that it can be
> stored
> in a field?
> Thanks.
>|||Any function for getting the current Windows user?
Thanks!
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...
> Check out HOST_NAME() in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Neil" <njones@.pxdy.com> wrote in message
> news:N1vTd.5219$873.4670@.newsread3.news.pas.earthlink.net...
> Is it possible to get the machine name in a trigger so that it can be
> stored
> in a field?
> Thanks.
>|||Check if this helps:
SELECT @.@.SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Neil" <njones@.pxdy.com> wrote in message
news:6_xTd.5362$873.4937@.newsread3.news.pas.earthlink.net...
> Any function for getting the current Windows user?
> Thanks!
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...
>|||Here's what I got:
ID Login Name
User Name
-- ---
---
----
----9
bo(1 row(s) af
fected)I was looking for the Windows account that's currently logged in, if
that'spossible.Thanks."Vin
od Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in messagenews:cvmtl9$ufs$1@.news01.int
el.com...> Check if this helps:>> SELECT @.@.SPID AS 'ID', SYSTEM_USER AS 'Login Name', U
SER AS 'User Name'>> --> HTH,> Vinod Kumar> MCSE, DBA, MCAD, MCSD> http://www.ext[
/url]
.../2000/books.asp>> "Neil" <njones@.pxdy.com> wrote in mess
age> news:6_xTd.5362$873.4937@.newsread3.news.pas.earthlink.net...>> Any func
tion for getting the
current Windows user?>>>> Thanks!>>>>>> "Tom Moreau" <tom@.dont.spam.me.cips.
ca> wrote in message>> news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...>> > Che
ck out HOST_NAME() in the BOL.>> >>> > -->> > Tom>> >>> > --
--
-->> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>> > SQL Server MVP>> > Columnist,
SQL Server Professional>> > Toronto, ON Canada>> > [url]www.pinnaclepublishing.com[/
url]>> > .>> > "Neil" <njones@.pxdy.com> wrote in message>> > news:N1vTd.5219
$873.4670@.newsre
ad3.news.pas.earthlink.net...>> > Is it possible to get the machine name in
a trigger so that it can be>> > stored>> > in a field?>> >>> > Thanks.>> >>>
>>>>>>>|||try suser_sname()
dean
"Neil" <njones@.pxdy.com> wrote in message
news:wGAUd.7684$873.5789@.newsread3.news.pas.earthlink.net...
> Here's what I got:
> ID Login Name
> User Name
> -- ---
---
> ----
----9bo(1 row(s)
affected)I was looking for the Windows account that's currently logged in,
if that'spossible.Thanks."Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com>
wrote in messagenews:cvmtl9$ufs$1@.news01.intel.com...> Check if this
helps:>> SELECT @.@.SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User
Name'>> --> HTH,> Vinod Kumar> MCSE, DBA, MCAD, MCSD>
http://www.extremeexperts.com>> Books Online for SQL Server SP3 at>
http://www.microsoft.com/sql/techin.../2000/books.asp>> "Neil"
<njones@.pxdy.com> wrote in message>
news:6_xTd.5362$873.4937@.newsread3.news.pas.earthlink.net...>> Any function
for getting the current Windows user?>>>> Thanks!>>>>>> "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote in message>>
news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...>> > Check out HOST_NAME() in
the BOL.>> >>> > -->> > Tom>> >>>
> ---->> > Thomas A. Moreau,
BSc, PhD, MCSE, MCDBA>> > SQL Server MVP>> > Columnist, SQL Server
Professional>> > Toronto, ON Canada>> > www.pinnaclepublishing.com>> > .>>
> "Neil" <njones@.pxdy.com> wrote in message>> >
news:N1vTd.5219$873.4670@.newsread3.news.pas.earthlink.net...>> > Is it
possible to get the machine name in a trigger so that it can be>> > stored>>
> in a field?>> >>> > Thanks.>> >>> >>>>>>>
>|||Neil (njones@.pxdy.com) writes:
> Here's what I got:
> ID Login Name
> User Name
>----
>9bo(1 row(s) affected)
>I was looking for the Windows account that's currently logged in, if
>that'spossible.
It appears that the user has logged in through SQL authentication. In
that case you cannot retrieve the Windows user. In fact, the user
may not even have been authenticated through Windows - he might be
accessing SQL Server from a Unix box.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Still get "sa".
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:uiV8mSZHFHA.2420@.TK2MSFTNGP14.phx.gbl...
> try suser_sname()
> dean
> "Neil" <njones@.pxdy.com> wrote in message
> news:wGAUd.7684$873.5789@.newsread3.news.pas.earthlink.net...
> ---
> ----9bo(1
> row(s)
> affected)I was looking for the Windows account that's currently logged in,
> if that'spossible.Thanks."Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com>
> wrote in messagenews:cvmtl9$ufs$1@.news01.intel.com...> Check if this
> helps:>> SELECT @.@.SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User
> Name'>> --> HTH,> Vinod Kumar> MCSE, DBA, MCAD, MCSD>
> http://www.extremeexperts.com>> Books Online for SQL Server SP3 at>
> http://www.microsoft.com/sql/techin.../2000/books.asp>> "Neil"
> <njones@.pxdy.com> wrote in message>
> news:6_xTd.5362$873.4937@.newsread3.news.pas.earthlink.net...>> Any
> function
> for getting the current Windows user?>>>> Thanks!>>>>>> "Tom Moreau"
> <tom@.dont.spam.me.cips.ca> wrote in message>>
> news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...>> > Check out HOST_NAME() in
> the BOL.>> >>> > -->> > Tom>> >>>
> BSc, PhD, MCSE, MCDBA>> > SQL Server MVP>> > Columnist, SQL Server
> Professional>> > Toronto, ON Canada>> > www.pinnaclepublishing.com>> >
> .>>
> news:N1vTd.5219$873.4670@.newsread3.news.pas.earthlink.net...>> > Is it
> possible to get the machine name in a trigger so that it can be>> >
> stored>>
>|||I was hoping there was a way to use a Windows API call or other, even with
SQL authentication.
Neil
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns960C1AF7BCEYazorman@.127.0.0.1...
> Neil (njones@.pxdy.com) writes:
> It appears that the user has logged in through SQL authentication. In
> that case you cannot retrieve the Windows user. In fact, the user
> may not even have been authenticated through Windows - he might be
> accessing SQL Server from a Unix box.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
in a field?
Thanks.Check out HOST_NAME() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Neil" <njones@.pxdy.com> wrote in message
news:N1vTd.5219$873.4670@.newsread3.news.pas.earthlink.net...
Is it possible to get the machine name in a trigger so that it can be stored
in a field?
Thanks.|||Great. Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...
> Check out HOST_NAME() in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Neil" <njones@.pxdy.com> wrote in message
> news:N1vTd.5219$873.4670@.newsread3.news.pas.earthlink.net...
> Is it possible to get the machine name in a trigger so that it can be
> stored
> in a field?
> Thanks.
>|||Any function for getting the current Windows user?
Thanks!
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...
> Check out HOST_NAME() in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Neil" <njones@.pxdy.com> wrote in message
> news:N1vTd.5219$873.4670@.newsread3.news.pas.earthlink.net...
> Is it possible to get the machine name in a trigger so that it can be
> stored
> in a field?
> Thanks.
>|||Check if this helps:
SELECT @.@.SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Neil" <njones@.pxdy.com> wrote in message
news:6_xTd.5362$873.4937@.newsread3.news.pas.earthlink.net...
> Any function for getting the current Windows user?
> Thanks!
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...
>|||Here's what I got:
ID Login Name
User Name
-- ---
---
----
----9
bo(1 row(s) affected)I was looking for the Windows account that's currently logged in, if
that'spossible.Thanks."Vin
od Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in messagenews:cvmtl9$ufs$1@.news01.int
el.com...> Check if this helps:>> SELECT @.@.SPID AS 'ID', SYSTEM_USER AS 'Login Name', U
SER AS 'User Name'>> --> HTH,> Vinod Kumar> MCSE, DBA, MCAD, MCSD> http://www.ext[
/url]
.../2000/books.asp>> "Neil" <njones@.pxdy.com> wrote in mess
age> news:6_xTd.5362$873.4937@.newsread3.news.pas.earthlink.net...>> Any func
tion for getting the
current Windows user?>>>> Thanks!>>>>>> "Tom Moreau" <tom@.dont.spam.me.cips.
ca> wrote in message>> news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...>> > Che
ck out HOST_NAME() in the BOL.>> >>> > -->> > Tom>> >>> > --
--
-->> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>> > SQL Server MVP>> > Columnist,
SQL Server Professional>> > Toronto, ON Canada>> > [url]www.pinnaclepublishing.com[/
url]>> > .>> > "Neil" <njones@.pxdy.com> wrote in message>> > news:N1vTd.5219
$873.4670@.newsre
ad3.news.pas.earthlink.net...>> > Is it possible to get the machine name in
a trigger so that it can be>> > stored>> > in a field?>> >>> > Thanks.>> >>>
>>>>>>>|||try suser_sname()
dean
"Neil" <njones@.pxdy.com> wrote in message
news:wGAUd.7684$873.5789@.newsread3.news.pas.earthlink.net...
> Here's what I got:
> ID Login Name
> User Name
> -- ---
---
> ----
----9
bo(1 row(s)affected)I was looking for the Windows account that's currently logged in,
if that'spossible.Thanks."Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com>
wrote in messagenews:cvmtl9$ufs$1@.news01.intel.com...> Check if this
helps:>> SELECT @.@.SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User
Name'>> --> HTH,> Vinod Kumar> MCSE, DBA, MCAD, MCSD>
http://www.extremeexperts.com>> Books Online for SQL Server SP3 at>
http://www.microsoft.com/sql/techin.../2000/books.asp>> "Neil"
<njones@.pxdy.com> wrote in message>
news:6_xTd.5362$873.4937@.newsread3.news.pas.earthlink.net...>> Any function
for getting the current Windows user?>>>> Thanks!>>>>>> "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote in message>>
news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...>> > Check out HOST_NAME() in
the BOL.>> >>> > -->> > Tom>> >>>
> ---->> > Thomas A. Moreau,
BSc, PhD, MCSE, MCDBA>> > SQL Server MVP>> > Columnist, SQL Server
Professional>> > Toronto, ON Canada>> > www.pinnaclepublishing.com>> > .>>
> "Neil" <njones@.pxdy.com> wrote in message>> >
news:N1vTd.5219$873.4670@.newsread3.news.pas.earthlink.net...>> > Is it
possible to get the machine name in a trigger so that it can be>> > stored>>
> in a field?>> >>> > Thanks.>> >>> >>>>>>>
>|||Neil (njones@.pxdy.com) writes:
> Here's what I got:
> ID Login Name
> User Name
>----
>9
bo(1 row(s) affected)>I was looking for the Windows account that's currently logged in, if
>that'spossible.
It appears that the user has logged in through SQL authentication. In
that case you cannot retrieve the Windows user. In fact, the user
may not even have been authenticated through Windows - he might be
accessing SQL Server from a Unix box.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Still get "sa".
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:uiV8mSZHFHA.2420@.TK2MSFTNGP14.phx.gbl...
> try suser_sname()
> dean
> "Neil" <njones@.pxdy.com> wrote in message
> news:wGAUd.7684$873.5789@.newsread3.news.pas.earthlink.net...
> ---
> ----9
bo(1> row(s)
> affected)I was looking for the Windows account that's currently logged in,
> if that'spossible.Thanks."Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com>
> wrote in messagenews:cvmtl9$ufs$1@.news01.intel.com...> Check if this
> helps:>> SELECT @.@.SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User
> Name'>> --> HTH,> Vinod Kumar> MCSE, DBA, MCAD, MCSD>
> http://www.extremeexperts.com>> Books Online for SQL Server SP3 at>
> http://www.microsoft.com/sql/techin.../2000/books.asp>> "Neil"
> <njones@.pxdy.com> wrote in message>
> news:6_xTd.5362$873.4937@.newsread3.news.pas.earthlink.net...>> Any
> function
> for getting the current Windows user?>>>> Thanks!>>>>>> "Tom Moreau"
> <tom@.dont.spam.me.cips.ca> wrote in message>>
> news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...>> > Check out HOST_NAME() in
> the BOL.>> >>> > -->> > Tom>> >>>
> BSc, PhD, MCSE, MCDBA>> > SQL Server MVP>> > Columnist, SQL Server
> Professional>> > Toronto, ON Canada>> > www.pinnaclepublishing.com>> >
> .>>
> news:N1vTd.5219$873.4670@.newsread3.news.pas.earthlink.net...>> > Is it
> possible to get the machine name in a trigger so that it can be>> >
> stored>>
>|||I was hoping there was a way to use a Windows API call or other, even with
SQL authentication.
Neil
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns960C1AF7BCEYazorman@.127.0.0.1...
> Neil (njones@.pxdy.com) writes:
> It appears that the user has logged in through SQL authentication. In
> that case you cannot retrieve the Windows user. In fact, the user
> may not even have been authenticated through Windows - he might be
> accessing SQL Server from a Unix box.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
Machine Name In Trigger
Is it possible to get the machine name in a trigger so that it can be stored
in a field?
in a field?
Thanks.Check out HOST_NAME() in the BOL.
--
Tom
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Neil" <njones@.pxdy.com> wrote in message
news:N1vTd.5219$873.4670@.newsread3.news.pas.earthl ink.net...
Is it possible to get the machine name in a trigger so that it can be stored
in a field?
Thanks.|||Great. Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...
> Check out HOST_NAME() in the BOL.
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Neil" <njones@.pxdy.com> wrote in message
> news:N1vTd.5219$873.4670@.newsread3.news.pas.earthl ink.net...
> Is it possible to get the machine name in a trigger so that it can be
> stored
> in a field?
> Thanks.|||Any function for getting the current Windows user?
Thanks!
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...
> Check out HOST_NAME() in the BOL.
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Neil" <njones@.pxdy.com> wrote in message
> news:N1vTd.5219$873.4670@.newsread3.news.pas.earthl ink.net...
> Is it possible to get the machine name in a trigger so that it can be
> stored
> in a field?
> Thanks.|||Check if this helps:
SELECT @.@.SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Neil" <njones@.pxdy.com> wrote in message
news:6_xTd.5362$873.4937@.newsread3.news.pas.earthl ink.net...
> Any function for getting the current Windows user?
> Thanks!
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...
> > Check out HOST_NAME() in the BOL.
> > --
> > Tom
> > ----------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> > .
> > "Neil" <njones@.pxdy.com> wrote in message
> > news:N1vTd.5219$873.4670@.newsread3.news.pas.earthl ink.net...
> > Is it possible to get the machine name in a trigger so that it can be
> > stored
> > in a field?
> > Thanks.|||Here's what I got:
ID Login Name
User Name
-- ---------------------------------------
---------------------------------------9 sadbo(1 row(s) affected)I was looking for the Windows account that's currently logged in, if that'spossible.Thanks."Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in messagenews:cvmtl9$ufs$1@.news01.intel.com...> Check if this helps:>> SELECT @.@.SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'>> --> HTH,> Vinod Kumar> MCSE, DBA, MCAD, MCSD> http://www.extremeexperts.com>> Books Online for SQL Server SP3 at> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp>> "Neil" <njones@.pxdy.com> wrote in message> news:6_xTd.5362$873.4937@.newsread3.news.pas.earthl ink.net...>> Any function for getting the current Windows user?>>>> Thanks!>>>>>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message>> news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...>> > Check out HOST_NAME() in the BOL.>> >>> > -->> > Tom>> >>> > ---------------->> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>> > SQL Server MVP>> > Columnist, SQL Server Professional>> > Toronto, ON Canada>> > www.pinnaclepublishing.com>> > .>> > "Neil" <njones@.pxdy.com> wrote in message>> > news:N1vTd.5219$873.4670@.newsread3.news.pas.earthl ink.net...>> > Is it possible to get the machine name in a trigger so that it can be>> > stored>> > in a field?>> >>> > Thanks.>> >>> >>>>>>|||try suser_sname()
dean
"Neil" <njones@.pxdy.com> wrote in message
news:wGAUd.7684$873.5789@.newsread3.news.pas.earthl ink.net...
> Here's what I got:
> ID Login Name
> User Name
> -- --------------------
-------------------
> ----------------------
-----------------9 sadbo(1 row(s)
affected)I was looking for the Windows account that's currently logged in,
if that'spossible.Thanks."Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com>
wrote in messagenews:cvmtl9$ufs$1@.news01.intel.com...> Check if this
helps:>> SELECT @.@.SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User
Name'>> --> HTH,> Vinod Kumar> MCSE, DBA, MCAD, MCSD>
http://www.extremeexperts.com>> Books Online for SQL Server SP3 at>
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp>> "Neil"
<njones@.pxdy.com> wrote in message>
news:6_xTd.5362$873.4937@.newsread3.news.pas.earthl ink.net...>> Any function
for getting the current Windows user?>>>> Thanks!>>>>>> "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote in message>>
news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...>> > Check out HOST_NAME() in
the BOL.>> >>> > -->> > Tom>> >>>
> ---------------->> > Thomas A. Moreau,
BSc, PhD, MCSE, MCDBA>> > SQL Server MVP>> > Columnist, SQL Server
Professional>> > Toronto, ON Canada>> > www.pinnaclepublishing.com>> > .>>
> "Neil" <njones@.pxdy.com> wrote in message>news:N1vTd.5219$873.4670@.newsread3.news.pas.earthl ink.net...>> > Is it
possible to get the machine name in a trigger so that it can be>> > stored>>
> in a field?>> >>> > Thanks.>> >>> >>>>>>|||Neil (njones@.pxdy.com) writes:
> Here's what I got:
> ID Login Name
> User Name
>-----------------
>9 sadbo(1 row(s) affected)
>I was looking for the Windows account that's currently logged in, if
>that'spossible.
It appears that the user has logged in through SQL authentication. In
that case you cannot retrieve the Windows user. In fact, the user
may not even have been authenticated through Windows - he might be
accessing SQL Server from a Unix box.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Still get "sa".
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:uiV8mSZHFHA.2420@.TK2MSFTNGP14.phx.gbl...
> try suser_sname()
> dean
> "Neil" <njones@.pxdy.com> wrote in message
> news:wGAUd.7684$873.5789@.newsread3.news.pas.earthl ink.net...
>> Here's what I got:
>>
>> ID Login Name
>> User Name
>> -- --------------------
> -------------------
>> ----------------------
> -----------------9 sadbo(1
> row(s)
> affected)I was looking for the Windows account that's currently logged in,
> if that'spossible.Thanks."Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com>
> wrote in messagenews:cvmtl9$ufs$1@.news01.intel.com...> Check if this
> helps:>> SELECT @.@.SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User
> Name'>> --> HTH,> Vinod Kumar> MCSE, DBA, MCAD, MCSD>
> http://www.extremeexperts.com>> Books Online for SQL Server SP3 at>
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp>> "Neil"
> <njones@.pxdy.com> wrote in message>
> news:6_xTd.5362$873.4937@.newsread3.news.pas.earthl ink.net...>> Any
> function
> for getting the current Windows user?>>>> Thanks!>>>>>> "Tom Moreau"
> <tom@.dont.spam.me.cips.ca> wrote in message>>
> news:OjjQtktGFHA.4032@.TK2MSFTNGP12.phx.gbl...>> > Check out HOST_NAME() in
> the BOL.>> >>> > -->> > Tom>> >>>
>> ---------------->> > Thomas A.
>> Moreau,
> BSc, PhD, MCSE, MCDBA>> > SQL Server MVP>> > Columnist, SQL Server
> Professional>> > Toronto, ON Canada>> > www.pinnaclepublishing.com>> .>>
>> "Neil" <njones@.pxdy.com> wrote in message>> news:N1vTd.5219$873.4670@.newsread3.news.pas.earthl ink.net...>> > Is it
> possible to get the machine name in a trigger so that it can be>> stored>>
>> in a field?>> >>> > Thanks.>> >>> >>>>>>>
>>|||I was hoping there was a way to use a Windows API call or other, even with
SQL authentication.
Neil
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns960C1AF7BCEYazorman@.127.0.0.1...
> Neil (njones@.pxdy.com) writes:
>> Here's what I got:
>>
>> ID Login Name
>> User Name
>>-----------------
>>9 sadbo(1 row(s) affected)
>>I was looking for the Windows account that's currently logged in, if
>>that'spossible.
> It appears that the user has logged in through SQL authentication. In
> that case you cannot retrieve the Windows user. In fact, the user
> may not even have been authenticated through Windows - he might be
> accessing SQL Server from a Unix box.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||But how do you expect to get the Windows login when it is not a Windows
login in use but a SQL login? Do you mean the user account used on the
client (as opposed to the server)? If that's what you mean then you're
out of luck because SQL Server only knows what it's been told. That is,
the SQL Server doesn't really know anything about the client. As Erland
said, you don't even know what OS the client is running.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Neil wrote:
>I was hoping there was a way to use a Windows API call or other, even with
>SQL authentication.
>Neil
>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>news:Xns960C1AF7BCEYazorman@.127.0.0.1...
>
>>Neil (njones@.pxdy.com) writes:
>>
>>
>>>Here's what I got:
>>>
>>>ID Login Name
>>>User Name
>>>-----------------
>>>9 sadbo(1 row(s) affected)
>>>I was looking for the Windows account that's currently logged in, if
>>>that'spossible.
>>>
>>>
>>It appears that the user has logged in through SQL authentication. In
>>that case you cannot retrieve the Windows user. In fact, the user
>>may not even have been authenticated through Windows - he might be
>>accessing SQL Server from a Unix box.
>>
>>--
>>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>>Books Online for SQL Server SP3 at
>>http://www.microsoft.com/sql/techin.../2000/books.asp
>>
>>
>
Subscribe to:
Posts (Atom)