Merging, and loading dynamic flat file.

I have number of csv files in a folder, all of them with same columns, need to be merged into one table and imported to sql server.

-The first row of the csv file is a header.
-The csv files are updated everyday
-The destination table is replace by new table with new info in the csv.
-The new csv files can be created and old csv files may no longer exist, but we are only interested in information contain in current csv files in the folder.
-I need SSIS to combine all the csv files in the folder and merge into one table.
-Other issue is that the field names may change in csv, so can the SSIS package recognize the change in field name and made necessary change in destination table as well.

Any insight on this issues will be greatly appreciated?

Put simply this is not a scenario that SSIS supports. The key problem is the dynamic nature of the file structure, which SSIS has no run-time support for. The target scenarios for SSIS revolve around unattended/automated data transfer, and any kind of dynamic format is just never going to work well with SSIS.

What use would a new table be that the system has never seen before? For you, you obviously then interpret this, but unless you tell SSIS to do that up front, it would never make sense for it.

MERGING Variable in FOR LOOP COntainer

Hi All,

Seems like a simple task, but been a struggle.

Simply trying to move a group of files from one folder to another folder and renaming the files with the monthyear in the middle of the filename..

I'm using a FOR Loop container and works find. The added complexity is I'm trying to rename the files the same tine and putting the Month Year into the file name.

I guess the struggle is how to get the file name out so I can manipulate it.

I tried creating variable(V_SOURCE) which stores the file path and create another variable(V_FILENAME) to hold the filename. I believe on the expression page of the FOR LOOP editor if I select the the file name and extension radio button, it should put the file name in the V_FILENAME variable.

In my file task trying to join the two variables together in another variable, keep saying my path is wrong with the file task kicks off.

here's the syntaxt

@.[User::V_SourcePath] + @.[User::V_FILE_NAME]

JUst to follow up if someone can help me with syntaxt for the merge and if they have a better approach

to moving and renaming the files

|||

I haven't checked myself, but do you need to have a \ between the folder and file, or is it included?

You can check the value of your variables by setting a breakpoint and typing the variable into the watch window.

|||THanks is helping out getting a picture of what's going on|||

Dan Cleary wrote:

JUst to follow up if someone can help me with syntaxt for the merge and if they have a better approach

to moving and renaming the files

I have done that using the File System task. I just posted an example in my blog:

http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

I hope you find it helpful.

|||

Rafeal, looks like just what the docotor ordered, does the scope of the variable make a difference?

Having an issue when joining the SourcePath with the file name. The scope of those variables are at the package level not at For Loop.

I do appreciate your response

|||

Variable scope is not an issue here; if you need it, just define all the variables at the package level.

regards

|||

Rafeal your Blog was great and very useful. I uess my struggle is doing the syntaxt on the file name in the expression builder.

I'm trying to take a group of file that are currently named RPT_BANK_NAME_@.MONTHYEAR_BANKNAME.XLS and

convert it to RPT_BANK_NAME_03_2007_BANKNAME.XLS. I have the report named storec in the variable just not sure on the syntaxt to strip out the @.MONTHYEAR and replace with the month and the year.

Any suggestions?

I tried to do a substring but not recognizing that function in the eexpresion builder

|||

Dan,

I guess I don't understand what is the format of the original name of the file. What I don;t get is the @.MONTHYEAR part.

is 03_2007 the month and year of the package execution date? or are they part of the original file name?

|||

THe @.monthyear is confusing, it's just hardcoded in the report name.

SO what I'm attempting to do in my expresion is take the Variable which is storing the report name

RPT_BANK_NAME_MONTHYEAR_BANKNAME.XLS and replace the monthyear with the curent Month and date.

I tried this but the expresion keeps failing the validation checks. Pretty much what you had in your blog

@.[User::V_DestinationFolder] + SUBSTRING( @.[User::V_Invoice_File] , 1 , FINDSTRING( @.[User::V_Invoice_File],".",1) 1 ) + "-" + (DT_STR, 2, 1252) Month( @.[System::StartTime] )+ (DT_STR, 4, 1252) Year( @.[System::StartTime] )+ SUBSTRING( @.[User::V_Invoice_File] , FINDSTRING( @.[User::V_Invoice_File],".",1) , LEN( @.[User::V_Invoice_File] ) )

|||

You've got an extra 1 in the expression (see red below) After correction, and assuming V_DestinationFolder is c:\temp\, it gives c:\temp\RPT_BANK_NAME_@.MONTHYEAR_BANKNAME.-32007.XLS, which I don't think is exactly what you want.

@.[User::V_DestinationFolder] + SUBSTRING( @.[User::V_Invoice_File] , 1 , FINDSTRING( @.[User::V_Invoice_File],".",1) 1 ) + "-" + (DT_STR, 2, 1252) Month( @.[System::StartTime] )+ (DT_STR, 4, 1252) Year( @.[System::StartTime] )+ SUBSTRING( @.[User::V_Invoice_File] , FINDSTRING( @.[User::V_Invoice_File],".",1) , LEN( @.[User::V_Invoice_File] ) )

The one below returns c:\temp\RPT_BANK_NAME_3-2007_BANKNAME.XLS and uses the Replace function. However, I have not tested it in the context of Rafael's example, so you might need to make some modifications to get it to work for you.

@.[User::V_DestinationFolder] + REPLACE( @.[User::V_Invoice_File] ,"@.MONTHYEAR", ((DT_STR, 2, 1252) MONTH( GETDATE() )) + "-" + ((DT_STR, 4, 1252) YEAR( GETDATE() )) )

|||

John is spot on.

In your case, REPLACE is a better option since '@.MONTHYEAR' is a literal that is always part of the file name. The expression in my example adds the month and year at the end of the file name and before the file extension (.txt); so I used FINDSTRING.

BTW, notice the expressions we are given use @.system::startTime; which gives you the month and year of the package execution date; so make sure that meets your requirements.

|||

Guys, you've been a huge help! I'm doing a watch and looking at the V_DESTINATION_PATH and it shows the

following in the watch window

+ User::V_DestinationPath {E:\\Client Billing 2\\FTP_Incoming\\INVOICE\\RPT-234-3-2007-BILL234.xls} String

but when running the task getting a path error

[File System Task] Error: An error occurred with the following error message: "Could not find a part of the path.". , any idea?

|||Looks like your variable V_Destination_Folder contains too many slashes, perhaps.|||

When you view it in the watch window, it shows the escape characters, thus the doubled slashes. You can validate the value by using a script task with a MsgBox.

Dan, you might want to double-check that the folder and filename you are referencing exist. Also, check permissions to ensure you can write to that location.

Merging two tables with selection

I would like to have two tables. One I call SystemPropertyTypeTable which
contains the defaults and the other UserPropertyTypeTable. Each has 3
fields. PropertyType, Description, Status.

The idea here is to allow a user to change his/her defaults or to add a new
Property Type without messing with the system default list.

I would like to Merge these two tables using the following logic.
The SystemPropertyTypeTable any records that have "ACTIVE" for the status.
The UserPropertyTypeTable all records.

Group by Name and remove any duplicates.
if the UserPropertyTypeTable has INACTIVE then Throw away the Active Record
from the SystemPropertyTypeTable and keep the INACTIVE record.

Here is my code so far.

SELECT T.PropertyType, T.Status
FROM [SELECT PropertyType,Status
FROM SystemPropertyTypeTable Where Status='ACTIVE'
UNION ALL
SELECT PropertyType,Status
FROM UserPropertyTypeTable]. AS T
GROUP BY T.PropertyType, T.Status
HAVING (((Count(*))=1));

here is the resultset
ShowAllRecordsMerged PropertyType Status
APARTMENT ACTIVE
APARTMENT INACTIVE
BUILDING ACTIVE
GARAGE ACTIVE
KOISK ACTIVE
MAINTENANCE SHOP ACTIVE
MAINTENANCE STORAGE AREA ACTIVE
OFFICE ACTIVE
PARKING SPACE ACTIVE
PARKING SPACE INACTIVE
SHOP ACTIVE
STORAGE AREA ACTIVE

So looking at this I would still like to remove any duplicates leaving the
INACTIVE ones which would be the first APARTMENT record and the first
PARKING SPACE record. Also it would be nice to add the description back into
this as well.

Any help anyone can be here would be wonderful.

Thanks in advance.

BruceBruce Stradling (bstradling@.cox.net) writes:

Quote:

Originally Posted by

I would like to Merge these two tables using the following logic.
The SystemPropertyTypeTable any records that have "ACTIVE" for the status.
The UserPropertyTypeTable all records.
>
Group by Name and remove any duplicates. if the UserPropertyTypeTable
has INACTIVE then Throw away the Active Record from the
SystemPropertyTypeTable and keep the INACTIVE record.

If I understand this correctly, you want:

SELECT U.PropertyType, U.Description, U.Status
FROM UserPropertyTable U
UNION ALL
SELECT S.PropertyType, S.Description, S.Status
FROM SystemPropertyType S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
FROM UserPropertyTable U
WHERE S.Property = U.Property)

--
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|||Thanks ... that was exactly what I needed. Here is the final code:

SELECT U.PropertyType, U.Description, U.Status
FROM UserPropertyTypeTable U
UNION ALL SELECT S.PropertyType, S.Description, S.Status
FROM SystemPropertyTypeTable S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
FROM UserPropertyTypeTable U
WHERE S.PropertyType = U.PropertyType)
ORDER BY PropertyType;

And then another that removed all inactive records:

SELECT U.PropertyType, U.Description, U.Status
FROM UserPropertyTypeTable U
WHERE U.Status = 'ACTIVE'
UNION ALL SELECT S.PropertyType, S.Description, S.Status
FROM SystemPropertyTypeTable S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
FROM UserPropertyTypeTable U
WHERE S.PropertyType = U.PropertyType)
ORDER BY PropertyType;

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns982BE98115752Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Bruce Stradling (bstradling@.cox.net) writes:

Quote:

Originally Posted by

I would like to Merge these two tables using the following logic.
The SystemPropertyTypeTable any records that have "ACTIVE" for the

status.

Quote:

Originally Posted by

Quote:

Originally Posted by

The UserPropertyTypeTable all records.

Group by Name and remove any duplicates. if the UserPropertyTypeTable
has INACTIVE then Throw away the Active Record from the
SystemPropertyTypeTable and keep the INACTIVE record.

>
If I understand this correctly, you want:
>
SELECT U.PropertyType, U.Description, U.Status
FROM UserPropertyTable U
UNION ALL
SELECT S.PropertyType, S.Description, S.Status
FROM SystemPropertyType S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
FROM UserPropertyTable U
WHERE S.Property = U.Property)
>
>
--
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

Merging two tables then grouping them

I have two tables, one is named Employee and the other Job_title. I'm trying to combine the two tables so I can group certain columns.

This is what I thought of so far and please correct me if I'm wrong.

SELECT Last_name FROM Employee
UNION
SELECT Job_title_code FROM Job_title
GROUP BY Exempt_non_exempt
FROM Job_title

Now this is just a theory (obviously it doesn't work) but basiclly I'm trying to have columns from two tables and have the grouped.Huh?

Wha?

There is no relationship between these tables?
No foreign keys?
You are selecting a single column and performing GROUP BY without any aggregate functions?

This makes no sense.|||Well, in both tables, they each have a job_title_code column.

Employee table
lastname, firstname, job title code

Job_title Table
job title code, job title, salary, exempt/non exempt|||Is this homework?

It looks like homework.
It sounds like homework.

Are we studying relational databases?|||So why aren't you using a join?

SELECT Last_name,
Job_title_code,
Exemp_non_exempt
FROM Employee
INNER JOIN Job_title on Employee.Job_title_code = Job_title.Job_title_code

I don't think this is homework. If it was homework his question would be more clearly phrased!|||sorry for the mix up, yes it is homework. that's for suggesting the INNER JOIN command. i found a variation of what you did and it worked out for my db. by the way, how did you put your code in a window like that?

SELECT Employee.Last_Name, Job_title.Exempt_non_exempt_status
FROM Employee
INNER JOIN Job_title
ON Employee.Job_title_code=Job_title.Job_title_code|||Enclose your code in CODE tagsL

[XCode]
Your code here
[X/Code]

Remove the X characters...

Your code here

Merging two tables

SQL 7
How do I merge two table's records? In other words, merge
T1 into T2. There are records in both tables that are the
same, but where T1 has a record that T2 doesn't have,
T1's record needs to be inserted into T2.
I've been reading Join syntax til my head is spinning.
Thanks,
DonInsert into T2
select * from T1 where T1.PrimaryKey
where T1.PrimaryKey NOT IN (Select PrimaryKey from T2)
Replace PrimaryKey with the unique priamry key from each table.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:7ff101c402df$8f68d030$a101280a@.phx.gbl...
> SQL 7
> How do I merge two table's records? In other words, merge
> T1 into T2. There are records in both tables that are the
> same, but where T1 has a record that T2 doesn't have,
> T1's record needs to be inserted into T2.
> I've been reading Join syntax til my head is spinning.
> Thanks,
> Don
>|||Don,

> How do I merge two table's records? In other words, merge
> T1 into T2. There are records in both tables that are the
> same, but where T1 has a record that T2 doesn't have,
> T1's record needs to be inserted into T2.
insert T2
select * from T1
where not exists (select * from T2 where T1.keycol = T2.keycol)
Linda

Merging two sql server DBs

Hi All,
i want to merge two sql server DBs (SourceDB,DestinationDB).
there could be some common tables between SourceDB and DestinationDB.
if that happens than i want sourceDb to overrite Destination DB table.
i know i can do this using exportWizard/DTS.
but problem with this is my source DB is huge and export wizard takes lot of
time.
i do not think i can do this using deattach\attach DB or using backup restor
e because both of this will overrite the detination database where as i want
to merge the two.
this is a one time job.
any ideas how i can do this.
thanks
siddharthHi
Are both databases's tables identical?
If they are you can create a view that contains data set of these tables
CREATE VIEW v_myview
AS
SELECT dbname1.dbo.table
UNION --or UNION ALL
SELECT dbname2.dbo.table
GO
SELECT * FROM v_myview
> if that happens than i want sourceDb to overrite Destination DB table
SELECT * INTO dbname2.dbo.NewTable FROM dbname1.dbo.table
GO
DROP TABLE dbname2.dbo.table
GO
--Run on dbname2
EXEC sp_rename 'NewTable','TABLE'
"siddharth" <anonymous@.discussions.microsoft.com> wrote in message
news:D9C89088-714B-4A6B-8AF8-10E0351EBF8F@.microsoft.com...
> Hi All,
> i want to merge two sql server DBs (SourceDB,DestinationDB).
> there could be some common tables between SourceDB and DestinationDB.
> if that happens than i want sourceDb to overrite Destination DB table.
> i know i can do this using exportWizard/DTS.
> but problem with this is my source DB is huge and export wizard takes lot
of time.
> i do not think i can do this using deattach\attach DB or using backup
restore because both of this will overrite the detination database where as
i want to merge the two.
> this is a one time job.
> any ideas how i can do this.
> thanks
> siddharth
>|||Hi
It sounds like you have a source and destination database the wrong way arou
nd! if you restored what you currently call the source database onto the des
tination server, you would then only need to more from the destination datab
ase the objects that are no
t in the source database! It would be possible to do this from the system ta
bles. DTS may still be an option to transfer the data or alternatively a DMO
program. If you don't have indexes, primary keys etc a straight forward SEL
ECT INTO statement would be
possible.
Alternatively you may want to look at something like the red gate tools to s
ee if they fulfil your requiremets:
http://www.red-gate.com/sql/summary.htm
John
-- siddharth wrote: --
Hi All,
i want to merge two sql server DBs (SourceDB,DestinationDB).
there could be some common tables between SourceDB and DestinationDB.
if that happens than i want sourceDb to overrite Destination DB table.
i know i can do this using exportWizard/DTS.
but problem with this is my source DB is huge and export wizard takes lot of
time.
i do not think i can do this using deattach\attach DB or using backup restor
e because both of this will overrite the detination database where as i want
to merge the two.
this is a one time job.
any ideas how i can do this.
thanks
siddharth

Merging two rows

Hi All,
I have question about how to do select on below table.
This table has 3column,
Col1->Account Id[Values 1, 2, 3, 4]
Col2>Code [Values 1,2,3]
Col3>Amount[Values 100,200,300,400]
Each code maps to debit, credit, balance...
I want to do a select which can return me debit, credit and balance for
all the Account in the table.
Something like
select debit, credit, balance from Tab1.
But the diffculty is each debit, credit and balance are in a different
row. How can I merge many rows into a single select statement.
Please advise me how I can do this
Thank YouTry,
select
accountid,
sum(case when code = 1 then amount else 0 end) as debit,
sum(case when code = 2 then amount else 0 end) as credit,
sum(case when code = 3 then amount else 0 end) as balance
from
group by accountid
AMB
"dhani" wrote:

> Hi All,
> I have question about how to do select on below table.
> This table has 3column,
> Col1->Account Id[Values 1, 2, 3, 4]
> Col2>Code [Values 1,2,3]
> Col3>Amount[Values 100,200,300,400]
> Each code maps to debit, credit, balance...
> I want to do a select which can return me debit, credit and balance for
> all the Account in the table.
> Something like
> select debit, credit, balance from Tab1.
> But the diffculty is each debit, credit and balance are in a different
> row. How can I merge many rows into a single select statement.
> Please advise me how I can do this
> Thank You
>|||dhani wrote:
> Hi All,
> I have question about how to do select on below table.
> This table has 3column,
> Col1->Account Id[Values 1, 2, 3, 4]
> Col2>Code [Values 1,2,3]
> Col3>Amount[Values 100,200,300,400]
> Each code maps to debit, credit, balance...
> I want to do a select which can return me debit, credit and balance
> for all the Account in the table.
> Something like
> select debit, credit, balance from Tab1.
> But the diffculty is each debit, credit and balance are in a different
> row. How can I merge many rows into a single select statement.
> Please advise me how I can do this
> Thank You
Your requirements are not clear.
Please read and comply with www.aspfaq.com/5006
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||The post by the user provides some information that should be able to help
him out.
Allthough I encourage the use of some 'standard' posting and
recommendations, just replying that the users requirements are not clear
and he should comply with etiquette that is not even requied by the
newsgroup doesn't make sense.
What the user wants is to pivot the table in this case.
Personally I would go for a different table design.
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:%234faQbWuFHA.3224@.TK2MSFTNGP10.phx.gbl...
> dhani wrote:
> Your requirements are not clear.
> Please read and comply with www.aspfaq.com/5006
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||> Allthough I encourage the use of some 'standard' posting and
> recommendations, just replying that the users requirements are not clear
> and he should comply with etiquette that is not even requied by the
> newsgroup doesn't make sense.
Sure it does (and "make sense" is very subjective, isn't it? Just because
SELECT * and a 9-page stored procedure makes sense to you, doesn't mean we
all share that opinion). The requirements were not clear, so Bob asked for
further clarification, and showed the user a quite helpful link in providing
requirements in a generally accepted format here.
Nobody said it was required by the newsgroup (what is required by the
newsgroup anyway, I have no idea what you're talking about). But you'll see
this follow-up a lot, and I can only assume from you lack of visibility here
that it's just a fact of life in here that you haven't yet been exposed to
enough to understand.
21 letters. Wow.|||So you are judging my knowlegde?
I don't think we ever met and I would definetely not judge you, knowing that
you are a respected MVP.
Did you use a user defined function to count the letters in my
certification?
So why do you conclude I have a lack of visibility and what fact of life
have I not been exposed to?
Don't you think it would be better to just share knowledge in order to
provide solutions and results?
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewD9UjXuFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Sure it does (and "make sense" is very subjective, isn't it? Just because
> SELECT * and a 9-page stored procedure makes sense to you, doesn't mean we
> all share that opinion). The requirements were not clear, so Bob asked
> for further clarification, and showed the user a quite helpful link in
> providing requirements in a generally accepted format here.
> Nobody said it was required by the newsgroup (what is required by the
> newsgroup anyway, I have no idea what you're talking about). But you'll
> see this follow-up a lot, and I can only assume from you lack of
> visibility here that it's just a fact of life in here that you haven't yet
> been exposed to enough to understand.
> 21 letters. Wow.
>|||> Don't you think it would be better to just share knowledge in order to
> provide solutions and results?
Yes, and part of that knowledge is learning how to post requirements that
work well, are easily re-create-able and test-able, and are requested.
You judged Bob's post and said it didn't make sense. I disagree. Period.|||Dandy Weyn [Dandyman] wrote:
> The post by the user provides some information that should be able to
> help him out.
I'm not trying to be combative or insulting, but, if the post contained
sufficient information, then why didn't you provide a solution?
Look, Alejandro made an attempt based on his guess as to what the user
needed. It may even be the correct solution. The question is: Who knows? I
could have made a similar attempt, only to have the OP come back and say:
"no that's not what I wanted at all", in which case both my time and the
OP's were wasted.

> Allthough I encourage the use of some 'standard' posting and
> recommendations, just replying that the users requirements are not
> clear and he should comply
Well, maybe "comply" was a poor choice of words: I meant "follow the
recommendations made" but I was a little strapped for time and was trying to
be concise.

> with etiquette that is not even requied by
I think if you use google, you will fiind that a large percentage of posts
to this group, as well as the comp.databases.ms-sqlserver group, receive a
similar initial request to provide DDL and sample data. This is usenet,
there is no way to "require" etiquette. That does not mean we should not
make an effort to help posters get help as efficiently as possible by
showing them the best way to provide the information needed to solve their
problems. Aaron's article does a good job of that. He put it in his
"ettiquette" section, but that does not mean this has anything to do with
"ettiquette".

> the newsgroup doesn't make sense.
Why not? What else should I do when the post contains insufficient
information to provide a solution? Waste my time and the OP's by making a
guess? Ignore the message (how does that help the OP)?
> What the user wants is to pivot the table in this case.
Maybe. In fact, I will even go so far as to say "probably". But, does he
need a dynamic pivot? Or does he have static values on which to base a
non-dynamic pivot?

> Personally I would go for a different table design.
Really? Based on what? You know very little about the OP's requirements ...
And why don't you think a vague statement about a "different table design"
is as bad as a request for clarification?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Merging two reports into one excel workbook.

Hallo all,
Its vey urgent for me ,please help me to solve the problem.
I am having two reports in a report project. when exporting to excel , I
have to export these two reports into a single excel workbook. Each report
should be exported to different sheets in same workbook. How is it possible?
Please help me.
I am really badly in need of help
thanks in advance to all
miikaOk, it can be done. Place a Subreport control in the main report (Report1)
and refer the other report(Report2) and place a Rectangle on the subreport
and on the properties check "insert before rectangle" there by it gives
pagination, which is the main thing for creating multiple sheets. now export
the main report you get both the reports in a single workbook but 2 sheets.
Check out.
Amarnath, MCTS.
"aka" wrote:
> Hallo all,
> Its vey urgent for me ,please help me to solve the problem.
> I am having two reports in a report project. when exporting to excel , I
> have to export these two reports into a single excel workbook. Each report
> should be exported to different sheets in same workbook. How is it possible?
> Please help me.
> I am really badly in need of help
> thanks in advance to all
> miika|||Hi,
Thanks alot for your valuable suggestion.
It worked fine, but I am having parameters in the two reports.
So If I make any one of the two as main report,and the second one that is
declared as subreport cannot be run as the parameter is null.It is gtting
exported into same excel workbook in two dofferent sheets. The sub report is
displaying only header part as the parameter is null.
How can I deal with parameters?
Can you please reply be as soon as possible.
Its very urgent to fix this task.
thanks in advance
miika
"Amarnath" wrote:
> Ok, it can be done. Place a Subreport control in the main report (Report1)
> and refer the other report(Report2) and place a Rectangle on the subreport
> and on the properties check "insert before rectangle" there by it gives
> pagination, which is the main thing for creating multiple sheets. now export
> the main report you get both the reports in a single workbook but 2 sheets.
> Check out.
> Amarnath, MCTS.
> "aka" wrote:
> > Hallo all,
> > Its vey urgent for me ,please help me to solve the problem.
> > I am having two reports in a report project. when exporting to excel , I
> > have to export these two reports into a single excel workbook. Each report
> > should be exported to different sheets in same workbook. How is it possible?
> > Please help me.
> > I am really badly in need of help
> >
> > thanks in advance to all
> > miika|||And also When exported to excel,in excel sheet ,I am having the main report
page header in the subreport also.
How can I avoid the main report page header in the subreport sheet?
Can you please help me?
I also want to have the sheet1 and sheet2 names as report names.
not simply sheet1 and sheet2.
IS it possible?
Please help me as soon as possible.
waiting for your suggestions
miika
"aka" wrote:
> Hi,
> Thanks alot for your valuable suggestion.
> It worked fine, but I am having parameters in the two reports.
> So If I make any one of the two as main report,and the second one that is
> declared as subreport cannot be run as the parameter is null.It is gtting
> exported into same excel workbook in two dofferent sheets. The sub report is
> displaying only header part as the parameter is null.
> How can I deal with parameters?
> Can you please reply be as soon as possible.
> Its very urgent to fix this task.
> thanks in advance
> miika
>
> "Amarnath" wrote:
> > Ok, it can be done. Place a Subreport control in the main report (Report1)
> > and refer the other report(Report2) and place a Rectangle on the subreport
> > and on the properties check "insert before rectangle" there by it gives
> > pagination, which is the main thing for creating multiple sheets. now export
> > the main report you get both the reports in a single workbook but 2 sheets.
> > Check out.
> >
> > Amarnath, MCTS.
> >
> > "aka" wrote:
> >
> > > Hallo all,
> > > Its vey urgent for me ,please help me to solve the problem.
> > > I am having two reports in a report project. when exporting to excel , I
> > > have to export these two reports into a single excel workbook. Each report
> > > should be exported to different sheets in same workbook. How is it possible?
> > > Please help me.
> > > I am really badly in need of help
> > >
> > > thanks in advance to all
> > > miika|||Aka, sorry for the late reply. You need to have group header in subreport so
that you dont have to rely on the main header. Unfortunately you dont get the
sheet's name. it will be sheet1, sheet2 etc... If the parameters are similiar
then you can select the parameter from the subreport properties from the
expression. if not then you need to hard code it, there is no way.
Amarnath,MCTS
"aka" wrote:
> And also When exported to excel,in excel sheet ,I am having the main report
> page header in the subreport also.
> How can I avoid the main report page header in the subreport sheet?
> Can you please help me?
> I also want to have the sheet1 and sheet2 names as report names.
> not simply sheet1 and sheet2.
> IS it possible?
> Please help me as soon as possible.
> waiting for your suggestions
> miika
>
> "aka" wrote:
> > Hi,
> > Thanks alot for your valuable suggestion.
> > It worked fine, but I am having parameters in the two reports.
> > So If I make any one of the two as main report,and the second one that is
> > declared as subreport cannot be run as the parameter is null.It is gtting
> > exported into same excel workbook in two dofferent sheets. The sub report is
> > displaying only header part as the parameter is null.
> > How can I deal with parameters?
> > Can you please reply be as soon as possible.
> > Its very urgent to fix this task.
> > thanks in advance
> > miika
> >
> >
> > "Amarnath" wrote:
> >
> > > Ok, it can be done. Place a Subreport control in the main report (Report1)
> > > and refer the other report(Report2) and place a Rectangle on the subreport
> > > and on the properties check "insert before rectangle" there by it gives
> > > pagination, which is the main thing for creating multiple sheets. now export
> > > the main report you get both the reports in a single workbook but 2 sheets.
> > > Check out.
> > >
> > > Amarnath, MCTS.
> > >
> > > "aka" wrote:
> > >
> > > > Hallo all,
> > > > Its vey urgent for me ,please help me to solve the problem.
> > > > I am having two reports in a report project. when exporting to excel , I
> > > > have to export these two reports into a single excel workbook. Each report
> > > > should be exported to different sheets in same workbook. How is it possible?
> > > > Please help me.
> > > > I am really badly in need of help
> > > >
> > > > thanks in advance to all
> > > > miika

Merging two reports at runtime..

How do I merge two or more reports at runtime?
For example :
I have reports (or pages) A, B and C. The user chose to print pages A and C
only. I will have to merge A and C and print them as one report.
Thanks in advance for any suggestions/solutions.
-SurendraTake a look at http://blogs.msdn.com/bryanke/articles/71491.aspx. You could
use this code in a custom app, have the users pass report names as
parameters and then use these parameters in your code to print the
corresponding reports accordingly.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Surendra" <spepakay@.hotmail.com> wrote in message
news:%23iEJB7GiEHA.3988@.tk2msftngp13.phx.gbl...
> How do I merge two or more reports at runtime?
> For example :
> I have reports (or pages) A, B and C. The user chose to print pages A and
C
> only. I will have to merge A and C and print them as one report.
> Thanks in advance for any suggestions/solutions.
> -Surendra
>|||Thanks for the suggestion Ravi!!
That was one option I had considered and was looking for a way to accomplish
this via the URL. Maybe I am being too naive and ambitious. Please let me
know if you have any other suggestions.
Thanks again
Surendra
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:OdCg6tIiEHA.3944@.tk2msftngp13.phx.gbl...
> Take a look at http://blogs.msdn.com/bryanke/articles/71491.aspx. You
could
> use this code in a custom app, have the users pass report names as
> parameters and then use these parameters in your code to print the
> corresponding reports accordingly.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Surendra" <spepakay@.hotmail.com> wrote in message
> news:%23iEJB7GiEHA.3988@.tk2msftngp13.phx.gbl...
> > How do I merge two or more reports at runtime?
> >
> > For example :
> >
> > I have reports (or pages) A, B and C. The user chose to print pages A
and
> C
> > only. I will have to merge A and C and print them as one report.
> >
> > Thanks in advance for any suggestions/solutions.
> > -Surendra
> >
> >
>

merging two mssql db

I have a problem that I need help on.

Right now, I have two MSSQL server and I am trying to merge them into one.

The problem is, I do not know the login Id and password for some of the users.

I know the login information are stored under syslogins at the master db.

How do I get the information out and append to the syslogins table of the second mssql db?

Any help would be appreciated.

--alucarrdTry to use linked server and query like this:

insert sysxlogins
(srvid,sid,xstatus,xdate1,xdate2,name,password,dbi d,language)
select srvid,sid,xstatus,xdate1,xdate2,name,password,dbid ,language
from remote.master.dbo.sysxlogins
where name='name'-- if not all of them|||Thank you snail,

I can smile now.

However, I tried with this statement, and now I am getting an error:

Cannot insert duplicate key row in object 'sysxlogins' with unique index 'sysxlogins'.
The statement has been terminated.

I got the linked server set up correctly and I can see the information of the remote server by checking the select statement on the sysxlogins table in the remote server.

However, I ran the script you showed me, and that's the error I got.

This is the script that I ran:

insert sysxlogins(srvid,sid,xstatus,xdate1,xdate2,name,
password,dbid,language)select srvid,sid,xstatus,xdate1,xdate2,
name,password,dbid,language
from remote1.master.dbo.sysxlogins where name<>'sa'

Thank you for helping me out.|||I think the reason I got the error is because the user exist already on the destination machine.

Thank you.

I will just go from here.

Merging two dbs

Hi
I have two sql server 2005 dbs on server A. I need to copy all items
(tables, view, sps and so on) from the two dbs into a single db on server B.
The added problem is that server B is SQL Server 2000 so direct copy is
perhaps not possible. How can I achieve this?
Thanks
RegardsYou can script out all the objects and try running on the 2000 Server|||The easiest way would be to add an instance of 2005 on that server and
simply copy them<g>. But assuming that is not an option and you have not
used any 2005 specific features or datatypes you can still do it. First
script all the objects using SSIS and then run those scripts in the other
db. Then export the data from the 2005 dbs and import them into the tables
in the 200 instance. Make sure to backup everything first.
Andrew J. Kelly SQL MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23AinXisUGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have two sql server 2005 dbs on server A. I need to copy all items
> (tables, view, sps and so on) from the two dbs into a single db on server
> B. The added problem is that server B is SQL Server 2000 so direct copy is
> perhaps not possible. How can I achieve this?
> Thanks
> Regards
>
>

Merging two dbs

Hi
I have two sql server 2005 dbs on server A. I need to copy all items
(tables, view, sps and so on) from the two dbs into a single db on server B.
The added problem is that server B is SQL Server 2000 so direct copy is
perhaps not possible. How can I achieve this?
Thanks
Regards
The easiest way would be to add an instance of 2005 on that server and
simply copy them<g>. But assuming that is not an option and you have not
used any 2005 specific features or datatypes you can still do it. First
script all the objects using SSIS and then run those scripts in the other
db. Then export the data from the 2005 dbs and import them into the tables
in the 200 instance. Make sure to backup everything first.
Andrew J. Kelly SQL MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23AinXisUGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have two sql server 2005 dbs on server A. I need to copy all items
> (tables, view, sps and so on) from the two dbs into a single db on server
> B. The added problem is that server B is SQL Server 2000 so direct copy is
> perhaps not possible. How can I achieve this?
> Thanks
> Regards
>
>

Merging two dbs

Hi
I have two sql server 2005 dbs on server A. I need to copy all items
(tables, view, sps and so on) from the two dbs into a single db on server B.
The added problem is that server B is SQL Server 2000 so direct copy is
perhaps not possible. How can I achieve this?
Thanks
Regards
The easiest way would be to add an instance of 2005 on that server and
simply copy them<g>. But assuming that is not an option and you have not
used any 2005 specific features or datatypes you can still do it. First
script all the objects using SSIS and then run those scripts in the other
db. Then export the data from the 2005 dbs and import them into the tables
in the 200 instance. Make sure to backup everything first.
Andrew J. Kelly SQL MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23AinXisUGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have two sql server 2005 dbs on server A. I need to copy all items
> (tables, view, sps and so on) from the two dbs into a single db on server
> B. The added problem is that server B is SQL Server 2000 so direct copy is
> perhaps not possible. How can I achieve this?
> Thanks
> Regards
>
>

Merging two dbs

Hi
I have two sql server 2005 dbs on server A. I need to copy all items
(tables, view, sps and so on) from the two dbs into a single db on server B.
The added problem is that server B is SQL Server 2000 so direct copy is
perhaps not possible. How can I achieve this?
Thanks
RegardsThe easiest way would be to add an instance of 2005 on that server and
simply copy them<g>. But assuming that is not an option and you have not
used any 2005 specific features or datatypes you can still do it. First
script all the objects using SSIS and then run those scripts in the other
db. Then export the data from the 2005 dbs and import them into the tables
in the 200 instance. Make sure to backup everything first.
Andrew J. Kelly SQL MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23AinXisUGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have two sql server 2005 dbs on server A. I need to copy all items
> (tables, view, sps and so on) from the two dbs into a single db on server
> B. The added problem is that server B is SQL Server 2000 so direct copy is
> perhaps not possible. How can I achieve this?
> Thanks
> Regards
>
>

merging two databases into single one

Hi,
I have two databases.i would like to joing these two databases
into single one.How should i do it?
Example:
I have database named DatabaseA and its size was increased very high
so i renamed to DatabaseA_old & create new database DatabaseA.I have
upgraded the server and increased the database size.Now i would like
to make a single database.
Please help me.
Regards,
Vijay
Create a new Database and use Import/Export Wizard to Import the Data
from database DatabaseA_old .
Well you have to attach the DatabaseA_old first and then do a import.
|||That depends on the exact nature of your 'merge'. There is no single command
to accomplish the 'merge'. But in general you need to script out all the
objects in your old database, create them in the new database--if not already
there, and insert the data from the old database into the corresponding
tables in the new database.
Inserting data may be done with INSERT, SELECT INTO, or bulk copy out
followed by bulk copy in.
It's a good practice to script out the entire 'merge' procedure.
Linchi
"Vijay" wrote:

> Hi,
> I have two databases.i would like to joing these two databases
> into single one.How should i do it?
> Example:
> I have database named DatabaseA and its size was increased very high
> so i renamed to DatabaseA_old & create new database DatabaseA.I have
> upgraded the server and increased the database size.Now i would like
> to make a single database.
> Please help me.
> Regards,
> Vijay
>

merging two databases into single one

Hi,
I have two databases.i would like to joing these two databases
into single one.How should i do it?
Example:
I have database named DatabaseA and its size was increased very high
so i renamed to DatabaseA_old & create new database DatabaseA.I have
upgraded the server and increased the database size.Now i would like
to make a single database.
Please help me.
Regards,
VijayCreate a new Database and use Import/Export Wizard to Import the Data
from database DatabaseA_old .
Well you have to attach the DatabaseA_old first and then do a import.|||That depends on the exact nature of your 'merge'. There is no single command
to accomplish the 'merge'. But in general you need to script out all the
objects in your old database, create them in the new database--if not alread
y
there, and insert the data from the old database into the corresponding
tables in the new database.
Inserting data may be done with INSERT, SELECT INTO, or bulk copy out
followed by bulk copy in.
It's a good practice to script out the entire 'merge' procedure.
Linchi
"Vijay" wrote:

> Hi,
> I have two databases.i would like to joing these two databases
> into single one.How should i do it?
> Example:
> I have database named DatabaseA and its size was increased very high
> so i renamed to DatabaseA_old & create new database DatabaseA.I have
> upgraded the server and increased the database size.Now i would like
> to make a single database.
> Please help me.
> Regards,
> Vijay
>

merging two databases into single one

Hi,
I have two databases.i would like to joing these two databases
into single one.How should i do it?
Example:
I have database named DatabaseA and its size was increased very high
so i renamed to DatabaseA_old & create new database DatabaseA.I have
upgraded the server and increased the database size.Now i would like
to make a single database.
Please help me.
Regards,
VijayCreate a new Database and use Import/Export Wizard to Import the Data
from database DatabaseA_old .
Well you have to attach the DatabaseA_old first and then do a import.|||That depends on the exact nature of your 'merge'. There is no single command
to accomplish the 'merge'. But in general you need to script out all the
objects in your old database, create them in the new database--if not already
there, and insert the data from the old database into the corresponding
tables in the new database.
Inserting data may be done with INSERT, SELECT INTO, or bulk copy out
followed by bulk copy in.
It's a good practice to script out the entire 'merge' procedure.
Linchi
"Vijay" wrote:
> Hi,
> I have two databases.i would like to joing these two databases
> into single one.How should i do it?
> Example:
> I have database named DatabaseA and its size was increased very high
> so i renamed to DatabaseA_old & create new database DatabaseA.I have
> upgraded the server and increased the database size.Now i would like
> to make a single database.
> Please help me.
> Regards,
> Vijay
>

Merging two databases into one

I currently have two databases which contain the same database structure
but different data. How do I merge the two databases into one single
database?
*** Sent via Developersdex http://www.developersdex.com ***You can do it using T-SQL queries.
How do you want to merge the data? Just copy all the data from dbA to dbB or
all the data from dbB to dbA. Do you have to resolve any conflicts? that is,
could the same key can have different data in different databases?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Enoch Chum" <enoch.chum@.wcb.ab.ca> wrote in message
news:ucNXVxIlFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I currently have two databases which contain the same database structure
> but different data. How do I merge the two databases into one single
> database?
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Hi,
To add on to Vyas, Incase if you have same table definitions in both
databases and different data then use:-
Insert into DBnameA..Tablename select * from DBnameB..tablename where
condition...
Do the above for all tables.. In your query take care of constraints as well
as identity.
Thanks
Hari
SQL Server MVP
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGmJfFPlFHA.2860@.TK2MSFTNGP15.phx.gbl...
> You can do it using T-SQL queries.
> How do you want to merge the data? Just copy all the data from dbA to dbB
> or all the data from dbB to dbA. Do you have to resolve any conflicts?
> that is, could the same key can have different data in different
> databases?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Enoch Chum" <enoch.chum@.wcb.ab.ca> wrote in message
> news:ucNXVxIlFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I currently have two databases which contain the same database structure
>> but different data. How do I merge the two databases into one single
>> database?
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>

Merging two databases into one

I currently have two databases which contain the same database structure
but different data. How do I merge the two databases into one single
database?
*** Sent via Developersdex http://www.codecomments.com ***
You can do it using T-SQL queries.
How do you want to merge the data? Just copy all the data from dbA to dbB or
all the data from dbB to dbA. Do you have to resolve any conflicts? that is,
could the same key can have different data in different databases?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Enoch Chum" <enoch.chum@.wcb.ab.ca> wrote in message
news:ucNXVxIlFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I currently have two databases which contain the same database structure
> but different data. How do I merge the two databases into one single
> database?
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Hi,
To add on to Vyas, Incase if you have same table definitions in both
databases and different data then use:-
Insert into DBnameA..Tablename select * from DBnameB..tablename where
condition...
Do the above for all tables.. In your query take care of constraints as well
as identity.
Thanks
Hari
SQL Server MVP
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGmJfFPlFHA.2860@.TK2MSFTNGP15.phx.gbl...
> You can do it using T-SQL queries.
> How do you want to merge the data? Just copy all the data from dbA to dbB
> or all the data from dbB to dbA. Do you have to resolve any conflicts?
> that is, could the same key can have different data in different
> databases?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Enoch Chum" <enoch.chum@.wcb.ab.ca> wrote in message
> news:ucNXVxIlFHA.1968@.TK2MSFTNGP14.phx.gbl...
>

Merging two databases into one

I currently have two databases which contain the same database structure
but different data. How do I merge the two databases into one single
database?
*** Sent via Developersdex http://www.codecomments.com ***You can do it using T-SQL queries.
How do you want to merge the data? Just copy all the data from dbA to dbB or
all the data from dbB to dbA. Do you have to resolve any conflicts? that is,
could the same key can have different data in different databases?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Enoch Chum" <enoch.chum@.wcb.ab.ca> wrote in message
news:ucNXVxIlFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I currently have two databases which contain the same database structure
> but different data. How do I merge the two databases into one single
> database?
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Hi,
To add on to Vyas, Incase if you have same table definitions in both
databases and different data then use:-
Insert into DBnameA..Tablename select * from DBnameB..tablename where
condition...
Do the above for all tables.. In your query take care of constraints as well
as identity.
Thanks
Hari
SQL Server MVP
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGmJfFPlFHA.2860@.TK2MSFTNGP15.phx.gbl...
> You can do it using T-SQL queries.
> How do you want to merge the data? Just copy all the data from dbA to dbB
> or all the data from dbB to dbA. Do you have to resolve any conflicts?
> that is, could the same key can have different data in different
> databases?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Enoch Chum" <enoch.chum@.wcb.ab.ca> wrote in message
> news:ucNXVxIlFHA.1968@.TK2MSFTNGP14.phx.gbl...
>