Why does M$ Query Analyzer display all numbers as positive, no matter whether they are truly positive or negative ? I am having to cast each column to varchar to find out if there are any negative numbers being hidden from me :( I tried checking Tools/Options/Connections/Use Regional Setting...more >>

I'm enhancing a large ASP application by replacing raw SQL statements in the code with calls to stored procedures. I've seen this recommended in many places to increase SQL Server effieicency. Replacing select statements is going fine but I hit a sticking point when trying to replace an update ...more >>

Can anyone tell me if it's possible to use a Case statement in a Where clause, and if so, the proper syntax? J.R. Largo SQL Tools The Finest Collection of SQL Tools Available http://www.largosqltools.com ...more >>

I have an Excel file of 1362 rows and 30 columns. I need to load it into my database to update addresses and phone numbers. Each record has its own ID number, which is defined in the Excel file and in the database. The database is Millenium Version 7.2.1.1. I can't really find any signifigance b...more >>

Here's a tricky SQL question that has definitely driven me to the end of my rope. I'm using Oracle 9i and I need to perform some simple multiplication on a field and then display it with a percent sign using the COLUMN command. Here's the code thus far: COLUMN price format 9,999.99 HEADING ...more >>

Hello, In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys be disabled? If Indexes (PK's and UN Constraints) can be disabled what happens if data is inserted while disable? Will the index be rebuilt when enabled? Thanks, Rob Panosh ...more >>

I attempted to create a view in SQL Server 2000 that Unions two queries. The first part of the query gets data from the local server, the second part gets info from a linked server. (The query works fine in Query Analyzer.) I received this error when I tried to save the query: ODBC error: [...more >>

I am new to Access/SQL server. I need to determine for performance which aggregate functions are not supported by SQL Server. I understand one way is to query the server to see if the function has to run locally or it can run remote. How can I poll the server? ...more >>

There is something very strange going on here. Tested with ADO 2.7 and MSDE/2000. At first, things look quite sensible. You have a simple SQL query, let's say select * from mytab where col1 = 1234 Now, let's write a simple VB program to do this query back to an MSDE/2000 database on our l...more >>

Erland Sommarskog wrote: > sqlserver yeahbaby (Use-Author-Address-Header@[127.1]) writes: > > [ ... ] I'd like to snapshot running queries (preferably with > > query text) at a particular instant via a script, although any > > help to show what queries are running at an instant will be much > >...more >>

I am firing a query like: SELECT TaskName, StartDate FROMTasks WHERE StartDate >= '01-Aug-2003' Now the result comes as: TaskName StartDate -------------------------- Task1 01-Aug-2003 Task5 10-Oct-2003 etc. Now what I want is this: Sl. No. TaskName ...more >>

Hi Guys Wonder if you could help me. Basically I produce an accounts package that uses a SQL 2000 DB as the RDBMS. I always instruct users to login as 'sa' and the relevant password when doing an update to my program, as sometimes I need to do database changes for new stuff. Found that ...more >>

I have to check whether a given date is between a day and a month. A guided tour is only scheduled from november 1st until april 1st. when i want to make a reservation for the tour Today the query should tell me that today is out of range. In the database we store this as seperate fields: f...more >>

Hello, I work for an attorney staff, so basically the whole day they are sending themselves a lot and a lot of papers (Word docs or Excell sheets). We are going to propose them the use of a Document Exchange and Database System (like Lotus Notes or Oracle Managment Content, formerly known ...more >>

Hi, I wonder if someone can answer a quesiton for me: I'm modifying a database with the purpose of adding the new feature of address change history. My model would consist of a table for keeping client name/logon (for a public site) info in one table, and address info in another table because th...more >>

I have a complex statement that is used in a SELECT statement. After all my calculations I need to do an additional filter on the results. Can I use an alias anywhere or is the AS clause just for column display purposes? Instead of: SELECT column1, column2, some_complex_stat...more >>

On MS-SQL 2k I have a created user. Security for the SQL instance is set to mixed. When I attempt to connect to SQL using Enterprise client on another machine on the local network using Windows Auth I get the error: DB Name-Login Failed for user ('null')- Reason: Not associated with a trusted S...more >>

We have a suppliers application that runs a stored procedure (one of many). This stored procedure then calls various other ones etc... and the final number of stored procedures run is 11. Now, some of our users have been having problems and deciding for themselves to CTRL+ALT+DEL the applicati...more >>

Hey Hey everyone. It's me again asking for help. I have an NT sp6 box with SQL 7. It seems the password for SA has been corrupted, so it won't allow me to log in to it. As an NT admin, is there anyway to reset the SA password? Or, can I create another SA account in SQL 7? Can't find any...more >>

I've seen this answered a number of times in the group, but now that I actually need the answer I don't know where to look or what to put in the search. The question is this: I have a query that produces these reults: Col1 Col2 A B A C A D B A B C ...more >>

Guys, I have a 40GB database being hosted on a NAS device (Network Appliance - http://www.netapp.com/). The log file for this database grows at a rate of 5GB per day. So I do a log cutting on this database on a weekly basis to free up space on my NAS device. I have created two separate Q-Tr...more >>

Hi, I have a table called Bookings which has two important columns; Booking_Start_Date and Booking_End_Date. These columns are both of type DATETIME. The following query calculates how many hours are available between the hours of 09.00 and 17.30 so a user can see at a glance how many hours t...more >>

Hello, I am quite new to ms-sql and I have a problem : I want to create an SQL request which would copy serveral records from a table in a given database to another table (with exactly the same structure) in another database (theses two tables and databases already exist). Could you please ...more >>

Hi, I hv the following query that is returning correct values. However, I want to add one more condition to this query. select b.btdnumber as btdnumber, b.btdtrfno as bbtdtrfno, a.btdtrfno as btdtrfnohidden, a.BtdAcno as BtdAcno, a.BtdCertNo as BtdCertNo, a.BtdShare as BtdShare from shrba...more >>

Question: What would be the best way to add carrage returns to a record, and would my method create alot of overhead and wasted space. What would be the best method to minimize overhead and wasted space. Scenario: Server MS SQL 2000 Table name= mitTickets Fields= problem,details, created, l...more >>

{CREATE TABLEs and INSERTs follow...} Gents, I have a main table that is in ONE-MANY with many other tables. For example, if the main table is named A, there are these realtionships: A-->B A-->C A-->D A-->E With one field in Common (Person). The tables B, C, D and E are History table...more >>

I am running SQL Server 7.0 and using a web interface. I would like for a user to be able to input multiple values into a single field with some sort of delimiter (such as a comma). I want to pass this field into a Stored Procedure and have the stored procedure use the data to generate the res...more >>

Hello, I have a column (text datatype) and has to send an email as a text (not attachment) using cdonts. I am reading the data from text column storing in a varchar field and saying cdonts.body = [data]. This way I can send email to as a text format. Now, my problem is when length of data i...more >>

Hi, I am making some alterations to my Database. I have a table called projects and a table called Work_Types. Projects currently contains the name of the work type (Work_Type) but now I want to change this so it contains the Work_Type_ID, is it possible to update Projects with one query? T...more >>

Hi There, I have an update statement to update a field of a table (~15,000,000 records). It took me around 3 hours to finish 2 weeks ago. After that no one touched the server and no configuration changed. Until yesterday, I re-ran it again and it took me more than 18hrs and still not yet fini...more >>

Hi; I have been writing a lot of quick and diryt tsql scripts to correct database issues. I've been saving the output of the scripts for future records. I have been executing them through query analyzer. I find the messages query analyzer puts out to be obscuring to my output: ie "...more >>

Hi; I have been writing a lot of short tsql scripts to fix a lot of tiny database issues. I was wondering if a could make an array of strings in tsql that I could process in a loop, something like array arrayListOfTablesToProcess = { "orders", "phone", "complaints"} for( int i = 0...more >>

How can I get a list of all SQL server logins that are on a given sql server? What sql query would get me that? Or is there an sp that gives me that. Also, if it could list what db they have rights to that would be great also....more >>

Hi due to big problems (basically since a couple of month iis service crash several times a day) i need to format a web server (win2k, sp4, iis5.0) where all our sites are hosted . To avoid downtime i would like to transfer them all on the new server. Do i have to recreate every single web (w...more >>

I've created a SQL View in SQL 2000 using a table that has columns defined as Numeric data type. In the view, I am grouping and summing. When I link the view via ODBC to Access, all of the data types are changed to text. If I link the original table which the views are created from to Access,...more >>

Hi, I've read conflicting articles on updating an ntext field in a column. My ntext field will exceed 8,000 characters (typically twice that size -- but just a text string). One article (I think from MicroSoft) said you could NOT use ntext in an UPDATE statement, but I've seen examples from...more >>

Hi, I've been reading all sorts of info on the ntext field. I need this to store xml documents in sql server via a stored proc. Because of its size, I apparently can not use SET (as in UPDATE) therefore I'm trying to do an INSERT of the row with this field (after deleting the old row). CREA...more >>

From previous postings I have read it seems that I cannot create a function that accepts a variable list of parameters. Seems that SQL Server does not support this. A few questions: Is this true? What is a workaround? Then how does the function COALESCE do it? Cut and pa...more >>

Hello Everybody! I have a POLINE table on a SQL Server 2000 DB. Before I update the record I need to check that either field, STORELOC or WONUM has data on it. If both fields are NULL I would like to send a message letting the user know that either fields needs data before they can save the r...more >>

Hi, figured out where I was going wrong in my post just prior, but is there ANY way I can assign several variables to then use them in an Update statement, for example (this does not work): ALTER PROCEDURE dbo.UpdateXmlWF ( @varWO varchar(50) ) AS DECLARE @varCust VARCHAR(50) SELECT ...more >>

I need to populate a table from several sources of raw data. For a given security (stock) it is possible to only receive PARTS of information from each of the different sources. It is also possible to have conflicting data. I am looking to make a composite picture of a given security using th...more >>

Hi, I'm trying to run a stored proc: ALTER PROCEDURE dbo.UpdateXmlWF ( @varWO varchar(50) ) AS DECLARE @varCust VARCHAR(50) SELECT @varCust=(SELECT Customer FROM tblWorkOrders WHERE WorkOrder=@varWO) When I remove the SELECT @varCust= I get the correct return. With it in, ...more >>

I am trying to get the DTS Execute SQL task to run a simple sql server 2000 no-parameter stored procedure. The procedure runs without error when using SQL Analyzer but DTS Execute SQL reports "could not find stored procedure "[dbo].[test]" The stored procedure is as follows: create proced...more >>

Greetings, I am seeking information related to this subject. BOL suggests backing up the active transaction log immediately after a failure, so that the backup can be used in a recovery scenario if necessary. This is the relevant text from BOL "Transaction Log Backup": ----// The transac...more >>

Hello, I'm currently working on debugging a very large DTS package that was created by someone else for the purpose of importing data into my company's database. The data is mainly user/contact-related data for our customer base. We ran into problems when one import, of about 40,000 rows...more >>

Hi, I want to display the results of the following query in my program. select a.cdecode as cdecode, a.cdename as cdename, a.cdecallid as cdecallid, a.cderecID as cderecid, b.cdecode as bcdecode, b.cdename as bcdename, b.cdecallid as bcdecallid, b.cderecid as bcderecid from shrcodemaster a...more >>

We just installed SP3 and the cursor behaviors changed, does anyone know anything about that? I call this stored procedure from DTS: DECLARE Queue_cursor SCROLL CURSOR FOR SELECT SOURCE_PATH,COMPANY, TABLE_NAME FROM dl.dbo.companies OPEN Queue_cursor FETCH ABSOLUTE @lineNumber FROM Q...more >>

Hello all, I'm a total newbie with SQL Server 2000 and I have a little problem when moving a database form Access 2000 to SQL Server 2000. In the Access database, each table has an auto-increment field. After importing the tables in SQL Server, all the auto-increment fields are turned into "i...more >>

Hello, Currently we have a database, and it is our desire for it to be able to store millions of records. The data in the table can be divided up by client, and it stores nothing but about 7 integers. | table | | id | clientId | int1 | int2 | int 3 | ... ...more >>

Hello there, I have been reading through Online Books and many postings and still am not sure how I should go about doing this. I have SQL Server 2000 table tblENTRY and an AS400 table TKMAST. TKMAST receives data and is maintained by an outside source. I need to get the received data fr...more >>

Both servers running SQL 2000 I have set up on our local SQL server (using Enterprise Manager) a linked server running on our ISP. Just did new linked server and added remote password and login. The following three queries work: insert into LinkedServer.dbname.dbo.Table2 select * from L...more >>

Hi all, I have the following databasedesign : www.marinescu.dk/databasedesign.pdf which i have a nested SELECT on but i need some more information which i don't know how to retrieve. I have the following SELECT : SELECT DISTINCT Resource.ResourceID, Localized.ResourceValue, Localized.Fon...more >>

I can't seem to find a step-by-step guide to how to detect or continuously monitor for long-running queries that is suitable for a comparative SQL Server novice. I know that it is possible to monitor for such with other database products - can anyone enlighten me as to how this is done with SQL ...more >>

Am I going about this the right way? I want to find pairs of entities in a table that have some relationship (such as a field being the same), so I select t1.id, t2.id from sametable t1 join sametable t2 on t1.id<>t2.id where t1.fieldx=t2.fieldx ... The trouble is, this returns each pair ...more >>

Knifflige Frage zu Datenbanken: Es gibt Tabellen, die mehr als ein PK-Feld haben; also z.B.MAID und GMID. Soweit ok. Nehmen wir an, aus dieser Tabelle sollen keine Datensätze physikalisch gelöscht werden, sondern der Löschstatus durch ein Datenfeld GELOESCHT angegeben werden. Hat das Feld d...more >>

This is probably a silly question to most of you, but I'm in the process of splitting off years from a large DB to several smaller ones. Some of the existing smaller DBs already have most of the data for their respective years. But some of the same data is also on the source DB. If I simply ...more >>

I have the following situation. We sell books on our website, and some of the books have more than one author. So I needed to create a many-to-many table, which is the intermidiate table between the author table and the book table. I can't get the right join statement to work. I've used the c...more >>

I'm running SQL Server 2K (sp3a) and when I run the following query (in query analyzer): SELECT id, LEN(ForeignWord) as Length, ForeignWord FROM Words WHERE Language ='Thai' and LEN(ForeignWord) > 300 ORDER BY Length desc I receive the following results: id Length ForeignW...more >>

Hello, This problem perplexes me and I hope that someone has done something efficient. Take for example the data in the MASTER..SYSPERFINFO: SELECT CAST(RTRIM(INSTANCE_NAME) AS VARCHAR(15)), CAST(RTRIM(COUNTER_NAME) AS VARCHAR(31)), CAST(RTRIM(CNTR_VALUE) AS VARCHAR(10)) FROM MAST...more >>

Is it possible to find out from the transaction log, who deleted a record and when? ...more >>

Hi, I'm trying to do calculations in a SQL statement, but depending on one variable (a.type in example) I'll need to pull another variable from seperate tables. Here is my code thus far: select a.DeptCode DeptCode, a.Type Type, (a.ExpenseUnit / (select volume from TargetData b where ...more >>

Hi, I have a database table contains a field that has data like 1,3/3,0/30,0/2,1/4,2 , and I am trying to make a sql query to query out this record if two numbers seperated by a comma, left side is equal to 1 and right site is greater than 0. Is it possible? if so, how can I do it? Thank...more >>

I have a stored procedure that inserts one row into a table. From a SELECT statement I would like to call the SP on each row in the results. Is setting up a cursor and using fetch statements the best way (or even the only way) to do this?...more >>

My understanding is that in a stored procedure (or any code for that matter) if an error occurs you can detect it by checking @@error variable and raise your own error with raiserror statement. The problem is that the original error is not suppressed. For example I received the following outpu...more >>

Hi, I would like to create a calculated column using the formula section for a table. I am having some trouble doing this. The table's name is ReportParameter. The calculated column's name is tbcalculatedcolumn and tb1 and tb2 are boolean columns in the table. I would like to use an If ...more >>

Hi- apologies for asking a stupid newbie question, but I'm really stuck at the moment. I need to change table ownership. I've got an asp script which is looking for a table owned by the dbo role, however the table was created under a different ownership. I understand the problem, and almost un...more >>

i want get fields description in structure tables that i made tables.please help me for resolve this problem .thank you...more >>