Hi! We are using rowversion fields for optimistic locking in a webservice scenario. When updating data we also want to retrieve the new rowversion of our updated rows in order to check for a lock violation in subsequent updates. We are searching for the most efficient solution for selec...more >>

I have two tables tbl_participants part_id PK full_name tbl_weekly_picks part_id FK pick_winner actual_winner total_pts I want to do a sql query to find the total wins, losses and pts. The pts are easy as they are a computed field (sum(total_pts)). If the actual_winner = pi...more >>

I have 3 tables :- 1) Files - fields: FileID, Filename 2) Projects - fields: ProjectID, ProjectName 3) FileProjectLinks - fields: FileID, ProjectID Files table keeps the list of all files, whereas Projects table keeps the list of all projects. A file may have been used in one or more projects...more >>

I am developing an application that has two tables that are related by date. Table has daily records. Table2 however is a reference table that only gets updated when the reference information is changed. This means I have a kind of loose relationship between these tables where I need to look...more >>

Return = REPLACE(Original, GetsReplaced, Replacement) Simple, right? run this in q/a: -- Table of Diacritical characters and replacements CREATE TABLE dbo.DiaRepl ( D NCHAR(1), R NVARCHAR(2) ) GO -- Data INSERT DiaRepl VALUES ('', 'UE') INSERT DiaRepl VALUES ('‚', 'E') INSERT...more >>

How do I test whether data has been filled into a date or numeric field? For example, using WHERE (((Table1.BirthDate)='')); does not work. (It gives a 'data type mismatch in criteria expression' error) Andrew Spencer ...more >>

Precisely, here's what I need: When I run getdate(), I get, for example: August 9 2004 5:17 P.M. I want to turn the date portion into: 8/9/2004 format and update one column with it I want to turn 5:17 P.M. into: hhmmss and update another column with it. I've been playing ...more >>

select left('1234567', 3) returns 123 but how can i just return the value of the character residing in the 3rd space from the left - in this case '3' ?? ...more >>

I'm trying to do an insert into one table by selecting rows from another. The target table has a primary key but it isnt an identity field. Which means i need to figure out the next integer value to put into the id field of the target table. How do i do this? I tried the code below (well simi...more >>

create proc Temp DECLARE @TSql VARCHAR (500) as Select * from @TSql go exec Temp "Users" go I'm trying to do something simialr to the above. Basically, it's a helper proc that I need to handle some error conditions that occurs in the DB. Is something like this possible? (Without execut...more >>

Hi All, Sorry for cross posting , but my neck on the line forced me to do this disgusting act. I have an Extended Stored Procedure DLL made in VC 7.0 It was working fine on my development PC (Windows 2000 Professional ) and worked just fine on couple of other development PCS. When the DLL ...more >>

If I run debugger on my local machine, I cannot step the stored procedure when debugging. I have to remote desktop into server and do it there. Is there a setting somewhere that will allow me to debug stored procedure on an SQL Server ( 2000 ) from my local PC? Thanks, Andrew ...more >>

I'm using Query Analyzer and looking at a list of our user-created stored procedures with its Object Browser. How can I tell the modified date of any of these? I know when I use CAST SQL Builder I can see the date, but CAST is not available on this server. What other tools do I have avail...more >>

I have an existing table. I want to change a column's default. I know I can use Enterprise Manager to do. Can I use Sql to do it? I tried 'alter table' command and it does not let me add a new defaults to the column. Any suggestions? Thanks....more >>

Hi, It is a basic Transact SQL question. The SQL Server generates such scripts for table/object creation: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ForReg_PosNeg]') and OBJECTPROPERTY(id, N'IsRule') = 1) drop rule [dbo].[ForReg_PosNeg] GO CREATE RULE ForR...more >>

Microsoft Access has a function called "Val" that takes a string as an argument and returns the numeric portion of the string, e.g.: Val(Z9698) returns 0; Val(9698Z) returns 9698; etc. Is there an equivalent T-SQL function? ...more >>

I've got three input parameters in my stored procedure: @GroupCat int, @Brand int, @PID int which will help filter the query results. However these are all optional. If they are not used then they will pass the default value of 0. My problem is trying to make a flexible WHERE clause to ...more >>

I want to be able to execute an SP (that takes one parameter) multiple times for a set of parameters. I could probably do it with a cursor after a SELECT but thought there would be some way to do it with sets. What I'd *like* to do is something like this: EXEC GenericSP (SELECT id FROM Cust...more >>

Hi all, I have a several tables in my database. They all contain a column called recid - varchar(15). I must use varchar(15) for this column. I need to store a unique id that will uniquely identify each record in the whole database not in just one table. How can I achieve this? Thanks....more >>

Hi: Is it possible to define a field or index attribute that will not allow a field value to be modified once it has been saved? Under other databases I could define an index as non-modifiable, this is what I'm looking for with MS-SQL2000 TIA, Martin. ...more >>

But never done so before. I have read the whole set of topics about Cursors in BOL, and I'm just not getting it. Could someone post a simple snip that opens a two column cursor, iterates over its records and prints their values? Something like: DECLARE rs CURSOR FOR -- need to declare re...more >>

Hi all Gurus I have a big task Is there any tool That can help me to convert stored procedure from oracle to sql server or db2 to sql server. Even I am ready to develop a tool which can help me to convert the stored procedure from other database to sql server, Can any one guide me how to s...more >>

Hello How can I select 10 records from a table of 100... ...imagine that the user select Record Number 50 and the SQL would give-me the data from record 45 to 55, not the SELECT TOP 10 -- Bruno Alexandre (Sintra, PORTUGAL) ...more >>

I have a situation with a view. This view uses 2 or 3 or 4 tables to retrieve data, doesn't matter I have several. Someone came to me and said "During design, I forgot a field..." in one of the tables that make up this view. So the field was inserted into one of the tables. Now when I execute th...more >>

Hi all SQL 2k I have a members table that holds a forum signature that the members can have to put signatures on the forum I was wondering what the best way to store this would be... either a) Sig VARCHAR(500) NULL b) Sig VARCHAR(500) NOT NULL DEFAULT('') in the Stored Proc I hav...more >>

If I have a foreign key COLUMN in TABLE, do I also need to explicitly create an index on COLUMN to improve performance, or will SQL Server create something under the covers automatically? Thanks, Bill Gregg *** Sent via Developersdex http://www.developersdex.com *** Don't just particip...more >>

My Stored ********************************************************* CREATE PROCEDURE st_seconddegree @fromwhom numeric(18), @towhom numeric(18) AS if not exists (select 1 from crosstable where (fromwhom=@fromwhom and towhom=@towhom ) or (fromwhom=@towhom and towhom=@fromwhom)) Begin create t...more >>

Hi, I have an INSTEAD OF on an insert for my table. Im using it to check a uniqueness on a combo of fields (im not using a UnqConstraint because it only needs to be unique for a certain value in one of the fields, others can be duplicates). Can i in any way from the trigger, re-issue the...more >>

I imported a text file to a sql server table. I need to look at the 4001th row. Is there a way to retrieve just that row without using criteria? I don't want to do a select *... and then scroll down to row 4001. I was thinking something like select between top 4002 and 4000 * .... ...more >>

Hi everyone, I need help on the query below. To quickly explain, I would like to log in a separate database, all the members that exists in [ST Fact - T3] BUT does not exist in [ST Dim - Phyysicians] table. The tricky part is that if this member already exists in the log database, it does not ...more >>

I have many stored proc. with a VB.NET application. Under XP work good. I deployed that in WIN 98 + MSDE200A. Here that the stored proc. where I use the sp_xml_preparedocumentum function don't work. Why don't work the stored procedures with sp_xmp_prep. uder the WIN98? Please help me! Th...more >>

I posted this question in the sqlserver.setup group, but only got a single answer which I felt was a more general RAID 0 observation. It is sort of performance-related, so I hope noone will tell me off for posting it in the programming group :) Any input on this? ---original question--- ...more >>

Hi, I am just wondering... anyone who has played around with Yukon... Will there be the addition of true "BEFORE" triggers on tables? It is something SO lacking because these INSTEAD OF triggers really just dont do the trick. Theyre great if you want to do something completely different ...more >>

is it possible to include an if loop or case statement in a where clause of an sql statement? For example: I have a stored procedure which gets a @type parameter passed in. Select * from Jobs where active=1 now if @type=1 then i would like to include it in the where clause else ex...more >>

Hi! I am trying to insert records in the table #B in such a way that everytime ID field increments by 1 for every new record. Please see below the DDL: create table #A (IDnum int) insert into #A values (500) create table #B (ID int, Contract varchar(20)) create table #C(Contract varch...more >>

Hi I need to dts information from my table into a flat file when this updates my text file it must flag my table so that i know what has been written to the text file. Is this possible Many Thanks AQ *** Sent via Developersdex http://www.developersdex.com *** Don't just participate ...more >>

Two questions related to SQL searches (ideally everything will be in a stored procedure): I have an ASP.NET checkboxlist on a search page (this is a search of properties for sale). The checkboxlist is responsible for displaying the available amenities in a home (air conditioning, patio, gar...more >>

Hi, I have a process that collects data from sensors and stores the data in a table in SQL Server. The data can arrive multiple times per second for several hours at a time or I can only get one or two data entries per hour. I want to limit the number of rows that can exist in this table. I...more >>

Hi I probably know the answer but have to make sure. You cant do a If exist on a view when altering, is there any way around this? I do not want to drop the view and re-create I find it does not always update sysdepends. I basically want to alter the view if it exist else create. Thanks...more >>

Hello, I need to update 80,000 rows in a table with 1.5 million rows. What is the fastest way to Bulk Update the rows? I would prefer to be able to Bulk Update via a SQL connection rather than using a command line utility. Is there a BULK UPDATE command like BULK INSERT? Thanks. -...more >>

Hello All, I'm struggling to work out how to create a clustered prime key constraint on an exisitent column in an existing SQL Server 2000 table. I need to do this in TSQL otherwise I would change the table design in Enterprise Manager. I've been playing with the syntax, but I just can't se...more >>

hi all i like have a string like this declare @tt varchar(200) set @tt='select sigroup from dbo.paygroup when sigroup=9' when i execut this i see result 9 "sigroup an integer field" so i want to declare an integer var and assign this result to this like this declare @rr int, @tt varchar(200...more >>

Hi All, I need to write a bcp program using a cmd file to export the data from a table (using select query) to .dat file. Problem: 1. The problem is the destination file name is stored in a table. How can i write a bcp program in this case? 2. Can i use bcp in stored procedure without...more >>