I have the following table: direction bin net 1 s1 6 2 s2 3 1 s1 2 2 s1 4 1 s2 5 I want to produce a table like this: bin sumIn sumOut totalRemaining s1 8 4 4 s2 5 3 2 sumIn=sum(net) where direction=1 sumOut=sum(net) where direction=2 totalRemaining=sumIn-sumOut I...more >>

Hi, I have data as follows Student City Marks -------- ----- ----- StudentA CityA 75 StudentA CityA 80 StudentA CityA 81 StudentB CityB 79 StudentB CityB 75 StudentC CityC 89 StudentC CityC 82 StudentC CityC 78 StudentD CityD 78 Can sb pl suggest me how to get top Mark...more >>

I have data in table as follows, Num Category ProductName ---- -------- ----------- 100 A Product1 100 B Product1 101 A Product2 101 B Product2 101 A Product2 101 B Product2 102 A Product3 102 B Product3 102 C Product3 102 D Product3 Can sb pl help to what t-...more >>

I'm working on a system that is very address-centric and detection of duplicate addresses is very important. As a result we have broken addresses down into many parts (DDL below, but I've left out some reference tables for conciseness), these being state, locality, street, street number, and add...more >>

Hi, I am using MSSQL 2k, and I have a linked server set up to an Oracle RDB version 7. It goes thru an OLE DB provider for ODBC drivers on a system DSN, which is using an Oracle RDB ODBC driver version 3.0.2. The problem occurs when I send a query that returns zero rows - query analyzer jus...more >>

If I join Table1 to Table2 with a WHERE condition, is it the same if I would join Table2 to Table1 considering that the size of the tables are different. Let's assume Table2 is much bigger than Table1. I've never used MERGE, HASH JOINs etc, do any of these help in this scenario? Thank ...more >>

I need to store payroll information (pay rates) in my data warehouse. I've search on field level encryption and most the stuff i find is for passwords, I.E. not reversable. I need to protect the payrate field from dba's and sa's, but I need to allow the authorized users to see the contents. Any...more >>

i want to implement authorization with windows authentication and don't have the slightest clue of how to do this implementation. the basic windows authentication for this .NET application is already setup. my problem lies within my inability to manipulate the username captured in the authenti...more >>

Hi all, This is my first batch file and I want to query a database and output it in a textfile. Up to now that works, the only problem is the formatting in the text file. It's all screewed up...lines aren't lined up and columns aren't right....how do you format the result from a query to a te...more >>

Hiya everyone, I have two tables in SQL 2000. I would like to append the contents of TableA to TableB. Table A has around 1.1 Million Records. Table B has around 1 Million Reocords. Basically TableA has all of the data held in TableB plus 100,000 additional records. I would only like to ...more >>

All, I have a perplexing problem that I hope someone can help me with. I have the following table struct: Permission ----------------- PermissionId Permission Description UserPermission ----------------- PermissionId UserId Active I am attempting to retrieve all records from t...more >>

Hello, I have been having a bit of trouble finding help on the safest way to move data files to a different disk on the same server. Most help is about moving data files to a different sqlserver. I just want to move the files to a different drive on the same server. Any help would be apprecia...more >>

I've been wrestling with some questions about databases. 1. I allow my databases to grow 10% and I find that I will have tons of excess space according to EM database properties. I try to shrink but it doesn't. I try a full databack up and transaction backup, but the shrink still doens't help. (...more >>

I've been tasked with designing a database to store collective agreements. I need to store each sub-article of each article of each agreement in it's own row within a table. I'm having difficulty figuring out the best way to store each sub-article's identifier so they sort properly. The maj...more >>

Hi all, I have an existing application that checks a few things before it launches another application. One thing that the application needs to check is the subscription properties of both the local database and the remote (LAN) server. I've stumbled upon some properties but I am trying ...more >>

Hi everyone, I have a database (not designed by me) in sql server, I am having trouble ordering it correctly, and I would appreciate any help. Here is my structure, it's sort of hierarchical: TABLE_A ID_COL SUB_ID_COL TABLE_B ID_COL' NAME This could be thought of as an employee ...more >>

I try to accomplish the following: I have two tables which are connected via a third table (N:N relationship): Table 1 "Locations" LocationID (Primary Key) Table 2 "Specialists" SpecialistID (Primary Key) Name (varchar) Table 3 "SpecialistLocations" SpecialistID (Foreign Key) Locat...more >>

I feel like I'm missing something obvious here, but I'm stumped... I have a stored procedure with code that looks like: INSERT INTO MyTableA ( ...fields... ) VALUES (...values...) IF (@@ERROR <> 0) BEGIN ROLLBACK TRANSACTION; RAISERROR('An error occurred in the stored pro...more >>

I created a system DSN for an Excel file and I have been using it for a while. I can read from it as well as update it. (The Excel file includes weekly updates to some records.) Then I tried to create the same connection with an explicit connection string. It opens the file fine, I can read...more >>

I am trying to query my SQL 2000 SP3 database for some info but can't seem to figure out how to get what I want. I have a fairly simple situation.... the data I want is in two tables, one with a single row for every computer in my domain and another with multiple rows for each computer that sho...more >>

I need to select and mark 150 records at a time in a large table. What I'm trying to do is... Select top 150 xxxx into #temp from largeTable update largeTable set marked = 1 where xxxx in #temp This is very simplified, the real procedure is quite large. The Error I am getting is In...more >>

Hey all. Can anyone point me in the general direction of documentation which would tell me how to restore a DB via the command line instead of using enterprise manager? Thanks Matt ...more >>

Hi guys! I'm facing a problem and I can't quite figure it out! I have created a table on SQL Server 2000 to store call records. We get about 250,000 calls a day, and the most frequently used query retrieves all the calls records for the day and groups them by destination, so the query would r...more >>

Hi Group, I am facing a problem regarding locking. I have created a Stored Procedure in my Database. In this Stored Procedure Temprary Tables get created and after that values are inserted in these tables.But this Stored Procedure is called from Java Portal. When then Stored Procedure execute f...more >>

Hello We have the challenge to introduce for our database applications a dynmaic search for the end users. The end users are non-technical users. I am looking therefore for possibilities which would allow to build own query language with expressions according to the need and skills for the...more >>

I am having a problem with seaching my tables. On my asp.net page i have 3 text boxes. One for an ID and NAME and ADDRESS. I want to be able to search a table by using all or any combination of the 3 to search. But i cant get it right. i am using c# and sql server. any help ideas would be a gr...more >>

We need to store land title information about properties in various Australian states, but each state maintains it's own land title registry and use different columns (well actually different combinations of the same columns). For example: Victoria store: TorrensUnit TorrensVolume Torrens...more >>

We're experiencing a large number of deadlocks since we began running SQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyper threading intel processors. We don't have the same problem on Dell 6650's w/o the hyper threading. If I turn off the parallel query processing option the deadl...more >>

I have an area on our website where users can customize what data fields they want to see displayed from a list of about 50 different fields. I have a query that has about 50 subqueries to pull all this info and it has just got to be extemely slow...sometimes even times out. I need to pull alot...more >>

Dear All, Do anyone know where I can find some useful documentation or any documentation about restoring database to the point in time (using logs). SQL Server 2000 on Win 2000 Thanks for all, M&M ...more >>

I have a VB6 SP6 MDAC 2.8 application talking to SQL Server 2000. Once I've installed this application on my local machine I have been able to move the ..exe file to a file server and it runs just fine from there for all my local users. This is very handy for updating the application without ha...more >>

Hi for all on this... I'm using MS SQL Server 7.0 SP4 in some customers to store some data from an aplication developed by me. I created an trigger to run on update. When I run an update command on it's table and the where condition returns only one row, the trigger is executed ok, but if whe...more >>

Hi, I'm trying to insert multiple rows with the following statment and am getting a syntax error on line 3. The datatypes are varchars except for status which is numeric. insert S (S#, SNAME, STATUS, CITY) values ('S2', 'Jones', 10, 'Paris'), ('S3', 'Blake', 30, 'Paris'), ('S4', 'Clark', ...more >>

Hello, I have a VB Function that I'd like to run in SQL Server. Is that possible? Thanks! ...more >>

Hello, I'm creating an application that will rely on a central SQL Server database for its help file. I'm a beginner with SQL Server really, and I keep getting the following error; "Login failed for use 'whateverUser'. Reason: Not associated with a trusted SQL Server connection" I ca...more >>

I ran into a table that is used a lot. Well less than 100,000 records. Maybe not a lot of records but i believe this table is used often. The table has 26 fields, 9 indexes but no Primary Key at all! There are no table relationships defined in this database, no Natural keys, only Surrogate ke...more >>

Hi all, Here is my situation. I have an application that uses SQL 2000. The client has a program that uses MSDE. The client MUST have the MSDE version of the Database. What I have done is to create a SQL 2000 Database that is exactly like the MSDE version. What I wish to do, is at regularly...more >>

Hi All Some my SQL Server are experience high memory usage. 1. How can I detect which process which process cause the big memory usage and not released? 2. Which sql server components in this memory, and what are their usage distribution? Any help will be appreciated. Thanks Willie...more >>

Hi, One of our backup jobs failed this morning but nothing got written into win nt application event log. This way our MOM 2005 couldn't generate an alert on this event since mom inspetcs application event log for that purpose (event id 17055). I see backup errors about 10 days ago in the app...more >>

Importing DBase files I get to the screen titled "Select Source Table and Views" And none appear in the list. (I can get the list displayed about once in ten tries) From that screen anything causes a crash of the wizard and Enterprise Manager. This happens on my deveopment installation of Enterp...more >>

I need to download free compilers for Oracle and SQL to teach my self . Is there any minimum of windows or Solaris requirements ? ...more >>

Hi, I've been able to successfully configure SQL Server Agent to send notification emails to an appropriate Operator in the event of an alert being fired. This all works fine on one condition - that Outlook client is running on the server. If Outlook is not running the alerts still be fired...more >>

Is it possible to write a Stored Procedure that takes a string of search keywords as argument and returns the recordset? At the mo I am passing the WHERE String as argument. I got this technique from an Extreme Ultradev tutorial by Rick Curtis it looked quite ok: http://www.princeton.edu/~rc...more >>

I have a large table with approx 250000 rows in sql 2000. I need to return this from an asp page but the query to return this amount of data causes the asp to time out before the query completes. Is there any way in ado (or another way useable from asp / vb) that I can run the query and then f...more >>

Hi all, This is not an urgent query merely an interested question. But for what would I use a Cursor for? I have looked at the on-line help but I am still unclear why it for what reason it might be employed? Many thanks Sam ...more >>

hi, i have a sqlserver CE and a sqlserver database. The tables are exactly the same on both databases. the sqlserver CE database Content will be synchronized with the sqlserver database with insert orders.. is there a way to merge the two databases? this would be great cause different conten...more >>

This SQL query has been driving me nuts, I am sure there is a simple way of doing this but I am afraid it is eluding me at the moment. I have two tables: Table1 X Y ----------- ----------- 1 12 2 41 6 14 9 12 Table2 X Y --...more >>

Can someone please tell me if this bug with the xp_sendmail proc has been addressed with SP4 of SQL Server 2000. "BUG: XP_SENDMAIL Fails to Send Mail to Personal Distribution List" If not, what is the best alternative? I have tried using SQLAgentMail but still could not get my query to work....more >>

hi i was trying to print a word document from a stored prcedure using the following two methods 1) cd C:\&&"C:\Program Files\Microsoft Office\OFFICE11\winword.exe" "c:\temp.doc" /mfileprint "/mfileprint" - this macro contains the code to print the word document and exit the word , this c...more >>

I execute a simple query with "for xml auto". One of the fields in my table is "char" type and has a blank value. when the query gets to this field the xml construction stops. such that it creates the following , where "core" is the field of type "char". What is the problem <tablename company...more >>

I have a table with one column, for example: 1 2 3 4 5 I need to make a query which return a table with two column, for example 'a' and 'b', and column 'a' contains all rows from the table except the last, and column 'b' contains all rows from the table except the first. How can I do i...more >>

Hi all, I need to put multiple results in one field, but not sure how. Here's some sample code: select a.name, a.accountnum, a.ordernum, (select itemid from items where items_ordernum = a.ordernum) as Items >From clients a There's multiple instances of 'itemid'...more >>

Hi My colleague has set up MSDE on his machine. I am only able to connect to his machine via IP and not servername Why is this? I want to use ACCESS as a fornt end and can't useing IP address Jerry ...more >>

Hi everyone, I have 5 servers, all with identical databases just different data. I have a rather lengthy SQL statement (in a View) to hit one database and pull-in certain data, but I'd like to somehow run this same SQL statement within the view but hit all 5 servers so we don't have 5 differ...more >>

Hello ! How to save on SQL server a date in this format? dd-mmm-yyyy Also, I have a web form in ASP.net and I want the users to enter dates only in the above format. I am going to need to make calculations beased on the above. Many Thanks, Marios Koumides ...more >>

Quick question about deleting data from SQL Server. We have a table that gets quite a bit of activity with an attribute of type text (inserts that store new text entries of 50-200k apiece). Older rows aren't needed so we have a process that deletes rows more than 30 days old (using delete stat...more >>

Gentlemen, I am using the following query to get a list of grouped checksum data. SELECT CAST(Field0_datetime AS INT), CHECKSUM_AGG(BINARY_CHECKSUM(Field1_bigint, Field2_datetime, Field3_datetime, Field4_bigint, Field5_bigint, CAST(Field6_float Decimal(38,6)), Field7_datetime)) FROM T...more >>

Hello, We are having a very strange problem. We have a table with about 5 million rows in it. The problem is with one of the non clustered indexes. I have noticed that sometimes in query analyzer, when doing an execution plan, the optimizer is NOT doing an index seek, or a bookmark lookup ...more >>

Hello Experts I am new in Sql triggers and i would like some help I have a Table called ITEM and another callded SOURCE [ITEM] ItemNo,ItemName,SourceNo [Source] SourceNo,Source i want to create a Trigger so when i delete a record from [SOURCE] all the records in [ITEM] where item.Sourc...more >>

I've got a view that creates a parent child relationship, this view is used in Analysis Services to create a dimension in a datastore. This query tends to deadlock after about 10 days of running smoothly. Only way to fix it is to reboot the box, I can recycle the services for a quick fix but t...more >>

Is there a stored procedure or extended stored proc which returns the table row size defined for a table ? Thanks, Rob ...more >>

Hi All, I have been struggling with this problem for quite some time. Basically i have an asp page (on the ISP server) that I want to connect to a sql server on another machine (on my server) and I have been trying to figure out how to do this. I tried to connect using an ip address in the da...more >>

Dear netters, We are looking to build a SQL Server database that will be hooked up to a Web server (WebLogic or a .NET). The database will house data for multiple customers, and the requirement is to have no customer see other customer data. Web server will be responsible for authenticating...more >>

Situation: Day 1 Table contains 100 items of actions imported via FTP. One of the fields in the table can be updated to reflect an assigned unit code. Day 2 Actions that may be duplicates of the table from Day 1 (with the exception of the updated assigned unit field) are imported for incl...more >>

I have two sql servers, I have defined each one as a linked server to the other. I can mostly access the servers from one another, but I get the following error on a sql insert. Insert statement... INSERT INTO [U1STSV02].[Custom Log Shipping].dbo.ls_secondary_files (database_name, tl_fi...more >>

Is there a way to query a SQL server such that it will return the current counter value of a field that increments automatically (an identity field)? Using a MAX query might not necessarily give you the correct information because it is possible that the high value records were deleted. I chec...more >>

I have 2 users that their client software must be going crazy.. they are sending packets every .02 seconds to the db server... I know this because I stuck a sniffer on teh traffic.. but now i just need to know what user is doing this (all traffic is encrypted.. so i couldn’t sniff out that.. i c...more >>

I'm writing an ASP page for a project and it requires multiple queries. However, I'm trying to combine multiple SELECT statements, but can't figure out a way that actually works. Basically, here are the SELECT statements I want to combine: SELECT * FROM schoolrequest WHERE SLid=10 SELECT ...more >>

I would like to convert a couple informix stored procedures to SQL Server stored procedures. I have no idea how to accomplish this. Here is an example of one of the procedures I need to convert. drop function mnaf_calc_calendar_quarter; CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Dat...more >>

Given the following table information: HOSTNAME DATETIME WEBNYC001 2005-06-15 10:30AM WEBNYC001 2005-06-15 10:31AM WEBNYC001 2005-06-15 10:31AM WEBNYC001 2005-06-15 10:34AM WEBNYC001 2005-06-15 10:35AM WEBNYC001 2005-06-15 10:35AM WEBNYC002 2005-06-15 10:30AM WEBNYC002 ...more >>

Task: Create 100 or so stored procedure scripts similar to the convention of Generating Script from EM automatically. I thought of essentially two things of a) using sp_helptext to get the content of a sp; and b) using bcp to write such content to a (dynamic) file. What bugs me is really the ...more >>

How is it possible to avoid truncation errors in MS SQL? For example, if I run the following declare @a as decimal(38,8) declare @b as decimal(38,8) declare @c as decimal(38,8) set @a = 30.0 set @b = 350.0 set @c = @a/@b select @c set @c = @c*@b select @c I get 29.99990000 instead o...more >>

Hello, I am trying to update records in my database from excel data using vba editor within excel. In order to launch a query, I use SQL langage in ADO as follwing: ------------------------------------------------------------ Dim adoConn As ADODB.Connection Dim adoRs As ADODB.Recordset ...more >>

Hi, I am currently creating an ASP page that returns a recordset of search result based on multiple keywords. The where string is dynamically built on the server page and that part work quite well. However I want to also return the number of records in the recordset and I can not manage to ge...more >>

Hi ! I don't know how can i see the serial number in an SQL Server already installed. I've tried wiht a Select Version but it don't work. If anybody can help me .. Thanks in advanced JLuis ...more >>

SQL gurus... I have one table of items that is the master file. We will call this Table A. I have another table that contains few, some or all items from Table A. We will call this Table B. I want to run a query that compares the list of items on Table B against the master list on Tab...more >>

Hi, It gives me great pleasure to announce the availability of the Beta Release of the Alpha/VMS version of TIER3 V3.0 (Incorporating the MTS/DTC <-> hotTIP/DECdtm Transaction Management gateway. If you are willing to assist, in anyway, with the testing/evaluation of this product then please l...more >>