Picking Up Data Meant For Many People
Nov 29, 2007
Hi,
I have a gridview on a page, which should select data from a database. I want it to select the data row if it finds its UserName in the UserName column, but there will be many usernames in the username column, seperated by commas.
Here is the select statement that I have at the moment, which doesnt return any data atall:
SELECT [message], [senddate], [subject], [messageid], [sendername], [recievername] FROM [Messages] WHERE ([recievername] LIKE '% ' + @recievername + ' %')
@recievername = Profile("UserName") which is the vwd wizards way of saying the current user.
A Username column has data like this:
bezzer , bezlan , beezer , beezler
with a space between commas and a space at the start and the end.
Thanks if someone can help!
Jon
View 2 Replies
ADVERTISEMENT
May 16, 2007
I have in my table something like this
Col1 Col2
6 O
6 O
6 C
6 C
6 C
5 O
5 O
i want the value as 6 iff all the corresponding
records in col2 are C
similarly, since for 5 there are no C it should
not pick record 5
Please suggest me a query for this
View 10 Replies
View Related
Mar 7, 2008
Hi there,
I believe this is simple requirement but to me it seems to be complicated. My data source is excel. I would like to configure the path of my data source in SSIS Package and file name is not a constant value. SSIS package should pick the file named as Today's (current) date from the configured path. As well, package should catch the exception for example file not found and log into custom log. It would be gr8 if some body helps me out.
View 7 Replies
View Related
Jul 20, 2005
I'm exporting a large file from a large Production database (SQL).Users are currently updating and inserting new records.Does the export take a snapshot of the data when it starts ? or willit freeze the database so all udpates/inserts happen after the exportcompletes?Also, how do I get the delta of data that wasn't exported, if I wantto get the difference the next day?Thank yoU!!!
View 4 Replies
View Related
Dec 20, 2006
Does anyone know what does the "@" meant?? Does it meant suppressing the error output?
Code:
sqlConnection sqlConnection = new SqlConnection(@"Integrated Security=SSPI; Data Source=(local)SQLEXPRESS");
View 2 Replies
View Related
May 28, 2007
Dear Experts, what exactly the defragmentation in sql server? what is the use
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 4 Replies
View Related
Mar 21, 2015
I'm trying to quantify the number of times folks use SQL Server Management Studio to change client data in one of our production databases. Does SQL Server keep this statistic? How do I get to this data?
View 6 Replies
View Related
Dec 13, 2013
When I am trying to insert to data from SQL ssis package to SharePoint list people or group column I am getting below error.[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SharePoint List Destination" (25) failed with error code 0x80131500 while processing input "Component Input" (34). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
View 8 Replies
View Related
Mar 19, 2008
Hi, I have a student results table with the layout shown below (four records with the fields separated by dashes). Sorry its so messy. Anyway, you can see that there are duplicates. I want to write an SQL statement that will pick out only the 'supplemental' records if duplicates occur. Any ideas on how to do this?
ID - StudentNo - Subject - Term - Yearofstudy - YearTaken - Grade
1195- 11111111- MA1E2- Annual - 1- 2006- 34
1205- 11111111- MA1E2- Supplemental- 1- 2006- 40 (S)
1194- 11111111- MA1E1- Annual -1- 2006- 35
1204- 11111111- MA1E1- Supplemental- 1- 2006- 40 (S)
Here is the SQL I'm using to get all the records from the studentresults table, for first years only:
SELECT *
FROM studentresults
WHERE studentresults.StudentNo = 11111111 AND studentresults.YearOfStudy = 1
How do I change this to pick out only the supplemental exam results?
Regards,
sabatier
View 1 Replies
View Related
Feb 5, 2013
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:
animals = {'rat', 'dog', 'cat', 'fish', 'fox'}
food = {'IAMS', 'Sci Diet', 'Purina', 'Alpo'}
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.
View 2 Replies
View Related
May 14, 2008
Hi,
Slightly knotty and hard-to-articulate problem here, so please bear with me.
As part of a series of transactions, I have a table which contains data which looks like this:
AdBookingID adWeeks AdID clientId valueid
----------- ----------- ----------- ----------- -----------
97336 3 95127 248 1007
98220 1 94304 458 1007
98220 1 94304 458 7610
98386 1 88123 319 7604
98388 1 91484 319 7610
98390 1 91963 319 7610
98392 1 92468 319 7601
98392 1 92468 319 7608
The key to this problem is the "valueID". The first digit of the value is significant - the "1" and the "7" in the above mean the values are parts of different groups of search criteria.
What I need to do is to select from this list into a temp table all those bookings (identified by AdBookingID) which have valueIds of both types - i.e. an row in the table where the valueId starts with a 1 and a row in the table where the valueId starts with a 7 - in the above data sample the only AdBooking which qualifies is 98220.
I worked out a method of doing this going by the number of entries each item had in the table:
select count(distinct valueId)
as valueId, adWeeks, clientId, adID, AdBookingID
into #worktable
from #tmp
group by adWeeks, clientId, adID, AdBookingID
having count(distinct valueid) > 1
Which is largely accurate. But - and here's the killer - although each adBooking can only have one valueId starting with 1, it can have multiple entries starting with 7. These bookings are rare, but they do exist and are causing anomalies in the data returned which - if you recall - must only contain booking records for which there are valueId entries with both a 1 and a 7.
Can anyone suggest a way I can get just the data I need?
Cheers,
Matt
View 7 Replies
View Related
Oct 11, 2007
Can anyone suggest an alternative to the following problem?
We have had 2 users change their NTLogin in AD. For example, from jsmith to jbloggs. NB this is a change, NOT a completely new login. When the users connect to sql server and call the function SYSTEM_USER, the login returned is the old login:
TheDomainjsmith
This is causing an audit trail problem. Exactly the same issue exists with sys.sysprocesses.
The only way I have found of correcting the problem is by restarting the SQL server service which is obviously not ideal for such a trivial issue.
You can recreate this problem via local users on a sql box as well:
1. Create a local user on the SQL box called FirstUserName and grant access to SQL
2. do a runas on management studio and runas the FirstUserName user
3. connect to SQL and run SELECT SYSTEM_USER, you will get [nameofsqlbox]FirstUserName which is correct
4. kill management studio
5. rename the user SecondUserName
6. do a runas on management studio and runas the SecondUserName user (note, same user, now has different login name)
7. connect to SQL and run SELECT SYSTEM_USER, you will get [nameofsqlbox]FirstUserName which is now incorrect
8. restart sql server service
9. connect to SQL and run SELECT SYSTEM_USER, you will get [nameofsqlbox]SecondUserName which is now correct
Any solutions other than just sucking up a restart everytime a user changes login name would be much appreciated!
View 15 Replies
View Related
Feb 8, 2007
Hi
I'm using SQL Server 2005. The problem I have is as follows. I have several production lines and as with everything parts in the line tend to break. I have data from all the breaks that occurred in the last 2 years. What I want to do is predict the next break and the production line it's going to happen on. I would also like to go to a future date and check what possible breaks might occur on that date. I've run quite a few models but none of them helps me with future events. I think I might be using the wrong algorithm or I€™m just not doing it right. If somebody can please suggest an algorithm and maybe help me with a web site that has a tutorial similar to my problem
Thanks
Elmo
View 7 Replies
View Related
Jun 20, 2001
Hello,
I have this SP that works, except I need to add another field value for the WHERE clause. As you can see I have "WM" but I need to add "PR", and those two are definitely in the table field. I've tried a variety of syntax arrangements using the AND operator, the OR operator, the & operator, just a comma between the two, nothing between the two. Can someone please show me what I'm doing wrong. It fileters for "WM" fine, but I also need it to filter in the WHERE clause for "PR". Here is the SP:
CREATE procedure spDemoSchedule (@beginDate varchar(10), @endDate varchar(10), @storeNum int)
AS
SELECT Progstats.[Program#], Progstats.KCKOFF, Progstats.ProgramName, Progstats.Parent, Store.[Str#], Store.Status, Progstats.Dept, Store.[Program#], Product.[Item#], Product.[Item]
FROM Progstats INNER JOIN Product ON Progstats.[Program#] = Product.[Program#] INNER JOIN Store ON Progstats.[Program#] = Store.[Program#]
WHERE Progstats.KCKOFF BETWEEN @beginDate AND @endDate AND Store.[Str#]=@storeNum AND Progstats.CLASS="WM"
GO
TIA,
Bruce Wexler
View 2 Replies
View Related
May 18, 2015
Got a table named Payments in the following form:
Report_DateCustomer_IDCustomer_NamePayment_DatePayment_Amount
The Customers Table have the fields:
Customer_IDCustomer_Name
Since the Customers Table contains the complete list of customers I seek to have a Join made between it and the Paymentstable to check the amount made by the customers.
However, the trouble arises when I seek to check the average amounts made by each customer during last two months in which such a customer had paid.
In further clarification, lets consider current month of May 2015 going on;
Customer_A had paid $10,000 in AprilCustomer_A had paid $5,000 in AprilCustomer_B had paid $10,000 in AprilCustomer_A had paid $18,000 in MarchCustomer_A had paid $2,000 in MarchCustomer_A had paid $10,000 in FebruaryCustomer_B had paid $8,000 in FebruaryCustomer_B had paid $4,000 in FebruaryCustomer_B had paid $3,000 in February...
View 4 Replies
View Related
Jul 25, 2006
Hi all,
Hi am creating a new database not sure which way is the best way to go. Here are the questions
I have a history table which has its an identity field and an identification field which shows what type of history field it is. As in payment, printout, change and so on.... For this field should i use
Full text which has the payment, printout, change and so on and make it easy on the eye and for queries and coded to use the same text all the time
have varchar(2) type of field which holds an understandable field PR, PO, CH and a reference table for it as in PR= PAYMENT, PO = Printout, CH as in change and so on
or have a integer field as in 1, 2, 3 and ref. table 1=payment, 2 = printout, 3=change and so on
I have a address table which holds multiple types of addresses which are really limited count maybe upto 4 different types
have a integer field which links to a reference table as above
have bit fields which can be used to check if it gets in one of the group types
have a full text option like above
Im open for any type of suggestions and i would love to hear the reasons why its picked TY
View 1 Replies
View Related
Nov 7, 2006
Hi,
I've created a solution with 5 packages in. I've scripted a config file for each package where I would be able to change the source file connection and the database servers (source and destination). On the first package, I am able to change the config attributes and the affects are picked up, but the rest of the packages do no pick up the changes.
For example, if the source file is "C:Filessource.txt" and this is changed to "C:Filessource123.txt" (Just to make sure it fails) it wont pick up the new changes and still uses "C:Filessource.txt".
Also, I tried changing the name of config file itself and the package still ran as if nothing had changed. It's as if it's not recognising the config file.
Any ideas ?
Thanks, Richie.
View 9 Replies
View Related
Feb 27, 2007
I cannot seem to get the report to pick up the custom style sheet I placed into Reporting servicesReportServerStyles subdirectory. Nor will it pick up any changes to the HTMLViewer.css either. The report obviously isn't looking there perhaps? I've used the http: etc. ReportServer/Pages/ReportViewer.aspx?%reportname%&rs:Format=HTML4.0&rs:Command=Render&rc:StyleSheet=MyStyleSheetname
I've even modified the HTMLViewer.css file as well. Nothing. Nada. Is there somewhere else this is cached? Or some way I can find where the report looks for this?
View 4 Replies
View Related
Jul 2, 2015
My package is having .csv file as a source and I kept OLEDB destination to load it.
Stored the .csv file in a shared folder and the exact path is given in Enumerator configuration of the foreach loop container. When I execute my package, it is giving the warning as below:
It is saying that file is not there in the specified path and directory is empty. I am running the SSIS package from TFS. I am sure that I have read and write access for the shared folder for my userID. Is there any access there to pick up this file from path.
View 4 Replies
View Related
Aug 31, 2006
Why i got "SqlException was unhandled by user code" "{"Incorrect syntax near '12'."}" ?
How can i only get people which is brithday is today?
The "rc_brithday" datatype is datetime
Thanks you
Code is below:
cmdSelect = New SqlCommand("Select rc_email From recruiters WHERE rc_brithday = " & DateTime.Now(), conPubs)
conPubs.Open()
dtrTitles = cmdSelect.ExecuteReader()
View 5 Replies
View Related
Jun 6, 2001
Currently, I have set up a SQL 7.0 database backup job and have one person notified if the job succeeds, but HOW do I choose an option to notify a different person if the job fails ?
Thank you in advance
View 5 Replies
View Related
Mar 5, 2015
I'm trying to calculate the % of people but it doesn't seem to be working. I get the following error:
ORA-00904: "ACT"."PAT_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"
What am I doing wrong?
WITH ENCOUNTERS AS
(
SELECT COUNT(*) AS TOT_COUNT
FROM
(
SELECT DISTINCT PATIENT.PAT_ID
[code]....
View 2 Replies
View Related
Jul 20, 2005
hello,My friend and I are working on a project involving oracle 9i and an oldUnify database. We got the oracle to see the unify (oracle running onwindows) by using the heterogenous services on oracle.However, that was a test environment. The production environment hasoracle running on linux. The problem is that the odbc drivers for theold unify database are only available on windows!So, I was wondering about something.In the production environment, they also have a MS-SQL 2000 database.Does that database have a function similar to oracle's heterogenousservices that will let us connect to the old unify database and passthat data to oracle?We're building custom apps. on oracle 10g application server. Runningoracle on windows in the production environment is not going to happen.Thanks,Dave
View 2 Replies
View Related
Oct 3, 2007
I have a report that need to be delivered to two different persons.
It has 10 columns. But one of them is not supposed to be seen by one person. Can I do this without creating another report and delivering two different reports although they are almost the same?
Thanks
View 5 Replies
View Related
Apr 3, 2015
Basically the question is, which number should I pick?
View 4 Replies
View Related
Mar 15, 2004
Hi Friends,
If I upload my Sql Server Database to a hosting company, can they see my tables?
Regards,
View 1 Replies
View Related
Dec 20, 2006
Is there a script or table that I can check to find out if there are any users who have BLANK passwords?
thanks
View 3 Replies
View Related
Feb 12, 2007
Is there a way to kick users out of your databases if there's no activity for a certain duration of time. For example, I have a database thats being shared by 5 people. One went to lunch and locked their office just when I needed to do maintanence. I had to wait for them to get back & logout be fore I could do anything?
View 1 Replies
View Related
Jun 20, 2007
Curious, what does one get out of going to a conference like PASS? (besides getting a free trip from work!).
I can see how it might be a place for consultants to mingle and potentially land new contracts. What other opportunities are there?
View 4 Replies
View Related
Jul 23, 2005
This is a question concerning query optimisation. Sorry if it's a bit long,but thanks to anyone who has the patience to help - This is my first posthere...If I have two tables: 'tblContact' and 'tblCategory' where categories arelike:Code Name010101 Short010102 Fat010103 StupidThe junction table 'tblConCat' has fields CctConID, CctCatCode to tell mewhich contacts have which category codes. These are nchar(6) fields, if itmakes any difference.If I need to find all people who are short, fat and stupid I can see twoways:Solution One:SELECT tblContact.* FROM tblContact WHEREConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010101') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010102') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010103')Solution Two:Build a helper table which contains the codes I'm looking forSELECT tblContact.* FROM tblContactWHERE ConID IN(SELECT CctConIDFROM tblConCat INNER JOIN tblHelperON tblConCat.CctCatCode = tblHelper.HlpCatCodeGROUP BY CctConID HAVING Count(*)=3)I have tried them both out and although I looked at the query analyzer itprovided more information than I knew what to do with. In practise theyboth provide similar working times (I wait about a second) but I thought thefirst looked rather inefficient and thought my helper table might help.There are about 30,000 contact records, 180 category records and 120,000junction table records.All I am looking for comments on any pros and cons of these two approaches -does one look that bad? The database was migrated from Access where thehelper table really did help, but using SQL Server I might not need it.Thanks again, if you got this far!
View 16 Replies
View Related
Dec 22, 2006
I run a report which displays transcripts for members. One page per person with their name, courses they've took, date of the course and their score. The problem I am having is when the report runs and comes across the same last name for many people (say, Hanna and Billy Alexander), it's only giving me the first "Alexander" it comes across and it is putting all the information for the other "Alexander's" in that first transcript. How do I separate out the people who's last name is the same say "Smith" or Jones"?
I have a distinct clause on my query and when I run it I can see the other people with the same last name and their unique courses and scores (each of them have a unique member ID) - it's when I actually run the report that it groups the information (courses, dates, scores, etc) for the same last name people all under the first one it comes across. Hope that made sense.
Thx,
Billy
View 4 Replies
View Related
Jan 25, 2008
i have a report that is specifc to a user (has a user parameter)
is there a way to send out a dynamic subscription where the report is sent to the user "BOB" using "BOB" as a parameter, and a report that is sent to the user "Sue", uses the parameter "sue"
is this possible - i hope im making some sort of sense
thank you in advance
Jonny
View 5 Replies
View Related
Feb 14, 2007
Thank you for taking the time to read this, I need all the advise and help I can get on this ... so please post anything you think would work ... A little confused I am:
Have a database table called "people" with "person name" and "ID" field. My ASP.NET application mainly stores articles in article table. An article's Article text mentions various people's names in different combinations (e.g. John, Smith, John Smith, Smith John, etc)
Is there any way, I could compare the article text stored in article table with people table and get the people from people table along with their ID's who have been mentioned in that article? ... so in an article "i love john smith ... and i think Mr smith has always been helpful", I get John Smith back...
Not too sure being honest, what is the best way of implementing this, looking for the most efficient way, probably using XML? SQL Query or may be ASP.NET's code behind?
Thanks once again for taking the time.
Cheers,
Tyro
View 4 Replies
View Related