Programming SQL

Alternatives to cursors
I have been requested to create a job which will display which SQL Server Agent jobs have been disabled. I would create the job based a stored procedure. The stored procedure I am creating would run in msdb and access the tables sysjobs and sysjobhistory. The only way I can see doing it is opening a cursor base... 2 Jun 2010 19:50

Insert Trigger on a BulkCopy
Hi, I think i have most of the kinks worked out. I am doing a sqlbulkcopy from a vb.net application. I am putting the data in Table2 and on Table2 I have an after insert trigger that needs to insert or update data in Table1. Only problem is.. with bulkcopy I need to process one row at a time with the trig... 2 Jun 2010 18:43

Merge membership
I have a table called membership which contains memberid, effdat and expdat. When a member extend the membership on time, the next effective date would start right after previous expiration date (the next day), thus there is no break in membership. However, if a membership does not extend on time, the next ef... 2 Jun 2010 19:50

Selecting largest for each group
I have the following table with no primary key. CREATE TABLE Sales ( SalesPersonID INT NOT NULL, FirstName CHAR(15) NULL, LastName CHAR(15) NULL, Gender CHAR(1) NULL CHECK (Gender IN ('M','F')), ... 3 Jun 2010 10:03

UPDATE in UDF
Is it possible to run an UPDATE statement in a user defined function? I am creating a UDF that determines the amount of a payroll deduction. Some deductions have a balance (total) to deduct over time. If a deduction is a balance deduction then I want to be able to reduce the balance by the amount of the dedu... 3 Jun 2010 20:10

scope_identiry
Hi, Taken from the example here: http://msdn.microsoft.com/en-us/library/ks9f57t0(VS.71).aspx I have this procedure defined: ---------------------------------- create procedure sp_insert_get_id @sql varchar(3000), @identity int output as execute (@sql); set @identity = scope_identity(); go ---------... 4 Jun 2010 02:43

ALTER DATABASE [...] SET COMPATIBILITY_LEVEL = 100 ---- giving err
The following statement came from a sample project I downloaded from Codeproject - sample illustrating WCF, MVC with asp.net (sql code in the project to set up a database for the sample project) ALTER DATABASE [BugTracker] SET COMPATIBILITY_LEVEL = 100 GO I get this error: Msg 102, Level 15, State 1, Line 1... 2 Jun 2010 18:43

Partition Handling
I didn't get an answer in replication so I am posting this here: We've got some big SS 2000-style partitioned views using traditional horizontal partitioning that we want to migrate to the new SS 2005 partitioning. The underlying tables to the view are partitioned by date in all cases and so each view holds 3-... 2 Jun 2010 12:03

Must declare the scalar variable "@myDB@"
I am encountering this error and I am not sure why. In my code I create the sql string and SQLParameter to deal with the SQL variables. I am not strong with SQL so I may be missing something here. Any advice is greatly appreciated. Web.config: <add key="myDB" value="DBServ1" /> <add key="myInfo" v... 2 Jun 2010 08:45

IDENTITY INSERT issue with INSERT INTO..SELECT
Hi, Here's my DML statements that duplicates records from a resultset qualfying to GESPRAECH_ID = @Quell_Gespraech_ID Column [TB_GESPRAECHPARTNER].POS_ID has its property IDENTITY set to true. SET IDENTITY_INSERT [TB_GESPRAECHPARTNER] ON INSERT INTO [TB_GESPRAECHPARTNER] ([GESPRAECH_ID] ... 2 Jun 2010 07:39