I have the follow T-SQL to update a table with test data: UPDATE SomeTable SET Created = GETDATE ( ) - CAST ( RAND ( ) * 365 AS int ) , LastUpdated = GETDATE ( ) - CAST ( RAND ( ) * 365 AS int ) I want it to pick random daes in the past year, unfortunately it uses the same date for every row. what is the best way to get it to...
Possible Duplicate: Is it possible to send a collection of IDs as a ADO.NET SQL parameter? Duplicate: this is a duplicate of Is it possible to send a collection of ID’s as a ADO.NET SQL parameter? and many others. Please vote to close it and add any additional answers to one of the other duplicates. Given an array of parameters...
Hello everyone, I am using SQL Server 2008. I have a table which has a datetime type column called CreateTime. I need to select all the records from this table whose CreateTime is more than 3 days and half an hour from current time (UTC time). I am using T-SQL store procedure. BTW: The CreateTime column is some time in the past time. ...
I am using SQL Server 2008 and I need to run a SQL Job from SQL Server Agent. I am new to SQL Server Job and I want to execute a stored procedure regularly from a SQL Server Job. But I did not find where to specify the executed stored procedure other than copy & paste SQL commands. Here is my screen snapshot, Any ideas how to assign ...
Hello everyone, I am using SQL Server 2008 and I want to test the execution correctness of my SQL Server Agent job immediately to ignore the schedule. Any ideas? thanks in advance, George ...
Hello everyone, I am using SQL Server 2008 and I am writing a SQL Server Agent Job. In my Job, there are 3 steps and in each step I will execute a store procedure. I noticed that in default Notification setting, it write information to Windows event log only when it fails. My question is, any easy solutions to write both success and fa...
I have the following problem, that I would like to solve with transact-sql. I have something like this Start | End | Item 1 | 5 | A 3 | 8 | B and I want to create something like Start |End | Item-Combination 1 | 2 | A 3 | 5 | A-B 6 | 8 | B For the Item-Combination concatenation I already thought of...
I have 2 regular Tables "Tasks" and "Employees" and one junction table EmployeeTasks simplified they look like this Tasks TaskID ProjectID Duration Employee EmployeeID EmployeeName EmployeeTasks EmployeeID TaskID I am trying to find a tsql query that based on a common project ID would deliver a summary of the total time spen...
Hello everyone, I am using SQL Server 2008 Enterprise. I am using Management Studio -> Jobs -> Script Job as -> Drop and Create TO feature to generate related sql statement so that from another computer I can import the job. My question is, from the generated sql scripts, from the beginning there is output like this, there is a hard co...
Hello everyone, If I know the database server name, instance name and the SQL Server job name, how to delete a SQL Server job by its name in a simple way? I am writing scripts which will be called by sqlcmd to delete SQL jobs. Appreciate if anyone could show me a sample? :-) thanks in advance, George ...
Consider a scenario where you'd like to pull the last x entries from a table. The column we want contains testimonials about a product. For performance reasons, we only want to grab the first 50 characters from the testimonial. The column is named TestimonialText and is of type text. Consider this condensed snippet of T-SQL: SELECT TOP...
Are there any patterns for writing stored procs? like: Should we write 1 Sp for update, 1 for insert and 1 for select for each table. When is it good to create views. How to manage business rules in SP? How to reduce duplicate code etc etc.. Any good article or book to read about these patterns... thanks ...
Hello, Here is my problem. I'd like to get the last inserted Id with a custom sql expression in Linq To Sql. My insert method: public int Add(string Label) { _dbContext.ExecuteCommand("INSERT INTO Products (Label) VALUES (@Label);", Label); _dbContext.SubmitChanges(); var lastId = _dbContext.ExecuteQuery<int>("SELECT Sco...
Hi, just wondering if any of you guys use Count(1) over Count(*) and if there is a noticeable difference for SQL Server 2005 in performance? Or is this just a legacy habit that has been brought forward from days gone past? ...
have a SQL table with 2 columns. ID(int) and Value(ntext) The value rows have all sorts of xml strings in them. ID Value -- ------------------ 1 <ROOT><Type current="TypeA"/></ROOT> 2 <XML><Name current="MyName"/><XML> 3 <TYPE><Colour current="Yellow"/><TYPE> 4 <TYPE><Colour current="Yellow" Size="Large"/><TYPE> 5 <...
MS Access has a button to generate sql code for finding duplicated rows. I don't know if SQL Server 2005/2008 Managment Studio has this. If it has, please point where If it has not, please tell me how can I have a T-SQL helper for creating code like this. ...
According to BOL on value() Method (xml Data Type), value() method takes two arguments XQuery SQLType Do I need to pass varchar or nvarchar to value()? How can I find out what kind of type XQuery or SQLType expects? End Goal: To create utility UDF/sprocs that uses XML Data Type methods. ...
I am trying to selectively delete records from a SQL Server 2005 table without looping through a cursor. The table can contain many records (sometimes > 500,000) so looping is too slow. Data: ID, UnitID, Day, Interval, Amount 1 100 10 21 9.345 2 100 10 22 9.367 3 200 11 21 4.150 4 300 ...
I am creating and maintaining my SQL Server 2008 warehouse database using a Visual Studio 2008 project. When I try to deploy the project, I get an error as below Creating DataWarehouseReports... Company.HigherEducation.DataWarehouse.dbschema(0,0)Error TSD01268: .Net SqlClient Data Provider: Msg 2714, Level 16, State 6, Line 2 There is ...
I have a stored procedure in an old SQL 2000 database that takes a comment column that is formatted as a varchar and exports it out as a money object. At the time this table structure was setup, it was assumed this would be the only data going into this field. The current procedure functions simply this this: SELECT CAST(dbo.member_cate...