SQL Server is not releasing Memory after operation is over from VB application...more >>

Hi, Does anyone has a some good advice/example how to store multiple values for a field. For single choice fields, i use a combobox in access and the value will be an integer number, but how about when a field has multiple choices? Should i use a listbox with multiple select enabled for vis...more >>

Hi, How can I select the data in a datetime field in "mm/dd/yyyy hh:mm:ss AM/PM" format? Thanks in advance. SKG...more >>

...more >>

Heres the syntax for the where part... set @sqlwhere = @sqlwhere + ' and contains (art.medium, '+ convert(varchar, @medium1)+')' when I do a print @sqlstatement..which is a combo of all the statements it prints this: Select distinct art.inventoryid, art.title From art ,artist Where ...more >>

SQL2000 - I want to compute the interval between a time in the previous row and the current row and update a field in the current row with the interval. Is FETCH the right way and may I have an example please. ...more >>

Hi, I want to insert a large number or rows (say 100,000) into a table of 4 columns. Each column will have identical values except for one (which will be an integer). Any suggestions as to the best way to do this (I'm writing the code in VB.NET). Thanks Steve ...more >>

Hi there, How can I make this smallmoney format conversion? 18.00 => 18 18.321 => 18.32 i.e - if .00 is present, ignore it, otherwise, display up to 2 digits after decimal. Thanks! ...more >>

Hi, I have a small T-SQL problem which I am hoping somebody may be able to help me out with. The senario is this I have two tables tblMember -- which simply holds the names of members tblMembershipPeriod -- which holds the start date of a members membership period, these membership period...more >>

Hi all, I'm new to this group and hope to learn more about SQL from your expertise. I have a question to ask and wish to get the answer. I have a table with 4 columns: Name, Value, DateTimeStamp, and Comments. Name column can have duplicated name (with different datetimestamp.) What I want ...more >>

A shop owner needs to run report based on 2 tables below: create table #Transaction (Tranid varchar(10), SaleAmt int, Mth varchar(10)) insert into #Transaction values ('101',50, 'July') insert into #Transaction values ('102',35, 'July') insert into #Transaction values ('103',25, 'June') ...more >>

Wondering if anyone has any clues on following problem I'm having with a linked server. This problem is something that occurs with WinXP SP2 and is documented in the following KB article: 839279 - You may receive a 7391 error message in SQL Server 2000 when you run a distributed transactio...more >>

Hi folks, I am building some fairly complex insert statements in order to perform a major data conversion from a legacy system. It has about 150 columns, 14 joins, and 4-5M rows in the result. I have spent a lot of time the QA, and it has helped me tremendously, but my insert still can take ...more >>

I'm basically looking for an idea here. I'm working on an app where I need to compute payroll hours. Payroll is submitted semi-monthly. However, overtime is calculated on a weekly basis. I already have my users enter the date range for the pay period when they print the report. But that is the e...more >>

Hi, I tried to use self-join to update two coumns in a table. Here is my query. update a.form_due_date = convert(datetime, a.due, 101), a.form_assessed_date = convert(datetime, a.assess, 101) from valid_output a, valid_output b where a.study_no = b.study_no and b.study_no = XXX and a.val...more >>

CREATE TABLE [temp_user] ( [user_id] [int] IDENTITY (1, 1) NOT NULL , [user_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [item_sold] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_temp_user] PRIMARY KEY NONCLUSTERED ( [user_id] ) ON [...more >>

Hi, Can somebody help me and tell me what's wrong with this insert statement? Insert Into CM_CHART_OF_ACCOUNT(GL_ACCT, GL_ACCT_DESC, ACCT_TYPE, INACTIVE, CHG_BY, CHG_DT) Select GlAccount, null, '9', 'N', 'MACC Conversion', getdate() From cv_ChartOfAccount Where GlAccount not in (select gl_...more >>

In a SQL 2000 trigger, I would like to use sp_getbindtoken to retrieve a bind token, and pass it to a process initiated through an extended stored procedure called by the trigger. If the process being called by the extended stored proc binds using sp_bindsession and the token obtained in the t...more >>

I have an MS Access mdb front end (not adp) hooked into a SQL 2000 backend. There are several ntext fields that my client wants to be able to use as Rich Text. I'm going to use Leban's Rich memo control. It works fine, I've tested it. The problem is that there is a LOT of existing data. I know t...more >>

Hi, I have written a stored procedure that inputs data into a specific table. The problem I am having starts when I try to get the guid of the specific record just entered. I tried using the inserted table object to retrieve that data but I get the error, Invalid object name 'inserted'. Can anyo...more >>

Hi All, Well I have been looking at this for hours and tried all manner of things. I am hopping that some one can give me an explanation to my problem. I have built this Stored procedure that will import data from and excel workbook. And obviously it does not work. See the part that does ...more >>

Ok. I have three tables. One table stores different "categories" that a section of my site contains. Another table stores "groups", which are smaller subsets of content. The same "group" can exist within multiple "categories." I already have the tables set up to do this, and it works fine....more >>

I have a table that list employees, the table includes the ID of that persons manager, who is obviously in the same table like below empid Name managerID 1 Fred 2 2 Bill 28 3 George 2 How can I list the employees and include their manager's ...more >>

Hi All, I am trying to create a query with an inner join on two different fields. I can only find an inner join on one field. I have a replicated account table that I want to join with my original account table on accountkey and branch Any help would be greatly appreciated. Thanks...more >>

Suppose I have a very simple table (just one column of integers, not null, for the sake of simplicity). I have an application that interacts with this table (in a SS2K database) via ADO.NET. There will periodically be the need to arbitrarily delete 300-500 out of 150,000 rows from this tabl...more >>

Hi, Easy question I'm sure. I'm getting an error on this. exec master.dbo.xp_cmdshell 'DIR E:\SQLBackups\Database Transaction Logs\Eus\*.TRN' The network path was not found. NULL E drive is a powerVault connected to the server. thanks Gerry ...more >>

I am gettitng the covert error with the following when I try to update the query through my client. The client is a Delphi app that is using ADO and the @TestDate parameter is a datetime type. CREATE FUNCTION EffluentDataTestByDate(@TestDate DATETIME) RETURNS TABLE AS RETURN ( SELECT T...more >>

I set up some jobs using the DB Maintenance Plan Wizard to do a nightly backup and consistency check. But I find that the jobs created aren't very flexible in terms of being able to Edit them. What I want to accomplish is a DB complete nightly backup, only saving two days of backups, and a nightl...more >>

Hello: I am wondering if there is a more efficient way to write the following stored procedure. The sp was designed to either return all records or filter by a given status value (input parameter value of 0 = no filter): CREATE PROCEDURE CustomerListGet (@CustomerStatusID [int]) AS ...more >>

I recently converted an Access DB to a SQL DB. I ma having trouble with a parameter query that works well in the Access DB but not in the SQL DB. Here is the SQL stmt from the Access DB. SELECT SvrReq_tbl.*, QCCategories_tbl.*, SvrReq_tbl.Request_Tracking_ID FROM SvrReq_tbl LEFT JOIN QCCat...more >>

I have the following code: DECLARE @IDO INT; select intOfficerId into @IDO from tableOfficer where charOfficerName = 'jane'; ..... but get a syntax error "Server: Msg 170, Level 15, State 1, Procedure uspInsertPrivate, Line 14 Line 14: Incorrect syntax near '@IDO'." I cannot find code ...more >>

In a single job, I need to check for the existence of a trigger. If it does not exist, I need to create it. If it does exist, I do nothing. How can I do this in a single job? I'm running into problems because 'CREATE TRIGGER must be the first statement in the batch'. I could check for t...more >>

hello, I was wondering how to track all changes on tables by using some sort of a history table. What i would like is a generic history table where i can see who updated, inserted, deleted or executed(stored procedures, triggers) what value in what table with a date when it was occured. ...more >>

Hi all, hope I'm posting this to the correct forum. I'm using a loop in an active server page to reorder a table, but perhaps I could do it using a stored procedure. Problem is, I don't know where to start. Here's my code. If somebody could point me in the right direction, I'd love to give ...more >>

When I run the following delete statement, it only deletes based on the date but ignores the time. So, if I have 2 records inserted on the same day for a certain client, they both stay - when I want to only see the last record inserted. How would I do that? delete #track_leads where hist...more >>

Hello, Trying to Unlock a database before I attempt to load data in a table. Any suggestion is appreciated thanks!! ...more >>

Hi, I would like to execute a sproc in a function. Is it possble? The following is the code. Create Function fn_helprolemember(@roleName varchar(100)) Returns @table Table (dbrole varchar(50), membername nvarchar(100), memberid varchar(100)) As exec sp_helprolemember @roleName Return ...more >>

If I want a code page independent binary collation for 8 bit chars, what would I use? For example, if latin1_general_bin sorts strictly based on the code value, then why does it contain latin1 in the name? It should work for any codepage I desire binary collation for. Thanks ...more >>

Hello! I am trying to write a command to check first if there are any locks in couple of tables I want to truncate. That way, if there is a lock, I would unlock the table and then truncate. It seems like that truncate failed because it was locked? Any help is highly appreciated!! ...more >>

I am running this command: select id, account from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel 5.0;Database=c:\Blocks.xls;HDR=YES;MEX=1' ,Sheet1$) and I am getting the following error: Server: Msg 7415, Level 16, State 1, Line 1 Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB...more >>

SQL Server 2000 Developer Edition Windows XP Professional The D: drive on my laptop is not really a drive. It is actually C:\Data mapped to drive D: via the SUBST command as part of my startup. I'm weird like that -- I want "My Documents" to be its own drive in the Windows Explorer. But ...more >>

I am a SQL programmer that needs to interact with AS/400. Since I know nothing about AS/400, I would like to gather some information regarding the ability for .net and sql to interact with as/400. I believe it can be done with the construction of a COM component, but I was wondering if there is...more >>

I have a table with unicode data, and the column in question has the collation set to LATIN1_BIN_GENERAL. I did a query 'select col from table order by col'. The rows are not in the expected order. A cyrilic character(U+0161) is sorting before a lower case b. In the expected binary o...more >>

Greetings all, In an sp I want to have the same select statement but 2 different Where Clauses. .... Select * from tbl1 --- if condition1 then Where UDF(@parm) --- if condition2 then Where UDF(@parm) Would it be possible to have a UDF inside the Where clause which would return the des...more >>

HI all, I got this error message and I don't really know what it means. [Microsoft][ODBC SQL Server Driver][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then r...more >>

I require a binary unicode collation. I tried LATIN1_BIN with no success on a database containing Cyrillic data. A string with a U+0161 character sorted before a lower case 'b' when doing a select with 'order by' when the collation of the column was LATIN1_GENERAL_BIN. I would have expe...more >>

Hi, There is a date in XML file: <KBArticles> <KBCatalogName>10</KBCatalogName> <FileName>841.HTM</FileName> <DateLastModified>2004-07-13T14:15:00.0000000+02:00</DateLastModified> </KBArticles> I'm trying to open it using OPENXML clause: OPENXML (@L_hdoc, '/FullKB/KBArt...more >>

I am reevaluating my backup strategy. I currently do a full backup on our sql2000 DB once a day. I now want to do transactional backups from 7am to 6pm every 15 minutes and around 10pm do a full backup on all the DB's. My problem is determining how to assign what backup to what file. If I a...more >>

I'm lost now... I have a user set up as a db_owner for one of our databases, the permissions all look correct and his machine keeps saying "can not open user default database. login failed" in enterprise manager... what could cause this and how do you fix it? thanks ...more >>

Hello, I have created a DTS package for importing about 4 million rows of data in to SQL2K database daily. The problem is that transaction log file bloats to 10-15 GB during update/delete phase. Lets say I have two tables "tblDTSTemp" and "tblData" DTS Package first must empty "tblDTSTemp" b...more >>

Hello all, I have the following field names in a table object holding membership information as of the 15th of every month and only on the 15th of every month:(going back several years) DateTime, CustID, CoverageID I need to find out the churn in my membership as required by the fo...more >>

Hi all, I'm trying to get a grip on reasonable performance and indexing. I'm using MSSQL Developer edition. I've got a table on my aged P3/Win2K machine with 15 million records containing an integer column named CustKey--of which there are somewhere in the vicinity of 6000 distinct values in...more >>

I have a piece of VB code accessing a large SQL query on a table. Im guessing the DB in which the table is in doesnt support the timeout function in VB, and so i need to do the following... .... my vb code calls a query on a table, if the query takes longer than lets say 10s, then the query...more >>

This is more a design question and if it would be better to post it in another area, please let me know. But for now... I'm in a new workplace where they deal with large tables in the millions of records. Though each file is indexed (default clustering) on the primary key, they use no forei...more >>

is there a convert() or string function that does this? 16.00 -> 16 16.10 -> 16.1 16.12 -> 16.12 -- .~. Might, Courage, Vision. In Linux We Trust. / v \ http://www.linux-sxs.org /( _ )\ Linux 2.4.27 ^ ^ 6:56pm up 1 day 11 min load average: 1.69 1.72 1.72...more >>

Hi there, I need help with a trigger please (it's to stop more than 10 students being entered in a class). This is what I've got so far: CREATE TRIGGER trTooManyStudents ON dbo.tblFutureClassesSub INSTEAD OF INSERT AS IF ( COUNT (BigClassCode + ClassDate + Num4Count) >= 10) PRINT 'You can on...more >>