Is there a way to SELECT and UPDATE rows at the same time?

I’d like to update a set of rows based on a simple criteria and get the list of PKs that were changed. I thought I could just do something like this but am worried about possible concurrency problems: SELECT Id FROM Table1 WHERE AlertDate IS NULL; UPDATE Table1 SET AlertDate = getutcdate() WHERE AlertDate IS […]

How to remove white space characters from a string in SQL Server

I’m trying to remove white spaces from a string in SQL but LTRIM and RTRIM functions don’t seem to work? Column: [ProductAlternateKey] [nvarchar](25) COLLATE Latin1_General_CS_AS NULL Query: select REPLACE(ProductAlternateKey, ‘ ‘, ‘@’), LEN(ProductAlternateKey), LTRIM(RTRIM(ProductAlternateKey)) AS LRTrim, LEN(LTRIM(RTRIM(ProductAlternateKey))) AS LRLen, ASCII(RIGHT(ProductAlternateKey, 1)) AS ASCIIR, ASCII(LEFT(ProductAlternateKey, 1)) AS ASCIIL, ProductAlternateKey from DimProducts where ProductAlternateKey like ‘46783815%’ Result: […]

sql varchar(max) vs varchar(fix)

every time i confused for selecting varchar(max) or varchar(fix) datatype. suppose i have a data column that will be around 5000 varchar. column is not null type. should i set it varchar(max) not null or varchar(5000) not null. same thing in case of null able data type. CREATE TABLE [dbo].[tblCmsPages]( [CmsPagesID] [int] IDENTITY(1,1) NOT NULL, […]

Database-independent SQL String Concatenation in Rails

I want to do a database-side string concatenation in a Rails query, and do it in database-independent way. SQL-92 specifies double-bar (||) as the concatenation operator. Unfortunately it looks like MS SQL Server doesn’t support it; it uses + instead. I’m guessing that Rails’ SQL grammar abstraction has solved the db-specific operator problem already. If […]

how to check if table exist and if it doesn't exist create table in sql server 2008

I am writing a Stored procedure in sql server 2008 I need to check if a table exist in the DB, if it doesn’t then I need to create it. How do I do it?

TSQL Comma Separation

I’m writing an export function, where I need to export contacts to Excel, and I’ve run into a technical snag – or perhaps a gap in my SQL skills is closer to the truth. 😉 Here’s the scenario: I’ve got a bunch of contacts in a database. Each contact can have many different roles, for […]

TransactionScope Prematurely Completed

I have a block of code that runs within a TransactionScope and within this block of code I make several calls to the DB. Selects, Updates, Creates, and Deletes, the whole gamut. When I execute my delete I execute it using an extension method of the SqlCommand that will automatically resubmit the query if it […]

SQL Reporting services: First call is very slow

I’ve installed a SQL Reporting server (2008 R2), with some reports. But I’ve some performances issues. The first call of the day to the server(going on the report interface by example), is VERY slow(something like 30-45seconds at best). The report generation is then “fast”(1-2 seconds). The next calls to the server are always fasts until […]

How do I set the default database in Sql Server from code?

I can’t seem to figure out how to set the default database in Sql Server from code. This can be either .Net code or T-Sql (T-Sql would be nice since it would be easy to use in any language). I searched Google and could only find how to do it in Sql Server Management Studio.

get @@Identity from another server(linked server)

I have a linked server, I want to add a record to the table on the linked server, Is it possible take @@identity from another server with linked server? (SQL Server 2005)

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.