Booleans?

Hello.
I just changed the databases of a website from Access to SQL Server.
Converting the bases went smothly, but now I recieve error message to my SQL-quereries:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'True'.

The query is: "Select pieces,date from orders where active = True AND company_id <> 19 AND authorize_wait = 0 ORDER BY send_date desc"

When I change int the query to: "active = 1" the page works like before (with Access)

I supose SQL Server is able to understand commands with True/False-quereries, so what is wrong here?
Thank you for your help
/CalleMSSQL does not define a "True" or "False" variable. You need to either use a one as you have in your example OR define a local variable

declare @.True bit
set @.True = 1
Select pieces,date
from orders
where active = @.True
AND company_id <> 19
AND authorize_wait = 0
ORDER BY send_date desc|||Thank you for your response!
/Calle

Books online upgrade

Hi,
I recently upgraded the Books online for SQL Server2000
personal edition. Since the when ever I try to open Books
on line I get the below error message.
Cannot open file c:\program files\microsoft SQL Server\80
\tools\books\sql80.col
Can any one please help me?
Thanks
Hi,
Have a look into the below link and proceed :-
http://webservertalk.com/t844230.html
Thanks
Hari
MCDBA
"Siva" <admin@.emoneylinks.com> wrote in message
news:1533a01c4464d$cb368330$a301280a@.phx.gbl...
> Hi,
> I recently upgraded the Books online for SQL Server2000
> personal edition. Since the when ever I try to open Books
> on line I get the below error message.
> Cannot open file c:\program files\microsoft SQL Server\80
> \tools\books\sql80.col
> Can any one please help me?
> Thanks
|||Hi Hari,
I searched my entire hard drive for SQL80.col. But could not find it. would you please help me how to down load it.
Thanks for your help in the long week end.
|||HI,
I Suggest you to install the latest books online. books online can be
downloaded from below link.
http://www.microsoft.com/downloads/d...cb1-a420-445f-
8a4b-bd77a7da194b&DisplayLang=en
Thanks
Hari
MCDBA
"Siva" <anonymous@.discussions.microsoft.com> wrote in message
news:97E9690C-1A33-47F4-8BCF-A6EF6908357F@.microsoft.com...
> Hi Hari,
> I searched my entire hard drive for SQL80.col. But could not find it.
would you please help me how to down load it.
> Thanks for your help in the long week end.
|||Hi Hari,
I down loaded this couple of times. Still I have the same problem. After downloading and Installing I searched my entire drive for SQL80.col but couldnot find it. Any other suggestions please.
Thanks
Siva
sql

books online tutorial - why am i getting an error?

Hi,
I am doing the MSDN Online tutorial "Accessing the Report Server Web Service
Using Visual Basic or Visual C#".
However, when I come to the 3rd step, Accessing the Web Service, and use the
supplied code, I get an error saying
"Namespace or type specified in the Imports
'GetPropertiesSample.ReportService2005' doesn't contain any public member or
cannot be found. Make sure the namespace or the type is defined and contains
at least one public member. Make sure the imported element name doesn't use
any aliases."
and
"Type 'ReportingService2005' is not defined."
This happens when using C# or VB.
What I am doing wrong?
ThanksSorry, I forgot to rename my project to GetPropertiesSample.
Works ok now.
"PacMan" wrote:
> Hi,
> I am doing the MSDN Online tutorial "Accessing the Report Server Web Service
> Using Visual Basic or Visual C#".
> However, when I come to the 3rd step, Accessing the Web Service, and use the
> supplied code, I get an error saying
> "Namespace or type specified in the Imports
> 'GetPropertiesSample.ReportService2005' doesn't contain any public member or
> cannot be found. Make sure the namespace or the type is defined and contains
> at least one public member. Make sure the imported element name doesn't use
> any aliases."
> and
> "Type 'ReportingService2005' is not defined."
> This happens when using C# or VB.
> What I am doing wrong?
> Thanks

Bol error

Anyone know if this has been resolved? Not hugely important, more
amusing. Thought I was patched up.

sp_lock
Reports information about locks.

Syntax
sp_lock [[@.spid1 =] 'spid1'] [,[@.spid2 =] 'spid2']

Arguments
[@.spid1 =] 'spid1'

Is the Microsoft SQL Server process ID number from
master.dbo.sysprocesses. spid1 is int, with a default of NULL. Execute
sp_who to obtain process information about the lock. If spid1 is not
specified, information about all locks is displayed.

[@.spid2 =] 'spid2'

Is another SQL Server process ID number to check for lock information.
spid2 is int, with a default of NULL. spid2 is another spid that may
have a lock at the same time as spid1, and on which the user also
wants information.

Note sp_who can have zero, one, or two parameters. These parameters
determine whether the stored procedure displays locking information on
all, one, or two spid processes.WangKhar (Wangkhar@.yahoo.com) writes:
> Anyone know if this has been resolved? Not hugely important, more
> amusing. Thought I was patched up.

If look in Books Online, on about every topic, there is a mail icon in the
upper left corner. If you click it, you can submit feedback on that
particular topic.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BOL Error

I have upgraded My books online to Updated - SP3 version
and now am getting:
A run time Error has occurred.
Do you wish to debug ?
Line 0
Error: Object doesn't support this property or method.
error when I click on the links of a help topic.
Does anyone get the samething ? Is there a solution to it
other than uninstalling and reinstalling again (I already
did that several times)?
Thanks.Books online are all in html format. What version of
Internet Explorer are you using? You might want to upgrade
your IE to the latest SP as well. HTH.
Leo
>--Original Message--
>I have upgraded My books online to Updated - SP3 version
>and now am getting:
>A run time Error has occurred.
>Do you wish to debug ?
>Line 0
>Error: Object doesn't support this property or method.
>
>error when I click on the links of a help topic.
>Does anyone get the samething ? Is there a solution to it
>other than uninstalling and reinstalling again (I already
>did that several times)?
>Thanks.
>.
>|||I have 'August 2003, Cumulative Patch for Internet
Explorer 6 Service Pack 1 (822925)' as the latest and stil
doing the samething. Could be the IE settings '
Thanks.
>--Original Message--
>Books online are all in html format. What version of
>Internet Explorer are you using? You might want to
upgrade
>your IE to the latest SP as well. HTH.
>Leo
>
>>--Original Message--
>>I have upgraded My books online to Updated - SP3 version
>>and now am getting:
>>A run time Error has occurred.
>>Do you wish to debug ?
>>Line 0
>>Error: Object doesn't support this property or method.
>>
>>error when I click on the links of a help topic.
>>Does anyone get the samething ? Is there a solution to
it
>>other than uninstalling and reinstalling again (I
already
>>did that several times)?
>>Thanks.
>>.
>.
>|||could be the location of the books online. When installing
help files on Windows, sometimes i'd get a similiar error
trying to open
a help file that was in a directory other than the default
expected.
>--Original Message--
>I have 'August 2003, Cumulative Patch for Internet
>Explorer 6 Service Pack 1 (822925)' as the latest and
stil
>doing the samething. Could be the IE settings '
>Thanks.
>
>>--Original Message--
>>Books online are all in html format. What version of
>>Internet Explorer are you using? You might want to
>upgrade
>>your IE to the latest SP as well. HTH.
>>Leo
>>
>>--Original Message--
>>I have upgraded My books online to Updated - SP3
version
>>and now am getting:
>>A run time Error has occurred.
>>Do you wish to debug ?
>>Line 0
>>Error: Object doesn't support this property or method.
>>
>>error when I click on the links of a help topic.
>>Does anyone get the samething ? Is there a solution to
>it
>>other than uninstalling and reinstalling again (I
>already
>>did that several times)?
>>Thanks.
>>.
>>.
>.
>sql

BOL BCP example

When I run the following BOL example:
bcp AdventureWorks.Sales.Currency out Currency.dat -T -c
I get the following error message:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
I have tried multiple variations ... still get the same error ...
This cannot be this hard.
Thanks in advance
GI hope you didn't give it in the SSMS. you are supposed to give it in comman
d
prompt|||Please find attached personal examples for that stuff...
C:\>bcp abs.abs_diario in c:\20050929.dat -n -Ssql4 -Uusrdts1 -Pdts1
C:\>bcp ABS_Diario_Dat in Diariomes10.dat -c -U ABS -P -S SQL1
C:\OFI0501>BCP abs.dbo.ABS_OF501 IN 20050726.DAT -e enric.txt -C -c -Ssql4
-Uusrdts1 -Pdts1
hope that helps
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)
"GH" wrote:

> When I run the following BOL example:
> bcp AdventureWorks.Sales.Currency out Currency.dat -T -c
> I get the following error message:
> Msg 102, Level 15, State 1, Line 2
> Incorrect syntax near '.'.
> I have tried multiple variations ... still get the same error ...
> This cannot be this hard.
> --
> Thanks in advance
> G|||Geeez ...
Thanks for the wake up call ... Doing SQL past 10PM should be a misdemeanor!
!
--
Thanks in advance
G
"Omnibuzz" wrote:

> I hope you didn't give it in the SSMS. you are supposed to give it in comm
and
> prompt|||Thanks Eric
--
Thanks in advance
GH
"Enric" wrote:
> Please find attached personal examples for that stuff...
> C:\>bcp abs.abs_diario in c:\20050929.dat -n -Ssql4 -Uusrdts1 -Pdts1
> C:\>bcp ABS_Diario_Dat in Diariomes10.dat -c -U ABS -P -S SQL1
> C:\OFI0501>BCP abs.dbo.ABS_OF501 IN 20050726.DAT -e enric.txt -C -c -Ssql
4
> -Uusrdts1 -Pdts1
> hope that helps
> --
> 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 informati
on
> without seeing the code. location: Alicante (ES)
>
> "GH" wrote:
>

BOL ABS Overflow error?

Ugh... Why does books online state that ABS can cause an overflow error?

The example given:

The ABS function can produce an overflow error. For example, the tinyint data type can hold only values that range from 0 to 255:

Copy Code SELECT ABS(CONVERT(tinyint, -256)); GO

It's the convert function that causes the overflow error, not ABS itself, right?

Hi Robert,

You're correct. It is the CONVERT function that is causing the overflow error in this example. Thanks for pointing that error out. We'll update the topic with a corrected example in a future update to Books Online.

Here's a better example.

DECLARE @.si smallint

SET @.si = -32768

SELECT @.si = ABS(@.si)

Regards,

Gail

bogus VM error message with Upgrade Wizard

In All Programs/Microsoft SQL Server - Switch is something called SQL Server
Upgrade Wizard. I have no idea what that does, so I thought I'd start it up
and see what happens.
What happens is that I get an error message saying that my virtual memory is
too low, and should be set at the physical memory + 16 MB. My physical
memory is 512 MB and virtual memory is 767 MB, so the error message is
wrong.
Can someone explain what's going on? What does that thing do, anyway?It's designed to upgrade old 6.5 server configuration and databases to SQL
Server 2K. Are you trying to upgrade old 6.5 server configurations and
databases?
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:efe2nK%23UFHA.584@.TK2MSFTNGP15.phx.gbl...
> In All Programs/Microsoft SQL Server - Switch is something called SQL
> Server Upgrade Wizard. I have no idea what that does, so I thought I'd
> start it up and see what happens.
> What happens is that I get an error message saying that my virtual memory
> is too low, and should be set at the physical memory + 16 MB. My physical
> memory is 512 MB and virtual memory is 767 MB, so the error message is
> wrong.
> Can someone explain what's going on? What does that thing do, anyway?
>
>|||No, just exploring.
But why does it refuse to run?
"Michael C#" <xyz@.abcdef.com> wrote in message
news:Ofrfe.20738$o32.19288@.fe09.lga...
> It's designed to upgrade old 6.5 server configuration and databases to SQL
> Server 2K. Are you trying to upgrade old 6.5 server configurations and
> databases?
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:efe2nK%23UFHA.584@.TK2MSFTNGP15.phx.gbl...
>|||Maybe because it can't find any 6.5 databases/server configurations? You'd
have to look up the specific reason in the MSKB. Doesn't sound like it's
worth the effort - considering you don't have any 6.5 databases to upgrde
anyway...
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:uTlQzpEVFHA.2616@.TK2MSFTNGP14.phx.gbl...
> No, just exploring.
> But why does it refuse to run?
>
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:Ofrfe.20738$o32.19288@.fe09.lga...
>

Bogus ''out of disk space'' error message

When running SQLEVAL.EXE, I'm getting an error message when the installation wizard finished 'reading contents of the package' reading:

There is not enough space on C:\ to extract this package.

Having checked the requirements http://www.microsoft.com/sql/editions/developer/sysreqs.mspx, the free disk space on my c:\ is 45.42Gb.

Any ideas?

See: http://blogs.msdn.com/nikop/archive/2005/03/13/394797.aspx

Bogus 'out of disk space' error message

When running SQLEVAL.EXE, I'm getting an error message when the installation wizard finished 'reading contents of the package' reading:

There is not enough space on C:\ to extract this package.

Having checked the requirements http://www.microsoft.com/sql/editions/developer/sysreqs.mspx, the free disk space on my c:\ is 45.42Gb.

Any ideas?

See: http://blogs.msdn.com/nikop/archive/2005/03/13/394797.aspx

Bogus "Invalid object name" error

Hi all,
I'm posting this so it'll be archived in the great google
repository, and hopefully be useful to someone. I spent awhile
troubleshooting what turned out to be a completely misleading error
arising from a simple typo in a query. Here's the crux:
create table foo (id int)
The command(s) completed successfully.
insert foo ('id') values (1)
Server: Msg 208, Level 16, State 3, Line 1
Invalid object name 'foo'.
insert foo (id) values (1)
(1 row(s) affected)
drop table foo
The command(s) completed successfully.
--
My first insert statement has incorrect single quotes around the
column name, which for some reason leads to the error shown.
Unfortunately, my real-life problem was much more convoluted, and
this took a couple hours to debug. I hope this post saves someone
time someday! This is on SQLServer 2000 w/SP3a.
Cheers...It probably won't when it happens, but it may for people who read it :)
These strange types of error messages have been around as long as time
itself. Thanks for posting stuff like this!
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Jerry" <leavemealone@.nowhere.com> wrote in message
news:Xns9660740FD7089leavemealonenowhere
c@.207.225.159.8...
> Hi all,
> I'm posting this so it'll be archived in the great google
> repository, and hopefully be useful to someone. I spent awhile
> troubleshooting what turned out to be a completely misleading error
> arising from a simple typo in a query. Here's the crux:
> --
> create table foo (id int)
> The command(s) completed successfully.
> insert foo ('id') values (1)
> Server: Msg 208, Level 16, State 3, Line 1
> Invalid object name 'foo'.
> insert foo (id) values (1)
> (1 row(s) affected)
> drop table foo
> The command(s) completed successfully.
> --
> My first insert statement has incorrect single quotes around the
> column name, which for some reason leads to the error shown.
> Unfortunately, my real-life problem was much more convoluted, and
> this took a couple hours to debug. I hope this post saves someone
> time someday! This is on SQLServer 2000 w/SP3a.
> Cheers...

BobMgr::GetBuf: Bob write not complete after 60 seconds.

What could cause the error listed below. Is there away to resolve this error
?
Thank You,
Error
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.http://support.microsoft.com/defaul...kb;en-us;309392
"Joe K." wrote:

> What could cause the error listed below. Is there away to resolve this err
or?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.|||Have a look here:
http://support.microsoft.com/defaul...b;en-us;Q309392
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:52D9083B-BD91-4CBA-883F-CA818754A38E@.microsoft.com...
> What could cause the error listed below. Is there away to resolve this
> error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.

BobMgr::GetBuf: Bob write not complete after 60 seconds.

What could cause the error listed below. Is there away to resolve this error?
Thank You,
Error
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
http://support.microsoft.com/default...b;en-us;309392
"Joe K." wrote:

> What could cause the error listed below. Is there away to resolve this error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
|||Have a look here:
http://support.microsoft.com/default...;en-us;Q309392
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:52D9083B-BD91-4CBA-883F-CA818754A38E@.microsoft.com...
> What could cause the error listed below. Is there away to resolve this
> error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.

BobMgr::GetBuf: Bob write not complete after 60 seconds.

What could cause the error listed below. Is there away to resolve this error?
Thank You,
Error
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.http://support.microsoft.com/default.aspx?scid=kb;en-us;309392
"Joe K." wrote:
> What could cause the error listed below. Is there away to resolve this error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.|||Have a look here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q309392
--
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:52D9083B-BD91-4CBA-883F-CA818754A38E@.microsoft.com...
> What could cause the error listed below. Is there away to resolve this
> error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.

Blocking Threshold Exceeded

I have a site that calls a stored procedure to populate data on an ASP page.
50% of the time when I access this page I get a timeout error. I looked in
the Event Viewer and noticed MSSQLSERVER errors with this description:
Error: 50001, Severity: 16, State: 1
Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
The total blockers number changes occasionaly but I don't know what this
means and how to fix it. I am not doing anything complex with the stored
procedure. It is a select statement which joins multiple tables. No
inserts, updates, or deletes are being done.
Does anyone have an idea how to correct this?
Thanks.Even though the procedure that performs the query is not
inserting/updating/deleting, is this still being done by any process? When
it actually completes, what is the average and maximum runtime of the query?
Also, the following article describes various methods for identifying what
processes are blocked, what process is doing the blocking, and what specific
T-SQL statement is blocking.
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
If this is a reporting type query against data potentially with uncommitted
transactions, then you may want to consider using "set transaction isolation
level read uncommitted", so long as you understand how this can impact the
results. Read up on this in Books Online.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:A1346683-78D7-40CE-9F7F-3AE8521D392F@.microsoft.com...
>I have a site that calls a stored procedure to populate data on an ASP
>page.
> 50% of the time when I access this page I get a timeout error. I looked
> in
> the Event Viewer and noticed MSSQLSERVER errors with this description:
> Error: 50001, Severity: 16, State: 1
> Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
> The total blockers number changes occasionaly but I don't know what this
> means and how to fix it. I am not doing anything complex with the stored
> procedure. It is a select statement which joins multiple tables. No
> inserts, updates, or deletes are being done.
> Does anyone have an idea how to correct this?
> Thanks.
>

Blocking and Transactions OK?

I'm wondering if I'm doing this right. Wondering about the transactions
and error handling. (Do I even need to put BEGIN TRANSACTION AND
COMMIT TRANSACTION in there?)

I think that this sproc is causing occasional blocking:

Alter Procedure sprocINSERTSTUFF
@.Col1Data int = Null,
@.Col2Data nvarchar(255) = Null,
@.Col3Data ntext = Null,
@.UniqueID int OUTPUT

AS

set nocount on
set xact_abort on

DECLARE @.err int
DECLARE @.ServerDate DateTime
SELECT @.ServerDate = GETUTCDATE()

BEGIN TRANSACTION

INSERT INTO
tblStuff (Col1, Col2, Col3, DateCreated, etc.)
VALUES
(@.Col1Data, @.Col2Data, @.Col3Data, @.ServerDate, etc.)

SELECT @.err = @.@.error IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
@.err END

SELECT @.UniqueID = SCOPE_IDENTITY()

COMMIT TRANSACTION

BEGIN TRANSACTION

INSERT INTO
tblMoreStuff (UniqueID, DateCreated, Col1, Col2, Col3)
Values
(@.UniqueID, @.ServerDate, @.Col1Data, @.Col2Data, 'Text Label: ' +
isnull(Cast(@.Col3Data AS nvarchar(4000)),'<none>')

SELECT @.err = @.@.error IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
@.err END

COMMIT TRANSACTION
SELECT @.err = @.@.error IF @.err <> 0 RETURN @.errlaurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I'm wondering if I'm doing this right. Wondering about the transactions
> and error handling. (Do I even need to put BEGIN TRANSACTION AND
> COMMIT TRANSACTION in there?)

Depends on your business requirements. If it's OK that a row gets
inserter into tblStuff, but not any row in tblMoreStuff, you can take
it out entirely. If you want both rows or none of the rows inserted,
you should take out the COMMIT and the BEGIN in the middle, to make
it one single transaction.

> I think that this sproc is causing occasional blocking:

Since it's only two plain insert statements, that's a bit surprising.
But if there is a long-running trigger on one of the tables you could
get blocking.

> @.Col3Data ntext = Null,
>...
> 'Text Label: ' +
> isnull(Cast(@.Col3Data AS nvarchar(4000)),'<none>')
>...

What's the point with accepting an ntext parameter, if you truncate it
anyway?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Thanks for your reply.

> @.Col3Data ntext = Null,
>...
> 'Text Label: ' +
> isnull(Cast(@.Col3Data AS nvarchar(4000)),'<none>')
>...

>What's the point with accepting an ntext parameter, if you truncate it anyway?

The second table is used to hold a history of changes to the first
table tblStuff and in this particular case it's not necessary (or
desirable) to have the unlimited text in any ntext column stored, so
it's truncated.|||I forgot to mention in my front end application (MS Access 2K) I use
the following:

On Error GoTo myErr:
' Execute the sproc that runs the Insert above
myErr:
CurrentProject.Connection.Execute "IF @.@.trancount > 0 ROLLBACK
TRANSACTION", , adExecuteNoRecords

I am wondering if this should be sufficient to not leave an open
transaction in the event of an error.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I forgot to mention in my front end application (MS Access 2K) I use
> the following:
> On Error GoTo myErr:
> ' Execute the sproc that runs the Insert above
> myErr:
> CurrentProject.Connection.Execute "IF @.@.trancount > 0 ROLLBACK
> TRANSACTION", , adExecuteNoRecords
> I am wondering if this should be sufficient to not leave an open
> transaction in the event of an error.

In theory maybe. But good software practice is that every module
cleans up after itself, and does not rely on somebody else to do it.
Least of all one should trust an application that uses ADO.

Look at it this way: you have this handling in many stored procedures.
Maybe you happen to forget to insert it in some place. And you have
this handling in many places in your client code. Maybe you acceidently
leave it in some place. So keep a double safeguard.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>Look at it this way: you have this handling in many stored procedures.
>Maybe you happen to forget to insert it in some place. And you have
>this handling in many places in your client code. Maybe you acceidently
>leave it in some place. So keep a double safeguard.

I'm not sure what you'rer suggesting...

I thought that this would clear up any error in the sproc:

SELECT @.err = @.@.error IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
@.err END

COMMIT TRANSACTION
SELECT @.err = @.@.error IF @.err <> 0 RETURN @.err

Are you suggesting I put:

IF @.@.trancount > 0 ROLLBACK TRANSACTION

inthe sproc as well and not check for that in the front-end
application?|||>Look at it this way: you have this handling in many stored procedures.
>Maybe you happen to forget to insert it in some place. And you have
>this handling in many places in your client code. Maybe you acceidently
>leave it in some place. So keep a double safeguard.

I'm not sure what you'rer suggesting...

I thought that this would clear up any error in the sproc:

SELECT @.err = @.@.error IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
@.err END

COMMIT TRANSACTION
SELECT @.err = @.@.error IF @.err <> 0 RETURN @.err

Are you suggesting I put:

IF @.@.trancount > 0 ROLLBACK TRANSACTION

inthe sproc as well and not check for that in the front-end
application?|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
>>Look at it this way: you have this handling in many stored procedures.
>>Maybe you happen to forget to insert it in some place. And you have
>>this handling in many places in your client code. Maybe you acceidently
>>leave it in some place. So keep a double safeguard.
> I'm not sure what you'rer suggesting...
> I thought that this would clear up any error in the sproc:
> SELECT @.err = @.@.error IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
> @.err END
>
> COMMIT TRANSACTION
> SELECT @.err = @.@.error IF @.err <> 0 RETURN @.err
>
> Are you suggesting I put:
> IF @.@.trancount > 0 ROLLBACK TRANSACTION
> inthe sproc as well and not check for that in the front-end
> application?

So I'm suggesting that you should keep things as they are. (But make
sure that your transaction scope is the right one. The procedure
you posted seemed funny to me with a COMMIT in the middle.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland. Thanks for the help and the good avice. The COMMIT in the
middle in this situation is because the first insert puts a new row of
data into a table, the second insert records the row/column info into a
row in a history table. In this situation, I want to be sure that the
new record is recorded in the primary data table even if the history is
not. If there is a failure on the second insert, I don't want to
rollback the first insert, even though the records might not end up in
sync (original and history) this would be corrected hopefully the next
time the record is updated.
I have considered and rejected using triggers for this purpose even
though that has been suggested.
Thanks again for your help in m understanding of how these things work!
lq|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Erland. Thanks for the help and the good avice. The COMMIT in the
> middle in this situation is because the first insert puts a new row of
> data into a table, the second insert records the row/column info into a
> row in a history table. In this situation, I want to be sure that the
> new record is recorded in the primary data table even if the history is
> not. If there is a failure on the second insert, I don't want to
> rollback the first insert, even though the records might not end up in
> sync (original and history) this would be corrected hopefully the next
> time the record is updated.

Good. I just wanted to make sure that it's on purpose.

(I remember a system I worked with looooong ago. There was a stored
proecdure that filled up a table, and it was one long transaction.
Unfortunately, it tended to fill up the transaction log. (This was
Sybase 4.x, no autogrow.) My colleagues found the fix - they inserted
some COMMIT/BEGIN. Oh well.)

> I have considered and rejected using triggers for this purpose even
> though that has been suggested.

Well, that would buy you a transaction for the current and history 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

Blocking Alert

I'm trying to set up an alert for when a block occurs on a database. I
thought I could use error 1229, but that doesn't appear to be the correct
one, as it never fires, even tho we do get an occassional block.
Is there another error number I should be using? Is it possible to do what
I'm trying to do? Ideally, I would like an email to be sent when a block
occurs, so I can analyze the situation and make necessary changes.
Thanks for any advice,
TomT
Tom,
Are you refering to a database lock or a lock on an object in the database
that might be causing blocking? Might want to check out the SQL Server
Agent - Performance Condition Alerts - SQL Server:Locks.
HTH
Jerry
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
> I'm trying to set up an alert for when a block occurs on a database. I
> thought I could use error 1229, but that doesn't appear to be the correct
> one, as it never fires, even tho we do get an occassional block.
> Is there another error number I should be using? Is it possible to do what
> I'm trying to do? Ideally, I would like an email to be sent when a block
> occurs, so I can analyze the situation and make necessary changes.
> Thanks for any advice,
> TomT
|||Jerry,
Thanks for your response. I'm talking about a block that shows up in EM,
under Current Activity\Process Info. There's a column for Blocking and
Blocked By.
"Jerry Spivey" wrote:

> Tom,
> Are you refering to a database lock or a lock on an object in the database
> that might be causing blocking? Might want to check out the SQL Server
> Agent - Performance Condition Alerts - SQL Server:Locks.
> HTH
> Jerry
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
>
>
|||Check the following Knowledge Base article for a sample
script on setting this up:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/?id=271509
-Sue
On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
<tomt@.newsgroup.nospam> wrote:

>I'm trying to set up an alert for when a block occurs on a database. I
>thought I could use error 1229, but that doesn't appear to be the correct
>one, as it never fires, even tho we do get an occassional block.
>Is there another error number I should be using? Is it possible to do what
>I'm trying to do? Ideally, I would like an email to be sent when a block
>occurs, so I can analyze the situation and make necessary changes.
>Thanks for any advice,
>TomT
|||Sue,
thanks very much for the link. Out of curiosity, is it possible to also set
an alert for this? Is there to your knowledge a specific error number for
this event?
thanks,
Tom
"Sue Hoegemeier" wrote:

> Check the following Knowledge Base article for a sample
> script on setting this up:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/?id=271509
> -Sue
> On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
> <tomt@.newsgroup.nospam> wrote:
>
>
|||TomT wrote:
> Sue,
> thanks very much for the link. Out of curiosity, is it possible to
> also set an alert for this? Is there to your knowledge a specific
> error number for this event?
Blocking is not really an error condition. It's a normal part of RDBMS
operation. You are always going to have blocking going on in some
fashion. In a healthly system, the blocks are so short they are never
noticed. On a system with problems these blocks are more pronounced. The
blocking scipts Sue mentioned will help you determine when extended
blocks are occurring in the database.
You can set a lock timeout in you application if you want. With a lock
timeout, the application will only wait for a specific maximum time when
blocked before cancelling the query. A cancelled query throws an
attention event, which can be captured with Profiler or using a
server-side trace.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi Tom,
Since many communities and MVP provide great answers, I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Blocking Alert

I'm trying to set up an alert for when a block occurs on a database. I
thought I could use error 1229, but that doesn't appear to be the correct
one, as it never fires, even tho we do get an occassional block.
Is there another error number I should be using? Is it possible to do what
I'm trying to do? Ideally, I would like an email to be sent when a block
occurs, so I can analyze the situation and make necessary changes.
Thanks for any advice,
TomTTom,
Are you refering to a database lock or a lock on an object in the database
that might be causing blocking? Might want to check out the SQL Server
Agent - Performance Condition Alerts - SQL Server:Locks.
HTH
Jerry
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
> I'm trying to set up an alert for when a block occurs on a database. I
> thought I could use error 1229, but that doesn't appear to be the correct
> one, as it never fires, even tho we do get an occassional block.
> Is there another error number I should be using? Is it possible to do what
> I'm trying to do? Ideally, I would like an email to be sent when a block
> occurs, so I can analyze the situation and make necessary changes.
> Thanks for any advice,
> TomT|||Jerry,
Thanks for your response. I'm talking about a block that shows up in EM,
under Current Activity\Process Info. There's a column for Blocking and
Blocked By.
"Jerry Spivey" wrote:

> Tom,
> Are you refering to a database lock or a lock on an object in the database
> that might be causing blocking? Might want to check out the SQL Server
> Agent - Performance Condition Alerts - SQL Server:Locks.
> HTH
> Jerry
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
>
>|||Check the following Knowledge Base article for a sample
script on setting this up:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/?id=271509
-Sue
On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
<tomt@.newsgroup.nospam> wrote:

>I'm trying to set up an alert for when a block occurs on a database. I
>thought I could use error 1229, but that doesn't appear to be the correct
>one, as it never fires, even tho we do get an occassional block.
>Is there another error number I should be using? Is it possible to do what
>I'm trying to do? Ideally, I would like an email to be sent when a block
>occurs, so I can analyze the situation and make necessary changes.
>Thanks for any advice,
>TomT|||Sue,
thanks very much for the link. Out of curiosity, is it possible to also set
an alert for this? Is there to your knowledge a specific error number for
this event?
thanks,
Tom
"Sue Hoegemeier" wrote:

> Check the following Knowledge Base article for a sample
> script on setting this up:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/?id=271509
> -Sue
> On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
> <tomt@.newsgroup.nospam> wrote:
>
>|||TomT wrote:
> Sue,
> thanks very much for the link. Out of curiosity, is it possible to
> also set an alert for this? Is there to your knowledge a specific
> error number for this event?
Blocking is not really an error condition. It's a normal part of RDBMS
operation. You are always going to have blocking going on in some
fashion. In a healthly system, the blocks are so short they are never
noticed. On a system with problems these blocks are more pronounced. The
blocking scipts Sue mentioned will help you determine when extended
blocks are occurring in the database.
You can set a lock timeout in you application if you want. With a lock
timeout, the application will only wait for a specific maximum time when
blocked before cancelling the query. A cancelled query throws an
attention event, which can be captured with Profiler or using a
server-side trace.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi Tom,
Since many communities and MVP provide great answers, I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Blocking Alert

I'm trying to set up an alert for when a block occurs on a database. I
thought I could use error 1229, but that doesn't appear to be the correct
one, as it never fires, even tho we do get an occassional block.
Is there another error number I should be using? Is it possible to do what
I'm trying to do? Ideally, I would like an email to be sent when a block
occurs, so I can analyze the situation and make necessary changes.
Thanks for any advice,
TomTTom,
Are you refering to a database lock or a lock on an object in the database
that might be causing blocking? Might want to check out the SQL Server
Agent - Performance Condition Alerts - SQL Server:Locks.
HTH
Jerry
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
> I'm trying to set up an alert for when a block occurs on a database. I
> thought I could use error 1229, but that doesn't appear to be the correct
> one, as it never fires, even tho we do get an occassional block.
> Is there another error number I should be using? Is it possible to do what
> I'm trying to do? Ideally, I would like an email to be sent when a block
> occurs, so I can analyze the situation and make necessary changes.
> Thanks for any advice,
> TomT|||Jerry,
Thanks for your response. I'm talking about a block that shows up in EM,
under Current Activity\Process Info. There's a column for Blocking and
Blocked By.
"Jerry Spivey" wrote:
> Tom,
> Are you refering to a database lock or a lock on an object in the database
> that might be causing blocking? Might want to check out the SQL Server
> Agent - Performance Condition Alerts - SQL Server:Locks.
> HTH
> Jerry
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
> > I'm trying to set up an alert for when a block occurs on a database. I
> > thought I could use error 1229, but that doesn't appear to be the correct
> > one, as it never fires, even tho we do get an occassional block.
> >
> > Is there another error number I should be using? Is it possible to do what
> > I'm trying to do? Ideally, I would like an email to be sent when a block
> > occurs, so I can analyze the situation and make necessary changes.
> >
> > Thanks for any advice,
> >
> > TomT
>
>|||Check the following Knowledge Base article for a sample
script on setting this up:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/?id=271509
-Sue
On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
<tomt@.newsgroup.nospam> wrote:
>I'm trying to set up an alert for when a block occurs on a database. I
>thought I could use error 1229, but that doesn't appear to be the correct
>one, as it never fires, even tho we do get an occassional block.
>Is there another error number I should be using? Is it possible to do what
>I'm trying to do? Ideally, I would like an email to be sent when a block
>occurs, so I can analyze the situation and make necessary changes.
>Thanks for any advice,
>TomT|||Sue,
thanks very much for the link. Out of curiosity, is it possible to also set
an alert for this? Is there to your knowledge a specific error number for
this event?
thanks,
Tom
"Sue Hoegemeier" wrote:
> Check the following Knowledge Base article for a sample
> script on setting this up:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/?id=271509
> -Sue
> On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
> <tomt@.newsgroup.nospam> wrote:
> >I'm trying to set up an alert for when a block occurs on a database. I
> >thought I could use error 1229, but that doesn't appear to be the correct
> >one, as it never fires, even tho we do get an occassional block.
> >
> >Is there another error number I should be using? Is it possible to do what
> >I'm trying to do? Ideally, I would like an email to be sent when a block
> >occurs, so I can analyze the situation and make necessary changes.
> >
> >Thanks for any advice,
> >
> >TomT
>|||TomT wrote:
> Sue,
> thanks very much for the link. Out of curiosity, is it possible to
> also set an alert for this? Is there to your knowledge a specific
> error number for this event?
Blocking is not really an error condition. It's a normal part of RDBMS
operation. You are always going to have blocking going on in some
fashion. In a healthly system, the blocks are so short they are never
noticed. On a system with problems these blocks are more pronounced. The
blocking scipts Sue mentioned will help you determine when extended
blocks are occurring in the database.
You can set a lock timeout in you application if you want. With a lock
timeout, the application will only wait for a specific maximum time when
blocked before cancelling the query. A cancelled query throws an
attention event, which can be captured with Profiler or using a
server-side trace.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi Tom,
Since many communities and MVP provide great answers, I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Blanks in columns

Quick Question I hope.

Does having a lot of blanks in a column cause errors ?

In 2 or 3 packages where I get 95% of the data in the Error Colomn I can find nothing wrong with the data at all ? it all either seems perfect or there is no data in the column in question usually I would have maybe 800 rows where data would have been inserted but in the other 40, 000 rows the column is blank

Using the "Retain null values from the source as null values in the destination" doesnt seem to make a differance.

This is the error description

The data value cannot be converted for reasons other than sign mismatch or data overflow.

Anyone know of a solution / reason why this keeps happening

Thanks

Without seeing the package, no. It would apear that there is an implicit conversion going on somewhere which is failing. You have the error files so it shouldn't be difficult to work out what is happening. Do you know which column is failing?

-Jamie

|||It would also help for us to know what type of source you are using. I would guess that your using a Flat File Source.

Please give us a little more info about the structure of the package and we'll be in a better position to offer suggestions.

Larry Pope|||

Will do, Thanks for the help

Package Details are as follows

Import data from a flat file source in csv format with semicolons as the delimiters

Change the date's using a derived column with an expression like
(DT_DBTIMESTAMP)[Column 21]

Then to a SQL Server Destination where that column wher e the table for that column would read [Column 2] DATETIME

I have also thrown in a ErrorDescription Package (Which is great Thanks Jamie :D) and also a package that tells me which column is failing (Which I got from Simon)

From these I can work out which column seems to be failing although when I switch that to Ignore errors another date column usually fails.

Is it REALLY bad practice to have all columns on "Ignore errors" ....when the data seems to be fine when I look over it after in the table I cant find anything wrong with it.

If there was an charactor such as '# 'or a random / ,\ surely that would just fail one row and not all the rows

|||Are you trying to cast nulls to a DT_DBTIMESTAMP. I think there

may be problems with this. Jamie might be able to confirm this.

You might want to change your derived column to

ISNULL([Column 21] ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)([Column

21]). And be aware that trying to cast invalid dates could also

raise errors (example '0000-00-00').

Have you put a data viewer between the source and the derived column to see how the Flat File Source is interpreting file?

Larry Pope|||

I've definately seen cases where an attempt to put a NULL into a DT_DBTIMESTAMP column resulted in a value of "0000-00-00 00:00:00" - which is just useless. This happened from inside a script task when setting the column to 'Nothing' but I guess the same symptoms could occur elsewhere as well!!

-Jamie

|||

Thanks guys ill try that,

Just realised aswell that 1 column is in the format MM/DD/YYYY while the other is DD/MM/YYYY

Should be fun trying to sort them out

|||

Can someone please elaborate on this occurrence. I believe we are experiencing a flavor of this.

We have a package that checks an amount field for values greater than 0.

When the value is greater than 0 then we populate with the processing date, else we place a Null Timestamp value.

We are having failed inserts because our target column is SmallDateTime and somehow the time value of "0000-00-00 00:00:00" is being created.

According to the logic, there should only be a valid date or a null date....no zeros. Does anyone have any information on this issue?

ChargeOffAmount > 0 ? (DT_DBTIMESTAMP)ReportingDate : NULL(DT_DBTIMESTAMP)

Thanks in advance...

Jamie Thomson wrote:

I've definately seen cases where an attempt to put a NULL into a DT_DBTIMESTAMP column resulted in a value of "0000-00-00 00:00:00" - which is just useless. This happened from inside a script task when setting the column to 'Nothing' but I guess the same symptoms could occur elsewhere as well!!

-Jamie

|||

Romeo,

Is there a default value specified in the column definition in the database?

|||

Phil, no, there is no default value specified for this column.

I also failed to mention that these values are not always consistently produced...meaning that we can try to recreate the occurrence and then the date will be generated correctly and load. Makes debugging not so easy and causes me to doubt my sanity.....well, that's never been in doubt, I get opinions on both sides....