This query works except when the count for the Wins and losses is 0, the query returns nothing. The count has to be at least 1 or more. ANy ideas? Select fname+' '+lname as full_name,SubQuery.Wins, Query.losses from tbl_Participants a inner join (Select Part_ID,count(*) as Wins from tbl_W...more >>

Hi All, I have a requirement to create the database with data and log files in a different machine on the network. Googling, I got a link which said Mapped drives do not work but UNC paths work. However in my case, both are failing. Any advices or links on net will be highly useful. T...more >>

I'm an SQL beginner and this is driving me nuts as it seems simple enough but I can't figure it out. I have a table that looks like: ID: int MajorVersion: int MinorVersion: int Content: ntext The ID is not the table key - different rows can have the same ID. The MajorVersion and Mino...more >>

Hi all, Sorry for HTML, there is a lot of code & comments =20 I tried to create a stored procedure from 3 queries .. to reduce # of = times DB gets access from 1 asp page. The result procedure only works = 1/2 way (does return the rest of the SELECT statement) :( =20 Please help me figure...more >>

Hi Please help me URGENT How do i make an tableField with a Auto-Incr, the same like in an access DB where it's called AutoNumber Thanx in advance __________________________________________________________________ Flemming Paulsen ICQ#: 270065050 Current ICQ status: + More ways to contact m...more >>

Hi all, I have a table in the DB which holds ISBN book nubers .. which, as you know have dashes (examlple: 0-98765-8-9). When I run a query from an ASP page to find the number, it doesn't return anything: sISBN = "0-98765-8-9" OpenSQL(select URL from ISBN_Redirect where ISBN=" & sISBN )...more >>

Hi, I have a table ORDER_DETAIL with 22 million rows which has an index of (person_id, code_id, created_dtt) I have another ORDER table with 5 million rows which has an index of (order_dtt, person_id) I have a small CODES table with 1000 rows which allows me to get the 50 ...more >>

I have a Recordset as below which works SELECT DISTINCT MemberID, RegStatus, ProfileDate, Title, PostTown, PostCode FROM [Site Photos] ORDER BY ProfileDate DESC However as soon as I add a further field called "images" it all goes wrong MemberID may have 3 images attached How can I do t...more >>

Hi all, Many times would be useful (for me at least) if sum() could summarize textual fields by simply concatenating them : eg a table named 'lessons' contains Lesson Teacher math Mr. Brown history Mr. Brown math Ms. White gym Mr. Green geo Ms. White so I ...more >>

Hello all, I'm using SS 2000 Based on the following query, how could I get only one row for each different field "F1"? I don't want to use temp table. ---------- SELECT ??? FROM ( SELECT 'A' AS F1, 1 AS F2, 10 AS F3 UNION ALL SELECT 'B', 2, 12 UNION ALL SELECT 'B...more >>

I am running the following OSQL command and capturing the return code for the error .Whenver i have an error like server not exists or uable to login I get a return code of 1 for the %ERRORLEVEL%.However whenever I have an errorof a wrong dbcompatibility error the retun code id 1 even though sql...more >>

This is like the bug from hell. It is kind of hard to explain, so please bear with me. Background Info: SQL Server 7.0, on an NT box, Active Server pages with Javascript, using ADO objects. I'm inserting simple records into a table. But one insert command is placing 2 or 3 records into t...more >>

I am trying to use Bulk Insert for a user that is not sysadmin. I have already set up the user as a member of "bulkadmin". When I run the following script: DECLARE @SQL VARCHAR(1000) CREATE TABLE amdbo.#temp ( [id] [varchar] (10) NULL ,[fld2] [varchar] (10) NULL ,[fld3] [varchar] (...more >>

I have a function which performs a query and returns a table. The one parameter that can get passed in is a date which defaults to NULL. There is an IF statement in the function that will set the paramter to an actual date if null. If I call the function while passing in a date the function come...more >>

I have the following requirement I am creating a login and database user 'test' on a database with dbo role . I want to remove create table , alter table permisions to this user. I am able to revoke create table permission but alter table goes through. I gave a command deny insert,delete,...more >>

I got a server that has a RAID-5 array partitioned into C: and D: drives (OS Win2K Adv. Server installed on C:). The server also has a mapping to a NAS device using the latest protocols that trick the system into thinking the map is actually a local SCSII drive. That's drive X:. This server i...more >>

Hello! When trying to install SqlServer 2000 Developer (as Administrator) on my Windows XP Professional I get the following message at the point where the system asks Local Account or Domain account: The logon account cannot be validated for the SQL server service. Verify that the user na...more >>

I am using SQL Server 7 SP4. I have created a blank database in which i am trying to import using DTS wizard all tables/views/stored procedures without any DATA (records). I keep getting different errors when importing the views and/or the SPs. I've tried many things unsuccessfully. Now e...more >>

All, Given multiple records with identical values in all fields except a single varchar field, is there an efficient query that will group the records into a single record and concatenate the aforementioned varchar field into a single field with each of the source records' values separated by...more >>

I am setting up a database that will receive a lot of data from two separate telephone centers, the log table will in a short time have over 1 million lines, and I was wondering if I should use 1 identify field or two: case 1: [Id] [int] IDENTITY (1, 1) NOT NULL [ServerId] [int] NOT NULL ...more >>

Hello Please help me as i am quite new to this. I intend to buy SQL 2000 but dont know which one to get. developer/standard/enterprise. I want to develop databases for multiple clients for their websites but i dont wish to host them in-house as I will put them in web-hosting servers whic...more >>

Hi, The SQL server 2000 Server hangs some times. It is not periodic. It is not specific in any queries, which are taking more time to execute. Because, it is occurring for different types of applications on the same server on different machines. For the same applications when we had the SQL S...more >>

I'm working on the security portion of this vb app. In the application the user is presented with a list of commands that can be executed on a piece of data. My client wants to be able to block certain users from seeing certain commands on their screen. The commands are loaded into a listv...more >>

Hi, I had a deadlock situation on SQLServer 2000. When I look at the Locks / process ID screen on Enterprise Manager, for one of the session, I see the object being one table and the index is on another totally unrelated table's primary key. This is really odd since there are no foreign key...more >>

I want to get a column count several times in one query using different filters but can't work out how to do it - can anyone point me in the right direction? For example, how would combine these two selects into one query that will list the total and filtered actions: SELECT COUNT(actions) ...more >>

Here is the situation, please let me know if you have any tips: ..TXT files in a share at \\foo SPROCS run daily parses of many things, including data on that share. The other day, we encountered rows in the TXT files which looked like: column1Row1data,column2Row1data column1Row2data,col...more >>

Hi (SQL Server 2000) I have an existing table (t) with a column that is NOT an identity column (t.ID), but it has manually inserted "row numbers". I want to make this column become an identity column. This column is a key field to other tables, so I want to keep the row numbers that are allre...more >>

Table Users FName(text), LName(text), UserID(number) Table Correspondence. Date(datetime), msg_From(number), msg_To(number) Table Users Data John, Quincy, 1 Jane, Doe, 2 Table Correspondence Data 2/2/2002, 1, 2 Ideally the query would return a Record that would look like. 2/2/2...more >>

The following stored procedure is taking too long (in my opinion). The problem seems to be the SUM line. When commented out the query takes a second or two. When included the response time climbs to minute and a half. Is my code that inefficient or is SUM and ABS calls just that slow? Any sug...more >>

Hi again, I have a problem with replication on SQLServer 2000. I've started "Configure Publishing and Distribution Wizard" and SQLSrvr posted message "SQLServer Enterprise Manager colud not configure 'DANILOPOPOVIC' as the Distributor for 'DANILOPOPOVIC'. Error 14234: The specified '@Server' i...more >>

Hi, I have 2 tables in an SQLServer db. I want to compare table A with table B and add any records that EXIST in table B but dont exist in table A, to table A. Can anyone help me with the SQL? TIA Hitcher...more >>

Hi, I have a SQL db which I need to update weekly with data from a legacy db but only where the legacy data has changed. The reason for this is I have to add a couple of fields to the SQL db and they need to remain linked to thier respective records. I'd need to delete from the SQLdb any record...more >>

Hi, I wanted to know if this is possible and if so, how do I do it. Say, I have a query "SELECT * FROM Table WHERE Column="some_value". This executes on a very large data set and I would like to return the results as they query executes rather than wait for the whole query to execute. B...more >>

We had someone create an extra data file and log file for tempdb. So we currently have two data files and two log files. Is it possible to delete the newly created data and log files? If I just delete the physical files, I assume they'll get created as soon as SQL Server gets started back up....more >>

An application called DBMS Copy is giving me an error every time I try to connect to an ODBC database. ERROR #1552 : ODBC, Connect Error -> [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Anly clues appreciated. Thanks Paul ...more >>

I'm going crazy trying to convert an Access Function to SQL. From what I've read, it has to be done as a stored procedure. I'm trying to take a field that is "minutes.seconds" and convert it to minutes. This is what I have in Access: Function ConvertToTime (myAnswer As Variant) Dim myMinute...more >>

If you delete rows in a table and do a full table scan... Is that supposed to read up to the highest block/extent that the table ever attended.(like in some databases I use) If so what is the best way to take care of such tables in sql server. I appreciate your responses Vince...more >>

Does anyone know of a good book or white papers that compare the relative strengths, capabilities and features of databases like SQL*Server, Oracle, Informix, MySQL etc? Thanks. ...more >>

Hello all. I have a table defined in sql server as follows: ROW_ID (identity) DEPTH_FROM Number (8,3) DEPTH_TO Number (8,3) COLOUR Char(10) With typical data like: ROW_ID DEPTH_FROM DEPTH_TO COLOUR ----------------------------...more >>

Hi everybody, I have SQL Server 2K database with 114 MB of data and 220 MB of log file. I have 213 MB of log file free ana stil I cannot shrink log file to 30 MB! Where is the problem? Thank you in advance Danilo Popoviæ, B. Sc. Trgostil d.d. - IC Toplièka 16 49240 Donja Stubica Croat...more >>

I am trying to run xp_cmdshell from the Query Analyzer using SQL Server 2000 running on Windows 98. It seems like it should be simple - I'm typing xp_cmdshell 'dir *.exe' in the Query Analyzer in the Master db. I'm logged in as sa. The timer starts running and never stops. No e...more >>

/* problem: Trying to get partitioned views to "prune" unneeded partitions from select statements against the partitioned view. There are 5 partitioned tables. Each with a check constraint based on a range of formula_id column. Test: Run this script to create the 5 partitioned tables a...more >>

As I had real problems working my head around sp_spaceused, I've written an SP to do it (I also noted a lot of questions about this when "searching"). Pass in a database name and it will return the size of the database as a float (ie. 0.75 for 0.75mb). Update usage set to 1 indicates the DB sh...more >>

Hi, I have a stored procedure that requires several input parameters. I would like to join the results of the stored procedure with another table but I'm having a tough time getting the syntax correct. For example: To run the stored procedure: exec usp_FetchPeople(@Gender = 'F', @City = 'Bo...more >>

hello, i would like to update a column called footer in a table called agency. i have to update about 60 different records in this table. footer is varchar(200) i would like to search for "<table bgcolor="#cccccc"" and replace it with "<table " there is stuff before and after the sta...more >>

Hi I have have two linked SQL Servers and I am trying to get remote writes working correctly (fast). I have configured the DB link on both machines to: Point at each others DB. I have security set up to map each others server logins and Server Options: Collation Compatible, Dat...more >>

Hi y'all I'm having some trouble with the following script I use in a dts-package. I use it to automatically import a dat-file of which the name changes every day. Today the name f.e. is P0000020.dat, tomorrow it's P0000021.dat and the day after P0000022.dat etc etc The error I get is end of ...more >>

Hi, Probably a very simple question but I'm afraid my experience at this is somewhat limited so... In a database with a many to one relationship, say orders to customers, is it preferable to have an UID for a customer and store this in the orders table rather than the customer name, for exa...more >>

Hi Thanks in advance for any ideas. What I'm trying to do, is have a category system like Yahoo or these newsgroups where you have a parent "COMP" and multiple children "LANG" or "DATABASES". However, these can also have children. So for "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE...more >>

I want to get a resultset of every table in the database, with the current record count of each. What is the easiest way to do this? I can get the list of tables with: Select s.name from sysobjects s where xtype = 'U' each s.name is a table name, but I'm not sure how to join a record cou...more >>

Sorry for my bad English but I'm Italian... I have a question for you... I delete all record of table but I don't set autonumber field to start with value 1.... What do you do to set a values of autonumber colomns with Query Analizer or Enterprise Manager...??? ...more >>

I would like the numbers 000001 to 999999 to autogenerate in a new database. I will be transfering information from another database and in that database the numbers 000001 to 010000 are already taken. They are used as identifiers in other programs and it would be easier if they were stored as...more >>

Hello, I have a problem with a view. The view reads as: create view v_lds as select * from lds where dsdate > getdate() - 14 The idea being that the view only shows data which has modified within the last 2 weeks. It works fine when viewing in through any tool. However, when I have on...more >>

If I declare a local variable in a cursors query and append values to it with successiuve concatenations, how come SQL7 does not report an error if I concatenate a value to it that will overflow it's declared size? Specifically, DELCARE @t varchar(5) .... SET @t = '12345' .... SET ...more >>

Say the following are the columns of a table A B C D E F Can a aggregate function like sum be applied to A like sum(a) and then order by b and c similarly aggregate function on d and group by e and f using a single query... Regards, Jai...more >>

I want to look at the size of the current database, so I can create a new one if it gets too big (we are working around the 2gb MSDE limit for our customers). I would like to do something like this: DECLARE @size INTEGER execute BLOB0000.dbo.sp_spaceused and make @size = the database_s...more >>

Hi have have two linked SQL Servers and I am trying to get things working smootly/quickly. Should I be using 'BEGIN TRANSACTION' or 'BEGIN DISTRIBUTED TRANSACTION' ? Basicly, these SPs update a local table and a remote table in the same transaction. I cant have one table updated and not the ...more >>

I'm trying to count the number of records in 'game_dates' where the columns home_team_id or away_team_id have the same value. E.g., i want to know the number of records for each team_id where team_id is home_team_id or away_team_id. I'm doing this in two separate select statements now. Examp...more >>

I am building an invoicing database. I have no problems searching for due dates and generating the invoice header. The problem is generating the invoice detail. My customers may have more than one item that needs to go into the invoice detail table. For example: customer #123 has 2 items t...more >>

I have some code that dynamically creates a database (name is @FullName) and then creates a table within that database. Is it possible to wrap these things into a transaction such that if any one of the following fails, the database "creation" is rolledback. Otherwise, I would try deleting on e...more >>

Hi We have a SQL 2000 (sp3) server with a database that I set up to have the MDF on D: and the LDF on E:. All was going along fine for several months and one day the server rebooted. The SQL log says something like "Service is shutting down due to server shutdown." When it came back up the ...more >>

Is there a way using MS SQL Server and Enterprise Manager to get a text document (or perhaps even a Word document) listing all table names, column names, etc of a database? -- Sugapablo ------------------------------------ http://www.sugapablo.com <--music http://www.sugapablo.net <--per...more >>

I've written code that dynamically builds an sql query based on various constraint possibilities. The problem is the code would have been very complex had I not come up with a dummy constraint as a kind of place holder in the statement. To avoid complex logic that determines if there was ano...more >>

I have 3 data tables, A, B and C, with many to many relationship tables between A-B and A-C. The data in A and C changes rarely, and the A-C relationship relates all possible combinations of A to a C If A contains A.1 to A.3 and C contains C.1 - C.8 then A-C could contain the records: A....more >>

Hi All, I've the following table with a PK defined on an IDENTITY column (INSERT_SEQ): CREATE TABLE MYDATA ( MID NUMERIC(19,0) NOT NULL, MYVALUE FLOAT NOT NULL, TIMEKEY INTEGER NOT NULL, TIMEKEY_DTTM DATETIME NULL, IID NUMERIC(19,0) NOT NULL, ...more >>