Dear all, I'd like to rewrite this update statement without using a temp table. For each row with duplicate my_id's, the reference_no field should be set to the number of duplicates for that id. When I try rewriting this as a single statement I have problems getting 'at' the calculated dupl...more >>

I am doing research into how to best allow developers to visually add views to a database using our in-house system (.NET Windows apps + SQL Server back end). Since Enterprise Manager's Access-like "New View" component is easy to use, I thought that might be nice to integrate into our syste...more >>

Hi all, I've a local server and a linked server, which I need to update both servers' tables. But I encountered the error "Can't start more transaction in a session'. Following is the structure of the code in the program: ----------------------------------------------------- Begin Tra...more >>

I need to count the total number of patient visits accumulated by each provider in a medical practice between selected dates. I am using the table of charges. Each visit may include several individual charges during the visit, but I want to count the visit only once. Therefore I used this st...more >>

add 2 columns to middle of sql server table Posted on: 03/07/2005 16:00:00 I am basically new to sql server. I need to add 2 new columns to a sql server 2000 database. I do not want to add the columns to the end of the table. I want to add the columns to the middle of the table...more >>

hello, please help me to give me solution i have a table CREATE TABLE T_STUDENT_MT (ID INT PRIMARY KEY, NAME VARCHAR2(50) NOT NULL, FATHERNAME VARCHAR2(50), COUNTRY VARCHAR2(50), STATE VARCHAR2(20), CITY VARCHAR2(30), STREET VARCHAR2(40)) MY DATA IS INSERT INTO T_STUDENT_MT VALUE...more >>

I am creating a stored procedure which is passed a comma delimited string of ids as a varchar datatype. The param is to be used in an SQL statement such as: CREATE PROCEDURE GetFromTable @IDs varchar(255) AS SELECT * FROM table WHERE iId NOT IN(@IDs) GO The problem is that the iI...more >>

Hello, I would like to know how to read the monitor information after running sp_monitor of what i have to do to get more sight in the workload on my machine. What is high and what is low? For Example: after running sp_monitor i get the following information. This information changes a lot...more >>

The other day I updated a table to add a few columns using Enterprise Manager. When I closed the table, it said it would update several other database items as a result (very good, I thought). However, subsequent to this change, even the most basic view is broken, e.g., CREATE VIEW [v_ta...more >>

I need to return all records in which the date 'lastmodified' + 'responsible' which is a number (of days) is the same as today's date. I have the following condition in my sql but even though it should return some records it doesnt: WHERE (DATEADD(day,Activities.responsible , Activi...more >>

Hello I have a remote SQL server. May I make a select of a table in a database from that server? I'm using select * from POMPANO-W.mydb.dbo.mytable and I get the error Incorrect syntax near "-" The server name is POMPANO-W. Thanks in advance Jennyfer ...more >>

How would I return only the 3rd row of a SELECT query? Let's say I start with: SELECT * FROM TABLE1 WHERE ID = 4 And let's also assume I've already counted rows with 'SELECT COUNT(*)' so I know the SELECT will return at least 3 rows. TIA, Jon -- Jon...more >>

If I'm concatenating two columns in a computed column (Formula), SQL Server is automatically making the column a varchar datatype. I've tried using CAST and CONVERT around the formula to change it to datetime, but it gives me an error. Is there a way to set the datatype of a computed colum...more >>

Hi, How can I get the name of the databases from SQL 2000, to a combo box. Thanks Dib ...more >>

I currently use an ISP to provide SQL Server service. I have one database, unlimited bandwidth and 100MB of space. My current service is going to he__. I keep getting periods when connectivity is flakey and the support people are of little help. Anyway, I am looking for a reliable, full featur...more >>

Hi, I got an error "ORDER BY items must appear in the select list if the statement contains a UNION operator." when trying to put an order clause at the end ot the union query. How to set order to the whole set? Thanks. ...more >>

All: I've been building software for a long time (mostly middleware and front end, but have done some SQL as well) and am interested in the use of temporary tables in database development. Under what conditions do temp tables help? Are they sometime the most straight forward solution to an...more >>

Hello, I have a problem with a contains clause. I believe I have the full-text index set correctly. Whenever I put a '-' (dash) character in the query, it seems to return incorrect results. Here is a query that works. SELECT DISTINCT Store_Products.Family As [key] FROM Store_Products WHERE ...more >>

All: Is there a way to tell when the last time a row was added to a table? SQL Enterprise Manager shows the creation table for a table in the standard view... Is there a way to get the "last modified date?" I am trying to analyze what a piece of code is doing (I do not have the source - onl...more >>

Hello All, I was wondering if there are other ways of doing this. I need a way to generate a Sequence number that would increment only when the [StartDateTime] & [EndDateTime] changes. I was able to do it after introducing some tables but I was wondering if there are other methods. ...more >>

I got the following XML format, but I don't know how to pull data out of it. Expect results: CustomValue Value ----------------------------- ApplicantFICOScore 804 Thanks all for your help, Q. DECLARE @xml VARCHAR(8000), @idoc INT SET @xml = '<?xml version="1.0"?> <decisio...more >>

Our app generates a simple-looking "delete" statement for single rows which shows up in the profiler logs as generating 17000 reads, and we know it takes five seconds! It turns out that the table has a delete trigger, and that trigger updates another table that has update triggers, and I think ...more >>

I would like to modified the SQL statement listed below to output the previous hour of data from the present time. The problem is CounterData.CounterDateTime field is in the char(24) format which cannot be changed. If this field was in datetime format it would be much easier. The Count...more >>

Hello all! Could somebody please help me with the following query: I have a table with 3 colums. First column are just identity numbers. Second column contains data in the following pattern: 2 rows of data, 1 null (empty) row and then again 2 rows on data, 1 null row, etc. Third column is a...more >>

Hello, I am writing data to a table using a DTS package from a VB app. One of the columns, nvarchar column, is getting carriage/return char appended to the data. Select '*' + fld1 + '*' From tbl1 fld1 contains digits. I get this * 10* If I do this: Declare @s varchar(10) Selec...more >>

Hello Friends, 1) I have the following SQL which is giving me the error message as listed the SQL. I can run the alter column statement individually but when I try to run the whole block(with the statements preceding and following it), it gives me the error. --33. Table WnBck ALTER T...more >>

Hi, I have this database designed by someone who didn't knew a timestamp isn't just an easy way to insert a usable date. However we needs this creation date for statistics. Is it possible to convert these 7000+ timestamps to a datetime? Thanks in advance!...more >>

I have a backup table that is just a mirror of another table. All changes are copied to backup table using FOR INSERT, UPDATE, DELETE trigger. The question is what happens if original table is damaged because of disk failure (suspect DB state). Is it possible that these "wrong" changes will b...more >>

Is there a way you can query stored procedures for a string in all of the stored procs in a database? For example, if you were looking for "LETTERS" in all of your stored procedures in your Account DB, how would you look for the string? Please post example if you know. -Toco-...more >>

Is is possible to construct a query that will list all the references to the database name that are used throughout the database itself. E.g. It would tell give me a list of all the views/stored procedures/triggers that refer to the database name in them. Example: CREATE PROCEDURE [dbo].[...more >>

when I execute the query, I get warning sometimes. such as aggregation warning... some null rows will be eliminates... Now, is warning part of master.dbo.sysmessages? I think it's impossible to save the warning message to a file or even to database table, right? ...more >>

Help, I’m trying to build a list into a temp table using an if/else statement but I keep getting the error “There is already an object named ‘wktable’ in the database.” Can you tell me why building a temp table using an if/else statement doesn’t work? See below example Decla...more >>

Hi, In my stored procedure I am calling sp_xml_preparedocument to load the xml string from the message table. The sp will be called when a record is inserted in the message table by an insert trigger. If the xml string is not well formed, I want to move this record to another failedmessage ...more >>

I have a view containing column X and column Y and a foreign key F. I want to filter the view so that it does not contain any rows which are in the foreign table, which also contain columns X and Y. I want to do something like this: SELECT * FROM vView v LEFT OUTER JOIN Tbl t ON t.f = v.f ...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 >>

Hi, Can someone please tell me the syntax for renaming a table in a query? Thanks, Steve ...more >>

Hey, Over the last couple of months SQL Server is getting slower and slower. This is due some know factors, like more customers, bigger databases, not verry nice programming and a machine that is 5 years old. Yes, I know i have to upgrade it but before i'll do that., i would like to know what ...more >>

I have a relation with two attributes acting as the primary key. Each key is a foreign key to another table. The requirements have changed and now I need to set the relation with a single auto increment primary key. The existing relation has about 45k tuples. As you might guess, if I add a ...more >>

sorry.. help with chinese character , cocode is nchar(10) int (1), i can see the record of that china company, but in (2) , show nothing.... (1)select cocode,invno ,netbaseamt from arinvinfo order by cocode (2)select * from arinvinfo where rtrim(cocode) = '©v§Q´ä¶ì®Æ' ...more >>

I am using Dreamweaver for the following: Hi, I have a problem with a search/results page, one of the fields is a date, I want to display all results if the date is not entered in the search page and display the records that match the date entered if any. My problem is that if in the resu...more >>

I want to restrict the results of a query only where the field 'email' has something in it. I had a record with data in 'email' and deleted it and my query still returns the record even thoug part of my statements reads: Select * from Users Where email is NOT NULL OR email <> '' Even though...more >>

hi, what is the T-SQL Syntax to remove a login name from the server, is it sp_dropsrvrolemember but i am not sure what i need to put right after the command in order to remove the login name Thanks Ed...more >>

How can execute sqlscript stored in a folder. i.e D:\SQLSCRIPT\cs_posting_exc.PRC What can I write in query analyzer so that I can run that procedure in that folder is a specified server and database. ...more >>

Hi there, I have a performance issue that I am having trouble solving, I am hoping that someone can give me some pointers as to where to look next. Here is the scenario: We have a SQL2000 SP3 Database on our live server. From time to time we have issues with this database where most que...more >>

This query returns 6 rows of data, my intent is to concatenate the values of each row into one varchar variable, if I take the distinct off it works fine, when I add the distinct keyword I only get the first row. Any ideas...? SELECT DISTINCT @WarClaimExceptions = @WarClaimExceptions + '<li...more >>

I need to change a column that is a six character varchar called meeting_date to a datetime value. My plan is to create a table that stores a int value and a datetime value. Copy the primary key (an identity column) and converted the value to a datetime on the insert. Disable or alter all o...more >>

Hello! I am working on migrating an ASP page writen in Visual Basic into C# ASP.NET. How do I convert the following 2 select statements? Thanks in advance. Set rsPPO = Server.CreateObject("ADODB.Recordset") sPPO = "SELECT a.line_no, a.loc_no, a.qty_ord, a.po_no, a.unit_price, a.tot_qt...more >>

Hello all, I have the following temporary table structure which I already populated with a regular query to the main database: CREATE TABLE #MESVENPAR2 ( CONTAID BIGINT, -- ID of Partner CONNAME VARCHAR(100), -- Name of Partner CONZONE VARCHAR(50), -- Geaographical Zone of ...more >>

Hi, Is there a way to stop SQL Server reporting errors in a Stored Procedure? The one I am trying to ignore is a divide-by-zero error? Infact, the ideal solution would be to have SQL Server raise the error so I can detect it in @@ERROR, but to not report it to Query Analyser or any other ...more >>

Hi, How can i create logins on the fly to access a database. I have a login which can access a database through my webapp. The enduser will access also through my webapp, but they have a different login to the database. My question how can this be done? I've tried to execute the followi...more >>

TblA HbdID JID GradeID Hours TblB SalesID PersID HbdID <-----FK TblC HR...more >>