Search Results

Search found 27530 results on 1102 pages for 'sql truncate'.

Page 330/1102 | < Previous Page | 326 327 328 329 330 331 332 333 334 335 336 337  | Next Page >

• Table rows with identifying parameter in each row SQL SERVER 2008 into single row

  - by LiverpoolsNumber9

  Sorry - my question title is probably as inept at my attempt to do this. I have the following (well, similar) in a table in a CMS pageID key value 201 title Page 201's title 201 description This is 201 201 author Dave 301 title Page 301's title 301 description This is 301 301 author Bob As you've probably guessed, what I need is a query that will produce: pageID title description author 201 Page 201's title This is page 201 Dave 301 Page 301's title This is page 301 Bob If anybody could help, i'd be eternally grateful - I know this is "please send me the code" but I'm absolutely stuck. Thanks in advance.

  Read the article

• How to apply Containstable 4 two join table?

  - by jaykanth

  product table pid modelnumber 1 a 2 b 3 c ProductTransation pid name description... 1 ball ball 2 bat cricket bat i create fullText for Modelnumber in product table. " for name & Description in productTrasaction table. Now i want to join this table if i search through modelnumber or name result should be pid name modelnumber 1 ball a

  Read the article

• Using Full-Text Search in SQL Server 2005 across multiple tables, columns

  - by crisgomez

  Hi, I have a problem, I created a full text search query which return a record(s), in which the paramater I have supplied match(es) in every fields(full-text indexed) of multiple tables. The problem is , when the user.id is equal to ceritification.AId it returns a records eventhough it was not satisfied with the parameter supplied. For this example I supplied a value "xandrick" which return an Id=184, but the problem is it returns two ids which is 184 and 154.What is the best way to return an ID(s) that satisfied of the supplied given value? User table Id Firstname Lastname Middlename Email AlternativeEmail 154 Gregorio Honasan Pimentel [email protected] [email protected] 156 Qwerty Qazggf fgfgf [email protected]. [email protected] 184 Xandrick Flores NULL [email protected] null Certification table Id AID Certification School 12 184 sdssd AMA 13 43 web-based and framework 2 Asian development foundation college 16 184 hjhjhj STI 17 184 rrrer PUP 18 154 vbvbv AMA SELECT DISTINCT Users.Id FROM Users INNER JOIN Certification on Users.Id=Certification.aid LEFT JOIN FREETEXTTABLE (Users,(Firstname,Middlename,Lastname,Email,AlternativeEmail), 'xandrick' )as ftUsr ON Users.Id=ftUsr.[KEY] LEFT JOIN FREETEXTTABLE (Certification,(Certification,School), 'xandrick' )as ftCert ON Certification.Id=ftCert.[KEY]

  Read the article

• Sql - add row when not existed

  - by Nguyen Tuan Linh

  Suppose I have a query that returns result like this: Project Year Type Amt PJ00001 2012 1 1000 PJ00001 2012 2 1000 PJ00001 2011 1 1000 PJ00002 2012 1 1000 What I want: Every Project will have 2 rows of Types for each Year. If the row is not there, add it to the result with Amt = 0. For example: - PJ00001 have 2 rows of type 1,2 in 2012 -- OK. But in 2011, it only have 1 row of Type 1 -- We add one row:PJ00001 2011 2 0 - PJ00002 have only 1 row of type 1 -- add:PJ00002 2012 2 0 Is there a way to easily do it. The only way I know now is to create a view like: PJ_VIEW. And then: SELECT * FROM PJ_VIEW UNION ALL SELECT t.PROJECT, t.YEAR_NO, 1 AS TYPE_NO, 0 AS AMT FROM PJ_VIEW t WHERE NOT EXISTS (SELECT 1 FROM PJ_VIEW t2 WHERE t2.PROJECT = t.PROJECT AND t2.YEAR_NO = t.YEAR_NO AND t2.TYPE_NO = 1) UNION ALL SELECT t.PROJECT, t.YEAR_NO, 2 AS TYPE_NO, 0 AS AMT FROM PJ_VIEW t WHERE NOT EXISTS (SELECT 1 FROM PJ_VIEW t2 WHERE t2.PROJECT = t.PROJECT AND t2.YEAR_NO = t.YEAR_NO AND t2.TYPE_NO = 2)

  Read the article

• Pass a variable into a trigger

  - by Codesleuth

  I have a trigger which deals with some data for logging purposes like so: CREATE TRIGGER trgDataUpdated ON tblData FOR UPDATE AS BEGIN INSERT INTO tblLog ( ParentID, OldValue, NewValue, UserID ) SELECT deleted.ParentID, deleted.Value, inserted.Value, @intUserID -- how can I pass this in? FROM inserted INNER JOIN deleted ON inserted.ID = deleted.ID END How can I pass in the variable @intUserID into the above trigger, as in the following code: DECLARE @intUserID int SET @intUserID = 10 UPDATE tblData SET Value = @x PS: I know I can't literally pass in @intUserID to the trigger, it was just used for illustration purposes.

  Read the article

• Retrieving data from MySQL in one SQL statement

  - by james.ingham

  Hi all, If I'm getting my data from Mysql like so: $result = $dbConnector->Query("SELECT * FROM branches, businesses WHERE branches.BusinessId = businesses.Id ORDER BY businesses.Name"); $resultNum = $dbConnector->GetNumRows($result); if($resultNum > 0) { for($i=0; $i < $resultNum; $i++) { $row = $dbConnector->FetchArray($result); // $row['businesses.Name']; // $row['branches.Name']; echo $row['Name']; } } Does anyone know how to print the field Name in businesses and how to print the name from branches? My only other alternative is to rename the fields or to call Mysql with two seperate queries. Thanks in advance

  Read the article

• SQL syntax problem (multiple selects)

  - by user279521

  I am having problems retrieving accurate data values with my stored proc query below: CREATE PROCEDURE usp_InvoiceErrorLog @RecID int AS DECLARE @ErrorString as varchar(1000), @ErrorCode as int; Select @ErrorCode = ErrorCode from tbl_AcctRecv_WebRpt Where RecID = @RecID; IF NOT(@ErrorCode = NULL) Begin Select @ErrorString = ErrorDesc from tbl_ErrDesc Where ErrorCode = @ErrorCode End Select RecID, VendorNum, VendorName, InvNum, InvTotal, (SELECT CONVERT(VARCHAR(11), InvDate, 106) AS [DD MON YYYY]) As InvDate, TicketRequestor, ErrorCode, @ErrorString as ErrorDesc from tbl_AcctRecv_WebRpt Where RecID = @RecID The ErrorDesc column (in the final select statement at the bottom) returns a NULL value, when it should return a valid string data. Any ideas?

  Read the article

• how to list out all triggers in a particular table OR database?

  - by nectar

  how can I list out all triggers on a table?

  Read the article

• How can I limit the number of connections to an mssql server from my tomcat deployed java applicatio

  - by CJ

  Hi, I have an application that is deployed on tomcat on server A and sends queries to a huge variety of mssql databases on an server B. I am concerned that my application could overload this mssql database server and would like some way to preventing it making requests to connect to any database on that server if some arbitrary number of connections were already in existence and unclosed. I am looking at using connection pooling but am under the impression that this will only pool connections to a specific database on the mssql server, I want to control the total of these combined connections that will occur to many different databases (incidentally I can only find out the names of individual db's dynamically as they change day to day). Will connection pooling take care of this for me, are am I looking at this from the wrong perspective? I have no access to the configuration of the mssql server. Links to tutorials or working examples of your suggested solution are most welcome! Thanks, Caroline

  Read the article

• sql error, NULL??

  - by Luke

  So I have a query, can someone let me know if it looks ok content wise? "INSERT INTO ".TBL_MESSAGES." (NULL, 'Your ranking points have changed', 'Due to your recent activity, your ranking points have increased by $r', '2', '$u', '0', '0', '0', '0', NULL, NULL, NULL, NULL, now())"; I can add further information if the query doesnt appear to have a problem? Thanks

  Read the article

• Using a RegEx in a SQL Query

  - by Jim B

  Hey Everyone, Here's the situation I'm in: We have a field in our database that contains a 3 digit number, surrounded by some text. This number is actually a PK in another table, and I need to extract this out so I can implement a proper FK relationship. Here's an example of what would currently reside in the column: Some Text Goes Here - (305) Followed By Some More Text So, what I'm looking to do is extract the '305' from the column, and hopefully end up with a result that looks something like this (pseudo code) SELECT <My Extracted Value>, Original Column Text, Id FROM dbo.MyTable It seems to me that using a Regex match in my query is the most effective way to do this. Can anybody point me in the right direction?

  Read the article

• SQL Group By equivalent

  - by MikeB

  Pretend I have a cupcake_rating table: id | cupcake | delicious_rating -------------------------------------------- 1 | Strawberry | Super Delicious 2 | Strawberry | Mouth Heaven 3 | Blueberry | Godly 4 | Blueberry | Super Delicious I want to find all the cupcakes that have a 'Super Delicious' AND 'Mouth Heaven' rating. I feel like this is easily achievable using a group by clause and maybe a having. I was thinking: select distinct(cupcake) from cupcake_rating group by cupcake having delicious_rating in ('Super Delicious', 'Mouth Heaven') I know I can't have two separate AND statements. I was able to achieve my goal using: select distinct(cupcake) from cupcake_rating where cupcake in ( select cupcake from cupcake_rating where delicious_rating = 'Super Delicious' ) and cupcake in ( select cupcake from cupcake_rating where delicious_rating = 'Mouth Heaven' ) This will not be satisfactory because once I add a third type of rating I am looking for, the query will take hours (there are a lot of cupcake ratings).

  Read the article

• Show two differents values from the same field

  - by Hebert

  HI, i have two tables 1- name, id, code 2- id, value, concept One name can have two concepts and two values. i want to retreive this: Id, name, value1, value2. How can i do that? Tanks

  Read the article

• Problem with update sql with excel

  - by phenevo

  Hi, I have a problem with this query: Update Provinces Set Provinces.DefaultName=T2.Defaultname from Provinces inner join OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\provinces.xlsx;HDR=YES', 'SELECT Code, Defaultname FROM [Arkusz1$]') T2 On Provinces.Code = t2.Code where Provinces.Code = T2.Code I get error: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". What is a reason of this unpleasent situation ?

  Read the article

• Linq-to-Sql query advice please

  - by Mantorok

  Hi all Just wondering if this is a good approach, I need to search for items containing all of the specified keywords in a space-delimted string, is this the right approach this? var result = (from row in DataContext.PublishedEvents join link in DataContext.PublishedEvent_EventDateTimes on row.guid equals link.container join time in DataContext.EventDateTimes on link.item equals time.guid orderby row.EventName select new {row, time}); // Split the keyword(s) to limit results with all of those words in. foreach(var keyword in request.Title.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries)) { var val = keyword; result = result.Where(row=>row.row.EventName.Contains(val)); } var end = result.Select(row=>new EventDetails { Title = row.row.EventName, Description = TrimDescription(row.row.Description), StartDate = row.time.StartDate, EndDate = row.time.EndDate, Url = string.Format(ConfigurationManager.AppSettings["EventUrl"], row.row.guid) }); response.Total = end.Count(); response.Result = end.ToArray(); Is there a slick Linq-way of doing all of this in one query? Thanks

  Read the article

• SQL Query with computed column

  - by plotnick

  help me please with a query. Assume that we have a table with columns: Transaction StartTime EndTime Now, I need a query with computed column of (value = EndTime-Startime). Actually I need to group Users(Transaction has a FK for Users) and sort them by average time spent for transaction.

  Read the article

• SQL Server last called stored procedures with parameters

  - by Teoman shipahi

  I am using ASP.NET environment. Is it possible to track last N number of stored procedures called with parameters info? I see in this article "Recently executed stored procedures"; http://sqlfool.com/2009/08/find-recently-executed-stored-procedures/ But I need input parameters also. If not what can be the best way to track it? For example, adding an insert statement to a information table for every single procedure beginning? Or is there any better solution for this?

  Read the article

• SQL to get friends AND friends of friends of a user

  - by Enrique

  My MySQL tables structure is like this. USER int id varchar username FRIEND_LIST int user_id int friend_id For each friend relationship I insert 2 records in FRIEND_LIST. If user 1 is friend of user 2 then the next rows are inserted into FRIEND_LIST 1,2 2,1 I want to get the friends and friends of friends of an specific user. The select should return columns a, b, c. a: user_id b: friend_id c: username (username of friend_id ) If 1 is friend of 2 and 3. 2 is friend of 3, 4 and 5 3 is friend of 5,6,7 Then the query to get 1's friends and friends of friends should return: 1 2 two 1 3 three 2 1 one 2 3 three 2 4 four 2 5 five 3 1 one 3 5 five 3 6 six 3 7 seven Can I get this rows with a single query?

  Read the article

• sql select with exact outcome

  - by Shiro

  Asking a simple question, just want everyone have fun to solve it. I got 2 tables. 1. Student 2. Course Student +----+--------+ | id | name | +----+--------+ | 1 | User1 | | 2 | User2 | +----+--------+ Course +----+------------+------------+ | id | student_id | course_name| +----+------------+------------+ | 1 | 1 | English | | 2 | 1 | Chinese | | 3 | 2 | English | | 4 | 2 | Japanese | +----+------------+------------+ I would like to get the result all student, who have taken English and Chinese, NOT English or Chinese. Expected result: +----+------------+------------+ | id | student_id | course_name| +----+------------+------------+ | 1 | 1 | English | | 2 | 1 | Chinese | +----+------------+------------+ What we normally do is select * from student join course on (student.id = course.student_id) WHERE course_name = 'English' OR course_name = 'Chinese' but in this result I can get User2 record which is not my expected result. I want the record only display the User take the course English+Chinese only.

  Read the article

• How to perform this select?

  - by m.edmondson

  Say I have the simple table below: KeyWordID KeyWord ----------- ---------- 1 Blue 3 Yellow 1 Yellow How would I select the KeyWordID that selects the KeyWordIDs that where both KeyWord is Blue and Yellow. E.g. it should only return 1, as this is the only KeyWordID that has both Keywords Blue and Yellow I initially thought GROUPBY - but its not quite working as expected.

  Read the article

• Microsoft SQL Server 2008 - 99% fragmentation on non-clustered, non-unique index

  - by user550441

  I have a table with several indexes (defined below). One of the indexes (IX_external_guid_3) has 99% fragmentation regardless of rebuilding/reorganizing the index. Anyone have any idea as to what might cause this, or the best way to fix it? We are using Entity Framework 4.0 to query this, the EF queries on the other indexed fields about 10x faster on average then the external_guid_3 field, however an ADO.Net query is roughly the same speed on both (though 2x slower than the EF Query to indexed fields). Table id(PK, int, not null) guid(uniqueidentifier, null, rowguid) external_guid_1(uniqueidentifier, not null) external_guid_2(uniqueidentifier, null) state(varchar(32), null) value(varchar(max), null) infoset(XML(.), null) -- usually 2-4K created_time(datetime, null) updated_time(datetime, null) external_guid_3(uniqueidentifier, not null) FK_id(FK, int, null) locking_guid(uniqueidentifer, null) locked_time(datetime, null) external_guid_4(uniqueidentifier, null) corrected_time(datetime, null) is_add(bit, not null) score(int, null) row_version(timestamp, null) Indexes PK_table(Clustered) IX_created_time(Non-Unique, Non-Clustered) IX_external_guid_1(Non-Unique, Non-Clustered) IX_guid(Non-Unique, Non-Clustered) IX_external_guid_3(Non-Unique, Non-Clustered) IX_state(Non-Unique, Non-Clustered)

  Read the article

• sql insert query needed

  - by masfenix

  Hey guys, so I have two tables. They are pictured below. I have a master table "all_reports". And a user table "user list". The master table may have users that do not exist in the user list. I need to add them to the user list. The master table may have duplicates in them (check picture). The master list does not contain all the information that the user list requires (no manager, no HR status, no department.. again check picture).

  Read the article

• sql stored procedure in visual studio 2008

  - by Greg

  Hi, I want to write stored procedure in visual studio that as a parameter recieves the name of project and runs in database TT and copies data from TT.dbo.LCTemp (where the LC is the name of the project recieved as a parameter) table to "TT.dbo.Points" table. both tables have 3 columns: PT_ID, Projectname and DateCreated I think I have written it wrong, here it is: ALTER PROCEDURE dbo.FromTmpToRegular @project varchar(10) AS BEGIN declare @ptID varchar(20) declare @table varchar(20) set @table = 'TT.dbo.' + @project + 'Temp' set @ptID = @table + '.PT_ID' Insert into TT.dbo.Points Select * from [@table] where [@ptID] Not in(Select PT_ID from TT.dbo.Points) END Any idea what I did wrong? Thanks! :) Greg

  Read the article

• Database schemas WAY out of sync - need to get up to date without losing data

  - by Zind

  The problem: we have one application that has a portion which is used by a very small subset of the total users, and that part of the application is running off of a separate database as well. In a perfect world, the schemas of the two databases would be synced up, but such is not the case. Some migrations have been run on the smaller database, most haven't; and furthermore, there is nothing such as revision number to be able to easily identify which have and which haven't. We would like to solve this quandary for future projects. During a discussion we've come up with the following possible plan of action, and I am wondering if anyone knows of any project which has already solved this problem: What we would like to do is create an empty database from the schema of the large fully-migrated database, and then move all of the data from the smaller non-migrated database into that empty one. If it makes things easier, it can probably be assumed for the sake of this problem specifically that no migrations have ever removed anything, only added. Else, if there are other known solutions, I'd like to hear them as well.

  Read the article

• deleting and reusing a temp table in a stored precedure

  - by Sheagorath

  Hi I need to SELECT INTO a temp table multiple times with a loop but I just can't do it, because after the table created by SELECT INTO you can't simply drop the table at the end of the loop, because you can't delete a table and create it again in the same batch. so how can I delete a table in a stored procedure and create it again? is it possible to this without using a temp table? here is a snippet of where I am actualy using the temp table which is supposed to be a pivoting algorithm: WHILE @offset<@NumDays BEGIN SELECT bg.*, j.ID, j.time, j.Status INTO #TEMP1 FROM #TEMP2 AS bg left outer join PersonSchedule j on bg.PersonID = j.PersonID and bg.TimeSlotDateTime = j.TimeSlotDateTime and j.TimeSlotDateTime = @StartDate + @offset DROP TABLE #TEMP2; SELECT * INTO #TEMP2 FROM #TEMP1 DROP TABLE #TEMP1 SET @offset = @offset + 1 END

  Read the article

< Previous Page | 326 327 328 329 330 331 332 333 334 335 336 337  | Next Page >