sql-server

Fastest way to calculate summary of database field

I have a ms-sql table with the following structure: Name nvarchar; Sign nvarchar; Value int example contents: Test1, 'plus', 5 Test1, 'minus', 3 Test2, 'minus', 1 I would like to have totals per "Name". (add when sign = plus, subtract when sign = minus) result: Test1, 2 Test2, -1 I want to show these results (and update them when ...

sql db problem with windows authentication

Have a SQL Server 2008 db which I connect to the Windows Authentication .. has worked good for 7-8 months .. but now when I come to work today it no longer worked to connect, without that I had done something Error message was: Can not open user default database. Login failed. Login failed for user 'Jimmy-PC \ Jimmy'. where the first...

Search a full text index for 'c#'

Hi I have a table (lets say it has one column called 'colLanguage') that contains a list of skills and has a full text index defined on it. One of the entries in the table is 'c#' but when I search for 'c#' (using the following SQL) I get no results back. select * from FREETEXTTABLE(tblList, colLanguage, 'c#') Can anyone help? Thanks...

Force DATABASE ALTER ignoring errors

As the name says, force the alter database to alter its collation ignoring the error message that I get from functions or calculated columns that compare data. Is there a way to do so? Like IGNORE_ERRORS or something like that? Thanks a lot! ...

need help in aggregate select

Hi, i have a problem with selecting some values from my DB. DB is in design stages so i can redesign it a bit of needed. You can see the Diagram on this image Basically what i want to select is select c.campaignID, ct.campaignTypeName, c.campaignName, c.campaignDailyBudget, c.campaignTotalBudget, c.campaignCPC, c.date, cs.campaignSta...

Minutes to time in SQL Server

I've created a function for converting minutes (smallint) to time (varchar(5)), like 58 -> 00:58. set QUOTED_IDENTIFIER ON GO Create FUNCTION [dbo].[IntToMinutes] ( @m smallint ) RETURNS nvarchar(5) AS BEGIN DECLARE @c nvarchar(5) SET @c = CAST((@m / 60) as varchar(2)) + ':' + CAST((@m % 60) as varchar(2)) RETURN @c E...

Setting up a linked server to another server which isn't in a domain without using SQL authentication?

Server A (SQL2005) is in our primary domain, but server B (SQL2000) is just in a windows workgroup. We are not allowed to join it to the domain, or bad things happen... We also can't enable SQL authentication on server B. We've got domain accounts for A, and matching local accounts on server B. I can connect to B from my local PC o...

I have data about deadlocks, but I can't understand why they occur

I am receiving a lot of deadlocks in my big web application. http://stackoverflow.com/questions/2941233/how-to-automatically-re-run-deadlocked-transaction-asp-net-mvc-sql-server Here I wanted to re-run deadlocked transactions, but I was told to get rid of the deadlocks - it's much better, than trying to catch the deadlocks. So I spen...

SQL Server create a row in Table B for every row in Table A and put the ID in Table A

I know how to create a row in Table B for every row in Table A: INSERT INTO [dbo].[TableB] SELECT 0, 5, 3, [TableAColumn] FROM [dbo].[TableA] But I need to also put the ID of each new TableB row into a column in TableA, and I can't figure out how to do that. [Edit] I forgot to mention that there are 3 TableB IDs referenced to TableA,...

Dynamically call a stored procedure from another stored procedure

I want to be able to pass in the name of a stored procedure as a string into another stored procedure and have it called with dynamic parameters. I'm getting an error though. Specifically I've tried: create procedure test @var1 varchar(255), @var2 varchar(255) as select 1 create procedure call_it @proc_name varchar(255) as ...

Why does SQL Server keep throwing exceptions?

I have my project in .NET that uses a database in SQL Server. I'm using Linq-to-SQL, sometimes when the project throws me an exception (Constraint) in a part of the project this same error keeps showing in other part of the project when I do another thing with the database. Like when I do an insertion and I had before an exception on del...

SQL: How to Return One DB Row from Two That Have The Same Values In Opposite Columns Using the MAX Function?

I have three columns in a table - ID, Column1, Column2 - with this example data: ID Column1 Column2 ---------------------- 1 1 2 2 2 1 3 4 3 4 3 4 Since, in the first two rows, Column1 and Column2 have the same values (but in different columns), I want my MAX query to return an ID of 2. Same thi...

How to do insert one row from one table to another table(what has less columns)?

Edit Ok I solved all my problems but one. Is it possible to have a hardcoded value insert as well. Like I said I am trying to insert one row(some of its columns) into another table that has about 80% of the same columns however the remaining ones hard non null columns and need some value to be inserted into them. I am wondering can I s...

SQL Server Search Proper Names Full Text Index vs LIKE + SOUNDEX

I have a database of names of people that has (currently) 35 million rows. I need to know what is the best method for quickly searching these names. The current system (not designed by me), simply has the first and last name columns indexed and uses "LIKE" queries with the additional option of using SOUNDEX (though I'm not sure this is a...

How to define ENUM in SQL Server 2005?

Possible Duplicate: Does SQL Server 2005 have an equivalent to MySqls ENUM data type? Is there any way to define ENUM in SQL Server 2005? I have fixed values which I need to use in procedures and functions. ...

.net: How to execute a stored procedure with a nullable parameter in c#?

How to execute a stored procedure with a nullable parameter in c#? EDIT: Actually, I've written below code. As you can see, status parameter is a nullable value type. Is it correct? or not? public void LoadAll(DataTable tb, int? status=null) { try { using (SqlConnection connection = new SqlConnection()...

SELECT SQL Variable - should i avoid using this syntax and always use SET?

Hi All, This may look like a duplicate to here, but it's not. I am trying to get a best practice, not a technical answer (which i already (think) i know). New to SQL Server and trying to form good habits. I found a great explanation of the functional differences between SET @var = and SELECT @var = here: http://vyaskn.tripod.com/diff...

SQL Server deadlock issue

Hello everyone, I am using SQL Server 2008 Enterprise. I am wondering whether dead lock issue is only caused by cross dependencies (e.g. task A has lock on L1 but waits on lock on L2, and at the same time, task B has lock on L2 but waits on lock on L1)? Are there any other reasons and scenarios which will cause deadlock? Are there any ...

Find the period of over speed ?

Just something interesting come in my mind. Assume that we have a table (in SQL Server) like this: Location Velocity Time for example: Location Velocity Time 1 40 1:20 2 35 2:00 3 45 2:05 4 50 2:30 5 60 2:45 6 48 2...

Better Data access components for windows desktop application

Which one can be the better one data access component for desktop application and why? 1.) For MS Access 2007: ADO, OLEDB, DAO 2.) For MSSQL 2005: ADO, OLEDB, DAO ...