tsql

SQL: "Write Protect" row of data possible?

May be a stupid questions but can I protect a row of data in a SQL Server database from being deleted or updated without setting user permissions? This is for a default row of data that can be referenced for its default values? Thanks ...

SQL Server 2005 - Check for Null DateTime Value

Hello, I'm trying to count the number of records that have a null DateTime value. However, for some reason, my attempts have failed. I have tried the following two queries without any luck: SELECT COUNT(BirthDate) FROM Person p WHERE p.BirthDate IS NULL and SELECT COUNT(BirthDate) FROM Person p WHERE p.BirthDate = NULL What...

How do I compare two columns for equality in SQL Server?

I have two columns that are joined together on certain criteria, but I would also like to check two see if two other columns are identical and then return a bit field if they are. Is there a simpler solution than using CASE WHEN? Ideally I could just use: SELECT Column1 = Column2 AS MyDesiredResult FROM Table1 INNER JOIN Tab...

Sql Query First, Next, Last Rows in a Set

I have two tables that I am trying to join together multiple times. The Parent table is called Jobs, and the child table is called Routings. A Job can have 1 or more Routings. I need my output to contain one record for each job, joined with three separate joins to the Routings table. One join is for Current data (the first null date ...

Customizing FreeTextTable in MS SQL 2000

I'm looking for answers for the following 2 questions (SQL Server 2000). I have an order info table that is indexed so that I may search data on particular columns. So, an example query I might run is: SELECT top 50 ft_tbl.*, key_tbl.Rank from OrderInfo as ft_tbl INNER JOIN FREETEXTTABLE(OrderInfo, Address1, 'W Main St') as key_tbl ...

How to know where AS keyword should be used ?

Hi everyone, There is a AS keyword in TSQL but in what situations I should use it ? For example : Create View StatisticalData AS Select * from People We used AS keyword in this statement but when creating tables we don't use it , I mean I am kinda confused. Could you please tell me, in what kinda position I should use AS. I mean it i...

How to get the item count for sub-categories on each parent?

This is related to the following post: The function that retrieves the item counts for each folder (category) is: ALTER FUNCTION [dbo].[GetFolderReceiptCount] ( -- Add the parameters for the function here @FolderID bigint ) RETURNS int AS BEGIN DECLARE @Return int SET @Return = 0 SELECT --H.ReceiptFolderID,...

FTS in sql 2005

Can somebody please, let me know that how do we find that full text search is enabled in SQL 2005. ...

Converting an integer to a 0-padded string

In SQL Server 2008, I want to represent an integer as a 3-character string - so: 3 becomes '003' 5 becomes '005' 107 becomes '107' How can I do this? ...

Query Performance of joining before or after UNION

Let's say we have a query that is essentially using a union to combine 2 recordsets into 1. Now, I need to duplicate the records by way of typically using a join. I feel option 1 is in my opinion the best bet for performance reasons but was wondering what the SQL Query experts thought. Basically, I "know" the answer is "1". But, I am...

Atomic Upgrade Scripts

With my database upgrade scripts, I typically just have one long script that makes the necessary changes for that database version. However, if one statement fails halfway through the script, it leaves the database in an inconsistent state. How can I make the entire upgrade script one atomic operation? I've tried just wrapping all of ...

Multiple SQL UPDATE statements OR Single with CASE in it?

I've built a trigger where I am trying to keep priorities in order and not allow duplicate priority values. There are some things to consider. The user can free form the priority in. There is nothing blocking them from picking the same priority as another item. When a value is entered equal to another value the newly prioritized ite...

More advanced logic for ordering results of a query in T-SQL?

I am currently writing a SQL query that should display a tree-view of areas inside a building with areas, sub-areas, etc. Unfortunately I have been unable to mimic the ordering used by some of our software tools. I'm restricted to MS SQL 2000 so the question of order becomes much more complicated and I'm just over my head at this point. ...

Eliminate group of NULLs from result set

Hi, I have a query that returns a result set similar to the one below: Quarter | Count ------- | ----- 1 Q2 2009 | NULL 2 Q2 2009 | NULL 3 Q2 2009 | NULL 4 Q3 2009 | 26 5 Q3 2009 | 27 6 Q3 2009 | NULL I don't want rows 1-3, because their combined Quarter-Count is NULL. But I do want rows 3-6 because at least one ...

TSQL using a wildcard in a where clause with dynamic sql

It appears that using the LIKE in a condition with wildcards and a variable inside of dynamic sql doesn't work, although it doesn't give an error. Here's an example. The column called code has values like A0B01C02,A0B02C2D05,A0B02C2D05, etc and I am trying to match on rows containing a subset like 'B1'. When I do this it works and ret...

TSQL Sweepstakes Script

I need to run a sweepstakes script to get X amount of winners from a customers table. Each customer has N participations. The table looks like this CUSTOMER-A 5 CUSTOMER-B 8 CUSTOMER-C 1 I can always script to have CUSTOMER-A,B and C inserted 5, 8 and 1 times respectively in a temp table and then select randomly usi...

Getting Monthly Data Even If Empty

I'm trying to get a report built up from data mining our accounting software. We have a table that stores the balances of each account in a general ledger for a given period (which is 0-12, 0 being carry over from last year, 1-12 being the corresponding month), the amount, and other data I don't need. I'm trying unsuccessfully to get ...

Seek & Scan in SQL Server

After googling i came to know that Index seek is better than scan. How can I write the query that will yield to seek instead of scan. I am trying to find this in google but as of now no luck. Any simple example with explanation will be appreciated. Thanks ...

Integration Services vs raw SQL (T-SQL)

When would you use Integration Services and when would just use SQL/stored procedures? What are the advantages of using one or the other? If you are migrating data from a legacy system, would you use SSIS or just SQL? ...

Why cursor show error

work on SQL Server 2000. want to Automated Email Notifications using SQL Server Job Schedular.If i run the bellow cursor syntax in query analyzer than i get no error .But when i set this syntax on SqlServer agent->Jobs then it's create error. -- Script generated on 29-Oct-09 11:57 AM -- By: sa -- Server: (LOCAL) BEGIN TRANSACTION ...