Search Results

Search found 27339 results on 1094 pages for 'sql tempdb'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 336/1094 | < Previous Page | 332 333 334 335 336 337 338 339 340 341 342 343  | Next Page >

  • SQL Grouping with multiple joins combining results incorrectly

    - by Matt
    Hi I'm having trouble with my query combining records when it shouldn't. I have two tables Authors and Publications, they are related by Publication ID in a many to many relationship. As each author can have many publications and each publication has many Authors. I want my query to return every publication for a set of authors and include the ID of each of the other authors that have contributed to the publication grouped into one field. (I am working with mySQL) I have tried to picture it graphically below Table: authors Table:publications AuthorID | PublicationID PublicationID | PublicationName 1 | 123 123 | A 1 | 456 456 | B 2 | 123 789 | C 2 | 789 3 | 123 3 | 456 I want my result set to be the following AuthorID | PublicationID | PublicationName | AllAuthors 1 | 123 | A | 1,2,3 1 | 456 | B | 1,3 2 | 123 | A | 1,2,3 2 | 789 | C | 2 3 | 123 | A | 1,2,3 3 | 456 | B | 1,3 This is my query Select Author1.AuthorID, Publications.PublicationID, Publications.PubName, GROUP_CONCAT(TRIM(Author2.AuthorID)ORDER BY Author2.AuthorID ASC)AS 'AuthorsAll' FROM Authors AS Author1 LEFT JOIN Authors AS Author2 ON Author1.PublicationID = Author2.PublicationID INNER JOIN Publications ON Author1.PublicationID = Publications.PublicationID WHERE Author1.AuthorID ="1" OR Author1.AuthorID ="2" OR Author1.AuthorID ="3" GROUP BY Author2.PublicationID But it returns the following instead AuthorID | PublicationID | PublicationName | AllAuthors 1 | 123 | A | 1,1,1,2,2,2,3,3,3 1 | 456 | B | 1,1,3,3 2 | 789 | C | 2 It does deliver the desired output when there is only one AuhorID in the where statement. I have not been able to figure it out, does anyone know where i'm going wrong?

    Read the article

  • Can't get DataGridView to refresh over Linq to SQL (WinForm)

    - by GringoFrenzy
    Very strange situation here: I'm using L2S to populate a DataGridView. Code follows: private void RefreshUserGrid() { var UserQuery = from userRecord in this.DataContext.tblUsers orderby userRecord.DisplayName select userRecord; UsersGridView.DataSource = UserQuery; //I have also tried //this.UserBindingSource.DataSource = UserQuery; //UsersGridView.Datasource = UserBindingSource; UsersGridView.Columns[0].Visible = false; } Whenever I use L2S to Add/Delete records from the database, the GridView refreshes perfectly well. However, if someone is editing the grid and makes a mistake, I want them to be able to hit a refresh button and have their mistakes erased by reloading from the datasource. For the life of me, I can't get it to work. The code I am currently using on my refresh button is this: private void button1_Click(object sender, EventArgs e) { this.DataContext.Refresh(RefreshMode.OverwriteCurrentValues); RefreshUserGrid(); } But the damn GridView remains unaffected. All that happens is the selected row becomes unselected. I have tried .Refresh(), .Invalidate(), I've tried changing the DataSource to NULL and back again (all suggestions from similar posts here)....none of it works. The only time the Grid refreshes is if I restart the app. I must be missing something fundamental, but I'm totally stumped and so are my colleagues. Any ideas? Thanks!

    Read the article

  • Sql: simultaneous aggregate from two tables

    - by Ash
    I have two tables: a Files table, which includes the file type, and a File Properties table, which references the file table via a foreign key. Sample Files table: | id | name | type | --------------------- | 1 | file1 | zip | | 2 | file2 | zip | | 3 | file3 | zip | | 4 | file4 | jpg | And the Properties table: | file_id | property | ----------------------- | 1 | x | | 2 | x | I want to make a query, which shows the count of each file type, and how many files of that type have a property. So in the example, the result would be | type | filecount | prop count | ---------------------------------- | zip | 3 | 2 | | jpg | 1 | 0 | I could accomplish this by select f.type, (select count(id) from files where type = f.type), count(fp.id) from files as f, file_properties as fp where f.id = fp.file_id group by f.type; But this seems very suboptimal and is very slow. Any better way to do this?

    Read the article

  • Calculate the SUM of the Column which has Time DataType:

    - by thevan
    I want to calculate the Sum of the Field which has Time DataType. My Table is Below: TableA: TotalTime ------------- 12:18:00 12:18:00 Here I want to sum the two time fields. I tried the below Query SELECT CAST( DATEADD(MS, SUM(DATEDIFF(MS, '00:00:00.000', CONVERT(TIME, TotalTime))), '00:00:00.000' ) AS TOTALTIME) FROM [TableA] But it gives the Output as TOTALTIME ----------------- 00:36:00.0000000 But My Desired Output would be like below: TOTALTIME ----------------- 24:36:00 How to get this Output?

    Read the article

  • getting sql records

    - by droidus
    when i run this code, it returns the topic fine... $query = mysql_query("SELECT topic FROM question WHERE id = '$id'"); if(mysql_num_rows($query) > 0) { $row = mysql_fetch_array($query) or die(mysql_error()); $topic = $row['topic']; } but when I change it to this, it doesn't run at all. why is this happening? $query = mysql_query("SELECT topic, lock FROM question WHERE id = '$id'"); if(mysql_num_rows($query) > 0) { $row = mysql_fetch_array($query) or die(mysql_error()); $topic = $row['topic']; $lockedThread = $row['lock']; echo "here: " . $lockedThread; }

    Read the article

  • Big table or multiple separate tables? (database design question)

    - by Khou
    This is a database design question. I want to build an invoice web application, an invoice can have many items, and each user can have an inventory list of product items that they can store and choose to add to an invoice item. My questions are: 1. Should I store all product inventory for all users using my application under one single table? Or have a separate product inventory table created for each user? 2. Is this even possible? 1 table is easier, but what if this single table grows too big, will I have a problem? (primary key INT).

    Read the article

  • Count for consecutive records

    - by Nai
    I have a table as follows > RowID SessionID EventID IPAddress RequestedURL Date > 1 m2jqyc45gtjmvb55dc4dg 1 82.23.149.238 Start 24/03/2010 19:52 > 2 m2jqyc45gtjmvb55dc4dg 1 82.23.149.238 BuyNow 24/03/2010 19:52 > 3 m2jqyc45gtjmvb55dc4dg 28 82.23.149.238 Clicked OK 24/03/2010 19:52 > 4 m2jqyc45gtjmvb55dc4dg 1 82.23.149.238 ProductPage 24/03/2010 19:52 > 5 m2jqyc45gtjmvb55dc4dg 1 82.23.149.238 Home 24/03/2010 19:56 > 6 m2jqyc45gtjmvb55dc4dg 1 82.23.149.238 ProductPage 24/03/2010 19:56 > 7 m2jqyc45gtjmvb55dc4dg 1 82.23.149.238 BuyNow 24/03/2010 19:56 > 8 m2jqyc45gtjmvb55dc4dg 28 82.23.149.238 Clicked OK 24/03/2010 19:56 > 9 m2jqyc45gtjmvb55dc4dg 1 82.23.149.238 Home 24/03/2010 19:56 How do I write a query that does a count whenever the rows BuyNow and Clicked OK have been recorded consecutively. For example, the dataset above, the return count should be 2.

    Read the article

  • Can we connect shaerpoint to sql server6.5?

    - by nalini
    Not able to import application definition file!! Error: The metadata object with Name 'XYZ' and of Type 'LobSystemInstance' has a Property with Name 'DatabaseAccessProvider' that has either an invalid value or Type. Error was encountered at or just before Line: '10' and Position: '10'. line 10 in ADF: <"Property Name="DatabaseAccessProvider" Type="System.String"SqlOledb<"/Property Please give me ideas on how to display data from sql6.5 to sharepoint?

    Read the article

  • How to write this query in Linq2Sql

    - by RememberME
    I have a table company which holds the company_id, company_name and other details. I have a table subcontracts which has a company_id column that maps to company.company_id. How can I write a select statement to get all companies which have not been assigned to a subcontract? IE The company_id cannot be found in subcontracts.company_id

    Read the article

  • Updating table takes very long time

    - by rrejc
    Hi all, I have a table in MsSQL Server 2008 (SP2) containing 30 millios of rows, table size 150GB, there are a couple of int columns and two nvarchar(max) columns: one containing text (from 1-30000 characters) and one containg xml (up to 100000 characters). Table doesn't have any primary keys or indexes (its is a staging table). So atm I am running a query: UPDATE [dbo].[stage_table] SET [column2] = SUBSTRING([column1], 1, CHARINDEX('.', [column1])-1); the query is running for 3 hours (and it is still not completed), which I think is too long. Is It? I can see that there is constant read rate of 5MB/s and write rate of 10MB/s to .mdf file. How can I find out why the query is running so long? The "server" is i7, 24GB of ram, SATA disks on RAID 10. Many thanks!

    Read the article

  • using PIVOT to sql server.

    - by NoviceToDotNet
    This is the abstract idea which i want to do by my first select of first line, i am presenting here, but i am unable to do that correct. here category[0], category[2]..etc representing the category columns values...i know this kind of syntax not work, but i want to do something like this. SELECT category[0], category[1], category[2], category[3], category[4], category[5] FROM( select Row_number() OVER(ORDER BY (SELECT 1)) AS 'Serial Number', EP.FirstName,Ep.LastName, Ep.SignUpID, [dbo].[GetBookingRoleName](ES.UserId,EP.BookingRole) as RoleName, (select top 1 convert(varchar(10),eventDate,103)from [3rdi_EventDates] where EventId=@ItemId) as EventDate, (CASE [dbo].[GetBookingRoleName](ES.UserId,EP.BookingRole) WHEN 'Employee - Marketing' THEN 'DC' WHEN 'Employee - Accounting' THEN 'DC' WHEN 'Coaches' THEN 'DC' WHEN 'Student Client' THEN 'ST' WHEN 'Guest Doctor' THEN 'GDC' ---....more categories here, i just removed a few END) as Category from [3rdi_EventParticipants] as EP inner join [3rdi_EventSignup] as ES on EP.SignUpId = ES.SignUpId WHERE EP.EventId = @ItemId AND EP.PlaceStatus IN (0,3,4,8) and userid in( select distinct userid from userroles where roleid not in(19,20,21,22) and roleid not in(1,2, 25, 44)) (My Below Query) PIVOT(sum(First_Name+Last_Name)) FOR Category (category[0], category[1], category[2], category[3], category[4], category[5]) Group by (SignUpID)

    Read the article

  • script to dynamically fix ophaned users after db restore

    - by JJgates
    After performing a database restore, I want to run a dynamic script to fix ophaned users. My script below loops through all users that are displayed after executing sp_change_users_login 'report' and apply "alter user [username] with login = [username]" to fix SID conflicts verses static go statements. Although, I'm getting an "incorrect syntax error on line 15." can't figure out why... DECLARE @Username varchar(100), @cmd varchar(100) DECLARE userLogin_cursor CURSOR FAST_FORWARD FOR SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid IS NOT NULL AND sid <> 0×0) AND suser_sname(sid) IS NULL ORDER BY name FOR READ ONLY OPEN userLogin_cursor FETCH NEXT FROM userLogin_cursor INTO @Username WHILE @@fetch_status = 0 BEGIN SET @cmd = ‘ALTER USER ‘+@username+‘ WITH LOGIN ‘+@username EXECUTE(@cmd) FETCH NEXT FROM userLogin_cursor INTO @Username END CLOSE userLogin_cursor DEALLOCATE userLogin_cursor

    Read the article

  • build SQL query string using user input

    - by user175084
    i have to make a string by using the values which the user selects on the webpage suppose i need to display files for multiple machines with differnt search criteria.. i currently use this code: DataTable dt = new DataTable(); SqlConnection connection = new SqlConnection(); connection.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString; connection.Open(); SqlCommand sqlCmd = new SqlCommand("SELECT FileID FROM Files WHERE MachineID=@machineID and date= @date", connection); SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd); sqlCmd.Parameters.AddWithValue("@machineID", machineID); sqlCmd.Parameters.AddWithValue("@date", date); sqlDa.Fill(dt); now this is fixed query where the user just has one machine and just selects one date... i want to make a query in which the user has multiple search options like type or size if he wants depending on what he selects also if he can select multiple machines.. SELECT FileID FROM Files WHERE (MachineID=@machineID1 or MachineID = @machineID2...) and (date= @date and size=@size and type=@type... ) all of this happens in runtime... other wise i have to create a for loop to put multiple machines one by one... and have multiple queries depending on the case the user selected... this is quiet interesting and i could use some help... thanks

    Read the article

  • Return dataset in dataflow

    - by praveen
    Hi All, Could I get ideas on retrieving the dataset using lookup method. Basically, my scenario as I have source data needs to lookup for other source table and on matching column from source I need to get all the records from other source data. its a one to many relations. I tried Lookup but gives only one record on matching condition, OLE DB command don't retrieve any data as it will do only Insert/Update operations. Thanks prav

    Read the article

  • Help with SQL query (Calculate a ratio between two entitiess)

    - by Mestika
    Hi, I’m going to calculate a ratio between two entities but are having some trouble with the query. The principal is the same to, say a forum, where you say: A user gets points for every new thread. Then, calculate the ratio of points for the number of threads. Example: User A has 300 points. User A has started 6 thread. The point ratio is: 50:6 My schemas look as following: student(studentid, name, class, major) course(courseid, coursename, department) courseoffering(courseid, semester, year, instructor) faculty(name, office, salary) gradereport(studentid, courseid, semester, year, grade) The relations is a following: Faculity(name) = courseoffering(instructor) Student(studentid) = gradereport (studentid) Courseoffering(courseid) = course(courseid) Gradereport(courseid) = courseoffering(courseid) I have this query to select the faculty names there is teaching one or more students: SELECT COUNT(faculty.name) FROM faculty, courseoffering, gradereport, student WHERE faculty.name = courseoffering.instructor AND courseoffering.courseid = gradereport.courseid AND gradereport.studentid = student.studentid My problem is to find the ratio between the faculty members salary in regarding to the number of students they are teaching. Say, a teacher get 10.000 in salary and teaches 5 students, then his ratio should be 1:5. I hope that someone has an answer to my problem and understand what I'm having trouble with. Thanks Mestika

    Read the article

  • SQL query showing missing expression

    - by Ashok Dasari
    Query to pull the data between Yseterday 6AM to today 6AM ... SELECT lot_id, log_time, batch_no, eqp_id, STATION_ID, EXTRACTVALUE (META_DATA, '/lot_info/A3') AS A3, EXTRACTVALUE (META_DATA, '/lot_info/A3Info') AS A3Info, EXTRACTVALUE ( META_DATA, '/lot_info/apc_status_info' ) AS apc_status_info FROM t_dlis_log_history WHERE ( (EQP_ID = 'ALC4360') OR (EQP_ID = 'ALC4361') OR (EQP_ID = 'ALC1360') OR (EQP_ID = 'ALC1361') OR (EQP_ID = 'ALC1362') OR (EQP_ID = 'ALC1363') OR (EQP_ID = 'ALC1364') OR (EQP_ID = 'ALC1365') OR (EQP_ID = 'ALC355') OR (EQP_ID = 'ALC353') OR (EQP_ID = 'ALC4350') OR (EQP_ID = 'ALC354') ) AND (( log_time >= DATEADD ( HOUR, 6, CONVERT(VARCHAR (10), GETDATE (), 110) ) AND ( log_time <= DATEADD ( HOUR, 6, CONVERT(VARCHAR (10), GETDATE () + 1, 110) ) ) ) It is showing error missing expression ...

    Read the article

  • Select return dynamic columns

    - by Ascalonian
    I have two tables: Standards and Service Offerings. A Standard can have multiple Service Offerings. Each Standard can have a different number of Service Offerings associated to it. What I need to be able to do is write a view that will return some common data and then list the service offerings on one line. For example: Standard Id | Description | SO #1 | SO #2 | SO #3 | ... | SO #21 | SO Count 1 | One | A | B | C | ... | G | 21 2 | Two | A | | | ... | | 1 3 | Three | B | D | E | ... | | 3 I have no idea how to write this. The number of SO columns is set to a specific number (21 in this case), so we cannot exceed past that. Any ideas on how to approach this? A place I started is below. It just returned multiple rows for each Service Offering, when they need to be on one row. SELECT * FROM SERVICE_OFFERINGS WHERE STANDARD_KEY IN (SELECT STANDARD_KEY FROM STANDARDS)

    Read the article

  • Database cache that I'm not aware of?

    - by Martin
    I'm using asp.net mvc, linq2sql, iis7 and sqlserver express 2008. I get these intermittent server errors, primary key conflicts on insertion. I'm using a different setup on my development computer so I can't debug. After a while they go away. Restarting iis helps. I'm getting the feeling there is cache somewhere that I'm not aware of. Can somebody help me sort out these errors? Cannot insert duplicate key row in object 'dbo.EnquiryType' with unique index 'IX_EnquiryType'. Edits regarding Venemos answer Is it possible that another application is also accessing the same database simultaneously? Yes there is, but not this particular table and no inserts or updates. There is one other table with which I experience the same problem but it has to do with a different part of the model. How often an in what context do you create a new DataContext instance? Only once, using the singleton pattern. Are the primary keys generated by the database or by the application? Database. Which version of ASP.NET MVC and which version of .NET are you using? RC2 and 3.5.

    Read the article

  • MS SQL Cursor data as result of stored procedure

    - by Dmitry Borovsky
    Hello, I have some stored procedure DECLARE cursor FOR SELECT [FooData] From [FooTable]; OPEN cursor ; FETCH NEXT FROM cursor INTO @CurrFooData; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @CurrFooData AS FooData; INSERT INTO Bar (BarData) VALUES(@CurrFooData); FETCH NEXT FROM cursor INTO @CurrFooData; END; CLOSE OldestFeeds DEALLOCATE OldestFeeds But in result I have a lot of tables, not one. How can I return one table with 'FooData' column and all '@CurrFooData' rows?

    Read the article

  • error handling in asp.net

    - by user98454
    Hi How can i pass the different types of errors from Data access layer to presentation layer? suppose if we take the northwind database scenario I want to delete the customer, so i selected one customer in ui and clicked the "delete" button.It internally calls the "delete" in data access layer. The prerequisite for deleting the customer is that the customer doesn't have any orders.So in data access layer we wil check whether that customer has any orders.If the customer has orders how can we pass the message from dal to presentation layer that the customer has orders and we don't delete. Am i doing right?is there any other ways to deal with this type? Thanks in advance

    Read the article

  • Improve SQL Server 2005 Query Performance

    - by user366810
    I have a course search engine and when I try to do a search, it takes too long to show search results. You can try to do a search here http://76.12.87.164/cpd/testperformance.cfm At that page you can also see the database tables and indexes, if any. I'm not using Stored Procedures - the queries are inline using Coldfusion. I think I need to create some indexes but I'm not sure what kind (clustered, non-clustered) and on what columns. Thanks

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 332 333 334 335 336 337 338 339 340 341 342 343  | Next Page >