Search Results

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

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 342/1094 | < Previous Page | 338 339 340 341 342 343 344 345 346 347 348 349  | Next Page >

  • Stored Procedure IDENTITY_INSERT

    - by Jacob
    I'm recently change my data table, I remove column and add a new column that define as identity = True and identity seed = 1, identity increment = 1. When i tried to insert data to this table by STORE PROCEDURE i get this exception: An explicit value for the identity column in table 'AirConditioner' can only be specified when a column list is used and IDENTITY_INSERT is ON. I saw that i need to add this lines: SET IDENTITY_INSERT [dbo].[AirConditioner] ON and finally OFF I added and its still throw an exception... My store procedure is attached as a picture

    Read the article

  • MongoDB equivalent of SQL "OR"

    - by Matt
    So, MongoDB defaults to "AND" when finding records. For example: db.users.find({age: {'$gte': 30}, {'$lte': 40}}); The above query finds users = 30 AND <= 40 years old. How would I find users <= 30 OR = 40 years old?

    Read the article

  • SQL query: Last but one rank for user

    - by Derk
    My table structure looks like this: create table rankings ( id IDENTITY NOT NULL, user_id INT NOT NULL, game_poule_id INT NOT NULL, rank INT NOT NULL, insertDate DATETIME NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (game_poule_id) REFERENCES game_poules(id) ON DELETE CASCADE ); All old rankings of users per game are saved in this table. Now I want to have the last but one rank in the table for all users in a gamepoule. Has someone an idea how to achive this? Thanks

    Read the article

  • SQL Server 2005: Insert a row in a table and update the same row

    - by vikas
    eg:table pkey --guid annualpay datefrom dateto--if null means current record percentannualincrease percent annual increase will be calculated only if there is a difference in newly inserted and previously existing last differing value. percentannualincrease = ([newannualpay-just previous pay(if different from current)]/newannualpay)*100 eg newid(),5000,today,null,0--very first row newid(),5000,today+1,null(*),0 newid,5500,today+2,null(*),?????????????--> need to be calculated before insert *--insert will close the previous record by updating dateto=null to todays date How can I do this stuff in a trigger???

    Read the article

  • Copy Rows in a One to Many with LINQ to SQL

    - by Refracted Paladin
    I have a table that stores a bunch of diagnosis for a single plan. When the users create a new plan I need to copy over all existing diagnosis's as well. I had thought to try the below but this is obviously not correct. I am guessing that I will need to loop through my oldDiagnosis part, but how? Thanks! My Attempt so far... public static void CopyPlanDiagnosis(int newPlanID, int oldPlanID) { using (var context = McpDataContext.Create()) { var oldDiagnosis = from planDiagnosi in context.tblPlanDiagnosis where planDiagnosi.PlanID == oldPlanID select planDiagnosi; var newDiagnosis = new tblPlanDiagnosi { PlanID = newPlanID, DiagnosisCueID = oldDiagnosis.DiagnosisCueID, DiagnosisOther = oldDiagnosis.DiagnosisOther, AdditionalInfo = oldDiagnosis.AdditionalInfo, rowguid = Guid.NewGuid() }; context.tblPlanDiagnosis.InsertOnSubmit(newDiagnosis); context.SubmitChanges(); } }

    Read the article

  • Application self aware of external database record modifications.

    - by Khou
    How do you make your application aware that a database record was changed or created by an external application rather than the application itself? Do you add a special check sum to the database record or what do you do to stop external changes to the database? (in case it was hacked and the hacker decides to add a new record or change an existing database record)

    Read the article

  • Interval arithmetic to correctly deal with end of month - Oracle SQL

    - by user2003974
    I need a function which will do interval arithmetic, dealing "correctly" with the different number of days in a month. For my version of "correctly" - see below! First try select to_date('31-May-2014') + interval '1' months from dual This returns an error, because there is no 31st June. I understand that this behaviour is expected due to the ANSI standard. Second try select add_months(to_date('31-May-2014'),1) from dual This correctly (in my use case) returns 30th June 2014, which is great. BUT select add_months(to_date('28-Feb-2014'),1) from dual returns 31st March 2014, when I want 28th March 2014. Background This has to do with legal deadlines. The deadlines are expressed in law as a number of months (say, 3) from a base date. If the base date is last day of the month and three months later the month is longer, then the deadline does NOT extend to the end of the longer month (as per the add_months function). However, if the base date is last day of the month and three months later the month is shorter, then the deadline expires on the last day of the shorter month. Question Is there a function that does what I need? I have intervals (year to month) stored in a table, so preferably the function would look like: add_interval_correctly(basedate DATE, intervaltoadd INTERVAL YEAR TO MONTH)

    Read the article

  • Sum two rows in one - My Sql

    - by user303832
    I have found some similar posts, but I didn't find them useful. But I didn't know how to group them. I would like to Sum 'No' and 'Not Set' to one row, and to lose 'Not Set' row. So : 'No' = 'No' + 'Not Set' I have something like this : TEST TestCount Month 'Yes' 123 March 'No' 432 March 'Not Set' 645 March 'Yes' 13 April 'No' 42 April 'Not Set' 45 April 'Yes' 133 May 'No' 41 May 'Not Set' 35 May .... And I would like something like this : TEST TestCount Month 'Yes' 423 March 'No' 410 March 'Yes' 154 April 'No' 192 April 'Yes' 130 May 'No' 149 May .... Can anybody help me with this, tnx in advance

    Read the article

  • Simple calculation in SQL Server

    - by meep
    I got a distance field in my database that stores the distance traveled on a specific route. I need to select all the distance fields and plus them together, then returning the result from my stored procedure. How can this be done?

    Read the article

  • What is the most effective and flexible way to generate combinations in TSQL?

    - by SDReyes
    What is the most effective and flexible way to generate combinations in TSQL? With 'Flexible', I mean you should be able to add easily combination rules. e.g.: to generate combinatories of 'n' elements, sorting, remove duplicates, get combinatories where each prize belongs to a different lottery, etc. For example, Having a set of numbers representing lottery prizes. Number | Position | Lottery --------------------------- 12 | 01 | 67 12 | 02 | 67 34 | 03 | 67 43 | 01 | 89 72 | 02 | 89 33 | 03 | 89 (I include the position column because, a number could be repeated among different lottery's prizes) I would like to generate combinatories like: Numbers | Lotteries ------------------- 12 12 | 67 67 12 34 | 67 67 12 34 | 67 67 12 43 | 67 89 12 72 | 67 89 12 33 | 67 89 . . .

    Read the article

  • SQL LIKE question

    - by user176121
    I was wondering if there's a drawback (other than bad practice) to using something like this SELECT * FROM my_table WHERE id LIKE '1'; where id is an integer. I know you're supposed to use id=1 but I am writing a java program and if everything can use LIKE it'll be a lot easier for me. Also, so far, everything works fine; I get the correct query results, so if there is no drawback I will continue doing it like this. edit: I am using MySQL.

    Read the article

  • integrity Constraints on a table.

    - by Dinesh
    See this sample schema Passenger(id PK, Name) Plane(id PK, capacity, type); Flight(id PK, planeId FK(Plane), flightDate, StartLocation, destination) CREATE TABLE Reservation(PassengerId, flightId, PRIMARY KEY (passengerId, flightId), FOREIGN KEY (passengerId) REFERENCES Passenger, FOREIGN KEY (flightId) REFERENCES Flight); I need to define an integrity constraint that enforces the restriction that the number of passengers on a plane cannot exceed the plane’s capacity. I have tried and achieved so far is this. CREATE TABLE Reservation( passengerId INTEGER, flightId INTEGER, PRIMARY KEY (passengerId, flightId), FOREIGN KEY (passengerId) REFERENCES Passenger, FOREIGN KEY (flightId) REFERENCES Flight, Constraint check1 check(Not Exists(select * from Flight s, (select count(*) as totalRes from Reservation group by flightId) t where t.totalRes > s.capacity ) ) ); I am not sure i am doing in right way or not. Any suggestions?

    Read the article

  • SSIS Null Value Questions

    - by Saobi
    I have a table with 5 string columns, all can be NULLs. After I read the data from this table, I want to convert any null values into empty strings. The reason is that I need to compare these columns with columns in another table of the same schema (using conditional split), and null values would cause the comparison to evaluate to NULL. Is there any functionality in SSIS that allows me to convert NULL's to empty strings, or just not having to deal with NULL's at all?

    Read the article

  • Strange LINQ to SQL Behavior

    - by mcass20
    What is wrong with the last query? Is it a bug or am I missing something? This query returns 2 records (correct): query = query.Where(Log => SqlMethods.Like(Log.FormattedMessage, "%<key>Name</key><value>David</value>%")); This query returns 2 records (correct): query = query.Where(Log => SqlMethods.Like(Log.FormattedMessage, "%<key>Name</key><value>%David%</value>%")); This query returns 0 records (correct): query = query.Where(Log => SqlMethods.Like(Log.FormattedMessage, "%<key>Name</key><value>av</value>%")); This query returns 2 records (correct): query = query.Where(Log => SqlMethods.Like(Log.FormattedMessage, "%<key>Name</key><value>%av%</value>%")); This query returns 0 records (correct): query = query.Where(Log => SqlMethods.Like(Log.FormattedMessage, "%<key>Name</key><value>v</value>%")); This query returns 15 records (incorrect, should return 2): query = query.Where(Log => SqlMethods.Like(Log.FormattedMessage, "%<key>Name</key><value>%v%</value>%"));

    Read the article

  • Fill in missing values in a SELECT statement

    - by benjamin button
    If i have a table with two fields.customer id and order. let's say i have in total order ID 1,2,3,4 all the customer can have all the four orders.like below 1234 1 1234 2 1234 3 1234 4 3245 3 3245 4 5436 2 5436 4 you can see above that 3245 customer doesnt have order id 1 and 2. how could i print in the query output like 3245 1 3245 2 5436 1 5436 3 EDIT: i dont have order table but i have list of order's like we can hard code it in the query(1,2,3,4) i dont have an orders table.

    Read the article

  • Multiple column sorting (SQL SERVER 2005)

    - by Newbie
    I have a table which looks like Col1 col2 col3 col4 col5 1 5 1 4 6 1 4 0 3 7 0 1 5 6 3 1 8 2 1 5 4 3 2 1 4 The script is declare @t table(col1 int, col2 int, col3 int,col4 int,col5 int) insert into @t select 1,5,1,4,6 union all select 1,4,0,3,7 union all select 0,1,5,6,3 union all select 1,8,2,1,5 union all select 4,3,2,1,4 I want the output to be every column being sorted in ascending order i.e. Col1 col2 col3 col4 col5 0 1 0 1 3 1 3 1 1 4 1 4 2 3 5 1 5 2 4 6 4 8 5 6 7 I already solved tye problem by the folowing program Select x1.col1 ,x2.col2 ,x3.col3 ,x4.col4 ,x5.col5 From (Select Row_Number() Over(Order By col1) rn1, col1 From @t)x1 Join(Select Row_Number() Over(Order By col2) rn2, col2 From @t)x2 On x1.rn1=x2.rn2 Join(Select Row_Number() Over(Order By col3) rn3, col3 From @t)x3 On x1.rn1=x3.rn3 Join(Select Row_Number() Over(Order By col4) rn4, col4 From @t)x4 On x1.rn1=x4.rn4 Join(Select Row_Number() Over(Order By col5) rn5, col5 From @t)x5 On x1.rn1=x5.rn5 But I am not happy with this solution. Is there any better way to achieve the same? (Using set based approach) If so, could any one please show an example. Thanks

    Read the article

  • Looping over some selected values in a stored procedure

    - by macca1
    I'm trying to modify a stored procedure hooked into an ORM tool. I want to add a few more rows based on a loop of some distinct values in a column. Here's the current SP: SELECT GRP = STAT_CD, CODE = REASN_CD FROM dbo.STATUS_TABLE WITH (NOLOCK) Order by STAT_CD, SRT_ORDR For each distinct STAT_CD, I'd also like to insert a REASN_CD of "--" here in the SP. However I'd like to do it before the order by so I can give them negative sort orders so they come in at the top of the list. I'm getting tripped up on how to implement this. Does anyone know how to do this for each unique STAT_CD?

    Read the article

  • SQL Server 2008 + expensive union all

    - by Tim Mahy
    Hi al, we have 5 tables over which we should query with user search input throughout a stored procedure. We do a union all of the similar data inside a view. Because of this the view can not be materialized. We are not able to change these 5 tables drastically (like creating a 6th table that contains the similar data of the 5 tables and reference that new one from the 5 tables). The query is rather expensive / slow what are our other options? It's allowed to think outside the box. Unfortunately I cannot give more information like the table/view/SP definition because of customer confidentiality... greetings, Tim

    Read the article

  • Use Linq to SQL to generate sales report

    - by Richard Reddy
    I currently have the following code to generate a sales report over the last 30 days. I'd like to know if it would be possible to use linq to generate this report in one step instead of the rather basic loop I have here. For my requirement, every day needs to return a value to me so if there are no sales for any day then a 0 is returned. Any of the Sum linq examples out there don't explain how it would be possible to include a where filter so I am confused on how to get the total amount per day, or a 0 if no sales, for the last days I pass through. Thanks for your help, Rich //setup date ranges to use DateTime startDate = DateTime.Now.AddDays(-29); DateTime endDate = DateTime.Now.AddDays(1); TimeSpan startTS = new TimeSpan(0, 0, 0); TimeSpan endTS = new TimeSpan(23, 59, 59); using (var dc = new DataContext()) { //get database sales from 29 days ago at midnight to the end of today var salesForDay = dc.Orders.Where(b => b.OrderDateTime > Convert.ToDateTime(startDate.Date + startTS) && b.OrderDateTime <= Convert.ToDateTime(endDate.Date + endTS)); //loop through each day and sum up the total orders, if none then set to 0 while (startDate != endDate) { decimal totalSales = 0m; DateTime startDay = startDate.Date + startTS; DateTime endDay = startDate.Date + endTS; foreach (var sale in salesForDay.Where(b => b.OrderDateTime > startDay && b.OrderDateTime <= endDay)) { totalSales += (decimal)sale.OrderPrice; } Response.Write("From Date: " + startDay + " - To Date: " + endDay + ". Sales: " + String.Format("{0:0.00}", totalSales) + "<br>"); //move to next day startDate = startDate.AddDays(1); } }

    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 | 338 339 340 341 342 343 344 345 346 347 348 349  | Next Page >