Hi all, In SQL Server we have concept of Pages. Page size is specified to be of 8k i.e., 8192 bytes. That consists of following: Page Header - 96 bytes Data Rows And Row offsets Now SQL Server online documentation says maximum size of one data row can be 8060 bytes. If that is the ...more >>

I really hope I'm wrong about this - I can't find any way to issue a SOAP call from SQL Server 2000. I've looked at the BOL, Google, MSDN, and the SQLXML documentation and samples... There is quite a bit of discussion about how to EXPOSE SQL Server 2000 as a SOAP service, or even exposing stor...more >>

For any of those know about SQL and MS SQL Server I need your help... below is some code out of an ASP script I have: <% If Request("cmd") = "rec_history" then strSQL = "SELECT orders.ordID, orders.ordName, payprovider.payProvName, orders.ordAuthNumber, orders.ordDate, orders.ordEmail, Last(s...more >>

Is it possible to increase an int value in SQL without having to read the current value first. I know I could reade the current value, add 1 to it and UPDATE with the new value but this involves multiple queries to the DB. Is it possible to just increment the value through SQL? Thanks, A...more >>

Hello all I got a good one for all of you SQL server masters. After installing SQL Server 2000 Developer version on laptop, runing Win XP, it ran fine for a few days. Suddenly, one day I boot and the SQL Service Manager, when double clicked on shows NOTHING in the Server field nor in th...more >>

Erland: I am going to have a very similar problem. I was under the, probably mistaken, impression, from reading BOL, that SELECT PIVOT was going to be the answer. Your response to Edward indicates I'm wrong. Please clarify when we can use PIVOT then. Many thanks. John Webb john-webb@sbcg...more >>

In the way of resolving my customer's problem, I am trying to get his complete sql server database so that I can debug a problem. I tried the following way: 1. Received the table and stored procedure scripts and data in text file 2. Create the table,sp and imported the data using the wizard...more >>

Greetings, I have encountered a problem which is starting to stretch the limits of my current abilities in an area of TSQL and have decided to post it here with a request for assistance. Essentially over time if it is possible I need to translate a fair number of maple algorithms (see below...more >>

Basically i need to know if its a good or bad idea and why.. im running a server with sql server with a friend and someone wants to run borland interbase of it.. we stand to make some money which is why im thinking about it, but im worried it'll be too much for the machine.. also ive heard some ...more >>

By using Access as a front end to SQL server, I finally came up with a statement that works (I think!?), but it doesn't seem elegant. Can anyone help with a better one? --Brent ====================================== Table1 AccountNumber Shares CUSIP Table2 AccountNumb...more >>

Dear Experts, I have posted this to a more xml oriented group, but there was no answer so-fr. Maybe just because of the fact that sql server is involved. So here I try it the other way around Please have a look to the following. I'm trying to include results from server ( SQL2000 )created b...more >>

We are having problems trying to restore a Veritas backup to a different partition. We have tried changing the options under the SQL tab and Redirection tab, but we either get a Access Denied error on the new partition or it restores to the original locations....more >>

This is a great group! Many thanks to the MVPs and all contributors! I have an extended stored procedure on SQL Server 7 (a C++ DLL) that I am migrating to SQL Server 2000. The extended sproc calls the SoapToolkit 2.0 sp2. This works great on SQL Server 7, but fails on SQL Server 2000. The cal...more >>

Hi! Can anyone tell me if there's a simple way to store doc and xls files in MS SQL. I've read somethig about BLOB's. The problem is I want to edit them and then save back to the DB. Env. - MSSQL + A2K + WINXP TIA :] Regards, Leon! ...more >>

Hi, can anyone tell my why I'm getting a 'syntax error near INNER? UPDATE [T-cards] INNER JOIN dbo_Table2 ON [T-cards].[T-card number] = dbo_Table2.[T-card] SET [T-cards].[Cancel code] = [dbo_Table2].[Code]; Thanks, Lap ...more >>

Hi All, I have just read the book ("Troubleshooting SQL" by Forrest Houlette) about the importance of primary key (PK) in a table. Forrest says: "Duplicate rows can be fiendish. You can encounter situations in which the database will refuse to delete a duplicate because it...more >>

I apologize, I am used to using PHP and MySQL so I am a little out of what I am used to At work they asked me to help them with something and thus I am here asking for your assistance :) We have a Database that we use with SQL 2000 In this database is a very simple table UserID, EmpNu...more >>

Hello, I am stuck on what is probably a very easy select query. The query is: Select Count(*), Distinct PID, ID, Name From Table 1 Group By PID I get: | Count | PID | ID | Name | -------------------------------------------------------- | ...more >>

All right, I'm a bit embarrassed at posting this but this problem keeps coming up in my design, I just can't figure where my thinking goes wrong and I want to learn! Sorry if my explanation is complicated. And thank you to anyone with enough patience to go through this. Say I have facilities:...more >>

I'm trying to concatenate two sets of fields SELECT cCustID, cFirst_name + cLast_name AS name, cAddress1, cCity + cState + cZip_code AS city_state_zip FROM dbo.tblCustomer This works ecept that the otput has spaces inbetween the fields Is there a way inside the query to trim t...more >>

Hello, I have had to run a few database restores over the past couple of days and I've noticed that they are taking longer than I would have expected. I decided to watch the "Device Throughput Bytes/Sec" and "Backup/Restore Throughput/Sec" counters on the server for the backup device that I a...more >>

Hi, I have a table containing multiple lists of words. A simplified version of the table might look like this: NAME: WORD: List1 a List1 b List1 c List2 a List2 c I want to compare the words from the lists to say what words from List1 are not on List2. (i.e. t...more >>

I actually have 2 questions here. 1. Which method of assigning values to variables is more optimal. DECLARE @Var1 int, @Var2 int, @Var3 varchar(50), @Var4 int SET @Var1 = 0 SET @Var2 = 26 SET @Var3 = 'this is SET a varchar' SET @Var4 = (SELECT TOP 1 Var4 FROM SomeTable) ---OR--- ...more >>

Hello, I work on MSSQL2000, 120-day limited version Enterprise Edition, service packs cannot be installed on this version. I have problem with a cursor - the query that fills the cursor ends rather quickly (10-20ms), there is 90 recs inside the cursor. Following fetches are taken basing on a ...more >>

I have three tables which are one-to-many relationship for each. A --8 B ---8 C office table IDX officeName ---------- 1 AAAAA 2 BBBBB 3 CCCCC Dept table (office.idx = dept.officeid) IDX officeID code Descript --------------------------- 1 2 01 TEST01 2 ...more >>

I'm having a strange problem. I have a table, here's the structure: id int 4 0 DocTypeID int 4 0 Date datetime 8 0 URL char 255 0 RawContent text 16 1 XMLContent text 16 1 imported bit 1 1 URL_Checksum int 4 1 This table contains just over 210,000 rows. The database...more >>

Standard Customer 1..n Orders. I need to show, in a single row, all Orders associated with a Customer CUSTOMER ORDER NUMBER Smith 223 Smith 245 Smith 456 Jones 889 Jones 990 Desired result ============== Smith 223, 245, 456 Jones 889, 990 How to do ...more >>

hi, I want all the numbers in my database to have two digits behind the ',' e.g. 1 --> 1,00 1,1 --> 1,10 1,123 --> 1,23 how can I fix this, I already tried different data types in 'design table', can anybody help me with this?...more >>

Hi, I am seeing some unusual failures in a very multithreaded app that uses ADO to write into a Sql 2000 database. I'm in bad need of some expert guidance in debugging. I'm not asking for anybody to debug my code, as much as I am asking for general strategies on *how* to debug it. This C+...more >>

Fairly new here. I need to be able to setup a condition on my query where I only display the record if the time delta is greater than some arbitrary value. The time delta is calculated from the time stamp on the record. So, I would have to read the current time record and the following time re...more >>

If I have a DB contaning photo's with a date field (date of photo) shouldn't this date field be split down into its own table during 2NF? vassone@hotmail.com ...more >>

I am getting this error when attempting to open a table in query analyser. [Microsoft][ODBC SQL Server]Changed language setting to us_english I can open other tables in the db in QA without any problems. I can open the tables in question through EM, just not through QA. The tables ...more >>

Hi All, I need to run an update against the records in one table, using pointer recids from another table. So, table 1 might look like this: Recid 1 2 3 4 5 6 7 8 9 10 Table 2 might look like this: Recid 2 4 6 7 9 So, I need to be able to run an update on table 1 ...more >>

I found an sample to get the 11th to the 20th rows using ms-sql server like this: SELECT TOP 10 x.name, x.population FROM (+ SELECT TOP 20 name, population FROM cia ORDER BY population DESC) x ORDER BY x.population ASC but it is only for simple script, without join. Can anyone show ...more >>

We're an ISP using SQL 6.5. When the user connects to us, the RADIUS server queries the database to authenticate the user. This is happening all the time. However when I run a query against the database that effects a large no. of rows and runs for a minute or two, often the authentication proces...more >>

I am moving our databases from Access to SQL Server 2000. Our laptop users currently sync with our "master" copy of the data using Access' briefcase replication. When I move our data to SQL Server will I be able to sync with msde data projects on the laptops? Thanks for your help on this. ...more >>

Hi I have a database with two datafiles, located on PRIMARY file group. The .mdf has 20gb, and .ndf has 5gb. How can i determine which tables are created on .mdf and which on .ndf data file thanks alex *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in ...more >>

Does anybody out there know how to get a topological sort of the user tables in an Sql Server database? This is a list of tables in the order that data should be INSERTed into them as dictated by their foreign key relationships. For example consider the following schema: A has no dependencies ...more >>

I have 6.5 installed. I don't run a server on that machine. Rather, I use ISQL_W and SQL Enterprise Manager to work on a remote 6.5 server. I would like to install 2000 on the same machine to do some development work on a 2000 server. When I try to install 2000, I get the error "SQL Server ...more >>

can anyone tell me how to get this cursor to loop through 50 records.. it stops after one record Declare @booking int Declare booking_cursor CURSOR FOR Select Booking From GS_Book Order By Booking Create Table #temp_details ( Z_id int NOT NULL IDENTITY (1, 1) ,a_name varchar(254) ,attend...more >>

i need foreign key with no action for delete or update, but prevent to insert new rec that foreign key is not exists. u think to get it by foreign key with no action. but i see it not prevent insert. i do not understand why have option or foreign key without any affect? ...more >>

I've managed to create a query that runs well in Query Analyser and returns three random rows from a table that contains only twelve rows: SELECT TOP 3 * FROM tbl_name ORDER BY rand(1000.0 * identitycolumn * datepart(ms,current_timestamp) ) However, when I try to create a stored procedure us...more >>

Can anyone tell me if SQL Server internally time tags each row that is written to a table. If so how do I access the datatime for single row. I want to write a housekeeping program to remove rows older than a certain date. Any help is appreciated. Thanks, Kelly...more >>

Hi All, I have a single-column table with pregenerated IDs and another, multi-column, table in which I need to insert those IDs. I'm wondring if anyone can give me an idea of the syntax I need to use, particularly since I also need to supply static values for some of the columns in the dest...more >>

Hi all! Newbie question...more a result of lack of knowledge of views than anything else. Is there any way of configuring a relationship from a table to a view? We have an application that needs to see a hard constraint between a "child-table" and a "parent-view". I am 99% sure that it can'...more >>

If I have a table cfgmain that stores a month in the following integer format YYYYMM ie. 200302. How do I find what the month was seven months ago? I came up with following SQL is the the most effiecent way of this month? select substring(convert(varchar,dateadd(mm,-7,convert(datetime,str(...more >>

Is it possible to create a view in SQL Server which incorporates tables from an Oracle database? It can be a ODBC connection, I don't care much about speed etc. Just is it possible? :) Any tips/suggestions/links to more information etc. would be great! Thanks. Sincerely, Nils Magnus Engl...more >>

My first question is; is it possible? If it is, does anyone bother to give some guidelines on how to implement this in my function below. This function convert a 8 digit integer to date. But sometimes the InputDate is an integer value that can't be converted to date, example 20043215. If this is...more >>

Can anyone give me an example of this? I want to display the sum in a label or textbox in visual basic 6. Thanks, Kelly Mayo...more >>

I use MS Visual C++ 6.0, ADO, MS SQL Server 2000. I need a following function: _RecordsetPtr f(_ConnectionPtr& con) and I want to know what happens in the following code snippet with recources which are used by Recordset object. _RecordsetPtr f(_ConnectionPtr& con) { _RecordsetPtr recs; ...more >>

Hello I have the need to import a small txt file into a table, do some processing and then export the file again on to a file server fo use by a another application. I have done this in 2 different ways. 1, I created a Stored Procedure that runs from SQL Server Agent that creates a tab...more >>

I'm using Classic ASP with SQL Server to read in XML data. If I return about 5 records everything seems fine but returning 10 records, the XML seems to cut off towards the end. Is this a problem people have come across before and if so how can I correct it? Code and sample output below AS...more >>

-- DDL create table #tbl(c1 varchar(10)); -- DML insert into #tbl (c1) values ('dog'); insert into #tbl (c1) values ('sheep'); insert into #tbl (c1) values ('cat'); -- goal, retrieve the rows in the order of 'cat','dog','sheep' -- however the following order by won't work select *...more >>

Hi all! I'm working on a meeting room booking system which, apart from the lack of recurring bookings, is almost complete. In addition to the user tables (which aren't really relevant for the query), I have two tables (somewhat simplified): rooms id (PK, integer) name (varchar(50)...more >>

Hi there, Just a quick post to see if someone can suggest a remedy to the following problem we're having. Basically, want to ensure that no any one record is not 'selected' more than once at any time. Code snippet at present is as follows: Note: This is in the middle of transaction and is e...more >>

Hi, I have problem using "between" operatior in SQL 7.0 SP2. I have 6 fields that I would like to use "WHERE" clause as following: fld1= varchar(6) NULL allowed fld2= varchar(10) NULL allowed fld3= varchar(10) NULL allowed fld4= varchar(20) NULL allowed fld5= varchar(20) NULL allow...more >>

Hi, I need to find a solution to for Data synchronization between geographically seperated databases (For eg. India, US, Aus, ...) The plan is to use MS SQL Sever for backend. Currently the option available was Merge Replication (When considering Snapshot & Transactional Replication). But I'm...more >>

I am trying to convert a native DB to MS SQL server. When I want to acces a specific collumn the output contains only fuzzy characters. So I tried to cast the datatype to an integer type. This does not work. The only thing I know is that the datatype of the native DB is DBTYPE_STR. Who can he...more >>

I'm sure this is a trivial issue, but it has me stuck. I have come up with some work-arounds that I don't like for the short-term, so I thought I should throw this one out... Imagine a table with these five rows: ID1 ID2 --- --- 100 101 101 100 333 444 333 555 444 333 55...more >>

Dear All What is the Difference between SuperKey & PrimaryKey .Please answer me elaborately with example because I am a fresher. Regards Arijit Chatterjee...more >>

how do you set a db in emergency mode? Thanks. Tim *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Hi, I want to write a single stored procedure that can insert into several different tables that have the same column types: Table_1 (varchar(80)) Table_2 (varchar(80)) I tried to pass in the tableName into the stored procedure and do a insert: CREATE PROCEDURE sp_insertToTables @tab...more >>

Hi, I have the following SQL statment and am trying to create an inner join accross 3 tables. Can anyone let me know what I am doing wrong as I am getting a You have an error in your SQL syntax error. Thank you James Gralton SELECT Model.ShortNameDescription,ExtendedBN.ShortNameDes...more >>

Hello: Below is the format of a *.dat file which I wish to convert into a relational format(table). Each row is divided by the symbol '>>' and as you can see in the sample file below, some variables have more than 1 value and some variables have missing values. In the sample file below, I have ...more >>

Hi, I have 2 tables. Table I: Charges table, with columns ChargeDate, ChargeAmount and ChargeTypeID Table II: ChargeTypes table, with columns ChargeTypeID and ChargeTypeName There is a relationship between 2 tables using ChargeTypeID. I am trying to write a query which provides informatio...more >>

Could someone help me about this problem, i never had this problem till today, nothing changed in my system. Starting copy... SQLState = 22003, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range Albert ...more >>

Hello I've noticed a very strange behaviour of sql sever 2000 group by clause. I've a row(along with many other rows) in a table as -------------------------------------------------------------------- SectorCode,FullName,SectorName,SectorGroup 7140,RENTING OF PERSONAL AND HOUSEHOLD GOODS ...more >>