Primary Key with a datetime datatype

We're seeing a data conversion error after adding a primary key that contains
a datetime column.
SQL profiler indicated the app was passing a date value in format that is
incompatible (passing dd-mm-yyyy). I am able to reproduce the error in Query
Analyzer.
The vendor is insisting that the problem is due to the primary key. To
humor them, I removed the PK's and of course the problem still persists. But
now the vendor is telling us to re-install the app and migrate all the data
again without defining primary key.
Is there something I don't know about the internal storage of a primary
key/datetime datatype that would cause this error?
Responses appreciated!
"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in
Query
> Analyzer.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
Aboslutely not...
But why did the date format that the app is passing change? Does the
vendor claim that it was that way before?
|||SORRY - THIS IS A DUPLICATE. I KEPT GETTING ERROR SAYING MY POST DIDN'T POST.
"Colleen Rossman" wrote:

> We're seeing a data conversion error after adding a primary key that contains
> a datetime column.
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in Query
> Analyzer.
> The vendor is insisting that the problem is due to the primary key. To
> humor them, I removed the PK's and of course the problem still persists. But
> now the vendor is telling us to re-install the app and migrate all the data
> again without defining primary key.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
> Responses appreciated!
>
|||A primary key constraint won't affect the way a date is stored and
interpreted.
If the application passes dates as strings then it should use one of the
standard ISO formats such as YYYYMMDD. That's the best way to be sure that
date strings will be interpreted correctly.
David Portas
SQL Server MVP
|||Yes, the vendor does claim it's 'by design' and it's been this way for a long
time. However I found a 'set dateformat=dmy' statement in the trace output
which works in QA, allowing me to pass ddmmyyyy format without error, but it
didn't work in the app and that's teh real issue here.
Colleen
"Adam Machanic" wrote:

> "Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
> news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> Query
> Aboslutely not...
> But why did the date format that the app is passing change? Does the
> vendor claim that it was that way before?
>
>
|||Perhaps the app passes appropriate SET DATEFORMAT command, but in a different connection? I'm
thinking things like connection pooling etc. It is definitely an app issue, and Profiler should
reveal what is really happening. I'd strongly suggest to the app vendor to use a "safe" format.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:75915596-9A5C-4665-950D-794B05F957C6@.microsoft.com...[vbcol=seagreen]
> Yes, the vendor does claim it's 'by design' and it's been this way for a long
> time. However I found a 'set dateformat=dmy' statement in the trace output
> which works in QA, allowing me to pass ddmmyyyy format without error, but it
> didn't work in the app and that's teh real issue here.
> Colleen
> "Adam Machanic" wrote:
|||Pass/retrieve it as text only. Then post-process
Jeff
"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> We're seeing a data conversion error after adding a primary key that
contains
> a datetime column.
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in
Query
> Analyzer.
> The vendor is insisting that the problem is due to the primary key. To
> humor them, I removed the PK's and of course the problem still persists.
But
> now the vendor is telling us to re-install the app and migrate all the
data
> again without defining primary key.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
> Responses appreciated!
>
sql

Primary Key with a datetime datatype

We're seeing a data conversion error after adding a primary key that contains
a datetime column.
SQL profiler indicated the app was passing a date value in format that is
incompatible (passing dd-mm-yyyy). I am able to reproduce the error in Query
Analyzer.
The vendor is insisting that the problem is due to the primary key. To
humor them, I removed the PK's and of course the problem still persists. But
now the vendor is telling us to re-install the app and migrate all the data
again without defining primary key.
Is there something I don't know about the internal storage of a primary
key/datetime datatype that would cause this error?
Responses appreciated!"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in
Query
> Analyzer.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
Aboslutely not...
But why did the date format that the app is passing change? Does the
vendor claim that it was that way before?|||SORRY - THIS IS A DUPLICATE. I KEPT GETTING ERROR SAYING MY POST DIDN'T POST.
"Colleen Rossman" wrote:
> We're seeing a data conversion error after adding a primary key that contains
> a datetime column.
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in Query
> Analyzer.
> The vendor is insisting that the problem is due to the primary key. To
> humor them, I removed the PK's and of course the problem still persists. But
> now the vendor is telling us to re-install the app and migrate all the data
> again without defining primary key.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
> Responses appreciated!
>|||A primary key constraint won't affect the way a date is stored and
interpreted.
If the application passes dates as strings then it should use one of the
standard ISO formats such as YYYYMMDD. That's the best way to be sure that
date strings will be interpreted correctly.
--
David Portas
SQL Server MVP
--|||Yes, the vendor does claim it's 'by design' and it's been this way for a long
time. However I found a 'set dateformat=dmy' statement in the trace output
which works in QA, allowing me to pass ddmmyyyy format without error, but it
didn't work in the app and that's teh real issue here.
Colleen
"Adam Machanic" wrote:
> "Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
> news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> >
> > SQL profiler indicated the app was passing a date value in format that is
> > incompatible (passing dd-mm-yyyy). I am able to reproduce the error in
> Query
> > Analyzer.
> >
> > Is there something I don't know about the internal storage of a primary
> > key/datetime datatype that would cause this error?
> Aboslutely not...
> But why did the date format that the app is passing change? Does the
> vendor claim that it was that way before?
>
>|||Perhaps the app passes appropriate SET DATEFORMAT command, but in a different connection? I'm
thinking things like connection pooling etc. It is definitely an app issue, and Profiler should
reveal what is really happening. I'd strongly suggest to the app vendor to use a "safe" format.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:75915596-9A5C-4665-950D-794B05F957C6@.microsoft.com...
> Yes, the vendor does claim it's 'by design' and it's been this way for a long
> time. However I found a 'set dateformat=dmy' statement in the trace output
> which works in QA, allowing me to pass ddmmyyyy format without error, but it
> didn't work in the app and that's teh real issue here.
> Colleen
> "Adam Machanic" wrote:
>> "Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
>> news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
>> >
>> > SQL profiler indicated the app was passing a date value in format that is
>> > incompatible (passing dd-mm-yyyy). I am able to reproduce the error in
>> Query
>> > Analyzer.
>> >
>> > Is there something I don't know about the internal storage of a primary
>> > key/datetime datatype that would cause this error?
>> Aboslutely not...
>> But why did the date format that the app is passing change? Does the
>> vendor claim that it was that way before?
>>|||Pass/retrieve it as text only. Then post-process
Jeff
"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> We're seeing a data conversion error after adding a primary key that
contains
> a datetime column.
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in
Query
> Analyzer.
> The vendor is insisting that the problem is due to the primary key. To
> humor them, I removed the PK's and of course the problem still persists.
But
> now the vendor is telling us to re-install the app and migrate all the
data
> again without defining primary key.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
> Responses appreciated!
>

Primary Key with a datetime datatype

We're seeing a data conversion error after adding a primary key that contain
s
a datetime column.
SQL profiler indicated the app was passing a date value in format that is
incompatible (passing dd-mm-yyyy). I am able to reproduce the error in Quer
y
Analyzer.
The vendor is insisting that the problem is due to the primary key. To
humor them, I removed the PK's and of course the problem still persists. Bu
t
now the vendor is telling us to re-install the app and migrate all the data
again without defining primary key.
Is there something I don't know about the internal storage of a primary
key/datetime datatype that would cause this error?
Responses appreciated!"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in
Query
> Analyzer.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
Aboslutely not...
But why did the date format that the app is passing change? Does the
vendor claim that it was that way before?|||SORRY - THIS IS A DUPLICATE. I KEPT GETTING ERROR SAYING MY POST DIDN'T POS
T.
"Colleen Rossman" wrote:

> We're seeing a data conversion error after adding a primary key that conta
ins
> a datetime column.
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in Qu
ery
> Analyzer.
> The vendor is insisting that the problem is due to the primary key. To
> humor them, I removed the PK's and of course the problem still persists.
But
> now the vendor is telling us to re-install the app and migrate all the dat
a
> again without defining primary key.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
> Responses appreciated!
>|||A primary key constraint won't affect the way a date is stored and
interpreted.
If the application passes dates as strings then it should use one of the
standard ISO formats such as YYYYMMDD. That's the best way to be sure that
date strings will be interpreted correctly.
David Portas
SQL Server MVP
--|||Yes, the vendor does claim it's 'by design' and it's been this way for a lon
g
time. However I found a 'set dateformat=dmy' statement in the trace output
which works in QA, allowing me to pass ddmmyyyy format without error, but i
t
didn't work in the app and that's teh real issue here.
Colleen
"Adam Machanic" wrote:

> "Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
> news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> Query
> Aboslutely not...
> But why did the date format that the app is passing change? Does the
> vendor claim that it was that way before?
>
>|||Perhaps the app passes appropriate SET DATEFORMAT command, but in a differen
t connection? I'm
thinking things like connection pooling etc. It is definitely an app issue,
and Profiler should
reveal what is really happening. I'd strongly suggest to the app vendor to u
se a "safe" format.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:75915596-9A5C-4665-950D-794B05F957C6@.microsoft.com...[vbcol=seagreen]
> Yes, the vendor does claim it's 'by design' and it's been this way for a l
ong
> time. However I found a 'set dateformat=dmy' statement in the trace outpu
t
> which works in QA, allowing me to pass ddmmyyyy format without error, but
it
> didn't work in the app and that's teh real issue here.
> Colleen
> "Adam Machanic" wrote:
>|||Pass/retrieve it as text only. Then post-process
Jeff
"Colleen Rossman" <crossman@.discussions.microsoft.com> wrote in message
news:DDBAF283-CC40-408A-B942-428DF8A157E3@.microsoft.com...
> We're seeing a data conversion error after adding a primary key that
contains
> a datetime column.
> SQL profiler indicated the app was passing a date value in format that is
> incompatible (passing dd-mm-yyyy). I am able to reproduce the error in
Query
> Analyzer.
> The vendor is insisting that the problem is due to the primary key. To
> humor them, I removed the PK's and of course the problem still persists.
But
> now the vendor is telling us to re-install the app and migrate all the
data
> again without defining primary key.
> Is there something I don't know about the internal storage of a primary
> key/datetime datatype that would cause this error?
> Responses appreciated!
>

Previous machine name in Profiler

I changed the machine name yesterday (from Machine1 to Server1).
I'd delete the SQL server registration in Enterprise Manager and
register again with the new server name - Server1.
Everything run correctly.
But when I open the Profiler, create a new trace, I use (local) when
"Connect to SQL Server" prompted. After I click "OK", I saw my old
machine name "Machine1" in "Trace SQL Server" field under the "General"
tab. Everytime I have to change that server name to my new machine name
in order to use the profiler.
Does anyone knows how to change that default to my new machine name?
(It didn't run when I use the old machine name)
Thanks.
Peter CCH
Check out Client/Server Network Utility the alias to be changed
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1140415112.093022.101800@.o13g2000cwo.googlegr oups.com...
>I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>
|||Hi Peter
Did you follow: http://support.microsoft.com/default...b;en-us;303774
John
"Peter CCH" wrote:

> I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>
|||I tried:
sp_dropserver 'Machine1'
go
sp_addserver 'Server1', 'local'
go
But the problem still remain.
I tried to add in alias under Client/Network Utility, but still the
same, problem remain.
Peter CCH
|||OK, got it.
Have to restart the SQL Server in order to take effect.
Thanks.
Peter CCH

Previous machine name in Profiler

I changed the machine name yesterday (from Machine1 to Server1).
I'd delete the SQL server registration in Enterprise Manager and
register again with the new server name - Server1.
Everything run correctly.
But when I open the Profiler, create a new trace, I use (local) when
"Connect to SQL Server" prompted. After I click "OK", I saw my old
machine name "Machine1" in "Trace SQL Server" field under the "General"
tab. Everytime I have to change that server name to my new machine name
in order to use the profiler.
Does anyone knows how to change that default to my new machine name?
(It didn't run when I use the old machine name)
Thanks.
Peter CCHCheck out Client/Server Network Utility the alias to be changed
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1140415112.093022.101800@.o13g2000cwo.googlegroups.com...
>I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>|||Hi Peter
Did you follow: [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;303774[/ur
l]
John
"Peter CCH" wrote:

> I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>|||I tried:
---
sp_dropserver 'Machine1'
go
sp_addserver 'Server1', 'local'
go
---
But the problem still remain.
I tried to add in alias under Client/Network Utility, but still the
same, problem remain.
Peter CCH|||OK, got it.
Have to restart the SQL Server in order to take effect.
Thanks.
Peter CCH

Previous machine name in Profiler

I changed the machine name yesterday (from Machine1 to Server1).
I'd delete the SQL server registration in Enterprise Manager and
register again with the new server name - Server1.
Everything run correctly.
But when I open the Profiler, create a new trace, I use (local) when
"Connect to SQL Server" prompted. After I click "OK", I saw my old
machine name "Machine1" in "Trace SQL Server" field under the "General"
tab. Everytime I have to change that server name to my new machine name
in order to use the profiler.
Does anyone knows how to change that default to my new machine name?
(It didn't run when I use the old machine name)
Thanks.
Peter CCHCheck out Client/Server Network Utility the alias to be changed
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1140415112.093022.101800@.o13g2000cwo.googlegroups.com...
>I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>|||Hi Peter
Did you follow: http://support.microsoft.com/default.aspx?scid=kb;en-us;303774
John
"Peter CCH" wrote:
> I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>|||I tried:
---
sp_dropserver 'Machine1'
go
sp_addserver 'Server1', 'local'
go
---
But the problem still remain.
I tried to add in alias under Client/Network Utility, but still the
same, problem remain.
Peter CCH|||OK, got it.
Have to restart the SQL Server in order to take effect.
Thanks.
Peter CCH