data synchronization accross multiple servers running replication

Hello,

We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.

In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?

Thanks,

Phil

In SQL Server 2005 there is a tablediff tool which you can use to compare the tables. You can also use the validation options built in replication to verify if the data are in sync.

Here are the validation options:

In transactional replication: sp_publication_validation

In merge replication: use the -validate parameter for merge agent

|||

Li,

Thanks for the reply. I believe we can use this. Do you know how well it does as far as speed is concerned? I think that's our main concern right now. Also, I'm reading a bit on the uitlity and I'm wondering how does it take a "snapshot" of a table on multiple servers and compare that. Also you wouldn't happen to have any syntax examples would you?

Thanks,

Phil

|||

Also, does anyone know if there is a way to get the utility to use windows authentication instead of coding the sa password in the utility statement?

Thanks,

Phil

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

Hi

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

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

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

best regards

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

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

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

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

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

' Read the rows in...

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

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

For i As Int = i < 10

With OutputBuffer

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

.Asset = Row.InputColumn

.Product = i

Next

End While

If Row.EndOfRowset Then

OutputBuffer.SetEndOfRowset()

End If

End Sub

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

regards,

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

DarrenSQLIS wrote:

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

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

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

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

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

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

I'll keep you posted!

thanks again for the input

Thibaut Barrère|||Hi

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

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

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

cheers

Thibaut

Data Retrieval

Our production environment has multiple SQL Servers installed and running and
i need to retrieve data from multiple different servers and databases. We
have established multiple Web Service DAL's to retrieve data for out
applications but i cannot see how to leverage that into Reporting Services.
Any ideas?
Thanks in advance
Goaler...Read up on data processing extentions. That should fullfill your needs.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"goaler" <goaler@.discussions.microsoft.com> wrote in message
news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> Our production environment has multiple SQL Servers installed and running
and
> i need to retrieve data from multiple different servers and databases. We
> have established multiple Web Service DAL's to retrieve data for out
> applications but i cannot see how to leverage that into Reporting
Services.
> Any ideas?
> Thanks in advance
> Goaler...|||Thaks Bruce
"Bruce L-C [MVP]" wrote:
> Read up on data processing extentions. That should fullfill your needs.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "goaler" <goaler@.discussions.microsoft.com> wrote in message
> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> > Our production environment has multiple SQL Servers installed and running
> and
> > i need to retrieve data from multiple different servers and databases. We
> > have established multiple Web Service DAL's to retrieve data for out
> > applications but i cannot see how to leverage that into Reporting
> Services.
> >
> > Any ideas?
> >
> > Thanks in advance
> > Goaler...
>
>|||As a follow-up, I think i need a bit more clarification and understanding.
Here is the issue
I need to be able to read multiple predefined shared data sources from
within MS-Reporting Services, to avoid the configuration of connection
information out side of the product. Then to combine the result set and
return it into a new report object.
There must be a way to do this, the designer allows you to create multiple
data sources but you cannot join the data together. Additionally, linked
servers are not an option in my environment.
Thanks again,
"Bruce L-C [MVP]" wrote:
> Read up on data processing extentions. That should fullfill your needs.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "goaler" <goaler@.discussions.microsoft.com> wrote in message
> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> > Our production environment has multiple SQL Servers installed and running
> and
> > i need to retrieve data from multiple different servers and databases. We
> > have established multiple Web Service DAL's to retrieve data for out
> > applications but i cannot see how to leverage that into Reporting
> Services.
> >
> > Any ideas?
> >
> > Thanks in advance
> > Goaler...
>
>|||You can not join datasets together.
Your clarification confused me, I thought you had web services that you
wanted to use to return the data? If so then data processing extensions are
the way to go.
Bruce Loehle-Conger
"goaler" <goaler@.discussions.microsoft.com> wrote in message
news:6C959BD5-C681-4E15-96EE-78EA69C282BA@.microsoft.com...
> As a follow-up, I think i need a bit more clarification and understanding.
> Here is the issue
> I need to be able to read multiple predefined shared data sources from
> within MS-Reporting Services, to avoid the configuration of connection
> information out side of the product. Then to combine the result set and
> return it into a new report object.
> There must be a way to do this, the designer allows you to create multiple
> data sources but you cannot join the data together. Additionally, linked
> servers are not an option in my environment.
> Thanks again,
> "Bruce L-C [MVP]" wrote:
>> Read up on data processing extentions. That should fullfill your needs.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "goaler" <goaler@.discussions.microsoft.com> wrote in message
>> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
>> > Our production environment has multiple SQL Servers installed and
>> > running
>> and
>> > i need to retrieve data from multiple different servers and databases.
>> > We
>> > have established multiple Web Service DAL's to retrieve data for out
>> > applications but i cannot see how to leverage that into Reporting
>> Services.
>> >
>> > Any ideas?
>> >
>> > Thanks in advance
>> > Goaler...
>>

Data Modeling: Type + SubTypes

Suppose I have a database that includes 5 different "types" of people, and
each person could simultaneously be of multiple types (the types don't
really matter... so pretend it has doctors, lawyers, janitors, students, and
customers).
My Question: In general, is it perfectly acceptable in a scenario like this
to have one People table for common attributes and then 5 additional "person
type-specific" tables (e.g., a Doctors table, Lawyers, Janitors, Students,
and Customers tables) that each hold type-specific attributes?
Or is there a *general* recommendation to go with 5 tables total and
duplicate the common attributes amongst the tables.
Please understand my objective is to come up with a solid relational
database design - NOT simply to emulate the object-oriented programming
concept of inheritance in my database; and yes, I do understand the issues
related to choice of primary key (my plan is to use an IDENTITY property to
glue the People table with the various "person type" tables, and then have a
UNIQUE constraint on the "real" natural keys per "person type").
Thanks!"Jefffff" <A@.B.COM> wrote in message
news:edGY0teUGHA.2704@.tk2msftngp13.phx.gbl...
> Suppose I have a database that includes 5 different "types" of people, and
> each person could simultaneously be of multiple types (the types don't
> really matter... so pretend it has doctors, lawyers, janitors, students,
> and customers).
> My Question: In general, is it perfectly acceptable in a scenario like
> this to have one People table for common attributes and then 5 additional
> "person type-specific" tables (e.g., a Doctors table, Lawyers, Janitors,
> Students, and Customers tables) that each hold type-specific attributes?
> Or is there a *general* recommendation to go with 5 tables total and
> duplicate the common attributes amongst the tables.
> Please understand my objective is to come up with a solid relational
> database design - NOT simply to emulate the object-oriented programming
> concept of inheritance in my database; and yes, I do understand the issues
> related to choice of primary key (my plan is to use an IDENTITY property
> to glue the People table with the various "person type" tables, and then
> have a UNIQUE constraint on the "real" natural keys per "person type").
> Thanks!
>
>
Your first suggestion is better. The following article explains how and why
tables should not duplicate each other (more precisely, their predicates
should not overlap):
http://www.dbdebunk.com/page/page/622331.htm
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Why do I find that amusing :)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23CQjX2eUGHA.1564@.TK2MSFTNGP11.phx.gbl...
> http://www.dbdebunk.com|||Think about it this way and it might be clearer:
People are people and have attributes that describe them as a person (Name,
DOB, etc)
People can play different roles such as Doctor, Lawyer, etc
So, you would likely have a table named Person to store those attributes
that are about the person
Then, seperate tables for each role the person may play
"Jefffff" <A@.B.COM> wrote in message
news:edGY0teUGHA.2704@.tk2msftngp13.phx.gbl...
> Suppose I have a database that includes 5 different "types" of people, and
> each person could simultaneously be of multiple types (the types don't
> really matter... so pretend it has doctors, lawyers, janitors, students,
> and customers).
> My Question: In general, is it perfectly acceptable in a scenario like
> this to have one People table for common attributes and then 5 additional
> "person type-specific" tables (e.g., a Doctors table, Lawyers, Janitors,
> Students, and Customers tables) that each hold type-specific attributes?
> Or is there a *general* recommendation to go with 5 tables total and
> duplicate the common attributes amongst the tables.
> Please understand my objective is to come up with a solid relational
> database design - NOT simply to emulate the object-oriented programming
> concept of inheritance in my database; and yes, I do understand the issues
> related to choice of primary key (my plan is to use an IDENTITY property
> to glue the People table with the various "person type" tables, and then
> have a UNIQUE constraint on the "real" natural keys per "person type").
> Thanks!
>
>

Data loss in Transactional Replication

We have setup transactional replication across several databases using SQL Server 2000 spread across multiple sites in a fully connected network. There is one main table from which data is replicated from the publisher to the destination. Horizontal filtering is being used on this table to enable sending/routing of the records to the correct DB(site). It has been observed that the documents/records are getting lost between some sites. Say 10 documents are being sent fron the publishing database but only 5 are being received at the destination database although the sent history for all the 10 documents is available at the publishing database.

Can anyone guide on how to analyse and resolve this problem? Can unreliable network be the issue, If the network is not reliable and the connection is lost during replication how does replication ensure that no data is lost?

Hi,

If you didn't see replication reports error, unreliable network should not be the cause of data loss. Replication delivers the transaction with order. If it can't deliver it for whatever reason, it will keep retry or report error unless you explicitly specify skiperror.

To troubleshoot, you first need to see if your horizontal filter actually filters the data at publisher. You can also use sp_browsereplcmds to see if the transactions is in distribution DB. From here, you can determine if it is a logreader issue or distribution agnet issue.

Please let me know if you need more assistance.

Peng

Data Import from Multiple Tables

Hi all,
I am a newbie to .NET and would appreciate all your valublesuggestions. I have and issue were I am trying to import data from afew selected columns MS Access and a couple of columns in SQLServer Table Y and trying to populate another table X . Bothtables X and Y are in the same Database . I am wondering if I coulddesign a custom package for this task.
Ananth
There might be better ways to do this, but...
I would first create a temporary table, and take and dump all of the relevant Access data into that temporary table (via DTS). From there, joining and inserting into Table Y should be pretty easy. When you're done, delete the temporary table.
(And I say temporary, but not TEMP).
I'm sure there are more efficient ways of doing this, and of course it depends on how much data is in the access table. But probably the easiest way to create the dataset you will need for Table Y will be by using T-SQL. So having everything within the same database will make that easier.|||Thanks a lot for the suggestion. I appreciate it.

data from multiple tables

Hi
I have 6 databases and each has a table sys_items. My client asked me
to create a list of all the unique items from these tables from all
the databases.
select a. item, b, item, c.item, d.item, e.item, f.item
from a, b, c, d, e,f
there are a few common items in each table. so it means i want all the
common and unique items in my list.
any idea? suggestions?
please let me know
thanks
Ajit
Try:
select
a.item
from
DatabaseA.dbo.sys_items a
join DatabaseB.dbo.sys_items b on b.Item = a.Item
join DatabaseC.dbo.sys_items c on c.Item = a.Item
join DatabaseD.dbo.sys_items d on d.Item = a.Item
join DatabaseE.dbo.sys_items e on e.Item = a.Item
join DatabaseF.dbo.sys_items f on f.Item = a.Item
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184705626.221486.182570@.e16g2000pri.googlegr oups.com...
Hi
I have 6 databases and each has a table sys_items. My client asked me
to create a list of all the unique items from these tables from all
the databases.
select a. item, b, item, c.item, d.item, e.item, f.item
from a, b, c, d, e,f
there are a few common items in each table. so it means i want all the
common and unique items in my list.
any idea? suggestions?
please let me know
thanks
Ajit
|||I think he wants to find the items that are common to all DB's.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:GKednS7PK8RAsADbnZ2dnUVZ8sKlnZ2d@.giganews.com ...
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184705626.221486.182570@.e16g2000pri.googlegr oups.com...
> Hi
> I have 6 databases and each has a table sys_items. My client asked me
> to create a list of all the unique items from these tables from all
> the databases.
> select a. item, b, item, c.item, d.item, e.item, f.item
> from a, b, c, d, e,f
> there are a few common items in each table. so it means i want all the
> common and unique items in my list.
>
SELECT item FROM a.dbo.tbl UNION
SELECT item FROM b.dbo.tbl UNION
SELECT item FROM c.dbo.tbl UNION
SELECT item FROM d.dbo.tbl UNION
SELECT item FROM e.dbo.tbl UNION
SELECT item FROM f.dbo.tbl ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks guys.. I tried Union and it worked...

data from multiple tables

Hi
I have 6 databases and each has a table sys_items. My client asked me
to create a list of all the unique items from these tables from all
the databases.
select a. item, b, item, c.item, d.item, e.item, f.item
from a, b, c, d, e,f
there are a few common items in each table. so it means i want all the
common and unique items in my list.
any idea' suggestions'
please let me know
thanks
Ajit"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184705626.221486.182570@.e16g2000pri.googlegroups.com...
> Hi
> I have 6 databases and each has a table sys_items. My client asked me
> to create a list of all the unique items from these tables from all
> the databases.
> select a. item, b, item, c.item, d.item, e.item, f.item
> from a, b, c, d, e,f
> there are a few common items in each table. so it means i want all the
> common and unique items in my list.
>
SELECT item FROM a.dbo.tbl UNION
SELECT item FROM b.dbo.tbl UNION
SELECT item FROM c.dbo.tbl UNION
SELECT item FROM d.dbo.tbl UNION
SELECT item FROM e.dbo.tbl UNION
SELECT item FROM f.dbo.tbl ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Try:
select
a.item
from
DatabaseA.dbo.sys_items a
join DatabaseB.dbo.sys_items b on b.Item = a.Item
join DatabaseC.dbo.sys_items c on c.Item = a.Item
join DatabaseD.dbo.sys_items d on d.Item = a.Item
join DatabaseE.dbo.sys_items e on e.Item = a.Item
join DatabaseF.dbo.sys_items f on f.Item = a.Item
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184705626.221486.182570@.e16g2000pri.googlegroups.com...
Hi
I have 6 databases and each has a table sys_items. My client asked me
to create a list of all the unique items from these tables from all
the databases.
select a. item, b, item, c.item, d.item, e.item, f.item
from a, b, c, d, e,f
there are a few common items in each table. so it means i want all the
common and unique items in my list.
any idea' suggestions'
please let me know
thanks
Ajit|||I think he wants to find the items that are common to all DB's.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:GKednS7PK8RAsADbnZ2dnUVZ8sKlnZ2d@.gi
ganews.com...
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184705626.221486.182570@.e16g2000pri.googlegroups.com...
> Hi
> I have 6 databases and each has a table sys_items. My client asked me
> to create a list of all the unique items from these tables from all
> the databases.
> select a. item, b, item, c.item, d.item, e.item, f.item
> from a, b, c, d, e,f
> there are a few common items in each table. so it means i want all the
> common and unique items in my list.
>
SELECT item FROM a.dbo.tbl UNION
SELECT item FROM b.dbo.tbl UNION
SELECT item FROM c.dbo.tbl UNION
SELECT item FROM d.dbo.tbl UNION
SELECT item FROM e.dbo.tbl UNION
SELECT item FROM f.dbo.tbl ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks guys.. I tried Union and it worked...

data from multiple tables

Hi
I have 6 databases and each has a table sys_items. My client asked me
to create a list of all the unique items from these tables from all
the databases.
select a. item, b, item, c.item, d.item, e.item, f.item
from a, b, c, d, e,f
there are a few common items in each table. so it means i want all the
common and unique items in my list.
any idea' suggestions'
please let me know
thanks
Ajit"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184705626.221486.182570@.e16g2000pri.googlegroups.com...
> Hi
> I have 6 databases and each has a table sys_items. My client asked me
> to create a list of all the unique items from these tables from all
> the databases.
> select a. item, b, item, c.item, d.item, e.item, f.item
> from a, b, c, d, e,f
> there are a few common items in each table. so it means i want all the
> common and unique items in my list.
>
SELECT item FROM a.dbo.tbl UNION
SELECT item FROM b.dbo.tbl UNION
SELECT item FROM c.dbo.tbl UNION
SELECT item FROM d.dbo.tbl UNION
SELECT item FROM e.dbo.tbl UNION
SELECT item FROM f.dbo.tbl ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Try:
select
a.item
from
DatabaseA.dbo.sys_items a
join DatabaseB.dbo.sys_items b on b.Item = a.Item
join DatabaseC.dbo.sys_items c on c.Item = a.Item
join DatabaseD.dbo.sys_items d on d.Item = a.Item
join DatabaseE.dbo.sys_items e on e.Item = a.Item
join DatabaseF.dbo.sys_items f on f.Item = a.Item
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184705626.221486.182570@.e16g2000pri.googlegroups.com...
Hi
I have 6 databases and each has a table sys_items. My client asked me
to create a list of all the unique items from these tables from all
the databases.
select a. item, b, item, c.item, d.item, e.item, f.item
from a, b, c, d, e,f
there are a few common items in each table. so it means i want all the
common and unique items in my list.
any idea' suggestions'
please let me know
thanks
Ajit|||I think he wants to find the items that are common to all DB's.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:GKednS7PK8RAsADbnZ2dnUVZ8sKlnZ2d@.giganews.com...
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184705626.221486.182570@.e16g2000pri.googlegroups.com...
> Hi
> I have 6 databases and each has a table sys_items. My client asked me
> to create a list of all the unique items from these tables from all
> the databases.
> select a. item, b, item, c.item, d.item, e.item, f.item
> from a, b, c, d, e,f
> there are a few common items in each table. so it means i want all the
> common and unique items in my list.
>
SELECT item FROM a.dbo.tbl UNION
SELECT item FROM b.dbo.tbl UNION
SELECT item FROM c.dbo.tbl UNION
SELECT item FROM d.dbo.tbl UNION
SELECT item FROM e.dbo.tbl UNION
SELECT item FROM f.dbo.tbl ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks guys.. I tried Union and it worked...

Data from multiple table

I want 2 show my data from 2 table
suppose
table1:
x y Z
------
x1 y1 z1
x2 y2 z2
x3 y3 z3
table2:
A B
a1 b1
a2 b2

I hv add both table to my report

but the report is being created like this
x y z A B
x1 y1 z1 a1 b1
x2 y2 z2 a1 b1
x3 y3 z3 a1 b1
x1 y1 z1 a2 b2
x2 y2 z2 a2 b2
x3 y3 z3 a2 b2

but i want 2 see the repot as follows
x y z A B
x1 y1 z1 a1 b1
x2 y2 z2 a2 b2
x3 y3 z3

wht should i do
i hv inserted another detail section in report.bt did nt get result as i m expecting.

plz help me.You have placed two tables in the report without linking them. The result you are getting is the expected one.

If the tables have any relationship at all, link on this. Otherwise, it appears you just want the result sets to appear side by side - try using subreports.|||tnx for ur reply
yes i hv no relation ship in my table
but i hv a link btn the table

it will be helpful for me if u send any sample project by attchment
waiting for ur sample.

advance tnx 2 u

siraj|||>yes i hv no relation ship in my table
>but i hv a link btn the table

Please describe the links you have between the tables.|||Dear
tnx to ur response
i am sending the code where is my problem .
i think u will send the code as my expected form mentioned in my original problem.

waiting for ur solution

siraj

data from multiple databases

Hi
I have 6 databases and each has sys_items table. my client ask me to
create an Excel file with all the unique items from all these
databases.
e.g. get all the items from database1.sys_items then merge all the
unique items from database2.sys_items and so on
i hope you guys are getting my point.
Please advice
thanks
Ajit
I am trying to create a sql query and i tried to explain giving an
example. i.e. get all items from database1.sys_items and then get all
unique items from other databases with same table name.
i dont have any sql query at the moment and that is my question in
this post.
i am not getting any error as i dont have anything to execute at this
moment.
Thanks
Ajit
>
>
>
>
> Replied in the previous thread.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> -- Hide quoted text -
> - Show quoted text -

data from multiple databases

Hi
I have 6 databases and each has sys_items table. my client ask me to
create an Excel file with all the unique items from all these
databases.
e.g. get all the items from database1.sys_items then merge all the
unique items from database2.sys_items and so on
i hope you guys are getting my point.
Please advice
thanks
I answered this yesterday in this same newsgroup.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184780384.310317.296780@.m37g2000prh.googlegr oups.com...
Hi
I have 6 databases and each has sys_items table. my client ask me to
create an Excel file with all the unique items from all these
databases.
e.g. get all the items from database1.sys_items then merge all the
unique items from database2.sys_items and so on
i hope you guys are getting my point.
Please advice
thanks
|||On Wed, 18 Jul 2007 15:02:37 -0400, "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote:

>I answered this yesterday in this same newsgroup.
I think he is using Google Groups. When I checked a few minutes ago
nothing new appears there in .programming since mid-day yesterday.
Roy Harvey
Beacon Falls, CT

data from multiple databases

Hi
I have 6 databases and each has sys_items table. my client ask me to
create an Excel file with all the unique items from all these
databases.
e.g. get all the items from database1.sys_items then merge all the
unique items from database2.sys_items and so on
i hope you guys are getting my point.
Please advice
thanks
Ajit"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184706525.529792.94420@.o11g2000prd.googlegroups.com...
> Hi
> I have 6 databases and each has sys_items table. my client ask me to
> create an Excel file with all the unique items from all these
> databases.
> e.g. get all the items from database1.sys_items then merge all the
> unique items from database2.sys_items and so on
> i hope you guys are getting my point.
> Please advice
> thanks
> Ajit
>
Replied in the previous thread.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I am trying to create a sql query and i tried to explain giving an
example. i.e. get all items from database1.sys_items and then get all
unique items from other databases with same table name.
i dont have any sql query at the moment and that is my question in
this post.
i am not getting any error as i dont have anything to execute at this
moment.
Thanks
Ajit
>
>
>
>
>
>
>
> Replied in the previous thread.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,
SQL.90).aspx
> -- Hide quoted text -
> - Show quoted text -

data from multiple databases

Hi
I have 6 databases and each has sys_items table. my client ask me to
create an Excel file with all the unique items from all these
databases.
e.g. get all the items from database1.sys_items then merge all the
unique items from database2.sys_items and so on
i hope you guys are getting my point.
Please advice
thanksI answered this yesterday in this same newsgroup.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184780384.310317.296780@.m37g2000prh.googlegroups.com...
Hi
I have 6 databases and each has sys_items table. my client ask me to
create an Excel file with all the unique items from all these
databases.
e.g. get all the items from database1.sys_items then merge all the
unique items from database2.sys_items and so on
i hope you guys are getting my point.
Please advice
thanks|||On Wed, 18 Jul 2007 15:02:37 -0400, "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote:

>I answered this yesterday in this same newsgroup.
I think he is using Google Groups. When I checked a few minutes ago
nothing new appears there in .programming since mid-day yesterday.
Roy Harvey
Beacon Falls, CT

data from multiple databases

Hi
I have 6 databases and each has sys_items table. my client ask me to
create an Excel file with all the unique items from all these
databases.
e.g. get all the items from database1.sys_items then merge all the
unique items from database2.sys_items and so on
i hope you guys are getting my point.
Please advice
thanksI answered this yesterday in this same newsgroup.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184780384.310317.296780@.m37g2000prh.googlegroups.com...
Hi
I have 6 databases and each has sys_items table. my client ask me to
create an Excel file with all the unique items from all these
databases.
e.g. get all the items from database1.sys_items then merge all the
unique items from database2.sys_items and so on
i hope you guys are getting my point.
Please advice
thanks|||On Wed, 18 Jul 2007 15:02:37 -0400, "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote:
>I answered this yesterday in this same newsgroup.
I think he is using Google Groups. When I checked a few minutes ago
nothing new appears there in .programming since mid-day yesterday.
Roy Harvey
Beacon Falls, CT

data from multiple databases

Hi
I have 6 databases and each has sys_items table. my client ask me to
create an Excel file with all the unique items from all these
databases.
e.g. get all the items from database1.sys_items then merge all the
unique items from database2.sys_items and so on
i hope you guys are getting my point.
Please advice
thanks
Ajit"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184706525.529792.94420@.o11g2000prd.googlegroups.com...
> Hi
> I have 6 databases and each has sys_items table. my client ask me to
> create an Excel file with all the unique items from all these
> databases.
> e.g. get all the items from database1.sys_items then merge all the
> unique items from database2.sys_items and so on
> i hope you guys are getting my point.
> Please advice
> thanks
> Ajit
>
Replied in the previous thread.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I am trying to create a sql query and i tried to explain giving an
example. i.e. get all items from database1.sys_items and then get all
unique items from other databases with same table name.
i dont have any sql query at the moment and that is my question in
this post.
i am not getting any error as i dont have anything to execute at this
moment.
Thanks
Ajit
> > Hi
> > I have 6 databases and each has sys_items table. my client ask me to
> > create an Excel file with all the unique items from all these
> > databases.
> > e.g. get all the items from database1.sys_items then merge all the
> > unique items from database2.sys_items and so on
> > i hope you guys are getting my point.
> > Please advice
> > thanks
> > Ajit
> Replied in the previous thread.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> -- Hide quoted text -
> - Show quoted text -

Data Flow: Converting data in multiple columns

Hi,

I'm just wondering what's the best approach in Data Flow to convert the following input file format:

Date, Code1, Value1, Code2, Value2

1-Jan-2006, abc1, 20.00, xyz3, 35.00

2-Jan-2006, abc1, 30.00, xyz5, 6.30

into the following output format (to be loaded into a SQL DB):

Date, Code, Value

1-Jan-2006, abc1, 20.00

1-Jan-2006, xyz3, 35.00

2-Jan-2006, abc1, 30.00

2-Jan-2006, xyz5, 6.30

I'm quite new to SSIS, so, I would appreciate detailed steps if possible. Thanks.

Ok, I have found a method to get what I wanted, but I'm not sure if it's the best approach. Any comments appreciated.

I first used a Multicast and feed the input data into 2 Script Components. The first Script Component has input columns of Date, Code1 & Value1, while 2nd Script Component has input columns of Date, Code2 & Value2. In both Script Components, the output columns are Date, Code & Value.

The output of each Script Component are then connected to a Sort and both Sort goes to a Merge. The output from the Merge will then go to a OLE DB Destination to be loaded into a SQL DB (not 2005 version).

Hope it makes sense.

|||

I think Unpivot tranformation cam deliver the same funcionality you put in your script components; but the multicast; the sort and merge join would be required anyway.

Rafael Salas

|||

Hi,

Why dont you have a union step feeded twice by the multicast

then

Delete and add lines so that you would have the following

Date date date

Field 2 cod1 cod2

Field 3 value1 value2

At the end you should have what you want,

Regards,

Data flow task multiple destinations

Hi,

The further i get with doing my current SSIS package the more i am starting to wonder about best practices and performance.

My current package loops through CSV files in a specified location and extracts events from these files. Each file contains multiple events which are a mixture of different types. Depending on the event there are a different number of comma seperated values. In the package i firstly set each event to one column seperated by a comma delimeter. I then create an array for the event which is split by the delimeter. In a script i weed out all elements of the array that are common to all events and set the remaining events to another array. After some processing i come to my conditional split transformation which splits the processing of each event based on the EventID. This is where i'm having doubts on whether i have approched the package correctly. There are approximately 60 different events so each one of these has a seperate pipeline to process the remaining parameters in the array and output them to the destination table. The destination table is differnet for each ID. Is it viable to have this amount conditions and paths when creating the pacakge and is this likely to have any detrimental effect on performance. Is there possibly another way that i could approach this problem?

Many thanks, i hope that made sense.

Grant

Grant Swan wrote:

Hi,

The further i get with doing my current SSIS package the more i am starting to wonder about best practices and performance.

My current package loops through CSV files in a specified location and extracts events from these files. Each file contains multiple events which are a mixture of different types. Depending on the event there are a different number of comma seperated values. In the package i firstly set each event to one column seperated by a comma delimeter. I then create an array for the event which is split by the delimeter. In a script i weed out all elements of the array that are common to all events and set the remaining events to another array. After some processing i come to my conditional split transformation which splits the processing of each event based on the EventID. This is where i'm having doubts on whether i have approched the package correctly. There are approximately 60 different events so each one of these has a seperate pipeline to process the remaining parameters in the array and output them to the destination table. The destination table is differnet for each ID. Is it viable to have this amount conditions and paths when creating the pacakge and is this likely to have any detrimental effect on performance. Is there possibly another way that i could approach this problem?

Many thanks, i hope that made sense.

Grant

Grant,

I have to be honest, it sounds like a very good approach. Importing as a single column and then splitting within the pipeline is definately a good idea.

Granted, 60 data-paths is alot but that does not mean the SSIS pipeline cannot handle them so yes, I do think it is viable. In terms of performance, well yes, of course as the number of paths increases then SSIS has more work to do and this will be detrimental to the time taken to execute - but that's not the same as being detrimental to perrformance. You're not going to get away from an increase in execution time whatever you solution may be, regardless of whether you use SSIS or not. Whether the performance is acceptable or not is entirely down to your discretion and the determining factors are:

Amount of data|||Hi Jamie,

Many thanks for this. It has put things in perspective. I still believe this solution at its current state to be quicker than the existing stored procedure which consists of a hell of a lot of nested if statements. The goal is to speed up this process as we are constantly getting these CSV files on a daily basis (approx 2000 a day) and the current methods are showing limitations.

I'll hopefully get some throughput and performance stats once i have the package completed and would be happy to let you cast your eye over them.

On a lightly differnet note. I am aware that when i process the remaining parameters there may be times where the event row will cause errors. Instead of using a row redirect and setting up the same destination multiple times would i be correct in saying that the redirects should point to a Union all transformation which then can input them all to one error destination table? Thinking about this would it be possible to have multiple inputs into a script which formalises all the events so that they can be directed to and error table?

Thanks,

Grant|||

Grant Swan wrote:

Hi Jamie,

Many thanks for this. It has put things in perspective. I still believe this solution at its current state to be quicker than the existing stored procedure which consists of a hell of a lot of nested if statements. The goal is to speed up this process as we are constantly getting these CSV files on a daily basis (approx 2000 a day) and the current methods are showing limitations.

I'll hopefully get some throughput and performance stats once i have the package completed and would be happy to let you cast your eye over them.

If resources start to get squeezed on performance then perhaps your conditional split could push EventIDs 1-30 into a raw file and EventIDs 31-60 into a second raw file. You can then process each "group" in isolation in seperate data-flows - thereby splitting out the processing. Variations on this these (i.e. more groups) are of course an option :)

As always the way to find the optimum solution is test and measure test and measure test and measure!!!

Grant Swan wrote:

On a lightly differnet note. I am aware that when i process the remaining parameters there may be times where the event row will cause errors. Instead of using a row redirect and setting up the same destination multiple times would i be correct in saying that the redirects should point to a Union all transformation which then can input them all to one error destination table? Thinking about this would it be possible to have multiple inputs into a script which formalises all the events so that they can be directed to and error table?

Yeah I think they should point to a UNION ALL. Maybe you could push that data into a raw file (which is stupendously quick compared to a flat file or DB table) and then push the contents of the raw file to wherever you want it in a seperate data-flow. Again, it would be an interesting test to find out. I dread to think what your data-flow will look like with 120 data paths in it though

-Jamie

|||Jamie,

Yeah I

think they should point to a UNION ALL. Maybe you could push that data

into a raw file (which is stupendously quick compared to a flat file or

DB table) and then push the contents of the raw file to wherever you

want it in a seperate data-flow. Again, it would be an interesting test

to find out. I dread to think what your data-flow will look like with

120 data paths in it though

It looks bad enough with nearly 60 :)
Need to look at the layout and set it out a bit better i think.

Thanks again,

Grant|||Jamie,

I've been thinking and believe i have come up with possibly a better approach to this problem. I've found that error re-direction from a script component isn't just a simple matter of dragging the red path to a seperate output although if you could help on how to do (as a seperate question) this i'd be interested in finding out.

Anyway, i believe i can get rid of all script tasks following the conditioanl split and just having one before the split. In this i would then split up my comma delimited list into generic parameter variables. Each event in the conditional split would then use only specific parameters from the array. This will work as long as i use a fixed length array with empty strings where no parameters exist. The problem i think i will encounter with this method is that i still need to convert the parameter data from strings into the destination table data type. I assume for this i should be able to use the data conversion task in place of the script? will the data conversion task allow me to do complex conversions that might involve changing a hex value in the source data to a integer value for the destination.

Any help or opinions on this would be appreciated.

Thanks,

Grant|||

Complicated conversions like that are more readily achieved in a Derived Column component or a script component.

-Jamie

|||Thanks Jamie,

Thats what i thought. At least with the Derived columns and the data conversion transformations i can use the error redirect as soon as a problem arises. As i mentioned the script task seems to be sadly missing this functionality for what ever reason.

Grant