I am looking for a SQL command that allows me to list for example the top 2 or 3 values for each category. for example I have a table with names, town, salary. I want the names of the two people who have the highest salaries in each town. Imagine the towns are Liverpool, Southport, London. the r...more >>

Hi, I´ve searching for an reuseable SQL-Statement for follwoing situation: Having e.g. a table tblAdress with some fields like persons full name (as Name), City, Birthdate, Street and so on. Now I will find e.g. all oldest persons for every City. No problem with: select name, city, max(...more >>

Quest Software has released a SQL Server version of its highly popular Oracle tool - TOAD. It is freeware! You can see more about it at http://www.toadsoft.com/toadss.html and download it at http://www.toadsoft.com/toad_ss.zip....more >>

Hi, I have a need to pull information from an Oracle 9.2.1 database. It is storing UTF-8 characters in a varchar2(33) field. I need to be able to pull these values out and put them in a SQL Server 2000 version of the table with the UTF-8 characters intact. Anyone know how to do this...eff...more >>

Quick question: Does SQL do table/schema changes "in place"? I've got a large table (140+ million rows of very wide data) that we want to change the schema on -- basically to remove a number of the unused data elements that we don't use. Anyway, does anyone know if SQL will do an in-plac...more >>

If you are in Silicon Valley on March 15, you might want to attend a panel discussion about the marriage of SQL, XML, web services and grid computing. Admission is FREE if you register online for the Software Development 2004 Expo pass. The panel includes the original enterprise Java architect...more >>

I have a database which stores information about organisms collected during sediment toxicology research. For each sample, organisms in sediment are collected and identified taxonomically (Order, Family, Genus, Species). Taxonomy lookup information in the database is stored in a recursive tab...more >>

SQL 2000, Windows 2000. I need to find a way to export our data from our production database and import it into a test database. I can do it to a freshly created test database I just have trouble doing it once the test database is already populated. Is there a script I can write that will automa...more >>

I'm building a DTS package on my development server which I want to move to my test server. I can save the file and import it, but there are connections etc that will be named differently. Is there a quick and easy way of changing the names of connections ? I also have some CSV files used for ...more >>

Hi, I am trying a Set Approach instead of Using of Cursor (which works). I am attaching the SQL to create tables and the my Procedure, and a piece of code to execute the Procedure. I would like the Procedure ReplaceTags to work with 'a' the same as with 'C'. Thanks in advance. Haree...more >>

I am using SQL 2000, 2000 Server. I need to find out how to schedule a database export import. I have a production database that I need to easily copy to a test database occassionaly. When I run the DTS function it won't import the primary keys. It errors. I need to get all the tables and views ...more >>

When I move data from one .NDF file(20 GB) to some .NDF file on some other drive using DBCC SHRINKFILE using EMPTYFILE entire 20 GB data first goes to transaction log. Transaction log space is only 5 GB. We are aware of other way of moving data like DTS/bcp etc. Please tell us if the above can...more >>

Our database size is 250 GB. We are facing problem of Tempdb database size increasing to 15 to 20 GB. We are not able to track query that is causing this problem. Please tell us how to find out the query that is using Tempdb. Thanks in advance. Kamal...more >>

I have a problem and don't understand why I cannot get this to work. I will include the sp below but the gist of it all is I have a field that is an equation in string format such as Value a/Value b and I have 3 seperate tables tblFCF1, tblFCF2, tblFCF3 that each hold information that holds numb...more >>

Hello, I want to COUNT all records between TODAY 12:00:00am and Now(). Select * where TIMESTAMP BETWEEN TODAY 12:00:00am AND Now() How do I do this? Not sure how to specify TODAY, 12:00am. Thanks, Gary. ...more >>

I have the table: Create Table ServiceSched( [key] primary key NOT NULL, [starttime] datetime NOT NULL, [endtime] datetime NOT NULL) (sql might not be perfect) when I enter records manually using enterprise manager, I type something like "8:00" into the starttime field, and E.M. expa...more >>

I am trying to find the best way to copy specific tables from one database to another when the source and target database names are not always the same. Can you use variables to specify (or prompt the user) to provide source and target databases? The target database will exist with the the sam...more >>

I have a form myForm and it's populated by a Stored Procedure mySP. This SP takes about a dozen parameters (the form displays the name and employee ID as results of a contacts search with many criteria.) What I want to do, after the contacts are displayed, is to be able to create a string of e-m...more >>

I've got a server (SQL 2K, Win2K) where the backups have started running long. The database is a bit largish -- 150GB or so. Up until last month, the backups were taking on the order of 4 to 5 hours -- depending on the level of activity on the server. I'm using a T-SQL script in the SQLAge...more >>

To alls... How can I perform a case-sensitive search on text value. For instance, "select * from Table wher Column='SOME TEXT HERE with case sensitive'". Thanks ...more >>

Ok, I have a field in a database that looks something like this Field1: BLAHBLAHBLAH (10.192.168.1) I need to either extract the IP address from this field or at least format it to only show the IP address in the result. The problem I am having with my limited experience is that the BLAHB...more >>

Is there a way to determine why the Textdata field has "NULL" for a value. The trace shows multiple "NULL" value records with the longest durations; many greater than 50,000ms. The {} indicate the TextData and Duration on the line below: rownumber | textdata | Eventclass | ......| Duration |....more >>

I tried setting up a Merge Replication between a MSDE (Pull Subscription) and Sql Server 2000 (Publisher) and got the following error: The process could not enumerate changes at the 'Subscriber'. Then I created another subscription, but this time, a PUSH, and this time I got the error "The ...more >>

Need some information regarding SQL Server locking. Requirement is to run SQL Statements for reports (read-only) that will not lock-out other people trying to update (write lock). For this, we tried the following to make the db connection not deny write locks (our report code will not have a ...more >>

I'm relatively new to SQL-Server, and I have a quick question about temporary tables. I am using a function from Erland Sommarskog's excellent article about arrays in SQL server. Erland's approach uses a user-defined function, which parses a string of delimited values (integers) into a tempora...more >>

I need to create an XML file based on the result from a Stored procedure, I don't really know where to start though, the file need to look like this <?xml version = "1.0" encoding = "WINDOWS-1252"?> <OpenShipments xmlns = "x-schema:s:\upsauto\OpenShipments.xdr"> <OpenShipment ProcessStatus...more >>

Has anyone had the above error ? We ahve an app that uses a C++ to bulk insert into a database, and during the procedure it some times gives us a error "bcp_exec() Failed" Any ideas. Cheers....more >>

I need to group records and assign a setid to the group. I have a table with data that looks like this ColA ColB 94015 01065 94016 01065 94015 01085 94015 01086 33383 00912 32601 00912 I need to create a resultset using just sql to look like this ColA ColB GRP 94015 01065 1 ...more >>

I'm having an argument with an incumbent self-taught programmer that it is OK to use null foreign keys in database design. My take is the whole point of a foreign key is that it's not supposed to be optional, it's very definition is it's a necessary link to the parent table and part of the def...more >>

How much traffic/load can a database server running MS SQL server take before it can't handle it anymore? And when that time comes, what are the recourses? Am I able to load balance it between separate servers? ...more >>

Hi, I'm a newbie to MS SQL server 2000. Is there any way or freeware to generate an ER diagram of a database?? Thanks in advance, Wing ...more >>

xp_sendmail has died on our SQL Server, I get error "xp_sendmail: failed with mail error 0x80004005" if I try using it. I believe that the reason that this has happened is because the SQL Server service account is unable to send mail, as follows: If I log on to Outlook Web Access as the SQL ...more >>

We have a JDBC project that works with SQLServer, along with other DBs. During development, we noticed that resource useage on SQLServer seemed to grow steadily - Processes, Process Locks and/or Object Locks, according to the "Current Activity" section of Enterprise Manager. Fairly quickly (a fe...more >>

i have last name as char(50) in my database. how can i pull out all last names beginning with say "S" or "s" ? thanks for your help Mel ...more >>

Hi; I am new to sql. I want to install MSSQL 2000 and have connectivity ( I am learning DB design). I do not know how to setup the SQL Executive account. I think it has something to do with network. I am using single PC ( no network). I need server and client on my PC and don't know how to do...more >>

I want to store many different types of objects in a single table. I was thinking of using the name value pair approach to achieve this. Does anybody have any experience with a such a design? The table might look like this CREATE TABLE NV (pk int, type int, [name] varchar(100), value varcha...more >>

Is there a stored procedure that allow the compact database programmatically in SQL Server? ...more >>

Hi, My questions is we want to make a copy of a database onto the same server while preserving the diagrams, stored procs, etc. We stopped the SQL service and made a copy of the data and log files. We attempted to ATTACH the file copies (after re-naming them), but the embedded file inform...more >>

''Error 21112: Polling interval is not valid parameter for the log reader agent'' Why?...more >>

what is sql thanks b...more >>

I'm creating a table for maintenance records. In each record, many of the values are simply checkboxes (on the UI). In the database table for these attributes, is a good way to store the state of these checkboxes as simple as 0 for false, 1 for true? -David...more >>

I'm currently trying to access data from a Cache DB using MSSQL. I have linked the Cache server through an ODBC connection. I can see in the Linked Server expansion all the tables in Cache for the File(?) I want to access. Here is my problem: Normally to access a linked sever I would do the f...more >>

Hi All I have a table with one column : CREATE TABLE test2 ( a char(15) primary KEY CLUSTERED ) The column a is filled with encrypted data (contains control and extended characters). On my test, the select statement for one row in table test2 does not always work sucessfully. ...more >>

I have a date that I get from my now() function that returns: 2/10/2004 2:17:16 PM I would like to be able to break this value up. Is there a function that allows me to pull whatever I want out of this? For example, if I just want the year 2004 pulled out (for a drop down list I'm creatin...more >>

Greetings For performance reasons I am trying to determine how often the pages in the index split due to inserts. Are there any records in the 'sys' tables, etc. Thanx ...more >>

Hi all, I've been using scalar functions as a way to perform some complex data transformation operations, and I've noticed that scalar functions reaaaaalllllyyyy sloooowwwwww thiiiiiings dooooooown. I expect slow-down, of course, and would even not be surprised at slow-downs up to a factor of...more >>

I am sertainly no SQL expert so I am looking for a bit of help (actually quite desperate). The extra CPU and 1GB of RAM to be added to a server (Compaq DL380 G2: 1.4 GHz PIII with a Gig of RAM) have not come in and we are scheduled to go live tomorrow with a MS SQL Server web app. Will potenti...more >>

Hi all. If my table looks like the following EMPLID NAME BU SAL ELIG_CONFIG1 1001 Tom 10 50000 1002 Sarah 10 49000 1003 John 20 45000 1005 Jane 10 67000 Now I would like to populate ELIOG_CONFIG with th...more >>

System Sql server 2000 Hi all I've dipped my toe in stored procedures, I'm now having a look at triggers If I had 2 tables and I wanted to update table 2 with some data from table 1, every time table 1 has a new entry made in it (e.g. for an audit trail) I thought I would be able to use a...more >>

I'm in need of a sql query that I'm not sure is possible. Here is an example of how it's laid out. employee ID Job class Last Change Date 12345 x 2/1/2004 12345 y 1/15/2004 12345 z ...more >>

I am converting a relatively large multi-user Access 97 database to SQL Server 2000. I use about a dozen groups to manage security, providing graduated levels of access to each group. I read that in SQL Server a user can belong to only two groups, "users" and one other. If this is true, can s...more >>

I want to create a 1-many relationship. Parent table has a primary key, child table has no primary key. The child table does have an index with all four fields of the parent's PK. How can I do this? Thanks, Bob C....more >>

Hello, I'm in need of a little assitance using MS Access with an SQL Server 2000 database backend. I currently have an application populates an Access database, but I would like to be able to send this data directly to the SQL Server also. Potential solution include the use of ADO/ADOX to mak...more >>

Hi, Does I need to install the mssql client binaries to use ODBC to connect to a mssql database ? TYIA rjp...more >>

Yesterday afternoon I lost access to my local MSDE 2000 sp3(a?) instance. Usinig NT or SQL authentication I get an 'access denied' error when I try to connect. This happened suddenly with no changes to the server on my part. I've tinkered with the Client Network Utility settings, from Multipr...more >>

We have SQL Server 2000 Standard and recently moved from Exchange 5.5 to Exchange 2003. This problem occurred both before and after the e-mail change. We would like to send notifications of failed jobs to multiple people in case one person happens not to be in on the day there is a problem. ...more >>

I have the following SELECT query, the results of which I would delete from the table they're pulled from: SELECT A.* FROM SalesOrderPartPrices A WHERE EXISTS( SELECT 'Exists' FROM SalesOrderPartPrices B WHERE (A.SalesOrderNo = B.SalesOrderNo) AND (A.PartNo = B.PartNo) A...more >>

A colleague of mine has a query which fails to run under SQLAgent batch with the following error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. [SQLSTATE 22007] (Error 242) The statement has been terminated. [SQLSTATE 01000] (Error 3621...more >>

I am using SQL 8 Personal edition with sp2 applied. I set the max server memory to 32MB and leave the min server memory at 0. When my application starts hitting the database hard the memory usage reported through task manager peaks between 41-42MB. I've stopped and restarted the MSSQLserver se...more >>

Hello. I test some query on sql server 2000 (sp2 on OS windows 2000) and i want to know why a simple query like this : select * from Table Where Column like '%value' is more slow on 2000 than on sql 7. And this case arrive only if the % character is in the begin. If you test this : select...more >>

Does anyone know if Microsoft is planning to add a DATE only data type to SQLServer. I know that you could use a datetime and convert/cast or use datepart to compare, but this can be tedious and error prone. What is the recommended way to compare date-only fields? eg if convert(char(11), ...more >>

I am currently working on a PHP based website that needs to be able to draw from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. I took a lot of time and care creating a flexible and solid wrapper and am deep into coding. The only problem is a noticed VARCHAR fields being ...more >>

Hey every one. I have a Trigger question. Here's the senairo: I have a database table called _1423 and another table called OLD_1423s. When a record the _1423 table is updated via a Web page, I want that record (before it's updated) to go into OLD_1423s as a new row. I wrote a trigger for th...more >>

I can access Microsoft Access database via ODBC on Windows 2000 using C++. Now, I would like to use the SAME piece of code to access MS SQL on Windows. Can I achieve this without any modification to my existing codes? Then, I would like to use the SAME piece of codes to access Oracle on Wind...more >>

I have a company table and I would like to write a query that will return to me any duplicate companies. However, it is a little more complicated then just matching on exact company names. I would like it to give me duplicates where x number of letters at the beginning of the company name match A...more >>

I'm trying to use sp_help to get information on my tables. I can use sp_help alone to get a list of objects (including user tables), but when I pass a table name as an argument I get the following error message: exec sp_help exec sp_help parcel Server: Msg 15009, Level 16, State 1, Proc...more >>

I need to access a MS-SQLSERVER with a Linux (SuSE 9.0) based computer. I need to do some selects. What do I have to do what kind of client Software and how do I have to install on the linux Maschine to comunicate with this Server? Probably some kind of odbc? thanx for any hints. Maximilian ...more >>

I posted this under 'microsoft.public.sqlserver.client' but got no reply. Any help with this problem would be greatly appreciated --------------------- I developed a database under SQL Server 2000, with Access 2000 on Windows 2000 as client. This had been running fine for several years. The clie...more >>

Hi, Currently we're a building a metadatadriven datawarehouse in SQL Server 2000. We're investigating the possibility of the updating tables with enormeous number of updates and insert and the use of checkpoints (for simple recovery and Backup Log for full recovery). On several website peop...more >>

Hi, I have a table as follows: HostId PurposeId ====== ========= 1 3 1 5 1 6 2 1 2 3 I'm looking for a query which will return: HostId Purposes ====== ======== 1 3, 5, 6 2 1, 3 Is ...more >>

I need to set multiple values for some SQL statements, for example SET NUMERIC_ROUNDABORT OFF GO SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIF IER,ANSI_NULLS ON GO in a .sql file, but would like to reset them to their previous settings again for other S...more >>

Using SS2K, I'm getting the following error while bulk inserting: Column 'warranty_expiration_date' cannot be modified because it is a computed column. Here is my bulk insert statement: BULK INSERT dbo.TestData FROM 'TestData.dat' WITH (CHECK_CONSTRAINTS, FIELDTERM...more >>

I understand that to implement SQL Server clustering solution, it is necessary to have Win 2K Advanced server. We are looking at 2Node failover solution on a 2CPU Pentium box. Is there a site where we can find out the cost of Win2K and SQL server for this configuration. Thanks. Ravi ...more >>

I have a temp table that's populated with an insert query in as tored procedure. The temp table has a uniqueID as the primary key. In that table I have a column SortOrder. What I want to do is to create a sequential number in SortOrder but only for records matching a WHERE statement, for example...more >>

Please guide me in solving the following I wish to query the the database SQL server using ASP.Information is to be updated on html page in real time and should not be cached,sould not by refreshing the page. Should not be reloaded,no inframes to be used,no hidden frames to be used,and no ...more >>

I was reading that Net-Library Encryption is an SSL utility. Does that mean the traffic uses TCP port 443 or does it still use TCP 1433? Thanks. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_6fu6.asp...more >>