Dear All, I developed an web application which use MS SQL Server 2000. I encounter the following SQLException "[Microsoft][SQLServer 2000 Driver for JDBC]Error setting up static cursor cache". Did anyone encounter this problem before? What does this exception mean and how to solve this ...more >>

Hi I have the following union query that retrieves two counts. Can I sum them up within this query, like wrap this in a sum function somehow to get the total count? Or is there a better way to do this. Please help. Using SQL 2000. select count(user_id) from table1 UNION select co...more >>

I have a requirment to take data from a large set of tables where the total number of these tables may change on a regular baisis and output into another table. All the tables willl have the same columns. Frequency is being debated but it maybe as much as once per hour. Example 1) I ne...more >>

Hi, I am trying to add a staggered running total and average to a query returning quarterly CPI data. I need to add 4 quarterly data points together to calculate a moving 12-month sum (YrCPI), and then to complicate things, calculate a moving average of the 12-month figure (AvgYrCPI). Give...more >>

I just got Visual Studio 6 Enterprise which includes SQL Server 7. However, when I try to install SQL7 (Standard Edition ?) on Windows XP Home it only installs the client components and not the server component. The install routine claims this is because of the version of Windows I am u...more >>

Hi group. I have been working with ColdFusion and SQL Server for some time now....abour 4 years I guess. I have developed various web applications successfully. The scenario I am in is that a company would like me to come on with them as a lead database admin type guy. I would still do a little ...more >>

Hello, I'm working with SQL 7 and I have one ldf 30 times the size of the database, someone knows the way to truncate this log in order to have te correct one and be able to have free space on disk? Thanks in advance Fnv/ ...more >>

In a Multi Server Job under SQL Server Agent (MSX), we have a series of steps like: DTSRun /~Z0x8E8635E6BBA... The ~Z means it's an encrypted hexidecimal string. The person who created this step is no longer accessible, and the string can't be trivially decrypted, so I don't know what this...more >>

http://www.f-secure.com/v-descs/swen.shtml Advisory above. I'm getting approximately 500 emails a day from posting on newsgroups. ...more >>

HI All I have a process that I am trying to accomplish with one statement. I cannot think of any way to do it other than using a cursor. I was wondering if anyone could point me in the right direction. I want to update the Domain in Table A with the Domain in Table B where A.Account = B....more >>

Which method would give me the best performance for logging large records into a database with over 25000 rows in SQL Server 2003?...more >>

Hi. I have two sql servers and have ran exec sp_addlinkedserver 'ACSPSM', N'SQL Server' to link one to the other and also vise versa. Each server has two users permissioned. My problem is when ever I try to do something that does a remote write I get the follow error message Microsoft O...more >>

Hi all, I have 2 independent stored procedures(I cannot join them because of the group condition are different however they have the same cust id) but one for reading the summary and other showing the details of the summary. Now I want to run these two stored procedures in order to get the bot...more >>

Just a quick question, how do I divide the results of these two queries together ? (select count (*) as ontime from schedule where actualarrivaldate <= estimatearrivaldate) (select count (*) as total from schedule) Thanks for any help Robert ...more >>

I need to replace every instance of a word in our pagecontent table with new terminology. I need help a script that can be used to mass replace every occurrence of a word in PageContent. Doing a update (UPDATE pagecontent SET contentshort = 'Deliverable' WHERE contentshort Like '%Project%') wi...more >>

Hi I need to see all the indexes in a database. The ID has dbo rights to the database, but not to the master. I can't see anything in INFORMATION_SCHEMA.CHECK_CONSTRAINTS or INFORMATION_SCHEMA.KEY_COLUMN_USAGE An sa ID for the master sees everything however. Thanks for your help Pachydermiti...more >>

MS has been nice enough to add the Cube and Rollup operators so I can have totals with my results. But out of shear meanness they won't let me use them unless I use a Group By clause in my select. I have a proc with 25 fields, and have no desire to Group By anything- when a certain field chang...more >>

Help! I have a table that has datetime format field, I exported the table to a csv while I dropped it and tried some other data, but now sql doesn't recognise the date format for importing, heck I don't! The dates look something like: 40:58.1 Whick means nothing to me, or any of us her...more >>

I have a problem with an instance of SQL Server that refuses to respond to a shutdown request. I've managed to shutdown the SQL Manager and DTC services but the sqlservr.exe process is permanently in a "Stopping" state. I cannot logon to the instance to issue a SHUTDOWN WITH NOWAIT command. ...more >>

Hello All, I'm trying to find out exactly what JOIN does eg. SELECT A.Name FROM Author A JOIN Publisher P ON A.SomeID = P.SomeID WHERE P.Country = 'X' I know what inner, outer, right and left joins do, but what does just JOIN on its own do? (Can't find it in help ei...more >>

Hi SQL Server 7.0 using stored procedures. I need to do a SELECT Top but I always need to find the record count for the entire SELECT statement. ID Name -- ---- 1 Abraham 20 Barrington 32 Callaway 54 Dennis 58 Emmett If I do a SELECT TOP 3, I'll get the requi...more >>

Hi, Any suggestions for a good intorudction to OLAP querying with MDX? Thanks, Ron. -- Performance Intelligence, Inc. Spy 4 DB2 - http://www.pireporting.com/spy4db2.html ...more >>

Hi! For the sake of simplicity, I have three tables, Employee, Department and Work Employee >---- Department \ / \ / ^ ^ Work The Work table have two columns, empno and depno and consists that the employee has w...more >>

I am having performance issues on a SQL query in Access. My query is accessing and joining several tables (one very large one). The tables are linked ODBC. The client submits the query to the server, separated by several states. It appears the query is retrieving gigs of data from the table ...more >>

Is it possible to execute a stored procedure in one database, which then itself executes a stored procedure from another database? We have decide to split our data into a tree structure (DB1) and data blobs (DB2) (we are using MSDE and we have a 2gb limit with each DB so we've done it this way...more >>

A colleague wants to insert many millions of records where the values are computed in a C++ program. He connects to the database with ODBC, and does an INSERT for each row. This is slow, apparently because each INSERT is a separate transaction. Is there a way to delay committing the data u...more >>

hi everyone Could someone please help me in following: One of my database in SQL 2000 going Offline automatically. When i bring it back Online its Ok for 20/30 minutes and then again appear as Offline. I had similar problem when one of the database keep going to 'Single user' automatically. A...more >>

Hi everyone, I am a complete noob with stored procedures. I was asked to modify the following stored procedure so that it includes the following conditions: IF InvWarehouse.DateLastSale is null then use InvWarehouse.DateLastStockMove else IF InvWarehouse.DateLastStockMove is null the...more >>

Hi, I have an access db which I regularly import into SQLServer after it has been updated. The access db has new records added and some records taken off so I use the DTS wizard to delete the destination table and recreate it with all the new data. My current process needs changing as I ...more >>

I have a MSDE database that I am trying to connect to with JDBC. I don't have SQL Server installed. Is this possible? When I try to connect I get the error: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer] Login failed for user 'sa'. Reason: Not associated with ...more >>

Hi all! I have a problem with a temp table. I start creating my table: bdsqlado.execute ("CREATE TABLE #MyTable ...") There is no error. The sql string has been tested and when it's executed in the sql query analyzer it really creates the table. After creating the table, I execute an ins...more >>

I have a job which runs on one of our servers overnight. For 3 months or so it has run successfully. In the last 12 days it has failed 9 times, curiously running successfully at the weekend and on one other occasion. The job has 4 steps and always fails on the 4th step which runs a DTS packag...more >>

I have written a generic script in asp to add records to a table. The script works fine with one table but in the other tables it updates the first record in the table with the values for the new record and adds a new record with all null values?!? Here is the script: adOpenKeyset=1 adLockOp...more >>

I have two similar stored procedures which I'm running. One runs and one doesn't. I can run both with no problems in SQL Enterprise (7.0 standard) and have checked the permissions and am happy with them. Whilst the statements in each sp are different, I'm calling them in exactly the same way (us...more >>

Help :-) I can't see how this should be laid out! Consider the scenario of a car rental company. They have "cars" and "lots" (nationwide) and "customers." So far it is easy to query each of these. A car is rented by a customer so there would be say a "rentals" table. A car is rented and r...more >>

use database db1; use database db2; use database db3; .... Duration for this example is 3 second, size of database db1, db2, db3 is 100MB each, Does anyone know how to speed up this !!! Win2000SP2, MSSQL 7.0 SP4...more >>

I got help from Dan Guzman on how to set single-user-mode. Thank you very much. After seting single-user-mode, I run DBCC CHECKDB ('MYDATABASE',REPAIR_REBUILD) Tehre are error messages. How can I repair torn page problem? Thanks. *** Sent via Developersdex http://www.developersdex.c...more >>

When I try to connect to a MS Access 2000 database in enterprise Manager , i get an error "Login failed - Catalog information cannot be retrieved". I get this error message in a dialog box about 5 or 6 times. The database file location ends up in the file location box. I can clic...more >>

Hi, I have two tables in differents databases : Master database : ServerInformation where there is a table called "Clientes" and Table "Documentos" in the Database Index2003 What I need to do via Trigger is update the table "Documentos" in the field "Cliente" everytime the "Clientes" table ...more >>

How to encrypt a column in a table (i.e. which might contain passwords)? ...more >>

We are experiencing a problem with Sql Server 2000 linking to an Access 97 file. We have two machines that link to this .mdb file, and we recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. The link on this upgraded machine no longer works, giving this message: Server: Msg 7399, L...more >>

Hello, We have a database in MS SQL 2000 and I'm trying to link to it from MS Access XP. I've created a user with full access to one specific table in the MS SQL database, but Access won't write to it. I can open this table in MS Query Analyzer and log-in as the user, and I can do update, ...more >>

I need a query that will select the closest date. I have to tables Pricing and InventoryItem. tblInventoryItem InventoryItemID <- Pk Description tblPricing PricingID <- Pk InventoryItemID Price EffectiveDate I need to select all the current "prices" for each InventoryItem based...more >>

Is SQL Server 7.0 compatible with the Windows 200 operating system? Are there certain patches or service packs that need to be applied to SQL to make this compatibility work? Thanks. Larry ...more >>

Bonjour, Je viens d'installer SQL Server 2000 Dev edition. J'ai créé des connexions sécurisées SQL Server et pourtant lorsque j'essaie de me connecter avec PHP (mssql_connect('localhost', 'uselsC', '333')) j'obtiens cette erreur: Échec de la connexion de l'utilisateur 'uselsC'. ...more >>

Just a quick question about connection management. My application will never need more than 1 or 2 connections about at any given time. Also, I do not expect many users to be connected at any given time. For efficiency, I would like to keep connections alive throughout the lifetime of the ob...more >>

My dts package has to pick up a file which is in a mapped drive of the sql server... (system1)... when I run it from code the execution ends up in an error... but if I have the drive mapped (drive where I have the source file) onto the web server (system2) the execution goes through fine.. I don...more >>

I have a column defined as DECIMAL(10,6). I want to display it as a string but I do not want the trailing zeros. I cannot seem to get CAST or CONVERT or STR to exclude the zeros. Examples: 45.340000 --> 45.34 27.700000 --> 27.7 55.000000 --> ...more >>

Hello, I need help in writing a SQL statement in MS SQL Server 2000 to select the latest date (i.e., the date closest to or equal to the current date) for a given date. For example, in a table I have the following records: Date Exchange-Rate 01/Sep/03 0.55 05/Sep/03 0.5...more >>

Hi- Is it possible to do a trusted connection for a web server not on the same physical machine. By this I mean can I make a remote connection from a web server to a sql server using a trusted connection. If so, how? Thanks, Dave ...more >>

Hi all, I have an invoice table query that returns 10 records. ie. there are 10 invoices. When I try to join a subjects table to retrieve the subject name associated with an invoice it returns 11 records. I know this is because for each invoice, there might be multiple subjects. So when I j...more >>

I have an entry form allowing customers to enter up to 15 skus (product id) at a time, so they can make a multiple order, instead of entering one sku, then submitting it, then returing to the form to submit the second one, and so forth. From time to time, the sku they enter will be wrong, or d...more >>

Hi, I have a table (SQL Server 2000) with several date columns in it, all of which are individually NULLable, but in any one row, not all the dates can be NULL. I want a query which ORDERs BY the earliest date it finds in each row. I'm guessing I have to do this in two steps: ST...more >>

I have a SQl Server 7.0 database that needs to be migrated to another server and to SQL2K version. For absolute simplictiy is this possible 1. Upgrade existing SQL7.0 database in place to SQL2K 2. Backup all databases including System DBs and restore to new Server Will backup/restore o...more >>

This is the case.... I would like to learn the statement that make the relation between these tables. Why? Cos these are separated in two different databases and if a user make an update in a table from database X these changes must to be applied in the other table in the another database: ...more >>

This is the case.... I would like to learn the statement that make the relation between these tables. Why? Cos these are separated in two different databases and if a user make an update in a table from database X these changes must to be applied in the other table in the another database: ...more >>

Hi, I'm using SQL Server 2000 MSDE on a laptop running Windows XP. I have a couple of SP's that that quite some time to compile. So I was wondering: is there any way to have the database *not* recompile them every time after a reboot? BOL says: "As a database is changed by such actions as...more >>

Hi, The following parameter query resulted the error below. Is the following syntax correct? select * from branch where branch_code = ? [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error Thanks *** Sent via Developersdex http://www.developersdex.com *** Don...more >>

How do you find the number of licenses that are installed on a SQL Server 2000? ...more >>

Hello - Anyone have any thoughts on which API to use - ADO or ODBC? I have a fat client written in C++ using MFC ODBC classes to access a Jet database. The app is going to be modified to write to a SQL Server central database with multiple users accessing their local copies of the database ...more >>

If I have a table with multiple foreign keys to various other tables, what's the best way to index them: one index that contains all of those columns, or multiple indexes containing one foreign key column each. Don't know if this makes a difference, but my foreign keys are not explicitly define...more >>

Hi everybody, How can I Update a field from another table by Trigger? Can someone send me the statment to do it? I have a table called Clients with fields : ID_Clients, Client And Another called Doc with fields : ID_Doc, ID_Clients, Client These tables are in different databases and I...more >>

Hi, I have a database stored in MS SQL 2000 and an application written in VB5, which connects the database via JET/ODBC. I have a problem with the UNION statement. When I run a simple query like: "SELECT field1 FROM table1 UNION SELECT field2 FROM table2" I get ...more >>

In our database is a decimal field with format YYMMDDhhmmss.9999999999 where the 9s are random digits. I'm trying to strip off just the YYMMDD and put it in date form. So far I came up with: SUBSTR(TO_CHAR(rec_num,99999999999),1,6) AS Date which returns YMMDD. For example where the rec_num...more >>

I use Blanks in ColumnNames ( I know that this isn´t very good, but a lot of code and querys had to be changed if I would remove all blanks in all columnnames). When I link this tables with ODBC in my ACC97 - project, some of the tables causes an ODBC-Error. Are there possibilities to workar...more >>

Hi, I was just helping a coworker optimize a query. He had two versions: one which used UNION for each value for which he was tallying results and another query which used GROUP BY. Here is an aproximation of what they were: Query #1: --------- SELECT 12 AS [Row], ISNULL(SUM(CASE ...more >>

Hi, I am having an issue with SQL server that slows everything to a crawl, and makes almost any query impossible to complete. Here are the symptoms: I stop SQL, I start SQL. The task manager shows SQL starting up and quickly allocates 50 MB of memory. I then open SQL Manager and I can open...more >>

Hi all!! Does anybody know how I can create a function in SQL 7.0?? I have to create functions that return a value that can be used in a select statement. I think SQL Server version 7.0 doesn't support CREATE FUNCTION, does it? Ex: Select MyFunction(Parameter) From MyTable Thanks a l...more >>

My new employer is CMM Level 3. As part of the CMM/Personal Software Process, I am required to create pseudo code for my stored procedure and UDF design. Has anyone done this? If so, can anyone give me some advice?...more >>

Hi, Plz, I need some info (SQL2000) :) A stored procedure is like this: "Select table1.id, table1.txt, (select table2.nr from table2 where table2.fk_table1=table1.id) as nr where table1.id<>10" The essence here is that "select table2.nr from table2 where table2.fk_table1=table1.id" ret...more >>

Hi is there a "SQL string" to obtain date and time of a SQLServer machine ? thank u ...more >>

I have 4 tables (AA,MAIN,CC,DD) Everything that is not in AA but is in MAIN I want put into CC Everything that is not in MAIN but is in AA I want put into DD Add everything in DD to MAIN Clear AA CC and DD Thanks in advance...more >>

After applying security patch MS03-031 (Sql server ver 8.00.818) a query that used to execute in under 2 seconds, now takes over 8 Minutes to complete. Any ideas on what the heck might be going on? I have tested this extensively and can say for certain that installing this hot fix is what has...more >>

I'm doing a big old join on one table with 10,000,000 rows, and another with 400,000 rows. As you can imagine, this is taking a long time. Is there any way to monitor the progress of the join after executing the sql statement (more specifically, from code)? (Oh, and any good practices for ...more >>

We are running three web sites in a clustered environment WLBS. The web servers are connected to a Database Server running MS SQL server enterprise edition licensed as per CPU license. The Windows 2000 Advanced Server Licensing mode on all the servers is per SEAT license. Our customers ar...more >>

I'm having a fit with a query for a range of dates. The dates are being returned from a view. The table/field that they are being selected from stores them as varchar and that same field also stores other fields from our dynamic forms. The field is called 'FormItemAnswer' and stores text, int...more >>