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 >>