Question with SQL String

I have this code below to parse the string ''00120212~pendin~mod pen~ria te~3/6/2007 3:51:49 pm'' into Multiple columns.

Here is the code, but the code is splititng the columns incorrectly.

I need it to appear as

col1 col2 col3 col4 col5
00120212 pendin mod pen ria te 3/6/2007 3:51:49 pm
Can someone pl assist with this code below.

-

DECLARE @.str varchar(8000)
SET @.str = '00120212~pendin~mod pen~ria te~3/6/2007 3:51:49 pm'

DECLARE @.columns TABLE (
col1 varchar(8000)
,col2 varchar(8000)
,col3 varchar(8000)
)

SET @.str = LTrim(RTrim(@.str))

DECLARE @.col1 int
,@.col2 int
,@.col3 int

SET @.col1 = CharIndex('~', @.str, 0)
SET @.col2 = CharIndex('~', @.str, @.col1 + 1)
SET @.col3 = CharIndex('~', @.str, @.col2 + 1)

INSERT INTO @.columns
VALUES (
SubString(@.str, 2, @.col1 - 2)
,SubString(@.str, @.col1 + 3, Len(@.str) - @.col2 - 4)
,SubString(@.str, @.col2 + 3, Len(@.str) - @.col3 - 1)
)

SELECT * FROM @.columns

You may find Jen Suessmeyer's Split function to be very useful for this situation.

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||

Please check out the link below:

http://www.sommarskog.se/arrays-in-sql.html

It contains few TVFs that can be used to split a string based on a delimiter. You can use it along with PIVOT for example to get the individual values easily like:

SELECT p.[1], p.[2], p.[3], p.[4], p.[5]

FROM split_str(@.str, '~') AS t

PIVOT (min(t.value) for t.idx in ([1], [2], [3], [4], [5])) as p

Alternatively, you can do these type of operations easily on the client side and send the values individually. This way you can use SQL for what it is supposed to do.

|||

This is by code below, Delimiter is ~. I need to split the values, into multilple field , based on the ~ being the delimiter. How do I loop throu this string, until the end of the string, and then split it one - by-one into multiple fileds? PL ADVISE?

Declare @.Str Varchar(1000),@.I Int

set @.str='0001232~PENDING~MOD PENDING~Trad Jane~3/29/2007 5:03:30 PM~0001232~PENDING~MODIFICATION PENDING~ Jane Delder~3/29/2007 5:05:06 PM~0001232~PENDING~Approved~ Mon Savy~3/29/2007 5:05:27 PM~0001232~PEND'
SET @.str = LTrim(RTrim(@.str))

DECLARE @.columns TABLE (
LoanNum varchar(8000)
,ConvertedFromStatus varchar(8000)
,ConvertedToStatus varchar(8000)
,ConvertedName varchar(8000)
,StatusChangedDate text
)

begin
Begin
DECLARE @.col1 int
,@.col2 int
,@.col3 int,
@.col4 int

SET @.col1 = CharIndex('~', @.str, 0)
SET @.col2 = CharIndex('~', @.str, @.col1 + 1)
SET @.col3 = CharIndex('~', @.str, @.col2 + 1)
SET @.col4 = CharIndex('~', @.str, @.col3 + 1)
--print @.col1
--print @.col2
--print @.col3
--print @.col4

INSERT INTO @.columns
VALUES (
Left(@.Str, @.Col1 - 1),
SubString(@.Str, @.Col1 + 1, @.col2 - @.Col1 - 1),
SubString(@.Str, @.Col2 + 1, @.col3 - @.Col2 - 1),
SubString(@.Str, @.Col3 + 1, @.col4 - @.Col3 - 1),
SubString(@.Str, @.Col4 + 1, @.col4 )
)
End

--Select @.I = 0

End
SELECT * FROM @.columns

|||Does the Split function I previously posted for you not work properly?

Question to index (SQL 2000)

I have got a question concerning multiple indices on one table.
We have an table where two colums combined build a unique index. Does this
index also help to speed up links where only one of the columns is involved,
or does it make senses to define a separate index for that column?
Example:
Fields a and b build an unique index.
Field b is involved in links to other tables.
Index on field b feasible?
TIA,
NorbertNorbert Meiss wrote:
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is involve
d,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
> TIA,
> Norbert
The combined index on a+b will speed up lookups on field a, but not
necessarily on b. I say "not necessarily" because any lookup on b will
perform a scan, but that scan can now be done on the index instead of
the table.
It is certainly feasible to create a second index that uses b+a.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:99ACEA45-2216-4B07-B5FF-0C4C82A028A4@.microsoft.com...
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is
involved,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
Yes and no.
If you can do an index on B+A that should cover both B and A.
(though if A has a higher exclusivity than B, A+B and then a separate index
for B MIGHT be better.)

> TIA,
> Norbert

Question to index (SQL 2000)

I have got a question concerning multiple indices on one table.
We have an table where two colums combined build a unique index. Does this
index also help to speed up links where only one of the columns is involved,
or does it make senses to define a separate index for that column?
Example:
Fields a and b build an unique index.
Field b is involved in links to other tables.
Index on field b feasible?
TIA,
NorbertNorbert Meiss wrote:
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is involved,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
> TIA,
> Norbert
The combined index on a+b will speed up lookups on field a, but not
necessarily on b. I say "not necessarily" because any lookup on b will
perform a scan, but that scan can now be done on the index instead of
the table.
It is certainly feasible to create a second index that uses b+a.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:99ACEA45-2216-4B07-B5FF-0C4C82A028A4@.microsoft.com...
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is
involved,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
Yes and no.
If you can do an index on B+A that should cover both B and A.
(though if A has a higher exclusivity than B, A+B and then a separate index
for B MIGHT be better.)
> TIA,
> Norbert

Question regarding a View that is taking long time to process

Good afternoon everyone, I have written a view that pulls customer demographic infomration as well as pulling data from multiple scalar-valued functions. I am using this view to pull and send data from one database to another in the same SQL server. The problem that I am having is that I am running this import as a scheduled job in windows. The job is taking almost 24 hours to complete this task. The total number of records that are being pulled is around 21,000+. I have tried removing the functions from the view and it only takes the view 20 seconds to pull the demographic information from the same 21,000+ records but when I add the function calls this is where the time to complete goes through the roof. Has anyone encountered this before if so what would you suggest doing? Any help would be appreciated.

Here is the syntax for my view:

SELECT TOP 100PERCENT CUS_EMAILAS Email, CUS_CUSTNUMAS MemberID, CUS_PREFIXAS Prefix, CUS_FNAMEAS FirstName, CUS_LNAMEAS LastName, CUS_SUFFIXAS Suffix, CUS_TITLEAS Title, CUS_STATEAS State, CUS_COUNTRYAS Country, CUS_ZIPAS ZipCode, CUS_SEXAS Gender,CAST(CUS_DEMCODEAAS nvarchar(20)) +',' +CAST(CUS_DEMCODEBAS nvarchar(20)) +',' +CAST(CUS_DEMCODECAS nvarchar(20)) +',' +CAST(CUS_DEMCODEDAS nvarchar(20))AS DemoCodes, dbo.GetSubScribedDateMLA(CUS_CUSTNUM, CUS_EMAIL)AS MLASubscribedDate, dbo.GetSubScribedDateMLP(CUS_CUSTNUM, CUS_EMAIL)AS MLPSubscribedDate, dbo.GetSubScribedDateLDC(CUS_CUSTNUM, CUS_EMAIL)AS LDCSubscribedDate, dbo.GetMLAExpiration(CUS_CUSTNUM, CUS_EMAIL)AS MLASubExpireDate, dbo.GetMLPExpiration(CUS_CUSTNUM, CUS_EMAIL)AS MLPSubExpireDate, dbo.GetLDCExpiration(CUS_CUSTNUM, CUS_EMAIL)AS LDCSubExpireDate, dbo.IsProspect(CUS_CUSTNUM, CUS_EMAIL)AS AGMProspect, dbo.IsCurrentCustomer(CUS_CUSTNUM, CUS_EMAIL)AS AGMCurrentCustomer, dbo.IsMLAMember(CUS_CUSTNUM, CUS_EMAIL)AS MLAMember, dbo.IsMLPMember(CUS_CUSTNUM, CUS_EMAIL)AS MLPMember, dbo.IsLDCMember(CUS_CUSTNUM, CUS_EMAIL)AS LDCMember, dbo.CalculateTotalRevenue(CUS_CUSTNUM, CUS_EMAIL)AS AGMTotalRevenue, dbo.GetPubCodes(CUS_CUSTNUM, CUS_EMAIL)AS ProductsPurchased, dbo.GetEmailType(CUS_CUSTNUM, CUS_EMAIL, CUS_RENT_EMAIL)AS EmailType, CUS_COMPANYAS Company, CUS_CITYAS CityFROM dbo.CUSWHERE (CUS_EMAILISNOT NULL)AND (CUS_EMAIL <>'')AND (CUS_EMAIL_VALID ='Y')AND (CUS_EMAILLIKE'%@.%.%')AND (CUS_RENT_EMAIL ='Y'OR CUS_RENT_EMAIL ='R'OR CUS_RENT_EMAIL ='I')AND (CHARINDEX(' ', CUS_EMAIL) = 0)AND (CUS_EMAILNOT LIKE'@.%')

Thanks in advance

Michael Reyeros

Windows based functions are not for moving data between databases, you use SQL Server Agent Jobs for that. Run a search for SQL Server Agent Job in SQL Server BOL (books online). Hope this helps.

|||Well actually what I have done is create a dtsx package in SQL 2005. This package is calling the above view to pull all the records from one database table and then for each one ofthe records return I am sending it to a stored procedure in another database. I then built a windows application that simply calls the dtsx package and runs it. This exe I have set it to run in the windows scheduler as a scheduled task.|||

Look for an extended stored proc called xp_cmdshell, it is disabled by default in 2005 enable it and use the instructions in the thread below and post again if it is not working.

http://forums.asp.net/thread/1358665.aspx

|||

But I am not sure exactly what this is supposed to do or how I hsould implement this in my siutation?

|||That is what is used to move data between database in all platforms with DTS not Windows service, it can move gigs in one day for you nothing works better, if it is there I would have known. Hope this helps.|||If I need to schedule this to run on a nightly basis, how would I run this and from where?|||

The first two links show you how to use the Agent to schedule Jobs for most admin tasks in SQL Server. The last link shows you system stored procedures you can use to create schedules and Jobs. I worked for a bank that used it to move deposits four hours a day five days a week your data is very small. Post again if you still need help, get it work and you can automate most operations in your application. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms139805.aspx

http://msdn2.microsoft.com/en-us/library/ms141701.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_00_519s.asp

|||OK the only problem that I have is that two of the packages that I am running rely on an ODBC connection to a very antiquated system, QuickFill, that is being used here in the office. When I try to run the job in SQL server as an agent job this is not allowed because the ODBC connection relies on two mapped drives and this does not seem to be allowed in SQL server.|||

Try using UNC instead of mapped drive you can create a proxy account with your admin context for the Agent to run it and the permissions are covered in the links in the thread I gave you. Try the link below and read up one how to get the correct mapping with ODBC. Sorry forgot to add the link. Hope this helps.

http://www.sqlteam.com/item.asp?ItemID=125

sql

Question on passing multi-value parameter for multiple branches

Hello. We are using asp .net and reporting services, and trying to pass a multi-value parameter into reporting services that will show data for multiple branches.

Dim paramList As New Generic.List(Of Microsoft.Reporting.WebForms.ReportParameter)

paramList.Add(New Microsoft.Reporting.WebForms.ReportParameter("BranchNumber", 1))

ReportViewer1.ServerReport.SetParameters(paramList)

pInfo = ReportViewer1.ServerReport.GetParameters()

Let me know if you have any suggestions!

Thanks.

Did you try passing "1,2,3,4" instead of "1" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||We tried that also, without any luck.|||

We have already figured out how to move between seperate branches, we just want a corporate option that will show all branches as a whole.

Any ideas?

Thanks

|||There is no way to post the "All" option, you will either have to pass all values to display or use an additional (hidden) parametert which uses the "All" option behind the scenes, something like:

Where SomeVar IN (@.TheValues) OR @.TheMagicParameter = 1 (Where TheMagicParameter is the magic hidden parameter)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Question on Multiple Parent-Child Hierarchies

Hi all,

I have a business scenario which is shown in the below hierachy which has 4 levels. Typically the fact table will hold data at the Project manager Level i.e Level 4. I want to implement a solution in SSAS 2005 such that it can create multiple parent child hierarchy

Can you please provide me with a data model whcih can do this?

I am aware of Many to Many dimensions but not sure how it can be implemented in SSAS 2005 based on the person logging into the cube i.e it will have an Employee Table with LOGIN ID.

Scenario 1:

Mgr1 can execute projects under Business CIO1 and Business CIO2, i.e. cross hierarchy is also possible.

Scenario 2:

A CIO at level 2 i.e. CIO1 can have horizontal level access of CIO2 also even though members of CIO2 are not reporting to CIO1.

CEO

/ \

/ \

/ \

CIO1 CIO2

/ \

/ \

/ \

Business CIO1 Business CIO2

/ \

/ \

/ \

Mgr1 Mgr2

Please treat this as urgent as i need to implement this as soon as possible for the security design

Regards

Sai

I do not fully understand the diagram you present in your question. Are you saying you have a member who may have multiple parents?

Regarding parent-child hierarchies in general, you can have only 1 per dimension. This is enough for most users. If you have a fixed number of levels and data associated with just the leaf-level of the hierarchy, you can implement your solution as a standard hierarchy. If you truly have two parent-child hierarchies, my advice would be to implement two dimensions, each with a single parent-child hierarchy.

B.

|||

Hi Bryan,

Yes am saying about a member having multiple parents. Can you please let me know how this can be achieved and based on the login ID I need to filter the members and fact data. Do you have a solution or any data model which can support this.

Regards

Sai Krishna

|||

If a member is part of independent parent-child hiearchies, you would need to implement two dimensions. Each dimension would house one of the parent-child hierarchies.

If a member may have multiple parents and those parents may have multiple parents and there is no logical/topical separation between these relationships, I'm not sure of a way to proceed. This kind of relationship is referred to as a network relationship and I do not believe SSAS supports this.

B.

Question on Log Backup and NORECOVERY

Dear experts,
I am confused. I always thought that using NORECOVERY was essential when
restoring multiple transactional log backups in a row and I always thought
this was due to the fact that without NORECOVERY the transactional log
backups wouldn't "fit" to each other, because of potential rollbacks
happening in recovery. E.g. I have a tlb (transactional log backup, for
brevity's sake) ending at LSN 124. I restore with recovery and transaction
106 isn't commited before LSN 124, so it gets rolled back. Now I try to
restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
because transaction 106 is commited at LSN 145 after all! I thought this is
why you have to use NORECOVERY.
BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
Server 2005 Implementation and Maintenance" that made me think otherwise
(pg. 419): "A log backup backs up the active log. It starts at the Log
Sequence Number (LSN) at which the previous log backup completed. SQL Server
then backs up all subsequent transactions UNTIL THE BACKUP ENCOUNTERS AN
OPEN TRANSACTION." (emphasis mine)
Now if this is so, why is there any need for a rollback after restoring a
tlb with recovery anyway? All transactions included in the tlb are not open
(i.e. commited or rolled backed), so the worst thing that could happen is
the need for a rollforward, in case the db isn't consistent with the tlb. So
why do we need NORECOVERY?
Somewhat related bonus question: What portion of the log is exactly backed
up when I do a FULL backup? It's only the part after the oldest open
transaction, right?
Thank you a lot
Nils LoeberHi Nils
This is not correct, but I appreciate your recognition of the quality of the
document as a whole, even though there are some errors.
Books Online is correct in stating that during a log backup:
the log is backed up from the last successfully executed log backup to
the current end of the log.
I have no idea where the comment about open transaction came from.
When you do a full backup, SQL Server records the current LSN when the full
backup starts. When the full backup is over, the new current LSN is records.
All the log records between the two recorded LSNs are then backed up. So it
basically captures all other changes that were going on while the backup was
taking place. (It may start much later than the oldest open transaction.)
HTH
Kalen Delaney, SQL Server MVP
"Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
> Dear experts,
> I am confused. I always thought that using NORECOVERY was essential when
> restoring multiple transactional log backups in a row and I always thought
> this was due to the fact that without NORECOVERY the transactional log
> backups wouldn't "fit" to each other, because of potential rollbacks
> happening in recovery. E.g. I have a tlb (transactional log backup, for
> brevity's sake) ending at LSN 124. I restore with recovery and transaction
> 106 isn't commited before LSN 124, so it gets rolled back. Now I try to
> restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
> because transaction 106 is commited at LSN 145 after all! I thought this
> is why you have to use NORECOVERY.
> BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
> Server 2005 Implementation and Maintenance" that made me think otherwise
> (pg. 419): "A log backup backs up the active log. It starts at the Log
> Sequence Number (LSN) at which the previous log backup completed. SQL
> Server then backs up all subsequent transactions UNTIL THE BACKUP
> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
> Now if this is so, why is there any need for a rollback after restoring a
> tlb with recovery anyway? All transactions included in the tlb are not
> open (i.e. commited or rolled backed), so the worst thing that could
> happen is the need for a rollforward, in case the db isn't consistent with
> the tlb. So why do we need NORECOVERY?
> Somewhat related bonus question: What portion of the log is exactly backed
> up when I do a FULL backup? It's only the part after the oldest open
> transaction, right?
>
> Thank you a lot
> Nils Loeber
>|||Hi Kalen,
this is the kind of helpful answer I had hoped for. Thank you very much.
Best regards
Nils Loeber
"Kalen Delaney" <replies@.public_newsgroups.com> schrieb im Newsbeitrag
news:OYJ2dQmuGHA.2260@.TK2MSFTNGP03.phx.gbl...
> Hi Nils
> This is not correct, but I appreciate your recognition of the quality of
> the document as a whole, even though there are some errors.
> Books Online is correct in stating that during a log backup:
> the log is backed up from the last successfully executed log backup to
> the current end of the log.
> I have no idea where the comment about open transaction came from.
> When you do a full backup, SQL Server records the current LSN when the
> full backup starts. When the full backup is over, the new current LSN is
> records. All the log records between the two recorded LSNs are then backed
> up. So it basically captures all other changes that were going on while
> the backup was taking place. (It may start much later than the oldest open
> transaction.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
> news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
>

Question on Log Backup and NORECOVERY

Dear experts,
I am confused. I always thought that using NORECOVERY was essential when
restoring multiple transactional log backups in a row and I always thought
this was due to the fact that without NORECOVERY the transactional log
backups wouldn't "fit" to each other, because of potential rollbacks
happening in recovery. E.g. I have a tlb (transactional log backup, for
brevity's sake) ending at LSN 124. I restore with recovery and transaction
106 isn't commited before LSN 124, so it gets rolled back. Now I try to
restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
because transaction 106 is commited at LSN 145 after all! I thought this is
why you have to use NORECOVERY.
BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
Server 2005 Implementation and Maintenance" that made me think otherwise
(pg. 419): "A log backup backs up the active log. It starts at the Log
Sequence Number (LSN) at which the previous log backup completed. SQL Server
then backs up all subsequent transactions UNTIL THE BACKUP ENCOUNTERS AN
OPEN TRANSACTION." (emphasis mine)
Now if this is so, why is there any need for a rollback after restoring a
tlb with recovery anyway? All transactions included in the tlb are not open
(i.e. commited or rolled backed), so the worst thing that could happen is
the need for a rollforward, in case the db isn't consistent with the tlb. So
why do we need NORECOVERY?
Somewhat related bonus question: What portion of the log is exactly backed
up when I do a FULL backup? It's only the part after the oldest open
transaction, right?
Thank you a lot
Nils LoeberHi Nils
This is not correct, but I appreciate your recognition of the quality of the
document as a whole, even though there are some errors.
Books Online is correct in stating that during a log backup:
the log is backed up from the last successfully executed log backup to
the current end of the log.
I have no idea where the comment about open transaction came from.
When you do a full backup, SQL Server records the current LSN when the full
backup starts. When the full backup is over, the new current LSN is records.
All the log records between the two recorded LSNs are then backed up. So it
basically captures all other changes that were going on while the backup was
taking place. (It may start much later than the oldest open transaction.)
--
HTH
Kalen Delaney, SQL Server MVP
"Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
> Dear experts,
> I am confused. I always thought that using NORECOVERY was essential when
> restoring multiple transactional log backups in a row and I always thought
> this was due to the fact that without NORECOVERY the transactional log
> backups wouldn't "fit" to each other, because of potential rollbacks
> happening in recovery. E.g. I have a tlb (transactional log backup, for
> brevity's sake) ending at LSN 124. I restore with recovery and transaction
> 106 isn't commited before LSN 124, so it gets rolled back. Now I try to
> restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
> because transaction 106 is commited at LSN 145 after all! I thought this
> is why you have to use NORECOVERY.
> BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
> Server 2005 Implementation and Maintenance" that made me think otherwise
> (pg. 419): "A log backup backs up the active log. It starts at the Log
> Sequence Number (LSN) at which the previous log backup completed. SQL
> Server then backs up all subsequent transactions UNTIL THE BACKUP
> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
> Now if this is so, why is there any need for a rollback after restoring a
> tlb with recovery anyway? All transactions included in the tlb are not
> open (i.e. commited or rolled backed), so the worst thing that could
> happen is the need for a rollforward, in case the db isn't consistent with
> the tlb. So why do we need NORECOVERY?
> Somewhat related bonus question: What portion of the log is exactly backed
> up when I do a FULL backup? It's only the part after the oldest open
> transaction, right?
>
> Thank you a lot
> Nils Loeber
>|||Hi Kalen,
this is the kind of helpful answer I had hoped for. Thank you very much.
Best regards
Nils Loeber
"Kalen Delaney" <replies@.public_newsgroups.com> schrieb im Newsbeitrag
news:OYJ2dQmuGHA.2260@.TK2MSFTNGP03.phx.gbl...
> Hi Nils
> This is not correct, but I appreciate your recognition of the quality of
> the document as a whole, even though there are some errors.
> Books Online is correct in stating that during a log backup:
> the log is backed up from the last successfully executed log backup to
> the current end of the log.
> I have no idea where the comment about open transaction came from.
> When you do a full backup, SQL Server records the current LSN when the
> full backup starts. When the full backup is over, the new current LSN is
> records. All the log records between the two recorded LSNs are then backed
> up. So it basically captures all other changes that were going on while
> the backup was taking place. (It may start much later than the oldest open
> transaction.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
> news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
>> Dear experts,
>> I am confused. I always thought that using NORECOVERY was essential when
>> restoring multiple transactional log backups in a row and I always
>> thought this was due to the fact that without NORECOVERY the
>> transactional log backups wouldn't "fit" to each other, because of
>> potential rollbacks happening in recovery. E.g. I have a tlb
>> (transactional log backup, for brevity's sake) ending at LSN 124. I
>> restore with recovery and transaction 106 isn't commited before LSN 124,
>> so it gets rolled back. Now I try to restore the tlb beginning at LSN 125
>> and lo and behold, it doesn't work because transaction 106 is commited at
>> LSN 145 after all! I thought this is why you have to use NORECOVERY.
>> BUT: There is a sentence in Solid Quality Learning's fine publication
>> "SQL Server 2005 Implementation and Maintenance" that made me think
>> otherwise (pg. 419): "A log backup backs up the active log. It starts at
>> the Log Sequence Number (LSN) at which the previous log backup completed.
>> SQL Server then backs up all subsequent transactions UNTIL THE BACKUP
>> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
>> Now if this is so, why is there any need for a rollback after restoring a
>> tlb with recovery anyway? All transactions included in the tlb are not
>> open (i.e. commited or rolled backed), so the worst thing that could
>> happen is the need for a rollforward, in case the db isn't consistent
>> with the tlb. So why do we need NORECOVERY?
>> Somewhat related bonus question: What portion of the log is exactly
>> backed up when I do a FULL backup? It's only the part after the oldest
>> open transaction, right?
>>
>> Thank you a lot
>> Nils Loeber
>

Question on Custom code assembly

Hi,
I have to build a report whose content is very complex and comes from
multiple tables. I think the best possible way is to write custom
code(assembly). Is it possible to return a data set using custom code
assembly and assign it to report ?
Please help.
-- Thanks
RKOn Oct 19, 11:19 am, "S V Ramakrishna"
<ramakrishna.seeth...@.translogicsys.com> wrote:
> Hi,
> I have to build a report whose content is very complex and comes from
> multiple tables. I think the best possible way is to write custom
> code(assembly). Is it possible to return a data set using custom code
> assembly and assign it to report ?
> Please help.
> -- Thanks
> RK
Hi,
What you could do is write a storeproc for your dataset that handles
all (or most of)
your complex table handling.
V.|||Hi,
Thanks for the reply. Stored Procedure is a very good idea. I have a small
doubt. Is it possible at all to have a return type other than primitive
type(for example an array of integers ) for a method in a custom code
assembly in SSRS ?
--
RK
"Vinnie" <vsempoux@.gmail.com> wrote in message
news:1192786497.261274.131220@.e34g2000pro.googlegroups.com...
> On Oct 19, 11:19 am, "S V Ramakrishna"
> <ramakrishna.seeth...@.translogicsys.com> wrote:
>> Hi,
>> I have to build a report whose content is very complex and comes from
>> multiple tables. I think the best possible way is to write custom
>> code(assembly). Is it possible to return a data set using custom code
>> assembly and assign it to report ?
>> Please help.
>> -- Thanks
>> RK
> Hi,
> What you could do is write a storeproc for your dataset that handles
> all (or most of)
> your complex table handling.
> V.
>