Friday, March 23, 2012
ODBC connection problems....
From: "Chris" <Chris@.discussions.microsoft.com>
Subject: MSDE / ODBC install problem...please help...
Date: Tuesday, October 25, 2005 9:41 PM
I have installed MSDE (and ODBC) at my client site on their 2003 server. I
have attached my SQL database via Teratrax Database Manager to the MSDE
server however when I try to setup the ODBC connection it doesn't list the
database in the drop-down. I cannot attach to the database from my notebook
either because I am not authenticated onto their domain.
I have also applied the regedit on the LoginMode key setting it to 2 and
rebooted but I still cannot connect to the database via the ODBC manager.
What am I doing wrong? I am NOT good with the scripts for configuring
things
- generally I use the Enterprise Manager.Hi
If you don't have Enterprise Manager you may want to try logging in with
SQL. You don't say what SQL user your are using and if they have access to
this database. If you can connect to a database on that server then you may
want to see what sp_helpdb returns, you could also use sp_attach_db to
attach the original files to the server.
John
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:uSbDAWe2FHA.1268@.TK2MSFTNGP10.phx.gbl...
> Also posted in the MSDE section...
> From: "Chris" <Chris@.discussions.microsoft.com>
> Subject: MSDE / ODBC install problem...please help...
> Date: Tuesday, October 25, 2005 9:41 PM
> I have installed MSDE (and ODBC) at my client site on their 2003 server.
> I
> have attached my SQL database via Teratrax Database Manager to the MSDE
> server however when I try to setup the ODBC connection it doesn't list the
> database in the drop-down. I cannot attach to the database from my
> notebook
> either because I am not authenticated onto their domain.
> I have also applied the regedit on the LoginMode key setting it to 2 and
> rebooted but I still cannot connect to the database via the ODBC manager.
> What am I doing wrong? I am NOT good with the scripts for configuring
> things
> - generally I use the Enterprise Manager.
>
ODBC connection problems....
From: "Chris" <Chris@.discussions.microsoft.com>
Subject: MSDE / ODBC install problem...please help...
Date: Tuesday, October 25, 2005 9:41 PM
I have installed MSDE (and ODBC) at my client site on their 2003 server. I
have attached my SQL database via Teratrax Database Manager to the MSDE
server however when I try to setup the ODBC connection it doesn't list the
database in the drop-down. I cannot attach to the database from my notebook
either because I am not authenticated onto their domain.
I have also applied the regedit on the LoginMode key setting it to 2 and
rebooted but I still cannot connect to the database via the ODBC manager.
What am I doing wrong? I am NOT good with the scripts for configuring
things
- generally I use the Enterprise Manager.
Hi
If you don't have Enterprise Manager you may want to try logging in with
SQL. You don't say what SQL user your are using and if they have access to
this database. If you can connect to a database on that server then you may
want to see what sp_helpdb returns, you could also use sp_attach_db to
attach the original files to the server.
John
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:uSbDAWe2FHA.1268@.TK2MSFTNGP10.phx.gbl...
> Also posted in the MSDE section...
> From: "Chris" <Chris@.discussions.microsoft.com>
> Subject: MSDE / ODBC install problem...please help...
> Date: Tuesday, October 25, 2005 9:41 PM
> I have installed MSDE (and ODBC) at my client site on their 2003 server.
> I
> have attached my SQL database via Teratrax Database Manager to the MSDE
> server however when I try to setup the ODBC connection it doesn't list the
> database in the drop-down. I cannot attach to the database from my
> notebook
> either because I am not authenticated onto their domain.
> I have also applied the regedit on the LoginMode key setting it to 2 and
> rebooted but I still cannot connect to the database via the ODBC manager.
> What am I doing wrong? I am NOT good with the scripts for configuring
> things
> - generally I use the Enterprise Manager.
>
ODBC connection problems....
From: "Chris" <Chris@.discussions.microsoft.com>
Subject: MSDE / ODBC install problem...please help...
Date: Tuesday, October 25, 2005 9:41 PM
I have installed MSDE (and ODBC) at my client site on their 2003 server. I
have attached my SQL database via Teratrax Database Manager to the MSDE
server however when I try to setup the ODBC connection it doesn't list the
database in the drop-down. I cannot attach to the database from my notebook
either because I am not authenticated onto their domain.
I have also applied the regedit on the LoginMode key setting it to 2 and
rebooted but I still cannot connect to the database via the ODBC manager.
What am I doing wrong? I am NOT good with the scripts for configuring
things
- generally I use the Enterprise Manager.Hi
If you don't have Enterprise Manager you may want to try logging in with
SQL. You don't say what SQL user your are using and if they have access to
this database. If you can connect to a database on that server then you may
want to see what sp_helpdb returns, you could also use sp_attach_db to
attach the original files to the server.
John
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:uSbDAWe2FHA.1268@.TK2MSFTNGP10.phx.gbl...
> Also posted in the MSDE section...
> From: "Chris" <Chris@.discussions.microsoft.com>
> Subject: MSDE / ODBC install problem...please help...
> Date: Tuesday, October 25, 2005 9:41 PM
> I have installed MSDE (and ODBC) at my client site on their 2003 server.
> I
> have attached my SQL database via Teratrax Database Manager to the MSDE
> server however when I try to setup the ODBC connection it doesn't list the
> database in the drop-down. I cannot attach to the database from my
> notebook
> either because I am not authenticated onto their domain.
> I have also applied the regedit on the LoginMode key setting it to 2 and
> rebooted but I still cannot connect to the database via the ODBC manager.
> What am I doing wrong? I am NOT good with the scripts for configuring
> things
> - generally I use the Enterprise Manager.
>sql
Wednesday, March 7, 2012
Obtaining timezone offset in T-SQL.
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
>
Obtaining timezone offset in T-SQL.
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 latest record by date
Hi,
I have two tables, one stores items, and another one that stores history for the items from first table. They are related by a foreign key from items table.
In one of my queries, I need to obtain the latest history entry from table 2. For this I use MAX(RecordDate) aggregate. Another way that I know is:
SELECT TOP(1) RecordDate, HistoryID, ....
FROM ProductHistory
WHERE (SerialNumber = '20070101000010')
ORDER BY RecordDate DESC, HistoryID DESC
The query is part of a larger query where I obtain more data from other tables.
The reason I prefer the second option is that, at times I have multiple entries for the same item on the same date (no time info). In such cases, the data returned is the latest entry (HistoryID) in to the system of the rows with same date, thanks to ORDER BY clause. Another reason is that I can select any columns, whereas in the first query type I need to use subquery to obtain other fields after I find my relevant row.
My question is, is there any problem using the second method that I am not aware of? Is this method reliable (TOP(1))? I appreciate if you can guide me to the right method. Thanks!
You're absolutely fine to use TOP in the way that you have done.
The most common mistake when using TOP is to omit an ORDER BY clause where it should really be included, which can make the results unpredicatable and inconsistent. As you have included an ORDER BY clause then you'll be fine.
Chris
|||Thank you for your answer Chris. My main concern was whether TOP was applied before ORDER BY or to the result of the query at the end. It seems it is applied after all the filters are applied, showing only the portion of the result matching the whole query. Please corrent me if I am wrong. I will mark your response as the answer.
Migrant
Obtaining statistical data in MSDB db
I would like have available statistical data concerning our scheduled DTS.
Up to date I am be able to store and to see some data using sysjobs and
sysjobhistory tables in MSDB database, i.e, I obtain variables such as last
execution, time inverted and owner, as well as owner or version id. That's
fine and very useful but I would need more accurate information in order to
create statistics with the total of time inverted per each DTS during the
last two w
therefore, to schedule better our DTS, covering spaces in terms of time and
avoiding bottlenecks or issues which sometimes are causing headaches to all
the developers.
How could I do such thing? I am seeing these aforementioned tables but the
info stored is the last day.
select run_date,run_duration,run_status,step_na
me from sysjobhistory where
step_name
in('__ABS_PasarDiarioaHistorico')
I see this:
20050805 2855 1 __ABS_PasarDiarioaHistor
ico
20050806 2518 1 __ABS_PasarDiarioaHistor
ico
20050807 148 1 __ABS_PasarDiarioaHistori
co
20050808 6 1 __ABS_PasarDiarioaHistorico
20050809 2113 1 __ABS_PasarDiarioaHistor
ico
20050810 3205 1 __ABS_PasarDiarioaHistor
ico
20050811 3355 1 __ABS_PasarDiarioaHistor
ico
But, July executions where on earth are?
Any though/comment/advice will be welcomed.
Regards,I've done this query which is solving any doubts:
SELECT run_date, run_duration, case MSDB..sysjobschedules.freq_type
when 4 then 'Daily'
when 8 then 'W
when 16 then 'Monthly'
when 32 then 'once'
end
FROM MSDB..sysjobhistory inner join MSDB..sysjobschedules
on MSDB..sysjobhistory.step_name = MSDB..sysjobschedules.name
WHERE STEP_NAME IN('__ABS_EliminarHistorico') ORDER BY 1
*** __ABS_EliminarHistorico is one of our DTS.
"Enric" wrote:
> Dear folks,
> I would like have available statistical data concerning our scheduled DTS.
> Up to date I am be able to store and to see some data using sysjobs and
> sysjobhistory tables in MSDB database, i.e, I obtain variables such as las
t
> execution, time inverted and owner, as well as owner or version id. That's
> fine and very useful but I would need more accurate information in order t
o
> create statistics with the total of time inverted per each DTS during the
> last two w
> therefore, to schedule better our DTS, covering spaces in terms of time an
d
> avoiding bottlenecks or issues which sometimes are causing headaches to al
l
> the developers.
> How could I do such thing? I am seeing these aforementioned tables but the
> info stored is the last day.
> select run_date,run_duration,run_status,step_na
me from sysjobhistory where
> step_name
> in('__ABS_PasarDiarioaHistorico')
> I see this:
> 20050805 2855 1 __ABS_PasarDiarioaHistor
ico
> 20050806 2518 1 __ABS_PasarDiarioaHistor
ico
> 20050807 148 1 __ABS_PasarDiarioaHistori
co
> 20050808 6 1 __ABS_PasarDiarioaHistorico
> 20050809 2113 1 __ABS_PasarDiarioaHistor
ico
> 20050810 3205 1 __ABS_PasarDiarioaHistor
ico
> 20050811 3355 1 __ABS_PasarDiarioaHistor
ico
>
> But, July executions where on earth are?
>
> Any though/comment/advice will be welcomed.
> Regards,|||Enric
select convert(varchar(22), j.name) as job_name
, case freq_type -- Daily, w
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For w
+ case freq_interval & 2 when 2 then 'M' else '' end -- Monday
+ case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
+ case freq_interval & 8 when 8 then 'W' else '' end -- etc
+ case freq_interval & 16 when 16 then 'Th' else '' end
+ case freq_interval & 32 when 32 then 'F' else '' end
+ case freq_interval & 64 when 64 then 'Sa' else '' end
+ case freq_interval & 1 when 1 then 'Su' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly
on a particular day
when 32 then 'Mthly ' -- The most complicated one, "every third Friday of
the month" for example
+ case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'W
when 10 then 'W
end
when 64 then 'Startup' -- When SQL Server starts
when 128 then 'Idle' -- Whenever SQL Server gets bored
else 'Err' -- This should never happen
end as schedule
, case freq_subday_type -- FOr when a job funs every few seconds, minutes or
hours
when 1 then 'Runs once at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours'
end as frequency
-- All the subsrings are because the times are stored as an integer with no
leading zeroes
-- i.e. 0 means midnight, 13000 means half past one in the morning
(01:30:00)
, substring (right (stuff (' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time),
6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time),
6) ,5 ,2) as start_at
,case freq_subday_type
when 1 then NULL -- Ignore the end time if not a recurring job
else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_end_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,5 ,2) end as end_at
from sysjobs j
join sysJobSchedules s
on j.job_id = s.job_id
order by j.name, start_at
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:E8350DF7-6624-465B-888A-78FDE5899FBB@.microsoft.com...
> I've done this query which is solving any doubts:
> SELECT run_date, run_duration, case MSDB..sysjobschedules.freq_type
> when 4 then 'Daily'
> when 8 then 'W
> when 16 then 'Monthly'
> when 32 then 'once'
> end
> FROM MSDB..sysjobhistory inner join MSDB..sysjobschedules
> on MSDB..sysjobhistory.step_name = MSDB..sysjobschedules.name
> WHERE STEP_NAME IN('__ABS_EliminarHistorico') ORDER BY 1
> *** __ABS_EliminarHistorico is one of our DTS.
> "Enric" wrote:
>|||Thanks so much Uri, it's amazing, just a quick and powerful response.
"Uri Dimant" wrote:
> Enric
> select convert(varchar(22), j.name) as job_name
> , case freq_type -- Daily, w
> when 1 then 'Once'
> when 4 then 'Daily'
> when 8 then 'Wk ' -- For w
> + case freq_interval & 2 when 2 then 'M' else '' end -- Monday
> + case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
> + case freq_interval & 8 when 8 then 'W' else '' end -- etc
> + case freq_interval & 16 when 16 then 'Th' else '' end
> + case freq_interval & 32 when 32 then 'F' else '' end
> + case freq_interval & 64 when 64 then 'Sa' else '' end
> + case freq_interval & 1 when 1 then 'Su' else '' end
> when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Month
ly
> on a particular day
> when 32 then 'Mthly ' -- The most complicated one, "every third Friday of
> the month" for example
> + case freq_relative_interval
> when 1 then 'Every First '
> when 2 then 'Every Second '
> when 4 then 'Every Third '
> when 8 then 'Every Fourth '
> when 16 then 'Every Last '
> end
> + case freq_interval
> when 1 then 'Sunday'
> when 2 then 'Monday'
> when 3 then 'Tuesday'
> when 4 then 'Wednesday'
> when 5 then 'Thursday'
> when 6 then 'Friday'
> when 7 then 'Saturday'
> when 8 then 'Day'
> when 9 then 'W
> when 10 then 'W
> end
> when 64 then 'Startup' -- When SQL Server starts
> when 128 then 'Idle' -- Whenever SQL Server gets bored
> else 'Err' -- This should never happen
> end as schedule
> , case freq_subday_type -- FOr when a job funs every few seconds, minutes
or
> hours
> when 1 then 'Runs once at:'
> when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
> seconds'
> when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
> minutes'
> when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours
'
> end as frequency
> -- All the subsrings are because the times are stored as an integer with n
o
> leading zeroes
> -- i.e. 0 means midnight, 13000 means half past one in the morning
> (01:30:00)
> , substring (right (stuff (' ', 1, 1, '000000') +
> convert(varchar(6),active_start_time), 6), 1, 2)
> + ':'
> + substring (
> right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time
),
> 6) ,3 ,2)
> + ':'
> + substring (
> right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time)
,
> 6) ,5 ,2) as start_at
> ,case freq_subday_type
> when 1 then NULL -- Ignore the end time if not a recurring job
> else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
> active_end_time), 6), 1, 2)
> + ':'
> + substring (
> right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
> 6) ,3 ,2)
> + ':'
> + substring (
> right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
> 6) ,5 ,2) end as end_at
> from sysjobs j
> join sysJobSchedules s
> on j.job_id = s.job_id
> order by j.name, start_at
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:E8350DF7-6624-465B-888A-78FDE5899FBB@.microsoft.com...
>
>
Obtaining business hours based on start and end date
time an issue is assigned to someone else, closed, etc. I get a time
stamp. I have these time stamps for the beginning of an issue to the
end of an issue and I'd like to determine how many business hours these
issues were open.
Issue BeginDt Enddt Total hours
1 3/29/05 5:00 PM 4/1/05 2:00 PM 69
Basically, this is the type of data I'm looking at and my hours of work
are from 7:30 - 5:00 weekdays. I need to come up with a way to remove
all nonbusiness hours, weekends, & holidays from the difference of the
two dates. Issues can span for 2-3 days or 20-30 days.
Please let me know if anyone has any ideas or has done something like
this before.
Thanks!On 1 Apr 2005 14:26:14 -0800, mitchchristensen@.gmail.com wrote:
>I have a transaction log that tracks issues from a call center. Each
>time an issue is assigned to someone else, closed, etc. I get a time
>stamp. I have these time stamps for the beginning of an issue to the
>end of an issue and I'd like to determine how many business hours these
>issues were open.
>Issue BeginDt Enddt Total hours
>1 3/29/05 5:00 PM 4/1/05 2:00 PM 69
>Basically, this is the type of data I'm looking at and my hours of work
>are from 7:30 - 5:00 weekdays. I need to come up with a way to remove
>all nonbusiness hours, weekends, & holidays from the difference of the
>two dates. Issues can span for 2-3 days or 20-30 days.
>Please let me know if anyone has any ideas or has done something like
>this before.
>Thanks!
Hi mitchchristensen,
The easiest way to do it is to use a calendar table. What that is, how
you can make it and various good ways to use it are described at Aaron's
site: http://www.aspfaq.com/show.asp?id=2519.
For this specific situation, I'd suggest the following approach:
DECLARE @.Start smalldatetime,
@.End smalldatetime
SET @.Start = '2005-03-22T17:00:00'
SET @.End = '2005-04-01T14:00:00'
SELECT DATEDIFF (minute, @.Start, @.End) / 60.0
- DATEDIFF (day, @.Start, @.End) * 14.5
- (SELECT COUNT(*)
FROM Calendar
WHERE dt > @.Start
AND dt < @.End
AND (isWeekday = 0 OR isHoliday = 1)) * 9.5
This might not be the quickes, but it has the advantage that it'spretty
straightforward: first, calculate the number of clock hours from start
to end; then subtract 14.5 hours (the time from 5:00 PM - 7:30 AM) for
each full day in the range; finally subtract another 9.5 hours (the time
from 7:30 AM to 5:00 PM) for each weekend or holiday in the range.
The assumption I made is that start and end dates will always be during
opening hours (i.e. not on weekends or on holidays and never outside the
7:30 AM - 5:00 PM range).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Hi mitchchristensen,
> The easiest way to do it is to use a calendar table. What that is, how
> you can make it and various good ways to use it are described at Aaron's
> site: http://www.aspfaq.com/show.asp?id=2519.
> For this specific situation, I'd suggest the following approach:
> DECLARE @.Start smalldatetime,
> @.End smalldatetime
> SET @.Start = '2005-03-22T17:00:00'
> SET @.End = '2005-04-01T14:00:00'
> SELECT DATEDIFF (minute, @.Start, @.End) / 60.0
> - DATEDIFF (day, @.Start, @.End) * 14.5
> - (SELECT COUNT(*)
> FROM Calendar
> WHERE dt > @.Start
> AND dt < @.End
> AND (isWeekday = 0 OR isHoliday = 1)) * 9.5
Since it seems unlikely that Mitch would like to disregard Christmas,
Thanksgiving and other holidays, Hugo solutions is very good. However,
I believe this is a solution that would work if we are for some reason
talking all Monday to Friday:
SELECT DATEDIFF (minute, @.start, @.stop) / 60.0 -
DATEDIFF (day, @.start, @.stop) * 14.5 -
DATEDIFF (week, @.start, @.stop) * 2 * 9.5
I owe Hugo's original query lot for this extension.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the suggestions!
Unfortunately I do need to disregard certain holidays based on the
region(I will have a holiday calendar for each region)
Also, end dates can occur on weekends or even holidays as some issues
are closed by the system based on a time parameter and the system has
no regards for whether or not it's an actual working day.
Mitch|||I have tried to do this in SQL and can tell you that you will be better
off doing it in application logic. The solution posted above performs
terribly.|||(mitchchristensen@.gmail.com) writes:
> Unfortunately I do need to disregard certain holidays based on the
> region(I will have a holiday calendar for each region)
Well, Hugo's solution should be your choice.
> Also, end dates can occur on weekends or even holidays as some issues
> are closed by the system based on a time parameter and the system has
> no regards for whether or not it's an actual working day.
Since I don't have any test data, I can't test Hugo's solution
for the case where the end date is a non a working day, but at a
glance it appers that his solution should handle this situation.
Since it was time, I repost Hugo's solution here:
DECLARE @.Start smalldatetime,
@.End smalldatetime
SET @.Start = '2005-03-22T17:00:00'
SET @.End = '2005-04-01T14:00:00'
SELECT DATEDIFF (minute, @.Start, @.End) / 60.0
- DATEDIFF (day, @.Start, @.End) * 14.5
- (SELECT COUNT(*)
FROM Calendar
WHERE dt > @.Start
AND dt < @.End
AND (isWeekday = 0 OR isHoliday = 1)) * 9.5
The complete thead can be reviewed at
http://groups.google.com/groups?dq=...0127.0.0.1%253E
--
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 Audit Information
Hi People
We want one trigger which captures the data as mentioned below.
Table Name
Date and Time
User
type
Mode of modification
TABLE_ABC
03/07/2007 12:00:04
XCS\Raoa
Update
Procedure
TABLE_DEF
03/07/2007 12:00:34
XCS\Raoa
Insert
Class Integration SSIS Package
TABLE_GHI
03/07/2007 12:01:04
XCS\Raoa
Insert
Procedure
TABLE_GHI
03/07/2007 12:01:34
XCS\Raoa
Update
XCS\Raoa (Manual)
I am not sure about how to achieve the last column. I hope u understand what exactly is expected. The idea is that, one should be able to track how a particular table was manipulate; whether it was manipulated using procedure or SSIS package or manually.
Could someone help me achieving this?
Regards
Abhi
Hi Abhi,
I think you are looking for a DDL trigger. Here is my example. We are monitoring the changes on views, procedures, etc. We are using this:
Code Snippet
USE [databasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_DDL_DDLEventLog](
[EventDate] [datetime] NOT NULL,
[UserName] [sysname] NOT NULL,
[ObjectName] [sysname] NOT NULL,
[CommandText] [varchar](max) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [databasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DDL_Alter_Audit]
ON DATABASE
FOR ALTER_TABLE, ALTER_PROCEDURE, ALTER_FUNCTION, ALTER_VIEW, ALTER_TRIGGER
AS
DECLARE @.eventData XML
SET @.eventData = eventdata()
INSERT T_DDL_DDLEventLog (EventDate, UserName, ObjectName, CommandText)
SELECT
GETDATE() AS EventDate,
@.eventData.value('data(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME')
AS UserName,
@.eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
AS ObjectName,
@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)') AS CommandText
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [DDL_Alter_Audit] ON DATABASE
Also, you can find more informationabout DDL triggers in the BOL.
I hope it helps.
Regards,
Janos
|||Thanks for replying Janos.
What does following statement return?
@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)') AS CommandText
Anyways, I m looking for DML triggers. Whenever someone inserts/updates the table, I wanna know how that table was manipulated..was it manipulated using SSIS package or stored procedure or manually by some user. Is it possible to capture this information
|||Hi,
CommandText returns the T-SQL script ran against the object. Eg.: In got a table called Table1. when I'm going to alter it, the Command text will contain the altering sql script, like ALTER TABLE Table1 .....
LoginName will return the credentila used to make the alter on the table and the ObjectName return Table1, in this case.
You can make this DDL trigger for all database events with DDL_DATABASE_LEVEL_EVENTS event, but this still monitors the objects, not monitoring your DML actions. You should write DML triggers for all your tables required audit.
Regards,
Janos
|||Thanks for you reply Janos.. Cheers!!!
Obtaining Audit Information
Hi People
We want one trigger which captures the data as mentioned below.
Table Name
Date and Time
User
type
Mode of modification
TABLE_ABC
03/07/2007 12:00:04
XCS\Raoa
Update
Procedure
TABLE_DEF
03/07/2007 12:00:34
XCS\Raoa
Insert
Class Integration SSIS Package
TABLE_GHI
03/07/2007 12:01:04
XCS\Raoa
Insert
Procedure
TABLE_GHI
03/07/2007 12:01:34
XCS\Raoa
Update
XCS\Raoa (Manual)
I am not sure about how to achieve the last column. I hope u understand what exactly is expected. The idea is that, one should be able to track how a particular table was manipulate; whether it was manipulated using procedure or SSIS package or manually.
Could someone help me achieving this?
Regards
Abhi
Hi Abhi,
I think you are looking for a DDL trigger. Here is my example. We are monitoring the changes on views, procedures, etc. We are using this:
Code Snippet
USE [databasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_DDL_DDLEventLog](
[EventDate] [datetime] NOT NULL,
[UserName] [sysname] NOT NULL,
[ObjectName] [sysname] NOT NULL,
[CommandText] [varchar](max) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [databasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DDL_Alter_Audit]
ON DATABASE
FOR ALTER_TABLE, ALTER_PROCEDURE, ALTER_FUNCTION, ALTER_VIEW, ALTER_TRIGGER
AS
DECLARE @.eventData XML
SET @.eventData = eventdata()
INSERT T_DDL_DDLEventLog (EventDate, UserName, ObjectName, CommandText)
SELECT
GETDATE() AS EventDate,
@.eventData.value('data(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME')
AS UserName,
@.eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
AS ObjectName,
@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)') AS CommandText
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [DDL_Alter_Audit] ON DATABASE
Also, you can find more informationabout DDL triggers in the BOL.
I hope it helps.
Regards,
Janos
|||Thanks for replying Janos.
What does following statement return?
@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)') AS CommandText
Anyways, I m looking for DML triggers. Whenever someone inserts/updates the table, I wanna know how that table was manipulated..was it manipulated using SSIS package or stored procedure or manually by some user. Is it possible to capture this information
|||Hi,
CommandText returns the T-SQL script ran against the object. Eg.: In got a table called Table1. when I'm going to alter it, the Command text will contain the altering sql script, like ALTER TABLE Table1 .....
LoginName will return the credentila used to make the alter on the table and the ObjectName return Table1, in this case.
You can make this DDL trigger for all database events with DDL_DATABASE_LEVEL_EVENTS event, but this still monitors the objects, not monitoring your DML actions. You should write DML triggers for all your tables required audit.
Regards,
Janos
|||Thanks for you reply Janos.. Cheers!!!
Friday, February 24, 2012
Obtaining all the dates
According to a date introduce I would need to obtain all the periodDesc of
the following table:
CREATE TABLE [dbo].[tbl_Periods] (
[sinStudyID] [smallint] NOT NULL ,
[strStudy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[boldone] [bit] NOT NULL ,
[sinPeriodID] [smallint] NOT NULL ,
[strPeriodDesc] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[datPeriodBegin] [datetime] NULL ,
[datPeriodEnd] [datetime] NULL ,
)
If I have got as input '2005-01-02' I need obtain this set of rows:
begin end
4 COW 0 203 W2005006 2005-01-30 2005-01-31
4 COW 0 202 W2005005 2005-01-23 2005-01-29
4 COW 0 201 W2005004 2005-01-16 2005-01-22
4 COW 0 196 W2005003 2005-01-09 2005-01-15
4 COW 0 195 W2005002 2005-01-02 2005-01-08
4 COW 0 194 W2005001 2005-01-01 2005-01-01
Any advice will be well received.
Regards,SELECT <column lists> FROM Table
WHERE datPeriodBegin >=@.dt AND datPeriodEnd < dateadd(day,1,@.dt)
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:BD321F1D-7679-4CFF-AFAB-137307FDCC84@.microsoft.com...
> Dear all,
> According to a date introduce I would need to obtain all the periodDesc of
> the following table:
>
> CREATE TABLE [dbo].[tbl_Periods] (
> [sinStudyID] [smallint] NOT NULL ,
> [strStudy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [boldone] [bit] NOT NULL ,
> [sinPeriodID] [smallint] NOT NULL ,
> [strPeriodDesc] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [datPeriodBegin] [datetime] NULL ,
> [datPeriodEnd] [datetime] NULL ,
> )
> If I have got as input '2005-01-02' I need obtain this set of rows:
> begin end
> 4 COW 0 203 W2005006 2005-01-30 2005-01-31
> 4 COW 0 202 W2005005 2005-01-23 2005-01-29
> 4 COW 0 201 W2005004 2005-01-16 2005-01-22
> 4 COW 0 196 W2005003 2005-01-09 2005-01-15
> 4 COW 0 195 W2005002 2005-01-02 2005-01-08
> 4 COW 0 194 W2005001 2005-01-01 2005-01-01
> Any advice will be well received.
> Regards,|||And the date you provide is related to the datPeriodBegin and datPeriodEnd
in which way? Is it related to datPeriodBegin only, datPeriodEnd only or
both?
Jacco Schalkwijk
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:BD321F1D-7679-4CFF-AFAB-137307FDCC84@.microsoft.com...
> Dear all,
> According to a date introduce I would need to obtain all the periodDesc of
> the following table:
>
> CREATE TABLE [dbo].[tbl_Periods] (
> [sinStudyID] [smallint] NOT NULL ,
> [strStudy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [boldone] [bit] NOT NULL ,
> [sinPeriodID] [smallint] NOT NULL ,
> [strPeriodDesc] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [datPeriodBegin] [datetime] NULL ,
> [datPeriodEnd] [datetime] NULL ,
> )
> If I have got as input '2005-01-02' I need obtain this set of rows:
> begin end
> 4 COW 0 203 W2005006 2005-01-30 2005-01-31
> 4 COW 0 202 W2005005 2005-01-23 2005-01-29
> 4 COW 0 201 W2005004 2005-01-16 2005-01-22
> 4 COW 0 196 W2005003 2005-01-09 2005-01-15
> 4 COW 0 195 W2005002 2005-01-02 2005-01-08
> 4 COW 0 194 W2005001 2005-01-01 2005-01-01
> Any advice will be well received.
> Regards,|||Hi,
Only could be this, nothing else:
2005-01-30
2005-01-23
2005-01-16
2005-01-09
2005-01-02
2005-01-01
Thanks,
"Jacco Schalkwijk" wrote:
> And the date you provide is related to the datPeriodBegin and datPeriodEnd
> in which way? Is it related to datPeriodBegin only, datPeriodEnd only or
> both?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:BD321F1D-7679-4CFF-AFAB-137307FDCC84@.microsoft.com...
>
>|||Sorry, these former values are efectively just for datPeriodBegin
Best wishes,
"Enric" wrote:
> Hi,
>
> Only could be this, nothing else:
> 2005-01-30
> 2005-01-23
> 2005-01-16
> 2005-01-09
> 2005-01-02
> 2005-01-01
>
> Thanks,
> "Jacco Schalkwijk" wrote:
>|||And how does that list of dates relate to 2005-01-02? All in the same month?
In that case:
DECLARE @.date DATETIME
SET @.date = '20050102'
SELECT [sinStudyID] , [strStudy], [boldone], [sinPeriodID],
[strPeriodDesc], [datPeriodBegin], [datPeriodEnd]
FROM [tbl_Periods]
WHERE datPeriodBegin >= DATEADD(dd, 1 - DAY(@.dt), @.dt)
AND datPeriodBegin < DATEADD(mm, 1, DATEADD(dd, 1 - DAY(@.dt), @.dt))
Jacco Schalkwijk
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:9540B0BB-7599-4C48-A6B4-EE78E9585884@.microsoft.com...
> Sorry, these former values are efectively just for datPeriodBegin
> Best wishes,
> "Enric" wrote:
>|||It works amazingly.
Thanks a lot man,
"Jacco Schalkwijk" wrote:
> And how does that list of dates relate to 2005-01-02? All in the same mont
h?
> In that case:
> DECLARE @.date DATETIME
> SET @.date = '20050102'
> SELECT [sinStudyID] , [strStudy], [boldone], [sinPeriodID],
> [strPeriodDesc], [datPeriodBegin], [datPeriodEnd]
> FROM [tbl_Periods]
> WHERE datPeriodBegin >= DATEADD(dd, 1 - DAY(@.dt), @.dt)
> AND datPeriodBegin < DATEADD(mm, 1, DATEADD(dd, 1 - DAY(@.dt), @.dt))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:9540B0BB-7599-4C48-A6B4-EE78E9585884@.microsoft.com...
>
>
obtain a date from week data
I have a problem with date functions...
In my program I use weeks, and with this variable I need to know which is the first day of the selected week...
For example... I put week 52 in my program... how I can obtain the first day of this week? -> (22/12/2003).
I have proved with this functions...
SET DATEFIRST 1 (to configure Monday as first day of week)
SELECT DATEADD(ww,DATEDIFF(ww,0,GETDATE()),0)
With this I have the day of the current week... but I can't put my week in this function... aarrgggg.
Please help me...
Thanks a lot.declare @.week tinyint, @.firstdate varchar(4)
-- Choose the week number
set @.week = 30
-- Choose the year
set @.firstdate = '2003-01-01'
select dateadd(day, (datepart(weekday, @.firstdate) - 1) * -1, dateadd(week, @.week, @.firstdate))|||Originally posted by joejcheng
declare @.week tinyint, @.firstdate varchar(10)
-- Choose the week number
set @.week = 30
-- Choose the year
set @.firstdate = '2003-01-01'
select dateadd(day, (datepart(weekday, @.firstdate) - 1) * -1, dateadd(week, @.week, @.firstdate))|||Simply Outstanding ... why did I not think of it first ?? :)
Monday, February 20, 2012
Objects by date
like views, procedures and so on...
Thanks,
VictorBy modified, do you mean when data in tables were modified, or structure of tables? SQL Server
doesn't keep track of when data were modified. For 2005, you can see when structure were modified in
sys.objects.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victor Rodriguez" <lasalsatx@.newsgroups.nospam> wrote in message
news:e%23OmtzibGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Is there a way to pull a list of objects by modified date?
> like views, procedures and so on...
> Thanks,
> Victor
>|||I mean changes on the structure of objects.
thanks,
Victor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23K7Cx3ibGHA.3352@.TK2MSFTNGP03.phx.gbl...
> By modified, do you mean when data in tables were modified, or structure
> of tables? SQL Server doesn't keep track of when data were modified. For
> 2005, you can see when structure were modified in sys.objects.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victor Rodriguez" <lasalsatx@.newsgroups.nospam> wrote in message
> news:e%23OmtzibGHA.3908@.TK2MSFTNGP02.phx.gbl...
>> Is there a way to pull a list of objects by modified date?
>> like views, procedures and so on...
>> Thanks,
>> Victor
>>
>|||Hi Victor,
Thanks for your post.
As Tibor mentioned, you could find the latest modified datetime of each sql
server object in the sys.objects catalog view.
The column modify_date includes the date when the object was last modified
by using an ALTER statement. If the object is a table or a view,
modify_date also changes when a clustered index on the table or view is
created or altered.
The column type_desc includes the object type. Example:
SQL_STORED_PROCEDURE
USER_TABLE
VIEW
Here is the article about sys.objects catelog view:
sys.objects (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms190324.aspx
Thank you for your patience and hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community 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.
Objects by date
like views, procedures and so on...
Thanks,
VictorBy modified, do you mean when data in tables were modified, or structure of
tables? SQL Server
doesn't keep track of when data were modified. For 2005, you can see when st
ructure were modified in
sys.objects.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victor Rodriguez" <lasalsatx@.newsgroups.nospam> wrote in message
news:e%23OmtzibGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Is there a way to pull a list of objects by modified date?
> like views, procedures and so on...
> Thanks,
> Victor
>|||I mean changes on the structure of objects.
thanks,
Victor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23K7Cx3ibGHA.3352@.TK2MSFTNGP03.phx.gbl...
> By modified, do you mean when data in tables were modified, or structure
> of tables? SQL Server doesn't keep track of when data were modified. For
> 2005, you can see when structure were modified in sys.objects.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victor Rodriguez" <lasalsatx@.newsgroups.nospam> wrote in message
> news:e%23OmtzibGHA.3908@.TK2MSFTNGP02.phx.gbl...
>|||Hi Victor,
Thanks for your post.
As Tibor mentioned, you could find the latest modified datetime of each sql
server object in the sys.objects catalog view.
The column modify_date includes the date when the object was last modified
by using an ALTER statement. If the object is a table or a view,
modify_date also changes when a clustered index on the table or view is
created or altered.
The column type_desc includes the object type. Example:
SQL_STORED_PROCEDURE
USER_TABLE
VIEW
Here is the article about sys.objects catelog view:
sys.objects (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms190324.aspx
Thank you for your patience and hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community 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.