Search Results

Search found 42327 results on 1694 pages for 'microsoft sql t sql'.

Page 392/1694 | < Previous Page | 388 389 390 391 392 393 394 395 396 397 398 399  | Next Page >

• find a duplicate series in SQL

  - by SomeMiscGuy

  I have a table with 3 columns containing a variable number of records based off of the first column which is a foreign key. I am trying to determine if I can detect when there is a duplicate across multiple rows for an entire series declare @finddupseries table ( portid int, asset_id int, allocation float ) ; INSERT INTO @finddupseries SELECT 250, 6, 0.05 UNION ALL SELECT 250, 66, 0.8 UNION ALL SELECT 250, 2, 0.105 UNION ALL SELECT 250, 4, 0.0225 UNION ALL SELECT 250, 5, 0.0225 UNION ALL SELECT 251, 13, 0.6 UNION ALL SELECT 251, 2, 0.3 UNION ALL SELECT 251, 5, 0.1 UNION ALL SELECT 252, 13, 0.8 UNION ALL SELECT 252, 2, 0.15 UNION ALL SELECT 252, 5, 0.05 UNION ALL SELECT 253, 13, 0.4 UNION ALL SELECT 253, 2, 0.45 UNION ALL SELECT 253, 5, 0.15 UNION ALL SELECT 254, 6, 0.05 UNION ALL SELECT 254, 66, 0.8 UNION ALL SELECT 254, 2, 0.105 UNION ALL SELECT 254, 4, 0.0225 UNION ALL SELECT 254, 5, 0.0225 select * from @finddupseries The records for portid 250 and 254 match. Is there any way I can write a query to detect this? edit: yes, the entire series must match. Also, if there was a way to determine which one it DID match would be helpful as the actual table has around 10k records. thanks!

  Read the article

• Problem in removing hardcoded values using temp table...

  - by Shahsra

  Hi All, First of all Wish u all Happy New Year. I have a problem in writing query. While executing my query I am getting an error. Query: select case when S.R1 = '6' then 5 when S.R1 = '7' then 6 when S.R1 = '8' then 7 when S.R1 = '9' then 8 when S.R1 ='10' then 9 else S.R1 end as Q FROM [HelpService].[dbo].[help] s ----------------------------------------------- SELECT [Source], [Score] INTO #Temp_Q FROM [HelpDesk].[dbo].[Survey] WHERE [data_Source Name] = 'Text Data' ----------------------------------------------- select CONVERT(REAL, a.[Dell Score]) as Q FROM [HelpService].[dbo].[help] s LEFT OUTER JOIN #CE_Temp_Q a on s.[R1] = a.[Source] ERROR Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to real. What I am asked to do is I need to remove the hard coded values and need to write queries with a temp table. Thanks in Advance, Shashra

  Read the article

• Why does this properly escaped SQL query fail?

  - by Jason Rhodes

  Here's the query: INSERT INTO jobemails (jobid, to, subject, message, headers, datesent) VALUES ('340', '[email protected]', 'We\'ve received your request for a photo shoot called \'another\'.', 'message', 'headers', '2010-04-22 15:55:06') The datatypes are all correct, it always fails at the subject, so it must be how I'm escaping the values, I assume. I'm sure one of you will see my idiot mistake right away. A little help?

  Read the article

• One-to-many relationship related to many tables

  - by Andrey

  I have a scenario where: there are two (or more) tables that represent independent items. lets say Users and Companies Both of these tables need addresses stored. Each one can have one or more address In a normal 1 to many scenario Addresses table woudl just have a UserId or a CompanyId creating a normal 1 to many relationship. In this case i have a few approaches i can think of the Addresses table could have both a UserId and a CompanyId and only one would be used for each record. 2 keys could be used ObjectId and ObjectType So Object id would have a UserId or a CompanyId, and ObjectType woudl be User or Company Create an ObjectTable and add ObjectId to Users and Companies. Addresses would then have an OjbectId I do not really like any of these solutions. i am wondering what is the best approach here. On another note i will most likely user linqtosql for my data access layer.

  Read the article

• Returning Null values with COUNT

  - by Randy B.

  With this query, I get a result that is two short of the table because they are not included in count, and I would like get the NULL values in the result. To do this, I am pretty sure I need to use a subquery of some kind, but I am not sure how, since the attribute in question is an aggregate. SELECT Equipment.SerialNo , Name, COUNT(Assignment.SerialNo) FROM Equipment INNER JOIN Assignment ON Assignment.SerialNo = Equipment.SerialNo GROUP BY Equipment.SerialNo, Name

  Read the article

• What is wrong with this simple update query?

  - by Kyle Noland

  I get no error message, but the row is not updated. The rows integer is set 1 following the query, indicating that 1 row was affected. String query = "UPDATE contacts SET contact_name = '" + ContactName.Text.Trim() + "', " + "contact_phone = '" + Phone.Text.Trim() + "', " + "contact_fax = '" + Fax.Text.Trim() + "', " + "contact_direct = '" + Direct.Text.Trim() + "', " + "company_id = '" + Company.SelectedValue + "', " + "contact_address1 = '" + Address1.Text.Trim() + "', " + "contact_address2 = '" + Address2.Text.Trim() + "', " + "contact_city = '" + City.Text.Trim() + "', " + "contact_state = '" + State.SelectedValue + "', " + "contact_zip = '" + Zip.Text.Trim() + "' " + "WHERE contact_id = '" + contact_id + "'"; String cs = Lib.GetConnectionString(null); SqlConnection conn = new SqlConnection(cs); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = query; cmd.Connection.Open(); int rows = cmd.ExecuteNonQuery();

  Read the article

• Something wrong on my very first LINQ to SQL c # code

  - by user334813

  using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace Advanced_LinQ_Query { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private DataClasses1DataContext database = new DataClasses1DataContext(); private void Form1_Load(object sender, EventArgs e) { database.Log= Console.Out; comboBox.SelectedIndex=0; } private void titleBindingNavigatorSaveItem_Click(object sender, EventArgs e) { Validate(); titleBindingSource.EndEdit(); database.SubmitChanges(); comboBox.SelectedIndex=0; } private void comboBox_SelectedIndexChanged(object sender, EventArgs e) { switch (comboBox.SelectedIndex) { case 0: titleBindingSource.DataSource = from Title in database.Titles orderby Title.BookTitle select Title; break; case 1: titleBindingSource.DataSource = from Title in database.Titles where Title.Copyright == "2008" orderby Title.BookTitle select Title; break; case 2: titleBindingSource.DataSource = from Title in database.Titles where Title.BookTitle.EndsWith("How to Program") orderby Title.BookTitle select Title; break; } titleBindingSource.MoveFirst(); } } } no connection seems to built after debugging between Title table in my database (book.mdf) and titleBindingSource! Where is the problem?

  Read the article

• NUmber of rows in Oracle SQL Select?

  - by twelshesgi

  I need to nkw how many records were returned in a select in oracle. Currently, I do two queries: SELECT COUNT(ITEM_ID) FROM MY_ITEMS; SELECT * FROM MY_ITEMS; I need to know the COUNT but I hate doing two queries. Is there a way to do: SELECT * FROM MY_ITEMS and then find out how many records are in there?

  Read the article

• linq2sql left join with "multiselect"

  - by just_azho

  Hi, folks I'm trying to achieve following by linq2sql, but not successful. I've Member and Reference tables. DB is design in such a manner that Member can have multiple (=0) References. What I want as a result of query is, list (rows) of members, where all references of the member are "collected" in one column. What I had achieved is following query, but for this one there exist a row for each Reference. var refs = (from m in db.Members join r in db.References on m.PID equals r.PID into g from o in g.DefaultIfEmpty() select new { member = m, name = (o == null ? "" : o.NameSurname) }); I feel I need to insert SelectMany somewher :) Could you please give hints on achieving the goal?

  Read the article

• Sql Server Management Studio Express (2005 or 2008) - Save Password Doesn't work

  - by hamlin11

  I always click "Remember Password" when connecting to a database server. Sometimes when I come back into SSMS, it has it remembered, sometimes not. More often it does not. I regularly connect to about 4 different servers, it's fairly random when it works for which servers. How can I get this to work all the time?

  Read the article

• Centralizing / Abstracting MSSQL Data from Multiple Tables / Databases

  - by davemackey

  If one has a number of databases (due to separate application front-ends) that provide a complete picture - for example a CRM, accounting, and product database - what methods are available to centralize/abstract this data for easy reporting? Essentially, I'm wondering if there is a way to automatically pull data from multiple databases into a central repository that is continuously updated from the three databases and which can be used for reporting? I'm also open to alternative best practice suggestions?

  Read the article

• Java: Do something on event in SQL Database?

  - by wretrOvian

  Hello I'm building an application with distributed parts. Meaning, while one part (writer) maybe inserting, updating information to a database, the other part (reader) is reading off and acting on that information. Now, i wish to trigger an action event in the reader and reload information from the DB whenever i insert something from the writer. Is there a simple way about this? Would this be a good idea? : // READER while(true) { connect(); // reload info from DB executeQuery("select * from foo"); disconnect(); }

  Read the article

• How to avoid Foreign Keys constraints for all tables in DB truncate ?

  - by eugeneK

  Hi, for designing purposes i need to truncate all DB which has lots of FK's. I cannot use DELETE command simply because some tables set with Identity of TinyInts and contain about 150 items. this is a query ( truncate all tables in selected DB ) i'm trying to run Declare @t varchar (1024) Declare tbl_cur cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' OPEN tbl_cur FETCH NEXT from tbl_cur INTO @t WHILE @@FETCH_STATUS = 0 BEGIN EXEC ('TRUNCATE TABLE '+ @t) FETCH NEXT from tbl_cur INTO @t END CLOSE tbl_cur DEALLOCATE tbl_Cur What the best and easiest way to achieve truncate on DB with many FK's ?

  Read the article

• How to generate alphanumeric tokens in SQL Server?

  - by CodeToGlory

  I am looking to generate soem alpha numeric tokens. Is there a function that can do to generate set length tokens?

  Read the article

• SQL - Getting Most Recent Date From Multiple Columns

  - by ScottSEA

  Assume a rowset containing the following EntryID Name DateModified DateDeleted ----------------------------------------------- 1 Name1 1/2/2003 NULL 2 Name1 1/3/2005 1/5/2008 3 Name1 1/3/2006 NULL 4 Name1 NULL NULL 5 Name1 3/5/2008 NULL I need to return the largest (i.e. most recent) non-null date from DateModified and DateDeleted, in this case 3/5/2008.

  Read the article

• alternative to lag SQL command

  - by mahen

  I have a table which has a table like this. Month-----Book_Type-----sold_in_Dollars Jan----------A------------ 100 Jan----------B------------ 120 Feb----------A------------ 50 Mar----------A------------ 60 Mar----------B------------ 30 and so on I have to calculate the expected sales for each month and book type based on the last 2 months sales. So for March and type A it would be (100+50)/2 = 75 For March and type B it is 120/1 since no data for Feb is there. I was trying to use the lag function but it wouldn't work since there is data missing in a few rows. Any ideas on this?

  Read the article

• Dynamic SQL and Funtions

  - by Unlimited071

  Hi all, is there any way of accomplishing something like the following: CREATE FUNCTION GetQtyFromID ( @oricod varchar(15), @ccocod varchar(15), @ocmnum int, @oinnum int, @acmnum int, @acttip char(2), @unisim varchar(15) ) AS BEGIN DECLARE @Result decimal(18,8) DECLARE @SQLString nvarchar(max); DECLARE @ParmDefinition nvarchar(max); --I need to execute a query stored in a cell which returns the calculated qty. --i.e of AcuQry: select @cant = sum(smt) from table where oricod = @oricod and ... SELECT @SQLString = AcuQry FROM OinActUni WHERE (OriCod = @oricod) AND (ActTipCod = @acttip) AND (UniSim = @unisim) AND (AcuEst > 0) SET @ParmDefinition = N' @oricod varchar(15), @ccocod varchar(15), @ocmnum int, @oinnum int, @acmnum int, @cant decimal(18,8) output'; EXECUTE sp_executesql @SQLString, @ParmDefinition, @oricod = @oricod, @ccocod = @ccocod, @ocmnum = @ocmnum, @oinnum = @oinnum, @acmnum = @acmnum, @cant = @result OUTPUT; RETURN @Result END The problem with this approach is that it is prohibited to execute sp_excutesql in a function... What I need is to do something like: select id, getQtyFromID(id) as qty from table The main idea is to execute a query stored in a table cell, this is because the qty of something depends on it's unit. the unit can be days or it can be metric tons, so there is no relation between the units, therefore the need of a specific query for each unit.

  Read the article

• Simple check for SELECT query empty result

  - by den-javamaniac

  Hi. Can anyone point out how to check if a select query returns non empty result set? For example I have next query: SELECT * FROM service s WHERE s.service_id = ?; Should I do something like next: ISNULL(SELECT * FROM service s WHERE s.service_id = ?) to test if result set is not empty?

  Read the article

• subqueries in UPDATE SET (sql server 2005)

  - by itdebeloper

  I have a question about using subqueries in an Update statement. My example: UPDATE TRIPS SET locations = city + ', ' FROM (select Distinct city from poi where poi.trip_guid = trips.guid) Is it possible to refer to main table value (trips.guid) in subqueries? When i try to use trips.guid I get the error: "The multi-part identifier "trips.guid" could not be bound."

  Read the article

• problem with sql statement using vb.net

  - by newBie

  hi i got some problem i got this 3 line statement: Dim infoID As Integer = objCommand1.ExecuteScalar() Dim strSQL2 As String = "insert into feedBackHotel (infoID, feedBackView) values(" + infoID + ",'" + FeedBack + "')" Dim objCommand2 As New SqlCommand(strSQL2, conn) objCommand2.ExecuteNonQuery() the problem here is..the strSQL2 can capture the infoID from previous statement, but it didnt insert into the database. it will pop out this error "Conversion from string "insert into feedBackHotel (infoI" to type 'Double' is not valid." in both table related, use same data type (int) but for the feedBackHotel's infoID i allow it to be null..bcoz if i make it not null, it will show another error.. im using vb.net ..can anyone help?

  Read the article

• ASP error 424 Object required

  - by jeff

  I've got a problem with the following code pasted below, the problem seems to be coming from the openastextstream this carries on from another question: Set str_text_stream = obj_file.OpenAsTextStream(ForReading, TristateUseDefault) response.Write "<table>" int_j = 0 int_x = 0 Err.number = 0 Do While Not str_text_stream.AtEndOfStream str_po_insert_sql = "INSERT into tbl_purchase_orders (purchase_order_number, purchase_order_vendor_number, purchase_order_vendor_name) " str_po_insert_sql = str_po_insert_sql & " VALUES (" str_line = str_text_stream.readline arr_line = Split(str_line, """,""", -1) if Ubound(arr_line) <> 2 then Response.write "<tr><td>The line " & str_line & " could not be imported.</td></tr>" int_x = 1 else int_y = Instr(arr_line(2), """,") - 1 str_field_0 = Replace(arr_line(0), """", "") str_field_0 = Replace(str_field_0, "'", "''") str_field_1 = Replace(arr_line(1), "'", "''") str_field_2 = Left(arr_line(2), int_y) str_field_2 = Replace(str_field_2, "'", "''") str_po_insert_sql = str_po_insert_sql & "'" & str_field_0 & "', '" & arr_line(1) & "', '" & str_field_2 & "')" rs_po_insert.Open str_po_insert_sql, dbConnection, 3 if Err.number <> 0 then Response.write "<tr><td>The line " & str_po_insert_sql & " was imported.</td></tr>" end if Err.number = 0 int_j = int_j + 1 end if loop data from test import: "4501366934","800002","Clancy Docwra Ltd",04/05/2010 00:00:00 "4501366935","800004","Clancy Docwra Ltd",04/05/2010 00:00:00 "4501366936","800004","Clancy Docwra Ltd",04/05/2010 00:00:00

  Read the article

• SQL: How to use a column that was just added

  - by sbenderli

  I am trying to add 2 columns and then I would like to set some values to them but I get a compile-error saying the column does not exist. I am using the following script: IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where table_name = 'SYSTM_FRM' and column_name = 'SF_Ip_TXT') ALTER TABLE SYSTM_FRM add SF_Ip_TXT NVARCHAR(20) IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where table_name = 'SYSTM_FRM' and column_name = 'SF_Port_NUM') ALTER TABLE SYSTM_FRM add SF_Port_NUM int Update dbo.SYSTM_FRM SET dbo.SYSTM_FRM.SF_Ip_TXT = dbo.FRM.FRM_Ip_TXT, dbo.SYSTM_FRM.SF_Port_NUM = dbo.FRM.FRM_Port_NUM FROM dbo.FRM INNER JOIN dbo.SYSTM_FRM ON dbo.FRM.FRM_RCRD_NUM = dbo.SYSTM_FRM.SF_FrameRecord_NUM Is there any way to use a column that I am adding in the same script?

  Read the article

• Union and order by

  - by David Lively

  Consider a table like tbl_ranks -------------------------------- family_id | item_id | view_count -------------------------------- 1 10 101 1 11 112 1 13 109 2 21 101 2 22 112 2 23 109 3 30 101 3 31 112 3 33 109 4 40 101 4 51 112 4 63 109 5 80 101 5 81 112 5 88 109 I need to generate a result set with the top two(2) rows for a subset of family ids (say, 1,2,3 and 4) ordered by view count. I'd like to do something like select top 2 * from tbl_ranks where family_id = 1 order by view_count union all select top 2 * from tbl_ranks where family_id = 2 order by view_count union all select top 2 * from tbl_ranks where family_id = 3 order by view_count union all select top 2 * from tbl_ranks where family_id = 4 order by view_count but, of course, order by isn't valid in a union all context in this manner. Any suggestions? I know I could run a set of 4 queries, store the results into a temp table and select the contents of that temp as the final result, but I'd rather avoid using a temp table if possible. Note: in the real app, the number of records per family id is indeterminate, and the view_counts are also not fixed as they appear in the above example.

  Read the article

• SQL Server PIVOT on key-value table

  - by Zenox

  I have a table that has attributes based on a key-value. Example: CREATE TABLE ObjectAttributes ( int objectId, key nvarchar(64), value nvarchar(512) ) When I select from this I get: objectId key value ---------------------------- 1 Key 1 Value 1 1 Key 2 Value 2 I was wondering if I could use the PIVOT syntax to turn this into: objectId Key 1 Key 2 --------------------------- 1 Value 1 Value 2 I know all of my tables will have the same keys. (Unfortunately I cannot easily change the table structure. This is what is leading me to attempt using PIVOTS). The big issue here though is that pivots require an aggression function to be used. Is there a way to avert this? Am I completely wrong attempting this? Or is there a better solution?

  Read the article

• SQL Reporting Services: Finding the folder a report is in

  - by Bob

  Hi there, If I have the report name how can I programmatically get the name of the project/folder the report is in? So for example if I have a report like so http://server/Reports/Pages/Report.aspx?ItemPath=/ReportProject1/ReportName Given "ReportName" how can I figure out that the report is in the folder "ReportProject1"? So I guess is there a function where I can pass int he report name and get it's details or else query the report server for a list of its report folders and I can loop through these and check some how that the report is inside?

  Read the article

< Previous Page | 388 389 390 391 392 393 394 395 396 397 398 399  | Next Page >