Is there a SQL command which will kill or exit all batches in the query
analyzer?
For example I have this script:
query1
query2
GO
query3
query4
query5
GO
Is there a way so that when I check @.@.ERROR after query1 that query2,3,4,5
do NOT get executed? GOTO's cannot see beyond the next GO. RETURN just
jumps to right after the next GO as well.
Thanks,
Greg
Greg Michalopoulos wrote:
> Is there a SQL command which will kill or exit all batches in the
> query analyzer?
> For example I have this script:
> query1
> query2
> GO
> query3
> query4
> query5
> GO
> Is there a way so that when I check @.@.ERROR after query1 that
> query2,3,4,5 do NOT get executed? GOTO's cannot see beyond the next
> GO. RETURN just jumps to right after the next GO as well.
> Thanks,
> Greg
I assume you are running this from QA or equivalent? You can use a temp
table to store status if you need to.
David Gugick
Imceda Software
www.imceda.com
Showing posts with label multiple. Show all posts
Showing posts with label multiple. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
Kick off procs
Hello. I was wondering what is the best way to kick off multiple procs
trapping the ones that had an error. Here's an example of what I came up
with.
alter procedure dbo.testError
@.problem int = 0 OUTPUT
AS
set nocount on
print 'start'
Declare @.error_msg int
set @.error_msg = 0
set @.error_msg = (Select count(*) from notable)
print 'yo'
select @.error_msg = @.@.error
IF @.error_msg != 0 GOTO handle_error
return @.Problem
handle_error:
set @.Problem = @.error_msg + @.Problem
print @.Problem
-- this is where I would kick off the processes in sequence
declare @.msg int
EXEC @.msg = testError
print 'testing = ' + convert(varchar(20), @.msg)
on testError I have an output variable. when you kick off the proc in my
kick off code, the print line never gets executed. and the subroutine in the
main proc never gets called. Really all I want to do is kick off a list of
sprocs and write to a table wether it was a success or not, then go on
kicking off the next sproc. Also, is it nessecarry to alter all my existing
sprocs to have an output variable and catch @.@.error on all calls to the db,
if not that would be ideal.
just wondering how everyone handles trapping errors with a kick off sproc,
and why this is not working.
Thanks,
RobRobert H wrote:
> Hello. I was wondering what is the best way to kick off multiple procs
> trapping the ones that had an error. Here's an example of what I came
> up with.
> alter procedure dbo.testError
> @.problem int = 0 OUTPUT
> AS
> set nocount on
> print 'start'
> Declare @.error_msg int
> set @.error_msg = 0
> set @.error_msg = (Select count(*) from notable)
> print 'yo'
> select @.error_msg = @.@.error
> IF @.error_msg != 0 GOTO handle_error
>
> return @.Problem
> handle_error:
> set @.Problem = @.error_msg + @.Problem
> print @.Problem
>
> -- this is where I would kick off the processes in sequence
> declare @.msg int
> EXEC @.msg = testError
> print 'testing = ' + convert(varchar(20), @.msg)
>
> on testError I have an output variable. when you kick off the proc in
> my kick off code, the print line never gets executed. and the
> subroutine in the main proc never gets called. Really all I want to
> do is kick off a list of sprocs and write to a table wether it was a
> success or not, then go on kicking off the next sproc. Also, is it
> nessecarry to alter all my existing sprocs to have an output variable
> and catch @.@.error on all calls to the db, if not that would be ideal.
> just wondering how everyone handles trapping errors with a kick off
> sproc, and why this is not working.
> Thanks,
> Rob
You're using a return value and an OUTPUT paramer in some interleaved
fashion, but they are not compatible.
You can return an INT return value using the following exec code and a
return statement:
DECLARE @.iRet INT
EXEC @.iRet = dbo.MyProc
PRINT @.iRet
You can use an OUTPUT parameter of most any data type and access the
value with the following exec code:
DECLARE @.NewName VARCHAR(50)
EXEC dbo.MyProc @.NewName OUTPUT
PRINT @.NewName
Or to do both:
DECLARE @.iRet INT
DECLARE @.NewName VARCHAR(50)
EXEC @.iRet = dbo.MyProc @.NewName OUTPUT
SELECT @.iRet, @.NewName
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi
Your print statement will reset @.@.ERROR therefore it is not going to give
you an error value.
You may want to read
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
John
"Robert H" wrote:
> Hello. I was wondering what is the best way to kick off multiple procs
> trapping the ones that had an error. Here's an example of what I came up
> with.
> alter procedure dbo.testError
> @.problem int = 0 OUTPUT
> AS
> set nocount on
> print 'start'
> Declare @.error_msg int
> set @.error_msg = 0
> set @.error_msg = (Select count(*) from notable)
> print 'yo'
> select @.error_msg = @.@.error
> IF @.error_msg != 0 GOTO handle_error
>
> return @.Problem
> handle_error:
> set @.Problem = @.error_msg + @.Problem
> print @.Problem
>
> -- this is where I would kick off the processes in sequence
> declare @.msg int
> EXEC @.msg = testError
> print 'testing = ' + convert(varchar(20), @.msg)
>
> on testError I have an output variable. when you kick off the proc in my
> kick off code, the print line never gets executed. and the subroutine in t
he
> main proc never gets called. Really all I want to do is kick off a list of
> sprocs and write to a table wether it was a success or not, then go on
> kicking off the next sproc. Also, is it nessecarry to alter all my existin
g
> sprocs to have an output variable and catch @.@.error on all calls to the db
,
> if not that would be ideal.
> just wondering how everyone handles trapping errors with a kick off sproc,
> and why this is not working.
> Thanks,
> Rob
>
>
trapping the ones that had an error. Here's an example of what I came up
with.
alter procedure dbo.testError
@.problem int = 0 OUTPUT
AS
set nocount on
print 'start'
Declare @.error_msg int
set @.error_msg = 0
set @.error_msg = (Select count(*) from notable)
print 'yo'
select @.error_msg = @.@.error
IF @.error_msg != 0 GOTO handle_error
return @.Problem
handle_error:
set @.Problem = @.error_msg + @.Problem
print @.Problem
-- this is where I would kick off the processes in sequence
declare @.msg int
EXEC @.msg = testError
print 'testing = ' + convert(varchar(20), @.msg)
on testError I have an output variable. when you kick off the proc in my
kick off code, the print line never gets executed. and the subroutine in the
main proc never gets called. Really all I want to do is kick off a list of
sprocs and write to a table wether it was a success or not, then go on
kicking off the next sproc. Also, is it nessecarry to alter all my existing
sprocs to have an output variable and catch @.@.error on all calls to the db,
if not that would be ideal.
just wondering how everyone handles trapping errors with a kick off sproc,
and why this is not working.
Thanks,
RobRobert H wrote:
> Hello. I was wondering what is the best way to kick off multiple procs
> trapping the ones that had an error. Here's an example of what I came
> up with.
> alter procedure dbo.testError
> @.problem int = 0 OUTPUT
> AS
> set nocount on
> print 'start'
> Declare @.error_msg int
> set @.error_msg = 0
> set @.error_msg = (Select count(*) from notable)
> print 'yo'
> select @.error_msg = @.@.error
> IF @.error_msg != 0 GOTO handle_error
>
> return @.Problem
> handle_error:
> set @.Problem = @.error_msg + @.Problem
> print @.Problem
>
> -- this is where I would kick off the processes in sequence
> declare @.msg int
> EXEC @.msg = testError
> print 'testing = ' + convert(varchar(20), @.msg)
>
> on testError I have an output variable. when you kick off the proc in
> my kick off code, the print line never gets executed. and the
> subroutine in the main proc never gets called. Really all I want to
> do is kick off a list of sprocs and write to a table wether it was a
> success or not, then go on kicking off the next sproc. Also, is it
> nessecarry to alter all my existing sprocs to have an output variable
> and catch @.@.error on all calls to the db, if not that would be ideal.
> just wondering how everyone handles trapping errors with a kick off
> sproc, and why this is not working.
> Thanks,
> Rob
You're using a return value and an OUTPUT paramer in some interleaved
fashion, but they are not compatible.
You can return an INT return value using the following exec code and a
return statement:
DECLARE @.iRet INT
EXEC @.iRet = dbo.MyProc
PRINT @.iRet
You can use an OUTPUT parameter of most any data type and access the
value with the following exec code:
DECLARE @.NewName VARCHAR(50)
EXEC dbo.MyProc @.NewName OUTPUT
PRINT @.NewName
Or to do both:
DECLARE @.iRet INT
DECLARE @.NewName VARCHAR(50)
EXEC @.iRet = dbo.MyProc @.NewName OUTPUT
SELECT @.iRet, @.NewName
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi
Your print statement will reset @.@.ERROR therefore it is not going to give
you an error value.
You may want to read
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
John
"Robert H" wrote:
> Hello. I was wondering what is the best way to kick off multiple procs
> trapping the ones that had an error. Here's an example of what I came up
> with.
> alter procedure dbo.testError
> @.problem int = 0 OUTPUT
> AS
> set nocount on
> print 'start'
> Declare @.error_msg int
> set @.error_msg = 0
> set @.error_msg = (Select count(*) from notable)
> print 'yo'
> select @.error_msg = @.@.error
> IF @.error_msg != 0 GOTO handle_error
>
> return @.Problem
> handle_error:
> set @.Problem = @.error_msg + @.Problem
> print @.Problem
>
> -- this is where I would kick off the processes in sequence
> declare @.msg int
> EXEC @.msg = testError
> print 'testing = ' + convert(varchar(20), @.msg)
>
> on testError I have an output variable. when you kick off the proc in my
> kick off code, the print line never gets executed. and the subroutine in t
he
> main proc never gets called. Really all I want to do is kick off a list of
> sprocs and write to a table wether it was a success or not, then go on
> kicking off the next sproc. Also, is it nessecarry to alter all my existin
g
> sprocs to have an output variable and catch @.@.error on all calls to the db
,
> if not that would be ideal.
> just wondering how everyone handles trapping errors with a kick off sproc,
> and why this is not working.
> Thanks,
> Rob
>
>
kick off multiple procs
Hello. I was wondering what is the best way to kick off multiple procs
trapping the ones that had an error. Here's an example of what I came up
with.
alter procedure dbo.testError
@.problem int = 0 OUTPUT
AS
set nocount on
print 'start'
Declare @.error_msg int
set @.error_msg = 0
set @.error_msg = (Select count(*) from notable)
print 'yo'
select @.error_msg = @.@.error
IF @.error_msg != 0 GOTO handle_error
return @.Problem
handle_error:
set @.Problem = @.error_msg + @.Problem
print @.Problem
-- this is where I would kick off the processes in sequence
declare @.msg int
EXEC @.msg = testError
print 'testing = ' + convert(varchar(20), @.msg)
on testError I have an output variable. when you kick off the proc in my
kick off code, the print line never gets executed. and the subroutine in the
main proc never gets called. Really all I want to do is kick off a list of
sprocs and write to a table wether it was a success or not, then go on
kicking off the next sproc. Also, is it nessecarry to alter all my existing
sprocs to have an output variable and catch @.@.error on all calls to the db,
if not that would be ideal.
just wondering how everyone handles trapping errors with a kick off sproc,
and why this is not working.
Thanks,
RobRobert,
Check out:
http://www.sommarskog.se/error-handling-I.html
and
http://www.sommarskog.se/error-handling-II.html
HTH
Jerry
"Robert H" <thestripe@.yahoo_spamno.com> wrote in message
news:u%23p0BJQyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello. I was wondering what is the best way to kick off multiple procs
> trapping the ones that had an error. Here's an example of what I came up
> with.
> alter procedure dbo.testError
> @.problem int = 0 OUTPUT
> AS
> set nocount on
> print 'start'
> Declare @.error_msg int
> set @.error_msg = 0
> set @.error_msg = (Select count(*) from notable)
> print 'yo'
> select @.error_msg = @.@.error
> IF @.error_msg != 0 GOTO handle_error
>
> return @.Problem
> handle_error:
> set @.Problem = @.error_msg + @.Problem
> print @.Problem
>
> -- this is where I would kick off the processes in sequence
> declare @.msg int
> EXEC @.msg = testError
> print 'testing = ' + convert(varchar(20), @.msg)
>
> on testError I have an output variable. when you kick off the proc in my
> kick off code, the print line never gets executed. and the subroutine in
> the
> main proc never gets called. Really all I want to do is kick off a list of
> sprocs and write to a table wether it was a success or not, then go on
> kicking off the next sproc. Also, is it nessecarry to alter all my
> existing
> sprocs to have an output variable and catch @.@.error on all calls to the
> db,
> if not that would be ideal.
> just wondering how everyone handles trapping errors with a kick off sproc,
> and why this is not working.
> Thanks,
> Rob
>
>
trapping the ones that had an error. Here's an example of what I came up
with.
alter procedure dbo.testError
@.problem int = 0 OUTPUT
AS
set nocount on
print 'start'
Declare @.error_msg int
set @.error_msg = 0
set @.error_msg = (Select count(*) from notable)
print 'yo'
select @.error_msg = @.@.error
IF @.error_msg != 0 GOTO handle_error
return @.Problem
handle_error:
set @.Problem = @.error_msg + @.Problem
print @.Problem
-- this is where I would kick off the processes in sequence
declare @.msg int
EXEC @.msg = testError
print 'testing = ' + convert(varchar(20), @.msg)
on testError I have an output variable. when you kick off the proc in my
kick off code, the print line never gets executed. and the subroutine in the
main proc never gets called. Really all I want to do is kick off a list of
sprocs and write to a table wether it was a success or not, then go on
kicking off the next sproc. Also, is it nessecarry to alter all my existing
sprocs to have an output variable and catch @.@.error on all calls to the db,
if not that would be ideal.
just wondering how everyone handles trapping errors with a kick off sproc,
and why this is not working.
Thanks,
RobRobert,
Check out:
http://www.sommarskog.se/error-handling-I.html
and
http://www.sommarskog.se/error-handling-II.html
HTH
Jerry
"Robert H" <thestripe@.yahoo_spamno.com> wrote in message
news:u%23p0BJQyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello. I was wondering what is the best way to kick off multiple procs
> trapping the ones that had an error. Here's an example of what I came up
> with.
> alter procedure dbo.testError
> @.problem int = 0 OUTPUT
> AS
> set nocount on
> print 'start'
> Declare @.error_msg int
> set @.error_msg = 0
> set @.error_msg = (Select count(*) from notable)
> print 'yo'
> select @.error_msg = @.@.error
> IF @.error_msg != 0 GOTO handle_error
>
> return @.Problem
> handle_error:
> set @.Problem = @.error_msg + @.Problem
> print @.Problem
>
> -- this is where I would kick off the processes in sequence
> declare @.msg int
> EXEC @.msg = testError
> print 'testing = ' + convert(varchar(20), @.msg)
>
> on testError I have an output variable. when you kick off the proc in my
> kick off code, the print line never gets executed. and the subroutine in
> the
> main proc never gets called. Really all I want to do is kick off a list of
> sprocs and write to a table wether it was a success or not, then go on
> kicking off the next sproc. Also, is it nessecarry to alter all my
> existing
> sprocs to have an output variable and catch @.@.error on all calls to the
> db,
> if not that would be ideal.
> just wondering how everyone handles trapping errors with a kick off sproc,
> and why this is not working.
> Thanks,
> Rob
>
>
Monday, March 19, 2012
Keys out of order on page error
We have found we have corruption in our db.
After running DBCC CHECKDB the following error were displayed on
multiple pages.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
(1:384662), slots 75 and 76.
I read a post that suggested running DBCC CHECKDB with the repair_build
option.
Since we are a 24/7 shop I cannot restore from tape, because it would
take several hours.
What are the risks of running DBCC CHECKDB with the repair_build option,
and will this fix the problem properly?
We are still unsure what has caused this corruption, any body else have
this problem?
TIA,
-BenThere are a couple of things.
Firstly it is very intensive so turn off as many services
you can get away with, i.e SQL Agent
Secondly depending upon the severity of the error it may
result in data loss. Personally I would try firstly with
REPAIR_FAST, then REPAIR_REBUILD and if all else fails
REPAIR_ALLOW_DATA_LOSS, its that option where you may lose
data.
If the REPAIR_ALLOW_DATA_LOSS doesn't work then you will
have to restore from backup.
As for the reason why to difficult to say.
Good luck
Peter
>--Original Message--
>We have found we have corruption in our db.
>After running DBCC CHECKDB the following error were
displayed on
>multiple pages.
>Server: Msg 2511, Level 16, State 1, Line 1
>Table error: Object ID 1285579618, Index ID 3. Keys out
of order on page
>(1:384662), slots 75 and 76.
>I read a post that suggested running DBCC CHECKDB with
the repair_build
>option.
>Since we are a 24/7 shop I cannot restore from tape,
because it would
>take several hours.
>What are the risks of running DBCC CHECKDB with the
repair_build option,
>and will this fix the problem properly?
>We are still unsure what has caused this corruption, any
body else have
>this problem?
>TIA,
>-Ben
>.
>|||Hi Ben,
As the error is in a non-clustered index, you can just rebuild the index
with DBCC DBREINDEX (<table_name>, <index_name>). Or if you want to be on
the safe side, just drop and recreate the index.
After that, investigate why the corruption occurred in the first place. You
might have hardware problems.
--
Jacco Schalkwijk
SQL Server MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:OJgEzgufEHA.636@.TK2MSFTNGP12.phx.gbl...
> We have found we have corruption in our db.
> After running DBCC CHECKDB the following error were displayed on multiple
> pages.
> Server: Msg 2511, Level 16, State 1, Line 1
> Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
> (1:384662), slots 75 and 76.
> I read a post that suggested running DBCC CHECKDB with the repair_build
> option.
> Since we are a 24/7 shop I cannot restore from tape, because it would take
> several hours.
> What are the risks of running DBCC CHECKDB with the repair_build option,
> and will this fix the problem properly?
> We are still unsure what has caused this corruption, any body else have
> this problem?
> TIA,
> -Ben|||In addition:
The latest update of Books Online has some very detailed recommendations for most error numbers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:374a01c47eee$f6a6a890$a501280a@.phx.gbl...
> There are a couple of things.
> Firstly it is very intensive so turn off as many services
> you can get away with, i.e SQL Agent
> Secondly depending upon the severity of the error it may
> result in data loss. Personally I would try firstly with
> REPAIR_FAST, then REPAIR_REBUILD and if all else fails
> REPAIR_ALLOW_DATA_LOSS, its that option where you may lose
> data.
> If the REPAIR_ALLOW_DATA_LOSS doesn't work then you will
> have to restore from backup.
> As for the reason why to difficult to say.
> Good luck
> Peter
>
> >--Original Message--
> >We have found we have corruption in our db.
> >
> >After running DBCC CHECKDB the following error were
> displayed on
> >multiple pages.
> >
> >Server: Msg 2511, Level 16, State 1, Line 1
> >Table error: Object ID 1285579618, Index ID 3. Keys out
> of order on page
> >(1:384662), slots 75 and 76.
> >
> >I read a post that suggested running DBCC CHECKDB with
> the repair_build
> >option.
> >
> >Since we are a 24/7 shop I cannot restore from tape,
> because it would
> >take several hours.
> >
> >What are the risks of running DBCC CHECKDB with the
> repair_build option,
> >and will this fix the problem properly?
> >
> >We are still unsure what has caused this corruption, any
> body else have
> >this problem?
> >
> >TIA,
> >
> >-Ben
> >.
> >|||I have dropped and recreated the index, thanks. I have also opened up
an issue with our vendor to see if this may be a possible cause.
Jacco Schalkwijk wrote:
> Hi Ben,
> As the error is in a non-clustered index, you can just rebuild the index
> with DBCC DBREINDEX (<table_name>, <index_name>). Or if you want to be on
> the safe side, just drop and recreate the index.
> After that, investigate why the corruption occurred in the first place. You
> might have hardware problems.
>
After running DBCC CHECKDB the following error were displayed on
multiple pages.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
(1:384662), slots 75 and 76.
I read a post that suggested running DBCC CHECKDB with the repair_build
option.
Since we are a 24/7 shop I cannot restore from tape, because it would
take several hours.
What are the risks of running DBCC CHECKDB with the repair_build option,
and will this fix the problem properly?
We are still unsure what has caused this corruption, any body else have
this problem?
TIA,
-BenThere are a couple of things.
Firstly it is very intensive so turn off as many services
you can get away with, i.e SQL Agent
Secondly depending upon the severity of the error it may
result in data loss. Personally I would try firstly with
REPAIR_FAST, then REPAIR_REBUILD and if all else fails
REPAIR_ALLOW_DATA_LOSS, its that option where you may lose
data.
If the REPAIR_ALLOW_DATA_LOSS doesn't work then you will
have to restore from backup.
As for the reason why to difficult to say.
Good luck
Peter
>--Original Message--
>We have found we have corruption in our db.
>After running DBCC CHECKDB the following error were
displayed on
>multiple pages.
>Server: Msg 2511, Level 16, State 1, Line 1
>Table error: Object ID 1285579618, Index ID 3. Keys out
of order on page
>(1:384662), slots 75 and 76.
>I read a post that suggested running DBCC CHECKDB with
the repair_build
>option.
>Since we are a 24/7 shop I cannot restore from tape,
because it would
>take several hours.
>What are the risks of running DBCC CHECKDB with the
repair_build option,
>and will this fix the problem properly?
>We are still unsure what has caused this corruption, any
body else have
>this problem?
>TIA,
>-Ben
>.
>|||Hi Ben,
As the error is in a non-clustered index, you can just rebuild the index
with DBCC DBREINDEX (<table_name>, <index_name>). Or if you want to be on
the safe side, just drop and recreate the index.
After that, investigate why the corruption occurred in the first place. You
might have hardware problems.
--
Jacco Schalkwijk
SQL Server MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:OJgEzgufEHA.636@.TK2MSFTNGP12.phx.gbl...
> We have found we have corruption in our db.
> After running DBCC CHECKDB the following error were displayed on multiple
> pages.
> Server: Msg 2511, Level 16, State 1, Line 1
> Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
> (1:384662), slots 75 and 76.
> I read a post that suggested running DBCC CHECKDB with the repair_build
> option.
> Since we are a 24/7 shop I cannot restore from tape, because it would take
> several hours.
> What are the risks of running DBCC CHECKDB with the repair_build option,
> and will this fix the problem properly?
> We are still unsure what has caused this corruption, any body else have
> this problem?
> TIA,
> -Ben|||In addition:
The latest update of Books Online has some very detailed recommendations for most error numbers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:374a01c47eee$f6a6a890$a501280a@.phx.gbl...
> There are a couple of things.
> Firstly it is very intensive so turn off as many services
> you can get away with, i.e SQL Agent
> Secondly depending upon the severity of the error it may
> result in data loss. Personally I would try firstly with
> REPAIR_FAST, then REPAIR_REBUILD and if all else fails
> REPAIR_ALLOW_DATA_LOSS, its that option where you may lose
> data.
> If the REPAIR_ALLOW_DATA_LOSS doesn't work then you will
> have to restore from backup.
> As for the reason why to difficult to say.
> Good luck
> Peter
>
> >--Original Message--
> >We have found we have corruption in our db.
> >
> >After running DBCC CHECKDB the following error were
> displayed on
> >multiple pages.
> >
> >Server: Msg 2511, Level 16, State 1, Line 1
> >Table error: Object ID 1285579618, Index ID 3. Keys out
> of order on page
> >(1:384662), slots 75 and 76.
> >
> >I read a post that suggested running DBCC CHECKDB with
> the repair_build
> >option.
> >
> >Since we are a 24/7 shop I cannot restore from tape,
> because it would
> >take several hours.
> >
> >What are the risks of running DBCC CHECKDB with the
> repair_build option,
> >and will this fix the problem properly?
> >
> >We are still unsure what has caused this corruption, any
> body else have
> >this problem?
> >
> >TIA,
> >
> >-Ben
> >.
> >|||I have dropped and recreated the index, thanks. I have also opened up
an issue with our vendor to see if this may be a possible cause.
Jacco Schalkwijk wrote:
> Hi Ben,
> As the error is in a non-clustered index, you can just rebuild the index
> with DBCC DBREINDEX (<table_name>, <index_name>). Or if you want to be on
> the safe side, just drop and recreate the index.
> After that, investigate why the corruption occurred in the first place. You
> might have hardware problems.
>
Wednesday, March 7, 2012
Keeping multiple SSRS instances syncrhonized?
Hi all,
I have a problem to solve for synchronizing report objects between multiple instances of reporting services. Our production data is distributed via replication from our head office to database servers at our remote offices. Likewise, we want to have an instance of reporting services co-located with each of the databases and therefore will be installing reporting services instances at the remote offices.
I would like to optimize for efficiency in keeping the report objects in sync, minimizing data transfer and as little manual intervention as possible so as to avoid mistakes. I have come up with several approaches to acheiving the SSRS replication I seek:
a) Use a scale-out deployment. This would mean creating multiple instances of the reports to account for the multiple DataSources I would need to configure, since I can't parameterize the DataSource in a linked report.
b) Manually replicate reports to each of my deployments using BIDS/SSMS. This would involve a ton of admin as all objects should be kept in sync.
c) Write RSS scripts to handle the synchronization of the servers. This shouldn't be too hard given that the webservices should play nicely with such a solution. It might take some effort to write the object comparison routine.
d) Have the replication engine take care of the replication of report objects between the servers. If I sync up the encryption keys, could I set up replication on all the tables I need synchronized and just be done with it? This would be my favored solution but I'm not sure it is possible.
Any ideas and suggestions would be appreciated.
Thanks,
Calvin
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de|||I know this is already marked answered, but I figured I would put this up here since I ran across it today, probably would do what you needed
http://www.sqldbatips.com/showarticle.asp?ID=62
it worked for me at least 
Keeping multiple SSRS instances syncrhonized?
Hi all,
I have a problem to solve for synchronizing report objects between multiple instances of reporting services. Our production data is distributed via replication from our head office to database servers at our remote offices. Likewise, we want to have an instance of reporting services co-located with each of the databases and therefore will be installing reporting services instances at the remote offices.
I would like to optimize for efficiency in keeping the report objects in sync, minimizing data transfer and as little manual intervention as possible so as to avoid mistakes. I have come up with several approaches to acheiving the SSRS replication I seek:
a) Use a scale-out deployment. This would mean creating multiple instances of the reports to account for the multiple DataSources I would need to configure, since I can't parameterize the DataSource in a linked report.
b) Manually replicate reports to each of my deployments using BIDS/SSMS. This would involve a ton of admin as all objects should be kept in sync.
c) Write RSS scripts to handle the synchronization of the servers. This shouldn't be too hard given that the webservices should play nicely with such a solution. It might take some effort to write the object comparison routine.
d) Have the replication engine take care of the replication of report objects between the servers. If I sync up the encryption keys, could I set up replication on all the tables I need synchronized and just be done with it? This would be my favored solution but I'm not sure it is possible.
Any ideas and suggestions would be appreciated.
Thanks,
Calvin
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
|||I know this is already marked answered, but I figured I would put this up here since I ran across it today, probably would do what you needed
http://www.sqldbatips.com/showarticle.asp?ID=62
it worked for me at least 
Keeping multiple detail lines together
Hi All,
I have a report that has a detail line with fields that can grow. I want to place a horizontal line at the bottom of the detail line.
Problem is that the line goes where I put it so if the fields that can grow get bigger than where the line is then the line is not at the bottom of the detail line.
Best answer I have come up with is to have a second detail line that only contains the horizontal line.
In most cases this works very well but every now and again I get the situation where the first detail line fits at the bottom of the page but the second detail line does not. The second detail line is then printed on the next page. Not good.
Question is how do I get the two detail lines to always be kept together? I know we can keep together one section by using the Section Expert, but what about keeping two sections together?
Any ideas?
Regards,
HywelCHi:
I am not sure wether you want underline below that text field only or it will go horizontal from left to right but ...
if you want under line below that field only just right click on field -> formate field -> border -> make bottom SINGLE
OR
if you need line in report then just insert new section (make sure section type should be same ... if u have field in report header insert report header , if you have detail section , insert detail section) and in 2nd section just put horizontal line only and in formate section check "keep together"
still you find some problem let me know
best of luck|||Hi jmendpara,
The line needs to go all the way across the page, not just under the fields.
Both detail sections have keep together checked but this only works within the individual section. It does not keep the two detail sections together as if it were one section.
Regards,
HywelC
I have a report that has a detail line with fields that can grow. I want to place a horizontal line at the bottom of the detail line.
Problem is that the line goes where I put it so if the fields that can grow get bigger than where the line is then the line is not at the bottom of the detail line.
Best answer I have come up with is to have a second detail line that only contains the horizontal line.
In most cases this works very well but every now and again I get the situation where the first detail line fits at the bottom of the page but the second detail line does not. The second detail line is then printed on the next page. Not good.
Question is how do I get the two detail lines to always be kept together? I know we can keep together one section by using the Section Expert, but what about keeping two sections together?
Any ideas?
Regards,
HywelCHi:
I am not sure wether you want underline below that text field only or it will go horizontal from left to right but ...
if you want under line below that field only just right click on field -> formate field -> border -> make bottom SINGLE
OR
if you need line in report then just insert new section (make sure section type should be same ... if u have field in report header insert report header , if you have detail section , insert detail section) and in 2nd section just put horizontal line only and in formate section check "keep together"
still you find some problem let me know
best of luck|||Hi jmendpara,
The line needs to go all the way across the page, not just under the fields.
Both detail sections have keep together checked but this only works within the individual section. It does not keep the two detail sections together as if it were one section.
Regards,
HywelC
Keeping Multiple Detail Lines in a Table Together
I have a table in my report. The table has two detail lines per detail item. My problem is that the two lines really need to be kept together, and the table will page break between the lines when the end of page is reached. I have been looking through the different table properties, and I have not been able to locate anything that lends a hand to my issue. I did some quick searches, and I couldn't find another thread requesting this information. I may just be missing something obvious.
Any help and/or advice would be great! Thanks.
Curtis
P.S.
This is what I am working with
Detail 1 = Product Description, Product Number
Detail 2 = Various Pricing and Units of Measure, Barcode
It makes it a little difficult if these two lines are not kept together! Thanks again!
I have not come up with a solution for my "Keeping Multiple Detail Lines in a Table Together", but I have thought up a work around that I would like to use. I wanted to page break at a specified line number to reduce or eliminate the detail lines being separated, because of where they fall on the page. The problem is, I cannot get the work around to function.
Page Breaks
In some reports, you may want to place a page break at the end of a specified number of rows instead of, or in addition to, on groups or report items. To do this, create a group in a data region (typically a group immediately outside the detail), add a page break to the group, and then add a group expression to group by a specified number of rows.
The following expression, when placed in the group expression, assigns a number to each set of 25 rows. When a page break is defined for the group, this results in a page break every 25 rows.
=Int((RowNumber(Nothing)-1)/25)
The preceding information was located in a help file that I found. (ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/87ddb651-a1d0-4a42-8ea9-04dea3f6afa4.htm to reference the help file) I created a group immediately outside my details. In the expression for the group I inserted the code verbatim.
I clicked preview, and the page break broke the report. I received the following error:
[rsInvalidGroupExpressionScope] A group expression for the table ‘table1’ uses the RowNumber function with a scope parameter that is not valid. When used in a group expression, the value of the scope parameter of RowNumber must equal the name of the group directly containing the current group.
So, what do I insert for "Nothing" that would give me the desired results?
Any help/advice is greatly appreciated!
Thanks
Curtis
|||One way of doing this is to created a rectangle and embed it in your detail line. This means that you have to use text boxes to display your data rather than the table cells, but it does resolve the problem with keeping the lines together.Keeping Multiple Detail Lines in a Table Together
I have a table in my report. The table has two detail lines per detail item. My problem is that the two lines really need to be kept together, and the table will page break between the lines when the end of page is reached. I have been looking through the different table properties, and I have not been able to locate anything that lends a hand to my issue. I did some quick searches, and I couldn't find another thread requesting this information. I may just be missing something obvious.
Any help and/or advice would be great! Thanks.
Curtis
P.S.
This is what I am working with
Detail 1 = Product Description, Product Number
Detail 2 = Various Pricing and Units of Measure, Barcode
It makes it a little difficult if these two lines are not kept together! Thanks again!
I have not come up with a solution for my "Keeping Multiple Detail Lines in a Table Together", but I have thought up a work around that I would like to use. I wanted to page break at a specified line number to reduce or eliminate the detail lines being separated, because of where they fall on the page. The problem is, I cannot get the work around to function.
Page Breaks
In some reports, you may want to place a page break at the end of a specified number of rows instead of, or in addition to, on groups or report items. To do this, create a group in a data region (typically a group immediately outside the detail), add a page break to the group, and then add a group expression to group by a specified number of rows.
The following expression, when placed in the group expression, assigns a number to each set of 25 rows. When a page break is defined for the group, this results in a page break every 25 rows.
=Int((RowNumber(Nothing)-1)/25)
The preceding information was located in a help file that I found. (ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/87ddb651-a1d0-4a42-8ea9-04dea3f6afa4.htm to reference the help file) I created a group immediately outside my details. In the expression for the group I inserted the code verbatim.
I clicked preview, and the page break broke the report. I received the following error:
[rsInvalidGroupExpressionScope] A group expression for the table ‘table1’ uses the RowNumber function with a scope parameter that is not valid. When used in a group expression, the value of the scope parameter of RowNumber must equal the name of the group directly containing the current group.
So, what do I insert for "Nothing" that would give me the desired results?
Any help/advice is greatly appreciated!
Thanks
Curtis
|||One way of doing this is to created a rectangle and embed it in your detail line. This means that you have to use text boxes to display your data rather than the table cells, but it does resolve the problem with keeping the lines together.Keep Together Possible?
I have a report which contains multiple sub-reports to display different data types that do not relate, but are needed on the same report (the reason for the various sub-reports).
Everything was working fine, until....
The first sub-report is very small, prints maybe 4 inches down the first page. The second sub-report is very large, and now has grown larger than the remainder of the page.
So the second sub-report pushes down and prints of on the 2nd page, leaving most of the first page blank (looks HORRIBLE). I have placed all the sub-reports into a rectangle property, but still do not see anyway to keep the sub-reports together (so they look continuous).
Does anyone know of a work around for this?
Thank you for you help.
T.J.
I'm sorry, there is not a way to override this implicit keep-together on the subreport. The closest you'll get is if you combine the two reports into one.
|||Thank you for your help.
What is really weird is on the 2nd page of the report, where the 2nd sub-report starts, a 3rd sub-report prints as well.
The 3rd sub-report does print on two pages (where the 2nd sub-report will not do this). And I can find nothing different in the properties of the sub-reports, the main report, or the tables used on the sub-reports.
Very weird. It is like SQL reporting services just picks and chooses when it wants the sub-report to behave correctly.
Hate to have to go back to Crystal, but that might be the only option.
Let me know if you have any other ideas.
Thank you,
T.J.
|||Was the third subreport too large to fit on a single page? If so, it means we can't honor the keep-together and will not try and push it to the next page.|||Yes, the 3rd sub-report is too large to fit on one page (like the 2nd sub-report), but it breaks out correctly and prints on the 2nd and 3rd page (as expected, the third sub-report starts on the 2nd page, and continues to the 3rd page).
Only the 2nd sub-report forces down to a new page. And I cannot find anything different in these sub-reports at all.
Very strange deal.
Thank you for your help. If worse comes to worse, I can build it in Crystal and run it for the users and send them a .pdf.
|||Printing it out and looking closely, it appears the report footer in the 2nd sub-form just barely does not fit on the first page.
I cannot find any properties to control this, but maybe the glitch is caused by the report forcing the page footer to print on the same page as the rest of the sub-report.
I then expanded the vertical white space in my 2nd sub-report, and the overall report printed as I had hoped (the 2nd sub-report correctly starts on the first page). The bad thing is now my 2nd sub-report has vertical white space on each record that I did not need added.
A start though.
Any thoughts?
I
|||Just wanted to follow up, for other report developer's sake.
Maybe I caught a glitch with SQL reporting services, in which the footer forces the entire sub-form to move to the next page? Maybe this is already known?
Any thoughts?
Thank you!
T.J.
Monday, February 20, 2012
KDC Error
Hello:
I am getting a KDC error on my domain controllers system log. The error is
below.
Event Source: KDC
Event ID: 11
Description:
There are multiple accounts with name "MSSQLSvc/computer.domain.local:1433"
of type 10.
I have tracked this down to one of the developers domain admin accounts. It
is the "servicePrincipalName" on his account. This is the same
"servicePrincipalName" as the SQL server. I research this before and it was
thought perhaps he had install SQL under this account or at one point SQL
was running under this account. When I removed it SQL basically shutdown.
I immediately put it back. Later someone told me I should have just stopped
and restarted the SQL service and it would have corrected the problem.
Can anyone give me any input on this? This is a major production server and
I want to be very careful.
Harrison MidkiffHi
I am not sure about what the cause of this problem is, but the solution may
be to create a new service account, set SQL Server to use this account,
stop/restart SQL server at a scheduled time. Changing the old service accoun
t
should then not effect the SQL Server.
John
"Harrison Midkiff" wrote:
> Hello:
> I am getting a KDC error on my domain controllers system log. The error i
s
> below.
> Event Source: KDC
> Event ID: 11
> Description:
> There are multiple accounts with name "MSSQLSvc/computer.domain.local:1433
"
> of type 10.
> I have tracked this down to one of the developers domain admin accounts.
It
> is the "servicePrincipalName" on his account. This is the same
> "servicePrincipalName" as the SQL server. I research this before and it w
as
> thought perhaps he had install SQL under this account or at one point SQL
> was running under this account. When I removed it SQL basically shutdown
.
> I immediately put it back. Later someone told me I should have just stopp
ed
> and restarted the SQL service and it would have corrected the problem.
> Can anyone give me any input on this? This is a major production server a
nd
> I want to be very careful.
> Harrison Midkiff
>
>|||Hi Harrison ,
This issue can occur if SQL accout is changed from Machine account to
service account. The SPN for the machine account was not removed from the
domain.
I suggest that you use LDIFDE to export the domain to text file and seach
for MSSQLSVC.
Syntax was:
LDIFDE -d DC=DEFIANT,DC=pactsolutions,DC=com, DC=au -f c:\export.txt
After you locate the duplicate SPN, you can use the Adsiedit.msc tool to go
to the object, view the duplicate SPN value, and remove the duplicate SPN
value. To determine which one was the one in current use, please delete one
of the
MSSQLSvc/uohssrv1.uohs.uottawa.ca:1433 SPNs, then restarted the SQL
service(s).
The SQL service will re-add the SPN on the currently in use service
account. If it is not readded, then the duplicate has been removed.
You can refer to the following article for more related information about
Kerberos Terminology and Service Principle Name (SPN).
811889.KB.EN-US HOW TO: Troubleshoot the "Cannot Generate SSPI Context"
Error Message
http://support.microsoft.com/defaul...KB;EN-US;811889
Hope this helps.
Best regards,
Vincent Xu
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 this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
is[vbcol=seagreen]
"MSSQLSvc/computer.domain.local:1433"[vbcol=seagreen]
It[vbcol=seagreen]
was[vbcol=seagreen]
shutdown.[vbcol=seagreen]
stopped[vbcol=seagreen]
and[vbcol=seagreen]
I am getting a KDC error on my domain controllers system log. The error is
below.
Event Source: KDC
Event ID: 11
Description:
There are multiple accounts with name "MSSQLSvc/computer.domain.local:1433"
of type 10.
I have tracked this down to one of the developers domain admin accounts. It
is the "servicePrincipalName" on his account. This is the same
"servicePrincipalName" as the SQL server. I research this before and it was
thought perhaps he had install SQL under this account or at one point SQL
was running under this account. When I removed it SQL basically shutdown.
I immediately put it back. Later someone told me I should have just stopped
and restarted the SQL service and it would have corrected the problem.
Can anyone give me any input on this? This is a major production server and
I want to be very careful.
Harrison MidkiffHi
I am not sure about what the cause of this problem is, but the solution may
be to create a new service account, set SQL Server to use this account,
stop/restart SQL server at a scheduled time. Changing the old service accoun
t
should then not effect the SQL Server.
John
"Harrison Midkiff" wrote:
> Hello:
> I am getting a KDC error on my domain controllers system log. The error i
s
> below.
> Event Source: KDC
> Event ID: 11
> Description:
> There are multiple accounts with name "MSSQLSvc/computer.domain.local:1433
"
> of type 10.
> I have tracked this down to one of the developers domain admin accounts.
It
> is the "servicePrincipalName" on his account. This is the same
> "servicePrincipalName" as the SQL server. I research this before and it w
as
> thought perhaps he had install SQL under this account or at one point SQL
> was running under this account. When I removed it SQL basically shutdown
.
> I immediately put it back. Later someone told me I should have just stopp
ed
> and restarted the SQL service and it would have corrected the problem.
> Can anyone give me any input on this? This is a major production server a
nd
> I want to be very careful.
> Harrison Midkiff
>
>|||Hi Harrison ,
This issue can occur if SQL accout is changed from Machine account to
service account. The SPN for the machine account was not removed from the
domain.
I suggest that you use LDIFDE to export the domain to text file and seach
for MSSQLSVC.
Syntax was:
LDIFDE -d DC=DEFIANT,DC=pactsolutions,DC=com, DC=au -f c:\export.txt
After you locate the duplicate SPN, you can use the Adsiedit.msc tool to go
to the object, view the duplicate SPN value, and remove the duplicate SPN
value. To determine which one was the one in current use, please delete one
of the
MSSQLSvc/uohssrv1.uohs.uottawa.ca:1433 SPNs, then restarted the SQL
service(s).
The SQL service will re-add the SPN on the currently in use service
account. If it is not readded, then the duplicate has been removed.
You can refer to the following article for more related information about
Kerberos Terminology and Service Principle Name (SPN).
811889.KB.EN-US HOW TO: Troubleshoot the "Cannot Generate SSPI Context"
Error Message
http://support.microsoft.com/defaul...KB;EN-US;811889
Hope this helps.
Best regards,
Vincent Xu
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 this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
is[vbcol=seagreen]
"MSSQLSvc/computer.domain.local:1433"[vbcol=seagreen]
It[vbcol=seagreen]
was[vbcol=seagreen]
shutdown.[vbcol=seagreen]
stopped[vbcol=seagreen]
and[vbcol=seagreen]
Subscribe to:
Posts (Atom)