Peculiar problem with seemingly identical data

Hello,

I have this peculiar problem concerning MS SQL Server.

My company works with an mailing application (ASP) which uses SQL
Server as it's repository. What I want to do is send data directly
from my own application to this SQL Server in order to feed the
mailing application.

To test if this was possible I linked the tables from SQL Server in MS
Access and entered the data. This worked fine and the data was picked
up correctly by the mailing application.

The problem occurs when I send the data from my application (Java
application with JDBC connection). The data is in this case no longer
picked up by the application. The strange thing is that the data which
is entered through Access and the data from the Application look
identical in de database view. The problem also occurs when the data
is send with the tool winSQL and when I view the data in here it still
looks identical.

Even more strange is when I select the record which is not working in
Access and copy it into a new record (only changing the key) it
suddenly works!

Has anyone have an idea how this can be?

Thanks in advance,

Sander Janssen.Sander Janssen (sjanssen@.plex.nl) writes:
> My company works with an mailing application (ASP) which uses SQL
> Server as it's repository. What I want to do is send data directly
> from my own application to this SQL Server in order to feed the
> mailing application.
> To test if this was possible I linked the tables from SQL Server in MS
> Access and entered the data. This worked fine and the data was picked
> up correctly by the mailing application.
> The problem occurs when I send the data from my application (Java
> application with JDBC connection). The data is in this case no longer
> picked up by the application. The strange thing is that the data which
> is entered through Access and the data from the Application look
> identical in de database view. The problem also occurs when the data
> is send with the tool winSQL and when I view the data in here it still
> looks identical.
> Even more strange is when I select the record which is not working in
> Access and copy it into a new record (only changing the key) it
> suddenly works!

I don't have a clue. The thing is, I have no idea by which criterias
your application is picking up data. You need to find this out one
way or another. One way to do this is to use the Profiler to catch
which SQL it is using.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Which JDBC Driver and what version of SQL Server?

"Sander Janssen" <sjanssen@.plex.nl> wrote in message
news:e3b8d8ea.0307040443.5bc4a8d@.posting.google.co m...
> Hello,
> I have this peculiar problem concerning MS SQL Server.
> My company works with an mailing application (ASP) which uses SQL
> Server as it's repository. What I want to do is send data directly
> from my own application to this SQL Server in order to feed the
> mailing application.
> To test if this was possible I linked the tables from SQL Server in MS
> Access and entered the data. This worked fine and the data was picked
> up correctly by the mailing application.
> The problem occurs when I send the data from my application (Java
> application with JDBC connection). The data is in this case no longer
> picked up by the application. The strange thing is that the data which
> is entered through Access and the data from the Application look
> identical in de database view. The problem also occurs when the data
> is send with the tool winSQL and when I view the data in here it still
> looks identical.
> Even more strange is when I select the record which is not working in
> Access and copy it into a new record (only changing the key) it
> suddenly works!
> Has anyone have an idea how this can be?
> Thanks in advance,
> Sander Janssen.|||Hello,

I have found the source of the problem. It isn't as peculiar as I
thought at first though. The problem was that I wrote a date to an
identifier field and the characters ":" were the source of the problem
in the mailing application. When I replaced them with underscores it
all works fine!

Thanks for your help anyway!

Sander Janssen.

Pausing Replication

Several times over the course of a month, our network admin team performs
necessary maintenance due to company moves and general growth. During this
time the connection between my Publisher and Distributor is disconnected. My
Subscriber and Distributor are on the same server. I have three
transactional publications set up and I would like to Pause them until the
connection is restored since I know when the connection is going down.
Is this the correct way to handle this?
From the Distributor in the enterprise manager drill down through the
replication monitor to the Distibution Agents and click on Stop Agent for
all of the publication that I have. Do I also have to Stop Agent on all of
the publications in the Log Reader Agents folder?
This also brings up the question of unplanned connection loss. If for some
reason the connection is lost late at night and I dont know about it until
the next morning do I have to recreate snapshots or re-initialize the
publication?
I would stop SQL Server agent, disable the jobs in the management folder and
then restart the agents. You should only have to stop the distributor or
merge agents.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Butler via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:4FFDFDD1EDC20@.droptable.com...
> Several times over the course of a month, our network admin team performs
> necessary maintenance due to company moves and general growth. During
> this
> time the connection between my Publisher and Distributor is disconnected.
> My
> Subscriber and Distributor are on the same server. I have three
> transactional publications set up and I would like to "Pause" them until
> the
> connection is restored since I know when the connection is going down.
> Is this the correct way to handle this?
> From the Distributor in the enterprise manager drill down through the
> replication monitor to the Distibution Agents and click on "Stop Agent"
> for
> all of the publication that I have. Do I also have to "Stop Agent" on all
> of
> the publications in the Log Reader Agents folder?
> This also brings up the question of "unplanned" connection loss. If for
> some
> reason the connection is lost late at night and I don't know about it
> until
> the next morning do I have to recreate snapshots or re-initialize the
> publication?
|||Does it matter what order I do this in?
|||no, you can disable the jobs first, which will mean that if they are
scheduled they won't run the next time they are scheduled. However if they
are in continuous mode, you must stop them before or after disabling them.
Then you should bounce SQL Server agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Butler via droptable.com" <forum@.droptable.com> wrote in message
news:4FFEBB746E630@.droptable.com...
> Does it matter what order I do this in?

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