Hi, I'm new to SQL. I need to return a recordset which will include only records with a date after previous June. (Could be this year or last) It's for tax purposes. How can i specify that it's the last June, not 'June' as a value. Hope somebody can help. Many thanks for your ideas. Ant ...more >>

I suddenly can't work with my Enterprise Manager. It tells me that log in failed - Can't access user's default database. What can I do about it? I really need to fix it fast. Thanks a lot Shimon. ...more >>

Hello, Does SQL have any sort of checksum value attached to each 8K page or is there a feature we can turn on to enable such a checksum? I'm trying to optimize our block data synchronization product specifically for SQL databases. Our product is similar in technology to rsync. We current...more >>

I have a table that has the following columns: ID SSN LastName FirstName TypeCodeID With keeping the integrity of the information can I create a new column that is between FIRSTNAME and TypeCodeID named Active? I am hoping not having to drop table and rather use the Alter tabl...more >>

I don't know much about SQL Server yet and am going to start writing some stored procedures. I thought the best way to reference the table names and columns would be writing their definitions out to a text file like a report. Any comments in this regard? <%= Clinton Gallagher ...more >>

Hi folks I have a SP that inserts a record into a table. The text is as follows: ''''''''''''''''''''''''''''''''''''''''' CREATE PROCEDURE [dbo].[procClientAdd] @lastname varchar (50), @firstname varchar (50) = NULL, @othernames varchar (50) = NULL, @gender varcha...more >>

How do I retrieve the size of the database from SQL Server 2000? I'm expecting that there is a sp for this, but I can't find it. I don't want to use the size from the filesystem because we are posting data and the data may not be flushed or the filesystem data may be otherwise behind and out...more >>

How do I work with groups of records and return the best data from the group so I can update another table. Example: I have 10 records in table A that share a common field (they all have the same business group number), some of them have phone numbers and some do not, I want to select the rec...more >>

I have an INSERT trigger on my table (TABA) which calls a stored proc. this stored proc loop thorough another table's data (TABB) which stores the names of some stored procedures, and exectues each of them (dynamically). If i issue an INSERT statement to TABA in Query Analyzer and if any one of ...more >>

SELECT tblACRDIMProgramReview.ProgSumID, tblACRDIMProgramReview.SysOrgLastReviewDate, tblACRDIMProgram.ProgramName, tblACRDIMProgramReview.ProgramSID FROM tblACRDIMProgramReview INNER JOIN tblACRDIMProgram ON tblACRDIMProgramReview.ProgramSID = tblACRDIMProgram.ProgramSID WHERE (tblACRDIMPro...more >>

I have a datetime column where the date and time data values are = sequential up to the second. There should be no gaps.=20 What is the best way to develop a select statement that looks at this = column to find gaps between the current and next record in that date and = time column? Yosh...more >>

I have a set of data which needs to be put in a table in addition to dates (daily). There are some dates where a location could be closed and hence no data values but i still want to see the date row in the table with location and all other associated details but NULL for visit counts. I hav...more >>

Hello, I know this group doesn't pertain to Active Server Script, but I didn't see a group for that and though the SQL and Access people would be better suited to help me, since many have exerience with ASP scripting. I have Active Server pages which write data to an Access database. On a ...more >>

Given the following table, how can I write a select statement to give the results shown below? Blank values are not null, but ''. Thank you. Table: ID VALUE 1 a 1 2 a 2 b 2 2 c 3 b 3 c 3 4 c 4 a Results: ID Values 1 a 2 a, b, c 3 b, c 4 a, c...more >>

SQL 2K I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME, SUPVR, MOD_DATE I want to retain just one row for unique (ID, DPTID) combination, doesn't matter with values of rest of the columns. Thanks In Advance John B ...more >>

ACCT final_decision -------------------- -------------- 2367 Decline 2367 Decline 7661 Review 7661 Decline 6002 Accept 6002 Review 6002 Accept 6002 D...more >>

I need to have the Not For Replication option set on some tables with Identity columns similar to the the code snippet below however this needs to be done through Enterprise Manager and can't find the option there. Is there way to set this option through EM? Thanks. John ...more >>

I found this statement in a program calling MS Access: UPDATE Item INNER JOIN (Detail INNER JOIN Main ON Detail.DetailMainKey = Main.MainKey) ON Item.ItemSKU = Detail.DetailItemSKU SET Item.ItemGroupKey = MainParentKey This syntax doesn't seem to be supported in SQL 7.0. Is it v...more >>

Does anyone have a good example of querying group members out of Active Directory Group. The error seems to happen when the field I am querying has multiple vales This is what I have so far: exec sp_addlinkedserver 'xxx', 'Active Directory Services 2.5', 'ADsDSOObject', 'adsdatasource' ...more >>

How do I delete duplicate entries? For example, lets says I have the following table format: SystemName, Memory, CPU I would like to delete any duplicates that are in SystemName. ...more >>

Hi, I have an initial value like this: 'TEST0001' I want to write a sql script that will automatically take that initial value and keep adding 1 to it and insert into a table for me until it get to 'TEST9999'. my table should now store: TEST0001 TEST0002 .... .... TEST0010 TEST0011...more >>

Hi Friends... I need to know how can i remove the colum headers when making a query with sql query analizer, the query is: select succeeded from sysdbmaintplan_history i need that the field header 'succeeded' dissapear!!! thanks all...more >>

Howdy, ISSUE 1: See issue 2 below. I have a distance calculator on my site = which works great. However, the users need to sort by distance, which = make sense. I'm not sure how to do it other than like this. With the = returning query include the distance from origin. Here's my dilemma, I = ...more >>

I have been testing some code that has been inserting large amounts of data into some tables. All of the tables have a Primary Key that is also an Identity. After a bad run, I go through and DELETE all of the entries in each table (I would TRUNCATE, but they all have at least three other tab...more >>

Should an application written in VB6 and early bound to SQL Server 2000 SQLDMO run on a machine with 64 bit SQL Server 2000 installed? We update SQL databases with a custom written in house VB6 app. What would be involved in getting this program to run in this environment? Thanks for any inp...more >>

I'm working on a stored procedure to age balances. In short, here's the algorithm: Payments get applied to the oldest 'bucket' first; in my example, BalanceOver180. Is the value in the Payments column greater than or equal to the value in BalanceOver180? If so, subtract BalanceOver180 from Pa...more >>

I have numerous deleimited text files being delivered to a folder and I need to import them into a data base. These files have diffrerent numbers of rows AND columns. I have set up a looping DTS package (based on http://www.sqldts.com/default.aspx?246) which identifies the file type by inter...more >>

Hi, I have a table user with the following field. id,code,xdate I am trying to write a stored procedure that would return the results as the following 3 queries a. select count(id) from user where code like 'TR%' b. select count(id) from user where code like 'TR%' and xdate < getdate() ...more >>

Is it possible to conditionally skip or jump steps in a job without failing a step? For example I don’t want to run a step if the are no records in a table. I also need to loop based on a condition. ...more >>

I have 2 date variables passed into my store procedure as follows @StartDate datetime, @EndDate datetime however I am getting a conversion error when running the command below which says "Syntax error converting datetime from character string" PRINT ('INSERT INTO ' + @NewSubsList + '(Sub...more >>

Hi, I'm working on a project where I have to do an intersect query based on county & city information currently in a XML file. I'm currently parsing the XML & doing the Intersection manually using for loops in VB... this is very complex & processor intensive operation. I kno...more >>

We have a legacy database that was ported over from LDAP where the user tables are indexed on the username (which is an nVarChar(50) field). We have sever (ok, 92) related tables all using the nVarChar(50) field as the primary key. Does anyone have hard numbers as to whether SQL2000 would b...more >>

Hi, I need some help writing a two queries to determine the average number of days between services for 1. a specific machineid 2. for all specific machineids. The table contains many columns including a MachineID column (INT) and a ServiceDate column (DATETIME) so sample data (excludin...more >>

Hi all, I need some help with the sequel statement. Below are the business rules and the desire results. Any help would greatly appreciate. IF OBJECT_ID('Tempdb.dbo.#Test', 'u') IS NOT NULL DROP TABLE #Test GO CREATE TABLE #Test ( Person_id INT NULL, OriginalA...more >>

I have the following UDF: CREATE FUNCTION [dbo].[GetInBoundID] (@Inbound nvarchar) RETURNS int AS BEGIN DECLARE @InboundID int IF (SELECT COUNT(*) FROM [TOFC].[dbo].[CITTAS_InBound_RailYard] WHERE InBoundRailYard = @Inbound) = 0 BEGIN INSERT INTO [TOFC].[dbo].[CITTAS_InBound_RailY...more >>

I am try to populate a table based on a variable (it holds the table name). I have managed to create the table using the EXEC command but am having problems populating it with the following command. I receive "Error 156: Incorrect syntax near the keyword 'CONVERT'". Could anyone offer a sol...more >>

Hi, Does anyone know of a way to AVOID merge joins? I have a complex query that works fine on one system, but not on another. After inspection, I found that the difference lies in different query plans generated by the query optimizer. On the failing system, a merge join is used, but sin...more >>

Hi, I want to start my SP on timer, let's say every minute. Is it possible to do in MS SQL Server internally? If not, what is the simplest way to do this? Thanks...more >>

Hello ! I need help to creat SQL field in the table to accept only small letter and only number? what is way to creat this in SQL 2000? Please help me. Best regards, Mike...more >>

Is it possible to have an if update and if delete section within one trigger? eg create trigger tgMyTrigger on dbo.tblMyTable For insert, update, delete as if update(MyColumn) begin blah blah etc end if delete(MyColumn2) begin blah blah etc end Is this possible, or should it be...more >>

When I run the query, SELECT nn FROM tt WHERE CONTAINS(nn, 'test') I get the error Server: Msg 7601, Level 16, State 2, Line 1 Cannot use a CONTAINS or FREETEXT predicate on table 'tt' because it is not full-text indexed. How can I set full-text index? Madhivanan ...more >>

Can anyone reccomend a book or online tutorials that shows quite advanced SQL examples. Most the sites and books I have seen for SQL only give small examples from the pubs database designed for a few concurrent users. This is fine for learning but what about real systems with 50+ concurrent us...more >>

Non-clustered is additional data with pointers to the actual data, Then where the pointers are stored? shine...more >>

I have two tables in the same SQL database. Both have a similar numeric field. One field has the same number as the other field but is prefixed with various letters. I know how to use LIKE but won't know the partial string I am looking for. I am trying to use LIKE '%' + Field A or somethin...more >>

Hi, I have 2 tables (dbo.specsAttribute & dbo.specsValue) in my database that stores the specifications for our products. Here's some data example for the 2 tables respectively dbo.specsAttribute: attributeID attributeName 1 Height 2 Width 3 Length 4 Weight dbo.specsValue: va...more >>

I have three tables: tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode tblBookRange has the fields bookRangeID, bookRangeDescription tblBookSubject has the fields bookSubjectID, bookSubjectDescription so some typical data in tblBook might be: 1, 1, 1, B1HBS...more >>

I have a requirement to produce a report that breaks down some data into totals by week. The data is in SQL Server, but the user just wants a one-off report, so we can use Access or Excel as alternatives is more suitable. We have a table of stock movements, and we want to total the number of ...more >>

I'm writing a SQL script. I'd like to display the value I have in a column of type FLOAT so that it appears with the commas in the correct place. It displays now as 10000000.0; I'd like it to display as 10,000,000. Thanks for any help....more >>

Hi, I have a proc and within another proc that i need to verify a result for my action, exemplo: if(Exec SP_VerifyUser @NewEmail=0) Begin -- action of my current proc End But of course this does not work, there's a way to accomplish that ? Tks in advance ...more >>

Hi Experts, My system stores events into a large table. I would like to apply some rules on that table and possibly take action if the rules were satisfied. For example if a certain event arrives 2 times in 60 seconds then perform an action. What is the best way to apply rules to data in...more >>

Hi, I have xml in the following form <ROOT><Emp><Name>Anil</Name><Age>4</Age><Phone>123</Phone></Emp><Emp><Name>S unil</Name><Age>41</Age><Phone>123</Phone></Emp></ROOT> I want to transform this xml into the following format by query (possibly OPENXML) (Nodes of the xml should be th...more >>