Hi Is there any way that we can change the data type of a column from 'image' type to 'ntext' via the ALTER TABLE command? Thanks...more >>

Hi! I am using Sql 7 SP3. I have noticed strange Query plan when using views. Here is the case: Table1 has Col1, Col2, Col3 .... etc. Col1 is Varchar column and has Clustered Index. When I created view V1 As select * from Table1 where Col1 like 'N%' and Then ran the statement Sele...more >>

I am designing a database. I am trying to create a structure. I have a product table, ingredient table, and a vendor table. Here is what I know: All Products can have many ingredients, any ingredient can have multiple vendors. Here is what I think I need: Product table, fields: Pr...more >>

Hi everyone, Being kind of new at SQL. I need some help with a script. I am currently working on converting stored procedures from a SYBASE environment to MSSQL 2k. I have hit a brick wall with the below query. I know that WHILE is the proper syntax to loop over the statement. But I'm con...more >>

Hi! I am using Sql server 7 and found strange behavior of Optimizer when using view. Here is the brief description of the case: Table1 has col1, col2, col3 ..... col1 has cluster index. If I created view V1 as select * from Table1 where col1 like 'x%' and run the query select * from V1 then ...more >>

Is it possible to do something like CREATE TRIGGER [testtrigger] ON * FOR DELETE AS .... or will I have to do a trigger for every table? Marc ...more >>

column 1 column2 column 3 column 4 1 current 1.0 yr1 1 previous 1.2 yr0 2 current 1.3 yr1 2 previous 2.0 ...more >>

I have a .Net app which calls a stored proc. Inside the stored proc, a cursor is created, opened and executed. However, none of this cursor activity is showing up in Profiler. I have all the Cursor events selected, with no filters in place. Am I doing something wrong? Thanks, J.R. Larg...more >>

We have a client with 2 connected SQL servers, development & production. I am having a problem using DTS to move objects between the 2. If I use the option to DTS tables and data, it all works fine - so connection is not the issue. However if I try to use the option to DTS objects, such as sto...more >>

Hi, I have to retrieve the record between 2 dates. Is it better, if we give date between 'somedate' and 'otherdate' OR date >= 'somedate' and date <= 'otherdate' Which is faster? Susanth. ...more >>

I need to change object ownership for all objects (sp's, tables, views, etc) from SGLuser to dbo for a sql 7 db. Can anyone give me an idea of the script involved (either using sp_changeobjectowner or other) ? Any help appreciated. TIA, ...more >>

SQL Server by default i suppose rounds values in a view. Maybe to 6 = digits? I am not sure. I am talking about rounding numbers that are a = calculated value. What I have been doing to get more decimals afer the decimal place is = something like this.... field1 * field2 * 1.000000000000...more >>

Beginner question: c1 c2 ---------------- 01/30/03 02/15/03 145 03/06/03 278 04/20/03 304 How may I find DATEDIFF on c1 and then find per day usage rate, 145 for 1/30/03 - 2/15/03, 278 for 02/15/03 - 03/06/03 etc. Thanks for your help....more >>

We've got about 15 databases (not including the archive copies, and training databases). The eventual intention is to have an archive database for every live system. At the present moment, we only have the one archive database - the reason it was created is because the main user table contains...more >>

Hi I really don't know how to go about this in a relational database. There is data thats downloaded from the net everyday. The data includes a opening price of stock. What I need in the database is to update the today's opening price with yesterday's closing price. eg Instrument, Date, ...more >>

We have just created 6 new instances of SQL Server 2000, each one to handle specific functions. The problem that I have got at the moment is this: We run a database which handles approximately 5000 records (as well as umpteen related-records) each day. In order to stop that database from b...more >>

I've got this SQL that scans one table(t1) for records containing (somewhere inside of a field) the fields of another table(t2) and then joining the records of a third table(t3, parent to t1) but only if the decimal column of t3 equals the SUM of the records joined in t2... It is a pretty...more >>

Could anybody please answer me on this query: SELECT * FROM DBName..TableName where CONVERT(DATETIME,(CONVERT(CHAR(8),((CONVERT (INT,CONVERT(CHAR(8),XyzDate,112))) - XyzDateset)))) = '2002/11/14' and convert(varchar,XyzDate) not like '20000214.%' It returns: Server: Msg 241, Level 16,...more >>

Is there a function that will turn a -50 into 50 and 50 into -50? I am familier with ABS, but ABS turns everthing to positive, I just want to revers the signs. Thanks...more >>

I know I'm going to sound like an idiot, but I have to ask anyway ... I am trying to use a system stored procedure (xp_sendmail) - I am trying to see if I have things configured properly for SQL Mail. When I go to execture the sp: exec xp_sendmail @recipients = 'a-carbo@tamu.edu', @...more >>

Hi all I run next declare cursor statement: declare c1 cursor for select date_modified, event_modified_cd, rec_id from physicalsecurity order by date_modified, event_modified_cd, rec_id for update of date_modified, event_modified_cd, rec_id .... and I am getting next error: Server:...more >>

Experts please advise. We are expecting around 100 clients to use our product on web. The data for each client will be around 20MB max each year. Is this a wise approach to keep all stored procedures in a common database and data in each client's database. Also is there a limit on number of dat...more >>

I have two tables TableA Name, DeptNum, SubDeptNum JDoe, 1001, 1004 TableB Number, Name 1001 Admin 1004 Admin Sec. I am using the following which works for one but to do both? select name, B.Name , A.DeptNum from TableA A , TableB B where A.DeptNum = B.Number ...more >>

In EnterpriseManager, Management Folder under CurrentActivities there is an option Process Info ... is there anyway of capturing that data (e.g in vb code) .Does SQL store it in a systemtable somewhere ...more >>

Hello I have a child table like this ID, Item ---------- 1 12 1 100 1 250 2 5 2 60 3 600 3 654 3 852 Now i want to collect all child values of one parent in one string, like for 1 = "12,100,250" I did that with cursor, but it is kinda slow. It is no pr...more >>

hi i have an sql statment that is run in excelwhich works fine but if i want to change the where criteria of the statment i have to go into the code and change it there what i would like to do is link the where criteria to a cell on one of the worksheets this is my attempt "WHERE(FORM_XD.TEAM...more >>

I have a stored proc in my database named "StateCode_Get". If I execute this via Query Analyzer, SQL Profiler treats it as a stored procedure call. If I call it via a .NET app our company has created, SQL Profiler treats it as a remote procedure call. Does anyone know why there is a difference?...more >>

I posted a question on this board on the 8th. Now I can't find it to see what respones there are. I have paged back to the 6th. Any ideas? Thanks....more >>

Are they any arguments that cannot be used inside a view, if there are what are they?...more >>

SQL Server Version - 7 Table - Pages Field - Content Hi, I need to update an ip address in an ntext field. I have looked at = UPDATEXT, the trouble is the ip address does not appear in the same = place within each row. The contents of the field were popuulated at = run-time. I need to s...more >>

Hello, I have the following cross tab query...I need to convert the EmployeeStatus from an int to a varchar datatype so I can describe the result more clearly rather than just 0,1,2. I would like the result to say "new hired" instead of 1. I tried to cast around the SUM function but I got a c...more >>

I have 3 integer columns in my table, which represents the date of entry: dayEntry monthEntry yearEntry How can I combine this 3 columns to get the real date and then use it in my query as condition: set @orderDate=CONVERT(CHAR(8), DATEADD(dd, -1*DATEPART(dw, GETDATE()) +1, GETDATE())...more >>

How can I get more informations about last error? I get the last error number with @@ERROR, but I don't know what is the source of error. For example when @@ERROR=547, I need to know ID of constraint that caused the error, so I can raise the custom error messages for each constraint (like in MS ...more >>

Hi, I have a stored procedure where in I process around 2 Million records in a table. During this process I do "Select Into", "Insert Into" operations on to temp tables. At the end of the Stored Proc execution the .ldf file of the database is inflating up to 15GB to 17 GB. I believe all my ope...more >>

Hi, We are having a query comprising of joins between 8 tables. The query fetches 30 fields. This query takes 55 seconds to complete executing. If the same query is re-written such that only 3 fields are fetched then it takes 15 seconds to complete. What are the possible problems that caus...more >>

Hello all. I have a rather large and rather active website that continues to grow. It is an ASP site that uses MS SQL Server. As it continues to grow, I am starting to have more and more problems with the site causing the following error: Microsoft OLE DB Provider for SQL Server ...more >>

I have a transaction with several steps that I need to accomplish. I'm trying to decide if I should manage that transaction from my vb.net program or put the whole thing in a single Stored Procedure. It involves an array of strings that would require numerous inserts into a table. After lookin...more >>

Simply wanted to say thanks in general to all the experienced people that post to this newsgroup. I am constantly amazed at the responsiveness of the group in general. I've regularly had posts answered in minutes. I've learned a great deal just from reading the various threads. If there is mod...more >>

hie, i have a statement in ms access and would like to transfer it to sql server. My original statement goes like this: Between #6/29/2003# And #7/5/2003# Currently, i am using this: CONVERT(DATETIME, '2003-06-29 00:00:00', 102) And CONVERT (DATETIME, '2003-07-05 00:00:00', 102) Can i j...more >>

I am putting this NOTE since 3 days. Many people are sharing their comments and experience. Your comments and experience are also welcome: And if I give a example to support the following note, there is package SOLOMON. It's Microsoft product. This software stores the data in two databses of ...more >>

Hi, I'm thinking this should be easy but I can't figure out a way to do this without using DTS, SPROC or trigger. I have a table that contains 365 records each one with an amount of meters produced for that day (a record for each day of the year). I would like to create a view that displays...more >>

hi Can anyone help me out !! how to list all the file names in a particular directory in Stored Procedure. I need to do this in a Stored Procedure. kindly provide me some solution.. hope to hear from u .. Thanks in advance .. regards John...more >>

Hi, I would have to make code in C++ that would check two database tables for differencies. The two tables should be identical. If there are any differencies then it would have to write the result to a text file. Is there any other way than bcp to write the results to that text file? Any help...more >>

I want to write a delete trigger to output to a log table but I want to include the text of the query i.e. DECLARE @spid INT SELECT @spid = @@spid DBCC INPUTBUFFER(@spid) Does anyone know a way to save the output of a DBCC command to a file/table/variable Thanks in advance !...more >>