nesting stored procedure

hi I have another question to ever so helpful forum
I am trying to nest stored procedure but I guess it is not the way to do it as it does not work:

CREATE PROCEDURE dbo.GetSharesTransactionsbyDates

(
@.Startdate as char(10),
@.Enddate as char(10)

)

AS

dbo.GetSharesTransactionsData /*tryting to nest sproc*/
WHERE TRANS_DATE BETWEEN @.Startdate and @.Enddate

I get complain that dbo is incorrect syntax

ST.Proc. which I try to call call is basicly a select statement with no parameters:

ALTER PROCEDURE dbo.GetSharesTransactionsData
AS
SELECT TRANS_DATE, TYPE_DESCRIPTION, SHARE_SYMBOL, SHARES_QUANTITY,
ROUND(PRICE_PER_SHARE,2) AS PRICE_PER_SHARE, COMMISSION_VALUE,
STAMP_DUTY,
dbo.GetShareTransactionTotalValue(PRICE_PER_SHARE,STAMP_DUTY,COMMISSION_VALUE,
SHARES_QUANTITY,CASH_AMOUNT, TYPE_DESCRIPTION) AS TOTAL_VALUE,/*calling function*/
ACCOUNT_NAME
FROM SHARES_TRANSACTIONS
WHERE (TYPE_DESCRIPTION = 'Sell') OR
(TYPE_DESCRIPTION = 'Buy') OR
(TYPE_DESCRIPTION = 'Cash Divident')
ORDER BY TRANS_DATEYou cannot really do what you are trying to do:

dbo.GetSharesTransactionsData /*tryting to nest sproc*/
WHERE TRANS_DATE BETWEEN @.Startdate and @.Enddate

You cannot add a WHERE clause like that, and you would need to add EXEC to execute the SP. Can you instead create a SP that accepts two dates as parameters?|||Well I can. The idea is that I first bring all transactions up and then only transactions between the chosen dates so I was trying to avoid repeating the same sql select statement but I can rewrite the original SPROC by adding parameters and additional where clause
Thanks for advice.|||alter the sproc, have it accept paramaters with default values:

create procedure spFoo
@.startDate datetime = '1900-01-01',
@.enddate datetime = '2199-12-31'
AS
--whatever

this way, you have a single sp. existng calls continue to work.|||Aaa, thanks.sql

nesting cursors

Hi, i have to nest 2 cursors together and frankly i'm lost.

Here's what I have so far

CREATE PROCEDURE SeeAllColumns
AS
SET NOCOUNT ON
DECLARE @.strMessage VARCHAR(100)
DECLARE @.strColumn VARCHAR(100)
DECLARE @.strTable VARCHAR(100)
DECLARE @.strCommand VARCHAR(250)

SELECT @.strMessage = 'SELECT ALL TABLES'
PRINT @.strMessage
DECLARE crsTables CURSOR FOR
SELECT
name AS strTable
FROM
sysobjects
WHERE
name LIKE 'T%'

OPEN crsTables
FETCH NEXT FROM crsTables INTO @.strTable
WHILE (@.@.FETCH_STATUS = 0) BEGIN

SELECT @.strMessage = 'SELECT ALL COLUMNS'
PRINT @.strMessage

SELECT @.strCommand = ' SELECT ' + @.strColumn + ' FROM ' + @.strTable

EXECUTE (@.strCommand)

FETCH NEXT FROM crsTables INTO @.strTable

END

DEALLOCATE crsTables

PRINT 'DONE'

What i need to do is select all columns from all tables, but none of the actual data within the columns, just the names. I just don't know where or how to insert the 2nd cursor.
Can someone help me?

Thanks!Frankly ... you lost me somewhere in the middle of the proc ... could you plz explain what you are trying to do here ...
as far as it seems to me .. this proc will generate as many resultsets as the total no of columns in the database

DECLARE crsTables CURSOR FOR
SELECT
name AS strTable
FROM
sysobjects
WHERE
name LIKE 'T%'

should probably be

DECLARE crsTables CURSOR FOR
SELECT
name AS strTable
FROM
sysobjects
WHERE
xtype = 'U'

if you are trying to select all user tables|||declare @.tbl sysname
declare @.clm sysname

declare tbls cursor local fast_forward for
select [TABLE_NAME]
from [INFORMATION_SCHEMA].[TABLES]
where [TABLE_TYPE] = 'BASE TABLE'

open tbls

fetch next from tbls into @.tbl
while @.@.fetch_status = 0
begin
declare cols cursor local fast_forward for
select [COLUMN_NAME]
from [INFORMATION_SCHEMA].[COLUMNS]
where [TABLE_NAME] = @.tbl

open cols

fetch next from cols into @.clm
while @.@.fetch_status = 0
begin
print @.tbl + ':' + @.clm

fetch next from cols into @.clm
end

close cols
deallocate cols

fetch next from tbls into @.tbl
end

close tbls
deallocate tbls|||you can just get by with 1 cursor if you declare it against this statement (alternatively you can use information_schema.columns view):

select cast(object_name(id) as char(128)), colid, [name] from syscolumns
where objectproperty(id, 'istable')=1 and objectproperty(id, 'ismsshipped')=0
order by id, colid

Nested Tables

Hi

I have 3 tables each with a diferent dataset's, is there any way i can nest 2 of the tables inside one of the other table's group's?

Any other way tath i could repeat 3 tables in some grouping method?

Using different datasets inside one top-level table is not currently supported, so you cannot nest the two tables inside the other table. But you can create two subreports and put the two tables in the subreports respectively, then nest the subreports in the other table.|||

That's exactly what i did. Was hopping there was some other way not using SubReports.

Never the less it works fine with Sub-Reports , thank you for your time

Nested Tables

Hi

I have 3 tables each with a diferent dataset's, is there any way i can nest 2 of the tables inside one of the other table's group's?

Any other way tath i could repeat 3 tables in some grouping method?

Using different datasets inside one top-level table is not currently supported, so you cannot nest the two tables inside the other table. But you can create two subreports and put the two tables in the subreports respectively, then nest the subreports in the other table.|||

That's exactly what i did. Was hopping there was some other way not using SubReports.

Never the less it works fine with Sub-Reports , thank you for your time

nested data regions

I am trying to nest 2 data regions. I have two data tables using a
different dataset but using the same data source.
I am trying to nest these and I've put the second data table in the group
footer of the first. How can I have the second data table work properly and
filter by the current group field value of the first data table. The
reporting services documentation is not clear.
Just testing it I am getting errors such as:
c:\inetpub\wwwroot\wcr\wcrreports\rptWCR_.rdl The value expression for the
textbox 'Comment' refers to the field 'Comment'. Report item expressions
can only refer to fields within the current data set scope or, if inside an
aggregate, the specified data set scope.
The reason I moved it into a seperate data region from a sub report is
because I could not find a way for the subreport to NOT "keep together".
Thanks for helping...Also,
Even though I have set the dataset of the second data table to a different
data set than the first table, the only fields that show up in the value
property for text boxes in the second data table are fields from the first
data table.
I am getting frustrated...
"D Witherspoon" <dwitherspoon@.noway.org> wrote in message
news:Oc76I6eGFHA.3072@.tk2msftngp13.phx.gbl...
>I am trying to nest 2 data regions. I have two data tables using a
>different dataset but using the same data source.
> I am trying to nest these and I've put the second data table in the group
> footer of the first. How can I have the second data table work properly
> and filter by the current group field value of the first data table. The
> reporting services documentation is not clear.
> Just testing it I am getting errors such as:
> c:\inetpub\wwwroot\wcr\wcrreports\rptWCR_.rdl The value expression for the
> textbox 'Comment' refers to the field 'Comment'. Report item expressions
> can only refer to fields within the current data set scope or, if inside
> an aggregate, the specified data set scope.
>
> The reason I moved it into a seperate data region from a sub report is
> because I could not find a way for the subreport to NOT "keep together".
> Thanks for helping...
>
>|||Nested data regions have to share the same data set. The closest you can get
is to use subreports - they are independent reports and will retrieve the
data on their own (you can pass parameters to subreports).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"D Witherspoon" <dwitherspoon@.noway.org> wrote in message
news:Oz%23MDAfGFHA.3876@.TK2MSFTNGP14.phx.gbl...
> Also,
> Even though I have set the dataset of the second data table to a different
> data set than the first table, the only fields that show up in the value
> property for text boxes in the second data table are fields from the first
> data table.
> I am getting frustrated...
> "D Witherspoon" <dwitherspoon@.noway.org> wrote in message
> news:Oc76I6eGFHA.3072@.tk2msftngp13.phx.gbl...
>>I am trying to nest 2 data regions. I have two data tables using a
>>different dataset but using the same data source.
>> I am trying to nest these and I've put the second data table in the group
>> footer of the first. How can I have the second data table work properly
>> and filter by the current group field value of the first data table. The
>> reporting services documentation is not clear.
>> Just testing it I am getting errors such as:
>> c:\inetpub\wwwroot\wcr\wcrreports\rptWCR_.rdl The value expression for
>> the textbox 'Comment' refers to the field 'Comment'. Report item
>> expressions can only refer to fields within the current data set scope
>> or, if inside an aggregate, the specified data set scope.
>>
>> The reason I moved it into a seperate data region from a sub report is
>> because I could not find a way for the subreport to NOT "keep together".
>> Thanks for helping...
>>
>

Nested Cursors

What is the best way to nest cursors?

This code does not seem to be returning me all of the data.

Code Snippet

DECLARE element_Cursor CURSOR FOR

SELECT ElementTypeRecNo

FROM dbo.tblTemplateElementType

where TemplateRecno = @.TemplateRecNo

OPEN element_cursor

FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno

--delete from tblElementCPO

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.Count = count (*)

from tblProjTypeSet

where ProjRecno = @.ProjRecNo

if @.Count > 0

begin

select @.ProjTypeRecno = ProjTypeRecno

from tblProjTypeSet

where ProjRecno = @.ProjRecNo

select @.Count = count (*)

FROM dbo.tblElementTypeDep

where TemplateRecno = @.TemplateRecNo

and ProjTypeRecno = @.ProjTypeRecNo

if @.Count > 0

begin

DECLARE ElementTypeDep_Cursor CURSOR FOR

SELECT ElementTypeDepRecNo, PreElementTypeRecNo,

PostElementTypeRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates,

Description

FROM tblElementTypeDep

WHERE (TemplateRecNo = @.TemplateRecNo)

AND (ProjTypeRecNo = @.ProjTypeRecno)

AND (PreElementTypeRecNo = @.ElementTypeRecno)

OPEN ElementTypeDep_cursor

FETCH NEXT FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementTypeRecNo,

@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,

@.Description

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.PreElementRecNo = ElementRecno

from tblElementCPO

where ProjRecNo = @.ProjRecNo

and IssueRecno = @.IssueRecNo

and ElementTypeRecno = @.PreElementTypeRecno

if @.PreElementRecno is not null

begin

select @.PostElementRecNo = ElementRecno

from tblElementCPO

where ProjRecNo = @.ProjRecNo

and IssueRecno = @.IssueRecNo

and ElementTypeRecno = @.PostElementTypeRecno

if @.PostElementRecno is not null

begin

select @.Count = count (*)

from tblElementDepCPO

where ElementTypeDepRecno = @.ElementTypeDepRecno

and PreElementRecNo = @.PreElementRecNo

and PostElementRecno = @.PostElementRecno

if @.Count = 0

begin

INSERT INTO tblElementDepCPO

(ElementTypeDepRecNo, PreElementRecNo,

PostElementRecNo, ElapsedTimeDueDates,

ElapsedTimePlanDates, Description,

ChangeDate, ChangePerson)

VALUES (@.ElementTypeDepRecno, @.PreElementRecNo,

@.PostElementRecno, @.ElapsedTimeDueDates,

@.ElapsedTimePlanDates, @.Description,

GETDATE(), CURRENT_USER)

end

select @.Count = count (*)

from tblElementAttemptCPO

where ElementRecNo = @.PostElementRecNo

if @.Count = 0

begin

select @.Count = count (*)

from tblElementAttemptCPO

where ElementRecNo = @.PostElementRecNo

if @.Count = 0

begin

select @.NextPlanDate = ProjectedCompletionDate,

@.NextDueDate = RequiredCompletionDate

from tblElementAttemptCPO

where ElementRecno = @.PreElementRecNo

end

else

begin

select @.NextPlanDate = @.StartDate

select @.NextDueDate = @.StartDate

end

select @.NextPlanDate =

dbo.fncAddBusinessDays (@.NextPlanDate, @.ElapsedTimePlanDates)

select @.NextDueDate =

dbo.fncAddBusinessDays (@.NextDueDate, @.ElapsedTimePlanDates)

insert into tblElementAttemptCPO (ElementRecno,

ProjectedCompletionDate, RequiredCompletionDate,

ProjectedStartDate, RequiredStartDate,

ActualStartDate, ActualCompletionDate, AttemptNum,

IsCompleted, IsStarted, ResponsibleRoleTypeRecno,

ChangeDate, ChangePerson)

values (@.PostElementRecno,

@.NextPlanDate, @.NextDueDate,

'1/11/1900', '1/11/1900',

'1/11/1900', '1/11/1900', 0,

0, 0, 0,

GETDATE(), CURRENT_USER)

end

end

end

FETCH NEXT

FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementTypeRecNo,

@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,

@.Description

END

CLOSE elementTypeDep_Cursor

DEALLOCATE elementTypeDep_Cursor

end

FETCH NEXT FROM element_Cursor into @.ElementTypeRecno

END

CLOSE element_Cursor

DEALLOCATE element_Cursor

end

There is a single insert statement hidden within the cursors. Since there is no select statement, there wouldn't be any data returned. Exactly what are you trying to return?

Also, I suggest you post DDL+sample data (i.e. insert statement)+expected output here. We might be able to help draft a non-cursor version.

|||As oj implied, cursors are extremely taxing to a SQL Server and generally should be avoided if possible. Is some cases, it's not possible. But if you'll post the info that oj requested, perhaps this is a case where they can be avoided.

Joe

Nested Cursors

What is the best way to nest cursors?

This code does not seem to be returning me all of the data.

Code Snippet

DECLARE element_Cursor CURSOR FOR

SELECT ElementTypeRecNo

FROM dbo.tblTemplateElementType

where TemplateRecno = @.TemplateRecNo

OPEN element_cursor

FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno

--delete from tblElementCPO

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.Count = count (*)

from tblProjTypeSet

where ProjRecno = @.ProjRecNo

if @.Count > 0

begin

select @.ProjTypeRecno = ProjTypeRecno

from tblProjTypeSet

where ProjRecno = @.ProjRecNo

select @.Count = count (*)

FROM dbo.tblElementTypeDep

where TemplateRecno = @.TemplateRecNo

and ProjTypeRecno = @.ProjTypeRecNo

if @.Count > 0

begin

DECLARE ElementTypeDep_Cursor CURSOR FOR

SELECT ElementTypeDepRecNo, PreElementTypeRecNo,

PostElementTypeRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates,

Description

FROM tblElementTypeDep

WHERE (TemplateRecNo = @.TemplateRecNo)

AND (ProjTypeRecNo = @.ProjTypeRecno)

AND (PreElementTypeRecNo = @.ElementTypeRecno)

OPEN ElementTypeDep_cursor

FETCH NEXT FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementTypeRecNo,

@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,

@.Description

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.PreElementRecNo = ElementRecno

from tblElementCPO

where ProjRecNo = @.ProjRecNo

and IssueRecno = @.IssueRecNo

and ElementTypeRecno = @.PreElementTypeRecno

if @.PreElementRecno is not null

begin

select @.PostElementRecNo = ElementRecno

from tblElementCPO

where ProjRecNo = @.ProjRecNo

and IssueRecno = @.IssueRecNo

and ElementTypeRecno = @.PostElementTypeRecno

if @.PostElementRecno is not null

begin

select @.Count = count (*)

from tblElementDepCPO

where ElementTypeDepRecno = @.ElementTypeDepRecno

and PreElementRecNo = @.PreElementRecNo

and PostElementRecno = @.PostElementRecno

if @.Count = 0

begin

INSERT INTO tblElementDepCPO

(ElementTypeDepRecNo, PreElementRecNo,

PostElementRecNo, ElapsedTimeDueDates,

ElapsedTimePlanDates, Description,

ChangeDate, ChangePerson)

VALUES (@.ElementTypeDepRecno, @.PreElementRecNo,

@.PostElementRecno, @.ElapsedTimeDueDates,

@.ElapsedTimePlanDates, @.Description,

GETDATE(), CURRENT_USER)

end

select @.Count = count (*)

from tblElementAttemptCPO

where ElementRecNo = @.PostElementRecNo

if @.Count = 0

begin

select @.Count = count (*)

from tblElementAttemptCPO

where ElementRecNo = @.PostElementRecNo

if @.Count = 0

begin

select @.NextPlanDate = ProjectedCompletionDate,

@.NextDueDate = RequiredCompletionDate

from tblElementAttemptCPO

where ElementRecno = @.PreElementRecNo

end

else

begin

select @.NextPlanDate = @.StartDate

select @.NextDueDate = @.StartDate

end

select @.NextPlanDate =

dbo.fncAddBusinessDays (@.NextPlanDate, @.ElapsedTimePlanDates)

select @.NextDueDate =

dbo.fncAddBusinessDays (@.NextDueDate, @.ElapsedTimePlanDates)

insert into tblElementAttemptCPO (ElementRecno,

ProjectedCompletionDate, RequiredCompletionDate,

ProjectedStartDate, RequiredStartDate,

ActualStartDate, ActualCompletionDate, AttemptNum,

IsCompleted, IsStarted, ResponsibleRoleTypeRecno,

ChangeDate, ChangePerson)

values (@.PostElementRecno,

@.NextPlanDate, @.NextDueDate,

'1/11/1900', '1/11/1900',

'1/11/1900', '1/11/1900', 0,

0, 0, 0,

GETDATE(), CURRENT_USER)

end

end

end

FETCH NEXT

FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementTypeRecNo,

@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,

@.Description

END

CLOSE elementTypeDep_Cursor

DEALLOCATE elementTypeDep_Cursor

end

FETCH NEXT FROM element_Cursor into @.ElementTypeRecno

END

CLOSE element_Cursor

DEALLOCATE element_Cursor

end

There is a single insert statement hidden within the cursors. Since there is no select statement, there wouldn't be any data returned. Exactly what are you trying to return?

Also, I suggest you post DDL+sample data (i.e. insert statement)+expected output here. We might be able to help draft a non-cursor version.

|||As oj implied, cursors are extremely taxing to a SQL Server and generally should be avoided if possible. Is some cases, it's not possible. But if you'll post the info that oj requested, perhaps this is a case where they can be avoided.

Joe

Nested cursor

Is there a better way to nest cursors in SQL Server (I'm using 2005). Due to the nature of the solution a set-based approach will not work. Essentially I have a list of People. Each Person has Multiple Properties, so at the moment I have something along the lines of ...

Declare PersonCursor
Open PersonCursor
Fetch Next Person Cursor
While FetchStatus <> -1
Declare PropertyCursor (for Select according to current values in PersonCursor)
Open PropertyCursor
Fetch Next PropertyCursor
While @.@.FetchStatus <> -1
Process Each Property
Fetch Next PropertyCursor
End
Close PropertyCursor
Deallocate PropertyCursor
End
Close PersonCursor
DeallocatePersonCursor

I hope it's clear. What I'm asking is (and I don't think it's possible) is that is there a method by which I can Declare the PropertyCursor once, but open it multiple times using different values? I haven't used SQL Server cursors much but have seen this done in other RDBMS's.

Even hints on another angle to approach from would be useful. As I say, the steps involved at the "Process Each Property" stage are not really suited to a set-based approach, as I have to maintain counters, and dynamically build SQL unique to each iteration dependent on the counter values.

Any thoughts much appreciated.

Greg.

If the problem is not suited to a set based approach, why use SQL Server? Surely this would be easier to perform at the client side? You could still have the "process each property" code in a stored procedure if you like, but have it called from the client side instead. Can you use a set based appraoch using a CASE statement? CASE statments allow you to apply quite complex logic using a set based approach as it can do something different for each row returned from the query based on the value of that particular row.

HTH

|||We're using SQL Server mainly because

a) it's a heavily DB intensive process, and

b) in this instance, there is no client - the process is a Start of Financial Year billing process

We're using SQL Server stored procs to generate the billing data, and then SSIS to extract it to be printed.

Greg.

|||

Perhaps if you post more information about your specific calculation we can tell you how to do it using a set-based approach.

Oftentimes, this is actually possible (or we, as implementers, want to make that statement true since cursoring even in one level is often not efficient compared to the equivalent set-based query).

Conor Cunningham

SQL Server Query Optimization Development Lead

|||

A solution is to call in your outer cursor loop a sp where you can handle the property cursor.

|||

You can have the cursors nested, without a stored procedure, but you need to declare/open/fetch/close/deallocate the inner cursor within the WHILE loop of the outer cursor.

Good luck, Dean

Nest transactions in SQLServer

hi guys:
i am doing a hard work of migrating programs from Oracle to SQL-Server.
i encounte a problem about transaction which hardly hurt my heart (forgive
my poor english first ... :-) )
you know,there is such a usage in oracle:
SAVEPOINT xxx
doing things1...
doing things2...
if error in doing things2 then
ROLLBACKTOSAVEPOINT xx
end if;
so, if things 2 failed, the data altered by things1 is guaranteed to be
rollback.
but when i change it to fit sql-server, i was happily wrote such codes:
BEGIN TRANS... //must
...
...
BEGIN TRANS... //SAVEPOINT xxx
doing things1...
doing things2...
if error in doing things2 then
ROLLBACK TRANS... //ROLLBACKTOSAVEPOINT xx
end if;
..
...
COMMIT TRANS.
yes, i got a error: 'Cannot start more transactions on this session' (oh,
these codes are under ado in delphi)
i find the answer in msdn
CAUSE
By design, OLE DB Provider for SQL Server does not allow nested
transactions.
http://support.microsoft.com/defaul...kb;en-us;316872
but,i think, such nest transcations (or same other trick equal to oracle
save point) is very needed in general business process.
how to solve this problem?
my best wishes.Hi
Have you looked up SAVE TRANSACTION in BOL?
John
"MaHahaXixi" wrote:

> hi guys:
> i am doing a hard work of migrating programs from Oracle to SQL-Server.
> i encounte a problem about transaction which hardly hurt my heart (forgi
ve
> my poor english first ... :-) )
> you know,there is such a usage in oracle:
> SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACKTOSAVEPOINT xx
> end if;
> so, if things 2 failed, the data altered by things1 is guaranteed to be
> rollback.
> but when i change it to fit sql-server, i was happily wrote such codes:
> BEGIN TRANS... //must
> ...
> ...
> BEGIN TRANS... //SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACK TRANS... //ROLLBACKTOSAVEPOINT xx
> end if;
> ...
> ...
>
> COMMIT TRANS.
> yes, i got a error: 'Cannot start more transactions on this session' (oh,
> these codes are under ado in delphi)
> i find the answer in msdn
> CAUSE
> By design, OLE DB Provider for SQL Server does not allow nested
> transactions.
> http://support.microsoft.com/defaul...kb;en-us;316872
>
> but,i think, such nest transcations (or same other trick equal to oracle
> save point) is very needed in general business process.
> how to solve this problem?
> my best wishes.
>
>|||hi john:
thank u for ur response.
what do u mean by BOL' sorroy, i am just a greenhand.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:E702B8AB-3AD4-4AE9-BA9A-95D6AE336046@.microsoft.com...
> Hi
> Have you looked up SAVE TRANSACTION in BOL?
> John
> "MaHahaXixi" wrote:
>
SQL-Server.
(forgive
be
codes:
(oh,|||oh... yes i got the answer
there are
save trans {savepoint}
rollack trans {savepoint}
in t-sql too.
thanks all the same
"MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message
news:d3fh2p$vuo$1@.mail.cn99.com...
> hi guys:
> i am doing a hard work of migrating programs from Oracle to SQL-Server.
> i encounte a problem about transaction which hardly hurt my heart
(forgive
> my poor english first ... :-) )
> you know,there is such a usage in oracle:
> SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACKTOSAVEPOINT xx
> end if;
> so, if things 2 failed, the data altered by things1 is guaranteed to be
> rollback.
> but when i change it to fit sql-server, i was happily wrote such codes:
> BEGIN TRANS... //must
> ...
> ...
> BEGIN TRANS... //SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACK TRANS... //ROLLBACKTOSAVEPOINT xx
> end if;
> ...
> ...
>
> COMMIT TRANS.
> yes, i got a error: 'Cannot start more transactions on this session' (oh,
> these codes are under ado in delphi)
> i find the answer in msdn
> CAUSE
> By design, OLE DB Provider for SQL Server does not allow nested
> transactions.
> http://support.microsoft.com/defaul...kb;en-us;316872
>
> but,i think, such nest transcations (or same other trick equal to oracle
> save point) is very needed in general business process.
> how to solve this problem?
> my best wishes.
>
>|||thank you john, i got it .yes, save transactions
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:E702B8AB-3AD4-4AE9-BA9A-95D6AE336046@.microsoft.com...
> Hi
> Have you looked up SAVE TRANSACTION in BOL?
> John
> "MaHahaXixi" wrote:
>
SQL-Server.
(forgive
be
codes:
(oh,|||BOL = SQL Server Books Online, found in the SQL Server program group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message news:d3ftsf$137e$1@.mail.cn99.com...

> hi john:
> thank u for ur response.
> what do u mean by BOL' sorroy, i am just a greenhand.
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:E702B8AB-3AD4-4AE9-BA9A-95D6AE336046@.microsoft.com...
> SQL-Server.
> (forgive
> be
> codes:
> (oh,
>|||Hi
You may find these articles useful:
http://www.microsoft.com/resources/...r />
0761.mspx
http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
John
MaHahaXixi wrote:
> oh... yes i got the answer
> there are
> save trans {savepoint}
> rollack trans {savepoint}
> in t-sql too.
> thanks all the same
> "MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message
> news:d3fh2p$vuo$1@.mail.cn99.com...
SQL-Server.
> (forgive
to be
codes:
session' (oh,
oracle|||thanks a lot :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23uTmv2zPFHA.3928@.TK2MSFTNGP09.phx.gbl...
> BOL = SQL Server Books Online, found in the SQL Server program group.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message
news:d3ftsf$137e$1@.mail.cn99.com...
to
oracle
>|||thank you! very useful to me!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1113307105.002786.136380@.f14g2000cwb.googlegroups.com...
> Hi
> You may find these articles useful:
>
http://www.microsoft.com/resources/...art2/c0761.mspx

> http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
> John
> MaHahaXixi wrote:
> SQL-Server.
> to be
> codes:
> session' (oh,
> oracle
>

nest subreport in a tables detail section

I'm running ssrs 2k and need to put a sub report in a table row. I ran a
simple test by creating a sub report with no data source and just has
textboxes with different colored backgrounds and named it zColors.rdl. I
dragged zColors into a table cell in the detail section. I also dragged a
valid data field into another table cell and ran the report. I can see the
data cell but zColors does not show. I even resized the table row big
enough to assure being able to see the contents of zColors.
Is this even possible in ssrs 2k? and if so, how do I do this?
Thanks.
--
moondaddy@.newsgroup.nospamHello moondaddy,
It is possible.
Instead of increase the main report table cell size, you could reduce the
subreport body size to only include a textbox.
This will be OK.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks its working now.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:OWtVsdm%23HHA.5604@.TK2MSFTNGHUB02.phx.gbl...
> Hello moondaddy,
> It is possible.
> Instead of increase the main report table cell size, you could reduce the
> subreport body size to only include a textbox.
> This will be OK.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Moondaddy,
My pleasure. If you have any questions, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.