Thursday, June 13, 2013

[SQL Server 2008 issues] Modifying a system stored procedure

[SQL Server 2008 issues] Modifying a system stored procedure

Modifying a system stored procedure

Posted: 12 Jun 2013 07:37 AM PDT

I have inherited maintenance of a SQL Server (2008), and I want to modify some of the system stored procedures. These are user-defined system stored procedures (for example: sys.sp_customproc). I can only assume they were created as system procedures so they could be shared across multiple databases? But regardless, I need to modify them.Here is an example of one of them.[code="sql"]USE [msdb]GO/****** Object: StoredProcedure [sys].[sp_dbmmonitorhelpmonitoring] Script Date: 06/12/2013 13:16:52 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [sys].[sp_dbmmonitorhelpmonitoring] asbeginset nocount onif (is_srvrolemember(N'sysadmin') <> 1 ) begin raiserror(21089, 16, 1) return (1) enddeclare @freq_type int, -- 4 = daily @freq_interval int, -- Every 1 days @freq_subday_type int, -- 4 = based on Minutes @freq_subday_interval int, -- interval @job_id uniqueidentifier, @schedule_id int, @retention_period int, @jobname nvarchar( 256 )select @jobname = isnull( formatmessage( 32047 ), N'Database Mirroring Monitor Job' )select @job_id = job_id from msdb.dbo.sysjobs where name = @jobnameif (@job_id is null) -- if the job does not exist, error outbegin raiserror( 32049, 16, 1 ) return 1 endselect @schedule_id = schedule_id from msdb.dbo.sysjobschedules where job_id = @job_idselect @freq_type = freq_type, @freq_interval = freq_interval, @freq_subday_type = freq_subday_type, @freq_subday_interval = freq_subday_interval from msdb.dbo.sysschedules where schedule_id = @schedule_id-- If the frequency parameters are not what we expect then return an error-- Someone has changed the job schedule on usif (@freq_type <> 4) or (@freq_interval <> 1) or (@freq_subday_type <> 4)begin raiserror( 32037, 16, 1) return 1endselect @freq_subday_interval update_periodreturn 0end[/code]When I try to execute it, I get the error:Msg 208, Level 16, State 6, Procedure sp_dbmmonitorhelpmonitoring, Line 46 Invalid object name 'sys.sp_dbmmonitorhelpmonitoring'.My login is 'sa', I am mapped to the user 'dbo' in the [msdb] database. How do I modify this stored procedure?

How to format the data in column in sql server

Posted: 12 Jun 2013 12:38 PM PDT

Hi, Please help me, In my table have a one date column. It contains data in different formats .I want to set all these different formats into a single format. Like... Date 10.12.2012 ---in this '10' is Day and 12 is Month and then Year. 12-10-2012 10.12.2012 2012/10/12

BCP IN a csv file

Posted: 12 Jun 2013 01:40 PM PDT

Hi All,Couldn't find a plain BCP IN syntax for CSV files on the web or in here.Can someone tell me once and for all what is the syntax?Thank you!-MB

cpu Utilization is showing 100 on particular database

Posted: 12 Jun 2013 11:43 AM PDT

hi,WITH DB_CPU_StatsAS(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time(Ms)]FROM sys.dm_exec_query_stats AS qsCROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] FROM sys.dm_exec_plan_attributes(qs.plan_handle)WHERE attribute = N'dbid') AS epaGROUP BY DatabaseID)SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time(Ms)] DESC) AS [row_num],DatabaseName, [CPU_Time(Ms)], CAST([CPU_Time(Ms)] * 1.0 / SUM([CPU_Time(Ms)]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]FROM DB_CPU_StatsWHERE DatabaseID > 4 -- system databasesAND DatabaseID <> 32767 -- ResourceDBORDER BY row_num OPTION (RECOMPILE); On single database cpu utilization 100 % .How to decrease cpu utilization on that database.?

Import flat files into SQL Server (apart from SSIS)

Posted: 12 Jun 2013 09:41 AM PDT

Which tool do you prefer to use to import flat files into SQL Server tables and why? We're trying to migrate some DTS packages into Stored Procedures but some contain data pumps which do these kind of imports.We've had lots of problems creating SSIS packages due to the lack of knowledge, that's why we're looking for alternatives.Thanks for all the ideas we might get.

Using SSIS To Import Data From A File To A Database

Posted: 12 Jun 2013 04:07 AM PDT

I am new to SSIS and I have been tasked to do use SSIS to import data from a file to our database.The file is delimited and has 46 columns. The file can contain 1,000,000 records. The test file I have contains 700,000.Each record in the file contains an ID. I need to take that ID, go to the database and see if the record already exists in the database. If it exists I need to update the record in the database. If it doesn't exist add the new record to the database.So far I set up a Flat File Source and I can see all of my columns and data. I'm not sure where to go from here.

Exec msdb.dbo.sp_send_dbmail (zipped and password protected) issue

Posted: 12 Jun 2013 07:42 AM PDT

Is it possible to send a csv file password protected and zipped and sent with msdb.dbo.sp_send_dbmail?

2008 R2 install (Sharepoint integrated mode)

Posted: 12 Jun 2013 04:52 AM PDT

How can set the sharepoint integrated mode (on) for Reporting Service if Reporting Service is already install on the server, would the DVD has to be loaded again or is there a value with I can turn on?

Verify multiple bak files via tsql

Posted: 12 Jun 2013 03:26 AM PDT

I backed up a database via Red Gate and used the sqb2mtf to convert the backup to be recognized by SQL (.bak file). In doing so, this created 9 bak files. Now I am attempting to VERIFY the backups via TSQL and am not having any luck.The command I am attempting to run is:RESTORE VERIFYONLYFROM DISK = 'F:\Temp\FULL_DbBackup_00.bak'In doing so I get the following error:Msg 3132, Level 16, State 1, Line 1The media set has 9 media families but only 1 are provided. All members must be provided.Msg 3013, Level 16, State 1, Line 1VERIFY DATABASE is terminating abnormally.I have been Googling this with no luck and am needing to verify all 9 of these bak files (00-08). I found that there is a WITH FILE = n switch that I can not get to work and I found where someone posted some syntax on using the backup command to split a backup into multiple files.Can someone please advise on the easiest way to do this? Any and all help will be greatly appreciated!!Thanks in Advance...

sql service startup account

Posted: 12 Jun 2013 05:18 AM PDT

Hi Team,I have sql service account which does not have sa rights but have all required domain rights and there is seperate account for agent service and this account has sa rights.When backup job runs it does not delete the old backup files.When I grant sa rights to sql service account it does.So my question is does sql service account always need 'sa' rights?

3 instances of SQL on 1 vm VS 3 separate vm's

Posted: 12 Jun 2013 05:36 AM PDT

We have a situation on one of our tier 3 sql servers where we want to move it to a vm. It currently has SQL Server 2005, 2008 R2 (2005 is named). We want to move this to a vm and upgrade some of the databases to 2012. So the thought was to have 3 separate VMs instead of just 1 with 3 instances. Thoughts?

Need a help in backup Encyption Certificate

Posted: 12 Jun 2013 05:27 AM PDT

HI ..I am having trouble with keeping backup of encryption certificate.I created database encryption key with folowing query.[b]Use TestGO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE SQLServerTDECertificate GO[/b]but now SQL server not allowing me to do the backup.[b]BACKUP CERTIFICATE SQLServerTDECertificate TO FILE = 'C:\temp\TDE\SQLServerTDECertificate.cert' WITH PRIVATE KEY ( FILE = 'C:\temp\TDE\SQLServerTDECertificateKey.key', ENCRYPTION BY PASSWORD = '!@90jujujuijkop*&^jni@!') GO[/b][b]error message:[/b]Msg 15151, Level 16, State 1, Line 1Cannot find the certificate 'SQLServerTDECertificate', because it does not exist or you do not have permission.Please help me how I can do the backup.

Queries are faster with Statistics Sampling rather than FULLSCAN?

Posted: 12 Jun 2013 03:09 AM PDT

I have a really odd issue that I don't understand. We have a 3rd party Excel add-in that talks to another 3rd party application (ERP system) that allows adhoc reporting in Excel for some of our "power users". Over the past couple of months since we've been using the add-in, I've had users report that it is taking a long time to pull all the data into the spreadsheets.Unfortunately, it appears that the way the Excel software works is by sending individual queries instead of building a set and returning that. So it will execute this one statement 2500 times. I can't change how the 3rd party software works. One of the queries that is executed looks like this:[code]SELECT SUM(A.AMOUNTMST) FROM LEDGERTRANS A WHERE ((DATAAREAID IN (@P1) ) AND (((((TRANSDATE>=@P2) AND (TRANSDATE<=@P3)) AND (((ACCOUNTNUM=@P4) OR (ACCOUNTNUM=@P5)) OR (ACCOUNTNUM=@P6))) AND (DIMENSION=@P7)) AND (DIMENSION2_ LIKE @P8 ESCAPE '\' )))[/code]All 2500+ queries look similar to that, but the only thing that changes may be the number of items in the WHERE clause (there sometimes might only be one AccountNum and sometimes there may be 10+ AccountNum's).On that database, we update all STATISTICS with FULLSCAN daily. The LedgerTrans table in our environment has 11 million records.When the users report issues, the only thing I do is type "UPDATE STATISTICS LedgerTrans", which does a sampling. If I look at the details, it only sampled 137K rows, which is only ~1% of the rows.I managed to capture a SQL Profiler trace of the issue to try to compare execution plans, but I'm still confused. Before I do my "fix", the query formatted above had a duration of 2944 in SQL Profiler (which I assume is 3 seconds). After the fix, the duration is 4 for the same query. Attached are the two execution plans that I captured in SQL Profiler using the Showplan XML event.I don't understand how the simple Index Seek/Key Lookup execution plan is slower than the more complicated one with all of the scalars and the same Index Seek/Key Lookup.I don't think the system is under heavy load or blocking when the users report the slowness because it is 100% of the time (for the past 10+ times we've had this issue) resolved immediately by running that UPDATE STATISTICS command. I've use Remote Desktop Services shadowing and watched the add-in slowly progress through it's computations at a rate of 1+ minutes per 1 percent of activity, and in the middle of that worksheet refresh, I ran the UPDATE statistics command and it immediately started doing 1% of the total calculations every second.

Converting a Non Standard Format Number to a Date

Posted: 12 Jun 2013 01:19 AM PDT

I have dates stored on an AS400 platform in the following format that I need to convert to a standard date in SQl so I can use datediff in my select statements. Current Format: CYYMMDD example: 1130531 = 05/31/2013 in this format, the C is either 0 (for 1900) or 1 (for 2000)Desired Format: 05/31/2013All the articles I have found in the forums deal with a properly formated set of digits. Any help would be greatly appreciated!

sqlMonitor alert:sql server instance unreachable

Posted: 12 Jun 2013 03:10 AM PDT

Hi EveryoneOn our monitoring tool sqlMonitor we got an alert called 'sql server instance unreachable' which lasted for about 2 minutes. I have checked the Cpu queue length for this period and it spikes at around a value of 17 and the counter 'Machine:Processot time' peaks 100% and stays there for about 1 minute. How can i find out what caused this as having the data is good but i need a way of analyzing the query that may have caused this ?

How to easily find which of our hundreds of servers has Analysis Services installed?

Posted: 12 Jun 2013 02:12 AM PDT

Hi All,Someone asked the question today "Just how many of our servers have AS installed?" and I didn't know.Out of 300 servers (mainly SQL 2008) I reckon maybe 10 have AS installed.I was hoping I could just fire some t-sql at our central management server and get a result back but I can't find anywhere within SQL that this info is stored?At the moment it's looking to me like it'll be easier for me to do it with a powershell script and just feed that an input file that's lists all our servers and look for either a partial Service or AMO name.Has anyone else had to do this and found a neat way to do it?Many thanks.

Policy management

Posted: 12 Jun 2013 01:59 AM PDT

Hi,I have not created the job syspolicy_purge_history, it has been created by default in sql server 2008.Can anyone please tell me what is syspolicy_purge_history? and Is there any effect , if I disable the job, sorry I am new in sql server 2008.Thanks in advance

Move dbs Identify Jobs

Posted: 10 Jun 2013 08:27 PM PDT

I'm embarking on a project to migrate some dbs in my environment. Moving the db logins etc is straight forward. But how do I make sure the relevant jobs are moved to.Can MSDB be queried or do I need to set up profilwer.

SQL Query

Posted: 11 Jun 2013 08:10 PM PDT

Hi All,I have two parameters start and endweek which shows the measure column weekly from Monday -until sunday.Say 13/05 --- value 10020/05 -- value 20027/05 -- value 300But I need the query to be added weekly with another column commencing from April 1st 2013 until EndweekWeek Total13/05/2013 13/05/2013 weeks value + Week commnecing april 1st 2013 until May 12ths value20/05/2013 20/05/2013 weeks value +Week commnecing april 1st 2013 until May 19ths value27/05/2013 27/05/2013 weeks value +Week commnecing april 1st 2013 until May 26ths valueany help pls? thankks

SQL Server Express - automatic Backup

Posted: 20 Feb 2013 10:50 PM PST

Hi,I have SQL Server 2008 R2 Express Edition on a server.I need to backup my database (small db) on a daily basis.Since Express Edition does not allow automatic backups what can I use to do this backup?thanks

Bcckup & restore

Posted: 11 Jun 2013 11:58 PM PDT

Hi all,Can we restore sql server 2008 databases with sql 2012 database backups. Or backward compatibility is not possible in any version of sql.

Partitioning an existing table in sql server 2008

Posted: 11 Jun 2013 08:54 PM PDT

Hi,I have a table of 600 million data. This table will grow monthly. We do monthly data load to this table.Column which i will use is LoadMonth. this will have the value like '201301','201304','201305'I would like to do a partitioning in this table.Please help me on this, what are the approaches/steps to create the partition.Regards,Karthik.

Select in multiple levels...

Posted: 11 Jun 2013 09:07 PM PDT

Hi,I have a query that now actually does exactly what I want it to and performs well. But the site has still low traffic and small amounts of data.I have never seen examples of queries with more than 2 "select levels" before, here I have "3 select levels", combined with 6 joins.Do you think this is going to be a problem as the site grows to tables with about 10 to 100 thousands rows ?SELECT DistinctCompanyCount, DenseGroup, CatName, CatId, CompanyId, CompanyName, PropertyId, Status, Day, LinkFormat, Updated, VardeFloat1FROM(SELECT MAX(DenseGroup) OVER(PARTITION BY PropertyId) AS DistinctCompanyCount, DenseGroup, CatName, CatId, CompanyId, CompanyName, PropertyId, Status, Day, LinkFormat, Updated, VardeFloat1FROM(SELECTDENSE_RANK() OVER (ORDER BY Categories.CatName,CompProp.VardeFloat1,CP2.Updated DESC,Company.CompanyId) AS DenseGroup,Categories.CatNamn,Categories.CatId,Company.CompanyId,Company.CompanyName,CompProp.PropertyId,Days.Status, Days.Day,OrgInfo.LinkFormat, CP2.Updated,CompProp.VardeFloat1FROM CompanyINNER JOIN CompProp ON Company.CompanyId = CompProp.CompanyId AND CompProp.PropertyId = 2 AND CompProp.State = 1INNER JOIN CompCat ON CompCat.CompanyId = CompProp.CompanyIdINNER JOIN Categories ON CompCat.CatId = Categories.CatIdINNER JOIN OrgInfo ON OrgInfo.UserId = Company.OrgIdLEFT OUTER JOIN Days ON Day.CompanyId = Company.CompanyId AND Days.Day >= '2013-06-10 00:00:00' AND Days.Day < '2013-06-15 00:00:00' LEFT OUTER JOIN CompProp AS CP2 ON Company.CompanyId = CP2.CompanyId AND CP2.PropertyId = 1WHERE Company.State = 1)AS PagingQ1)AS PagingQ2WHERE RankGrupp BETWEEN 1 AND 20ORDER BY CatName,VardeFloat1,Updated DESC,CompanyId,Day

Update stats

Posted: 11 Jun 2013 08:44 PM PDT

Hi ,In Production server we have executed updatestats for a database manually(which contains 5 tables) on Wensday and it took 1hr to comleted for all the tables.For the same database we created a updatestas job to run on sunday and it took around 15 hrs to complete.Can any one tell me how to find the cause for taking long time to run the job.

How to update base on parameter vaues in other table

Posted: 11 Jun 2013 08:55 PM PDT

Hi I have two table details and parameter.I want to update Type_score,Amount_score,Age_score acording to the criteria given in the #para table.Please help me as I need this urgently.drop table #TempCreate Table #Temp(Number Int,Type Varchar(100),Amount Int,Age Int,Type_score Varchar(100),Amount_score Int,Age_score Int)drop table #paracreate table #para(Id varchar(100),Type Varchar(100),valueFrom Int,valueTo Int,score Int)Insert Into #Temp(Number,Type,Amount,Age)Values (1,'Vannila',1000,20)Insert Into #Temp(Number,Type,Amount,Age)Values (2,'Strawberry',2500,40)Insert into #paraValues ('Product','Vannila','','',5), ('Product','Strawberry','','',1), ('Age','',18,25,5), ('Age','',26,30,1), ('Amount','',1000,2000,5), ('Amount','',2001,3000,1)Select * from #TempSelect * from #paraThanks in Advance!

database in suspect mode

Posted: 11 Jun 2013 08:51 PM PDT

Hii tried to put the database in emergency and then put into single user mode and ran dbcc check db with repair_allow_data_loss command but i am getting below error.... can any one helpMsg 7987, Level 16, State 1, Line 1System table pre-checks: Object ID 3 has chain linkage mismatch. (1:5177)->next = (1:3277), but (1:3277)->prev = (1:1289). Check statement terminated due to unrepairable error.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog