Exchange data between databases on two servers?

Dear all

I have tow server on the same intranet. One server has a sql server 2000 database and the other one has SQL server 2005 databse.

The sql 2000 database has a table called employee. When ever a new employee is inserted in the database i would like the same values to be sent to the sql 2005 database. But this cant be done on the application level. It has to be done in the database. The application level can not be changed.

I was thinking a trigger but how to achieve the writing from one database to another. If they were on the same server then it would be easier but because they are on different servers i dont know how to do it.

Has anyone had similar issue before?

Any help is apreciated.

Sincerely

Dan

First make sure 2 server are trusted.

Then link 2 servers using:EXEC sp_addlinkedserver 'server2'.

Quantifier of the table is: [server2].dbo.[db].[table], 'dbo' is the default security schema, use a proper schema name if it's different.

If you used trigger on the table, and the transaction failed, you may want to read this discussion: http://forums.databasejournal.com/showthread.php?t=6701

|||

Thank you for the response.

For everyone else that might come across this post the way to go is using linked servers.

Once you create the linked server (as a security Object in SQL05) you can use 4 part naming. In a normal scenario only 3 part naming is suported. The forth part is the linked servr name.

Please make sure that the DTC service is turned on. Check for proxies and firewals becasue if you have those they will have to be configured properly or othervise it will not work.

Last thing you should check is that you are running the latest version of the MDAC's. I belive at the time i make this post the most current version is 2.8. This will ensure that you have all the latest providers.

Cheers

Dan

EXCEPTION_ACCESS_VIOLATION While Excecuting Select Query.

Dear Freinds,

While Excecuting select Query in my client place ,the following error is coming.

SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..

When i got same database , i try restore in my pc and run the same...query its excecuting properly..with out giving any error..i tried lot of things ..nothing is working out..i am not able to find the root cause itself..why the exception is coming..

I attaching the Error log with this post.

Note: Check For the Keyword 'EXCEPTION_ACCESS_VIOLATION' in the Error Log File.

Any body can help me out in sorting this issue...then :beer: other wise i will be:eek:

With Reg,
Sathesh.Mapply service pack 3a or service pack 4 of Sql server 2000

Excel Pivot Table Limitations?

Dear Anyone,

We seem to have encountered a bug o a limitation but we're not sure what to make of it.

We are using Analysis Services 2005 RTM. Went have a 3 level dimension. We noticed that when we used the dimension in the page field of the pivot table, we noticed that not all members of some parents in the 2nd level does not display. We double checked by browsing the dimension using the Management studio and we confirmed that not all members in the lowest level is being displayed. The ratio is somewhere in the numbers of 45900/2500. The lower number being the only number of members that are being displayed.

The weird part is that we didnt encounter this problem with the Analysis Services 2000. Our current OLAP project now is a migrated version of our old data warehouse.

Any insights anyone?

Thanks,

Joseph

Couple of things to try.

One: Try to browse your cube using cube browser and not dimension browser in the SQL Management studio.

Two. Try building a simpler cube (just few dimensions) in AS2000 and migrate it to AS2005. Compare results in Excel. See if you see different results when browsing cubes of exact the same structure.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights