I want to make a table for tracking the daily sales figures to 6 of the customers. I thought of two differnt ways 1. Table-> Company(CompanyCode,Companyname) Table-> Sales(CurrentDate,CompanyCode,Sales) 2. Table-> AllDetails(Date,Sales_Compnay1,Sales_Company2...Sales_Company6) Which ...more >>

I have thousands of rows where data is like the following: NAME ANIMAL SOUND DESC ACT EST ----- ------ ----- --------- --- ---- spot dogs 1d jump 0 0 spot dogs 1d bark 5 10 beauty horses 2c ...more >>

I have thousands of rows where data is like the following: NAME ANIMAL SOUND DESC ACT EST ----- ------ ----- --------- --- ---- spot dogs 1d jump 0 0 spot dogs 1d bark 5 10 beauty horses 2c whine 10 10 beauty horses 2c 5...more >>

I was working with my DB. I wrote some unit test it failed and I didn't clean up it correctly in the code. So usually in this case I just go to DB and clean it myself by hand and lean my lesson. But this time some records I just can't delete - it tells me that record doesn't exists because it ...more >>

I have the following trigger that seems to work when inserting 1 record. But when inserting multiple records, I'm getting the below error. Is there something wrong with my logic here? How do I get around this? thanks -------- error ------------ Error message is... Error: 16389 Descripti...more >>

Hello, I need a Master because this one seems like trouble... I am getting that error that says: Column 'ARM1.finvdate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I know what the error means, but I am stuck because the ...more >>

I'm trying to construct a SQL statement that will do the following, but have not been able to come up with a solution. I have the following tables (simplified): games ----- id away_team_id home_team_id players ------- id team_id player_stats ------------ player_id game_id...more >>

Scenario: The system needs to manage events by group. There is no hierachical concept in the events. Special events have different attributes that need to be treated differently. Note that the DeviceID has a many-to-one relationship with a LocationID (not in the model). Not seen in the model a...more >>

Hi We have recently been experiencing a performance problem that appears to involve COALESCE in a WHERE clause. It started to happen recently when one of the affected tables grew a bit. Given the following tables (each is about 200,000 rows and 10 or 12 columns) and view: CREATE TABLE Tab...more >>

Hi, I define a field as auto number field, usually how people deal with if data is growing near 2147483647? Thanks. ...more >>

Is there any way in a query to pass back a NULL if no data is found??? I have this query that is looking at our Customer table and some customers may not exist but in that case I want to pass back a NULL... My SQL looks like so... SELECT * FROM _CUSTOMER_ WHERE (NAME LIKE 'Alway%' AND F...more >>

Hi Is there any TSQL command return String value LIKE Input value : 365 Required output : Three Hundred Sixty Five Thanks Kalyan...more >>

I'm writing a utility app to check on the status of a distributed set of SQL Server machines. What I originally planned on was just creating a SqlConnection (conn) object for each machine I wanted to check, and then calling SqlConnection.Open() and checking for exceptions. That doesn't seem t...more >>

SETUP: I have the following 2 tables on SQL Server 2000: Items ItemKey bigint IDENTITY (1, 1) NOT NULL , FamilyKey bigint NOT NULL , ZName char (8) Name varchar (50) , Type varchar (20) , Description varchar (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT PK_Items ...more >>

At first glance, you'd think that SQL Server considers it a hexadecimal number. But if you substitute d for any hex letter other than 'e', it will return false. So IsNumeric doesn't consider hex numbers to be numeric. Now if the 'd' or 'e' happen anywhere exept in the middle, IsNumeric return...more >>

Hi, I need to modify the data in a column in CODE.stock_code. Some of the records are in the form of 250-10-12-9. Not all records are the same lengh, but I need to remove the -9 in those records. This newbie would appreciate your help in the sql that would do this. regards, g --...more >>

Hi, I have a problem with a table, that was not design for me, and now I can't figure out how to get some information out of it with one single stored procedure. The table stores the details for a purchase order. It stores everything on a single row: Purchase order number (1 column) ITEMS...more >>

Hi, I used database A: use A go (do something) After I worked with a database A, I want to drop it. I need to change to another database B by issue the coommands use B go drop databse A go I wonder if there is a command to "unuse" A so that I do not need use B to drop A. ...more >>

Hi, consider the following scenario: create table T1 (c1 char(5), c2 varchar(10), c3 varchar(15)) insert into T1 values ('abc', 'def' , 'ghi') select (case when (c1 is not null) then c1 else c2 end)+c3 from T1 According to the books, CASE returns the type with the highest preceden...more >>

Hi All, Trying simple select like select * from authors where au_id = '172-32-1176' sql is placing intent exclusive locks on page. It is correct. To my knowledge "select” statement either share lock or intent share lock. (run sql profiler for that spid & select event lock acquired & ...more >>

I have a simple case statement which changes the "ORDER BY" clause based on a parameter passed to a stored proc. 4 out of 5 cases work, but one fails with the following: Server: Msg 295, Level 16, State 3, Procedure pr_reportPartnersDBTest, Line 12 Syntax error converting character string to ...more >>

Is anyone aware of any good Data Dictionary tools (apps) that are available. My boss has ruled out using extended properties... TIA, Jordan...more >>

I am wondering if it is possible to reference a view as a subquery instead of typing the entire SELECT statement. I have a query with many subqueries that are the same, just used in both the FROM and WHERE clauses. If I create the subquery as a view, can I reference the view? WB ...more >>

Hi, Once I convert a field which was classified as float to nvarchar, the larger numbers (for ex: 9873366522) are shown in scientific notation. I dont want this to happen and want to display it as it is. When i enter new data, it remains as it is, but the problem is with historical data. Is there...more >>

This does not work: select * from (exec sp_lock) as ex I want to process sp_lock in code as a table. How can I do this? Sean ...more >>

How do you get it from the system? Thanks in advance, Koni....more >>

Suppose i have table col1 | col2 -----|----- aaa | 1 aaa | 2 aaa | 3 aaa | 4 i want to get col1 | col2 -----|----- aaa | 1 | 2 | 3 | 4 any ideas?? -- Message posted via http://www.sqlmonster.com...more >>

Hi, I have two servers, SV1 is a 24/7 OLTP server and SV2 is a backup server. A Full Backup of the db (prototype) was taken from SV1 was applied to SV2. One of the tables in the database has over 40 million rows in it. My task is to keep any rows no older than 18 months. SV1.Prototype.dbo.t...more >>

Is there a simple method within SQL to copy the contents of one field to another field in the same row? TGS...more >>

Well, this one got lost in the fog in my other post so I figured I'd just repost it alone. Bear with me. I'm just trying to fully understand all of this. 1. If you use an INSTEAD OF trigger to maintain uniqueness on a column, what's the point of also having a unique constraint? My understandin...more >>

This works, but i'd like to know how to re-write it so that i don't use a temp table. select h.stkhstDate, Sum(Case When h.stkhstRSBS = 'B' Then 1 End) as Buy, Sum(Case When h.stkhstRSBS = 'S' Then 1 End) as Sell, SUM(Case When h.stkhstRSXO = 'X' Then 1 End) as RSinX, SUM(Case W...more >>

Hi all, I am storing my monetary values in columns with the money data type. Naturally this stores with 4 digits of precision. My client app in ..Net reads this value and is formatting it to actually look like real money--2 digits. My question is this, should I store monetary values using ...more >>

I want to create a user defined table with Foreign Key constrained to uid of sysusers table. How can this constraint be enforced? I am using Server Authentication on SQL Server 2000 v8. Thank you for your assistance. ...more >>

I have Two tables. Products and ProductInfo. All the Products(productId) have entries in ProductInfo (FK productId) I want to find out products whose entries do not exist in ProductInfo Pls hlp...more >>

At Query Anlyser,from Master database, this works select * from test..tt but no this sp_help 'test..tt' test is dbname and tt is table name any reason? Madhivanan ...more >>

Hi, I have a table that has column data type varchar(1000). Some data in that column with the values like: ArgumentException - invalid argument of some sort passed to a method ArgumentNullException - used by methods that don't allow arguments to be null The first question that i have is...more >>

Hi, Is calulation more efficent in the SQL CODE or in a Calculated Column? If I'm correct the Calculated Column is done on the client SELECT query every time where as my INSERT TSQL code will only do it once on the INSERT? Thanks DECLARE @ACCDCC_Table TABLE (cnt INT NULL, ...more >>

Hi Guys, Can someone please tell me what is the max size of text that i can insert in SQL Column. I want to insert about 100000 characters or more. I am using XML files at this point however i need to do it in SQL. What is the datatype that i can use? Thanks Manny...more >>

Where can I find the informations about the Trigger tables Inserted, Updated and Deleted? They are not available in sysobjects. Are they created and deleted after trigger is fired? Madhivanan ...more >>

Hi, I have a stored procedure, which takes a parameter of type SqlDbType.SmallInt. I assign a string type value to this parameter. ExecuteNonQuery succeeds without throwing exception. Option Strict is on. Any suggestions? Thanks, kd...more >>

I have tried everything to get a total of one of the columns in this query including Rollup/Cube and compute sum. The problem is that compute sum cannot be used with select into and one cannot do a sum(count(distinct... Rollup and cube give hierarchies and combination totals which is not what...more >>

Most of the characters are stored in a Single byte but some Japanese Characters requires Two Bytes to store Characters. Is there anyway to store those characters in a Single byte? I am looking for the query or any other tool. Is this possible with SQL Server or any other programming Languages? ...more >>

i ve simplified the problem to avoid any complications i have a simple view select * from tbl_main with(no lock) to be sure i removed all triggers and index also i have a simple access query select * from the view so far so good when i open the query while being connected to our server (s...more >>

Hello, My problem is that I don't know how to refresh the view. Actually, what I want to programaticaly do is something equivalant to presing the "run" button in the Enterprise manager. A stored procedure that will do it, will also be good. But the only stored procedure that I thought ab...more >>

Hi, I'm fairly new to Sql Server, and often run aground on what to others is probably totally obvious, soplease forgive me if this is trivial. I have a list (of Theatres) some of which contain the town name, others dont. So I need a query / sp to append the town name to the end of the t...more >>

How can I do a select statement and make the field = '' when it is null ? This is because later I do a 'replace' which crashes where there are NULL fields. Help is appreciated. Aleks ...more >>

I have the following records: Date EmployeeId Project 20050503 12345 VERIZON 20050503 12345 CINGULAR 20050503 12345 SPRINT 20050503 24680 CINGULAR I need the resulting table to look like this: Date Em...more >>

Hi Guys This above error is suppose to be fixed in SP4 for SQL Server 2000, however, SP4 is not available yet! Does anyone know of a HOT FIX or work around? Many Thanks Luke ...more >>

http://www.aspfaq.com/show.asp?id=2516 Hi, I have hard time to understand this, can you please explain more detail? Why it can return: every good boy deserves fudge ?? Thanks... DECLARE @csv VARCHAR(255) SET @csv = 'every,good,boy,deserves,fudge' SELECT word = SUBSTRING (...more >>

The following is a simplified sample of a table with over 2 Million records and 15 fields: LINE VARIETY VOL RATE LSV 00299 100 (600) LSV 00308 75 (400) D&A 00299 50 (200) D&A 00308 40 (500) I need to insert records into this table based on the following calculation: LINE ...more >>

tbl.Products ProductID(PrimaryKey)(int) Description (nvarchar(500)) tbl.ProductImages ImageID(PrimaryKey)(int) ProductID(int) ImageName(nvarchar(50)) OrderID(int) Many images are allowed for each product. I need to select only one image for each product and that image must be the one ...more >>

Is there anyway in this stored procedure I can substitute the name of the column where I am seeking DISTINCT entries (QUOTEJOBSTATE) as a variable in some way? Otherwise I have to create a separate stored procedure for each distinct I am doing in this table? CREATE PROCEDURE owner.[sprocnam...more >>

Hello! Could somebody provide me a sample how I can create a SP which returns Count(*) from 3 different tables... Thank you! James ...more >>

hi i have a stored procedure for Delete with following code Even though i delete thru UI the delete procedure gets called but when i again open my UI i find the record that is being deleted. could u please eloborate especially what that ROWSTATUS | 0x890 mean and also probable reasons for...more >>

hi i am trying to understand how this query works. basically it just ranks each row. and why do we need this line? B.telecast_right_id <= A.telecast_right_id query SELECT (SELECT COUNT(*) FROM telecast_right B WHERE B.telecast_right_id <= A.telecast_right_id ) AS Rank, * FROM telecas...more >>

Hi What is exact use of using Instead of triggers can't i use after triggers and why only one instead of trigger for an update ,delete or insert thanks and regrds Madhavi ...more >>