Hi I have a View and a stored procedure as follows: ALTER PROCEDURE dbo.LettersandLabelsinfo AS BEGIN SET NOCOUNT ON; SELECT Family.familyID , MAX(CASE WHEN Member.memType = 0 then Member.memType end) as 'Mother1 IndiviualID' , MAX(CASE WHEN Member.memType = 1 then Member.memType end)...more >>

I get following error after query results.VFP is the linked server used in SQL2005 to acess Visual Fox Pro9 tables. Hotfixes received from Microsoft does nt fix the issue. See KB article . http://support.microsoft.com/kb/887474 I am running SQL2005 with SP2 on Windows XP professional V.2002...more >>

I'm charged with building a web service that accepts data from multiple locations and stores it to be queried by users. The data we receive will vary from business unit to business unit and we intend to add more units as the project progresses. Each unit's system we add will return data in a dif...more >>

Hello there I have view that use select from table which is not exist in the same database but on another database at the same server. in the view i have order by clause which sort data by diffrent of the table primary key. However, when i run the view it still sorting by primary key of...more >>

I am trying to create a temporary table that is populated with staff from a particular office along with a calculated utilisation rate. However, when I test the following statements I get 0 DECLATE @Rate decimal SET @Rate = (@DaysWorked / @DaysAvailable) * 100 I tested the logic by execu...more >>

What am I doing wrong here? We have full database backups set to go off once/month, with differential and transaction log backups more frequently. My current backup set has File 1 = Full Backup, Files 4 & 12 = Differential Backup, and the rest are transaction log backups. What I'm tryin...more >>

Hello, I have been trying to work on a query to return the amount of entries that are not in each hour. There is a problem with the syncronisation between our databases and I want to find a pattern, in the hours or in the tags on what is not syncronising. In one DB (primary) I have the full...more >>

Hi, all! I'd like to analyze picture pixel by pixel and store this info into the table, something like this Pixels(x,y, R, G, B). I need to analyze large images with about 5 millon pixels. What are your advices to implement this task? Thanks in advance ...more >>

Ok, I thought this was simple, but maybe I am having some brain drain. Devices id (PK) name 1 Printer1 2 Printer2 History hid(pk) deviceid lifetimepgcount 1 1 1000 2 2 30 3 1 1...more >>

Ok, this should be easy, but apparantly I can;t quite get it. I have 2 tables (described below) and I want the max value of the history table. THis is a super simplified post version of the tables and problem.. Devices id (PK) name 1 Printer1 2 Printer2...more >>

Hi: Can you point me in the right direction to do the following? I have a table with an ID and an XML data type. For each row, I want to extract a recordset from the XML document. ID XMLDoc ---- --------------------------------------------------------------- 1 <root><ln>Smith...more >>

hello My book claims there are two kinds of relationships: Two way connection and one way connection, but it doesn't go into any more details. Two way connection is graphically represented as having arrows on both sides of a line representing a connection, while one way connection has an arro...more >>

Hi, Is there any difference between the performance of these if exists statements? The execution plans look the same to me. If there is not perf difference, i guess select * is the best because I don't have to type in (hence no typo) the primary key id. thanks! if exists ( select * fr...more >>

I have a table when everytime a user click an add their automatically logged into database. I need to count and group how many times the user click the ad and how many times they placed an order with a date range. Here are and example of the tables: tableOne -adID -username -cartID -date...more >>

I am trying to set up the query listed below to output the used db_name, log_size_mb, log_pct_used, and status if less than 84, output 'OK' and status if between than 85 and 93 output 'WARNING', and status if between than 94 and 100 output 'CRITICAL'. When I try to run the script I recei...more >>

SQL Server 2000 SP4 =========================================== I have a medium width - high row count table that has a flag isReady which is 0/1. This flag changes after we process records. The table IS HEAVILY HIT and therefore we are trying to determine what is a better, more efficient wa...more >>

I am using the following code from the KB on my PC that bleongs to a workgroup and it works but the same code fails on my client's site where the SQL Server resides on a networked server belonging to a domain: SELECT * INTO ExcelTVData FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;D...more >>

Hello I tried this case statment and it works fine select case when (1=1) then 'hi' else 'bye' end as test now when I try to put it in subquery it fails .ie. select count(*) from ( select case when (1=1) then 'hi' else 'bye' end as test ) group by test The message I get Incorrec...more >>

Hello - I'm sure some of you have attended this. Comparing a week of training at other seminars, on a scale of 0 to 10, how would you rate this? -- Sandy...more >>

I have four tables - The Article table stores information about articles. - Each Article belongs to a User (foreign key) - Each Article can be marked private - When an article is marked private only the article owner and his/her friends can see them. - The ArticleFavorite tabl...more >>

SELECT * FROM Employee emp JOIN Department dept ON (emp.DepartmentID = dept.DepartmentID AND emp.LastName = 'Doe') And SELECT * FROM Employee emp JOIN Department dept ON emp.DepartmentID = deptDepartmentID WHERE emp.LastName = 'Doe' ...more >>

Hi ALL, This query is taking more than 5 minutes. I need to fine tune so that it is completed in less than two minutes. Can anyone help me with this ? All these tables contains large volume of data Create table #TempBatch(batchid int, transid int, amt money) Insert into #TempBatch ...more >>

I've been given a DDL that contains a SP. I've been told I need to create the assembly using "PERMISSION_SET = UNSAFE". Should I be concerned about this assembly? I don't have the source code, but I've been told the only reason this assembly needs UNSAFE is because it uses the System.IO.Dri...more >>

I need to store 256 bit hash (SHA-2 alogrithmn) in one of the table's primary key. I would prefer to use numeric data type rather varchar etc. * Decimal datatype range is -10^38 +1 to 10^38 -1. I can split my 256 bit hash into two decimal(38, 0) type columns as composite key * I can store the...more >>

I am a SQL novice and I am having an issue creating a query. There are two tables in question. One table lists unique Investor, Fund, Dates and attributes of those Investor/Fund/Dates (crstbl_InvestorFundMonitoring). Another table lists transactions related to unique Investor, Fund, (crstbl...more >>

How do I default the owner to dbo for all users who have the ability to create Stored Procedures? It is assigning the owner as the user login name instead of defaulting to dbo Thanks in advance....more >>

Hello, I have recently updated my application to SQL Server 2005 Express (9.0.3042) and found one problem with permissions. I was able to simulate it on this simple example: Two tables: CREATE TABLE [dbo].[project]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NOT NULL, [...more >>

I would like to calculate the average days between orders for top customers. Given the AdventureWorks database as an example how would I calculate AvgDaysBetweenOrders? SELECT TOP 10 customerid, ordercnt = count(SalesOrderID) --, AvgDaysBetweenOrders = ?? FROM Sales.SalesOrderHeader GROUP...more >>

Can anyone help me simplify this leap year calculation? -- DECLARE @test_leap TABLE ( start_date DATETIME, end_date DATETIME, CHECK ( end_date > start_date ) ) SET NOCOUNT ON INSERT INTO @test_leap VALUES ( '20000227', '20010201' ) INSERT INTO @test_leap VALUES ( '20000229', '20010201'...more >>

How do I update multiple values in a table using a select statement where a value in the table to be updated exists in another table? For instance, I want to update the users FirstName and LastName in a flattened data table where a UserID already exists, by getting these fields from the tbl...more >>

Hi all, I've been pulling my hair out with this over the last few days as I'm sure it must be possible but no matter what I try it fails somewhere along the line... In a table containing flattened user data, I have a field which holds the users flattened hierarchy relative to where they are...more >>

Hi, I am working with a CRM. The Opportunity form (saved in Opportunity tbl) must have one team member assigned as SSO, (saved to the OpportunityResource tbl). One team member is automatically added to an Opportunity on creation. Each user may be assigned a Role (saved in OpportunityResourc...more >>

I have 2 tables that I need to join with where condition like below. Problem is because I always get Cartesian product which is very slow on tables with 50000 rows. Is there any suggestion how to rewrite Select? CREATE TABLE #TABLE1( PartNumberLength CHAR(2), PartNumber CHAR(19) ) CREA...more >>

Friends, I have some other set of questions: What is the main difference between having and Where clause? (My answer having is only use with group by clause ) , but we can also use where clause with groupby? so which one is fast and why? I have a table with a column, can we create more than...more >>

Friends, I want to know about a simple concept. the problem is i have three trigger in a table tr1 tr2 tr3 when i execute a statement update statement, what is the order of executing these trigger? As my view Depends on creating order, Is I am right? My second question is -: How we...more >>