Database Newsgroups: SQL Server database programming

SQLDMO - What name to use to connect to the default instance?

Mark Findlay - 24 Dec 2005 7:54 PM - 3 messages

I am using SQLDMO (C++) and trying to connect to the SQL Server default instance, but I don't know the name to use in the Connect() statement. I've tried: "local" "(local)" "" What should the "name" parameter be when attempting to connect to the ...

Deleting files

Roy Goldhammer - 24 Dec 2005 7:47 PM - 4 messages

Hello there On every week by Enterprise manager i: 1. Detatch the database 2. delete the log file 3. reattaching it again I would like to do this by code. Is there a way to do that? ...

Design Question

Jose - 24 Dec 2005 2:57 PM - 2 messages

Dear All, I have the following design issue which I am unsure how to solve. Here's the key information: I am writing a tool which will send emails/SMS to registered users. The registered user data will be stored in one database (we'll call it ...

Number of indexes can affect performance event in nearly read-only table?

M. Simioni - 24 Dec 2005 11:23 AM - 3 messages

I have a table with something like 4.000.000 rows, but only once per day about 200 rows are inserted by an application, running as a scheduled job. So few writes per day are performed, but many reads are executed. ...

Faster execution of stored procedure

MilanB - 24 Dec 2005 11:03 AM - 12 messages

Hello I ask which approach gives better speed results. To put next query in one stored procedure, or to split in two: //ONE IF(@FindType=1) /*Find Full word*/ BEGIN SELECT f_English FROM t_Dictionary WHERE f_NonEnglish = @parWord ...

Index for Join

Ed - 24 Dec 2005 2:56 AM - 3 messages

Hi, If there are two tables with 1 to many relationship. In order to speed up the join, what would be the major different if I put the Clustered or Non-Clustered index on ForeignKey (Many Side) and ...

Works in the QueryDesigner but not in real life

Daniel Manes - 24 Dec 2005 2:30 AM - 4 messages

I've been working on a fairly complex query and testing it in the QueryDesigner in Visual Studio. I was going under the premise that, "Gee, if it works in the QueryDesigner, it should work when I run it on ...

No Of Months - Function

Vishal - 24 Dec 2005 2:27 AM - 10 messages

Hi, This is what I want to do: User enters parameters StoreNo. Month(11), Year(2005), Open Months(8). Store Table : Store No. INT (IDENTITY) OpenDate (DATETIME) When the user calls a S Proc with the above parameters, I want to check ...

IIF Statement

Scott - 24 Dec 2005 12:22 AM - 10 messages

Below I'm trying to return the larger column. If the SUM of UnitsInStock is greater than the SUM of UnitsOnOrder, then return SUM(UnitsInStock) AS largerUnits and vice versa. This is in northwind, can someone help me correct my syntax? I wasn't sure ...

Julian to Gregorian and Gregorian to Julian

myob@beatles.com - 23 Dec 2005 10:01 PM - 3 messages

Are there SQL functions to convert back and forth between these two? TIA for the help. -- Posted with NewsLeecher v3.0 Beta 6 -- [link] ...

How to select combinations of table attributes correct

dmecir - 23 Dec 2005 9:23 PM - 3 messages

I have a table that associates name/value search items with documents: create table search_values ( id integer unsigned not null, document varchar(40) not null, name varchar(40) not null, value varchar(40) not null ...

Last Day of the Month

Vishal - 23 Dec 2005 7:41 PM - 7 messages

Hi, is there a way to find the last day of the month ? the input parameter is just the month. year will be the current year, start date will always be 1st of the month. I need to find the last day of the ...

export a query to Excel

jaylou - 23 Dec 2005 7:10 PM - 4 messages

Hi all, Is there a way to export the results of a query to an Excel file? I need to create a report in a Stored procedure and I want it to email an excel file to a group. I know how to create the email and attach the file, ...

DECLARE CURSOR intermittently failing

Gerard - 23 Dec 2005 6:08 PM - 26 messages

Hi, I have a trigger which contains a cursor such as the example below. DECLARE cCur CURSOR FOR select aFld from aTable where bFld = @aVar and cFld = bVar and dFld = @cVar this declare intermittently fails, on one occasion the declare ...

SQL server performance

culam - 23 Dec 2005 6:07 PM - 2 messages

I have SQL Server 2000 run on 2 processors with 2gig of RAM on Windows Server 2003. I run select and insert statement for about 1 million records thru a client php scripts and it runs very slow. I check the memory usage, ...

How to update price from excel file ?

msnews.microsoft.com - 23 Dec 2005 5:53 PM - 2 messages

I dont know which group will be appropriate for this question so I am posting to all the above groups. I am running sql2k. Our company changes the prices every quarter. Accounts department works on some figures and finally they set a new price each parts ...

Test Message

Reetu - 23 Dec 2005 5:34 PM - 1 message

This is a Test -- Reetu ------------------------------------------------------------------------ Posted via [link] ------------------------------------------------------------------------ ...

MS SQL vs Versant DB

George Adrian - 23 Dec 2005 4:54 PM - 3 messages

Is anyone familiar with Versant and how it stacks up against MS SQL as a development technology. I have a client that currently has an application developed on Versant and they want to investigate moving to MS SQL. Any help or direction is appreciated. ...

How do I pass a second parameter to user-defined aggregate (SQL 20

JediMaster - 23 Dec 2005 1:21 PM - 18 messages

I am implementing user-defined aggregates. One of which is the string concatenation (which I think everyone is doing). The problem I have with this is that you have to hard-code what your delimter is inside the aggregate code. ...

Custom number format

usamaalam - 23 Dec 2005 11:18 AM - 16 messages

Hello everybody, I have a float type field in my sql server table and I want to select the data in the format $XX,XXX.XX. Any ideas ?? Thanks. ...

Unable to get data in sql 2005 ok in sql 2000

den 2005 - 23 Dec 2005 10:37 AM - 7 messages

Hi everybody, happy Holidays to you all. I developed a windows application that connects to Sql server 2000, using windows authentication and it works, able to retrive and other data process. But when I used it on Sql Server 2005 ...

WHERE IN - Return NULL values

Redowl - 23 Dec 2005 10:19 AM - 11 messages

Hi, I am trying to return a resultset using the WHERE IN with a list of selection criteria, and would like to return a NULL where the value does not exist. Is this possible ? Grateful for any advice. Alex ...

how to solve deadlock issue

Deepu - 23 Dec 2005 10:18 AM - 4 messages

Hi, In our application we are getting a deadlock error â€œtransaction (process id) was deadlocked on (lock) resources with another process and has been chosen as the deadlock victimâ€ When we traced the queries in sql profiler we were able to find out two ...

general comment is required

kishor - 23 Dec 2005 9:55 AM - 6 messages

Hi, I am trying to implement master detail kind of functionality. table1 has primary key t1 and table2 has primary key t2. I have not placed any relationship on these tables. these tables are free tables. what will happen if I place foreign key will search sort become faster ? ...

Duplicating Database

4vinoth - 23 Dec 2005 9:28 AM - 5 messages

Hi all Please any body tell how to duplicate an database within the same server from vb program? ...

Happy Holidays to you all

Hassan - 23 Dec 2005 8:44 AM - 2 messages

Been fun posting on some of these SQL newsgroups and receiving timely responses that made my day. Thanks to all who responded and also thanks to all the ones that ask questions. I only wish i could read the newsgroup postings every day ...

how to get the db files size ?

joycsharp - 23 Dec 2005 8:20 AM - 2 messages

thanks ~ ...

Create Table

TAHA - 23 Dec 2005 5:43 AM - 3 messages

Hi All how can I Create Table (COPY STRUCTURE TO TableName Only ) From Another Table In The Same Database Thanks ...

INSERT ... NOLOCK

lara169 - 23 Dec 2005 5:13 AM - 9 messages

Hi, Our company policy for UPDATE / SELECT operation is as follows. For both of these operations we have to put NOLOCK hint. Nut i know that the 'SELECT' operation with 'NOLOCK' hint will return the uncommitted value. So my ...

newbie tsql syntax: update one table from another ...

Chris Smith - 23 Dec 2005 4:43 AM - 6 messages

I have two tables, t1 and t2 for lack of a more imaginative mood. t1 -> field1, field2, field3, field4 t2 -> fieldA, fieldB, fieldC, fieldD I want to set t1.field2=t2.fieldB where t1.field3=t2.fieldC I have tried and tried, but no luck. This has GOT to be a whole lot simpler ...

Finding user tables in database

Mana - 23 Dec 2005 4:12 AM - 4 messages

Hi, I am using SQL Server 2005.I need to find out all the user tables in a database. For this purpose I have written following query. NSBank is the name of the database. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ USE NSBank; select name, type_desc from sys.objects where type='u'; ...

restore data file only

Datasort - 23 Dec 2005 3:47 AM - 5 messages

I am trying to restore a data file and not the log file. Acutally, the log file will max out my drive space so i am trying to create a new db from a backup without the log file. Any thoughts? ...

Stored Procedure Advantages

David - 23 Dec 2005 1:04 AM - 5 messages

All Can anyone advise me the performance advantages of using Stored Procedures vs. a normal SQL statement. Thanks ...

Index Help

Ed - 23 Dec 2005 12:37 AM - 3 messages

Hi, Can any please explain to me the diff.? Use Northwind I created a Non-Clustered index on "LastName' and "FirstName" (Combined) Select * from customers where LastName = 'Hello' and FirstName = 'World' Select * from customers where FirstName = 'World' and LastName = 'Hello' ...

THE > SUPER-MONEY-SOURCE <

THE > SUPER-MONEY-SOURCE < - 23 Dec 2005 12:00 AM - 1 message

THE > SUPER-MONEY-SOURCE < Would store / would copy this text or side before the administrator could remove this letter. This special program is acquire copyrighting-protected, only with the participation you the copyrighting at this program. Prohibited utilization ...

THE > SUPER-MONEY-SOURCE <

THE > SUPER-MONEY-SOURCE < - 23 Dec 2005 12:00 AM - 1 message

THE > SUPER-MONEY-SOURCE < Would store / would copy this text or side before the administrator could remove this letter. This special program is acquire copyrighting-protected, only with the participation you the copyrighting at this program. Prohibited utilization ...

Is this possible?

Confused - 22 Dec 2005 9:45 PM - 8 messages

I have a stored procedure and I want to do something like: INSERT INTO @Table (Field1, Field2, Field3, ....FieldN) SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3 FROM @Table2 WHERE n is null SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6 ...

compare varchar column values case-sensitively?

n_o_s_p_a__m - 22 Dec 2005 9:36 PM - 4 messages

Is it possible to case-sensitively compare 2 varchar column values without changing the collation on the column to case-sensitive? For example, I have column called Note on a table called Operation, and I'd like to select distinct note, case-sensitively, without changing the ...

ORDER BY question

yodarules - 22 Dec 2005 9:20 PM - 11 messages

I'm having some issuses with ORDER BY with my query. I'm trying to run the same query in Oracle and SQL Server. select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", SUM(a.CA) ...

enumerating sql servers in sql server 2005

Brian Henry - 22 Dec 2005 8:50 PM - 7 messages

Is there anyway new in SQL Server 2005 to enumerate all the servers on the network without sqldmo? I'm working in .NET and want to get the list, im doing it now in sqldmo and it works fine, but would like to do it with ...

Non-Deterministic function in DPV

alw - 22 Dec 2005 8:49 PM - 2 messages

Hi! Using the new SQL 2005 functionality where the optimizer determines where the data is based on the check constraints defined on the tables, can you use a non-deterministic function such as getdate() in the check constraints as long as you have another mechanism to make certain the data ...

reorganize data files

Gordon Cowie - 22 Dec 2005 8:30 PM - 3 messages

Just added a new datafile to my database. How do I distribute all the data evenly across the two files now? I tried dbcc dbreindex on all tables, this results in 75%/25% I also tried creating 2 new files and emptying the main file dbcc ...

Help with query

ViLo - 22 Dec 2005 7:35 PM - 3 messages

Hi all, I have the following table: CREATE TABLE [dbo].[Company]( [CompanyID] [int] IDENTITY(1,1) NOT NULL, [CompanyName] [varchar](100) NOT NULL, [CompanyTypeID] [int] NOT NULL, ...

parameters.refresh bug

Jack Zhong - 22 Dec 2005 7:12 PM - 7 messages

It seems MS have not fixed the parameters.refresh bug yet.If I use this code, it would throw out an exception of 'AV in module SQLOLEDB.dll'. By the way, my SQL is 2000 + SP4 under Win2000 +SP4. Jack Zhong ...

Can we boost up the priority of a procedure

Pushkar - 22 Dec 2005 6:59 PM - 2 messages

Hi, I have a stored procedure which works fine under normal load. But under heavy load on SQL Server, my procedure is taking very long time to execute. Is there any way to increase the priority of my procedure so that it get proper time slot. ...

Dynamic table and column list

StvJston - 22 Dec 2005 6:15 PM - 5 messages

Thanks in advance (even CELKO) DDL attached I DID NOT create this application. I am trying to fix it. The application recieves a Access database table once a month. This table contains employees and multiple columns detailing begining and ending balances, pay amounts ...

DBTYPE_DBTIMESTAMP OLE DB question

dermite - 22 Dec 2005 5:42 PM - 4 messages

Folks, I am trying to use ole db to read a date field created as a DATETIME in C#. It only seems to work if I set the binding type (wType) to DBTYPE_DBTIMESTAMP. When I do this, 16 bytes of data are written to my ...

SET XACT_ABORT: what the f***k

kevin - 22 Dec 2005 5:38 PM - 2 messages

I got this error, for the first time today: System.Data.SqlClient.SqlException: Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A nested transaction was required because the XACT_ABORT option was set to OFF. [OLE/DB provider returned message: Cannot start more transactions on this ...

Help with writing Triggers

Jim Abel - 22 Dec 2005 5:26 PM - 7 messages

I have no experience with Triggers and need help to create 2. My goal is to generate the average and Stard Devation for 2 values when they are inserted or updated. In the first field I insert a count that I need to calculate the ...

How do I use a UserDefinedFunction in a View?

James - 22 Dec 2005 5:12 PM - 5 messages

I have a UDF that takes one single INT parameter and returns a Table like So:: CREATE PROCEDURE dbo.TestUDF ( @aParam int ) AS BEGIN CREATE TABLE #TestTempTable ( a INT PRIMARY KEY, b INT, c INT ) ...

Result set from stored procedure

Confused - 22 Dec 2005 5:09 PM - 2 messages

This is probably stupid, but can I have 3 selects in my stored procedure and get the results back from all of them? I have a set of data and I need to get 3 sets of totals based on ...

bizarre query problem

John 3:16 - 22 Dec 2005 5:02 PM - 4 messages

Below is part of a stored proc. This code returns an error ONLY if Field.OrderId is included in the query. It runs ok if the OrderID is not included. The error recieved is: Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18 ...

Case Statement Really Urgent Please

marcmc - 22 Dec 2005 4:53 PM - 8 messages

Why will this case statement not work? It checks the length of a char field TRAN_TIME and pads it accordingly. SELECT a.TRAN_TIME FROM QUINN_ST..get_bcp_h_cvcmis a(nolock), QUINN_CT..Rec_Pol_WH b(nolock) WHERE a.POLICY_CODE = b.Policy_desc ...

getdate() rounded when runned in a job.

Geir Holme - 22 Dec 2005 4:50 PM - 3 messages

Hi all. I am confusing on this matter so if anyone recognize this please let me know. Simple scenario. I am using getdate() to fill datetime into a column in a table. All within a prosedure. Create a tmp table with datetime column. ...

Matching Transactions and Duplicates

downintheflood@yahoo.com - 22 Dec 2005 4:50 PM - 4 messages

Can anyone suggest a good approach to this problem? I need to match transactions from two tables on several columns. When I have a match, I need to set a match flag and a match number in each table. There can be duplicate transactions in either table. If I have ...

Procedure or function has too many arguments specified.

tomm - 22 Dec 2005 4:35 PM - 3 messages

I've seen others have had trouble with this. Using vs2005 connecting to sql2000, procedure works from command object with parameters but doesn't from a datasource defined on the page. I've tried any number of variations to get it to work, uses a session parameter and three ...

newbie tsql/column def'n: default values - SQL Server 2005

Chris Smith - 22 Dec 2005 4:24 PM - 3 messages

I have a table that has phone numbers, and a phone-type column. The phone type is foreign-keyed into a phone type table, and the phone-types in both tables are integers. I want to change this to use the new UniqueValues data type. ...

Fiscal Month Date Range: 22 thru 21

JDP@Work - 22 Dec 2005 4:13 PM - 7 messages

I'm looking for a script to do the following? I need to have a date range for each month of the year from the 22nd of each month to the 21st of the next. This doesn't work... as it starts at the first, I need it to start at the 22 of ...

Need help with Month / Year Query

Gomi - 22 Dec 2005 3:57 PM - 5 messages

I have a table with order dates for the past few years. I am trying to write a query that just ives me a distinct month/year for all the orders so that I can do my reports monthly. I have been wracking my ...

Need help obtaining query_plan from T-SQL statement (SQL Server 20

Mike - 22 Dec 2005 3:41 PM - 4 messages

Could some please help me in obtaining the query plan by modifying the T-SQL statement below? Thank you. select top 50 sum(qs.total_worker_time) as total_cpu_time, sum(qs.execution_count) as total_execution_count, count(*) as number_of_statements, ...

Popuate field with value from last row

rmg66 - 22 Dec 2005 3:20 PM - 54 messages

Does anyone know how -- during an insert -- to automatically populate a field with the same data as the field in the last inserted row? Thanks. ...

64-bit SQL

MAS - 22 Dec 2005 3:19 PM - 6 messages

I have run into an interesting problem. I have some code that adds a column to an existing table. The column is set as primary key, identity and clustered. On the 32-bit version of SQL Server 2005 it works fine. It ...

Finding the right record 2

John Baima - 22 Dec 2005 2:24 PM - 4 messages

Okay, I can restate this in a more simple way. If I have a table like: EmpSSN EmpID LastTimeIn 001624633 13864 01/28/05 020489644 2897 01/28/05 ...

Why query optimizer does not select the index?

Vagif Abilov - 22 Dec 2005 12:32 PM - 10 messages

Hello, I have a table with a couple of non-clustered indexes. One of them is a single-column index on a time column. The table has several hundred thousands records, so when I search for data within certain time range, I ...

auto increatment

calvin - 22 Dec 2005 11:39 AM - 7 messages

how can i do that in mssql ? e.g. existing data Column 1, Column 2 A 001 B 001 ...

COnvert

Phil - 22 Dec 2005 8:47 AM - 5 messages

Hi All, I posted this query a coupe of days ago and had a few repiies but I have only just got around to trying them out, I was trying to convert a nvarchar values of 01112005 which this time I will explain is meant to be the !st of ...

connections remote SQLExpress

EWAT - 22 Dec 2005 8:31 AM - 3 messages

how I can connect remotely to SQLExpress?.. I have enabled TCP/IP, but it does not work. error: ..... When connecting to SQL server 2005m this failure may be caused by the fact the under the default settings SQL server does not allow remote ...

Merge XML data

VV - 22 Dec 2005 7:36 AM - 1 message

I'm trying to merge two or more database records. For example: id | doc (type xml) 1 <s><t id="2"><p n="1">Something</p></t></s> ...

INFORMATION_SCHEMA question

Jeff User - 22 Dec 2005 7:02 AM - 1 message

Hi I am selecting some column info from information_schema like this: SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE FROM test.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Test Table'; I am doing this via OleDb connection to Sql2000 I connect as the owner of the database/tables. ...

why is it giving output ?

Manish Sukhija - 22 Dec 2005 6:34 AM - 6 messages

Hi, when i run this query select * from personal where phone > '40-8496-7223' it works fine, eventhough i declared phone as ...

alternate to "NOT IN " query

Vikram - 22 Dec 2005 5:19 AM - 3 messages

any better way of writing this Select serialnumber from tbl_test2 where serialnumber not in (select serialnumber from tbl_test) and exportedflag=0 ...

append property value

Vikram - 22 Dec 2005 5:06 AM - 1 message

sorry for incomplete text earlier i am using sp_OASetProperty for setting property value for a cdo.message object. But it might happen body size is more than 8000 character... in that case i want to write follwoing statement in a loop ...

append property value

Vikram - 22 Dec 2005 5:04 AM - 2 messages

i am using sp_OASetProperty for setting property value for a cdo.message object. But it might happen body size is more than 8000 character... in that case i want to write follwoing statement in a loop ...

How to insert data into table varialable in a sp from dynamic table

John Smith - 22 Dec 2005 3:09 AM - 8 messages

In my sp,I want to get some data from a table which name is dynamic ,and insert the data to a table varialbe,in other words,look like below: StoredProcedure ( DECLARE @TABLENAME VARCHAR(255) DECLARE @MyTABLE TABLE (ID INT,NAME VARCHAR(255),AGE INT) ...

Stored Procedure Script

Bil Click - 22 Dec 2005 1:04 AM - 7 messages

I would like to run a script that takes some variables and creates a view and stored procedure. I keep getting an error saying "'create view' must be the first statement in a query batch". What is the correct way to do this? ...

Finding the right record

John Baima - 22 Dec 2005 12:19 AM - 11 messages

My brain is not working right now and I need some help. I have two tables Employee EmpID varchar(10) (primary key, unique values) EmpSSN varchar(9) And TimeSheet EmpID varchar(10) (foreign key to employee EmpID) ...

Create 3 users tables

Miguel Dias Moura - 21 Dec 2005 11:10 PM - 2 messages

Hello, In my web site I have 2 types of users: students and professors. All users need to login but the personal information for each user type has different fields. So I create 3 tables: professors, students and authentication, "professors" will have all the personal data from professors. ...

Consolidation problem

tshad - 21 Dec 2005 10:52 PM - 3 messages

I have 2 tables. In the User Table I have a UserID and the CompanyID of the Company he belongs to. In the second table (EmailBlocked), are the users or company a particular user has blocked. If a user blocks a company, then all users in that ...

Problem reattaching database

iScanTeam@community.nospam - 21 Dec 2005 10:49 PM - 3 messages

I am writing a program in vb6 to move a database and its log file from one directory to another using sp_attach_db and sp_detach_db. I used 'sp_detach_db mydb' to detach the database, moved it to the new directory location and then tried to attach the database using the command: ...

SVF in CLR with Nullable Parameters

I/Gear - 21 Dec 2005 10:36 PM - 3 messages

I'm just learning to use the new CLR integration features of SQL 2005 and I created the following simple function: [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true, SystemDataAccess=SystemDataAccessKind.None)] public static SqlString Foo(DateTime dtStartDate, DateTime dtEndDate) ...

Which Way is More Efficient - Declare Variable or Use Single Quote

RitaG - 21 Dec 2005 10:16 PM - 6 messages

Hello. Could somebody please tell me which is more efficient: 1) To declare a variable and set it to a value and then use the variable thoughout the SQL code. E.g. DECLARE @PL Char(2) ...

CHARINDEX in CASE Within SELECT Statement

RitaG - 21 Dec 2005 10:11 PM - 13 messages

Hello. I need help with using CHARINDEX. I have a column in a table (Discount_Specification) that could hold the following values: LF(I03U,CHA-14,ALL-0) MR(I05U,I06U,CHA-5) etc. I'm inserting into another table and need to pick up the value following ...

How to debug SQL Stored Procedure on 2005

Shimon Sim - 21 Dec 2005 9:50 PM - 4 messages

I can't debug SQL Stored Procedure on SQL Server 2005. I don't see any options in Management Studio and in VS.NET 2005 I see options but it is disabled. What should I do? Thank you, Shimon ...

Problem Using Alias

joeacunzo - 21 Dec 2005 9:43 PM - 5 messages

I need a select that sums the same column, but with different criteria. I also need to "roll up" the sums into a "higher level" sum based on some other tables. This is an accounting database, where subaccounts roll up into a single account (i.e. subaccounts 101 and 102 roll up ...

complex calculated fields

_n8 - 21 Dec 2005 8:14 PM - 2 messages

Does anyone have any shortcuts for doing complex calculated fields? Example: Code: -------------------- SELECT COALESCE((D.dropCount + S.stockCount),D.dropCount,S.stockCount,0) AS 'Total_Count' ,COALESCE((D.dropSales + S.stockSales),D.dropSales,S.stockSales,0) AS 'Total_Sales' ,(COALESCE((D.dropSales + S.stockSales),D.dropSales,S.stockSales,0)/COALESCE((D.dropCount + S.stockCount),D.dropCount,S.stockCount,0)) AS 'Average_Sale' ...

How to update the newest of several records

Terry Carnes - 21 Dec 2005 7:42 PM - 2 messages

I'm writing a stored procedure that updates a record in a table which is keyed by [ID_No], [Sub_ID] and [TimeStart]. I can easily find the record to update by finding the Top 1 Where [ID_No] and [Sub_ID] are the IDs I want and I Order By [TimeStart] Desc. ...

data content comparison

GB - 21 Dec 2005 7:05 PM - 6 messages

Hello: I'm looking for a free tool or procedures to compare data content of two tables which belongs to different servers. Each table does not have any indexes or primary key. I need this as verification procedures for my ...

Default Value Calculation

Asp Psa - 21 Dec 2005 6:20 PM - 22 messages

Hi, Is there a way to calculate the Default Value of a column with other data columns? Using "date_of_birth" column and "screen_date" columns, I would like to calculate the "age" automatically with the Default Value definition. Please let me know if it's possible. ...

Is it possible to insert a PDF File into a SQL 2k Table?

Jorge Luzarraga Castro - 21 Dec 2005 6:17 PM - 3 messages

Hello, I want to develop a small PDF Management System for our Web Insurance Systems and I´m wondering if I can use SQL Server to save my generated PDF Documents. Is it possible? If so is it suggested? Are there any other ...

Deadlock updating different rows

rand - 21 Dec 2005 6:17 PM - 11 messages

Here is the condensed version of my question: why do I get a deadlock when multiple threads are updating different rows in the same table? Details: I have a table with a clustered index spread across multiple columns. I run multiple threads, each of which accesses a separate row ...

EXECUTING PACKAGES from vb.net

Gina Hernandez - 21 Dec 2005 6:10 PM - 3 messages

Hello: I have a web application from where I need to execute a DTS, my platform is SQL 2000 and .net 2003. From a Vb.net module I want to execute the package, my question is: does the package need to be STRUCTURED ...

Performance - php scripts

culam - 21 Dec 2005 6:06 PM - 4 messages

A vendor wrote a php script to manipulate date within SQL Server, basically process data from 1 table and copy the data to multiple tables within the same server. He said the preformance for others client run really fast, ...

script out table definition and data

sqlster - 21 Dec 2005 5:42 PM - 5 messages

Is there any free download tool that I could use to script out table definition along with the data? There is a tool by vyas but it was not scripting out correctly. I don't mean to say wrong things about the tool but may be it was "my bad"!! ...

stored procedure -> this.getName()

peppi911 - 21 Dec 2005 5:39 PM - 2 messages

hi, ist there a way to retreive the name of the procedure i am actually in, to send an email with error/name of the procedure? CREATE PROCEDURE xyz as begin if @error<0 sp_sendMessage @error ??@getName -> xyz?? ...

Strange result associated with execution of stored procedure

Jack - 21 Dec 2005 5:23 PM - 6 messages

Hi, I got a stored procedure which is as following: CREATE PROCEDURE sp_parmSailorRanking @ClubCode varchar(50), @Rank varchar(10) AS SELECT People.PeopleID, People.PeopleNameLast, People.PeopleNameFirst, People.PeopleProfessionalClass, People.PeopleClubCode, Rank.Year, Rank.Month, Rank.Rank ...

No retrieving dupicate rows

Mike9900 - 21 Dec 2005 5:19 PM - 9 messages

I need to get rows without dupicate ProductID. In the below query, the subquery return 1 row, which is fine. But the query itself returns 2 rows because the TransactionDate has two same date. I want the quesry to return ...

Please help - setting order of select in a self-referencing table

Alan Silver - 21 Dec 2005 5:11 PM - 3 messages

Hello, I have a table of product categories that looks like this (air code, so could be some typos, but it's basically right)... create table producttypes ( ptype int not null identity(1,1) primary key, typename varchar(50) not null default '', ...

Lock and update problem

GM - 21 Dec 2005 4:38 PM - 4 messages

Hello, I use sql server 2000. I want to create the following stored procedure: I have a table with serveral field including the field "isqueued" I want to select one record (select top 1) from the table and immediatly update the iqueued field to "true" while locking the record. ...

case and group by problem

Sammy - 21 Dec 2005 4:23 PM - 6 messages

Hi I have this code below it gives me the right results but there are load of null values. Is there a way to group by on a case statement to elimiinate these nulls to smarten up ther report select ,c.name ,c.custno,c.salesno, case catno ...

Problem connecting to SQL Server 2005

Pascal Berger - 21 Dec 2005 4:18 PM - 3 messages

Under certain circumstances there seems to be a problem connecting a SQL Server 2005 (Final Release). The connection is done with a Delphi Application using an ADOConnection (SQLOLEDB.1 provider): - Setup a vanilla WinXP PC (Seems to also appear under Win98, WinME, and ...

Service Broker - BEGIN DIALOG CONVERSATION @dialog_handle

Electrical Symbols - Ohms - micro

Performance

XP_smtp_sendmail

How to Restore an Archived Filegroup on Another Server?

Would like an Exists in a Case statement

Book Recommendations

i want help to build a application that will load data in msolap

How to find the triggers in a database

Login Failed error 18456

Collation issue

Dyanamic Where Clause

Indexed view wrong count

how to duplicate my database?

Urgent Requirement for identity

SQL sequence of subprocedures

I need to rebuild Log file from Master file

Select/Join with Top

Change TSQl statement using case

Error automating SQL DTS Pkg from VB6 to VB.Net

Getting the last Error Message

SQL Server with Access2000: Unique identifier and Dates restriction

Query Question

Possible Bug Using TOP and Paging via a Temp Table

Outputting a CSV file

Get who has an application lock? Lock name is mangled

Procedure or function has too many arguments specified

Sql view

Run jobs in SELECT CASE?

Reduce Log Size

How do I use SELECT on a column with datatype TEXT?

Last table date access

Changing a column to an Identity column. Please Help...

Coalesce and CASE

SQL Union issue, detail records returned when condition not satisf

Adding a Column in an Ordinal Position

indexes.

Convert Informix to SQL Server

Registering User changing data

SQL 2005 books

OT: Need Project Tracking software

Problem : Oracle *statement* trigger runs for each row when called via a linked-server definition in

Performance question.

Custom Error Message for Check Constraints

global temp table issue with SQL Server 2000

DDL Primer?

Easy Start

sysindexes

Re: sql server 2005 Ebook

Replace substring

user function basic question

sql server 2005 Ebook

List only the visible columns in a view?

Getting Job name, Job ID , Step within the Job

String Function for Grouping

Stored procedure using dynamic WHERE statement

Query Analyser/ Optimiser

Moving transaction log file

clear sql error

How do I set decimal(9, 6) using enterprise manager

Need help with Update statement; DDL included

In over my head - need good learning tools

Getting totals

Error trying to retreive lastlogon value through LDAP ADSI

How to use SQL2005 Notification Services?

Finding a value within a range using IN

error when inserting same data into database

Is a "Double" Insert on a Single Select Statement Possible?

SQL 2005 Books

data conversion -- varchar to nvarchar

NOT IN not working

DateTime column conversion

Group By Date?

Stored procedures

Tran Log file reader???

XP_Sendmail - calling stored proc with parms with a '\'

Scripting Jobs

Can SQL Unzip after backup is complete

Domain Error variation

Scripting Tables from Query Analyzer possible?

Making a create statement from existing database

Extended Stored Procedure

INFORMATION_SCHEMA on another database

Convert/Cast

ORDER BY DESC

nested case - is there an simpler way to do this?

Getting identity value

HOST_NAME ( )

SQL Math

DB Metric?

Dynamic WHERE clause (SQL server 2005)

search question

Locating strange updates

Service Broker : Need to confirm a security constraint

sysdepends

How to Group by Max of Sums and display additional field (siteid)

BCP issue

Grouping accuracy

sql server 2000 stops after transaction

view should not make affect on base table

Selective update

Sort to display without duplicates

Change dateformat

Error

Dynamic For Loop

Grouping and Average Question

Error 213: Insert Error: Column name or values don't match table definition

Group by problem!

WRITETEXT when column value is NULL

trigger help

Temp Tables

Help with writing a Stored Procedure

can define UDF in DLL

Error in using xp_cmdshell and osql

Violation of PRIMARY KEY constraint errors.

Pausing-Restarting SQL Server Service

Adventure Works DB

need some TSQL help