Hi All, I am getting slower performance of select statements in MS SQL. I am finding select statements in MS SQL are even slower than MS ACCESS. Is there any way to improve the performance of select statements in MS SQL by tuning the database of anything else?? Thanks in advance! Hoque...more >>

I am looking for some document or resource that shows the difference between SQL Server 7.0 and SQL Server 2000. Any information is greatly appreciated. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Hi; I am still improving my sql/tsql skills. I was wondering if someone could give me some advice on how to do some high powered deletes. I have two tables, A and B. "A" is the table I want to delete records from. "B" is a table where each record is 4 keys ( fields ) that I want to ...more >>

Hi everyone, I encountered an error "Need to run the object to perform this operation Code execution exception: EXCEPTION_ACCESS_VIOLATION " When I try to import data from Oracle to MS SQL Server with Enterprise Manager (version 8.0) using DTS Import/Export Wizard. There are 508 rows...more >>

I have a table with fields called fname (First Name) and lname (Last Name). I need the user´s email thai is compose from lname and fname: LOWER(LEFT (fname,1) + lname) Is there any difference between creatig this computed column ia a table or in a view in SQL Server 2000? I can do: 1....more >>

Here's the scenario. Using a campaigning applicaion for an eNewsletter. It writes to an Access table. I have an ASP web page that writes to another table. I chained 3 Access queries together into a Macro to copy that data to the campaigning table, give a numeric identifier to that record, and t...more >>

We are moving all databases from a SQL version 7 server to a version 2000 server. Which of the following is the preferred method? 1) Detach the DB on the version 7 server, move it to the new server via Windows Explorer and reattach it there. 2) Use SQL Enterprise Mananger with the DTS Exp...more >>

hi there, i am using sql server 7. below is the stored procedure that is giving me grief. its purpose it two-fold, depending on how it is called: either to return a pageset (based on page number and page size), or to return IDs of previous and next records (based on current record id). the...more >>

Hi; Can a table.....with all of its data be exported into a SQL script that will restor the table with all of its data? Thanks in advance Steve...more >>

Dear Friens, I am writing a SP.But storeing a query in a variable.But at the time of execution generating error.Exam =================== Declare @query varchar{500) Set @query = 'Select * from table' if exists (exec (@query)) print 'Hi' ==================== But "if exists" line giving err...more >>

I have a 6.5 db that I want to create a copy of, I can move it to either a machine running 7 or 2000. I am more used to doing this in Oracle so pls bear with me... I don't want to have any downtime on the 6.5 db, so I'm thinking perhaps import is the best way to go? I am assuming that 6.5 b...more >>

strSearch is a dynamically created search string Copy of select statement below sql = "SELECT Master.Master_Key,Master.First_App,Original_Info_Date,Last_Info_Date,Area.Ar ea_Name AS Area_Name, State_Cntry.State_Cntry_Name, Company.Oper_Name1 AS Oper_Name1, Company.Oper_Name2 AS Oper_Name2,...more >>

Can anyone here tell me about initializing high-values? I want to create a generic stored procedure that will perform the following Select * from ? where ? between parm1 and parm2 no problem, but when no parms are passed I want to perform a select * so....... In the declare se...more >>

Any help would be greatly appreciated. My problem is that I need to set up a backup SQL Server 2000 machine which can be used in case of a failure to my primary. All databases (30 as of now) must be an up to the minute exact copy of production and include most recent changes in data as well a...more >>

Hi all, I have a statement that reads as follows: select [Sales Ledger] - gl as difference from (SELECT SUM(RPAAP / 100) AS [Sales Ledger] FROM F03B11) Q1 join (SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GB...more >>

Hello I have many different tables, that most have the same structure.. they all have some primary key, some fields and mylinkid ... so what I want to make is to create a stored procedure, where I can pass the table-name, primary key, and the new mylinkid, what should happen is, that the proce...more >>

Hi does anyone know of a way of giving developers read only access to a database diagram in Enterprise Manager (SQL Server 2000). The database was supplied by a third party and we don't want them tinkering with it, but they do need to be able to create additional tables etc. to extend the...more >>

Can the only thing you do is delete backup files with xp_sqlmaint, or do you also have to backup the database?...more >>

I need to access a table and return 3 values from it in the same recordset - ie one iteration of the recordset will have 3 values from the same database, I have looked at sub queries but they dont seem to be able to do what i want. I would be grateful for any guidance S...more >>

Here's my carefully hand-crafted SQL: SELECT * FROM tblMailbookQueryISPRO WHERE ((fldClosed = -1) AND (fldCSNo LIKE N'%PSH%') AND (fldReceivedDate >= CONVERT(DATETIME,'2003-04-01 00:00:00', 102)) AND (fldReceivedDate <= CONVERT(DATETIME,'2003-04-30 23:59:59', 102)) AND (fldAction = -1) ...more >>

Hi All, I am facing problem in MS SQL Server 2000. It is behaving slow for select statements. It is even slower than MS ACCESS. For example, if i use "Select count(*) from tbl;". i get the results after long time if there is more than 100k rows. What might be the possible reasons for this?? ...more >>

I want a report in excel from my SQL database from one table. I want to create a view and then use DTS package to export it to excel format. But how can i get the following format in a view?? They should be grouped by date. Date Column 1 column 2 column 3 29/10/2003 ...more >>

Greetings, I think I saw an article yesterday in which you told someone not to make an Identity column a primary key. I seem to remember you saying something like "It undermines the entire foundation of the relational model." I've been thinking about that all night and I think I underst...more >>

I am currently running a windows 2000 machine with asp, sql server, mail server, ftp server etc all on the same box. The site runs several hundred ecommerce stores. Recently the processor utilization has been spiking and I have decided to get another server and use sql server on one and asp o...more >>

I have MS SQL 2000 Standard edition licensed in "per device" mode on a dual xeon. However the properties->Processor tab on EM only list 1 cpu (CPU 0) and has "use all available processors" selected and the use [#] is greyed out. Anybody else experience this? Is it really using both proc...more >>

I have a whole bunch of forms that have an unbound StartDate and an EndDate field that I have used in MSAccess MDB databases as parameters in queries (ie tblEvent.StartDate > Forms!myFormName.StartDate.) So, now I'm migrating this beast over to and ADP/SQL Server project using Views and Procedur...more >>

I have a sql database I run dbcc checkdb and get no errors I go to backup the database in enterprise manager, and it won't restore because it has internal consistency errors I can go back to an older version of the database I run a particular procedure in our accounting software package Neve...more >>

Hi- I'm having problems with the following stored procedure. Below first I have the stored proc, then I have the asp code that calls it. Randomly it won't insert the order into the database and a way out of sequence order id is returned. My connection string looks like this: Provider=SQ...more >>

Hello All, I'm using SQL Server 2000 and wish to restrict the user login to one instance only. Is this possible? Is this wise? ...more >>

Hello all. Before my arrival at my current employer, our consultants physically set up our MSSQL 7 server as follows: drive c: contains the mssql engine drive d: contains the transaction log drive e: contains the data files No filegroups were set up and the data files consist of only 1 large...more >>

Seeking several Data Junction / VB Developers for 3 month contract with possible renewal in Alpharetta, GA. This opportunity requires 3-5 years development experience with Data Junction, Visual Basic (VB6) and SQL2000. Must be US Citizen or US Permanent Resident. No 3rd Parties. Healthcare e...more >>

Hi Guys, I have editing a SQL Server table field that have long text data. I am updating some text in this field. How can I update this field instead of re-write all text. With the Select command its gives me complete text in one line and it hard to read it. Any idea. Thanks in Advance Adna...more >>

The Scenario: We have some data that can be in three states. One is a saved state, a temporary state, and a complete state. The complete state is the bulk of the data. This data will be requested 1% of the time, maybe even less. The saved state and temporary state will only number 10-15 ...more >>

Hi all, I have 2 files containing Id numbers and surnames (these files essentially contain the same data) I want to select distinct() and join on id number to return a recordset containing every individual listed in both the files HOWEVER, in some cases an incomplete ID number has been colle...more >>

Hi, Is it possible to do from one script? We have a set of user's tables like "tbl%". We can get this list very easy using this script: SELECT name FROM sysobjects WHERE xtype = 'U' AND name LIKE 'tbl%' ORDER BY name; We need to change some column names if these names are in a special lis...more >>

I want to know if there is a "best-practice" for setting up Database Maintenance Plans in SQL Server 7 or 2000. To be more specific, I want to know the order in which I complete the tasks. Do I complete optimization first, then integrity checks, then translog backup, then full backup??? OR is t...more >>

I am wanting to learn how to diagnose locking problems on a query by looking at locking data in SP_Lock and not SQL Profiler. Books on line describes what each column means, but I would like an example to know what actions to take if I find 2 SPIDs competing for resources. For example, should I ...more >>

I need to send the result of a procedure to an update statement. Basically updating the column of one table with the result of a query in a stored procedure. It only returns one value, if it didnt I could see why it would not work, but it only returns a count. Lets say I have a sproc like s...more >>

Hello, When I try "Insert Into" with a text column value that's very long, I only get a cropped version inserted (I know because when I programmatically access the field, it's cropped-- I'm not just assuming this from query manager). The text column has a default size of small, however I don...more >>

Hi, Does anyone have any suggestions on the best to go about enforcing a relationship between two tables that exist within separate databases on the same SQL Server instance....more >>

sql2000: what would be the query to return max - min score from #Temp (grouped by name,name1,name2) to look like following:?. Fred A,A1,A21,math100,88 A,A1,A21,math104,45 A,A1,A21,math101,88 A,A1,A21,Chm100,86 A,A1,A21,chm102,44 A,A1,A22,math101,99 A,A1,A22,math100,86 ...more >>

Hi, What methods can we use to check the integrity problems? Some records in my database are having Foreign Keys but the database doesn't have any related records with required Primary Keys. I need to scan the whole database and delete (maybe with backup, maybe not) all wrong records. Who c...more >>

Hello, Database log file on MSsql2000sp3 is 27gb when database itsself 305mb. I attempted to shrink the log file with Enterprise manager,but it wants to use a minimum of 26.xxx MB,approximatley 27gb of disk space. when running the dbcc shrinkfile (file_name) message returned is "all virtual...more >>

hello, I'm need some help with some sql to return some data...obviously ;) I have 3 tables. tblMgr, tblSup, and tblRep. I'd like to get all the names of the reps that are assigned to a particular manager. Here is a simplified exmaple of table setup: tblRep ------- repid repname supi...more >>

Our database size is currently 4G and is incrementing at a rate of 45M/day. What is the max size of a SQL database? And what is the size beyond which the server performance will start to go down? ...more >>

Well I've done some reading but I think I may be missing something here. I am trying to pass an optional argument to a sproc that will select with a "Where like" clause on an integer data type. I basically need to select All id's or only One id from the customer table. Here's what I have ...more >>

I posted a message regarding a problem I'm having with SQL server 7 not taking any notice of the permissions that I'm setting up on my database tables (see thread "SQL Server 7 ignores user permissions" started on 10/10/2003). I did get one response to this original message, but unfortunately th...more >>

We have C:, D: and E: drives on our server box. C: drive is partitioned and is big enough only to hold Operating system files. D: and E: drives are what were supposed to be used by developers / dba's to store / create SQL Server (system and user databases). Well, some developers installed the ...more >>

Hi, I have a table like this : TimeID Application Login State 1 App1 login1 0 1 App2 login2 1 1 App3 login3 1 1 App1 login4 0 1 App1 login5 1 1 App4 login1 0 2 App1 login1 0 2 App2 login2 0 2 App3 login3 1 2 App1 login4 0 2 App1 login5 1 2 App4 login1 0 3 App1 login1 0 ...more >>

Hi! Please help with the following: Our disk space is limited. I set the database recovery model to "FULL", and backup transaction log every hour between business hours. But the disk is always full because the transactions log growth. Do you think increase the frequency of transaction log backup...more >>