I have spent the last couple of hours unsuccessfully trying to get my
installation to work with my MSSQL server.
First, I tried ODBC. I created the database, the DSN, and configured
ASSP to talk to the database.
I started by setting the whitelistDb to DB: and restarting. It
created a "whitelist" table in the SQL server, as such:
pkey (PK, varbinary(254))
pvalue (varbinary(255))
The table was empty, and as the server started processing mail, it
started populating this table.
However, the ASSP log started filling with this error:
Oct-14-10 12:21:56 [Worker_10000] Warning: got database error
Database select statement failed: [Microsoft][ODBC SQL Server
Driver]Invalid character value for cast specification (SQL-22018) at
assp.pl line 8242 thread 1
on table whitelist - try to reconnect
I moved the whitelist file from the database directory to the
mysql/dbimport directory (as defined by importDBDir), renamed it with
a .rpl suffix, and ran ImportMysqlDB. I saw this in the log:
d:\assp\ASSPDebug3.log/3946: Oct-14-10 13:19:53 [Main_Thread] Admin
Update: task ImportMysqlDB was queued to run
However, nothing was ever populated in the whitelist table, and the
whitelist.rpl file was never modified.
I tried changing spamdb to DB: as well, and copying the spamdb to the
import directory. ASSP created the appropriate tables, but did not
populate them.
Looking in the SQL server profiler, I saw tons of statements such as:
SET FMTONLY ON select pkey from spamdb where 1=2 SET FMTONLY OFF
and
declare @p1 int
set @p1=5
exec sp_prepexec @p1 output,N'@P1 varbinary(6)',N'select
pvalue,pfrozen from spamdb where pk...@p1',0x02
select @p1
However, the ASSP log was filled with errors like:
Oct-14-10 12:45:27 [Worker_5] Error: Fetch: Can't call method
"fetchrow_arrayref" on an undefined value at assp.pl line 44418.
- [Microsoft][ODBC SQL Server Driver]Invalid character value for
cast specification (SQL-22018)
I decided to give ADO a try, and switched it from ODBC to ADO. I
dropped the tables from the database and tried again. The tables were
created again, identically, however I got lots more errors. Looking
at the profiler, I saw it trying to run statements like this:
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,NULL,N'insert into whitelist
(pkey,pvalue) values (''[email protected]'',''1287076921'')'
select @p1
Trying to run this statement manually gives this error:
Implicit conversion from data type varchar to varbinary is not
allowed. Use the CONVERT function to run this query.
Obviously...because it's attempting to insert character data into a
varbinary column. I changed the columns in the tables from varbinary
to varchar and tried again. The errors disappeared from the ASSP log,
and I could see it running lots of queries such as this:
declare @p1 int
set @p1=21
exec sp_prepexec @p1 output,NULL,N'select pvalue from spamdb where
pkey=''rcpt razzaqi''
'
select @p1
It started populating the whitelist table as it processed mail, but
the spamdb and spamdbhelo tables were left untouched, and none of the
files in the import directory were touched, either when ASSP was
restarted or when the ImportMysqlDB task was queued.
I'm at the point where I *think* I have the database running OK with
ASSP - however, I can't get it to import any of my existing data,
which really makes the whole exercise kind of pointless. Any ideas
what I should be looking for?
------------------------------------------------------------------------------
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
Spend less time writing and rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb
_______________________________________________
Assp-test mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/assp-test
