Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







The Target Service Name Could Not Be Found.


Hi :

I am creating a queue application using Service broker. I was able to send and receieve messages between 2 databases in the same instance. Now I am trying to communicate b/w 2 different instances. I am getting the following error...

The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied..

The routing informations is as follows

DROP ROUTE SERVERROUTE

CREATE ROUTE SERVERROUTE

WITH

BROKER_INSTANCE = 'D6F1721F-E7A2-4497-8890-FD4C2AAD98FE',

SERVICE_NAME = 'SERVERSERVICE' ,

ADDRESS = 'TCP://10.23.3.12:6022';

GO

I also check the sys.routes and the entry is correct. I was also trying to debuggin using SQL profiler...but it didnt help much.

How do I fix this ? Your help is appreciated.

Thanks,

Pramod






View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
How To Prevent The Hang On The Initator Service Broker If The Target Service Broker Is Not Started?
How to prevent the hang on the initator service broker if the target service broker is not started?

Our case has two service brokers (two databases), sometime, the target is need to turn off. But the sitation is the initator service broker (in fact, the message is sent from triggers) become hang,I want to prevent this case and continue to operation, and the messages should queue and will continue to send to target service broker when it startup. How should I do?

View Replies !   View Related
XML Validation Error Detected On The Target Service. How To Keep And Save The Original Wrong XML Message?
Hello,



I have a message that is validated using a schema.


If I send the message that is not compliant with the schema,the message is not enqueued on the target queue but it is send back to the Initiator queue and the message_body contains the description of the Error.



On the Initiator queue I have activated a SP that is I used to end the conversation and log the error.



One of the information that I need to log is the original XML message that causes the validation error so I can check it.


It seems that in the moment I'm on this SP, activated when there is something on the Initiator queue I have no memory of the original message or at least I'm not able to find it.



Any hints/helps??



Thank you very much!

M.B.



View Replies !   View Related
Service Broker Getting Error '10061(No Connection Could Be Made Because The Target Machine Actively Refused It.)'.
Hi All,

I have created an application using service broker.i have a source database which in a particular server say server A and want to send message to target database which is in another server say server B.



i have configured all the service broker objects and advanced service broker objects in correct way by reffering the site --http://www.sqlservercentral.com/columnists/sindukuri/2797.asp



but when i am trying to send message from my source server to target server , i am getting the following error in sys.transmission_queue.

Connection attempt failed with error: '10061(No connection could be made because the target machine actively refused it.)'.

I have enabled the tcp port in both the server.

Note-My sourceserver is an XP and Target Server is an windows server 2003.



can anybody please tell do i need to set any extra configuration in windows server 2003 or any thing do l need to do which i have left.

please do help!!!!!!!!!!!!!!!!!!



Thanks a lot in advance.....................................



View Replies !   View Related
Service Manager In 2005 Not Found
I have uninstall MS SQL 2000 and installed MS SQL 2005, but i cant find nowhere the service manager to run the server

where is it ?
thank you

View Replies !   View Related
Service Broker &&"TO Service Could Not Be Found&&" Message Origin: &&"Transport&&"
I am trying to send a message between to SQL Server 2005 instances on two different machines. I have checked all my routes and all my objects appear to be setup correctly. However, when running Profiler on the target machine, I receive the "This message has been dropped because the TO service could not be found. Service name: "[tcp://mydomain.com/TARGET/MyService]". Message origin: "Transport". This is my activated stored procedure that is sending the message to the target service. I am using certificate security.Any help appreciated....



CREATE PROCEDURE [usp_ProcessMessage]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @conversation_handle uniqueidentifier

DECLARE @message_body AS VARBINARY(MAX)

WHILE (1=1)

BEGIN

BEGIN TRANSACTION;

WAITFOR(RECEIVE TOP (1)

@conversation_handle = conversation_handle,

@message_body = message_body

FROM [tcp://mydomain.com/INITIATE/MyQueue]

), TIMEOUT 1000;

IF (@@ROWCOUNT = 0)

BEGIN

COMMIT;

BREAK;

END

END CONVERSATION @conversation_handle

IF @message_body IS NOT NULL

BEGIN



BEGIN DIALOG CONVERSATION @conversation_handle

FROM SERVICE [tcp://mydomain.com/INITIATE/MyService]

TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'

ON CONTRACT [tcp://mydomain.com/INITIATE/MyMessage/v1.0]

WITH ENCRYPTION = ON, LIFETIME = 600;

SEND ON CONVERSATION @conversation_handle

MESSAGE TYPE [tcp://mydomain.com/TARGET/VisitMessage]

(@message_body);

END

COMMIT;

END

END

GO



My endpoints are created like so:



CREATE ENDPOINT MyEndpoint

STATE = STARTED

AS TCP

(

LISTENER_PORT = 4022

)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE MasterCertificate)

GO



GRANT CONNECT TO CertOwner

GRANT CONNECT ON ENDPOINT::MyEndpoint TO CertOwner

GO



And my routes like so:



GRANT SEND ON SERVICE::[tcp://mydomain.com/INITIATE/MyService] TO CertOwner

GO

CREATE REMOTE SERVICE BINDING [MyCertificateBinding]

TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'

WITH USER = CertOwner,

ANONYMOUS=OFF

CREATE ROUTE [tcp://mydomain.com/INITIATE/MyRoute]

WITH SERVICE_NAME = '[tcp://mydomain.com/TARGET/MyService]',

BROKER_INSTANCE = N'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',

ADDRESS = N'TCP://xxx.xx.xx.xx:4022'

GO

View Replies !   View Related
Excution 'rw5shff520eszz55eavmoeqe' Canot Be Found (rsExcution Not Be Found)


When user cliks on report link through website, user getting error "Excution 'rw5shff520eszz55eavmoeqe' canot be found (rsExcution not be found)" . this particular scenario was working correctly till one hour back( there is no database changes). This is happening only when the user clicks on the link through website.

it is not giving the problem, when he cliks the link from his mail or thorugh history in the same website. i have tested another reports are working fine except this report.

Please suggest me to resolve this issue. Thanks in advance!!!

Raju......



View Replies !   View Related
The Connection &&"&&" Is Not Found. This Error Is Thrown By Connections Collection When The Specific Connection Element Is Not Found
I've got a package which reads a text file into a table and updates another. I set upconfigurations so that I could import it into the SSIS store on both my dev and live servers. Now, I'm getting this error. I tried removing the configs and am still getting it.

I've been through each step and everything looks okay. Does anyone have any idea (a) what's wrong, (b) how to localise the error or (c) get any additional information? Or do I just have to recreate the package from scratch?



TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at PartnerLinkFlatFileImporter: The connection "" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at PartnerLinkFlatFileImporter [Log provider "SSIS log provider for SQL Server"]: The connection manager "" is not found. A component failed to find the connection manager in the Connections collection.

(Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------

View Replies !   View Related
SET TARGET = NULL
Hi,

I am writing an insert stamenet that appears like:

INSERT INTO SppTarget (IndicatorNumber, Part, Years, Target, CompareMethod)
SELECT '8', 'B', '20052006', '0.682', '1' UNION ALL
SELECT '8', 'B', '20062007', '0.688', '1' UNION ALL
SELECT '8', 'B', '20072008', '0.692', '1'

What if I want to SET Target = NULL in this statement, how can i do that?

Thanks in advance !

View Replies !   View Related
MSX Setup: Cannot See Target DBs?
Hi All

I have set up a MSX server for managing all of our backup jobs. However when I try and create a DB maintenence plan I can only see the system DBs not user created ones!

Is there anything I should do?

Thanks

Gopher

View Replies !   View Related
MSX Setup: Cannot See Target DBs?
Hi All

I have set up a MSX server for managing all of our backup jobs. However when I try and create a DB maintenence plan I can only see the system DBs not user created ones!

Is there anything I should do?

Thanks

Gopherman

View Replies !   View Related
Backing Up All Target Servers
Has anyone created a script that, when run against the Master database, will back up all databases on target servers?

Thanks

View Replies !   View Related
Master / Target Server
Silly question, but why can you not setup a multi-server job and have that same server as a target? What's the rational behind this?

View Replies !   View Related
SQLAGENT MASTER/TARGET
I have several target servers defined, i have a job that needs to run in a serial fashion (ie.. 1 target at at time) if every target kicks off the job at the same time it's going to bog down the disk drives... can this be serialized? or do i need to write a special routine...

View Replies !   View Related
Hyperlinks With A Target Frame
Does anyone know how to add a hyperlink with a target frame. In the navigation tab of reporting services I am having difficulites adding
http://www.linkgoeshere.com target="framename"

Thanks

View Replies !   View Related
The Target Is Not In The Current Database
Can someone please help me with the following error?



I am trying to create a trigger that copies data from a table in one database to another. However I am getting the error 'Cannot create trigger on databasename.dbo.tablename as the target is not in the current database.



Below is the SQL I am using to create the trigger:

create trigger addtotable1

on databasename.dbo.tablename

for insert

as

insert into anotherdatabase.dbo.tablename

select databasename.dbo.tablename.userid, databasename.dbo.tablename.username, biadatabasename.dbo.tablename.issuperuser

from databasename.dbo.tablename, inserted

where databasename.dbo.tablename.userid = inserted.userid

and inserted.roleid = 3



Andrew Ling

View Replies !   View Related
How To Split Three-value Column Into The Same Target?
Hi everyone,

We've got a source file which owns three different values: 'A','B','M'.

Where 'A' stands for "New Rows" and 'B' for "Delete rows" and 'M' for 'Update rows'

Using Conditional Split task we can redirect each subset into a OLEDB Destination but we are wondering how can we do the same using only one OLEDB? We've got only one table.

Thanks for your input and time,





View Replies !   View Related
What Do I Need To Use SSIS API On A Target Machine?!
Hi,

I've created an SSIS package to be loaded using my ASP .NET app. Thispackage is kept within the application and loaded via Microsoft.SqlServer.Dts.Runtime.LoadPackage method.

My target machine does not have MS SQL Server 2005 installed. So what do I need at minimum to get this up and running? What components do I've to install or copy over?

I tried to copy just the class libraries across. I copiedMicrosoft.SQLServer.ManagedDTS.dlland Microsoft.SQLServer.DTSRuntimeWrap.dll into my bin directorybut I got this error:

[COMException (0x80040154): Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.]
Microsoft.SqlServer.Dts.Runtime.Application..ctor() +25

[DtsPipelineException: Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.]
Microsoft.SqlServer.Dts.Runtime.Application..ctor() +87


I can't find any information online so any insight or advice will be very much appreciated!

View Replies !   View Related
Invalid Target Name - How Can It Be Determined?
Hi, I am sending a message to an invalid target name. The message eventually gets back to the initiator as an error type message. How can I determine the exact cause of the error - and determine that the target service name is invalid? I am using the ServiceBrokerInterface and the Message does not tell much - it seems. Also, in the sys.conversation_endpointstable, the record associated to the message only says 'Error', but no other indicator.

View Replies !   View Related
Target Queue Disabled
Hi,

I receive messages in my target queue but target queue continously become disabled even aften I enable and receive message it still says target queue is disabled?



Please Guide

View Replies !   View Related
Source And Target - Both Behind Firewall Using NAT
In this situation do I need a proxy or forwarder at both ends to prevent connection issues? Are there plans to handle this in future SSSB upgrades. Thanks.

View Replies !   View Related
Multiple Sources And One Target
I'mthinking aboug using Service Broker to aggregate transactions from 12 remote SQL Servers to a central SQL Server. Service Broker is new to me, so I don't know what the proper setup would be.

We have five different kinds of transactions that can be sent from any of the remote servers. These remote servers truly are remote, connecting to the central server over a WAN. We don't want problems with one server to interfere with any other server. We also don't want problems with one kind of transaction to interfere with the processing of any other kind of transaction.

If were only talking about one remote server, I'm guessing I'd want tocreate5 different services - one for each kind of transaction. Does that mean I'd also need to create5 different routes on both the remote server and the central server?

And how would I scale this out to a dozen remote servers? Can I stick with 5 services on the central server, or would I need to define 5 different services for each remote server, so 60 total? And would that be 60 different routes defined on the central server? Based on my limited knowledge of routes and how they're tied to services, I think that would mean 60 routes, but maybe I'm missing something.

Please let me know. Thanks.

View Replies !   View Related
Jump To URL With A New Target Page
In Opening a display of invoices in Report Server I have a ULR link in the Jump to URL section under the navigation tab.

I have the URL with the invoice number in the expression. Is there something I can add to this expression that will force the interface to Open a new windows page instead of the existing window?















View Replies !   View Related
Jump To URL With A New Target _blank
Hi all,

In Opening a display of invoices in Report Server I have a ULR link in the Jump to URL section under the navigation tab.

html code given : <a href="url"TARGET="_top">my value</a>

I would like have this code html : <a href="url"TARGET="_blank">my value</a>

How can I do it ?

Thanks.



View Replies !   View Related
Changes To Master/Target Server Configurations
I've installed SQL Server 2000 and applied SP3a.  I see SP4 is available and fixes a lot of things.  The only bit I am unsure about is section '5.3.2 Changes to Master/Target Server Configurations' 
 http://download.microsoft.com/download/1/b/d/1bdf5b78-584e-4de0-b36f-c44e06b0d2a3/ReadmeSql2k32sp4.htm#_5464_sql_server_agent_enhancements_705
"Before you apply SP4, you must complete several steps to upgrade your SQL Server 2000 master/target server configuration. The changes that are introduced with SP4 are not compatible with SQL Server 7.0 target servers, or with any servers not running SP3 or later. This is a change from the original SQL Server 2000 functionality. "
As I'm just starting out I don't even know what 'Master/Target Server' is.  I'm only installing to my PC which is XP Pro SP2. Can I ignore this step and apply the patch or do I need to do as it states?
Thanks 
 
 

View Replies !   View Related
Replication Target Table Shrinks To Zero
I want to replicate a database to a subscriber that will be used as a readonly copy. The data has to be replicated as close to instantly as possible.To do this I set up a database export of objects and data to populate thesubscriber, then I set up transactional replication. To verify thatreplication is working successfully, I count the rows in each table, thereare 3 tables in total. For one of the tables, the replication completes butalmost immediately afterward, the table starts to shrink, and after severalhours the record count is zero. This isn't happening to the other twotables, and I can't figure out why.If you have no idea what might be causing this, perhaps you can suggestsome places to start looking. This is Win2k SP4 with SQL 2000 SP3.Thanks much.

View Replies !   View Related
Backing Up All Target Servers (revised)
I am looking for a script that will do differential backups of all user databases on target servers. This script is run from the master server and must determine if new user databases have been added to the target servers and add those to the backups.

Any help would be appreciated.

View Replies !   View Related
Master / Target Maintenance Plans
I have successfully set up a Master Server with Target Servers for nightly database backups. However, I am now attempting to add additional target
servers to an already existing "maintenance plan" on the Master server and I am having some problems.

Here are the steps I have used to ADD an additional Target Server:


Edited the "Database Maintenance Plan" on the master server to ADD an additional target server.

The "job" is successfully added to the new Target Server, but the corresponding "Database Maintenance Plan" is NOT added! Because the maintenance
plan does not exist on the Target server, the job fails!

Why doesn't the "maintenance plan" get created on the Target Server when ADDING an additional target server to the "Master Maintenance Plan"?

During the initial creation of the "maintenance plan" on the master server, all "Target servers" do receive the instructions to add the job and the maintenance plan.

View Replies !   View Related
DTS Help - Identity Column In Target Table
Having an identity column in target table, how do I use SQL 7 DTS ?

In Import wizard, I chose 'ignore' in source, checked Enable Indenity Insert - DTS failed with a msg like - a column cannot have NULL...

In DTS package, had Fast load and Enable Indenity checked - no luck !

What would be the standard procs for this since this sounds like a very common
senario.

Appreciate your help very much !

-Ivan

View Replies !   View Related
Data Transfer With Possible Duplicates In Target
I need to copy data from 3 tables in one database into another db. The destination db already contains some data and it is expected that there will be duplicates which we do not want to have copied across (I think there is a constraint that prevents duplicate email addresses which is our main search field)

The three tables are effectively a user table, an address table, and a [phone] numbers table, each of which has an auto generated id field. The user table also maintains a reference to the address and numbers tables.

We are using SQL Server 8 (SP3) and it has been suggested that I use the data transformation service (DTS) tool which I have used numerous times to copy entire databases, but I can't figure this bit out.

I am still learning t-sql using SQL Query Analyzer, but have been doing so for a while and think that I'm fairly competent in it. My main question is this: Is it possible to connect to two DBs at the same time in SQL QA? If so, I'm pretty sure that I could work out how to pass the data across, I'd just need to know how to connect to them both.

Any help would be much appreciated. If you need any more information to help, please let me know.

Thanks,

Stephen

View Replies !   View Related
Merge Replic: Different Tables On Src And Target
Is it possible to treat data while merging. For example my source table is different then my target table. (target table has additional columns). Merge replication should be done in two ways.

View Replies !   View Related
Can I Use A Variable To Hold The Target Table Name.
We need to pull from a table that is named tablename_mmddyy and populate a table with the same format tablename_mmddyy. The date will be different every month so I want to be able to build the tablenames every month. Is there a way to do this in SSIS? Thank you.

View Replies !   View Related
Exception Has Been Thrown By The Target Of An Invocation
I have this exception on page loading, is there any solution for this how can I fix this exception...

View Replies !   View Related
Exception Has Been Thrown By The Target Of An Invocation
Getting this error "Exception Has been thrown by the target of an invocation" when trying to create Integration Services Project. Any ideas what can be wrong?

Thanks.

View Replies !   View Related
Delete Matching Rows From Target
Hi

I am very new to SSIS and currently was reading through various manuals but couldnt find a way to do a simple delete.

STAGE_ORDERS looks something like

order_id order_value
10050000
30070000
.....

Orders table looks like
order_id order_value
100 9000
200 1000
300 8000

now i want to use SSIS to delete the orders 100,300 which are present in staging table from the orders table .

How do i accomplish this task since i dont see a delete transformation.

How about updates if i want to update the order values from the staging table to the orders table what transformation can accomplish this

regards
Hrishy


View Replies !   View Related
Calculated Values In Target Table
Hi,

is there any way to fill up tables with calculated valued? My Source Table is build-on like this example:

CustNo / Year / Variable / Value
100 / 2005 / var1 / 321.90
100 / 2005 / var2 / 44.20
100 / 2006 / var1 / 12.09
100 / 2006 / var2 / 7.91
101 / 2005 / var1 / 23.78
101 / 2005 / var2 / 67.22

My Target Table should have this result:

KPI / CustNo / Year / Value
KPI1 / 100 / 2005 / 366.10
KPI1 / 100 / 2006 / 20.00
...

What I want to accomplish? I want to add var1 and var2 from Customer 100 in Year 2005, but is this possible with standard tasks in SSIS?

bye
Jonas

View Replies !   View Related
How To Target The File Name With Date Variable
My client will upload a file to a FTP folder regularly, but the time patten is unknown, only thing i know ishe'll usetoday'sdate as the file name.for example, he uploaded a file 'wk070705_id.txt' today, but i don't know whenthe next file will be uploaded. so how can i write a package like, eveytime when he upload a new file (for example if he upload a file'wk070708_id.txt'), i can import the data into my table? i am a very newbie of SSIS, it would be perfect if i can get a full script for this. thanks in advance with appreciation!



ps: the date in file name is yymmdd

View Replies !   View Related
Don't Receive Response From Target Server
Hi,

I have a problem using service broker, a send the message from server SSB1(initiator) and a receive this message on serverSSB2(target), but I don't receive response to SSB1...

In my server SSB2 has this messages on Profiler:
- This message could not be delivered because it is a duplicate.
- Could not forward the message because forwarding is disabled in this SQL Server instance.
- The message could not be delivered because it could not be classified. Enable broker message classification trace to see the reason for the failure.


Message fromSSB1 Profiler:

- This message was dropped because it could not be dispatched on time. State: 1


And the messages not end in both servers


Tks


Fernando Bueno


View Replies !   View Related
Duplicte Masseages In Target Queues
Hi

We have problems with duplicate messages in Service Broker queues. We have tried the "fire and forget" method.



Senario
Initiator doing SEND and END Conversation , target doing RECEIVE and End Conversation ,
Sql servar agent job runing every minute doing End conversation with cleanup in sys.conversations_endpionts queue.

We are runing 5000 - 10000 messages per minute.

When the clean up job is running we get som duplicte messages in the target queue.



Enviroment
Two separate machines runing Sql Server 2005 Standard Editon SP2


Initiator Machine


CREATE MESSAGE TYPE [TransactionStatisticsSend] AUTHORIZATION [dbo] VALIDATION = WELL_FORMED_XML

CREATE CONTRACT [TransactionStatistics] AUTHORIZATION [dbo] ([TransactionStatisticsSend] SENT BY ANY)


CREATE QUEUE [dbo].[TransactionStatisticsQueueActive] WITH STATUS = ON , RETENTION = OFF
ON [PRIMARY]

CREATE SERVICE [TransactionStatisticsServiceActive] AUTHORIZATION [dbo] ON QUEUE [dbo].[TransactionStatisticsQueueActive] ([TransactionStatistics])


CREATE ROUTE [Route::TransactionStatisticsServiceStat,0705DBB2-8CBA-43BC-A8FF-774A27F8ABC0] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'TransactionStatisticsServiceStat' , ADDRESS = N'tcp://EBSDBCONFIG1A:4022'


CREATE REMOTE SERVICE BINDING [RSB::TransactionStatisticsServiceStat] AUTHORIZATION [dbo] TO SERVICE N'TransactionStatisticsServiceStat' WITH USER = [Proxy::BtsDebitServiceStat] , ANONYMOUS = OFF


CREATE ENDPOINT [EBSDBCURR1A_BROKER]
AUTHORIZATION [BTSTULLENtsappl]
STATE=STARTED
AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = CERTIFICATE [EBSDBCURR1A]
, ENCRYPTION = REQUIRED ALGORITHM RC4)




ALTER PROCEDURE [dbo].[Bts_SP_TransactionStatPrepare]
(@TransferID varchar(30))
AS
declare
@RowCount Int,
@ErrorSave Int,
@Msg xml,
@dialog_handle UNIQUEIDENTIFIER



Begin


BEGIN TRY

set @msg = (Select
Regtime,
SendPartShortName,
RecPartShortName,
MsgType,
From btslogactive.dbo.StatTransferlog Tl
where TransferID = @TransferID
FOR XML RAW)


BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [TransactionStatisticsServiceActive]
TO SERVICE 'TransactionStatisticsServiceStat'
ON CONTRACT [TransactionStatistics]
WITH ENCRYPTION = OFF ;

SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [TransactionStatisticsSend]
(@msg) ;
END CONVERSATION @dialog_handle

END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH

End






Target Machine

CREATE MESSAGE TYPE [TransactionStatisticsSend] AUTHORIZATION [dbo] VALIDATION = WELL_FORMED_XML


CREATE CONTRACT [TransactionStatistics] AUTHORIZATION [dbo] ([TransactionStatisticsSend] SENT BY ANY)


CREATE QUEUE [dbo].[TransactionStatisticsQueueStat] WITH STATUS = ON , RETENTION = OFF
, ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[Bts_SP_TransactionStatUpdateBroker] , MAX_QUEUE_READERS = 1 , EXECUTE AS N'dbo' ) ON [PRIMARY]


CREATE SERVICE [TransactionStatisticsServiceStat] AUTHORIZATION [dbo] ON QUEUE [dbo].[TransactionStatisticsQueueStat] ([TransactionStatistics])

CREATE ROUTE [Route::TransactionStatisticsServiceActive,D8A1A78B-CEAD-4C63-B3B3-3C986D2AB3AA] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'TransactionStatisticsServiceActive' , BROKER_INSTANCE = N'D8A1A78B-CEAD-4C63-B3B3-3C986D2AB3AA' , ADDRESS = N'tcp://EBSDBCURR1A:4022'


CREATE ENDPOINT [EBSDBCONFIG1A_BROKER]
AUTHORIZATION [BTSTULLENtsappl]
STATE=STARTED
AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = CERTIFICATE [EBSDBCONFIG1A]
, ENCRYPTION = REQUIRED ALGORITHM RC4)





Activation procedure

ALTER PROCEDURE [dbo].[Bts_SP_TransactionStatUpdateBroker]

AS


DECLARE @MessageType sysname
DECLARE @ConversationHandle uniqueidentifier
DECLARE @MessageBody XML
DECLARE
@RegTime datetime,
@SendPartShortName varchar(30),
@RecPartShortName varchar(30),
@RC int



BEGIN TRANSACTION;
WHILE (1=1)
BEGIN
WAITFOR
(
RECEIVE TOP (1)
@MessageType = message_type_name,
@MessageBody = message_body,
@ConversationHandle = conversation_handle,
@SeqNo = message_sequence_number
FROM [TransactionStatisticsQueueStat]
), TIMEOUT 5000;

IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END

IF (@MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
END CONVERSATION @ConversationHandle
BREAK
END
ELSE IF (@MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
END CONVERSATION @ConversationHandle
BREAK
END
ELSE IF (@MessageType = 'TransactionStatisticsSend')
BEGIN
Begin try
SET @RegTime = @MessageBody.value('(/row/@Regtime)[1]', 'datetime')
SET @SendPartShortName = @MessageBody.value('(/row/@SendPartShortName)[1]', 'varchar(30)')
SET @RecPartShortName = @MessageBody.value('(/row/@RecPartShortName)[1]', 'varchar(30)')
SET @MsgType = @MessageBody.value('(/row/@MsgType)[1]', 'varchar(30)')

.


End try
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CONVERSATION @ConversationHandle
BREAK
END CATCH

-- EXEC @RC = SP_XXXXX



END
END CONVERSATION @ConversationHandle
BREAK
END
END
COMMIT TRANSACTION
RETURN




Sql Server Agent job procedure


ALTER PROCEDURE [dbo].[Bts_SP_Del_Conversation_Endpoints]

AS
begin
DECLARE c_PurgeConversationEndpoints CURSOR FAST_FORWARD
FOR SELECT conversation_handle
FROM sys.conversation_endpoints
with (nolock)
WHERE is_system = 0
AND [State] = 'CD';

OPEN c_PurgeConversationEndpoints;
DECLARE @DialogHandle UNIQUEIDENTIFIER;
FETCH NEXT FROM c_PurgeConversationEndpoints
INTO @DialogHandle;
WHILE @@FETCH_STATUS = 0
BEGIN
END CONVERSATION @DialogHandle WITH CLEANUP;
FETCH NEXT FROM c_PurgeConversationEndpoints
INTO @DialogHandle;
END
CLOSE c_PurgeConversationEndpoints;
DEALLOCATE c_PurgeConversationEndpoints;


end







View Replies !   View Related
Target Conversation Endpoint Not Clearing Out
Hi There



I am ending conversations properly for a dialog, the end conversation at the target properly marks the conversation as closed in sys.conversation_endpoints and sends the EndDialog message to the initiator.

The initiator get the EndDialog message from the target and ends the conversation and it clears out of sys.conversation_endpoints.



I know that to avoid reply attacks that the conversation at the target will only clear out in 30 minutes.



However it has been a day now and the conversation is still sitting in sys.conversation_endpoints as CLOSED at the target.



How do i troubleshoot this ? Why is the conversation not clearing?



Thanx

View Replies !   View Related
Conversation Enpoints Are Not Getting Cleaned Up On Target End
Hi,



We are using service broker between two different instances. But were facing issues with increasing row count in conversation_endpoints view. We found that this was because we were using default value for lifetime for the conversation which is value of size int. Later on we changed the lifetime to 1 minute and conversation_endpoints view start getting cleaned up after 30 minutes



Following commands are used to send message



Before :



BEGIN DIALOG CONVERSATION @handle
FROM SERVICE @SendService
TO SERVICE @ReceiveService
ON CONTRACT @Contract
SEND ON CONVERSATION @handle
MESSAGE TYPE @xmlMessageType(@xmlMessage);

END CONVERSATION @handle;



After:



BEGIN DIALOG CONVERSATION @handle
FROM SERVICE @SendService
TO SERVICE @ReceiveService
ON CONTRACT @Contract
WITH LIFETIME = @lifetime;

SEND ON CONVERSATION @handle
MESSAGE TYPE @xmlMessageType(@xmlMessage);



END CONVERSATION @handle;



But as we use default life time for a long due to which around 15 million records got acumlated in this view. What is the best way to clean up this view.


END Conversation @handle with cleanup is taking so long is their any other way to do this



Thanks,

Prashant





View Replies !   View Related
Multiple Initiators To Common Target
Information for configuring Service Broker when you have multiple initiators seems to be thin on the ground.
The examples are all point-to-point and seem to require separate logins for each initiator and corresponding exchange of certificates using a multi-pass installation.
E.g. Rushi's ServiceListing API.

We have an application with several hundred workstations which need to send transaction data to department servers and then on to corporate. There is also a need to replicate small amounts of reference data to all levels.

The examples seem to generate an installation and administrative nightmare.

I have a question - would the following scheme work?

1. All our machines are on the same domain so transport security can be handled by Windows Security (One less set of certificates to exchange).

2. Generate 2 Certificates on the Corporate database and export them to the department and workstations
use these pre-installed certificates in each database to handle dialog security for all converations.
Ie. multiple workstations feed into one user id on the
department server using a single certificate. Ditto for all the department servers feeding into corporate.

3. Use Service broker id's to determine where messages are sent (as the same services would be installed at multiple locations).

4. When a database is intalled/restored at a workstation does this invalidate any certificates that are shipped with it?

View Replies !   View Related
How Deploying A Local Database To Target Machine?
how deploying a local database to target machine?

View Replies !   View Related
DTS Question: 2 Source Tables, 1 Target, 3 Connections
I am converting a sp that joins 2 OPENQUERY statements to populate atarget table into a DTS package.I have the following SQL statement (whichs execute without error) thatI am required to make a DTS package out of:INSERT tbl_Sku SELECT AX.sku_id, AX.style_id, AX.style_color_id,AX.style_size_id, AY.color_id, AY.size_master_id fromOPENQUERY("SVR-1",'SELECT sku_id, style_id, style_color_id,style_size_id FROM merch.dbo.sku') as AX, OPENQUERY("SVR-2",'SELECTsku_id, style_id, color_id, size_master_id FROM ma.dbo.sku') as AYWhere AX.sku_id = AY.sku_idThe issue I am having is that I do not know how to use 3 connectionsto do this. I have one table that is being loaded from tables on twodifferent servers. Although they are currently linked servers, I wantto be able to run the existing query using a DTS package. I just donot know how to set up three connections. I have no problem with twoconnections, just three.Does anyone have experience with setting up a DTS package to insertinto the target table data from 2 other connections, where theconnection inserting into the target table is extracting from yet athird connection?I found some references to transformation lookup and was wondering ifanyone reading this has done this before.

View Replies !   View Related
Transactional Replication - Modifying Target Tables
HiI tried posting this query in microsoft.public.sqlserver.programming but gotno response.I am new to replication but I am trying to setup up a transactionalreplication of tables from one database to another in MSSQL 2000 (SP2).My target tables have primary keys defined. Under publication properties Igo to the snapshot tab and for each table I clear the check box that says"Drop the existing table and re-create-it" and "clustered Indexes." On thispage the nothing is checked. for each table.Whenever the subscription is reinitialized it drops the primary keys on mytarget tablesand replaces them with a unique clustered index on the column that used tobethe Primary key.Is this normal? Is there anyway to stop it from doing this?I don't plan to send the snapshot more than once and let transactionalreplication take over for keeping my source and target in sync, but if Iever have to reinitialize the subscription, it would seem that I (orsomeone) willhave to take the a second step of manually dropping these clustered indexesand recreating the primary keys on the target table.Thanks in advance.---Dick Christoph---Dick ChristophJoin Bytes!612-724-9282

View Replies !   View Related
Perfmon: Target Versus Total Memory
Hi,
Can anybody explain to me what's going on with my Target memory and Total memory in Perfmon?
Last week, before I upgraded my servers memory, they were both almost the same, at around 24 on the graph. Target was just fractionally above total, but there was almost no space between the two.
Then I doubled my servers memory to 4GB and expected to see total go way up and target stay the same. However, target went up to 72, and total came down to 16. When I looked this morning, target is now around 47 and total is 25.
I guess I expected these numbers to fluctuate, but not as much as this, and also why is there now such a big difference between target and total?

Thanks

Colin

View Replies !   View Related
Generating Sequence Numbers In Target Table
Hi,
What transformations can be used to generate sequence numbers in a data flow?

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved