Named Set in Excel Pivot Table Displays as Enumeration

Pardon me if this is really an Excel 2007 Pivot table question. I have a named set defined in the cube as:

CREATE SET CURRENTCUBE. [Team Building Overall]

AS {[Question].[Short Name].[Q-02],

[Question].[Short Name].[Q-03],

[Question].[Short Name].[Q-05],

[Question].[Short Name].[Q-06],

[Question].[Short Name].[Q-08],

[Question].[Short Name].[Q-13],

[Question].[Short Name].[Q-17],

[Question].[Short Name].[Q-18],

[Question].[Short Name].[Q-21],

[Question].[Short Name].[Q-30],

[Question].[Short Name].[Q-31]};

And can display measure values for this set as whole in Proclarity with:

SELECT { [Measures].[Average]} ON COLUMNS ,

{ [Corporate Hierarchy].[Hierarchy].DEFAULTMEMBER } ON ROWS

FROM [360 Training Survey]

WHERE ([Team Building Overall], [Test Month].[Test Month].&[200704] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

In Excel 2007, unfortunately I can not use this set as a filter as I can in Proclarity. Rather, the best I can accomplish is to display this as 11 different columns, one for each member of this named set.

Any suggestions ?

Anyone ?

I figure I can create a calculated measure in the cube named [Team Building Overall Average] which is defined as ([Team Building Overall],[measures].[Average]), but this seems a bit klutzy, especially given that I have eight sets and six measures, thus resulting in a total of 48 calculate measures I need to create.

|||

You could create a measure independant calculated member in the Question dimension as follows.

CREATE MEMBER CurrentCube.[Question].[Short Name].[Team Building Overall] AS AGGREGATE([Team Building Overall])

If the only reason that you have the sets is for filtering you could put the set definition inline in this memeber, otherwise you could leave them separate.

NAMED SET / PIVOT TABLE ! HELP ME

NEED KNOW IF SOMETHING KNOW CERTANLY ....ABOUT THIS :

************************************************** ********************************
PLACE A NAMED SET INTO A PIVOT TABLE : ALL ABOUT THIS..FORMULAS...FORMS...
EXAMPLES... ALL !

ANOTHER : GET RELATIONSHIP ABOUT THE FIRST TROUBLE : SEE A NAMED SET FROM A
CUBE ( OLAP)

************************************************** ************************************************

REALLY NEED ALL ABOUT YOU CAN SEND ME.AND WRITE ME ...A OLNLY FIN MANY
QUESTIONS IN FORUMS...BUT NO ONE RESPONSES....APARENTLY TOO MUCH PEOPLE
HAVE THIS TROUBLE ..... I KNOW ...NO EVERY DAY .MEMBERS THAT POST IN THIS
WEBSITE..TAKE REPLY...BUT......, REALLY NEED INFO ABOUT .AS SOON AS
POSSIBLE...
MI E-MAIL :
THANABULLET@.HOTMAIL.COM

THANKS !.
MY BEST WISHES...

--
hi all !wow

it shows up just like a dimension i thought

are you using the olap 8.0 oledb provider?

-aaron
msolap1.1 or somethign right

Named Query and Global Variables

Hello,
I want to filter records right in the Data Source View.
I did a "Replace Table with new Named Query" but how can I get it to filter
records by User!UserID?
Thanks,
-Will"I want to filter records right in the Data Source View."
What do you mean by this?
you want to filter your DATA? or do you want to set different
permissions for different users?|||I want to filter my data.
So "select * from table where field1=user!userid"
"sorcerdon@.gmail.com" wrote:
> "I want to filter records right in the Data Source View."
> What do you mean by this?
> you want to filter your DATA? or do you want to set different
> permissions for different users?
>|||Query parameters do not have to map to report parameters. RS automatically
creates a report parameter for every query parameter but you can delete them
and use an expression.
So, do this:
select * from mytable where field1 = @.User
RS will create automatically a report parameter called User. Instead of
using this mapping, you want to change it.
Click on the ... in the dataset designer, go to the parameters tab. On the
right side change from using a report parameter to using an expression. This
will bring you to the expression builder where you can set it to =User!UserID
Now, in the layout tab, report parameters menu, delete the report parameter
which was created for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WillG" <WillG@.discussions.microsoft.com> wrote in message
news:4DBBDA27-67CC-4873-97EF-38B90C1D1E9B@.microsoft.com...
>I want to filter my data.
> So "select * from table where field1=user!userid"
>
> "sorcerdon@.gmail.com" wrote:
>> "I want to filter records right in the Data Source View."
>> What do you mean by this?
>> you want to filter your DATA? or do you want to set different
>> permissions for different users?
>>|||I'm trying to use report models though.
So f I follow the example from msdn here:
http://msdn2.microsoft.com/en-us/library/ms156284.aspx
So after I create "Data Source View" I click on a Product table and click
replace with a named query, this let's me edit the sql or use the query
builder to filter records. In the "Filer" column for a field called user_id I
put =User!UserID, hoping that only products that user_id = User!UserId will
be returned. It does not like that filter, in fact I see nothing about global
variables or variables at all.
**The above is the way I'd like to do my filtering, but if not then:
I tried filtering in the "Report Model":
Looked at the fields, click "Add Group" and All must contain
dragged the user_id field into that "All must contain" box and set it to
user_id must contain GetUserID()
Not sure about the exact area's I clicked because I'm not by my development
pc. But I could not get the Report Model to Filter either.
I like the DataSource View filter because it's a universal way for anyone
who creates reports, in reportbuilder or dev. studio, to only have access to
records that have their userid.
-Will
.
"Bruce L-C [MVP]" wrote:
> Query parameters do not have to map to report parameters. RS automatically
> creates a report parameter for every query parameter but you can delete them
> and use an expression.
> So, do this:
> select * from mytable where field1 = @.User
> RS will create automatically a report parameter called User. Instead of
> using this mapping, you want to change it.
> Click on the ... in the dataset designer, go to the parameters tab. On the
> right side change from using a report parameter to using an expression. This
> will bring you to the expression builder where you can set it to => User!UserID
> Now, in the layout tab, report parameters menu, delete the report parameter
> which was created for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "WillG" <WillG@.discussions.microsoft.com> wrote in message
> news:4DBBDA27-67CC-4873-97EF-38B90C1D1E9B@.microsoft.com...
> >I want to filter my data.
> > So "select * from table where field1=user!userid"
> >
> >
> > "sorcerdon@.gmail.com" wrote:
> >
> >> "I want to filter records right in the Data Source View."
> >>
> >> What do you mean by this?
> >>
> >> you want to filter your DATA? or do you want to set different
> >> permissions for different users?
> >>
> >>
>
>|||Sorry, I don't have any experience with report models. Can't help you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WillG" <WillG@.discussions.microsoft.com> wrote in message
news:E1041A66-B463-4011-9EF6-0C0D11BF2E12@.microsoft.com...
> I'm trying to use report models though.
> So f I follow the example from msdn here:
> http://msdn2.microsoft.com/en-us/library/ms156284.aspx
> So after I create "Data Source View" I click on a Product table and click
> replace with a named query, this let's me edit the sql or use the query
> builder to filter records. In the "Filer" column for a field called
> user_id I
> put =User!UserID, hoping that only products that user_id = User!UserId
> will
> be returned. It does not like that filter, in fact I see nothing about
> global
> variables or variables at all.
> **The above is the way I'd like to do my filtering, but if not then:
> I tried filtering in the "Report Model":
> Looked at the fields, click "Add Group" and All must contain
> dragged the user_id field into that "All must contain" box and set it to
> user_id must contain GetUserID()
> Not sure about the exact area's I clicked because I'm not by my
> development
> pc. But I could not get the Report Model to Filter either.
> I like the DataSource View filter because it's a universal way for anyone
> who creates reports, in reportbuilder or dev. studio, to only have access
> to
> records that have their userid.
> -Will
>
> .
> "Bruce L-C [MVP]" wrote:
>> Query parameters do not have to map to report parameters. RS
>> automatically
>> creates a report parameter for every query parameter but you can delete
>> them
>> and use an expression.
>> So, do this:
>> select * from mytable where field1 = @.User
>> RS will create automatically a report parameter called User. Instead of
>> using this mapping, you want to change it.
>> Click on the ... in the dataset designer, go to the parameters tab. On
>> the
>> right side change from using a report parameter to using an expression.
>> This
>> will bring you to the expression builder where you can set it to =>> User!UserID
>> Now, in the layout tab, report parameters menu, delete the report
>> parameter
>> which was created for you.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "WillG" <WillG@.discussions.microsoft.com> wrote in message
>> news:4DBBDA27-67CC-4873-97EF-38B90C1D1E9B@.microsoft.com...
>> >I want to filter my data.
>> > So "select * from table where field1=user!userid"
>> >
>> >
>> > "sorcerdon@.gmail.com" wrote:
>> >
>> >> "I want to filter records right in the Data Source View."
>> >>
>> >> What do you mean by this?
>> >>
>> >> you want to filter your DATA? or do you want to set different
>> >> permissions for different users?
>> >>
>> >>
>>sql

Named Calculation, Referencing a field from a linked table

Is there a way to reference a field from another table in a named calculation.

Ex: Table movies has a link to table theatre, in the theatre there is a display code, the display-name of the movie should be comp.comp_displaycode + ' - ' + mov.mov_name.

I know i could do this in a view and than use the view instead of the movies table, but if it is possible in the Datasourceviews it would be easier.

Try replacing table in the DSV with named query joining 2 tables.

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

|||This is very similar to defining views, i just hoped that there was a syntax available to do it.

name value pair design

I want to store many different types of objects in a single table. I
was thinking of using the name value pair approach to achieve this.
Does anybody have any experience with a such a design?

The table might look like this
CREATE TABLE NV (pk int, type int, [name] varchar(100), value
varchar(100))

--Insert a manager - type = 1
INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'FirstName', 'John')

INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'LastName', 'Smith')

INSERT INTO NV (pk, type, [name], val)
VALUES (11, 1, 'Position', 'CEO')

--Insert an employee - type = 2
INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'FirstName', 'Joe')

INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'LastName', 'Blog')

INSERT INTO NV (pk, type, [name], val)
VALUES (21, 2, 'Position', 'Developer')

--Insert an inventory item - type = 3
INSERT INTO NV (type, [name], val)
VALUES (13, 3, 'Name', 'Chair')

INSERT INTO NV (type, [name], val)
VALUES (13, 3, 'Color', 'White')

INSERT INTO NV (type, [name], val)
VALUES (3, 3, 'Price', '$150')rgarvey@.polymorphia.com (Ruaidhri) wrote in message news:<7681c3eb.0402111626.6173889b@.posting.google.com>...
> I want to store many different types of objects in a single table. I
> was thinking of using the name value pair approach to achieve this.
> Does anybody have any experience with a such a design?
> The table might look like this
> CREATE TABLE NV (pk int, type int, [name] varchar(100), value
> varchar(100))
> --Insert a manager - type = 1
> INSERT INTO NV (pk, type, [name], val)
> VALUES (11, 1, 'FirstName', 'John')
> INSERT INTO NV (pk, type, [name], val)
> VALUES (11, 1, 'LastName', 'Smith')
> INSERT INTO NV (pk, type, [name], val)
> VALUES (11, 1, 'Position', 'CEO')
> --Insert an employee - type = 2
> INSERT INTO NV (pk, type, [name], val)
> VALUES (21, 2, 'FirstName', 'Joe')
> INSERT INTO NV (pk, type, [name], val)
> VALUES (21, 2, 'LastName', 'Blog')
> INSERT INTO NV (pk, type, [name], val)
> VALUES (21, 2, 'Position', 'Developer')
> --Insert an inventory item - type = 3
> INSERT INTO NV (type, [name], val)
> VALUES (13, 3, 'Name', 'Chair')
> INSERT INTO NV (type, [name], val)
> VALUES (13, 3, 'Color', 'White')
> INSERT INTO NV (type, [name], val)
> VALUES (3, 3, 'Price', '$150')

Generally speaking, this is not good design in a relational database -
each table should represent only one entity. Why not have two tables,
Employees and Inventory? (Managers are employees too.)

In any case, if you can explain what you're trying to do, and why you
are considering this design, then someone may be able to suggest a
better solution.

Simon|||The design is for a Biotech company. We were thinking about using
this
design because it is flexible. For example, we can use it to store
materials that we know about now. It can also be used in the future to
store new materials that we don't yet know about yet that might have
different attributes.

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0402120128.7fdad08d@.posting.google.com>...
> rgarvey@.polymorphia.com (Ruaidhri) wrote in message news:<7681c3eb.0402111626.6173889b@.posting.google.com>...
> > I want to store many different types of objects in a single table. I
> > was thinking of using the name value pair approach to achieve this.
> > Does anybody have any experience with a such a design?
> > The table might look like this
> > CREATE TABLE NV (pk int, type int, [name] varchar(100), value
> > varchar(100))
> > --Insert a manager - type = 1
> > INSERT INTO NV (pk, type, [name], val)
> > VALUES (11, 1, 'FirstName', 'John')
> > INSERT INTO NV (pk, type, [name], val)
> > VALUES (11, 1, 'LastName', 'Smith')
> > INSERT INTO NV (pk, type, [name], val)
> > VALUES (11, 1, 'Position', 'CEO')
> > --Insert an employee - type = 2
> > INSERT INTO NV (pk, type, [name], val)
> > VALUES (21, 2, 'FirstName', 'Joe')
> > INSERT INTO NV (pk, type, [name], val)
> > VALUES (21, 2, 'LastName', 'Blog')
> > INSERT INTO NV (pk, type, [name], val)
> > VALUES (21, 2, 'Position', 'Developer')
> > --Insert an inventory item - type = 3
> > INSERT INTO NV (type, [name], val)
> > VALUES (13, 3, 'Name', 'Chair')
> > INSERT INTO NV (type, [name], val)
> > VALUES (13, 3, 'Color', 'White')
> > INSERT INTO NV (type, [name], val)
> > VALUES (3, 3, 'Price', '$150')
> Generally speaking, this is not good design in a relational database -
> each table should represent only one entity. Why not have two tables,
> Employees and Inventory? (Managers are employees too.)
> In any case, if you can explain what you're trying to do, and why you
> are considering this design, then someone may be able to suggest a
> better solution.
> Simon|||rgarvey@.polymorphia.com (Ruaidhri) wrote in message news:<7681c3eb.0402121628.70a8488f@.posting.google.com>...
> The design is for a Biotech company. We were thinking about using
> this
> design because it is flexible. For example, we can use it to store
> materials that we know about now. It can also be used in the future to
> store new materials that we don't yet know about yet that might have
> different attributes.

<snip
You might want to have a look at this article from SQL Server Magazine
(June 2003) on super/subtypes:

http://www.sqlmag.com/Articles/Inde...ArticleID=38656

This is one possible solution to the issue of modelling different
items which share some common attributes (Quantity, UnitPrice, etc.),
but have other attributes which only apply to one type of item
(Colour, NumberOfLegs).

Your approach would be extremely difficult to query, index, and
maintain. In fact, taken to an extreme, you would end up with only one
table in the database. Even if you finally decide to have some degree
of denormalization to handle very diverse inventory items, there's no
good reason to include employees in the same table.

Simonsql

name of the command to get table structure

how do i get the table structure within the sql server
2000 (the corresponding command within oracle is describe)sp_help tablename
may help
--
HTH
Ryan Waight, MCDBA, MCSE
"uwe" <uwe.woedl@.linde.de> wrote in message
news:2a70401c39259$8d5ca6f0$a601280a@.phx.gbl...
> how do i get the table structure within the sql server
> 2000 (the corresponding command within oracle is describe)|||Hello Ryan,
sorry but this is not what I want. I do get only the
number of tables within the database. I want the structure
of a table including the attributes, their formats, the
primary key and foreign keys.
>--Original Message--
>sp_help tablename
>may help
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"uwe" <uwe.woedl@.linde.de> wrote in message
>news:2a70401c39259$8d5ca6f0$a601280a@.phx.gbl...
>> how do i get the table structure within the sql server
>> 2000 (the corresponding command within oracle is
describe)
>
>.
>|||If you specify a table name as the first argument to sp_help, that is
exactly what you get. Have a look at the syntax and comments in BOL.
"uw" <uwe.woedl@.linde.de> wrote in message
news:00aa01c39262$bf1328a0$a101280a@.phx.gbl...
> Hello Ryan,
> sorry but this is not what I want. I do get only the
> number of tables within the database. I want the structure
> of a table including the attributes, their formats, the
> primary key and foreign keys.
>
> >--Original Message--
> >sp_help tablename
> >
> >may help
> >
> >--
> >HTH
> >Ryan Waight, MCDBA, MCSE
> >
> >"uwe" <uwe.woedl@.linde.de> wrote in message
> >news:2a70401c39259$8d5ca6f0$a601280a@.phx.gbl...
> >> how do i get the table structure within the sql server
> >> 2000 (the corresponding command within oracle is
> describe)
> >
> >
> >.
> >

name of table used for logging in sql server

Hello,

When I enable logging where is the table name specified for SQL Server?

Can I specify the table name?

Thanks,

Michael

No. The SQL Server log provider, which writes log entries to the sysdtslog90 table in a SQL Server 2005 database.

Name Matching

Hi All,
I have a table with two columns that I want to match but am unsure of
how to.
The Columns are called "User_Name" and Managed_By" the user_name is
entered as "Fred Flintstone" while the Managed_By is entered are
"Flintstone, Fred".
To the human eye you can see that they are the same person but how can
i do that match in SQL?
I am using a SQL 2000 server
Many Thanks
MarkSELECT U1.user_name, U1.managed_by, U2.user_name
FROM Users AS U1
LEFT JOIN Users AS U2
ON U2.user_name =
SUBSTRING(U1.managed_by,CHARINDEX(',',U1.managed_by)+2,40)+' '+
LEFT(U1.managed_by,CHARINDEX(',',U1.managed_by)-1)
Hopefully this will help you fix the table by adding keys and
referential integrity. It's obviously not a sensible design as it
stands.
David Portas
SQL Server MVP
--|||David,
Thanks for this,
I changed the table users to match my table name, however I got an
error massage when trying to excute the statments.
Any Ideas?
Cheers
Mark
<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>
(1 row(s) affected)
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>|||I'd guess that the Managed_by column isn't consistent and that some of
the rows don't have commas between the two names. That's hardly
surprising if there are no constraints used. Try the following query to
take a look at the inconsistent data. Perhaps you'll have to clean it
up manually.
SELECT managed_by
FROM Users
WHERE managed_by NOT LIKE '_%,%_'
David Portas
SQL Server MVP
--|||David
I ran that query and there are 2210 rows which match, when i distinct
it it comes down to 14 names that match.
I then decided to run the first query agains only one name that meets
the critira "surname, firstname" the statment returned 24500 matches
but there is only 196 entries in the table
Any ideas?
Thanks
Mark|||If you need more help please post some code that will reproduce the
problem so that I and others don't have to keep guessing: CREATE TABLE
statement for the table and some INSERT statements of a few rows of
sample data.
It now seems you have duplicate rows in your table. As the data quality
is so poor it may be easier for just 196 rows to create a new table
(with a key), insert the distinct names and then check and re-key the
manager references by hand.
David Portas
SQL Server MVP
--

name a default constraint upon creation

In QA I can alter a table with
ALTER TABLE dbo.Incidents ADD NewIncident int NOT NULL default 0
However later on I wish to remove the default on this column. However
upon creation the defualt is given a name like
DF__Incidents__NewIn__05D9AC15
Can I a) Give a name to the default upon creation or b) get the name of
the default for the column so i can then remove it like
DROP CONSTRAINT DF_Incidents_NewIncident
ThanksNever let SQL Server name your constraints, name them yourself:
create table t(c1 int constraint d_t default 1)
go
exec sp_helpconstraint t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rippo" <info@.rippo.co.uk> wrote in message
news:1126094026.545449.59440@.g14g2000cwa.googlegroups.com...
> In QA I can alter a table with
> ALTER TABLE dbo.Incidents ADD NewIncident int NOT NULL default 0
>
> However later on I wish to remove the default on this column. However
> upon creation the defualt is given a name like
> DF__Incidents__NewIn__05D9AC15
> Can I a) Give a name to the default upon creation or b) get the name of
> the default for the column so i can then remove it like
>
> DROP CONSTRAINT DF_Incidents_NewIncident
> Thanks
>|||I prefer to name them as Tibor recommended. See if this helps:
http://www.microsoft.com/communitie...72e7&sloc=en-us
AMB
"Rippo" wrote:

> In QA I can alter a table with
> ALTER TABLE dbo.Incidents ADD NewIncident int NOT NULL default 0
>
> However later on I wish to remove the default on this column. However
> upon creation the defualt is given a name like
> DF__Incidents__NewIn__05D9AC15
> Can I a) Give a name to the default upon creation or b) get the name of
> the default for the column so i can then remove it like
>
> DROP CONSTRAINT DF_Incidents_NewIncident
> Thanks
>

N'' character in INSERT

Hi,

for example:

INSERT INTO TABLE (FIELD1, FIELD2) VALUES (N' value1', N' value2') WHERE ......

What does N' mean?

Thank you.

N identifies the given string is the UNICODE string. Which will be used when you store the values on NVARCHAR.

If you want to store other than English(or Latin based) in your table (globalization) then you have to use NVARCHAR as your string datatype the N is prefixed to identify the given string is Unicode char.

If you failed to give N prefix on Unicode string the SQL Server try to covert the value to ascii based string and the output printed with question marks

Select '日本語' Output

/*

-

?

*/

If you prefix N then string parsed perfectly,

Select N'日本語' Output

/*

Output

日本語

*/

|||Ok. Thank you very much.

N'' character in INSERT

Hi,

for example:

INSERT INTO TABLE (FIELD1, FIELD2) VALUES (N' value1', N' value2') WHERE ......

What does N' mean?

Thank you.

N identifies the given string is the UNICODE string. Which will be used when you store the values on NVARCHAR.

If you want to store other than English(or Latin based) in your table (globalization) then you have to use NVARCHAR as your string datatype the N is prefixed to identify the given string is Unicode char.

If you failed to give N prefix on Unicode string the SQL Server try to covert the value to ascii based string and the output printed with question marks

Select '日本語' Output

/*

-

?

*/

If you prefix N then string parsed perfectly,

Select N'日本語' Output

/*

Output

日本語

*/

|||Ok. Thank you very much.

N'' character in INSERT

Hi,

for example:

INSERT INTO TABLE (FIELD1, FIELD2) VALUES (N' value1', N' value2') WHERE ......

What does N' mean?

Thank you.

N identifies the given string is the UNICODE string. Which will be used when you store the values on NVARCHAR.

If you want to store other than English(or Latin based) in your table (globalization) then you have to use NVARCHAR as your string datatype the N is prefixed to identify the given string is Unicode char.

If you failed to give N prefix on Unicode string the SQL Server try to covert the value to ascii based string and the output printed with question marks

Select '日本語' Output

/*

-

?

*/

If you prefix N then string parsed perfectly,

Select N'日本語' Output

/*

Output

日本語

*/

|||Ok. Thank you very much.

mystery truncating

I have table in SQL Server 2000 that stores comments in one field of type
varchar with a length of 250. A test value entered is 249 characters long.
When I run the following:
select len(comment) from tblcomments where emplid = '241s'
the len returned is 249, as expected. However, once I try concatenating
date and user name info, funny stuff starts happening.
For example, if I run this code:
select len(Cast([Date] as varchar(128)) + ' - ' + NTUSERNAME + ' - ' +
Comment)
from tblcomments where emplid = '241S'
len returned is 284, which is correct. But when the code is run without
using the len() function the result returned has been truncated by 28
characters. I end up with a character output of only 256 characters. I get
the date, the ntusername and the first 221 characters of the comment sting.
For some reason, after concatenating the additional info I'm losing 28
characters and the output is limited to 256 characters. But again, when
using the len function I'm getting 284. What is happening? Any suggestions
?> characters. I end up with a character output of only 256 characters
Where, in QA?
Go to Tools - Options... - Results and increase "Maximum characters per
column". Max value allowed is 8K or 8192.
AMB
"kiloez" wrote:

> I have table in SQL Server 2000 that stores comments in one field of type
> varchar with a length of 250. A test value entered is 249 characters long
.
> When I run the following:
> select len(comment) from tblcomments where emplid = '241s'
> the len returned is 249, as expected. However, once I try concatenating
> date and user name info, funny stuff starts happening.
> For example, if I run this code:
> select len(Cast([Date] as varchar(128)) + ' - ' + NTUSERNAME + ' - ' +
> Comment)
> from tblcomments where emplid = '241S'
> len returned is 284, which is correct. But when the code is run without
> using the len() function the result returned has been truncated by 28
> characters. I end up with a character output of only 256 characters. I g
et
> the date, the ntusername and the first 221 characters of the comment sting
.
> For some reason, after concatenating the additional info I'm losing 28
> characters and the output is limited to 256 characters. But again, when
> using the len function I'm getting 284. What is happening? Any suggestio
ns?
>|||Alejandro, thank you. How can this be done when running the same code from
a
stored procedure? I don't see a similar option in Enterprise Mgr. The
problem was first noticed when running this code from a sp.
"Alejandro Mesa" wrote:
> Where, in QA?
> Go to Tools - Options... - Results and increase "Maximum characters per
> column". Max value allowed is 8K or 8192.
>
> AMB
> "kiloez" wrote:
>|||Where did you run the sp?
This is an internal setting of the client app (QA).
AMB
"kiloez" wrote:
> Alejandro, thank you. How can this be done when running the same code fro
m a
> stored procedure? I don't see a similar option in Enterprise Mgr. The
> problem was first noticed when running this code from a sp.
> "Alejandro Mesa" wrote:
>|||Alejandro, thanks again for replying. I found out what the problem was. I'
m
actually running this sp from a dataenvironment in VB6. After checking the
properties for the sp in the DE I saw that the length for the output
parameter was only 250. After changing it to 8000 output was as it should
be. The character length had already been changed in the sp itself.
"Alejandro Mesa" wrote:
> Where did you run the sp?
> This is an internal setting of the client app (QA).
>
> AMB
> "kiloez" wrote:
>

Mystery Table Delete!

I'm hoping someone out there can help me solve a mystery!
29 tables in our live database were deleted the other day
and we've no real idea how this could have happened.
We have found an entry in Application Event Viewer that
happened at about the same time as the tables vanished,
but we don't really understand what it means:
EventID: 17055
Categorey: (6)
Description: 3041: BACKUP failed to complete the command
dump tran [Claims] with no_log
I understand that this is saying that the command failed,
but where did the command come from? The user on the
event was creating new tables and replacing stored
procedures in the database at the time, however, [Claims]
is the name of the whole database!
We have recovered from the disaster now but really do need
to understand what has happened here, how it happened, and
how we can ensure it never happens again in the future.
Thanks in advance for any help.
Regards
PaulaCheck your web server logs, if there is web application connected. Check
your security logs...
"Pegasus" <anonymous@.discussions.microsoft.com> wrote in message
news:212c301c45a9e$73c9e3c0$a301280a@.phx
.gbl...
> I'm hoping someone out there can help me solve a mystery!
> 29 tables in our live database were deleted the other day
> and we've no real idea how this could have happened.
> We have found an entry in Application Event Viewer that
> happened at about the same time as the tables vanished,
> but we don't really understand what it means:
> EventID: 17055
> Categorey: (6)
> Description: 3041: BACKUP failed to complete the command
> dump tran [Claims] with no_log
> I understand that this is saying that the command failed,
> but where did the command come from? The user on the
> event was creating new tables and replacing stored
> procedures in the database at the time, however, [Claims]
> is the name of the whole database!
> We have recovered from the disaster now but really do need
> to understand what has happened here, how it happened, and
> how we can ensure it never happens again in the future.
> Thanks in advance for any help.
> Regards
> Paula|||There is no web application connected (to my knowledge)
and nothing is showing in the security logs.
Using SQL Server 2000.
Has been suggested that using Enterprise Manager and the
DTS Import/Export Wizard to move objects could have caused
the tables to be dropped? Any value in this theory?

>--Original Message--
>Check your web server logs, if there is web application
connected. Check
>your security logs...
>"Pegasus" <anonymous@.discussions.microsoft.com> wrote in
message
> news:212c301c45a9e$73c9e3c0$a301280a@.phx
.gbl...
mystery![vbcol=seagreen]
day[vbcol=seagreen]
failed,[vbcol=seagreen]
[Claims][vbcol=seagreen]
need[vbcol=seagreen]
and[vbcol=seagreen]
>
>.
>|||Probably the best way to see might be to get the tool from Lumigent (
www.lumigent.com)...
Give them a call and see if the tool will help in your situation.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Pegasus" <anonymous@.discussions.microsoft.com> wrote in message
news:212c301c45a9e$73c9e3c0$a301280a@.phx
.gbl...
> I'm hoping someone out there can help me solve a mystery!
> 29 tables in our live database were deleted the other day
> and we've no real idea how this could have happened.
> We have found an entry in Application Event Viewer that
> happened at about the same time as the tables vanished,
> but we don't really understand what it means:
> EventID: 17055
> Categorey: (6)
> Description: 3041: BACKUP failed to complete the command
> dump tran [Claims] with no_log
> I understand that this is saying that the command failed,
> but where did the command come from? The user on the
> event was creating new tables and replacing stored
> procedures in the database at the time, however, [Claims]
> is the name of the whole database!
> We have recovered from the disaster now but really do need
> to understand what has happened here, how it happened, and
> how we can ensure it never happens again in the future.
> Thanks in advance for any help.
> Regards
> Paula|||Try a log tool like LogExplorer from www.lumigent.com or ApexSQL Log from
www.apexsql.com to trawl trough your transaction logs and see what happened.
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:216db01c45aac$4cc30320$a401280a@.phx
.gbl...[vbcol=seagreen]
> There is no web application connected (to my knowledge)
> and nothing is showing in the security logs.
> Using SQL Server 2000.
> Has been suggested that using Enterprise Manager and the
> DTS Import/Export Wizard to move objects could have caused
> the tables to be dropped? Any value in this theory?
>
> connected. Check
> message
> mystery!
> day
> failed,
> [Claims]
> need
> and|||Hi,
There is only Copy objects option in DTS and this will just copy the objects
to destination.
What recovery model you are using for this database, If it is FULL or
Bulk_logged and if you have trasnaction logs you can use the Log explorer
tool to analyze the situation which caused the object drop.
www.lumigent.com
If you do not have the transaction log backup, it is very difficult to find
the cause for the object drop.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:216db01c45aac$4cc30320$a401280a@.phx
.gbl...[vbcol=seagreen]
> There is no web application connected (to my knowledge)
> and nothing is showing in the security logs.
> Using SQL Server 2000.
> Has been suggested that using Enterprise Manager and the
> DTS Import/Export Wizard to move objects could have caused
> the tables to be dropped? Any value in this theory?
>
> connected. Check
> message
> mystery!
> day
> failed,
> [Claims]
> need
> and|||Thanks for your input.
No, we don't have the transaction log or a backup of it
(don't ask!!!) Is there another way to see what's
happened? There were 29 tables dropped in all so this
had very serious consequences and now we're trying to
ensure this doesn't happen again, but, without knowing
what DID happen we can't safeguard against it happening
again.

>--Original Message--
>Hi,
>There is only Copy objects option in DTS and this will
just copy the objects
>to destination.
>What recovery model you are using for this database, If
it is FULL or
>Bulk_logged and if you have trasnaction logs you can use
the Log explorer
>tool to analyze the situation which caused the object
drop.
>www.lumigent.com
>If you do not have the transaction log backup, it is very
difficult to find
>the cause for the object drop.
>--
>Thanks
>Hari
>MCDBA
><anonymous@.discussions.microsoft.com> wrote in message
> news:216db01c45aac$4cc30320$a401280a@.phx
.gbl...
caused[vbcol=seagreen]
in[vbcol=seagreen]
that[vbcol=seagreen]
vanished,[vbcol=seagreen]
command[vbcol=seagreen]
the[vbcol=seagreen]
happened,[vbcol=seagreen]
future.[vbcol=seagreen]
>
>.
>

Mystery Table Delete!

I'm hoping someone out there can help me solve a mystery!
29 tables in our live database were deleted the other day
and we've no real idea how this could have happened.
We have found an entry in Application Event Viewer that
happened at about the same time as the tables vanished,
but we don't really understand what it means:
EventID: 17055
Categorey: (6)
Description: 3041: BACKUP failed to complete the command
dump tran [Claims] with no_log
I understand that this is saying that the command failed,
but where did the command come from? The user on the
event was creating new tables and replacing stored
procedures in the database at the time, however, [Claims]
is the name of the whole database!
We have recovered from the disaster now but really do need
to understand what has happened here, how it happened, and
how we can ensure it never happens again in the future.
Thanks in advance for any help.
Regards
PaulaCheck your web server logs, if there is web application connected. Check
your security logs...
"Pegasus" <anonymous@.discussions.microsoft.com> wrote in message
news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> I'm hoping someone out there can help me solve a mystery!
> 29 tables in our live database were deleted the other day
> and we've no real idea how this could have happened.
> We have found an entry in Application Event Viewer that
> happened at about the same time as the tables vanished,
> but we don't really understand what it means:
> EventID: 17055
> Categorey: (6)
> Description: 3041: BACKUP failed to complete the command
> dump tran [Claims] with no_log
> I understand that this is saying that the command failed,
> but where did the command come from? The user on the
> event was creating new tables and replacing stored
> procedures in the database at the time, however, [Claims]
> is the name of the whole database!
> We have recovered from the disaster now but really do need
> to understand what has happened here, how it happened, and
> how we can ensure it never happens again in the future.
> Thanks in advance for any help.
> Regards
> Paula|||There is no web application connected (to my knowledge)
and nothing is showing in the security logs.
Using SQL Server 2000.
Has been suggested that using Enterprise Manager and the
DTS Import/Export Wizard to move objects could have caused
the tables to be dropped? Any value in this theory?
>--Original Message--
>Check your web server logs, if there is web application
connected. Check
>your security logs...
>"Pegasus" <anonymous@.discussions.microsoft.com> wrote in
message
>news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
>> I'm hoping someone out there can help me solve a
mystery!
>> 29 tables in our live database were deleted the other
day
>> and we've no real idea how this could have happened.
>> We have found an entry in Application Event Viewer that
>> happened at about the same time as the tables vanished,
>> but we don't really understand what it means:
>> EventID: 17055
>> Categorey: (6)
>> Description: 3041: BACKUP failed to complete the command
>> dump tran [Claims] with no_log
>> I understand that this is saying that the command
failed,
>> but where did the command come from? The user on the
>> event was creating new tables and replacing stored
>> procedures in the database at the time, however,
[Claims]
>> is the name of the whole database!
>> We have recovered from the disaster now but really do
need
>> to understand what has happened here, how it happened,
and
>> how we can ensure it never happens again in the future.
>> Thanks in advance for any help.
>> Regards
>> Paula
>
>.
>|||Probably the best way to see might be to get the tool from Lumigent (
www.lumigent.com)...
Give them a call and see if the tool will help in your situation.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Pegasus" <anonymous@.discussions.microsoft.com> wrote in message
news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> I'm hoping someone out there can help me solve a mystery!
> 29 tables in our live database were deleted the other day
> and we've no real idea how this could have happened.
> We have found an entry in Application Event Viewer that
> happened at about the same time as the tables vanished,
> but we don't really understand what it means:
> EventID: 17055
> Categorey: (6)
> Description: 3041: BACKUP failed to complete the command
> dump tran [Claims] with no_log
> I understand that this is saying that the command failed,
> but where did the command come from? The user on the
> event was creating new tables and replacing stored
> procedures in the database at the time, however, [Claims]
> is the name of the whole database!
> We have recovered from the disaster now but really do need
> to understand what has happened here, how it happened, and
> how we can ensure it never happens again in the future.
> Thanks in advance for any help.
> Regards
> Paula|||Try a log tool like LogExplorer from www.lumigent.com or ApexSQL Log from
www.apexsql.com to trawl trough your transaction logs and see what happened.
--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:216db01c45aac$4cc30320$a401280a@.phx.gbl...
> There is no web application connected (to my knowledge)
> and nothing is showing in the security logs.
> Using SQL Server 2000.
> Has been suggested that using Enterprise Manager and the
> DTS Import/Export Wizard to move objects could have caused
> the tables to be dropped? Any value in this theory?
>
> >--Original Message--
> >Check your web server logs, if there is web application
> connected. Check
> >your security logs...
> >
> >"Pegasus" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> >> I'm hoping someone out there can help me solve a
> mystery!
> >>
> >> 29 tables in our live database were deleted the other
> day
> >> and we've no real idea how this could have happened.
> >>
> >> We have found an entry in Application Event Viewer that
> >> happened at about the same time as the tables vanished,
> >> but we don't really understand what it means:
> >> EventID: 17055
> >> Categorey: (6)
> >> Description: 3041: BACKUP failed to complete the command
> >> dump tran [Claims] with no_log
> >>
> >> I understand that this is saying that the command
> failed,
> >> but where did the command come from? The user on the
> >> event was creating new tables and replacing stored
> >> procedures in the database at the time, however,
> [Claims]
> >> is the name of the whole database!
> >>
> >> We have recovered from the disaster now but really do
> need
> >> to understand what has happened here, how it happened,
> and
> >> how we can ensure it never happens again in the future.
> >>
> >> Thanks in advance for any help.
> >>
> >> Regards
> >>
> >> Paula
> >
> >
> >.
> >|||Hi,
There is only Copy objects option in DTS and this will just copy the objects
to destination.
What recovery model you are using for this database, If it is FULL or
Bulk_logged and if you have trasnaction logs you can use the Log explorer
tool to analyze the situation which caused the object drop.
www.lumigent.com
If you do not have the transaction log backup, it is very difficult to find
the cause for the object drop.
--
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:216db01c45aac$4cc30320$a401280a@.phx.gbl...
> There is no web application connected (to my knowledge)
> and nothing is showing in the security logs.
> Using SQL Server 2000.
> Has been suggested that using Enterprise Manager and the
> DTS Import/Export Wizard to move objects could have caused
> the tables to be dropped? Any value in this theory?
>
> >--Original Message--
> >Check your web server logs, if there is web application
> connected. Check
> >your security logs...
> >
> >"Pegasus" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> >> I'm hoping someone out there can help me solve a
> mystery!
> >>
> >> 29 tables in our live database were deleted the other
> day
> >> and we've no real idea how this could have happened.
> >>
> >> We have found an entry in Application Event Viewer that
> >> happened at about the same time as the tables vanished,
> >> but we don't really understand what it means:
> >> EventID: 17055
> >> Categorey: (6)
> >> Description: 3041: BACKUP failed to complete the command
> >> dump tran [Claims] with no_log
> >>
> >> I understand that this is saying that the command
> failed,
> >> but where did the command come from? The user on the
> >> event was creating new tables and replacing stored
> >> procedures in the database at the time, however,
> [Claims]
> >> is the name of the whole database!
> >>
> >> We have recovered from the disaster now but really do
> need
> >> to understand what has happened here, how it happened,
> and
> >> how we can ensure it never happens again in the future.
> >>
> >> Thanks in advance for any help.
> >>
> >> Regards
> >>
> >> Paula
> >
> >
> >.
> >|||Thanks for your input. I have already got information
about LogExplorer from www.lumigent.com and we're looking
into purchasing this product for use in the future.
Unfortunately the log file no longer exists (don't ask!!!)
so this software would be of no use on this occasion. Is
there any other way forward on this?
Could the DTS Import/Export Wizard have caused this much
destruction by copying over 2 new tables and 3 existing
stored procedures from a development copy of the
database? The user insists that the objects were not
transferred to the live database using this Wizard. Even
if "dependant objects" was selected, could there be a
scenario where SQL would have dropped all tables
referenced in the SP's and all tables related to those
tables? If this is possible, can we disable the use of
DTS Import/Export wizard in Enterprise Manager for ALL
users?
>--Original Message--
>I'm hoping someone out there can help me solve a mystery!
>29 tables in our live database were deleted the other day
>and we've no real idea how this could have happened.
>We have found an entry in Application Event Viewer that
>happened at about the same time as the tables vanished,
>but we don't really understand what it means:
>EventID: 17055
>Categorey: (6)
>Description: 3041: BACKUP failed to complete the command
>dump tran [Claims] with no_log
>I understand that this is saying that the command failed,
>but where did the command come from? The user on the
>event was creating new tables and replacing stored
>procedures in the database at the time, however, [Claims]
>is the name of the whole database!
>We have recovered from the disaster now but really do
need
>to understand what has happened here, how it happened,
and
>how we can ensure it never happens again in the future.
>Thanks in advance for any help.
>Regards
>Paula
>.
>|||Thanks for your input.
No, we don't have the transaction log or a backup of it
(don't ask!!!) Is there another way to see what's
happened? There were 29 tables dropped in all so this
had very serious consequences and now we're trying to
ensure this doesn't happen again, but, without knowing
what DID happen we can't safeguard against it happening
again.
>--Original Message--
>Hi,
>There is only Copy objects option in DTS and this will
just copy the objects
>to destination.
>What recovery model you are using for this database, If
it is FULL or
>Bulk_logged and if you have trasnaction logs you can use
the Log explorer
>tool to analyze the situation which caused the object
drop.
>www.lumigent.com
>If you do not have the transaction log backup, it is very
difficult to find
>the cause for the object drop.
>--
>Thanks
>Hari
>MCDBA
><anonymous@.discussions.microsoft.com> wrote in message
>news:216db01c45aac$4cc30320$a401280a@.phx.gbl...
>> There is no web application connected (to my knowledge)
>> and nothing is showing in the security logs.
>> Using SQL Server 2000.
>> Has been suggested that using Enterprise Manager and the
>> DTS Import/Export Wizard to move objects could have
caused
>> the tables to be dropped? Any value in this theory?
>>
>> >--Original Message--
>> >Check your web server logs, if there is web application
>> connected. Check
>> >your security logs...
>> >
>> >"Pegasus" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
>> >> I'm hoping someone out there can help me solve a
>> mystery!
>> >>
>> >> 29 tables in our live database were deleted the other
>> day
>> >> and we've no real idea how this could have happened.
>> >>
>> >> We have found an entry in Application Event Viewer
that
>> >> happened at about the same time as the tables
vanished,
>> >> but we don't really understand what it means:
>> >> EventID: 17055
>> >> Categorey: (6)
>> >> Description: 3041: BACKUP failed to complete the
command
>> >> dump tran [Claims] with no_log
>> >>
>> >> I understand that this is saying that the command
>> failed,
>> >> but where did the command come from? The user on
the
>> >> event was creating new tables and replacing stored
>> >> procedures in the database at the time, however,
>> [Claims]
>> >> is the name of the whole database!
>> >>
>> >> We have recovered from the disaster now but really do
>> need
>> >> to understand what has happened here, how it
happened,
>> and
>> >> how we can ensure it never happens again in the
future.
>> >>
>> >> Thanks in advance for any help.
>> >>
>> >> Regards
>> >>
>> >> Paula
>> >
>> >
>> >.
>> >
>
>.
>

Mystery Table Delete!

I'm hoping someone out there can help me solve a mystery!
29 tables in our live database were deleted the other day
and we've no real idea how this could have happened.
We have found an entry in Application Event Viewer that
happened at about the same time as the tables vanished,
but we don't really understand what it means:
EventID: 17055
Categorey: (6)
Description: 3041: BACKUP failed to complete the command
dump tran [Claims] with no_log
I understand that this is saying that the command failed,
but where did the command come from? The user on the
event was creating new tables and replacing stored
procedures in the database at the time, however, [Claims]
is the name of the whole database!
We have recovered from the disaster now but really do need
to understand what has happened here, how it happened, and
how we can ensure it never happens again in the future.
Thanks in advance for any help.
Regards
Paula
Check your web server logs, if there is web application connected. Check
your security logs...
"Pegasus" <anonymous@.discussions.microsoft.com> wrote in message
news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> I'm hoping someone out there can help me solve a mystery!
> 29 tables in our live database were deleted the other day
> and we've no real idea how this could have happened.
> We have found an entry in Application Event Viewer that
> happened at about the same time as the tables vanished,
> but we don't really understand what it means:
> EventID: 17055
> Categorey: (6)
> Description: 3041: BACKUP failed to complete the command
> dump tran [Claims] with no_log
> I understand that this is saying that the command failed,
> but where did the command come from? The user on the
> event was creating new tables and replacing stored
> procedures in the database at the time, however, [Claims]
> is the name of the whole database!
> We have recovered from the disaster now but really do need
> to understand what has happened here, how it happened, and
> how we can ensure it never happens again in the future.
> Thanks in advance for any help.
> Regards
> Paula
|||There is no web application connected (to my knowledge)
and nothing is showing in the security logs.
Using SQL Server 2000.
Has been suggested that using Enterprise Manager and the
DTS Import/Export Wizard to move objects could have caused
the tables to be dropped? Any value in this theory?

>--Original Message--
>Check your web server logs, if there is web application
connected. Check
>your security logs...
>"Pegasus" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
mystery![vbcol=seagreen]
day[vbcol=seagreen]
failed,[vbcol=seagreen]
[Claims][vbcol=seagreen]
need[vbcol=seagreen]
and
>
>.
>
|||Probably the best way to see might be to get the tool from Lumigent (
www.lumigent.com)...
Give them a call and see if the tool will help in your situation.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Pegasus" <anonymous@.discussions.microsoft.com> wrote in message
news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> I'm hoping someone out there can help me solve a mystery!
> 29 tables in our live database were deleted the other day
> and we've no real idea how this could have happened.
> We have found an entry in Application Event Viewer that
> happened at about the same time as the tables vanished,
> but we don't really understand what it means:
> EventID: 17055
> Categorey: (6)
> Description: 3041: BACKUP failed to complete the command
> dump tran [Claims] with no_log
> I understand that this is saying that the command failed,
> but where did the command come from? The user on the
> event was creating new tables and replacing stored
> procedures in the database at the time, however, [Claims]
> is the name of the whole database!
> We have recovered from the disaster now but really do need
> to understand what has happened here, how it happened, and
> how we can ensure it never happens again in the future.
> Thanks in advance for any help.
> Regards
> Paula
|||Try a log tool like LogExplorer from www.lumigent.com or ApexSQL Log from
www.apexsql.com to trawl trough your transaction logs and see what happened.
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:216db01c45aac$4cc30320$a401280a@.phx.gbl...[vbcol=seagreen]
> There is no web application connected (to my knowledge)
> and nothing is showing in the security logs.
> Using SQL Server 2000.
> Has been suggested that using Enterprise Manager and the
> DTS Import/Export Wizard to move objects could have caused
> the tables to be dropped? Any value in this theory?
>
> connected. Check
> message
> mystery!
> day
> failed,
> [Claims]
> need
> and
|||Hi,
There is only Copy objects option in DTS and this will just copy the objects
to destination.
What recovery model you are using for this database, If it is FULL or
Bulk_logged and if you have trasnaction logs you can use the Log explorer
tool to analyze the situation which caused the object drop.
www.lumigent.com
If you do not have the transaction log backup, it is very difficult to find
the cause for the object drop.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:216db01c45aac$4cc30320$a401280a@.phx.gbl...[vbcol=seagreen]
> There is no web application connected (to my knowledge)
> and nothing is showing in the security logs.
> Using SQL Server 2000.
> Has been suggested that using Enterprise Manager and the
> DTS Import/Export Wizard to move objects could have caused
> the tables to be dropped? Any value in this theory?
>
> connected. Check
> message
> mystery!
> day
> failed,
> [Claims]
> need
> and
|||Thanks for your input.
No, we don't have the transaction log or a backup of it
(don't ask!!!) Is there another way to see what's
happened? There were 29 tables dropped in all so this
had very serious consequences and now we're trying to
ensure this doesn't happen again, but, without knowing
what DID happen we can't safeguard against it happening
again.

>--Original Message--
>Hi,
>There is only Copy objects option in DTS and this will
just copy the objects
>to destination.
>What recovery model you are using for this database, If
it is FULL or
>Bulk_logged and if you have trasnaction logs you can use
the Log explorer
>tool to analyze the situation which caused the object
drop.
>www.lumigent.com
>If you do not have the transaction log backup, it is very
difficult to find[vbcol=seagreen]
>the cause for the object drop.
>--
>Thanks
>Hari
>MCDBA
><anonymous@.discussions.microsoft.com> wrote in message
>news:216db01c45aac$4cc30320$a401280a@.phx.gbl...
caused[vbcol=seagreen]
in[vbcol=seagreen]
that[vbcol=seagreen]
vanished,[vbcol=seagreen]
command[vbcol=seagreen]
the[vbcol=seagreen]
happened,[vbcol=seagreen]
future.
>
>.
>

Mysteriously dropped rows using Data Flow Task

I'm using the Data Flow Task to load data from a flat file into a SQL table and I'm missing rows. And there doesn't see to be any consistent or obvious reason why.

When I use the Bulk Insert Task I import the correct number of rows from the flat file. But when I use the Data Flow task and use a Flat File Source connected to a OLE DB Destination I get about 1/3 the right number of rows. So looking at these loaded tables at the same time I notice that the Data Flow Task method just skips rows sometimes.

Why does this happen?

Try to enable error output and redirect the error output to a different table and see if any rows are sent there.

Hope this helps,

Ovidiu Burlacu

|||

just for people looking around; this issue got addres in other post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=884012&SiteID=1

Rafael Salas

Mysteriously changing datetime data

I know this may seem like a weird thing, but today something very strange
happened to one of my database tables. The table has three columns that are
of type datetime. They normally contain values such as 08/12/2004 16:03:35.
One of the three columns, however, now shows either 08/12/2004 only or
08/12/2004 00:00:00. The other two columns are still "normal".
No update queries have been run against this table.
Does anybody know why the dates would have changed like that?DATETIME columns don't contain any inherent format and they always include
both date and time. It looks like your client application, whatever that may
be, is formatting the date differently to what you expect. Check the
regional settings on your PC or any other options in your app that may
affect date formatting.
David Portas
SQL Server MVP
--|||Where are you looking, in Enterprise Manager? Try using SELECT cols FROM
table in Query Analyzer.
http://www.aspfaq.com/
(Reverse address to reply.)
"SackerMan" <SackerMan@.discussions.microsoft.com> wrote in message
news:6529C05A-2374-421C-93C4-6A498163F365@.microsoft.com...
> I know this may seem like a weird thing, but today something very strange
> happened to one of my database tables. The table has three columns that
are
> of type datetime. They normally contain values such as 08/12/2004
16:03:35.
> One of the three columns, however, now shows either 08/12/2004 only or
> 08/12/2004 00:00:00. The other two columns are still "normal".
> No update queries have been run against this table.
> Does anybody know why the dates would have changed like that?|||Hi SackerMan
The values actually stored in a datetime column don't 'look' anything like
what you have shown us. They are stored in an internal unambigous format
that you never see.
How a datetime value 'looks' depends on the tool and the command you use to
display it.
So, how are you actually seeing these values?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"SackerMan" <SackerMan@.discussions.microsoft.com> wrote in message
news:6529C05A-2374-421C-93C4-6A498163F365@.microsoft.com...
> I know this may seem like a weird thing, but today something very strange
> happened to one of my database tables. The table has three columns that
are
> of type datetime. They normally contain values such as 08/12/2004
16:03:35.
> One of the three columns, however, now shows either 08/12/2004 only or
> 08/12/2004 00:00:00. The other two columns are still "normal".
> No update queries have been run against this table.
> Does anybody know why the dates would have changed like that?

Mysteriously changing datetime data

I know this may seem like a weird thing, but today something very strange
happened to one of my database tables. The table has three columns that are
of type datetime. They normally contain values such as 08/12/2004 16:03:35.
One of the three columns, however, now shows either 08/12/2004 only or
08/12/2004 00:00:00. The other two columns are still "normal".
No update queries have been run against this table.
Does anybody know why the dates would have changed like that?DATETIME columns don't contain any inherent format and they always include
both date and time. It looks like your client application, whatever that may
be, is formatting the date differently to what you expect. Check the
regional settings on your PC or any other options in your app that may
affect date formatting.
--
David Portas
SQL Server MVP
--|||Where are you looking, in Enterprise Manager? Try using SELECT cols FROM
table in Query Analyzer.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"SackerMan" <SackerMan@.discussions.microsoft.com> wrote in message
news:6529C05A-2374-421C-93C4-6A498163F365@.microsoft.com...
> I know this may seem like a weird thing, but today something very strange
> happened to one of my database tables. The table has three columns that
are
> of type datetime. They normally contain values such as 08/12/2004
16:03:35.
> One of the three columns, however, now shows either 08/12/2004 only or
> 08/12/2004 00:00:00. The other two columns are still "normal".
> No update queries have been run against this table.
> Does anybody know why the dates would have changed like that?|||Hi SackerMan
The values actually stored in a datetime column don't 'look' anything like
what you have shown us. They are stored in an internal unambigous format
that you never see.
How a datetime value 'looks' depends on the tool and the command you use to
display it.
So, how are you actually seeing these values?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"SackerMan" <SackerMan@.discussions.microsoft.com> wrote in message
news:6529C05A-2374-421C-93C4-6A498163F365@.microsoft.com...
> I know this may seem like a weird thing, but today something very strange
> happened to one of my database tables. The table has three columns that
are
> of type datetime. They normally contain values such as 08/12/2004
16:03:35.
> One of the three columns, however, now shows either 08/12/2004 only or
> 08/12/2004 00:00:00. The other two columns are still "normal".
> No update queries have been run against this table.
> Does anybody know why the dates would have changed like that?

Mysteriously changing datetime data

I know this may seem like a weird thing, but today something very strange
happened to one of my database tables. The table has three columns that are
of type datetime. They normally contain values such as 08/12/2004 16:03:35.
One of the three columns, however, now shows either 08/12/2004 only or
08/12/2004 00:00:00. The other two columns are still "normal".
No update queries have been run against this table.
Does anybody know why the dates would have changed like that?
DATETIME columns don't contain any inherent format and they always include
both date and time. It looks like your client application, whatever that may
be, is formatting the date differently to what you expect. Check the
regional settings on your PC or any other options in your app that may
affect date formatting.
David Portas
SQL Server MVP
|||Where are you looking, in Enterprise Manager? Try using SELECT cols FROM
table in Query Analyzer.
http://www.aspfaq.com/
(Reverse address to reply.)
"SackerMan" <SackerMan@.discussions.microsoft.com> wrote in message
news:6529C05A-2374-421C-93C4-6A498163F365@.microsoft.com...
> I know this may seem like a weird thing, but today something very strange
> happened to one of my database tables. The table has three columns that
are
> of type datetime. They normally contain values such as 08/12/2004
16:03:35.
> One of the three columns, however, now shows either 08/12/2004 only or
> 08/12/2004 00:00:00. The other two columns are still "normal".
> No update queries have been run against this table.
> Does anybody know why the dates would have changed like that?
|||Hi SackerMan
The values actually stored in a datetime column don't 'look' anything like
what you have shown us. They are stored in an internal unambigous format
that you never see.
How a datetime value 'looks' depends on the tool and the command you use to
display it.
So, how are you actually seeing these values?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"SackerMan" <SackerMan@.discussions.microsoft.com> wrote in message
news:6529C05A-2374-421C-93C4-6A498163F365@.microsoft.com...
> I know this may seem like a weird thing, but today something very strange
> happened to one of my database tables. The table has three columns that
are
> of type datetime. They normally contain values such as 08/12/2004
16:03:35.
> One of the three columns, however, now shows either 08/12/2004 only or
> 08/12/2004 00:00:00. The other two columns are still "normal".
> No update queries have been run against this table.
> Does anybody know why the dates would have changed like that?