Use Parameterized Queries
By default, if this is not checked, the SQL statement is processed as a string, with the data being inserted into the SQL statement, which is passed directly to SQL Server as a string. This works well, unless literal company and contact names are passed, which may include a quotation mark, which makes the SQL statement invalid.
The solution to this is to use the Parameterized Queries option, which will pass data to SQL properly parameterized.
Note that the SQL Webstore cannot know the right thing to do, because the flexibility in SQL is great. Consider:
Notes = '%SHIPPINGSERVICE% TRK:%TRACKINGNUMBER% Cost: %SHIPPINGCHARGES%',
The above will only work if the SQL is not parameterized, as it assumes the SQL is being built by the SQL Webstore itself. The above approach will not work if data elements may break the SQL statement.
The way to accomplish the above and use parameters, is as follows:
declare @Notes varchar(50);set @Notes = '%SHIPPINGSERVICE%' + ' TRK:' + '%TRACKINGNUMBER%' + ' Cost: ' + '%SHIPPINGCHARGES%';
The above will work when set to Parameterized.
Preprocess Data
This option will strip quotation characters from the data, so that a non-parameterized approach can be used. Note that data from the SQL Webstore (e.g., O'Neil) will be modified (in this case to: ONeil).
Data Types
If parameterized queries are NOT used: For numeric data types, the string representation will be used going to and from the SQL Webstore. For example, a SQL Server Numeric type must be declared in the database with appropriate precision.
For saving the shipment cost, a default numeric declaration will cause truncation of data after the decimal place, as the SQL Webstore will pass a string of the cost, and SQL Server will truncate off the digits after the decimal place when storing the data.
Using the Cast and related operators in SQL Server will allow data to be passed from the SQL Webstore to any data type. In SQL Server, the Money data type is commonly used, and should serve well for storing shipment cost. If using the numeric data type, it would typically be declared as follows: numeric(10,4).
e.g.
alter table orders add ShipmentCost numeric(10,4)
Multiple Carriers with the SQL Webstore
When using multiple carriers with the SQL Webstore, the shipping form can only open from the search window of the carrier you have open, even if the order is marked to open under a different carrier.