Obtaining Window login name from a trigger

Hi,
We are running SQL 2K with SQL Server and Windows authentication. Our system has been designed to login external user to SQL Server using one login account. I am trying to retrieve that actual Windows login user name from a trigger. Does anyone know ho
w I accomphish this task.
Try SUSER_SNAME function:
http://msdn.microsoft.com/library/de...u-sus_86xx.asp
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.
|||If you are using something like MTS / COM+ then you're not going to be able to get that information unless the component explicitly passes in the user name.
"Herb" wrote:

> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our system has been designed to login external user to SQL Server using one login account. I am trying to retrieve that actual Windows login user name from a trigger. Does anyone know
how I accomphish this task.
|||Hi,
Thanks for your suggestion. When I issue SUSER_SNAME while using Window Authication, I do get the Window user ID. However, when I issue SUSER_SNAME while using SQL login, I get the SQL login(for ex. SQL login = RUSER, SUSER_SNAME retrieves RUSER).
"Narayana Vyas Kondreddi" wrote:

> Try SUSER_SNAME function:
> http://msdn.microsoft.com/library/de...u-sus_86xx.asp
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user name
> from a trigger. Does anyone know how I accomphish this task.
>
>
|||You'll need to pass the "real" login name through to SQL. One technique for
this when an application uses a single service account is to use SET
CONTEXT_INFO to pass the information through with the user connection and
pull this out in your trigger (i. Note that you have to explicitly set this
in your app for each connection that you require this information for. In
some configurations this may not be practical/possible. See BOL for more on
the command.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.
|||Thanks for the suggestion. I will give that a try.
"Jasper Smith" wrote:

> You'll need to pass the "real" login name through to SQL. One technique for
> this when an application uses a single service account is to use SET
> CONTEXT_INFO to pass the information through with the user connection and
> pull this out in your trigger (i. Note that you have to explicitly set this
> in your app for each connection that you require this information for. In
> some configurations this may not be practical/possible. See BOL for more on
> the command.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user name
> from a trigger. Does anyone know how I accomphish this task.
>
>
|||Thanks for that information. We still have some apps that are using COM+
Herb
"Shawn Brock" wrote:
[vbcol=seagreen]
> If you are using something like MTS / COM+ then you're not going to be able to get that information unless the component explicitly passes in the user name.
> "Herb" wrote:
w how I accomphish this task.

Obtaining Window login name from a trigger

Hi,
We are running SQL 2K with SQL Server and Windows authentication. Our syste
m has been designed to login external user to SQL Server using one login acc
ount. I am trying to retrieve that actual Windows login user name from a tr
igger. Does anyone know ho
w I accomphish this task.Try SUSER_SNAME function:
http://msdn.microsoft.com/library/d.../>
us_86xx.asp
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.|||If you are using something like MTS / COM+ then you're not going to be able
to get that information unless the component explicitly passes in the user n
ame.
"Herb" wrote:

> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our system has
been designed to login external user to SQL Server using one login account. I am tr
ying to retrieve that actual Windows login user name from a trigger. Does anyone kn
ow
how I accomphish this task.|||Hi,
Thanks for your suggestion. When I issue SUSER_SNAME while using Window Aut
hication, I do get the Window user ID. However, when I issue SUSER_SNAME wh
ile using SQL login, I get the SQL login(for ex. SQL login = RUSER, SUSER_S
NAME retrieves RUSER).
"Narayana Vyas Kondreddi" wrote:

> Try SUSER_SNAME function:
> http://msdn.microsoft.com/library/d...
-sus_86xx.asp
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user nam
e
> from a trigger. Does anyone know how I accomphish this task.
>
>|||You'll need to pass the "real" login name through to SQL. One technique for
this when an application uses a single service account is to use SET
CONTEXT_INFO to pass the information through with the user connection and
pull this out in your trigger (i. Note that you have to explicitly set this
in your app for each connection that you require this information for. In
some configurations this may not be practical/possible. See BOL for more on
the command.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Herb" <Herb@.discussions.microsoft.com> wrote in message
news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> Hi,
> We are running SQL 2K with SQL Server and Windows authentication. Our
system has been designed to login external user to SQL Server using one
login account. I am trying to retrieve that actual Windows login user name
from a trigger. Does anyone know how I accomphish this task.|||Thanks for the suggestion. I will give that a try.
"Jasper Smith" wrote:

> You'll need to pass the "real" login name through to SQL. One technique fo
r
> this when an application uses a single service account is to use SET
> CONTEXT_INFO to pass the information through with the user connection and
> pull this out in your trigger (i. Note that you have to explicitly set thi
s
> in your app for each connection that you require this information for. In
> some configurations this may not be practical/possible. See BOL for more o
n
> the command.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Herb" <Herb@.discussions.microsoft.com> wrote in message
> news:38AB6DF7-C403-469B-A00C-F56CAE4DB2DD@.microsoft.com...
> system has been designed to login external user to SQL Server using one
> login account. I am trying to retrieve that actual Windows login user nam
e
> from a trigger. Does anyone know how I accomphish this task.
>
>|||Thanks for that information. We still have some apps that are using COM+
Herb
"Shawn Brock" wrote:
[vbcol=seagreen]
> If you are using something like MTS / COM+ then you're not going to be abl
e to get that information unless the component explicitly passes in the user
name.
> "Herb" wrote:
>
w how I accomphish this task.

Obtaining values on datasource inserting event

Hi,

I want to be able to spot when the same employee name gets added to my grid. This is to ensure that I cannot not have the same firstname and lastname (i.e. cannot have 2 John Smiths).

It is kind of like spoting for duplicates but they are not PKs. I was hoping if there was a way you could identify the the feild values on the "inserting" event of the datasource so I could put some logic in. The reason for placing it there is because we have the e.cancel = true command.

Thanks in advance,

Jon

Since you are going to have to query the database to get this information, why not just use If Not Exists in your SQL?

|||

Mike,

Thanks for getting back so promptly. I understand what you are saying what does the SQL look like ? would you be able to provide a very basic example?

Just to give you a little more background, I am using a mixture of standard ASP controls and also using the RAD controls. Currently I focus on the controls to perform this kind of checking and would like a more generic solution at the Datasource level, this is so I dont need to worry about specific coding based on which control I use.

Thanks

|||

You would put it in a stored procedure:http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5

Note: there are some other ideas in that article. You can review those as well.

|||

Thanks, marked as answered

|||

You can create a procedure like below and bind it as the insert command for your sql datasource. Then you can execute the insert method in a try catch block and display the error message to the user:

create procedure pcheckDuplicates_insert(@.firstNamevarchar(50) ,@.lastNamevarchar(50))asbeginifexists (select 1from checkDuplicateswhere firstName = @.firstNameand lastName = @.lastName )beginraiserror ('A person with this name already exists',16,1)with nowaitreturnendelsebegininsert checkDuplicates ( firstName , lastName )values ( @.firstName , @.lastName )endreturnend

I don't think you'll need that inserting event of the datasource. You can directly write all your duplicate prevention logic in the procedure itself.

Hope this will help.

Obtaining total of partial

Dear all,
I was wondering how do I for obtain the total for the field 'parcial' in the
same query, of course:
SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
COUNT(*) AS partial
FROM ABS_DIARIOHISTO
WHERE DIA_SUCURSAL = 81
GROUP BY MONTH(DIA_HORAESCRITURA),DAY(HORAESCRITU
RA)
ORDER BY MES,DIA
DDL:
CREATE TABLE [ABS_DiarioHisto] (
[DIA_CodigoApunte] [int] NOT NULL ,
[DIA_MaquinaFisica] [varchar] (36) COLLATE Traditional_Spanish_CI_AS NOT
NULL ,
[DIA_HoraEscritura] [datetime] NULL ,
[DIA_Sucursal] [int] NULL ,
[DIA_Puesto] [smallint] NULL ,
[DIA_Usuario] [char] (6) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_FechaOper] [datetime] NULL ,
[DIA_HoraOpera] [varchar] (8) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_FechaConta] [datetime] NULL ,
[DIA_Comentarios] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_CodOper] [varchar] (6) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_NumCuen] [varchar] (10) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_Importe] [float] NULL ,
[DIA_Predisp] [smallint] NULL ,
[DIA_NumDocum] [varchar] (30) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_ClCajero] [smallint] NULL ,
[DIA_Vise] [varchar] (2) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_NumHost] [int] NULL ,
[DIA_CodError] [int] NULL ,
[DIA_Anulada] [smallint] NULL ,
[DIA_TipoOper] [int] NULL ,
[DIA_NumOper] [int] NULL ,
[DIA_FormatoTXS] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_CodEntidad] [int] NULL ,
[DIA_CodOficina] [int] NULL ,
[DIA_CasoEuro] [tinyint] NULL ,
[DIA_ImporteB] [float] NULL ,
[DIA_CodISO] [varchar] (4) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_CuentaContrapart] [varchar] (16) COLLATE Traditional_Spanish_CI_AS NULL
) ON [PRIMARY]
GO
Thanks in advance and best regards,
Enricsorry, as usual, I haven't enough patience...
select sum(total) from (
SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
count(*) as total
FROM ABS_DIARIOHISTO WHERE DIA_SUCURSAL = 81
GROUP BY MONTH(DIA_HORAESCRITURA),DAY(DIA_HORAESC
RITURA)
) D
"Enric" wrote:

> Dear all,
> I was wondering how do I for obtain the total for the field 'parcial' in t
he
> same query, of course:
> SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
> COUNT(*) AS partial
> FROM ABS_DIARIOHISTO
> WHERE DIA_SUCURSAL = 81
> GROUP BY MONTH(DIA_HORAESCRITURA),DAY(HORAESCRITU
RA)
> ORDER BY MES,DIA
>
> DDL:
> CREATE TABLE [ABS_DiarioHisto] (
> [DIA_CodigoApunte] [int] NOT NULL ,
> [DIA_MaquinaFisica] [varchar] (36) COLLATE Traditional_Spanish_CI_AS NOT
> NULL ,
> [DIA_HoraEscritura] [datetime] NULL ,
> [DIA_Sucursal] [int] NULL ,
> [DIA_Puesto] [smallint] NULL ,
> [DIA_Usuario] [char] (6) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_FechaOper] [datetime] NULL ,
> [DIA_HoraOpera] [varchar] (8) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_FechaConta] [datetime] NULL ,
> [DIA_Comentarios] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_CodOper] [varchar] (6) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_NumCuen] [varchar] (10) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_Importe] [float] NULL ,
> [DIA_Predisp] [smallint] NULL ,
> [DIA_NumDocum] [varchar] (30) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_ClCajero] [smallint] NULL ,
> [DIA_Vise] [varchar] (2) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_NumHost] [int] NULL ,
> [DIA_CodError] [int] NULL ,
> [DIA_Anulada] [smallint] NULL ,
> [DIA_TipoOper] [int] NULL ,
> [DIA_NumOper] [int] NULL ,
> [DIA_FormatoTXS] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_CodEntidad] [int] NULL ,
> [DIA_CodOficina] [int] NULL ,
> [DIA_CasoEuro] [tinyint] NULL ,
> [DIA_ImporteB] [float] NULL ,
> [DIA_CodISO] [varchar] (4) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_CuentaContrapart] [varchar] (16) COLLATE Traditional_Spanish_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
> Thanks in advance and best regards,
> Enric|||Hi
This should be no different to
SELECT COUNT(*)
FROM ABS_DIARIOHISTO
WHERE DIA_SUCURSAL = 81
SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
COUNT(*) as totalDay, ( SELECT COUNT(*) FROM ABS_DIARIOHISTO WHERE
DIA_SUCURSAL = 81 ) AS TotalCount
FROM ABS_DIARIOHISTO WHERE DIA_SUCURSAL = 81
GROUP BY MONTH(DIA_HORAESCRITURA),DAY(DIA_HORAESC
RITURA)
John
"Enric" wrote:
> sorry, as usual, I haven't enough patience...
> select sum(total) from (
> SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
> count(*) as total
> FROM ABS_DIARIOHISTO WHERE DIA_SUCURSAL = 81
> GROUP BY MONTH(DIA_HORAESCRITURA),DAY(DIA_HORAESC
RITURA)
> ) D
>
> "Enric" wrote:
>

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

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(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 version of ADO installed on XP

How is it possible to tell which version of ADO is installed on an XP workstation?

ThanksOne method is with the ADO Connection object Version property. VBScript
example:

Set connection = CreateObject("ADODB.Connection")
MsgBox connection.Version

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Alan Taylor" <alan.taylor8@.abbey.com> wrote in message
news:e821d807.0412160429.79b36010@.posting.google.c om...
> How is it possible to tell which version of ADO is installed on an XP
> workstation?
> Thanks

Obtaining the time from datetime field

From this value: 2002-11-22 00:00:22.000
I want to obtain in string type "00:00:22.000" including miliseconds
I try this but returns: 0:0:22:0
select
Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
from cargaprocesos
Can you help me?
Thanks a lot,Enric, try:
convert(varchar(12), dt, 114)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Enric" wrote:

> From this value: 2002-11-22 00:00:22.000
> I want to obtain in string type "00:00:22.000" including miliseconds
> I try this but returns: 0:0:22:0
> select
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
> from cargaprocesos
> Can you help me?
> Thanks a lot,|||You should go with that:
Select Convert(varchar(12), GETDATE(), 114)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> From this value: 2002-11-22 00:00:22.000
> I want to obtain in string type "00:00:22.000" including miliseconds
> I try this but returns: 0:0:22:0
> select
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
> from cargaprocesos
> Can you help me?
> Thanks a lot,|||Thanks Itzik, I had tried this:
select substring(convert(varchar(50),duracionul
t,113),13,12),
duracionult
from cargaprocesos
But it was failing.
regards,
"Itzik Ben-Gan" wrote:
> Enric, try:
> convert(varchar(12), dt, 114)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Enric" wrote:
>|||A disign suggestion:
I would create a function for this logic.
Makes the code more readable and it makes the logic reusable aswell.
Hope this is helpfull.
Marcel van Eijkel
( www.vaneijkel.com )

Obtaining the time

dear all,
I'm looking for a better version of this stuff. I want to obtain the time of
a datetime field:
DuracionUlt is a datetime field.
SELECT DuracionUlt,
Ltrim(Rtrim(cast(datepart(hh,duracionult
) as char(2))))
+ ':' + Ltrim(Rtrim(cast(datepart(mi,duracionult
) as char(2)))) + ':' +
Ltrim(Rtrim(cast(datepart(ss,duracionult
) as char(2))))
+ ':' +
Ltrim(Rtrim(cast(datepart(ms,duracionult
) as char(3))))
FROM CARGAPROCESOS WHERE PROCESO like 'ARIES%'
AND datediff(dd,duracionult,getdate()) = 1
This query returns:
2006-03-28 00:00:00.000 = 0:0:0:0
2006-03-28 00:00:42.000 = 0:0:42:0
Any comment/suggestion/thought/advice would be very appreciated,
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)SELECT CONVERT(VARCHAR(30),GETDATE(),108)
select {fn extract(minute from getdate())}
Select {fn dayname( GetDate()) }
select {fn CURRENT_DATE()}
select {fn CURRENT_time()}
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:5FAE6527-EA24-490D-A25E-7093C222DF68@.microsoft.com...
> dear all,
> I'm looking for a better version of this stuff. I want to obtain the time
> of
> a datetime field:
> DuracionUlt is a datetime field.
> SELECT DuracionUlt,
> Ltrim(Rtrim(cast(datepart(hh,duracionult
) as char(2))))
> + ':' + Ltrim(Rtrim(cast(datepart(mi,duracionult
) as char(2)))) + ':' +
> Ltrim(Rtrim(cast(datepart(ss,duracionult
) as char(2))))
> + ':' +
> Ltrim(Rtrim(cast(datepart(ms,duracionult
) as char(3))))
> FROM CARGAPROCESOS WHERE PROCESO like 'ARIES%'
> AND datediff(dd,duracionult,getdate()) = 1
> This query returns:
> 2006-03-28 00:00:00.000 = 0:0:0:0
> 2006-03-28 00:00:42.000 = 0:0:42:0
> Any comment/suggestion/thought/advice would be very appreciated,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||thanks a lot
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Uri Dimant" wrote:

> SELECT CONVERT(VARCHAR(30),GETDATE(),108)
> select {fn extract(minute from getdate())}
> Select {fn dayname( GetDate()) }
> select {fn CURRENT_DATE()}
> select {fn CURRENT_time()}
>
>
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:5FAE6527-EA24-490D-A25E-7093C222DF68@.microsoft.com...
>
>

Obtaining the number of records in a sqlDataReader

Can anyone tell me how I can obtain the number of records returned in a sqlDataReader recordset?You will need to read through the records to know how many rows, or alternately, return the rowcount as a column:

SELECT *,(SELECT COUNT(*) FROM Customer WHERE ID=7)
FROM Customer WHERE ID=7

If using stored procedures, perhaps you can do this more elegantly.

Why do you need the count?|||You can also check the @.@.rowcount system variable after the select

select ...
set @.Count = @.@.rowcount|||I need the count because the project I am working on needs to do specific tasks dependent on the no of records returned.

How can I access the @.@.rowcount from inside my code?|||Well, then I think the reality is you are going to have to (either in a seperate query or in a subquery) run the COUNT(*) query and send that as a column in the select statement. You could pass an OUTPUT parameter, but I do not believe the OUTPUT parameter will be readable until the DataReader has been read through.

Perhaps you should just use a DataSet, which has the count available.|||Perhaps a dataset is the better tool for the job then. Will it have any significant overhead compared to a datareader?|||Yes. Rather than reading records one at a time in firehose mode, it will read all rows into memory, and store them in memory (the dataSet is an in-memory representation). If you are processing a huge resultset, it could be a problem vs. the DataReader, but generally it is OK as well.|||You can also try using a stored procedure and you can return the count as either a seperate resultset or using an output paramater.

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
>

obtaining the license key

Hello:
Tomorrow AM, I am going to reinstall SQL 2000 on a new server for a client
of ours as this client's soon-to-be old server has run out of disk space.
I hope to obtain the SQL license key in a timely manner from their IT
department. In case I cannot get this key as quickly as I'd like, though, is
there a way to somehow get this key by reviewing files in the SQL folder on
the old server's hard drive?
Thanks, for your time!
childofthe1980s
http://www.nirsoft.net/utils/product_cd_key_viewer.html
Regards,
Trevor Benedict
MCSD
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:B9D5700B-6E99-44B3-BEED-16E2B7C3F200@.microsoft.com...
> Hello:
> Tomorrow AM, I am going to reinstall SQL 2000 on a new server for a client
> of ours as this client's soon-to-be old server has run out of disk space.
> I hope to obtain the SQL license key in a timely manner from their IT
> department. In case I cannot get this key as quickly as I'd like, though,
> is
> there a way to somehow get this key by reviewing files in the SQL folder
> on
> the old server's hard drive?
> Thanks, for your time!
> childofthe1980s

obtaining the license key

Hello:
Tomorrow AM, I am going to reinstall SQL 2000 on a new server for a client
of ours as this client's soon-to-be old server has run out of disk space.
I hope to obtain the SQL license key in a timely manner from their IT
department. In case I cannot get this key as quickly as I'd like, though, is
there a way to somehow get this key by reviewing files in the SQL folder on
the old server's hard drive?
Thanks, for your time!
childofthe1980shttp://www.nirsoft.net/utils/product_cd_key_viewer.html
Regards,
Trevor Benedict
MCSD
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:B9D5700B-6E99-44B3-BEED-16E2B7C3F200@.microsoft.com...
> Hello:
> Tomorrow AM, I am going to reinstall SQL 2000 on a new server for a client
> of ours as this client's soon-to-be old server has run out of disk space.
> I hope to obtain the SQL license key in a timely manner from their IT
> department. In case I cannot get this key as quickly as I'd like, though,
> is
> there a way to somehow get this key by reviewing files in the SQL folder
> on
> the old server's hard drive?
> Thanks, for your time!
> childofthe1980s

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 the last record

Let's say I need to be able to dynamically retrieve the last record in a table (ChData) in order to be able to continuously monitor the flow of data. Since theoretically there is no such thing defined in SQL, let's say I have an index column - ChTimestamp - (which, for some odd reason, is a DOUBLE), which grows monotonically. Thus, I want the row with the largest ChTimestamp value. The data columns would be Ch0, Ch1, and so on. Let's also assume that for some reason I am limited to using the LabVIEW database connectivity toolkit and because of that, I don't have full use of the SELECT statement but I have to supply the table and column names for the "SELECT FROM " and only the WHERE and the like predicates can be freely specified (I don't know how freely). What would be an elegant query to achieve the desired result? Thanks!
Kamenelegant query to achieve your result:select phonenumber
from dbms_vendors
where dbms in ('sql server','oracle','mysql','postgresql','sybase','db 2')
sorry, couldn't help it

:cool:|||select * from chdata
where
chtimestamp=(select max(chtimestamp) from chdata)

or am I missing something?

Alternatively you could write a trigger to update a known row in another version of the table with a single row so you can have a simpler where clause.

select * from mystupidtable where x='1'|||SELECT TOP 1 * FROM Table1 ORDER BY chTimestamp DESC|||Thank you very much, LoztInSpace and Peso. No it's not a homework, I'm a professional, it's just that I have to deal with so many different technologies, programming languages, APIs, etc., that sometimes it's just easier (not to mention quicker) to ask those who deal with it every day instead of trying to jump start my fried brain. :o

Kamen :angel:|||Maybe it is time to make your niche?|||Actually, small companies appreciate multivalent engineers like me. Plus, I'm too old to change. :cool:
Kamen

obtaining the ip address of connection

any one know how the get the ip address of a source connection to my sql
server using a query/stored procedure/ex stored procedure etc.
essentially to ip address of the "host" process
thanksI don't if IP is possible, but system_user returns the Windows
Authentication domain\user, host_name() returns the workstation name, and
host_id() returns workstation id.
"Matthew Kempf" <mpkempf@.gmail.com> wrote in message
news:OeeW$6ijFHA.4000@.TK2MSFTNGP12.phx.gbl...
> any one know how the get the ip address of a source connection to my sql
> server using a query/stored procedure/ex stored procedure etc.
> essentially to ip address of the "host" process
> thanks
>|||Note that you can't always derive an IP address from a host name, but in an
enclosed environment, this might work for you:
declare @.cmd varchar(255)
set @.cmd = 'ping '+HOST_NAME()
create table #foo (l VARCHAR(8000))
set nocount on
insert #foo exec master..xp_cmdshell @.cmd
select substring
(
l,
charindex('[', l)+1,
charindex(']', l)-charindex('[',l)-1
)
from #foo
WHERE l like 'Pinging%'
drop table #foo
"Matthew Kempf" <mpkempf@.gmail.com> wrote in message
news:OeeW$6ijFHA.4000@.TK2MSFTNGP12.phx.gbl...
> any one know how the get the ip address of a source connection to my sql
> server using a query/stored procedure/ex stored procedure etc.
> essentially to ip address of the "host" process
> thanks
>|||Aaron Bertrand [SQL Server MVP] wrote:
> Note that you can't always derive an IP address from a host name, but
> in an enclosed environment, this might work for you:
>
> declare @.cmd varchar(255)
> set @.cmd = 'ping '+HOST_NAME()
> create table #foo (l VARCHAR(8000))
> set nocount on
> insert #foo exec master..xp_cmdshell @.cmd
> select substring
> (
> l,
> charindex('[', l)+1,
> charindex(']', l)-charindex('[',l)-1
> )
> from #foo
> WHERE l like 'Pinging%'
> drop table #foo
>
Good idea. You'll get better performance if you limit the number of echo
requests to 1 with the -n parameter:
declare @.c nvarchar(255)
set @.c = 'ping ' + host_name() + ' -n 1'
exec master..xp_cmdshell @.c
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> Good idea. You'll get better performance if you limit the number of echo
> requests to 1 with the -n parameter:
Good catch. Though I have to wonder if this is a one-time thing or if we
plan on putting this into production. =)|||thanks,
the problem is I want the closed environment to be able to access, but
certain connections from the firewall not to be able to. I have replication
coming in over the firewall (connection I want), but do not want someone
just trying to launch the app (unwanted connection) from across the
internet, if I knew the ip of the connection this could be accomplished, but
it looks like so far it can't be done.........
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:erKHYNjjFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Aaron Bertrand [SQL Server MVP] wrote:
> Good idea. You'll get better performance if you limit the number of echo
> requests to 1 with the -n parameter:
> declare @.c nvarchar(255)
> set @.c = 'ping ' + host_name() + ' -n 1'
> exec master..xp_cmdshell @.c
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Assuming Windows Authentication, to verify a that a connection is internal,
all you need is to check the host name of the connection. Also, system_user
will return the domain\user like: Cerebrus\jturner
Is resolving to a specific IP really necessary?
Also change the default listening port, remove support for unneeded
protocols, etc.
"Matthew Kempf" <mpkempf@.gmail.com> wrote in message
news:OpUktQjjFHA.1480@.TK2MSFTNGP10.phx.gbl...
> thanks,
> the problem is I want the closed environment to be able to access, but
> certain connections from the firewall not to be able to. I have
> replication coming in over the firewall (connection I want), but do not
> want someone just trying to launch the app (unwanted connection) from
> across the internet, if I knew the ip of the connection this could be
> accomplished, but it looks like so far it can't be done.........
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:erKHYNjjFHA.2152@.TK2MSFTNGP14.phx.gbl...
>|||So what is your protection doing inside the database? Don't you think
Windows (or another application) will be better at analyzing incoming
connections, trying to PREVENT your app from being called, than from the
database merely responding to the fact that your firewall has been breached
and the app has been successfully called?
Again, you seem to be protecting one morsel of cheese from the mice, but
letting them have their way with the rest of the fridge...
"Matthew Kempf" <mpkempf@.gmail.com> wrote in message
news:eBpkbZjjFHA.1048@.tk2msftngp13.phx.gbl...
> it doesn't
> i have satellites and i don't want to try to launch the program.... i
> just know they will try.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:evZZeWjjFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||the firewall is fine; the security is fine
i have an application that makes an encrypted connection to an sql server
located at a satellite. the satellite uses transactional replication over
ssl to push a copy of the database to the host so reporting can be run; so
the only connection that can be made to the sql server is that application
or if you know the sa password or application userid and password. the
problem is if someone launches that application from the satellite and makes
a connection to the firewall (not violating any security). what i'm
attempting to do is write the remote application to have sql server check to
see what ip address the application is connecting from. if the application
sees that it attempting a connection from the firewall ip address then the
app will shutdown. it needs to do this because it will break replication if
this occurs, and a snapshot merge of 24 8gb databases over the internet
sucks royally.
"Aaron Bertrand [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
news:%23$gVjdjjFHA.3336@.TK2MSFTNGP10.phx.gbl...
> So what is your protection doing inside the database? Don't you think
> Windows (or another application) will be better at analyzing incoming
> connections, trying to PREVENT your app from being called, than from the
> database merely responding to the fact that your firewall has been
> breached and the app has been successfully called?
> Again, you seem to be protecting one morsel of cheese from the mice, but
> letting them have their way with the rest of the fridge...
>
>
> "Matthew Kempf" <mpkempf@.gmail.com> wrote in message
> news:eBpkbZjjFHA.1048@.tk2msftngp13.phx.gbl...
>|||> see what ip address the application is connecting from. if the
> application sees that it attempting a connection from the firewall ip
> address then the app will shutdown.
Can you explain "the application"? Is this an EXE, a service, or ...?

Obtaining TCP/IP Port and Pipe name

Hello All,

Does anyone know how to obtain the TCP/IP port and the pipe name for an instance?

I would have expected to see these properties in smo.server.configuration or smo.server.settings. What am I missing here?

Thanks

Rob

Take a look at Microsoft.SqlServer.Management.Smo.Wmi.ServerInstance. ServerProtocols property is a collection with information about each protocol settings.

WBR, Evergray
--
Words mean nothing...

|||

Thanks Evergray,

Unfortunatly, this will not pick-up any 2000 instances, only 2005 instances installed on the same machine.

Does anyone else know of any other method to obtain the pipe/port via standard smo, or? I know that i can issue a UDP broadcast via a socket and the response (with limited success) will include the information I require, so it can't be rocket science!

Thanks

Rob

|||You will need DMO for this (see Registry object), as the SMO only supports SQL Server 2005 instances.|||

Thanks for the reply Michiel. I have, however respectfully unmarked your post as an answer as the registry object does not expose the required information. I can, via WMI access a remote registry and reqad the NP and port info, but there is a massive overhead in doing this.

I'll rephrase my question in the hope someone knows how to achieve what I'm trying to do:

Via a udp broadcast on 1434, the reply from SQL Servers includes the pipe name and TCP/IP port number they are listening on. This is the information I am trying to obtain. I can, very easily obtain this once connected to an instance, but I need this information prior to connecting, as of course the pipe/port may be different depending upon the instance.

Does anyone know of anyway to do this (without using a socket/IP broadcast)?

Cheers

Rob

|||

Thanks for claryfing your answer. I am somewhat puzzled about what you are trying to accomplish. The normal way to approach this configure you server for the required network protocols, and then configure your clients so that it will try the protocols in a preconfigured order. On a SQL Server 2000 and above versions, the connection betwee your client and instance will be 'routed' for you (on SQL Server 2000 it was a function of one of the instance; in SQL Server 2005, the SQL Browser service is introduced).

Are you trying to connect without this facility? If so, then it makes more sense to preconfigure your servers to some known IP port or named pipe names instead of reinventing the SQL Browser service.

At the risk I get unmarked again, please mark if this helps or clarify further if this is not what you're looking for :-)

HTH

|||

Hi Michiel,

Thanks for your help.

Basically, I'm attempting to audit all unauthorised MSSQL instances on our corporate network. This involves firstly listing all instances, then attempting to connect to the discovered instances to obtain further information. In order to connect, I need to know the pipe name and port number the instances are listening on (quite often the port is changed to be something other then 1433). So, I'm in effect trying to discover a way to to obtain the pipe/port of a particular instance without connecting to it first (chicken or the egg?). This could be done via a udp broadcast as the response from the instances would include the pipe/port they are listening on (as per isql -L). I just can't find a way to get this info without using a socket udp boradcast (or wmi for a remote registry interrogation , which is far too much overhead and is prone to security exceptions)...is there a way?

Thanks

Rob

|||

Makes sense. The UDP broadcast is however unreliable, and an instance could be configured to hide itself. There is really no silver bullet here. This is quite a common question however and it has the SQL Server's team attention (which will not help immediately).

If you are domain admin, you could enforce local scans and aggregate that data up to a central point, if you don't want to depend on WMI.

|||

Hi Michiel,

That confirms my suspicions.

Thanks for all your help regardless; I appreciate it.

Cheers

Rob

Obtaining statistical data in MSDB db

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 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 ws (for instance) or be able to desing a "production map" and
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 'Wly'
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 ws (for instance) or be able to desing a "production map" and
> 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, wly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For wly, add in the days of the 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 day'
when 10 then 'Wend day'
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 'Wly'
> 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, wly, Monthly
> when 1 then 'Once'
> when 4 then 'Daily'
> when 8 then 'Wk ' -- For wly, add in the days of the 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 day'
> when 10 then 'Wend day'
> 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 SSRS 2000

What is a way of obtaining and licensing SQL 200 Reporting Services? Is it
available for a download?
ThanksOn Apr 10, 8:53 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> What is a way of obtaining and licensing SQL 200 Reporting Services? Is it
> available for a download?
> Thanks
I don't believe that SSRS 2000 is still available; however, the only
way to license it is to have a license for SQL Server 2000. Depending
on your needs, you could either obtain SQL Srever 2005 Express Edition
w/Advanced Services(incl.
http://www.microsoft.com/downloads/details.aspx?familyid=5B5528B9-13E1-4DB9-A3FC-82116D598C3D&displaylang=en)
w/the SSRS Toolkit for the BIDS environment:
http://www.microsoft.com/downloads/details.aspx?familyid=E8AD606A-0960-4EFD-8BD7-B21370C7BE2B&displaylang=en
-or- you can purchase SQL Server 2005 (which will incl. SSRS). Hope
this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant|||What I have found is:
"SQL Server 2000 Reporting Services Evaluation Edition" download.
Can that be licensed with a license from SQL 2000 server?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1176222887.227623.238510@.w1g2000hsg.googlegroups.com...
> On Apr 10, 8:53 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> What is a way of obtaining and licensing SQL 200 Reporting Services? Is
>> it
>> available for a download?
>> Thanks
> I don't believe that SSRS 2000 is still available; however, the only
> way to license it is to have a license for SQL Server 2000. Depending
> on your needs, you could either obtain SQL Srever 2005 Express Edition
> w/Advanced Services(incl.
> http://www.microsoft.com/downloads/details.aspx?familyid=5B5528B9-13E1-4DB9-A3FC-82116D598C3D&displaylang=en)
> w/the SSRS Toolkit for the BIDS environment:
> http://www.microsoft.com/downloads/details.aspx?familyid=E8AD606A-0960-4EFD-8BD7-B21370C7BE2B&displaylang=en
> -or- you can purchase SQL Server 2005 (which will incl. SSRS). Hope
> this is helpful.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||The problem is, that is a 90 day evaluation edition that will time out.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:enB%23cL5eHHA.5052@.TK2MSFTNGP06.phx.gbl...
> What I have found is:
> "SQL Server 2000 Reporting Services Evaluation Edition" download.
> Can that be licensed with a license from SQL 2000 server?
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1176222887.227623.238510@.w1g2000hsg.googlegroups.com...
>> On Apr 10, 8:53 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> What is a way of obtaining and licensing SQL 200 Reporting Services? Is
>> it
>> available for a download?
>> Thanks
>> I don't believe that SSRS 2000 is still available; however, the only
>> way to license it is to have a license for SQL Server 2000. Depending
>> on your needs, you could either obtain SQL Srever 2005 Express Edition
>> w/Advanced Services(incl.
>> http://www.microsoft.com/downloads/details.aspx?familyid=5B5528B9-13E1-4DB9-A3FC-82116D598C3D&displaylang=en)
>> w/the SSRS Toolkit for the BIDS environment:
>> http://www.microsoft.com/downloads/details.aspx?familyid=E8AD606A-0960-4EFD-8BD7-B21370C7BE2B&displaylang=en
>> -or- you can purchase SQL Server 2005 (which will incl. SSRS). Hope
>> this is helpful.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||But I thought it can be registered within 90 days to avoid expiration.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e7dpNP5eHHA.4868@.TK2MSFTNGP06.phx.gbl...
> The problem is, that is a 90 day evaluation edition that will time out.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mark Goldin" <mgoldin@.ufandd.com> wrote in message
> news:enB%23cL5eHHA.5052@.TK2MSFTNGP06.phx.gbl...
>> What I have found is:
>> "SQL Server 2000 Reporting Services Evaluation Edition" download.
>> Can that be licensed with a license from SQL 2000 server?
>> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
>> news:1176222887.227623.238510@.w1g2000hsg.googlegroups.com...
>> On Apr 10, 8:53 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> What is a way of obtaining and licensing SQL 200 Reporting Services? Is
>> it
>> available for a download?
>> Thanks
>> I don't believe that SSRS 2000 is still available; however, the only
>> way to license it is to have a license for SQL Server 2000. Depending
>> on your needs, you could either obtain SQL Srever 2005 Express Edition
>> w/Advanced Services(incl.
>> http://www.microsoft.com/downloads/details.aspx?familyid=5B5528B9-13E1-4DB9-A3FC-82116D598C3D&displaylang=en)
>> w/the SSRS Toolkit for the BIDS environment:
>> http://www.microsoft.com/downloads/details.aspx?familyid=E8AD606A-0960-4EFD-8BD7-B21370C7BE2B&displaylang=en
>> -or- you can purchase SQL Server 2005 (which will incl. SSRS). Hope
>> this is helpful.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>
>|||I'm not sure. I had to install RS 2000 to get this to occur. One point, the
evaluation edition is the equivalent of Enterprise Edition so if you just
activated it but all you want was Standard edition you would have a problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:OSF3$l5eHHA.1240@.TK2MSFTNGP04.phx.gbl...
> But I thought it can be registered within 90 days to avoid expiration.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e7dpNP5eHHA.4868@.TK2MSFTNGP06.phx.gbl...
>> The problem is, that is a 90 day evaluation edition that will time out.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mark Goldin" <mgoldin@.ufandd.com> wrote in message
>> news:enB%23cL5eHHA.5052@.TK2MSFTNGP06.phx.gbl...
>> What I have found is:
>> "SQL Server 2000 Reporting Services Evaluation Edition" download.
>> Can that be licensed with a license from SQL 2000 server?
>> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
>> news:1176222887.227623.238510@.w1g2000hsg.googlegroups.com...
>> On Apr 10, 8:53 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> What is a way of obtaining and licensing SQL 200 Reporting Services?
>> Is it
>> available for a download?
>> Thanks
>> I don't believe that SSRS 2000 is still available; however, the only
>> way to license it is to have a license for SQL Server 2000. Depending
>> on your needs, you could either obtain SQL Srever 2005 Express Edition
>> w/Advanced Services(incl.
>> http://www.microsoft.com/downloads/details.aspx?familyid=5B5528B9-13E1-4DB9-A3FC-82116D598C3D&displaylang=en)
>> w/the SSRS Toolkit for the BIDS environment:
>> http://www.microsoft.com/downloads/details.aspx?familyid=E8AD606A-0960-4EFD-8BD7-B21370C7BE2B&displaylang=en
>> -or- you can purchase SQL Server 2005 (which will incl. SSRS). Hope
>> this is helpful.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>
>>
>|||If you have licensed SQL Server2000, you do not need to license RS2000, it
came free.
Your problem is that it may be too late to get it from MS, which does not
provide RS2000 download any more. If you cannot upgrade to SQL Server2005 at
this time, your best bet might be to contact the reseller where you bought
your SQL Server2000 from to see what they can do (it may be as simple as
giving you the CD, once they verified you have licensed SQL Server2000?).
There is no technical issue, just availability problem for an old software
(not old enough, though, especially for an expensive server software. MS
should make it available for longer, instead of implicitly enforcing upgrade
to SQL Server2005).
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:uORHbe3eHHA.3932@.TK2MSFTNGP02.phx.gbl...
> What is a way of obtaining and licensing SQL 200 Reporting Services? Is it
> available for a download?
> Thanks
>

Obtaining SQL RSS Developer Addition

I want to install SQL Reporting Services on my laptop which has Windows XP
Pro, SQL 2000 Dev. Edition, and Visual Studio .NET 2003.
Where can I download or obtain the install file for SQL Reporting Services
Developer Edition?
- MaxI tried the same question a while ago with no reply out here.
No I bought msdn pro subscription to get visual studio and here I can
download rep s dev. edition as well.
You should think that you could get it for free if you have sql 2000 dev
edition, though...
"Max Tyack" <MaxTyack@.discussions.microsoft.com> wrote in message
news:89C903A1-E927-4F26-93B0-6D8C63CEFDF5@.microsoft.com...
> I want to install SQL Reporting Services on my laptop which has Windows XP
> Pro, SQL 2000 Dev. Edition, and Visual Studio .NET 2003.
> Where can I download or obtain the install file for SQL Reporting Services
> Developer Edition?
> - Max|||What are you trying to achieve Max?, the Reporting Services server-part does
not run on MSDE you can't install the reports-database on MSDE and it also
needs IIS to host the reports website.
Ed
"Max Tyack" <MaxTyack@.discussions.microsoft.com> wrote in message
news:89C903A1-E927-4F26-93B0-6D8C63CEFDF5@.microsoft.com...
>I want to install SQL Reporting Services on my laptop which has Windows XP
> Pro, SQL 2000 Dev. Edition, and Visual Studio .NET 2003.
> Where can I download or obtain the install file for SQL Reporting Services
> Developer Edition?
> - Max|||But you have different versions of rep s as welle as different versions of
sql server:
developers ediition
standard edition
entreprise edition
dev ed is much cheaper and can be used for dev purposes.
"Ed Richard" <Ed_XXX_@.wss-ed_REMOVE_.nl> wrote in message
news:%23mIKQ8tgFHA.3788@.tk2msftngp13.phx.gbl...
> What are you trying to achieve Max?, the Reporting Services server-part
does
> not run on MSDE you can't install the reports-database on MSDE and it also
> needs IIS to host the reports website.
> Ed
> "Max Tyack" <MaxTyack@.discussions.microsoft.com> wrote in message
> news:89C903A1-E927-4F26-93B0-6D8C63CEFDF5@.microsoft.com...
> >I want to install SQL Reporting Services on my laptop which has Windows
XP
> > Pro, SQL 2000 Dev. Edition, and Visual Studio .NET 2003.
> >
> > Where can I download or obtain the install file for SQL Reporting
Services
> > Developer Edition?
> >
> > - Max
>|||I got lucky and found someone who had the setup file for SQL RSS Dev.
Edition. I am using it on SQL Dev Edition, not SQL MSDE (Desktop Edition).
And running iis on WinXP as well. All are working fine on my XP machine now.
"Michael Vardinghus" wrote:
> But you have different versions of rep s as welle as different versions of
> sql server:
> developers ediition
> standard edition
> entreprise edition
> dev ed is much cheaper and can be used for dev purposes.
> "Ed Richard" <Ed_XXX_@.wss-ed_REMOVE_.nl> wrote in message
> news:%23mIKQ8tgFHA.3788@.tk2msftngp13.phx.gbl...
> > What are you trying to achieve Max?, the Reporting Services server-part
> does
> > not run on MSDE you can't install the reports-database on MSDE and it also
> > needs IIS to host the reports website.
> >
> > Ed
> >
> > "Max Tyack" <MaxTyack@.discussions.microsoft.com> wrote in message
> > news:89C903A1-E927-4F26-93B0-6D8C63CEFDF5@.microsoft.com...
> > >I want to install SQL Reporting Services on my laptop which has Windows
> XP
> > > Pro, SQL 2000 Dev. Edition, and Visual Studio .NET 2003.
> > >
> > > Where can I download or obtain the install file for SQL Reporting
> Services
> > > Developer Edition?
> > >
> > > - Max
> >
> >
>
>|||You can order a CD here:
http://www.microsoft.com/sql/reporting/howtobuy/retailfulfillment.mspx
Ed
"Max Tyack" <MaxTyack@.discussions.microsoft.com> wrote in message
news:89C903A1-E927-4F26-93B0-6D8C63CEFDF5@.microsoft.com...
>I want to install SQL Reporting Services on my laptop which has Windows XP
> Pro, SQL 2000 Dev. Edition, and Visual Studio .NET 2003.
> Where can I download or obtain the install file for SQL Reporting Services
> Developer Edition?
> - Max|||Hi,
This discussion is probably closed, but maybe you can still help me.
Could anyone ever find a link to download the Reporting Services Developer
Edition?
I have Reporting Services Evaluation Edition on my XP machine, but I wonder
what will happen when the 120 day trial duration expires and therefore wanna
install the developer edition.
Thanks,
Gul
"Ed Richard" wrote:
> You can order a CD here:
> http://www.microsoft.com/sql/reporting/howtobuy/retailfulfillment.mspx
> Ed
> "Max Tyack" <MaxTyack@.discussions.microsoft.com> wrote in message
> news:89C903A1-E927-4F26-93B0-6D8C63CEFDF5@.microsoft.com...
> >I want to install SQL Reporting Services on my laptop which has Windows XP
> > Pro, SQL 2000 Dev. Edition, and Visual Studio .NET 2003.
> >
> > Where can I download or obtain the install file for SQL Reporting Services
> > Developer Edition?
> >
> > - Max
>
>