SQL Server 2005 SELECT MAX Function For Multiple Columns On The Same Record
Hello,
I am trying to figure out how to use the select maximum command in SQL Server 2005. I have already created a database and I have it populate it with multiple fields and multiple records. I Would like to create a new column or field which contains the maximum value from four of the fields. I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column.
Any help you can provide will be greatly appreciated!
Thank you,
Nathan
View Complete Forum Thread with Replies
Related Forum Messages:
COUNT FUNCTION ON MULTIPLE COLUMNS
I have a database that contains a column for UnitName , BeginDate andEndDate.I want to pass two parameters (@BeginDate and @EndDate) and retrieve atable of valuesthat include UnitName along with Counts for each UnitName.SELECT UnitName, COUNT(BeginDate) AS Start(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @BeginDate AND@EndDate)FROM TableWHERE BeginDate BETWEEN @BeginDate AND @EndDateGROUP BY UnitNameORDER BY UnitNameThis works. But when I try to add another count by using a subselect Iget an error dealing with GROUP BY not including the column in mysubselect.How is the best way to Count two columns using Group By.
View Replies !
Microsoft SQL Server 2005. Selecting Multple Columns From Multiple Tables
Im just curious how i would take multiple columns from multiple tables.... would it be something like this ??? table: Products COLUMNS ProductName, ProductID table: Categorys COLUMNS CategoryName, CategoryID,ProductID SELECT Products.ProductName, Categorys.CategoryName,Products.ProductID,Categorys.CategoryID,Categorys.ProductID FROM Categorys, Tables WHERE Products.ProductID = Categorys.ProductID
View Replies !
Select Where LIKE From Multiple Columns
I am using mySQL and the following query works fine: SELECT * from listings where name LIKE "%$trimmed%" order by name"; and so does this query: SELECT * from listings where keywords LIKE "%$trimmed%" order by name"; however, I can't seem to combine the two with an OR statement as this query only returns the results from the first LIKE column select * from listings where name LIKE "%$trimmed%" or keywords LIKE "%$trimmed%" order by name I want to be able to search both columns and return a row if the NAME column or the KEYWORDS columns contains a string.
View Replies !
Select Record Based On Multiple Criteria (vars)
Hi! I'm new to SQL and have a question... I'm writing a script that gathers a few variables from an outside source, then queries a table and looks for a record that has the exact values of those variables. If the record is not found, a new record is added. If the record is found, nothing happens. Basically my SELECT statement looks something like this, then is followed by an If... Else statement SELECT * FROM TableName WHERE LastName = varLastName AND FirstName = varFirstName AND Address = varAddress If RecordSet.EOF = True Then 'Item Not Found, add new record 'code to add new record...... Else 'Item Found, do nothing End If RecordSet.Update RecordSet.Close Even when I try to delete the If.. statement and simply display the records, it comes up as blank. Is the syntax correct for my SELECT statement??
View Replies !
Aggregate Multiple Columns With Different SELECT Criteria
Let me start with saying thanks to all of you who have helped me (I'm a SQL newbee after doing OO for the past 12+ years) I need to do several aggregates on multiple columns, with each column having different SELECT Criteria. Sample Data: Dept Project Cost CostFlag Schedule ScheduleFlag D1 D1P1 495 1 135 3 D1 D1P2 960 2 70 2 D1 D1P3 1375 3 105 2 D1 D1P4 1050 2 160 3 D1 D1P5 1890 3 40 1 D2 D2P1 650 1 155 3 D2 D2P2 890 2 125 2 D2 D2P3 1235 3 85 1 D2 D2P4 430 1 140 3 D3 D3P1 1960 3 45 1 D3 D3P2 1490 3 85 1 D3 D3P3 1025 2 135 3 D3 D3P4 615 1 100 2 D3 D3P5 270 1 70 1 D3 D3P6 815 2 155 3 I need to calculate MEAN (average), Standard Deviation, Variance, Range, Span & Median for each data column (Cost, Schedule in the test data), where each data column has different selection criteria. I have the calculations working for each column individually (e.g. funcCalcCost, funcCalcSchedule), but I need to return the calculated values as a single data set: SELECT Dept, Project, AVG(Cost) as Cost_Mean, MAX(Cost) - MIN(Cost) as Cost_Range, ....... WHERE CostFlag = @InputParameter GROUP BY Dept, Project The code above works great - but only for a single column. I need to return a dataset like this: Dept Project Cost_Mean Cost_Range D1 D1P1 495 135 D1 D1P2 960 70 D1 D1P3 1375 105 I need to return a dataset like this: Dept Project Cost_Mean Cost_Range Schedule_Mean Schedule_Range D1 D1P1 495 135 100 28 D1 D1P2 960 70 42 12 D1 D1P3 1375 105 91 38 I also have working code calculate the MEDIAN (what a pain that was, thank god I found a code example to get me going on the MEDIAN) Thanks!
View Replies !
Select With Grouping For Multiple Date Columns
Hi All, Thanks for dropping by my post. I have a table which is of this form. ID MS030_A MS030_F MS036_A MS036_F MS040_A MS040_F ZZ0023 2/16/06 2/16/06 8/10/07 8/10/07 11/21/05 11/21/05 ZZ0031 8/10/07 4/5/07 8/9/07 8/9/07 3/22/07 3/22/07 ZZ0077 8/9/07 9/7/07 8/10/07 8/10/07 8/10/07 9/7/07 ZZ0078 8/10/07 9/7/07 8/9/07 8/9/07 8/9/07 9/7/07 ZZ0079 8/9/07 8/10/07 10/26/05 10/26/05 8/10/07 8/10/07 ZZ1030 3/31/05 8/10/07 9/1/05 9/1/05 8/9/07 ZZ1033 3/24/06 8/9/07 8/9/07 8/9/07 3/31/05 ZZ1034 8/10/07 8/10/07 8/9/07 8/9/07 3/24/06 ZZ1037 8/9/07 8/9/07 9/24/07 9/24/07 ZZ1040 10/26/05 10/26/05 9/24/07 9/24/07 ZZ1041 9/1/05 9/1/05 9/24/07 9/24/07 ZZ1042 8/9/07 8/9/07 9/24/07 9/24/07 11/21/05 The goal is to group all this transactions by Month and Year. Something like this.... MS030_A MS030_F MS036_A MS036_F MS040_A MS040_F Month Year 3 2 2 2 2 2 1 2006 4 4 7 9 8 9 2 2006 10 10 6 8 8 3 2006 4 4 5 5 3 2 4 2006 5 6 8 3 7 1 5 2006 For just one date column it is pretty straight forward i.e., just do a select count and group by DATEPART ( Mm, DateField) but for multiple columns i am in a total fix.... can please someone help me out... appreciate your help
View Replies !
Multiple Tables Select Performance - SQL 2005 - Should It Take 90 Seconds For A Select?
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database. The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records. I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered). I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these). I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes. I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem. So my question is: Is it normal for such a type of read query to take 90 seconds to complete? Is there anything I could do to speed it up. Any other thoughts? Thanks
View Replies !
Select Query Results In Multiple Columns Based On &&"type&&" From Another Table
Using SQL Server 2005 Express: I'd like to know how to do a SELECT Query using the following tables: Miles Table: Date/Car/Miles/MilesTypeID =============== (some date)/Ford/20/1 (some date)Ford/20/2 (some date)Chevy/30/1 (some date)Toyota/50/3 (some date)Ford/30/3 Miles Type Table MilesTypeID/MilesType ================= 1/City 2/Highway 3/Off-Road I'd like the results to be like this: Date/Car/City Miles/Highway Miles/Off-Road Miles ===================================== (date)-Ford-20-0-0 (date)-Chevy-0-20-0 (date)-Ford-20-0-0 (date)-Toyota-0-0-50 (date)-Ford-0-0-30 Anyone? Thanks in advance!
View Replies !
Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?
Hi, I have the following script segment which is failing: CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude)) INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'." Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values. The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why. Any ideas?
View Replies !
SQL 2005 Upgrade Insert Into Select * Changes Order Of Columns
Hi there, This appears to be a change in behaviour between SQL 2000 & 2005. Can anyone confirm? We have two tables with same schema but different column orders. In Sql 2000, the statement Insert Into table1 select * from table2 appears to map the column names between the two tables. There is one column out of order, however Sql 2000 doesn't seem to care and correctly inserts the data. In Sql 2005 the behaviour is to return the columns in the order of table2, rather than mapping column names. This results in incorrect values being added to the columns. (Column shift) The Sql 2005 behaviour seems to be correct, and select * is bad practice anyway, however I would like to confirm why this was changed and whether there is a service pack/hotfix in 2000 that would have the same result. We are running compatibility mode in Sql 2005 v9.0.3042. Sql 2000 is 8.0.2187 thanks, Andrew
View Replies !
Record Locking: Multiple Users Accessing The Same Record
I have read several discussions about SQL 7 having built-in record locking. I am assuming that this is only during the transaction process. I have a problem with multiple users access the same record on a SQL table. We have these users accessing the SQL data with an Access 2000 DB Project form. When one person accesses the form to pull up a record, someone doing this at the same time will get an error window that asks the user to Save/Drop changes. Is there any way to LOCK DOWN a record until a user has finished making changes to it?
View Replies !
SQL Server Max Record, Multiple Table Join Problems
Thanks in advance for you help.SQL Server 2000I have a complex join, but a simple example will do the trick so...table1 (placement records, possibly many per case,highest ID beingmost recent)----------placementID(pk) * CaseID(fk) * OrganizationID(fk) * Name * Numbertable2 (Organizations Table, Many Placements can be at 1 organization)----------OrganizationID(pk) * OrgName * OrgTypetable 3(Case Table, each Case can have many placements)----------CaseID(pk) * StaffName * CreationDatenow my query...SELECT t1.placementID,t1.caseID,t2.OrgNameFROM table1 as t1INNER JOIN table2 as t2 on t2.OrganizationID = t1.OrganizationIDWHERE exists (select distinct max(placementID),CaseIDfrom t1 group by caseID)GROUP BY t2.OrgName,t1.PlacementID,t1.CaseIDmy results-------------placementID CaseID OrgName1 1 oneOrg2 1 two org3 1 three org4 2 another org5 3 yet another orgmy desired results------------------PlacementID CaseID OrgName3 1 three org4 2 another org5 3 yet another orgas you can see i get all records, but what i want is to see only thelast placementID for each case so i dont want duplicate caseID but Ido need the orgName, and yes the query works correctly without the orgname but as soon as i add orgName to the select statement I getduplicate CaseID's, How do i eliminate duplicate CaseID's and get onlythe MAX(placementID) for each Case and the OrgNameplease advise, getting desperate.thanks again so much for the help
View Replies !
SQL Server - Multiple Rows Into Columns
Hello! I am trying to figure out if there is a way to convert (unlimited numberof) rows into columns in SQL Server? Can this be done using a SQL query and without creating any extra tables or procedures? Please let me know. Thanks much. -PV
View Replies !
Multiple Record Insertion For Each Record From Source
Hi, How can we insert multiple records in a OLEDB destination table for each entry from the source table. To be more clear, for every record from source we need to insert some 'n' number of records into the destination table. this 'n' changes depending on the record. how is this achieved. thanks.
View Replies !
SQL Select From A Function On A Different Server
Hi all, in SQL 2000 I can select data from a different server using the sintax "select * from server_name.db_name.owner.table_name". Can i do the same thing with a table function (e.g "select * from server_name.db_name.owner.MyFun_name()")? Is there a way to do it? Thank you all, Andrew
View Replies !
How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns
Please can anyone help me for the following? I want to merge multiple rows (eg. 3rows) into a single row with multip columns. for eg: data Date Shift Reading 01-MAR-08 1 879.880 01-MAR-08 2 854.858 01-MAR-08 3 833.836 02-MAR-08 1 809.810 02-MAR-08 2 785.784 02-MAR-08 3 761.760 i want output for the above as: Date Shift1 Shift2 Shift3 01-MAR-08 879.880 854.858 833.836 02-MAR-08 809.810 785.784 761.760 Please help me.
View Replies !
Multiple Columns With Different Values OR Single Column With Multiple Criteria?
Hi, I have multiple columns in a Single Table and i want to search values in different columns. My table structure is col1 (identity PK) col2 (varchar(max)) col3 (varchar(max)) I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as SELECT TBL.col2,TBL.col3 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col2,'engine') TBL1 ON TBL.col1=TBL1.[key] INNER JOIN CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON TBL.col1=TBL2.[key] Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record. I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result = 1 row But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result=0 Row Any idea how i can write second query to get result?
View Replies !
Obtaining Data To Be Displayed In Multiple Columns From Multiple Rows
Hello All, I am rather new to reporting on SQL Server 2005 so please be patient with me. I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row. For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report The table (System Info) looks like:- ID | System part | 1 | RAM 2 | Disk Drive 10| CPU 11| CD ROM | Which So basically I need it to look like this. Name | IP | RAM | Disk Drive| ---------------------------------------------- A | 127.0.0.1 | 512MB | Floppy So Far my SQL code looks like this for 1 item SELECT SYSTEM PART FROM System Info WHERE System.ID = 1 How would I go about displaying the other system parts as columns with info Any help is much appreciated!
View Replies !
SELECT Query - Different Columns/Number Of Columns In Condition
I am working on a Statistical Reporting system where: Data Repository: SQL Server 2005 Business Logic Tier: Views, User Defined Functions, Stored Procedures Data Access Tier: Stored Procedures Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by different organizational hierarchies different number of layers within a hierarchy select a organization or select All of the organizations with the organizational hierarchy combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies: Hierarchy 1 Country -> Work Group -> Project Team (Project Team within Work Group within Country) Hierarchy 2 Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases: Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams Country = "USA", Work Group = all work groups Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance" Client = "Client A", Contract = "2007-2008 Maint", Project = all Client = "Client A", Contract = allI am totally stuck on: How to implement the data interface (Stored Procs) to the Reports Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all") (WorkGroup = @argWorkGroup OR @argWorkGrop is NULL) Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services. Thanks for all your help!
View Replies !
Search In Fulltextindexes For Multiple Searchterms In Multiple Columns
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is: I don't want only the records with columns that contains both searchterms. I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms. For example I search for NETWORK and PERFORMANCE in two columns. Jobdescr_________________________|Jobtext Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow. I only get the first record because JobDescr contains both searchterms I don't get the second record because none of the columns contains both searchterms I managed to find a workaround: SELECT T3.jobid, T3.jobdescr FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1 INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase. Does anyone know a better solution? Thanks in advance Bart Rouw
View Replies !
Processing Multiple Rows And Multiple Columns In SSIS
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
View Replies !
Deleting Multiple Columns From Multiple Objects
Is there a way to delete from multiple tables/views a column with a specificname? For example, a database has 50 tables and 25 views all have a columnnamed ColumnA. Is it possible to write a simple script that will deleteevery column named ColumnA from the database?Seems to be it would be possible and I can somewhat vision it usingsysobjects but without wanting to spend too much time generating the script(when I could in shorter time manually delete) thought I'd pose the question.Thanks.
View Replies !
Convert Multiple Rows Into Multiple Columns
I need to push multiple rows of data from one table to one record in another table. This can be achieved using cursors but I need to do them without using cursors. Source Table: fld_name fld_value fld1 val1 fld2 val2 fld3 val3 fld4 val4 Destination should be like this: fld1 fld2 fld3 fld4 val1 val2 val3 val4 Thanks.
View Replies !
Multiple Server Select Query
hi,i have three database servers with heterogeneous databases, and i have a sql server 2005 that should has a table that will be filled with records from the three servers every time period, so what would be the best technique to create this table with the scripts ????i used to use linked server + sql server agent jobs but usually for one linked server only, but this time i am afraid of the performance as there will be three linked servers, so i need ur suggestions.
View Replies !
How To Select Columns When Columns Change
I have a scenario that reminds me of a pivot table and I am wondering if there is a way to handle this in SQL. I have four tables. Product Line, Item, Property, and Value. A Product Line has many items and an item can have many property's and a property can have many values. I want to select a product line and show all the items with the Property's as column headers and the Values as the data. The thing I am having trouble with is the property's for an item are variable from a few to a whole bunch. Any help would be appreciated. Thanks, vmon
View Replies !
Multiple Rows To One Row And Multiple Columns
Hi, I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work. Heres what i want to do: This is the how the table is: EmpID Designation 678 CFA 679 CFA 680 CFA 685 CFP 685 CIMA 685 IMCA I want it to display as: EmpID Designation1 Designation2 Designation3 678 CFA 679 CFA 680 CFA 685 CFP CIMA IMCA could anyone provide some help on this? Thanks
View Replies !
Is It Possible To Dispaly All Columns For A Record In A Dropdownlist
Say a record has four columns in { Emp#, firstName, secondName, thirdName, spouseName }. Now if you configure a sql datasource for a dropdownlist to return all columns, only Emp# will be displayed in the dropdownlist, the other columns wont be listed. Is there a way to list the values of all columns in the dropdownlist using the sql datasource or in ADO.net code. Thanks.
View Replies !
Testing For Repeated Value In The The Columns Record Sql
If I have this table Mytable IdnCol ColumnA ColumnB ColumnC ColumnD 1 CC DD EE FF 2 DD GG HH JJ 3 HH JJ KK HH How I can check for the repeated value in the column. for example in the 3rd record, in columnA and ColumnD the value HH is repeated so is there a way to find if there are values repeated in any of the columnA ColumnB ColumnC ColumnD. Thanks
View Replies !
What Function Can Create A Record Automatically
In the table, there is a record which has several field. every month, the function will create a same record. that means, the first month, one record. the secord month, two reocrds, ..... for a years. will have same 12 record. so what function can do this? Thanks.
View Replies !
Fetching A Record From The Database Within A Function.
Can I do something like this in RS - I would like to call a function in a calculated field like =code.GetNHW(Fields!id1.Value,Fields!date1.Value) where the GetNHW() function would return a double value based on some conditions- The code I would like to write in Code window- Public Function GetNHW(ByVal ID As Integer, ByVal attDate As Date) As Double Dim dsDTConfig As New DataSet Dim NHW As Double dsDTConfig = GetDS("tblEmployee_DailyTimingsConfig", "id=" & ID & " and '" & attDate & "' between configStartDate and configExpiryDate", False) If dsDTConfig.Tables(0).Rows(0)("allowUnscheduledBreaks") = True Then NHW = 0.01 ElseIf dsDTConfig.Tables(0).Rows(0)("allowUnscheduledBreaks") = False Then NHW = 0.05 End If Return NHW End Function What I am looking for is - I would want to get the record(dataset) within the function from the database and based on the values got, I would check few conditions and calculate NHW. --Anand
View Replies !
Multi Select Parameter From Function - Select All?
I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter. Query: Select [name], city, state, zipcode From Golf inner join charlist_to_table(@State,Default)f on State = f.str Function: CREATE FUNCTION charlist_to_table (@list ntext, @delimiter nchar(1) = N',') RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL, str varchar(4000), nstr nvarchar(2000)) AS BEGIN DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr nvarchar(4000), @leftover nvarchar(4000), @tmpval nvarchar(4000) SET @textpos = 1 SET @leftover = '' WHILE @textpos <= datalength(@list) / 2 BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr) WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SET @pos = charindex(@delimiter, @tmpstr) END SET @leftover = @tmpstr END INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover))) RETURN END GO Anyone have any ideas? Thanks, Deb
View Replies !
In SQL SERVER 2005, How Can I Get The ID Of The Record I Just Insert To Table?
In SQL SERVER 2005, how can I get the ID of the record I just insert to table? I defined a table MyTable, and I insert a record into the table using the SQL below Insert into MyTable (Name) values ("User Name") You know the field ID is IDENTITY, so it can not be in Insert SQL, and SQL SERVER will pass a value to it automatically.How can I know the ID of the record I just insert to table? CREATE TABLE [dbo].[MyTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nchar](10) NOT NULL,CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
View Replies !
ContainsTable Function Searching Only One Column Per Record
Hello all, I am using the ContainsTable function to search a database from my (c#) app. This works relatively well and all fields of the table are indexed and searched. That is, any column, but per record only one column. What I mean is this: when searching for "chris 2007", I want to retrieve all items where author contains chris and year contains 2007. Currently, a search for chris brings up all items where author (or any other field) contains chris, a search for 2007 works as well, but chris 2007 fails as there is no -one- field where chris and 2007 are located. Can anybody help me achieve this? My code is: Code Block SELECT FT_TBL.ID, FT_TBL.Type, FT_TBL.Author, IsNull(FT_TBL.Author, FT_TBL.Editor + ' (Ed.)') AS CorrectedAuthor, FT_TBL.Editor, FT_TBL.Title, FT_TBL.Abstract, FT_TBL.Comments, FT_TBL.Year, FT_TBL.City, FT_TBL.Publisher, FT_TBL.ISBN, FT_TBL.Pages, FT_TBL.Journal, FT_TBL.Issue, FT_TBL.Hyperlink, FT_TBL.Tags, KEY_TBL.RANK FROM Sources AS FT_TBL INNER JOIN CONTAINSTABLE(Sources, *, @searchQuery) AS KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY] ORDER BY KEY_TBL.RANK DESC; What am I doing wrong? Thanks in advance, Chris
View Replies !
FN_DBLOG - Limitations On Columns - [RowLog Contents 0] [Log Record]
Although unsupported / undocumented, using FN_DBLOG(..) is inevitable. While retrieving [RowLog Contents 0]/ [Log Record] transaction log information I encounter a possible limitation regarding the size of the fetched data. There is a suspect that data exceeding 8000 bytes is truncated. I do not manage to get more than 8000 bytes - neither at the Studio Management Query pane nor with a programmatic ODBC piece of code. There is more data for sure - no doubt about that!! Does anybody have any idea? Thanks /H
View Replies !
Not Able To Insert Record In SQL Server 2005 Express Edition
Hello, In a web application of VS.Net 2005 I am able to insert records in both the database SQL 2000 & 2005 In a windows application of VS.Net 2005 I am able to insert record in SQl 2000 but I am not able to insert record in SQL 2005 Express Edition. Please help Me I tried very Much but not succeded yet. Thanks in Advance
View Replies !
In VB Express 2005, I Cant Save Any Record In Database Of Sql Server.
I was using the msdn tutorial regarding sql database for vb2005 express. In that tutorial, I followed the steps as listed. But the problem is that the save button in my application during run time, it does not work. Furthermore, the code for for saving a record during run time does not work as well. I have reinstall sql 2005 server and vb2005 express but of no use. I have winxp with system of pentium 4. What I should do to solve this dilemma. Faisal.
View Replies !
Scalar Function Columns
Is it ill-advised to have columns whose values pull from scalar functionsusing other fields in the record as parameters? For example, if I havecreate table a(iID int primary key)create table b(iID int ,iDetail int,CONSTRAINT PK PRIMARY KEY(iID,iDetail),CONSTRAINT FK FOREIGN KEY (iID) REFERENCES a(iID))Let's say in table b I put price information for each detail and in table aI'd like to put a column that sums these prices for the children of eachrecord. Should I make a computed column that references a function usingiID as a parameter? Or would it be better to create a view for this kind ofpurpose?Regards,Tyler
View Replies !
Passing Columns To CLR Function
Hello, I am trying to send to colums to SQL CLR function and get some results. I want the CLR code be like: Code Snippet public void DoSomething(SqlDouble[] a, SqlDouble[] b, out SqlDouble x, out SqlDouble y, out SqlDouble z) { //Do Something... x = .... y=... z=... } I want to call this code from SQL code: Code Snippet create table #Temp (float a,float b) declare @x float declare @y float declare @z float exec dbo.DoSomething(a,b,@x,@y,@z) ---???? Do someone have an idea?
View Replies !
|
TRACKER
