Tuesday, October 8, 2013

[SQL 2012] I dindn't understand how this query works?

[SQL 2012] I dindn't understand how this query works?

I dindn't understand how this query works?

Posted: 08 Oct 2013 02:47 AM PDT

select * from empolyee where name > 'ax'ID NAME SALARY DEPT PREVIOUS_SAL1 amol singh 2000 10 2200 2 bill gates 3000 30 3300 3 hasan mir 7000 20 7700 4 robin 9000 50 9600 how this command compares?

SSIS SharePointList Adapters with Visual Studio/SQL Server 2012 - Destination Connector Error

Posted: 08 Oct 2013 03:06 AM PDT

Has anyone successfully used the newest SharePointList Adapters for 2012 at [url=https://sqlsrvintegrationsrv.codeplex.com/downloads/get/607593]SharePointListAdaptersSetup-Beta-2012-01-28.msi[/url] , specifically the Destination connection? I have a working 2008 package, and there, when you edit the SharePointList Destination and click on the Refresh button, the columns on the SharePoint List are imported so that you can then map whichever columns you may want to update.In this more recent SharePoint Adapter, however, this produces the error shown in the attached screen snippet.And, since I cannot get the columns, I am unable to update the two that my package should write back to SharePoint.Incidentally, this beta version of the SharePointList adapters [u]only installs a 32-bit [/u]version, and not a 64-bit, so you will find them only in the Program Files (x86) tree.Any help, comments, suggestions, etc. are greatly appreciated - thanks!Larry

trying to create a table - unpivot

Posted: 08 Oct 2013 01:18 AM PDT

Hi :-),I am having problems understand the function unpivot on sql.I have a query: [code="sql"]select RecordDay, ClickCount into #myclicks from dbo.clickswhere RecordDay >= getdate()-38[/code]that the outcome will be (with the hours, minutes and seconds)RecordDay ClickCount8/31/2013 18/31/2013 18/31/2013 68/31/2013 18/31/2013 18/31/2013 18/31/2013 18/31/2013 18/31/2013 18/31/2013 18/31/2013 18/31/2013 18/31/2013 18/31/2013 18/31/2013 18/31/2013 2I want to build from my temp table something that will look like this, where the measure will be the sum of ClickCountRecordDay 8/31/2013 9/7/2013 9/14/2013 9/21/20130 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 I know I will need to define on the columns[code="sql"] datepart(weekday,getdate()) = datepart(weekday,datecreated) [/code] in order to get the same day of the week, and I imagine I will need to do a dateadd to group it by minutes on the rows.But I am getting extremely lost here.Help please :w00t:

split performance

Posted: 07 Oct 2013 10:46 PM PDT

Hi all i have tried below two functions to split string,but both are working slow:[b]Function 1 using substring and while loop[/b]ALTER function [dbo].[uf_split]( @String nvarchar (MAX), @Delimiter nvarchar (10) )returns @ValueTable table ([Value] nvarchar(MAX))begin declare @NextString nvarchar(MAX) declare @Pos int declare @NextPos int declare @CommaCheck nvarchar(1) --Initialize set @NextString = '' set @CommaCheck = right(@String,1) --Check for trailing Comma, if not exists, INSERT --if (@CommaCheck <> @Delimiter ) set @String = @String + @Delimiter --Get position of first Comma set @Pos = charindex(@Delimiter,@String) set @NextPos = 1 --Loop while there is still a comma in the String of levels while (@pos <> 0) begin set @NextString = substring(@String,1,@Pos - 1) insert into @ValueTable ( [Value]) Values (@NextString) set @String = substring(@String,@pos +1,len(@String)) set @NextPos = @Pos set @pos = charindex(@Delimiter,@String) end returnend[b]FUNCTION 2 with XML:[/b]ALTER function [dbo].[split] ( @qustion_option_id varchar(max), @separator char ) RETURNS @list TABLE (item varchar(max))BEGIN DECLARE @xml XML SELECT @xml = CAST ( '<A>' + REPLACE ( @qustion_option_id, @separator, '</A><A>') + '</A>' AS XML) INSERT INTO @list SELECT t.value('.', 'varchar(max)') FROM @xml.nodes('/A') AS xml(t) RETURN ENDany other way to do the split function efficiently?

Migrating SQL Jobs from SQL 2005 to SQL 2012

Posted: 08 Oct 2013 01:17 AM PDT

This week we migrated from SQL Server 2005 to SQL Server 2012. I just looked in SSMS 2012 and found none of our SQL Jobs there. How do I migrate them? Do I generate their SQL scripts and do it that way?

dbWarden HealthReport won't run on SQL 2012 standard

Posted: 07 Oct 2013 05:35 PM PDT

Hi everybody,i am usins dbWarden on 8 sql-servers. Everything runs fine, only my SQL 2012 standard won't execute the heath report.Error from job-history:Null value is elimitnated by an aggregate or other SET ......If i try to execute the SP direct, i get the following error:Warning: Null value is eliminated by an aggregate or other SET operation.Msg 213, Level 16, State 7, Procedure sp_replmonitorhelppublication, Line 320Column name or number of supplied values does not match table definition.Has anybody an idea? I get emails when there are blockings etc.., only the healthreport won't run.SQL 2012 Standard SP1 64bitWindows Cluster 2008It is our distribution-cluster, nothing else.thanksregards,Andreas

SQL Tools 2012

Posted: 07 Oct 2013 06:43 AM PDT

Hello EveryoneI have had some SSIS packages that I converted to SQL 2012 using the SQL tools, what was once BIDS. I would like to edit one, but none of the tools are active. Has anyone else experienced this?Thank you in advance for your assistance, comments and suggestions.

Setting database OFFLINE and ONLINE does not "appear" to clear the cache.

Posted: 07 Oct 2013 05:48 AM PDT

Per BOL: [url]http://msdn.microsoft.com/en-us/library/bb522682.aspx[/url][quote]The plan cache for the instance of SQL Server is cleared by setting one of the following options[/quote] Which includes OFFLINE and ONLINE. I don't doubt it, but we like to test things. So far, setting the database offline, even for 10 minutes, and bringing it back online did not appear to clear the cache as evidenced by no differences before and after in sys.dm_exec_query_stats, sys.dm_exec_cached_plans, and:[code="sql"]SELECT OBJECT_NAME(p.object_id) AS [ObjectName] , p.object_id , p.index_id , COUNT(*) / 128 AS [buffer size(MB)] , COUNT(*) AS [buffer_count]FROM sys.allocation_units AS a INNER JOIN sys.dm_os_buffer_descriptors AS b ON a.allocation_unit_id = b.allocation_unit_id INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_idGROUP BY p.object_id , p.index_idORDER BY buffer_count DESC;[/code]Any thoughts on what we are doing wrong to test this? We tested on a 2008R2 and 2012 instance.

Additional Log Shipping secondary server - how to

Posted: 07 Oct 2013 07:15 AM PDT

I have an existing 2008R2 primary server shipping logs from ODS to a third server. I want to add my Data Warehouse server to the existing log shipping scheme as a secondary server. I know this is likely an idiotic question to those who have done it a bazillion times, but how can this be done?:ermm:
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog