In my web form I recieve the following message - Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. I believe that part of the problem resolves around having a nulls (which i need) in a foreign key column. When i pu...more >>

Hi, I am trying to implement a global full text search on our SQL Server. Our app has several entities that are stored in the DB. I would like to be able to search for 'John Doe' and get results in all types of entities. Problem is: - FT Search does not crawl views. Unfortunately, each entity ...more >>

Hi, Is it possible to create trigger on an indexed view? I tried and it keep give me this error: Server: Msg 208, Level 16, State 4, Procedure Tr_TmpTrigger, Line 1 Invalid object name 'dbo.VIEW_MYVIEW'. I also attempted to use Enterprise Manager tool to create the trigger on the indexe...more >>

I need to write an UPDATE statement that adds to a field from data in another table. Can someone help? below is sample: UPDATE TableA SET Total = Total + TableB.Amount FROM TableB JOIN TableA ON TableB.EmpNo = TableA.EmpNo WHERE TableB.PrdYr = 2005 When I do this, it does not add in the...more >>

HI I want to make a nested SP that finds out whether a certain record exists (keys: stockItemId and cartId) and return a contents of a "Quantity" column, else 0; I can't find anything in the BOL on how to select into a local variable. Also how do I avoid the SP returning an open recordset? An...more >>

hi, how to find out whether my database have identity columns or not? I don't want to go check through all the tables one by one. ...more >>

My customer has a lot of govt customers and has a non-classified SharePoint 2003 implementation with SQL2K for the backend (all SPS 2003 content is stored in SQL server). They're very concerned about what to do if/when a user unintentionally uploads a classified document to the site. Deleting ...more >>

I have 3 datafiles (A,B,C) in one filegroup and due to space I want to move the data from one of the files to another drive. Will the following work? 1) Create new file (D) on seperate drive 2) Do not allow growth for A and B 3) Run DBCC shrinkfile with the empty file clause against C. If...more >>

I'm using sql2k. I'm providing a simplified scenario here. I'm trying to build a fact table on sales (ie. item, price, quantity, price*quantity). I'd like build a cube that I can look up the price by range ($0-$5, $5-10, $10-$15, etc...). What's the best way to handle this? do i need a price...more >>

I keep getting Severity 20 errors from my server. How can I find out what is causing the error? I have version 8.00.760 and is used with a large 3rd party application. We can't upgrade to SP 4 and still be able to get help from them on other issues since they haven't "blessed" the service pa...more >>

Hello, is it possible to write the he result of query to a unicode textfile instead of an ansi textfile. i need this because i want to generate udl files on the fly. any help appriciated. Gerben...more >>

When calling multi select statements in a stored procedure how do you set the namespace to each select statement? Thanks Chris ...more >>

How do you effectively mix OR and AND together? I have the query below for my SEARCH page. I would like the users to have the option of selecting one field to search with OR selecting pairs of fields together to search the database with. The problem is, with the SQL statement below, OR works (...more >>

I have a number: 3320.8000000. My expected result is 3320.80 based on a dynamic decimal parameter for example: declare idecimal int select @idecimal=3 convert(decimal(20,@idecimal),number) does not work - error message. How can I accomplish this? ...more >>

SQL 2K I have a table 'TripMovement' with columns CarID, TripType, TripDate, ....... (These three columns form the PRIMARY-KEY) Each trip will have an entry in table 'TripMovement' , there are four different 'TripType' (A, B, C and D) For a trip cycle, Trip will start with type 'A...more >>

With cdosysmail I have gotten my code to work to programatically send emails out as HTML. Does anyone know how to tweak the standard CDO example to send the query results as an attachment? thanks ...more >>

Hi. I have a sp that allows the user to search based on a variable set of = parameters for example just a home phone or just a buss. phone however = when searching by last name the user also has to supply the ZIP optionally he can filter that search by first name or address. I am = trying to ...more >>

I am getting the following error in my SQL. Column 'dbo.ClientWorkerStatus.WorkerTypeID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. My code is below. What I want to get is up to 1, 2 or 3 counts for one ClientID as t...more >>

Dear All, Got a question with stored procedure, is there any way with in a stored procedure to capture where or what is calling the stored procedure, an ASP page or another stored procedure? other than using sp_depends. Thanks for help. Raymond...more >>

Hi, I have a problem with index pages on a varchar column in my database. The varchar field is an Identifier which is not unique. The field length is 15 DataId varchar (15) I have an index on that column. When we are inserting data in the table, we do lot of transactions per second (~2000)...more >>

Hi, See the statement below. SET NOCOUNT ON SELECT TOP 1 [Name] FROM [Persons] Can I use "TOP 1" in combination with "SET NOCOUNT ON"? Is this statement faster with or without "TOP 1"? Thanks! ...more >>

Hi all. I'm very confuse. I was thinking that I understand Stored Procedure, Begin, Commit and RollBack Transaction, but I think that is false. I have a stored procedure that is called from a Visual Basic application. My stored procedure look like that. Create procedure [sp_Test] as...more >>

Hi All. Where I should use timestamp? Thanks. ...more >>

Hello, I seem to remember in previous versions of sql server, you could try to compile a procedure with missing tables, and it would fail. For example, I believe this used to fail on old versions: create procedure TestABC as select * from TestTableDoesNotExist Now (sql 2000) this gives...more >>

Hi I found the filestream attribute in Beta 1 new features list, but cannot find any reference to it in the CTP documentation. It allows direct access to word documents stored in a VarBinary(MAX) column. By direct access I mean a file path\name to the file as stored in the database using SQL ...more >>

I am building a software system for a client and SQL Server is my back end. I am hoping to take this system and commercially market it BUT alot of my logic is wrapped up in stored procecedures that I don't want my clients to touch, let alone even see just like my compiled VB code. Question...more >>

Hello all - I'm looking for some simple SQL statements to truncate text when we copy data from one field in a table to another field in the same table. The problem is this; the copy from field is 100 char in length and the copy to field is 15 char in length. We want to only copy the first 15...more >>

I have a stored proc that updates 2 fields on a table but only 1 of the 2 fields is getting updated. The pay table (PayInfo) should update either PayFirst or PaySecond based on the DAY of a date field. Can anyone see anything wrong? Thanks. CREATE PROCEDURE [dbo].[mc_updPayTotals] (@Prd...more >>

The goal is to have one SP that handles both scenarios. Scenario 1 is when there is a real number and scneario 2 is when there is no number to store.The column in the database is numeric. Anyone have suggestions on how to pass that variable to the SP such that this variable can be both a n...more >>

Hi I'm executing declare @ARCHIVOidx varchar(300) set @ARCHIVOidx = (Select top 1 MyFile from task) declare @sentencia varchar(300) set @sentencia = 'bulk insert a from ''' + @ARCHIVOidx+'''' + ' with (formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) ' exec (@sentencia) and ...more >>

Hi. I'm not familiar with using triggers, but that seems like the best tool for the job at hand. I have a table with phone number records, and I get updates from multiple sources. The phone numbers are often in one of two formats -- 000-000-0000 or 0000000000 -- and I need a consistent format....more >>

Hi all In a project i've been working on recently I came across a problem. I have a table with a varchar(7) column consisting of around 20million rows. This column needs to be searchable by substring - i.e a LIKE '%AB%' but this obviously is taking forever. The shortest search string i...more >>

Hello "precious" developers of Microsoft SQL Server 2000. Huge bugs are performed when: 1) sp4 has applied for my default instance (developer edition) 2) everytime I setup a new named instance (MSDE Last release) a letter from the registry key SQLPath (located under HKEY_LOCAL_MACHINE\S...more >>

I want to execute a number of sProcs from a table thru the below cursor. It only executes the first and stops without executing the second. Am I missing something blatently obvious? set nocount on declare @pCommand varchar(50) declare trigger_cursor cursor for select pCommand from MIS_R...more >>

I've been given some logic which i've been asked to but into code. Basically i've been given a table of records and based on certain values of each record I have been asked to either insert the row into another table or don't insert and skip unto the next record. i've been asked to produce som...more >>

I have a decimal 15.14567 and I want to tuncate to 2 decimal places without rounding. My expected result is 15.14. I have tried cast, convert, and string and all round to 15.15. How can I accomplish this in T-SQL?...more >>

getErrorInfo just produces 'Description NULL HelpFile NULL HelpId 0 comes up in the process of using sp_OAmethod, using OpenTextFile. The file is there, and as the script has already opened and processed around 80 files in the same folder, its not a coding issue?...more >>

Hi; I need some help with quering. I have three tables as follows. EmpIoyees -------------- EmpNo FirstName LastName EmployeeTraining ---------------------- TrainingId EmpNo TrainingCode TrainingDate Training ------------ TrainingId TrainingCode Description What I w...more >>

Ok, I have tables t1, t2, t3 and t4. I also have identical tables in a CopyOfFirstDataBase, except this one contains older data that needs to combine with the newer. It's like: DATABASE1 -------------> DATABASE2 [1995 through 2003] [2004 through Present] Both are struc...more >>

Hi Folks, I am having a problem with a proprietary application that is writing a transaction record into a MS SQL database. The transaction date is store as an integer i.e. “1097683526” , how can I convert this into a readable date and time stamp format. I have also developed my own ap...more >>

Hi - want to update records using a subquery view the value to update them too is also returned from the subquery. What goes at the @?? UPDATE tblRegistration SET Outcome=@?? WHERE RegistrationID IN (SELECT RegistrationID, Outcome FROM UnitsCompleted WHERE CourseID=@CourseID AND Compl...more >>

Hi, Is it possible to use BULK insert or any other faster way to insert data from a fixed length file ( file which does not have a delimeter as a column separator). The column are defined as fixed length. e.g. col1 - 10 char col2 - 5 char Thank you ...more >>

Hi all, Can i do have two different databases[onse server]. I need to copy the data from one table to another table[another database]. I dont want to recreate the table, i need to copy only the data how is it possible? Thanks in Advance thomson ...more >>

I didn't know what group to post this in... I'm looking for a good hex editor. One that would permit me to view two files at the same time and as I move the position in one it would also move it in the other. I'm trying to compare the position location of two files. ...more >>

Hi, My scenario is that i have 2 system with name and adress (100.000 names), that have to be merged into 1 system without any duplicates. The problem is that the spelling is not 100% between the system. One way to find duplicate is to group name,adress and count > 1. My dream is to us...more >>

Hi, i store dates in a smalldatetime field. Now i want to retrieves all records between 2 dates SELECT * FROM myTable WHERE addDate BETWEEN '2005/7/24' AND '2005/7/25' (suppose to retrieve all records added yesterday and today.) I get no error but get no results back ? What do i wrong...more >>

Hello! How can I drop an unnamed primary key with a query? Else how can I recover its name assigned by SQLServer? Thanks for your help ...more >>

I wanna know. I have 2 ways to copy data. way I INSERT into A SELECT * from B Way II DECLARE CURSOR ... For SELECT * from B WHILE ... BEGIN /* insert one by one to A END .... .... Which the best, way I or II? How much its speed comparison? ...more >>

My scenario is like follow: id number teamid 1 0 1 2 0 1 3 3 1 4 1 1 5 0 2 6 0 2 7 2 2 8 0 3 9 ...more >>

I've data like this noid fdate ftime fstatus --------------------------------------- 1 1/1/2005 1/1/2005 6:30:00 1 1 1/1/2005 1/1/2005 6:30:00 1 1 1/1/2005 1/1/2005 6:31:00 1 1 ...more >>

Hi, I would like to write IF or Case expression inside where condition lilke: DECLARE @Allow_All_Customers BIT SET @Allow_All_Customers = 0 -- This will be 0 or 1 depends on permission table settings. SELECT CustomerCode FROM tblCustomer WHERE (Normal Where Condition...) AND IF (...more >>

Hi, I am reading online transaction log using dbcc log command. But I don't want to read the whole online transaction log, because it = will take too long time. Is there any way through which I can filter the = record and get lesser no. of records. Is there any way through which I can use d...more >>

It my ddl table: CREATE TABLE [dbo].[TTEMP_BC] ( [RecID] [int] IDENTITY (1, 1) Primary Key, [FDATE] [smalldatetime] NULL , [FTIME] [smalldatetime] NULL , [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL , [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ) and...more >>