I found my import problem - the assp_db_import.cfg file hadn't been
copied from /docs to the assp root.
It seems to be importing now - although I have had to change all the
tables from varbinary to varchar to get it to work with ADO, as I mentioned.
At 02:04 PM 10/14/2010, Scott MacLean wrote:
>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
------------------------------------------------------------------------------
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
