out-of-range smalldatatime value

I am trying to insert a rown into a table with this line in my program :

INSERT INTO MYTABLE (Usuario, Datahora, COD_PROG, Tabela, TipoMov, Registro, Campo, ValAnt, ValAtu, Motivo, Sequencial) VALUES (23, '06/28/2007 19:53:45', '002101', 'Contribuinte', 'A', '1006626', ' ', 'Isentou Tx 2a Via 062007', ' ', ' ', ' ')

and I am receiving this message :

The conversion of char data type to smalldatatime data type resulted in an out-of-range smalldatatime value.

Thanks

Hello

I suppose that the smalldatetime has the value 06/28/2007 19:53:45

what is the language used for the server ?

the format of the date is corresponding to month/day/year ( american format ).Maybe it's the origin of the problem.

For the french format ( dd/mm/yyyy ), you have an error ( 28 does not correspond to a month )

Have a good day

|||

The smalldatetime datatype does NOT include seconds. But it truncates the seconds. -That isn't the problem.

I suspect that your server is expecting a date in the form of 'dd/mm/yyyy', and you are providing 'mm/dd/yyyy'.

If you changed the date format on the INSERT data to the ISO standard of 'yyyy/mm/dd', you wouldn't have any problem.

|||Thank you, that's ok now.

Outer Join that limits one side

I'm trying to retrieve a result set that uses an Outer Join, but I want to
limit the records in one of tables. The problem is when I insert the Where
condition, it doesn't show the other "Outer" records from the other table.
Here is the current SQL Syntax:
SELECT ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
FROM PROJECT_FAMILY PF INNER JOIN
PROJECT P ON PF.FamilyID = P.FamilyID LEFT OUTER JOIN
EMAIL_SIGNUP ES ON P.ProjectID = ES.ProjectID
WHERE ES.WebContactID = 1
When I remove the WHERE condition, it displays all the records from PROJECT,
which is what I want, but also all the EMAIL_SIGNUP records, which I don't
want. I want to limit the EMAIL_SIGNUP to one ID, which is the primary key.
The result should look like this:
1 (Project Name A) 1 (Family Name X)
NULL (Project NameB ) 2 (Family Name X)
1 (Project Name C) 3 (Family Name Y)
Instead it looks like this:
1 (Project Name A) 1 (Family Name X)
1 (Project Name C) 3 (Family Name Y)
I assuming I need to move the "ES.WebContactID = 1". Thanks.Try:
SELECT ES.webcontactid, P.projectname, P.projectid, PF.familyname
FROM project_family AS PF
INNER JOIN project AS P
ON PF.familyid = P.familyid
LEFT OUTER JOIN email_signup AS ES
ON P.projectid = ES.projectid
AND ES.webcontactid = 1
David Portas
SQL Server MVP
--|||As soon as you put a predicate in the where clause, which operates on the
"wrong" side of an outer join, you effectively destroy the "Outerness".
Think of it this way, The Join creates a temporarily constructed resultset
consisting of everything put together up to then, plus the records from the
new table being joined, using the join conditions... WEach Join repeats this
process, using only the conditions assiated with that specific join.
The where clause conditions, on the other hand, apply to the last
constructed resultset, after all joins have been done.
So before your where clause operates, all those record s were in there,
including the ones from PROJECT_FAMILY and PROJECT that had no counterparts
in EMAIL_SIGNUP - but for every one of those, the columns from EMAIL_SIGNUP
were NULL. then you say
Where ES.WebContactID = 1, and that eliminates all of them, because
ES.WebContactID Is NULL for all of them...
What you need to do is add this additional predicate condition in the Join
conditions.
Like THis:
Select ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
From PROJECT_FAMILY PF
Join PROJECT P
On PF.FamilyID = P.FamilyID
Left Join EMAIL_SIGNUP ES
On P.ProjectID = ES.ProjectID
And ES.WebContactID = 1
"jmhmaine" wrote:

> I'm trying to retrieve a result set that uses an Outer Join, but I want to
> limit the records in one of tables. The problem is when I insert the Where
> condition, it doesn't show the other "Outer" records from the other table.
> Here is the current SQL Syntax:
> SELECT ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
> FROM PROJECT_FAMILY PF INNER JOIN
> PROJECT P ON PF.FamilyID = P.FamilyID LEFT OUTER JOIN
> EMAIL_SIGNUP ES ON P.ProjectID = ES.ProjectID
> WHERE ES.WebContactID = 1
> When I remove the WHERE condition, it displays all the records from PROJEC
T,
> which is what I want, but also all the EMAIL_SIGNUP records, which I don't
> want. I want to limit the EMAIL_SIGNUP to one ID, which is the primary ke
y.
> The result should look like this:
> 1 (Project Name A) 1 (Family Name X)
> NULL (Project NameB ) 2 (Family Name X)
> 1 (Project Name C) 3 (Family Name Y)
> Instead it looks like this:
> 1 (Project Name A) 1 (Family Name X)
> 1 (Project Name C) 3 (Family Name Y)
> I assuming I need to move the "ES.WebContactID = 1". Thanks.|||jmhmaine wrote on Tue, 15 Mar 2005 07:35:02 -0800:

> I'm trying to retrieve a result set that uses an Outer Join, but I want to
> limit the records in one of tables. The problem is when I insert the Where
> condition, it doesn't show the other "Outer" records from the other table.
> Here is the current SQL Syntax:
> SELECT ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
> FROM PROJECT_FAMILY PF INNER JOIN
> PROJECT P ON PF.FamilyID = P.FamilyID LEFT OUTER JOIN
> EMAIL_SIGNUP ES ON P.ProjectID = ES.ProjectID
> WHERE ES.WebContactID = 1
You are limiting the results to only those that have a WebContactID value of
1. You need to add an OR for the NULL values where there is no matching
record.
SELECT ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
FROM PROJECT_FAMILY PF INNER JOIN
PROJECT P ON PF.FamilyID = P.FamilyID LEFT OUTER JOIN
EMAIL_SIGNUP ES ON P.ProjectID = ES.ProjectID
WHERE ES.WebContactID = 1 OR ES.WebContactID IS NULL
Dan|||No that just lists the all the records in Project, and webcontactid is NULL.
Josh.
"David Portas" wrote:

> Try:
> SELECT ES.webcontactid, P.projectname, P.projectid, PF.familyname
> FROM project_family AS PF
> INNER JOIN project AS P
> ON PF.familyid = P.familyid
> LEFT OUTER JOIN email_signup AS ES
> ON P.projectid = ES.projectid
> AND ES.webcontactid = 1
> --
> David Portas
> SQL Server MVP
> --
>|||Ignore this message. That worked thanks.
Josh.
"jmhmaine" wrote:
> No that just lists the all the records in Project, and webcontactid is NUL
L.
> Josh.
> "David Portas" wrote:
>|||jmhmaine,
For those records in Project which have no matching record in email_signup,
the value of webcontactid MUST BE NULL. There's no way around that... Thos
e
records don't HAVE a webContactID, cause that data item is in the
email_signup Table...
"jmhmaine" wrote:
> No that just lists the all the records in Project, and webcontactid is NUL
L.
> Josh.
> "David Portas" wrote:
>

Out of range datetime value error when inserting using select...union

Hi all,
I am getting this error when insert values from one table to another in
the first table the values are varchar (10). In the second they are
datetime. The format of the data is mm/dd/yyyy to be easily converted
to dates. The conversion in this case is implicit as indicated in SQL
Server documentation. Here is my query:

INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
mysqlfactiva.dbo.campaigns.campaign_name AS Name,
MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,

MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs
WHERE mysqlfactiva.dbo.campaigns.campaign_id =
mysqlfactiva.dbo.programs.campaign_id
GROUP BY mysqlfactiva.dbo.campaigns.campaign_id,
mysqlfactiva.dbo.campaigns.campaign_name,
mysqlfactiva.dbo.campaigns.description
UNION
SELECT program_id + 100000, program_name, start_date, end_date,
campaign_id AS ParentID, NULL AS ListID
FROM mysqlfactiva.dbo.programs
UNION
SELECT execution_id + 200000, execution_name, start_date,
end_date, program_id + 100000 AS ParentID, NULL AS ListID
FROM mysqlfactiva.dbo.executions
UNION
SELECT wave_id + 300000, wave_name, start_date, end_date,
mysqlfactiva.dbo.waves.execution_id + 200000 AS ParentID, NULL AS
ListID
FROM mysqlfactiva.dbo.waves, mysqlfactiva.dbo.executions
WHERE mysqlfactiva.dbo.waves.execution_id =
mysqlfactiva.dbo.executions.execution_id

I am referencing programs table two times. If I just select this all I
get all data I need. When doing insert I get a message:

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. The statement has been terminated.

If I execute just first part of the query before first union, I insert
data fine:

INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
mysqlfactiva.dbo.campaigns.campaign_name AS Name,
MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,

MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs
WHERE mysqlfactiva.dbo.campaigns.campaign_id =
mysqlfactiva.dbo.programs.campaign_id
GROUP BY mysqlfactiva.dbo.campaigns.campaign_id,
mysqlfactiva.dbo.campaigns.campaign_name,
mysqlfactiva.dbo.campaigns.description

As soon as I use union I get the above error. This is very strange
since even when I execute the query using first union where the dates
come from the same table 'programs' I get the error. Why I can insert
from programs first time and can's second time?

Any help will be appreciated.

Thanks,
Stan(suslikovich@.yahoo.com) writes:
> I am getting this error when insert values from one table to another in
> the first table the values are varchar (10). In the second they are
> datetime. The format of the data is mm/dd/yyyy to be easily converted
> to dates. The conversion in this case is implicit as indicated in SQL
> Server documentation. Here is my query:

Note that the interpretation is dependent on the settings for DATEFORMAT
and LANGUAGE.

> INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
> ListID)
> SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
> mysqlfactiva.dbo.campaigns.campaign_name AS Name,
> MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,
> MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
> NULL AS ParentID,
> NULL AS ListID
> FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs

So the data type of mysqlfactiva.dbo.programs.start_date is varchar(10)?

Excuse me, if I am nosy, but a MIN on that columns appears somewhat
funny to me. If there is a date in early January you will use that
date, no matter which year it is in?

Better would be

MIN (CASE WHEN isdate(start_date) = 1 THEN start_date END

> As soon as I use union I get the above error. This is very strange
> since even when I execute the query using first union where the dates
> come from the same table 'programs' I get the error. Why I can insert
> from programs first time and can's second time?

In the first query, you are only taking the first value, in the
second query you are taking all the values. This query should give
you the bad data:

SELECT * FROM mysqlfactiva.dbo.programs.start_date
WHERE isdate(start_date) = 0 OR isdate(end_date) = 0

By the way, I seem recall that MySQL has a very liberal view on what
is a good date...

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

osql with Unicode ?

Hi
How can I insert data in sql file(unicode - Thai chars) into sql server
using osql ?
regards
Dishan
Whe I run these files(inserts) using osql, enterprise manager shows
"?" as data
please help..
|||Sorry its my mistake.. I didnt put N( N'unidata' ) in my sql script

OSQL Unicode Insert of ^F (Hex 06) from Registry Multi-String Value is Duplicating in DB

I'm using a Unicode sql script imported using OSQL. One of the values
we are attempting to insert is a Registry Multi-String value by passing
a string to a stored procedure. These Multi-String values appear to be
delimited by a Hex 06 (^F) character. When I import this character,
embedded in a string preceeded by an N, i.e

N'something something2 something3'

I end up with TWO of this character in the db. I get :

something something2 something3

Any help figuring out why or how to fix this? We MUST use Unicode due
to extended character sets, so NOT using Unicode is NOT a solution.Seems to be an encoding issue. We are using SQLXML to retrieve the
data initially. That seems to be UTF-8 encoding the data which creates
an xml stream that contains an odd escape sequence. This escape
sequence is not then being translated into the correct unicode
representation in the insert scripts (which are created by transforming
the xml). We are going to try Unicode encoding on the SQLXmlCommand
object and setting the xml to encoding=UTF-16 to see what happens.

osql timeout

Hi,
I have several big tables with rows more than 25 mil rows
and to update/delete/insert data in these tables,
it can take minutes.
I use BULK Insert/DELETE/Update with osql.
While I run one of these updates,
if I try to select, it seems like both read and write get locked.
Shouldn't SQL resolve this kind of locking?

I left these to see if it gets resolved but both never returned.
So I need to kill these processes.
Does anyone have any scripts to find how long queries are running?

Also I need to make osql timeout and tried -t but it didn't work.
I used -t 1200 with DELETE in osql but it was running for more than 40
minutes. So I killed it and ran DBCC DBREINDEX on the table and re-ran
it and it worked.
Shouldn't the query get killed after 10 minutes?
What is exactly -t option for ?

thanks,(second714@.hotmail.com) writes:
> I have several big tables with rows more than 25 mil rows
> and to update/delete/insert data in these tables,
> it can take minutes.
> I use BULK Insert/DELETE/Update with osql.
> While I run one of these updates,
> if I try to select, it seems like both read and write get locked.
> Shouldn't SQL resolve this kind of locking?

If they are deadlocked, that is waiting for each other, SQL Server should
indeed detect this situation, and select one of them as a deadlock
victim.

But it may be the case that the first process is blocked by something
else, and when you try to select, the SELECT statement is blocked by
the update.

I have a tool on my web site, aba_lockinfo, which is good for looking
at locking chains. You can get it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

> Does anyone have any scripts to find how long queries are running?

You cannot really get the time how long a certain query has been
running, but you can see when a process last submitted a batch. The
latter is included in the information returned by aba_lockinfo.

> Also I need to make osql timeout and tried -t but it didn't work.
> I used -t 1200 with DELETE in osql but it was running for more than 40
> minutes. So I killed it and ran DBCC DBREINDEX on the table and re-ran
> it and it worked.
> Shouldn't the query get killed after 10 minutes?
> What is exactly -t option for ?

I didn't have the patience to try -t 1200, but I did try this:

E:\temp>osql -E -t 10
1> WAITFOR DELAY '00:00:20'
2> go
Timeout expired

So it does seem to work. I can't say why your DELETE did not timeout, but
it might be that it has to rollback the deletion that far, and rolling
back takes longer time than executing the command.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the info!
I'll try aba_lockinfo and test timeout more with select.

thanks again,

OSQL + sql files with accent characters = problems!

Hi,
I've an SQL file which contain accent characters to fill my default values
like this:
insert into mtytable... values('Février')
and also I create storedprocedures which execute some insert / update
command with the same values.
The SQL file is correct.
I've a batch file (.bat) which execute this command :
osql -E -i "MyScript.sql" -d MyDatabase
but the result is:
the accents disappeared !
Février become FÚvrier in the database!
My stored procedure has changed during the OSQL execution.
How can I solve the problem?
The storeprocedure (and other commands) are correct in the .sql file.
thanks.
Jerome."Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> I've an SQL file which contain accent characters to fill my default values
> like this:
> insert into mtytable... values('Février')
> and also I create storedprocedures which execute some insert / update
> command with the same values.
> The SQL file is correct.
> I've a batch file (.bat) which execute this command :
> osql -E -i "MyScript.sql" -d MyDatabase
> but the result is:
> the accents disappeared !
> Février become FÚvrier in the database!
> My stored procedure has changed during the OSQL execution.
> How can I solve the problem?
> The storeprocedure (and other commands) are correct in the .sql file.
> thanks.
> Jerome.
>
osql can use Unicode input files, however, when you create the Unicode input
files,
you need to do so using UTF-16 rather than the default UTF-8.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||ok, I'll try this
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uYdrCK0yEHA.2676@.TK2MSFTNGP12.phx.gbl...
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> I've an SQL file which contain accent characters to fill my default
>> values like this:
>> insert into mtytable... values('Février')
>> and also I create storedprocedures which execute some insert / update
>> command with the same values.
>> The SQL file is correct.
>> I've a batch file (.bat) which execute this command :
>> osql -E -i "MyScript.sql" -d MyDatabase
>> but the result is:
>> the accents disappeared !
>> Février become FÚvrier in the database!
>> My stored procedure has changed during the OSQL execution.
>> How can I solve the problem?
>> The storeprocedure (and other commands) are correct in the .sql file.
>> thanks.
>> Jerome.
>>
> osql can use Unicode input files, however, when you create the Unicode
> input files,
> you need to do so using UTF-16 rather than the default UTF-8.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>