SQL Server Duplicate product references

[AClarke]

Hello,

I'm trying to link to an external MS SQL Server database from an empty site using ODBC. When linking the external product reference to the internal one, the following error message appears:
Actinic Ecommerce was unable to determine if there are duplicate product references in the external table. Please correct the error or disable external product linking. The error message reported was:
ODBC--connection to 'systemDSN1' failed.

The external table has the product reference as its primary key and a unique index and a unique constraint on the field. It does work however when the data is imported to an MS Access table.

I'm using Actinic Developer 6.12, and the Product ID is a 20 character varchar.

Does anyone know what I need to do to use SQL Server or will I have to resort to Access? Any help would be appreciated.

Thanks

[cdicken]

As there has been no answer to this, I suggest you contact our email support team here and see if they have any ideas.

[Jan]

The error you are getting suggests that Actinic cannot connect to your SQL server, which suggests that your ODBC connection may be wrong. However you can connect to it from Access which suggests that it is ok. Is your ODBC connection defined as a user DSN or system DSN? If it is a user is might be worth trying it as a system.

Not much help I'm afraid, all I can say is that the connection to SQL server definately works, I can recall some issues with the underlying database being forced to readonly but none with ability to connect. I haven't got a SQL server here to double check with ATM.

Regards,

[AClarke]

I've tried System, User, and file DNS (actinic doesn't seem to work with those at all). The DSN connection is fine through Access and it's fine connecting through query analyser and enterprise manager, Actinic can connect to get the list of tables but just complains when I link to the Product reference field and click OK.

Thanks

[Jan]

All I can think now is that it might be permissions on the table. Have you selected the checkbox that allows you to update the table in access - if you have try it without that. Another thing to try would be to create an excel spreadsheet that links to it and try linking Actinic to that - you might get a better error message then. You could also turn on ODBC tracing from the ODBC control panel and see if that gives you any useful error messages.

Regards

[Charlie]

Actinic is trying to do something (simple) that ODBC does not like - like using SQL! I have MDAC V2.8 which is the latest so no question of having old odbc drivers!

Catalog fffb2e7d-fffb0609 ENTER SQLPrepare
HSTMT 00E92ED8
UCHAR * 0x00C99568 [ -3] "SELECT `i_ingram_partcode` FROM `ProductLinkedTest` GROUP BY `i_ingram_partcode` HAVING (((Count(`i_ingram_partcode`))>1)) \ 0"
SDWORD -3

Catalog fffb2e7d-fffb0609 EXIT SQLPrepare with return code -1 (SQL_ERROR)
HSTMT 00E92ED8
UCHAR * 0x00C99568 [ -3] "SELECT `i_ingram_partcode` FROM `ProductLinkedTest` GROUP BY `i_ingram_partcode` HAVING (((Count(`i_ingram_partcode`))>1)) \ 0"
SDWORD -3

DIAG [S1C00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented (106)

[Jan]

Well done to find the error. That is fairly standard SQL though, I have used external linking with SQL server, Sybase, Access, Foxpro, Dbase, Paradox, MYSQL and FileMaker and they could all run that SQL BUT your system is finding a problem with it for some reason.

That particular error message is often linked to data type problems. So it could be the fact that the product reference is a varchar (you are told to use varchars for strings for external linking but maybe the product reference is an exception). Worth a try anyway.

You could try running that SQL in SQL server directly and see what results you get.

Regards,

[harlequin]

i may be way off track

but it may be something simple such as having the data type for the cell/column for your product reference in the table defined as number when it needs to be text.. im sure actinic HAS to have it formatted as a text field....i think i have had that problem before although i know nothing about sql and rely on jan usually.....

steve q
harlequin domains
www.harlequindomains.com

[Charlie]

I have tried the column as varchar and char but not surprisingly it has nothing to do with that.

This is what I think is the problem:

In term of SQL (i.e. MS-SQL Server's TRANSACT-SQL):
> the single (') quotes should be double (")
> but more importantly the "\ 0" at the end of the statement is invalid!

I don't do ODBC programming so may be there are good reasons why it is like that but that is my best guess so far!

"SELECT `i_ingram_partcode` FROM `ProductLinkedTest` GROUP BY `i_ingram_partcode` HAVING (((Count(`i_ingram_partcode`))>1)) \ 0"

[Jan]

Sounds like this is one for the Actinic programmers to look into then.

I have used external linking with SQL server so there is a way to make it work, try checking the advanced settings on your ODBC setting. Check that you can run the SQL that failed from excel or something similar (correcting the "'" and removing the "\ 0" first)

FYI the "'" is generated by the C++ libraries that eventually call the ODBC libraries for SQL server. In the code it would say something like [fieldname] and in the depths of the libraries it would be changed to 'fieldname'. Thinking about it if you change it to " that would make it a text string rather than a fieldname, I think.

Regards,