Select website from wce_contact where uniqueid like
'O,U4Ba-l2Nmc' or uniqueid like
'O90kja8,aofc' or uniqueid like
'OIiZnaAHj1fc' or uniqueid like
'oho9mc2HA4fc' or uniqueid like
'ogJrMa.YV9mc' or uniqueid like
'PNocYaQtA6fc' or uniqueid like
'NLWTfcUSbXec' or uniqueid like
'O_eHjaNhPtfc' or uniqueid like
'OK(Qkau,H(fc' or uniqueid like
'nYcUfcCQbXec' or uniqueid like
'O-R5oa2C-2fc' or uniqueid like
'O-R5oa2C-2fc' or uniqueid like
'nPYhfat5eJec'
Is there anyway i can select these contacts without having to copy and paste 'or uniqueid like' numerous times?
I have two product tables in two different databases, both contain thousands of records. I have to write a query that suggests matches on similar codes, and have come up with:
SELECT TB1.product, TB2.product FROM TB1 JOIN (select distinct product from db2.dbo.TB2) as TB2 --this table has PK of product and warehouse ON TB2.product LIKE '%' + TB1.product+'%'
which DOES work, but because the table have many rows,takes time to do it... is there a way of rewritting this query, so it gives a faster result?
Hi,I have a new job. It needs to drop and re-create (by insert) a tableevery night. The table contains approximately 3,000,000 (and growing)records. The insert is fine, runs in 2 minutes. The problem is thatwhen I create the indexes on the table, it is taking 15-20 minutes.There is one clustered index and 11 non-clustered. This is a lookuptable that takes many different paremeters, so it really needs theindexes for the user interface to run efficiently. However, thedatabase owners aren't keen on a job taking 20 minutes to run everynight.Any ideas?
I have a small archiving SSIS pacakge that I use to archive off old orders. I was OLEDB and found that I would be deleting about 1 order (plus all its relational records) a second. Try to find some speed I tried the ADO.net connetion and turn pooling on with a min of 10. Now I am getting about 100 order records deleted a second.
Has any one else found that that ADO.net connection is faster than the OLEDB (I would have thought it would be the other way around).
The
Control flow items I am using are the Execute SQL Task and the queries are like this
Insert into arcprod.wh1.pickdetail Select * from wh1.pickdetail where status='9' and orderkey = @Pram1 and pickdetailkey not in (select pickdetailkey from arcprod.wh1.pickdetail)
...
delete from wh1.pickdetail where orderkey = @Pram1
I can't find the answer to the following question.
Why is Visual Studio quicker then the ReportServer.
I've got a report with more then 20 parameters and several are muli-select.
When I'm opening the report in Visual Studio it appears with in one or two seconds whereas I open it on a reportserver it takes 8 to 15 seconds to open or to refresh. After that I can enter the report parameters and then the report is running in approximately the same time.
I know that the refresh is by default, so I can't change that, but the users have problems with wating 15 seconds between entering the parameter values.
The development machine is 2,8 GHz P4, the reportserver is a 4 proc 16 GB databaseserver. The development machine is just being used editing the rdl in Visual Studio.The databases are kept on the database server.The network speed is 100mbps.
Is the only alternative to build my ASPX and reportviewer component inside of that APSX.
I sure would like a quicker way to get to a script's code, then:
1. double-click 2. ctrl+tab to switch to script item in list 3. click on "Design Script" button.
I'm constantly editing scripts and I would LOVE to be able to right-click, "Design Script", bypassing the silly dialog. I've tried creating a macro with no success. While recording, when I click on "Design Script" from the dialog, it actually brings up the macro script in the editor. This task is frequent enough, that it deserves its own context menu entry.
Hi Everyone, Am a third year student doing work placement. Could anyone please give me clues on how to go about extracting data from a LDAP and then into an SQL database?
1 A defined subset of data is to be extracted from GDS on a nightly basis, 2 Then imported into a SQL database for quick & easy retrieval. 3 A web interface is required to present data retrieved from the SQL database.
SELECT TOP 4000 * FROM wce_contact where website not like '' and expressemail like '%@%' and idstatus like '' and salutation not like '%there%' and task not like '%x%'
I know that I can update a field like this...
update wce_contact set task = '' where task in ('x')
But how would I update a field that matches the above query? If you read my other post, this would eliminate the need for me to select a range of contacts.
I am currently workin on clearin a litte in our data collation, Can anyone tell me how i will get this to work.. I have this selected so far [URL=http://img247.imageshack.us/my.php?image=sqlresultatct2.png][/URL]
But i only want those selected who have the highest value in the last 3 columns in relation with their counterpart.... Anyway of doin that??
this is what i have to get the result-set
SELECT contact.contact_id, contact_p.p_name, contact_adr.adr_zip_code, contact_p.p_first_name, contact_p.p_last_name, form_land_i_os.cdp_1y81__, form_land_i_os.cdp_totalha, form_land_i_os.cdp_ha_krak FROM contact, contact dub_contact, contact_p, contact_p dub_contact_p, contact_adr, contact_adr dub_contact_adr, form_land_i_os, form_land_i_os dub_form_land_i_os, po_userdw WHERE (contact.deleted IS NULL AND dub_contact.deleted IS NULL AND dub_contact_adr.adr_zip_code IS NOT NULL AND contact.contact_status = 3 AND dub_contact.contact_status = 3 AND contact.p_id = contact_p.p_id AND dub_contact.p_id = dub_contact_p.p_id AND contact.adr_id = contact_adr.adr_id AND dub_contact.adr_id = dub_contact_adr.adr_id AND form_land_i_os.id = po_userdw.id AND dub_form_land_i_os.id = po_userdw.id AND po_userdw.relation_id = contact.contact_id AND contact_adr.adr_zip_code = dub_contact_adr.adr_zip_code AND contact_p.p_first_name = dub_contact_p.p_first_name AND contact_p.p_last_name = dub_contact_p.p_last_name AND contact.contact_id <> dub_contact.contact_id AND contact.adr_id <> dub_contact.adr_id AND contact_p.p_name <> 'X') ORDER BY contact_p.p_name
Thank you in advance wise gentlemen
Dont cross the road, if you cant get out of the kitchen
I'm new to SQL with 2 weeks under my belt....lol, so this may be a simple edit:
When I run the following query, I can get a list of all dups in the contact field: ++++++++++++++++++++++++++++++++++ SELECT full_name, COUNT(full_name) AS NumOccurrences FROM contact GROUP BY full_name HAVING ( COUNT(full_name) > 1 ) ++++++++++++++++++++++++++++++++++ However: I need to make sure I am de-activating (active = 0) only the contacts where they are listed more then once within the same company table (company.company_id) and the condition is that phone is NULL. I can't seem to make it work. Does anyone have any suggestions for an UPDATE I can use?
Guys Does anyone know of a 3rd party module/plug-in for VS or even some downloadable code that enables the import of Outlook/ACT and similar contact information through an ASP.NET page into a SQL Server table with ongoing 1-click button sync.
I need to replicate something similar to the Intellisync tool used in Yahoo Mail.
I'm in the process of trying to identify duplicate contacts. I doing this for millions of contacts and have gotten stuck and could use some elegant solutions!
The business rule is this:
Any contact that has the same name, phone and email address are the same contact Any contact that has the same name, and email address are the same contact Any contact that has the same name, email address, but different phone are a different contact. Any contact that has the same name, email address, and a blank phone can be the same contact as one that has the same name, email address, and has an email address Rank by the DataSource_fk. 1 being the highest
Put another way:
If 3 contacts have the same name, 2 have phone '1112223344' and all three have the email address 'johndoe@gmail.com' they are the same contact and the lowest DataSource_fk should be ranked the highest.
I've used the Row_number over (Partition by) in the past, but am unsure how to deal with the blanks in email and phone.
DROP TABLE [dbo].[TestBusinessContact]; GO CREATE TABLE [dbo].[TestBusinessContact] ( [TestBusinessContact_pk] INT IDENTITY(1,1)NOT NULL, [Business_fk]INT NOT NULL CONSTRAINT DF_TestBusinessContact_Business_fk DEFAULT(0),
Below is my sample data. I can't figure out how to select Unique phonenumber contacts for the same Ranked values from the set.
Basically the table is a mix of contactIDs. Some of them have duplicate phone numbers and through a separate mechanism we have ranked them.
It's easier then to pull out max(ranked) CLI_Numbers and their counterpart contactID(s). But I am also getting 2 or more records where the rank happens to be the same. I don't want that. Any one of the contactID will do for me.
The table has also same cliNumbers with different rank values, which are then correctly being picked up in the query below.
Note: ContactId is a unique value for each person in the table. RecordID is simply RowID.
( I have attempted to populate a sample data suited for this forum - not sure how it comes out on the browser)
if object_id('tempdb..#MyData') is not null drop table #MyData create table #MyData ( RecordID int, contactID int, forename varchar(25), surname varchar(25),
[Code] ....
This is my query attempt
With RankedmobileDuplicateSet as( select cliNumber, max(ranked_value) as ranked_max_value from #temp_UK_mobiledata group by cliNumber)
I get the error "The column prefix 'contacts' does not match with a table name or alias used in the query".I am trying to obtain all fields from the communications table whether it is used or NULL.
I've got a big problem that I'm trying to figure out: I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like: [AddressID] [int] [LocationID] [int]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table. So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
Before I begin describing our problem, let me first start off with a description of our environment and current configuration.
Background/Configuration Specs
We have SQL Server Reporting Services running on a Microsoft Windows Server 2003, Standard Edition, Service Pack 1 server. It is configured so that the Reports and ReportServer virtual directories run under a sql server domain account.
The datasets for our reports are xml datasets returned from calling a web service on a second IIS server, Microsoft Windows Server 2003, Standard Edition, Service Pack 1. The web service runs under a service account and is not set to impersonate the user. The web service contacts a database located on the same server as reporting services to retrieve the data. So, it should receive the request, login to the database it is trying to access using the service account, and return the dataset.
We are programatically accessing reports via a client application, specifying the virtual directory via the reportserver url (e.g. http:\hostname\reportserver\virtual directory path\report name).
Kerberos is enabled on both the sql box and the iis box with constrained delegation allow the sql account access to the iis box.
Problem
When I try to browse to the report from Report Manager in IE, (http:\hostname\Reports\...etc) I am able to successfully view the report.
When I try to view the report in the client app, (http:\hostname\reportserver\...etc.) the report fails.
Looking at the iis logs, it appears that in the first case, the web service is receiving the request from the sql server domain account. In the second case, it appears that the web service is receiving the request from an anonymous login.
Questions
What exactly is the difference between rendering/viewing a report via ReportManager versus programatically via ReportServer?
Is there any configuration we need to be aware of when configuring ReportServer?
Any help you can provide would be very much appreciated.
I have to extract, dayly a list of contacts on a exchange server in a table on our EDW on sql server 2005. Is it possible to get the information directly from a dataflow or i will have to developpe a script task ?
Can anyone take me through synchronization of contacts within Business Contacts Outlook into Microsoft Small Business Accounts?
I run a stand alone PC with NO network. When SBA came SQL was also installed. Apparently you can synchronise Contacts within Business Contacts with SBA but both SBA & Outlook should work through the same SQL server.
A colleague of mine has a view that returns approx 100000 rows in about 60 seconds.
He wants to use the data returned from that view in an OLE DB Source component.
When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins).
He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname>
In this instance all the data was returned in approx 60 seconds (as expected).
This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case.
I am trying to select all entries from a database apart from the top/latest entry, via a stored procedure. Below is the code that i have but i am doing it wrong somehow.
Any ideas?
Thanks
CREATE PROCEDURE spNEWSARCHIVE AS
SELECT * FROM tblNEWS WHERE NewsID NOT TOP 1 ORDER BY NewsID DESC GO
Hi,I have the following set and would like to select all rows that havemin value of column 4 for a given column 1 and 2 group, irrespective ofcolumn 3, as below:C1 C2 C3 C4---------------------A B x 5A B y 10A BB XX 55A BB YY 11AA CC z 1AA CC zz 11Need---A B x 5A BB YY 11AA CC z 1using sql server 2000 (which does not provide rank or partition by)Thanks in advance,Tamas
Hello,I have a gridview bound to a sqldatasource control. I have an emptydatatemplate setup. In the selecting event, I make sure that an actual select has been performed (and not on null data); however, I have cancelselectonnullparameters set to true to stop it, because initially it will be null.However, the selecting event runs, even when I cancel it, the emptydatatemplate shows up. How do I prevent that from occuring?
Dear All, I need to select records between two datetimes. My Database Structure and sample values are as below Fromdate ToDate 2007-02-20 09:00:00.000 2007-02-20 12:00:00.0002007-02-20 08:00:00.000 2007-02-20 12:00:00.0002007-02-20 06:00:00.000 2007-02-20 13:00:00.000 Query i used select * from tablename where ((fromdate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103)) or (todate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103))) My query is not returning values when i am querying between '2007-19-2 10:00' and '2007-19-2 11:00' Please help Thanks
Hi, This is quite an obvious problem, but for some reason I can't think through the solution. I have two columns, a datetime (datecreated) and an id (FK) othertableid (id)what I would like to be able to do is select a list of id and datecreated, but only chose the latest row for each id.So I guess it's a bit like a distinct on the id column, but ensuring the date returned is the top date. Please help
Hi i was wanting to know how to select a record in a gridview. I have a gridview with firsname and lastname. I currently have select command on it but don't want it. I want to be able to select first name or last name and have it take me to that record on the database.
hello all, i have a nested gridview. how do i add a SELECTING event for this datasource for my inner gridview? because i need to increase the commandtimeout. thnx. private SqlDataSource ChildDataSource(string strCustno) { string strQRY = "Select ..."; SqlDataSource dsTemp = new SqlDataSource(); dsTemp.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString ; dsTemp.SelectCommand = strQRY; return dsTemp; }
How can I select data between Mth row and Nth row from a specific search condition? for example , I want to get the 5th to 10th employee who is born in March and ordered by their name.
I have one table and it contains a column named ID Number, and a column named Date. I have a Do While statement that runs a SQL select statement a few times based on the number of records with the same ID Number. During the Do While statement the information is copied into another table and deleted from the old table. After I look at the results, I see that at the second Do While loop, the data was not selected and the Select statement did not run... so the old variable value from varValue is used again... Any reasons on why?
Here is a code snippet of what is going on: Do While varCount < varRecordCount conSqlConnect.Open() cmdSelect = New SqlCommand ("Select * From temp_records_1 where [id number]=@idnumber and date<@date", conSqlConnect) cmdSelect.Parameters.Add( "@accountnumber", "10000" ) cmdSelect.Parameters.Add( "@date", dtnow ) dtrdatareader = cmdSelect.ExecuteReader() While dtrdatareader.Read() If IsDbNull(dtrdatareader("value")) = false Then varValue = dtrdatareader("value") End If End While dtrdatareader.Close() conSqlConnect.Close()
'#####The information above is copied to another table here '#####The record where the information was received is deleted.
