Hi, I want to remove a field from a table by using 'ALTER TABLE tblTest DROP [TST_Available]'. I got an error message returned that there is a constraint on the field (the field has a default value). How do I programmaticaly check for constraints on a specific field and delete them? ...more >>

Hi, i have 2 procedure 1. CREATE PROC TD_Get AS BEGIN DECLARE @TD_Max INT, @TD_New INT SET @TD_Max = ( SELECT MAX(OrdNo) FROM Ord ) SET @TD_New = @TD_Max +1 SELECT @TD_New END GO 2. CREATE PROC TD_NewOrd AS BEGIN INSERT INTO Ord (OrdNo) EXEC TD_Get ??????? S...more >>

Hi guys , can any one help me?, thanks in advanced for your help. I use the following to do some operations and to create the body of an email message, but I'm getting this error: Server: Msg 8115, Level 16, State 2, Line 446 Arithmetic overflow error converting expression to data type int...more >>

Hi, I have created a stored procedure that retrieves data based on customer details. The customer name is used for the input. When the customer name is inputted instead of selecting the single customer all of them are returned. The same select statement in sequel is fine. Its when it is run...more >>

I am trying to write a single sql statement so that I don't have to use cursors or other looping to get the table CustColors filled in with the missing combinations of Custs & Colors. Table 1: Custs (CustID INT, CustName VARCHAR(20)) ID = 1 CustName = 'Fred' ID = 2 CustName = 'George' ...more >>

Cannot sort a row of size 8107, which is greater than the allowable maximum of 8094.? why this error occur? can someone explain? how to avoid this? thanks!!...more >>

I see the following in some code and I'm trying to understand it. create proc someproc as begin transaction mytran select * from sometable with (tablockx, serializable) update sometable set somecolumn = 'abc' commit go My question: is it redundant to ask for both the tablockx and ask fo...more >>

Greetings, I am trying to create an RSS2 feed using classic .ASP from my data stored in SQL 2000 (for a Google base feed). Everything works great, be every so often I get an invalid character (examples: =F6, =A3, =B1) that crashes my feed. I'm new to RSS (and XML), but my understanding is...more >>

I have a table that looks like following COl1 Col2 ------------------ A 1 B 2 C 3 I want to transpose rows and columns and want the result set to look like A B C 1 2 3 How do I write the query to get this...more >>

I'm racking my brain and I cant for the life of me figure this out. It's probably simple for you SQL Gurus, so could you please take a look and help? I have a Team Schedule database with 2 tables. The 'Schedule' table looks like this: ID, GameDate, VisitorID, HomeID My 'Team' table looks l...more >>

I have a table used to monitor Fuel Readings from multiple tanks. Table has TankId, Reading Date, Reading Quantity. Each tank is read once a week. I need to pull both the first and last reading from a tank between 1 Oct 2006 and 30 sep 2005. I have written the following that gets me the correc...more >>

Hello: I am using SQL Server 2000 and have encountered a situation where an array of GUID values needs to be passed to a stored procedure. I considered passing the values using a comma delimited string, but since this solution won't work for GUID's, I was wondering if any other options e...more >>

hi there, i tried a sql statement like this: "SELECT * FROM Products WHERE description like 'cup%' but it seemed to return me an error. is the LIKE parameter supported in SQLCE2.0? thanks, Paul ...more >>

I tried Searching Google but haven't seen anything like it yet. I'm having a Problem with CHARINDEX getting the correct count when Parsing Unicode strings. It seems to Only happen when there is a Upper range Unicode Character right before the Delimiter. In the Below example, the First Result...more >>

When I executed DBCC Memory Status following are the out put Buffer Distribution Stolen 187191 Free 1026 Procedures 6698 Inram 0 Dirty 154661 Kept 0 I/O 0 Latched 772 Other 436084 Buffer Counts Committed 786432 Target 786432 Hashed 591517 Internal Reservation 510 Extern...more >>

I have three tables filelog id filename eventlog id eventdescr eventdatetime errorlog id errordescr errordatetime Id connects all three tables. I need to produce id filename type descr datetime where descr and datetime might come from either the e...more >>

We have had some table structure changes to our database and sp_updatestats had been run but our queries are still running much slower. I read about DBCC FLUSHPROCINDB which someone said recompiles the stored procedures in one databse. Then another site said never use on a production machi...more >>

I have three tables filelog id filename eventlog id eventdescr eventdatetime errorlog id errordescr errordatetime I need to produce id filename type descr datetime I expect filename to repeat. type will either be event or error descr and datetime will come...more >>

Here's a question I can't seem to find an answer to (maybe I haven't looked hard enough?) Anyway, can a SQLCLR UDA return a table-style result? I have in mind the statistical Mode, which can return multiple results; i.e., the Mode for the set of numbers { 1, 2, 3, 3, 4 } would be 3, but the ...more >>

Hello, I try to eliminate a table for temporary results by using a table variable on SQL Server 2000 SP4. BOL basically says just replace the names... The following part works fine with with the database table tq2: update tq2 set Rank = ( select count(*)+1 from tq2 as a where tq2.Drel <...more >>

Hello, Beginner having trouble. The following is for example purposes. .. I am trying to return a distinct top N ordered by the rowid descending. Thank you for your time. CREATE TABLE [dbo].[tblJunk] ( [rowid] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [junk] [nvarchar] (50) ) inser...more >>

Hello, I would like to know if it is possible to create a view from the following 2 tables where each client row has the corresponding group codes (CLIENT_GROUP_CODE) for group id's 1,2 and 3. The objective is to have one row for each client and I don't think I can achieve this with a join s...more >>

Hi, I'm using VB6 and I want to know which Column in the recordset is the Identity Column (if any). I've a SQL Server 2000 table. It has 5 colums, the first one is the Identity Column. Now I want to recognize this column in VB6 using ADO 2.8. I used the recordset fields collection, but ...more >>

I'm trying to export some serial number records from one table, and convert them into a different format, prior to importing into an Oracle DB. The old data is in a single table, but since rows are logically related to each other, the database design is 'sub-optimal' shall we say. Apart from ...more >>

How can you (if possible) compare dates in a date range but exlude weekends. I need to exclude weekends because the amount of days and amount processed in that time frame counts, therefore we are excluding the weekends because there is no production time those days. Example: tbl1 : dockd...more >>

(I'm a SQL beginner) I have a table with fields containing parameters that need their range checked prior to accepting inserts or updates based on another table: Table: JobParameters JobParameterName JobParameterValue ---------------------------------------------------------- length ...more >>

I have a column for a master table of airplane flights. I have station ID, Flight #, ScheduledTime, ..... I need to use this as a sorting condition, 1:52, 12:03 What is the best way for setting this up as a "Time" only? TIA __Stephen ...more >>

I want to run a nightly Transact-SQL that will set a field where the date column is less than the current date. I want to only compare dates of course and not time. some date(yyyy-mm-dd) < current date(yyyy-mm-dd) UPDATE WorkSched SET Status='Finished' WHERE WorkDate < GetDate() How c...more >>

Hey all I am creating a new table for tracking popular search queries. At the time being I have one table called queries, and am about to create a new one called queries_popular. I'm a bit of a newbie when it comes to database modeling so I'm not sure if I should use a foreign primary key (qu...more >>

Hello, I have a DTS package that needs to be run at irregular intervals. What I want to do is create a stored procedure to execute the DTS package that I can then call from a simple .NET console application. However, I can't find the code to execute a DTS from TSQL. I've looked in BOL and c...more >>

Hi all, Using "Select into" to make a backup copy of a table. There are two indexes on it. Why does one index get dropped on the new backup copy of the table? thanks gv ...more >>

Hi! I did something to the Query Analyzer, and now all of my numbers (including integers) are formatted #,###.00. That is, every number has a decimal point (after which all integers show '00') and every number at least 1000 has a comma four positions away. How did I do this? How can I und...more >>

Relatively new to Triggers. I cannot seem to figure out how to update a record. Let's call the table tbl1. There is a date field in this table (dateModified). Whenever a record is added or updated, I would like the trigger to update dateModified for this record to the current date\time. Tha...more >>

Fix your system clock, Leon! ...more >>

I'm trying to use CONVERT(datetime, ColName, 103) and I have 10/23/2006 12:15:48 PM But what I really need is 23/10/2006 12:15:48 PM i.e.: date format dd/mm/yyyy. But I can't figure out how. I was trying a lot with the third parameter, but I wasn't able to find a solution. Any ideas? Thanks in...more >>

I have the following SQL to delete duplicates in a table. The FollowupID is an identity column so I want to keep the highest one with the same RecordID. I tried to add an ORDER BY but that is not allowed. Can someone help? Thanks. DELETE FROM dbo.CustomerFollowups WHERE Exists (SELECT * ...more >>

Hi I have another question on getting Top 5 values. I have a table as shown below UserName Industry Vote1Total Vote2Total Vote3Total ------------------------------------------------------------------------------------------------- A1001 IT 15 ...more >>

I'm having some problems and I'm not sure if it's our table naming convention that's causing it (possible in all cases except #1) or it's a bug in SQL Server 2005... Issue #1: have you noticed that you can have an incorrect table name within your stored procedure code and it will execute/co...more >>

i have a counter in my table that contains the next sequence number for line items in my application (i know about identity columns.... i can't use it in this case). anyway, i want to write three stored procedures. one (a select procedure) allows a user to select the next line item counter t...more >>

Hi There, Is there any advantage in executing a script like the one below manually over setting proper file growth options in data file tab in SQL 2000. ALTER DATABASE MYDB MODIFY FILE ( NAME = mydata,size = 10) TIA -- Manoj Kumar...more >>

I am using SQL Server 2005 Sp1. I don't understand why HASHBYTES() only accepts Binary/Character data. Can someone please explain why the limitation is like it is for HASHBYTES() ??? I would have to assume that (datetime, int, bigint, varchar, etc.), are simply (n) number of bytes. --...more >>

I am using SQL Server 2000 CREATE PROCEDURE usp_Test ( @ClientID INT, @PricingOnly BIT ) AS SET NOCOUNT ON IF @PricingOnly = 0 -- Call this case #1 SELECT DISTINCT a.Vendor_ID, DBA FROM Vendors a INNER JOIN ClientCost b ON b.Poss_Vendor_ID = a.Vendor_ID ...more >>

Hello Folks: The great majority of our queries and stored procedures do not reference the tables with the dbo.table_name format. Due to some production issues, we're not facing a situation that is forcing us to redo our database by scripting it out, making a few modificationsm, and importing t...more >>

hi friends As per my knowledge dynamic sql can be execute using sp_executesql. Now my problem is i wanna write a function that takes A column name and A table name as argument then want to execute query SELECT <col> FROM <table> WHERE <condition> and that function returns column value for ...more >>

I have a function that calls out to an external COM object for decrypting data. The COM object has an Init() call that is required, and takes three parameters - the database name, a username, and a password. The way I see it, I have three options: 1) Take the username and password as input ...more >>

Platform: SQL Server 2005 Express; ASP (not .net) I have this problem: When I have more than one VARCHAR(MAX) field in a rowset, any query i return to my web-browser, only shows me the last VARCHAR(MAX) column. I can only see this field if I CAST the field as TEXT, or VARCHAR(8000). I've ...more >>

Greetings, I have 2 inline table functions, one function is called by the other function. The 'Base' function takes 8 input parameters. It compiles fine and executes fine when you hard code the 8 input parameters. The other function calls the 'Base' function 3 times in its body, passing...more >>

I have an encrypted SP (create WITH ENCRYPTION option). I'd like to know if a user sa can retrive the code connection on a DAC port connection using ADMIN:MYSERVER. And if is possible, in which system table is saved de definition of my encrypted stored proc? Thanks!...more >>

Hello, using SQl2KSP4, I'd to create a view based on a table, but also needs to alter some columns values when presented in the view. If there's some SQL gurus out there, I'd appreciated if you can shows me how to do a SELECT statement of the following without having to run a bunch of updates as...more >>

Hi Hello I am using sql server 2005. I have two tables as described below. Table1 UserID UserSales --------------------- 1 10 2 13 3 17 4 19 5 21 6 10 7 12 8...more >>

Hi I am unable to solve one of the puzzles in SQL Server 2005. I am having two tables A & B decribed as below. Table A Description: This table contains Companycode as a Unique Primary key which is autoincemental. This filed is also used as a Foreign key in Table B. There is a foreign...more >>

I have a query similar to the one below select count(*) from table where flag = 1 group by number having count(*) > 1 So basically I want to find how numbers have multiple records where the flag is set to 1. The problem that I am having is if there are no records that meet this condi...more >>

I have a table: ID DataField ------------------ 1 aaaa 1 bbbb 2 ccccc 3 dddd 3 eeee I want query that return me like this when I pass in an ID: ID DataField ----------------- 1 aaaa,bbbb 2 ccccc 3 dddd,eeee Thanks ...more >>

Hi all, I want to query SQL Server 2005 and show all the records that are edited today. The field R_UpdateDT is a datetime field. How can I select those records? In MS Access I could say 'WHERE DateValue(R_UpdateDT) = DateValue(Now())' but how do I do this in SQL Server 2005? Do I have...more >>

Hi all Can someone tell me out of query given below which one is better for faster execution. My main aim is to check whether any records exist or not. And there is always unique record with identity. my first query is :- IF exists ( select * from TESTwhere ID = @id ) print'valid' ...more >>

Hi, SQL Server 2000 question We all know SELECT * INTO ... and INSERT INTO ... EXEC xxxx structures. Is there a way of combining these so that you don't need to know the structure of the table being returned from the sp in order to gain access to the table? Cheers, James ...more >>

Hello everyone, Does anyone have a good method for testing sequential alpha ID's (other than writing a function to convert the letters to numbers in 2^24)? Example ID's: AAAA AAAB AAAE AAAK AAAZ AABA The SQL could should return 'AAAA', AAAB' and 'AAAZ', 'AABA'. --The numeric equival...more >>

Dear valued developers, I'm exploring around partitioning under ms sql server 2005 and have the following tables: country, state and city. The table city holds 42239 records and is having the zip code as a primary unique clustered key while the state code such as 'NY' is a foreign key refern...more >>

I have a column named "ID" and its type is "uniqueidentifier" in SQL Server 2005 Express. this column is the primary key and its default value is "newid()" in SQL Server. I have generated a DataSet in C# that has this column but its default value is DBNull what can I do to make the default va...more >>

It returns Server: Msg 208, Level 16, State 1, Line 48 Invalid object name 'dbo.fn_split'. What can be the possible problem ? CREATE FUNCTION fn_split(@STRING nvarchar(4000), @Delimiter char(1)) RETURNS @Results TABLE (Items nvarchar(4000)) AS BEGIN DECLARE @INDEX INT ...more >>

Is it by design? In SQL 2000, when I use query hints on OPTION cluase, it just works Merge join or Hash join. In SQL 2005, it looks like that both hints are working. --SQL2000 select lastName, charge_amt from c inner join member m on m.member_no = c.member_no join corporation co ...more >>