Data structure Internals Question

Hi all,
I have found that adding a column to an existing table (tested on a table
with >1M rows >200 bytes long) behaves as follows:
1. If the added column does not allow NULLs and has a DEFAULT value, the
operation actually changes the row structure.
I can see that from the time it takes to complete and the number of reads
and writes I trace with profiler.
SELECTing from the table shows the default value was correctly entered to
the new column.
So far, so good.
2. If the added column allows NULLs, the operation takes ~0 time and just a
few reads and writes, implying a metadata operation only.
SELECTing from the table shows that NULLs were entered instead of the
DEFAULT value.
This is a documented behavior, I know I can use the "WITH VALUES" option to
force the default values into the columns instead of NULLs which will result
in the same behavior as above (1).
This happens regardless of whether the column has a DEFAULT value and also
regardless of whether the column is fixed length or variable length.
What surprises me about it, is the fact that now I actually have a table
with different row structures.
The old rows (before the column was added) were not changed and the new rows
I INSERT or UPDATE will have a different structure reflecting the newly
added column.
From what I know about the data structures, fixed length columns take up
their size regardless of their actual value and variable length columns
require an additional 2 bytes for the column offset in the row. Both fixed
and variable length columns that allow NULLs require an additional NULL bit
in the NULL bitmap for each row as well.
If I SELECT from the table, I get the NULLs as expected from the old rows as
well but where the **** are they actually stored?
I suspected that since the NULL bitmap always uses 8 bit bytes, perhaps
there was a bit left to spare for the new column (although it doesn't seem
logical to have a NULL bit for a column that does not exist in the row
itslef) so I created a table with exactly 8 nullable columns and added a 9th
one to see if it would take the time to allocate another byte for the NULL
bitmap but it seems not to... it completed almost instantaneously (<100ms)
taking only about 20 reads and 3 writes to complete for a >2500 pages table
(I have cleared the cache before running the ALTER TABLE).
Any insights about what's happening under the hood here will be appreciated.
TIA,
AmiAmi,
It would help a lot if for each of the situations you don't
understand you provide the following:
1. A CREATE TABLE statement that creates the table with
the structure it has before the new column is added.
2. An ALTER TABLE statement that adds the new column.
3. What you see from the ALTER TABLE in terms of reads
and writes from Profiler.
4. What you expected from #3.
5. The statements to create any indexes or constraints that
exist on the table beyond what is in the CREATE TABLE
statement.
You seem to have a good understanding of the way rows are
constructed, and I'm not sure exactly what specific situation
it is that you don't understand. As far as whether there is
a bit in the null bitmap to represent NULLs in a non-existent
column, in some sense, this does exist, but a better way of
saying it is that there are at times unused bits in the NULL
bitmap which are just meaningless filler to pad the data
to byte boundaries; when a new nullable column is added
without VALUES in this case, the change can be represented
without any data changes. One of these spare null-bitmap
bits becomes the null bit for the new column through a
change in metadata and at most a flipping of the bit (I don't
know offhand if spare bits are always zeroed out - the
bits may have to be set with write operations also. The null
values don't take up space in the table beyond the null bit.
Some of this is a little different depending on whether the
column in question is fixed or variable length, or whether
the table has any variable-length columns or not. Whether
the table is a heap (no clustered index) or is its clustered
index (if there is one) may also be an issue, and when data
is actually added, the number of writes may change dramatically
depending on whether adding a new column with VALUES
changes the number of rows that fit on each 8K data page.
I hope you can see why it helps to know the particulars of
the examples that concern you. For more information, you
can look at the topics "Estimating the Size of a Table with a
Clustered Index" and "Estimating the Size of a Table Without
a Clustered Index" in Books Online.
Steve Kass
Drew University
Ami Levin wrote:

>Hi all,
>I have found that adding a column to an existing table (tested on a table
>with >1M rows >200 bytes long) behaves as follows:
>1. If the added column does not allow NULLs and has a DEFAULT value, the
>operation actually changes the row structure.
>I can see that from the time it takes to complete and the number of reads
>and writes I trace with profiler.
>SELECTing from the table shows the default value was correctly entered to
>the new column.
>So far, so good.
>2. If the added column allows NULLs, the operation takes ~0 time and just a
>few reads and writes, implying a metadata operation only.
>SELECTing from the table shows that NULLs were entered instead of the
>DEFAULT value.
>This is a documented behavior, I know I can use the "WITH VALUES" option to
>force the default values into the columns instead of NULLs which will resul
t
>in the same behavior as above (1).
>This happens regardless of whether the column has a DEFAULT value and also
>regardless of whether the column is fixed length or variable length.
>What surprises me about it, is the fact that now I actually have a table
>with different row structures.
>The old rows (before the column was added) were not changed and the new row
s
>I INSERT or UPDATE will have a different structure reflecting the newly
>added column.
>From what I know about the data structures, fixed length columns take up
>their size regardless of their actual value and variable length columns
>require an additional 2 bytes for the column offset in the row. Both fixed
>and variable length columns that allow NULLs require an additional NULL bit
>in the NULL bitmap for each row as well.
>If I SELECT from the table, I get the NULLs as expected from the old rows a
s
>well but where the **** are they actually stored?
>I suspected that since the NULL bitmap always uses 8 bit bytes, perhaps
>there was a bit left to spare for the new column (although it doesn't seem
>logical to have a NULL bit for a column that does not exist in the row
>itslef) so I created a table with exactly 8 nullable columns and added a 9t
h
>one to see if it would take the time to allocate another byte for the NULL
>bitmap but it seems not to... it completed almost instantaneously (<100ms)
>taking only about 20 reads and 3 writes to complete for a >2500 pages table
>(I have cleared the cache before running the ALTER TABLE).
>Any insights about what's happening under the hood here will be appreciated
.
>TIA,
>Ami
>
>|||Hi Steve,
Thanks for your prompt reply.
here is a similar script to the one i have used for the last test to try and
focus the question:
CREATE TABLE T1
(
PK_col INT NOT NULL IDENTITY PRIMARY KEY,
Col1 INT NULL,
col2 INT NULL,
col3 INT NULL,
col4 INT NULL,
col5 INT NULL,
col6 INT NULL,
col7 INT NULL,
col8 INT NULL
)
GO
SET NOCOUNT ON
DECLARE @.c INT
SET @.c = 0
WHILE @.c <= 250000
BEGIN
INSERT T1
SELECT @.c,@.c,@.c,@.c,@.c,@.c,@.c,@.c
SET @.c = @.c + 1
END
GO
SELECT dpages
FROM sysindexes
WHERE id = OBJECT_ID('T1')
-- 1420 pages
GO
DBCC DROPCLEANBUFFERS
GO
-- start profiler trace
ALTER TABLE T1 ADD col9 INT NULL
GO
-- 46ms duration, 27 Reads, 3 Writes
Since the ALTER TABLE took only 27 reads, it obviously couldn't have changed
all 1420 pages.
Since the original table had exactly 8 nullable columns, it should have
filled the default 1 byte allocated for the null bitmap.
My questions are:
Where are the null values stored for the existing 250,000 rows?
If I execute the following statements:
INSERT T1
SELECT 1,1,1,1,1,1,1,1,1
GO
Now I have a new row that is physically about 9X4 bytes long (ignoring row
overhead) and 2 bytes of null bitmap.
The other 250K rows are 8X4 bytes long with 1 byte null bitmap.
How does SQL Server know how to distinguish between the 2 different
structures?
The only way it can do that IMHO is to "assume" that if the physical row
strcuture is missing a column compared to the table metadata, that column
must be NULL.
When updating, does it compare the table metadata with the actual row
structure it encounters while reading the pages and change the row structure
"on the fly" if necessary?
Things will get much more complicated now if for example I will do:
1. INSERT a few rows with values for all 9 columns
2. DROP col3 (or any other column) - I know that drop column is also a
metadata only operation, the column will not be physically removed.
3. ADD col10
4. UPDATE some rows adding values to the new columns
4. INSERT more rows
5. ... etc. etc.
I've read quite a few books and articles on SQL Server internals but I can't
remember ever encountering any articles relating to this issue.
I can start digging into it by examining DBCC PAGE internal structures but I
was hoping someone here already knows how it works under the hood to save me
the time and effort :-)
TIA
Ami
"Steve Kass" <skass@.drew.edu> wrote in message
news:OkQfZDuAFHA.2640@.TK2MSFTNGP14.phx.gbl...
> Ami,
> It would help a lot if for each of the situations you don't
> understand you provide the following:
> 1. A CREATE TABLE statement that creates the table with
> the structure it has before the new column is added.
> 2. An ALTER TABLE statement that adds the new column.
> 3. What you see from the ALTER TABLE in terms of reads
> and writes from Profiler.
> 4. What you expected from #3.
> 5. The statements to create any indexes or constraints that
> exist on the table beyond what is in the CREATE TABLE
> statement.
> You seem to have a good understanding of the way rows are
> constructed, and I'm not sure exactly what specific situation
> it is that you don't understand. As far as whether there is
> a bit in the null bitmap to represent NULLs in a non-existent
> column, in some sense, this does exist, but a better way of
> saying it is that there are at times unused bits in the NULL
> bitmap which are just meaningless filler to pad the data
> to byte boundaries; when a new nullable column is added
> without VALUES in this case, the change can be represented
> without any data changes. One of these spare null-bitmap
> bits becomes the null bit for the new column through a
> change in metadata and at most a flipping of the bit (I don't
> know offhand if spare bits are always zeroed out - the
> bits may have to be set with write operations also. The null
> values don't take up space in the table beyond the null bit.
> Some of this is a little different depending on whether the
> column in question is fixed or variable length, or whether
> the table has any variable-length columns or not. Whether
> the table is a heap (no clustered index) or is its clustered
> index (if there is one) may also be an issue, and when data
> is actually added, the number of writes may change dramatically
> depending on whether adding a new column with VALUES
> changes the number of rows that fit on each 8K data page.
> I hope you can see why it helps to know the particulars of
> the examples that concern you. For more information, you
> can look at the topics "Estimating the Size of a Table with a
> Clustered Index" and "Estimating the Size of a Table Without
> a Clustered Index" in Books Online.
> Steve Kass
> Drew University
>
> Ami Levin wrote:
>
a
to
result
also
rows
fixed
bit
as
seem
9th
NULL
(<100ms)
table
appreciated.|||Hi Ami,
My guess is that NULLs for the new columns aren't stored anywhere, and that
the algorithm for reading data is something like this:
- read metadata
- read row
- if you find less columns than expected, assume NULL for missing columns
It's easy to figure out whether there are missing columns since the row
internally holds the actual number of fixed/variable length columns.
When the row is actually modified for any purpose, then all postponed
physical changes take place.
If you want, I can post this in the private MVP forum to get the developers
feedback, but I suspect it won't be much different than this.
Let me know...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ami Levin" <XXX_NOSPAM___XXX__amlevin@.mercury.com> wrote in message
news:%23GjYwBvAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> Hi Steve,
> Thanks for your prompt reply.
> here is a similar script to the one i have used for the last test to try
> and
> focus the question:
> CREATE TABLE T1
> (
> PK_col INT NOT NULL IDENTITY PRIMARY KEY,
> Col1 INT NULL,
> col2 INT NULL,
> col3 INT NULL,
> col4 INT NULL,
> col5 INT NULL,
> col6 INT NULL,
> col7 INT NULL,
> col8 INT NULL
> )
> GO
> SET NOCOUNT ON
> DECLARE @.c INT
> SET @.c = 0
> WHILE @.c <= 250000
> BEGIN
> INSERT T1
> SELECT @.c,@.c,@.c,@.c,@.c,@.c,@.c,@.c
> SET @.c = @.c + 1
> END
> GO
> SELECT dpages
> FROM sysindexes
> WHERE id = OBJECT_ID('T1')
> -- 1420 pages
> GO
> DBCC DROPCLEANBUFFERS
> GO
> -- start profiler trace
> ALTER TABLE T1 ADD col9 INT NULL
> GO
> -- 46ms duration, 27 Reads, 3 Writes
> Since the ALTER TABLE took only 27 reads, it obviously couldn't have
> changed
> all 1420 pages.
> Since the original table had exactly 8 nullable columns, it should have
> filled the default 1 byte allocated for the null bitmap.
> My questions are:
> Where are the null values stored for the existing 250,000 rows?
> If I execute the following statements:
> INSERT T1
> SELECT 1,1,1,1,1,1,1,1,1
> GO
> Now I have a new row that is physically about 9X4 bytes long (ignoring row
> overhead) and 2 bytes of null bitmap.
> The other 250K rows are 8X4 bytes long with 1 byte null bitmap.
> How does SQL Server know how to distinguish between the 2 different
> structures?
> The only way it can do that IMHO is to "assume" that if the physical row
> strcuture is missing a column compared to the table metadata, that column
> must be NULL.
> When updating, does it compare the table metadata with the actual row
> structure it encounters while reading the pages and change the row
> structure
> "on the fly" if necessary?
> Things will get much more complicated now if for example I will do:
> 1. INSERT a few rows with values for all 9 columns
> 2. DROP col3 (or any other column) - I know that drop column is also a
> metadata only operation, the column will not be physically removed.
> 3. ADD col10
> 4. UPDATE some rows adding values to the new columns
> 4. INSERT more rows
> 5. ... etc. etc.
> I've read quite a few books and articles on SQL Server internals but I
> can't
> remember ever encountering any articles relating to this issue.
> I can start digging into it by examining DBCC PAGE internal structures but
> I
> was hoping someone here already knows how it works under the hood to save
> me
> the time and effort :-)
> TIA
> Ami
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:OkQfZDuAFHA.2640@.TK2MSFTNGP14.phx.gbl...
> a
> to
> result
> also
> rows
> fixed
> bit
> as
> seem
> 9th
> NULL
> (<100ms)
> table
> appreciated.
>|||Hi Itzik :-)
How are you?
I have guessed the same (I wrote it in my reply to steve) but i would like
to know how it really works.
Our "guess" seems good enough for the simple script I have used but what
about the more complex situation if after adding col9 we will
1. INSERT a few rows with values for all 9 columns
2. DROP col3 - physical data will not be removed...
3. ADD col10
4. UPDATE some rows with values to the new column
5. DROP col5
5. Add col11
6. INSERT more rows
7. ... etc. etc.
can you see a way to do it without keeping extra metadata?
Let us know what the developers say.
I find this very interesting.
See you soon,
Ami
--
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:ecderKvAFHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi Ami,
> My guess is that NULLs for the new columns aren't stored anywhere, and
that
> the algorithm for reading data is something like this:
> - read metadata
> - read row
> - if you find less columns than expected, assume NULL for missing columns
> It's easy to figure out whether there are missing columns since the row
> internally holds the actual number of fixed/variable length columns.
> When the row is actually modified for any purpose, then all postponed
> physical changes take place.
> If you want, I can post this in the private MVP forum to get the
developers
> feedback, but I suspect it won't be much different than this.
> Let me know...
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com|||Ami, here's what I got back:
"* Add nullable column -- This does not require any data update because the
way the row format is setup, the newly added nullable column assumes NULL
value by default. Similarly, when a
not-nullable column is altered to be nullable, no update is required
because the row format is capable of storing a nullbit for all columns.
* Drop column -- The dropped column(s) continue to consume space (even if
rows are updated or inserted(fixed-len cols)) until the clustered index is
rebuilt. This includes the LOB allocations, and they are only deallocated
on rebuild. For heaps, dbcc cleantable will reclaim space used by dropped
LOB and variable-length columns only.
In Shiloh, we did not have a case where we postponed a data-update due to a
schema-change until the time the row is touched. In Yukon, we do postpone
pushing/pulling data out-of-row/inrow when the setting is changed for the
new max LOB types.
For dropped columns we did not store any information in Shiloh except for a
flag in sysindexes indicating that LOB deallocation/cleanup is required.
Evidence of dropped columns could be deduced from the offsets allocated to
existing columns. As far as compacting the rows is concerned, this occurs
automatically as part of index rebuild. In Yukon, we now store a bit more
information about dropped columns.
Bottom-line, we don't really have a general concept of "postponed or lazy
schema changes", except for the couple of cases mentioned above which only
Sort-of fall into this category."
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ami Levin" <XXX_NOSPAM___XXX__amlevin@.mercury.com> wrote in message
news:%23fB$chvAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> Hi Itzik :-)
> How are you?
> I have guessed the same (I wrote it in my reply to steve) but i would like
> to know how it really works.
> Our "guess" seems good enough for the simple script I have used but what
> about the more complex situation if after adding col9 we will
> 1. INSERT a few rows with values for all 9 columns
> 2. DROP col3 - physical data will not be removed...
> 3. ADD col10
> 4. UPDATE some rows with values to the new column
> 5. DROP col5
> 5. Add col11
> 6. INSERT more rows
> 7. ... etc. etc.
> can you see a way to do it without keeping extra metadata?
> Let us know what the developers say.
> I find this very interesting.
> See you soon,
> Ami
> --
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:ecderKvAFHA.824@.TK2MSFTNGP11.phx.gbl...
> that
> developers
>|||Thanks Itzik.
That does not explain the "how" but we'll have to settle for it i guess :-)
Thanks again,
Ami
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uN7eb6VBFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ami, here's what I got back:
> "* Add nullable column -- This does not require any data update because
> the
> way the row format is setup, the newly added nullable column assumes NULL
> value by default. Similarly, when a
> not-nullable column is altered to be nullable, no update is required
> because the row format is capable of storing a nullbit for all columns.
> * Drop column -- The dropped column(s) continue to consume space (even if
> rows are updated or inserted(fixed-len cols)) until the clustered index is
> rebuilt. This includes the LOB allocations, and they are only deallocated
> on rebuild. For heaps, dbcc cleantable will reclaim space used by dropped
> LOB and variable-length columns only.
> In Shiloh, we did not have a case where we postponed a data-update due to
> a
> schema-change until the time the row is touched. In Yukon, we do postpone
> pushing/pulling data out-of-row/inrow when the setting is changed for the
> new max LOB types.
> For dropped columns we did not store any information in Shiloh except for
> a
> flag in sysindexes indicating that LOB deallocation/cleanup is required.
> Evidence of dropped columns could be deduced from the offsets allocated to
> existing columns. As far as compacting the rows is concerned, this occurs
> automatically as part of index rebuild. In Yukon, we now store a bit more
> information about dropped columns.
> Bottom-line, we don't really have a general concept of "postponed or lazy
> schema changes", except for the couple of cases mentioned above which only
> Sort-of fall into this category."
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ami Levin" <XXX_NOSPAM___XXX__amlevin@.mercury.com> wrote in message
> news:%23fB$chvAFHA.2016@.TK2MSFTNGP15.phx.gbl...
>

Data Space Usage

Hi All
I have tables with Text / nText columns. On an average I add 12-15K rows/day in each of these tables.
Is there any easy way to compute the growth of SIZE of these tables, on a daily basis.
I used datalength (Text Col Name), but not sure if that is the right way.
Any ideas.I plot the growth of databases in Excel. To do this I use the backup
information stored in msdb. If it's just a single table you want to monitor
this won't be of any help.
select backup_start_date,backup_size from msdb..backupset where
database_name = 'pubs'
--
HTH
Ryan Waight, MCDBA, MCSE
"Prasanna Prabhu" <pprabhu@.pbs.solutionsiq.com> wrote in message
news:A98763F8-F73C-4EDF-918A-2B5D1D33F2E1@.microsoft.com...
> Hi All
> I have tables with Text / nText columns. On an average I add 12-15K
rows/day in each of these tables.
> Is there any easy way to compute the growth of SIZE of these tables, on a
daily basis.
> I used datalength (Text Col Name), but not sure if that is the right way.
> Any ideas.
>|||How about using sysindexes to get table/index size each night? Just be aware that the info might be
out-of-date (DBCC UPDATEUSAGE).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Prasanna Prabhu" <pprabhu@.pbs.solutionsiq.com> wrote in message
news:A98763F8-F73C-4EDF-918A-2B5D1D33F2E1@.microsoft.com...
> Hi All
> I have tables with Text / nText columns. On an average I add 12-15K rows/day in each of these
tables.
> Is there any easy way to compute the growth of SIZE of these tables, on a daily basis.
> I used datalength (Text Col Name), but not sure if that is the right way.
> Any ideas.
>|||Hi All,
Thanks for your help. Because there is something wrong on Prasanna side. He
asked me post his response here. Here is the reply from the Prasanna:
--
Hi Tibor
I could only get the "COUNT of rows" information from sysindexes.
How will get the information about the SPACE usage of these newly added
rows.
I want to know the rate at which the DB is growing evey 3-4 hours.
----
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Prasanna,
Thanks for your response. I understand that you want to monitor the growth
size of the tables in your database on a daily basis. If I have
misunderstood, please feel free to let me know.
As I understand, there is no easy way to meet your requirements with SQL
Server. However, we can produce a stored procedure selecting data from the
sysindexes table and storing the result records in the specified tables.
Then we perform the stored procedure every day via scheduled job. In that
case, we can monitor the growth size of the tables via reviewing the
specified tables storing the useful records.
Sysindexes contains one row for each index and table in the database and
stored in each database.
For additional information regarding the sysindexes tables, please refer to
the following articles on SQL Server Books Online.
Topic: "sysindexes"
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Sorry for the late reply:
> Hi Tibor
> I could only get the "COUNT of rows" information from sysindexes.
> How will get the information about the SPACE usage of these newly added
> rows.
Check out the pages, dpages and reserved columns. Sysindexes is documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:ibfolwcmDHA.2464@.cpmsftngxa06.phx.gbl...
> Hi All,
> Thanks for your help. Because there is something wrong on Prasanna side. He
> asked me post his response here. Here is the reply from the Prasanna:
> --
> Hi Tibor
> I could only get the "COUNT of rows" information from sysindexes.
> How will get the information about the SPACE usage of these newly added
> rows.
> I want to know the rate at which the DB is growing evey 3-4 hours.
> ----
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>
>

Data Source Column Changes

I have some Dynamic SQL that builds a table with the top 20 item numbers as columns, Stores as rows and an X marking the intersection of Store/Item. This produces a table which is a matrix showing which stores are low on which items.
This table is generated once a week and each week will have different column names because the item numbers which the stores are low on changes from week to week.
How can I write a report that will handle the column names of the data table changing without having to do maintenance on the report every week?

Thanks,
Mark Redman.

One way of doing this is to move the dynamic SQL into a stored procedure and return a dataset which always has fixed column names. The actual item information (item number) would be e.g. the first row in that generated dataset.

-- Robert

Data script task - how to generate multiple rows from one row ? (string splitter)

Hi

in input we have a set of rows, each one with a column containing a string (eg: "AAOOOOAAAOOA").

We'd like to split this string (using a vb.net data script task) into tokens (eg: "AA", then "OOOO","AAA","OO","A"), and to output one line per token.

How can we achieve that with a vb.net data script task ? (Or anything else ?)

best regards

ThibautJust take your source and throw it into a derived column transformation where you'll perform your string split to create new fields for each "token." Then you'll go into an unpivot transformation where you'll take the new columns and turn them into rows.

Why use a script task when you can use the optimized data flow tools as-is?|||

You need to create an asynchrnous transform. So add the transform, select and also setup columns as required. Ensure the SynchronousInputID of the output is set to 0, making it async.

Then read rows and add them to the output as required. Sum dummy code -

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

' Read the rows in...

While Row.NextRow() ' This happens once for each input row

' Do something here, and as required, add rows to the output, use a loop or whatever

For i As Int = i < 10

With OutputBuffer

.AddRow() ' Adding a new output row and setting values. Can do this as many times as we like, 0 or more times in the context of this input row loop interation

.Asset = Row.InputColumn

.Product = i

Next

End While

If Row.EndOfRowset Then

OutputBuffer.SetEndOfRowset()

End If

End Sub

|||Thanks a lot Darren ! That's really perfect (and thanks for the detailed sample, I really appreciate).

regards,

Thibaut|||Just be careful because some have tested the script task and it performs slower than the other, native dataflow tasks.|||Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?|||

DarrenSQLIS wrote:

Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?

Yep, no problem... http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=857796&SiteID=1|||Hi Phil

thanks for your input as well. Like Darren, I agree that a good rule of thumb is that a pipeline component will work generally faster than a corresponding script component.

A script component is generally one-shot code, which generally doesn't get the same level of testing and optimization. In the article you point to, the script task benchmark shows an average CPU usage of 5% (which is often a sign of synchronization issues, contention etc). Here (like suggested by a commenter), a modification of the implementation (like handling sets of rows instead of one row at a time) would most likely boost the performance a lot.

So I wouldn't draw conclusions based on a single script example, given that each implementation is likely to vary a lot in terms of performance and memory consumption - just like any kind of code!

But anyway - it seems that I have two solutions for my problem now. I don't hesitate to use script task when they prove useful, but I first try to stick to the pipeline components.

I'll keep you posted!

thanks again for the input

Thibaut Barrère|||Hi

I've finished the job using a script task (I will report back the details later).

I'm curious about how it would have been possible to implement this using a derived column transformation (at first sight I couldn't find out).

Phil could you give a bit more details ? I could not find any real string splitting functions in SSIS - were you thinking of using a FIND function recursively (or any other feature I've missed ?)

cheers

Thibaut

Data Retrieval is Slow

I have a database with a table that holds 30 million rows. Clients have
access to only those records that concern them. The problem is they want to
have access to all their data at once with the largest resultset
returning1.5 million rows.
Having toyed with the indexes, performance monitors and the like, I still
can't get the data to the client fast enough.
Could you offer any advice on what I should do?
Thanks
'Wale
wale wrote:

> I have a database with a table that holds 30 million rows. Clients have
> access to only those records that concern them. The problem is they want to
> have access to all their data at once with the largest resultset
> returning1.5 million rows.
> Having toyed with the indexes, performance monitors and the like, I still
> can't get the data to the client fast enough.
> Could you offer any advice on what I should do?
> Thanks
> 'Wale
>
LOL. Manage their expectations? ;)
But, realistically, how can anyone do anything practical with 1.5
million rows at once? There must be some better way of presenting the
data without giving it all to them at once (obviously I'm making this
statement with absolutely no knowledge of your situation so I could be
full of it ;) )
Zach
sql

Data Retrieval is Slow

I have a database with a table that holds 30 million rows. Clients have
access to only those records that concern them. The problem is they want to
have access to all their data at once with the largest resultset
returning1.5 million rows.
Having toyed with the indexes, performance monitors and the like, I still
can't get the data to the client fast enough.
Could you offer any advice on what I should do?
Thanks
'Walewale wrote:

> I have a database with a table that holds 30 million rows. Clients have
> access to only those records that concern them. The problem is they want
to
> have access to all their data at once with the largest resultset
> returning1.5 million rows.
> Having toyed with the indexes, performance monitors and the like, I still
> can't get the data to the client fast enough.
> Could you offer any advice on what I should do?
> Thanks
> 'Wale
>
LOL. Manage their expectations? ;)
But, realistically, how can anyone do anything practical with 1.5
million rows at once? There must be some better way of presenting the
data without giving it all to them at once (obviously I'm making this
statement with absolutely no knowledge of your situation so I could be
full of it ;) )
Zach

Data processing

Hello,
I am wondering where to do certain processing of data, I receive data
of about 5000 rows in the sql server (2005 Beta) Now after I got the
data I need to go trough the records and if that record matches some
criteria than I add a new record in an other table, than the next
records criteria are affected by the last record added in the other
table. Anyway it is not something I think I can do whitout some
programming logic.
Now my question is, where should I do this? Do I create a client
program and let that do it or should I create the same functionality in
the sql server? I understood I can now use c# there. Also will that be
supported by SQL Server Express 2005?
Any help is appriciated.
-MarkYou could use a cursor to do linear operations (row by row)...
declare yourcur cursor for
select ...
from ...
open yourcur
declare @.col1 ...
fetch next from yourcur into @.col1
while @.@.fetch_status = 0
begin
insert ...
fetch next from yourcur into @.col1
end
deallocate yourcur
I hope that gives you an idea, have a gander at books online for more
detailed information and examples.
But it will let you control on a row by row basis what the insert does, you
can have complex logic in the while loop.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<internet.spam.mail@.gmail.com> wrote in message
news:1129721426.240935.263200@.g49g2000cwa.googlegroups.com...
> Hello,
> I am wondering where to do certain processing of data, I receive data
> of about 5000 rows in the sql server (2005 Beta) Now after I got the
> data I need to go trough the records and if that record matches some
> criteria than I add a new record in an other table, than the next
> records criteria are affected by the last record added in the other
> table. Anyway it is not something I think I can do whitout some
> programming logic.
> Now my question is, where should I do this? Do I create a client
> program and let that do it or should I create the same functionality in
> the sql server? I understood I can now use c# there. Also will that be
> supported by SQL Server Express 2005?
> Any help is appriciated.
> -Mark
>|||Best do it in the back end: two ways of doing this
1) Use Triggers to cascade updates
2)use sproc to update table by enabling a job at defined frequencies
--
Regards
R.D
--Knowledge gets doubled when shared
"internet.spam.mail@.gmail.com" wrote:

> Hello,
> I am wondering where to do certain processing of data, I receive data
> of about 5000 rows in the sql server (2005 Beta) Now after I got the
> data I need to go trough the records and if that record matches some
> criteria than I add a new record in an other table, than the next
> records criteria are affected by the last record added in the other
> table. Anyway it is not something I think I can do whitout some
> programming logic.
> Now my question is, where should I do this? Do I create a client
> program and let that do it or should I create the same functionality in
> the sql server? I understood I can now use c# there. Also will that be
> supported by SQL Server Express 2005?
> Any help is appriciated.
> -Mark
>|||Tony,
Would I be able to do tha using c#? since sql server 2005 supposed to
use that, than it can be faster, because I thought using your way is
really not that efficient.
R.D.
So when a new record is added to the first table excetute some stuff to
add or alter an record in the second database? Does that also work with
sql server express (the new msde)?
,
Thanks will look into both approcess.
-Mark

Data not visible??

I recently imported some Access tables into SQL Server. I can go into enterprise manager and return all rows and the data is there.

I build an ASP page to edit the data. After the connection the data for the record is displayed in a form. For some reason some fields are not coming through?

Is this a problem when converting to SQL Server. It just doesn't make sense that the table in Enterprise manager says the field is populated but when I do a pull via ASP the data doesn't come through.

Does anyone know anything about this?

Any help or pointing in the right direction would be greatly appreciated.

Thanks,

JWExecute your query in Query Analyzer. Compare the results to those obtained through your ASP page.|||There is nothing special about the query. It's select all from the table but by the vaiable ID number that is passed.

See the weird thing is, is that it doesn't bomb out or anything and brings back some fields in the recordset but not all. But then I return all rows in query analyzer and the data for those fields is populated.|||Please post the ASP code that executes the query and displays the data from the recordset.|||<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=golfinam;UID=golfinam;PWD=XXXXXX;DATABASE=XXXX XX"
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM tbl_Courses where CourseID = " & request.querystring("CourseID")
objRS.Open strSQL, objConn%>
<% if ObjRS.eof then %><% else %>

'Then this is where the form is with each forms default set to a field,
<textarea rows="7" name="PTPDirections" cols="53"><%=objRS("PTPDirections")%></textarea>

<%End If%>
<%'Close the Recordset object
objRS.Close

'Delete the Recordset Object
Set objRS = Nothing

'Close the Connection object
objConn.Close

'Delete the Connection Object
Set objConn = Nothing
%>|||Is there a setting or something in the import that would cause it to act this way?

Data not showing for some users

Hi there,

Some users, when they select a table and try to "Return all rows" in Enterprise Manager, they get the word Binary instead of the data that should be there. The table has 4 columns, an ID column and three data columns. The ID field data is correct but the data in the other columns just show the word 'Binary'.

I got the user to connect to the database using the 'sa' login and the problem is still there. I can connect from my machine and see the data fine. Only one user is experiencing these problems.

The problem occurs with all the tables in the database. Any field that should have text data, e.g. nchar, nvarchar, etc., is showing 'Binary'. Fields that have numeric or date types show fine.

If the users runs a select query from Query Analyser, then the data appears fine.

Any help??This problem seems to have occured since i tried to install a SQLBase odbc driver. The data in the database is fine but Enterprise Manager seems to be corrupted.

Any help??

Data Loss?

Hi,
I'm sure this is a very broad question, but what could cause roughly
125,000 rows in one table to disappear over night. I'm running SQL server
7. I've done multiple virus scans...all clean...although mydoom was
previously found and quarantined.
Could someone give me at least a starting point on troubleshooting this.
The same thing happened to the same table about 3 months ago. At that time,
I figured it was some strange fluke, but now that it has happened again, it
is rather disconcerting.
Any help would be appreciated!!
Thanks,
DanHave you used a SQL log examination tool (Log P.I., Lumigent Log Explorer)
to see if somebody issued a DELETE statement?
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan B" <none@.none.com> wrote in message
news:u038qvI3EHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm sure this is a very broad question, but what could cause roughly
> 125,000 rows in one table to disappear over night. I'm running SQL server
> 7. I've done multiple virus scans...all clean...although mydoom was
> previously found and quarantined.
> Could someone give me at least a starting point on troubleshooting this.
> The same thing happened to the same table about 3 months ago. At that
time,
> I figured it was some strange fluke, but now that it has happened again,
it
> is rather disconcerting.
> Any help would be appreciated!!
> Thanks,
> Dan
>|||Dan B wrote:
> Hi,
> I'm sure this is a very broad question, but what could cause roughly
> 125,000 rows in one table to disappear over night. I'm running SQL
> server 7. I've done multiple virus scans...all clean...although
> mydoom was
> previously found and quarantined.
> Could someone give me at least a starting point on troubleshooting
> this. The same thing happened to the same table about 3 months ago. At
> that time, I figured it was some strange fluke, but now that it
> has happened again, it is rather disconcerting.
> Any help would be appreciated!!
> Thanks,
> Dan
Make sure there are no running jobs in SQL Agent. Change all your
passwords on all logins that have delete access to the table or are in
the sysadmin group.
--
David Gugick
Imceda Software
www.imceda.com|||"Dan B" <none@.none.com> wrote in message
news:u038qvI3EHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm sure this is a very broad question, but what could cause roughly
> 125,000 rows in one table to disappear over night. I'm running SQL server
> 7. I've done multiple virus scans...all clean...although mydoom was
> previously found and quarantined.
> Could someone give me at least a starting point on troubleshooting this.
> The same thing happened to the same table about 3 months ago. At that
time,
> I figured it was some strange fluke, but now that it has happened again,
it
> is rather disconcerting.
> Any help would be appreciated!!
> Thanks,
> Dan
>
Perhaps you could add a trigger to the table that would write diagnostic
information to a logging table, such as time-of-deletion and possibly
information about the user that the deletion SQL was running as.
Regards,
Simon|||I have a evaluation copy of Lumigent Log Explorer, which I used to the
extent the evaluation allows. I was able to when the deleting took place,
and the SPID of the user or computer it came from, but apparently SPIDs
change so whoever had that number on the day the deletions occurred has a
different SPID now. It looks like the 125,000 rows where deleted within
about 10 minutes. People who use the application that is tied to the
database cannot delete anything. I just can't see how this
happened....again.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OSKCb0I3EHA.3000@.TK2MSFTNGP15.phx.gbl...
> Have you used a SQL log examination tool (Log P.I., Lumigent Log Explorer)
> to see if somebody issued a DELETE statement?
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Dan B" <none@.none.com> wrote in message
> news:u038qvI3EHA.2012@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> I'm sure this is a very broad question, but what could cause roughly
>> 125,000 rows in one table to disappear over night. I'm running SQL
>> server
>> 7. I've done multiple virus scans...all clean...although mydoom was
>> previously found and quarantined.
>> Could someone give me at least a starting point on troubleshooting this.
>> The same thing happened to the same table about 3 months ago. At that
> time,
>> I figured it was some strange fluke, but now that it has happened again,
> it
>> is rather disconcerting.
>> Any help would be appreciated!!
>> Thanks,
>> Dan
>>
>|||Lumigent Log Explorer will record logins and SPIDS so you can see extended
information about who and where the changes came from. If you know when it
is happening, you can run Profiler to grab complete information.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan B" <none@.none.com> wrote in message
news:%23O9sqnJ3EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I have a evaluation copy of Lumigent Log Explorer, which I used to the
> extent the evaluation allows. I was able to when the deleting took place,
> and the SPID of the user or computer it came from, but apparently SPIDs
> change so whoever had that number on the day the deletions occurred has a
> different SPID now. It looks like the 125,000 rows where deleted within
> about 10 minutes. People who use the application that is tied to the
> database cannot delete anything. I just can't see how this
> happened....again.
>
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OSKCb0I3EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > Have you used a SQL log examination tool (Log P.I., Lumigent Log
Explorer)
> > to see if somebody issued a DELETE statement?
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Dan B" <none@.none.com> wrote in message
> > news:u038qvI3EHA.2012@.TK2MSFTNGP15.phx.gbl...
> >> Hi,
> >> I'm sure this is a very broad question, but what could cause roughly
> >> 125,000 rows in one table to disappear over night. I'm running SQL
> >> server
> >> 7. I've done multiple virus scans...all clean...although mydoom was
> >> previously found and quarantined.
> >>
> >> Could someone give me at least a starting point on troubleshooting
this.
> >> The same thing happened to the same table about 3 months ago. At that
> > time,
> >> I figured it was some strange fluke, but now that it has happened
again,
> > it
> >> is rather disconcerting.
> >>
> >> Any help would be appreciated!!
> >>
> >> Thanks,
> >> Dan
> >>
> >>
> >
> >
>|||You could turn on event auditing. Also, is the system secured? You don't
need your app to connect to the system and delete information. MS Access,
SS Query Analyzer, MS Query, etc., etc.
Is the application using Windows Authentication or SQL Server logins?
Logins per user or one application login? Is the password well known? What
is the application security level on the server and within the database? Is
the SA account secured? Who knows the password?
I'd be running audit traces pretty heavily until you found out who has been
connecting to the system directly.
Sincerely,
Anthony Thomas
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uNkFfqJ3EHA.3416@.TK2MSFTNGP09.phx.gbl...
Lumigent Log Explorer will record logins and SPIDS so you can see extended
information about who and where the changes came from. If you know when it
is happening, you can run Profiler to grab complete information.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan B" <none@.none.com> wrote in message
news:%23O9sqnJ3EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I have a evaluation copy of Lumigent Log Explorer, which I used to the
> extent the evaluation allows. I was able to when the deleting took place,
> and the SPID of the user or computer it came from, but apparently SPIDs
> change so whoever had that number on the day the deletions occurred has a
> different SPID now. It looks like the 125,000 rows where deleted within
> about 10 minutes. People who use the application that is tied to the
> database cannot delete anything. I just can't see how this
> happened....again.
>
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OSKCb0I3EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > Have you used a SQL log examination tool (Log P.I., Lumigent Log
Explorer)
> > to see if somebody issued a DELETE statement?
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Dan B" <none@.none.com> wrote in message
> > news:u038qvI3EHA.2012@.TK2MSFTNGP15.phx.gbl...
> >> Hi,
> >> I'm sure this is a very broad question, but what could cause roughly
> >> 125,000 rows in one table to disappear over night. I'm running SQL
> >> server
> >> 7. I've done multiple virus scans...all clean...although mydoom was
> >> previously found and quarantined.
> >>
> >> Could someone give me at least a starting point on troubleshooting
this.
> >> The same thing happened to the same table about 3 months ago. At that
> > time,
> >> I figured it was some strange fluke, but now that it has happened
again,
> > it
> >> is rather disconcerting.
> >>
> >> Any help would be appreciated!!
> >>
> >> Thanks,
> >> Dan
> >>
> >>
> >
> >
>

Data Loss?

Hi,
I'm sure this is a very broad question, but what could cause roughly
125,000 rows in one table to disappear over night. I'm running SQL server
7. I've done multiple virus scans...all clean...although mydoom was
previously found and quarantined.
Could someone give me at least a starting point on troubleshooting this.
The same thing happened to the same table about 3 months ago. At that time,
I figured it was some strange fluke, but now that it has happened again, it
is rather disconcerting.
Any help would be appreciated!!
Thanks,
Dan
Have you used a SQL log examination tool (Log P.I., Lumigent Log Explorer)
to see if somebody issued a DELETE statement?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan B" <none@.none.com> wrote in message
news:u038qvI3EHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm sure this is a very broad question, but what could cause roughly
> 125,000 rows in one table to disappear over night. I'm running SQL server
> 7. I've done multiple virus scans...all clean...although mydoom was
> previously found and quarantined.
> Could someone give me at least a starting point on troubleshooting this.
> The same thing happened to the same table about 3 months ago. At that
time,
> I figured it was some strange fluke, but now that it has happened again,
it
> is rather disconcerting.
> Any help would be appreciated!!
> Thanks,
> Dan
>
|||Dan B wrote:
> Hi,
> I'm sure this is a very broad question, but what could cause roughly
> 125,000 rows in one table to disappear over night. I'm running SQL
> server 7. I've done multiple virus scans...all clean...although
> mydoom was
> previously found and quarantined.
> Could someone give me at least a starting point on troubleshooting
> this. The same thing happened to the same table about 3 months ago. At
> that time, I figured it was some strange fluke, but now that it
> has happened again, it is rather disconcerting.
> Any help would be appreciated!!
> Thanks,
> Dan
Make sure there are no running jobs in SQL Agent. Change all your
passwords on all logins that have delete access to the table or are in
the sysadmin group.
David Gugick
Imceda Software
www.imceda.com
|||"Dan B" <none@.none.com> wrote in message
news:u038qvI3EHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm sure this is a very broad question, but what could cause roughly
> 125,000 rows in one table to disappear over night. I'm running SQL server
> 7. I've done multiple virus scans...all clean...although mydoom was
> previously found and quarantined.
> Could someone give me at least a starting point on troubleshooting this.
> The same thing happened to the same table about 3 months ago. At that
time,
> I figured it was some strange fluke, but now that it has happened again,
it
> is rather disconcerting.
> Any help would be appreciated!!
> Thanks,
> Dan
>
Perhaps you could add a trigger to the table that would write diagnostic
information to a logging table, such as time-of-deletion and possibly
information about the user that the deletion SQL was running as.
Regards,
Simon
|||I have a evaluation copy of Lumigent Log Explorer, which I used to the
extent the evaluation allows. I was able to when the deleting took place,
and the SPID of the user or computer it came from, but apparently SPIDs
change so whoever had that number on the day the deletions occurred has a
different SPID now. It looks like the 125,000 rows where deleted within
about 10 minutes. People who use the application that is tied to the
database cannot delete anything. I just can't see how this
happened....again.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OSKCb0I3EHA.3000@.TK2MSFTNGP15.phx.gbl...
> Have you used a SQL log examination tool (Log P.I., Lumigent Log Explorer)
> to see if somebody issued a DELETE statement?
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Dan B" <none@.none.com> wrote in message
> news:u038qvI3EHA.2012@.TK2MSFTNGP15.phx.gbl...
> time,
> it
>
|||Lumigent Log Explorer will record logins and SPIDS so you can see extended
information about who and where the changes came from. If you know when it
is happening, you can run Profiler to grab complete information.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan B" <none@.none.com> wrote in message
news:%23O9sqnJ3EHA.3452@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> I have a evaluation copy of Lumigent Log Explorer, which I used to the
> extent the evaluation allows. I was able to when the deleting took place,
> and the SPID of the user or computer it came from, but apparently SPIDs
> change so whoever had that number on the day the deletions occurred has a
> different SPID now. It looks like the 125,000 rows where deleted within
> about 10 minutes. People who use the application that is tied to the
> database cannot delete anything. I just can't see how this
> happened....again.
>
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OSKCb0I3EHA.3000@.TK2MSFTNGP15.phx.gbl...
Explorer)[vbcol=seagreen]
this.[vbcol=seagreen]
again,
>
|||You could turn on event auditing. Also, is the system secured? You don't
need your app to connect to the system and delete information. MS Access,
SS Query Analyzer, MS Query, etc., etc.
Is the application using Windows Authentication or SQL Server logins?
Logins per user or one application login? Is the password well known? What
is the application security level on the server and within the database? Is
the SA account secured? Who knows the password?
I'd be running audit traces pretty heavily until you found out who has been
connecting to the system directly.
Sincerely,
Anthony Thomas

"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uNkFfqJ3EHA.3416@.TK2MSFTNGP09.phx.gbl...
Lumigent Log Explorer will record logins and SPIDS so you can see extended
information about who and where the changes came from. If you know when it
is happening, you can run Profiler to grab complete information.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan B" <none@.none.com> wrote in message
news:%23O9sqnJ3EHA.3452@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> I have a evaluation copy of Lumigent Log Explorer, which I used to the
> extent the evaluation allows. I was able to when the deleting took place,
> and the SPID of the user or computer it came from, but apparently SPIDs
> change so whoever had that number on the day the deletions occurred has a
> different SPID now. It looks like the 125,000 rows where deleted within
> about 10 minutes. People who use the application that is tied to the
> database cannot delete anything. I just can't see how this
> happened....again.
>
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OSKCb0I3EHA.3000@.TK2MSFTNGP15.phx.gbl...
Explorer)[vbcol=seagreen]
this.[vbcol=seagreen]
again,
>

data loss

Hi,

I have a big problem with a database in MS SQL SERVER 2000.

the rows into the some tables, for the second time, have been mixed between
they without appearing reason.

the application that uses the db is totally TRANSACTIONAL and they do not
exist query that they do not have clause WHERE.

The database is on a computer with NAS architecture.

I have this problem for the first time in 5 years of use of MS SQL SERVER

Can someone help me?

TIA"Stefano Ferrante" <s.ferrante@.staffmedia.it> wrote in message
news:c2flov$sqc$1@.lacerta.tiscalinet.it...
> Hi,
> I have a big problem with a database in MS SQL SERVER 2000.
> the rows into the some tables, for the second time, have been mixed
between
> they without appearing reason.

I have no idea what you mean above. Inserted rows are disappearing?

Or rows appear to be in a different order when you do a select?

Remember, a table is a collection of rows. There is NO order unless you use
an ORDER BY clause. It doesn't matter what order they are inserted in.

> the application that uses the db is totally TRANSACTIONAL and they do not
> exist query that they do not have clause WHERE.
> The database is on a computer with NAS architecture.

SQL Server does NOT normally support a NAS architecture. Are you sure
you don't mean SAN? That's more common.

> I have this problem for the first time in 5 years of use of MS SQL SERVER
> Can someone help me?

> TIA|||Stefano,
SQL Server is behaving normally if it is randomly sorting data. Data is not
supposed to be sorted any particular way and you are not supposed to write
code that relies on a particular sort. If you did write code that depends
on a particular sort, shame on you--but a view with an ORDER BY clause may
get you the sort you need. However--most of the time ORDER BY also means
the data isn't updateable so if the reason for the sort is to update things
you may be SOL.

"Stefano Ferrante" <s.ferrante@.staffmedia.it> wrote in message
news:c2flov$sqc$1@.lacerta.tiscalinet.it...
> Hi,
> I have a big problem with a database in MS SQL SERVER 2000.
> the rows into the some tables, for the second time, have been mixed
between
> they without appearing reason.
> the application that uses the db is totally TRANSACTIONAL and they do not
> exist query that they do not have clause WHERE.
> The database is on a computer with NAS architecture.
> I have this problem for the first time in 5 years of use of MS SQL SERVER
> Can someone help me?
> TIA

Data load: want to redirect extant rows in table to a file

We are working on a DataWarehouse app. The DW has been loaded wiith transactional data from the start of September. and we want refresh the DW with a full load from the original source. This full load wil consist largely of the same records that we loaded initially in the DW but some records will be new and others will have changed.

During the load I want to direct input records NOT already in the DW to a "mods" table and ignore those input records that alreayd exist in the DW. Can SSIS help out with this task?

TIA,

Barkingdog

Use a lookup task to compare the input with the existing table. Redirect errors to the "mods" table and ignore all records where the lookup succeeds.

Data hiding

Hi,
I wonder if anyone knows if it is possible to configure SQL Server 2000 so
that rows inserted in a table by user A are invisible for user B. User A and
B both insert and select into the same table but only sees their own rows. Is
there inbuild support for such "data hiding" or would I have to build myself?
//Magnus
http://vyaskn.tripod.com/row_level_s..._databases.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> Hi,
> I wonder if anyone knows if it is possible to configure SQL Server 2000 so
> that rows inserted in a table by user A are invisible for user B. User A and
> B both insert and select into the same table but only sees their own rows. Is
> there inbuild support for such "data hiding" or would I have to build myself?
> //Magnus
|||Thanks!
So SQL Server does not directly support VPD, like Oracle apparently does.
What ever solution I choose, I'll be forced to make changes in my client. Is
there a reason to why SQL Server lacks this feature?
/Magnus
"Tibor Karaszi" wrote:

> http://vyaskn.tripod.com/row_level_s..._databases.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
>
>
|||We're doing this now, and no client changes are needed. We removed SELECT
from the underlying tables, and created Views based on suser_sname as
suggested.
Jeff
"Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
message news:6F92D63D-EEE2-483A-8342-8E32A8AE1856@.microsoft.com...
> Thanks!
> So SQL Server does not directly support VPD, like Oracle apparently does.
> What ever solution I choose, I'll be forced to make changes in my client.
Is[vbcol=seagreen]
> there a reason to why SQL Server lacks this feature?
> /Magnus
> "Tibor Karaszi" wrote:
2000 so[vbcol=seagreen]
A and[vbcol=seagreen]
rows. Is[vbcol=seagreen]
myself?[vbcol=seagreen]
|||Hi Jeff!
Hmm, I don't really understand what you mean with removing SELECT from the
underlying tables. My problem is that my client doesn't do all db-access
through stored procedures. Have you been able to implement row level security
without forcing the client to use stored procedures? Then *please* tell me
how you do it!
/Magnus
"Jeff Dillon" wrote:

> We're doing this now, and no client changes are needed. We removed SELECT
> from the underlying tables, and created Views based on suser_sname as
> suggested.
> Jeff
> "Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
> message news:6F92D63D-EEE2-483A-8342-8E32A8AE1856@.microsoft.com...
> Is
> 2000 so
> A and
> rows. Is
> myself?
>
>
|||We're not using stored procedures either.You remove SELECT permissions from
the tables, then create Views that use suser_sname() in the Where clause,
and give SELECT permissions on the Views.
Jeff
"Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
message news:B69BAD33-2BFF-489E-83F7-4BDC88AD66EA@.microsoft.com...
> Hi Jeff!
> Hmm, I don't really understand what you mean with removing SELECT from the
> underlying tables. My problem is that my client doesn't do all db-access
> through stored procedures. Have you been able to implement row level
security[vbcol=seagreen]
> without forcing the client to use stored procedures? Then *please* tell me
> how you do it!
> /Magnus
> "Jeff Dillon" wrote:
SELECT[vbcol=seagreen]
does.[vbcol=seagreen]
client.[vbcol=seagreen]
http://vyaskn.tripod.com/row_level_s..._databases.htm[vbcol=seagreen]
User[vbcol=seagreen]
own[vbcol=seagreen]
build[vbcol=seagreen]

Data hiding

Hi,
I wonder if anyone knows if it is possible to configure SQL Server 2000 so
that rows inserted in a table by user A are invisible for user B. User A and
B both insert and select into the same table but only sees their own rows. Is
there inbuild support for such "data hiding" or would I have to build myself?
//Magnushttp://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> Hi,
> I wonder if anyone knows if it is possible to configure SQL Server 2000 so
> that rows inserted in a table by user A are invisible for user B. User A and
> B both insert and select into the same table but only sees their own rows. Is
> there inbuild support for such "data hiding" or would I have to build myself?
> //Magnus|||Thanks!
So SQL Server does not directly support VPD, like Oracle apparently does.
What ever solution I choose, I'll be forced to make changes in my client. Is
there a reason to why SQL Server lacks this feature?
/Magnus
"Tibor Karaszi" wrote:
> http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> > Hi,
> > I wonder if anyone knows if it is possible to configure SQL Server 2000 so
> > that rows inserted in a table by user A are invisible for user B. User A and
> > B both insert and select into the same table but only sees their own rows. Is
> > there inbuild support for such "data hiding" or would I have to build myself?
> >
> > //Magnus
>
>|||We're doing this now, and no client changes are needed. We removed SELECT
from the underlying tables, and created Views based on suser_sname as
suggested.
Jeff
"Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
message news:6F92D63D-EEE2-483A-8342-8E32A8AE1856@.microsoft.com...
> Thanks!
> So SQL Server does not directly support VPD, like Oracle apparently does.
> What ever solution I choose, I'll be forced to make changes in my client.
Is
> there a reason to why SQL Server lacks this feature?
> /Magnus
> "Tibor Karaszi" wrote:
> > http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> > news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> > > Hi,
> > > I wonder if anyone knows if it is possible to configure SQL Server
2000 so
> > > that rows inserted in a table by user A are invisible for user B. User
A and
> > > B both insert and select into the same table but only sees their own
rows. Is
> > > there inbuild support for such "data hiding" or would I have to build
myself?
> > >
> > > //Magnus
> >
> >
> >|||Hi Jeff!
Hmm, I don't really understand what you mean with removing SELECT from the
underlying tables. My problem is that my client doesn't do all db-access
through stored procedures. Have you been able to implement row level security
without forcing the client to use stored procedures? Then *please* tell me
how you do it!
/Magnus
"Jeff Dillon" wrote:
> We're doing this now, and no client changes are needed. We removed SELECT
> from the underlying tables, and created Views based on suser_sname as
> suggested.
> Jeff
> "Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
> message news:6F92D63D-EEE2-483A-8342-8E32A8AE1856@.microsoft.com...
> > Thanks!
> >
> > So SQL Server does not directly support VPD, like Oracle apparently does.
> > What ever solution I choose, I'll be forced to make changes in my client.
> Is
> > there a reason to why SQL Server lacks this feature?
> >
> > /Magnus
> >
> > "Tibor Karaszi" wrote:
> >
> > > http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> > > news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> > > > Hi,
> > > > I wonder if anyone knows if it is possible to configure SQL Server
> 2000 so
> > > > that rows inserted in a table by user A are invisible for user B. User
> A and
> > > > B both insert and select into the same table but only sees their own
> rows. Is
> > > > there inbuild support for such "data hiding" or would I have to build
> myself?
> > > >
> > > > //Magnus
> > >
> > >
> > >
>
>|||We're not using stored procedures either.You remove SELECT permissions from
the tables, then create Views that use suser_sname() in the Where clause,
and give SELECT permissions on the Views.
Jeff
"Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
message news:B69BAD33-2BFF-489E-83F7-4BDC88AD66EA@.microsoft.com...
> Hi Jeff!
> Hmm, I don't really understand what you mean with removing SELECT from the
> underlying tables. My problem is that my client doesn't do all db-access
> through stored procedures. Have you been able to implement row level
security
> without forcing the client to use stored procedures? Then *please* tell me
> how you do it!
> /Magnus
> "Jeff Dillon" wrote:
> > We're doing this now, and no client changes are needed. We removed
SELECT
> > from the underlying tables, and created Views based on suser_sname as
> > suggested.
> >
> > Jeff
> > "Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
> > message news:6F92D63D-EEE2-483A-8342-8E32A8AE1856@.microsoft.com...
> > > Thanks!
> > >
> > > So SQL Server does not directly support VPD, like Oracle apparently
does.
> > > What ever solution I choose, I'll be forced to make changes in my
client.
> > Is
> > > there a reason to why SQL Server lacks this feature?
> > >
> > > /Magnus
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > >
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> > > > news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> > > > > Hi,
> > > > > I wonder if anyone knows if it is possible to configure SQL Server
> > 2000 so
> > > > > that rows inserted in a table by user A are invisible for user B.
User
> > A and
> > > > > B both insert and select into the same table but only sees their
own
> > rows. Is
> > > > > there inbuild support for such "data hiding" or would I have to
build
> > myself?
> > > > >
> > > > > //Magnus
> > > >
> > > >
> > > >
> >
> >
> >

Data Flow Tasks - SSIS

I am using Recordset destination for SSIS. Flat file destination will
not work and Excel destination can not handle more that 65k rows and I
don't want to send my output to the server. Therefore after doing a
Merge join I want to view the results and Recordset seemed to be a
valid choice. The problem is how do I view the output? For other
components sometimes there are preview option. However this preview is
not consistent for all steps in the data flow tasks and sometimes
missing. My question is there any way to preview the Recordset
destination? Thanks.On May 25, 11:38 am, SB <othell...@.yahoo.com> wrote:
> I am using Recordset destination for SSIS. Flat file destination will
> not work and Excel destination can not handle more that 65k rows and I
> don't want to send my output to the server. Therefore after doing a
> Merge join I want to view the results and Recordset seemed to be a
> valid choice. The problem is how do I view the output? For other
> components sometimes there are preview option. However this preview is
> not consistent for all steps in the data flow tasks and sometimes
> missing. My question is there any way to preview the Recordset
> destination? Thanks.
Okay, instead of Recordset I have used DataReaderDestination and
created a Grid viewer to view the data. The problem is, it is very
slow and hangs after processing 96k out of 396k of records. (This is
one simple update involving a 2 table join that I can do in seconds in
the server) It is taking hours to develop the package and when it did
come back it is only showing 9k out of 396k of data in the grid. How
do I see all the data? I have tried attach/detach but still can't seem
to view all the data! How do I view all the data in the grid? Thanks.

Data Flow Tasks - SSIS

I am using Recordset destination for SSIS. Flat file destination will
not work and Excel destination can not handle more that 65k rows and I
don't want to send my output to the server. Therefore after doing a
Merge join I want to view the results and Recordset seemed to be a
valid choice. The problem is how do I view the output? For other
components sometimes there are preview option. However this preview is
not consistent for all steps in the data flow tasks and sometimes
missing. My question is there any way to preview the Recordset
destination? Thanks.
On May 25, 11:38 am, SB <othell...@.yahoo.com> wrote:
> I am using Recordset destination for SSIS. Flat file destination will
> not work and Excel destination can not handle more that 65k rows and I
> don't want to send my output to the server. Therefore after doing a
> Merge join I want to view the results and Recordset seemed to be a
> valid choice. The problem is how do I view the output? For other
> components sometimes there are preview option. However this preview is
> not consistent for all steps in the data flow tasks and sometimes
> missing. My question is there any way to preview the Recordset
> destination? Thanks.
Okay, instead of Recordset I have used DataReaderDestination and
created a Grid viewer to view the data. The problem is, it is very
slow and hangs after processing 96k out of 396k of records. (This is
one simple update involving a 2 table join that I can do in seconds in
the server) It is taking hours to develop the package and when it did
come back it is only showing 9k out of 396k of data in the grid. How
do I see all the data? I have tried attach/detach but still can't seem
to view all the data! How do I view all the data in the grid? Thanks.

Data Flow Tasks - SSIS

I am using Recordset destination for SSIS. Flat file destination will
not work and Excel destination can not handle more that 65k rows and I
don't want to send my output to the server. Therefore after doing a
Merge join I want to view the results and Recordset seemed to be a
valid choice. The problem is how do I view the output? For other
components sometimes there are preview option. However this preview is
not consistent for all steps in the data flow tasks and sometimes
missing. My question is there any way to preview the Recordset
destination? Thanks.On May 25, 11:38 am, SB <othell...@.yahoo.com> wrote:
> I am using Recordset destination for SSIS. Flat file destination will
> not work and Excel destination can not handle more that 65k rows and I
> don't want to send my output to the server. Therefore after doing a
> Merge join I want to view the results and Recordset seemed to be a
> valid choice. The problem is how do I view the output? For other
> components sometimes there are preview option. However this preview is
> not consistent for all steps in the data flow tasks and sometimes
> missing. My question is there any way to preview the Recordset
> destination? Thanks.
Okay, instead of Recordset I have used DataReaderDestination and
created a Grid viewer to view the data. The problem is, it is very
slow and hangs after processing 96k out of 396k of records. (This is
one simple update involving a 2 table join that I can do in seconds in
the server) It is taking hours to develop the package and when it did
come back it is only showing 9k out of 396k of data in the grid. How
do I see all the data? I have tried attach/detach but still can't seem
to view all the data! How do I view all the data in the grid? Thanks.

Data flow task reports different row count than actual rowcount

I have a data flow task that moves all the rows from 18 tables on a production server to a reporting services server. One table, which does not contain the most rows (about 650K rows) reports all the rows have been transferred. However, if I go in to the SQL Mgmt Studio and do a Select count(*) on the table, there are only 110k rows.

Has anyone else experienced this problem?

Thanks,

Nick Anzano

How does the data flow report that 650k rows have been transferred? Are you looking in the logs?

SSIS could certainly report that it has sent 650k to the database - what happens then is up to the database! Is the target database SQL Server? If so, use SQL Profiler to see what is happening when the rows are being sent to the server. Perhaps they are not being committed for some reason.

Donald Farmer