Search Results

Search found 43145 results on 1726 pages for 'sql select'.

Page 332/1726 | < Previous Page | 328 329 330 331 332 333 334 335 336 337 338 339  | Next Page >

• Multipart Identifier And Functions

  - by The King

  Here is my Query... Here I'm using a function Fn_getStagesForProject()... For which I need to pass the SWProjectID from Projects Table... The function takes the ID as parameter and return all stages that corressponds to the project, on which I need to filer only the row that contains StageLevel as 0. Select A.SWProjectID, A.ShortTitle, C.StageName as StageName, B.ExpectedCompletionDate as BudgetedReleaseDate From Projects as A left outer join ProjectBudgets as B on A.SWProjectID = B.SWProjectID Left outer join Fn_getStagesForProject(Projects.SWProjectID) as C on B.StageID = C.StageID Where C.StageLevel = 0 The error is The multi-part identifier "Projects.SWProjectID" could not be bound. I tried changing it to A.SWProjectID, but I still get the error... Thanks in advance for your help. Let me know, incase you need the Table Structure Raja

  Read the article

• casting odd smallint time to to datetime format.

  - by c6400sc

  Hello everyone, I'm working with a db (SQL server 2008), and have an interesting issue with times stored in the db. The DBA who originally set it up was crafty and stored scheduled times as smallints in 12-hour form-- 6:00AM would be represented as 600. I've figured out how to split them into hours and minutes like thus: select floor(time/100) as hr, right(time, 2) as min from table; What I want to do is compare these scheduled times to actual times, which are stored in the proper datetime format. Ideally, I would do this with two datetime fields and use datediff() between them, but this would require converting the smallint time into a datetime, which I can't figure out. Does anyone have suggestions on how to do this? Thanks in advance.

  Read the article

• Fastest method for SQL Server inserts, updates, selects

  - by Ian

  I use SPs and this isn't an SP vs code-behind "Build your SQL command" question. I'm looking for a high-throughput method for a backend app that handles many small transactions. I use SQLDataReader for most of the returns since forward only works in most cases for me. I've seen it done many ways, and used most of them myself. Methods that define and accept the stored procedure parameters as parameters themselves and build using cmd.Parameters.Add (with or without specifying the DB value type and/or length) Assembling your SP params and their values into an array or hashtable, then passing to a more abstract method that parses the collection and then runs cmd.Parameters.Add Classes that represent tables, initializing the class upon need, setting the public properties that represent the table fields, and calling methods like Save, Load, etc I'm sure there are others I've seen but can't think of at the moment as well. I'm open to all suggestions.

  Read the article

• Forcing LINQ to SQL to make one single call for all child rows

  - by zaph0d

  Let say I have a method (example taken from another post): public IQueryable<CityBlock> GetCityBlocks(){ var results = from o in db.city_blocks let buildings = GetBuildingsOnBlock(o.block_id) //returns Iqueryable select new CityBlock { BuildingsOnBlock = buildings, BlockOwner = o.block_owner }; return results; } In the calling method I add Skip() and Take() methods plus some filtering and then do a ToList(). The trouble is that I am getting dozens of database calls - one for all the city blocks and then a separate one for each building. Is there a way that I can refactor this code to just make two calls: one for the city blocks and one for all the buildings

  Read the article

• Speed up this query joining to a table multiple times

  - by Mongus Pong

  Hi, I have this query that (stripped right down) goes something like this : SELECT [Person_PrimaryContact].[LegalName], [Person_Manager].[LegalName], [Person_Owner].[LegalName], [Person_ProspectOwner].[LegalName], [Person_ProspectBDM].[LegalName], [Person_ProspectFE].[LegalName], [Person_Signatory].[LegalName] FROM [Cache] LEFT JOIN [dbo].[Person] AS [Person_Owner] WITH (NOLOCK) ON [Person_Owner].[PersonID] = [Cache].[ClientOwnerID] LEFT JOIN [dbo].[Person] AS [Person_Manager] WITH (NOLOCK) ON [Person_Manager].[PersonID] = [Cache].[ClientManagerID] LEFT JOIN [dbo].[Person] AS [Person_Signatory] WITH (NOLOCK) ON [Person_Signatory].[PersonID] = [Cache].[ClientSignatoryID] LEFT JOIN [dbo].[Person] AS [Person_PrimaryContact] WITH (NOLOCK) ON [Person_PrimaryContact].[PersonID] = [Cache].[PrimaryContactID] LEFT JOIN [dbo].[Person] AS [Person_ProspectOwner] WITH (NOLOCK) ON [Person_ProspectOwner].[PersonID] = [Cache].[ProspectOwnerID] LEFT JOIN [dbo].[Person] AS [Person_ProspectBDM] WITH (NOLOCK) ON [Person_ProspectBDM].[PersonID] = [Cache].[ProspectBDMID] LEFT JOIN [dbo].[Person] AS [Person_ProspectFE] WITH (NOLOCK) ON [Person_ProspectFE].[PersonID] = [Cache].[ProspectFEID] Person is a huge table and each join to it has a pretty significant hit in the execution plan. Is there anyway I can adjust this query so that I am only linking to it once, or at least get SQL Server to scan through it only once?

  Read the article

• Incorrect value for UNIQUE_CONSTRAINT_NAME in REFERENTIAL_CONSTRAINTS

  - by van

  I am listing all FK constraints for a given table using INFORMATION_SCHEMA set of views with the following query: SELECT X.UNIQUE_CONSTRAINT_NAME, "C".*, "X".* FROM "INFORMATION_SCHEMA"."KEY_COLUMN_USAGE" AS "C" INNER JOIN "INFORMATION_SCHEMA"."REFERENTIAL_CONSTRAINTS" AS "X" ON "C"."CONSTRAINT_NAME" = "X"."CONSTRAINT_NAME" AND "C"."TABLE_NAME" = 'MY_TABLE' AND "C"."TABLE_SCHEMA" = 'MY_SCHEMA' Everything works perfectly well, but for one particular constraint the value of UNIQUE_CONSTRAINT_NAME column is wrong, and I need it in order to find additional information from the referenced Column. Basically, for most of the rows the UNIQUE_CONSTRAINT_NAME contains the name of the unique constraint (or PK) in the referenced table, but for one particular FK it is the name of some other unique constraint. I dropped and re-created the FK - did not help. My assumption is that the meta-data is somehow screwed. Is there a way to rebuild the meta data so that the INFORMATION_SCHEMA views would actually show the correct data?

  Read the article

• Designing Relational Survey Questionnaires Database

  - by user1213055

  I'm trying to build a simple sql database for following access database. Currently there is no relationship and I just have two tables male and female with 6 sections in each form. How can I design it a better way so end user can connect to the database and analyze using STATA or SPSS ? I'm really confused whether I should create one table with all fields or break down into different tables. The database is specific to this study only so I'm not looking for a generic survey database where user can create surveys and capture them. Any feedback or suggestion is much appreciated.

  Read the article

• Remove specific string from multiple database rows in SQL

  - by Scott

  I have a column that contains page titles, which has the website name appended to the end of each. (e.g. Product Name | Company Name Inc.) I would like to remove the " | Company Name Inc." from multiple rows simultaneously. What SQl query commands (or query itself) would allow me to accomplish this? To re-illustrate, I want to convert multiple rows of 1 column from this: Product Name | Company Name Inc. To this: Product Name

  Read the article

• Conditional PIVOT/transform problem

  - by IanC

  Hi folks I have a table with three columns, which we'll call ID1, ID2, and Value. Sample data: ID ID1 Value 1 1 0 1 2 1 1 3 1 1 3 2 1 4 0 1 4 1 1 5 0 1 5 2 2 1 2 Value is limited to 0, 1, or 2. What I need to do is pivot/transform this data into a column-based count of how many times each possible Value appears, grouped by ID, ID1. The output of the above should be: ID ID1 Val0 Val1 Val2 1 1 1 0 0 1 2 0 2 0 1 3 0 1 1 1 4 1 1 0 1 5 1 0 1 2 1 0 0 1 I'm using SQL Server 2008. How do I do this?

  Read the article

• big size of database-log SQL-Server 2008

  - by t.kehl

  I have a database which is running under Microsoft SQL Server 2008. Now, I have seen, that the log of the database (ldf-file) is growing to big size. The database-file (mdf) has a size of 630MB and the log-file has a size of 12GB. I ask me now, what the reason for this can be. Is there a tool which let me seeing into the log where I can see, what is the reason for this big size? What can I do to prevent that the log is growing to this big size?

  Read the article

• How do I switch the table that is queried with linq-to-sql

  - by Ian Ringrose

  We have two tables with the same set of columns; depending on the “type” of object the value is stored in one of the two tables. I wish to use common code to access these two tables. If I was using “raw sql” I could just use String.Format() to change the table name. (Likewise for updates etc) The two separate tables are needed as the data access patterns are very different for the common queries on the two tables and therefore different indexes are needed. “Views” and “instead of triggers” etc to make the tables look like a single table are not liked here. A lot of our customers use low end version of SqlServer so we cannot use partition tables.

  Read the article

• SQL finding overlapping of times pass midnight (across 2 days)

  - by janechii

  Hi everyone, I know there are lots of these types of questions, but i didn't see one that was similar enough to my criteria. So i'd like to ask for your help please. The fields i have are just start and end which are of time types. I cannot involve any specific dates in this. If the time ranges don't go pass midnight across day, i'd just compare two tuples as such: end1 > start2 AND start1 < end2 (end points touching are not considered overlapped here.) But when I involve time range that pass (or at) midnight, this obviously doesn't work. For example, given: start | end --------+-------- 06:00PM | 01:00AM 03:00PM | 09:00PM Without involving dates, how can i achieve this, please. My assumption is, if end is less than start, then we're involving 2 days. I'm trying to do this in plain standard SQL, so just a simple and concise logic in the WHERE clause. Thank you everyone!

  Read the article

• SQL update statement to change the value of a field and not replace it

  - by wzzrd

  I'm in the process of migrating some databases. I have this table that has a couple of hundred rows, and has a filename column. To each record in this table, the filename column needs to be altered and part of a path needs to be prepended to the value that is in that field. The table is like: | 1 | filename1 | | 2 | filename2 | and needs to become: | 1 | path/filename1 | | 2 | path/filename2 | I am not an SQL guru, but I know the basics. This eludes me though. Is there a way to do something like: update table set filename = 'path/' + filename where id = 1;

  Read the article

• Arabic SQL query (on Oracle DB) returns empty result

  - by unprecedented

  I have this query (that runs on Oracle 10g database): SELECT ge.*, ge.concept AS glossarypivot FROM s_glossary_entries ge WHERE (ge.glossaryid = '161' OR ge.sourceglossaryid = '161') AND (ge.approved != 0 OR ge.userid = 361) AND concept = '?' ORDER BY ge.concept The query must display all words that begin with the arabic letter "?" but unfortunately, it returns empty result .. However, if I run the same query on the same database which runs on MYSQL, it works well and displays the correct result .. What should I do in order to get this query working the right way on oracle 10 database? P.S. the oracle database character set is : "AL32UTF8" thank you so much in advance

  Read the article

• LINQ to SQL - Get only substring from a field

  - by domanokz

  I'm studying ASP.NET MVC and I use LINQ to SQL for model. I have a table named "Note" with the fields "Title" and "Content". The "Content" field can contain thousand characters. What I want to do is to display the LIST of notes in a page. I use table with two columns, for "Title" and SUBSTRING of the "Content" (50 characters). My problem is, I don't know how to edit the model so that it will display only the substring of the "Content". Thanks in advance!

  Read the article

• MS SQL Specific Tables Hanging at Queries

  - by Jonn

  I have SQL Server 2008. I run a query in a table on a database. The weirdest thing keeps happening. I run a simple select statement on the table. I know there are 62 rows in the table but it gets stuck at row 48 and goes on "querying...". Waited already for hours and it didn't move on from there. I only know of two programs, and one reporting service connecting to that particular table and one other user. Does anyone have any idea on what could be causing this and how I could trace the source of the lock on that table? As a side note, I noted that the logs only had a notice that Autogrow failed the day before I checked. Could this have something to do with it?

  Read the article

• Database design: one huge table or separate tables?

  - by littlegreen

  Currently I am designing a database for use in our company. We are using SQL Server 2008. The database will hold data gathered from several customers. The goal of the database is to acquire aggregate benchmark numbers over several customers. Recently, I have become worried with the fact that one table in particular will be getting very big. Each customer has approximately 20.000.000 rows of data, and there will soon be 30 customers in the database (if not more). A lot of queries will be done on this table. I am already noticing performance issues and users being temporarily locked out. My question, will we be able to handle this table in the future, or is it better to split this table up into smaller tables for each customer?

  Read the article

• Multiple column foreign key contraints

  - by eugene4968

  I want to setup table constraints for the following scenario and I’m not sure how to do it or if it’s even possible in SQL Server 2005. I have three tables A,B,C. C is a child of B. B will have a optional foreign key(may be null) referencing A. For performance reasons I also want table C to have the same foreign key reference to table A. The constraint on table C should be that C must reference its parent (B) and also have the same foreign key reference to A as its parent. Anyone have any thoughts on how to do this?

  Read the article

• How to avoid Foreign Keys constraints for all tables in DB truncate ?

  - by eugeneK

  Hi, for designing purposes i need to truncate all DB which has lots of FK's. I cannot use DELETE command simply because some tables set with Identity of TinyInts and contain about 150 items. this is a query ( truncate all tables in selected DB ) i'm trying to run Declare @t varchar (1024) Declare tbl_cur cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' OPEN tbl_cur FETCH NEXT from tbl_cur INTO @t WHILE @@FETCH_STATUS = 0 BEGIN EXEC ('TRUNCATE TABLE '+ @t) FETCH NEXT from tbl_cur INTO @t END CLOSE tbl_cur DEALLOCATE tbl_Cur What the best and easiest way to achieve truncate on DB with many FK's ?

  Read the article

• Java: Do something on event in SQL Database?

  - by wretrOvian

  Hello I'm building an application with distributed parts. Meaning, while one part (writer) maybe inserting, updating information to a database, the other part (reader) is reading off and acting on that information. Now, i wish to trigger an action event in the reader and reload information from the DB whenever i insert something from the writer. Is there a simple way about this? Would this be a good idea? : // READER while(true) { connect(); // reload info from DB executeQuery("select * from foo"); disconnect(); }

  Read the article

• Beginner SQL question(s)

  - by unit

  I am two months in to an intro sql course, it's late at night, and I am drawing a blank. I have two tables, one customers, and one orders. I have to increase any customers credit limit by twenty five percent for all customers who have made two or more orders in which each order is more than the amount of 250.00. I get how to UPDATE CreditLimit * 1.25 and Cust with an order 250, but how the hell do I get it to check if they have made two orders over 250? Second question, we are just starting to take subqueries, and I am having a difficult time getting it into my skull. Another question posed by the prof of our class is to increase the credit limit of a customer who has an order that exceeds their credit limit. (Credit limit is on a customers table, order and amount are on an orders table). I then take that customer and UPDATE his CreditLimit +1000. Thanks for any help.

  Read the article

• No supported translation to SQL

  - by derans

  We have this code: private IList<InfoRequest> GetBy(Func<InformationRequest, string> func, string searchby) { var requests = _dc.InformationRequests .Where(x => func.Invoke(x).Contains(searchby)) .OrderBy(y => y.RequestDate); return Mapper.Map<InformationRequest[], InfoRequest[]>(requests.ToArray()); } It continues to throw the no supported translation to SQL error. Any ideas on the problem or how to resolve it?

  Read the article

• [SQL] Query returning more than one row with the same name

  - by Neutralise

  I am having trouble with an SQL query returning more than one row with the same name, using this query: SELECT * FROM People P JOIN SpecialityCombo C ON P.PERSONID = C.PERSONID JOIN Speciality S ON C.GROUPID = S.ID; People contains information on each person, Specialty contains the names and ID of each specialty and SpecialityCombo contains information about the associations between People and their Speciality, namely each row has a PERSONID and a Speciality ID (trying to keep it normalised to some extent). My query works in that it returns each Person and the name of their specialty, but it returns n rows for the number of specialitys they want, because each specialty returns the same row 'name'. What I want is it to return just one row containing each speciality. How can I do this?

  Read the article

• How to write a CASE WHEN statement with multiple DATEDIFF variables

  - by Anne C

  I need to calculate the difference between two dates (facility_start_date, facility_end_date) for a report in Reporting Services in SQL 2005. If the facility_end_date is null then it needs to use the report parameter @EndDate in the calculation. However if the facility_end_date is greater than the parameter @EndDate, then it also needs to use the paramenter @EndDate. The code below works fine except that if the facility_end_date is greater than the parameter @EndDate it is still calculating between the facility_start_date and facility_end_date, rather than between the facility_start_date and @EndDate. Any help would be appreciated. CASE WHEN facility_start_date > facility_end_date THEN NULL WHEN DATEPART(day , facility_start_date) > DATEPART(day , facility_end_date) THEN DATEDIFF(d , facility_start_date , ISNULL(facility_end_date , @EndDate)) - 1 WHEN DATEPART(day , .facility_end_date) > DATEPART(day , @EndDate) THEN DATEDIFF(d , facility_start_date , @EndDate) - 1 ELSE DATEDIFF(d , facility_start_date , ISNULL facility_end_date , @EndDate)) END

  Read the article

• How to use the merge command

  - by Vaccano

  Say I have a table called Employee (has ID, NAME, ADDRESS, and PHONE columns). (Not my real problem, but simplified to make the question easier.) If I call a sproc called UpdateEmployee and I pass in a @Name, @Address, @Phone and @ID. Can merge be used to easily check to see if the ID exists? If it does to update the name, address and phone? and if it does not to insert them? I see examples on the net, but they are huge and hairy. I would like a nice simple example if possible. (We recently upgraded to SQL 2008, so I am new to the merge command.)

  Read the article

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