Hi keep getting excellent help so I am trying again. Hopefully, we will have all our trigger twists resolved soon. Below is the trigger we are using for a variety of tables but I am now getting an error that I don't understand exactly how to solve: CREATE TRIGGER [SaveDeletedCustomer] ON [...more >>

I am wondering if FUll Text Indexing is suported with MS SQL Server 2000 Developer Edition on XP Pro SP2 . I have the service installed and setup. I have created several catalogs with data but when they are done populating they show up with an Item Count of 0 and a Unique Index of 1 despite the ...more >>

Hi All, I need a suggestion to modify existing code. Is it possible to modify the below code to replace EXISTS clause with a UDF where function can use SET ROWCOUNT 1 etc to return me true or false rather than me using EXISTS AND NOT EXISTS logic here. What are the alternatives to avoid ...more >>

Hi all, Set up an import earlier using the DTS Import Wizard - it created the DTS and I ran it immediately. It rather surprised me by managing to do nearly everything without an error...until... :o) Ok - so it created all of the tables, populated them with the content. It created all of...more >>

I'm puzzled: I need to use a CASE statement in a view I was designing with Enterprise Manager. But the EM informs me that the CASE statement is not supported in views???? -- sam...more >>

How should I write (look at the sample data) a SQL Query to get the two top user amounts, NOT top by table, but top two by client CREATE TABLE sample ( transaction_id int PRIMARY KEY, clinet_id int NOT NULL, amount int NOT NULL ); INSERT INTO sample (transaction_id, clinet_id, amoun...more >>

Because I cannot configure each call to xp_sendmail with a @from parameter (@from is not a parameter of xp_sendmail), I wish to process inbound mail to my SQLServer's Exchange account searching for "invalid recipient" messages using xp_findnext and xp_readmail. Ironically, I can read every messag...more >>

As SQL Server runs, it caches data in memory. While this is normally efficient, it's cumbersome for my laptop setup as a dev / testing server. As I test my application code, I would like to periodically dump the cache to regain some memory back. Is there any way to do that without restarting...more >>

Hi How to I create an SQL agent Job which runs a stored procedure on a number of named databases? I.e. not just one database which I can easily set up from enterprise manager. All the named databases will have the same stored procedure. Many thanks Andrew ...more >>

I have a DTS that runs fine when executed at the package level. However, it always fails when it runs as a scheduled job through the SQL Server Agent. It always fails at ~93 seconds . Anybody else experiencing this? Here is the text for the failed scheduled DTS: The execution of the f...more >>

Hi, I encountered with a curious behavior of Foreign Key Constraint in a company that I was writing some Stored Procedures for them There was two tables. The child table references parent table with a Foreign Key Constraint. The status of the constraint is enabled and both Update and Delete opt...more >>

What is wrong here: IsTime AS (GETDATE() >= DateColumn) SQL says 'Incorrect syntax...' ...more >>

Hi,Is there any equivalent of MS Access FIRST() function in SQL Server 2000?I am looking for a workaround of FIRST function in SQL Server. Please help me out RJ...more >>

Does anyone have any opinions/insight as to the use of BizTalk for integrating systems together, versus using DTS/Integration Services? I don't know much about BizTalk, but from the opening web page it seems it might make for a tool to do ongoing ETL. -- --------------------------------...more >>

I have a column in a table that contains a message from the email. This message is usually asking for the information for our company. This message can contain several email addresses. I want to find out all the email addresses from that column. My message will have something like this : "...more >>

SQL Server 2000 Hi. I'm writing a procedure that inserts some rows. The actual insert is handled by calling another procedure. It then needs to read the rows it has inserted. However, the procedure seems unable to see the rows that it has inserted while it is still running. The rows ...more >>

Hi, which ways are possible to connect to a MS SQL Server on a foreign host? Are there other ways than ODBC? Does exist an API, that allows to connect through TCP/IP to an MS SQL Server? Thanks, Holger...more >>

We need to do the query from 2 tables. TBL1 has the following columns: ID, Date, PrevBalance TBL2 has the following columns: ID, Date, Balance We need to find those records where TBL1.ID = TBL2.ID and TBL1.PrevBalance <> TBL2.Balance, but we only need those whose dates are the latest. For ex...more >>

Hi, I am following the example on a parameter query and running it in the Query Analyzer. The following generates the error shown below. use Northwind select * from Employees where FirstName = ? ERROR: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error If I use...more >>

Hi, I'm having some issues with this! :s I have a set of rows that have several columns in common and I need them to be formatted in a concatenated list way. This is a sample of my current data: Product OProduct Week Value Prod1 ...more >>

Hi all, I have conducted an index tuning session for a small set of medium sized tables (>10M rows, > 4GB including indexes). The tables already have a set of indexes designed by a former DBA and I was looking to see if I can find better ones. The large tables are actually partitioned views. ...more >>

I must import some exemplary file to database (MS Srrver 2000) ofcourse using procedure Transact SQL. This file must: 1.Read the xml file 2. Create table 3. Import this date from xml file to my database Ps. I create procedure who File xml imports to base, but unfortunately she only schedule...more >>

I am new to this and would appreciate design help. I have a House and a Person living in that house. The Person living in the house may or may not be the owner. So I layed out the following HouseTable/HouseID PersonTable/PersonId HousePersonTable/AutoID HouseID PersonID IsOwner ...more >>

hi i'd like to know if exists an aggregate function like SUM, that do the product of the terms i wanna write something like that: SELECT PROD(conta) FROM table table conta 1 2 4 5 and the result: 1*2*4*5= 40 tx Carlo ...more >>

Hello, Sorry for the long subject, but this is a very intersting problem I am having. I am faced with the following situation: - Single table used by various applications for read-only lookups - Updates to that table are slow due to network latency (~5 minutes) I am updating the table by...more >>

I've recently been faced with a sql problem that I can't seem to nail down. The problem is as follows: The product manger wants a user interface that allows the user to first select a portfolio. A portfolio is a collection of up to 1.5 million instruments with disparate instrument types(B...more >>

Hi Freinds, I am monitoring a SQL server with perfmon and want to have counter log recorded for later usage. I can't capture the counters from \\mssqlserver machine on the monitoring log. I am running perfmon on my machine and sql server is another box.. I have no problem recording of loc...more >>

And what would it be used for? ...more >>

is it possible to return multiple values in a user defined function? if so, how? tia jt ...more >>

Hi I am trying to speed up the existing code. Now..In ASP page get the recordset object and they are creating the XML string using the do while loop like "<name>" & rs(1) & "</name>" but its too slow and taking like 20 seconds to load as the number of records grow it will take time. ...more >>

I have a field which holds the time. For example 4 PM is 57,600 seconds but the field hold the value 5760001. I am not sure why this was done, but I would like to convert the time 1600. How can I do this? Any help would be greatly appreciated. Thanks, Gary ...more >>

I just have a simple C# Windows form with a button on it that runs this code: private void button1_Click(object sender, System.EventArgs e) { SQLDMO.SQLServer2Class server = new SQLDMO.SQLServer2Class(); server.LoginSecure = true; server.Connect("(local)", null, null); SQLDMO....more >>

Hi All, Just another re-posting for a question I posted earlier, after further investigation I have found out that it's quite a common problem where people are trying to insert in to a remote table from a local table using a linked server and INDENTITY_INSERT, the only solution that I have...more >>

Does anyone know how to throw the results of a query into a DAT file (a.k.a. .TXT) format @ a desired filer server location? Joshi...more >>

Hello all, How would I display the following query (which calculates age of a person) not only as an age in absolute number but an accurate count of age in years and months both. E.g. If someone is 24 years old, I just dont want to see the figure 24. I would want to see 24.6 (years & mo...more >>

Is there an easy way to combine these two queries together? I have a sorted column of non sequential numbers called myNumbers (….40, 46, 48, 49, 50, 52, 53, 55….) I need a query that returns (variable) 3 values less then (variable) 50 and (variable) 2 values greater then 50. (This repres...more >>

Due to complete lack of good documentation and since a good number of years have passed from my college days I am having a bit of difficulty dealing with the damn COLUMNS_UPDATE() function. I have a table that has some 53 columns. I need to know if columns 46 and 47 have been update. Either o...more >>

Here is what I'm trying to do: I need to round a float to the nearest 0.25. I WANT a SQL FUNCTION or nested functions to set the value for update using a STORED PROC. It will need to round up or down. It would not be a major issue if the update didn't include 12 - 20 million records. example...more >>

Hi, I have an application that is starting to produce deadlock activity. The query that is producing the deadlock is an update of a large table with 2 Million rows. I can see from the query that the where clause references columns that are not part of any index on that table, therefore I wo...more >>

Greets, All Question with udl and connection string does it work? I'm having problems with the code below I keep getting an error "Keyword not supported 'File Name'" I was just following app.dev cbt course and for some reason it doesn't work for me does someone know what I doing wrong ...more >>

Hi friends, I have problem of orphaned connections. From my VB application or SQl Server procedure how can i detect and close orphaned connection? By making the registry key as explanined in Books online my other applications may be affected. If i use SP_WHO then AWAITING COMMAND m...more >>

Hi All, As the subject says I have a question about Identity Inserts, when I use the option Set Identity_Insert (table name) ON I get the following error Table '(table name)' does not exist or cannot be opened for SET operation. I know that it exsist's but why can it not be SET for th...more >>

I am running the following SQL statement. If I use the current date the query takes 3 minutes. If I use yesterday's date or any previous date or even a future date then the query takes 2 seconds. .......... myStr = "SELECT dbo.vDailyAttendance.homName as 'School', dbo.vDailyAttendance.cliLN...more >>

I was adding some constraints to a few tables of mine to improve integrity and to get some experience with constraints. In access you can set up rules, and rule text that descriptive to the rule. In sql if you set up the constraint is there a way to specify the text that's raised as an e...more >>

Hi All, I have the SP included below... It's quite long with various elements etc, however, my question is more generic than specific. I need to use the SP in an app. When I place it into QA and declare and set the input varibles to the SP it runs in around 3-6 seconds depending on the i...more >>

I am loading data from one table to another the structure of both the tables are the same. When I copy from the staging table to the main table I want the staging table records to overwrite the records with the same key in the main table. So for example the following are the keys in both ta...more >>

I have a proc used for retrieving data for a report. The proc took about 5 secs on DEV and seemed to be working fine on UAT (both DEV & UAT have the same data with data updated/inserted on a daily basis) until today, when it was timing out on the Web side for some queries. I ran the proc from ...more >>

Hi, I need to query 50,000 records and show to user on the client (in a grid in VB.NET). I need to update values on the client as soon as other user modifies the base records. It's not possible to requery each time to refresh the data(because of high number of records). Are there any solution t...more >>