RESTORE FILELISTONLY
   FROM DISK = 'c:\mssql\t2_20051222_1327.bak'
GO

-- Restore the files for KK.
RESTORE DATABASE KK
   FROM DISK = 'c:\mssql\t2_20051222_1327.bak'
   WITH REPLACE,
   MOVE 'T2SQL' TO 'C:\MsSql\Data\KK_Data.MDF', 
   MOVE 'T2SQL_log' TO 'C:\MsSql\Data\KK_Log.LDF'
GO

declare @Page int
declare @PageSize   int
set @Page = 500
set @PageSize = 10

begin
   declare @RowsCount int
   declare @FirstSelectingRowNumber int
   declare @FirstSelectingRowId  int
  
   select @RowsCount = count(Id) from table1
   set @FirstSelectingRowNumber = (@Page - 1) * @PageSize + 1
    
   if (@FirstSelectingRowNumber <= @RowsCount)
      begin
         SET ROWCOUNT @FirstSelectingRowNumber
         SELECT @FirstSelectingRowId = id
         FROM table1
         ORDER BY 1


         SET ROWCOUNT @PageSize
         SELECT * FROM table1 
         WHERE id >= @FirstSelectingRowId  
         ORDER BY 1     
      end
end

SELECT  Description, Date
FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY Date DESC)
             AS Row, Description, Date FROM LOG)
            AS LogWithRowNumbers
WHERE  Row >= 1 AND Row <= 10

DECLARE CURSOR1 CURSOR LOCAL FORWARD_ONLY FOR 
SELECT id, code, chcode from t_dealers 
WHERE filial_id=@filial_source_id
FOR UPDATE OF code, chcode
SET @Cursor=1

OPEN CURSOR1
SET @Cursor=2
 
WHILE( @Cursor=2 ) 
BEGIN 
  FETCH NEXT FROM CURSOR1 INTO @id, @intcode, @strcode
  IF @@fetch_status=-1 BREAK
  IF @@fetch_status=-2 CONTINUE
  ...	
  SET @strcode = RIGHT('000' + RTRIM(STR(@intcode)),4)

  UPDATE t_dealers SET code=@intcode, chcode=@strcode
	WHERE CURRENT OF CURSOR1

END 
CLOSE CURSOR1
DEALLOCATE CURSOR1
SET @Cursor=0

• like '5[_]' - будут выбираться строки, содержащие '5_', а не '51', '5a' и т.д.;
• like'ffff[%]' - будут выбираться строки, содержащие 'ffff%', а не 'ffffaaaa', 'ffff1' и т.д.;
• like 'MB_an[_]' - будут выбираться строки, содержащие 'MB'+ любой символ+'an_'.
• like '%[[]%' - будут выбираться строки, содержащие '['
• like '%]%' - будут выбираться строки, содержащие ']'
• like '%[[]]%' - будут выбираться строки, содержащие '[]'

DECLARE @history_id  AS INT
BEGIN TRAN
DELETE FROM tblMember WHERE intMemberId=@id 
SET @history_id = @@IDENTITY
UPDATE tblmember_history SET vchUpdatedByWho = @user WHERE id = @history_id
COMMIT TRAN

BACKUP LOG BaseName WITH TRUNCATE_ONLY 
GO
DBCC SHRINKDATABASE( 'BaseName')
GO

CREATE FUNCTION dbo.GetTableVarchar(@Data image)
 
RETURNS @DataTable TABLE (RowID int primary key IDENTITY , 
                                          Value Varchar(8000)) 
AS
BEGIN
      --First Test the data is of type Varchar.
      IF(dbo.ValidateExpectedType(103, @Data) != 1) RETURN
 
      --Loop thru the list inserting each
      -- item into the variable table.
      DECLARE @Ptr int, @Length int, 
         @VarcharLength smallint, @Value Varchar(8000)
      SELECT @Length = DataLength(@Data), @Ptr = 2
      WHILE(@Ptr < @Length)
      BEGIN
            --The first 2 bytes of each item is the length of the 
            --varchar, a negative number designates a null value.
            SET @VarcharLength = SUBSTRING(@Data, @ptr, 2)
            SET @Ptr = @Ptr + 2
            IF(@VarcharLength < 0)
                  SET @Value = NULL
            ELSE
            BEGIN
               SET @Value = SUBSTRING(@Data, @ptr, @VarcharLength)
               SET @Ptr = @Ptr + @VarcharLength
            END
            INSERT INTO @DataTable (Value) VALUES(@Value)
      END
      RETURN
END

CREATE PROCEDURE GetSnailDetails 
 @SnailIDs   image
AS
SELECT Snail.* 
FROM dbo.GetTableInt(@SnailIDs) SIDs 
INNER JOIN Snails ON SIDs.Value = Snails.SnailID
GO

CREATE FUNCTION dbo.CharToInt (
 @data varchar(8000) 
)  
RETURNS @Table TABLE (ind int primary key IDENTITY, value int) 
AS  
BEGIN 

    DECLARE @Value int
    DECLARE @I int
    DECLARE @J int
    DECLARE @L int
    DECLARE @ch char(1)
  
    SET @I=1
    SET @L=LEN(@data)
    WHILE( @I <= @L )
	BEGIN
       SET @J=@I 
	   WHILE( @I-@J < 16 )
	   BEGIN
	    SET @ch = SUBSTRING(@data,@I,1) 
		IF @ch<'0'OR @ch>'9' BREAK 
		SET @I= @I+1
		IF @I > @L BREAK		        	
	   END
	   SET @value= CONVERT(int,SUBSTRING(@data,@J,@I-@J))	

	   INSERT INTO @Table (Value) VALUES(@Value)
	   SET @I=@I+1
    END
     RETURN  
END

CREATE PROCEDURE Test 
 @users varchar(8000) 
AS
 select * INTO #users from dbo.chartoint(@users)
 
GO

• IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
• SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

declare @id int
declare @pos int
declare @cnt int
declare @rows int
declare @PtrVal varbinary(16)
DECLARE @Len int
DECLARE @Size int


CREATE TABLE #Tmp(
  ind int IDENTITY,
  m_id uniqueidentifier,	
  msg text
)  

insert into #Tmp(m_id,msg) 
select m_id, XM_XH_TEXT from XT
where XM_XH_TEXT like '%<cut />%'
set @rows = @@rowcount

set @id = 1
while( @id <= @rows )
begin
  set @cnt = 0
  while( 1 = 1  )
  begin 
  	SELECT @Len= DATALENGTH(msg)-1, 
			@PtrVal = TEXTPTR(msg), 
				@Pos = PATINDEX( '%<cut/>%', msg )
	FROM #Tmp WHERE  ind= @id
	if @pos < = 0 break
    set @pos = @pos - 1 	
    UPDATETEXT #Tmp.msg @PtrVal @pos 7
    set @cnt = @cnt + 1   
  end 
  break

  set @id = @id + 1
end 

update XT set XT.XM_XH_TEXT = #Tmp.Msg
from XT
inner join #Tmp on #Tmp.m_id = XT.m_id

CREATE PROCEDURE dbo.read_Flash
 @Id int
AS
BEGIN

DECLARE @PtrVal varbinary(16)
DECLARE @Buf varchar(200)
DECLARE @Len int
DECLARE @Size int

CREATE TABLE #Tmp(
  ind int IDENTITY,
  msg text
)  

CREATE TABLE #Msg(
  ind int IDENTITY,
  msg varchar(200)
)

INSERT #Tmp(msg) SELECT fl_text FROM ut_flash WHERE fl_id=@Id
IF @@rowcount=0 GOTO ERREXIT

SELECT @Len=DATALENGTH(msg)-1, @PtrVal=TEXTPTR(msg) 
FROM #Tmp WHERE ind=1 

WHILE(@Len>0 )
BEGIN
  INSERT #Msg(msg)
  SELECT SUBSTRING(CONVERT(char(200),msg),1,200) FROM #Tmp WHERE ind=1
  
  SELECT @Size=CASE WHEN @Len-200 < 0 THEN @Len ELSE 200 END
  UPDATETEXT #Tmp.msg @PtrVal 0 @Size
  SELECT @Len=@Len-200
END

 SELECT msg FROM #Msg
 RETURN 0
 
ERREXIT:
 SELECT NULL
 RETURN -1
END

DECLARE @tableId int
SET @tableId = (SELECT id FROM sysobjects
WHERE name = 'tblSystem' and type=  'U')
IF @tableId > 0
BEGIN
	IF EXISTS( SELECT id FROM syscolumns WHERE name = 'intRegionCityId' AND id=@tableId )
	BEGIN
		ALTER TABLE tblSystem DROP COLUMN intRegionCityId
		ALTER TABLE tblSystem_History DROP COLUMN intRegionCityId
		ALTER TABLE tblSystem_Archive DROP COLUMN intRegionCityId
	END
END	
ELSE
BEGIN
	PRINT 'Не найдены колонки'
END

SELECT xm_client_id, CONVERT(bit, 0) AS IsOK INTO #client FROM xt_finaccount

SELECT    
DATALENGTH(xm_data), 
SQL_VARIANT_PROPERTY(xm_data,'BaseType'),
SQL_VARIANT_PROPERTY(xm_data,'TotalBytes'),
xm_data
FROM dbo.XT_DC_DATA

INSERT pt_page SELECT * FROM ut_page

DECLARE @Lvl int

  CREATE TABLE #Nodes
  (
    ind int IDENTITY(1,1),
    lvl	 int default(0),
    node_id   int,
    parent_id int
  )

   SET @Lvl =0 
   INSERT INTO	 #Nodes(lvl,node_id,parent_id) 
   SELECT @Lvl, id, parent_id FROM ut_sort
   WHERE id = @Id
   WHILE( @Lvl > = 0 )
   BEGIN
       INSERT INTO #Nodes(lvl,node_id,parent_id)
       SELECT @Lvl+1, id, parent_id FROM ut_sort 
	   	WHERE parent_id IN (SELECT node_id FROM #Nodes WHERE Lvl=@Lvl)
       IF @@rowcount=0 BREAK
       SET @Lvl=@Lvl+1
   END 
   
   SELECT * FROM #Nodes

-- Список ролей
insert into #r(role_id)
select role_id from uf_roles(@roles)

-- Права на сущность
set @rt = 
(select 
	CASE WHEN SUM(CASE WHEN ut_table_role.tb_right & 0x01 = 0x01 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x01 END +
	CASE WHEN SUM(CASE WHEN ut_table_role.tb_right & 0x02 = 0x02 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x02 END +
	CASE WHEN SUM(CASE WHEN ut_table_role.tb_right & 0x04 = 0x04 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x04 END +
	CASE WHEN SUM(CASE WHEN ut_table_role.tb_right & 0x08 = 0x08 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x08 END +
	CASE WHEN SUM(CASE WHEN ut_table_role.tb_right & 0x10 = 0x10 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x10 END +
	CASE WHEN SUM(CASE WHEN ut_table_role.tb_right & 0x20 = 0x20 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x20 END +
	CASE WHEN SUM(CASE WHEN ut_table_role.tb_right & 0x40 = 0x40 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x40 END +
	CASE WHEN SUM(CASE WHEN ut_table_role.tb_right & 0x80 = 0x80 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x80 END as tb_right 

from ut_table_role
inner join ut_table on ut_table.id = ut_table_role.table_id
where ut_table.tb_name = 'ut_folder' and ut_table_role.role_id in (select role_id from #r))

-- Права для родительской папки
if @parent_id is not null
begin
set @id = @parent_id
while( @id is not null )
begin
	insert into #p(fd_id,fd_allow,fd_deny) 
	select 
		page_role.folder_id,
	
	CASE WHEN SUM(CASE WHEN page_role.fd_allow & 0x01 = 0x01 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x01 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_allow & 0x02 = 0x02 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x02 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_allow & 0x04 = 0x04 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x04 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_allow & 0x08 = 0x08 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x08 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_allow & 0x10 = 0x10 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x10 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_allow & 0x20 = 0x20 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x20 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_allow & 0x40 = 0x40 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x40 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_allow & 0x80 = 0x80 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x80 END as fd_allow,
	
	CASE WHEN SUM(CASE WHEN page_role.fd_deny & 0x01 = 0x01 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x01 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_deny & 0x02 = 0x02 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x02 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_deny & 0x04 = 0x04 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x04 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_deny & 0x08 = 0x08 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x08 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_deny & 0x10 = 0x10 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x10 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_deny & 0x20 = 0x20 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x20 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_deny & 0x40 = 0x40 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x40 END +
	CASE WHEN SUM(CASE WHEN page_role.fd_deny & 0x80 = 0x80 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x80 END as fd_deny
	from ut_folder_role page_role
	where page_role.folder_id = @id
		and page_role.role_id in (select role_id from #r)
	group by page_role.folder_id
    
	set @id = (select parent_id from ut_folder where id=@id)
end 

-- Спускаюсь вниз для определения прав 
set @ind = isnull((select max(ind) from #p),0)
while( @ind > 0 )
begin  
	set @rt =    
	(select (@rt | fd_allow) & ~(fd_deny)  from #p where ind = @ind)
	set @ind = @ind - 1 
end 
	
truncate table #p

-- Создаю список страниц	
insert into #p(fd_id,fd_allow,fd_deny) 
select 
	page.id,

ISNULL(CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_allow,0) & 0x01 = 0x01 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x01 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_allow,0) & 0x02 = 0x02 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x02 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_allow,0) & 0x04 = 0x04 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x04 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_allow,0) & 0x08 = 0x08 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x08 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_allow,0) & 0x10 = 0x10 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x10 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_allow,0) & 0x20 = 0x20 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x20 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_allow,0) & 0x40 = 0x40 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x40 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_allow,0) & 0x80 = 0x80 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x80 END,0) as fd_allow,

ISNULL(CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_deny,0) & 0x01 = 0x01 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x01 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_deny,0) & 0x02 = 0x02 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x02 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_deny,0) & 0x04 = 0x04 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x04 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_deny,0) & 0x08 = 0x08 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x08 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_deny,0) & 0x10 = 0x10 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x10 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_deny,0) & 0x20 = 0x20 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x20 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_deny,0) & 0x40 = 0x40 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x40 END +
CASE WHEN SUM(CASE WHEN ISNULL(page_role.fd_deny,0) & 0x80 = 0x80 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE 0x80 END,0) as fd_deny
from ut_folder page
left join ut_folder_role page_role on page_role.folder_id = page.id 
	and page_role.role_id in (select role_id from #r)
where page.parent_id = @parent_id 
group by page.id

set nocount off

select id, isnull(t0.parent_id,0) as parent_id, t0.fd_name, 
		(select count(*) from ut_folder t1 where t1.parent_id=t0.id) as fd_count,  
			((@rt | #p.fd_allow) & ~(#p.fd_deny))  as fd_right,
				t0.fd_isSystem, t0.fd_isVisible
	from ut_folder t0 
	inner join #p on #p.fd_id = t0.id
		where t0.parent_id = @parent_id
			order by t0.fd_name

CREATE FUNCTION dbo.uf_roles (@role as varchar(1000))  
RETURNS 
 @RoleTable TABLE (ind int primary key IDENTITY,  role_id int) 
AS  
BEGIN 
 declare @I int
 declare @J int
 declare @L int
 declare @role_id int
 declare @ch char(1)
 declare @str varchar(16)
 SELECT @J=1, @L=DATALENGTH(RTRIM(@role))
 WHILE( @J <= @L ) 
 BEGIN
	  SET @I= @J
	  WHILE( @J <= @L ) 
	  BEGIN
		 SET @ch=SUBSTRING(@role,@J,1)
		 IF @ch = ',' BREAK
		 SET @J=@J+1
	  END
 	  IF @ch = ',' 
		  SET @str=SUBSTRING(@role,@I,@J-@I)
	  ELSE
		  SET @str=SUBSTRING(@role,@I,@J-@I+1)
	  
	  SET @role_id=CONVERT(int,@str)
	  IF @role_id > 0 
		INSERT INTO @RoleTable (role_id) VALUES(@role_id)
	  SET @J=@J+1
 END
 RETURN
END

SELECT RIGHT('000' + LTRIM(STR(1)),4)

SELECT REPLACE(STR(1,4),' ','0')

SET @intcode = ISNULL(SELECT MIN(code-1) FROM t_dealers t1 
WHERE t1.filial_id = @filial_target_id AND
		not exists(SELECT * FROM t_dealers t2 WHERE t2.code=t1.code-1 AND filial_id=@filial_target_id),1)
IF @intcode = 0 
BEGIN
    SET  @intcode =ISNULL(SELECT MIN(code+1),1) FROM t_dealers t1 
	WHERE t1.filial_id = @filial_target_id AND
			not exists(SELECT * FROM t_dealers t2 WHERE t2.code=t1.code+1  AND filial_id=@filial_target_id),1)

END

Create Procedure dbo.xxsp_currate_add
	@xm_currency_id uniqueidentifier,
	@xm_publication_id uniqueidentifier = NULL,
	@xm_datefrom datetime,
	@xm_ratio float = 1,	
	@xm_basecurrencyvalue float,
	@xm_currencyvalue float,
	@recid uniqueidentifier = null output

As
begin

DECLARE @tran int
DECLARE @err int SET @err = -1

IF @@NESTLEVEL = 1 SET NOCOUNT ON

SET @tran = @@TRANCOUNT
IF @tran =0
	BEGIN TRAN currate_add
ELSE
	SAVE TRAN currate_add

...	
	
INSERT ...
IF @@rowcount=0 GOTO ERR_RETURN
   
...   

OK_RETURN:
	IF @tran = 0 
		COMMIT TRAN currate_add
	IF @@NESTLEVEL = 1
		SELECT 0 AS Error
	RETURN 0

ERR_RETURN:
	ROLLBACK TRAN currate_add
	IF @@NESTLEVEL = 1
		SELECT @err AS Error
	RETURN @err
end

exec sp_addlinkedserver
 'DBF_PGB',
 'Jet 4.0',
 'Microsoft.Jet.OLEDB.4.0',
 'c:\work\pgb',
 NULL,
 'dBase IV'
go

exec sp_addlinkedsrvlogin
 @rmtsrvname = 'DBF_PGB',
 @useself = false,
 @locallogin = NULL,
 @rmtuser = NULL,
 @rmtpassword = NULL
go

exec sp_tables_ex N'DBF_PGB'

SELECT * FROM DBF_PGB...BNKSEEK
go

exec sp_droplinkedsrvlogin
 @rmtsrvname = 'DBF_PGB', 
 @locallogin = NULL
go

exec sp_dropserver 
 @server = 'DBF_PGB'
go

SELECT * FROM OPENROWSET(
 'MSDASQL',
 'Driver=   {MicrosoftVisualFoxProDriver};SourceType=DBF;SourceDb=c:\work\pgb\BNKSEEK.DBF;',
 'select * from c:\work\pgb\BNKSEEK.DBF'
) as dbf
		 
SELECT * FROM OPENROWSET(
 'MSDASQL', 
 'Driver=Microsoft Excel Driver (*.xls);DBQ=c:\ramsql7', 
 'SELECT * FROM [sheet1$]'
) as xls

1. Выполните представленный ниже скрипт на SQL сервере с которого необходимо перенести логины. Этот скрипт создаёт два хранимых процедуры с именами sp_hexadecimal и sp_help_revlogin в системной базе данных master. После успешного исполнения скрипта, выполните операции из пункта 2.

ОБРАТИТЕ ВНИМАНИЕ: Создаваемые в процессе исполнения скрипта процедуры напрямую оперируют с системными таблицами SQL Server. Структура этих таблиц может изменяться от версии к версии SQL Server, что может повлиять на работоспособность этого скрипта.

----- Begin Script, Create sp_help_revlogin procedure -----

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin 
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name    sysname
DECLARE @xstatus int
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name = @login_name
OPEN login_curs 
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs 
  DEALLOCATE login_curs 
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script ' 
PRINT @tmpstr
SET @tmpstr = '** Generated ' 
  + CONVERT (varchar, GETDATE()) + ' on ' + @@ServerNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr 
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
      IF (@xstatus & 1) = 1
      BEGIN -- NT login is denied access
        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
        PRINT @tmpstr 
      END
      ELSE BEGIN -- NT login has access
        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
        PRINT @tmpstr 
      END
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        PRINT @tmpstr
	EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN 
        -- Null password
	EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      IF (@xstatus & 2048) = 2048
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
      ELSE 
        SET @tmpstr = @tmpstr + '''skip_encryption'''
      PRINT @tmpstr 
    END
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  END
CLOSE login_curs 
DEALLOCATE login_curs 
RETURN 0
GO

----- End Script -----

2. После того, как будет создана хранимая процедура sp_help_revlogin, запустите эту процедуру в Query Analyzer на исходном сервере:

   EXEC master..sp_help_revlogin

Хранимая процедура sp_help_revlogin может использоваться и на SQL Server 7.0 и на SQL Server 2000. Результат, выводимый sp_help_revlogin, представляет собой готовый скрипт, который создаёт логины с оригинальными SID и паролям. Сохраните выведенный в окно результатов исполнения скрипта текст, и затем выполните его как скрипт в Query Analyzer на том SQL сервере,куда необходимо перенести логины

BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_ABC
	(
 	A1 int NOT NULL,
	A2 char(2) NULL
	) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.ABC)
	 EXEC('INSERT INTO dbo.Tmp_ABC(A1, A2)
		SELECT A1, A2 FROM dbo.ABC TABLOCKX')
DROP TABLE dbo.ABC
EXECUTE sp_rename 'dbo.Tmp_ABC', 'ABC'
COMMIT

-- примерный скрипт
update sysobjects set status=значение where  name='ИмяТаблицы'
update syscolumns set autoval=NULL where  name='ИмяПоля'  
	and  OBJECT_ID('ИмяТаблицы')=id

IF NOT IDENT_SEED('ИмяТаблицы') IS NULL
        SET IDENTITY_INSERT ИмяТаблицы ON

IF NOT IDENT_SEED('ИмяТаблицы') IS NULL
        SET IDENTITY_INSERT ИмяТаблицы OFF

with [recursive] <имя_алиаса_запроса> [ (<список столбцов>) ]
as (<запрос>)
<основной запрос>

В MS SQL нет ключевого слова recursive, а в остальном все тоже самое. Такой синтаксис поддерживается в DB2, Sybase iAnywhere, MS SQL и во всех базах данных, которые поддерживают стандарт SQL 99.

Проще разобрать на примере. Предположим, есть таблица:

create table tree_sample (
  id integer not null primary key,
  id_parent integer foreign key references tree_sample (id),
  nm varchar(31) )

id – идентификатор
id_parent – ссылка на родитель
nm – название.

Для вывода дерева:

with recursive tree (nm, id, level, pathstr)
as (select nm, id, 0, cast(`` as text)
   from tree_sample
   where id_parent is null
union all
   select tree_sample.nm, tree_sample.id, t.level + 1, tree.pathstr + tree_sample.nm
   from tree_sample
     inner join tree on tree.id = tree_sample.id_parent)
select id, space( level ) + nm as nm
from tree
order by pathstr

Этот пример выведет дерево по таблице с отступами. Первый запрос из tree_sample этот запрос выдаст все корни дерева. Второй запрос соединяет между собой таблицу tree_sample и tree, которая определяется этим же запросом. Этот запрос дополняет таблицу узлами дерева.

Сначала выполняется первый запрос. Потом к его результатам добавляются результаты второго запроса, где данные таблица tree – это результат первого запроса. Затем снова выполняется второй запрос, но данные таблицы tree – это уже результат предыдущего выполнения второго запроса. И так далее. На самом деле база данных работает не совсем так, но результат будет таким же, как результат работы описанного алгоритма.

После этого данные этой таблицы можно использовать в основном запросе как обычно.

Хочу заметить, что я не говорю о применимости конкретно этого примера, а лишь пишу его для демонстрации возможностей рекурсивных запросов. Этот запрос реально будет работать достаточно медленно из-за order by.

CREATE TABLE #Tabl (
    CntrId int,	   
    data   datetime,
    nomer  char(10),
    type   char(1) COLLATE SQL_Latin1_General_CP1251_CI_AS ,
    doc	   char(1)  COLLATE SQL_Latin1_General_CP1251_CI_AS,
)

INNER JOIN DOC d ON 
  d.subcod COLLATE Cyrillic_General_CI_AS =#Tabl.type  
  AND d.cod COLLATE Cyrillic_General_CI_AS =#Tabl.doc

SELECT CntrId, c.NAME COLLATE Cyrillic_General_CI_AS

declare @inp varchar(1000)
SET @inp = 'Иванов, Петров, Сидоров, Дудкин, Пупкин, Билли, Боря'
declare @s varchar(1000)
set @s='select ltrim('''+ replace(@inp, ',', ''') name union all select ltrim(''')+''')'
print(@s)
exec (@s)

select 
(select ', ' + name from dbo.T_User_City  
where dbo.T_User_City.CountryId = dbo.T_User_Country.Id for xml path('') ) as city, name
from dbo.T_User_Country