I'm on a mission to prove to the bosses that some indexes are useless because of selectivity approaching zero. So I want to count the records in the table, and then get the count of a select-distinct on a given column. Not a biggie, but it occured to me that there are probably several ways...more >>

Hi Just wanting an opinion as to what would be a better way to go. I have an update trigger that does several queries against INSERTED and DELETED tables as joins between them, as well as to other database tables . I wonder if it would be more optimal to save PK of my record set from INSE...more >>

I've got a trigger that I would like to create to the same table in each database that is used by my application (one database is created per facility). I have some that figures out which databases are used by my application and put the list into a temporary table. Next I was going to go th...more >>

I have a fairly large Sql Statement that was giving me a 0 as one of the results, when I was trying to get blank. The case statement is: Misc2=CASE WHEN p.TravelRequired = 1 and ((TravelPercentage <> 0) or (TravelPercentage is not NULL)) THEN 'Travel Required - about ' + Trave...more >>

I have two SQL Server 2003 Enterprise Edition servers configured for replication: one as the distributor/publisher and the other as the subscriber. I need T-SQL that completely removes the replication. (Ideally, this script would run remotely from either of the machines, but could run on...more >>

SQL2000 SP3 I have an Account table that has a 1:M relationship to a Client_Ratings table. What I'm trying to do is to create a SQL statement using the IN operator that displays ONLY the MOST RECENTly added Client_Ratings record. The MOST RECENTly added field is determined by the CreateDate...more >>

hi i have the following query select col1 from table1 which gives me the result a b c d and so on..(abuot a 100 rows) i want the result to be a b c d in one row. how do i do this? ...more >>

test1 and test5 need to be counted as the same value. test6 is a separate value. Between test1 and test5 I get a count of 17, and a count of 3 for test6. Could someone help me figure out what kind of tsql could do this? Do I need a udf? a self join? create table tbl1m (fld1 varchar(20...more >>

Hi everyone, I have having a real hard time finding any information/example of how to take a field's value, say it's 24000 chars long (defined as a text field, not a varchar, of course) and split it up so I can create a new table based on the delimiter (which is "|;|", no quotes). Can an...more >>

Does someone could tell me why I got an error 4104 "Multi-part identifier c.CustomerID could not be found" by invoking this? The fonction return an inline table for all Active addresses associated to the customerID param... SELECT * FROM Customer c LEFT JOIN fctGetCustomerAddressB...more >>

hi is there any command like @@Servername to get current database name thanks kalyan...more >>

I need a way to discover how many days two date ranges share. For example, the ranges 06/01/2005 - 06/24/2005 and 06/06/2005 - 06/10/2005 share 5 days, while 06/08/2005 - 06/24/2005 and 06/06/2005 - 06/10/2005 share 3. I have written a small ASP function which accomplishes this, but I'd li...more >>

Hello Everybody, I do have query which will return XML. Now I want to write that XML to my C drive, pls let me know, How can i write it. thx...more >>

I am new to SQL Server. I am using Sql Server2000. I am designing and application that offers services to user. I have a webapplication with a no of forms. In form1 i am asking the user to select all those services he wants. Once he selects services I take him to the other forms where he cus...more >>

Hi, I need to develop a log of changes to selected tables. The requirement only calls to record the primary key (an integer), the timestamp of the event, the user id (another integer), and whether the row was added/changed/deleted. My initial idea is to write triggers for each table wh...more >>

How do I convert the following erroneous insert syntax into functional syntax? --INSERT INTO [REODEVTEST].[FileSpecific_Master] --(REO_NUMBER) --SELECT Matter --FROM REO_FIRST --WHERE Left([Matter],9) = dbo.FileSpecific_Master.REO_NUMBER AND dbo.REO_FIRST.Matter IS NOT NULL AND dbo.REO_...more >>

Hey all - fun question for ya. Let's say I have a table with a list of random identifiers between 1 and 9,999,999. I want to return a list of values not already in-use by this table, but between a valid range of 1 to 9,999,999. What is the most efficient way to do this? For example: T...more >>

Hi All We are planning to implement an ASP application which will be used by our UK and US offfices. We are planning to use the two sql servers one for each location so the data access is quicker however I am confused as to how the data will be affected when the replication occurs. For e...more >>

Hello, I am creating an audit system where am I logging field changes. I have an audit table looking like this: CREATE TABLE dbo.Audit ( OperationTimestamp datetime NOT NULL , Operation int NOT NULL , AuditID int IDENTITY (1, 1) NOT NULL , ModifiedBy varchar (50) NOT N...more >>

I am able to get the following result using temp tables but its not very clean. There has to be more cleaner and efficient technique than what I am using. emp lessthan10 morethan10 ---- ---- ------- 1 1 1 2 0 1 sample table and test data are as follows: set ...more >>

Hello All, I have quite a complex task that needs to be run every 12 hours. The totals tasks TSQL extends beyond the 14000 line mark with several internal cursors. I have done the best I can to try and make this process as tuned as possible and I had the procedure running in under 13 minut...more >>

sql2k i'm looking for a syntax like this, but can add condition if a value is not found in a constraint, default to another vaule? something like if cust_name not in ('a', 'b', 'c', 'd') then 'invalid' is there a similar sql to do the trick? thank you! CREATE TABLE cust_sample ( ...more >>

If a single Acct_cd has positions with both security 2 and 3 I want to add the position value associated with security 3 to the position value associated with security 2 and delete the position with security 3. If an Acct_cd has a position with security 3 but not 2 I want the position with se...more >>

I got two Databases A and B Few of the Table structures are one and the same in both the data bases. when ever there is a change in the Table Structure in any of the Database immediately It should give a message that the Impacted table exists in the other Databases ... Solution we have r...more >>

Hi I have the following query which only need to return the top 20 records. However I only need to return the first 20 records . I am trying to figure out how to do this the most efficiant way. For example I know that all the records are returned in the first part of the query and all those f...more >>

Dear all, I’ve got a production table with a large amount of rows. That table own a identify field and constantly, day in day out is populated with massive input files. Well, the problem is that I would need have available a mechanism which notifies me when it’s ready to go beyond of t...more >>

Someone told me that file groups affect how Sql Server buffers or retains database pages in memory. Is there a connection? The reason I ask is that we have some small look-up tables we would like to retain in buffer cache. Any suggestions will be appreciated. Thanks, Michael...more >>

Hi all, I have the folowing query SELECT Y.[Year], A.ProjectID, A.FCode, A.stdUnits, A.stdFUnits, A.Cost , B.Cost FROM #AData A INNER JOIN #BData B ON (A.ProjectID = B.ProjectID) AND (A.FCode = B.FCode) RIGHT OUTER JOIN #Years Y ON (Y.[Year] = A.ReadYear) AND (Y.[Year] = ...more >>

Hi, I have the foll code when I run it I get the error Server: Msg 156, Level 15, State 1, Line 19 Incorrect syntax near the keyword 'else'. Server: Msg 156, Level 15, State 1, Line 28 Incorrect syntax near the keyword 'else'. This is the query IF Exists(select * from dbo.table1 w...more >>

I've got a new remote server which is called as <ip>\bkupexec and it's only reachable with that structure from my wk. Why? Who or what has put on that name? I mean, 'bkupexec'. Regards, ...more >>

Is it possible to generate from a SP a DDL of a VIEW? wanting to pass the 'SELECT..." into an EXECUTESQL thanks!...more >>

Good Morning Joe, I have one large question for you SQL Guru. This will be the last question for a long time that I promise for sure. Would you "PLEASE" help me with this Jet code that is being converted from VBA MS Access v2.0 to Transact SQL 2000? Is there another way of converting this C...more >>

Hi all, I have a strange problem with a stored procedure. There are 3 *equal* statements in it, lets say INSERT INTO Table1(item list1) SELECT something1 FROM SrcTable s JOIN AddTable a ON a.xx=s.xx INSERT INTO Table2(item list2) SELECT something2 FROM SrcTable s JOIN AddTable a ON a.xx=...more >>

Hi I'm trying to think of a feasible design for my servers. This is my situation. Data is uploaded into database A, this data upload occur frequently(1- 2 times a week) , Lookup tables generated by client is also uploaded through the same process but less frequent. In the next step the ...more >>

I look after a SQL Server database that is backed up every night. Despite this, the transaction log, grows and grows. Should the backing up not shrink the transaction log ? Am I missing something -- like a setting ? How can I ensure that the transaction log is shrunk when we back up the d...more >>

Hi, I have scheduled a job to occur once. If that job succeeds or fails, I am writing the status of the job in the log file. Where do I actually find the log file (physical path)? Regards, Sevugan.C...more >>

Hi experts, I'm using "With (Nolock)" in SQL SELECT statement to retrieve records from two tables (using inner join). Records being returned are not in respective order even with proper Primary Key or Index set inside the tables. First Select Statement [use "With (Nolock)"]: SELECT c.* F...more >>

Hi folks, I need to supress Zero divide error, so I tried SET ANSI_WARNINGS OFF. But I am still getting the below error. "Server: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered." Any ideas?? Here is the test script. -------------------------------- SET ANSI_WARNING...more >>

Hello How can i add more then 100 user in SQL by using sp_addlogins, and how to assign one roll to 30 users by using sp_addrolememeber and how can i add 10 users by uisng sp_addsrvrolemember in SQL 2000? Please help me. Regards, -- Henrik...more >>

I'm working with some code that is on our site that was developed by outside contractors and I came across a SQL statement like the following: "SELECT... FROM Agent ca RIGHT OUTER JOIN Groups g INNER JOIN Location_Groups l ON g.Group_ID = l.Group_ID ON ca.Group_ID = g.Group_ID AND ca.Count...more >>

I imported some salutation data from a flat file to a table with a field nvarchar(20). Within the table, the data appears as: MR MRS MISS However, my code sees all of those values as null! Is there some sort of control character I am not seeing in this field? When I do a replace for 'M...more >>

Hi - I have a knowledge base/faq program, which has the FAQ summary information (title, created, last modified etc) in the FAQ table, and various statements, which make up the body of the FAQ in the Statements table. There is a 1 to many relationship between FAQ.ID and Statements.FAQID - the ...more >>

I need to script my functions that have been edited since the last time my app is altered. I want to use this sql: select routine_definition from information_schema.routines where routine_type = 'function' and last_altered > @run_date My question is this: the ROUTINE_DEFINITION column is ...more >>