Hi Is it possible to get all the column values in a single column from a table? For e.g. a1 a2 a3 == == == 1 2 3 4 5 6 I need the output like Allcolums *********** 123 456 I am not ready to mention the colum...more >>

not sure if this would be consider "on topic" or not, but i have a design question for all the brains out there. problem: many-to-many ( with priority ). "Person" can speak many "Languages" Languages can be spoken by any number of "Persons" People have an order of preference that they spe...more >>

I am using a query by joiing 2 tables. I know that you can join 2 tables the traditional way also ie Select * from table1, table 2 where table1.Col1 = table2.Col2 is this query any way inferior to Select * from table1JOIN table 2 ON table1.Col1 = table2.Col2 THanks ...more >>

Hi, Is there anyone know what is the the meaning of last re-cycle of SQL Server and when does it occur? Thanks, Kenny ...more >>

Hi Situation: A table contain 10 columns, say a1, a2, ... I want to select records based on the given value, say 'test'. The issue is I don't know the value 'test' is in which column. It could be in the column a1 or a2 or so on. How I can get those records? Thanks in advance ...more >>

I have a report output generated for each vendor and I want to email this attachment to the appropriate vendor. The problem is how can I send a email to each vendor with a attachment for the DTS email task or the XP_SMTP procedure. Thanks Karen ...more >>

All, I'm currently suffering trying to "explode" and parent-child hierarchy for optimisation purposes. The structure is thus: PK Parent VARCHAR(8) PK Child VARCHAR(8) PK Adopt DATETIME Abolish DATETIME What I want to get is an entity which looks ...more >>

Hello, I need some help with this trigger. I am a newbie at Triggers as you will see. I have had difficulty with some of the details of the concept. My questions are within the text of my attempt at the trigger. Here is how it is to work: When the column "Order" is changed for a record ...more >>

Whats the syntax for assigning the return value of a select statement in a stored proc to a variable in the stored proc? In the sample below I want to look up a value in an existing table and use it later in the stored proc Create PROC [dbo].[ShowMyStuff] Myval = (Select TOP 1 Myfield from ...more >>

Hi experts, i am working with SQL-Server 2000 and have a special problem in one of my stored procedures. To be as fast as possible i use a temp table defined as variable. And i want to calculate a value in this table depending on values in the same table. But i can't make it work. Here my prob...more >>

I have a stored proc with code similar to this: <SQL Query 1 Here> If @@RowCount < 1 Begin <SQL Query 2 Here> End Basically, I want to execute SQL Query 1 and if I don't get any rows, then execute Query 2. What happens here is that I get two result sets returned if the first one i...more >>

Please consider the following table: create table mytable( pkid int, d1 int c1 int null, c2 int null, c3 int null ) I want to make sure that for a given value of d1: c1, c2, and c3 are unique. How do I create unique constraints for that. I went throught this forum and found some exam...more >>

Hi all, Just trying to create a utility script..to populate a db table with dummy data.. Can anyone give me a bit of sytax help..so i can get around the errors listed below. Cheers, Adam Code: WHILE (SELECT COUNT(*) FROM cntr_mgt_contractors) < 300 BEGIN DECLARE @Coun...more >>

Hello all, I am trying to write an procedure that will return the topmost row of each author id that is in the table. This is what I have so far : select * from PreviousBills where [BatchDate] >= @date or [Date] >= @date order by [Date] DESC, [BatchDate] DESC this will return an ordered se...more >>

I'm trying to get a count of salesmanagers for a selected director from a listing of Sales. This is a one of many columns that I'm returning, but I need this ONE to be right. I want to get a count for a given dos as... dos = 2 (there are only two smg's for this dos) sod = 1 -- ddl b...more >>

Hi, a client privide me a cvs file with report tile (1th row) and column name (2nd row) and following by all the corresponding data. In DTS, you only has two choice 1) Skip ## rows 2) First row have columns name. If I skip 2 rows, then I can keep the column name. If I choose skip one...more >>

Hi, We have a scenario where we want to bulk insert data into only a select number of columns in a table. We are currently generating a BCP file with - data for all columns, NULL in case of columns which are not needed (These columns are nullable in the table) - column data is ordered as...more >>

Hi, I have to select a fiels from one table which is not presnt in both the other 2 tables.. What will be the query for this..? I used the following but it showes results where the field was present in one table..i.e it showed wrong data.. Here is what I used: Select MDN from Activity...more >>

Hello. I'm inserting rows into a table that I retrieve from another table. There's a lot of data manipulation going on during this process. For 10 columns in the Select From portion I'm using a CASE statement that starts with CASE WHEN Left(Discount_Specification, 2)= @PF THEN ...more >>

I have a table that stores IIS access logs. I run SELECT username, target, COUNT(*) as "hits" FROM weblog GROUP BY username, target to show how many times each user has hit each target. I would like to find out what each users' most popular target is. In other words, for each user, I ...more >>

Hi! let's say that I have a table that represent files: CREATE TABLE DocumentFiles ( name ntext not null, size bigint not null constraint filesize_check check(size >= 0), lastmodif datetime not null default GetUTCDate() ) Now I cannot use the name of the file as a primary key. But I wa...more >>

Hi, in SQL there is a term that's bothering me: EXISTS (SELECT ... FROM I guess generations of SQL programmers put some brains into thinking of some expression to put after the "SELECT" term. I usually use a NULL, like EXISTS (SELECT NULL FROM I tend to believe this term is as re...more >>

A user logs into the SQL-server via SQL-login. When inserting a record into a given table a trigger is fired. This trigger calls a vb-script on the C-drive of the server. However, the calling of the script fails because missing persmission. How do I grant a SQL-login permission to run a scri...more >>

Hi Freinds, SQL 2000 SP3 I am running the following : ----------------------------------------------------------------------- DECLARE curMove_no CURSOR FOR SELECT top 10 lm.move_no FROM lib_movement lm with (nolock) left outer JOIN lib_shelf ls with (nolock) ON lm.shelf = ls.shelf WHERE m...more >>

Windows based server farm, SQL 2003, client side is W2k, vb6, Access 97...(i cant upgrade now,..legacy issues), all db access is done thru daoobject. Program walks thru the database object on the server farm table by table. It checks each table for new data, if present it then executes an in...more >>

I'm want to remove duplicate rows from a table by reading from a temp table and inserting the records into another table. However, I continue to get the followingerror: Server: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near 'bkrdiscoverdetail'. My SQL statement is ...more >>

Hello, I have been trying to figure out how to user Row Level locking feature in SQL Server 2005. Here is the scenario. 1.Open a new query window in SQL Server Management sudio. 2.Execute a Begin transaction and an Update statement that updates a row in a table. Do not commit the transacti...more >>

I have the following table structure: email A B abc@acme.com apples product1 abc@acme.com apples product2 frank@acme.com apples product5 abc@acme.com apples product3 And would like to ...more >>

using: Access.adp; Report usage: Campus Lab usage: how long has a student been in the lab I can get: daily hours and minutes on the report with: =HoursAndMinutes([CheckOut]-[CheckIn]) HoursAndMinutes function from: http://tc5.iponet.net/en-us/assistance/HA0111021...more >>

Dear fellows, I was wondering how it was for rename a database? Thanks a lot for any input, Enric...more >>

I am normalizing a sales header file for a cross tab report. I want to make a column that will describe the total in the next column. Sales, Shipping, Tax In my select statement I define the text as a column like this: 'Sales' as Type, Sum(Sales) Amount but my group by won't take eithe...more >>

I need your help in MS Access… There are 2 tables Table A with 50 records and Table B with 5 records (similar records), I want to delete the 5 records of table B from Table A so that in the end Table A should have 45 records (assuming all 5 records of Table B are in Table A)… Please ...more >>

I have an OLE automation object that I am instantiating inside of a stored proc using sp_OACreate 'myObject', @object OUT. The stored proc is called multiple times, each time instantiating a new instance of the object (and then destroying it when it is finished). Is it possible in SQL Ser...more >>

Hi, I am writing a Visual Studio.NET client server app that will reside on one PC, the GUI, Business Logic and Database. I am trying to decide which database to use, either SQL Server/ Express or use Access with JET engine. The database on this PC will be also be accessed by remote PC's t...more >>

HELP! This is driving me mad.... Greetings all. I am trying to import XML into SQL. I have the following ingredients: 1 X xml document with 2 levels. Parent > child 1 X xml source object 2 X tables in SQL. The XML doc is quite simple. Parent element (PLU) and a chi...more >>

Hi, I use a stored proc to load XML data into tables. The stored proc takes as input the XML as a text parameter: spLoadXML (@XMLText text) I have a table "tblXMLContent" with a column of text type containing the XML text. How do I pass the content of the column to the stored proc? T...more >>

I am building a .NET app that uses sqlDataReader.GetSchemaTable method. this returns a table which includes the column "Provider Type" which is the database data type of the column, however it is a numeric value, are the mappings between these numeric values and the textual data type names d...more >>

I am having a problem with a query statement when I try to use sum( ) on a field in the query. For example: select somast.fsono, somast.forderdate, somast.fcompany, sorels.fduedate, sum(fnetprice) from somast inner join sorels on somast.fsono=sorels.fsono where somast.fstatus<>'CANCELLED' ...more >>

I know how it functions, but what does this mean exactly, and where can I find it in the books online? SELECT * FROM Table1, Table2 What is the name of that Comma's function? Thanks, Jeremy ...more >>

Hi All, this is my first time posting here as i cannot find the answer myself. I have couple tables i want to join and i can't seem to get it right. I have the following tables: Part: (Part ID), PartDescription Part_warehouse: ( WarehouseID), (Part_ID), Available_QTY Inventory_Trans: (Tra...more >>

I am running SQL 2000. I have several procedures where I do general validation on entered values. User procedures typically call these "checkprocedures" to verify all entered values. I thought I grasped the idea of error handling but must have done something wrong. When a wrong value is fo...more >>

There are 2 tables - say table 1 - child table - say child table 2 - parent table - say parent Assume that each child only resides on a single parent and each parent may contain multiple children..... Can we write a query for finding how many children are on each and every parent...??? ...more >>

Hi I have two tables (in an third party application, I cannot change the data structure) as follows:- T1 - Main data Amount Ref1 Ref2 Ref3 ============================== 100 A A A 150 A B A 200 A B B T2 - Reference data...more >>

There are 2 tables - say table 1 - child table - say child table 2 - parent table - say parent Assume that each child only resides on a single parent and each parent may contain multiple children..... Can we write a query for finding how many children are on each and every parent...??? ...more >>

There are 2 tables - say table 1 - child table - say child table 2 - parent table - say parent Assume that each child only resides on a single parent and each parent may contain multiple children..... Can we write a query for finding how many children are on each and every parent...??? ...more >>

I have a web site running on IIS 6.0 that is full of asp.net 2.0 pages. Each of the aspx pages contains a gridview control that uses an SQL view as its data source. The SQL views associated with the various aspx pages are all related to the same SQL table. The SQL Server is located on the ...more >>

There are 2 tables - say table 1 - child - gid table 2 - parent - cid Assume that each child only resides on a single parent and each parent may contain multiple children..... Can we write a query for finding jow many children are on each and every parent...??? ...more >>

SQL Server 2000 SP4 When using a cursor together with a SELECT which uses an IN, the cursor performs very badly. The sample code below is an adaptation of something which is happening in one of our applications. I can easily change the IN to a regular JOIN, but it doesn't seem right that ...more >>

I am currently running a DTS package to extract data from a DB2 database. The code reads Select * from ABC where entrydate='12/15/2005' This works fine but I need to automate the process by selecting the date automatically. As soon as the entrydate = formula the extract do not work. I ha...more >>

In certain applications, it would be useful to be able to display to an 'ordinary' user, a list of other accessors of a particular database. Any ideas how this can be done? -- John Austin...more >>

Hi everybody, I've an application written in Visual C++ 2003 and I'm using ODBC to connect to SQL Server 2000. My application runs smoothly under SQL 2000. When i tried to test my app on SQL Server 2005, I encountered an error while executing stored procedures. The error appears after e...more >>

HI, I have a table with IDENTITY column with the datatype as INTEGER. Now this table record count is almost reaching its limt. that is total record count is almost near to 2^31-1. It will reach the limit with in another one or two months. In order to avoid the arithmentic overflow error 8...more >>

i've read some posts regarding this but am not sure if its suitable for me. i am implementing an audit table that records the time and user who performed a delete operation. this is done via a trigger. is it possible to set the a unique variable (that identifies the executor) within the ...more >>

hi guys, Is this possible to store data in excel file through select statment. for example if i write 'select au_lname from authours' in query analyzer, it should store all au_lname in excel file,somewhere in hard disk or location like server....more >>

Dear gurus, We wish a good migration, faster and the less traumatic possible. So that after five years with the same logical configuration, hardware and so on the main point is to improve considerably the availability and drop once for all our bottlenecks. We’ve got an A-A cluster run...more >>

I like the little sql 2000 tray icon to see/start/stop services. Doesn't sql 2005 have a similar tray icon?...more >>

hi, i need to calculate the differennce of two subquerys in another row, like: plus minus diff ------------------------- 10 3 7 is there a way instead of doing the subquery twice, set some variables to use them to calculate the sum? it can't be a stored procedure or fun...more >>