Hi,
I want to achieve the following. Please let me know if it is possible or not. If not reasons.
There are 2 servers A and B. Server B has one service and queue available and some data is available in the queue. The data in the queue is online and being received by some other database. Now I want to access the data in the queue in server B from a service which is residing in Server A.
In short I want to continuosly poll the Queue on Server B from Server A. Please let me know whether this is feasible or not.
I need to connect to a SQL server thats running in say abc.trident.com and also sits in a shared environment..
I have couple of questions
1) That SQL server is accessible from my network, yet when I swtich on my enterprise manager I am unable to view that in the list of running SQL servers in the populated list.
Is it because its sitting in a shared environment I am unable to view that?
2) What is the connection striing I should use to connect to the server..When I try to configure a SQL server registrations it normally asks for SQL Server name along with the user authentication
Should I mention fill the Server: field as
abc.trident.com
or
// abc.trident.com/abc.trident.com
to connect to the clustered server.
Can some one tell me the proper connection string if both aforementioned ones are incorrect?
When I run XP_sendmail,it says the 'mail sent',but the mail will just sit in the outlook out box.It is getting sent only when we try to close outlook or when we hit send button in out box.The SQL mail is setup for internet mail on outlook97.Can anyone help?
Hello, This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A. When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back? 1. Message is left on the Queue. 2. is the worker procedure triggered again for the same message by the queue? 3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B. For my scenario messages are independent and ordering does not matter. Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
My problem is that when I run a query in EM with the date supplied as parameter 'mm/dd/yy' I get the correct number of records.
If I run with parameter formatted as 'dd/mm/yy' then I get 0 records because, I guess, sql expects first part to be 'mm'.
I have windows and EM set as 'English' (i.e. 'dd/mm/yy'). The values in the tables also display as 'dd/mm/yy', so:
1) WHY must I supply a date parameter for a sp as 'mm/dd/yy'. 2) How should end users enter the date. 3) Can I store 'dd/mm/yy' and 'mm/dd/yy' format dates in the same table?
Please help, I am a little confused. Thank you in advance. Regards - Rehman
When I run XP_sendmail,it says the 'mail sent',but the mail will just sit in the outlook out box.It is getting sent only while we exit outlook or when we hit send button in out box.The SQL mail is setup for internet mail on outlook97.Can anyone help?
I've been having trouble getting the SqlCacheDependency to work without polling (which is set up through SqlCacheDependencyAdmin or aspnet_regsql). If you check out the documentation it says things like: Microsoft:
I have a sql server database which is a huge database(let us call as database 1) and highly secured. I create another database which is small in size (let us call as database 2). I create tables in database2 which requires input from database1. In order to achieve this i create views in database2 for the input from database1. Now when ever records are added in database1 view needs to be updated. Now here is tehq query. 1. when ever records is inserted in database1 views needs to be updated in the database2. how can i achieve this ? 2. if the answer is triggers or if are there are any ways to deal with it please let me know
I have an application which needs recently inserted records from SQL Server. SQL Server gets raw data from third party and makes summary out of it. This summary needs to be read from SQL Server whenever it is ready. The application currently polls the DB and gets the data. Instead, Is there a way to get the summary pushed from SQL Server to application. Your help is highly appreciated.
Somebody got an idea about how to implement the following in SQL 2005? Or does anybody know of an example posted somewhere for this? In my opinion it's such a common problem, someone somewhere is bound to have written something about this somewhere (but I could nog find it).
We've got a table in a database and regularly an event happens that inserts a record in this table. At that moment a signal should be given to a .NET-service (in the form of a XML-message) which will do some intelligent stuff with this info.
In SQL 2000 I would implement a polling mechanism that every 10 secs (or some other appropriate interval) would do a select on the table to see if anything had changed. But with all the new features in 2005, I'm of the opinion that this could be done differently.
I've been looking at: Notification Services, but this looks a bit like overkill for such a simple demand. Query notifications from the Service Broker. But this is also a bit much because deletes are also a cause for a notification. Also all the articles I found are dealing with cache expiration. (Afterthought... maybe SELECT MAX(id) will work as defined query)
Ofcourse it would be nice if the signal is queued somewhere when the receiving service is temporarily not available.
Hi all, I'm in the process of cleaning up a very VERY poorly implemented system. One of the problems I'm tackling consists of :
1 - Database A 2 - Database B (different schema than A) 3 - A task which regularly polls A, checks for updated/new records and inserts them (with some transformation) into B
As you can imagine, the overhead of the polling is killing the system. What I want to know is :
What is the best way for A to notify B whenever there are changes to a particular table on A ?
I'm running 2000 but we will most probably upgrade to 2005.
if the value of a paticular cell in the table has changed since last poll,
then initiate the second task
2. do a select query that picks about 10,000 new rows off another db table,
the 10,000 rows should then be stored in a in-memory dataset.
Every time the poll initiates a new select query, it should insert the new rows to the existing in-memory dataset.
thus if the select runs for 2 times in 2 minutes, the the in-memory dataset would contain a maximum of 20,000 rows.
3. Then I want to apply a set of transformations on the dataset and then finally update some db tables, push some records to the ssas database. (push mode incremental processing)
which sub tasks can be achieved and which cannot.
if not, Is there a workaround?
Please do provide some specific links that accomplish some of these similar tasks.
I have tested some functionality, like
doing a full processing of a ssas database.
reading from a database table and inserting into a flat file.
I tired to use the ExecuteSQLTask, and i also assigned the resultant to an user:variable. the execution completed succesfully but I am not able to see the value of the variable change. also I am not able to use the variable to figure out a change in previous value and thus initiate a sql select. or use the variable to do anything.
I have an application that is reading a message from a Service Broker Queue. When I use integrated security with an NT account it works fine. When I use a SQL Server User through Management Studio I can select from the Queue however, when I use this same account through the web app with the SQL Server User, I cannot see the Queue. Is there a grant that I must do to this account to get it to see the Service Broker Objects?
I am implementing a message queue system in SQL Server 2000. My queue table looks something like this:
[MessageId] [uniqueidentifier] NOT NULL, [MessageType] [uniqueidentifier] NOT NULL, [Status] [tinyint] NOT NULL, [SubmittedTime] [datetime] NOT NULL, [StartTime] [datetime] NOT NULL, [DispatchedTime] [datetime] NULL, [CompletedTime] [datetime] NULL, [MessageData] [image] NULL
This is how I retrieve the next message for processing:
SELECT TOP 1 * FROM [Queue].[MessageQueue] WITH (ROWLOCK, UPDLOCK, READPAST) WHERE [StartTime]=@pStartTime AND [MessageType]=@pMessageType AND [Status]=@pStatus ORDER BY [StartTime]
and mark it as being processed:
UPDATE [Queue].[MessageQueue] SET [Status]=1 WHERE [MessageId]=@pMessageId
After message has been processed I delete it from the queue:
DELETE FROM [Queue].[MessageQueue] WHERE [MessageId]=@pMessageId
All database accesses are transactional with default READ COMMITTED. The problems start when there are a few concurrent accesses: I get deadlocks when retrieving next message. If I do not delete message after processing then there is no deadlock. But this is not what I need.
I played with different isolation levels and locking hints and was able to avoid deadlock using TABLOCKX:
SELECT .... FROM [Queue].[MessageQueue] WITH (TABLOCKX)
But in this case you cannot concurrently retrieve messages which was my goal from the beginning. How do I achieve this?
I had to restore a database in one of the staging servers the other day. However, upon completion, I found out that the service broker queue in the database is not working anymore. The service broker queue error was similiar to this error: The activated proc dbo.procedure_name running on queue database_name.dbo.queue_name output the following: '<error message>.'
Hello everybody, I'm not completly aware of the SQL server 2005 possibilities so I'd need an hints from somebody with a wide knowledge to understand the direction to take!
This is what I have to do.
I insert into a table XML message. the messages are pushed automatically by an application I have no ""control" on and I get several messages "at the same time".
Everytime the message is inserted into the database I need to trasform the XML data into the correspondent relational value and I know already that in some cases it could take a while (1 second can be considered a while..) My worry is that in the moment I process one message I loose the other one inserted after ,,,
There is some tool that helps me to handte the process as I would..
[I originally posted this in the General Tools forum, but perhaps I'll get better response here...]
I am wondering if this is a bug or a (strange) feature: when a SQL Server Agent job step is a CmdExec job (properly configured for owner/proxy, etc), the step is invoked with the proper user credentials (of the 'Run As' account); HOWEVER, the job step doesn't ever seem to be logged in and thus the step does not pick up the critical local group membership(s) such as BATCH (my preference) or SERVICE, etc. Provided that the command consists solely of built-ins or specifically-permissioned executable invocations (permissioned to the specific 'Run as' account), all is well. However, if the command attempts to invoke general OS exe's (cmd.exe as a sub-shell, whoami, ftp, etc -- anything that is generally permissioned via a local group such as BATCH or SERVICE), such invocations fail with access denied. Specifying a CmdExec step with 'whoami /all' as the command demonstrates the missing group memberships.
(The only workaround seems to be to either crawl through the OS exes and assign permissions, or to grant administrator membership to the proxy credentials account.)
So, design feature or major annoyance?
[Additional info I left out: SS05 (same behavior on SP2 and SP1); on WS03R2-x64; in an Active Directory domain; 'Run as' account granted log on as service, log on as batch, etc.]
We've set up a report farm with two servers, both 64 bit with 4 CPUs each. One has 16Gig and the other 8Gig of memory. We're using Windows NLB and the load test software confirms that the NLB is working. When we run a number of concurrent reports, both servers get utilized, but they only work on a few at a time. The report server queue doesn't seem to be fully utilizing the hardware. From a prior post I've learned that the report server queue automatically runs 4 reports per CPU. This is not occuring for our setup. Has anyone else experienced the same? Are there any configurations that need to be set to open the queue up? The reports are heavy (300,000 records grouped and summed). Does this affect the queuing process?
Im getting this error when trying to set up a cache dependency...are there any special permissions etc?From CS:SqlCacheDependency dep = new SqlCacheDependency("MySite-Cache", "Products");Cache.Insert("Products", de.GetAllProductsList(), dep); From connectionStrings.config:<add name="SiteDB" connectionString="Data Source=localhost,[port]SQLEXPRESS;Integrated Security=true;User Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />Also tried this using my machinename<add name="SiteDB" connectionString="Data Source=<machinename>,[port]SQLEXPRESS;Integrated Security=true;User Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" /> From web.config: <caching> <sqlCacheDependency enabled="true" pollTime="10000"> <databases> <add name="MySite-Cache" connectionStringName="SiteDB" pollTime="2000"/> </databases> </sqlCacheDependency> </caching> EDIT: So making progress I can't seem to get the table registered for cache dependency:The sample i have says"aspnet_regsql.exe -E -S .SqlExpress -d aspnetdb -t Customers -et"and the command line response is "Enabling the table for SQL cache dependency..An error has happened. Details of the exception:The table 'Customers' cannot be found in the database."Where does this "Customers" table come from? There is obviously not an application specific "Customers" table in aspnetdb I'm confused probably more by the example than anything....
I have a 2GHZ cpu with 1GB of RAM. I occassionally see very slow (long) queries against a local SQL Server 2005 Express (SP2) database. The issue occurs against different SQL Queries, but all queries are rather basic select statements Perfmon shows that the SQL Server counter for the "MEMORY GRANT QUEUE WAIT Avg MS" gets extremely high (25000+ ms). Perfmon also also shows that PAGING is not occuring, and the system is not under unsual stress. The problem is not reproducible with MSDE.
Has anyone seen this issue, or have any recommendations for a next course of action?
I have a requirement where once we create a new record in a table, we submit a query to fetch some data and save it in one of the columns of the newly created record. The main requirement is that the server where we fetch the data from can be down for sometime for regular maintenance and we do not want to loose the fetch query in that process. Is there a way we can implement this? Thanks.
I was testing my error handling and purposefully failed some messages. Automatic posion message detection kicked in and disabled my queue. I tried the following, one at a time to enable it again but it doesn't work:
ALTER QUEUE MigrationQueue WITH STATUS = ON;
ALTER QUEUE MigrationQueue WITH STATUS = ON, ACTIVATION (STATUS = ON);
I would have thought the first line would've worked but I get the following when trying to receive...
The service queue "MigrationQueue" is currently disabled.
I am running a basic SSB queue setup (more or less the Hello World example)and running into the following error message:
Transaction (Process ID 120) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
At first, I thought it was because I had the Initiator and Target Services on the same Queue, but I get this error even when I separate the two Services onto two Queues. This happens when I run more than one Target application receiving messages from the Target Queue.
Does anybody have any idea what could be happening here? Am I not allowed to set up more than one receiver?
I have a queue that, after running fine for several days will mysteriously turn off. It doesn't seem to be related to a poison message because I can restart the queue and processing resumes just fine. What are all the scenarios that would cause a queue to turn itself off, so I can 1) take preemptive action to prevent it from happening in the first place and 2) respond appropriately when it occurs.
Also, how to properly setup and verify that the BROKER_QUEUE_DISABLED is working properly. This is the SQL that I have so far, but is there a more direct way to raise the event other than writing an activated stored procedure that rolls back 5 times?
I have sent messages and they are all sitting in the transmission queue with a blank status, why is service broker not trying to send them ? They are no errors in the sql log. BOL says this is blank when it has not tried to send the message ? Service broker is definately activated in the database.
How do i force sql server to send anything in the transmission que ?
Running transactional replication, dedicated server for distributor. While performance in terms of latency is excellent (usually 1 sec, almost never higher than 4) the disk queue length on the distributor is extremely high (over 6 usually). Is this typical? On any other server I would be very concerned, but cpu and memory usage are excellent and as said, latency is good. what is recommended config for distributor? others see high queue length?
I have created Queue with the following syntax. But it is not getting activated itself. What I have to do to get it activated itself, and what could be the frequency by default.
CREATE QUEUE NewCustomerQueue WITH ACTIVATION (PROCEDURE_NAME = prProcessNewCustomers, STATUS = ON, MAX_QUEUE_READERS = 1, EXECUTE AS SELF) GO
If I execute the prProcessNewCustomers procedure manually it is showing that the Queue has been activated. What change I have to make in the syntax to get it activated itself.
Actually I have two scenarios in my requirement,
1. One Queue processing immediately when it receives data (Order Processing)
2. Another Queue, Process when the server is idle i.e., off-peak time (for mailing)
(1) When I was monitoring the SystemProcessor Queue locally ( Via a term server login onto the box ) I would see a queue of 3-4. If I monitor the same parameter from a remote PC, I see a Processor Queue of 1 - why? The box had 1 GB RAM ( SQL used 500 MB and had 250 MB free according to Task manager ).
(2) I have another almost identical box that has same CPU but twice ammount of RAM ( 2 GB ) but has SystemProcessor Queue of almost 0 - why?
Again one client with SAN EMC and again performace is several times worse then you can have with a cheap and primitive IDE drive... :(
Anyway, my question. I am monitoring many parameters, including Avg Read Queue and Avg Write Queue. So if I ReadQueue=3 and WriteQueue=7, what does it mean?
Scenario 1, there are 2 different Queues (R- read request, W - write request): Windows --> Device Read. Queue: R:R:R Write Queue: W:W:W:W:W:W:W
Scenario 2: Windows --> Device Common Queue: W.R.W.W.R.W.W.R.W.W
In other words, if SQL server flushes writes (TRAN COMMIT or CHECKPOINT), generating hundreds or even thousands of write requests in few milliseconds, so Queue grows to 100-300 for a second or so, are read requests locked during that time?
