I am not good at SQL - hence here goes Consider this scenario of 2 tables X and Y with a many to many relationship Table X (name,weightage) X1 2 X2 1 X3 5 X4 1 Table Y (name ,attrib) Y1 attrib1 Y2 attrib2 Y3 attrib3 Y4 attrib4 Relationship table Z (id,id) X1 Y1 X1 Y2 X2 Y1 X...more >>

Hello, I am trying to write a query and getting a little confused. My problem: I send enquiries to partners from 12:00am to 11:59pm each day. I have ten partners, and each partner has a different daily limit. In my partner table (Table1), I have a columnm for the Daily Enquiry Limit calle...more >>

Hey all, i have a students_table which i want to do a multiple update on. Update student_table set grant = 35000 where course_id = 2 There are lots of students in the student table which have course_id of 2. I get this error: Subquery returned more than 1 value. This is not permitted...more >>

Hi Guys I don't want to keep asking for your help all the time on each individual query, so could you please help me to break the myths on the following: 1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET. ACCOUNTS includes all of the usual details for customers and sup...more >>

Hi All! i have 2 tables TABLE1 ------ DESC CODE SEVERITY CODE is the key here. (SEVERITY ranges from 0 to 7) TABLE2 ------ CODE1 CODE2 CODE3 CODE4 CUST_ID CALL_NBR CUST_ID and CALL_NBR form the key. CODE1 to CODE4 reference CODE in TABLE1 There can be at max 2 CALL_NBR...more >>

I currently have a date column that draws from the now() function in my asp script. The date looks like this: 2/5/2004 10:09:52 AM As you can see, there is the day and the time data all in the same string. What I would like to do is run a group by clause that groups all the dates that ar...more >>

I need a script that will delete the first 100000 rows of a table. Is this possible?...more >>

Hi, I have a table that contains log data, usually around a million records. The table has about 10 columns with various attributes of the logged data, nothing special. We're using SQL Server 2000. Some of the columns (for example "category") have duplicate values throughout the records. We...more >>

Hi all, who knows any free tool similar to Enterprise Manager (if exist) ? TIA, Marek Powichrowski ...more >>

I'm composing a comparison table of database capabilities of several different products. I've read the Transact-SQL reference and have gathered most of the information needed for SQL Server 2000 (MSSQL), but there are some things that I'm not very sure of. Could someone confirm or complete the p...more >>

Is it possible to add a column to a table using the "alter table" statement and specify where in the sequence of columns the new column sits. If not is there any way to alter the order of columns using TSQL rather than Enterprise Manager / Design Table. TIA Laurence Breeze...more >>

SQL SERVER 2000 Hi all This is my first attempt at writing a stored procedure. I have managed to get it working but its unlikely to be the best way of handling the problem. While writing it I found some things that I don't understand so if any one could shed any light it would be much appre...more >>

I'm trying to do multiple insert statements. The table looks like this: CREATE TABLE $table (CNTY_CNTRY_CD char(3),ST char(2), CNTY_CNTRY_DESCR varchar(50),CNTY_CNTRY_IND char(1), HOME_CNTRY_IND char(1), CONSTRAINT cnty_key PRIMARY KEY (CNTY_CNTRY_CD, ST)) I'm ...more >>

Hi, I having problems returning only the first row of data for each GROUP for the query below. Can someone please help? Thanks. SELECT bid, xact_date, xact_time FROM badge_history bh GROUP BY bid, xact_date, xact_time ORDER BY bid, xact_date DESC, xact_time DESC So if the data r...more >>

Hello, I have a large set of data that I have set up as a partitioned view. The view is partitioned by a datetime column and the individual tables each represent one month's worth of data. I need to keep at least two year's worth of data at all times, but after two years I can archive the dat...more >>

Hi! How I can know the "Country version" of SQL Server using VB.NET (perhaps with the SQLDMO)? I mean: if the SQL Server is in "English version" or in "Italian version" or ... Thank you so much!! Bye...more >>

We would like to create a audit table for storing information if any objects like tables / stored procedures are dropped. Table would also contain information about time and the user name who has dropped the object. Any help would be great. Thanks in advance. Kamal...more >>

Hi I need create, write and read text files in a procedure transac sql from sql server 2000 How i do this ?? Thanks,...more >>

Hi! I need how to pass variables struct witt n fields and n records to procedures in transac sql in one bbdd sql server 2000, this parameters are in/out. Thanks....more >>

Hi all Ive got a problem I was hoping someone may be able to help with. Im calling an extended stored procedure provided by a third party (master..xp_OrderHeader). This xp requires 3 inputs and is supposed to provie one output. When I call it in SQL Query Analyser all runs OK and I get a...more >>

Andy, I am new to SQL. It was hard just to figure out how to install it. I know I am logged in as a Admin. in Windows. I have the little server box with the round circle on it in the bottom right corner(toolbar). The SQL Server Service Manager. I tried to search for the sp_helpsrvrol...more >>

I did this thing with host_name(). I'm hoping someone can tell me if I'm gonna get into trouble with this scheme before it's too late... I have maybe 75 users. They all call on a table of appointments (many thousands) and I dump the ones being viewed into a temp table named TaskTEMP. I am usi...more >>

Hi everyone, I need to create a procedure that would take the two parameters @FieldName and @FieldValue and replaces it in my SQL statement without using Exec because Exec can take at most 128 characters. CREATE PROCEDURE Get_Person_By_Field @FieldName varchar(20), @FieldValue varchar(100)...more >>

Hi , Is there a way/tool in Sql Server 2000 SP3 to monitor all activities going on in the Database ? For example, I first create an empty database. Then I have an ERWIN generated DDL to create all views and tables. After that, I have INSERT scripts that populate all the base tables. What I...more >>

I have a relatively simple SQL Server 2000 database which I am querying from a VB.NET application. The query is very simple, such as SELECT * FROM tblSystems. Let's say there are 10 records in the table tblSystems. I expect to get 10 records from this query, and sometimes do. But for some re...more >>

We use "FOR XML EXPLICIT" and "OPENXML" heavily on our SQL 2000 Advanced server. Every now and then we get an "Undeclared Tag ID" error (SQL Error #6806). If I open the stored procedure, modify it in any way (add a carriage return, hit the space key) and save it, the error goes away. To my knowle...more >>

I am trying to create a job that, as one of its steps, will kick off a DTS package. As part of the command parameter, I need to concat a system variable (@@SERVERNAME) to a constant string. I am receiving an error about incorrect syntax near the +. Here is the code for the job step. -- Ad...more >>

I am using SQL Server 2000. I need to query my database for all the contracts that came in during a certain time frame (user is prompted for reportingperiodid). Table - Periods Fields - Reporting Period id int Reporting Period desc varchar(30) Reporting Period Begin Date ...more >>

Code: ALTER PROCEDURE GetBookedResource ( @StartDate datetime, @EndDate datetime, @Resource char(30) ) AS SELECT * FROM tblBookings WHERE StartDate >= @StartDate and EndDate <= @EndDate and Resource=@Resource _______________________________________________...more >>

Hello, There is a program which performs some scripted actions via ODBC on tables in some database on mssql 2000. Sometimes that program tries to insert record with key that is already present in the database. The error comes up and the program stops. Is there any way to globally configure ...more >>

Hi Got a strange problem. For some reason our web client box times out occasionally. Maybe a few times a day. Nothing appears in the logs. What I do know is that the disk queue ramps up to <high>(think top of the graph), processor queue jumps up and the tps drops to 0 (naturally enou...more >>

Hi all, I am doing some large updates, that may update 10,000 plus rows. This works fine when I execute the SQL directly in Query Analyzer. If I set the timeout on my VB connection to 0 (zero) the connection should not time out???? But it does. If I set the time out to a high valu...more >>

Hi Everybody, I have a complex view, that includes a "group by" clause. I'm trying to join this view with a table, in a very simple query. The problem is that the optimizer is not using the table data as input for the view (I expect this because I have arguments for the table, but not for t...more >>

What is the best way to use a table located on 1 server on another server. We have an application that needs to use data from 2 separate servers. TIA. TS...more >>

Dear All A quick question: I run a set of scheduled jobs. The jobs run Stored Procedures. However, if the sproc fails, the job quits and moves on to the next one. However, the sproc should carry on. E.g, IF find bad records, EXPORT to file, GO TO NEXT RECORD (BUT DON'T QUIT THE SPROC). The ...more >>

Hi, A DTS package which is running fine when execute ,always fails as scheduled job. I read from the previous posts about MS KB article 269074, security and MDAC, but I don't understand and don't know how to solve it. Please help. My scenerio: SERVER : MS Windows 2000 server...more >>

Hi All, I have to write a stored procedure which will send/Receive text file from/to a server by using FTP. Is anybody have done anything on it? or know about it. If yes, I would like to know about it. (also provide the stored procedure code if possible) Thanks in Adv. T.S.Negi...more >>

Looking for some insight from the professionals about how they handle row inserts. Specifically single row inserts through a stored procedure versus bulk inserts. One argument are people who say all inserts (and updates and deletions I guess) should go through stored procedures. The reasoning ...more >>

Hi All, I have a table that holds pregenerated member IDs. This table is used to assign an available member id to web site visitors who choose to register with the site So, conceptually the process has been, from the site (in ASP), to: - select the top record from the members table wher...more >>

Hi All, I'm a relatively newbie to SQL Server 2000, having come from a MySQL background. I'm creating my first Trigger statement on a table, and I'd like to know how I go about performing an update on the row that was changed when the trigger was fired. To explain, I have 2 columns, one ...more >>

Hi, I am trying to concoct a query that will join rows on the basis of a whitespace insensitive comparison. For instance if one row has the value 'a<space>b' and another has the value 'a<space><space>b' I want them to be considered equal (but not the same as 'ab') I am happy to do some T-SQ...more >>

Hello, I am new to sql*server, but have used Oracle for years. Can anyone recommend a good online guide for converting all the Oracle SQL I know to TSQL? tia, Mike...more >>

Hello, First, sorry for my bad English. Here is my problem: BoxA: XPpro + Apache + php (using php_mssql.php)+ ADOdb + MS SQL Server + My_Prog (using local MS SQL PUBS database) BoxB: NT4 + Apache + php (using php_mssql.php)+ ADOdb + My_Prog (using remote BoxA PUBS database) BoxC: ...more >>

I need to split a string in two if there are lowercase characters at the end of it. For example: 'AAPLpr' becomes 'AAPL' and 'pr' 'Ta' becomes 'T' and 'a' 'MSFT' becomes 'MSFT' and '' 'TAPA' becomes 'TAPA' and '' I am using SQL 2000....more >>

I am trying to create an outer join between two tables in a query that includes several other tables. When I double-click on the Join line, it presents three join options: 1) ONLY records from table1 and table2 where join fields are equal 2) ALL values from table1 and ONLY records from table...more >>

i have a query which goes like this:- select a.col1,b.col2,c.col2 from tab1 a,tab2 b,tab3 c where a.col1 *= b.col1 and a.col2 *= c.col2 and b.col3 = c.col3 how do I write this query in SQL 92? also can we replace *= with =* by interchanging the participating tables as in "a.col1 *=...more >>

Hi I have a problem creating a backup in SQL Server 6.5. Although, the backup produced does restore ok, I want to stop this error message appearing everytime I make a backup. The error message is: " [SQL Server ]Can't allocate space for object 'sysbackupdetail' in database 'msdb' because the...more >>

I have a table that matches up Securities and Exchanges. Individual securities can belong on multiple exchanges. One of the columns, named PrimaryExchangeFlag, indicates if a particular exchange is the primary exchange for that symbol. Each symbol can only have one primary exchange. I am tryi...more >>

Table1 has field fieldvalue1 indexed. Table2 has field fieldvalue1 indexed. This query, select fieldvalue1 from table2 where fieldvalue2 = '123456' StmtText ------------ |--Clustered Index Seek(OBJECT:([table2].[table2_fieldvalue1_i1]), SEEK:([table2].[fieldvalue1]=[@1]) ORDERED FORW...more >>

Spent couple of hours trying to figure out this issue. How can I pass a parameter in dynamic SQL? Here is a simplified example: CREATE PROCEDURE dbo.spTest @LastName varchar(25) AS exec ('SELECT * FROM SubmissionLog WHERE LastName LIKE @LastName') exec spTest 'Doe' Server: ...more >>

Is there a ready made database for benchmarking a server's performance to see what tweaks external to SQL Server will have an effect? -- A)bort, R)etry, I)nfluence with large hammer....more >>

I'm trying to query the pubs database for last title for each author. The query returns each title for each author. How can I operate on max of title? Here's what I'm trying: select distinct au_id,max(title_id)as title from titleauthor group by au_id,title_id order by au_id dito ***...more >>

Code: ALTER PROCEDURE GetBookings ( @CurrentDate datetime, @Catergory char(10) ) AS SELECT BookingId, StartDate, PeriodStart, Catergory, Staff, Resource, Class, @CurrentDate AS Expr1 FROM tblBookings WHERE StartDate <= @CurrentDate and EndDate >= @CurrentDate and Cater...more >>

Hi! I want to pass a column name, sometimes a table name, to a stored procedure, but it didn't work. I tried to define the data type as char, vachar, nchar, text, but the result were same. Any one know how to get it work? Thanks a lot!! Saiyou...more >>

Hi all I'm struggling to find an answer to this one! We've configured our SQL boxes to run under a different port number, e.g. from 1433 to 4533. This works fine under our Citrix enviornmnet where the dsn is configured to use the new port. The problem comes in terms of SQL Server manageme...more >>

I need to do something relatively simple… I need to update a table using a cursor. (I may have to create a stored procedure for doing this…) I need to declare an update cursor, fetch the cursor and update the data (and presumably close the cursor and de-allocate it… The update query is...more >>

I'm trying to create an OLAP system using SQL Server 2000 Analysis Services (AS). I want the AS cube to be based on a database with a star schema. I have a field called Ratio. The initial ratio value is based upon this formula: (CurrentTimePeriodAmount - PreviousTimePeriodAmount)/PreviousTi...more >>

I am trying to use MDX to dynamically determine which hierarchy of the time dimension is selected. I have a calculated member which is using the last non-empty descendant of the [Time].[Calendar] dimension, but I want it to work even if Fiscal is selected instead of Calendar. Here is the code ...more >>

Hai , I am in the situation to find the p.key field from a table.So i use sp_pkeys 'table' .It works nice. But i want to select the pkey field alone instead of the rest of the informations supplied by the sp. How to get the P.key field name alone from a table .Or If u tell the way to...more >>

Hallo! I have small or big problem. I want creat Store Procedurs whit limit rows. For example I need rows from 100 to 200. --Select * from table How I doing best way....more >>

Hi All, I have a server that contains sqlserver 2000 and ms access 2000. Everything were working fine when i ran my adp file in the server side, and i faced some problem when i ran it on the client computer. After checking my adp file, i found all of my stored procedures added by comma and ...more >>

I'm quite new to DTS, and have to modify a number of existing local DTS packages as part of an ongoing development project. Naturally, I'm doing these on a test server first, but so far I've failed to find any utility which will simply copy the package definition from the test server to the liv...more >>

Hi, usually, I'm not using MS servers, but I have a big problem with a Access table. I should create a web application for a Historical Dipartment. They have create a populated a Access database using unicode compression field (for ancient language). I would like to export this table int...more >>

I am running a SQL Server 2000 installation with several databases. Each database and log is backed-up using a maintenance plan. The scheduled maintance plan for the latest database does not run, but displays no error. There is no entry in the job history. The same thing happens when I try to ...more >>

I have a Session table: Username Type ------------------ bob private bob private sally private bob duet And I have an event table Username Date Type --------------------------- bob 1/1/04 private bob 1/2/04 private bob 1/3...more >>

Hi I want to connect with Magic 9.4 to the northwind table. I get always this error: Error during the registration user SA. A cause: No familiar SQL server connection assigned. How can I create a trusted server connection ? Thanks. Volker ...more >>

Dear Friends, I need the time between the busy_end_time and the next busy_start_time. oracle has a 'Lead' function which can easily address this issue. create table t ( bst date, bet date ); insert into t values ( to_date( '2004-01-15 12:00:00','yyyy-mm-dd hh24:mi:ss'), to_date( '20...more >>

Hi All I was wondering if there is a way to call a stored procedure from inside another stored procedure. So for example my first procedure will call a second stored procedure which when executed will return one record and i want to use this data in the calling stored procedure. Is this possib...more >>

Hello, What are the default login/password to access pubs and northwind databases ? I remember the login = "sa", but nothing about the password. Thanks Eric ...more >>

hi all ms sql specialists..... i am a newbie on ms sql but i am used to oracle and toad ... and now a question (i guess this was asked xxx times befor but i couldn't find the answer easilly) : is there something powerfull like toad on oracle for mssql server? (not enterprise manager)...more >>

This UNION query is very slow. With only 3,000 records in the Parent table and 7,000 records in the Child table, it takes about 60 seconds to run and returns about 2200 records. Any ideas on speeding it up? Thanks. -- PART 1: HAS NO CHILD RECORDS SELECT P.PROJECT_ID, 'No Child Data' A...more >>

I am having conceptual trouble with the following query: select r.ServiceID,r.ContractID,sum(Total) from ( select csc.ServiceID,c.ContractID, sum(csc.ContainerMovement) as Total from iwms_tbl_CustomerSiteContainers csc, iwms_tbl_ContractLines cl, iwms_tbl_Contracts c, iwms_tbl_ContractLi...more >>

Perhaps someone can settle an arguement for me ? I have a set of data that I need to group together. SQL Script below. CREATE TABLE [dbo].[CommTransactions] ( [ID] [id_type] NOT NULL , [TransactionID] [id_type] NULL , [ClientID] [id_type] NULL , [AccountCode] [varchar] (10) NULL , [A...more >>

I'm using Access as the front end to SQL2000. I have a table of contacts. UniqueID is the PK. There's also a column named "CreatedBy" int and a column Category smallint. They're all set to no nulls. If I create an index on Category, an SP that searches the CreatedBy column fails and I get an er...more >>

I am using java applications to connect to my MS SQL Server database. I am using ODBC for database connectivity. If i am running my java applications in a remote PC and try to connect to database server, unless i login to the serer, i am unable to connect to database. If a login to database ser...more >>

I have a table similar to the northwind products table. The difference is that I have products that may be supplied by multiple supplier. (i.e. there may be two or more rows that have the same product name, with different supplier,price,etc.) I need the following queries: 1. Show rows of all ...more >>

Hi is it possible to attach a database when a log file is missing? My database had one .MDF file and 2 .LDFs. I lost one of the .LDFs. Is it possible to recover any data? Tegie ...more >>

First of all I am not familiar with SQL so bear with me. I have a field called stock_code, the data in that field, looks like this 000000851. I need to replace only the first two charachters '00' with 'DR'. If I use the replace function, it will of course replace every occurance of 00 whic...more >>

Hi All, I had two Window NT 4.0 Server, say A and B... We recently upgraded from MS SQL Server 6.5 to MS SQL Server 2000 on Computer A, and for Computer B, I installed SQL Server 2000 from scratch. These two have a same RAM(512MB) and CPU speed...so the setup for hardware is almost identical...more >>

Fellas!! This is a very complicated one and it took me a few days to figure out exactly what's going on, but here's the final story: I have a production environment running on .NET with a SQL Server (2000, SP3). The SQL Server is on a dedicated Proliant computer with 2GB RAM (the actual SQL...more >>