combining two tables

hi,

does anyone have any good insight to this problem? I will have two tables which contains the same number of columns for same data types, they are related together by a key book_id. I need to combine them together and create some extra totalling data in the new datatable for a report. Here is an example

table1:
book_id new_words cost_of_change
1 3000 2
1 4000 4
2 500 4

table2
book_id old_words cost_of_change
1 1500 1
3 2500 5

I need to combine them into a table like this:

book_id new_words cost_of_change old_words cost_of_change total_cost
1 7000 6 1500 1 7
2 500 4 0 0 4
3 2500 5 0 0 5

whats the best way to do this?

I have been trying to use full outer joins to do this but I find this a difficult way to create new rows in the new combined table, like what will be an easy way for me to say in SQL that only one row should be used for book_id 1, as it's is present in the two source tables 3 times? I think i will be able to find out from using left and right inner joins, before i make the new combined table but this seems like a very ineligant way of doing this, as it seems to require lots of temp tables.

thxIs table1 the only place where there can be duplicate book IDs? I'm going to assume so, but if table2 can have duplicates you'll need to modify this a bit. But the basic idea should work.

You can use an aggregate subquery for table1 that you then join on table2. The subquery looks something like this (all of this is untested code; you may need to tweak):

SELECT SUM(new_words), SUM(cost_of_change) FROM table1 GROUP BY book_id

That sums the two fields for each id and eliminates the dupes. That subquery becomes one of the derived tables in the outer select. Something like this:

SELECT B.book_id, A.new_words, A.new_cost, B.old_words, B.cost_of_change AS old_cost, total_cost FROM table2 AS B
INNER JOIN (SELECT SUM(new_words) AS new_words, SUM(cost_of_change) AS new_cost
FROM table1 GROUP BY book_id) AS A
ON A.book_id = B.book_id

This query doesn't yet aggregate the totals from the two tables, so that will be another outer query, but the idea is the same. And there are almost certainly ways to simplify this query.

One way is to use table variables in SS2K. Then you can do three more straightforward joins.

Is this helpful? Or have I confused things more?

Don|||Something like this should work:

Select
IsNull(A.book_id,B.book_id) as book_id,
IsNull(A.new_words,0.0) as New_Words,
IsNull(A.Cost_of_change,0.0) ACost_of_Change,
IsNull(B.old_words,0.0) as Old_Words,
IsNull(B.Cost_of_change,0.0) BCost_of_Change,
IsNull(A.Cost_of_change,0.0)+IsNull(B.Cost_of_change,0.0) as Cost_of_change
From
(Select book_id, Sum(new_words) New_Words,Sum(Cost_of_change) Cost_of_change FROM Table1 Group By book_id) A
FULL OUTER JOIN
(Select book_id, Sum(old_words) Old_Words,Sum(Cost_of_change) Cost_of_change FROM Table2 Group By book_id) B
ON A.book_id=B.book_id

|||Thanks Guys, that solved my problem. The second method is lot more readable, but which would be the most efficient method?|||Both methods are basically the same thing. The second method could be made clearer by using Table variables as mentioned in the first method. But I don't think that would affect efficiency. You could test this using the Sql Query Analyzer and compare the execution plans and execution times for each.

Combining Reports

am currently looking for a way to potentially combine several types of
reports systematically to create a single report pack for a client.
This would need to be done on the fly so the clients can choose which bits /
Reports they wish to have and then click the Download PDF button and hey
presto here it comes?
Is there a way and if so can anyone let me know the best / easiest way for a
simple brain to do it!
Cheers alotHi, Paul
I think we'd need more information about your application/UI and method
of delivery...
but making some simple assumption, if you are dealing with your own
custom web app, you can use the SOAP api to invoke reporting services
and render the reports you need.
A nice way to present this would be to use the fileshare delivery
extension, and have the ReportServer render the selected reports to
this share, then just send an email or notification to the user with a
link to access the file share where the PDFs have been saved.
You can also create one big report, that has multiple datasets and
several dataregions, corresponding to different "reports." Then using
parameters, you can drive which datasets get to be populated with data
or hidden from the user...Note that this approach may be a bit slower,
but it will provide a better way of giving the user one single PDF
which contains multiple "reports."
I hope I've given you some ideas to get started.
Regards,
Thiago Silva
MCAD.NET
Paul Roberts wrote:
> am currently looking for a way to potentially combine several types of
> reports systematically to create a single report pack for a client.
> This would need to be done on the fly so the clients can choose which bits /
> Reports they wish to have and then click the Download PDF button and hey
> presto here it comes?
> Is there a way and if so can anyone let me know the best / easiest way for a
> simple brain to do it!
> Cheers alot|||"tafs7" <tsilva7@.gmail.com> wrote in message
news:1156518561.205312.262860@.74g2000cwt.googlegroups.com...
Thiago,
Firstly, Great Name.
Right here is the setup / Architecture, the company I am currently working
for has been using SSRS2000 to provide reports to clients via a web
interface allowing them a series of criteria to make their bespoke report.
All information is saved to a database and a GUID and Version ID is supplied
back to the interface that will then be passed to a process filter that will
then be sent off to SSRS as parameters to be disseminated to the Stored
procedure that is called from the RDL. Hey presto, SSRS send back the
desired report in the desired format.
Now, my lead developer has said, Paul you are a Genius!!!, we need you to
provide a solution for our clients to say I would like "Report 1", "Report
4" and "Report 12" [Where each of these reports is an individual report RDL
Template] to be be selected so as to be combined into one single but
brilliant report. He has also asked as a request that it has continuous
Page Numbers (I can do this bit.) and a Key / Legend bespoke to the report /
s generated and only containing the description to Icons that are contained.
Now this I know is a massive job to undertake and I have a couple of Ideas
of how it can be done. However if there is a way that I can say, "Right I
have a generated report for each individual report, 'Report 1', 'Report 4'
and 'Report 12' and then say go and get me these GENERATED reports and
output them into a single PDF or Excel.
I am guessing that this would be a bigger issue than your suggestion and it
is not something I would get SSRS to do.
Thank you for you reply and look forward to hearing a response.
Paul
> Hi, Paul
> I think we'd need more information about your application/UI and method
> of delivery...
> but making some simple assumption, if you are dealing with your own
> custom web app, you can use the SOAP api to invoke reporting services
> and render the reports you need.
> A nice way to present this would be to use the fileshare delivery
> extension, and have the ReportServer render the selected reports to
> this share, then just send an email or notification to the user with a
> link to access the file share where the PDFs have been saved.
> You can also create one big report, that has multiple datasets and
> several dataregions, corresponding to different "reports." Then using
> parameters, you can drive which datasets get to be populated with data
> or hidden from the user...Note that this approach may be a bit slower,
> but it will provide a better way of giving the user one single PDF
> which contains multiple "reports."
> I hope I've given you some ideas to get started.
> Regards,
> Thiago Silva
> MCAD.NET
> Paul Roberts wrote:
>> am currently looking for a way to potentially combine several types of
>> reports systematically to create a single report pack for a client.
>> This would need to be done on the fly so the clients can choose which
>> bits /
>> Reports they wish to have and then click the Download PDF button and hey
>> presto here it comes?
>> Is there a way and if so can anyone let me know the best / easiest way
>> for a
>> simple brain to do it!
>> Cheers alot
>|||Paul,
Firstly, thanks for the name compliment...it's Portuguese (BR), if
you're wondering.
I don't think it would be easy or even possible to generate separate
reports (different RDLs), then combine them into PDF via code, etc.
The best approach to this in my opinion, still would be to have one RDL
that contains the different report bodies in their contained rectangles
or tables or lists, and based on parameters for which report number was
selected, only execute the appropriate query and render the appropriate
RDL body/data section.
Unfortunately, RS does not allow for expression in Subreport names,
otherwise, I would recommend an entry point report with a Subreport
that would be defined based on a parameter, contained in a table. Then
you could write a little SQL to parse the entered rpt numbers as
individual rows, so you'd have the Subreport render different report
numbers in each "row" of the parent table/list. Hope this makes sense,
but it won't matter 'cause it ain't supported ;-)
Anyways, that's my 2 cents.
Cheers back at you!
Thiago Silva
MCAD.NET
Paul Roberts wrote:
> "tafs7" <tsilva7@.gmail.com> wrote in message
> news:1156518561.205312.262860@.74g2000cwt.googlegroups.com...
> Thiago,
> Firstly, Great Name.
> Right here is the setup / Architecture, the company I am currently working
> for has been using SSRS2000 to provide reports to clients via a web
> interface allowing them a series of criteria to make their bespoke report.
> All information is saved to a database and a GUID and Version ID is supplied
> back to the interface that will then be passed to a process filter that will
> then be sent off to SSRS as parameters to be disseminated to the Stored
> procedure that is called from the RDL. Hey presto, SSRS send back the
> desired report in the desired format.
> Now, my lead developer has said, Paul you are a Genius!!!, we need you to
> provide a solution for our clients to say I would like "Report 1", "Report
> 4" and "Report 12" [Where each of these reports is an individual report RDL
> Template] to be be selected so as to be combined into one single but
> brilliant report. He has also asked as a request that it has continuous
> Page Numbers (I can do this bit.) and a Key / Legend bespoke to the report /
> s generated and only containing the description to Icons that are contained.
> Now this I know is a massive job to undertake and I have a couple of Ideas
> of how it can be done. However if there is a way that I can say, "Right I
> have a generated report for each individual report, 'Report 1', 'Report 4'
> and 'Report 12' and then say go and get me these GENERATED reports and
> output them into a single PDF or Excel.
> I am guessing that this would be a bigger issue than your suggestion and it
> is not something I would get SSRS to do.
> Thank you for you reply and look forward to hearing a response.
>
> Paul
>
> > Hi, Paul
> >
> > I think we'd need more information about your application/UI and method
> > of delivery...
> >
> > but making some simple assumption, if you are dealing with your own
> > custom web app, you can use the SOAP api to invoke reporting services
> > and render the reports you need.
> >
> > A nice way to present this would be to use the fileshare delivery
> > extension, and have the ReportServer render the selected reports to
> > this share, then just send an email or notification to the user with a
> > link to access the file share where the PDFs have been saved.
> >
> > You can also create one big report, that has multiple datasets and
> > several dataregions, corresponding to different "reports." Then using
> > parameters, you can drive which datasets get to be populated with data
> > or hidden from the user...Note that this approach may be a bit slower,
> > but it will provide a better way of giving the user one single PDF
> > which contains multiple "reports."
> >
> > I hope I've given you some ideas to get started.
> >
> > Regards,
> > Thiago Silva
> > MCAD.NET
> >
> > Paul Roberts wrote:
> >> am currently looking for a way to potentially combine several types of
> >> reports systematically to create a single report pack for a client.
> >>
> >> This would need to be done on the fly so the clients can choose which
> >> bits /
> >> Reports they wish to have and then click the Download PDF button and hey
> >> presto here it comes?
> >>
> >> Is there a way and if so can anyone let me know the best / easiest way
> >> for a
> >> simple brain to do it!
> >>
> >> Cheers alot
> >

Combining Reports

I am currently looking for a way to potentially combine several types of
reports systematically to create a single report pack for a client.
This would need to be done on the fly so the clients can choose which bits /
Reports they wish to have and then click the Download PDF button and hey
presto here it comes?
Is there a way and if so can anyone let me know the best / easiest way for a
simple brain to do it!
Cheers alotIf I understood correctly, you want a list of reports in one page and when
the client clicks on any report or download option it should download.
You can use "Action" to create a page with all of your reports and use
action to render or create a small program using asp.net and use render
method and create pdf depending on the report clicks.
Amarnath
"Paul Roberts" wrote:
> I am currently looking for a way to potentially combine several types of
> reports systematically to create a single report pack for a client.
> This would need to be done on the fly so the clients can choose which bits /
> Reports they wish to have and then click the Download PDF button and hey
> presto here it comes?
> Is there a way and if so can anyone let me know the best / easiest way for a
> simple brain to do it!
> Cheers alot
>
>

Combining 3 datasource controls with t-sql

Hello everyone,

I'm trying to get a count of 3 different types on the same field. For Example, let's use Gender as the field with these options: Male, Female, Not Given. What I'm wanting to do is retrieve a count for each type. What I have so far is: SELECT COUNT(Gender) WHERE Gender = 'Male' and I have to duplicate this in 3 different data controls. I would like, however, to have one datasource control with a statement along the lines of:

SELECT ( SELECT COUNT(Gender) FROM Users WHERE Gender='Male), SELECT COUNT(Gender) FROM Users WHERE Gender='Female', SELECT COUNT(Gender) WHERE Gender='NotGiven' )

From Users

Or something to that effect. Any suggestions?

Thank you greatly for your help,

Mark

SELECTSUM(CASEWHEN Gender='Male'THEN 1ELSE 0END)as MaleCount,

SUM(CASEWHEN Gender='Female'THEN 1ELSE 0END)as FemaleCount,

SUM(CASEWHEN Gender='NotGiven'Or GenderISNULLTHEN 1ELSE 0END)as NotGivenCount

FROM Users

|||

This ended up working for me: SELECT Count_1 = (SELECT COUNT(Gender) FROM User WHERE Gender='male'), Count_2 = (SELECT COUNT(Gender) FROM Users WHERE Gender='female') and then access Count_1 and Count_2

Thanks for your help.. I will mark yours as the answer because it looks like it would work too.

sqlsql

Combine 3 Queries into One

Hello,
I have a large database that contains info about several types of business
industries. There is a table called Co_Ind_Sales which contains industry
sales that I need to sum up for 3 separate industries. There is an
Industry_Id in the Co_Ind_Sales table. I am using the following SQL in
Query Analyzer and I get the right result for the 1 industry I am using:
select SUM(cis.Sales)as Sales Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id =9
And c.listing_type in ('H','S')
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100) ,CONVERT(MONEY,Sales)
,1)),1,100))
AS 'Total Foodservice Revenues - Chain Restaurants'
From Lisa_TotalRev
This gives me the following results:
Total Foodservice Revenues - Chain Restaurants
$177,835,953,607.00
I now have 2 other industries that I need to do the same thing with, they
would be:
And cis.industry_id =42
And cis.industry_id =52
I need to combine all three result sets into one report, like such:
Total Foodservice Revenues
$177,835,953,607.00 Chain Restaurants
Total Foodservice Revenues
$16,077,196,215.00 Hotel/Motel
Total Foodservice Revenues
$30,244,812,996.00 Foodservice Management Operators
Please help. I am rather new to SQL so if you could add to my code the
pieces that I need that would be wonderful. I have trouble understanding
the help file. I do better with examples not just text. Thanks for any help
anyone can give.
On Thu, 12 May 2005 18:34:56 GMT, "Lisa Farina via droptable.com"
<forum@.nospam.droptable.com> wrote:
Something like:

>select
> SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
>Into Lisa_TotalRev
>From company_industry ci, co_ind_sales cis, company c
>Where ci.company_id=cis.company_id
> And cis.company_id=c.company_id
> And cis.current_record='Y'
> And ci.in_book='Y'
> And cis.industry_id in (9, 42, 43)
> And c.listing_type in ('H','S')
>GROUP BY cis.industry_id
>SELECT
> '$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100) ,CONVERT(MONEY,Sales),1)),1,100))
> AS 'Total Foodservice Revenues',
> IndustryName
>From Lisa_TotalRev
and it would make me personally very happy if you learned to use the
newer ANSI join style!
Welcome to SQL!
Josh
|||Sorry I am using the older style. I will do my best to learn the newer way.
I'm not sure if you actually posted a solution because the messgage started
with Something like: [quoted text clipped - 17 lines]
and then it was cut off. Could you please repost. Thanks.
|||On Thu, 12 May 2005 19:33:46 GMT, "Lisa Farina via droptable.com"
<forum@.droptable.com> wrote:
>Sorry I am using the older style. I will do my best to learn the newer way.
>I'm not sure if you actually posted a solution because the messgage started
>with Something like: [quoted text clipped - 17 lines]
>and then it was cut off. Could you please repost. Thanks.
I think that's a display option you can turn off, but here's the
pseudo-code I posted with the quotes removed.
J.
select
SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id in (9, 42, 43)
And c.listing_type in ('H','S')
GROUP BY cis.industry_id
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100) ,CONVERT(MONEY,Sales),1)),1,100))
AS 'Total Foodservice Revenues',
IndustryName
From Lisa_TotalRev

Combine 3 Queries into One

Hello,
I have a large database that contains info about several types of business
industries. There is a table called Co_Ind_Sales which contains industry
sales that I need to sum up for 3 separate industries. There is an
Industry_Id in the Co_Ind_Sales table. I am using the following SQL in
Query Analyzer and I get the right result for the 1 industry I am using:
select SUM(cis.Sales)as Sales Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id =9
And c.listing_type in ('H','S')
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VA
RCHAR(100),CONVERT(MONEY,Sales)
,1)),1,100))
AS 'Total Foodservice Revenues - Chain Restaurants'
From Lisa_TotalRev
This gives me the following results:
Total Foodservice Revenues - Chain Restaurants
---
$177,835,953,607.00
I now have 2 other industries that I need to do the same thing with, they
would be:
And cis.industry_id =42
And cis.industry_id =52
I need to combine all three result sets into one report, like such:
Total Foodservice Revenues
---
$177,835,953,607.00 Chain Restaurants
Total Foodservice Revenues
---
$16,077,196,215.00 Hotel/Motel
Total Foodservice Revenues
---
$30,244,812,996.00 Foodservice Management Operators
Please help. I am rather new to SQL so if you could add to my code the
pieces that I need that would be wonderful. I have trouble understanding
the help file. I do better with examples not just text. Thanks for any help
anyone can give.On Thu, 12 May 2005 18:34:56 GMT, "Lisa Farina via droptable.com"
<forum@.nospam.droptable.com> wrote:
Something like:

>select
> SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
>Into Lisa_TotalRev
>From company_industry ci, co_ind_sales cis, company c
>Where ci.company_id=cis.company_id
> And cis.company_id=c.company_id
> And cis.current_record='Y'
> And ci.in_book='Y'
> And cis.industry_id in (9, 42, 43)
> And c.listing_type in ('H','S')
>GROUP BY cis.industry_id
>SELECT
> '$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VA
RCHAR(100),CONVERT(MONEY,Sales)
,1)),1,100))
> AS 'Total Foodservice Revenues',
> IndustryName
>From Lisa_TotalRev
and it would make me personally very happy if you learned to use the
newer ANSI join style!
Welcome to SQL!
Josh|||Sorry I am using the older style. I will do my best to learn the newer way.
I'm not sure if you actually posted a solution because the messgage started
with Something like: [quoted text clipped - 17 lines]
and then it was cut off. Could you please repost. Thanks.|||On Thu, 12 May 2005 19:33:46 GMT, "Lisa Farina via droptable.com"
<forum@.droptable.com> wrote:
>Sorry I am using the older style. I will do my best to learn the newer way.
>I'm not sure if you actually posted a solution because the messgage started
>with Something like: [quoted text clipped - 17 lines]
>and then it was cut off. Could you please repost. Thanks.
I think that's a display option you can turn off, but here's the
pseudo-code I posted with the quotes removed.
J.
select
SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id in (9, 42, 43)
And c.listing_type in ('H','S')
GROUP BY cis.industry_id
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VA
RCHAR(100),CONVERT(MONEY,Sales),1)),
1,100))
AS 'Total Foodservice Revenues',
IndustryName
From Lisa_TotalRev

Combine 3 Queries into One

Hello,
I have a large database that contains info about several types of business
industries. There is a table called Co_Ind_Sales which contains industry
sales that I need to sum up for 3 separate industries. There is an
Industry_Id in the Co_Ind_Sales table. I am using the following SQL in
Query Analyzer and I get the right result for the 1 industry I am using:
select SUM(cis.Sales)as Sales Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id =9
And c.listing_type in ('H','S')
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales)
,1)),1,100))
AS 'Total Foodservice Revenues - Chain Restaurants'
From Lisa_TotalRev
This gives me the following results:
Total Foodservice Revenues - Chain Restaurants
---
$177,835,953,607.00
I now have 2 other industries that I need to do the same thing with, they
would be:
And cis.industry_id =42
And cis.industry_id =52
I need to combine all three result sets into one report, like such:
Total Foodservice Revenues
---
$177,835,953,607.00 Chain Restaurants
Total Foodservice Revenues
---
$16,077,196,215.00 Hotel/Motel
Total Foodservice Revenues
---
$30,244,812,996.00 Foodservice Management Operators
Please help. I am rather new to SQL so if you could add to my code the
pieces that I need that would be wonderful. I have trouble understanding
the help file. I do better with examples not just text. Thanks for any help
anyone can give.On Thu, 12 May 2005 18:34:56 GMT, "Lisa Farina via SQLMonster.com"
<forum@.nospam.SQLMonster.com> wrote:
Something like:
>select
> SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
>Into Lisa_TotalRev
>From company_industry ci, co_ind_sales cis, company c
>Where ci.company_id=cis.company_id
> And cis.company_id=c.company_id
> And cis.current_record='Y'
> And ci.in_book='Y'
> And cis.industry_id in (9, 42, 43)
> And c.listing_type in ('H','S')
>GROUP BY cis.industry_id
>SELECT
> '$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales),1)),1,100))
> AS 'Total Foodservice Revenues',
> IndustryName
>From Lisa_TotalRev
and it would make me personally very happy if you learned to use the
newer ANSI join style!
Welcome to SQL!
Josh|||Sorry I am using the older style. I will do my best to learn the newer way.
I'm not sure if you actually posted a solution because the messgage started
with Something like: [quoted text clipped - 17 lines]
and then it was cut off. Could you please repost. Thanks.|||On Thu, 12 May 2005 19:33:46 GMT, "Lisa Farina via SQLMonster.com"
<forum@.SQLMonster.com> wrote:
>Sorry I am using the older style. I will do my best to learn the newer way.
>I'm not sure if you actually posted a solution because the messgage started
>with Something like: [quoted text clipped - 17 lines]
>and then it was cut off. Could you please repost. Thanks.
I think that's a display option you can turn off, but here's the
pseudo-code I posted with the quotes removed.
J.
select
SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id in (9, 42, 43)
And c.listing_type in ('H','S')
GROUP BY cis.industry_id
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales),1)),1,100))
AS 'Total Foodservice Revenues',
IndustryName
From Lisa_TotalRev

ColumnNamesInFirstDataRow Expression

I have a SSIS package I am trying to create that will accept two types of files. They are the same exact file except for one contains a header and one does not. So I setup a conneciton manager to the csv file. I then set a variable of bool type, and then assigned that to the ColumNamesInFirstDataRow expressions property of the conneciton manager.

So the pacakge runs. Loops a directory, runs a script that sets the Header variable to true/false based on the file name. But when it gets to the data flow it always ignores the property and never bypasses the header row. The variable is being set. I tried datarowstoskip and set it to 1 instead of the above mentioned property, and that does not work either.

How can I accomplish this?

There is a known bug in evaluation of flat file connection properties that might be causing this. IT is scheduled to be fixed for the next release. Unfortunately, I do not see an easy workaround...

-Bob

|||Is there any know work around to this with .net code or anything else?|||

You could set up two connection managers (one with headers, one without) and two dataflows to match, and use a script task to check the file for a header row. Then use precedence constraints to pick the data flow to execute.

Or you could parse the whole file in a script source, but that could be a lot of work, depending on the complexity of your file.

ColumnNamesInFirstDataRow Expression

I have a SSIS package I am trying to create that will accept two types of files. They are the same exact file except for one contains a header and one does not. So I setup a conneciton manager to the csv file. I then set a variable of bool type, and then assigned that to the ColumNamesInFirstDataRow expressions property of the conneciton manager.

So the pacakge runs. Loops a directory, runs a script that sets the Header variable to true/false based on the file name. But when it gets to the data flow it always ignores the property and never bypasses the header row. The variable is being set. I tried datarowstoskip and set it to 1 instead of the above mentioned property, and that does not work either.

How can I accomplish this?

There is a known bug in evaluation of flat file connection properties that might be causing this. IT is scheduled to be fixed for the next release. Unfortunately, I do not see an easy workaround...

-Bob

|||Is there any know work around to this with .net code or anything else?|||

You could set up two connection managers (one with headers, one without) and two dataflows to match, and use a script task to check the file for a header row. Then use precedence constraints to pick the data flow to execute.

Or you could parse the whole file in a script source, but that could be a lot of work, depending on the complexity of your file.

Column metadata from Connection Manager programmatically

Hi all!

My problem I've been struggling with is the following. I have a set of text files (around 70), each with different column numbers and types. I define Flat File Connection Managers for each of them where I can nicely rename, set data types and omit certain columns. I do this once and this will be the basis for the rest of the data process (would be nice programmatically too actually).
I would like to pump each of these text files into SQL Server tables using CREATE TABLE and BULK INSERT (because do it one-by-one is really a pain). The question is:

is there a way to obtain column information (Script Task) from a Connection Manager so I can run CREATE TABLE-s? I just need the names, data type for each nothing fancy...

(I bumped into interfaces like IDTSConnectionManagerFlatFileColumns90, which I cannot handle from the Script Task.)

Any help appreciated!

What your asking is a design-time action, not run-time, and could be done if you load the package and walk round the object model. If using BULK INSERT, then why bother with SSIS Flat File Connections at all?|||

Thanks for the answer. That is exactly I cannot achieve:

Dim mgr As ConnectionManager = Dts.Connections(1)
Dim o As Object = mgr.Properties("Columns").GetValue(mgr)

This returns something (COM IDTSConnectionManagerFlatFileColumns90?) that I cannot handle more. Or am I on the wrong track? Do I need more assemblies and references?

The other question: I've found it very comfortable to define flat file structure using Flat File Connections (UI, data types). On the other hand I need a CREATE TABLE based on a flat file structure. Other ideas maybe?

|||

What I said was that this was probably not the right way to do this. The Script task is using run-time.

If you try and use IDTSConnectionManagerFlatFileColumns90 then you will need another reference. Just look it up in Books Online and it wiull tell you that it is in the Microsoft.SqlServer.DTSRuntimeWrap assembly, so add this reference.

Dim conn As ConnectionManager = Dts.Connections(0)

Dim o As Object = conn.Properties("Columns").GetValue(conn)

Dim xx As Wrapper.IDTSConnectionManagerFlatFileColumns90 = CType(o, Wrapper.IDTSConnectionManagerFlatFileColumns90)

Dim dt As Wrapper.DataType = xx.Item(0).DataType

Dim w As Integer = xx.Item(0).MaximumWidth

The above code seems to work.

|||

Hi darren, how do you get the column name? The Wrapper.IDTSConnectionManagerFlatFileColumns90 doesn't have any 'name' member.

Also, i'm trying to do the reverse of this process, which is to add columns to the connection programmatically? How do i go about this?

I've come as close as getting adding the column into the wrapper.idtsconnectionmanagerflatfilecolumns90 collection, but i have no way of adding a 'name' to it? How do i do that? Here's my code:

dim conn2 as idtsconnectionmanager90 = pkg.connections("FlatFileConn").value

Dim conn3 As Wrapper.IDTSConnectionManagerFlatFile90 = CType(conn2.InnerObject, Wrapper.IDTSConnectionManagerFlatFile90)

Dim mynewcol1 As Wrapper.IDTSConnectionManagerFlatFileColumn90

mynewcol1 = conn3.Columns.Add

mynewcol1.DataType = Wrapper.DataType.DT_BOOL

mynewcol1.ColumnDelimiter = "~"

'<< This is where i can't add the 'name' >>

Some code would be really helpful.

Thanks.

|||

Try the following code after you add the mynewcol1

Dim name As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSName90

name = TryCast(mynewcol1, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSName90)

name.Name = "ColumnName"

Column metadata from Connection Manager programmatically

Hi all!

My problem I've been struggling with is the following. I have a set of text files (around 70), each with different column numbers and types. I define Flat File Connection Managers for each of them where I can nicely rename, set data types and omit certain columns. I do this once and this will be the basis for the rest of the data process (would be nice programmatically too actually).
I would like to pump each of these text files into SQL Server tables using CREATE TABLE and BULK INSERT (because do it one-by-one is really a pain). The question is:

is there a way to obtain column information (Script Task) from a Connection Manager so I can run CREATE TABLE-s? I just need the names, data type for each nothing fancy...

(I bumped into interfaces like IDTSConnectionManagerFlatFileColumns90, which I cannot handle from the Script Task.)

Any help appreciated!

What your asking is a design-time action, not run-time, and could be done if you load the package and walk round the object model. If using BULK INSERT, then why bother with SSIS Flat File Connections at all?|||

Thanks for the answer. That is exactly I cannot achieve:

Dim mgr As ConnectionManager = Dts.Connections(1)
Dim o As Object = mgr.Properties("Columns").GetValue(mgr)

This returns something (COM IDTSConnectionManagerFlatFileColumns90?) that I cannot handle more. Or am I on the wrong track? Do I need more assemblies and references?

The other question: I've found it very comfortable to define flat file structure using Flat File Connections (UI, data types). On the other hand I need a CREATE TABLE based on a flat file structure. Other ideas maybe?

|||

What I said was that this was probably not the right way to do this. The Script task is using run-time.

If you try and use IDTSConnectionManagerFlatFileColumns90 then you will need another reference. Just look it up in Books Online and it wiull tell you that it is in the Microsoft.SqlServer.DTSRuntimeWrap assembly, so add this reference.

Dim conn As ConnectionManager = Dts.Connections(0)

Dim o As Object = conn.Properties("Columns").GetValue(conn)

Dim xx As Wrapper.IDTSConnectionManagerFlatFileColumns90 = CType(o, Wrapper.IDTSConnectionManagerFlatFileColumns90)

Dim dt As Wrapper.DataType = xx.Item(0).DataType

Dim w As Integer = xx.Item(0).MaximumWidth

The above code seems to work.

|||

Hi darren, how do you get the column name? The Wrapper.IDTSConnectionManagerFlatFileColumns90 doesn't have any 'name' member.

Also, i'm trying to do the reverse of this process, which is to add columns to the connection programmatically? How do i go about this?

I've come as close as getting adding the column into the wrapper.idtsconnectionmanagerflatfilecolumns90 collection, but i have no way of adding a 'name' to it? How do i do that? Here's my code:

dim conn2 as idtsconnectionmanager90 = pkg.connections("FlatFileConn").value

Dim conn3 As Wrapper.IDTSConnectionManagerFlatFile90 = CType(conn2.InnerObject, Wrapper.IDTSConnectionManagerFlatFile90)

Dim mynewcol1 As Wrapper.IDTSConnectionManagerFlatFileColumn90

mynewcol1 = conn3.Columns.Add

mynewcol1.DataType = Wrapper.DataType.DT_BOOL

mynewcol1.ColumnDelimiter = "~"

'<< This is where i can't add the 'name' >>

Some code would be really helpful.

Thanks.

|||

Try the following code after you add the mynewcol1

Dim name As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSName90

name = TryCast(mynewcol1, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSName90)

name.Name = "ColumnName"

Column metadata from Connection Manager programmatically

Hi all!

My problem I've been struggling with is the following. I have a set of text files (around 70), each with different column numbers and types. I define Flat File Connection Managers for each of them where I can nicely rename, set data types and omit certain columns. I do this once and this will be the basis for the rest of the data process (would be nice programmatically too actually).
I would like to pump each of these text files into SQL Server tables using CREATE TABLE and BULK INSERT (because do it one-by-one is really a pain). The question is:

is there a way to obtain column information (Script Task) from a Connection Manager so I can run CREATE TABLE-s? I just need the names, data type for each nothing fancy...

(I bumped into interfaces like IDTSConnectionManagerFlatFileColumns90, which I cannot handle from the Script Task.)

Any help appreciated!

What your asking is a design-time action, not run-time, and could be done if you load the package and walk round the object model. If using BULK INSERT, then why bother with SSIS Flat File Connections at all?|||

Thanks for the answer. That is exactly I cannot achieve:

Dim mgr As ConnectionManager = Dts.Connections(1)
Dim o As Object = mgr.Properties("Columns").GetValue(mgr)

This returns something (COM IDTSConnectionManagerFlatFileColumns90?) that I cannot handle more. Or am I on the wrong track? Do I need more assemblies and references?

The other question: I've found it very comfortable to define flat file structure using Flat File Connections (UI, data types). On the other hand I need a CREATE TABLE based on a flat file structure. Other ideas maybe?

|||

What I said was that this was probably not the right way to do this. The Script task is using run-time.

If you try and use IDTSConnectionManagerFlatFileColumns90 then you will need another reference. Just look it up in Books Online and it wiull tell you that it is in the Microsoft.SqlServer.DTSRuntimeWrap assembly, so add this reference.

Dim conn As ConnectionManager = Dts.Connections(0)

Dim o As Object = conn.Properties("Columns").GetValue(conn)

Dim xx As Wrapper.IDTSConnectionManagerFlatFileColumns90 = CType(o, Wrapper.IDTSConnectionManagerFlatFileColumns90)

Dim dt As Wrapper.DataType = xx.Item(0).DataType

Dim w As Integer = xx.Item(0).MaximumWidth

The above code seems to work.

|||

Hi darren, how do you get the column name? The Wrapper.IDTSConnectionManagerFlatFileColumns90 doesn't have any 'name' member.

Also, i'm trying to do the reverse of this process, which is to add columns to the connection programmatically? How do i go about this?

I've come as close as getting adding the column into the wrapper.idtsconnectionmanagerflatfilecolumns90 collection, but i have no way of adding a 'name' to it? How do i do that? Here's my code:

dim conn2 as idtsconnectionmanager90 = pkg.connections("FlatFileConn").value

Dim conn3 As Wrapper.IDTSConnectionManagerFlatFile90 = CType(conn2.InnerObject, Wrapper.IDTSConnectionManagerFlatFile90)

Dim mynewcol1 As Wrapper.IDTSConnectionManagerFlatFileColumn90

mynewcol1 = conn3.Columns.Add

mynewcol1.DataType = Wrapper.DataType.DT_BOOL

mynewcol1.ColumnDelimiter = "~"

'<< This is where i can't add the 'name' >>

Some code would be really helpful.

Thanks.

|||

Try the following code after you add the mynewcol1

Dim name As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSName90

name = TryCast(mynewcol1, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSName90)

name.Name = "ColumnName"

column data types?

I know there's a way to query the schema, but don't recall exactly. Does
anyone know how to query the database to determine the field type, length,
precision, scale, etc?By the way, I know there was an undocumented stored procedure to do this too
,
but it returned the datatype string as the 3rd argument, and since I'm hopin
g
to receive this in a recordset (although one row) from an ADODB.Connection i
n
VB, I'm not sure how to take that 3rd argument and get it to the recordset.
"Les Stockton" wrote:

> I know there's a way to query the schema, but don't recall exactly. Does
> anyone know how to query the database to determine the field type, length,
> precision, scale, etc?
>|||www.aspfaq.com/2177
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:7CFEB2D5-4C65-4353-A350-4FF755F0C478@.microsoft.com...
>I know there's a way to query the schema, but don't recall exactly. Does
> anyone know how to query the database to determine the field type, length,
> precision, scale, etc?
>

Column Conflict error

Hi;

I am getting this error while I am trying to delete a record from "MediaTypes" table
Media Types table and DVBTestCOutputs table have related through MediaID (cascade on update only not delete)

MediaTypes DVBTestCOutputs
---- -------
MediaID int ....................
MType char(10) ....................
MediaType int

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DVBTestCOutputs_MediaTypes1'. The conflict occurred in database 'Test', table 'DVBTestCOutputs', column 'MediaType'.

can you help me please I do not want the related record deleted also from DVBTestCOutputs table so I didn't choose the cascade on delete checkbox is this the problem??If there is a PK-FK constraint, then you are not allowed to have child records that have no parent record. You will need to update the value of the ParentID in the child table to another ParentID, before deleting the parent record.

Otherwise, you need to remove your constraint.

Cheers
Ken

Collecting data from remote DBs

Got to start planning for a project that requires our system to collect data from different types of DB platforms remotely from our customers and store them in our SQL DB. Anybody know of any references I could read or where to start with this? I have a couple of ideas, but need to look at all the aspects of this to ensure that it's done correctly.

Thanks.What kind of database? Locating on what platform? How to remotely to access it? Through HTTP or something else?

In general, web service could be a possible solution.|||A couple of our clients are using SQL server, and others are using proprietary DB systems that have XML export capabilities. All are MS based systems and the data collection preference would be via http requests.

Thanks.