ODBC error

Dear all,
We are using Ipswitch email application “ICS 2006’ with SQL 2000
enterprise edition as database. Occasionally web mail users are facing the
below
error on their web page.
‘ERROR [HY001][Microsoft][ODBC Microsoft Access Driver] Not enough space on
temporary disk. ERROR [IM006][Microsoft][ODBC Driver Manager] Driver’s
SQLSetConnectAttr failed ERROR [HY001][Microsoft][ODBC Microsoft Access
Driver] Not enough space on temporary disk.’
The problem gets rectified only after we restart the IIS service on the mail
server. What could be the reason for this problem? Kindly provide me with
your suggestions.
Regards
Ram
It's an error from MS Access - the cause depends on what you
are doing in Access, what version of Access, etc.
Try posting on one of the Access forums or newsgroups.
-Sue
On Sat, 17 Feb 2007 20:59:08 -0800, Ram
<Ram@.discussions.microsoft.com> wrote:

>Dear all,
> We are using Ipswitch email application ICS 2006 with SQL 2000
>enterprise edition as database. Occasionally web mail users are facing the
>below
>error on their web page.
> ERROR [HY001][Microsoft][ODBC Microsoft Access Driver] Not enough space on
>temporary disk. ERROR [IM006][Microsoft][ODBC Driver Manager] Drivers
>SQLSetConnectAttr failed ERROR [HY001][Microsoft][ODBC Microsoft Access
>Driver] Not enough space on temporary disk.
>The problem gets rectified only after we restart the IIS service on the mail
>server. What could be the reason for this problem? Kindly provide me with
>your suggestions.
>Regards
>Ram
>

Odbc error

Dear all
My one user has installed sql server 2000 enterprise edition , SP3
(8.00.0860) on my computer.
I have created one odbc connection to sql server.
When he want to connect to sql user via odbc in application he gets
following error
Statement(s) could not be prepared
Unable to close cursor
[Microsoft][ODBC SQL Server Driver]Invalid cursor stat
What can be the problem?
RegardsWhat language is the application written in? At least the failing module?
I have seen similar errors in both T/SQL (implying a Stored Procedure) and C
(api level calls) modules.
I suspect it needs to be relinked with your new modules. To me, it sounds
like the api calls either are for a different ODBC release or have messed up
pointers.
If you can supply error numbers and complete error messages, it woulld help
--
Joseph R.P. Maloney, CSP,CCP,CDP
"amish" wrote:
> Dear all
> My one user has installed sql server 2000 enterprise edition , SP3
> (8.00.0860) on my computer.
> I have created one odbc connection to sql server.
> When he want to connect to sql user via odbc in application he gets
> following error
> Statement(s) could not be prepared
> Unable to close cursor
> [Microsoft][ODBC SQL Server Driver]Invalid cursor stat
> What can be the problem?
> Regards
>

ODBC error

Dear all,
We are using Ipswitch email application “ICS 2006’ with SQL 2000
enterprise edition as database. Occasionally web mail users are facing the
below
error on their web page.
‘ERROR [HY001][Microsoft][ODBC Microsoft Access Driver] Not en
ough space on
temporary disk. ERROR [IM006][Microsoft][ODBC Driver Manager] Dr
iver’s
SQLSetConnectAttr failed ERROR [HY001][Microsoft][ODBC Microsoft
Access
Driver] Not enough space on temporary disk.’
The problem gets rectified only after we restart the IIS service on the mail
server. What could be the reason for this problem? Kindly provide me with
your suggestions.
Regards
RamIt's an error from MS Access - the cause depends on what you
are doing in Access, what version of Access, etc.
Try posting on one of the Access forums or newsgroups.
-Sue
On Sat, 17 Feb 2007 20:59:08 -0800, Ram
<Ram@.discussions.microsoft.com> wrote:

>Dear all,
> We are using Ipswitch email application ICS 2006 with SQL 2000
>enterprise edition as database. Occasionally web mail users are facing the
>below
>error on their web page.
> ERROR [HY001][Microsoft][ODBC Microsoft Access Driver] Not en
ough space on
>temporary disk. ERROR [IM006][Microsoft][ODBC Driver Manager] D
rivers
>SQLSetConnectAttr failed ERROR [HY001][Microsoft][ODBC Microsof
t Access
>Driver] Not enough space on temporary disk.
>The problem gets rectified only after we restart the IIS service on the mai
l
>server. What could be the reason for this problem? Kindly provide me with
>your suggestions.
>Regards
>Ram
>sql

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 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 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 different timings for the same process?

Dear fellows,
The following loop bring me differents results, execute one after one (among
them a truncate table, of course):
DECLARE @.loop as integer
set @.loop = 1
while @.loop < 10000
begin
insert into A_test(id,nombre,ape) values(@.loop,'a','aadfasdf')
set @.loop = @.loop + 1
end
1st: 26 sec.
2nd: 30 sec.
3rd: 32 sec.
What's happening?
Any input would be much appreciated.
Enric"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:F66A1DC3-E5CD-4B06-AE4E-53818843E3C5@.microsoft.com...
> Dear fellows,
> The following loop bring me differents results, execute one after one
> (among
> them a truncate table, of course):
> DECLARE @.loop as integer
> set @.loop = 1
> while @.loop < 10000
> begin
> insert into A_test(id,nombre,ape) values(@.loop,'a','aadfasdf')
> set @.loop = @.loop + 1
> end
>
> 1st: 26 sec.
> 2nd: 30 sec.
> 3rd: 32 sec.
> What's happening?
> Any input would be much appreciated.
> Enric
What other processes are running on your server while this is running.
Page splitting or allocation of new extents to store your data may be
happening.
Indexes may be getting updated
Statistics may be getting updated
It's hard to say what the real culprit is. These are some things that I
would check however.
Rick Sawtell
MCT, MCSD, MCDBA|||Well, what does your Profiler say?
ML
http://milambda.blogspot.com/|||Thanks to both for the quick responses but I was wondering about the
difference among them. It seems very high. We are talking about 6 or 7
seconds for a total of 35 more or less..
"Rick Sawtell" wrote:

> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:F66A1DC3-E5CD-4B06-AE4E-53818843E3C5@.microsoft.com...
> What other processes are running on your server while this is running.
> Page splitting or allocation of new extents to store your data may be
> happening.
> Indexes may be getting updated
> Statistics may be getting updated
>
> It's hard to say what the real culprit is. These are some things that I
> would check however.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

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