Help: Should Sys.service_queues.object_id Be Subset Of Sys.dm_broker_queue_monitors.queue_id?
Apr 4, 2007
I am having trouble sending messages between service broker, set up on 2 different machine ( different domain ). The ports on which service brokers listening are bidirectional.
for some reason the messages get piled up at the sys.transmission_queue and transmission_status seems to be empty.
I found the following link and tried to find status of all message queues.
SELECT t1.name AS [Service_Name], t3.name AS [Schema_Name], t2.name AS [Queue_Name],
CASE WHEN t4.state IS NULL THEN 'Not available'
ELSE t4.state
END AS [Queue_State],
CASE WHEN t4.tasks_waiting IS NULL THEN '--'
ELSE CONVERT(VARCHAR, t4.tasks_waiting)
END AS tasks_waiting,
CASE WHEN t4.last_activated_time IS NULL THEN '--'
ELSE CONVERT(varchar, t4.last_activated_time)
END AS last_activated_time ,
CASE WHEN t4.last_empty_rowset_time IS NULL THEN '--'
ELSE CONVERT(varchar,t4.last_empty_rowset_time)
END AS last_empty_rowset_time,
(
SELECT COUNT(*)
FROM sys.transmission_queue t6
WHERE (t6.from_service_name = t1.name) ) AS [Tran_Message_Count]
FROM sys.services t1 INNER JOIN sys.service_queues t2
ON ( t1.service_queue_id = t2.object_id )
INNER JOIN sys.schemas t3 ON ( t2.schema_id = t3.schema_id )
LEFT OUTER JOIN sys.dm_broker_queue_monitors t4
ON ( t2.object_id = t4.queue_id AND t4.database_id = DB_ID() )
INNER JOIN sys.databases t5 ON ( t5.database_id = DB_ID() )
What I noticed is the values of sys.service_queues.object_id and sys.dm_broker_queue_monitors.queue_id are totally different. I am not sure whether this has any impact on why the messages are stuck at transmission_queue
1) Comparing 2 versions of same database. 2) Keeping track of schema versions of Tables & Stored Procedures so that I can rollback to previous schema.
I have started with Sys.Objects, Sys.Tables & Sys.Columns and I kept things rely on Object_ID but the problem started whenever I change some part of Table. It assigns new Object_ID to the table whenever I change it; moreover creationdate of object is also changed. So I assume that it drops and creates new table.
Please throw some light on following questions, 1) When and How Object_ID Changes? 2) Is there any other thing in database which is Unique & Not Changing with alteration of object? 3) What should be the preferred way to Compare 2 Database?
I hate readymade tools as they charge per user and my requirements are very small.
I restored a development database to a test database on the same server. I thought this would be a good thing to do because testers would have the same data as developers and the tables would be exactly alike. However, I noticed that the object_id is identical for the tables. For example, the Accounting Calendar in the development database and the Accounting Calendar in the test database have the same Object_id. Could anyone verify that this is or is not going to cause me trouble?
I have an employee table (empl), and a labor table (lab). The labor table is populated whenever an employee clocks in/out. If an employee does not clock in, they do not appear in the labor table.
I need to capture employees whose time is less than 8 hours, or whoever did not log in at all that date. If I run the following query without a WHERE clause, I get all of my employees, with NULL data in the labor table if they did not log in that day. This is good.
However, if I add
WHERE (DATEDIFF(s, tt.StartTime, tt.EndTime) < 28800) OR (tt.StartTime = NULL)
I get all employees whose time is less than 8 hours, but do not get any of the employees who did not work at all that date, and that's expected... there should NEVER be a NULL in the labor table - it is populated whenever somebody clocks in/out.
So, how would I query the resulting table to get those that worked less than 8 hours, and those that did not?
FROM (select empl.EmpNum as EmpNo, empl.FFName as FirstName, empl.FLName as LastName from empl where empl.ftermdate IN ('1900-01-01 00:00:00.000') AS et
LEFT JOIN (select MIN(lab.StartDt) as StartTime, MAX(lab.EndDt) as EndTime, lab.WorkDt as WorkDate, lab.EmpNum as EmpNo from lab where lab.WorkDt in ('2008-03-03 00:00:00.000') group by lab.WorkDT, lab.EmpNum) AS TT
I am trying to exclude patients from a dataset. There are multiple records per patid in this dataset. I have the following code:
SELECT meds.PATID, meds.MEDICATION, meds.MEDTYPE FROM meds INNER JOIN patient ON meds.PATID = patient.PATID WHERE (((meds.MEDTYPE) Not In ("FI (Fusion Inhibitor)","NNUC (","Non-nucleoside","NRTI & NNUC","NRTI (Nucleoside/tide Rev","PI (Protease Inhibitor)")));
I want to exclude all patient records if the patient had any of the above exclusions ever. If they have the exclusion if one record get rid of the rest of the records for that patient. Right now the code only excludes the particular record.
My table contains customer records with multiple records per customer. As a result of a query, I´m only interested in one record per customer with the highest value of a certain field in the record.
I thought of using DISTINCT, but can I use DISTINCT on a subset of all fields? Or sort the table in a certain way that the query result only shows the first unique records for a customer.
I am trying to update a small subset of records of a given table (TRValue) using the records contained in ParcelTemp. The difficult part is getting the summation from a child file, TRGreen, for those same parcels contained in ParcelTemp. Instead of updating just a few records, all the records in TRValue are being updated, with the wrong values of course!
Basically, Update records in TRValue that are equal to:
Year = P.Year Code = 'LG01' Parcel = P.Parcel
with the summation of child records where the child records needed are:
Year = P.Year Parcel = P.Parcel
Code: UPDATE TRValue SET Acres = SumAcres, CurrentMarket = SumMarket, CurrentTaxable = SumTaxable, CurrentTaxAmt = ((SumTaxable * D.CertifiedRate) + 0.50) FROM ParcelTemp P
The problem is to find a subset of rows such that each value in each of two columns (animals and food brands in this example) appears in at least one row. The purpose is to produce a set of samples from a large table. The table has a animal_name column and an food_brand column; I want a set of samples that contains at least one of each animal_name and at least one of each food_brand, but no more than necessary.
CREATE TABLE Feeding_Options (license_nbr INTEGER NOT NULL PRIMARY KEY, animal_name VARCHAR (10) NOT NULL, food_brand VARCHAR(15) NOT NULL); INSERT INTO Feeding_Options VALUES (6401715, 'rat', 'IAMS'),
[code]....
To frame the problem better, her are the values in each column:
In this data, (6401715, 'rat', 'IAMS') and (1058337, 'rat', 'IAMS') are interchangeable, as are some of the other rows. There can be more than one minimal solution whcihmight be the whole set.
How would I write a select statement that would return multiple fields in a records based on a a distinct of one of those fiels.
Example
Table Name : Sales Table Field Name : Name Address Phone Zip Sale Rec1: Peter Smith 12 Market St 999-999-9999 12345 99.99 Rec2: John Jones 73 Broadway 999-999-8888 12345 12.34 Rec3: Charle Brown 42 Peanuts Ave 999-999-7777 12345 34.56 Rec4: Peter Smith 12 Market St 999-999-6666 12345 67.89 Rec5: John Jone 73 Broadway 999-999-5555 12345 36.52
How would I be able to return the columns Name Address and Phone based on the distinct of Name.
I need to be able to sum items grouped by op_wkctr and op_part and op_date. Here's my latest version which of course does not work.
SELECT op_wkctr, pcs, hrs.b FROM dbo.op_hist INNER JOIN ( SELECT op_part, SUM(op_qty_comp) as pcs FROM dbo.op_hist where op_type='BACKFLSH' group by op_hist.op_part
UNION ALL
SELECT op_part, SUM(op_act_setup + op_act_run) as b FROM dbo.op_hist where op_type='LABOR' group by op_hist.op_part ) AS hrs ON op_hist.op_part = hrs.op_part where (op_wkctr = 'P-P36' or op_wkctr = 'P-P38' or op_wkctr='P39') and op_date ='10/22/07'
I could do this even with my limited knowledge of SQL but I'm surethere's a slick way that might be dead easy. My way hardly seems worththe effort . If not thanks anywayCol1Col2Col3QWQWQWQAQBQBQXWQXWQXWQXAQXBQXBEvery time there is a change of group (Col1 and Col2), I want to startand increment Col3 so I end up with;Col1Col2Col3QW001QW002QW003QA001QB001QB002QXW001QXW002QXW003QXA001QXB001QXB002
Can I install SQL Server on a machine and use less than the # of processor on the machine. In a UNIX world, I'd call it LPARing with Oracle and AIX, and they only let me do this with Enterprise Edition. With Windows, I think the only way is using virtual machines and attaching processors to them? Do any vendors offering LPARing? Can I take any edition of SQL Server and subcapacity price so that I only pay for the processors I'm using?
What about SS Express? It only scheds to a single core - so could I put that on a larger machine?
This question has been posted on the site before but I could not find any resolution....I want to return rows 11 - 20 from a query that returns 100 records without using a cursor or temp table.
The closest query I have found is a query that numbers the rows, but I can't seem to use rownumber in a between clause...
Use Pubs SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber FROM employee e WHERE job_id = 10 ORDER BY emp_id
how do i calculate all the checkdigits for a subset of ids and return all the ids and checkdigits? basic calculation isn't the problem, just how to select the ids and utilize the ids in the calc. I am new to trying to do calculations on data.
I'm sure this is an easy problem but my brain is fried today...however how do I do the following:
I have a two column table. One is a key field where duplicates can arise and the other is a datetime field. So you might have some records looking like this:
OK, how do I get the top 1 of each key so that I get a subset of records looking like the following: 1231999-06-14 12:17:11.000 8901999-06-15 10:00:18.000
I have a need to dump a subset of a database from the server (SQL Server) to a notebook via the network, for data entry to be done on the notebook when it is in the field & not connected to the network & then the changes made to this data on the notebook to be applied to the database on the server.
The application for the front end to this is in Access. Would MSDE be the way to go for the database on the notebook ?
It's a small application with not many users, likelihood of conflicting edits is small.
Would the data transfer best be done with replication or with DTS ? Presumably replication would allow options for control over conflicts, such as the same bit of data being changed on the server & on the notebook’s copy of the data ?
I need guidance re direction to head in with this.
Hi,I was wondering if you guys have any nth script to reads from tableand outputs into a temp table subset of records. There was a nth toolI used to use it was GROUP1 which was written in C and it used to bevery fast on nth -in a flat file. In this program we used to pass fewparamaeters. For example if I want 30,000 records from the file of500,000. The function seams to work something like this. you dividethe 30,000 records of 500,000 which will result with .090909090909.Now we would pass only the first 7 digit (0909090) as parameter thatwould nth the file down to 30,000 records. This function allwaysworked whichever number you use as long as the read file is largerthan output fileI like to use the similar concept in Sql Server and I was wondering ifanyone has any script to do this or how to go about this?Thank you. I appreciate your feedbackagron
I want to break up a set of search results into small chunks. For instance, think about how Google displays a block of ten results out of the entire set. Selecting the top 10 is dead easy with "TOP 10"; how do I select the next 10?
Obviously one option would be to select the top 20, and programmatically discard the first 10, but surely there is a better way? I am doing this for a ASP.NET 2.0 application, and if I can retrieve just what I want, I can DataBind to a Repeater, and let ASP.NET do all the hard work of displaying the data.
I need to pull records from single table such that I get a subset defined like this:
( acctcode = 'xh364' and product = 'T&E' )
And return all of the rest of the records:
( acctcode = '%' and product = '%' )
Can I do this within a WHERE clause, or will this require CASE / ELSE? There will be other specific acctcode/product rules that will be added later. I could do this with a UNION, but I need to avoid that if possible.
Hi all, I’ve a table with production objects, and another with possible items composition of the object. I need to count how many occurrences of each standard composition appears: Table PROD: PROD_ID COMPONENT TYPE --------- ----------- ----- 1 AAA X1 BBB Y2 AAA X3 BBB Y4 AAA Y5 AAA X5 BBB YTable ITEM_COMPITEM_ID COMPONENT TYPE-------- --------- -----7 AAA X7 BBB Y8 AAA X9 BBB Y The result should be: ITEM_ID OCCURRENCES-------- ----7 28 19 1 Table PROD have millions of rows, my way is too slow (I’ve a loop where each PROD object are separately queried against the ITEM_COMP), some have an idea for a most efficient way?
A table gets data every 4 minutes, I only need spread of every 15 minutes. Can I select only records spread every 15 minutes apart from this table without having to run a scheduled job every 15 minutes and loading one record closest to getdate() at that point into another table(this is how I am doing it now) Is there a better way. Please help Thanks
There is a production database which has ever increasing data. For testing purposes though, I would like to build a test database with exactly the same schema but only a subset of data copied from the production database . I'll specify the criteria (something like a where clause in select query) for copying the data from the production database.
Is there a tool that anyone has come across to do this job ?
I have multiple tables with information about a user. The tables are Roles, Users, Groups and Profiles.
For a user session I need information from all those tables. Would it be better to make a table called UserSession and collect the necessary data from the above mentioned tables and stick them in one the UserSession table or should I just write a query that goes out and gets the data from the different tables.
I have a cube with some dimensions (dim1,dim2,dim3).I want to add another dimension (dim4) based on dim1 but more restricted.Eg. Dim1 is a product dimension, and I will create Dim4 from a subset of Dim1 (only few and predetermined products).
I will create a view (or a named query) with the appropriate where clause, create a dimension on this view/namedQ, then add on the existant cube and link it on dimension usage. Great. But in this way in the cube I haven't all fact data but only data for the product of Dim4! (Dim1 is still present).
eg: in fact data I have a total of 100$ of sales for all the products, if I link the Dim4 then the sales amount is 40$, that is the value for the product in Dim4 not for all product (I can't set the filter for Dim1/Dim4 in excel). I haven't found any way for get the 100$.
I try with two Date dimension: the first from 01/01/2010 to 31/12/2015 and the second from 01/01/2015 to 31/12/2015. If I link both I see only data for the second period (only 2015).
it seems that the more restrictive dimension trumps.What's wrong? There are other ways to get what I want? I've tried the property "DependOnDimension" but without results.
I won't publish the attribute used in the WHERE clause on the dimension.
I have been messing around with trying to replicate sql server 2000 tables to postgres as well as mySQL. I'm starting with just one table and trying to get that working, it has around 9000 rows, all of which copy to postgres (with some datatype issues).
However, when I try to replicate to a mySQL database however, only the same 152 rows get copied every time.
I compared rows that were copied to ones that were not to see if there was any obvious differences between the two and couldn't find any.
I am trying to implement custom paging. I want to get a subset from my Threads and Post tables by userID. But I can't make the stored proc work. Could somebody have a look at this and tell me what I am doing wrong or if there is a better way of doing this?
ALTER PROCEDURE [dbo].[syl_ThreadPost_GetSubsetSortedByUserID2]
@UserID uniqueidentifier,
@sortExpression nvarchar(64),
@startRowIndex int,
@maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
BEGIN TRY
IF LEN(@sortExpression) = 0
SET @sortExpression = 'PostID'
-- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment
SET @startRowIndex = @startRowIndex + 1
-- Issue query
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT t.[ThreadName],
p.PostID,
p.[PostTypeID],
p.[LanguageID],
p.[PostAccessID],
p.[UserID],
p.[ThreadID],
p.[PostParentID],
p.[VoteSummaryID],
p.[Subject],
p.[Body],
p.[PostAuthor],
p.[PostDate],
p.[IsApproved],
p.[TotalViews],
p.[FormattedBody],
p.[IPAddress],
p.[PostCount],
p.[ArticleCount],
p.[TrackbackCount],
p.[IsSticky],
p.[StickyDate]
FROM
(SELECT t.[ThreadName],
p.PostID,
p.[PostTypeID],
p.[LanguageID],
p.[PostAccessID],
p.[UserID],
p.[ThreadID],
p.[PostParentID],
p.[VoteSummaryID],
p.[Subject],
p.[Body],
p.[PostAuthor],
p.[PostDate],
p.[IsApproved],
p.[TotalViews],
p.[FormattedBody],
p.[IPAddress],
p.[PostCount],
p.[ArticleCount],
p.[TrackbackCount],
p.[IsSticky],
p.[StickyDate],
ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') AS RowNum
FROM syl_Threads t RIGHT OUTER JOIN syl_Posts p
ON t.[ThreadID] = p.[ThreadID])
WHERE t.[UserID] = ' + CONVERT(nvarchar(16), @UserID) + ' )
AS syl_ThreadPostInfo
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(16), @startRowIndex) + ' AND (' + CONVERT(nvarchar(16), @startRowIndex) + ' + ' + CONVERT(nvarchar(16), @maximumRows) + ') - 1'
-- Execute the SQL query
EXEC sp_executesql @sql
RETURN
END TRY
BEGIN CATCH
--Execute LogError_Insert SP
EXECUTE [dbo].[syl_LogError_Insert];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).
CREATE TABLE #tblTemplateBlocks ( TemplateID int, BlockID int, OrderID int
[Code] ....
I have a table called TemplateBlocks which contains which Blocks are on a Template. In this example - just one template - with three Blocks.
Table tblFields contains a list of Fields that are on each TemplateID/BlockID. In this example there are 3 fields on each TemplateID/BlockID pair.
Before I can use a template, I have to check that, in tblFields, for each Template/BlockID pairing - one of the fields must be set as the Stage Base (I cannot have 2 fields as StageBase or no fields as StageBase). In the example data above, the data would be okay as each Template/BlockID pairing has one row where StageBase is true.
Having checked that each Template/BlockID pairing has a StageBase, I need to check that each row where StageBase is true has a value for the WeekStart column and that, taking into account the order of the Blocks in tblTemplateBlocks, the values in WeekStart for each TemplateID/BlockID pairing are getting progressively bigger.
So, for example, the example data above would fail because the third TemplateID/BlockID pairing has no value for the WeekStart column in the row where StageBase is true.
If I added a value of 2 for WeekStart in the row for the third TemplateID/Block that has a StageBase of true - again the data would fail because, taking into account the order of the Blocks - the values for WeekStart would be 0,3,2 and these numbers need to increase.
0,3,4 would be fine. 0,3,10 would be fine. 0,3,3 would fail.
I can do this easily using a cursor or two - but how to do this without cursors.
