Need To Push Data To Several Servers In Parallel

Jul 25, 2007

I'm working on an SSIS package whose job it is to push data from x # of tables to x# of servers. Right now, I have it implemented with nested ForEach loops. ForEach Server loop inside of a ForEach table loop. It works great, but it will take too long as it is serial. I need a way for the inner operation to take place in parallel. In SQL 2000, the way I 'm doing this is by spawning a SQLAgent job per server. I could always do this here as well, but I'm interested in finding out if there is a way to do this with a single package.



I'm okay with process the table serially, but for each table I'd like to be able to parallel the push to the servers. Can't do it with multiple data flows because there are N # of servers being pushed to.



Hope that makes sense.





View 1 Replies


ADVERTISEMENT

Retrieving Data From Multiple SQL Servers In Parallel

Nov 9, 2001

I have multiple SQL 2000 servers that hold data which I retrieve using a single SQL stored procedure. Unfortunately, I don't know how to retrieve this data asychronously (in parallel).

For example, I retrieve data from two linked servers using the following:

select * into #temp from openquery(SomeLinkedServer,'exec BigQueryHere')
select * into #temp2 from openquery(OtherLinkedServer,'exec BigQueryThere')
.
.
lots of manipulation of the temporary tables to get what I want, etc.
.
.

The problem I have with this is that there is no reason why the first query should have to finish before the second query begins (serially) because these are on separate servers. Is there a way to execute these so that they run at the same time?

Bill

View 1 Replies View Related

Development Push To UAT Push To Production

Mar 15, 2006

We need to configure a way to publish from development to testing and from testing to production. An easy, automated process would be best.

View 1 Replies View Related

Push Data Into Sql2K

Sep 18, 2007

Hi, all,

I did not see this one coming, and I am not sure if I did something wrong.

How do you push data from sql05 to sql2k?

I set up a data flow task, with one sql05 connection magager and another sql2k connection manager. Then when I tried to map them, I cann't!

The message on the box said: The connection manager uses an earlier version of sql server provider. Bulk insert operations require a connection that uses a sql server 2005 provider.

I have been trying different source, destination and transformation, but seems like missing something.

Thanks!

View 3 Replies View Related

Pocket PC Application Cannot Push Data

Mar 17, 2008

I have a Pocket PC application (VS2005, SQLCE 2005, Windows Moblile 5.0, SQL Server 2000) that can pull data with no problem but produces an error when an RDA push is attempted. The error message is: Error Code: 80072EE4 Minor Err: 28037. The information in TechNet has description of : A request to send data to the computer running IIS has failed. For more information, see HRESULT. It does produce a log in the folder for SQLCE 3.0 and has the following message: Hr=80070585 ERR:REQUEST NOT QUEUED for ulRSCBId = -1. Not sure if this means anything because I can't find much information on it. Anyone have any ideas on what this could be? Do I display the HRESULT the message mentions in Visual Studio debug? We have this working on our development server so I am not sure what is different here.

View 5 Replies View Related

Call A Stored Procedure For Data Push

May 2, 2007

Is it possible to call a stored procedure on the remote sql server to do a push (insert the data onto the main sql server)?



View 1 Replies View Related

Integration Services :: How To Push Data Rows Into Third Row Of Excel Instead Of First Row

Jul 27, 2015

I have data rows ( 7 rows and hundreds of colums) obtained by using execute sql task and i placed the output in CSV. Then I also moved this data in csv into excel (first row of excel)using simple data flow task with flat file source(CSV) and excel destination connections. However, I need to push data into 3rd row of excel sheet(I need to write some description and titles in  the first two rows) .I need to do this inorder to automate the process of producting the excel which has predefined pivot tables. I only need to update the excel sheet with raw data( 3rd row) which drives the pivot tables. How do I do this? How do i push into the third row instead of first?

View 6 Replies View Related

Service Broker In Data Push Scenario:best Practice

May 7, 2007

Hallo I need some hints because I would like to set up my service broker in the proper way.

First: I'm going to setup service Broker between 2 databases on the same sql server instance.

My Goal: I insert the data on table1 in the DB1.On table1 there is a trigger that begin conversation and send the message to the service on the DB2.
On the receiving queue there is an Activation that take the xml message, shred it and save the content on th table 2 on the DB2.
Actually this SP is my main concern because the shred and insert involves also several checks so it could take "a while".

Volume of data: it seems that there are 100-200 daily insert in the table1 on the DB1 and it is possible that some of them arrives at the "same time".

Very quickly:
On the DB1 the trigger:

Notice that I reuse always the same conversation and the send queue as RETENTION = OFF


begin transaction;

begin

set @dialog_handle = (select conversation_handle from sys.conversation_endpoints where far_service='ReceiveService');



if @dialog_handle is null

BEGIN

BEGIN DIALOG CONVERSATION @dialog_handle

FROM SERVICE [SendService]

TO SERVICE 'ReceiveService'

ON CONTRACT [MainContract]

WITH ENCRYPTION = OFF;

END

SEND ON CONVERSATION @dialog_handle

MESSAGE TYPE Message ('uyiuy')

commit;

END


On the DB2
ReceiveQueue is defined as:

CREATE QUEUE [dbo].[ReceiveQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[OnReceivedTrade] , MAX_QUEUE_READERS = 100 , EXECUTE AS N'dbo' ) ON [PRIMARY]



But most important is the Activation's SP and this is the main code:

while (1 = 1)

begin

begin transaction

-- Receive the next available message from the queue

WAITFOR (

RECEIVE top(1)
@message_type=message_type_id,

@message_body=message_body,

@dialog = conversation_handle
FROM ReceivedQueue

)
if (@@ROWCOUNT = 0)

BEGIN

Rollback Transaction

BREAK

END



SET @ErrorSave = @@ERROR ;

IF (@ErrorSave <> 0)

BEGIN

ROLLBACK TRANSACTION ;

SET @ErrorDesc = N'An error has occurred.' ;

END CONVERSATION @dialog

WITH ERROR = @ErrorSave DESCRIPTION = @ErrorDesc ;

INSERT INTO [dbo].[tblLog] VALUES(@ErrorDesc,NULL)

END

ELSE



BEGIN

'this is the SP that does most of the work

exec [dbo].[sp_ShredXMLMessageToRelationalData] @message_body

END

commit transaction



I have to say that everything works fine but I don't think that it is completly performant.
I read several blogs/forums and now I'm confused on the best way to implement service broker messaging:
These are my questions:

1) I read that is the DB's are in the same instance It can improve performances the send the message directly on the Queue2.
Can be useful in my case? How to implement it?
2)In my case it is necessarly that I send a message back to the initiator?
3) In my case the conversation never end. I don't think that this is correct but in case of the data push who has to END the conversation? The target? the initiator? never end to avoid overhead (I read that big overhead is caused by the BEGIN and END DIALOG)?

3) where (initiator or Target) and how to handle the service broker error? In my case my applications are SPs and I need to inform the developer or DBA that something went wrong during the processing ( conversation or shredding stored procedure).

4) In my case is should be a good idea to think about how to solve the possibility of the "poison messages"



for sure I will find out other questions...
Any hint, link is appreciated!

Thank you very much!

Marina B.

View 17 Replies View Related

Using Push In RDA For Sync The Data From Sql Mobile To Sql Server 2005

Oct 6, 2006

Hi Everyone,

I have tried the Sync the data from sql server mobile (.SDF) to Sql server 2005 (.mdf).In push command, i am getting the error "Sql Server Everywhere encountered problems when opening the database".
string rdaOleDbConnectString = @"Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=master;Data Source=PRASSANAPRASANNA;Password=master";
qlCeRemoteDataAccess rda = null;
try
{
rda = new SqlCeRemoteDataAccess();
rda.LocalConnectionString = "Data Source=D:/DATABASE/test.sdf";
rda.InternetUrl = http://localhost/Dataset/sqlcesa30.dll;
rda.Push("company", rdaOleDbConnectString, RdaBatchOption.BatchingOn);
MessageBox.Show("Completed");

}

catch (SqlCeException)

{



}



finally

{



rda.Dispose();



}

please guide me any one ours guys to solve this problem .

Thanks

Prasanna.





View 5 Replies View Related

Push Merge Snapshot Fails With Data Is Null Message.

Mar 10, 2006

Hi,

I'm converting a replication script from SQL 2000 to SQL 2005.

I am getting an error with push merge with no way to figure out what is wrong.

I've configured replication on a single XP server in SQL 2005 RTM version.

I have a push merge set up between A and B and between B and C. All 3 databases are 9.0 compatibility.

The snapshot and merge jobs for the A to B run fine with no errors, and merge replicates ok.

The snapshot for B to C fails with this message:
Message
2006-03-09 17:30:35.94 ---------------------------------------------
2006-03-09 17:30:35.94 -BcpBatchSize 100000
2006-03-09 17:30:35.94 -HistoryVerboseLevel 2
2006-03-09 17:30:35.94 -LoginTimeout 15
2006-03-09 17:30:35.94 -QueryTimeout 1800
2006-03-09 17:30:35.94 ---------------------------------------------
2006-03-09 17:30:35.95 Connecting to Publisher 'MyInstance'
2006-03-09 17:30:35.97 Publisher database compatibility level is set to 90.
2006-03-09 17:30:35.97 Retrieving publication and article information from the publisher database 'MyInstance.MyDB'
2006-03-09 17:30:36.22 [0%] The replication agent had encountered an exception.
2006-03-09 17:30:36.22 Source: Replication
2006-03-09 17:30:36.22 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2006-03-09 17:30:36.22 Exception Message: Data is Null. This method or property cannot be called on Null values.
2006-03-09 17:30:36.22 Message Code: 52006
2006-03-09 17:30:36.22

Love that exception message: "Data is Null" - very helpful to someone who is clairvoyant perhaps.
I checked the snapshot bcp files. The tables being merged all have data.

A sample add article command is:

exec sp_addmergearticle @publication = N'MyMerge', @article = N'Phone', @processing_order = 4, @source_owner = N'dbo', @source_object = N'Phone', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option = 0x000000004C42CDDF, @article_resolver = null, @subset_filterclause = null, @vertical_partition = N'false', @destination_owner = N'dbo', @verify_resolver_signature = 0, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @identityrangemanagementoption = N'none' ,@force_invalidate_snapshot = 1,@force_reinit_subscription = 1

If you have any ideas on how to fix this, I'd be most grateful. As it is after 6pm I probably won't read this again until morning. Thanks for any suggestions.

View 19 Replies View Related

Any Way To Control Execution Processing (parallel Or Serially) When Using Data Driven Subscriptions?

Apr 12, 2007



I've got an SSRS report that is set up using a data-driven subscription to supply input parameters to the stored procedure that is called to generate the report results.



I was wondering if there is any way to specify the execution processing method (running the reports in parallel or serially). The subscription that we have set up appears to be running all of the reports in parallel which is causing massive load on our servers.



Thanks.

View 3 Replies View Related

Master Data Services :: Master Data Services - Data Push Back To Excel Sheet

Nov 2, 2015

We already integrated different client data to MDS with MS Excel plugin, now we want to push back updated or new added record to source database. is it possible do using MDS?  Do we have any background sync process to which automatically sync data to and from subscriber and MDS?

View 4 Replies View Related

Integration Services :: Execute Several Data-flow Tasks In Parallel And Write To Single Excel File?

Jul 2, 2015

Is it possible to do? I'm getting lock violations in I try to execute several tasks in parallel.

View 4 Replies View Related

Data Transfer Between Two Or More SQL SERVERS

Dec 8, 2006

Hello,
I want to transfer data in between two or more sql servers. Actually One server is on the web while others are in small networks in diffrent locations.
I want to update the main server with the changes in local servers. My problem is if  the connection goes failed in between process of data transfer then how will it be recovered that how much of data is uploaded and how much is left.
Please I need help on this isue.
Thanks in advance
pronov 
 
 
 

View 1 Replies View Related

Getting Data From Remote Servers

Jun 20, 2007

 
 am an authenticated user of a remote data base
I can log in using sql server management studio and add tables and
even I can back up the database .
Good.

 But I want to get a copy of the
database to my local computer
Is there any way to do
this?The problem is that i don't have access  to the directory of sql sever  pleases tel me a way that is possible just by using sql server or any add on on it 

View 7 Replies View Related

Data Access Across Servers

Aug 27, 1999

Hello,
I have a developer here who wants to be able to access two databases on different servers with the same query statement.
Both servers have SQL Server 7.0 ??? Any suggesstions?

Joanna

View 1 Replies View Related

How To Query Data From To Servers

Aug 25, 2000

hi,
I have a question. how do query data between the two servers.
thanks
kumar

View 2 Replies View Related

Replicating Data Across Servers

Jun 17, 1999

We have a database that I would like to replicate on another server but am unable to use regular replication via publish/subscribe due to the fact that the production database has no primary keys on tables, only clustered indexes. The backup db needs to be as close to real-time synchronized as possible and will be in fairly active use most of the time. Has anyone had success in developing such a system? How did you do it and what are the pitfalls? Any advice would be greatly appreciated. Thank you.
W.

View 1 Replies View Related

Accessing Data Across 6.5 Servers

Jun 22, 1999

Hi,

I need to access data across my sql servers . All the servers are 6.5. Think MS DTC is the solution. But how to implement the same. Can somebody give me step by step instructions.

Thanks in Advance.

Cheers

View 1 Replies View Related

Transfer Data Across Servers

Apr 1, 2008

Hi,

Not sure thisis the right forum for my question.
I have to insert data from one table into another. The problem is that the 2 tables are on different servers and computers for that matter. I only need data that is in one specific table.
What is the easiest and most efficient way to do that.

If I access the 2 computers via remote desktop from my computer and actually go and copy the data from the table in computer 1 and paste it inside the table in computer 2 it does paste the data.However, for some reason it doesn't paste all the data. The column datatype is ntext.
I mean if the entry is for example 'hello world how are you today? blah bla blah'
It will only paste 'hello world how are'

I will appreciate your help.

Many thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

View 4 Replies View Related

View Data From 2 Servers

Apr 6, 2006

Guys,

How do I create a view that will pull data from dbases on 2 different servers ?

I have 2 server names....

svr10mbr01
svr11mbr02

2 Databases...

PWBstaff on svr10mbr01
PWBdata on svr10mbr02

I need to create a view in PWBdata that will do something like....

Select * from PWBstaff.dbo.staff

Is this possible, if so what is the syntax.

Ta
Bob

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com

View 3 Replies View Related

Transfer Data Between SQL Servers

Sep 20, 2006

Hi,

I've got a quick question here. I'm still quite new to SQL Server. My question is how to transfer file between SQL Servers. For example, i have been working on the database - updating or editing some tables in the SQL Server 2000 at my end. Then, i need to pass whichever tables got updated on to my client who also uses the SQL Server 2000. So we have two separate SQL Servers 2000. i wonder how to transfer data like tables between the two SQL Servers.

Thank you in advance

View 2 Replies View Related

Data Collection From Many To One Servers

Mar 7, 2008

I need to feed head office sql server with the data from regional servers. Servers are spread through all continents
Data input done locally on Head office server as well and plus need to ship data from other servers.
So clarify this - Head office server is not standby one. Mirroring is out of the picture, I think..
Initially, I thought ship a log every 15 min and restore on Head office server but is this going to create an issue for the local data processing?

Any bright ideas welcome!

View 2 Replies View Related

Transferring Data Between Servers

May 14, 2006

I need to append data from a database on one server to a table in a databaseon a different server. Both servers are running SQL 7. How can that be done?Thanks.

View 2 Replies View Related

Sync Data Between Two SQL Servers

Jul 20, 2005

Ok..here is my problem. I have two SQL servers that I need to haveidentical, at least semi-real time data on. One is in a public DMZwith full access to and from the internet, and one is behind acorporate firewall and the box can get out to the internet but cannotbe hit directly from the internet in. The two are on seperatenetworks and cannot be connected via lan. Is there a way for me tohave semi-real time data transfers between the two to keep bothservers identical? I have thought that the server behind the firewallcould initiate an XML session with my dmz sql server and processupdates, but I am unfamiliar with how it would know what has changedon the remote server and how the remote server would know what haschanged on the server behind the corporate firewall. Any good ideas?ThanksDan Hirsch

View 3 Replies View Related

Synchronize Data Between Servers

Mar 29, 2006

What is the easiest way to synchronize all data and database objects between my development machine and hosting server? Is there any SSIS free script I can use?

View 7 Replies View Related

Exchange Data Between Databases On Two Servers??

May 29, 2007

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

View 2 Replies View Related

Select Data From Two Databases On Different Servers

Oct 27, 1999

Hi there,
I was just wondering is it possible to select data from two different databases on two different servers?

ie Select * from
Server1.databasename..table, Server2.databasename..table


If anyone has any suggestions they would be appreciated,
Thanks,
Fin

View 2 Replies View Related

Transfer Data Between Linked Servers

Nov 12, 2004

Hello to all,

my problem is about the slow execution of a query.

I have two database located in two different servers. The servers are linked with the linked server connection made with the enterprice manager.

The databases structure is the same and I copy some data from a database to the other with the following query:

INSERT INTO DATASERVER.RESULTS.dbo.Tab_Tests SELECT * FROM LINESERVER.WIP.dbo.Tab_Tests WHERE ID_Result = '{76271FC1-9470-4EF7-A403-000CF75C2215}' OR ID_Result = '{08C8EEB9-CD22-4CF9-8269-8C1CC58C752B}' OR ID_Result = '{B7B7EED1-36E4-48FA-9636-ABBE62AAE04D}' OR ID_Result = '{8F7BA69E-D2CE-4EB2-BBC4-AFFA165CE0CE}' OR ID_Result = '{273CB805-945F-4EFA-AE20-BC4E0898A19F}'

Everything works till the database grows up more than 400 MB.

Also if I select one result, that consist of some records, the query is very slow and the CPU is working over the 75 %.

I ask you if my working way is corret or if you have some solution or some explanation for me.

Thank you

View 1 Replies View Related

Transfer Data Between Two Remote Servers

Sep 26, 2005

Hi, I'm looking for an artical or Someone who can explain me what is the best way to transfer data between two remot database?

I'll Explane:
two business have local SqlServer in their office.
1 of business need to get Specific data from other. each database is protected. how can I read ake information of it?

Thanks.

View 5 Replies View Related

SQL Express Data On Web Servers - Will It Work?

Jul 7, 2007

I am preparing a new website that will contain names, address and phone numbers of people in my region of Costa Rica. Will I be able to post a database prepared in Visual Basic 2005 Express / SQL Server 2005 Express on the website's MySQL Server and utilize the data to populate webpages?

If not, what should I use to prepare the database and drive the webpage.

New to SQL and VB
Don Johnson
Latitude 8 Lodge
Costa Rica

View 2 Replies View Related

Moving Data Tables Between SQL Servers

Jul 20, 2005

I have user that we just migrated his Access database to SQLServer. All went well with the migration, but then he came up withanother requirement to be able to replicate the database to a localSQL server living on the hard drive of a laptop. Before the migrationhe just copied the entire Access databse to the lap top.I tried using the Copy SQL Server Objects Task to move thenecessary tables from the production server to the laptop, but noticedit doesn't copy over the table Indexes/keys identiy fields etc. Iended up backing up the production database and restoring it to thelaptop database, but wondered if there is any way to move the tables,with their properties from one server to another? I know I can setup the backup process to run as scheduled, but the problem is the dataneeds to be moved on an irregular time table. I thought about justwriting code on the remaining Access front end to empty the localtables and then query the data from the production side to reloadthem, but I'm sure there's an easier way.Any suggestions would be appreciated.Thanks,Tom

View 1 Replies View Related

Binary Data On Multiple Servers

Feb 18, 2007

I'm using EncryptByKey to encrypt data in my SS2005 database. Since our server is really slow to access from home to work on, I used the Database Publishing Wizard and installed the db to work on at home. Then I created the certificate and symmetric key in my home db.

When I pull info using the DecryptByKey on our database at work on Windows 2003 Server, no problem, the data is decrypted. However, the same data does not decrypt at home on my Windows XP computer. I'm using TripleDes on both machines for the symmetric key (AES won't work on XP).

--To create my cert and key:

USE My_DB;
CREATE CERTIFICATE MyCert
ENCRYPTION BY PASSWORD = 'some password'
WITH SUBJECT = My Data',
START_DATE = '01/01/2007',
EXPIRY_DATE = '01/01/2099';
GO

CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCert;
GO


To encrypt:

OPEN SYMMETRIC KEY MyKey

DECRYPTION BY CERTIFICATE MyCert

WITH PASSWORD = 'same password as above';

Insert my record, use scope_identity to return primary key into @CustomerID.

INSERT INTO [Customers] (EncryptByKey(Key_GUID('MyKey'), @DataToEncrypt, 1, CONVERT( varbinary, @CustomerID)))

CLOSE SYMMETRIC KEY MyKey

To decrypt:

SELECT CONVERT(varchar(3925), DecryptByKey(EncryptedField, 1, CONVERT( varbinary, @CustomerID))) as PlainTextData

FROM Customers

WHERE (CustomerID= @CustomerID)

Everything works fine when I run the decrypt query on the database on our work server. But I'm not getting decrypted data at home. Is the symmetric key or certificate machine specific? If so, that will cause a huge problem when we deploy to a production server.

Thanks in advance for your help!

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved