Wednesday, July 3, 2013

[T-SQL] Get Employee Managers Manger Query

[T-SQL] Get Employee Managers Manger Query

Get Employee Managers Manger Query

Posted: 03 Jul 2013 12:36 AM PDT

Hi I want to take employee, Manager and the managers' manger from employee table. I've tried the below query its working if i have the three values (emp, manager1, manager2).But f i have only two levels (emp and manger only) then its not working.[code="sql"]CREATE TABLE [#Employee]( [EmpID] [int] NOT NULL, [EmpName] [varchar](50) NOT NULL, [SupID] [int] NULL)GOINSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (1, 'ram', 2)INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (2, 'sureh', 3)--INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (3, 'vimal', null)select e1.empname employee,m1.empname Manager1, m2.empname Manager2 from[#Employee] e1, [#Employee] m1, [#Employee] m2where m1.EmpID = e1.SupID and m2.EmpID = m1.SupID[/code]Can any one help me here?

Delta of Disk IO

Posted: 02 Jul 2013 11:55 PM PDT

Hi all - I am trying to work out some deltas from information I collect every 5mins from the sys.dm_io_virtual_file_stats DMV. I am currently struggling to find a way to minus one value from the other for each row in the table.I am trying to do the following to get this to work however I have syntax problems in the CASE statement and also issues when trying to + 1 to the ROW total.All help appreciated, apologies if this is not clear.[code="sql"]WITH IOPS ([IO_STALL] ,[IO_STALL_READ_MS] ,[IO_STALL_WRITE_MS] ,[NUM_OF_READS] ,[NUM_OF_WRITES] ,[SIZE_ON_DISK_MB] ,[DBNAME] ,[NAME] ,[FILE_ID] ,[DB_FILE_TYPE] ,[FILE_LOCATION] ,[TIMESTAMP] ,[ROW])AS(SELECT *, ROW_NUMBER() OVER (PARTITION BY FILE_LOCATION ORDER BY TIMESTAMP DESC) AS [ROW]FROM dbo.DISKIOPS)SELECT CASE WHEN ROW = 1 THEN 0 ELSE ((SELECT IO_STALL FROM IOPS WHERE ROW = (ROW+1)) - IO_STALL) END AS IO_STALL, FILE_LOCATIONFROM IOPS[/code]

SQL statement that will perform a daily sum aggregation my last chance

Posted: 02 Jul 2013 07:05 PM PDT

a SQL statement that will perform a daily sum aggregation on the field cost. It must only use costs that are associated with the follow items: Z001, Z002. The cost must also only come from division 1. Below is the example table that you will be retrieving data from:Costs• Receipt• Date• Item• Reason• Division• CostI have tried this :use STEVENTESTgoCREATE FUNCTION Daily_Cost1( @item varchar , @Division nchar )RETURNS money ASBEGIN DECLARE @daily_cost money DECLARE @Cost_date date set @Cost_date = GETDATE () if @item = 'Z001' OR @item ='Z002'SELECT @daily_cost = SUM(cost) from Costwhere @Cost_date =@Cost_date RETURN @daily_cost ENDGO

sql sales

Posted: 02 Jul 2013 07:08 PM PDT

Write a SQL statement that will return all the Sales Orders for the Salespersons' with the name starting with 'John'. [b]i have tried this :[/b] CREATE VIEW [dbo].[SalesOrder]ASSELECT dbo.Trnasction.Salesorder, dbo.SalesPerson.SalesPersonNameFROM dbo.Trnasction CROSS JOIN dbo.SalesPersonWHERE (dbo.SalesPerson.SalesPersonName = N'John')

Information displaying incorrectly.....

Posted: 02 Jul 2013 06:11 PM PDT

Hi,I have 2 tables:[code="sql"]CREATE TABLE [dbo].[GV_InwardAtStore]( [InwardAtStoreID] [int] IDENTITY(1,1) NOT NULL, [StoreCode] [int] NULL, [STNNo] [varchar](20) NULL, [GRNNo] [varchar](10) NULL, [VoucherBookletNo] [varchar](10) NULL, [ReceivedDate] [datetime] NULL, [StoreManagerID] [varchar](50) NULL,PRIMARY KEY CLUSTERED ( [InwardAtStoreID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY][/code][code="sql"]CREATE TABLE [dbo].[GV_StoreAllocation]( [StoreTransactionId] [int] IDENTITY(1,1) NOT NULL, [StoreId] [int] NULL, [STNNo] [varchar](20) NULL, [VoucherBookletNo] [varchar](10) NULL, [Quantity] [int] NULL, [AllocatedDate] [datetime] NULL, [AllocatedBy] [varchar](40) NULL, CONSTRAINT [PK_GV_Allocation] PRIMARY KEY CLUSTERED ( [StoreTransactionId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]ALTER TABLE [dbo].[GV_StoreAllocation] ADD DEFAULT (getdate()) FOR [AllocatedDate][/code]Let me explained the scenario now:Firstly I allocate some vouchers to a store, after allocating follwing information gets saved in table GV_StoreAllocation like this:StoreTransactionId StoreId STNNo VoucherBookletNo Quantity AllocatedDate AllocatedBy1 1006 1004000066 B06349 2 2013-07-03 11:42:45.480 012a967b-50b5-4af6-825f-f064af4d93272 1006 1004000066 B06350 2 2013-07-03 11:42:45.480 012a967b-50b5-4af6-825f-f064af4d9327After this I have inwarded those vouchers in my store, when I inward some voucher it will get stored into GV_InwardAtStore like this:lets suppose I have inward 2 voucher with information as below:InwardAtStoreID StoreCode STNNo GRNNo VoucherBookletNo ReceivedDate StoreManagerID1 1006 1004000066 000000001 B06349 2013-07-03 11:43:35.033 012a967b-50b5-4af6-825f-f064af4d93272 1006 1004000066 000000001 B06350 2013-07-03 11:43:35.033 012a967b-50b5-4af6-825f-f064af4d9327Now, I am not able to sell voucher B06349 so I want to allocate it to another store 5001, as I allocate it to store 5001 table GV_StoreAllocation gets following rows:StoreTransactionId StoreId STNNo VoucherBookletNo Quantity AllocatedDate AllocatedBy1 1006 1004000066 B06349 2 2013-07-03 11:42:45.480 012a967b-50b5-4af6-825f-f064af4d93272 1006 1004000066 B06350 2 2013-07-03 11:42:45.480 012a967b-50b5-4af6-825f-f064af4d93273 5001 1006000006 B06349 1 2013-07-03 11:46:14.927 012a967b-50b5-4af6-825f-f064af4d9327Now, store 5001 have to inward this voucher untill that it should not get displayed on the screen, but its displaying. These are the stored procedures that I have created to display vouchers inforamtion for both scrrens Inward at Store and Allocation To Store,[code="sql"]ALTER PROCEDURE [dbo].[BS_InwardAtStore_ShowVouchers]@UserID varchar(40) = '012A967B-50B5-4AF6-825F-F064AF4D9327'ASBEGIN SET NOCOUNT ON;DECLARE @IsAdmin INT = 0SELECT @IsAdmin = a.IsAdmin FROM GV_StoreUserDetails a WHERE a.UserID = @UserID SELECT gias.StoreCode ToStore, --gias.STNNo, gias.grnno GRNNo, MIN(gias.VoucherBookletNo)FirstVoucherNo, MAX(gias.VoucherBookletNo)LastVoucherNo, gv.Denomination Denomination, gvt.VoucherType VoucherType, CAST(MAX(RIGHT(gias.VoucherBookletNo,7))AS int) - CAST(MIN(RIGHT(gias.VoucherBookletNo,7)) AS int) +1 Quantity, CASE WHEN LEFT(MIN(gias.VoucherBookletNo),1)='V' THEN 'VOUCHER' ELSE 'BOOKLET' END AS [Type]INTO #Voucher FROM GV_InwardAtStore gias JOIN GV_Voucher gv ON gv.VoucherNo = gias.VoucherBookletNo JOIN GV_VoucherType gvt ON gvt.VoucherTypeID = gv.VoucherTypeId JOIN GV_StoreAllocation gsa ON gsa.VoucherBookletNo = gias.VoucherBookletNo JOIN GV_StoreUserDetails sud ON sud.StoreCode = gias.StoreCode--WHERE sud.UserID = @UserIDGROUP BY gias.StoreCode, gias.grnno, gv.Denomination, gsa.Quantity, gvt.VoucherType --UNION ALLSELECT gias.StoreCode ToStore, --gias.STNNo, gias.grnno GRNNo, MIN(gias.VoucherBookletNo) FirstBookletNo, MAX(gias.VoucherBookletNo) LastBookletNo, gv.Denomination Denomination, gvt.VoucherType VoucherTYpe, CAST(MAX(RIGHT(gias.VoucherBookletNo,5))AS int) - CAST(MIN(RIGHT(gias.VoucherBookletNo,5)) AS int) +1 Quantity, CASE WHEN LEFT(MIN(gias.VoucherBookletNo),1) = 'B' THEN 'BOOKLET' ELSE 'VOUCHER' END AS [Type]INTO #Booklet FROM GV_InwardAtStore gias JOIN GV_Booklet gv ON gv.BookletID = gias.VoucherBookletNo JOIN GV_VoucherType gvt ON gvt.VoucherTypeID = gv.VoucherTypeId JOIN GV_StoreAllocation gsa ON gsa.VoucherBookletNo = gias.VoucherBookletNo JOIN GV_StoreUserDetails sud ON sud.StoreCode = gias.StoreCode--WHERE sud.UserID = @UserIDGROUP BY gias.StoreCode, gias.grnno, gv.Denomination, gsa.Quantity, gvt.VoucherTypeIF @IsAdmin = 1 SELECT v.ToStore, v.GRNNo, v.FirstVoucherNo, v.LastVoucherNo, v.Denomination, v.VoucherType, v.Quantity, v.[Type] FROM #Voucher v UNION ALL SELECT b.ToStore, b.GRNNo, b.FirstBookletNo, b.LastBookletNo, b.Denomination, b.VoucherTYpe, b.Quantity, b.[type] FROM #Booklet bELSE SELECT v.ToStore, v.GRNNo, v.FirstVoucherNo, v.LastVoucherNo, v.Denomination, v.VoucherType, v.Quantity, v.[Type] FROM #Voucher v WHERE v.ToStore = (SELECT a.StoreCode FROM GV_StoreUserDetails a WHERE a.UserID = @UserID) UNION ALL SELECT b.ToStore, b.GRNNo, b.FirstBookletNo, b.LastBookletNo, b.Denomination, b.VoucherTYpe, b.Quantity, b.[type] FROM #Booklet b WHERE b.ToStore = (SELECT a.StoreCode FROM GV_StoreUserDetails a WHERE a.UserID = @UserID)ORDER BY GRNNo DESCEND[/code][code="sql"]ALTER PROCEDURE [dbo].[BS_StoreAllocation_ShowVouchers]ASBEGINSET NOCOUNT ON;WITH Voucher AS( SELECT gsa.StoreId StoreCode, gsa.STNNo STNNo, MIN(gsa.VoucherBookletNo) FirstID, MAX(gsa.VoucherBookletNo) LastID, gv.Denomination, CAST(MAX(RIGHT(gsa.VoucherBookletNo,7))AS int) - CAST(MIN(RIGHT(gsa.VoucherBookletNo,7)) AS int) +1 Quantity, gv.VoucherTypeId, CASE WHEN LEFT(MIN(gsa.VoucherBookletNo),1) = 'B' THEN 'BOOKLET' ELSE 'VOUCHER' END AS Booklet, ISNULL(gvstn.AWBNo, '') AS AWBNo FROM GV_StoreAllocation gsa JOIN GV_Voucher gv ON gv.VoucherNo = gsa.VoucherBookletNo JOIN GV_VoucherType gvt ON gv.VoucherTypeId = gvt.VoucherTypeID JOIN GV_STNDetails gvstn ON gsa.STNNo = gvstn.STNNO AND gvstn.TO_StoreCode = gsa.StoreId --AND gvstn.AWBNo IS NULL GROUP BY gsa.StoreId, gsa.STNNo, --gsa.Quantity, gv.Denomination, gv.VoucherTypeId, AWBNo --gsa.VoucherBookletNo),Booklet AS( SELECT gsa.StoreId StoreCode, gsa.STNNo STNNo, MIN(gsa.VoucherBookletNo) FirstID, MAX(gsa.VoucherBookletNo) LastID, gv.Denomination, CAST(MAX(RIGHT(gsa.VoucherBookletNo,5))AS int) - CAST(MIN(RIGHT(gsa.VoucherBookletNo,5)) AS int) +1 Quantity, gv.VoucherTypeId, CASE WHEN LEFT(MIN(gsa.VoucherBookletNo),1) = 'B' THEN 'BOOKLET' ELSE 'VOUCHER' END AS Booklet, ISNULL(gvstn.AWBNo, '') AS AWBNo FROM GV_StoreAllocation gsa JOIN GV_Booklet gv ON gv.BookletID = gsa.VoucherBookletNo JOIN GV_VoucherType gvt ON gv.VoucherTypeId = gvt.VoucherTypeID JOIN GV_STNDetails gvstn ON gsa.STNNo = gvstn.STNNO AND gvstn.TO_StoreCode = gsa.StoreId --AND gvstn.AWBNo IS NULL GROUP BY gsa.StoreId, gsa.STNNo, --gsa.Quantity, gv.Denomination, gv.VoucherTypeId, AWBNo)SELECT * FROM VoucherUNION ALLSELECT * FROM BookletORDER BY STNNo DESCEND[/code]Please help....

the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011 Then confirm the Financial Year, Quarter, Month, Week and Weekday,please help

Posted: 02 Jul 2013 08:59 PM PDT

i am not sure about this statements please help :CREATE PROCEDURE FinYear2012 @Cost_date date = '2011-02-15 00:00:00.0000000' ASBEGIN DECLARE @Fin_year int DECLARE @Fin_quarter int DECLARE @fin_Month int DECLARE @Fin_Week int DECLARE @Weekday Varchar (25) SELECT Mydate , CASE WHEN (Mydate) BETWEEN '2010-02-28 ' AND '2010-05-28 ' THEN 'First Quarter' WHEN (Mydate) BETWEEN '2010-05-28 ' AND '2010-08-28 ' THEN 'Second Quarter' WHEN (Mydate) BETWEEN '2010-08-28 ' AND '2010-12-28 ' THEN 'Third Quarter' WHEN (Mydate) BETWEEN '2010-12-28 ' AND '2011-02-28 ' THEN 'Fourth Quarter' END AS FiscalYearFROM Transactions set @Fin_year = DATEDIFF(year, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Fin_quarter = DATEDIFF(quarter, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @fin_Month = DATEDIFF(MONTH, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Fin_Week = DATEDIFF(WEEK, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Weekday = DATEDIFF(WEEKDAY, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); SELECT @Fin_year as FinancialYr ,@Fin_quarter as FinancialQtr , @fin_Month as FinancialMnth , @Weekday as WekkedayENDGO:-D

sql quantity sold per day

Posted: 02 Jul 2013 07:10 PM PDT

I have tried this but my head of it department still telling me tht i have failed this is my last chanceWrite a SQL statement from the above example tables that will return the Product Name and Quantity with the maximum quantity sold per day.SELECT e.BuProductName,s.sum(OrderQuantity) as Sold per dayFROM dbo.Product AS e INNER JOIN dbo.Transact AS s ON e. productID= s. productID

Problem using FOR XML in SQL Query

Posted: 02 Jul 2013 08:04 PM PDT

Hi to all, I have one format problem that i cannot get it right. I hope you can help me.So here it is.[code="sql"]declare @test table(id int identity(1,1), custname varchar(10), custcode varchar(10) )insert @testvalues ('name1','code1'),('name2','code2')SELECT (SELECT custname,custcode FOR XML PATH(''),TYPE) AS ItemFROM @testFOR XML PATH('Header'),ROOT('ns1')[/code]The above code it will output like this.[code="xml"]<ns1> <Header> <Item> <custname>name1</custname> <custcode>code1</custcode> </Item> </Header> <Header> <Item> <custname>name2</custname> <custcode>code2</custcode> </Item> </Header></ns1>[/code]What i want is something like this...[code="xml"]<ns1> <Header> <Item> <custname>name1</custname> <custcode>code1</custcode> </Item> <Item> <custname>name2</custname> <custcode>code2</custcode> </Item> </Header></ns1>[/code]All of your answers is very much appreciated. thanks

financial year sql statement please help

Posted: 02 Jul 2013 07:06 PM PDT

You are only given the transaction date in your transactions table and your client requires you to be able to show them their data by Financial Year, Quarter, Month, Week and Week Day[b]Write the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011Then confirm the Financial Year, Quarter, Month, Week and Weekday.[/b]CREATE PROCEDURE Fin_Year1 @Cost_date date = '2011-02-15 00:00:00.0000000' ASBEGIN DECLARE @Fin_year int DECLARE @Fin_quarter int DECLARE @fin_Month int DECLARE @Fin_Week int DECLARE @Weekday Varchar (25) set @Fin_year = DATEDIFF(year, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Fin_quarter = DATEDIFF(quarter, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @fin_Month = DATEDIFF(MONTH, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Fin_Week = DATEDIFF(WEEK, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Weekday = DATEDIFF(WEEKDAY, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); SELECT @Fin_year as FinancialYr ,@Fin_quarter as FinancialQtr , @fin_Month as FinancialMnth , @Weekday as WekkedayENDGO

Write the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011

Posted: 02 Jul 2013 08:26 PM PDT

5.You are only given the transaction date in your transactions table and your client requires you to be able to show them their data by Financial Year, Quarter, Month, Week and Week DayWrite the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011Then confirm the Financial Year, Quarter, Month, Week and Weekday.R/CREATE PROCEDURE FinYear2012 @Cost_date date = '2011-02-15 00:00:00.0000000' ASBEGIN DECLARE @Fin_year int DECLARE @Fin_quarter int DECLARE @fin_Month int DECLARE @Fin_Week int DECLARE @Weekday Varchar (25) SELECT Mydate , CASE WHEN (Mydate) BETWEEN '2010-02-28 ' AND '2010-05-28 ' THEN 'First Quarter' WHEN (Mydate) BETWEEN '2010-05-28 ' AND '2010-08-28 ' THEN 'Second Quarter' WHEN (Mydate) BETWEEN '2010-08-28 ' AND '2010-12-28 ' THEN 'Third Quarter' WHEN (Mydate) BETWEEN '2010-12-28 ' AND '2011-02-28 ' THEN 'Fourth Quarter' END AS FiscalYearFROM Transactions set @Fin_year = DATEDIFF(year, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Fin_quarter = DATEDIFF(quarter, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @fin_Month = DATEDIFF(MONTH, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Fin_Week = DATEDIFF(WEEK, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); set @Weekday = DATEDIFF(WEEKDAY, '2010-02-28 23:59:59.9999999' , '2011-02-28 00:00:00.0000000'); SELECT @Fin_year as FinancialYr ,@Fin_quarter as FinancialQtr , @fin_Month as FinancialMnth , @Weekday as WekkedayENDGO

Function to replace string 'NULL' with null value

Posted: 02 Jul 2013 07:26 PM PDT

HiI am selecting values to one table and populating them in another table, and I'm using a case statement to check if the word is the string 'NULL' then I replace it with value null.e.g CASE WHEN [Gross_Amount] = 'NULL' THEN NULL ELSE CAST([Gross_Amount] AS DECIMAL(18,2)) END[Gross_Amount]So I'm thinking I can write a function that can check to see the value if it is a string NULL, if so replace it with null or return Gross_Amount if not. and rewrite that whole CASE statement, to objective here is to minimise the lines of code.Can anyone help please.

Max of 2 dates

Posted: 24 Jun 2013 07:40 PM PDT

I search for Date functions for finding the max of the 2 dates. But such function does not exits.Is there any easy way to do it.Thanks in advance.

CHARINDEX problem

Posted: 02 Jul 2013 09:50 AM PDT

Hello comunityI have a table name field FT.DESCAR Varchar(60) with the following content:5695 - 0 | 7050-127 MONTEMOR O NOVO with this Select :Select RIGHT(FT.DESCAR,CHARINDEX('|',FT.DESCAR)-1) from FTwhere ftstamp = 'jm13070255373,181882981'the result is:OR O NOVOto remove blank spaces i change my select to :select RIGHT( Ltrim(Rtrim(FT.DESCAR)),CHARINDEX('|', Ltrim(Rtrim(FT.DESCAR)))-1) from ft where ftstamp = 'jm13070255373,181882981'the result is the same:OR O NOVOI don´t understand why??someone could give me some help.Many thanksLuis Santos

Urgent help with Date comparison in TSQL

Posted: 02 Jul 2013 02:23 AM PDT

Dear friends,I have requirement - to retrieve order from database that were created before 12:00 AM GMT on June 3, 2013.I'm using datediff but it is giving me couple records that were created on June 3rd 2.59 AM also,below is my code of line-datediff(second,[WorkForce_JobPosting].[Job Posting Create Date_JP] ,'2013-06-03 12:00:00.000')> 0 )note the date format for thhis create date in DB is 'date time'thanksDhananjay

Put first line as column name

Posted: 02 Jul 2013 04:03 AM PDT

Hi people,I am creating a function to be used in a specific application that a user can insert any kind of information into a table. This code below:[code="sql"]IF NOT EXISTS(SELECT * FROM SYS.tables WHERE name = 'TB_HEADER')CREATE TABLE TB_HEADER(ID_HEADER INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, CODIGO VARCHAR(50) UNIQUE, COL1_H VARCHAR(100), COL2_H VARCHAR(100), COL3_H VARCHAR(100), COL4_H VARCHAR(100), COL5_H VARCHAR(100), COL6_H VARCHAR(100), COL7_H VARCHAR(100), COL8_H VARCHAR(100), COL9_H VARCHAR(100), COL10_H VARCHAR(100))GO IF NOT EXISTS(SELECT * FROM SYS.tables WHERE name = 'TB_DADOS')CREATE TABLE TB_DADOS(ID_DADOS INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, ID_HEADER INT, COL1_D VARCHAR(100), COL2_D VARCHAR(100), COL3_D VARCHAR(100), COL4_D VARCHAR(100), COL5_D VARCHAR(100), COL6_D VARCHAR(100), COL7_D VARCHAR(100), COL8_D VARCHAR(100), COL9_D VARCHAR(100), COL10_D VARCHAR(100))GOINSERT INTO TB_HEADERSELECT CODIGO = 'CARRO_01', COL1_H = 'MODELO_CARRO', COL2_H = 'ANO', COL3_H = 'DATA_COMPRA', COL4_H = 'COR', COL5_H = 'PLACA', COL6_H = 'COL6', COL7_H = 'COL7', COL8_H = 'COL8', COL9_H = 'COL9', COL10_H = 'COL10'GO INSERT INTO TB_HEADERSELECT CODIGO = 'IMOVEL_01', COL1_H = 'TIPO_DE_IMOVEL', COL2_H = 'LOCALIZAÇÃO', COL3_H = 'VALOR_ENTRADA', COL4_H = 'QTD_PARCELA', COL5_H = 'VALOR_PARCELA', COL6_H = 'MOEDA', COL7_H = 'COL7', COL8_H = 'COL8', COL9_H = 'COL9', COL10_H = 'COL10'GO INSERT INTO TB_DADOSSELECT ID_HEADER = ID_HEADER, COL1_D = 'PALIO', COL2_D = '2012', COL3_D = '05/02/2013', COL4_D = 'VERMELHO', COL5_D = 'XPT 1515', COL6_D = 'COL6', COL7_D = 'COL7', COL8_D = 'COL8', COL9_D = 'COL9', COL10_D = 'COL10'FROM TB_HEADER WHERE CODIGO = 'CARRO_01'GO INSERT INTO TB_DADOSSELECT ID_HEADER = ID_HEADER, COL1_D = 'CRV', COL2_D = '2011', COL3_D = '23/06/2012', COL4_D = 'PRATA', COL5_D = 'ABC 2020', COL6_D = '', COL7_D = '', COL8_D = '', COL9_D = '', COL10_D = ''FROM TB_HEADER WHERE CODIGO = 'CARRO_01'GO INSERT INTO TB_DADOSSELECT ID_HEADER = ID_HEADER, COL1_D = 'APARTAMENTO', COL2_D = 'BARRA DA TIJUCA', COL3_D = '200.000,00', COL4_D = '48', COL5_D = '8.000,00', COL6_D = 'R$', COL7_D = '', COL8_D = '', COL9_D = '', COL10_D = ''FROM TB_HEADER WHERE CODIGO = 'IMOVEL_01'GO CREATE FUNCTION DBO.UF_DADOS (@CODIGO VARCHAR(50))RETURNS TABLEASRETURNSELECT COL1_H, COL2_H, COL3_H, COL4_H, COL5_H, COL6_H, COL7_H, COL8_H, COL9_H, COL10_H FROM TB_HEADER WHERE CODIGO = @CODIGOUNION ALLSELECT COL1_D, COL2_D, COL3_D, COL4_D, COL5_D, COL6_D, COL7_D, COL8_D, COL9_D, COL10_DFROM TB_DADOS INNER JOIN TB_HEADER ON TB_HEADER.ID_HEADER = TB_DADOS.ID_HEADER WHERE CODIGO = @CODIGOGO[/code]Although big query, that is a query function creation. The problem is: How can I return first lines from "tb_Header" to be column name?, I thought in "sp_rename", but I afraid it can be slow in a big table. I'd like to emphasize that columns are always dynamic. Part of code below[code="sql"]CREATE FUNCTION DBO.UF_DADOS (@CODIGO VARCHAR(50))RETURNS TABLEASRETURNSELECT COL1_H, COL2_H, COL3_H, COL4_H, COL5_H, COL6_H, COL7_H, COL8_H, COL9_H, COL10_H FROM TB_HEADER WHERE CODIGO = @CODIGOUNION ALLSELECT COL1_D, COL2_D, COL3_D, COL4_D, COL5_D, COL6_D, COL7_D, COL8_D, COL9_D, COL10_DFROM TB_DADOS INNER JOIN TB_HEADER ON TB_HEADER.ID_HEADER = TB_DADOS.ID_HEADER WHERE CODIGO = @CODIGOGO[/code]grateful for your attention

Need to derive avg execuion time for 3 tables

Posted: 02 Jul 2013 04:23 AM PDT

[font="Courier New"]Need to derive avg execution time per row in table for each tablename. Sample table layoutTableName BeginTime EndTime========= ========= ======= T1 2013-06-02 00:30.10.073 2013-06-02 00:30.17.463 T1 2013-05-14 00:40.09.283 2013-05-14 00:42.10.640T1 2013-06-22 00:52.02.073 2013-06-22 01:03.10.727T1 2013-07-01 00:03.07.487 2013-07-01 00:04.03.120T2 2013-03-06 00:16.10.763 2013-03-06 02:21.10.133T2 2013-07-02 00:22.10.880 2013-07-02 01:12:04.282 T2 2013-05-12 00:01.10.113 2013-05-12 00:44.15.797T3 2013-01-18 00:14.10.577 2013-01-18 02:02.19.120T3 2013-07-02 00:23.10.560 2013-07-02 01:42.03.480So I need avg time for T1, T2, and T3... just these 3 values.thanks for any suggestions[/font]

Test

Posted: 02 Jul 2013 05:07 AM PDT

Testing
Posted by at No comments:

[SQL Server 2008 issues] Logshipping Alerts

[SQL Server 2008 issues] Logshipping Alerts

Logshipping Alerts

Posted: 02 Jul 2013 05:43 PM PDT

Hi all,I am getting this message in error log frequently. Actually there is no database with name test in our environment.The log shipping primary database QBSECPU01.Test has backup threshold of 20 minutes and has not performed a backup log operation for 70399 minutes. Check agent log and log shipping monitor information.How to suppress or workaround for this issue.

Restoring failed

Posted: 02 Jul 2013 04:43 PM PDT

Hi all,i am trying to restore a database to point in time in sql server 2012. so i want to test that one with a sample database... i have taken fullbackup ..and then entered some data..then taken differential backup and entered some data and taken log backup... when i am trying to restore, the full backup was successfully restored. when i am trying the differential backup it is saying unable to create restore plan due to break in LSN chain. i am not able to find what wrong i have done...

CPU Flat lines and numerous Time Outs

Posted: 02 Jul 2013 01:55 AM PDT

Sorry for the long post... but here goes:We have recently migrated from a physical 2 node cluster environment to VM Ware.Ever since that day we have an increased number of time outs being reported from the client application (almost 300 per day!) and almost weekly the CPU is flat lining and keeps flat lining for hours…When it flat lines it shows SQL Server using all 100% of the CPU resources.We have made no major changes in SQL procs or queries since the migration and we never had these problems before the migration (when it was on the physical cluster).Here is the spec of the VM host environment:Production3x BL460cG6 Blades with 2x Hex core 2.9GHz CPUs (12 CPU per hots) and 96GB RAM2x BL460cG6 Blades with 2x Quad Core 2.5GHz CPUs (8 CPU per host) and 96GB RAM2x BL490cG6 Blades with 2x Hex Core 2.9Ghz CPUs (12 CPU per Host) and 96GB RAM - Still to be added (Old SQL Cluster)DR3x BL460cG6 Blades with 2x Hex core 2.9GHz CPUs (12 CPU per hots) and 96GB RAM2x BL460cG6 Blades with 2x Quad Core 2.5GHz CPUs (8 CPU per host) and 96GB RAM2x BL490cG6 Blades with 2x Quad Core 2.5GHz CPUs (8 CPU per host) and 96GB RAMNetwork Uplinks from VM to Core 3x 10GB4x 1GBFibre Uplinks to SAN6x 8GBThe sysadmin is not really a VM admin and so cannot assist me much…Any ideas why our server is flat lining and why we are getting so many time outs all of a sudden?One very strange thing I did see was this:The current MaxDop setting is 8. When the CPU flat lines, I change the MaxDop setting to something different like 4. There is no change and the CPU still flat lines. When I change it back to MaxDop of 8, the CPU recovers and runs on average 70% - I have done this 3 different times - just to check it was not a fluke….same result each timeHow can this be?How do I troubleshoot this?

summary

Posted: 01 Jul 2013 10:54 PM PDT

alter procedure [dbo].[pocc](@empid nvarchar(10),@department varchar(10))asbegincreate TABLE #TempEmployees(date datetime,eid int,remarks varchar(50))-- Insert result from the SP to temp tableINSERT INTO #TempEmployeesEXEC dbo.at @empid,@department--Verify the Insert recordsSELECT *FROM #TempEmployees where eid=@empidendexec [pocc] 17074,''when i exceute its giving me that resultdate----------------------------eid--------remarks2013-01-06 00:00:00.000-------17074---OFF DAY 2013-01-07 00:00:00.000-------17074---ABSENT 2013-01-07 00:00:00.000-------17074----Late2013-01-08 00:00:00.000-------17074----HALFDAYhow i make a summary result like this-----eid---offday-----absent-----late---------halfday--------17074--1----------1--------1----------1------

Problem in executing bunch of Insert statements

Posted: 02 Jul 2013 04:33 AM PDT

Hello friends,I am executing bunch of Insert satements as follows:[b]use TestinggoINSERT INTO Table_Update(TableName,StartlastWritten) SELECT 'Students'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten FROM [10.198.68.39]..dbo.Students;.....INSERT INTO Table_Update(TableName,StartlastWritten) SELECT 'teachers'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten FROM [10.198.68.39]..dbo.teachers;[/b] I have 1450 insert statments, which I am running in SQL SERVER to insert the data from "Linked Server" to "SQL Server".[b]when I run them in one go ..it give me few successfully inserted records. around 250 out of 1450 and following error[/b][b]The OLE DB provider "OraOLEDB.Oracle" for linked server "10.198.68.39" supplied inconsistent metadata for a column. The column "NOTESTEXT" (compile-time ordinal 3) of object ""dbo"."Notes"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.[/b]But, When I run them individually or in samll bunch around 23-30 at a time it success fully executes.I don't know how to get rid of this, because, every time it is very costly to run them individually or in small bunch.please help.Thanks.

Using snapshots for reporting on historical data?

Posted: 02 Jul 2013 12:58 PM PDT

I've been using timestamps and status dates on tables to do historical reporting (e.g. today last year, quarter over quarter, etc). But I've been thinking that using snapshots might simplify life a little. Are snapshots generally the best practice for historical reporting? I really on have 15 or so tables (out of the hundreds) that I query a bunch for history, but I read snapshots run against the entire instance. So I'm just looking for ideas and general advice. As always, thanks a bunch for any help.

help with sp_MsForEachDb

Posted: 02 Jul 2013 10:20 AM PDT

Hi everyone. I have created a table using the following code in sql2008:[code="sql"]USE Dba_AdminGOCREATE TABLE dbo.databaseDataFileSize ( rowId INT IDENTITY(1,1), dbName sysname, dbSize BIGINT, sampleDate DATE, sampleTime TIME)GO[/code]I am trying to alter a script to run which should populate the table using the Sp_MsforeachDb. I am running the follwoing code to try this :[code="sql"]EXEC master.sys.sp_MSforeachdb INSERT INTO Dba_admin.dbo.databaseDataFileSize SELECT ''?'', SUM(size) * 8, cast(getdate() as date), cast(getdate() as time) FROM [?].sys.database_filesWHERE type = 0;[/code]but i am getting the following error:[quote]Msg 102, Level 15, State 1, Line 3Incorrect syntax near '?'.[/quote]Can anybody help me modify this so it itterates through each database and populates the table so i can keep track of my datafile sizes ?Thanks

How to Check Change in the Status

Posted: 02 Jul 2013 06:34 AM PDT

I have a table for instructors which contains subjects, date and time and Status information about their classes.Once they register on the website they get the pending status, and somebody from administration either Approve, deny or Cancel their class.so the status changes from Pending to either Approv, deny Or cancel.I want to write a query that to piush the information to instructors if their Status gets changed from Pending to Approve, Deny Or CancelHow do I do that.Thaks,Blyzzard

Unable to use a variable in the @body parameter of sp_send_dbmail

Posted: 02 Jul 2013 06:51 AM PDT

I am trying to build a variable to be used with the @body parameter of sp_send_dbmail. Here is the code I am trying to run: DECLARE @v_body varchar(MAX) DECLARE @v_business_name varchar(75) = 'Hollies Gift Shop' DECLARE @v_owner_first_name varchar(30) = 'Bernice' DECLARE @v_owner_last_name varchar(30) = 'Goodly' DECLARE @v_profile varchar(30) = 'testprofile'set @v_body = 'Business Name: ' + @v_business_name + char(13) +char(10) + 'First Name : ' + @v_owner_first_name + char(13) +char(10) + 'Last Name : ' + @v_owner_first_nameUSE msdbGOEXEC sp_send_dbmail @profile_name = 'testprofile',@recipients = 'example@yahoo.com',@subject = 'Test message',@body = @v_body I receive the following error: Must declare the scalar variable "@v_body".What is it that I'm doing wrong

table creation on one table referenced by many tables

Posted: 02 Jul 2013 07:15 AM PDT

hiHere a requirement that I am trying to implementOne table Address is used by many tables. like member, provider and sales representative.While creating Sales Order, User would entry information for member, provider and sales representative.and address for each entitymember, provider, sales representative tables has reference to sales order tableWhich is best way to create tables1. member, provider and sales representative have FK to Address table2. Address table has 3 columns for member, provider and sales representative.3. Instead of address table, add columns to each tablemember, provider and sales representative to address is one-one.

Calculating date in time.

Posted: 02 Jul 2013 06:56 AM PDT

How I create a function to get all policies that expire a 120 days from now.Eg If the policy effectivedate is November 1 2012 and expiration date is November 1 2013 then on july first when I run a query this policy should be selected.

Connection encryption between SQL Server and BackupExec

Posted: 02 Jul 2013 07:38 AM PDT

We are in the mists of securing all internal communications via encryption, a security requirement by law for us...We are using SQL Server encryption from the server to the clients for all sensitive databases, but we have no turned on force encryption yet due to not knowing how some programs might act.One such program is Symantec's BackupExec. We are using BE 2012 and the SQL Server backup agent to backup our database servers. We have contacted Symantec about this and they don't seem to have a clue what we are asking. They keep telling us the agent is encrypted if you have hardware encryption turned on the tape... well that's not what we need to know... we wanted to know if you force encryption at the database server connection level how does the agent react...We need to make sure we are not leaving an unencrypted path between the server and the backup agent. We know from the agent to the media server are encrypted via certificate / key exchanges. We know the media server to the tape are encrypted via a key we provided. We just don't know how or if the data from the server to the agent are encrypted...Anyone have any experience with this or any knowledge of how BackupExec handles a require encrypted connection setting?

How to get total of months, like Year to Day in cross tab query?

Posted: 02 Jul 2013 01:21 AM PDT

I used code below to create cross tab query. It works fine.How to add code to get total of months, like Year to Day?SELECT *FROM (SELECT year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], InvoiceAmount as Amount FROM Invoice) as sPIVOT(SUM(Amount)FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec))AS pivot

Restore Database using backup file

Posted: 02 Jul 2013 03:40 AM PDT

I need help in using the backup file. I am trying to restore the sql database and I want to avoide the date and use astrick so how would I do that?example:MyDb_backup_2013_06_30_231639_7324894.bakWant to use like: MyDb_backup*.bakCan you please help

SQL 2008 - Litespeed backup fails

Posted: 02 Jul 2013 02:14 AM PDT

Out Litespeed backup was running without any prob. Aftre sometime I changed the SQL Server name and that started all the issues. All backups jobs are failing now. Error: Unable to connect 'Local Server Connection'Since the maintenance plan is an ssis package, the 'data source' tab in job step shows a row Local Server Connection with the old server name and other values. How to fix this without creating a new maintenance plan all over again

Help on blocking in tempdb

Posted: 02 Jul 2013 02:08 AM PDT

On one of our 2008 R2 SP2 sql server we are experiencing blocking in tempdb. The blocked requests are Intellisense and Data Collection queries. We have found some stored procedures that create but do not drop temp tables, their connection starts when application begins and ends when application is terminated. We are having these stored procedures changed. Just wondering if anyone else has experienced this. Server has 16 Processors, 65 gb and tempdb has 4 data files of equal size.

Effect of Clustered Index on Non-Clustered Index

Posted: 02 Jul 2013 01:50 AM PDT

Not remotely a DBA so forgive me if this is a novice question, but will having a clustered index in a database improve the performance of non-clustered indexes?Sean

tempdb keeps growing

Posted: 02 Jun 2013 09:08 PM PDT

I have configured my tempdb as follows:8 data files with an initial size of 14GB each. This was based on 2 weeks of monitoring the DB and seeing to what size it grows.I have set the autogrow to 10%.The thing is, the data files have now grown to 22 GB each - making the overall size just over 183 GB. The tempdb is on its own drive and the capacity of this drive is 200 GB.I am afraid that I might be running out of space....I know restarting the SQL service will shrink the files back to its initial size of 14 GB each, but this is a production server and a restart is not possible.Any possible solutions?Will the tempdb not release unused space?

Space Consumed by table

Posted: 02 Jul 2013 12:57 AM PDT

Hi,A table has two VARCHAR(100) column. Number of Records 2100000, Disk Space Consumed 222MB. It is having Varchar data of length 80.I have updated these two column with number 15.So before Update- 80 character data in both columnafter Update- 2 character data in both columnAfter Update Can i aspect less disk consumption by the table.Thanks,Anupam

SqlServer 2008 Processor Affinity

Posted: 02 Jul 2013 12:51 AM PDT

Hello All ... I am looking for some advice on CPU affinity settings in SqlServer 2008. I have a 2 node 3 instance cluster with 16 cpu's and 104 GB's of ram. The OS is 2008 Enterprise.The current setup is:Instance 1 Processor: 0-7IO : 8-15Instance 2 Processor: 8-15IO : 0-7Instance 3Processor: 0-3,8-11IO : 4-7,12-15Currently Instance 1 and Instance 3 are sharing a cluster node. I do see some spikes in CPU activity but those are generally focused during maintenance tasks, so that can be expected. Does anyone know of any best practices or any other recommendations?

SQL 2008 express installation using command prompt

Posted: 01 Jul 2013 11:40 PM PDT

HiI am trying to install sql server 2008 express using command prompt from my .net applicationThis is the command[code="sql"]Arguments='/q /hideconsole /action=Install/TCPENABLED=1/NPENABLED=1/SECURITYMODE=SQL/features=SQL,Tool/SAPWD ="Mydwp2008#"s/instancename=SQLEXPRESS/enableranu=1/sqlsvcaccount="NT Authority\Network Service"/AddCurrentUserAsSqlAdmin/skiprules=RebootRequiredCheck/IAcceptSQLServerLicenseTerms'[/code]installion is working fine..but the authentication mode is changed to WINDOWS authentication mode even though i am choosing mixed mode.How to solve this issue

Log on Index Creation?

Posted: 01 Jul 2013 11:52 PM PDT

I am trying to determine when some indexes were added to a table. Is that logged anywhere? Thank you.

The system has rebooted from a Automatic Server Recovery (ASR) event.

Posted: 01 Jul 2013 11:44 PM PDT

We are facing a frequent restart of our production server once in a month or so. The event log is as below..[i]User ActionDetermine the nature of the Automatic Server Recovery (ASR) event, and take corrective action. WBEM Indication PropertiesAlertingElementFormat: 1 0x1 (Other)AlertType: 5 0x5 (Device Alert)BladeBay: "11"BladeName: "DBServer.histogenetics.com"Description: "The system has rebooted from a Automatic Server Recovery (ASR) event."EnclosureName: "OB-005DD323242"EventCategory: 16 0x10 (System Power)EventID: "1"ImpactedDomain: 3 0x3 (Enclosure)IndicationIdentifier: "{B211AC7F-9D6F-435C-825E-560CB382D0CF}"IndicationTime: "20130701224253.923000-240"NetworkAddresses[0]: "fe80::a8d7:c0f3:ac5f:b15d%10"NetworkAddresses[1]: "xxx.xxx.xxx.xx"OSType: 103 0x67 (Microsoft Windows Server 2008 R2)OSVersion: "6.1.7601"PerceivedSeverity: 5 0x5 (Major)ProbableCause: 111 0x6f (Timeout)ProbableCauseDescription: "ASR Reboot Occurred"ProviderName: "HP Recovery"ProviderVersion: "2.7.0.0"RackName: ""RackUID: ""RecommendedActions[0]: "Determine the nature of the Automatic Server Recovery (ASR) event, and take corrective action."Summary: "ASR reboot occurred"SystemCreationClassName: "HP_WinComputerSystem"SystemFirmwareVersion[0]: "2010.03.30"SystemFirmwareVersion[1]: "2010.03.30"SystemGUID: "33424235-3532-584D-51343-425235235"SystemModel: "ProLiant BL460c G6"SystemName: "DBServer.histogenetics.com"SystemProductID: "785725-K22"SystemSerialNumber: "XDfs898sd"TIME_CREATED: 130172065740131089 0x1ce76cdda1e8b11[/i]Did anybody faced a similar problem and get it solved..

Multiple SQL Standard Instances on 4 Processor/32-core Server

Posted: 01 Jul 2013 09:27 PM PDT

We have a large 4 processor/32-core server with 192GB of memory available in the data center and over twenty small SQL Standard databases to consolidate. They are a mix of SQL 2012 and 2008 R2 for 3rd-party apps.Is there any issue with simply installing two instances of SQL Standard on the server - one for 2012 and one for 2008 R2 ? Each instance will use up to 64GB out of the 192GB and 16 cores. If we did this with Enterprise, the licensing would be a fortune and the Enterprise features are not needed.

Linked server from SQL to Progress?

Posted: 01 Jul 2013 07:59 PM PDT

Hi, What driver do I need to connect SQL via a linked server to a Progress database? This is so we can export data from an application called CCure which monitors building door access. I downloaded the DataDirect driver pack from datadirect.com but none of them work as the SQL ODBC connection wants a numerical port number to connect to the CCure server but the Progress database on it uses (from what I can see) a port number of CFSRV. I have seen online that some people use an old Merant driver but when googling for that I always get pointed to DataDirect which looks like it has superseeded Merant now? Any ideas? Thanks.
Posted by at No comments:

Tuesday, July 2, 2013

[SQL Server] a

[SQL Server] a

a

Posted: 02 Jul 2013 03:49 AM PDT

a

Configuration Manager; nothing under SQL Server Services

Posted: 01 Jul 2013 09:58 PM PDT

Honestly, I give up. I can't connect to this database as sa ('a network-related or instance-specific error occurred error 40 [etc.]') so I open up Configuration Manager, click on SQL Server Services, and... it's completely empty (see attached). Why would this happen?I'm connecting via remote desktop, and I'm an administrator on that machine (Windows Server 2008 R2). I've tried Google. The SQL Server service must be up and running because people are using that database right now.As before if you've read any of my posts, I'm fairly new here, everybody else has left and I know nothing about how this machine was set up. This job sucks. Any pointers would be gratefully received! Thanks.

How to compate cross column values

Posted: 02 Jul 2013 12:12 AM PDT

Hi,I am having requirement for to check cross column values,Below is the example tablecol1 col2 col3 col41 2 1/7/2013 5/7/20131 2 8/7/2013 9/7/20131 2 9/7/2013 10/7/20131 2 11/7/2013 12/7/2013Out put should be as belowcol1 col2 col3 col4 OutPut1 2 1/7/2013 5/7/2013 01 2 8/7/2013 9/7/2013 01 2 9/7/2013 10/7/2013 11 2 11/7/2013 12/7/2013 0Where two cross date matches there should be 1 else 0so please help me..

Filtering on SQL Views

Posted: 01 Jul 2013 07:34 PM PDT

Hi AllLooking for a little help.I have a crystal report that is based on a View on my SQL Server (VwQPPMaterialRptPart3).This view is based on a union of 2 other views (VwQPPMaterialRptPart1 & VwQPPMaterialRptPart2). After playing with my Crystal Report Select parameters I cannot get it to filter down the way I need the report to be. As a test I filtered some of the information on the underlying Views (Part1 and Part2) and the report worked perfectly. Is there a way to pass parameters from Crystal Reports to my underlying views? Also because of all my views the report does take an age to load.I am very basic in SQL I can create databases, setup security, add and populate tables and create basic views. So I think my views are very inefficient. Please find below the SQL for my 3 views.VwQPPMaterialRptPart1 (This combines 2 tables, Shift and SAPMaterials)[code="sql"]SELECT TOP (100) PERCENT dbo.SAPMaterials.Material, dbo.Shift.ShiftDepartment, REPLACE(REPLACE(dbo.Shift.ShiftMachine, CHAR(13), ''), CHAR(10), '') AS QPPMachine, dbo.Shift.ShiftAmount, CONVERT(datetime, LEFT(dbo.Shift.ShiftStart, 10), 103) AS Date, dbo.SAPMaterials.MaterialButton, dbo.SAPMaterials.OperationFROM dbo.Shift INNER JOIN dbo.SAPMaterials ON dbo.Shift.ShiftDepartment = dbo.SAPMaterials.Department AND REPLACE(REPLACE(dbo.Shift.ShiftMachine, CHAR(13), ''), CHAR(10), '') = dbo.SAPMaterials.ProcessORDER BY dbo.SAPMaterials.Material[/code]VwQPPMaterialRptPart2 [code="sql"]SELECT TOP (100) PERCENT ShiftDepartment, REPLACE(REPLACE(ShiftMachine, CHAR(13), ''), CHAR(10), '') AS ShiftMachine, MIN(ShiftAmount) AS ShiftAmounts, SUM(Amount) AS Defects, CONVERT(datetime, LEFT(ShiftStart, 10), 103) AS Date, ClassType, MaterialFROM dbo.VwWeeklyPerformanceGROUP BY ShiftDepartment, REPLACE(REPLACE(ShiftMachine, CHAR(13), ''), CHAR(10), ''), CONVERT(datetime, LEFT(ShiftStart, 10), 103), ClassType, MaterialORDER BY ShiftDepartment[/code]VwQPPMaterialRptPart3 - Final Report View[code="sql"]SELECT dbo.VwQPPMaterialRptPart1.Material, dbo.VwQPPMaterialRptPart1.ShiftDepartment, dbo.VwQPPMaterialRptPart1.QPPMachine, dbo.VwQPPMaterialRptPart1.ShiftAmount, CASE WHEN dbo.VwQPPMaterialRptPart2.Defects IS NULL THEN 0 ELSE dbo.VwQPPMaterialRptPart2.Defects END AS Defects, dbo.VwQPPMaterialRptPart1.MaterialButton, dbo.VwQPPMaterialRptPart1.Operation, dbo.VwQPPMaterialRptPart1.DateFROM dbo.VwQPPMaterialRptPart1 LEFT OUTER JOIN dbo.VwQPPMaterialRptPart2 ON dbo.VwQPPMaterialRptPart1.MaterialButton = dbo.VwQPPMaterialRptPart2.Material AND dbo.VwQPPMaterialRptPart1.QPPMachine = dbo.VwQPPMaterialRptPart2.ShiftMachineGROUP BY dbo.VwQPPMaterialRptPart1.Material, dbo.VwQPPMaterialRptPart1.ShiftDepartment, dbo.VwQPPMaterialRptPart1.QPPMachine, dbo.VwQPPMaterialRptPart1.ShiftAmount, dbo.VwQPPMaterialRptPart1.MaterialButton, dbo.VwQPPMaterialRptPart1.Operation, CASE WHEN dbo.VwQPPMaterialRptPart2.Defects IS NULL THEN 0 ELSE dbo.VwQPPMaterialRptPart2.Defects END, dbo.VwQPPMaterialRptPart1.Date[/code]Now the filtering I was using was by ShiftDepartment and Date on my report but the information is not right. I need to filter by ShiftDepartment and Date on Part1 and also filter by SiftDepartment and Date on Part2.Hope you can help sort my mess out.CheersDj
Posted by at No comments:

[how to] Can a database restore fail after it has begun?

[how to] Can a database restore fail after it has begun?

Can a database restore fail after it has begun?

Posted: 02 Jul 2013 06:57 PM PDT

I've noticed that a lot of times if a database restore is going to fail, it will fail before it even starts restoring in SQL Server 2008 Management Studio (displaying an error about space or something similar). I need to set up a schedule to restore a database overnight, but am not sure if I'll need to monitor it overnight or not. The hardware is stable enough that I believe this won't be a point of failure.

A better question might be: What checks are done against a backup prior to the restore process beginning?

MySQL Security permissions

Posted: 02 Jul 2013 06:08 PM PDT

If mysql possible, block user command execute:

DESC "table"
SHOW TABLES
SHOW CREATE TABLE "table"

Or need write mysql transport port wrapper ?

GRANT ON TABLE not help "hide user" database structure
My developers no need allow dump database structure and "show tables name"

/etc/my.cnf

[mysqld]  skip-show-database  skip-networking  bind-address=127.0.0.1  #skip-show-tables  #skip-table-stucture-show

Database query to determine privileges given to public [on hold]

Posted: 02 Jul 2013 06:23 PM PDT

command to find out all object and system privileges granted to public

command to find out users and roles having the following privileges:

SELECT ANY, CREATE ANY, ALTER ANY, DROP ANY, INSERT ANY, UPDATE ANY, DELETE ANY, EXECUTE ANY, DEBUG ANY, DEQUEUE ANY, ENQUEUE ANY, FORCE ANY, GRANT ANY, LOCK ANY, MANAGE ANY, BACKUP ANY, ANALYZE ANY, COMMENT ANY, AUDIT ANY

Oracle Select Query Over Database Link Suddenly Hangs Endlessly

Posted: 02 Jul 2013 02:29 PM PDT

I have a query that's been in use for years and today it started hanging endlessly. I took the query apart and found the piece causing it to hang is a simple join on a primary key that should run like lightning. If I remove problem table the query runs in less than a second.

Synthetically it looks like this: (The problem table is remote2) 

SELECT fields   FROM local_table1, local_table2, ... (more tables)       remote_table1@dblink remote1, remote_table2@dblink remote2   WHERE        ...clauses to tie the local tables together with each other and remote_table1...         remote2.primary_key = remote1.primary_key_of_remote2         AND remote2.afield <> 'avoid this value'

It's acting like it stuck on some kind of DB lock, but neither myself nor the DBA can find it. Is there some kind of special queuing mechanism used by remote database links that is locking this up?

Restoring a backed up database fails

Posted: 02 Jul 2013 06:04 PM PDT

I have backed up a database (full backup) and was attempting to restore as a new database (different name). However when attempting to restore it I get the following error:

The transaction log for database 'L-Test' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

Not really sure why that's happening. The size of the database is only 37.50MB and the logfile is set to auto grow as well.

Partitioning / indexing an extremely large table

Posted: 02 Jul 2013 03:15 PM PDT

I'm working on indexing and partitioning a single data warehouse table that weighs in around 500 GB. The table is a heap, has over a hundred TEXT columns, and the TEXT_IN_ROW option is enabled. I didn't design this table, and I don't have any capacity to change it in the immediate future.

I've been tasked with partitioning it. We're tackling this using a copy of the database on a test server. It can push about 2 GB per second to the SSD RAID arrays, so I/O isn't a significant bottleneck, and it's got 16 cores (2 NUMA nodes), and 64 GB of RAM.

My approach is to disable all the nonclustered indexes, create a partition function and partition scheme (about 12 partitions, all on the PRIMARY filegroup - they're using this to enable rolling maintenance and provide more localized inserts for nightly ETL, and not to distribute I/O), then build a clustered index for the table using this partition scheme.

I'm creating the clustered index and partitioning the table as follows:

CREATE CLUSTERED INDEX CX_DailyTable ON DailyTable (LoadDate, SeqNumber)     WITH (SORT_IN_TEMPDB = ON) ON monthly_on_primary (LoadDate)

Obviously, it's taking a long time (3 hours so far as of this post), and I certainly don't expect it to be quick. What worries me slightly is that tempdb is now pushing nearly 1 TB and steadily climbing, despite the current table being around half that size. The MS docs I've read suggest the tempdb space usage should be about the size of the final table/clustered index.

http://msdn.microsoft.com/en-us/library/ms188281.aspx

If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs, and sufficient free space in the destination filegroup to store the final index structure. The sort runs contain the leaf rows of the index.

Are their estimates incorrect? Is tempdb being used for substantially more than just the sort runs? Or is creating this clustered index somehow doubling the size of the table? (Seems pretty unlikely; it's a rather wide table, and I estimate we're getting an extra 4-8 bytes per row, plus non-leaf pages by adding a clustered index.)

Default trace enabled but not active

Posted: 02 Jul 2013 12:53 PM PDT

When I query the configuration of the default trace, it shows enabled:

exec sp_configure 'default trace enabled';  -->  name                    minimum  maximum  config_value  run_value  default trace enabled         0        1             1          1

But when I query sys.traces for the path, it returns an empty rowset:

select * from sys.traces;

What could explain the absence of the enabled trace?

Is it possible to reduce a group to a row based on some criteria?

Posted: 02 Jul 2013 12:05 PM PDT

I'd like to perform a select on a table involving a GROUP BY such that all rows that share the same set of identifiers are grouped together, but I want to reduce the group to one of the grouped rows based on some criteria. For example, the maximum date_added. However, there are other fields of data that could be different among the grouped rows. I want all of those columns to resolve to the row with the max date_added as well.

I realize to get the max date_added I could simply SELECT MAX(date_added), but that is just a column-level aggregate function. Is there any way I can resolve the entire row in a group?

Conceptually, if you imagine each group as a separate table, I want to SELECT * WHERE date_added=(SELECT MAX(date_added)) from that group table.

How to run steps in one job sequentially

Posted: 02 Jul 2013 07:29 PM PDT

I have one job on SQL server Job Agent that has 3 steps. I want to the job to run starting with step one. After step one is complete and only when it completes I want step 2 to start running and so for step 3

Returning a True or False from an IIF statement

Posted: 02 Jul 2013 09:28 AM PDT

I have a query with the following IIF statement

OnlineEnrolment: IIf([qry_ebs_final_CAN-ENROL-ONLINE].[SP_CAN_ENROL]='Y',True,False)

It gives the correct results, but the format that it gives them is what I am struggling with. It responds back with 0 or -1. I want it to respond TRUE or FALSE.

How can I change this other than making TRUE and FALSE strings? I still want them as boolean values.

Thanks

Kieran

Zero conversion in sql

Posted: 02 Jul 2013 07:31 PM PDT

Char convertion in Oracle have a pattern: to_char(number,'pattern') and provide option "s" to take sign to result, for ex: select to_char(10,'S999') from dual; will return +10, but when i check select to_char(0,'S999') from dual; it return +0 Anyone could explain why :-)

boolean purpose column char(0) or tinyint(1)

Posted: 02 Jul 2013 07:53 AM PDT

For Boolean purpose column which one is better: nullable char(0) or tinyint(1).

I know bool is an alias for tinyint(1) , but in "High Performance MySQL" book that published by O'reilly said:

"If you want to store a true/false value in a single bit of storage space, another option is to create a nullable CHAR(0) column. This column is capable of storing either the absence of a value (NULL) or a zero-length value (the empty string). "

which one is better for size, performance, indexing or ...

UPDATE: I found this link useful for this question: Efficient Boolean value storage for Innodb Tables

Dealing with identity ranges for transactional replication

Posted: 02 Jul 2013 09:40 AM PDT

I've noticed that when you set up a transactional replication, SQL Server will set identity range management to manual. What this means is that in my subscription database, when I try to insert a new record into a table whose PK is an identity column, it will give me an error and say that it tried to insert a PK of "1", "2", "3", etc. This is because the current identity value for all identity columns on the subscriber gets reset to 1 instead of staying at what it was on the publisher.

I understand why SQL Server does this - you're supposed to leave the subscriber table as read-only. However, my scenario is a little unorthodox - I update my subscriber from time to time through replication, make an immediate backup of that DB, then I want to do some updates to the subscriber that WON'T be pushed back to the publisher, then when I go to update the subscriber again, I restore its database from the earlier backup and pull the latest updates. Because I want to do updates to the subscriber in between these updates ('temporary deltas' if you will), I need the identity column to work and not to reset to 1 when replicated.

I tried turning on automatic identity range management when setting up my publication, but that just gives me the following error when I try to add a table to the publication:

Msg 21231, Level 16, State 1, Procedure sp_MSrepl_addarticle, Line 2243
Automatic identity range support is useful only for publications that allow updating subscribers.

Is there any way I can get round this problem? I do kind of want to present this replication to SQL Server as if it were read-only at the subscriber end because I don't plan on making updates that will be pushed back to the publisher, but I do want to make temporary updates that will be erased before the next replication.

I have also considered that snapshot replication might be a more appropriate method than transactional replication for my usage pattern, but the trouble is that snapshot replication requires sending the whole darn DB every single update; because I'm planning on taking an immediate backup of the DB after the latest replication, I shouldn't need to do that whole transfer every time; just the changes since last time.

What is the difference between TCP port and TCP dynamic port?

Posted: 02 Jul 2013 07:54 PM PDT

Can anyone tell me the difference?

I tried to set either 1 port and left another blank also can work. I wonder what is the difference, or anything I missed out?

Get old database off external hard drive

Posted: 02 Jul 2013 09:47 AM PDT

My hard drive crashed yesterday, luckily I had a back up. I didn't restore from backup though cause I needed a fresh system anyways. However there was a development database I would like back.

Is there a way to grab an old database file from my back up and somehow get that database onto my new localhost?

Auto increment value needs to be changed inside trigger

Posted: 02 Jul 2013 10:32 AM PDT

I have a slight problem with a table that I have converted which used to be MyISAM, I have now made it a InnoDB table.

Original structure:

id varchar(15),  seqno int(11) auto increment   Joint PK index on the above 2 fields

New structure:

auto_id INT (11) auto increment,  id varchar(15),  seqno int(11)

The original structure had a joint PK on id and seqno with seqno being auto incremented for each unique id value. It would then return the seqno as the last_insert_id in my web application.

With the new innodb structure, because innodb doesnt support joint primary keys, I've had to add in an auto_id which now has the auto increment.

I then implemented a trigger to generate the seqno unique per id value. My question is how do I set the auto increment id so that it returns in place of the auto_id value? This trigger would be called when I insert into my_table, so that it can calculate the next seqno value to insert with.

DROP TRIGGER IF EXISTS `innodb_seqno_trigger`;  //    CREATE TRIGGER `innodb_seqno_trigger` BEFORE INSERT ON `my_table`   FOR EACH ROW BEGIN    SET @newseqno = 0;    SELECT MAX(seqno) INTO @newseqno  FROM my_table  WHERE id = NEW.id;    SET NEW.seqno = (@newseqno + 1);  /* HERE I NEED HELP PLEASE TO GET THE AUTO INCREMENT VALUE TO ACTUALLY RETURN NEW.seqno */  END;  //

Thanks Peter

map user in oracle 10g

Posted: 02 Jul 2013 12:18 PM PDT

here is the scenario:

There are two schema created in oracle 10g, say db 1 and db 2. db1 can be accessed by username : sys with password and db2 can be accessed by username : test with password.

How is it possible to access db1 using username : test with password? In MSSQL there is an option for user mapping to different database. is there any such options in oracle 10g to do the same?

What are the valid formats of a PostgreSQL schema?

Posted: 02 Jul 2013 09:40 AM PDT

I can't seem to find documentation that describes the valid formats of a PostgreSQL schema. I know that a schema cannot:

  • start with a number
  • have spaces
  • start with pg_

What else? Where should I look?

What are the default settings for accessing MySQL remotely?

Posted: 02 Jul 2013 10:11 AM PDT

What is default settings in MYSQL for Remote access?

In CentOS the file /etc/my.cnf does not have skip-networking or bind-address. MySQL Version is 5.5, does it mean the server is enabled for remote access?

MySQL restart failed after adding skip-networking and bind-address=127.0.0.1 in my my.cnf file, so how can I disable remote access?

my.cnf

[mysqld]  skip-networking  innodb_file_per_table=1  innodb_file_format=barracuda  innodb_strict_mode=1  default-storage-engine=MyISAM  long_query_time=5  log_slow_queries=ON  thread_cache_size=32  key_buffer_size=64M  table_open_cache=1000  max_connections=300  query_cache_type=2  query_cache_limit=32M  query_cache_size=160M  max_heap_table_size=256M  tmp_table_size=256M  wait_timeout=360  interactive_timeout=360  max_allowed_packet=16M  sort_buffer_size=8M  innodb_buffer_pool_size=256M  innodb_log_file_size=64M

what is the best practice when making changes to the the application and database?

Posted: 02 Jul 2013 08:39 AM PDT

I need to make changes to an application which utilizes a database that is replicated across locations. I was wondering what the best practice is when working with databases and making changes to the database.

I will be backing up the database to dev environment and making changes to both the application and the database. Then when testing the application and the database I will likely make changes to the records. In that case, would it make sense run the t-sqls that I applied in the dev to the production database?

restrict user host settings to socket connection only

Posted: 02 Jul 2013 12:20 PM PDT

Is there a way to restrict a user's host setting so that (s)he can only connect by socket and not TCP/IP? I'd like to have this setting for the root account.

edit: As Abdul Manaf pointed out skip-networking can be used to turn off TCP/IP connectivity altogether. But can it be done on a user account basis?

Unable to create a new listener in oracle 11g

Posted: 02 Jul 2013 12:45 PM PDT

In net manager i get an error message "A valid logfile is required" when i click on listener and choose '+' button. and then it keeps on giving me that error when i click on any other tab or button in net manager.

Why doesn't DELETE + REORG free diskspace (DB2)?

Posted: 02 Jul 2013 01:00 PM PDT

In DB2 I have a table containing large binary data. Now i purged the whole table and ran runstats, reorg, runstats, but the amount of disk space taken does not change. What could be wrong here?

The table resides in its own tablespace which I created as follows:

CREATE BUFFERPOOL "MY_BP" SIZE 250 AUTOMATIC PAGESIZE 4096;  CREATE LARGE TABLESPACE MY_TBS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 BUFFERPOOL MY_BP OVERHEAD 10.500000 TRANSFERRATE 0.140000 FILE SYSTEM CACHING;

I deleted/reorged as follows:

DELETE FROM MY_TBL  RUNSTATS ON TABLE MY_TBL WITH DISTRIBUTION AND DETAILED INDEXES ALL  REORG TABLE MY_TBL  RUNSTATS ON TABLE MY_TABLE WITH DISTRIBUTION AND DETAILED INDEXES ALL  ALTER TABLESPACE MY_TBS REDUCE

The table MY_TBL took up 2.5GB before all that and after deleting/reorging it uses only 3 MB less.

FWIW: I'm running DB2/NT v9.5.2.

event scheduler not called

Posted: 02 Jul 2013 02:46 PM PDT

I had created one event scheduler which looks like this

mysql> show create event event1      -> ;  +--------+----------+-----------+--------------------------------------------------------------------------------------------+  | Event  | sql_mode | time_zone | Create Event  | character_set_client | collation_connection | Database Collation |  +--------+----------+-----------+-----------------------------------------------------------------------+----------------------+----------------------+    | event1 |          | SYSTEM    | CREATE DEFINER=`root`@`localhost` EVENT `event1` ON SCHEDULE EVERY 1 MONTH STARTS '2013-02-02 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN   update samp set col1 =col1  + 1; END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |  +--------+----------+-----------+-----------------------------------------------------------------------+----------------------+----------------------+---------  -----------+  1 row in set (0.00 sec)

This events has not called on 1st of month. So i tried show processlist\g to find it is runnung or not; it showed following o/p

mysql> show processlist\g;  +-----+-----------------+-----------------+---------------+---------+------+---     | Id  | User            | Host            | db            | Command | Time | State                       | Info             |  +-----+-----------------+-----------------+---------------+---------+------+---  | 136 | event_scheduler | localhost       | NULL          | Daemon  | 1855 | Waiting for next activation | NULL    |

so NULL in db col means that no DB is assigned to it?

Please help me to solve it.

FETCH API_CURSOR causing open transaction in tempDB

Posted: 02 Jul 2013 04:46 PM PDT

A select statement run from Oracle 11gR1 to SQL Server 2005 using Gateway leaves an open transaction in tempdb. The user is a datareader in Database XYZ. Not sure why the open tran is in tempDB when the statement run is SELECT.

Any one had seen this issue before ?

Thanks in advance sekhar

Synchronising SQL tables in two locations [on hold]

Posted: 02 Jul 2013 09:34 AM PDT

We have a bit of a conundrum at the moment.

ANSI SQL solutions please.

We have two databases located at two different geographic locations and will be using the web for all traffic between the two locations. We have a few tables common between the two databases that need to be sync'd periodically (i.e. at the end of the day). With such syncs, we are worried about many things like security, congested traffic (could be thousands of records), and data integrity.

The end game is to store files in each record (up to 10MB) so the sync needs to be efficient. (Not a priority at the moment.)

There may be records that need to be sync'd both directions (i.e a column on one updated, then a column on two updated, the one record needs these two changes).

What software/hardware options are there out there?

Is there another way without using triggers (want to get away from this)?

Thanks, been a while since I dealt with these technologies.

BACKUP failed to complete - with spaces, for no reason

Posted: 02 Jul 2013 11:26 AM PDT

Every day my database backup fails for some databases and does fine for others. I'm calling the backup through a Stored Procedure that runs scheduled in a job. Tonight I'm going to run the procedure in a query window with a WAITFOR in place just to check. In the \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG the message is:

2011-12-28 07:49:52.43 Backup      Error: 3041, Severity: 16, State: 1.  2011-12-28 07:49:52.43 Backup      BACKUP failed to complete the command BACKUP DATABASE                                                                                                                                 . Check the backup application log for detailed messages.

Obs: the log actually contains all these spaces and no further info.
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog