In SQL Server 2005, I've logged in via DAC using ADMIN:instance. I'm trying to execute the query select session_id, sql_handle, plan_handle from sys.dm_exec_requests where blocking_process_id > 0 but get this error Msg 207, Level 16, State 1, Line 3 Invalid column name 'blocking_proces...more >>

Hi All: I need to calculate a running total of a particular column and store it in a table. For example, consider the below SQL statement: INSERT INTO Table1 SELECT QuarterId, QuestionID,AnswerID, abcvalue, SUM(abcvalue) OVER (PARTITION BY QuarterId, ...more >>

Hi, I would like to know what is the major difference between Included Column when creating a non-clustered index and adding columns when creating an index. e.g. if I have a non-clustered index on customerID Select CustomerID, CustomerName from customers where CustomerID = 3 1. I create...more >>

I am NEW to SQL so please understand this when I ask what is undoubtedly a stupid question (I have searched the web, but as I am so unknowledgable on SQL keys/id's I am not sure where to start - any helpful starting points would be great) I usually set my primary keys as such: CREATE TABLE ...more >>

What is the best way to filter via date (not date and time)? If I want all the orders submitted today. I might want to do: select * from orders where CONVERT ( varchar(50),OrderDate , 101 ) = CONVERT ( varchar(50),getdate(), 101 ) What if you were looking for a >= comparison? select * f...more >>

Hi, I would like to append all the values for a column when I run a select statement I thought I could just do something like Declare @LastName nvarchar(50) SELECT @LastName = @LastName ++ LastName FROM Employee Print @LastName Where LastName is the column that I would like to append to ...more >>

I have a series of numbers that I need to store as hex values for example I have 255 and need to store 'FF' or I have 70104 and need to store '111D8' Is there a built in function in SQL2000 that I can use? Or might there be a stored procedure code snipet available?...more >>

I am using sql server 2005. I have a SP made in it which i complined and it is executing fine when getting used. I want to alter it. So, i am selecting it --> right click--> modify --> I get the error message Syntax error in TextHeader of StoredProcedure 'ProcedureName'. (Microsoft.SqlS...more >>

I do the following: BEGIN TRY exec(@execcmd) END TRY BEGIN CATCH -- Execute the error retrieval routine. print 'error' END CATCH and I get this: Msg 22049, Level 16, State 1, Line 0 xp_delete_file() returned error 2, 'The system cannot find the file specified.' Msg 22049...more >>

This is a sample of a table I'm querying: Functionality_Status Account_id Functionality_id Status 100 1 1 100 2 1 100 3 0 101 1 ...more >>

I am using SQL 2005 Analysis Services. I have a table that contains 2 fields (month key , number of business days). Now my fact table has records with the month key in it. Now I create a calculated measure called NBD (number of business days), but I only populate the NBD measure if the value o...more >>

Here is the situation, I have multiple account numbers, and each account has multiple transactions. I first need to setup a count of transactions per account, which needs to be returned in my selection criteria.. then I need to setup criteria where I only return account numbers who have over...more >>

I don't understand why the following statements both returns no rows but the peformance is tremendously different. The first statement takes only 2% of the entire batch and the second takes 98% of the batch. The execution path is different between the two. By the way there is no index for ...more >>

Hi, I'm trying to do a multiple INSERT, but I need it to rollback if any of the records fail to be inserted. I've used Transactions for this before in this manner: BEGIN TRANSACTION; INSERT INTO TABLE (col1,col2) VALUES (1,2) INSERT INTO TABLE (col1,col2) VALUES (1,2) COMMIT TRANSACTION...more >>

Hi All, I have a historical table with no indexes,no constraints on it..basically its used for storing old data. If I do a select it takes more than 2 mins to return the top 10 count. How can I improve the query time? TIA ...more >>

I have a report that I need to group by Director and Manager. This is what the output should look like: The Director, Sr Manager and Manager can be found by looking at the certain digits in the orgcode and then the mgmtlevel. Director Sr Manager Manager Emp # Name FY07 Score...more >>

Hello there On my database i have some tables that i want that some of the fields will be envrypted to any one exept me. How can do that? ...more >>

I've had problems with this ever since we went live back in November, and I can't seem to find any one answer to the whole thing. What I'm trying to do is connect to the database through SqlServer Management Studio (SSMS) as 'petel' not 'sa'. 'sa' runs fine and does what I need it to do. Fi...more >>

I have to display categories from a table I just started working with appropriately named 'categories'. The output has to look like this: Battery Related <------parent name Boat Battery Care <------sub category name Battery Books <------2nd sub category name Battery Tools <-...more >>

Guys I am really stuck on this one. Any help or suggestions would be appreciated. We have a large table which seemed to just hit some kind of threshold. They query is somewhat responsive when there are NO indexes on the table. However, when we index email the query takes forever. FACTS ...more >>

Hi - I have a very simple table with 2 fields: ID int identity User_ID int sickdate datetime Every time someone is sick, a new entry of the date is added to the table along with their user_id reference. I would like to write a query which simply returns the number of sick days...more >>

Our order dates field looks like this: 2007-01-01 11:11:43.000 If I just wanted to do a Select on orders today or between a date range, is there a simple way? Instead of like: Select * >From orders Where order_date Like '%2007-01-04%' Thanks, Trint ...more >>

About a month ago, when adding products to our 'products' table, some of the fields got NULLs instead of a value of '1' or '0' or EA or anything. Our software hasn't been changed on our website in two years and has worked just fine up until this point. (Coldfusion runs our site). Is it possibl...more >>

How can I retrieve all the rows in one table which are not present in another table? This is for instance in a use case where I have a person table and a person_address table (with the foreign key person_id) and I want to know which persons are not present in the person_address table. Thanks...more >>

Ok, I've got a problem that I haven't been able to find a solution to yet. To simplify it, say I've got 2 tables as follows: Items table: ItemID ItemName ----------------- 1 Item1 2 Item2 3 Item3 4 Item4 Location table: LocationID ItemID LocationName LocationDate ------------------...more >>

Hi everyone I am working in a HR db.. I need to create an organization hierarchy chart Anyone has a datamodel that I can use as an example thanks LH ...more >>

Hi Everyone, How I can connect a function table to a table in which its input is an atribute of table. For example,function F has 3 fields and need to connect to a table S to catch one of its field s1 which I want to be Fs' input in every row. Can you all please help me? Thanks, Nassa ...more >>

Hi all, I want to create a query that returns me all the rows with a certain value set: SELECT * FROM TestRun WHERE result = '0' These values should only be returned when rhere isn't a row in TestRun that has result = 1 Can this be done in one query? Since I use a stored procedure thi is...more >>

I have the following sql statement that works without the UNION inside the # characters. I would like to merge the result into one result set. Normally do this with the union function but how to do that inside a while loop? declare @crane as int declare @side as int declare @ycord as int s...more >>

I need to change a field in a table from one user-defined type to another, but how to I make it 'effective'. For example, using the following: UDTtype1 - varchar 1000 UDTtype2 - varchar 2000 I have a table with a field currently defined using type UDTtype1, but now I need to change that...more >>