Hi, Can't post specifics at the moment but if this seems like a common problem any help would be appreciated. When querying with ~6 tables, using mostly left outer joins, I get standard performance with the where clause where XXX is not null however, if I try the clause where XXX ...more >>

Hello, Thanks for taking the time to help me out with this. I've been pulling my hair out trying to figure this out. SERVER: Microsoft SQL Server 2000 TABLE STRUCTURE: terms_no varchar 10 note_no int 4 note_text varchar 60 SAMPLE DATA: terms_no | note_no | note_text 2 ...more >>

Select member from NameList Inner join Members on (Left(Namelist.NameID,5) = Members.ID OR (left(namelist.SSN,9) = Members.ssn OR (Left(namelist.CustID,9) + '*01' = Members.CustID) where namelist.name <> '' How do I speed up a process like this? Can I create indexes on the memb...more >>

I am working in a project where the business model is complex enough that many common retrieval functions become difficult to develop and maintain in single query statements or functions. I have found the logic is easier to implement (and later modify when the code is no longer freshly remembe...more >>

I've got a table with some datetime fields in it. One field (call it field 1) is of the form mm/dd/yyyy and the other two (fields 2 and 3) are in the form of hh:mm:ss:xx where xx is hundreths of a second. I'm getting the difference between field 2 and 3 using (datediff(ms, access_time, rele...more >>

Hi I'm having update problem. Here is the senario I have to different db server (SQL Server) linked with each other In DB Server 1 I have 2 tables & In DB Server 2 I have 3 tables. I have joined tables with each other first & then between the servers When I run select on DB Server1 with bo...more >>

How can I write a query to return non distinct results?...more >>

I am tracing a SQL Server 2000 production server that gets a query about every second. The Event I chose to watch was "Lock:Timeout". To my surprise I see many of these come through the trace. Is this normal behavior? What is "Lock:Timeout" showing me? Thanks, John ...more >>

-- ============================================= -- Create table with IDENTITY column -- ============================================= create table logviewer ( idLog smallint IDENTITY(1, 1), timeLog, Char(14) NOT NULL, dateLog, Char(14) NOT NULL, typeLog, Char(14) NOT NULL, adressLog, Ch...more >>

Dear Mr Portas and Mr Sommarskog, Cause I am a newbie in sql, can I ask for your email address? My email Address is mike@ptmb.com Thanx Best Regards Michael -- Message posted via http://www.sqlmonster.com...more >>

Friends, I am a newbie in sql programming, I don't know much about the programming. I 've tried to make a trigger but it failed. Where can I learn about it and rather free for payments. ( Sorry about that statement, cause it rather expensive for my revenue) -- Message posted via http://...more >>

I have a feeling I'll be forced to use a script and a trigger for this type of field format but I'm wondering if any of your wizards could point at a simple way I could do something like this: For example, if I want to be able to keep track of new orders following this incrementing convention:...more >>

Hi All. My question is this. I have a complex stored procedure in SQL Server which works fine when I run it in Query Analyzer. However, when I call it within my ASP script, it returns nothing, and sometimes locks up. If I change my script to call other existing stored procedures it works fin...more >>

I have experience with MS Access, but I am an absolute newbie when it comes to MySQL. I was looking over the site, mysql.com and found the following downloads: MySQL Database Server Generally Available (GA) 4.1.10 Alpha 5.0.2 Documentation MaxDB=99 by MySQL Generally A...more >>

www.shanje.com does sql server hosting, on shared servers, at a reasonable price. It seems. They also allow client connections. Just playing around I've managed to connect an Access Data Project to a SQL Server database, and to access the database from Enterprise Manager (running locally). ...more >>

Hi, I have a select query that returns three integer fields from a table the values range from 0 to 5. On each row I would like to calculate the average value in the three fields however, the difficulty is that only the rows where the value is greater than 0 should be included in the calcula...more >>

We have an application with a SQL Server 2000 back end that is fairly database intensive -- lots of fairly frequent queries, inserts, updates -- the gamut. The application does not make use of performance hogs like cursors, but I know there are lots of ways the application could be made more ef...more >>

Hi, I wish to create new rows of data based on a source table. Example: I have a file that contains a SESSION (time roughly a calendar quarter for a University), START_DT, END_DT. I want to create rows that would be for each session and each day, so for session 200102 that starts 09/10/2000 and...more >>

Hello All, I am attempting a bulk load of fixed position flat file data via bcp and I have noticed that I get a Right Truncation error when trying to load a row where the last column value is NULL. For example: Flat file row: 0000016M FMT file: 7.0 3 1 SQLCHAR 0 7 "" 1 RECORD_KEY ...more >>

Hi, Problem: I need to get the value of auto-incremented field from just inserted record In Oracle this is INSERT .. RETURNING command. In SQL Server there are @@IDENTITY, IDENT_CURRENT, SCOPE_IDENTITY - @@IDENTITY returns the value from the very LAST insert on any table involving ...more >>

I have a location table that contains all North American zip codes, the city, county, state etc.. each record has a GUID. My problem is that several places in the US you will have a single city and zip code that crosses a county line. So if you need to match this table to another in a select ...more >>

Hello all, I need to do the following task repeatedly. I have been doing it manually until now and I am thinking if there is any way I could automate the whole process. Here is the task: For a selected group of tables, I need to create an Excel file with one worksheet per table. The worksh...more >>

Hi, I'm looking for stand alone installation for SQL server Client tools, or the files required for creating such an installation. I would like to include this installation as part of the setup for my own SQL-dependant app. Any assistance would be greatly appreciated. ...more >>

I'm currently running Profiler sessions to track down Lock Timeout problems. My Profiler view contains (amongst others) the dbid column. Much of the time, this displays familiar dbids, such as 2 (tempdb) and 5 (my main user db). However, it also regularly displays IDs of 0 and 132. Usin...more >>

Hi, I am Just beginning to get to grips with SQL Server v 7 security on a stand alone win 98 PC. I have created a login which only has access to the pubs database and when I try and connect to any other database (while using this login) I am correctly told that I don't have access. However,...more >>

Hi, I would like to check if a string value exist in a string in sql server stored procedure, e.g. set @testString = 'this is my test document.' if (@testString contains 'test') begin ..... end How do I do this in sql server stored procedure? Thanks, June......more >>

Hi, I'm currently implementing a database with a tree structure in a table. The nodes in the tree are stored as records with a column called "Parent". The root of the tree has a "NULL" parent. The path to each node is stored in the column "Path" and is of the form "\000001\000002\000003\...more >>

I have a table containing typed log entries. One log entry is supposed to be created every twelve hours, but sometimes there are gaps. I need to create a report showing the time of entry, and the actual log entry. I can't just list the contents of the log table, because if I do that there wil...more >>

I am using ASP to insert/delete/update rows into a very simple SQL Server database (2000). When a certain amount of text (as little as 1000 chars) is inserted to the table (the insert works fine) ANY update call to that row will time out. I can set the time out for 5 minutes and it still times ...more >>

I don't know enough math to demonstrate that any numerical operation with a null should yield a null; although I would guess that it's true. I just don't buy it, however, when dealing with strings and nulls. In a simple table with first, middle and last name columns, I would infer that a null va...more >>

Does anyone know if it possible to make a specific table invisible to a user when they are in Enterprise Manager? ...more >>

I'm working on a fairly complex query, and to avoid exploding my brain any further, I'm going to keep this simple with an example of what I'm trying to do... Let's say you had a table of customers and a table of orders And you had data that looked like this: Customers CustID CustN...more >>

I don't like the fact that any user I create has access to system tables in the Master databases and the ability to issue 'sp_who' plus God knows what other system stored procs. Is there a white paper or a script to lock down a user so that they do not have this broad access without break...more >>

Okay guys heres the senario. I have written a kick butt asp application that allows me to test sql statements and manage/display all my databases from the web but I have a feature I want to include that I can't figure out how. In Enterprise Manager, one of the column editable properties is th...more >>

I am having a problem backing up my database and TLog files due to a lack of local diskspace. The db file is about 30GB and the TLog is about 20GB each on a different hard disk. Each disk doesn't have enough available space to accomadate a backup. I also can't shrink the files because part of...more >>

I am very new to Transact-SQL programming and don't have a programming background and was hoping that someone could point me in the right direction. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIs and want to ORDER BY 'IOI's'. I have been combing through the BOL, but I don't even know...more >>

I have been working with SQL for a while...but I am stumped. I can not seem to get my arms around this query....can anyone help... Here it is: Table = 12 rows, 4 columns (id, name, amount, date) row1 = 771, "steve", $50.00, "01/01/2005" row2 = 772, "steve", $100.00, "01/11/2005" row3 ...more >>

Hi all, I need to export tables out of a Pervasive DB and into SQL Server 2K. I have set up a DTS Package to do this when a user visits a web page (which will then allow them to view a up to date report using MS Reporting Services). Currently my DTS package checks to see if the table exists...more >>

Hi I have a query as follows select * from ( select CompanyID , TickerSymbol , CompanyName, dbo.FormatNumber(LatestClosingPrice,2) as 'CurrentPrice' from backscreeningdata3 where dailydate= '12/31/04' and (LatestClosingPrice>100) ) as m0, ( select CompanyID , TickerSymbol , CompanyName, ...more >>

Hi Gurus... CREATE TABLE [Table1] ( [Col1] [int] NOT NULL , [Col2] [int] NOT NULL , [Sequence] [tinyint] NOT NULL , CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [Col1], [Col2] ) ON [PRIMARY] , CONSTRAINT [IX_Table1] UNIQUE NONCLUSTERED ( [Col1], [Sequence] ) ON...more >>

Hi, Can you guys see if there's a solution to this problem? I have a database from which I have to read each record and process that record. New records are being added all the time, so I need to go back and check for new records and process them. However: -- there is no 'identity...more >>

Hi, I have a table called opportunity, included within this table are a opclosed (date) , a companyid (integer) and some other columns. There are many opportunity records per companyid . I am only interested in closed opportunities i.e select * from opportunity where opclosed is not nul...more >>

Yes, it should be easy.... I have a single box hosing IIS and SQL Server 2000. In my ASP script I've tried using the following connection strings to attempt to connect to no avail: "Provider=SQLOLEDB;Server=servername\instancename,1433;Database=TEST;network=DBMSSOCN;uid=TEST;pwd=TEST" "Pr...more >>

I have a table called BidItem which has another table called BidAddendum related to it by foreign key. I have another table called BidFolder which is related to both BidItem and BidAddendum, based on a column called RefId and one called Type, i.e. type 1 is a relationship to BidItem and type 2 i...more >>

Hello, Can anyone tell me how to backup read-only databases? I want to backup the secondary databases in my log shipping pairs. Thanks, TGru *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

I'm sure this is an obvious question but much of SQL is new to me. I have a stored procedure and I want to use the results of a select statement elsewhere in the stored prcedure. The select statement will return at most one record and only one column, here's an example : select top 1 Sequ...more >>

I am trying to divide one number by another and I can't get it to return the right value.... in general I want to see how many fruits I have, then get the number of apples and divide the apples by that total to get the percentage of apples I have Declare @Val decimal(6,2) Set @Val= (...more >>

I am trying to drop a database, but keep getting the following error. "cannot drop database 'blah' because it is currently being used for replication". This db is not currently being replicated, but once was. It is the subscriber side of an old replication pair. Can anyone tell what I h...more >>

I have a series of database objects that represent things such as people, accounts, etc. I have a set of options (boolean) that I need to add to these objects. Normally I would just create a bit field for each one and be done with it. The challenge however is that there could be hundreds o...more >>

Is there an equivalent function in SQL to the 'NETWORKDAYS(start_date,end_date,holidays)' function in Excel ? NetworkDays in Excel returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. This part isn't vital...more >>

I am supplying you with Sample Data:- Initial Classcode SampleSize Average ------- ---------- ------------------------------- ADK SSC 22 3.6800000000000002 ADK TSC 17 2.7599999999999998 ADK TSM 5 3.5499999999999998 ANB FCA ...more >>

1. In SQL Server 2000, even after using round function upto 2 decimals, the operation over a float field gives results upto 16 decimal places. How can we avaoid this? Is it possible to display such results upto 2 decimal places in Query Analyzer? What are the related settings and where to do it...more >>

Hello. How to format field "create" (Jan 12 2005) in sp_helpdb procedure to sth like yyyy-mm-dd (2005-01-12) in SQL? bye... -- __ __ |__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve |__// |__|| | \\ \__// / \\ +------------------------------+ +[ http://buko...more >>

I encounter one weird problem, I have a database with around 7 GB ... when I delete a bunch of data from it, it suppose to reduce the database file size, but weirdly, the file size increase to 8 GB. Wondering why. Is it suppose to be like that? Is it the architecture is designed to work like t...more >>

Hi All, Is there way that commands executed in the query analyzer get logged automatically? TIA Joriz ...more >>

I am running a profiler trace against a database and noticed that the reads column always shows 0. When running the same trace against another machine I get back values in the reads column. I took a query that profiler reported as having 0 reads and ran in in query analyzer wtih STATISTICS IO on...more >>

I'm doing some vulnerability assessments of MS SQLServer installations using AppDetective. On doing the "discovery" proble (to find the instances), I find that I get two "hits" -- port 1433 shows the MS SQALServer instance, but port 1434 comes up with MS SQL Server Redirector. The online bo...more >>

Is there an XP/SP out there that will return a list of files residing in a specified directory? I'm looking for something simlar to Execute master..xp_subdirs N'C:\' But instead of it returning a list of subdirs I want it to return a list of files in that directory. Jeff ...more >>

I have been asked to monitor SQL to tell me when we are performing better than others. Can anyone tell me what kinds of scheduled jobs or scripts they utilize? ...more >>

Hi. I have a DB in which we store dates in yyyy/mm/dd. However when we want to display this date via a web frontend, it needs to be in dd/mm/yyyy. I've declared a function (shown below) which converts between these date formats and returns a varchar(20). This works fine however now I need to hav...more >>

Hi, I am trying to change an SP from dynamic SQL to proper SQL but I can't figure a way to conditionally add extra parts to the statement. How can I do the equivalent of the following? DECLARE @arg NVARCHAR(10) SELECT a.i, a.x FROM aTable a IF LEN(@arg) BEGIN INNER JOIN bTable b ON a...more >>