SQL Server 2000 Can anyone help me with some T-SQL? If I have a parent table with about 10 child tables (ie FK'd to parent). How can I construct a SELECT clause which returns the name of these child tables, given the name of the parent? Thanks Paul...more >>

In one of my SQL Server Trigger I have written code like this.What i am doing is getting the primary key(s) of the table by calling sp_pkeys by passing the table name and insert the data in to #TPKEYS , i am getting the primary key cloumn name using SELECT COLUMN_NAME FROM #TPKEYS an...more >>

I want to see what all tables have been dropped today and by whom. also, we have couple of developers who login into the system as sa , I want to find which developer has dropped a particular table.(is there a way) Help appreciated. ...more >>

USE NORTHWIND SELECT * FROM ORDERS A WHERE ORDERDATE='1996-07-08 00:00:00.000' AND [ORDERDATE] IN (SELECT [ORDERDATE] FROM ORDERS B WHERE ORDERDATE='1996-07-08 00:00:00.000' GROUP BY [ORDERDATE] HAVING COUNT(*)>1 ) ------------------------------------------------------- USE N...more >>

Hi folks (i don't know if this is the right newsgroup for this question, if so, please forgive me) I have a table ("ORDERS") which looks like this Order Serial FromCty ToCty Price 1000 1 washington NY 500 1000 2 wa...more >>

When I want to find the records that the word "World" is a substring of a certain field I do: Select SomeField from SomeTable where OtherField like '%World%' This quary will find records with "Hello World". I am looking for a reverse solution, how do I search if a that field is SubString of "W...more >>

I'm sure this has been answered many times before, unfortunately my news server only has the last 300 or so messages from this group. I have implemented Tropashkos' nested sets and materialized path algorithms for my tree structure - I can see how I might insert a new node after the current ...more >>

hi i insert a row in a table, the key of this table is a field set with identity on, then when i insert a row i dont know the primary key of this row. After the insert i need to know the primary key of the row inserted, HOW can i??? Is there a function that tell me the last row inserted??? I...more >>

Is generally bad to store word docs and other binary data in SQL Server 2000? Our current solution stores documents in a file share on the server, but it's hard to do full text searches and ranking if you have some ntext data in sql server and then some word documents in file shares. Could ...more >>

From Query Analyzer, I get the correct results. But from VB, using ADO, am unable to get the results and do not see the problem. The recordset "rs" is always closed after the cmd.Execute call. ===== Create Table if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClaimSe...more >>

I'm Brazilian Developer, so sorry my English... Exists bug !!! where it is? Thanks, Luciano. CREATE PROCEDURE sp_Page_Data @page_num int = 1, @rows_per_page int = 25, @total_rows int output, @sql_string varchar(4096) as SET nocount ON begin DECLARE @column_name ...more >>

Hello All, Is there any way to know when (Time/Date) the record was entered in (Inserted/Updated) in a table, without storing datetime value for each Insert/Update statement? Thanks in advance. -- Maher K. Al-Jendasi mkaljendasi@hotmail.com Privacy and Confidentiality Notice: ...more >>

Help I need to convert the following Interbase Trigger to an MSSQL Trigger and I am new to MSSQL. Any help would be appreciated. CREATE TRIGGER ADDPREFERENCES FOR CUSTOMER ACTIVE BEFORE INSERT POSITION 0 AS BEGIN if (n...more >>

i'm looking at a query, trying to see why it takes so long. An obvious culprit is where an Employees table (~1,500 rows) is being run through "Lazy Spool" operator. The output of the operation is over 7 million rows. There is then a join against these 7 million rows. Is this normal? The B...more >>

Since I got such a quick response on my first question I have another. Here's the DLL: CREATE TABLE [dbo].[ctEnrollment] ( [pkey] [int] NOT NULL , [ctId] [int] NOT NULL , [EnrollmentStatus] [int] NOT NULL , [EnrollmentDtStart] [datetime] NOT NULL , [EnrollmentDtStop] [datetime] NULL , ...more >>

has anyone ever encountered a situation where asp.net does not recognize changes to SPs (or sub-components such as UDFs) until MSSQLServer is cycled .... i'm not sure if i am definitely seeing this .... but some evidence is pointing to it ... ...more >>

Ok here's the deal. I'm doing some work for an ISP / telcom. They basically provide communication from the customer (banks mostly) to the terminal (ATM / POS). So if you imagine a typical network, we're looking at something like... Cust 1 Cust 2 | ...more >>

There have two table, company information (tblCompany) and submitted form history (tblSubmittedForm). A company could submit a form many time, which means no limited. I would like to retreive an company information and submitted history from two table whatever that company have or havent submit ...more >>

Hi all, I have a question regarding how to express a certain SQL statement and any feedback is mostly welcome. I have the table below (ENTRYTABLE) which has 3 records. ID ENTRY EXIT ------------------------ 1 10 15 2 17 22 3 25 30 I am trying to write a...more >>

The only reason I can think of for creating indexes on non-indexed columns is to recommend when a column might actually benefit from an index. On an established database where this decision has been finalised then, it seems to me that only the indexed columns would benefit from having statistics...more >>

Can anyone help with this?? Without completey rebuilding the table, or copying it to a new table, is there a way to change a column's name? For example, if I had 15 columns, and the 7th column was named "SubTotals" and I wanted to change it to "CurrentTotals", what would be the best way to ac...more >>

SELECT InventoryListing.PropertyValue as item1, InventoryListing_1.PropertyValue as item2, InventoryListing_2.PropertyValue as item3, InventoryListing_3.PropertyValue as item4, InventoryListing_4.PropertyValue as item5 FROM InventoryListing InventoryListing_1 INNER JOIN ...more >>

Hi, Intention here is to lock records having Minimum Datetime (using XLOCK or some hint) in a table of records as below... the below table has 3 fields, Id, Last_Processed_DateTime & state (not shown below, it is used to indicate state of ID, whether it is idle ('I') or it's already in...more >>

I am not very good at T-SQL and have the following problem. I need to send a message to a given list of users each night with followup and stale items. These items come from database quearies. The easiest way seemed to be a stored proc that could be schedule that would send the mail via XP_...more >>

Hello Group, I have a database named Test1 which I want to restore as Test2. I normally take the backup of Test1 and restore it as Test2 using Enterprise Manager. However, while restoring, the logical file name of Test2 remains Test1 and I am not able to change it to Test2. Please help. ...more >>

I am converting an Access Database to SQL server. There are some issues in my SQL code that are incompatible, I was wondering if anyone could tell me how to do the Access equivelant in SQL server SQL: The following will select the average of dates that workorders were submitted. I use this to ...more >>

A client of ours has a somewhat large database 655MB. The properties are set to auto update statistics, auto-shrink statistics. Yesterday a user went to run a somewhat heavy SP and it kept timing out within 25 seconds. Later that day, it ran perfectly in 5 seconds. I am suspicious that SQLs...more >>

I have a table that has a composite primary key containing 6 columns(5 columns are of type INT and the other one is CHAR(2)). My question is: Is it better to have a composite key or a unique constraint on those 6 columns and have an indentity column as the primary key for the table ? ...more >>

Hi All Is there a way (using queries or extended stored procedure) to find out the server role and database role/rights that an user has? thanks. ...more >>

I am trying to figure out the best way to generate new calls into our call tracking system proactively. Here is the scenario: We have a call ticketing system used to track calls into our HelpDesk. We also use this system for daily/weekly/monthly/etc. maintenance events. I've created an a...more >>

Hello, I added a timestamp column to my tables, and it appears to have actually improved performance. But the timestamp data is in binary. I have been digging around in booksonline (probably not long enough) to find a way to convert timestamp values into something readable, datetime, ...more >>

Looking for a SQL Server script that will display non-dbo object owners. Thank You, Mickey mautry@ev1.net...more >>

Hello, I hope this is the right group for this question. I have a running program written in Delphi, and I have a SQL2000. I want to use a trigger in SQL to activate the program, the program must be running all the time, and I just want to activate a function in the program (actually just t...more >>

SQL 7.0 Is there any way I can make another copy of db rather restoring from the backup. In my case, I just restored a db from last night backup, before applying the changes to it , I want a copy of my db with different name. Thanks Sh ...more >>

I want the sum of the last payments (amount) for all customers. The last payment is with one with most recent date. And if there are more than one payment on the most recent date then the one with the higher paymentid is the last payment. for example in the given data the insert statement that s...more >>

Hi, I have three select statements in a stroed procedure: SELECT dbo.vw_prop_status_pre.BID, dbo.vw_prop_status_pre.ADD1, dbo.vw_prop_status_pre.UNI, dbo.vw_prop_status_pre.[PRE-IMP DATE], dbo.vw_prop_status_post.[POST-IMP DATE] FROM dbo.vw_prop_status_pre INNER JOIN dbo.vw_...more >>

Hello, Im having a problem with a simple select.. My table looks like this; Recipie From To A 1 10 B 11 20 C 21 30 ... How do I select the correct recipie if my parameter value is 15? That should be recipie B.. The solution is possibly ver...more >>

Hi Please help:: I need a reason for an error I am getting::: When running from query analyzer a specific stmt is working just fine, but when I trace it I notice an error. I am running a delete statement from one database that deletes a record in another database on the same database ...more >>

Do you know how to synchronize the data of specific tables for two databases in SQL Server 7.0? Thx a lot....more >>

I am trying to get a COUNT for the follwoing query select i.iIncidentTypeID, COUNT(CASE i.iStatusID WHEN 104 THEN 1 ELSE NULL)AS "Closed Incidents" COUNT(CASE i.iStatusID WHEN 130 THEN 1 ELSE NULL)AS "Open Incidents" FROM incident i, incidentauditlog a where a.iIncidentID = i.iIncide...more >>

Hi, My company is running a SQL Server version 8.0 and I have a database that was imported from Access into the server. Unfortunately, though, in a text field with a size of 2500 some of the records will show the apostrophes (like in it's) and others will show an AE (like itAEs) when...more >>

I am grouping by a date field by converting it to mm/dd/yyyy so that it groups by day and not by time. This works fine, but I want to spit out some text on a rollup line, and as soon as I try to conditionally detect when the date field is grouped, I get a run time error, "A grouping funct...more >>

I have a SQL statement in Oracle and now I need to do it in SQL Server : Select ActualTime,TO_CHAR(ActualTime,'J.SSSSS') as ActualTimeJulian from MyTable J = Julian date (number of Days since January 1 4712 BC SSSSS = Seconds since midnight Thank's in advance for any Help !!!...more >>

I have a simple 3 table SELECT. One of the tables has 234 million records in it. When I run this select looking for the top 50,000 records, it uses my clustered index and works perfectly (19 seconds). When I ask for 100,000 is does a table scan on the large table, bypassing the index. There...more >>

Hi, I am trying to restore a database to another server, then add a user to it. I set up a stored proc with runs the restore, but I cannot then add the other user to it with the same sp, as it has USE statement - so that I can move to the right db to execute grantdbaccess How can I u...more >>

Hello, I want to choose the minimun value (same columns(date_timeen)) of two tables A,B, using a Cursor. How this Can be? For example I have the following part of code: Declare cursor for select min(date_timeen),aithid from tableA,tableB where dbo.tableA.date_timeen=dbo.tableB.date_timeen ...more >>

Hello, up until i started using Stored procedures, i had no problems whatsoever passing date parameters (27/09/2003) as string ('20030927') to sql server. (I do not use an ADP but do everything through Pass Through queries). a query that is performing perfectly well in Query Analyser does ...more >>

Hi there, Trying to find a way of updating a column on the master table when any updates/inserts/deletes that occur on the slave table. The trigger fires and updates succesfully for any inserts & updates on the slave table. The problem is when any deletes occur. My guess is probably w...more >>

does any one know the command for checking someones permissions on a database thanks Shau...more >>

Hi All, I wanted to know whether it is possible to change/alter a system stored procedure. If yes how? Thank you Satish...more >>

Hi Sql Gurus, This is a best prectice question : If a transaction is DELETED from the system (e.g : Order, Voucher etc), what is your best practice : Do you really delete it from the Table (I think No, for audit reason) ? IF NO, on that DELETED/CANCELLED transaction, do you still mainta...more >>

I am using the designer tool in Enterprise Manager. Is there a way to export or copy the relation diagram to another copy of the database? Export does not seem to do it. Thanks, James ...more >>

i am trying to query the net amounts of monies recieved in. I have selected one Account which shows the general problem The Query I am using Is I have made it a selective record - 217524 purly for this example. Table B shows the Invoice number. Table b gives the breakdown of the in...more >>

When I tried to update a distributed partitioned view im getting the error number 4366 UNION ALL view <table name> is not updatable because a partitioning column was not found. I have followed all the rules for the partitioning column....more >>