Friday, May 17, 2013

[SQL Server 2008 issues] Batch file in SQL Procedure

[SQL Server 2008 issues] Batch file in SQL Procedure

Batch file in SQL Procedure

Posted: 16 May 2013 06:33 PM PDT

Hi Team,Am trying to call a batch in sql server stored proc.CREATE PROCEDURE test_batchASBEGIN declare @bat varchar(200) set @bat = 'd:\test\test.bat' exec master..xp_cmdshell @batENDbut am getting below error -- The system cannot find the path specified.-- NULLCan u please help me....

SQL Service Broker suddenly stops working

Posted: 16 May 2013 04:40 PM PDT

Hi,I'm new in using service broker. Our existing service broker suddenly stops working and I can't figure out why.When I try to enable service broker (which is already enabled) I get this in SQL error log:An error occurred in the service broker manager, Error: 3602, State: 25.Also tried creating new message queues. I am able to see the created queues under sys.service_queues and sys.objects but not in sys.dm_broker_queue_monitors.

How to convince people for small changes

Posted: 09 May 2013 11:07 PM PDT

I was checking in my database and found lots of unused indexes. I found it based on summing up the index seeks, index scans and index lookups and comparing this with index updates. Now with this data and by checkig at the number of indexes within the table, I can say which index is not required and is only adding cost by only getting used in writes.Now the issue I face is that for every such change, my management asks me to do some profit analysis and show significant profit with the change. Now how is it possible to show some process's benefit by just removing unused index. If I show them the numbers calculated as mentioned above, then there is a logic put up that it might cause creating bad execution plans and blah blah. Have anyone faced such issues or it's just with me ?

Pivot/Unpivot function with multiple aggregates?

Posted: 16 May 2013 06:37 AM PDT

I'm a newbie to the forum and I was hoping that I could get some help with my script. I have a simple query that I would like to transpose the rows to columns but I'm unsure of how to do that having multiple columns. My script is as follows:[code="sql"]SELECT dt.YEAR [caucasian_enrollment] = SUM(caucasian_enrollment) ,[black_or_african_american_enrollment] = SUM(black_or_african_american_enrollment) ,[asian_enrollment] = SUM(asian_enrollment) ,[hispanic_enrollment] = SUM(hispanic_enrollment) ,[native_hawaiian_pacific_islander_enrollment] = SUM(native_hawaiian_pacific_islander_enrollment) ,[american_indian_or_alaskan_ative_enrollment] = SUM(american_indian_or_alaskan_ative_enrollment) ,[multiracial_enrollment] = SUM(multiracial_enrollment) ,[unknown_ethnicity_enrollment] = SUM(unknown_ethnicity_enrollment) FROM mart.dbo.fact_enrollment_school AS fes INNER JOIN dim_time AS dt ON fes.time_key = dt.time_key INNER JOIN dim_time AS dt2 ON fes.time_key = dt.time_key GROUP BY dt.year[/code][b]My data currently looks like the following:[/b]Year caucasian Black Asian....2012 3000 1000 1002011 4000 2000 502010 2500 1500 30I would like it to look like the following:Race 2012 % inc/dec 2011 % inc/dec 2010Caucasian 3000 -.33 4000 .6 2500Black 1000 -.5 2000 .25 1500Asian 100 .5 50 .4 30I have started my script below but cannot get it to work correctly with multiple aggregates:[code="sql"]WITH cte AS ( SELECT * FROM ( SELECT [year] , --aggregates FROM mart.[dbo].[vw_salary] ) AS source PIVOT ( SUM(total_salary) FOR [year] IN ( [2012], [2011], [2010], [2009], [2008]) ) as pvt ) SELECT [2012] , ( CASE WHEN [2011] <> 0 THEN CONVERT(DECIMAL(18, 2), ( [2012] - [2011] ) / CONVERT(DECIMAL(18, 2), ( [2011] ))) * 100 END ) AS '% End Balance -/+' , [2011] , ( CASE WHEN [2010] <> 0 THEN CONVERT(DECIMAL(18, 2), ( [2011] - [2010] ) / CONVERT(DECIMAL(18, 2), ( [2010] ))) * 100 END ) AS '% End Balance -/+' , [2010] , ( CASE WHEN [2009] <> 0 THEN CONVERT(DECIMAL(18, 2), ( [2010] - [2009] ) / CONVERT(DECIMAL(18, 2), ( [2009] ))) * 100 END ) AS '% End Balance -/+' , [2009] , ( CASE WHEN [2008] <> 0 THEN CONVERT(DECIMAL(18, 2), ( [2009] - [2008] ) / CONVERT(DECIMAL(18, 2), ( [2008] ))) * 100 END ) AS '% End Balance -/+' FROM cte [/code]Here you will find a sample of my data set. I would really appreciate your help:[code="sql"]CREATE TABLE [dbo].[dim_time]( [time_key] [int] IDENTITY(1,1) NOT NULL, [year] [int] NULL ), CONSTRAINT [PK_dim_time_time_key] PRIMARY KEY CLUSTERED ( [time_key] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOEXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'dim_time' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dim_time'GOSET IDENTITY_INSERT [dbo].[dim_time] ONINSERT [dbo].[dim_time] ([time_key], [year]) VALUES (1, 2007)INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (2, 2008)INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (3, 2009)INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (4, 2010)INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (5, 2011)INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (6, 2012)INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (7, 2013)INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (8, 2006)SET IDENTITY_INSERT [dbo].[dim_time] OFF/****** Object: Table [dbo].[fact_enrollment_school] Script Date: 05/16/2013 14:00:01 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[fact_enrollment_school]( [fact_enrollment_school_key] [int] IDENTITY(1,1) NOT NULL, [building_key] [int] NULL, [time_key] [int] NULL, [total_enrollment] [decimal](18, 2) NULL, [asian_enrollment] [decimal](18, 2) NULL, [native_hawaiian_pacific_islander_enrollment] [decimal](18, 2) NULL, [black_or_african_american_enrollment] [decimal](18, 2) NULL, [hispanic_enrollment] [decimal](18, 2) NULL, [caucasian_enrollment] [decimal](18, 2) NULL, [american_indian_or_alaskan_ative_enrollment] [decimal](18, 2) NULL, [multiracial_enrollment] [decimal](18, 2) NULL, [unknown_ethnicity_enrollment] [decimal](18, 2) NULL, [male_enrollment] [decimal](18, 2) NULL, [female_enrollment] [decimal](18, 2) NULL, [unknown_gender_enrollment] [decimal](18, 2) NULL, [low_income_enrollment] [decimal](18, 2) NULL, [free_reduced_lunch_count] [decimal](18, 2) NULL, [free_lunch_count] [decimal](18, 2) NULL, [reduced_lunch_count] [int] NULL, [last_create_date] [datetime] NULL, [last_update_date] [datetime] NULL, [student_with_a_disability] [decimal](18, 2) NULL, CONSTRAINT [PK_fact_enrollment_school] PRIMARY KEY CLUSTERED ( [fact_enrollment_school_key] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[fact_enrollment_school] ONINSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (1, 9, 2, CAST(7.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(6.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (2, 9, 3, CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (3, 12, 2, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (4, 15, 2, CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (5, 15, 3, CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (6, 26, 6, CAST(139.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(6.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(124.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(77.00 AS Decimal(18, 2)), CAST(62.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(83.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (7, 26, 4, CAST(168.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(160.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(84.00 AS Decimal(18, 2)), CAST(84.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(80.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (8, 26, 5, CAST(149.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(136.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(79.00 AS Decimal(18, 2)), CAST(70.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(84.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (9, 26, 3, CAST(190.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(183.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(83.00 AS Decimal(18, 2)), CAST(107.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(83.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (10, 26, 2, CAST(183.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(178.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(86.00 AS Decimal(18, 2)), CAST(97.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(80.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (11, 32, 2, CAST(8.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(8.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (12, 35, 4, CAST(30.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(30.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(14.00 AS Decimal(18, 2)), CAST(16.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (13, 35, 5, CAST(19.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(14.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(8.00 AS Decimal(18, 2)), CAST(11.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (14, 35, 6, CAST(29.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(26.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(11.00 AS Decimal(18, 2)), CAST(18.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (15, 41, 2, CAST(7.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(7.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (16, 41, 4, CAST(6.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(6.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (17, 41, 3, CAST(12.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(12.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(10.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (18, 49, 2, CAST(166.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(166.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(81.00 AS Decimal(18, 2)), CAST(85.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(57.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (19, 49, 4, CAST(194.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(189.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(95.00 AS Decimal(18, 2)), CAST(99.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(76.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (20, 49, 3, CAST(191.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(7.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(184.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(99.00 AS Decimal(18, 2)), CAST(92.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(64.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (21, 52, 2, CAST(264.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(35.00 AS Decimal(18, 2)), CAST(222.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(148.00 AS Decimal(18, 2)), CAST(116.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (22, 55, 5, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (23, 55, 6, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (24, 55, 3, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (25, 55, 4, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (26, 58, 2, CAST(7.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(7.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (27, 61, 5, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (28, 61, 6, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (29, 61, 4, CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (30, 61, 2, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (31, 72, 5, CAST(11.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(9.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(10.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (32, 72, 4, CAST(22.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(14.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(17.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (33, 72, 3, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)[/code]

Try to use variable in procedure

Posted: 16 May 2013 03:51 PM PDT

Hi Professionals.I have the following procedure which bulk inserts a csv file into the newtable.Can this procedure be amended so that I can use variables passed in by what the user selects as I am trying to execute this procedure with front end written PHPso for instance can the bulk insert part be changedFROM ''C:\inetpub\wwwroot\Synergy_SAR_Raw.csv''TO ''C:\inetpub\wwwroot\VARIABLE PASSED IN HERE'' --As VARIABLE PASSED IN HERE is where I would want the user to choose a file from the directory in the form of a dropdown box on the front endhere is my codeUSE [TestData]GO/****** Object: StoredProcedure [dbo].[importSAR] Script Date: 05/17/2013 14:42:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[importSAR]asBEGIN IF EXISTS ( SELECT * FROM sys.tables JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id WHERE sys.schemas.name = 'dbo' AND sys.tables.name = 'newtable' )DROP TABLE dbo.newtable CREATE TABLE dbo.newtable( Software_Manufacturer Nvarchar(MAX) null, Part Nvarchar(MAX) null, Product_Description Nvarchar(MAX) null, Edition Nvarchar(MAX) null, Version Nvarchar(MAX) null, License_Type Nvarchar(MAX) null, Active_Qty NVARCHAR(MAX) null, Maintenance Nvarchar(MAX) null, Expiry_Date NVARCHAR(max) NULL, Qty_Used Nvarchar(MAX) null, Quantity_Pooled Nvarchar(MAX) null, Serial_1 Nvarchar(MAX) null, Serial_2 Nvarchar(MAX) null, Barcode Nvarchar(MAX) null, Authorisation_Number Nvarchar(MAX) null, Additional_Licence_Data_1 Nvarchar(MAX) null, Additional_Licence_Data_2 Nvarchar(MAX) null, Comments Nvarchar(MAX) NULL, Business_Unit Nvarchar(MAX) NULL, Cost_Centre Nvarchar(max) NULL, Purchase_Order nvarchar(MAX) null, Date_Of_Purchase NVARCHAR(max) NULL, Date_Of_Delivery NVARCHAR(max) NULL, Invoice Nvarchar(MAX) null, Supplier Nvarchar(MAX) null, Purchasing_Officer Nvarchar(MAX) NULL, actual_image Nvarchar(MAX) null)DECLARE @cmd nvarchar(max)SET @cmd = 'BULK INSERT newtableFROM ''C:\inetpub\wwwroot\Synergy_SAR_Raw.csv''WITH ( FIRSTROW = 4, FIELDTERMINATOR = '','',ROWTERMINATOR = '''')' EXEC(@cmd)end

update statement optimization?

Posted: 16 May 2013 07:25 AM PDT

Hello - I have a table with 6 million rows that I need to update.These rows will be updated based on a join to a temp table that has the needed update values.This will be a "real-time" update so the record updates need to be done in chunks to avoid locking.Someone I know with a deeper SQL background than myself suggested the following structure for performing the update:WHILE @@ROWCOUNT > 0 BEGIN update TOP(5000) ceb set ceb.new_WCDSCreatedOn = i.CreateDate OUTPUT inserted.New_IndividualId INTO @Updates from contactextensionbase ceb join #tmpIndividualCreateDates i on ceb.New_IndividualId = i.IndividualID WHERE NOT EXISTS ( SELECT * FROM @Updates AS Updates WHERE Updates.New_IndividualId = ceb.New_IndividualId ) ENDI think this SQL is fairly elegant but it doesn't seem to work the way we expect. For example, I executed the SQL and it ran for 2.5 hours without completing. It probably shouldn't take that long to update 6M rows.So have you executed an update statement like this before? Does this structure look correct or can you identify any flaws that seem to stand out?

error in Micorsoft 70-432 book

Posted: 16 May 2013 08:38 AM PDT

I am doing some practice exercises from Microsofts training kit for 70-432 Implementation and Maintenance 2008(written by Mike Hotek) and am running the following code from page 148 (chapter 6 distributing and partitioning data) - i am however getting an error. The error is shown below. I am running this from sql2012 developer management studio.use partitiontestSET NOCOUNT ONDECLARE @month int, @day intSET @month = 1set @day = 1while @month <= 12BEGIN WHILE @day <=28 begin INSERT dbo.orders (OrderDate, OrderAmount) SELECT cast(@month as varchar(2)) + '/' + cast(@day as varchar(2)) + '/2005', @day = 20 SET @DAY = @DAY +1END set @day = 1 set @month = @month + 1 end goThe error i get is:Msg 141, Level 15, State 1, Line 0A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.Msg 199, Level 15, State 1, Line 0An INSERT statement cannot contain a SELECT statement that assigns values to a variable.Is this because i am using sql2012 instead of 2008 ? I find it hard to imagine that what appears to be a basic error (for somebody who has written a book !) would be allowed to be published !!

How to alter a primary key on a Replicated Table in SQL Server 2008

Posted: 16 May 2013 08:23 AM PDT

We have a table in Sql Server 2008 R2, which has been (Transactional Replication) replicated to two subscriber. We need to increase the size of primary key column from char(14) to char(28).This specific article is part of publication that includes other articles. I fallowed the following steps and it wroked fine. 1- Remove that article from publication 2- Apply the changes on primary key on publisher and subscribers 3- Re-Add article to publication When I checked the web,I noticed that many people were talking about fallowing steps: 1- drop the subscription 2- drop the article 3- modify the table 4- add the article 5- add the subscription Do I need to drop the subscription?

Parse a filed to look up data???

Posted: 16 May 2013 07:48 AM PDT

Hi,Not sure how to start this, or even explain it right....I have this "Node path" field on a table looks like(@@)@@/1/140/@@/1/2/3/84/6/11/12/82/7/28/@@/1/2/3/84/6/11/12/82/7/28/76/@@/1/2/3/84/6/11/12/82/7/28/76/77/@@after looking it up each set between @@ is a path with the last number set = node_id on another tablefor above 140 = transaction 28 = client 76 = transportetc......any ideas on how to parse and look up the values?Thanks

execute a stored proc within a string through xp_cmdshell

Posted: 16 May 2013 02:46 AM PDT

Hello,I am trying to execute a stored proc like thisDeclare @cmd varchar(1000)Declare @Path1 varchar (1000)Declare @user_id varchar (50)Declare @password varchar (50)set @cmd = 'exec' + ' ' + stored_proc_name' + ', ' + @user_id +', ' + @password + ', ' + @Path1 exec master..xp_cmdshell @cmdERROR I am getting = 'exec' is not recognized as an internal or external command,Please help !!!!:unsure:Thanks,

high processor usage

Posted: 15 May 2013 10:15 PM PDT

Hi Any help on this one ? We have this morning had a processor on one of servers constantly being pegged at 100% for a few minutes then varying between 80% and 100% for the last few hours.It seems to have subsided now.I did check all the usual suspects during the time ie replcation, open transactions, blocking - all of which seemed fine. Any ideas on what i can check either now or in the future should this happen again - i think the boss was looking for answers but i couldnt give anyBTW we run our server in the cloud - was just wondering if we need to contact our 3rd party provider to see if any issues from their end ?

crystal reports 8

Posted: 16 May 2013 05:26 AM PDT

Hi,When was Crystal reports 8 released? I know it is not related to sql server but I had to answer this question for some SSRS relation question to my manager.Thanks.

Inserting records with a TIMESTAMP field for Archiving Table Data

Posted: 23 Apr 2013 06:14 AM PDT

Because I am paranoid (well, I call it 'realistic'), I like to backup my data before I delete anything and so I have quite a few examples of[code="sql"]INSERT INTO _Archive_TableSELECT * FROM Data_Table WITH (nolock)WHERE Last_Modified_Date < @Archive_Date[/code]Then, when I do the actual delete from Data_Table, I only delete the records that have a Unique Identifier already in the _Archive_Table.However, since a recent upgrade, this does not work on some tables because there is a field with the TIMESTAMP data type. I like using the asterisk, especially when there are 50 fields so specifying the fields is not acceptable.I have found the following solution;1. Get Query Analyser to generate the SQL script to Create the original table (Data_Table in my example above) and then edit the script as follows;a) Use _Archive_Table as the table name (use _Archive_Table_New as the name if an old _Archive_Table already exists)b) Replace the TIMESTAMP datatype with BINARY(8)c) Add a field to the end called Archive_Date with the type DATETIME2. Run the CREATE TABLE _Archive_Data SQL Script.3. If you have an existing archive table that you want to keep the data from, run the following SQL Scripts in order;[code="sql"]INSERT INTO _Archive_Table_NewSELECT *, GETDATE()FROM _Archive_Table WITH (nolock)TRUNCATE TABLE _Archive_TableDROP TABLE _Archive_TableSELECT *INTO _Archive_TableFROM _Archive_Table_New WITH (nolock)TRUNCATE TABLE _Archive_Table_NewDROP TABLE _Archive_Table_New[/code]4. Edit the archiving script to include a GETDATE() field after the asterisk;[code="sql"]INSERT INTO _Archive_TableSELECT *, GETDATE() FROM Data_Table WITH (nolock)WHERE Last_Modified_Date < @Archive_Date[/code]The TIMESTAMP field will implicitly convert to BINARY(8). The Archive_Date field is a useful way to find when a record was deleted and potentially allows you to delete the data from the _Archive_Table a year or two later once you are sure that no-one is going to want to recover it. I tend to use an initial underscore for backup tables in order to easily find them. If the backup table is a complete copy of an existing table, I usually just add the underscore as the prefix and the current date _YYYYMMDD as the suffix to the original table name. This makes it easy to know how old the backup table is for later pruning.A similar approach will work if the original Data_Table structure changes and you want to keep the old _Archive_Table active.

Delete Statement on Fact table (Transaction data table)

Posted: 16 May 2013 04:27 AM PDT

Hi All,I have a Transaction Data holding table, which has data around 10 to 20 Million rows.Now the problem, is that we need to delete and load the data occasionally based on Time Period of the Data.which will be around 1 million records.please need your help and suggestion, how to make this table feasible for this operating and does not effect the performance.All ideas are appreciated, please need help.

What is @Dummy ?

Posted: 16 May 2013 12:49 AM PDT

Hello all! I've been assigned to deconstruct and document a rather large Stored Procedure (SQL 2008). This procedure simply declared about a dozen of other procedures with I will need to anaylize and document. I'm coming into an environment where a handleful of other developers wrote, programmed, and left and not a damn thing was documented. So, I along with the help of someone else, are decontructing it all and documenting it. Anyway, the SP starts off like this:ALTER PROCEDURE [dbo].[The Name of this very SP](@Dummy varchar(20))asExec [SP #1] 1Exec [SP #2] 1...and so on.I have two questions. 1) What does [i]@Dummy [/i]do?2) What do the "1" indicate after firing off each SP?Thanks in advance for your help!!

polygon-geography

Posted: 15 May 2013 10:41 PM PDT

Hi all,i'm facing some issue in geography datatype when converting into polygonThis working fineDECLARE @polygon GEOGRAPHY SET @polygon=GEOGRAPHY::STPolyFromText('POLYGON ((-0.10200500506471144 51.517787451292115, -0.05943298358033644 51.49834206046075, -0.04981994647096144 51.52868178886899, -0.10200500506471144 51.517787451292115))', 4326)BUT... if the points are changed(2 and 3) its giving errorDECLARE @polygon GEOGRAPHY SET @polygon=GEOGRAPHY::STPolyFromText('POLYGON ((-0.10200500506471144 51.517787451292115, -0.04981994647096144 51.52868178886899, -0.05943298358033644 51.49834206046075, -0.10200500506471144 51.517787451292115))', 4326)this is giving error Msg 6522, Level 16, State 1, Line 3A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation. To create a larger than hemisphere geography instance, upgrade the version of SQL Server and change the database compatibility level to at least 110.Microsoft.SqlServer.Types.GLArgumentException: at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData& g, Double eccentricity, Boolean forceKatmai) at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive(Boolean forceKatmai) at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid) at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid).

How do I generate Conditional TRUNCATE statements ?

Posted: 16 May 2013 04:22 AM PDT

I regularly copy our Production database to our Test system and then need to delete most of the data, while keeping sufficient to allow meaningful testing.I generate a list of record keys that should be kept and then I have been using multiple[code="sql"]DELETE FROM <table>WHERE <key> NOT IN (SELECT <key> FROM <keep table>)[/code]statements to prune the various tables in the database.This is slow and generates logfiles which kill the system if I try to run too many of the deletes in a single execute.What I would like to do is effectively;For each <Table>[code="sql"]SELECT *INTO Temp_<Table>FROM <Table> WITH (nolock)WHERE <key> IN (SELECT <key> FROM <Keep Table>)TRUNCATE TABLE <Table>INSERT INTO <Table>SELECT *FROM Temp_<Table> WITH (nolock)[/code]Of course, this does not actually work.:-PFirstly, all of the tables have timestamp type fields which the asterisk cannot handle. However, since this is potentially using a dynamic list of the tables, it should be easy enough to dynamically list the fields as well, generating a SQL Statement that then gets executed.:ermm:Is there an existing script to copy multiple tables to another name ?The other issue is that most of the tables have triggers and constraints on them, including foreign keys. I am not sure;:ermm:What needs to be dropped or disabled to allow this process to run without any automatic integrity management processes running:ermm:How to drop or disable these processes and then re-enable them once update is complete so that the system will run the application normally.Any ideas or suggestions as to where to look for this information ?

Need Help

Posted: 16 May 2013 03:57 AM PDT

Okay, I have created an application where you can add what we call (Export Scripts) and when you run the script, it creates an Excel spreadsheet with the result set.I have already created about 26 scripts all running fine. We have multiple databases so we use a variable called say DBNAME and we have different amounts of parameters that a script may have. I'm having trouble trying to figure out why this (because others work) will not work. I guess it may be something to do with the IF Statements. [code="other"] SET QUOTED_IDENTIFIER OFF GO DECLARE @ValueType Varchar(5) DECLARE @Land_Bldg_Both Varchar(5) SET @ValueType = 'RES' SET @Land_Bldg_Both = 'BOTH' DECLARE @MY_STRING varchar(3000) SET @MY_STRING = 'DECLARE @ValueType varchar(5) DECLARE @Land_Bldg_Both varchar(5) SET @ValueType = ' + @ValueType + ' SET @Land_Bldg_Both = ' + @Land_Bldg_Both + ' IF @ValueType = "RES" BEGIN IF @Land_Bldg_Both = "BOTH" BEGIN SELECT COUNT(cMajor) As [Residential Count] FROM NDTAX.dbo.TXPAR WHERE nResidenceLot + nResidenceStruc >= 20000 AND nResidenceLot + nResidenceStruc <= 140000 AND cYear = 2012 END IF @Land_Bldg_Both = "LAND" BEGIN SELECT COUNT(cMajor) As [Res Land Count] FROM NDTAX.dbo.TXPAR WHERE nResidenceLot >= 20000 AND nResidenceLot <= 140000 AND cYear = 2012 END IF @Land_Bldg_Both = "BLDG" BEGIN SELECT COUNT(cMajor) As [Bldg Count] FROM NDTAX.dbo.TXPAR WHERE nResidenceStruc >= 20000 AND nResidenceStruc <= 140000 AND cYear = 2012 END END'EXEC(@MY_STRING);GO[/code]I have tried the code different ways, my main error I get isMsg 207, Level 16, State 1, Line 4Invalid column name 'RES'.Msg 207, Level 16, State 1, Line 5Invalid column name 'BOTH'.SQL Server 2008 R2

exporting hidden columns in SSRS R2 in Excel

Posted: 16 May 2013 03:34 AM PDT

Hi there,Have SSRS report with 2 hidden columns that must be a part of export into Excel.What do i do?thanks much for your help!

created statistics on columns because of an execution plan warning

Posted: 16 May 2013 03:50 AM PDT

yet, the execution plan is still complaining that the same columns i created the statistics on have no statistics on them!they are on an indexed view (clustered and unique, but these warning columns are not in the index) the plan is scanning.So even though it is the most expensive element in the execution plan, it didnt make sense to me to include them in the clustered index, but i can't seem to resolve the warning...what am i doing wrong?thanks a lotdrew

The multi-part identifier "Members.MemberID_" could not be bound.

Posted: 15 May 2013 09:20 PM PDT

HiWhen runing queries I keep geting the above error.Does anyone know how to resolve this pls?thanks

Connection Manager Properties

Posted: 16 May 2013 01:56 AM PDT

Hi,I need some assistance in adapting this connection string expression to retrieve the previous day rather than the current day- GETDATE -1@[Dest_Dir] + "\\" + (DT_WSTR,2)DatePart("DD", GetDate()) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,4)DatePart("YYYY", GetDate()), 2) + "FX.csv"Thanks in advance

same function from sql 2000 doesn't work on sql 2008 r2

Posted: 15 May 2013 11:53 PM PDT

We have very strange situation I hope someone can help, we copied a database from sql 2000 sp4 to sql 2008 R2 SP2 , runs with compatibility level 2000, it has table valued function basically returns text column, the statement has select * from mytable where len(convert(varchar(4000), TextCol)) > 25 Same function works in sql 2000, but doesn't work in sql 2008, simply no result just runs hours and hangs and we have to kill it. Any idea why? And what I need to do in sql 2008, BTW: this new server has more RAM and CPU than old server, old server on 2003, but this sql on win 2008 r2Thank you

Antivirus on a SQL Server VM

Posted: 15 May 2013 11:57 PM PDT

hi guys, my vm admin wants to run AV at the host and not at the guest level. what do i need to ask the vm admin to make sure he is adhering to sql server best practices?cheers

Check each cell in one table and update another table

Posted: 15 May 2013 11:59 PM PDT

There are two tables as below: COLOR and ISCOLOR.I need a loop to check cell by cell in table COLOR.If ID has any one color, update table ISCOLOR as "YES" otherwise is "NO"COLORID----BLUE---RED---YELLOW111---X-------------------- 222-------------X---------X333---X-------------------- 444-------------------------ISCOLORID ISCOLOR111 YES222 YES333 YES444 NO

SQL2008R2 Connection reset by peer: socket write error

Posted: 16 May 2013 12:35 AM PDT

New application is getting this error:ContentManager.log-2013-05-15:ClientAbortException: java.net.SocketException: Connection reset by peer: socket write errorContentManager.log-2013-05-15:Caused by: java.net.SocketException: Connection reset by peer: socket write errorWe are running a Win2008R2/SQL2008R2 SP2 active/passive cluster. It hosts 45 other databases that are running fine. This is a relatively new app. I am just starting to look into this issue. Has anyone experienced this or have any tips on resolving it?

sql copying column attributes of one table to another table

Posted: 15 May 2013 09:59 PM PDT

please find the attached file...need a query for the table design i have mentioned here...

Missing Statistics

Posted: 15 May 2013 10:46 PM PDT

Good Day.We are running SQL Server 2008R2 ENT and kept auto update stats and auto create stats enabled. We identified a performance issue and added the required index(es) in full . However performance did not improve. I then ran the query through the Database Engine Tuner which identified some statistics were missing . What I don't understand is that the indexes were created , but the required statistics were not .

single quote issue

Posted: 15 May 2013 10:05 PM PDT

Hi all, I have to update a column where I am giving query which isUpdate Table Set col = '[189] IS NOT NULL and [189] <> ''' Where colid= 198but its giving output:- [189] IS NOT NULL and [189] <> ' But I want output :- [189] IS NOT NULL and [189] <> '' actually I am not getting single quotes at last..Please let me know how to give update query?Thanks in advance...

sql copying column attributes of one table to another table

Posted: 15 May 2013 09:19 PM PDT

i have 2 tables one call it as dbo.t1 and another dbo.t2t1:P1 S1 B1 H1 S2 J1______________________________ b1 s1 b1 s1 b1 s1 ----> these are the merged columns (like: H1 has b1 & s1, S2 has b1 & s1, J1 has b1 & s1)

sql copying column attributes of one table to another table

Posted: 15 May 2013 09:48 PM PDT

i have 2 tables one call it as dbo.t1 and another dbo.t2t1:P1 S1 B1 H1 S2 J1______________________________ b1 s1 b1 s1 b1 s1 ----these are the merged columns (like: H1 has b1 & s1, S2 has b1 & s1, ----------------------------------- J1 has b1 & s1)pj1 sk1 bu1 2 4 0 2 1 3 --- these r d values in d table t2:P1 S1 B1 Fun Val Loc ___________________________________pj1 sk1 bu1 H1 2 b1pj1 sk1 bu1 H1 4 s1--- --- --- S2 0 b1--- --- --- S2 2 s1--- --- --- J1 1 b1--- --- --- J2 3 s1t1 is the imported table from excel and t2 is the disired output table i need the column names of t1 as values in t2.. please help me out

SQL AGENT JOBS ARE FAILING

Posted: 15 May 2013 08:57 PM PDT

Hi Team,I have problems with sql agent. Getting below error.SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but serviceaccount is not a member of the SysAdmin role)The agent use to work properly with same account. suddenly no job is running.I change it to other sql service account. Got the below error. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]Changed the service account to Local it works. But I can not use it in prod.OS: Windows 2003 SQL: 2008 R2 Enterprise.Please suggest.

Trigger to fire when inserting rows in tbale

Posted: 15 May 2013 08:25 PM PDT

Hi ALlI am trying to fire a trigger when I am trying to excute the statement : SELECT * INTO Table_BACKUP from TABLEDefined a AFTER INSERT trigger on TABLE_BACKUP but its not doing anything?CAn you please help?Meghna
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog