I am trying to create a stored procedure in SQL Server 2000 that updates multiple rows in a database table. I have two web pages. On the first page I use a form to submit multiple rows of data to a database table. On the second page I receive that form data and excute a stored procedure. It wo...more >>

Hi, I have a StudentMark table (StudentID int , SubjectID int, Mark int, Semester tinyint) The pass criteria for each student is given as follow 1. He should pass in all the 6 subjects (pass mark = 40) 2. Condition 1 + 1. Avg of any 4 subjects is 40% + Avg any 3 70% or 2. Avg of ...more >>

Hi, I have an employee table having fields empid and managerid. I need a query which returns all the children once the parentid is given. Here is the script create table employee (empid int , parentID int ) GO insert into employee select 1,null insert into employee select 2,1 insert into...more >>

hi Guys! I am having a table customer { Name varchar(255), step int, details varchar(255) } I am trying to get the list of customer name with GROUP BY, and order it using ORDER BY SELECT Name FROM customer GROUP BY Name ORDER BY step but cause of ORDER BY clause it fa...more >>

Hi, I'm new to SQL programming and SQL 2000 server. My problem is that I have a table that contains antenna information. (Their can be different types of antenna's) I have another table that points to the antenna table twice, because it needs two different types of antennas. IE: GPS and ra...more >>

Is it possible to create a constraint where the uniqueness is defined by 3 columns (2 VarChar, 1 Int), with the Int column having a value of 0? Thanks. ...more >>

I've moved on from my earlier question on "max rows", having figured out an approach, but it's not doing what I expect. Below is my table definition. CREATE TABLE [dbo].[Recycles] ( [recycleID] [int] IDENTITY (1, 1) NOT NULL , [origEndOffice] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS...more >>

i want to generate a column in the resultset that works like an identity column. one way i thought is creating a temp table with an identity column and inserting resultset to that table and select again. is there any other way that works serverside? thanks... ...more >>

Hi I was told to write an "application" that sends emails with some data stored in database to our company's departments. I'm trying to do this using stored procedures and DTS. Problem is that every file must have differrent name (depertment's ID is a part of it). So, I considered doing...more >>

Hello All I am an Oracle DBA currently learning SQL Server. I have downloaded SQL Server Express 2005 and installed it including Northwind and pubs databases. This worked just fine. Got the Server Management Studio Express CTP and installed it and that too works just fine - on the same machin...more >>

how do i rewrite the following using the two-phase protocol and produce a schedule that is serializable? Transaction T22 sum:=0 Read(A) sum:=sum+A Read(B) sum:=sum+B Show(sum) Transaction T23 sum:=0 Read(A) A:=A-100 Write(A) sum:=sum+A Read(B) B:=B+100 Write(B) sum:=Sum+B Sho...more >>

If the Access field is a "Yes/No" field, in Sql Server to what Data Type should I convert it to ? Thanks. ...more >>

show me how the backward error recovery technique is applied to a DBMS that uses the update-in-place scheme to recover from a system crash with minimum loss of processing? thank u....more >>

what modifications have to b made to a recovery scheme if the transactions are nested? thank you....more >>

if I invoke this stored proc and bind the output param @ReturnState to a VisualBasic app, I get the newly generated identity value. But running the script on the bottom, the @result variable is NULL. What am I missing ? --- create table Test111 ( scode int IDENTITY NOT NULL, sdesc varc...more >>

Is there a way for me to programmatically create a job that can be run by SQL Agent ? What I need to is to programmatically do what one would using Enterprise Manager to create a SQL Agent job. Thanks, RM. ...more >>

I have a SQL UNION (I know I know....they're bad, but trust me, I have to use it) in a stored proc. Effectively, it liiks like this: SELECT f1, f2, f3 FROM T1 UNION SELECT f1, f2, f3 FROM T2 note: T1 and T2 are not tables, but parameterized query expressions that contain 3 and 4 t...more >>

Hi, I have to change ownership of all the tables/storedprocedures/views in a database. Can some tell me a good and easy way to do it. Thnx in advance....more >>

Why the "top 100 percent"? Or am I just retarded? CREATE VIEW dbo.SmdsProductList AS SELECT TOP 100 PERCENT {whole bunch of columns and expressions} FROM {whole bunch of joins} WHERE ThisColumn = 1 AND ThatColumn IS NOT NULL ORDER BY SomeOtherThing -- Peace & happy co...more >>

I am looking for a great SQL Server Developer position in the Charlotte area and I am looking for any good leads for where to look for qualified candidates. I would greatly appreciate any help that anyone could offer but I thought his would be the best place to ask for help. Thank you very muc...more >>

I'm having trouble developing a query that will group on a given set of columns and, for each group, will also display the other columns in the "max row" for that group. Let me illustrate what I mean: CREATE TABLE [dbo].[GroupTest] ( [testID] [int] IDENTITY (1, 1) NOT NULL , [office] [cha...more >>

I can't find any examples on how to do the below. Can anyone advise? I have an 'Address' table which has a primary key constructed from 'postcode' and 'house no'. I have numerous other tables that reference this table with a foreign key - how do I reference a composite foreign key such as t...more >>

Hi, need some help... The problem I face is that the last column in the select list which is [Amount2] returns the sum of all users sum so each row in the returned result has the same number. The [Amount1] column returns the correct sum per each user due to the grouping. How shall I do...more >>

hi guys, a few questions regarding system table on sql 2000. I think I need to rebuild indexes for msdb..sysdtspackages table. after I do indexdefrag, the density is still 52%. So i'm thinking to do "CREATE INDEX ... WITH DROP_EXISTING" on it, but I can't generate index script fr...more >>

I am having issues getting a SqlDependency proof of concept app to work. I took a step back and performed the MSDN lab titled SQL Server and ADO.NET (LabB), located here: http://msdn.microsoft.com/vstudio/tryit/hosted/sql/default.aspx. It contains an example using SqlDependency to monitor ...more >>

Hi all! I'm trying to create a text file of data from a MS-SQL table. I'm using the utility at the end of this post. The problem I'm having is the output file is wrapping text after 80 chars (I think). I need the record written on one line completely. Finding ISQL tutorial help on the we...more >>

Guys I am trying to demoralize the source column in one of my tables so I can identify the combination of sources that the email was recruited from. I know I can do this with a cursor but I would really prefer taking a set based approach. My data table looks something like #data_table and I...more >>

I wrote a program that uses embedded SQL to make rows in a table with more than 100 fields. Each row type only uses a few fields, the rest are blank. I used the form "insert ... (fieldname1, fieldname12, fieldname44) values (val1, val12, val44)", with different numbers of values each time de...more >>

I have a site that calls a stored procedure to populate data on an ASP page. 50% of the time when I access this page I get a timeout error. I looked in the Event Viewer and noticed MSSQLSERVER errors with this description: Error: 50001, Severity: 16, State: 1 Blocking Threshold Exceeded, ...more >>

Hi All, Well... I got the xml file to work with OpenXML, but as you see on my xml file I have different structure for all data and I want to get part of it but store in one row on my table. Here is sample of xml structure <?xml version=""1.0""?> <FieldTitle1> <Request> <Reference...more >>

I'm trying to insert xx identical records with 1 statement. Basically, I want to avoid the following if I know I want to insert 6 records: INSERT INTO myTable(Field1,Field1) VALUES(1,2); INSERT INTO myTable(Field1,Field1) VALUES(1,2); INSERT INTO myTable(Field1,Field1) VALUES(1,2); INSERT I...more >>

Hello, I need to extract some data from an un-normalized database. The structure is basically: ID, Address, City, State, Zip. I want the distinct (address, city, state, zip) combos with one and only one ID (doesn't matter which one). Sample table values: 1, 123 Main St, Anywhere, IL, 666...more >>

Does anybody know of some good articles or whitepapers on when to use stored procs and when not to use them? Thanks in advance. Dave ...more >>

How could I store a value given to me as a string as a duration of minutes, seconds? As opposed to a datetime? Say I'm given the string...00:04:08 But I don't want to, if it can be helped, store as: 1/1/1900 00:04:08 Make sense? I want to store as a duration of time, not an actual time......more >>

Here's a table that's PK'd on "SurveyKey" and "ProductKey" Both columns are NULL. From top to bottom. Some developer [not me] must have done this. Just makes you wanna say, "GRBNF!!!!" -- Peace & happy computing, Mike Labosh, MCSD "When you kill a man, you're a murderer. Kill...more >>

I'm using this query: select count(*) / 25793 * 100 thecount, promocode from [December-Fixed2005v2] GROUP BY PromoCode order by thecount 'TheCount' comes back as all 0's which isn't right as without the divide/multiply bit it comes back with 45, 127 etc. How can I fix this? Do I need to cas...more >>

just noticed this, wonder if there is a performance hit got results when executing the following select AgendaID from ObjectiveAgenda Inner Join Objective On Objective.ObjectiveID = ObjectiveAgenda.ObjectiveID problem is that the ObjectiveID column in one table is of SQL type INTEGER, w...more >>

Hello, Can we do table partitioning with SQL Server 2005 Express?? I post this question, because I saw in features comparisons tables for each edition, that was only possible with Entreprise edition.. at this url: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx T...more >>

I have five SQL jobs that I want to run at the same time to save time. Running them in serialization costs me lot of time so running in parallel is choice I have. However, they all need to run after 5 DBs are restored. So, I created one job that does all the restore for these 5 DBs one after a...more >>

Just learning SQL from a "learn in a weekend" book. Working on Stored Procedures. Wrote procedure exactly as in book to "Add New Customers". That went well BUT when I put in the EXECUTE part after-calling on the stored procedure, all the data requires " (Quotes) instead of ' around them or el...more >>

How should I resolve the following error message? Thank you. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.D...more >>

Hi NG, I have a question about a query like this: I have a table X with an ID and a varchar-value like this: ID | myString 1 -12-33-177-44- 2 - 76-12-6-888-34- 3 - 19-34-65-111-9-212- As you see, the myString value is a set of INT values, divided by a "-". What I...more >>

Have two tables: [CODE] CREATE TABLE [Table1] ( [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [Number] [varchar] (50) NOT NULL , [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp] DEFAULT (getdate()), CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTER...more >>

Hi I'm trying to improve performance of this query: select fk_serverid, count( distinct fk_metricid ) from sdb_metrics SDBM inner join LU_SERVER LUS on LUS.pk_serverid = SDBM.fk_serverid inner join LU_SERVERNAME LUSN on LUSN.pk_servernameid = LUS.fk_servernameid group by fk_serverid ...more >>

Hi, I'm not that experienced with this so I need some help :) I've got 2 tables a Header and a Line table Now I want to select all headers but I want to filter out those that have lines with the Status Finished So I came up with this query: SELECT * FROM Header LEFT JOIN Line ON (Head...more >>

hi friends, my table structure is create table sample1(ref_no varchar(50),Date1 varchar(10),time1 varchar(10)) records are like insert into sample1 values('sdfsdf','12/12/2004','03:02:02') insert into sample1 values('sdfsdf','12/12/2004','02:02:02') now i want to retrieve the latest ...more >>

I am using BULK INSERT to load data from some CSV-file into SQL-server 7.0. The files use TAB to seperate fields and NEWLINE to seperate rows. However - we would like to be able to have these characters in the fields themselves, e.g. a TAB or NEWLINE in a field which contain free-text from ...more >>

Hi, I seem to face a problem getting the right files from an FTP-server. I do need to logon to the ftp site, go into a sub-directory and transfer the files in that directory. However the transfer is always carried out one the main directory eventhough I have explicit defined that it is the ...more >>

Hi Everyone, I've read a lot about a problem I'm experiencing but I'm still very confused. I have a winxp vbscript that inserts username, machine name, ip address and current date into an SQL table. The code is: strSQL = "INSERT INTO users VALUES ('" & strUserName & "', '" &strComputerNa...more >>

Select Num,dbo.Total(Num) As Total_Salary ,dbo.Tax(Total_Salary) From Test Total is Function to Calculate Base Salary + Some in come Tax is Function To Calculate Tax From Function Total plz Help Me ...more >>

we can get directory information by EnumDirectories method of sql server object from a remote sql server, but how can I get the files under a special directory on a remote sql server? I tried finding result in MSDN, but I can't get it. Any one can help me? Thanks in advance. Bill White ...more >>

Hi all, I have a script that will ALTER TABLE and ADD a Timestamp column to all tables in the database. In SQL2000 this works fine. The column does not all nulls and by default, SQL2000 add the current timestamp record. Now - In SQL 6.5.... YES!, I said SQL 6.5 :) - this behaviour doesn't...more >>

i want to make a procedure which accept loginname and will use this paramtere to grant execess to some objects like: GRANT EXEC ON sp_OACreate TO @BMS_DEV @BMS_DEV will be parameter passed to the sp... how ca i do this ...more >>