Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Windows Windows Serveur SQL Serveur pb alter table dans procédure stockée

pb alter table dans procédure stockée

4 réponses
Avatar
Anne-Laure
Bonjour,

sous SQL server 2000, j'ai un problème dans une procédure stockée : je crée
une table temporaire, je fais un bulk insert d'un fichier dans cette table,
je modifie la table pour ajouter une colonne, puis je fais des contrôles sur
les données.
L'alter table pour ajouter une colonne fonctionne seulement si il n'y a pas
le contrôle de donnée ensuite. Je ne comprends pas pourquoi...

voilà le code :

CREATE PROCEDURE [dbo].ps_tap_import_GLA
-- importe les données du fichier du grand livre analytique dans la table
après différents contrôles
-- n'est lancée que s'il y a un fichier à intégrer
@cheminFichier varchar(100),
@nomFichier varchar(100)
AS
declare @query varchar(2000)
declare @result int
begin

-- On créé la table Temporaire de destination
if exists (select uid from tempdb.dbo.sysobjects where name =
'##GL_ANALYTIQUE')
drop table ##GL_ANALYTIQUE

CREATE TABLE ##GL_ANALYTIQUE (
num_mvt varchar(255) NULL,
num_ecriture varchar (255) NULL ,
compte_ana_12 varchar (255) NULL ,
compte_gene varchar (255) NULL ,
code_uc_aff varchar (255) NULL ,
code_uc_emet varchar (255) NULL ,
debit varchar (255) NULL ,
credit varchar (255) NULL ,
quantite varchar (255) NULL ,
code_journal varchar (255) NULL ,
complement_uc varchar (255) NULL ,
ref_externe varchar (255) NULL ,
exercice_comptable varchar (255) NULL ,
mois_comptable varchar (255) NULL ,
code_periode varchar (255) NULL ,
date_enregistrement varchar (255) NULL ,
lib_ecr varchar(255) NULL
)

set @nomFichier=@cheminFichier+@nomFichier
-- On insère les données du fichier dans la table temporaire
set @query='bulk insert dbo.##GL_ANALYTIQUE from ''' + @nomFichier + ''''
set @query=@query+' with ('
set @query=@query+'fieldterminator=''\t'','
set @query=@query+'firstrow=2,'
set @query=@query+'keepnulls)'
print @query
execute (@query)

-- On ajoute un champ flag à la table temporaire
print 'ajout flag à la table'
ALTER TABLE dbo.##GL_ANALYTIQUE ADD flag1 varchar(1) NULL


-- On contrôle les lignes
print 'contrôle des lignes'
-- longueur de champs
-- si l'update suivant est enlevé, la table est modifiée, tout va bien
-- sinon, la table n'est pas modifié, et la colonne flag1 n'existe pas !!!!
update dbo.##GL_ANALYTIQUE
set flag1='1'
where len(num_mvt) > 9
or len(compte_gene) > 8
or len(code_journal) > 3
or len(complement_uc) > 5
or len(ref_externe) > 15
or len(code_periode) > 1
or len(lib_ecr)>30
or len(num_ecriture)>9


end
GO


Je vous remercie d'avance pour votre aide !
Bonne journée,
Anne-Laure
Signaler un problème avec ce contenu

4 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
Fred BROUARD
manipuler du DDL dans une SP est toujours sujet à caution car lors de la
compilation de la SP le moteur SQL à besoin de connaître la structure des objets
en jeu.

Je vous conseille de créer la structure de votre table de manière propre dès le
départ à l'aide d'un fichier de format ce qui permet de cartographier les
colonnes dans l'import. Respectez aussi les longeurs des littéraux...
255 caractères pour un exercie comptable me parait quelque peu exagéré.

A lire : utilitaire bcp, fichiers de formats
dans l'aide en ligne

A +

Anne-Laure a écrit:
Bonjour,

sous SQL server 2000, j'ai un problème dans une procédure stockée : je crée
une table temporaire, je fais un bulk insert d'un fichier dans cette table,
je modifie la table pour ajouter une colonne, puis je fais des contrôles sur
les données.
L'alter table pour ajouter une colonne fonctionne seulement si il n'y a pas
le contrôle de donnée ensuite. Je ne comprends pas pourquoi...

voilà le code :

CREATE PROCEDURE [dbo].ps_tap_import_GLA
-- importe les données du fichier du grand livre analytique dans la table
après différents contrôles
-- n'est lancée que s'il y a un fichier à intégrer
@cheminFichier varchar(100),
@nomFichier varchar(100)
AS
declare @query varchar(2000)
declare @result int
begin

-- On créé la table Temporaire de destination
if exists (select uid from tempdb.dbo.sysobjects where name =
'##GL_ANALYTIQUE')
drop table ##GL_ANALYTIQUE

CREATE TABLE ##GL_ANALYTIQUE (
num_mvt varchar(255) NULL,
num_ecriture varchar (255) NULL ,
compte_ana_12 varchar (255) NULL ,
compte_gene varchar (255) NULL ,
code_uc_aff varchar (255) NULL ,
code_uc_emet varchar (255) NULL ,
debit varchar (255) NULL ,
credit varchar (255) NULL ,
quantite varchar (255) NULL ,
code_journal varchar (255) NULL ,
complement_uc varchar (255) NULL ,
ref_externe varchar (255) NULL ,
exercice_comptable varchar (255) NULL ,
mois_comptable varchar (255) NULL ,
code_periode varchar (255) NULL ,
date_enregistrement varchar (255) NULL ,
lib_ecr varchar(255) NULL
)

set @nomFichier=@cheminFichier+@nomFichier
-- On insère les données du fichier dans la table temporaire
set @query='bulk insert dbo.##GL_ANALYTIQUE from ''' + @nomFichier + ''''
set @query=@query+' with ('
set @query=@query+'fieldterminator=''t'','
set @query=@query+'firstrow=2,'
set @query=@query+'keepnulls)'
print @query
execute (@query)

-- On ajoute un champ flag à la table temporaire
print 'ajout flag à la table'
ALTER TABLE dbo.##GL_ANALYTIQUE ADD flag1 varchar(1) NULL


-- On contrôle les lignes
print 'contrôle des lignes'
-- longueur de champs
-- si l'update suivant est enlevé, la table est modifiée, tout va bien
-- sinon, la table n'est pas modifié, et la colonne flag1 n'existe pas !!!!
update dbo.##GL_ANALYTIQUE
set flag1='1'
where len(num_mvt) > 9
or len(compte_gene) > 8
or len(code_journal) > 3
or len(complement_uc) > 5
or len(ref_externe) > 15
or len(code_periode) > 1
or len(lib_ecr)>30
or len(num_ecriture)>9


end
GO


Je vous remercie d'avance pour votre aide !
Bonne journée,
Anne-Laure



--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Avatar
Med Bouchenafa
C'est effectivement assez surprenant!!!
T'es bien sure que la seul difference est la ligne UPDATE
Je conviens que ta procedure est peut etre longue et complexe mais il serait
interessant d'essayer d'isoler le probleme en la reecrivant de facon assez
generique en supprimant un a un les ontroles intermediaires.
Tu t'apercevras peut etre plus facilement du bout de code responsable
Si la fin, tu as toujours le meme probleme, publie alors le code de cette
procedure simpliee afin de pouvoir tester

--
Bien cordialement
Med Bouchenafa


"Anne-Laure" wrote in message
news:
Bonjour,

sous SQL server 2000, j'ai un problème dans une procédure stockée : je
crée
une table temporaire, je fais un bulk insert d'un fichier dans cette
table,
je modifie la table pour ajouter une colonne, puis je fais des contrôles
sur
les données.
L'alter table pour ajouter une colonne fonctionne seulement si il n'y a
pas
le contrôle de donnée ensuite. Je ne comprends pas pourquoi...

voilà le code :

CREATE PROCEDURE [dbo].ps_tap_import_GLA
-- importe les données du fichier du grand livre analytique dans la table
après différents contrôles
-- n'est lancée que s'il y a un fichier à intégrer
@cheminFichier varchar(100),
@nomFichier varchar(100)
AS
declare @query varchar(2000)
declare @result int
begin

-- On créé la table Temporaire de destination
if exists (select uid from tempdb.dbo.sysobjects where name > '##GL_ANALYTIQUE')
drop table ##GL_ANALYTIQUE

CREATE TABLE ##GL_ANALYTIQUE (
num_mvt varchar(255) NULL,
num_ecriture varchar (255) NULL ,
compte_ana_12 varchar (255) NULL ,
compte_gene varchar (255) NULL ,
code_uc_aff varchar (255) NULL ,
code_uc_emet varchar (255) NULL ,
debit varchar (255) NULL ,
credit varchar (255) NULL ,
quantite varchar (255) NULL ,
code_journal varchar (255) NULL ,
complement_uc varchar (255) NULL ,
ref_externe varchar (255) NULL ,
exercice_comptable varchar (255) NULL ,
mois_comptable varchar (255) NULL ,
code_periode varchar (255) NULL ,
date_enregistrement varchar (255) NULL ,
lib_ecr varchar(255) NULL
)

set @nomFichier=@cheminFichier+@nomFichier
-- On insère les données du fichier dans la table temporaire
set @query='bulk insert dbo.##GL_ANALYTIQUE from ''' + @nomFichier +
''''
set @query=@query+' with ('
set @query=@query+'fieldterminator=''t'','
set @query=@query+'firstrow=2,'
set @query=@query+'keepnulls)'
print @query
execute (@query)

-- On ajoute un champ flag à la table temporaire
print 'ajout flag à la table'
ALTER TABLE dbo.##GL_ANALYTIQUE ADD flag1 varchar(1) NULL


-- On contrôle les lignes
print 'contrôle des lignes'
-- longueur de champs
-- si l'update suivant est enlevé, la table est modifiée, tout va bien
-- sinon, la table n'est pas modifié, et la colonne flag1 n'existe pas
!!!!
update dbo.##GL_ANALYTIQUE
set flag1='1'
where len(num_mvt) > 9
or len(compte_gene) > 8
or len(code_journal) > 3
or len(complement_uc) > 5
or len(ref_externe) > 15
or len(code_periode) > 1
or len(lib_ecr)>30
or len(num_ecriture)>9


end
GO


Je vous remercie d'avance pour votre aide !
Bonne journée,
Anne-Laure


Avatar
GNocent
D'accord avec mes prédecesseurs, le mélange DML/DDL dans une proc stokée
n'est pas sans quelques conséquences.
Cependant, sous vous ne pouvez pas vous en passer, un contournement peut
malgré tout exister, à savoir effectuer le alter table dans un exec
(EXEC('alter table ...')).

Guillaume.
======================= "Med Bouchenafa" a écrit :

C'est effectivement assez surprenant!!!
T'es bien sure que la seul difference est la ligne UPDATE
Je conviens que ta procedure est peut etre longue et complexe mais il serait
interessant d'essayer d'isoler le probleme en la reecrivant de facon assez
generique en supprimant un a un les ontroles intermediaires.
Tu t'apercevras peut etre plus facilement du bout de code responsable
Si la fin, tu as toujours le meme probleme, publie alors le code de cette
procedure simpliee afin de pouvoir tester

--
Bien cordialement
Med Bouchenafa


"Anne-Laure" wrote in message
news:
> Bonjour,
>
> sous SQL server 2000, j'ai un problème dans une procédure stockée : je
> crée
> une table temporaire, je fais un bulk insert d'un fichier dans cette
> table,
> je modifie la table pour ajouter une colonne, puis je fais des contrôles
> sur
> les données.
> L'alter table pour ajouter une colonne fonctionne seulement si il n'y a
> pas
> le contrôle de donnée ensuite. Je ne comprends pas pourquoi...
>
> voilà le code :
>
> CREATE PROCEDURE [dbo].ps_tap_import_GLA
> -- importe les données du fichier du grand livre analytique dans la table
> après différents contrôles
> -- n'est lancée que s'il y a un fichier à intégrer
> @cheminFichier varchar(100),
> @nomFichier varchar(100)
> AS
> declare @query varchar(2000)
> declare @result int
> begin
>
> -- On créé la table Temporaire de destination
> if exists (select uid from tempdb.dbo.sysobjects where name > > '##GL_ANALYTIQUE')
> drop table ##GL_ANALYTIQUE
>
> CREATE TABLE ##GL_ANALYTIQUE (
> num_mvt varchar(255) NULL,
> num_ecriture varchar (255) NULL ,
> compte_ana_12 varchar (255) NULL ,
> compte_gene varchar (255) NULL ,
> code_uc_aff varchar (255) NULL ,
> code_uc_emet varchar (255) NULL ,
> debit varchar (255) NULL ,
> credit varchar (255) NULL ,
> quantite varchar (255) NULL ,
> code_journal varchar (255) NULL ,
> complement_uc varchar (255) NULL ,
> ref_externe varchar (255) NULL ,
> exercice_comptable varchar (255) NULL ,
> mois_comptable varchar (255) NULL ,
> code_periode varchar (255) NULL ,
> date_enregistrement varchar (255) NULL ,
> lib_ecr varchar(255) NULL
> )
>
> set @nomFichier=@cheminFichier+@nomFichier
> -- On insère les données du fichier dans la table temporaire
> set @query='bulk insert dbo.##GL_ANALYTIQUE from ''' + @nomFichier +
> ''''
> set @query=@query+' with ('
> set @query=@query+'fieldterminator=''t'','
> set @query=@query+'firstrow=2,'
> set @query=@query+'keepnulls)'
> print @query
> execute (@query)
>
> -- On ajoute un champ flag à la table temporaire
> print 'ajout flag à la table'
> ALTER TABLE dbo.##GL_ANALYTIQUE ADD flag1 varchar(1) NULL
>
>
> -- On contrôle les lignes
> print 'contrôle des lignes'
> -- longueur de champs
> -- si l'update suivant est enlevé, la table est modifiée, tout va bien
> -- sinon, la table n'est pas modifié, et la colonne flag1 n'existe pas
> !!!!
> update dbo.##GL_ANALYTIQUE
> set flag1='1'
> where len(num_mvt) > 9
> or len(compte_gene) > 8
> or len(code_journal) > 3
> or len(complement_uc) > 5
> or len(ref_externe) > 15
> or len(code_periode) > 1
> or len(lib_ecr)>30
> or len(num_ecriture)>9
>
>
> end
> GO
>
>
> Je vous remercie d'avance pour votre aide !
> Bonne journée,
> Anne-Laure





Avatar
Anne-Laure
Merci pour vos réponses !
Je vais donc abandonner l'idée de l'alter table et passer par un fichier de
format... Mais j'ai une erreur lors du bulk insert : Serveur : Msg 4828,
Niveau 16, État 1, Ligne 1
Could not bulk insert. Invalid destination table column number for source
column 1 in format file 's64vbibi670EMC2fichiers_sicfichier_format.fmt'.

Je précise ci-dessous mon code et le fichier de format.
Je vous remercie grandement par avance pour tout élément de réponse !!

Anne-Laure

Mon code :

CREATE PROCEDURE [dbo].ps_tap_import_GLA
@cheminFichier varchar(100),
@nomFichier varchar(100)
AS
declare @query varchar(2000)
declare @result int
begin

-- On créé la table Temporaire de destination
if exists (select uid from tempdb.dbo.sysobjects where name =
'##GL_ANALYTIQUE')
drop table ##GL_ANALYTIQUE

CREATE TABLE ##GL_ANALYTIQUE (num_ecriture varchar (255) NULL ,
num_mvt varchar(255) NULL,
code_uc_aff varchar (255) NULL ,
code_journal varchar (255) NULL ,
exercice_comptable varchar (255) NULL ,
mois_comptable varchar (255) NULL ,
complement_uc varchar (255) NULL ,
compte_gene varchar (255) NULL ,
compte_ana_12 varchar (255) NULL ,
code_uc_emet varchar (255) NULL ,
ref_externe varchar (255) NULL ,
debit varchar (255) NULL ,
credit varchar (255) NULL ,
quantite varchar (255) NULL ,
code_periode varchar (255) NULL ,
date_enregistrement varchar (255) NULL ,
lib_ecr varchar(255) NULL,
flag varchar(1) NULL)


set @nomFichier=@cheminFichier+@nomFichier
-- On insère les données du fichier dans la table temporaire
set @query='bulk insert dbo.##GL_ANALYTIQUE from ''' + @nomFichier + ''''
set @query=@query+' with ('
set @query=@query+'fieldterminator=''t'','
set @query=@query+'firstrow=2,'
set @query=@query+'FORMATFILE =
''serveurfichiers_sicfichier_format.fmt'','
set @query=@query+'keepnulls,'
set @query=@query+'ROWTERMINATOR = ''n'')'
print @query
execute (@query)

end
GO

Le fichier "fichier_format.fmt" :
8.0
16
1 SQLCHAR 0 255 "t" num_mvt 1 ""
2 SQLCHAR 0 255 "t" code_uc_aff 2 ""
3 SQLCHAR 0 255 "t" code_journal 3 ""
4 SQLCHAR 0 255 "t" exercice_comptable 4 ""
5 SQLCHAR 0 255 "t" mois_comptable 5 ""
6 SQLCHAR 0 255 "t" complement_uc 6 ""
7 SQLCHAR 0 255 "t" compte_gene 7 ""
8 SQLCHAR 0 255 "t" compte_ana_12 8 ""
9 SQLCHAR 0 255 "t" code_uc_emet 9 ""
10 SQLCHAR 0 255 "t" ref_externe 10 ""
11 SQLCHAR 0 255 "t" debit 11 ""
12 SQLCHAR 0 255 "t" credit 12 ""
13 SQLCHAR 0 255 "t" quantite 13 ""
14 SQLCHAR 0 255 "t" code_periode 14 ""
15 SQLCHAR 0 255 "t" date_enregistrement 15 ""
16 SQLCHAR 0 255 "n" lib_ecr 16 ""

"GNocent" a écrit :

D'accord avec mes prédecesseurs, le mélange DML/DDL dans une proc stokée
n'est pas sans quelques conséquences.
Cependant, sous vous ne pouvez pas vous en passer, un contournement peut
malgré tout exister, à savoir effectuer le alter table dans un exec
(EXEC('alter table ...')).

Guillaume.
======================= > "Med Bouchenafa" a écrit :

> C'est effectivement assez surprenant!!!
> T'es bien sure que la seul difference est la ligne UPDATE
> Je conviens que ta procedure est peut etre longue et complexe mais il serait
> interessant d'essayer d'isoler le probleme en la reecrivant de facon assez
> generique en supprimant un a un les ontroles intermediaires.
> Tu t'apercevras peut etre plus facilement du bout de code responsable
> Si la fin, tu as toujours le meme probleme, publie alors le code de cette
> procedure simpliee afin de pouvoir tester
>
> --
> Bien cordialement
> Med Bouchenafa
>
>
> "Anne-Laure" wrote in message
> news:
> > Bonjour,
> >
> > sous SQL server 2000, j'ai un problème dans une procédure stockée : je
> > crée
> > une table temporaire, je fais un bulk insert d'un fichier dans cette
> > table,
> > je modifie la table pour ajouter une colonne, puis je fais des contrôles
> > sur
> > les données.
> > L'alter table pour ajouter une colonne fonctionne seulement si il n'y a
> > pas
> > le contrôle de donnée ensuite. Je ne comprends pas pourquoi...
> >
> > voilà le code :
> >
> > CREATE PROCEDURE [dbo].ps_tap_import_GLA
> > -- importe les données du fichier du grand livre analytique dans la table
> > après différents contrôles
> > -- n'est lancée que s'il y a un fichier à intégrer
> > @cheminFichier varchar(100),
> > @nomFichier varchar(100)
> > AS
> > declare @query varchar(2000)
> > declare @result int
> > begin
> >
> > -- On créé la table Temporaire de destination
> > if exists (select uid from tempdb.dbo.sysobjects where name > > > '##GL_ANALYTIQUE')
> > drop table ##GL_ANALYTIQUE
> >
> > CREATE TABLE ##GL_ANALYTIQUE (
> > num_mvt varchar(255) NULL,
> > num_ecriture varchar (255) NULL ,
> > compte_ana_12 varchar (255) NULL ,
> > compte_gene varchar (255) NULL ,
> > code_uc_aff varchar (255) NULL ,
> > code_uc_emet varchar (255) NULL ,
> > debit varchar (255) NULL ,
> > credit varchar (255) NULL ,
> > quantite varchar (255) NULL ,
> > code_journal varchar (255) NULL ,
> > complement_uc varchar (255) NULL ,
> > ref_externe varchar (255) NULL ,
> > exercice_comptable varchar (255) NULL ,
> > mois_comptable varchar (255) NULL ,
> > code_periode varchar (255) NULL ,
> > date_enregistrement varchar (255) NULL ,
> > lib_ecr varchar(255) NULL
> > )
> >
> > set @nomFichier=@cheminFichier+@nomFichier
> > -- On insère les données du fichier dans la table temporaire
> > set @query='bulk insert dbo.##GL_ANALYTIQUE from ''' + @nomFichier +
> > ''''
> > set @query=@query+' with ('
> > set @query=@query+'fieldterminator=''t'','
> > set @query=@query+'firstrow=2,'
> > set @query=@query+'keepnulls)'
> > print @query
> > execute (@query)
> >
> > -- On ajoute un champ flag à la table temporaire
> > print 'ajout flag à la table'
> > ALTER TABLE dbo.##GL_ANALYTIQUE ADD flag1 varchar(1) NULL
> >
> >
> > -- On contrôle les lignes
> > print 'contrôle des lignes'
> > -- longueur de champs
> > -- si l'update suivant est enlevé, la table est modifiée, tout va bien
> > -- sinon, la table n'est pas modifié, et la colonne flag1 n'existe pas
> > !!!!
> > update dbo.##GL_ANALYTIQUE
> > set flag1='1'
> > where len(num_mvt) > 9
> > or len(compte_gene) > 8
> > or len(code_journal) > 3
> > or len(complement_uc) > 5
> > or len(ref_externe) > 15
> > or len(code_periode) > 1
> > or len(lib_ecr)>30
> > or len(num_ecriture)>9
> >
> >
> > end
> > GO
> >
> >
> > Je vous remercie d'avance pour votre aide !
> > Bonne journée,
> > Anne-Laure
>
>
>