Tuesday, March 26, 2013

[SQL Server 2008 issues] Enabling Trace Flag T1118

[SQL Server 2008 issues] Enabling Trace Flag T1118

Enabling Trace Flag T1118

Posted: 25 Mar 2013 04:17 PM PDT

Hi, We are using Sql server 2008 R2 express. We heard enabling T1118 is good for tempdb contention.Somewhere it said its not much help sql server 2005 aftwrwards. We are thinking about enabling it. Is there any disadvantages of enabling T1118?Thank You

calculating period of time

Posted: 25 Mar 2013 06:37 PM PDT

hi, and here comes my next question :-)i need to calculate the sum of quantity by period of daysfrom beginning of time till today without writing a sum for each period of days.i know i need to use sum and time diff to calculate the quantity but how do i tell the table, give me the first 5 days and then the next 5, etc etci saw something that is called interval (which of course i have no clue how to use) or maybe i should build a loop (which of course i dont know how to do in sql either :w00t:)what would you recommend? :-)

calculating time difference

Posted: 24 Mar 2013 08:20 PM PDT

hi,I have a table ID UserID Purpose DateCreated1 500 login 2013-03-24 14:39:43.2732 501 login 2013-03-24 14:39:43.2773 502 login 2013-03-24 14:39:43.2774 503 login 2013-03-24 14:39:43.2775 500 logout 2013-03-24 14:44:43.2806 501 logout 2013-03-24 14:44:43.280I need to calculate the time spent on total for each user idthe result needs to look something likeUserID TotalTime500 25501 25502 25503 25i've been trying a bunch of thing, and i think i need to make cases for the login/logout but it is not working...this is what i wrote that wrong in so many levels :w00t:select userid, sum(datediff(mi,loginn,logoutn)) as TotalTime,case when Purpose like '%login%' then DateCreated else 0 end as [loginn],case when Purpose like '%logout%' then DateCreated else 0 end as [logoutn]

how to copy all the scripts one sql server database server to another sql server database

Posted: 25 Mar 2013 05:06 PM PDT

Dear all,Hope you doing...,actually i am changing all my tables from 10.101.__.17.dailyreps database name to another server 10.101.--.23.daily reps.i copied all the tables successfully,now i am having multiple scripts in my database of source i want move all the scripts into my destination data.can you please how it is possible all at a time other than manually copying and paste one by one.?

Inserting child between descendants using HierarchyID datratype

Posted: 25 Mar 2013 06:10 PM PDT

Hi,We are using Sql server 2008 R2 express. We are trying to use HierarchyID datatype to represent organizational structure. We have to insert a child by using parent id. I found this query [code="sql"]declare @root hierarchyid,@last_id hierarchyid,@id hierarchyidselect @root=orgnode from [adventureworks].HumanResources.EmployeeDemo where employeeid=1 SELECT @last_id = MAX(orgnode) FROM [adventureworks].HumanResources.EmployeeDemo WHERE orgnode.GetAncestor(1) = @root SELECT @id=@root.GetDescendant(@last_id, NULL)INSERT into [adventureworks].HumanResources.EmployeeDemo values(@id,12,'seh','Application Developer',1905-02-12);[/code]But this inserts a node at end in particular level and i sometimes want to insert node in between other nodes in that level.How can i achieve that?

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

Creation of auto Increment row

Posted: 25 Mar 2013 05:42 PM PDT

Hi,I am using Sql server 2008 R2 express.I want a column [b]id[/b] with auto incrementing value in my table.But i don't want to use built in Auto increment property because I want to delete a row and then insert row in middle etc and Built in Auto increment property will cause some restriction on operations.So i want to increment that value during inserting a row manually.I thought i can do like this [code="sql"]declare @root int select @root=max(id)from [AdventureWorks].dbo.seh_test insert into AdventureWorks.dbo.seh_test values(@root+1,'seh')[/code]Is there any way to do same thing using single query? Or any optimizations for my query?Thank you

select 200 column out of 250 column

Posted: 25 Mar 2013 04:57 PM PDT

If My table consist of 250 colum and i want to select 200 column so it is possible to write query to select 200 column without write all column name in select statement

certificate error

Posted: 25 Mar 2013 05:32 PM PDT

Iam unable to open the reporting services url as getting getting error as There is a problem with this website's security certificateplease suggest how to resolve this .My windows is win2008 r2

Assistance with this query

Posted: 25 Mar 2013 10:01 AM PDT

I'm a bit new to SQL, and I'm attempting my first real project. Currently, my setup is: SQL Server 2008 R2 with a linked SQL Server 2000.A couple points that I'm having issues comprehending:[li]Each record in the "employee" table has it's own ACTIONDATE, which allows more than one record for the same person, so getting one result for one person is appearing difficult. EMPLOYEEIDs are unique to the user, so how would I word the query to grab the most recent ACTIONDATE for each employeeid?[/li][li]Multiple joins are causing my queries to run for over 3 minutes. Each table has 100k records, minimum. How would I optimize a query with multiple joins with big tables? Or is there a better way to grab this data?[/li]I'm attempting to make a hierarchy from a SQL Server that houses are workforce software and place it into my SQL Server 2008 R2 to be utilized in another application. I want to grab all active employees (ENDDATE IS NULL) with the most recent ACTIONDATE of each active EMPLOYEEID.The query I'm attempting to run is:[code="sql"]SELECT DISTINCT employee.EMPLOYEEID, employee.FIRSTNAME, employee.LASTNAME, employee.STARTDATE, employee.EMAILADDRESS, org.ORGANIZATIONID, job.JOBTITLEIDFROM linkedServer.linkedDB.dbo.AUEMPLOYEE as employee RIGHT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEORGANIZATION as org ON employee.employeeid = org.employeeid RIGHT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEJOBTITLE as job ON employee.employeeid = job.employeeidWHERE employee.ENDDATE IS NULL[/code]

Import .txt file into SQL Table - Not working!

Posted: 25 Mar 2013 01:37 AM PDT

Hi there, I would really appreciate it if someone could help me. At the moment part of our customer information is held in Lotus Approach and part in SQL. I have exported the Approach information to Excel and am trying to import this into SQL. I have also saved the Excel sheet as a .txt file.My issue is that three of the Approach fields are free text boxes, with lots of carriage returns. I have tried the following query to enter the information into SQL:BULK INSERT livedev.dbo.CarriageReturnsFROM 'C:\Users\janitor\Desktop\TestData\Test.txt'WITH ( FIELDTERMINATOR = '\t',ROWTERMINATOR = '\r',FIRSTROW=2 ) This works fine on the simple fields but the large text fields are carrying over to the next row and creating lots of errors.I have read lots of forums but just can't seem to find anything that works. I should have 437 records imported into SQL, but looking at a Hex editor - my data is showing as having 1124 lines because of the carriage returns.It must be something to do with the ROWTERMINATOR and it doesn't know where the row ends - but I can't find a solution for this!ThanksCaroline

Trace/Log Transaction Rollback

Posted: 25 Mar 2013 08:38 AM PDT

Hello,I am looking for a good (if any) way to log the event of transaction rollback. I'am on SQL2008 R2 Standart.Situation:Transaction is opened from .NET application. Request timed out. Transaction (in SQL) has been rolled back by the application. No errors happened on the SQL side, so I have nothing to log in the CATCH.Is there any way to log the event of the rollback itself (like we do for dead locks for example)? With or without details (e.g ID, time).Thanks,Greg

Propagate view changes.

Posted: 25 Mar 2013 11:20 AM PDT

For security reasons, we have exact same view ( with same name) in different schemas based on different clients. Now if we have 100 clients, a change in one view or a if an new view is added to that that schema, they need be propagated to all other views in other schemas. I was wondering about a convenient yet easy approach to reflect these changes in a one view and propagate them to all the others schemas. Thanks.

Trace - to Capture all or most queries

Posted: 18 Mar 2013 01:49 AM PDT

A. Are black box and defaula traces same?B. Is it possible to capture all the queires in the black/default trace, if it does not degrade sql performance.

Lock only one row in update (lock a row)

Posted: 24 Mar 2013 09:40 PM PDT

Hello!!!The question is (if it is possible without any workaround, like add a state column)How can i achieve this... I understand that I can't lock a row with select statement, but can I achive this:--TRANS 1 while a transation is executing an update statement modifing one row, lock this row. Nobody can select this row while transaction is running.--TRANS 2 while TRANS1 is running I can update other ROWS (not row in TRANS 1)[b]--example 1[/b]--TRANS 1 => update table set field = 1 where id = 1 (LOCK THIS ROW ONLY THE ROW!!!)--TRANS 2 => select * from table where id = 1 =====> MEANWHILE TRANS 1 IS RUNNING, HAS TO FAIL BECAUSE TRANS 1 is updating...[b]--example 2[/b]--TRANS 1 => update table set field = 1 where id = 1--TRANS 2 => select * from table where id = 2 =====> OK ![b]--example 3[/b]--TRANS 1 => update table set field = 1 where id = 1--TRANS 2 => update table set field = 1 where id = 2 =====> OK ! no one is locking this row!!!* I disable lock escalation on the table (LOCK_ESCALATION = DISABLE)* No cluster index on the table.Hope you understand, i try a lot of query hints but noone get the results I spected (rowlock, xlock) (updlock)when I use hits I test the lock results with use masterSELECT * from sys.dm_tran_locks where request_session_id in (55,60) order by request_session_idand see a PAGE lock for the resuouce, trans id 55 and 60 both takes differentes rows,for a more especific example i post the las example i try.TRANS 55[quote]begin transactionupdate reque_pa with (rowlock) set MINIMO = 2 WHERE CEREQ = 1 --with (rowlock,updlock) WAITFOR DELAY '00:00:10';commit transaction[/quote]The waitfor is to simulate a more long transaction to force the lock.TRANS 60[quote]begin transactionSELECT * FROM Reque_pa with (rowlock) WHERE CEREQ = 2commit transaction[/quote]hope you can help me, thanks in advance.!!!

Database Diagram printing problems

Posted: 29 Oct 2010 03:08 AM PDT

Greetings all; I have seen this issue/problem for a number of releases now and I have not been able to resolve it. I will admit I may be missing something here. When I design a database I typically create subject area database diagrams. Something I've done for quite some time and is essential when designing complex data structures. The problem that I have is printing the diagrams. I will spend a fair amount of time to insure I have my tables lined up and not overlapping the page. However when I go to print my entire ER diagram will shift causing tables to overlap pages. This is so annoying! 1) Has anyone else experience this? 2) Does anyone have a resolution for this? 3) Is there something that I'm doing wrong? or is this a known issue?Thanks in advance.Kurt

Validating trace file events

Posted: 08 Mar 2013 05:09 AM PST

Hello,If I have a trace running and populating some *trc files..My question is how can I validate that i am indeed tracing the correct sp_trace_setevent filters I have set ?Are there dynamic tables where I can fetch the information on what is being traced?Thank you !JR

How to add more partitions to existed table ?

Posted: 31 Dec 2011 02:54 AM PST

I have a table that contains records of transactions with ID column is primary keyI use partition follow ID column, each partition have 1 million records.CREATE PARTITION FUNCTION [pfTBLTRANS_ID](int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 7000000, 8000000, 9000000, 10000000)CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP11])But now I have more records with IDs that are greater than 11.000.000. So how can I add more partitions to this table ?Thanks

Concatenation help

Posted: 25 Mar 2013 05:48 AM PDT

Hi all,I'm having a brain fart. What is the best way to achive the below results?name LabMICROSOFT VISUAL STUDIO .NET 101MICROSOFT VISUAL STUDIO .NET 118MICROSOFT VISUAL STUDIO .NET 256MICROSOFT VISUAL STUDIO .NET 367MICROSOFT VISUAL STUDIO .NET 51MICROSOFT VISUAL STUDIO .NET 901PYTHON 256PYTHON 376PYTHON 256name lab(s)MICROSOFT VISUAL STUDIO .NET 101, 118, 256,367,51,901PYTHON 256PYTHON 376, 256

Urgent - Reporting services migration and upgrade to 2008 R2

Posted: 25 Mar 2013 01:03 AM PDT

Hello,We have a task to move reporting services 2005 instance to new servers with 2008 R2 version.This is what I did so far:1. Since web and database servers are different, I first installed sql server on database server. 2. Installed reporting services on web server. 3. Restored ReportServer and ReportserverTempdb to the new 2008 R2 database server.4. Configured reporting services and selected resored reportserver and reportservertempdb on the new server.I can access the URLs and reports now.This is my first time touching reporting services. Our reporting services guy in the team is gone and we have a very tight deadline to deliver test, stage and prod environments in next 3 weeks.We have over 250 reports. From the collected info, I believe that there is no custom configuration.Our Development team has given us estimate of 2 manual months offshore and 80 hours onsite to convert all reports to 2008 R2. First of all, we don't have that kind of time and second, I am trying to find out what is left to do now and whether it is worth paying so much money to development.I searched the forums and articles but I am not clear on when to conclude that the migration is done. Do I need to open every report in BIDs and save it in order to upgrade it? If yes, is there any script or utility availabel to do that?Thanks,Nikita

Importing 00:00 into Table

Posted: 25 Mar 2013 03:18 AM PDT

So I've created a query that adds times to get a certain calculation. The end result I would get a time of 09:26, which means 9 minutes and 26 seconds. I can easily use the query directly into the report, but I'm wanting to dump the results into a table for historical reasons.However, I'm running into an issue. When I attempt to import the results into a table, the import wants to change my 09:26 to an actual date and time, which I dont want.What data type should the column be in the table so I'm allowed to import the 09:26 as it is?Thanks!

SSMS Performing Slow over remote connection

Posted: 25 Mar 2013 05:19 AM PDT

Hi Mates,One of the instance is hosted in a cluster node with 128GB of ram and 80 processors allocated to this instance...And last week, we had an issue with temp DB as it was full.We increased the drive space over the weekend.Now whenever we connect this instance from other server through remote SSMS, we see, refreshing the object including connecting to this instance is very very slow...It takes more than 10 sec. Initially this was not the case. Even hear from end user that the application is performing gradually slow.I check the network, fregmentation, settings across this instance, and all looks good.Any suggestions please? Thanks

deploying a cube

Posted: 25 Mar 2013 03:48 AM PDT

Hiwhat are my options for deploying a cube?is there an interface users can use to browse the cube as I can in BIDS?I know excel is an option but not quite as user friendly as the cube browser.I am new to ssas BTW and i am just looking at my options.Thanks in advance

Trigger For External Server Insertion

Posted: 25 Mar 2013 03:35 AM PDT

Hi All,Please i need an answer to this question. I have two servers, one locally and one hosted, i normally upload data to the online one using data export wizard. but the local database is now connected to an application that adds data to it almost every second. This is what i want to do1. write a trigger for insert that will insert the data to the hosted server(database) anytime the local database table is added with data(pardon my English)2. Is it posible to do upload data from a local database to an online database through a trigger, a sample script will guide me through.ThanksTim

Documenting RDL files

Posted: 25 Mar 2013 03:34 AM PDT

I'm working in SSRS2008 R2 and I have several projects, which in turn have several report definitions held within them. Each rdl may have many datasets, tablix, matrix definitions detailed within it. My problem is documentation! I need to document all the datasets, tablix/matrix names, stored procedures etc Is there an easy way of extracting this information and creating either an excel workbook using VBA, or detailing them back into a SQL2008 table that I can then use SSRS to produce the report?I have read a similar post that suggests using the reportserver database to get this information from, but if the rdl hasn't been deployed yet it won't be in the database. Any help would be much appreciated as it will save me going through over 150 rdl's manually and writing down the information. Thanks

SQL 2005 - Get Deadlock information

Posted: 25 Mar 2013 02:56 AM PDT

IN sql 2005, I use DMV to get the number of deadlocks per sec. How can i get those deadlock's details? I havnt enabled the Traces or run profiler. These deadlocks already happend and i suspect if we could get the necessray details

Increase values with update statement

Posted: 25 Mar 2013 03:18 AM PDT

Hi everyone I hope somebody can help...I have a table in SQL with the following columns: FinancialYear (varchar), Site (varchar), ProductRange (varchar), ProductName (varchar), PlannedSales (decimal). I want to increase the PlannedSales in the table for each of the sites but by a different amount. For the North Site I want to increase PlannedSales by 0.05% and for the South Site by 0.19%. All the other sites should keep their current PlannedSales totals. The increase will need to be spread across all product ranges and actual products ie all products should increase their PlannedSales by 0.05% in the North Site. Thanks in advance. BO

SQL Server Install Location

Posted: 25 Mar 2013 02:46 AM PDT

Can the SQL Server Binaries (2005,2008,2012) be installed in a location other than the C: drive.How can I specify the location to install the SQL Server on a drive of my choosing.

Truncate table

Posted: 21 Mar 2013 01:35 AM PDT

When we truncate the table does the indexes made on them exists in memory??Can we check whether they exist or no???

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?

Assistance in converting INT into date.

Posted: 25 Mar 2013 12:16 AM PDT

I have a query that has two date fields that are Int....20050307. I need to convert them to date fields. 03/07/2005 I can not seem to get them to convert. Can anyone out there offer me some assistance please. The fields are fact.StartDtID and fact.EndDtId. See query below and Thank you in advance. SELECT dimUser.EmpFullName1 [User Name],dimUser.MgrEmpFullName1 AS Manager,dimUser.PrimaryJobName AS [Primary Job], dimUser.EmpNo AS [User Number], CONVERT(VARCHAR(10), fact.StartDtID, 101) [Start Date], CONVERT(VARCHAR(10),fact.EndDtID, 101) [Completion Date],dimUser.PrimaryOrgCode, dimUser.SecondaryOrg1 AS [Secondary Org 1], dimUser.SecondaryOrg2 AS [Secondary Org 2], dimUser.PrimaryOrgName AS Org, fact.EndDtID,dimUser.EmpStat AS [Emp Status], CASE WHEN fact.completionStatusID = '-1' THEN 'Not Satisfied' ELSE 'Satisfied' END AS Status FROM factAttempt AS fact INNER JOIN dimActivity AS A ON A.ID = fact.ActivityID INNER JOIN dimUser ON dimUser.ID = fact.UserID INNER JOIN dimDate AS DD ON DD.DateID = fact.EndDtID WHERE (dimUser.EmpStat = 'active') AND (a.code = 'A2102')AND (fact.StartDtID >= '20130101')

Is SQL2K8 SSMS slower than SQL2K5 SSMS?

Posted: 24 Mar 2013 10:57 PM PDT

We have a DB [MYDB] in SQL2K5 box, we are migrating the DB to SQL2K8 box, hence restored the backup from 2K5 box to 2K8. I have a question with following queryset statistics time on SELECT TOP 1000 * FROM [MYDB].[my].[MY_TABLE] set statistics time off In 2K5 SSMS it takes around 3000 ms and in 2K8 SSMS it is taking 9000 ms. Now if I execute the query from 2K5 SSMS for both the DB then execution in 2K8 DB is less than 3000 msand if I execute the query from 2K8 SSMS for both the DB then execution in 2K5 DB is more than 9000 msWhat could be the reason? Is there any feature I need to disable in SSMS 2K8?

BCP

Posted: 21 Mar 2013 07:16 AM PDT

I have a stored procedure that exports data from a table into .csv file. There are several different record sets that get exported. They are all comma delimited with double quotes as a text qualifier. All of my files export and you can double click open the .csv file into excel and everything is fine, except for one. The interesting thing about this record set is that some of the data has a trademark symbol.BCP:DECLARE @bcp varchar(1000) = 'bcp "SELECT * from dbo.tempExportData" queryout "' + @filePath + + @FileOutputName + '" -T -w';EXEC @Result = xp_cmdshell @bcp;In the process I am getting the data:DECLARE @ColumnList varchar(5000) select @ColumnList = coalesce(@ColumnList + ',','') + quotename(cast(Name as varchar(50)) , '"') from tempdb.sys.columns where object_id = object_id('tempdb..#tempData') insert into dbo.tempExportData (outputData)values (@ColumnList) insert into dbo.tempExportData (outputData)selectquotename(isnull(FieldA, ''), '"') + ',' +quotename(isnull(FieldB, ''), '"')from #tempDataCSV:FieldA,"FieldB"ACME®,"some more data","even more data"Big Company,"still more data"All of the data is contained in column A of the spreadsheet and FieldA is not text qualified. If you open the .csv in notepad,textpad, etc all of the columns are text qualified. I know that I can open excel and use the import wizard to successfully import the data but I am wondering why when you open the .csv excel is not handling it correctly?Let me know if you need more information.

Is DBCC CHECKDB an appropriate weekly check for a 365x24x7 availability db?

Posted: 24 Mar 2013 10:08 PM PDT

We have a db that has a mission critical website sitting on top of it. We check it weekly with a DBCC CHECKDB command, which takes 1 to 2.5 minutes to run. Are web users likely to see issues during that time? If so, is there a better way to do a weekly health check on it?

Validate a date held in a text field.

Posted: 24 Mar 2013 09:22 PM PDT

Our systems administrator has took it upon themselves to use a free txt field to house a data value.This is all good and well if users enter the date in the right format, which of course they do not. I need to come up with some way of listing all dates in said field that do not appear in the the following format via a SQL query.Acceptable format = dd/mm/yyyy (ie 13/03/2013)Any suggestions extremely welcome.Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog