Today many applications (SAP, SIEBEL, MANUGISTICS, PEOPLESOFT, and so on) does not have a client direct access to connect to the database. They use an Application Server that has only one connection to the database and the users get connected to this application server. THE QUESTION: How to co...more >>

I have 2 tables related as: T1.KEY, T1.FIELD1, T1.FIELD2 T2.KEY, T2.FIELDA, T2.FIELDB T2.KEY, T2.FIELDA, T2.FIELDB T1.KEY = T2.KEY I want to return a SELECT as: T1.FIELD1, T1.FIELD2, T2.FIELDA, T2.FIELDB, T2.FIELDA, T2.FIELDA The second table, in some cases but not all, has multi...more >>

I have two tables: tb_news-- -story_id -productlist tb_lookup-- -story_id -product when an insert command is run on tb_news, productlist field is populated with a value such as 'abc, def, de' when this happens, i need tb_lookup to be populated with seperate records for each product...more >>

Hi All, I am trying to delete rows from a table using a SQL statement similar to this: DELETE FROM TableA where ID1 IN (Select ID1 From TableB where ID2>= @min and ID2<=@max) Basically I want to delete all rows from TableA that have an ID in a range in TableB. This is done in a stored pro...more >>

I have a question. I am doing some work for someone and I have a batch file that they can run that will execute an OSQL line and a DTSRUN line. In both lines I run them using the /S /U /P switches and of course the /N or /i switch to tell it what to run. I have also tried replacing the /U /P ...more >>

and if so does anyone know where I can find some code examples? thanks ...more >>

Hi there I have small problem with IDENT_CURRENT... We have a table where PK is generated automatically. What I want is to get that value and insert it to another, corresponding table. So here is what I am doing... BEGIN TRAN Insert Into table() --> PK is created. SELECT IDENT_CURRENT('Table...more >>

TURN $6 INTO $42,000 EASILY!!!FOR REAL!!! THIS IS REALLY EASY MONEY!! TRUST ME IT WORKS !!! BUT YOU HAVE TO FOLLOW IT TO A LETTER FOR IT TO WORK!!!! A while back, I was browsing through newsgroup, and came across an article similar to this that said you could make thousands dollars within weeks ...more >>

I have a field containing a string with '/' in it multiple times. How can I return the charindex of the last occurance of '/' in the string? Regards, Ciar=E1n ...more >>

I have two tables I need to join but there are 2 fields which they could be joined on. Using the example Tablles, TableA and TableB below; TableA ID1 ID2 Qty 1 Null 4 2 A 5 Null B 6 TableB ID1 ID2 Qty Null A 6 3 B 6 4 Null 7 Null C 8...more >>

My client has a number of jobs that are run overnight. We've set them up to email me when they're completed. Every morning I get in to a bunch of emails like this: <quote> JOB RUN: 'Tech Pubs Email Notification' was run on 18/03/2006 at 00:00:00 DURATION: 0 hours, 0 minutes, 0 seconds S...more >>

I have a table A, with two ID columns. In a report both ID colums should be shown with the actual value stored in a second table, B The problem is, both IDs need to be looked up in B, but are not in the same row. How do I do this in an efficient way? A sub select? Thanks, -- John ...more >>

Hi, I was wondering if anyone out there can help me with this SQL problem: I have a database that has two tables: EMPLOYEE and JOB_TITLE The EMPLOYEE Table consists of a salary and job_title _code columns, among many others; the JOB_TITLE table contains job_title_code column, among ma...more >>

Hi All, Sorry if this seems incredibly basic but what is the syntax to solve this query. I have a table (ENC_UserDefinedData) with 4 columns, ID, UserID, UserDefinedField and Value. I want to select all the UserIDs that match this criteria: [ENC_UserDefinedData].[UserDefinedField] = 8 AND ...more >>

I need help capturing information from a free text field. The 10 examples below contain examples of the information I am trying to capture. In each cell I am trying to capture the number between 'TranID=3D' and the next '&'. So in the fisrt cell I would like to capture 14078800. Things you w...more >>

Hi All, Can anyone help with the following problem? I have a database which contains a table with a 'text' field, and the text field contains an xml document - typically 50-100K. Now I'd like to make use of SQL Server 2005s XMLData type. To do this I have created a new field of the 'xmldat...more >>

Hi experts, Ex. Data set in TableA ID, Value -------------- 1,A 2,B 1,F 3,C Expected result set ID, Value ------------- 1,A,F 2,B 3,C Can I do this with T-SQL? Thanks. Regards Dishan ...more >>

Hello. I'm using SQL2000, and I have a design problem. I know that every time I make a primary key, sql server makes it by default a clustered index. Since I have a large composite key in the table I don't know if it's smart to leave it as a clustered index. This is the table: CREATE TABLE [In...more >>

Hi! I have a table with a check constraint. But unfortunately it does not work like I wanted. CREATE TABLE MAP ( [R_ID] [T_D_ID] NOT NULL, [R_ID1] [T_D_ID] NULL, CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 = NULL), CONSTRAINT [PK_MAP] PRIMARY KEY ([R_ID]) ) R_ID1...more >>

hello sir, i have a table emp ,it has three fields one is empno int ,second is ename varchar(20). and last is salary , emp empno ename salary ----------- ------------ --------- 1000 suresh 10000 1001 ramesh 20000 i ...more >>

Hi everybody, I have a table storing the test dates, student names and the grade of the students. I am now going to find out those dates with student1 got A and student2 got B and vice versa (i.e. student1 got B and student2 got A). How can I do that? I think it is quite complicate for me. Pl...more >>

I heard about bigpubs2000.mdf but i can't found it in google. Maybe someone have it. or have a good link to download this db. ...more >>

Hi everyone, It looks like a mystery, but I hope there should be some explanation to the issue I experience. Once in a blue moon a random stored procedure stops working the way it was designed. The stored procedure code looks unchanged. Recompiling, altering the code do not help. It looks like...more >>

Hi In my server (MSSQL 2000 sp4) very often I see long time of executing query with waittype=WRITELOG Recovery is set to SIMPLE and there is no autoShrink. Auto increment file size (log) is set to 10% What can be the reason of this problem? Marek ...more >>

I have a one to many relationship between a billing table and a task table. I want to write a procedure I can pass a billingID to and it will go delete all the task records with that billingID as well as the as the single billing record. Like referential integrity. There are two other task t...more >>

I am recommending that we change our Raid Configuration on some of our Servers from Raid 5 to Raid 0+1; we are experiencing severe IO bottlenecks. Our hardware guys are pushing back a bit. They claim that Dell has a weird implementation of 0+1 and told me something about one drive filling up...more >>

Thanks in advance for any help. I'm trying to attempt the following with one table/query. I have a table called TABLE_1 It has the following fields: SSN TITLE LOCATION 322 EX DALLAS 568 REP FT_WORTH 536 EX DALLAS 485 SLS YOUNGSTOWN 854 BRO FEEDER 258 EX EVANSVILLE 478 T...more >>

Hi All, I am designing a purge process for a db that has grown to almost 200GB. My purge process will remove about 1/3 of the 500 million rows spread over seven tables. Currently there are about 35 indexes defined on those seven tables. My question is will there be a performance gain by drop...more >>

I need a trigger (well, I don't *need* one, but it would be optimal!) but I can't get it to work because it references ntext fields. Is there any alternative? I could write it in laborious code in the application, but I'd rather not! DDL for table and trigger below. TIA Edward if e...more >>

Hi all, I'm new to SQLServer (Express edition) so I was wondering: if the web host supports SQLServer 2005 do I just need to move the .MDF file to my directory on the web host to be able to use it? Thanks, Lorenzo ...more >>

hi i have emp table in one database , how to retrieve same table another database through query analizer give code ,,, ...more >>

Hi All, I want to pass XML and the data in the XML should be stored in the tables of the database. However, I do not want to use the OpenXML statement. Please let me know. Regards, Shilpa ...more >>

I have a SQL query I need to design to select name and email addresses for policies that are due and not renewed in a given time period. The problem is, the database keeps the information for every renewal in the history of the policyholder. The information is in 2 tables, policy and customer, w...more >>

Hi, I'm trying to upload a large number of log entries currently stored as text files into a database table using bcp. For a few rows I get a "right truncation" error and the offending rows are not uploaded to the table. I don't want to increase the size of the table varchar fields because ...more >>

I created a lot of procedures and functions using QueryAnalyzer from SQL 2000. Now I have a new workstation, and installed SQL 2005. The code on one particular server seems to be inaccessible. When I go to edit ("modify") objects, or copy them to the clipboard, I get the message, "Property A...more >>

Hello Everyone, Regarding stored procedures and views, I know that stored procedures cause SQL Server to create a cached execution plan. Is the same thing done for views? Also, how bad is the performance hit for a stored procedure that use 1 or a few views as opposed to re-creating the same se...more >>

Hello, I wanted your opinion on a technique that we want to use and hear from you if you know of or, have experienced performance or other issues in such a scenario. We have a business application that has two parts to it - 1. A part that works on things such as managing users, roles, autho...more >>

Hi All, I was hoping someone has experienced this before, I'm having trouble googling this. I'm working with a poorly writtend database that has some fields named as such: MTIC_PROD_VEND[ 1] I do not have the option to rename this field but I do need to retreive data from it with SQL. I...more >>

Hi all, I have a table with columns: Name, english, french, spanish, german values (eg) John, 20, 20, 30, 30 I want to diplay the data as: john, 20 john, 20 john, 30 john, 30 how do i do this? Thanx in advance.. ...more >>

Hai, Here is a query that i am creating a table create table test10 (item varchar(10),state bit) now at the time of insertion i will enter like this insert into test10 values ('book' ,1) but at the time of display in the table i want ...more >>

Good Morning, I have a view that contains rate information, contractIDs, and effective dates. I need to select the rate info based on contractID and date. I can provide a date and contractID, and I need to select the rate info for that contract where the effective date is <= dateprovided. ...more >>

Hi I want to write a function that can return a sum for a given date range. The same function should be able to return the sum for the same period year before. Let me give an example: The Table LedgerTrans consist among other of the follwing fields AccountNum (Varchar) Transdate AmountMST ...more >>

I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN working on a three table query. Assumptions: -- I have events in the Event table. -- Each event CAN have one Transaction, but it's not guaranteed -- Each transaction, ir present, will have one or more Amount records This would...more >>

Hello.. I was wondering if anyone out there could tell me how they deal with NULL values in a data warehouse? I am looking to implement a warehouse in SQL 2005 and have some fields which will have NULL values and I would like some further ideas on how to deal with them. At my last job in de...more >>

Hi, In oracle I have a LAG function using which I could get the previous value of a field. Do we have anything similar to that in SQL Server or access? Thanks Devi ...more >>

Whenever I want help on a query, I get told my design is wrong, So this time I'm posting a message during the design phase: How am I going to perfectly design the following? We want to be able to track time for users for multiple modules, for now a Schedule module and a Punchlist module. These...more >>

Hello All, I am am having a strange problem. I am trying to execute a DTS package from VB .NET (.aspx) page. The DTS package takes 21 minutes to execute. When the aspx page sends a command to MS SQL server to execute this DTS package it starts executing. After 8 minutes the .aspx page show...more >>

The database scheme consists of four relations: Product(maker, model, type) PC(code, model, speed, ram, hd, cd, price) Laptop(code, model, speed, ram, hd, screen, price) Printer(code, model, color, type, price) The relation "Product" shows the maker, model number, and type (pc, laptop, or ...more >>