Hi, I have the following details table: ElementID DetailID Category 10001 2 1482 10001 3 1914 10001 4 1705 10001 5 1683 10001 6 2031 10002 3 ...more >>

Assume 3 fields A, B & C and a single record where: A = 'AAA' B = 'BBB' C = 'CCC' I want to do an update to change C to 'ZZZ' and A and B are either '' or NULL. I want the update to result in: A = 'AAA' B = 'BBB' C = 'ZZZ' The application is written in .Net. Is the only way to accomplis...more >>

I have a procedure to update a billing record by recalculating all payments made to the bill. There are six types of payments that can be made, and I am currently performing a SELECT for each type of payment. I am wondering if there is a way to simplify this sp to speed it up. As a side note, ...more >>

Hi - I'm typically a layer removed from having to worry about locking issues. But it looks like I can't escape it forever. If I have 40 worker threads digesting and updating a single table, how can I best assure that each thread selects its own partition of data? Burning through the table...more >>

Hi all, I'm making decision on using a solution for handling employees chart. I was suggested a good solution (in this newsgroup) based on famous (EmployeeID,ReportsTo) columns to use a variable table and a loop to retrieve the chart. But the company has asked me that if I can still find fast...more >>

I have 2 tables teams, matches. teams has structure teamno and teamname matches has hometeamno, opponentteamno, startdate What i want is to get hometeamno, hometeamname, opponentteamno, opponentteamname, startdate Last time i asked about this question and i got all correct result...more >>

I am storing some field in chinese. (field type is nvarchar). When i store it i can not see as chinese character but as some sort of unicode characters. (unreadable) When i try to pick it up from database and display in asp page - i get it but same unicode format (unreadabel) Now wheth...more >>

Hello all, I have a table the tracks jobs and footage. I am attemping to return an average count for each job. Each job can have a 100 different entries in the database.. Table example: OrderID BundleName Length Bundlelayer ...more >>

Hi, I am trying to figure out how to compare or detect changes in two text datatype fields. One containing an old text string and the later a revised/new text string. SELECT (CASE WHEN src.strTrialResults = dest.strTrialResults THEN 1 ELSE 0 END) AS btEqual FROM tblUserSavedTrials AS sr...more >>

Howdy, I need to write what I would think is a simply trigger to delete the = record if a certain field is equal to a string. So, in my head, it would = look like this: if [field] =3D "string" then delete record. But of = course its not as simple as that. Any ideas? Thanks, --=20 David ...more >>

I want to Update multiple fields in a record with multiple fields from one select statement, what is the syntax? ...more >>

Hello, We have a column with datetime datatype. When I select the column using Query Analyzer SELECT create_date FROM myMail I get a result in the following format: 2004-12-15 18:54:03.073 2004-12-15 18:54:03.493 2004-12-15 18:54:03.593 2004-12-15 18:54:03.823 2004-12-15 18:54:0...more >>

Here's a piece of an update qry UPDATE [CPSS_Warehouse].[dbo].[INETINFO] SET [CustName]= (select CustName from cplu.dbo.vw_inetinfo_push WHERE inetinfo.dex_row_id=cplu.dbo.vw_inetinfo_push.dex_row_id), [ADRSCODE]= (select adrscode from cplu.dbo.vw_inetinfo_push WHERE inetinfo.dex_row_id...more >>

I have a stored procedure that use FOR XML to output. I want to test the output but in SQL Query Analyzer, it only return the first 1xx character of the XML data (same result if I return the result to a file). Is there a way that I can see the complete XML data output from the stored pro...more >>

I have a query that comes up in many similar forms and would like to know the best way to approach it. I have a client table and an address table that stores a historical record of addresses for each client. I want the query to pull a list of clients and the latest address (by the date_cre...more >>

Hi all, There may be a simple answer to this such as you cannot do that in SQL. I have a select statement that looks like this: Select * from someTable There are a lot of fields in that table and I want to select minus one or more fields instead of typing every single field in the stateme...more >>

SQL 2000 I got a table with file names and path for the Excel files to be loaded into a sql table. My plan is to use DTS and global variable to assign the path and file name and use that as the source for the data-pump. Any thoughts or sample code to achieve this ?? Thanks In Advance ...more >>

I know that Select * is generally frowned on. Samples using Where Exists and Where Not Exists generally use Select * in the subquery, though. Does the list of fields being selected in the subquery have any real meaning? Here's a sample from BOL for SQL 2000, talking about set difference...more >>

I am trying to check every field in the inserted and deleted tables in an update trigger. I want to compare each field value between tables because I don't want to log fields that haven't acutally changed. The only thing that comes to mind is to create a cursor based on the inserted/deleted tab...more >>

I have table with startdate and enddate. I would like to get all occupied date ranges - all ranges of days, which has been in period. Example: startDate endDate ---------------------------------------------------------------- 2004-09-21 09:34:10.000 ...more >>

Hi, I am writing the following query, my problem is that in my return I am getting 8.000 instead of 7.500. I am not sure why this isn't working, can you help? SELECT distinct c_driver,orderid as OrderID,shiptoacct, shiptoaddressseq, readydate, opreferredroute,totalcartons,customerid, cas...more >>

Hi all, By stored procedure, how can I count the number of Sundays between 2 dates? Suppose @d1 and @d2 are the input parameters of the stored proc. Thanks....more >>

I know in sql server using dts you can export the results of a query as a text file. However, I have run into a problem. I have a stored procedure that takes parameters. I want to export this results value as a text file. Is there any tsql statements that allow me to write results to a tex...more >>

Hi, I am a stored procedure newbie, let me ask a stupid question, hope someone can help. I have a table with primary key pID and it is in int type and is an auto-incremented identity value. I use an INSERT statement to insert the record, say INSERT INTO tblTest (secondField, thirdFie...more >>

Hi All, How to send mail using Sql Server 2000 Stored Procedure through Exchange Server Regards, ...more >>

I have a web app that calls a SP. Twice in the last month the app has locked up. The first time we ran a SQL Trace which showed that the SP had "completed". But no dataset was returned and ultimately the app "timed out" by presenting an NT challenge dialog. Here's the weird part: if we...more >>

How can I create a unique constraint using t-sql. The constraint involves 3 columns that need to be unique. I know how to do this using Enter. Mngr, but how can I do this using t-sql. Below is my table definition and the t-sql code that is not working for me. I wan the first 3 columns to b...more >>

How do i change this connect string to support a SQL Server 2000 running on port 8832 User ID=car;Password=rat;Server=abc.def.hij;Initial Catalog=foobar i tried Server=abc.def.hij:8823 and Server=abc.def.hij;port=8823 neither of these work ...more >>

Hello, system administrator asked me to modify my VB application to authanticate itself for ISA when wants to connect to an outer sql server. the system administrator dont want to open fully the port 1433 (from inner LAN), and also dont want to set my application name at ISA. Is any way to sol...more >>

Hello folks ! I have a "simple" question, i hope someone can help me: I got a list (Ex$el it is) with 2 rows (ROW1, ROW2) and 2816 lines. I want something like update table set db_row2 = row2 where db_row1 = row1 Is this possible ? In the database row1 has multiple occurences, in th...more >>

Hello Sql Gurus, For me Sql Server Security concept is the one very much confusing. I had gone to the BOL but lot of pages to read. But I am looking for a few pages articles which explains very well abt the security model abt groups,roles,users, loging,fixed database roles , fixed server r...more >>

i have a table with a datetime field . when i do a select datefield from table i get datefield ---------- 2004-10-12 how can i get it to show in dd/mm/yyyy format...more >>

Hi Guys, i need some help... i'm creating a bowling database for a tournament i'm gonna run, and i have the following problem... i'm gonna put a small example of my tables to show it.... TABLE BOWLERS : ( BowlerId,Name ) TABLE GAMES: (BowlerId,GameNumber,Total ) The thing is i want t...more >>

I have been sent a sample TRADACOMS 9 invoice as part of a project. I would like to import certain elements of the invoice into a SQL database and then export out. The export isn't a problem it's just the import. I would like to carry this out via DTS. Does anyone have any source code or knows of...more >>

Are there any routines out there that will automatically convert a table (A) with numerous numeric fields to a new table (B) with just one numeric field. Thus the number of records in the table (B) would be the number or records in A mutliplied by the number of numeric fields. Thanks in advan...more >>

Allright, so we have this unique situation that we have a bunch of tables in which their primary keys must be mutually unique (i.e. keys are unique all over these tables). I could use GUID, but I'd rather not because of performance and bloat reasons. The other option is to emulate oracle se...more >>

where would the text of the stored procedures be stored? I need to find, in a large list, a SP with specific text in the notes of the SP. Any help is appreciated, Thanks! ...more >>

I am working with an application database on SQL Server where there is a need to maintain a database history. Several related tables have 2 date columns FROM & TO which define the date range that the row is valid. Every row in every table has a GUID column which we are using as primary k...more >>

hi in what format does sql server store dates? and can this default format be changed? how? Thnx Ichor ...more >>

Hello, I am looking for a utility that can help me to populate data to some of my sample databases. I know that the SQL Resource kit had tool like this (I think it's called hammer or something) but since I can't get access to the SQL Resource kit can someone point me to a resource on the w...more >>

Hi SQLServer gives me error 8626 when I execute an update- statement on a table with a column of text type. DDL: CREATE TABLE [dbo].[DM100 Wettbewerbe] ( [Schluessel] [int] NOT NULL , [GBL] [int] NOT NULL , [Bearbeitet] [datetime] NULL , [Geloescht] [bit] NOT NULL , [Beurteilung] [t...more >>

I am trying to register a xp on S2k. The DLL was written in C#. I have not been successful. I use the Wizard and the sp to add extended sps and still get an error message stating the the function can not be found in the DLL. I have found some references (codeproject.com) on how to reg mana...more >>

I have table, where one of the columns columns is datetime data type. Now I would like to get for example, the first free wednesday greater than now. example: dateColumn ......... ------------------------- 08.12.2004 10.12.2004 15.12.2004 17.12.2004 19.12.2004 29.12.2004 30.12....more >>

Hai, When I try to connect SQL Server, I am getting the following error. Specified driver could not be found (Error Number 126). Can anyone help me to solve this problem. Regards, Sevugan.C ...more >>

Hello- I am trying to audit values between 2 different databases. ie: We send out dividend checks every year, and it is based on how much our customers spend with us. Therefore, once a year we allocate our profits to the customers by dumping customer information from the billing databa...more >>

Hi, I am seeking pros and cons, examples, and opinions whether it is a good idea to store images in the database instead of in a directory structure. The images in this case are tif:s, usually less than 50K, and about 100 000 per year. I want to save the image and its meta data in the same ta...more >>

Hi all, How can I get the result showing below which include the business rules. Thank you in advance. IF OBJECT_ID('Tempdb.dbo.#Prospect', 'u') IS NOT NULL DROP TABLE #Prospect GO CREATE TABLE #Prospect ( Person_id INT NULL, MFPolicy_nb INT NULL ) GO INSERT #Pros...more >>

I want to find all people of all ages when the parameters aren't specified in the proc below. I know how to do this with the varchar field but not with an int field. I know the '%' is completely wrong for age since '%' is a char and age is an int field. It's there just to show what I'm tryi...more >>

Hi All: I need to devel a tsql script that will track changes of various SQL 2K DBs and Logs on a weekly basis and write this information to a statistical table. Any sample code would be appreciated Thanks Brennan...more >>

I have an assignment that requires me to do log reporting via a web interface. The database incriments the table name every month for example: RAFT Billing Apr 2004, RAFT Billing May 2004. I have a stored proc that goes out and unions all the tables and then another that queiries that union. ...more >>

Hi, I came across this question and would like some one to answer if possible. We are designing a solution where we would be storing multiple preferences in form of name value pair for various user. Data could range from 100 + name value pair with 4000+ chars in size. What could be best ch...more >>

I have this table create table dbo.cusomter ( custnum char(16) not null constraint customer_PK primary key (custnum) , ProcessCode char(4) not null , custName varchar(32) not null , DateNew datetime not null , DateLive datetime null , DateOld datetime null , Method ...more >>

Hi guys, I am working in a reatil company where they do 1000s of transactions per day. Our application is running on SQL Server 2000 :) and the application response time is damm slow so can any one give me good hints, tips and best practices to improve the SQL Server perfomance and respons...more >>

Dear Friends I am writing Procedure where in i need to refer to the computed columns. and i am getting the following error. Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'balgw'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'BALPKG'. ****************...more >>

Hi, I have a cube with a calculated measure. In the cube the value shows correctly, but when i create a MDX query to display this calculated measure i get an error: 1.#INF or -1.#IND Anyone have an explanation for this? The calculated measure is: [Measures].[Available]/Count({des...more >>

Hi all, I have a question about a conversion of a Oracle stored procedure, in SQL Server. I need some advice about my job (correct or less, ecc.). Below I've written the 2 stored procedure (original is in Oracle). It is correct the SQL Server version? Any advice is well accepted. Thanks so mu...more >>

Hi everybody, I've a little problem with a transaction that updates a value in a sql server table. I execute an insert code inside a begin transaction .............commit and when I do a select I saw the right value. If I try to find out the same value after some time it sames like it has not...more >>

I need to trim strings in a select statement. I have used LTrim and RTrim functions. But it removes only blank spaces. It does not remove the tabs and white spaces. How can I do that? ...more >>

Hi, I would like to create a calculated measure which compares if a measure has a certain value, for exmaple: iif([Measure].[Complaints]=1,'Text 1', 'Text 2') The problem is that you can not insert the equal sign (=) in the MDX queries. It won't except it. How can make such compar...more >>

Hey, there, I'm trying to write an SP that will copy an arbitrary SP to another database. Here's where I'm stuck: ALTER PROCEDURE dbo.procArchiveResults_Procedure @ArchiveDatabaseName varchar(50), @ProcedureName varchar(50) AS SET NOCOUNT ON DECLARE @cProcText VARCHAR(8000) ...more >>

I am trying to create a generic query to return data from a column based on the column number not the name. I am sure I have seen this done, but can not find where. Any ideas? Thanks, Phil....more >>

Hi experts, In the table [Order Details] of the Northwind database, i need to display the records having second maximum quantity in each OrderID. I worked with the view type query for the above said reqr.. SELECT OrderId, MAX(QTY) SECOND_MAX FROM ( SELECT s1.OrderID AS OrderId...more >>