Hey I have a table with per agent, per month an amount. I want to sum all the amounts on the biggest month per agent I have a query as follows select (select ru1.amount from ru ru1 where ru1.enddt = max(ru.enddt) and ru1.agentid = ru.agentid from r where ru.enddt <= adat group by agentid ...more >>

Hi All, Just a quick one which I have not been able to find a straight answer for yet... When programming a stored proc, you can declare a list of parameters in the top. Currently I am using varchar 8000 to collect a long string of information which is then broken into an array from ente...more >>

I have written a dll which to put it simply, registers and sends a windows system message. I need to call it from a trigger which I have already, or write a similar function within MSDE. My problem is either or both.. I cannot figure out the syntax for passing the parameters to the etended proce...more >>

Hi Here is my trigger statement - CREATE TRIGGER dbo.trg_insert_expor ON dbo.expor FOR INSER A BEGI INSERT INTO [shipment (DELIVERY_DATE,NOTE SELECT (convert(datetime,pickupdate)),not FROM Inserte EN It works on first record when I inserted a batch of...more >>

Hi All, I'm new to SQL server and just wonder if transaction locks all underlying tables. For example, I'm trying to write an application with SQL server as back-end. I have one table ProductsIncome and one table SoldProducts, and several tables with information on current prices, full desc...more >>

hi guys, I need to select from 2 tables, - Cost and Special. Cost Table has all the values and special table on has the values which have been marked as special. Table Struture... Rate RateID Price Special RateID Price I need a query where I need to select from above ...more >>

In the following Inventory/Order system: - Items are Ordered/Invoiced (Stored in tblInvoice and tblInvoiceItem) - Items are then Shipped (Stored in tblShipment and tblShipmentItem) This allows for items in Inventory to be "Allocated": - Stock levels are maintained in each warehouse ( 5 i...more >>

I have a very simple query which returns a single row for each employee on each day (effectively returning a result set with (no. of employees multiplied by the no. of days) rows.) SELECT e.id, e.name, d.dt FROM Employees e, Days d I have another table 'Absence' (see below) which ...more >>

I have a trigger with the following code fragment: ---------------------------------------------------------------------------- ---------------------------------- UPDATE processes SET process_group_id = CASE WHEN dbo.fn_ComputeProcessGroupId(id) IS NULL THEN newid() ELSE dbo.fn_Compute...more >>

DDL BELOW: I am looking for a way to associate groups of locations (regions) with = one another for navigation purposes. The concept is that I should be able to provide options to refine or = expand a region selection based on some basic properties: If Set A represents the currently selec...more >>

Hi, Can any one tell me if there is any built-in function to split a delimited string as present in VB (SPLIT). If no, can anyone tell me how can I achieve this ? Thanks and Regards, Peri ...more >>

Hi, How can I do for this to work? CREATE PROCEDURE Run_Test (@Rate as float) AS SET @Rate = 0.74166666666666666666666666666667 INSERT INTO tbl_Error (ErrorDescription) VALUES (CONVERT(nvarchar(40), @Rate)) GO ...more >>

I have been having major problems getting some parsed strings put together. The current one I have been playing with is as follows: SQLserver 2000 @strsQL as varchar (40) @DBname as sysname SET @strSQL = 'INSERT INTO LSData.dbo.DatabaseInfo (db_name) VALUES (' + @DBname + ')' EXEC ...more >>

In running a stored procedure that simply performs an 'insert table(fldname... ) values @var..' SQL statement we are receiving a timeout after about 3900 records. We are testing using Access 97 and Access 2000(DAO) on both NT and XP machines with SQL 2000 as the backend. The environment i...more >>

Dear all can anyone plz explain me What is Unicode? and the difference between datatype nvarchar and varchar. Thnx Regards Ramesh :) ...more >>

Using the table below, I have a query where I want to group the results by "day" and have one record for each day based on the max "creation_ts". As I was informed, this can be done with: SELECT max(creation_ts) FROM tableName GROUP BY day docid creation_ts ...more >>

I have the following Query select [Custinv].[RegisterNo], [Custinv].[LocationNo], [Custinv].[InvoiceNo], [Custinv].[CustNo], [Custinv] [InvoiceTime], [Custinv].[InvoiceTotal], [Custinv] [InvoiceDate], [Custinv].[Method_Of_Payment from [Custinv where [Custinv].[InvoiceDate]='2004-05-22 00:00:0...more >>

I have for example table tblWarehouse with fields: id warehouse product quantity productID ------------------------------------------------------ 1 warehouse1 product1 20 1 2 warehouse1 product2 30 2 3 warehouse1 product1 10 ...more >>

I have a function getintoarriveorqueue, If defined "simply" as follows it is taking upward of 20 seconds to run. create function GetInToArriveOrQueue( @Fac as varchar(20) = 'Default' ) returns table as return ( select fjobno, foperno from getinqueue(@fac) union select fjobno, fopern...more >>

Hi, I am migrating my access database to SQL Server. I have a crosstab query in Access which is TRANSFORM Sum(qryDatafile2003.[Total Price]) AS [SumOfTotal Price] SELECT qryDatafile2003.[Product Code], qryDatafile2003. [Sub Product Code], tblExpenseCodes.[Report Client], First (qryDa...more >>

I am trying to learn how to use some of the system databases in stored procedures and have come across some things I don't understand when looking at the system stored procedures bundled with SQL Server. sp_helpfile code has: (case status & 0x100000 when 0x100000) I have seen this type o...more >>

Hi, In one of my customer place, he is having 5 concurrent connection license. I am desinging a 3 tier architecture, where the middle tier which is connecting to the SQL server database is present in COM+. How do I ensue that I use only 5 connections at a time. What will happen if there are 6 ...more >>

Hi all I need to get the field that has been updated through using a FOR UPDATE trigger. At the moment, I am checking each value individually, eg: ----------------------------- IF UPDATE(Field1) BEGIN Set @SearchFlag = 1 Set @Field = 'Field1' END ELSE IF UPDATE(Field2) ...more >>

Hi All, Our office still working with SQL 6.5. I have a stored proc. that has more than 16 tables and SQL 6.5 doesn't allow more than 16 tables in procedure. Is there a fix or a way to force it to accept 16 tables. Thanks Angelle aadlay@co.santa-barbara.ca.us...more >>

Case DECLARE @v_DIM TABL [URN] [INTEGER] [NAME] [VARCHAR](255 INSERT INTO @v_DIM ([URN], [NAME]) VALUES(1, 'FUNCT1' INSERT INTO @v_DIM ([URN], [NAME]) VALUES(2, 'FUNCT2' INSERT INTO @v_DIM ([URN], [NAME]) VALUES(3, 'FUNCT1' INSERT INTO @v_DIM ([URN], [NAME]) VALUES(4, 'FUNCT2...more >>

I'm trying to delete records from a table for all people over the age of 65. I have to derive the age based off of todays date - the year from the dob column. dob column is char(8), not DATETIME. I can get to a point with the select statement to show me all records in the table, but I can't appl...more >>

I have an issue where we need Instead of insert triggers on a table with an Identity Column. In this scenario (example below), once the Trigger has been created the SCOPE_IDENTITY() returns NULL because the actual insert has moved into another scope (that of the trigger) and therefore the value ...more >>

Hi guys, According to http://www.sql-server-performance.com/sg_sql_server_performance_article.asp article, they mention about SQL Server: Buffer Manager - Free Buffers in perfmon. however, I went to perfmon in my computer, I can't find Free Buffers section, I see Free Page in SQL Server:...more >>

Hello, I'm very new to SQL and have just jumped right into the middle of it without any prior SQL experience. What I have is a table with a column [Part #] and I need to add 'JENN' before each of the part numbers in that table. How would I go about doing that? Thanks in advance!...more >>

I recently attended the MOC class 2073A Programming a Microsoft SQL Server 2000 Database. On page 12 of Module 9: Implementing Stored Procedures, it is stated that "If one stored procedure calls a second stored procedure, the second stored procedure can access all of the objects that the first...more >>

Hi, When one does a "SELECT INTO" in SQL 2000 and specifies a Temp table does this table actually get called by the name you give it or is it some unique name? I want to make sure that two people will not try to create the table at the same time. If this is an issue how do you avoid conflict...more >>

im using the following SQL query to create a recordset, Can i use somethig similar to update all the tables in on hit. Each table has a field CBIDispatchedDate which is a datetime field. I want to set the CBIDispatchedDate = GetDate( Select t1.Licence, t1.CompanyName, t1.Software Case Whe...more >>

Say I had 2 dates, @fromdate & @todate and i wanted to find the number of days inbetween, i would use datediff but how would i exclude saturdays & sundays if i only wanted the number of working days between the 2 dates? Cheers, Craig ...more >>

I need a calculated column I have DateBilled DateReceived and then AmountBilled. if DateReceived isn't null I want AmountReceived to be a calculated column that has the AmountBilled value I can't get it working below is my example code. select DateReceived,case DateReceived when NU...more >>

Hi Does anyone know a source for the contents of the SQL Hotfix which updates SQL2000 to version 8.00.919? I have the hotfix and ran it on a development SQL server but I cannot find a definitive list of the contents. I've been given one article from microsoft (http://support.microsoft.com/default....more >>

Hi all, I'm trying to change an incoming field from a CSV (the CSV is actually imported into a staging table first, so any queries will be run against that table), and split it into two fields to insert into the destination database table. The incoming field is a textual representation of t...more >>

I would like to retrieve the values of columns not used in by an aggregate function. For example, if I have the following table: Rank | Value 5 | 1 2 | 0 9 | 1 I would like to be able to select the row with Rank = 9 using the MAX aggregate functio...more >>

I need to find a way to programmatically determine the size of a MSDE database in order to warn the user to clean up old information. Is there a stored procedure or a table/view that I can access to determine the size of the database? Thanks, Dave...more >>

Hello all. We need to generate a recordset from SQL Server with the person's name from the tblGeneralPerson, where the person's first/last names are, and tblSchools, where the people's ID's are. The schoolPrincipalID and schoolSuperintendantID are identity values in tblGeneralPerson. Here are the...more >>

Is there any way to create a database connection from within a sp - preferably by using tsql? I have an ASP/MSSQL 7 web application which uses a number of cross-database views, which are very slow if there is no connection to the remote database. I could create the connections from the asp ap...more >>

Greetings I have a customer that wants to update all of the rates on a multiple table join. My boss wants me to use a view to accomplish this, but I can't seem to get it to come out editable. I create the view perfectly, but I can't edit the data when I open it in access. Any help or thou...more >>

I have a couple of stored precedures which create temporary tables. (create table #mytemptable) If either of them fail, they leave their temp table behind which means they generate the error 'table #mytemptable already exists... and refuse to run until I have manually deleted it. Ca...more >>

Hi How do I write a query that returns min, max & average for every hour in a given time frame In SQL Server 2K, I have a table called [AnalogHistory] with 3 fields ‘DateTime’, ‘Value’ and ‘SignalName’ There are approximately 25000 signals that are logging datetime and value, in r...more >>

hi I have a requirement to hold 50000 (and more, since the tasks grow with the business being handled by my company) future-dated tasks in a 'job queue'. In Oracle this is accomplished by using dbms_ijob, to which I provide job_id, what (the job/task to be run), and next_date. Each jo...more >>

Does anyone know of a way to create a table with a table name of the current timestamp? I would like to create a job that will create a new table every time the job runs with the current datetime as it's name. Thanks....more >>

I am creating some very large tables in MS SQL 2000. I am using VB code in an MS Access 2000 DB. It reads .csv files linked into the Access DB, does some process work to massage the data, then adds records to an SQL table via an ADO recordset. This has worked fine for loading roughly 150,000,000 ...more >>

Hello, Referring to the posted topic :Change datatype on all table (HELP, HELP, rush), helped me to start with what i want to do, i.e. alter tables in my huge database(250+ tables) from char to nchar and text to ntext BUT since there are many tables with constraints defined on them,the given piece ...more >>

I have two SQL 2000 tables, Folders and MailItems, I am unable to UPDATE Folders table. I am trying to UPDATE Folders.TotalSize with totals from MailItems.Length column. In other words, SUM(MailItems.Length) for each FolderID, and UPDATE Folders.TotalSize. When UPDATE is finished, the Folders ...more >>

Hey, Is there anyway i can change the datatype of the user-defined datatype without dropping it and all tables/SPs etc that are using it? Thank You in advance, Martin....more >>

Is there an equivalent in SQL Server to the autonumber data type in Access? ...more >>

Can I assume that if I have an identity field set when I add a record to a table, the identity value will never be used again for any other record? IS THIS TRUE EVEN IF I COMPRESS OR DO OTHER MAINTENANCE ON THE SQL SERVER? Thanks in advance. Jim ...more >>