Thursday, July 4, 2013

[SQL Server 2008 issues] Find out Percentage Free Virtual Memory

[SQL Server 2008 issues] Find out Percentage Free Virtual Memory

Find out Percentage Free Virtual Memory

Posted: 03 Jul 2013 06:23 PM PDT

Hi,How do i find out Free Virtual memory,Process CPU Utilization,Replication Available,Lock Timeout Rateby sql query

Turn rows into columns

Posted: 03 Jul 2013 06:12 PM PDT

Hi,I have the following table & (sample) associated data set:[code="sql"]CREATE TABLE TableHeaders(Period_Year int,Period_Month varchar(255),ColumnID int,ColumnLabel varchar(255));---/* sample data setPeriod_Year Period_Month ColumnID ColumnLabel2012 January 1 Jan-122012 January 2 RAF2012 January 3 STP2012 January 4 PY2012 January 5 Jan-122012 January 6 RAF2012 January 7 STP*/[/code]I want to select from the table[code="sql"]SELECT ColumnLabelWHERE Period_Year = @pv_SelectedYearAND Period_Month = @pv_SelectedMonthORDER BY ColumnID[/code]but instead of getting a data set giving a number of rows (from example it would be the list of columnLabels), I would like to get back one row with as many columns as they are rows.From example, I would like my data set to be:[code="other"]Col1 col2 col3 col4 col5 col6 col7Jan-12 RAF STP PY Jan-12 RAF STP[/code](column names will not be used so can be anything)I hope the above makes sense - annoyingly, I can test things out out as I am "sqlserver-less" just now :ermm:Cheers,bleroy

Find out free physical memory

Posted: 03 Jul 2013 04:58 PM PDT

HiHow do i find out free Physical Memory using sqlserver query

Queries that cause full scan table

Posted: 13 Jun 2013 05:35 PM PDT

Hi everybody , I would like to retrieve queries that cause full scan table in sql server.Someone can help me please?? :ermm:

Can this be coded better?

Posted: 03 Jul 2013 02:45 PM PDT

My goal is to get a dataset of orders with their overall statuses.The order overall status is a computed column based of their detail line statuses.I have 2 options:1. Create a view of distinct orders and their overall statuses via a function call2. Create a computed column on the order table ( non persisted) because the order status changes over time.This computed column will still be the result of a function call.I am choosing option 1. Attached is sample data and function code.I am looking for suggestions and ideas on how to make the function code better. So far I only code a portion of the function. There are other rules to implement in the function so I am trying to find ways to make it as readable, short and simple. Eventually my function will return an INT. I just return varchar(50) for now to facilitate verification.Thank you![code="sql"]CREATE TABLE dbo.OrderDetail( OrderID INT NOT NULL ,OrderDetailID INT NOT NULL, ProductID INT NOT NULL, Qty INT NOT NULL, STATUS varchar(50) NOT NULL ) -- OrderID=1 overall status = 'SHIPPED' INSERT INTO OrderDetail values ( 1, 1,100, 10, 'Shipped') INSERT INTO OrderDetail values ( 1, 2, 200, 10, 'Shipped') -- OrderID=2 overall status = 'BACKORDERED' INSERT INTO OrderDetail values ( 2, 3,100, 10, 'Shipped') INSERT INTO OrderDetail values ( 2, 4, 200, 10, 'Backordered') -- OrderID=3 overall status = 'CANCELLED' INSERT INTO OrderDetail values (3, 5,100, 10, 'Cancelled') INSERT INTO OrderDetail values ( 3, 6, 200, 10, 'Cancelled') -- OrderID=4 overall status = 'SHIPPED' INSERT INTO OrderDetail values (4, 7,100, 10, 'Cancelled') INSERT INTO OrderDetail values ( 4, 8, 200, 10, 'Shipped') INSERT INTO OrderDetail values ( 4, 9, 300, 10, 'Shipped') -- OrderID=5 overall status = 'NO STATUS' INSERT INTO OrderDetail values (5, 10,100, 10, 'No status') INSERT INTO OrderDetail values ( 5, 11, 200, 10, 'No status') INSERT INTO OrderDetail values ( 5,12, 300, 10, 'No status')USE [TrainingDB]GOCREATE FUNCTION [dbo].[udf_CalculateOrderStatus]( @OrderID int)RETURNS varchar(50)ASBEGINDECLARE @Status varchar(50)DECLARE @TotalDetails intSET @STATUS=NULLSELECT @TotalDetails = Count(Status) FROM [dbo].[OrderDetail] where Orderid=@OrderID-- SHIPPED?IF @Status is nullBEGIN SELECT @Status= 'SHIPPED' FROM [dbo].[OrderDetail] Where OrderID=@OrderID GROUP BY OrderID HAVING MAX(Status) = 'Shipped' AND MIN(Status) = 'Shipped' SELECT @Status= 'SHIPPED' FROM [dbo].[OrderDetail] Where OrderID=@OrderID GROUP BY OrderID HAVING (COUNT(CASE Status WHEN 'Shipped' THEN 1 END)) >= 1 AND (COUNT(CASE Status WHEN 'Shipped' THEN 1 WHEN 'Cancelled' THEN 1 END )) = @TotalDetails END-- CANCELLED?IF @Status is nullBEGIN SELECT @Status= 'CANCELLED' FROM [dbo].[OrderDetail] Where OrderID=@OrderID GROUP BY OrderID HAVING MAX(Status) = 'Cancelled' AND MIN(Status) = 'Cancelled' END--BACKORDERED?IF @Status is nullBEGIN SELECT @Status= 'BACKORDERED' FROM [dbo].[OrderDetail] Where OrderID=@OrderID GROUP BY OrderID HAVING (COUNT(CASE Status WHEN 'BackOrdered' THEN 1 END )) >= 1 END--NO STATUS?IF @Status is nullBEGIN SELECT @Status= 'NO STATUS' FROM [dbo].[OrderDetail] Where OrderID=@OrderID GROUP BY OrderID HAVING MAX(Status) = 'No Status' AND MIN(Status) = 'No Status' ENDRETURN @StatusENDGO-- Goal: Set of Orders with their overall statusesselect distinct orderid, dbo.udf_CalculateOrderStatus(orderid) as OrderShipmentStatus from dbo.orderdetail[/code]

How to import non-delimited text file

Posted: 03 Jul 2013 05:07 AM PDT

Hi, all. I (will) have a non-delimited text file containing 35 lines each for over 5000 records. Below is an example of lines 1-5 and 30-35 of one record. I need two of the 35 values (SubnetMask and SubnetDescription) for each of the 5000+ records in this long, continuous file. Just looking for high-level ideas on the best way to handle this. All my other processes are in SSIS, so will ultimately integrate this import/parse solution in the flow. Any thoughts appreciated.[font="Courier New"]SubnetAddress=10.16.224.128SubnetName=FOS-Salzburg Kasernenstraase #823 VPNSubnetMask=[b]255.255.255.128[/b]NetworkAddress=10.0.0.0LocationID=895...SubnetDescription=[b]CHINA-BEIJING-C025 F2 XIANGJIANG CNVASX1706[/b]DHCPOptionTemplate=DHCPPolicyTemplate=PrimaryInterface=NoAllowDHCPClientsModifyDynamicObjectResourceRecords=Same As in Global Policies[/font]

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

Posted: 20 Apr 2012 03:24 AM PDT

Windows 2003 R2 Enterprise SP2 x64SQL Server 2008 SP2At first, I thought it was an AD issue. Server guys checked and said no errors in domain controllers. I ran some reports for network errors/issues, didn't see any. The error lasted for 5-10 minutes before correcting itself.Saw this error message before the authentication one: The client was unable to reuse a session with SPID ---, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.Any ideas? I keep googling and not finding a definitive causes and resolutions.

Implicit conversion using COALESCE or CASE

Posted: 03 Jul 2013 06:07 AM PDT

Hi,I've discovered that I always seem to get an implicit conversion to INT when using this syntax:SELECT ... ORDER BY COALESCE(SectionOrder, 99); (Column [SectionOrder] is defined as SMALLINT).The execution plan COMPUTE SCALAR operation shows this syntax as being changed to a CASE statement:CASE WHEN SectionOrder IS NOT NULL THEN convert_implicit(int,SectionOrder,0) ELSE (99) ENDIs this conversion a "feature" of SQL Server? Any info is appreciated.~ Jeff

Find out which value in table is in error

Posted: 03 Jul 2013 07:11 AM PDT

I know which column is erroring out. It is varchar and I am trying to convert it to decimal. But do we know how to find out which value is erroing out. It is kind of big table.Error converting data type varchar to numeric.

Limit in date picker control in SSRS

Posted: 13 Jul 2010 11:11 PM PDT

I want to set the date limit in a parameter to the results of a SQL query/SSRS dataset. I mean that I give the limit and the calender control shows date in between those dates only.

system sp permission

Posted: 03 Jul 2013 01:18 AM PDT

quick question here: where can I find out the needed privilege to access different system sp, for example, sys.sql_modulesMy code works in my laptop but fails on server, I am guessing the application pool doesn't have enough privilege to access the system sp.Thanks.

Query Help

Posted: 03 Jul 2013 01:24 AM PDT

Hi All I need one logical help to implement in my main logic.so please help me to do [code="sql"]create table #x1(SchoolID int,SchoolName Varchar(50))INSERT INTO #x1 VALUES (101,'CITY CONNECTIONS-WEST')INSERT INTO #x1 VALUES (102,'City Connections - South')[/code]I need space around hyphens where SchoolName has no space around Hyphes. [code="plain"]SchoolID SchoolName101 CITY CONNECTIONS-WEST102 City Connections - South[/code]Expected output is as below[code="plain"]SchoolID SchoolName101 CITY CONNECTIONS - WEST102 City Connections - South[/code]Please help me to do thisThanks for Your Help

monitoring rant

Posted: 03 Jul 2013 01:37 AM PDT

I have spent the last month with a monitoring product, which shall remain nameless, that advertises its applicability to do network monitoring, application monitoring and database monitoring. I am not qualified to speak about networking or application monitors, but want to ask the list if my database expectations are out of line.From what I have learned from using the product, its main feature is the ability to poll servers throughout the network and collect WMI bits from each server. What surprised me was that if you want any db server specifics, you must write your own queries to get to them! I had been using Glen Berry's scripts which are a godsend, and dumping them to table, to collect and persist monitoring results, but my expectations are that for this kind of money, one would expect that db and resource specific information built into the product, not listed as a 'feature' as in 'yes, you can establish your own metrics and counters through the product'. Being cast into a community of other customers and community templates is not my idea of support either.Further, there is zero in the way of AI, or remedies or suggestions or indeed details pertaining to counters that have crossed thresholds. All i have been able to get out of it is 'Locks are critical' or 'Scanning is critical', but not the specifics of what is being locked how, or what resource is consuming cpu, memory or pipe. Drill down to the critical message gets you to a page with a giant gage, almost militating against getting related details in one place...almost makes me want to attach sparklines to Mr. Berry's suite of queries.Shouldn't a for profit monitoring tool do this stuff for you, besides just collecting WMI and centralizing it?thanks for bearing with me.PS...paradoxically, I just noticed the Redgate message at the top of the forum and feel compelled to say my rant is NOT directed at Redgate.

SQL 2012 SE downgrade to SQL 2008 R2 EE

Posted: 03 Jul 2013 01:22 AM PDT

Hello everyone,Is it possible to downgrade the SQL 2012 standard edition software to SQL 2008 R2 EE if I purchase SQL 2012 standard edition license? I know this option is available windows server.Thanks

SSIS Solution File Issue: Generate a new .database file and ask to overwrite?

Posted: 29 Mar 2013 02:18 AM PDT

Hello All,I have a trouble with my SSIS project solution file. I would appreciate it if someone could help me out.I'm doing things on our team foundation server. I mapped the latest project to my local path, but every time I clicked the project solution file, it gave me like: "[local mapping path]\ [b]projectname11.database[/b] already exists. Are you sure you want to overwrite it?" YES/NO?I have the [projectname].database in my mapping folder, the projectname11.database will be generated automately when I hit the .sln file in VS.If I choose to overwrite it, it will show:" The item [local mapping path]\[b]projectname11.database[/b] could not be found in your workspace" and " This file is under source code control. It must be checked out before it can be modified."If I choose not to overwrite it, " The '[local mapping path]\[b]projectname11.database[/b]' file already exists" This is really irritating. Please let me know if you have any thoughts. Thank you in advance. Best,Jing

Managing Registered Servers

Posted: 02 Jul 2013 08:51 PM PDT

Hi,I connect to SQL servers which are all hosted on a virtual environment.This virtual environment is not our on local network so we have to connect via IP & port number. I've set these servers up in my Registered Servers list in Management Studio.Once a month, this virtual is refreshed with the latest updates but during the process, the IP addresses of these SQL servers change. I then have to go & update every registered server I created to point to the new IP address. There are 17 of them in total. The IP addresses I get from a view in another SQL database.Question is... Is there a quick way I can update these IP addresses from this view? Maybe a process I can use to create a registered servers file & import that again?It's not a serious issue but a tedious process to go through every server & fix it up manually. If there's any way I can automate this process it would make my life much easier.Any help would be appreciated.Thanks!

Online Indexing Being Blocked

Posted: 02 Jul 2013 08:26 PM PDT

Hi All,Running SQL Server 2008 R2 Enterprise Edition. The environment runs 24/7Had a normal indexing job...but this was obviously being blocked by user processes. Changed to Online Indexing...but i'm still having this issue which I don't understand why. Any assistance is greatly appreciated.ThanksDenesh

Page Splits exceeds a predefined value

Posted: 02 Jul 2013 11:14 PM PDT

Hi How can i get Page splits valuePlease provide sql query to find out Page splits value

Multiple Primary key in table?

Posted: 02 Jul 2013 08:55 PM PDT

Hi,I have doubts multiple primay key on single table,Table can be support multiple primary key in this case one Clustred index created by default and other primary key which index name will be created?thanksananda

SQL SERVER 2008 64 bit installation on Windows 2008 - Error code 32

Posted: 26 Jun 2013 07:50 AM PDT

Any advices or idea how to fix it. I am in the middle of installation of it and It is stuck on error code 32 with sqlEngineConfigAction_install_confignonrc_Cpu64. I have admin rights. It is brand new box.

Datafile percentages

Posted: 02 Jul 2013 08:03 PM PDT

Does anyone have any handy T-SQL for datafile percentages?What I am looking for is the percentage of the datafiles size in relation to each other for example: total size 4GBPrimary 10%Log 15%Data 1 20%Data 2 20%Index 35%Many thanks J.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog