stored procedure error after AD DomainController demotion
we are having real problems with this
A description of the problem per our sql developer, "Out of VB.Net, a call to a stored procedure is returning an error for the Sql "convert" function. "
we have a program (FTPINVal) installed (Add/Remove Programs, written in vb.net) that shows up as a Service...the service starts and runs fine. The program is designed to look for particular data files at particular times in a certain data location - these files are retrieved by another service and placed in the particular data location.
When the files are THERE, the process completes normally.
When the files are NOT THERE, the service errors out with this:
System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
at System.Data.SqlClient.SqlDataReader.Read()
at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at FTPINVAL.FTPINValService.ValidateLastRun()
We have placed a debug on the values that should be coming out of the stored procedure and they look correct.
Immediately prior to this problem, we demoted the SQL server from it's role as a Global Catalog/Domain Controller. Comments about the wisdom of having a production SQL server also be a DC are nice but un-necessary, that's why we demoted it right? The SQL server is currently a member of the domain but is not a DC.
The CODE has not changed. We have uninstalled and re-installed the program/service (no change). We have copied the relevant code out of production and placed onto a development box and it works correctly.
In order to try to get this function to work correctly in production we have:
nettime (thinking that it didn't know what time it was, no love)
group policy (didn't find a dc at first, it does now but no love)
In AD, trusting SQL server to call services from other servers (no love, undid this after it didn't work)
starting FTPInVal as local Administrator instead of domain/Administrator, no love
refershing SQL Parameter in Active Directory (no love)
After Googling the "conversion of char data type" and seeing lots of references to "US English" and other comments re: Regional Settings we tried changing the regional settings around (date, time), we tried all of the different combos but no love
Again, something changed on the SQL box (independent of code) when the domotion from DC completed and it's causing a SQL error on the "convert" function (incidentally, the "convert" function is being called - and executing correctly - in other places, both withing this service and other services)
advice on what to check would be appreciated