We have a small affiliate program and a simple tool to view affiliate statistics. The Linq for showing the report: from e in linq0 select new { Id = e.Id, Name = e.CompanyName, EnquiryCount = (int?)e.CampaignCodes.Sum(f => f.Enquiries.Count()) ?? 0, EnquiryOrderSum = (int?)e.CampaignCodes.Sum(f => (int?)f.Enquiries.Sum...
I have seen many examples of the HOLDLOCK hint being used in combination with UPDLOCK (like this). However as I read the documentation for these hints, it seems that HOLDLOCK should be redundant, since UPDLOCK already persists the lock until the end of the transaction. (Also it seems to say that HOLDLOCK only applies to shared locks anyw...
Due to an added field in a MsSql Database, I have to change the records in a table from NULL to something more usefull. I've got a table with addresses. Because customers have multiple addresses we'd like to mark certain addresses as default and others as something like shipping. How can I, using a query, change only the first of ever...
Let's say I have a custom class like this one: public class Customer { public int CustomerID { get; set; } public string CompanyName { get; set; } public string BusinessAddress { get; set; } public string Phone { get; set; } public int ParentID { get; set; } } I create custom objects from the...
How to display a time HH:MM format? Using SQL 2000 In my Database time column datatype is varchar Example Table1 Time 08:00:00 09:00:23 214:23:32 Here I want to take only 08:00, 09:00, 214:23 How to make a query for this condition? ...
Hi guys. Started getting this error it seems since we upgraded to SQL Server 2008. When inserting into the db and then returning the identity i get a 'Item cannot be found in the collection corresponding to the requested name or ordinal' error. Here is the code: SQL = "INSERT INTO PageFeatures(nPageFeatureFlagId,nPageFeatureFeatureId...
I don't seem to find a tool for it, nor an odbc driver. Thanks UPDATE : I'm aware of the sql scripting possibilities. But than again : how to script a sql 2k table? (not just ddl, but data also?) Of course you can write this all by yourself, but importing data into CE cannot be such a hassle, or can it ? UPDATE2 : I don't seem to...
I want to write an SQL Server 2005 stored procedure which will select and return the user records from the user table for some userids which are passed to the stored procedure as parameter. How to do this ? I can pass the user ids as a string separated by comma. So that i can use the select * from users where userid in (userids) ...
Example table: CREATE TABLE Log ( logID int identity logDate datetime logText varchar(42) ) logID is already indexed because it is a primary key, but if you were to query this table you would likely want to use logDate as a constraint. However, both logID and logDate are going to be in the same order because logDate w...
I'd like to determine the primary key of a table using TSQL (stored procedure or system table is fine). Is there such a mechanism in SQL Server (2005 or 2008)? ...
Using SQL Server 2000 I want to get Table2.TimeIn Table2.TimeOut according to Table1.personid and also If Table1.Date = Table3.Date then it should take a Table3.TimeIn, Table3.TimeOut. 3 Tables Table1 ID Date 001 20090503 001 20090504 001 20090506 002 20090505 002 20090506 So on…, Table2 ID TimeIn TimeOut 001 08:00:...
Hi All, I have a .NET 1.1 application that currently connects to a SQL Server 2000 database server. This SQL Server is about to be upgraded to a SQL Server 2005 server with mirroring enabled. I have been trying to figure out if mirroring (i.e. automatic failover) is supported by .NET 1.1. I know that it is supported through the SQL Serv...
Hi all, I am working on a project where I need to extract data from a MSSQL database table, and then insert the data into a table of the same structure on an AS400. The SqlBulkCopy would be the ideal candidate for this operation, but fails (understandably) because the AS400 uses an ODBC connection. Any help or suggestions on this woul...
Hi I keep getting the error: Error (3709) - /mysite/Pages_Secure/mypage.asp ADODB.Recordset. "The connection cannot be used to perform this operation. It is either closed or invalid in this context.." strQuery = "" strQuery = strQuery + "SET ROWCOUNT 0 " strQuery = strQuery + "SELECT FIRSTNAME, LASTNAME, EMAIL, USER_TEAM_ID, USER_SERV...
Having just started working for a new client I have noticed that their database schema could do with a serious overhaul. I've made some suggestions (naming conventions mainly) which would be acceptable for the new suite of applications we are developing going forward, however the system would also have to support the lagacy names used ...
I have a SQL Server database. One of the tables has a column that, for lack of a better word, is acting corrupted. I can select * or even select that specific column and it works fine. I can perform functions like count, order by and group by on all other columns with no problems. But if I try and do one of those functions on this one co...
Hello, I'm working on a purging procedure on SQL Server 2005 which has to delete all rows in a table older than 1 year ago + time passed the current year. Ex: If I execute the procedure today 6-10-2009 it has to delete rows older than 2008-01-01 00:00 (that is 2007 included and backwards). How can I get the date of the first second ...
Table1 ID Date Intime Outtime A001 20000501 12:00:00 22:00:00 A001 20000502 14:00:00 22:00:00 A001 20000503 12:00:00 23:00:00 A002 20000501 11:00:00 20:00:00 A002 20000502 13:00:00 21:00:00 So on…, Table2 ID Date Intime Outtime A001 20050501 14:00:00 23:00:00 A002 20050501 08:00:00 16:00:00 From the above table I want...
Hello, I have a database with merge replication set up on a SQL 2005 server with 1 subscriber. I am having some identity range issues an I started to look at the MSmerge_identity_range table. I have one subscriber but 2 rows for each. For Example(I shortend the guids but for arguments sake they are the same for each row): subid ar...
I am working on a SQL Job which involves processing around 75000 records. Now, the job works fine for 10000/20000 records with speed of around 500/min. After around 20000 records, execution just dies. It loads around 3000 records every 30 mins and stays at similar speed. I asked a similar question yesterday and got few good suggestions...