hi friends, I want a query to select the dulicate records Example create table test1(id1 int,name1 varchar(30)) values SELECT id1, name1 FROM test1 GROUP BY id1,name1 HAVING ( COUNT(id1) > 1) it selects id1 name1 1 vanitha 1 vanitha 2 akash 3 gaya3 3 prem n...more >>

I have a table required to do housekeeping (range delete). It takes several minutes. I found that the other program is unable to insert into the table during the delete. The insert statement is blocked and finally timed out. The new record does not fall into the delete range. Besides of increa...more >>

Hello, I am not sure that the term "derived table field" would be recognizable, so it is probably better to illustrate my problem at hand using an artificially simplified example. Let's say, there is a table named Product and it has fields named Price and SalePrice. This table is used all a...more >>

If I have a table that contains 2 columns, column A is varchar(20),column B is char(20) let say.... both columns contain 1 million records. I only want to apply non-clustered on one column. which column should i apply to? would it make a big difference on performance between those 2? thx...more >>

I'm trying to use this script to delete duplicate records but am getting errors Thanks for you help in advance http://www.sql-server-performance.com/dv_delete_duplicates.asp Stephen K. Miyasato /* ** This file will delete dupe rows from a table based on a column list. ie., the com...more >>

Has anyone ever imported data from a MySql database on a linux server? I know almost nothing about Linux so I have no idea how to get started accessing the database with SQL from my SQL Server in order to do an import. ...more >>

My SQL acumen stems from just a couple courses, and everything since from the trenches. Fun + angst over time. I'm needing some advice on joins. Though I understand the basics, I'm having problems abstracting from instances where it's easy to think about discrete key values (. . . and student...more >>

I found how to adjust the tab settings when editing a stored procedure using Query Analyzer, but I don't see any way to adjust the tab settings when editing a stored procedure using SQL Server Enterprise Manager. Does anyone know how to do this? Thanks, Vern...more >>

Howdy, Is this proc: SELECT ID, CID, dtDate, strTime, dtRSVP, CASE WHEN dtRSVP > 1/1/1900 THEN 'RSVP by ' + CAST(MONTH(dtRSVP) as varchar(15)) + '/' + CAST(DAY(dtRSVP) as varchar(15)) + '/' + CAST(YEAR(dtRSVP) as varchar(15)) ELSE NULL END AS niceRSVP FROM tblDates the best ...more >>

is it possible to use table variable in a cursor? what else are limited in cursor? ...more >>

This trigger is supposed to copy data from deleted rows(deleted from via app I have no control over nor do I have control over the database structure) to another table. it works fine if there is only one record deleted. If there is more than one record being deleted I get the following error ...more >>

I have just started using erwin on a new database project that would be part of our existing data warehouse. Currently, I use sql scripts to create table, stored procs etc. and then I update the erwin data model. This is not a good idea because I would rather drive my data model from centr...more >>

So far I have a query that returns results for two fields from two linked tables: SELECT locationproduct.prod_payed_until, productinstance.license_number FROM productinstance INNER JOIN locationproduct ON productinstance.loc_prod_id = locationproduct.loc_prod_id WHERE productinstance.license...more >>

Hi everybody I don't know to much about DB projects in Visual Studio. I have read that the new SQL Server 2005 supports .NET programming languages internally so instead of SQL-Transact you can use C# to create procedures and everything. Since System.Data.SqlClient.SqlTransaction works togeth...more >>

Hi This might be very simple, but I just can't figure out how to do it the best way. I'd like to update a single field in one database,with a values from several records in another database. E.g. with the data in the table below : EmployeeNo Knowledge 1 Word 1 Excel 1 Outlook...more >>

I have a table, iciwhs, with fields, citemno, cwarehouse, clocation, etc. There are 5 cwarehouse values (warehouses) each having the same citemno values (inventory item number). Only one of the warehouses, MAIN, has a clocation value for each citemno. I need all of the warehouses to have the sa...more >>

Is there a known issue within SQL Server where a stored procedure terminates prematurely? The best way to describe this problem is by assuming I have the following configuration: create procedure MyTestProc3( @Value integer ) as begin set nocount on declare @ReturnCode integer d...more >>

In stright t-sql or sql server 2000 stored procdures, I was wondering if you can setup dynamic sql statments. Basically I am going to have a sql server 2000 control table where users can select via a web page, what columns they want to see with their own unique (personal) sql server 2000. T...more >>

Around one a week users are getting timeouts. I have identified this to one stored procedure. In profiler it shows the reads at around 20million and taking over 30secs. This keeps on occuring until i execute the SP in query analyser. This SP performs a number of reads on a table with 2million ...more >>

Using QA, I want to select data from one database into another database using a select query. I am struggling with the sytax insert into projects select * from dbo.otherdatabase.projects What am I doing wrong. Thanks. Archer...more >>

I am using a query in a stored procedure, where the user can dictate which field they want to sort by. That I can do. What I also want to do is filter out the null values for the selected field, and one of the fields is a concatenated result. This is the query: Select LName + ', ' + FNam...more >>

Hi there, I'm tracking down a syntax error and have an odd problem. I'm using the SQL Trace tool to view SQL arriving at the server. Now, there's two problems... SQL Server keeps on putting EXEC in front of my statements - eg: ENABLE TRIGGER ddlDatabaseTriggerLog ON DATABASE is change...more >>

I am designing a table structure for scheduled tasks. Tasks must be scheduled either on a fixed interval or at specific times. Those specific times can be either every day or specific days of every week. I have come up with the plan I'll paste in here, but the NULL's and one field telling ho...more >>

Hello, I am a little confused between For Insert and After Insert statements on Triggers. I just need to know which is the Sql Server Default Trigger. Here are 2 sample Triggers. Is For Insert or After Insert the default? -------------------------------------------------------------------...more >>

Hello, friends, To have an array of data stored in an array-type variable is very common in many languages. It is also very convenient to iterate each element of an array by going through each index number in a loop. How do we normally handle this type of data in SQL Server 2000? (Cursor ha...more >>

hi guys, Sorry, it's off the topic question. If you get hired as a consultant "senior sql developer", then before you accept the offer, what kind of important questions you must ask? I'm just asking because I never work as a consultant before. thanks for help ...more >>

I am trying to implement a trigger on a specific column to audit all changes on that column. I used a sample from the SQL Server 2000 Bible. In my trigger, I join to the Deleted and Inserted virtual tables to store the new and old values in the audit table. Everything seemed to work great in my ...more >>

Hello I would like to join Stats.ClientID with Clients.ClientID using an inner join to get hold of Clients.Client but I cant figure out how......... (thanks to Rebecca for providing help earlier) SELECT MonthAdded AS StartDate ,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate ...more >>

Hello there, i really don't know if this is the right forum and i appreciate some guidance. I have a problem with the LEFT/RIGHT OUTER JOIN, in 2000 you can made them in the WHERE clause usring *=/=* and in the last build Microsoft says they're no longer supporting this sintax, so i have mo...more >>

Assume we're using SQL Server authentication. In 2000's Query Analyzer, once you had logged in, you could open up several new query windows without having to repeatedly log in each time. Logging in the first time is important to validate that you are who you say you are. However, in 2005's ...more >>

Using Sql Srv 7 I know I can use the system sp's sp_stored_procedures and sp_sproc_columns to determine all the sps in a db, and what input parms there are for a particular sp... but... if the sp returns a result set, is there a way to find out the stru of that in a similar manner??? ...more >>

I need to get the value of the filed in mytable that appeears more often. myfield1: 10 20 10 30 40 10 10 50 60 70 20 30 10 I need a query tha get me 10. Thanks ...more >>

Hi All, I am sorry about the lack of information and spec for this question, but I am trying to work it out myself, I have a stored procedure that when it's passed results i.e. spProcedure 'a', 'b','c' it loads a line of data into a tabel, What I would like to do is loaded a table wi...more >>

How do I reference the identity column after an update? I know I can use Scope_Identity for an Insert statement, but what about for an update? I need the identity value returned (after the update) for an insert into another table. Thanks, Eric...more >>

Hello, I have a script that create some tables with (Select ... Into) for caching and optimizing. This cache is created twice a week automatically. Problem: The caching process needs 20 mins and while this 20 mins my website is not working because SQL Server is busy with 100%. Is there a...more >>

Can someone help me with a query in MS Access that will return the decimal portion of a value as an integer. For example: 49.345 results in 345 0.124 results in 124 etc. These Double values are stored as a field a table. Thanks in advance. Druidia ...more >>

Hello all, I'm trying to run a query that looks like this: UPDATE MyTable SET ParsedField1 = (SELECT ParsedField1 FROM dbo.parseField(ot.FieldToParse)), ParsedField2 = (SELECT ParsedField2 FROM dbo.parseField(ot.FieldToParse)), ParsedField3 = (SELECT ParsedField3 FROM dbo.parseField(o...more >>

I have two tables Daily_Production (Tracks daily production data) pdate,totalOil 1-Sep-05,30 2-Sep-05,28 ' ' 3-Oct-05,30 4-Oct-05,35 Well_test (Welltest happens at any random date) wellid,testdate,Oil A,25-Aug-05,2 A,5-Sep-05,3 A,3-Oct-05,2 B,28-Aug-05,4 B,7-Sep-05,5 B,2-Oc...more >>

....using SQL 2K ....This is for a data migration project which involves the splitting of a flat file into two or more tables with relationships. The migration will happen over time and not at one time. ....My SQL Server/DB experience up to this point has been primarily focused on client si...more >>

I have one problem while optimizing the sql query .For a few rows the query works perfectly ,but as the number of rows increases it works but gives wrong result. I am using nested queries W/O aliasing . So what I assume is that Query Optimizer is trying to flatten the query (converting it into j...more >>

Hi all, My production system is experiencing abnormal timeouts during posting of transaction. The abnormal part is: System usually works fine for about 6 weeks; but one unlucky day, suddenly for no apparent reason, system experiences massive timeout for about 2 - 3 hours; after that ever...more >>

I have just migrated an application from SQL Server 7 to SQL Server 2000 and got everything working fine except for one small issue. I run a monthly job that sends emails to users matching certain criteria. The emails are being sent, but they are not being stored in the Sent Items folder of th...more >>

i want to select some records where issuedate <="20041223" How can I write it in SQL statment ?? thanks a lot. ...more >>

Scenario - I would like to compare two queries to find out if their results are identical. The first query returns all the values stored in an invoice table for a given invoice no. The second query returns all the values stored in a table holding booking information. E.g. SELECT Quantity...more >>

Hi, I am currently investigating replicating a dataset and extending the dataset on the subscriber. Because of the requirements of the subscriber, additional support tables are required that contain data extracted from the replicated table. I have modified the sp_MS[ins|upd|del]_blahblah ...more >>

I have an system with an order header table (Orders: PK=OrderID) which has a one-to-many relationship with the order lines table (OrderDetails: PK = LineID). I want to list all Orders where ALL the linked lines have a Status set to 'complete'. I've had a stab at it but I'm failing miserably...more >>

hi can i have sql2005 and sql2000 on one dev server installed 4 testing without messing sql2000 up? one instance of sql2000 is curently runing without problems and i need to test all app on sql2005 so i want to install it on the same server and i don't want to kill sql2000 :)\ thanx...more >>

I have a sproc that has been running for over 2 years. We then alter a = table structure increasince the size of 3 fields, and correspondingly alter an Insert = statement for this table. Now an 'update tablename where current of mycur' much later in = the code, issues an error of=20 'The cur...more >>

I want to incorporate another two columns in to the following query: These columns will contain the result of the following sub queries respectively. 1) (for Column A) SELECT Count(asmt_v2_question_id) FROM asmt_v2_question_results WHERE asmt_v2_questi...more >>

Hello Given two tables in a one-to-many relationship, how do I write a query to return only one row from the table on the "many" side of the relationship? e.g., in the "pubs" sample database, the following query returns all rows from the Authors table and all related rows from the TitleAut...more >>

Thought this might be informative http://www.phptr.com/articles/printerfriendly.asp?p=415980&rl=1 Thanks, Gopi ...more >>

hi, i am right: if i create SP dropping clustered index and recreating them will SQL automatically recreate nonClustered index for that table? i use create ...with drop_existing only 4 clustered index when nonClustered indexes are recreated? just after the clustered was created (in...more >>

Hi, I have some doubts regarding transaction log. Please help me to solve = them out. 1. I want to read the online transaction log without using DBCC Log = command. Is there some other alternative. 2. Can I somehow read those transaction log record which are deleted = are by doing ch...more >>

What is the benefit of using a stored procedure vs. a prepared query? Does the execution plan for a stored procedure persist longer in the database than that for a prepared query? ...more >>