Search Results

Search found 42321 results on 1693 pages for 'sql reporting services 05'.

Page 64/1693 | < Previous Page | 60 61 62 63 64 65 66 67 68 69 70 71 | Next Page >

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
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
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
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
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
Cloud MBaaS : The Next Big Thing in Enterprise Mobility

- by shiju

In this blog post, I will take a look at Cloud Mobile Backend as a Service (MBaaS) and how we can leverage Cloud based Mobile Backend as a Service for building enterprise mobile apps. Today, mobile apps are incredibly significant in both consumer and enterprise space and the demand for the mobile apps is unbelievably increasing in day to day business. An enterprise can’t survive in business without a proper mobility strategy. A better mobility strategy and faster delivery of your mobile apps will give you an extra mileage for your business and IT strategy. So organizations and mobile developers are looking for different strategy for meeting this demand and adopting different development strategy for their mobile apps. Some developers are adopting hybrid mobile app development platforms, for delivering their products for multiple platforms, for fast time-to-market. Others are adopting a Mobile enterprise application platform (MEAP) such as Kony for their enterprise mobile apps for fast time-to-market and better business integration. The Challenges of Enterprise Mobility The real challenge of enterprise mobile apps, is not about creating the front-end environment or developing front-end for multiple platforms. The most important thing of enterprise mobile apps is to expose your enterprise data to mobile devices where the real pain is your business data might be residing in lot of different systems including legacy systems, ERP systems etc., and these systems will be deployed with lot of security restrictions. Exposing your data from the on-premises servers, is not a easy thing for most of the business organizations. Many organizations are spending too much time for their front-end development strategy, but they are really lacking for building a strategy on their back-end for exposing the business data to mobile apps. So building a REST services layer and mobile back-end services, on the top of legacy systems and existing middleware systems, is the key part of most of the enterprise mobile apps, where multiple mobile platforms can easily consume these REST services and other mobile back-end services for building mobile apps. For some mobile apps, we can’t predict its user base, especially for products where customers can gradually increase at any time. And for today’s mobile apps, faster time-to-market is very critical so that spending too much time for mobile app’s scalability, will not be worth. The real power of Cloud is the agility and on-demand scalability, where we can scale-up and scale-down our applications very easily. It would be great if we could use the power of Cloud to mobile apps. So using Cloud for mobile apps is a natural fit, where we can use Cloud as the storage for mobile apps and hosting mechanism for mobile back-end services, where we can enjoy the full power of Cloud with greater level of on-demand scalability and operational agility. So Cloud based Mobile Backend as a Service is great choice for building enterprise mobile apps, where enterprises can enjoy the massive scalability power of their mobile apps, provided by public cloud vendors such as Microsoft Windows Azure. Mobile Backend as a Service (MBaaS) We have discussed the key challenges of enterprise mobile apps and how we can leverage Cloud for hosting mobile backend services. MBaaS is a set of cloud-based, server-side mobile services for multiple mobile platforms and HTML5 platform, which can be used as a backend for your mobile apps with the scalability power of Cloud. The information below provides the key features of a typical MBaaS platform: Cloud based storage for your application data. Automatic REST API services on the application data, for CRUD operations. Native push notification services with massive scalability power. User management services for authenticate users. User authentication via Social accounts such as Facebook, Google, Microsoft, and Twitter. Scheduler services for periodically sending data to mobile devices. Native SDKs for multiple mobile platforms such as Windows Phone and Windows Store, Android, Apple iOS, and HTML5, for easily accessing the mobile services from mobile apps, with better security. Typically, a MBaaS platform will provide native SDKs for multiple mobile platforms so that we can easily consume the server-side mobile services. MBaaS based REST APIs can use for integrating to enterprise backend systems. We can use the same mobile services for multiple platform so hat we can reuse the application logic to multiple mobile platforms. Public cloud vendors are building the mobile services on the top of their PaaS offerings. Windows Azure Mobile Services is a great platform for a MBaaS offering that is leveraging Windows Azure Cloud platform’s PaaS capabilities. Hybrid mobile development platform Titanium provides their own MBaaS services. LoopBack is a new MBaaS service provided by Node.js consulting firm StrongLoop, which can be hosted on multiple cloud platforms and also for on-premises servers. The Challenges of MBaaS Solutions If you are building your mobile apps with a new data storage, it will be very easy, since there is not any integration challenges you have to face. But most of the use cases, you have to extract your application data in which stored in on-premises servers which might be under VPNs and firewalls. So exposing these data to your MBaaS solution with a proper security would be a big challenge. The capability of your MBaaS vendor is very important as you have to interact with your legacy systems for many enterprise mobile apps. So you should be very careful about choosing for MBaaS vendor. At the same time, you should have a proper strategy for mobilizing your application data which stored in on-premises legacy systems, where your solution architecture and strategy is more important than platforms and tools. Windows Azure Mobile Services Windows Azure Mobile Services is an MBaaS offerings from Windows Azure cloud platform. IMHO, Microsoft Windows Azure is the best PaaS platform in the Cloud space. Windows Azure Mobile Services extends the PaaS capabilities of Windows Azure, to mobile devices, which can be used as a cloud backend for your mobile apps, which will provide global availability and reach for your mobile apps. Windows Azure Mobile Services provides storage services, user management with social network integration, push notification services and scheduler services and provides native SDKs for all major mobile platforms and HTML5. In Windows Azure Mobile Services, you can write server-side scripts in Node.js where you can enjoy the full power of Node.js including the use of NPM modules for your server-side scripts. In the previous section, we had discussed some challenges of MBaaS solutions. You can leverage Windows Azure Cloud platform for solving many challenges regarding with enterprise mobility. The entire Windows Azure platform can play a key role for working as the backend for your mobile apps where you can leverage the entire Windows Azure platform for your mobile apps. With Windows Azure, you can easily connect to your on-premises systems which is a key thing for mobile backend solutions. Another key point is that Windows Azure provides better integration with services like Active Directory, which makes Windows Azure as the de facto platform for enterprise mobility, for enterprises, who have been leveraging Microsoft ecosystem for their application and IT infrastructure. Windows Azure Mobile Services is going to next evolution where you can expect some exciting features in near future. One area, where Windows Azure Mobile Services should definitely need an improvement, is about the default storage mechanism in which currently it is depends on SQL Server. IMHO, developers should be able to choose multiple default storage option when creating a new mobile service instance. Let’s say, there should be a different storage providers such as SQL Server storage provider and Table storage provider where developers should be able to choose their choice of storage provider when creating a new mobile services project. I have been used Windows Azure and Windows Azure Mobile Services as the backend for production apps for mobile, where it performed very well. MBaaS Over MEAP Recently, many larger enterprises has been adopted Mobile enterprise application platform (MEAP) for their mobile apps. I haven’t worked on any production MEAP solution, but I heard that developers are really struggling with MEAP in different way. The learning curve for a proprietary MEAP platform is very high. I am completely against for using larger proprietary ecosystem for mobile apps. For enterprise mobile apps, I highly recommend to use native iOS/Android/Windows Phone or HTML5 for front-end with a cloud hosted MBaaS solution as the middleware. A MBaaS service can be consumed from multiple mobile apps where REST APIs are using to integrating with enterprise backend systems. Enterprise mobility should start with exposing REST APIs on the enterprise backend systems and these REST APIs can host on Cloud where we can enjoy the power of Cloud for our services. If you are having REST APIs for your enterprise data, then you can easily build mobile frontends for multiple platforms. You can follow me on Twitter @shijucv

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">  </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
Print Any Document Type with AutoVue Document Print Services

- by [email protected]

The newly released AutoVue Document Print Services allow development organizations to automate and process high volume printing operations, of both business and technical document types, within their broader enterprise applications. For many organizations, their printing processes are challenged by the fact that they can only print a small subset of the documents required by their enterprise users. By integrating AutoVue Document Print Services, and deploying them in conjunction with their existing print server solutions, organizations can address that challenge and automate the printing of virtually any document type required in any business process, greatly extending the value of their print server solutions, and improving business processes and workforce productivity. For further details, check out the AutoVue Document Print Services datasheet.

Read the article
SQL Server Analysis Services, DNS, AD, Kerberos, Connection Issues

- by ScaleOvenStove

Running into a very weird issue. Converting servers to Windows 2008/SQL 2008. Have a server, SERVER_A, brand new, setup with Win2k8,Sql2k8 - works. Have a Server SERVER_B, running Windows2003/SQL2005. I want to migrate from SERVER_B to SERVER_A. I have all db's, cubes, etc setup on SERVER_A and it is mimicking functionality. Since users are using Excel to connect to SSAS, they connection string has SERVER_B in it. What I want to do, is, change DNS on the network to point SERVER_B (by name) at the ip of SERVER_A. I have successfully done this with another server, SERVER_C, but I need to do it with SERVER_B. What I have found is that with SERVER_C, after changing DNS, had to remove SERVER_C from AD and then it worked. I could connect to SERVER_C (DB), SERVER_C (SSAS Default Instance) and SERVER_C (SSAS Named instance) and it all was actually connecting to SERVER_A I tried to do the same with with SERVER_B, and no luck. Changed DNS, removed from AD, and it wouldn't connect. Found out that there were some SPN's in AD set up, so removed those and tried again. I then could connect to SERVER_B (DB), SERVER_B (SSAS Named Instance), but not SERVER_B (SSAS Default Instance). I could connect to SERVER_B (SSAS Default Intance WITH the Port #), but I need to be able to connect without the port number. I am at a loss to as why I can't connect to the default instance without a port #. Not sure if it is SPN's in AD, or another AD issue, or something else. Pretty sure it isnt something on the server (because SERVER_C works!) Any insight or suggestions would be greatly helpful!!

Read the article
Fibre Optic Services: Gibson Technical Services Leads In Telecommunications

Fibre optic services are important to the success of your cable television network or equipment manufacturing business. You require the insurance of smooth, efficient operations and the knowledge tha... [Author: Toan Dinh - Computers and Internet - March 20, 2010]

Read the article
How to deploy SQL Server 2005 Reporting Services on a network without a domain server?

- by ti

I have a small Windows network (~30 machines) and I need to deploy SQL Server 2005 Reporting Services. Because I use SQL Server Standard Edition and not Enterprise, I am forced to use Windows Authentication to the users. I am a Linux admin, and have near zero knowledge on Active Directory. As deep as my shallow knowledge goes, I think that I would need to invest in a domain server, a mirrored backup of that domain server. I think that I need to change every computer to use this domain too, and if the domain server goes down, every computer will be unavailable. Is there a easier way to deploy Windows Authentication so that users can access Reporting Services from their computers without changing the infra-structure that much? Thanks!

Read the article
SQL Azure and Trust Services

- by BuckWoody

Microsoft is working on a new Windows Azure service called “Trust Services”. Trust Services takes a certificate you upload and uses it to encrypt and decrypt sensitive data in the cloud. Of course, like any security service, there’s a bit more to it than that. I’ll give you a quick overview of how you can use this product to protect data you send to SQL Azure. The primary issue with storing data in the cloud is that you are in an environment that isn’t under your control – in fact, that’s the benefit of being in a distributed computing environment in the first place. On premises you’re able to encrypt data you don’t want anyone else to see, using various methods such as passwords (not very strong) or certificates (stronger). When you use a certificate, it’s vital that you create (or procure) and protect it yourself. When you store data remotely, regardless of IaaS, PaaS or SaaS, you don’t own the machines where the data lives. That means if you use a certificate from the cloud vendor to encrypt the data, you have to trust that the data won’t be accessed by the vendor. In some cases having a signed agreement with the vendor that they won’t access your data is sufficient, in other cases that doesn’t meet the requirements your system has for security. With the new Trust Services service, the basic process is that you use a Portal to create a Trust Server using policies and other controls. You place a X.509 Certificate you create or procure in that server. Using the Software development Kit (SDK), the developer has access to an Application Layer Encryption Framework to set fields of data they want to encrypt. From there, the data can be stored in SQL Azure as a standard field – only it is encrypted before it ever arrives. The portion of the client software that decrypts the data uses the same service, so the authenticated user sees the data if they are allowed to do so. The data remains encrypted “at rest”. You can learn more about this product and check it out in the SQL Azure labs at Microsoft Codename "Trust Services"

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
Reporting Services 2008 & 2012 side by side

- by Iulian Ilies

I have installed Sql Server 2008 & 2012 side by side on the same machine, and that's includes the reporting service for each. Both are instances are named instances: MSSQLSERVER2008 and MSSQLSERVER2012. I didn't configure the 2008 one but configured 2012 first and this one is working fine. However later on when I wanted to configure the 2008 reporting service instance I was not able to do so; it simply cannot find it. Both services are displayed as running, nevertheless while being in Reporting Services Configuration Manager only the 2012 instance is displayed. I tried stopping the 2012 but still no luck, 2008 won't show up in the RS configuration manager. Any suggestions? Thanks in advance Iulian

Read the article
Services or Shared Libraries?

- by Royal

I work in an environment where we have several different web applications, where each of them have different features but still need to do similar things: authentication, read from common data sources, store common data, etc. Is it better to build the shared functionality into a set of services, to be called by the web apps, or is it better to make a shared library, which the webapps include? The services or libraries would need to access various databases, and it seems like keeping that access in a single place (service) is a good idea. It would also reduce the number of database connections needed. A service would also keep the logic in a single place, but then it could be argued that a shared library can do the same thing. Are there other benefits to be gained from using services over shared libraries?

Read the article
Brief Info About SEO Services Vs PPC Services

Both of these services are different, but both have an important say in the way the website does business. After all, both of them are implemented for the very fact that the website has to do well.

Read the article
SQL reporting using SQL Server Business Intelligence Studio

SQL reporting using SQL Server Business Intelligence Studio

Read the article
????????SQL Developer?Data Modeler?????????????

- by Yusuke.Yamamoto

????? ??:2010/05/18 ??:?????? Oracle ?GUI?????????···??????????????????SQL Developer ? Data Modeler ???????GUI???????????????????????????SQL Developer ? Data Modeler ?????????????????? ????Oracle SQL Developer Data Modeler ??Oracle SQL Developer Data Modeler ????Oracle SQL Developer ???Oracle SQL Developer ???????? ????????? ????????????????? http://www.oracle.com/technology/global/jp/ondemand/otn-seminar/pdf/100518_sqldeveloper_evening.pdf

Read the article

< Previous Page | 60 61 62 63 64 65 66 67 68 69 70 71 | Next Page >