Search Results

Search found 1208 results on 49 pages for 'tsql'.

Page 21/49 | < Previous Page | 17 18 19 20 21 22 23 24 25 26 27 28  | Next Page >

• find rows that fall between a day and time of the week in sql server

  - by Corgalore

  I have a table of rows in MS SQL that contain a start and end day of the week, hour, and time. I need a T-SQL query that can pull rows from that table where GETDATE matches the day of week and time of those rows. Specifically, I need the query to work if a row has a day/time that starts on one day of the week and ends on the next day. Here's the structure I'm working with: _start_day_of_week (int) = 5_start_hour (int) = 15_start_minute (int) = 30 _end_day_of_week (int) = 6_end_hour (int) = 2 _end_minute (int) = 30 _title (string) = 'My Sample Row' _id (int) = 1 How would I retrieve this row given the current DATETIME?

  Read the article

• How do I return an empty result set from a procedure using T-SQL?

  - by Kivin

  I'm interested in returning an empty result set from SQL Server stored procedures in certain events. The intended behaviour is that a L2SQL DataContext.SPName().SingleOrDefault() will result in CLR null value. I'm presently using the following solution, but I'm unsure whether it would be considered bad practice, a performance hazard (I could not find one by reading the execution plan), or if there is simply a better way: SELECT * FROM [dbo].[TableName] WHERE 0 = 1; The execution plan is a constant scan with a trivial cost associated with it. The reason I am asking this instead of simply not running any SELECTs is because I'm concerned previous SELECT @scalar or SELECT INTO statements could cause unintended result sets to be served back to L2SQL. Am I worrying over nothing?

  Read the article

• SQL Server and Table-Valued User-Defined Function optimizations

  - by John Leidegren

  If I have an UDF that returns a table, with thousands of rows, but I just want a particular row from that rowset, will SQL Server be able to handle this effciently? SELECT * FROM dbo.MyTableUDF() WHERE ID = 1 To what extent is the query optimizer capable of reasoning about this type of query? How are Table-Valued UDFs different from traidtional views if they take no parameters? Any gotchas I should know about?

  Read the article

• Store a byte[] stored in a SQL XML parameter to a varbinary(MAX) field in SQL Server 2005. Can it be

  - by Mikey John

  Store a byte[] stored in a SQL XML parameter to a varbinary(MAX) field in SQL Server 2005. Can it be done ? Here's my stored procedure: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[AddPerson] @Data AS XML AS INSERT INTO Persons (name,image_binary) SELECT rowWals.value('./@Name', 'varchar(64)') AS [Name], rowWals.value('./@ImageBinary', 'varbinary(MAX)') AS [ImageBinary] FROM @Data.nodes ('/Data/Names') as b(rowVals) SELECT SCOPE_IDENTITY() AS Id In my schema Name is of type String and ImageBinary is o type byte[].

  Read the article

• Finding One record from table having unique PK and duplicate FK

  - by Muhammad Kashif Nadeem

  I want one record from a table having unique Primary Key and duplicate Foreign Key Please see attached image below Thanks

  Read the article

• How to check if a Statistics is auto-created in a SQL Server 2000 DB using T-SQL?

  - by The Shaper

  Hi all. A while back I had to come up with a way to clean up all indexes and user-created statistics from some tables in a SQL Server 2005 database. After a few attempts it worked, but now I gotta have it working in SQL Server 2000 databases as well. For SQL Server 2005, I used SELECT Name FROM sys.stats WHERE object_id = object_id(@tableName) AND auto_created = 0 to fetch Statistics that were user-created. However, SQL 2000 doesn't have a sys.stats table. I managed to fetch the indexes and statistics in a distinguishable way from the sysindexes table, but I just couldn't figure out what the sys.stats.auto_created match is for SQL 2000. Any pointers? BTW: T-SQL please.

  Read the article

• T-SQL: Dynamic Where clause in normal SQL statement

  - by Torben H.

  Hey there, I looking for a way to dynamicly add a filter to my statment without using dynamic SQL. I want to select all computers from a table, but when I pass a computer id to the sp, I want to get only this computer. Actually I try this on DECLARE @ComputerFilter AS INT DECLARE @ComputerID AS INT SELECT Computername FROM Computer WHERE (ComputerID = @ComputerID) OR (@ComputerFilter IS NULL)) But this is 100 times slow then this statment and tooks as long as SELECT * FROM Computer SELECT Computername FROM Computer WHERE ComputerID = @ComputerID Is there a way to speed this statment up or is there any other way to solve this problem with one select und without dynamic sql?

  Read the article

• SQL - How to join on similar (not exact) columns

  - by BlueRaja

  I have two tables which get updated at almost the exact same time - I need to join on the datetime column. I've tried this: SELECT * FROM A, B WHERE ABS(DATEDIFF(second, A.Date_Time, B.Date_Time) = ( SELECT MIN(ABS(DATEDIFF(second, A.Date_Time, B2.Date_Time))) FROM B AS B2 ) But it tells me: Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression. How can I join these tables?

  Read the article

• How do I select the number of distinct days in a date range?

  - by isme

  I'm trying to use the T-SQL function DATEDIFF to select the number of distinct dates in a time period. The following query: SELECT DATEDIFF(DAY, '2012-01-01 01:23:45', '2012-01-02 01:23:45') selects 1, which is one less than I want. There are two distinct dates in the range: 2012-01-01 and 2012-01-02. It is not correct to add one to the result in the general case. The following query: SELECT DATEDIFF(DAY, '2012-01-01 00:00:00', '2012-01-02 00:00:00') selects 1, which is correct, because there is only one distinct date in the range. I'm sure there is a simple bit of arithmetic that I'm missing to calculate this. Can someone help me?

  Read the article

• How to migrate large amounts of data from old database to new

  - by adam0101

  I need to move a huge amount of data from a couple tables in an old database to a couple different tables in a new database. The databases are SQL Server 2005 and are on the same box and sql server instance. I was told that if I try to do it all in one shot that the transaction log would fill up. Is there a way to disable the transaction log per table? If not, what is a good method for doing this? Would a cursor do it? This is just a one-time conversion.

  Read the article

• Dynamically change my schema

  - by Kirk

  I am wondering if there is a way to change the schema that I am working in while inside Management Studio. For instance I may have a default schema of dbo. But there are times I may want to query objects in say the accounting schema. It would be nice if I could issue a command and make it so I no longer must include the accounting before tables and views. But the next time I go in, I will be back to default of dbo.

  Read the article

• SQL Server 2008 FTS CONTAINSTABLE Not Returning More Than Five Rows

  - by Elijah Glover

  I have a single table called "Indexes", it contains one nvarchar and three ntext columns (all Full Text Indexes). Index is up to date. CONTAINSTABLE(Indexes, *), 'test', 5) //5 results No matter what I change the above keyword too, it only returns the first 3-5 results. It should roughly return 90-120 results, for the above query. SELECT count(*) FROM Indexes WHERE [Description] like '%test%' //122 results How would I start to troubleshoot this problem?

  Read the article

• Need some serious help with self join issue.

  - by kralco626

  Well as you may know, you cannot index a view with a self join. Well actually even two joins of the same table, even if it's not technically a self join. A couple of guys from microsoft came up with a work around. But it's so complicated I don't understand it!!! The solution to the problem is here: http://jmkehayias.blogspot.com/2008/12/creating-indexed-view-with-self-join.html The view I want to apply this work around to is: create VIEW vw_lookup_test WITH SCHEMABINDING AS select count_big(*) as [count_all], awc_txt, city_nm, str_nm, stru_no, o.circt_cstdn_nm [owner], t.circt_cstdn_nm [tech], dvc.circt_nm, data_orgtn_yr from ((dbo.dvc join dbo.circt on dvc.circt_nm = circt.circt_nm) join dbo.circt_cstdn o on circt.circt_cstdn_user_id = o.circt_cstdn_user_id) join dbo.circt_cstdn t on dvc.circt_cstdn_user_id = t.circt_cstdn_user_id group by awc_txt, city_nm, str_nm, stru_no, o.circt_cstdn_nm, t.circt_cstdn_nm, dvc.circt_nm, data_orgtn_yr go Any help would be greatly apreciated!!! Thanks so much in advance!

  Read the article

• Convert a Date to a String in Sqlite

  - by Billy

  Is there a way to convert a date to a string in Sqlite? For example I am trying to get the min date in Sqlite: SELECT MIN(StartDate) AS MinDate FROM TableName I know in SQL Server I would use the SQL below to accomplish what I am trying to do: SELECT CONVERT(VARCHAR(10), MIN(StartDate), 101) AS MinDate FROM TableName Thanks!

  Read the article

• Get all related products based on their full-text search relationship

  - by MikeJ

  I have a Product table with the fields Id, Title, Description, Keywords (just comma separated list of keywords). Table is full-text indexed. When I view one product, I do a query and search the full-text catalog for any related products based on the Keywords field. select * from Products where Contains(Products.*, @keywordsFromOneProduct) Works like a charm. Now I would like to list all products and all their related products in a big list and I want to avoid calling this method for each item. Any ideas how could I do it? I was thinking about a job that would go through products one by one and build a one-many mapping table (fields ProductId, RelatedProductId), but I wonder is there a better way?

  Read the article

• Handling Datetime with decimal '2010-02-14 20:18:58.313000000'

  - by AaronLS

  In SQL Server I have some textual data in varchar fields I am trying to convert to datetime's. The funny thing is this data at some point was in a datetime field, exported to flat file, and now I am reimporting it. The problem is it is in this format 2010-02-14 20:18:58.313000000 and the conversion to datetime fails. I have no idea how it ended up like this when it was originally extracted from a datetime column. Basically a table was exported to a flat file by someone else. The original table was lost. I am reimporting back from the flatfile. I could just drop the decimal but this would be like throwing out some of the data. I'd like to maintain as much precision as possible. How can I import this data from the varchar column back into a datetime column and preserve as much accuracy as possible?

  Read the article

• Help on understanding multiple columns on an index?

  - by Xaisoft

  Assume I have a table called "table" and I have 3 columns, a, b, and c. What does it mean to have a non-clustered index on columns a,b? Is a nonclustered index on columns a,b the same as a nonclustered index on columns b,a? (Note the order). Also, Is a nonclustered index on column a the same as a nonclustered index on a,c? I was looking at the website sqlserver performance and they had these dmv scripts where it would tell you if you had overlapping indexes and I believe it was saying that having an index on a is the same as a,b, so it is redundant. Is this true about indexes? One last question is why is the clustered index put on the primary key. Most of the time the primary key is not queried against, so shouldn't the clustered index be on the most queried column. I am probably missing something here like having it on the primary key speeds up joins? Great explanations. Should I turn this into a wiki and change the title index explanation?

  Read the article

• Calculating Average Percentage Yield (APY) with T-SQL

  - by M. Travis Volker

  How do you calculate Average Percentage Yield (APY) with T-SQL? Is there a system function to calculate APY or do I have to create one? Note: APY = (1 + r/n )^n – 1 where r is the stated annual interest rate and n is the number of times you’ll compound per year.

  Read the article

• T SQL WHERE Clause

  - by johniom

  Hiya Guys, I'm trying to develop some code which pulls latest informtion only. What it is when a user goes into a form there a subtable and everytime they change something it creates a new row in the colum called type12_OriginalNoteID which puts its own unquie number in. Another feild called type12_OriginalNoteID keeps the same number - which keeps track of what the oringal number was before any changes were made. I do have some code which does pull the latest information but it does not pull anything if the user has not made any changes to the form - and thats because the type12_OriginalNoteID is null. The code is as follows ea.type12_NoteID IN (SELECT TOP 1 ea.type12_NoteID FROM UserAssessv1aidsadaptations ea1 WHERE ea.type12_NoteID = ea1.type12_OriginalNoteID ORDER BY ea.type12_UpdatedDate DESC) An example of the data is as follows :- type12_note ID - 12 type12_OriginalNoteID NULL type12_UpdatedDate - 11/03/2010 What would be the solution to show the ifnroamtion if no one has made any changes to the subtable? - adding an if statement to run if type12_OriginalNoteID is null??

  Read the article

• Addresses stored in SQL server have many small variations(errors)

  - by MAW74656

  I have a table in my database which stores packing slips and their information. I'm trying to query that table and get each unique address. I've come close, but I still have many near misses and I'm looking for a way to exclude these near duplicates from my select. Sample Data CompanyCode CompanyName Addr1 City State Zip 10033 UNITED DIE CUTTING & FINISHIN 3610 HAMILTON AVE CLEVELAND Ohio 44114 10033 UNITED DIE CUTTING & FINISHING 3610 HAMILTON AVE CLEVELAND Ohio 44114 10033 UNITED DIE CUTTING & FINISHING 3610 HAMILTON AVE. CLEVELAND Ohio 44114 10033 UNITED DIE CUTTING & FINISHING 3610 HAMILTON AVENUE CLEVELAND Ohio 44114 10033 UNITED DIECUTTING & FINISHING 3610 HAMILTON AVE CLEVELAND Ohio 44144 10033 UNITED FINISHING 3610 HAMILTON AVE CLEVLAND Ohio 44114 10033 UNITED FINISHING & DIE CUTTING 3610 HAMILTON AVE CLEVELAND Ohio 44114 And all I want is 1 record. Is there some way I can get the "Average" record? Meaning, if most of the records say CLEVELAND instead of CLEVLAND, I want my 1 record to say CLEVELAND. Is there any way to par this data down to what I'm looking for? Desired Output CompanyCode CompanyName Addr1 City State Zip 10033 UNITED DIE CUTTING & FINISHING 3610 HAMILTON AVE CLEVELAND Ohio 44114

  Read the article

• T-SQL query with date range

  - by Moo

  Hi, I have a fairly weird 'bug' with a simple query, and I vaguely remember reading the reason for it somewhere a long time ago but would love someone to refresh my memory. The table is a basic ID, Datetime table. The query is: select ID, Datetime from Table where Datetime <= '2010-03-31 23:59:59' The problem is that the query results include results where the Datetime is '2010-04-01 00:00:00'. The next day. Which it shouldn't. Anyone? Cheers Moo

  Read the article

• T-SQL - Date rounding and normalization

  - by arun prakash

  Hi: I have a stored procedure that rounds a column with dates in (yyyy:mm:dd hh:mM:ss) to the nearest 10 minute handle (yyyy:mm:dd hh:mM) 20100303 09:46:3000 ------ 20100303 09:50 but i want to chage it to round it off to the nearest 15 minute handle: 20100303 09:46:3000 ------20100303 09:45 here is my code : IF OBJECT_ID(N'[dbo].[SPNormalizeAddWhen]') IS NOT NULL DROP PROCEDURE [dbo].[SPNormalizeAddWhen] GO CREATE PROCEDURE [dbo].[SPNormalizeAddWhen] As declare @colname nvarchar(20) set @colname='Normalized Add_When' if not exists (select * from syscolumns where id=object_id('Risk') and name=@colname) exec('alter table Risk add [' + @colname + '] datetime') declare @sql nvarchar(500) set @sql='update Risk set [' + @colname + ']=cast(DATEPART(yyyy,[add when]) as nvarchar(4)) + ''-'' + cast(DATEPART(mm,[add when]) as nvarchar(2)) + ''-'' + cast(DATEPART(dd,[add when]) as nvarchar(2)) + '' '' + cast(DATEPART(Hh,[add when]) as nvarchar(2)) + '':'' + cast(round(DATEPART(Mi,[add when]),-1) as nvarchar(2)) ' print @sql exec(@sql) GO

  Read the article

• Get mutually and non mutually existening Fields in same table in Two columns

  - by ranabra

  This is a question similar to another question I posted here but is a little different. I am trying to get a list of all instances of mutual and non-mutual existing Users. What I mean is that the returned result from the query will return a list of users along with their co-worker. It is similar to the question here, but the difference is that non mutual users will be returned too and with out the "duplicity" mutually existing users return in the list (See image below in-order simplify it all). I took the original answer from Thomas (Thanx again Thomas) Select D1.u_username, U1.Permission, U1.Grade, D1.f_username, U2.Permission, U2.Gradefrom tblDynamicUserList As D1    Join tblDynamicUserList As D2        On D2.u_username = D1.f_username            And D2.f_username = D1.u_username    Join tblUsers As U1        On U1.u_username = D1.u_username    Join tblUsers As U2        On U2.u_username = D2.u_username and after some several trials I commented out 2 lines (Below). The returned result are exactly as described in the beginning of this question, but with the "duplicity" returned by mutually existing users in the table. How can I eliminate this duplicity? Select D1.u_username, U1.Permission, U1.Grade, D1.f_username, U2.Permission, U2.Gradefrom tblDynamicUserList As D1    Join tblDynamicUserList As D2        On D2.u_username = D1.f_username            /* And D2.f_username = D1.u_username */    Join tblUsers As U1        On U1.u_username = D1.u_username    Join tblUsers As U2        On U2.u_username = D2.u_username /* WHERE D1.U_userName < D1.f_username */ *Screenshot that hopefully helps explain it all. Database is SQL 2005. Many thanx in advance

  Read the article

• How to do regex HTML tag replace in MS SQL?

  - by timmerk

  I have a table in SQL Server 2005 with hundreds of rows with HTML content. Some of the content has HTML like: <span class=heading-2>Directions</span> where "Directions" changes depending on page name. I need to change all the <span class=heading-2> and </span> tags to <h2> and </h2> tags. I wrote this query to do content changes in the past, but it doesn't work for my current problem because of the ending HTML tag: Update ContentManager Set ContentManager.Content = replace(Cast(ContentManager.Content AS NVARCHAR(Max)), 'old text', 'new text') Does anyone know how I could accomplish the span to h2 replacing purely in T-SQL? Everything I found showed I would have to do CLR integration. Thanks!

  Read the article

• Return a value if no rows are found SQL

  - by Matt

  Here's my simple query. If I query a record that doesn't exist then I will get nothing returned. I'd prefer that false (0) is returned in that scenario. Looking for the simplist method to account for no records. SELECT CASE WHEN S.Id IS NOT NULL AND S.Status = 1 AND (S.WebUserId = @WebUserId OR S.AllowUploads = 1) THEN 1 ELSE 0 END AS [Value] FROM Sites S WHERE S.Id = @SiteId

  Read the article

< Previous Page | 17 18 19 20 21 22 23 24 25 26 27 28  | Next Page >