I have a database back-up named SBSI4200.bkp is my C: drive. Please note = that I haven't created the back-up on my machine; I got it from someone. = Now to RESTORE this database, I am using the following code: RESTORE DATABASE SBSI4200 FROM DISK=3D'C:\SBSI4200.bkp' WITH MOVE 'SBSI4200' TO 'C...more >>

Hi, When I excute this view it takes between 10 to 20 sec to finish, if I execute the sql statement in the query analyzer it takes no time to finish using same conditions in where clause. How can that be so? The view calls 2 other views. Lasse ...more >>

Hi, I wondered if anybody could tell me if this is possible in sql via a sheduled maintence plan or backup job or whether I have to implement my own script for it. I ma a server with approx 250 databases on which do not currntly get backed up (not sure how this happend..) most of the dat...more >>

Hi All, I have a database that is modeled similar to Quickbooks. A table called "Names" includes address information for Customers, Employees, and Vendors and each record's key is a foreign key in various other tables. I want to display a list of say Vendors where the user can select what co...more >>

I would like to somehow break a row like this inside the trigger col1 col2 col3 col4 col5 AA BB BB CC AB into a table that would look like this id value 1 AA 2 BB 3 BB 4 CC 5 AB BTW, since all metadata will be in the INSERTED table, how do I go about capturing th...more >>

Hello Friends, When I am trying to connect to SQL Server 2000 from my C# application by providing the following connection string: "server = ServerName; uid = amit; pwd = amitagarwal; database = pubs; encrypt = true"; A MessageBox popped up in my C# application saying "Encryption is no...more >>

I have a quote table and a quoteDetail table They share a ID. What is the quickest way to copy a record in the quote table to the sales table and the quoteDetail records to the salesDetail table. I have to copy the sales record Identity and put it along with the salesDetail records Dave ...more >>

I have created a column for ship date. If ship date is blank, I want to display blank. But SQL is displaying 1/1/1900 if date column is blank. Any suggestion ? Thanks. ...more >>

My data tables have been reorganized and now I have a new problem. My Where clause has to be restructured in a way that is unknown to me. I have columns named: Period, Symbol, Revenue The Periods (rows) are named. Here's the catch; this is a flat table at this point and these Periods repeat ...more >>

I have an application that makes use of SQL Server 2000, one particular aspect of it has ceased working recently. Specifically a report that logs expected income started producing null values. I tracked the problem to a specific query SELECT DISTINCT (SELECT SUM(income.amount) * 12 AS m ...more >>

I want to put commas every 3 digits. for exampe 39298384 should look like 39,298,384 the value I want to show is the type of number. Thanks. ...more >>

-- Lock the Table ???? How to Lock e.g. OutboundQueueItems -- Get recordset (0 to N JobItemdID) from a table select ***JobItemID*** from OutboundQueueItems where JobID = 123 and IsLocked = 0 and AddToProcessing = 1 -- Loop to update () to N) ***JobItemID**** ???? How to get and loop updat...more >>

I am looking for a query (or simple stored proc) to return the max value for all my identity columns ( MAX(IDENTITYCOL) ) in my database. I would like this query to be dynamic in that, I do not need to specify the table names or the column names. If not, I guess could I iterate through the ta...more >>

hi, i am doing a query to get all the tables froma a database so i did this. SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%dbt%' but my next step is to get all the contents from those table results and dump it into a single table. how should i go about it? thank...more >>

When I encounter an error in my trigger, my entire transaction gets rolled back, so far so good. But I want to preserve my transaction and I dont care if my trigger goes wrong. I have tried Commit at the beginning, what this does is that it strops the trigger, I tried a Commit and then a ...more >>

My mind is mushy - hopefully I'm just missing something obvious here. I'm trying to do an update on a table, based on info in a related joined table. In Access, I'd simply run the following query: UPDATE tblCities INNER JOIN tblStreets ON tblCities.City = tblStreets.City SET tblStreet...more >>

I have some weird things happening to data in a column defined as float(8). Values returned from select statments are returning werid values.. ...03 returns 2.9999999999999999E-2 ...09 returns 8.9999999999999997E-2 .....etc. Also I tried to insert values and had diff values end up in the d...more >>

Hey all, I need help trying to come up with a data structure to represent an illustration of a wine cellar. Let's say I have 4 sections in an example wine cellar, Top, Left, Middle, Right. For the sake of illustration, I would use the following html to describe what this wine cellar is suppose...more >>

Hi I have a column called abc and the data is numberic. I have a variable called def and this is also numeric How can I search the database to get records where abc=def. Select @ from table where abc=def - doesn't work Select @ from table where abc=&def & - doesn't work I get error c...more >>

I have a very simple problem: -- TABLE OLDTABLE create table oldtable (id int, code varchar(20)) insert into oldtable values (904,'x') insert into oldtable values (905,'y') insert into oldtable values (906,'z') insert into oldtable values (907,'w') I want to copy all this data to an EXIST...more >>

I am developing an update trigger that checks some stuff on the table and if it meets certain criteria sends an email out. What I want to do is figure out code that will limit the checking to a certain time span. The hours I am interested in monitoring are from 9:00 AM to 5:15 PM, Monday throu...more >>

I'm trying to automate an export process to export a table as a flat text file, and include the column headers (names) as part of the data. BCP will export the data ok, but there are no column names included. Any ideas?...more >>

How can I do to estimate the space disk that I will need to the future in a SQL Server Database?...more >>

Here's my query: SELECT FNAME, LNAME, SALARY FROM USERS U, SALARY S WHERE U.USERID = S.USERID AND SALARY < 50000 ORDER BY LNAME Is it possible for this query to return a counter that is incremented from 1 to n, where n is the total number of rows returned? sampl...more >>

I have a field in a sql table which includes the path of document. The field reads as follows: F:\files\corr\plead\this is my document.wpd. I would like to globally change the F:\ to read as S:\. How can I accomplish this? Many thanks...more >>

Hi, I have ascrript that uses a cursor to loop through all of the databases and back each one up, back up the transaction log and then truncate it. This script works fine, except it always tries to back up the transaction logs of dbs that are in simple recovery mode. my question is can I p...more >>

I hope someone else has had to do this in the past..... I have a vb 6 program that performs a series of calculation on a table in a sql server (7 or 2000) database. It can run from the client side but can be incredibly slow, especially over a poor (256kb dsl) connection. What i would like to do...more >>

I have a problem with sql statement in the query design in MS Access 2000 which is giving me an syntax error in expression error message. My sql statement is: ==================================================================== SELECT DocumentsDR.ID, DocumentsDR.[Document Number], DocumentsD...more >>

Hello I am trying to figure out the best way to keep only 30 days worth of data in my table. Right now I have 9 months worth of data... I need to be able to extract that data and archive it into another table. So that I have the most recent 30 days worth of data. I am using SQL Serve...more >>

H I have a table of, say, articles. Looking something like this datestamp datetime, article varchar, company_id numeri I get many articles throughout the day. I need to produce a report with the latest article for each company. How I trie select max(datestamp),company_i from table group by...more >>

In enterprise manager, when I select Backup, put a tick in Tape and click the Add button, the "Select Backup Destination" dialog appears. I can either select a Tape or I can select a Backup device. I'm trying to code a similar dialog in DMO. I can display the Backup Devices easily by looping ...more >>

Hi All I'm trying to write a trigger and need to figure out how to identify whether a column has been updated. The column is number 12 from the left that I want to trigger an action if it is updated. I want to use the IF (COLUMNS_UPDATED()) but don't know how to calculate the value of the b...more >>

Hi All I'm trying to write a trigger and need to figure out how to identify whether a column has been updated. The column is number 12 from the left that I want to trigger an action if it is updated. I want to use the IF (COLUMNS_UPDATED()) but don't know how to calculate the value of the b...more >>

I have a field in a table which contains a string including Carriage Returns, can anyone tell me how I could separate this field into separate fields based on where the carriage returns are using a simple SQL command. Thanks Michael ...more >>

Hi, Anyone know if it is possible to use Bulk Insert or BCP commands to import a text file into SQL 2000 which contains single or double quotes around string values, and tell it to ignore any embedded column delimiters? 1 Row Example: 9382,"Test, Description",349.00,"N","Joe Smith" (...more >>

I have data as follows in a sql table.I want to split it into columns using case...any idea how I could do this? rmonth QtyShipped material shipto ----------- ---------------------------------------- ------------------------- --------------- -4 0 ...more >>

Hi, When I create a Stored Procedure, View etc, I always SET these two of them to SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO By default, every time, I SET these two to this setting, without even thinking of anything. Am I right setting ANSI_NULLS OFF. Would you please dir...more >>

How do I convince someone that it is better to have a clustered index on (ProcDate, ClientID) than on (ClientID, ProcDate)? Thanks! Daniel ...more >>

Hi, I've been tinkering with the idea of re-designing a database that our company uses and have encountered a problem that I hope someone can clear up... Basically, how should a design account for a relationship that does not always exist. For example, I have a Stock table and a Hazardous t...more >>

In a SP, how can i loop through every User Table and determine if a field exists and add it if it does not? ...more >>

I downloaded the code by Vyas for generating the SQL Insert statements necessary for transfering data from one database to another. While the code is excellent, I am having an issue with a couple of my tables, where the INSERT statements are getting truncated at 8000 characters. Between the erro...more >>

I'm using SQL Server 2K and VFP8. When I save a record, the following ODBC Error Message is displayed. 'Column Name [RefNo] appears more than once in the result column list.' Pls help....more >>

Say I have three records thus (I really have hundreds of thousands, but let's just say): Date Name Address1 Address2 Contact ------- --------- ------------- ----------- ------ 9/21/03 John null null Judy 12/5/03 John Address1 nu...more >>

We have some long running transactions and we want to log the process to a table, but if the transaction fails we want the log records to be in the table. Begin Trans Loop Update........ Insert record in log outside the transaction to report progress or errors End loop Rollbac...more >>

We have 2 servers - Test and Development. On the Test server the Query Analyzer debugger runs fine like the way it should. On the Development server the debugger will not stop on the first statement, even if it has break point. It starts to run all the statements without stepping one statem...more >>

Hi all, I'm a newbie in SQL Server. I just found out that: INSERT INTO some_table (a_char_column) VALUES ("This is a string") doesn't work. Took me almost half an hour to got the idea that I should use single quotes instead of double quotes. Remind me of my days in college with C language...more >>

Hi! I'm writting a program that needs to do the same thing that Query Analyzer does. I'm using Delphi 6 as my development platform. I ran into the following problem when I executed a Query that does not return a result set I get an error. My questions is, is there a way to check the Q...more >>

How do I do a numeric pattern search in sql? What I have is a field with a length of 30 that contains text and numbers. I need to find the zip code in the field. (I've already done a right trim to remove spaces on the right side) For example, field 1 It dept 2 ...more >>

Hi there, Simple question: how do i disable the warning messages in ms-sql 2000 ? I have a script that returns (sometimes) some warnings, and i don't the customer to see this when running my scripts on their machine... Thanks! Etienne ...more >>

i'm modifying a stored procedure that was written in 7.5. and i have varibles that i set to empty like this select @test = "" i pull it into the query analyzer in 7.5 and it works fine but with 2000 I run it and it says... "Server: Msg 1038, Level 15, State 3, Line 85 Cannot use emp...more >>

I need to maintain a copy of all transactions made in a database and who made them. My current thinking is to create another database called MyDB_History and maintain copies of all of the original tables in this database. What I need is everytime table data is changed, I want to copy that data...more >>

I'm using Full Text Search to query a table, when i search for the string 333 no results are returned but when i search for 22-333-4444 using FREETEXT or CONTAINS results are returned. Does anyone know if there is a problem looking for numeric values? The data type on my column is NVARCHAR. I hav...more >>

How can I create a security rule for my database? ...more >>

How can we create a table with one FOREIGN KEY column that refrences a table with two or more PRIMARY KEYS? (I need just one of the primary keys for referencing) ...more >>

1- How can I export a table to a text file (by SQL Script)? 2- How can I import a text file to a table in my database(by SQL Script)? ...more >>

How can enter data from a different table using "INSERT INTO" and the SELECT statement? ...more >>

Hello, all. I'm create a table that'll need to store a Password field. What's the best datatype to use? I've read about "Binary" being used to store confidential data, as it converts the actual data into unreadable garbage. Is this the most common used method? Also, will I be able to ...more >>

How can i get a formated date string from GetDate() to return this string Thu, 11 Dec 2003 17:00:00 +1100 I need to set an expiry date on a cdo message header to +3 hours from request time Don ...more >>

Is is normal to have multiple lines with different CPU Times assigned to the same SPID?...more >>

I'm currently tasked with designing an appropriate way of the change management of SQL Objects and static data scripts. We currently use VSS to manage code and it works well for the usual VS code and docs. However, for the SQL, one or two extremely large and unwieldy script files are used to create...more >>

Hi, How do i convert a given date to fiscal year,week and day using T-sql? any help is appreciated Sh....more >>

Hi All In other versions of SQL (UDB, SAS) it is possible to get the row number returned from a select. Is this possible in SQL server ex select rownum(), nam from mytabl return 1 SALL 2 TO TIA Chris...more >>

I have created a stored procedure: CREATE PROCEDURE dbo.Update1213 AS DECLARE @intErrCode int SELECT @intErrCode=@@error BEGIN TRANSACTION IF @intErrCode=0 BEGIN ALTER TABLE POCompany ADD pcmp_lastorder int NOT NULL DEFAULT(0) SELECT @intErrCode=@@error END IF @intErrCode=0 BEGIN...more >>

Hello All, I have a Database "UserDB". This database has a user "USER_A". He has very minimal permissions. But I want to grant him permission to execute xp_sendMail. Is it possible? I tried this way, but it says GRANT EXECUTE ON master..xp_sendmail TO USER_A Server: Msg ...more >>

Hi - if I have a SP CREATE PROCEDURE Test ( @a nvarchar(32) = NULL OUTPUT, @b nvarchar(32) = NULL OUTPUT ) AS SELECT @a='aaaa' SELECT @b='bbbb' I get the output parameters values, @a='aaaa', @b='bbbb' However, if the SP is: CREATE PROCEDURE Test ( @a nvarchar(32) = NULL OUTPU...more >>

Hi all, I want to define a membership table like (Pyramid). It can be search from up to down or down to up member list. What is the best way to define or use OLAP cube? For Example Level 1 - Tom Level 2 - John Level 3 - May Level 4 - Apple Level 5 - Cat Level 6 - Candy Level 7 - Joh...more >>

What ADODB CommandType should optimally be used to execute SQL 2000 User Defined Functions returning a table? I can't find any documentation on ADO/UDFs and would be grateful for any info. Cheers, Martin ...more >>

We plan to use MS SQL 2000 for ASP.NET application Can we use License "Server plus Device CALs : 667$(Server)+146$(1 Client Device)=813$" Users send request to IIS, and IIS send query to MSSQL ...more >>