Obtaining count for identity

Is there a way to query a SQL server such that it will return the
current counter value of a field that increments automatically (an
identity field)? Using a MAX query might not necessarily give you the
correct information because it is possible that the high value records
were deleted. I checked the system tables and none of them appear to
have what I need. I suppose it would be possible to maintain a table
that lists all the incrementing columns, but ideally there would be a
way already built into SQL Server.

Thanks.On 15 Jun 2005 14:00:46 -0700, hartley_aaron@.hotmail.com wrote:

>Is there a way to query a SQL server such that it will return the
>current counter value of a field that increments automatically (an
>identity field)? Using a MAX query might not necessarily give you the
>correct information because it is possible that the high value records
>were deleted. I checked the system tables and none of them appear to
>have what I need. I suppose it would be possible to maintain a table
>that lists all the incrementing columns, but ideally there would be a
>way already built into SQL Server.
>Thanks.

Hi hartley_aaron,

Look up IDENT_CURRENT in Books Online.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Look up IDENT_CURRENT or @.@.IDENTITY in Books Online.

HTH,
Stu|||It worked great.

Thank you both!

Obtaining CD in the UK

Anybody had any luck with this... It's hardly an obvious process is it
?Yea i have got the CD. If you got your SQL licenses through OSL agreement
(like us) then get in contact with the guys who supplied your agreement and
they should be able to send it to you. It should be free apart from the
media cost, about £20 for us.
Mark
"mt@.calluk.com" wrote:
> Anybody had any luck with this... It's hardly an obvious process is it
> ?
>

Obtaining business hours based on start and end date

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!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!!!

obtaining an image from another server

Hello,
My report gets an image off another server. It works fine in testing, but
when I deploy it to my production server the image doesnt show up. I put a
try/catch around the code and output the error to a text field in my report,
only to see the following:
System.UnauthorizedAccessException: Access to the path
\\server\domain\Applications\myApp\Pictures is denied. at
System.IO.__Error.WinIOError(Int32 errorCode, String str) at
System.IO.Directory.InternalGetFileDirectoryNames(String fullPath, String
userPath, Boolean file) at System.IO.Directory.InternalGetFiles(String path,
String userPath, String searchPattern) at
System.IO.Directory.GetFiles(String path, String searchPattern) at
CustomCodeProxy.test(Int32 PartID)
The user that is running the report is myself. I have access to the server.
I'm using the code section in the report (I created a function called
test()) and within that I'm using System.IO.Directory.GetFiles to see if the
file exists.I should also mention that I'm displaying the image from a virtual
directory, but sometimes the image doesnt exist, which is why I'm trying to
check to see if it exists, but GetFiles doesnt accept URLs.
Is there any way around this? There should be a "noimage" property for
images just like theres a "norow" property for rows
"Brandon" <broberts@.presstran.com> wrote in message
news:eGMxMvFRGHA.256@.TK2MSFTNGP14.phx.gbl...
> Hello,
> My report gets an image off another server. It works fine in testing, but
> when I deploy it to my production server the image doesnt show up. I put
> a try/catch around the code and output the error to a text field in my
> report, only to see the following:
>
> System.UnauthorizedAccessException: Access to the path
> \\server\domain\Applications\myApp\Pictures is denied. at
> System.IO.__Error.WinIOError(Int32 errorCode, String str) at
> System.IO.Directory.InternalGetFileDirectoryNames(String fullPath, String
> userPath, Boolean file) at System.IO.Directory.InternalGetFiles(String
> path, String userPath, String searchPattern) at
> System.IO.Directory.GetFiles(String path, String searchPattern) at
> CustomCodeProxy.test(Int32 PartID)
>
> The user that is running the report is myself. I have access to the
> server.
>
> I'm using the code section in the report (I created a function called
> test()) and within that I'm using System.IO.Directory.GetFiles to see if
> the file exists.
>

Obtaining all the dates

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,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...
>
>