stored procedure broken after ad demotion 1

[reallywildstuff]

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

 

[JayKusch]

Had this process worked in the past?

Thanks

J. Kusch

 

[SQLBill]

We have copied the relevant code out of production and placed onto a development box

This sounds like you didn't test the whole code, just part of it. If so, then test JUST that part of it on the production side. It might not be this portion causing the problem.

You don't provide the code or anything else SQL Server related, so it's really hard to help.

First thing I can think of is permissions. Originally this SQL Server was also the Domain Controller, now it's not. Maybe authentication is a problem.

What does this script actually do?
[quote}
When the files are THERE, the process completes normally.

When the files are NOT THERE, the service errors out with this:
[/quote]
What does it do when it finds a file? What should happen when the file isn't there?

-SQLBill

Posting advice: FAQ481-4875

 

[reallywildstuff]

oh yes, absolutely, the thing worked great before the ad demotion

 

[SQLBill]

Someone gave me a star...why? Did I solve the problem? If so, how?

-SQLBill

Posting advice: FAQ481-4875

 

[reallywildstuff]

in reply to SQLBill -

when the file is there, it evaluates the file to make sure it meets certain parameters and sends it on down the process chain

when the file is not there, it's supposed to e-mail certain people (clients, e-mail addresses are in a table) and advise them that the files aren't there (this is a time-sensitive operation, "your files processed in 24 hours or it's free!")

i'm sorry about not posting the sql code, I barely have a grip on it myself but the issue is NOT with the code - we have compared the existing code line-by-line with the archived version and it is identical, we pull the code off of the production and put it on test and it runs great, run the same code on production and it fails, remember that this code we're speaking about ran fine before the demotion.

so yes, i think it's authentication as well - i've checked the service start validation as noted, where else should I be looking?

thanks,

reallywildstuff

 

[SQLBill]

There are normally two issues with authentication.

This doesn't seem to really be a true authentication issue. It connects fine when the files are there. So it's connecting and authenticating. It's just when the files aren't there that there's a problem.

Possibilities....

1. when the files aren't found, the connection is 'broken'. This may be occuring since two different systems are being used, where before it was one.

2. an error message is being passed back that the 'files don't exist' and that is causing the problem.

I would 'step through' the script and compare what happens when files are found and what happens when they don't exist. Somewhere in the script there might be a portion that causes the link to drop. Maybe add a bit of test code in the script - prior to the check for files add PRINT 'Checking for files'. Then where the files are found - PRINT 'Found Files'. Then where they aren't found - PRINT 'Files NOT found'. Then somewhere else put - PRINT 'Script continuing'.

Adding some prints into the script will give you some better ideas as to where it's failing. Once you find out where in your script it's failing, then you can better figure out the cause.

-SQLBill

Posting advice: FAQ481-4875

 

[JamesLean]

I would say, without seeing any code, that this problem is going to be to do with a date value being passed in to the procedure in an ambiguous format, eg mm/dd/yyyy, and then falling over when being converted to a datetime value.

Say you were calling a stored proc like this:

EXEC myproc '01/12/2005'

SQL Server would not know whether this was 1st December or 12th January. The way it interprets this is down to the language of the current login (assuming no SET DATEFORMAT command has been issued).

Have you checked the logins that this process uses on test/production and verified that they are both using the same language?

--James