Search Results

Search found 49224 results on 1969 pages for 'database mirroring sql se'.

Page 156/1969 | < Previous Page | 152 153 154 155 156 157 158 159 160 161 162 163  | Next Page >

• Rounding down DECIMAL(14,3) to third decimal digit in SQL 2008

  - by abatishchev

  I have to round down all incoming data with type DECIMAL(14,3) which has 3 decimal digit to the last one. I.e.: 100; 100.0; 100.00; 100.000 -> 100 100.2; 100.02; 100.20; 100.22 -> 100.xx but 100.221 -> 100.22 100.229 -> 100.22 Using which SQL operator can I check that residue of division in decimal digit is greater then zero?

  Read the article

• T-SQL query to check number of existences

  - by abatishchev

  I have next approximate tables structure: accounts: ID INT, owner_id INT, currency_id TINYINT related to clients: ID INT and currency_types: ID TINYINT, name NVARCHAR(25) I need to write a stored procedure to check existence of accounts with specific currency and all others, i.e. client can have accounts in specific currency, some other currencies and both. I have already written this query: SELECT ISNULL(( SELECT 1 WHERE EXISTS ( SELECT 1 FROM [accounts] AS A, [currency_types] AS CT WHERE A.[owner_id] = @client -- sp param AND A.[currency_id] = CT.[ID] AND CT.[name] = N'Ruble' )), 0) AS [ruble], ISNULL(( SELECT 1 WHERE EXISTS ( SELECT A.[ID] FROM [accounts] AS A, [currency_types] AS CT WHERE A.[owner_id] = @client AND A.[currency_id] = CT.[ID] AND CT.[name] != N'Ruble' )), 0) AS [foreign] Is it possible to optimize it? I'm new to (T)SQL, so thanks in advance!

  Read the article

• Inserting Large volume of data in SQL Server 2005

  - by Manjoor

  We have a application (written in c#) to store live stock market price in the database (SQL Server 2005). It insert about 1 Million record in a single day. Now we are adding some more segment of market into it and the no of records would be double (2 Millions/day). Currently the average record insertion per second is about 50, maximum is 450 and minimum is 0. To check certain conditions i have used service broker (asynchronous trigger) on my price table. It is running fine at this time(about 35% CPU utilization). Now i am planning to create a in memory dataset of current stock price. we would like to do some simple calculations. I want to know different views of members on this. Please provide your way of dealing with such situation.

  Read the article

• Table-level diff and sync procedure for T-SQL

  - by Ville Koskinen

  I'm interested in T-SQL source code for synchronizing a table (or perhaps a subset of it) with data from another similar table. The two tables could contain any variables, for example I could have base table source table ========== ============ id val id val ---------- ------------ 0 1 0 3 1 2 1 2 2 3 3 4 or base table source table =================== ================== key val1 val2 key val1 val2 ------------------- ------------------ A 1 0 A 1 1 B 2 1 C 2 2 C 3 3 E 4 0 or any two tables containing similar columns with similar names. I'd like to be able to check that the two tables have matching columns: the source table has exactly the same columns as the base table and the datatypes match make a diff from the base table to the source table do the necessary updates, deletes and inserts to change the data in the base table to correspond the source table optionally limit the diff to a subset of the base table, preferrably with a stored procedure. Has anyone written a stored proc for this or could you point to a source?

  Read the article

• SQL Average Data Based on Distance

  - by jsmith

  I'm pretty new to SQL. I have a database with records based on road/milepoints. My goal is to get an average value every 52.8 ft along the road. My related table has data every 15 ft, this table of course has a foreign key relating it to the primary table. If I wanted to pull out the average value every 52.8 ft, along a given milepost, how would I go about this? Example Data: RecID Begin_MP End_MP 100 0 0.56 RecID MP Value1 Value2 100 0 159 127.7 100 0.003 95.3 115.3 100 0.006 82.3 107 100 0.009 56.5 74.5 100 0.011 58.1 89.1 100 0.014 95.2 78.8 100 0.017 108.9 242.5 100 0.02 71.8 73.3 100 0.023 84.1 80.2 100 0.026 65.5 66.1 100 0.028 122 135.8 100 0.031 99.9 230.7 100 0.034 95.7 111.5 100 0.037 127.3 74.3 100 0.04 140.7 543.1 The first Data is an example of a Road. The second subset of data are the values I need to query out every 52.8 ft. Thank you

  Read the article

• T-SQL Where statement - OR

  - by Zyphrax

  Sorry for the vague title but I really didn't know what title to give to this problem: I have the following result set: ID Data Culture 1 A nl-NL 2 B nl-NL 3 C nl-NL 4 A en-GB 5 B en-GB 6 A en 7 B en 8 C en 9 D en 10 A nl I would like to mimic the ASP.Net way of resource selection, user Culture (nl-NL) SELECT Data FROM Tbl WHERE Culture = 'nl-NL' Or when there are no results for the specific culture, try the parent Culture (nl) SELECT Data FROM Tbl WHERE Culture = 'nl' Or when there are no results for the parent culture, try the default Culture (en) SELECT Data FROM Tbl WHERE Culture = 'en' How can I combine these SELECT-statements in one T-SQL statement? I'm using LINQ so a LINQ expression would be even greater. The OR-statement won't work, because I don't want a mix of cultures. The ORDER BY-statement won't help, because it returns multiple records per culture.

  Read the article

• SQL Server: position based on marks

  - by Rhys

  I am using SQL Server 2008. I have a Student table in which there are following fields: 1. StudentId, 2. StudentName, 3. Marks . I want to get a resultset in which there should be a column named “Position”. Something like “Select StudentId,StudentName,Marks, as Position from Student...” so that, depending on the marks a student scored, i can evaluate them as the 1st, 2nd or 20th position. If students have the same marks, then they have the same position. Thanks. Rhys

  Read the article

• Saving a huge dataset into SQL table in a XML column - MS SQL, C#.Net

  - by NLV

  Hello all I'm having a requirement where i've to save a dataset which has multiple tables in it in a sql table XML column by converting into an XML. The problem is that in some cases the dataset grows extremely huge and i get OutOfMemoryException. What i basically do is that i convert the dataset into an xml file and save it in the local disk and then load the XML again and send it as a stored procedure parameter. But when i write the dataset xml into the disk the file sizes over 700 Mb and when i load it in the XMLDocument object in memory i get OutOfMemoryException. How can get the dataset xml without saving it in a file and re-reading it again? Thank You NLV

  Read the article

• Linq to sql Error "Data at the root level is invalid " for nullable xml column

  - by Mukesh

  I have Created a table like CREATE TABLE [dbo].[tab1]( [Id] [int] NOT NULL, [Name] [varchar](100) NOT NULL, [Meta] [xml] NULL, CONSTRAINT [PK_tab1] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] When I am doing linq to sql query to fetch a data it throw an error "data at the root level is invalid linq". In further investigation I come to know that the meta column is null in that case. In real it is nullable Do I have to remove the nullable and set some blank root node as default or there is some another way to get rid of the error. My linq Query which throws error var obj1= (from obj in dbContext.tab1s where obj.id== 123 select obj).FirstOrDefault<Tab1>();

  Read the article

• XML Output is Truncated in SQL

  - by Muhammad Akhtar

  Hi, I need to return my result set in XML and this works fine, but if the number of records are increased, my xml output is truncated here is my query select t.id,t.name,t.address from test FOR XML AUTO, ROOT('Response'), ELEMENTS However I have set some option to increase the output result set like.. Tools --> Options --> Query Results --> SQL Server --> Results to Text --> Maximum number of characters displayed in each column Tools --> Options --> Results --> Maximum characters per column but still I am unable to get my desired result. please suggest my solution Thanks....

  Read the article

• How to export SQL Server 2005 query to CSV

  - by jmgant

  I want to export some SQL Server 2005 data to CSV format (comma-separated with quotes). I can think of a lot of complicated ways to do it, but I want to do it the right way. I've looked at bcp, but I can't figure out how to put the quotes around the fields (except concatenating them to the field values, which is ugly). I guess I could do it with sqlcmd and -o, but that seems ugly for the same reason. Is there a bcp way to do it? Is there a reasonable sqlcmd way to do it? Is there some great, simple utility built into the Management Studio that I'm just overlooking?

  Read the article

• SQL Exec T-SQL statement?

  - by salvationishere

  I am trying to list all of the columns from whichever Adventureworks table I choose. What T-sQL statement or stored proc can I execute to see this list of all columns? I want to use my C# web app to input one input parameter = table_name and then get a list of all the column_names as output. Right now I am trying to execute the sp_columns stored proc which works, but I can't get just the one column with select and exec combined. Does anybody know how to do this?

  Read the article

• SQL Server 2005 RIGHT OUTER JOIN not working

  - by CheeseConQueso

  I'm looking up access logs for specific courses. I need to show all the courses even if they don't exist in the logs table. Hence the outer join.... but after trying (presumably) all of the variations of LEFT OUTER, RIGHT OUTER, INNER and placement of the tables within the SQL code, I couldn't get my result. Here's what I am running: SELECT (a.first_name+' '+a.last_name) instructor, c.course_id, COUNT(l.access_date) course_logins, a.logins system_logins, MAX(l.access_date) last_course_login, a.last_login last_system_login FROM lsn_logs l RIGHT OUTER JOIN courses c ON l.course_id = c.course_id, accounts a WHERE l.object_id = 'LOGIN' AND c.course_type = 'COURSE' AND c.course_id NOT LIKE '%TEST%' AND a.account_rights > 2 AND l.user_id = a.username AND ((a.first_name+' '+a.last_name) = c.instructor) GROUP BY c.course_id, a.first_name, a.last_name, a.last_login, a.logins, c.instructor ORDER BY a.last_name, a.first_name, c.course_id, course_logins DESC Is it something in the WHERE clause that's preventing me from getting course_id's that don't exist in lsn_logs? Is it the way I'm joining the tables? Again, in short, I want all course_id's regardless of their existence in lsn_logs.

  Read the article

• Variable as numeric sent to stored procedure (SQL Server 2005)

  - by TimCarrett

  I see that with SQL Server 2005 you can pass a parameter as numeric e.g. create procedure dbo.TestSP @Param1 numeric as But what does this equate to? E.g. Numeric(10,0), Numeric(9,2), etc? We have some Developers here who are using this instead of the correct definition for the field that this parameter is going to be used against e.g. instead of using Numeric(10, 0) for the parameter @Param1. Also are there any underlying performance issues with using Numeric instead of the data type defined against the field in the table? Many thanks.

  Read the article

• Compiled Linq Queries with Built-in SQL functions

  - by Brandi

  I have a query that I am executing in C# that is taking way too much time: string Query = "SELECT COUNT(HISTORYID) FROM HISTORY WHERE YEAR(CREATEDATE) = YEAR(GETDATE()) "; Query += "AND MONTH(CREATEDATE) = MONTH(GETDATE()) AND DAY(CREATEDATE) = DAY(GETDATE()) AND USERID = '" + EmployeeID + "' "; Query += "AND TYPE = '5'"; I then use SqlCommand Command = new SqlCommand(Query, Connection) and SqlDataReader Reader = Command.ExecuteReader() to read in the data. This is taking over a minute to execute from C#, but is much quicker in SSMS. I see from google searching you can do something with CompiledQuery, but I'm confused whether I can still use the built in SQL functions YEAR, MONTH, DAY, and GETDATE. If anyone can show me an example of how to create and call a compiled query using the built in functions, I will be very grateful! Thanks in advance.

  Read the article

• T-SQL SQL Server - Stored Procedure with parameter

  - by Ricardo Conte

  Please, the first TSQL works FINE, the second does not. I guess it must be a simple mistake, since I am not used to T-SQL. Thank you for the answers. R Conte. * WORKS FINE ******************* (parm hard-coded) ALTER PROCEDURE rconte.spPesquisasPorStatus AS SET NOCOUNT ON SELECT pesId, RTRIM(pesNome), pesStatus, pesPesGrupoRespondente, pesPesQuestionario, pesDataPrevistaDisponivel, pesDataPrevistaEncerramento, pesDono FROM dbo.tblPesquisas WHERE (pesStatus = 'dis') ORDER BY pesId DESC RETURN Running [rconte].[spPesquisasPorStatus]. pesId Column1 pesStatus pesPesGrupoRespondente pesPesQuestionario pesDataPrevistaDisponivel pesDataPrevistaEncerramento pesDono 29 XXXXXXXXX xxxxx dis 17 28 5/5/2010 08:21:12 5/5/2010 08:21:12 1 28 Xxxxxxxx xxxxxxxxxxxxx dis 16 27 5/5/2010 07:44:12 5/5/2010 07:44:12 1 27 Xxxxxxxxxxxxxxxxxxxxxxx * DOES NOT WORK ************** (using a parm; pesStatus is nchar(3)) ALTER PROCEDURE rconte.spPesquisasPorStatus (@pPesStatus nchar(3) = 'dis') AS SET NOCOUNT ON SELECT pesId, RTRIM(pesNome), pesStatus, pesPesGrupoRespondente, pesPesQuestionario, pesDataPrevistaDisponivel, pesDataPrevistaEncerramento, pesDono FROM dbo.tblPesquisas WHERE (pesStatus = @pPesStatus) ORDER BY pesId DESC RETURN Running [rconte].[spPesquisasPorStatus] ( @pPesStatus = 'dis' ). pesId Column1 pesStatus pesPesGrupoRespondente pesPesQuestionario pesDataPrevistaDisponivel pesDataPrevistaEncerramento pesDono No rows affected. (0 row(s) returned) @RETURN_VALUE = 0 Finished running [rconte].[spPesquisasPorStatus]

  Read the article

• What does SQL Server trace flag 253 do?

  - by kamens

  In another question I was trying to research how to control SQL Server's query plan caches: http://stackoverflow.com/questions/2593749/is-there-an-equivalent-of-optionrecompile-or-with-recompile-for-an-entire-c ...and I found trace flag 253 via this article: http://www.sqlservercentral.com/Forums/Topic837613-146-1.aspx The article is correct, if I run DBCC TRACEON(253) and then a number of queries, I can manually check the query plan cache and see that plans have not been inserted. If I run DBCC TRACEOFF(253), query plans are cached as normal. So my question is...what else does this flag do? Does anybody know the official story?

  Read the article

• Convert T-SQL function to PL/SQL

  - by llasarov

  can you help me convert following T-SQL funcntion into Oracle. The function converts a string like service|nvretail;language|de;yyyy|2011; to a table. The main problem I have is the usage of the temp table. I could not find any equivalent to it in Oracle. CREATE FUNCTION [dbo].[TF_ConvertPara] ( @parastringNVARCHAR(max) ) RETURNS @para TABLE ( [Key] varchar(max), [Value] varchar(max) ) begin DECLARE @NextString NVARCHAR(40) DECLARE @Pos1 INT DECLARE @Pos2 INT DECLARE @NextPos INT DECLARE @Delimiter1 NCHAR=';' DECLARE @Delimiter2 NCHAR='|' if substring(@paraString, len(@paraString) - 1, 1) <> @Delimiter1 SET @paraString = @paraString + @Delimiter1 SET @Pos1 = charindex(@Delimiter1, @paraString) WHILE (@pos1 <> 0) BEGIN SET @NextString = substring(@paraString, 1, @Pos1 - 1) SET @paraString = substring(@paraString, @pos1 + 1, len(@paraString)) SET @pos1 = charindex(@Delimiter1, @paraString) SET @Pos2 = charindex(@Delimiter2, @NextString) if (@Pos2 > 0) begin insert into @para values (substring(@NextString, 1, @Pos2 - 1), substring(@NextString, @Pos2 + 1, len(@NextString))) end END return; end Thank you in advance.

  Read the article

• Visual Studio error on valid SQL

  - by Sam

  I'm getting an annoying error in visual studio for SQL that executes fine. SELECT InvoiceLines.LineID, InvoiceLines.InvoiceID, InvoiceLines.Text, InvoiceLines.Rate, InvoiceLines.Count, InvoiceLines.Rate * InvoiceLines.Count AS LineTotal, ((InvoiceLines.Rate * InvoiceLines.Count) * (1 + Invoices.VatRate / 100)) * (1 - CAST(Invoices.Discount AS money) * InvoiceLines.ApplyDiscount / 100) AS LineTotalIncVat, InvoiceLines.ApplyDiscount FROM InvoiceLines LEFT JOIN Invoices ON Invoices.InvoiceID = InvoiceLines.InvoiceID What LineTotalIncVat is trying to do is compute the total for the invoice item while adding the vat and subtracting the discount, and yes, probably better to do this in code (would if I could) The error visual studio gives is: There was an error parsing the query [token line number =1, token line offset =14, token in error = InvoiceLines] Even though it will validate and execute without a problem in the query builder...

  Read the article

• T-SQL Picking up active IDs from a comma seperated IDs list

  - by hammayo

  I have two tables "Product" having following structure: ProductID,ProductName, IsSaleTypeA, IsSaleTypeB, IsSaleTypeC 1, AAA, N, N, N 2, BBB, N, Y, N -- active 3, CCC, N, N, N 4, DDD, Y, N, N -- active 5, EEE, N, N, N 6, FFF, N, N, N 7, FFE, N, N, N 8, GGG, N, N, N 9, HHH, Y, N, N -- active The second table "ProductAllowed" having following structure where ProductIDs is a comma separated string filed having mix of active and inactive product ids based on their IsSaleType mode. ProductCode, ProductIDs AMRLSPN, "1,2" AMRLOFD, "1,3" BLGHVF, "2,4,6" BLGHVO, "2,4" BLGHVD, "3,5" BLGSDO, "0" CHOHVF, "1,6" CHOHVP, "1,2,7,8" ... ... Q: Is there a t-sql query that will return a list of active records from the "ProductAllowed" table if any of three IsSaleType fileds is/are switched on for a product. Based on the sample data the ProductAllowed records should return following records. AMRLSPN BLGHVF BLGHVO BLGSDO CHOHVP This needs to be applied in a SQLSERVER 2000 database containing aprox 150000 records. Thanks

  Read the article

• SQL Split function.

  - by Wardy

  Hi guys, I'm looking for a way to do this ... SELECT FirstName, LastName, Split(AddressBlock, ' ', 1), Split(AddressBlock, ' ', 2), PostCode FROM Contacts The arguments I want to pass are ... The address The separator (current situation requires 2 spaces but this might be a comma or a space followed by a comma) or something else (it varies). The address part I want to return (i don't always need all parts of the split result). I seem to be able to find a few examples of splitting functions about the internet but they return a table containing the entire set of split parts. My SQL skills aren't that great so I need the answer to be ultra simple. I'm always working with nvarchar data and the function needs to be reusable.

  Read the article

• MS Sql Full-text search vs. LIKE expression

  - by Marks

  Hi. I'm currently looking for a way to search a big database (500MB - 10GB or more on 10 tables) with a lot of different fields(nvarchars and bigints). Many of the fields, that should be searched are not in the same table. An example: A search for '5124 Peter' should return all items, that ... have an ID with 5124 in it, have 'Peter' in the title or description have item type id with 5124 in it created by a user named 'peter' or a user whose id has 5124 in it created by a user with '5124' or 'peter' in his street address. How should i do the search? I read that the full-text search of MS-Sql is a lot more performant than a query with the LIKE keyword and i think the syntax is more clear, but i think it cant search on bigint(id) values and i read it has performance problems with indexing and therefore slows down inserts to the DB. In my project there will be more inserting than reading, so this could be a matter. Thanks in advance, Marks

  Read the article

• Fastest method for SQL Server inserts, updates, selects from C# ASP.Net 2.0+

  - by Ian

  Hi All, long time listener, first time caller. I use SPs and this isn't an SP vs code-behind "Build your SQL command" question. I'm looking for a high-throughput method for a backend app that handles many small transactions. I use SQLDataReader for most of the returns since forward only works in most cases for me. I've seen it done many ways, and used most of them myself. Methods that define and accept the stored procedure parameters as parameters themselves and build using cmd.Parameters.Add (with or without specifying the DB value type and/or length) Assembling your SP params and their values into an array or hashtable, then passing to a more abstract method that parses the collection and then runs cmd.Parameters.Add Classes that represent tables, initializing the class upon need, setting the public properties that represent the table fields, and calling methods like Save, Load, etc I'm sure there are others I've seen but can't think of at the moment as well. I'm open to all suggestions.

  Read the article

• Sql server indexed view

  - by Jose

  OK, I'm confused about sql server indexed views(using 2008) I've got an indexed view called AssignmentDetail when I look at the execution plan for select * from AssignmentDetail it shows the execution plan of all the underlying indexes of all the other tables that the indexed view is supposed to abstract away. I would think that the execution plan woul simply be an clustered index scan of PK_AssignmentDetail(the name of the clustered index for my view) but it doesn't. There seems to be no performance gain with this indexed view what am I supposed to do? Should I also create a non-clustered index with all of the columns so that it doesn't have to hit all the other indexes? Any insight would be greatly appreciated

  Read the article

• SQL Server Group Concat with Different characters

  - by Molloch

  I have looked through a number of solutions to emulating "Group concat" functionality in SQL Server. I wanted to make a more human readable solution though and I can't work out how to do it. I have a view: ParentID | ChildName Which contains the records, for example: 1 | Max 1 | Jessie 2 | Steven 2 | Lucy 2 | Jake 3 | Mark I want to "Group Concat" these to get: 1 | Max and Jessie 2 | Steven, Lucy and Jake 3 | Mark So If there is only 1 child, just return name, if there are more than one, concat the last 2 with an ' and ' and all others with a ', '. I am a bit stuck on how to do this without resorting to CLR, which I don't want to do. I am happy with a function - but speed is an issue and how do I determine the child number so I can choose between ' and ', ', ' or ''?

  Read the article

< Previous Page | 152 153 154 155 156 157 158 159 160 161 162 163  | Next Page >