Monday, August 19, 2013

[T-SQL] Query Help - To delete old files

[T-SQL] Query Help - To delete old files

Query Help - To delete old files

Posted: 07 Aug 2013 11:27 PM PDT

Hi All,Looking for assistance to develop T-SQL codeRequirement is to delete old files from directory & its sub-directories(say F:\Temp\test\), which were placed in directory before 1 week and not modified.The files can be of any type :txt,bak,zip,doc,bak,mdf,ldfThanks in advance for any help.

How to restrict insertion depends on condition?

Posted: 18 Aug 2013 05:16 PM PDT

Hi Friends,create table travel(user_id varchar(10),from varchar(10),to varchar(10),Lodging varchar(10),foodBill varchar(10),DailyAllowance varchar(10))insert into travel (user_id,from,to,Lodging,foodbill,DailyAllowance) values('002','chennai','Banglore','4500','200',' ')insert into travel (user_id,from,to,Lodging,foodbill,DailyAllowance) values('002','chennai','Banglore',' ',' ','500')now mY requirement is when the user select Lodging & FoodBill Daily allowance ll be null.if users choose DailyAllowance that Lodging & FoodBill ll be Null.how to write procedure for these condition?

SQL:StatementCompleted trace event?

Posted: 18 Aug 2013 06:14 PM PDT

Hi,I have found one of the SQL blogs for tracing SP completed duration as below codeIn this code, How do we include SQL:StatementCompleted trace event?[code="sql"] CREATE PROCEDURE [StartTrace] AS BEGIN DECLARE @trc INT DECLARE @TraceID INT DECLARE @maxfilesize BIGINTSET @maxfilesize = 100DECLARE @file NVARCHAR(500) SELECT @file = N'D:\TraceCollection\Test_Trace'EXEC @trc = SP_TRACE_CREATE @traceid = @TraceID output, @options = 2, --TRACE_FILE_ROLLOVER @tracefile = @file, @maxfilesize = @maxfilesize, @stoptime = NULL, @filecount = 10000-- Set the events --Event number 43 is SP:Complete --right after 43, we have number like 1, 12, 14 etc. which are column numbers of event DECLARE @on BIT, @ret INT SET @on = 1 EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 1, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 13, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 14, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 15, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 18, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 28, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 34, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 35, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 48, @on EXEC @ret = SP_TRACE_SETEVENT @TraceID, 43, 59, @onEXEC SP_TRACE_SETFILTER @TraceID, 28, -- 28 is a column number for "Object Type" 0, -- and 0, -- == 8272 --we are filtering that only SPs should come and store in trace file-- Set the trace status to start EXEC sp_trace_setstatus @TraceID, 1END GO[/code]Thanks

Automated SQL code to XML test data files using systems Tables

Posted: 18 Aug 2013 08:34 AM PDT

I created a stored procedure which produces xml test data from any table using [INFORMATION_SCHEMA.COLUMNS] The stored proc is supposed to create 3 xml tests files - Customers, Booking, and Ancillary. Where a customer record number ends in a 1-10 integer value, create that number of Childrenselect Customer 1 then there will be 2 bookings and each of those bookings will have 2 ancillaries.E.g. Customer 2 will have 2 bookings and each of those bookings will have 2 ancillaries.E.g. Customer 3 will have 3 bookings and each of those bookings will have 3 ancillaries Customer seems to be working perfectly but I cannot seem to make the logic work with the child relationship with Booking and Ancillary plus the SQL code does not work.Once the sql script is created it should be able generate test xml files for any table in the system.Any insight would be helpful in how to fix the booking and ancillary problem, Thank you.Create table[code="sql"]/****** Object: Table [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] Script Date: 08/18/2013 22:09:32 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS]( [TABLE_CATALOG] [nvarchar](128) NULL, [TABLE_SCHEMA] [nvarchar](128) NULL, [TABLE_NAME] [sysname] NOT NULL, [COLUMN_NAME] [sysname] NULL, [ORDINAL_POSITION] [int] NULL, [COLUMN_DEFAULT] [nvarchar](4000) NULL, [IS_NULLABLE] [varchar](3) NULL, [DATA_TYPE] [nvarchar](128) NULL, [CHARACTER_MAXIMUM_LENGTH] [int] NULL, [CHARACTER_OCTET_LENGTH] [int] NULL, [NUMERIC_PRECISION] [tinyint] NULL, [NUMERIC_PRECISION_RADIX] [smallint] NULL, [NUMERIC_SCALE] [int] NULL, [DATETIME_PRECISION] [smallint] NULL, [CHARACTER_SET_CATALOG] [sysname] NULL, [CHARACTER_SET_SCHEMA] [sysname] NULL, [CHARACTER_SET_NAME] [sysname] NULL, [COLLATION_CATALOG] [sysname] NULL, [COLLATION_SCHEMA] [sysname] NULL, [COLLATION_NAME] [sysname] NULL, [DOMAIN_CATALOG] [sysname] NULL, [DOMAIN_SCHEMA] [sysname] NULL, [DOMAIN_NAME] [sysname] NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'CustomerContact', N'AddressID', 1, NULL, N'YES', N'bigint', NULL, NULL, 19, 10, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'CustomerContact', N'AddressOptin', 2, NULL, N'YES', N'bit', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'CustomerContact', N'AddressLine1', 3, NULL, N'YES', N'nvarchar', 60, 120, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'CustomerContact', N'AddressLine2', 4, NULL, N'YES', N'nvarchar', 60, 120, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'AncillarySummary', N'CustomerID', 1, NULL, N'YES', N'bigint', NULL, NULL, 19, 10, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'AncillarySummary', N'AddressID', 2, NULL, N'YES', N'bigint', NULL, NULL, 19, 10, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'AncillarySummary', N'EmailAddress', 3, NULL, N'YES', N'nvarchar', 255, 510, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'AncillarySummary', N'AncillaryBrandCode', 4, NULL, N'YES', N'nvarchar', 30, 60, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'BookingSummary', N'AccommodationTypeCode', 1, NULL, N'YES', N'nvarchar', 20, 40, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'BookingSummary', N'AccomodationArrivalDate', 2, NULL, N'YES', N'datetime', NULL, NULL, NULL, NULL, NULL, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'BookingSummary', N'AddressCountry', 3, NULL, N'YES', N'nvarchar', 30, 60, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'BookingSummary', N'AddressID', 4, NULL, N'YES', N'bigint', NULL, NULL, 19, 10, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)[/code]Based on the Master XML I created the following SQLto XML test datat script:[code="sql"]---SQL Customer --it seems worksCreate procedure [dbo].[HealthCreateTestEntities](@CustomersTocreate int)asbegindeclare @CustomerCounter int, @ChildElements int, @Bookings int, @Ancillary intdeclare @XMLFileCustomers table( RecordNumber int identity (1,1) ,XMLRecordType varchar(20) ,XMLRecord varchar(2000))select @CustomerCounter = 1--Customersinsert @XMLFileCustomers values('Customer','<?xml version="1.0"?>')insert @XMLFileCustomers values('Customer','<CustomerContacts xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="CustomerContactSummary.xsd">')while @CustomerCounter <= @CustomersTocreatebegin select @ChildElements = case when right(cast(@CustomerCounter as varchar(10)),1) = 1 then 1 when right(cast(@CustomerCounter as varchar(10)),1) = 2 then 2 when right(cast(@CustomerCounter as varchar(10)),1) = 3 then 3 when right(cast(@CustomerCounter as varchar(10)),1) = 4 then 4 when right(cast(@CustomerCounter as varchar(10)),1) = 5 then 5 when right(cast(@CustomerCounter as varchar(10)),1) = 6 then 6 when right(cast(@CustomerCounter as varchar(10)),1) = 7 then 7 when right(cast(@CustomerCounter as varchar(10)),1) = 8 then 8 when right(cast(@CustomerCounter as varchar(10)),1) = 9 then 9 when right(cast(@CustomerCounter as varchar(10)),1) = 0 then 10 end select @Bookings = @ChildElements, @Ancillary = @ChildElements--Customerinsert @XMLFileCustomers values('Customer','<CustomerContact>') insert @XMLFileCustomers select 'Customer','<' + COLUMN_NAME + '>' + case when COLUMN_NAME like '%code' then COLUMN_NAME + cast(@CustomerCounter as varchar) when data_Type in ('bigint', 'int', 'decimal') then cast(@CustomerCounter as varchar) when COLUMN_NAME = 'CustomerDOB' or COLUMN_NAME like '%date' then cast(dateadd(month, @CustomerCounter, cast('01-12-2012' as date)) as nvarchar) when COLUMN_NAME like '%Time' then '20:59:15' when COLUMN_NAME = 'LandlinePhoneSource' then cast(@CustomerCounter as varchar) when data_Type = 'bit' then case when @CustomerCounter % 2 = 0 then '1' else '0' end ELSE COLUMN_NAME + cast(@CustomerCounter as varchar) END + '</' + COLUMN_NAME + '>' from [Fake_INFORMATION_SCHEMA.COLUMNS] where table_name = 'CustomerContact' set @CustomerCounter = @CustomerCounter + 1 ENDinsert @XMLFileCustomers values('Customer','</ShortBookingSummaries>')insert @XMLFileCustomers values('Customer','</CustomerContact>')insert @XMLFileCustomers values('Customer','</CustomerContacts>') select * from @XMLFileCustomerswhere XMLRecordType = 'Customer'end[/code][code="sql"]--BOOKING does not work declare @XMLFileBookings table( RecordNumber int identity (1,1) ,XMLRecordType varchar(20) ,XMLRecord varchar(2000))select @CustomerCounter = 1while @CustomerCounter <= @CustomersTocreatebegin select @ChildElements = case when right(cast(@CustomerCounter as varchar(10)),1) = 1 then 1 when right(cast(@CustomerCounter as varchar(10)),1) = 2 then 2 when right(cast(@CustomerCounter as varchar(10)),1) = 3 then 3 when right(cast(@CustomerCounter as varchar(10)),1) = 4 then 4 when right(cast(@CustomerCounter as varchar(10)),1) = 5 then 5 when right(cast(@CustomerCounter as varchar(10)),1) = 6 then 6 when right(cast(@CustomerCounter as varchar(10)),1) = 7 then 7 when right(cast(@CustomerCounter as varchar(10)),1) = 8 then 8 when right(cast(@CustomerCounter as varchar(10)),1) = 9 then 9 when right(cast(@CustomerCounter as varchar(10)),1) = 0 then 10 end select @Bookings = @ChildElements, @Ancillary = @ChildElements while @Bookings > 0 begin insert @XMLFileBookings values('BookingStartTag','<Booking>') insert @XMLFileBookings values('Booking','<BookingRef>BR_'+cast(@CustomerCounter as varchar)+'_'+cast(@Bookings as varchar)+'</BookingRef><TotalRevenue>'+cast(@Bookings * 100 as varchar)+'</TotalRevenue>') Insert @XMLFileBookings select 'Booking','<' + COLUMN_NAME + '>' + case when COLUMN_NAME like '%code' then COLUMN_NAME + cast(@Bookings as varchar) when data_Type in ('bigint', 'int', 'decimal') then cast(@Bookings as varchar) when data_Type in ('bit') ---case when @CustomerCounter % 2 = 0 then '1' else '0' then 'Booking' +''+ cast(@Bookings as varchar) else COLUMN_NAME + cast(@Bookings as varchar) END + '</' + COLUMN_NAME + '>' from [Fake_INFORMATION_SCHEMA.COLUMNS] where table_name = 'BookingSummary' set @CustomerCounter = @CustomerCounter + 1end select @Bookings = @Bookings - 1 select @Ancillary = @ChildElementsselect * from @XMLFileBookingswhere XMLRecordType = 'Booking'end[/code][code="sql"]--ANCILLARY also does not workselect @CustomerCounter = 1declare @XMLFileAncillaries table( RecordNumber int identity (1,1) ,XMLRecordType varchar(20) ,XMLRecord varchar(2000))while @CustomerCounter <= @CustomersTocreatebegin select @ChildElements = case when right(cast(@CustomerCounter as varchar(10)),1) = 1 then 1 when right(cast(@CustomerCounter as varchar(10)),1) = 2 then 2 when right(cast(@CustomerCounter as varchar(10)),1) = 3 then 3 when right(cast(@CustomerCounter as varchar(10)),1) = 4 then 4 when right(cast(@CustomerCounter as varchar(10)),1) = 5 then 5 when right(cast(@CustomerCounter as varchar(10)),1) = 6 then 6 when right(cast(@CustomerCounter as varchar(10)),1) = 7 then 7 when right(cast(@CustomerCounter as varchar(10)),1) = 8 then 8 when right(cast(@CustomerCounter as varchar(10)),1) = 9 then 9 when right(cast(@CustomerCounter as varchar(10)),1) = 0 then 10 end while @Ancillary > 0 begin insert @XMLFileAncillaries values('AncillaryStartTag','<Ancillary>') insert @XMLFileAncillaries values('Ancillary','<BookingRef>BR_'+cast(@CustomerCounter as varchar)+'_'+cast(@Ancillary as varchar)+'</BookingRef><AncillaryRevenue>'+cast(@Ancillary * 100 as varchar)+'</AncillaryRevenue>') Insert @XMLFileAncillaries select 'Ancillary','<' + COLUMN_NAME + '>' + case when COLUMN_NAME like '%code' then COLUMN_NAME + cast(@Ancillary as varchar) when data_Type in ('bigint', 'int', 'decimal') then cast(@Ancillary as varchar) when data_Type in ('bit') ---case when @CustomerCounter % 2 = 0 then '1' else '0' then 'Ancillary' +''+ cast(@Ancillary as varchar) else COLUMN_NAME + cast(@Ancillary as varchar) END + '</' + COLUMN_NAME + '>' from [Fake_INFORMATION_SCHEMA.COLUMNS] where table_name = 'AncillarySummary' insert @XMLFileAncillaries values('Ancillary','</Ancillary>') select @Ancillary = @Ancillary - 1 end select @Bookings = @Bookings - 1 select @Ancillary = @ChildElements end set @CustomerCounter = @CustomerCounter + 1endselect * from @XMLFileAncillarieswhere XMLRecordType = 'Ancillary'---the 1 is the number of customers selected, it could be any number.exec [dbo].[HealthCreateTestEntities] 1[/code]

how to view log files

Posted: 18 Aug 2013 03:11 AM PDT

This gives me the location of the log file. How do I view it?SELECT name, physical_name AS current_file_locationFROM sys.master_files where name = 'MyDatabase'
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog