Hey Stackoverflow. Say I have a table with the following layout: Id Int PRIMARY KEY IDENTITY DateFrom datetime NOT NULL DateTo datetime NOT NULL UserId Int UserId is a foreign key to a users table, and well obviously there's more data which is irrelevant to this question. What I want to do, is to make sure that per User, there can ...
I'd like to send an email for each row of a result set using sp_send_dbmail. What is the appropriate way to accomplish this without using loops? Edit: I'm not insisting a loop is not proper here, but is there a set based way to do this. I've tried creating a function, but a function cannot call a stored proc inside it. Only anoth...
How do I retrieve a date from SQL Server in YYYY-MM-DD format? I need this to work with SQL Server 2000 and up. Is there a simple way to perform this in SQL Server or would it be easier to convert it programatically after I retrieve the result set? I've read the CAST and CONVERT on Microsoft Technet, but the format I want isn't listed a...
Hi I have a table in SQL Server 2005 which has a date time field. I want to save the date time value in mm/dd/yyyy hh:mm:ss format, but I guess SQL Server allows date time in yyyy-mm-dd HH:mm:ss.ll format. I can save date time value in mm/dd/yyyy hh:mm:ss format as a varchar, but that defeats my intention of sorting the table on that d...
I've read around the subject of temporary tables and scope and all the answers i've seen don't seem to talk about one of my concerns. I understand that a local temporary table's scope is only valid withing the lifetime of a stored procedure or child stored procedures. However what is the situation with regard to concurency. i.e. if i ha...
I have a @variabletable simply defined as EOMDate(datetime), DandA(float), Coupon(float), EarnedIncome(float) 04/30/2008, 20187.5,17812.5,NULL 05/31/2008, 24640.63, 22265.63, NULL 06/30/2008, 2375, 26718.75,NULL What I am trying to do is after the table is populated, I need to go back and calculate the EarnedIncome field to populate i...
I store events in SQLServer 2005 where the time the event occured is important and must be stored in the datebase. What is the fastest way to write the date range check in the where clause to ensure everything on that day is selected? Currently when @DateStart and @DateEnd are passed in I set @DateStart to midnight and set @DateEnd to t...
Hello, I have a table in SQL Server 2005 which has approx 4 billion rows in it. I need to delete approximately 2 billion of this rows, but if I try and do it in a single transaction, the transaction log fills up and it fails. I don't have any extra space to make the transaction log bigger, so I assume the best way forward is to batch up...
I am writing a scheduled job to mimic a SQL Server maintenance plan for SQL Express. (I have to do this because SQL Agent and related tools don't exist for SQL Express) One of the steps is to do a database integrity check. The TSQL for this is: DBCC CHECKDB(N'Northwind') WITH NO_INFOMSGS How do I know if an error occurred during ex...
Before changing database schema I issue: ALTER DATABASE SET RESTRICTED_USER On completion: ALTER DATABASE SET MULTI_USER I understand that a running transaction will be permitted to continue until completion. Q: Is there any way to wait till all regular users are off the database? Q: Can the regular users issue more transactions?...
Is there a clean and simple method of formatting an integer which is a count of a whole number of minutes into a decimal represnetation of hours and minutes. It's a great shame that there is not such thing as a timespan in T-SQL to support this. Just to be clear what i mean by this is if i have 70 minutes, i want to covert it into a re...
I have a table with 5 columns in it, what's the easiest way to select all rows, but where each column is individually randomised? All I can think of is to select each column separately, along with row_number over ( order by newid()) as lookup and then join each column back together on lookup. Is there an easier way? Thanks ...
I'm currently running the follow code segment in a java servlet using the suggested PareparedStatement class. It is currently inserting inventory data into a SQL Server database of an accounting system. I have two problems. Because the insert statements insert data into 3 different tables, should I move these sql statements to tsql ...
This came up at the office today. I have no plans of doing such a thing, but theoretically could you write a compiler in SQL? At first glance it appears to me to be turing complete, though extremely cumbersome for many classes of problems. If it is not turing complete, what would it require to become so? Note: I have no desire to d...
Hi There, What is the best way to create databases on C# application, I mean by a click of a button, it will create a database with a different name but the same model? Currently, I script TSQL onto a C# application, so when I click a button, a new database will be created with the name I defined, it works well, however, it is extremel...
I have tried to perform update on table which was trigger by update on other table and I got error message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows. For example I have this tables: table_1 =========== int id (primary_key,identity) nchar(10) state_name table_2 =========== in...
I am a bit lost as to how to explain this, so I will try to give an example of some tables (+ data) and then the result that I am after (all my table columns are NOT NULL): Table: Customers Id int primary key Name varchar(250) Table: Stats (Date, CustomerId is the primary key) Date (date) CustomerId (int) - foreign key to Customers t...
I'm writing an export function, where I need to export contacts to Excel, and I've run into a technical snag - or perhaps a gap in my SQL skills is closer to the truth. ;) Here's the scenario: I've got a bunch of contacts in a database. Each contact can have many different roles, for example a contact can be both C# Developer and DBA, o...
I'm running a T-SQL script to migrate old data to our new DB. It's taking to long to process! Right now it's more than 2 and a half hours. Is there a way to check the status of the executing script? ...
I have a Sales table, with the following columns: employeeID amount date Now I want to SUM up the last 15 rows, so I am currently doing: SELECT TOP 15 SUM(amount) FROM Sales ORDER BY [Date] DESC But I get 15 rows obviously, is there a way I can sum it up and not have to loop through and SUM it on the client side? ...