Hi guys Has anyone here had any experience with internal rate of return (IRR)calculations on SQL Server. We have an asset management system where a client invests money and then makes transactions over a period of time. We need to calculation an IRR value for inception, year and quarter data. ...more >>

I am working on a project with an IP address database. When the engineering types are assigning addresses to our hosting customers, I would like my app to return the largest "contiguous" block that is available to meet their request. For Example if customer XYZ comes along and needs 10 IP Add...more >>

Hey all, Below is some code I'm working with. What I'm trying to do is create an integer variable which increments +1 each time a script is run. The script is supposed to detect that tables "Ctrans1, Ctrans2, etc..." already exist and create the next table in the sequence. My question: is it pos...more >>

Hi! I have a database where a field in a certain table needs to be updated with data from a field in another table. Scenario: table 1 (t1) has a description field (df1), and numeric field (nf1) table 2 (t2) has a description field (df2), and numeric field (nf2) t1.nf1 has some inaccurate...more >>

Hi all, Is it possible with or without 3rd party addons to show graph like / pivot tables etc from the client browser. I appreciate that drill down reports are available, and I realise that there is an export feature to excel, but is it possible to already have the graph just appear on th...more >>

Hi All, SQL2000 server. I have a database system with more than 500 user tables, each table has 2 or 3 triggers. I am going to upgrade this database to a new application version. I have hundred more table need to be updated, before updating those tables, I need to remove all the triggers,...more >>

SQL 2K I have 2 servers A and B. I use windows authentication to get-into system. From A, I setup a linked server to B In query analyzer (log-in via 'windows authentication), tried executing SELECT * FROM B.db.dbo.Table , returning Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. ...more >>

Could MS SQL Server be setup to have no deadlock in this code (insert in transaction from one connection and select from another without closing transaction): //------------------------------------------------------------- var connStr = "Provider=SQLOLEDB;Integrated Security=SSPI;Initial cata...more >>

Hi, I've been scratching my head over this for a while now and it's driving me mad, so while I realise that this question isn't strictly relevant to the group I hope that a kind soul will take pity and point me in the right direction. I have a UDF that is trying to do the following: basical...more >>

I want to find out all the columns with type uniqueidentifier and which tables the columns are located in, but so far I could get the column names but can't find their table names. Here is what I got so far, could someone please help! select name AS COLUMN_NAME, ID from syscolumns where xt...more >>

Hi, I am creating a very simple stored procedure which goes as follows: CREATE PROCEDURE testCrimeRate AS If dbo.Data2003.Population = 0 BEGIN UPDATE dbo.Data2003 SET [Crime Rate] = 0 END ELSE BEGIN UPDATE dbo.Data2003 SET [Crime Rate] = ([Crime Index...more >>

With Windows 2003, SQL Server 2000: There are two applications using SQL Server. Most tables are unique to one or the other application. But there are abount 3 tables, which should be shared. How can this be organized? An idea might be to have two separate databases for the applications as w...more >>

Hi! I've built procedures in which I use to transfer data between databases using cursor. One of the procedures (just one of about 30) gave me error all the time and I just couldn't figure out why. By accident I changed the order of two rows and it started working!?! I cut&pasted the part...more >>

the store procedure is passing in the 'mm/dd/yyyy' as varchar instead of datetime. when the user enters the search condition for begin and end date, the result should include all events happened on end date, not just end date at 0:0:00 am. I tried to convert a datetime to varchar to be a...more >>

Hi all I am tring to have a sql job running everyday for coping files to diff location, the name of the folders like 20040703, 20040702..... I only need to copy previous 5 days folders. today - 1, today - 2, today - 3, exec xp_cmdshell 'copy G:\today-1 \\abc\c$\mssql7\backup\ Q...more >>

I did a test on DATABASE1 I did a complete backup, then I added a table called "TABLE1" with no data in it. Now, I restored my backup, of course I didn't see "TABLE1" there, so I tried to restore transaction log, RESTORE LOG DATABASE1 FROM DATABASE1_log WITH RECOVERY Ser...more >>

I have a table like this SystemOwner Microsoft SABRDReviewer Microsoft LeadAnalyst Microsoft LeadDeveloper Microsoft LeadQA Microsoft i want to have the output SystemOwner SABRDReviewer LeadAnalyst LeadDeveloper LeadQA --Columns -------------------------------------...more >>

Hi everybody ! I' d like to check data before inserting. Select @nb = Count (idCustomer) From Customer Where company like '%' + @company+ '%' If "PEUGEOT SA" already exists, @nb > 0 if I enter "PEUGEOT". On the other hand, if "PEUGEOT" exists, @nb =0 if I enter "PEUGEOT SA". I tried t...more >>

Hi all I would appreciate some help in getting information out of the system tables and into a user table that I've created. Basically, I've created 2 tables - SecFields and SecTables, and I need to do the following: 1. Insert all of the User tables in the database into my SecTables table, ...more >>

This is a pretty contrived example but I think it is sufficient for my question. The script at the end of this message sets up the example. Consider the following query: SELECT person.personid, firstname, lastname FROM person JOIN familymembership ON person.personid = fa...more >>

Hi all, I need to return records that contain the literal text value in upper case, in a char field. Most of the records are titlecased but some are uppercase. I'm having difficulty finding the string function or any other means to return the records. Any ideas?? Thanks, Eric ...more >>

Please help me optimize the update statement listed below. Thank You, Dan CREATE TABLE SHOWCONTIG_Update ( ObjectId INT NULL, DefragIndexUpdate CHAR (1) NULL, IndexId INT NULL, DateChecked datetime) update SHOWCONTIG_Update set DefragIndexUp = 'D' ...more >>

I have gotten Bulk Insert working with an uploaded CSV file, but when I try to automate the location of the file from which to do the insert I get problems, the syntax doesn't check out. I know I can control the location of the upload, but I can't control the name, so I am passing it to the sproc...more >>

When I write in the criteria DELETE BETWEEN 151975 AND 151980, it dosen't want to execute the function because no JobId's appear. The sql pane gives me the below: FROM Jobs WHERE ('DELETE' BETWEEN '151975' AND '151975') ORDER BY JobId DESC Why won't it run properlly and give ...more >>

I want to do something like this select * from exec spLC_Extentions_Test where MOD_COUNT >2 spLC_Extentions_Test exists and works and MOD_COUNT is a column created by spLC_Extentions_Test what am I missing here? thanks...more >>

Hi All, I hear that from my friend. I wonder it can be possible? Anybody knows this? "IN" keyword don't use index when querying database? Is it true? Thanks to all for helps. ...more >>

I receive Msg 245, Level 16, State 1 when i try AVG on more than one column within my select statement. Each AVG works on its own, but if i have both within the select statement, they both fail. the SQL Select statement is quite complicated so i have included it below: =======================...more >>

Hello I am running the simple query..... SELECT count(*) FROM errorlog WHERE failuredescription like 'Error validating the map reference%' Stating the obvious this returns the count for the column that starts with the string 'Error validating the map reference%'. I attempted to use GRO...more >>

My ... Open <Cursor> generates this error : SqlDumpExceptionHandler: Process 10 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. I am encoutering this error whenever I have a LONG Select statement when declaring a cursor... I don't know...more >>

I have problems with lock and deadlock in my database, which have recently grown and has about 150 new users (connections). Is it good idea to use sp_getapplock?...more >>

Hello, Can I have a stored procedure with 11 output argumentsin the stored procedure? Because i get the me error message "Procedure or function * has too many arguments specified." ...more >>

Dear Hi I want to generate Random Strings in SQLServer2000.Any way to do this? thanx...more >>