Friday, April 26, 2013

[SQL Server 2008 issues] Forgot sysadmin user password

[SQL Server 2008 issues] Forgot sysadmin user password

Forgot sysadmin user password

Posted: 25 Apr 2013 04:31 PM PDT

In a SQL server instance I have only SQL logins with sysadmin permission. Windows authentication has only public permission. So in case if I forget password of the SQL login having sysadmin then how can I recover it or What steps I should take?

BUILIN login in sql server

Posted: 25 Apr 2013 06:59 PM PDT

In sql server login list i have BUILIN\Administartor(has sysadmin) and BUILTIN\Users(only public permission) with other logins too.I think these two are created while installing sql as i have not created it manually. So i want to know if i add a windows authentication login then from which login it will consider permission? I mean does BUILIN\Administartor will override the windows authentication logins of each users in that pc?Also can i delete those Builtin logins?

Sql server start as single user mode

Posted: 25 Apr 2013 04:27 PM PDT

I started sql server instance using single user mode by changing start up parameters in sql server configuration manager.Then when i try to connect to sql server in command line using "sqlcmd -s pcname\instancename" i get following error. So how to solve this?HResult 0x2, Level 16, State 1 Named Pipes Provider: Could not open a connection to SQL Server [2]. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in stance-specific error has occurred while establishing a connection to SQL Server . Server is not found or not accessible. Check if instance name is correct and i f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

Problem With BackupDB for Deleted Info

Posted: 24 Apr 2013 11:15 PM PDT

I have two database AgencyDB(primary DB) and AgencyBackupDB(Deleted Info from AgencyDB by user) and i have this three table in both DB(but there is no relationship between Tables in AgencyBackupDB ).I get confused when I Want to restore Info From AgencyBackupDB to AgencyDB because the sabtHazine Table Have Foreign Key From Hazine And Hazine Also Have foreign Key from HazineGroup.how Can I restore my Info of sabtHazine to AgencyDB???????????? this is my table with test data[code="sql"]CREATE TABLE [dbo].[HazineGroup]( [HazineGroupID] [smallint] IDENTITY(1,1) NOT NULL, [HazineName] [nvarchar](150) NOT NULL, CONSTRAINT [PK_HazineGroup] PRIMARY KEY CLUSTERED ( [HazineGroupID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_HazineGroup] UNIQUE NONCLUSTERED ( [HazineName] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[HazineGroup] ONINSERT [dbo].[HazineGroup] ([HazineGroupID], [HazineName]) VALUES (6, N'برق')INSERT [dbo].[HazineGroup] ([HazineGroupID], [HazineName]) VALUES (1, N'شستشویی')INSERT [dbo].[HazineGroup] ([HazineGroupID], [HazineName]) VALUES (2, N'نظافتی')SET IDENTITY_INSERT [dbo].[HazineGroup] OFF/****** Object: Table [dbo].[Hazine] Script Date: 04/07/2013 17:34:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Hazine]( [HazineID] [int] IDENTITY(1,1) NOT NULL, [HazineGroupID] [smallint] NOT NULL, [Mablagh] [int] NOT NULL, [HazineName] [nvarchar](250) NOT NULL, CONSTRAINT [PK_Hazine] PRIMARY KEY CLUSTERED ( [HazineID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[Hazine] ONINSERT [dbo].[Hazine] ([HazineID], [HazineGroupID], [Mablagh], [HazineName]) VALUES (1, 1, 20000, N'صابون گل')INSERT [dbo].[Hazine] ([HazineID], [HazineGroupID], [Mablagh], [HazineName]) VALUES (3, 2, 10000, N'بروس')SET IDENTITY_INSERT [dbo].[Hazine] OFF/****** Object: Table [dbo].[SabtHazine] Script Date: 04/07/2013 17:34:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[SabtHazine]( [SabtHazineID] [int] IDENTITY(1,1) NOT NULL, [HazineID] [int] NOT NULL, [EndUserName] [nvarchar](20) NOT NULL, [Tedad] [smallint] NOT NULL, [sabtHazineDate] [date] NOT NULL, [Describtion] [nvarchar](500) NULL, CONSTRAINT [PK_SabtHazine] PRIMARY KEY CLUSTERED ( [SabtHazineID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[SabtHazine] ONINSERT [dbo].[SabtHazine] ([SabtHazineID], [HazineID], [EndUserName], [Tedad], [sabtHazineDate], [Describtion]) VALUES (6, 3, N'SA', 3, CAST(0xC2360B00 AS Date), N'')SET IDENTITY_INSERT [dbo].[SabtHazine] OFF/****** Object: Default [DF_SabtHazine_Tedad] Script Date: 04/07/2013 17:34:59 ******/ALTER TABLE [dbo].[SabtHazine] ADD CONSTRAINT [DF_SabtHazine_Tedad] DEFAULT ((1)) FOR [Tedad]GO/****** Object: ForeignKey [FK_Hazine_HazineGroup] Script Date: 04/07/2013 17:34:59 ******/ALTER TABLE [dbo].[Hazine] WITH CHECK ADD CONSTRAINT [FK_Hazine_HazineGroup] FOREIGN KEY([HazineGroupID])REFERENCES [dbo].[HazineGroup] ([HazineGroupID])GOALTER TABLE [dbo].[Hazine] CHECK CONSTRAINT [FK_Hazine_HazineGroup]GO/****** Object: ForeignKey [FK_SabtHazine_Hazine] Script Date: 04/07/2013 17:34:59 ******/ALTER TABLE [dbo].[SabtHazine] WITH CHECK ADD CONSTRAINT [FK_SabtHazine_Hazine] FOREIGN KEY([HazineID])REFERENCES [dbo].[Hazine] ([HazineID])GOALTER TABLE [dbo].[SabtHazine] CHECK CONSTRAINT [FK_SabtHazine_Hazine]GO[/code]

problem with stored procedure

Posted: 25 Apr 2013 06:03 PM PDT

I have a table[code]Create table SINHVIEN ( MASV nvarchar(20) not null, HOTEN nvarchar(100) not null, NGAYSINH datetime, DIACHI nvarchar(200), MALOP varchar(20), TENDN nvarchar(100) not null, MATKHAU varbinary not null, constraint PK_SINHVIEN primary key (MASV))go[/code]and I want to create a stored procedure to insert values for this table in security by use md5 hash function.[code]CREATE PROCEDURE SP_INS_SINHVIEN @MASV nvarchar(20), @HOTEN nvarchar(100), @NGAYSINH datetime, @DIACHI nvarchar(200), @MALOP varchar(20), @TENDN nvarchar(100), @MATKHAU varchar(100) AS INSERT INTO SINHVIEN VALUES ( @MASV, @HOTEN, @NGAYSINH, @DIACHI, @MALOP, @TENDN, HASHBYTES('MD5',@MATKHAU) )GO[/code]but when I try to execute this stored procedure,[code]insert into SINHVIEN values ( '1012283','Vo Thi Yen Nhi', '01/01/1992', 'VietNam', 'TH2010/4', 'meomun', HashBytes('MD5', '123456'))[/code]I get error[code]Msg 8152, Level 16, State 11, Line 1String or binary data would be truncated.The statement has been terminated.[/code]I debug and know the problem is [b]HashBytes('MD5', '123456')[/b]how can I do to fix this problem?

Problem with audit trigger

Posted: 24 Apr 2013 09:14 PM PDT

Hi, Have just created an audit trigger which will fire whenever a row is inserted, updated or deleted from a table. Problem is I get a row in my audit table [Audit].[SystemInfo] when I do an insert but not for an update or delete. I know those parts of the IF statement are executing as I'm writing to a logging table to check. Here is the code, any ideas on what is wrong? [code="sql"]ALTER TRIGGER dbo.SystemInfoTrg ON dbo.SystemInfo AFTER INSERT, DELETE, UPDATEAS BEGIN SET NOCOUNT ON; DECLARE @SessionID UNIQUEIDENTIFIER; SET @SessionID = NEWID(); DECLARE @Inserted INTEGER; DECLARE @Updated INTEGER; DECLARE @Deleted INTEGER; SELECT @Inserted = COUNT(1) FROM INSERTED; SELECT @Deleted = COUNT(1) FROM DELETED; IF (@Inserted > 0 AND @Deleted > 0) BEGIN INSERT INTO [Audit].SystemInfo SELECT 'Deleted', @SessionID, * FROM DELETED; INSERT INTO [Audit].SystemInfo SELECT 'Inserted', @SessionID, * FROM INSERTED; INSERT INTO logging (f1) VALUES ('@Inserted > 0 AND @Deleted > 0'); END ELSE BEGIN IF (@Deleted > 0) BEGIN INSERT INTO logging (f1) VALUES ('Deleted'); INSERT INTO [Audit].SystemInfo SELECT 'Deleted', @SessionID, * FROM DELETED; END; IF (@Inserted > 0) BEGIN INSERT INTO logging (f1) VALUES ('Inserted'); INSERT INTO [Audit].SystemInfo SELECT 'Inserted', @SessionID, * FROM INSERTED; END; ENDEND[/code]

Colulmn Reference

Posted: 25 Apr 2013 04:50 PM PDT

CREATE TABLE [dbo].[GV_ReceivedOffice]([ReceivedOfficeID] [int] IDENTITY(1,1) NOT NULL,[VoucherNo] [varchar](20) NULL,[ReceivedDate] [datetime] NULL,[ReceivedBy] [int] NULL,[TransactionID] [varchar](20) NOT NULL,PRIMARY KEY CLUSTERED ([ReceivedOfficeID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOI am trying to add a constraint to a column TransactionID so that it can references column 'TransactionID' of table GV_vocuher ALTER TABLE GV_ReceivedOffice ADD CONSTRAINT FK_ReceivedOffice_VocuherTransactionID FOREIGN KEY(TransactionID) REFERENCES GV_Voucher(TransactionID)But I am getting this error:Msg 1776, Level 16, State 0, Line 1There are no primary or candidate keys in the referenced table 'GV_Voucher' that match the referencing column list in the foreign key 'FK_ReceivedOffice_VocuherTransactionID'.Msg 1750, Level 16, State 0, Line 1Could not create constraint. See previous errors.

Table Structure

Posted: 25 Apr 2013 04:40 PM PDT

Hi,Below are the 2 table structure from which I have to select TransactionID from table GV_Voucher which are not present in GV_ReceivedOffice. But as TransactionID column not present in Gv_ReceivedOffice so I am confused is it good to add to a TransactionId in GV_Received table or should I exctract TransactionID on basis of vouchers?CREATE TABLE [dbo].[GV_Voucher]( [VoucherId] [int] IDENTITY(1,1) NOT NULL, [VoucherTypeId] [int] NOT NULL, [VoucherNo] [varchar](20) NOT NULL, [Denomination] [int] NOT NULL, [ExpiryDate] [datetime] NULL, [CreatedDate] [datetime] NULL, [ModifyDate] [datetime] NULL, [VoucherStatusId] [int] NOT NULL, [TransactionID] [varchar](20) NOT NULL, [Quantity] [int] NOT NULL, [AmountValue] [int] NULL, [CreatedBy] [nvarchar](50) NULL, [ModifiedBy] [nvarchar](50) NULL, [Validatedays] [int] NULL, [IsDeleted] [bit] NULL, CONSTRAINT [PK_GV_Voucher] PRIMARY KEY CLUSTERED ( [VoucherId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [UN_GVVoucher_VoucherNo] UNIQUE NONCLUSTERED ( [VoucherNo] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[GV_Voucher] WITH NOCHECK ADD CONSTRAINT [FK_GV_Voucher_GV_VoucherStatus] FOREIGN KEY([VoucherStatusId])REFERENCES [dbo].[GV_VoucherStatus] ([VoucherStatusId])GOALTER TABLE [dbo].[GV_Voucher] NOCHECK CONSTRAINT [FK_GV_Voucher_GV_VoucherStatus]GOALTER TABLE [dbo].[GV_Voucher] WITH NOCHECK ADD CONSTRAINT [FK_GV_Voucher_GV_VoucherType] FOREIGN KEY([VoucherTypeId])REFERENCES [dbo].[GV_VoucherType] ([VoucherTypeID])GOALTER TABLE [dbo].[GV_Voucher] NOCHECK CONSTRAINT [FK_GV_Voucher_GV_VoucherType]GO-----------------------------------------------------------------------------------------CREATE TABLE [dbo].[GV_ReceivedOffice]( [ReceivedOfficeID] [int] IDENTITY(1,1) NOT NULL, [VoucherNo] [varchar](20) NULL, [ReceivedDate] [datetime] NULL, [ReceivedBy] [int] NULL,PRIMARY KEY CLUSTERED ( [ReceivedOfficeID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO

Result of dynamic sql with parameteres into a variable

Posted: 06 Mar 2013 03:22 PM PST

Edit 25.4.2013:::Problem was solved, but I have question about view, my last reply...thx for response ////////Hello, i have problem with dynamic sql in cursor and i want to set to variablefirst I had problem, when I wanted to use variable int, there was problem with assign varchar to int ...I solved with SET @WORK_SEQ1=CAST(@WORK_SEQ as varchar(2))then I had problem with error -- Must declare the scalar variable "@RESULT_COUNT".and I found something on internet and I tried executed @sqlStr like this [code="sql"] exec sp_executesql @query=@sqlStr, @params=N'@RESULT_COUNT INT OUTPUT', @RESULT_COUNT=@RESULT_COUNT OUTPUT PRINT @RESULT_COUNT[/code]But now I have problem with this error Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.All code is below [code="other"]SET @sqlStr = 'DECLARE myCursor CURSOR FORSELECT LINE_CODE, ORDER_DATE, COMMIT_NO, BODY_NO, STATION_ID, WORK_POS, WORK_QTY, WORK_SEQ, WORK_TYPE, ITEM_CODE, ALC_CODE, OPTION_VALUE, LIMITV_LOW, LIMITV_HIGHFROM MCS_MESDB.dbo.TB_MASTER_' + @P_LINE_CODE + ' M (NOLOCK)WHERE M.ORDER_DATE = ''' + @P_ORDER_DATE + ''' AND M.COMMIT_NO = ''' + @P_COMMIT_NO + ''' AND M.LINE_CODE = ''' + @P_LINE_CODE + ''' AND M.WORK_TYPE = ''N'' AND M.STATION_ID = ''' + @P_STATION_ID + ''' 'EXEC(@sqlStr);OPEN myCursor FETCH NEXT FROM myCursor INTO @LINE_CODE, @ORDER_DATE, @COMMIT_NO, @BODY_NO, @STATION_ID, @WORK_POS, @WORK_QTY, @WORK_SEQ, @WORK_TYPE, @ITEM_CODE, @ALC_CODE, @OPTION_VALUE, @LIMITV_LOW, @LIMITV_HIGH WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @RESULT_COUNT int DECLARE @WORK_SEQ1 varchar(2) SET @WORK_SEQ1=CAST(@WORK_SEQ as varchar(2)) PROBLEM IS HERE >> SET @sqlStr = N'SELECT @RESULT_COUNT=(SELECT CASE(COUNT(*)) WHEN 0 THEN 1 ELSE COUNT(*) END FROM MCS_MESDB.dbo.TB_RESULT_TOOL_ENG01 R WITH(NOLOCK) WHERE ORDER_DATE = ''' + @ORDER_DATE + ''' AND COMMIT_NO = ''' + @COMMIT_NO + ''' AND STATION_ID = ''' + @STATION_ID + ''' AND ITEM_CODE = ''' + @ITEM_CODE + ''' AND WORK_SEQ = ''' +@WORK_SEQ1 + ''' AND WORK_RESULT = ''OK'' )' exec sp_executesql @query=@sqlStr, @params=N'@RESULT_COUNT INT OUTPUT', @RESULT_COUNT=@RESULT_COUNT OUTPUT PRINT @RESULT_COUNT[/code]

Sum ??

Posted: 25 Apr 2013 12:32 AM PDT

I am a SQL newbie that is just learning. I have 3 tables that I am joining on a Sales Order #. The first 2 tables are one to one, but the third is the one to many that I need to sum the amount. This is the code I have below so far. The issue seems to be the Shipment status that is created off of the select that I can't group on and throws an error there.[code="sql"]select ss.SOHNbr, ss.Shipment_Nbr, ss.Shiplist_Date, 'Shipment_Status' = CASE WHEN Shiplist_Status = 'S' THEN 'Invoiced' WHEN Shiplist_Status = 'I' THEN 'Shipped' WHEN Shiplist_Status = 'T' THEN 'Transferred' WHEN Shiplist_Status = 'P' THEN 'Progress Billing Order Shipped' WHEN Shiplist_Status = 'C' THEN 'Consigned' WHEN Shiplist_Status = 'R' THEN 'Remote DB Shipment' END, Ship_Date, Invoice, Line_Ship_Amt, [color=#FF0000]SUM(Ec_Amts) [/color]from soh sLEFT JOIN soh_shipmentnbrdata ss ON s.SOHNBr = ss.SOHNbrLEFT JOIN soh_ecdata se ON s.SOHNBr = se.SOHNbrwhere s.custnbr = '2395' AND (ss.ship_date between '2009-04-24' AND '2010-03-29')GROUP BY ss.SOHNbrorder by ss.ship_date, ss.SOHNbr asc[/code]

Split a range of numbers in equal parts

Posted: 25 Apr 2013 08:42 AM PDT

Hi, I want to divide a range values into equal parts with t-sql If i have a range of valuessay 1-100 If i enter Min(Value) as 1 and Max(value) as 100 (Here i am taking it as 5 parts) but i want to pass the value while running the querry. the output should come as 1-20 21-40 41-60 61-80 81-100 My ranges are in millions Min(value)= 5000000000000480 and max(Value)=5000001000000690 Thanks in advance

Really Challenging Query: Break out average value across months

Posted: 25 Apr 2013 04:47 AM PDT

Racking my brain on this one. I have the following tableAccountID DataDate DataValue1 1/1/2012 1001 2/5/2012 1901 3/18/2012 1181 6/5/2012 4901 6/29/2012 892 1/8/2012 1202 3/5/2012 218etcThe datadata is the end of a period -- so the datavalue for a record refers to a value that has been accumulated -- not a forward projection of value owed, if that makes sense.I need to be able to figure out and generate a by-month average of the datavalue. So, for instance, for the second row: 190 units, spread across a date range from 1/1/2012 to 2/5/2012 would be 36 days -- 5 of them in February. This I can do -- when the gap between reads is less than 2 months, it's easy. # of days in first month divided by total number of days in date range * value, then # of days in second month divided by total number of days in date range * value. What I need is to be able to do, though, is handle the instances where there's a multi-month gap. How can I take rows 4 and 5, and return a prorated average across march, may, and june? Looking at my dataset (55 million rows from 1/1/2011 to current), I've got 2000+ instances where the data gap is 13 months. So it would have to be able to handle multi-year scenarios as well.Anyone know a good math formula to calculate this? It's like statistical analysis or something, but I'm just a DBA, not a mathemagician. How can I solve this easily, without a really ugly, ugly cursor and other scary methods?

Script Out Database Mail Settings?

Posted: 08 Sep 2010 06:30 AM PDT

Anyone bother to have created a script to Reverse Engineer / Script out your EXISTING database mail settings?I set up a profile to use gMail, and it seems logical for me to export out the settings to a script, then run the script on my laptop, other servers, etc.There's no built in option, so I figured i'd ping the forum before i do it myself.by the way, my google-fu [i]is strong[/i], and there are example scripts where you fill in the blanks, examples how to set up dbmail, but i did not find anything that scripts out existing settings.[img]http://www.stormrage.com/SQLStuff/mail_gmail.gif[/img]

SQL Server Query Performance Questions

Posted: 25 Apr 2013 02:17 AM PDT

I have a question on performance as it relates to SQL queries and Stored Procedures/Views. Please help me understand the performance impacts of my question. Thank you. I have a Stored Proc that runs 50+ hours to complete. It has 7 table variables that build on each other (i.e. DECLARE @Table1 ..., insert into @Table1 select stuff ...; DECLARE @Table2 ..., insert into @Table2 select more stuff plus @Table1 stuff from @Table1; etc.) I took this procedure and revamped it into a VIEW, actually each table variable was turned into its own view with a SELECT * FROM final_view at the end. The view approach produced the exact same results as stored procedure, it just ran over 30 minutes. VIEW : 30 minutes, Stored Proc : 50+ hours. It seems like a no brainer. What am I missing? What performance impacts would a view have over a stored proc, if any? Why is the view so much faster? (The view is not on a production server. It's on a report server.)

Need help with a Summary Query

Posted: 25 Apr 2013 02:18 AM PDT

Hello, this is my first time posting here and I hope someone can help me. First, let me say that I am NO SQL Guru and this one has me stumped.I have a table that stores the results of customer surveys and am tasked with displaying graphs of the results in an ASP.Net web page. I am trying to write a query for an sProc that will return a data table with the summaries data. The table looks something like this:SurveyID Q1 Q2 Q3 Q4 Q5 HE1 HE2 HE3 HE4 HE52 2 2 2 2 2 2 2 2 2 23 1 3 1 3 1 1 2 3 4 54 0 0 0 0 0 0 0 0 0 07 3 0 0 0 0 0 0 0 0 08 4 4 2 4 2 3 3 3 3 39 1 2 2 3 1 2 2 2 2 210 2 2 2 2 2 1 1 1 1 1What I would like to return would be a data table that would summarize the counts of each value (0 to 4) in each column like:Q1 Q2 Q3 Q4 … etc1 2 2 22 0 1 01 3 4 11 1 0 21 1 0 1 Of course, the rows represent the values 0,1,2,3,4Is this possible? I have experienced abject failure with everything I have tried! Thank you in advance for your help.

Disk_Queue_Length is CRITICAL or high

Posted: 25 Apr 2013 04:58 AM PDT

This is regarding the Cluster servers 723/722/721 3 Nodes (Active/Active/Passive). At around Fri Mar 29 11:05:42 EDT 2013, We have got alert from monitoring agent Nagios that "Disk_Queue_Length is CRITICAL" and during that time SQL stopped responding for a couple minutes and this happened about 3 times.Since that time I disabled the Nagios alert on that particular event.Storage Team/Backup Team/Wintel Team - saying that no issues found from their end.Windows Administration team says its everything normal on their side. SQL Team - Saying that when this issue happened that time there was no jobs were running on this cluster and no issues. Can any one suggest here where exactly issue is and how to resolve this issue.Thanks a million in advance.

Search for partial text in text field

Posted: 25 Apr 2013 03:35 AM PDT

There is a table Order in which one filed [Notes] as text data type.How to search a partial text such as "ordered on 10/02/2012" in the field [Notes]?

SQL 2008 Express

Posted: 25 Apr 2013 12:18 AM PDT

Hi,I am trying to install SQL 2008 Express edition R2 with advance service 64 bit on Windows Server 2012 64 bit, But i am getting error it says "This program has compatibility issue"

Query issue

Posted: 25 Apr 2013 02:34 AM PDT

Hi All,I have a table with the following values Alexander Glaesel Alexander GlæselAlexander Riess Alexander RießI am running the following query select name from table A group by name having count(1)>1.The output seems to be returning all the above rows though all the records are not similar.Can anyone explain why this is happening?Your help would be appreciated.

Excel 64 bit driver

Posted: 14 Jan 2013 03:51 PM PST

Hi all, I am running the following query in sql server 2008 r2(64-bit),MS office 2007 excel file.SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=<loc>', 'SELECT * FROM [Sheet1$]')its showing errorMsg 7308, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.i run the following codesp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Ad Hoc Distributed Queries', 1;GORECONFIGURE;GObut still its showing the same error...any help....thanks and regardsSathiyan R

Pivot and grouping question

Posted: 25 Apr 2013 02:37 AM PDT

I have a table with structure Education(edu_id, emp_id, school, degree) where an employee can have multiple entries if they have multiple degrees.I am trying to do a data extract with the following columns needed: emp_id, school1, degree1, school2, degree2, ..., school5, degree5. So this looks like a pivot query. My idea is to create a CTE on the original table to add schooln and degreen on the original table based on the top 5 schools of an employee and then do a pivot. Any elegant idea on how to implement this? Thanks,Erick

SQL Server 2008 Merge Replication: Metadata Cleanup Failure

Posted: 24 Apr 2013 08:00 PM PDT

We are an issue, related to the metadata cleanup. Some time ago we reduced the retention period down from the default to 3 days and did not encounter issues, then last suddenly began to encounter the error below for various publications. It does not occur on every synchronisation. The output from setting agent parameters defined at http://msdn.microsoft.com/en-us/library/ms151872(d=printer,v=sql.100).aspx is shown below2013-04-24 11:09:13.648 The merge process could not perform retention-based metadata cleanup in database 'XXXXX'. If this failure continues, try increasing the query timeout for this process, or try reducing the retention period. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.2013-04-24 11:09:13.648 OLE DB Subscriber 'XXXXXXX': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}2013-04-24 11:09:13.664 Percent Complete: 572013-04-24 11:09:13.664 The merge process could not perform retention-based metadata cleanup in database 'XXXXX'. If this failure continues, try increasing the query timeout for this process, or try reducing the retention period. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.2013-04-24 11:09:13.664 OLE DB Distributor 'FULTON-LON1-C': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}2013-04-24 11:09:13.742 Percent Complete: 02013-04-24 11:09:13.742 Category:NULLSource: Merge Replication ProviderNumber: -2147199466Message: The merge process could not perform retention-based metadata cleanup in database 'XXXX'. If this failure continues, try increasing the query timeout for this process, or try reducing the retention period. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.Viewing the synchronisation history in replication monitor for the failed synchronisations shows the following:Error messages:The merge process could not perform retention-based metadata cleanup in database 'XXXX'. If this failure continues, try increasing the query timeout for this process, or try reducing the retention period. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199466)Get help: http://help/MSSQL_REPL-2147199466Violation of UNIQUE KEY constraint 'UQ__#oldgens__DCDFEE736CD828CA'. Cannot insert duplicate key in object 'dbo.#oldgens'. (Source: MSSQLServer, Error number: 2627)Get help: http://help/2627

Performance - Single VS Multiple Mount Points

Posted: 25 Apr 2013 01:42 AM PDT

Hi,I would appreciate any thoughts or comments on the following.We have a 3 nodes cluster hosting 12 SQL 2008 R2 instances. Each instance has a minimum of 4 Luns (1 for the instance root, 1 for data, 1 for log and 1 for tempdb). Some instances have several data LUN (up to 6 on two of them). All this for a total of 64 LUNs (using Mount points obviously).We are soon to migrate to a new Toshiba SAN (using thin provisioning) that will have a 276GB cache with 3 level of drives (12K, 15K and SSD). The SAN will not host only SQL.All the data is spread over all spindles with the SAN being intelligent enough to move blocks of data to faster drives depending on requests. We want to see if it's possible to reduce the number of LUNs. The high number of Luns is causing some headaches to the DR team when doing DR exercise.Since all spindles are used, our first thought is a go ahead.The setup is fairly complex. The data, log and tempdb mount points are attached under the instance mount point. For exemple you have a drive F: which is the instance LUN and F:\MSSQLDATA\Data1 which is a mount point for data and F:\MSSQLDATA\Log1 the mount point for log, etcI'm kind of concerned about impact on the server (node) side of things. I saw from other posts that having multiple LUN creates multiple drive queues in Windows. On the other side, we only have a single 10GB NIC card, therefore having one or multiple LUN shouldn't change anything, no? At the end is it just a matter of the NIC card bandwith?Would youA) keep the status quoB) Increase the number of LUN, when possible (like having a pseudo size limit of 500GB or 1TB)C) Decrease the number of LUN, when possible but keep the 4 LUN/instances (1 for the instance root, 1 for data, 1 for log and 1 for tempdb)D) Decrease the number as much as possible (thinking of going with 1 for the instance root and one for the rest)I thought about testing it in our lab, but how would I test it and with which tool?How would I use SQLIO in a way to compare throughput from let's say 2 data LUN VS having a single one?I did test using 2 test files on 2 different LUNs with 2 threads each and I get roughly the same IOs/sec than 1 LUN with 4 threads.Any input would be appreciated.Thank youPatrick

SSAS Current Fiscal Period

Posted: 25 Apr 2013 01:33 AM PDT

I would like to add the concept of a Current Fiscal Period into an existing cube that already has a time dimension. Within the SSAS Database, there already exists a cube called "Current Fiscal Period" that has 1 measure group and 1 dimension. The one value consists of a six character string such as "201304".I plan to include the "Current Fiscal Period" cube as a linked object in my larger cube.The Million dollar question is: Do I want to use the Current Fiscal Period as a dimension or a measure?Instinctively I want to use it as a dimesnion, but when I look at some other applications in my shop from the past it seems they used it as a measure instead. Why?What is the best way to implement this?Please give me your opinion.Thanks!

large object inBuffer cache alert

Posted: 20 Apr 2013 04:21 AM PDT

HiCan anbody advise on how to handle an alert that tells me i have a large object in the buffer cache. Do i need to clear them out or does sql server flush these out after a certain retention period ? How do i find out what the object is that is causing the issue ?

identity insert

Posted: 24 Apr 2013 09:28 PM PDT

Hi, when I am insrting data from a csv file I am getting error:'Explicit value must be specified for identity column in table 'ABC' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.'CREATE TABLE ABC(ID [int] IDENTITY(1,1) NOT NULL, -- primary keyaa [varchar](50) NULL,bb [varchar](50) NULL,cc [datetime] NULL,dd [varchar](50) NULL,ee [varchar](50) NULL,ff [int] NULL,gg [varchar](50) NULL,ii [int] NULL,jj [int] NULL)SET IDENTITY_INSERT ABC onBULK INSERT ABCFROM 'D:\test.csv'WITH( FIELDTERMINATOR = ',', ROWTERMINATOR = '')GOdata in csv file is like:84,0b0dbe1d,192.168.10.221,15:41.5,0b0dbe1d_16-15-18,1.0.0,2,pra,2,NULL85,111de4b6,192.168.10.221,27:06.1,111de4b6_16-27-05,1.0.0,8,Diane,5,NULL

Combining two stored procedures to get one result set.

Posted: 24 Apr 2013 08:14 PM PDT

I have got two stored procedures as below:[code="sql"]ALTER PROCEDURE [rept].[systemActivities] -- Add the parameters for the stored procedure here @client VARCHAR(50), @startDate DATETIME, @endDate DATETIMEASBEGIN SET NOCOUNT ON; DECLARE @results AS TABLE ( [subId] INT, [total] INT ) DECLARE @SubID int, @queryString varchar(8000), @currentDate DATETIME, @jcStart int, @jcEnd int TRUNCATE TABLE rept.tblActivityList DECLARE crSystems CURSOR FOR SELECT CAST(SUBSTRING(TABLE_NAME, 7, 5) AS int) AS SystemID -- Returning the substring as a integer of the table name. FROM DB3.information_schema.tables WHERE LEFT(TABLE_NAME,6) = 'tblAct' AND LEN(TABLE_NAME) = 11 -- Avoids tblAct0, tblAct1 AND TABLE_NAME <> 'tblActError' -- Needs to be excluded AND @client = ( SELECT TOP(1) dbo.tblClient.ClientNumber FROM DB_admin1.dbo.tblClient INNER JOIN DB_admin1.dbo.tblSystem ON DB_admin1.dbo.tblSystem.ClientNumber = DB_admin1.dbo.tblClient.ClientNumber WHERE SystemNum = CAST(SUBSTRING(TABLE_NAME, 7, 5) AS int) ) ORDER BY TABLE_NAME --Probably better to process the entries in number order OPEN crSystems FETCH NEXT FROM crSystems INTO @SubID WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Checking system ' + CAST(@subID AS varchar(10)) SET @currentDate = @startDate WHILE @currentDate < @endDate BEGIN PRINT @currentDate -- Calculate JCStartDateTime, JCEndDateTime SET @jcStart = DB_admin1.dbo.fnc_IsoDateTime_JcDateTime(@currentDate) SET @jcEnd = DB_admin1.dbo.fnc_IsoDateTime_JcDateTime(DATEADD(DAY, 1, @currentDate)) DECLARE @sql AS varchar(8000) SET @sql = 'INSERT INTO rept.tblActivityList ([systemNum] ,[dtuStart] ,[dtuEnd] ,[total]) VALUES ( ' + CAST(@subID AS varchar(10)) + ', ''' + CONVERT(varchar(10), @currentDate, 20) + ''', DATEADD(DAY, -1, DATEADD(MONTH, 1, ''' + CONVERT(varchar(10), @currentDate, 20) + ''')), ( SELECT COUNT (*) FROM DB3.dbo.tblAct' + CAST(@subID AS varchar(10)) + ' WHERE actTimeDate BETWEEN ' + CAST(@jcStart AS varchar(10)) + ' AND ' + CAST(@jcEnd AS varchar(10)) + ' ) )' --PRINT @sql EXEC (@sql) SET @currentDate = DATEADD(MONTH, 1, @currentDate) END FETCH NEXT FROM crSystems INTO @SubID END CLOSE crSystems; DEALLOCATE crSystems; -- Group the results. SELECT [dtuStart] ,COUNT(systemNum) AS total FROM [DB_admin1].[rept].[tblActivityList] WHERE total > 10 GROUP BY dtuStart ORDER BY dtuStart END[/code]AND[code="sql"]ALTER PROCEDURE [rept].[spSubscribedSystems] -- Add the parameters for the stored procedure here @startDate DATETIME, @endDate DATETIME, @client INTASBEGIN SET NOCOUNT ON; TRUNCATE TABLE rept.tblSubscribedSystems DECLARE @systemSubcriptions AS TABLE( SystemNum int, startDate datetime2(0), endDate datetime2(0) ) ;WITH cteRenewals AS ( -- Rank the renewals in order by System. SELECT [UniqueId] ,[dbo].[tblRenewals].[SystemNum] ,[Date] ,[Invoice] ,[Renewed] ,[dbo].[tblClient].[ClientNumber] ,RANK() OVER(PARTITION BY [dbo].[tblRenewals].SystemNum ORDER BY UniqueID ASC) as seq FROM [DB_admin1].[dbo].[tblRenewals] INNER JOIN dbo.tblSystem ON dbo.tblRenewals.SystemNum = dbo.tblSystem.SystemNum INNER JOIN dbo.tblClient ON dbo.tblSystem.ClientNumber = dbo.tblClient.ClientNumber WHERE [dbo].[tblRenewals].SystemNum >= 10000 AND dbo.tblClient.ClientNumber = 1006--@client ), cteRenewalsWithEndSubscriptionLength AS ( -- Join back on itself, but join to previous sequence SELECT ren1.UniqueId, ren1.SystemNum, ren2.[Date] AS startDate, ren1.[Date] AS endDate, DATEDIFF(DAY, ren2.[Date], ren1.[Date]) AS subLength, ren1.Invoice, ren1.Renewed, ren1.seq AS seq1, ren2.seq AS seq2 FROM cteRenewals ren1 INNER JOIN cteRenewals ren2 ON ren1.SystemNum = ren2.SystemNum AND ren1.seq - 1 = ren2.seq -- Disjoint to find previous record for that system WHERE ren1.seq > 1 ) INSERT INTO @systemSubcriptions SELECT SystemNum, startDate, endDate FROM cteRenewalsWithEndSubscriptionLength WHERE subLength BETWEEN 0 AND 3000 -- Ignoring systems with strange values for subscription length DECLARE @currentDate datetime2(0), @monthEnd datetime2(0), @monthStart datetime2(0) SET @currentDate = @startDate -- Loop around each monthn WHILE @currentDate < @endDate BEGIN SET @monthStart = @currentDate SET @monthEnd = DATEADD(DAY, -1, DATEADD(MONTH, 1, @currentDate)) -- Insert any subscribed system in that month into the report table INSERT INTO rept.tblSubscribedSystems SELECT SystemNum, @monthStart AS startDate, @monthEnd AS endDate FROM @systemSubcriptions sysSubs WHERE sysSubs.startDate <= @monthEnd--DATEADD(DAY, 7, @monthStart)--@monthEnd AND sysSubs.endDate >= @monthStart SET @currentDate = DATEADD(MONTH, 1, @currentDate) END -- Summarise by start date, total number of systems. SELECT dtuStart, COUNT(DISTINCT(systemNum)) AS [Total Systems] FROM rept.tblSubscribedSystems GROUP BY dtuStart ORDER BY dtuStartEND[/code]When I run both stored procedures by executing :[code="sql"]DECLARE @return_value int EXEC @return_value = [rept].[systemActivities] @client = N'1060', @startDate = N'2000-01-01', @endDate = N'2020-01-01' GO -- Subscription data: DECLARE @return_value int EXEC @return_value = [rept].[spSubscribedSystems] @client = N'1060', @startDate = N'2000-01-01', @endDate = N'2020-01-01' [/code]I get the result set:[img]http://imageshack.us/a/img23/8209/20668286.png[/img]What I want is to combine both stored procedures. So in the new result set it will show:dtuStart and Total Systems from the [rept].[spSubscribedSystems] with the total from the [rept].[systemActivities]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog