Delete Database

A database disappeared from one of our qa servers last night yet when we looked at the logs no record of the drop datbase command was to be found like wise no record of create database.
Can any one tell me where we can look in sqlserver or on the windows 2000 server fro a record of these eventsYikes! I'd never really looked at my SQL Error log files when I dropped a database, but it appears that there is no entry for this event in there. I dunno where to tell you to go from here...

I would suggest that you audit your security settings, remove anyone from the sysadmin group that doesn't absolutely positively have to be there and for sure set up to audit failed logins.

Regards,

hmscott|||Might try the event logs on the server. At least you'll have an idea who was doing what and when.|||Thanks for the advice fortunately it was a dev server so no real damange and we could restore it.
Lokks like there may be a case to go to microsoft and see if it could be added .

Delete Data

Simple question, what is that I should use to delete data from one table and
keep the table's definition? DROP TABLE deletes everything and I need to
maintain the table's definition.
Thanks a lot.
TSYou can use "truncate table table_name" or "delete table_name". The first on
e
is not a logged operation.
AMB
"TS" wrote:

> Simple question, what is that I should use to delete data from one table a
nd
> keep the table's definition? DROP TABLE deletes everything and I need to
> maintain the table's definition.
> Thanks a lot.
> --
> TS|||In order to do a truncate table, there must be no foreign keys...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"TS" <TS@.discussions.microsoft.com> wrote in message
news:A95E62C6-A8C1-4590-B71C-DE8726761C9F@.microsoft.com...
> Simple question, what is that I should use to delete data from one table
> and
> keep the table's definition? DROP TABLE deletes everything and I need to
> maintain the table's definition.
> Thanks a lot.
> --
> TS|||If there are foreign keys pointing to the table, you need to clean those up
first.
If there are foreign keys in the table pointing elsewhere, you need to say
DELETE tablename
Otherwise you can use
TRUNCATE TABLE tablename
(Which also resets the IDENTITY seed if such a column exists.)
"TS" <TS@.discussions.microsoft.com> wrote in message
news:A95E62C6-A8C1-4590-B71C-DE8726761C9F@.microsoft.com...
> Simple question, what is that I should use to delete data from one table
> and
> keep the table's definition? DROP TABLE deletes everything and I need to
> maintain the table's definition.
> Thanks a lot.
> --
> TSsql

Delete Column

I want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is store in the table below. If the UdateMonth is not null then I want to delete.

FinancilaPeriodMonthUdateMonth
ThisYearMonthEnd01Jan
ThisYearMonthEnd02Feb
ThisYearMonthEnd03Mar
ThisYearMonthEnd04Apr
ThisYearMonthEnd05May
ThisYearMonthEnd06Jun
ThisYearMonthEnd07Jul
ThisYearMonthEnd08Aug
ThisYearMonthEnd09Sep
ThisYearMonthEnd10OctOct
ThisYearMonthEnd11Nov
ThisYearMonthEnd12Dec

Can anyone help.

ThanksI want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.

FinancilaPeriod Month UdateMonth
ThisYearMonthEnd01 Jan
ThisYearMonthEnd02 Feb
ThisYearMonthEnd03 Mar
ThisYearMonthEnd04 Apr
ThisYearMonthEnd05 May
ThisYearMonthEnd06 Jun
ThisYearMonthEnd07 Jul
ThisYearMonthEnd08 Aug
ThisYearMonthEnd09 Sep
ThisYearMonthEnd10 Oct Oct
ThisYearMonthEnd11 Nov
ThisYearMonthEnd12 Dec

Can anyone help.

Thanks|||------------------------

I want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.

FinancilaPeriod Month UdateMonth
ThisYearMonthEnd01 Jan
ThisYearMonthEnd02 Feb
ThisYearMonthEnd03 Mar
ThisYearMonthEnd04 Apr
ThisYearMonthEnd05 May
ThisYearMonthEnd06 Jun
ThisYearMonthEnd07 Jul
ThisYearMonthEnd08 Aug
ThisYearMonthEnd09 Sep
ThisYearMonthEnd10 Oct Oct
ThisYearMonthEnd11 Nov
ThisYearMonthEnd12 Dec

Can anyone help.

Thanks|||------------------------

I want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.

FinancilaPeriod Month UdateMonth
ThisYearMonthEnd01 Jan
ThisYearMonthEnd02 Feb
ThisYearMonthEnd03 Mar
ThisYearMonthEnd04 Apr
ThisYearMonthEnd05 May
ThisYearMonthEnd06 Jun
ThisYearMonthEnd07 Jul
ThisYearMonthEnd08 Aug
ThisYearMonthEnd09 Sep
ThisYearMonthEnd10 Oct Oct
ThisYearMonthEnd11 Nov
ThisYearMonthEnd12 Dec

Can anyone help.

Thanks|||A table is nothing more then a collection of columns. If you want to remove a column you do it for the entire table. You cannot just drop a column for one specific row when it contains some data. When you drop a column, it removes that entire column from the table, data or not.

Ronald :cool:.|||

Quote:

Originally Posted by shieldsco

------------------------

I want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.

FinancilaPeriod Month UdateMonth
ThisYearMonthEnd01 Jan
ThisYearMonthEnd02 Feb
ThisYearMonthEnd03 Mar
ThisYearMonthEnd04 Apr
ThisYearMonthEnd05 May
ThisYearMonthEnd06 Jun
ThisYearMonthEnd07 Jul
ThisYearMonthEnd08 Aug
ThisYearMonthEnd09 Sep
ThisYearMonthEnd10 Oct Oct
ThisYearMonthEnd11 Nov
ThisYearMonthEnd12 Dec

Can anyone help.

Thanks

hi,can u please tell me, do u want to delete a column or a row? if u really want to delete a row whose FinancilaPeriod Month value is ThisYearMonthEnd10. u can use the following statement
delete from tblMarketing where FinancilaPeriodMonth = 'ThisYearMonthEnd10'

I hope i understood ur problem correctly.|||

Quote:

Originally Posted by Anu139

hi,can u please tell me, do u want to delete a column or a row? if u really want to delete a row whose FinancilaPeriod Month value is ThisYearMonthEnd10. u can use the following statement
delete from tblMarketing where FinancilaPeriodMonth = 'ThisYearMonthEnd10'

I hope i understood ur problem correctly.

I really want to delete a column based on the following example:
Table 1
FinancilaPeriodMonthUdateMonth
ThisYearMonthEnd01Jan
ThisYearMonthEnd02Feb
ThisYearMonthEnd03Mar
ThisYearMonthEnd04Apr
ThisYearMonthEnd05May
ThisYearMonthEnd06Jun
ThisYearMonthEnd07Jul
ThisYearMonthEnd08Aug
ThisYearMonthEnd09Sep
ThisYearMonthEnd10OctOct
ThisYearMonthEnd11Nov
ThisYearMonthEnd12Dec

Table 2
ThisYearMonthEnd01ThisYearMonthEnd02ThisYearMonthEnd10
I want to delete the cloumn ThisYearMonthEnd10 in Table 2 based on the the UdateMonth in table 1.|||Hi shieldsco,

I hope you are getting the help you want. However I would like to request that in future you don't double (or in this case quadruple) post the same question to the same forum.

If you think you question/problem has been overlooked then please post a reply to it yourself so that it gets bumped to the top of the Forum list.

I have now merged your 4 threads on this subject into this single thread.

Regards
Banfa|||

Quote:

Originally Posted by Banfa

Hi shieldsco,

I hope you are getting the help you want. However I would like to request that in future you don't double (or in this case quadruple) post the same question to the same forum.

If you think you question/problem has been overlooked then please post a reply to it yourself so that it gets bumped to the top of the Forum list.

I have now merged your 4 threads on this subject into this single thread.

Regards
Banfa

Actually two of threads were futher explanation of the problem.

Delete & create a partition

Hello All,
I am working on a hugeee table partionned in 20.
Working on one partition at one time, I need to drop and re-create a
partition before inserting treated data.
Anyone know if I can drop then re-create a partion ? if yes, How. if no, any
alternative like truncate partion maybe...
Thanks !!
Arnold.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
To effectively truncate a partition, SWITCH the desired partition into a
staging table. The staging table needs to be on the same filegroup(s) with
like schema and indexes. You can then drop or truncate the staging table to
permanently remove the data.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> Hello All,
> I am working on a hugeee table partionned in 20.
> Working on one partition at one time, I need to drop and re-create a
> partition before inserting treated data.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
> Thanks !!
> Arnold.
|||But what do I do after doing the truncate on the staging table ? how do I go
back to main table ? i need some kind of "switch back" to original table
partition ?
Arnold
"Dan Guzman" wrote:

> To effectively truncate a partition, SWITCH the desired partition into a
> staging table. The staging table needs to be on the same filegroup(s) with
> like schema and indexes. You can then drop or truncate the staging table to
> permanently remove the data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.no" <rno@.discussions.microsoft.com> wrote in message
> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
>
|||After the switch out, the source partition will still exist with the same
boundaries but will be empty. No need to switch anything back.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:33DB4230-786C-4661-9905-9A23D5CE3C84@.microsoft.com...[vbcol=seagreen]
> But what do I do after doing the truncate on the staging table ? how do I
> go
> back to main table ? i need some kind of "switch back" to original table
> partition ?
> --
> Arnold
>
> "Dan Guzman" wrote:

Delete & create a partition

Hello All,
I am working on a hugeee table partionned in 20.
Working on one partition at one time, I need to drop and re-create a
partition before inserting treated data.
Anyone know if I can drop then re-create a partion ? if yes, How. if no, any
alternative like truncate partion maybe...
Thanks !!
Arnold.> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
To effectively truncate a partition, SWITCH the desired partition into a
staging table. The staging table needs to be on the same filegroup(s) with
like schema and indexes. You can then drop or truncate the staging table to
permanently remove the data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> Hello All,
> I am working on a hugeee table partionned in 20.
> Working on one partition at one time, I need to drop and re-create a
> partition before inserting treated data.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
> Thanks !!
> Arnold.|||But what do I do after doing the truncate on the staging table ? how do I go
back to main table ? i need some kind of "switch back" to original table
partition ?
--
Arnold
"Dan Guzman" wrote:
> > Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> > any
> > alternative like truncate partion maybe...
> To effectively truncate a partition, SWITCH the desired partition into a
> staging table. The staging table needs to be on the same filegroup(s) with
> like schema and indexes. You can then drop or truncate the staging table to
> permanently remove the data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.no" <rno@.discussions.microsoft.com> wrote in message
> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> > Hello All,
> >
> > I am working on a hugeee table partionned in 20.
> >
> > Working on one partition at one time, I need to drop and re-create a
> > partition before inserting treated data.
> >
> > Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> > any
> > alternative like truncate partion maybe...
> >
> > Thanks !!
> > Arnold.
>|||After the switch out, the source partition will still exist with the same
boundaries but will be empty. No need to switch anything back.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:33DB4230-786C-4661-9905-9A23D5CE3C84@.microsoft.com...
> But what do I do after doing the truncate on the staging table ? how do I
> go
> back to main table ? i need some kind of "switch back" to original table
> partition ?
> --
> Arnold
>
> "Dan Guzman" wrote:
>> > Anyone know if I can drop then re-create a partion ? if yes, How. if
>> > no,
>> > any
>> > alternative like truncate partion maybe...
>> To effectively truncate a partition, SWITCH the desired partition into a
>> staging table. The staging table needs to be on the same filegroup(s)
>> with
>> like schema and indexes. You can then drop or truncate the staging table
>> to
>> permanently remove the data.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "r.no" <rno@.discussions.microsoft.com> wrote in message
>> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
>> > Hello All,
>> >
>> > I am working on a hugeee table partionned in 20.
>> >
>> > Working on one partition at one time, I need to drop and re-create a
>> > partition before inserting treated data.
>> >
>> > Anyone know if I can drop then re-create a partion ? if yes, How. if
>> > no,
>> > any
>> > alternative like truncate partion maybe...
>> >
>> > Thanks !!
>> > Arnold.

Delete & create a partition

Hello All,
I am working on a hugeee table partionned in 20.
Working on one partition at one time, I need to drop and re-create a
partition before inserting treated data.
Anyone know if I can drop then re-create a partion ? if yes, How. if no, any
alternative like truncate partion maybe...
Thanks !!
Arnold.> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
To effectively truncate a partition, SWITCH the desired partition into a
staging table. The staging table needs to be on the same filegroup(s) with
like schema and indexes. You can then drop or truncate the staging table to
permanently remove the data.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> Hello All,
> I am working on a hugeee table partionned in 20.
> Working on one partition at one time, I need to drop and re-create a
> partition before inserting treated data.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
> Thanks !!
> Arnold.|||But what do I do after doing the truncate on the staging table ? how do I go
back to main table ? i need some kind of "switch back" to original table
partition ?
Arnold
"Dan Guzman" wrote:

> To effectively truncate a partition, SWITCH the desired partition into a
> staging table. The staging table needs to be on the same filegroup(s) wit
h
> like schema and indexes. You can then drop or truncate the staging table
to
> permanently remove the data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.no" <rno@.discussions.microsoft.com> wrote in message
> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
>|||After the switch out, the source partition will still exist with the same
boundaries but will be empty. No need to switch anything back.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:33DB4230-786C-4661-9905-9A23D5CE3C84@.microsoft.com...[vbcol=seagreen]
> But what do I do after doing the truncate on the staging table ? how do I
> go
> back to main table ? i need some kind of "switch back" to original table
> partition ?
> --
> Arnold
>
> "Dan Guzman" wrote:
>

define default for date report parameter / analysis services

I have a report which will one day display some data from an analysis services cube. my first step is to create a drop down parameter enabling the user to choose the date. I'd like to display only dates that have data, and I'd like it to default to today.

So I've created a dataset that will be the datasource for the dropdown displaying the available non-empty dates, which works fine.

SELECT measures.turnover ON COLUMNS,

nonempty([TBL DIM DATE].[DATE_ONLY].[DATE_ONLY].ALLMEMBERS ) ON ROWS

FROM [Itdev1 Hk]

I've also set the report parameter up to be a queried paramter,and to use the above dataset as it source, with [DATE_ONLY] displayed. and [DATE_ONLY] as the value.

Now, how do I get it to default to the last valid member in the list?

I presume you are trying to have the latest date selected by default? If so, return your dataset in descending order (do an ORDER(<set>, DESC) on the rows), so that your latest date is at the top of the list.

|||this is helpful since now when I click the drop down the most likely values for me to use are at the top. but it has not caused any value to be selected by default.
|||You can use the same dataset in the default value query, which seems to collapse to the first row returned. I have no idea what the implications of doing this are, I happened on it by accident.|||

this is a helpful tip!

|||

i;m attempting to order by date in descending order. but it seems to sort in a random order when I do this ....

SELECT NON EMPTY { [Measures].[TURNOVER - WM INTERDAY] } ON COLUMNS, NON EMPTY { order([Time].[Date].[Date].ALLMEMBERS, [Time].[Date], desc ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Itdev1 Hk] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

and then in alphabetical order when I sort like this

SELECT NON EMPTY { [Measures].[TURNOVER - WM INTERDAY] } ON COLUMNS, NON EMPTY { order([Time].[Date].[Date].ALLMEMBERS, [Time].[Date].MemberValue, desc ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Itdev1 Hk] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

how do I get it to sort by real date?

Defaulting the export option

Is there a way to control the contents of the report export drop down
list? Specifically, I would like to set a default export to PDF
(instead of the "Select a format" entry)You can force the rendering format for that report but I don't think there is
a way to default the drop-down selection.
Here is a PDF render format against the AdventureWorks sample reports (the
report gets generated in PDF):
//localhost/ReportServer?/AdventureWorks Sample Reports/Company
Sales&rs:Format=PDF&rs:Command=Render
"Paul" wrote:
> Is there a way to control the contents of the report export drop down
> list? Specifically, I would like to set a default export to PDF
> (instead of the "Select a format" entry)

defaultcodepage is set to false

when ever i drop a ole db source or destination control on data flow, upon clicking to edit it complains about -- defaultcodepage is set to false. When i check in the properties and set it to true; i get no error. What is it all about?

kushpaw

codepage has to do with whether your columns in the table have data that are in other languages.... seting it to true will ask SSIS to take the defualt translation that it thinks fits your data.

Default value in textboxes are lost when a drop down does a post b

We have a couple of dropdown boxes and a text field as parameters to a report.
The options in the second dropdown box depends on the selection of the first
one, so I'm happy with the fact that a post back occurs when the selected
option in dropdown box 1 is changed.
However the value entered by the user in the text box get overriden by the
default value, that I'm not too happy with.
Is there a way of preserving user input?
Fred.
--
FredFred, have you tried NOT setting a default for the second parameter?
If that doesn't work then I think the answer is no!
Chris
Fred wrote:
> We have a couple of dropdown boxes and a text field as parameters to
> a report.
> The options in the second dropdown box depends on the selection of
> the first one, so I'm happy with the fact that a post back occurs
> when the selected option in dropdown box 1 is changed.
> However the value entered by the user in the text box get overriden
> by the default value, that I'm not too happy with.
> Is there a way of preserving user input?
> Fred.

Default Value for Non-Queried Drop Down Parameter

I'm new to reporting services. Please forgive me if this is a stupid
question.
I have several different parameters in my report. Some are from
queries. I set my default value as Non-Queried with a valid value
from my query, and it works fine. Some of my other parameters are Non-
Queried where I have keyed in the appropriate labels and values. When
I run my report, I can see and select all of the values that I keyed.
I tried to set the default value as Non-Queried with a valid value
from the values that I keyed, and when I run my report, it still comes
up with Select a Value. Can you set defaults for Non-Queried
parameters? Why isn't this working?On Jul 11, 6:22 pm, kkrizl <kkr...@.co.el-dorado.ca.us> wrote:
> I'm new to reporting services. Please forgive me if this is a stupid
> question.
> I have several different parameters in my report. Some are from
> queries. I set my default value as Non-Queried with a valid value
> from my query, and it works fine. Some of my other parameters are Non-
> Queried where I have keyed in the appropriate labels and values. When
> I run my report, I can see and select all of the values that I keyed.
> I tried to set the default value as Non-Queried with a valid value
> from the values that I keyed, and when I run my report, it still comes
> up with Select a Value. Can you set defaults for Non-Queried
> parameters? Why isn't this working?
To use a default for a non-queried parameter, you need to set the
Default Value to one of the Values in the Available Values section. It
seems like you are setting it to a Label from the Available Values
section. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant