Hello all! I'm a newly appointed database administrator and have been struggling with my consious about what would make me an excellent database administrator. I've worked in Access for years and know that if your comfortable with: 1. writing expressions 2. writing some VBA 3. the basics ...more >>

I've created this: SELECT c.ProjectID, Count(c.ID) as 'Registrants', Count(dt.Hits) as 'Submissions' FROM CME_TBL c JOIN (SELECT ProjectID, Count(*) as Hits FROM CME_TBL WHERE evalDate Is Not NULL OR testDate Is Not NULL GROUP BY ProjectID ) dt ON c.ProjectID = ...more >>

Hi, with SQL 2000 and Windows 2000, we're getting lots of this error message "Communication link failure" from our C++ application. Any idea why? Thanks,...more >>

Greetings to all database professionals and laymen, Let us make a bold assumption that we have developed a software tool for the SQL Server environment which simply acts as an interface between an end-user in an organization and the database, through the exclusive use of stored procedures whic...more >>

I rebuilt my machine a few weeks back - re-installed the developer version... The app in question can see all it's tables in the SQL back end via ODBC and everything looks normal via Enterprise Manager, but I can no longer create an ADO Connection. Source code is unchanged, what's changed i...more >>

Hello All, I have a linked ADSI Server to our company Active Directory and everything is fine. I'm running queries and such using LDAP. BUT how can I aquire a list of attributes for the classes and categories. Such as if i wanted to see a user's telephone number and email address for use in ...more >>

Hi All Wondered if you could help. Basically I have 2 tables that contain all the data I want for my report, but I need to put it in a particular way and I need to display it in an ASP page so my queries got to be manual rather than an MS Excel/Query 'munge'. To be honest, the report itse...more >>

I needed to get a list of rows from a table that is not present in another table. My problem lies in the fact that I only want SOME of the rows in table 2 used in determining existance. This happens because table 2 contains historical data (based on report date). Table 1 contains my production...more >>

Hallo! Ich habe bisher nur mit "alten" relationalen Datenbanken wie dBase gearbeitet. Ich würde zukünftig gerne MSDE bzw. den SQL Server oder auch mySQL einsetzen. Was empehlt Ihr zum Einstieg, welche Literatur, gibt es Online Tutorial, ... ? Speziell alles rund um SQL. Vielmehr wie Select ...more >>

I'm a bit of a newby to creating update queries so I was hoping those more experienced could help with what should be a simple query. I do know to create backups and test on a test database first. I need to copy an items' cost from location 'CA' to the cost in location 'OH' when the cost in '...more >>

Hello all, this is my second post to this newsgroup. It's a question about stored procedures and permissions and how these behave between databases. Here's the scenario. I have a database that stores information for a system "A", and I have a different database on the same SQL server that sto...more >>

Hi, we're having a problem with SQL 2000 and Opta 2000 JDBC driver where there is large update running and at the same time, read is blocked for a while. We're looking for a way to catch this blocking process and if it last more than 10 minutes, then email or send out a message. I know sp_lock...more >>

Hi, I have an Oracle (8.1) & a SQL Server 2000 database with Production data. There are situations when I need data from both the databases. My first choice was to link Oracle to SQL and run DTS overnight. But this would have a 1 day latency not to mention the time it would take. 1. H...more >>

Hi everybody, i try to send messages longer than 7990 characters from a text field in SSQL2000. Unfortunatly the messages get cut off after 7990 character. I did everything which is described in BOL (see below). It does not solve the problem. Upgraded to newest Outlook Client and tried to s...more >>

Hi, This question has been bothering me for some time. A lot of people seem to "think" XML is the king of data problems, and I've heard that next version of SQL Server is going to have a strong XML flavor, meantime, I seem to get the impression that a large number of hard-core relational mod...more >>

Hi, I have a question regarding join query syntax: In my DB there are Users table, and Transactions table (which has 'TransactionDate' and 'UserName' fields). I would like to display a list of all users (each user will appear only once), and for each user - his last transaction date. Please ad...more >>

Environment: SQLServer Developer Edition on Machine A Enterprise Manager running on remote machine B Both machines are in the same subnet I open Enterprise Mgr on Machine A, right click on the Stored Proc, enter the parameters and the debugger stops on the first line and I can single s...more >>

Hi, I have dumped a very large database from mysql (using mysqldump program) as a raw sql file. The reason was, convert this database to a MSSQL database. Since mysqldump creates the file as raw sql file with the database-table structures and the data in it, I thought using OSQL command li...more >>

Is it possible to write these two blocks of code as one? The only difference between them is the AND clause: AND is_trade_date = 1 versus AND is_sett_date = 1. Cheers, David IF (@trade_dates = 1) BEGIN IF EXISTS (SELECT 1 FROM calendar WHERE calendar_date = @date...more >>

Hi, I a stored procedure that inserts a record into a table as below. The insert works OK, but if the insert violates a unique indewx constraint on one of the columns, the proc terminates immediately, and does NOT execute the 'if @@ERROR <> 0' statement. Am I doing something wrong,...more >>

I want to monitor any suspicious financial transaction which take place in a bank through electronic transfer. There are three tables Customers, Account and transaction_type. How can I write a SQl to report the following: Detect an outbound Electronic transfer that is unusually high, comp...more >>

Hi all, please see if some one can suggest a better approach for this. i need to move 5 million rows from one table to another. LoanID is the only clumn in Source table Source table structure =========--> ============================== loanID 1 1 2 2 2 3 4 ============================...more >>

I found this great rollup query example that uses grouping and sub totals. Would it be possible to expand on this and include a group within a group and subtotal each group? For example, parent product then child product? Help appreciated. Thanks. Frank SQL: SELECT CASE WHEN (Grou...more >>

I use cascading delete on my SQL Server Database. I am experiencing a long query time on my highest level delete, 10 minutes. If I delete from each table manually and then delete the parent, I will usually be done in less than a minute. Any suggestions?...more >>

HI, I WANT THIS TO PRODUCE EXACT RESULT. (IN SQL SERVER) SELECT (23 / 233) * 100 THE ANSWER SHOULD COME 9.871244635 OR 9.87 BUT IT RETURNS 0. I WANT THE PERCENTAGE. HOW TO HANDLE THIS KIND OF PROBLEM. IS THERE ANY SET COMMAND FOR IT? THANKS T.S.NEGI tilak.negi@mind-infote...more >>

HI all, I am trying to migrate a database from mysql to mssql2k. I use myODBC to connect to mysql server to pull the database from DTS and insert in to sql server. But in mysql server, there is one huge table. Size of it is 1.3 GB and more than 12 million rows. While I try with DTS, i...more >>

Dear Sql Server experts: First off, I am no sql server expert :) A few months ago I put a database into a production environment. Recently, It was brought to my attention that a particular query that executed quite quickly in our dev environment was painfully slow in production. I analyzed ...more >>

What getdate() syntax command can give me time in the following format: 10:41:55 AM Regards, Addi ...more >>

Folks, I have a quick question that I would very much appreciate some feedback on. We are a not for profit charity organization that has decided to develop a software in-house to manage our volunteers. We have SQL and that makes the most sense from a database solution but we have some issues su...more >>

table 1 APP_ID 1 3 table 2 APP_ID Profile_NUM 1 001AAA 2 001AAA 3 001AAA 1 000A223 2 000A223 1 01A345 2 01A345 1 0909 3 0909 5 BASS1 6 BASS1 7 BASS1 I would Like to perform the following: . read table 1 . match table 1 against table 2, using App_ID colum...more >>

When I query or browse databases or tables in SQL Server 2000 it works extremely slow. It started working like this from one day to another. I tried reinstalling but it stayed the same. Now I'm installing Service Pack 3, it's curious that executing replsys.sql, replcom.sql and repltran.sql s...more >>

hi, I have data like 0, null or some value in one column. I want to use this column for devision of some other column data. It gives me devision by 0 error. how can I replace all 0 and null with 1 in fly. thanks in adv. t.s.negi...more >>

I have these 3 queries - they are the same except each fetches record counts for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and nImages (type 2). Is there any way I could get all 3 of these (based on the Node.Type integer) with a single query? IF @Error = 0 BEGIN ...more >>

Hi, not too swift with anything other than simple SQL statements, so I'm looking for some help. Using SQL Server 2000 with this stored proc: (@varCust varchar(50)) AS SET NOCOUNT ON SELECT d.WorkOrder, d.Customer, d.SerialNo, d.Assy, d.Station, d.WIdoc, d.Start, d.StartUser, d.F...more >>

I'm using Access 2000 and have tried Access 2003 front-end to execute the stored procedure sp_password on SQL Server 2000. I continue to receive the following message: "The old (current) password was not correct. Password not changed." If I go into query analyzer and run an execute @old, @ne...more >>

Hi, I am doing a really simple test with SQL Server 7.0: Using the Query Analyzer Logged as sa Located in master database #1 USE Test #2 EXEC('USE Test') #1 => the database context is switched to Test #2 => the database is NOT switched ???...more >>

Hi, I am having problems setting the value of a variable in a SQL String that I have to create dynamically in my procedure. The code that I currently have is as follows: set @sqlStatement='Set @compare_string=' + '(Select ' + @group_column_list_mod + ' from ' + @Tbl_Name + '_Sorted' + ' w...more >>

HI, I have an SQL Server table with only 1 column. That column is an identity column. How can I insert a row in this table using SQL syntax? I tried insert into T_tableName () values () and a few other options, but I can't seem to get it to insert. Thanks Alain...more >>

I am having a database table which has a column of datatype number. i am having a function which takes a number as the parameter and returns a number which is the next highest number in the database than the passed parameter. suppose if i my database is having the data in a column as below ...more >>

I am into a new project where I have to write C programs on UNIX connecting to SQLServer through ODBC. I am not able to find sample programs to learn about APIs. Can anyone point out to documentation containing ODBC APIs for C. TIA. ...more >>

SQL Server 2000 I have a table that contains records of FTP transfers from several = remote locations. TransferHistory HistoryID (PK) StoreID TransferDate Comment I would like to do a query, probably as a view that will give me a list = of each instance where records d...more >>

Hi guys! I need a query to remove all records from my database except the latest 100 added. I use an id as the primary key. But those records are also often removed manually depending on some other values. So I can not simply use : delete * from db where id < last_id - 100 because the last ...more >>

Hello all, I'm using SS 2000 on NT 4.0 I've got a database (4Gb) with a maintenance plan. In this MP, once a week, I do an integrity check including indexes + reorginizing data and indexes pages. Both of these operation are done the same night. I'm baking up transaction log from 8AM to 6PM....more >>

Hello, If I wrote the next ebay (yes I know, yawn-snore) and I had a database with 5 million auction items in it, what would be a really good strategy to get a search done very quickly? Would it involve something called OLAP and/or "data mining"? The only technology I am familiar with is si...more >>

I read the follow query about calculating median posted by Daivd Porta on 10/8/03. CREATE TABLE SomeValues (keyx CHAR(1) PRIMARY KEY, valuex INTEGER NOT NULL) INSERT INTO SomeValues VALUES ('A',1) INSERT INTO SomeValues VALUES ('B',2) INSERT INTO SomeValues VALUES ('C',3) INSERT INTO S...more >>

I need to know how I can capture and handle a runtime exception thrown by SQL Server. The following code does not work. I know WHY I'm getting an error but the ONLY way I can tell is I run my code in query analyzer. The SP cisp_GetData has a permissions problem and I know how to fix it. My i...more >>

Hello - I was hoping that someone out there would have Database Administration books on the above versions of MS SQL Server collecting dust on a corner shelf somewhere in your office. I've accepted a new position - and there is vendor software with databases requiring support using the abov...more >>

My company currently uses SQL Server 2000 in a hosted, shared environment - and it's time to move up. The choices are installing SQL Server on our dedicated web server box, and adding 512MB RAM -- or -- Leasing another dedicated box, this one for SQL Server alone. Our total db size is ...more >>

Hi, I'm maintaining a couple of identical (in structure) SQL 2000 databases and I'm having problems with just one of them. After a couple of days views start to get slow and result in timeouts. I checked the entire W2K terminal server system and the database but there's nothing funny excep...more >>

How would I create a table that logs any changes that happens to another table? Like field that has changed, time of change. Help appreciated. Thanks. Frank *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>