Hi, I have a big problem with a database in MS SQL SERVER 2000. the rows into the some tables, for the second time, have been mixed between they without appearing reason. the application that uses the db is totally TRANSACTIONAL and they do not exist query that they do not have clause WHE...more >>

Hi All, Just wondering if it's any more efficient to use the following SQL statement to return a partial match from a column as opposed to the second statement. SELECT * FROM Table1 WHERE CHARINDEX('value', mycol) > 0 Versus: SELECT * FROM Table1 WHERE mycol LIKE '%value%' Does an...more >>

here is a structure of tables in a database CUSTOMERS (CustID*, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax) EMPLOYEES (EmployeeID*, Lastname, Firstname, Title, TitleofCourtesy, Birthdate, Hiredate, Address, City, Region, Postalcode, Count...more >>

This is probably easy but I can't work it out. I have this statement SELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceReference.Name FROM dbo.CIF_Departures INNER JOIN dbo.CIF_PlaceReference ON dbo.CIF_Departures.EndPoint = dbo.CIF_PlaceReference.PlaceID ORDER BY dbo...more >>

I'm new to DTS. I read some docs before adventuring into this matter. I still haven't found in all the docs I read if there is some "built-in" DTS task or function or wathever, to do a mixed "insert/update" import from a source, giving a unique field as primary key. I'll try to be more specific....more >>

Hi I'm trying to make this to work and need help Here my SP and I'm building sql with output param. Alter PROCEDURE lpsadmin_getSBWReorderDollars ( @out decimal(10,2) output, @sType varchar(20), @dSearchDateFrom datetime, @dSearchDateTo datetime, @sOrderType char(...more >>

Hi All We have an order processing database which includes the standard Order Header/Order Lines tables. I'm trying to write a query to get the average number of lines per order over a given period, and I'm stuck :-( I can get the number of lines per order with: SELECT COUNT(*) FROM Order...more >>

I was looking thru the mas90 info site of http://www.accountingsoftwareconsultants.net/mas90_accounting_software.htm for detailed info on MAS 200 SQL server. There's some info on that page, but I need more. Is there any more detailed info anywhere else on that site, or anywhere else?...more >>

in simple words it's about versioning at record level. Example TableEmployee - EmployeeId, EmployeeName, EmployeeAddress, DepartmentId, TableDesignationMap - EmployeeId, DesignationId, EffectiveDate, validity TableDepartment - DepartmentId, Department ...more >>

I'm probably going to get shot down with thousands of reasons for this, but I've never really heard or read a convincing explanation, so here goes ... Clustered indexes are more efficient at returning large numbers of records than non-clustered indexes. Agreed? (Assuming the NC index doesn't...more >>

I have a client using SQL 2k, SP2 (due application requirements, SP3 is not an option - the application vendor will not specify why). We are receiving: [Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server Connection Broken There doesn't appear to be any rhyme or reaso...more >>

Hallo, table A x : int, increment, identity, index relationship: fk_b_a has pk in A:X , fk in B:X enforce for repl , insert i update-selected Table B x : int, index relationship: fk_b_a has pk in A:X , fk in B:X enforce for repl , insert i update-selected relationship name in B is ma...more >>

Hi I got the advice to rebuild the indexes on the databases once a year. Espesially if the database have grown much. The question is: How do I do that? If I try to run the indexscript wich was run when the databases were created, I only get the message that the indexes already exists. Are ther...more >>

I am trying to export data to a text file using bcp. The table that I am trying to export has an ntext column. I keep getting this error, and have not been able to find a way to get around it. How do I specify to another provider, or get it around it anyway? Thanks... C:\Program Files\Micr...more >>

I have received a table of data that has a field containing date information. Unfortunately it was derived from a MainFrame dump and originated as a txt file and was then ported into an Access MDB file before it became an SQL table. The date format is vchar(50) and actually is comprised of 6 c...more >>

Greetings, I am getting a flow of error messages: Error: 17805, Severity: 20, State: 3 Invalid buffer received from client. Can anyone shed some light as to how to get rid of them or resolve any issues that may be causing it? Any assistance would be greatly appreciated. Thank you.....more >>

Hi Folks, Lets assume I have three tables. Their layout is as follows. Please note that tblPeople does not have an entry for Denver (this is my problem) tblCity _________________ CityName OCID LA 1 Denver 2 tblCars _________________ OCID CarVolume 1 300,000 2 ...more >>

I have table1 and table2. In table1 I have a column of numbers, numbers1. In table2 I have a column of numbers, numbers2. I'd like to select the highest number represented in either column. Example: table1:column1 -------------- 3 45 5656 43 6565 556 76 table2:column2 ----...more >>

I am posting this message after reading all the previous posts with the above subject. I have a SQL2K named instance running on a Windows 2K machine under Local system Account and it uses SQL Server authentication. This Server is on a domain by itself. Let us call this M1. I am connecting to th...more >>

Please Help me. I have a Stored Proc as follows: USE feesched GO CREATE PROCEDURE [dbo].[sp_UpdateAveragedMedicare] AS -- copy records with alpha in pos 1 that's not J SELECT SUBSTRING([cpt code],2,LEN([cpt code])),amount,inscode INTO t_AveragedMedicare FROM AveragedM...more >>

Hopefully someone can at least point me in the right direction for more research (e.g.: correct terminology). My only previous experience was just dumping data into a database using ODBC, and that was some years ago so now mostly forgotten. I need to write an NT Service/Application (in C/C++) ...more >>

Hi, Please look at the code at the bottom of this procedure (UPDATE COVENANT PRINT DATE). When I put it at the tope of this section, it does the update and fails to return the SELECT results. When I put it at the bottom, the SELECT returns results and the UPDATE fails. Can anyone suggest ...more >>

I would like to search a table for a phrase, or for a partial phrase, eg on table product - for name or description, or name + descprition. How does one say select * from product where name + description like %phrase% or contains phrase Currently I can get where name, or where descriotion l...more >>

Can anyone help me translate this statement from using the legacy outer joins to the SQL-92 standards? Select CA.* From Customer C, Shipper S, Customer_Order CO, Cust_Address CA Where CA.Customer_ID =* CO.Customer_ID and CA.Addr_No =* isnull(S.Ship_To_Addr_No,CO.Ship_To_Addr_No) and C.ID = CO...more >>

When sending two 'text' columns using BCP, and using bcp_moretext because the first is very large, and if the second is NULL, then how do you send a NULL value using bcp_moretext?...more >>

I'm copying data out to a file with pipe delimiters. I would like to add a header and trailer. Is this possible? If so, please help me with the steps. Also, is it possible to append to a text file while doing the bcp? If so, how?...more >>

Q1.)In SQL Server 2000, is it always possible to use copies of the data and transaction log files of a database from one server to reattach to a new database on another server, or even to the same server, without first detaching from the existing database? Books Online says that detaching a data...more >>

Does anyone know a select statement that would return the column names and keys and indexes of a table? Thanks, TGru *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Hi I work ith sql server 2000 and i need know the diferent of joins in format not ansi ( with * ) and joins in format ansi ( with 'outher join on' ). Two format work equal ??? What is de correct format ??? Thank you. R....more >>

Hi All, Just wondering if anyone can tell me if you can test for multiple conditions as part of an "IF" statement in T-SQL in SQL Server 2000? ie something like: IF @merr = 1 or @merr=2 Begin SELECT statement UPDATE statement End Any help much appreciated! Much warmth, M...more >>

Can anybody tell, how to implement the application roles in SQL Server 2000. Basically, I want to Implement the application roles in our application, so that it can be application specific. Its' an clients requirement from we people. Thanks Prashant Thakwani...more >>

We are using ODBC connection pooling with SQL Server 2000 v5 on Win 2000 v5 sp4. Each time we get a connection, SQLConnect, we see the memory usage go up, about 52K. When we return the connection to the pool, SQLDisconnect, and after the time that the connection remains in pool expires we se...more >>

Hi there, it has been a while since i have posted. I am in a situation where I am stumped. I am learning to build a dts package where I am connecting to a table in an AS400. This database is being maintained by an outsourced company and therefore I can't change the table structure or even ask...more >>

Greetings All! I just started playing with MS SQL and was wondering how i could possibly do the following for example. I do a 'SELECT source FROM history' and that gives me a list of sources. Now i want to run a SELECT for each source i received from the previous SELECT. For further clarity,...more >>

The C++ application calls the database to look up property data. One troublesome query is a function that returns a table, finding data which is assembled from four or five tables through a view that has a join, and then updating the resulting @table from some other tables. There are several ...more >>

We're considering purchasing an application, which stores some data in the filesystem, and some data (meta data and links to files) in MS-SQL. We need to be able to create a backup wherein the database and files are "in synch" (in other words, in a consistent state). We need to maintain the...more >>

I have a SQL 6.5 database that we parse some data into everyday using an access program. All this was devises and setup by a programmer that I can't get in contact with anymore and it has actually run for about five years without a hickup! But just a few days ago our parsing program just stops d...more >>

Hi, Is there a way to control the connection pools using SQL Server 2000? I know how to alter the connection string to do this(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguid e/html/cpconconnectionpoolingforsqlservernetdataprovider.asp), but I am looking for a setting i...more >>

Hi, I have the following problem: I have a table of users and a table of appointments that references these users and has start and end times. I want to select those users that have appointments at, or bridging, the given time. As a starter I've tried to do the following: DECLARE @Dat...more >>

Hello, I have a table say SalesLeads. This table is accessed through various front -ends and it stores data for Sales leads of various products. This table has a reference to another table(SalesLeadRefTable) Sample Data in SalesLeads SalesLeadID Comments RefTableID -------------...more >>

Hi, I have problem about writing a proper SELECT query for the following goal: Table name: peoplelist column 1: id (not NULL, auto_incremental) column 2: name column 3: country now, there are about 7,000 rows in this table. I want to select out: first 10 or less people in the table fo...more >>

Is it possible to have a stored procedure in database A while calling it from database B and have it manipulate the tables in database B (whatever the calling database happens to be)? We have a large-scale app that uses many complex stored procedures, and as of now, we're copying the SPs to ev...more >>

I have a database that is pre-populated with sequential part numbers. As people reserve the parts I update a flag to show the # is no longer available. Now they want the ability to take out a block of "x" number of sequential part numbers - say for example 5. If my database had the following...more >>

I have a database that has grown larger than I had expected. I think there is one table that is at the root of the problem. The table is defined as follows: CREATE TABLE [dbo].[UserAudit] ( [UserAudit_id] [int] IDENTITY (1, 1) NOT NULL , [UserAuditAction_id] [int] NOT NULL , [Dataset_id]...more >>

I am attempting to document a sql server 2000 based accounting system. Is there any way to see what tables a stored procedure affects without diving into the code? Regards, Ty...more >>

I'm using SQL 7.0 (but may have the same problem in SQL 2000). The problem is: I created a DTS package to load data from a csv file. The package works fine by manually start, but cannot be scheduled as a job. As soon as it becomes a JOB, it doesn't work. Can anyone give me an idea? Thanks. S...more >>

I'm trying to create a backup set which maintains only a fixed number of days. As such, I've got the following script: BACKUP DATABASE [mydb] to [mydb Backup Set] WITH NOINIT, NOUNLOAD, NAME=N'My Database Backup', NOSKIP, STATS=10, NOFORMAT, RETAINDAYS=5 DECLARE @i INT select @i=position ...more >>

Hello All I am using SQL server 2000 as the backend of my application but don't want my clients tobe able to view or edit the database tables, stored procedures , view etc using enterprise manager or query analyser (or similar tools)How can this be done ? I searched a lot for this but unable t...more >>

I tried this: use northwind go SELECT OrderDate FROM Orders WHERE OrderDate > '19950101' see the query plan? ok SELECT OrderDate, EmployeeId FROM Orders WHERE OrderDate > '19950101' see the query plan? what appened? the only way to make an index seek instead of an index scan...more >>

Was wondering if anyone knew of a tool that you can point to a view in the database and it graphically (or even in text) determines the dependencies. At the moment I am manually going through a swag of views and putting them in visio org chart but there must be a better way !...more >>

Hi, Handling # table is giving problem. Does anybody have solution/suggestion for the below given problem. Create a #TMPTAB table ------------------------ SELECT GETDATE() MYDATE INTO #TMPTAB Select #TMPTAB Table ----------------- SELECT NAME FROM TEMPDB..sysobjects where name ='#TMPTA...more >>

I have a problem that I have spent an age on and can't resolve. Basically, I have a table that has a date field. I have created SP's to return values within a given date range. The problem that I have is that when I append some data to the table, inconsistency occurs. What basically happ...more >>

With MS SQL 2000 Enterprise Manager, is there a way to allow a user access to only a few tables, but deny the user access to the rest without having to go to all of the tables and denying access? The database has roughly 50 tables, but only 3 should be granted to the new user, so as you can see i...more >>

Which is more efficient Where NonindexedColumn=x and IndexedColumn=y or Where IndexedColumn=y and NonindexedColumn=x or does matter? Will the optimiser work it out? (I'm building the SQL string on the fly in the fron-end)...more >>

Hi guys, I have been told that only users with SA priviledges could check the status of a job. For this reason, I had to give SA priviledges to this user so he could check the results of a job (succesful or not) and do his work. Now, he makes changes on the database without telling me and las...more >>

Greetings, The following shows how the Properties of a database look like: Data Files: File Name: student_dat Location: e:\data\MSSQL\Data\student.mdf Space allocated (MB): 62 'Automatically grow file' checked File growth: 'By percent 10%' checked Maximum file size: 'Unrestricted file ...more >>

hi I need generate dinamic sql: I need a select sentence with all fields of one table 't1' and all fields of tables which 't1' have foreings keys 't1 ( reflexive ) ,t2,t3,...' Now, l have de select part, de select from... but I don`t know how create de join clauses ? Some idea about this...more >>

I'm trying to create a new database and new login to allow a client (through my asp.net page) to create their own database, username and password. I've tried using the stored procedures I've found in BOL, but I can't get it to work right. It keeps saying that I can't assign this username as th...more >>

I have an asp page that currently is creating a database and a user login for that database. After everything successfully (I thought) executed, I tried to change my connection properties for the server and then login as this new user. It wouldn't allow me to, so I logged back in as the admini...more >>

/*Code below raises following errors: Server: Msg 245, Level 16, State 1, Line 6 Syntax error converting the varchar value 'a' to a column of data type int. */ create table #x (i integer, c char(1)) create table #y (c char(1), i integer) insert into #x VALUES (1, 'a') insert into #y S...more >>

Has anyone explored a process whereby the Transaction Log would be backup up based upon a defined threshold, ie. 75% full? All the research against news group posts and SQL2000 literature seems to point to scheduling a log backup job on a periodic basis. My workflow isn't 24 hour consistant an...more >>

I am new to database programming and was curious how others solve the problem of storing encrypted in data in db table columns and then subsequently searching for these records. The particular problem that I am facing is in dealing with (privacy) critical information like credit-card #s and S...more >>