Search Results

Search found 28900 results on 1156 pages for 'sql 2005'.

Page 355/1156 | < Previous Page | 351 352 353 354 355 356 357 358 359 360 361 362  | Next Page >

  • How do I free SQLServerCE's COM instance from the current running process?

    - by David Thornley
    It's been a while since I touched COM so be nice ;) This is under WindowsCE 5.0 with SQLServerCE 2.0. After calling this to load SQLServerCE 2.0 : - IDBInitialize *pIDBInitialize = NULL; CoCreateInstance(CLSID_SQLSERVERCE_2_0, NULL, CLSCTX_INPROC_SERVER, IID_IDBInitialize, (void**)&pIDBInitialize); Module load occurs for SSCE20.dll which obviously loads the SQLServerCE engine into the process space. What I don't understand is if I do this immediately after :- pIDBInitialize->Release(); I don't see a dll module unload, so that SSCE20.dll (and friends) are still loaded into my process. Now I tried CoFreeUnusedLibraries() which I figure forces COM to purge any unused libraries, but it doesn't seem to do the trick. At runtime I want to be able to completely unload the SQLServerCE 2.0 dll from the process to streamline an upgrade to 3.5SP1. I suspect this has something to do with the shared dll model that Microsoft use under WindowsCE... but, I might be wrong :) Thanks in advance, David.

    Read the article

  • Linq-to-sql Compiled Query returning object NOT belonging to submitted DataContext

    - by Vladimir Kojic
    Compiled query: public static class Machines { public static readonly Func<OperationalDataContext, short, Machine> QueryMachineById = CompiledQuery.Compile((OperationalDataContext db, short machineID) => db.Machines.Where(m => m.MachineID == machineID).SingleOrDefault() ); public static Machine GetMachineById(IUnitOfWork unitOfWork, short id) { Machine machine; // Old code (working) //var machineRepository = unitOfWork.GetRepository<Machine>(); //machine = machineRepository.Find(m => m.MachineID == id).SingleOrDefault(); // New code (making problems) machine = QueryMachineById(unitOfWork.DataContext, id); return machine; } It looks like compiled query is caching Machine object and returning the same object even if query is called from new DataContext (I’m disposing DataContext in the service but I’m getting Machine from previous DataContext). I use POCOs and XML mapping. Revised: It looks like compiled query is returning result from new data context and it is not using the one that I passed in compiled-query. Therefore I can not reuse returned object and link it to another object obtained from datacontext thru non compiled queries. [TestMethod] public void GetMachinesTest() { // Test Preparation (not important) using (var unitOfWork = IoC.Get<IUnitOfWork>()) { var machineRepository = unitOfWork.GetRepository<Machine>(); // GET ALL List<Machine> list = machineRepository.FindAll().ToList<Machine>(); VerifyIntegratedMachine(list[2], 3, "Machine 3", "333333", "G300PET", "MachineIconC.xaml", false, true, LicenseType.Licensed, "10.0.97.3", "10.0.97.3", 0); var machine = Machines.GetMachineById(unitOfWork, 3); Assert.AreSame(list[2], machine); // PASS !!!! } using (var unitOfWork = IoC.Get<IUnitOfWork>()) { var machineRepository = unitOfWork.GetRepository<Machine>(); // GET ALL List<Machine> list = machineRepository.FindAll().ToList<Machine>(); VerifyIntegratedMachine(list[2], 3, "Machine 3", "333333", "G300PET", "MachineIconC.xaml", false, true, LicenseType.Licensed, "10.0.97.3", "10.0.97.3", 0); var machine = Machines.GetMachineById(unitOfWork, 3); Assert.AreSame(list[2], machine); // FAIL !!!! } } If I run other (complex) unit tests I'm getting as expected: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext.

    Read the article

  • Penetration testing tools

    - by Shoban
    We have 100s of websites which were developed in asp, .net and java... and we are paying lot of money for an external agency to do a penetration testing for our sites to check for security loop holes. Are there any (good) software (paid or free) to do this? or.. are there any tehnical articles which can help me develop this tool.

    Read the article

  • SQL COUNT of COUNT

    - by cryptic-star
    I have some data I am querying. The table is composed of two columns - a unique ID, and a value. I would like to count the number of times each unique value appears (which can easily be done with a COUNT and GROUP BY), but I then want to be able to count that. So, I would like to see how many items appear twice, three times, etc. So for the following data (ID, val)... 1, 2 2, 2 3, 1 4, 2 5, 1 6, 7 7, 1 The intermediate step would be (val, count)... 1, 3 2, 3 7, 1 And I would like to have(count_from_above, new_count)... 3, 2 -- since three appears twice in the previous table 1, 1 -- since one appears once in the previous table Is there any query which can do that? If it helps, I'm working with Postgres. Thanks!

    Read the article

  • Problemwit sql query

    - by phenevo
    Hi, I've got query: INSERT INTO [Tasks] ([LoginName] ,[Type] ,[Filter] ,[Dictionary] ,[Description]) Select N'Anonymous',4,'SomeTable.targetcode in (select Code from cities where countrycode in ('TN')) and SomeTable.SomeValue in ('13','15')',3,N'Cities from tunis' Union All ... [Dictionary] is a part of query that i need to function on my server. I get: Incorrect syntax near ')) and SomeTable.SomeValue in (13,15)'.

    Read the article

  • sql - datetime variable versus string representation of datetime variable

    - by BhejaFry
    Hi folks, I have a query that takes too long to respond when the search parameter happens to be a varchar datatype with date. However, if i convert varchar to datetime variable, the query runs fine. For ex: This takes too long. select count(id) from names where updateddate '1/5/2010' This runs fine. declare @dateparam datetime set @dateparam = convert(datetime, '1/5/2010',102) select count(id) from names where updateddate @dateparam What's the reason one runs fine but the other doesn't? TIA

    Read the article

  • My VARCHAR(MAX) field is capping itself at 4000; what gives?

    - by eidylon
    Hello all... I have a table in one of my databases which is a queue of emails. Emails to certain addresses get accumulated into one email, which is done by a sproc. In the sproc, I have a table variable which I use to build the accumulated bodies of the emails, and then loop through to send each email. In my table var I have my body column defined as VARCHAR(MAX), seeing as there could be any number of emails currently accumulated for a given email address. It seems though that even though my column is defined as VARCHAR(MAX) it is behaving as if it were VARCHAR(4000) and is truncating the data going into it, although it does NOT throw any exceptions, it just silently stops concatenating any more data after 4000 characters. The MERGE statement is where it is building the accumulated email body into @EMAILS.BODY, which is the field that is truncating itself at 4000 characters. Below is the code of my sproc... ALTER PROCEDURE [system].[SendAccumulatedEmails] AS BEGIN SET NOCOUNT ON; DECLARE @SENTS BIGINT = 0; DECLARE @ROWS TABLE ( ROWID ROWID, DATED DATETIME, ADDRESS NAME, SUBJECT VARCHAR(1000), BODY VARCHAR(MAX) ) INSERT INTO @ROWS SELECT ROWID, DATED, ADDRESS, SUBJECT, BODY FROM system.EMAILQUEUE WHERE ACCUMULATE = 1 AND SENT IS NULL ORDER BY ADDRESS, DATED DECLARE @EMAILS TABLE ( ADDRESS NAME, ALLIDS VARCHAR(1000), BODY VARCHAR(MAX) ) DECLARE @PRVRID ROWID = NULL, @CURRID ROWID = NULL SELECT @CURRID = MIN(ROWID) FROM @ROWS WHILE @CURRID IS NOT NULL BEGIN MERGE @EMAILS AS DST USING (SELECT * FROM @ROWS WHERE ROWID = @CURRID) AS SRC ON SRC.ADDRESS = DST.ADDRESS WHEN MATCHED THEN UPDATE SET DST.ALLIDS = DST.ALLIDS + ', ' + CONVERT(VARCHAR,ROWID), DST.BODY = DST.BODY + '<i>'+CONVERT(VARCHAR,SRC.DATED,101)+' ' +CONVERT(VARCHAR,SRC.DATED,8) +':</i> <b>'+SRC.SUBJECT+'</b>'+CHAR(13)+SRC.BODY +' (Message ID '+CONVERT(VARCHAR,SRC.ROWID)+')' +CHAR(13)+CHAR(13) WHEN NOT MATCHED BY TARGET THEN INSERT (ADDRESS, ALLIDS, BODY) VALUES ( SRC.ADDRESS, CONVERT(VARCHAR,ROWID), '<i>'+CONVERT(VARCHAR,SRC.DATED,101)+' ' +CONVERT(VARCHAR,SRC.DATED,8)+':</i> <b>' +SRC.SUBJECT+'</b>'+CHAR(13)+SRC.BODY +' (Message ID '+CONVERT(VARCHAR,SRC.ROWID)+')' +CHAR(13)+CHAR(13)); SELECT @PRVRID = @CURRID, @CURRID = NULL SELECT @CURRID = MIN(ROWID) FROM @ROWS WHERE ROWID > @PRVRID END DECLARE @MAILFROM VARCHAR(100) = system.getOption('MAILFROM'), DECLARE @SMTPHST VARCHAR(100) = system.getOption('SMTPSERVER'), DECLARE @SMTPUSR VARCHAR(100) = system.getOption('SMTPUSER'), DECLARE @SMTPPWD VARCHAR(100) = system.getOption('SMTPPASS') DECLARE @ADDRESS NAME, @BODY VARCHAR(MAX), @ADDL VARCHAR(MAX) DECLARE @SUBJECT VARCHAR(1000) = 'Accumulated Emails from LIJSL' DECLARE @PRVID NAME = NULL, @CURID NAME = NULL SELECT @CURID = MIN(ADDRESS) FROM @EMAILS WHILE @CURID IS NOT NULL BEGIN SELECT @ADDRESS = ADDRESS, @BODY = BODY FROM @EMAILS WHERE ADDRESS = @CURID SELECT @BODY = @BODY + 'This is an automated message sent from an unmonitored mailbox.'+CHAR(13)+'Do not reply to this message; your message will not be read.' SELECT @BODY = '<style type="text/css"> * {font-family: Tahoma, Arial, Verdana;} p {margin-top: 10px; padding-top: 10px; border-top: single 1px dimgray;} p:first-child {margin-top: 10px; padding-top: 0px; border-top: none 0px transparent;} </style>' + @BODY exec system.LogIt @SUBJECT, @BODY BEGIN TRY exec system.SendMail @SMTPHST, @SMTPUSR, @SMTPPWD, @MAILFROM, @ADDRESS, NULL, NULL, @SUBJECT, @BODY, 1 END TRY BEGIN CATCH DECLARE @EMSG NVARCHAR(2048) = 'system.EMAILQUEUE.AI:'+ERROR_MESSAGE() SELECT @ADDL = 'TO:'+@ADDRESS+CHAR(13)+'SUBJECT:'+@SUBJECT+CHAR(13)+'BODY:'+@BODY exec system.LogIt @EMSG,@ADDL END CATCH SELECT @PRVID = @CURID, @CURID = NULL SELECT @CURID = MIN(ADDRESS) FROM @EMAILS WHERE ADDRESS > @PRVID END UPDATE system.EMAILQUEUE SET SENT = getdate() FROM system.EMAILQUEUE E, @ROWS R WHERE E.ROWID = R.ROWID END

    Read the article

  • How do I shrink my SQL Server Database?

    - by Rory Becker
    I have a Database nearly 1.9Gb Database in size MSDE2000 does not allow DBs that exceed 2.0Gb I need to shrink this DB (and many like it at various client locations) I have found and deleted many 100's of 1000's of records which are considered unneeded. these records account for a large percentage of some of the main (largest) tables in the Database. Therefore it's reasonable to assume much space should now be retrievable. So now I need to shrink the DB to account for the missing records I execute "DBCC ShrinkDatabase('MyDB')"......No effect. I have tried the various shrink facilities provided in MSSMS.... Still no effect. I have backed up the database and restored it... Still no effect. Still 1.9Gb Why? Whatever procedure I eventually find needs to be replayable on a client machine with access to nothing other than OSql or similar.

    Read the article

  • PIVOT / UNPIVOT in SQL Server 2008

    - by Nev_Rahd
    Hello I got child / parent tables as below. MasterTable: MasterID, Description ChildTable ChildID, MasterID, Description. Using PIVOT / UNPIVOT how can i get result as below in single row. if (MasterID : 1 got x child records) MasterID, ChildID1, Description1, ChildID2, Description2....... ChildIDx, Descriptionx Thanks

    Read the article

  • Help with SQL query (add 5% to users with conditions)

    - by Mestika
    Hi everyone, I’m having some difficulties with a query which purpose is to give users with more than one thread (called CS) in current year a 5% point “raise”. My relational schema looks like this: Thread = (threadid, threadname, threadLocation) threadoffering = (threadid, season, year, user) user = (name, points) Then, what I need is to check: WHERE thread.threadid = threadoffering.threadid AND where threadoffering.year AND threadoffering.season = currentDate AND where threadoffering.User 1 GIVE 5 % raise TO user.points I hope it is explained thoroughly but otherwise here it is in short text: Give a 5 % “point raise” to all users who has more than 1 thread in threadLocation CS in the current year and season (always dynamic, so for example now is year = 2010 and season is = spring). I am looking forward to your answer Sincerely, Emil

    Read the article

  • SQL Server 2008 Running trigger after Insert, Update locks original table

    - by Polity
    Hi Folks, I have a serious performance problem. I have a database with (related to this problem), 2 tables. 1 Table contains strings with some global information. The second table contains the string stripped down to each individual word. So the string is like indexed in the second table, word by word. The validity of the data in the second table is of less important then the validity of the data in the first table. Since the first table can grow like towards 1*10^6 records and the second table having an average of like 10 words for 1 string can grow like 1*10^7 records, i use a nolock in order to read the second this leaves me free for inserting new records without locking it (Expect many reads on both tables). I have a script which keeps on adding and updating rows to the first table in a MERGE statement. On average, the data beeing merged are like 20 strings a time and the scripts runs like ones every 5 seconds. On the first table, i have a trigger which is beeing invoked on a Insert or Update, which takes the newly inserted or updated data and calls a stored procedure on it which makes sure the data is indexed in the second table. (This takes some significant time). The problem is that when having the trigger disbaled, Reading the first table happens in a few ms. However, when enabling the trigger and your in bad luck of trying to read the first table while this is beeing updated, Our webserver gives you a timeout after 10 seconds (which is way to long anyways). I can quess from this part that when running the trigger, the first table is kept (partially) in a lock untill the trigger is completed. What do you think, if i'm right, is there a easy way around this? Thanks in advance! Cheers, Koen

    Read the article

  • Delete record in Linq to Sql

    - by Anders Svensson
    I have Linq2Sql classes User, Page, and UserPage (from a junction table), i.e. a many-to-many relationship. I'm using a gridview to show all Users, with a dropdownlist in each row to show the Pages visited by each user. Now I want to be able to delete records through the gridview, so I have added a delete button in the gridview by setting "Enable deleting" on it. Then I tried to use the RowDeleting event to specify how to delete the records since it doesn't work by default. And because its a relationship I know I need to delete the related records in the junction table before deleting the user record itself, so I added this in the RowDeleting event: protected void GridView2_RowDeleting(object sender, GridViewDeleteEventArgs e) { int id = (int)((DataKey)GridView2.DataKeys[e.RowIndex]).Value; UserPageDBDataContext context = new UserPageDBDataContext(); var userPages = from userPage in context.UserPages where userPage.User.UserID == id select userPage; foreach (var userPage in userPages) context.UserPages.DeleteOnSubmit(userPage); context.SubmitChanges(); var user = context.Users.Single(u => u.UserID == id); context.Users.DeleteOnSubmit(user); context.SubmitChanges(); } This actually seems to delete records, because the record with the id in question does indeed disappear, but strangely, a new record seems to be added at the end...! So, say I have 3 records in the gridview: 1 Jack stackoverflow.com 2 Betty stackoverflow.com/questions 3 Joe stackoverflow.com/whatever Now, if I try to delete user 1 (Jack), record number 1 will indeed disappear in the gridview, but the same record will appear at the end with a new id: 2 Jack stackoverflow.com 3 Betty stackoverflow.com/questions 4 Joe stackoverflow.com/whatever I have tried searching on how to delete records using Linq, and I believe I'm doing exacly as the examples I have read (e.g. the second example here: http://msdn.microsoft.com/en-us/library/Bb386925%28v=VS.100%29.aspx). I have read that you can also set cascade delete on the relationship in the database, but I wanted to do it this way in code, as your supposed to be able to. So what am I doing wrong?

    Read the article

  • Refreshing BindingSource after insert (Linq To SQL)

    - by Armagan
    I have a grid bound to a BindingSource which is bound to DataContext table, like this: myBindingSource.DataSource = myDataContext.MyTable; myGrid.DataSource = myBindingSource; I couldn't refresh BindingSource after insert. This didn't work: myDataContext.Refresh(RefreshMode.OverwriteCurrentValues, myBindingSource); myBindingSource.ResetBinding(false); Neither this: myDataContext.Refresh(RefreshMode.OverwriteCurrentValues, myDataContext.MyTable); myBindingSource.ResetBinding(false); What should I do?

    Read the article

  • NHibernate mapping error SQL Server 2008 Express

    - by developer
    Hi All, I tried an example from NHibernate in Action book and when I try to run the app, it throws an exception saying "Could not compile the mapping document: HelloNHibernate.Employee.hbm.xml" Below is my code, Employee.hbm.xml <?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true"> <class name="HelloNHibernate.Employee, HelloNHibernate" lazy="false" table="Employee"> <id name="id" access="field"> <generator class="native"/> </id> <property name="name" access="field" column="name"/> <many-to-one access="field" name="manager" column="manager" cascade="all"/> </class> Program.cs using System; using System.Collections.Generic; using System.Linq; using System.Text; using NHibernate; using System.Reflection; using NHibernate.Cfg; namespace HelloNHibernate { class Program { static void Main(string[] args) { CreateEmployeeAndSaveToDatabase(); UpdateTobinAndAssignPierreHenriAsManager(); LoadEmployeesFromDatabase(); Console.WriteLine("Press any key to exit..."); Console.ReadKey(); } static void CreateEmployeeAndSaveToDatabase() { Employee tobin = new Employee(); tobin.name = "Tobin Harris"; using (ISession session = OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { session.Save(tobin); transaction.Commit(); } Console.WriteLine("Saved Tobin to the database"); } } static ISession OpenSession() { if (factory == null) { Configuration c = new Configuration(); c.AddAssembly(Assembly.GetCallingAssembly()); factory = c.BuildSessionFactory(); } return factory.OpenSession(); } static void LoadEmployeesFromDatabase() { using (ISession session = OpenSession()) { IQuery query = session.CreateQuery("from Employee as emp order by emp.name asc"); IList<Employee> foundEmployees = query.List<Employee>(); Console.WriteLine("\n{0} employees found:", foundEmployees.Count); foreach (Employee employee in foundEmployees) Console.WriteLine(employee.SayHello()); } } static void UpdateTobinAndAssignPierreHenriAsManager() { using (ISession session = OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { IQuery q = session.CreateQuery("from Employee where name='Tobin Harris'"); Employee tobin = q.List<Employee>()[0]; tobin.name = "Tobin David Harris"; Employee pierreHenri = new Employee(); pierreHenri.name = "Pierre Henri Kuate"; tobin.manager = pierreHenri; transaction.Commit(); Console.WriteLine("Updated Tobin and added Pierre Henri"); } } } static ISessionFactory factory; } } Employee.cs using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace HelloNHibernate { class Employee { public int id; public string name; public Employee manager; public string SayHello() { return string.Format("'Hello World!', said {0}.", name); } } } App.config <?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler,NHibernate"/> </configSections> <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2"> <session-factory> <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property> <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property> <property name="connection.connection_string"> Data Source=SQLEXPRESS2008;Integrated Security=True; User ID=SQL2008;Password=;initial catalog=HelloNHibernate </property> <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property> <property name="show_sql">false</property> </session-factory> </hibernate-configuration> </configuration>

    Read the article

  • sql: can i do a max(count(*)) ?

    - by every_answer_gets_a_point
    here's my code: select yr,count(*) from movie join casting on casting.movieid=movie.id join actor on casting.actorid = actor.id where actor.name = 'John Travolta' group by yr here's the question Which were the busiest years for 'John Travolta'. Show the number of movies he made for each year. here's the table structure movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) this is the output i am getting: yr count(*) 1976 1 1977 1 1978 1 1981 1 1994 1 etcetc i need to get the rows for which count(*) is max how do i do this?

    Read the article

  • Displaying Many-To-Many Database relationship in VB.NET 2008 with DataGrid, MS SQL 2008

    - by user337501
    Computer bombed while posting this, couldnt find a duplicate question but if there is one, forgive me. So, I've run into a wall. And rather than use a ladder to avoid it, I'd like go through it. I'm setting up what I can best describe as a many-to-many relationship in a database. To examplify, imagine I have three primary tables: Items, Categories, Sections(nevermind the potential redundancy) Then I have another table, Properties. Items, Categories, and Sections can be associated with many properties. A single property can be associated with one, all, or none of the other tables. The best way I can figure to do this is to have join tables make the relationship. i.e. tblItems----(Foreign Key)----tblItems_To_Properties----(Foreign Key)----tblProperties In this example, tblItems simply has an "ItemID" Primary Key. tblItems_To_Properties has its own Primary Key(tblItems_To_PropertiesID), a Foreign Key to the Item(ItemID) and a Foreign key to the Property(PropertyID). The Properties table simply has its primary key(PropertyID) I hope this example isnt too confusing...if I have to I can find a way to put a diagram up or something. My problem is, I want to display this in a DataGrid using the Master-Detail method(DevExpress GridControl). I use the tblItems as a test, and I can see the Items in the parent view, but in the child view I see(understandably) the join table and that is it. My goal is to make it so the Grid ignores the join table and shows the Properties table as the only child. Any help on this method or insight into another solution would be muuuuuuuch appreciat

    Read the article

  • SQL Function that calculates Shift from StartTime and EndTime

    - by Gentis
    Hello Folks I have been trying to get a function going that calculates what Shift the Employees worked from their StartTime and EndTime. Here is the code i have so far, there seems to be calculating the shift wrong. Shift 1 from 08:00:00 - 16:30:00 Shift 2 from 16:00:00 - 00:30:00 Shift 3 from 00:00:00 - 08:30:00 Also the shift with most hours wins for times between shifts. Thanks, G `FUNCTION [dbo].[ShiftDifferential] ( @StartTime time(0), @EndTime time(0) ) RETURNS int AS BEGIN --DECLARE @StartTime time(0) --DECLARE @EndTime time(0) -- Declare the return variable here DECLARE @Shift1StartTime time(0) DECLARE @Shift2StartTime time(0) DECLARE @Shift3StartTime time(0) DECLARE @Shift1EndTime time(0) DECLARE @Shift2EndTime time(0) DECLARE @Shift3EndTime time(0) DECLARE @HrsShift1 decimal(18,2) DECLARE @HrsShift2 decimal(18,2) DECLARE @HrsShift3 decimal(18,2) DECLARE @ShiftDiff int --SET @StartTime = '09:00:00' --SET @EndTime = '13:00:00' SET @Shift1StartTime = '08:00:00' SET @Shift2StartTime = '16:00:00' SET @Shift3StartTime = '00:00:00' SET @Shift1EndTime = '16:30:00' SET @Shift2EndTime = '00:30:00' SET @Shift3EndTime = '08:30:00' --SELECT DATEDIFF(HH, @Shift1StartTime, @EndTime) -- hours are between shift 3 and shift 1 if DATEDIFF(HH, @Shift1StartTime, @StartTime) < 0 AND (DATEDIFF(hh, @Shift1StartTime, @EndTime) < 8.0 AND DATEDIFF(hh, @Shift1StartTime, @EndTime) 0) begin --PRINT 'Shift 3-1 step1' SET @HrsShift3 = DATEDIFF(HH, @StartTime, @Shift1StartTime) SET @HrsShift1 = DATEDIFF(HH, @Shift1StartTime, @Endtime) --PRINT @HrsShift3 --PRINT @HrsShift1 -- get shift with most hours if @HrsShift3 @HrsShift1 begin SET @ShiftDiff = 3 end else begin SET @ShiftDiff = 1 end end -- hours are in shift 1 if (DATEDIFF(HH, @Shift1StartTime, @StartTime) = 0 AND DATEDIFF(HH, @Shift1StartTime, @EndTime) <= 8) OR (DATEDIFF(HH, @Shift1StartTime, @StartTime) 0 AND DATEDIFF(HH, @Shift1StartTime, @EndTime) <= 8) begin --PRINT 'Shift 1 step2' SET @HrsShift3 = 0 SET @HrsShift1 = DATEDIFF(HH, @StartTime, @EndTime) --PRINT @HrsShift3 --PRINT @HrsShift1 -- only one shift with hours SET @ShiftDiff = 1 end -- hours are between shift 1 and shift 2 if DATEDIFF(HH, @Shift2StartTime, @StartTime) < 0 and (DATEDIFF(HH, @Shift2StartTime, @EndTime) < 8.0 AND DATEDIFF(HH, @Shift2StartTime, @EndTime) 0) begin --PRINT 'Shift 1-2 step1' SET @HrsShift1 = DATEDIFF(HH, @StartTime, @Shift2StartTime) SET @HrsShift2 = DATEDIFF(HH, @Shift2StartTime, @Endtime) --PRINT @HrsShift1 --PRINT @HrsShift2 -- get the shift with most hours if @HrsShift1 @HrsShift2 begin SET @ShiftDiff = 1 end else begin SET @ShiftDiff = 2 end end -- hours are in shift 2 if (DATEDIFF(HH, @Shift2StartTime, @StartTime) = 0 AND DATEDIFF(HH, @Shift2StartTime, @EndTime) <= 8) OR (DATEDIFF(HH, @Shift2StartTime, @StartTime) 0 AND DATEDIFF(HH, @Shift2StartTime, @EndTime) <= 8) begin --PRINT 'Shift 2 step2' SET @HrsShift3 = 0 SET @HrsShift1 = DATEDIFF(HH, @StartTime, @EndTime) --PRINT @HrsShift3 --PRINT @HrsShift1 -- only one shift with hours SET @ShiftDiff = 2 end -- hours are between shift 2 and shift 3 - overnight shift if DATEDIFF(HH, @StartTime, @EndTime) < 0 begin --PRINT 'Shift 2-3 step1' SET @HrsShift2 = DATEDIFF(HH, @StartTime, '23:59:59') + DATEDIFF(HH, '00:00:00', '00:30:00') SET @HrsShift3 = DATEDIFF(HH, '00:30:00', @EndTime) --PRINT @HrsShift2 --PRINT @HrsShift3 -- get the shift with most hours if @HrsShift2 @HrsShift3 begin SET @ShiftDiff = 2 end else begin SET @ShiftDiff = 3 end end -- hours are in shift 3 if (DATEDIFF(HH, @Shift3StartTime, @StartTime) = 0 AND DATEDIFF(HH, @Shift3StartTime, @EndTime) <= 8) OR (DATEDIFF(HH, @Shift3StartTime, @StartTime) 0 AND DATEDIFF(HH, @Shift3StartTime, @EndTime) <= 8) begin --PRINT 'Shift 3 step2' SET @HrsShift2 = 0 SET @HrsShift3 = DATEDIFF(HH, @StartTime, @EndTime) --PRINT @HrsShift2 --PRINT @HrsShift3 -- only one shift with hours SET @ShiftDiff = 3 end RETURN @ShiftDiff; END`

    Read the article

  • Lost Update Anomaly in Sql Server Update Command

    - by Javed
    Hi, I am very much confused. I have a transaction in ReadCommitted Isolation level. Among other things I am also updating a counter value in it, something similar to below: Update tblCount set counter = counter + 1 My application is a desktop application and this transaction happens to occur quite frequently and concurrently. We recently noticed an error that sometimes the counter value doesn't get updated or is missed. We also insert one record on each counter update so we are sure that records have been inserted but somehow counter fails to update. This happens once in 2000 simulaneous transactions. I seriously doubt it is a lost update anomaly I am facing but if you look at the command above, it's just update the counter from its own value: if I have started a transaction and the transaction has reached this statement, it should have locked the row. This should not cause lost update, but it's happening somehow. Is the thing that this update command works in two parts? Like first it reads the counter value (during which it doesn't get the exclusive lock) and then writes the new calculated value (when it does get an exclusive lock)? Please help, I have got really confused.

    Read the article

  • SQL complex Question

    - by santanu
    Hello All, I need to find a function. the scenario is : I have 2 table. 1. news table 2. news_comment table need to find the most commented 20 news. the news_comment table has the relationship with the news table Thank you

    Read the article

  • SQL-Calculate percentages from database table values

    - by Howard
    Hi, Im trying to calculate the percentages of selected fields from tables. Within the fields that data is numeric but I want to show the percentage value. Please help. private void btnpics_Click(object sender, EventArgs e) { try { myCon.Open(); string queryString = "SELECT FoodType.Description,FoodType.Calories, FoodType.Carbohydrate, FoodType.Fat, FoodType.Protein FROM [FoodType], [Meal] WHERE (Meal.UserID =" + userid.Text + ") AND (Meal.MealDate =" + date.Text + ");"; MessageBox.Show(queryString); loadDataGrid(queryString); } catch (Exception ex) { MessageBox.Show(ex.Message); } }

    Read the article

  • LINQ to SQL: To Attach or Not To Attach

    - by bradhe
    So I'm have a really hard time figuring out when I should be attaching to an object and when I shouldn't be attaching to an object. First thing's first, here is a small diagram of my (very simplified) object model. Edit: Okay, apparently I'm not allowed to post images...here you go: http://i.imgur.com/2ROFI.png In my DAL I create a new DataContext every time I do a data-related operation. Say, for instance, I want to save a new user. In my business layer I create a new user. var user = new User(); user.FirstName = "Bob"; user.LastName = "Smith"; user.Username = "bob.smith"; user.Password = StringUtilities.EncodePassword("MyPassword123"); user.Organization = someOrganization; // Assume that someOrganization was loaded and it's data context has been garbage collected. Now I want to go save this user. var userRepository = new RepositoryFactory.GetRepository<UserRepository>(); userRepository.Save(user); Neato! Here is my save logic: public void Save(User user) { if (!DataContext.Users.Contains(user)) { user.Id = Guid.NewGuid(); user.CreatedDate = DateTime.Now; user.Disabled = false; //DataContext.Organizations.Attach(user.Organization); DataContext.Users.InsertOnSubmit(user); } else { DataContext.Users.Attach(user); } DataContext.SubmitChanges(); // Finished here as well. user.Detach(); } So, here we are. You'll notice that I comment out the bit where the DataContext attachs to the organization. If I attach to the organization I get the following exception: NotSupportedException: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported. Hmm, that doesn't work. Let me try it without attaching (i.e. comment out that line about attaching to the organization). DuplicateKeyException: Cannot add an entity with a key that is already in use. WHAAAAT? I can only assume this is trying to insert a new organization which is obviously false. So, what's the deal guys? What should I do? What is the proper approach? It seems like L2S makes this quite a bit harder than it should be...

    Read the article

  • SQL Query to retrieve highest item up to a point in a group

    - by James
    The best way of describing this is I have a table of people with their names and ages. Assume that people with the same surname are from the same family. I need a query in oracle which will retrieve a list of the oldest person in each family, but not older than a certain age. Table: person name surname age =============================== James Smith 23 Sarah Powell 17 Barry Smith 31 Mark Smith 35 Mary Smith 18 Bob Powell 30 How do I retrieve the oldest person in each family under 30? Results I'm after name surname age =============================== James Smith 23 Sarah Powell 17

    Read the article

  • Linq-to-sql Compiled Query is returning result from different DataContext

    - by Vladimir Kojic
    Compiled query: public static Func<OperationalDataContext, short, Machine> QueryMachineById = CompiledQuery.Compile((OperationalDataContext db, short machineID) => db.Machines.Where(m => m.MachineID == machineID).SingleOrDefault()); It looks like compiled query is caching Machine object and returning the same object even if query is called from new DataContext (I’m disposing DataContext in the service but I’m getting Machine from previous DataContext). I use POCOs and XML mapping. Revised: It looks like compiled query is returning result from new data context and it is not using the one that I passed in compiled-query. Therefore I can not reuse returned object and link it to another object obtained from datacontext thru non compiled queries. I’m using unit of work pattern : // First Call Using(new DataContext) { Machine from DataContext.Table == machine from cached query } // Do some work // Second Call is failing Using(new DataContext) { Machine from DataContext.Table <> machine from cached query }

    Read the article

< Previous Page | 351 352 353 354 355 356 357 358 359 360 361 362  | Next Page >