First time poster...
Alright this is what I got...I am running MSSQL server 2000.
I have already restored a copy of the production db to our test sql server.
What I need to do is run some update scripts (from 3rd party vendor, going to newer version of their product). The database is about 60Gb and I filled up the transaction log (causing later updates to fail) while running one of their scripts that executed 194 routines of:
alter table Table_Name add New_Column int
GO
EXEC sp_bindefault mg_zero_dflt, [Table_Name.New_Column]
GO
update Table_Name set New_Column=1
GO
Will "sp_dboption 'dbname' 'trunc. log on chkpt' 'TRUE' " automatically truncate the transaction log when approaching the transaction log file limit while running these update scripts. Then when the updates are complete go ahead and "sp_dboption 'dbname' 'trunc. log on chkpt' 'FALSE' "
I'm not worried about logging the transactions during this process, I just don't want to have the transaction log fill up while running these scripts causing it to error out.
Or is there a way to make those changes using bcp?In the database properties window, go to the options tab and set your database recovery model to "simple". This should keep the log from filling up.
When you are finished, set the recovery model back to "full" and run an immediate backup.|||Thanks for the help. I remember seeing something about that and got sidetracked with the whole 'trunc. log on chkpt' idea. I'm in the process of finishing the scripts on the test server and I'll update with results. Thanks again!|||I finished running the scripts late yesterday without any hitches. That was exactly what I was looking for!
Showing posts with label time. Show all posts
Showing posts with label time. Show all posts
Friday, March 16, 2012
"The SQL Server cannot obtain a LOCK resource at this time" error
Hi there,
I am running on my machine the following SQL statements on my (local) SQL
Server:
SET NOCOUNT ON
SET ROWCOUNT 1500
delete_more:
delete table1 where field1 = '1' and field2 = '2'
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
The table has arround 5 million records and the DELETE statement would
delete arround 100000 records. I can see by running sp_lock that SQL is
escalating the locks to a table lock.
I am running in paralel the following:
SET ROWCOUNT 100
delete table1 where field1 = '3' and field2 = '2'
SET ROWCOUNT 0
This query is running fine, however the DELETE statement fails with the
error 1204:
"The SQL Server cannot obtain a LOCK resource at this time". Can somebody
shed some light for me here? Am I not able to run two DELETE statements is
paralel or am I doing something wrong?
I am running with the run_value for locks set to 0.
Thank you in advance.You mentioned there is already a table lock on that particular table, as it
is deleting millions of records. You probably need to wait for this
transaction to complete before you execute another delete statement on the
same table.
I suspect changing isolation level would solve your purpose.
"Andrei" wrote:
> Hi there,
> I am running on my machine the following SQL statements on my (local) SQL
> Server:
> SET NOCOUNT ON
> SET ROWCOUNT 1500
> delete_more:
> delete table1 where field1 = '1' and field2 = '2'
> IF @.@.ROWCOUNT > 0 GOTO delete_more
> SET ROWCOUNT 0
> The table has arround 5 million records and the DELETE statement would
> delete arround 100000 records. I can see by running sp_lock that SQL is
> escalating the locks to a table lock.
> I am running in paralel the following:
> SET ROWCOUNT 100
> delete table1 where field1 = '3' and field2 = '2'
> SET ROWCOUNT 0
> This query is running fine, however the DELETE statement fails with the
> error 1204:
> "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> shed some light for me here? Am I not able to run two DELETE statements is
> paralel or am I doing something wrong?
> I am running with the run_value for locks set to 0.
> Thank you in advance.|||What I want is to run several jobs concurrently, I do not want to wait for
one to finish in order to start the second one.
However I do not understand why I get the error, since the first statement
is suppose to delete 1500 records (so maximum 1500 key locks applied, if
there is no lock escalation) and the second statement is deleting 100
records. Am I missing something here?
"GYK" wrote:
> You mentioned there is already a table lock on that particular table, as it
> is deleting millions of records. You probably need to wait for this
> transaction to complete before you execute another delete statement on the
> same table.
> I suspect changing isolation level would solve your purpose.
> "Andrei" wrote:
> > Hi there,
> >
> > I am running on my machine the following SQL statements on my (local) SQL
> > Server:
> >
> > SET NOCOUNT ON
> >
> > SET ROWCOUNT 1500
> > delete_more:
> > delete table1 where field1 = '1' and field2 = '2'
> > IF @.@.ROWCOUNT > 0 GOTO delete_more
> > SET ROWCOUNT 0
> >
> > The table has arround 5 million records and the DELETE statement would
> > delete arround 100000 records. I can see by running sp_lock that SQL is
> > escalating the locks to a table lock.
> >
> > I am running in paralel the following:
> > SET ROWCOUNT 100
> > delete table1 where field1 = '3' and field2 = '2'
> > SET ROWCOUNT 0
> >
> > This query is running fine, however the DELETE statement fails with the
> > error 1204:
> > "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> > shed some light for me here? Am I not able to run two DELETE statements is
> > paralel or am I doing something wrong?
> >
> > I am running with the run_value for locks set to 0.
> >
> > Thank you in advance.
I am running on my machine the following SQL statements on my (local) SQL
Server:
SET NOCOUNT ON
SET ROWCOUNT 1500
delete_more:
delete table1 where field1 = '1' and field2 = '2'
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
The table has arround 5 million records and the DELETE statement would
delete arround 100000 records. I can see by running sp_lock that SQL is
escalating the locks to a table lock.
I am running in paralel the following:
SET ROWCOUNT 100
delete table1 where field1 = '3' and field2 = '2'
SET ROWCOUNT 0
This query is running fine, however the DELETE statement fails with the
error 1204:
"The SQL Server cannot obtain a LOCK resource at this time". Can somebody
shed some light for me here? Am I not able to run two DELETE statements is
paralel or am I doing something wrong?
I am running with the run_value for locks set to 0.
Thank you in advance.You mentioned there is already a table lock on that particular table, as it
is deleting millions of records. You probably need to wait for this
transaction to complete before you execute another delete statement on the
same table.
I suspect changing isolation level would solve your purpose.
"Andrei" wrote:
> Hi there,
> I am running on my machine the following SQL statements on my (local) SQL
> Server:
> SET NOCOUNT ON
> SET ROWCOUNT 1500
> delete_more:
> delete table1 where field1 = '1' and field2 = '2'
> IF @.@.ROWCOUNT > 0 GOTO delete_more
> SET ROWCOUNT 0
> The table has arround 5 million records and the DELETE statement would
> delete arround 100000 records. I can see by running sp_lock that SQL is
> escalating the locks to a table lock.
> I am running in paralel the following:
> SET ROWCOUNT 100
> delete table1 where field1 = '3' and field2 = '2'
> SET ROWCOUNT 0
> This query is running fine, however the DELETE statement fails with the
> error 1204:
> "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> shed some light for me here? Am I not able to run two DELETE statements is
> paralel or am I doing something wrong?
> I am running with the run_value for locks set to 0.
> Thank you in advance.|||What I want is to run several jobs concurrently, I do not want to wait for
one to finish in order to start the second one.
However I do not understand why I get the error, since the first statement
is suppose to delete 1500 records (so maximum 1500 key locks applied, if
there is no lock escalation) and the second statement is deleting 100
records. Am I missing something here?
"GYK" wrote:
> You mentioned there is already a table lock on that particular table, as it
> is deleting millions of records. You probably need to wait for this
> transaction to complete before you execute another delete statement on the
> same table.
> I suspect changing isolation level would solve your purpose.
> "Andrei" wrote:
> > Hi there,
> >
> > I am running on my machine the following SQL statements on my (local) SQL
> > Server:
> >
> > SET NOCOUNT ON
> >
> > SET ROWCOUNT 1500
> > delete_more:
> > delete table1 where field1 = '1' and field2 = '2'
> > IF @.@.ROWCOUNT > 0 GOTO delete_more
> > SET ROWCOUNT 0
> >
> > The table has arround 5 million records and the DELETE statement would
> > delete arround 100000 records. I can see by running sp_lock that SQL is
> > escalating the locks to a table lock.
> >
> > I am running in paralel the following:
> > SET ROWCOUNT 100
> > delete table1 where field1 = '3' and field2 = '2'
> > SET ROWCOUNT 0
> >
> > This query is running fine, however the DELETE statement fails with the
> > error 1204:
> > "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> > shed some light for me here? Am I not able to run two DELETE statements is
> > paralel or am I doing something wrong?
> >
> > I am running with the run_value for locks set to 0.
> >
> > Thank you in advance.
"Text column data incomplete" error during replication
I'm doing a transactional replication for the first time between 2
servers.
After 14 minutes of running (according to the logs), the replicaton
crashes. This is what I find in the log file generated by the
distribution agent. It starts bulk copying data from many tables until
it finally crashes on the first copy.
Any ideas why?
What does it mean 'Text column data incomplete'.
Table Parcel has 2 text columns that can be NULL.
Also, at the time the replication was taking place, it is possible
other transactions might have been being added/deleted from
Parcel......could this produce this type of error?
Here goes the erro log:
Bulk copying data into table 'Parcel'
:
Bulk copying data into table 'Item Specific Cost Factor'
:
Bulk copying data into table 'Item Appraisal Details'
:
:
Agent message code 20037. The process could not bulk copy into table
'"Parcel"'.
[6/16/2004 10:37:08 AM]SERVER.distribution: {call
sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?,
414, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
Transaction Seqno = 0004158900000066000100000001, Command ID = 414
Message: Replication-Replication Distribution Subsystem: agent
SERVER-Acadia_Final_Assessor-Acadia_Final_Assessor-68.216.171.51-4
failed.
The process could not bulk copy into table '"Parcel"'.[6/16/2004
10:37:08 AM]SERVER.distribution: {call sp_MSadd_repl_alert(3, 4, 9,
14151, ?, 414, N'SERVER', N'Acadia_Final_Assessor', N'68.216.171.51',
N'TestDB', ?)}
ErrorId = 9, SourceTypeId = 1
ErrorCode = ''
ErrorText = 'select * from "Parcel Item" where 1 = 2
'
[6/16/2004 10:37:08 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 1, ?, N'', ?)}
Category:COMMAND
Source: Failed Command
Number:
Message: select * from "Parcel Item" where 1 = 2
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: 68.216.171.51
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
[6/16/2004 10:37:09 AM]68.216.171.51.TestDB: exec
dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
N'Acadia_Final_Assessor', 0, 6, N'The process could not bulk copy into
table ''"Parcel"''.'
Disconnecting from Subscriber '......'
Disconnecting from Distributor 'SERVER'
Disconnecting from Distributor History 'SERVER'
is the database collations on the subscriber and publisher the same?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Rocio" <rocio.katsanis@.softwareservices.net> wrote in message
news:7b914006.0406161119.2936cea7@.posting.google.c om...
> I'm doing a transactional replication for the first time between 2
> servers.
> After 14 minutes of running (according to the logs), the replicaton
> crashes. This is what I find in the log file generated by the
> distribution agent. It starts bulk copying data from many tables until
> it finally crashes on the first copy.
> Any ideas why?
> What does it mean 'Text column data incomplete'.
> Table Parcel has 2 text columns that can be NULL.
> Also, at the time the replication was taking place, it is possible
> other transactions might have been being added/deleted from
> Parcel......could this produce this type of error?
> Here goes the erro log:
> Bulk copying data into table 'Parcel'
> :
> Bulk copying data into table 'Item Specific Cost Factor'
> :
> Bulk copying data into table 'Item Appraisal Details'
> :
> :
> Agent message code 20037. The process could not bulk copy into table
> '"Parcel"'.
> [6/16/2004 10:37:08 AM]SERVER.distribution: {call
> sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?,
> 414, 0x01, 0x01)}
> Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
> Transaction Seqno = 0004158900000066000100000001, Command ID = 414
> Message: Replication-Replication Distribution Subsystem: agent
> SERVER-Acadia_Final_Assessor-Acadia_Final_Assessor-68.216.171.51-4
> failed.
> The process could not bulk copy into table '"Parcel"'.[6/16/2004
> 10:37:08 AM]SERVER.distribution: {call sp_MSadd_repl_alert(3, 4, 9,
> 14151, ?, 414, N'SERVER', N'Acadia_Final_Assessor', N'68.216.171.51',
> N'TestDB', ?)}
> ErrorId = 9, SourceTypeId = 1
> ErrorCode = ''
> ErrorText = 'select * from "Parcel Item" where 1 = 2
> '
> [6/16/2004 10:37:08 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 1, ?, N'', ?)}
> Category:COMMAND
> Source: Failed Command
> Number:
> Message: select * from "Parcel Item" where 1 = 2
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: 68.216.171.51
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> [6/16/2004 10:37:09 AM]68.216.171.51.TestDB: exec
> dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
> N'Acadia_Final_Assessor', 0, 6, N'The process could not bulk copy into
> table ''"Parcel"''.'
> Disconnecting from Subscriber '......'
> Disconnecting from Distributor 'SERVER'
> Disconnecting from Distributor History 'SERVER'
|||Hi Hillary,
I treid the same replication 3 more times, and the erro I got on those 3
times was different than the one I published here. Now the error is:
Category:SQLSERVER
Source: <IP of subscriber...>
Number: 2812
Message: Could not find stored procedure 'sp_MSupd_Entity Address'.
[6/16/2004 8:24:39 PM]68.216.171.51.TestDB: exec
dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
N'Acadia_Final_Assessor', 0, 6, N'Error at parameter 199 during
datastream processing of parameterized command.'
Disconnecting from Subscriber '<IP of subscriber...>'
Disconnecting from Distributor 'SERVER'
Disconnecting from Distributor History 'SERVER'
If I looked carefully at this log file, and it seems to me that such
stored procedure is somehow created:
[6/16/2004 8:01:12 PM]<IP of subscriber...>.TestDB: create procedure
"[sp_MSupd_Entity Address]"
@.c1 int,@.c2 int,@.c3 int,@.c4 int,@.c5 int,@.c6 char(4),@.c7 int,@.c8 bit,@.c9
int,@.c10 varchar(500),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "Entity Address" set
"Address ID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
"Address ID" end
,"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"Entity ID" end
,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
ID" end
,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"State ID" end
,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "Postal Code ID" end
,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"Zip Last 4" end
,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"Country ID" end
,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
else "Primary Address" end
,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
else "Address Type ID" end
,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"Address Lines" end
where "Address ID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "Entity Address" set
"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"Entity ID" end
,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
ID" end
,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"State ID" end
,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "Postal Code ID" end
,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"Zip Last 4" end
,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"Country ID" end
,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
else "Primary Address" end
,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
else "Address Type ID" end
,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"Address Lines" end
where "Address ID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
however, if I look at TestDB in my subscriber, I can find
[sp_MSupd_Entity Address] but not sp_MSupd_Entity Address, note one is
with brackets, the other not.
why 2 sp's anyway (with & without brackets)?
how can I control the creation of this procedure? I looked at the
properties tab of this publictaion at the Publisher, under Article
Entity Address, and make sure all the options are selected (e.g. Replace
INSERT commands with this stored procedure, etc).
The only option I could not access is the one at the very bottom that
causes replication to use the columns name. It was just enabled.
Any ideas?
btw: your book about Transactional & Snapshot replication looks good, I
haven't found a good book on replication yet! Is it out already?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||on your publisher do this
sp_scriptpublicationcustomprocs 'publicationName'
then in your results pan, copy the results and then paste them into another ISQLW window which you open into your subsciption database. then execute this script there.
Restart your distribution agent.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Rocio Katsanis" wrote:
> Hi Hillary,
> I treid the same replication 3 more times, and the erro I got on those 3
> times was different than the one I published here. Now the error is:
> Category:SQLSERVER
> Source: <IP of subscriber...>
> Number: 2812
> Message: Could not find stored procedure 'sp_MSupd_Entity Address'.
> [6/16/2004 8:24:39 PM]68.216.171.51.TestDB: exec
> dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
> N'Acadia_Final_Assessor', 0, 6, N'Error at parameter 199 during
> datastream processing of parameterized command.'
> Disconnecting from Subscriber '<IP of subscriber...>'
> Disconnecting from Distributor 'SERVER'
> Disconnecting from Distributor History 'SERVER'
> If I looked carefully at this log file, and it seems to me that such
> stored procedure is somehow created:
> [6/16/2004 8:01:12 PM]<IP of subscriber...>.TestDB: create procedure
> "[sp_MSupd_Entity Address]"
> @.c1 int,@.c2 int,@.c3 int,@.c4 int,@.c5 int,@.c6 char(4),@.c7 int,@.c8 bit,@.c9
> int,@.c10 varchar(500),@.pkc1 int
> ,@.bitmap binary(2)
> as
> if substring(@.bitmap,1,1) & 1 = 1
> begin
> update "Entity Address" set
> "Address ID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
> "Address ID" end
> ,"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "Entity ID" end
> ,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
> ID" end
> ,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "State ID" end
> ,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "Postal Code ID" end
> ,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "Zip Last 4" end
> ,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "Country ID" end
> ,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
> else "Primary Address" end
> ,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
> else "Address Type ID" end
> ,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "Address Lines" end
> where "Address ID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> else
> begin
> update "Entity Address" set
> "Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "Entity ID" end
> ,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
> ID" end
> ,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "State ID" end
> ,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "Postal Code ID" end
> ,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "Zip Last 4" end
> ,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "Country ID" end
> ,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
> else "Primary Address" end
> ,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
> else "Address Type ID" end
> ,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "Address Lines" end
> where "Address ID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> however, if I look at TestDB in my subscriber, I can find
> [sp_MSupd_Entity Address] but not sp_MSupd_Entity Address, note one is
> with brackets, the other not.
> why 2 sp's anyway (with & without brackets)?
> how can I control the creation of this procedure? I looked at the
> properties tab of this publictaion at the Publisher, under Article
> Entity Address, and make sure all the options are selected (e.g. Replace
> INSERT commands with this stored procedure, etc).
> The only option I could not access is the one at the very bottom that
> causes replication to use the columns name. It was just enabled.
> Any ideas?
> btw: your book about Transactional & Snapshot replication looks good, I
> haven't found a good book on replication yet! Is it out already?
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
>
servers.
After 14 minutes of running (according to the logs), the replicaton
crashes. This is what I find in the log file generated by the
distribution agent. It starts bulk copying data from many tables until
it finally crashes on the first copy.
Any ideas why?
What does it mean 'Text column data incomplete'.
Table Parcel has 2 text columns that can be NULL.
Also, at the time the replication was taking place, it is possible
other transactions might have been being added/deleted from
Parcel......could this produce this type of error?
Here goes the erro log:
Bulk copying data into table 'Parcel'
:
Bulk copying data into table 'Item Specific Cost Factor'
:
Bulk copying data into table 'Item Appraisal Details'
:
:
Agent message code 20037. The process could not bulk copy into table
'"Parcel"'.
[6/16/2004 10:37:08 AM]SERVER.distribution: {call
sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?,
414, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
Transaction Seqno = 0004158900000066000100000001, Command ID = 414
Message: Replication-Replication Distribution Subsystem: agent
SERVER-Acadia_Final_Assessor-Acadia_Final_Assessor-68.216.171.51-4
failed.
The process could not bulk copy into table '"Parcel"'.[6/16/2004
10:37:08 AM]SERVER.distribution: {call sp_MSadd_repl_alert(3, 4, 9,
14151, ?, 414, N'SERVER', N'Acadia_Final_Assessor', N'68.216.171.51',
N'TestDB', ?)}
ErrorId = 9, SourceTypeId = 1
ErrorCode = ''
ErrorText = 'select * from "Parcel Item" where 1 = 2
'
[6/16/2004 10:37:08 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 1, ?, N'', ?)}
Category:COMMAND
Source: Failed Command
Number:
Message: select * from "Parcel Item" where 1 = 2
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: 68.216.171.51
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
[6/16/2004 10:37:09 AM]68.216.171.51.TestDB: exec
dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
N'Acadia_Final_Assessor', 0, 6, N'The process could not bulk copy into
table ''"Parcel"''.'
Disconnecting from Subscriber '......'
Disconnecting from Distributor 'SERVER'
Disconnecting from Distributor History 'SERVER'
is the database collations on the subscriber and publisher the same?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Rocio" <rocio.katsanis@.softwareservices.net> wrote in message
news:7b914006.0406161119.2936cea7@.posting.google.c om...
> I'm doing a transactional replication for the first time between 2
> servers.
> After 14 minutes of running (according to the logs), the replicaton
> crashes. This is what I find in the log file generated by the
> distribution agent. It starts bulk copying data from many tables until
> it finally crashes on the first copy.
> Any ideas why?
> What does it mean 'Text column data incomplete'.
> Table Parcel has 2 text columns that can be NULL.
> Also, at the time the replication was taking place, it is possible
> other transactions might have been being added/deleted from
> Parcel......could this produce this type of error?
> Here goes the erro log:
> Bulk copying data into table 'Parcel'
> :
> Bulk copying data into table 'Item Specific Cost Factor'
> :
> Bulk copying data into table 'Item Appraisal Details'
> :
> :
> Agent message code 20037. The process could not bulk copy into table
> '"Parcel"'.
> [6/16/2004 10:37:08 AM]SERVER.distribution: {call
> sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?,
> 414, 0x01, 0x01)}
> Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
> Transaction Seqno = 0004158900000066000100000001, Command ID = 414
> Message: Replication-Replication Distribution Subsystem: agent
> SERVER-Acadia_Final_Assessor-Acadia_Final_Assessor-68.216.171.51-4
> failed.
> The process could not bulk copy into table '"Parcel"'.[6/16/2004
> 10:37:08 AM]SERVER.distribution: {call sp_MSadd_repl_alert(3, 4, 9,
> 14151, ?, 414, N'SERVER', N'Acadia_Final_Assessor', N'68.216.171.51',
> N'TestDB', ?)}
> ErrorId = 9, SourceTypeId = 1
> ErrorCode = ''
> ErrorText = 'select * from "Parcel Item" where 1 = 2
> '
> [6/16/2004 10:37:08 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 1, ?, N'', ?)}
> Category:COMMAND
> Source: Failed Command
> Number:
> Message: select * from "Parcel Item" where 1 = 2
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: 68.216.171.51
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> [6/16/2004 10:37:09 AM]68.216.171.51.TestDB: exec
> dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
> N'Acadia_Final_Assessor', 0, 6, N'The process could not bulk copy into
> table ''"Parcel"''.'
> Disconnecting from Subscriber '......'
> Disconnecting from Distributor 'SERVER'
> Disconnecting from Distributor History 'SERVER'
|||Hi Hillary,
I treid the same replication 3 more times, and the erro I got on those 3
times was different than the one I published here. Now the error is:
Category:SQLSERVER
Source: <IP of subscriber...>
Number: 2812
Message: Could not find stored procedure 'sp_MSupd_Entity Address'.
[6/16/2004 8:24:39 PM]68.216.171.51.TestDB: exec
dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
N'Acadia_Final_Assessor', 0, 6, N'Error at parameter 199 during
datastream processing of parameterized command.'
Disconnecting from Subscriber '<IP of subscriber...>'
Disconnecting from Distributor 'SERVER'
Disconnecting from Distributor History 'SERVER'
If I looked carefully at this log file, and it seems to me that such
stored procedure is somehow created:
[6/16/2004 8:01:12 PM]<IP of subscriber...>.TestDB: create procedure
"[sp_MSupd_Entity Address]"
@.c1 int,@.c2 int,@.c3 int,@.c4 int,@.c5 int,@.c6 char(4),@.c7 int,@.c8 bit,@.c9
int,@.c10 varchar(500),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "Entity Address" set
"Address ID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
"Address ID" end
,"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"Entity ID" end
,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
ID" end
,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"State ID" end
,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "Postal Code ID" end
,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"Zip Last 4" end
,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"Country ID" end
,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
else "Primary Address" end
,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
else "Address Type ID" end
,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"Address Lines" end
where "Address ID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "Entity Address" set
"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"Entity ID" end
,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
ID" end
,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"State ID" end
,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "Postal Code ID" end
,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"Zip Last 4" end
,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"Country ID" end
,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
else "Primary Address" end
,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
else "Address Type ID" end
,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"Address Lines" end
where "Address ID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
however, if I look at TestDB in my subscriber, I can find
[sp_MSupd_Entity Address] but not sp_MSupd_Entity Address, note one is
with brackets, the other not.
why 2 sp's anyway (with & without brackets)?
how can I control the creation of this procedure? I looked at the
properties tab of this publictaion at the Publisher, under Article
Entity Address, and make sure all the options are selected (e.g. Replace
INSERT commands with this stored procedure, etc).
The only option I could not access is the one at the very bottom that
causes replication to use the columns name. It was just enabled.
Any ideas?
btw: your book about Transactional & Snapshot replication looks good, I
haven't found a good book on replication yet! Is it out already?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||on your publisher do this
sp_scriptpublicationcustomprocs 'publicationName'
then in your results pan, copy the results and then paste them into another ISQLW window which you open into your subsciption database. then execute this script there.
Restart your distribution agent.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Rocio Katsanis" wrote:
> Hi Hillary,
> I treid the same replication 3 more times, and the erro I got on those 3
> times was different than the one I published here. Now the error is:
> Category:SQLSERVER
> Source: <IP of subscriber...>
> Number: 2812
> Message: Could not find stored procedure 'sp_MSupd_Entity Address'.
> [6/16/2004 8:24:39 PM]68.216.171.51.TestDB: exec
> dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
> N'Acadia_Final_Assessor', 0, 6, N'Error at parameter 199 during
> datastream processing of parameterized command.'
> Disconnecting from Subscriber '<IP of subscriber...>'
> Disconnecting from Distributor 'SERVER'
> Disconnecting from Distributor History 'SERVER'
> If I looked carefully at this log file, and it seems to me that such
> stored procedure is somehow created:
> [6/16/2004 8:01:12 PM]<IP of subscriber...>.TestDB: create procedure
> "[sp_MSupd_Entity Address]"
> @.c1 int,@.c2 int,@.c3 int,@.c4 int,@.c5 int,@.c6 char(4),@.c7 int,@.c8 bit,@.c9
> int,@.c10 varchar(500),@.pkc1 int
> ,@.bitmap binary(2)
> as
> if substring(@.bitmap,1,1) & 1 = 1
> begin
> update "Entity Address" set
> "Address ID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
> "Address ID" end
> ,"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "Entity ID" end
> ,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
> ID" end
> ,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "State ID" end
> ,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "Postal Code ID" end
> ,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "Zip Last 4" end
> ,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "Country ID" end
> ,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
> else "Primary Address" end
> ,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
> else "Address Type ID" end
> ,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "Address Lines" end
> where "Address ID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> else
> begin
> update "Entity Address" set
> "Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "Entity ID" end
> ,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
> ID" end
> ,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "State ID" end
> ,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "Postal Code ID" end
> ,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "Zip Last 4" end
> ,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "Country ID" end
> ,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
> else "Primary Address" end
> ,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
> else "Address Type ID" end
> ,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "Address Lines" end
> where "Address ID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> however, if I look at TestDB in my subscriber, I can find
> [sp_MSupd_Entity Address] but not sp_MSupd_Entity Address, note one is
> with brackets, the other not.
> why 2 sp's anyway (with & without brackets)?
> how can I control the creation of this procedure? I looked at the
> properties tab of this publictaion at the Publisher, under Article
> Entity Address, and make sure all the options are selected (e.g. Replace
> INSERT commands with this stored procedure, etc).
> The only option I could not access is the one at the very bottom that
> causes replication to use the columns name. It was just enabled.
> Any ideas?
> btw: your book about Transactional & Snapshot replication looks good, I
> haven't found a good book on replication yet! Is it out already?
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
>
Sunday, March 11, 2012
"Server Application Unavailable" error on generating PDF reports
Hi,
I'm using ASP.NET with SQL Reporting Service to generate PDF reports.
Most of the time it works well.
But sometimes PDF doesn't come out (esp. if the report is complicated),
with the error message at below.
I use 2 separate servers for Web Server / Reporting Service.
And i suspect a cause is not enough memory?
Thanks,
Azul
-- error message --
Server Application Unavailable
The web application you are attempting to access on this web server is
currently unavailable. Please hit the "Refresh" button in your web browser
to retry your request.
Administrator Note: An error message detailing the cause of this specific
request failure can be found in the application event log of the web server.
Please review this log entry to discover what caused this error to occur.
--.: at
System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(...)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(...)
at LYNX.ReportServices.ReportingService.Render(...) in ...
at LYNX.BLL.reportServices.getCustomReport(...) in ...
at LYNX.BLL.reportServices.getPDFReport(...) in ...
at LYNX.Components.Report.RunningAverage.PreviewGraph() in ...
-- end of error message --I found the solution from another post in this newsgroup!
Just downloaded and installed Reporting Services SP1, everything works fine
Although the earlier post talked about long long table,
the solution also applies in my case of generating complicated graphs.
"Azul" <a@.b.c> wrote in message
news:ebcX1F1qEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm using ASP.NET with SQL Reporting Service to generate PDF reports.
> Most of the time it works well.
> But sometimes PDF doesn't come out (esp. if the report is complicated),
> with the error message at below.
> I use 2 separate servers for Web Server / Reporting Service.
> And i suspect a cause is not enough memory?
> Thanks,
> Azul
>
> -- error message --
> Server Application Unavailable
> The web application you are attempting to access on this web server is
> currently unavailable. Please hit the "Refresh" button in your web
> browser to retry your request.
> Administrator Note: An error message detailing the cause of this specific
> request failure can be found in the application event log of the web
> server. Please review this log entry to discover what caused this error to
> occur.
> --.: at
> System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(...)
> at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(...)
> at LYNX.ReportServices.ReportingService.Render(...) in ...
> at LYNX.BLL.reportServices.getCustomReport(...) in ...
> at LYNX.BLL.reportServices.getPDFReport(...) in ...
> at LYNX.Components.Report.RunningAverage.PreviewGraph() in ...
> -- end of error message --
>
I'm using ASP.NET with SQL Reporting Service to generate PDF reports.
Most of the time it works well.
But sometimes PDF doesn't come out (esp. if the report is complicated),
with the error message at below.
I use 2 separate servers for Web Server / Reporting Service.
And i suspect a cause is not enough memory?
Thanks,
Azul
-- error message --
Server Application Unavailable
The web application you are attempting to access on this web server is
currently unavailable. Please hit the "Refresh" button in your web browser
to retry your request.
Administrator Note: An error message detailing the cause of this specific
request failure can be found in the application event log of the web server.
Please review this log entry to discover what caused this error to occur.
--.: at
System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(...)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(...)
at LYNX.ReportServices.ReportingService.Render(...) in ...
at LYNX.BLL.reportServices.getCustomReport(...) in ...
at LYNX.BLL.reportServices.getPDFReport(...) in ...
at LYNX.Components.Report.RunningAverage.PreviewGraph() in ...
-- end of error message --I found the solution from another post in this newsgroup!
Just downloaded and installed Reporting Services SP1, everything works fine
Although the earlier post talked about long long table,
the solution also applies in my case of generating complicated graphs.
"Azul" <a@.b.c> wrote in message
news:ebcX1F1qEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm using ASP.NET with SQL Reporting Service to generate PDF reports.
> Most of the time it works well.
> But sometimes PDF doesn't come out (esp. if the report is complicated),
> with the error message at below.
> I use 2 separate servers for Web Server / Reporting Service.
> And i suspect a cause is not enough memory?
> Thanks,
> Azul
>
> -- error message --
> Server Application Unavailable
> The web application you are attempting to access on this web server is
> currently unavailable. Please hit the "Refresh" button in your web
> browser to retry your request.
> Administrator Note: An error message detailing the cause of this specific
> request failure can be found in the application event log of the web
> server. Please review this log entry to discover what caused this error to
> occur.
> --.: at
> System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(...)
> at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(...)
> at LYNX.ReportServices.ReportingService.Render(...) in ...
> at LYNX.BLL.reportServices.getCustomReport(...) in ...
> at LYNX.BLL.reportServices.getPDFReport(...) in ...
> at LYNX.Components.Report.RunningAverage.PreviewGraph() in ...
> -- end of error message --
>
Thursday, March 8, 2012
"second newest" record?
I've seen a number of solution to get the "newest record" from a time series
-- and extensively benchmarked them on our 2 million+ row prices database. In
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum id
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
Maury
Maury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time series
>-- and extensively benchmarked them on our 2 million+ row prices database. In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>
|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury
|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can't
seem to find it. It might be possible to compare the side (buy/sell) with the
quantity or something, but that seems pretty nasty too.
|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true:
>they DO sometimes put the correction as the second record, and sometimes the
>third. I've looked through the data for some sort of determinant, but I can't
>seem to find it. It might be possible to compare the side (buy/sell) with the
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
-- and extensively benchmarked them on our 2 million+ row prices database. In
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum id
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
Maury
Maury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time series
>-- and extensively benchmarked them on our 2 million+ row prices database. In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>
|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury
|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can't
seem to find it. It might be possible to compare the side (buy/sell) with the
quantity or something, but that seems pretty nasty too.
|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true:
>they DO sometimes put the correction as the second record, and sometimes the
>third. I've looked through the data for some sort of determinant, but I can't
>seem to find it. It might be possible to compare the side (buy/sell) with the
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
"second newest" record?
I've seen a number of solution to get the "newest record" from a time series
-- and extensively benchmarked them on our 2 million+ row prices database. In
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum id
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
MauryMaury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time series
>-- and extensively benchmarked them on our 2 million+ row prices database. In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can't
seem to find it. It might be possible to compare the side (buy/sell) with the
quantity or something, but that seems pretty nasty too.|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true:
>they DO sometimes put the correction as the second record, and sometimes the
>third. I've looked through the data for some sort of determinant, but I can't
>seem to find it. It might be possible to compare the side (buy/sell) with the
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
-- and extensively benchmarked them on our 2 million+ row prices database. In
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum id
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
MauryMaury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time series
>-- and extensively benchmarked them on our 2 million+ row prices database. In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Grrr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can't
seem to find it. It might be possible to compare the side (buy/sell) with the
quantity or something, but that seems pretty nasty too.|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true:
>they DO sometimes put the correction as the second record, and sometimes the
>third. I've looked through the data for some sort of determinant, but I can't
>seem to find it. It might be possible to compare the side (buy/sell) with the
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
"second newest" record?
I've seen a number of solution to get the "newest record" from a time series
-- and extensively benchmarked them on our 2 million+ row prices database. I
n
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum i
d
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grr
r.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
MauryMaury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time serie
s
>-- and extensively benchmarked them on our 2 million+ row prices database.
In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum
id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Gr
rr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can'
t
seem to find it. It might be possible to compare the side (buy/sell) with th
e
quantity or something, but that seems pretty nasty too.|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true
:
>they DO sometimes put the correction as the second record, and sometimes th
e
>third. I've looked through the data for some sort of determinant, but I can
't
>seem to find it. It might be possible to compare the side (buy/sell) with t
he
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
-- and extensively benchmarked them on our 2 million+ row prices database. I
n
a similar task I'm trying to get the "right" record from a series of
transactions that include cancel/corrections.
For instance, let's say the operator fat-fingers an order for IBM and gets
ten times too much. We'll notice the error, and they'll issue a
cancel/correct, like this...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy -50000 IBM #1028A
buy 5000 IBM #1028A
So in this case they cancel the original mistake, then send out the
correction. When I import these into our DB I give them row numbers and
timestamp them. In order to get the "right record" I subselect the maximum i
d
for all orders with the same ID...
SELECT *
FROM import
INNER JOIN
(SELECT MAX(id) AS MAXID
FROM import
GROUP BY OrderNumber) o
ON id= o.MAXID
Ok, so now what if they actually report it this way instead...
DAY 1: buy 50000 IBM #1028A
DAY 2: buy 5000 IBM #1028A
buy -50000 IBM #1028A
Yes, that's right, they don't report cancel/correct, but correct/cancel. Grr
r.
So how do I adjust my SQL to get the "second most maximum ID"? I can't
figure this out.
MauryMaury,
Aren't you saying that for some orders the order you want is the last,
and for others it's the next-to-last? Selecting the second-largest ID
for every order doesn't sound likely to solve your problem. While you
can do that, is there another way you can describe the row you want,
such as the most recent row with a positive number of shares, or the
result of adding up all shares in an order?
Anyway, you can get the second largest id for each OrderNumber like this
(untested):
select * from import as I1
where id = (
select top 1 T.id
from (
select top 2 I2.id
from import as I2
where I2.OrderNumber = I1.OrderNumber
order by id desc
) T
order by id
)
Steve Kass
Drew University
Maury Markowitz wrote:
>I've seen a number of solution to get the "newest record" from a time serie
s
>-- and extensively benchmarked them on our 2 million+ row prices database.
In
>a similar task I'm trying to get the "right" record from a series of
>transactions that include cancel/corrections.
>For instance, let's say the operator fat-fingers an order for IBM and gets
>ten times too much. We'll notice the error, and they'll issue a
>cancel/correct, like this...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy -50000 IBM #1028A
> buy 5000 IBM #1028A
>So in this case they cancel the original mistake, then send out the
>correction. When I import these into our DB I give them row numbers and
>timestamp them. In order to get the "right record" I subselect the maximum
id
>for all orders with the same ID...
>SELECT *
>FROM import
>INNER JOIN
> (SELECT MAX(id) AS MAXID
> FROM import
> GROUP BY OrderNumber) o
>ON id= o.MAXID
>Ok, so now what if they actually report it this way instead...
>DAY 1: buy 50000 IBM #1028A
>DAY 2: buy 5000 IBM #1028A
> buy -50000 IBM #1028A
>Yes, that's right, they don't report cancel/correct, but correct/cancel. Gr
rr.
>So how do I adjust my SQL to get the "second most maximum ID"? I can't
>figure this out.
>Maury
>|||"Steve Kass" wrote:
> Aren't you saying that for some orders the order you want is the last,
> and for others it's the next-to-last?
No, for some brokers (ie, the smart ones) it's the last record, but in this
case the "correct" record will ALWAYS be the second-to-last.
> can do that, is there another way you can describe the row you want,
> such as the most recent row with a positive number of shares, or the
> result of adding up all shares in an order?
I considered the last idea, but it only works for quantity. Other changes,
like the security name or price, can't be added up.
I'm going to try your SQL suggestion now!
Maury|||"Steve Kass" wrote:
Your SQL worked great Steve. Sadly your other comment turned out to be true:
they DO sometimes put the correction as the second record, and sometimes the
third. I've looked through the data for some sort of determinant, but I can'
t
seem to find it. It might be possible to compare the side (buy/sell) with th
e
quantity or something, but that seems pretty nasty too.|||On Thu, 6 Jan 2005 11:21:02 -0800, Maury Markowitz wrote:
>"Steve Kass" wrote:
>Your SQL worked great Steve. Sadly your other comment turned out to be true
:
>they DO sometimes put the correction as the second record, and sometimes th
e
>third. I've looked through the data for some sort of determinant, but I can
't
>seem to find it. It might be possible to compare the side (buy/sell) with t
he
>quantity or something, but that seems pretty nasty too.
Hi Maury,
Sorry to hear about the mess you're finding yourself in. I don't think I
can help you sort this out (at least not based on the info you've posted
so far), but once you have this nder control, I suggest you prevent this
from happenning again by adding one column:
ALTER TABLE import
ADD COLUMN correction_to INT
DEFAULT NULL
REFERENCES import(ID)
(Change the datatype from INT to the datatype of your import.ID column)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
"sa" Login locking resources?
I see lock timeouts in my trace and the object_id is for the same table
every time.
However, something does not seem right - the timeouts all show login = "sa".
Also, when I did a trace of lock acquired I see a lot of locks for that
table being acquired by login = "sa". There are locks being acquired by the
user being used for the connection by the application. But I cannot figure
out why "sa" is locking anything.
Can anyone out there help me?
TIA,
RohanDo you have any scheduled jobs that could be running in the context of 'sa'.
.?
"Rohan Hattangdi" wrote:
> I see lock timeouts in my trace and the object_id is for the same table
> every time.
> However, something does not seem right - the timeouts all show login = "sa
".
> Also, when I did a trace of lock acquired I see a lot of locks for that
> table being acquired by login = "sa". There are locks being acquired by th
e
> user being used for the connection by the application. But I cannot figure
> out why "sa" is locking anything.
> Can anyone out there help me?
> TIA,
> Rohan
>
>|||No ...
This is a load-test server and the only three jobs on there are for backup,
integrity checks and optimizations - they run at 12am, 1am and 2am
respectively. So it could not be jobs.
Any other ideas?
TIA,
Rohan
"Alien2_51" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
news:E4FED000-D1E9-4D15-8118-8E751AA94CD9@.microsoft.com...
> Do you have any scheduled jobs that could be running in the context of
> 'sa'...?
> "Rohan Hattangdi" wrote:
>|||Rohan Hattangdi wrote:
> No ...
> This is a load-test server and the only three jobs on there are for
> backup, integrity checks and optimizations - they run at 12am, 1am
> and 2am respectively. So it could not be jobs.
> Any other ideas?
> TIA,
> Rohan
>
Change the "sa" password and see if the problem continues. If someone is
using the "sa" account without you knowing about it or an application
somewhere hard-codes the account login, you're likely to get a phone
call.
The other thing you can do is check the net_address in the sysprocesses
table and call your network admin and have him/her tell you whose PC the
MAC address belongs to.
David Gugick
Imceda Software
www.imceda.com|||That is a good idea.
Let me see what happens now.
Thank you,
Rohan
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OUDf6ylKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Rohan Hattangdi wrote:
> Change the "sa" password and see if the problem continues. If someone is
> using the "sa" account without you knowing about it or an application
> somewhere hard-codes the account login, you're likely to get a phone call.
> The other thing you can do is check the net_address in the sysprocesses
> table and call your network admin and have him/her tell you whose PC the
> MAC address belongs to.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||I spoke too soon.
Changing the pwd seems to have had no effect.
This is very strange. Why would it say "sa"? If a lock timeout event
occurs on the connection made by the application, should not the login name
be that used by the application to connect to SQL Server?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OUDf6ylKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Rohan Hattangdi wrote:
> Change the "sa" password and see if the problem continues. If someone is
> using the "sa" account without you knowing about it or an application
> somewhere hard-codes the account login, you're likely to get a phone call.
> The other thing you can do is check the net_address in the sysprocesses
> table and call your network admin and have him/her tell you whose PC the
> MAC address belongs to.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Rohan Hattangdi wrote:
> I spoke too soon.
> Changing the pwd seems to have had no effect.
> This is very strange. Why would it say "sa"? If a lock timeout event
> occurs on the connection made by the application, should not the
> login name be that used by the application to connect to SQL Server?
Were those "sa" logins logged out before the password change? If not, I
think they woudl stay connected and would just have a problem the next
time around. But, yes, in most cases you should see the login name used
in the lock timeout event. Unless you are using Application Roles.
Run sp_helprole and see if any are Application Roles.
David Gugick
Imceda Software
www.imceda.com
every time.
However, something does not seem right - the timeouts all show login = "sa".
Also, when I did a trace of lock acquired I see a lot of locks for that
table being acquired by login = "sa". There are locks being acquired by the
user being used for the connection by the application. But I cannot figure
out why "sa" is locking anything.
Can anyone out there help me?
TIA,
RohanDo you have any scheduled jobs that could be running in the context of 'sa'.
.?
"Rohan Hattangdi" wrote:
> I see lock timeouts in my trace and the object_id is for the same table
> every time.
> However, something does not seem right - the timeouts all show login = "sa
".
> Also, when I did a trace of lock acquired I see a lot of locks for that
> table being acquired by login = "sa". There are locks being acquired by th
e
> user being used for the connection by the application. But I cannot figure
> out why "sa" is locking anything.
> Can anyone out there help me?
> TIA,
> Rohan
>
>|||No ...
This is a load-test server and the only three jobs on there are for backup,
integrity checks and optimizations - they run at 12am, 1am and 2am
respectively. So it could not be jobs.
Any other ideas?
TIA,
Rohan
"Alien2_51" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
news:E4FED000-D1E9-4D15-8118-8E751AA94CD9@.microsoft.com...
> Do you have any scheduled jobs that could be running in the context of
> 'sa'...?
> "Rohan Hattangdi" wrote:
>|||Rohan Hattangdi wrote:
> No ...
> This is a load-test server and the only three jobs on there are for
> backup, integrity checks and optimizations - they run at 12am, 1am
> and 2am respectively. So it could not be jobs.
> Any other ideas?
> TIA,
> Rohan
>
Change the "sa" password and see if the problem continues. If someone is
using the "sa" account without you knowing about it or an application
somewhere hard-codes the account login, you're likely to get a phone
call.
The other thing you can do is check the net_address in the sysprocesses
table and call your network admin and have him/her tell you whose PC the
MAC address belongs to.
David Gugick
Imceda Software
www.imceda.com|||That is a good idea.
Let me see what happens now.
Thank you,
Rohan
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OUDf6ylKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Rohan Hattangdi wrote:
> Change the "sa" password and see if the problem continues. If someone is
> using the "sa" account without you knowing about it or an application
> somewhere hard-codes the account login, you're likely to get a phone call.
> The other thing you can do is check the net_address in the sysprocesses
> table and call your network admin and have him/her tell you whose PC the
> MAC address belongs to.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||I spoke too soon.
Changing the pwd seems to have had no effect.
This is very strange. Why would it say "sa"? If a lock timeout event
occurs on the connection made by the application, should not the login name
be that used by the application to connect to SQL Server?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OUDf6ylKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Rohan Hattangdi wrote:
> Change the "sa" password and see if the problem continues. If someone is
> using the "sa" account without you knowing about it or an application
> somewhere hard-codes the account login, you're likely to get a phone call.
> The other thing you can do is check the net_address in the sysprocesses
> table and call your network admin and have him/her tell you whose PC the
> MAC address belongs to.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Rohan Hattangdi wrote:
> I spoke too soon.
> Changing the pwd seems to have had no effect.
> This is very strange. Why would it say "sa"? If a lock timeout event
> occurs on the connection made by the application, should not the
> login name be that used by the application to connect to SQL Server?
Were those "sa" logins logged out before the password change? If not, I
think they woudl stay connected and would just have a problem the next
time around. But, yes, in most cases you should see the login name used
in the lock timeout event. Unless you are using Application Roles.
Run sp_helprole and see if any are Application Roles.
David Gugick
Imceda Software
www.imceda.com
Tuesday, March 6, 2012
"Orphaned" maintenance plan
Warning ahead of time - I'm a total idiot about this stuff.
Server is Windows Server 2003
SQL Enterprise Manager version is 8.0
I had been having a problem getting backups to auto delete and so
followed the instructions in a Microsoft KB article and set the
recovery mode to Full for all of the DBs. That solved the problem for
almost every database in the system.
However, apparently at some point several of the databases used a
maintenance plan called "DB Maintenance Plan2" which no longer exists
on the system. (There is currently only a single maintenance plan on
the system.) How can I find out where this is coming from and delete
it?
This was the error in the Events log:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
The Job was invoked by Schedule 5 (Schedule 1). The last step to run
was step 1 (Step 1).
Please talk in baby steps - thanks.
Julie
Hi Julie
"Kaidi" wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used a
> maintenance plan called "DB Maintenance Plan2" which no longer exists
> on the system. (There is currently only a single maintenance plan on
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie
>
I am not sure if it is the job you are looking for or not! But if you start
SQL Enterprise Manager, and open up the Node for the SQL Server Instance, you
will see a node for management under which is SQL Server Agent and then Jobs.
There should be a job called "DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2' " which will have a red cross next to it because it has
failed. Right click the job and choose delete from the menu to remove the
job. Usually when a maintenance plan is deleted the associated jobs should be
deleted.
John
|||Followup, for anyone who happens to hit this post on a usenet search.
(I hate finding my problem with no solution.
There's an SP called sp_delete_job that you can use with either a job
name or a job id and it takes care of the cleanup required to remove
the maintenance plan completely from your system.
Do *not* simply delete the job rows that are causing the problem from
the msdb.sysjobs table. Unfortunately, that is what I did, and it's
taken me a couple of hours to track down all of the pieces/references
to the job in other tables. I'm just hoping to heck I got them all...I
went manually through all of the delete commands in the sp_delete_job
procedure, and praying that it worked!
:P
Julie
On Apr 4, 12:04 pm, "Kaidi" <julie.sie...@.gmail.com> wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used amaintenance plancalled "DB MaintenancePlan2" which no longer exists
> on the system. (There is currently only a singlemaintenance planon
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DBMaintenance Plan'DB
> MaintenancePlan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie
|||Hi Julie
"Kaidi" wrote:
> Followup, for anyone who happens to hit this post on a usenet search.
> (I hate finding my problem with no solution.
I had replied to this!
> There's an SP called sp_delete_job that you can use with either a job
> name or a job id and it takes care of the cleanup required to remove
> the maintenance plan completely from your system.
> Do *not* simply delete the job rows that are causing the problem from
> the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> taken me a couple of hours to track down all of the pieces/references
> to the job in other tables. I'm just hoping to heck I got them all...I
> went manually through all of the delete commands in the sp_delete_job
> procedure, and praying that it worked!
You did not mention this in your original post. It is never recommended to
hack the system tables.
> :P
> Julie
>
John
|||On Apr 7, 6:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Julie
> "Kaidi" wrote:
> I had replied to this!
>
No replies came up in this thread at Google, John - sorry!
> You did not mention this in your original post. It is never recommended to
> hack the system tables.
> John
When I posted originally, I hadn't DONE that yet, but didn't see any
replies.
There are 3 dbs that are backing up through the orphan, at 1gb each
when squished as small as they can go - the HD fills up and if I miss
deleting them, it shuts my client's biz down entirely. I'm gonna be
gone for a month, and they clueless with regards to the server and
have no IT person, so I had to do SOMETHING. Obviously, that something
wasn't a great choice! (Wish I'd seen whatever your post was - lol)
While the orphan is still generating a couple of "Unable to retrieve
steps" errors in the event log, it's at least no longer creating
backups. I think the job order or whatever that'd be called (hey, I'm
a graphic designer and a hack vbscript/javascript programmer - lol)
must be cached somewhere. I'll track it down eventually.
You have *no idea* how sorry I am that I haven't seen your reply! :D
Thanks again,
Julie
Server is Windows Server 2003
SQL Enterprise Manager version is 8.0
I had been having a problem getting backups to auto delete and so
followed the instructions in a Microsoft KB article and set the
recovery mode to Full for all of the DBs. That solved the problem for
almost every database in the system.
However, apparently at some point several of the databases used a
maintenance plan called "DB Maintenance Plan2" which no longer exists
on the system. (There is currently only a single maintenance plan on
the system.) How can I find out where this is coming from and delete
it?
This was the error in the Events log:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
The Job was invoked by Schedule 5 (Schedule 1). The last step to run
was step 1 (Step 1).
Please talk in baby steps - thanks.
Julie
Hi Julie
"Kaidi" wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used a
> maintenance plan called "DB Maintenance Plan2" which no longer exists
> on the system. (There is currently only a single maintenance plan on
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie
>
I am not sure if it is the job you are looking for or not! But if you start
SQL Enterprise Manager, and open up the Node for the SQL Server Instance, you
will see a node for management under which is SQL Server Agent and then Jobs.
There should be a job called "DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2' " which will have a red cross next to it because it has
failed. Right click the job and choose delete from the menu to remove the
job. Usually when a maintenance plan is deleted the associated jobs should be
deleted.
John
|||Followup, for anyone who happens to hit this post on a usenet search.
(I hate finding my problem with no solution.
There's an SP called sp_delete_job that you can use with either a job
name or a job id and it takes care of the cleanup required to remove
the maintenance plan completely from your system.
Do *not* simply delete the job rows that are causing the problem from
the msdb.sysjobs table. Unfortunately, that is what I did, and it's
taken me a couple of hours to track down all of the pieces/references
to the job in other tables. I'm just hoping to heck I got them all...I
went manually through all of the delete commands in the sp_delete_job
procedure, and praying that it worked!
:P
Julie
On Apr 4, 12:04 pm, "Kaidi" <julie.sie...@.gmail.com> wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used amaintenance plancalled "DB MaintenancePlan2" which no longer exists
> on the system. (There is currently only a singlemaintenance planon
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DBMaintenance Plan'DB
> MaintenancePlan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie
|||Hi Julie
"Kaidi" wrote:
> Followup, for anyone who happens to hit this post on a usenet search.
> (I hate finding my problem with no solution.
I had replied to this!
> There's an SP called sp_delete_job that you can use with either a job
> name or a job id and it takes care of the cleanup required to remove
> the maintenance plan completely from your system.
> Do *not* simply delete the job rows that are causing the problem from
> the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> taken me a couple of hours to track down all of the pieces/references
> to the job in other tables. I'm just hoping to heck I got them all...I
> went manually through all of the delete commands in the sp_delete_job
> procedure, and praying that it worked!
You did not mention this in your original post. It is never recommended to
hack the system tables.
> :P
> Julie
>
John
|||On Apr 7, 6:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Julie
> "Kaidi" wrote:
> I had replied to this!
>
No replies came up in this thread at Google, John - sorry!
> You did not mention this in your original post. It is never recommended to
> hack the system tables.
> John
When I posted originally, I hadn't DONE that yet, but didn't see any
replies.
There are 3 dbs that are backing up through the orphan, at 1gb each
when squished as small as they can go - the HD fills up and if I miss
deleting them, it shuts my client's biz down entirely. I'm gonna be
gone for a month, and they clueless with regards to the server and
have no IT person, so I had to do SOMETHING. Obviously, that something
wasn't a great choice! (Wish I'd seen whatever your post was - lol)
While the orphan is still generating a couple of "Unable to retrieve
steps" errors in the event log, it's at least no longer creating
backups. I think the job order or whatever that'd be called (hey, I'm
a graphic designer and a hack vbscript/javascript programmer - lol)
must be cached somewhere. I'll track it down eventually.
You have *no idea* how sorry I am that I haven't seen your reply! :D
Thanks again,
Julie
"Orphaned" maintenance plan
Warning ahead of time - I'm a total idiot about this stuff.
Server is Windows Server 2003
SQL Enterprise Manager version is 8.0
I had been having a problem getting backups to auto delete and so
followed the instructions in a Microsoft KB article and set the
recovery mode to Full for all of the DBs. That solved the problem for
almost every database in the system.
However, apparently at some point several of the databases used a
maintenance plan called "DB Maintenance Plan2" which no longer exists
on the system. (There is currently only a single maintenance plan on
the system.) How can I find out where this is coming from and delete
it?
This was the error in the Events log:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
The Job was invoked by Schedule 5 (Schedule 1). The last step to run
was step 1 (Step 1).
Please talk in baby steps - thanks.
JulieHi Julie
"Kaidi" wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used a
> maintenance plan called "DB Maintenance Plan2" which no longer exists
> on the system. (There is currently only a single maintenance plan on
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie
>
I am not sure if it is the job you are looking for or not! But if you start
SQL Enterprise Manager, and open up the Node for the SQL Server Instance, yo
u
will see a node for management under which is SQL Server Agent and then Jobs
.
There should be a job called "DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2' " which will have a red cross next to it because it has
failed. Right click the job and choose delete from the menu to remove the
job. Usually when a maintenance plan is deleted the associated jobs should b
e
deleted.
John|||Followup, for anyone who happens to hit this post on a usenet search.
(I hate finding my problem with no solution.
There's an SP called sp_delete_job that you can use with either a job
name or a job id and it takes care of the cleanup required to remove
the maintenance plan completely from your system.
Do *not* simply delete the job rows that are causing the problem from
the msdb.sysjobs table. Unfortunately, that is what I did, and it's
taken me a couple of hours to track down all of the pieces/references
to the job in other tables. I'm just hoping to heck I got them all...I
went manually through all of the delete commands in the sp_delete_job
procedure, and praying that it worked!
:P
Julie
On Apr 4, 12:04 pm, "Kaidi" <julie.sie...@.gmail.com> wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used amaintenan
ce plancalled "DB MaintenancePlan2" which no longer exists
> on the system. (There is currently only a singlemaintenance planon
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DBMaintenance Plan'DB
> MaintenancePlan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie|||Hi Julie
"Kaidi" wrote:
> Followup, for anyone who happens to hit this post on a usenet search.
> (I hate finding my problem with no solution.
I had replied to this!
> There's an SP called sp_delete_job that you can use with either a job
> name or a job id and it takes care of the cleanup required to remove
> the maintenance plan completely from your system.
> Do *not* simply delete the job rows that are causing the problem from
> the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> taken me a couple of hours to track down all of the pieces/references
> to the job in other tables. I'm just hoping to heck I got them all...I
> went manually through all of the delete commands in the sp_delete_job
> procedure, and praying that it worked!
You did not mention this in your original post. It is never recommended to
hack the system tables.
> :P
> Julie
>
John|||On Apr 7, 6:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Julie
> "Kaidi" wrote:
> I had replied to this!
>
No replies came up in this thread at Google, John - sorry!
> You did not mention this in your original post. It is never recommended to
> hack the system tables.
> John
When I posted originally, I hadn't DONE that yet, but didn't see any
replies.
There are 3 dbs that are backing up through the orphan, at 1gb each
when squished as small as they can go - the HD fills up and if I miss
deleting them, it shuts my client's biz down entirely. I'm gonna be
gone for a month, and they clueless with regards to the server and
have no IT person, so I had to do SOMETHING. Obviously, that something
wasn't a great choice! (Wish I'd seen whatever your post was - lol)
While the orphan is still generating a couple of "Unable to retrieve
steps" errors in the event log, it's at least no longer creating
backups. I think the job order or whatever that'd be called (hey, I'm
a graphic designer and a hack vbscript/javascript programmer - lol)
must be cached somewhere. I'll track it down eventually.
You have *no idea* how sorry I am that I haven't seen your reply! :D
Thanks again,
Julie
Server is Windows Server 2003
SQL Enterprise Manager version is 8.0
I had been having a problem getting backups to auto delete and so
followed the instructions in a Microsoft KB article and set the
recovery mode to Full for all of the DBs. That solved the problem for
almost every database in the system.
However, apparently at some point several of the databases used a
maintenance plan called "DB Maintenance Plan2" which no longer exists
on the system. (There is currently only a single maintenance plan on
the system.) How can I find out where this is coming from and delete
it?
This was the error in the Events log:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
The Job was invoked by Schedule 5 (Schedule 1). The last step to run
was step 1 (Step 1).
Please talk in baby steps - thanks.
JulieHi Julie
"Kaidi" wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used a
> maintenance plan called "DB Maintenance Plan2" which no longer exists
> on the system. (There is currently only a single maintenance plan on
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie
>
I am not sure if it is the job you are looking for or not! But if you start
SQL Enterprise Manager, and open up the Node for the SQL Server Instance, yo
u
will see a node for management under which is SQL Server Agent and then Jobs
.
There should be a job called "DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2' " which will have a red cross next to it because it has
failed. Right click the job and choose delete from the menu to remove the
job. Usually when a maintenance plan is deleted the associated jobs should b
e
deleted.
John|||Followup, for anyone who happens to hit this post on a usenet search.
(I hate finding my problem with no solution.
There's an SP called sp_delete_job that you can use with either a job
name or a job id and it takes care of the cleanup required to remove
the maintenance plan completely from your system.
Do *not* simply delete the job rows that are causing the problem from
the msdb.sysjobs table. Unfortunately, that is what I did, and it's
taken me a couple of hours to track down all of the pieces/references
to the job in other tables. I'm just hoping to heck I got them all...I
went manually through all of the delete commands in the sp_delete_job
procedure, and praying that it worked!
:P
Julie
On Apr 4, 12:04 pm, "Kaidi" <julie.sie...@.gmail.com> wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used amaintenan
ce plancalled "DB MaintenancePlan2" which no longer exists
> on the system. (There is currently only a singlemaintenance planon
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DBMaintenance Plan'DB
> MaintenancePlan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie|||Hi Julie
"Kaidi" wrote:
> Followup, for anyone who happens to hit this post on a usenet search.
> (I hate finding my problem with no solution.
I had replied to this!
> There's an SP called sp_delete_job that you can use with either a job
> name or a job id and it takes care of the cleanup required to remove
> the maintenance plan completely from your system.
> Do *not* simply delete the job rows that are causing the problem from
> the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> taken me a couple of hours to track down all of the pieces/references
> to the job in other tables. I'm just hoping to heck I got them all...I
> went manually through all of the delete commands in the sp_delete_job
> procedure, and praying that it worked!
You did not mention this in your original post. It is never recommended to
hack the system tables.
> :P
> Julie
>
John|||On Apr 7, 6:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Julie
> "Kaidi" wrote:
> I had replied to this!
>
No replies came up in this thread at Google, John - sorry!
> You did not mention this in your original post. It is never recommended to
> hack the system tables.
> John
When I posted originally, I hadn't DONE that yet, but didn't see any
replies.
There are 3 dbs that are backing up through the orphan, at 1gb each
when squished as small as they can go - the HD fills up and if I miss
deleting them, it shuts my client's biz down entirely. I'm gonna be
gone for a month, and they clueless with regards to the server and
have no IT person, so I had to do SOMETHING. Obviously, that something
wasn't a great choice! (Wish I'd seen whatever your post was - lol)
While the orphan is still generating a couple of "Unable to retrieve
steps" errors in the event log, it's at least no longer creating
backups. I think the job order or whatever that'd be called (hey, I'm
a graphic designer and a hack vbscript/javascript programmer - lol)
must be cached somewhere. I'll track it down eventually.
You have *no idea* how sorry I am that I haven't seen your reply! :D
Thanks again,
Julie
"Orphaned" maintenance plan
Warning ahead of time - I'm a total idiot about this stuff.
Server is Windows Server 2003
SQL Enterprise Manager version is 8.0
I had been having a problem getting backups to auto delete and so
followed the instructions in a Microsoft KB article and set the
recovery mode to Full for all of the DBs. That solved the problem for
almost every database in the system.
However, apparently at some point several of the databases used a
maintenance plan called "DB Maintenance Plan2" which no longer exists
on the system. (There is currently only a single maintenance plan on
the system.) How can I find out where this is coming from and delete
it?
This was the error in the Events log:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
The Job was invoked by Schedule 5 (Schedule 1). The last step to run
was step 1 (Step 1).
Please talk in baby steps - thanks.
JulieHi Julie
"Kaidi" wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used a
> maintenance plan called "DB Maintenance Plan2" which no longer exists
> on the system. (There is currently only a single maintenance plan on
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie
>
I am not sure if it is the job you are looking for or not! But if you start
SQL Enterprise Manager, and open up the Node for the SQL Server Instance, you
will see a node for management under which is SQL Server Agent and then Jobs.
There should be a job called "DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2' " which will have a red cross next to it because it has
failed. Right click the job and choose delete from the menu to remove the
job. Usually when a maintenance plan is deleted the associated jobs should be
deleted.
John|||Followup, for anyone who happens to hit this post on a usenet search.
(I hate finding my problem with no solution.
There's an SP called sp_delete_job that you can use with either a job
name or a job id and it takes care of the cleanup required to remove
the maintenance plan completely from your system.
Do *not* simply delete the job rows that are causing the problem from
the msdb.sysjobs table. Unfortunately, that is what I did, and it's
taken me a couple of hours to track down all of the pieces/references
to the job in other tables. I'm just hoping to heck I got them all...I
went manually through all of the delete commands in the sp_delete_job
procedure, and praying that it worked!
:P
Julie
On Apr 4, 12:04 pm, "Kaidi" <julie.sie...@.gmail.com> wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used amaintenance plancalled "DB MaintenancePlan2" which no longer exists
> on the system. (There is currently only a singlemaintenance planon
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DBMaintenance Plan'DB
> MaintenancePlan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie|||Hi Julie
"Kaidi" wrote:
> Followup, for anyone who happens to hit this post on a usenet search.
> (I hate finding my problem with no solution.
I had replied to this!
> There's an SP called sp_delete_job that you can use with either a job
> name or a job id and it takes care of the cleanup required to remove
> the maintenance plan completely from your system.
> Do *not* simply delete the job rows that are causing the problem from
> the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> taken me a couple of hours to track down all of the pieces/references
> to the job in other tables. I'm just hoping to heck I got them all...I
> went manually through all of the delete commands in the sp_delete_job
> procedure, and praying that it worked!
You did not mention this in your original post. It is never recommended to
hack the system tables.
> :P
> Julie
>
John|||On Apr 7, 6:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Julie
> "Kaidi" wrote:
> > Followup, for anyone who happens to hit this post on a usenet search.
> > (I hate finding my problem with no solution.
> I had replied to this!
>
No replies came up in this thread at Google, John - sorry!
> > Do *not* simply delete the job rows that are causing the problem from
> > the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> > taken me a couple of hours to track down all of the pieces/references
> > to the job in other tables. I'm just hoping to heck I got them all...I
> > went manually through all of the delete commands in the sp_delete_job
> > procedure, and praying that it worked!
> You did not mention this in your original post. It is never recommended to
> hack the system tables.
> John
When I posted originally, I hadn't DONE that yet, but didn't see any
replies.
There are 3 dbs that are backing up through the orphan, at 1gb each
when squished as small as they can go - the HD fills up and if I miss
deleting them, it shuts my client's biz down entirely. I'm gonna be
gone for a month, and they clueless with regards to the server and
have no IT person, so I had to do SOMETHING. Obviously, that something
wasn't a great choice! (Wish I'd seen whatever your post was - lol)
While the orphan is still generating a couple of "Unable to retrieve
steps" errors in the event log, it's at least no longer creating
backups. I think the job order or whatever that'd be called (hey, I'm
a graphic designer and a hack vbscript/javascript programmer - lol)
must be cached somewhere. I'll track it down eventually.
You have *no idea* how sorry I am that I haven't seen your reply! :D
Thanks again,
Julie
Server is Windows Server 2003
SQL Enterprise Manager version is 8.0
I had been having a problem getting backups to auto delete and so
followed the instructions in a Microsoft KB article and set the
recovery mode to Full for all of the DBs. That solved the problem for
almost every database in the system.
However, apparently at some point several of the databases used a
maintenance plan called "DB Maintenance Plan2" which no longer exists
on the system. (There is currently only a single maintenance plan on
the system.) How can I find out where this is coming from and delete
it?
This was the error in the Events log:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
The Job was invoked by Schedule 5 (Schedule 1). The last step to run
was step 1 (Step 1).
Please talk in baby steps - thanks.
JulieHi Julie
"Kaidi" wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used a
> maintenance plan called "DB Maintenance Plan2" which no longer exists
> on the system. (There is currently only a single maintenance plan on
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie
>
I am not sure if it is the job you are looking for or not! But if you start
SQL Enterprise Manager, and open up the Node for the SQL Server Instance, you
will see a node for management under which is SQL Server Agent and then Jobs.
There should be a job called "DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2' " which will have a red cross next to it because it has
failed. Right click the job and choose delete from the menu to remove the
job. Usually when a maintenance plan is deleted the associated jobs should be
deleted.
John|||Followup, for anyone who happens to hit this post on a usenet search.
(I hate finding my problem with no solution.
There's an SP called sp_delete_job that you can use with either a job
name or a job id and it takes care of the cleanup required to remove
the maintenance plan completely from your system.
Do *not* simply delete the job rows that are causing the problem from
the msdb.sysjobs table. Unfortunately, that is what I did, and it's
taken me a couple of hours to track down all of the pieces/references
to the job in other tables. I'm just hoping to heck I got them all...I
went manually through all of the delete commands in the sp_delete_job
procedure, and praying that it worked!
:P
Julie
On Apr 4, 12:04 pm, "Kaidi" <julie.sie...@.gmail.com> wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used amaintenance plancalled "DB MaintenancePlan2" which no longer exists
> on the system. (There is currently only a singlemaintenance planon
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DBMaintenance Plan'DB
> MaintenancePlan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie|||Hi Julie
"Kaidi" wrote:
> Followup, for anyone who happens to hit this post on a usenet search.
> (I hate finding my problem with no solution.
I had replied to this!
> There's an SP called sp_delete_job that you can use with either a job
> name or a job id and it takes care of the cleanup required to remove
> the maintenance plan completely from your system.
> Do *not* simply delete the job rows that are causing the problem from
> the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> taken me a couple of hours to track down all of the pieces/references
> to the job in other tables. I'm just hoping to heck I got them all...I
> went manually through all of the delete commands in the sp_delete_job
> procedure, and praying that it worked!
You did not mention this in your original post. It is never recommended to
hack the system tables.
> :P
> Julie
>
John|||On Apr 7, 6:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Julie
> "Kaidi" wrote:
> > Followup, for anyone who happens to hit this post on a usenet search.
> > (I hate finding my problem with no solution.
> I had replied to this!
>
No replies came up in this thread at Google, John - sorry!
> > Do *not* simply delete the job rows that are causing the problem from
> > the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> > taken me a couple of hours to track down all of the pieces/references
> > to the job in other tables. I'm just hoping to heck I got them all...I
> > went manually through all of the delete commands in the sp_delete_job
> > procedure, and praying that it worked!
> You did not mention this in your original post. It is never recommended to
> hack the system tables.
> John
When I posted originally, I hadn't DONE that yet, but didn't see any
replies.
There are 3 dbs that are backing up through the orphan, at 1gb each
when squished as small as they can go - the HD fills up and if I miss
deleting them, it shuts my client's biz down entirely. I'm gonna be
gone for a month, and they clueless with regards to the server and
have no IT person, so I had to do SOMETHING. Obviously, that something
wasn't a great choice! (Wish I'd seen whatever your post was - lol)
While the orphan is still generating a couple of "Unable to retrieve
steps" errors in the event log, it's at least no longer creating
backups. I think the job order or whatever that'd be called (hey, I'm
a graphic designer and a hack vbscript/javascript programmer - lol)
must be cached somewhere. I'll track it down eventually.
You have *no idea* how sorry I am that I haven't seen your reply! :D
Thanks again,
Julie
Saturday, February 25, 2012
"Must declare variable" error caused by stored procedure
I'm having the most difficult time trying to generate a report that
first calls a stored procedure and then retrieves the data produced
from it.
I get the error, "An error has occurred during report
processing...query execution failed for data set dsOrgs...Must declare
the variable @.INum" when I try to run the report.
The dataset below (dsOrgs) first calls a stored procedure
(SV_GetSubordinates) that populates a table with hierarchical data.
The second part (the Select statement) then retrieves the data
produced by the stored procedure. I have no problem running this set
of SQL statements in the Data view of the Reporting Services Report
Designer.
EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
SELECT v_Orgs.*
FROM v_Orgs, SVSiblings
WHERE
v_Orgs.INum = @.INum AND
v_Orgs.INum = SVSiblings.INum AND
v_Orgs.OrgNum = SVSiblings.Num
By the way, @.INum is a parameter that will be passed to the report in
a URL string eventually. But for now, I have to use both the Preview
capability of the Report Designer and the Report Manager rendering
engine to test out my report.
I have another dataset that gets the @.OrgNum parameter value from a
selection in a drop down in my report. Here is the query for that
data set...
SELECT
NULL AS OrgNum,
'-- ALL Orgs --' AS [Description]
FROM SVGroupDefs
WHERE
INum = @.INum
UNION
SELECT
OrgNum,
[Description]
FROM SVGroupDefs
WHERE
INum = @.INum
ORDER BY [Description]
As you can see, I'm using @.INum in this dataset first so I can
populate my drop down list. When the user selects an Organization
from the drop down list, the selection returns the value for the
parameter @.OrgNum, which is used in my dsOrgs dataset along with @.INum
to retrieve the hierarchical data for my report.
You may be asking why I need to get hierarchical data when the table
object in the report designer uses a parent-child relationship. The
reason why I'm going through all this pain is because I need to
recursively get all the children from a starting parent level, which
@.OrgNum supplies. SQL Server does not natively support a way to
recursively get all the children in a hierarchy. The only way to do
this is to run through my stored procedure, which recursively calls
itself and then populates a table with the child OrgNum values
(fortunately Yukon has solved this recursive nightmare).
Anyway, how can I generate my report when the error states I must
first declare @.INum?I guess thats because the SPs run independently.
BTW have you considered using cursors in your SP?
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves the
data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use both
the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the query
for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support a
way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Ravi, the stored procedure must call itself recursively as it gets the
children for each parent. For example, here's my hierarchy
OrgNum ParentOrgNum
1001 NULL
1002 1001
1003 1002
1004 1002
1005 1001
1006 1005
1007 1006
1008 1006
If I want all the children for OrgNum 1005, the sproc first runs and
gets OrgNum 1006 as a child. Then 1006 becomes the parent and the
sproc calls itself to get all the children for 1006, which are 1007
and 1008. When the sproc tries to get their children, there are no
more, and the sproc terminates. So, I end up with 1006, 1007, and
1008 as children for 1005. During each iteration of the sproc, I
insert the child OrgNum values into a table.
So, with my dsOrgs dataset, I first run the sproc, which does the
stuff above. Then I run a select statement which retrieves all the
children from the table I populated from my sproc. All that works
fine when I run everything in the data area of Reporting Services'
Report Builder.
But that's not the problem. The problem is that I get an error
stating that I have to declare a variable, @.INum. That's what my
first message covers in detail, and the problem to which I'm seeking a
solution.
"Ravi" <ravikantkv@.rediffmail.com> wrote in message news:<549501c49175$e1e4dcd0$a601280a@.phx.gbl>...
> I guess thats because the SPs run independently.
> BTW have you considered using cursors in your SP?
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves the
> data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use both
> the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the query
> for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support a
> way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||Hi Steve,
I am doing something sort of similar... I have created a
stored procedure that takes in a couple of parameters and
passes them to the database and creates a table. I then
have a query that selects the data from the table. I
created two seperate datasets one for the stored
procedure and one for the select statement and it seems
to be working. I'm not sure how you tell it what to
execute first but another report writer here is doing the
same but she had to increase the timeout to give the
stored procedure a chance to finish otherwise it was
throwing errors. Sorry I'm not much help but I'm curious
if you've tried to use the parent group within the group
that is suppose to recursively search in a parent-child
relationship? I have the same exact thing to do that you
are doing and I would love to hear any lessons learned.
Thanks!!
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves
the data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use
both the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the
query for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support
a way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Melissa,
Reporting Services does a fine job handling parent-child sets of
data in tables and what not...but that's assuming you have a data set
with all the data you want. What I have is an entire hierarchy, of
which, only one part I might want to retrieve in the data set for the
report (e.g. pulling back a region and their branches vs. the whole
entire organization). So, the whole problem here is about retrieving
the data and not about how Reporting Services will handle it after the
data is retrieved.
With that being said, I've already tried to break out the stored
procedure that gets all the children vs. the SQL query that retrieves
the data produced by the stored procedure into two separate data sets.
But how would Reporting Services know when the stored procedure has
finished in order to run the second query, which retrieves the data?
By arbitrarily setting a timeout? I find that method too unreliable.
I've already broken out my stored procedure and select statement into
two data sets, but that doesn't work.
So, what else have I done? I've tried to do a recursive SQL
function, but to no avail (functions can't recursive do selects of
data), a recursive stored procedure with a varying output parameter
(Reporting Services allows only 1 value per parameter for the current
release), but to no avail, setting the output of my stored procedure
to a temp table (can't seem to get that to work), but to no avail,
using a global temp table within my stored procedure (doesn't work
because the stored procedure recursively calls itself and you can only
declare the global temp table once), but to no avail, and a bunch of
other techniques in order to recursively grab all the children for the
region I select for my data set. The Yukon release of SQL Server will
solve my problem, because I will be able to execute a single
expression and retrieve the recursive data I need in a single
operation...but I need something in the meantime. (By the way, Oracle
already supports recursion with their Connect method).
So, I ended up having to pair the stored procedure with my select in
the same dataset in order to 1) generate a list of child values and 2)
retrieve that list of values AFTER they are generated. The dataset
refreshes no problem, but I get that stupid "need to declare @.INum
first" error, which I can't get rid of...it's so frustrating. I wish
I could speak to one of the Reporting Services developers over the
phone and figure this out.
"Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> Hi Steve,
> I am doing something sort of similar... I have created a
> stored procedure that takes in a couple of parameters and
> passes them to the database and creates a table. I then
> have a query that selects the data from the table. I
> created two seperate datasets one for the stored
> procedure and one for the select statement and it seems
> to be working. I'm not sure how you tell it what to
> execute first but another report writer here is doing the
> same but she had to increase the timeout to give the
> stored procedure a chance to finish otherwise it was
> throwing errors. Sorry I'm not much help but I'm curious
> if you've tried to use the parent group within the group
> that is suppose to recursively search in a parent-child
> relationship? I have the same exact thing to do that you
> are doing and I would love to hear any lessons learned.
> Thanks!!
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves
> the data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use
> both the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the
> query for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support
> a way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||In case anyone cares, I solved this problem myself.
The problem was related to a dataset that first calls a stored
procedure to populate a table with recursive data and then runs a
select statement to retrieve a set of data filtered by the recursive
data. What was happening was that Reporting Services was erroring
because my select was trying to fire before my stored procedure
finished. I ended up gettting a "Must declare variable" error (among
other things).
The trick is to make the select statement "wait" for the stored
procedure. How do you do this, you ask? It's simple.
You have to declare a variable and then set the execution of the
stored procedure to the variable. The select statement that follows
has to wait for the variable above to get populated with a "0", which
means the stored procedure executed properly. Then the select will
fire.
Here's a sample dataset query that uses the variable wait method:
DECLARE @.ResultValue INT
EXEC @.ResultNum = SV_GetSubordinates @.INum,'Goals',@.GoalNum
SELECT *
FROM v_Goals
WHERE
GoalNum IN (
SELECT Num
FROM SVSiblings
WHERE
INum = @.INum AND
Type = 'Goals' AND
UserID = USER_ID())
See the part about "EXEC @.ResultNum = "? That's the key to avoiding
the "Must declare variable" error I encountered and the misfiring of
the select statement. The select part of the query waits for
@.ResultNum to be populated with a "0" before getting run.
steve.pantazis@.salesviz.com (Steve Pantazis) wrote in message news:<45c5a039.0409032037.1e978da4@.posting.google.com>...
> Melissa,
> Reporting Services does a fine job handling parent-child sets of
> data in tables and what not...but that's assuming you have a data set
> with all the data you want. What I have is an entire hierarchy, of
> which, only one part I might want to retrieve in the data set for the
> report (e.g. pulling back a region and their branches vs. the whole
> entire organization). So, the whole problem here is about retrieving
> the data and not about how Reporting Services will handle it after the
> data is retrieved.
> With that being said, I've already tried to break out the stored
> procedure that gets all the children vs. the SQL query that retrieves
> the data produced by the stored procedure into two separate data sets.
> But how would Reporting Services know when the stored procedure has
> finished in order to run the second query, which retrieves the data?
> By arbitrarily setting a timeout? I find that method too unreliable.
> I've already broken out my stored procedure and select statement into
> two data sets, but that doesn't work.
> So, what else have I done? I've tried to do a recursive SQL
> function, but to no avail (functions can't recursive do selects of
> data), a recursive stored procedure with a varying output parameter
> (Reporting Services allows only 1 value per parameter for the current
> release), but to no avail, setting the output of my stored procedure
> to a temp table (can't seem to get that to work), but to no avail,
> using a global temp table within my stored procedure (doesn't work
> because the stored procedure recursively calls itself and you can only
> declare the global temp table once), but to no avail, and a bunch of
> other techniques in order to recursively grab all the children for the
> region I select for my data set. The Yukon release of SQL Server will
> solve my problem, because I will be able to execute a single
> expression and retrieve the recursive data I need in a single
> operation...but I need something in the meantime. (By the way, Oracle
> already supports recursion with their Connect method).
> So, I ended up having to pair the stored procedure with my select in
> the same dataset in order to 1) generate a list of child values and 2)
> retrieve that list of values AFTER they are generated. The dataset
> refreshes no problem, but I get that stupid "need to declare @.INum
> first" error, which I can't get rid of...it's so frustrating. I wish
> I could speak to one of the Reporting Services developers over the
> phone and figure this out.
>
> "Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> > Hi Steve,
> > I am doing something sort of similar... I have created a
> > stored procedure that takes in a couple of parameters and
> > passes them to the database and creates a table. I then
> > have a query that selects the data from the table. I
> > created two seperate datasets one for the stored
> > procedure and one for the select statement and it seems
> > to be working. I'm not sure how you tell it what to
> > execute first but another report writer here is doing the
> > same but she had to increase the timeout to give the
> > stored procedure a chance to finish otherwise it was
> > throwing errors. Sorry I'm not much help but I'm curious
> > if you've tried to use the parent group within the group
> > that is suppose to recursively search in a parent-child
> > relationship? I have the same exact thing to do that you
> > are doing and I would love to hear any lessons learned.
> > Thanks!!
> > >--Original Message--
> > >I'm having the most difficult time trying to generate a
> report that
> > >first calls a stored procedure and then retrieves the
> data produced
> > >from it.
> > >
> > >I get the error, "An error has occurred during report
> > >processing...query execution failed for data set
> dsOrgs...Must declare
> > >the variable @.INum" when I try to run the report.
> > >
> > >The dataset below (dsOrgs) first calls a stored procedure
> > >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> > >The second part (the Select statement) then retrieves
> the data
> > >produced by the stored procedure. I have no problem
> running this set
> > >of SQL statements in the Data view of the Reporting
> Services Report
> > >Designer.
> > >
> > >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> > >SELECT v_Orgs.*
> > >FROM v_Orgs, SVSiblings
> > >WHERE
> > > v_Orgs.INum = @.INum AND
> > > v_Orgs.INum = SVSiblings.INum AND
> > > v_Orgs.OrgNum = SVSiblings.Num
> > >
> > >By the way, @.INum is a parameter that will be passed to
> the report in
> > >a URL string eventually. But for now, I have to use
> both the Preview
> > >capability of the Report Designer and the Report Manager
> rendering
> > >engine to test out my report.
> > >
> > >I have another dataset that gets the @.OrgNum parameter
> value from a
> > >selection in a drop down in my report. Here is the
> query for that
> > >data set...
> > >
> > >SELECT
> > > NULL AS OrgNum,
> > > '-- ALL Orgs --' AS [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >UNION
> > >SELECT
> > > OrgNum,
> > > [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >ORDER BY [Description]
> > >
> > >As you can see, I'm using @.INum in this dataset first so
> I can
> > >populate my drop down list. When the user selects an
> Organization
> > >from the drop down list, the selection returns the value
> for the
> > >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> > >to retrieve the hierarchical data for my report.
> > >
> > >You may be asking why I need to get hierarchical data
> when the table
> > >object in the report designer uses a parent-child
> relationship. The
> > >reason why I'm going through all this pain is because I
> need to
> > >recursively get all the children from a starting parent
> level, which
> > >@.OrgNum supplies. SQL Server does not natively support
> a way to
> > >recursively get all the children in a hierarchy. The
> only way to do
> > >this is to run through my stored procedure, which
> recursively calls
> > >itself and then populates a table with the child OrgNum
> values
> > >(fortunately Yukon has solved this recursive nightmare).
> > >
> > >Anyway, how can I generate my report when the error
> states I must
> > >first declare @.INum?
> > >.
> > >
first calls a stored procedure and then retrieves the data produced
from it.
I get the error, "An error has occurred during report
processing...query execution failed for data set dsOrgs...Must declare
the variable @.INum" when I try to run the report.
The dataset below (dsOrgs) first calls a stored procedure
(SV_GetSubordinates) that populates a table with hierarchical data.
The second part (the Select statement) then retrieves the data
produced by the stored procedure. I have no problem running this set
of SQL statements in the Data view of the Reporting Services Report
Designer.
EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
SELECT v_Orgs.*
FROM v_Orgs, SVSiblings
WHERE
v_Orgs.INum = @.INum AND
v_Orgs.INum = SVSiblings.INum AND
v_Orgs.OrgNum = SVSiblings.Num
By the way, @.INum is a parameter that will be passed to the report in
a URL string eventually. But for now, I have to use both the Preview
capability of the Report Designer and the Report Manager rendering
engine to test out my report.
I have another dataset that gets the @.OrgNum parameter value from a
selection in a drop down in my report. Here is the query for that
data set...
SELECT
NULL AS OrgNum,
'-- ALL Orgs --' AS [Description]
FROM SVGroupDefs
WHERE
INum = @.INum
UNION
SELECT
OrgNum,
[Description]
FROM SVGroupDefs
WHERE
INum = @.INum
ORDER BY [Description]
As you can see, I'm using @.INum in this dataset first so I can
populate my drop down list. When the user selects an Organization
from the drop down list, the selection returns the value for the
parameter @.OrgNum, which is used in my dsOrgs dataset along with @.INum
to retrieve the hierarchical data for my report.
You may be asking why I need to get hierarchical data when the table
object in the report designer uses a parent-child relationship. The
reason why I'm going through all this pain is because I need to
recursively get all the children from a starting parent level, which
@.OrgNum supplies. SQL Server does not natively support a way to
recursively get all the children in a hierarchy. The only way to do
this is to run through my stored procedure, which recursively calls
itself and then populates a table with the child OrgNum values
(fortunately Yukon has solved this recursive nightmare).
Anyway, how can I generate my report when the error states I must
first declare @.INum?I guess thats because the SPs run independently.
BTW have you considered using cursors in your SP?
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves the
data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use both
the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the query
for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support a
way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Ravi, the stored procedure must call itself recursively as it gets the
children for each parent. For example, here's my hierarchy
OrgNum ParentOrgNum
1001 NULL
1002 1001
1003 1002
1004 1002
1005 1001
1006 1005
1007 1006
1008 1006
If I want all the children for OrgNum 1005, the sproc first runs and
gets OrgNum 1006 as a child. Then 1006 becomes the parent and the
sproc calls itself to get all the children for 1006, which are 1007
and 1008. When the sproc tries to get their children, there are no
more, and the sproc terminates. So, I end up with 1006, 1007, and
1008 as children for 1005. During each iteration of the sproc, I
insert the child OrgNum values into a table.
So, with my dsOrgs dataset, I first run the sproc, which does the
stuff above. Then I run a select statement which retrieves all the
children from the table I populated from my sproc. All that works
fine when I run everything in the data area of Reporting Services'
Report Builder.
But that's not the problem. The problem is that I get an error
stating that I have to declare a variable, @.INum. That's what my
first message covers in detail, and the problem to which I'm seeking a
solution.
"Ravi" <ravikantkv@.rediffmail.com> wrote in message news:<549501c49175$e1e4dcd0$a601280a@.phx.gbl>...
> I guess thats because the SPs run independently.
> BTW have you considered using cursors in your SP?
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves the
> data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use both
> the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the query
> for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support a
> way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||Hi Steve,
I am doing something sort of similar... I have created a
stored procedure that takes in a couple of parameters and
passes them to the database and creates a table. I then
have a query that selects the data from the table. I
created two seperate datasets one for the stored
procedure and one for the select statement and it seems
to be working. I'm not sure how you tell it what to
execute first but another report writer here is doing the
same but she had to increase the timeout to give the
stored procedure a chance to finish otherwise it was
throwing errors. Sorry I'm not much help but I'm curious
if you've tried to use the parent group within the group
that is suppose to recursively search in a parent-child
relationship? I have the same exact thing to do that you
are doing and I would love to hear any lessons learned.
Thanks!!
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves
the data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use
both the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the
query for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support
a way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Melissa,
Reporting Services does a fine job handling parent-child sets of
data in tables and what not...but that's assuming you have a data set
with all the data you want. What I have is an entire hierarchy, of
which, only one part I might want to retrieve in the data set for the
report (e.g. pulling back a region and their branches vs. the whole
entire organization). So, the whole problem here is about retrieving
the data and not about how Reporting Services will handle it after the
data is retrieved.
With that being said, I've already tried to break out the stored
procedure that gets all the children vs. the SQL query that retrieves
the data produced by the stored procedure into two separate data sets.
But how would Reporting Services know when the stored procedure has
finished in order to run the second query, which retrieves the data?
By arbitrarily setting a timeout? I find that method too unreliable.
I've already broken out my stored procedure and select statement into
two data sets, but that doesn't work.
So, what else have I done? I've tried to do a recursive SQL
function, but to no avail (functions can't recursive do selects of
data), a recursive stored procedure with a varying output parameter
(Reporting Services allows only 1 value per parameter for the current
release), but to no avail, setting the output of my stored procedure
to a temp table (can't seem to get that to work), but to no avail,
using a global temp table within my stored procedure (doesn't work
because the stored procedure recursively calls itself and you can only
declare the global temp table once), but to no avail, and a bunch of
other techniques in order to recursively grab all the children for the
region I select for my data set. The Yukon release of SQL Server will
solve my problem, because I will be able to execute a single
expression and retrieve the recursive data I need in a single
operation...but I need something in the meantime. (By the way, Oracle
already supports recursion with their Connect method).
So, I ended up having to pair the stored procedure with my select in
the same dataset in order to 1) generate a list of child values and 2)
retrieve that list of values AFTER they are generated. The dataset
refreshes no problem, but I get that stupid "need to declare @.INum
first" error, which I can't get rid of...it's so frustrating. I wish
I could speak to one of the Reporting Services developers over the
phone and figure this out.
"Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> Hi Steve,
> I am doing something sort of similar... I have created a
> stored procedure that takes in a couple of parameters and
> passes them to the database and creates a table. I then
> have a query that selects the data from the table. I
> created two seperate datasets one for the stored
> procedure and one for the select statement and it seems
> to be working. I'm not sure how you tell it what to
> execute first but another report writer here is doing the
> same but she had to increase the timeout to give the
> stored procedure a chance to finish otherwise it was
> throwing errors. Sorry I'm not much help but I'm curious
> if you've tried to use the parent group within the group
> that is suppose to recursively search in a parent-child
> relationship? I have the same exact thing to do that you
> are doing and I would love to hear any lessons learned.
> Thanks!!
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves
> the data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use
> both the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the
> query for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support
> a way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||In case anyone cares, I solved this problem myself.
The problem was related to a dataset that first calls a stored
procedure to populate a table with recursive data and then runs a
select statement to retrieve a set of data filtered by the recursive
data. What was happening was that Reporting Services was erroring
because my select was trying to fire before my stored procedure
finished. I ended up gettting a "Must declare variable" error (among
other things).
The trick is to make the select statement "wait" for the stored
procedure. How do you do this, you ask? It's simple.
You have to declare a variable and then set the execution of the
stored procedure to the variable. The select statement that follows
has to wait for the variable above to get populated with a "0", which
means the stored procedure executed properly. Then the select will
fire.
Here's a sample dataset query that uses the variable wait method:
DECLARE @.ResultValue INT
EXEC @.ResultNum = SV_GetSubordinates @.INum,'Goals',@.GoalNum
SELECT *
FROM v_Goals
WHERE
GoalNum IN (
SELECT Num
FROM SVSiblings
WHERE
INum = @.INum AND
Type = 'Goals' AND
UserID = USER_ID())
See the part about "EXEC @.ResultNum = "? That's the key to avoiding
the "Must declare variable" error I encountered and the misfiring of
the select statement. The select part of the query waits for
@.ResultNum to be populated with a "0" before getting run.
steve.pantazis@.salesviz.com (Steve Pantazis) wrote in message news:<45c5a039.0409032037.1e978da4@.posting.google.com>...
> Melissa,
> Reporting Services does a fine job handling parent-child sets of
> data in tables and what not...but that's assuming you have a data set
> with all the data you want. What I have is an entire hierarchy, of
> which, only one part I might want to retrieve in the data set for the
> report (e.g. pulling back a region and their branches vs. the whole
> entire organization). So, the whole problem here is about retrieving
> the data and not about how Reporting Services will handle it after the
> data is retrieved.
> With that being said, I've already tried to break out the stored
> procedure that gets all the children vs. the SQL query that retrieves
> the data produced by the stored procedure into two separate data sets.
> But how would Reporting Services know when the stored procedure has
> finished in order to run the second query, which retrieves the data?
> By arbitrarily setting a timeout? I find that method too unreliable.
> I've already broken out my stored procedure and select statement into
> two data sets, but that doesn't work.
> So, what else have I done? I've tried to do a recursive SQL
> function, but to no avail (functions can't recursive do selects of
> data), a recursive stored procedure with a varying output parameter
> (Reporting Services allows only 1 value per parameter for the current
> release), but to no avail, setting the output of my stored procedure
> to a temp table (can't seem to get that to work), but to no avail,
> using a global temp table within my stored procedure (doesn't work
> because the stored procedure recursively calls itself and you can only
> declare the global temp table once), but to no avail, and a bunch of
> other techniques in order to recursively grab all the children for the
> region I select for my data set. The Yukon release of SQL Server will
> solve my problem, because I will be able to execute a single
> expression and retrieve the recursive data I need in a single
> operation...but I need something in the meantime. (By the way, Oracle
> already supports recursion with their Connect method).
> So, I ended up having to pair the stored procedure with my select in
> the same dataset in order to 1) generate a list of child values and 2)
> retrieve that list of values AFTER they are generated. The dataset
> refreshes no problem, but I get that stupid "need to declare @.INum
> first" error, which I can't get rid of...it's so frustrating. I wish
> I could speak to one of the Reporting Services developers over the
> phone and figure this out.
>
> "Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> > Hi Steve,
> > I am doing something sort of similar... I have created a
> > stored procedure that takes in a couple of parameters and
> > passes them to the database and creates a table. I then
> > have a query that selects the data from the table. I
> > created two seperate datasets one for the stored
> > procedure and one for the select statement and it seems
> > to be working. I'm not sure how you tell it what to
> > execute first but another report writer here is doing the
> > same but she had to increase the timeout to give the
> > stored procedure a chance to finish otherwise it was
> > throwing errors. Sorry I'm not much help but I'm curious
> > if you've tried to use the parent group within the group
> > that is suppose to recursively search in a parent-child
> > relationship? I have the same exact thing to do that you
> > are doing and I would love to hear any lessons learned.
> > Thanks!!
> > >--Original Message--
> > >I'm having the most difficult time trying to generate a
> report that
> > >first calls a stored procedure and then retrieves the
> data produced
> > >from it.
> > >
> > >I get the error, "An error has occurred during report
> > >processing...query execution failed for data set
> dsOrgs...Must declare
> > >the variable @.INum" when I try to run the report.
> > >
> > >The dataset below (dsOrgs) first calls a stored procedure
> > >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> > >The second part (the Select statement) then retrieves
> the data
> > >produced by the stored procedure. I have no problem
> running this set
> > >of SQL statements in the Data view of the Reporting
> Services Report
> > >Designer.
> > >
> > >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> > >SELECT v_Orgs.*
> > >FROM v_Orgs, SVSiblings
> > >WHERE
> > > v_Orgs.INum = @.INum AND
> > > v_Orgs.INum = SVSiblings.INum AND
> > > v_Orgs.OrgNum = SVSiblings.Num
> > >
> > >By the way, @.INum is a parameter that will be passed to
> the report in
> > >a URL string eventually. But for now, I have to use
> both the Preview
> > >capability of the Report Designer and the Report Manager
> rendering
> > >engine to test out my report.
> > >
> > >I have another dataset that gets the @.OrgNum parameter
> value from a
> > >selection in a drop down in my report. Here is the
> query for that
> > >data set...
> > >
> > >SELECT
> > > NULL AS OrgNum,
> > > '-- ALL Orgs --' AS [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >UNION
> > >SELECT
> > > OrgNum,
> > > [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >ORDER BY [Description]
> > >
> > >As you can see, I'm using @.INum in this dataset first so
> I can
> > >populate my drop down list. When the user selects an
> Organization
> > >from the drop down list, the selection returns the value
> for the
> > >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> > >to retrieve the hierarchical data for my report.
> > >
> > >You may be asking why I need to get hierarchical data
> when the table
> > >object in the report designer uses a parent-child
> relationship. The
> > >reason why I'm going through all this pain is because I
> need to
> > >recursively get all the children from a starting parent
> level, which
> > >@.OrgNum supplies. SQL Server does not natively support
> a way to
> > >recursively get all the children in a hierarchy. The
> only way to do
> > >this is to run through my stored procedure, which
> recursively calls
> > >itself and then populates a table with the child OrgNum
> values
> > >(fortunately Yukon has solved this recursive nightmare).
> > >
> > >Anyway, how can I generate my report when the error
> states I must
> > >first declare @.INum?
> > >.
> > >
Subscribe to:
Posts (Atom)
