Clear Cache via Trigger?

I have a dimension table that gets updated nightly. The dimension table is used by a ROLAP cube.

If you wanted to Clear Cache through the use of a trigger once the dimension table is updated, how would you do it? Is there an easy way to execute the XMLA ClearCache from within T-SQL?

If you have a way to call external process from your procedure, you can use ascmd utility to send any XMLA command to Analysis Server. (http://msdn2.microsoft.com/en-us/ms365187.aspx)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Clear all the tables of a database

Is there a simple method to clear all the tables in a db or to make a copy of the mdf file without the datas ?

I tried to use TRUNCATE TABLE but it doesn't work with foreign keys ?

Thanks.

You probably will have to write your own script for this to reflect the dependencies. As you already stated, Truncate won′t work with foreign keys and an undocumented procedure like sp_msforeachtable won′t care about the order to process.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Cleanup / Foreign key

I have an address table I set up so that I can start our address campaign.
There is a field (open entry) called Business Type that I allowed the works
to enter to describe the business type. Now that the entry is complete ,
I'm in cleanup phase. Now, my question is: I know I need to make Business
Type a foreign key and select distinct to insert them into their own table,
but should I do that before I clean up the inconsistencies or after. There
are about 2000 different business types, some are minor like - Car Sales -
Used and Car Sales _ Used. I'm creating a system that allows users to
search based on business type.
Before or after, why?Before!
Why? Do not allow bad data into the database in the first place, it is a
bitch getting it out.
Jay
<programmingcodeATjards.com> wrote in message
news:uVedQl9%23HHA.320@.TK2MSFTNGP04.phx.gbl...
>I have an address table I set up so that I can start our address campaign.
>There is a field (open entry) called Business Type that I allowed the works
>to enter to describe the business type. Now that the entry is complete ,
>I'm in cleanup phase. Now, my question is: I know I need to make Business
>Type a foreign key and select distinct to insert them into their own table,
>but should I do that before I clean up the inconsistencies or after. There
>are about 2000 different business types, some are minor like - Car Sales -
>Used and Car Sales _ Used. I'm creating a system that allows users to
>search based on business type.
> Before or after, why?
>sqlsql

cleansing data

I have a table with 1.5 million rows. Each field has a text qualifier of " ". Could you please tell me if and how I can write a script to remove the quotes from each field?
Thank you. ;)Using which database engine? The string operations aren't very standard between engines, so which engine makes a considerable difference.

-PatP|||Using MS SQL Server 2000|||Hi mary10k, try this
update tablename set filedname=replace(filedname,""" ""","")

Madhivanan

cleaning up duplicates

I have a table with column a, b, c, and d
column b has several duplicates and I want to clean this up.
a b c d
=============
1 name1 aa gg
2 name2 bb hh
3 name3 cc ii
4 name3 dd jj
5 name4 ee kk
6 name5 ff ll
etc.
I'm guessing I would need to select all the unique rows from the table,
put them into temp_table1, select the rows with the duplicates as
single rows into temp_table2, and join those two back into the original
table.
would this be the right idea? and if so,
exactly how would I accomplish this?
any help would be greatly appreciated!create table #test
(
col1 int not null primary key,
col2 varchar(10) not null,
col3 char(1) not null
)
insert into #test values (1,'name1','a')
insert into #test values (2,'name2','b')
insert into #test values (3,'name2','y')
insert into #test values (4,'name3','c')
insert into #test values (5,'name4','f')
select * from #test
where col1=(select max(col1) from #test t where t.col2=#test.col2)
order by col1
<guilesf2@.hotmail.com> wrote in message
news:1141542444.740452.81000@.e56g2000cwe.googlegroups.com...
>I have a table with column a, b, c, and d
> column b has several duplicates and I want to clean this up.
> a b c d
> =============
> 1 name1 aa gg
> 2 name2 bb hh
> 3 name3 cc ii
> 4 name3 dd jj
> 5 name4 ee kk
> 6 name5 ff ll
> etc.
>
> I'm guessing I would need to select all the unique rows from the table,
> put them into temp_table1, select the rows with the duplicates as
> single rows into temp_table2, and join those two back into the original
> table.
> would this be the right idea? and if so,
> exactly how would I accomplish this?
> any help would be greatly appreciated!
>|||thanks a lot! just for my understanding, what would be the reason the
table
needs that alias-type thing in order to work? just curious and trying
to learn.
again, thanks a lot|||If your goal is to cleanup only column b then you probably need to
normalize the table further. You will need to store unique names in a
separate table called NAMES (id, name) and store the id in column b.sqlsql

cleaning up conflicts table

We have 30 remote subscribers and a publisher.We have
experienced in past that the conflict tables grow to the
limit and for some reason after that we start having
replication issues.(It will take anywhere from 3 hr to 6
hrs compare to 10 mins)
becos of the timings the data gets replicated to all the
sites but it doesn't clear them up from the conflicts
tables. How do we manually purge the conflict records so
that it shouldn't cause us problems in long run.
thanks
delete table
where....
Conflict tables are just that tables. There is nothing special about them,
so you can freely insert/update/delete from them. (They are required for
merge so just playing with data isn't recommended.) But, you won't get a
failure on a transaction.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||HI should I use delte table name or I should use truncate
table name statement.
thanks
>--Original Message--
>delete table
>where....
>Conflict tables are just that tables. There is nothing
special about them,
>so you can freely insert/update/delete from them. (They
are required for
>merge so just playing with data isn't recommended.) But,
you won't get a
>failure on a transaction.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
sqlsql

clean up data - when process runs next time

In a integration project I am moving data from A to B.

First time is fine - since table B is empty.

However next time I run the process, I would like to delete all records in B before I run the project again.

What is the best way to delete / clean up data when you re run the process ?

Cheers, T

Issue a DELETE or TRUNCATE from an Execute SQL Task.

-Jamie

Clean up a table & save to another table

I'm trying to filter and update the table AllAgreements (below) by
moving parts of each row into appropriate tables and deleting redundant
rows. This table is just a scratch table used while importing old data
into a new DB.
The situation: A company supplies credits to its customers (other
companies [construction] called Developers) that the customers
(Developers) can use to reduce any bills sent to them by the company.
Each time the credits are supplied they are referred to as a Credit
Agreement. The developers can transfer those credits, or parts of those
credits, to other developers. When a transfer is made they are creating
a new agreement. The Agreement identifier, in correspondence, uses a
user constructed identifier (ID #). The company keeps the original
credit agreement identifier & just adds a sequential letter suffix
representing each iteration of a new agreement off the original (because
the original agreement has one cut-off date that is enforced for all
subsequent agreements that use credits from the original credit agreement).
E.g.:
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
10,'TIF1 MSSH 001A','HARRY',10000.00
11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
12,'TIF1 MSSH 001B','JOE',10000.00
I need to generate a new row in the table Transactions (below) for both
the debit on the original credit agreement and the transfer/credit for
the new credit agreement.
/* BEGIN DDL */
set nocount on
CREATE TABLE AllAgreements ( -- scratch table - no PK
ca_id INTEGER NOT NULL UNIQUE,
"ID #" VARCHAR(20) NOT NULL,
Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
issued_date DATETIME NOT NULL,
amt decimal(9,2) NOT NULL
)
CREATE TABLE Transactions (
ca_id INTEGER NOT NULL REFERENCES AllAgreements,
developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
transaction_type INTEGER NOT NULL
CHECK (transaction_type BETWEEN 1 AND 5),
applied_date DATETIME NOT NULL,
amount DECIMAL(9,2) NOT NULL,
CONSTRAINT PK_Transactions
PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
)
INSERT INTO AllAgreements
VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
INSERT INTO AllAgreements
VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
INSERT INTO AllAgreements
VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
INSERT INTO AllAgreements
VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
INSERT INTO AllAgreements
VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
INSERT INTO AllAgreements
VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
INSERT INTO AllAgreements
VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
INSERT INTO AllAgreements
VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
INSERT INTO AllAgreements
VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
INSERT INTO AllAgreements
VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
INSERT INTO AllAgreements
VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
INSERT INTO AllAgreements
VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
/* desired results in table AllAgreements (I'll remove the Amt column
after the clean up & the values are in the Transactions table. Didn't
show the date in order to display row on one line).
ca_id ID # Developer amt
1 FIF2 GRNBR 001 LUCY $55,000.00
3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
6 TIF1 ACSPA 001 WILLY $30,000.00
7 TIF1 ACSPA 002 WILLY $45,000.00
8 TIF1 MSSH 001 FRED $25,000.00
10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
*/
/* desired results in table Transactions:
(1 = Original credit agreement; 2 = transfer of CR agreement)
E.g.: below the 1st 2 LUCY lines show the original credit
(trans_type=1) and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001"
caused by the transfer to JUDY (line 3). The JUDY line's ca_id, 3,
points to the new credit agreement "FIF2 GRNBR 001A" row in the
AllAgreements table.
ca_id developer transaction_type applied_date amount
-- -- -- -- --
1 LUCY 1 20050115 55000.00
1 LUCY 2 20050122 -20000.00
3 JUDY 2 20050122 20000.00
4 SUSIE 2 20050122 10000.00
3 JUDY 2 20050122 -10000.00
6 WILLY 1 20050211 30000.00
7 WILLY 1 20050211 45000.00
8 FRED 1 20050212 25000.00
8 FRED 2 20050212 -10000.00
10 HARRY 2 20050212 10000.00
10 HARRY 2 20050225 -100000.00
12 JOE 2 20050225 10000.00
*/
DROP TABLE AllAgreements, Transactions
/* END */
QUESTION: What append command will move the amounts to the Transactions
table w/ the correct ca_id value.
Thanks for your help.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)Hi
See inline:
"MGFoster" <mgf00@.earthlink.net> wrote in message
news:8waoe.562$HM.259@.newsread1.news.pas.earthlink.net...
> I'm trying to filter and update the table AllAgreements (below) by moving
> parts of each row into appropriate tables and deleting redundant rows.
> This table is just a scratch table used while importing old data into a
> new DB.
> The situation: A company supplies credits to its customers (other
> companies [construction] called Developers) that the customers
> (Developers) can use to reduce any bills sent to them by the company. Each
> time the credits are supplied they are referred to as a Credit Agreement.
> The developers can transfer those credits, or parts of those credits, to
> other developers. When a transfer is made they are creating a new
> agreement. The Agreement identifier, in correspondence, uses a user
> constructed identifier (ID #). The company keeps the original credit
> agreement identifier & just adds a sequential letter suffix representing
> each iteration of a new agreement off the original (because the original
> agreement has one cut-off date that is enforced for all subsequent
> agreements that use credits from the original credit agreement).
> E.g.:
> 1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
> 2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
> 3,'FIF2 GRNBR 001A','JUDY',20000.00
> 5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
> 4,'FIF2 GRNBR 001B','SUSIE',10000.00
> 6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
> 7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
> 8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
> 9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
> 10,'TIF1 MSSH 001A','HARRY',10000.00
> 11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
> 12,'TIF1 MSSH 001B','JOE',10000.00
> I need to generate a new row in the table Transactions (below) for both
> the debit on the original credit agreement and the transfer/credit for the
> new credit agreement.
> /* BEGIN DDL */
> set nocount on
> CREATE TABLE AllAgreements ( -- scratch table - no PK
> ca_id INTEGER NOT NULL UNIQUE,
> "ID #" VARCHAR(20) NOT NULL,
> Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> issued_date DATETIME NOT NULL,
> amt decimal(9,2) NOT NULL
> )
> CREATE TABLE Transactions (
> ca_id INTEGER NOT NULL REFERENCES AllAgreements,
> developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> transaction_type INTEGER NOT NULL
> CHECK (transaction_type BETWEEN 1 AND 5),
> applied_date DATETIME NOT NULL,
> amount DECIMAL(9,2) NOT NULL,
> CONSTRAINT PK_Transactions
> PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
> )
> INSERT INTO AllAgreements
> VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
> INSERT INTO AllAgreements
> VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
> INSERT INTO AllAgreements
> VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
> INSERT INTO AllAgreements
> VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
> INSERT INTO AllAgreements
> VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
> INSERT INTO AllAgreements
> VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
> INSERT INTO AllAgreements
> VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
> INSERT INTO AllAgreements
> VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
> INSERT INTO AllAgreements
> VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
> INSERT INTO AllAgreements
> VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
> INSERT INTO AllAgreements
> VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
> INSERT INTO AllAgreements
> VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
> /* desired results in table AllAgreements (I'll remove the Amt column
> after the clean up & the values are in the Transactions table. Didn't
> show the date in order to display row on one line).
> ca_id ID # Developer amt
> 1 FIF2 GRNBR 001 LUCY $55,000.00
> 3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
> 4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
> 6 TIF1 ACSPA 001 WILLY $30,000.00
> 7 TIF1 ACSPA 002 WILLY $45,000.00
> 8 TIF1 MSSH 001 FRED $25,000.00
> 10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
> 12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
> */
This seems to be
SELECT A.ca_id,A.[ID #],A.Developer,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.Amt FROM AllAgreements A
WHERE A.Amt > 0
order by A.ca_id

> /* desired results in table Transactions:
> (1 = Original credit agreement; 2 = transfer of CR agreement)
> E.g.: below the 1st 2 LUCY lines show the original credit (trans_type=1)
> and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001" caused by the
> transfer to JUDY (line 3). The JUDY line's ca_id, 3, points to the new
> credit agreement "FIF2 GRNBR 001A" row in the AllAgreements table.
> ca_id developer transaction_type applied_date amount
> -- -- -- -- --
> 1 LUCY 1 20050115 55000.00
> 1 LUCY 2 20050122 -20000.00
> 3 JUDY 2 20050122 20000.00
> 4 SUSIE 2 20050122 10000.00
> 3 JUDY 2 20050122 -10000.00
> 6 WILLY 1 20050211 30000.00
> 7 WILLY 1 20050211 45000.00
> 8 FRED 1 20050212 25000.00
> 8 FRED 2 20050212 -10000.00
> 10 HARRY 2 20050212 10000.00
> 10 HARRY 2 20050225 -100000.00
> 12 JOE 2 20050225 10000.00
> */
>
I can't see the login in the ca_id column as they do not relate to the
transaction concerned! But this is almost what you wanted:
SELECT A.ca_id,--A.[ID #],
B.Developer,
2 Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
B.AMT AS AMT
FROM AllAgreements A
JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
UNION ALL
SELECT A.ca_id,--A.[ID #],
A.Developer AS Developer,
CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.AMT AS AMT
FROM AllAgreements A
LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
ORDER BY A.CA_ID, AMT

> DROP TABLE AllAgreements, Transactions
> /* END */
>
> QUESTION: What append command will move the amounts to the Transactions
> table w/ the correct ca_id value.
>
You will have to decide what the criteria is for the ca_id.
John

> Thanks for your help.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)|||John Bell wrote:
> I can't see the login [do you mean logic?] in the ca_id column as they do
not relate to the
> transaction concerned! But this is almost what you wanted:
> SELECT A.ca_id,--A.[ID #],
> B.Developer,
> 2 Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> B.AMT AS AMT
> FROM AllAgreements A
> JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> UNION ALL
> SELECT A.ca_id,--A.[ID #],
> A.Developer AS Developer,
> CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> A.AMT AS AMT
> FROM AllAgreements A
> LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> ORDER BY A.CA_ID, AMT
>
> You will have to decide what the criteria is for the ca_id.
>
Ah, but that's just what I need the query to do - figure out which ca_id
goes w/ each transfer's credit transaction.
Think of it this way: Lucy transfers $20K from agreement FIF2 GRNBR 001
(ca_id 1) to Judy, which makes a new agreement FIF2 GRNBR 001A (ca_id 2)
for Judy. I need to show in the Transactions table a debit of $20K from
ca_id 1 and a credit to ca_id 2 (easy to do) of $20K.
The following DML command will get the credits:
INSERT INTO Transactions (ca_id, developer, transaction_type,
applied_date, amount)
SELECT ca_id, developer, 2 As trans_type, issued_date, amt
FROM AllAgreements
WHERE amt > 0
It's the DML command that puts the debits, w/ the proper ca_id, that's
the problem.
Ex. 1:
ca_id, "ID #", developer, amt
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
The 2nd row (ca_id 2) needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
1 Lucy 2 -20000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001.'
Ex. 2:
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
The above row needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
3 Judy 2 -10000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001A.'
IOW, the credits ($) are deducted from the prior agreement:
001A deducts from 001
001B deducts from 001A
I believe I need to add a character "@." to the 001 so the query can
"see" the previous agreement as
Right([current ID #],1) > Right([other ID #],1)
This sorta works:
SELECT C.ca_id, c.[ID #], d.developer, d.transaction_type,
d.issued_date, d.amt
FROM AllAgreements As C,
(SELECT ca_id,[ID #], Developer, 2 AS Transaction_Type,
issued_date, amt
FROM AllAgreements
WHERE amt<0 ) As D
WHERE LEFT(C.[id #],LEN(c.[id #])-1)=LEFT(d.[id #],LEN(d.[id #])-1)
AND ASC(RIGHT(C.[ID #],1)) = ASC(RIGHT(D.[ID #],1))-1
ORDER BY 1, 2
Data from above query (removed the amt & date cols so could display on
one line):
ca_id ID # developer amt
1 FIF2 GRNBR 001@. LUCY ($20,000.00) <- correct
2 FIF2 GRNBR 001A JUDY ($10,000.00) <- incorrect
3 FIF2 GRNBR 001A JUDY ($10,000.00) <- correct
8 TIF1 MSSH 001@. FRED ($10,000.00) <- correct
9 TIF1 MSSH 001A HARRY ($10,000.00) <- incorrect
10 TIF1 MSSH 001A HARRY ($10,000.00) <- correct
Any refinements possible?
Rgds,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

cleaing up text type fields

Hello.
I have a text type field in an SQL2000 table I need to clean up. After
converting from MySQL I am finding that I have leading Tabs and when I
display the text field in an asp textbox I end up see the record
double spaced.
Any suggestion on how I can clean the field up?
Also, as I test an application I just migrated from apache/php/mysql
to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
pentium 4 with 512mb I am finding the the MS solution is like 3 times
slower?
Thanks for any help or info.> I have a text type field in an SQL2000 table I need to clean up. After
> converting from MySQL I am finding that I have leading Tabs and when I
> display the text field in an asp textbox I end up see the record
> double spaced.
> Any suggestion on how I can clean the field up?
Trim it on the client before displaying it?
> Also, as I test an application I just migrated from apache/php/mysql
> to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> pentium 4 with 512mb I am finding the the MS solution is like 3 times
> slower?
We don't even have a fraction of the information required to analyze your
environment and point out potential reasons for any performance differences.|||Assuming that each record has two leading tabs, you could do something like:
DECLARE @.ptr binary(16)
SELECT @.ptr=textptr(textColumn) FROM yourTable
UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
That will delete the first two characters from each record. If you need to
do more complex evaluation, you can run a cursor over the table and use
CHARINDEX to determine where the replacement(s) should be made.
<jason@.cyberpine.com> wrote in message
news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> Hello.
> I have a text type field in an SQL2000 table I need to clean up. After
> converting from MySQL I am finding that I have leading Tabs and when I
> display the text field in an asp textbox I end up see the record
> double spaced.
> Any suggestion on how I can clean the field up?
> Also, as I test an application I just migrated from apache/php/mysql
> to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> pentium 4 with 512mb I am finding the the MS solution is like 3 times
> slower?
> Thanks for any help or info.|||On second thought, you will need a CURSOR - Running that code as-is will
only strip the first two characters from the last record selected.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> Assuming that each record has two leading tabs, you could do something
like:
> DECLARE @.ptr binary(16)
> SELECT @.ptr=textptr(textColumn) FROM yourTable
> UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> That will delete the first two characters from each record. If you need
to
> do more complex evaluation, you can run a cursor over the table and use
> CHARINDEX to determine where the replacement(s) should be made.
> <jason@.cyberpine.com> wrote in message
> news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > Hello.
> >
> > I have a text type field in an SQL2000 table I need to clean up. After
> > converting from MySQL I am finding that I have leading Tabs and when I
> > display the text field in an asp textbox I end up see the record
> > double spaced.
> >
> > Any suggestion on how I can clean the field up?
> >
> > Also, as I test an application I just migrated from apache/php/mysql
> > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > pentium 4 with 512mb I am finding the the MS solution is like 3 times
> > slower?
> >
> > Thanks for any help or info.
>|||You can see this example for using replace in this case:
http://www.aspfaq.com/2445
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eZxpAnUpDHA.684@.TK2MSFTNGP09.phx.gbl...
> On second thought, you will need a CURSOR - Running that code as-is will
> only strip the first two characters from the last record selected.
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > Assuming that each record has two leading tabs, you could do something
> like:
> >
> > DECLARE @.ptr binary(16)
> > SELECT @.ptr=textptr(textColumn) FROM yourTable
> > UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> >
> > That will delete the first two characters from each record. If you need
> to
> > do more complex evaluation, you can run a cursor over the table and use
> > CHARINDEX to determine where the replacement(s) should be made.
> >
> > <jason@.cyberpine.com> wrote in message
> > news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > > Hello.
> > >
> > > I have a text type field in an SQL2000 table I need to clean up. After
> > > converting from MySQL I am finding that I have leading Tabs and when I
> > > display the text field in an asp textbox I end up see the record
> > > double spaced.
> > >
> > > Any suggestion on how I can clean the field up?
> > >
> > > Also, as I test an application I just migrated from apache/php/mysql
> > > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > > pentium 4 with 512mb I am finding the the MS solution is like 3 times
> > > slower?
> > >
> > > Thanks for any help or info.
> >
> >
>|||Ahhh, nice... I didn't realize this slightly annoying caveat:
"Initially I used CHARINDEX, but that failed if the pattern was deeper than
8,000 characters into the value. PATINDEX never dies."
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uAGRfqUpDHA.2772@.TK2MSFTNGP12.phx.gbl...
> You can see this example for using replace in this case:
> http://www.aspfaq.com/2445
>
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eZxpAnUpDHA.684@.TK2MSFTNGP09.phx.gbl...
> > On second thought, you will need a CURSOR - Running that code as-is will
> > only strip the first two characters from the last record selected.
> >
> > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> message
> > news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > > Assuming that each record has two leading tabs, you could do something
> > like:
> > >
> > > DECLARE @.ptr binary(16)
> > > SELECT @.ptr=textptr(textColumn) FROM yourTable
> > > UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> > >
> > > That will delete the first two characters from each record. If you
need
> > to
> > > do more complex evaluation, you can run a cursor over the table and
use
> > > CHARINDEX to determine where the replacement(s) should be made.
> > >
> > > <jason@.cyberpine.com> wrote in message
> > > news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > > > Hello.
> > > >
> > > > I have a text type field in an SQL2000 table I need to clean up.
After
> > > > converting from MySQL I am finding that I have leading Tabs and when
I
> > > > display the text field in an asp textbox I end up see the record
> > > > double spaced.
> > > >
> > > > Any suggestion on how I can clean the field up?
> > > >
> > > > Also, as I test an application I just migrated from apache/php/mysql
> > > > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > > > pentium 4 with 512mb I am finding the the MS solution is like 3
times
> > > > slower?
> > > >
> > > > Thanks for any help or info.
> > >
> > >
> >
> >
>|||It took a few practical uses before I cleaned that up. The code is a little
simpler for this specific case, because you don't have to find the index,
and you don't have to loop through... you just replace the first two
characters if they're what you think they are.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ePbcpuUpDHA.2528@.TK2MSFTNGP12.phx.gbl...
> Ahhh, nice... I didn't realize this slightly annoying caveat:
> "Initially I used CHARINDEX, but that failed if the pattern was deeper
than
> 8,000 characters into the value. PATINDEX never dies."sqlsql

Classification crosstab query - 2000

Hello --

I think this is the term for what I want (something that could be generated
in ACCESS using a pivot table, or, maybe Yukon).

We have data for sales by sales people in sales regions. More than one
person sells in a region.

We want to display data as follows:

salesperson's names
---- ---- ---- ---- ----
- ----
region 1
region 2 row/column values are sales amounts for person in
that region
region 3

We will add a WHERE clause for the period of time covered.

I don't want to have to change the query if a new salesperson or new region
is added.

Can this be done in SQL Server 2000, Analysis Services, OLAP, anywhere?

Can someone direct me to examples of how to do this?

Thanks for any direction.

Larry MehlHi

This previous post should help:
http://tinyurl.com/6rhsj

John

"L Mehl" <mehl_nospam@.cyvest.com> wrote in message
news:96uyd.7239$9j5.4696@.newsread3.news.pas.earthl ink.net...
> Hello --
> I think this is the term for what I want (something that could be
> generated
> in ACCESS using a pivot table, or, maybe Yukon).
> We have data for sales by sales people in sales regions. More than one
> person sells in a region.
> We want to display data as follows:
> salesperson's names
> ---- ---- ---- ---- ----
> - ----
> region 1
> region 2 row/column values are sales amounts for person
> in
> that region
> region 3
> We will add a WHERE clause for the period of time covered.
> I don't want to have to change the query if a new salesperson or new
> region
> is added.
> Can this be done in SQL Server 2000, Analysis Services, OLAP, anywhere?
> Can someone direct me to examples of how to do this?
> Thanks for any direction.
> Larry Mehl|||John --

Thanks for pointing me to this article.

It worked right out of the box.

Larry

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:41cace16$0$15616$afc38c87@.news.easynet.co.uk. ..
> Hi
> This previous post should help:
> http://tinyurl.com/6rhsj
> John
> "L Mehl" <mehl_nospam@.cyvest.com> wrote in message
> news:96uyd.7239$9j5.4696@.newsread3.news.pas.earthl ink.net...
> > Hello --
> > I think this is the term for what I want (something that could be
> > generated
> > in ACCESS using a pivot table, or, maybe Yukon).
> > We have data for sales by sales people in sales regions. More than one
> > person sells in a region.
> > We want to display data as follows:
> > salesperson's names
> ---- ---- ---- ---- ---
-
> > - ----
> > region 1
> > region 2 row/column values are sales amounts for person
> > in
> > that region
> > region 3
> > We will add a WHERE clause for the period of time covered.
> > I don't want to have to change the query if a new salesperson or new
> > region
> > is added.
> > Can this be done in SQL Server 2000, Analysis Services, OLAP, anywhere?
> > Can someone direct me to examples of how to do this?
> > Thanks for any direction.
> > Larry Mehlsqlsql

Classic error and question how do restore?

Hi all,
Well, Im here to make a classic question (I guess).
I did droped a table today, there was no backup, exists any possibility to
recover the table?
Before to come here I make a research about this and find few options.
1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
2 use some commands to recover the database and log to a new database at a
certain point of transaction log.
I tried first download the Log Explorer and it restrict me to open only an
example database.
So i look on google and find a lot of command.. all confuse.
So I pressed F1 and read about the RECOVER command and tried this:
I made an backup of my database
and executed the following string in Query Analizer
RESTORE DATABASE NewDB
FROM disk='c:\Program Files\Microsoft SQL
Server\MSSQL\Backup\BrasValor.bak'
WITH NORECOVERY, REPLACE
GO
RESTORE LOG NewDB
FROM disk='c:\Program Files\Microsoft SQL
Server\MSSQL\Backup\BrasValor.bak'
WITH RECOVERY, STOPAT = '21/12/2004'
GO
the restore occours successful, but the table is empty
after this I tried to restore the old database with the LumigentDemoDB name
to use the software, Nice, it is listed but it says tha there is no log for
the database.
And now? what to do?
If you think that u can help me, please, I beg to you.
Reguards,
Luiz
Hi
It is not clear if you followed the correct procedure to restore to point in
time from:
http://msdn.microsoft.com/library/de...ackpc_5a61.asp
you have only done the steps two and four.
John
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, Im here to make a classic question (I guess).
> I did droped a table today, there was no backup, exists any possibility to
> recover the table?
> Before to come here I make a research about this and find few options.
> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
> 2 use some commands to recover the database and log to a new database at a
> certain point of transaction log.
> I tried first download the Log Explorer and it restrict me to open only an
> example database.
> So i look on google and find a lot of command.. all confuse.
> So I pressed F1 and read about the RECOVER command and tried this:
> I made an backup of my database
> and executed the following string in Query Analizer
> RESTORE DATABASE NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH NORECOVERY, REPLACE
> GO
> RESTORE LOG NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH RECOVERY, STOPAT = '21/12/2004'
> GO
> the restore occours successful, but the table is empty
> after this I tried to restore the old database with the LumigentDemoDB
> name to use the software, Nice, it is listed but it says tha there is no
> log for the database.
> And now? what to do?
> If you think that u can help me, please, I beg to you.
> Reguards,
> Luiz
>
|||Luiz
It sounds from your description that you made the backup of the database
AFTER you dropped the table, since you said you had no backup at the time
the error occurred. If that is true, you backed up a copy of the database
with the table already missing, so there is no way that restoring that
database will bring anything back. You must have a backup made BEFORE you
dropped the table.
The Lumigent product might have been able to help before you did the backup
and restore. However, the sample that you downloaded was just a sample. If
you want a product that will save your skin, you really should not expect to
get it for free.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, Im here to make a classic question (I guess).
> I did droped a table today, there was no backup, exists any possibility to
> recover the table?
> Before to come here I make a research about this and find few options.
> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
> 2 use some commands to recover the database and log to a new database at a
> certain point of transaction log.
> I tried first download the Log Explorer and it restrict me to open only an
> example database.
> So i look on google and find a lot of command.. all confuse.
> So I pressed F1 and read about the RECOVER command and tried this:
> I made an backup of my database
> and executed the following string in Query Analizer
> RESTORE DATABASE NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH NORECOVERY, REPLACE
> GO
> RESTORE LOG NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH RECOVERY, STOPAT = '21/12/2004'
> GO
> the restore occours successful, but the table is empty
> after this I tried to restore the old database with the LumigentDemoDB
> name to use the software, Nice, it is listed but it says tha there is no
> log for the database.
> And now? what to do?
> If you think that u can help me, please, I beg to you.
> Reguards,
> Luiz
>
|||Kalen,
Sorry, well, sorry everybody, my english is pretty bad.
Yes, I do not have any backup before the drop command
Looking all night long I discovered that I must make the transaction log
backup, so, I did it, trying to restore it dont let me to restore before
the date of
backup.
Opening the transaction log file (.ldf) in notepad I saw the data that has
been lost. So I still believe that I can recover it, but, how?
Now i'm going to try something, change back the date of computer, make the
backup and try to restore it.
Any other light?
Thank you Kalen and John
[]'s
Luiz
"Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Luiz
> It sounds from your description that you made the backup of the database
> AFTER you dropped the table, since you said you had no backup at the time
> the error occurred. If that is true, you backed up a copy of the database
> with the table already missing, so there is no way that restoring that
> database will bring anything back. You must have a backup made BEFORE you
> dropped the table.
> The Lumigent product might have been able to help before you did the
> backup and restore. However, the sample that you downloaded was just a
> sample. If you want a product that will save your skin, you really should
> not expect to get it for free.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>
|||Hi
You don't say how often the data changes in this table. If it is reasonably
static, you could restore the last full backup (before the problem) to a new
database and then just copy the table back into your live database.
John
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
> Kalen,
> Sorry, well, sorry everybody, my english is pretty bad.
> Yes, I do not have any backup before the drop command
> Looking all night long I discovered that I must make the transaction log
> backup, so, I did it, trying to restore it dont let me to restore before
> the date of
> backup.
> Opening the transaction log file (.ldf) in notepad I saw the data that has
> been lost. So I still believe that I can recover it, but, how?
> Now i'm going to try something, change back the date of computer, make the
> backup and try to restore it.
> Any other light?
> Thank you Kalen and John
> []'s
> Luiz
>
> "Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
> news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
>
|||John,
Every day about 600 rows is inserted on this table, only insert, no delete.
I have no one backup before this happen (damn why?!)
I'll be more especific. I was trying to add a column in this table creating
a new temporary table,
inserting all the data, droping the old and renaming the new.
The error occour inserting all data in the new table.
The way that i commented (set back the date of computer) don't work
restoring the log it says that the STOPAT parameter is wrong (using the MMC
console)
Thank u one more time
Luiz
"John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
> Hi
> You don't say how often the data changes in this table. If it is
> reasonably static, you could restore the last full backup (before the
> problem) to a new database and then just copy the table back into your
> live database.
> John
> "Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
> news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
>
|||>
> Now i'm going to try something, change back the date of computer, make the
> backup and try to restore it.
> Any other light?
>
Seems good to me, but when you restore full data base backup use with
NORECOVERY option, this will give you the chance to restore log, but this
time with RECOVERY option
Regards,
Daniel
|||Hi
It is not a good idea to do ad-hoc SQL on a production system, it may cause
unnecessary locking and excessive resource usage, performing ad-hoc DDL may
cause problems like yours. Keeping your code in a source code control system
will enable you to audit and test changes. It will also allow you to
re-create any version of your database from scratch.
You should also implement a structured backup and maintainance process.
I assume that you have lost your temporary table?
You should not need to change the date of the computer to do the recovery.
Have you tried an earlier time to see if you get some data back?
John
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:etUnKFE6EHA.828@.TK2MSFTNGP14.phx.gbl...
> John,
> Every day about 600 rows is inserted on this table, only insert, no
> delete. I have no one backup before this happen (damn why?!)
> I'll be more especific. I was trying to add a column in this table
> creating a new temporary table,
> inserting all the data, droping the old and renaming the new.
> The error occour inserting all data in the new table.
> The way that i commented (set back the date of computer) don't work
> restoring the log it says that the STOPAT parameter is wrong (using the
> MMC console)
> Thank u one more time
> Luiz
> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
> news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
>
|||Hi John,
are you still in this case?
Well, yes, i lost the temp table (wel, both tables). My script has executed
something like this:
EXEC ('INSERT INTO Temp_Table (bla bla bla bla...') /*Error occours*/
DROP TABLE Original_Table /*Here is the shit*/
bla bla bla
..
..
..
DROP TABLE Temp_Table /*Shit was not complete without this*/
I tried to recover with a date before, but sql says that the date is less
than the minimun date.
I guess i think all possibilities.
Thankyou,
Luiz
"John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
news:u7KVYAF6EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> It is not a good idea to do ad-hoc SQL on a production system, it may
> cause unnecessary locking and excessive resource usage, performing ad-hoc
> DDL may cause problems like yours. Keeping your code in a source code
> control system will enable you to audit and test changes. It will also
> allow you to re-create any version of your database from scratch.
> You should also implement a structured backup and maintainance process.
> I assume that you have lost your temporary table?
> You should not need to change the date of the computer to do the recovery.
> Have you tried an earlier time to see if you get some data back?
> John
> "Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
> news:etUnKFE6EHA.828@.TK2MSFTNGP14.phx.gbl...
>
|||Hi Luiz
I think the best thing you can do is call Microsoft PSS
http://support.microsoft.com/default.aspx, they will charge you for the
incident, but if it is recoverable they will be able to get you back up and
running quickly.
John
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:%23MmR1hA7EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> are you still in this case?
> Well, yes, i lost the temp table (wel, both tables). My script has
> executed something like this:
> EXEC ('INSERT INTO Temp_Table (bla bla bla bla...') /*Error occours*/
> DROP TABLE Original_Table /*Here is the shit*/
> bla bla bla
> .
> .
> .
> DROP TABLE Temp_Table /*Shit was not complete without this*/
>
> I tried to recover with a date before, but sql says that the date is less
> than the minimun date.
> I guess i think all possibilities.
> Thankyou,
> Luiz
> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
> news:u7KVYAF6EHA.2156@.TK2MSFTNGP10.phx.gbl...
>

Classic error and question how do restore?

Hi all,
Well, I´m here to make a classic question (I guess).
I did droped a table today, there was no backup, exists any possibility to
recover the table?
Before to come here I make a research about this and find few options.
1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
2 use some commands to recover the database and log to a new database at a
certain point of transaction log.
I tried first download the Log Explorer and it restrict me to open only an
example database.
So i look on google and find a lot of command.. all confuse.
So I pressed F1 and read about the RECOVER command and tried this:
I made an backup of my database
and executed the following string in Query Analizer
RESTORE DATABASE NewDB
FROM disk='c:\Program Files\Microsoft SQL
Server\MSSQL\Backup\BrasValor.bak'
WITH NORECOVERY, REPLACE
GO
RESTORE LOG NewDB
FROM disk='c:\Program Files\Microsoft SQL
Server\MSSQL\Backup\BrasValor.bak'
WITH RECOVERY, STOPAT = '21/12/2004'
GO
the restore occours successful, but the table is empty
after this I tried to restore the old database with the LumigentDemoDB name
to use the software, Nice, it is listed but it says tha there is no log for
the database.
And now? what to do?
If you think that u can help me, please, I beg to you.
Reguards,
LuizHi
It is not clear if you followed the correct procedure to restore to point in
time from:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_5a61.asp
you have only done the steps two and four.
John
"Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, I´m here to make a classic question (I guess).
> I did droped a table today, there was no backup, exists any possibility to
> recover the table?
> Before to come here I make a research about this and find few options.
> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
> 2 use some commands to recover the database and log to a new database at a
> certain point of transaction log.
> I tried first download the Log Explorer and it restrict me to open only an
> example database.
> So i look on google and find a lot of command.. all confuse.
> So I pressed F1 and read about the RECOVER command and tried this:
> I made an backup of my database
> and executed the following string in Query Analizer
> RESTORE DATABASE NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH NORECOVERY, REPLACE
> GO
> RESTORE LOG NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH RECOVERY, STOPAT = '21/12/2004'
> GO
> the restore occours successful, but the table is empty
> after this I tried to restore the old database with the LumigentDemoDB
> name to use the software, Nice, it is listed but it says tha there is no
> log for the database.
> And now? what to do?
> If you think that u can help me, please, I beg to you.
> Reguards,
> Luiz
>|||Luiz
It sounds from your description that you made the backup of the database
AFTER you dropped the table, since you said you had no backup at the time
the error occurred. If that is true, you backed up a copy of the database
with the table already missing, so there is no way that restoring that
database will bring anything back. You must have a backup made BEFORE you
dropped the table.
The Lumigent product might have been able to help before you did the backup
and restore. However, the sample that you downloaded was just a sample. If
you want a product that will save your skin, you really should not expect to
get it for free.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, I´m here to make a classic question (I guess).
> I did droped a table today, there was no backup, exists any possibility to
> recover the table?
> Before to come here I make a research about this and find few options.
> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
> 2 use some commands to recover the database and log to a new database at a
> certain point of transaction log.
> I tried first download the Log Explorer and it restrict me to open only an
> example database.
> So i look on google and find a lot of command.. all confuse.
> So I pressed F1 and read about the RECOVER command and tried this:
> I made an backup of my database
> and executed the following string in Query Analizer
> RESTORE DATABASE NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH NORECOVERY, REPLACE
> GO
> RESTORE LOG NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH RECOVERY, STOPAT = '21/12/2004'
> GO
> the restore occours successful, but the table is empty
> after this I tried to restore the old database with the LumigentDemoDB
> name to use the software, Nice, it is listed but it says tha there is no
> log for the database.
> And now? what to do?
> If you think that u can help me, please, I beg to you.
> Reguards,
> Luiz
>|||Kalen,
Sorry, well, sorry everybody, my english is pretty bad.
Yes, I do not have any backup before the drop command
Looking all night long I discovered that I must make the transaction log
backup, so, I did it, trying to restore it don´t let me to restore before
the date of
backup.
Opening the transaction log file (.ldf) in notepad I saw the data that has
been lost. So I still believe that I can recover it, but, how?
Now i'm going to try something, change back the date of computer, make the
backup and try to restore it.
Any other light?
Thank you Kalen and John
[]'s
Luiz
"Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Luiz
> It sounds from your description that you made the backup of the database
> AFTER you dropped the table, since you said you had no backup at the time
> the error occurred. If that is true, you backed up a copy of the database
> with the table already missing, so there is no way that restoring that
> database will bring anything back. You must have a backup made BEFORE you
> dropped the table.
> The Lumigent product might have been able to help before you did the
> backup and restore. However, the sample that you downloaded was just a
> sample. If you want a product that will save your skin, you really should
> not expect to get it for free.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m here to make a classic question (I guess).
>> I did droped a table today, there was no backup, exists any possibility
>> to recover the table?
>> Before to come here I make a research about this and find few options.
>> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
>> 2 use some commands to recover the database and log to a new database at
>> a certain point of transaction log.
>> I tried first download the Log Explorer and it restrict me to open only
>> an example database.
>> So i look on google and find a lot of command.. all confuse.
>> So I pressed F1 and read about the RECOVER command and tried this:
>> I made an backup of my database
>> and executed the following string in Query Analizer
>> RESTORE DATABASE NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH NORECOVERY, REPLACE
>> GO
>> RESTORE LOG NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH RECOVERY, STOPAT = '21/12/2004'
>> GO
>> the restore occours successful, but the table is empty
>> after this I tried to restore the old database with the LumigentDemoDB
>> name to use the software, Nice, it is listed but it says tha there is no
>> log for the database.
>> And now? what to do?
>> If you think that u can help me, please, I beg to you.
>> Reguards,
>> Luiz
>|||Hi
You don't say how often the data changes in this table. If it is reasonably
static, you could restore the last full backup (before the problem) to a new
database and then just copy the table back into your live database.
John
"Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
> Kalen,
> Sorry, well, sorry everybody, my english is pretty bad.
> Yes, I do not have any backup before the drop command
> Looking all night long I discovered that I must make the transaction log
> backup, so, I did it, trying to restore it don´t let me to restore before
> the date of
> backup.
> Opening the transaction log file (.ldf) in notepad I saw the data that has
> been lost. So I still believe that I can recover it, but, how?
> Now i'm going to try something, change back the date of computer, make the
> backup and try to restore it.
> Any other light?
> Thank you Kalen and John
> []'s
> Luiz
>
> "Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
> news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
>> Luiz
>> It sounds from your description that you made the backup of the database
>> AFTER you dropped the table, since you said you had no backup at the time
>> the error occurred. If that is true, you backed up a copy of the database
>> with the table already missing, so there is no way that restoring that
>> database will bring anything back. You must have a backup made BEFORE you
>> dropped the table.
>> The Lumigent product might have been able to help before you did the
>> backup and restore. However, the sample that you downloaded was just a
>> sample. If you want a product that will save your skin, you really should
>> not expect to get it for free.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m here to make a classic question (I guess).
>> I did droped a table today, there was no backup, exists any possibility
>> to recover the table?
>> Before to come here I make a research about this and find few options.
>> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
>> 2 use some commands to recover the database and log to a new database at
>> a certain point of transaction log.
>> I tried first download the Log Explorer and it restrict me to open only
>> an example database.
>> So i look on google and find a lot of command.. all confuse.
>> So I pressed F1 and read about the RECOVER command and tried this:
>> I made an backup of my database
>> and executed the following string in Query Analizer
>> RESTORE DATABASE NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH NORECOVERY, REPLACE
>> GO
>> RESTORE LOG NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH RECOVERY, STOPAT = '21/12/2004'
>> GO
>> the restore occours successful, but the table is empty
>> after this I tried to restore the old database with the LumigentDemoDB
>> name to use the software, Nice, it is listed but it says tha there is no
>> log for the database.
>> And now? what to do?
>> If you think that u can help me, please, I beg to you.
>> Reguards,
>> Luiz
>>
>|||John,
Every day about 600 rows is inserted on this table, only insert, no delete.
I have no one backup before this happen (damn why?!)
I'll be more especific. I was trying to add a column in this table creating
a new temporary table,
inserting all the data, droping the old and renaming the new.
The error occour inserting all data in the new table.
The way that i commented (set back the date of computer) don't work
restoring the log it says that the STOPAT parameter is wrong (using the MMC
console)
Thank u one more time
Luiz
"John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
> Hi
> You don't say how often the data changes in this table. If it is
> reasonably static, you could restore the last full backup (before the
> problem) to a new database and then just copy the table back into your
> live database.
> John
> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
> news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
>> Kalen,
>> Sorry, well, sorry everybody, my english is pretty bad.
>> Yes, I do not have any backup before the drop command
>> Looking all night long I discovered that I must make the transaction log
>> backup, so, I did it, trying to restore it don´t let me to restore before
>> the date of
>> backup.
>> Opening the transaction log file (.ldf) in notepad I saw the data that
>> has been lost. So I still believe that I can recover it, but, how?
>> Now i'm going to try something, change back the date of computer, make
>> the backup and try to restore it.
>> Any other light?
>> Thank you Kalen and John
>> []'s
>> Luiz
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
>> news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
>> Luiz
>> It sounds from your description that you made the backup of the database
>> AFTER you dropped the table, since you said you had no backup at the
>> time the error occurred. If that is true, you backed up a copy of the
>> database with the table already missing, so there is no way that
>> restoring that database will bring anything back. You must have a backup
>> made BEFORE you dropped the table.
>> The Lumigent product might have been able to help before you did the
>> backup and restore. However, the sample that you downloaded was just a
>> sample. If you want a product that will save your skin, you really
>> should not expect to get it for free.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m here to make a classic question (I guess).
>> I did droped a table today, there was no backup, exists any possibility
>> to recover the table?
>> Before to come here I make a research about this and find few options.
>> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
>> 2 use some commands to recover the database and log to a new database
>> at a certain point of transaction log.
>> I tried first download the Log Explorer and it restrict me to open only
>> an example database.
>> So i look on google and find a lot of command.. all confuse.
>> So I pressed F1 and read about the RECOVER command and tried this:
>> I made an backup of my database
>> and executed the following string in Query Analizer
>> RESTORE DATABASE NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH NORECOVERY, REPLACE
>> GO
>> RESTORE LOG NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH RECOVERY, STOPAT = '21/12/2004'
>> GO
>> the restore occours successful, but the table is empty
>> after this I tried to restore the old database with the LumigentDemoDB
>> name to use the software, Nice, it is listed but it says tha there is
>> no log for the database.
>> And now? what to do?
>> If you think that u can help me, please, I beg to you.
>> Reguards,
>> Luiz
>>
>>
>|||>
> Now i'm going to try something, change back the date of computer, make the
> backup and try to restore it.
> Any other light?
>
Seems good to me, but when you restore full data base backup use with
NORECOVERY option, this will give you the chance to restore log, but this
time with RECOVERY option
Regards,
Daniel|||Hi
It is not a good idea to do ad-hoc SQL on a production system, it may cause
unnecessary locking and excessive resource usage, performing ad-hoc DDL may
cause problems like yours. Keeping your code in a source code control system
will enable you to audit and test changes. It will also allow you to
re-create any version of your database from scratch.
You should also implement a structured backup and maintainance process.
I assume that you have lost your temporary table?
You should not need to change the date of the computer to do the recovery.
Have you tried an earlier time to see if you get some data back?
John
"Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
news:etUnKFE6EHA.828@.TK2MSFTNGP14.phx.gbl...
> John,
> Every day about 600 rows is inserted on this table, only insert, no
> delete. I have no one backup before this happen (damn why?!)
> I'll be more especific. I was trying to add a column in this table
> creating a new temporary table,
> inserting all the data, droping the old and renaming the new.
> The error occour inserting all data in the new table.
> The way that i commented (set back the date of computer) don't work
> restoring the log it says that the STOPAT parameter is wrong (using the
> MMC console)
> Thank u one more time
> Luiz
> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
> news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
>> Hi
>> You don't say how often the data changes in this table. If it is
>> reasonably static, you could restore the last full backup (before the
>> problem) to a new database and then just copy the table back into your
>> live database.
>> John
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
>> Kalen,
>> Sorry, well, sorry everybody, my english is pretty bad.
>> Yes, I do not have any backup before the drop command
>> Looking all night long I discovered that I must make the transaction log
>> backup, so, I did it, trying to restore it don´t let me to restore
>> before the date of
>> backup.
>> Opening the transaction log file (.ldf) in notepad I saw the data that
>> has been lost. So I still believe that I can recover it, but, how?
>> Now i'm going to try something, change back the date of computer, make
>> the backup and try to restore it.
>> Any other light?
>> Thank you Kalen and John
>> []'s
>> Luiz
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
>> news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
>> Luiz
>> It sounds from your description that you made the backup of the
>> database AFTER you dropped the table, since you said you had no backup
>> at the time the error occurred. If that is true, you backed up a copy
>> of the database with the table already missing, so there is no way that
>> restoring that database will bring anything back. You must have a
>> backup made BEFORE you dropped the table.
>> The Lumigent product might have been able to help before you did the
>> backup and restore. However, the sample that you downloaded was just a
>> sample. If you want a product that will save your skin, you really
>> should not expect to get it for free.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m here to make a classic question (I guess).
>> I did droped a table today, there was no backup, exists any
>> possibility to recover the table?
>> Before to come here I make a research about this and find few options.
>> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
>> 2 use some commands to recover the database and log to a new database
>> at a certain point of transaction log.
>> I tried first download the Log Explorer and it restrict me to open
>> only an example database.
>> So i look on google and find a lot of command.. all confuse.
>> So I pressed F1 and read about the RECOVER command and tried this:
>> I made an backup of my database
>> and executed the following string in Query Analizer
>> RESTORE DATABASE NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH NORECOVERY, REPLACE
>> GO
>> RESTORE LOG NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH RECOVERY, STOPAT = '21/12/2004'
>> GO
>> the restore occours successful, but the table is empty
>> after this I tried to restore the old database with the LumigentDemoDB
>> name to use the software, Nice, it is listed but it says tha there is
>> no log for the database.
>> And now? what to do?
>> If you think that u can help me, please, I beg to you.
>> Reguards,
>> Luiz
>>
>>
>>
>|||Hi John,
are you still in this case?
Well, yes, i lost the temp table (wel, both tables). My script has executed
something like this:
EXEC ('INSERT INTO Temp_Table (bla bla bla bla...') /*Error occours*/
DROP TABLE Original_Table /*Here is the shit*/
bla bla bla
.
.
.
DROP TABLE Temp_Table /*Shit was not complete without this*/
I tried to recover with a date before, but sql says that the date is less
than the minimun date.
I guess i think all possibilities.
Thankyou,
Luiz
"John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
news:u7KVYAF6EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> It is not a good idea to do ad-hoc SQL on a production system, it may
> cause unnecessary locking and excessive resource usage, performing ad-hoc
> DDL may cause problems like yours. Keeping your code in a source code
> control system will enable you to audit and test changes. It will also
> allow you to re-create any version of your database from scratch.
> You should also implement a structured backup and maintainance process.
> I assume that you have lost your temporary table?
> You should not need to change the date of the computer to do the recovery.
> Have you tried an earlier time to see if you get some data back?
> John
> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
> news:etUnKFE6EHA.828@.TK2MSFTNGP14.phx.gbl...
>> John,
>> Every day about 600 rows is inserted on this table, only insert, no
>> delete. I have no one backup before this happen (damn why?!)
>> I'll be more especific. I was trying to add a column in this table
>> creating a new temporary table,
>> inserting all the data, droping the old and renaming the new.
>> The error occour inserting all data in the new table.
>> The way that i commented (set back the date of computer) don't work
>> restoring the log it says that the STOPAT parameter is wrong (using the
>> MMC console)
>> Thank u one more time
>> Luiz
>> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
>> news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
>> Hi
>> You don't say how often the data changes in this table. If it is
>> reasonably static, you could restore the last full backup (before the
>> problem) to a new database and then just copy the table back into your
>> live database.
>> John
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
>> Kalen,
>> Sorry, well, sorry everybody, my english is pretty bad.
>> Yes, I do not have any backup before the drop command
>> Looking all night long I discovered that I must make the transaction
>> log backup, so, I did it, trying to restore it don´t let me to restore
>> before the date of
>> backup.
>> Opening the transaction log file (.ldf) in notepad I saw the data that
>> has been lost. So I still believe that I can recover it, but, how?
>> Now i'm going to try something, change back the date of computer, make
>> the backup and try to restore it.
>> Any other light?
>> Thank you Kalen and John
>> []'s
>> Luiz
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
>> news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
>> Luiz
>> It sounds from your description that you made the backup of the
>> database AFTER you dropped the table, since you said you had no backup
>> at the time the error occurred. If that is true, you backed up a copy
>> of the database with the table already missing, so there is no way
>> that restoring that database will bring anything back. You must have a
>> backup made BEFORE you dropped the table.
>> The Lumigent product might have been able to help before you did the
>> backup and restore. However, the sample that you downloaded was just a
>> sample. If you want a product that will save your skin, you really
>> should not expect to get it for free.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m here to make a classic question (I guess).
>> I did droped a table today, there was no backup, exists any
>> possibility to recover the table?
>> Before to come here I make a research about this and find few
>> options.
>> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
>> 2 use some commands to recover the database and log to a new database
>> at a certain point of transaction log.
>> I tried first download the Log Explorer and it restrict me to open
>> only an example database.
>> So i look on google and find a lot of command.. all confuse.
>> So I pressed F1 and read about the RECOVER command and tried this:
>> I made an backup of my database
>> and executed the following string in Query Analizer
>> RESTORE DATABASE NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH NORECOVERY, REPLACE
>> GO
>> RESTORE LOG NewDB
>> FROM disk='c:\Program Files\Microsoft SQL
>> Server\MSSQL\Backup\BrasValor.bak'
>> WITH RECOVERY, STOPAT = '21/12/2004'
>> GO
>> the restore occours successful, but the table is empty
>> after this I tried to restore the old database with the
>> LumigentDemoDB name to use the software, Nice, it is listed but it
>> says tha there is no log for the database.
>> And now? what to do?
>> If you think that u can help me, please, I beg to you.
>> Reguards,
>> Luiz
>>
>>
>>
>>
>|||Hi Luiz
I think the best thing you can do is call Microsoft PSS
http://support.microsoft.com/default.aspx, they will charge you for the
incident, but if it is recoverable they will be able to get you back up and
running quickly.
John
"Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
news:%23MmR1hA7EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> are you still in this case?
> Well, yes, i lost the temp table (wel, both tables). My script has
> executed something like this:
> EXEC ('INSERT INTO Temp_Table (bla bla bla bla...') /*Error occours*/
> DROP TABLE Original_Table /*Here is the shit*/
> bla bla bla
> .
> .
> .
> DROP TABLE Temp_Table /*Shit was not complete without this*/
>
> I tried to recover with a date before, but sql says that the date is less
> than the minimun date.
> I guess i think all possibilities.
> Thankyou,
> Luiz
> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
> news:u7KVYAF6EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> Hi
>> It is not a good idea to do ad-hoc SQL on a production system, it may
>> cause unnecessary locking and excessive resource usage, performing ad-hoc
>> DDL may cause problems like yours. Keeping your code in a source code
>> control system will enable you to audit and test changes. It will also
>> allow you to re-create any version of your database from scratch.
>> You should also implement a structured backup and maintainance process.
>> I assume that you have lost your temporary table?
>> You should not need to change the date of the computer to do the
>> recovery.
>> Have you tried an earlier time to see if you get some data back?
>> John
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:etUnKFE6EHA.828@.TK2MSFTNGP14.phx.gbl...
>> John,
>> Every day about 600 rows is inserted on this table, only insert, no
>> delete. I have no one backup before this happen (damn why?!)
>> I'll be more especific. I was trying to add a column in this table
>> creating a new temporary table,
>> inserting all the data, droping the old and renaming the new.
>> The error occour inserting all data in the new table.
>> The way that i commented (set back the date of computer) don't work
>> restoring the log it says that the STOPAT parameter is wrong (using the
>> MMC console)
>> Thank u one more time
>> Luiz
>> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
>> news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
>> Hi
>> You don't say how often the data changes in this table. If it is
>> reasonably static, you could restore the last full backup (before the
>> problem) to a new database and then just copy the table back into your
>> live database.
>> John
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
>> Kalen,
>> Sorry, well, sorry everybody, my english is pretty bad.
>> Yes, I do not have any backup before the drop command
>> Looking all night long I discovered that I must make the transaction
>> log backup, so, I did it, trying to restore it don´t let me to restore
>> before the date of
>> backup.
>> Opening the transaction log file (.ldf) in notepad I saw the data that
>> has been lost. So I still believe that I can recover it, but, how?
>> Now i'm going to try something, change back the date of computer, make
>> the backup and try to restore it.
>> Any other light?
>> Thank you Kalen and John
>> []'s
>> Luiz
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
>> news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
>> Luiz
>> It sounds from your description that you made the backup of the
>> database AFTER you dropped the table, since you said you had no
>> backup at the time the error occurred. If that is true, you backed up
>> a copy of the database with the table already missing, so there is no
>> way that restoring that database will bring anything back. You must
>> have a backup made BEFORE you dropped the table.
>> The Lumigent product might have been able to help before you did the
>> backup and restore. However, the sample that you downloaded was just
>> a sample. If you want a product that will save your skin, you really
>> should not expect to get it for free.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>>> Hi all,
>>>
>>> Well, I´m here to make a classic question (I guess).
>>> I did droped a table today, there was no backup, exists any
>>> possibility to recover the table?
>>>
>>> Before to come here I make a research about this and find few
>>> options.
>>> 1 use the "Log Explorer" from Lumigent to "rollback" the
>>> transaction.
>>> 2 use some commands to recover the database and log to a new
>>> database at a certain point of transaction log.
>>>
>>> I tried first download the Log Explorer and it restrict me to open
>>> only an example database.
>>> So i look on google and find a lot of command.. all confuse.
>>>
>>> So I pressed F1 and read about the RECOVER command and tried this:
>>> I made an backup of my database
>>> and executed the following string in Query Analizer
>>>
>>> RESTORE DATABASE NewDB
>>> FROM disk='c:\Program Files\Microsoft SQL
>>> Server\MSSQL\Backup\BrasValor.bak'
>>> WITH NORECOVERY, REPLACE
>>> GO
>>> RESTORE LOG NewDB
>>> FROM disk='c:\Program Files\Microsoft SQL
>>> Server\MSSQL\Backup\BrasValor.bak'
>>> WITH RECOVERY, STOPAT = '21/12/2004'
>>> GO
>>>
>>> the restore occours successful, but the table is empty
>>> after this I tried to restore the old database with the
>>> LumigentDemoDB name to use the software, Nice, it is listed but it
>>> says tha there is no log for the database.
>>>
>>> And now? what to do?
>>>
>>> If you think that u can help me, please, I beg to you.
>>>
>>> Reguards,
>>> Luiz
>>>
>>
>>
>>
>>
>>
>

Classes Per Table ( and vice versa ) Design

I work on the Clinical Surgery Department Data Application.
The patient gets through the Admission, Surgery, PostSurgery and Discharge stages during his staying in the department. The Doctors just insert their medical data in to the different tables. I have three main classes: User, Patient and Portfolio ( patients portfolio ) and all the business bases on these classes.
The problem is with the Classes per Medical Data Tables. The Medical tables should exist cause it includes the medical data about the patient however what is not clear: Whether I should create Business and Entity classes per each medical data table ( and this is something like 50 tables ) or I should insert all the functions in the Portfolio class and then there will be a mess of functions in one class? What is the accepted way to perform it ?I'd create a base portfolio class, then create sub-classes for each of the sub-types. Put the common functions for all portfolio entries in the parent class, then put the specific functions into the appropriate sub-class.

-PatP

Class not registered

Hi,

when i am trying to open a table from Microsoft Visua Studio 2005
I got the following error

Class Not Registered.Loking for object with classID:......

how resolve this?

Hi,

What is the class ID that it complains about. This seems more like a Visual Studio setup/installation issue than a SQL Server Data Access issue.

Thanks

Waseem

Class does not support aggregation

In Microsoft SQL Server Management Studio 2005 whenever I open a table I get
the error
Class does not support aggregation (or class object is remote) (Exception
from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION))
(Microsoft.SqlServer.SqlTools.VSIntegration)
I previously had SQL Server 2000 installed, but the upgrade worked
successfully.
Could someone please advise?
Many thanks
Richard.
Hello Richard,
This is a COM interop related issue:
http://msdn.microsoft.com/library/de...us/com/html/3b
414b95-e8d2-42e8-b4f2-5cc5189a3d08.asp
You may want to remove Workstation componenents, then do a repair on Net
Framework 2.0 then Re-Install workstation components to test:
http://forums.microsoft.com/MSDN/Sho...78936&SiteID=1
If the issue persists, please make sure you have removed all SQL 2000
components including SQL client tool to test the situation again.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.
--
>Thread-Topic: Class does not support aggregation
>thread-index: AcYdzJqnv7Gvd6SmT+qRbL0nvajMoA==
>X-WBNR-Posting-Host: 194.131.103.210
>From: "=?Utf-8?B?UmljaA==?=" <richvista@.nospam.nospam>
>Subject: Class does not support aggregation
>Date: Fri, 20 Jan 2006 06:20:03 -0800
>Lines: 13
>Message-ID: <801EF9EE-69E9-4B04-902C-E7BC6C98D685@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:418324
>X-Tomcat-NG: microsoft.public.sqlserver.server
>In Microsoft SQL Server Management Studio 2005 whenever I open a table I
get
>the error
>--
>Class does not support aggregation (or class object is remote) (Exception
>from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION))
>(Microsoft.SqlServer.SqlTools.VSIntegration)
>I previously had SQL Server 2000 installed, but the upgrade worked
>successfully.
>Could someone please advise?
>Many thanks
>Richard.
>
|||Thank you Peter, I did as you suggested and it now works ok.
"Peter Yang [MSFT]" wrote:

> Hello Richard,
> This is a COM interop related issue:
> http://msdn.microsoft.com/library/de...us/com/html/3b
> 414b95-e8d2-42e8-b4f2-5cc5189a3d08.asp
> You may want to remove Workstation componenents, then do a repair on Net
> Framework 2.0 then Re-Install workstation components to test:
> http://forums.microsoft.com/MSDN/Sho...78936&SiteID=1
> If the issue persists, please make sure you have removed all SQL 2000
> components including SQL client tool to test the situation again.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner 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.
>
> --
> get
>

Class does not support aggregation

In Microsoft SQL Server Management Studio 2005 whenever I open a table I get
the error
--
Class does not support aggregation (or class object is remote) (Exception
from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION))
(Microsoft.SqlServer.SqlTools.VSIntegration)
I previously had SQL Server 2000 installed, but the upgrade worked
successfully.
Could someone please advise?
Many thanks
Richard.Hello Richard,
This is a COM interop related issue:
http://msdn.microsoft.com/library/d...-us/com/html/3b
414b95-e8d2-42e8-b4f2-5cc5189a3d08.asp
You may want to remove Workstation componenents, then do a repair on Net
Framework 2.0 then Re-Install workstation components to test:
http://forums.microsoft.com/MSDN/Sh...178936&SiteID=1
If the issue persists, please make sure you have removed all SQL 2000
components including SQL client tool to test the situation again.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.
--
>Thread-Topic: Class does not support aggregation
>thread-index: AcYdzJqnv7Gvd6SmT+qRbL0nvajMoA==
>X-WBNR-Posting-Host: 194.131.103.210
>From: "examnotes" <richvista@.nospam.nospam>
>Subject: Class does not support aggregation
>Date: Fri, 20 Jan 2006 06:20:03 -0800
>Lines: 13
>Message-ID: <801EF9EE-69E9-4B04-902C-E7BC6C98D685@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:418324
>X-Tomcat-NG: microsoft.public.sqlserver.server
>In Microsoft SQL Server Management Studio 2005 whenever I open a table I
get
>the error
>--
>Class does not support aggregation (or class object is remote) (Exception
>from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION))
>(Microsoft.SqlServer.SqlTools.VSIntegration)
>I previously had SQL Server 2000 installed, but the upgrade worked
>successfully.
>Could someone please advise?
>Many thanks
>Richard.
>|||Thank you Peter, I did as you suggested and it now works ok.
"Peter Yang [MSFT]" wrote:

> Hello Richard,
> This is a COM interop related issue:
> l]
> 414b95-e8d2-42e8-b4f2-5cc5189a3d08.asp
> You may want to remove Workstation componenents, then do a repair on Net
> Framework 2.0 then Re-Install workstation components to test:
> [url]http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=178936&SiteID=1" target="_blank">http://msdn.microsoft.com/library/d...178936&SiteID=1
> If the issue persists, please make sure you have removed all SQL 2000
> components including SQL client tool to test the situation again.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner 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
.
>
> --
> get
>