ForEach Loop and Data Flow

In my Control Flow, I execute a data flow that opens a flat file and populates the file into a recordset.

Back to my Control Flow, I have a ForEach container that uses a ForEach ADO Enumerator. Inside the ForEach, I execute an "Execute SQL Task" that updates a table.

This is where I'm confused, while in the ForEach, I also want to call a Data Flow and use the current record (record in my ForEach) and perform several lookup tasks. Unfortunately, I'm now sure how to use the ForEach record as a source in my Data Flow....What am I missing?

Thanks,
Gary

We need to get the column values for each record in the "ForEach ADO Enumerator" in some variable.
Then for the tasks inside the ForEach container - we can use those variables.

Similar example is @. http://www.codeproject.com/useritems/foreachadossis.asp

Thanks,
Loonysan

sql

Foreach Loop - Value back to 0

Hi,

Let's say you have a Data Flow Task that connects to a Foreach Loop, looping through the data.

Somewhere inside the Foreach Loop, you set an int variable MyInt to a value.

My question:

Is it possable when each iteration begins to set MyInt to 0?

Thanks in advance.

Is the foreach loop evaluating the same variable, or are you using the variable for something else?

Script tasks can assign values to variables.|||Can you set a value of a variable inside an expression?|||

MrHat wrote:

Can you set a value of a variable inside an expression?

No.|||But you could set it by running "SELECT ? = 0" inside an Execute SQL task at the start of your loop, and assigning that output parameter to your variable.

FOR XML AUTO returning blank.

Hello,
I have an SQL statement which returns 2 rows
If I append the "FOR XML AUTO" at the end of the query I get nothing back.
The SQL is
Your post was incomplete. Could you post again? and possibly paste the
output of the SELECT query (without the FOR XML AUTO)
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"msnews.microsoft.com" <wachaca.no@.spam.santiago.cl> wrote in message
news:uLLueH4bEHA.2816@.TK2MSFTNGP11.phx.gbl...
Hello,
I have an SQL statement which returns 2 rows
If I append the "FOR XML AUTO" at the end of the query I get nothing back.
The SQL is
|||Hi,
the NULL value are not returned with FOR XML
I hope to help you
@.+
Boss Hog
"msnews.microsoft.com" <wachaca.no@.spam.santiago.cl> a crit dans le message
de news:uLLueH4bEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have an SQL statement which returns 2 rows
> If I append the "FOR XML AUTO" at the end of the query I get nothing back.
> The SQL is
>

For Xml Auto Help

In sql 2005 if I have a query along the lines of:
select * from myTable
for xml auto, elements xsinil, root('myRoot')
I will get back one row, one column, filled with a nice xml string.
The problem I'm having is figuring out how to access that data, such that I
can put it into a local var. What I want to do looks something like this:
declare @.foo varchar(max)
select @.foo = * from myTable
for xml auto, elements xsinil, root('myRoot')
select @.foo
This brings about an error of course, even though the final result is just
one row and column of data.
Does anybody know how I can get the results of a query using "for xml auto"
into a local variable?
Thanks,
KevinJust use an xml type variable e.g.
declare @.x xml
set @.x = (select [name]
from sys.databases as [database]
for xml auto,root('databases'))
select @.x as 'XML Result'
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
> In sql 2005 if I have a query along the lines of:
> select * from myTable
> for xml auto, elements xsinil, root('myRoot')
> I will get back one row, one column, filled with a nice xml string.
> The problem I'm having is figuring out how to access that data, such that
> I can put it into a local var. What I want to do looks something like
> this:
> declare @.foo varchar(max)
> select @.foo = * from myTable
> for xml auto, elements xsinil, root('myRoot')
> select @.foo
> This brings about an error of course, even though the final result is just
> one row and column of data.
> Does anybody know how I can get the results of a query using "for xml
> auto" into a local variable?
> Thanks,
> Kevin
>
>|||Just to add a tiny bit:
The TYPE directive mean that the result from the query is of the datatype XM
L instead of a string.
It doesn't make any difference in Jasper's example, as there would be an imp
licit datatype
conversation from string to xml anyhow, but it might be useful in other case
s. The TYPE directive is
obviously new for 2005 (as the xml datatype is).
set @.x = (select [name]
from sys.databases as [database]
for xml auto,root('databases'), type)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u9$Y3ODDGHA.2644@.TK2MSFTNGP09.phx.gbl...
> Just use an xml type variable e.g.
> declare @.x xml
> set @.x = (select [name]
> from sys.databases as [database]
> for xml auto,root('databases'))
> select @.x as 'XML Result'
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
> news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Jasper and Tibor, that's just what I needed.
Kevin
"Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
> In sql 2005 if I have a query along the lines of:
> select * from myTable
> for xml auto, elements xsinil, root('myRoot')
> I will get back one row, one column, filled with a nice xml string.
> The problem I'm having is figuring out how to access that data, such that
> I can put it into a local var. What I want to do looks something like
> this:
> declare @.foo varchar(max)
> select @.foo = * from myTable
> for xml auto, elements xsinil, root('myRoot')
> select @.foo
> This brings about an error of course, even though the final result is just
> one row and column of data.
> Does anybody know how I can get the results of a query using "for xml
> auto" into a local variable?
> Thanks,
> Kevin
>
>

For XML AUTO

Hi
I'm trying to produce XML datasets to be used in Crystal Reports, but I
cannot seem to view the XML output when produced.
It just comes back with errors, when viewed in IE.
The error is:
************************************************** **************************
********
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and
then click the Refresh button, or try again later.

Only one top level element is allowed in an XML document. Error processing
resource 'file:///C:/Documents and Settings/hsrp...
<Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
ConvertedDate1="19981007"/><Order_ Order_Date="19...
************************************************** **************************
********
Any ideas why this is happening?
Kind Regards
Rikesh
(SQL2K-SP3/W2K-SP4)
FOR XML produces XML fragments - not documents. You might need to add a root
element to the results in order to make it well-formed.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"rikesh" <rikesh_patel@.website.com> wrote in message
news:ejMHMbMZEHA.3752@.TK2MSFTNGP12.phx.gbl...
Hi
I'm trying to produce XML datasets to be used in Crystal Reports, but I
cannot seem to view the XML output when produced.
It just comes back with errors, when viewed in IE.
The error is:
************************************************** **************************
********
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and
then click the Refresh button, or try again later.

Only one top level element is allowed in an XML document. Error processing
resource 'file:///C:/Documents and Settings/hsrp...
<Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
ConvertedDate1="19981007"/><Order_ Order_Date="19...
************************************************** **************************
********
Any ideas why this is happening?
Kind Regards
Rikesh
(SQL2K-SP3/W2K-SP4)
|||Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
that didn't work?
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> FOR XML produces XML fragments - not documents. You might need to add a
root
> element to the results in order to make it well-formed.
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:ejMHMbMZEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm trying to produce XML datasets to be used in Crystal Reports, but I
> cannot seem to view the XML output when produced.
> It just comes back with errors, when viewed in IE.
> The error is:
>
************************************************** **************************
> ********
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error
and
> then click the Refresh button, or try again later.
>
> ----
--
> --
> Only one top level element is allowed in an XML document. Error
processing
> resource 'file:///C:/Documents and Settings/hsrp...
> <Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
> ConvertedDate1="19981007"/><Order_ Order_Date="19...
>
>
************************************************** **************************
> ********
> Any ideas why this is happening?
>
> --
> Kind Regards
> Rikesh
> (SQL2K-SP3/W2K-SP4)
>
>
|||You can't do it in Transact-SQL in SQL Server 2000 (Yukon includes a ROOT
directive). You need to do it on the client. The ADO and ADO.NET classes
shipped with SQLXML have a RootTag property you can use, or you could use a
template or schema. Have a look at the documentation shipped with SQLXML
3.0.
Chers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"rikesh" <rikesh_patel@.website.com> wrote in message
news:%23BdETGNZEHA.3476@.tk2msftngp13.phx.gbl...
Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
that didn't work?
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> FOR XML produces XML fragments - not documents. You might need to add a
root
> element to the results in order to make it well-formed.
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:ejMHMbMZEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm trying to produce XML datasets to be used in Crystal Reports, but I
> cannot seem to view the XML output when produced.
> It just comes back with errors, when viewed in IE.
> The error is:
>
************************************************** **************************
> ********
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error
and
> then click the Refresh button, or try again later.
>
> ----
--
> --
> Only one top level element is allowed in an XML document. Error
processing
> resource 'file:///C:/Documents and Settings/hsrp...
> <Order_ Order_Date="1998-10-07T00:00:00" ConvertedDate="19981007"
> ConvertedDate1="19981007"/><Order_ Order_Date="19...
>
>
************************************************** **************************
> ********
> Any ideas why this is happening?
>
> --
> Kind Regards
> Rikesh
> (SQL2K-SP3/W2K-SP4)
>
>
|||Is this a separate component of SQL!!!
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:uR9QRPQZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can't do it in Transact-SQL in SQL Server 2000 (Yukon includes a ROOT
> directive). You need to do it on the client. The ADO and ADO.NET classes
> shipped with SQLXML have a RootTag property you can use, or you could use
a
> template or schema. Have a look at the documentation shipped with SQLXML
> 3.0.
> Chers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:%23BdETGNZEHA.3476@.tk2msftngp13.phx.gbl...
> Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
> that didn't work?
>
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> root
>
************************************************** **************************
> and
> ----
> --
> processing
>
************************************************** **************************
>
>
|||http://www.microsoft.com/downloads/d...displaylang=en
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"rikesh" <rikesh_patel@.website.com> wrote in message
news:OtBYZUQZEHA.3752@.TK2MSFTNGP12.phx.gbl...
Is this a separate component of SQL!!!
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:uR9QRPQZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can't do it in Transact-SQL in SQL Server 2000 (Yukon includes a ROOT
> directive). You need to do it on the client. The ADO and ADO.NET classes
> shipped with SQLXML have a RootTag property you can use, or you could use
a
> template or schema. Have a look at the documentation shipped with SQLXML
> 3.0.
> Chers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:%23BdETGNZEHA.3476@.tk2msftngp13.phx.gbl...
> Any ideas, how to do that, someone suggested FOR XML AUTO, ELEMENTS, but
> that didn't work?
>
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:ua2LxDNZEHA.1248@.TK2MSFTNGP11.phx.gbl...
> root
>
************************************************** **************************
> and
> ----
> --
> processing
>
************************************************** **************************
>
>
|||The root property is available in the OLEDB provider that ships with SQL
Server 2000. So it is not part of the server-side software but the client
component.
Best regards
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:uPWrT$QZEHA.136@.TK2MSFTNGP11.phx.gbl...
> http://www.microsoft.com/downloads/d...displaylang=en
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "rikesh" <rikesh_patel@.website.com> wrote in message
> news:OtBYZUQZEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Is this a separate component of SQL!!!
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:uR9QRPQZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> a
> ************************************************** **************************
> ************************************************** **************************
>
>
|||Thanks chaps, I'll give it a try...
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23Yv8W1XZEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> The root property is available in the OLEDB provider that ships with SQL
> Server 2000. So it is not part of the server-side software but the client
> component.
> Best regards
> Michael
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:uPWrT$QZEHA.136@.TK2MSFTNGP11.phx.gbl...
http://www.microsoft.com/downloads/d...displaylang=en[vbcol=seagreen]
ROOT[vbcol=seagreen]
classes[vbcol=seagreen]
use[vbcol=seagreen]
SQLXML[vbcol=seagreen]
but[vbcol=seagreen]
a[vbcol=seagreen]
I[vbcol=seagreen]
************************************************** **************************[vbcol=seagreen]
error[vbcol=seagreen]
-[vbcol=seagreen]
************************************************** **************************
>

For Loop help

I have a problem not receiving any data back from a for loop in my report. The column data shows only as False or True.

Here is my code below. The one in VB and the one i have in Crystal Reports

Crystal Reports Code:

Dim ServicePeriod As number
ServicePeriod = {command.Advisor_Service_Period}
Dim amount As number
Dim i as number
For i=1 To 28
If ServicePeriod > 53 Then
formula = amount =+ 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
formula = amount =+ 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
formula = amount =+ 1000
ElseIf ServicePeriod = 0 Then
formula = amount =+ 800
End If
ServicePeriod =+ 1
Next i

VB Code:

Dim ServicePeriod As Integer = 1
Dim amount As Integer
For i As Integer = 1 To 28
If ServicePeriod > 53 Then
amount += 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
amount += 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
amount += 1000
ElseIf ServicePeriod = 0 Then
amount += 800
End If
ServicePeriod += 1Next iI don't think you can use += in Crystal to perform addition, so I think you are returning whether amount = value, i.e. a boolean.|||I don't think you can use += in Crystal to perform addition, so I think you are returning whether amount = value, i.e. a boolean.

Thanks for the quick response. removing the = helped eliminate the bool problem but for some reason i am still getting bad data. Should i maybe take a different approach on how to retreive this data? It returns 1000 for every record and does not seem to loop.|||Is 1000 the correct value for the first record in the report?
Did you put WhilePrintingRecords at the top of the formula?
I see you're using Basic syntax, which I'm not particularly familiar with. Does setting the formula 'variable' result in the formula ending? Maybe you need to use another variable to hold the calculated value and return it as the formula result at the end.|||Is 1000 the correct value for the first record in the report?
Did you put WhilePrintingRecords at the top of the formula?
I see you're using Basic syntax, which I'm not particularly familiar with. Does setting the formula 'variable' result in the formula ending? Maybe you need to use another variable to hold the calculated value and return it as the formula result at the end.

It does not seem to loop and add values. It instead just checks once and adds a value rather than looping for a set amount of times. How would you write the loop with crystal syntax. I have been stuck with this for a while any help is greatly appreciated.|||This is still basic syntax:

whileprintingrecords
Dim ServicePeriod As number
Dim amount As number
Dim i as number

amount = 0
ServicePeriod = 54

For i=1 To 28
If ServicePeriod > 53 Then
amount = amount + 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
amount = amount + 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
amount = amount + 1000
ElseIf ServicePeriod = 0 Then
amount = amount + 800
End If
ServicePeriod = ServicePeriod + 1
Next i

formula = amount|||This is still basic syntax:

whileprintingrecords
Dim ServicePeriod As number
Dim amount As number
Dim i as number

amount = 0
ServicePeriod = 54

For i=1 To 28
If ServicePeriod > 53 Then
amount = amount + 2000
ElseIf ServicePeriod >= 27 And ServicePeriod <= 53 Then
amount = amount + 1500
ElseIf ServicePeriod >= 1 And ServicePeriod <= 26 Then
amount = amount + 1000
ElseIf ServicePeriod = 0 Then
amount = amount + 800
End If
ServicePeriod = ServicePeriod + 1
Next i

formula = amount

works perfectly. Thanks for all the help.

for auto question

Hi,
The following TSQL command ;
select * from tbldata for xml auto, elements
brings back the xml form of all the data records.
Hence, for a table with multiple records on the output is;
<tblrecord>
<text>Hi</text>
</tblrecord>
<tblrecord>
<text>Goodbye</text>
</tblrecord>
Is there any way (with the transact SQL command) to get it to put a top
level element around the data - so it can be immediately viewable in I.E.
i.e. To put the <records> tags into the start and end.
<records>
<tblrecord>
<text>Hi</text>
</tblrecord>
<tblrecord>
<text>Goodbye</text>
</tblrecord>
</records>
Many thanks in advance,
Mike.
No - the FOR XML clause is designed to return an XML fragment, not a
complete document. You'd have to use the SQLXML client-side technology to
add the root tag. Basically, the choices are to use an annotated schema
(with an "is-constant" root element), an XML template, the "xml root"
property of the ADO command object, the RootTag property of the ADO.NET
SqlXmlCommand object, or the root parameter in a SQLISAPI URL. Most of these
(apart from the ADO.NET approach) are available in the original release of
SQL Server 2000 and are discussed in the SQL Server Books Online. However,
you'd be best installing the latest SQLXML 3.0 release
(http://www.microsoft.com/downloads/d...33A9-CF10-4E22
-8004-477098A407AC&displaylang=en).
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Mike G" <mickyg@.mickyg.com> wrote in message
news:%235WFDHMcEHA.2844@.TK2MSFTNGP12.phx.gbl...
Hi,
The following TSQL command ;
select * from tbldata for xml auto, elements
brings back the xml form of all the data records.
Hence, for a table with multiple records on the output is;
<tblrecord>
<text>Hi</text>
</tblrecord>
<tblrecord>
<text>Goodbye</text>
</tblrecord>
Is there any way (with the transact SQL command) to get it to put a top
level element around the data - so it can be immediately viewable in I.E.
i.e. To put the <records> tags into the start and end.
<records>
<tblrecord>
<text>Hi</text>
</tblrecord>
<tblrecord>
<text>Goodbye</text>
</tblrecord>
</records>
Many thanks in advance,
Mike.
|||Note that FOR XML in SQL Server 2005 is adding a ROOT directive. But for SQL
Server 2000, Graeme's answer is correct.
Best regards
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:e8XX9EXcEHA.1000@.TK2MSFTNGP12.phx.gbl...
> No - the FOR XML clause is designed to return an XML fragment, not a
> complete document. You'd have to use the SQLXML client-side technology to
> add the root tag. Basically, the choices are to use an annotated schema
> (with an "is-constant" root element), an XML template, the "xml root"
> property of the ADO command object, the RootTag property of the ADO.NET
> SqlXmlCommand object, or the root parameter in a SQLISAPI URL. Most of
> these
> (apart from the ADO.NET approach) are available in the original release of
> SQL Server 2000 and are discussed in the SQL Server Books Online. However,
> you'd be best installing the latest SQLXML 3.0 release
> (http://www.microsoft.com/downloads/d...33A9-CF10-4E22
> -8004-477098A407AC&displaylang=en).
> Hope that helps,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "Mike G" <mickyg@.mickyg.com> wrote in message
> news:%235WFDHMcEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> The following TSQL command ;
> select * from tbldata for xml auto, elements
> brings back the xml form of all the data records.
> Hence, for a table with multiple records on the output is;
> <tblrecord>
> <text>Hi</text>
> </tblrecord>
> <tblrecord>
> <text>Goodbye</text>
> </tblrecord>
> Is there any way (with the transact SQL command) to get it to put a top
> level element around the data - so it can be immediately viewable in I.E.
> i.e. To put the <records> tags into the start and end.
> <records>
> <tblrecord>
> <text>Hi</text>
> </tblrecord>
> <tblrecord>
> <text>Goodbye</text>
> </tblrecord>
> </records>
>
> Many thanks in advance,
> Mike.
>
>

For all MS SQL Gurus :( please advice

Hi guys,
we has accidently restored our Database with a back up file created months ago. we have no back up for the huge data and Stored procedures ( I know it is very stupid :( and i m sad abt it all ), guys is there any way by which i can get restore my db back to what it was before restoration.
the flks in team have also deleted(shift + delete) the transaction log file ( I know it is very stupid again :( and i m very sad abt it all )..
please advice
Thanx in advance
ashish

There are a few ways of doing this ...
1) Go to your off-drive backups (tape, CDs, etc) and restore the latest version
2) Restore the Stored Procedures from your SCM, but deal with the data
3) Use a time machine to go back in time and prevent that from ever happening.
If all three do not work, then as you guessed, you are screwed. Let it be an expensive lesson in regular backups and SCM for your code. And investing in a time machine.

|||If this database is critical, immediately cease all activity on that server until you decide what you are going to do.

Personally I have no experience at all with such utilities, but you might consider something like this:

Recovery for SQL Server

And here's a review for the product at SQL Server Central (it's a freeregistration to read the article; great site, I recommend registering)

Review - MSSQLRecovery