Sunday, July 7, 2013

[SQL Server 2008 issues] Simple select query with no where or join takes a long time

[SQL Server 2008 issues] Simple select query with no where or join takes a long time

Simple select query with no where or join takes a long time

Posted: 06 Jul 2013 07:23 PM PDT

Do you guys have any idea why a simple select statement without any joins or where clause would take over 9 minutes to run? The table holds under 300,000 records and isn't used for anything yet.Table:[code="plain"]CREATE TABLE [dbo].[TEMP_RSS_PRODUCTS2]( [product_name] [varchar](500) NULL, [noride_add] [varchar](70) NULL, [manufacturer_name] [varchar](50) NULL, [product_longdesc] [nvarchar](4000) NULL, [pno] [varchar](100) NOT NULL, [product_no] [int] NOT NULL, [main_part_no] [varchar](50) NULL, [mfr_pno] [varchar](50) NULL, [price] [smallmoney] NULL, [product_id] [varchar](70) NULL, [weight] [float] NULL, [width] [float] NULL, [height] [float] NULL, [depth] [float] NULL, [est_min_shipping] [smallmoney] NULL, [dropship_fee] [smallmoney] NULL, [qualifies_for_free_shipping] [bit] NULL, [status] [varchar](4) NULL, [upc_code] [varchar](30) NULL, [qty_available] [int] NULL, [vehicle_specific] [bit] NULL, [pic] [varchar](255) NULL, [option_desc] [varchar](60) NULL, [ship_zip] [char](5) NULL, [keywords] [varchar](1024) NULL, [retail_price] [smallmoney] NULL, [main_product_name] [varchar](300) NULL, [gender] [char](1) NULL, [age] [char](5) NULL, [color] [varchar](50) NULL, [material] [varchar](50) NULL, [viscosity] [varchar](20) NULL, [size] [varchar](30) NULL, [size_unit] [varchar](15) NULL, [sale_price] [smallmoney] NULL, [sale_end_dt] [smalldatetime] NULL, [pattern] [varchar](50) NULL, [num_options] [int] NULL, [p_upc] [varchar](30) NULL, [p_isbn] [varchar](30) NULL, [option_id] [uniqueidentifier] NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOCREATE NONCLUSTERED INDEX [IX_TEMP_RSS_PRODUCTS2_pno] ON [dbo].[TEMP_RSS_PRODUCTS2] ( [pno] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_TEMP_RSS_PRODUCTS2_product_no] ON [dbo].[TEMP_RSS_PRODUCTS2] ( [product_no] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]GO[/code]Query selects all columns except the last 4:[code="plain"]select product_name, noride_add, manufacturer_name, product_longdesc, pno, product_no, main_part_no, mfr_pno, price, product_id, [weight], width, height, depth, est_min_shipping, dropship_fee, qualifies_for_free_shipping, [status], upc_code, qty_available, vehicle_specific, pic, option_desc, ship_zip, keywords, retail_price, main_product_name, gender, age, color, material, viscosity, size, size_unit, sale_price, sale_end_dt, patternfrom dbo.TEMP_RSS_PRODUCTS2[/code]Execution plan says:SELECT Cost: 0%Table Scan Cost: 100%Thanks for any help.

Service pack 2 SQL Server 2008 R2 is released

Posted: 06 Jul 2013 10:05 AM PDT

http://support.microsoft.com/kb/2630458*never mind, old news. rss acting up

How to load dynamically created excel into DB table (SQL Server 2012) using SSIS

Posted: 05 Jul 2013 09:46 PM PDT

Hi All,I need to load excel sheet data into database table in SQL Server (2012) using SSIS (2012). The columns of the excel sheet are more than 800 and they are not static. The excel sheet will be provided at monthly basis with different number of columns and number and nature of the columns will vary each month.For example in July 2013, there are 850 columns in the excel sheet and in August 2013, it can be expected 900 columns in the excel sheet. We have to import the excel sheet into the database table. The target table will be created each time before loading the table from excel sheet. The columns of the target table will be determined by reading the header columns in the excel sheet.Please help me out to get the solution for the above problem.

Please HELP !! :SQL SERVER with MySQL Linked Server. Error Executing Trigger

Posted: 06 Jul 2013 11:42 AM PDT

Hi sirs ! If somebody can help me i would appreciate a lot !I have SQL server with MySQL Linked server through ODBC.Also have a Trigger on SQL Server Table :[i]ALTER TRIGGER [dbo].[TriggerStock] ON [dbo].[T_Contabilidad] FOR UPDATE,insertASBEGINDECLARE @Id integer, @Stock integer SELECT @ID = Idt_Conta,@stock=Stock FROM INSERTED IF @Stock>0 BEGIN UPDATE OPENQUERY(MYSQLVINC,'SELECT Idt_web, disponible FROM mibasedatos.t_web where idt_web=@id') SET disponible= 1 END ELSE BEGIN UPDATE OPENQUERY(MYSQLVINC,'SELECT Idt_web, disponible FROM mibasedatos.t_web where idt_web=@id') SET disponible= 0 ENDEND[/i]The idea : I NEED TO UPDATE MySQL TABLE WHEN SQL SERVER Table is UPDATED/INSERTPROBLEM : SQL throws an error :[b]Mens 7390, Nivel 16, Estado 2, Procedimiento TriggerStock, Línea 18No se pudo realizar la operación solicitada porque el proveedor OLE DB "MSDASQL" del servidor vinculado "MYSQLVINC" no admite la interfaz de transacción requerida[/b].(In english : Could'nt do the operation because of OLE DB MSDASQL from linked server does'nt admit transation required interface.Any help please ? Thanks in advance

Ranking Based of Advert Breaks

Posted: 06 Jul 2013 11:43 AM PDT

Hi People,This is a tough one for me. Let me start by explaining what i want to do then posting my code and data for you guys to give me ideas on how i can go about it.I have this table that shows date, time, programme code(i.e. brandflag field) and station that an advert is being captured:[code="sql"]CREATE TABLE [dbo].[TestTable]( [AdDate] [datetime] NULL, [AdTime] [nvarchar](8) NULL, [FK_StationId] [nvarchar](5) NULL, [BrandFLag] [nvarchar](5) NULL) ON [PRIMARY]GOINSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:36', N'A1', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:20:00', N'A1', N'T1004')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:43:00', N'A1', N'T1004')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:58:22', N'A1', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:10:57', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:12:22', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:30:51', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:31:00', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:32:00', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:33:00', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:00', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:01', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:02:57', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:00', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:23', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:07:59', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:08:34', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:00', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:01', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:02', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'23:35:00', N'A1', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'06:30:16', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:00:00', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:30:05', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:31:30', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:32:27', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:45:45', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:10', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:48:12', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:01:18', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:02:18', N'A10', N'T1442')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:30:31', N'A10', N'T1443')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'10:01:00', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'15:58:05', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:03:00', N'A10', N'T202')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:02', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:14', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:00:13', N'A10', N'T1154')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:31:15', N'A10', N'T219')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:00:00', N'A10', N'T1154')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:30:37', N'A10', N'T231')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:58:40', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:59:47', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:00:00', N'A10', N'T375')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:17:46', N'A10', N'T1164')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:21:53', N'A10', N'T1164')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:31:42', N'A10', N'T1154')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:58:22', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:00:00', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:01:53', N'A10', N'T0')[/code]And i have this code that shows the position of each advert per each programme (brandflag):[code="sql"]SELECT AdDate, AdTime, FK_StationId,brandflag, CAST(rank() over(partition by brandflag order by fk_stationid, addate,adtime) AS VARCHAR(10)) + '/ ' + CAST(count(*) over(partition by fk_stationid,brandflag) AS VARCHAR(10)) as PositionFROM dbo.testtableGROUP BY AdDate, AdTime, FK_StationId, brandflagHAVING (AdDate = CONVERT(DATETIME, '2013-01-04 00:00:00', 102))order by fk_stationid, addate, adtime[/code]That gives me this result:[code="sql"]CREATE TABLE [dbo].[TestResult]( [AdDate] [datetime] NULL, [AdTime] [nvarchar](8) NULL, [FK_StationId] [nvarchar](5) NULL, [brandflag] [nvarchar](5) NULL, [Position] [varchar](22) NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:36', N'A1', N'T79', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:20:00', N'A1', N'T1004', N'1/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:43:00', N'A1', N'T1004', N'2/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:58:22', N'A1', N'T0', N'1/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:10:57', N'A1', N'T21', N'1/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:12:22', N'A1', N'T21', N'2/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:30:51', N'A1', N'T21', N'3/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:31:00', N'A1', N'T21', N'4/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:32:00', N'A1', N'T21', N'5/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:33:00', N'A1', N'T21', N'6/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:00', N'A1', N'T21', N'7/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:01', N'A1', N'T21', N'8/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:02:57', N'A1', N'T320', N'1/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:00', N'A1', N'T320', N'2/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:23', N'A1', N'T320', N'3/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:07:59', N'A1', N'T320', N'4/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:08:34', N'A1', N'T320', N'5/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:00', N'A1', N'T320', N'6/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:01', N'A1', N'T320', N'7/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:02', N'A1', N'T320', N'8/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'23:35:00', N'A1', N'T0', N'2/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'06:30:16', N'A10', N'T79', N'2/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:00:00', N'A10', N'T0', N'3/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:30:05', N'A10', N'T79', N'3/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:31:30', N'A10', N'T79', N'4/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:32:27', N'A10', N'T79', N'5/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:45:45', N'A10', N'T79', N'6/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:10', N'A10', N'T79', N'7/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:48:12', N'A10', N'T79', N'8/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:01:18', N'A10', N'T0', N'4/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:02:18', N'A10', N'T1442', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:30:31', N'A10', N'T1443', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'10:01:00', N'A10', N'T0', N'5/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'15:58:05', N'A10', N'T0', N'6/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:03:00', N'A10', N'T202', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:02', N'A10', N'T0', N'7/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:14', N'A10', N'T0', N'8/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:00:13', N'A10', N'T1154', N'1/ 3')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:31:15', N'A10', N'T219', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:00:00', N'A10', N'T1154', N'2/ 3')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:30:37', N'A10', N'T231', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:58:40', N'A10', N'T0', N'9/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:59:47', N'A10', N'T0', N'10/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:00:00', N'A10', N'T375', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:17:46', N'A10', N'T1164', N'1/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:21:53', N'A10', N'T1164', N'2/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:31:42', N'A10', N'T1154', N'3/ 3')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:58:22', N'A10', N'T0', N'11/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:00:00', N'A10', N'T0', N'12/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:01:53', N'A10', N'T0', N'13/ 11')[/code]Actually, the kind of result i'm looking for is something like breaking the results into separate advert breaks, e.g. when you have a programme, one way of determining the advert breaks in the programme is to look at the closeness of the adverts e.g: looking at the results below, you will notice that the first record i.e '1/8' and '2/8' are close together having the times '21:10:57' and '21:12:22', but for the rest of the timing they start from '21:30:51' through to '21:34:01', i would consider that as the 2nd advert break and consider the first set as the first advert break.I would like to get a result displays in this format e.g. '1/2 of 1' meaning that for the first advert break, the first advert is advert 1 of 2 of the first advert break i.e ('21:10:57') and ('21:12:22') is advert '2/2 of 1' i.e breaking the adverts basedon advert breaks.Then the rest will follow suit e.g. ('21:30:51') will be '1/6 of 2' meaning advert 1 of six adverts in the 2nd break.[code="sql"]INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:10:57', N'A1', N'T21', N'1/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:12:22', N'A1', N'T21', N'2/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:30:51', N'A1', N'T21', N'3/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:31:00', N'A1', N'T21', N'4/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:32:00', N'A1', N'T21', N'5/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:33:00', N'A1', N'T21', N'6/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:00', N'A1', N'T21', N'7/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:01', N'A1', N'T21', N'8/ 8')[/code]Please if you need any clarification, i will be willing to explain again, i hope there is a solution to this.Thanks very much.Tim

Differential restore without full backup restore!!

Posted: 06 Jul 2013 06:53 AM PDT

HII have 2 servers. I do a full backup of serverA and restore it to ServerB with NORECOVERY.Now I do a differential backup of ServerA after 1 hr and Apply it to ServerB with RECOVERY. So my ServerB become replica of ServerA. Now going forward, I just want to do differential backups of ServerA and apply them onServerB. To achieve this, Do I always need to do a full database restore of ServerA with NORECOVERY and then Apply differential with RECOVERY? Full backup size is big , around 50GB and it takes time to do it everytime. Is there a way just to apply the differential without first restoring the full DB?

for me problem this select insert

Posted: 05 Jul 2013 08:59 PM PDT

create table table1(phone int,kod_name int ,kod_fname int,kod_country int,kod_auto int) -- create table table2 (name_ nvarchar(30),kod int) insert into table2 values('james',1)insert into table2 values('stivens',2)insert into table2 values('carlos',3)create table table3 (f_name nvarchar(30),kod int) insert into table2 values('john',1)insert into table2 values('tayson',2)insert into table2 values('swarzneger',3)create table table4 (country nvarchar(30),kod int) insert into table2 values('argentina',1)insert into table2 values('brazilia',2)insert into table2 values('korea',3)create table table5 (m_auto nvarchar(30),kod int) insert into table2 values('mersedec',1)insert into table2 values('jaguar',2)insert into table2 values('landrover',3)how i make created procedure for insert to table1 when name=james then in kod_name_d insert 1 (kod table2)when fname=jhon then in kod_fname insert 1 (kod table3 )when country=korea then in kod_country insert 3 (kod table4 )when auto=jaguar then in kod_auto insert 2 (kod table5 )forexamle i inserted table 1 (11111,'james','jhon','korea','jaguar')after inserted table1table1-----------phone kod_name kod_fname kod_country kod_auto ----- -------- --------- ----------- --------11111 1 1 3 2

last insert date

Posted: 05 Jul 2013 09:27 PM PDT

Hi,My question: i have database emit046 here i need all table last insert/update/deleted date time information required. please help.........thanks in advance
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog