Search Results

Search found 62701 results on 2509 pages for 'sql function'.

Page 54/2509 | < Previous Page | 50 51 52 53 54 55 56 57 58 59 60 61  | Next Page >

• SQL Server PIVOT with multiple X-axis columns

  - by HeavenCore

  Take the following example data: Payroll Forname Surname Month Year Amount 0000001 James Bond 3 2011 144.00 0000001 James Bond 6 2012 672.00 0000001 James Bond 7 2012 240.00 0000001 James Bond 8 2012 1744.50 0000002 Elvis Presley 3 2011 1491.00 0000002 Elvis Presley 6 2012 189.00 0000002 Elvis Presley 7 2012 1816.50 0000002 Elvis Presley 8 2012 1383.00 How would i PIVOT this on the Year + Month (eg: 201210) but preserve Payroll, Forename & Surname as seperate columns, for example, the above would become: Payroll Forename Surname 201103 201206 201207 201208 0000001 James Bond 144.00 672.00 240.00 1744.50 0000002 Elvis Presley 1491.00 189.00 1816.50 1383.00 I'm assuming that because the Year + Month names can change then i will need to employ dynamic SQL + PIVOT - i had a go but couldnt even get the code to parse, nevermind run - any help would be most appreciated! Edit: What i have so far: INSERT INTO #tbl_RawDateBuffer ( PayrollNumber , Surname , Forename , [Month] , [Year] , AmountPayable ) SELECT PayrollNumber , Surname , Forename , [Month] , [Year] , AmountPayable FROM RawData WHERE [Max] > 1500 DECLARE @Columns AS NVARCHAR(MAX) DECLARE @StrSQL AS NVARCHAR(MAX) SET @Columns = STUFF((SELECT DISTINCT ',' + QUOTENAME(CONVERT(VARCHAR(4), c.[Year]) + RIGHT('00' + CONVERT(VARCHAR(2), c.[Month]), 2)) FROM #tbl_RawDateBuffer c FOR XML PATH('') , TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') SET @StrSQL = 'SELECT PayrollNumber, ' + @Columns + ' from ( select PayrollNumber , CONVERT(VARCHAR(4), [Year]) + RIGHT(''00'' + CONVERT(VARCHAR(2), [Month]), 2) dt from #tbl_RawDateBuffer ) x pivot ( sum(AmountPayable) for dt in (' + @Columns + ') ) p ' EXECUTE(@StrSQL) DROP TABLE #tbl_RawDateBuffer

  Read the article

• Call a void* as a function without declaring a function pointer

  - by ToxIk

  I've searched but couldn't find any results (my terminology may be off) so forgive me if this has been asked before. I was wondering if there is an easy way to call a void* as a function in C without first declaring a function pointer and then assigning the function pointer the address; ie. assuming the function to be called is type void(void) void *ptr; ptr = <some address>; ((void*())ptr)(); /* call ptr as function here */ with the above code, I get error C2066: cast to function type is illegal in VC2008 If this is possible, how would the syntax differ for functions with return types and multiple parameters?

  Read the article

• SQL Server - Multi-Column substring matching

  - by hamlin11

  One of my clients is hooked on multi-column substring matching. I understand that Contains and FreeText search for words (and at least in the case of Contains, word prefixes). However, based upon my understanding of this MSDN book, neither of these nor their variants are capable of searching substrings. I have used LIKE rather extensively (Select * from A where A.B Like '%substr%') Sample table A: ID | Col1 | Col2 | Col3 | ------------------------------------- 1 | oklahoma | colorado | Utah | 2 | arkansas | colorado | oklahoma | 3 | florida | michigan | florida | ------------------------------------- The following code will give us row 1 and row 2: select * from A where Col1 like '%klah%' or Col2 like '%klah%' or Col3 like '%klah%' This is rather ugly, probably slow, and I just don't like it very much. Probably because the implementations that I'm dealing with have 10+ columns that need searched. The following may be a slight improvement as code readability goes, but as far as performance, we're still in the same ball park. select * from A where (Col1 + ' ' + Col2 + ' ' + Col3) like '%klah%' I have thought about simply adding insert, update, and delete triggers that simply add the concatenated version of the above columns into a separate table that shadows this table. Sample Shadow_Table: ID | searchtext | --------------------------------- 1 | oklahoma colorado Utah | 2 | arkansas colorado oklahoma | 3 | florida michigan florida | --------------------------------- This would allow us to perform the following query to search for '%klah%' select * from Shadow_Table where searchtext like '%klah%' I really don't like having to remember that this shadow table exists and that I'm supposed to use it when I am performing multi-column substring matching, but it probably yields pretty quick reads at the expense of write and storage space. My gut feeling tells me there there is an existing solution built into SQL Server 2008. However, I don't seem to be able to find anything other than research papers on the subject. Any help would be appreciated.

  Read the article

• SQL View with Data from two tables

  - by Alex

  Hello! I can't seem to crack this - I have two tables (Persons and Companies), and I'm trying to create a view that: 1) shows all persons 2) also returns companies by themselves once, regardless of how many persons are related to it 3) orders by name across both tables To clarify, some sample data: (Table: Companies) Id Name 1 Banana 2 ABC Inc. 3 Microsoft 4 Bigwig (Table: Persons) Id Name RelatedCompanyId 1 Joe Smith 3 2 Justin 3 Paul Rudd 4 4 Anjolie 5 Dustin 4 The output I'm looking for is something like this: Name PersonName CompanyName RelatedCompanyId ABC Inc. NULL ABC Inc. NULL Anjolie Anjolie NULL NULL Banana NULL Banana NULL Bigwig NULL Bigwig NULL Dustin Dustin Bigwig 4 Joe Smith Joe Smith Microsoft 3 Justin Justin NULL NULL Microsoft NULL Microsoft NULL Paul Rudd Paul Rudd Bigwig 4 As you can see, the new "Name" column is ordered across both tables (the company names appear correctly in between the person names), and each company appears exactly once, regardless of how many people are related to it. Can this even be done in SQL?! P.S. I'm trying to create a view so I can use this later for easy data retrieval, fulltext indexing and make the programming side simpler by just querying the view.

  Read the article

• sql server : get default value of a column

  - by luke

  Hello, I execute a select to get the structure of a table. I want to get info about the columns like its name or if it's null or if it's primary key.. I do something like this ....sys.columns c... c.precision, c.scale, c.is_nullable as isnullable, c.default_object_id as columndefault, c.is_computed as iscomputed, but for default value i get the id..something like 454545454 but i want to get the value "xxxx". What is the table to search or what is the function to convert that id to the value. Thanks

  Read the article

• Problem with duplicates in a SQL Join

  - by Chris Ballance

  I have the following result set from a join of three tables, an articles table, a products table, an articles to products mapping table. I would like to have the results with duplicates removed similar to a select distinct on content id. Current result set: [ContendId] [Title] [productId] 1 article one 2 1 article one 3 1 article one 9 4 article four 1 4 article four 10 4 article four 14 5 article five 1 6 article six 8 6 article six 10 6 article six 11 6 article six 13 7 article seven 14 Desired result set: [ContendId] [Title] [productId] 1 article one * 4 article four * 5 article five * 6 article six * 7 article seven * Here is condensed example of the relevant SQL: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tempdb.dbo.products') AND type = (N'U')) drop table tempdb.dbo.products go CREATE TABLE tempdb.dbo.products ( productid int, productname varchar(255) ) go IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'articles') AND type = (N'U')) drop table tempdb.dbo.articles go create table tempdb.dbo.articles ( contentid int, title varchar(255) ) IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'articles') AND type = (N'U')) drop table tempdb.dbo.articles go create table tempdb.dbo.articles ( contentid int, title varchar(255) ) IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'articleproducts') AND type = (N'U')) drop table tempdb.dbo.articleproducts go create table tempdb.dbo.articleproducts ( contentid int, productid int ) insert into tempdb.dbo.products values (1,'product one'), (2,'product two'), (3,'product three'), (4,'product four'), (5,'product five'), (6,'product six'), (7,'product seven'), (8,'product eigth'), (9,'product nine'), (10,'product ten'), (11,'product eleven'), (12,'product twelve'), (13,'product thirteen'), (14,'product fourteen') insert into tempdb.dbo.articles VALUES (1,'article one'), (2, 'article two'), (3, 'article three'), (4, 'article four'), (5, 'article five'), (6, 'article six'), (7, 'article seven'), (8, 'article eight'), (9, 'article nine'), (10, 'article ten') INSERT INTO tempdb.dbo.articleproducts VALUES (1,2), (1,3), (1,9), (4,1), (4,10), (4,14), (5,1), (6,8), (6,10), (6,11), (6,13), (7,14) GO select DISTINCT(a.contentid), a.title, p.productid from articles a JOIN articleproducts ap ON a.contentid = ap.contentid JOIN products p ON a.contentid = ap.contentid AND p.productid = ap.productid ORDER BY a.contentid

  Read the article

• SQLce DAL Linq to Sql or EntityFramework

  - by bretddog

  Hi, I'm learning databases, using SqlCe, and need business object to database mapping. Currently I try to decide if to use Linq to Sql, or EntityFramework. (I understand a bit L2S, but haven't familiarized with EF yet) The program will only be debeloped and used by myself, so I have good control of the priorities: I don't need to consider potential change of database type or data storage type, as I'm quite certain SQLce will stay sufficient. I DO expect continued development and changes to the data scheme while the program is in active use; change business object properties (Hence database columns), and possibly overall table scheme. So old data must be transported to new scheme. I also want to keep a decent degree of layer separation DAL/BLL, although this may not be necessary, it is good for me to learn these principles. My question is: With these priorities, would I have any benefit by choosing either Linq2Sql vs. EntityFramwork? (and please explain why) Btw, the project involves very simple table scheme with only 4-5 tables and very simple relations. Thanks!

  Read the article

• ASP.NET (VB) - Close an opened SQL connection inside Function

  - by B1GB0Y

  Can anyone tell me how I close an opened SQL connection inside a Function? I call a Select Function like this: Function Selec(ByVal SQLStr As String) As SqlDataReader Dim SQLConn As New SqlConnection() Dim SQLCmd As New SqlCommand() SQLConn.ConnectionString = Session("bd") SQLConn.Open() SQLCmd.Connection = SQLConn SQLCmd.CommandText = SQLStr Selec = SQLCmd.ExecuteReader End Function And in another page I do a While method to retrieve me the data like this: (Note: BDcon.BD is the name of the Class that have Functions) Dim write as New BDcon.BD Dim menu As SqlDataReader = writeBD.Selec("SELECT something from Table") While menu.Read 'Do something End While menu.Close 'This close just the DataReader and not the SqlConnection Finally I want to Close my SQL Connection by Function like this: Function Close() As SqlConnection Dim SQLConn As New SqlConnection() SQLConn.ConnectionString = Session("bd") SQLConn.Close() End Function I think that the problem is on the Close() Function, I want to close the connection but I don't know how to call my Opened Conneciton. Please anyone can help me? Thanks in advance :)

  Read the article

• JS function returning another function

  - by Michael

  I want to understand about variables, that has been used in returning function. This is example code Prototype = {} Prototype.F = { bind: function() { var args = arguments, __method = args.shift(), object = args.shift(); return function() { return __method.apply(object, args.concat(arguments)); } } } function ObjectA() { ... this.addListener = Prototype.F.bind(this.eventSource.addListener, this.eventSource); ... } var a = ObjectA(); a.addListener(this); // assuming 'this' here will point to some window object As I understand the returning function in F() is not evaluated until it's called in the last line. It's ok to accept. So addListener will hold a function body containing 'apply'. But what I don't understand, when addListener is called, what kind of parameters it is going to have? particularly _method and args will always be uninitialized?

  Read the article

• SQL Server INSERT ... SELECT Statement won't parse

  - by Jim Barnett

  I am getting the following error message with SQL Server 2005 Msg 120, Level 15, State 1, Procedure usp_AttributeActivitiesForDateRange, Line 18 The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns. I have copy and pasted the select list and insert list into excel and verified there are the same number of items in each list. Both tables an additional primary key field with is not listed in either the insert statement or select list. I am not sure if that is relevant, but suspicious it may be. Here is the source for my stored procedure: CREATE PROCEDURE [dbo].[usp_AttributeActivitiesForDateRange] ( @dtmFrom DATETIME, @dtmTo DATETIME ) AS BEGIN SET NOCOUNT ON; DECLARE @dtmToWithTime DATETIME SET @dtmToWithTime = DATEADD(hh, 23, DATEADD(mi, 59, DATEADD(s, 59, @dtmTo))); -- Get uncontested DC activities INSERT INTO AttributedDoubleClickActivities ([Time], [User-ID], [IP], [Advertiser-ID], [Buy-ID], [Ad-ID], [Ad-Jumpto], [Creative-ID], [Creative-Version], [Creative-Size-ID], [Site-ID], [Page-ID], [Country-ID], [State Province], [Areacode], [OS-ID], [Domain-ID], [Keyword], [Local-User-ID], [Activity-Type], [Activity-Sub-Type], [Quantity], [Revenue], [Transaction-ID], [Other-Data], Ordinal, [Click-Time], [Event-ID]) SELECT [Time], [User-ID], [IP], [Advertiser-ID], [Buy-ID], [Ad-ID], [Ad-Jumpto], [Creative-ID], [Creative-Version], [Creative-Size-ID], [Site-ID], [Page-ID], [Country-ID], [State Province], [Areacode], [OS-ID], [Domain-ID], [Keyword], [Local-User-ID] [Activity-Type], [Activity-Sub-Type], [Quantity], [Revenue], [Transaction-ID], [Other-Data], REPLACE(Ordinal, '?', '') AS Ordinal, [Click-Time], [Event-ID] FROM Activity_Reports WHERE [Time] BETWEEN @dtmFrom AND @dtmTo AND REPLACE(Ordinal, '?', '') IN (SELECT REPLACE(Ordinal, '?', '') FROM Activity_Reports WHERE [Time] BETWEEN @dtmFrom AND @dtmTo EXCEPT SELECT CONVERT(VARCHAR, TripID) FROM VisualSciencesActivities WHERE [Time] BETWEEN @dtmFrom AND @dtmTo); END GO

  Read the article

• how to pass arguments into function within a function in r

  - by jon

  I am writing function that involve other function from base R with alot of arguments. For example (real function is much longer): myfunction <- function (dataframe, Colv = NA) { matrix <- as.matrix (dataframe) out <- heatmap(matrix, Colv = Colv) return(out) } data(mtcars) myfunction (mtcars, Colv = NA) The heatmap has many arguments that can be passed to: heatmap(x, Rowv=NULL, Colv=if(symm)"Rowv" else NULL, distfun = dist, hclustfun = hclust, reorderfun = function(d,w) reorder(d,w), add.expr, symm = FALSE, revC = identical(Colv, "Rowv"), scale=c("row", "column", "none"), na.rm = TRUE, margins = c(5, 5), ColSideColors, RowSideColors, cexRow = 0.2 + 1/log10(nr), cexCol = 0.2 + 1/log10(nc), labRow = NULL, labCol = NULL, main = NULL, xlab = NULL, ylab = NULL, keep.dendro = FALSE, verbose = getOption("verbose"), ...) I want to use these arguments without listing them inside myfun. myfunction (mtcars, Colv = NA, col = topo.colors(16)) Error in myfunction(mtcars, Colv = NA, col = topo.colors(16)) : unused argument(s) (col = topo.colors(16)) I tried the following but do not work: myfunction <- function (dataframe, Colv = NA) { matrix <- as.matrix (dataframe) out <- heatmap(matrix, Colv = Colv, ....) return(out) } data(mtcars) myfunction (mtcars, Colv = NA, col = topo.colors(16))

  Read the article

• MS SQL - Multi-Column substring matching

  - by hamlin11

  One of my clients is hooked on multi-column substring matching. I understand that Contains and FreeText search for words (and at least in the case of Contains, word prefixes). However, based upon my understanding of this MSDN book, neither of these nor their variants are capable of searching substrings. I have used LIKE rather extensively (Select * from A where A.B Like '%substr%') Sample table A: ID | Col1 | Col2 | Col3 | ------------------------------------- 1 | oklahoma | colorado | Utah | 2 | arkansas | colorado | oklahoma | 3 | florida | michigan | florida | ------------------------------------- The following code will give us row 1 and row 2: select * from A where Col1 like '%klah%' or Col2 like '%klah%' or Col3 like '%klah%' This is rather ugly, probably slow, and I just don't like it very much. Probably because the implementations that I'm dealing with have 10+ columns that need searched. The following may be a slight improvement as code readability goes, but as far as performance, we're still in the same ball park. select * from A where (Col1 + ' ' + Col2 + ' ' + Col3) like '%klah%' I have thought about simply adding insert, update, and delete triggers that simply add the concatenated version of the above columns into a separate table that shadows this table. Sample Shadow_Table: ID | searchtext | --------------------------------- 1 | oklahoma colorado Utah | 2 | arkansas colorado oklahoma | 3 | florida michigan florida | --------------------------------- This would allow us to perform the following query to search for '%klah%' select * from Shadow_Table where searchtext like '%klah%' I really don't like having to remember that this shadow table exists and that I'm supposed to use it when I am performing multi-column substring matching, but it probably yields pretty quick reads at the expense of write and storage space. My gut feeling tells me there there is an existing solution built into SQL Server 2008. However, I don't seem to be able to find anything other than research papers on the subject. Any help would be appreciated.

  Read the article

• Optimizing T-SQL where an array would be nice

  - by Polatrite

  Alright, first you'll need to grab a barf bag. I've been tasked with optimizing several old stored procedures in our database. This SP does the following: 1) cursor loops through a series of "buildings" 2) cursor loops through a week, Sunday-Saturday 3) has a huge set of IF blocks that are responsible for counting how many Objects of what Types are present in a given building Essentially what you'll see in this code block is that, if there are 5 objects of type #2, it will increment @Type_2_Objects_5 by 1. IF @Number_Type_1_Objects = 0 BEGIN SET @Type_1_Objects_0 = @Type_1_Objects_0 + 1 END IF @Number_Type_1_Objects = 1 BEGIN SET @Type_1_Objects_1 = @Type_1_Objects_1 + 1 END IF @Number_Type_1_Objects = 2 BEGIN SET @Type_1_Objects_2 = @Type_1_Objects_2 + 1 END IF @Number_Type_1_Objects = 3 BEGIN SET @Type_1_Objects_3 = @Type_1_Objects_3 + 1 END [... Objects_4 through Objects_20 for Type_1] IF @Number_Type_2_Objects = 0 BEGIN SET @Type_2_Objects_0 = @Type_2_Objects_0 + 1 END IF @Number_Type_2_Objects = 1 BEGIN SET @Type_2_Objects_1 = @Type_2_Objects_1 + 1 END IF @Number_Type_2_Objects = 2 BEGIN SET @Type_2_Objects_2 = @Type_2_Objects_2 + 1 END IF @Number_Type_2_Objects = 3 BEGIN SET @Type_2_Objects_3 = @Type_2_Objects_3 + 1 END [... Objects_4 through Objects_20 for Type_2] In addition to being extremely hacky (and limited to a quantity of 20 objects), it seems like a terrible way of handling this. In a traditional language, this could easily be solved with a 2-dimensional array... objects[type][quantity] += 1; I'm a T-SQL novice, but since writing stored procedures often uses a lot of temporary tables (which could essentially be a 2-dimensional array) I was wondering if someone could illuminate a better way of handling a situation like this with two dynamic pieces of data to store. Requested in comments: The columns are simply Number_Type_1_Objects, Number_Type_2_Objects, Number_Type_3_Objects, Number_Type_4_Objects, Number_Type_5_Objects, and CurrentDateTime. Each row in the table represents 5 minutes. The expected output is to figure out what percentage of time a given quantity of objects is present throughout each day. Sunday - Object Type 1 0 objects - 69 rows, 5:45, 34.85% 1 object - 85 rows, 7:05, 42.93% 2 objects - 44 rows, 3:40, 22.22% On Sunday, there were 0 objects of type 1 for 34.85% of the day. There was 1 object for 42.93% of the day, and 2 objects for 22.22% of the day. Repeat for each object type.

  Read the article

• xml parameter in sql server stored procedure

  - by npalle

  I want to write a stored procedure that accept an XML parameter, parsing it's elements and inserting them in a table SQL. This is my XML: <Lines> <Line> <roomlist> <room> <namehotel>MeSa</namehotel> <typeroom>506671</typeroom> <typeroomname>Dbl Standard - Tip</typeroomname> <roomnumber>0</roomnumber> <priceroom>444.60</priceroom> <costroom>400.00</costroom> <boardtype/> <paxes> <pax> <name>EU</name> <lastname>CADO</lastname> <typepax>Adult</typepax> </pax> <pax> <name>LIN</name> <lastname>BAC</lastname> <typepax>Adult</typepax> </pax> </paxes> </room> </roomlist> </Line> </Lines> How can do that?

  Read the article

• Summing Row in SQL query for time range

  - by user3703334

  I'm trying to group a large amount of data into smaller bundles. Currently the code for my query is as follows SELECT [DateTime] ,[KW] FROM [POWER] WHERE datetime >= '2014-04-14 06:00:00' and datetime < '2014-04-21 06:00:00' ORDER BY datetime which gives me DateTime KW 4/14/2014 6:00:02.0 1947 4/14/2014 6:00:15.0 1946 4/14/2014 6:00:23.0 1947 4/14/2014 6:00:32.0 1011 4/14/2014 6:00:43.0 601 4/14/2014 6:00:52.0 585 4/14/2014 6:01:02.0 582 4/14/2014 6:01:12.0 580 4/14/2014 6:01:21.0 579 4/14/2014 6:01:32.0 579 4/14/2014 6:01:44.0 578 4/14/2014 6:01:53.0 578 4/14/2014 6:02:01.0 577 4/14/2014 6:02:12.0 577 4/14/2014 6:02:22.0 577 4/14/2014 6:02:32.0 576 4/14/2014 6:02:42.0 578 4/14/2014 6:02:52.0 577 4/14/2014 6:03:02.0 577 4/14/2014 6:03:12.0 577 4/14/2014 6:03:22.0 578 . . . . 4/21/2014 5:59:55.0 11 Now there is a reading every 10 seconds from a substation. Now I want to group this data into hourly readings. Thus 00:00-01:00 = sum([KW]] for where datetime >= '^date^ 00:00:00' and datetime < '^date^ 01:00:00' I've tried using a convert to change the datetime into date and time field and then only to add all the time fields together with no success. Can someone please assist me, I'm not sure what is right way of doing this. Thanks ADDED Ok so the spilt between Datetime is working nicely, but as if I add a SUM([KW]) function SQL gives an error. And if I include any of the group functions it also nags. Below is what works, I still need to sum the KW per the grouping of hours. I've tried using Group By Hour and Group by DATEPART(Hour,[DateTime]) Both didn't work. SELECT DATEPART(Hour,[DateTime]) Hour ,DATEPART(Day,[DateTime]) Day ,DATEPART(Month,[DateTime]) Month ,([KVAReal]) ,([KVAr]) ,([KW]) FROM [POWER].[dbo].[IT10t_PAC3200] WHERE datetime >= '2014-04-14 06:00:00' and datetime < '2014-04-21 06:00:00' order by datetime

  Read the article

• SQL to insert latest version of a group of items

  - by Garett

  I’m trying to determine a good way to handle the scenario below. I have the following two database tables, along with sample data. Table1 contains distributions that are grouped per project. A project can have one or more distributions. A distribution can have one of more accounts. An account has a percentage allocated to it. The distributions can be modified by adding or removing account, as well as changing percentages. Table2 tracks distributions, assigning a version number to each distribution. I need to be able to copy new distributions from Table1 to Table2, but only under two conditions: 1. the entire distribution does not already exist 2. the distribution has been modified (accounts added/removed or percentages changed). Note: When copying a distribution from Table1 to Table2 I need to compare all accounts and percentages within the distribution to determine if it already exists. When inserting the new distribution then I need to increment the VersionID (max(VersionID) + 1). So, in the example provided the distribution (12345, 1) has been modified, adding account number 7, as well as changing percentages allocated. The entire distribution should be copied to the second table, incrementing the VersionID to 3 in the process. The database in question is SQL Server 2005. Table1 ------ ProjectID AccountDistributionID AccountID Percent 12345 1 1 25.0 12345 1 2 25.0 12345 1 7 50.0 56789 2 3 25.0 56789 2 4 25.0 56789 2 5 25.0 56789 2 6 25.0 Table2 ------ ID VersionID Project ID AccountDistributionID AccountID Percent 1 1 12345 1 1 50.0 2 1 12345 1 2 50.0 3 2 56789 2 3 25.0 4 2 56789 2 4 25.0 5 2 56789 2 5 25.0 6 2 56789 2 6 25.0

  Read the article

• What exactly is saved in SQL Server Statistics? When they get updated? Is SQL Server itself is taking care of them?

  - by Pritesh

  I have been working with SQL Server as a Developer a while. One thing I learnt is SQL Server manages Statistics which help Engine to create optimized execution plan. I could not figure out what exactly is stores in Statistics? (I read it saves Vector, but what Vector?) When/In which scenario SQL Server updates Statistics? How/why some time they go out of sync (old Statistics) In case of old Statistics is a manual DBA/Developer intervention is required or SQL Server Will get them updated. As a DBA/Developer how to find out if Statistics OLD? What should we do?

  Read the article

• Evaluation of CTEs in SQL Server 2005

  - by Jammer

  I have a question about how MS SQL evaluates functions inside CTEs. A couple of searches didn't turn up any results related to this issue, but I apologize if this is common knowledge and I'm just behind the curve. It wouldn't be the first time :-) This query is a simplified (and obviously less dynamic) version of what I'm actually doing, but it does exhibit the problem I'm experiencing. It looks like this: CREATE TABLE #EmployeePool(EmployeeID int, EmployeeRank int); INSERT INTO #EmployeePool(EmployeeID, EmployeeRank) SELECT 42, 1 UNION ALL SELECT 43, 2; DECLARE @NumEmployees int; SELECT @NumEmployees = COUNT(*) FROM #EmployeePool; WITH RandomizedCustomers AS ( SELECT CAST(c.Criteria AS int) AS CustomerID, dbo.fnUtil_Random(@NumEmployees) AS RandomRank FROM dbo.fnUtil_ParseCriteria(@CustomerIDs, 'int') c) SELECT rc.CustomerID, ep.EmployeeID FROM RandomizedCustomers rc JOIN #EmployeePool ep ON ep.EmployeeRank = rc.RandomRank; DROP TABLE #EmployeePool; The following can be assumed about all executions of the above: The result of dbo.fnUtil_Random() is always an int value greater than zero and less than or equal to the argument passed in. Since it's being called above with @NumEmployees which has the value 2, this function always evaluates to 1 or 2. The result of dbo.fnUtil_ParseCriteria(@CustomerIDs, 'int') produces a one-column, one-row table that contains a sql_variant with a base type of 'int' that has the value 219935. Given the above assumptions, it makes sense (to me, anyway) that the result of the expression above should always produce a two-column table containing one record - CustomerID and an EmployeeID. The CustomerID should always be the int value 219935, and the EmployeeID should be either 42 or 43. However, this is not always the case. Sometimes I get the expected single record. Other times I get two records (one for each EmployeeID), and still others I get no records. However, if I replace the RandomizedCustomers CTE with a true temp table, the problem vanishes completely. Every time I think I have an explanation for this behavior, it turns out to not make sense or be impossible, so I literally cannot explain why this would happen. Since the problem does not happen when I replace the CTE with a temp table, I can only assume it has something to do with the functions inside CTEs are evaluated during joins to that CTE. Do any of you have any theories?

  Read the article

• Migrate data from SQL Compact to SQL Server 2008

  - by Martin

  I need to do a one-time migration of data from SQL Server Compact Edition to SQL Server 2008 Express Edition. I'm looking for a tool to do this kind of migration. I've tried using Import and Export Data in SQL Server, but it doesn't let me import from SQL Server Compact Edition. Anyone knows of a easy way to do it?

  Read the article

• Problem attaching mdf file in sql server 2008

  - by Fraz Sundal

  I have an mdf file of sql server 2005 database now i want it to attach in sql server 2008 R2 but when i try to attach it, it gave me error saying. Unable to open the physical file "D:\Fraz\Freelance\Database\DBmdf13aug\mbh_pk.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120) what can be the problem and how to fix it? Is this folder permission error or sql server 2008 have something missing

  Read the article

• SQL Server - VMWare install - Utilize more RAM

  - by alex

  We have a SQL server machine - It’s a VMWare image (running on ESXi hardware etc..) It has windows 2008 x64 standard The SQL install is SQL 2008 standard The virtual machine has 12gb of RAM, and 4 virtual CPU The box is suffering from near 100% CPU a lot of the time I enabled the AWE- but SQL server only seems to use 3-4gb of RAM Is there a way of making it use more available ram more effectively? cache results for example..?

  Read the article

• SQL server deadlock between INSERT and SELECT statement

  - by dtroy

  Hi! I've got a problem with multiple deadlocks on SQL server 2005. This one is between an INSERT and a SELECT statement. There are two tables. Table 1 and Table2. Table2 has Table1's PK (table1_id) as foreign key. Index on table1_id is clustered. The INSERT inserts a single row into table2 at a time. The SELCET joins the 2 tables. (it's a long query which might take up to 12 secs to run) According to my understanding (and experiments) the INSERT should acquire an IS lock on table1 to check referential integrity (which should not cause a deadlock). But, in this case it acquired an IX page lock The deadlock report: <deadlock-list> <deadlock victim="process968898"> <process-list> <process id="process8db1f8" taskpriority="0" logused="2424" waitresource="OBJECT: 5:789577851:0 " waittime="12390" ownerId="61831512" transactionname="user_transaction" lasttranstarted="2010-04-16T07:10:13.347" XDES="0x222a8250" lockMode="IX" schedulerid="1" kpid="3764" status="suspended" spid="52" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-04-16T07:10:13.350" lastbatchcompleted="2010-04-16T07:10:13.347" clientapp=".Net SqlClient Data Provider" hostname="VIDEV01-B-ME" hostpid="3040" loginname="DatabaseName" isolationlevel="read uncommitted (1)" xactid="61831512" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="DatabaseName.dbo.prcTable2_Insert" line="18" stmtstart="576" stmtend="1148" sqlhandle="0x0300050079e62d06e9307f000b9d00000100000000000000"> INSERT INTO dbo.Table2 ( f1, table1_id, f2 ) VALUES ( @p1, @p_DocumentVersionID, @p1 ) </frame> </executionStack> <inputbuf> Proc [Database Id = 5 Object Id = 103671417] </inputbuf> </process> <process id="process968898" taskpriority="0" logused="0" waitresource="PAGE: 5:1:46510" waittime="7625" ownerId="61831406" transactionname="INSERT" lasttranstarted="2010-04-16T07:10:12.717" XDES="0x418ec00" lockMode="S" schedulerid="2" kpid="1724" status="suspended" spid="53" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-04-16T07:10:12.713" lastbatchcompleted="2010-04-16T07:10:12.713" clientapp=".Net SqlClient Data Provider" hostname="VIDEV01-B-ME" hostpid="3040" loginname="DatabaseName" isolationlevel="read committed (2)" xactid="61831406" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="DatabaseName.dbo.prcGetList" line="64" stmtstart="3548" stmtend="11570" sqlhandle="0x03000500dbcec17e8d267f000b9d00000100000000000000"> <!-- XXXXXXXXXXXXXX...SELECT STATEMENT WITH Multiple joins including both Table2 table 1 and .... XXXXXXXXXXXXXXX --> </frame> </executionStack> <inputbuf> Proc [Database Id = 5 Object Id = 2126630619] </inputbuf> </process> </process-list> <resource-list> <pagelock fileid="1" pageid="46510" dbid="5" objectname="DatabaseName.dbo.table1" id="lock6236bc0" mode="IX" associatedObjectId="72057594042908672"> <owner-list> <owner id="process8db1f8" mode="IX"/> </owner-list> <waiter-list> <waiter id="process968898" mode="S" requestType="wait"/> </waiter-list> </pagelock> <objectlock lockPartition="0" objid="789577851" subresource="FULL" dbid="5" objectname="DatabaseName.dbo.Table2" id="lock970a240" mode="S" associatedObjectId="789577851"> <owner-list> <owner id="process968898" mode="S"/> </owner-list> <waiter-list> <waiter id="process8db1f8" mode="IX" requestType="wait"/> </waiter-list> </objectlock> </resource-list> </deadlock> </deadlock-list> Can anyone explain why the INSERT gets the IX page lock ? Am I not reading the deadlock report properly? BTW, I have not managed to reproduce this issue. Thanks!

  Read the article

• Different ways to query this search in SQL?

  - by Bart Terrell

  I am teaching myself MS-SQL and I am trying to find different ways to find the Count of Paid and Unpaid Claims for 2012 grouped by Region from these 3 tables. If there is a returned date, the claim is unpaid if the returned date is null then the claim is paid. I will attach the code I have ran, but I am not sure if there are better ways to do it. Thanks. Here is the code: SET dateformat ymd; CREATE TABLE Claims ( ClaimID INT, SubID INT, [Claim Date] DATETIME ); CREATE TABLE Phoneship ( ClaimID INT, [Shipping Number] INT, [Claim Date] DATETIME, [Ship Date] DATETIME, [Returned Date] DATETIME ); CREATE TABLE Enrollment ( SubID INT, Enrollment_Date DATETIME, Channel NVARCHAR(255), Region NVARCHAR(255), Status FLOAT, Drop_Date DATETIME ); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (102, 201, '2011-10-13 00:00:00', '2011-10-14 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (103, 202, '2011-11-02 00:00:00', '2011-11-03 00:00:00', '2011-11-20 00:00:00'); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (103, 203, '2011-11-02 00:00:00', '2011-11-22 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (105, 204, '2012-01-16 00:00:00', '2012-01-17 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (106, 205, '2012-02-15 00:00:00', '2012-02-16 00:00:00', '2012-02-26 00:00:00'); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (106, 206, '2012-02-15 00:00:00', '2012-02-27 00:00:00', '2012-03-06 00:00:00'); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (107, 207, '2012-03-12 00:00:00', '2012-03-13 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (108, 208, '2012-05-11 00:00:00', '2012-05-12 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (109, 209, '2012-05-13 00:00:00', '2012-05-14 00:00:00', '2012-05-28 00:00:00'); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (109, 210, '2012-05-13 00:00:00', '2012-05-30 00:00:00', NULL); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (101, 12345678, '2011-03-06 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (102, 12347190, '2011-10-13 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (103, 12348723, '2011-11-02 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (104, 12349745, '2011-11-09 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (105, 12347190, '2012-01-16 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (106, 12349234, '2012-02-15 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (107, 12350767, '2012-03-12 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (108, 12350256, '2012-05-11 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (109, 12347701, '2012-05-13 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (110, 12350256, '2012-05-15 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (111, 12350767, '2012-06-30 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12345678, '2011-01-05 00:00:00', 'Retail', 'Southeast', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12346178, '2011-03-13 00:00:00', 'Indirect Dealers', 'West', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12346679, '2011-05-19 00:00:00', 'Indirect Dealers', 'Southeast', 0, '2012-03-15 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12347190, '2011-07-25 00:00:00', 'Retail', 'Northeast', 0, '2012-05-21 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12347701, '2011-08-14 00:00:00', 'Indirect Dealers', 'West', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12348212, '2011-09-30 00:00:00', 'Retail', 'West', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12348723, '2011-10-20 00:00:00', 'Retail', 'Southeast', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12349234, '2012-01-06 00:00:00', 'Indirect Dealers', 'West', 0, '2012-02-14 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12349745, '2012-01-26 00:00:00', 'Retail', 'Northeast', 0, '2012-04-15 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12350256, '2012-02-11 00:00:00', 'Retail', 'Southeast', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12350767, '2012-03-02 00:00:00', 'Indirect Dealers', 'West', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12351278, '2012-04-18 00:00:00', 'Retail', 'Midwest', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12351789, '2012-05-08 00:00:00', 'Indirect Dealers', 'West', 0, '2012-07-04 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12352300, '2012-06-24 00:00:00', 'Retail', 'Midwest', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12352811, '2012-06-25 00:00:00', 'Retail', 'Southeast', 1, NULL); And Query1 SELECT Count(ClaimID) AS 'Paid Claim', (SELECT Count(ClaimID) FROM dbo.phoneship WHERE [returned date] IS NOT NULL) AS 'Unpaid Claim' FROM dbo.Phoneship WHERE [Returned Date] IS NULL GROUP BY claimid Query2 SELECT Count(*) AS 'Paid Claims', (SELECT Count(*) FROM dbo.Phoneship WHERE [Returned Date] IS NOT NULL) AS 'Unpaid Claims' FROM dbo.Phoneship WHERE [Returned Date] IS NULL; Query3 Select Distinct(C.[Shipping Number]), Count(C.ClaimID) AS 'COUNT ClaimID', A.Region, A.SubID From dbo.HSEnrollment A Inner Join dbo.Claims B On A.SubId = B.SubId Inner Join dbo.Phoneship C On B.ClaimID = C.ClaimID Where C.[Returned Date] IS NULL Group By A.Region, A.Subid, C.ClaimID, C.[Shipping Number] Order By A.Region

  Read the article

• SQL Server Query Notification with BizTalk Server 2009 WCF SQL Adapter

  SQL Server Query Notification with BizTalk Server 2009 WCF SQL Adapter

  Read the article

• SQL reporting using SQL Server Business Intelligence Studio

  SQL reporting using SQL Server Business Intelligence Studio

  Read the article

< Previous Page | 50 51 52 53 54 55 56 57 58 59 60 61  | Next Page >