Hi all I need to write an SQL like Select distinct column1 from table1 where column1='Test' Group by Column1 Order by Column2 B'coz ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Is it possible using sub query we can sole this. I don't want to use ...more >>

Hello, in my table I have 2 columns: 1. "datadate" which has format "yyyy-mm-dd" and contains daily dates for 3 years. 2 . "data" - contains daily data. I need SQL statement to extract only end-month data like this: 2000-01-31 200 2000-02-28 100 2000-03-31 300...more >>

Fifteen years ago when databases were primarily used in the context of transaction-intensive applications, triggers were a nice feature of a DBMS system that did not get used because of performance concerns. Fast forward 15 years, it seems to me that 95% of database usage is for non-transacti...more >>

I have a trigger on SERVERB, so that everytimes something get updated/deleted/inserted on manager table, it will fire the trigger and delete same thing from manager table on SERVERA (Linked server) ---------------------------------------------------------- CREATE TRIGGER managers_tr_iud ON...more >>

I have following data. 1.2 Banks 3000 <----- need this 1.2.1 Local Banks 2000 1.2.2 City Banks 1000 1.3 Agents 2000 <----- need this 1.3.1 Local Agents 1000 1.3.2 City Agents 1000 I want to show the sum of childrens in their parent accounts. ...more >>

Hello all, we need to modify a stored procedure that does an insert: It needs to have conditional branching based on the value of a parameter being in a list. So it's something like this in pseudocode, if parmEmpType is in ('1','2','3') do insert with a flag set to 'S' else do insert...more >>

If I do in a select some math count I recevive strange result Image this table data ID PRICE 1 50.00 2 12.99 3 11.01 4 1.00 Well I need to return back a percentage value given from a division from totalprice and rowprice. In this manne if I do select (pr...more >>

Hello, I have written a stored procedure that attempts to use XP_SENDMAIL but I cannot seem to located this stored procedure. When I compile my stored procedure I get the following message: "Cannot add rows to sysdepends for the current stored procedure because it depends on the missing ob...more >>

Hello there Is there a way to hide system tables, views and stored procedures? ...more >>

Is there a way to add a default value to an existing column without having to create a default and then attaching it? I am trying to run the script below with little success: alter table t007_subscriber Alter column create_dt datetime DEFAULT getdate() Thanks! ...more >>

I have a varchar(15) field (Product_size) that has null/string values. I'm trying to use an index on the following query: SELECT COUNT(*) FROM Products WHERE Product_size IS NOT NULL I'm getting a Clustered Index Scan, although I've created an index to this column. I tried also WHERE P...more >>

Hi, Would you please tell me how to declare a variable as a column name and use it the a return value stored procedure? Thanks hb ...more >>

I have two servers running SQL 2000 (version 760). The collation property for both is COLLATE SQL_Latin1_General_CP1_CI_AS. Each server has a [database] with a [table], and the first has the [name] field as varchar, while the second has the [name] field as nvarchar. Both tables have a record whe...more >>

Hi I'm working on a proc that is big...some 600 lines long or so. I will be going back to split it up into several procs, in order to better organize it and optimize. I'm just working out the code and logic at this point. Part of the proc builds a temp table and uses a cursor to write out ...more >>

A very basic question ( I think....) I am new to SQL Server and DTS. I want to create a simple .xls(excel) file with a specific name using my DTS package How can I do it? Thanks. ...more >>

This function used when a trigger is fired is sometimes inconsistent with the len of the string returned by Columns_updated(). For example, I receive a length of 0 at times using the Len function as such Len(columns_updated ()). The length that should be returned is 1. Anyone that has so...more >>

Would you believe that sometimes the length of columns_udpated() is actualy 0 even if in actuality it is 1......more >>

Hi, I have a table with constraint on a column time_stamp, default value is getdate(). I'm trying to insert a row using store procedure, exec Proc1 @time_stamp=getdate() -------------------------------- Insertion fail, I got: Incorrect syntax near '('. How come I can't pass g...more >>

I keep getting the error; "Tempdb is full" How to I stop this from happening in the middle of huge transactions? I have a database that's approximately 3gb large. There is 1 main table containing all the records (in tact) and then 4 other tables that will contain sorted and manipulated ...more >>

Hi. I made a search on this newsgroup and answered my question about what are the dt_ stored procedures I have in my database. But could not find anything about deleting them on a production environment. My database will be regenerated on my customer's server. There will be no developers to u...more >>

What insert is missed in this Necessary to insert only some fields in the table Activities. select (cpu, schedule, job, state, pr, start, elapse, dependencies) into Atividades (cpu, schedule, job, state, pr, start, elapse, dependencies) from Sched_dia ...more >>

SQL Server 7 MQ SQL 3.2 Hi, I am trying to update a table on a MQ SQL lined server, the trouble is the MQ SQL driver does not seem to support the owner part, any suggestions? Or a link to some examples would be good. Thanks for any help in advance ...more >>

...more >>

Hello, I am trying to compile the following code but the syntax is incorrect when I try to assign a cursor to the results of the second SP (MissingSheets) call. Please Advise, Dan ALTER PROCEDURE NotifyDeliquentTimeSheets AS DECLARE @AssigneeID VARCHAR(15) DECLARE @MissingDate SMALLDA...more >>

Is it possible for a trigger to retrieve the NT Login User name of an = individual who deleted or updated a record through Enterprise Manager. Obviously it can be done via asp.net programmatically and you can pass = the user name to the sproc, but what about through Enterpriser Manager. = Is ...more >>

Hello, I would like to know the SQL synatx that run a query from a field of a table. Something like: EXEC (Select qry from tbl1), where the field qry is the actual query e.g. "INSERT INTO #MyTempTable VALUES ('abc') " Thanks in advance. Your help would be greatly appreicated. Wanda...more >>

Hi. I'm seeking opinions regarding a stored procedure coding practice that I keep running into. At a high level, here's what I'm dealing with: One stored procedure that contains a loop with calls to several other stored procedures. Rough example... create procedure A as while (s...more >>

Hi, I have two tables I'm using-- "folders" and "folderitems". Think of this like Windows Explorer, where you have folders and excel, word docs, or whatever contained within them. The two tables are linked on the "FolderID" key using a one-to-many relationship. What I'm tryi...more >>

i logged in as a memeber (not SA account) of sysadmin server role. But when i executed this query: exec sp_cycle_errorlog it returned: Server: Msg 15003, Level 16, State 1, Line 1 Only members of the sysadmin role can execute this stored procedure. but when i logged in as SA, it worked...more >>

I had an access query that used a select statement with an IIf function to evaluate some values and want to do the same type of thing but using a sql server query. This is what the access query looks like: SELECT Sum((IIf(DBO_REQUIREMENT.PART_ID Like "8500*",0.8*dbo_requirement.qty_per,II...more >>

Hello: I need to create a stored procedure to grant the SELECT permissions to all tables in the database. What would be the easiest way to do this? The basic syntax is: CREATE PROC uspGrant_Permissions AS GRANT SELECT ON dts_Imports.dbo.WA_INK TO "specialty\SQLUsers" GO But it work...more >>

Hello. I have to perform inserts in 7 different tables. I put all the code in one Stored procedure and marked it with begin transaction and at the end commit or rollback transaction. What happens is if it fails at table #4, it does not rollback inserts in other tables. Am I missing somethin...more >>

Thanks for taking the time to look over this question: What I would like to accomplish is to scan a MS ACCESS 2000 database to determine if a table has recently been updated. If this is true, then append the NEW data to the table stored in SQL Server. Does anyone have any suggestions an...more >>

All, I am currently working with a SQL server 6.5 database for my asp pages application. Is it possible to have a snapshot of the data similar to an Oracle snapshot. any suggestion welcome Jawahar ...more >>

I wish to be able to send message from trigger when ever table is modified. This should be some kind of windows message that my application can catch. The message should contain the tablename and maybe what has happened (update, insert, delete). A requirement is that the send of the message sh...more >>