Hi All, I have a few triggers on a SQL2000 table that does inserts to other servers via linked servers. 2 of the linked servers are SQL7 while the other is an Informix Dynamic Server 9.3. All three triggers fire together due to an insert on the SQL2000 table. I use a insert query to fir...more >>

Stumped again: How do I create a query that selects all itemNO where the cost changed, then changed back? In this example I would want >all< records where itemNO equals 3 but not 4 sigh create table test1 ( idno int NOT NULL IDENTITY (1, 1), itemNo int, cost money, thi...more >>

Hi, What is the best and fasted way to locate/identify the candidate column/columns for an existing table's primary keys? What I usually do is to do a select distinct on candidate columns and to compared the returned row counts with the total row counts of the table. If it matches, th...more >>

I have three tables... CUSTOMERS, pk = customer_id BUREAUS, pk = bureau_id LOCATIONS, pk = location_id All pk's are INT, and are identity fields. CUSTOMERS is linked to BUREAUS via customer_id, and BUREAUS is linked to LOCATIONS via bureau_id. Each customer, can have multiple bureaus,...more >>

Hi There, We are having a little problem with the SQL server and we would appreciate any input on the problem below. Here it is... We have a db VB6 application that keeps failing on some PCs and works perfectly fine on the other ones. The problem occurs when we try to execute an INSERT state...more >>

Greetings, A customer places an order, and data is stored in "tblOrd", within the "ProductsOrdered" field. The product name, quantity and unitprice of each item are separated by "::" If there is more than one item within an order, a "," separates each item. I want to dynamically generate ...more >>

Did anybody have problems with dbopen (ODS - Extended Stored Procedures) function? Time to time without aparent reason it is returning NULL. Thanks, Gomes ...more >>

I am in the local SQL server 2000 & the web assistant wizard is available . I connect to a remote sql server 2000 OK but when I click on tools wizards it does not show the set of wizards that I saw in my local server. What is the delio ? ...more >>

Hello, I have two tables both of them having a text type field in them. I need to select the text from one table and insert it into the other. Any possible solution would be appreciated. Thanks PS - The text field is huge, sometime containing data that is > 16000 bytes ...more >>

Hi, Two questions, 1 . Can I create user-defined filegroup from Enterprise manager? 2. how do you put specific tables and indexes into user-defined filegroup? can you do this on EM? ...more >>

If I want to do a Query where date = XX Julian day, is ti any function to convert date to julian day on the query ? Thanks ...more >>

Though ProcessID is the unique key for the connection, it still can be reused if it's available. Now I have an application: It must records the unique ID for each connection and this ID can't be reused at any time. So I can tell from this ID which records are old and connection it relates are ...more >>

We used a CURSOR sp in SQL 6.5 to allow simultaneous, multiple users to retrieve a single row from a table. When this sp was executed, FETCH NEXT would loop through all of the rows giving each user a unique row. The sp was moved to SQL 2000 and now the FETCH NEXT only returns the 1st row...more >>

I am creating a SQL query in Query Analyzer, using CASE statements to get info for the query. The CASE statements seem to be working, except for one kind, where I am trying to find if a field value is greater than another value. I have a field called BoltsQty, as a tinyint datatype. My case ...more >>

I have a Local DTS Package. The first step is running a batch file through "Execute Process Task". If it is successful then proceed to the second step which is running some SQL delete statements. So the workflow between first and second step is "On Success". In the batch file I have multiple com...more >>

I have increased the length of an NVARCHAR column from 50 to 255. When I attempt to put 51 characters into the field I get a 'String or binary data would be truncated'. I first noticed this trying to update an ADO recordset against the table, but then found it's the same in Enterprise manage...more >>

Dear SQL Programmers, I need to split a string into three components. The basic steps are to select the last entry from the table containing the strings. Parse the string and store it in table split up into 4 columns as below. The table in which the string is stored looks a bit like this: ...more >>

I have a table with the following fields: ticketownerid varchar 12 primary key ticketid int techid int dateassigned date Is there a way to concatenate the ticketid and techid fields into the ticketownerid field using a default value of ticketid+techid (applying any needed conversions) o...more >>

i am trying to do update to a database where users can enter decimal values. Problem I am having is that, lets say if user enters .75 , it is entered in table as 1 . Column data type is a Decimal CREATE PROCEDURE OP_Update ( @job_position_id as int, @jp_approved as decimal, @jp_actua...more >>

HI, I have a table with two fields, one non-primary key field and one text field. The text field is contained multiline text. Unfortunately, I need to break the multiline text into individual row. An example on how to do this will be appreciated! Tom S. ...more >>

Hi I have written the following logic using the cursor, but is it possible to write a query without cursor. Create table #t (vid int, bid int, sd datetime, ed datetime) insert into #t values(1, 1, convert(varchar(20),'10:00',114), convert(varchar(20),'11:03',114) insert into #t values(2,...more >>

Hello, I have been testing a table with a Identity seed starting at 3000, incrementing by 1. All I want to do is delete all the test data from the table and reset the Identity seed back to 3000. I need to know how to reset the Identity seed without having to recreate a new table. ...more >>

I am trying to get the net monies recieved for any given month. A typicl transacyion looks like this ACNO TRNTYPE TRANDATE AMOUNT VATAMT MATCHDATE ------------------ ------- --------------------------- 217224 SIN 2003-06-30 -64.6500 -9.6300 2003-07-31 217224 MQR 2003-...more >>

I'm customizing Network Associates (Magic Solutions Division) Magic HelpDesk software for our environment. I'm creating custom helpdesk forms for our personnel to use as templates for submitting frequently submitted issues. To create these forms, displaying the information that is needed, ...more >>

Hi, is there as way i can handle Exception and throw message from Stored Procedure and which can be traped in GUI and displayed to user. can PRINT be used for that ? Thanks Brij ...more >>

Hello, I need to accomplish something that I'm sure a SQL Query can do, but just can't find the syntax. I'm thinking I need to use a CAST statement, but not sure how exactly: I have a field in my table (called ILC) that I need to truncate the first digit if the field begins with 07. So, ...more >>

Hi, How to prompt user to get variable value(like ServerName,DBName)? Can I have messagebox? Thanks... ...more >>

I have a table wihch contains Car Manuf (ie FORD) in on field, and peoples names, prices etc in the other fields. ie. 1 FORD SMITH 10000 2 HONDA PARK 12000 3 FORD COHEN 11000 4 HONDA JAMES 8000 5 HONDA CWOW 9000 etc I am trying, without sucess so far...more >>

Hi, well i trying to run a dynamic SQL using Exec where i can get the Value in a Variable as in the stored Procedure below but it gives me error when i execute this procedure i know we can assign variables like this if its not dynamic SQL. is there any other way to get value in dynamic SQL....more >>

Anyone know how to count rows in a table depending on 2 columns, and different values within the second column. Basically i am trying to obtain the resultset example below from the table example below TABLE example: Date | Status ------------------------- 2003/07/01 ...more >>

sql2k sp3 Ive got a nitely proc that bcp's data into several different tables. What I want is for the data to either make it into all or none of the table. The problem is that bcp is itself transactional. So each table either commits or rolls back. How do I make them as a group? In the ...more >>

How to do the following using sql statements? I would like to be able to have the following table Everytime I insert a new record the AutoIncrementKey field will increase by one but I want the PriKey to be in the order of the datetime as shown below. INSERT TABLE DateTime = '1/1/2001 12:00:00'...more >>

What is the quick way to test if SQL Mail is started before running xp_startmail? Thanks, Robert *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Is there any implications to doing this: if 1 = '1' print 'true' else print 'false' instead of if 1=1 ? Karl ...more >>

Hi Friends, SQL 2000 Since yesterday , I am not able to connect to one of my servers through enterprise manager. It gave me connection timeout. But I am sure I have connection and access to that server because I am able to connect to it through query analyzer. Any Idea? What is the solution?...more >>

I am trying to do DBCC CHECKIDENT (#TmpTable,RESEED,0) inside an SP. When I run the SP from SQL Query Analyzer, it gives me the error: Only the owner of object '#TmpTable' can run DBCC CHECKIDENT on it. DBCC execution completed. If DBCC printed error messages, contact your system a...more >>

I have an application which summarizes data for the purpose of reporting. Occasionally, invalid non-numeric codes are submitted on transactional records. My issue is whether or not to keep the columns as integer and convert non-numeric values to NULL or to retain the invalid values by switching ...more >>

Hello, as there is noone able to answer my question some postings below, I'd like to know if it is possible manually decrease the @@TRANCOUNT value in a stored procedure? If so, plz give advice how to do it, e.g.: "set @@TRANCOUNT = 0" or "select @@TRANCOUNT = 0" Thx in advance, Hy...more >>

Hello, Is there any way to find out if a column has a duplicate value and then report back what the value is. I know how to do this in MS Access, but the syntax is different for SQL. Thank you, Brandon...more >>

Hi, How many tables can we add in join stattement in sql 2000 Thanks in adv. Bob....more >>

Hi,all Oracle can produce a unique session ID which can't be repeating at any time for every connection to database and it can also tell users whether a connection is active by providing a function using session ID as a parameter. So does SQLServer have an unique ID for each connection wh...more >>

I'm VERY new to SQL Server (background is mainly Oracle) so I'm slowly figuring out the Transact-SQL equivalents of all my PL/SQL. One thing that that I could do in PL/SQL was to create a local variable with the same datatype as a column in a DB table. That way, if the structure of the table e...more >>

Hi, We have our main "Sales System" on a SQL2K database and we're now looking at importing some 3rd party data to link with ours to provide reports to end-users. This is very much read-only data. I was thinking of loading the 3rd party data into a seaparate database, but on the same server,...more >>

IN DatabaseOne I Have this table CREATE TABLE [dbo].[TableOne] ( [ID] [bigint] IDENTITY (1, 1) NOT NULL PRIMARY KEY, [NoProduct] [char] (6) NOT NULL , [NoCustomer] [char] (6) NOT NULL , In DatabaseTwo i have this other table CREATE TABLE [dbo].[TableTwo] ( [No_Product] [char] (6) NOT ...more >>

IN DatabaseOne I Have this table CREATE TABLE [dbo].[TableOne] ( [ID] [bigint] IDENTITY (1, 1) NOT NULL PRIMARY KEY, [NoProduct] [char] (6) NOT NULL , [NoCustomer] [char] (6) NOT NULL , In DatabaseTwo i have this other table CREATE TABLE [dbo].[TableTwo] ( [No_Product] [char] (6) NOT ...more >>

Hello, I have a somewhat strange problem: I have defined some StoredProcedures and PHP generates me a valid "exec" query. If I put this query into the Query Analyzer or in the "SQL" section of a table, it returns me the correct values. If I try to execute this query via PHP with ...more >>

I have an application written by a third part that works fine in Windows 98. When I try to run the program on Windows 2000 it doesn't work right. It can do many of the things that it normally does but it seems that sometimes it can't update certain tables or fields. Since I don't have th...more >>

Hi, I would appreciate any recommendations on a situation when a table will be frequently truncated of all records and new rows will be added to it. This process will occur on the table once every hour. I was just wondering if there are any guidelines (like proper indexes, recomplication of t...more >>

Hi SQL Gurus, I have Header-Detail Order Tables, where in Header I store Last User_Id who update the Order. Anytime Order Detail is changed (insert/update/delete), I should Update User_Id in Header. How can I (?) do that from within Trigger for Detail ? User_Id is FrontEnd Application Us...more >>