Binding Different DataSets to One Table.

I have a situation where i am binding a Dataset Dset1 to a table.
, I also have a Dset2 which i am not binding. Now as the Rows gets
rendered depending on Dset1 i also want to populate a value from Dset2
(2nd Datset ) in a cell for a particular Row.
How do i do that?
Any help.
ThanksYou cannot bind a data region (in this case a table) to more than one
dataset. You should re-design the report so that it has one dataset that has
a query returning both the data currently in Dset1 and in Dset2. This will
most likely mean writing a JOIN SQL query.
Charles Kangai, MCDBA, MCT
"Rahul" wrote:
> I have a situation where i am binding a Dataset Dset1 to a table.
> , I also have a Dset2 which i am not binding. Now as the Rows gets
> rendered depending on Dset1 i also want to populate a value from Dset2
> (2nd Datset ) in a cell for a particular Row.
>
> How do i do that?
> Any help.
>
> Thanks
>|||That is easier to to in a store proc, identifying to temp tables and
populating your row in there - and THEN returning one data-set back to the
report server.
"Rahul" wrote:
> I have a situation where i am binding a Dataset Dset1 to a table.
> , I also have a Dset2 which i am not binding. Now as the Rows gets
> rendered depending on Dset1 i also want to populate a value from Dset2
> (2nd Datset ) in a cell for a particular Row.
>
> How do i do that?
> Any help.
>
> Thanks
>

Between date selection

Hi,

I have the following situation.

I want to create reports with a start date parameter and an end date parameter

In my query i use "where date between @.startdate and @.enddate"

i configure my parameters so i get a nice calendar when i generate the report.

the problem is when i select for example a date starting 1 april 2007 and enddate 10 april 2007,

the result in my report gives me only the data from 1 until 9 april and not until 10.

in my database the date is stored as a date time (10/04/2007 17:25:30).

Any suggestion how i can solve my problem?

Greetings

Vinnie

Hello Vinnie,

The problem is that your database stores the time with your date and when you select a date in the date picker, it doesn't have a tme, so anything after midnight gets excluded. Try this as your where clause:

where date >= @.startdate and date < dateadd(d, 1, @.enddate)

Hope this helps.

Jarret

|||

Hi Vinnie,

When I come across this I normally use a conversion in my query:

where convert(varchar(25),date,101) between @.startdate and @.enddate

The conversion eliminates the time from the database field and will pull back all rows with the date portion between the start and end dates.

Simone

|||

Do you have a date picker control on your reporting website, so that instead of the user having to manually input the date in the correct format, they are given a control to pick the date ranges?

(Like datetimepicker control in C#)

Please let me know as that is what I need to do.

|||

Yes I do. You need to change the data type of the parameter in the Report to datetime. Go to "Report" then "Report Parameters" Select the parameter from the list on the left and change the data type to DateTime. This should automatically give the date control.

Simone

Better Practices Wanted for Cascading Inserts of Hierarchical Data from Staging Tables

I apologize if this has been asked, but I can't find a complete answer.

We have a situation with parent/child tables which have an identity column as their PK. We need to be able to insert into the live tables from staging tables. The data in the staging tables are related via a surrogate key.

I have found the OUTPUT clause, but that can only refer to columns of the actual table (since there is no FROM clause in an INSERT). Our current best solution to this problem involves adding bogus "staging" columns to the destination tables, and removing them after we've inserted everything from staging. This is an unattractive solution to say the least.

I'll give an example that mirrors our actual solution, and ask if anyone has a better solution?

-

Code Snippet

CREATE TABLE [dbo].[TABLE_A](

[ID] [int] IDENTITY(1,1) NOT NULL,

[DATA] [nchar](10) NOT NULL,

[STAGING_COLUMN] [bigint] NULL,

CONSTRAINT [PK_TABLE_A] PRIMARY KEY ([ID] ASC)

)

GO

CREATE TABLE [dbo].[TABLE_B](

[ID] [int] IDENTITY(1,1) NOT NULL,

[A_ID] [int] NOT NULL,

[DATA] [nchar](10) NOT NULL,

[STAGING_COLUMN] [bigint] NULL,

CONSTRAINT [PK_TABLE_B] PRIMARY KEY ([ID] ASC)

)

GO

ALTER TABLE [dbo].[TABLE_B]

ADD CONSTRAINT [FK_TABLE_A_TABLE_B] FOREIGN KEY([A_ID]) REFERENCES [dbo].[TABLE_A] ([ID])

GO

CREATE TABLE [dbo].[STAGE_TABLE_A](

[A_Key] [bigint] NOT NULL,

[DATA] [nchar](10) NOT NULL

)

GO

CREATE TABLE [dbo].[STAGE_TABLE_B](

[B_Key] [bigint] NOT NULL,

[DATA] [nchar](10) NOT NULL,

[A_Key] [bigint] NOT NULL

)

GO

The STAGING_COLUMN columns are the ones that will be added before, and dropped after.

Code Snippet

DECLARE @.TABLE_A_MAP TABLE (

A_ID INT,

A_Key BIGINT

)

INSERT INTO TABLE_A (DATA, STAGING_COLUMN)

OUTPUT INSERTED.ID, INSERTED.STAGING_COLUMN INTO @.TABLE_A_MAP

SELECT DATA, A_Key FROM STAGE_TABLE_A

INSERT INTO TABLE_B (A_ID, DATA)

SELECT TAM.A_ID, STB.DATA

FROM STAGE_TABLE_B STB INNER JOIN @.TABLE_A_MAP TAM ON TAM.A_Key = STB.A_Key

This seems to work, but I'd really like another alternative. Even though this is happening when nobody else is using the database, I cringe at the thought of adding and removing columns just to make this work.

Here are a few of my constraints:

The above is a simplification of the actual problem. The actual problem goes about five levels deep (hence the B_Key in STAGE_TABLE_B). At the top level, our larger customer will have 100,000 rows to insert. Each level will average 3 times as many rows as the next higher level, so we're talking about real volumes here.

This has to finish over the course of a weekend.

This has to be delivered to QA this Friday

Thanks for any help or insight.

John,

After trigger is perfect for this.

e.g.

Code Snippet

create trigger tr_tb_a on table_a

for insert

as

if @.@.rowcount = 0 return;

insert table_b(a_id,data)

select i.id, s.data

from stage_table_b s join inserted i on s.a_key=i.staging_column

go

|||

Thanks, I just extended this to three tables, and it worked.

This is sort of a "pull" model as opposed the the "push" model we were using.

I've sent this to our DB expert, and will post the result here.

best way to add column not null

Hi.

I've read up on this, and have something that works, but I was wondering if
there is anything I'm overlooking with this.

Situation is:

I have a bunch of tables.. I need to modify table2 as part of an upgrade of a
database schema.

I am using T-SQL scripts to do the trick which I'm writing myself.

I need to add a new varchar(8) column that is not null to the primary key.
I have a default I would like to use for the initial ddl modification.
I want to get rid of the default after the modification is complete, but leave
the column not null for future operations.
..

(Some if the code I'm using I took from one of Erlands posts.. hope I'm not
abusing it).
Here is the code I'm using now.. it basically adds the column 'institution_id'
as not null along with a default.
Then I jump through a couple of hoops trying to get rid of the default.
Finally I setup the primary key again.

I can only feel I'm supposed to be maybe using a constraint column with a name
to do this easier/more properly.

set @.dynamicsql = ' alter table institution_xref add institution_id
varchar(60) not null default ''' + @.default_institution_id + ''' '
EXEC (@.dynamicsql)
set @.dynamicsql = ' alter table institution_xref alter column
institution_id varchar(60) not null '
EXEC (@.dynamicsql)
select @.institution_iddefault = object_name(cdefault) from syscolumns
where id = object_id('institution_xref') and name = 'institution_id'
exec(' alter table institution_xref drop constraint ' +
@.institution_iddefault)
set @.dynamicsql = ' alter table institution_xref drop constraint
institution_xref_pk '
EXEC (@.dynamicsql)
set @.dynamicsql = ' alter table institution_xref with nocheck add
constraint institution_xref_pk primary key clustered (originalcode,
institution_id) '
EXEC (@.dynamicsql)

thanks
Jeff
Jeff KishJeff Kish (jeff.kish@.mro.com) writes:

Quote:

Originally Posted by

I need to add a new varchar(8) column that is not null to the primary key.

Ouch! That can be paintful, at least if there are foreign keys
referencing the table.

Quote:

Originally Posted by

abusing it).
Here is the code I'm using now.. it basically adds the column
'institution_id'
as not null along with a default.
Then I jump through a couple of hoops trying to get rid of the default.
Finally I setup the primary key again.
>
I can only feel I'm supposed to be maybe using a constraint column with
a name to do this easier/more properly.

Yes, if you name the constraint it's a little easier:

Quote:

Originally Posted by

set @.dynamicsql = ' alter table institution_xref add institution_id
varchar(60) not null default ''' + @.default_institution_id + ''' '

...not null CONSTRAINT my_temp_default DEFAULT ''' ...

Quote:

Originally Posted by

EXEC (@.dynamicsql)

ALTER TABLE ... DROP CONSTRAINT my_temp_default

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Tue, 5 Jun 2007 22:28:21 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Jeff Kish (jeff.kish@.mro.com) writes:

Quote:

Originally Posted by

>I need to add a new varchar(8) column that is not null to the primary key.

>
>Ouch! That can be paintful, at least if there are foreign keys
>referencing the table.
>

Quote:

Originally Posted by

>abusing it).
>Here is the code I'm using now.. it basically adds the column
>'institution_id'
>as not null along with a default.
>Then I jump through a couple of hoops trying to get rid of the default.
>Finally I setup the primary key again.
>>
>I can only feel I'm supposed to be maybe using a constraint column with
>a name to do this easier/more properly.

>
>Yes, if you name the constraint it's a little easier:
>

Quote:

Originally Posted by

> set @.dynamicsql = ' alter table institution_xref add institution_id
>varchar(60) not null default ''' + @.default_institution_id + ''' '

>
>...not null CONSTRAINT my_temp_default DEFAULT ''' ...
>

Quote:

Originally Posted by

> EXEC (@.dynamicsql)

>
ALTER TABLE ... DROP CONSTRAINT my_temp_default

thanks. much!
Jeff Kish