If I do SELECT 2 I get back 2 How do select, say 2 to 10 ? Is it possible? thanks Stephen Howe ...more >>

When you do average of a column that includes null values, does it ignore the rows with null or does it not ? If it does not, how can we include it ...i mean if we have 5 rows with one null value, how can we avg with 4 and also average with 5.. Thanks ...more >>

I have a stored procedure that creates several temporary tables. When I call this procedure from the Query Analyzer, it works just fine. When I call the stored procedure through the DTS or from a query from the reporting services, I get the error: Invalid object name '#NSLP' #NSLP is the ...more >>

is this legal t-sql ? COALESCE(DateCreated,GETDATE()) DateCreated is a non-nullable DateTime column ...more >>

Our application on Windows XP SP1 with SQL Server 2000 SP3a takes 3.5hours to complete. If i change the SQL Server memory option from "Dynamically configure SQL Server Memory" to "Use a fixed memory size" the same process completes in less than an hour. This machine has 1GB RAM. The same p...more >>

I have Three Triggers for Ins, Upd, Del, How can I fetch Field values from Trigger table ? What I have to do is to Insert Inserted or Updated or Deleted row's Key value to another Table.. Thanks. ========= CREATE TRIGGER INSERTTRIGGER ON [dbo].[TBL_PRO] FOR INSERT AS INSERT INTO TBL_...more >>

Im trying to design a trigger that will: 1. Allow people to Update a column in our TsysQueue.ResponseDate column from a NULL value to a real value. 2. Allow people to Update a column in our TsysQueue.ResponseDate column from a real value to a NULL value but only 1 row at a time. --would be ...more >>

Hi, I have data in a dataset. One of the column in the table is of type Timestamp. How do i get the actual value of it. when i assign value like string x = row["rowtimestamp"]; it gives value as "System.Byte[]" it gives the same value if string x = row["rowtimestamp"].ToString(); Is there a...more >>

I have the need to read/export Binary data (Text BOG). Using SELECT @@TEXTSIZE, I found the size is 64512. What do I need to do next? Thanks Bill ...more >>

I know the SQL Server 2000 didn't support adSeek and adIndex, but I was wondering, is it possible to select an Index in SQL Server 2005 and Seek for specific key values? e.g. .... _RecordsetPtr rs("ADODB.Recordset"); rs->Open("Facil", vtConnectionString, adOpenDynamic, adLockOptimistic, a...more >>

I have the following statement: SET DATEFIRST 1; SELECT DATEDIFF(week,'20041202','20041205') It returns 1, but I expected it to return 0. I have set the week to start on a Monday, which means that the two dates (2nd Dec 2004 and 5th Dec 2004) should be on the same week, therefore returni...more >>

Hello, In MSSQL 2000: For my project, it would be great to have one centralized function or procedure return a result of a huge join, and have other functions/procedures process this result set further - summarize it, filter it, etc. Unfortunately this result set contains more than a 100...more >>

Hi How can I Pass an array into a MS SQL Server Stored Procedure from Visual Basic? Thanks Ian ...more >>

i'm getting this back from query analyzer: Server: Msg 156, Level 15, State 1, Procedure Totalnews, Line 15 Incorrect syntax near the keyword 'SET'. Server: Msg 156, Level 15, State 1, Procedure Totalnews, Line 37 Incorrect syntax near the keyword 'BEGIN'. for the following procedure: CREA...more >>

Hello, I need to allow a user to execute a BULK INSERT command, but I can not add them to the Bulk Admin server role. The import table determined at run-time. So, I have a procedure like so: create procedure dbo.importfile @file varchar(255), @table varchar(100) as EXEC "BULK INSERT ...more >>

I have to migrate a Japanese application from Oracle-Rdb to SQL2000 database. I use an Oracle driver to get the japanese strings. To be sure I read correctly the values from the source database, I display the strings in a VB textbox with MS Mincho (Japanese script) font and this is fine. Now I...more >>

Say I've got a database that stores the standard Customer, Invoice and InvoiceLineItem info in tables something like this (this isn't what it stores but getting into the specific details of my database would just confuse things) CREATE TABLE Customer(ID INT, Name VARCHAR(60)) CREATE TABLE ...more >>

ok...i am writing a program that makes a report...and then after that report is ran the user is asked if it should be accepted. If the user chooses to accept then a table is suppose to be updated. What would be the most appropriate method for updating the table through the program... what i...more >>

If i do a backup database,will the stored procedures also get backed up?...more >>

my trigger *should* create a row in a secondary table on any update of column(s) in a primary table ... however, no rows show up in the secondary table ... what is the best method to ascertain whether or not my trigger in fact is firing when i update the column in the primary table ? ...more >>

Hi, I have about 50 databases on a single server. They are all identical in structure. I have a long SQL script that contains DML that I need to apply to each database. I am looking for a simpler way to apply it to all databases, since I do this about weekly. I need to see the results o...more >>

Is there a tool/utility/script that will do a syntax check of all stored procedures in a database? After doing some table maintenance, I occasionally have the need to verify the associated stored procedures, and have resorted to the (very manual) process of opening a stored procedure and press...more >>

Has anyone measured the performance hit that a user-defined data type imposes as compared with a native data type? Specifically a Numeric(38,0) versus a UDT of a Numeric(38,0) but any comparisons are welcome information....more >>

I'm working with the integer values for next run date and time in sysjobschedules from msdb. Is there some easy way to convert those values to a datetime? -- Thanks, CGW...more >>

Thank you in advance for any and all help/advice. How do I execute/activate a stored procedure from an Access 2000 front end? Thank you very much, Ari ...more >>

Scenario: Central SQL Server database with multiple tables holding millions of rows each. Multiple clients, each having an arbitrary subset (say, 10%) of the server data each in a local MSDE database for offline use. The clients should be able to make modifications to the data and sync...more >>

Hi, After learning how to do the query statement shown below, of course with help from this newsgroup, I now need to modify the statement to return all the email addresses once as well. I tried but I could not get it to work. Query below returns results like: =============================...more >>

I have a change-history table that duplicates all the columns in a primary table. I have an AFTER UPDATE trigger that records before/after snapshots of the data-rows in the primary table. The primary table contains an Identity column. In the change-history table, I declare this column with t...more >>

How can i create a Store Procedure in Run Time with VB.NET? ...more >>

How do I set the primary key on a field in a table that is in ms sql thanks...more >>

Hello, I am currently learning to use SQL 2000 and like it's many features it has, however I have two questions to ask: 1. Can any one provide me with some information as to what types I should use for my data (names, addresses, numbers), maybe there is web page out there that can show m...more >>

I have a trigger: CREATE TRIGGER Ordr_Delete ON [dbo].[Ordr] FOR DELETE AS DECLARE @OrderID int SELECT @OrderID=OrderID FROM deleted DELETE FROM OrderDetail WHERE OrderDetail.OrderID=@OrderID If I delete a record from table Ordr directly in EM or run delete statement from VB6 application...more >>

workign with hierarchies. Here's the table. CREATE TABLE [dbo].[Tree] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [ParentID] [int] NULL , [Lineage] [varchar] (50) NULL , [Name] [varchar] (50) NOT NULL ) ON [PRIMARY] GO Here's som...more >>

Hi Freinds, SQL 2000 I am trying to Update text filed and getting an error create table tbl_a (col_a text, col_b nvarchar(10), col_c int) go update tbl_a set col_a = (select col_a from tbl_a where col_b = '989' and col_c = 9) where col_b = '991' go error : Server: Msg 279, Level 16...more >>

I am trying to build an insert statement that will take data from one table and dump it into another. I am all set except for one field called trans_id. It is an integer field that does not allow nulls, but is not an identity column. Primary key non-clustered for this field is what I get when I ...more >>

How would you check if a field in a table exists? ...more >>

I was told that unless Profiler is running when a deadlock occurs, you cant tell which 2 procs are involved. What Im confused about though is that Im looking through my TLog and I see about 25 messages that I beleive (could be wrong on this one) are related to the Deadlock I just had. 2 of them g...more >>

Hi Freinds, SQL2000 I know this is a redundent question, but I was not able to find my answer. How can I update a text or ntext field from query analyser. I have a text about 3000 chr that needs to be update on a field. Thanks in advance, Pat ...more >>

I created a DTS package to backup my online SQL objects down to my local SQL. There's one table [ORD] online that has an added user "bob". I am the owner. The package downloads a few objects properly, then stops with the error "no such login bob". I assume the DTS got to table [ORD] and stoppe...more >>

I'm still new to SQL programming and have been tasked with writing a procedure that tests and forecasts server load for the purpose of running procedures who's forecasted run time would fit in available chunks of server time/resources. Can anyone point me to an example of a procedure that d...more >>

How can I optimize this query.Can anybody help.can anybody plzzz convert subquery into left join. select sAccountManager AS Engineer, cFullName, cCompany.cKey, (SELECT Max(sBilling.sPO) FROM sBilling with (NoLock) WHERE sBilling.sKeyCompany=cCompany.cKey AND sBilling.sModule IN(''SourceHR...more >>

I have a field which is varchar. I need to find the values in the field that are all '1's. Meaning if the value is '1' or '111111' or '111111111111111111' I need to select the value. I haven't been able to track down any suggestions via the groups so thought I would post to the experts. Tha...more >>

Hi. Sorry for the newbie's question. I have a SQL 7 database, with some stored procedures coded in it. I'm looking at them with Enterprise Manager and only have the "Creation Date" for each one. How can I know when any of these stored procedures was last modified ? Thanks a lot in adva...more >>

Hi, Somebody knows a way to update multiple columns without typing each column? I'm cleaning up a database where i have one table that shouldn't have double records. The thing is, that those records should be merged into 1 record. I would like to do something where the value is null in one...more >>

I have a trigger that calls a console application with the below code. When I hard code the parameter to 99999.0, it works perfectly. When I try to use a variable coming from the INSERTED row, it fails and gives me an error (below). I know the @AN8 variable is correct because I have passed ...more >>

Would like to change an entire colum of alphanumeric charecter to all upper case. Currently it is mixed case. Would appreciate the assistance. Thanks....more >>

In my sql query I am finding orders that are still due, and on what weekday they are due (1,2,3,4,5,6,7). From that weekday number I would like to show the what the due date will be. So for weekday of 2, when the query is ran today (12/3/2004) the due date is 12/6/2004 (monday, 2). Any id...more >>

Hi, Am using the GetDate() function in a sproc I have created. Works fine. But, I just want to record the current date, not the time as well. Any ideas?? -- Many thanks in advance for any assistance...more >>

Hi, I'd like now if can I get the statistic information like last access, delete and update, or how many accesses, deletes and updates on a table. Thanks, Solli...more >>

I need capture all tables or objects of all databases in SQL Server instance. How do this? I can do this with any View in master database? thanks Eder F. Dias...more >>

Hi NG, This would be my first post on a newsgroup so bare with me :) I'm working on an existing application which uses a database with tables which have identity columns as primarykeys. The webapplication needs new functionality which requires 4 tables to be copied from one database to a...more >>

Hi If you create a unique clusterd index , your table is recreated and the data is ordered according to you index. I've got a question about this, Let say I applied the index and my ID col (unique clustered index) looks like this now 1 2 3 4 5 So if I query the table it goes f...more >>

Hello there This could be beyond the realms of a query - I have not been able to find a way to do this and thought I would ask... I have an .asp application that uses a lot of stored procedures in SQL I have a new requirement whereby I want to retrieve records based upon status of money...more >>

I'm looking for a well organized book on Transact SQL and SQL Server 2000 Programming. I'm especially looking for one that gives a nice tabular, short summary of all the commands, functions, variables, etc. organized into categories like data types, operators, Data Definition Language, Data Man...more >>