I have a problem using service broker, a send the message from server SSB1(initiator) and a receive this message on server SSB2(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 from SSB1 Profiler:
- This message was dropped because it could not be dispatched on time. State: 1
I am new to BIZTALK 2006; Please help me get started as below>> I have XML Request (send) and XML Response (receive) data; What are the steps needed for this to be accomplished in BizTalk to create webservice for this? THX
I am trying to send messages between 2 different server instances. I am getting the following errors in Profiler:
"This message could not be delivered because the user with ID 0 in database ID 14 does not have permission to send to the service. Service name: 'TestService1'."
"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 scripts for object creation and messaging is following at the first server instance:
USE master GO CREATE ENDPOINT SBroker STATE = STARTED AS TCP ( LISTENER_PORT = 1212 ) FOR SERVICE_BROKER ( ENCRYPTION = DISABLED ); GO
USE Test GO CREATE QUEUE TestQueue WITH STATUS=ON
CREATE SERVICE TestService AUTHORIZATION dbo ON QUEUE TestQueue
BEGIN DIALOG CONVERSATION @dh FROM SERVICE [TestService] TO SERVICE 'TestService1','2B7CE76A-9804-46F3-9AE8-0AE59313613A' ON CONTRACT [DEFAULT] WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @dh MESSAGE TYPE [DEFAULT] ('this is message1');
DECLARE @status nvarchar(1024); SELECT status = GET_TRANSMISSION_STATUS(@dh);
END CONVERSATION @dh;
on second server instance:
use master GO CREATE ENDPOINT SBroker2 STATE = STARTED AS TCP ( LISTENER_PORT = 1212 ) FOR SERVICE_BROKER ( ENCRYPTION = DISABLED ); GO
in first server, in sys.transmission_queue transmission_status is empty
Both servers in the same domain, and databases on this server has the same owner.
on both servers, select @@version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I have a SQL Server 2000 runing for production. Recently it is frozened occasionally. Ath the time, no response from SQL server even I use Enterprise Manager, I can not connect to the server.
So there is no way to fixed only reboot the server. Aftrer that, I checked the error log and go the info as:
SQL Server terminating because of system shutdown. LogEvent: Failed to report the current event. Operating system error = 31(A device attached to the system is not functioning.).
After reboot, I checked the error log again, seems no special error except:
Attempting to initialize Distributed Transaction Coordinator. Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
What's the possible reason for this issue? how to figure out and slove this problem?
When I try to connect to a SQL server instance from Enterprise Manager, I'm getting a timout connection error. I have to change the timoeout parameter from 4 (the default) to 30 in order to work. Also I realize that some applications (like sharepoint) are having the same problem connecting to that server.
My question is:
Why is that happening?
It used to work fine, and I'm getting this issue a couple of days ago.
I have html files which want to store in database SQL Server with data type image. How store and receive html file from database SQL Server by ASP.NET. Data type image is pointer 16 bit to file html. So where will content of files html with their image store ? Can I help you.
Hi All,I have to write a stored procedure which will send/Receive text filefrom/to a server by using FTP.Is anybody have done anything on it? or know about it.If yes, I would like to know about it. (also provide the storedprocedure code if possible)Thanks in Adv.T.S.Negi
I have a VM with Windows Server 2012 and SQL 2014 installed. Some things about the VM: Using VMware Workstation 11. Created with 160 GB hard disk and 4G memory (that's all we could do when needing to run multiple VMs from the box we're using). I am able to get to the internet and to our network but the internet connection tells me that there is no internet access and we can't seem to fix it.When I attempt to create a backup of one of my DBs, I receive the attached error. No other backup has been created for this particular DB so it would not need to overwrite anything. This is an issue for all of the DBs that are in the SQL Server and not just an isolated DB.
We ran DBCC CHECKDB against the DB and no errors were returned.A bit about the DBs: Our software creates a number of empty DBs upon install. For testing purposes we have DBs that are populated with data that we restore once the install is complete. So our procedure is, once all services are shut down for our software and the SQL connection has been cut, we delete the DBs created during install and restore the DBs that will replace them. All of the DBs restored successfully so I don't understand why there would be an issue with backing them up.
I am working with a client that after every reboot of there SQL 2000 DB server, they experience slow response time for a couple of hours. The server has 12 GB RAM and a Dual 3.8 processor. It is believed that the slow response is due to as queries run after the reboot, they are re-building information in memory and after the memory is built up, it goes back to the normal performance utilizing the memory for speed. Is this an accurate assumption or is there something else to be looking at after the server is rebooted?
I am new to SSIS. I need to read the files in a remote server folder and ftp them to the other remote server folder.
I know , I have to set up FTP task and FTP task must use a variable to provide the path information. But I am not sure how I can do this. Anyone suggest me with the script task to populate the variable and steps involved in this.
I need to know if I am able to use OLE DB connection strings without the username and password for Challenge/Response logins.
I have a web site that uses SSL and Challenge/Response to authenticate users, but my connection to the database is by embedding a generic username and password in the connection string.
I would like to leave that off and have the connection to the database use the challenge/response authentication when they first logged into the web site. This way I can control their permissions in SQL server.
I have 2 servers (say MAINSRV e SECSRV) running SQL2000 Standard SP3 on Windows 2000 Advanced within a NT (!) domain and each server is linked to the other.
My problem is that if I run a query returning few dozens of rows like:
SELECT * FROM MAINSRV.DbName.dbo.TblName TBLA WHERE Fieldx = 'anyval'
from a client connected to the SECSRV server, it takes something like 35 minutes to complete, while the same query completes in no time when run on clients connected to MAINSRV.
Even the simplest SELECT Count(*) FROM... takes more than one minute from SECSRV while completing in a fraction of second from MAINSRV.
I tried to change the linked server security options (SQL/Windows), but the remote query remains slow.
There are no locks active on the table, both the servers have almost no load (CPU less than 10%, when tested) and the query returns just a few KBytes, so communication overhead will not be the problem.
Any suggestions will be very appreciated, thank you!!!
I have recently installed SQL 2005 client tools with SQL Server Management Studio and accessing databases on a SQL 2000 server. The response I am getting is extremely slow. Should I go back to SQL 2000 client or are there methods by which I can improve the performance.
I have two servers, one production server and one backup server which have transactional replication with a pull subscription.
When I configure replication, it works fine during our test weekends testing production load. After tests, replication looks fine for a random number of days. Then, all of a sudden, an error message is displayed on one of the agents: "The agent is suspect, no response within 10 minutes." This has happened a number o times. If I remove replication and configures it again, it always works. Sometimes it works by just updating one of the tables and the error message disappears. The last time (today) that did not work. Updating the database did not replicate and the error message remained.
Has anyone experienced this same problem and has a god solution. One thing that is common is that the error message appears after long times of inactivity on the servers, or perhaps after a restart but that I am not sure about.
Question 1: How can I prevent this error message?
Question 2: Are there any special things to think about when I need to restart the servers and replication is configured, e.g. after installing updates from Windows Update.
I would be very grateful for any answers regarding this.
Please assist, the issue with SQL 2005 Browser and SQL 2000 Server Service is understood.
Our problem is with networked 2000 instances and SQL Express. The SQL 2000 machines (Standard & MSDE) do not have SQL 2005 or Express installed only 2000. When a SQL Express computer is put on the network with the SQL Browser service running almost all SQL 2000 machines lose sight of the other SQL 2000 instances. The second the SQL Browser is turned off on the SQL Express box the SQL 2000 machines can see each others instances. It appears that the response from the SQL Express SQL Browser causes the SQL 2000 machine to stop listening for responses. Once in a while one of the SQL 2000 instances will show up with the SQL Browser active on the network and it is my belief that it is because that response made it in before the SQL browser response. Please help as this does not appear to be a recognized issue. I'm assuming there aren't many sites running as many named instances on individual machines like we do.
Please note this appears to be a problem with SQL Express Browser not SQL 2005 Standard's which runs without problems on our network.
I'm looking for a way to list the target servers associated with a master server. The reason is that we're moving to another master server, and I'd prefer not to move the targets manually.
I've got most of the T-SQL already (sp_msx_enlist, sp_add_jobserver), but I'd like a scripted solution instead of a wizard.
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
When I launch Outlook, it takes forever for the program to finally open. With any inbound email, it stops processing whatever is underway at the time....and frequently there is a 2-3 second lag between keyboard input and what appears on the screen. SQLserver is usually consuming upwards of 1-gb of memory....help. Mike
Sql Server 7 Standard EditionMy Server was renamed and the normal steps of reinstallingand running sp_dropserver and sp_addserver were followed.Everything seems to work fine but jobs from the master server.I did defect the server as a target server since it appeared as offlineanyway.On trying to re establish the status of the server as a target serverFailed to make "serverName" a target serverEnlist into Master Server "MasterServerName" Failed(Error 14262)Your insight is appreciatedVincento
I'm not even sure this is possible but I'm using MERGE in a process that has 3 source tables (the process steps through each source table sequentially) and I need to delete from the Target database occasionally.
My current code is
sqlMerge = "MERGE " + TableName + " AS target USING @CData AS source" + " ON target.TotRsp = source.TotRsp AND target.ClientRef = source.ClientRef AND target.dbPatID = source.dbPatID" + " WHEN MATCHED THEN" + " UPDATE SET dbPatFirstName = source.dbPatFirstName, dbPatLastName = source.dbPatLastName,
[Code] ....
The Target db data is made up from several different clients and when the MERGE runs it uses TotRsp, ClientRef and dbPatID to uniquely match a source row to the target row and if no match it inserts the source row.
My problem is that when this runs with Source A first, it will delete all merged data from Source B & C. Then when Source B runs it will insert all Source B data but delete all from A & C and so on.
Is there way that that I can include additional clauses into NOT MATCHED BY SOURCE THEN so it knows only to delete when data has come from say Source A. Once the data is in the target table there is no reference to which source table it came from tho.
If there isn't a solution I suppose I could always add an extra column to the target db to indicate which source it came from and then have something like
NOT MATCHED BY SOURCE AND t.Source = 'SourceA'.
That's quite a bit of work my end to do that tho so I'd like to be sure it works.
I was able to build and Preview the report, but ran into an issue when trying to deploy the report that I created. After reviewing various postings on this issue, I still can't seem to get past the issue.
Currently running SQL 2014 Developer Edition, SQL Server Data Tools for VS 2013, and O/S Windows 8.1 all on the laptop. I followed the video and left the default value of LocalHostReportServer. System said LocalhostReport Server could not be found.
I went to SQL Configuration Manager and tried using the Logon as the URL. Still received the same error.
I went to Reporting Services Configuration Manager and used the URL, still received the same error.
Hi, I am less of a technical but more of a analyst professional and right now investgating on various tools / options for the new conversion project I will be leading in insurance client. One of the tools that client want to use is SSIS but the source and target database is not on SQL server but plans are to build a staging SQL server database for transformation. Does SSID supports this kind of ETL process where both source and target system are non SQL servers.
declare tableName table ( uniqueid int identity(1,1), id int, starttime datetime2(0), endtime datetime2(0), parameter int )
A stored procedure has new set of values for a given id. Sometimes the startime and endtime are the same, in which case I update the value of parameter. Sometimes I add a new time range (insert statement), and sometimes I delete a time range (delete statement).
I had a question on merge, with insert, delete and update and I got that resolved. However I have a different question regarding performance of the merge statement.
If my target table has hundreds of millions of records and I want to delete/update/insert a handful of records, will SQL server scan the entire target table? I can't have:
merge ( select * from tableName where id = 10 ) as target using ...
and I can't have:
merge tableName as target using [my query] as source on source.id = target.id and source.starttime = target.startime and source.endtime = target.endtime where target.id = 10 ...
This means I cannot filter the set of rows in the target table to a handful of records where id = 10.
I am created a SSIS package to export data. I am exporting query data to a flat file to a different server. I tried to use the UNC path and it failed saying could not access the file. How can I create a SSIS package to export data from one server to another?
I have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?
I have a SQL Server 2005 box that was moved from a domain environment to a non-domain environment and was never dropped from its original domain before moving over. Subsequently a new domain was created at the data center this SQL Server box now resides in. The machine was dropped from its old domain and joined to the new one.
All the while the service accounts were set to the local admin account. When I tried to start the Configuration Manager I got the message "Connection to target machine could not be made in a timely fashion.". I read some blog entries and tried changing all the service accounts to Local System and the error message still came up.
All of the SQL Server services run fine. It is just this management console that I cannot open. I wasn't with this employer when this box was moved to the data center so I do not know if it worked prior and there isn't anyone here that could give me its history.
OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS I'VE DONE AND HAVE WORKING:1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image) 2) I've also able to load the file using a Handler as below: using System;using System.Web;using System.Data.SqlClient;public class HandlerImage : IHttpHandler {string connectionString;public void ProcessRequest (HttpContext context) {connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NWS_ScheduleSQL2000"].ConnectionString;int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);SqlConnection myConnection = new SqlConnection(connectionString);string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@Image_Id";SqlCommand cmd = new SqlCommand(Command, myConnection);cmd.Parameters.Add("@Image_Id", System.Data.SqlDbType.Int).Value = ImageID;SqlDataReader dr;myConnection.Open(); cmd.Prepare(); dr = cmd.ExecuteReader();if (dr.Read()){ //WRITE IMAGE TO THE BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'> <a href='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>File </a>- Click on this link, I'll be able to download or view the file WHAT I WANT TO DO, BUT HAVE PROBLEM:- I would like to be able to read CONTENT of this file and load it in a string as belowStreamReader SR = new StreamReader()SR = File.Open("File.txt");String contentText = SR.Readline();txtBox.text = contentText;BUT THIS ONLY WORK FOR files in the server.I would like to be able to read FILE CONTENTS from SQL Server.PLEASE HELP. I really appreciate it.
I did a load testing and found the following observations:
1. The Memory:Pages/sec was crossing the limit beyond 20.
2. The Target Server Memory was always greater than Total Server Memory
Seeing the above data it seems to be memory pressure. But I found that AvailableMemory was always above 200 MB. Also Buffer Cache HitRatio was close to 99.99. What could be the reason for the above behavior?
In working through some examples, sometimes I will see this pattern for receiving messages: What is the purpose of the "nested" WAITFOR (RECEIVE? What is this actually doing? Is it receiving the same message in both RECEIVE?
WAITFOR ( RECEIVE @dh = [conversation_handle], @message_type = [message_type_name], @message_body = CAST([message_body] AS NVARCHAR(4000)) FROM [Queue]), TIMEOUT 1000; WHILE @dh IS NOT NULL BEGIN IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' BEGIN RAISERROR (N'Received error %s from service [Target]', 10, 1, @message_body) WITH LOG; END END CONVERSATION @dh; COMMIT; SELECT @dh = NULL; BEGIN TRANSACTION; WAITFOR ( RECEIVE @dh = [conversation_handle], @message_type = [message_type_name], @message_body = CAST([message_body] AS NVARCHAR(4000)) FROM [Queue]), TIMEOUT 1000; END COMMIT;
Other times I will see this pattern for receiving messages: Why do a RECEIVE TOP(1) instead of just a RECEIVE?
WAITFOR(RECEIVE TOP(1)
@conversationHandle = conversation_handle,
@messageTypeName = message_type_name,
@messageBody = message_body
FROM [Queue]), TIMEOUT 1000;
And other times I will see this pattern for receiving messages: What is the purpose of RECEIVING into an in-memory table when you can just process the message directly?
WAITFOR(RECEIVE
queuing_order,
conversation_handle,
message_type_name,
message_body
FROM [Queue]
INTO @tableMessages), TIMEOUT 1000;
IF (@@ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK; END
What is the difference between the three approaches from an architectural and performance perspective? I need to process messages as fast as possible and I'm not sure why or when each should be used. Also, does the timeout have any impact on how FAST messages will be processed, or is it exactly what it says - a timeout - if a message is not found within the period then the procedure will break?
I am trying to set up a stored procedure to retrieve to 20 messages from a queue into a table to implement a batched process. I have the following code in a stored procedure.
WAITFOR ( RECEIVE top (20) -- get batched so that we can process same listid once message_type_name, message_body, -- the message contents conversation_handle -- the identifier of the dialog this message was received on FROM dbo.target into @PayloadData ), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away
However, the stored procedure is only retrieving 1 message at a time from the queue. Did I miss some other setting
Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows. Here is my logic - 1. Create Ole DB source to RemoteSERVER - using SELECT stmt 2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output). 3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows 4. I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE; But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing. And I do not see error in my package either. It is not failing.
