I am writing a stored procedure to get aggregrate data out of a web site log table by joining it with a view DDL for the table: CREATE TABLE w3cexlog ( pacifictime datetime, [cs-method] varchar(20), target varchar(8000), query varchar(2000), username varchar(100), browser...more >>

Hi all, I have found many solutions for selecting duplicates with the most popular being: SELECT ColA, COUNT(ColA) FROM SomeTable GROUP BY ColA HAVING count(ColA) > 1 However, what I am after is a query that returns all of columns and all of the duplicate rows where duplicates exist ...more >>

Hi everyone, I was hoping anyone came across implementing set theory operations on two tables containing date ranges. In specific the tables are set up as so: Table A [client] [start date] [ end date] a 1 3 a 8 12 b 6 8 b 10 12 c 15 31 Table B [client] [start ...more >>

I've tried SQLGetDiagRec, which tells me that there was an invalid date format on a column, but there's no indication of *which* column, and my table has several date columns in it. I then spotted some references to SQLGetDiagField with SQL_DIAG_COLUMN_NUMBER, but I don't get any records back ...more >>

Hi, This is a silly question 1. What the precausions we need to take care while coding a procedure, if we have an MTS. Do we need to explicitly mention the Transactions in the sps ? And what about setting the Value of 'XACT_ABORT' . 2. Another situation in the same. Normally all our ma...more >>

I have two applications that pass data in one direction via a MSMQ. "Scheduler" application looks at the database and finds records that need to be worked on. It creates object for those records and puts them in the message queue. The "Worker" application is monitoring the queue for new messag...more >>

How to handle error in trigger. i m inserting record in other table in after insert trigger. but if insert statement in trigger has error, procedure quits with error and i cannot use @@error to check it ...more >>

I have a task before me that i need help with. I have values in table that look like this: 'MY TASK' [random_operator] 'VERY ANNOYING' I need to replace SPACES that are inside quotes with '_?' Only values that have quotes around them should have their spaces replaced with '_?' Any idea...more >>

I have a small customer with a 2GB database. They recently added a RAID 5 array and RAID10 array to their server. I want to move the data files to the RAID 5 and the log files to the RAID 10. Is there a script/command I can use to move it or is it easier to just backup the database and then us...more >>

Hello, Does anybody has any clue if its possible to verify if a record has any related data? Let me try to explain these a better, i'd like to show the delete option only when no data related with the specifc Record is available and not return an error message if the data that they try to d...more >>

When I try to compile this (complete Store Procedure below) I get the following error: The select list for the INSERT statement contains fewer items than the insert list. I know what is causing this to bomb is the complex Grouping (which I've marked below as ---COMPLEX GrOUPING (this gro...more >>

How can I get the RowCount of a SQL that has been executed in a dynamic SQL. @@RowCount appears to return the rowcount for the EXEC of sp_executesql. T.I.A. E.g.: DECLARE @TblGen Table (relation nvarchar(50), attribute nvarchar(50), sql nvarchar(255), rtncde int, c...more >>

I have a table that stores data points for several different data sources. The general format is: DECLARE @Data TABLE ( DataID int, TimeCollected int, DataValue decimal(9,9) ) INSERT @Data VALUES (1, 1000, 0.75) INSERT @Data VALUES (1, 1001, 0.69) INSERT @Data VALUES...more >>

Hi all, I have the following query that returns a two column result set. SELECT s.standard_id, si.standard_item_id FROM standards s INNER JOIN standard_items_bk si ON s.parent_id = si.standard_id AND s.idx = si.idx Sample results: standard_id | standard...more >>

Having a list of words in a table...haw can I make with SQL a "did you mean" search? for example.. Having a table with these data: hello hallo hi lup hai If I look for "hollo" it should return "hello", "hallo" I know this could be a very complex algorithm, but I'm looking for it'...more >>

Hi All "No more data available to read" error is thrown by database driver when connections max out on SQL Server. Does any one know what this means and how can i resolve this issue? An application developed in Java uses SQL Server for data storage. Thank you in advance. ...more >>

Is there a way to add a column to a PRIMARY KEY constraint (without deleting and recreating it?) Thanks....more >>

Hi all, I'm new to SQL programming, and I am having a hard time figuring this one out. I have a table containing the following information: Activity Cost Account Hours 1 A 100 1 B 200 1 C 250 1 D 100 2 A 600 2 F 200 3 B 100 3 C 200 3 D 400 I would like to create a view that will...more >>

I know there are tools that can look at two different versions of a database and generate a script to take one to match the other. I cannot seem to find anything suitable. I thought Visio could do this but I'm not seeing anything in the 2003 Pro version. We have major revisions that need ...more >>

Hi, I am using a SQL Server 2000 database with a VB.net program and uses Integerated security. I want to log all the changes made to any table data(whether addition, deletion, updation) to a single table by all the users. Is this possible by way of a common trigger. I dont want to use th...more >>

Ok, I'm at a crossroads in my program. I've got a program that needs to throw an SQL update command to update some individual records. >From an efficiency standpoint, does SQL handle the UPDATE command differently if the field is the same as the old field? IE Is it worth doing a string...more >>

I would like to see what's happening on the database which I'm database owner. But when I open SQL profiler it requires that I'm a member of sys admin server role. What now? I'm dbo of that database(I created it) but I can't see what's happening there. Is there some way to avoid th...more >>

Hello I was wondering howto insert into a new table with a union query, something like: SELECT * INTO #New FROM SELECT SomeNmbr FROM #A UNION SELECT SomeNmbr FROM #A ...more >>

Hello I was wondering if you guys could help me with a simple max select, Im trying to find the max value where SomeNmbr doesnt follow the increment step of 1. And no, im not trying to fiddle with the IDENTITY(1,1) thing :) In this case 4 CREATE TABLE #A ( SomeNmbr int ) INSERT ...more >>

Hello! I have a problem with my sql server. When I run: select stdev(sales), avg(sales) from salestable I get: standard deviation: 65, average: 63 Which is garbage. What would be the correct syntax to get the stdev? What would I have to tell the sql server? Thank you very much fo...more >>

I have a statistic that has been created by SQL Server automatically that is deadlocking. I understand how it deadlocks but would like some advice on the best fix. My thoughts are dropping the statistic or creating an index on the column. Has anyone else had experience with stats deadlockin...more >>

Is it possible to pass a table name to a stored procedure? I have a ton of stored procedures that do the same basic thing, just on different tables. Can I pass a table name, and, if so can you give me an example? Ant help is greatly appreciated. ...more >>

hello everyone, so i have the need for an audit trail for certain activities. ideally i would like a generic audit trail table, that denotes the kind of activity, the entity reference it was performed on, and the entity reference it was performed by. for example, there are currently entitie...more >>

i am encountering a problem when calling a web service from the stored procedure. the sp_OAGetProperty procedure is not running fine. at this line the error generated is 0x80020006 ODSOLE Extended Procedure Unknown name. do anyone have any idea about how to solve this problem. i need he...more >>

Sub: Append a string to all the values in a column SQL Hi all, This might be a very simple query. But I am new to SQL programming. Hope someone can help me. I have a table --- 1634 Fred los angeles 123 Sam 1245 abc 1231 .... 1278 ..... 578 ...more >>

Hello! I have a BCP file (.BCP) and I want to do a BCP IN but I don't have a table associated with it. (Lets say table got dropped). I am curious if there is a way to create a table on-the-fly while doing BCP IN? Any neat solution? Thanks! *** Sent via Developersdex http://www.develope...more >>

Hi, we want to check the paystatusid in the table Treatement CREATE TABLE [Treatement] ( [treatementid] int IDENTITY(1,1) NOT NULL , [customerid] int NOT NULL , [paystatusid] int NOT NULL , ..... and CREATE TABLE [Paystatus] ( [paystatusid] int IDENTITY(1,1) NOT NULL , [customerid] int...more >>

How do I move tempdb to another drive on the same server? Thanks Emma...more >>

Hi I had a similar problem awhile back and it was solved here. Now it has gotten more complex. I have removed uneeded stuff here to keep this simple.: I am sure I know why I am getting the results I am, but don't know how (or if) there is a way around it. If someone can help, I will be mighty ...more >>

I have an exact table in both SQL2000 and SQL2005. But the return order of 'colid' for the first key is different! (only the first key, not the others). My select query is: "SELECT [indid], [colid] FROM AWT..sysindexkeys where [id] in ( select [id] from AWT..sysobjects where [name]='Vend...more >>

Dear fellows, The following loop bring me differents results, execute one after one (among them a truncate table, of course): DECLARE @loop as integer set @loop = 1 while @loop < 10000 begin insert into A_test(id,nombre,ape) values(@loop,'a','aadfasdf') set @loop = @loop ...more >>

Hi all ! I have the following query which runs ok: SELECT estates.l_memberID, member.l_memberID FROM estates left join companies on estates.l_companyid=companies.l_companyid left join multimedia on multimedia.l_estateid=estates.l_estateid and isnull(N_INDEX,1)=1 left join member (NOLOC...more >>

Hi, I have two tables which has 2 linked fields, as David Portas posted in 'Field value determines whether there is extra info that should be held about that record': CREATE TABLE events (event_no INTEGER NOT NULL PRIMARY KEY, event_type INTEGER NOT NULL CHECK (event_type BETWEEN 1 AND 10...more >>

Hi, I have a table with a smalldatetime field. Some of the queries in my application are using range searches over that smalldatetime field, such as selecting all the records within a date range. None of these queries are selecting records from a specific date/time. They all work on ranges ...more >>

Dear all, I was wondering how do I for obtain the total for the field 'parcial' in the same query, of course: SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA, COUNT(*) AS partial FROM ABS_DIARIOHISTO WHERE DIA_SUCURSAL = 81 GROUP BY MONTH(DIA_HORAESCRITURA),DAY(HO...more >>

Hi there, I've managed to come this far and I need a bit of help to finalise. I'm extracting records correctly using the following: select substring(D.MStockCode,1,3) as Style, sum(case month(D.MLineShipDate) when 1 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0 end) as J...more >>

Hi folks, I wonder if any of you in all your wisdom happen to know the URL of a published web article on a really decent SQL server naming convention I've obviously googled it, but have come across nothing that has particularly grabbed me as thorough or especially coherent. Regards, and t...more >>