Hi folks, I have the following sql statement, which works perfectly fine. I was hoping to see how this could be refactored so it doesn't require the use of RANK/PARTITION ... if possible. SELECT LogEntryId, FileId, CreatedOn, EventTypeId FROM (SELECT a.LogEntryId, a.FileId, a.CreatedOn, a.EventTypeId, RANK() OVER (PARTITION B...
Hello, I have a column in a SQL table that is the times that measurements were made. As shown in Table1 below, the measurement times can be out of sequence, repeated, or irregularly sampled (they are generally but not always 100 milliseconds apart). When the time stamp is missing (like 2009-12-20 11:59:56.1 in the example below), that...
Hey, I'm thinking of using ASP.NET and MySQL together on a large project. Does anyone have any experience in using these two on a large scale and are there any aspects I should be wary of? If there is a chance that there could be compatibility issues, then I may be prepared to purchase an MSSQL license, but I'd rather go the free route...
I'm using the usp_RethrowError ( given as example in Using TRY...CATCH in Transact-SQL article on technet.microsoft site ) when signaling something is wrong. Is there any way to get the query which triggers this error inside the usp_RethrowError procedure ? I would also like to add the query text to the @ErrorMessage. You can find bel...
suppose i have two tables. articles and comments. when i am selecting columns from articles table, i also want to select the number of comments on the article in the same select statement... (suppose the common field between these two tables is articleid) how do I do that? I can get it done, but I do not know if my way would be effici...
Hi all, I need help in auto populating the primary key values in foreign key table while inserting data in foreign key table. For Example: I have created table: create table Patient ( PatientId int IDENTITY(1,1) primary key, FirstName varchar(50), SurName varchar(50), Gender char(20), ) Say 5 rows are there in this Patient Table: Say...
I have an application that make a copy from my database by bulk copy class in c#. Can I rollback the bulk copy action in sql server when occur an exception? ...
Using VB.Net and SQL 2005 Table1 Name FromDate ToDate Sched1 20091201 20091231 Sched2 20090515 20090613 ..., In table2 i want to add a time for a date between the FromDate and ToDate Table2 Name Date StartTime EndTime Sched1 20091201 080000 120000 ..., In my application am using "AddTime" Button. In the click event ...
Using VB.NET and SQL Server 2005 In DataGridView n number of rows was displaying. Suppose I edit some value in the datagridview cell then I click save means it should update in the table also. Suppose I click add button means it should add a new row in datagridview. How to write a code for this? ...
Is it possible to use a SQL query that returns XML, in order to bind a TreeView control to SQL data? In other words, I would like to set up a query that presents table data as hieracrhical XML, and bind this XML to the TreeView. ...
I'm using ADO in a JScript (Microsoft JavaScript dialect) Windows Scripting Host script to update a SQL Server table. I'd like to get the number of rows affected by the update in the script, but JavaScript doesn't have pass-by-reference and so I can't do the usual thing where I receive the records affected from the Command#Execute functi...
Using SQL Server 2005 When i insert the date it should compare the date in the table. If it is equal with other date, it should display a error message and also it should allow only to insert the next date. For Example Table1 Date 20091201 20091202 Insert into table1 values('20091202') The above query should not allow to insert...
I'm currently hand-writing a DAL in C# with SqlDataReader and stored procedures. Performance is important, but it still should be maintainable... Let's say there's a table recipes (recipeID, author, timeNeeded, yummyFactor, ...) and a table ingredients (recipeID, name, amount, yummyContributionFactor, ...) Now I'd like to query like...
I'm not a DBA ("Good!", you'll be thinking in a moment.) I have a table of logging data with these characteristics and usage patterns: A datetime column for storing log timestamps whose value is ever-increasing and mostly (but only mostly) unique Frequent-ish inserts (say, a dozen a minute), only at the end of the timestamp range (new...
I have dynamic generated XML files -----Example1----- <items> <param1>aaaa</param1> <param2>bbbb</param2> <param3>cccc</param3> </items> -----Example2----- <items> <test1>dddd</test1> <test7>eeee</test7> <john1>ffff</john1> <john2>ffff</john2> </items> I want to convert this xml files to table like this -----SQL TABLE for Example1---...
Let's say I have information in a table that is in the wrong database (like Customer info in the Items database). I really want to move this information, but I need to figure out who's using it. If I use this code to search for the table name, say CustomerContactNumbers, is there any possibility of things slipping by? I'm going to ignore...
Hello , I need to use queue mechanism . I have read some articles about msmq . It seems that I have to send data to msmq from sql by using triggers when a data is inserted to sql table an insert trigger pushes the data to msmq. Lets assume that I have stopped msmq service for a minute. At that time I have inserted data to sql table. As ...
Hey I have a column in one of my tables which is suppose to be the total sum for from the rows of a number of tables. Is there a way i can have a default query which runs on the total sum column so that every time a row is added to the other table an update is made in the total sum column. Thanks ...
I am trying to write an installation program. I have completely automated the SQL Server 2008 Express installation (silently), using the Command Line install process as described here (http://msdn.microsoft.com/en-us/library/bb264562(SQL.90).aspx). It works really well, but my problem now is that I need SQL Server 2008 Express with Adva...
Hi, I want to count the total number of order detail rows over all orders a customer has ever had. This is my query SELECT SUM( (SELECT count(*) FROM dbo.Order_Details WHERE dbo.Order_Details.OrderID = dbo.Orders.OrderID)) FROM dbo.Orders WHERE dbo.Orders.CustomerID = "123" SQL Server is ...