SQL Query Posting the Data Properly but Generating An Error

[Former Member]

I've created a SQL query to update records in a database. While testing I used an Access database and it worked perfectly. To go into production I've moved the data to SQLServer 2005 installed on my xMII server. The SQL syntax is different for handling dates between the two databases so I modified my queries accordingly. I now have:

UPDATE ControlLimits SET Metric_ID='[Param.1]', CDate='[Param.2]', LCL=[Param.3], Mean=[Param.4], UCL=[Param.5]

Where (Metric_ID='[Param.1]' and CDate = '[Param.2]')

CDate is my date field being passed in the format 'mm/dd/yyyy', the table field is formatted for datetime - the same as in Access.

When running this query (with the [Param.x]'s being replaced with actual values)through SQL Server Management Studio on the xMII server, it works just fine. When run as a query template, the data is posted properly to the table but the template returns a 'A SQL Error has occurred on query, The statement did not return a result set..'

Any ideas?

David

[Former Member · Accepted solution]

You are probably using the Fixed Query mode, instead use the Command mode.

For all queries where you are supposed to write to a DB (inserts, updates , deletes), use the Command Mode.

[Former Member · Accepted solution]

Udayan,

You are absolutely correct! When I did it in Access I was correctly using the Command mode. When I moved to the SQL Server data source in my query template, I picked the Fixed Query mode by mistake.

Thanks for your help!

Regards,

David