We have a reporting database that end users run ad-hoc queries against (SQL
Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
following error message:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space.
When I track down the user associated with the Event Log message, he/she is
running a huge query that needs better selection criteria, but I don't
believe that the tempdb log is truely out of space. We have plenty of disk
space, and have gotten this message with tempdb log set to unrestricted and
with a limit of 15 GB.
Question: Is this a sql server bug or related to an i/o problem, because it
really isn't a true tempdb log full condition. Does this impact all users
using tempdb (I would think so) and any suggestions on how to prevent this?
Thanks,
DanDan,
This is not a bug. It is truly a tempdb log full alert. It does not matter
how much space you have because you are limiting the log to 15 GB. It is ver
y
much possible for the log to be full on a reporting database. Remember, all
sorting, agregations and temp tables are created in the tempdb.
I don't think there is a right answer for the size, you will have to
increase it and watch it and increase it again as needed.
hth
DeeJay
"Dan H" wrote:
> We have a reporting database that end users run ad-hoc queries against (SQ
L
> Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
> following error message:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the transaction log fo
r
> the database to free up some log space.
> When I track down the user associated with the Event Log message, he/she i
s
> running a huge query that needs better selection criteria, but I don't
> believe that the tempdb log is truely out of space. We have plenty of dis
k
> space, and have gotten this message with tempdb log set to unrestricted an
d
> with a limit of 15 GB.
> Question: Is this a sql server bug or related to an i/o problem, because
it
> really isn't a true tempdb log full condition. Does this impact all users
> using tempdb (I would think so) and any suggestions on how to prevent this
?
> Thanks,
> Dan
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Tuesday, March 27, 2012
Error 9002, tempdb log full
We have a reporting database that end users run ad-hoc queries against (SQL
Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
following error message:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space.
When I track down the user associated with the Event Log message, he/she is
running a huge query that needs better selection criteria, but I don't
believe that the tempdb log is truely out of space. We have plenty of disk
space, and have gotten this message with tempdb log set to unrestricted and
with a limit of 15 GB.
Question: Is this a sql server bug or related to an i/o problem, because it
really isn't a true tempdb log full condition. Does this impact all users
using tempdb (I would think so) and any suggestions on how to prevent this?
Thanks,
Dan
Dan,
This is not a bug. It is truly a tempdb log full alert. It does not matter
how much space you have because you are limiting the log to 15 GB. It is very
much possible for the log to be full on a reporting database. Remember, all
sorting, agregations and temp tables are created in the tempdb.
I don't think there is a right answer for the size, you will have to
increase it and watch it and increase it again as needed.
hth
DeeJay
"Dan H" wrote:
> We have a reporting database that end users run ad-hoc queries against (SQL
> Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
> following error message:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the transaction log for
> the database to free up some log space.
> When I track down the user associated with the Event Log message, he/she is
> running a huge query that needs better selection criteria, but I don't
> believe that the tempdb log is truely out of space. We have plenty of disk
> space, and have gotten this message with tempdb log set to unrestricted and
> with a limit of 15 GB.
> Question: Is this a sql server bug or related to an i/o problem, because it
> really isn't a true tempdb log full condition. Does this impact all users
> using tempdb (I would think so) and any suggestions on how to prevent this?
> Thanks,
> Dan
Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
following error message:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space.
When I track down the user associated with the Event Log message, he/she is
running a huge query that needs better selection criteria, but I don't
believe that the tempdb log is truely out of space. We have plenty of disk
space, and have gotten this message with tempdb log set to unrestricted and
with a limit of 15 GB.
Question: Is this a sql server bug or related to an i/o problem, because it
really isn't a true tempdb log full condition. Does this impact all users
using tempdb (I would think so) and any suggestions on how to prevent this?
Thanks,
Dan
Dan,
This is not a bug. It is truly a tempdb log full alert. It does not matter
how much space you have because you are limiting the log to 15 GB. It is very
much possible for the log to be full on a reporting database. Remember, all
sorting, agregations and temp tables are created in the tempdb.
I don't think there is a right answer for the size, you will have to
increase it and watch it and increase it again as needed.
hth
DeeJay
"Dan H" wrote:
> We have a reporting database that end users run ad-hoc queries against (SQL
> Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
> following error message:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the transaction log for
> the database to free up some log space.
> When I track down the user associated with the Event Log message, he/she is
> running a huge query that needs better selection criteria, but I don't
> believe that the tempdb log is truely out of space. We have plenty of disk
> space, and have gotten this message with tempdb log set to unrestricted and
> with a limit of 15 GB.
> Question: Is this a sql server bug or related to an i/o problem, because it
> really isn't a true tempdb log full condition. Does this impact all users
> using tempdb (I would think so) and any suggestions on how to prevent this?
> Thanks,
> Dan
Error 9002, tempdb log full
We have a reporting database that end users run ad-hoc queries against (SQL
Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
following error message:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space.
When I track down the user associated with the Event Log message, he/she is
running a huge query that needs better selection criteria, but I don't
believe that the tempdb log is truely out of space. We have plenty of disk
space, and have gotten this message with tempdb log set to unrestricted and
with a limit of 15 GB.
Question: Is this a sql server bug or related to an i/o problem, because it
really isn't a true tempdb log full condition. Does this impact all users
using tempdb (I would think so) and any suggestions on how to prevent this?
Thanks,
DanDan,
This is not a bug. It is truly a tempdb log full alert. It does not matter
how much space you have because you are limiting the log to 15 GB. It is very
much possible for the log to be full on a reporting database. Remember, all
sorting, agregations and temp tables are created in the tempdb.
I don't think there is a right answer for the size, you will have to
increase it and watch it and increase it again as needed.
hth
DeeJay
"Dan H" wrote:
> We have a reporting database that end users run ad-hoc queries against (SQL
> Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
> following error message:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the transaction log for
> the database to free up some log space.
> When I track down the user associated with the Event Log message, he/she is
> running a huge query that needs better selection criteria, but I don't
> believe that the tempdb log is truely out of space. We have plenty of disk
> space, and have gotten this message with tempdb log set to unrestricted and
> with a limit of 15 GB.
> Question: Is this a sql server bug or related to an i/o problem, because it
> really isn't a true tempdb log full condition. Does this impact all users
> using tempdb (I would think so) and any suggestions on how to prevent this?
> Thanks,
> Dan
Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
following error message:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space.
When I track down the user associated with the Event Log message, he/she is
running a huge query that needs better selection criteria, but I don't
believe that the tempdb log is truely out of space. We have plenty of disk
space, and have gotten this message with tempdb log set to unrestricted and
with a limit of 15 GB.
Question: Is this a sql server bug or related to an i/o problem, because it
really isn't a true tempdb log full condition. Does this impact all users
using tempdb (I would think so) and any suggestions on how to prevent this?
Thanks,
DanDan,
This is not a bug. It is truly a tempdb log full alert. It does not matter
how much space you have because you are limiting the log to 15 GB. It is very
much possible for the log to be full on a reporting database. Remember, all
sorting, agregations and temp tables are created in the tempdb.
I don't think there is a right answer for the size, you will have to
increase it and watch it and increase it again as needed.
hth
DeeJay
"Dan H" wrote:
> We have a reporting database that end users run ad-hoc queries against (SQL
> Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
> following error message:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the transaction log for
> the database to free up some log space.
> When I track down the user associated with the Event Log message, he/she is
> running a huge query that needs better selection criteria, but I don't
> believe that the tempdb log is truely out of space. We have plenty of disk
> space, and have gotten this message with tempdb log set to unrestricted and
> with a limit of 15 GB.
> Question: Is this a sql server bug or related to an i/o problem, because it
> really isn't a true tempdb log full condition. Does this impact all users
> using tempdb (I would think so) and any suggestions on how to prevent this?
> Thanks,
> Dan
error 8906
Hi,
I have sql 7.o server and lately after running dbcc checkdb I saw these errors. These errors have not come in the way of functionality of application and database. I have run repair_allow_data_loss option couple of times with no sucess. Since these allocation errors are not associated with any single object can we go on with these errors in DB or how do I resolve these errors.
Thx,
Bxmakin
Server: Msg 8906, Level 16, State 1, Line 0
Page (1:656) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 100_PCT_FULL'.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:2464) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:12720) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:17144) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:223008) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:310776) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8906, Level 16, State 1, Line 0
Page (1:322715) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:315432), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 50_PCT_FULL'.
DBCC results for 'onbase'.
CHECKDB found 7 allocation errors and 0 consistency errors not associated with any single object.Do you have any text or image columns in your database? If so, what is the biggest BLOB file size for a particular row out of all the tables in your db?
I'm wondering this because those errors are related to extents and heaps.
...just a thought.
Kael|||Kael,
Thanks for reponding to my dilemma. I am not a DBA by any strech of imagination where I am caught between our pplication people and Microsoft. They have suggested that I restore from a backup and I stand to loose lot of work. I did restore a recent backup on a test ma chine with same allocation errors. I got suspicious about this one table which stores the trancations logs i.e. "Person X looked at a Y document at Certain time etc.." . I know these tables tend to grow really large and cause problems. I ran checktable etc. with no luck. Finally I dropped the table and ran checkdb and it cleared all the allocation errors. I donot know how to answer to your question. If you donot mind sending me a command or two to find out what you pointed out. It will be immensely helpful.
I did try running dbreindex on the table and it didnot help. Do you think if I run dbreindex with new fill factor it may help.
Thanks in advance.
Bxmakin
I have sql 7.o server and lately after running dbcc checkdb I saw these errors. These errors have not come in the way of functionality of application and database. I have run repair_allow_data_loss option couple of times with no sucess. Since these allocation errors are not associated with any single object can we go on with these errors in DB or how do I resolve these errors.
Thx,
Bxmakin
Server: Msg 8906, Level 16, State 1, Line 0
Page (1:656) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 100_PCT_FULL'.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:2464) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:12720) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:17144) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:223008) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:310776) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8906, Level 16, State 1, Line 0
Page (1:322715) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:315432), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 50_PCT_FULL'.
DBCC results for 'onbase'.
CHECKDB found 7 allocation errors and 0 consistency errors not associated with any single object.Do you have any text or image columns in your database? If so, what is the biggest BLOB file size for a particular row out of all the tables in your db?
I'm wondering this because those errors are related to extents and heaps.
...just a thought.
Kael|||Kael,
Thanks for reponding to my dilemma. I am not a DBA by any strech of imagination where I am caught between our pplication people and Microsoft. They have suggested that I restore from a backup and I stand to loose lot of work. I did restore a recent backup on a test ma chine with same allocation errors. I got suspicious about this one table which stores the trancations logs i.e. "Person X looked at a Y document at Certain time etc.." . I know these tables tend to grow really large and cause problems. I ran checktable etc. with no luck. Finally I dropped the table and ran checkdb and it cleared all the allocation errors. I donot know how to answer to your question. If you donot mind sending me a command or two to find out what you pointed out. It will be immensely helpful.
I did try running dbreindex on the table and it didnot help. Do you think if I run dbreindex with new fill factor it may help.
Thanks in advance.
Bxmakin
Error 87 - more info
Does any one know what this is about
i think its something to do with running the xp_cmdshell
**as a non sys admin user
[Microsoft][ODBC SQL Server Driver][SQL Server]xpsql.cpp:
Error 87 from GetProxyAccount on line 604
some saidto run this
exec master..xp_sqlagent_proxy_account N'SET',
N'DomainName',
N'UserName',
N'Password'
but didn't make any difference .. HELP
any ideas.
thanks
mic
.Hiave you tried the suggestion in response to your previous thread? Check
to ensure the 'Only users with sysadmin privileges ...' box is unchecked
from Enterprise Manager (Management\SQL Server Agent\Properties\Job System).
Hope this helps.
Dan Guzman
SQL Server MVP
"mike" <anonymous@.discussions.microsoft.com> wrote in message
news:028c01c3bd2a$e0792e20$a401280a@.phx.gbl...
i think its something to do with running the xp_cmdshell
**as a non sys admin user
[Microsoft][ODBC SQL Server Driver][SQL Server]xpsql.cpp:
Error 87 from GetProxyAccount on line 604
some saidto run this
exec master..xp_sqlagent_proxy_account N'SET',
N'DomainName',
N'UserName',
N'Password'
but didn't make any difference .. HELP
any ideas.
thanks
mic
.Hiave you tried the suggestion in response to your previous thread? Check
to ensure the 'Only users with sysadmin privileges ...' box is unchecked
from Enterprise Manager (Management\SQL Server Agent\Properties\Job System).
Hope this helps.
Dan Guzman
SQL Server MVP
"mike" <anonymous@.discussions.microsoft.com> wrote in message
news:028c01c3bd2a$e0792e20$a401280a@.phx.gbl...
quote:
> Does any one know what this is about
> i think its something to do with running the xp_cmdshell
> **as a non sys admin user
> [Microsoft][ODBC SQL Server Driver][SQL Server]xpsql.cpp:
> Error 87 from GetProxyAccount on line 604
> some saidto run this
> exec master..xp_sqlagent_proxy_account N'SET',
> N'DomainName',
> N'UserName',
> N'Password'
> but didn't make any difference .. HELP
> any ideas.
>
> thanks
> mic
>
> .
>
Error 8525: Distributed transaction completed. Either enlist this session ... PROBLEM!
Hi there,
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there'
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefId varchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/defaul...b;en-us;834849.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in m
essage news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/defaul...b;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there'
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefId varchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/defaul...b;en-us;834849.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in m
essage news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/defaul...b;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Error 8525: Distributed transaction completed. Either enlist this session ... PROBLEM!
Hi there,
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there?
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefIdvarchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT
> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/default...;en-us;834849.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/default...;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there?
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefIdvarchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT
> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/default...;en-us;834849.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/default...;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Error 8525: Distributed transaction completed. Either enlist this session ... PROBLEM!
Hi there,
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there'
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefId varchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834849.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there'
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefId varchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834849.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Error 845 running backup of one database
I have one database that I keep getting an error 845: Time out ocurred while
waiting for buffer latch type 3 for page (0:0), database ID 15, object ID
569352192, Index ID 0..
This only started happening last week. The database has not changed (other
than normal data entry) in over a year. Does anyone have any ideas of what
should be checked?
Thanks in advanceDid you see the specific recommendations in Books Online for this?
Error 845
Severity Level 17
Message Text
Time-out occurred while waiting for buffer latch type %d for page %S_PGID, d
atabase ID %d.
Explanation
When under a heavy stress load or high I/O conditions, your system may produ
ce this message.
Action
This message can usually be ignored; however, if you receive repeated messag
es where the wait time
increases, it may indicate an internal server problem. Contact your system a
dministrator. The system
administrator should check the waittype, waittime, lastwaittype, and the wai
tresource columns of
sysprocesses to see what activities each SPIDs is performing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Connie" <cfelt@.ga.wa.gov> wrote in message news:OcwIz0TWEHA.1144@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> I have one database that I keep getting an error 845: Time out ocurred whi
le
> waiting for buffer latch type 3 for page (0:0), database ID 15, object ID
> 569352192, Index ID 0..
> This only started happening last week. The database has not changed (othe
r
> than normal data entry) in over a year. Does anyone have any ideas of wha
t
> should be checked?
> Thanks in advance
>[/vbcol]
waiting for buffer latch type 3 for page (0:0), database ID 15, object ID
569352192, Index ID 0..
This only started happening last week. The database has not changed (other
than normal data entry) in over a year. Does anyone have any ideas of what
should be checked?
Thanks in advanceDid you see the specific recommendations in Books Online for this?
Error 845
Severity Level 17
Message Text
Time-out occurred while waiting for buffer latch type %d for page %S_PGID, d
atabase ID %d.
Explanation
When under a heavy stress load or high I/O conditions, your system may produ
ce this message.
Action
This message can usually be ignored; however, if you receive repeated messag
es where the wait time
increases, it may indicate an internal server problem. Contact your system a
dministrator. The system
administrator should check the waittype, waittime, lastwaittype, and the wai
tresource columns of
sysprocesses to see what activities each SPIDs is performing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Connie" <cfelt@.ga.wa.gov> wrote in message news:OcwIz0TWEHA.1144@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> I have one database that I keep getting an error 845: Time out ocurred whi
le
> waiting for buffer latch type 3 for page (0:0), database ID 15, object ID
> 569352192, Index ID 0..
> This only started happening last week. The database has not changed (othe
r
> than normal data entry) in over a year. Does anyone have any ideas of wha
t
> should be checked?
> Thanks in advance
>[/vbcol]
Monday, March 26, 2012
Error 845 running backup of one database
I have one database that I keep getting an error 845: Time out ocurred while
waiting for buffer latch type 3 for page (0:0), database ID 15, object ID
569352192, Index ID 0..
This only started happening last week. The database has not changed (other
than normal data entry) in over a year. Does anyone have any ideas of what
should be checked?
Thanks in advance
Did you see the specific recommendations in Books Online for this?
Error 845
Severity Level 17
Message Text
Time-out occurred while waiting for buffer latch type %d for page %S_PGID, database ID %d.
Explanation
When under a heavy stress load or high I/O conditions, your system may produce this message.
Action
This message can usually be ignored; however, if you receive repeated messages where the wait time
increases, it may indicate an internal server problem. Contact your system administrator. The system
administrator should check the waittype, waittime, lastwaittype, and the waitresource columns of
sysprocesses to see what activities each SPIDs is performing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Connie" <cfelt@.ga.wa.gov> wrote in message news:OcwIz0TWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> I have one database that I keep getting an error 845: Time out ocurred while
> waiting for buffer latch type 3 for page (0:0), database ID 15, object ID
> 569352192, Index ID 0..
> This only started happening last week. The database has not changed (other
> than normal data entry) in over a year. Does anyone have any ideas of what
> should be checked?
> Thanks in advance
>
sql
waiting for buffer latch type 3 for page (0:0), database ID 15, object ID
569352192, Index ID 0..
This only started happening last week. The database has not changed (other
than normal data entry) in over a year. Does anyone have any ideas of what
should be checked?
Thanks in advance
Did you see the specific recommendations in Books Online for this?
Error 845
Severity Level 17
Message Text
Time-out occurred while waiting for buffer latch type %d for page %S_PGID, database ID %d.
Explanation
When under a heavy stress load or high I/O conditions, your system may produce this message.
Action
This message can usually be ignored; however, if you receive repeated messages where the wait time
increases, it may indicate an internal server problem. Contact your system administrator. The system
administrator should check the waittype, waittime, lastwaittype, and the waitresource columns of
sysprocesses to see what activities each SPIDs is performing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Connie" <cfelt@.ga.wa.gov> wrote in message news:OcwIz0TWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> I have one database that I keep getting an error 845: Time out ocurred while
> waiting for buffer latch type 3 for page (0:0), database ID 15, object ID
> 569352192, Index ID 0..
> This only started happening last week. The database has not changed (other
> than normal data entry) in over a year. Does anyone have any ideas of what
> should be checked?
> Thanks in advance
>
sql
Error 845 running backup of one database
I have one database that I keep getting an error 845: Time out ocurred while
waiting for buffer latch type 3 for page (0:0), database ID 15, object ID
569352192, Index ID 0..
This only started happening last week. The database has not changed (other
than normal data entry) in over a year. Does anyone have any ideas of what
should be checked?
Thanks in advanceDid you see the specific recommendations in Books Online for this?
Error 845
Severity Level 17
Message Text
Time-out occurred while waiting for buffer latch type %d for page %S_PGID, database ID %d.
Explanation
When under a heavy stress load or high I/O conditions, your system may produce this message.
Action
This message can usually be ignored; however, if you receive repeated messages where the wait time
increases, it may indicate an internal server problem. Contact your system administrator. The system
administrator should check the waittype, waittime, lastwaittype, and the waitresource columns of
sysprocesses to see what activities each SPIDs is performing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Connie" <cfelt@.ga.wa.gov> wrote in message news:OcwIz0TWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> I have one database that I keep getting an error 845: Time out ocurred while
> waiting for buffer latch type 3 for page (0:0), database ID 15, object ID
> 569352192, Index ID 0..
> This only started happening last week. The database has not changed (other
> than normal data entry) in over a year. Does anyone have any ideas of what
> should be checked?
> Thanks in advance
>
waiting for buffer latch type 3 for page (0:0), database ID 15, object ID
569352192, Index ID 0..
This only started happening last week. The database has not changed (other
than normal data entry) in over a year. Does anyone have any ideas of what
should be checked?
Thanks in advanceDid you see the specific recommendations in Books Online for this?
Error 845
Severity Level 17
Message Text
Time-out occurred while waiting for buffer latch type %d for page %S_PGID, database ID %d.
Explanation
When under a heavy stress load or high I/O conditions, your system may produce this message.
Action
This message can usually be ignored; however, if you receive repeated messages where the wait time
increases, it may indicate an internal server problem. Contact your system administrator. The system
administrator should check the waittype, waittime, lastwaittype, and the waitresource columns of
sysprocesses to see what activities each SPIDs is performing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Connie" <cfelt@.ga.wa.gov> wrote in message news:OcwIz0TWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> I have one database that I keep getting an error 845: Time out ocurred while
> waiting for buffer latch type 3 for page (0:0), database ID 15, object ID
> 569352192, Index ID 0..
> This only started happening last week. The database has not changed (other
> than normal data entry) in over a year. Does anyone have any ideas of what
> should be checked?
> Thanks in advance
>
Error 823, Severity 24, State 2
Here is my problem:
About a month ago or so, I had some hardware problems with a dell server
running sharepoint portal server and SQL 2000 on the same server where the
server just died a couple of times requiring restart. We got the hardware
problems resolved, and at first, we had some errors about torn pages and
database suspect and we restored from a backup. Databases were no longer in
suspect mode and everything looked fine.
A couple of days ago, while looking at possibly moving the SQL databases to
a SQL cluster we have, I noticed that we are getting some errors while
performing certain actions on the server.
First, if I try to go and do a SQL backup from within SQL Enterprise
manager, I get:
Error 823: I/O error (torn page) detected during read at offset
0x00000000b18000 in file 'e:\Program files\microsoft SQL
server\mssql\data\<database name>'
Here is the kicker... this happens on EVERY database on that server at same
offset, and then it gets me into the sql server backup window to do the
backup. The backup goes fine and then I get another I/O error (without the
Error 823 in front) at the same offset.
The error that is logged in the eventviewer is
Event ID: 17052
Error: 823, severity: 24, State:2
I/O error (torn page) detected during read at offset 0x00000000b18000 in
file 'e:\Program files\microsoft SQL server\mssql\data\<database name>'
I have tried the following:
DBCC CHECKDB returns the following:
DBCC results for 'SSO'.
DBCC results for 'sysobjects'.
There are 61 rows in 1 pages for object 'sysobjects'.
.
.
DBCC results for 'SSO_Config'.
There are 1 rows in 1 pages for object 'SSO_Config'
CHECKDB found 0 allocation errors and 0 consistency errors in database
'SSO'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I ran DBCC CHECKCATALOG on some of the databases and got the following:
DBCC results for 'SSO'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Also ran DBCC CHECKALLOC and got:
CHECKALLOC found 0 allocation errors and 0 consistency errors in database
'SSO'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
So it seems as if everything is fine. People connect to the databases
through Sharepoint, and that seems to be working fine.
If someone could shed some light on this problem and how to correct, that
would be awesome and i would be very greatful.
Thank you in advance.
ChuckYou should contact Dell for the utilities to perform full hardware
diagnostics on the server.
The following KB's explain how to turn on an additional trace flag that will
allow you to gather more information when 823 errors occur
http://support.microsoft.com/default.aspx?scid=kb;en-us;828339
http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
Also, make sure that if you're using /PAE, you have the appropriate
hotfix/patches at the OS level to prevent issues like memory corruption
http://support.microsoft.com/default.aspx?scid=kb;en-us;838765
You can also run SQLIOStress to simulate a load to determine if you may have
hardware issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;231619#XSLTH4300121122120121120120
HTH.
"msnews.microsoft.com" <nonya@.bid.edu> wrote in message
news:eG%23mqey2FHA.1716@.TK2MSFTNGP10.phx.gbl...
> Here is my problem:
> About a month ago or so, I had some hardware problems with a dell server
> running sharepoint portal server and SQL 2000 on the same server where the
> server just died a couple of times requiring restart. We got the hardware
> problems resolved, and at first, we had some errors about torn pages and
> database suspect and we restored from a backup. Databases were no longer
> in suspect mode and everything looked fine.
> A couple of days ago, while looking at possibly moving the SQL databases
> to a SQL cluster we have, I noticed that we are getting some errors while
> performing certain actions on the server.
> First, if I try to go and do a SQL backup from within SQL Enterprise
> manager, I get:
> Error 823: I/O error (torn page) detected during read at offset
> 0x00000000b18000 in file 'e:\Program files\microsoft SQL
> server\mssql\data\<database name>'
> Here is the kicker... this happens on EVERY database on that server at
> same offset, and then it gets me into the sql server backup window to do
> the backup. The backup goes fine and then I get another I/O error
> (without the Error 823 in front) at the same offset.
> The error that is logged in the eventviewer is
> Event ID: 17052
> Error: 823, severity: 24, State:2
> I/O error (torn page) detected during read at offset 0x00000000b18000 in
> file 'e:\Program files\microsoft SQL server\mssql\data\<database name>'
> I have tried the following:
> DBCC CHECKDB returns the following:
> DBCC results for 'SSO'.
> DBCC results for 'sysobjects'.
> There are 61 rows in 1 pages for object 'sysobjects'.
> .
> .
> DBCC results for 'SSO_Config'.
> There are 1 rows in 1 pages for object 'SSO_Config'
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'SSO'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I ran DBCC CHECKCATALOG on some of the databases and got the following:
> DBCC results for 'SSO'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Also ran DBCC CHECKALLOC and got:
> CHECKALLOC found 0 allocation errors and 0 consistency errors in database
> 'SSO'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> So it seems as if everything is fine. People connect to the databases
> through Sharepoint, and that seems to be working fine.
> If someone could shed some light on this problem and how to correct, that
> would be awesome and i would be very greatful.
> Thank you in advance.
> Chuck
>
About a month ago or so, I had some hardware problems with a dell server
running sharepoint portal server and SQL 2000 on the same server where the
server just died a couple of times requiring restart. We got the hardware
problems resolved, and at first, we had some errors about torn pages and
database suspect and we restored from a backup. Databases were no longer in
suspect mode and everything looked fine.
A couple of days ago, while looking at possibly moving the SQL databases to
a SQL cluster we have, I noticed that we are getting some errors while
performing certain actions on the server.
First, if I try to go and do a SQL backup from within SQL Enterprise
manager, I get:
Error 823: I/O error (torn page) detected during read at offset
0x00000000b18000 in file 'e:\Program files\microsoft SQL
server\mssql\data\<database name>'
Here is the kicker... this happens on EVERY database on that server at same
offset, and then it gets me into the sql server backup window to do the
backup. The backup goes fine and then I get another I/O error (without the
Error 823 in front) at the same offset.
The error that is logged in the eventviewer is
Event ID: 17052
Error: 823, severity: 24, State:2
I/O error (torn page) detected during read at offset 0x00000000b18000 in
file 'e:\Program files\microsoft SQL server\mssql\data\<database name>'
I have tried the following:
DBCC CHECKDB returns the following:
DBCC results for 'SSO'.
DBCC results for 'sysobjects'.
There are 61 rows in 1 pages for object 'sysobjects'.
.
.
DBCC results for 'SSO_Config'.
There are 1 rows in 1 pages for object 'SSO_Config'
CHECKDB found 0 allocation errors and 0 consistency errors in database
'SSO'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I ran DBCC CHECKCATALOG on some of the databases and got the following:
DBCC results for 'SSO'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Also ran DBCC CHECKALLOC and got:
CHECKALLOC found 0 allocation errors and 0 consistency errors in database
'SSO'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
So it seems as if everything is fine. People connect to the databases
through Sharepoint, and that seems to be working fine.
If someone could shed some light on this problem and how to correct, that
would be awesome and i would be very greatful.
Thank you in advance.
ChuckYou should contact Dell for the utilities to perform full hardware
diagnostics on the server.
The following KB's explain how to turn on an additional trace flag that will
allow you to gather more information when 823 errors occur
http://support.microsoft.com/default.aspx?scid=kb;en-us;828339
http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
Also, make sure that if you're using /PAE, you have the appropriate
hotfix/patches at the OS level to prevent issues like memory corruption
http://support.microsoft.com/default.aspx?scid=kb;en-us;838765
You can also run SQLIOStress to simulate a load to determine if you may have
hardware issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;231619#XSLTH4300121122120121120120
HTH.
"msnews.microsoft.com" <nonya@.bid.edu> wrote in message
news:eG%23mqey2FHA.1716@.TK2MSFTNGP10.phx.gbl...
> Here is my problem:
> About a month ago or so, I had some hardware problems with a dell server
> running sharepoint portal server and SQL 2000 on the same server where the
> server just died a couple of times requiring restart. We got the hardware
> problems resolved, and at first, we had some errors about torn pages and
> database suspect and we restored from a backup. Databases were no longer
> in suspect mode and everything looked fine.
> A couple of days ago, while looking at possibly moving the SQL databases
> to a SQL cluster we have, I noticed that we are getting some errors while
> performing certain actions on the server.
> First, if I try to go and do a SQL backup from within SQL Enterprise
> manager, I get:
> Error 823: I/O error (torn page) detected during read at offset
> 0x00000000b18000 in file 'e:\Program files\microsoft SQL
> server\mssql\data\<database name>'
> Here is the kicker... this happens on EVERY database on that server at
> same offset, and then it gets me into the sql server backup window to do
> the backup. The backup goes fine and then I get another I/O error
> (without the Error 823 in front) at the same offset.
> The error that is logged in the eventviewer is
> Event ID: 17052
> Error: 823, severity: 24, State:2
> I/O error (torn page) detected during read at offset 0x00000000b18000 in
> file 'e:\Program files\microsoft SQL server\mssql\data\<database name>'
> I have tried the following:
> DBCC CHECKDB returns the following:
> DBCC results for 'SSO'.
> DBCC results for 'sysobjects'.
> There are 61 rows in 1 pages for object 'sysobjects'.
> .
> .
> DBCC results for 'SSO_Config'.
> There are 1 rows in 1 pages for object 'SSO_Config'
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'SSO'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I ran DBCC CHECKCATALOG on some of the databases and got the following:
> DBCC results for 'SSO'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Also ran DBCC CHECKALLOC and got:
> CHECKALLOC found 0 allocation errors and 0 consistency errors in database
> 'SSO'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> So it seems as if everything is fine. People connect to the databases
> through Sharepoint, and that seems to be working fine.
> If someone could shed some light on this problem and how to correct, that
> would be awesome and i would be very greatful.
> Thank you in advance.
> Chuck
>
Error 823 that I cant fix
Hi,
I am running SQL 2000 server for datawarehousing and I am getting the following error in the application log when I build large tables:
Error: 823, Severity: 24, State: 3
I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset 0x00000829618000 in file '<file name.mdf>'
I have run through MS Support and really haven't found helpful info on this. I don't know what process is locking the mdf file, but I don't beleive there are any server or even client side programs running against this datafile.
Any thoughts?
joeNot come across that error before, but I would try looking under the Managment section of your server in Enterprise Manager then Locks/Objects and see if you can spot an offending process. If there is one it may be worth trying to kill it.
I am running SQL 2000 server for datawarehousing and I am getting the following error in the application log when I build large tables:
Error: 823, Severity: 24, State: 3
I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset 0x00000829618000 in file '<file name.mdf>'
I have run through MS Support and really haven't found helpful info on this. I don't know what process is locking the mdf file, but I don't beleive there are any server or even client side programs running against this datafile.
Any thoughts?
joeNot come across that error before, but I would try looking under the Managment section of your server in Enterprise Manager then Locks/Objects and see if you can spot an offending process. If there is one it may be worth trying to kill it.
Error 823
I'm running W2000 server with SQL 7.0 SP2. we get the following error
Error 823, severity 24, state 1 , i/o error (bad page 10) detected
during read of BUF pointer= 0x11959fc80, page ptr=0x45724000, page id=
(0x3:0x777f), dbid=7, status= 0x 801, file=D:\masdata\monitordb.ndf
We continued restoring a backup, but after a few day, the database crash again.Microsoft has reported to fix this error in service pack 3,
FIX: Error 823 or 8966 Reported by SQL Server with Insufficient System Resources
Knowledge base article Q274310sql
Error 823, severity 24, state 1 , i/o error (bad page 10) detected
during read of BUF pointer= 0x11959fc80, page ptr=0x45724000, page id=
(0x3:0x777f), dbid=7, status= 0x 801, file=D:\masdata\monitordb.ndf
We continued restoring a backup, but after a few day, the database crash again.Microsoft has reported to fix this error in service pack 3,
FIX: Error 823 or 8966 Reported by SQL Server with Insufficient System Resources
Knowledge base article Q274310sql
Thursday, March 22, 2012
Error 8198: Could not obtain information about Windows NT group/user
Hi, we have a job that has been running for over 1 year. A few days
ago it stopped sending out an e-mail once the processing is done.
However, the job still runs and shows that it has been sucessful. I
have traced the problem to the select statement in the xp_sendmail
command below. When I comment out he query line, the command works
fine. The error I get is also below. Thanks in advance.
EXEC master..xp_sendmail
@.recipients = 'me@.company.com',
@.query = 'select COUNT(1) FROM DPEVENTD',
@.width = 160, @.no_header = 'TRUE',
@.subject = 'Test'
ODBC error 8198 (42000) Could not obtain information about Windows NT
group/user 'DOMAINNAME\me.
BTW, we have SQL2000 on Win2000/SP4. Thanks again.Is the job owner sa or a windows account ? That error usually means that SQL
could not get info back from a domain controller about the windows account.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Joe Kim" <palestine2000bc@.yahoo.com> wrote in message
news:386f7047.0307251717.323ab1a6@.posting.google.com...
Hi, we have a job that has been running for over 1 year. A few days
ago it stopped sending out an e-mail once the processing is done.
However, the job still runs and shows that it has been sucessful. I
have traced the problem to the select statement in the xp_sendmail
command below. When I comment out he query line, the command works
fine. The error I get is also below. Thanks in advance.
EXEC master..xp_sendmail
@.recipients = 'me@.company.com',
@.query = 'select COUNT(1) FROM DPEVENTD',
@.width = 160, @.no_header = 'TRUE',
@.subject = 'Test'
ODBC error 8198 (42000) Could not obtain information about Windows NT
group/user 'DOMAINNAME\me.
BTW, we have SQL2000 on Win2000/SP4. Thanks again.|||This error caused because of the mixed Windows NT and
Windows 2000 domain environment. You probably have both
Windows 2000 and NT servers in the same domain. You need
to have your account to be added into the 'Pre-Windows
2000 Compatible Access' group in the domain. The server
has to be rebooted after the change is made to take effect.
I have ran into the same issue some time ago.
Hope it helps.
>--Original Message--
>Hi Jasper, I set the user to sa on Friday and yes, the
xp_sendmail
>works now. However, if I run xp_sendmail code from my
desktop, I
>still get the same message. I would leave things the way
they are,
>but my manager wants me to fix this problem. Any idea
what I should
>do next? Microsoft has close to nothing on this. Thanks
for your
>help!
>"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
message news:<uD4uYu5UDHA.2268@.TK2MSFTNGP11.phx.gbl>...
>> Is the job owner sa or a windows account ? That error
usually means that SQL
>> could not get info back from a domain controller about
the windows account.
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Joe Kim" <palestine2000bc@.yahoo.com> wrote in message
>> news:386f7047.0307251717.323ab1a6@.posting.google.com...
>> Hi, we have a job that has been running for over 1
year. A few days
>> ago it stopped sending out an e-mail once the
processing is done.
>> However, the job still runs and shows that it has been
sucessful. I
>> have traced the problem to the select statement in the
xp_sendmail
>> command below. When I comment out he query line, the
command works
>> fine. The error I get is also below. Thanks in advance.
>> EXEC master..xp_sendmail
>> @.recipients = 'me@.company.com',
>> @.query = 'select COUNT(1) FROM DPEVENTD',
>> @.width = 160, @.no_header = 'TRUE',
>> @.subject = 'Test'
>> ODBC error 8198 (42000) Could not obtain information
about Windows NT
>> group/user 'DOMAINNAME\me.
>> BTW, we have SQL2000 on Win2000/SP4. Thanks again.
>.
>|||Your suggestion certainly did the job. Thanks for your help.
BTW, for those who are going through the same problem, you need to
restart your database server for it to be able to detect the changes
on the Domain Controller.
"Coskun" <cxd349@.yahoo.com> wrote in message news:<000701c355eb$e8525050$a401280a@.phx.gbl>...
> This error caused because of the mixed Windows NT and
> Windows 2000 domain environment. You probably have both
> Windows 2000 and NT servers in the same domain. You need
> to have your account to be added into the 'Pre-Windows
> 2000 Compatible Access' group in the domain. The server
> has to be rebooted after the change is made to take effect.
> I have ran into the same issue some time ago.
> Hope it helps.
>
> >--Original Message--
> >Hi Jasper, I set the user to sa on Friday and yes, the
> xp_sendmail
> >works now. However, if I run xp_sendmail code from my
> desktop, I
> >still get the same message. I would leave things the way
> they are,
> >but my manager wants me to fix this problem. Any idea
> what I should
> >do next? Microsoft has close to nothing on this. Thanks
> for your
> >help!
> >
> >"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
> message news:<uD4uYu5UDHA.2268@.TK2MSFTNGP11.phx.gbl>...
> >> Is the job owner sa or a windows account ? That error
> usually means that SQL
> >> could not get info back from a domain controller about
> the windows account.
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >>
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "Joe Kim" <palestine2000bc@.yahoo.com> wrote in message
> >> news:386f7047.0307251717.323ab1a6@.posting.google.com...
> >> Hi, we have a job that has been running for over 1
> year. A few days
> >> ago it stopped sending out an e-mail once the
> processing is done.
> >> However, the job still runs and shows that it has been
> sucessful. I
> >> have traced the problem to the select statement in the
> xp_sendmail
> >> command below. When I comment out he query line, the
> command works
> >> fine. The error I get is also below. Thanks in advance.
> >>
> >> EXEC master..xp_sendmail
> >> @.recipients = 'me@.company.com',
> >> @.query = 'select COUNT(1) FROM DPEVENTD',
> >> @.width = 160, @.no_header = 'TRUE',
> >> @.subject = 'Test'
> >>
> >> ODBC error 8198 (42000) Could not obtain information
> about Windows NT
> >> group/user 'DOMAINNAME\me.
> >>
> >> BTW, we have SQL2000 on Win2000/SP4. Thanks again.
> >.
> >
ago it stopped sending out an e-mail once the processing is done.
However, the job still runs and shows that it has been sucessful. I
have traced the problem to the select statement in the xp_sendmail
command below. When I comment out he query line, the command works
fine. The error I get is also below. Thanks in advance.
EXEC master..xp_sendmail
@.recipients = 'me@.company.com',
@.query = 'select COUNT(1) FROM DPEVENTD',
@.width = 160, @.no_header = 'TRUE',
@.subject = 'Test'
ODBC error 8198 (42000) Could not obtain information about Windows NT
group/user 'DOMAINNAME\me.
BTW, we have SQL2000 on Win2000/SP4. Thanks again.Is the job owner sa or a windows account ? That error usually means that SQL
could not get info back from a domain controller about the windows account.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Joe Kim" <palestine2000bc@.yahoo.com> wrote in message
news:386f7047.0307251717.323ab1a6@.posting.google.com...
Hi, we have a job that has been running for over 1 year. A few days
ago it stopped sending out an e-mail once the processing is done.
However, the job still runs and shows that it has been sucessful. I
have traced the problem to the select statement in the xp_sendmail
command below. When I comment out he query line, the command works
fine. The error I get is also below. Thanks in advance.
EXEC master..xp_sendmail
@.recipients = 'me@.company.com',
@.query = 'select COUNT(1) FROM DPEVENTD',
@.width = 160, @.no_header = 'TRUE',
@.subject = 'Test'
ODBC error 8198 (42000) Could not obtain information about Windows NT
group/user 'DOMAINNAME\me.
BTW, we have SQL2000 on Win2000/SP4. Thanks again.|||This error caused because of the mixed Windows NT and
Windows 2000 domain environment. You probably have both
Windows 2000 and NT servers in the same domain. You need
to have your account to be added into the 'Pre-Windows
2000 Compatible Access' group in the domain. The server
has to be rebooted after the change is made to take effect.
I have ran into the same issue some time ago.
Hope it helps.
>--Original Message--
>Hi Jasper, I set the user to sa on Friday and yes, the
xp_sendmail
>works now. However, if I run xp_sendmail code from my
desktop, I
>still get the same message. I would leave things the way
they are,
>but my manager wants me to fix this problem. Any idea
what I should
>do next? Microsoft has close to nothing on this. Thanks
for your
>help!
>"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
message news:<uD4uYu5UDHA.2268@.TK2MSFTNGP11.phx.gbl>...
>> Is the job owner sa or a windows account ? That error
usually means that SQL
>> could not get info back from a domain controller about
the windows account.
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Joe Kim" <palestine2000bc@.yahoo.com> wrote in message
>> news:386f7047.0307251717.323ab1a6@.posting.google.com...
>> Hi, we have a job that has been running for over 1
year. A few days
>> ago it stopped sending out an e-mail once the
processing is done.
>> However, the job still runs and shows that it has been
sucessful. I
>> have traced the problem to the select statement in the
xp_sendmail
>> command below. When I comment out he query line, the
command works
>> fine. The error I get is also below. Thanks in advance.
>> EXEC master..xp_sendmail
>> @.recipients = 'me@.company.com',
>> @.query = 'select COUNT(1) FROM DPEVENTD',
>> @.width = 160, @.no_header = 'TRUE',
>> @.subject = 'Test'
>> ODBC error 8198 (42000) Could not obtain information
about Windows NT
>> group/user 'DOMAINNAME\me.
>> BTW, we have SQL2000 on Win2000/SP4. Thanks again.
>.
>|||Your suggestion certainly did the job. Thanks for your help.
BTW, for those who are going through the same problem, you need to
restart your database server for it to be able to detect the changes
on the Domain Controller.
"Coskun" <cxd349@.yahoo.com> wrote in message news:<000701c355eb$e8525050$a401280a@.phx.gbl>...
> This error caused because of the mixed Windows NT and
> Windows 2000 domain environment. You probably have both
> Windows 2000 and NT servers in the same domain. You need
> to have your account to be added into the 'Pre-Windows
> 2000 Compatible Access' group in the domain. The server
> has to be rebooted after the change is made to take effect.
> I have ran into the same issue some time ago.
> Hope it helps.
>
> >--Original Message--
> >Hi Jasper, I set the user to sa on Friday and yes, the
> xp_sendmail
> >works now. However, if I run xp_sendmail code from my
> desktop, I
> >still get the same message. I would leave things the way
> they are,
> >but my manager wants me to fix this problem. Any idea
> what I should
> >do next? Microsoft has close to nothing on this. Thanks
> for your
> >help!
> >
> >"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
> message news:<uD4uYu5UDHA.2268@.TK2MSFTNGP11.phx.gbl>...
> >> Is the job owner sa or a windows account ? That error
> usually means that SQL
> >> could not get info back from a domain controller about
> the windows account.
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >>
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "Joe Kim" <palestine2000bc@.yahoo.com> wrote in message
> >> news:386f7047.0307251717.323ab1a6@.posting.google.com...
> >> Hi, we have a job that has been running for over 1
> year. A few days
> >> ago it stopped sending out an e-mail once the
> processing is done.
> >> However, the job still runs and shows that it has been
> sucessful. I
> >> have traced the problem to the select statement in the
> xp_sendmail
> >> command below. When I comment out he query line, the
> command works
> >> fine. The error I get is also below. Thanks in advance.
> >>
> >> EXEC master..xp_sendmail
> >> @.recipients = 'me@.company.com',
> >> @.query = 'select COUNT(1) FROM DPEVENTD',
> >> @.width = 160, @.no_header = 'TRUE',
> >> @.subject = 'Test'
> >>
> >> ODBC error 8198 (42000) Could not obtain information
> about Windows NT
> >> group/user 'DOMAINNAME\me.
> >>
> >> BTW, we have SQL2000 on Win2000/SP4. Thanks again.
> >.
> >
Error 8114 on big query - how to hone it down?
I have a big select into statement I'm running from QA (on the same machine
where the DB is). I've used it in the past, and it worked then. It's with
the NBI (National Bridge Inventory), trying to convert some data types, so
there are over 600,000 records with 120 or so fields each. It runs for
awhile, and I don't what what record or field produces the problem. How can
I get more info?
Every field in the source table is NVARCHAR.
The message after 5 minutes or so is:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric
TIA -
Mark
The statement:
select
identity(int,1,1) as ID,
Item1 as StateCode_1,
Item2 as HighwayDistrict_2,
Item3 as CountyCode_3,
Item4 as PlaceCode_4,
Item5a as InvenRouteRecordType_5a,
Item5b as InvenRouteSigningPrefix_5b,
Item5c as InvenRouteLevelService_5c,
item5d as InvenRouteNumber_5d,
Item5e as InvenRouteDirSuffix_5e,
Item6a as FeaturesIntersected_6a,
Item7 as FacilityCarried_7,
Item8 as StructureNumber_8,
Item9 as LocationNarrative_9,
-- 99.99 is a possible vert clearance (over 30 meters), don't care
case PATINDEX('%[^0-9.]%',Item10)
when 0 then cast(Item10 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as InvenRouteMinVertClearance_10,
case PATINDEX('%[^0-9.]%',Item11)
when 0 then cast(Item11 as decimal(12,3))/1000
else cast(0 as decimal(12,3))
end as LrsKilometerPoint_11,
Item12 as IsOnBaseHighwayNetwork_12,
Item13a as LrsInvenRoute_13a,
Item13b as LrsSubroute_13b,
Item16 as Latitude_16,
Item17 as Longitude_17,
-- detours over 199 km are coded as 199
case PATINDEX('%[^0-9]%',Item19)
when 0 then cast(Item19 as int)
else cast(0 as int)
end as BypassDetourKilometers_19,
Item20 as TollCode_20,
Item21 as MaintRespCode_21,
Item22 as MaintRespOwnerCode_22,
Item26 as InvenRouteFunctionClass_26,
Item27 as YearBuilt_27,
case PATINDEX('%[^0-9]%',Item28a)
when 0 then cast(Item28a as int)
else cast(0 as int)
end as LanesOn_28a,
case PATINDEX('%[^0-9]%',Item28b)
when 0 then cast(Item28b as int)
else cast(0 as int)
end as LanesUnder_28b,
case PATINDEX('%[^0-9]%',Item29)
when 0 then cast(Item29 as int)
else cast(0 as int)
end as AvgDailyTraffic_29,
Item30 as AvgDailyTrafficYear_30,
Item31 as DesignLoadCode_31,
case PATINDEX('%[^0-9.]%',Item32)
when 0 then cast(Item32 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as ApproachRoadwayWidth_32,
Item33 as MedianExistOpenClosed_33,
case PATINDEX('%[^0-9]%',Item34)
when 0 then cast(Item34 as int)
else cast(0 as int)
end as RoadwayPierSkewDegrees_34,
Item35 as StructureIsFlared_35,
Item36a as TsRailings_36a,
Item36b as TsTransitions_36b,
Item36c as TsApprGuardrail_36c,
item36d as TsApprGuardrailEnds_36d,
Item37 as HistoricSigCode_37,
Item38 as NavControlCode_38,
case PATINDEX('%[^0-9.]%',Item39)
when 0 then cast(Item39 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavVertClearance_39,
case PATINDEX('%[^0-9.]%',Item40)
when 0 then cast(Item40 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as NavHorzClearance_40,
Item41 as OpenPostedClosedCode_41,
Item42a as ServiceTypeOnCode_42a,
item42b as ServiceTypeUnderCode_42b,
Item43a as StructureMaterialTypeCode_43a,
Item43b as StructureTypeCode_43b,
Item44a as ApproachMaterialTypeCode_44a,
Item44b as ApproachStructureTypeCode_44b,
case PATINDEX('%[^0-9]%',Item45)
when 0 then cast(Item45 as int)
else cast(0 as int)
end as MainSpans_45,
case PATINDEX('%[^0-9]%',Item46)
when 0 then cast(Item46 as int)
else cast(0 as int)
end as ApproachSpans_46,
-- 100 meters or greater coded as 999, leave as is
case PATINDEX('%[^0-9.]%',Item47)
when 0 then cast(Item47 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRouteHorzClearance_47,
case PATINDEX('%[^0-9.]%',Item48)
when 0 then cast(Item48 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as MaxSpanLength_48,
case PATINDEX('%[^0-9.]%',Item49)
when 0 then cast(Item49 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as StructureLength_49,
case PATINDEX('%[^0-9.]%',Item50a)
when 0 then cast(Item50a as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthLeft_50a,
case PATINDEX('%[^0-9.]%',Item50b)
when 0 then cast(Item50b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthRight_50b,
case PATINDEX('%[^0-9.]%',Item51)
when 0 then cast(Item51 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbToCurbRoadwayWidth_51,
case PATINDEX('%[^0-9.]%',Item52)
when 0 then cast(Item52 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as DeckWidth_52,
-- Item53: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item53)
when 0 then cast(Item53 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceOverRoadway_53,
Item54a as VertClearanceTypeUnderStructure_54a,
-- Item54b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item54b)
when 0 then cast(Item54b as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceUnderStructure_54b,
Item55a as LatClearanceTypeUnderStructure_55a,
-- Item55b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item55b)
when 0 then cast(Item55b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderStructure_55b,
-- Item56: 999 for "open", 998 for greater than 30 meters
case
when Item56 = '999' then cast(0 as decimal(7,1))
when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderOnLeft_56,
Item58 as DeckConditionCode_58,
Item59 as SuperstructureConditionCode_59,
Item60 as SubstructureConditionCode_60,
Item61 as ChannelConditionCode_61,
Item62 as CulvertConditionCode_62,
Item63 as OperationRatingMethodCode_63,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item64)
when 0 then cast(Item64 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as OperatingRatingMetricTons_64,
Item65 as InvenRatingLoadMethodCode_65,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item66)
when 0 then cast(Item66 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRatingMetricTons_66,
Item67 as StructureEvalCode_67,
Item68 as DeckGeoEvalCode_68,
Item69 as UnderclearanceEvalCode_69,
Item70 as BridgePostingCode_70,
Item71 as WaterwayAdequacyCode_71,
item772 as ApprRoadwayAlignmentCode_72,
Item75a as WorkTypeCode_75a,
Item75b as WorkerTypeCode_75b,
case PATINDEX('%[^0-9.]%',Item76)
when 0 then cast(Item76 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as ImprovementSpan_76,
Item90 as LastInspectionMonthYear_90,
Item91 as InspectionFrequencyMonths_91,
item92a as CfiFractureCriticalDetailsCode_92a,
Item92b as CfiUnderwaterInspectionCode_92b,
Item92c as CfiOtherSpecialInspectionCode_92c,
Item93a as CfiFractureCriticalDetailsMonthYear_93a,
Item93b as CfiUnderwaterInspectionMonthYear_93b,
item93c as CfiOtherSpecialInspectionMonthYear_93c,
case PATINDEX('%[^0-9]%',Item94)
when 0 then cast(Item94 as decimal)*1000
else cast(0 as decimal)
end as BridgeImprovementCost_94,
case PATINDEX('%[^0-9]%',Item95)
when 0 then cast(Item95 as decimal)*1000
else cast(0 as decimal)
end as RoadwayImprovementCost_95,
case PATINDEX('%[^0-9]%',Item96)
when 0 then cast(Item96 as decimal)*1000
else cast(0 as decimal)
end as TotalImprovementCost_96,
Item97 as ImprovementCostEstYear_97,
Item98a as BorderBrNeighborStateCode_98a,
-- 99 means no responsibility for the structure
case
when Item98b = '99' then cast(0 as int)
when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
else cast(0 as int)
end as BorderBrPctResponsibility_98b,
-- This one could be an NBI id, or a state structure number
Item99 as BorderBrNeighborsStructureId_99,
Item100 as StrahnetHighwayDesCode_100,
Item101 as ParallelStructureDesCode_101,
Item102 as InvenRouteTrafficDirCode_102,
Item103 as TemporaryStructure_103,
Item104 as IsOnNhs_104,
Item105 as FederalLandsHighwayCode_105,
Item106 as YearReconstructed_106,
Item107 as DeckTypeCode_107,
Item108a as DeckProtectSurfaceTypeCode_108a,
Item108b as DeckProtectMembraneTypeCode_108b,
Item108c as DeckProtectTypeCode_108c,
case PATINDEX('%[^0-9]%',Item109)
when 0 then cast(Item109 as int)
else cast(0 as int)
end as AvgDailyTruckTrafficPct_109,
Item110 as IsOnNatlTruckNetwork_110,
Item111 as PierOrAbutmentProtectionCode_111,
Item112 as NbisLengthYesNo_112,
Item113 as ScourCriticalCode_113,
case PATINDEX('%[^0-9]%',Item114)
when 0 then cast(Item114 as int)
else cast(0 as int)
end as FutureAvgDailyTraffic_114,
Item115 as FutureAvgDailyTrafficYear_115,
case PATINDEX('%[^0-9.]%',Item116)
when 0 then cast(Item116 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavMinVertClearanceLiftBrClosed_116,
cast(' ' as varchar(22)) as CountyName,
cast(' ' as varchar(52)) as PlaceName
into STEEL
from NBI
GO
Sometimes something about posting to usenet makes it work...
There are decimal points in the PATINDEX functions. I suppose if there is
one (or two) in a source field, then it will try to convert. Got rid of
those and it works.
Mark
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
etc...
|||You can add ISNUMERIC to your CASE in order to identify other invalid data,
such as multiple decimal points and empty strings:
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN CAST(Item10 AS decimal(7,2))/100
ELSE CAST(0 AS decimal(7,2)) END
If you need to identify rows with invalid data, specify the CASE statements
in a WHERE clause.
WHERE 'Invalid' =
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN 'Valid'
ELSE 'invalid' END
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark G. Meyers" <mmeyers[at]hydromilling.com> wrote in message
news:ep5pRIqYEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I have a big select into statement I'm running from QA (on the same
machine
> where the DB is). I've used it in the past, and it worked then. It's
with
> the NBI (National Bridge Inventory), trying to convert some data types, so
> there are over 600,000 records with 120 or so fields each. It runs for
> awhile, and I don't what what record or field produces the problem. How
can
> I get more info?
> Every field in the source table is NVARCHAR.
> The message after 5 minutes or so is:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type nvarchar to numeric
> TIA -
> Mark
> The statement:
> select
> identity(int,1,1) as ID,
> Item1 as StateCode_1,
> Item2 as HighwayDistrict_2,
> Item3 as CountyCode_3,
> Item4 as PlaceCode_4,
> Item5a as InvenRouteRecordType_5a,
> Item5b as InvenRouteSigningPrefix_5b,
> Item5c as InvenRouteLevelService_5c,
> item5d as InvenRouteNumber_5d,
> Item5e as InvenRouteDirSuffix_5e,
> Item6a as FeaturesIntersected_6a,
> Item7 as FacilityCarried_7,
> Item8 as StructureNumber_8,
> Item9 as LocationNarrative_9,
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
> Item12 as IsOnBaseHighwayNetwork_12,
> Item13a as LrsInvenRoute_13a,
> Item13b as LrsSubroute_13b,
> Item16 as Latitude_16,
> Item17 as Longitude_17,
> -- detours over 199 km are coded as 199
> case PATINDEX('%[^0-9]%',Item19)
> when 0 then cast(Item19 as int)
> else cast(0 as int)
> end as BypassDetourKilometers_19,
> Item20 as TollCode_20,
> Item21 as MaintRespCode_21,
> Item22 as MaintRespOwnerCode_22,
> Item26 as InvenRouteFunctionClass_26,
> Item27 as YearBuilt_27,
> case PATINDEX('%[^0-9]%',Item28a)
> when 0 then cast(Item28a as int)
> else cast(0 as int)
> end as LanesOn_28a,
> case PATINDEX('%[^0-9]%',Item28b)
> when 0 then cast(Item28b as int)
> else cast(0 as int)
> end as LanesUnder_28b,
> case PATINDEX('%[^0-9]%',Item29)
> when 0 then cast(Item29 as int)
> else cast(0 as int)
> end as AvgDailyTraffic_29,
> Item30 as AvgDailyTrafficYear_30,
> Item31 as DesignLoadCode_31,
> case PATINDEX('%[^0-9.]%',Item32)
> when 0 then cast(Item32 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as ApproachRoadwayWidth_32,
> Item33 as MedianExistOpenClosed_33,
> case PATINDEX('%[^0-9]%',Item34)
> when 0 then cast(Item34 as int)
> else cast(0 as int)
> end as RoadwayPierSkewDegrees_34,
> Item35 as StructureIsFlared_35,
> Item36a as TsRailings_36a,
> Item36b as TsTransitions_36b,
> Item36c as TsApprGuardrail_36c,
> item36d as TsApprGuardrailEnds_36d,
> Item37 as HistoricSigCode_37,
> Item38 as NavControlCode_38,
> case PATINDEX('%[^0-9.]%',Item39)
> when 0 then cast(Item39 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavVertClearance_39,
> case PATINDEX('%[^0-9.]%',Item40)
> when 0 then cast(Item40 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as NavHorzClearance_40,
> Item41 as OpenPostedClosedCode_41,
> Item42a as ServiceTypeOnCode_42a,
> item42b as ServiceTypeUnderCode_42b,
> Item43a as StructureMaterialTypeCode_43a,
> Item43b as StructureTypeCode_43b,
> Item44a as ApproachMaterialTypeCode_44a,
> Item44b as ApproachStructureTypeCode_44b,
> case PATINDEX('%[^0-9]%',Item45)
> when 0 then cast(Item45 as int)
> else cast(0 as int)
> end as MainSpans_45,
> case PATINDEX('%[^0-9]%',Item46)
> when 0 then cast(Item46 as int)
> else cast(0 as int)
> end as ApproachSpans_46,
> -- 100 meters or greater coded as 999, leave as is
> case PATINDEX('%[^0-9.]%',Item47)
> when 0 then cast(Item47 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRouteHorzClearance_47,
> case PATINDEX('%[^0-9.]%',Item48)
> when 0 then cast(Item48 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as MaxSpanLength_48,
> case PATINDEX('%[^0-9.]%',Item49)
> when 0 then cast(Item49 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as StructureLength_49,
> case PATINDEX('%[^0-9.]%',Item50a)
> when 0 then cast(Item50a as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthLeft_50a,
> case PATINDEX('%[^0-9.]%',Item50b)
> when 0 then cast(Item50b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthRight_50b,
> case PATINDEX('%[^0-9.]%',Item51)
> when 0 then cast(Item51 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbToCurbRoadwayWidth_51,
> case PATINDEX('%[^0-9.]%',Item52)
> when 0 then cast(Item52 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as DeckWidth_52,
> -- Item53: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item53)
> when 0 then cast(Item53 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceOverRoadway_53,
> Item54a as VertClearanceTypeUnderStructure_54a,
> -- Item54b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item54b)
> when 0 then cast(Item54b as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceUnderStructure_54b,
> Item55a as LatClearanceTypeUnderStructure_55a,
> -- Item55b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item55b)
> when 0 then cast(Item55b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderStructure_55b,
> -- Item56: 999 for "open", 998 for greater than 30 meters
> case
> when Item56 = '999' then cast(0 as decimal(7,1))
> when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as
decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderOnLeft_56,
> Item58 as DeckConditionCode_58,
> Item59 as SuperstructureConditionCode_59,
> Item60 as SubstructureConditionCode_60,
> Item61 as ChannelConditionCode_61,
> Item62 as CulvertConditionCode_62,
> Item63 as OperationRatingMethodCode_63,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item64)
> when 0 then cast(Item64 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as OperatingRatingMetricTons_64,
> Item65 as InvenRatingLoadMethodCode_65,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item66)
> when 0 then cast(Item66 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRatingMetricTons_66,
> Item67 as StructureEvalCode_67,
> Item68 as DeckGeoEvalCode_68,
> Item69 as UnderclearanceEvalCode_69,
> Item70 as BridgePostingCode_70,
> Item71 as WaterwayAdequacyCode_71,
> item772 as ApprRoadwayAlignmentCode_72,
> Item75a as WorkTypeCode_75a,
> Item75b as WorkerTypeCode_75b,
> case PATINDEX('%[^0-9.]%',Item76)
> when 0 then cast(Item76 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as ImprovementSpan_76,
> Item90 as LastInspectionMonthYear_90,
> Item91 as InspectionFrequencyMonths_91,
> item92a as CfiFractureCriticalDetailsCode_92a,
> Item92b as CfiUnderwaterInspectionCode_92b,
> Item92c as CfiOtherSpecialInspectionCode_92c,
> Item93a as CfiFractureCriticalDetailsMonthYear_93a,
> Item93b as CfiUnderwaterInspectionMonthYear_93b,
> item93c as CfiOtherSpecialInspectionMonthYear_93c,
> case PATINDEX('%[^0-9]%',Item94)
> when 0 then cast(Item94 as decimal)*1000
> else cast(0 as decimal)
> end as BridgeImprovementCost_94,
> case PATINDEX('%[^0-9]%',Item95)
> when 0 then cast(Item95 as decimal)*1000
> else cast(0 as decimal)
> end as RoadwayImprovementCost_95,
> case PATINDEX('%[^0-9]%',Item96)
> when 0 then cast(Item96 as decimal)*1000
> else cast(0 as decimal)
> end as TotalImprovementCost_96,
> Item97 as ImprovementCostEstYear_97,
> Item98a as BorderBrNeighborStateCode_98a,
> -- 99 means no responsibility for the structure
> case
> when Item98b = '99' then cast(0 as int)
> when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
> else cast(0 as int)
> end as BorderBrPctResponsibility_98b,
> -- This one could be an NBI id, or a state structure number
> Item99 as BorderBrNeighborsStructureId_99,
> Item100 as StrahnetHighwayDesCode_100,
> Item101 as ParallelStructureDesCode_101,
> Item102 as InvenRouteTrafficDirCode_102,
> Item103 as TemporaryStructure_103,
> Item104 as IsOnNhs_104,
> Item105 as FederalLandsHighwayCode_105,
> Item106 as YearReconstructed_106,
> Item107 as DeckTypeCode_107,
> Item108a as DeckProtectSurfaceTypeCode_108a,
> Item108b as DeckProtectMembraneTypeCode_108b,
> Item108c as DeckProtectTypeCode_108c,
> case PATINDEX('%[^0-9]%',Item109)
> when 0 then cast(Item109 as int)
> else cast(0 as int)
> end as AvgDailyTruckTrafficPct_109,
> Item110 as IsOnNatlTruckNetwork_110,
> Item111 as PierOrAbutmentProtectionCode_111,
> Item112 as NbisLengthYesNo_112,
> Item113 as ScourCriticalCode_113,
> case PATINDEX('%[^0-9]%',Item114)
> when 0 then cast(Item114 as int)
> else cast(0 as int)
> end as FutureAvgDailyTraffic_114,
> Item115 as FutureAvgDailyTrafficYear_115,
> case PATINDEX('%[^0-9.]%',Item116)
> when 0 then cast(Item116 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavMinVertClearanceLiftBrClosed_116,
> cast(' ' as varchar(22)) as CountyName,
> cast(' ' as varchar(52)) as PlaceName
> into STEEL
> from NBI
> GO
>
where the DB is). I've used it in the past, and it worked then. It's with
the NBI (National Bridge Inventory), trying to convert some data types, so
there are over 600,000 records with 120 or so fields each. It runs for
awhile, and I don't what what record or field produces the problem. How can
I get more info?
Every field in the source table is NVARCHAR.
The message after 5 minutes or so is:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric
TIA -
Mark
The statement:
select
identity(int,1,1) as ID,
Item1 as StateCode_1,
Item2 as HighwayDistrict_2,
Item3 as CountyCode_3,
Item4 as PlaceCode_4,
Item5a as InvenRouteRecordType_5a,
Item5b as InvenRouteSigningPrefix_5b,
Item5c as InvenRouteLevelService_5c,
item5d as InvenRouteNumber_5d,
Item5e as InvenRouteDirSuffix_5e,
Item6a as FeaturesIntersected_6a,
Item7 as FacilityCarried_7,
Item8 as StructureNumber_8,
Item9 as LocationNarrative_9,
-- 99.99 is a possible vert clearance (over 30 meters), don't care
case PATINDEX('%[^0-9.]%',Item10)
when 0 then cast(Item10 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as InvenRouteMinVertClearance_10,
case PATINDEX('%[^0-9.]%',Item11)
when 0 then cast(Item11 as decimal(12,3))/1000
else cast(0 as decimal(12,3))
end as LrsKilometerPoint_11,
Item12 as IsOnBaseHighwayNetwork_12,
Item13a as LrsInvenRoute_13a,
Item13b as LrsSubroute_13b,
Item16 as Latitude_16,
Item17 as Longitude_17,
-- detours over 199 km are coded as 199
case PATINDEX('%[^0-9]%',Item19)
when 0 then cast(Item19 as int)
else cast(0 as int)
end as BypassDetourKilometers_19,
Item20 as TollCode_20,
Item21 as MaintRespCode_21,
Item22 as MaintRespOwnerCode_22,
Item26 as InvenRouteFunctionClass_26,
Item27 as YearBuilt_27,
case PATINDEX('%[^0-9]%',Item28a)
when 0 then cast(Item28a as int)
else cast(0 as int)
end as LanesOn_28a,
case PATINDEX('%[^0-9]%',Item28b)
when 0 then cast(Item28b as int)
else cast(0 as int)
end as LanesUnder_28b,
case PATINDEX('%[^0-9]%',Item29)
when 0 then cast(Item29 as int)
else cast(0 as int)
end as AvgDailyTraffic_29,
Item30 as AvgDailyTrafficYear_30,
Item31 as DesignLoadCode_31,
case PATINDEX('%[^0-9.]%',Item32)
when 0 then cast(Item32 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as ApproachRoadwayWidth_32,
Item33 as MedianExistOpenClosed_33,
case PATINDEX('%[^0-9]%',Item34)
when 0 then cast(Item34 as int)
else cast(0 as int)
end as RoadwayPierSkewDegrees_34,
Item35 as StructureIsFlared_35,
Item36a as TsRailings_36a,
Item36b as TsTransitions_36b,
Item36c as TsApprGuardrail_36c,
item36d as TsApprGuardrailEnds_36d,
Item37 as HistoricSigCode_37,
Item38 as NavControlCode_38,
case PATINDEX('%[^0-9.]%',Item39)
when 0 then cast(Item39 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavVertClearance_39,
case PATINDEX('%[^0-9.]%',Item40)
when 0 then cast(Item40 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as NavHorzClearance_40,
Item41 as OpenPostedClosedCode_41,
Item42a as ServiceTypeOnCode_42a,
item42b as ServiceTypeUnderCode_42b,
Item43a as StructureMaterialTypeCode_43a,
Item43b as StructureTypeCode_43b,
Item44a as ApproachMaterialTypeCode_44a,
Item44b as ApproachStructureTypeCode_44b,
case PATINDEX('%[^0-9]%',Item45)
when 0 then cast(Item45 as int)
else cast(0 as int)
end as MainSpans_45,
case PATINDEX('%[^0-9]%',Item46)
when 0 then cast(Item46 as int)
else cast(0 as int)
end as ApproachSpans_46,
-- 100 meters or greater coded as 999, leave as is
case PATINDEX('%[^0-9.]%',Item47)
when 0 then cast(Item47 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRouteHorzClearance_47,
case PATINDEX('%[^0-9.]%',Item48)
when 0 then cast(Item48 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as MaxSpanLength_48,
case PATINDEX('%[^0-9.]%',Item49)
when 0 then cast(Item49 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as StructureLength_49,
case PATINDEX('%[^0-9.]%',Item50a)
when 0 then cast(Item50a as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthLeft_50a,
case PATINDEX('%[^0-9.]%',Item50b)
when 0 then cast(Item50b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthRight_50b,
case PATINDEX('%[^0-9.]%',Item51)
when 0 then cast(Item51 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbToCurbRoadwayWidth_51,
case PATINDEX('%[^0-9.]%',Item52)
when 0 then cast(Item52 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as DeckWidth_52,
-- Item53: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item53)
when 0 then cast(Item53 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceOverRoadway_53,
Item54a as VertClearanceTypeUnderStructure_54a,
-- Item54b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item54b)
when 0 then cast(Item54b as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceUnderStructure_54b,
Item55a as LatClearanceTypeUnderStructure_55a,
-- Item55b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item55b)
when 0 then cast(Item55b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderStructure_55b,
-- Item56: 999 for "open", 998 for greater than 30 meters
case
when Item56 = '999' then cast(0 as decimal(7,1))
when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderOnLeft_56,
Item58 as DeckConditionCode_58,
Item59 as SuperstructureConditionCode_59,
Item60 as SubstructureConditionCode_60,
Item61 as ChannelConditionCode_61,
Item62 as CulvertConditionCode_62,
Item63 as OperationRatingMethodCode_63,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item64)
when 0 then cast(Item64 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as OperatingRatingMetricTons_64,
Item65 as InvenRatingLoadMethodCode_65,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item66)
when 0 then cast(Item66 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRatingMetricTons_66,
Item67 as StructureEvalCode_67,
Item68 as DeckGeoEvalCode_68,
Item69 as UnderclearanceEvalCode_69,
Item70 as BridgePostingCode_70,
Item71 as WaterwayAdequacyCode_71,
item772 as ApprRoadwayAlignmentCode_72,
Item75a as WorkTypeCode_75a,
Item75b as WorkerTypeCode_75b,
case PATINDEX('%[^0-9.]%',Item76)
when 0 then cast(Item76 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as ImprovementSpan_76,
Item90 as LastInspectionMonthYear_90,
Item91 as InspectionFrequencyMonths_91,
item92a as CfiFractureCriticalDetailsCode_92a,
Item92b as CfiUnderwaterInspectionCode_92b,
Item92c as CfiOtherSpecialInspectionCode_92c,
Item93a as CfiFractureCriticalDetailsMonthYear_93a,
Item93b as CfiUnderwaterInspectionMonthYear_93b,
item93c as CfiOtherSpecialInspectionMonthYear_93c,
case PATINDEX('%[^0-9]%',Item94)
when 0 then cast(Item94 as decimal)*1000
else cast(0 as decimal)
end as BridgeImprovementCost_94,
case PATINDEX('%[^0-9]%',Item95)
when 0 then cast(Item95 as decimal)*1000
else cast(0 as decimal)
end as RoadwayImprovementCost_95,
case PATINDEX('%[^0-9]%',Item96)
when 0 then cast(Item96 as decimal)*1000
else cast(0 as decimal)
end as TotalImprovementCost_96,
Item97 as ImprovementCostEstYear_97,
Item98a as BorderBrNeighborStateCode_98a,
-- 99 means no responsibility for the structure
case
when Item98b = '99' then cast(0 as int)
when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
else cast(0 as int)
end as BorderBrPctResponsibility_98b,
-- This one could be an NBI id, or a state structure number
Item99 as BorderBrNeighborsStructureId_99,
Item100 as StrahnetHighwayDesCode_100,
Item101 as ParallelStructureDesCode_101,
Item102 as InvenRouteTrafficDirCode_102,
Item103 as TemporaryStructure_103,
Item104 as IsOnNhs_104,
Item105 as FederalLandsHighwayCode_105,
Item106 as YearReconstructed_106,
Item107 as DeckTypeCode_107,
Item108a as DeckProtectSurfaceTypeCode_108a,
Item108b as DeckProtectMembraneTypeCode_108b,
Item108c as DeckProtectTypeCode_108c,
case PATINDEX('%[^0-9]%',Item109)
when 0 then cast(Item109 as int)
else cast(0 as int)
end as AvgDailyTruckTrafficPct_109,
Item110 as IsOnNatlTruckNetwork_110,
Item111 as PierOrAbutmentProtectionCode_111,
Item112 as NbisLengthYesNo_112,
Item113 as ScourCriticalCode_113,
case PATINDEX('%[^0-9]%',Item114)
when 0 then cast(Item114 as int)
else cast(0 as int)
end as FutureAvgDailyTraffic_114,
Item115 as FutureAvgDailyTrafficYear_115,
case PATINDEX('%[^0-9.]%',Item116)
when 0 then cast(Item116 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavMinVertClearanceLiftBrClosed_116,
cast(' ' as varchar(22)) as CountyName,
cast(' ' as varchar(52)) as PlaceName
into STEEL
from NBI
GO
Sometimes something about posting to usenet makes it work...
There are decimal points in the PATINDEX functions. I suppose if there is
one (or two) in a source field, then it will try to convert. Got rid of
those and it works.
Mark
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
etc...
|||You can add ISNUMERIC to your CASE in order to identify other invalid data,
such as multiple decimal points and empty strings:
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN CAST(Item10 AS decimal(7,2))/100
ELSE CAST(0 AS decimal(7,2)) END
If you need to identify rows with invalid data, specify the CASE statements
in a WHERE clause.
WHERE 'Invalid' =
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN 'Valid'
ELSE 'invalid' END
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark G. Meyers" <mmeyers[at]hydromilling.com> wrote in message
news:ep5pRIqYEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I have a big select into statement I'm running from QA (on the same
machine
> where the DB is). I've used it in the past, and it worked then. It's
with
> the NBI (National Bridge Inventory), trying to convert some data types, so
> there are over 600,000 records with 120 or so fields each. It runs for
> awhile, and I don't what what record or field produces the problem. How
can
> I get more info?
> Every field in the source table is NVARCHAR.
> The message after 5 minutes or so is:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type nvarchar to numeric
> TIA -
> Mark
> The statement:
> select
> identity(int,1,1) as ID,
> Item1 as StateCode_1,
> Item2 as HighwayDistrict_2,
> Item3 as CountyCode_3,
> Item4 as PlaceCode_4,
> Item5a as InvenRouteRecordType_5a,
> Item5b as InvenRouteSigningPrefix_5b,
> Item5c as InvenRouteLevelService_5c,
> item5d as InvenRouteNumber_5d,
> Item5e as InvenRouteDirSuffix_5e,
> Item6a as FeaturesIntersected_6a,
> Item7 as FacilityCarried_7,
> Item8 as StructureNumber_8,
> Item9 as LocationNarrative_9,
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
> Item12 as IsOnBaseHighwayNetwork_12,
> Item13a as LrsInvenRoute_13a,
> Item13b as LrsSubroute_13b,
> Item16 as Latitude_16,
> Item17 as Longitude_17,
> -- detours over 199 km are coded as 199
> case PATINDEX('%[^0-9]%',Item19)
> when 0 then cast(Item19 as int)
> else cast(0 as int)
> end as BypassDetourKilometers_19,
> Item20 as TollCode_20,
> Item21 as MaintRespCode_21,
> Item22 as MaintRespOwnerCode_22,
> Item26 as InvenRouteFunctionClass_26,
> Item27 as YearBuilt_27,
> case PATINDEX('%[^0-9]%',Item28a)
> when 0 then cast(Item28a as int)
> else cast(0 as int)
> end as LanesOn_28a,
> case PATINDEX('%[^0-9]%',Item28b)
> when 0 then cast(Item28b as int)
> else cast(0 as int)
> end as LanesUnder_28b,
> case PATINDEX('%[^0-9]%',Item29)
> when 0 then cast(Item29 as int)
> else cast(0 as int)
> end as AvgDailyTraffic_29,
> Item30 as AvgDailyTrafficYear_30,
> Item31 as DesignLoadCode_31,
> case PATINDEX('%[^0-9.]%',Item32)
> when 0 then cast(Item32 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as ApproachRoadwayWidth_32,
> Item33 as MedianExistOpenClosed_33,
> case PATINDEX('%[^0-9]%',Item34)
> when 0 then cast(Item34 as int)
> else cast(0 as int)
> end as RoadwayPierSkewDegrees_34,
> Item35 as StructureIsFlared_35,
> Item36a as TsRailings_36a,
> Item36b as TsTransitions_36b,
> Item36c as TsApprGuardrail_36c,
> item36d as TsApprGuardrailEnds_36d,
> Item37 as HistoricSigCode_37,
> Item38 as NavControlCode_38,
> case PATINDEX('%[^0-9.]%',Item39)
> when 0 then cast(Item39 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavVertClearance_39,
> case PATINDEX('%[^0-9.]%',Item40)
> when 0 then cast(Item40 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as NavHorzClearance_40,
> Item41 as OpenPostedClosedCode_41,
> Item42a as ServiceTypeOnCode_42a,
> item42b as ServiceTypeUnderCode_42b,
> Item43a as StructureMaterialTypeCode_43a,
> Item43b as StructureTypeCode_43b,
> Item44a as ApproachMaterialTypeCode_44a,
> Item44b as ApproachStructureTypeCode_44b,
> case PATINDEX('%[^0-9]%',Item45)
> when 0 then cast(Item45 as int)
> else cast(0 as int)
> end as MainSpans_45,
> case PATINDEX('%[^0-9]%',Item46)
> when 0 then cast(Item46 as int)
> else cast(0 as int)
> end as ApproachSpans_46,
> -- 100 meters or greater coded as 999, leave as is
> case PATINDEX('%[^0-9.]%',Item47)
> when 0 then cast(Item47 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRouteHorzClearance_47,
> case PATINDEX('%[^0-9.]%',Item48)
> when 0 then cast(Item48 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as MaxSpanLength_48,
> case PATINDEX('%[^0-9.]%',Item49)
> when 0 then cast(Item49 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as StructureLength_49,
> case PATINDEX('%[^0-9.]%',Item50a)
> when 0 then cast(Item50a as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthLeft_50a,
> case PATINDEX('%[^0-9.]%',Item50b)
> when 0 then cast(Item50b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthRight_50b,
> case PATINDEX('%[^0-9.]%',Item51)
> when 0 then cast(Item51 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbToCurbRoadwayWidth_51,
> case PATINDEX('%[^0-9.]%',Item52)
> when 0 then cast(Item52 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as DeckWidth_52,
> -- Item53: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item53)
> when 0 then cast(Item53 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceOverRoadway_53,
> Item54a as VertClearanceTypeUnderStructure_54a,
> -- Item54b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item54b)
> when 0 then cast(Item54b as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceUnderStructure_54b,
> Item55a as LatClearanceTypeUnderStructure_55a,
> -- Item55b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item55b)
> when 0 then cast(Item55b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderStructure_55b,
> -- Item56: 999 for "open", 998 for greater than 30 meters
> case
> when Item56 = '999' then cast(0 as decimal(7,1))
> when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as
decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderOnLeft_56,
> Item58 as DeckConditionCode_58,
> Item59 as SuperstructureConditionCode_59,
> Item60 as SubstructureConditionCode_60,
> Item61 as ChannelConditionCode_61,
> Item62 as CulvertConditionCode_62,
> Item63 as OperationRatingMethodCode_63,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item64)
> when 0 then cast(Item64 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as OperatingRatingMetricTons_64,
> Item65 as InvenRatingLoadMethodCode_65,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item66)
> when 0 then cast(Item66 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRatingMetricTons_66,
> Item67 as StructureEvalCode_67,
> Item68 as DeckGeoEvalCode_68,
> Item69 as UnderclearanceEvalCode_69,
> Item70 as BridgePostingCode_70,
> Item71 as WaterwayAdequacyCode_71,
> item772 as ApprRoadwayAlignmentCode_72,
> Item75a as WorkTypeCode_75a,
> Item75b as WorkerTypeCode_75b,
> case PATINDEX('%[^0-9.]%',Item76)
> when 0 then cast(Item76 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as ImprovementSpan_76,
> Item90 as LastInspectionMonthYear_90,
> Item91 as InspectionFrequencyMonths_91,
> item92a as CfiFractureCriticalDetailsCode_92a,
> Item92b as CfiUnderwaterInspectionCode_92b,
> Item92c as CfiOtherSpecialInspectionCode_92c,
> Item93a as CfiFractureCriticalDetailsMonthYear_93a,
> Item93b as CfiUnderwaterInspectionMonthYear_93b,
> item93c as CfiOtherSpecialInspectionMonthYear_93c,
> case PATINDEX('%[^0-9]%',Item94)
> when 0 then cast(Item94 as decimal)*1000
> else cast(0 as decimal)
> end as BridgeImprovementCost_94,
> case PATINDEX('%[^0-9]%',Item95)
> when 0 then cast(Item95 as decimal)*1000
> else cast(0 as decimal)
> end as RoadwayImprovementCost_95,
> case PATINDEX('%[^0-9]%',Item96)
> when 0 then cast(Item96 as decimal)*1000
> else cast(0 as decimal)
> end as TotalImprovementCost_96,
> Item97 as ImprovementCostEstYear_97,
> Item98a as BorderBrNeighborStateCode_98a,
> -- 99 means no responsibility for the structure
> case
> when Item98b = '99' then cast(0 as int)
> when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
> else cast(0 as int)
> end as BorderBrPctResponsibility_98b,
> -- This one could be an NBI id, or a state structure number
> Item99 as BorderBrNeighborsStructureId_99,
> Item100 as StrahnetHighwayDesCode_100,
> Item101 as ParallelStructureDesCode_101,
> Item102 as InvenRouteTrafficDirCode_102,
> Item103 as TemporaryStructure_103,
> Item104 as IsOnNhs_104,
> Item105 as FederalLandsHighwayCode_105,
> Item106 as YearReconstructed_106,
> Item107 as DeckTypeCode_107,
> Item108a as DeckProtectSurfaceTypeCode_108a,
> Item108b as DeckProtectMembraneTypeCode_108b,
> Item108c as DeckProtectTypeCode_108c,
> case PATINDEX('%[^0-9]%',Item109)
> when 0 then cast(Item109 as int)
> else cast(0 as int)
> end as AvgDailyTruckTrafficPct_109,
> Item110 as IsOnNatlTruckNetwork_110,
> Item111 as PierOrAbutmentProtectionCode_111,
> Item112 as NbisLengthYesNo_112,
> Item113 as ScourCriticalCode_113,
> case PATINDEX('%[^0-9]%',Item114)
> when 0 then cast(Item114 as int)
> else cast(0 as int)
> end as FutureAvgDailyTraffic_114,
> Item115 as FutureAvgDailyTrafficYear_115,
> case PATINDEX('%[^0-9.]%',Item116)
> when 0 then cast(Item116 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavMinVertClearanceLiftBrClosed_116,
> cast(' ' as varchar(22)) as CountyName,
> cast(' ' as varchar(52)) as PlaceName
> into STEEL
> from NBI
> GO
>
Error 8114 on big query - how to hone it down?
I have a big select into statement I'm running from QA (on the same machine
where the DB is). I've used it in the past, and it worked then. It's with
the NBI (National Bridge Inventory), trying to convert some data types, so
there are over 600,000 records with 120 or so fields each. It runs for
awhile, and I don't what what record or field produces the problem. How can
I get more info?
Every field in the source table is NVARCHAR.
The message after 5 minutes or so is:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric
TIA -
Mark
The statement:
select
identity(int,1,1) as ID,
Item1 as StateCode_1,
Item2 as HighwayDistrict_2,
Item3 as CountyCode_3,
Item4 as PlaceCode_4,
Item5a as InvenRouteRecordType_5a,
Item5b as InvenRouteSigningPrefix_5b,
Item5c as InvenRouteLevelService_5c,
item5d as InvenRouteNumber_5d,
Item5e as InvenRouteDirSuffix_5e,
Item6a as FeaturesIntersected_6a,
Item7 as FacilityCarried_7,
Item8 as StructureNumber_8,
Item9 as LocationNarrative_9,
-- 99.99 is a possible vert clearance (over 30 meters), don't care
case PATINDEX('%[^0-9.]%',Item10)
when 0 then cast(Item10 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as InvenRouteMinVertClearance_10,
case PATINDEX('%[^0-9.]%',Item11)
when 0 then cast(Item11 as decimal(12,3))/1000
else cast(0 as decimal(12,3))
end as LrsKilometerPoint_11,
Item12 as IsOnBaseHighwayNetwork_12,
Item13a as LrsInvenRoute_13a,
Item13b as LrsSubroute_13b,
Item16 as Latitude_16,
Item17 as Longitude_17,
-- detours over 199 km are coded as 199
case PATINDEX('%[^0-9]%',Item19)
when 0 then cast(Item19 as int)
else cast(0 as int)
end as BypassDetourKilometers_19,
Item20 as TollCode_20,
Item21 as MaintRespCode_21,
Item22 as MaintRespOwnerCode_22,
Item26 as InvenRouteFunctionClass_26,
Item27 as YearBuilt_27,
case PATINDEX('%[^0-9]%',Item28a)
when 0 then cast(Item28a as int)
else cast(0 as int)
end as LanesOn_28a,
case PATINDEX('%[^0-9]%',Item28b)
when 0 then cast(Item28b as int)
else cast(0 as int)
end as LanesUnder_28b,
case PATINDEX('%[^0-9]%',Item29)
when 0 then cast(Item29 as int)
else cast(0 as int)
end as AvgDailyTraffic_29,
Item30 as AvgDailyTrafficYear_30,
Item31 as DesignLoadCode_31,
case PATINDEX('%[^0-9.]%',Item32)
when 0 then cast(Item32 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as ApproachRoadwayWidth_32,
Item33 as MedianExistOpenClosed_33,
case PATINDEX('%[^0-9]%',Item34)
when 0 then cast(Item34 as int)
else cast(0 as int)
end as RoadwayPierSkewDegrees_34,
Item35 as StructureIsFlared_35,
Item36a as TsRailings_36a,
Item36b as TsTransitions_36b,
Item36c as TsApprGuardrail_36c,
item36d as TsApprGuardrailEnds_36d,
Item37 as HistoricSigCode_37,
Item38 as NavControlCode_38,
case PATINDEX('%[^0-9.]%',Item39)
when 0 then cast(Item39 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavVertClearance_39,
case PATINDEX('%[^0-9.]%',Item40)
when 0 then cast(Item40 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as NavHorzClearance_40,
Item41 as OpenPostedClosedCode_41,
Item42a as ServiceTypeOnCode_42a,
item42b as ServiceTypeUnderCode_42b,
Item43a as StructureMaterialTypeCode_43a,
Item43b as StructureTypeCode_43b,
Item44a as ApproachMaterialTypeCode_44a,
Item44b as ApproachStructureTypeCode_44b,
case PATINDEX('%[^0-9]%',Item45)
when 0 then cast(Item45 as int)
else cast(0 as int)
end as MainSpans_45,
case PATINDEX('%[^0-9]%',Item46)
when 0 then cast(Item46 as int)
else cast(0 as int)
end as ApproachSpans_46,
-- 100 meters or greater coded as 999, leave as is
case PATINDEX('%[^0-9.]%',Item47)
when 0 then cast(Item47 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRouteHorzClearance_47,
case PATINDEX('%[^0-9.]%',Item48)
when 0 then cast(Item48 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as MaxSpanLength_48,
case PATINDEX('%[^0-9.]%',Item49)
when 0 then cast(Item49 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as StructureLength_49,
case PATINDEX('%[^0-9.]%',Item50a)
when 0 then cast(Item50a as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthLeft_50a,
case PATINDEX('%[^0-9.]%',Item50b)
when 0 then cast(Item50b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthRight_50b,
case PATINDEX('%[^0-9.]%',Item51)
when 0 then cast(Item51 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbToCurbRoadwayWidth_51,
case PATINDEX('%[^0-9.]%',Item52)
when 0 then cast(Item52 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as DeckWidth_52,
-- Item53: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item53)
when 0 then cast(Item53 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceOverRoadway_53,
Item54a as VertClearanceTypeUnderStructure_54a,
-- Item54b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item54b)
when 0 then cast(Item54b as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceUnderStructure_54b,
Item55a as LatClearanceTypeUnderStructure_55a,
-- Item55b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item55b)
when 0 then cast(Item55b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderStructure_55b,
-- Item56: 999 for "open", 998 for greater than 30 meters
case
when Item56 = '999' then cast(0 as decimal(7,1))
when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderOnLeft_56,
Item58 as DeckConditionCode_58,
Item59 as SuperstructureConditionCode_59,
Item60 as SubstructureConditionCode_60,
Item61 as ChannelConditionCode_61,
Item62 as CulvertConditionCode_62,
Item63 as OperationRatingMethodCode_63,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item64)
when 0 then cast(Item64 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as OperatingRatingMetricTons_64,
Item65 as InvenRatingLoadMethodCode_65,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item66)
when 0 then cast(Item66 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRatingMetricTons_66,
Item67 as StructureEvalCode_67,
Item68 as DeckGeoEvalCode_68,
Item69 as UnderclearanceEvalCode_69,
Item70 as BridgePostingCode_70,
Item71 as WaterwayAdequacyCode_71,
item772 as ApprRoadwayAlignmentCode_72,
Item75a as WorkTypeCode_75a,
Item75b as WorkerTypeCode_75b,
case PATINDEX('%[^0-9.]%',Item76)
when 0 then cast(Item76 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as ImprovementSpan_76,
Item90 as LastInspectionMonthYear_90,
Item91 as InspectionFrequencyMonths_91,
item92a as CfiFractureCriticalDetailsCode_92a,
Item92b as CfiUnderwaterInspectionCode_92b,
Item92c as CfiOtherSpecialInspectionCode_92c,
Item93a as CfiFractureCriticalDetailsMonthYear_93a,
Item93b as CfiUnderwaterInspectionMonthYear_93b,
item93c as CfiOtherSpecialInspectionMonthYear_93c,
case PATINDEX('%[^0-9]%',Item94)
when 0 then cast(Item94 as decimal)*1000
else cast(0 as decimal)
end as BridgeImprovementCost_94,
case PATINDEX('%[^0-9]%',Item95)
when 0 then cast(Item95 as decimal)*1000
else cast(0 as decimal)
end as RoadwayImprovementCost_95,
case PATINDEX('%[^0-9]%',Item96)
when 0 then cast(Item96 as decimal)*1000
else cast(0 as decimal)
end as TotalImprovementCost_96,
Item97 as ImprovementCostEstYear_97,
Item98a as BorderBrNeighborStateCode_98a,
-- 99 means no responsibility for the structure
case
when Item98b = '99' then cast(0 as int)
when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
else cast(0 as int)
end as BorderBrPctResponsibility_98b,
-- This one could be an NBI id, or a state structure number
Item99 as BorderBrNeighborsStructureId_99,
Item100 as StrahnetHighwayDesCode_100,
Item101 as ParallelStructureDesCode_101,
Item102 as InvenRouteTrafficDirCode_102,
Item103 as TemporaryStructure_103,
Item104 as IsOnNhs_104,
Item105 as FederalLandsHighwayCode_105,
Item106 as YearReconstructed_106,
Item107 as DeckTypeCode_107,
Item108a as DeckProtectSurfaceTypeCode_108a,
Item108b as DeckProtectMembraneTypeCode_108b,
Item108c as DeckProtectTypeCode_108c,
case PATINDEX('%[^0-9]%',Item109)
when 0 then cast(Item109 as int)
else cast(0 as int)
end as AvgDailyTruckTrafficPct_109,
Item110 as IsOnNatlTruckNetwork_110,
Item111 as PierOrAbutmentProtectionCode_111,
Item112 as NbisLengthYesNo_112,
Item113 as ScourCriticalCode_113,
case PATINDEX('%[^0-9]%',Item114)
when 0 then cast(Item114 as int)
else cast(0 as int)
end as FutureAvgDailyTraffic_114,
Item115 as FutureAvgDailyTrafficYear_115,
case PATINDEX('%[^0-9.]%',Item116)
when 0 then cast(Item116 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavMinVertClearanceLiftBrClosed_116,
cast(' ' as varchar(22)) as CountyName,
cast(' ' as varchar(52)) as PlaceName
into STEEL
from NBI
GOSometimes something about posting to usenet makes it work...
There are decimal points in the PATINDEX functions. I suppose if there is
one (or two) in a source field, then it will try to convert. Got rid of
those and it works.
Mark
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
etc...|||You can add ISNUMERIC to your CASE in order to identify other invalid data,
such as multiple decimal points and empty strings:
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN CAST(Item10 AS decimal(7,2))/100
ELSE CAST(0 AS decimal(7,2)) END
If you need to identify rows with invalid data, specify the CASE statements
in a WHERE clause.
WHERE 'Invalid' = CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN 'Valid'
ELSE 'invalid' END
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark G. Meyers" <mmeyers[at]hydromilling.com> wrote in message
news:ep5pRIqYEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I have a big select into statement I'm running from QA (on the same
machine
> where the DB is). I've used it in the past, and it worked then. It's
with
> the NBI (National Bridge Inventory), trying to convert some data types, so
> there are over 600,000 records with 120 or so fields each. It runs for
> awhile, and I don't what what record or field produces the problem. How
can
> I get more info?
> Every field in the source table is NVARCHAR.
> The message after 5 minutes or so is:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type nvarchar to numeric
> TIA -
> Mark
> The statement:
> select
> identity(int,1,1) as ID,
> Item1 as StateCode_1,
> Item2 as HighwayDistrict_2,
> Item3 as CountyCode_3,
> Item4 as PlaceCode_4,
> Item5a as InvenRouteRecordType_5a,
> Item5b as InvenRouteSigningPrefix_5b,
> Item5c as InvenRouteLevelService_5c,
> item5d as InvenRouteNumber_5d,
> Item5e as InvenRouteDirSuffix_5e,
> Item6a as FeaturesIntersected_6a,
> Item7 as FacilityCarried_7,
> Item8 as StructureNumber_8,
> Item9 as LocationNarrative_9,
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
> Item12 as IsOnBaseHighwayNetwork_12,
> Item13a as LrsInvenRoute_13a,
> Item13b as LrsSubroute_13b,
> Item16 as Latitude_16,
> Item17 as Longitude_17,
> -- detours over 199 km are coded as 199
> case PATINDEX('%[^0-9]%',Item19)
> when 0 then cast(Item19 as int)
> else cast(0 as int)
> end as BypassDetourKilometers_19,
> Item20 as TollCode_20,
> Item21 as MaintRespCode_21,
> Item22 as MaintRespOwnerCode_22,
> Item26 as InvenRouteFunctionClass_26,
> Item27 as YearBuilt_27,
> case PATINDEX('%[^0-9]%',Item28a)
> when 0 then cast(Item28a as int)
> else cast(0 as int)
> end as LanesOn_28a,
> case PATINDEX('%[^0-9]%',Item28b)
> when 0 then cast(Item28b as int)
> else cast(0 as int)
> end as LanesUnder_28b,
> case PATINDEX('%[^0-9]%',Item29)
> when 0 then cast(Item29 as int)
> else cast(0 as int)
> end as AvgDailyTraffic_29,
> Item30 as AvgDailyTrafficYear_30,
> Item31 as DesignLoadCode_31,
> case PATINDEX('%[^0-9.]%',Item32)
> when 0 then cast(Item32 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as ApproachRoadwayWidth_32,
> Item33 as MedianExistOpenClosed_33,
> case PATINDEX('%[^0-9]%',Item34)
> when 0 then cast(Item34 as int)
> else cast(0 as int)
> end as RoadwayPierSkewDegrees_34,
> Item35 as StructureIsFlared_35,
> Item36a as TsRailings_36a,
> Item36b as TsTransitions_36b,
> Item36c as TsApprGuardrail_36c,
> item36d as TsApprGuardrailEnds_36d,
> Item37 as HistoricSigCode_37,
> Item38 as NavControlCode_38,
> case PATINDEX('%[^0-9.]%',Item39)
> when 0 then cast(Item39 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavVertClearance_39,
> case PATINDEX('%[^0-9.]%',Item40)
> when 0 then cast(Item40 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as NavHorzClearance_40,
> Item41 as OpenPostedClosedCode_41,
> Item42a as ServiceTypeOnCode_42a,
> item42b as ServiceTypeUnderCode_42b,
> Item43a as StructureMaterialTypeCode_43a,
> Item43b as StructureTypeCode_43b,
> Item44a as ApproachMaterialTypeCode_44a,
> Item44b as ApproachStructureTypeCode_44b,
> case PATINDEX('%[^0-9]%',Item45)
> when 0 then cast(Item45 as int)
> else cast(0 as int)
> end as MainSpans_45,
> case PATINDEX('%[^0-9]%',Item46)
> when 0 then cast(Item46 as int)
> else cast(0 as int)
> end as ApproachSpans_46,
> -- 100 meters or greater coded as 999, leave as is
> case PATINDEX('%[^0-9.]%',Item47)
> when 0 then cast(Item47 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRouteHorzClearance_47,
> case PATINDEX('%[^0-9.]%',Item48)
> when 0 then cast(Item48 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as MaxSpanLength_48,
> case PATINDEX('%[^0-9.]%',Item49)
> when 0 then cast(Item49 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as StructureLength_49,
> case PATINDEX('%[^0-9.]%',Item50a)
> when 0 then cast(Item50a as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthLeft_50a,
> case PATINDEX('%[^0-9.]%',Item50b)
> when 0 then cast(Item50b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthRight_50b,
> case PATINDEX('%[^0-9.]%',Item51)
> when 0 then cast(Item51 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbToCurbRoadwayWidth_51,
> case PATINDEX('%[^0-9.]%',Item52)
> when 0 then cast(Item52 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as DeckWidth_52,
> -- Item53: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item53)
> when 0 then cast(Item53 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceOverRoadway_53,
> Item54a as VertClearanceTypeUnderStructure_54a,
> -- Item54b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item54b)
> when 0 then cast(Item54b as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceUnderStructure_54b,
> Item55a as LatClearanceTypeUnderStructure_55a,
> -- Item55b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item55b)
> when 0 then cast(Item55b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderStructure_55b,
> -- Item56: 999 for "open", 998 for greater than 30 meters
> case
> when Item56 = '999' then cast(0 as decimal(7,1))
> when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as
decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderOnLeft_56,
> Item58 as DeckConditionCode_58,
> Item59 as SuperstructureConditionCode_59,
> Item60 as SubstructureConditionCode_60,
> Item61 as ChannelConditionCode_61,
> Item62 as CulvertConditionCode_62,
> Item63 as OperationRatingMethodCode_63,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item64)
> when 0 then cast(Item64 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as OperatingRatingMetricTons_64,
> Item65 as InvenRatingLoadMethodCode_65,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item66)
> when 0 then cast(Item66 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRatingMetricTons_66,
> Item67 as StructureEvalCode_67,
> Item68 as DeckGeoEvalCode_68,
> Item69 as UnderclearanceEvalCode_69,
> Item70 as BridgePostingCode_70,
> Item71 as WaterwayAdequacyCode_71,
> item772 as ApprRoadwayAlignmentCode_72,
> Item75a as WorkTypeCode_75a,
> Item75b as WorkerTypeCode_75b,
> case PATINDEX('%[^0-9.]%',Item76)
> when 0 then cast(Item76 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as ImprovementSpan_76,
> Item90 as LastInspectionMonthYear_90,
> Item91 as InspectionFrequencyMonths_91,
> item92a as CfiFractureCriticalDetailsCode_92a,
> Item92b as CfiUnderwaterInspectionCode_92b,
> Item92c as CfiOtherSpecialInspectionCode_92c,
> Item93a as CfiFractureCriticalDetailsMonthYear_93a,
> Item93b as CfiUnderwaterInspectionMonthYear_93b,
> item93c as CfiOtherSpecialInspectionMonthYear_93c,
> case PATINDEX('%[^0-9]%',Item94)
> when 0 then cast(Item94 as decimal)*1000
> else cast(0 as decimal)
> end as BridgeImprovementCost_94,
> case PATINDEX('%[^0-9]%',Item95)
> when 0 then cast(Item95 as decimal)*1000
> else cast(0 as decimal)
> end as RoadwayImprovementCost_95,
> case PATINDEX('%[^0-9]%',Item96)
> when 0 then cast(Item96 as decimal)*1000
> else cast(0 as decimal)
> end as TotalImprovementCost_96,
> Item97 as ImprovementCostEstYear_97,
> Item98a as BorderBrNeighborStateCode_98a,
> -- 99 means no responsibility for the structure
> case
> when Item98b = '99' then cast(0 as int)
> when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
> else cast(0 as int)
> end as BorderBrPctResponsibility_98b,
> -- This one could be an NBI id, or a state structure number
> Item99 as BorderBrNeighborsStructureId_99,
> Item100 as StrahnetHighwayDesCode_100,
> Item101 as ParallelStructureDesCode_101,
> Item102 as InvenRouteTrafficDirCode_102,
> Item103 as TemporaryStructure_103,
> Item104 as IsOnNhs_104,
> Item105 as FederalLandsHighwayCode_105,
> Item106 as YearReconstructed_106,
> Item107 as DeckTypeCode_107,
> Item108a as DeckProtectSurfaceTypeCode_108a,
> Item108b as DeckProtectMembraneTypeCode_108b,
> Item108c as DeckProtectTypeCode_108c,
> case PATINDEX('%[^0-9]%',Item109)
> when 0 then cast(Item109 as int)
> else cast(0 as int)
> end as AvgDailyTruckTrafficPct_109,
> Item110 as IsOnNatlTruckNetwork_110,
> Item111 as PierOrAbutmentProtectionCode_111,
> Item112 as NbisLengthYesNo_112,
> Item113 as ScourCriticalCode_113,
> case PATINDEX('%[^0-9]%',Item114)
> when 0 then cast(Item114 as int)
> else cast(0 as int)
> end as FutureAvgDailyTraffic_114,
> Item115 as FutureAvgDailyTrafficYear_115,
> case PATINDEX('%[^0-9.]%',Item116)
> when 0 then cast(Item116 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavMinVertClearanceLiftBrClosed_116,
> cast(' ' as varchar(22)) as CountyName,
> cast(' ' as varchar(52)) as PlaceName
> into STEEL
> from NBI
> GO
>
where the DB is). I've used it in the past, and it worked then. It's with
the NBI (National Bridge Inventory), trying to convert some data types, so
there are over 600,000 records with 120 or so fields each. It runs for
awhile, and I don't what what record or field produces the problem. How can
I get more info?
Every field in the source table is NVARCHAR.
The message after 5 minutes or so is:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric
TIA -
Mark
The statement:
select
identity(int,1,1) as ID,
Item1 as StateCode_1,
Item2 as HighwayDistrict_2,
Item3 as CountyCode_3,
Item4 as PlaceCode_4,
Item5a as InvenRouteRecordType_5a,
Item5b as InvenRouteSigningPrefix_5b,
Item5c as InvenRouteLevelService_5c,
item5d as InvenRouteNumber_5d,
Item5e as InvenRouteDirSuffix_5e,
Item6a as FeaturesIntersected_6a,
Item7 as FacilityCarried_7,
Item8 as StructureNumber_8,
Item9 as LocationNarrative_9,
-- 99.99 is a possible vert clearance (over 30 meters), don't care
case PATINDEX('%[^0-9.]%',Item10)
when 0 then cast(Item10 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as InvenRouteMinVertClearance_10,
case PATINDEX('%[^0-9.]%',Item11)
when 0 then cast(Item11 as decimal(12,3))/1000
else cast(0 as decimal(12,3))
end as LrsKilometerPoint_11,
Item12 as IsOnBaseHighwayNetwork_12,
Item13a as LrsInvenRoute_13a,
Item13b as LrsSubroute_13b,
Item16 as Latitude_16,
Item17 as Longitude_17,
-- detours over 199 km are coded as 199
case PATINDEX('%[^0-9]%',Item19)
when 0 then cast(Item19 as int)
else cast(0 as int)
end as BypassDetourKilometers_19,
Item20 as TollCode_20,
Item21 as MaintRespCode_21,
Item22 as MaintRespOwnerCode_22,
Item26 as InvenRouteFunctionClass_26,
Item27 as YearBuilt_27,
case PATINDEX('%[^0-9]%',Item28a)
when 0 then cast(Item28a as int)
else cast(0 as int)
end as LanesOn_28a,
case PATINDEX('%[^0-9]%',Item28b)
when 0 then cast(Item28b as int)
else cast(0 as int)
end as LanesUnder_28b,
case PATINDEX('%[^0-9]%',Item29)
when 0 then cast(Item29 as int)
else cast(0 as int)
end as AvgDailyTraffic_29,
Item30 as AvgDailyTrafficYear_30,
Item31 as DesignLoadCode_31,
case PATINDEX('%[^0-9.]%',Item32)
when 0 then cast(Item32 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as ApproachRoadwayWidth_32,
Item33 as MedianExistOpenClosed_33,
case PATINDEX('%[^0-9]%',Item34)
when 0 then cast(Item34 as int)
else cast(0 as int)
end as RoadwayPierSkewDegrees_34,
Item35 as StructureIsFlared_35,
Item36a as TsRailings_36a,
Item36b as TsTransitions_36b,
Item36c as TsApprGuardrail_36c,
item36d as TsApprGuardrailEnds_36d,
Item37 as HistoricSigCode_37,
Item38 as NavControlCode_38,
case PATINDEX('%[^0-9.]%',Item39)
when 0 then cast(Item39 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavVertClearance_39,
case PATINDEX('%[^0-9.]%',Item40)
when 0 then cast(Item40 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as NavHorzClearance_40,
Item41 as OpenPostedClosedCode_41,
Item42a as ServiceTypeOnCode_42a,
item42b as ServiceTypeUnderCode_42b,
Item43a as StructureMaterialTypeCode_43a,
Item43b as StructureTypeCode_43b,
Item44a as ApproachMaterialTypeCode_44a,
Item44b as ApproachStructureTypeCode_44b,
case PATINDEX('%[^0-9]%',Item45)
when 0 then cast(Item45 as int)
else cast(0 as int)
end as MainSpans_45,
case PATINDEX('%[^0-9]%',Item46)
when 0 then cast(Item46 as int)
else cast(0 as int)
end as ApproachSpans_46,
-- 100 meters or greater coded as 999, leave as is
case PATINDEX('%[^0-9.]%',Item47)
when 0 then cast(Item47 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRouteHorzClearance_47,
case PATINDEX('%[^0-9.]%',Item48)
when 0 then cast(Item48 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as MaxSpanLength_48,
case PATINDEX('%[^0-9.]%',Item49)
when 0 then cast(Item49 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as StructureLength_49,
case PATINDEX('%[^0-9.]%',Item50a)
when 0 then cast(Item50a as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthLeft_50a,
case PATINDEX('%[^0-9.]%',Item50b)
when 0 then cast(Item50b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthRight_50b,
case PATINDEX('%[^0-9.]%',Item51)
when 0 then cast(Item51 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbToCurbRoadwayWidth_51,
case PATINDEX('%[^0-9.]%',Item52)
when 0 then cast(Item52 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as DeckWidth_52,
-- Item53: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item53)
when 0 then cast(Item53 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceOverRoadway_53,
Item54a as VertClearanceTypeUnderStructure_54a,
-- Item54b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item54b)
when 0 then cast(Item54b as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceUnderStructure_54b,
Item55a as LatClearanceTypeUnderStructure_55a,
-- Item55b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item55b)
when 0 then cast(Item55b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderStructure_55b,
-- Item56: 999 for "open", 998 for greater than 30 meters
case
when Item56 = '999' then cast(0 as decimal(7,1))
when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderOnLeft_56,
Item58 as DeckConditionCode_58,
Item59 as SuperstructureConditionCode_59,
Item60 as SubstructureConditionCode_60,
Item61 as ChannelConditionCode_61,
Item62 as CulvertConditionCode_62,
Item63 as OperationRatingMethodCode_63,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item64)
when 0 then cast(Item64 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as OperatingRatingMetricTons_64,
Item65 as InvenRatingLoadMethodCode_65,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item66)
when 0 then cast(Item66 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRatingMetricTons_66,
Item67 as StructureEvalCode_67,
Item68 as DeckGeoEvalCode_68,
Item69 as UnderclearanceEvalCode_69,
Item70 as BridgePostingCode_70,
Item71 as WaterwayAdequacyCode_71,
item772 as ApprRoadwayAlignmentCode_72,
Item75a as WorkTypeCode_75a,
Item75b as WorkerTypeCode_75b,
case PATINDEX('%[^0-9.]%',Item76)
when 0 then cast(Item76 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as ImprovementSpan_76,
Item90 as LastInspectionMonthYear_90,
Item91 as InspectionFrequencyMonths_91,
item92a as CfiFractureCriticalDetailsCode_92a,
Item92b as CfiUnderwaterInspectionCode_92b,
Item92c as CfiOtherSpecialInspectionCode_92c,
Item93a as CfiFractureCriticalDetailsMonthYear_93a,
Item93b as CfiUnderwaterInspectionMonthYear_93b,
item93c as CfiOtherSpecialInspectionMonthYear_93c,
case PATINDEX('%[^0-9]%',Item94)
when 0 then cast(Item94 as decimal)*1000
else cast(0 as decimal)
end as BridgeImprovementCost_94,
case PATINDEX('%[^0-9]%',Item95)
when 0 then cast(Item95 as decimal)*1000
else cast(0 as decimal)
end as RoadwayImprovementCost_95,
case PATINDEX('%[^0-9]%',Item96)
when 0 then cast(Item96 as decimal)*1000
else cast(0 as decimal)
end as TotalImprovementCost_96,
Item97 as ImprovementCostEstYear_97,
Item98a as BorderBrNeighborStateCode_98a,
-- 99 means no responsibility for the structure
case
when Item98b = '99' then cast(0 as int)
when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
else cast(0 as int)
end as BorderBrPctResponsibility_98b,
-- This one could be an NBI id, or a state structure number
Item99 as BorderBrNeighborsStructureId_99,
Item100 as StrahnetHighwayDesCode_100,
Item101 as ParallelStructureDesCode_101,
Item102 as InvenRouteTrafficDirCode_102,
Item103 as TemporaryStructure_103,
Item104 as IsOnNhs_104,
Item105 as FederalLandsHighwayCode_105,
Item106 as YearReconstructed_106,
Item107 as DeckTypeCode_107,
Item108a as DeckProtectSurfaceTypeCode_108a,
Item108b as DeckProtectMembraneTypeCode_108b,
Item108c as DeckProtectTypeCode_108c,
case PATINDEX('%[^0-9]%',Item109)
when 0 then cast(Item109 as int)
else cast(0 as int)
end as AvgDailyTruckTrafficPct_109,
Item110 as IsOnNatlTruckNetwork_110,
Item111 as PierOrAbutmentProtectionCode_111,
Item112 as NbisLengthYesNo_112,
Item113 as ScourCriticalCode_113,
case PATINDEX('%[^0-9]%',Item114)
when 0 then cast(Item114 as int)
else cast(0 as int)
end as FutureAvgDailyTraffic_114,
Item115 as FutureAvgDailyTrafficYear_115,
case PATINDEX('%[^0-9.]%',Item116)
when 0 then cast(Item116 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavMinVertClearanceLiftBrClosed_116,
cast(' ' as varchar(22)) as CountyName,
cast(' ' as varchar(52)) as PlaceName
into STEEL
from NBI
GOSometimes something about posting to usenet makes it work...
There are decimal points in the PATINDEX functions. I suppose if there is
one (or two) in a source field, then it will try to convert. Got rid of
those and it works.
Mark
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
etc...|||You can add ISNUMERIC to your CASE in order to identify other invalid data,
such as multiple decimal points and empty strings:
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN CAST(Item10 AS decimal(7,2))/100
ELSE CAST(0 AS decimal(7,2)) END
If you need to identify rows with invalid data, specify the CASE statements
in a WHERE clause.
WHERE 'Invalid' = CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN 'Valid'
ELSE 'invalid' END
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark G. Meyers" <mmeyers[at]hydromilling.com> wrote in message
news:ep5pRIqYEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I have a big select into statement I'm running from QA (on the same
machine
> where the DB is). I've used it in the past, and it worked then. It's
with
> the NBI (National Bridge Inventory), trying to convert some data types, so
> there are over 600,000 records with 120 or so fields each. It runs for
> awhile, and I don't what what record or field produces the problem. How
can
> I get more info?
> Every field in the source table is NVARCHAR.
> The message after 5 minutes or so is:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type nvarchar to numeric
> TIA -
> Mark
> The statement:
> select
> identity(int,1,1) as ID,
> Item1 as StateCode_1,
> Item2 as HighwayDistrict_2,
> Item3 as CountyCode_3,
> Item4 as PlaceCode_4,
> Item5a as InvenRouteRecordType_5a,
> Item5b as InvenRouteSigningPrefix_5b,
> Item5c as InvenRouteLevelService_5c,
> item5d as InvenRouteNumber_5d,
> Item5e as InvenRouteDirSuffix_5e,
> Item6a as FeaturesIntersected_6a,
> Item7 as FacilityCarried_7,
> Item8 as StructureNumber_8,
> Item9 as LocationNarrative_9,
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
> Item12 as IsOnBaseHighwayNetwork_12,
> Item13a as LrsInvenRoute_13a,
> Item13b as LrsSubroute_13b,
> Item16 as Latitude_16,
> Item17 as Longitude_17,
> -- detours over 199 km are coded as 199
> case PATINDEX('%[^0-9]%',Item19)
> when 0 then cast(Item19 as int)
> else cast(0 as int)
> end as BypassDetourKilometers_19,
> Item20 as TollCode_20,
> Item21 as MaintRespCode_21,
> Item22 as MaintRespOwnerCode_22,
> Item26 as InvenRouteFunctionClass_26,
> Item27 as YearBuilt_27,
> case PATINDEX('%[^0-9]%',Item28a)
> when 0 then cast(Item28a as int)
> else cast(0 as int)
> end as LanesOn_28a,
> case PATINDEX('%[^0-9]%',Item28b)
> when 0 then cast(Item28b as int)
> else cast(0 as int)
> end as LanesUnder_28b,
> case PATINDEX('%[^0-9]%',Item29)
> when 0 then cast(Item29 as int)
> else cast(0 as int)
> end as AvgDailyTraffic_29,
> Item30 as AvgDailyTrafficYear_30,
> Item31 as DesignLoadCode_31,
> case PATINDEX('%[^0-9.]%',Item32)
> when 0 then cast(Item32 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as ApproachRoadwayWidth_32,
> Item33 as MedianExistOpenClosed_33,
> case PATINDEX('%[^0-9]%',Item34)
> when 0 then cast(Item34 as int)
> else cast(0 as int)
> end as RoadwayPierSkewDegrees_34,
> Item35 as StructureIsFlared_35,
> Item36a as TsRailings_36a,
> Item36b as TsTransitions_36b,
> Item36c as TsApprGuardrail_36c,
> item36d as TsApprGuardrailEnds_36d,
> Item37 as HistoricSigCode_37,
> Item38 as NavControlCode_38,
> case PATINDEX('%[^0-9.]%',Item39)
> when 0 then cast(Item39 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavVertClearance_39,
> case PATINDEX('%[^0-9.]%',Item40)
> when 0 then cast(Item40 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as NavHorzClearance_40,
> Item41 as OpenPostedClosedCode_41,
> Item42a as ServiceTypeOnCode_42a,
> item42b as ServiceTypeUnderCode_42b,
> Item43a as StructureMaterialTypeCode_43a,
> Item43b as StructureTypeCode_43b,
> Item44a as ApproachMaterialTypeCode_44a,
> Item44b as ApproachStructureTypeCode_44b,
> case PATINDEX('%[^0-9]%',Item45)
> when 0 then cast(Item45 as int)
> else cast(0 as int)
> end as MainSpans_45,
> case PATINDEX('%[^0-9]%',Item46)
> when 0 then cast(Item46 as int)
> else cast(0 as int)
> end as ApproachSpans_46,
> -- 100 meters or greater coded as 999, leave as is
> case PATINDEX('%[^0-9.]%',Item47)
> when 0 then cast(Item47 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRouteHorzClearance_47,
> case PATINDEX('%[^0-9.]%',Item48)
> when 0 then cast(Item48 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as MaxSpanLength_48,
> case PATINDEX('%[^0-9.]%',Item49)
> when 0 then cast(Item49 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as StructureLength_49,
> case PATINDEX('%[^0-9.]%',Item50a)
> when 0 then cast(Item50a as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthLeft_50a,
> case PATINDEX('%[^0-9.]%',Item50b)
> when 0 then cast(Item50b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthRight_50b,
> case PATINDEX('%[^0-9.]%',Item51)
> when 0 then cast(Item51 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbToCurbRoadwayWidth_51,
> case PATINDEX('%[^0-9.]%',Item52)
> when 0 then cast(Item52 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as DeckWidth_52,
> -- Item53: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item53)
> when 0 then cast(Item53 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceOverRoadway_53,
> Item54a as VertClearanceTypeUnderStructure_54a,
> -- Item54b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item54b)
> when 0 then cast(Item54b as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceUnderStructure_54b,
> Item55a as LatClearanceTypeUnderStructure_55a,
> -- Item55b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item55b)
> when 0 then cast(Item55b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderStructure_55b,
> -- Item56: 999 for "open", 998 for greater than 30 meters
> case
> when Item56 = '999' then cast(0 as decimal(7,1))
> when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as
decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderOnLeft_56,
> Item58 as DeckConditionCode_58,
> Item59 as SuperstructureConditionCode_59,
> Item60 as SubstructureConditionCode_60,
> Item61 as ChannelConditionCode_61,
> Item62 as CulvertConditionCode_62,
> Item63 as OperationRatingMethodCode_63,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item64)
> when 0 then cast(Item64 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as OperatingRatingMetricTons_64,
> Item65 as InvenRatingLoadMethodCode_65,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item66)
> when 0 then cast(Item66 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRatingMetricTons_66,
> Item67 as StructureEvalCode_67,
> Item68 as DeckGeoEvalCode_68,
> Item69 as UnderclearanceEvalCode_69,
> Item70 as BridgePostingCode_70,
> Item71 as WaterwayAdequacyCode_71,
> item772 as ApprRoadwayAlignmentCode_72,
> Item75a as WorkTypeCode_75a,
> Item75b as WorkerTypeCode_75b,
> case PATINDEX('%[^0-9.]%',Item76)
> when 0 then cast(Item76 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as ImprovementSpan_76,
> Item90 as LastInspectionMonthYear_90,
> Item91 as InspectionFrequencyMonths_91,
> item92a as CfiFractureCriticalDetailsCode_92a,
> Item92b as CfiUnderwaterInspectionCode_92b,
> Item92c as CfiOtherSpecialInspectionCode_92c,
> Item93a as CfiFractureCriticalDetailsMonthYear_93a,
> Item93b as CfiUnderwaterInspectionMonthYear_93b,
> item93c as CfiOtherSpecialInspectionMonthYear_93c,
> case PATINDEX('%[^0-9]%',Item94)
> when 0 then cast(Item94 as decimal)*1000
> else cast(0 as decimal)
> end as BridgeImprovementCost_94,
> case PATINDEX('%[^0-9]%',Item95)
> when 0 then cast(Item95 as decimal)*1000
> else cast(0 as decimal)
> end as RoadwayImprovementCost_95,
> case PATINDEX('%[^0-9]%',Item96)
> when 0 then cast(Item96 as decimal)*1000
> else cast(0 as decimal)
> end as TotalImprovementCost_96,
> Item97 as ImprovementCostEstYear_97,
> Item98a as BorderBrNeighborStateCode_98a,
> -- 99 means no responsibility for the structure
> case
> when Item98b = '99' then cast(0 as int)
> when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
> else cast(0 as int)
> end as BorderBrPctResponsibility_98b,
> -- This one could be an NBI id, or a state structure number
> Item99 as BorderBrNeighborsStructureId_99,
> Item100 as StrahnetHighwayDesCode_100,
> Item101 as ParallelStructureDesCode_101,
> Item102 as InvenRouteTrafficDirCode_102,
> Item103 as TemporaryStructure_103,
> Item104 as IsOnNhs_104,
> Item105 as FederalLandsHighwayCode_105,
> Item106 as YearReconstructed_106,
> Item107 as DeckTypeCode_107,
> Item108a as DeckProtectSurfaceTypeCode_108a,
> Item108b as DeckProtectMembraneTypeCode_108b,
> Item108c as DeckProtectTypeCode_108c,
> case PATINDEX('%[^0-9]%',Item109)
> when 0 then cast(Item109 as int)
> else cast(0 as int)
> end as AvgDailyTruckTrafficPct_109,
> Item110 as IsOnNatlTruckNetwork_110,
> Item111 as PierOrAbutmentProtectionCode_111,
> Item112 as NbisLengthYesNo_112,
> Item113 as ScourCriticalCode_113,
> case PATINDEX('%[^0-9]%',Item114)
> when 0 then cast(Item114 as int)
> else cast(0 as int)
> end as FutureAvgDailyTraffic_114,
> Item115 as FutureAvgDailyTrafficYear_115,
> case PATINDEX('%[^0-9.]%',Item116)
> when 0 then cast(Item116 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavMinVertClearanceLiftBrClosed_116,
> cast(' ' as varchar(22)) as CountyName,
> cast(' ' as varchar(52)) as PlaceName
> into STEEL
> from NBI
> GO
>
Error 8114 on big query - how to hone it down?
I have a big select into statement I'm running from QA (on the same machine
where the DB is). I've used it in the past, and it worked then. It's with
the NBI (National Bridge Inventory), trying to convert some data types, so
there are over 600,000 records with 120 or so fields each. It runs for
awhile, and I don't what what record or field produces the problem. How can
I get more info?
Every field in the source table is NVARCHAR.
The message after 5 minutes or so is:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric
TIA -
Mark
The statement:
select
identity(int,1,1) as ID,
Item1 as StateCode_1,
Item2 as HighwayDistrict_2,
Item3 as CountyCode_3,
Item4 as PlaceCode_4,
Item5a as InvenRouteRecordType_5a,
Item5b as InvenRouteSigningPrefix_5b,
Item5c as InvenRouteLevelService_5c,
item5d as InvenRouteNumber_5d,
Item5e as InvenRouteDirSuffix_5e,
Item6a as FeaturesIntersected_6a,
Item7 as FacilityCarried_7,
Item8 as StructureNumber_8,
Item9 as LocationNarrative_9,
-- 99.99 is a possible vert clearance (over 30 meters), don't care
case PATINDEX('%[^0-9.]%',Item10)
when 0 then cast(Item10 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as InvenRouteMinVertClearance_10,
case PATINDEX('%[^0-9.]%',Item11)
when 0 then cast(Item11 as decimal(12,3))/1000
else cast(0 as decimal(12,3))
end as LrsKilometerPoint_11,
Item12 as IsOnBaseHighwayNetwork_12,
Item13a as LrsInvenRoute_13a,
Item13b as LrsSubroute_13b,
Item16 as Latitude_16,
Item17 as Longitude_17,
-- detours over 199 km are coded as 199
case PATINDEX('%[^0-9]%',Item19)
when 0 then cast(Item19 as int)
else cast(0 as int)
end as BypassDetourKilometers_19,
Item20 as TollCode_20,
Item21 as MaintRespCode_21,
Item22 as MaintRespOwnerCode_22,
Item26 as InvenRouteFunctionClass_26,
Item27 as YearBuilt_27,
case PATINDEX('%[^0-9]%',Item28a)
when 0 then cast(Item28a as int)
else cast(0 as int)
end as LanesOn_28a,
case PATINDEX('%[^0-9]%',Item28b)
when 0 then cast(Item28b as int)
else cast(0 as int)
end as LanesUnder_28b,
case PATINDEX('%[^0-9]%',Item29)
when 0 then cast(Item29 as int)
else cast(0 as int)
end as AvgDailyTraffic_29,
Item30 as AvgDailyTrafficYear_30,
Item31 as DesignLoadCode_31,
case PATINDEX('%[^0-9.]%',Item32)
when 0 then cast(Item32 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as ApproachRoadwayWidth_32,
Item33 as MedianExistOpenClosed_33,
case PATINDEX('%[^0-9]%',Item34)
when 0 then cast(Item34 as int)
else cast(0 as int)
end as RoadwayPierSkewDegrees_34,
Item35 as StructureIsFlared_35,
Item36a as TsRailings_36a,
Item36b as TsTransitions_36b,
Item36c as TsApprGuardrail_36c,
item36d as TsApprGuardrailEnds_36d,
Item37 as HistoricSigCode_37,
Item38 as NavControlCode_38,
case PATINDEX('%[^0-9.]%',Item39)
when 0 then cast(Item39 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavVertClearance_39,
case PATINDEX('%[^0-9.]%',Item40)
when 0 then cast(Item40 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as NavHorzClearance_40,
Item41 as OpenPostedClosedCode_41,
Item42a as ServiceTypeOnCode_42a,
item42b as ServiceTypeUnderCode_42b,
Item43a as StructureMaterialTypeCode_43a,
Item43b as StructureTypeCode_43b,
Item44a as ApproachMaterialTypeCode_44a,
Item44b as ApproachStructureTypeCode_44b,
case PATINDEX('%[^0-9]%',Item45)
when 0 then cast(Item45 as int)
else cast(0 as int)
end as MainSpans_45,
case PATINDEX('%[^0-9]%',Item46)
when 0 then cast(Item46 as int)
else cast(0 as int)
end as ApproachSpans_46,
-- 100 meters or greater coded as 999, leave as is
case PATINDEX('%[^0-9.]%',Item47)
when 0 then cast(Item47 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRouteHorzClearance_47,
case PATINDEX('%[^0-9.]%',Item48)
when 0 then cast(Item48 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as MaxSpanLength_48,
case PATINDEX('%[^0-9.]%',Item49)
when 0 then cast(Item49 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as StructureLength_49,
case PATINDEX('%[^0-9.]%',Item50a)
when 0 then cast(Item50a as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthLeft_50a,
case PATINDEX('%[^0-9.]%',Item50b)
when 0 then cast(Item50b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthRight_50b,
case PATINDEX('%[^0-9.]%',Item51)
when 0 then cast(Item51 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbToCurbRoadwayWidth_51,
case PATINDEX('%[^0-9.]%',Item52)
when 0 then cast(Item52 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as DeckWidth_52,
-- Item53: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item53)
when 0 then cast(Item53 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceOverRoadway_53,
Item54a as VertClearanceTypeUnderStructure_54a,
-- Item54b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item54b)
when 0 then cast(Item54b as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceUnderStructure_54b,
Item55a as LatClearanceTypeUnderStructure_55a,
-- Item55b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item55b)
when 0 then cast(Item55b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderStructure_55b,
-- Item56: 999 for "open", 998 for greater than 30 meters
case
when Item56 = '999' then cast(0 as decimal(7,1))
when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as decimal(7,1))/
10
else cast(0 as decimal(7,1))
end as LatClearanceUnderOnLeft_56,
Item58 as DeckConditionCode_58,
Item59 as SuperstructureConditionCode_59,
Item60 as SubstructureConditionCode_60,
Item61 as ChannelConditionCode_61,
Item62 as CulvertConditionCode_62,
Item63 as OperationRatingMethodCode_63,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item64)
when 0 then cast(Item64 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as OperatingRatingMetricTons_64,
Item65 as InvenRatingLoadMethodCode_65,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item66)
when 0 then cast(Item66 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRatingMetricTons_66,
Item67 as StructureEvalCode_67,
Item68 as DeckGeoEvalCode_68,
Item69 as UnderclearanceEvalCode_69,
Item70 as BridgePostingCode_70,
Item71 as WaterwayAdequacyCode_71,
item772 as ApprRoadwayAlignmentCode_72,
Item75a as WorkTypeCode_75a,
Item75b as WorkerTypeCode_75b,
case PATINDEX('%[^0-9.]%',Item76)
when 0 then cast(Item76 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as ImprovementSpan_76,
Item90 as LastInspectionMonthYear_90,
Item91 as InspectionFrequencyMonths_91,
item92a as CfiFractureCriticalDetailsCode_92a,
Item92b as CfiUnderwaterInspectionCode_92b,
Item92c as CfiOtherSpecialInspectionCode_92c,
Item93a as CfiFractureCriticalDetailsMonthYear_93a,
Item93b as CfiUnderwaterInspectionMonthYear_93b,
item93c as CfiOtherSpecialInspectionMonthYear_93c,
case PATINDEX('%[^0-9]%',Item94)
when 0 then cast(Item94 as decimal)*1000
else cast(0 as decimal)
end as BridgeImprovementCost_94,
case PATINDEX('%[^0-9]%',Item95)
when 0 then cast(Item95 as decimal)*1000
else cast(0 as decimal)
end as RoadwayImprovementCost_95,
case PATINDEX('%[^0-9]%',Item96)
when 0 then cast(Item96 as decimal)*1000
else cast(0 as decimal)
end as TotalImprovementCost_96,
Item97 as ImprovementCostEstYear_97,
Item98a as BorderBrNeighborStateCode_98a,
-- 99 means no responsibility for the structure
case
when Item98b = '99' then cast(0 as int)
when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
else cast(0 as int)
end as BorderBrPctResponsibility_98b,
-- This one could be an NBI id, or a state structure number
Item99 as BorderBrNeighborsStructureId_99,
Item100 as StrahnetHighwayDesCode_100,
Item101 as ParallelStructureDesCode_101,
Item102 as InvenRouteTrafficDirCode_102,
Item103 as TemporaryStructure_103,
Item104 as IsOnNhs_104,
Item105 as FederalLandsHighwayCode_105,
Item106 as YearReconstructed_106,
Item107 as DeckTypeCode_107,
Item108a as DeckProtectSurfaceTypeCode_108a,
Item108b as DeckProtectMembraneTypeCode_108b,
Item108c as DeckProtectTypeCode_108c,
case PATINDEX('%[^0-9]%',Item109)
when 0 then cast(Item109 as int)
else cast(0 as int)
end as AvgDailyTruckTrafficPct_109,
Item110 as IsOnNatlTruckNetwork_110,
Item111 as PierOrAbutmentProtectionCode_111,
Item112 as NbisLengthYesNo_112,
Item113 as ScourCriticalCode_113,
case PATINDEX('%[^0-9]%',Item114)
when 0 then cast(Item114 as int)
else cast(0 as int)
end as FutureAvgDailyTraffic_114,
Item115 as FutureAvgDailyTrafficYear_115,
case PATINDEX('%[^0-9.]%',Item116)
when 0 then cast(Item116 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavMinVertClearanceLiftBrClosed_116,
cast(' ' as varchar(22)) as CountyName,
cast(' ' as varchar(52)) as PlaceName
into STEEL
from NBI
GOSometimes something about posting to usenet makes it work...
There are decimal points in the PATINDEX functions. I suppose if there is
one (or two) in a source field, then it will try to convert. Got rid of
those and it works.
Mark
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
etc...|||You can add ISNUMERIC to your CASE in order to identify other invalid data,
such as multiple decimal points and empty strings:
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN CAST(Item10 AS decimal(7,2))/100
ELSE CAST(0 AS decimal(7,2)) END
If you need to identify rows with invalid data, specify the CASE statements
in a WHERE clause.
WHERE 'Invalid' =
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN 'Valid'
ELSE 'invalid' END
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark G. Meyers" <mmeyers[at]hydromilling.com> wrote in message
news:ep5pRIqYEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I have a big select into statement I'm running from QA (on the same
machine
> where the DB is). I've used it in the past, and it worked then. It's
with
> the NBI (National Bridge Inventory), trying to convert some data types, so
> there are over 600,000 records with 120 or so fields each. It runs for
> awhile, and I don't what what record or field produces the problem. How
can
> I get more info?
> Every field in the source table is NVARCHAR.
> The message after 5 minutes or so is:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type nvarchar to numeric
> TIA -
> Mark
> The statement:
> select
> identity(int,1,1) as ID,
> Item1 as StateCode_1,
> Item2 as HighwayDistrict_2,
> Item3 as CountyCode_3,
> Item4 as PlaceCode_4,
> Item5a as InvenRouteRecordType_5a,
> Item5b as InvenRouteSigningPrefix_5b,
> Item5c as InvenRouteLevelService_5c,
> item5d as InvenRouteNumber_5d,
> Item5e as InvenRouteDirSuffix_5e,
> Item6a as FeaturesIntersected_6a,
> Item7 as FacilityCarried_7,
> Item8 as StructureNumber_8,
> Item9 as LocationNarrative_9,
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
> Item12 as IsOnBaseHighwayNetwork_12,
> Item13a as LrsInvenRoute_13a,
> Item13b as LrsSubroute_13b,
> Item16 as Latitude_16,
> Item17 as Longitude_17,
> -- detours over 199 km are coded as 199
> case PATINDEX('%[^0-9]%',Item19)
> when 0 then cast(Item19 as int)
> else cast(0 as int)
> end as BypassDetourKilometers_19,
> Item20 as TollCode_20,
> Item21 as MaintRespCode_21,
> Item22 as MaintRespOwnerCode_22,
> Item26 as InvenRouteFunctionClass_26,
> Item27 as YearBuilt_27,
> case PATINDEX('%[^0-9]%',Item28a)
> when 0 then cast(Item28a as int)
> else cast(0 as int)
> end as LanesOn_28a,
> case PATINDEX('%[^0-9]%',Item28b)
> when 0 then cast(Item28b as int)
> else cast(0 as int)
> end as LanesUnder_28b,
> case PATINDEX('%[^0-9]%',Item29)
> when 0 then cast(Item29 as int)
> else cast(0 as int)
> end as AvgDailyTraffic_29,
> Item30 as AvgDailyTrafficYear_30,
> Item31 as DesignLoadCode_31,
> case PATINDEX('%[^0-9.]%',Item32)
> when 0 then cast(Item32 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as ApproachRoadwayWidth_32,
> Item33 as MedianExistOpenClosed_33,
> case PATINDEX('%[^0-9]%',Item34)
> when 0 then cast(Item34 as int)
> else cast(0 as int)
> end as RoadwayPierSkewDegrees_34,
> Item35 as StructureIsFlared_35,
> Item36a as TsRailings_36a,
> Item36b as TsTransitions_36b,
> Item36c as TsApprGuardrail_36c,
> item36d as TsApprGuardrailEnds_36d,
> Item37 as HistoricSigCode_37,
> Item38 as NavControlCode_38,
> case PATINDEX('%[^0-9.]%',Item39)
> when 0 then cast(Item39 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavVertClearance_39,
> case PATINDEX('%[^0-9.]%',Item40)
> when 0 then cast(Item40 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as NavHorzClearance_40,
> Item41 as OpenPostedClosedCode_41,
> Item42a as ServiceTypeOnCode_42a,
> item42b as ServiceTypeUnderCode_42b,
> Item43a as StructureMaterialTypeCode_43a,
> Item43b as StructureTypeCode_43b,
> Item44a as ApproachMaterialTypeCode_44a,
> Item44b as ApproachStructureTypeCode_44b,
> case PATINDEX('%[^0-9]%',Item45)
> when 0 then cast(Item45 as int)
> else cast(0 as int)
> end as MainSpans_45,
> case PATINDEX('%[^0-9]%',Item46)
> when 0 then cast(Item46 as int)
> else cast(0 as int)
> end as ApproachSpans_46,
> -- 100 meters or greater coded as 999, leave as is
> case PATINDEX('%[^0-9.]%',Item47)
> when 0 then cast(Item47 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRouteHorzClearance_47,
> case PATINDEX('%[^0-9.]%',Item48)
> when 0 then cast(Item48 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as MaxSpanLength_48,
> case PATINDEX('%[^0-9.]%',Item49)
> when 0 then cast(Item49 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as StructureLength_49,
> case PATINDEX('%[^0-9.]%',Item50a)
> when 0 then cast(Item50a as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthLeft_50a,
> case PATINDEX('%[^0-9.]%',Item50b)
> when 0 then cast(Item50b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthRight_50b,
> case PATINDEX('%[^0-9.]%',Item51)
> when 0 then cast(Item51 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbToCurbRoadwayWidth_51,
> case PATINDEX('%[^0-9.]%',Item52)
> when 0 then cast(Item52 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as DeckWidth_52,
> -- Item53: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item53)
> when 0 then cast(Item53 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceOverRoadway_53,
> Item54a as VertClearanceTypeUnderStructure_54a,
> -- Item54b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item54b)
> when 0 then cast(Item54b as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceUnderStructure_54b,
> Item55a as LatClearanceTypeUnderStructure_55a,
> -- Item55b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item55b)
> when 0 then cast(Item55b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderStructure_55b,
> -- Item56: 999 for "open", 998 for greater than 30 meters
> case
> when Item56 = '999' then cast(0 as decimal(7,1))
> when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as
decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderOnLeft_56,
> Item58 as DeckConditionCode_58,
> Item59 as SuperstructureConditionCode_59,
> Item60 as SubstructureConditionCode_60,
> Item61 as ChannelConditionCode_61,
> Item62 as CulvertConditionCode_62,
> Item63 as OperationRatingMethodCode_63,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item64)
> when 0 then cast(Item64 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as OperatingRatingMetricTons_64,
> Item65 as InvenRatingLoadMethodCode_65,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item66)
> when 0 then cast(Item66 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRatingMetricTons_66,
> Item67 as StructureEvalCode_67,
> Item68 as DeckGeoEvalCode_68,
> Item69 as UnderclearanceEvalCode_69,
> Item70 as BridgePostingCode_70,
> Item71 as WaterwayAdequacyCode_71,
> item772 as ApprRoadwayAlignmentCode_72,
> Item75a as WorkTypeCode_75a,
> Item75b as WorkerTypeCode_75b,
> case PATINDEX('%[^0-9.]%',Item76)
> when 0 then cast(Item76 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as ImprovementSpan_76,
> Item90 as LastInspectionMonthYear_90,
> Item91 as InspectionFrequencyMonths_91,
> item92a as CfiFractureCriticalDetailsCode_92a,
> Item92b as CfiUnderwaterInspectionCode_92b,
> Item92c as CfiOtherSpecialInspectionCode_92c,
> Item93a as CfiFractureCriticalDetailsMonthYear_93a,
> Item93b as CfiUnderwaterInspectionMonthYear_93b,
> item93c as CfiOtherSpecialInspectionMonthYear_93c,
> case PATINDEX('%[^0-9]%',Item94)
> when 0 then cast(Item94 as decimal)*1000
> else cast(0 as decimal)
> end as BridgeImprovementCost_94,
> case PATINDEX('%[^0-9]%',Item95)
> when 0 then cast(Item95 as decimal)*1000
> else cast(0 as decimal)
> end as RoadwayImprovementCost_95,
> case PATINDEX('%[^0-9]%',Item96)
> when 0 then cast(Item96 as decimal)*1000
> else cast(0 as decimal)
> end as TotalImprovementCost_96,
> Item97 as ImprovementCostEstYear_97,
> Item98a as BorderBrNeighborStateCode_98a,
> -- 99 means no responsibility for the structure
> case
> when Item98b = '99' then cast(0 as int)
> when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
> else cast(0 as int)
> end as BorderBrPctResponsibility_98b,
> -- This one could be an NBI id, or a state structure number
> Item99 as BorderBrNeighborsStructureId_99,
> Item100 as StrahnetHighwayDesCode_100,
> Item101 as ParallelStructureDesCode_101,
> Item102 as InvenRouteTrafficDirCode_102,
> Item103 as TemporaryStructure_103,
> Item104 as IsOnNhs_104,
> Item105 as FederalLandsHighwayCode_105,
> Item106 as YearReconstructed_106,
> Item107 as DeckTypeCode_107,
> Item108a as DeckProtectSurfaceTypeCode_108a,
> Item108b as DeckProtectMembraneTypeCode_108b,
> Item108c as DeckProtectTypeCode_108c,
> case PATINDEX('%[^0-9]%',Item109)
> when 0 then cast(Item109 as int)
> else cast(0 as int)
> end as AvgDailyTruckTrafficPct_109,
> Item110 as IsOnNatlTruckNetwork_110,
> Item111 as PierOrAbutmentProtectionCode_111,
> Item112 as NbisLengthYesNo_112,
> Item113 as ScourCriticalCode_113,
> case PATINDEX('%[^0-9]%',Item114)
> when 0 then cast(Item114 as int)
> else cast(0 as int)
> end as FutureAvgDailyTraffic_114,
> Item115 as FutureAvgDailyTrafficYear_115,
> case PATINDEX('%[^0-9.]%',Item116)
> when 0 then cast(Item116 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavMinVertClearanceLiftBrClosed_116,
> cast(' ' as varchar(22)) as CountyName,
> cast(' ' as varchar(52)) as PlaceName
> into STEEL
> from NBI
> GO
>
where the DB is). I've used it in the past, and it worked then. It's with
the NBI (National Bridge Inventory), trying to convert some data types, so
there are over 600,000 records with 120 or so fields each. It runs for
awhile, and I don't what what record or field produces the problem. How can
I get more info?
Every field in the source table is NVARCHAR.
The message after 5 minutes or so is:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric
TIA -
Mark
The statement:
select
identity(int,1,1) as ID,
Item1 as StateCode_1,
Item2 as HighwayDistrict_2,
Item3 as CountyCode_3,
Item4 as PlaceCode_4,
Item5a as InvenRouteRecordType_5a,
Item5b as InvenRouteSigningPrefix_5b,
Item5c as InvenRouteLevelService_5c,
item5d as InvenRouteNumber_5d,
Item5e as InvenRouteDirSuffix_5e,
Item6a as FeaturesIntersected_6a,
Item7 as FacilityCarried_7,
Item8 as StructureNumber_8,
Item9 as LocationNarrative_9,
-- 99.99 is a possible vert clearance (over 30 meters), don't care
case PATINDEX('%[^0-9.]%',Item10)
when 0 then cast(Item10 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as InvenRouteMinVertClearance_10,
case PATINDEX('%[^0-9.]%',Item11)
when 0 then cast(Item11 as decimal(12,3))/1000
else cast(0 as decimal(12,3))
end as LrsKilometerPoint_11,
Item12 as IsOnBaseHighwayNetwork_12,
Item13a as LrsInvenRoute_13a,
Item13b as LrsSubroute_13b,
Item16 as Latitude_16,
Item17 as Longitude_17,
-- detours over 199 km are coded as 199
case PATINDEX('%[^0-9]%',Item19)
when 0 then cast(Item19 as int)
else cast(0 as int)
end as BypassDetourKilometers_19,
Item20 as TollCode_20,
Item21 as MaintRespCode_21,
Item22 as MaintRespOwnerCode_22,
Item26 as InvenRouteFunctionClass_26,
Item27 as YearBuilt_27,
case PATINDEX('%[^0-9]%',Item28a)
when 0 then cast(Item28a as int)
else cast(0 as int)
end as LanesOn_28a,
case PATINDEX('%[^0-9]%',Item28b)
when 0 then cast(Item28b as int)
else cast(0 as int)
end as LanesUnder_28b,
case PATINDEX('%[^0-9]%',Item29)
when 0 then cast(Item29 as int)
else cast(0 as int)
end as AvgDailyTraffic_29,
Item30 as AvgDailyTrafficYear_30,
Item31 as DesignLoadCode_31,
case PATINDEX('%[^0-9.]%',Item32)
when 0 then cast(Item32 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as ApproachRoadwayWidth_32,
Item33 as MedianExistOpenClosed_33,
case PATINDEX('%[^0-9]%',Item34)
when 0 then cast(Item34 as int)
else cast(0 as int)
end as RoadwayPierSkewDegrees_34,
Item35 as StructureIsFlared_35,
Item36a as TsRailings_36a,
Item36b as TsTransitions_36b,
Item36c as TsApprGuardrail_36c,
item36d as TsApprGuardrailEnds_36d,
Item37 as HistoricSigCode_37,
Item38 as NavControlCode_38,
case PATINDEX('%[^0-9.]%',Item39)
when 0 then cast(Item39 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavVertClearance_39,
case PATINDEX('%[^0-9.]%',Item40)
when 0 then cast(Item40 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as NavHorzClearance_40,
Item41 as OpenPostedClosedCode_41,
Item42a as ServiceTypeOnCode_42a,
item42b as ServiceTypeUnderCode_42b,
Item43a as StructureMaterialTypeCode_43a,
Item43b as StructureTypeCode_43b,
Item44a as ApproachMaterialTypeCode_44a,
Item44b as ApproachStructureTypeCode_44b,
case PATINDEX('%[^0-9]%',Item45)
when 0 then cast(Item45 as int)
else cast(0 as int)
end as MainSpans_45,
case PATINDEX('%[^0-9]%',Item46)
when 0 then cast(Item46 as int)
else cast(0 as int)
end as ApproachSpans_46,
-- 100 meters or greater coded as 999, leave as is
case PATINDEX('%[^0-9.]%',Item47)
when 0 then cast(Item47 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRouteHorzClearance_47,
case PATINDEX('%[^0-9.]%',Item48)
when 0 then cast(Item48 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as MaxSpanLength_48,
case PATINDEX('%[^0-9.]%',Item49)
when 0 then cast(Item49 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as StructureLength_49,
case PATINDEX('%[^0-9.]%',Item50a)
when 0 then cast(Item50a as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthLeft_50a,
case PATINDEX('%[^0-9.]%',Item50b)
when 0 then cast(Item50b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbSidewalkWidthRight_50b,
case PATINDEX('%[^0-9.]%',Item51)
when 0 then cast(Item51 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as CurbToCurbRoadwayWidth_51,
case PATINDEX('%[^0-9.]%',Item52)
when 0 then cast(Item52 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as DeckWidth_52,
-- Item53: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item53)
when 0 then cast(Item53 as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceOverRoadway_53,
Item54a as VertClearanceTypeUnderStructure_54a,
-- Item54b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item54b)
when 0 then cast(Item54b as decimal(7,2))/100
else cast(0 as decimal(7,2))
end as VertClearanceUnderStructure_54b,
Item55a as LatClearanceTypeUnderStructure_55a,
-- Item55b: 9999 coded for values greater than 30 meters
case PATINDEX('%[^0-9.]%',Item55b)
when 0 then cast(Item55b as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as LatClearanceUnderStructure_55b,
-- Item56: 999 for "open", 998 for greater than 30 meters
case
when Item56 = '999' then cast(0 as decimal(7,1))
when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as decimal(7,1))/
10
else cast(0 as decimal(7,1))
end as LatClearanceUnderOnLeft_56,
Item58 as DeckConditionCode_58,
Item59 as SuperstructureConditionCode_59,
Item60 as SubstructureConditionCode_60,
Item61 as ChannelConditionCode_61,
Item62 as CulvertConditionCode_62,
Item63 as OperationRatingMethodCode_63,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item64)
when 0 then cast(Item64 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as OperatingRatingMetricTons_64,
Item65 as InvenRatingLoadMethodCode_65,
-- 999 coded for "live load is insignificant in structure capacity"
case PATINDEX('%[^0-9.]%',Item66)
when 0 then cast(Item66 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as InvenRatingMetricTons_66,
Item67 as StructureEvalCode_67,
Item68 as DeckGeoEvalCode_68,
Item69 as UnderclearanceEvalCode_69,
Item70 as BridgePostingCode_70,
Item71 as WaterwayAdequacyCode_71,
item772 as ApprRoadwayAlignmentCode_72,
Item75a as WorkTypeCode_75a,
Item75b as WorkerTypeCode_75b,
case PATINDEX('%[^0-9.]%',Item76)
when 0 then cast(Item76 as decimal(9,1))/10
else cast(0 as decimal(9,1))
end as ImprovementSpan_76,
Item90 as LastInspectionMonthYear_90,
Item91 as InspectionFrequencyMonths_91,
item92a as CfiFractureCriticalDetailsCode_92a,
Item92b as CfiUnderwaterInspectionCode_92b,
Item92c as CfiOtherSpecialInspectionCode_92c,
Item93a as CfiFractureCriticalDetailsMonthYear_93a,
Item93b as CfiUnderwaterInspectionMonthYear_93b,
item93c as CfiOtherSpecialInspectionMonthYear_93c,
case PATINDEX('%[^0-9]%',Item94)
when 0 then cast(Item94 as decimal)*1000
else cast(0 as decimal)
end as BridgeImprovementCost_94,
case PATINDEX('%[^0-9]%',Item95)
when 0 then cast(Item95 as decimal)*1000
else cast(0 as decimal)
end as RoadwayImprovementCost_95,
case PATINDEX('%[^0-9]%',Item96)
when 0 then cast(Item96 as decimal)*1000
else cast(0 as decimal)
end as TotalImprovementCost_96,
Item97 as ImprovementCostEstYear_97,
Item98a as BorderBrNeighborStateCode_98a,
-- 99 means no responsibility for the structure
case
when Item98b = '99' then cast(0 as int)
when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
else cast(0 as int)
end as BorderBrPctResponsibility_98b,
-- This one could be an NBI id, or a state structure number
Item99 as BorderBrNeighborsStructureId_99,
Item100 as StrahnetHighwayDesCode_100,
Item101 as ParallelStructureDesCode_101,
Item102 as InvenRouteTrafficDirCode_102,
Item103 as TemporaryStructure_103,
Item104 as IsOnNhs_104,
Item105 as FederalLandsHighwayCode_105,
Item106 as YearReconstructed_106,
Item107 as DeckTypeCode_107,
Item108a as DeckProtectSurfaceTypeCode_108a,
Item108b as DeckProtectMembraneTypeCode_108b,
Item108c as DeckProtectTypeCode_108c,
case PATINDEX('%[^0-9]%',Item109)
when 0 then cast(Item109 as int)
else cast(0 as int)
end as AvgDailyTruckTrafficPct_109,
Item110 as IsOnNatlTruckNetwork_110,
Item111 as PierOrAbutmentProtectionCode_111,
Item112 as NbisLengthYesNo_112,
Item113 as ScourCriticalCode_113,
case PATINDEX('%[^0-9]%',Item114)
when 0 then cast(Item114 as int)
else cast(0 as int)
end as FutureAvgDailyTraffic_114,
Item115 as FutureAvgDailyTrafficYear_115,
case PATINDEX('%[^0-9.]%',Item116)
when 0 then cast(Item116 as decimal(7,1))/10
else cast(0 as decimal(7,1))
end as NavMinVertClearanceLiftBrClosed_116,
cast(' ' as varchar(22)) as CountyName,
cast(' ' as varchar(52)) as PlaceName
into STEEL
from NBI
GOSometimes something about posting to usenet makes it work...
There are decimal points in the PATINDEX functions. I suppose if there is
one (or two) in a source field, then it will try to convert. Got rid of
those and it works.
Mark
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
etc...|||You can add ISNUMERIC to your CASE in order to identify other invalid data,
such as multiple decimal points and empty strings:
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN CAST(Item10 AS decimal(7,2))/100
ELSE CAST(0 AS decimal(7,2)) END
If you need to identify rows with invalid data, specify the CASE statements
in a WHERE clause.
WHERE 'Invalid' =
CASE WHEN
PATINDEX('%[^0-9.]%',Item10) = 0 AND ISNUMERIC(Item10) = 1
THEN 'Valid'
ELSE 'invalid' END
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark G. Meyers" <mmeyers[at]hydromilling.com> wrote in message
news:ep5pRIqYEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I have a big select into statement I'm running from QA (on the same
machine
> where the DB is). I've used it in the past, and it worked then. It's
with
> the NBI (National Bridge Inventory), trying to convert some data types, so
> there are over 600,000 records with 120 or so fields each. It runs for
> awhile, and I don't what what record or field produces the problem. How
can
> I get more info?
> Every field in the source table is NVARCHAR.
> The message after 5 minutes or so is:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type nvarchar to numeric
> TIA -
> Mark
> The statement:
> select
> identity(int,1,1) as ID,
> Item1 as StateCode_1,
> Item2 as HighwayDistrict_2,
> Item3 as CountyCode_3,
> Item4 as PlaceCode_4,
> Item5a as InvenRouteRecordType_5a,
> Item5b as InvenRouteSigningPrefix_5b,
> Item5c as InvenRouteLevelService_5c,
> item5d as InvenRouteNumber_5d,
> Item5e as InvenRouteDirSuffix_5e,
> Item6a as FeaturesIntersected_6a,
> Item7 as FacilityCarried_7,
> Item8 as StructureNumber_8,
> Item9 as LocationNarrative_9,
> -- 99.99 is a possible vert clearance (over 30 meters), don't care
> case PATINDEX('%[^0-9.]%',Item10)
> when 0 then cast(Item10 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as InvenRouteMinVertClearance_10,
> case PATINDEX('%[^0-9.]%',Item11)
> when 0 then cast(Item11 as decimal(12,3))/1000
> else cast(0 as decimal(12,3))
> end as LrsKilometerPoint_11,
> Item12 as IsOnBaseHighwayNetwork_12,
> Item13a as LrsInvenRoute_13a,
> Item13b as LrsSubroute_13b,
> Item16 as Latitude_16,
> Item17 as Longitude_17,
> -- detours over 199 km are coded as 199
> case PATINDEX('%[^0-9]%',Item19)
> when 0 then cast(Item19 as int)
> else cast(0 as int)
> end as BypassDetourKilometers_19,
> Item20 as TollCode_20,
> Item21 as MaintRespCode_21,
> Item22 as MaintRespOwnerCode_22,
> Item26 as InvenRouteFunctionClass_26,
> Item27 as YearBuilt_27,
> case PATINDEX('%[^0-9]%',Item28a)
> when 0 then cast(Item28a as int)
> else cast(0 as int)
> end as LanesOn_28a,
> case PATINDEX('%[^0-9]%',Item28b)
> when 0 then cast(Item28b as int)
> else cast(0 as int)
> end as LanesUnder_28b,
> case PATINDEX('%[^0-9]%',Item29)
> when 0 then cast(Item29 as int)
> else cast(0 as int)
> end as AvgDailyTraffic_29,
> Item30 as AvgDailyTrafficYear_30,
> Item31 as DesignLoadCode_31,
> case PATINDEX('%[^0-9.]%',Item32)
> when 0 then cast(Item32 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as ApproachRoadwayWidth_32,
> Item33 as MedianExistOpenClosed_33,
> case PATINDEX('%[^0-9]%',Item34)
> when 0 then cast(Item34 as int)
> else cast(0 as int)
> end as RoadwayPierSkewDegrees_34,
> Item35 as StructureIsFlared_35,
> Item36a as TsRailings_36a,
> Item36b as TsTransitions_36b,
> Item36c as TsApprGuardrail_36c,
> item36d as TsApprGuardrailEnds_36d,
> Item37 as HistoricSigCode_37,
> Item38 as NavControlCode_38,
> case PATINDEX('%[^0-9.]%',Item39)
> when 0 then cast(Item39 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavVertClearance_39,
> case PATINDEX('%[^0-9.]%',Item40)
> when 0 then cast(Item40 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as NavHorzClearance_40,
> Item41 as OpenPostedClosedCode_41,
> Item42a as ServiceTypeOnCode_42a,
> item42b as ServiceTypeUnderCode_42b,
> Item43a as StructureMaterialTypeCode_43a,
> Item43b as StructureTypeCode_43b,
> Item44a as ApproachMaterialTypeCode_44a,
> Item44b as ApproachStructureTypeCode_44b,
> case PATINDEX('%[^0-9]%',Item45)
> when 0 then cast(Item45 as int)
> else cast(0 as int)
> end as MainSpans_45,
> case PATINDEX('%[^0-9]%',Item46)
> when 0 then cast(Item46 as int)
> else cast(0 as int)
> end as ApproachSpans_46,
> -- 100 meters or greater coded as 999, leave as is
> case PATINDEX('%[^0-9.]%',Item47)
> when 0 then cast(Item47 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRouteHorzClearance_47,
> case PATINDEX('%[^0-9.]%',Item48)
> when 0 then cast(Item48 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as MaxSpanLength_48,
> case PATINDEX('%[^0-9.]%',Item49)
> when 0 then cast(Item49 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as StructureLength_49,
> case PATINDEX('%[^0-9.]%',Item50a)
> when 0 then cast(Item50a as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthLeft_50a,
> case PATINDEX('%[^0-9.]%',Item50b)
> when 0 then cast(Item50b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbSidewalkWidthRight_50b,
> case PATINDEX('%[^0-9.]%',Item51)
> when 0 then cast(Item51 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as CurbToCurbRoadwayWidth_51,
> case PATINDEX('%[^0-9.]%',Item52)
> when 0 then cast(Item52 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as DeckWidth_52,
> -- Item53: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item53)
> when 0 then cast(Item53 as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceOverRoadway_53,
> Item54a as VertClearanceTypeUnderStructure_54a,
> -- Item54b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item54b)
> when 0 then cast(Item54b as decimal(7,2))/100
> else cast(0 as decimal(7,2))
> end as VertClearanceUnderStructure_54b,
> Item55a as LatClearanceTypeUnderStructure_55a,
> -- Item55b: 9999 coded for values greater than 30 meters
> case PATINDEX('%[^0-9.]%',Item55b)
> when 0 then cast(Item55b as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderStructure_55b,
> -- Item56: 999 for "open", 998 for greater than 30 meters
> case
> when Item56 = '999' then cast(0 as decimal(7,1))
> when PATINDEX('%[^0-9.]%',Item56) = 0 then cast(Item56 as
decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as LatClearanceUnderOnLeft_56,
> Item58 as DeckConditionCode_58,
> Item59 as SuperstructureConditionCode_59,
> Item60 as SubstructureConditionCode_60,
> Item61 as ChannelConditionCode_61,
> Item62 as CulvertConditionCode_62,
> Item63 as OperationRatingMethodCode_63,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item64)
> when 0 then cast(Item64 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as OperatingRatingMetricTons_64,
> Item65 as InvenRatingLoadMethodCode_65,
> -- 999 coded for "live load is insignificant in structure capacity"
> case PATINDEX('%[^0-9.]%',Item66)
> when 0 then cast(Item66 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as InvenRatingMetricTons_66,
> Item67 as StructureEvalCode_67,
> Item68 as DeckGeoEvalCode_68,
> Item69 as UnderclearanceEvalCode_69,
> Item70 as BridgePostingCode_70,
> Item71 as WaterwayAdequacyCode_71,
> item772 as ApprRoadwayAlignmentCode_72,
> Item75a as WorkTypeCode_75a,
> Item75b as WorkerTypeCode_75b,
> case PATINDEX('%[^0-9.]%',Item76)
> when 0 then cast(Item76 as decimal(9,1))/10
> else cast(0 as decimal(9,1))
> end as ImprovementSpan_76,
> Item90 as LastInspectionMonthYear_90,
> Item91 as InspectionFrequencyMonths_91,
> item92a as CfiFractureCriticalDetailsCode_92a,
> Item92b as CfiUnderwaterInspectionCode_92b,
> Item92c as CfiOtherSpecialInspectionCode_92c,
> Item93a as CfiFractureCriticalDetailsMonthYear_93a,
> Item93b as CfiUnderwaterInspectionMonthYear_93b,
> item93c as CfiOtherSpecialInspectionMonthYear_93c,
> case PATINDEX('%[^0-9]%',Item94)
> when 0 then cast(Item94 as decimal)*1000
> else cast(0 as decimal)
> end as BridgeImprovementCost_94,
> case PATINDEX('%[^0-9]%',Item95)
> when 0 then cast(Item95 as decimal)*1000
> else cast(0 as decimal)
> end as RoadwayImprovementCost_95,
> case PATINDEX('%[^0-9]%',Item96)
> when 0 then cast(Item96 as decimal)*1000
> else cast(0 as decimal)
> end as TotalImprovementCost_96,
> Item97 as ImprovementCostEstYear_97,
> Item98a as BorderBrNeighborStateCode_98a,
> -- 99 means no responsibility for the structure
> case
> when Item98b = '99' then cast(0 as int)
> when PATINDEX('%[^0-9.]%',Item98b) = 0 then cast(Item98b as int)
> else cast(0 as int)
> end as BorderBrPctResponsibility_98b,
> -- This one could be an NBI id, or a state structure number
> Item99 as BorderBrNeighborsStructureId_99,
> Item100 as StrahnetHighwayDesCode_100,
> Item101 as ParallelStructureDesCode_101,
> Item102 as InvenRouteTrafficDirCode_102,
> Item103 as TemporaryStructure_103,
> Item104 as IsOnNhs_104,
> Item105 as FederalLandsHighwayCode_105,
> Item106 as YearReconstructed_106,
> Item107 as DeckTypeCode_107,
> Item108a as DeckProtectSurfaceTypeCode_108a,
> Item108b as DeckProtectMembraneTypeCode_108b,
> Item108c as DeckProtectTypeCode_108c,
> case PATINDEX('%[^0-9]%',Item109)
> when 0 then cast(Item109 as int)
> else cast(0 as int)
> end as AvgDailyTruckTrafficPct_109,
> Item110 as IsOnNatlTruckNetwork_110,
> Item111 as PierOrAbutmentProtectionCode_111,
> Item112 as NbisLengthYesNo_112,
> Item113 as ScourCriticalCode_113,
> case PATINDEX('%[^0-9]%',Item114)
> when 0 then cast(Item114 as int)
> else cast(0 as int)
> end as FutureAvgDailyTraffic_114,
> Item115 as FutureAvgDailyTrafficYear_115,
> case PATINDEX('%[^0-9.]%',Item116)
> when 0 then cast(Item116 as decimal(7,1))/10
> else cast(0 as decimal(7,1))
> end as NavMinVertClearanceLiftBrClosed_116,
> cast(' ' as varchar(22)) as CountyName,
> cast(' ' as varchar(52)) as PlaceName
> into STEEL
> from NBI
> GO
>
Subscribe to:
Posts (Atom)