Tuesday, June 11, 2013

[MS SQL Server] I/O requests taking longer than 15 seconds

[MS SQL Server] I/O requests taking longer than 15 seconds

I/O requests taking longer than 15 seconds

Posted: 11 Jun 2013 12:21 AM PDT

Hello, we have an issue today and we have to restart our sql server (restart the server not the sql process)we have sql server 2008 enterprise[b]What we noticed?[/b]we noticed that all transactions over a table were very slow, a lot of locks, poor performance etc...when we try to kill one trasaction, it keeps on rollback state, never finish.so we try to shutdown sql server process, but never finish (i think because of rollback), so we restart windows and everythigs starts working again[b]What I check?[/b]I check windows events, nothingbut in sql server logs we find this [b]I/O requests taking longer than 15 seconds to complete[/b]I don't know what could be the cause of this, any help will be grateful[code="plain"]Fecha,Origen,Gravedad,Mensaje06/11/2013 09:01:01,Servidor,Desconocido,SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.06/11/2013 09:00:03,spid5s,Desconocido,SQL Server has encountered 11 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x0000000000000558. The offset of the latest long I/O is: 0x000000000a000006/11/2013 08:59:42,spid6s,Desconocido,Tiempo de espera agotado para el tipo de bloqueo temporal del búfer 2 de la página (1:9)<c/> id. de base de datos 2.06/11/2013 08:59:42,spid6s,Desconocido,Error: 845<c/> gravedad: 17<c/> estado: 1.06/11/2013 08:59:42,spid6s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000041EFE8080<c/> page 1:9<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 72145324650528768<c/> task 0x0000000004286508 : 0<c/> waittime 300<c/> flags 0x100000003a<c/> owning task 0x000000000040E748. Not continuing to wait.06/11/2013 08:58:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007ADB88. Not continuing to wait.06/11/2013 08:55:28,spid72,Desconocido,A time-out occurred while waiting for buffer latch -- type 4<c/> bp 000000010CFCE280<c/> page 1:116<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 72057594574798848<c/> task 0x000000043BD182C8 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007AD4C8. Not continuing to wait.06/11/2013 08:54:41,spid51,Desconocido,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.06/11/2013 08:54:41,spid51,Desconocido,Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.06/11/2013 08:53:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007ADB88. Not continuing to wait.06/11/2013 08:48:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007ADB88. Not continuing to wait.06/11/2013 08:47:20,spid86,Desconocido,Process ID 66 was killed by hostname PC-SISTEMAS2<c/> host process ID 5140.06/11/2013 08:45:57,spid86,Desconocido,Process ID 99 was killed by hostname PC-SISTEMAS2<c/> host process ID 5140.06/11/2013 08:43:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007ADB88. Not continuing to wait.06/11/2013 08:38:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007ADB88. Not continuing to wait.06/11/2013 08:38:13,spid5s,Desconocido,SQL Server has encountered 18 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x0000000000000558. The offset of the latest long I/O is: 0x000001b924000006/11/2013 08:33:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x3a<c/> owning task 0x00000000007ADB88. Not continuing to wait.06/11/2013 08:31:53,spid5s,Desconocido,SQL Server has encountered 12 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x0000000000000558. The offset of the latest long I/O is: 0x000001b221000006/11/2013 08:28:16,spid21s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000015DFF0C80<c/> page 1:77<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 536870912/281475513581568<c/> task 0x00000000007ADB88 : 0<c/> waittime 300<c/> flags 0x1a<c/> owning task 0x00000000007ADB88. Not continuing to wait.06/11/2013 08:27:39,spid12s,Desconocido,The background checkpoint thread has encountered an unrecoverable error. The checkpoint process is terminating so that the thread can clean up its resources. This is an informational message only. No user action is required.06/11/2013 08:27:39,spid12s,Desconocido,Error: 18400<c/> gravedad: 16<c/> estado: 1.06/11/2013 08:27:39,spid12s,Desconocido,Tiempo de espera agotado para el tipo de bloqueo temporal del búfer 2 de la página (1:9)<c/> id. de base de datos 2.06/11/2013 08:27:39,spid12s,Desconocido,Error: 845<c/> gravedad: 17<c/> estado: 1.06/11/2013 08:27:39,spid12s,Desconocido,A time-out occurred while waiting for buffer latch -- type 2<c/> bp 000000041EFE8080<c/> page 1:9<c/> stat 0x3c0040d<c/> database id: 2<c/> allocation unit Id: 72145324650528768<c/> task 0x000000000040E748 : 0<c/> waittime 300<c/> flags 0x100000001a<c/> owning task 0x000000000040E748. Not continuing to wait.06/11/2013 08:26:02,spid5s,Desconocido,SQL Server has encountered 9 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x0000000000000558. The offset of the latest long I/O is: 0x000001abad000006/11/2013 08:20:32,spid5s,Desconocido,SQL Server has encountered 14 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x0000000000000558. The offset of the latest long I/O is: 0x00000184eb000006/11/2013 08:00:13,,Desconocido,Log was backed up. Database: sistema<c/> creation date(time): 2012/08/12(00:40:07)<c/> first LSN: 279474:3117:1<c/> last LSN: 279496:11627:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'I:\LOG\sistema_backup_2013_06_11_080007_0603540.trn'}). This is an informational message only. No user action is required.[/code]edit:I'm checking this linkhttp://www.sql-server-pro.com/i-o-requests-taking-longer-than-15-seconds-to-complete.htmlI'm investigating if this could be a bad config of sql server or its related to OS (windows server 2008/storage system).thanks!

How to find out cause of deadlock?

Posted: 11 Jun 2013 04:21 AM PDT

Hi All,2days ago we have a dead lock.how to find out, for what cause the dead lock got occured?

DDL Audit at Instance Level in SQL 2008

Posted: 11 Jun 2013 04:16 AM PDT

Hi Everyone,Can you please guide and correct me if I'm wrong somewhere...We need to capture all DDL events (only) in SQL Server 2008 R2..so as per my understanding I guess we have two of these options as below1) Triggers2) SQL Server Audit specificationNow I further have a requirement to capture all the DDL events both in SQL 2008 and SQL 2005.But in SQL 2005 we don't have Audit specification (not sure), so only option left is triggers.. Please let me know if there are any other options or ways to do this in SQL 2005..?Also is SQL Server Audit Specification the best way to do the DDL Audits in SQL 2008, and what would be the best option then for SQL 2005?Thanks-Nivedita

All Jnr DBA to Create Logins without using the sysadm / securityadmin role

Posted: 11 Jun 2013 01:25 AM PDT

Hi all,I'm looking at a way of allowing a Jnr DBA user to create sql logins and then map those logins to users to what could be one or many databases. Ideally i want to do this without giving them sysadmin rights.If i grant them the server level permission ALTER ANY LOGIN then they are able to create the login but not able to then map the login to a database. I think if i give the Jnr DBA user the ALTER USER permission then this would work but as this is a database level permission i didnt really wont to do this on every database. Any ideas would be much appriciated.Many thanksFrant

SQL Agent permissions model help

Posted: 10 Jun 2013 10:38 PM PDT

Hi there, I've been trying to get some security defined around SQL Agent to a point I'm happy with the controls in place but allowing my developers to do their work without too many restrictions (aka needing my intervention :-D). I've hit an obstacle and I'm not sure of how to get around it, please help! :-)I have three environments - DEV, UAT & PRD. I have granted my developers sysadmin on DEV, no worries there. Moving to UAT, I have granted them SQLAgentOperator role so they have the ability to edit / enable / execute jobs. In PRD, security is stripped back further in that a job needs to be owned by sa to run (i.e. if something gets loaded up, it won't run until I've reviewed it and no one can amend it either). In UAT I have created a proxy account, permissions granted to a database role I've created in msdb, which my developers have been added to, that then allows them to run SSIS jobs (SQL Server doesn't allow you permission to run) by using the runas selection in the jobstep.The issue is around UAT. We generally work in a team, so different people will be working on the same piece of work at different points. The SQLAgentOperator role only allows you to edit jobs in your own name. SQL Server won't allow you to add a group as a job owner and therefore I'm a little stuck, if user A has created a job, then user B wants to edit it, I still need to intervene in order to change the owner (since ownership change is reserved for sysadmin).So how do I get around this? Is this by design? One way I've considered solving this is by granting their secondary ("admin") accounts sysadmin permissions, but ideally I'd like a neater solution.

Automated backup with no Agent i.e. SQL Express. Hide logon details

Posted: 10 Jun 2013 09:42 AM PDT

Hi [url=http://support.microsoft.com/kb/2019698]http://support.microsoft.com/kb/2019698[/url]Shows a SP to backup DB(s) and how to use windows scheduler to achieve this.This would work ok if you could use Windows Authentication, however if the only user you have is an SQL login, the SQLCMD line parameter will contain login credentials i.e. password.I tried creating the backup DB SP with EXECUTE AS but would effectively have to have this on every DB because of reasons explained in [url=http://www.sommarskog.se/grantperm.html]http://www.sommarskog.se/grantperm.html[/url][quote]when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.[/quote]This could be done with certificates and proxy users - but is OVER my head. It SEEMS difficult to maintain on many client machines. I can only think of creating an EXE (binary code) that is called by Windows Scheduler and connects to the DB Server with backup rights. The Login credentials would be hardcoded but safe in binary form.

SQL 2008R2 Maintenance Plan in a Master/Target Server Environment (MSX/TSX)

Posted: 10 Jun 2013 09:45 PM PDT

Hi,I'm just triialling a situation to see if I can figure out if I can set this up in our SQL Server estate.We have > 180 SQL Servers to maintain, and was hoping I could set up 1 Master server to deploy all the SQ LAgent Jobs to all the remaining servers.There is a mix of SQL 2008R2, SQL 2008 and SQL 2005 (there are a couple of SQL 2000, but I can live with managing these individually).Anyway, I've recreated a basic set up on my laptop. The base install of SQL is SQL2008, then there are 3 more instances installed as well, a SQL 2005, SQL 2008 and SQL2008R2 instances.I can add a SQL agent job and it happily gets populated to all the instances and all seems fine.However, here's my problem. There was a maintenance plan already created for all the instances except the SQL2005 one. When I edit the maintenance plan, and add the instance to the list of servers to deploy to, it issues the message:"Apply to target server failed for job 'MaintenancePlan.Daily (Multi-Server)"I've tried creating a brand new Maintnenace Plan from scratch, using the Wizard and also manually, but still getthe same results.So, now my head is hurting from bashing it against the immovable wall in front of me. :-)Could anyone help to get rid of this wall ?ThanksDave

SQL Server 2008 R2 Maintenance plans and backups

Posted: 10 Jun 2013 01:00 PM PDT

Hi, I have a customised maintenance plan that rebuilds the indexes and updates the statistics and then takes a full DB backup.Is there a way to find out what backup files where created by the maintenance sub-plan?I used to be able to do this with SQL Server 2000 but with 2008 R2 it does not look easy.Regards,Ignacio
Posted by at No comments:

[SQL 2012] SQL Server 2012 cluster install error

[SQL 2012] SQL Server 2012 cluster install error

SQL Server 2012 cluster install error

Posted: 11 Jun 2013 02:11 AM PDT

I am installing SQL Server 2012 Enterprise (x64) with SP1 on a Windows 2012 SP1 3 node cluster utilizing mount points. I get the following error during the initial install on the first node:While updating permission setting for Folder 'z:\SQLDATA\System Volume Information' the permission setting update failed for file 'z:\SQLDATA\System Volume Information\ResumeFilterKey.Store' The folder were supposed to be set to 'D:P(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;FA;;;S-1-5-80-617070787-1164103636-1191419902-1692725703-799689240)The 3 workarounds I found all referenced SQL Server 2008 R2. I tried all 3 suggestions and none have worked.I am thinking this relates to the mount somehow, but I am stuck. And, no, the mount points are NOT in the root of the drive.Any ideas???

How to change IntelliSense timeOut in SSMS 2012?

Posted: 11 Jun 2013 01:11 AM PDT

Hi everyone(my english is not very strong)I was wondering if anybody know how to "manually" change de time out of the intellisense in SQL server Management Studio 2012.I have a local instance and the SSMS works very well with it. But when I connect to the remote instance, intellisense doesn't work.I read that this value (timeout) is "hardcoded", but I'll try to change it. :-DThanks in advance

Data Cube / SQL Server 2012 / BI

Posted: 10 Jun 2013 10:02 PM PDT

Hi Folks,I am a newbie to this forum and relatively new to SQL especially SQL Server.My first query in this area:I have got 2 tables with one common field on which i can make a join.A/ I have been asked to convert this data into a cube.All i know is that a cube is more then 2 dimensional data. Would love your input on where i can learn about cubes and how do i achieve this in sql server 2012 as i already have a license for it.B/ The objective of this exercise i believe, is to keep ready as many reports in as many permutation combinations as possible so that when a user asks for this data, its readily available and very fast to execute.Wouldn't a view just work for this or is that too simple and time consuming to modify every time there is a different request?C/ We also would like to use the BI part of SQL Server 2012. End result is for users to use Excel to interact and play with the data/reports.Can users directly through excel leverage the BI tools of SQL Server 2012Thanks in advance..Regds, Ross

Oracle Data Guard Functionality

Posted: 10 Jun 2013 09:47 AM PDT

Hello all,Does anyone know if the functionality of Oracle Data Guard (Separation of Duties) is available under SQL Server 2012?

Where to find the Sql Server Integration Services 2012 components' componentClassIDs (CLSID)

Posted: 10 Jun 2013 09:41 AM PDT

Hi,I need to find the list of all the component CLSIDs for sql server integration services 2012. Can anyone let me know where I can find them?Thanks!
Posted by at No comments:

[T-SQL] Finding duplicate rows with multiple fields for comparison

[T-SQL] Finding duplicate rows with multiple fields for comparison

Finding duplicate rows with multiple fields for comparison

Posted: 11 Jun 2013 01:16 AM PDT

I've taken a look at the scripts and forum posts on the site regarding finding duplicate rows, but I haven't been able to adapt them to my situation. I have data being pulled from Oracle and placed in a table in my sql server db. The table consists of a primary key identity field, a field containing the oracle ID, and 4 fields containing data. I need to find all duplicates, with a duplicate being defined as a match across all 4 data fields. I've tried queries along the following lines, but they don't work. [code="sql"]SELECT MAX(id), MAX(oracle_id), field1, field2, field3, field4FROM oracleRecordsGROUP BY field1, field2, field3, field4 HAVING COUNT(*) > 1[/code]If I understand how this is [i]supposed [/i]to work, only duplicate rows should be returned. Is this the case? I've gotten it to work when comparing only one field, but when I try to compare more than one I break it. Any suggestions?

Hi guys this urgent reg int to varchar conversion

Posted: 10 Jun 2013 12:46 PM PDT

I am a fresher dba i got a task where i need to convert int value to varchar please help me with this in the highlighted value else 0 instead i want 'not enabled' and 'not scheduled' SELECT distinct @@SERVERNAME AS ServerName, CASE WHEN J.Name IS NOT NULL THEN 1 -- job exists ELSE 0 -- Job does not exist END AS IsExisting, CASE J.enabled WHEN 1 Then 1 -- job is enabled ELSE 0 -- instead of 0 i want to return 'not enabled' job does not exist error condition END AS IsEnabled, CASE WHEN ss.enabled IS NOT NULL THEN 1 -- schedule is enabled ELSE 0--instead of 0 i want to return 'not scheduled' END AS IsScheduleEnabled , J.NAME, CASE WHEN MAX(H.RUN_DATE) IS NULL THEN 0 ELSE MAX(h.run_date) END AS LASTRUNDATE ,getdate() as Date FROM MSDB..sysjobs J LEFT JOIN MSDB..sysjobhistory H ON J.job_id = H.job_id LEFT JOIN MSDB..sysjobschedules sjs ON sjs.job_id = J.job_id LEFT JOIN MSDB..sysschedules ss ON ss.schedule_id = sjs.schedule_id LEFT JOIN msdb..sysjobsteps steps on steps.job_id = j.job_id AND steps.step_id = 1 -- we are just checking for job existance here so it won't matter if there are multiple steps where j.name like 'sys%' and (j.enabled=0 or ss.enabled is null or j.name is null or h.run_date is null) GROUP BY J.NAME,J.enabled, SS.enabled,h.run_date

not able to understand that store procedure running recursively or not

Posted: 10 Jun 2013 07:56 PM PDT

create procedure testRecur()BEGIN if call testRecur(); end if; select 'hi'; //This return does not execute anymore even I cant debug that procedure running //recursively or not END

subquery returning more than one value

Posted: 10 Jun 2013 06:24 AM PDT

I have two tables, a and b. I want to select all from table a (except where proc_num is null) and one field from table b when it matches a row in table a. I keep getting the data in the table b field for all the rows in table a. select *.a, b.result from tablea as a left outer join tableb as b on a.id = b.parent_id The result I get are:proc_id dtime proc_num result70360 20130404 3706 positive 63 20080313 0960 positive2956 20080313 null positive59913 20120327 3705 positiveI want to get 3 rows for this. I don't want the row for 2956 to show, I only want the result to be positive for the row with proc_id of 63, the rest should be null. I probably need a subquery but I can't figure out how to make this work. Thanks very much for any ideas.

Pivoting help

Posted: 10 Jun 2013 12:07 PM PDT

Hi,I trying to get the dataset Ptid Test Result Date1 BP Neg 1/1/20131 CG Pos 1/2/2013I want result as Ptiid BP Date CG Date1 Neg 1/1/2013 Pos 1/2/2013

using Correlated subQuery in Join not working

Posted: 10 Jun 2013 07:11 AM PDT

I have a large query that is returning multiple rows that I tracked down to my jornal table.It has 2 (or more) credit entries for some entries and I only want one (doesn't matter which one). So I want to use a "TOP 1" but can't use it directly in my JOIN. I can use a CROSS APPLY but not sure if that is the best way. My query is something like:[code]SELECT *FROM Client scJOIN dbo.JOURNAL jrnlON jrnl.ClientId = sc.ClientIdAND jrnl.DC = 'c'[/code]This will give me back 2 records.I tried to do a correlate subquery, like so:[code]SELECT *FROM dbo.Client scJOIN ( SELECT TOP 1 * FROM dbo.JOURNAL jrnl WHERE jrnl.ClientId= sc.ClientId AND jrnl.DC= 'c') jrnlON jrnl.ClientId= sc.ClientIdWHERE sc.ClientId= 942222[/code]The problem is that the "sc.ClientId" inside the join gets an error:The multi-part Identifier "sc.ClientId" could not be bound.Why is that?I can get this to work using a CROSS APPLY (not sure why) but wanted to use a normal join to solve the issue. This works:[code]SELECT *FROM dbo.Client scCROSS APPLY( SELECT TOP 1 * FROM dbo.JOURNAL jrnl WHERE jrnl.ClientId= sc.ClientId AND jrnl.DC= 'c') jrnlWHERE sc.ClientId= 942222[/code]Why doesn't the normal Join work?Thanks,Tom

More efficient way to iterate through table rows to perform Update

Posted: 10 Jun 2013 05:51 AM PDT

I have a temporary table in SQL Server 2008. I want to perform UPDATE row-by-row.As of now I am using a less efficient way by adding an Id column and then updating the row number. Later, I am running a loop and picking row with Id matching the loop counter.Is there any more efficient way to iterate the table row by row and performing Update operation?I have MERGE in mind, but the problem is that I want to check several conditions before update is performed. With MERGE, I will be bound to use CASE WHEN.There are two tables, #Temp1 and SalesRecord. I want to read from #Temp1 and update SalesRecordBoth tables have few similar columns.[b]#Temp1 (CustId int, AllocVal decimal, RowId)SalesRecord (CustId int , Points decimal)[/b][code]declare @Total as int select @Total = count(*) from #Temp1 declare @counter as int set @counter = 1 declare @CurrentVal as decimal declare @CurrentCustomer as int declare @SumPoints as decimalwhile (@counter <= @Total) begin -- Get row from #Temp1 select @CurrentCustomer = CustId, @CurrentVal = allocVal from #Temp1 where RowId = @counter select @SumPoints = Sum(Points) from SalesRecord where CustId = @CurrentCustomerif ((@CurrentVal + @SumPoints) <= 1) begin update SalesRecord set Points = @CurrentVal end else begin delete from SalesRecord where CustId = @CurrentCustomer end set @counter = @counter + 1 end[/code]

bulk insert not firing triggers ?

Posted: 10 Jun 2013 02:24 AM PDT

hii have table with triggers on the table while bulk inserting it is not firing what is the problem ?with best regardsPradeep
Posted by at No comments:

[SQL Server 2008 issues] Resource Governor in SQL Server 2008

[SQL Server 2008 issues] Resource Governor in SQL Server 2008

Resource Governor in SQL Server 2008

Posted: 10 Jun 2013 12:41 PM PDT

Hi, Am looking to implement Resource Governor functionality, and would like to know the links or any useful details which can help me in proceeding further along with sample code.Regards,Sai Viswanath

sql server 2008

Posted: 10 Jun 2013 06:08 PM PDT

I am confused how to install sql server 2008

which type of value should be given to timestamp datatype

Posted: 10 Jun 2013 06:23 PM PDT

example:--- use 12 go insert into tstamp values( ?????)

can anyone give me name of the software which creates views automatically with wizard

Posted: 10 Jun 2013 06:27 PM PDT

I had tried to create views for sql server 2008 with tocows software ,but it does not give any valuable results.

Cannot connect from VM with Windows XP to SQL Server 2008 R2 on Host Windows 8

Posted: 10 Jun 2013 01:22 AM PDT

Hi,I have a PC running windows 8 and SQL server 2008 R2 express and a virtual machine with Windows XP. I cannot connect from virtual machine to SQL Server.What I have done:1) Allow remote connections on SQL Server o host machine.2) Enable TCP on sql configuration manager on host machine3) Create an inbound rule in Windows Firewall in Host machine to allow connections through port 1433 ( default port used for SQL server)4) Set a Bridged nertwork connection on virtual machine. When I set the firewall off ( for private networks ) I can connect to SQL server , I can see the databases , but I cannot access them. When I use the SQL Server Management Studio from the virtual machine I can connect to the server and I see the databases but when I try to open one of them to see the tables I get the following message: [quote] The database ERP_DB is not accessible. (ObjectExplorer)[/quote] When the firewall is on I cannot even connect to SQL server on host. and I get the following message:[quote]A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)[/quote] I would be grateful if someone could help me. Thank you very much.

Switch Connection

Posted: 10 Jun 2013 03:11 AM PDT

I am trying to load data from a replicated database in SSIS-if for some reason the replicated fails then i need to point my source to the production server. I am doing a record count comparison to find out if the replication has not failed. My problem though is to find out how we can switch the connection based on the result of the record counts. I have other destination databases connections down the stream which do not need to be switched. The switching only applies to the source database. Any suggestions on the approach - Thank You.

Can't update to SP3 for SQL 2008

Posted: 10 Jun 2013 07:09 AM PDT

HelloI am running SQL Server Enterprise 2008 RTM and need to upgrade to service pack 3 to support a database on a 32 bit server (Windows Server 2008 standard sp2).I tried installing SP3 first but it failed.I then tried installing SP2 and received a message to install the .NET 4.0 frameworkThe framework was installed and I have a one hour window tommorow to install SP3.I'm going to have SP1 ready too in case I need to run them consecutively.I am new to service packs - the one experience I had was to make sure reporting services was running. I suspected the reason for the error this time was also related to Report Services because the log in the bootstrap folder (C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130610_121337 on mine) stops listing the instance ID.I don't know why SP3 would stop at report services while SP2 let me know I needed the .NET framework. (why didn't sp3 tell ask for the framework?)It says to check the logs so I assume they mean the bootstrap logs. I don't see anything in the SQL logs through SSMS.It also seems unusual that the INSTANCENAME reads <empty.User Input Settings: ACTION: Patch ALLINSTANCES: False CLUSTERPASSIVE: False CONFIGURATIONFILE: HELP: False INDICATEPROGRESS: False INSTANCENAME: <empty> QUIET: False QUIETSIMPLE: False X86: FalseCould you please offer advice installing patches/trouble shooting? Am I supposed to stop services? ( I tried it, didn't work) Thanks in advanceDave

Benefits in using a unique login for each user.

Posted: 10 Jun 2013 08:13 AM PDT

My colleague has received a new application to maintain, written in 4G language.The users have their own, unique credentials to connect to this application, [b]BUT[/b] the application itself connects to MSSQL(there are various servers, running 2005 or 2008 versions) using only one, application dedicated, login. He thought to advise as wellto allocate unique sql login to each user, but in preliminary discussions he was told that there is no benefit for them to go that route,as it will only add an additional layer of maintenance.May I have your pros and cons what are the advantages to have unique sql login for each of the users as oppose of having only one,application dedicated login, please?Much appreciated!

SQL 2008 - Configuring SSIS in Cluster

Posted: 10 Jun 2013 11:11 AM PDT

After making the changes to the xml file to have the cluster name in all the nodes, can i connect IS from SSMS using Cluster name or individvual node name?select @@servername gives me SQL name of cluster which i have added in the xml file.

Date and Time when Table last queried

Posted: 10 Jun 2013 02:58 AM PDT

Hi,Is it possible to find out when a table was last accessed i.e Table has been selected from a query.Thanks

PAGELATCH_UP Won't Let Go!

Posted: 10 Jun 2013 04:48 AM PDT

All,I ran into an issue where my backups and checkdb jobs are failing on one database. Result of a checkdb[quote]Msg 1823, Level 16, State 2, Line 1A database snapshot cannot be created because it failed to start.Msg 7928, Level 16, State 1, Line 1The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.Msg 5030, Level 16, State 12, Line 1The database could not be exclusively locked to perform the operation.Msg 7926, Level 16, State 1, Line 1Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.Msg 845, Level 17, State 1, Line 1Time-out occurred while waiting for buffer latch type 3 for page (1:2872334), database ID 25.[/quote]sysprocesses shows spid 16 with a PAGELATCH_UP and a waitresource 25:1:2872334 and cmd of CHECKPOINTThis makes me think there is a hung process out there somewhere with a latch on the specified page. Short of restarting the service, what other action should I be taking. I didn't want to jump right in and restart the service for fear of causing more harm than good.Let me know if providing more information would be helpful.

urgent tsql

Posted: 10 Jun 2013 01:02 PM PDT

I am a fresher dba i got a task where i need to convert int value to varchar please help me with this in the highlighted value else 0 instead i want 'not enabled' and 'not scheduled' SELECT distinct @@SERVERNAME AS ServerName, CASE WHEN J.Name IS NOT NULL THEN 1 -- job existsELSE 0 -- Job does not existEND AS IsExisting,CASE J.enabledWHEN 1 Then 1 -- job is enabledELSE 0 -- instead of 0 i want to return 'not enabled' job does not exist error conditionEND AS IsEnabled,CASEWHEN ss.enabled IS NOT NULL THEN 1 -- schedule is enabledELSE 0--instead of 0 i want to return 'not scheduled'END AS IsScheduleEnabled ,J.NAME, CASE WHEN MAX(H.RUN_DATE) IS NULL THEN 0 ELSE MAX(h.run_date) END AS LASTRUNDATE,getdate() as Date FROM MSDB..sysjobs JLEFT JOIN MSDB..sysjobhistory H ON J.job_id = H.job_idLEFT JOIN MSDB..sysjobschedules sjs ON sjs.job_id = J.job_idLEFT JOIN MSDB..sysschedules ss ON ss.schedule_id = sjs.schedule_idLEFT JOIN msdb..sysjobsteps steps on steps.job_id = j.job_idAND steps.step_id = 1 where j.name like 'sys%' and (j.enabled=0 or ss.enabled is null or j.name is null or h.run_date is null)GROUP BY J.NAME,J.enabled, SS.enabled,h.run_date

Transaction log size far exceeds database size when running delete

Posted: 10 Jun 2013 08:15 AM PDT

we setup a purge process and had about 20GB of free log space but kept filling up the T-log.After breaking into smaller chunks I realized that the amount of log space required far exceeded the total db size.On one particular table (see definition below) the delete command loaded 15GB into the T-log. This was only for a fraction of the data within the 19GB database.I'm just looking for an explaination as to why this is. This table does have 3 Text type columns, my thought is that this data is compressed within the table but cant be within the log. Any ideas on this would be appreciated. Thanks.[p]Data Type Length scale precisionint 4 0 10datetime 8 3 23char 1 0 0char 4 0 0char 8 0 0char 6 0 0varchar 47 0 0varchar 40 0 0tinyint 1 0 3tinyint 1 0 3char 3 0 0char 20 0 0char 8 0 0char 8 0 0char 8 0 0char 10 0 0char 7 0 0tinyint 1 0 3tinyint 1 0 3tinyint 1 0 3char 3 0 0char 6 0 0tinyint 1 0 3tinyint 1 0 3char 4 0 0tinyint 1 0 3int 4 0 10smallint 2 0 5char 3 0 0char 4 0 0char 6 0 0tinyint 1 0 3text 16 0 0text 16 0 0text 16 0 0datetime 8 3 23datetime 8 3 23char 15 0 0char 15 0 0char 3 0 0[/p]

SQL Server Transactional Replication Unitialized Replication

Posted: 10 Jun 2013 02:57 AM PDT

Good Day,we added the replication via the gui and the snapshot ran successfully. However when we run the Logreader Agent we get the message of UNITIALIZED Subscription . Any ideas ?

Learn pivot but got an error

Posted: 10 Jun 2013 08:12 AM PDT

I copied code exactly from http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query as below but got an error said that Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.How to fix it?SELECT *FROM ( SELECT year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], InvoiceAmount as Amount FROM Invoice) as sPIVOT( SUM(Amount) FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec))AS pivot

DB Size Growing after moving fields

Posted: 10 Jun 2013 12:30 AM PDT

Hi All,I am new here, but have read the site a lot. I have been doing some SQL maintenance on various servers that I administer. One of the scripts takes 20-30 fields or so, and moves the data from the old column to a new column, and once complete it drops the old column. After doing this, the DB file (MDF) grew from about 3.5GB to about 5GB.Why?I did a shrink on both the file and database. This particular box is SQL 2008.Let me know any questions.Thanks for the help!

How to get results from an SP into a table.

Posted: 09 Jun 2013 11:13 PM PDT

Dear reader,Below is an example to get data from a stored procedure into a table.Is there a more generic method or better method to do this. (Generating a resulttable for example which works for 'any' sp). sp_spaceused is used as an example. Thanks in advance,ben[code="sql"]---- 20130610-- ben brugman---- EXAMPLE:-- two databases-- three tables-- get the space used (6 rows) into a table.---- Question:-- How do I get the info of a stored procedure in a table.------ Create a result table.--select CONVERT(varchar(30), '') as name,CONVERT(varchar(30), '') as rows,CONVERT(varchar(30), '') as reserved,CONVERT(varchar(30), '') as data,CONVERT(varchar(30), '') as index_size,CONVERT(varchar(30), '') as unusedinto ##A where 1 = 1-- second result table with an extra columnselect CONVERT(varchar(30), 'Database1') as DB_name,* Into ##B from ##A---- Fill the first result table.--insert into ##A EXEC sp_spaceused 'table_name1'insert into ##A EXEC sp_spaceused 'table_name2'insert into ##A EXEC sp_spaceused 'table_name3'-- Fill the second result table with extra info.insert into ##B select CONVERT(varchar(30), 'Database1') as DB_name, * from ##A-- Repeat process for a second database.Use database2delete ##Ainsert into ##A EXEC sp_spaceused 'table_name1'insert into ##A EXEC sp_spaceused 'table_name2'insert into ##A EXEC sp_spaceused 'table_name3'insert into ##b select CONVERT(varchar(30), 'Database2') as DB_name,* from ##A---- Show all results.--select * from ##B-- Clear updrop table ##Adrop table ##B[/code]

SQL Server 2008 Database Mail

Posted: 29 May 2013 05:04 PM PDT

Hi SQL Masters,I have a situation on my SQL Server hosted on Virtual machine (Oracle Virtual Box). I have a SQL Server there and i configured the Database mail via Wizard and 'Database Mail XPs'. My problem here is that the test mail could not succeed.Anybody could help on this situation?Thanks and Regards,dev1

SQL Agent Service runs fine, but keeps logging this error [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]

Posted: 10 Sep 2010 12:29 AM PDT

Hi AllI need some help here, I have looked and googled to no avail. I have an installation of SQL Server 2008 installed on a windows server 2008 active/passive cluster. SQL Server runs at any point on the active node until a failover if required.Now I am getting the following errors being logged in the sqlagent.out file. I am stumped as to the repeated Could not open a connection to SQL Server [2]. [SQLSTATE 08001] Error.2010-09-10 10:03:43 - ? [100] Microsoft SQLServerAgent version 10.0.2531.0 ((Unknown) unicode retail build) : Process ID 88002010-09-10 10:03:43 - ? [101] SQL Server ******* version 10.00.2531 (0 connection limit)2010-09-10 10:03:43 - ? [102] SQL Server ODBC driver version 10.00.25312010-09-10 10:03:43 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is 2010-09-10 10:03:43 - ? [310] 16 processor(s) and 98295 MB RAM detected2010-09-10 10:03:43 - ? [339] Local computer is ****** running Windows NT 6.1 (7600) 2010-09-10 10:03:44 - ? [432] There are 12 subsystems in the subsystems cache2010-09-10 10:03:44 - ! [125] Subsystem 'PowerShell' could not be loaded (reason: 3)2010-09-10 10:03:44 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent2010-09-10 10:03:44 - ? [129] SQLSERVERAGENT starting under Windows NT service control2010-09-10 10:03:47 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect2010-09-10 10:03:47 - + [408] SQL Server MSSQLSERVER is clustered - AutoRestart has been disabled2010-09-10 10:05:04 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:07:04 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:08:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:10:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:10:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:10:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:10:03 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:10:10 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:10:10 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:15:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:15:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:15:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:20:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:20:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] 2010-09-10 10:20:03 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]Any ideas on how I can get rid of this error would be most appreciated. Thanks guysRegardsMatt

Transaction log file growth

Posted: 09 Jun 2013 09:34 PM PDT

HI everybody.We backup our log files every 10 mins. 3 days ago we had an incident where users where complaining of 'timous' or slow/poor response times. We use monitoring software and i have since discovered that our monitoring software had stopped monitoring the server in question so i was not alerted to any issues. There is no info in the error logs other than the usal log every 10 mins of a succesfull backup. I can see that the transaction log file is sized at 2Gb and has CURRENTLY 99.5% free. I am looking for a way to see if the file had to auto grow during the reported time of poor performance but unforrtunately the individual log file backups have now been deleted. Is there a way of checking this from a dmv ? Or the first LSN and last LSN which is recorded in the error logs ?

SSRS and Report Parts

Posted: 10 Jun 2013 05:05 AM PDT

Does anyone have experience developing SSRS reports in the following way.A co-worker wants to build "parts" of a report and then merge them into the final report.Normally I don't see a problem with that but the reports are such that it shows an item per page in a multi item report, so we have to embed parts inside each other at multiple levels. There's a hierarchy in the body. He suggests that we build "parts" using TVF in the dataset and then merge the parts, re-writting the sql script by joining the TVF from the child part to the parent dataset. We would do this multiple times since there will be multiple parts. Then we can tweak the child parts we added to make the final report look and behave like what we needed it to.His goal is to make "reusable" parts for other reports but the layout is unknow for any future reports, if any. We are also using custom codes and custom assemblies in the reports so we would have to copy those over manually as well.And it's not a simple report. It's actually a form with detailed product information. Imagine a form for a car product.parent level is the car (i.e. model)child is components such as steering system, fuel system.Parts under that as steering wheel, fuel pump.Then part details like leather steering wheel, electronic fuel pump. etc..Feedback on this process would be appreciated.

The size of a table. (space_used against calculated space).

Posted: 09 Jun 2013 11:42 PM PDT

With sp_space_used you get the 'gross' size of a table. (Used_size for data)Summing all fields with a datalength function also gives a size for a table, offcourse this is without the overhead.The used_size is about 2 to 3 times the calculated size.[b]So how can I calculate the size more accurately?[/b]Thanks for your time and attention.ben brugmanExtra info:This was calculated for heaps and clusters (90 percent fill rate), both were similar.No deleted rows.From 30 to 75 columns for each table, with a lot of NULL's.Overhead was 3 to 5 bytes for each field.(180 to 280 bytes of overhead for each row).

Resource Governor - How to Trace Events

Posted: 10 Jun 2013 04:05 AM PDT

I am trying to set up an automatic email when a user in a resource group goes over a threshhold. I found the resource governor event REQUEST_MAX_CPU_TIME_SEC that according to the documentation does exactly what I'm trying to do. (The reference is following.) I have set the event in the resource governor, but cannot figure out how to trace the event. I did not see the event in SQL profiler and have not been able to find any examples of this. I'm just beginning with the resource governor. I think I may be tracking down the wrong thing because everything I've found doesn't seem to relate to trapping the event and sending an email.The documentation says:1.Set a limit on CPU usage for the ad hoc group. (DONE)2.Monitor SQL Trace events (Resource Governor management class event). (???)3.Perform an action on the event. For example, ignore the event, send an e-mail, send a page, or execute the KILL command on the request. (???)http://msdn.microsoft.com/en-us/library/bb933944(v=sql.105).aspxAny help is appreciated.-Brian

Is it okay for these files to be deleted if it's eating up a lot of space?

Posted: 10 Jun 2013 03:03 AM PDT

I have some files(SQLAGENT.1, SQLAGENT.2, SQLDump.mdmp, SQLDump.log and SQLAGENT.OUT) in one of the drive under the [b]LOG[/b] folder and taking a lot of the space.Is it okay to delete some of these older files? Can someone please explain what these files does?SueTons.

Event log errors from non-valid IP addresses: SSPI handshake failed.

Posted: 10 Jun 2013 01:48 AM PDT

I get this error in the event log for MS SQL 2008 SP1:SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. [CLIENT: 14.200.19.3]. I have received this error 32 times in the last 24 hours. The Client IP address changes, but they all seem to be from India when I look it up. There's no way that there anyone valid from India who is trying to log in. How can I prevent these attacks? They don't get in, I want it to be rejected before it even gets to my server.

Optimizer Execution plan.

Posted: 10 Jun 2013 01:58 AM PDT

I have a Parameterized Stored procedure that has a query with some joins and use of temp table and UNION.When I run the query for the first time in SSMS it runs for 55 secs , however, any subsequent run completes in 5 secseven if I change the parameter values... seems optimizer creates the execution plan and re-uses it subsequently.When I repeat the same action after few hours or in another session (new SPID) then it runs for 55 secs again in first run and subsequently 5 secs, no matter how many times I run it. Why is SQL Server flushing the Query plan out in new session... how can I make it cache the execution plan until optimizer finds a good reason to regenerate one like a new Index or change of columns etc...

Use of not exists

Posted: 09 Jun 2013 11:50 PM PDT

HiI need to get the data from my "live" load table that does not exist in my archive_loadTrying:select count([Live_Key]) FROM [LOAD_SCHED] as T1 WHERE NOT EXISTS (SELECT T2.[Arch_Key] FROM [Arch_LOAD] AS T2 where T2.[Arch_Key] = T1.[Live_Key]) Results in the exact same posts asselect count([Live_Key]) FROM [LOAD_SCHED] as T1There are 9402 records, but about 7500 allready exist in the arch table, thus I would expect only 2000 rows from my first queryIf I replace with NOT IN I get 0 rows in returnAny comments on why?BrDJ

Database Virtualisation Tool

Posted: 03 Jun 2013 11:52 PM PDT

Hi,I would like to know if anyone has come across or used Delphix virtualised tool?Thanks

Back up

Posted: 10 Jun 2013 12:16 AM PDT

What is the use of WITH CHECKSUM in backup statement?

SSIS Package Error

Posted: 10 Jun 2013 12:22 AM PDT

User is again getting error when he executed an SSIS package..he wants to transffer 9Lakh records from SQL database to Excel 2007The package runs well & so the data about 5Lakhs from tables are getting transffered to Excle file but after 5 Lakh the below error comes, also find the attached error file.[SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance countes are not available. To resolve, run this package as an administrator, or on the system's console.Please suggest :w00t:

Foreignkey based primary key reocrd insert SSIS

Posted: 09 Jun 2013 10:35 PM PDT

Dear all, I am balamurugan,new for ssis.we have sql server source & destination is oracle.we need to implement the following steps in ssis:1. SQL to ORACLE record insert ===> its completed.2.SQL to ORACLE --->we need to check forign key of table based record in primary key table if record available the foreign key table record move to oracle table.if record is not in primary key table we give retry job until records(retry time is 5 ) received in PK table.(referencial integrity constriant check).3. how to load balance the records for insert.EX: in source we have 1lak records we need to insert the 1000(set limit 1000 ) records vise versa..please help on this????????????

ctrl+tab quits working in SSMS

Posted: 09 Jun 2013 10:28 PM PDT

Something I have done has disabled ctrl+tab from working. I don't want to restart SSMS, as I have numerous tabs open.Is there a way to reset it so that ctrl+tab works again? Also, what is the key sequence I did to cause this?I have tried "reset window layout" -- didn't help. ctrl+F6 still works.
Posted by at No comments:

Monday, June 10, 2013

[SQL server issues] Commented Issue: AdventureWorksDW2008R2 DimDate table has missing dates [13383]


Commented Issue: AdventureWorksDW2008R2 DimDate table has missing dates [13383]



pavanbarnala wrote Feb 23, 2012 at 10:47 AM


Hello,
Is this been taken care? If so, please share the latest data for DimDate. I'm working on some POC which requires dates in between 2008 and 2010 as well. If it is not already done, can someone let me know is there is any way I can generate the data for those missing dates in that table?



petermyers wrote Feb 26, 2012 at 5:21 AM


The attached script will fix the problem. Use the stored procedure to extend to future dates if needed.



.

sqlserversamples.codeplex.com

Posted by at No comments:
Labels: ,

[SQL Server Data Warehousing] T-SQL SCD2


T-SQL SCD2



Here is the query i am using in case if it helps.


To give you little more scenario  on my request  : Our Data warehouse is very traditional approach of ETL , We have update statments that does lookup and update the Foriegn keys.


My first update statments goes and finds an employee based on a logic of Data Entry



Update dbo.SFWP 
Set EmployeeID  = we2.EmployeeSK
from dbo.SFWP a
Inner Join WMDM.dbo.WMDM AT
on  a.AssignedTaskID = AT.AssignedTaskID
Inner join WMDM.dbo.Employee we1
on AT.EmployeeID = we1.SK
Inner Join WMDM.dbo.Employee we2
on we1.NK = we2.NK
and  Src_EntryDate >= we2.EffectiveBeginDTM and 
Src_EntryDate <= we2.EffectiveEndDTM

Apparently for some reason some employees cannot be matched because  our employee table has this condition not met because of missing records


and  Src_EntryDate >= we2.EffectiveBeginDTM and
Src_EntryDate <= we2.EffectiveEndDTM


So, my new query i am trying to take a previous or Next record and find employee ID and update it.



wITH CustCTE as
(
select  
We2.SK,
We2.NK,
We2.EffectiveBeginDTM,
We2.EffectiveEndDTM,
ROW_NUMBER() OVER (PARTITION BY We2.NK ORDER BY We2.EffectiveBeginDTM) RowNum
from dbo.SFWP a
Inner Join WMDM.dbo.WMDM AT
on  a.AssignedTaskID = AT.AssignedTaskID
Inner join WMDM.dbo.Employee we1
on AT.EmployeeID = we1.SK
Inner Join WMDM.dbo.Employee we2
on we1.NK = we2.NK
Where 
 Src_EntryDate < we2.EffectiveBeginDTM or 
Src_EntryDate > we2.EffectiveEndDTM
and a.EmployeeID is NULL
)
,
CustCTE1 as (
Select 
a.SK
,a.NK
,a.EffectiveBeginDTM
,a.EffectiveEndDTM
,a.RowNum
    
From CustCTE a
Union All 
Select 
Curr.SK
,Curr.NK
,Curr.EffectiveBeginDTM
,Curr.EffectiveEndDTM
,Curr.RowNum
    
From CustCTE Curr
Inner Join      CustCTE1 Prev
on Curr.NK = Prev.NK
and Curr.RowNum = Prev.RowNum-1
)
select * From CustCTE1

But i am not getting the same thing as i expect..


Any help in this matter will be highly appreciated.


thank you in advance



.

social.technet.microsoft.com/Forums

Posted by at No comments:
Labels: , ,
Subscribe to: Comments (Atom)

Search This Blog