I want to revert back to original state of database before i performed restore database

Hello,
i am in great trouble. I want to revert back to original state of
database before i performed restore database on my sql server 2K
Database. Accidently i didn't take backup of my Database and i did
restore, so is there any way to get the original state back of my
Database?

Any suggestion will be highly appriciated.

Regards,
S. Domadia."san" <shdomadia@.gmail.comwrote in message
news:1163592758.514890.277030@.h48g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

Hello,
i am in great trouble. I want to revert back to original state of
database before i performed restore database on my sql server 2K
Database. Accidently i didn't take backup of my Database and i did
restore, so is there any way to get the original state back of my
Database?
>

If I understand you correctly, no.

You restored a backup over a copy of your database?

That completely overwrites it.

Quote:

Originally Posted by

Any suggestion will be highly appriciated.
>
>
Regards,
S. Domadia.
>

|||You can't rollback a restore. Your only hope is that the SQL Server created
new files during the restore (e.g. backup was from a different server) and
the original deleted database files still exist on the drive. It's a long
shot but an undelete utility might allow you go recover those.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"san" <shdomadia@.gmail.comwrote in message
news:1163592758.514890.277030@.h48g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

Hello,
i am in great trouble. I want to revert back to original state of
database before i performed restore database on my sql server 2K
Database. Accidently i didn't take backup of my Database and i did
restore, so is there any way to get the original state back of my
Database?
>
Any suggestion will be highly appriciated.
>
>
Regards,
S. Domadia.
>

I want to have a live link to data from SQL Server

This is a bit confusing but here goes: I need to access data in SAP via OLE DB. I can't go direct to the back end database (Oracle), we have to use RFC or BAPI calls to access the SAP data. That's part works, we have a DLL that accesses the SAP data we need.

Next I would like to be able to access the data in that DLL with something like:
Select * from view_SAPData.

We messed around with extended stored procedures and it looked good for a bit because we could call the extended proc from a stored proc and we could create a view from a function so I figured we could make it work, but we couldn't.

So now I'm asking you nice folks if there is a way we can provide a dataset to SQL Server from our DLL? I don't know much about DMO, is there anything there?

Hope this makes sense.
TIA,
Blaise

Hi blaise,

You would to use Microsoft .NET Data Provider for mySAP Business Suite:

http://download.microsoft.com/download/f/7/4/f74cbdb1-87e2-4794-9186-e3ad6bd54b41/DataProviderSAP.exe

You could to see more info about it, here:

Feature Pack for Microsoft SQL Server 2005
http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

sql

I want to have a live link to data from SQL Server

This is a bit confusing but here goes: I need to access data in SAP via OLE DB. I can't go direct to the back end database (Oracle), we have to use RFC or BAPI calls to access the SAP data. That's part works, we have a DLL that accesses the SAP data we need.

Next I would like to be able to access the data in that DLL with something like:
Select * from view_SAPData.

We messed around with extended stored procedures and it looked good for a bit because we could call the extended proc from a stored proc and we could create a view from a function so I figured we could make it work, but we couldn't.

So now I'm asking you nice folks if there is a way we can provide a dataset to SQL Server from our DLL? I don't know much about DMO, is there anything there?

Hope this makes sense.
TIA,
Blaise

Hi blaise,

You would to use Microsoft .NET Data Provider for mySAP Business Suite:

http://download.microsoft.com/download/f/7/4/f74cbdb1-87e2-4794-9186-e3ad6bd54b41/DataProviderSAP.exe

You could to see more info about it, here:

Feature Pack for Microsoft SQL Server 2005
http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

I want all the Table names

hi

I am using Sql Server 2000 as Back end. i am using Visual Basic as front End. i want all the Table Names from the particluar database(i Know the Database name) and load it into an Recordset. how to do that. is there any stored Procedure for that to get all the tables names.

Thanking you

Regards

GandhiYou can issue the following select statement to any database in SQL Server...

Select Name from Sysobjects Where type= 'U'|||Originally posted by pcmbalaji
You can issue the following select statement to any database in SQL Server...

Select Name from Sysobjects Where type= 'U'

Thank u it is working fine

Regrds
gandhi

I take it back

In the standard report manager I'm running a report with 5 parameter choices. The first 3 are determinants of the 4th, and the combined 1-4 determine the 5th.

Although this works fine in my VS BIDS (sp1) with SQL 2005 sp2 CTP, when I deploy it to my sp2 CTP Report server, it throws an error as soon as I select the 3rd parameter.

One or more data sources is missing credentials

I found this answer from MS in another thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=923684&SiteID=1) and since it deals with my problem, wanted to pass it along to anyone else that is dealing with this problem.

" This is a known regression with CTP2 that will be fixed before SP2 is released. The problem is not specific to multivalue parameters. It has to do with dependencies between parameters.

In some cases, you can work around the issue if you give the dependent parameter a default value. You can also work around the issue by removing the valid values dependency between the parameters, though this may not be possible for a given report."

The suggested workaround does work! I can get beyond the 3rd parameter now because I applied a default value!

The workaround is useless. It only alleviated the problem when I stayed with that default value for my 3rd parameter. As soon as I changed it I'm right back to the 'one or more data sources is missing credentials'

SP2 cannot be soon enough...

|||

The workaround suggested can work, but it's tricky, especially with nested parameters that are dependent on each other. I finally got a six parameter functional again but with some pain. I made all the dependent parameter lists 'multi-select' and gave them a functional default value. Only then do they populate correctly.

And this all because I was hoping to use these reports in WSS 3.0 with the report server addin. Got very close and now have stumbled across similar bug with multiple parameters in Sharepoint. Will post entry to Sharepoint forums.

I only get the first character of the field returned .. what am i doing wrong ?

here is my business/data object for some reason I only get the first character back, say value is Charlie, I only get C

 
public string GetUserName(long UserId) {try {string userName; DiscussionDB data =new DiscussionDB(); List paramlist =new List(); paramlist.Add(data.CreateParameter("@.UserId", UserId)); paramlist.Add(data.CreateParameter("@.UserName","", ParameterDirection.Output, DbType.String, 20)); data.ExecuteNonQuery("dbo.Discussion_User_Name",ref paramlist); userName = paramlist[1].Value.ToString();return userName; }catch {throw; } }ALTER PROCEDURE [dbo].[Discussion_User_Name]@.UserId bigint,@.UserName varchar(20) outputASSET NOCOUNT ONSET @.UserName = (Select [Name] from Discussion_Member WHERE UserID = @.UserID)if (@.UserNameis null or @.UserName ='')BEGIN SET @.UserName = (Select UserName from Membership_User WHERE UserID = @.UserID)END
 

hello,

It does not seem that thre is anything wrong with the code.

Have you checked the size of the columns "Name" and "UserName" in Discussion_Member and/or Membership_User ?

hope this helps?

regards,

g

|||

If you run the stored procedure using the sql server tools, does it return the correct result?

|||

yes when I run the SP in MS sever 2005 studio, I get the full field ... e.g Charlie

|||

You need to set the size of @.UserName, the default is 1. There's a size property toIDbDataParameter that you need to set (it looks like your DiscussionDB is wrapping IDbDataParameter and some other stuff)

|||

I tried wording your suggestion in my code by I can't seem to figure it out

I was thinking that the 20in the code below was giving the size

paramlist.Add(data.CreateParameter("@.UserName","", ParameterDirection.Output, DbType.String, 20));

|||

I thought I got this working but I was wrong

I am still having this issue, here is my last code

public string GetUserName(long UserId) {try {string userName; DiscussionDB data =new DiscussionDB(); List paramlist =new List(); paramlist.Add(data.CreateParameter("@.UserId", UserId)); paramlist.Add(data.CreateParameter("@.UserName","", ParameterDirection.Output, DbType.String, 15)); paramlist[1].Size = 15; data.ExecuteNonQuery("dbo.Discussion_User_Name_Fetch",ref paramlist); userName = paramlist[1].Value.ToString();return userName; }catch {throw; } }

I never post my problems but I have no time right now

I just released a beta and I am too busy with the testers right now.

New company. Brought into automate the back office and to advise on the design on their software. Lots of sloppiness here. Denormalized tables, inline SQL all of the usual crap I get paid to cleanup.

I have'nt gotten to this part of there process yet but they do some over night processing that can take up to 12 hours. (I can't wait to look at this code).

Data entry people get here at 6 am and their applications can't seem to connect to the SQL Server but the processing has stopped. They just hang and hang. The network guy comes in about 8 am and restarts the SQL Service Fonzi style and everything is fine. The DBA thinks it's a network issue which seems a tid bit ridiculous to me since restarting the service "fixes" it.

I suspecting it's a memory issue.

Any ideas on where to start.since you're one of the good guys i will ask you if you have a preference for what to rename the subject line of your question to, since the current subject is too nebulous|||on the other hand, it is fairly distinctive so i may just leave it

:) :) :)|||rename constipated SQL Server|||Log on to the SQL server, and see what is going on in Task Manager, first. You have probably already done that, but it does sound like a resource problem somewhere (memory, CPU, etc.). Can we hope this is SQL 2000? Or are you stuck with 6.5?

If there are no resource problems, and the operating system is running smoothly, you probably have some sort of open transaction hanging everyone up. The data entry application may have commandtimeout set to "0", which sounds about in line with what you describe of their code.|||My bet is on SQL object locking. I'd check sp_who (definitely not sp_who2 until I knew it was safe) before rebooting to see if a spid is blocking the rest of the known universe.

The lovely little nightly process might be camping on some resource that everybody needs to "wake up the daemons" in the morning. When you kick the ground out from under it (by restarting SQL), the locks are gone and life is lovely once again.

-PatP|||Yes it is SQL 2K.

Thanks guys. I will let you know the resolution. Of course I will have to come in at 6 am. No VPN here. Small shop.

Have a good weekend.|||How long does the "fix" take after it's restarted?

Are the files set to autogrow?

Is the log being blown out?

How do you know if the 12 hour batch windows actually completes.

Is there enough space left on the drives?

Are there any other services on the box that are in competition for the memory?

If there are, my first reccomendation would be to isolate|||Thanks. I will know more on Monday. I just have'nt had much time to think about this due to the revamp project from hell. I plan on being here early to gauge what is going on.

===========================================
How long does the "fix" take after it's restarted?

Immediate

Are the files set to autogrow?

Yes

Is the log being blown out?

Not sure I would have to check the log size before and after.

How do you know if the 12 hour batch windows actually completes.

They tell me so when I ask.

Is there enough space left on the drives?

Yes

Are there any other services on the box that are in competition for the memory?

This box is dedicated the SQL Server.

If there are, my first reccomendation would be to isolate|||My bet: uncommitted transactions in the nightly process.

I need help with rolling back transaction

Hi,
I have the foll statements that are calling a dts package. I deliberately
misspelt the table name on the last update statement but my transaction
doesnot rollback why the DTS is suppose to rollback
SET NOCOUNT ON
BEGIN TRANSACTION
Select @.doc_no = str_inv_no from dbo.arctlfil_sql
WITH (TABLOCKX )
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END
-- DTSSQLServerStorageFlags :
-- DTSSQLStgFlag_Default = 0
-- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @.hr = sp_OAMethod @.oPKG,
'LoadFromSQLServer("", , "dts")',
NULL
IF @.hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END
.......
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END
UPDATE dbo.fil_sql1 <--here i mispelt the name (dbo.fil_sql)
SET str_inv_no = @.doc_no + 1
WHERE str_inv_no = @.doc_no
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
The DTS basically imports data from a textfile. The data is imported from
the text file even when I get the error
Server: Msg 208, Level 16, State 1, Line 64
Invalid object name 'dbo.fil_sql1' .
I want the entire procedure to be rolled back including the DTS. The
transaction is enabled on the DTS property.
Any ideas?Hi Chris
The DTS package is not being executed in the same context (it will have it's
own connection) and therefore will not be part of the transaction. Include
your update a a step in the package.
John
"Chris" wrote:

> Hi,
> I have the foll statements that are calling a dts package. I deliberately
> misspelt the table name on the last update statement but my transaction
> doesnot rollback why the DTS is suppose to rollback
>
>
> SET NOCOUNT ON
> BEGIN TRANSACTION
> Select @.doc_no = str_inv_no from dbo.arctlfil_sql
> WITH (TABLOCKX )
> EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUT
> IF @.hr <> 0
> BEGIN
> PRINT '*** Create Package object failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> RETURN
> END
> -- DTSSQLServerStorageFlags :
> -- DTSSQLStgFlag_Default = 0
> -- DTSSQLStgFlag_UseTrustedConnection = 256
> EXEC @.hr = sp_OAMethod @.oPKG,
> 'LoadFromSQLServer("", , "dts")',
> NULL
> IF @.hr <> 0
> BEGIN
> PRINT '*** Load Package failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> RETURN
> END
> .......
> EXEC @.hr = sp_OADestroy @.oPKG
> IF @.hr <> 0
> BEGIN
> PRINT '*** Destroy Package failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> RETURN
> END
>
> UPDATE dbo.fil_sql1 <--here i mispelt the name (dbo.fil_sql)
> SET str_inv_no = @.doc_no + 1
> WHERE str_inv_no = @.doc_no
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION
> END
> COMMIT TRANSACTION
> The DTS basically imports data from a textfile. The data is imported from
> the text file even when I get the error
> Server: Msg 208, Level 16, State 1, Line 64
> Invalid object name 'dbo.fil_sql1' .
> I want the entire procedure to be rolled back including the DTS. The
> transaction is enabled on the DTS property.
> Any ideas?
>

I need help normalizing this table.

So I'm creating an administrative back end for a site that's already been created, and whoever made the tables the site uses didn't know much about database design. So I need to normalize this table of Links so it can be easier to have someone make changes and updates to it, but then I need to put all my normalized tables back together to create a View exactly like the old table which the old site can select from. Basically the stipulation is I can't change the code for the old site so I have to make it think it's still selecting from the same table with the same type of parameters. Is it worth doing all this? Or should I just tough it out with this really ugly table?
Here's the table:

and here's the site that uses this table:
http://waahp.byu.edu/links.asp
Thanks!
~Cattrah~
You'll have to explain what the data is. E.g. Can there be duplicates in any of that lot? So canwww.aacp.org be in there twice? Where do these OrderWith values come from? Are there any existing constraints on the table?
|||there are no constraits except that the ID column is the auto generated primary key. Any links are allowed to be in there multiple times, although it really doesn't make much sense to do so. The whole context of the table is to generate a list of website links, as seen in the link below the table image. Most links have a Section(which does not have a URL), which under each section are the weblinks corresponding to it. There are also two special types of links, email links, which can also appear under a Section or be on their own, and Links that appear at the top. IE they will just be listed at the top of the page reguardless if its under a section or not, if it does have a section, it will also appear there. descriptions will appear to the right of all links except email links in which case it will appear to the left. Sections should not have descriptions. OrderWith is a pretty worthless column the way it stands, it defines the order in which the items are selected from the database, in this case alphabetical. With the first links in which the OrderWith is numbered, changing the numbering would change the order that the links appear on the page. In the other cases if you wanted two links under a section to be ordered non alphabetically, you could order them with numbers or just change the order with values to cause them to order a different way.
That's really the whole thing, if you look at the old site, it makes more sense.
~Cattrah~|||Looks like a lot of SQL for one Page of links.
I would not normalize it: There is content and layout information in the DB and the result is a page that looks more like a static HTML file.
If you realy need to touch it I would try to convince my customer to use a simple Category-Link Relation or enter HTML with a Richtextbox.
But I guess this is not an option for you...
regards
Mathias