Hi, I have a string manipulation problem. I need to insert three square brackets in a string list. What I basically have is the following string: April,2004,June 2004,May 2004, I need to insert square brackets after the 2004 so the string looks like this: [April, 2004],[June 2004],[May...more >>

If I have a query like select blah blah from mytable where mytable.somedate < getdate() does SQL Server evaluate getdate() for every record in mytable, or is it smarter than that? ...more >>

I have the following tables: Table1: Emp %Sales 1 2.3 Table2: Project %Sales CommissionAmt A 2.2 1.50 A 2.4 1.75 Employee1 has 2.3 % sales. I have to figure out from Table 2 which commission amount to apply. Because emp1 has 2.3 I need to ap...more >>

I have 3 Tables: CLMR (primary) CLMRPhone (foreign) CLMRDuns (foreign) All three tables are related on CLMRKey. CLMR Sample Data: CLMRKey SampleSourceKey 1 1 2 1 3 1 4 2 5 2 6 2 CLMRPh...more >>

Why does this get "Incorrect syntax near '>'"? select convert(bit, 1 > 2) Is there a better way to do this? ...more >>

I am using a Table that has a Start_Time column that is a datetime data type. How can I return the time portion from this column formatted as a 12 hour clock. Example: Existing datetime is “1/1/1900 4:30:00 PM” How can I return only "4:30 PM". Thanks...more >>

Ive been reading up on transaction isolation levels to figure out what level I should use for a particular transaction I have. The conclusion I reached was an eye opener (for me) and Id like to see if what I understand is correct. Basically I have a simple application where I keep information ...more >>

i attempt to parse the following T-SQL in QA : connect to server1.master as server1_master user $integrated and receive the error Incorrect syntax near the keyword 'to'. ...more >>

Hi, I have a varchar column which has html tags I need to select the column without the tags, how do I write the query? Thanks a lot...more >>

hello there is there any guide or premaid reports that parses trough windows logs exported to sql server ? I want to know before I write anything ? thanks ...more >>

I have problem with EXEC function. I'm using the following function: EXEC('EXEC sp_executesql N''' + @sql1 + @sql2 + ''',N'''+@paramList+''',N''' + @productID + ''','+@price+','''+@productName+'''') If @sql1 or @sql2 includes single quotation mark, for example: set @sql1='SELECT top 10...more >>

Why does this get "Incorrect syntax near '>'"? select convert(bit, 1 > 2) Is there a better way to do this? ...more >>

We're in the SQL Profiler trying to decipher some users that are blocking each other, and it's REALLY hard to read because about every few seconds, the SQL Agent service executes: SELECT N'Testing Connection...' Is there an easy way to filter those items out of the profiler activity or m...more >>

How do I Disable a foreign key constratint temporarily? I want to delete some records that are referenced from a parent tabel, but I get a foreign key constraint error, I don't care about this error as I delete all the parents in the next SQL statement. Another solution would be to delete form...more >>

1. I am given a Erwin erx file (with 500 tables) and am asked to alter about 50 tables based on a list of table and column names. The 50 tables primary key(s) columns need to be altered to include Identity (1,1). I can't do this manually because I will be given an updated erx file in the near ...more >>

I have two tables joined by an INNER JOIN on a field called ID. The ID field is unique in the first table but may occur several times in the second table.. The first table has a column called UserName and i am counting the number of occurances that ID occurs for any specific Username. The seco...more >>

How can I run a command that will execute a job. is there a system stored procedure that can do this? TIA, Vic...more >>

Hello: I would like to know if SQL Server offers a functionality whereby a long-running query returns a configurable number of rows to the requester as soon as they are available. This way, the requester does no have to wait for the entire result set to be created. I can use this to crea...more >>

The following two SQL statements do the same updates, but the second runs 10 times faster, just by creating an intermediary temp table explicitly. I wonder why... It uses the following three tables ordered by size: #small, #mid, and #big 1. Slow version update #small set ...more >>

When creating a view in Query Analyzer it works fine, until it is saved in Enterprise Manager. Code is as follows: CREATE VIEW dbo.ShipmentPerformancetest AS SELECT DISTINCT TOP 100 PERCENT dbo.FS_COHeader.CONumber, dbo.FS_ShipmentCOLine.COLineNumber, dbo.FS_COLine.COLineStatus, dbo.FS_...more >>

I have a table with a billing DAY. i.e lets say i have a customer that started on 1/1/05 and he always gets billed on the 20th of every month. I need to create a BETWEEN statement but the start will always be different. So i will need to somehow look at the start date and set the end date ...more >>

Hello, I have a stored procedure that is made up of only a (complex) SQL statement. When I try to execute it, the procedure hangs (I have to cancel it after about 5 minutes). If I execute the select statement outside the procedure, it returns in about 5 seconds. Also, if I remove the par...more >>

SQL 2000 I am trying to list all stored procedures which got a reference to one of my table. Is there any undocumented system procedures or need to write a query. Please help. Thanks John ...more >>

osql -U sa -P <password> 1> exec sp_defaultdb 'username', 'dbname' 2> go Default database changed. 1> exit osql -U username -P <password> Cannot open user default database. Login failed. Sorry for asking so many questions recently... and thanks to all who respond. ...more >>

Hello all, I am running a VB app that opens a crystal report. I am trying to create a stored procedure rather than a view. The problem is, if I just run the sp, the report comes up fine. If I try to pass ANY parameters to the sp, the report won't come up. I get 2 errors "Unable to access...more >>

I have a table representing a recursive hierarchy of "areas". I have a stored procedure (GetAreaAreas) that, when given an area, returns it and all areas underneath it. The client application needs this returned as a result set. An "area" may also contain several "locations", stored in a diff...more >>

Hello Im looking for a way to get the day out of a datetime value. i.e. 1/25/2005 12:45:00.000 I want to see "25" Thanks in advance. ...more >>

I believe that I have discovered an anomaly in SQL Server. Although I am generally not concerned about the values in an IDENTITY column, the fact that the current IDENTITY value for a table is updated when a constraint violation occurs troubles me. In my opinion, if a statement is terminated o...more >>

How can I take the output of sp_helpuser and use it in updating another table? I want to update/insert the LoginName of users with access to a particular database into another table. Thanks Emma ...more >>

Hi, I'm currently contracting for a financial client who, among many other things, maintain a table (called FXRates) of historical exchange rates as follows: FXCurr FXDate FXRate ============================= EU 06/06/2005 0.675105485232 EU 07/06/20...more >>

Hello all, I have a SP that inserts or updates data from a web form. I would like to modify the SP so that when there is data in a column, but when the user updates the form, but leaves a few fields blank on the from, the SP will ignore the Null value, and not pass it to the database. Righ...more >>

I created a database, added a couple users, detached it, and attached it to another instance. (I'm using Mixed Mode authentication.) Using that second instance, the database users I originally created were refused access to the database (this was a database user problem, not a server login pro...more >>

What would some of you experts suggest would be The Top 5 Fundamentals (or top 10 or whatever) that should be thoroughly understood by anyone who wants to become a truly competent DBA? Thanks! ...more >>

Hey all, I was searching around and i found many solutions that seem to work with MS-SQL only, I am working on Teradata and i was hoping i can find an effentient query that inserts a row if that row does not exist. Actually its a bit more complicated than that. Let me give an example. myTa...more >>

i'm looking for a way to copy tables, their dependencies, and any data in them from a source database to a destination database, without locking them in the source. my questions are: 1) does the dts copy object task require exclusive access to the source database? if so, 2) what's the best ...more >>

Hi, I've got a general design question. If you have a database with 6 tables that will be updated in one go, ie an insertion will will contrain 6 tables insertions within the same transaction, what can I do to improve concurrency? If 10-20 connections are all inserting into the database t...more >>

Hi, How do I update a column in one table , using a criteria that requires inner joins. What is the correct sysntax for this ? For example : Update t1 inner join t2 on t1.col=t2.col SET t1.status=1 WHERE t1.status=0 and t2.col=some_value thanks. ...more >>

Is it possible to have a null parameter in a UDF table function, as it is in a stored procedure? Thanks. Alan ...more >>

I have an area on our website where users can customize what data fields they want to see displayed from a list of about 50 different fields. I have a query that has about 50 subqueries to pull all this info and it has just got to be extemely slow...sometimes even times out. I need to pull alot...more >>

I am creating and will be working with a database in SQL Server. However, all of my previous experience in writing SQL was for Oracle. This means that even though I have a reasonable amount of SQL experience, I need to know the small differences between Oracle's SQL and SQL Server's SQL (such ...more >>

I have grouped records with GROUP BY. How can i find the number of that records? (Using 1 query) Thanks!!! ...more >>

As example, I'll use pubs database which is default by SQL: First insert one record: USE pubs INSERT INTO jobs (job_desc,min_lvl,max_lvl) values('Publisher first',120,200) Now if you execute the following statement, you should get 2 records: select * from jobs WHERE min_lvl>100 AND job_de...more >>

hi the answer for this one is C but i would think it is D. shouldnt region be the first col and not city because there are not many ppl in the same region. You are a database developer for Contoso, Ltd. The company stores its sales data in a SQL Server 2000 database. The database contain...more >>

can you share with me on how to display a query results using stored proc with mutilple variables within the field and it goes like this... i have a list of Products with codes e.g. [Drinks] 0001 Coke 0002 Sprite 0003 Royal 0004 Crown 0005 Budweiser 0006 San Miguel Beer 0007 Vodka 00...more >>

hi in the question below A is the right answer. but i got that only when i eliminated b,c, and d . why is A correct? You have designed two transactions as follows: Begin transaction Begin transaction Update Customer Update CustomerHistory ? ? Update CustomerHistory Update Customer ? ? ...more >>