Peculiar Replication (Could be OT)

We have 2 types of Production Database. One is an OLTP database another contains all the Master Tables. We also have 2 copies of each and this repeats itself for each country where the plant is located. Is there someone who can explain to me how we might
setup this Replication so that the Master tables which are maintained in one location gets replicated over to the other places and also the updated from those locations are also propogated to all locations. We had MS come into the picture and they have cr
eated triggers such that the data is copied over but they seem to delete the record and insert it back again for an Update and sometimes inbetween the delete and insert the leased line goes down and the result is a total failure.
Regards,
Trevor Benedict R
Trevor,
this is typically implemented using merge replication. You'll need to consider partitioning of data for each subscriber, or the posibility of conflicts and how they should be resolved. There's a good set of explanations of general concepts in BOL.
The network failures you experience are usually solved in merge by simply restarting the merge agent.
HTH,
Paul Ibison

PDF Full Text Search

Hi all,

I have a table configured to support Full-Text Search and in this tables a column stores documents of three types, Word, Excel and PDF. Everything works fine with Word and Excel documents.

In order to search PDF documents I've installed "IFilter 5.0", which is published in Acrobat's page, but nothing. What else should I do, is there any other solution?

I've read that I can create a particular filter, so the question is where can find information about creating customized filters? But first, is it a good business to create a new filter?

Thanks in advance.Dear mvargasp,
I have a problem about FTS. I think you could help me...
I wrote to forum but nobody reply to me...

I need your help,
Thanks alot
Yusuf
--------
I've a problem. I'm inexperienced about FTS.
I have a TEXT datatype field in my table and I store MS Word documents.

I want to do full text search on this TEXT datatype field.
So I build catalog and run

"Select * from table where Contains (textfield,'Yusuf')" sql in analyzer.

but null value returns. however I know the table has 3 records.

Anyway, I insert a varchar field in the table and edit my fts catalog.
and then insert 3 records in the table.than rebuild catalog and I try
"Select * from table where Contains (varcharfield,'Yusuf')"
this sql returns true records.

I know that I can FTS in TEXT datatype field but I can't. I don't know why.
I need your help.
thanks alot

Yusuf|||Hi Yusuf,

It seems that you have created a catalog and rebuild it then, but you have not populate it. So What you must do after you create or rebuild a catalog is to populate it. I order to do so, you must right click your catalog in the Enterprise Manager and then click Populate.

After this the FTS must work.

God Bless.

Password Data Types

I have recently switched my company's database over to MSS2k and all has been going fine untill i came along passwords. I was wondering if there is a password data type like there is in access (where it only shows ****** instead of PASSWORD). I was also wondering if there is a crypt command like in PHP and MySQL or am I stuck writing my own vba for that one.You have to write your own vba|||Here is a one-way encryption algorythm I wrote. You are free to use, just leave my credit in the header!

Store the encrypted password in a table, and noone will be able to unencrypt it. When someone supplies logs in, encrypt the password they supply to see if it matches the one in their user record.

blindman

----------
CREATE FUNCTION [dbo].[Encrypt_Password]
(@.RawPassword varchar(20))
Returns varchar(20)
as
BEGIN
--Function dbo.EncryptPassword
--Bruce Lindman, 11/19/2002
--
--This function returns a 20 character encryption string derived from a supplied password.
--It uses a non-linear deterministic number generation algorithm known as the Linear Congruential Method
--to generate pseudo-random numbers from the Ascii values of the password characters, and these
--random numbers are then converted back into Ascii characters to form the the encrypted string.
--Because the algorithm is non-linear and uses the password itself as the initial key value, it should be
--practically impossible to reverse engineer the process.

--These variables used for testing
--declare @.RawPassword varchar(20)
--set @.RawPassword = 'Pa$$w0rD'
--set @.RawPassword = '!!!!!!!!!!' --A low ascii value password
--set @.RawPassword = '' --A high ascii value password

declare @.counter int --we'll use this to step through the password character by character
declare @.seed decimal(10, 9) --The derived seed value for the random number generator
declare @.EncryptedPassword varchar(20)
set @.EncryptedPassword = ''
declare @.Modulo int --The divisor in the random number generator
set @.Modulo = 100000000
declare @.Multiplier int --The multiplier in the random number generator
declare @.AsciiValue numeric

--Extend the password to 20 characters by repeating it, separated by the character x
--The x character ensures that password ABC does not return the same value when doubled,
--as ABCABC, but passwords that are doubled with a padded x character will return the same
--encrypted value. ABC returns the same value as ABCxABC or ABCxABCxABC.
while datalength(@.RawPassword) < 20
begin
set @.RawPassword = @.RawPassword + 'x' + @.RawPassword
end
--I think it is unavoidable that for any function F() there exists a pair of values A, B such
--that F(A) = F(B).

--Derive the seed value for the random number function from the password itself
set @.counter = 0
set @.seed = 1
while @.counter < datalength(@.RawPassword)
begin
set @.counter = @.counter + 1
--Use the ascii value of each character to revise the seed value
set @.AsciiValue = ascii(substring(@.RawPassword, @.Counter, 1))
set @.seed = @.seed * (@.AsciiValue/1000)
--We don't want any leading zeros in our decimal value, or the seed may get too small
while @.seed < 0.1 set @.seed = @.seed * 10
end

--We'll derive the multiplier from the seed value, following the principle that a good multiplier
--should be 1 digit less than the Modulo, and should follow the pattern ...x21 where x is an even number
set @.Multiplier = round(@.seed * @.Modulo/100, 0) * 200 + 21

--Now encrypt the password
set @.counter = 0
while @.counter < datalength(@.RawPassword)
begin
set @.counter = @.counter + 1
set @.AsciiValue = ascii(substring(@.RawPassword, @.Counter, 1))
--This next statement is the guts of the random number generator
--It creates a new seed value between 0 and 1
set @.seed = cast(cast(1 + (@.seed + @.AsciiValue/1000) * @.Multiplier * @.Modulo as bigint) % @.Modulo as numeric)/@.Modulo
--Now use the first three digits of the seed value to lookup an ascii character between 1 and 255 and append it to the encrypted password
set @.EncryptedPassword = @.EncryptedPassword + char(1 + cast(round(@.seed * 1000, 0) as int) % 254)
end

Return @.EncryptedPassword
end|||This is a code snippet, I found somewhere, using one of those SQL Server un-documented password functions.

DECLARE @.ClearPIN varchar(255)
DECLARE @.EncryptedPIN varbinary(255)
SELECT @.ClearPIN = 'test'
SELECT @.EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@.ClearPIN))
SELECT @.EncryptedPIN
SELECT pwdcompare(@.ClearPIN, @.EncryptedPIN, 0)

But I would always suggest you use your own encryption routines like the one Blindman wrote.

Blindman- Thanks for sharing your code. If I ever use it, I will definitely leave the header in there.|||There are a few problems with pwdencrypt:

It is an undocumented function, so that it is not guaranteed to work the same, or at all, in future realeases. In fact, its functionality changed between version 6.5 and 7.0 and it was no longer able to recognize old passwords.

The pwdencrypt function has suffered from a Buffer Overflow vulnerability in the past. I'm not sure if this had been corrected in current patches.

The pwdencrypt function is not very secure, and has been cracked:
http://www.nextgenss.com/papers/cracking-sql-passwords.pdf

Most of the research I did recommended writing customer code rather than using pwdencrypt, so that's what I did! I have used the function I posted in several applications now and have never had an issue with it.

blindman