Wednesday, April 3, 2013

[SQL Server 2008 issues] EX0-117 Demo

[SQL Server 2008 issues] EX0-117 Demo

EX0-117 Demo

Posted: 02 Apr 2013 07:21 PM PDT

Exam Number/Code : EX0-117Exam Name : ITIL Foundation (syllabus 2011)Questions and Answers : 113 Q&AsUpdate Time: 2013-02-01Question:1Implementation of ITIL service management requires the preparation and planning of the effective and efficient use of "the four Ps".What are these four Ps?A.People, process, partners, performanceB.Performance, process, products, problemsC.People, process, products, partnersD.People, products, perspective, partnersAnswer：CQuestion:2Which one of the following is it the responsibility of supplier management to negotiate and agree?A.Service level agreements (SLAs)B.Third-party contractsC.The service portfolioD.Operational level agreements (OLAs)Answer：BQuestion:3Which one of the following activities are carried out during the "Where do we want to be?" step of the continual service improvement (CSI) approach?A.Implementing service and process improvementsB.Reviewing measurements and metricsC.Creating a baselineD.Defining measurable targetsAnswer：Dhttp://www.exam1pass.com/EX0-117-exam.html

Splitting the rows -- distributing on logic

Posted: 01 Apr 2013 08:02 PM PDT

Hi Experts , here is sample table and data create table #sample ( Invtid varchar(255) ,[Final SiteId] varchar(255) ,Whseloc varchar(255) ,Days int ,Qty int ,[Aging Stock] int ,Priority int)insert into #sampleselect '11003291001-----NOB30000','0V003','A00A00',269,285,305,1 union allselect '11003291001-----NOB30000','03003','A00A00',287,30,305,2-- Expected output Invtid Siteid Whseloc Days Qty11003291001-----NOB30000 0V003 A00A00 269 28511003291001-----NOB30000 03003 A00A00 269 20 -- 305-285=20 (20 out of 30)11003291001-----NOB30000 03003 A00A00 287 10 -- 10 out of 30here is the logic:row 1 priority column 1 first distribution i allocated 285 units. But the bucket at the [Aging Stock] had originally 305 units Then the balance between the 305 - 285 = 20 units. That means that i still have 20 units in stock with 269 days.So i will allocate only the 20 units to the priority 2[Aging Stock] (20 from 30)Now in the final table i have 2 lines11003291001-----NOB30000 0V003 A00A00 269 285 ( First Allocation) 11003291001-----NOB30000 03003 A00A00 269 20 ( Second Allocation -- from row 2)But there are still 10 units left from the priority 2 So now if i look again to the priority 1 [Aging Stock], i can't work with the 269 days, since now there is 0 balance there, i use the 10 left for the second allocation. So i move to the next record11003291001-----NOB30000 03003 287 10The next record has exactly the same 10 units i need, and i insert those 10 units with 287 days to the final table. Now the final output will have 3 records11003291001-----NOB30000 0V003 A00A00 269 285 ( First Allocation) 11003291001-----NOB30000 03003 A00A00 269 20 ( Second Allocation ) 11003291001-----NOB30000 03003 A00A00 287 10 ( Third Allocation )please help me ..

Corruption - Renaming of system data databases to ldf :(

Posted: 02 Apr 2013 10:34 AM PDT

Hi,This morning a made a proper boo boo while building a new SQL Server 2008 R2 install. After the installation had completed I wanted to move the system databases to a new drive.However when moving the log files I executed the following T-SQL in error:ALTER DATABASE [master] MODIFY FILE ( NAME = master , FILENAME = 'J:\Log\SystemLog\mastlog.ldf' );ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'J:\Log\SystemLog\modellog.ldf' );i.e. I renamed the data files to ldf's.....As a result I cannot start the SQL Engine and get the following error in the ERRORLOG on startup:2013-04-03 10:12:40.74 spid9s Error: 5171, Severity: 16, State: 1.2013-04-03 10:12:40.74 spid9s J:\Log\SystemLog\modellog.ldf is not a primary database file.2013-04-03 10:12:40.77 spid9s Error: 945, Severity: 14, State: 2.2013-04-03 10:12:40.77 spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.2013-04-03 10:12:40.77 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.I would rather not have to re-install again. Does anyone have any advice on how to get around this one?Thanks in advance,JK

Sql Server 2008 Generate Script Process

Posted: 02 Apr 2013 06:36 PM PDT

Hi,I have a questions with regards to using Sql Server 2008 to automatically script out sql server objects.If I right-click on a database and select "Tasks" and then "Generate Scripts.." under the "Choose Script Options" page if I select "Include If NOT EXISTS" and then script a stored procedure out to a New Query Window, the code is wrapped in an sp_executesql statements:IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Example]') AND type in (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @statement = N'create procedure [dbo].[Example]asselect top 1 [name] from sys.databases;' ENDGOWhereas if I just script the object out from SSMS manually it scripts it like this, which is exactly how I want it to be scripted:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[Example]asselect top 1 [name] from sys.databases;GOI need to be able to script thousands of SP's out of multiple databases into one file per object. I'm running Sql Server 2008, do you know if this behaviour also exists in Sql Server 2008 R2, and if so, a simple way to get the objects out, one per file without the "sp_executesql".I could put something together in Powershell or using dynamic sql, but I was wondering if anyone had any better ideas, or if it was different in R2.Thanks in advance,Chris

Named Instance and specified port not connecting

Posted: 13 Feb 2013 07:23 PM PST

Hi allI have opened up a port on a remote SQL instance and can see that the port is LISTENING when using the PortQry tool. I have also set the TCP port in the TCP/IP properties in the IPAll section for that instance, yet I am unable to connect and get an error ofConnection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1; handshake=14998; (.Net SqlClient Data Provider)Does anyone have any suggestions on what could be wrong? I have done this on other instances, although they were default instances, and it has always worked fine.Thanks

executing batch file from remote server using SSIS

Posted: 02 Apr 2013 05:12 PM PDT

Can anybody help me>>>>we have a batch file in 227 server(admin login) and i want to execute it through SSIS from 227 server user login (not admin login).. i hope it cannot be done due to restricted permissions in 227 server user login,, but can it be done vice-versa, i mean executing batch file of 227 server(user login) from 227 server(admin login) using SSIS.it's confusing ...but any help is appreciatedThanks a Million!!!Regards,SQL Geek

Updating multipe databases on multiple serves

Posted: 02 Apr 2013 09:19 AM PDT

Is there a way we can update multiple databases on Multiple servers? I am trying to sync multiple databases but do not want to use replication since I only want it run once a day .

Informix Linked Server problems

Posted: 20 Apr 2010 09:13 PM PDT

I have been asked to set up a few procs that will take data from our core data source in Informix and compare the values with our data warehouse which is SQL Server 2008.The Linked Server works fine and connects no problem, but whenever I try to run a simple select I get a few errors that are driving me nuts.The Simple select I'm trying to test first is:select * from LIVE.live_db.informix.thistableThis is correct (I've messed about with it a bit hence the stupid naming) but when I run it I get the following message:OLE DB provider "Ifxoledbc" for linked server "LIVE" returned message "EIX000: (-111) ISAM error: no record found.".Msg 7311, Level 16, State 2, Line 1Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "Ifxoledbc" for linked server "LIVE". The provider supports the interface, but returns a failure code when it is used.Has anyone had any experience with these kind of errors before as it's driving me nuts.CheersJim

Migrate 1 Billion row, 1 TB table from SQL2005 to SQL2008

Posted: 02 Apr 2013 02:56 AM PDT

I am trying to figure out the best way to migrate a 1 billion row, 1 terabyte table from SQL2005 to SQL2008. The source table is not partitioned and is not in its own file group. The target table will be partitioned on 50 million row partitions with one filegroup per partition and one file per file group.The approach I am currently testing is as follows:1. Setup the target table with the desired partitioned setup.2. In each file group, create a staging table corresponding to that partition. For example, the first partition will contain rows where the rowid is less than 50 million. Data in the source table with a rowid less than 50 million will be migrated into the staging table for the first partition. The migration method right now is INSERT INTO target SELECT FROM source.3. After the staging table is loaded, the cluster index and secondary indexes will be built (the same index structure as is found on the staging table). 4. Then, the staging table will be moved into the partition using an ALTER command.Anyway, I am not pleased with the processing times and I am looking for suggestions on faster methods.What about bulk data options?Does it make sense to load each staging table table with indexes or is it better to create the indexes after loading the table.

Analyse C2 Auditing trace files data in SQL Server

Posted: 02 Apr 2013 10:38 AM PDT

Hi,I enabled c2 auditing on a sql server and loaded those files in to a table for analysing last one month database activity. Requirement: I want to list out all the users who accessed any database in that server.Can you please let me know what data should i analyse and also can you provide me the query for analysing data.SELECT TOP 1000 [TextData] ,[BinaryData] ,[DatabaseID] ,[TransactionID] ,[LineNumber] ,[NTUserName] ,[NTDomainName] ,[HostName] ,[ClientProcessID] ,[ApplicationName] ,[LoginName] ,[SPID] ,[Duration] ,[StartTime] ,[EndTime] ,[Reads] ,[Writes] ,[CPU] ,[Permissions] ,[Severity] ,[EventSubClass] ,[ObjectID] ,[Success] ,[IndexID] ,[IntegerData] ,[ServerName] ,[EventClass] ,[ObjectType] ,[NestLevel] ,[State] ,[Error] ,[Mode] ,[Handle] ,[ObjectName] ,[DatabaseName] ,[FileName] ,[OwnerName] ,[RoleName] ,[TargetUserName] ,[DBUserName] ,[LoginSid] ,[TargetLoginName] ,[TargetLoginSid] ,[ColumnPermissions] ,[LinkedServerName] ,[ProviderName] ,[MethodName] ,[RowCounts] ,[RequestID] ,[XactSequence] ,[EventSequence] ,[BigintData1] ,[BigintData2] ,[GUID] ,[IntegerData2] ,[ObjectID2] ,[Type] ,[OwnerID] ,[ParentName] ,[IsSystem] ,[Offset] ,[SourceDatabaseID] ,[SqlHandle] ,[SessionLoginName] ,[PlanHandle] ,[GroupID] FROM [audit].[dbo].[audit]Thansk all

drop and add all FKs in the database

Posted: 02 Apr 2013 07:35 AM PDT

I am using SSIS to import data, but there is problem of Forignkey constraints.So I think I need to drop all the FKs, then truncate table, import data then add the FK back.Is there an easy way to generate drop and create FK statements other than using SSMS?Thanks

‘Near Real Time’ Reporting Copy of OLTP databases (many of them) with a twist! Help

Posted: 02 Apr 2013 05:35 AM PDT

Version is 2008 R2Number of Databases: 1,328 (and growing)Database Size: 5MB – 600GB (Depends on client's size)Schema: Single Tenant (All Db's schemas are the same)Cluster: Currently 2 node active\passiveRight now clients log into a portal and do run reports and since the DB is more of an OLTP these reports as clients grow start to become an issue. For the last 3 years I have optimized the hell out of these DB's to co-exist in a OLTP\Reporting world but I think it's time to create reporting copies (or something??) so that locking is at bay and we can start building larger reports without effecting the production DBs.Requirements:1. Near real-time (1-10min)2. A way to automate new reporting replication when new client database is created3. Be able to work with thousands of databasesCurrently I'm looking at all kinds of ways but I just don't know where to start? We only really report on certain databases and would it be best to replicate data to a single data cube where the portal just connects to that database for reporting?I wouldn't be opposed to hiring someone either to help.Thanks.-Richard King

Show cross-database dependencies between objects

Posted: 02 Apr 2013 01:46 AM PDT

Hello all,I use SQL Server 2008 R2. In the SSMS there is a nice funcionality that shows both "Objects that depend on ..." and "Objects on which ... depends". The latter one works fine, even referencing objecs that are located in other databases, but the first ("Objects that depend on "...") does not work if it is referenced by an object in other database.Is there any way to find out what objects depend on one table, for example, that is referenced by views, for example, in another database? I have tested the sys.dm_sql_referencing_entities but it also didn't work.I think it is possible to do it, because, if it works for objects on which .... depends, theoretically, it is possible to do the other way.Thanks in advanceDBA CabulosoLucas Benevides

Bulk Insert with Format File

Posted: 02 Apr 2013 02:59 AM PDT

I am recreating my database as I have had some errors on some of the imports. I also am creating an IDENTITY column, but when I use my typical bulk insert, it doesn't work anymore as in my files I am importing does not have a column for the IDENTITY column in the database. So I have read about the format file, and created it but am now getting this error and I do not know where to go from here:Msg 8152, Level 16, State 13, Line 1String or binary data would be truncated.The statement has been terminated.Here is the query for my table properties:[code]CREATE TABLE [dbo].[Names]( [ID] [int] IDENTITY(1,1) NOT NULL, [File] [nvarchar](50) NULL, [First Name] [nvarchar](50) NULL, [Last Name] [nvarchar](50) NULL, [Address] [nvarchar](75) NULL, [City] [nvarchar](50) NULL, [ST] [nvarchar](2) NULL, [Zip] [nvarchar](5) NULL, [Year] [nvarchar](4) NULL, [Make] [nvarchar](50) NULL, [Model] [nvarchar](50) NULL) ON [PRIMARY][/code]This is the format file:[code]<?xml version="1.0"?><BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <RECORD> <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="75" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="5" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="File" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="2" NAME="First Name" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="3" NAME="Last Name" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="4" NAME="Address" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="5" NAME="City" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="6" NAME="ST" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="7" NAME="Zip" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="8" NAME="Year" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="9" NAME="Make" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="10" NAME="Model" xsi:type="SQLNVARCHAR"/> </ROW></BCPFORMAT>[/code]And this is my SQL query for the bulk insert:[code]BULKINSERT [Names]FROM 'D:\Mail Files\Kaiser Files\Old\jfinley55 052411.csv'WITH(FIRSTROW = 2,FIELDTERMINATOR=',',ROWTERMINATOR='',FORMATFILE ='D:\Mail Files\format.xml')[/code]What am I doing wrong? Thanks for the help in advance!

SQL Server 2008 Transaction log backup issue...

Posted: 25 Mar 2013 05:49 PM PDT

Hello SQL Masters,Greetings!I would just like to ask regarding Transaction log backup. While backing up the T-log, is there still incoming transaction to write on log?Thank you and Best Regards,dev1.bohol@gmail.com

Windows Updates Broke My DB! :) .NET Questions!

Posted: 02 Apr 2013 02:37 AM PDT

Hello everyone,I'm wondering how everyone else is dealing with this issue or if our servers are just configured honkey...We have a SQL 2008 R2 server running on Windows Server 2008 R2. Everything was hunky dorey up till this past weekend when the Network Admin installed the latest windows patches and updates.Once he did this, my database mail stopped working and my Maintenance Plans stopped working (they were running under the SQL Agent start up account).The DB Mail gave the following error:Object Instance Not Set to an Instance of an Object.The Maintenance Plans said:Failed to acquire connection "Local Server Connection". Connection may not be configured correctly or you may not have the right permissions on this connection.So, the fix for this was reverting back to deprecated .NET versions. When he did the updates, he got rid of .NET 1.0, 1.1 and 2.0 and upgraded to 3.5 and 4.0 ... this somehow caused the problems in my DB.Once he put the old versions of .NET back, everything worked again... however, since the old versions of .NET are not supported anymore... they really want us to stop using them.My question is... how does this relate to my database mail and my domain account that starts up SQL Agent? I don't understand why a .NET version caused these problems and I would assume that this would cause problems on other people's servers? Is this a SQL Server problem or a Windows problem? And what is the remedy? Anybody know?Thanks!

encryptbypassphrase:decrypt or read values without passphrase

Posted: 01 Apr 2013 10:25 PM PDT

Just interested to know some details about encryption. If i have admin access to the sql server then i can access anything in it,even databases which are encrypted using master key can be decypted easily.But How i can decrypt the columns of a table without knowing the passphrase which are encrypted using ENCRYPTBYPASSPHRASE if i have admin access to sql server? If possible then how?

SQL Compatibility Mode Questions

Posted: 02 Apr 2013 02:41 AM PDT

Hello again!We have recently upgraded from SQL 2000 to SQL 2008 and you would think with my 10+ years experience that I would have known that the compatibility mode of the DB did not automatically change to 2008 when I configured the server. Be that as it may, I figured it out and changed it to 100.However, we apparently have queries and logic that is not compatible with 100. So, I had to change it back to 80. Is there a tool or an extended stored procedure that will identify all the things I need to change? It really isn't feasable for me to go through every single thing in the DB and check it. I'm really bummed about this cause now I can't use some of the new features of SQL 2008 :angry:And there isn't any way to set certain things in the DB to 80 and other things to 100 is there?Thanks!

Best Way to Speed Up Queries

Posted: 02 Apr 2013 01:55 AM PDT

Good Day,Please i need to speed up the runtime execution of queries on my online server, this database has over a million records in separate tables, and i've already indexed the tables using date fields. Please is there anyother way of speeding up my queries from my application online. I use Stored Procedures for querying the database. Its more of a business Intelligence Portal of viewing charts and information.ThanksTim

Differences between sql server 2008 and 2008 R2

Posted: 05 May 2010 11:52 AM PDT

I did some research to find the differences between between sql server 2008 and 2008 R2 but couldnt find much. I know in R2, BI is made more effecient but is it different from what it is present in 2008? Can someone please explain me the major differences between sql server 2008 and 2008 R2. Thanks in advance.

recovery model

Posted: 02 Apr 2013 01:44 AM PDT

why the system databases are in master-simplemodel-fullmsdb-simple

Subscriptions - Failure sending mail. 'Include Link' works, 'Include Report' does not.

Posted: 16 Feb 2012 07:03 PM PST

Hi EveryoneI'm hoping you can help me, I've followed most links on the net and decided to post.SQL 2008 R2Local SMTP service on server.Sending subscription'Include Link' works, 'Include Report' does not. Tried different rending formats.Error reported is The report server has encountered a configuration error. Mail will not be resent.Thank you

Server Hardware and Performance

Posted: 02 Apr 2013 12:38 AM PDT

Has anyone heard of using VeloBit to improve SQL Server performance? One of our domain administrators came across this one day and asked me if we would benefit from having it on our SQL Servers. It's a relatively low-cost add that, quite simply, seems too good to be true. Their site is [url]http://www.velobit.com/applications/databases/sql-server/[/url].The device claims to use caching to boost SQL Server performance by routing disk I/O to their solid state drive instead of the physical disk. I am not a SAN expert and we can't afford solid state drives for all our data and log files. Of course, without seeing any downside to it, I remember the old adage "If something looks too good to be true, it probably isn't true". I would like some opinions on what others think about it.Here's our current SAN configuration:It's an HP4300 with 15K spin drivesThe total capacity is currently 7 TBIt has 4 GB controller cacheThe connection is 2 GB copper from SQL Server to the SANWe have a few SQL Servers, but here's the one we're considering:SQL Server 2008 SP32 4-core HT CPUs with 144 GB RAM eachI already know we're lacking good design in many locations. That's a separate area that's being addressed...slowly.I'm not looking at having this replace any existing tuning or optimization efforts, but am looking for some feedback from anyone who either knows about the product or sees something in their product that I don't.Thanks very much.

MSSQL cluster failed will mscs validation errors

Posted: 02 Apr 2013 12:26 AM PDT

Hi,When i was installing cluster mssql server 2008 r2 on win 208 server, sql installation is failing due to MSCS Cluster validation errors.After that i have run the cluster validation on failover cluster manager and found the below errors.Failover Cluster Validation Report:Cluster Configuration Failed Inventory Success Network Success Storage Success System Configuration Failed Name Result Summary Description Cluster Configuration : Validating that Network Load Balancing is not configured on node XXXX02.Validating that Network Load Balancing is not configured on node XXXX02.An error occurred while executing the test.Failed to connect to the service manager on 'XXX02'.The RPC server is unavailableValidate Cluster Service and Driver Settings:An error occurred while executing the test.Failed to connect to the service manager on 'XXX02'.The RPC server is unavailable.The issue seams to be Firewall issuse so i ask wintel to look into it.MY DOUT IS:Please confirm mean while can i start installing the SQL SERver by skipping the validation errors on MSSCS(using below command).Willl it effect any thing on the installing sql part.Setup /SkipRules=Cluster_VerifyForErrors /Action=CompleteFailoverClusterNeed suggestion...........on this....Many thanks,

Server Review

Posted: 01 Apr 2013 11:39 PM PDT

What are the things we need to consider for reviewing a windows server keeping SQL server as our point of interest.

What is a Staging table ?

Posted: 01 Apr 2013 09:17 PM PDT

What is a Staging table ?How to use it ?And When is it advised to use Staging tablesRegards,Arjun

Query in SSRS

Posted: 01 Apr 2013 09:15 PM PDT

there is duplication of field source column name when i saw in the dataset properties of my report for a particular field name ,can i rename it in the dataset property to the original value which is retrieved from the dataset.

Non Clustered Index

Posted: 01 Apr 2013 10:19 PM PDT

Hi,One of my query (simple update query) with clustered index utilizing 98%. And then i added non-clustered index on the same table then its utilizing 25% [non-clustered Index] and 75% [Clustered Index].Is it good thing????Because development team people again saying query is very slow.How to resolve this issue. Pl. help on this.Thanks.

Deadlock on update command

Posted: 18 Mar 2013 10:33 PM PDT

I have the following dead lock happening quite frequently at 3-4 times a day. The deadlock seems to happen on 2 things one being an index on the status column keylock hobtid=72057605790367744 dbid=9 objectname=dbname.dbo.orderha indexname=IX_status id=lock2189e7200 mode=S associatedObjectId=72057605790367744the other on the primary clustered keykeylock hobtid=72057602492792832 dbid=9 objectname=dbname.dbo.orderha indexname=PK_orderha id=lock1399f2200 mode=X associatedObjectId=72057602492792832DEADLOCK INFO:Node:1 KEY: 9:72057602492792832 (7900b77d3449) CleanCnt:2 Mode:X Flags: 0x1Grant List 1:Owner:0x00000001A34042C0 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:67 ECID:0 XactLockInfo: 0x00000002643C19B0SPID: 67 ECID: 0 Statement Type: UPDATE Line #: 1Input Buf: Language Event: [b]update orderha set status=2 where sessionid='7560129' and orderha=1[/b]Requested by:ResType:LockOwner Stype:'OR'Xdes:0x00000001A183B780 Mode: S SPID:64 BatchID:0 ECID:0 TaskProxy:(0x00000000D7EAC538) Value:0x118db7c0 Cost:(0/0)Node:2 KEY: 9:72057605790367744 (7a00de2866cc) CleanCnt:2 Mode:S Flags: 0x1Grant List 0:Owner:0x00000002E14CBCC0 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:64 ECID:0 XactLockInfo: 0x00000001A183B7C0SPID: 64 ECID: 0 Statement Type: SELECT Line #: 1Input Buf: Language Event: [b]select rsn from orderha where sessionid='7558101' and status < 3[/b]Requested by:ResType:LockOwner Stype:'OR'Xdes:0x00000002643C1970 Mode: X SPID:67 BatchID:0 ECID:0 TaskProxy:(0x0000000281984538) Value:0x42de2bc0 Cost:(0/456)Victim Resource Owner:ResType:LockOwner Stype:'OR'Xdes:0x00000001A183B780 Mode: S SPID:64 BatchID:0 ECID:0 TaskProxy:(0x00000000D7EAC538) Value:0x118db7c0 Cost:(0/0)Should I use a Lock hint to force a block rather then encounter a deadlock , such as UPDLOCK? or HOLDLOCK?

Converting to Float in ssis

Posted: 01 Apr 2013 08:38 PM PDT

I'm exporting Flat File to DB Table.In Flat File I have numeric column with some values has empty space.In Derived column I'm using " [Column 188] == " " ? "0" : [Column 188] " to make empty space as 0.then In data conversion I'm converting it to float b'coz destination column is Float.But I'm getting as errors as follows[Data Conversion [3495]] Error: Data conversion failed while converting column "Derived Column 2" (6895) to column "Copy of Derived Column 2" (6901). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".[Data Conversion [3495]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Copy of Derived Column 2" (6901)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of Derived Column 2" (6901)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (3495) failed with error code 0xC0209029 while processing input "Data Conversion Input" (3496). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

how to convert a MySQL date (linked server) to MS SQL?

Posted: 12 Feb 2013 02:01 AM PST

Hi, I'm stuck on converting a MySQL date field (on a linked server) to MS SQL datetime format. I've googled this, but am having trouble finding something useful (other than using OpenQuery, but that's not ideal for a large update command with joins between the MySQL and MS SQL tables).Here's an abbreviated version of my update command:UPDATE dbo.NCU_Apply_FromWebSET guardian1_attended_date = A_D.guardian1_attended_date from NCU_Apply_FromWeb NAWinner join mysql_apply...apply_2 A_D on A_D.uid = NAW.uidError message:Msg 8114, Level 16, State 8, Line 1Error converting data type DBTYPE_DBTIMESTAMP to datetime.Any suggestions?

Query takes long time

Posted: 01 Apr 2013 07:38 PM PDT

I have a stored proc which takes quite long time. I have seen the execution plan and I found that order by takes 44% of the total query cost. What are the actions that should be taken to improve the performance for sorting the data?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog