In MS SQL 2000, I would like to obtain the timezone offset from UTC for
a given date. For today's date, I can do
DATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a future
date may not be the same as today because some countries go in Daylight
Saving mode. Can you suggest a way to obtain the timezone offset for
any given date?
Thanks
Yashhttp://www.aspfaq.com/show.asp?id=2218
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
<yashgt@.yahoo.com> wrote in message
news:1108654973.175225.138590@.g14g2000cwa.googlegroups.com...
> In MS SQL 2000, I would like to obtain the timezone offset from UTC for
> a given date. For today's date, I can do
> DATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a future
> date may not be the same as today because some countries go in Daylight
> Saving mode. Can you suggest a way to obtain the timezone offset for
> any given date?
> Thanks
> Yash
>
Showing posts with label t-sql. Show all posts
Showing posts with label t-sql. Show all posts
Wednesday, March 7, 2012
Obtaining timezone offset in T-SQL.
In MS SQL 2000, I would like to obtain the timezone offset from UTC for
a given date. For today's date, I can do
DATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a future
date may not be the same as today because some countries go in Daylight
Saving mode. Can you suggest a way to obtain the timezone offset for
any given date?
a given date. For today's date, I can do
DATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a future
date may not be the same as today because some countries go in Daylight
Saving mode. Can you suggest a way to obtain the timezone offset for
any given date?
Thanks
Yash(yashgt@.yahoo.com) writes:
> In MS SQL 2000, I would like to obtain the timezone offset from UTC for
> a given date. For today's date, I can do
> DATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a future
> date may not be the same as today because some countries go in Daylight
> Saving mode. Can you suggest a way to obtain the timezone offset for
> any given date?
Since the datetime data type is not timezone-aware, I can't see that
this can be done by SQL Server itself.
What I can think of is:
1) Set up a table with all the worlds time-zone information and query that
table.
2) Write an extended stored procedure or a COM object that retrieves
the information from Windows.
3) A combination of 2 and 3: you talk to Windows when the server boots,
and then stores the information in a table.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Feb 17 2005, 05:43 pm, Erland Sommarskog <esquel@.sommarskog.se> wrote
in news:Xns9600F106C231BYazorman@.127.0.0.1:
> 3) A combination of 2 and 3: you talk to Windows when the server
> boots,
> and then stores the information in a table.
This may not be a good idea if the server is started on first Saturday in
April, for example...
--
remove a 9 to reply by email|||Dimitri Furman (dfurman@.cloud99.net) writes:
> On Feb 17 2005, 05:43 pm, Erland Sommarskog <esquel@.sommarskog.se> wrote
> in news:Xns9600F106C231BYazorman@.127.0.0.1:
>> 3) A combination of 2 and 3: you talk to Windows when the server
>> boots,
>> and then stores the information in a table.
> This may not be a good idea if the server is started on first Saturday in
> April, for example...
What you would read is of course the static information about when the
transitions to/from DST occur.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
obtaining the names of triggers within T-SQL
I have a db containing 2 base tables and a view with an INSTEAD OF INSERT
trigger that updates both tables. I want to prevent inserts on the base
tables themselves with an INSTEAD OF INSERT trigger that would raise an erro
r
indicating that the appliation should insert against the view and then
rolling back the insert on the base table.
Is there a way to find out the names of the triggers in play so I could do
this? (I've experimented with trigger_nestlevel() but it doesn't do exactly
what I want.)
This is on SQL Server 2000.Brian,
Deny "insert" permission on the table, grant "insert" on the view.
AMB
"Brian Cobb" wrote:
> I have a db containing 2 base tables and a view with an INSTEAD OF INSERT
> trigger that updates both tables. I want to prevent inserts on the base
> tables themselves with an INSTEAD OF INSERT trigger that would raise an er
ror
> indicating that the appliation should insert against the view and then
> rolling back the insert on the base table.
> Is there a way to find out the names of the triggers in play so I could do
> this? (I've experimented with trigger_nestlevel() but it doesn't do exact
ly
> what I want.)
> This is on SQL Server 2000.
>|||That will work only if one can safely assume one has control over what users
and roles will be permitted in the database, won't it?
"Alejandro Mesa" wrote:
> Brian,
> Deny "insert" permission on the table, grant "insert" on the view.
>
> AMB
> "Brian Cobb" wrote:
>|||> That will work only if one can safely assume one has control over what
> users
> and roles will be permitted in the database, won't it?
Yes - And why wouldn't one make this assumption? Any "solution" can be
circumvented by someone with administrative access to the database. The
suggestion, by far, is the most straightforward one and involves no obscure
code to determine the "triggers in play" (without know specifically what
that means).|||I thought "triggers in play" was obvious given the context in which it was
used in my original post. But let me try to clarify. If a trigger,
trigger1, performs an action that initiates another trigger, trigger2, is it
possible within trigger2 to determine the name of the initiating trigger?
I am only interested in an answer to my question as posed in the original
post, and (hopefully) clarified here. Please feel free not to respond if yo
u
can't answer it.
"Scott Morris" wrote:
> Yes - And why wouldn't one make this assumption? Any "solution" can be
> circumvented by someone with administrative access to the database. The
> suggestion, by far, is the most straightforward one and involves no obscur
e
> code to determine the "triggers in play" (without know specifically what
> that means).
>
>|||On Fri, 17 Mar 2006 12:41:26 -0800, Brian Cobb wrote:
>I thought "triggers in play" was obvious given the context in which it was
>used in my original post. But let me try to clarify. If a trigger,
>trigger1, performs an action that initiates another trigger, trigger2, is i
t
>possible within trigger2 to determine the name of the initiating trigger?
Hi Brian,
There's no easy way to do that. The only method I can think of is to
query TRIGGER_NESTLEVEL(object_id) with the object_id of each trigger
that might be active when trigger2 runs; if it's not 0, trigger2 must
have been fired (either directly or indirectly) by that trigger.
>I am only interested in an answer to my question as posed in the original
>post, and (hopefully) clarified here. Please feel free not to respond if y
ou
>can't answer it.
The only correct answer to your original post would be to dump the idea
of enforcing this with triggers. Use DCL, as suggested by Alejandro.
Hugo Kornelis, SQL Server MVP|||I suspected it probably couldn't be done the way I hoped but wanted some
outside input. I had already considered something similar to what Mr. Mesa
had suggested, prior to my first post, which I will now revisit.
Thank you for your response.
"Hugo Kornelis" wrote:
> On Fri, 17 Mar 2006 12:41:26 -0800, Brian Cobb wrote:
>
> Hi Brian,
> There's no easy way to do that. The only method I can think of is to
> query TRIGGER_NESTLEVEL(object_id) with the object_id of each trigger
> that might be active when trigger2 runs; if it's not 0, trigger2 must
> have been fired (either directly or indirectly) by that trigger.
>
> The only correct answer to your original post would be to dump the idea
> of enforcing this with triggers. Use DCL, as suggested by Alejandro.
> --
> Hugo Kornelis, SQL Server MVP
>
trigger that updates both tables. I want to prevent inserts on the base
tables themselves with an INSTEAD OF INSERT trigger that would raise an erro
r
indicating that the appliation should insert against the view and then
rolling back the insert on the base table.
Is there a way to find out the names of the triggers in play so I could do
this? (I've experimented with trigger_nestlevel() but it doesn't do exactly
what I want.)
This is on SQL Server 2000.Brian,
Deny "insert" permission on the table, grant "insert" on the view.
AMB
"Brian Cobb" wrote:
> I have a db containing 2 base tables and a view with an INSTEAD OF INSERT
> trigger that updates both tables. I want to prevent inserts on the base
> tables themselves with an INSTEAD OF INSERT trigger that would raise an er
ror
> indicating that the appliation should insert against the view and then
> rolling back the insert on the base table.
> Is there a way to find out the names of the triggers in play so I could do
> this? (I've experimented with trigger_nestlevel() but it doesn't do exact
ly
> what I want.)
> This is on SQL Server 2000.
>|||That will work only if one can safely assume one has control over what users
and roles will be permitted in the database, won't it?
"Alejandro Mesa" wrote:
> Brian,
> Deny "insert" permission on the table, grant "insert" on the view.
>
> AMB
> "Brian Cobb" wrote:
>|||> That will work only if one can safely assume one has control over what
> users
> and roles will be permitted in the database, won't it?
Yes - And why wouldn't one make this assumption? Any "solution" can be
circumvented by someone with administrative access to the database. The
suggestion, by far, is the most straightforward one and involves no obscure
code to determine the "triggers in play" (without know specifically what
that means).|||I thought "triggers in play" was obvious given the context in which it was
used in my original post. But let me try to clarify. If a trigger,
trigger1, performs an action that initiates another trigger, trigger2, is it
possible within trigger2 to determine the name of the initiating trigger?
I am only interested in an answer to my question as posed in the original
post, and (hopefully) clarified here. Please feel free not to respond if yo
u
can't answer it.
"Scott Morris" wrote:
> Yes - And why wouldn't one make this assumption? Any "solution" can be
> circumvented by someone with administrative access to the database. The
> suggestion, by far, is the most straightforward one and involves no obscur
e
> code to determine the "triggers in play" (without know specifically what
> that means).
>
>|||On Fri, 17 Mar 2006 12:41:26 -0800, Brian Cobb wrote:
>I thought "triggers in play" was obvious given the context in which it was
>used in my original post. But let me try to clarify. If a trigger,
>trigger1, performs an action that initiates another trigger, trigger2, is i
t
>possible within trigger2 to determine the name of the initiating trigger?
Hi Brian,
There's no easy way to do that. The only method I can think of is to
query TRIGGER_NESTLEVEL(object_id) with the object_id of each trigger
that might be active when trigger2 runs; if it's not 0, trigger2 must
have been fired (either directly or indirectly) by that trigger.
>I am only interested in an answer to my question as posed in the original
>post, and (hopefully) clarified here. Please feel free not to respond if y
ou
>can't answer it.
The only correct answer to your original post would be to dump the idea
of enforcing this with triggers. Use DCL, as suggested by Alejandro.
Hugo Kornelis, SQL Server MVP|||I suspected it probably couldn't be done the way I hoped but wanted some
outside input. I had already considered something similar to what Mr. Mesa
had suggested, prior to my first post, which I will now revisit.
Thank you for your response.
"Hugo Kornelis" wrote:
> On Fri, 17 Mar 2006 12:41:26 -0800, Brian Cobb wrote:
>
> Hi Brian,
> There's no easy way to do that. The only method I can think of is to
> query TRIGGER_NESTLEVEL(object_id) with the object_id of each trigger
> that might be active when trigger2 runs; if it's not 0, trigger2 must
> have been fired (either directly or indirectly) by that trigger.
>
> The only correct answer to your original post would be to dump the idea
> of enforcing this with triggers. Use DCL, as suggested by Alejandro.
> --
> Hugo Kornelis, SQL Server MVP
>
obtaining physical file and path using T-SQL
SQL 2000.
Is there a way to get a databases physical file names for data and log
using t-sql?
exec sp_helpdb '<database name>' returns two resultsets, the latter
would be good if I can get at the filename in the second resultset. If
anyone knows how?...
sysdatabases seems to hold the physical name for the data only. So
where is the logfile physical name stored?
I need to get the physical names into t-sql variables for later use.
Any help much appreciate.
thanks
PaulDid you look at sp_helpfile?
"Paul Fell" <penzina@.iinet.net.au> wrote in message
news:bea9531b.0308271721.7a2ea54f@.posting.google.com...
> SQL 2000.
> Is there a way to get a databases physical file names for data and log
> using t-sql?
> exec sp_helpdb '<database name>' returns two resultsets, the latter
> would be good if I can get at the filename in the second resultset. If
> anyone knows how?...
> sysdatabases seems to hold the physical name for the data only. So
> where is the logfile physical name stored?
> I need to get the physical names into t-sql variables for later use.
> Any help much appreciate.
> thanks
> Paul|||> I found all the info I needed by looking at sysdatabases and sysaltfiles
Note that the behavior (and existence!) of these tables might change in a
future release, which is why I tend to recommend using system stored
procedures (and information_schema views, where possible) rather than direct
system table access, especially if you're putting code into production.|||Agreed. But if you look at my orginal posting, you'll see I did try
sp_helpfile.
This system SP returns two recordsets. How do I get at the second
records set in my SP? I need to be able to access this data and I
don't know of a way of doing it dynamically.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message news:<#FImIRWbDHA.2580@.TK2MSFTNGP09.phx.gbl>...
> > I found all the info I needed by looking at sysdatabases and sysaltfiles
> Note that the behavior (and existence!) of these tables might change in a
> future release, which is why I tend to recommend using system stored
> procedures (and information_schema views, where possible) rather than direct
> system table access, especially if you're putting code into production.|||> This system SP returns two recordsets. How do I get at the second
> records set in my SP?
No can do. INSERT ... EXEC only picks up the first resultset. Either pick it up in a client program,
or use the source code for the proc to write your own proc.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Paul Fell" <penzina@.iinet.net.au> wrote in message
news:bea9531b.0308281845.730c942b@.posting.google.com...
> Agreed. But if you look at my orginal posting, you'll see I did try
> sp_helpfile.
> This system SP returns two recordsets. How do I get at the second
> records set in my SP? I need to be able to access this data and I
> don't know of a way of doing it dynamically.
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:<#FImIRWbDHA.2580@.TK2MSFTNGP09.phx.gbl>...
> > > I found all the info I needed by looking at sysdatabases and sysaltfiles
> >
> > Note that the behavior (and existence!) of these tables might change in a
> > future release, which is why I tend to recommend using system stored
> > procedures (and information_schema views, where possible) rather than direct
> > system table access, especially if you're putting code into production.
Is there a way to get a databases physical file names for data and log
using t-sql?
exec sp_helpdb '<database name>' returns two resultsets, the latter
would be good if I can get at the filename in the second resultset. If
anyone knows how?...
sysdatabases seems to hold the physical name for the data only. So
where is the logfile physical name stored?
I need to get the physical names into t-sql variables for later use.
Any help much appreciate.
thanks
PaulDid you look at sp_helpfile?
"Paul Fell" <penzina@.iinet.net.au> wrote in message
news:bea9531b.0308271721.7a2ea54f@.posting.google.com...
> SQL 2000.
> Is there a way to get a databases physical file names for data and log
> using t-sql?
> exec sp_helpdb '<database name>' returns two resultsets, the latter
> would be good if I can get at the filename in the second resultset. If
> anyone knows how?...
> sysdatabases seems to hold the physical name for the data only. So
> where is the logfile physical name stored?
> I need to get the physical names into t-sql variables for later use.
> Any help much appreciate.
> thanks
> Paul|||> I found all the info I needed by looking at sysdatabases and sysaltfiles
Note that the behavior (and existence!) of these tables might change in a
future release, which is why I tend to recommend using system stored
procedures (and information_schema views, where possible) rather than direct
system table access, especially if you're putting code into production.|||Agreed. But if you look at my orginal posting, you'll see I did try
sp_helpfile.
This system SP returns two recordsets. How do I get at the second
records set in my SP? I need to be able to access this data and I
don't know of a way of doing it dynamically.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message news:<#FImIRWbDHA.2580@.TK2MSFTNGP09.phx.gbl>...
> > I found all the info I needed by looking at sysdatabases and sysaltfiles
> Note that the behavior (and existence!) of these tables might change in a
> future release, which is why I tend to recommend using system stored
> procedures (and information_schema views, where possible) rather than direct
> system table access, especially if you're putting code into production.|||> This system SP returns two recordsets. How do I get at the second
> records set in my SP?
No can do. INSERT ... EXEC only picks up the first resultset. Either pick it up in a client program,
or use the source code for the proc to write your own proc.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Paul Fell" <penzina@.iinet.net.au> wrote in message
news:bea9531b.0308281845.730c942b@.posting.google.com...
> Agreed. But if you look at my orginal posting, you'll see I did try
> sp_helpfile.
> This system SP returns two recordsets. How do I get at the second
> records set in my SP? I need to be able to access this data and I
> don't know of a way of doing it dynamically.
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:<#FImIRWbDHA.2580@.TK2MSFTNGP09.phx.gbl>...
> > > I found all the info I needed by looking at sysdatabases and sysaltfiles
> >
> > Note that the behavior (and existence!) of these tables might change in a
> > future release, which is why I tend to recommend using system stored
> > procedures (and information_schema views, where possible) rather than direct
> > system table access, especially if you're putting code into production.
Monday, February 20, 2012
Obscuring T-SQL code from RDL File and SQL Server
I would like to write some reports that will query T-SQL for information, but
I don't what anyone to be able to see the T-SQL code. So I can not store the
T-SQLin the RDL file, or put it in a SP. Also can't even use and encrypted
SP because someone would have to have the source code to create the T-SQL.
So what options are available to hide the T-SQL code, but not allow anyone to
see it, or change it?On Apr 17, 7:50 pm, Greg Larsen <gregalar...@.removeit.msn.com> wrote:
> I would like to write some reports that will query T-SQL for information, but
> I don't what anyone to be able to see the T-SQL code. So I can not store the
> T-SQLin the RDL file, or put it in a SP. Also can't even use and encrypted
> SP because someone would have to have the source code to create the T-SQL.
> So what options are available to hide the T-SQL code, but not allow anyone to
> see it, or change it?
As far as I know, there is not really much else that can be done to
restrict access to the T-SQL or stored procedure(s). Sorry that I
could not be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||I would guess if a compiled piece of code could be used as a datasource then
potentially this might work. Don't suppose you know how to create a piece of
compiled code that can be used as a data source to a reporting services
report.
"EMartinez" wrote:
> On Apr 17, 7:50 pm, Greg Larsen <gregalar...@.removeit.msn.com> wrote:
> > I would like to write some reports that will query T-SQL for information, but
> > I don't what anyone to be able to see the T-SQL code. So I can not store the
> > T-SQLin the RDL file, or put it in a SP. Also can't even use and encrypted
> > SP because someone would have to have the source code to create the T-SQL.
> > So what options are available to hide the T-SQL code, but not allow anyone to
> > see it, or change it?
> As far as I know, there is not really much else that can be done to
> restrict access to the T-SQL or stored procedure(s). Sorry that I
> could not be of further assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
I don't what anyone to be able to see the T-SQL code. So I can not store the
T-SQLin the RDL file, or put it in a SP. Also can't even use and encrypted
SP because someone would have to have the source code to create the T-SQL.
So what options are available to hide the T-SQL code, but not allow anyone to
see it, or change it?On Apr 17, 7:50 pm, Greg Larsen <gregalar...@.removeit.msn.com> wrote:
> I would like to write some reports that will query T-SQL for information, but
> I don't what anyone to be able to see the T-SQL code. So I can not store the
> T-SQLin the RDL file, or put it in a SP. Also can't even use and encrypted
> SP because someone would have to have the source code to create the T-SQL.
> So what options are available to hide the T-SQL code, but not allow anyone to
> see it, or change it?
As far as I know, there is not really much else that can be done to
restrict access to the T-SQL or stored procedure(s). Sorry that I
could not be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||I would guess if a compiled piece of code could be used as a datasource then
potentially this might work. Don't suppose you know how to create a piece of
compiled code that can be used as a data source to a reporting services
report.
"EMartinez" wrote:
> On Apr 17, 7:50 pm, Greg Larsen <gregalar...@.removeit.msn.com> wrote:
> > I would like to write some reports that will query T-SQL for information, but
> > I don't what anyone to be able to see the T-SQL code. So I can not store the
> > T-SQLin the RDL file, or put it in a SP. Also can't even use and encrypted
> > SP because someone would have to have the source code to create the T-SQL.
> > So what options are available to hide the T-SQL code, but not allow anyone to
> > see it, or change it?
> As far as I know, there is not really much else that can be done to
> restrict access to the T-SQL or stored procedure(s). Sorry that I
> could not be of further assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
Subscribe to:
Posts (Atom)