Sunday, March 10, 2013

[SQL Server 2008 issues] Assign values: conditional case when

[SQL Server 2008 issues] Assign values: conditional case when

Assign values: conditional case when

Posted: 09 Mar 2013 01:39 PM PST

My data are arranged like:IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1--===== Create the test table with CREATE TABLE #Table1 ( Name1 Char(5),Name2 Char(5),Total int)INSERT INTO #Table1 (Name1, Name2,Total) SELECT 'X1','Y1',8UNION ALLSELECT 'X2','Y2',38UNION ALLSELECT 'X3','Y3',2UNION ALLSELECT 'X4','Y4',29UNION ALL SELECT 'X4','Y5',18UNION ALL SELECT 'X4','Y6',7UNION ALL SELECT 'X4','Y7',10UNION ALL SELECT 'X5','Y8',4UNION ALL SELECT 'X5','Y9',80UNION ALL SELECT 'X5','Y10',32UNION ALL SELECT 'X5','Y11',93UNION ALL SELECT 'X6','Y12',54UNION ALL SELECT 'X6','Y13',22UNION ALL SELECT 'X6','Y14',68UNION ALL SELECT 'X7','Y15',6UNION ALL SELECT 'X7','Y16',9UNION ALL SELECT 'X7','Y17',100UNION ALL SELECT 'X8','Y18',3Select * from #Table1 I am trying to assing values (0 or 1) in columns D thru G based on the following logic. THe first wo case when conditions work. The last two are not working and assigning wrong values. The desired outcome for example for Name1=X7 is:Name1 Name2 Total Name1>100&Name2<25 Name1<100&Name2<25 Max of Name1>100&Name2<25 Max of Name1<100&Name2<25 X7 Y15 6 1 0 1 0 X7 Y16 9 1 0 0 0 X7 Y17 100 0 0 0 0 select name1, name2,total, case when (sum(total) over (partition by name1) >100) and total <25 Then 1else 0end as [Name1>100&Name2<25], --Workscase when (sum(total) over (partition by name1) <100) and total <25 Then 1else 0end as [Name1<100&Name2<25], --Workscase when (sum(total) over (partition by name1) > 100) and (min(total) over (partition by name1) <25) Then 1else 0end as [Max of Name1>100&Name2<25], ---[b]not working[/b]case when (sum(total) over (partition by name1) <100) and (min(total)over (partition by name1) <25) Then 1else 0end as [Max of Name1<100&Name2<25] --[b]not working[/b]from #Table1group by name1,total,name2order by name1, name2, total Any suggesitons? Thank you for your help, Helal

Sharepoint SQL Server

Posted: 09 Mar 2013 05:24 AM PST

http://technet.microsoft.com/en-us/library/cc298801.aspx•To ensure optimal performance, we strongly recommend that you set max degree of parallelism (MAXDOP) to 1 SQL Server instances that host SharePoint Server 2010 databases? can someone explain why sharepoint team put this document with the above line? Sharepoint surpresses all parallelism?

Backup issue through maintenance plan

Posted: 09 Mar 2013 06:14 AM PST

i am getting the below error while taking the backup via maintence plan.. i am getting an error only for one particular database and rest been completed successfully.Please advice Executed as user: XXXXXXXXX\XXX$. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 4:07:42 PM Progress: 2013-03-09 16:07:43.12 Source: {136FEB27-FA79-435D-AE44-BC65CE8B1D5A} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2013-03-09 16:07:43.31 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'F:\Backups\\...".: 100% complete End Progress Error: 2013-03-09 16:07:43.33 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP DATABASE [Logger ] TO DISK = N'F:\Backu..." failed with the following error: "Cannot open backup device 'F:\Backups\\Logger \Logger _backup_2013_03_09_160743_3082964.bak'. Operating system error 3(The system cannot find the path specified.). BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:07:42 PM Finished: 4:07:43 PM Elapsed: 0.687 seconds. The package execution failed. The step failed.

Issue with a Connection to a Named Instance in Home Lab

Posted: 05 Mar 2013 10:15 AM PST

I installed a single named instance of SQL Server in my lab, and I am having trouble connecting from a remote machine, although local connections to the instance work fine. The server hosting SQL Server is a brand new VMware Workstation virtual machine that does not have any other SQL Server versions or instances installed.On the database server, I used SQL Server Configuration Manager to change the TCP port to static 1433.On the database server, I opened the firewall with the following commands:[code="plain"]netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAINnetsh advfirewall firewall add rule name = SQLPort_1434 dir = out protocol = UDP action = allow localport = 1434[/code]PortQry.exe shows 1433 open when I run it from the remote machine that I am using to connect to SQL Server.Scenario 1: Surprisingly, this command (which does not specify the instance name) works on the remote machine:[code="plain"]sqlcmd.exe -S sql_netbios_name -U sa -P password -Q "select getdate()"[/code]Scenario 2: This command (which specifies the instance name) gets an error error (which I am attempting to fix with this web posting) on the remote machine:[code="plain"]sqlcmd.exe -S sql_netbios_name\instance_name -U sa -P password -Q "select getdate()"HResult 0xFFFFFFFF, Level 16, State 1SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.[/code]Scenario 3: But this command (which specifies the instance name *AND* the port number, with the connection string encapsulated in double quotes) works on the remote machine:[code="plain"]sqlcmd.exe -S "sql_netbios_name\instance_name,1433" -U sa -P password -Q "select getdate()"[/code]Any ideas why Scenario 2 fails? This has to be simple. I just don't see it.

SSIS Error Code DTS_E_PRODUCTLEVELTOLOW

Posted: 08 Mar 2013 07:11 PM PST

Hi I am executing a SSIS packag through command prompt using command DTEXEC /F .The package gets executed but shoots an error "SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The component cannot run on installed (64-bit) of Integration Services. It requires Standard Edition (64-bit) or higher. "The package gets executed successfully in BIDS without any error. I execute this package in the same server and its 64 Bit. Any Suggestions ?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog