Friday, March 15, 2013

[SQL Server 2008 issues] How to Pivot table?

[SQL Server 2008 issues] How to Pivot table?

How to Pivot table?

Posted: 14 Mar 2013 06:58 PM PDT

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[sales_report]( [salesid] [bigint] NOT NULL, [productName] [varchar](100) NULL, [Qty] [bigint] NULL, [Date] [date] NULL, CONSTRAINT [PK_sales_report] PRIMARY KEY CLUSTERED ( [salesid] 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 ANSI_PADDING OFFGOINSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (1, N'dettol', 6, CAST(0xDD360B00 AS Date))INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (2, N'hamam', 10, CAST(0xDE360B00 AS Date))INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (3, N'sweet', 10, CAST(0xDF360B00 AS Date))INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (4, N'test', 23, CAST(0xDC360B00 AS Date))INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (5, N'boost', 10, CAST(0xDF360B00 AS Date))INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (6, N'horlicks', 2, CAST(0xDD360B00 AS Date))INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (7, N'shampo', 5, CAST(0xDE360B00 AS Date))here i want ProductName14 15 16 17 Total---------------------------------dettol 0 6 0 0 6hamam 0 0 10 0 10boost 0 0 0 10 10horlicks 0 2 0 0 2shampo 0 0 5 0 5sweet 0 0 0 10 10test 23 0 0 0 23

date format 7/31/2013

Posted: 04 Mar 2013 03:52 AM PST

Hi friends,Im new to TSql -I have requirement to display date as 7/31/2013 but currently I'm getting it from DB as 2013-03-01 18:40:24.000.any help on this will he highly appreciated,ThanksDhananjay

how to split the days between two month

Posted: 13 Mar 2013 11:19 PM PDT

hi i am developing a web application dot net..i am struck with the sql server query...if the person is taking a leave from 25/3/2013 to 3/02/2013,i want to split the 6days in month and 3 days in febrarury,,,can anyone tell me pls

can we install 2008 R2 integration services and 2012 integration services on the same server

Posted: 14 Mar 2013 09:59 AM PDT

Hi DBA'SI had 2012 integration services installed on a server. i am trying to connect to Oracle but it is failing. I worked with Same SSIS Pkg in 2008 r2 where i can connect to oracle and import data. So i am planning to install sql server 2008 r2 ssis on the same box where 2012 is installed. My Database engine will be 2012. can i do this ?please adviseThanks a alot in adavnce

table Joins and overby clause

Posted: 14 Mar 2013 05:25 PM PDT

Hi,Please find below query,tab1 - Col1 int, Col2 varchar2(30)tab2 - Col1 int, col2 varchar2(30) , col3 datetimeQuery 1:select a.col1,a.col2,b.col3 from tab1 afull outer join tab2 bon a.col1=b.col1and b.col3 > '19000101'Query 2:select a.col1,a.col2,b.col3from tab1 afull outer join (select col1,col2,col3, row_number() over(partition by col1,col3 order by col3) as rownumber from tab2) bon a.col1=b.col1and b.col3 > '19000101'Please see the above two queries, Issue : I am getting row difference between this two queries. for example while executing first query , getting output 3 rows.while executing second query , getting output 5 rows.Can you please explain me?

Query to find cheapest supplier when multiple suppliers?

Posted: 14 Mar 2013 06:38 AM PDT

Hi I have the following table (created from a view):BaseSupplierID__SupplierID___ProductID__Price3_______________3___________1________113_______________4___________1________113_______________5___________1________153_______________6___________1________104_______________3___________2________164_______________4___________2________105_______________5___________3________165_______________8___________3________145_______________9___________3________10Basically each product can be supplied from multiple supplies yet there is a base supplier (which in theory should be the cheapest). I need to create a query that would show me the products where the base supplier was not the cheapest for their products? Any pointers?? Thanks

DB restored from customer -> Database diagram vanishes

Posted: 14 Mar 2013 02:55 AM PDT

Hello,I use SS2008R2 Dev.Edition and altered a DB from my customer. Then I send him my DB-backup, the customer synchronizes the db-schema with SQLCompare. So far, so good. All works. Now the customers production DB is sent back to me and I restored this one. But the Database diagram I created has vanished.:w00t:I suppose that has to do with user rights.At my PC I always login as user with sysadmin rights/windows security. The customer uses windows security AND some SQL User logins and I think thats the issue. Whats the right way to assure that any DB-backup I receive from my customer also returns the Database diagram?thx

Slow Cascade Stored Procedure & Hang

Posted: 14 Mar 2013 06:11 AM PDT

Table Image : http://tinypic.com/r/1075g6v/6So I have this query which searches id by id and calculates the cost accordingly. But is very slow and I would like to understand how I could convert it into a set based operation.So depending on our condition we calculate our modeled costs differently.When user updates a driver we can run a update on the entire driver column based on where it has changed.But when it comes to calculating the modeled cost. We do it row by row as the fixed cost differs and then divide by months. I have pasted the code below. Is there still a way to this by set based operation ?First we update the drivers in the same table depending on what value has changed using an update and then update the modeled cost row by row (which is really slow)Code :SELECT @rowCounter = 1, @totalrows = @@ROWCOUNTWHILE @rowCounter <= @totalrows BEGIN SELECT @currentId = tempId FROM @temp WHERE row = @rowCounter SELECT @newModeledCost = case when not exists (select 1 from dbo.DIMSTD_SCENARIO where SCENARIO0_Name = SCENARIO and SCENARIO2_Name = 'Model') then ISNULL(DriverValue1,0)*ISNULL(DriverValue2,0)*ISNULL(UnitA,0)*ISNULL(UnitB,0)+ISNULL(FixedCost,0) -- normal allocation for all scenarios else (ISNULL(unita,0) * (ISNULL(DriverValue1,0)/ISNULL(NULLIF(DriverValue2,0),1))* ISNULL(UnitB,0))+ISNULL(FixedCost,0) --(ISNULL(unita,0) * (ISNULL(DriverValue1,0)/ISNULL(DriverValue2,0))*ISNULL(UnitB,0))+ISNULL(FixedCost,0) -- allocation for model scenarios end , @oldModeledCost = ISNULL(ModeledCost,0), @newOct = (ISNULL(@newModeledCost,0) * (ISNULL(Oct, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newNov = (ISNULL(@newModeledCost,0) * (ISNULL(Nov, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newDec = (ISNULL(@newModeledCost,0) * (ISNULL(Dec, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newJan = (ISNULL(@newModeledCost,0) * (ISNULL(Jan, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newFeb = (ISNULL(@newModeledCost,0) * (ISNULL(Feb, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newMar = (ISNULL(@newModeledCost,0) * (ISNULL(Mar, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newApr = (ISNULL(@newModeledCost,0) * (ISNULL(Apr, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newMay = (ISNULL(@newModeledCost,0) * (ISNULL(May, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newJun = (ISNULL(@newModeledCost,0) * (ISNULL(Jun, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newJul = (ISNULL(@newModeledCost,0) * (ISNULL(Jul, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newAug = (ISNULL(@newModeledCost,0) * (ISNULL(Aug, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newSep = (ISNULL(@newModeledCost,0) * (ISNULL(Sep, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))) FROM dbo.TBF_BUDGETExpenses WHERE BudgetId = @currentId --and not exists (select 1 from dbo.DIMSTD_SCENARIO where SCENARIO0_Name = SCENARIO and SCENARIO2_Name = 'Model') UPDATE dbo.TBF_BUDGETExpenses SET ModeledCost = @newModeledCost, Oct = @newOct, Nov = @newNov, Dec = @newDec, Jan = @newJan, Feb = @newFeb, Mar = @newMar, Apr = @newApr, May = @newMay, Jun = @newJun, Jul = @newJul, Aug = @newAug, Sep = @newSep, Username = 'Cascade', lastmodified = getdate() WHERE BudgetId = @currentId AND @oldModeledCost <> 0 Print 'Record Update ' + CAST(@currentId AS VARCHAR(15)) SET @rowCounter = @rowCounter + 1END

Test connection succesfull with Import Export Wizard but fails in BIDS 2012 - Oracle to SQL

Posted: 14 Mar 2013 06:17 AM PDT

Hello All,Test connection succesfull with Import Export Wizard but fails in SSIS Pkg in BIDS 2012 Can you please help me in debugging this issue. Attached screenshot of error in SQL 2012[img]http://www.sqlservercentral.com/Forums/Attachment13333.aspx[/img]

Distribution cleanup failed

Posted: 14 Mar 2013 07:05 AM PDT

What happens if the Distribution Cleanup Maintenance job failes before the next replication cycle? Does it push duplicates over? If it succeeds after the next replication cycle does it clean up everything that is missed from it's last failure? Is this really anything to worry about? It's not a constant failure but it fails every now and then.

Where is my execution plan?

Posted: 14 Mar 2013 02:27 AM PDT

I am trying to get the execution plan for a very slow procedure that I have, and I can't. I am able to list the child queries and IO utilization, but my DMV can't display the graphical plan. Initially, I though was memory pressure on my PRO environment, but this is also happening on my test box, which it is totally isolated.Here's my DMV, taken from "SQL Server DMVs In Action" book, wonderful book, by the way:[code="sql"]SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT TOP 100[Total IO] = (qs.total_logical_reads + qs.total_logical_writes), [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count, qs.execution_count, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2ELSE qs.statement_end_offsetEND - qs.statement_start_offset)/2) + 1) AS [Individual Query], qt.text AS [Parent Query], DB_NAME(qt.dbid) AS DatabaseName, qp.query_planFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpWHERE DB_NAME(qt.dbid) ='db_name' AND qt.text LIKE '%sproc_name%'ORDER BY [Total IO] DESC, qs.execution_count DESC[/code]The query_plan column displays NULL.The store procedure uses temporary tables, not variable tables, but I do not think that's relevant anyway.

Need Help with DateDiff function

Posted: 14 Mar 2013 07:43 AM PDT

Hi Friends,I'm using DateDiff function -my requirement is -1) "SmartLaborII Job Posting End Date"" is NOT < ""SmartLaborII Work Order End Date"and my code is ---------not(datediff(day,[WorkForce_JobPosting].[Job Posting End Date_JP] ,[WorkForce_WorkOrder].[Work Order End Date_WO]) > 0)--------2) "SmartLaborII Work Order End Date"" is NOT greater than ""SmartLaborII Previous Work Order End Date"my code is -/// datediff(day,[WorkForce_WorkOrder].[Work Order End Date_WO] ,[WorkForce_WorkOrder].[Previous Work Order End Date_WO] ) >= 0//Need help from experts to verify if my code for the 2 requirements is correct ?Kind RegardsDhananjay

adding a new not null bit column with default 0

Posted: 13 Mar 2013 11:32 PM PDT

I need to add a new bit column to a table with around 6 million rows.I know the table will be locked during this operation. My question is: Is there a quick way of doing this without copying the data into a new table ,re-applying indexes extra and then doing a table name change?Thanks

How to get Below T-SQL qury Output..?

Posted: 08 Mar 2013 05:23 PM PST

[size="3"][font="Courier New"]Hi All,This is the query I have writtenDECLARE @FromDate DATETIMEDECLARE @EndDate DATETIMESET @FromDate = '2013-01-01 00:00:00.000'SET @EndDate = '2013-02-13 00:00:00.000'SELECT year(sd.FKDAT) As YEARWISE_DATA, sg.KUNNR As PARTY, sg.NAME1 As NAME, SUM(sd.FKIMG) As QUANTITY, SUM(sd.NETWR) As VALUE_IN_FC, SUM(sd.NTGEW) As WEIGHTFROM Sales_group sg WITH(NOLOCK)INNER JOIN SALES_DATA sd WITH(NOLOCK) ON sg.KUNNR = sd.KUNAGWHERE sd.FKDAT >= @FromDate AND sd.FKDAT <= @EndDateGROUP By sd.FKDAT, sg.KUNNR, sg.NAME1ORDER By 1, sg.KUNNR ASC[b]Below is the output i am getting,[/b]2013 HA010 ADK 360.000 36988.20 9206.4342013 HA010 ADK 205.000 31363.80 9299.8482013 HA018 AGRI 295.000 42646.25 12578.1492013 HA018 AGRI 119.000 29587.75 8816.1122013 HA018 AGRI 21.000 10289.65 2882.4882013 HA018 AGRI 249.000 57764.20 17605.415[b]Required Output I want[/b]2013 HA010 ADK 565.000 68352.00 18506.312013 HA018 AGRI 684.000 140287.85 41882.164Thanks & Regards,Bhushan[/font][/size]

Capture Data Change

Posted: 14 Mar 2013 05:18 AM PDT

I'm trying to use CDC for auditing certain tables, the problem I find is it doesn't capture the user.I have tried using Default Constraints SUSNAME_USER and adding an extra column to the cdc table. But it puts sa into the field each time is there another way to do this using CDC. A part from changing the user table to capture this. I can't see another option.

Internal Query Processor Error

Posted: 14 Mar 2013 03:37 AM PDT

Any idea on this...Msg 8624, Level 16, State 21, Line 1Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.we are on SQL Server 2008 R2.Thanks

Import Access 2010 db to SQL Server

Posted: 14 Mar 2013 04:32 AM PDT

I am trying to import a .accdb file into SQL Server but do not see any option in SQL Server Import Wizard. There is an option of importing a .mdb file but not .accdb file.Does anyone know how to import such a file to SQL 2008?ThanksKK

fetch tables most often queried

Posted: 26 Feb 2013 12:24 PM PST

Is there a way to determine which tables of a database are being queried most often, sorted in descending order?

encryptbypassphrase and encrypting by using symmetric key

Posted: 14 Mar 2013 12:45 AM PDT

I wanted to know some details about sql server encryptiona) What is the exact difference between encryptbypassphrase and encrypting using symmetric key which is protected by a password(while creating symmetric key it is encypted by using password not master key or certificate)b)Which one is more secure in those two?c) If i use the second method mentioned in question a) i.e encrypting using symmetric key which is protected by a password,then in any case after about a year if i want to change password then how it can be easily done?

Websockets?

Posted: 13 Mar 2013 11:54 PM PDT

Anyone has experience with SQLServer and websockets? I need my Stored Procedure to be able to send a message to clients through nodeJS.Thanks,

SQLSERVER UNINSTALL PREVIOUS & INSTALL EXPRESS EDITION

Posted: 14 Mar 2013 04:06 AM PDT

Hi All,My 2008 r2 eval copy has expired. I am wanting to uninstall it and install the express version. I've already uninstalled some SQL Server 2008 programs and deleted some directories. When I try to uninstall the Microsoft SQL Server 2008 R2 setup(English) program, this is what I get: Warning 26003: SQL Server 2008 R2 Setup Support Files cannot be uninstalled because the following products are installed:SQL Server 2008 R2 Common FilesSQL Server 2008 R2 Database Engine Shared After clicking OK, I get this:"The feature you are trying to use is on a network resource that is unavailable""Click OK to try again, or enter an alternate path to a folder containing the installation package 'sqlsupport.msi' in the box below (c:\97c8055e113829e172976f0a9df07e57\1033_ENU_LP\x86\setup\sqlsupport_msi\)Now the other problem is I can't install the 2008 R2 Express version. It goes through the a few things then just sits there.How hosed am I? Any help would be appreciated.

SQL Server for Content Censorship

Posted: 05 Mar 2013 06:08 PM PST

Hi Champs,We have little strange requirement in a case of SharePoint where backend is SQL. Users in social features of SharePoint shout not be able use offensive words in comments, newsfeeds, conversations, reply etc, The system must not allow "offence words". Somehow it is not possible SharePoint automatically. We can do it only manually through content approval process. But that approach is not practical approach for the larger environment. What i was thinking if something can be done from the SQL Side as SharePoint stores Data in SQL. I was thinking something at runtime detection of Offensive words against a Master Table (Offensive word Dictionary) or While user post (Saved) also fine so SQL can compare and if found offensive Data it should tgriger alert for Admin. Please champs let me know if this approach is possible some how. Thanks.Regards,Inder.

Upgrading SQL Server

Posted: 14 Mar 2013 03:41 AM PDT

Hi DBA We are upgrading SQL Server 2005 Box to 2012(Side by Side), for planning new server hardware requirements. we want some useful information from current 2005 box. Can you please guys let me know what all information should we consider from current box(like activity, pressure,)while creating a new box.Please let me know if you have any query which brings out all useful informationThanks a lot in adavnce!

a transaction is beeing rolled back for several days

Posted: 13 Mar 2013 08:55 PM PDT

The Situation is as follows:1. server Sql 2008 r2 (10.50.1600)2. data base Competablitiy Level 2000 (80)a job was stopped and the process is killed The transaction is beeing rolled back for serveral days nowThe amount of processing made by the job does not justify a rollback of more than an hourThe Table in which the data is beeing rolled back is expendable and can be droped or truncated.Kill The spid of the rolled back transaction displays this message -:SPID 173: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.:hehe: how can I drop the table .:hehe:

Encryption doubts

Posted: 11 Mar 2013 07:21 PM PDT

I want to encrypt my database. So which method is simple and the best. If i use symmetric key then does i encrypt it by using database master key or password? Which is safe here?

SQL migration - Database 90 to 100

Posted: 13 Mar 2013 10:05 PM PDT

After restoring 2005 db on SQL 2008 Do we have an list of impact when we change from 90 to 100

Clearing/deleting/resetting asynchronous file target - Extended events

Posted: 11 Mar 2013 11:55 PM PDT

Does anyone have any suggestions on how to clear/delete/reset the asynchronous file target files for extended events?
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog