Hi, I'm using SQL 2000. I've XML data stored ntext column. The XML is <vItem> <platform configuredProviderGuid="00000000-0000-0000-0000-000000000000" /> <properties priority="Normal" exclusive="false" /> </vItem> Now i want to create a query and want to Display Priority (Attribute under ...more >>

Hi all, Is there a way that I can find out if a SP takes parameter and of what datatype that is. Preferbly via a select statement or running another Sp. Thanks Robert ...more >>

Hi, I have what appears to be a simple problem but it is cracking me up. I have to insert records into a table every day. The tables deals with account numbers. No problems there. But one of the fields is called 'daysonreport' which is basically a number which signifies how long the r...more >>

Hi, We are thinking of changing the primary key of our table from GUID type to squential id using the new NEWSEQUENTIALID function. But the issue we are having is how to efficiently retrieve the generated value because we need to pass this information back to the client app. With GUID we ha...more >>

Hello, I am trying to write some basic queries and am running into issues. The first one I am working on is this a query that determines which products cost less than the average cost of products available from a particular store. The second one I am working with is writing a query that l...more >>

I have a storde procedure which compares one column of a table against each of the other columns in the same table. If it finds a 'one' in each column, it counts the number of 'paired data' . As there can be up to 60 columns and 60 rows in any table , can anyone suggest a shorter , more effici...more >>

I have an ASP/ADO application querying an SQL Server DB. I want know the most efficient way to determine if more than one row is returned from a query. If more than one row is returned, the user will be presented with a choice of which row to process. If only one row is returned, I want to ski...more >>

I have a select statement that puts records into a temporary table and then emails the records as an Excel Attachment. How, if there are no records returned in the 1st select statement can I stop executing the remaining steps. I don't want to email a blank Excel file. thanks Mike ...more >>

What is the correct order to do these actions: - dbcc shrinkdatabase ('mydb') - dbcc dbreindex ('table'); dbcc dbreindex ('anothertable'); Any help appreciated. Best regards. -- fabri MKDS: Joker® - <waiting for ds lite...> AC:Fabrizio, Kanoemi, 1890 4700 1546 Tetris DS: 748422...more >>

Say that the values of Sev3Met = 1 and the value of Sev3Total = 2 Using this round((Sev3Met/(Sev3Total + 0.0)),2), I would expect .50 However when I code in SQL 2005 Express manager against a SQL 2005 database, the results display as 0.5000000000 Using this round(Sev3Met/cast(Sev3...more >>

Hi. How does one programmatically do a SELECT on another database on the same server? I've got a stored proc in "EducationData" database that needs to call a SELECT on table "tblStudents" on the "Directory" database. This is on SQL Server 2000. Also, I've installed the Books Online from her...more >>

I have a C# stored procedure that I use to run a query, do some processing on the results and send the results back to the caller via SqlPipe.SendResultsRow(). It looks something like this: string myConditions = MyFunctionToBuildTheConditionsFromCallerProvidedData(); SqlCommand cmd = new Sq...more >>

I need to call a stored select proc frmo an update stored proc. How do I do it. here is the proc that does the call INSERT INTO tblFileRecords_Stage2 ( FirstName, LastName.......) SELECT CALLEDStoreproc.FirstName , CALLEDStoreproc.s.LastName, ..... FROM CALLEDStoreproc The proc CALLEDStore...more >>

Hi, I have the foll table col1 col2 1 2002-01-01 1 2003-01-01 1 2004-01-01 2 2004-01-01 1 2005-01-01 how can I display the most recent 2 or 3 of id 1? Thanks...more >>

Good day all, I am hoping this will be a relatively easy question to answer. I need to compare two lists of phone numbers, which are stored in two separate databases. The first list is static, and the phone numbers come in the format 123-456-7890. The second list has the phone numbers comin...more >>

What are some acceptable ways to deal with "required" data that is unavailable at the time a row is added to a table? Example: Lab tests are requested for a patient. Some results come in right away and others come days later. All are eventually required in order for the patient's lab record...more >>

Below is a table call sv_call_log. The coulmns are row_id, card_num, start_date and end_date. They all run together but to fit it on this page I put the start_date and end_date below. The start_date is when a call begins and the end_date is when a call ends. We currently have 4 phone lin...more >>

How can I run a query to see if a database exists? I see the queries like using the sysobjects and xtype to query different table names and stored procs...but can I use something similar to see if a database exists? Something along the lines of: if exists(Select * from sysobjects where xtyp...more >>

using mssql 2000... Table Def... CREATE TABLE [dbo].[tblEmployee] ( [EmpID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [EmployeeID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DateCreated] [datetime] NULL , [LastModified] [datetime] NULL , [Deleted] [bit]...more >>

Hi All I am trying to run some straightforward update statemtents to update all rows on a table, for example: UPDATE MY_TABLE SET X=1,Y=2,Z=3 And this is taking hours on end. Any ideas/direction on what I could look at to see if I can improve this? Thanks!!!! *** Sent via Developersd...more >>

Heres my problem. I am trying to update a Column (400000 Rows) with Random Values, The column is a varchar(9). Actually it is a Col with SSN # and I am tryng to get the column ready for TEST environment. So i want to scramble the Original # and get in ake RAND() generated numbers. I generate ...more >>

Hi all, Does SQL2K5 have any new features that allows to easily prevent an Update statement from updating the row if none of the column values have changed? For example take the following statement: Update Persons set name = @name, phone = @phone, modifydate = getdate() where id = @id ...more >>

I'm trying to convert a check amount to a fixed length string with leading zeros and no decimal point. All is well, except for the pesky decimal point. Here is what I have: COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') + (CONVERT(varchar(12),ckamt)) Thanks for any ideas on...more >>

I am creating a small project management application using SQL Server. There are three main tables (prjProjects, prjTasks, prjDetails). A project can have 0 to many tasks and a tasks can have 0 to many details. Many of our projects are very similar in that they would consist of basically th...more >>

Hi, I'm trying to get to the provider options screen in SQL2K5 Management Studio, however I am unable to find it anywhere. This is really frustrating... Here is what I am trying to do: 1.. In SQL Mgmt Studio, connect to the SQL Server Database Engine and go to Server Objects->Linked Serve...more >>

I have a table that has the following data Key1 Key2 Related AB12 50 0 AB13 50 0 AB14 51 0 AB15 52 0 AB16 53 0 AB17 53 0 AB18 53 0 AB19 53 0 In the related column, I need to add up the Key2...more >>

I am new to user-defined functions in SQL Server. Can someone please shed some light on my question. I am trying to call a user-defined function from within a where clause but I am getting errors. If I move the same UDF call to the 'select' part, it works. It appears to me that it is not...more >>

Created a DTS Package is SQL that drops a table then recreates it then imports data from an excel spreadsheet. This works fine if you run it from the Local Packages and click execute package. But, if you run it using the xp_cmdshell it doesn't popluate all the columns. It will fill the 29 column...more >>

Hello! 1. Is there a performance difference using the IN keyword or the OR keyword? 2. Does SQL Server internally convert one of the queries into another, e.g. a query using IN into a query using OR? 3. When should I use OR and when IN (I know about using IN with subselects)? For ex...more >>

I have the following table: CREATE TABLE [dbo].[ShortStay]( [SS_ID] [int] IDENTITY(1,1) NOT NULL, [SS_WLID] [int] NULL, [SS_From] [smalldatetime] NOT NULL, [SS_Till] [smalldatetime] NOT NULL) There are these records for example: Insert into ShortStay (SS_WLID, SS_From, SS_Till) v...more >>

Dear All, I want to create a trigger on a table (A) which can copy the records into new table(B) whenever it updates or any new record inserted into the table (A). Kindly create this trigger for me. TIA ...more >>

I am trying to discover the source of an error that occurs in some code that I run regularly. I have found a problem that when I try to update a table with a calculated figure a rounding error occurs. This problem can be shown by running the following code: UPDATE NumericVal SET ValExpecte...more >>

I need to save my files from SQL Server 2005 Management Studio in windows ascii text and not unicode. How i can do this? Thanks...more >>

How do we go about setting a C/S application. Here are the steps. 1. Network Operating system. 2. SQL Server 2000 Installation on a Client / Server Network. 3. Install and setup Active Directory Services on a Client / Server Network. 4. Concept of Domain Controller in a Windows Network. 5. S...more >>

Hello All, I like to output my next query to a file. File type maybe CSV or XLS. Code : SELECT cicmpy.debnr, cicmpy.PriceList, cicmpy.cmp_status FROM cicmpy LEFT OUTER JOIN cicntp ON cicmpy.cnt_id = cicntp.cnt_id WHERE debcode IS NOT NULL AND (cmp_Status in ('~','A','B','N','P','R','S')...more >>

I am writing a small utility to capture Blocking/Blocked processes that is running for certiain period of time...say 5 sec or more. logic that I use in the stored proc is (Current Time Stamp - sysprocesses.Last_Batch) for the duration of the process. But I am getting incorrect values!!! ...more >>

Hi folks, Can somebody post a link which has information about migrating databases from SQL Server 2000 (32 bit) to SQL Server 2005 64 bit. Your help is greatly appreciated.. Thanks in advance, Venkat ----- *** Sent via Developersdex http://www.developersdex.com ***...more >>

Structure of DB Fields ----- FieldID Description Details -------- DetailsID FieldID RangeDayID RangeHourMonID RangeHourTueID RangeHourWedID RangeHourThuID RangeHourFriID RangeHourSatID RangeHourSunID RangeDays ----------------- RangeDayID >From (DateTime) To (DateTime) O...more >>

I have 1 table "Progress" P_no b_no status build_date 25 1 First_slab 2006/4/5 25 1 second slab 2006/5/6 25 2 first slab 2006/1/2 25 2...more >>

I have tried the following in all kinds of combinations but cannot get it to work. At this point I am not seeing the problem straight and need new eyes to guide me. CREATE TABLE EmpEvals ( last_name varchar(25), first_name varchar(25), begin_dt datetime, adj_beg_dt datetime, term...more >>

I have a large table (not so many rows: 200.000 or 300.000, but a field ntext that is about 150Kb per rows). Executing sp_spaceused 'table' I obtain that data used is about 2.8 Gb but data reserved is 8 Gb and about 5gb unused :-\ What does it mean? Do I have to do something with this? ...more >>

Hello, when i'm insert new data into my sql 2005 Database table i get the error: The supplied value is not a valid instance of data type float...... This happens when i insert a float value like 2E-310. In Sql 2000 it was no problem and when i insertet smaller values in sql 2000 Zero was save...more >>

A SP with join's between tables of 2 SQL 2000 linked server used to work relatively quick. Since a few weeks the same SP that runs on +/- the same amount of data takes much more time (43 sec). When i copied the DB so that the SP should run on the same server it takes only 13 seconds. Probab...more >>