I am writing a SP, inside the SP i have a select statement.
I also have another SP, when i run it, it sends a list back with all the users that are currently online.
Now in my first SP, i want to construct a select statement that returns a users data, but only for the users that are online.
This is the first SP (GetAllOnlineUsers), that produces a list with users online (just abit rewritten from the membership function total users online):
SELECT u.Username FROM dbo.aspnet_Users u(NOLOCK), dbo.aspnet_Applications a(NOLOCK), dbo.aspnet_Membership m(NOLOCK) WHERE u.ApplicationId = a.ApplicationId AND LastActivityDate > @DateActive AND a.LoweredApplicationName = LOWER(@ApplicationName) AND u.UserId = m.UserId
This is my second SP, in witch i want to get the online users data:
SELECT m.username, d.data1, d.data2 FROM m.userstable INNER JOIN data ON (data.username = m.username) WHERE (m.username IN (EXEC GetAllOnlineUsers))
Is it possible to exec a SP into a select statement?
If sp_helpuser showed some Username with (null) LoginName, I believe these must be some obsolete username defined in the database before ,can I have them cleaned up , anyone has this experience ?
Also, I was able to find out from sp_who, or select * from sysprocesses the active login id to the system, is there quick way ,stored procedure etc to find out the usernames for the current active session for a particular database ?? I don't think these usernames are shown up in the current activity under EM..
I have a table having single column id and n number of rows and 1 want to know all combination of id's where sum or subtraction of any two id's is equal to 7.
It seems my sysindexes table is inaccurate on a nonclustered index. In my case the rowcount (rows and rowcnt) do not match the actual rowcount of the table. The command UPDATE STATISTICS doesn't change the rows or rowcount, adding 'FULSLCAN' won't budge rowcount either.
After I did a dbcc reindex, the number of rows matched, however, upon adding rows in the table both rows and rowcount are out of sync again.
It's a fairly straightforward table, no triggers, no computed fields, only integer, datetime, varchar and bigint columns. There's a clustered index on a bigint column and a nonclustered index on a integer column.
dbcc show_statistics show that the nonclustered index is updated and it's rows and rows sampled match the number of rows in the table (not in the sysindexes-table).
I'd like to know if I'm chasing ghosts here or if there's something very wrong here. What could be causing the counts being inaccurate? Anyone who could shed some light?
SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills FROM PersonSkills INNER JOIN....
How can I choose a certain value from PersonSkills.Skills?
for example, I would like to choose a value from PersonSkills.Skills that matches a certain product. I can do the Max/Min value but the selected value needs to match the product.
The tables that I have are: PersonSkills: id, ProductID, Skills
Product: id, ProductName
Ive been reading and playing around with this without success.
I want to select Property names or owner id from table A where there is no match between owner ID in table A and B , for example Property 3 in red. In table A only one person can be listed as owner of property. In table B many people can be listed as owner of the same property.
The way i script will return property 1 twice (as no match) because the owner ID from table A '123456' mismatch twice with owner ID from table B
'111111' and '222222'. I dont want Property 1 to be selected at all because the owner '123456' is listed as one of the owners in Table B
I have two tables in the same DB, one named “Client” one named “Case”.
-One column inside Client is “ClientID”, another is “ClientCode”.
-One column inside Case is “CaseID” and another is “ClientCode”, which corresponds to the ClientCode column in the previous table.
I need a file with output that looks like this CaseID+ClientID.
So, an example row from the “Client” table” could be ClientID = 123, ClientCode=999. An example row from the “Case table” could be CaseID=555, ClientCode=999. So, I need output of 555123 .
Scenario: 2 sets of data Set 1 (200 records): firstname, lastname, address, city, phone Set 2 (100 records): firstname, lastname, address, city, phone
I run a query using phone as criteria/condition (where xx.phone=yy.phone). I got 75 matches based on the phone numbers
However, if I deleted the phone numbers from set 2 data, used below query, the return result is only 45 matches. My question is what technique that I can use to optimize the result just based on criteria like first, last, and address.
select xx.firstname, xx.lastname, yy.Firstname, yy.Lastname, xx.address, yy.address, xx.city, yy.city, yy.phone from set2 xx, set1 yy where substring(soundex(xx.Firstname),0,3)=substring(soundex(yy.Firstname),0,3) and substring(soundex(xx.lastname),0,4)=substring(soundex(yy.Lastname),0,4) and substring(xx.address,1,7)= substring(yy.address,1,7) and xx.city=yy.city;
I have a table of employees and two sites. Most employees only do shifts at the one site but may occasionally work at the other site. What i want is the details of those who have worked at both sites.
for e.g.
Employees 1 Dave 2 Peter 3 John
Site 1 Mon John Tue Peter Wed John Thu John Fri Peter
Site 2 Mon Dave Tue Dave Wed Dave Thu Dave Fri Peter
So the answer should be 2 Peter. Hope this explains the problem. Probably very obvious but it has me stumped.
In the trading (stock market) industry there is a practice of rolling up (merging) multiple trades into a single trade in an effort to save on ticket charges. The way this is done is performing a SUM() on the quantities and calculating an average price. (Average price is the SUM(Qty * Price) / SUM(Qty).
Here is my dilema: If given a set of trades, I need to loop through them and check every combination to determine which one matches the expected rolled-up final trade. In other words,
If I know that the final trade is:
15 $10
And I have the following trades in my set:
TradeId Qty Price 1 10 $10 2 7 $20 3 5 $10
I need to check the roll-up of trades (1, 2), (1, 3), and (2, 3) and determine that it final trade was made by rolling up trades 1 and 3.
In the real situation, the number of trades that I need to check is not set to a specific number.
Any help would be appreciated. Cursors, temp tables, functions, recursive calls, .NET (I am running SQL 2005 so have access to CLR) are ALL acceptable solutions...
Here is a sample SQL code (table and data) to work with.
i have two tables one with id and one with storyid which match. i need to check which record is at top of table two. and then pull top four from table one where the top story from table two (storyid) is not one of the top four in table one (id). is this possible? can anyone help?
I have a need to execute a query in T-SQL on a numeric field in a SQL table. However if there is no exact match I'd likea query that will return the row that is just below my value. As an example if the table has values: 1,2,4,5,7,9, 15 and 20 for instance and I am matching with a varibale containing the value 9 then I'd like it to return that row. however if my variable has the value 19 I want the row containign 15 returned. Is this possible? Can anyone help me with such a query? Regards Clive
Find value match in SQL table Is there any application that can compare two tables and find the similarities (there is no high rate of exact match on the field values but there are similarities)?
I'm trying to correct an error that a user is getting when running SQL Server 7.0 SP3, Desktop edition/ Windows NT 4.0 SP6. The error says that C:WINNTSYSTEM32odbcint.dll and C:WINNTSYSTEM32odbccp32.dll are different versions. I tried to install MDAC 2.5 sp1 to correct this problem but this message continues throughout the install. This is causing sqlagent service to hang. Any ideas of how to reinstall ODBC components without reinstalling the OS?
I asked this question in the mysql forums, but I am also interested in any info regarding MSSQL. I've seen databases with search systems with the functionality I seek below. I just have no idea how they are doing it. Thank you.
We have an mysql inventory database. We want to be able to put in 4984.600 and choose "match any portion" and it finds 4984600 which is in our database.
Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already.
Any ideas? or are we stuck with using MSSQL. We know this will work with MSSQL.
Thank you very much. This is a huge problem for us.
I asked this question about mysql on another forum, but I would also like to know about MSSQL. I may need to switch to this platform if Mysql doesn't work. Here's my problem:
Instead of "match", I am looking for a "match any portion" way to do searches with mysql.
We have an mysql inventory database. We want to be able to put in 4984.600 and choose "match any portion" and it finds 4984600 which is in our database.
Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already.
Any ideas?
Thank you very much. This is a huge problem for us.
SQL 2000 I am testing a query for use in Crystal Reports. It was copied from an existing query with the necessary adjustments. The first part of it works correctly;
SELECT NA.* into #cl_temp FROM OLT.dbo.NACBTR NA WHERE NA.CourseCode in ('RGF00001','RGF00002','RGF00005','RGF00006', 'RGF00038','RGF00039','RGF00040','RGF00041','RGF00042','RGF00043') And NA.completedDate >= '01/01/2006' and NOT EXISTS (SELECT * FROM hrdw.dbo.E_View EV WHERE NA.ssn = EV.ssn)
but when I add the second line;
select #cl_temp.*, ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp
I receive the error message: The column prefix '#cl_temp' does not match with a table name or alias name used in the query.
I have an Access database, that is in connection with sql server. On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] ) SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says:quote: ODBC call failed [Microsoft][ODBC SQL Server Driver][SQL Server] The column prefix 'MS1' does not match with a table name or alias name used in the query. The column prefix 'MS2' does not match with a table name or alias name used in the query.
hi..i'm new in sql progaming,i try to make make a query that in table field "match" return to "1"if no member record in another table and return to "0" if there is anyrecord member :extable member:member idA 12B 14Table Incoming.member note matchC bla..bla 1A bla..bla 0D bla..bla 1...... ....... .....can anyone help me please?D
same colored items are grouped by GroupID. Each group contains ItemID, Qty and rate. How can i compare IteamID, Qty and Rate of each group with other group's ItemID, Qty and rate? OR How can i get number of groups with same ItemID, Qty and rate?
I have configured a lookup transformation to 'redirect error' all no-matched rows to a text file using the flat file destination.
Now I want to send the same text file as an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.
Can we use the send email task on eventhandler and invoke the same in case of such error "row yielded no match during lookup" so that we can send the such non matching rows as an email.
Or else any other way to send an email after generating the text file ocntaining the non matching rows.
I want to match dates from two tables, and I want to match the six latest dates. If I have a match a will delete it from one of the tables. I will do a while loop and do a delete for every date that already exist. If ,for example, the third date not have a match the loop must do a break. but if all six is a match they all will be deleted from one table.
how to check it? max(date) max(date) - 1 max(date) - 2 ... Some one ho know and understand the question?
I have a table with 6 fields, and I will have all 6 parameters passed in - is there any way to write a query to give me rows based on matching ANY combination of 4 fields out of the 6 parameters passed in ? This is driving me crazy... short of doing an OR statement for all the different combinations - I have no idea how to do this....
This is what I have so far -
SELECT @nodeMachineType = nodeMachineType, @nodePKID = NodePKID, @biosVar = Bios, @computerNameVar = ComputerName, @diskVolumeVar = DiskVolume, @guidVar = Guid, @macAddressVar = MacAddress, @motherboardVar = motherboard FROM Nodes_Active WHERE case when Bios = @bios then 1 else 0 end + case when ComputerName = @computerName then 1 else 0 end + case when DiskVolume = @diskVolume then 1 else 0 end + case when guid = @guid then 1 else 0 end + case when macAddress = @macAddress then 1 else 0 end + case when MotherBoard = @motherboard then 1 else 0 end >= 4
I have a view where I am joining two very large tables.
I am joining where Brand and then Product match.
My problem is that there are quite a few rows where there is a Brand and Product in Table A but it is not in Table B and the other way around, Products in B that aren't in A and I need ALL of the products to show, not just the ones that are in both tables.
The field names are the same in each table, one is North America and the other is Europe. HOW do I write that?
SELECT dbo.SummaryNA.Brand, dbo.SummaryNA.Product, dbo.SummaryEU.Brand AS Expr1, dbo.SummaryEU.Product AS Expr2, dbo.SummaryNA.Supplier,
I get the error below from the following SQL. This SQL worked until I tried adding a third table "ship_to_salesrep". If I comment-out the third join and the last condition it works.
I have tried putting the "r" table in the FROM statement and still it does not work. Everything looks right to me -- what am I doing wrong?
Server: Msg 107, Level 16, State 2, Line 2 The column prefix 'h' does not match with a table name or alias name used in the query.
SELECT distinct m.inv_mast_uid, CONVERT(DECIMAL(10,4),0.00) as 'inv_cost', CONVERT(DECIMAL(10,4),0.00) as 'oe_cost', l.invoice_line_type, s.cost, h.invoice_no, h.order_no, h.order_date, h.invoice_date, h.customer_id, h.ship_to_id, h.ship2_name, h.ship2_address2, h.ship2_city, h.ship2_state, h.ship2_postal_code, h.terms_desc, h.po_no, h.salesrep_id, h.salesrep_name, h.period, h.year_for_period, h.ship_date, h.total_amount, h.amount_paid, h.terms_taken, h.allowed, h.paid_in_full_flag, h.last_maintained_by, h.printed, h.printed_date, h.shipping_cost, h.invoice_reference_no, h.invoice_adjustment_type, h.memo_amount, h.bad_debt_amount, h.invoice_class, h.period_fully_paid, h.year_fully_paid, h.approved, h.other_charge_amount, h.tax_amount, h.original_document_type, h.date_paid, h.print_flag, h.print_date, h.customer_id_number, h.date_created, h.date_last_modified, h.consolidated, h.sold_to_ah_uid, h.sold_to_customer_id, h.invoice_batch_uid, h.sales_location_id, h.source_type_cd, l.qty_requested, l.qty_shipped, l.unit_of_measure, l.item_id, l.item_desc, l.unit_price, l.extended_price, l.gl_revenue_account_no, l.gl_salse_tax_account_no, l.pricing_quantity, l.line_no, l.sales_cost, l.commission_cost, l.other_cost, l.other_charge_item, l.exceptional_sales, l.pricing_unit, l.invoice_line_uid, l.invoice_line_uid_parent into jch1.dbo.sales_history_invoices FROM invoice_hdr h, invoice_line l left join inv_mast m on l.item_id = m.item_id left join inventory_supplier s on m.inv_mast_uid = s.inv_mast_uid left join ship_to_salesrep r on h.ship_to_id = r.ship_to_id WHERE l.invoice_no = h.invoice_no and h.invoice_date >= '2006-07-01' and h.invoice_date < '2006-08-01' and l.invoice_line_type = 0 and m.inv_mast_uid is not NULL and r.primary_salesrep_flag = 'Y';
I need to add a filter clause like WHERE username = '%%%'
I know you will say 'why add a filter if you're not going to use it?' but I need to for a certain application which will use the parent query for child queries in which I select the specificity required for the child query's data set.I've tried '%*%' and '%_%' but always it returns nothing. I need the filter to exist yet not really filter.
