I am working on MS SQL 2K and Java as backend. I want to store unicode data that is coming from form HTML page. The problem is the data that is getting stored to the database is ቅቚ etc instead of the correct letter value. I have declared the filed type as nvarchar. Pls pls help me, stuc...more >>

I have a large and ever growing database with over 15 million records, all timestamped, and increasing by roughly 30,000 records per day so efficient queries are essential. I need to pull a list of records that match certain criteria, that have timestamps in the last hour. and I need to run...more >>

I have three tables as follows: Table1: ClientID StudentID Name ---------------------------- 1 22 John 2 23 Chen 3 34 Ted 4 22 Bod Primary Key=ClientID + StudentID Table2: InternalID Cli...more >>

Hi. I have seen lots of discussion. My starting problem was that after installing sql server 2000 and sp3, I could not debug stored procedures. I thought I saw the solution in MSDN, but it did not work for me (I could not grant execute on sp_sdidebug to my user). short end is.. I had to...more >>

I have this table that contains multiple messages in serverA. I need to update the similair messages on serverB if messages on serverA is updated. ServerA select memb.memberNo, memb.sub, omsg.comment1 + ' ' + omsg.comment2 + ' ' + omsg.comment3 + ' ' + omsg.comment4 + ' ' + omsg.commen...more >>

Hi I have a table which stores the customer information....There is a Customer Address column defined as nvarchar(50)....Data for the table is imported using DTS Bulk Insert from a Customer.dat file.... My question: - 1. What are the requirements for me to view the foreign characters (A...more >>

I have a table, named car. It normally have 10000 records and will growth in the furture. Administrator can delete the car record through an web based administrator platform And we not like delete the car record physically. My question is that, what is the best method to keep the database ...more >>

Hey, I have a need to query on 2 tables as follows: SELECT Table1.*, Table2* FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID What I need to be able to accomplish is to return the rowset that includes only ID's that have the same ParamID specified more than once (> 1) in [Table2]. ...more >>

Hi All When I create stored procedures that reference missed object I have noticed that my SQLServer dosn't throw the warning "Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object ...." Is there any witch to turn ON in order to have this mess...more >>

I'm trying to import a simple excel spreadsheet into MSDE using = OpenRowSet, but receive the following error: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. =20 Connection Closed here's the sql: select * into MyTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',=20 'Exc...more >>

Hello, I'm trying to insert into another table where select clause contains a join with another table. However the number of columns of the source does not match with the destination table. How can i fix this without specifying all columns? ...more >>

Hello there I have huge database on sql server. The database probide tables, views, store procedures and functions Some of the views or the store procedures are depend on the functions, store presedures are depend on the views. When i update the version I generate SQL Script and run it on...more >>

Hi, I have a problem. My problem is : I have two tables reprezentoing a tree, first table representing nades, and second representing links first - ID, Name second- ID1, ID2,weight --------------------- ---------------------- | 1 | name 1 | | 2 | 1 | 6.0 | | 2 | n...more >>

Hi everybody, We have an stored procedure at master database that runs when SQL Server starts up (exec sp_procoption N'sp_ICarDMSStartup', N'startup', N'true'). This stored procedure calls xp_cmdshell for retrieving hard disc information: INSERT #xpoutput EXEC master.dbo.xp_cmdshell "di...more >>

I am doing an insert with a stored proc using the ExecuteNonQuery in the DataAccess Block from Microsoft. My parameters are inserted correctly into the database but my return code is always a -1 instead of 0. Please review this code and tell me if you see something I am doing wrong> Thanks ...more >>

I have a table which has a number of large notes columns where users sometimes need to record several pages of notes for each column. So if I have 2 columns each varchar(4000) then at times the row size will exceed its 8k limit and nasty problems will occur. How is it that people are able to...more >>

I have a medicationtable that looks like this: Acct Medication Active 1 Drug 1 Y 1 Drug 8 Y 1 Drug 9 Y 2 Drug 1 Y 2 Drug 3 Y 3 Drug 1 ...more >>

hi all i have 2 tables with identical columns. How can i get a sum of the 2 tables: select sum(QTUM1) as pieces, sum(QTUM2) as volume, sum(QTUM1O) as [Pieces free], sum(QTUM2O) as [Volume Free], sum(FATTM) as Invoiced from stat001f where (myYear = 2004 and myMonth = 9) select sum(QTUM1) a...more >>

Hello, I am developing several database centric applications. Each application has gotten one or more databases. I discovered that some applications share some functionalities around database tables. For example, Application A will query the Members, Groups, MemberPreferences tables in datab...more >>

I am running an osql using input script which runs a database backup. Is there a way I could get a return of 1 or 0, 0 being success and 1 being error. I tried using -b option but it returns the entire details of failure followed by the return number and if i use -o paramater I dont see any resu...more >>

SQL 2000 Current Query SELECT YEAR(T1.RecievedDate) AS 'YEAR', DATENAME(MONTH,T1.RecievedDate) AS 'MONTH', COUNT(T2.LedgerKey) as 'COUNTS' FROM BacsHdrYearly AS T1 JOIN BacsTrnYear AS T2 ON T1.LedgerKey = T2.LedgerKey GROUP BY YEAR(T1.RecievedDate), ...more >>

Is there a better way to do this query? I was hoping to do it with a CASE and IF... SELECT TOP 100 PERCENT CustomerNumber, InvoiceNumber, InvoiceDate, DocType, AgingBucket, CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END * CASE WHEN AgingBucket = 1 THEN Amount ELSE 0 END AS '...more >>

Hi, What's wrong with the following stored procedure? CREATE PROCEDURE [dbo].[usp_Status] (@Status varchar(30)) )AS if(@Status= "Open") { select * from INVOICES where OPEN_STATUS="T" } else if (@Status= "In Process") { select * from INVOICES where INPROCESS_STATUS="T" } ...more >>

Hi, What is wrong with the following stored procedure? CREATE PROCEDURE [dbo].[usp_Status] (@Status varchar(30)) )AS if(@Status= "Open") { select * from INVOICES where OPEN_STATUS="T" } else if (@Status= "In Process") { select * from INVOICES where INPROCESS_STATUS="T" } ...more >>

Hi! We are developing cgi applications, using ODBC and (among else) MSSQL. Now we are experiencing deadlocks on MSSQL only, and the problem is - they occur not when the query is submitted, but when rows are fetched. We don't use transactions with several statements, so basically every que...more >>

Hi, I have a field called StreetNo in a table called Prospects, the field is a varchar(10). The table is populated by someone else, so I have no control over what kind of data entered into the table. The StreetNo I got is a complete mess, e.g. 00001, 01234, 01234a, 0000 PO Box. Is there a...more >>

I need to update a current row(s) from a previous row(s) the keys are CSIID and CSIDATE If i want to update all rows for today from the values from yesterday i would need to match the csiid = csiid and check for the dates Would it be Update [todays rows] where [todays rows].date = toda...more >>

I need help writing a query that can tell me the number of records with the same content. For example, this is my database (Data1 only has 1 column) Table Data1 Column numbers 234 322 2323 234 453 234 412 2323 ----------- the query should like something like this Select ...... From...more >>

I tried to add 7 bit filelds together but it failed. I have 7 bit fields to indicate days of week that person works, e.g. Mon, Tue,.....Sun and want to add them together to come up with # days worked in a week. I was trying [Mon]+[Tue]+[Wed].......+[Sun] but it failed. Is there any easier w...more >>

For testing purposes I would like a query that is configurably slow. Like a mathematical query with a maxcount variable... Any hints? - Kristoffer - ...more >>

Hello Experts, I am using SQL Server 2000 (SP3). May I ask you a question regarding programming in SQL Server? I have an "Opportunity" table (more than 30 fields with various data type) and an "AuditTrail" table. Whenever there is an update action to one or more fields in the "Opportunity...more >>

Here is what I have so far: SELECT DISTINCT SPEEDLOCATIONS.HWY, SPEED.SPEEDMPH, SPEED.DATETIME, SPEEDLOCATIONS.[CROSS] FROM SPEEDLOCATIONS INNER JOIN SPEED ON SPEEDLOCATIONS.ID = SPEED.ID WHERE (SPEEDLOCATIONS.CITY = 'HOU') ORDER BY SPEEDLOCATIONS.HWY, SPEEDLO...more >>

Is there a way to disable/enable all SQL Agent jobs using a set based operation as opposed to a cursory approach that i can think of right now If so, can one provide the SQL for it ...more >>

Hi I am trying to create a indexed view. This view will run on Standard Edition Server so I have to use the WITH (NOEXPAND) option. However I am getting the following error - Server: Msg 8171, Level 16, State 2, Procedure ActionList, Line 4 Hint 'noexpand' on object 'dbo.Action' is invalid. ...more >>

I have a database hosted on a comercial web server I can access it using SQL server Entreprise Manager I have never backed the database up though i copy it regularly. Exactly how do you recover the database to an earlier point using the Transaction Log and can it be done to recover 'Dropped'...more >>

background: sql2k and active directory, using windows authentication. I'm wondering if anyone has done this successfully in the past and is willing to share the how to... pretty basis stuff, just a list of users and the windows group they belong to, something like... user1 group 1 user1 ...more >>

Hi, I'm writing sproc based on server A, which during processing needs to create a select statement with a join from a view on server B. I only have access to the view on server B through use of a specific sql login, which I obviously also have the password for. The sql login and password o...more >>

I have a table which, until now had an update trigger only. That trigger was used to record the record as it existed BEFORE the change, and who made the change. It works fine. Now I have a situation where a program will INSERT a row into the table (which now has a new field). The value of ...more >>

Hi Gurus, I am doing Backup and Restore. But while restore it restore from the previous backup. What am I doing wrong. BACKUP DATABASE TestDB TO DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TestDB.bak' RESTORE DATABASE TestDb FROM disk='c:\Program Files\Microsoft SQL Serv...more >>

Hi All, I have a small problem usint Distinct that I am sure will be easy for you guys and gals. I have a table (tblA) that has a 6 columns one of which is "SerialNumber". This table has 300000+ records. I want to be able to pull back one record for each of Distinct Serial number in the tab...more >>

Hi, Recently, i convert the database from access 2000 to mssql 2000. After that, i find access sql function ' iif( , , ) ' can not be run on mssql 2000. So, my question is that any function can replace 'iif' in mssql 2000? Please give an example. Remark: If.. then..else can not be used in vie...more >>

Hi all, I am getting the following errors in the event log. Table error: IAM page (1:185442) (object ID 2041058307, index ID 255) is out of the range of this database. Table error: IAM page (1:161784) (object ID 2105058535, index ID 0) is out of the range of this database. How do I ...more >>

Hi, I need some help to de-duping an orders table. I have an orderitems table containing OrderID, OrderItemID, (plus a few other columns), and we need to clean the data prior to migration. We have quite a few duplicates of orders, and in moving to a template, or set based model, we want to...more >>

Hi: I got following error. Anyone can help to determine what cause this? Collation seams fine on the server. DB Error: 10039 DB Error Message: Attempt to convert data stopped by syntax error in source field. OS Error: -1 OS Error Message: (null) Severity: 7 Thanks...more >>

I have a fairly simple stored procedure that accepts two parameters: 1. CompanyID (varchar) 2. Year (int) The Year parameter is optional. If the year is not supplied, then I would like all records for the particular company returned. If a specific year is supplied, then only the record ...more >>

i have to develop a cryatal report from SQL database. I have a option to choose stored procedure OR a view i need to know which will work better on a large network in terms of speed and performance Thanx in advance -- ssaud -----------------------------------------------------------...more >>

I was wondering if anyone knew if this was possible in T-SQL. I need to calculate the total number of hours carried over from a previous month, based on a 6 hours per day rate. Some examples, Job has 12 hours total and starts on 2/28/2005 it would carry over 6 hours into March. Job has 5 h...more >>

Hey all, the malfunctioning portion of a stored proc is below. Essentially, it's the part of the proc where sorting occurs. The error is occurring because the "daystosail" and "cnt" below aren't real columns, they're created on the fly within this proc using "...AS daystosail" and "...AS cnt" ...more >>

This has to be obvious but I can't see the problem. I am executing the query: ======================================= Insert Into TempTable (email) Values (Select Distinct N1.Email From Names N1 Inner Join Bands B1 On B1.BandDirector = N1.NameID Where B1.BandClass In ( 'A', 'AAA', 'AAAAA')) ...more >>

I am very new at stored procs and I have one that is puzzling me. I run the stored proc and it returns what it should but also runs the error handling code. I know this is very basic but I can't figure this out. Thanks and here is the code: CREATE PROCEDURE getSecurityLevel_sp (@user_i...more >>

I have a query that I have developed on SQL Server 2000 that times out when it's moved to a system running SQL Server 7.0. I've include the steps from the execution plan within Query Analyzer below that are taking 40% and 60% of the time. Stream Aggregate/Aggregate Computing summary value...more >>

I have a basic question about Triggers. I have a table within our CRM database that has information about contacts (tblcontacts) for our client base. I have a field in this contact table that is called datemodified. I want this field to be updated everytime any part of a record is updated....more >>

How can I join 3 tables that represent many-many realtions, in a way that returns all the values of one table and an extra column of true/false or null or some way to to show that the record exists or doesnt exist in the link table. This is so that when I pass a paramter of an author ID I can...more >>

Hi, Is there a simple way to get files "date modified" into table, any xp_sp?. the only solution I found is to use parsing on "dir" command whith the cmdshell. The motivation is to check data files downlowded every day are to be changed. Thanks, Hadar ...more >>

The data is entered correctly but identity of new record is always sent back as 1 . Why is this always returning a value of 1 ? sp: Create PROCEDURE AddPlayer @SQLCMD nvarchar(1000) AS BEGIN EXECUTE sp_executesql @SQLCMD END SELECT SCOPE_IDENTITY() ...more >>

Is there a mechanism in SQL Server 2000 for notifying client apps in a multiuser setting when a change has occurred in a table (notification event). And/Or, is there a way a client application can 'ask' if a table has changed? Thanks, Wyk ...more >>