tsql

Check if one column value is contained in another column value (TSQL)?

Hey, I have 2 tables with many columns and I want to find those rows where the value from table1.somecolumn is contained in table2.someothercolumn. Example: table1.somecolumn has Smith, Peter and table2.someothercolumn has peter.smith That should be a match, how would I do such a search? Thanks :) ...

SQL Efficiency - Which is quicker?

Hi I'm doing a bulk insert but before inserting into the actual table I need to do some checks. So currently I'm inserting into a temp table but I've just seen that it's possible to declare tables. So my question is - Which is quicker? To CREATE or DECLARE. The table will have multiple SELECTS done on it and will contains around 200,000...

Visual Studio 2010 SQL Variable Color

I'm trying to configure a zenburn-like syntax highlighting for my Visual Studio 2010 environment and am having trouble customizing the color scheme of the SQL editor. In the SQL text editor, "plain text" including local variables and user-defined table names are something like teal and I want them to be 220, 220, 204 like they are in o...

Simple MS SQL count question

I have this SQL DECLARE @url varchar(100) SET @url = 'http://mysite.com/%' SELECT [UserSessionSequenceID] ,[SiteID] ,[Referer] ,[Timestamp] ,ROW_NUMBER() over (PARTITION BY [Referer] ORDER BY referer DESC) AS sort FROM [tblSequence] WHERE [Referer] IS NOT NULL AND [Referer] NOT LIKE @url AND siteID = 15 Want to c...

SQL Server - How to insert into Varbinary(Max) column?

I have a table that looks as follows below. I don't really want to create a C# application to insert rows into this table, if I can avoid it, because of the VarBinary column. My intent is to store a Crystal report .RPT file in this column. Is there a T-SQL statement I can execute to insert/update rows into this table, and include an .RPT...

Problem in WHERE clause

So I have a query. It works if I comment out the part checking for the exclusion. WHERE H.BatchId = 3 AND H.IsExcluded != 1 IsExcluded is a bit field that accepts NULL values. All the values I'm selecting from are NULL, so that SHOULD be right. What am I doing wrong? It's returning no values the way it is, but if I comment out just ...

Create a DATABASE using as a Name from a Variable

I would like create a database using value from a variable. Here my script but it does not work. Any ideas? -- Set Data Base name DECLARE @DataBaseName char(64); SET @DataBaseName = 'DbCmsWebsiteTest'; -- Create Data Base CREATE DATABASE @DataBaseName; GO ...

SQL Server2008: BeginExecuteNonQuery / EndExecuteNonQuery Problem

I have a stored procedure, which writes a backup of a specific database. I call this SP in a C# / Windows Forms application asynchrounously. Here is the code snipped: IAsyncResult result = command.BeginExecuteNonQuery(); while (!result.IsCompleted) { System.Threading.Thread.Sleep(1000); ... } command.EndExecuteNonQuery(result)...

Unique Index Error when creating non-unique index - SQL Server

I trying to and a non-unique index to a table table in SQL Server 2005. I am getting the following error when I try to create it. Msg 1505, Level 16, State 1, Line 1 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.oe_pick_ticket' and the index name 'idx_pick_ticket_popup_wmms...

Incorrect TSQL DISABLE and ENABLE statements

SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [Lending].[uspHMDAUpdateExport] (@BatchId int, @ModifiedById int) AS BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DISABLE TRIGGER Lending.utrHMDAAudit ON Lending.HMDA UPDATE Lending.HMDA SET IsExported = 1,ModifiedById = @ModifiedById WHERE Bat...

LTRIM usage with SQL server 2005

I am a bit of an sql noob so please forgive. I can't seem to find a usage example of LTRIM anywhere. I have a NVARCHAR column in my table in which a number of entries have leading whitespace - I'm presuming if I run this it should do the trick: SELECT LTRIM( ColumnName) From TableName; Will this give the desired result? ...

How to check for a substring in a column, using CASE /IF (SQL querying)

I need to return one of 2 values for certain conditions: My different cases are: when one column has 'substring' on the right end, return that column. (i.e. LIKE '%substring') Else return other column (from other table) This works: SELECT * From Table1 where col1 is not null and col1 like '%substring' However, this doesn't work: ...

Is there a way to pivot a customer ID and a their most recent order dates?

I have a query that gives me all customer's and their last three order dates. EX: CustomerId DateOrdered 167 2006-09-16 01:25:38.060 167 2006-09-21 13:11:53.530 171 2006-08-31 15:19:22.543 171 2006-09-01 13:30:54.013 171 2006-09-01 13:34:36.483 178 2006-09-04 11:36:19.983 186 2006-09-05 12:50:27.153 186 2006-09-05 12:51:08.513 I want...

Best way to prevent Update

Hi All, I have a situation where we want to prevent the update of a table for a specific scenario. So for 95% of the updates I would want the update to flow through as normal and update the desired fields....for that other 5% I want to prevent the update from happening. This will be based on data passed in the update along with what i...

how to insert data into db as a serialized object

my basic question is how to insert data into DB as a serialized object and how to extract and use it then ... any suggestion !!? e.g : {id:1, userId:1, type:PHOTO, time:2008-10-15 12:00:00, data:{photoId:2089, photoName:A trip to the beach}} as you see how could I insert data into column Data and then to use it !? another question i...

Create view based on hierarchy / used of CTE

Hi everyone, Is it possible to create a view based on hierarchy / cte ? I've seen an example on how to generate a resultset based on the link recursive query. I've attached the ddl and the statement. Thank you, Elmer CREATE TABLE [dbo].[XHR_PERSON]( [PERSON_ID] [bigint] NOT NULL, [LAST_NAME] [varchar](100) NOT NULL, [FIRST_NAME]...

SQL CASE and local varaibles

Hi, I am studying SQL, I would like to know how can I use local variables in CASE statements in SQL. Thanks guys for your help. This script gives me error: DECLARE @Test int; DECLARE @Result char(10); SET @Test = 10; CASE @Test WHEN @Test = 10 THEN SET @Result='OK test' END Print @Result; I USE MS...

Should stored procedures check for potential issues when executing?

Let me explain this question a bit :) I'm writing a bunch of stored procedures for a new product. They will only ever be called by the c# application, written by the developers who are following the same tech spec I've been given. I cant go into the real tech spec, so I'll give an close enough example: In the tech spec, we're having to...

How to calculate the local datetime from a utc datetime in tsql (sql 2005)?

Hi, i want to loop over a period of time in tsql, and print the utc datetimes and our local variant. We live in UTC +1, so i could easily add 1 hour, but in the summertime we live in UTC +2. In C# i can create a datetime and use a method to ask for the UTC variant and vice versa. Till now i have this: declare @counter int declare @l...

Working around globalization issues when executing T-SQL

In an app, where SQL server databases with schemas and locale unknown at compile-time (determined at runtime) can be plugged in, I need to figure out how to update a decimal field - ie. what decimal separator to use. So against one server, if I need to update a decimal field, I would send 100.125. On another server that would be interpr...