IIS ODBC logging with extended stored procedure on a trigger

Hello,
I passed several days trying to configure SQL Server 2000 with no success,
so may be someone can help me with this problem.
What I want to do is to log data from an IIS using ODBC logging but in a
diferent way, I mean to a different table structure, so my idea is to create
the standard table that IIS needs to log and insert an instead of trigger
that collect and transform some of the columns of the standard table and
insert the processed results to my new table.
Up to here this worked perfectly, I was able to use the instead of trigger
functionality and insert to my table with no problems.
The problem comes when I try to use an extended stored procedure called from
the trigger, this procedure is responsible to do the transformation IP to
country code, it works very nice outside the trigger.
To sumarize, the weird thing is the following :
On the trigger code, if I comment the line that calls to the extended
sotored procedure, all works ok.
But whenever I try to call it, it seems that the trigger aborts the
execution, and nothing is inserted to my table.
Alse IIS raise this error on event logs :
"IIS ODBC Logging failed to log data to data source WEBSTATS. For
additional information specific to this message please visit the Microsoft
Online Support site located at:
http://www.microsoft.com/contentredirect.asp"
All the permissions on tables and the extended stored procedure are the more
relaxed as I can configure...
If someone can give me some advice, I would really apresiate it !
Thanks !Although I don't have any idea re the actual problem.. it isn't a good thing
to do extended SP calls in a trigger anyway... Any error will abort the
trigger (which might be what is happening), and then you get a scope
abort...
Perhaps you could have another program run every x minutes and update the
table with the info outside of the trigger..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"-ND-" <death@.elrancho.com> wrote in message
news:ep6CGVUSEHA.3756@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I passed several days trying to configure SQL Server 2000 with no success,
> so may be someone can help me with this problem.
> What I want to do is to log data from an IIS using ODBC logging but in a
> diferent way, I mean to a different table structure, so my idea is to
create
> the standard table that IIS needs to log and insert an instead of trigger
> that collect and transform some of the columns of the standard table and
> insert the processed results to my new table.
> Up to here this worked perfectly, I was able to use the instead of trigger
> functionality and insert to my table with no problems.
> The problem comes when I try to use an extended stored procedure called
from
> the trigger, this procedure is responsible to do the transformation IP to
> country code, it works very nice outside the trigger.
> To sumarize, the weird thing is the following :
> On the trigger code, if I comment the line that calls to the extended
> sotored procedure, all works ok.
> But whenever I try to call it, it seems that the trigger aborts the
> execution, and nothing is inserted to my table.
> Alse IIS raise this error on event logs :
> "IIS ODBC Logging failed to log data to data source WEBSTATS. For
> additional information specific to this message please visit the Microsoft
> Online Support site located at:
> http://www.microsoft.com/contentredirect.asp"
> All the permissions on tables and the extended stored procedure are the
more
> relaxed as I can configure...
> If someone can give me some advice, I would really apresiate it !
> Thanks !
>|||Thanks, for response.
The extended stored procedure doesn't seem to be executed.
If I call it from outside the trigger, with the exact same arguments it
works ok.
So, it should be some permission issue ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OOlPoxWSEHA.3020@.TK2MSFTNGP10.phx.gbl...
> Although I don't have any idea re the actual problem.. it isn't a good
thing
> to do extended SP calls in a trigger anyway... Any error will abort the
> trigger (which might be what is happening), and then you get a scope
> abort...
> Perhaps you could have another program run every x minutes and update the
> table with the info outside of the trigger..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "-ND-" <death@.elrancho.com> wrote in message
> news:ep6CGVUSEHA.3756@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> > I passed several days trying to configure SQL Server 2000 with no
success,
> > so may be someone can help me with this problem.
> >
> > What I want to do is to log data from an IIS using ODBC logging but in a
> > diferent way, I mean to a different table structure, so my idea is to
> create
> > the standard table that IIS needs to log and insert an instead of
trigger
> > that collect and transform some of the columns of the standard table and
> > insert the processed results to my new table.
> >
> > Up to here this worked perfectly, I was able to use the instead of
trigger
> > functionality and insert to my table with no problems.
> > The problem comes when I try to use an extended stored procedure called
> from
> > the trigger, this procedure is responsible to do the transformation IP
to
> > country code, it works very nice outside the trigger.
> >
> > To sumarize, the weird thing is the following :
> > On the trigger code, if I comment the line that calls to the extended
> > sotored procedure, all works ok.
> > But whenever I try to call it, it seems that the trigger aborts the
> > execution, and nothing is inserted to my table.
> > Alse IIS raise this error on event logs :
> > "IIS ODBC Logging failed to log data to data source WEBSTATS. For
> > additional information specific to this message please visit the
Microsoft
> > Online Support site located at:
> > http://www.microsoft.com/contentredirect.asp"
> >
> > All the permissions on tables and the extended stored procedure are the
> more
> > relaxed as I can configure...
> >
> > If someone can give me some advice, I would really apresiate it !
> > Thanks !
> >
> >
>|||After all, it was a bug in the extended stored procedure that was causing a
fatal error, and it was reflected only inside the trigger... nothing to do
with the server...
Thanks BTW !
"-ND-" <death@.elrancho.com> wrote in message
news:uNSm5VaSEHA.3660@.tk2msftngp13.phx.gbl...
> Thanks, for response.
> The extended stored procedure doesn't seem to be executed.
> If I call it from outside the trigger, with the exact same arguments it
> works ok.
> So, it should be some permission issue ?
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:OOlPoxWSEHA.3020@.TK2MSFTNGP10.phx.gbl...
> > Although I don't have any idea re the actual problem.. it isn't a good
> thing
> > to do extended SP calls in a trigger anyway... Any error will abort the
> > trigger (which might be what is happening), and then you get a scope
> > abort...
> >
> > Perhaps you could have another program run every x minutes and update
the
> > table with the info outside of the trigger..
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "-ND-" <death@.elrancho.com> wrote in message
> > news:ep6CGVUSEHA.3756@.TK2MSFTNGP11.phx.gbl...
> > > Hello,
> > > I passed several days trying to configure SQL Server 2000 with no
> success,
> > > so may be someone can help me with this problem.
> > >
> > > What I want to do is to log data from an IIS using ODBC logging but in
a
> > > diferent way, I mean to a different table structure, so my idea is to
> > create
> > > the standard table that IIS needs to log and insert an instead of
> trigger
> > > that collect and transform some of the columns of the standard table
and
> > > insert the processed results to my new table.
> > >
> > > Up to here this worked perfectly, I was able to use the instead of
> trigger
> > > functionality and insert to my table with no problems.
> > > The problem comes when I try to use an extended stored procedure
called
> > from
> > > the trigger, this procedure is responsible to do the transformation IP
> to
> > > country code, it works very nice outside the trigger.
> > >
> > > To sumarize, the weird thing is the following :
> > > On the trigger code, if I comment the line that calls to the extended
> > > sotored procedure, all works ok.
> > > But whenever I try to call it, it seems that the trigger aborts the
> > > execution, and nothing is inserted to my table.
> > > Alse IIS raise this error on event logs :
> > > "IIS ODBC Logging failed to log data to data source WEBSTATS. For
> > > additional information specific to this message please visit the
> Microsoft
> > > Online Support site located at:
> > > http://www.microsoft.com/contentredirect.asp"
> > >
> > > All the permissions on tables and the extended stored procedure are
the
> > more
> > > relaxed as I can configure...
> > >
> > > If someone can give me some advice, I would really apresiate it !
> > > Thanks !
> > >
> > >
> >
> >
>