Search Results

Search found 105765 results on 4231 pages for 'sql server management stu'.

Page 159/4231 | < Previous Page | 155 156 157 158 159 160 161 162 163 164 165 166  | Next Page >

  • How to create an alias for a named SQL Server instance

    - by Svish
    On my developer computer I have an SQL Server instance named *developer_2005*. In the resource setting files of a C# application we are creating, the instance name is set to foobar (not really, but just as an example). So when I run the application (in debug or realease) it tries to connect to an SQL Server on localhost, named foobar. I am wondering if it is possible to create an alias or something like that, so that the application actually finds an SQL Server on localhost named foobar, but it is actually connecting to the instance named *developer_2005*. The connection string in the config file of the application is Data Source=localhost\foobar;Initial Catalog=barfoo;Integrated Security=True with provider name System.Data.SqlClient. If I change localhost\foobar to *localhost\developer_2005* then the application can connect like it should. How can I create an alias so that I won't have to change the string in the file? I tried, in SQL Server Management Studio, to create a Server Registration with registered server name "localhost\developer", but this didn't seem to do any good. Not even sure what that really did... But then I discovered SQL Server Configuration Manager\SQL Native Client COnfiguration\Aliases. And I kind of assume this is where the solution lies. But I can't quite figure out how to add a new one... When creating a new one, I have to provide Alias Name, Port No, Protocol and Server, and I don't really have a clue what to put in either of them.

    Read the article

  • Scream if you want to go faster

    - by simonsabin
    My session for 24hrs of pass on High Performance functions will be starting at 11:00 GMT thats migdnight for folks in the UK. To attend follow this link https://www.livemeeting.com/cc/8000181573/join?id=N5Q8S7&role=attend&pw=d2%28_KmN3r The rest of the sessions can be found here http://www.sqlpass.org/24hours/2010/Sessions/ChronologicalOrder.aspx So far the sessions have been great so no pressure :( See you there in 4.5 hrs...(read more)

    Read the article

  • Change Windows Authentication user for Sql Server Management Studio

    - by Asmor
    We're using Sql Server 2005 with Windows Authentication setup. So normally, when you log in using e.g. Sql Server Management Studio, it forces you to log in at MACHINE_NAME\Username. Anyways, on this one particular computer, the person said they had to make a new account called User01 to do something and showed me where she'd created it under security in the "master" system database. And so now when she logs in, it's listed as MACHINE_NAME\User01 (not the actual Windows user name). It's still set to Windows Authentication, though, and I'm unable to change the login name. Now here's where the real problem comes in... I didn't realize that she was being logged in under this user name at the time, and I disabled it to see what would happen. Now I can't log into the server under her account. I created a new account in Windows called test, and as expected SSMS had the username as MACHINE_NAME\test, and I was able to log in fine. However, the area where the User01 account was listed is not visible to me as far as I can tell and so I can't reenable it. I also tried running the following query: alter login User01 ENABLE And got this error: Msg 15151, Level 16, State 1, Line 1 Cannot alter the login 'User01', because it does not exist or you do not have permission. So in a nutshell, ideally I'd like to reenable User01 somehow, just to get things back to where they used to be. Failing that, how can I force SSMS to log in using the Windows account name as it should be, rather than trying to use User01?

    Read the article

  • Make huge space savings by using SPARSE columns

    - by simonsabin
    I’ve blogged before about Getting more than 1024 columns on a table , this is done by using sparse columns. Whilst this is potentially useful for people with insane table designs, sparse columns aren’t just for this. My experience over the past few years has shown that sparse columns are useful for almost all databases when you have columns that are largely null i.e. sparse. A recent client was able to reduce the size of the table by 60% by changing columns to sparse. The way this is achieved is...(read more)

    Read the article

  • SQL language drawbacks, The Third Manifesto

    - by David Portabella
    Sometime ago I read about SQL language drawbacks (the basic language specification, not vendor specific), and one of the drawbacks was that the language does not allow to create a set of tuples that don't come from a table. For instance, SELECT firstName, lastName from people; this creates a set of tuples coming from the table people. Now, if I don't have this table people, and I want to return a constant, I'd need something like this to return a set of two tuples (this would not require to have a table): SELECT VALUES('james', 'dean'), ('tom', 'cruisse'); Why I would need that? Because of the same reasons that we can define constants (not only basic types, but objects and arrays also) in any advanced programming language. Workarounds, Yes, I could create a temporal table, fill the data, and SELECT from that table. This is a hack, to overcome the drawbacks of the poor SQL language. I think that I read about this somewhere in "The Third Manifesto", but I don't find the paragraph/example talking about this concrete drawback anymore. Do you know a reference about it?

    Read the article

  • Wait Statistics in Microsoft SQL Server

    - by KKline
    When it comes to troubleshooting in relational databases, there's no better place to start than wait statistics. In a nutshell, a wait statistic is an internal counter that tells you how long the database spent waiting for a particular resource, activity, or process. Since wait statistics are categorized by type, one look will quickly tell the variety of problem that needs your attention, assuming you know meaning for Microsoft's lingo for each wait type....(read more)

    Read the article

  • Why can't the IT industry deliver large, faultless projects quickly as in other industries?

    - by MainMa
    After watching National Geographic's MegaStructures series, I was surprised how fast large projects are completed. Once the preliminary work (design, specifications, etc.) is done on paper, the realization itself of huge projects take just a few years or sometimes a few months. For example, Airbus A380 "formally launched on Dec. 19, 2000", and "in the Early March, 2005", the aircraft was already tested. The same goes for huge oil tankers, skyscrapers, etc. Comparing this to the delays in software industry, I can't help wondering why most IT projects are so slow, or more precisely, why they cannot be as fast and faultless, at the same scale, given enough people? Projects such as the Airbus A380 present both: Major unforeseen risks: while this is not the first aircraft built, it still pushes the limits if the technology and things which worked well for smaller airliners may not work for the larger one due to physical constraints; in the same way, new technologies are used which were not used yet, because for example they were not available in 1969 when Boeing 747 was done. Risks related to human resources and management in general: people quitting in the middle of the project, inability to reach a person because she's on vacation, ordinary human errors, etc. With those risks, people still achieve projects like those large airliners in a very short period of time, and despite the delivery delays, those projects are still hugely successful and of a high quality. When it comes to software development, the projects are hardly as large and complicated as an airliner (both technically and in terms of management), and have slightly less unforeseen risks from the real world. Still, most IT projects are slow and late, and adding more developers to the project is not a solution (going from a team of ten developer to two thousand will sometimes allow to deliver the project faster, sometimes not, and sometimes will only harm the project and increase the risk of not finishing it at all). Those which are still delivered may often contain a lot of bugs, requiring consecutive service packs and regular updates (imagine "installing updates" on every Airbus A380 twice per week to patch the bugs in the original product and prevent the aircraft from crashing). How can such differences be explained? Is it due exclusively to the fact that software development industry is too young to be able to manage thousands of people on a single project in order to deliver large scale, nearly faultless products very fast?

    Read the article

  • June 2013 release of SSDT contains a minor bug that you should be aware of

    - by jamiet
    I have discovered what seems, to me, like a bug in the June 2013 release of SSDT and given the problems that it created yesterday on my current gig I thought it prudent to write this blog post to inform people of it. I’ve built a very simple SSDT project to reproduce the problem that has just two tables, [Table1] and [Table2], and also a procedure [Procedure1]: The two tables have exactly the same definition, both a have a single column called [Id] of type integer. CREATE TABLE [dbo].[Table1] (     [Id] INT NOT NULL PRIMARY KEY ) My stored procedure simply joins the two together, orders them by the column used in the join predicate, and returns the results: CREATE PROCEDURE [dbo].[Procedure1] AS     SELECT t1.*     FROM    Table1 t1     INNER JOIN Table2 t2         ON    t1.Id = t2.Id     ORDER BY Id Now if I create those three objects manually and then execute the stored procedure, it works fine: So we know that the code works. Unfortunately, SSDT thinks that there is an error here: The text of that error is: Procedure: [dbo].[Procedure1] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[Table1].[Id] or [dbo].[Table2].[Id]. Its complaining that the [Id] field in the ORDER BY clause is ambiguous. Now you may well be thinking at this point “OK, just stick a table alias into the ORDER BY predicate and everything will be fine!” Well that’s true, but there’s a bigger problem here. One of the developers at my current client installed this drop of SSDT and all of a sudden all the builds started failing on his machine – he had errors left right and centre because, as it transpires, we have a fair bit of code that exhibits this scenario.  Worse, previous installations of SSDT do not flag this code as erroneous and therein lies the rub. We immediately had a mass panic where we had to run around the department to our developers (of which there are many) ensuring that none of them should upgrade their SSDT installation if they wanted to carry on being productive for the rest of the day. Also bear in mind that as soon as a new drop of SSDT comes out then the previous version is instantly unavailable so rolling back is going to be impossible unless you have created an administrative install of SSDT for that previous version. Just thought you should know! In the grand schema of things this isn’t a big deal as the bug can be worked around with a simple code modification but forewarned is forearmed so they say! Last thing to say, if you want to know which version of SSDT you are running check my blog post Which version of SSDT Database Projects do I have installed? @Jamiet

    Read the article

  • Benefits of In-house server management vs outsourcing [closed]

    - by Eric Di Bari
    I've just created a small web-based company. We're planning on using a cloud hosting solution, but don't have the current resources to properly setup and manage the server. As a new company, are there more benefits to bringing someone on-board as part of the organization to manage the server, versus going with a third-party management company? Such as a greater degree of 'ownership' and involvement?

    Read the article

  • Can’t connect to SQL Server 2008 - looks like Shared Memory problem

    - by Proposition Joe
    I am unable to connect to my local instance of SQL Server 2008 Express using SQL Server Management Studio. I believe the problem is related to a change I made to the connection protocols. Before the error occurred, I had Shared Memory enabled and Named Pipes and TCP/IP disabled. I then enabled both Named Pipes and TCP/IP, and this is when I started experiencing the problem. When I try to connect to the server with SSMS (with either my SQL server sysadmin login or with windows authentication), I get the following error message: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233) Why is it returning a Named Pipes error? Why would it not just use Shared Memory, as this has a higher priority order in the list of connection protocols? It seems like it is not listening on Shared Memory for some reason? When I set Named Pipes to enabled and try to connect, I get the same error message. My windows account is does not have administrator priviliges on my computer - perhaps this is making a difference in some way (as some of the discussions in this post about an "SuperSocketNetLib\Lpc" registry key seems to suggest). I have tried restarting the SQL Server service, by the way, and also tried to get someone to log onto the machine with an admin account to restart the SQL Server service. Still no luck.

    Read the article

  • How can I backup a remote dedicated Server 2008 to my own computer at home?

    - by Nishant
    My server 2008 has mysql databases, SQL Server databases, and multiple websites. How do I back this information up? I have setup the dedicated server to have two 300GB hard drives and they are both mirrored. I want to make sure that I have a backup somewhere else as well. If it's not my home server, I don't mind backing up somewhere else either but I want to make sure I can restore it completely in case of a failure.

    Read the article

  • Best method to implement a filtered search

    - by j0N45
    I would like to ask you, your opinion when it comes to implement a filtered search form. Let's imagine the following case: 1 Big table with lots of columns It might be important to say that this SQL Server You need to implement a form to search data in this table, and in this form you'll have several check boxes that allow you to costumize this search. Now my question here is which one of the following should be the best way to implement the search? Create a stored procedure with a query inside. This stored procedure will check if the parameters are given by the application and in the case they are not given a wildcard will be putted in the query. Create a dynamic query, that is built accordingly to what is given by the application. I am asking this because I know that SQL Server creates an execution plan when the stored procedure is created, in order to optimize its performance, however by creating a dynamic query inside of the stored procedure will we sacrifice the optimization gained by the execution plan? Please tell me what would be the best approach in your oppinion.

    Read the article

  • SQL Server 2012 memory usage steadily growing

    - by pgmo
    I am very worried about the SQL Server 2012 Express instance on which my database is running: the SQL Server process memory usage is growing steadily (1.5GB after only 2 days working). The database is made of seven tables, each having a bigint primary key (Identity) and at least one non-unique index with some included columns to serve the majority of incoming queries. An external application is calling via Microsoft OLE DB some stored procedures, each of which do some calculations using intermediate temporary tables and/or table variables and finally do an upsert (UPDATE....IF @@ROWCOUNT=0 INSERT.....) - I never DROP those temporary tables explicitly: the frequency of those calls is about 100 calls every 5 seconds (I saw that the DLL used by the external application open a connection to SQL Server, do the call and then close the connection for each and every call). The database files are organized in only one filgegroup, recovery type is set to simple. Some questions to diagnose the problem: is that steadily growing memory normal? did I do any mistake in database design which probably lead to this behaviour? (no explicit temp-table drop, filegroup organization, etc) can SQL Server manage such a stored procedure call rate (100 calls every 5 seconds, i.e. 100 upsert every 5 seconds, beyond intermediate calculations)? do the continuous "open connection/do sp call/close connection" pattern disturb SQL Server? is it possible to diagnose what is causing such a memory usage? Perhaps queues of wating requests? (I ran sp_who2, but I didn't see a big amount of orphan connections from the external application) if I restrict the amount of memory which SQL Server is allowed to use, may I sooner or later get into trouble?

    Read the article

  • Can’t connect to SQL Server 2008 - looks like Shared Memory problem

    - by user38556
    I am unable to connect to my local instance of SQL Server 2008 Express using SQL Server Management Studio. I believe the problem is related to a change I made to the connection protocols. Before the error occurred, I had Shared Memory enabled and Named Pipes and TCP/IP disabled. I then enabled both Named Pipes and TCP/IP, and this is when I started experiencing the problem. When I try to connect to the server with SSMS (with either my SQL server sysadmin login or with windows authentication), I get the following error message: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233) Why is it returning a Named Pipes error? Why would it not just use Shared Memory, as this has a higher priority order in the list of connection protocols? It seems like it is not listening on Shared Memory for some reason? When I set Named Pipes to enabled and try to connect, I get the same error message. My windows account is does not have administrator priviliges on my computer - perhaps this is making a difference in some way (as some of the discussions in this post about an "SuperSocketNetLib\Lpc" registry key seems to suggest). I have tried restarting the SQL Server service, by the way, and also tried to get someone to log onto the machine with an admin account to restart the SQL Server service. Still no luck.

    Read the article

  • Two way replication

    - by Nidzaaaa
    I have a little problem... I have this case: -2 server instances -2 Databases -1 Table (5 columns) From server 1 I created publication to replicate all columns of table I have in 1. DB From server 2 I created subscription to pull all columns from table which is in server 1 DB But now, I need to publicate one columns of same table from server 2 to server 1 and also it has to be in same DB... I tried with using logic and creating publication for server 2 and subscription on server 1 but there is error appearing "You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database." I hope someone understood my problem and have an answer for me, thanks in advance... p.s. Ask for more info if you need ...

    Read the article

  • SQL*Plus??? - SPOOL??????(????? ???Tips-5)

    - by Yuichi.Hayashi
    SQL??????????????????????????????????????SPOOL?????????????? ???SPOOL??????????????????????????????? ????????? ????????????????????????????????????????????????????????????????????    $ sqlplus @sample.sql > /dev/null ??????????????????????????????? ??????????SPOOL????????????????? arraysize???????? arraysize???????SELECT??????fetch?????????SQL*Plus????????? ????????SPOOL????????SELECT???????????? SPOOL????     SQL> set array[size] 100 ????????fetch?100?????????????15???? ???????????????????????????????????????????????????? ????????????????????????????????????????????????????????????? ?????????????????????????????????????????????? ?????????? SPOOL?????????????1?????linesize???????????????????????????????? ????????????????????????????????????????? ????????????SPOOL????    SQL> set trims[pool] on ????????????off???????????????? ?????????? ???????????????SQL*Plus????????????1????????????????????????????????????????????????????????? ??????????????????????   (Written by Hiroyuki Nakaie) ?????SQL*Plus TIPS???????????????? Oracle SQL*Plus - ??, ??, ?????

    Read the article

  • ???????/????????????????? SQL????????? ??? Part1&2

    - by Yusuke.Yamamoto
    ????? ??:2010/10/12 ??:??????/?? ????????????????????????????????????????????SQL????????????????????????????????????????????????????SQL??????????????????????????????????????????????????SQL???????????????????????????????????????????????????!! ??????SQL???????????????/ SQL?????????????SQL????????????????SQL???????????????????:????SQL??????/ SQL?????????SQL???????????????????:?????SQL??????/ ??????·???????????????????????(???????????????·??)??? ????????? ????????????????? http://otndnld.oracle.co.jp/ondemand/otn-seminar/movie/OCSTuning12_10121330.wmv http://www.oracle.com/technology/global/jp/ondemand/otn-seminar/pdf/SQL.pdf

    Read the article

  • Handling inheritance with overriding efficiently

    - by Fyodor Soikin
    I have the following two data structures. First, a list of properties applied to object triples: Object1 Object2 Object3 Property Value O1 O2 O3 P1 "abc" O1 O2 O3 P2 "xyz" O1 O3 O4 P1 "123" O2 O4 O5 P1 "098" Second, an inheritance tree: O1 O2 O4 O3 O5 Or viewed as a relation: Object Parent O2 O1 O4 O2 O3 O1 O5 O3 O1 null The semantics of this being that O2 inherits properties from O1; O4 - from O2 and O1; O3 - from O1; and O5 - from O3 and O1, in that order of precedence. NOTE 1: I have an efficient way to select all children or all parents of a given object. This is currently implemented with left and right indexes, but hierarchyid could also work. This does not seem important right now. NOTE 2: I have tiggers in place that make sure that the "Object" column always contains all possible objects, even when they do not really have to be there (i.e. have no parent or children defined). This makes it possible to use inner joins rather than severely less effiecient outer joins. The objective is: Given a pair of (Property, Value), return all object triples that have that property with that value either defined explicitly or inherited from a parent. NOTE 1: An object triple (X,Y,Z) is considered a "parent" of triple (A,B,C) when it is true that either X = A or X is a parent of A, and the same is true for (Y,B) and (Z,C). NOTE 2: A property defined on a closer parent "overrides" the same property defined on a more distant parent. NOTE 3: When (A,B,C) has two parents - (X1,Y1,Z1) and (X2,Y2,Z2), then (X1,Y1,Z1) is considered a "closer" parent when: (a) X2 is a parent of X1, or (b) X2 = X1 and Y2 is a parent of Y1, or (c) X2 = X1 and Y2 = Y1 and Z2 is a parent of Z1 In other words, the "closeness" in ancestry for triples is defined based on the first components of the triples first, then on the second components, then on the third components. This rule establishes an unambigous partial order for triples in terms of ancestry. For example, given the pair of (P1, "abc"), the result set of triples will be: O1, O2, O3 -- Defined explicitly O1, O2, O5 -- Because O5 inherits from O3 O1, O4, O3 -- Because O4 inherits from O2 O1, O4, O5 -- Because O4 inherits from O2 and O5 inherits from O3 O2, O2, O3 -- Because O2 inherits from O1 O2, O2, O5 -- Because O2 inherits from O1 and O5 inherits from O3 O2, O4, O3 -- Because O2 inherits from O1 and O4 inherits from O2 O3, O2, O3 -- Because O3 inherits from O1 O3, O2, O5 -- Because O3 inherits from O1 and O5 inherits from O3 O3, O4, O3 -- Because O3 inherits from O1 and O4 inherits from O2 O3, O4, O5 -- Because O3 inherits from O1 and O4 inherits from O2 and O5 inherits from O3 O4, O2, O3 -- Because O4 inherits from O1 O4, O2, O5 -- Because O4 inherits from O1 and O5 inherits from O3 O4, O4, O3 -- Because O4 inherits from O1 and O4 inherits from O2 O5, O2, O3 -- Because O5 inherits from O1 O5, O2, O5 -- Because O5 inherits from O1 and O5 inherits from O3 O5, O4, O3 -- Because O5 inherits from O1 and O4 inherits from O2 O5, O4, O5 -- Because O5 inherits from O1 and O4 inherits from O2 and O5 inherits from O3 Note that the triple (O2, O4, O5) is absent from this list. This is because property P1 is defined explicitly for the triple (O2, O4, O5) and this prevents that triple from inheriting that property from (O1, O2, O3). Also note that the triple (O4, O4, O5) is also absent. This is because that triple inherits its value of P1="098" from (O2, O4, O5), because it is a closer parent than (O1, O2, O3). The straightforward way to do it is the following. First, for every triple that a property is defined on, select all possible child triples: select Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value from TriplesAndProperties tp -- Select corresponding objects of the triple inner join Objects as Objects1 on Objects1.Id = tp.O1 inner join Objects as Objects2 on Objects2.Id = tp.O2 inner join Objects as Objects3 on Objects3.Id = tp.O3 -- Then add all possible children of all those objects inner join Objects as Children1 on Objects1.Id [isparentof] Children1.Id inner join Objects as Children2 on Objects2.Id [isparentof] Children2.Id inner join Objects as Children3 on Objects3.Id [isparentof] Children3.Id But this is not the whole story: if some triple inherits the same property from several parents, this query will yield conflicting results. Therefore, second step is to select just one of those conflicting results: select * from ( select Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value, row_number() over( partition by Children1.Id, Children2.Id, Children3.Id, tp.Property order by Objects1.[depthInTheTree] descending, Objects2.[depthInTheTree] descending, Objects3.[depthInTheTree] descending ) as InheritancePriority from ... (see above) ) where InheritancePriority = 1 The window function row_number() over( ... ) does the following: for every unique combination of objects triple and property, it sorts all values by the ancestral distance from the triple to the parents that the value is inherited from, and then I only select the very first of the resulting list of values. A similar effect can be achieved with a GROUP BY and ORDER BY statements, but I just find the window function semantically cleaner (the execution plans they yield are identical). The point is, I need to select the closest of contributing ancestors, and for that I need to group and then sort within the group. And finally, now I can simply filter the result set by Property and Value. This scheme works. Very reliably and predictably. It has proven to be very powerful for the business task it implements. The only trouble is, it is awfuly slow. One might point out the join of seven tables might be slowing things down, but that is actually not the bottleneck. According to the actual execution plan I'm getting from the SQL Management Studio (as well as SQL Profiler), the bottleneck is the sorting. The problem is, in order to satisfy my window function, the server has to sort by Children1.Id, Children2.Id, Children3.Id, tp.Property, Parents1.[depthInTheTree] descending, Parents2.[depthInTheTree] descending, Parents3.[depthInTheTree] descending, and there can be no indexes it can use, because the values come from a cross join of several tables. EDIT: Per Michael Buen's suggestion (thank you, Michael), I have posted the whole puzzle to sqlfiddle here. One can see in the execution plan that the Sort operation accounts for 32% of the whole query, and that is going to grow with the number of total rows, because all the other operations use indexes. Usually in such cases I would use an indexed view, but not in this case, because indexed views cannot contain self-joins, of which there are six. The only way that I can think of so far is to create six copies of the Objects table and then use them for the joins, thus enabling an indexed view. Did the time come that I shall be reduced to that kind of hacks? The despair sets in.

    Read the article

  • T-SQL: Compute Subtotals For A Range Of Rows

    - by John Dibling
    MSSQL 2008. I am trying to construct a SQL statement which returns the total of column B for all rows where column A is between 2 known ranges. The range is a sliding window, and should be recomputed as it might be using a loop. Here is an example of what I'm trying to do, much simplified from my actual problem. Suppose I have this data: table: Test Year Sales ----------- ----------- 2000 200 2001 200 2002 200 2003 200 2004 200 2005 200 2006 200 2007 200 2008 200 2009 200 2010 200 2011 200 2012 200 2013 200 2014 200 2015 200 2016 200 2017 200 2018 200 2019 200 I want to construct a query which returns 1 row for every decade in the above table, like this: Desired Results: DecadeEnd TotalSales --------- ---------- 2009 2000 2010 2000 Where the first row is all the sales for the years 2000-2009, the second for years 2010-2019. The DecadeEnd is a sliding window that moves forward by a set ammount for each row in the result set. To illustrate, here is one way I can accomplish this using a loop: declare @startYear int set @startYear = (select top(1) [Year] from Test order by [Year] asc) declare @endYear int set @endYear = (select top(1) [Year] from Test order by [Year] desc) select @startYear, @endYear create table DecadeSummary (DecadeEnd int, TtlSales int) declare @i int -- first decade ends 9 years after the first data point set @i = (@startYear + 9) while @i <= @endYear begin declare @ttlSalesThisDecade int set @ttlSalesThisDecade = (select SUM(Sales) from Test where(Year <= @i and Year >= (@i-9))) insert into DecadeSummary values(@i, @ttlSalesThisDecade) set @i = (@i + 9) end select * from DecadeSummary This returns the data I want: DecadeEnd TtlSales ----------- ----------- 2009 2000 2018 2000 But it is very inefficient. How can I construct such a query?

    Read the article

  • How to analyze 'dbcc memorystatus' result in SQL Server 2008

    - by envykok
    Currently i am facing a sql memory pressure issue. i have run 'dbcc memorystatus', here is part of my result: Memory Manager KB VM Reserved 23617160 VM Committed 14818444 Locked Pages Allocated 0 Reserved Memory 1024 Reserved Memory In Use 0 Memory node Id = 0 KB VM Reserved 23613512 VM Committed 14814908 Locked Pages Allocated 0 MultiPage Allocator 387400 SinglePage Allocator 3265000 MEMORYCLERK_SQLBUFFERPOOL (node 0) KB VM Reserved 16809984 VM Committed 14184208 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 408 MEMORYCLERK_SQLCLR (node 0) KB VM Reserved 6311612 VM Committed 141616 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 1456 MultiPage Allocator 20144 CACHESTORE_SQLCP (node 0) KB VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 3101784 MultiPage Allocator 300328 Buffer Pool Value Committed 1742946 Target 1742946 Database 1333883 Dirty 940 In IO 1 Latched 18 Free 89 Stolen 408974 Reserved 2080 Visible 1742946 Stolen Potential 1579938 Limiting Factor 13 Last OOM Factor 0 Page Life Expectancy 5463 Process/System Counts Value Available Physical Memory 258572288 Available Virtual Memory 8771398631424 Available Paging File 16030617600 Working Set 15225597952 Percent of Committed Memory in WS 100 Page Faults 305556823 System physical memory high 1 System physical memory low 0 Process physical memory low 0 Process virtual memory low 0 Procedure Cache Value TotalProcs 11382 TotalPages 430160 InUsePages 28 Can you lead me to analyze this result ? Is it a lot execute plan have been cached causing the memory issue or other reasons?

    Read the article

  • SQLXML with Windows 2008 and SQL Server 2008

    - by Rafa G. Argente
    Hi all, I have an application that uses SQLXML to access data on the database. We have it working on a Windows 2003 server and SQL Server 2005. Now the client wants to install it on Windows 2008 and SQL Server 2008 and we are getting errors like: Microsoft.Data.SqlXml.SqlXmlException: Class not registered --- System.Runtime.InteropServices.COMException (0x80040154): Class not registered at Microsoft.Data.SqlXml.Common.UnsafeNativeMethods. ISQLXMLCommandManagedInterface.ExecuteToOutputStream() at Microsoft.Data.SqlXml.SqlXmlCommand.innerExecute(Stream strm) ... etc etc This is driving me crazy. SQLXML is quite an obsolete technology, and we are trying to use it with the latest SO. I can't find official information about SQLXML and Windows 2008, it seems it's not officially supported but they don't say it's not supported either. The SQLXML4.0SP1 installation seems to work fine, but it seems like it fails on runtime. Do you have any ideas? Has someone tried anything like this?

    Read the article

  • Generating the query plan takes 5 minutes, the query itself runs in milliseconds. What's up?

    - by TheImirOfGroofunkistan
    I have a fairly complex (or ugly depending on how you look at it) stored procedure running on SQL Server 2008. It bases a lot of the logic on a view that has a pk table and a fk table. The fk table is left joined to the pk table slightly more than 30 times (the fk table has a poor design - it uses name value pairs that I need to flatten out. Unfortunately, it's 3rd party and I cannot change it). Anyway, it had been running fine for weeks until I periodically noticed a run that would take 3-5 minutes. It turns out that this is the time it takes to generate the query plan. Once the query plan exists and is cached, the stored procedure itself runs very efficiently. Things run smoothly until there is a reason to regenerate and cache the query plan again. Has anyone seen this? Why does it take so long to generate the plan? Are there ways to make it come up with a plan faster?

    Read the article

  • how to rethrow same exception in sql server

    - by Shantanu Gupta
    I want to rethrow same exception in sql server that has been occured in my try block. I am able to throw same message but i want to throw same error. BEGIN TRANSACTION BEGIN TRY INSERT INTO Tags.tblDomain (DomainName, SubDomainId, DomainCode, Description) VALUES(@DomainName, @SubDomainId, @DomainCode, @Description) COMMIT TRANSACTION END TRY BEGIN CATCH declare @severity int; declare @state int; select @severity=error_severity(), @state=error_state(); RAISERROR(@@Error,@ErrorSeverity,@state); ROLLBACK TRANSACTION END CATCH RAISERROR(@@Error, @ErrorSeverity, @state); This line will show error, but i want functionality something like that. This raises error with error number 50000, but i want erron number to be thrown that i am passing @@error, I want to capture this error no at frontend i.e. catch (SqlException ex) { if ex.number==2627 MessageBox.show("Duplicate value cannot be inserted"); } I want this functionality. which can't be achieved using raiseerror. I dont want to give custom error message at back end.

    Read the article

  • Finding duplicate rows in SQL Server

    - by xtine
    I have a SQL Server database of organizations, and there are many duplicate rows. I want to run a select statement to grab all of these and the amount of dupes, but also return the ids that are associated with each organization. A statement like: SELECT orgName, COUNT(*) AS dupes FROM organizations GROUP BY orgName HAVING (COUNT(*) > 1) Will return something like orgName | dupes ABC Corp | 7 Foo Federation | 5 Widget Company | 2 But I'd also like to grab the IDs of them. Is there any way to do this? Maybe like a orgName | dupeCount | id ABC Corp | 1 | 34 ABC Corp | 2 | 5 ... Widget Company | 1 | 10 Widget Company | 2 | 2 The reason being that there is also a separate table of users that link to these organizations, and I would like to unify them (therefore remove dupes so the users link to the same organization instead of dupe orgs). But I would like part manually so I don't screw anything up, but I would still need a statement returning the IDs of all the dupe orgs so I can go through the list of users. Any help would be greatly appreciated. Thanks :)

    Read the article

< Previous Page | 155 156 157 158 159 160 161 162 163 164 165 166  | Next Page >