MSDE2000 I have an application in which I am running a TSQL command of BACKUP = DATABASE and RESTORE DATABASE for the backup and restore commands for my = application. For testing purposes, i did the following: 1) Ran a BACKUP DATABASE command to a file named C:\TEST.BAK. 2) Deleted the da...more >>

hi, i have a small question regarding sql, there are two tables that i need to work with on this, one has fields like: Table1: (id, name, street, city, zip, phone, fax, etc...) about 20 more columns Table2: name what i need help with is that table2 contains about 200 distinct names that i ...more >>

Hi, After a power outage (and UPS failure), my boss's SQL Server 2000 won't start (or crashes quickly or something). We don't have any backups, as it's a test server. Is there a way to import the database files into a new SQL installation? By "installation," I mean either a re-installed SS, a se...more >>

I made a database to hold recordings of calls made to our customers. When I made it I set the size of the primary datafile to 18GB. It's been running flawlessly for over 10 months. A few days ago the users were suddenly no longer able to save the recordings to the database. They got an error ...more >>

Hello I need to find out what stored procedure was executed when I click on a button in a third party application, is there a way to find this information out in some sort of log? Some kind of profiling to see what procedure was executed and with what parameters. Kind Regards Matt ww...more >>

Hello there! I am trying to install MS SQL 2000 Server on Windows XP Home edition. I found in Microsoft that the above installation is possible. But when i run the installation, i am getting a black screen that is closes after a second. Nothing else happens. Any ideas how to troubleshoot thi...more >>

Hi, I would like to know, if I need to do some floting point operations (mainly multiplication and division) on each roll of a table, should I read the data out from the DB and do the calculation with a programming language, say C#, or should I just use sql to do it on the sql server. A...more >>

I'm trying to find a way to format a FLOAT variable into a varchar in SQL Server 2000 but using CAST/CONVERT I can only get scientific notation i.e. 1e+006 instead of 1000000 which isn't really what I wanted. Preferably the varchar would display the number to 2 decimal places but I'd settle f...more >>

I tried to copy tables from SQL Server 2000 to DB2 UDB by using DTS package through ODBC, but always got an error that says: "The number of failing rows exceeds the maximum specified. [IBM][CLI Driver] CLI0150E Driver not capable, SQLSTATE=S1C00" However, I can copy tables inversely from DB2...more >>

When I create a stored procedure like the one below and save it, everything seems fine. But when I close Enterprise Manager and re-open it, the stored procedure is gone. Any ideas? SET ANSI_DEFAULTS ON GO CREATE PROCEDURE [dbo].[sp_Test] AS SELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT...more >>

I'm using the Transact-SQL Debugger for the first time here. I go to Object Browser, find my stored procedure, right click and choose "Debug". The "Debug Procedure" window comes up, and I click on the Execute button. The Debug environment comes up and automatically executes my procedure....more >>

ok.. here goes.. I have a name field in my table called "bname" all of the data in this field has full names in it, spaces are separated by a "+" ie. "john+doe" i have two other fields that are currently NULL. "First_name" and "Last_name" now my question is obvious. I need to pop...more >>

Hi SQL gurus! My PC crashed but was able to recover some of my data including the directory and folder with SQL Server 2000. I bought a new PC and want to restore some of my databases. I tried to create a new database with the same name and then copy and paste data and log files to a Data folde...more >>

We have one user who enters a transaction and then does a single row update (updates all columns but only one is changing - this is due to the way our sql is generated in the application), at this point another user enter a transaction and tries to update the same row (he understandably has to s...more >>

Hi All, My question is what are the best practices for administering large DBs. (My coworker is the DB administrator. I'm more of the developer. But slowly being sucked in.) My main concern is that we have some DBs that take approx 3 hrs a night just to rebuild the indexes. I know that w...more >>

hello someone could tell me how could I insert SQL statement in a C++ code ? If you know online documents easy to read about this subject, it will be very welcome(in english or in french).. Thanks ...more >>

SQL 7.0 I have a form in ASP.NET and I want to write the values to the SQL Server tables. The tables are Customer and Address tables. There will be an insert into the Customer table, and I need to use the Identity of this inserted record for the Foreign Key in the Address table insert. ...more >>

Here is my code. SELECT [Owner].[First Name], [Owner].[Last Name], [Condo].[Unit Number], [Condo].[Weekly Rate], [Condo].[Linens] FROM [Owner], [Condo] WHERE [Condo].[Linens]=True AND [Owner].[Owner ID]=[Condo].[Owner ID] ORDER BY [Condo].[Unit Number]; I am trying to not ...more >>

Hi When I attempt to install MS SQL Server evaluation copy (downloaded from Microsoft site) on Windows 2K Professional getting following error. Setup initialization error. The system cannot find the path specified. Source file or directory missing or cannot be read: 'C:\SQLEVAL\x86\setu...more >>

I have a table named expense_report. It has over 100 columns in it. Most of the columns are of type "money(8)". I have a form on my website that I created using Frontpage and a custom query. I hade to convert most columns from money to varchar to get the format that I was looking for on the ...more >>

Hi, I am looking for an easy way to get all of the properties from a SQL 2000 database much like the documenter does in MS Access. Any ideas? ...more >>

Hi, I have a simple query that does the following :- select desc1,desc2,desc3,desc4,desc5 from testdata So I select the data for the above What I would like to achieve without having to go to great lengths the following:- So taking the column desc1 I want to insert this into a...more >>

I need a query that looks at one table and appends another if new customer data is added. I think I need an Insert, Select statement using the NOT IN clause. I need to compare Division, CustomerNumber of the two tables. Help, Example Appreciated. Thanks Frank *** Sent via Developers...more >>

[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server Data transfering between two databases in SQL2000. WHAT for the fu...k that means? I went crazy by now also!!!! ...more >>

Is there a length limit on retrieving Microsoft SQL Server text fields? Thanks Siobhan *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

I am designing and product ordering database and have a design question. I have a Payments table which records all payments for a given OrderID. There are several payment methods (cash, credit card, check). Each of these payment methods has their own fields (i.e. CreditCardNum for payments b...more >>

I have a need integrating SQL2000 & VSS. Is there a simple way or any other way to do it? There are some undocumented procedures in SQL2000 which are to help one to integrate SQL database into VSS. Does anyone have documentation about those procedures? Or, is there a tool which makes it easyer?...more >>

Hello, I am looking for help with the following situation. I have created a simple call tracking system that is integrated with our accounting / warehousing program in the company. The call tracking system uses ASP 3.0 and MSSQL 2000 sitting on an Intranet web server. All appears ...more >>

Hello, Newbie of sorts. Been reading up on best practices though. I'm trying to optimize performance to the SQL DB vs. the Web server and an ASP application. I've read that you're supposed to open late/close early which makes sense. In the past I'd created a connection and thrown many ...more >>

I will be doing some performance testing on financial application next month. Without going into a lot of details, I suspect I will have a potential bottleneck when writing to the log file. My hardware setup is a quad 2.8 Xeon Dell server direct attached to a DELL/EMC CX200 (Fibre channel arra...more >>

Greetings all, I'm hoping someone can recommend a web-based reporting software. My database is currently in MS Access, but could be ported to SQL Server if necessary. Would prefer a broswer independent tool with no plug-in requirements. Would also prefer it to be compatible with existing AS...more >>

Hello All, I have been encountering trouble with a SQL Server 2000 Transaction log file, mainly with the constant growth and lack of the autoshrink option. Here are the details: 1.) OS is Windows 2000 server sp3 2.) SQL Server 2000 (Build 2195) sp3 3.) Database Recovery Mode is set to Full ...more >>

Hi, I have a problem with updating a datetime column, When I try to change the Column from VB I get "Incorrect syntax near '942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server] '942' is the unique key column value However if I update any other column the syntax is fine T...more >>

Suppose you have a table in which one of the fields can have any number of the same values. Is there a way to select on that field for only those records where there is a single occurrence of that value in the entire table ? I don't want any records returned by the query if there is more than ...more >>

Hi, Is there any way of telling if a database or table has been updated, altered or deleted from since the last backup was made in SQL server. Windows does not correctly report .mdf file size changes until a database is brought offline so it seems impossible to work on physical file size chan...more >>

On a hung query and using the SP_Lock to reveal 2 contending SPID's: Is there ever a time when you would justify the release one SPID instead of both SPIDs that were contending for the same ObjID? would this depend on the modes the SPIDs hold? Help appreciated. Frank *** Sent via Deve...more >>

....it's possible without any third party application? I need to recover some encrypted user functions but the sources have been lost long time ago, someone can help me? -- Lav. ...more >>

Hello Newsgroup ! I'm working with SQL Server 7.0 and Access 2000 . I build some stored procedures and indexes to enhance the performance of my queries. Now there is another application (blackbox) which imports new data to my only database table. Once it took 1 ms for one file (1kb) today ...more >>

Hello - I am trying to determine the last time a SP was executed. Does anyone know how to do this? I'm trying to cleanup some databases. Thanks! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Hello: How does one pool a connection object? I have the same application running on 4 different machines, all connecting to the same server/SQL Server 2000 instance for DB activity. Some posts have mentioned pooling the connection objects to reduce overhead, but how do I do that for the 4...more >>

Hey... Why do I sometimes experience low performance when I use a parameter instead of an exact value? For example the following performs very bad: declare @TypeID integer select @TypeID = 10 select ID from t_Table1 t1, t_Table2 t2 where t_t1.ID = t_t2.FID and t2.Type =...more >>

I have a table that contains the following: LOANID,PAYMENTDATE,PAIDTAG,PAYMENTAMOUNT -------------------------------------------------------------------------= ---------- 1 01/01/04 0 100 1 02/01/04 0 100 2 01/01/04 0 100 2 02/01/04 0 100 I need to create a VIEW that contains only 1 reco...more >>

I have created a DTS package that imports a comma delimited (CSV) text file into a table. One field uses a VB script that parses a date into the proper format for the transformation. All other fields are copied as is. When I run the package directly the date field is imported correctly (ie MM/DD...more >>

Hello, This is probably a stupid question but I am going to ask it anyway because I don't know the answer to it. Both SQL Select Statements, below, execute and retuurn the same results. Why would on put the [ around the table and column names? SELECT Customer.FirstName FROM Customer or ...more >>

Recently, one of our database's mdf and ldf was corrupted. We were able to bring back the database with the capability of importing and querying the data. However, the data is not the full list. Some of the data are still missing because the tables we want still has sone 'chain mis linkage' err...more >>

Hello, I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db). 1.back-up the entire database 2.truncate the log 3.shrink th...more >>

Hi, DDL: -- create table #task (taskID int identity(1,1) primary key, taskName varchar(25) unique, taskCompleteDate dateTime, taskComplete bit default(0)); /* Business Rules: a) if taskCompleteDate is NULL (like default) then, taskComplete may not be set to 1 (attempt to update it to 1 wou...more >>

Hello, I am hoping you can help me with the following problem; I need to = process the following steps every couple of hours in order to keep our = Sql 2000 database a small as possible (the transaction log is 5x bigger = than the db). 1.back-up the entire database 2.truncate the log 3.sh...more >>

I have a SQL Server 2000 system dsn that uses SQL Server authentication. The DSN does not retain the password field entry I make. I enter the password (the sql login is already there), click next, next then ok. When I reopen the DSN, the password table is blank again. Help appreciated. Than...more >>

Hi: I have a Point-of-sale application that uses SQL Server2000 for the backend. Basically, the users perform various functions boiling down to login (check password from a table) and data entry (insert a food entry). Previously, I would open a new ADO 2.7 connection to the database each time...more >>

Hello, I have an application that will be logging to a SQL Server 2000 database user user activity from several Windows 2003 terminal servers. This information will be retrieved by monitoring the Security logs of these servers (this part I know how to accomplish already). A table in the d...more >>

Does anybody have any experiences in Query analyzer & Source Safe inegration? Please, any suggestion is welcome. ...more >>

I have a table for authors (for our bookstore) that has several fields (firstname, lastname, etc.) and an author_id field (set as identity) I'm trying to import a spreadsheet into this table, but keep getting error messages that say I can't import data into the author_id field (the identityf f...more >>

This subject has been posted several times, but I haven't seen a good answer. Problem: I want to change the order of the columns in a table using T-SQL only. Explanation: After running your code, I want to see the following table... CREATE TABLE [dbo].[TableName] ( [First_Column] [int]...more >>

I'm exporting a large file from a large Production database (SQL). Users are currently updating and inserting new records. Does the export take a snapshot of the data when it starts ? or will it freeze the database so all udpates/inserts happen after the export completes? Also, how do I g...more >>

Hi all, I am using following update command but getting error on database field name. I can't change the database filed name, please give me idea how can I use reserved word in this statement. update ws_apps set database = 'EFORMS' where app_id=4 Thanks for your help...more >>

Hi folks, I got a script which restores a database. It works fine if it is running in my Query Analyzer. It fails when I put this script in an automated schedule using the SQL agent. This is my script RESTORE DATABASE [RestoreTest] FROM DISK = N'E:\sqlbak\RestoreTest.BAK' WI...more >>

Is there a way to increase the number of records preserved in job history? I have 82 jobs on my box, 30 of them run every 20 minutes for 23 hours a day, every 30 days. Another 30 run once, every 30 days. The system seems to have a limit of 50 history records for any job that hasn't run since ...more >>

Hi to all, I ask to everybody which is the best book of the MS SQl Server 2000. I search in particulary a book complete and advanced. Thanks Alex Palermo...more >>

I'm currently working with a 10 million plus row database with the data residing on a Unix box with Cache 5.0. The problems is that it can take five days to pull one table from Cache to SQL 2000 using the ODBC connection provided by Cache in a SQL 2000 DTS package. I think the real problem is co...more >>

Hi All, I'm trying to migrate from SQL Server 7.0 to SQL Server 2000. I've got some ASP page which call VB components that retrieve shaped recordsets from SQL Server using the MSDATASHAPE provider. Precisely, here is the code i have Dim Cmdobj As New ADODB.Command Cmdobj.ActiveConnection ...more >>

i run mysql in windows server 2000 in LAN and when i want ot see mydatabase with phpmyadmin i recieved a an error that was "Access is denied for user:root" my password was correct and when i empty the password in phpmyadmin it work correct why? i set password in mysql but it doesn't work in p...more >>

I have an application written in Access 97 that connects to a SQL2000 backend. One field is a description field that is a data type NTEXT in the SQL database. In my access form, I can not enter more than 255 characters. Before I converted the backend to SQL, the description field was a memo ...more >>

Hi all, I am trying to write a stored procedure that will delete records in a table that are older then 30 days. I am checking against a field that is called PositionDate. Can someone point me in the right direction? TIA...more >>

Yes, I know that cursors are gauche, but I can't see a solution using queries and I'm pretty adept at them. This will be running once a day in the wee small hours when minimal server activity will be taking place, it will be handling 700-1000ish records. The box is a P4/SQL 2000 with lots of r...more >>

Jobs at MySQL AB: / Field Sales Engineer/Consultant MySQL is rapidly growing and looking for high-caliber technical engineers who will work to both support the field sales staff, as well as working within our Professional Services group for onsite and remote consulting opportunities. We offer ...more >>

Suppose you have two (or more) tables with foreign key constraints. My question is thus: Is it better to check if the fk exists before you try to perform the insert or let SQL do it for you? On one hand, if you check yourself and the key does not exist you can gracefully handle it (maybe ex...more >>

I am trying to get a better understanding of when to use return (with a print statement) and when to use raiserror. * Both statements can be used with stored procedures while only return can be used with functions. * With raiserror it is easy to have multiple errors thrown. (If both the cal...more >>

Could someone please tell me how I can trap permission errors in vb.net on a stored procedure: ie. Execute permission denied on object 'sel_mytable', database 'mydatabase', owner 'dbo' I would like to print a message like response.write("You don't have permission to select on this table") as ...more >>

Hi All This is a belter that my little brain can't handle. Basically I have 1 SQL table that contains the following fields: Stock Code Stock Desc Reference Transaction Date Qty Cost Price Basically this table stores all the transaction lines of when a user books stock items into st...more >>

I 'inherited' a group of SQL Server server class machines. They are true server technology but the disk sub-systems are lacking. There is one hot-swap backplane that all the drives share (with one SCSI channel) thusly even though there are three logical drives (composed from 6 to 8 hard drives...more >>

trying to get to the bottom of this for some time...... eventually to be used with asp. heres the problem the following rather complex SQL statement works fine via query analyser: SELECT TOP 100 tbl_LevelDetail.nvchLevelName AS DataLevelName, MAX(CASE tintDataFieldId WHEN '1' THEN CAST...more >>

I'm using MS SQL 2000 server. I have a table which includes a date field which has people's birthdays in it. How can I write a query to return all the records of people with birthdays within the next 30 days? (Based on system date as the starting point.) Actual year of birthday is irrelevant. ...more >>

Hi I take one nightly full database backup at 02:00 and backup the transaction log to one backup set every 15mins. The commands to do this are as follows and are set up to run as database jobs: -- Database Backup BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT , NOUN...more >>

Hi I have inherited a web app with the following table structure, and need to produce a table without any duplicates. Email seems like the best unique identifier - so only one of each e-mail address should be in the table. Following http://www.sqlteam.com/item.asp?ItemID=3331 I have been ab...more >>