T-SQL

Re: DROP constrangere (SQL 2000 si SQL 2005)

sorineatza — Wed, 27 Aug 2008 13:35:49 GMT

nasol...

stiu ca s-a scris, dar inca mai speram ca exista o solutie.

eu am o replicare intre 2 bd si, uneori, o anumita tabela trebuie s-o modific deoarece sincronizarea imi da fail (duplicate index etc...). pe langa tabela respectiva mai trebuie sa modific si "tabela" sys.sysusers" pentru a corespunde la uid cu tabela mea de useri.

Re: DROP constrangere (SQL 2000 si SQL 2005)

B_gd_n[ ]Sahlean — Wed, 27 Aug 2008 13:10:17 GMT

S-a scris tot in acest topic aici !

[1], [2]

Re: DROP constrangere (SQL 2000 si SQL 2005)

sorineatza — Wed, 27 Aug 2008 12:56:42 GMT

nu merge.... am incercat in cazul meu si nimic....

DECLARE @T TABLE (I INT IDENTITY(1,1) PRIMARY KEY, OrderID INT, SqlStatement NVARCHAR(4000));

INSERT INTO @T(SqlStatement,OrderID)

SELECT

'update sys.sysusers set uid=546 where uid=318'

AS SqlStatement,

2 AS OrderID

DECLARE @v NVARCHAR(4000);

SELECT @v=SqlStatement FROM @T WHERE I=1;

EXECUTE @v;

PRINT @v;

imi da eroare si nu pot actualiza sys.sysusers

Re: DROP constrangere (SQL 2000 si SQL 2005)

xmldeveloper — Mon, 25 Feb 2008 21:12:49 GMT

Gasisem si eu eroarea de JOIN insa ai fost mai rapid. Nu testasem initial.

Re: DROP constrangere (SQL 2000 si SQL 2005)

cretzu — Mon, 25 Feb 2008 09:45:16 GMT

Merci mult Cristian pentru tamplate-ul de m sus .

Azi am avut timp sa ma uit peste el si sa il folosesc pe o BD exemplu si l-am schimbat un picutz ... Stergea toate cheile dar de adaugat nu mai adauga pentru ca era ceva eroare in conditia de JOIN. Pun m jos ultimele modificari ca sa fie totusi o versiune functionala si pentru cei pe care ii intereseaza problema asta.

In loc de

JOIN sys.columns sc
ON f.[parent_object_id]=sc.[object_id] AND f.referenced_column_id = sc.column_id

am pus

JOIN sys.columns sc

ON f.[referenced_object_id]=sc.[object_id] AND f.referenced_column_id = sc.column_id

iar in while-ul care executa toate instructiunile gasite in tabelul temporar in loc de -- EXECUTE @v;

am pus

execute sp_executesql @v

Te rog sa ma corectati daca am gresit undeva sau nu am inteles bine codul.

Daniel.

PS. Multumesc si lui Andrei si Dianei pentru ideile oferite.

PPS. Voi incerca cand o sa am in pic de timp liber sa transcriu si in SQL 2000

Re: DROP constrangere (SQL 2000 si SQL 2005)

xmldeveloper — Wed, 20 Feb 2008 11:14:20 GMT

Eu as face cu cursor insa daca vreau neaparat sa evit cursorul si sunt "system administrator" atunci pot executa urmatorul script:

DECLARE @T TABLE (I INT IDENTITY(1,1) PRIMARY KEY, ORDERID INT, SqlStatement NVARCHAR(MAX));

INSERT INTO @T(SqlStatement,OrderID)
SELECT
  'ALTER TABLE ' +
  OBJECT_SCHEMA_NAME(parent_object_id)+ '.' +
  OBJECT_NAME(parent_object_id) +
  ' DROP CONSTRAINT ' + name AS SqlStatement,
  1 AS ORDERID
FROM sys.foreign_keys
UNION
SELECT
  'ALTER TABLE ' +
  OBJECT_SCHEMA_NAME(f.parent_object_id)+ '.' +
  OBJECT_NAME(f.parent_object_id) +
  ' ADD CONSTRAINT FK_' +
  OBJECT_NAME(f.parent_object_id) +
  '_' +
  OBJECT_NAME(f.referenced_object_id) +
  '_' +
  c.name + ' FOREIGN KEY (' + c.name + ') REFERENCES ' +
  OBJECT_SCHEMA_NAME(f.referenced_object_id)+ '.' +
  OBJECT_NAME(f.referenced_object_id) + '(' + sc.name + ')' +
  CASE delete_referential_action
    WHEN 0 THEN ''
    WHEN 1 THEN ' ON DELETE CASCADE'
    WHEN 2 THEN ' ON DELETE SET NULL'
    WHEN 3 THEN ' ON DELETE SET DEFAULT'
  END +
  CASE update_referential_action
    WHEN 0 THEN ''
    WHEN 1 THEN ' ON UPDATE CASCADE'
    WHEN 2 THEN ' ON UPDATE SET NULL'
    WHEN 3 THEN ' ON UPDATE SET DEFAULT'
  END
  AS SqlStatement,
  2 AS ORDERID
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns f
ON fk.object_id = f.constraint_object_id
JOIN sys.columns c
ON f.[parent_object_id]=c.[object_id] AND f.constraint_column_id = c.column_id
JOIN sys.columns sc
ON f.[parent_object_id]=sc.[object_id] AND f.referenced_column_id = sc.column_id
ORDER BY 2

DECLARE @j INT;
DECLARE @max INT;
DECLARE @v NVARCHAR(MAX);
SELECT @max = COUNT(*) FROM @T;
SET @j = 1;
WHILE @j < @max + 1
BEGIN
SELECT @v=SqlStatement FROM @T WHERE I=@j;
-- EXECUTE @v;
PRINT @v;
SET @j=@j+1;
END

Atentie e doar un template pentru SQL Server 2005.

Re: DROP constrangere (SQL 2000 si SQL 2005)

Diana — Tue, 19 Feb 2008 13:19:47 GMT

Se pare ca asta e unul dintre cazurile in care cursorul e "un rau necesar"...Pentru ceva mai multa eficienta, se poate folosi un cursor "FAST_FORWARD".

Re: DROP constrangere (SQL 2000 si SQL 2005)

ignatandrei — Tue, 19 Feb 2008 13:10:05 GMT

Diana:
Andrei, ceea ce propui tu nu este o buna practica.

Daniel, o cale de a rezolva acest gen de probleme este sa selectezi numele obiectelor (din tabele sistem in SQL 2000 sau din "catalog views" / "dm views" in SQL 2005 sau din "information schema views" ) intr-un cursor (eventual FAST_FORWARD) si sa aplici "sp_rename" pentru fiecare.

Sunt de acord cu ce zici ... dar omul vroia fara cursor ...

Re: DROP constrangere (SQL 2000 si SQL 2005)

cretzu — Tue, 19 Feb 2008 12:48:12 GMT

Merci Diana,
M-am prins ca nu e la fix .... dar incercam acu sa il inteleg si sa vad cam cum pot sa il aplic la mine ...
O sa incerc pe serverul de test si ce mi-a dat Andrei si va scriu. Oricum abordare Dianei imi prieste m mult

Re: DROP constrangere (SQL 2000 si SQL 2005)

Diana — Tue, 19 Feb 2008 12:44:28 GMT

Exemplul precedent nu e tocmai "la fix", dar sper ca te ajuta sa vezi "cam cum" se poate aborda problema.

Re: DROP constrangere (SQL 2000 si SQL 2005)

Diana — Tue, 19 Feb 2008 12:33:12 GMT

Andrei, ceea ce propui tu nu este o buna practica.

Daniel, o cale de a rezolva acest gen de probleme este sa selectezi numele obiectelor (din tabele sistem in SQL 2000 sau din "catalog views" / "dm views" in SQL 2005 sau din "information schema views" ) intr-un cursor (eventual FAST_FORWARD) si sa aplici "sp_rename" pentru fiecare.

Am la indemana un exemplu care aplica "rebuild" sau "reorganize" pentru indecsii dintr-o baza, in functie de valoarea campului avg_fragmentation_in_percent din sys.dm_db_index_physical_stats. N-o fi "perfect", dar functioneaza (pe SQL 2005, nu si pe 2000)...

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname sysname;

DECLARE @objectname sysname;

DECLARE @indexname sysname;

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command varchar(8000);

-- ensure the temporary table does not exist

IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')

DROP TABLE work_to_do;

-- conditionally select from the function, converting object and index IDs to names.

SELECT

object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

INTO work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

WHILE @@FETCH_STATUS = 0

BEGIN;

SELECT @objectname = o.name, @schemaname = s.name

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = name

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding

IF @frag < 30.0

BEGIN;

SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';

IF @partitioncount > 1

SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);

EXEC (@command);

END;

IF @frag >= 30.0

BEGIN;

SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD';

IF @partitioncount > 1

SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);

EXEC (@command);

END;

PRINT 'Executed ' + @command;

FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;

END;

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- drop the temporary table

IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')

DROP TABLE work_to_do;

END

Re: off topic

ignatandrei — Tue, 19 Feb 2008 11:46:32 GMT

cretzu:
exista o aplicatie la care dam alte versiuni impreuna cu scripturile aferente versiunii

Super... modifica aplicatia( are auto update?) astfel incit sa mai aiba un buton cu un text "frumos", de ex. "Reorganizare BD" si pe click sa faca single user, redenumire, restart sql server
Apropo : merge cu single user update-ul respectiv?

Re: off topic

cretzu — Tue, 19 Feb 2008 11:28:53 GMT

exista o aplicatie la care dam alte versiuni impreuna cu scripturile aferente versiunii

Re: off topic

ignatandrei — Tue, 19 Feb 2008 11:16:03 GMT

cretzu:
dar chestia asta cu single user ... cum as putea sa o fac la client ? daca nu am remote acolo ??

Ai vreo aplicatie downloadata la client ?sau numai BD?

Re: off topic

cretzu — Tue, 19 Feb 2008 10:56:26 GMT

dar chestia asta cu single user ... cum as putea sa o fac la client ? daca nu am remote acolo ??