Thursday, July 25, 2013

[SQL Server] Handling SQL Server Errors


Handling SQL Server Errors




Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.



SQLTeam.com Articles via RSS


SQLTeam.com Weblog via RSS



Read more

sqlteam.com



Posted by at No comments:
Labels: ,

[SQL Server] Using Dynamic SQL in Stored Procedures


Using Dynamic SQL in Stored Procedures




Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.



SQLTeam.com Articles via RSS


SQLTeam.com Weblog via RSS



Read more

sqlteam.com



Posted by at No comments:
Labels: ,

[MS SQL Server] Adding New Node to exisiting SQL Server 2008 R2 Cluster

[MS SQL Server] Adding New Node to exisiting SQL Server 2008 R2 Cluster

Adding New Node to exisiting SQL Server 2008 R2 Cluster

Posted: 25 Jul 2013 05:08 AM PDT

Hi,I hope someone will be able to assist me. I currently have an active/passive cluster setup on SQL Server 2008 R2. My active node has 2 named instances that are running on the same machine. Both named instances are failing over to my single passive node. I want to add a new node and move one of the named instances to the new node and then add that node to my cluster. So the end result i would like to have 2 active nodes and one passive node. So my question is do i need to physically move or re-install one of the instances on the new node and then add that node to the cluster, or do i first add the new node to the cluster and then just use the cluster manager to move one of the instances to run on the new node?Thanks in advance

Cluster backup drives

Posted: 25 Jul 2013 03:24 AM PDT

I cannot see the main backup drive in SQL cluster when attempting to do a restore. The backups for the cluster is stored on this drive. I only see three of the four SAN mapped drives (which are desiegnated as reserve drive in disk manager). Is there somewhere in SSMS I add the drive I cannot see or is this something to do with SAN?Thanks in advance.

delete old SSIS log files using maintenance plan

Posted: 24 Jul 2013 05:21 AM PDT

We have a SSIS log folder that has a lot of daily log files for SSIS packages.I am hoping to use maintenance plan to delete the files.So I chose the task of maintenace clean up task, and browse to the folder, and put the extension txt.and select older than one hour to test.But no files are deleted,is it because this only applies to backup filea nd maintenance plan report file?but look at the SQl it genreate: it is :EXECUTE master.dbo.xp_delete_file 1,N'D:\_SSISFILES\Log\' ,N'txt',N'2013-07-24T10:19:31',1So I guess it should not matter what files are they, as log as it has an extension of txt.But why it is not working?THanks

LCK_M_RIn_NL lock blocking inserts

Posted: 16 Jan 2012 07:36 AM PST

Has anyone seen this specific lock contributing to a significant wait/blocking time? What could be the cause/solution?The description here is not very helpful: "Occurs when a task is waiting to acquire a NULL lock on the current key value, and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL)."http://msdn.microsoft.com/en-us/library/ms179984.aspxIs it possible that multiple threads trying to insert records in the same table at the same time end up blocking each other with this lock?Thanks

Webservice to establish connection with SQL Server

Posted: 24 Jul 2013 08:13 PM PDT

Hi,Can i get a sample [b]webservice [/b] which can:1. Connect to SQL Server 20082. Take 'Database table name' as input and return its recordsPlease help.
Posted by at No comments:

[Articles] Data Farming

[Articles] Data Farming

Data Farming

Posted: 24 Jul 2013 11:00 PM PDT

Another spy story involving data that's not true, and perhaps not plausible, but it makes Steve Jones think about data shadows.
Posted by at No comments:

[SQL 2012] CDC - SQL Data Tools on multiple table

[SQL 2012] CDC - SQL Data Tools on multiple table

CDC - SQL Data Tools on multiple table

Posted: 25 Jul 2013 03:11 AM PDT

I trying to use the CDC functionality to pull data from multiple tables. I have two packages(One for Init Load and One for Incremental Load) per table and a wrapper package that call Individual SSIS. I have set up the states to be stored at the source db. Where should I have the "Mark Initial Load Start" and "Mark Initial Load End"? If I do it at every package(table), how will it affect incremental loads? Should I do "Mark Initial" and "Mark Incremental" just once not at table level. I am yet to find an article that shows how it can be done for multiple tables.ThanksSreejith

SQL 2012 cannot execute SSIS 2012

Posted: 24 Jul 2013 08:47 PM PDT

Hi to everyone,I have the issue below: I have an ssis package built in VS 2008, which is being called from SSMS 2008 through an sql job. The job runs with an sql proxy account and is being executed normally.On another server with SQL 2012, the same ssis package has been upgraded with VS 2010 to 2012, the job has been generated from 2008 and has been setup to be called through SQL 2012 with the new parameters and, even though the ssis package is executed normally in VS 2010, when I call it through the job it won't be executed, and I get the following error: "Exception has been thrown by the target of an invocation." This seems to be a security issue. The package is also executed successfully through command line (C:\Windows\system32>dtexec /FILE "\"E:\Package.dtsx\"" /CHECKPOINTING OFF /REPORTING EW).Let me inform you that the sql proxy account has been configured to the new server and the job runs with this account.I would appreciate any help and directions.Thanks a lot.

Maintenance Plans missing

Posted: 25 Jul 2013 12:41 AM PDT

A third party has updated a SQL Server 2005 installation to SQL Server 2012. I am maintaining the SQL Server DB's and front end remotely. I wish to recreate the DB maintenance plans and jobs, however these are no longer available in SQL Server Management Studio. I suspect the third party did not install Integration Services and or Analysis Services. Am I able to post install these services and how as I will need to advise the third party. Any assistance gratefully received.Thank youNeil

SQL 2012 and SQL 2008 R2 Instances on the same cluster

Posted: 24 Jul 2013 05:30 AM PDT

We need to include both a SQL 2008 R2 and SQL 2012 instance on the same Windows 2008 R2 cluster. Has anyone done this before? Caveats? Worked like a charm/don't touch with a 10 foot pole?Thanks in advance for any input you can provide!

Always On Availability Group - Log Send Rate Horrible

Posted: 26 Sep 2012 03:33 AM PDT

Hi -We've been using AlwaysOn Availability Groups for a few months. Prior to Always On, we leveraged SQL 2008 R2 Async mirroring.We have a dedicated 10 GB pipe between the two servers. The servers sit physically next to one another on a 10 GB Network.The SQL mirroring (in SQL 2008 R2) was able to maintain the mirroring process.Well - the performance of AlwaysOn is NOT good. The log_send_rate is absolutely horrible. The best rate we see if 11 MB. But normally the rate is 2 MB.Has anyone experienced the rate become so slow that the status becomes unhealthy and the database has be removed and reconfigured w/in the availability group?

Query performance and SSIS package

Posted: 24 Jul 2013 09:29 PM PDT

Hi I am retrieving data from 3 tables. Total no of rows will be 19 million.After getting this, I am inserting into staging table in SSIS dataflow task.It is taking 35 minutes to finish and doing MERGE on target table.It is again taking 20 minutes. How can I reduce this time?If I run the query in SSMS, its taking just 1.5 minutes to insert into #temp table.

what is the use of IDENTITY data type?

Posted: 24 Jul 2013 06:05 PM PDT

CREATE TABLE Production.Categories(categoryid INT IDENTITY(1,1) NOT NULL,categoryname NVARCHAR(15) NOT NULL,description NVARCHAR(200) NOT NULL DEFAULT (''))This is my create table statement in this [categoryid INT IDENTITY(1,1) NOT NULL] what is the use of IDENTITY data type

did anyone do SQL MCSA certification?

Posted: 24 Jul 2013 06:37 PM PDT

Did anyone do MCSA certification .I would like to know how to prepare for those exams as there are three exams for MCSA.which material to refer? which book to refer?are there any online courses for this subject? I would like to know how you cleared your MCSA what are the efforts you have put to pass the exam?

Red Gate Hosted SQL Monitor

Posted: 24 Jul 2013 04:01 AM PDT

I couldn't find any other place that made any more sense..For those of you who have used the Red Gate SQL Monitor package I'm betting you loved it, I know I did.I have been patiently awaiting the hosted version of that tool to be released by Red Gate.So my question is does anyone have any information on when that might be released?CEWII

SQL 2012 Cluster failure during Add node

Posted: 05 Jun 2013 06:48 AM PDT

I installed a new SQL 2012 failover cluster Enterprise on a 4 node cluster. When I am trying to run Add node on any of the other 3 nodes, the installation fails with the following error:While running 'Update HADRResource'gives error:Status: Failed: see logs for details Reason for failure: An error occurred for a dependency of the feature causing the setup process for the feature to fail. Next Step: Use the following information to resolve the error, uninstall this feature, and then run the setup process again. Component name: SQL Server Database Engine Services Instance Features Component error code: 0x86D80018 Error description: There was an error to lookup cluster resource types. Error: There was a failure to call cluster code from a provider. Exception message: Generic failure . Status code: 4104. Description: .Please help in resolving this error.

Default system_health XE shows security_error_ring_buffer_recorded ImpersonateSecurityContext

Posted: 24 Jul 2013 04:32 AM PDT

So I stumbled across this while playing with XE, and not sure if it's an issue or not. If I watch the live data for the default system_health Extended Event I see nothing but these:Event: security_error_ring_buffer_recorded api_name:ImpersonateSecurityContextcall_stack:0xCE7C8CDEFE070000AC878ADEFE07000005DE8ADEFE07000088468ADEFE0700004D3B8ADEFE070000447D8CDEFE070000803DC8E7FE070000643BC8E7FE0700006739C8E7FE0700000F2FCAE7FE0700005037CAE7FE0700005D2CCAE7FE070000883FCAE7FE0700002D6583770000000021C5967700000000calling_api_name:NLShimImpersonateerror_code: 5023id: 0session_id: 120timestamp : timestamp 595798There do not appear to be failed logins in the error log.This is on a failover cluster that is using two linked servers, which is what I sort of suspect but have no hard evidence at this time. For the linked server config, a list of logins is mapped to specific remote login, and for the section below "For a login not defined in the list above, connections will" the radio button Be made using the login's current security context is selected.Pointers on how to narrow this down to the problem login are appreciated.EDIT: for the SPID's I have checked, they are all SQL logins. I found some articles that suggest this can be due to orphaned domain users, but that does not appear to be the case for me.Thanks,Jared
Posted by at No comments:

[T-SQL] audit my code for current request_id?

[T-SQL] audit my code for current request_id?

audit my code for current request_id?

Posted: 24 Jul 2013 10:54 PM PDT

Hai friends, I made on web appplication dependscreate table onward_joureny(joureny_id int identity,departuredate datetime,from varchar(100),to char(100),is_draft varchar(100))create table users(user_id varchar(100) PK,username varchar(50),password varchar(50))create table travel_request(request_id int identity,user_id varchar(100) fk references users(user_id),total_amount)if new requestId means only my data ll shown......old once not shown be here is my code correct ah?--select * from onward_journey where request_id = 58 order by departuredate--select * from travel_requestcreate procedure bindata( @username varchar(20))as begindeclare @user varchar(20), @request varchar(10)select @user=a.user_id ,@request=max(b.request_id) from travel_request b inner join users a on a.user_id = b.user_id where a.username=@username group by a.user_idif exists (select * from travel_request where request_id = @request)select onward_journey_id,convert(varchar, departuredate, 106)as DepartureDate,from_location,to_location, case metro when 'Yes' then 'Yes' else 'No' end as Metro,case Trans_All when 'Yes' then 'Yes' else 'No' END as transit,case when mode_of_travel like 'Ai%' then mode_of_travel when mode_of_travel like 'Tr%' then mode_of_travel when mode_of_travel like 'Bu%' then mode_of_travel when mode_of_travel like 'Ca%' then mode_of_travel when mode_of_travel like 'Au%' then mode_of_travel when mode_of_travel like 'lo%' then mode_of_travel end as Mode,case when mode_of_travel like ('ai%') then seat_type when mode_of_travel like ('tra%') then seat_type when mode_of_travel like ('Bu%') then seat_type when mode_of_travel like ('ca%') then seat_type when mode_of_travel like ('au%') then seat_type when mode_of_travel like ('loc%') then seat_type end as SEAT,no_of_days as noofdays,other_details from onward_journey inner join(select a.user_id, a.userName, b.request_idfrom users ainner join travel_request bon a.request_id = b.request_idwhere a.username = @username)xxonxx.request_id= onward_journey.request_idwhere xx.username=@usernameand xx.request_id = @requestend

Audit log for INSTEAD OF DELETE trigger

Posted: 24 Jul 2013 03:23 AM PDT

Hi,I'm trying to write an INSTEAD OF DELETE trigger that also logs some data about the attempted delete. It doesn't have to be super-restrictive across the board such as trying to prevent deletes/truncate by sa or anything like that. We just want to have any routine delete operations stopped. Additionally, I would like to log as much as possible about when the delete attempt occurred and who attempted it.My code is below. The trigger successfully stops the delete attempt, but when I check the log table, no records have been inserted. Could someone help me find out what is stopping the log record insert, and how I can fix it?Thanks for any help,webrunner[code]/****** Object: Trigger [dbo].[trNumber_D] Script Date: 07/24/2013 12:16:06 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[trNumber_D] ON [dbo].[Numbers] INSTEAD OF DELETEAS-- Log attempted delete info. IF EXISTS ( SELECT 1 FROM deleted ) BEGIN SET NOCOUNT ON; INSERT INTO Number_Log ( ID, Number, Date_Inserted, Date_Delete_Attempted, UserName ) SELECT d.ID, d.Number, d.Date_Inserted, GETDATE(), SYSTEM_USER FROM deleted d -- Deletion from this table is not allowed. RAISERROR('Deletion of records from Numbers is not allowed.', 16, 1) RETURN; END[/code]

tsql function with dynamic number of parameters?

Posted: 24 Jul 2013 08:57 PM PDT

Hi,in a SELECT, I would like to have a function which returns the minimum of dynamic list of values, like this:select GetMin(4,3) => returns 3select GetMin(4,3,1) => returns 1select GetMin(2,5,0,null,7) => returns 2Do you have an idea, how to code the dynamic parameter list of GetMin?It's like COALSECE, is there a source code available of this build-in function?thank you for your help!Ralf

How to code for already exist date message in my porcedure?

Posted: 24 Jul 2013 06:27 PM PDT

Hai friends , i m creating web application behind using sql server 2000these is my table structure:create table journey( joureny_id int identity, user_id varchar(100) fk references users(user_id), departuredate datetime, from varchar(100), to char(100),is_draft varchar(100))create table users(user_id varchar(100) PK,username varchar(50),password varchar(50))in users table:===========insert into users values('002258','ram','rama@123')in travel_request============insert into travel_request(user_id , departuredate,from,to,is_draft)('002258','01-jul-2013','A','B',' ')insert into travel_request(user_id , departuredate,from,to,is_draft)('002258','02-jul-2013','c','D',' ')now My requirement is:================1)when the same user_id is trying to insert date for already exist date means want to show error mesaage whe n i press the button of draft?2)if is it new date means update colmun depends on request_id " is_draft='Y' " (i.e allow to draft)how to do that?

Is it possible to return a value from a SP like this?

Posted: 24 Jul 2013 06:37 AM PDT

Or should I really be using a function? That's what I was attempting at first, but got pretty stumped on making it dynamic. I'm trying to take the code to return a median value form here ( http://sqlblog.com/blogs/adam_machanic/archive/2006/12/18/medians-row-numbers-and-performance.aspx ) and turn it into a callable SP/FN. Or if anyone has another suggestion, I'm open to whatever. Thanks[code="sql"]USE [Utilities]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET NOCOUNT ONGOalter PROC [dbo].[ReturnMedian] @median sysname,@tablenm sysnameASdeclare @sql nvarchar (max)set @sql = 'WITH MEDCTE (ID, MED) AS (SELECT Id, AVG( ' + QUOTENAME(@median) + ')FROM( SELECT Id, ' + QUOTENAME(@median) + ', ROW_NUMBER() OVER ( PARTITION BY Id ORDER BY ' + QUOTENAME(@median) + ') AS RowNum, COUNT(*) OVER ( PARTITION BY Id) AS RowCnt FROM ' + QUOTENAME(@tablenm) + ') xWHERE RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)GROUP BY IdSELECT MED FROM MEDCTE'--EXEC (@SQL)--PRINT (@SQL)--SELECT (@SQL)--declare @tempmed table (--TEMPMEDFLOAT float--)--insert into @tempmed exec sp_executesql @sql, N'@sql float', @sql--RETURN SELECT TEMPMEDFLOAT FROM @tempmed[/code]

Using DelimitedSplit8k against a Space Delimited Field - Problems

Posted: 24 Jul 2013 07:14 AM PDT

Hi All - I came across Jeff Moden's Splitter Code earlier today and have been trying to run the function against a space delimited field in my database. When running the function against the function does not seem to recognize the space as a delimiting character.[code]Sample DataCoreID ComputerID ResultsText1 1456 192.168.1.1 192.168.1.2 192.168.1.32 1458 192.168.1.8 192.168.1.6 192.168.1.5[/code]Here is what I am running against this table:[code="sql"]Select sd.CoreID, sd.ComputerID, sd.ResultsTextFrom SampleData sdcross apply dbo.DelimitedSplit8k(sd.ResultsText, ' ')[/code]Additionally I have tried the following and it did not work either:[code="sql"]Select sd.CoreID, sd.ComputerID, sd.ResultsTextFrom SampleData sdcross apply dbo.DelimitedSplit8k(sd.ResultsText, SPACE(1))[/code]The output that results from both of these is below (as you can see it is not splitting out the field at all):[code]CoreID ComputerID ResultsText ItemNumber Item1 1456 192.168.1.1 192.168.1.2 192.168.1.3 1 192.168.1.1 192.168.1.2 192.168.1.32 1458 192.168.1.8 192.168.1.6 192.168.1.5 1 192.168.1.8 192.168.1.6 192.168.1.5[/code]Any help would be greatly appreciated. Not sure what is not working here....

handle data which contains space

Posted: 24 Jul 2013 09:10 PM PDT

Hi all,I am having table JobDetail whcih contains jiobname as a one of column.here the data entered from front end is not correct because of space issue. for example if job name is Software engineer then it might be entered single space or sometimes multiple spaces between "Software" & "engineer". but while selecting i want only once.I am using distinct but giving wrong output. Please suggest any idea.thanksAbhas

Temporary Functions?

Posted: 31 Aug 2010 09:06 PM PDT

I've found myself creating loads of functions recently which are typically used for one specific task and rarely required again. In the same way that temporary tables can be created can temporary functions be created? - i.e. you specify the code to create it, query using it then when you close the window it's gone. It doesn't clutter up the DB this way... If so, how would one alter the existing syntax:CREATE FUNCTION [dbo].[f_functionname](paramaters)Cheers!

Case statement

Posted: 24 Jul 2013 08:35 AM PDT

I would like use case statement in select .for example, if rate =100, then 1, if it is null, then 1, and if it is <>100, it is 0,how to write the select case statement in best way?Thanks

Help with query

Posted: 24 Jul 2013 07:13 AM PDT

CREATE TABLE #NewTempLogins ( CompanyCode varchar(12),Login VARCHAR(MAX))CREATE TABLE #TempLogins ( ID INT IDENTITY(1,1), CompanyCode varchar(12),CompanyName VARCHAR(1000),Login VARCHAR(MAX))DECLARE @listStr VARCHAR(MAX)INSERT INTO #TempLogins (CompanyCode,CompanyName,Login)SELECT 'company1','MyCompany','psb'UNION ALL SELECT 'company1','MyCompany','lll'UNION ALL SELECT 'company2','MyCompany2','bbb'UNION ALL SELECT 'company2','MyCompany2','rrr'UNION ALL SELECT 'company2','MyCompany2','test1'DECLARE @StratTrackCount INT = 1DECLARE @TrackCount INT = (SELECT MAX(ID) FROM #TEmpLogins)SELECT @TrackCount select distinct * from #TEmpLogins order By CompanyCodeDECLARE @CompanyCode VARCHAR(12)DECLARE @Login VARCHAR(MAX)WHILE (@StratTrackCount <= @TrackCount)BEGINDECLARE @MyCursor CURSORSET @MyCursor = CURSOR FORSELECT CompanyCode FROM #TEmpLoginsOPEN @MyCursorFETCH NEXTFROM @MyCursor INTO @CompanyCodeWHILE @@FETCH_STATUS = 0BEGINSELECT @listStr = COALESCE(@listStr+',' ,'') + LoginFROM #TEmpLogins where CompanyCode = @CompanyCodeINSERT INTO #NewTempLogins (CompanyCode,Login)SELECT DISTINCT @CompanyCode,@listStr from #TEmpLogins where CompanyCode = @CompanyCode FETCH NEXT FROM @MyCursor INTO @CompanyCode SET @StratTrackCount = @TrackCount + 1 END CLOSE @MyCursor DEALLOCATE @MyCursor END SELECT distinct * FROM #NewTempLogins order By CompanyCode drop table #TEmpLogins drop table #NewTempLogins I want to display the results like below CompanyCode Login company1 psb,lll company2 bbb,rrr,test1

Partitioned table JOIN query performance

Posted: 24 Jul 2013 02:54 AM PDT

I have five large (500m+ records) tables with differing informaton and column structures. However, each of these tables have five fields in common. Coincidentally, the combination of those five fields makes up the logical key for each table. As such, the tables all have a primary key built on those five fields. I have to run a daily report that draws some data from each of those tables, and when I run the SQL for this report against query analyzer, it's clear that it is using these clustered indexes appropriately and doing merge joins and/or nested loops to get it done. The query takes about 90 minutes to complete.Then I set about trying to leverage partitioning to improve performance. I dropped the primary key on each of the tables and recreated all of the primary keys with the same five fields, but all on the same partition scheme - effectively aligning all of the tables and their clustered indexes on the same partition scheme. The scheme uses the same one of those five fields as it's partition key (a date field, if that matters) for each table.The query now runs in about 45 minutes - not bad, right? However, in the original execution plan, all of the time was spent doing the clustered index scans - about 90% of it. In the new execution plan, only 10% of the time is spend scanning indexes, and 80% is spent sorting the data between the index scan and the join (a step which previously didn't exist). Is there a way I can eliminate the sort by structuring my query differently, or is this intermediate sort a natural product of pulling data from partitioned tables?
Posted by at No comments:

[SQL Server 2008 issues] Fuzzy searching

[SQL Server 2008 issues] Fuzzy searching

Fuzzy searching

Posted: 23 Jul 2013 11:55 PM PDT

Hello everyone, I'm having trouble finding a way to use fuzzy searching (specifically Levenstein) in my database. For now, I've been using an optimized T-SQL implementation of the Levenstein algorithm that I found on another forum. However, it is way too slow for me to use. I'm unable to use CLR functions on my server due to "memory pressure" issues (My dba is nervous about using the -g start up parameter to allocate additional memory). In addition, I'm unable to use Master Data Services' Similarity function. I'm using SQL 2008 R2 Standard, which doesn't support MDS.Can anyone think of another way that I can use fuzzy searching to compare entries in my database?Thanks in advance.

SSIS list file names in directory

Posted: 09 Apr 2013 04:12 AM PDT

Hello All,I am trying to create an SSIS package that will look at a directory, get a list of all the file names in the directory, and enter them into a table. Any advice to accomplish this?Thanks!Robert

MSDTC errors - SQL server stopped responding to any request

Posted: 07 Jul 2013 10:20 PM PDT

HiRather a strange issue with multiple SQL server instances - SQL server abruptly not responding to client applications for a brief period (5-15 minutes), but it's started responding as soon as these errors raised on SQL error log. SQL version: 2008 R2 enterprise RTMOS version: Windows server 2008 R2 Enterprise, Service pack1Client applications (IIS7) displayed with error [b]8004e024 [/b]- COM+ unable to create the instance when this issue occurring. [b]MessageError: 8510, Severity: 20, State: 1.MessageEnlist operation failed: 0x8004d00e(failed to retrieve text for this error. Reason: 15100). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager.[/b][b]MessageError: 8509, Severity: 16, State: 1.MessageImport of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(failed to retrieve text for this error. Reason: 15105).[/b]Any idea what's causing this issue? Any help on this highly appreciated. ThanksPS

Unknown Traces - Keep restarting when stopped

Posted: 24 Jul 2013 05:14 PM PDT

We have an application that detects when tracing is enabled on our SQL server and it has been requested that the tracing is disabled to attempt to troubleshoot an issue with the application.I've run: SELECT *FROM fn_trace_getinfo(default);GOand getting: traceid property value1 1 11 2 NULL1 3 NULL1 4 2013-07-26 16:00:01.1071 5 13 1 13 2 NULL3 3 NULL3 4 2013-07-26 06:12:01.2773 5 1and:Select * from sys.traceswhich gets:id status path max_size stop_time max_files is_rowset is_rollover is_shutdown is_default buffer_count buffer_size file_position reader_spid start_time last_event_time event_count dropped_event_count2 1 NULL NULL 2013-07-26 06:12:01.057 NULL 1 0 0 0 1088 1 NULL 221 2013-07-25 06:12:01.120 NULL 0 03 1 NULL NULL 2013-07-26 06:12:01.277 NULL 1 0 0 0 1088 1 NULL 223 2013-07-25 06:12:01.207 2013-07-25 15:49:45.433 1004 0When I run:Exec sp_trace_setstatus @traceId=2,@Enabled=0 and Exec sp_trace_setstatus @traceId=2,@Enabled=2to stop and remove the trace, it just starts up again automatically.I've checked if there are any jobs running under the SQL server agent but there is only the syspolicy_purge_history job and its history is not showing it running.Is there anything else to check to try and find what is running these traces??

DELETE FROM Table WHERE PKID = 0 -runs for over 5 min without completing

Posted: 24 Jul 2013 05:45 AM PDT

We have a table, and just noticed if we try to delete a row from it (for example the first row) it runs for over 5 minutes without deleting (finally just killed the process).Restarted the server, just in case.Created a copy of the table, and I can delete just fine from that.Other tables work fine in that database, it is just this particular table.Only has 500 or so rows.It has a PK clustered index on it and that is it. (two constraints for default values of 0 for a bit field and getdate for a datetime field)I've never encountered this before.Additionally, it works fine on our other database servers, just our dev environment is having this issue.DBCC CHECKTABLE didn't find anything wrong with that given table.Any thoughts?Running the DELETE statement begins to show wait types for CXPacket after a while.... CPU jumps to 40% while it runs... and moves around (normally it is at 2%).PAGEIOLATCH_SH and CXPacket come up in wait type However, they don't stay it just suspends the task and juggles between suspended and running.

TDS DONE Message

Posted: 24 Jul 2013 05:34 AM PDT

Hello,I am facing a "problem" with the TDS network protocol. When I send a batch like the one below I receive back from SQL Server a lot of packets with the DONE message saying that a statement was executed. I understand that for each while iteration this message is generated. The problem is when I execute this command over a WAN link I can allocate all the network bandwidth of my WAN link for these messages.[i]declare @i intset @i = 0while @i < 10000begin set @i = @i + 1end[/i]From what I could find I can not disable this DONE message. Does anyone know if I can disable this message?I can execute this batch directly in the server, but I wish to know if I could disable it.Thanks,Thanks,

Indexing for Pivot Performance?

Posted: 24 Jul 2013 08:31 AM PDT

Hi SSC,I've got a table which is wasting a lot of space in it's current narrow format and I'd like to pivot it out. The table contains about 44 million rows. The example below is a scaled down version of what I'm dealing with. The idea being to pivot out the [Measure] values into their own columns. In the real data there are about 12 different measures which will become their own columns.My question is, is there a way to index the underlying table, or perhaps an analogous method to Pivot which would get index seeks or better performance over a data set of this size?[code="sql"]--Setup raw tableif object_id('tempdb.dbo.#Raw') is not null drop table #Rawcreate table #Raw( Id varchar(25), TradeDate int, Measure varchar(100), Value float primary key clustered (Id, TradeDate, Measure))--Populate sample data;with nums as --numbers table( select num = row_number() over (order by [object_id]) from sys.objects with (nolock) ), measures as --measures( select Measure = 'Alpha' union all select 'Beta' union all select 'Gamma' union all select 'Delta' union all select 'Epsilon')insert into #Raw( Id, TradeDate, Measure, Value)select Id = ids.num, TradeDate = 40000 + dates.num, Measure = m.Measure, Value = checksum(newid()) % 10000from nums idscross join nums datescross join measures mwhere ids.Num between 1 and 100/*********************** Actual Statement ***********************/select Id, TradeDate, Alpha = max(Alpha), Beta = max(Beta), Gamma = max(Gamma), Delta = max(Delta), Epsilon = max(Epsilon)from (select Id, TradeDate, Measure, Value from #Raw) srcpivot (max(Value) for Measure in (Alpha, Beta, Gamma, Delta, Epsilon)) pvtgroup by Id, TradeDate[/code]

Executing Reporting Services reports from SQL Server

Posted: 24 Jul 2013 08:01 AM PDT

Hi,I hope someone can help me. I work in SQL Server (T-SQL), and I develop reports in SQL Server Reporting Services. I am looking for a way run SSRS reports from a T-SQL Stored Procedure. I have researched it enough to know its possible by writing a CLR procedure that uses the SSRS WebServices API. However, I don't program in either VB.NET or C#.NET and don't have Visual Studio (other than the small portion that comes with BIDS).I am running SQL Server 2008R2 x64. Does anyone have a CLR procedure already compiled to do this, that they can share?Thanks,David R.

Database hosting solutions

Posted: 24 Jul 2013 04:03 AM PDT

All right folks, I'm looking for various popular database hosting solutions. Preferably places that use their own images with fully functional database engines (unlike SQL Azure that seems to strip nearly everything off an image..)Does anyone have any recommendations?Erin

Changing the system date on a server running SQL Server instance

Posted: 24 Jul 2013 06:53 AM PDT

I am recently supporting an application that can be classified a 'rules engine' and the developers and business rely on testing scenarios whereby it is required to move the date to the future. Once that occurs, anyone is pretty much prevented from accessing the server remotely, as kerberos authentication blocks due to out of sychronization with system time(I think 5 minutes off).Has anyone been exposed to this scenario (rolling dates forward, and back on server), and what are some of the negatives in doing so in regard to SQL Server ? It makes job history difficult to dicipher, but I also wonder what negatives from an engine it may have. i.e., if windows patches applied, group policy, txn log history...Thanks

Edit multiple stored procedures

Posted: 24 Jul 2013 02:34 AM PDT

I need to create 5 test databases by copying the live db's. There are many stored procedures in each db. Each sp is told which db to use, Use [NEHEN_prod]. I've been told that I have to go into each sp, about 50 per db, and change the NEHEN_prod to NEHEN_test. Is this necessary? If so, is there an easy way of doing it?

SSRS Newbie -- Question on combining data from different instances in 1 report

Posted: 24 Jul 2013 05:54 AM PDT

Hi All, I'm just learning on SSRS and needed clarification. If I want to create a simple report that outputs a list with the names of my current sql server instances and the version information, how do i set up the report to pull the information from different servers? Most tutorials show beginners how to create a simple data source/dataset but nothing on combining the data of multiple instances into 1 report.Thanks for any input.

Running SSRS report from SSIS package

Posted: 27 Feb 2009 04:28 AM PST

I have an SSIS package. It updates a table in SQL Server 2005 database. There is another SSRS report that presents my final table and I run it and export it into Excel.Is it any way after SQL Query task and Store procedures are being finished in my SSIS package, I add another object to run my SSRS report and export it to Excel format?

How get some informations about MSSQL (options , memory...)?

Posted: 24 Jul 2013 01:41 AM PDT

Hi everybody , I need help :1) I want to know the value of options "Partitioning" and "Bit-Mapped" (In oracle , informations are iin v$option table) An equivalence to MSSQL ?2) Information about the memory architecture of the DB ( In Oracle the equivalence is SGA : v$sga ....)I'm new in sql server administration , so sorry :/

Change Data Capture.

Posted: 24 Jul 2013 04:19 AM PDT

I am trying to enable Change Data Capture on SQL Server 2008 R2 DB.When I enable CDC on a table by using this SP sys.sp_cdc_enable_table the Capture Job is not getting created....did some search about it and found out thatCapture job wont work on DB 's where transactional replication is enabled..Is there a workaround for enabling CDC on Database where Transaction Replication is active ?

Can someone help me with a script please?

Posted: 24 Jul 2013 12:35 AM PDT

Hi,I got very nice assistance last time i had an issue on this site. Can anyone assist me? I would like to know, is there a script i can run the re indexes all the tables in a sql database? Reasons:I have clients running sql databases.Some of them start to complain about speed. The servers is fine, i cjhecked them out. Databases from 1.0 - 18 GB in size. After i re-index each table one by one, it seems a lot better. It takes forever though. Is there a script that can assist me?Thank You!

Currious thing about Count

Posted: 23 Jul 2013 10:48 PM PDT

I accidently had an unfinished piece of SQL code in my query window during execution. The code was[code="sql"]select count(*)[/code]Now to my supprise it returned 1. Anyone know why it returns 1? The only thing i can think of is that its counting itself./T

SQL dependency

Posted: 24 Jul 2013 02:42 AM PDT

Is there any free script or software for SQL dependycy?Red-gate has one but need to purchase.

Update table values if corresponding values change in another table

Posted: 23 Jul 2013 10:03 PM PDT

Hi,I am very new to SQL and really dont know how to phrase my question. There are 2 tables linked through a primary key and if the values in one table change, the corresponding values in another table should be changed and reflected accordingly.Does someone know what logic I need to apply for this to work ? Do I have to create a primary key-foreign key relationship and then create a trigger on the other table on which the values need to be updated ?The values in the table will be changed through a webpage.Any ideas would be appreciated.Thanks.

SSIS to Excel export error

Posted: 24 Jul 2013 01:18 AM PDT

An Script task in SSIS fails with error saying excel cant access 'C:\....\Windows\Temporary Internet Files\Content.MSO\'. Any resolution to stop this error?

issue using the Konestan File Watcher Task in SSIS 2008 to move files between network folders

Posted: 24 Jul 2013 01:03 AM PDT

Hi,I am using the Konestan File Watcher task for SSIS 2008 within a For Loop Container to watch a specific folder and move any files that are "dropped" into the folder to another location. The For Loop Container is set to loop infinitely.This works perfectly on a local drive, files that are randomly dropped into the folder are moved to the destination.However when I move the path to a network folder, existing files within the network folder are processed but any new files dropped into the network folder subsequently are not.The network is an Active Directory domain Any advice greatly appreciated. Thanks

SSIS Package with a Send Mail Task works perfectly within BIDS but does not work in a SQL Agent Job.

Posted: 24 Jul 2013 12:03 AM PDT

Hi,I have a SSIS Package with a Send Mail Task in it. This works perfectly within BIDS...I can get the desired email. However when I put this SSIS package in a SQL Agent Job, the Send Mail Tasks does not work. I am getting the below error :An error occurred with the following error message: "Failure sending mail. System.Net.WebException: Unable to connect to the remote server System.Net.Sockets.SocketException: An attempt was made to access a socket in a way forbidden by its access permissions 10.38.58.184:25"What needs to be done to get my Send Mail Tasks to send the desired emails while executing as a SQL Agent Job?Any and All help will be greatly appreciated...Thanks

space check

Posted: 23 Jul 2013 10:40 PM PDT

how to check the free space on clustered drives which are having mountpoints?

reinitialize subscription cancel

Posted: 23 Jul 2013 10:44 PM PDT

One of our IT staff has accidently initiated a reinitialize subscription on a 150GB database via a VPN link. Anybody know if this can be cancelled in any way.:w00t:Thanks

split mirroring

Posted: 23 Jul 2013 09:55 PM PDT

is any technique split mirroring in sql ? if yes can any one explain how to do ?

Lock Wait

Posted: 23 Jul 2013 09:40 PM PDT

Hi,How to find out the total amount of time spent on lock wait event using T-SQL Query

sql server agent is not sending dbmail

Posted: 23 Jul 2013 08:27 PM PDT

Hi everyone, i've got a problem with the agent.I've create a procedure to check the memory and, in case is too low, to send an alert via sp_send_dbmail,this procedure works if I execute it manually, but when I start the job via agent, nothing happens, no error, no mail, nothing.it is my understanding that the only difference is the user that start the procedure, but both are sysadmin.The job success with no error, have u got any ideas about the solution?many thanks!!! :)
Posted by at No comments:
Subscribe to: Comments (Atom)

Search This Blog