Hi, I'm maintaining a couple of identical (in structure) SQL 2000 databases and I'm having problems with just one of them. After a couple of days views start to get slow and result in timeouts. I checked the entire W2K terminal server system and the database but there's nothing funny excep...more >>

How would I create a table that logs any changes that happens to another table? Like field that has changed, time of change. Help appreciated. Thanks. Frank *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

How do I use a SQL Server 2000 database at home if someone gave it to me on CD ROM. The CD just has the DB.MDF file only. My SQL Server 2000 at home has never had that particular database before. Should I use the SP_Attach_DB somehow? Help appreciated. Thanks. Frank *** Sent via Develop...more >>

Hi All, This is the addition to my previous question about "index parameter". Note: special thanks to Erland Sommarskog for previous advice. I have just tested 5 queries below using query analyser: 1. select * from tb where c = 'x' 2. select * from tb where a = 'x' and c = 'x' 3. selec...more >>

We have multiple(about 20) tables in our database where multiple individual users (programmatically, nothing to do with sql server security) or groups can get rights on records. every table of us has a uniqueidentifier as primary key(we need that and a few other fields for replication reasons). ...more >>

CREATE TABLE [dbo].[LOG] ( [TYPE] [smallint] NULL , [TIME_STAMP] [datetime], [ID] [varchar] (44) ) ID is non-unique. I want to select all IDs where the last entry for that ID is of type 11. Below is the query that I used. Notice that the subquery used is a nested (not correlated...more >>

Does anyone have an example of an SQL query which returns rows for the year-to-date, but where the "year" commences on August 1st? e.g. select * from mytable where datefield > last august 1st TIA for any help Isabel...more >>

I have written the following trigger alter trigger updatecustomerorderreleasedatewhenstatusisreleased on CUSTOMER_ORDER after update as declare @CUST_ORDER_ID VARCHAR(15) select @CUST_ORDER_ID = ID from customer_order if update(STATUS) insert into CUSTER_ORDER_RELEASE_DAT...more >>

I have a primary key that comprises 2 columns (lets say ReportDate and Symbol). I know that if I submit a statement like SELECT * FROM T1 WHERE ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index. But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'? Do I need to...more >>

Hi All, I have read about deadlocks here on Google and I was surprised to read that an update and a select on the same table could get into a deadlock because of the table's index. The update and the select access the index in opposite orders, thereby causing the deadlock. This sounds to me a...more >>

Access 2k -> SQL Server 2k My client has an app that is A2k FE with A2k BE. They have asked me to move the BE to SQL Server. I have a bit of experience with SQL Server, and I'm happy with scripting the database etc. However, when it comes time to move the data itself, I have a teensy li...more >>

SQL Server 2000 I need to compare 2 tables as follows: Table 1 - Accounts Acct#, Account Name Table 2 - Ledger (Among Others) Acct#,AccountName I would like to create a view where the account number matches in both = tables but the account name does not. I.e.=20 Table 1: 5000 ...more >>

Hai , As in sql server sysobjects , i know that in access there a system table called msysobjects. But i want to find all the stored procedures, views in Access. Could u tell How is it possiable in Access and i will be more happy to tell the query to access them. I also communicated in Acc...more >>

The installation of MSDE fails and I dont find any solution to get it work. If I start the setup than a windows with the time bar appears and says it will take 1 minute. After nearly 1 minute it closes the window and gives no additional information. But I see that it generates first all the dire...more >>

I having a difficult time here trying to figure out what to do here. I need a way to scroll through a recordset and display the results with both forward and backward movement on a web page(PHP using ADO/COM).. I know that if I use a client side cursor all the records get shoved to the clien...more >>

The following was captured from SQL Profiler during an interaction between Access Xp and SQL Server 2000: declare @P1 int set @P1=1 exec sp_prepexec @P1 output, N'@P1 nvarchar(50)', N'SELECT "totalid","FPSID","IFPSSYSID","FAMLNAME","OTHAGENID", "FromFPW","prevdown","tempifpssysid","upsize_ts...more >>

Hi All, I have a table with an index. It contains column a to column m. There are two queries that often run : Query 1: select * from tb where a = '01' and b = '51' Query 2: select * from tb where a = '01' and b = '51' and c = 'yy' and d = 'zz' If I must create only one index and (a+b)...more >>

I'm curious if there is a quick way to query the date/time a table was last modified? I appreciate any tips or suggestions provided! TIA - Rob ...more >>

Is it possible to have to ORDER BY statements in the same stored procedure? I am trying to use the same stored procedure for two different pages but the data returned needs to sorted DESC on one page and ASC on another. Below is my SP: CREATE procedure sp_getLeads @p_SortType int, @p_Prop...more >>

SQL 2000, latest SP. We currently have the need to store data from a UTF-8 application in multiple languages in a single database. Our findings thus far support the fact that single-byte and double-byte characters can be held in the same DB without issue. However, when holding two sets of DI...more >>

i'm using delphi 7 and have a query in which i'm trying to find names that have an apostrophe in them, i.e. "o'mally". my problem is that when i write my select statement i can't get the quotes right. i get all types of errors no matter what i try. i get "missing right quote", "invalid token" et...more >>

Hi we are considering porting an application from Ingres to SQL Server. Part of the application uses a feature of Ingres called database events. These allow a application to monitor for an event happening in the database, e.g. a user enters a record meeting certain conditions, the database ra...more >>

I have a table that seems to have a bad index. When I do the following query I get inconsistant and needless to say incorrect results. select count(*) from mytable where mycolumn = 1 If I remove the index from "mycolumn" the query works correctly. If I add the index back (even with a new nam...more >>

I was wondering if any of you guys can kindly help me in finding all the different versions of MSDE 2000 that came out since it's first release. I expected to find something similar like Sun's archive (http://java.sun.com/products/archive/index.html) for MSDE 2000 too but failed to find after a ...more >>

Is it better to have a table with 10,000 row or 10 tables of 100 rows? ...more >>

Hi, Apologies for the repeat - the previous mail nad an incorrect reply address. I have been battling with this for days now. If you execute the SQL below you will get the following result: 0x3078303030303030, which is incorrect. It looks like the conversion is more literal than I am wanting...more >>

Hi, I've enherited a big mess, a SQL Server 2000 database with approximately 50 user tables and 65+ GB data, no explicit relationships among entities (RI constraints whatsover), attempt to create an ERD would more than likely kill the relatively complexed app, the owner would want to drop out...more >>

Now, I know that *theoretically* one can have some unattainably great number of records in a table. But, in practicality (and I know "it depends"), how many records can I plan to have in a table in a fairly lightweight (low transaction density) environment? Would ten million be HUGE, or mod...more >>

I want to replicate a database to a subscriber that will be used as a read only copy. The data has to be replicated as close to instantly as possible. To do this I set up a database export of objects and data to populate the subscriber, then I set up transactional replication. To verify that ...more >>

Hello, I am working on a project using SQL Server 2000 with a database containing about 10 related tables with a lot of columns containing text. The total current size of the database is about 2 Gig. When I delete data from the database, it takes a lot of system resources and monopolizes the...more >>

Hi all, I have been given the task of moving an sql database from one server to another. These servers are not on the same network. I know I can detach the database, copy it to a new location, then FTP it to the new server and re-attach it. My question is, is this the best way to do this? O...more >>

I'm storing time series data in a table in SQL server 2000. The table has columns like: CodeEquity, PriceDate, LastPrice. To extract the last price for a number of equities on COMMON DATES I have used the query: select t.LastPrice,h1.LastPrice,h2.LastPrice,h3.LastPrice from Blg_HistoricData t...more >>

I have about 150 people I would like to send e-mail automatically. Each person would get a unique form letter that includes username and password I have stored in a SQL table. Is this possible? Help appreciated. Thanks. Frank *** Sent via Developersdex http://www.developersdex.com *** ...more >>

Hello, I'm writing to see if anyone is familiar with a tool that can update SQL Server code (i.e. stored procedures, views or table modifications) throughout multiple databases. Currently we use Query Analyzer to step through every database and run the code that's being added/edited. As the...more >>

I an trying to install MSDE2000A, but it stops every time before finishing. I have also tried with sql server 2000, but with same problem. Anyone that knows why? Is there something I can do about it? Please help... Kurt ...more >>

Does anyone know how to delete a primary key with T-SQL? triocchu ...more >>

Is there a quick way to import a txt file from an http location manually... and then later automate the process? This is a do-not-call list that consists only of phone numbers. I appreciate any and all pointers!!! TIA - Rob ...more >>

I am using SQL Server 2000. I have a table with, say, 20 columns. I have one procedure which updates all 20 columns at once, accepting a parameter for each column. However, I want to be able to pass any combination of parameters and only update those columns if passed. So I created the sp as...more >>

Can SQL Server generate an XML schema from a database? TIA, TimJowers...more >>

This is probably a simple question, but I'm stumped. I cannot figure out a way to do the following. For example, say I have two tables - one of used toys and one of broken toys. Say I wanted to see a list of all the distinct used toys that were not in the broken toys list. The two tables are...more >>

Our system administrator set up an NT server group in order to allow our users to login to our application via https to our sql server. The group appears as a User in SQL Server when you look at it in Enterprise Manager. That said, I can not see the users associated with the group from Enterpr...more >>

I want to make a user ho have the same rights as the SA user. I have tried to use Sp_addlogin and sp_grantdbaccess I have got a new user but i have only access to master DB. I can use sp_grantdbaccess to every database. Inn this case i have about 100 different databases i need to hav...more >>

If SQL Server is designed for multi processor systems, how can running a query in parallel make such a dramatic difference to performance ? We have a reasonably simple query which brings in data from a few none complex views. If we run it on our 2x2.4Ghz Xeon server it takes 6 minutes plus to ...more >>

Hello, I have installed MSDE2000A on a standalone PC with Win ME O/s. The PC was earlier used on a Lan and has a Lan card. I could install a named instance of the server successfully but the server does not start when I attempt to start it using the service manager. Could you tell me how th...more >>

Hi, DDLs and DMLs: create table #job (jobID int identity(1,1) primary key, jobName varchar(25) unique not null, jobEndDate dateTime, jobComplete bit default(0), check (([JobEndDate] is null and [JobComplete] = 0) OR ([JobEndDate] is not null and [JobComplete] = 1))); Q1 with check constrai...more >>

Hi all, I am trying to convert all the NULL values in a column to "Open". Any ideas?? TIA...more >>

I have the following view (vProcurementPlan) SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am, dbo.tblRequisition.ROS AS ROS, dbo.tblRequisition.ActivityID AS ActivityID, dbo....more >>

Hi all, I have a sproc that uses OpenRowset to an Oracle db. If OpenRowset errors, it terminates the procedure. I need it to continue processing. Is there any workaround for this? Thanks Pachydermitis...more >>

Hi! I'm new to SQL Server 2K and have a very basic question. Will the "Shrink Database" function reduce the physical size of the database file (as seen in Windows) after records have been deleted? The physical database size is currently 1.2 GB but even after deleting all the records, its stil...more >>

Hi I am using the following code in a procedure update bia set bia.behovantal = convert(int, bia.behovantal) + (convert(int, al.multipel) - (convert(int, bia.behovantal) % convert(int, al.multipel))) from al where bia.artnr = al.artnr and al.arthuvudavt = 1 I need a way to set...more >>

Scenario: -I'm brand new to SQL Server 2000 -I have a vendor supplied application running on SQLServer 2k. -I need to log which of my users has seen which data (from a particular table). It's a healthcare privacy thing. -The application does not do this and the vendor has no interest in...more >>

Hi I'm a bit stuck with a SELECT query. This is a simplified version of what I need. I've had a look in a few books and online but I'm definitely missing something. I'm trying to avoid looping and cursors. I'll be running this in a stored procedure on SQL 7. I have a separate query which re...more >>

Hi everibody, it's the first time i post on this newsgroup. I'm Stefano from Milano, italy. I'm a beginners with Sql2000. My problem is this. I run a View using enterprise manager and after less then 20 second it goes in error time out. I run this view using a VB application and the error come...more >>

If, after analysis, I determine that two tables have the same primary key, does that necessarily indicate that I really have two parts of the same table and they should be collapsed into one? Thank you, -- Rick...more >>

In an effort to improve the speed of queries against my main table, I'll be indexing a column whose data type is varchar(50). Would I be better off (better performance) if I changed the column's data type to some numeric type? I would have to update the column's data to accomodate this, but I...more >>

Once I've converted my floats to chars using STR, is there an easy way to put commas in separating the thousands. i.e. convert 53000000.12 to 53,000,000.12 I'm thinking I'll have to do it with a user defined function and the various string functions myself but was wondering if anyone had ...more >>