Hi all, We had a database that we upgraded to SQL2005. This is a highly transactional database and is about 20GB in size with about 200,000 rows in many tables. Our app is seeing serious performance issues due to queries and stored procs that take a long time to run.I am presuming these are...more >>

I've written quite a bit of code to manage db backup jobs in an exe I'm working on. Everything from adding to editing jobs is working fine. For some reason I get an error when the following code runs (it's in Delphi but you should get the idea): with TAdoStoredProc.Create(nil) do try ...more >>

I have to perform several counts based upon certain criteria. The first main decisioning branch is based upon whether or not there is more than one record with the same account (mr.account). I'm no whiz at complex SQL statements, but I think I've determined that the best way to do this is creat...more >>

Hello: I have two SQL Servers: one is production SQL Server 2000 (SS1) and another one is development SQL Server 2005 (SS2). I need to migrate from SS1 to SS2. For SS1 I have: Computer name: SS1 SQL Server name (default instance): SS1 Analysis Server name: SS1 For SS2 I have: Computer nam...more >>

Hello - I have a couple of apps using a Sql Server 2000 backend. Newer apps are being developed using Sql Server 2005. We have a new server and my boss would like both Sql Servers to co-exist on the new server instead of having two different servers, which is the case now. I'm rather r...more >>

I think I've seen this before but am not sure. Is there a way to insert rows into a table from a stored procedure? For example, I'd like to run sp_spaceused and output the values to a table. This is the idea: SELECT EXEC sp_spaceused 'customers' INTO #tablestats. I know that doesn't work but is ...more >>

hello! the problem I run into is the following: if I use the query: SELECT YEAR, PERIOD, NUM FROM OPENQUERY(Oracle, 'SELECT YEAR, PERIOD, NUM FROM schema.table') AS derivedtbl_1 WHERE (YEAR = 2006) AND (PERIOD = '10') it will take forever because the sequel is actual...more >>

Why does the following in QA: print RIGHT(CONVERT(DATETIME, '22:15:45', 108), 7) show this: 10:45PM and not this: 10:45:15PM? I expected to see seconds. How can I convert integer time such as 224515 and 127 to character time such as 10:45:15 PM and 00:01:27 AM? Is there a pr...more >>

I need to one-to-one map a range of integer (A), say 1 to 10000, randomly to 1 to 10000 (B). And I need to get A if a B is given. Any existed good algorithm in T-SQL?...more >>

I am using VC++ 7.1, MFC, SQL Server 2000, and Visual Studio 2003. I want to do array inserts aka bulk inserts. In other words, I want to insert a hundred rows into a table in one round trip. Of course I could generate a large SQL script as follows insert abc values(1,2,3) insert abc values(1...more >>

When somebody drops a view or a column, some stored procedures become invalid. I would like to find all invalid ones. I tried to look up how Enterprise Manager checks their syntax, but it simply recreates them and it fails to detect errors like this. Even when I replaced a valid table name with ...more >>

I have this stored procedure that is actually executed from an Access application. The UPDATEs seem to be happening just fine. But the INSERTs are not. Can someone take a look at this and let me know if I'm doing this correctly? The Stored Procedure needs to handle a data store if the row ...more >>

When I do the following I do not get any records returned: declare @CaseNum VARCHAR(50); set @CaseNum = '''123-4567890-123'',''321-0987654-321'''; select * from record where case_number in (@CaseNum) I'm trying store a programmatically generated list of comma delineated string values ...more >>

I want to call a function omitting an argument to which I have assigned a default value. Apparently, I am calling the function incorrectly because I am receiving the error message "An insufficient number of arguments were supplied for the procedure or function dbo.fn_char_count." I've declar...more >>

Hi folks, I have just examined a few examples for clr stored procedures from internet. I have two questions as below: 1. Those examples use .net code, "SqlPipe.Send(ISqlReader reader)". It means that the returned result set will go to the result pane of the SQL client tool. My ques...more >>

I was working with a two dimensional array in my method that got too big and gave me: Msg 6532, Level 16, State 49, Procedure spPackTime, Line 0 ..NET Framework execution was aborted by escalation policy because of out of memory. System.Threading.ThreadAbortException: Thread was being abor...more >>

1. Record created locally at 14:00 on July 1st. This is the local time the user sees on the screen. 2. Since it's July and I'm on the East Coast (USA), record goes into database as 10:00 UTC. 3. If the user recalls this record in a report in August, the presentation layer converts back to lo...more >>

I've got an issue where a join takes about 3 minutes to run on a development server (2 X 1.4 GHz/ 2GB / Win2K3SP1 / SQL2KSP4 (8.00.2187) standard) and does not finish (even after 10 Hours!) on a monster (8 X ? GHz/ 12GB / Win2K3SP1 / SQL2KSP4 (8.00.2187) clustered Enterprise) data center test ...more >>

Hi, I got a column b which has the following values for the column. First Row: 5313.4566 Second Row: 1757.8125 Now I need to write a sql on column b so that I get 5313 in the first row while 1758 in the second row of the new manipulated column c. Any hints. Thanks in advance....more >>

Hi All, I am working on SQL2000 SQLOLE objects now but I found that there is very few information of SQLOLE objects, e.g. object list; properties and methods for each objects which blocks me from using sp_OA procedures Can someone share the link or document of these information? Great thanks...more >>

using sql server 2005, .net 2.0 Is it possible to determine the CLR UDF, SP etc methods availabe within an assembly from sql server via a function or statement? -- kevin......more >>

is there any equivalent of MINUS set operator in T-sql. ...more >>

Hi, Simple question: I would like to retore a backup database as a copy with a different name. So now I would have 2 identical databases but, with different names. When I run this I get errors complaining about overriding mdf files and ldf files. Well I don't want to override those fil...more >>

hi all I have a fox pro application that inserts , deletes and selects data in an SQL server 2000 SP4 database , when I work on my XP Laptop i dont have problems but when I launch the application locally on a Windows 2003 Server (2 processors, more RAM better disks ) the process takes more ...more >>

I need some help here, if you don't mind - - - Here's the setup of the three tables: BUDGET (PK is Budget_ID) BDSTORES (PK is Store_ID, with FK to Budget_ID) Then, Rate_Detail (PK - Rate_Detail_ID - FK to STORE_ID) I can delete all stores referencing the budget_ID fine - BUT - I need to del...more >>

Alright, I have this table called Tags. The three columns of interest are Tags.Id, Tags.Name, Tags.ParentTagId This is the query I am currently using: Select Tags.Id, Tags.Name, Tags.ParentTagId >From Tags WHERE Tags.Id IN ( 22536, 22535 ) This outputs to: Id Name ...more >>

I'm new to version 2005. Can I program using the Developer edition of SQL server 2005, but when it comes time to deploy, install the program on the client's computer where they would use SQL Express and not have the full version on any server? I'm coding in Visual Studio 2005. Also, this pr...more >>

On a Sql Server 2005, suppose the query SELECT ...... FROM A LEFT JOIN B ON B.Col1 = A.Col2 LEFT JOIN C ON (C.Col3 = A.Col4 OR C.Col5 = B.Col6) If the left join on B results in 1 or more rows, then I get all the rows from C: the rows that apply to the first condition and the rows that app...more >>

Our VB6 app uses an ADO command object to execute a stored procedure. We found out that while running the SP from the VB app takes about 15 seconds, running the same SP with the same params from Query Analyzer takes less than 1 second. After checking things out thoroughly, we discovered that com...more >>

I am using CTE (Common Table Expressions) on SQL 2005 to create a recursive query. I have written the following query USE Ensight; GO WITH DirectReports(BaseCategory_ID,Category_ID, CategoryDescription_STRING, Level) AS ( SELECT BaseCategory_ID,Category_ID, CategoryDescription_STRING, 0 ...more >>

Have Anybody worked on MLM -Binary Plan Database Design. I would like to know abt the database design and the payout calculations. How to calculate and link members with one another. thanks Rahul ...more >>

Hello Guys, I have question concerning IF EXISTS (this statement is not correct but I would like to test if this select statement is true, if it is I qould like to insert the @DATE, @VALUE_, @S, @T of table @QT1 into the table QT) I would like to do a test: DECLARE @DATE_ datetime DECLA...more >>

Hi, I would like to ask experienced programmmers two questions about INSTEAD OF triggers on tables: 1. when to use them? 2. is standard action also performed? I mean, if I have some action in INSTEAD OF UPDATE trigger, will UPDATE ... SET ... be also preformed? Thank you very much! /RAM...more >>

Hello NG, I am migrating application from Oracle to SqlServer 2000 and would need some assistence: I need a trigger (update , insert), which manipulates data of inserted/updated row. for example on update the column 'modnr' should increment by 1 (indicating row data change to other user...more >>

I'm trying to trace several stored procs that call other procedures. I need to see the values that are being used inside the procedures. What events to I need to select so I dont see the variable names but see the values instead? Thanks, DougS ...more >>

Hi, Using MS SQL Server 2000 I am trying to hook up to a database. I have = created an update trigger that is supposed to insert rows in another tab= le = ONLY if the updates have changed in one of four columns and a certain = column is of a certain type. The trigger is as follow...more >>