data that in one table column and not in the other table column

dear all

i have 2 tables, lets say table A and Table B

both tables has column ID

i wonder how can i find records that appears in B.ID and not appear in A.ID

what is the SQL command in this case?

Thnks alot

Please post T-SQL question in the Transact-SQL forum at:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

You can use the following query:

SELECT B.ID from B where B.ID NOT IN

(SELECT A.ID FROM A)

And this will return records from B which are not in A

data table fragmentation

we've got some large _data_ tables that could be fragmented (indexes
are already being defragged using dbcc indexdefrag). so,
1. how can i confirm this?
2. if they are, what's the best way to unfragment them?
i've been looking through old threads and see a lot of references to
dbcc shrinkfile, but that command doesn't seem to address the data
contiguity issue. the other option i've seen is to bcp the data out and
back in, but that seems a bit cumbersome.
thanks,
arthur
1)
DBCC SHOWCONTIG (For more details please refer to the BOL)
2)
DBCC INDEXDEFRAG
DBCC DBREINDEX
"arthur" <alangham@.gmail.com> wrote in message
news:1107784896.734177.96160@.l41g2000cwc.googlegro ups.com...
> we've got some large _data_ tables that could be fragmented (indexes
> are already being defragged using dbcc indexdefrag). so,
> 1. how can i confirm this?
> 2. if they are, what's the best way to unfragment them?
> i've been looking through old threads and see a lot of references to
> dbcc shrinkfile, but that command doesn't seem to address the data
> contiguity issue. the other option i've seen is to bcp the data out and
> back in, but that seems a bit cumbersome.
> thanks,
> arthur
>
|||No don't use shrinkfile as that will fragment them even more. You defrag
indexes with DBCC DBREINDEX or DBCC INDEXDEFRAG. The table itself should
have a clustered index on it so if you rebuild the clustered index you
rebuild the table itself. Have a look here:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1107784896.734177.96160@.l41g2000cwc.googlegro ups.com...
> we've got some large _data_ tables that could be fragmented (indexes
> are already being defragged using dbcc indexdefrag). so,
> 1. how can i confirm this?
> 2. if they are, what's the best way to unfragment them?
> i've been looking through old threads and see a lot of references to
> dbcc shrinkfile, but that command doesn't seem to address the data
> contiguity issue. the other option i've seen is to bcp the data out and
> back in, but that seems a bit cumbersome.
> thanks,
> arthur
>
|||use dbcc showcontig
check books on line for lots of examples and explanation of what the output
means.
fix via indexdefrag or dbReindex.
keep in mind that if a table does not have a clustered index, you will not
be able to defrag it.
cheers,
Greg Jackson
Portland, Oregon
|||as always, thanks!
arthur

data table fragmentation

we've got some large _data_ tables that could be fragmented (indexes
are already being defragged using dbcc indexdefrag). so,
1. how can i confirm this?
2. if they are, what's the best way to unfragment them?
i've been looking through old threads and see a lot of references to
dbcc shrinkfile, but that command doesn't seem to address the data
contiguity issue. the other option i've seen is to bcp the data out and
back in, but that seems a bit cumbersome.
thanks,
arthur1)
DBCC SHOWCONTIG (For more details please refer to the BOL)
2)
DBCC INDEXDEFRAG
DBCC DBREINDEX
"arthur" <alangham@.gmail.com> wrote in message
news:1107784896.734177.96160@.l41g2000cwc.googlegroups.com...
> we've got some large _data_ tables that could be fragmented (indexes
> are already being defragged using dbcc indexdefrag). so,
> 1. how can i confirm this?
> 2. if they are, what's the best way to unfragment them?
> i've been looking through old threads and see a lot of references to
> dbcc shrinkfile, but that command doesn't seem to address the data
> contiguity issue. the other option i've seen is to bcp the data out and
> back in, but that seems a bit cumbersome.
> thanks,
> arthur
>|||No don't use shrinkfile as that will fragment them even more. You defrag
indexes with DBCC DBREINDEX or DBCC INDEXDEFRAG. The table itself should
have a clustered index on it so if you rebuild the clustered index you
rebuild the table itself. Have a look here:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1107784896.734177.96160@.l41g2000cwc.googlegroups.com...
> we've got some large _data_ tables that could be fragmented (indexes
> are already being defragged using dbcc indexdefrag). so,
> 1. how can i confirm this?
> 2. if they are, what's the best way to unfragment them?
> i've been looking through old threads and see a lot of references to
> dbcc shrinkfile, but that command doesn't seem to address the data
> contiguity issue. the other option i've seen is to bcp the data out and
> back in, but that seems a bit cumbersome.
> thanks,
> arthur
>|||use dbcc showcontig
check books on line for lots of examples and explanation of what the output
means.
fix via indexdefrag or dbReindex.
keep in mind that if a table does not have a clustered index, you will not
be able to defrag it.
cheers,
Greg Jackson
Portland, Oregon|||as always, thanks!
arthur

data table fragmentation

we've got some large _data_ tables that could be fragmented (indexes
are already being defragged using dbcc indexdefrag). so,
1. how can i confirm this?
2. if they are, what's the best way to unfragment them?
i've been looking through old threads and see a lot of references to
dbcc shrinkfile, but that command doesn't seem to address the data
contiguity issue. the other option i've seen is to bcp the data out and
back in, but that seems a bit cumbersome.
thanks,
arthur1)
DBCC SHOWCONTIG (For more details please refer to the BOL)
2)
DBCC INDEXDEFRAG
DBCC DBREINDEX
"arthur" <alangham@.gmail.com> wrote in message
news:1107784896.734177.96160@.l41g2000cwc.googlegroups.com...
> we've got some large _data_ tables that could be fragmented (indexes
> are already being defragged using dbcc indexdefrag). so,
> 1. how can i confirm this?
> 2. if they are, what's the best way to unfragment them?
> i've been looking through old threads and see a lot of references to
> dbcc shrinkfile, but that command doesn't seem to address the data
> contiguity issue. the other option i've seen is to bcp the data out and
> back in, but that seems a bit cumbersome.
> thanks,
> arthur
>|||No don't use shrinkfile as that will fragment them even more. You defrag
indexes with DBCC DBREINDEX or DBCC INDEXDEFRAG. The table itself should
have a clustered index on it so if you rebuild the clustered index you
rebuild the table itself. Have a look here:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1107784896.734177.96160@.l41g2000cwc.googlegroups.com...
> we've got some large _data_ tables that could be fragmented (indexes
> are already being defragged using dbcc indexdefrag). so,
> 1. how can i confirm this?
> 2. if they are, what's the best way to unfragment them?
> i've been looking through old threads and see a lot of references to
> dbcc shrinkfile, but that command doesn't seem to address the data
> contiguity issue. the other option i've seen is to bcp the data out and
> back in, but that seems a bit cumbersome.
> thanks,
> arthur
>|||use dbcc showcontig
check books on line for lots of examples and explanation of what the output
means.
fix via indexdefrag or dbReindex.
keep in mind that if a table does not have a clustered index, you will not
be able to defrag it.
cheers,
Greg Jackson
Portland, Oregon|||as always, thanks!
arthur

Data SWITCH partition fails with primary key constraint Error

Hi champs,

I am trying to use SWITCH partitions from one fact table out to another identical table. On some tables this does not work as I get an ERROR conserning primary key constraints; is there a way around this without deleting the primary key constraint?

ERROR:

"SWITCH PARTITION 1 TO my_switch_out_table PARTITION 1 " failed with the following error: "ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table 'my_fact_table' contains primary key for constraint "

/Many thanks

Please post some sample DDL that demonstrates the problem. It will be easier to suggest the solution. You should also take a look at the BOL topic below:

http://msdn2.microsoft.com/en-gb/library/ms191160.aspx

It lists the table, index and constraint requirements for the switch to work.

|||

I have one table that has a two colums as a PK and this table has a PK constraint to one other table and other constraints to 5 other tables.

I've constructed the "OLD_DATA" table as a exact duplicate, including index, of the source table.

However I cannot create the exact same constraints on the destination table, as these already exists in the database.

when I run the following SWITCH, I get an error that

ALTER TABLE dbo.source_table_fact
SWITCH PARTITION 1
TO dbo.OLD_DATA_source_table_fact
PARTITION 1
go

[Execute SQL Task] Error: Executing the query "ALTER TABLE dbo.Ordination_fact SWITCH PARTITION 1 TO dbo.OLD_DATA_MYtable_fact PARTITION 1 " failed with the following error: "ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table 'dbo.MYtable_fact' contains primary key for constraint 'FK_fact_Mytable_fact'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This works fine on most of my fact tables but some SWITCHES will not work.

/Many thanks

|||

Here is the script
/* script start */
USE master
go
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'testdb')
BEGIN
ALTER DATABASE [testdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [testdb]
END
go
CREATE DATABASE [testdb]
GO
USE [testdb]
GO

CREATE PARTITION FUNCTION [RangeMonth] (datetime)
AS RANGE RIGHT FOR VALUES (
N'2006-12-01 00:00:00',
N'2007-01-01 00:00:00',
N'2007-02-01 00:00:00',
N'2007-03-01 00:00:00',
N'2007-04-01 00:00:00',
N'2007-05-01 00:00:00'
);
GO

CREATE PARTITION SCHEME [RangeM]
AS PARTITION [RangeMonth] all
TO ([Primary]);

CREATE TABLE dbo.test1
(
A uniqueidentifier NOT NULL,
B uniqueidentifier NULL,
C uniqueidentifier NULL,
ST Datetime NOT NULL,

CONSTRAINT test1_pk
PRIMARY KEY NONCLUSTERED (A ,ST) ON [RangeM] (ST),
) on [RangeM] (ST)
;

CREATE TABLE dbo.Lefttest1
(
A uniqueidentifier NOT NULL,
B uniqueidentifier NULL,
C uniqueidentifier NULL,
ST Datetime NOT NULL,

CONSTRAINT Lefttest1_pk
PRIMARY KEY NONCLUSTERED (A,ST) ON [Primary]
) ON [Primary]
;

CREATE TABLE dbo.test2
(
D uniqueidentifier NOT NULL,
E uniqueidentifier NOT NULL,
F uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL

CONSTRAINT test2_pk
PRIMARY KEY NONCLUSTERED (D,PartKey) ON [RangeM] ([PartKey]),
) ON [RangeM] ([PartKey])
go

CREATE TABLE dbo.Lefttest2
(
D uniqueidentifier NOT NULL,
E uniqueidentifier NOT NULL,
F uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL

CONSTRAINT Lefttest2_pk
PRIMARY KEY NONCLUSTERED (D,PartKey) ON [Primary],
) ON [Primary]

;

CREATE TABLE dbo.test3 (
G uniqueidentifier NOT NULL,
D uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL,
AnotherTime Datetime NOT NULL,

constraint test3_pk
primary key nonclustered (G),

constraint test3_D_PartKey_ref
foreign key (D,PartKey)
references test2(D,PartKey)
on delete cascade
)
;
GO
CREATE TABLE dbo.Lefttest3 (
G uniqueidentifier NOT NULL,
D uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL,
AnotherTime Datetime NOT NULL,

constraint Lefttest3_pk
primary key nonclustered (G) ON [Primary],

constraint Lefttest3_D_PartKey_ref
foreign key (D,PartKey)
references Lefttest2(D,PartKey)
on delete cascade
) ON [Primary]
;
GO

-- Try to switch out the first partition
-- on test1

ALTER TABLE test1
SWITCH PARTITION 1
TO Lefttest1;

-- Try to switch out the first partition
-- on test2
-- Fails with
-- Msg 4967, Level 16, State 1, Line 1
-- ALTER TABLE SWITCH statement failed.
-- SWITCH is not allowed because source table 'testdb.dbo.test2'
-- contains primary key for constraint 'test3_did_ref'.

ALTER TABLE test2
SWITCH PARTITION 1
TO Lefttest2;

Data Structure Advice

Hello,
I am in the process of redesigning a database for client. The advice I need
is how to design a set of tables to reflect what they current use a single
table for.
They have a table that has records that reflect hierarchal data. For
example, they have fields called Level 1, Level 2, Level 3, and Level 4. The
data in Level 1 is repeated many many times. As is Level 2, but not as often,
and so on. Here are examples of types of data found in this table:
- record 1 will have "building" in Level 1, and nothing in the other fields.
- record 2 will have "building" in Level 1 and "Manufacturing in Level 2,
and nothing in the rest of the fields.
- record 3 will have the same in Level 1 and Level 2 but include "office" in
Level 3.
I would like to design a data structure that will not only normalize the
data, but also allow for a dynamic number of levels. Currently, because the
structure is flat, the maximum number of levels is 4.
Thanks in advance!
Jack
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:E9151293-4B0A-4370-844B-53FCF1752ED6@.microsoft.com...
> I would like to design a data structure that will not only normalize the
> data, but also allow for a dynamic number of levels. Currently, because
the
> structure is flat, the maximum number of levels is 4.
Jack,
Depending on your definition of "dynamic", that may not be possible.
A normalized version of what you described would have one table for
Buildings, then a table for Floors (or whatever the level 2 is) with a
foreign key referencing the PK of the Buildings table, then a table for
Offices, with a FK referencing the PK of the Floors table, etc.
More levels could be added to the hierarchy at any time by simply adding
more tables (and, possibly, an addition FK column to the top level table if,
for instance, you had clusters of buildings at different locations).
If, on the other hand, your definition of "dynamic" is that the number of
levels could change at any time by someone inserting or deleting a row,
that's not possible with a normalized structure. Any structure that does
attempt to implement that kind of dynamism for different types will violate
1NF, as you'll be forced to represent differing types (e.g. a Building key
and a Floor key) in the same column. In addition, there's no real win here
because different entities require different attributes. If someone needs
to add, for instance, a Locations level to the hierarchy, that level will
not share the same attributes as Buildings, Floors, or Offices, and
therefore you will still be required to add a new table or new columns.
|||There is a topic in BOL titled "Expanding Hierarchies" that may be of
interest. Searching the NGs will also yield many discussions of
hierarchical data (and the issues with representing and querying the data).
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:E9151293-4B0A-4370-844B-53FCF1752ED6@.microsoft.com...
> Hello,
> I am in the process of redesigning a database for client. The advice I
need
> is how to design a set of tables to reflect what they current use a single
> table for.
> They have a table that has records that reflect hierarchal data. For
> example, they have fields called Level 1, Level 2, Level 3, and Level 4.
The
> data in Level 1 is repeated many many times. As is Level 2, but not as
often,
> and so on. Here are examples of types of data found in this table:
> - record 1 will have "building" in Level 1, and nothing in the other
fields.
> - record 2 will have "building" in Level 1 and "Manufacturing in Level 2,
> and nothing in the rest of the fields.
> - record 3 will have the same in Level 1 and Level 2 but include "office"
in
> Level 3.
> I would like to design a data structure that will not only normalize the
> data, but also allow for a dynamic number of levels. Currently, because
the
> structure is flat, the maximum number of levels is 4.
> Thanks in advance!
> Jack

Data Structure Advice

Hello,
I am in the process of redesigning a database for client. The advice I need
is how to design a set of tables to reflect what they current use a single
table for.
They have a table that has records that reflect hierarchal data. For
example, they have fields called Level 1, Level 2, Level 3, and Level 4. The
data in Level 1 is repeated many many times. As is Level 2, but not as often
,
and so on. Here are examples of types of data found in this table:
- record 1 will have "building" in Level 1, and nothing in the other fields.
- record 2 will have "building" in Level 1 and "Manufacturing in Level 2,
and nothing in the rest of the fields.
- record 3 will have the same in Level 1 and Level 2 but include "office" in
Level 3.
I would like to design a data structure that will not only normalize the
data, but also allow for a dynamic number of levels. Currently, because the
structure is flat, the maximum number of levels is 4.
Thanks in advance!
Jack"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:E9151293-4B0A-4370-844B-53FCF1752ED6@.microsoft.com...
> I would like to design a data structure that will not only normalize the
> data, but also allow for a dynamic number of levels. Currently, because
the
> structure is flat, the maximum number of levels is 4.
Jack,
Depending on your definition of "dynamic", that may not be possible.
A normalized version of what you described would have one table for
Buildings, then a table for Floors (or whatever the level 2 is) with a
foreign key referencing the PK of the Buildings table, then a table for
Offices, with a FK referencing the PK of the Floors table, etc.
More levels could be added to the hierarchy at any time by simply adding
more tables (and, possibly, an addition FK column to the top level table if,
for instance, you had clusters of buildings at different locations).
If, on the other hand, your definition of "dynamic" is that the number of
levels could change at any time by someone inserting or deleting a row,
that's not possible with a normalized structure. Any structure that does
attempt to implement that kind of dynamism for different types will violate
1NF, as you'll be forced to represent differing types (e.g. a Building key
and a Floor key) in the same column. In addition, there's no real win here
because different entities require different attributes. If someone needs
to add, for instance, a Locations level to the hierarchy, that level will
not share the same attributes as Buildings, Floors, or Offices, and
therefore you will still be required to add a new table or new columns.|||There is a topic in BOL titled "Expanding Hierarchies" that may be of
interest. Searching the NGs will also yield many discussions of
hierarchical data (and the issues with representing and querying the data).
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:E9151293-4B0A-4370-844B-53FCF1752ED6@.microsoft.com...
> Hello,
> I am in the process of redesigning a database for client. The advice I
need
> is how to design a set of tables to reflect what they current use a single
> table for.
> They have a table that has records that reflect hierarchal data. For
> example, they have fields called Level 1, Level 2, Level 3, and Level 4.
The
> data in Level 1 is repeated many many times. As is Level 2, but not as
often,
> and so on. Here are examples of types of data found in this table:
> - record 1 will have "building" in Level 1, and nothing in the other
fields.
> - record 2 will have "building" in Level 1 and "Manufacturing in Level 2,
> and nothing in the rest of the fields.
> - record 3 will have the same in Level 1 and Level 2 but include "office"
in
> Level 3.
> I would like to design a data structure that will not only normalize the
> data, but also allow for a dynamic number of levels. Currently, because
the
> structure is flat, the maximum number of levels is 4.
> Thanks in advance!
> Jacksql

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 View and Report Model

Guys,

I created a Data Source View that uses two different Data Source connections to two different databases. I was able to drop in the tables in the data source view designer from the two databases, but when I try to create the Report Model based on that data source view, it's complaining that it can't find table in the non-primary data source from the data source view.

Here is the error I'm getting:

An error occurred while executing a command.

Message: Invalid object name 'dbo.[tablename]'

Command:

SELECT COUNT(*) FROM [dbo].[tablename] t

Why can't it find the database to connect to since the data source view is already creating using two different databases. No problem in the data source view, but when it actually becomes a model, it complains. How come it can't find one of the tables in a different database?

Please help and any help is appreciated.

Enkh.

semantic queries currently do not support multiple data sources. you can create named query that would reference table from another database using db.schema.tablename notation.

Data Source For Report Model Project

When I tried to generate ad-hoc reports with Microsoft reporting services
2005 (april CTP), it generally ask for the tables and views. Is there a way
that I can just put in a stored procedure and use the results as a data
feeder for the report model? Thanks.I don't think this is supported out of the box
You can however create a named query as part of the data source view
"Geek" wrote:
> When I tried to generate ad-hoc reports with Microsoft reporting services
> 2005 (april CTP), it generally ask for the tables and views. Is there a way
> that I can just put in a stored procedure and use the results as a data
> feeder for the report model? Thanks.sql

Data source file name has a timestamp

I have 2 years worth of data that are stored in individual .dbf files for each day. Is there a way to 1 quickly import all of these tables into one and 2. move the timestamp from the file name to a date column?

Any help would be greatly appreciated

The For Each Loop has a file enumertor. You end up with the filename in a variable. You can then use this variable in two ways -

1 Use it in an expression for a connection, so that the connection string is changed to pick up the new file. You would use this connection for your source component, in a Data Flow within the loop itself.

2 Add a derived column transform in your data flow, and use the variable again, grab the date part and pass out in a new column.

Data Sliles or Filter or seperate Fact Tables for ourCube Partitio

Hi,
we have cubes partitioned on the time dimension. The majority of data that
comes in if for the last 4 days or so. The new data is loaded into a seperate
fact table, and loaded into a partition. This partition is then merged into
the current month partition.
However, we can have data that comes in which is a month or longer old. So
would be merged into the incorrect partition. And would remain there until a
full reprocess of the Fact tables are cube partitiions.
I understand that when data slices are defined for a partition, that AS2000
will only visit the partitions that contain the relevent data. What happens
if we use data slices in our situation?
If the data is in the incorrect partition, as a result of merging, is it
possible that the data is missed out, depending on the query? Or would the
merging not be allowed to take place?
Would we want to define data slices for all partitions, except for the
current month ?
Or would we want to define a filter, and rather than a build and merge, do
an incremental build against the cube ?
We are really looking for the quickest load time possible, while having
correct data of course.
Thanks in advance for any advice and help.
Is this for AS 2000 or 2005? In 2000, slices that you defined are used for
querying. In 2005, slices for MOLAP partitions are detected automatically.
For ROLAP partitions the slice you specify will be used to eliminate
partitions from queries.
But besides that, only your latest partition would have "incorrect" data,
right? So just don't set a slice for that last partition -- it would mean
that this partition would be unnecessarily scanned for some queries, but it
should be a fairly small partition anyway...
Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:370E22EC-6304-49F2-B01D-812B37B2E1EF@.microsoft.com...
> Hi,
> we have cubes partitioned on the time dimension. The majority of data that
> comes in if for the last 4 days or so. The new data is loaded into a
> seperate
> fact table, and loaded into a partition. This partition is then merged
> into
> the current month partition.
> However, we can have data that comes in which is a month or longer old. So
> would be merged into the incorrect partition. And would remain there until
> a
> full reprocess of the Fact tables are cube partitiions.
> I understand that when data slices are defined for a partition, that
> AS2000
> will only visit the partitions that contain the relevent data. What
> happens
> if we use data slices in our situation?
> If the data is in the incorrect partition, as a result of merging, is it
> possible that the data is missed out, depending on the query? Or would the
> merging not be allowed to take place?
> Would we want to define data slices for all partitions, except for the
> current month ?
> Or would we want to define a filter, and rather than a build and merge, do
> an incremental build against the cube ?
> We are really looking for the quickest load time possible, while having
> correct data of course.
> Thanks in advance for any advice and help.
>

Data Sliles or Filter or seperate Fact Tables for ourCube Partitio

Hi,
we have cubes partitioned on the time dimension. The majority of data that
comes in if for the last 4 days or so. The new data is loaded into a seperat
e
fact table, and loaded into a partition. This partition is then merged into
the current month partition.
However, we can have data that comes in which is a month or longer old. So
would be merged into the incorrect partition. And would remain there until a
full reprocess of the Fact tables are cube partitiions.
I understand that when data slices are defined for a partition, that AS2000
will only visit the partitions that contain the relevent data. What happens
if we use data slices in our situation?
If the data is in the incorrect partition, as a result of merging, is it
possible that the data is missed out, depending on the query? Or would the
merging not be allowed to take place?
Would we want to define data slices for all partitions, except for the
current month ?
Or would we want to define a filter, and rather than a build and merge, do
an incremental build against the cube ?
We are really looking for the quickest load time possible, while having
correct data of course.
Thanks in advance for any advice and help.Is this for AS 2000 or 2005? In 2000, slices that you defined are used for
querying. In 2005, slices for MOLAP partitions are detected automatically.
For ROLAP partitions the slice you specify will be used to eliminate
partitions from queries.
But besides that, only your latest partition would have "incorrect" data,
right? So just don't set a slice for that last partition -- it would mean
that this partition would be unnecessarily scanned for some queries, but it
should be a fairly small partition anyway...
Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:370E22EC-6304-49F2-B01D-812B37B2E1EF@.microsoft.com...
> Hi,
> we have cubes partitioned on the time dimension. The majority of data that
> comes in if for the last 4 days or so. The new data is loaded into a
> seperate
> fact table, and loaded into a partition. This partition is then merged
> into
> the current month partition.
> However, we can have data that comes in which is a month or longer old. So
> would be merged into the incorrect partition. And would remain there until
> a
> full reprocess of the Fact tables are cube partitiions.
> I understand that when data slices are defined for a partition, that
> AS2000
> will only visit the partitions that contain the relevent data. What
> happens
> if we use data slices in our situation?
> If the data is in the incorrect partition, as a result of merging, is it
> possible that the data is missed out, depending on the query? Or would the
> merging not be allowed to take place?
> Would we want to define data slices for all partitions, except for the
> current month ?
> Or would we want to define a filter, and rather than a build and merge, do
> an incremental build against the cube ?
> We are really looking for the quickest load time possible, while having
> correct data of course.
> Thanks in advance for any advice and help.
>sql

Data sequence in tables, please help

I'm new to sql server 2005. Previously I used Access. I've noticed that in sql server when a record is inserted into the database table, the data does not get entered sequentially. I looked at the id column and saw that the order numbers are mixed up. Is this something that is common in sql server, and how can I change this. I need the data to be ordered in the order that it was inserted into the database.

Can someone please help.

SQLServer does not enforce any order of data for storage purposes.

If you wish to have the data presented in a sorted, or ordered, scheme, you MUST use an ORDER BY on the query.

IF you have an IDENTITY field (Column) in the table, it will sequentially number all rows that are added to the table. Verify that your table has the [ID] column defined as IDENTITY.

Also, you may wish to review the list of reserved words to be sure that you do not name tables and columns with words that have special meaning for SQL Server. You may also wish to avoid common words for your Table and Column names in order to reduce confustion. You will have to use square brackets anytime your table or column names violate the reserved word list.

Refer to Books Online, Topic: 'Reserved Words [SQL Server]'

|||

it seems like your table is a heap. ie. there is no clustered index. in that case the data will be in the order how u have inserted. In a relational database it does not really matter since while selecting you can order the records set. But first of all you need to define a primary key for this table and you should create a clustered index(not necessary to create on PK) .

Madhu

Data search

I use SQL server to connect to a HUGE odbc compliant db with 1000's of tables. The db contains tons of data. I am looking for specific data elements. Is there any way to search the entire db for a data value?

Thanks Marcie

I'm sorry, I'm not sure I understand the question. Do you mean besides using standard SQL?sql

Data Report

I am creating a report using the Data Report in VB. The data is taken from two tables with a certain date criterea, that is, between two
dates. The date is formatted as MMMM yyyy. My problem is that the layout of the report does not match the data from the table. To make
my problem clear, the report layout is as follows:

Month1 Month2 Month3
Name W1 W2 W3 W4 W5 W1 W2 W3 W4 W5 W1 W2 W3 W4 W5

where W1, W2 etc are the weeks of the month.

Now, let me give you the structure of my table. It is as follows:

Name Month W1 W2 W3 W4 W5

The Query is as follows:

"SELECT DISTINCT EMPNAME, AH1, AH2, AH3, AH4, AH5 FROM STAFFMASTER, STAFFHRS WHERE " & _
"STAFFMASTER.EMPCODE=STAFFHRS.EMPCODE AND STAFFHRS.[PROJECT]='" & cbo1.Text & _
"' AND STAFFHRS.[MONTH & YEAR]>=#" & Format(DTP1.Value, "MMMM yyyy") & _
"# AND STAFFHRS.[MONTH & YEAR]<=#" & Format(DTP2.Value, "MMMM yyyy") & "#"

Now, how will I distribute the data in my report? One important thing, I am building dynamic data report. Please help.

ThanksLooks like it's more of a SQL issue and not Crystal.

Converting rows into columns is a complicated process and needs either a stored procedure or more than one sql statement.. it may need a temp table or dummy table to collect data first and then group it accordingly.

The fast forward solution is this :

.
.
.
Select space(30) "my1", space(30) "my2" , space(30) "my3", h.monthyear "my4", space(30)"my5", space(30) "my6", space(30) "my7",
space(30) "my7", space(30) "my8", space(30) "my9", space(30) "my10", space(30) "my11", space(30) "my12",
sum(h.ah1) april_Ah1, sum(h.ah2) april_Ah2, sum(h.ah3) april_Ah3, sum(h.ah4) april_Ah4, sum(h.ah5) april_Ah5,
0 may_Ah1, 0 may_Ah2, 0 may_Ah3, 0 may_Ah4, 0 may_Ah5, m.empcode
From staffmaster m, staffhrs h
where m.empcode = h.empcode
group by m.empcode, h.monthyear
having h.monthyear = "April 2004" and between (h.monthyear, start_date, end_date)
union all
Select space(30) "my1", space(30) "my2" , space(30) "my3", space(30) "my4", h.monthyear "my5", space(30) "my6", space(30) "my7",
space(30) "my7", space(30) "my8", space(30) "my9", space(30) "my10", space(30) "my11", space(30) "my12",
sum(h.ah1) april_Ah1, sum(h.ah2) april_Ah2, sum(h.ah3) april_Ah3, sum(h.ah4) april_Ah4, sum(h.ah5) april_Ah5,
sum(h.ah1) may_Ah1, sum(h.ah2) may_Ah2, sum(h.ah3) may_Ah3, sum(h.ah4) may_Ah4, sum(h.ah5) may_Ah5,m.empcode
From staffmaster m, staffhrs h
where m.empcode = h.empcode
group by m.empcode, h.monthyear
having h.monthyear = "May 2004" and between (h.monthyear, start_date, end_date)
.
.
.
for each month write a separate sql and just do union all that's going to give you data in column format : like this

my1 my2 my3 my4 my5 my6 my7 my7 my8 my9 my10 my11 my12 april_Ah1 april_Ah2 april_Ah3 april_Ah4 april_Ah5 may_Ah1 may_Ah2 may_Ah3 may_Ah4 may_Ah5 empcode
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -------
April 2004 40 80 120 160 200 0 0 0 0 0 001
May 2004 40 80 120 160 200 40 80 120 160 200 001
May 2004 40 80 120 160 200 40 80 120 160 200 002
May 2004 40 80 120 160 200 40 80 120 160 200 003

Finally in crystal report you just have to group it based on employee code so that per empoyee there would be only one record in your report.

Thanks
Guru
:wave:|||Ah! This is exactly what I wanted. But what is 'space' in the SQL. And also my1, my2, etc. Also, my table structure is as follows:

Table1:

Code
Month
AH1
AH2
AH3
AH4
AH5

Table2:

Code
Name

The retrieved recordset will be as follows:

Name
Month
AH1
AH2
AH3
AH4
AH5

The report I wish to display is as follows:

Name Month1 AH1 AH2 AH3 AH4 AH5 Month2 AH2 AH3 AH4 AH5

I hope the SQL that you mentioned will display as the above.

Thanks

Thanks|||Hi,

I would like to now what database are you using. I have given you sample of MSSql server ?

Also what do u store in month field ? Just give an actual example because you want to retrieve it based on dates ? Also are you trying to retrieve only one yrs. data or multiple yrs ?

Thanks
Guru
:confused:|||Just wondering how can you display 72 fields in one line. For each month you need to display this info in one line is that true ?

Are you trying to print list of all employees and man hrs worked ?

Thanks

:(|||Originally posted by dilemma
Hi,

I would like to now what database are you using. I have given you sample of MSSql server ?

Also what do u store in month field ? Just give an actual example because you want to retrieve it based on dates ? Also are you trying to retrieve only one yrs. data or multiple yrs ?

Thanks
Guru
:confused:

I use MS Access.

In month field I store the date formatted as "MMMM yyyy".

Example:

Name Month_Year W1 W2 W3 W4 W5
John May 2004 40 40 40 40 24

The user wants to display in the record, the data of the month he picks from a DateTimePicker control and two months ahead of that month. So, for example, if he picks December 2004, then the data of January 2005 and February 2005 should also be displayed.

Thanks|||Originally posted by dilemma
Just wondering how can you display 72 fields in one line. For each month you need to display this info in one line is that true ?

Are you trying to print list of all employees and man hrs worked ?

Thanks

:(

Not 72 fields. There will be only 19 fields, that is, the Name field is displayed only once.

That's right. All employees working on a particular project and the man hours assigned to them. I am attaching a picture of the report how it should be displayed.

Thanks|||Looking at the complexity of your problem If I were you I would do this. Assuming you are planning to create report in Crystal, create report with 3 sub reports for each month. 1 for Current month. 2 for II'nd month. 3 for III'rd month. Add these all 3 reports in one report as a subreport. They each will have different where criteria . eg the current month report would have criteria to fetch only current months data and similarly II'nd one retrieves only II'nd months data and III'rd guy retrieves III'rd months data.

Thanks
:wave:|||That sounds interesting. I am totally new to Crystal Reports. Can you help me on how to create the crystal subreport?

Thanks|||It's pretty easy. Create 3 reports to retrieve records for each month based on appropriate criteria. ONce done create 4'th report and add these 3 reports as subreports using crystal's subreporting features. Input parameter of these reports would be date.

Creation of reports in Crystal is also fairly easy. Just go through help. It has pretty good information.

Thanks|||Thanks for your reply.

Mine is a multiuser system. The database is going to be in the network. So I perfer using the .Dsr as report and not the .rpt. Also, should I use the wizard or should I go for runtime reports?

Thanks.|||I am sorry. I do not know what .Dsr is ? You could use wizard to create crystal report if you are a beginner. I didn't get last part runtime reports ? Doesn't matter even if you have multiuser environment. There is a parameter to open report in exclusive mode or temp copy mode. In your case you would be opening your report in temporary mode. That's it.

Thanks
Sanjay|||.Drs is a report created using the RDC. I have read some articles on Crystal Reporting that the .rpt files increases the size of the compiled program.

Is the parameter to open the report in exclusive mode set inside the crystal report application or is set within the vb code?

Thanks|||There is no doubt that .rpt files increase the size of the package. To open report in temp mode or exclusive mode you need to add something called OpenReport. The syntax is :

Function OpenReport (pFileName As String, [OpenMethod]) As Report

Where,
The OpenMethod parameter can take the values "0" (open by default) or "1" (open temporary copy).

If you do not provide this parameter the report is opened exclusively and it cannot be opened a second time

Thanks|||Ah! That is a very good information. So you suggest that I use .rpt? Thanks for the info.

Thankssql

Data Relationship & SQL data adapter

Hi

I was trying to fill SqlDataAdapter from a " View" with inner join of two tables. When I call the update method of the data adapter it is failing as it cannot generate automatic SQL for multiple tables in my view. Writing own SQL script for update is one way out but it is tedious process.

Is there any other work around to do this?

Can I create a view of two joined data sets on some condition (Just like creating a view in database) in the memory.

Cheers,

Sudath Attanayake.One option you might look into which is extremely common among database programmers, is to only do updates through stored procedures. One of the main reason is security. If you disalow any updates to hapen to the tables or views, then you can control who can update what by simply controlling the permission to execute the individual stored procs, or special case what data can be updated.

data reader ?

is it possible to use a data reader to read from 2 tables with 1 store procedure(sp)?

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

ex:

create sp1

as

select * from tbl1

select * from tbl2

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

how can i use a data reader to read the items from tbl2?

If you have SqlServer 2005 (inc Express), you can use MARS -MultipleActiveResultSets. It's only available with SqlClient, so won't work with Oracle, or MySQL etc.|||

One of the restrictions of ADO.NET and datreaders is that although SQL can return multiple datasets, ADO.NET can only read the first.

|||

Use a DataSet in this instance to retrieve your results. Then, you can retrieve the items out of the two tables that will be returned.

|||

It looks like you can support mulitple resultsets with a DataReader as well although I've always used a DataSet in this instance. See the following article.

http://msdn.microsoft.com/msdnmag/issues/04/06/DataPoints/#S2

|||

TATWORTH:

One of the restrictions of ADO.NET and datreaders is that although SQL can return multiple datasets, ADO.NET can only read the first.

As I understand it, that's only the case when using the SqlDataSource control, although I can't remember whether it can only hold the first resultset, or the last....

sql

Data Read-Consistency During Update

Hi folks,

I have 3 tables of related data in SQL 2000/2005 that receive periodic adds/updates every 30 seconds.

I have enabled transactions on all updates, is anything else needed to ensure data consistency during reads in a high # of fetches per second situation?

Thanks,

john

Edit:

Clarification -- I just need the data to be correctly displayed to all users.

Hi,

I am not sure what do you mean by consistency. You may need to change transaction isolation level. Setting "serializable" level ensures complete consistency, but may impact performance of your server.

-yuriy
http://weblogs.asp.net/ysolodkyy

|||

Thanks, that's what I was describing.

Data quality tools

Guys,

I'm trying to implement some of the data quality techniques. I use staging tables in my ETL process.

What I'd like to do is:

- check for required fields - make sure all required fields have values in all records

- check for duplicates

- check for data formats (line phone#, zip code etc)

Obviously all of this can be in SQL.

My question is what would be the best approach in SSIS to do it?

Dima.

Dima S wrote:

- check for required fields - make sure all required fields have values in all records

The Derived Column component has an ISNULL() function.

Dima S wrote:

- check for duplicates

Aggregate transform will give you all records that contains duplicates. COUNT over all the columns in which you want to check for duplicates.

Dima S wrote:

- check for data formats (line phone#, zip code etc)

Regular expressions would be a good fit here: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1754.aspx

-Jamie

|||

Thanks!