I am using trigger to trace DDL changes but when I change column name from the SQL Server Management Studio the trigger is not working? create TRIGGER trgLogDDLEvent ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS FOR AS DECLARE @data XML SET @data = EVENTDATA() IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') <> 'CREATE_ST...
Hi guys, How can I debug TSQL on SQL Server 2005 instance? Thanks. ...
Hi, Can I add a column which is I specify as NOT NULL,I don't want to specify the DEFAULT value but MS-SQL 2005 says: "ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditi...
I have a table with records for each zip code in the united states. For the purposes of displaying on a map, I need to select X random records per state. How would I go about doing this? ...
Hey all I am trying to combine my data into one sum. This is my output right now: Amount --------- $258.0 $400.0 $1011.0 $628.0 $628.0 $340.0 $340.0 $1764.0 of course the total would be $5369. This is the type of output I need Description | Quantity | Price | Amount -------------------------------------------- Fees 8 ...
I need to run a simple select statement for a column called AddrZip to show all records that contain '1/2 ' after the first space in the column. In Access 2007 it would be: **Left([Names],InStr(1,[Names]," ")-1), but can't find out how to do it in SQL 2005. All help will be appreciated. ...
Hey all, I am wondering how to set variables to the output of the following query string: SELECT count(*) fees_quantity, 1.5 fees_price, 1.5 * count(*) fees_amount, round(SUM((CONVERT(int,Points) * .1)),0)) redep_amount, round(SUM((CONVERT(int,Points) * .1)),0)) + 1.5 * count(*) total_amount FROM tblHGP HGP, OrderDetail...
So I've created a trigger that compares update before and after and determines if specific fields, specified in the where clause, have changed. If so, I insert a snapshot of the prior information into a history table. The problem is when the field is created with null values (given the business rules, it is legitimately null) and when ...
duh, I figured it out, but I can't delete my own post sorry I'm trying to use the XML concatination trick in a query. but, I'm getting this error: SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on ...
I have a column of user names. They are as follows: 'first last middleinitial' Notice the large spaces between the name parts, these are always a different number of spaces. Question: How would I separate first, last, and middleinitial into separate columns (even if the spaces are different for every name)? ...
I have a question similar to this but in the context of L2S. I want to create a lookup table to store values that could be one of several possible datatypes. As suggested in the referenced question, I could use sql_variant datatype. However, L2S maps sql_variant to Object, which is suboptimal. I'm guessing it's possible to get at the tab...
SQL Server 2005 supports CLR so it means we can use CLR in backend so how to do that, I have some function in c# which do some complex manipulation with date-time variable now I want to use those functions in SP. First of all IS IT POSSIBLE TO DO THIS. ...
By some setting as using IDENTITY_INSERT we have duplicate entry in identity column, What is the best method of removing duplicate entry. I have a table Details With column DetailID | FKey | Col1 | Col2 | Col3 | Col4 DetailID is "Identity" and FKey is foreign key with another table. Now we have already 240000 record. Some one used "I...
I want to create a computed column in SQL SERVER and set the formula to this ([Category] + '.aspx?ID=' + [Post_ID]) Not working though......what am i missing? Category and Post_ID are current columns in the table ...
I have an id field (int type) and varchar field. I have to concatenate both columns and store the result to another column with data type nvarchar; Is this possible? ...
Is .Skip().Take() the only way to get paging in Entity Framework? Is there some way to select the row number in the output so I could use Where( p => p.row > 9 && p.row <21)? select top 100 ROW_NUMBER() over (order by ID) as row, * from myTable I would think the ROW_Number() field must exist in the generated SQL for it to know what ...
I want to create a two stored procedures that will remove records older than N days from a stgging database. Both stored proc will call in a package to cleanup data older than N days. Stored proc A will remove records from table A.Fullimport & stored proc B will remove records from table B.weeklyimport. The same stored proc will remov...
Hi all, Have created a stored procedure which is utilised for monitoring purposes of a website. On first run the procedure takes over a minute to execute and if run shortly after this it takes only a few seconds to run. The problem is that the script is scheduled to run at ten minute intervals and each time it runs, it takes over a mi...
How can i convert the datetime format below 2010-10-25 11:13:36.700 into 25-Oct-2010 or 2010-10-25 00:00:00.000 ...
i am trying to do as below its giving syntax error please help DELETE FROM table WHERE col1 = 2 AND EXISTS ( SELECT COUNT(*) FROM table WHERE col1 = 3 ) > 2 ; i need to do a delete only if the cout is greater than 2 ...