Search Results

Search found 28052 results on 1123 pages for 't sql tuesday'.

Page 357/1123 | < Previous Page | 353 354 355 356 357 358 359 360 361 362 363 364  | Next Page >

• Saving Double.MinValue in SQLServer

  - by PatrickL

  Using a TSQL update command against a SQLServer database, how can I update a column of type FLOAT with the smallest possible double value? The smallest possible double value in hex notation being 3ff0 0000 0000 0001 (http://en.wikipedia.org/wiki/Double%5Fprecision)

  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

• How to enumerate returned rows in SQL?

  - by SilentGhost

  I was wondering if it would be possible to enumerate returned rows. Not according to any column content but just yielding a sequential integer index. E.g. select ?, count(*) as usercount from users group by age would return something along the lines: 1 12 2 78 3 4 4 42

  Read the article

• What is wrong with this SQL syntax?

  - by Finbarr

  UPDATE files SET filepath = REPLACE(filepath, `sites/somedomain.com/files/`, `sites/someotherdomain.com/files/`); I have a table called files with a field called filepath. MySQL returns this error: Unknown column 'sites/somedomain.com/files/' in 'field list'

  Read the article

• Aggregate SQL column values by time period

  - by user305688

  I have some numerical data that comes in every 5 minutes (i.e. 288 values per day, and quite a few days worth of data). I need to write a query that can return the sums of all values for each day. So currently the table looks like this: 03/30/2010 00:01:00 -- 553 03/30/2010 00:06:00 -- 558 03/30/2010 00:11:00 -- 565 03/30/2010 00:16:00 -- 565 03/30/2010 00:21:00 -- 558 03/30/2010 00:26:00 -- 566 03/30/2010 00:31:00 -- 553 ... And this goes on for 'x' number of days, I'd like the query to return 'x' number of rows, each of which containing the sum of all the values on each day. Something like this: 03/30/2010 -- <sum> 03/31/2010 -- <sum> 04/01/2010 -- <sum> The query will go inside a Dundas webpart, so unfortunately I can't write custom user functions to assist it. All the logic needs to be in just the one big query. Any help would be appreciated, thanks. I'm trying to get it to work using GROUP BY and DATEPART at the moment, not sure if it's the right way to go about it.

  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

• 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

• 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

• 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

• best practises to delete a set of tables in sql 2008

  - by Hari

  Basically i want to keep the transaction very simple but i should be able to rollback if any error in the later part. Something like mentioned below, BEGIN TRANSACTION DELETE SET 1(this will delete first set of table) COMMIT DELETE SET 2 (will delete second set of table) If any error occurs while deleting set 2 i should be able to rollback set 1 transaction as well.Let me know if we have any options to do like this. Appreciate for your help.

  Read the article

• How to run stored procedure 1000 times

  - by subt13

  I have a stored procedure that I'm using to populate a table with about 60 columns. I have genereated 1000 exec statements that look like this: exec PopulateCVCSTAdvancement 174, 213, 1, 0, 7365 exec PopulateCVCSTAdvancement 174, 214, 1, 0, 7365 exec PopulateCVCSTAdvancement 175, 213, 0, 0, 7365 Each time the stored procedure will be inserting anywhere from 1 to 3,000 records (usually around 2,000 records). The "server" is running desktop hardware with 4 gigs of available memory on a server OS. The problem I have is that after the first 10-15 executes of an average of 1-2 seconds each time, the next 10-15 seem to never finish. Am I doing this correctly? How should I do this? Thanks! Top 10 waiters: LAZYWRITER_SLEEP SQLTRACE_INCREMENTAL_FLUSH_SLEEP REQUEST_FOR_DEADLOCK_SEARCH XE_TIMER_EVENT FT_IFTS_SCHEDULER_IDLE_WAIT CHECKPOINT_QUEUE LOGMGR_QUEUE SLEEP_TASK BROKER_TO_FLUSH BROKER_TASK_STOP

  Read the article

• SQL is this equivalent to a LEFT JoIn?

  - by Jim

  Is this equivalent to a LEFT JOIN? SELECT DISTINCT a.name, b.name FROM tableA a, (SELECT DISTINCT name FROM tableB) as b It seems as though there is no link between the two tables. Is there an easier / more efficient way to write this?

  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

• sql select from a large number of IDs

  - by Claudiu

  I have a table, Foo. I run a query on Foo to get the ids from a subset of Foo. I then want to run a more complicated set of queries, but only on those IDs. Is there an efficient way to do this? The best I can think of is creating a query such as: SELECT ... --complicated stuff WHERE ... --more stuff AND id IN (1, 2, 3, 9, 413, 4324, ..., 939393) That is, I construct a huge "IN" clause. Is this efficient? Is there a more efficient way of doing this, or is the only way to JOIN with the inital query that gets the IDs? If it helps, I'm using SQLObject to connect to a PostgreSQL database, and I have access to the cursor that executed the query to get all the IDs.

  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

• 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

• 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

• how to enforce multiple unique fields in database table

  - by oo

  i have a table called Cars and the primary key of the table is 'id'. I also have a field called 'name'. I would like to make sure no one enters the same name twice even though it wont break my db integrity. what is the best way of doing this?

  Read the article

• SQL and/or LINQ query for determining daily increases in viewers

  - by Gio

  We're montioring usage of a certain resources by monitoring users logins (We can see user logins growing daily). After filtering out repeat inter day logins for users, we'd like to track the # of users using the service each day, and then using that info to determine overall incremental gains for each calendar day. Our table is pretty simple: class ServiceLogin { String login; DateTime loginTime; }

  Read the article

• Microsoft SQL Server 2005 time of day data type

  - by Kevin

  Is there a data type which represents time of day? Such as a datetime data type without the date.

  Read the article

• FreeText COUNT query on multiple tables is super slow

  - by Eric P

  I have two tables: **Product** ID Name SKU **Brand** ID Name Product table has about 120K records Brand table has 30K records I need to find count of all the products with name and brand matching a specific keyword. I use freetext 'contains' like this: SELECT count(*) FROM Product inner join Brand on Product.BrandID = Brand.ID WHERE (contains(Product.Name, 'pants') or contains(Brand.Name, 'pants')) This query takes about 17 secs. I rebuilt the FreeText index before running this query. If I only check for Product.Name. They query is less then 1 sec. Same, if I only check the Brand.Name. The issue occurs if I use OR condition. If I switch query to use LIKE: SELECT count(*) FROM Product inner join Brand on Product.BrandID = Brand.ID WHERE Product.Name LIKE '%pants%' or Brand.Name LIKE '%pants%' It takes 1 secs. I read on MSDN that: http://msdn.microsoft.com/en-us/library/ms187787.aspx To search on multiple tables, use a joined table in your FROM clause to search on a result set that is the product of two or more tables. So I added an INNER JOINED table to FROM: SELECT count(*) FROM (select Product.Name ProductName, Product.SKU ProductSKU, Brand.Name as BrandName FROM Product inner join Brand on product.BrandID = Brand.ID) as TempTable WHERE contains(TempTable.ProductName, 'pants') or contains(TempTable.BrandName, 'pants') This results in error: Cannot use a CONTAINS or FREETEXT predicate on column 'ProductName' because it is not full-text indexed. So the question is - why OR condition could be causing such as slow query?

  Read the article

• Data mixing SQL Server

  - by Pythonizo

  I have three tables and a range of two dates: Services ServicesClients ServicesClientsDone @StartDate @EndDate Services: ID | Name 1 | Supervisor 2 | Monitor 3 | Manufacturer ServicesClients: IDServiceClient | IDClient | IDService 1 | 1 | 1 2 | 1 | 2 3 | 2 | 2 4 | 2 | 3 ServicesClientsDone: IDServiceClient | Period 1 | 201208 3 | 201210 Period = YYYYMM I need to insert into ServicesClientsDone the months range from @StartDate up @EndDate. I have also a temporary table (#Periods) with the following list: Period 201208 201209 201210 The query I need is to give me back the following list: IDServiceClient | Period 1 | 201209 1 | 201210 2 | 201208 2 | 201209 2 | 201210 3 | 201208 3 | 201209 4 | 201208 4 | 201209 4 | 201210 Which are client services but the ranks of the temporary table, not those who are already inserted This is what i have: Table periods: DECLARE @i int DECLARE @mm int DECLARE @yyyy int, DECLARE @StartDate datetime DECLARE @EndDate datetime set @EndDate = (SELECT GETDATE()) set @StartDate = (SELECT DATEADD(MONTH, -3,GETDATE())) CREATE TABLE #Periods (Period int) set @i = 0 WHILE @i <= DATEDIFF(MONTH, @StartDate , @EndDate ) BEGIN SET @mm= DATEPART(MONTH, DATEADD(MONTH, @i, @FechaInicio)) SET @yyyy= DATEPART(YEAR, DATEADD(MONTH, @i, @FechaInicio)) INSERT INTO #Periods (Period) VALUES (CAST(@yyyy as varchar(4)) + RIGHT('00'+CONVERT(varchar(6), @mm), 2)) SET @i = @i + 1; END Relation between ServicesClients and Services: SELECT s.Name, sc.IDClient FROM Services JOIN ServicesClients AS sc ON sc.IDService = s.ID Services already done and when: SELECT s.Name, scd.Period FROM Services JOIN ServicesClients AS sc ON sc.IDService = s.ID JOIN ServicesClientsDone AS scd ON scd.IDServiceClient = sc.IDServiceClient

  Read the article

• Run a proc on several different values of one parameter

  - by WEFX

  I have the following query that gets run within a proc. The function MyFunction returns a table, and this query joins on that table. This proc works great when a @MyArg value is supplied. However, I’m wondering if there’s a way to run this on all @MyArg values in the database. I’m sure there’s a way to do it within a loop, but I know that loops are generally to be avoided at the db layer. I really just need to perform this for the sake of checking (and possibly cleansing) some bad data. SELECT ColumnA, ColumnB, ColumnC FROM ( SELECT a.ColumnA, a.ColumnB, a.ColumnC, ROW_NUMBER() over(partition by a.ColumnD order by f.ColumnX) as RowNum FROM dbo.MyTableA AS a INNER JOIN dbo.MyFunction(@MyArg) f ON f.myID = a.myID WHERE (a.myBit = 1 OR a.myID = @MyArg) ) AS x WHERE x.rownum = 1;

  Read the article

• 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

• 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

< Previous Page | 353 354 355 356 357 358 359 360 361 362 363 364  | Next Page >