Search Results

Search found 98443 results on 3938 pages for 'sql server'.

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 465/3938 | < Previous Page | 461 462 463 464 465 466 467 468 469 470 471 472  | Next Page >

  • How to Delete all data from a table which contain self referencing foreign key

    - by Shantanu Gupta
    I have a table which has employee relationship defined within itself. i.e. EmpID Name SeniorId ----------------------- 1 A NULL 2 B 1 3 C 1 4 D 3 and so on... Where Senior ID is a foreign key whose primary key table is same with refrence column EmpId I want to clear all rows from this table without removing any constraint. How can i do this? Deletion need to be performed like this 4, 3 , 2 , 1 How can I do this

    Read the article

  • What is the mysql 5.5 equivalent for the sys.dm_fts_index_keywords_by_document in sql 2008

    - by djsurge
    I'm making a web application that uses the data in the sys.dm_fts_index_keywords_by_document. I'm interested how many times a given term occurs in each string that is indexed. For example, I have a table with a column called comments, the table has various strings in the comments field. When I make that column full text searchable, the dm_fts_index_keywords_by_document is created and I can see the word per document data. Can i do the same thing in mySQL?

    Read the article

  • MS SQL: Mitigating schema changes/upgrades

    - by bradhe
    I haven't spent a ton of time researching this yet, mostly looking for best practices on upgrading/changing DB schemas. We're actively developing a new product and as such we often have additions or changes to our DB schema. We also have many copies of the DB -- one for the test environment, one for the prod environment, dev environments, you name it. We don't really want to have to blow away test data every time we want to make a change to the DB. s Are there good ways of automating this or handling this? None of us have really ever had to deal with this so...

    Read the article

  • If/Then in SQL Embedded in VBA

    - by Daniel
    I've got a string like this in my Excel VBA: strSQL = "SELECT * FROM Total WHERE (SimulationID = (" & TextBox1.Text & ") And Test1 = (" & Example & "))" However, sometimes Test will be 'is null', which makes the query And Example = is NULL How can I change it to add an if/then statement or something to make it say And Example is null when Example has a value of "is null"?

    Read the article

  • SQL joins "going up" two tables

    - by blcArmadillo
    I'm trying to create a moderately complex query with joins: SELECT `history`.`id`, `parts`.`type_id`, `serialized_parts`.`serial`, `history_actions`.`action`, `history`.`date_added` FROM `history_actions`, `history` LEFT OUTER JOIN `parts` ON `parts`.`id` = `history`.`part_id` LEFT OUTER JOIN `serialized_parts` ON `serialized_parts`.`parts_id` = `history`.`part_id` WHERE `history_actions`.`id` = `history`.`action_id` AND `history`.`unit_id` = '1' ORDER BY `history`.`id` DESC I'd like to replace `parts`.`type_id` in the SELECT statement with `part_list`.`name` where the relationship I need to enforce between the two tables is `part_list`.`id` = `parts`.`type_id`. Also I have to use joins because in some cases `history`.`part_id` may be NULL which obviously isn't a valid part id. How would I modify the query to do this?

    Read the article

  • SQL Server search filter and order by performance issues

    - by John Leidegren
    We have a table value function that returns a list of people you may access, and we have a relation between a search and a person called search result. What we want to do is that wan't to select all people from the search and present them. The query looks like this SELECT qm.PersonID, p.FullName FROM QueryMembership qm INNER JOIN dbo.GetPersonAccess(1) ON GetPersonAccess.PersonID = qm.PersonID INNER JOIN Person p ON p.PersonID = qm.PersonID WHERE qm.QueryID = 1234 There are only 25 rows with QueryID=1234 but there are almost 5 million rows total in the QueryMembership table. The person table has about 40K people in it. QueryID is not a PK, but it is an index. The query plan tells me 97% of the total cost is spent doing "Key Lookup" witht the seek predicate. QueryMembershipID = Scalar Operator (QueryMembership.QueryMembershipID as QM.QueryMembershipID) Why is the PK in there when it's not used in the query at all? and why is it taking so long time? The number of people total 25, with the index, this should be a table scan for all the QueryMembership rows that have QueryID=1234 and then a JOIN on the 25 people that exists in the table value function. Which btw only have to be evaluated once and completes in less than 1 second.

    Read the article

  • copy entire row (without knowing field names)

    - by Todd Webb
    Using SQL Server 2008, I would like to duplicate one row of a table, without knowing the field names. My key issue: as the table grows and mutates over time, I would like this copy-script to keep working, without me having to write out 30+ ever-changing fields, ugh. Also at issue, of course, is IDENTITY fields cannot be copied. My code below does work, but I wonder if there's a more appropriate method than my thrown-together text string SQL statement? So thank you in advance. Here's my (yes, working) code - I welcome suggestions on improving it. Todd alter procedure spEventCopy @EventID int as begin -- VARS... declare @SQL varchar(8000) -- LIST ALL FIELDS (*EXCLUDE* IDENTITY FIELDS). -- USE [BRACKETS] FOR ANY SILLY FIELD-NAMES WITH SPACES, OR RESERVED WORDS... select @SQL = coalesce(@SQL + ', ', '') + '[' + column_name + ']' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'EventsTable' and COLUMNPROPERTY(OBJECT_ID('EventsTable'), COLUMN_NAME, 'IsIdentity') = 0 -- FINISH SQL COPY STATEMENT... set @SQL = 'insert into EventsTable ' + ' select ' + @SQL + ' from EventsTable ' + ' where EventID = ' + ltrim(str(@EventID)) -- COPY ROW... exec(@SQL) -- REMEMBER NEW ID... set @EventID = @@IDENTITY -- (do other stuff here) -- DONE... -- JUST FOR KICKS, RETURN THE SQL STATEMENT SO I CAN REVIEW IT IF I WISH... select EventID = @EventID, SQL = @SQL end

    Read the article

  • How to call an extended procedure from a function

    - by Alan Featherston Lago
    hi im having trouble trying to get the following function to work. CREATE FUNCTION test ( @nt_group VARCHAR(128) ) RETURNS @nt_usr TABLE ( [name] [nchar](128) NULL , [type] [char](8) NULL , [privilege] [char](9) NULL , [mapped login name] [nchar](128) NULL , [permission path] [nchar](128) NULL ) AS BEGIN INSERT INTO @nt_usr EXEC master.dbo.xp_logininfo 'DOMAIN\USER', @nt_group RETURN END As far as i know i should be allowed to call an extended stored procedure, im getting the following error Mes 443, Level 16, State 14 Could it be that xp_logininfo might return different result sets depending on the parameters? When i use openquery i can overcome this by setting this: SET FMTONLY OFF. Does anyone know if there's a similar workaround for my problem?

    Read the article

  • SQL GROUP BY - also SELECT not-constant columns

    - by Michal Kosek
    can someone please help me with this query? I have 2 tables in my database: log_visitors: -------------------- id | host_id log_access: -------------------- visitor | document | timestamp "log_access.visitor" links to "log_visitors.id" Currently, I'm using this query: SELECT log_visitors.host_id , MIM(log_access.timestamp) AS min_timestamp FROM log_access INNER JOIN log_visitors ON (log_access.visitor = log_visitors.id) GROUP BY log_visitors.host_id; to get "MIN(timestamp)" for each "host_id" in the database. HERE'S MY QUESTION: I also need to get "document" for that access with that timestamp... I can't simply add "log_access.document" into SELECT list, since it's not constant and I am not grouping by document... Any ideas?

    Read the article

  • SQL Server Full Text Search Leading Wildcard

    - by aherrick
    After taking a look at this SO question and doing my own research, it appears that you cannot have a leading wildcard while using full text search. So in the most simple example, if I have a Table with 1 column like below: TABLE1 coin coinage undercoin select COLUMN1 from TABLE1 where COLUMN1 LIKE '%coin%' Would get me the results I want. How can I get the exact same results with FULL TEXT SEARCH enabled on the column? The following two queries return the exact same data, which is not exactly what I want. SELECT COLUMN1 FROM TABLE1 WHERE CONTAINS(COLUMN1, '"coin*"') SELECT COLUMN1 FROM TABLE1 WHERE CONTAINS(COLUMN1, '"*coin*"')

    Read the article

  • How to combine two sql queries?

    - by plasmuska
    Hi Guys, I have a stock table and I would like to create a report that will show how often were items ordered. "stock" table: item_id | pcs | operation apples | 100 | order oranges | 50 | order apples | -100 | delivery pears | 100 | order oranges | -40 | delivery apples | 50 | order apples | 50 | delivery Basically I need to join these two queries together. A query which prints stock balances: SELECT stock.item_id, Sum(stock.pcs) AS stock_balance FROM stock GROUP BY stock.item_id; A query which prints sales statistics SELECT stock.item_id, Sum(stock.pcs) AS pcs_ordered, Count(stock.item_id) AS number_of_orders FROM stock GROUP BY stock.item_id, stock.operation HAVING stock.operation="order"; I think that some sort of JOIN would do the job but I have no idea how to glue queries together. Desired output: item_id | stock_balance | pcs_ordered | number_of_orders apples | 0 | 150 | 2 oranges | 10 | 50 | 1 pears | 100 | 100 | 1 This is just example. Maybe, I will need to add more conditions because there is more columns. Is there a universal technique of combining multiple queries together?

    Read the article

  • Optimizing a Soundex Query for finding similar names

    - by xkingpin
    My application will offer a list of suggestions for English names that "sound like" a given typed name. The query will need to be optimized and return results as quick as possible. Which option would be most optimal for returning results quickly. (Or your own suggestion if you have one) A. Generate the Soundex Hash and store it in the "Names" table then do something like the following: (This saves generating the soundex hash for at least every row in my db per query right?) select name from names where NameSoundex = Soundex('Ann') B. Use the Difference function (This must generate the soundex for every name in the table?) select name from names where Difference(name, 'Ann') = 3 C. Simple comparison select name from names where Soundex(name) = Soundex('Ann') Option A seems like to me it would be the fastest to return results because it only generates the Soundex for one string then compares to an indexed column "NameSoundex" Option B should give more results than option A because the name does not have to be an exact match of the soundex, but could be slower Assuming my table could contain millions of rows, what would yield the best results?

    Read the article

  • stored procedure for importing txt in sql server db

    - by Iulian
    I have to insert new records in a database every day from a text file ( tab delimited). I'm trying to make this into a stored procedure with a parameter for the file to read data from. CREATE PROCEDURE dbo.UpdateTable @FilePath BULK INSERT TMP_UPTable FROM @FilePath WITH ( FIRSTROW = 2, MAXERRORS = 0, FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n' ) RETURN Then i would call this stored procedure from my code (C#) specifying the file to insert. This is obviously not working, so how can i do it ?

    Read the article

  • switch case in where clause

    - by Nimesh
    hi, i need to check three conditions: if @filter = 1 { **select * from employeestable where rating is not null** } else if @filter = 2 { **select * from employeestable where rating is null** } else { **select * from employeestable** } This i need to do using a case statement. now i have more than 30 lines of query, if i use case i can reduce my code upto 70% Please let mek now how can i do this.

    Read the article

  • Saving and retrieving image in SQL database from C# problem

    - by Mobin
    I used this code for inserting records in a person table in my DB System.IO.MemoryStream ms = new System.IO.MemoryStream(); Image img = Image_Box.Image; img.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp); this.personTableAdapter1.Insert(NIC_Box.Text.Trim(), Application_Box.Text.Trim(), Name_Box.Text.Trim(), Father_Name_Box.Text.Trim(), DOB_TimePicker.Value.Date, Address_Box.Text.Trim(), City_Box.Text.Trim(), Country_Box.Text.Trim(), ms.GetBuffer()); but when i retrieve this with this code byte[] image = (byte[])Person_On_Application.Rows[0][8]; MemoryStream Stream = new MemoryStream(); Stream.Write(image, 0, image.Length); Bitmap Display_Picture = new Bitmap(Stream); Image_Box.Image = Display_Picture; it works perfectly fine but if i update this with my own generated Query like System.IO.MemoryStream ms = new System.IO.MemoryStream(); Image img = Image_Box.Image; img.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp); Query = "UPDATE Person SET Person_Image ='"+ms.GetBuffer()+"' WHERE (Person_NIC = '"+NIC_Box.Text.Trim()+"')"; the next time i use the same code for retrieving the image and displaying it as used above . Program throws an exception

    Read the article

  • SQL joining 3 tables when 1 table is emty

    - by AdRock
    I am trying to write a query that connects 3 tables. The first table is info about each festival The second table is the number of votes for each festival The third table is reviews for each festival I want to join all 3 tables so i get all the feilds from table1, join table1 with table2 on the festivalid but i also need to count the number of records in table 3 that applys to each festival. The first 2 tables give me a result becuase they both have data in them but table 3 is empty becuase there are no reviews yet so adding that to my query fives me no results SELECT f.*, v.total, v.votes, v.festivalid, r.reviewcount as count FROM festivals f INNER JOIN vote v ON f.festivalid = v.festivalid INNER JOIN (SELECT festivalid, count(*) as reviewcount FROM reviews) GROUP BY festivalid) as r on r.festivalid = v.festivalid

    Read the article

  • How to split a single column values to multiple column values?

    - by Shahsra
    Hi All, I have a problem splitting single column values to multiple column values. For Example: **Name** abcd efgh ijk lmn opq asd j. asdjja asb (asdfas) asd asd and I need the output something like this: first_name last_name abcd efgh ijk opq asd asdjja asb asd asd null The middle name can be omitted (no need for a middle name) The columns are already created and need to insert the data from that single 'Name' column. Thanks a lot, Shahsra

    Read the article

  • More FP-correct way to create an update sql query

    - by James Black
    I am working on access a database using F# and my initial attempt at creating a function to create the update query is flawed. let BuildUserUpdateQuery (oldUser:UserType) (newUser:UserType) = let buf = new System.Text.StringBuilder("UPDATE users SET "); if (oldUser.FirstName.Equals(newUser.FirstName) = false) then buf.Append("SET first_name='").Append(newUser.FirstName).Append("'" ) |> ignore if (oldUser.LastName.Equals(newUser.LastName) = false) then buf.Append("SET last_name='").Append(newUser.LastName).Append("'" ) |> ignore if (oldUser.UserName.Equals(newUser.UserName) = false) then buf.Append("SET username='").Append(newUser.UserName).Append("'" ) |> ignore buf.Append(" WHERE id=").Append(newUser.Id).ToString() This doesn't properly put a , between any update parts after the first, for example: UPDATE users SET first_name='Firstname', last_name='lastname' WHERE id=... I could put in a mutable variable to keep track when the first part of the set clause is appended, but that seems wrong. I could just create an list of tuples, where each tuple is oldtext, newtext, columnname, so that I could then loop through the list and build up the query, but it seems that I should be passing in a StringBuilder to a recursive function, returning back a boolean which is then passed as a parameter to the recursive function. Does this seem to be the best approach, or is there a better one?

    Read the article

  • Get latest sql rows based on latest date and per user

    - by Umair
    I have the following table: RowId, UserId, Date 1, 1, 1/1/01 2, 1, 2/1/01 3, 2, 5/1/01 4, 1, 3/1/01 5, 2, 9/1/01 I want to get the latest records based on date and per UserId but as a part of the following query (due to a reason I cannot change this query as this is auto generated by a tool but I can write pass any thing starting with AND...): SELECT RowId, UserId, Date FROM MyTable WHERE 1 = 1 AND ( // everything which needs to be done goes here . . . ) I have tried similar query, but get an error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    Read the article

  • SQL for Opening Hours

    - by mouthpiec
    Hi, In my shops database I need to have the opening hours. Do you have an idea how i can implement this in my dB? The opening hours are from Monday to Sunday, each day can have 2 opening windows (ex 09:00-12:00, 16:00-19:00)

    Read the article

  • MDX Filter a dimention with another dimension

    - by John Hartsock
    To all excuse my ignorance, but im new to MDX below is the query im trying to run SELECT NON EMPTY { [Measures].[Task Burn Down] } ON COLUMNS, NON EMPTY { ([Calendar].[Date].[Date].ALLMEMBERS * [Priority].[Priority Code].[Priority Code].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { Filter([Calendar].[Date].AllMembers, [Calendar].[Date].CurrentMember <= [Sprint].[Sprint].CurrentMember.Properties("Stop Date")) } ) ON COLUMNS FROM ( SELECT ( { [Sprint].[Sprint].AllMembers } ) ON COLUMNS FROM ( SELECT ( { [Team].[Team].AllMembers } ) ON COLUMNS FROM [Backlog2] ) ) ) WHERE ( [Team].[Team].&[27], [Sprint].[Sprint].&[9] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS When I run this I expect to get 1 value for [Measures].[Task Burn Down] for each day of the month. But I only want one month. My Sprint dimension has a stop date and I do not want to return any values after the stop date. What am I doing wrong?

    Read the article

  • syntax for single row MERGE / upsert in SQL Server

    - by Jacob
    I'm trying to do a single row insert/update on a table but all the examples out there are for sets. Can anyone fix my syntax please: MERGE member_topic ON mt_member = 0 AND mt_topic = 110 WHEN MATCHED THEN UPDATE SET mt_notes = 'test' WHEN NOT MATCHED THEN INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test') Resolution per marc_s is to convert the single row to a subquery - which makes me think the MERGE command is not really intended for single row upserts. MERGE member_topic USING (SELECT 0 mt_member, 110 mt_topic) as source ON member_topic.mt_member = source.mt_member AND member_topic.mt_topic = source.mt_topic WHEN MATCHED THEN UPDATE SET mt_notes = 'test' WHEN NOT MATCHED THEN INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test');

    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 | 461 462 463 464 465 466 467 468 469 470 471 472  | Next Page >