Wednesday, August 28, 2013

[SQL Server 2008 issues] Delete Vs Truncate difference on rollback

[SQL Server 2008 issues] Delete Vs Truncate difference on rollback

Delete Vs Truncate difference on rollback

Posted: 26 Aug 2013 11:06 PM PDT

I guess anyone who has read even basic SQL will be aware of this classy question. And most of the people have fell for the catch on the difference based on rolling back the transaction."Delete can be rolled back but Truncate cannot"Some time back i cleared it by experimenting that Truncate can also be rolled back, IF, it is contained inside the Transaction scope AND session is not closed. However my question is if the session is closed and we have full recovery model, then can we roll back the truncate from log ? I guess yes if the pages allocated for deletion by the truncate process are not yet overwritten by any other process (?). Please clarify.

Calculating AVG in mdx for reporting services 2008

Posted: 27 Aug 2013 06:19 PM PDT

Hello , I am new to MDX and would like to know how to calculate average in MDX . I tried several expressions but i failed .I have a ITSM cube . I would like to calculate average of incidents per month and for a group .Number of incident = [Measures].[Nombre incidents]Per month = [Date création].[Mois]Organised by the group = [Groupe affecté - Historique].[Groupe affecté].&[N1-Hotline] I am using SSRS . Thanks .Regards .My actual MDX is (without average calculation) :SELECT NON EMPTY { [Measures].[Nombre incidents] } ON COLUMNS, NON EMPTY { ([Date création].[Mois].[Mois].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Date création].[Année].&[2012] } ) ON COLUMNS FROM ( SELECT ( { [Groupe affecté - Historique].[Groupe affecté].&[N1-Hotline] } ) ON COLUMNS FROM [Incidents])) WHERE ( [Groupe affecté - Historique].[Groupe affecté].&[N1-Hotline], [Date création].[Année].&[2012] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGSThanks ...

SQL Server Services in the 2008 SQL Configuration Manager FAILED

Posted: 27 Aug 2013 08:30 AM PDT

I was told in my book to check to status of the SQL Server Services and to start it if its stopped. Well, when i click on SQL Server Services, it says: "The remote procedure call failed."I'm checking windows update to see if there is an update i need to install. I do have SS2012 on my computer as well. Everything seems to be running fine on it. I don't know if there might be some conflicting issues though.HEre were the books instructions:[quote]Start the SQL Configuration Manager utility in the Configuration Tools folder, located in the Microsoft SQL Server 2008 program group. In the left pane, click SQL Server Services. In the right pane, examine the status of the SQL Server (SQLEXPRESS) service. If the status is Stopped, right-click the service, and then click Start. Wait for the status to change to Running, and then close SQL Configuration Manager.[/quote]When i right click there is no start or stop and refreshing it means no difference. Any advice?

Update even if there is not a match

Posted: 27 Aug 2013 01:13 PM PDT

Hi ProfessionalsI am running an update to update the columns and tag the licensable column as either, Licensable, Non Licensable or Unknownproblem is when there is not a match it does nothing, is there a way to also update the licensable column to unknown if there is no match[code]UPDATE dbsource SET softwaremanufacturer = dbref.Amended_SW_Manufacturer, productname = dbref.Amended_Product_name, productversion = dbref.Amended_Product_Version, licensable = dbref.category FROM dbo.newtable dbsourceINNER JOIN ( SELECT Raw_SW_Manufacturer,Amended_SW_Manufacturer, Raw_Product_Version,Amended_Product_Version, Raw_Product_Name,Amended_Product_Name,category FROM datalookuptable GROUP BY Raw_SW_Manufacturer,Amended_SW_Manufacturer, Raw_Product_Version,Amended_Product_Version, Raw_Product_Name,Amended_Product_Name,category) dbref ON dbref.Raw_SW_Manufacturer = dbsource.softwaremanufacturer and dbref.Raw_Product_Version = dbsource.productversion and dbref.Raw_Product_Name = dbsource.productname[/code]

What is @Dummy ?

Posted: 16 May 2013 12:49 AM PDT

Hello all! I've been assigned to deconstruct and document a rather large Stored Procedure (SQL 2008). This procedure simply declared about a dozen of other procedures with I will need to anaylize and document. I'm coming into an environment where a handleful of other developers wrote, programmed, and left and not a damn thing was documented. So, I along with the help of someone else, are decontructing it all and documenting it. Anyway, the SP starts off like this:ALTER PROCEDURE [dbo].[The Name of this very SP](@Dummy varchar(20))asExec [SP #1] 1Exec [SP #2] 1...and so on.I have two questions. 1) What does [i]@Dummy [/i]do?2) What do the "1" indicate after firing off each SP?Thanks in advance for your help!!

DATALENGTH much faster than LEN in where clause with varchar(max)

Posted: 27 Aug 2013 10:26 AM PDT

hy,I read a lot of previous post, but I could not find why the running time is so different,making some changes to the "where" conditionI need to delete some record from a table with an exact match.here an exampleCREATE TABLE TO_DROP_BIG_HTML( [BIG_HTML] [varchar](max) NULL) ON [PRIMARY]select count(*), avg(DATALENGTH(BIG_HTML)) from TO_DROP_BIG_HTML-- 5000, 16215here the t-sql with duration estimation declare @start_time datetime select @start_time = getdate()-- ORIGINAL QUERYdelete TO_DROP_BIG_HTML where BIG_HTML = 'NOREC' select DATEDIFF(ms, @start_time, getdate()) -- 1366 select @start_time = getdate()-- adding LEN()delete TO_DROP_BIG_HTMLwhere LEN(BIG_HTML) < 20and BIG_HTML = 'NOREC' select DATEDIFF(ms, @start_time, getdate()) -- 43 select @start_time = getdate()-- replacing with DATALENGTH()delete TO_DROP_BIG_HTMLwhere DATALENGTH(BIG_HTML) < 20and BIG_HTML = 'NOREC' select DATEDIFF(ms, @start_time, getdate()) -- 26every query in the Exection plan does a Table Scan cost 70-79%I can understand that adding more condition in the where I improve performance, but I don't understand the big difference between DATALENGTH and LENRemarks that the original table has 180k record and 5 gb data:43 vs 26 ms become huge more than 10 min vs 12 sec can you explain me why?thank youL

Unable to open Step output file

Posted: 27 Aug 2013 05:19 AM PDT

Hi I am running sql script inside job.But sql is not saving the results in the file which i specified('Unable to open Step output file').I tried same thing on different server, it works fine.Both are are 2008 R2.Is there any server level setting that i have to look at.On both servers i logged in as sa.Any ideas why is it working on server1 ,but not on server2?Tnx advance

Replication without primary keys

Posted: 27 Aug 2013 07:30 AM PDT

Hi,My tables do not have primary keys and as I understand it, transactional replication requires primary keys so my question is: Has anybody found a 3rd party solution to implement Replication when your tables do not have primary keys? We have over 3,000 tables and it would not be possible to alter them to add primary keys so I was just wondering is there another way to implement this.Thanks in advance, :-DIsabelle

Using a parameter to select dates from an Oracle Database

Posted: 27 Aug 2013 08:06 AM PDT

I have a table with two fields: Fromdate and todate that I change every month to the current month value.For example this month fromdate is '2013-08-01' and todate is '2013-08-31' Then when I run the program from a job, it will alread have the date range in the selection process. This is what I do in Teradata which works: DECLARE @tempfromdate AS varchar(10)DECLARE @fromdate AS varchar(10)DECLARE @temptodate AS varchar(10)DECLARE @todate AS varchar(10) select @tempfromdate = fromdate, @temptodate = todate from tbl_fiscperset @fromdate = CONVERT(VARCHAR(10), @tempfromdate, 111)set @todate = CONVERT(VARCHAR(10), @temptodate, 111)DECLARE @SQL VARCHAR(MAX) SET @SQL = ' SELECT *FROM OPENQUERY (TERADATA,''select * from teradata tablewhere reportdate between between ''''' + @fromdate + ''''' and ''''' + @todate + ''''''')'EXEC (@SQL)***EDIT***sorry, I should have added this to the post.This is what works in Oracle with a hard coded dates:where to_char(RPT_DATE,''''yyyy-mm-dd'''') between ''''2013-01-01'''' and ''''2013-08-31''''I can't get the parameter to work.Any thoughts?Thanks

Reporting services

Posted: 27 Aug 2013 02:53 AM PDT

So we have some reports (which I didnt develop) that work fine if I run them on the server reporting services is configured on, but if they are ran from my machine, or from any other box, they fail with the error Cannot create a connection to data source If I go into the reports using this data source, and click on the history tab it says - Credentials used to run this report are not storedThe data source is currenly using windows integrated security. Is the only way to get around this error to use "credentials stored securely in the reportserver" and specify an account to use?

Finding Smart Hierarachical order based on interdependencies

Posted: 27 Aug 2013 07:38 AM PDT

I have a table with two columns, DealID & NetOutDeals (consider this as foreign key to DealID). I am trying to find the Order in which I can execute my deals. [code="sql"]--Create TableCREATE TABLE [DealExecutionOrder]( [DealID] [int] , [NetOutDeals] [varchar](30)) ON [PRIMARY]--Inserting Sample DataINSERT INTO [DealExecutionOrder]VALUES (1, '3,10,12'), (2, '3,5,7'), (3, '0'), (4, '6'), (5, '3'), (6, '0'), (7, '0'), (10,'0'), (12,'5')[/code]Here are the business rules: 1. value 0 in NetOutDeals means no DealID dependencies. I can straight away execute DealID with 0 value in NetOutDeals first. 2. From here, I can execute a deal only if all deals in the corresponding NetOutDeals are executed. For eg: DealID 1 can be executed only after 3, 10, 12 (again 12 can be executed only after 5 (again 5 can be executed only after 3)). I need to come up with the hierarchical order of DealID to process my deals. Order does not matter at same level. Eg: 3, 6, 7, 10 can come in any order but should be before next immediate deals that depend only on this list. So output DealID order should be like this: (6,10,7,3), (5, 4), (2, 12), (1)Order does not matter within the brackets. Can someone help me to achieve this?

Difference of two consecutive datetime rows

Posted: 20 Aug 2013 02:39 AM PDT

HI,I have the following tableEntryID DateInserted InsertedBy1776285 2013-06-03 07:46:38.340 5921776286 2013-06-03 07:47:47.677 5921776287 2013-06-03 07:48:17.367 5921776288 2013-06-03 07:49:08.750 5921776289 2013-06-03 07:49:08.750 5921776290 2013-06-03 07:49:45.177 5921776291 2013-06-03 07:54:54.290 5921776292 2013-06-03 07:57:11.703 5921776293 2013-06-03 07:57:35.930 831776294 2013-06-03 07:58:33.843 831776295 2013-06-03 07:58:36.293 5921776296 2013-06-03 07:58:54.853 831776297 2013-06-03 07:59:06.523 5921776298 2013-06-03 07:59:27.633 831776299 2013-06-03 07:59:46.383 831776300 2013-06-03 08:00:02.020 5921776301 2013-06-03 08:00:04.390 831776302 2013-06-03 08:00:29.590 5921776303 2013-06-03 08:01:00.240 5921776304 2013-06-03 08:01:12.943 831776305 2013-06-03 08:01:31.150 831776306 2013-06-03 08:01:31.990 5921776307 2013-06-03 08:01:50.373 831776308 2013-06-03 08:02:10.840 831776309 2013-06-03 08:02:30.243 831776310 2013-06-03 08:03:03.737 831776311 2013-06-03 08:04:08.750 5921776312 2013-06-03 08:04:14.723 831776313 2013-06-03 08:04:27.033 5921776314 2013-06-03 08:05:26.617 831776315 2013-06-03 08:05:26.617 831776316 2013-06-03 08:05:38.527 83Now I want to get the difference between 2 consecutive dateinserted col in hours or mins into another column say Time diff grouped by on the inserted by col since i want total time taken by each person inserted.like as belowEntryID DateInserted InsertedBy TimeDiff1776285 2013-06-03 07:46:38.340 592 diff(2013-06-03 07:47:47.677-2013-06-03 07:46:38.340)-- should be the output of this col either in mins or hours.Also if the difference is > than 3 mins I want only 3 mins as the output.1776286 2013-06-03 07:47:47.677 5921776287 2013-06-03 07:48:17.367 5921776288 2013-06-03 07:49:08.750 5921776289 2013-06-03 07:49:08.750 5921776290 2013-06-03 07:49:45.177 5921776291 2013-06-03 07:54:54.290 5921776292 2013-06-03 07:57:11.703 5921776293 2013-06-03 07:57:35.930 831776294 2013-06-03 07:58:33.843 831776295 2013-06-03 07:58:36.293 5921776296 2013-06-03 07:58:54.853 831776297 2013-06-03 07:59:06.523 5921776298 2013-06-03 07:59:27.633 831776299 2013-06-03 07:59:46.383 831776300 2013-06-03 08:00:02.020 5921776301 2013-06-03 08:00:04.390 831776302 2013-06-03 08:00:29.590 5921776303 2013-06-03 08:01:00.240 5921776304 2013-06-03 08:01:12.943 831776305 2013-06-03 08:01:31.150 831776306 2013-06-03 08:01:31.990 5921776307 2013-06-03 08:01:50.373 831776308 2013-06-03 08:02:10.840 831776309 2013-06-03 08:02:30.243 831776310 2013-06-03 08:03:03.737 831776311 2013-06-03 08:04:08.750 5921776312 2013-06-03 08:04:14.723 831776313 2013-06-03 08:04:27.033 5921776314 2013-06-03 08:05:26.617 831776315 2013-06-03 08:05:26.617 831776316 2013-06-03 08:05:38.527 83Thanks in advance.

index dmv stats

Posted: 27 Aug 2013 02:28 AM PDT

HiI have an alert set up for 'Number of unused indexes' set to a number above a certain threshold.Our servers get rebooted every month therefore flushing out the stats from the dmv's that I use to trigger these alerts.I suppose this is a 'how long is a piece of string' question, but is 1 months worth of stats going to give me a decent idea of my index usage - i don't feel that it is but i welcome peoples opionions....

will not using the statement terminator ';' break code in future?

Posted: 26 Aug 2013 07:21 PM PDT

i've been searching for some definitive (or as definitive as it can be, since we dont all work for Microsoft) about whether SQL that doesn't have the ';' terminator will break at the point they decide to make it mandatory.[url]http://www.sqlservercentral.com/Forums/Topic1261877-392-1.aspx[/url][url]http://www.linkedin.com/groups/Future-deprecated-feature-Must-terminate-72017.S.198380247[/url]this next link mentions 'Not ending Transact-SQL statements with a semicolon' is not supported in a future version[url]http://technet.microsoft.com/en-us/library/ms143729.aspx?goback=%2Egmr_4764645#![/url]i'm sure it wont but i'd prefer to know ;)

Fastest way to read a 600MB table (in memory)

Posted: 27 Aug 2013 04:32 AM PDT

A few days ago I came across this interesting problem:I cannot get more than 15-19mb/s is there any limitation that I need to know about?The query is a simple select (not doing sorting) and the database is read-only : "select * from tablename"Tried local and remote and still get the same throughput therefore is not a network problem.Launching multiple queries in parallel bring the total time down almost linearly as I increase the number of parallel connections (which bring different sections of the data).Finally the table fits entirely in memory therefore no DiskIO is involved either.If someone has any insight, please chime in.

Maintenance Plan Job Proxy Failures

Posted: 27 Aug 2013 03:30 AM PDT

I have a problem that has been kicking my butt for a couple of days and I just can't find how to fix it.Server:SQL Server 2008 R2 Std Ed 10.50.2500 SP1Windows Server 2008 R2 StdError:MessageExecuted as user: MyServer\_sqlagentservice. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 8:45:51 AM Could not load package "Maintenance Plans\MaintenancePlan-Backups" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E4D (Login failed for user 'MyServer\_sqlagentservice'.). The SQL statement that was issued has failed. Setup:1. Created maintenance plan to run backups.2. Created a Credential assigning the identity to the local user account that runs the SQL Server Agent service (MyServer\_sqlagentservice)3. Created proxy "JobAgentProxy" using the above credential4. In the maintenance plan job, I set the step Run As to "JobAgentProxy".When I run this job, it fails. I have other servers (all 2005) that use this proxy setup with no problem what-so-ever!Any help here would be greatly appreciated.:Greg

NEWID()

Posted: 26 Aug 2013 07:54 PM PDT

Hi All,I have to know which algorithm is used in NEWID() function or to know how it works internally?Regards,Ningaraju N E

OMR into SQL Server Reports.

Posted: 27 Aug 2013 02:06 AM PDT

Does any one know how to include OMR (Optimum Mark Recognition) into sql server reports?Thanks in advance

Sum

Posted: 26 Aug 2013 10:22 PM PDT

HI,I have table like the given belowgrade losal hisal1 700 12002 1201 14003 1401 20004 2001 30005 3001 9999so, i need the Total of that cols like given below grade losal hisal1 700 12002 1201 14003 1401 20004 2001 30005 3001 9999Total 8304 17599plz help me

calculaing percentage Increase or Decrease

Posted: 26 Aug 2013 07:28 PM PDT

Hi,I need help for calculaing percentage Increase or Decrease in Price of some Product for last two years.I have some table gg_Price_TDate Product Price1-01-2013 Bingo 50.07 2-01-2013 Tingo 52.07 3-01-2013 Zingo 56.07 . . .. . .27-08-2013 Sigo 59.07It is having different price for each day and I want to know with what percentage this price differs every year, If we take it for last two years 2012 and 2011

Data Sync between two Applications on different Database servers and sync should be done on set up tables data on both sides

Posted: 26 Aug 2013 09:53 PM PDT

Hi Everyone,I am in a situation where two applications are interacting from two DB servers and i need to sync the data of set up tables on both Application sides.Can anyone provide a feasible low cost approach.Thanks in advance.:-)

Duplicates

Posted: 26 Aug 2013 11:29 PM PDT

When you have a temp table being populated. Can you join on that table so you don't get duplicates? I tried using distinct but even if the time stamp is off by just a little, it will add it because it is distinct.

SQL Agent Job Scheduling

Posted: 27 Aug 2013 12:19 AM PDT

Let's say we have an instance of SQL Server (the default instance) and two PROD databases within in that default instance, DB1 and DB2. The job scheduler is global to the instance, so will it not work if I have a Index rebuild scheduled (SQL Job) for DB1 at 1:00am and a Index rebuild scheduled for DB2 at 1:00am, those two jobs should not interfere with each other, correct?

DeNormalize Key Value Hierarchy table

Posted: 26 Aug 2013 09:48 PM PDT

Hi All,I have an hierarchy table as below which stores the key data.And the value for this key will be stored in another table.And the Value can be multiple i e Key-Value 1:n.And this values can be associated with an Item.Now I have to De normalize this table so that I can use the same in Query builder search?What is the best solution and design?ID Name Parent ID Category ID 19 Identifier NULL 320 Type of Classification 19 321 Entry 19 322 Title NULL 323 Language NULL 324 Description NULL 325 Keyword NULL 326 Structure NULL 327 Aggregation level NULL 328 Version NULL 429 Status NULL 430 Contribute NULL 431 Role 30 432 Entity 30 433 Date 30 434 Identifier NULL 535 Type of Classification 34 536 Entry 34 537 Contribute NULL 538 Entity 37 539 Date 37 540 Metadata schema NULL 541 Language NULL 5ThanksNingaraju

CTE in MERGE

Posted: 27 Aug 2013 12:01 AM PDT

Can we use CTE in MERGE statement ?If possible then please post a sample code of that...
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog