tsql

Why is SQLServerCE query performing a table scan and is it an issue?

I'm trying to optimise a query for a SQLServerCE database running on a Windows Mobile device. The query is used to identify rows to delete in one table based on no longer being referenced from a different table. The original query used a DISTINCT to identify matching rows: SELECT TestGroupId, TestNameId, ServiceTypeId FROM ServiceTypeIn...

Stored procedure that deletes records by looping through Id's

So I am really confused on how to write this proc. Here is what needs to be done. I have 3 tables laid out as follows: tblObject {ObjectId, MasterId} tblAnotherObject {ObjectId} tblFurtherObject {ObjectId} I need a proc that can delete the rows in 'tblAnotherObject' and 'tblFurtherObject' that contain an 'ObjectId' that does NOT hav...

The tablix 'Tablix1' is invalid. The value for the DataSetName Property is missing - Help

For some weird reason i can not run my report, when i try to preview i get this error: (PLEASE RIGHT CLICK THE IMAGE AND CHOOSE VIEW IMAGE TO SEE IT LARGE) Here is how my tablix property looks like: Any idea what to do? The current .rdl was copied from another one. I changed the newly copied .rdl values to match my needs. I always...

Is there any Query to find the SP which contains errors?

I am working in Sql Server 2008 and there is a change of table in my database. So I need to know which all the queries I am using that table or I need to know which all queries will throw errors.I has changed some SP to work fine.But there is a lot of queries which may cause errors.So If there is possible to find which all queries will r...

sql server compatibility level

hi there, i've to downgrade a bunch of queries from sql server 2008 to 2000. some functions are not supported in 2k, like cte or row_number(). i've copied the database to an sql-2008 server, setting compatibility level to 80, hoping this db would behave like in a 2k-server. but i can easily run a query using cte or "not-supported" func...

SQL Server - Drop and Recreate a Table - Blocking Access While Doing So

Hi, I need to drop and recreate a table, which exists to "cache" an expensive view. The view may change and I want to make maintenance as easy as possible, so I want the new table to reflect the latest version of the view. I also want to be able to prevent read errors should a procedure try to access the table while it is in the middl...

Selecting a set of distinct values and counting them individually into new columns

I'm fairly sure this is an easy thing to do, but I'm a newbie at SQL so be gentle. If I want to write a query, that adds up the total occurences of each process number and stores those values to a new column, what do I do? I thought some mixture of count(distinct ...) could get it down but I'm not sure. See the result table for what I'm ...

tsql : Access query to TSQL union update conversion correct?

I have a query in access which i need to convert to a stored proc in sql server 2005. the query in access is as follows: UPDATE tblitem, tblFileSignature SET tblitem.strFileProcesstype = [tblFileSignature].[STRFILEPROCESSTYPE], tblitem.strFileSignatureType = [tblFileSignature].[strfilesignaturetype] WHERE (((tblitem.strFileSignatu...

Poor Man's SQL Pivot. List Questions as Columns and Answers per User in one row

Current query: SELECT order_id AS OrderNumber, ordName, ordLastName, question, answer from cart_survey JOIN orders ON cart_survey.order_id=orders.ordID JOIN survey_answers ON survey_answers.id=cart_survey.answer_id JOIN survey_questions ON survey_questions.id=cart_survey.question_id Results: OrderNumber ordName ordLastName qu...

Self join to lowest occurrence of group

Hi. I have a problem in T-SQL that I find difficult to solve. I have a table with groups of records, grouped by key1 and key2. I order each group chronologically by date. For each record, I want to see if there existed a record before (within the group and with lower date) for which the field "datafield" forms an allowed combination wit...

What is the difference in these two queries as getting two different result set?

I am getting different result set for these two queries and second result set seems to be correct. What is the difference in these queries. What type of inner join query second is? 1) FROM TABLE1 t1 INNER JOIN TABLE2 t2 ON t1.Id = t2.Id WHERE t1.StatusId = 12 2) FROM TABLE1 t1 INNER JOIN TABLE2 t2 ON t1.Id = t2.Id AND t1.Sta...

T-SQL: A better sliding distribution function/query

I need a T-SQL ranking approach similar to the one provided by NTILE(), except that the members of each tile would be on a sliding distribution so that higher ranking tiles have fewer members. For example CREATE TABLE #Rank_Table( id int identity(1,1) not null, hits bigint not null default 0, PERCENTILE smallint null ) --Slant the dist...

Help with select statement with one to many relationship

what is the best way to write a select statement with two table that have a one to many relation ship? I have a table called broker with these fields id companyname then another table called brokerContact that has a foreign key to the broker table with these fields id brokerid contact name phone How can I write a select statement...

How to Take Part of a String using One of the T-SQL Text Functions

I have the following snippet: CONVERT(varchar, FLOOR(ROUND((DATEDIFF(minute, shiftStart.timeEntered, shiftEnd.timeEntered) - DATEDIFF(minute, lunchStart.timeEntered, lunchEnd.timeEntered)) * 1.0 / 60, 2, 1))) + ':' + CONVERT(varchar, ROUND(ROUND(DATEDIFF(minute, shiftStart.timeEntered, shif...

How optimal is this function

I'm about to implement this function to calculate some numbers. CREATE FUNCTION [dbo].[funLookupFTE] (@PFID int) RETURNS VARCHAR(20) AS BEGIN DECLARE @NumberOfFTE AS VARCHAR(20) SET @NumberOfFTE = (SELECT SUM(CASE WHEN Hours <= 20 THEN 0.5 WHEN Hours > 20 THEN 1 END) AS FTECount FROM tblPractitioners ...

transact-sql question

Hi, Assume there were 100 records in tableA and tableA contained a column named 'price'. How do I select the first-n record if where sum of price > a certain amount (e.g. 1000) without using cursor? thanks ...

In SQL Server, xml data are saved in a text column, how to query from this column?

Hi, In my situation, xml data are saved in a text column, how to query this against this column? For example: create table t1 ( id INT IDENTITY(1, 1) PRIMARY KEY, content text ) insert into t1(content) values ('<?xml version="1.0"?> <people> <person> <firstName>ooo</firstName> <lastName>ppp</lastName> ...

Sybase IQ - how to show stored procedure without wrapping text?

Hello. Using Sybase IQ v12.7. Executing sp_helptext ProcedureName shows the text of the stored procedure which is fine. However it wraps lines at 80 characters. The question is, how to show text text of a stored procedure without wrapping? In Sybase Central Java Edition there is a feature see the text of a stored procedure (Tranact-...

T-SQL Update Statment with ORDER BY

I am using SQL Server 2005. I would like to update fields like Order BY MatchId orders as below query. But updeted fields not order by MatchId. DECLARE @counter int SET @counter = 10008 UPDATE Matches SET @counter = MatchNumberCounter = @counter + 1 WHERE MatchId IN (SELECT TOP (232) MatchId FROM Matches WHERE LeagueStatueId =...

How to place negative value into brackets

i want to place negative value in bracket like -2000 to (2000) for that i made a which is converting -1361236.75886298 to (1.36124e+006) the function that i made for this is : ALTER function [dbo].[IsNegative]( @Number NVARCHAR (500) ) Returns nvarchar (200) as begin Declare @Number2 FLOAT set @Number2=Cast (@Number as float) Declar...