Column name or number of supplied values?

Question

Post reply

Column name or number of supplied values?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
April 4, 2013 at 6:59 am

#272712

Hi,
what is wrong in my coding? I want monitor some time interval for IO pending task witing in queue..
Error:
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
create table PendingIO (
[database] varchar (100),
Physical_name varchar (100),
io_pending int,
io_pending_ms_ticks int,
io_type varchar (20),
num_of_reads int,
num_of_writes int, time_stamp datetime default getdate())
insert into PendingIO values (
'[database]',
'Physical_name',
'io_pending',
'io_pending_ms_ticks',
'io_type',
'num_of_reads',
'num_of_writes')
SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,
r.io_pending , r.io_pending_ms_ticks , r.io_type , fs.num_of_reads ,
fs.num_of_writes FROM sys.dm_io_pending_io_requests AS r INNER JOIN
sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
ON r.io_handle = fs.file_handle INNER JOIN
sys.master_files AS mf ON fs.database_id = mf.database_id
AND fs.file_id = mf.file_id ORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;
thanks
ananda

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

there's a VALUES command hanging out there, and the column names should not be in single quotes;

create table PendingIO (

[database] varchar (100),

Physical_name varchar (100),

io_pending int,

io_pending_ms_ticks int,

io_type varchar (20),

num_of_reads int,

num_of_writes int,

time_stamp datetime default getdate())

INSERT INTO PendingIO ( [database],Physical_name,io_pending,io_pending_ms_ticks,io_type,num_of_reads,num_of_writes)

SELECT

DB_NAME(mf.database_id) AS [Database],

mf.physical_name,

r.io_pending,

r.io_pending_ms_ticks,

r.io_type,

fs.num_of_reads,

fs.num_of_writes

FROM sys.dm_io_pending_io_requests AS r

INNER JOIN sys.DM_IO_VIRTUAL_FILE_STATS(NULL, NULL) AS fs

ON r.io_handle = fs.file_handle

INNER JOIN sys.master_files AS mf

ON fs.database_id = mf.database_id

AND fs.file_id = mf.file_id

ORDER BY

r.io_pending,

r.io_pending_ms_ticks DESC;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Try this:

insert into PendingIO(

[database],

Physical_name,

io_pending,

io_pending_ms_ticks,

io_type,

num_of_reads,

num_of_writes

)

SELECT

DB_NAME(mf.database_id) AS [Database],

mf.physical_name ,

r.io_pending,

r.io_pending_ms_ticks,

r.io_type,

fs.num_of_reads,

fs.num_of_writes

FROM

sys.dm_io_pending_io_requests AS r

INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs

ON r.io_handle = fs.file_handle

INNER JOIN sys.master_files AS mf

ON fs.database_id = mf.database_id

AND fs.file_id = mf.file_id

ORDER BY

r.io_pending,

r.io_pending_ms_ticks DESC;

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 3

yes.. it is working, thanks

what is the best intervel time configure at scheduling job for monitoring IO usages?

Thanks

ananda