pre-1750 (#?!) dates in SQL table

Sorry for incomplete info here. This is infrequent problem we have.
We have SQL 2000 (sp3?). Our internet team inserts rows into
a table. Don't know the mechanism they use (yet). We use a
VB6 app to read the rows. Two rows have appeared in the table
with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
for year. When I did SELECT CONVERT (float, colname) I got
-61024.0 (or some value pretty close to that).
Has anyone see anything similar?
Craig HesselI've never seen anything like it. I'd start with DBCC CHECKTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:f39je.4580$eR.285@.fe05.lga..
.
> Sorry for incomplete info here. This is infrequent problem we have.
> We have SQL 2000 (sp3?). Our internet team inserts rows into
> a table. Don't know the mechanism they use (yet). We use a
> VB6 app to read the rows. Two rows have appeared in the table
> with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
> I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
> for year. When I did SELECT CONVERT (float, colname) I got
> -61024.0 (or some value pretty close to that).
> Has anyone see anything similar?
> Craig Hessel
>
>|||Thanks, Tibor. I contacted internet team member and he confirmed they could
indeed insert bad dates:
"I just ran a test and it looks like the SQL driver we use from WebSphere
does not do the same date checks as the one you use. I was able to enter a
date in the year 204 and the database took it without question. "
"We use JDBC drivers to interact with the database and we use Java code to
come up with what essentially is a Long value representing the number of
milliseconds since 1/1/1900 (or something like that) to represent the date.
We then update the database with that value. "
BTW, I missed a digit in the SELECT CONVERT(float, colname) value reported
earlier. The year
204 is about 600,000 days, not 60,000 days, before Jan 1, 1900.
Craig
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PrRtBRXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> I've never seen anything like it. I'd start with DBCC CHECKTABLE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Craig Hessel" <craig_hessel@.hotmail.com> wrote in message
> news:f39je.4580$eR.285@.fe05.lga...
>|||I've seen a similar problem with bulk loads of dates using non-MS ODBC
drivers. Do check your SP though as I don't recall this happening since
SP3. Check for a driver update as well.
You may find you can fix the dates with an UPDATE like this:
UPDATE YourTable SET dt = NULL
WHERE dt < '17530101'
David Portas
SQL Server MVP
--sql

pre-1750 (#?!) dates in SQL table

Sorry for incomplete info here. This is infrequent problem we have.
We have SQL 2000 (sp3?). Our internet team inserts rows into
a table. Don't know the mechanism they use (yet). We use a
VB6 app to read the rows. Two rows have appeared in the table
with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
for year. When I did SELECT CONVERT (float, colname) I got
-61024.0 (or some value pretty close to that).
Has anyone see anything similar?
Craig Hessel
I've never seen anything like it. I'd start with DBCC CHECKTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:f39je.4580$eR.285@.fe05.lga...
> Sorry for incomplete info here. This is infrequent problem we have.
> We have SQL 2000 (sp3?). Our internet team inserts rows into
> a table. Don't know the mechanism they use (yet). We use a
> VB6 app to read the rows. Two rows have appeared in the table
> with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
> I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
> for year. When I did SELECT CONVERT (float, colname) I got
> -61024.0 (or some value pretty close to that).
> Has anyone see anything similar?
> Craig Hessel
>
>
|||Thanks, Tibor. I contacted internet team member and he confirmed they could
indeed insert bad dates:
"I just ran a test and it looks like the SQL driver we use from WebSphere
does not do the same date checks as the one you use. I was able to enter a
date in the year 204 and the database took it without question. "
"We use JDBC drivers to interact with the database and we use Java code to
come up with what essentially is a Long value representing the number of
milliseconds since 1/1/1900 (or something like that) to represent the date.
We then update the database with that value. "
BTW, I missed a digit in the SELECT CONVERT(float, colname) value reported
earlier. The year
204 is about 600,000 days, not 60,000 days, before Jan 1, 1900.
Craig
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PrRtBRXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> I've never seen anything like it. I'd start with DBCC CHECKTABLE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Craig Hessel" <craig_hessel@.hotmail.com> wrote in message
> news:f39je.4580$eR.285@.fe05.lga...
>
|||I've seen a similar problem with bulk loads of dates using non-MS ODBC
drivers. Do check your SP though as I don't recall this happening since
SP3. Check for a driver update as well.
You may find you can fix the dates with an UPDATE like this:
UPDATE YourTable SET dt = NULL
WHERE dt < '17530101'
David Portas
SQL Server MVP

pre-1750 (#?!) dates in SQL table

Sorry for incomplete info here. This is infrequent problem we have.
We have SQL 2000 (sp3?). Our internet team inserts rows into
a table. Don't know the mechanism they use (yet). We use a
VB6 app to read the rows. Two rows have appeared in the table
with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
for year. When I did SELECT CONVERT (float, colname) I got
-61024.0 (or some value pretty close to that).
Has anyone see anything similar?
Craig HesselI've never seen anything like it. I'd start with DBCC CHECKTABLE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:f39je.4580$eR.285@.fe05.lga...
> Sorry for incomplete info here. This is infrequent problem we have.
> We have SQL 2000 (sp3?). Our internet team inserts rows into
> a table. Don't know the mechanism they use (yet). We use a
> VB6 app to read the rows. Two rows have appeared in the table
> with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
> I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
> for year. When I did SELECT CONVERT (float, colname) I got
> -61024.0 (or some value pretty close to that).
> Has anyone see anything similar?
> Craig Hessel
>
>|||Thanks, Tibor. I contacted internet team member and he confirmed they could
indeed insert bad dates:
"I just ran a test and it looks like the SQL driver we use from WebSphere
does not do the same date checks as the one you use. I was able to enter a
date in the year 204 and the database took it without question. "
"We use JDBC drivers to interact with the database and we use Java code to
come up with what essentially is a Long value representing the number of
milliseconds since 1/1/1900 (or something like that) to represent the date.
We then update the database with that value. "
BTW, I missed a digit in the SELECT CONVERT(float, colname) value reported
earlier. The year
204 is about 600,000 days, not 60,000 days, before Jan 1, 1900.
Craig
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PrRtBRXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> I've never seen anything like it. I'd start with DBCC CHECKTABLE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Craig Hessel" <craig_hessel@.hotmail.com> wrote in message
> news:f39je.4580$eR.285@.fe05.lga...
>> Sorry for incomplete info here. This is infrequent problem we have.
>> We have SQL 2000 (sp3?). Our internet team inserts rows into
>> a table. Don't know the mechanism they use (yet). We use a
>> VB6 app to read the rows. Two rows have appeared in the table
>> with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
>> I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
>> for year. When I did SELECT CONVERT (float, colname) I got
>> -61024.0 (or some value pretty close to that).
>> Has anyone see anything similar?
>> Craig Hessel
>>
>|||I've seen a similar problem with bulk loads of dates using non-MS ODBC
drivers. Do check your SP though as I don't recall this happening since
SP3. Check for a driver update as well.
You may find you can fix the dates with an UPDATE like this:
UPDATE YourTable SET dt = NULL
WHERE dt < '17530101'
--
David Portas
SQL Server MVP
--

Possible? SQL Svr obtaining data via SOAP/XML from another SQL Sv

Hello All,
My challenge is to create a process that will bring together data from two
SQL servers (one local, one remote through the internet). The task is to
create a Stored Procedure that will perform a relational query based on data
from the two databases and return the resulting record set to a Crystal
Report. The remote database is part of our company's CRM HOSTED solution
which provides data accessible via a SOAP/XML interface.
I have implemented solutions in the past using a SOAP/XML data feed, whereby
I used VBScript, MSXML, MSSOAP to connect, query, parse and insert data into
an SQL database. For this implementation, however, I want to avoid this
method at all costs since there would be a tremendous overhead of converting,
maintaining and updating dozens of Crystal Reports to this new environment.
Instead, I would like to be able to make an SQL Stored Procedure invoke the
SOAP call and, in turn, load and process the resulting XML dataset as though
it were an SQL Record Set. I have found technical leads supporting the theory
that I might be able to achieve this goal. The following are technical
"bread-crumbs" I found or technical areas I have investigated thus far:
(1) SOAP/XML can be delivered from an MSSQL server in several ways via "AS
XML" functionality provided in SELECT verb. I am hypothesizing that the CRM
can deliver the XML data per a direct SQL query through this method. The nut
to crack here is that our XML data would be delivered back to the caller
"shrink-wrapped" in a SOAP Envelope and thus there would be the task of
unwrapping the XML dataset from the SOAP envelope upon the return of data to
the caller.
(2) MSSQL provides a method via "OPENXML" to open and parse an XML source
file and associate data returned with SELECT / Relational logic. CAVEAT: I
have only seen the OPENXML deal with XML data stored in FILE resources...not
an XML object such as that found implemented in the MSXML object classes
which I have used in VBSCRIPT.
I am trying to efficiently architect a solution without having to develop
too many components and excessive rework of prior development. My goal is to
perform this data query task ALL within the MSSQL environment. Any direction
or validation is greatly appreciated.
Sincerely,
Karl Engvold
Website: http://www.mcint.net
In SQLServer 2005 we have Native Web Services Support. This allows one to expose a stored procedure as a web service. The stored procedcure could be written in CLR or T-SQL. We also generate WSDL on demand. The SOAP responses contain "rowsets" to captu
re the output of select statements. These rowsets use the diffgram format on the wire and as such can be mapped to datasets on the client. Using a VS>net proxy generator to generate the client code does this automatically for you.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||as mentioned earlier this fucntionality is available in SQL2005 Beta bits. If you can get a hold of these bits and .Net framewwork Beta1 you can do what I suggested.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...