I have a table called work_order which has over 1 million records and a contractor table which has over 3000 records. When i run this query ,it takes long time since its grouping by contractor and doing multiple sub SELECTs. is there any way to improve performance of this query ?? -------...more >>

Hi all, We are using a VB component to create and save an XML document on our local LAN. We want to load this straight into a DB Table using a query based on OPENXML. Trouble is, the sp_xml_preparedocument command always throws an error. Here is some code: >>>> CODE >>>> DECLARE @id...more >>

Everybody, I've been doing a lot of on-line research and cannot find any reference to the exact problem I'm having. Let me preface this question with the fact that I'm coming from an Oracle background so my approach may not be the best way to tackle this. However, from the research I have ...more >>

Hi all, We have stored procedure, which basically strips a header and footer of a table (first and last line). The procedure is written like so: CREATE procedure stage.usp_StripSMMT as declare @count int declare @smmt_count int declare @check char(3) select @count = (select count(*) fr...more >>

Hi all, I have an SQL server in Colorado and one in Atlanta. Is it possible in SQL to mirror the two SQL servers? Meaning, once a day have the server in Colorado send all changes made to the server in Atlanta? Or am I looking at purchasing some type of formal backup software? TIA...more >>

Hello, I want to set up a query in SQL Server that is "grouped by" a variable PRICE. Since PRICE takes on continuous decimal values, I want to create deciles based on this variable and then display the average price in each decile. As background, PRICE is a calculated quantity: I divide a t...more >>

Afternoon all, Apologies for cross-posting but as my query covers both Access and SQL Server I thought I'd send it both! I have inherited a project to migrate a fairly complex series of Access databases into a single proper SQL database with a web front end. Its quite a nasty job as peop...more >>

Hi, In our environment a database view is based on columns in a table from an oracle database. This is a linked server I believe. I am told that since we use this construction no where clause is possible. So we get to many rows in our database which we have to filter later on. How can we d...more >>

When you get a server error message referring to a line number in a script, is there an easy way to jump to the correct line? It's not easy to count down in a big script or when there is a lot of white space. In WordPerfect we had a linenum macro that allowed you to enter the line number a...more >>

Hi; I have a table with a TEXT datatype. Its a comment field. Right now the users who put in singlequotes are killing the web front end. The programmer responsible is fixing this issue but it might be a few weeks until we get the patch. I would like to write a trigger that whenever th...more >>

Hi i heared that Oracle can convert from number to word thru query i remember thru (jsp format) .Is it possiable in sqlserver Thanks...more >>

Hi, guys - looking for a bit of help/advice on moving a database from SQL 7 to SQL 2000. We've a third party application which currently uses a SQL7 database. This database has a number of views which are owned by user INFORMATION_SCHEMA and are set as system tables. This user doesn't actua...more >>

HI, I HAVE AN EXCEL SHEET WITH SOME DATA, I WANT TO IMPORT THAT DATA (CELL BY CELL WITH MANIPULATION) INTO THE SQL SERVER TABLES BY USING STORED PROCEDURE(IF POSSIBLE). IF ANYBODY HAVE DONE SIMILER TYPE OF JOB OR KNOWING ABOUT IT, PLS. LET ME KNOW. THANKS IN ADV. T.S.NEGI...more >>

Hi All, I am receiving the following message when I run Dts package which is creating a cube. Error Source : Microsoft Data transmission Services (DTS) Package Error Description: File name or extension is too long Any help will be appreciated! Thanks in advance, Mohammed Sarwar Oc...more >>

I've production sql server 7 sp3 on windows NT. I had a 8GB data file of which 5GB were used and 3GB were unused. I wanted to take back the unused 3GB. So I did the following with EM GUI: 1. I tried to "truncate fre space from end of the file". Didn't truncate the file. I believe there was ...more >>

Hi - I have what I think is a "simple problem". We currently have a database table that stores ItemProperties by ItemID, PropertyID and Value. (The PropertyID references another table for property names and types.) This ItemProperties table is indexed and provides a flexible way of storing...more >>

I created a trigger in the "source table" that will "feed" and second table. The trigger is as follows: CREATE TRIGGER [FeedToP21] ON dbo.FromUPS FOR INSERT AS Declare @Count int Select @Count = Count(*) from Inserted If @Count > 0 Begin Insert into ToP21 Select i.* From Insert...more >>

I have a problem with bcp and format files. We changed our databases from varchar to nvarchar to support unicode. No problems so fare with that. It is working fine. But now I need a format file for the customer table and and it is not working. It is working fine with the old DB with va...more >>

I have an ADP connected to an SQL 2000 database that works fine on my machine, Opening the ADP show lists of stored procedures ala up_ListPrograms but, when I copy it to another computer, and open the ADP with the same connection properties all of the stored procedures and views have a ?revisi...more >>

I am attaching a database with 3 data files. When I execute "exe sp_attache_db..." I obtain this error: database 'POINT' cannot be opened because some of the files could not be activated. I have deleted its LDF file. Usually I detach my db, then I delete transaction log, and reattach 3 data f...more >>

Need to recover from a corrupted log file. The database in Enterprise Manager shows the database as corrupt. Using sp_Attach_db produces a corrupt log message. Any way to recover the data? Thanks...more >>

Everyone, I have a data warehouse that at the moment includes around 2500 hundred jobs. I am planning for a worst case scenario and would like to increase the maximum number of sql server threads so that more jobs can execute simultaneously. Could this pose a problem and if so, at what number...more >>

Using CONTAINS, we can search for "never*" and we will find "nevertheless". However, I would like to do the inverse: "*less" should also find "nevertheless". Strangely, I can't find that in the doc and it does not seem to work. Am I getting something wrong? Or is this really not possible? T...more >>

Dear All, I'm looking for a VBA-code to run a sql-sript from the Access.adp-interface. I want to send scripts so my client can add sql-server-objects from the interface. Thanks, Filip ...more >>

Is there a possibility to change the default instalation directory for the logfiles for sql server 2000? I would like the default database files to be placed on teh d: drive and the logfiles on the e: drive? Sjaak van Esdonk...more >>

I am trying to connect to a sql server database from enterprise manager. I am using sql server authentication and have set the SQL Server up to accept mixed mode authentication. I am still get the message : Login failed for xxx. Reason not associated with a trusted sql server connection....more >>

Is it worth studying towards an MCDBA (hopefully going to get my employer to pay for it) ... has anyone completed it in the UK? if so what learning methods did you use(ie Boot Camp, elearning etc)? And who do you recommend for the training course? Thanks Chris Longstaff...more >>

in my java application I've made a call to this stored procedure CREATE procedure pruebaICM @pANI varchar(20), @pTABLA varchar(20), @pInsert varchar(500), @pUpdate varchar(1000), @pFLAG varchar(1), @pResultado int OUTPUT ...more >>

Hello all, I have a database in SQL Server that should save data from a CRM-like application. The database consists of tables like products, services, customers, partners etc. Problem is that the users should be able to find these items on different properties and with or without substring ...more >>

HOW TO ORDER THIS DATA. MONTH-YEAR 10-2003 10-2020 11-2003 3 -2003 4 -2003 5 -2003 6 -2003 7 -2003 8 -2003 9 -2003 THANX IN ADV. tilak.negi@mind-infotech.com...more >>

I am replacing a production database. Initially I sized a Quad 2.8 GHz with 8 GB of Ram. I will be running Windows 2003 Enterprise Server with Sql Server 2000 Enterprise. I was wondering what kind of performance I would (better/worse) get from a Dual 1.5 GHz Itanium with 8 GB of Ram running t...more >>

I need to know if there is a better way to construct this SQL statement. (Error handling is omitted) MS SQL Server 2000 Insert into FSSUTmp Select a.acct_no, a.ac_nm, a.ac_type, 10, -1, -1 * sum(CHARINDEX(convert(char(4), b.post_yr), @post_yr) * CHARINDEX('-', CONVERT(char(2), b.post_prd ...more >>

Hallo everybody :) I cant make anything of MS explanation about MSDE and its limitations. Could you please tell me, what is the matter ? Is it true that only five paralell connections are accepted by engine ? What abouth next ones? They are wainting in the queue ? Thhanks in advance reg...more >>

Hi All hope you can help. I have a SQL 2k Standard Ed. SP3 server that is in mixed security mode. I have logged into EM with the sa account. Then added a Active Directory group (i.e. Domain\DBAdmins) to the System Administrators for that server. When I try and modify the SQL server registr...more >>

I have three columns, RecordID, FirstName, and LastName, but somehow through some program glitch, there is sometimes a trailing space in the firstname and lastname columns, for example, a persons name could be entered as "John " "Smith" or "Bob " "Johnson " I know there is a RTRIM function in ...more >>

Hello! MSQL2000: I want to make db-backup (maintenance) but haven't enough space on the discs that are physically in the machine. I have enough on the network, but i can't choose an network-drive, when i want to specify the backup directory. What to do? /Bjørn ...more >>

I wrote a procedure in SQL 6.5 which is something like CREATE PROCEDURE abc AS BEGIN TRAN xyz insert statement .. insert statement ... update statement... insert statement ... insert statement ... COMMIT TRAN xyz I want either all statements to execute or none...more >>

I am unable to update a table (either by opening it and entering data directly or through a form). I have set the recordsettype property to updateable snapshot and have set the permissions to update inside of the SQL Server 2000 to checked. Unfortunately, these items have not worked. Any advice ...more >>

I have 2 tables in the same database: TABLE_A --------- ID username email first_n last_n TABLE_B --------- PID message address date It seems quite simple what i want to do but i can not figure it out!!! I would like to DELETE all records in TABLE_A where the ID field does not m...more >>

Field Names: NOs Code Code1a UniqueID 61 10 888 10 62 10 888 11 63 10 888 ...more >>

I've got an awful query that does what I want but uses NOT IN and a UNION and looks to do plenty of table scans from the execution plan. I'm convinced there's a nice way of doing this efficiently with joins but after about an hour of searching I thought I'd give up and ask... For the purposes ...more >>

Where can I find any materials about models of distriubution of data in sql server ? Any help will be appreciable Gawel ...more >>

Hello, apologizing in advance 4 propably asking a trivial question ... in my Server list in Enterprise Manager 7.0 in front of the name of my servers i have a small server symbol followed by an even smaller green triangle in a circle (which, according 2 my information means "Server running...more >>

Hi, all: I have a form which lets users choose more than one value for each question. But how do I insert each value as a separate row in my table (instead of having the values submitted as a comma-delimited list)? Thanks for your help. J ...more >>

MSSQL2000 I have a table that contains customer transactions CustomerID Transaction TransactionDate .... I need to select the most recent record that matches a specific = CustomerID. I am fairly new to SQL, could someone provide a sample select statement. TIA Tim Morrison --=2...more >>

To all gurus, I am developing an application in which i want to show the number of rows returned by the query. e.g. Select Categories.CategoryName, Products.ProductName, Sum(([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS ProductSales FROM ((([Order Details] INNER J...more >>

Hi, I've used the minus functionality which is available in Oracle and i would like to use it in SQL server, but i don't know how to. The folllowing is how it works in Oracle Select symbols from symbol_table minus select tsymbols from trade It returns a list of all the symbols from sym...more >>

Hi all, I have been wrestling with this problem all morning with no success so far where I have a need to bring back an excluded field. Basically I have a list of order numbers. Each order number can have many order types attached one of which is a ‘P’ type. Most order types have an accoun...more >>

I am having alot of trouble with a union query Im trying to create. The bizarre thing is I initially created this query and managed to save it as a view SELECT ID, DepartureDate, City, Country, Region, fkDepartureAirport, CONVERT(MONEY, Price) AS Price, '1' AS Type FROM dbo.vHoli...more >>

I have a table(work_order) with time as varchar(5). The values in table looks like this work_order_id rtim 1 08:15 2 08:45 3 10:13 4 14:56 and so on... I want to count how many work orders for every half an hou...more >>

Hi all, I think I have the right group, if I don't then I do apologize. My problem is that I'm trying to run a SQL script within a VB Environment. The actual program is WISE Installer, but I'm sure that the same problem arise within VB because we have also wrote an app that is meant to run SQL ...more >>

Hi, On one side, I have a Sybase 12.5 32 bits Server running on AIX 5.1 (maintenance level 3). On the other, I've got W2000 5.00.2195 (SP2) with SQL Server Standard Edition, Product Version 8.00.534 (SP2), having installed MDAC 2.6. I connect to the Sybase 12.5 database using Merant ASE OD...more >>

Hi, all: This is probably a simple problem, but, as an SQL newbie, I'm having a little trouble understanding multi-joins and subqueries. I have the following tables and columns: MemberTable ----- MemberID (primary key) MemberName Address Country FoodsTable ------ FoodID (prim...more >>

Hi all. Have a SQL server with about 10 small databases on it. One database in particular, the tables are somehow being recreated each night at 12:03 AM. The tables are also empty of data. There's nothing in the logs either. All other databases are just fine. I've been searching and troublesho...more >>

Dear Friends, I wanted to configure Failover cluster for SQL Server 2000 on Windows 2000 advanced servers. I have only 2 no.s of windows 2000 advanced server m/cs. I dont have any shared disks and SCSI drives. Can I still configure the Failover clustering? I would like to know more about...more >>

Is it true that binary and varbinary has a max-kapacity at 8000 bytes? Im jyst unsure how to read the dokumentation and want to be completely sure. And image is only one to use for data aobve that size? Thx in regards for clearing things out for me Reagards Anders ...more >>

How do I detect with a query which database is off-line. And how do I detect with a query the recovery model of a database. Bye Arno de Jong ...more >>

I need to execute xp_cmdshell in a trigger and pass a command line parameter to the .exe program i.e. I have a .exe program c:\program files\savedata.exe In the trigger I have a parameter @Id bigint I need to pass the parameter @Id to savedata.exe In the trigger, I am trying EXEC mas...more >>

I am looking for the SQL Server 2000 Licensing Setup Utility Program and cannot find it. Everything I read says, goto Control Panel->Administrative Tools->SQL Server License Utility. When I go there, I do not see... Any help would be Greatly appreciated! Setup: Win 2000 Server SQL Server ...more >>

Is it possible to have a conditional union statement in a stored proc? Here's an example on the northwind database. If says there's a syntax error near the UNION statement. Looks like it doesn't like having the BEGIN directly in front of it. Is the only solution to create a dynamic sql s...more >>

SQLSourceSafe 2.0 is the right version control solution for SQL Server database development projects. SQLSourceSafe 2.0 Integrates with both SQL Server and Visual SourceSafe, providing an effective and effortless version control management system for individuals and project teams to manage datab...more >>

Hello: The installation details: W2K SP4, SQL Server 2000 Ent with 1GB RAM. It is a Bi-P3. When I run the Profiler to trace Stored Procedure performance, I get a bunch of SP:CacheMiss for couple of stored procedure I invoke quite often in a web app. But I do not see SP:Recompile. Here...more >>

I am writing a database app which will run under MSDE on the user's systems. Other than limitations as to the number of concurrent users, are there any issues that I need to be aware of when programming the database using SQL Server 2000? i.e., are there T-SQL programming statements that will ...more >>

Hello: I am new to T-SQL programing, and relativly new to SQL statements in general, although I have a good understanding of database theory. I'm a little confused as to the fundamental differences between a view and a query, or rather, when it is more appropriate to use one vs. the other. ...more >>

I have created a Database Editor with FrontPage using the Wizzard. I have a database in SQL 2000 called Equipment with one table named Equipment. When I bring up the editor, it displays a list of all records with a link to the edit page. When I click on the link for the first record on the pa...more >>

Reading a lot about Nulls right now I still can't find a Technical reason to use it or not. For what I've understand is this: In an Ingres database a Null column has a standard extra storage of 2 Bits. In a SQL Server database every column has a NULL-bit telling about this column it is NULL...more >>

I have a recordset in my VB6 app that was created using ODBC and I would = like to insert the data into a SQL db table. I already have the target = Db & table setup. What is the fastest way to accomplish the data = transfer? Can I do a Select into using the ODBC recordset as my source? Than...more >>

I'm populating an Access continuous form with lots of icons from a SQL Server backend. If I remove the field holding the icons from the stored procedure, the form loads 5X faster. Is there any sort of trick to improve the performance of this sort of scheme? lq...more >>

Hi, I've a prob in my office.i have a table with the following fields, table : acc_file acc_cod nvarchar(6) P.key acc_name nvarchar(30), level int, acc_mas nvarchar(6) which should any one of the previously entered values of the field acc_cod(reference). the rows will be (exam...more >>