Programming SQL

T-Sql question
I have a query that produces a list of companies and their effective controls status for the given year: select companyname, iseffective, year from internalcontrols where year > 2004 This produces a list similar to this: ABC Corp. yes 2005 ABC Corp. yes 2006 ABC Corp. yes 2007 ABC... 1 Jul 2008 01:04

Inner Dynamic SQL Single Quotes Issue
Trying to get single quotes are + lvc_Column + in inner Dynamic SQL Loop below. If I add '' to either side, when I execute, I get Wednesday_02 = case when right(' + @lvc_Column + ', 2) = c.Today then c.Course and it should be Wednesday_02 = case when right('Wednesday_02', 2) = c.Today then c.Course ... 2 Jul 2008 19:07

How to copy stored procedures (in SQL 2005)?
I am trying to move sp from SQL2000 to SQL2005. I guess I have to go to do this procedure (below).. "Right click on the database in the object explorer, go Tasks->Generate Scripts. Set Script Object-Level Permission to True, click Next, check only the Stored procedures, you will see a list of SPs after clicking... 30 Jun 2008 23:01

Inner Dynamic SQL update not updating. any thoughts?
use AELN go if (object_id('RMS.upsResourceManagement') is not null) and (objectProperty(object_id('RMS.upsResourceManagement'), 'IsPRocedure') = 1) begin raiserror('Dropping and recreating procedure ''%s''', 0, 1, 'upsResourceManagement') drop procedure RMS.upsResourceManagement end go /*===... 30 Jun 2008 19:57

Deleting from a table variable
Hi all, Trying to delete from a Table variable from a join on 2 tables. Why is this example deleting all rows instead of just what is equal to the where clause? DECLARE @TABLE1 TABLE (numbid INT IDENTITY(1,1) NOT NULL ,dateColumn VARCHAR(10), value DECIMAL(15,2)) INSERT INTO @TABLE1 (dateColumn,valu... 1 Jul 2008 08:13

Inner Dynamic SQL execute doesnt appear to execute.
l I dont think my inner dynamic sql is executing, at least it doesnt look like it in profiler. anyone got any suggestions? And please refrain from providing the default how dynamic sql is bad, just need help. Thank you. use AELN go if (object_id('RMS.upsResourceManagement') is not null) and (objectPropert... 30 Jun 2008 18:56

sql agent JOB to Backup Database and Maintain Backup Files
Hi friends, How can I create ONE SQL Agent JOB That will 2 activities: (Using SQL 2005) 1) Backup a specified Database (Full Backup) and store the backup file in a Specified FOLDER. 2) Manage the Backup Files in the FOLDER, ie. Delete the files that are 5 week OLD. I want to schedule the JOB to run every S... 30 Jun 2008 22:00

unhandled exceptions
Why is it that some errors immediately throw an unhandled exception and others do not ? For example, when attempting a DELETE an unhandled FK violation will immediately throw an exception and exit the sproc ... ( it is not possible to check @@ERROR and gracefully exit ( unless a try/catch is used ) ) ... 30 Jun 2008 15:50

Data formatting question...please help
Hello all, I just started using SQL to create a report. I got the syntax working but I have absolutely no idea of how to format data. Can anyone kindly look at my code and help me? I thank you in advance. Details:- The code: SELECT Column1, Column2, Column3 FROM WORKSHEET Output: Column 1 Colu... 1 Jul 2008 13:22

Where clause ignore 2nd test
Using Sql Server 2005, I have a query with 2 tests. What I want to do is make sure that if the first test is true, the 2nd test is not even done. SELECT @ApprID=ApprID FROM Apprs WHERE (FileName = @FileName OR FileNumber = @FileNumber) In the above, query, I want to make sure that if the test "FileName = ... 30 Jun 2008 22:00