Just trying to find out if this was the best solution. I used the derived table solution of a couple of posts ago. What I am trying to do is to select a record from a table and compare the fields to fields in another table. For example, if I have a table with criteria of age, I want to see ...more >>

Hi. I have the following updates to make in a table: a. I have to update SalesBrut to (Sales - [Returns]) where Sales and [Returns] are fields in my table: SalesBrut = (Sales - Returns) b. Then I have to find out the total of SalesBrut for the whole table, so that I can calculate percen...more >>

I have table valid_until proposal_date 25.10.2005 NULL 23.10.2005 26.10.2005 NULL 20.10.2005 28.10.2005 24.10.2005 22.10.2005 28.10.2005 how to select that result will be NULL 20.10.2005 22.10.2005 28.10.2005 23.10.2005 26.10.2005 28.10...more >>

Im trying build a query from c# like below, but its not work, how should I wrote MAX section? int xValue = someIntValue; string sql; sql = "SELECT xmin, xmax, ymin, ymax FROM COORDS "; sql += "WHERE MAX(xmax, " + xValue.ToString() + ")" >= 100"; ...more >>

I'm writing a stored procedure and am trying to update a field which is found in many tables, but I don't want to hard code all the tables into this stored procedure. I am able to execute a select statement which identifies all the tables which have this field. I can create a cursor to itera...more >>

I have a problem when i write procedure on sqlserver 2000. [code] DECLARE @intTotalRecord int; DECLARE @strTables varchar(200); DECLARE @strWhereDescs varchar(200); DECLARE @strSQL varchar(500); SET @strTables = 'MyDB.dbo.MyTable'; SET @strWhereDescs = ' name like ''%kane%'''; SET @strSQL = ...more >>

I have a table called order, in which there is a field called ordertype. The format of this record in this field is XXX---XXXXX, the number of Xs before --- vary, and do not necessarily 3Xs and 5Xs. Is there any way I could truncate whatever is after --- (including ---) and keep only whateve...more >>

I have records in a budget table that contain user startdate, enddate and value. I want to create a view, that contains a row (user, date, value) for each week day (not week-end), and the proportion of the value from the table Thanks...more >>

I am a zipCode4 field, and some of them have letters in it. I would like to search those that contain letter(s) in it. Thanks for your help, Lam...more >>

Hi All Am hoping someone can help me please. I am using bcp to create a csv file on a remote share. If I use the ip address of the remote share as part of the unc path to the remote share I get an access denied error. If I use the machine name of the remote share as part of the unc path to the re...more >>

Hi, Is it a good idea to use SQL 2005 Management Studio to manage/work with SQL 2000 servers? Thank you, Alan ...more >>

Hi I am new to SQL server. Hence please help me. I need to delete around 100 million records of a table in SQL Server 2000. One delete statement is creating the transaction log issue. 1) Can I run a DELETE statement without recording in the transaction log? 2) Can I run the delete in st...more >>

I have a remote query to an oracle table with 81 millions rows in it. The query runs on two SQL servers, both with remote collation and collation compatible turned on. Both execution plans use a nested joop join. However, on one server, the local table is the outer input and the remote ta...more >>

Hello, I am kinda lost with this problem and hoping that someone here might be able to help out! The sample data below shows transactions for an ID - basically, for different IDs, we keep track of when their rc (return code) changes and the values to what it changes. I would like to get th...more >>

Hi, I have installed SQL Server 2005 CTP/Trial Version. When I try to attach the database 'AdventureWorks', I get the following error. Could not find row sysindexes for database ID 11, Object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes Could not open new database 'AdventureWorks'....more >>

I am have a table like this Date Clicks Impressions -------------------------------------------- 10/1/2005 10 100 10/2/2005 20 100 . . . 10/30/2005 100 1000 I want to display a report by weekly based upon the dates user selec...more >>

I have a table called Order, within which there is a field called orderdate, I can I program so that I can always retrieve order with an order date as next Friday when I am running this script anytime this week. Thanks, Qjlee...more >>

Hi This is proabably a trivial issue, but I have a table containing names of personnel in the format of : Smith, John However I wish to have the format as: John Smith Is there a simple way in which I can achieve this? Kind Regards Ricky ...more >>

Is there running sum function in MS SQL Server 2000 like a property in MS Access? In advance thank you, Stanko Milosev work: stanko@nospam--netcomp.co.yu www.netcomp.co.yu home: stanko@nospam.milosev.co.yu www.milosev.co.yu ...more >>

I have a dynamic sql which uses "select into" to create a temp table. It has to be dynamic because the fieldnames are soft coded.I need to join this temp table (that i created using the dynamic sql) with another table.Since the temp table goes out of scope after the exec statement i am not abl...more >>

Hi all - I have been trying to figure out if this can be done in one query, or if I need to break it out into multiple queries. The rows are for a report - so subreports may be the direction I need to go. I have an sql statement with 5 joins in it - three of the joins are no problem ...more >>

I am trying to write a update that will update the value of one column in several rows of data. Basically I want to move a total into a column for easier use later as follows. In English it would be EMP_VARTOT=EMP_VARHRS - (SUM OF TAKEN THIS YEAR) When I run the statement below I get the erro...more >>

I am still fairly new to SQL server and I am trying to make a stored procedure that I can do the following: run select statement on a table update a table return the data that was selected in line 1. Update -- TIA ...more >>

Hi All, I have a field whose datatype is decimal. I am trying to see if the value is less than or equal to zero. I am getting Server: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric. Here is what I was doing select field1= CASE WHEN table2.MaxLabrHrs<...more >>

How would I make a query to know what the next step is? Currently I run a query that returns these columns and I manually scan the data to look for issues. I would like to be able to have a NextAction date column, perhaps a case, but what would the construct look like? -- ddl create tabl...more >>

I have an auto-incremental field in my sql database table. After I add a new record I need to get that ID. My below code adds the record with no problems but the ID field I request always comes back empty. If I look in the table the new record is there with the auto ID field. hr = pCon...more >>

I am trying to use "select into" query in a dynamic sql.But the temp table is not getting created when i execute the dynamic sql.What am I missing? Thanks in advance....more >>

Does anybody know what the sysobjects.sysstat field is used for and when/if it should be used in programming? It's just that I've found it referenced in some old generated SQL scripts and used in logic (if object doesn not exist then create....) and was thinking I should replace it. The actual...more >>

I'm looking for a way to get a Row count on all of the tables in a Database. Is there a slick way of doing a SELECT COUNT(*) FROM (All Tables) Thanks, Scott<- ...more >>

Given a table: ref date AA 1/1/2005 BB 1/1/2005 CC 1/1/2005 BB 5/5/2005 CC 10/10/2005 How can i get the most recent record for each ref? e.g. ref date AA 1/1/2005 BB 5/5/2005 CC 10/10/2005 I know how to do it with a single record using T...more >>

I want to insert @DBNAME before the tables in the from section. Would someone help me with the syntax? Thanks! set @DBNAME = N'AFD.dbo.' SELECT @NumRows = count(*) from @DBNAME + tbl_Revenue Rev, @DBNAME + tbl_RevenueTransactions RevTrans WHERE Rev.RevenueID = RevTrans.RevenueID AND Re...more >>

SQL Server 2000 SP3a Have I totally missunderstood and AFTER INSERT, UPDATE trigger as mine fires on an insert being run but with no actual inserts occuring. ie I have a sproc which inserts into a table, I've checked the select statement of the insert and it returns no rows, so I presumed ...more >>

Hi All, I have some peocedures that call stored procedures in Oracle from SQL Server 2000. the way I am doing this is by inserting into an Oracle table that has a trigger that executes the procs. I have been doing this for some time now, we needed to add a new proc in Oracle, but the new ...more >>

When you group and wants to show the aggregate of a numeric column, you do SUM() on it. Is there a way to do this for a varchar type so that all the values are concatenated and separated by a comma for example? And it needs to be a single select statement. Is this possible? Jiho Han Sen...more >>

Hi, I have UAT and production servers with same database schema. I am running a stored procedure on both machines, Its taking much more time on UAT (31 hrs) where as it is taking less time i.e. 8 hrs (which is expected because of nature of query) Can someone please explain why is it taking mo...more >>

Hi friends, i have the following issue for you... On one SQL2000 with SP3, i have one database with one table, with this structure: Servername (char,20) DateOfData (Datetime) DataBName (Char,20) SizeOfDB (int) The purpose of this table is to collect information for a lot of serve...more >>

Hello everyone, I have 3 tables namely User,UserRole,Role. User table has userid as the primarykey and all the other user details. UserRole has 2 columns userid referencing User.userid roleid referencing Role.roleid Role has 3 columns Roleid primary key rolename description...more >>

I need to return a specific number of rows in a table based on the number of options selected in another table. i have the following code, but the query analyzer says i must declare @countSelected. Any suggestions? the table reportOptions is always qualified by a userID so that multiple use...more >>

Some idiot decided to use unique identifiers instead of an auto-number identity column on every table on the system I am going to be working with. This is causing problems because I need to perform simple SELECT statements where a unique-identifier column is used as the foreign key. For ...more >>

Hi, I have a table that has a number of fields including a Date/Time field. I want to use this field to hold a time only. I've chosen a date/time filed so that i can sort the column. The problem i am having is returning just the time part. When inserting data i use '1899/12/30 HH:MM:SS...more >>

Hi, I have looked high and low for a solution to my problem. Of no avail. I would appreciate any help on the following issue. I have a field (column A) that contains a string that consists of words seperated by semicolons. The number of semicolons differ per record, sometimes 4, sometimes 5. ...more >>

Hi - I have a column in a table that is supposed to be a date, it's data type is text since it's source is a text file. Which looks like this: Chg_Date 19990105 20000323 20010526 00000000 00000000 as you can see, there are some dates with 0's which of course, are invalid, I need...more >>

I've got an interesting query that I'm trying to come up with a clever way to do rather than unioning like 50 queries together. I have a table Responses that stores responses to a questionairre. Say there are 3 questions (there are actually about 20 but to simplify) each stored in a field with...more >>

Hi I got a table with 2 columns as follows col1 col2 10 193.51 10 194.5 10 202.71 20 192.79 20 197.6 20 192.9 30 192.76 30 191.91 30 187.9 Now i need to add a column dynamically thru s...more >>

Hi at all, i'm trying to read a table with 400.000 records but the DataAdapter is very slow to fill a DataTable. Anyone knows a way to read fast this records (like when I execute a veiw from SQL Server). Thanks!!!...more >>

I want to rename all tables and fields that starts with 'AAA_' to start with 'BBB_' instead. How can I do that?...more >>

Hi, I am having a problem with xp_sendmail. My SQLServerAgent service is configured OK with an Outlook profile (I can set up an Operator and a 'Test' gets through fine), however when I try to use xp_sendmail it returns the following error xp_sendmail: failed with mail error 0x80004005 ...more >>

Hi I got a table with 2 columns as follows col1 col2 10 193.51 10 194.5 10 202.71 20 192.79 20 197.6 20 192.9 30 192.76 30 191.91 30 187.9 Now i need to add a column dynamically thru sql statement to the t...more >>

Dear all, The last value I see for a identity field is 174. That's fine. But the next value after insert which appears is 217 instead of 175. How do I force the sequence 'natural' again?? I suppose that it happen due to I deleted some rows... I would need in order to add a new row into ...more >>

I have two tables: Contact & Address. A contact can have many addresses. I need to do a query that will return the contact and _all_ if any addresses in a single row (similar to a pivot table): ContactName - Address1 - Address2 - AddressN What's the best way to do this, and fastest as it m...more >>

Dear all, When I launch a pump from a DTS appears the following errror: Backup operations, CHECKALLOC, massive copy, SELECT INTO and the manipulation operations of files in a current database must be done serial. Launch again the statement after ended the current operation. I haven't idea w...more >>

I would like to ask for an advice/ suggestion on how my task become faster.. I have a table named CIF with 6.5million rows, LOANS table with 750,000 rows, Branches table with 350 rows CIF fields (CustName, CustID, NotIncluded, BranchID, ApplicationCode, etc..) Loans fields (CustID, etc..) ...more >>

Deal all, I have a table with values like : ProductID ProductName ProductPrice 1 Product01 1000 2 Product02 1100 3 Product03 2100 4 5... about 113 prodcut on line (could be around 150 in the future)....more >>

Hi, Is there any executable or script that I can run to get a list of all servers to monitor on my management server. I already have a few that I'm monitoring but I think we have added a few more SQL Servers to the network that I need to add to the list. Thanks. -- Message posted via...more >>

I am astounded. I haven't read any where that adding a sort order to a query would drastically increase read time for the same query. This query performed on a table with 360,000 records: SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], * FROM [PLVWDIV_INV_SHORT] ORDER BY [SEARCHKEY] DESC Take...more >>

I'm really sad guys... I installed Visual Studio 2005 RC with SQL Express 2005 (included on CD) and it worked very well Today I removed SQL Express 2005 and I was really enthusiastic to install SQL Server 2005 Developer Edition because I belive that is more flexible use Management Studio in...more >>

TableA contains customer_number,product_id,purchase_date. TableB contains customer_number,product_id,purchase_date. I need to insert everything into TableB from TableA where the entire row from TableA (customer_number,product_id,purhase_date) doesn't already exist in TableB (customer_number,...more >>

how can i display numbers in a specific format like function: format(Num,"#,###") in msaccess? thanks ...more >>

Hi all I want to know that can I use MSDE as server that other clients connect to it?? If yes,what limitations does it have?? thanks ...more >>

hi All, i need a way to store bank details in a database, because of what it is i don't want to leave the information in a way that is easliy accessed. is there a white paper somewhere that i could read? or can somebody point me in the right direction :-) Many thanks Simon Whale ...more >>

Hi all How can I prgrammatically detect name of Server of SqlServerService Manager? thx all ...more >>