the datetime2 datatype goes to 7 decimal places;

can you show what it is you are trying to do?

With MyCTE (TheTime)

AS

(

SELECT convert(datetime2,'1900-01-01 0:12:14.1241234') UNION ALL

SELECT '1900-01-01 0:08:16.361' UNION ALL

...

how are you checking the data? are you doing select len(YourVarcharColumn) From SomeTable? How...

defintiely the problem.

do a SELECT * FROM T WHERE isdate(time_column) = 0

those are the values that cannot be converted, and raise an error.

i think this will do what you wanted:

With MyCTE (TheTime)

AS

(

SELECT CONVERT(time,'0:12:14:124') UNION ALL

SELECT '0:08:16:361' UNION ALL

SELECT '0:08:57:705'

)

SELECT *,DATEDIFF(millisecond,cast('00:00:00' as time), TheTime)

From myCTE

Edit:

late to the...

well if you cannot change the code, kick it back to the developer and make them fix it, since it didn't work as expected, i guess.

My shop is different; no...

t.brown 89142 (9/4/2012)

---

I've attempted to answer a newbie question recently and because some of their data is private, some of the thread happened in private messages - which may be...

not sure about the error; typically that might be caused my the wrong schema, or an object name not being quoted;

you seem to be doing both of those things;

only thing...

no, if you grant xp_cmdshell permissions, you cannot restrict the commands that they might use when they construct the strings.

typically what you want to do instead is

1. Remove access...

if you can have an identity column in your table, you can create a calculated persisted column that auto generates that texty-like value.

--#################################################################################################

--Pattern: AA000 to ZZ999 max value=676000

--#################################################################################################

IF OBJECT_ID('X') IS...

saidapurs (9/3/2012)

---

Hi Lowell,

Thanks a lot, my issue has been resolve and i have small doubt i am not able to same name on zip file.

...

for #2 & 3, why not create a new/different user, and use that for the off hours processing?

have you determined what the impact would be if you killed the user...

SQL Mad Rafi (8/31/2012)

---

Thank you Lowell.

i knew a bit in how to set the values, but my question was, can i go and change these DEFAULT settings just like that...

not sure about extensibility, but i've seen two tools that look for issues, complexity, and rules for coding;

http://sqlcop.lessthandot.com/

http://www.sqlcodeguard.com/

I'm not familiar with either one, but it's a starting point;

you can change the default settings for your connections in SQL Server Management Studio in the Tools>>Options menu

as soon as you change it, that would affect all NEW query windows...

in that case, everyone who has CREATE DATABASE permissions must also have INSERT permissions on the logging table

INSERT adminlog.dbo.dba_ddl_events

no insert permissions (line 13, right, like the error said?) would cause...