Database Discussion Boards

First Prev Next

Re: Join two tables of different servers in SSIS

Syed Mehroz Alam1-Aug-09 2:02

Syed Mehroz Alam

1-Aug-09 2:02

Hi Abhijit/CodeManiac,

Expert exchange displays the answers at the bottom when your referrer is Google. Try searching for the question title "Linking, Connecting MS SQL 2000 to AS400 Database" using Google, go to the first result, and scroll down to the bottom of page to view the replies.

Hope that helps.

Regards,

Syed Mehroz Alam
My Blog | My Articles

Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

Re: Join two tables of different servers in SSIS

Robin_Roy29-Jul-09 17:53

Robin_Roy

29-Jul-09 17:53

One option could be, create multiple Data Source (one for AS400 and another for SQL Server) in your DataFlow task and then use Union All Data Flow transformation Item and set the output to one Data Destination that is your SQL Server.

synchronization of two databases

Robert Vrinceanu28-Jul-09 21:35

Robert Vrinceanu

28-Jul-09 21:35

Hi,
I have two identical databases whitch are different only by name (DB1 and DB2)
Eatch database is on one computer (PC1 and PC2) into a network.
Database DB1 contains table Tab1.
Database DB2 contains table Tab2.
The two tables Tab1 and Tab2 are identical as structure an receive the same data from an external aplication.
There are moments when the network is interrupted, from different causes and the two tables no longer contains the same data.
To synchronize the tables I have to copy only the rows which are lost from Tab1 in Tab2.
By a stored procedure i have to know which are the numbers of rows lost from Tab2 to fill in with rows from Tab1
I actually have to copy a number of lines from Tab1 in Tab2 with the condition to know which are the rows lost from Tab2.
Someone knows how can I do that? Thanks.

Re: synchronization of two databases

SeMartens28-Jul-09 23:43

SeMartens

28-Jul-09 23:43

Hi
Do you rows have an unique identifier? If yes you could use something like this:

insert into tab2 select * from tab1 where not exists (select table2.id from tab2 as table2 where table2.id = tab1.id)

This will only work for added rows. Changing existing rows and synchronizing deleted rows will not work with this.

Regards
Sebastian

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

Re: synchronization of two databases

Blue_Boy29-Jul-09 0:35

Blue_Boy

29-Jul-09 0:35

Use this:

Case 1

Insert into DB1.dbo.Table1 <br />
select * DB2.dbo.Table2 from where DB2.dbo.Table2.ID not in (select DB1.dbo.Table1.ID from DB1.dbo.Table1 )

Case 2:

Insert into DB2.dbo.Table2 <br />
select * DB1.dbo.Table1 from where DB1.dbo.Table1.ID not in (select DB2.dbo.Table2.ID from DB2.dbo.Table2)

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

Re: synchronization of two databases

Robert Vrinceanu29-Jul-09 3:51

Robert Vrinceanu

29-Jul-09 3:51

Thank you all.
The problem is that databases DB1 and DB2 are on different computers.

Re: synchronization of two databases

Eddy Vluggen29-Jul-09 6:15

Eddy Vluggen

29-Jul-09 6:15

What brand are DB1 and DB2? If it's SQL Server, you could add a linked server[^] Smile | :)

Re: synchronization of two databases

David Skelly29-Jul-09 5:21

David Skelly

29-Jul-09 5:21

http://www.sqlaccessories.com/SQL_Data_Examiner/[^]

Re: synchronization of two databases

Robin_Roy29-Jul-09 17:55

Robin_Roy

29-Jul-09 17:55

Which database engine are you using. If you are using MS-SQL Server, it is fairly simple using linked server to keep both the dB in sync using some simple queries and statements...

Re: synchronization of two databases

Robert Vrinceanu30-Jul-09 0:45

Robert Vrinceanu

30-Jul-09 0:45

Yes, DB1 is MSSQL 2005 and DB2 is MSSQL2000.
I haven't experience with "linked server"
Can you indicate some examples.
Thanks.

Regards,
Robert

Re: synchronization of two databases

Robin_Roy30-Jul-09 0:52

Robin_Roy

30-Jul-09 0:52

You can get good details from the following URL
http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx

Let me know, if you have any difficulty

Re: synchronization of two databases

dingoishere23-Feb-11 22:32

dingoishere

23-Feb-11 22:32

Try this to compare and sync two sql server database D-Softs Database Compare Tool

Use sub reports in the header of a report in SSRS

Pankaj Saha28-Jul-09 20:37

Pankaj Saha

28-Jul-09 20:37

hi..
I have created a sub report and want to put that sub report in the header of a report in the SQL Server Reporting Services.

How can i achieve this?

Pankaj

Re: Use sub reports in the header of a report in SSRS

leckey29-Jul-09 4:00

leckey

29-Jul-09 4:00

Why the header? Why not just put it into the body portion?

Back in the blog beatch!
http://CraptasticNation.blogspot.com/[^]

Re: Use sub reports in the header of a report in SSRS

Pankaj Saha29-Jul-09 19:13

Pankaj Saha

29-Jul-09 19:13

we can use sub reports in the body, but it will not show every page. If body is larger then it will continue in the second page and I have to show the header in every page. I do not want to design of header of every reports that's why I have created a sub report and want to put that sub report (header) in each and every report.

Pankaj

dbping error

MWRivera28-Jul-09 7:51

MWRivera

28-Jul-09 7:51

Hi All,

Hope you can help me with this one.

I'm receiving an error when trying to dbping a database on a server. I'm able to ping the server however. The error I receive is:

"SQL Anywhere Server Ping Utility Version 10.0.1.3415
Ping database failed -- Specified database not found."

It's clear that it's telling me the database can't be found on the server specied, the problem is the database is on that server.

Has anyone got any ideas on how to overcome this issue?

The command I'm using is:

dbping -d -c "ENG=ServerName;DBN=DatadaseName;UID=UserID;PWD=Password"

Thanks,

Mel

MS SQL: how to truncate all tables without unlink the keys.

Ersan Ercek28-Jul-09 6:21

Ersan Ercek

28-Jul-09 6:21

Is it possible to truncate all tables without touching the constraints.

All the best

Re: MS SQL: how to truncate all tables without unlink the keys.

leckey28-Jul-09 11:11

leckey

28-Jul-09 11:11

By truncate do you mean delete records, but not the table itself? If there are constraints (for example table CUSTOMERS, which has records in SALES by a customer ID) you would have to delete the records from the child table (in this case SALES) first, then delete from the parent (in this case CUSTOMERS).

I have had this issue at work where we want to delete something, so we have to start with the "smallest child" and work our way up. If it's something you plan to do frequently then just write a stored procedure for it so you can run when necessary.

Back in the blog beatch!
http://CraptasticNation.blogspot.com/[^]

Re: MS SQL: how to truncate all tables without unlink the keys.

Ersan Ercek28-Jul-09 21:39

Ersan Ercek

28-Jul-09 21:39

I want truncate because it resets primary key identity as well.

Anyway I had some time to search on web and found a sp that drops the foreign keys, truncates the tables and re-creates them. It is very cleaverly written.

Re: MS SQL: how to truncate all tables without unlink the keys.

Mycroft Holmes28-Jul-09 20:06

Mycroft Holmes

28-Jul-09 20:06

It can't be done simply, any table without an key can be truncated and use the following if there is a key

DELETE FROM TableName
DBCC CHECKIDENT (TableName, reseed, 1)

this will have the same effect as truncate. It will only work if there is no DATA based on the foriegn key, it will not violate the referential integrity of the data.

I have a script that will clean out a database but it is hard coded for each application. It truncates all the non key table and then walks the key structure of the database manually.

You could write a script/app that walked the FK structure of a database and created a hierarchy tree based on the keys (this is what code generators do)

Never underestimate the power of human stupidity
RAH

Re: MS SQL: how to truncate all tables without unlink the keys.

Ersan Ercek28-Jul-09 21:41

Ersan Ercek

28-Jul-09 21:41

I needed to reset identities as well so, deletion was not enough for me...

But at the end I found a good sp that makes all I need.

unique contraint

Ebube28-Jul-09 5:40

Ebube

28-Jul-09 5:40

Hi how can i set union constraint on a column using sql server management studion

Re: unique contraint

Ebube28-Jul-09 5:51

Ebube

28-Jul-09 5:51

i have got the answer

Re: unique contraint

Baran M28-Jul-09 18:34

Baran M

28-Jul-09 18:34

Ebube wrote:
set union constraint

Union contraint ?

Education is not a way to escape poverty — it is a way of fighting it.

Union HELP!!!!!

cdietschrun28-Jul-09 5:01

cdietschrun

28-Jul-09 5:01

CREATE PROCEDURE dbo.sp_GetNewestTemplates

AS 

SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Joe Smith' 
order by lastchange desc
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Jane Smith'
order by lastchange desc
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'GI Jane'
order by lastchange desc

GO

WHY does that code give no errors but this does:

CREATE PROCEDURE dbo.sp_GetNewestTemplates

AS 

SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Joe Smith' 
order by lastchange desc
union
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Jane Smith'
order by lastchange desc
union
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'GI Jane'
order by lastchange desc

GO

I need this to work! What am I missing, and how can I get around this ridiculously annoying error? All it says is "Error 156: Syntax error near union."

Last Visit: 31-Dec-99 18:00 Last Update: 29-Apr-24 22:03

Refresh

ᐊ Prev 1...794 795 796 797798799 800 801 802 803 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.