hi guys! just want to know if the answer on this thread is the best one. please check it out: http://msforums.ph/forums/87274/ShowPost.aspx i don't know if the bitwise operation generates the most efficient query, please verify, thanks! ...more >>

Hi! I want to create constraints, but I want to check if the constraint exists first. How do I do that? I think something like following example would work for me, but I'm not sure how to check... filling the space between ...exists (...) IF NOT EXISTS (.......) ALTER TABLE UserTab...more >>

My dynamic sql correctly returns the top 10 dates with records if @timeID equals 1. I need the @timeID equals 2 part to return records between @dtStartDate and @dtEndDate. (I just set the enddate equal to maxdate to keep my example simple). In the below section where @timeID = 2, I'm gettin...more >>

Hi, I need some help formulating a query. Below is an example records. What I am trying to do is to come up with a query that will tell me which record has a position_startdate that is less than position_enddate from the record above. ID EMP_ID POSITION POSITION_STARTDATE POSITIO...more >>

I've been quite busy these days looking up which was the best way to create a full-text search catalog for a field composed entirely of HTML content, and I found out that the best way to handle it, so it could ignore HTML tags as the search was performed, was to make the column an Image-type ...more >>

I figure there is someway to pull this out but I can't find it anywhere. I have a recursive stored procedure and I want to error out if it recurses more than 10 times. How can I pull out how deep in the call stack I am? Is this possible? I know I could pass a parameter to the stored proced...more >>

Guys, I have to replace some old chinese characters (words) with some new ones. In the database, the fields are displayed as ???. The current chinese characters will display fine when you retrieved the fields through the website. As soon as I cut and paste the new chinese characters onto ...more >>

After OK parse, I try to execute this statement in a trigger: if inserted.col1 = 10 --col1 type int --do instruction But I have this message: The multi-part identifier "inserted.col1" could not be bound. What is this problem?? How can I resolve it??? Thanks! Good night!...more >>

I'm just wondering if I install SQL Express + Express Manger will this include SQL Server profiler ? If not is there any way to get SQL Server profiler without purchase a complete version of SQL Server 2005 Standared or Entreprise editions ? Thank you ...more >>

I have a few tables which I'd like to protect from accidental deletes. I'm thinking adding an INSTEAD of trigger on DELETE to the table that raise an error and return could be an approach (not sure if its the best). Something like: CREATE TRIGGER trig_DeleteNoAllowed ON sandbox.dbo.Table1 ...more >>

The following statement will display all records in WF_FMDispatch table. In this case, the LEFT OUTER JOIN is working. However, as soon as I filter the WF_SupplierEntity table , only records with matching pair f_vendor_no = factorSupplierID are shown (statement #2). Am I missing something here...more >>

I'm trying to speed up a query by joining on a known index. However, I've forgotten the syntax. Could someone please help me? JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) <-- here!! Thanks a million, Patrick ...more >>

Hi everyone, I have 2 questions regarding table hints: 1) While doing INSERT/DELETE/UPDATE which table hint makes more sense: with (rowlock) or with (updlock)? Is there any difference between the two? 2) From SQL Server Help: "UPDLOCK has the advantage of allowing you to read data (witho...more >>

Hello: I have a table with id (int) and text1(varchar) that import from .dbf and I have to get all row or record that have some len in this text. I make this query: select id, text1, len(ltrim(rtrim(text1))) as length from table but I have some text1 with this values '' and havethat th...more >>

I have a stored procedure (see below) that inserts records into a history table from a source table (PayTotals). After that is successful, I would like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr = @PaidYr" but only if the previous INSERT is successful. What would I nee...more >>

How do i update a column to the similar column of a similar table with common keys? Thanks. -- bic...more >>

hey everyone, i want to update this table COEMP EmpId - Email 999 - NULL with this data RDKCOEMP EmpId - Email 999 - abe@rdk.com i tried : UPDATE COEMP SET Email = SELECT Email FROM ad_2000_beta.dbo...more >>

Okay, using examples I found on the internet, I managed to piece this together. Now the part in the data section, works, and i get the data to come out. Once I add the pivot to it, it stops producing results. I am guessing that I have 2 or 3 major problems with this. 1. Where do i sen...more >>

hey all, is a 5-table join a lot of tables? the reason why i ask because this one table has so many code fields (that reference master files which contain descriptions i need for a report). how do you get around that? thanks, rodchar...more >>

I have two tables: Trans & History. For each record in Trans, there can be many in History. I want to return records in the Trans table that have a certain status in the History table but not other statuses. Here's an example: select transactions.transaction_id, name from transactio...more >>

hey all, is it better to build a view and use it in a stored procedure -or is it better to put the T-SQL used to created the view directly in a stored procedure and not use a view altogether? thanks, rodchar...more >>

While talking about breaking some larger queries into a parallel process with a college, they propsed that SQL may decide to execute code in basically any order it feels like (per any determined execution plan). I asked for an example of this, but thought it'd propose the question here for a qu...more >>

Hi all! Hope you can help me to find a way to do a quite tricky calculation. The task is to calculate the amount of time an ATM (Automatic Teller Machine/Cash machine) is available to the public. It may be unavailable due to failure of the machine, inacessible location (closed shopping mal...more >>

SQL SERVER 2000 SP3a My question is not about this exact statement, its to do with table hints. How many times do I specify the table hint _with (updlock)_ when the same table is used in a statement. eg update table_a with(updlock) set table_a.col_1 = b.col_1 from table_a a with(up...more >>

Strangely, I cannot find a description of the USE T-SQL command in any Microsoft documentation and Help that I have. Within a script it is used to set the focus of the subsequent T-SQL commands to a specific database. Normally I'd use: USE DB1 CREATE PROCEDURE A .... USE DB2 ...more >>

Hi all, First Happy Holidays to all and a big thanks for all those who help us in our times of need :) I am writing a vbscript/ASP app for our website and have come across a problem. We have three external databases, first is the member database, second is the dependent database, third is t...more >>

I am clear on what a table (full) scan is, but I am NOT clear on a Range Scan. It seems that a Range Scan is a searching of a sub-set of values from an index (if I am reading it correctly). That being the case, does that mean that Range Scans are the normal operation of a select on an indexed ...more >>

and she wonders why it doesn't work. I will now crawl back under my rock. Thanks for listening. -- Peace & happy computing, Mike Labosh, MCSD "When you kill a man, you're a murderer. Kill many, and you're a conqueror. Kill them all and you're a god." -- Dave Mustane ...more >>

I tried to write some test code that looks like: DECLARE @ErrorValue int sp_addmessage 50001,10,'This is a test error message' RAISERROR (50001,10,1) SELECT @ErrorValue = @@ERROR If @ErrorValue <> 0 ...etc. I expected @ErrorValue to be 50001, since I tried to move @@ERROR t...more >>

This query is driving me insane. I need to extract the number of rows in tblUsers where the first 3 characters of "fldSurname" are the same and they where born "fldDOB" on the same year and month. Can anyone help ?...more >>

Does nayone have any insight into how to access WMI using sp_OACreate?...more >>

Ok... it's stupid but I'm getting crazy... SELECT DATEDIFF("D",ORD_DTRSC,BLT_DATA) AS DAYS FROM BOLCLI INNER JOIN ORDCLI ON BOLCLI.BLT_NORD=ORDCLI.ORD_NUM AND BOLCLI.BLT_PRORD = ORDCLI.ORD_PRG WHERE ORD_DTRSC > 0 how can I have the COUNT() of the days grouped by positive and negative? s...more >>

I have a UDF for splitting delimiter strings: CREATE FUNCTION Split (@Source varchar (5000) ,@Delimiter varchar (10) = ',' ) RETURNS @T table (F1 varchar (100)) AS --Accepts a source string @Source and parses it to break it up into single units --delineated by @Delimiter. --Returns a ...more >>

hello, i have the problem that the week nr calcualted from the actual date in not localized.... in germany for example - from 21.11.2009 till 27.11.2005 there is the week nr:47 if i execute in the queryanalyser Print DATEPART(wk, CONVERT(datetime,GetDate(),104)) Print DATEPART(wk, CON...more >>

I have three date parts namely, Year Month Date/Day as integer values stored in one column each in a table in a SQL Server 2000 database. I need a function to serialize/compose/create a datetime type out of them so I could use that in a query (pseudosyntax) as below: SELECT CreateDat...more >>

Is it wise to use a cursor to loop through records and use SET commands for variables for a very large table - like 1.2 million records? Thanks!...more >>

hi, my procedure is create procedure ct_tpin(@Start_Date datetime,@End_Date datetime,@Rpt_Name varchar(50)) as begin case when @Rpt_Name = 'TpinGenerated' then select gr_tpin_flag_t.KEY1, gr_cust_m.Name,gr_tpin_flag_t.dateandtime FROM gr_tpin_flag_t, gr_cust_m WHERE (gr_tpin_flag_t....more >>

Hi to All! is there any way i can view the staements issued by the user against a particular database without starting a sql profiler trace. Thanx *** Sent via Developersdex http://www.developersdex.com ***...more >>

I have following Invoice Nos. LS-123 LS-123R LS-123A LS-124 LS-124R LS-125 LS-126 1. I need to delete all invoice nos. which have been issued again as 'R' at the end of Invoice No., for example: LS-123 2. I need to delete all invoice nos. which have 'R' at the end of Invoice No., for...more >>

Helloo.. I there anyway to make ntext search faster?? I have a table of legal documents with 2 million text record, While no full text index & search on text fields searching with "Like is very very slow". I need anyway to make it faster using any techniques.. Do i need to use cursor or how ca...more >>

Hi all, I have a Japanese SQL2K server that uses the following servertime format returned by getdate() - 2005-11-23 23:29. The problem i'm experiencing is when I use the DATENAME function, it returns me the INT value for November (11) and not the name. Very weird. It works correctly on m...more >>

Is this valid query: select a.ID_Attachment FROM attachment a INNER JOIN message_attachment b ON a.ID_Attachment = b.ID_Attachment INNER JOIN message c ON b.ID_Message = c.ID_Message WHERE c.ID_SOMETHING = 1 ? ...more >>

Adam, This link http://www.sql-server-performance.com/covering_indexes.asp was exactly what I needed. Thank you. Sincerely Patrick ...more >>

Hi I have a VB6 program that talks to SQL Server 2000 database. The Connection String is as below: Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & M_USER_NAME & ";Password=" & M_PASSWORD & ";Initial Catalog=" & Database & ";Data Source=" & M_SERVER When the program is idle...more >>