Stephen Lloyd

request.cmsDSN = WinderemereDSN

Search for column names and return Table names

SELECT name FROM sysobjects WHERE id IN

(SELECT id FROM syscolumns WHERE name like '%wft%') and xtype = 'u'

Subquery

SELECT                ID, fk_metauser_id, description, username, wProspect.ID as wProspectsID, u.agentMetauser_id

FROM                wProspect

JOIN (                SELECT                metauser_id, agentMetauser_id

                FROM                metauser

                WHERE                username like '%lloyd%'

                        AND agentMetauser_id <> 0

) u

ON fk_metauser_id = u.metauser_id

No list price - QA

Get the listingID of all listings that do not have a list price.

Use that to get the mainID from CMAComp

Use that to get the records from CMA Main

records in CMA Main also list the agent and the contact name

SELECT a.cmaMainID, a.fkContactID, a.fkAgentMetaUserID, a.subjectListingID, a.active,                                 b.metauser_id, b.first_name, b.last_name, b.username,                                                c.firstname, c.lastname, c.street, c.city, c.state                                        

FROM cmaMain a, metauser b, cms_contact c                                                                

WHERE cmaMainID IN(                                                                

SELECT fkCmaMainID                                                        

FROM cmacomp                                                        

WHERE fkCompListingID IN (                                                        

SELECT  listingID                                                 

FROM listingdata                                                

WHERE listprice IS NULL                                                

AND saleprice is NULL                                                

)                                                

)                                                        

AND a.fkAgentMetauserID = b.metauser_id                                                                

AND a.fkContactID = c.con_id                                                                

ORDER BY cmaMainID                                                                

Here’s a query that’ll find duplicates:

select count(*), username, password from metauser

group by username,password

having count(*) >1

SELECT count(*) AS howmany, username, password, metauser_type_id

FROM metauser with(nolock)

GROUP BY username,password, metauser_type_id

HAVING count(*) >1

ORDER BY metauser_type_id

You can't use Replace() and many other functions on a "TEXT" type column

The code below replaces commas with carrots in a TEXT column

SELECT TOP 500 id, fk_metauser_id,fk_con_id,Replace(substring(sch_string,1,datalength(sch_string)),',','^'),creation,description, AEN, AENAGENT,username,lastsearched,aentype

FROM wProspect

ORDER BY lastsearched desc

queries for images on sqlpri and elephant02

USE Windermere

GO

 

/* This does a simple listing of the rows in the agentimages table that have the correct entry for the pics number.

Substitute the ## in the quotes for the imagestore database you want to check. Sorted by last entry descending. */

 

select * from agentimages with (nolock)

where imagestore = 'pics44'

order by lastmodifiedaudit desc

 

select * from agentimages with (nolock)

order by lastmodifiedaudit desc

 

select * from agentimages with (nolock)

where imagestore = 'elephant.windermere.com.imagestore44'

order by lastmodifiedaudit desc

 

/* This does a simple listing of the rows in the listingimages table that have the correct entry for the pics number.

Substitute the ## in the quotes for the imagesgtore database you want to check. Sorted by last entry descending. */

 

select * from listingimages with (nolock)

where imagestore = 'pics44'

order by lastmodifiedaudit desc

 

select * from listingimages with (nolock)

where imagestore = 'elephant.windermere.com.imagestore44'

order by lastmodifiedaudit desc

 

/* SPECIAL SECTION FOR NO IMAGES AND IF THE BRIDGE/COLLECTOR IS SUSPECT */

 

/* This does a lookup for the row entry in the listingimages table using the MLS number. This will make sure that the

actual listing made it across the Bridge from PostGres into SQL so that the ColdFusion side has a way to find out what

Imagestore DB has the images in it on the Elephant02 server.  Just replace the number in quotes with the listing number

you are checking on.  If this query does not find it for the correct imagestore db, then the Bridge or Postgres side/Collector

is not working correctly.  Archway could be fine, but there is no row in the listingimages table to lookup the images with

on Elephant02. If there is a row with the listing number, then you can use the information in the next query.*/

 

select * from listingimages with (nolock)

where listingnumber = '676393'

order by lastmodifiedaudit desc

 

/*  On the Elephant02 server, you will want to use the same listing number as above but in a different query.  At the top left menu on this SQL

Management Studio, click on "Connect" and log into the Elephant02 Database Engine the same way you did for SQLPRI.  It will then appear

in the lefthand column as another SQL server.  Click on the "Databases" choice then find the imagestore database you want to run

the query against.  Here is the query using the same listing number from above to find the row(s) of images for the listing number

that is in question.  This might take awhile to run but if it comes back blank, then no images have been inserted into the imagestore

database for that listing.  If rows are returned, make sure the column for the database pics number is the correct number for the

imagestore database the listing is pointing at from SQLPRI (shown in the imagestore column from the query directly above).*/

 

SELECT     *

FROM         Images

with (nolock)

where caption like '%676393%'

 

/* End of Special section for Bridge/Collector suspect.  Other queries below are useful for other purposes */

 

 

/* This does a select of rows in the agentimages table that do not have the correct string for the location of the images.

Substitute the ## in the quotes for the imagestore database you want to check.

If you find any rows that contain the string in the quotes below, then use the next sql statement to update the agentimages

table to the correct string. Sorted by last entry descending. */

 

select * from agentimages with (nolock)

where imagestore like '%pics44%'

order by lastmodifiedaudit desc

 

/* This does an update of rows in the agentimages table that do not have the correct string for the location of the images.

Substitute the ## in the quotes for the imagestore database you want to locate then update.

Run this then run the above query again to check that it did actually update the rows you expected. */

 

UPDATE agentimages SET imagestore = 'pics44'

WHERE imagestore like '%elephant02.windermere.com.imagestore44%'

 

select * from listingimages with (nolock)

where imagestore = 'elephant.windermere.com.imagestore44'

order by lastmodifiedaudit desc

 

/* This does a select of rows in the listingimages table that do not have the correct string for the location of the images.

Substitute the ## in the quotes for the imagestore database you want to check.

If you find any rows that contain the string in the quotes below, then use the next sql statement to update the listingimages

table to the correct string. Sorted by last entry descending. */

 

select * from listingimages with (nolock)

where imagestore like '%elephant02.windermere.com.imagestore44%'

order by lastmodifiedaudit

desc

 

/* This does an update of rows in the agentimages table that do not have the correct string for the location of the images.

Substitute the ## in the quotes for the imagestore database you want to locate then update.

Run this then run the above query again to check that it did actually update the rows you expected. */

 

UPDATE listingimages SET imagestore = 'pics43'

WHERE imagestore like '%elephant.windermere.com.imagestore44%'

 

/* This query does a quick count of the total rows in the Images table on the imagestore database.

Just modify the imagestore## to look at whichever db you want to check on */

/* Don't forget to "connect" to the Elephant02 SQL Server & open a new query window for it before you do the query below */

 

 

USE imagestore44

GO

SELECT COUNT(*) FROM images

 

/*  */