This will give you float values... select data,value1, value2=(select sum(value1) from (select top 3 value1 from tst t2 where t2.data<=t1.data order by data desc)x having count(*)=3)/3., value3=(select sum(value1) from (select top 6 value1 from tst t2 where t2.data<=t1.data order by data desc...more >>

sqlserver2000/win2k3 Why are these statements: BEGIN TRANSACTION ROLLBACK TRANSACTION UPDATE (to a table created outside of the function) COMMIT TRANSACTION not allowed in a function? Jeff ...more >>

I have a XML file of 2 fields (productnumber , prodqty) of 70 rows saved from an SQL2k server database. I have a vbscript that reads in xml ok, how could i or how should i run through each row of xml file and update a table in a sql2000 table. I can not seem to get the logic of how to enumerat...more >>

I was fooling around with UDTs in SQL Server 2000 but couldn't find a way to define a field as type year. Such a field would be 4 digits long and would be greater than 1900 but less than 2100. Any ideas? ...more >>

Hi, I am writing to this group to get your opinions about using foreign keys with SQL Server. The company I work for handles insurance claims for several hundred thousand people, and our database is built around Microsoft SQL Server 7 running with 6.5 compatibility turned on using a two tier ...more >>

If a query needs to sum child data and get a text field, how do I get around these three messages: select ... mAddr Column 'Address.mAddr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. group by kPerson_pk, cFirstName, cLastN...more >>

create table code (codevalue varchar(20)) insert into code values('dc023') insert into code values('dc024') insert into code values('dc025') insert into code values('dc001') insert into code values('dc004') insert into code values('sa004') this table has some more data. I want to select...more >>

I have a stored procedure that has 7 "If" constructs and nested within each is 4 more. The proc is designed so that only 1 inner branch is executed. When I execute the proc from Query Analyzer with the following: EXEC PLSPDIV_APR_FIND_OWN 0, 1 so that only the 2nd branch of the first constr...more >>

Does anyone know if it's possible to create a user input for using ole automation procedure provided in TSQL. I've been able to create an application that can be accessed using the sp_OACreate procedures but cannot get the application to be visible. Thanks for any suggestions. -Terry...more >>

Hi, I noticed that the records are ordered differently in the results in ascending order returned from SQL server comparing the result in descending order if the sorting column is not unique. I tried to implement top sort for paging technique described in http://msdn.microsoft.com/library/defau...more >>

Hello! Do some of you know if there are some optimizations in MS SQL Server 2000 regaring cases where a cell is updated to the value it already has, e.g. that an index on the value is not getting rebalanced in order to just get back at the starting point and the like? Best regards, Hen...more >>

Hi, When the job started from sqlagent, is there any entry in table on msdb recorded that job has been starting. I think sysjobhistory only records when job fails or completes....more >>

I'm writing some search SPs and I'm trying to find the best way. My problem is with null values for numeric fields. The options that I'm thinking are: 1. Test the value of the numeric parameter and if it is null( or 0) write one select, if it is not write another. 2. Test if the parameter is 0 a...more >>

Hi, I am new to sqlserver and i am looking for a good online tutorial on calculated columns in views. Doesn't really appear to work quite like in MS Access, but most tutorials only give few examples. Does anybody know a good tutorial?? Thank you!...more >>

I use ADO to append new records to a SQL Server 2000 back end. When the row is created I can get the new identity number immediately. What is the syntax I need to use to get an new identity number from inside a stored procedure that calls another stored procedure where the new row is created ...more >>

I currently have a query where I need to do multiple joins to get the appropriate information (7 in total). My question is how many joins is too many?...more >>

Hai everybody, Our database is running on SQL - Server 7.0 . It is storing date in 'mmm/dd/yyyy' format. How can I configure it to store date in 'dd/mmm/yyyy' format? Satya ...more >>

Hi, (sorry for the cross post) is it possible to set up sql mail for a windows 2000/2003 server which uses msde and doesn't have an outlook license or local exchange server? Thanks in advance Tim B ...more >>

I have a couple simple questions, coming from an Oracle background. 1. How can I manipulate the tabular data returned by a system stored procedure? I want to do something like "SELECT [name] from sp_helpdb" 2. Some system stored procedures return multiple tables of data, such as sp_help_jo...more >>

Hi, I have a database, with tenthousands (180.000 to be precise) of "members" in it. For mailing (using the postal service) - they want to cut expenses by grouping the mailings for those people who live together, and this to a maximum of 4 for each adress. Now my question was: What would b...more >>

Hi, maybe you can help me with this: I need to identify the user which uses my application and logs onto SQL Server. Therefore, I can't just use a standard account like sa. If I use trusted connection, users would be able to connect to the database from outside my application, which I w...more >>

I had some querydefs set in an access database front-end linked to tables in an access database back-end. I changed the backend to an sql server and now this code doesn't execute the queries. How do I run them? Dim qdf As QueryDef Dim qdf As QueryDef Dim qdf2 As QueryDef Set db ...more >>

Suppose I have a stored procedure with the following SQL statement. select * from abc_table where status = 1 I'd like to improve the readability of the above statement a little bit. So, I use declare @active_status = 1 select * from abc_table where status = @active_status In terms...more >>

I have a stored procedure that executes one of three SELECT statements based on a value passed to the procedure Like this: IF <condition1> SELECT <bla bla bla> IF <condition2> SELECT <bla bla bla> IF <condition3> SELECT <bla bla bla> ....The procedure takes one minute to ex...more >>

We are expecting around 4million records in a customer Info database. Also we intend doing search on customer's first name ,lastname any word sequence in that is in firstname or last name. Is it wise to use fulltext search in this situation any overhead that we can expect? Thanks GKS...more >>

Hello everyone, I've a question about the keyword "DISTINCT" and was hoping you had some insight. I've got a a multiple join query that looks something like this: ----------------------------------- select a,b,sum(case when year=2003 then c else null end) as SUM_2003, sum(case ...more >>

Hi! I plan to access to MSSQL from my own service. What API is preferred? ADO? OLE DB? ODBC? Thx in adv, Peter ...more >>

A sql server 7 application was converted to sql server 2000. All of the sp's refered to the true condition of a bit field as -1 in sql server 7. After converting to sql server 2000, all of the sp's failed until we changed the reference to 1. ex- "select * from tblbooks where onhand= -1"...more >>

Does anyone have T-SQL code or script that saves a resultset into a text file? Thanks in advance. Steve....more >>

I have a view that fetches data as well as calculates other fields. Here's the select statement: SELECT (SELECT ((HARD_COST) * (BLENDED_PERC)) / 100 FROM Purchase_Use PU WHERE PU.PURCHASE_USE_SEQ_NO = Purchase_Use.PU_ESTIMATE_SEQ_NO) AS USER_SDE FROM dbo.Pu...more >>

Hello... I am attempting to start a job on another SQL Server 2000 server. I am running the following sql code in query analyzer on a different SQL Server 2000 server: EXECUTE msdb.dbo.sp_start_job @job_name = 'RESTORE DATABASE EXPENSE DIFF', @server_name = 'SQL01', @step_name = 'RE...more >>

Hi, I am seeing some baffling behavior from the query below. The query is taking a very long time to run, approximately 120 seconds. However, if I remove one of the EXISTS SELECTS the query will run in under 1 second. It does not matter which one I remove. Is there, or was there, a bug ORi...more >>

Hi, I created a link table from MS Access(XP) to our Exchange 2000 for a spam mailbox. I then imported the data to SQL Server, but now I can't read some of the fields. For instance, the body field just shows <Long Text>, how do I read the actual email message? Also does anyone know ho...more >>

I have an application that helpdesk can use to view NT group permissions. Im having a problem where I am missing some of the role members. Does anybody see anything wrong in this code. I cant find it. SELECT so.name AS Object, su.name AS Grantee, Permission = CASE spr.action WHE...more >>

Hi, I accidently delete all my data on a table. how do I recover them? can I recover them by rollback transaction log? we didn't do database backup for a couple month, so i can't rollback the database. please help! thanks Jay ...more >>

Hi, I want to post a question but I know that I will get flame messages from quite a few people about the way I post my questions. I always go to Enterprise Mgr and generate scripts so that I can post them here. I was never able to get data samples. Please let me know what I need to do so that...more >>

Hi! What API is the best? In past, we used ADO, but there are too many internal memory and resource leaks in ADO. (We looks our programs with NuMega BoundsChecker). Its is critical for us. Thx in adv, Peter ...more >>

My database name is: 'mydatabase' I wanted to rename it to: 'yourdatabase' First, I tried this way: use master exec sp_rename 'mydatabase', 'olddatabase' I got the following error (message): "Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273" "No item by the name of ...more >>

In W2K, you can easily search for a word "foobar" in the text (not the name) of a .sql file. It appears that in XP, you can search .txt files for text, but it does not search .sql files by default. Any idea on how to get XP to search .sql for text just like any other .txt program? I've chang...more >>

Hi what is the syntax to extract data from a date field from 3 PM day before to 3 pm today. Thanks....more >>

I've got multiple DTS packages that are automated, many of them depend on user input of files and such, so there is the chance of errors. What I'm looking for is a way to put some SQL statement behind each query in the DTS to detect if an error was created. I know I can look for @@ERROR, but I ...more >>

Can I do table query using rownum to get exactly rows. I know Oracle can do it. How abou sql server or may be sql server has some glabal varialbes can do it. Please email me the answer. Thanks, Michael ...more >>

Hi! We plan to use DbLib as interface to MS SQL in our future system. There is the question: will Microsoft support DbLib in next version of MS SQL? Thx in adv, Peter ...more >>

I just try to create an index and get an error message here is the exact error message: Server: Msg 3023, level 16 , state 2 Backup, CHECKALLOC, bulk copu, SELECT INTO, and file manipulation (such as create file) operations on a db must be serialized. Reissue the satement after the current backup, ...more >>

Successfully designed a system with relations between tables.Another group designed the same system with relations exisiting only between some tables while missing others.Their argument was that a database system with less relations is usually faster for reporting purposes than the one with rela...more >>

Hi, I need to do a date comparison inside a function and I get the error that getdate() cannot be used inside function. I am SQL server 2000 sp3a Any suggetsions? Thank you, Chandra....more >>

Looking for a LAZY way to copy a bunch of tables for testing. I have several tables that I want to do some testing on and want to make a copy (and work on the copy). Since several of the tables have many columns, I was looking for a way to create the tables without typing all the Create Table stat...more >>

I have a store procedure error, and I want to view the insert table to see what is wrong. CAN I? - How do I find it/reference it. Or does the error and rollback kill the insert table? Thanks. Fred...more >>

What is SQL Server equivalent to Oracle's dual? I need to display some text message (not stored in any table) along with my table columns (retrieved from a databse table)in SQL ...more >>

Hello, someone knows whats happen with this issue please. I have created a DTS package. It just was: Existing connection_1: MySQL ODBC 3.51 DSN: MovingFilesMySQLtoSQL Existing connection_2: Microsoft OLE DB Provider for SQL Server Sql Server Authentication username/password and...more >>

Hello I am trying to get an SQL statement which will do a rather complicated serach without much luck. First the background i need to search in a single field "oldname" for example the word "spc" the problem is that the data in this field is highly varable examples include: "spc, yspc, dox" o...more >>

I have a field in table with varchar 8. I would like to update to smalldatetime without loosing the data. how do I do this?...more >>

I have this access query that I am trying to move into SQL as Cross Tab query. Every time I move this one into Query Analyzer, it fails. Any help would be greatly appreciated…… I have the visual design if anyone would like to take a stab at it Or maybe its just a complex Summary Query SELE...more >>

Hi, I am new to SQL SERVER. What is the maximum row size that can be accomodated in a table in SQL SERVER 2000. Thanks, Shailaja...more >>

hello I have 2 table and I want to create a trigger and when ever I add a row in parent table the row insert in child table with the same id. creating tables : create table parent ( table_id integer not null primary key, dummycol integer ) create table child( table_id integer not null, ...more >>

Hello, Im experimenting with a trigger on a table. It should retrive a specified value from a specified row, where the value has been updated and insert that value into an other table. Im succeding in getting the whole column or Im getting the last value from the update column. Does anyon...more >>

I haveing some problems with the sortorder when i get data from my SQL 2k database. I have som fields with ÅÄÖ and the problem is that Ö is sorted as O and so on. I dont realy know what settings this is controled by so any help would be nice. The regional settings on my computer is set to S...more >>

Hi all, Anyone know what is the maximum length of SQL statemenet in characters I can pass from ADO to SQL server ? Thank in advance Regards, ong...more >>