Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

stored procedure troubles Expand / Collapse
Author
Message
Posted Thursday, May 09, 2013 2:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 05, 2013 4:11 AM
Points: 25, Visits: 105
Hello im back and hope i will be comprehensible ( i dont know it exist in english :D )
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 ?
Post #1450939
Posted Thursday, May 09, 2013 2:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1450941
Posted Thursday, May 09, 2013 3:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 05, 2013 4:11 AM
Points: 25, 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
Post #1450961
Posted Thursday, May 09, 2013 4:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 05, 2013 4:11 AM
Points: 25, Visits: 105
i typed back up my DW and i do it , but i meant restore my DW
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'.

Post #1450987
Posted Thursday, May 09, 2013 4:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451018
Posted Thursday, May 09, 2013 5:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 05, 2013 4:11 AM
Points: 25, 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
Post #1451021
Posted Thursday, May 09, 2013 5:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451030
Posted Thursday, May 09, 2013 5:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 05, 2013 4:11 AM
Points: 25, 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'.
Post #1451039
Posted Thursday, May 09, 2013 6:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451060
Posted Thursday, May 09, 2013 8:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 05, 2013 4:11 AM
Points: 25, 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 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)
Post #1451138
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse