I want to create a trigger that will send an e-mail when a purchase order is approved on our system. When this occurs the value of the approval flag changes from 2 to 1. How can I create an update trigger that will only fire when the old value of any value in the column was 2 and now becomes o...more >>

If the SQL's default language (set up in the installation) is CJK, there is no problem to insert ANSI CJK string. However, when the SQL default language is English (non-CJK), I can insert ANSI CJK string with the Query Analyzer, but if I use DBLibrary to do so, the data becomes garbage. (It l...more >>

I posted this in the DataAccess form by mistake (sorry for the double post). I'm having a problem trying to figure out how to create a query for the following problem. Any help would be greatly appreciated. The following two tables define events occuring during a satellite revolution. REV r...more >>

Hi, I want to write a Script that will walk through all the user tables of my database and assign a default value to all columns of type nvarchar which don't have a default value. Can such a script be written, if yes how can I achieve the above result. Any help or suggestions will be highly...more >>

I have created a stroed procedure that returns some of the data I reuire but not all of it. I have used the following View: SELECT tblkpROE.CurrencyCode, MAX(DISTINCT tblkpROE.ROEDate) AS ROEDate, tblkpROE.ROEID, tblkpROE.ExchangeRate, CurrencyListView.CurrencyName, SettingsVie...more >>

Hi, is their a way to calculated a truncated average? ie. an average which excludes the MAX and MIN values in its calculation. lets say I have this table: Column1 Column2 ------ ------- 5 60 25 10 50 13 10 7 10 3 i want the Truncated...more >>

Hi Folks, How can I learn registered server list in my sql server 2000? Thanks Arda ...more >>

Hi All, I have a DATE column in my SQl Server. If I run the folowing QUERY : select batchdate, day(batchdate) as DT, month(batchdate) as MON, year(batchdate) as YR from batchhdr then I get my output. But my problem is I am not able to get in "dd", "mm" format. MEAN:- If the month is 02...more >>

I want to pass a part of column name as a parameter but it does not work. For example I have column names [test Exp] and [test2 Exp] and so on... I want to pass test, test2 and so as a parameter and in a store procedure. I want to re-assemble again something like '[' + @parameter + Space(1...more >>

Hi, I have a bit of problem. The way SQL sorts isn't the way we need to sort in our application. For example, right now SQL sorts 1, 2, 3, ... , A, B, C, where the numbers sort first and then letters. However, for our app, we need the letters to sort before the numbers, ie. A, B, C, ... 1, 2, ...more >>

Hi! Can I read an IMAGE field and other fields in single dbnextrow step? My query is like this: SELECT an_int_field, an_varchar_field, an_image_field FROM dbo.MyTab? Thx in adv, Peter ...more >>

Hi I have a table with a field called 'State' I have also a table with a list of all the states from the USA I would like add a constraint that checks that field 'State' is one of the records of the table States. So, instead of doing this: ([State] = 'LA' or [State] = 'VA' or ... ) I ...more >>

Hi gang, I'm helping troubleshoot some performance problems on a client's system. Essentially, I was looking at a particular SP that was taking many seconds to run, when it should have executed much more quickly. After doing some steps, we ended up forcing the SP to recompile, and the proced...more >>

Hi all, How can I delete the transaction log of my database??? Can I have future problems doing this??? Thanks in advance, Fabio ...more >>

Hello all, I have a problem that has stumped me. I appologise if it's a bit lengthy, but I had trouble articulating it clearly. I have a table "Weightings" that contains a list of fees and an arbitrary rating: Fee Weighting 1000 1.0 2000 1.2 3000 1.5 5000 2.0 6000 ...more >>

Hi. I have a litle chalenge trying to format a query the way I want to. I want to list serialnumbers within 3 locket kolumns. The sourse is one table with one column with the serialnumbers. One record pr. number with the corresponding Orderline. There can be many serialnumbers pr. orderline. (F...more >>

I want to pass a part of column name as a parameter but it does not work For example I have column names [test Exp] and [test2 Exp] and so on.. I want to pass test, test2 and so as a parameter and in store procedure I want to assemble again something lik '[' + @parameter + Space(1) + 'Exp]' ...more >>

Correct me if the following statements have errors. Correlated subqueries. 1. Each subquery is executed once for every row of the outer query. 2. The sub queries can not be in the outer qeury column lists. 3. When correlated subqueries are used -. Each row in the outer query needs to be co...more >>

start_date increaseUnit increaseNum finish_date 12/01/02 year 1 I would like to update a table's finish_date by using start_date and increaseUnit and increaseNum as such: SET finish_date = DATEADD(increaseUnit, increaseNum, start_date) However, I am having difficulties...more >>

Hi, I wanted to know how I can perform at count on a table that will give me the results as follows: All Editions (100) Latest Editions (15) In the case of 'Latest Editions', the count should be based on all the most recent edition of a particular of a book. For example, if there are 12 ...more >>

I cut-n-pasted the following from BOL... ROUND(748.58, -4) I get an error: Incorrect syntax near '748.58' What am I doing wrong? My end mission is to round a money value like $745.345 to $745.35 How do I round a money value back to 2 digits after the decimal? thanks ...more >>

I apologize in advance to posting this a couple of times, but I'm finding out more things as I go along and have some more questions. Below are the two proc's and the output data that I need to put into a table. I've tried adding the insert statement to the "set @cmd = @db + '..sp_MySpaceU...more >>

I am trying to pull inventory for the last six months. Jan and Feb are in 2004 and Sept through Dec are in 2003. I would like to pull all this data from the same table but for now to make the logic easier to follow I am using two tables ITEMHIST which contains all years and ITEMHIST1 which conta...more >>

Here I am again searching for answers about my doubt Now I need to know if is right when in a single select statement there are many variables assigning values from the statement columns, like declare @name varchar(10) @creditcar varchar(30 Select @name = name, @creditcar=creditcar f...more >>

Hi Everybody, I have a proc which will have a primary order and then a secondary order. In general for secondary order its ORDER BY _____ , _____ but in this stored proc when i do this it says that the "," is invalid. Can anybody tell me whats wrong with it? ORDER BY CASE WHEN @SortOrd...more >>

I have delivery dates that comes in. They can get delivery on Monday, Wednesday or Friday. I need a way to compare that they have entered the correct delivery day Such as on Friday they need to enter an order date for Monday. (which is 3 days difference.) But on Monday and Tuesday the orders n...more >>

A developer on my current project mentioned a few years ago he heard that you should store all money as integers (in cents) as it processes faster. I had not heard this before, and wanted to get other opinions. Any thoughts?...more >>

Hello! I've got stored proc in which the is an IF condition that is always false. Within that IF clause there is an UPDATE statement. I examine Reads and Duration values reported by the SQL Profiler while executing that proc. I normally get 1800 reads and 170 ms of duration. And then I remov...more >>

I've been trying to figure out a way to capture the results of an sp_spaceused for every table in every database on the server. I have a script that gets what I need, but am having trouble putting it into a table. So I was browsing through different sites looking for something that would w...more >>

With with whis code in a sp: DECLARE @SQLString NVARCHAR(4000) SET @SQLString = N'select * from vw_Devices where ([id]=@id or @id is null) and (code like ''%@code%'' or @code is null) and (((@enabled=0 or @enabled=1) and enabled=@enabled) or @enabled=2) and ([description] like ''%@descript...more >>

Hi, When I alter a table by using the Enterprise Manager, the "Save Change Script" option shows the strategy for applying the changes to the database. I was surprised the Enterprise Manager instead of using ALTER TABLE, prefers recreate the modified table by CREATE TABLE, and copy all the data...more >>

Can you include multiple store procs/queries in a single xp_sendmail exec? E.g. I want to add one more query: (marked in stars). Can I do that? Is there anyway to get around this? I want results of multiple queries to come in the same email. EXEC master..XP_SENDMAIL @RECIPIENTS = 'JJ...more >>

I'm nearing the end of upsizing mu Access2000 application but cannot get the follwoing to work in SQL2000: I have an Exchange Rates table (tblkpROE) that contains currency rates of exchange against dates. I have a settings table (tblSettings) that contains default information relating to the ...more >>

the old "to allow nulls" debate ... practical experience with business-processes has taught me that for booleans, numerics, and dates the scales seem to weigh in favor of allowing nulls in the db-col ... nulls are the best way to indicate "data not yet supplied" for the field ... other meth...more >>

I have a bunch of stray data in a table that is distinguished only by the fact that the description field is in ALL CAPS. I want to be able to do something like ... select * from my table where upper(description) = description I realize this will only help me with alpha entries but it will g...more >>

Hi all, Apologies for the cross-post. Just wasn't sure where to put this one: I'm getting this error sporadically on an ADO connection. When it happens I also cannot connect through the Enterprise Manager (or anything else!). The environment is SQL Server 2000 running on Windows 2000 Ad...more >>

given the following table and data, how would I select the distinct pairs of col1, col2 such that A,B and B,A would be considered identical? col1 col2 ------------- A B B A C D E F G H G A H G so I would get out the following res...more >>

Does anyone know why a stored procedured executed in Query Analyzer would generate a different execution plan as opposed to when it's executed from within the VB Code? I turned on profiler and ran them both. The one in Query Analyzer only reads about 1600 rows. Whereas the one from the ...more >>

Hi, I've the formatted a string and use xp_cmdshell to output that into a = text file. I've 2 problems:=20 1) the return carriage doesn't work, it write everything in a single = line.=20 2) the log file is always over-written, is there anyway for it to append = instead? set @result =3D '...more >>

I have the following problem: A stored procedure deletes rows from a table. At the same time a stored proc is updating those rows, an if not updating, inserting. The problem occurs where an update is happening, as a delete is firing. Is there a way to avoid this? PS: It is not imperative ...more >>

Hi, I have a problem with some triggers that shows me an error described = bellow (using ODBC) "Could not complete cursor operation because table schema changed after = the cursor was declared" Somebody has an ideia why this error occurs ? Tks Luiz...more >>

Hi, In store procedure how can i implement like: CREATE procedure dbo.procAbc @errorcode int output, @other_param .... as select @errorcode=0 try update ...... catch select @errorcode=@@error end try go because when i implement like this: CREATE procedure dbo.procAbc @er...more >>

Hi, I want to get the interval in hours between a date posted and the current date. But SQL does not permit to use subqueries in the context. Is there other way to do it? here is my query: DateDiff (hour, (Select DateTimePosted from Ticket where TicketID = '3012'), CONVERT(varchar(23), GETD...more >>

I want to set the database to DBO Use Only at a specific time, to run some maintenance process. I can kill all the process ids and set it to DBO Use Only, but if there is any active transactions, then I would like to wait for it complete, before setting the database to DBO Use Only. Before st...more >>

I have a stored proc that collects info on all tables for every database on the server. I now need to put the output of that proc into a table, but a having a hard time figuring it out. Here's the output of the stored proc.. AutoRep covquote 3148601 334800 228032 106736 106.33 AutoRep v...more >>

Hello, I have 3 tables Invoice InvoiceDescription InvoiceDescriptionHistory The tables Invoice and InvoiceDescription have a relationship Cascade Update and Delete, So if I delete the invoice the details it will get delete it too. My problem is that I try to do a trigger to copy the ...more >>

I am trying to have multi row report displayed as a single row of out put. Example I need to list a schools with many teachers and their information, and have them all out putted to one row. There will be many schools with many teachers. Each School and all of its information needs to ...more >>

I have the DBCC TRACEON (1204) set within SQL Server which writes out the deadlocks to the SQL Server errorlog. But the deadlock information list SPID, transaction time, and comments. I would to create a job that saves the user accounts and SPIDs and login and logout times that corres...more >>

I have created this stored procedure that updates a record in my Database, and it works fine. However i want this SP to be able to return the record ID for the updated record. can this be done? HERE IS THE SP CODE CREATE PROC sp_quote_update_Cruise @ID int, @Ship varchar(10), @...more >>

I have a trigger that is defined against a table with a date field. My objective is to prevent changes to a row once the row had a datestamp older than one month. My trigger works beautifully on the table, but strangely allows updates from a view that references the table. MS SQL Server 20...more >>

I have a self referencing table, which is used to model a tree like structure. Is is possible to use cursors in a recursive manner to walk the tree ?...more >>

Hi there I am using bcp to copy data between sql servers that are not connected. The source DB has all kinds of constraints applied and suffers no consistency problems The only Parameter I am using during export is -N BCP reports a success I import the data using the parameters -N -E in orde...more >>

Can SQL Server procces http request itself?...more >>

There are a bunch of undocumented stored procedures (dt_addtosourcecontrol, dt_isundersourcecontrol, etc.) that seem to imply that it's possible to migrate new and changed objects into and out of Visual Sourcesafe and perhaps lock the actual db objects causing everyone to go through VSS. Has anyo...more >>

Sample data for STAR_BANK_BIN table: CARD_RANGES PRIMARY_PHONE SECONDARY_PHONE BANK BANK_BIN_FROM BANK_BIN_TO ISSUER 510024-510025 349 13465342 349 13465546 SOCIEDAD ESPANOLA DE MEDIOS DE PAGO, S.C. (SEMP) 510024 510025 MC I have a sp that uses a cursor to read the STAR_BANK_BIN...more >>

Im still having problems re-writing parts of an MS Access SELECT Query to a STORED PROCEDURE. These are the two parts of my select statement I am having problems re-writing: (Looked at the SQL Online help but couldn't make much sense of it CInt(Left(Format(((dbo.cms_out.date)- ('2003/02/08'))/7)...more >>

hi I need help for an order by error for a sp. The funtionality I want to obtain is to sort the resultset depending on which parameter I'm using there is no compile error but when I try to execute the procedure got the following error Server: Msg 245, Level 16, State 1, Procedure usp_Fokus_GetC...more >>

I need to create a nonclustered index in a table but as part of the 'create table definition' Create table t (x int not null nonclustered, y varchar(3)) ---fails synta I don't want to make the column unique or a primary key...is this possible I know that I can use a create index statemente or us...more >>

I am attempting to successfully convert a database that is: 1 SQL Server 6.5 compatibility 2 Code page 437 3 Resides on a SQL 7.0 server, To 1 SQL Server 2000 compatibility 2 Collation as SQL_Latin1_General_CP1_CI_AS (Server Default) Residing on a SQL 2000 server. I have taken t...more >>

Hi, I Want to use the top caluse with variables in a stored procedure or UDF but in sql server 2000 this is not allowed . how can i do this without making dynamic sql using exec, or using spexecquery thanks alot...more >>

I need to increment the sequence number in a sequence number table as part of a larger transaction taking care that no other process can access the table during the course of the transaction. I plan on using a Holdlock against the table in a stored proc when I get the next sequence numbe...more >>

Hi friends, how can i copy a role from one server to other ? i just want copy the roles... the database is the same and have the same name. thx Johnny Silvestre ...more >>

Hello Experts, If I use the stored procedure sp_renamedb to rename my DB, will face up any "invinsible errors"? ...more >>

Hi all, I have to use an sp to return a single integer value.. the sep works to create the value but I ahve only used Select statements to return values to the calling procedure.. how do I jsut return a single value.. Thanks in advance Russ...more >>

Hi all, I have some code that should create a #temp table in a function .. but the error says cannot have a #temp table in a function.. Do I have to use DECLARE @variable TABLE and is this treated as a tempory table Thanks in advan...more >>

Hello I'm working on a trading system which have a search form for exisiting offers/advertisements. From this form the user is able to search for a records by requesting a combination of different database fields which can be part of different tables. I hand over all request-data to a stored p...more >>

I build a thread to watch Table, this thread open a data shaping recordset. After reading data from table, the memory with sqlserver increase continuously. And After I close the recordset it did not decrease to old size. Do I need to do some special thing to relase data shaping? How can I deter...more >>

Hi all I have a few questions for the gurus Short version 1.) Can I replace the below cursor based procedure with an updat query? How If not .. 2.) I am Declaring / Deallocating one cursor inside a loop. Would i be better to move the Allocation/Deallocation outside of th loop? ...more >>