Hi I wonder if an expert is able to help with a deadlocking problem I am seeing. I have a stored procedure which executes a single UPDATE statement within a transaction running at SERIALIZABLE isolation level. The stored procedure operates on a table which has a single row and 2 columns. Th...more >>

I just got through installing SQL Server 2005 Standard. Everything went fine, but now when I click Start. I see a few choices, but not Management Studio. Where did it go? Any thought on what I did wrong (or what I need to do in addition)? Thanks, Ron ...more >>

Hi, I have a problem to write a trigger. I would to know if the 4th column of my table has been changed CREATE TRIGGER myTrigger ON myTable AFTER UPDATE AS IF (COLUMNS_UPDATED() & 8 ) = 8 ( equal to power(2,(4-1)) BEGIN here I would like to insert a row in an AUDIT table ...more >>

This baffles me... I have a query: select sum(coalesce(nullif(io.current_assets_usd, 0), nullif(io.initial_assets_usd, 0), nullif(io.expected_assets_usd, 0))) from opportunity o inner join inf_opportunity io on (o.opportunityid = io.opportunityid) where not exists (select entityid from inf...more >>

Any help would be appreciated: Table columns: time_stamp, ID, Wind_speed (Note: time_stamp is collected per minute) I need to capture data based on a specific ID and based on following criteria. wind speed > 15 for wind blowing for 2 hours continous wind speed > 10 and < 14.9 for wind ...more >>

Janet, please fix your system clock and/or time zone. ...more >>

Is there a string function that allows you to find the Nth index of a character? CHARINDEX is okay for the first or second index, but gets pretty unwieldy after that. DECLARE @myString varchar(11) SET @myString = 'a|bb|c|dddd' --- first index: returns 2 SELECT CHARINDEX('|', @myString) -...more >>

Hi there, I have a task of going through a table which contains some XML documents stored in text type of variables, extracting relevant information from each xml and then returning all the rows. I have created a stored procedure which will take the XML in text variable as an input parameter ...more >>

Hi, I am using the following to find out how many 4 week intervals there are between one date and todays date. SET @WEEK = DATEDIFF(ww, @ROTA_DATE, GETDATE()) SET @WEEK = @WEEK / 4. @WEEK is a decimal number. What I need to do is check to see what the number is i.e. 6.25 which would mea...more >>

I've built a stored procedure that accepts two variables which are strings of comma separated values that are put into table variables and then refered to in the select statement at the end of the stored procedure. I works fine. I have another report where i need to do the same thing but the...more >>

Hi, I posted this question before but have some follow up to it... I'm looking for some opinions on delimited seperators. What I'm trying to do is get data from sql and put it into a flat file and use a seperator between each column of data. I was going to use comma's but then realized ...more >>

I was wondering what the query string would be to do the following: Database Name: Database1 Table Name: Table1 Fields: Field1, Field2 and Field3 I want to specify a value and when Field1 equals that value it copies Field2 on that record to Field3. Thanks. ...more >>

I am looking for the equivalent of the isnumeric operator to find field values that are anything but alpha characters. Example value 'abcde' is OK values '8abge' or '$*abc' would display. I am trying to use this to verify the name data in a table. ...more >>

I was on holiday last friday and a backup was done of a database at 11PM by another dba. We are now wanting to take that backup and restore it to another database, however we keep getting an error saying that it cannot find file id 2 on device 'c:\dbbackup.bak'. RESTORE DATABASE is terminating...more >>

I'm looking for information with regard to the pros and cons of using table variables versus temporary tables in SQL Server 2005. I read that queries modifying table variables do not generate parallel query execution plans and can adversely affect performance if very large tables are used, or ...more >>

I am trying to improve the performance of a stored procedure. Below is the code that is consuming most of the time. For each row in the cursor text is appended (UPDATETEXT) to the single record in #taskinfo. We have reached a point where there are now thousands of records and the performa...more >>

I am trying to figure out the syntax to search a text field (comment) for any digits that are more than so many character long... In short - I want to make sure that no one put credit card numbers in the comments field of a database. I tried a few queries, but am not getting good results... ...more >>

I unwittingly made a copy of my database 'mydb'- BIG mistake. That left me with a database named 'mydbCopy.mdf'. I've tried sp_renamddb to get rid of the 'Copy' part of the name. It says it changed it, but when I attempt a Shrink Database it still calls the Database file 'mydbCopy_dat'. ...more >>

http://www.sunusb.com/freeusb.html No, I have nothing to do with them. I am just passing it along. -- Peace & happy computing, Mike Labosh, MCSD MCT Owner, vbSensei.Com "Veritas e aequitas; in nominae Patri, et Fili, et Spiritus Sancti." -- The Boondock Saints ...more >>

I was lookin for a some way for change the formula for a field in a table, but I don't find how change this field for Sql. (no in MMC) can you help me with that? Thank you very much. Rolando. ...more >>

Hello All, I need to sort a Union all query by a field that does not exist in the select statement. The following works but I do not want the line_number field in the result, I only want NC_Block field. But it must be sorted. How do I do this? select line_number,Nc_block FROM Broetje_DNC..cur...more >>

If I store string data that is a comma delimited list of key values to another table, is it possible to reference that field using 'IN'. I want to write the query like this - SELECT a.*, (SELECT SUM(b.Amount) FROM Table2 AS b WHERE b.PKId IN (a.Table2PKList)) FROM Table1 AS a When I do ...more >>

I've got a couple of 2000 servers where some databases that are bulk loaded are in SIMPLE mode. Isn't it true that leaving the database in SIMPLE mode will result in much faster load times because nothing is logged? The problem is that I am getting pushed to do log backups on these, but I am ...more >>

I have some procedures that worked in SQLServer2000. We're migrating to SQLServer2005 and got this error returned to a VB6 app: Error No -2147217900 Error occurred while ... sql = pilot_time_sp '10/3/2006 9:41:00 AM' - Error = CAST or CONVERT: invalid attributes specified f...more >>

I am using SQL Server 2000 and trying to modify my database to a different location but getting error messages. USE MASTER go Alter database Asset modify file(name = Asset_Data,filename='K:\SQLdata\Asset_data.mdf') go ...more >>

Hi Everyone, I have a performance issue with a SQL Server query and i need some assistance with it. It would be great if you could offer your inputs on it. We have a SQL Server database that is of a rather large size [20GB +]. The most significant table "Order_Detail" has a large number of ...more >>

Hi, While I am able to retrieve data directly in the form of an openquery statement in QA, I am unable to do so when I apply it in a stored proc. I get the error: Server: Msg 8501, Level 16, State 3, Procedure spc_GFolio_Init, Line 38 MSDTC on server 'HAS02' is unavailable. Here's the a...more >>

If I have a stored procedure that does a number of things and do not have a delcared transaction, will it be logged as one transaction or will each piece be a seperate transaction? Here is what I mean... SP Test1 Insert data Insert data Update data Update data Dele...more >>

I found this code at http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm and it works great for passing in an unknown amount of account numbers for a report that I've built. I have another report where i need to pass in two fields to the table Variable -month and year. Ho...more >>

Hello everyone, I'm stuck on one of my calculations columns here and am asking for some help. I'm simply looking to calculate the percentage of two columns in my database. Below is my portion of SQL that needs some work. Please take a look and let me know what needs to be added to make ...more >>

Hi all, We are designing our entity , I am confused about the criteria to determine whether my two tables should be put into Supertype/Subtype relationship. 1) Just because two table have common attributes, is it enough reason to create supertype-subtype on them ? What are other reason ? ...more >>

I have the following tables in db repairresources: dbo.users with columns: -city -state -idusers dbo.rmainfo with columns: -ascname -idrma -ups -shiptech -loccode -idusers The information I need to return is (above) where the idrma is a variable and when it is entered it will show...more >>

Hi, is there any easy way to export data from stored procedure to excel file on SQL 2005? Import/Export wizard allows to export only tables and views. The solution could be saving my select as view, but it contains PIVOT so it is not possible. Thanks Przemo...more >>

This is kind of complicated. I am using a timer proc to access sql server to fetch new records. Am using ADO. It runs most of the time but then it start giving error SQL Server does not exist or access denied How is this possible ..... Cause most of the times query runs and fetch records...more >>

I am having a problem getting a Stored Procedure to compile using sp_addlinkedserver to connect to an Access mdb database. My test Access database only has one table in it, which I am using for a simple test to try and get this work. I keep getting the following error message: Msg 7202, L...more >>

This is a follow up to the question I asked a couple of days ago http://msdn.microsoft.com/newsgroups/default.aspx?&query=stvjston&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.programming&p=1&tid=2e28c5d3-45b0-4fa1-9b19-588e801732b6&mid=2e28c5d3-45b0-4fa1-9b19-588e801732b6 I add...more >>

Each of the records in my table represents a different counter. Each counter has a name and a value. I need a stored procedure that both increments a counter and returns the value. I’m particularly worried about maintaining concurrency and performance. This stored procedure could simul...more >>

I was usinig a program where I accessed an ADO fields collection containing a column of type Bit on a SQL server. My first version found I that I retured a 1 or 0. No big deal . Well, without changing any programming, the SQL server started returning only True and False. Is there any sort of ...more >>

Sorry, it possible design not programming question, but I don't know where I coul post such messages. Anyway it's intresting topic. Situation: MainRecrodsTable -------------------------- MainRecrodsTableID and two (really more) other tables: AttributeSet1 ------------------- Attri...more >>

Hi All, I’ve a string with following format '3AF184A1-57B5-4F5A-AC76-AEED842672FF_Water lilies.jpg' i'd to retrieve only the "Water lilies.jpg". without "'3AF184A1-57B5-4F5A-AC76-AEED842672FF_" thanks, Obreen ...more >>

Dear all, Suppose I got a table as follows Field_A Field_B A 123 B 456 Now I got a value "456" and I don't know w...more >>

Hi, I am having trouble with "Could not continue scan with NOLOCK due to data movement." I have a very large table in which i need to delete rows without having neither the rowlocks to escalate nor the transactionlog to blow up. So i have created a query for this. Unfortunately it doesnt wor...more >>

Hi, I want to be able to work out what day is todays date, but I want Monday to be day 1, instead of the default of Sunday. Is there anyway I can do this? This is my code @ the moment:- SET @WEEK_DAY = @ROTA_DAY - DATEPART(dw,GETDATE()). This is coming out with a day less than the actual ...more >>

I am migrating a classic asp/access app to .net/sql server. The old app has lots of sql in the pages which I want to move over to stored procedures. As a SQL Server newbie, most of it is straightforward for me, but the search page is posing me a puzzle. The search form contains 4 optional...more >>