SQL Query To Check Against A List
Jul 23, 2005
SQL gurus...
I have one table of items that is the master file. We will call this Table A.
I have another table that contains few, some or all items from Table A. We
will call this Table B.
I want to run a query that compares the list of items on Table B against the
master list on Table A.
If the item is not present on Table B, show it in the SQL results.
Best way to achieve this? I am looping and doubling results in my sql tries.
Jeff
--
Message posted via http://www.sqlmonster.com
View 1 Replies
ADVERTISEMENT
Mar 27, 2006
I need to create daily check list for SQL 2000. Here is what I have done:
1. Check the connectivity of each server over the network. (Ping/open Enterprise Manager and proof connection)
2. Check whether the services are running for each server. (Green light)
3. Check the scheduled tasks on the production servers are running normally. Enterprise Manager of each server or your email (Set up SQL Mail to notify you).
4. Check the hard disk space available on the SQL Servers.
5. Check all the database and transaction log space on each server. If the database or transaction log space runs out, the transactions will fail.
6. Check NT event Logs for any error messages.
7. Check SQL Error Logs for any errors occurring within SQL Server.
My question is: any other tips & ideas for every day routine?
Thank in advanced!
View 1 Replies
View Related
Jan 20, 2005
Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
View 3 Replies
View Related
Jul 20, 2007
the error message shows it's now allow " =�!=�<�<=�>�>=" after sub query..
Select * From ZT_MediaImportLog Where isNumeric(ImportFileTime) = 1 And ImportFileTime < Convert(varchar(10),DateAdd(Month,-CAST (( select keepmonth from ZT_MediaImportLog, ZT_BillerChain a,ZT_BillerInfo b,ZT_Biller c,ZT_databackup d where a.BillerInfoCode = b.BillerInfoCode AND c.CompanyCode = d.Companycode AND ZT_MediaImportLog.Importsource = a.ChainCode ) AS int),GetDate()),112)
View 6 Replies
View Related
Sep 26, 2007
Is it possible to create an SSIS package that checks for a running Query on my SQL db?
I need to some how check my SQL server and see if there is a query running, if its running I need to set an indicator in my table for my app. This job needs to be scheduled and run nightly (which I can do). But how can I query SQL and see if the query is still running?
View 4 Replies
View Related
Jan 27, 2007
Hi there, I tried to make it different as usual but i´m stacked into this problem:Supose TABLE DetailsID_DET ID_CAR DETAILS1 3 1,2,3,4,5,62 4 2,43 5 5,6,7,8andTABLE Details_ItemsID_DI DETAIL_NAME1 Stereo HiFi CD2 Alarm3 AirConditioning4 LeatherSeats5 Pro Wires6 Aluminium WheelsThe problem appears when i need to bring CAR DETAILS (NAME) from TABLE DETAIL_ITEMS.Mi guess i that I should make something like:SELECT * FROM DETAILS_NAME WHERE id_di = (( Array(i) FROM Details )) one by one...I really dont know how to face it.First I thought in bringing ALL details_Items (datafieldtext = id_di and datavaluetext=Details_names) into a dataview.And then "somohow?" filter this dataview according with the Array previuosly splited by me with a For each function.Then I thought "Perhaps" there is a simpliest way to do that using SQL views, o advanced SQL QUERYS.and Finally I thought that creating a VIEW in for both TABLES would be great.The point is that, neither 1,2,3 options, honestly , I dont know how to face them.Thanks in advance, apologise my "rude" English grammar.LUCAS ( From Argentina )
View 2 Replies
View Related
Feb 29, 2008
hii,,i am using asp.net 2005 and sql server 2005.i have a web page in which i can enter details and it gets stored in a table in a database..in the table thrs a column called as sme_id,,what i want is when one inserts a new sme_id from the page,,it should check in the table so tht no duplicate sme_id wil b generated..,,this code is workin fine,,i just want to implement the above condition...here is the insert code which i have used along with sql datasource:::__________________________
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:sme_trackerConnectionString %>"
InsertCommand="INSERT INTO SME_Master(SME_Id, FirstName, LastName, Type_of_SME, Agency_id, Agency_Name, Email, Address, Phone, Mobile, Fax, TimeZone_Id, Experience, City, State, Status, Level_Of_Exam, Other_Comments, Certificate, Expertise)
VALUES (@SME_Id, @FirstName, @LastName, @Type_of_SME, @Agency_id, @Agency_Name, @Email, @Address, @Phone, @Mobile, @Fax, @TimeZone_Id, @Experience, @City, @State, @Status, @Level_Of_Exam, @Other_Comments, @Certificate, @Expertise)"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [SME_Master]">
_______hope u got my problem,,,,,reply asap....thnks in advance
View 11 Replies
View Related
Dec 1, 2003
I have a table, emailaddresses with an emailaddress field.
before i do an insert from a stored proc, i want to check if the emailaddress is already in the database.
pseudo-code:
if emailaddresssparameter is IN emailaddress then
do not insert
else
insert into table
end
i've got the insert statement and the stored proc, but how do i write the check to see if it's already there? I mean i could do a select * from emailaddress wehre emailaddress=emailaddressparam
but how to i test it? if the count=1 then skip?
here's my proc now:
ALTER PROCEDURE dbo.AddOneEmailAddress
(
@emailAddress varchar(255),
@emailID int=0 OUTPUT
)
AS
/* SET NOCOUNT ON */
insert into EmailAddresses
(email_address)
values
(@emailAddress)
set @emailID=@@identity
RETURN @@identity
View 2 Replies
View Related
Jan 9, 2007
Hello All,
It seems the following queries are causing my DB connections to time out, but I cant seem to figure out why.
These queries reside within my xml.config file.
The connection to the DB is fine and live. I get timeout errors every few hours or so.
Can anyone take a look at the queries which I pasted below and tell me why I keep getting timeout errors? PLEASE, I need all the help I can get.
<query name="Products" rowElementName="Product">
<sql>
<![CDATA[
SELECT p.*,
pv.VariantID, pv.name VariantName, pv.Price, pv.Description VariantDescription, isnull(pv.SalePrice, 0) SalePrice, isnull(SkuSuffix, '') SkuSuffix, pv.Dimensions, pv.Weight, isnull(pv.Points, 0) Points,
sp.name SalesPromptName,
isnull(e.Price, 0) ExtendedPrice
FROM Product p
join productvariant pv on p.ProductID = pv.ProductID
join SalesPrompt sp on p.SalesPromptID = sp.SalesPromptID
left join ExtendedPrice e on pv.VariantID=e.VariantID and e.CustomerLevelID=@CustomerLevelID
WHERE p.ProductID = @ProductID
and p.Deleted = 0
and pv.Deleted = 0
and p.Published = 1
and pv.Published = 1
ORDER BY p.ProductID, pv.DisplayOrder, pv.Name
]]>
</sql>
<queryparam paramname="@CustomerLevelID" paramtype="runtime" requestparamname="CustomerLevelID" sqlDataType="int" defvalue="0" validationpattern="" />
<queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^d{1,10}$" />
</query>
<query name="Ratings" rowElementName="Rating">
<sql>
<![CDATA[
SELECT ProductID, CAST ( AVG(CAST(Rating AS decimal)) as decimal (10,2)) as Rating
FROM Rating
WHERE ProductID = @ProductID
Group By ProductID
ORDER BY ProductID
]]>
</sql>
<queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^d{1,10}$" />
</query>
<query name="TotalRatings" rowElementName="TotalRating">
<sql>
<![CDATA[
SELECT ProductID, Count(Rating) as TotalRating
FROM Rating
WHERE ProductID = @ProductID
Group By ProductID
ORDER BY ProductID
]]>
</sql>
<queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^d{1,10}$" />
</query>
<query name="popup" rowElementName="popit">
<sql>
<![CDATA[
SELECT ProductID as rateID
FROM Product
WHERE ProductID = @ProductID
Group By ProductID
]]>
</sql>
<queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^d{1,10}$" />
</query>
View 9 Replies
View Related
Sep 28, 2006
Is there a global variable or something of the sort that would tell me how long it took to execute a query??
I need to monitor my DB response times and we have a query that runs in under 2 seconds. So we want to run this query every couple of minutes and if it takes more than 12 sec to run, we want to send an email to our DB staff...
I know that I could take a time stamp before and after then subtract but I wanted to know if there was an easier way to do it..
Thanks.
View 3 Replies
View Related
Sep 3, 2007
i have a table which is having size and pieces information and say it is having following 4 records.
Size, pieces, sizeID, sizecombID (SizeCombination Id)
S, 1, 1, 1
M, 3, 2, 1
L, 3, 3, 1
XL, 1, 4, 1
Now suppose user enters all these 4 records which i will pass as xml string to my stored procedure.
Is there any way to check existense of all these 4 records in this table from the xml data which i pass by using one SQL statement ? Or what is the right way of checking it, Is it through using cursor ??
Finally after checking it should retrun SizeCombID for a existing record, and if record is not existent then it should add these 4 records in table and return the new SizeCombID.
Can somebody pls help on this..
View 7 Replies
View Related
Oct 23, 2006
anyone know how to query a list of databases?
current the sp_helpdb seems cant help me, because it query all the details, i want to do it like select * from database where database id=2
View 5 Replies
View Related
Apr 2, 2008
I have a table that has a list of skills, ie "HP One", "HP Two"
I need to pass the these skills from my applications search page to a stored procedure, hence I have a snippet of the SP below.
DECLARE @SkillSet NVarChar (200)
SET @SkillSet = '-HP One-,-HP Two-'
SET @SkillSet = Replace(@SkillSet, '-', '''')
SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN (@SkillSet)
The following sp does not return any results, but when i set the last line to ..
SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN ('HP One', 'HP Two')
It returns a set of results. Also when I do a select on the variable @SkillSet, ie SELECT @SkillSet, it displays 'HP One', 'HP Two'
Can enybody help me here, i Know i'm doing something wrong, but I cant think of what it is.
Thanks
View 6 Replies
View Related
Oct 17, 2007
Can someone help me with Query to list all indexes (name, table, field etc.) on a particular user/dbo database in sqlserver.
Thanks
View 3 Replies
View Related
Jan 31, 2008
Does anyone have any way to run a query against Sharepoint to return the content of a list through transact SQL?. Perferably without having to go throught the "back door" and access the Sharepoint database directly?
Thanks
View 1 Replies
View Related
Aug 1, 2007
in table Databackup
company keepmonth
----------------- -------------------
001 12002 12003 6005 607917 609747 6
I run this query
select Max(keepmonth) as keep from Databackup
why the result is 6 not 12? I think the max value should 12 , have no idea why it return 6
does my query error?
thank you
View 1 Replies
View Related
Dec 27, 2001
Hello,
I have a dynamic query in the stored procedure, and the code looks something like this:
SET @section_test = 'SELECT sectioncode FROM ' + @tablename + ' WHERE sectioncode = "' + @condition + '"'
EXEC (@section_test)
What I need to do is try to check if the query returns any values using EXISTS (possibly), but at the same time I don't want to return the results of that dynamic query's select statement in my stored procedure. Is it possible?
Thanks.
Regards,
Katya
View 1 Replies
View Related
Nov 27, 2000
we tried out the following code in query analyser -
create procedure TrialProc
as
select * from sakjdhf
when we executed this piece of TSQL in query analyser, we expected it to give an error or warning that no object by the name of sakjdhf exists ( as actually there is no such table or view in the database ). however to our surprise we got "command completed successfully " !!
does this mean the SQL server does not check for necessary objects when creating a stored procedure ? or is there some setting that we missed out whihch is causing SQL server to overlook the error in the code ?
View 3 Replies
View Related
Oct 20, 2014
how to check multiple parameter instead of dynamic query.
View 1 Replies
View Related
Mar 23, 2008
i'm pretty familiar w/ mysql, but relatively new to ms sql. Since i can do a lot more w/ ms sql, I have been trying to do all tasks in sql queries or scripts, but i'm stumped on this one. I have a table that links two things together. t1 has rows id1 and id2. When a row has these two id's, there is a "link" between them. I'm trying to make a command or script that will see if there is only 2 links for any given id.
for example, it would loop through each row, and run this command:
SELECT COUNT(*) AS Expr1
FROM links
WHERE (Id1 = this_rows_id1) OR (Id2 = this_rows_id1)
and again w/ id2
and then i would be interested in any row that only had a count of 2. is this possible?
View 2 Replies
View Related
Jul 23, 2005
Hi,I would like to check if a string value exist in a string in sqlserver stored procedure, e.g.set @testString = 'this is my test document.'if (@testString contains 'test')begin.....endHow do I do this in sql server stored procedure?Thanks,June...
View 1 Replies
View Related
Aug 9, 2013
I want to check if a table is empty using query code?
How to do this?
View 4 Replies
View Related
Aug 21, 2015
query that checks to see if a variable contains special characters; except for hyphens, periods, and accents?
View 3 Replies
View Related
Oct 31, 2006
Hello I have a newbie question. If I have a table of the form:Table1{id, name} with the valuesid: 9 , name: test1,id: 7 , name: test2,id: 3 , name: test3,id: 15 , name: test4, id: 5 , name: test5,id: 13 , name: test6,.........If I have a list generated from user selection ( LIST{1, 7, 8, 15} ,) will I in a way be able to use this list in a query of the form, thus only having to make one query to the database: SELECT id, nameFROM Table1WHERE Table1.id in LIST Or is the solution to make multiple queries to the database, one for each member of the list, of the form:SELECT id, nameFROM Table1WHERE ID = @IDThanks in advance /dresen
View 4 Replies
View Related
Jul 7, 2007
Hi:I have created a table that has a column of smalldatetimes. For any given day, there may be different records with the same date but with different times. I've created the query SELECT Event FROM Events where Start=@Start and I input start as a selected date from the calendar control. Because of the different times, I do not get any matches. Could someone help me with a query that will get all of the events on the same day? Regards,Roger
View 2 Replies
View Related
Jan 20, 2008
I have a drop down list populated using sqldatasource1 it is populated with [item]
However, I need it to be poplulated with the three fields below AND, I don't want duplicates.
SELECT [Item], [Alias1], [Alias2] FROM [Authors]
View 6 Replies
View Related
Sep 21, 2004
Can some one please tell me whats a best way ot get a list of all the tables that are present in a particular database.
Thanks
View 1 Replies
View Related
Sep 1, 2006
How can I list indexes with a (SQL) query ?
Thanks
View 1 Replies
View Related
Sep 18, 2006
What could be the query or the stored procedure to list locks or deadlocks ?
Thanks
View 2 Replies
View Related
Mar 5, 2014
I'm running SQL Server 2012 Express. I need to query a database and return the list of tables in the database. How to construct this query?
View 4 Replies
View Related
Sep 2, 2014
I am doing parameterized queries from Visual Basic in Visual Studio.
I have a query where I want to do something like this.
Select * from people where city in (<list of cities>)
The problem is I want to build my <list of cities> in Visual Basic and pass it to the SQL as a parameter.
I know I can't do this:
Select * from people where city in (@ListOfCities)
Currently, I'm doing this by writing the <list of cities> out to a separate table, just so I can do the query.
Select * from people where city in (Select CityName from CityTable)
View 4 Replies
View Related
Feb 24, 2008
Hi,
I am a newbie, this is my first post (please go easy).
Iam at the moment trying to set up a query for someone looking for a property on an estate agents website.
From a drop down menu, the user can:
select an area (where they may like to live) from a list of areas.
select an amount of bedrooms from a list of bedrooms
select a minimum price from a list of prices
select a maximum price from a list of prices.
The query I worked out for this is as follows:
$data = mysql_query("SELECT * FROM property WHERE area like '$area' and bedrooms like '$bedrooms' AND price BETWEEN '$min_price' AND '$max_price'") or die(mysql_error());
This seems to work fine and shows all the properties that meet the criteria onto my webpage.
However, I then thought, someone may not care which area they live in and want to see all properties in all the areas, so I decided to add the option 'All areas' to my 'areas' list, I then did the same for the other lists, eg 'all bedrooms' option to my bedrooms list and so on.
I am now trying to write a query that incorporates where the 'all..' option is selected and have become very stuck!
Can someone set me off in the right direction for this.
I hope that makes sense?!?!
View 20 Replies
View Related
Nov 19, 2015
I need to retrieve list of all databases and the size occupied by them in sql server. As of now am pulling data for each database using sys.dqatabase_files which is hard for me as there are around 40-50 db on SQL Server.
View 4 Replies
View Related
