In database, I have a LastEditTime column of type datetime. I used this to track last row update/insert time. Linq throws exception claiming that it can not insert null at that column. Naturally, since LastEditTime column is NOT NULL, an exception is expected. My hand generated query inserts getutcdate(). How can I ask Linq to do simila...
I would like to generate a line number for each line in the results of a sql query. How is it possible to do that? Example: In the request select distinct client_name from deliveries I would like to add a column showing the line number I am working on sql server 2005. ...
I am writing a stored procedure generator and I need to map CLR types to their SQL Server types. MSDN lists the type mappings at: http://msdn.microsoft.com/en-us/library/ms131092.aspx but I don't want to use a big switch statement to handle the mappings. Is there a simple way to retrieve the SQL Server type as a string using whatever p...
Weblogic documentation indicates that version 8.1 of Weblogic Supports MS SQL 2000 and MS SQL 2005. Has anyone used weblogic 8.1 with MS SQL 2008 ? If so, any "ifs" and "buts" to be aware of ? ...
when i run queries to the database im constantly getting this System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Generated: Tue, 18 Aug 2009 08:05:39 GMT System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledExcept...
Hi everybody I'm working with 2 related tables in a Microsoft SQL Server 2008 environment which are connected via a GUID. In one table, the field has the type varchar(50), the other one is properly types as uniqueidentifier. This is obviously bad but I can't change this now because it's given by a legacy piece of software. The conversi...
I use system stored procedures all the time, but always need/want to filter or sort the results of the sproc. I typically use a table variable for this, but defining the fields/datatypes for the table variable is a pain. For instance... DECLARE @SpWhoResults TABLE ( spid INT, STATUS VARCHAR(100), ...
I have recently read about how cursors should be avoided. Well, I would like to know if my usage of them is appropriate. I am creating a scripting engine that will work online(embedded in a page, server-side) This scripting engine will be used by "advanced" end users of this product. The product works very heavily with the database howe...
Good morning all. I have an issue with a query. I want to select something in a query only if another field is somethingelse. The below query will better explain select Case isnull(rl.reason,'Not Found') When 'D' then 'Discontinued' When 'N' then 'Not Found' When 'I' then 'Inactive' When 'C' then 'No Cost' When '' then 'Not ...
In Sql Server 2005, what is the difference between a database user account that is mapped to an active directory group vs. a database role that is contains the same active directory group as a member? ...
I have a database with hundreds of tables. I am building a script to delete all of the rows in this database. Of course, being a relational database, I have to delete rows from the children before I can touch the parents. Is there something I can use for this or do I have to do this the hard way? EDIT Accepted Answer was modified t...
Out of curiosity, does a stored procedures have the ability to delete a file from the OS? If not I will have to make a windows Batch file that deletes the file and then runs the stored procedure using OSQL. ...
I know how to look up this week number: (SELECT DATEPART(wk, GETDATE())) I need to know the syntax of getting the week number compare to another table: SYNTAX: SELECT THISWEEK -- WEEK NUMBER DATA FROM dbo.DATETABLE WHERE THISWEEK = (DATEPART(wk, GETDATE())) -- THIS IS THE PART I AM NOT SURE. ...
Ok, so I know you should use prepared statements to avoid injection attacks as well as for the huge speed improvements, but there is still one place that I find myself doing string manipulation to build my sql query. Suppose I have a search feature that consists of six or seven or n dropdown lists. Based on whether there is a default v...
I am coding facebook kind request page for a project. I am stuck with grouping requests in all requests printed page. my sql as SQL = "SELECT R.REQUESTID, R.BYID, R.TOID, R.TYPE, R.DATEENTERED, M.MEMBERID, M.FIRSTNAME, M.LASTNAME" SQL = SQL & " FROM REQUESTS R, MEMBERS M" SQL = SQL & " WHERE R.ACTIVE = 1 AND R.TOID = "& Session("MEMB...
Hello, I'm using .NET 3.5 SP1. I have a stored procedure returning a result of a transaction as shown below: Create PROCEDURE SetPrice @itemId int, @price int AS DECLARE @myERROR int -- Local @@ERROR , @myRowCount int -- Local @@ROWCOUNT SET NOCOUNT ON BEGIN TRAN1 UPDATE item_price_table SET price=@price WHERE...
I have a need to take in a list of ID numbers corresponding to a member. Their can be anywhere from 10 to 10,000 being processed at any given time. I have no problem collecting the data, parsing the data and loading it in to a DataTable or anything (C#) but I want to do some operations in the database. What is the best way to insert a...
Is it possible to use the CONTAINSTABLE and NOT keywords with SQL Server full-text searching and exclude rows where a single column contains the value to be excluded? For example, let's take the following indexed view (simplified for the purposes of illustrating this problem): ItemId INT FirstName VARCHAR(200) MiddleName VARCHAR(200) L...
When I do for example DATEPART(mm, GETDATE()) I get the result of the month being "8" for August. If i did the same thing in December I would get "12". Those are two different length results. Is there a way to get the DATEPART results to always be a fixed length? So that for example the months would show up as 08 or 12. And days would b...
I'm trying to see what connection protocol a given connection to sql 2008 is using (be it shared memory, named pipes, or tcp/ip)...I'm not seeing this info in the sql activity monitor. Is there some place I can view this? ...