mounaim's questions

Estou recebendo este erro:
"Error 102 : non-boolean type specified in a context where a condition is expected"
para esta solicitação:

DECLARE @num_dossiers TABLE (num_dossier INT,indice NVARCHAR(3))
insert into @num_dossiers
select num_dossier,indice from dossier where num_sec=57

delete from constitue where (num_dossier,indice) in select (num_dossier,indice) from @num_dossiers  

A solicitação que aciona o erro é a última:

delete from constitue where (num_dossier,indice) in select (num_dossier,indice) from @num_dossiers   

Qual pode ser a causa desse erro?

Quando tento expandir a lista de publicações locais no elemento Replication no SQ Server Management Studio, recebo o seguinte erro:

Aqui está o log de erro detalhado:

===================================
    
    Échec de la récupération de données pour cette demande. (Microsoft.SqlServer.Management.Sdk.Sfc)
    
    ------------------------------
    Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
    
    ------------------------------
    Emplacement du programme :
    
       à Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
       à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.NavigableItemBuilderDataReader.RunQuery()
       à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.NavigableItemBuilderDataReader.Process()
       à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.NavigableItemBuilderDataReader.get_PropertyNames()
       à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItemWithQuery(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, String urnQuery, Boolean registerBuilder, Boolean registerBuiltItems)
       à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItem(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
       à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.Build(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
       à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequestChildren(IGetChildrenRequest request)
       à Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent)
    
    ===================================
    
    Une exception s'est produite lors de l'exécution d'une instruction ou d'un lot Transact-SQL. (Microsoft.SqlServer.ConnectionInfo)
    
    ------------------------------
    Emplacement du programme :
    
       à Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       à Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteImmediate(String query)
       à Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
       à Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
       à Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
       à Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
       à Microsoft.SqlServer.Management.Smo.Replication.Publication.GetData(EnumResult erParent)
       à Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
       à Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
       à Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
       à Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
    
    ===================================
    
    Impossible de résoudre le conflit de classement entre "Arabic_CI_AI" et "Arabic_CI_AS" dans l'opération equal to. (.Net SqlClient Data Provider)
    
    ------------------------------
    Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.4042&EvtSrc=MSSQLServer&EvtID=468&LinkId=20476
    
    ------------------------------
    Nom du serveur : my-server
    Numéro de l'erreur : 468
    Gravité : 16
    État : 9
    Procédure : sp_MSrepl_enumpublications
    Numéro de la ligne : 213
    
    
    ------------------------------
    Emplacement du programme :
    
       à Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
       à Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

Como você deve ter notado, o log de erros está apontando para o procedimento armazenado sys sp_MSrepl_enumpublications que contém o seguinte código:

USE [master]
GO
/****** Object:  StoredProcedure [sys].[sp_MSrepl_enumpublications]    Script Date: 11/11/2016 15:13:26 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

--
-- Name:
--  sp_MSrepl_enumpublications
--
-- Description:
--  Enumerate publications on a database
--
-- Returns:
--      0 == Failed
--      1 == Succeed
--
-- Security:
--  public, PAL access for tran publications, dbo check for merge publications
--  Requires Certificate signature for catalog access
--
-- Notes:
--  Used by the UI to generate a list of pubications
--
--  Publication type:
--      0 == TRAN
--      1 == SNAPSHOT
--      2 == MERGE
--
ALTER PROCEDURE [sys].[sp_MSrepl_enumpublications]
(
    @reserved bit = 0   -- Set to 1 when used by UI
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @retcode int
                ,@pubid int
                ,@pubname sysname
                ,@username sysname
                ,@OPT_ENABLED_FOR_P2P int
                ,@category int
                ,@skippalcheck bit
                ,@dbname sysname
                ,@fpublished bit
                ,@OPT_ENABLED_FOR_P2PCONFLICTDETECTION int

    --
    -- initialize
    --
    select @dbname = db_name()
            ,@OPT_ENABLED_FOR_P2P = 0x1
            ,@username = SUSER_SNAME()
            ,@fpublished = 0
            ,@OPT_ENABLED_FOR_P2PCONFLICTDETECTION = 0x08
    --
    -- Verify database is published
    -- Have put the code from fn_MSrepl_ispublished inline for faster processing
    -- If fn_MSrepl_ispublished() is updated - this code block should be updated as well
    --
    SELECT @category = category
    FROM master.sys.sysdatabases
    WHERE name = @dbname 

    IF (@category IS NOT NULL)
    BEGIN
        -- We have entry for this db_name in sysdatabases
        -- Is this database a distributor?
        IF (@category & 16 = 16)
        BEGIN
            -- DB is a distributor - is it used for HREPL publisher?
            IF OBJECT_ID(N'msdb.dbo.MSdistpublishers') IS NOT NULL
            BEGIN
                IF EXISTS
                (
                    SELECT name
                    FROM msdb.dbo.MSdistpublishers
                    WHERE distribution_db = @dbname 
                        AND publisher_type != N'MSSQLSERVER'
                )
                BEGIN
                    SELECT @fpublished = 1
                END
            END
        END
        ELSE
        BEGIN
            -- Check if this database is a transactional or merge publisher
            IF (@category & 1 = 1 OR @category & 4 = 4)
            BEGIN
                SELECT @fpublished = 1
            END
        END
    END -- @category not null
    --
    -- If the database is not published - return
    --
    IF (@fpublished = 0) 
    BEGIN
        RETURN (0)
    END
    --
    -- Security Check.
    -- Skip PAL check if DBO 
    -- For PAL check - Part of the query to gather the list of publications uses security context
    --
    select @skippalcheck = case when (is_member ('db_owner') = 1 OR is_srvrolemember('sysadmin') = 1) then 1 else 0 end
    if (@skippalcheck = 0)
    begin
        DECLARE @accessiblepubs TABLE
        (
            pubid   int
        )
    end
    --
    -- Create local temp table if needed
    --
    if (@reserved = 0)
    begin
        create TABLE #tmp_publications
        (
            publisher sysname not null, 
            dbname sysname not null, 
            publication sysname not null, 
            publisher_type sysname not null, 
            publication_type int not null,
            description nvarchar(255) null,
            allow_queued bit default 0 NOT NULL,
            enabled_for_p2p bit default 0 NOT NULL,
            enabled_for_p2pconflictdetection bit default 0 NOT NULL
        )
    end
    --
    -- Get snapshot or transactional publications
    --
    IF object_id(N'dbo.syspublications') IS NOT NULL
    BEGIN
        if (@skippalcheck = 0)
        begin
            -- Catalog accessible pub ids
            DECLARE #hC CURSOR LOCAL FAST_FORWARD FOR 
            SELECT pubid, name
            FROM dbo.syspublications

            OPEN #hC
            FETCH #hC INTO @pubid, @pubname
            WHILE (@@fetch_status <> -1)
            BEGIN
                EXEC @retcode = sys.sp_MSreplcheck_pull @publication = @pubname,
                                                @raise_fatal_error = 0,
                                                @given_login = @username
                IF (@retcode = 0 AND @@error = 0)
                BEGIN
                    INSERT INTO @accessiblepubs values(@pubid)
                END

                FETCH #hC INTO @pubid, @pubname
            END
            CLOSE #hC
            DEALLOCATE #hC
        end -- if (@skippalcheck = 0)
        --
        -- Determine if distribution db is being cataloged
        --
        IF (@category & 16 != 16)
        BEGIN
            -- SQL Server publication db
            INSERT INTO #tmp_publications
            (
                publisher,
                dbname,
                publication,
                publisher_type,
                publication_type,
                description,
                allow_queued,
                enabled_for_p2p,
                enabled_for_p2pconflictdetection
            )
            SELECT publishingservername(),
                        @dbname,
                        name,
                        N'MSSQLSERVER',
                        repl_freq,
                        description,
                        allow_queued_tran,
                        (options & @OPT_ENABLED_FOR_P2P),
                        (options & @OPT_ENABLED_FOR_P2PCONFLICTDETECTION)
            FROM dbo.syspublications
            WHERE @skippalcheck = 1 
                OR (pubid IN (SELECT pubid FROM @accessiblepubs))
        END
        ELSE
        BEGIN
            -- Distribution db - Heterogeneous publications
            INSERT INTO #tmp_publications
            (
                publisher,
                dbname,
                publication,
                publisher_type,
                publication_type,
                description,
                allow_queued,
                enabled_for_p2p
            )
            SELECT ss.srvname,
                        @dbname, -- distribution db name for enumerator to work
                        sp.name,
                        msd.publisher_type,
                        sp.repl_freq,
                        sp.description,
                        sp.allow_queued_tran,
                        (sp.options & @OPT_ENABLED_FOR_P2P)
            FROM dbo.syspublications sp
                join dbo.MSpublications msp on sp.pubid = msp.publication_id
                join master.dbo.sysservers ss on msp.publisher_id = ss.srvid
                join msdb.dbo.MSdistpublishers msd on msd.name = ss.srvname
            WHERE @skippalcheck = 1 
                OR (pubid IN (SELECT pubid FROM @accessiblepubs))
        END
    END -- IF object_id(N'dbo.syspublications') IS NOT NULL
    --
    -- Get merge publications
    --
    IF object_id(N'dbo.sysmergepublications') IS NOT NULL
    BEGIN
        INSERT INTO #tmp_publications
        (
            publisher,
            dbname,
            publication,
            publisher_type,
            publication_type,
            description
        )
        SELECT publisher,
                    publisher_db,
                    name,
                    N'MSSQLSERVER',
                    2,
                    description
        FROM dbo.sysmergepublications
        WHERE (@skippalcheck = 1 OR {fn ISPALUSER(pubid)} = 1)
            and   publisher_db = @dbname 
            and   UPPER(publisher) = UPPER(publishingservername())
    END -- object_id(N'dbo.sysmergepublications') IS NOT NULL
    --
    -- Return result set if we created local table
    --
    if (@reserved = 0)
    begin
        SELECT *
        FROM #tmp_publications
        ORDER BY publisher, dbname, publication
    end
    --
    -- all done
    --
    RETURN (0)
END

O log de erros aponta para a Linha 213, contida na seguinte consulta no procedimento:

BEGIN
            -- Distribution db - Heterogeneous publications
            INSERT INTO #tmp_publications
            (
                publisher,
                dbname,
                publication,
                publisher_type,
                publication_type,
                description,
                allow_queued,
                enabled_for_p2p
            )
            SELECT ss.srvname,
                        @dbname, -- distribution db name for enumerator to work
                        sp.name,
                        msd.publisher_type,
                        sp.repl_freq,
                        sp.description,
                        sp.allow_queued_tran,
                        (sp.options & @OPT_ENABLED_FOR_P2P)
            FROM dbo.syspublications sp
                join dbo.MSpublications msp on sp.pubid = msp.publication_id
                join master.dbo.sysservers ss on msp.publisher_id = ss.srvid
                join msdb.dbo.MSdistpublishers msd on msd.name = ss.srvname
            WHERE @skippalcheck = 1 
                OR (pubid IN (SELECT pubid FROM @accessiblepubs))
        END

Não consegui encontrar a parte da consulta responsável por este conflito de collation, o master db collation é Arabic_CI_AI ; Não consegui reproduzir os resultados desta consulta sozinho para ver exatamente o que está causando o erro.
Como posso resolver este problema?

Estou usando este script para excluir dados de todas as tabelas do meu banco de dados. Ele gera DELETEinstruções na ordem correta, cada solicitação em uma linha:

DECLARE @schema_name sysname;

DECLARE @table_name sysname;

DECLARE @constraint_name sysname;

DECLARE @constraint_object_id int;

DECLARE @referenced_object_name sysname;

DECLARE @is_disabled bit;

DECLARE @is_not_for_replication bit;

DECLARE @is_not_trusted bit;

DECLARE @delete_referential_action tinyint;

DECLARE @update_referential_action tinyint;

DECLARE @tsql nvarchar(4000);

DECLARE @tsql2 nvarchar(4000);

DECLARE @fkCol sysname;

DECLARE @pkCol sysname;

DECLARE @col1 bit;

DECLARE @action char(6) = 'DROP';  

DECLARE @referenced_schema_name sysname;



DECLARE FKcursor CURSOR FOR

     select OBJECT_SCHEMA_NAME(parent_object_id)

         , OBJECT_NAME(parent_object_id), name, OBJECT_NAME(referenced_object_id)

         , object_id

         , is_disabled, is_not_for_replication, is_not_trusted

         , delete_referential_action, update_referential_action, OBJECT_SCHEMA_NAME(referenced_object_id)

    from sys.foreign_keys

    order by 1,2;

OPEN FKcursor;

FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name

    , @referenced_object_name, @constraint_object_id

    , @is_disabled, @is_not_for_replication, @is_not_trusted

    , @delete_referential_action, @update_referential_action, @referenced_schema_name;

WHILE @@FETCH_STATUS = 0

BEGIN



      IF @action <> 'CREATE'

        SET @tsql = 'ALTER TABLE '

                  + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)

                  + ' DROP CONSTRAINT ' + QUOTENAME(@constraint_name) + ';';

    ELSE

        BEGIN

        SET @tsql = 'ALTER TABLE '

                  + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)

                  + CASE @is_not_trusted

                        WHEN 0 THEN ' WITH CHECK '

                        ELSE ' WITH NOCHECK '

                    END

                  + ' ADD CONSTRAINT ' + QUOTENAME(@constraint_name)

                  + ' FOREIGN KEY (';

        SET @tsql2 = '';

        DECLARE ColumnCursor CURSOR FOR

            select COL_NAME(fk.parent_object_id, fkc.parent_column_id)

                 , COL_NAME(fk.referenced_object_id, fkc.referenced_column_id)

            from sys.foreign_keys fk

            inner join sys.foreign_key_columns fkc

            on fk.object_id = fkc.constraint_object_id

            where fkc.constraint_object_id = @constraint_object_id

            order by fkc.constraint_column_id;

        OPEN ColumnCursor;

        SET @col1 = 1;

        FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;

        WHILE @@FETCH_STATUS = 0

        BEGIN

            IF (@col1 = 1)

                SET @col1 = 0;

            ELSE

            BEGIN

                SET @tsql = @tsql + ',';

                SET @tsql2 = @tsql2 + ',';

            END;

            SET @tsql = @tsql + QUOTENAME(@fkCol);

            SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);

            FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;

        END;

        CLOSE ColumnCursor;

        DEALLOCATE ColumnCursor;

       SET @tsql = @tsql + ' ) REFERENCES ' + QUOTENAME(@referenced_schema_name) + '.' + QUOTENAME(@referenced_object_name)

                  + ' (' + @tsql2 + ')';

        SET @tsql = @tsql

                  + ' ON UPDATE ' + CASE @update_referential_action

                                        WHEN 0 THEN 'NO ACTION '

                                        WHEN 1 THEN 'CASCADE '

                                        WHEN 2 THEN 'SET NULL '

                                        ELSE 'SET DEFAULT '

                                    END

                  + ' ON DELETE ' + CASE @delete_referential_action

                                        WHEN 0 THEN 'NO ACTION '

                                        WHEN 1 THEN 'CASCADE '

                                        WHEN 2 THEN 'SET NULL '

                                        ELSE 'SET DEFAULT '

                                    END

                  + CASE @is_not_for_replication

                        WHEN 1 THEN ' NOT FOR REPLICATION '

                        ELSE ''

                    END

                  + ';';

        END;

    PRINT @tsql;

    IF @action = 'CREATE'

        BEGIN

        SET @tsql = 'ALTER TABLE '

                  + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)

                  + CASE @is_disabled

                        WHEN 0 THEN ' CHECK '

                        ELSE ' NOCHECK '

                    END

                  + 'CONSTRAINT ' + QUOTENAME(@constraint_name)

                  + ';';

        PRINT @tsql;

        END;

    FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name

        , @referenced_object_name, @constraint_object_id

        , @is_disabled, @is_not_for_replication, @is_not_trusted

        , @delete_referential_action, @update_referential_action, @referenced_schema_name;

END;

CLOSE FKcursor;

DEALLOCATE FKcursor;  

Depois de copiar essas instruções e executá-las.
Existe alguma maneira de gerar essas instruções e executá-las em apenas uma etapa?

É possível atualizar um valor de coluna de chave primária com a atualização em cascata entre todas as chaves estrangeiras que fazem referência a ele?

# EDIT 1: Quando eu executo a consulta followinq

select * from sys.foreign_keys where referenced_object_id=OBJECT_ID('myTable') 

, vejo que update_referential_action está definido como 0. Assim, NENHUMA AÇÃO é executada após atualizar minhas colunas de chaves primárias. Como posso atualizar as chaves estrangeiras para torná-las ON CASCADE UPDATE ?

# EDIT 2:
Para criar scripts ou descartar todas as chaves estrangeiras em seu esquema, execute o seguinte script (retirado daqui )

DECLARE @schema_name sysname;

DECLARE @table_name sysname;

DECLARE @constraint_name sysname;

DECLARE @constraint_object_id int;

DECLARE @referenced_object_name sysname;

DECLARE @is_disabled bit;

DECLARE @is_not_for_replication bit;

DECLARE @is_not_trusted bit;

DECLARE @delete_referential_action tinyint;

DECLARE @update_referential_action tinyint;

DECLARE @tsql nvarchar(4000);

DECLARE @tsql2 nvarchar(4000);

DECLARE @fkCol sysname;

DECLARE @pkCol sysname;

DECLARE @col1 bit;

DECLARE @action char(6);  

DECLARE @referenced_schema_name sysname;



DECLARE FKcursor CURSOR FOR

     select OBJECT_SCHEMA_NAME(parent_object_id)

         , OBJECT_NAME(parent_object_id), name, OBJECT_NAME(referenced_object_id)

         , object_id

         , is_disabled, is_not_for_replication, is_not_trusted

         , delete_referential_action, update_referential_action, OBJECT_SCHEMA_NAME(referenced_object_id)

    from sys.foreign_keys

    order by 1,2;

OPEN FKcursor;

FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name

    , @referenced_object_name, @constraint_object_id

    , @is_disabled, @is_not_for_replication, @is_not_trusted

    , @delete_referential_action, @update_referential_action, @referenced_schema_name;

WHILE @@FETCH_STATUS = 0

BEGIN



      IF @action <> 'CREATE'

        SET @tsql = 'ALTER TABLE '

                  + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)

                  + ' DROP CONSTRAINT ' + QUOTENAME(@constraint_name) + ';';

    ELSE

        BEGIN

        SET @tsql = 'ALTER TABLE '

                  + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)

                  + CASE @is_not_trusted

                        WHEN 0 THEN ' WITH CHECK '

                        ELSE ' WITH NOCHECK '

                    END

                  + ' ADD CONSTRAINT ' + QUOTENAME(@constraint_name)

                  + ' FOREIGN KEY (';

        SET @tsql2 = '';

        DECLARE ColumnCursor CURSOR FOR

            select COL_NAME(fk.parent_object_id, fkc.parent_column_id)

                 , COL_NAME(fk.referenced_object_id, fkc.referenced_column_id)

            from sys.foreign_keys fk

            inner join sys.foreign_key_columns fkc

            on fk.object_id = fkc.constraint_object_id

            where fkc.constraint_object_id = @constraint_object_id

            order by fkc.constraint_column_id;

        OPEN ColumnCursor;

        SET @col1 = 1;

        FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;

        WHILE @@FETCH_STATUS = 0

        BEGIN

            IF (@col1 = 1)

                SET @col1 = 0;

            ELSE

            BEGIN

                SET @tsql = @tsql + ',';

                SET @tsql2 = @tsql2 + ',';

            END;

            SET @tsql = @tsql + QUOTENAME(@fkCol);

            SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);

            FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;

        END;

        CLOSE ColumnCursor;

        DEALLOCATE ColumnCursor;

       SET @tsql = @tsql + ' ) REFERENCES ' + QUOTENAME(@referenced_schema_name) + '.' + QUOTENAME(@referenced_object_name)

                  + ' (' + @tsql2 + ')';

        SET @tsql = @tsql

                  + ' ON UPDATE ' + CASE @update_referential_action

                                        WHEN 0 THEN 'NO ACTION '

                                        WHEN 1 THEN 'CASCADE '

                                        WHEN 2 THEN 'SET NULL '

                                        ELSE 'SET DEFAULT '

                                    END

                  + ' ON DELETE ' + CASE @delete_referential_action

                                        WHEN 0 THEN 'NO ACTION '

                                        WHEN 1 THEN 'CASCADE '

                                        WHEN 2 THEN 'SET NULL '

                                        ELSE 'SET DEFAULT '

                                    END

                  + CASE @is_not_for_replication

                        WHEN 1 THEN ' NOT FOR REPLICATION '

                        ELSE ''

                    END

                  + ';';

        END;

    PRINT @tsql;

    IF @action = 'CREATE'

        BEGIN

        SET @tsql = 'ALTER TABLE '

                  + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)

                  + CASE @is_disabled

                        WHEN 0 THEN ' CHECK '

                        ELSE ' NOCHECK '

                    END

                  + 'CONSTRAINT ' + QUOTENAME(@constraint_name)

                  + ';';

        PRINT @tsql;

        END;

    FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name

        , @referenced_object_name, @constraint_object_id

        , @is_disabled, @is_not_for_replication, @is_not_trusted

        , @delete_referential_action, @update_referential_action, @referenced_schema_name;

END;

CLOSE FKcursor;

DEALLOCATE FKcursor;  

Para gerar o script de chaves estrangeiras DROP, modifique o valor @action para ser igual a 'DROP' na cláusula de declaração:

DECLARE @action char(6) = 'DROP';

Desejo migrar os dados contidos em uma Tabela A do Oracle para o SQL Server. A tabela A contém uma coluna CIN com restrição única, mas vários valores nulos. O problema é que o SQL Server está rejeitando os dados. Existe alguma solução alternativa para isso?

Estou consultando dba_tablespara obter uma listagem de tabelas e sua contagem de linhas com a seguinte solicitação sql:

select * from dba_tables where owner='dbSID';

Mas noto que mesmo para algumas tabelas não vazias, a NUM_ROWScoluna está retornando valores nulos.

Alguém poderia explicar esse comportamento estranho de dba_tables?

É possível exportar todas as funções de usuários e suas permissões em objetos de esquema dentro de um banco de dados em um arquivo .sql e importá-los posteriormente em outro banco de dados?

Eu quero calcular o número de colunas com restrição não nula em meu banco de dados. Então pensei em consultar a tabela sys.all_columns:
select * from sys.all_columns

O problema é que ele me fornece informações extras de que não preciso (colunas em tabelas definidas pelo sistema).

Então eu tenho que adicionar algum código SQL para determinar se alguma coluna pertence à tabela definida pelo usuário ou não . Qual é a melhor forma de proceder?

Sou novo na administração do SQL Server e gostaria de obter o equivalente a esta consulta de administração do Oracle 9i:

select object_type,count(*) as number
from all_objects 
where owner='ownerSID'
group by object_type;

Qualquer ajuda seria muito apreciada :)

Uma chave primária e uma referência de chave estrangeira devem ter o mesmo tamanho de coluna no sql server 2008? alguém poderia me indicar um link para uma documentação explicando isso?

Obrigado pela ajuda.

Criei um link de banco de dados para um banco de dados remoto, MIGRERDOSSIER.US.ORACLE.COM, então utilizo para inserir registros em uma tabela em um banco de dados local. Aqui está a solicitação que estou tentando executar:

insert into 
    Dossier(num_dossier,indice,code_service,num_secteur,date_depot,type_dossier,SUP_DECL_ARE,SUP_DECL_CE,SUP_DECL_HE,SUP_DEF_ARE,SUP_DEF_CE,SUP_DEF_HE)
    select s.num_dossier,s.indice,s.code_service,s.num_secteur,s.date_depot,s.cod_sous_typ,s.SUP_DECL_ARE,s.SUP_DECL_CA,s.SUP_DECL_HE,s.SUP_DEF_ARE,s.SUP_DEF_CA,s.SUP_DEF_HA
    from MIGRERDOSSIER.US.ORACLE.COM@Dossier s

mas o Oracle está exibindo o erro ORA 00933: SQL command not properly ended.

Não consigo descobrir o problema com o meu pedido, qualquer ajuda seria muito apreciada.