1) Transaction (Process ID 110) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 2) Transaction (Process ID 120) was deadlocked on lock | communication buffer resources with another process and has been chosen as the de...more >>

I don't konw how to use SQLDMO, I want to find the SQLDMO reference, Who = can tell me!Thanks!...more >>

Hello All I am trying to use a stored procedure to insert a record into an SQL Server 2000 database, however I have recieved the following error message: ODBC--call failed [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '20060615',(#170) I am using a pass-th...more >>

Dear group, I need to create a stored procedure to return some data. The trick is the data needs to be AGGREGATED with a SUM of commissions against each line for each broker... I have a table (TRADE) with the following data: trade_id (PK), broker_id, tradeType_id, commission_amount, dat...more >>

Hi all, I'm having trouble googling this problem ... Would anyone know the easiest way to obtain the number of times a character appeared in a given string? Thanks AC ...more >>

I'd like to ask if there's any statement to insert records into a table, such that if any record violates the primary key constraint, it will "neglect" the record and insert the next one. Thank you...more >>

Hello I have a requirement to update the Exchange contacts folder according to a table in MSSQL 200. Also changes in the db has to be updated in the contacts. The solution that I have thought about is to write a trigger and contact a COM object that does the update. Is there a better way to d...more >>

Hi all I Have Table Num As Int , Name As NvarChar(20) I Need Trigger to Resort The Field Num When I Change The num Num Name 1 aaaaa 2 bbbbb 3 cccccc 4 ddddd I Want when I Change the Num...more >>

I have this query which gets only those sales people who made a sale each month from Feb 03 - May 03. I need to change it to get sales person who did sales for each quater. so instead of comparing for each month quaterly comparison would be done. Here the range of data is from Feb 03 - May 03 bu...more >>

In SQL Server 2000: If I create a derived table as follows SELECT ..... FROM Cust INNER JOIN ( SELECT Orders.CustID, SUM (Invoice.Amount) FROM Orders INNER JOIN Invoice ON Orders.InvoiceID=Invoice.ID AND Orders.E...more >>

Are there any good books on Stored Procedure and Triggers? ...more >>

I have a table tmPunchtimeSummary which contains a sum of employee's hours per day. The table contains some duplicates. [code] CREATE TABLE [tmPunchtimeSummary] ( [iTmPunchTimeSummaryId] [int] IDENTITY (1, 1) NOT NULL , [sCalldate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL...more >>

What is the best way to identify what tables are selected, updated, inserted, or updated in a sql profiler trace? ...more >>

I created an online application for one of our educational programs. The = data is inserted via website entry -> client side validation -> stored = procedure -> database. Given a table stucture as follows, if 100 or even = 1000 people applied at the exact same time using an online application, ...more >>

hi i am working on Windows 2000 Advanced Server + SP4 and SQL2000 Enterprise + SP4. i have database contains Tables, and i want make script to build Tables on other Database, but i need this script include Data in Tables too, i can make that ? and how? thanks -- Tarek M. Siala ...more >>

Hi I'm writing a web search application that utilises AJAX. The idea is that as someone types in their search term, the page constantly tells them how many items (rows) the current search term is likely to return. So...the SQL. The stored procedure could be something like: selec...more >>

To all guru's: I have something that sounds simple in theroy, but I haven't quite figured out what I need to do to accomplish this. Simply put here is what I'd like to do. 1. User logs into database/application 2. Upon login, update a table that contains the users login name (not sysxlo...more >>

I am not posting the DDL because it is not relevant to my question. So far I have been unable to find a decent "natural key" for a table I am designing. The true "natural key" is varbinary(MAX), which is unusable, and so I have to consider a surrogate key, which is using SHA1 agorithm to ge...more >>

Hi all, I need to built an efficient query that would be able to tell me the amount of times that the field 'mark' has an 'x'. It should count only one time for a given id. In the following example for id 23 the count should be only 1 even though it occurs more than once. At the e...more >>

Hello, I'm trying to combine 4 statements below into one that will give me results that will be in this format: BPSName Total(X+Y+Z+W) ________________________ Mike 23 John 44 Gus 63 .... 4 statements: SELECT tb...more >>

Once again, just trying this out so please correct my obvious errors ... I can not seem to get Cache.Insert to work if I pass it a SqlCacheDependency. If I don't pass the dependency then it works fine. Here is some code from my DataAccess layer (thats why I am using HttpContext.Current). ...more >>

I have this belief that the first result set in a UNION ALL query will come first. So select * from authors where au_id = '427-17-2319' UNION ALL select * from authors in the pubs database will prepend Ann Dull's record to the results. However, this is not what Books Online says. Am I correc...more >>

I've included two queries below, which give me the same result and it seems the query plan ends up being the same. Is there any benefit of writing it one way over the other? SELECT o.[id], o.[lastname], o.[firstname], o.[startdate], a.[timestamp], o.[manager] FR...more >>

1. select .... from T1 inner join T2 on T1.c=T2.c where T2.c1 = '...' 2. select .... from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...' Any reason not use the approach 2? ...more >>

Hi, I have a field of timestamp datatype. The data is hexadecimal. I would like to create a function or query the field so that I can see it as normal 00:00:00 format? It would also be nice to be able to query the field by entering a 00:00:00 value but it searches the field in the hexade...more >>

i have a table with sale_id , sale_object_id for each sale_id there can be multiple sale_object_id's but not 2 same sale_object_id in each sale sale_id sale_object_id 1 100 1 120 1 140 2 100 2 120 ...more >>

I saw something today that I had never noticed before. A collegue of mine and I were troubleshooting a query that was not performing as expected. The query consisted of a Select from a complex view with a where in subquery. In all, the query was taking about 30s. The subquery runs quickly w...more >>

I am not very familiar with handling deadlocks. I've identified the SP that is causing the deadlock, but I'm just not sure how to write it any differently. Basically we are conforming records from an old system to the new system. This happens on a hourly basis as old events are re-opened. ...more >>

We have created a web app that has it's own database. The web app has been designed to work with multiple third party loan origination systems. In order to reduce the work required to install the web app along side various other systems, we created views in the web app database that call views...more >>

I would like my query to return the KeyID from row 4 but from only one of the first 3 rows where the address data is redundant. Since use of the key eliminates the DISTINCT operator, is there another method? DECLARE @tAddress TABLE (KeyID int, Address varchar(10), City varchar(10), ST varch...more >>

Hi there. I have an application that connects to a SQL Server using ODBC (and CRecordSets) to peruse and retrieve data from a number of large database tables. As a result, SQL Server seems to be constantly using up a very large amount of memory - even when my application doesn't seem to be ...more >>

Die folgende Stored Procedure (MS SQL Server 2005 Express) verursacht folgende Fehlermeldungen: "Meldung 4104, Ebene 16, Status 1, Prozedur GetNextSequence, Zeile 11 Der mehrteilige Bezeichner "dbo.Text.text_id" konnte nicht gebunden werden. Meldung 4104, Ebene 16, Status 1, Prozedur GetNextS...more >>

Hi, Could anyone tell me if this is possible: If i have a table like follows: Col1 John Smith John Jones Ann Smith Ann Thoms John Peters I want a query to group by the first name, so is there anyway to select the first word and then group by it. ie: My desired result is: First ...more >>

How is it possible to take out all data types ? Which procedure( function, view) should I use ? ...more >>

We have an alert system set up on SQL Server but the login and pwd is not documented. If we change both, what if any negative impact can we anticipate? To our knowledge there are no hard coded login/pwd calls....more >>

Hi, I want to deny select on many views/table so that my excel users won't access them. 1. USING DENY SELECT ON VIEWS/TABLE deny select on sys.all_columns to loana deny select on sys.all_objects to loana ....etc... -> This works fine but takes ages for going throgh all the views/tab...more >>

Hi, I am upgrading tables on a legacy SQL Server 2000 database Some price data is stored in real datatypes, which is not correct. Prices are in Euros and I just need 2 decimals. I could convert them to numeric or use integer multiplying values per 100 then dividing on them on the presentation...more >>

How can I determine the type of some value ?...more >>

Hi Am looking around for interesting performance tips and one was to use 1. SELECT rows FROM sysindexes WHERE id = OBJECT_ID('Table1') AND indid < 2 instead of SELECT COUNT(*) FROM Table1 Is this a best practice to use? 2. Whats the benefits of using dbo.Table1 instead of just T...more >>

Hi - my SP below, update two fields to Null, it should update them to the minimum value in @ci and the maximum value in @co - can anyone please advise what I'm doing wrong? Create Procedure updateStartEnd { @cust_ref bigint, @car_id bigint } AS declare @ci datetime declare @co datetime ...more >>

I was very happy to read about the OUTPUT clause in SqlServer but I don't know if I am missing something but I find it quite limiting. Here is how I am using the OUTPUT clause to return the new valuess: CREATE PROCEDURE [ExpData].[GSP_PortfolioGroup_Create] @PortfolioGroupNu...more >>

Hi I have a VB6 windows app which calls a VB6 COM+ application (both running on machine CHOPGBCOM001) which in turn calls a stored procedure on a remote machine CANSUR001 but it keeps failing with error "MSDTC on server 'CANSUR001' is unavailable". The COM+ application is configured wit...more >>

When running a command line query via SQLCMD, ISQL, OSQL, etc., is there away to turn off or not display the rows affected message so that it will not be displayed? I just want the results of my command line query nothing else. -- Jason...more >>

Hi champs, I am kicking my self for the syntax for a derived column in a CREATE TABME -statement. I just wnat a extra colum that is the result of colum1+colum2 ... CREATE TABLE [dbo].[test]( [colum1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [colum2] [nchar](10) COLLATE SQL_...more >>

Hi All How can I Change The column Name in The table whit out drop it column_b ----- to -----column_c ALTER TABLE doc_exa (ADD) column_b VARCHAR(20) NULL ...more >>

Hi Wonder how the query engine works. Say I have a query like this. SELECT * FROM Product WHERE StoreId = @storeid AND Id = @productid AND IsActive = 1 Say that (StoreId = @storeid) returns true but (Id = @productid) returns false does it still go and check (IsActive = 1) or...more >>

Hi to all, Im using a query like this: select A.AfectadoApellidos as Apellido, A.AfectadoName, A.AfectadoDNI,(Convert(varchar,TA.TipoAccionBreve)+Convert(varchar,Empresas.EmpresaBreve)+Convert(varchar,E.ExpedienteIncidenciaID)) as Codigos from Expedientes E inner join Afectados A on ...more >>

Hi, How can i retrieve primary key columns including foreign key ( part of primary key) by querying system tables of sql server 2000. I want to know that how many columns are primary key column in table. Pls help. Thanks in advance Shail ...more >>

Hi Any one able to give comments on "Dose managed stored procedure hit the application performance compare to normal sql procedure?" Now take the example, There are 10,000 rows having 40 columns now spliting & inserting these records in 8 tables How much time and resources will be consu...more >>

Hi, I want to execute some SQL statement that has more than 4000 characters, is there any way to do it? I'm using the exec sp_executesql command as following: declare @SQLString nvarchar(4000) select @SQLString = 'SQL statement more than 4000 characters' exec sp_executesql @SQLString T...more >>

Hi, I have a query like this. don't look at from part i have a problem with the SET part. UPDATE DSREA Set HostAmt = ROUND(CONVERT(MONEY,(((CONVERT(FLOAT, DSREA.HostAmt)) / ISNULL(ER1.ExchangeRate,1)) * ISNULL(ER2.ExchangeRate,0))), 4) * CASE DSREA.DebitFlg WHEN 1 THEN 1 ELSE -1 END...more >>

-- which is best? -- trans per table CREATE TRIGGER trMyTrigger ON tbTable INSTEAD OF INSERT AS BEGIN -- PRODUCTS BEGIN TRANSACTION UPDATE tbProducts SET prodName = 'abc' INSERT tbProducts SELECT prodName FROM INSERTED COMMIT TRANSACTION -- EMPLOYEE BEGIN TRANSACTION UPD...more >>

Help need to get records from this "Trans" Table: TransID CustomerName LicenseNo ProductID Price DtOfSale 1 Prabhat 0123456781 10 125 2 Prabhat 0123456782 20 123 3 Prabhat 0123456783 30 ...more >>

Hi, I am trying to group results based on the date of the transaction. The date field is a normal datetime field, so returns the full date time value. How do I just get the month and year bit, so i can select all transactions in say May-2006, and then i will be able to easily group on that...more >>

Hi, I have a stored proc which builds a string of dynamic sql. The problem is that i have some parameters i pass to the stored proc which is used to build the dynamic sql. If one of these values is null, then the dynamic sql i am building is set to null.. is sql = 'select col1, col2 f...more >>

1. What is the best way to compare efficiency (performance) of two different queries that return the same result in general? 2. What if one contains user defined function one query and another without user defined function? The reason I am asking this is that the optimizer's cost model do ...more >>