SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


stored procedure troubles


stored procedure troubles

Author
Message
El_Mou
El_Mou
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 105
Hello im back and hope i will be comprehensible ( i dont know it exist in english BigGrin )
so im gonna post all related script to my troubles ( table and column sources , PS
and table and column destination )

for my first trouble THIS IS THE RELATED SCRIPT

- Table(column)source :

USE [DB_SOURCE]
CREATE TABLE [AGE].[MATERIEL]
([Datach] [nvarchar](255) NULL)
ON [PRIMARY]

GO

- Stored procedure :
CREATE PROCEDURE [dbo].[PS_DIM_MATERIEL]

AS
BEGIN
Truncate table DWHPROD.dbo.DIM_MATERIEL
INSERT INTO DWPROD.dbo.DIM_MATERIEL
(DATE_ACHAT_MATERIEL)

SELECT
CONVERT (datetime,MATERIEL.Datach) AS DATE_ACHAT_MATERIEL,


FROM
DB_SOURCE.AGE.MATERIEL

END

- Table (column)destination:
CREATE TABLE [dbo].[DIM_MATERIEL]([DATE_ACHAT_MATERIEL] [datetime] NULL,)
ON [PRIMARY]

GO

I HAVE SOME ROWS WHO HAVE A null values in column Datach
so it can generate an error due a conversion from nvarchar null to datetime null ?
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5178 Visits: 5478
Have you had a chance to read all responses made to your thread so far?
What exactly problem you are facing now?
The reason why some dates can not be converted from character representation to datetime is use of foreign languages, eg. your example of date which failed to be converted is in French. In order to convert this date SQL should be switched to use French language.
If you have more than one language used, you will need to convert dates for each language separately, check my last post, shows exactly how it can be achieved.

And the last one: NULL string will be converted to NULL datetime without any problems.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
El_Mou
El_Mou
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 105
thanks eugene no i have just one language
first im gonna back up my DW because i made a lot of modifications especially to ignore conversion to datetime because its the only type who stop normal execution of my SP
and im gonna try to set language to french like you teach me now
then i return to you if it doesnt works
El_Mou
El_Mou
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 105
i typed back up my DW and i do it , but i meant restore my DW w00t
the coffee machine is out service this week and i cant concentrate on my works adding my low technical skills i think i can do a big disaster here if they give acces and authorizations to production environement !
so i set the language on the SP exact ?
like this :

SET LANGUAGE French
SELECT CONVERT (datetime,MATERIEL.Datach) AS DATE_ACHAT_MATERIEL,

when i do it they give me this message :
Msg 156, Level 15, State 1, Procedure PS_DIM_MATERIEL, Line 35
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Procedure PS_DIM_MATERIEL, Line 36
Incorrect syntax near the keyword 'CONVERT'.
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5178 Visits: 5478
Please post complete query, as it is impossible to see what is wrong just from two lines.
BTW, your second line finishes with comma. If nothing follows it, it will generate an error.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
El_Mou
El_Mou
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 105
Truncate table DWProd.dbo.DIM_MATERIEL
INSERT INTO DWProd.dbo.DIM_MATERIEL
(ID_MATERIEL,
LIB_MATERIEL,
CODE_MATERIEL,
TYPE_MATERIEL,
MARQUE_MATERIEL,
MODELE_MATERIEL,
PUISSANCE,
VALEUR_ACHAT,
FOURNISSEUR,
DATE_ACHAT_MATERIEL,
DATE_DEBUT,
IMMATRICULATION,
ID_EXP,
NOM_EXP,
PROPRE_DNE_LOCATION
)



SELECT MATERIEL.ID_MATERIEL,
MATERIEL.LIB_MATERIEL,
CODE_MATERIEL,
MATERIEL.LIB_TYPE_MATERIEL AS TYPE_MATERILE,
MATERIEL.LIB_MARQUE_MATERIEL AS MARQUE_MATERIEL,
MATERIEL.MODELE_MATERIEL,
CONVERT(real,ISNULL(MATERIEL.puiss,0)) AS PUISSANCE,
CONVERT(real, ISNULL(MATERIEL.Valeur,0)) AS VALEUR_ACHAT,
MATERIEL.Fourn AS FOURNISSEUR,
CONVERT(datetime,MATERIEL.Datach) AS DATE_ACHAT_MATERIEL,
MATERIEL.D_MATERIEL AS DATE_DEBUT,
MATERIEL.Immatr AS IMMATRICULATION,
MATERIEL.ID_EXP,
NOM_EXP,
[PDNEL]

FROM
SOURCE.AGE.MATERIEL MATERIEL INNER JOIN
SOURCE.AGE.EXP EXP
on EXP.ID_EXP=MATERIEL.ID_EXP

END

GO
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5178 Visits: 5478
El_Mou (5/9/2013)
Truncate table DWProd.dbo.DIM_MATERIEL
INSERT INTO DWProd.dbo.DIM_MATERIEL
(ID_MATERIEL,
LIB_MATERIEL,
CODE_MATERIEL,
TYPE_MATERIEL,
MARQUE_MATERIEL,
MODELE_MATERIEL,
PUISSANCE,
VALEUR_ACHAT,
FOURNISSEUR,
DATE_ACHAT_MATERIEL,
DATE_DEBUT,
IMMATRICULATION,
ID_EXP,
NOM_EXP,
PROPRE_DNE_LOCATION
)



SELECT MATERIEL.ID_MATERIEL,
MATERIEL.LIB_MATERIEL,
CODE_MATERIEL,
MATERIEL.LIB_TYPE_MATERIEL AS TYPE_MATERILE,
MATERIEL.LIB_MARQUE_MATERIEL AS MARQUE_MATERIEL,
MATERIEL.MODELE_MATERIEL,
CONVERT(real,ISNULL(MATERIEL.puiss,0)) AS PUISSANCE,
CONVERT(real, ISNULL(MATERIEL.Valeur,0)) AS VALEUR_ACHAT,
MATERIEL.Fourn AS FOURNISSEUR,
CONVERT(datetime,MATERIEL.Datach) AS DATE_ACHAT_MATERIEL,
MATERIEL.D_MATERIEL AS DATE_DEBUT,
MATERIEL.Immatr AS IMMATRICULATION,
MATERIEL.ID_EXP,
NOM_EXP,
[PDNEL]

FROM
SOURCE.AGE.MATERIEL MATERIEL INNER JOIN
SOURCE.AGE.EXP EXP
on EXP.ID_EXP=MATERIEL.ID_EXP

END

GO


The above query doesn't have SET LANGUAGE statement (or any other SET statement), so it can not possibly produce an error:

when i do it they give me this message :
Msg 156, Level 15, State 1, Procedure PS_DIM_MATERIEL, Line 35
Incorrect syntax near the keyword 'SET'.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
El_Mou
El_Mou
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 105
this is the new query with SET statement
CREATE PROCEDURE [dbo].[PS_DIM_MATERIEL]

AS
BEGIN
Truncate table DWProd.dbo.DIM_MATERIEL
INSERT INTO DWProd.dbo.DIM_MATERIEL
(ID_MATERIEL,
LIB_MATERIEL,
CODE_MATERIEL,
TYPE_MATERIEL,
MARQUE_MATERIEL,
MODELE_MATERIEL,
PUISSANCE,
VALEUR_ACHAT,
FOURNISSEUR,
DATE_ACHAT_MATERIEL,
DATE_DEBUT,
IMMATRICULATION,
ID_EXP,
NOM_EXP,
PROPRE_DNE_LOCATION
)



SELECT MATERIEL.ID_MATERIEL,
MATERIEL.LIB_MATERIEL,
CODE_MATERIEL,
MATERIEL.LIB_TYPE_MATERIEL AS TYPE_MATERILE,
MATERIEL.LIB_MARQUE_MATERIEL AS MARQUE_MATERIEL,
MATERIEL.MODELE_MATERIEL,
CONVERT(real,ISNULL(MATERIEL.puiss,0)) AS PUISSANCE,
CONVERT(real, ISNULL(MATERIEL.Valeur,0)) AS VALEUR_ACHAT,
MATERIEL.Fourn AS FOURNISSEUR,
SET LANGUAGE French
CONVERT(datetime,MATERIEL.Datach) AS DATE_ACHAT_MATERIEL,
MATERIEL.D_MATERIEL AS DATE_DEBUT,
MATERIEL.Immatr AS IMMATRICULATION,
MATERIEL.ID_EXP,
NOM_EXP,
[PDNEL]

FROM
SOURCE.AGE.MATERIEL MATERIEL INNER JOIN
SOURCE.AGE.EXP EXP
on EXP.ID_EXP=MATERIEL.ID_EXP

END

GO


and this is the message error :

Msg 156, Level 15, State 1, Procedure PS_DIM_MATERIEL, Line 36
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Procedure PS_DIM_MATERIEL, Line 37
Incorrect syntax near the keyword 'CONVERT'.
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5178 Visits: 5478
You cannot put SET in the middle of SELECT.
It is a separate statement and should be placed before INSERT!
Also, if you have some dates in English, then CONVERT will fail for this dates. As I've said before, dates in different languages should be processed separately. Please check my sample where it checks for date convertibility in the specific language.
So, if you have dates in two languages you will need to do two separate inserts with checking if date is convertable (using ISDATE function).

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
El_Mou
El_Mou
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 105
thanks you so much EUGENE for your precious help its done now for all the table who had similar message error

but still have another error with a dimension_time generate with this script Sad if i revolve it i can move to the next step )


CREATE PROCEDURE [dbo].[PS_DIM_TEMPS] /*(@Date_debut varchar(10), @Date_fin varchar(10))*/
AS
truncate table DWProd.dbo.DIM_TEMPS
declare @D_DEBUT datetime
declare @D_FIN datetime

declare @Date_Complete datetime
declare @Annee VARCHAR(4)
declare @Annee2 int
declare @campagne VARCHAR(5)
declare @trimestre VARCHAR(6)
declare @quinzaine varchar(1)
declare @mois VARCHAR(6)
declare @ordre_mois VARCHAR(6)
declare @mois_complet VARCHAR(12)
declare @semaine_annee VARCHAR(7)
declare @jour VARCHAR(2)
declare @jour_semaine VARCHAR(9)
declare @anneeS VARCHAR(4)

BEGIN

SET @D_DEBUT = cast('01-10-2009' as datetime)
SET @D_FIN = cast('30-09-2013' as datetime)

/*set @D_DEBUT = @Date_debut
set @D_FIN = @Date_fin*/

WHILE @D_DEBUT<@D_FIN BEGIN

set @Date_Complete= @D_DEBUT
set @Annee =datename(YY,@D_DEBUT)
set @trimestre =datepart(Q,@D_DEBUT)
set @mois = datepart(MM,@D_DEBUT)
set @ordre_mois = case @mois
when '10' then 1
when '11' then 2
when '12' then 3
when '1' then 4
when '2' then 5
when '3' then 6
when '4' then 7
when '5' then 8
when '6' then 9
when '7' then 10
when '8' then 11
when '9' then 12
end
set @mois_complet =datename(MONTH,@D_DEBUT)
set @semaine_annee = datepart(WW,@D_DEBUT)
set @jour = datepart(DD,@D_DEBUT)
set @jour_semaine = datepart(DW,@D_DEBUT)
if (@jour<16)
set @quinzaine =1
else
set @quinzaine =2
set @anneeS=@annee+1

if (@jour>0 and @mois>9) set @campagne=substring(@annee,3,2)+'/'+substring(@anneeS,3,2)
if (@jour>0 and @mois>9) set @annee2=@anneeS



INSERT INTO DIM_TEMPS(DATEID,ANNEE,TRIMESTRE,
MOIS,LIB_MOIS,SEMAINE,
JOUR,JOUR_SEMAINE,
QUINZAINE,CAMPAGNE,ANNEE_CAMP,ORDRE_MOIS)
VALUES(@Date_Complete,@Annee,CONVERT(int,@trimestre),
CONVERT(int,@mois),@mois_complet,CONVERT(int,@semaine_annee),
CONVERT(int,@jour),CONVERT(int,@jour_semaine),
CONVERT(int,@quinzaine),@campagne,@annee2,CONVERT(int,@ordre_mois))

set @D_DEBUT =dateadd(d,1,@D_DEBUT)
END

Update DIM_TEMPS set DECADE=case When day(DATEID) between 1 and 10 then convert(varchar(2),month(DATEID))+'-D1'
When day(DATEID) between 11 and 20 then convert(varchar(2),month(DATEID))+'-D2'
When day(DATEID) between 21 and 31 then convert(varchar(2),month(DATEID))+'-D3' END


END

GO

and this is the message error :

Msg 242, Level 16, State 3, Procedure PS_DIM_TEMPS, Line 29
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

(0 row(s) affected)

(1 row(s) affected)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search