Hi all,
Just wondering what would be the normal or more efficient practice to insert/update a record.
1. Check for existence of primary record (using SELECT in stored procedure)
2. Capture error and handling
My problem is that I try to execute an stored procedure from a VB client.. but unable to capture the errors in SP, it just prompts the error and thats it, not responding to my "SELECT @.err = @.@.ERROR" after the insert statement.
so now, I'm thinking of capturing the error on the client (which I am able to do) and handle it from there.. or to make sure that RI is enforced by 'searching' for Pks in the primary tables before executing the INSERT statement in my stored procedure.
Any advise would be appreciated..
CyherusHi all,
Just wondering what would be the normal or more efficient practice to insert/update a record.
1. Check for existence of primary record (using SELECT in stored procedure)
2. Capture error and handling
My problem is that I try to execute an stored procedure from a VB client.. but unable to capture the errors in SP, it just prompts the error and thats it, not responding to my "SELECT @.err = @.@.ERROR" after the insert statement.
so now, I'm thinking of capturing the error on the client (which I am able to do) and handle it from there.. or to make sure that RI is enforced by 'searching' for Pks in the primary tables before executing the INSERT statement in my stored procedure.
Any advise would be appreciated..
Cyherus
Any error above 16 won't be caught. You need to catch those at the client or app level. If you're having these types of RI issues though, you have an app design issue. The PK should already be known by the app when you go to insert foreign key records. On the insert of a new PK, it should either be auto or a true natural key. In this case, there is no chance of error.|||Many thanks.. now things are much clearer..
I'm reading these data from a text file and foreign keys on these records are not checked against the primary tables in my DB, thus the need to handle this.
I dun quite get you when you mention about inserting new PKs, what do you mean by auto or true natural key??
Showing posts with label error. Show all posts
Showing posts with label error. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Check image in a SQL Server DB
Hello!
I want to read an image from the database. I've this function but she returns me an error that suggest me that the image might be damaged or corrupted.
I would like to know if it's possible and how to check an image in the DB.
Thank you!
i use the datalenght function to check the size of the image in the DB, it returned "1" in some cases and "null".
I assumed that the imagens aren't ok, and i'll see if i can find the original ones to upload them to the BD.
Am i correct? Can i assume that the number one (1), returned from the function "datalength" means that the images have 1byte of size which i know it's false?
Thank you
Tuesday, March 20, 2012
Check For Null Value
Hello,
I have to check for a value of NULL in my field and keep getting a syntax error.
What have I done wrong?
=SUM(IIF(Fields!new_status.value IS NULL, CInt(Fields!Total_NotRec.value), 0))
Is this Excel or Access or something?
|||Look into the IsDBNull() method (instead of using IS NULL). Use IS NULL within a SQL statement to check for null. Use IsNull() within a SQL statement to return a different value if the field is null. Use IsDBNull() within your VB code to check if a database field is null.
Also, if you're using a DataTable there will be a boolean method for each nullable column called Is[ColumnName]Null().
||| In SRS, they have a function IsNothing.
When I wrapped the IsNothing function around this, I got a sum of 0.
SUM(IIF(Fields!new_rpcstatus.value IS NULL, CInt(Fields!Total_NotRec.value), 0))
Is there any other way to check for the NULL value?
|||
Hi,
From your description, you are unable to calculate the total value
I'm not sure how do you handle with IsNothing function? Have tried the following expression?
=SUM(IIf(IsNothing(Fields!ANYFIELD.Value),0,Fields!ANYFIELD.Value))
OR
=SUM(IIf(Fields! ANYFIELD.Value is nothing, 0, Fields! ANYFIELD.Value))
If the issue still exists, please use IIF function to check each row's value, to see if it matches the conditions.
Thanks.
Monday, March 19, 2012
Check error question.
Hi All,
I need check if one of thise SP had error.
EXEC gerar_dnum_sp @.dnum OUTPUT
EXEC gerar_auto_sp @.auto OUTPUT
EXEC gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
Then I did this:
EXEC gerar_dnum_sp @.dnum OUTPUT
IF @.@.ERROR <> 0
BEGIN
...
END
EXEC gerar_auto_sp @.auto OUTPUT
IF @.@.ERROR <> 0
BEGIN
...
END
EXEC gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
IF @.@.ERROR <> 0
BEGIN
...
END
But I need 3 IF @.@.ERROR <> 0..., i would like know if have way to check all
in one time.
like this:
EXEC gerar_dnum_sp @.dnum OUTPUT
EXEC gerar_auto_sp @.auto OUTPUT
EXEC gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
IF(error in same one)
BEGIN
do same thing
END
Thanks to helpOn Sun, 13 Nov 2005 21:45:21 -0200, ReTF wrote:
(snip)
>But I need 3 IF @.@.ERROR <> 0..., i would like know if have way to check al
l
>in one time.
>like this:
>EXEC gerar_dnum_sp @.dnum OUTPUT
>EXEC gerar_auto_sp @.auto OUTPUT
>EXEC gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
>IF(error in same one)
>BEGIN
>do same thing
>END
>Thanks to help
Hi ReTF,
You'll have to do SOME work in between the calls, even though you don't
need to cpopy the complete error handling three times.
Most simple:
DECLARE @.err1 int, @.err2 int, @.err3 int
EXEC gerar_dnum_sp @.dnum OUTPUT
SET @.err1 = @.@.ERROR
EXEC gerar_auto_sp @.auto OUTPUT
SET @.err2 = @.@.ERROR
EXEC gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
SET @.err3 = @.@.ERROR
IF @.err1 <> 0 OR @.err2 <> 0 OR @.err3 <> 0
BEGIN
...
END
Or, better yet because you mostly don't want to keep on executing code
once an error is found:
DECLARE @.err int
EXEC gerar_dnum_sp @.dnum OUTPUT
SET @.err = @.@.ERROR
IF @.err = 0
BEGIN
EXEC gerar_auto_sp @.auto OUTPUT
SET @.err = @.@.ERROR
END
IF @.err = 0
BEGIN
EXEC gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
SET @.err = @.@.ERROR
END
IF @.err <> 0
BEGIN
...
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello,
You may use the return status:
DECLARE @.retstat1 int, @.retstat2 int, @.retstat3 int
DECLARE @.dnum int, @.auto int, @.aute int
EXEC @.retstat1=gerar_dnum_sp @.dnum OUTPUT
EXEC @.retstat2=gerar_auto_sp @.auto OUTPUT
EXEC @.retstat3=gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
if @.retstat1<>0 or @.retstat2<>0 or @.retstat3<>0
begin
print 'error'
end
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I did see
BEGIN TRY ...
I think that this is good to do what I need.
See:
BEGIN TRY
EXEC gerar_dnum_sp @.dnum OUTPUT
EXEC gerar_auto_sp @.auto OUTPUT
EXEC _aute_sp @.dnum, @.auto, @.aute OUTPUTTRY
--and more...
END TRY
BEGIN CATCH
-- if error do samething here
END CATH
What you think?
Thanks
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:8q12SPP6FHA.2536@.TK2MSFTNGXA02.phx.gbl...
> Hello,
> You may use the return status:
> DECLARE @.retstat1 int, @.retstat2 int, @.retstat3 int
> DECLARE @.dnum int, @.auto int, @.aute int
> EXEC @.retstat1=gerar_dnum_sp @.dnum OUTPUT
> EXEC @.retstat2=gerar_auto_sp @.auto OUTPUT
> EXEC @.retstat3=gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
> if @.retstat1<>0 or @.retstat2<>0 or @.retstat3<>0
> begin
> print 'error'
> end
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||TRY ... CATCH is a good way of doing error handling if you have SQL 2005.
Otherwise, (sql7/2000)
You should put an @.@.ERROR catch after every failable statement within the
stored proc, if the @.@.ERROR return it to the caller.
Checking @.@.ERROR after the SP wont guarentee you'll catch the error (as it
may have run other commands since the error and reset it back to 0)
EXEC @.re1 = gerar_dnum_sp @.dnum OUTPUT
EXEC @.re2 = gerar_auto_sp @.auto OUTPUT
EXEC @.re3 = gerar_aute_sp @.dnum, @.auto, @.aute OUTPUTTRY
IF ( @.re1 != 0 OR @.re2 != 0 OR @.re3 != 0 )
BEGIN
PRINT '...'
END
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:eb90wBU6FHA.1148@.tk2msftngp13.phx.gbl...
> Hi,
> I did see
> BEGIN TRY ...
> I think that this is good to do what I need.
> See:
> BEGIN TRY
> EXEC gerar_dnum_sp @.dnum OUTPUT
> EXEC gerar_auto_sp @.auto OUTPUT
> EXEC _aute_sp @.dnum, @.auto, @.aute OUTPUTTRY
> --and more...
> END TRY
> BEGIN CATCH
> -- if error do samething here
> END CATH
>
> What you think?
>
> Thanks
> "Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
> news:8q12SPP6FHA.2536@.TK2MSFTNGXA02.phx.gbl...
>
I need check if one of thise SP had error.
EXEC gerar_dnum_sp @.dnum OUTPUT
EXEC gerar_auto_sp @.auto OUTPUT
EXEC gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
Then I did this:
EXEC gerar_dnum_sp @.dnum OUTPUT
IF @.@.ERROR <> 0
BEGIN
...
END
EXEC gerar_auto_sp @.auto OUTPUT
IF @.@.ERROR <> 0
BEGIN
...
END
EXEC gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
IF @.@.ERROR <> 0
BEGIN
...
END
But I need 3 IF @.@.ERROR <> 0..., i would like know if have way to check all
in one time.
like this:
EXEC gerar_dnum_sp @.dnum OUTPUT
EXEC gerar_auto_sp @.auto OUTPUT
EXEC gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
IF(error in same one)
BEGIN
do same thing
END
Thanks to helpOn Sun, 13 Nov 2005 21:45:21 -0200, ReTF wrote:
(snip)
>But I need 3 IF @.@.ERROR <> 0..., i would like know if have way to check al
l
>in one time.
>like this:
>EXEC gerar_dnum_sp @.dnum OUTPUT
>EXEC gerar_auto_sp @.auto OUTPUT
>EXEC gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
>IF(error in same one)
>BEGIN
>do same thing
>END
>Thanks to help
Hi ReTF,
You'll have to do SOME work in between the calls, even though you don't
need to cpopy the complete error handling three times.
Most simple:
DECLARE @.err1 int, @.err2 int, @.err3 int
EXEC gerar_dnum_sp @.dnum OUTPUT
SET @.err1 = @.@.ERROR
EXEC gerar_auto_sp @.auto OUTPUT
SET @.err2 = @.@.ERROR
EXEC gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
SET @.err3 = @.@.ERROR
IF @.err1 <> 0 OR @.err2 <> 0 OR @.err3 <> 0
BEGIN
...
END
Or, better yet because you mostly don't want to keep on executing code
once an error is found:
DECLARE @.err int
EXEC gerar_dnum_sp @.dnum OUTPUT
SET @.err = @.@.ERROR
IF @.err = 0
BEGIN
EXEC gerar_auto_sp @.auto OUTPUT
SET @.err = @.@.ERROR
END
IF @.err = 0
BEGIN
EXEC gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
SET @.err = @.@.ERROR
END
IF @.err <> 0
BEGIN
...
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello,
You may use the return status:
DECLARE @.retstat1 int, @.retstat2 int, @.retstat3 int
DECLARE @.dnum int, @.auto int, @.aute int
EXEC @.retstat1=gerar_dnum_sp @.dnum OUTPUT
EXEC @.retstat2=gerar_auto_sp @.auto OUTPUT
EXEC @.retstat3=gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
if @.retstat1<>0 or @.retstat2<>0 or @.retstat3<>0
begin
print 'error'
end
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I did see
BEGIN TRY ...
I think that this is good to do what I need.
See:
BEGIN TRY
EXEC gerar_dnum_sp @.dnum OUTPUT
EXEC gerar_auto_sp @.auto OUTPUT
EXEC _aute_sp @.dnum, @.auto, @.aute OUTPUTTRY
--and more...
END TRY
BEGIN CATCH
-- if error do samething here
END CATH
What you think?
Thanks
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:8q12SPP6FHA.2536@.TK2MSFTNGXA02.phx.gbl...
> Hello,
> You may use the return status:
> DECLARE @.retstat1 int, @.retstat2 int, @.retstat3 int
> DECLARE @.dnum int, @.auto int, @.aute int
> EXEC @.retstat1=gerar_dnum_sp @.dnum OUTPUT
> EXEC @.retstat2=gerar_auto_sp @.auto OUTPUT
> EXEC @.retstat3=gerar_aute_sp @.dnum, @.auto, @.aute OUTPUT
> if @.retstat1<>0 or @.retstat2<>0 or @.retstat3<>0
> begin
> print 'error'
> end
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||TRY ... CATCH is a good way of doing error handling if you have SQL 2005.
Otherwise, (sql7/2000)
You should put an @.@.ERROR catch after every failable statement within the
stored proc, if the @.@.ERROR return it to the caller.
Checking @.@.ERROR after the SP wont guarentee you'll catch the error (as it
may have run other commands since the error and reset it back to 0)
EXEC @.re1 = gerar_dnum_sp @.dnum OUTPUT
EXEC @.re2 = gerar_auto_sp @.auto OUTPUT
EXEC @.re3 = gerar_aute_sp @.dnum, @.auto, @.aute OUTPUTTRY
IF ( @.re1 != 0 OR @.re2 != 0 OR @.re3 != 0 )
BEGIN
PRINT '...'
END
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:eb90wBU6FHA.1148@.tk2msftngp13.phx.gbl...
> Hi,
> I did see
> BEGIN TRY ...
> I think that this is good to do what I need.
> See:
> BEGIN TRY
> EXEC gerar_dnum_sp @.dnum OUTPUT
> EXEC gerar_auto_sp @.auto OUTPUT
> EXEC _aute_sp @.dnum, @.auto, @.aute OUTPUTTRY
> --and more...
> END TRY
> BEGIN CATCH
> -- if error do samething here
> END CATH
>
> What you think?
>
> Thanks
> "Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
> news:8q12SPP6FHA.2536@.TK2MSFTNGXA02.phx.gbl...
>
CHECK DB
Hi
When i execute dbcc checkdb in the database i am getting the error message "CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object."
Detail of the error messages are
Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
How to repair the Database .Plz help me
Thanks
Aravind
Thanks
Aravind
Aravind,
Refer DBCC CHECKDB in BooksOnLine.It lists options where you can repair the
data.It says:
" DBCC CHECKDB is the safest repair statement because it identifies and
repairs the widest possible errors. If only allocation errors are reported
for a database, execute DBCC CHECKALLOC with a repair option to repair these
errors. However, to ensure that all errors, including allocation errors, are
properly repaired, execute DBCC CHECKDB with a repair option rather than
DBCC CHECKALLOC with a repair option. "
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind
|||Aravind
Have you tried to use REPAIR option which is given by Microsoft?
DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind
|||Make sure you have the updated books online, as it contains specific instructions on how to deal with
different DBCC errors. Your error number seems to be 8906. Here's an online link to the specific suggestions
for that error:
http://msdn.microsoft.com/library/de...err_2_6eye.asp
And here's a generic link on handling database corruption:
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error message "CHECKDB found 2 allocation
errors and 0 consistency errors not associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any
IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any
IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind
When i execute dbcc checkdb in the database i am getting the error message "CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object."
Detail of the error messages are
Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
How to repair the Database .Plz help me
Thanks
Aravind
Thanks
Aravind
Aravind,
Refer DBCC CHECKDB in BooksOnLine.It lists options where you can repair the
data.It says:
" DBCC CHECKDB is the safest repair statement because it identifies and
repairs the widest possible errors. If only allocation errors are reported
for a database, execute DBCC CHECKALLOC with a repair option to repair these
errors. However, to ensure that all errors, including allocation errors, are
properly repaired, execute DBCC CHECKDB with a repair option rather than
DBCC CHECKALLOC with a repair option. "
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind
|||Aravind
Have you tried to use REPAIR option which is given by Microsoft?
DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind
|||Make sure you have the updated books online, as it contains specific instructions on how to deal with
different DBCC errors. Your error number seems to be 8906. Here's an online link to the specific suggestions
for that error:
http://msdn.microsoft.com/library/de...err_2_6eye.asp
And here's a generic link on handling database corruption:
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error message "CHECKDB found 2 allocation
errors and 0 consistency errors not associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any
IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any
IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind
CHECK DB
H
When i execute dbcc checkdb in the database i am getting the error message "CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object."
Detail of the error messages ar
Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'
Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'
How to repair the Database .Plz help m
Thank
Aravin
Thank
AravindAravind,
Refer DBCC CHECKDB in BooksOnLine.It lists options where you can repair the
data.It says:
" DBCC CHECKDB is the safest repair statement because it identifies and
repairs the widest possible errors. If only allocation errors are reported
for a database, execute DBCC CHECKALLOC with a repair option to repair these
errors. However, to ensure that all errors, including allocation errors, are
properly repaired, execute DBCC CHECKDB with a repair option rather than
DBCC CHECKALLOC with a repair option. "
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind|||Aravind
Have you tried to use REPAIR option which is given by Microsoft?
DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind|||Make sure you have the updated books online, as it contains specific instructions on how to deal with
different DBCC errors. Your error number seems to be 8906. Here's an online link to the specific suggestions
for that error:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6eye.asp
And here's a generic link on handling database corruption:
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error message "CHECKDB found 2 allocation
errors and 0 consistency errors not associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any
IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any
IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind
When i execute dbcc checkdb in the database i am getting the error message "CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object."
Detail of the error messages ar
Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'
Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'
How to repair the Database .Plz help m
Thank
Aravin
Thank
AravindAravind,
Refer DBCC CHECKDB in BooksOnLine.It lists options where you can repair the
data.It says:
" DBCC CHECKDB is the safest repair statement because it identifies and
repairs the widest possible errors. If only allocation errors are reported
for a database, execute DBCC CHECKALLOC with a repair option to repair these
errors. However, to ensure that all errors, including allocation errors, are
properly repaired, execute DBCC CHECKDB with a repair option rather than
DBCC CHECKALLOC with a repair option. "
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind|||Aravind
Have you tried to use REPAIR option which is given by Microsoft?
DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind|||Make sure you have the updated books online, as it contains specific instructions on how to deal with
different DBCC errors. Your error number seems to be 8906. Here's an online link to the specific suggestions
for that error:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6eye.asp
And here's a generic link on handling database corruption:
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error message "CHECKDB found 2 allocation
errors and 0 consistency errors not associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any
IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any
IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind
CHECK DB
Hi
When i execute dbcc checkdb in the database i am getting the error messag
e "CHECKDB found 2 allocation errors and 0 consistency errors not associated
with any single object."
Detail of the error messages are
Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1)
, but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_F
ULL'.
Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1)
, but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
How to repair the Database .Plz help me
Thanks
Aravind
Thanks
AravindAravind,
Refer DBCC CHECKDB in BooksOnLine.It lists options where you can repair the
data.It says:
" DBCC CHECKDB is the safest repair statement because it identifies and
repairs the widest possible errors. If only allocation errors are reported
for a database, execute DBCC CHECKALLOC with a repair option to repair these
errors. However, to ensure that all errors, including allocation errors, are
properly repaired, execute DBCC CHECKDB with a repair option rather than
DBCC CHECKALLOC with a repair option. "
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind|||Aravind
Have you tried to use REPAIR option which is given by Microsoft?
DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind|||Make sure you have the updated books online, as it contains specific instruc
tions on how to deal with
different DBCC errors. Your error number seems to be 8906. Here's an online
link to the specific suggestions
for that error:
serr_2_6eye.asp" target="_blank">http://msdn.microsoft.com/library/d...serr_2_6eye.asp
And here's a generic link on handling database corruption:
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error message "CHE
CKDB found 2 allocation
errors and 0 consistency errors not associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but
was not allocated in any
IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but
was not allocated in any
IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind
When i execute dbcc checkdb in the database i am getting the error messag
e "CHECKDB found 2 allocation errors and 0 consistency errors not associated
with any single object."
Detail of the error messages are
Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1)
, but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_F
ULL'.
Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1)
, but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
How to repair the Database .Plz help me
Thanks
Aravind
Thanks
AravindAravind,
Refer DBCC CHECKDB in BooksOnLine.It lists options where you can repair the
data.It says:
" DBCC CHECKDB is the safest repair statement because it identifies and
repairs the widest possible errors. If only allocation errors are reported
for a database, execute DBCC CHECKALLOC with a repair option to repair these
errors. However, to ensure that all errors, including allocation errors, are
properly repaired, execute DBCC CHECKDB with a repair option rather than
DBCC CHECKALLOC with a repair option. "
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind|||Aravind
Have you tried to use REPAIR option which is given by Microsoft?
DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind|||Make sure you have the updated books online, as it contains specific instruc
tions on how to deal with
different DBCC errors. Your error number seems to be 8906. Here's an online
link to the specific suggestions
for that error:
serr_2_6eye.asp" target="_blank">http://msdn.microsoft.com/library/d...serr_2_6eye.asp
And here's a generic link on handling database corruption:
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error message "CHE
CKDB found 2 allocation
errors and 0 consistency errors not associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but
was not allocated in any
IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but
was not allocated in any
IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind
Sunday, March 11, 2012
check database integrity
Folks, do you know some way or tool to check the database integrity and
repair error like corrupt entries to a d/b in sql 2k?
Thanks
DanielDBCC = Database consistency checker. Have a look in SQL BOL
Try DBCC CHECKDB to start
--
HTH
Ryan Waight, MCDBA, MCSE
"Daniel_A" <dabella@.empower.edu.uy> wrote in message
news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> Folks, do you know some way or tool to check the database integrity and
> repair error like corrupt entries to a d/b in sql 2k?
> Thanks
> Daniel
>
>|||Ryan,
DBCC is an abbreviation for Database Console Command.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> DBCC = Database consistency checker. Have a look in SQL BOL
> Try DBCC CHECKDB to start
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > Folks, do you know some way or tool to check the database integrity and
> > repair error like corrupt entries to a d/b in sql 2k?
> >
> > Thanks
> >
> > Daniel
> >
> >
> >
>|||If i'm wrong i stand corrected. Can't find a translation on Microsoft but...
I got the following from the Sybase site...
What Is the Database Consistency Checker?
The database consistency checker (dbcc) provides commands for checking the
logical and physical consistency of a database. Two major functions of dbcc
are:
http://manuals.sybase.com/onlinebooks/group-as/asg1200e/asesag/@.ebt-link;pt=57217?target=%25N%15_57285_START_RESTART_N%25
HTH
Ryan Waight, MCDBA, MCSE
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23%23QpfeSfDHA.3896@.tk2msftngp13.phx.gbl...
> Ryan,
> DBCC is an abbreviation for Database Console Command.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > DBCC = Database consistency checker. Have a look in SQL BOL
> >
> > Try DBCC CHECKDB to start
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> > news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > > Folks, do you know some way or tool to check the database integrity
and
> > > repair error like corrupt entries to a d/b in sql 2k?
> > >
> > > Thanks
> > >
> > > Daniel
> > >
> > >
> > >
> >
> >
>|||Ryan,
Its mentioned in BooksOnLine:
'DBCC'
http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_217n.asp?frame=true
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%234aktjSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> If i'm wrong i stand corrected. Can't find a translation on Microsoft
but...
> I got the following from the Sybase site...
> What Is the Database Consistency Checker?
> The database consistency checker (dbcc) provides commands for checking the
> logical and physical consistency of a database. Two major functions of
dbcc
> are:
>
http://manuals.sybase.com/onlinebooks/group-as/asg1200e/asesag/@.ebt-link;pt=57217?target=%25N%15_57285_START_RESTART_N%25
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:%23%23QpfeSfDHA.3896@.tk2msftngp13.phx.gbl...
> > Ryan,
> >
> > DBCC is an abbreviation for Database Console Command.
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > > DBCC = Database consistency checker. Have a look in SQL BOL
> > >
> > > Try DBCC CHECKDB to start
> > >
> > > --
> > > HTH
> > > Ryan Waight, MCDBA, MCSE
> > >
> > > "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> > > news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > > > Folks, do you know some way or tool to check the database integrity
> and
> > > > repair error like corrupt entries to a d/b in sql 2k?
> > > >
> > > > Thanks
> > > >
> > > > Daniel
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks, have a look at a response to Event ID 17055 by Scott Morris. It's
posted under the wrong subject but is about DBCC.
In his version of BOL searching for DBCC (T-SQL) returns Database
Consistency Checker
In my version it states Database Console Command
The acronym appears to have changed meaning between version.
From now on I'll be using Database Console Command
--
HTH
Ryan Waight, MCDBA, MCSE
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:eH1lmpSfDHA.3700@.TK2MSFTNGP11.phx.gbl...
> Ryan,
> Its mentioned in BooksOnLine:
> 'DBCC'
>
http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_217n.asp?frame=true
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%234aktjSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > If i'm wrong i stand corrected. Can't find a translation on Microsoft
> but...
> >
> > I got the following from the Sybase site...
> > What Is the Database Consistency Checker?
> > The database consistency checker (dbcc) provides commands for checking
the
> > logical and physical consistency of a database. Two major functions of
> dbcc
> > are:
> >
> >
>
http://manuals.sybase.com/onlinebooks/group-as/asg1200e/asesag/@.ebt-link;pt=57217?target=%25N%15_57285_START_RESTART_N%25
> >
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > news:%23%23QpfeSfDHA.3896@.tk2msftngp13.phx.gbl...
> > > Ryan,
> > >
> > > DBCC is an abbreviation for Database Console Command.
> > >
> > > --
> > > Dinesh.
> > > SQL Server FAQ at
> > > http://www.tkdinesh.com
> > >
> > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > > news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > > > DBCC = Database consistency checker. Have a look in SQL BOL
> > > >
> > > > Try DBCC CHECKDB to start
> > > >
> > > > --
> > > > HTH
> > > > Ryan Waight, MCDBA, MCSE
> > > >
> > > > "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> > > > news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > > > > Folks, do you know some way or tool to check the database
integrity
> > and
> > > > > repair error like corrupt entries to a d/b in sql 2k?
> > > > >
> > > > > Thanks
> > > > >
> > > > > Daniel
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Historically, DBCC was the acronym for DataBase Consistency Checker, and had
the same options as in Sybase.
The basic options have to do with checking the consistency of a table or the
whole database, and verifying the allocation structures. In later releases,
the DBCC command began to be overloaded, and everytime the dev engineers at
Microsoft wanted to add a new non-SQL command, they just tacked it on to
DBCC, so if you check the Books Online you'll see lots of options that have
nothing to do with consistency checking.
For this reason the BOL nows says the acronym means DataBase Console
Command, but that is a case of putting the cart before the horse. We already
had the command, and they just had to find something new for it to mean.
And actually, there is still a reference in BOL for DBCC having the old
meaning. If you look in the System Monitor section, under the description of
SQL Server: Database Object, it will tell you that the "DBCC Logical Scan
Bytes/sec" counter measures
"Number of logical read scan bytes per second for database consistency
checker (DBCC) statements."
So Daniel, DBCC is what you want, especially the CHECKDB and CHECKALLOC
options, but there might be a few others that are useful. Please read the
docs.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:##QpfeSfDHA.3896@.tk2msftngp13.phx.gbl...
> Ryan,
> DBCC is an abbreviation for Database Console Command.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > DBCC = Database consistency checker. Have a look in SQL BOL
> >
> > Try DBCC CHECKDB to start
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> > news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > > Folks, do you know some way or tool to check the database integrity
and
> > > repair error like corrupt entries to a d/b in sql 2k?
> > >
> > > Thanks
> > >
> > > Daniel
> > >
> > >
> > >
> >
> >
>
repair error like corrupt entries to a d/b in sql 2k?
Thanks
DanielDBCC = Database consistency checker. Have a look in SQL BOL
Try DBCC CHECKDB to start
--
HTH
Ryan Waight, MCDBA, MCSE
"Daniel_A" <dabella@.empower.edu.uy> wrote in message
news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> Folks, do you know some way or tool to check the database integrity and
> repair error like corrupt entries to a d/b in sql 2k?
> Thanks
> Daniel
>
>|||Ryan,
DBCC is an abbreviation for Database Console Command.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> DBCC = Database consistency checker. Have a look in SQL BOL
> Try DBCC CHECKDB to start
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > Folks, do you know some way or tool to check the database integrity and
> > repair error like corrupt entries to a d/b in sql 2k?
> >
> > Thanks
> >
> > Daniel
> >
> >
> >
>|||If i'm wrong i stand corrected. Can't find a translation on Microsoft but...
I got the following from the Sybase site...
What Is the Database Consistency Checker?
The database consistency checker (dbcc) provides commands for checking the
logical and physical consistency of a database. Two major functions of dbcc
are:
http://manuals.sybase.com/onlinebooks/group-as/asg1200e/asesag/@.ebt-link;pt=57217?target=%25N%15_57285_START_RESTART_N%25
HTH
Ryan Waight, MCDBA, MCSE
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23%23QpfeSfDHA.3896@.tk2msftngp13.phx.gbl...
> Ryan,
> DBCC is an abbreviation for Database Console Command.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > DBCC = Database consistency checker. Have a look in SQL BOL
> >
> > Try DBCC CHECKDB to start
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> > news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > > Folks, do you know some way or tool to check the database integrity
and
> > > repair error like corrupt entries to a d/b in sql 2k?
> > >
> > > Thanks
> > >
> > > Daniel
> > >
> > >
> > >
> >
> >
>|||Ryan,
Its mentioned in BooksOnLine:
'DBCC'
http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_217n.asp?frame=true
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%234aktjSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> If i'm wrong i stand corrected. Can't find a translation on Microsoft
but...
> I got the following from the Sybase site...
> What Is the Database Consistency Checker?
> The database consistency checker (dbcc) provides commands for checking the
> logical and physical consistency of a database. Two major functions of
dbcc
> are:
>
http://manuals.sybase.com/onlinebooks/group-as/asg1200e/asesag/@.ebt-link;pt=57217?target=%25N%15_57285_START_RESTART_N%25
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:%23%23QpfeSfDHA.3896@.tk2msftngp13.phx.gbl...
> > Ryan,
> >
> > DBCC is an abbreviation for Database Console Command.
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > > DBCC = Database consistency checker. Have a look in SQL BOL
> > >
> > > Try DBCC CHECKDB to start
> > >
> > > --
> > > HTH
> > > Ryan Waight, MCDBA, MCSE
> > >
> > > "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> > > news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > > > Folks, do you know some way or tool to check the database integrity
> and
> > > > repair error like corrupt entries to a d/b in sql 2k?
> > > >
> > > > Thanks
> > > >
> > > > Daniel
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks, have a look at a response to Event ID 17055 by Scott Morris. It's
posted under the wrong subject but is about DBCC.
In his version of BOL searching for DBCC (T-SQL) returns Database
Consistency Checker
In my version it states Database Console Command
The acronym appears to have changed meaning between version.
From now on I'll be using Database Console Command
--
HTH
Ryan Waight, MCDBA, MCSE
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:eH1lmpSfDHA.3700@.TK2MSFTNGP11.phx.gbl...
> Ryan,
> Its mentioned in BooksOnLine:
> 'DBCC'
>
http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_217n.asp?frame=true
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%234aktjSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > If i'm wrong i stand corrected. Can't find a translation on Microsoft
> but...
> >
> > I got the following from the Sybase site...
> > What Is the Database Consistency Checker?
> > The database consistency checker (dbcc) provides commands for checking
the
> > logical and physical consistency of a database. Two major functions of
> dbcc
> > are:
> >
> >
>
http://manuals.sybase.com/onlinebooks/group-as/asg1200e/asesag/@.ebt-link;pt=57217?target=%25N%15_57285_START_RESTART_N%25
> >
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > news:%23%23QpfeSfDHA.3896@.tk2msftngp13.phx.gbl...
> > > Ryan,
> > >
> > > DBCC is an abbreviation for Database Console Command.
> > >
> > > --
> > > Dinesh.
> > > SQL Server FAQ at
> > > http://www.tkdinesh.com
> > >
> > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > > news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > > > DBCC = Database consistency checker. Have a look in SQL BOL
> > > >
> > > > Try DBCC CHECKDB to start
> > > >
> > > > --
> > > > HTH
> > > > Ryan Waight, MCDBA, MCSE
> > > >
> > > > "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> > > > news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > > > > Folks, do you know some way or tool to check the database
integrity
> > and
> > > > > repair error like corrupt entries to a d/b in sql 2k?
> > > > >
> > > > > Thanks
> > > > >
> > > > > Daniel
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Historically, DBCC was the acronym for DataBase Consistency Checker, and had
the same options as in Sybase.
The basic options have to do with checking the consistency of a table or the
whole database, and verifying the allocation structures. In later releases,
the DBCC command began to be overloaded, and everytime the dev engineers at
Microsoft wanted to add a new non-SQL command, they just tacked it on to
DBCC, so if you check the Books Online you'll see lots of options that have
nothing to do with consistency checking.
For this reason the BOL nows says the acronym means DataBase Console
Command, but that is a case of putting the cart before the horse. We already
had the command, and they just had to find something new for it to mean.
And actually, there is still a reference in BOL for DBCC having the old
meaning. If you look in the System Monitor section, under the description of
SQL Server: Database Object, it will tell you that the "DBCC Logical Scan
Bytes/sec" counter measures
"Number of logical read scan bytes per second for database consistency
checker (DBCC) statements."
So Daniel, DBCC is what you want, especially the CHECKDB and CHECKALLOC
options, but there might be a few others that are useful. Please read the
docs.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:##QpfeSfDHA.3896@.tk2msftngp13.phx.gbl...
> Ryan,
> DBCC is an abbreviation for Database Console Command.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > DBCC = Database consistency checker. Have a look in SQL BOL
> >
> > Try DBCC CHECKDB to start
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> > news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > > Folks, do you know some way or tool to check the database integrity
and
> > > repair error like corrupt entries to a d/b in sql 2k?
> > >
> > > Thanks
> > >
> > > Daniel
> > >
> > >
> > >
> >
> >
>
Check Constraint Violation
I am getting a check constraint error on the following query.
INSERT TABLEA
( COL1, COL2, COL3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
returning 20000. All columns are of datatype INT as is the return value of
the function.
If i replace the function call with a literal 0, it works, but having the
function in there violates the constraint on COL3, despite the value still
being 0.
Does anyone have any idea why this is happening? I have tried making COL3 a
computed column, but can't have a constraint on a computed column, tried
making the select statement into a derived table and selecting from that int
o
my insert statement.
This is being done on SQL Server 2000 Enterprise.
Thank you
Clint ColefaxHi
What is the error that you received.
and what was displayed when you tried:
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:
> I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that i
nto
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||The error received was a violation of check constraint.
INSERT statement conflicted with COLUMN CHECK constraint...
Execute that select statement returns as expected
20000, -20000, 0
Thank you
Clint Colefax|||Hi
Can you try as
INSERT INTO TABLEA
SELECT dbo.Function1(),-20000, dbo.Function1() + (-20000)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:
> The error received was a violation of check constraint.
> INSERT statement conflicted with COLUMN CHECK constraint...
> Execute that select statement returns as expected
> 20000, -20000, 0
> Thank you
> Clint Colefax
>|||What data types are the columns and the function?
If they are not of an exact type, but e.g. "real", the third expression may
evaluate to a value slightly below 0, although it is displayed as 0. CAST to
integer to avoid this problem.
I hope this helps!
Martin
"Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
news:42D1D89D-9EA0-4F00-8F91-D8D55732C387@.microsoft.com...
>I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
> a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that
> into
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||On Mon, 30 May 2005 19:19:41 -0700, Clint Colefax wrote:
>I am getting a check constraint error on the following query.
>INSERT TABLEA
>( COL1, COL2, COL3)
>SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
>There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
>returning 20000. All columns are of datatype INT as is the return value of
>the function.
Hi Clint,
I could not reproduce this behaviour (see repro script below). Could you
post a repro script for me to run and reproduce the error?
>I have tried making COL3 a
>computed column, but can't have a constraint on a computed column,
If Col3 is always equal to Col1 - Col2, you should make it a computed
column. You can replace the check constraint wiuth the following
equivalent:
CHECK (Col1 >= Col2)
Here's the script I used to try to reproduce your problem, and the
output I got from it:
create table TableA
(Col1 int not null,
Col2 int not null,
Col3 int not null,
PRIMARY KEY (Col1),
CHECK (Col3 >= 0)
)
go
create function dbo.Function1()
returns int
as
begin
return 20000
end
go
INSERT TableA
( Col1, Col2, Col3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
go
select * from TableA
go
drop function dbo.Function1
go
drop table TableA
go
Col1 Col2 Col3
-- -- --
20000 -20000 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I gave the earlier example as I didn't want to post all of the infrastructur
e
around this problem. With the following cut down version, I am still able to
replicate the problem.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST
Thank you
Clint Colefax|||Sorry, I didn't think out my example very well, the following is code that
should reproduce the error.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST|||Thank you but all datatype are of INT, all match, even using a CAST or
CONVERT statement does not get around the problem (had previouslty attempted
).
Thank you for your input
Clint Colefax|||It's a bug, and a surprising one:
CREATE TABLE TEST (
a int not null,
b smalldatetime not null,
constraint finalv CHECK (a >= 0)
)
go
INSERT TEST(a) SELECT 1
Gives this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.TEST';
column does not allow nulls. INSERT fails.
The statement has been terminated.
Can you post the result of
SELECT @.@.version
so we can see what version you're running?
I verified this on 8.00.2039, and will report it to Microsoft.
SK
Clint Colefax wrote:
>there error is a check constraint violation for the FINAL_VALUE constraint.
>Server: Msg 547, Level 16, State 1, Line 1
>INSERT statement conflicted with COLUMN CHECK constraint
>'CK__test__FINAL_VALU__3AA27A0F'. The conflict occurred in database
>'LIPSDev', table 'test', column 'FINAL_VALUE'.
>The statement has been terminated.
>Thank you
>
INSERT TABLEA
( COL1, COL2, COL3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
returning 20000. All columns are of datatype INT as is the return value of
the function.
If i replace the function call with a literal 0, it works, but having the
function in there violates the constraint on COL3, despite the value still
being 0.
Does anyone have any idea why this is happening? I have tried making COL3 a
computed column, but can't have a constraint on a computed column, tried
making the select statement into a derived table and selecting from that int
o
my insert statement.
This is being done on SQL Server 2000 Enterprise.
Thank you
Clint ColefaxHi
What is the error that you received.
and what was displayed when you tried:
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:
> I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that i
nto
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||The error received was a violation of check constraint.
INSERT statement conflicted with COLUMN CHECK constraint...
Execute that select statement returns as expected
20000, -20000, 0
Thank you
Clint Colefax|||Hi
Can you try as
INSERT INTO TABLEA
SELECT dbo.Function1(),-20000, dbo.Function1() + (-20000)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:
> The error received was a violation of check constraint.
> INSERT statement conflicted with COLUMN CHECK constraint...
> Execute that select statement returns as expected
> 20000, -20000, 0
> Thank you
> Clint Colefax
>|||What data types are the columns and the function?
If they are not of an exact type, but e.g. "real", the third expression may
evaluate to a value slightly below 0, although it is displayed as 0. CAST to
integer to avoid this problem.
I hope this helps!
Martin
"Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
news:42D1D89D-9EA0-4F00-8F91-D8D55732C387@.microsoft.com...
>I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
> a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that
> into
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||On Mon, 30 May 2005 19:19:41 -0700, Clint Colefax wrote:
>I am getting a check constraint error on the following query.
>INSERT TABLEA
>( COL1, COL2, COL3)
>SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
>There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
>returning 20000. All columns are of datatype INT as is the return value of
>the function.
Hi Clint,
I could not reproduce this behaviour (see repro script below). Could you
post a repro script for me to run and reproduce the error?
>I have tried making COL3 a
>computed column, but can't have a constraint on a computed column,
If Col3 is always equal to Col1 - Col2, you should make it a computed
column. You can replace the check constraint wiuth the following
equivalent:
CHECK (Col1 >= Col2)
Here's the script I used to try to reproduce your problem, and the
output I got from it:
create table TableA
(Col1 int not null,
Col2 int not null,
Col3 int not null,
PRIMARY KEY (Col1),
CHECK (Col3 >= 0)
)
go
create function dbo.Function1()
returns int
as
begin
return 20000
end
go
INSERT TableA
( Col1, Col2, Col3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
go
select * from TableA
go
drop function dbo.Function1
go
drop table TableA
go
Col1 Col2 Col3
-- -- --
20000 -20000 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I gave the earlier example as I didn't want to post all of the infrastructur
e
around this problem. With the following cut down version, I am still able to
replicate the problem.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST
Thank you
Clint Colefax|||Sorry, I didn't think out my example very well, the following is code that
should reproduce the error.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST|||Thank you but all datatype are of INT, all match, even using a CAST or
CONVERT statement does not get around the problem (had previouslty attempted
).
Thank you for your input
Clint Colefax|||It's a bug, and a surprising one:
CREATE TABLE TEST (
a int not null,
b smalldatetime not null,
constraint finalv CHECK (a >= 0)
)
go
INSERT TEST(a) SELECT 1
Gives this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.TEST';
column does not allow nulls. INSERT fails.
The statement has been terminated.
Can you post the result of
SELECT @.@.version
so we can see what version you're running?
I verified this on 8.00.2039, and will report it to Microsoft.
SK
Clint Colefax wrote:
>there error is a check constraint violation for the FINAL_VALUE constraint.
>Server: Msg 547, Level 16, State 1, Line 1
>INSERT statement conflicted with COLUMN CHECK constraint
>'CK__test__FINAL_VALU__3AA27A0F'. The conflict occurred in database
>'LIPSDev', table 'test', column 'FINAL_VALUE'.
>The statement has been terminated.
>Thank you
>
Thursday, March 8, 2012
Check constraint and foreign key error
The error message for err. 547 is:
"%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
This error is raised when a foreign key error is occured, and when a check
constraint error occured, too.
What kind of values can have the parameters of this error message? How can I
know, what kind of error is this?
thanks
-enci-Unfortunately there's no system variable that gives you the actual error
message, so that you could parse it for more information. But from a client
application, you should be able to access the error message. For example,
Err.Description in ADO.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Eniko Tegen" <EnikoTegen@.discussions.microsoft.com> wrote in message
news:04C8D5D7-D273-4D68-9757-31DF9ED09867@.microsoft.com...
The error message for err. 547 is:
"%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
This error is raised when a foreign key error is occured, and when a check
constraint error occured, too.
What kind of values can have the parameters of this error message? How can I
know, what kind of error is this?
thanks
-enci-|||I have the error message, but I want to format and translate it. And I didn'
t
know what kind of values can have the messages parameters. I have diferent
messages and todo's in case of check constraint and diferent in case of
foreign keys. And I didn't know what kind of any errors can appear with this
error number.
thanks
-enci-
"Narayana Vyas Kondreddi" wrote:
> Unfortunately there's no system variable that gives you the actual error
> message, so that you could parse it for more information. But from a clien
t
> application, you should be able to access the error message. For example,
> Err.Description in ADO.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Eniko Tegen" <EnikoTegen@.discussions.microsoft.com> wrote in message
> news:04C8D5D7-D273-4D68-9757-31DF9ED09867@.microsoft.com...
> The error message for err. 547 is:
> "%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
> occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
> This error is raised when a foreign key error is occured, and when a check
> constraint error occured, too.
> What kind of values can have the parameters of this error message? How can
I
> know, what kind of error is this?
> thanks
> -enci-
>
>
"%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
This error is raised when a foreign key error is occured, and when a check
constraint error occured, too.
What kind of values can have the parameters of this error message? How can I
know, what kind of error is this?
thanks
-enci-Unfortunately there's no system variable that gives you the actual error
message, so that you could parse it for more information. But from a client
application, you should be able to access the error message. For example,
Err.Description in ADO.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Eniko Tegen" <EnikoTegen@.discussions.microsoft.com> wrote in message
news:04C8D5D7-D273-4D68-9757-31DF9ED09867@.microsoft.com...
The error message for err. 547 is:
"%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
This error is raised when a foreign key error is occured, and when a check
constraint error occured, too.
What kind of values can have the parameters of this error message? How can I
know, what kind of error is this?
thanks
-enci-|||I have the error message, but I want to format and translate it. And I didn'
t
know what kind of values can have the messages parameters. I have diferent
messages and todo's in case of check constraint and diferent in case of
foreign keys. And I didn't know what kind of any errors can appear with this
error number.
thanks
-enci-
"Narayana Vyas Kondreddi" wrote:
> Unfortunately there's no system variable that gives you the actual error
> message, so that you could parse it for more information. But from a clien
t
> application, you should be able to access the error message. For example,
> Err.Description in ADO.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Eniko Tegen" <EnikoTegen@.discussions.microsoft.com> wrote in message
> news:04C8D5D7-D273-4D68-9757-31DF9ED09867@.microsoft.com...
> The error message for err. 547 is:
> "%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
> occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
> This error is raised when a foreign key error is occured, and when a check
> constraint error occured, too.
> What kind of values can have the parameters of this error message? How can
I
> know, what kind of error is this?
> thanks
> -enci-
>
>
Sunday, February 19, 2012
Chart expression error
I have a pretty simple chart which seems to work, but when I build it, I get
a warning that "the expression for the chart "chart1" contains an error:
operation is not valid due to the current state of the object". I've
experimented with a bunch of things to get rid of the warning,
unsuccessfully. May I have some suggestions for how to figure out exactly
what the warning is referencing?
--
Bob RogersBob, can you post a minimal RDL running against e.g. Northwind which
reproduces the issue. This would help in narrowing down and finding a
solution.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Rogers" <BobRogers@.discussions.microsoft.com> wrote in message
news:11DF0CF7-5EE9-415E-934B-7F407604AFD2@.microsoft.com...
> I have a pretty simple chart which seems to work, but when I build it, I
get
> a warning that "the expression for the chart "chart1" contains an error:
> operation is not valid due to the current state of the object". I've
> experimented with a bunch of things to get rid of the warning,
> unsuccessfully. May I have some suggestions for how to figure out exactly
> what the warning is referencing?
> --
> Bob Rogers|||I did finally discover the problem: an error in an expression controlling the
"data label" within "point labels" accessed through the Data tab of a chart
property. There was no misbehavior apparent, so I just had to plug through
all the specifications. That was my first chart. Next time, I will pay
close attention to the appearance of a warning, while I still have some idea
what I had just done that might have caused it. I was hoping you would say
"turn on x debug option and read a stack" or something like that. Anyway,
thanks for the attempt.
"Robert Bruckner [MSFT]" wrote:
> Bob, can you post a minimal RDL running against e.g. Northwind which
> reproduces the issue. This would help in narrowing down and finding a
> solution.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Bob Rogers" <BobRogers@.discussions.microsoft.com> wrote in message
> news:11DF0CF7-5EE9-415E-934B-7F407604AFD2@.microsoft.com...
> > I have a pretty simple chart which seems to work, but when I build it, I
> get
> > a warning that "the expression for the chart "chart1" contains an error:
> > operation is not valid due to the current state of the object". I've
> > experimented with a bunch of things to get rid of the warning,
> > unsuccessfully. May I have some suggestions for how to figure out exactly
> > what the warning is referencing?
> > --
> > Bob Rogers
>
>
a warning that "the expression for the chart "chart1" contains an error:
operation is not valid due to the current state of the object". I've
experimented with a bunch of things to get rid of the warning,
unsuccessfully. May I have some suggestions for how to figure out exactly
what the warning is referencing?
--
Bob RogersBob, can you post a minimal RDL running against e.g. Northwind which
reproduces the issue. This would help in narrowing down and finding a
solution.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Rogers" <BobRogers@.discussions.microsoft.com> wrote in message
news:11DF0CF7-5EE9-415E-934B-7F407604AFD2@.microsoft.com...
> I have a pretty simple chart which seems to work, but when I build it, I
get
> a warning that "the expression for the chart "chart1" contains an error:
> operation is not valid due to the current state of the object". I've
> experimented with a bunch of things to get rid of the warning,
> unsuccessfully. May I have some suggestions for how to figure out exactly
> what the warning is referencing?
> --
> Bob Rogers|||I did finally discover the problem: an error in an expression controlling the
"data label" within "point labels" accessed through the Data tab of a chart
property. There was no misbehavior apparent, so I just had to plug through
all the specifications. That was my first chart. Next time, I will pay
close attention to the appearance of a warning, while I still have some idea
what I had just done that might have caused it. I was hoping you would say
"turn on x debug option and read a stack" or something like that. Anyway,
thanks for the attempt.
"Robert Bruckner [MSFT]" wrote:
> Bob, can you post a minimal RDL running against e.g. Northwind which
> reproduces the issue. This would help in narrowing down and finding a
> solution.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Bob Rogers" <BobRogers@.discussions.microsoft.com> wrote in message
> news:11DF0CF7-5EE9-415E-934B-7F407604AFD2@.microsoft.com...
> > I have a pretty simple chart which seems to work, but when I build it, I
> get
> > a warning that "the expression for the chart "chart1" contains an error:
> > operation is not valid due to the current state of the object". I've
> > experimented with a bunch of things to get rid of the warning,
> > unsuccessfully. May I have some suggestions for how to figure out exactly
> > what the warning is referencing?
> > --
> > Bob Rogers
>
>
Chart error: Parameter is not valid
Hi,
I'm using Reporting Services 2005, trying to design a report in Visual
Studio 2005.
I have a chart in the report that seems to work just fine when I test it
with Preview.
However, every now and then, if I change some trivial detail in the chart
properties, such as the title font, the chart suddenly won't render in design
mode, instead displaying the following error: "Parameter is not valid."
Does anyone have any idea what might be causing this?I sometimes get the same error in charts but they are still working in
preview.
It´s really strange...
"Rolf Smeds" <Rolf Smeds@.discussions.microsoft.com> escribió en el mensaje
news:8BE6B851-1C4A-4CEC-8943-28F1F703F362@.microsoft.com...
> Hi,
> I'm using Reporting Services 2005, trying to design a report in Visual
> Studio 2005.
> I have a chart in the report that seems to work just fine when I test it
> with Preview.
> However, every now and then, if I change some trivial detail in the chart
> properties, such as the title font, the chart suddenly won't render in
> design
> mode, instead displaying the following error: "Parameter is not valid."
> Does anyone have any idea what might be causing this?
I'm using Reporting Services 2005, trying to design a report in Visual
Studio 2005.
I have a chart in the report that seems to work just fine when I test it
with Preview.
However, every now and then, if I change some trivial detail in the chart
properties, such as the title font, the chart suddenly won't render in design
mode, instead displaying the following error: "Parameter is not valid."
Does anyone have any idea what might be causing this?I sometimes get the same error in charts but they are still working in
preview.
It´s really strange...
"Rolf Smeds" <Rolf Smeds@.discussions.microsoft.com> escribió en el mensaje
news:8BE6B851-1C4A-4CEC-8943-28F1F703F362@.microsoft.com...
> Hi,
> I'm using Reporting Services 2005, trying to design a report in Visual
> Studio 2005.
> I have a chart in the report that seems to work just fine when I test it
> with Preview.
> However, every now and then, if I change some trivial detail in the chart
> properties, such as the title font, the chart suddenly won't render in
> design
> mode, instead displaying the following error: "Parameter is not valid."
> Does anyone have any idea what might be causing this?
Chart dataset error from sql sum fields
I'm receiving an,
"The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
Report item expressions can only refer to fields within the current data set
scope or, if inside an aggregate, the specified data set scope."
Error whenever I use the fields from my similar running total query
SELECT a.DayCount,
a.Sales,
SUM(b.Sales)
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Anyone run into a similar problem?Just in case anyone else has this problem
if in your select you use a Sum and it will automatically convert it to a
name like AS expr1, Change the expr to that field.
Ex:
Select Sum(Distinct Something.FieldName) As Expr1
Change Expr1 to FieldName and it should work in the graph.
Don't know why I was getting this error.
"Sean Edwards" wrote:
> I'm receiving an,
> "The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
> Report item expressions can only refer to fields within the current data set
> scope or, if inside an aggregate, the specified data set scope."
> Error whenever I use the fields from my similar running total query
> SELECT a.DayCount,
> a.Sales,
> SUM(b.Sales)
> FROM Sales a
> CROSS JOIN Sales b
> WHERE (b.DayCount <= a.DayCount) AS RunningTotal
> GROUP BY a.DayCount,a.Sales
> ORDER BY a.DayCount,a.Sales
> Anyone run into a similar problem?
"The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
Report item expressions can only refer to fields within the current data set
scope or, if inside an aggregate, the specified data set scope."
Error whenever I use the fields from my similar running total query
SELECT a.DayCount,
a.Sales,
SUM(b.Sales)
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Anyone run into a similar problem?Just in case anyone else has this problem
if in your select you use a Sum and it will automatically convert it to a
name like AS expr1, Change the expr to that field.
Ex:
Select Sum(Distinct Something.FieldName) As Expr1
Change Expr1 to FieldName and it should work in the graph.
Don't know why I was getting this error.
"Sean Edwards" wrote:
> I'm receiving an,
> "The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
> Report item expressions can only refer to fields within the current data set
> scope or, if inside an aggregate, the specified data set scope."
> Error whenever I use the fields from my similar running total query
> SELECT a.DayCount,
> a.Sales,
> SUM(b.Sales)
> FROM Sales a
> CROSS JOIN Sales b
> WHERE (b.DayCount <= a.DayCount) AS RunningTotal
> GROUP BY a.DayCount,a.Sales
> ORDER BY a.DayCount,a.Sales
> Anyone run into a similar problem?
Tuesday, February 14, 2012
Character is not valid error
Here's my code:
Dim Cmd as New SQLCommand(sqlString, conn)
cmd.CommandType=CommandType.StoredProcedure
Breaks Here -->>cmd.parameters.add(New SQLParameter(@.OrdAlias, OrdNum))
cmd.parameters.add(New SQLParameter(@.AliasSourceCode, 4))
The error says:
compilation error - -
then, on the line that is in red:
Compiler Error Message:BC30037: Character is not valid.
I have 'OrdNum' declared globally, and OrdNum is assigned right away in the Page_Load event. I've checked the spelling of the SQL parameters (OrdAlias is a varchar, and AliasSourceCode is a tinyInt)
After all this - then, this code runs
Any ideas why I'm getting this error??
Character is not valid
Hi,
I do not know what I did wrong. I tested a simple function in my custom code and get the error saying "Character is not valid". Any help is appreciated!
Here is the function inside my custom code.
Shared Function test() As string
Dim items As Fields
Return items("DataField1").Value;
End Function
I call the function in the body of my report using: Code.test()
and the error says "There is an error on line 18 of custom code: [BC30037] Character is not valid."
Thanks,
Tabbey
Try to remove " ; " after Return items("DataField1").Value|||Also, you cannot refer to dataset items inside of your Report code block. You must pass values into your function.
Something like this:
Shared Function test(fieldvalue as string) As string
test = fieldvalue
End Function
And then call the function in the body of the report:
=Code.test(ReportItems!DatasetField.Value)
|||I need to build "fields string" in my custom code depending on the parameters users selected.
If I cannot refer to the dataset items inside the custom code, can you think of any other alternative solution?
I construct the fields string in textbox expression, but I get an error if the field did not exist since IIF evaluate the entire expression.
Thanks,
Tabbey
|||Can you create a function with a signature that accepts all the fields you wish to concatenate?Function addString (field1 as string, field2 as string, field3 as string) as String
if Parameter!UserInput.Value = true then
addString = field1 & field2 & field3
End Function
You could always create a custom data processing extension and handle it there. you could also probably handle this in the logic of your stored procedure.|||
Andy,
That sounds like a good idea. Let me try that out. Thanks!
Sunday, February 12, 2012
chapter 3 page 44
I'm getting the following errow when I cut and paste script into the query design
There is an error in the query. Invalid object name 'FactResellerSales'.
Invalid object name 'DimEmployee'.
Invalid object name 'DimTime'.
Invalid object name 'DimSalesTerritory'.
I've tried some of everything to resolve this issue. Need help... Not sure what I'm doing wrong.
ThanksCheck your database connection "Data Source Properties" and give a Test connection
Friday, February 10, 2012
channel for SSL/TLS ????
My users are getting the following error
"The underlying connection was closed: Could not establish secure channel
for SSL/TLS."
any idea'?when ?
when they open a report?
or when they try to access the web server http://server/reports?
Does your reportserver is protected by a firewall?
does the user can access the http://server/reportserver web site?
"Soan" <Soan@.discussions.microsoft.com> wrote in message
news:00C1B137-5CA9-4FD3-B6AA-86E464145CA8@.microsoft.com...
> My users are getting the following error
> "The underlying connection was closed: Could not establish secure channel
> for SSL/TLS."
>
> any idea'?|||Just a guess, but something is probably out of sync in one of your *.config
files. Either an SSL setting or report server name. Check them out under
the MSRS install folders.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:OAEFqys5EHA.3416@.TK2MSFTNGP09.phx.gbl...
> when ?
> when they open a report?
> or when they try to access the web server http://server/reports?
> Does your reportserver is protected by a firewall?
> does the user can access the http://server/reportserver web site?
> "Soan" <Soan@.discussions.microsoft.com> wrote in message
> news:00C1B137-5CA9-4FD3-B6AA-86E464145CA8@.microsoft.com...
>> My users are getting the following error
>> "The underlying connection was closed: Could not establish secure channel
>> for SSL/TLS."
>>
>> any idea'?
>
"The underlying connection was closed: Could not establish secure channel
for SSL/TLS."
any idea'?when ?
when they open a report?
or when they try to access the web server http://server/reports?
Does your reportserver is protected by a firewall?
does the user can access the http://server/reportserver web site?
"Soan" <Soan@.discussions.microsoft.com> wrote in message
news:00C1B137-5CA9-4FD3-B6AA-86E464145CA8@.microsoft.com...
> My users are getting the following error
> "The underlying connection was closed: Could not establish secure channel
> for SSL/TLS."
>
> any idea'?|||Just a guess, but something is probably out of sync in one of your *.config
files. Either an SSL setting or report server name. Check them out under
the MSRS install folders.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:OAEFqys5EHA.3416@.TK2MSFTNGP09.phx.gbl...
> when ?
> when they open a report?
> or when they try to access the web server http://server/reports?
> Does your reportserver is protected by a firewall?
> does the user can access the http://server/reportserver web site?
> "Soan" <Soan@.discussions.microsoft.com> wrote in message
> news:00C1B137-5CA9-4FD3-B6AA-86E464145CA8@.microsoft.com...
>> My users are getting the following error
>> "The underlying connection was closed: Could not establish secure channel
>> for SSL/TLS."
>>
>> any idea'?
>
Subscribe to:
Posts (Atom)