Calculating date for a day of the week
Given a week-day (1-7), how can I calculate what that week-day's last date was? Example: Today is Wednesday, 2008/11/12, and I want to know what last Friday's date was. ...
tsql
Sheduled run of stored procedure on MS SQL server
Is it possible to set up somehow Microsoft SQL Server to run automatically a stored procedure on regular basis? ...
t-sql decimal assignment changes value
Why does the select statement return two different values? declare @tempDec decimal set @tempDec = 1.0 / (1.0 + 1.0) select @tempDec, 1.0 / (1.0 + 1.0) ...
SQL Server xp_cmdshell
Is their any other way to get a list of file names via T-SQL other than INSERT INTO @backups(filename) EXEC master.sys.xp_cmdshell 'DIR /b c:\some folder with sql backups in it I am attempting to get a list of sql backup files from a folder to restore and I do NOT want to use the xp_cmdshell for obvious security reasons. ...
How do I add the identity property to an existing column in SQL Server
In SQL Server (in my case, 2005) how can I add the identity property to an existing table coulumn using t-sql? something like: alter table tblFoo alter column bar identity(1,1) ...
SQL Server 2000 "NO JOIN PREDICATE" warning - Why?
I've got a strange problem with SQL Server 2000, and I just can't think of a reason for this to happen. There are two tables, both having a combined primary key with a clustered index on it, both keys have the same structure: (VARCHAR(11), INT, DATETIME) /* can't change this, so don't suggest I should */ So, joining them like this ...
Query to list SQL Server stored procedures along with lines of code for each procedure
I want a query that returns a list of all the (user) stored procedures in a database by name, with the number of lines of code for each one. i.e. sp_name lines_of_code -------- ------------- DoStuff1 120 DoStuff2 50 DoStuff3 30 Any ideas how to do this? ...
Is there a good reason to use upper case for T-SQL keywords?
The default seems to be upper case but is there really any reason to use upper case for keywords? I started using upper case because I was just trying to match what SQL Server gives me whenever I tried to create something, like a new stored procedure. But then, I feel terrible for my baby (5th) finger that always needs to hold down the S...
Creating a long string from a result set
I have a result set in MS-SQL within a stored procedure, and lets say it has one VARCHAR column, but many rows. I want to create a comma separated string conataining all these values, Is there an easy way of doing this, or am I going to have to step through each result and build the string up manually? Preferably I'd like to do this in ...
Inner select on one to many
Hi all, I have 2 tables event + event_artist event eventId | eventName 1 , gig1 2, gig2 event_artist eventId, artistName 1, Led Zip 1, The Beatles ie Led Zep and the Beatles are both playing @ Gig1 I need to create the SQl to bind to a gridview ( you necessarily need to know about gridviews to answers this ) The results that i ...
What is the best way to store historical data in SQL Server 2005/2008?
My simplified and contrived example is the following:- Lets say that I want to measure and store the temperature (and other values) of all the worlds' towns on a daily basis. I am looking for an optimal way of storing the data so that it is just as easy to get the current temperature in all the towns, as it is to get all the temperature...
What is the best query to get the current records in an archive table (SQL Server 2005/2008)
Example There is an application that measures temperature in each town of the world. Each measurement is taken every 5 minutes and written in to the Measurement table. CREATE TABLE [dbo].[Measurement]( [MeasurementID] [int] IDENTITY(1,1) NOT NULL, [Town] [varchar](50) NOT NULL, [Date] [datetime] NOT NULL, [Temp] [int] N...
Using a IF Statement within a TSQL UPDATE
I want to update two columns in a table. The value of the second column is dependant upon the first; If the first is Null the second's value is 'false', otherwise it is 'true'. Can I do this within TSQL or do I need to work out the values separately in my code before hand and change the SQL to suit. I was looking for something like: ...
Convert NVARCHAR to INT skipping non convertibles
I need to query a database which is designed so what a NVARCHAR column keeps age of clients. I want group clients using GROUP BY age / 10 but when engine encounters some string like '20.10.1984' (someone entered his bithday instead of age), my query fails. The percent of "bad" ages is very small, so I want just to skip records which ...
Using an arbitrary number of parameters in T-SQL
Is it possible to create a parameterized SQL statement that will taken an arbitrary number of parameters? I'm trying to allow users to filter a list based on multiple keywords, each separated by a semicolon. So the input would be something like "Oakland;City;Planning" and the WHERE clause would come out something equivalent to the belo...
Best way to periodically remove a set of records with LINQ to SQL
This is my first crack at a method that is run periodically during the lifetime of my ASP.NET application to clean up expired sessions stored in my database. It seems to work pretty well, but the software engineer in me doesn't feel "right" about this code. I've been working with LINQ to SQL for a few months now, but I'm not very confide...
Microsoft SQL Server - What does it mean that a Transaction Log is Full?
What does it mean that a Transaction Log is Full? I have it the file set to grow 20% when needed. I have 4GBs left on the drive. How do I solve this issue permanently? Running these commands solves the issue temporarily: DBCC SHRINKFILE('MyDatabase_log', 1) BACKUP LOG MyDatabase WITH TRUNCATE_ONLY DBCC SHRINKFILE('MyDatabase_log', 1) ...
What are the best practices in writing a sql stored procedure
I found that SQL stored procedures are very interesting and useful. I have written stored procedures but i want to write well crafted, good performance tuned and concise SPs for any sort of requirement and also would love to learn about any tricks or good practices for stored procedures. How do i move from the beginner to the advanced st...
T-SQL distance formula using a radius in miles around a central location
What's the best way to calculate the distance formula around a central location using a radius in miles? We've typically been using a circular radius formula, but are considering using a plain square because it's faster and much more efficient. Sometimes we'll need to be exact, so we'll use the circle, but other times we can just use ...
How do you return a constant from an sql statement?
How do I return a constant from an sql statement? For example how would I change the code below so "my message" would return if my (boolean expression) was true if (my boolean expression) "my message" else select top 1 name from people; I am using ms sql 2000 ...