Trim all user tables with nvarchar datatype

  • We are working with a vendor that recently supplied us with a new copy of their database. They changed 70-100 user tables field datatypes from char to nvarchar. In doing so, our data has padding or trailing spaces. A couple of the fields also have preceeding spaces...

    Can someone help me with a script that will:

    Loop through all user tables with nvarchar datatype and perform a ltrim(rtrim(field))?

  • This will take a few modifications but the basic is there :

    for the server objects

    IF Object_id('ListTableColumns_NotNulls') > 0

    DROP FUNCTION dbo.ListTableColumns_NotNulls

    GO

    IF Object_id('TablesColumnsNulls') > 0

    DROP TABLE dbo.TablesColumnsNulls

    GO

    IF Object_id('TablesColumnsNulls_Insert') > 0

    DROP PROCEDURE dbo.TablesColumnsNulls_Insert

    GO

    CREATE FUNCTION dbo.ListTableColumns_NotNulls (@TableID as int)

    RETURNS varchar(8000)

    AS

    BEGIN

    Declare @Items as varchar(8000)

    SET @Items = ''

    SELECT

    @Items = @Items + 'COUNT([' + C.Name + ']) [' + C.Name + '],'

    FROMdbo.SysColumns C

    WHEREC.id = @TableID

    AND OBJECTPROPERTY(@TableID, 'IsTable') = 1

    AND C.IsNullAble = 1

    AND XType NOT IN (34,35,36,99)

    ORDER BYC.Name

    SET @Items = LEFT(@Items, ABS(LEN(@Items) - 1))

    RETURN @Items

    END

    GO

    CREATE TABLE dbo.TablesColumnsNulls

    (

    TableName sysname not null,

    DateLog datetime not null default (GetDate()),

    ColumnName sysname not null,

    QtyNonNulls int not null,

    primary key clustered (DateLog, TableName, ColumnName)

    )

    GO

    CREATE PROCEDURE dbo.TablesColumnsNulls_Insert @TableName as sysname, @ColumnName as sysname, @QtyNonNulls as int

    AS

    SET NOCOUNT ON

    Insert into dbo.TablesColumnsNulls (TableName, ColumnName, QtyNonNulls) values (@TableName, @ColumnName, @QtyNonNulls)

    SET NOCOUNT OFF

    GO

    The dts activeX script :

    Function Main()

    Dim MyRsCols

    Dim MyRsCount

    Dim MyCn

    Dim OColumns

    Dim OTable

    Dim oField

    Dim sTable

    Dim iCount

    SET MyCn = CreateObject ("ADODB.Connection")

    SET MyRsCols = CreateObject ("ADODB.RecordSet")

    SET MyRsCount = CreateObject ("ADODB.RecordSet")

    MyCn.Open ("Provider=sqloledb;Data Source=SERVEUR4;Initial Catalog=Ideal;Integrated Security=SSPI;")

    MyRsCols.Open "Select O.Name, NULLIF(dbo.ListTableColumns_NotNulls(O.id), '') as NullColumns from dbo.SysObjects O WHERE O.XType = 'U' and LEN(dbo.ListTableColumns_NotNulls(O.id)) < 7000 order by O.Name", MyCn, 1, 3

    if not MyRsCols.EOF THEN

    SET OTable = MyRsCols.Fields ("Name")

    SET OColumns = MyRsCols.Fields ("NullColumns")

    WHILE NOT MyRsCols.EOF

    if not ISNULL(OColumns.value) THEN

    MyRsCount.Open "Select '[" & OTable.Value & "]', " & OColumns.Value & " FROM dbo.[" & OTable.value & "]", MyCn, 1, 3

    sTable = ""

    iCount = 0

    For each oField in MyRsCount.Fields

    if iCount = 0 then

    sTable = MyRsCount.Fields(0).Value

    else

    MyCn.Execute "EXEC dbo.TablesColumnsNulls_Insert '" & sTable & "', '" & OField.Name & "', " & oField.Value

    end if

    iCount = iCount + 1

    next

    MyRsCount.Close

    END IF

    MyRsCols.MoveNext

    WEND

    END IF

    MyRsCols.Close

    MyCn.Close

    SET MyRsCount = NOTHING

    SET MyRsCols = NOTHING

    SET MyCn = NOTHING

    Main = DTSTaskExecResult_Success

    End Function

  • here's a script to find all columns by datatype that might help:

    select sysobjects.name as TblName,

    syscolumns.name as ColName,

    type_name(syscolumns.xtype) As DataType

    --into #FKFinder

     from sysobjects

    inner join syscolumns on sysobjects.id=syscolumns.id

    where sysobjects.xtype='U'

    and type_name(syscolumns.xtype)='datetime'

    --datatypes'bit','char','datetime','float','image','int','money','nvarchar','real','smallint','text','varchar')

    --and syscolumns.name like '%TBLKEY'

    order by TblName,colname

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • There are actually a couple of ways to do this.  I use the folowing:

    declare TextColumnCursor cursor for select name from syscolumns

       where id in (select id from sysobjects where name = N'TblPrepTemplate')

       and xtype = 231 --nVarchar

    open TextColumnCursor

    declare @ColName varchar (50), @SQLText nvarchar (4000)

    fetch next from TextColumnCursor into @ColName

    while @@fetch_status = 0

    begin

     -- Trims all Field

     set @SQLText = 'update TblPrepTemplate set ['

     set @SQLText = @SQLText + @ColName + '] = ltrim(rtrim(['

     set @SQLText = @SQLText + @ColName + ']))'

     Exec (@SQLText) 

     fetch next from TextColumnCursor into @ColName

    end

    close TextColumnCursor

    deallocate TextColumnCursor

     

     

     

    <hr noshade size='1' width='250' color='#BBC8E5'>Kindest Regards,

    Roelof
    <a href='http://' class='authorlink' target='_blank'></a>

    --There are only 10 types of people in the world. Those who understand binary, and those who don't.

  • That's the simple way... the problem is that if you have a table with 100 text columns and a few million rows, it'll take hours/day to finish this. While my version does a single pass and updates all the columns for the whole table in one shot.

  • I like Roelof's solution But instead of building and executing an update statement for each text column of the table, it's probably faster to use the loop to build a single update statement that updates all text columns of the table, and then execute this after the loop. This keeps the code simple and I don't think you can do it much faster...

    I have better mention that such an update statement can also be generated without using a cursor, otherwise Remi will tell me that cursors are bad (because of performance) and can almost always be avoided In this case, however, I don't think it is worth doing it...

  • See any cursor in my code?

    How much do you wanna bet that my code finishes WAAAYYY before Roelof's solution??

  • I think my solution is faster than Roelof's solution... The cursor part of my solution will take less than 1 second, and after that there is "only" a single update statement to be executed. I don't think this can be done any faster, but I could be wrong...

  • You're basically doig the same thing as I am, however I don't have a limit of 8000 characters to work with (providing moving the function's work in the dts loop) .

  • I think I can live with the 8000 character limit

    Especially if it means that my code is easier to read... I still haven't completely figured out how your code works - but that's not the first time...

  • Here's why I moved this code to vbs... and why I would have to put the columns' listing in vbs as well :

    CREATE TABLE [Chaudières] (

    [no] [int] IDENTITY (1, 1) NOT NULL ,

    [CNUM] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Nom du client] [nvarchar] (50) COLLATE French_CI_AS NOT NULL ,

    [Succursale] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Numéro] [int] NULL ,

    [Rue] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Ville] [nvarchar] (50) COLLATE French_CI_AS NOT NULL ,

    [Responsable] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [N° téléphone] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Appareil] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Date de dernière mise à jour] [smalldatetime] NULL ,

    [Appareil #] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Eau Chaude] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Eau Chaude] DEFAULT (0),

    [Vapeur] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Vapeur] DEFAULT (0),

    [Huile Thermique] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Huile Thermique] DEFAULT (0),

    [Huile#2] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Huile#2] DEFAULT (0),

    [Huile#6] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Huile#6] DEFAULT (0),

    [Huile usée] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Huile usée] DEFAULT (0),

    [Gaz Naturel] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Gaz Naturel] DEFAULT (0),

    [Gaz Propane] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Gaz Propane] DEFAULT (0),

    [Gaz Butane] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Gaz Butane] DEFAULT (0),

    [Électrique] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Électrique] DEFAULT (0),

    [Autre1] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Autre1] DEFAULT (0),

    [Autre] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Pression de gaz] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Série] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [commande] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Capacité de la chaudière] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Capacité de la chaudière2] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Crn] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Surface de chauffe] [real] NULL ,

    [dappareil usagé] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Trou de main qtée] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [trou de main dim] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Trou de main qtée2] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [trou de main dim2] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [trou dhomme qtée] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [trou dhomme dim] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Tubes Qtée] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Tubes Diamètre] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Tubes Longueur 1] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Tubes Quantité 2] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Tubes Diamètre 2] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Tubes Longueur 2] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Gauge Glass Ø] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Gauge Glass longueur] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Pression de conception] [int] NULL ,

    [Pression dopération] [int] NULL ,

    [Brûleur intégral] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Brûleur intégral] DEFAULT (0),

    [Brûleur Marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Brûleur Modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Brûleur Série] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Moteur HP] [nvarchar] (255) COLLATE French_CI_AS NULL ,

    [Moteur Volts] [nvarchar] (255) COLLATE French_CI_AS NULL ,

    [Moteur RPM] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Moteur Frame] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Nozzle débit] [numeric](18, 2) NULL ,

    [Nozzle degré] [int] NULL ,

    [Nozzle type] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Electrodes] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Transformateur dallumage marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Transformateur dallumage modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Type de ventilateur] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Dimension du ventilateur] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Sens de rotation du ventilateur] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Régulateur marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Régulateur modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Régulateur orifice] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Régulateur spring] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve principale #1 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve principale #1 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve #1 actuateur] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve principale #2 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve principale #2 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve #2 actuateur] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Vent valve marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Vent valve modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve modulante marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve modulante modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Actuateur modulant marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Actuateur modulant modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Lgps marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Lgps modele] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Hgps marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Hgps modele] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve lubrifiée principale marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve lubrifiée principale modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Régulateur de veilleuse marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Régulateur de veilleuse modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Régulateur de veilleuse orifice] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Régulateur de veilleuse spring] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve veilleuse #1 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve veilleuse #1 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve veilleuse #2 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve veilleuse #2 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Vent valve de veilleuse marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Vent valve de veilleuse modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve lubrifiée de veilleuse marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve lubrifiée de veilleuse modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve principale dhuile #1 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve principale dhuile #1 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve principale dhuile #2 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve principale dhuile #2 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve modulante dhuile marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve modulante dhuile modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Actuateur modulant dhuile marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Actuateur modulant dhuile modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de retour marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de retour modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de purge marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de purge modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Réchaud dhuile électrique marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Réchaud dhuile électrique modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Réchaud dhuile à vapeur marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Réchaud dhuile à vapeur modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Contrôle temp dhuile marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Contrôle temp dhuile modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Carte de sortie du contrôle marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Carte de sortie du contrôle modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Compresseur à air marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Compresseur à air modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Filtre à air marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Filtre à air modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Filtre à huile marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Filtre à huile modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Courroie] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Moteur compresseur HP] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Moteur compresseur Volts] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Moteur compresseur RPM] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Moteur compresseur Frame] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Contrôle de bas niveau deau marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Contrôle de bas niveau deau modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Contrôle de très bas niveau deau marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Contrôle de très bas niveau deau modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Contrôle de haut niveau deau marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Contrôle de haut niveau deau modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Haute limite marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Haute limite modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Opérateur marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Opérateur modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Contrôle modulation marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Contrôle modulation modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Interrupteur de pression dair marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Interrupteur de pression dair modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Superviseur de flamme marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Superviseur de flamme modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Programmeur marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Programmeur modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Amplificateur marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Amplificateur modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Détecteur de flamme marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Détecteur de flamme modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #1 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #1 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #1 pression] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #1 capacité] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #1 dimension] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #2 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #2 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #2 pression] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #2 capacité] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #2 dimension] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #3 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #3 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #3 pression] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #3 capacité] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #3 dimension] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #4 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #4 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #4 pression] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #4 capacité] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #4 dimension] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #5 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #5 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #5 pression] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #5 capacité] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Valve de sureté #5 dimension] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Fusibles du moteur du ventilateur] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Fusibles de la metering pump] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Fusibles du réchaud électrique] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Fusibles du transformateur de contrôle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Démarreur du moteur du ventilateur marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Démarreur du moteur du ventilateur modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [O/L modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [O/L Range] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [Transformateur de contrôle] [nvarchar] (50) COLLATE French_CI_AS NULL ,

    [DateInspection] [datetime] NULL ,

    [Ouverte] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Ouverte] DEFAULT (0),

    [DateNettoyage] [datetime] NULL ,

    [InspecteurEstFaxe] [bit] NOT NULL CONSTRAINT [DF_Chaudières_InspecteurEstFaxe] DEFAULT (0),

    [FkContrat] [varchar] (10) COLLATE French_CI_AS NULL ,

    [ANettoyer] [bit] NOT NULL CONSTRAINT [DF_Chaudières_ANettoyer] DEFAULT (0),

    [AnneeInspection] [int] NULL ,

    [TsAccess] [timestamp] NOT NULL ,

    CONSTRAINT [PK_Chaudières] PRIMARY KEY NONCLUSTERED

    (

    [no]

    ) WITH FILLFACTOR = 90 ON [PRIMARY] ,

    CONSTRAINT [FK_Chaudières_FkContrat] FOREIGN KEY

    (

    [FkContrat]

    ) REFERENCES [PD et SOUM] (

    [# Prix donné]

    )

    ) ON [PRIMARY]

    GO

    Count it all you want, but 8000 characters is not long enough .

  • Got me there

    You are from Quebec, I guess (changing the subject )?

  • Yup.

    btw I DID NOT build this ?table??? if anyone's wondering .

  • Been to Montreal twice, nice city

    This should deal with your table

     

    create table Lines

    (

     Seq int,

     Line nvarchar(4000)

    )

    go

    declare @Query nvarchar(4000)

    declare @add nvarchar(2)

    declare @Seq int

    declare @Query1 nvarchar(4000)

    declare @Query2 nvarchar(4000)

    declare @Query3 nvarchar(4000)

    declare @Query4 nvarchar(4000)

    declare @Query5 nvarchar(4000)

    declare @Query6 nvarchar(4000)

    declare @Query7 nvarchar(4000)

    declare @Query8 nvarchar(4000)

    declare @Query9 nvarchar(4000)

    declare @Query10 nvarchar(4000)

    set @Query = ''

    set @Query1 = ''

    set @Query2 = ''

    set @Query3 = ''

    set @Query4 = ''

    set @Query5 = ''

    set @Query6 = ''

    set @Query7 = ''

    set @Query8 = ''

    set @Query9 = ''

    set @Query10 = ''

    set @add = ''

    set @Seq = 1

    declare TextColumnCursor cursor for select name from syscolumns

       where id in (select id from sysobjects where name = N'Chaudières')

       and xtype = 231 --nVarchar

    open TextColumnCursor

    declare @ColName varchar (50), @SQLText nvarchar (4000)

    fetch next from TextColumnCursor into @ColName

    while @@fetch_status = 0

    begin

     set @Query = @Query + @add + ' [' + @ColName + '] = ltrim(rtrim([' + @ColName + ']))'

     set @add = ', '

     if len(@Query) > 3000

     begin

      insert into Lines select @Seq, @Query

      set @Query = ''

      set @Seq = @Seq + 1

     end

     fetch next from TextColumnCursor into @ColName

    end

    close TextColumnCursor

    deallocate TextColumnCursor

    select @Query1 = Line from Lines where Seq = 1

    select @Query2 = Line from Lines where Seq = 2

    select @Query3 = Line from Lines where Seq = 3

    select @Query4 = Line from Lines where Seq = 4

    select @Query5 = Line from Lines where Seq = 5

    select @Query6 = Line from Lines where Seq = 6

    select @Query7 = Line from Lines where Seq = 7

    select @Query8 = Line from Lines where Seq = 8

    select @Query9 = Line from Lines where Seq = 9

    select @Query10 = Line from Lines where Seq = 10

    exec('update Chaudières set ' + @Query1 + @Query2 + @Query3 + @Query4 + @Query5 + @Query6 + @Query7 + @Query8 + @Query9 + @Query10)

    drop table Lines

    go

  • I'll keep using my dts task... far less complicated .

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply