I have customerid and sales and calculated sales percentage. What is the best way to calculate the cumulative sales and cumulative percentage how do I get the cumulative sales and cumulative sales percentage. -- drop table test_sales1 create table test_sales1 (customerid int ,salesto...more >>

In BOL, under the sub-topic 'GROUP BY Fundamentals', it is stated that --------------------------------------------- You can group by an expression as long as it does not include aggregate functions. --------------------------------------------- If I am not mistaken, the above sentence can ...more >>

Hello I have a SQL Server 2000 on a remote server, and installed VS.NET 2005 Beta2 on my computer. Now I try to debug stored procedures, but I always geht the message, "Symbols not loaded for this breakpoint", thought it does not stop at this breakpoint Any Ideas what I have to do?? T...more >>

Suppose there are more than 1 condition in the WHERE clause & only the logical operator 'AND' is being used in the WHERE clause. For eg. (note that none of the conditions are bracketted) SELECT...FROM...WHERE ColA>4 AND ColB<2 AND ColC<=10 AND ColD=5 Under such circumstances, will the condit...more >>

Is there a function to randomly select certain percent of the data from a table each time?...more >>

How do you insert multiple rows from a select statement while incrementing a unique primary key in chr? i.e. 00000001 - 00000100 What I have tried to no avail, with error: A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. or V...more >>

Hi All, I am importing data from a mainframe output to SQl Server 2000 database. I have some cost fields but I have made the field's datatupe in char, so that I will not have any problem with populating the SQL Server database (in the mainframe, it is signed numeric datatype). here i...more >>

declare @datacontr datetime declare @datafine datetime select @datacontr = '20040615', @datafine = '20050514' Assuming I have two dates I would like to query results if these conditions are satisfied: 1) Year of @datafine must have at least year(getdate()) - 1 value 2) Month...more >>

Hi, I am somewhat new to T-SQL and I am this error The column prefix 'table1' does not match with a table name or alias name used in the query. What is wrong with the code below? SELECT fmtno, fmtr INTO table1 FROM fomtg WHERE fmtno = @ptNo SET @rowCount = @@ROWCOUNT IF @rowCount > 0 ...more >>

is their is a way to combine MSaccess security login with login to linked sql db? thanks ...more >>

Hi, I have a DROP TABLE command in a proc. The proc fails on DROP TABLE, if I run it under any user other than sa. Even with the execute permission. My understanding is, that procs are run with the permission of the proc owner. So this should be fine. I've been through google groups...more >>

Hello ! I'm looking at the query Excecution plan, there is CLUSTERED INDEX SEEK operation and the numbers are: ROW COUNT: 6 850 703 Estimated Row Count: 554 But in fact there is only 2 557 244 rows in the table. How to explain and repair ? Kuido -- Message posted via SQL...more >>

I need to update few records in a very large table and wanted to know the pros and cons of using a fetch statement in a stored procedure to first select all the records that need to be updated and then loop through each row and update accordingly... verses updating the rows using the update ...more >>

What is the meaning/use of an n' prefix like this: select N'fname, N'lname for customers tx for any help Bernie Yaeger ...more >>

Hi. I'm writing my firsts stored procedures. I'm trying to get a row from one table and insert the results in other table. By now I have this: create proc registerTable @email varchar(30) as select name,email from users where email=@email go insert into employees exec registerTable ...more >>

select ID from syscolumns how do I find out what the table name is based on ID? thanks ...more >>

Hi all, Hope everyone is having a nice day. I'm currently developing in ASP.NET and sql Server. Here's my problem. I have a database that acts like a queue. Users can add or delete lines from this queue. When the stored procedure sp_CalculationProcedure (see section 1.0)is called, based...more >>

Hi, Probably a silly question but given the statement SELECT B.Booking_Number, B.Job_Status, A.Address_1, A.Address_2, A.Address_3, A.Address_4, A.Address_Postcode FROM dbo.tblAddress A RIGHT OUTER JOIN dbo.tblBooking B ON A.Booking_ID = B.Booking_ID wh...more >>

Hi guys, DBCC CHECKTABLE (sysindexes, 255) Result: Server: Msg 8928, Level 16, State 1, Line 1 Object ID 2, index ID 255: Page (1:869938) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 2, index ID 255, page (1:869...more >>

Hi guys, DBCC CHECKTABLE (sysindexes, 255) Result: Server: Msg 8928, Level 16, State 1, Line 1 Object ID 2, index ID 255: Page (1:869938) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 2, index ID 255, page (1:869...more >>

My tempdb has grown to 40 GB and is growing my the minute but I'm unable to determine what process is to blame. If I look at tempdb..sysobjects all the objects are created very recently and change each time i rerun the proc. I would think that anything that has grown tempdb to 40 GB would have b...more >>

I need to use Application Role for our security issue. But my application use three (3) different databases. After I setup Application Role in one database, my application can not access to other 2 database, unless create a 'guest" account, which I don't want to do that. Is there anyway that ...more >>

i'm using sql2k. one of the table i'm working with has book title field in in. the max lengh is 1800 (varchar). i need to index on the title for search performance but the max index key lenght for sql is 900 bytes. what can i do? thank you ...more >>

How could I achieve this easily? I have 50 fields in my table and I want to insert a new row from an existing row with changing the value of 2 fields only. I want to somehow specify the two field names I want changed in my insert statement along with the remaining field names without typin...more >>

Hi All, Here is the scenario. Table contains vendor codes and these are either numeric or mix of alphabets and numbers. Such as : 56710 BAC67 ABF11 34567 I want to query which will give me the results which starts with alphabets only. CREATE TABLE [dbo].[Table1] ( [Vendor] [cha...more >>

Hi, I an using the following stored procedure. Now what I want to do is: 1. If @month is null or nothing.. then @month's value should be all months, i.e it should display data for all months. 2. If @ItemCode is not set to anything or is null, then it the stpored procedure should display ...more >>

I have a table 'download' with 2 fields userid,ipaddress I would like to know the userid's that have the same ip address, how can I get this? sample data ID ip 1 1.2.3.4 2 5.6.7.8 3 192.0.0.0 4 1.2.3.4 My query should return 1 1.2.3.4 4 1.2.3.4 Th...more >>

Is it in any way possible to have a sProc called and executed within SQLServer return its return value to an .Net Vb application or indeed any application? As a programmer I beleive this can be done and if I wreck my head enough I will find a way. I have toyed with the ideas of xml, SQLServ...more >>

is there a way to alter stored procedures as part of a batch? for example if i want to change the name of a parameter, or field that appears in many stored procedures, is there a batch method i can use to essentially 'find and replace' the old value with the new value? thanks for any help, ...more >>

hey guys, need a little advice here. if i want to restrict a column to a decimal precision of 2 - can i do this when i create the table. and should i use a decimal or float datatype. i just want to force two decimal places. or do i just allow the datatype to default and then set the precsion when ...more >>

Hi all, I have a linked Oracle Database to one of my SQL servers. I can query the database using openquery. Is there a wat to execute and pass parameters into a Stored Proc on the Orcle DB? I had the Orcle DBA put the SP into a package but I still can not get any data back. I have been tr...more >>

Hello World, I have a SalesAgent, SalesOrder, Product, and ProductType entities. Say, my product types are "Hard Drive","Mouse","Monitor". I need to store the record when/if the SalesAgent did purchase a product for demo purpose (for profiling). Please comment on the options below or adivis...more >>

I have a stored procedure which accepts a single input string of variable lenght, as its parameter. It then parses that string to develop a set of inputs which will be input, deleted or updated in the database. The amount of time required to run is dependant on the length of the stirng passe...more >>

I'm not a programmer, so I need help with a query. Table: IpAddress Char 23 UserName char 20 ComputerName char 20 LoginDate datetime Many people different people have logged into any one computer many times. I'm looking to get a Count of the number of times a computer has been logged ...more >>

Hello folks, I'm not sure if this is possible, but I would appreciate any guidance. I have a number of passthrough queries in an Access template I am developing for clients as well as some linked tables. In order to make my life easier in the event that I need to change a client's passw...more >>

Hi, I need to query XML data that is held in a text field on a SQL Server 2000 table, the XML includes DTD and Data elements. For example the XML has a data element named S1 and I want to list all the records where S1 is like ‘%house%’. I would also like to be able to list all the fie...more >>

Hi, Is it possible to have a script (e.g main.sql) that calls other sql scripts ? In Oracle you can use "@<path><filename>.sql to call it but I cannot find a way to do it in SQL Server. What I want to be able to do is run other scripts from my main script and be able to pass parameters ...more >>

I have used CASE before, but am currently running into problems using it for different parameters in the WHERE clause, CREATE PROCEDURE spTest @Value int AS SELECT * FROM Category WHERE CASE WHEN @Value = 1 THEN CatID = 2 ELSE CatID = 3 The above example is very much simplified... I ...more >>

I want to retreive the records for when a certain column has a maximum value in a group. Using MAX i get the maximum value but what I really want is the row, not the value. I can do it with a subquery but it doesn't seem that efficient. My actual situation is much more complex involving joins ...more >>

I would like a trigger to both block updates and deletions as well as log all information I can get. I really don't care what the update or deletion is. Thanks for any help. -John...more >>

Cheers, I need to implement an audit trail on a existing Database. The way im planning to do this is to make a duplicate table for each of the tables on the Database and add 2 fields, namely Change Date and Change By. Now on these audit tables you are only allowed to do an insert, so i figur...more >>