Hello, I am looking for the most efficient way to calculate a trend based upon individual figures in a table. Here is an example (e.g. sold items): Data: date number ------------- ---------- 2001-01-01 3 2001-01-02 5 2001-01-03 10 expected result: date ...more >>

I can write a stored proc to create a temp table and with "while" loops = achieve this, but was wondering if there is a select/something simpler. As an example, if I have 3 tables, Clients (with ID and name), and = ClientCities and ClientProducts. Then I want to list them something = like: Clie...more >>

Hi, I have a query to retrieve data from a table for the give two dates(from and to) in a stored procedure. select * from employees where (DateJoined BETWEEN @FromDate AND @ToDate) now I have give the dates like this to get the results @FromDate =01/01/2005(mm/dd/yyyy) @ToDate=6/2/2005...more >>

The pay period begins on a sunday and ends on a saturday. Some employees work on the weekends, some don't. How can I calculate by week how many hours an employee has worked? Date EmpNumber Hours 20050522(sun) 123 7.5 20050523(mon) 123 8 200...more >>

sql2k There is no way this can be. CREATE TABLE [dbo].[PKauthors] ( [au_id] [int] NOT NULL , [au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [phone] [char] (12) COLLATE SQL_Latin1_Genera...more >>

Hi, can I use between for nvarchar field? can some one explain more detail please... fd1 has: 1000, 1000a, 1000b, a1000, b1000, 2000, 2000a, 2000b, b2000. select * from tb1 where fd1 between '1000' and 2000' ...more >>

How are 'inserted' and 'deleted' implemented by SQL Server? When I look at the execution plan of a trigger that selects from 'inserted', I can see that the user table is queried instead and aliased as 'inserted': >>> Execution Tree -------------- Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [...more >>

Hello, Trying to create a trigger. I was assigned the task (wrong guy for sure), and I have no idea what I am doing. Here is the table. CREATE TABLE [dbo].[Employee] ( [EmployeeName] [varchar] (50), [ActiveFlag] [smallint], [ActiveTS] [datetime] ) The front end is a vb app. When a...more >>

SQL Server 6.5 is a 32 bits version or 16 bits, or bout of them?...more >>

Hi, I used to have queries like this: SELECT P.Name, A.City, C.Country FROM People P LEFT OUTER JOIN Addresses A ON (A.ParentId = P.Id) LEFT OUTER JOIN Countries C ON (C.Id = A.CountryId) Countries are referenced in table Addresses, so instead of writing it...more >>

Need some design ideas. I have to create a table which will have 2 types of IDs MainID SubID 1 1 1 2 2 1 2 2 3 100 3 101 Basically I want to auto generate SubID for different MainIDs. For ...more >>

How can I make a field required based on the status of other fields? I have a Users table for my app that I also reference in forms that are filled out by everyone. Most users don't need to use this table for login, so they don't require a password. Each user has a UserName, Password, and a...more >>

Hi NG Im writing a web-interface that enables the user to update a DB. The (non)queries takes heaps of time, and since my page "waits" for the query to finish, wich is a pain. So basically I was wondering if there is kinda a "update mytable set myrow=... LATER" kinda keyword or something, t...more >>

I must be overlooking something because I cant figure out how to execute the results of a query. The query result is over 8000 characters long so I cant put it in a variable without having more than one and appending them together (which caused some other issues when I have tried that). In...more >>

Hi friends. I have two servers, one with SQL 7.0 SP4 (Production server) and the development server with SQL 2000 SP3. One developer tells me that in the development server the store procedures he creates permits the NULL output. On the production server, he says that the SP migrated, do...more >>

I've been running Project Server 2003 for close to a year now. Currently the database in SQL Server is at 16GB. I find it hard to believe that this is correct, given that there are only around 6 projects stored in the database and I know that much data has not been accumulated. One thing I had...more >>

The Exec Plans from these two simple queries are identical. Of course theres not much data here though. Are the old type of joins in query 1 typically slower, or just not liked/ ANSI standard? select a.*,titleauthor.title_id from authors a,titleauthor where a.au_id = titleauthor.au_id ...more >>

Hi I have the following table: tblMeasurement StationID, PollutantCode, Value e.g. 01220 013 23.4 01122 011 7.8 ..... I want to make a simple transformation-update to the data : IF the code is 013 then multiply the value by 0.2 and replace the existing value, etc. Is t...more >>

The fowwowing 2 queries run in less than 1 second: SELECT Table1.Key, Table1.Field FROM Table1 WHERE Table1.Key='ABC123' Returns 'ABC123', 'ABC' Select Tabel2.Key, Tabel2.Field From Table2 Where Table2.Key = "ABC" Returns "ABC", "DEF" The following query attempts to jo...more >>

Error was returned when we compiled a proc. The proc compiles under SP3a. We're using Standard Edition SP4. The offending statement is a sub-select in a where clause that uses a union: and mv.INSTR_ID in ( select @INSTR_ID union select ridf.INSTR_ID from FT_T_RIDF r...more >>

I am trying to extract all of the supervisors that work in an office. They can appear in two different columns. Some appear in one column and not the other, and some in both. I have the following code: SELECT DISTINCT [Supervisor] FROM ( SELECT DISTINCT [InSupervisor] AS [Supervisor] ...more >>

I need to get two values from a complex SQL statement which returns a single record and use those two values to update a single record in a table. In order to assign those two values to variables and then use those variables in the UPDATE statement, I created a cursor and used Fetch Next.... I...more >>

I need to sharpen my performance tuning skills... fast. Does anyone know of a good book for this? I don't need a book to tell me where to put Indexes. I need a book that will tell me "if you see this in the Graphical Eecution Plan, re-write your code like this". Is there such a thing? Also,...more >>

dear all, I need do constantly queries against a remote server: select * from [xxx.xx.xxx.xx].db.owner.table I would like to avoid put every time the IP between brackets Thanks a lot, ...more >>

Can I use a view inside a fuction, My code runs fine in query analyzer ,but when I put it inside a fuction, I get the error could not use view or function '..... ' because of binding errors. Thanks ...more >>

Trying to build routine to determine when LAST a database was used - accessed by a login. This would help determine AGE of databases, and build archival / removal plans. Anyone know how to get LAST ACCESSES info per database, per server instance?...more >>

Dear fellows, I've got a Query Analyzer session opened against a remote SQL (Argentina) but when I launch "select host_name" it returns me the name of the my local host instead of the remote one. Does anybody know here how can I obtain the real name? Thanks a lot, Enric...more >>

I am working with some data in the form of dates formatted as mm/dd/yy. Over the years, people have made mistekes in entering dates using formats like mmdd. I am using a CASE statement to sort through these and apply the correct formatting to them before I parse them to smalldatetime. I hav...more >>

Dear all, I put on my query analyzer: George Bush and then I press intro and after that, SQL takes 6 seconds till it say: Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'George'. Fine. Repeat the operation It takes 6 seconds Repeat the opera...more >>

Hello all! I have created a update query, which looks like this: UPDATE NMR.dbo.NMR_wpisy INNER JOIN (SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production Machine]) AS Confirmed_Production_Machine, Sum(ProductionConfirmation.[Confirmed Production Labor]) AS Confirmed_Produc...more >>

Ok I have a complex search I need to perform and I am unsure of the best way to go about it. I'll try and describe it the best way I can. I have to whittle down from several values to one or more records, however I don't know some of the values straight off, I may only find them out the 4t...more >>

Hello folks! If you want to see if a table exists you do something like this... if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[My_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) GO What do you do if the table is a ##tmp table? How do you see if it already e...more >>

In the main server(for example, THIS_SERVER): USE master EXEC sp_addumpdevice 'disk', 'INTERMEDIATE_LOG_DEVICE', '\\ANOTHER_SERVER_NAME\C:\SOME_FOLDER_NAME\ INTERMEDIATE_LOG.DAT' Everything is normal I wanted to back up trans. log on the another server in the local net, but failed. BACKU...more >>

Server = SQL Server 2000 SP3a Client = Access 2000 SP3 (.adp) Hi, can anyone advise me on creating an audit process to account for any changes in a table, I thought I'd come up with quite a good idea and started to do it, but now Im stuck. There are several tables which have 20-40 columns...more >>

Hi... This is a very simple question I'm sure but I don't know how to solve the problem... I am trying to do a simple SELECT statement Select * From tblTAble Where id = @id or datecreated = '06/02/2005' The problem is that the date part of the select statement doesn't work as the d...more >>

Hi. 1) How we query the database to get the character set for sql server 2000. 2) How do we change the database character set for sqlserver2000 Please do let me know the detail steps involved in changing the database character set for 2000sqlserver Regards Govardhan ...more >>

Hi! Until now I have a varchar field containing decimal numbers. I want too convert the field to decimal(10,5). This will be no problem because all the numbers in the varchar field is now true decimal numbers. The problem is that the customers using the application writes for example 120,0. And ...more >>

Hi, I have a stored procedure that passes in a string of comma seperated values (ie '3,5,9') using the var @strlist I want to have my stored proc select using this list in an 'in' clause. (ie select * from tbl where customerID in (@strlist) The problem is that the sql sees the @strlist ...more >>

Hello everyone: How to retrieve the user tables not system table?...more >>

Hi I need some SQL script help. Need script to delete all table rows that are duplicates in mirror image. Table has 2 columns, ColumnA and ColumnB. Row1: ColumnA = x, ColumnB = y Row2: ColumnA = y, ColumnB = x Those 2 rows are exactly the same for me. Need a script that will delete Row2 a...more >>

Hi this a question from testking. 27. (27) You are a database developer for your Company's SQL Server 2000 database. You are deleting objects in the database that are no longer used. You are unable to drop the 1997 Sales view. After investigation, you find that the view has the following ...more >>

hi is it possible to Query the sysprocesses and sysobjects system tables to find deadlocked resources and to identify which processes are accessing those resources and Set a shorter lock timeout for the processes that are accessing the deadlock resources?? ...more >>

hai all can any one give syntax in sql server like how to access remote stored procedures ? wheather we have to set any permissions in client machine to access the remote stored procedure please give solution (sytax or procedure ) how to access from sqlserver Thanks & R...more >>

hi in the question below why is C the right answer? doesnt the question say that the CSR and the Marketing employees need Select, insert and update permission? so basically both types of users need an overall access? 17. (17) You are designing your Company's SQL Server 2000 sales datab...more >>

26. (26) You are a database developer for your Company's SQL Server 2000 database. This database contains a table named Sales, which has 2 million rows. The sales table contains sales information for all departments in the company. Each department is identified in the table by the DepartmentID...more >>

hi in the question below i would like to know what is a filegroup? and why is c the right answer thanks ICHOR 4. (4) You are a database developer for your company's SQL Server 2000 online transaction processing database. Many of the tables have 1 million or more rows. All tables have...more >>