I need to do something that goes against normalization, but it is what the client wants. Let's say one person has several addresses. I need to be able to take each of those addresses and combine them into one field. So I need to take this: John Doe | Address 1 John Doe | Address 2 Joh...more >>

Hi, I need to update a field in about 20 records on a table. The table has an update trigger (which updates the [lastedited] field whenever a record is updated). As a result I'm getting an error: "Subquery returned more than 1 value.", and the update fails. Is there a way in the stored proc...more >>

This is kind of what I'm trying to do in my MS SQL 2000 query. Should I be able to reference s1.col1 inside the 2nd derived table? I'm getting 'Invalid column name col1' and it's coming from the 2nd derived table (I've commented out other refs to just it to check). Maybe I need to use a temp...more >>

Is there some reference material that shows how to connect to a SQL 2005 DB using Visual Basic 6? Any help is appreciated. ...more >>

I have a client who has reported a discrepancy in their database. In their test database a certain report returns 5,333 rows, but in the production database it returns 5, 332 rows. I'll get to the bottom of it in due course, but I came across an oddity. I wanted to know how many rows there w...more >>

Hello, I was wondering if anyone can help me figure something out. Is it possible to do a querey in MS SQL server and have the results returned so that each result in each row is preceeded by the column name? eg. instead of usual output -> colName1, colValue1, colName2,colValue2,colName3,c...more >>

How does the phrase "Is Not Null" in the where clause effect the effectiveness of a query? If it is a determent to the effectiveness of the query, how do you work around it? Thanks, ...more >>

I need to open a newly created xml doc (in the same format) each time a new one is sent to our web service. I would like to use a stored procedure and call to it the problem is I keep getting a XML parsing error: Invalid at the top level of the document. See code What I really need is a ...more >>

I've written this query: select distinct topics, questions, answer from topics AS A, QuesNans AS B where A.topicid = B.topicid order by a.topics The results are: Topic Questions Answers Topic Four Question 1 Answer to question 1 Topic One Quesstion 2 Ans...more >>

I have a table with the following columns, NAME, TYPE, TAG And there may be 'duplicates' on name and type. How can I delete them?? I want to delete all with duplicate NAME and TYPE ...more >>

Here is my problem: I have two tables. Table1 contains two fields. Field1 contains names of job types (Accountant,Doctor,etc.) Field2 contains the number of cases to select from Table2 for each job type. Table2 contains three fields. Field1 contains Unit codes(Unit1,Unit2,etc.).Field2 conta...more >>

Hello, everyone I have one question about the standard join and inner join, which one is faster and more reliable? Can you recommend me to use? Please, explain me... Thanks Chamnap ...more >>

All, Is there any way to _set_ an IDENTITY column to have a certain value so as to create a gap in the set of identity values on purpose? All I've found is SET IDENTITY_INSERT. Thanks, Wes ...more >>

All, I need to do some bulk loading of data, and in order to do it, I need to be able to do the following: 1) Retrieve the current identity value for the table using SELECT IDENT_CURRENT('myTable') 2) Generate a data file with pre-created identity column values 3) SET INDENTITY_INSERT 'myT...more >>

Hell All, To reproduce one of our cusotmer's probem, I need to make the SQL to run for more than a minutes before it returns the result set. I do not have large amount of data in the database to simulate the dealy. Is there a way in SQL to cause the delay while returning the result set Tha...more >>

Hello, I need to alter fields in all my tables of a given database, and I would to do this via a t-sql script. Example, I want to change all fields called SESSION_ID to char(6). The field is usually varchar(10), but the data is always 6 characters in length. I have serveral fields that are fixe...more >>

Hello, I am loading data from our MS Active Directory into our data warehouse. (check out Mircosofts's Logparser, it can pull data from ADS, server event logs and more. It can also create text files or load directly to SQL. Its free and a pretty useful tool) There is a field that contains the d...more >>

Hello, I currently have Table1 and View1. View1 is a query from 2 or 3 tables that works fine on its own. However in my current query if I try to use it...something like... SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3 FROM View1 a JOIN Table1 b on a.col1 = b.col1 ...more >>

I've got a weird one here. I'm running a DTS package on SQL Server 2005. It copies a bunch of stored procedures. I renamed them on the originating server and ran the DTS again. The came over with the old name and code! I deleted the DTS and built it from scratch, and the same thing happened...more >>

Does anyone know of a built-in function to return the maximum possible value of a given datatype? I have to return the biggest value for a smalldatetime or datetime in a view if the field is null, but can't find such a function. The closest I've come is: select datalength(cast(getdate() as sma...more >>

If I have table1 and table2 with table2 having multiple rows tied to a single row in table 1. What I am trying to do is set up a view that has one row that shows the following table1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail, table2.row3.detail I'd like to be able to do...more >>

I have inherited a database that tracks if a customer ordered a product, with 1 being a yes and 0 being no. What I want to do is sum those columns (customer_tbl.ordered2004, customer_tbl.ordered2005, customer_tbl.ordered2006) and set the value of that sum into a column in the same table (custome...more >>

All, I'm new to MS SQL Server and I'm wondering if there is a free client that will allow me to connect to a remote SQL Server instance. Currently I can connect via the tsql command on a Solaris box but it seems to offer limited options. A PC based GUI front end is a good idea. Anyone knows i...more >>

I have a problem in this query: select order from ordertable where orderdate >= '06/20/2007' and orderdate < '06/21/2007' this query do not return any record although there are records in the table matching the query please help me ...more >>

I have a problem in this query: select order from ordertable where orderdate >= '06/20/2007' and orderdate < '06/20/2007' this query do not return any record although there are records in the table matching the query please help me ...more >>

Hello! I have this piece of SQL code: UPDATE a SET Field1 = c.Field1 FROM a INNER JOIN b ON a.GUID1 = b.GUID1 INNER JOIN c ON b.GUID2 = c.GUID2 WHERE c.Type = 1 AND @date BETWEEN b.DateFrom AND b.DateTo This query takes hours to complete. Now while trying to find out what's causing...more >>

Hi, I got 'Invalid Column Name NewCol1' when I query the following: Select col1, col2, (some calculation from the fields) as NewCol1, (some calculation from the fields) as NewCol2, NewCol1 = NewCol2 from Table1 inner join Table2 inner join Table3.... Where ..... Basically, I want to fi...more >>

im trying to understand how the profiler works. so i started 2 profilers,one listen to another and I saw the profiler is running: exec sp_trace_create @P1 output, 1, NULL, NULL, NULL which means @tracefile = NULL so where from the profiler read the results?! ...more >>

I am trying to compare the last two values in the same column of a table. First of all, I have a column titled Row_Index that uses an index which starts at 1 and increments by 1. What I am trying to do is compare the values in the column 'Shift_Date' for the maximum value of Row_Index and the ...more >>

I am doing an update to set a field value = anothe field value (in the same table) where it is not supplied. I'm handling this in the trigger, but am getting deadlocks. Do you see anything wrong with this that would cause deadlocking? ALTER TRIGGER [trg_myTable_UPDATE] ON [dbo].[myTable...more >>

Hi folks I have a little problem converting from an access database to the SQL server I need to have a highscore that shows the top 10 scores, grouped by email. In my access db i had this query: "SELECT TOP 10 First(users.user_naam) AS FirstOfuser_naam, First(users.user_voornaam) AS First...more >>

Hello, We are using SQL server 2000 on W2k Server and MS Access 2000 ADP (like front-end). Now we try to change operating system for SQL Server 2000 from W2k to W2k3 and we found problem with date format - we receive error: "Cannot convert date type varchar to datetime". Datetime used in ap...more >>

I'm trying to get into an installation of v.6.5 to use with another server and I need the following information: Database hostname Database name Database usernane Database password Is this information available anyplace within the installation files? I'm not asked for uname and pwd to get...more >>

Hi Friends, Is there any way to get the table name which is referenced by the foreign key for example: consider two table "Staff" and "Department" Staff with following columns PK_ID FK_DepartmentID Name Address Department with following columns PK_DepartmentID Dept...more >>

All, Just want to make sure that I understand what's going on here. I have a table with IGNORE_DUP_KEY set on a unique, multi-column index. What I'm seeing is this: 1) When performing a BULK INSERT, the UNIQUE index is not being respected and rows which violate the unique index are in...more >>

This (demo) statement is fine in Access, and so far as I can see, should be OK in SQL Server. But Enterprise Manager barfs at the final bracket. Can anyone help please? select sum(field1) as sum1, sum(field2) as sum2 from (SELECT * from test where id < 3 union SELECT * from test where id ...more >>

I have read all of the posts regarding this subject as well as 3 "Dummies" guides and I keep having the same problems. I have a very simple multi-user application that has to hit a database. We cuerrently have this system deployed and it is working fine on a domain based network. We are scali...more >>

I have a customer using our program with SQL server and is occasionally getting a "Transaction (process ID xxxxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim." From what they are telling me, there shouldn't be any deadlock happening as they s...more >>

Hi, I am looking to check and set the encoding of the database using sql commands that work both for SQL-Server and JET. something equivalent to the postgreSQL commands: 'SHOW server_encoding' Thanks in advance, Maarten...more >>

In the database i have a table with this information: key_id => 1 key_desc => 43+34+22+12 I want sum the values in key_desc. Something like: SELECT key_desc FROM table But the result of the select was "111" and not "43+34+22+12". Is this posible? ...more >>

Hello, I have a problem. I've linked MySql server to MsSql, in MySql I have a table with Latvian data(character set is ucs2, ucs2_general_ci) and the problem is that when I use openquery to read data from MySQL server, some characters are not translated correctly! I receive question symbols ins...more >>

Hello, Using SQL 2005. Columns: ID, int (PK, auto-increment by 1) WorkHours, int Name, varchar(100) I can't seem to get the following query to work. I want to return all Names and the sum of ALL work hours, in each row and order by each INDIVIDUAL work hour: SELECT Name, SUM(WorkHour...more >>

i have a copy of a SQL Server 2000 database on an external hardrive. I have SQL Enterprise Manager 2005 on my laptop and was wondering if it is possible to view the backup on my laptop when external hard drive is plugged in. What other software/tools/procedures do i need to do to make the b...more >>

Hi Friends, Is there any tool or assistant to upgrade SQL 2000 to SQL 2005. Thanks, Arunkumar ...more >>

I have googled by heart out trying to find answers to this and am only more confused. DB Server: Windows Server 2003 Enterprise 4 GB memory SQL Server 2000 Enterprise Edition, SP4 8.00.2039 The SQL server process appears to be using a steady 1.7 gb of memory. There are no other memo...more >>

Hello everyone, I have a fairly unique need :) I am trying to determine the use/clients for databases in my corporation that I am maintaining, but that noone seems to know what they are for. Many of these databases never seem to have anybody connected to them in the current activity. What I...more >>

Hi. I have an 'Attendance' table like this: PIN Year Category Days 1 2006 Authorized 1 1 2006 Available 2 1 2006 Personal 3 2 2006 Authorized 4 2 2006 Available 5 2 2006 Personal 6 3 ...more >>

Hi! I am trying to do a simple udpate on a table and I am getting the error: "Cannot insert duplicate key row in object 'UserInfo' with unique index 'UserInfo_Login'.The statement has been terminated." Here is the command I am trying to run --- USE Deve2_SITE update UserInfo set tp_...more >>

Hi I`ve Ms SqlExpress 2005 and question how to export/import all databases to/from file. Bcp.exe can do it but only for a table I think. Pls help Mirek ...more >>

I need to find the rows that exist in one table but not in the other with this condition: (prod_name exist in table1 and not in table2.prod_name ) AND (prod_name exist in table1 and not in table2.'S'+prod_name ) explanation: i want to know if the product not exit and if the combinatio...more >>