Conversion ERROR

This is the error message I get: :(
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This is the query:
Select Qual_ins.CompanyCode, Qual_ins.ParticipantCode, Qual_ins.Ins_Code, Qual_ins.Plan_Code,
dbo.PremiumRate(Qual_Ins.Crit,Qual_Ins.PQB_Spec,Pl an_Mas.Extend_Fee,
Qual_Ins.Adjpremium,Qual_Ins.Adjpremiumper,Qual_In s.Adjpremend,
GetDate(),Qual_Ins.Cover_Amt, Plan_Mas.CR_A, Plan_Mas.CR_B,
Plan_Mas.CR_C, Plan_Mas.CR_D, Plan_Mas.CR_E, Plan_Mas.CR_F,
Plan_Mas.CR_G, Plan_Mas.CR_H, Plan_Mas.CR_I, Plan_Mas.CR_J,
Plan_Mas.CR_K, Plan_Mas.CR_L, Plan_Mas.CR_M, Plan_Mas.CR_N,
Plan_Mas.CR_O) AS PremiumRate
FROM Qual_ins, Plan_Mas
WHERE Qual_Ins.CompanyCode = 'ACME'
AND Qual_ins.ParticipantCode = 4
AND Plan_Mas.CompanyCode = Qual_ins.CompanyCode
AND Plan_Mas.Ins_Code = Qual_ins.Ins_Code
AND Plan_Mas.Plan_Code = Qual_ins.Plan_Code
Order BY Qual_ins.Ins_Code

Please let me know if you need to see my PremiumRate (User Defined Function) in order to help me elimate this error message.
Any help is appreciate!
I'm new to this... "Hello" to all!

ShuviBased on the error message, I'd guess that you are trying to convert a string (CHAR or VARCHAR) to a DATETIME or a SMALLDATETIME. If that is the case, then one or more rows in your data isn't a valid date string.

-PatP

Conversation lifetime after close

Given that the conversation states are as follows: (Thanks Rushi!)

Event Initiator Endpoint state Target Endpoint state

---

BEGIN DIALOG SO --
from Initiator
to Target

SEND message(s) CO --
from Initiator
to Target

Target receives a fragment CO SI
of the first message sent
or receives out of order
message

Target received entire CO CO
first message

END conversation at CO DO
target

Initiator receives EndDialog DI DO
message from target

Target receives ACK for the DI CD
EndDialog message sent

END conversation at CD CD
Initiator

When does the 30 minute timer start for clearing the conversation from the sys.conversation_handles table? Is it the same for both sides (initiator and Target) ie, the end conversation at the Initiator. I guess it must be just in case a resend is necessary.

Gary

Looks like the fire-and-forget problem. Please look at this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=758562&SiteID=1

|||

Thanks for the article Rushi,and special thanks for Roger's help. I found out that I did not have a route defined back to the initiator. Things are much better today.

Thanks a heap for the help!

Gary

Controlling the size of the Tempdb

Description:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log
space.
Rather than have to check the size of the tempdb.mdf every
day to make sure it's not consuming too much drive space,
we would like to find out if anyone has found an automated
way of backing up the tempdb to reduce the log size, or
maybe even schedule a restart of the SQL services in the
middle of the night to reset the log to its default size?
Any help would be greatly appreciated.
If it's growing too large, it's because your application requires it.
Either make more room on the disk for tempdb, move tempdb to a different
drive, or fix the application so it doesn't require so much space.
http://www.aspfaq.com/2446
Of course you can schedule a job to restart SQL Server, etc. But this is a
really bad hack at best.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Animatrix1" <anonymous@.discussions.microsoft.com> wrote in message
news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
> Description:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log
> space.
> Rather than have to check the size of the tempdb.mdf every
> day to make sure it's not consuming too much drive space,
> we would like to find out if anyone has found an automated
> way of backing up the tempdb to reduce the log size, or
> maybe even schedule a restart of the SQL services in the
> middle of the night to reset the log to its default size?
> Any help would be greatly appreciated.
|||We've also had tempdb spiral out of control. We are
using PeopleSoft as the front end application, and I can
tell you with great assurance, that there is no possible
way the application requires a 20 gig tempdb, to support
a 5 gig database.
We find the tempdb slowly grows over time. Sometimes
quickly, normally slowly. The only solution we came up
with, was the limit the size of tempdb, to something
large, but not all consuming.
We have about 15 installations of PeopleSoft, of varying
version levels with SQLServer. I've only seen two of
them suffer from this problem. So it's certainly not the
norm.
Fred...

>--Original Message--
>If it's growing too large, it's because your application
requires it.
>Either make more room on the disk for tempdb, move
tempdb to a different
>drive, or fix the application so it doesn't require so
much space.
>http://www.aspfaq.com/2446
>Of course you can schedule a job to restart SQL Server,
etc. But this is a
>really bad hack at best.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>"Animatrix1" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
every[vbcol=seagreen]
space,[vbcol=seagreen]
automated[vbcol=seagreen]
the[vbcol=seagreen]
size?
>
>.
>

Controlling the size of the Tempdb

Description:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log
space.
Rather than have to check the size of the tempdb.mdf every
day to make sure it's not consuming too much drive space,
we would like to find out if anyone has found an automated
way of backing up the tempdb to reduce the log size, or
maybe even schedule a restart of the SQL services in the
middle of the night to reset the log to its default size?
Any help would be greatly appreciated.If it's growing too large, it's because your application requires it.
Either make more room on the disk for tempdb, move tempdb to a different
drive, or fix the application so it doesn't require so much space.
http://www.aspfaq.com/2446
Of course you can schedule a job to restart SQL Server, etc. But this is a
really bad hack at best.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Animatrix1" <anonymous@.discussions.microsoft.com> wrote in message
news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
> Description:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log
> space.
> Rather than have to check the size of the tempdb.mdf every
> day to make sure it's not consuming too much drive space,
> we would like to find out if anyone has found an automated
> way of backing up the tempdb to reduce the log size, or
> maybe even schedule a restart of the SQL services in the
> middle of the night to reset the log to its default size?
> Any help would be greatly appreciated.|||We've also had tempdb spiral out of control. We are
using PeopleSoft as the front end application, and I can
tell you with great assurance, that there is no possible
way the application requires a 20 gig tempdb, to support
a 5 gig database.
We find the tempdb slowly grows over time. Sometimes
quickly, normally slowly. The only solution we came up
with, was the limit the size of tempdb, to something
large, but not all consuming.
We have about 15 installations of PeopleSoft, of varying
version levels with SQLServer. I've only seen two of
them suffer from this problem. So it's certainly not the
norm.
Fred...
>--Original Message--
>If it's growing too large, it's because your application
requires it.
>Either make more room on the disk for tempdb, move
tempdb to a different
>drive, or fix the application so it doesn't require so
much space.
>http://www.aspfaq.com/2446
>Of course you can schedule a job to restart SQL Server,
etc. But this is a
>really bad hack at best.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>"Animatrix1" <anonymous@.discussions.microsoft.com> wrote
in message
>news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
>> Description:
>> Error: 9002, Severity: 17, State: 6
>> The log file for database 'tempdb' is full. Back up the
>> transaction log for the database to free up some log
>> space.
>> Rather than have to check the size of the tempdb.mdf
every
>> day to make sure it's not consuming too much drive
space,
>> we would like to find out if anyone has found an
automated
>> way of backing up the tempdb to reduce the log size, or
>> maybe even schedule a restart of the SQL services in
the
>> middle of the night to reset the log to its default
size?
>> Any help would be greatly appreciated.
>
>.
>

Controlling the size of the Tempdb

Description:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log
space.
Rather than have to check the size of the tempdb.mdf every
day to make sure it's not consuming too much drive space,
we would like to find out if anyone has found an automated
way of backing up the tempdb to reduce the log size, or
maybe even schedule a restart of the SQL services in the
middle of the night to reset the log to its default size?
Any help would be greatly appreciated.If it's growing too large, it's because your application requires it.
Either make more room on the disk for tempdb, move tempdb to a different
drive, or fix the application so it doesn't require so much space.
http://www.aspfaq.com/2446
Of course you can schedule a job to restart SQL Server, etc. But this is a
really bad hack at best.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Animatrix1" <anonymous@.discussions.microsoft.com> wrote in message
news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
> Description:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log
> space.
> Rather than have to check the size of the tempdb.mdf every
> day to make sure it's not consuming too much drive space,
> we would like to find out if anyone has found an automated
> way of backing up the tempdb to reduce the log size, or
> maybe even schedule a restart of the SQL services in the
> middle of the night to reset the log to its default size?
> Any help would be greatly appreciated.|||We've also had tempdb spiral out of control. We are
using PeopleSoft as the front end application, and I can
tell you with great assurance, that there is no possible
way the application requires a 20 gig tempdb, to support
a 5 gig database.
We find the tempdb slowly grows over time. Sometimes
quickly, normally slowly. The only solution we came up
with, was the limit the size of tempdb, to something
large, but not all consuming.
We have about 15 installations of PeopleSoft, of varying
version levels with SQLServer. I've only seen two of
them suffer from this problem. So it's certainly not the
norm.
Fred...

>--Original Message--
>If it's growing too large, it's because your application
requires it.
>Either make more room on the disk for tempdb, move
tempdb to a different
>drive, or fix the application so it doesn't require so
much space.
>http://www.aspfaq.com/2446
>Of course you can schedule a job to restart SQL Server,
etc. But this is a
>really bad hack at best.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>"Animatrix1" <anonymous@.discussions.microsoft.com> wrote
in message
>news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
every[vbcol=seagreen]
space,[vbcol=seagreen]
automated[vbcol=seagreen]
the[vbcol=seagreen]
size?[vbcol=seagreen]
>
>.
>

Contitional Split, LTRIM

I am trying to follow an SSIS tutorial. It is doing a transformation on mainframe data.

It has the statement LTRIM(State) == '' in the Condititonal split editor.

The data type is DT_STR.

Nothing works (dbl quote,brackets, ect)

I have also tried to change the type to DT_WSTR. According to docs ltrim only works with Unicode.

Can someone please tell me how to detect an empty string.

Thanks for any help

walter

Walter_Of_Maggie wrote:

I am trying to follow an SSIS tutorial. It is doing a transformation on mainframe data.

It has the statement LTRIM(State) == '' in the Condititonal split editor.

The data type is DT_STR.

Nothing works (dbl quote,brackets, ect)

I have also tried to change the type to DT_WSTR. According to docs ltrim only works with Unicode.

Can someone please tell me how to detect an empty string.

Thanks for any help

walter

In the conditional split, it'd best be done this way:

TRIM(State) == "" || ISNULL(State)|||

Hello Phil,

That did the trick. The book with the tutorial is Professional SQL Server2005 Integration Services (Wrox)

Is the syntax in the book just flat wrong? LTRIM(State) = '' One article I read said that LTRIM only works with Unicode.

Anyway, thanks for the help

Regards,

Walter

|||They do work with unicode only, but non-unicode gets implicity converted first.

LTRIM(State) = "" should work fine as well, unless the data is really NULL, not blank.|||

LTRIM(State) == "" Works now.

It didn't work last night.

I don't know how that.

Maybe it just doesn't work on Tuesday

Anyway, My package is working now. I appreciate your help.

Thanks again

Walter

|||Please mark the appropriate response as the answer to your question.

Thanks,
Phil|||

Walter_Of_Maggie wrote:

LTRIM(State) == ""

This is different from what you said on your post at the top of this thread. In there you said (and I've copied and pasted):

LTRIM(State) == ''

Walter_Of_Maggie wrote:

Works now.

It didn't work last night.

I don't know how that.

Maybe it just doesn't work on Tuesday

Anyway, My package is working now. I appreciate your help.

Thanks again

Walter

-Jamie

|||

Looks like I made a mistake. I was sure that the book had single quotes (which didn't work) But when I looked with a magnifying glass, they were double quotes.

Nevertheless, I am sure that last night, before I posted, I tried every conceivable syntax, single quotes,double quotes, brackets, even a regex. I got errors on everything.

I'm sorry for the confusion, it works now, that's it.

Thanks for the help

Walter

CONTAINS not working

When I run the query,
SELECT nn
FROM tt
WHERE CONTAINS(nn, 'test')
I get the error
Server: Msg 7601, Level 16, State 2, Line 1
Cannot use a CONTAINS or FREETEXT predicate on table 'tt' because it is
not full-text indexed.
How can I set full-text index?
MadhivananHi
Just rey this way
EXEC sp_fulltext_column 'tt', 'nn', 'add'
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Madhivanan" wrote:

> When I run the query,
> SELECT nn
> FROM tt
> WHERE CONTAINS(nn, 'test')
> I get the error
> Server: Msg 7601, Level 16, State 2, Line 1
> Cannot use a CONTAINS or FREETEXT predicate on table 'tt' because it is
> not full-text indexed.
> How can I set full-text index?
> Madhivanan
>|||When I execute that I get the error
Server: Msg 15601, Level 16, State 1, Procedure sp_fulltext_column,
Line 13
Full-Text Search is not enabled for the current database. Use
sp_fulltext_database to enable Full-Text Search.
Madhivanan|||check this URL
http://msdn.microsoft.com/library/d...r />
_0kjd.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Madhivanan" wrote:

> When I execute that I get the error
> Server: Msg 15601, Level 16, State 1, Procedure sp_fulltext_column,
> Line 13
> Full-Text Search is not enabled for the current database. Use
> sp_fulltext_database to enable Full-Text Search.
>
> Madhivanan
>|||can I enable fulltext indexing with os win xp pro?
"Chandra" wrote:
> Hi
> Just rey this way
> EXEC sp_fulltext_column 'tt', 'nn', 'add'
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Madhivanan" wrote:
>