Hi, In our application, we want to store the data a record was modified. Would it be appropriate to use the "Timestamp" datatype? If not, which would be appropriate. For example, if a user updates his/her profile, we wanted to store the data modified.. WestSide... .. *** Sent v...more >>

I would like to insert into Student table from another database (as follows). The problem is I encounter duplicate key (primary key is student_id) error when inserting the record. How do I change this to perform insert if record not exist, perform update if record exist? insert into Student ...more >>

Dear all, I am trying to create a View, which will summarise the data in one table called tblPatients. The important fields are Sex (char(5)), Weightkgs (smallint) and DateAdded (smalldatetime - default getdate()). I would like to create a view which summarises the date as follows: Sex: Ma...more >>

I have a query that needs converting to a stored procedure...goes something like this: Select field1, field2, field1 + field 2 as field3, field3 + 5 as field4, field4 + 492 as field 5....etc. There are about 6 actual fields, and a number of fields that are calculated ...more >>

I need someone to answer a few questions that I have. Nobody seems to want to help me on the other newsgroup. Can you please answer a few SQL questions? Here are the questions: 1. Say you have a drop down list based on a SQL statement, want limited by the values in another control on th...more >>

Tryin' to do something relatively simple but I'm just drawing a blank. Before an INSERT takes place, I want to check another table. The other table must have the primary key already existing AND one other field must be NOT NULL. If either of these fails, I want to stop the INSERT. I know ...more >>

<< Situation >> There is a view like this: create view v_test(PK_x, x1, x2, commonValue) as select PK_A, fieldA1, fieldA2, commonValueFromA from table_A union all select PK_B, fieldB1, fieldB2, commonValueFromB from table_B union all select PK_C, fieldC1, fieldC2, commonValueFromC from tabl...more >>

I need a query that will insert an autonumber into a table. I haven't developed the table structure yet because I don't know which datatype to use. I have been trying with a varchar. Let me give an example of what I need the autonumber field to look like. Field 1 ------- 001 002 003...more >>

Hi there, Well, what I want should be Quite simple, but I'm having quite of a = Hard Time to complete this task. Let's Say I have the following tables.. ID_Order and Customer_Name are Primary Keys in Both Tables, but = they're also FK in the Order_Items table -- ***************...more >>

I am not a DBA< so forgive me is this is too easy, but it's not easy for me. Doing ASP, I run this query to build a dropdown list (also known as a <select> box): SELECT EmployeeID, FirstName + ' ' + Lastname FROM Employee ORDER BY FirstName It gives me a long list, most of which is displa...more >>

hi i have this query SELECT * FROM CUSTOMER i want to get the rows between 5-15 in example any one cane help me to solve this problem ...more >>

Hi, Can you someone tell me if SQL Server 7.0 can talked to SQL Server 2000 and vice versa. Thanks...more >>

Hi! Is it possible to maintain a relation between two tables of two different databases. can you use triggers to check if the integrity holds or not. by replication you can maintain the relation. Is there any other way??? Thanx ...more >>

I have a SP (Stored Procedure) that pulls several columns from a single table. The rows returned are determined by the input variable of the SP. The WHERE clause is used to determine which rows to includes by using IN(x) where X is the primary key value of the row and x is the value passed t...more >>

Howdy - I'm trying to 'map' my knowledge of Teradata to SQL Server - sometimes a worthwhile exercise, others not. My question is about performance of stored procedures. Is it a valid assumption that the procedure cache holds the parsing engine's plan for the SQL in the procedure? If s...more >>

Hi, just wondered if anyone here knows a way to programtically retrieve the default values (if any) of the parameters of a given proc ? Tool to retrieve is not mportant (SQLDMO, SQL, ADOX, etc). Example: create proc proc_test @param1 int, @param2 int = 3, @param3 int out as begin s...more >>

What do I need to do to access data from a stored procedure within a view? Let's say I want to join information from one table, to information derived in a view. -jeff...more >>

Hi!, I have a table with an identity column, in some special proceses is requested to disable the identity option using SET IDENTITY_INSERT ON/OFF. Is there a way to give to a certain user the posibility to execute SET IDENTITY_INSERT without giving all the permissions of ALTER/DROP/CREATE a ...more >>

I want to move my .mdf file from one drive (E:) to another drive (C:). Can I do it without backup/restore? WHat are the steps? ...more >>

I bought a book for study For the first time I installed SQL Server 2000 Entreprise Editon. In the book it consists the information " Remembers that, for if conetar to SQL Server, a password will be demanded. The password standard of the healthy user is in white. In that installation " scre...more >>

Each customer has one or more sales records, and each sales record has a date. I need SQL to deliver one sales record per customer, choosing only the earliest-dated record for each customer. Have tried various combinations of GROUP BY, HAVING, TOP, subqueries etc - it seems so damn simple, ...more >>

Hi, I would like to know how I do a calculation in SQL if one of the values I am retrieving can equal NULL. See below: --------------------------------------------------------------------- SELECT TotalPrice = SUM(sc.quantity * p.retailPrice) --TotalPrice = SUM(sc.quantity * p.retai...more >>

I am trying to generate a query that will give me a top 5 report of calls anwered by our drivers. It almost works, it does give me the top 5 the problem is that there really should be 7. The bottom 4 records all have 35 as their number but only 2 show up. Is there a way to get all there record...more >>

I got some SP's from a book called "The Guru's Guide to T-SQL" and I get this error: Executed as user: myserver\Administrator. Incorrect syntax near the keyword 'and'. [SQLSTATE 42000] (Error 156) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Incorrect syntax near the key...more >>

ok, i have these files needing to go into these tables in sql server 2000: Program.txt = TBL_Client BatchGroup.txt = TBL_Group Catalog.txt = TBL_Catalog Item.txt = TBL_MasterItem ItemSold.txt = TBL_ItemsSold Seller.txt = TBL_Seller they will always have the same format...something lik...more >>

I have 2 tables: orders and orderProduct: Orders: orderId status orderProduct: orderId productId quantity date Now I must select the latest quantity of product, if status of order is 2 or the second latest quantity of product if the status of order is 3. For example: ...more >>

I seem to be able to use the default parameter syntax in declaring a UDF, but when I call it from the Query Analyzer, it still requires the optional parameter! >CREATE FUNCTION strClean > (@tx as varchar(255), @nLen as int = 0) > RETURNS varchar(255) AS >BEGIN > return (@tx) >END ...more >>

I am rewriting some code that I want to run in stored procedures. The gist is to cycle through a work table and update several fields for each record based upon what is determined by other fields. (There is only about 10 fields in my table) I don't know alot about cursors. But should I use ...more >>

Hello, I have scheduled the attached sql script to run in the SQL Server Agent. I have tried twice so far and get the following error message: "The job failed. The Job was invoked by Schedule 175 (Sunday). The last step to run was step 1 (Perform Email). NOTE: Failed to notify 'Chuck Ki...more >>

Is it possible to use an if statement in a view? example of what I would like to do: select contribid, sum(iif(contribtype = 'mon', amt, 0) as moncontrib, sum(iid(contribtype = 'prd', amt, 0) as prdcontrib from hcontrib IIF does not seem to work when I am generating the view. Any sug...more >>

Hi, Can someone please provide an example of inserting into a table from a table in another database. i.e. use dbName1 go insert into Table1 select * from dbName2.Table1 Thanks Mike ...more >>

Hello all - I'm wondering, is it possible to create a stored procedure that would allow me to customize the order by clause with each run? Example: Table = EMP Columns = Last First Sex ...more >>

I have two window 2003 servers machines, each one has sql 2000 server installed in it. BOTH servers are on same domain!! I have a trigger on SERVERB, so that everytimes something get updated/deleted/inserted on manager table, it will fire the trigger and delete same thing from ma...more >>

the bulk insert command looks like it would only work in a c program...is there a vb.net version? thanks, trint Net programmer trintsmith@hotmail.com *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

i currenntly have sql server7.0 and sqlserver2000 on two different boxes. The sql server is a more controled environment and i have been asked to move databases from sql server7.0 to the 2ksqlserver. the 7.0 databases are being accessed by several webapps and a windowbased application....more >>

Dear All, I am an Access designer more used to designing Crosstab queries with the syntax TRANSFORM...PIVOT, however when I look at SQL Server BOL, there is little mention of TRANSFORM as the T-SQL for creating Crosstab views. Can someone advise me of the syntax I should be looking at ...more >>

The bug described by KB Article 200299 seems to apply to SQL Server 2000 as well. Here is how to reproduce: set datefirst 1 select datename(ww,'29 dec 2003') returns 53 instead of 52. I would like to know how to make sure this gets fixed asap. Apparently MS is only working on a fix for...more >>

I have a table with a column of type text storing xml documents. I need to pass the contents of this column to stored proc sp_xml_preparedocument, all coding in Transact- SQL. Anyone know how to do this, bearing in mind you can't declare locals of type text? (I don't want to have to do mani...more >>

im having difficulty creating multiple relationships between tables. i have a main table Name PROJECTS: COLUMN DATA TYPE ----------------------- project_id int(IDENTITY) Primary Key skill_1 varchar(50) skill_2 varchar(50) skill_3 varchar(50) skill_4 varc...more >>

Hi, I got a problem with BIG5. I want to send out an INSERT statement containing chinese character ( BIG5 ) via API to SQL Server database ( chinese version ). so, take a simple example, create table table1 ( c1 char(6) ); INSERT into Table1 values ( '<variable>' ); If the vari...more >>

The users of the application I work on want to be able to perform queries for themselves. For me that is ok, as long as there is no performance-loss for other users. Is there a way to predict the CPU-time the query will take in Sql-Server 2000? I know that with the option "set showplan_all on" I...more >>

Hello!!! I have a problem with this stored_procedure: I declared some variables in my SP and I made a select that returns some data of a certain activity as for example her name. The select is the following: SELECT @strBody = dbo.fn_BodyMail(IDActivity,ISNULL (Activity,''''),ISNULL (Desc...more >>