sql-server-2008

Can de VS2010 SQL Server 2008 Project type be used to do migrations

I just started using the new project type SQL Server 2008 Database. I can manage the complete schema in detail, but I don't see how I can use the deployment to do versioning like I can with migrations in Ruby on Rails. There is always just one version of a table (CREATE TABLE), and I see no way to go forward and backwards in time with t...

How to compute all trigrams of a string in SQL Server

I want to compute and count all trigrams of a string in SQL Server. For example if the string is hello I want the following output: Trigram Count ------- ----- hel 1 ell 1 llo 1 lo- 1 Wikipedia page on n-gram ...

Getting "no join predicate" warning even tho I am using "inner join"

I am using SQL server 2008 R2 and I have a query that inner joins 3 tables: select * from t1 INNER JOIN t2 on t1."Account Key" = t2."Account Key" INNER JOIN t3 on t2."Account Key" = t3."Account Key" The problem is that I am getting a "no join predicate" warning on the second inner join; in addition to that, it is doing cross product ...

Best strategy for storing documents in SQL Server 2008

One of our teams is going to be developing an application to store records in a SQL2008 database and each of these records will have an associated PDF file. There is currently about 340GB of files, with most (70%) being about 100K, but some are several Megabytes in size. Data is mostly inserted and read, but the files are updated on oc...

SQL Server NULL constraint

Is is possible in SQL Server 2008 to create such a constraint that would restrict two columns to have NULL values at the same time? So that Column1 Column2 NULL NULL -- not allowed 1 NULL -- allowed NULL 2 -- allowed 2 3 -- allowed ...

error:failed retrieve data for this request.

Hi,I need to attach my database to SQL server 2008(I used it before without problem),but now when I want to attach db , sqlsrv sent me this error. error:failed retrieve data for this request. Why do I have this problem and how can I solve it? ...

Help with insert query which replaces an original id with different value in sql ?

UPDATE: Adding more Employee and ReplacementEmployee Table Employee - EmployeeId SerialNo ----------------------------- 1 11111 34 23233 23 13234 Table ReplacementEmployee- ReplacementId SerialNo ----------------------------- 11 11111 23 23233 13...

SQL:Getting count from many tables for a user record in USER table.Whats the best approach ?

I have 4 SQL server(2008 version) tables 1) USER- to store user information (Fields : UserId,UserName) 2) FILES - to store files uploaded by user (FileId,FileName,UserId) 3) PHOTOS -to store files uploaded by user (PhotoId,PhotoName,UserId) 4) GROUPS= to store groups created by user ( GroupId,GroupName,UserId) Now I want to get a USE...

How to retry a transaction that has failed because of a deadlock?

I use linq-sql and this is what I need. Sub Commit() Try StepStart: Transaction Scope Serialized Begin Transaction Check BusinessRule1 Check BusinesRule2 if BusinessRulesFailed { Transaction.Rollback } else { Query = db.out db.SubmitChanges() Transaction.Commit() } Catch DeadLockException Goto StepStart End Try End Sub Is it ok ...

Delete from multiple tables using Merge statement in Sql Server 2008

I have a target table which has a lot of dependent tables. I am trying to modify the table data using the MERGE statement. Insert/Update is fine, but there is a problem while deleting. WHEN NOT MATCHED BY SOURCE THEN DELETE; Here the data needs to be deleted from the dependent tables as well. Is it possible to call a stored proce...

Running trigger that calls stored procedure on another database

We would like to run a trigger on one database (A) that calls a stored procedure on another database (B). CREATE TRIGGER trg_A ON TableA FOR INSERT AS BEGIN EXEC DatabaseB.dbo.stp_B END We would like to do this as LoginA with UserA on DatabaseA. We also have LoginB with UserB on DatabaseB. How can we accomplish this? Currently w...

Trim before destination write in SSIS?

I have a dataflow, where there is a DB source and a flat text file destination(delimited by pipe '|'). The DB source is picking up the SQL query from a variable. Problem is that if my DB field size of say, firstname and lastname are 30 characters, i get the output as(space represented by dots) saurabh......................|kumar.........

Array like functionality in SQL Server 2008

I want to read EmpID in EMP Table based on some condition. For every EmpID I need to do some operation in another table. How can I read single value of EmpID at a time. Thanks in advance ...

SQL Server 2008 XML Query method - retrieve text() value where another text() value exists?

Please consider the following: DECLARE @xml XML SET @xml = '<Capture> <Data><DataType>Card Number</DataType><Value>1234567898765</Value></Data> <Data><DataType>Expiry Date</DataType><Value>1010</Value></Data> </Capture>' SELECT @xml.query('//*[text()="Expiry Date"]/text()') Returns: Expiry Date Rather than retrieving the <DataTyp...

Set multiple fields in a single case statement

I'm not as familiar with SQL Server 2008 and I haven't seen an answer to this question anywhere else. Does TSQL in SQL Server 2008 allow for multiple fields to be set in a single case statement. For instance: case MyField when 1 then ThisField = 'foo', ThatField = 'bar' when 2 then ThisField = 'Mickey', ThatField = 'Mouse' e...

get date of all saturdays in a given year - sql server

I need to get a list of all Saturday dates in a given year. I've seen an oracle post that goes against a table that had "fiscal calendar table" but I haven't been able to succeed in converting it nor do I have a table that contains a set of dates I want to investigate. SELECT DATE DATES,TO_CHAR(DATE,'DAY') DAYS FROM FISCAL_CALENDAR WHE...

Rules are deprecated, what's instead (TSQL)?

Rules (Transact-SQL)[1] are reusable what permitted to overcome the shortcoming of non-re-usability of check constraints. And now I read [1] that: "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feat...

SQL Server to MySql conversion - schema vs. database question

All, We are evaluating MySql as a lower cost alternative to SQL server for our smaller clients in a shared hosting environment. I am still foggy on how we would migrate some of the particulars such as table/scalar value functions, sql jobs, etc, but I will post specifics in another thread. I do, however, have a fundamental question ab...

Do stored procedures lock tables/rows?

Quite a simple question. In SQL 2008 if I have a stored procedure (see below) do I run the risk of a race condition between the first two statements or does the stored procedure put a lock on the things it touches like transactions do? ALTER PROCEDURE [dbo].[usp_SetAssignedTo] -- Add the parameters for the stored procedure here ...

Online interpreter for SQL Server T-SQL code?

Is there any possibility to test T-SQL code on the internet? I mean that e.g many programming languages like Python has several online shell interpreters on the internet. Is there one for T-SQL? ...