tsql

Conversion failed when converting the varchar value to int

I'm getting an error with what should be a simple query to insert data. I've done searching, but for the life of me, I cant figure out whats happening. Here's my SQL: IF OBJECT_ID('settings') IS NOT NULL DROP TABLE [settings] CREATE TABLE [settings] ( [id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY, [tenant_id] [bigint] NOT ...

sql help with association table update

update: This is more of a maintenance issue than the normal case. I have the following table: CourseId StudentId --------------------- 1 1 1 2 2 1 CourseId and StudentId are composite primary key. Let's say I wanted to update where courseid=1 to courseid=2 for some reason. This will cause a primary k...

Is Join or Where clause approach more efficient in restricting result set?

I need to restrict a result set for a SELECT statement based upon Col1 have 1-to-many potential values. For example, I want to return all rows where Col1 equals a value of 1, 2, and 3. So far I have two different approaches to restricting the result set: Approach #1 Inner Join Table1 On (Table2.ColA=Table1.ColA) And (Col1=1 And Col1=2...

SQL Server query to return rank based on game results

I have looked at multiple other question similarly asked on StackOverflow, but nothing seems to fit my bill. My query is slightly more complex. Essentially, I need to find the rank of the entry. My table structure is: TestEntry Id, TotalTime, DateCreated GameResult GameId, TestEntryId, Duration, Score QuestionResult QuestionId, ...

Why does this T-SQL function always return true?

Hi I wrote this function and now its always returning true... can anybody help me? Create FUNCTION dbo.ValidateCenter ( @Center_ID nvarchar(50), @Password nvarchar(50) ) RETURNS bit AS BEGIN declare @Res bit if exists(Select * From TPasswords Where (Center_ID = @Center_ID) and (isSecurity = 0) and (Pass = @Passw...

SQL Server - How do I query a table for matches with keywords from another table?

I have two tables: Job, and JobKeyword. JobKeyword contains three fields: Id, JobId and Keyword, so every Job has its JobKeyword-s. How can I use SQL Server Full Text Search so it will query the Job table, with keywords from the JobKeyword table? This is my current SQL: WITH JobRN AS ( SELECT TOP (@End) searchTable.*, ROW_NUMBER()...

Help with this query

Please can you take a look at this query and point out what I'm doing wrong: update @output set fromdate = o.fromdate, todate = o.todate from ( select fromdate, max(todate) from @output as o left join status as es on o.number = es.empid and o.ccode = es.compcode and @status = es.status ) I'm try...

TSql, building indexes before or after data input

Performance question about indexing large amounts of data. I have a large table (~30 million rows), with 4 of the columns indexed to allow for fast searching. Currently I set the indexs (indices?) up, then import my data. This takes roughly 4 hours, depending on the speed of the db server. Would it be quicker/more efficient to import the...

Conversion of decimal and date/datetime types into custom varchar equivalents

I am generating a flat text file via an SSIS package. The package reads data from a table, creates a fixed length text file and ftp's the file to a partner. The data arrives into my table via a process that I have control over so I can make adjustments based on suggestions. My quandry is this. I have a two fields, lets call them: Dat...

I need to show the monthly inventory data

I have a table some thing like as follows for Inventory details. InventoryTable. InventoryTableID DateCreated quantity ItemName ------------------------------------------------- 1 2010-02-04 12 abc 2 2010-03-10 4 abc 3 2010-03-13 5 xyz 4 201...

Opposite Of An Inner Join Query

can someone help me write sql for a scernerio like this: Table 1 2 columns: ID, Name Table 2 2 columns: ID, Name I want a query to show names from Table 1 that are not in table 2. So filter out all the names in table 1 that are in table 2 is the result query. Use ID for the filtering not name. This will help me in what I am tryin...

T-SQL: Question about NOT IN

Why would these queries return difference results: SELECT * FROM ProjectStatus PS WHERE 0 = (SELECT COUNT(*) FROM Project P WHERE P.ProjectStatusKey = PS.ProjectStatusKey) SELECT * FROM ProjectStatus PS WHERE PS.ProjectStatusKey NOT IN (SELECT P.ProjectStatusKey ...

How to get words from field in SQL Server 2008

I need to get the words in a text field and make some updates with those words, for example: original data words | other field | another field --------------------------------------------- white | | some words | | some other w | | desired result words | other...

T-SQL cursor and update

I use a cursor to iterate through quite a big table. For each row I check if value from one column exists in other. If the value exists, I would like to increase value column in that other table. If not, I would like to insert there new row with value set to 1. I check "if exists" by: IF (SELECT COUNT(*) FROM otherTabe WHERE... > 1) ...

Exclusive access could not be obtained because the database is in use

I'm using following code to restore databases, void Restore(string ConnectionString, string DatabaseFullPath, string backUpPath) { string sRestore = "USE [master] RESTORE DATABASE [" + DatabaseFullPath + "] FROM DISK = N'" + backUpPath + "' WITH FILE = 1, NOUNLOAD, STATS = 10"; using (SqlConnection con = new SqlConne...

How to add #temp tables

I have formed few temp tables in my query (TSQL) like - #temphold1, #temphold2, #temphold3..... #temphold10. Each temp table has different schema (different columns) each derived by grouping data from different tables with specific conditions. I need to determine a way to carry all these temp tables to the User Interface and display each...

How to select rows where multiple joined table values meet selection criteria?

Given the following sample table schema Customer Table CustID 1 2 3 Invoice Table CustID InvoiceID 1 10 1 20 1 30 2 10 2 20 3 10 3 30 The objective is to select all customers who have an InvoiceID value of 10 and 20 (not OR). So, in this example customers w/ CustID=1 and 2 would be return...

Inconsistent cursor results when looping over databases

I have several databases named very similar (my-db-1, my-db-2, my-db-3, my-db-4). I want to execute the same stored procedure on each of these databases. I decided to use cursors. However, I am getting some strange issues. First here is my simple code that I am executing through SQL Server Management Studio 2008. DECLARE @db_cursor CUR...

If column exists query problem - SQL Server

I have a query to add a column if it doesn't exist. It works the first time, but if I run it again, it fails, saying that the column exists?!? IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLE_NAME' AND COLUMN_NAME = 'COLUMN_NAME') BEGIN ALTER TABLE TABLE_NAME ADD COLUMN nchar(3) NULL; END And when ...

C# with INSERT Stored Procedure \r\n problem

Basically i've got a very simple insert statement which INSERT INTO [dbo].[ORDER] (ORDER_DATE ,ORDER_TYPE_ID ,PAYMENT_STATUS_ID ,TOTAL_COST ,SENDER_NAME ,SENDER_EMAIL ,SENDER_MESSAGE ,RECIPIENT_NAME ,RECIPIENT_ADDRESS) VALUES (@ORDER_DATE ,@ORDER_TYPE_ID ,@PAYMENT_STATUS_ID ,@TOTAL_COS...