Poll - Stored Procedure vs. Command Text?

Has anyone found a clear benefit to one over the other for complex SQL
in Reporting Services reports?
I've stayed away from stored procedures so that my report development
lifecycle can remain mostly independent from my team's database
migrations, but if there is a clear benefit, I will strongly consider
it.
Thanks!
Mike"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in
news:1123614436.830410.247520@.g14g2000cwa.googlegroups.com:
> Has anyone found a clear benefit to one over the other for complex SQL
> in Reporting Services reports?
> I've stayed away from stored procedures so that my report development
> lifecycle can remain mostly independent from my team's database
> migrations, but if there is a clear benefit, I will strongly consider
> it.
> Thanks!
> Mike
>
I've used both. I prefer to use queries. I'll only used stored procs if 1)
I'll be reusing the same query in a number of reports, and I want to
simplify my life for maintenance, or 2) I need to do some processing on
values prior to returning data.
I query data from 3rd party software and I hate to place stored procs in
those databases. It's too easy to forget them, have have the software
company blow them away during an update.

Point in time restore

Is it possible?

I have no Backups at all.
And I dropped a important table completely.
Is there anyway to roll back this command.

I am guessing that the transaction log keeps this inforation and its still there. I think with other 3rd party tools you can recover to a point in time without backups so there must be a way we can do it without 3rd party tools , cant it?

Your 'best' option at this point is to immediately download an eval copy of one of the Third party log tools. Many are fully functioning for 14-30 days.

But since you indicated you have no backups, if by some bad karma your database is in 'Simple' mode and you may be SOL.

|||Is there anyother way without 3rd party tools.
its a Full Recovery Model.
what is meant by > you may be SOL. <
|||

Download Lumnigents' Log Exlorer, or ApexSQL's SQLLog -or one of the several other good log tools.

It is just not worth any time and effort to try this without their help.

*SOL = "Sorry, Out of Luck"

|||

what is u r backup policy ... if u have full backup and subsequent transaction log backup it is possible to restore to the time just before u deleted the table... Read about STOP AT option in Restore Database...

suppose

(a) 06 Mar 07 you deleted the table at 1000 AM...

(b) U have full backup of 05 Mar 07 20:00 hrs (8 PM)

(c) your database is in Full recovery model

(d) you have no differential backup

(e) you have no TL backup

06 Mar 07 15:00 hrs you found that the data has been delete by mistake and u need to get the databack

(a) first and foremost thing is to take the TL backup

(b) restore the full backup of 05 Mar 07 20:00 hrs as a new database with No REcovery option

(c) Restore the TL with STOPAT 09:55 hrs with Recovery option

this is just a scenario which u can relate and try to get the data back

Madhu

|||I am aware of the stop at option and the scenario you mentioned.
But i am thinking of the worst-worst case scenario. What if you have no backups at all.
you deleted a table.
now u need to recover that table. How do you go about doing that without any backups.

If the history is kept in the transactional logs then it seems correct to assume that you can recover a table dropped 5 minutes before from the transactional logs.

Can we achive such a thing without using third party tools. worst case scenario.

Thanks everyone..|||

In simple terms, as I've tried to communicate to you a couple of times -NO!

If you need to do this, get a third party tool.

|||

Just out of interest, I've noticed that SQL 2005's RESTORE DATABASE command now has a STOPAT option, however BOL doesn't give much information away as to how/under what circumstances this option can be used.

I gave it quick a test earlier on today but couldn't get it to work. Does anyone have any experience of using it? I was wondering if it could possibly be of use to the original poster of this thread (i.e. by taking a full backup now, and if the log hasn't been backed-up, then is it possible to restore to a point in time earlier than the full backup? - my guess is 'probably not', although in theory surely this should be possible by examining the log and rolling back / undoing all uncommitted and comitted transactions to the point in time specified?).

Thanks
Chris

|||

It seems to me that if you make a 'Full Backup' at this juncture, ALL committed activities would be assumed to be in the database, and the transaction log would be truncated. Then not only would the table to permanently 'gone', but any record of it would no longer be in the transaction log.

The missing element is the previous 'Backup' -it does not exist, therefore, the only vestiges of the missing table (if other than page logging) 'might' be in the existing transaction log. Lunmigent's Log explorer would help find out IF there is any chance of recovering the data.

|||

Just one thing, though, taking a full database backup does not truncate the transaction log - this is one of the reasons why I was wondering whether the STOPAT option in the RESTORE DATABASE command would be of use. Agreed that a log-reader tool would be a good tool to invest in / trial if not.

Chris

Pls help me tuning SQL Command

SELECT Fund.FundName, Fm.FundManagerName, Fm.Attention1, Hbo.ValueDate, Hbo.ValueTime, Hbo.FundManagerCode, Hbo.FundCode, Hbo.InstrumentNo, Hbo.IssueCode, Hbo.SecurityCode, Hbo.Guarantor, Hbo.GuaranteeType, Hbo.Maturity, Hbo.CouponRate, Hbo.Face_Amt, Hbo.RedemptionYield, Hbo.N_AmortiseCost, Hbo.N_MarketValue, Hbo.N_AccruedInterest, Hbo.H_MarketYield, Hbo.RemainingDays, Hbo.MainGroup103, Adjust.T_AmortiseCost, Adjust.T_AccruedInterest, Adjust.T_MarketValue,
(select SecLongDesc From SecurityType Where SecCode=Hbo.MainGroup103 ) as SecLongDesc,
(Select SUM(HBOadjust.T_AmortiseCost) From HBOAdjust Where (HBOAdjust.FundCode=Hbo.FundCode)
and (HBOAdjust.ValueDate=Hbo.ValueDate)
and (HBOAdjust.ProductCode<>'100')) As aAmo,
(Select SUM(HBOadjust.T_AccruedInterest) From HBOAdjust Where (HBOAdjust.FundCode=Hbo.FundCode)
and (HBOAdjust.ValueDate=Hbo.ValueDate)
and (HBOAdjust.ProductCode<>'100')) as aInt,
(Select SUM(HBOadjust.T_MarketValue) From HBOAdjust Where (HBOAdjust.FundCode=Hbo.FundCode)
and (HBOAdjust.ValueDate=Hbo.ValueDate)
and (HBOAdjust.ProductCode<>'100')) as aMKT
FROM dbo.HBOData Hbo, dbo.Fund Fund, dbo.FundManager Fm, dbo.HBOAdjust Adjust
WHERE (Hbo.FundCode *= Fund.FundCode)
AND (Hbo.FundManagerCode *= Fm.FundManagerCode)
AND (Hbo.FundCode *= Adjust.FundCode)
AND (Hbo.ValueDate *= Adjust.ValueDate)
AND (Hbo.MainGroup103 *= Adjust.ProductCode)
AND ( (Hbo.FundCode = 'V01001')
AND (Hbo.ValueDate = '020606')
AND (Hbo.Report103 = 'Y') )
ORDER BY Hbo.MainGroup103, Hbo.Guarantor, Hbo.SecurityCode
**************************************************
This is command . I wanna tuning 'cos it used hight CPU and IO.
How Can I tune. Thank youIt would be helpful to know what version of SQL server you are using.|||I may not have this exactly right but if you are using v7 or 2k try:

SELECT Fund.FundName, Fm.FundManagerName, Fm.Attention1, Hbo.ValueDate, Hbo.ValueTime, Hbo.FundManagerCode, Hbo.FundCode
, Hbo.InstrumentNo, Hbo.IssueCode, Hbo.SecurityCode, Hbo.Guarantor, Hbo.GuaranteeType, Hbo.Maturity, Hbo.CouponRate
, Hbo.Face_Amt, Hbo.RedemptionYield, Hbo.N_AmortiseCost, Hbo.N_MarketValue, Hbo.N_AccruedInterest, Hbo.H_MarketYield
, Hbo.RemainingDays, Hbo.MainGroup103, Adjust.T_AmortiseCost, Adjust.T_AccruedInterest, Adjust.T_MarketValue
, St.SecLongDesc as SecLongDesc
, tmp.aAmo
, tmp.aInt
, tmp.aMKT
FROM (Select Adjust.FundCode
, Adjust.ValueDate
, Adjust.ProductCode
, SUM(adjust.T_AmortiseCost) As aAmo
, SUM(adjust.T_AccruedInterest) as aInt
, SUM(adjust.T_MarketValue) as aMKT
FROM dbo.HBOData Hbo
join dbo.HBOAdjust Adjust on Hbo.FundCode = Adjust.FundCode
AND Hbo.ValueDate = Adjust.ValueDate
AND Hbo.MainGroup103 = Adjust.ProductCode
WHERE Hbo.FundCode = 'V01001'
AND Hbo.ValueDate = '020606'
AND Hbo.Report103 = 'Y'
and Adjust.ProductCode <> '100'
group by Adjust.FundCode, Adjust.ValueDate, Adjust.ProductCode) as t1
right join dbo.HBOData Hbo on t1.FundCode = hbo.FundCode
and t1.ValueDate = hbo.ValueDate
and t1.ProductCode = hbo.MainGroup103
left join dbo.SecurityType St on Hbo.MainGroup103 = St.SecCode
left join dbo.Fund Fund on Hbo.FundCode = Fund.FundCode
left join dbo.FundManager Fm on Hbo.FundManagerCode = Fm.FundManagerCode
left join dbo.HBOAdjust Adjust on Hbo.FundCode = Adjust.FundCode
AND Hbo.ValueDate = Adjust.ValueDate
AND Hbo.MainGroup103 = Adjust.ProductCode
WHERE Hbo.FundCode = 'V01001'
AND Hbo.ValueDate = '020606'
AND Hbo.Report103 = 'Y'
ORDER BY Hbo.MainGroup103, Hbo.Guarantor, Hbo.SecurityCode|||Paste your code into a new view and run it. The sql-server (at least the 2000-version!?!) will then rearrange and optimize your sql-statement for you...|||thank you :)

Please help!! Urgent

Hi guys,

we have a database here and something happened which causes a database block. We tried to run the 'sp_who' 'active' command to see the spid which locked the database, and we found out that some transaction is blocking another transaction. The following is the sample data results from the sp_who 'active'

spid ecid status loginame hostname blk dbname cmd

52 0 sleeping HOSTING\SQLMonitor BLUE2 185 tempdb INSERT
53 0 sleeping sa 10.10.10.106 185 mfgq_live SELECT
56 0 sleeping sa 10.10.10.106 175 mfgq_live UPDATE
57 0 sleeping sa 10.10.10.143 185 mfgq_live SELECT

We killed all spid which casuse the blocking, but they are keep on coming.

Does anybody have any idea on what casuses this problem or a teporary solution for this? Please help.

Thx

you need to findout what these process is actually doing.. run

dbcc inputbuffer(185) to see the sql statement which these spid is executing.

madhu