Wednesday, June 19, 2013

[SQL Server] Using Dynamic SQL in Stored Procedures


Using Dynamic SQL in Stored Procedures




Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.



SQLTeam.com Articles via RSS


SQLTeam.com Weblog via RSS



.

sqlteam.com

Posted by at No comments:
Labels: ,

[MS SQL Server] Need to free up Physical memory (RAM)

[MS SQL Server] Need to free up Physical memory (RAM)

Need to free up Physical memory (RAM)

Posted: 19 Jun 2013 05:05 AM PDT

Hi,There were lots of PAGEIOLATCH_SH waits while running queries, when drilled down I found the statistics were out of date, hence I ran update statistics to all databases. That time usage of physical memory became high and it hold almost full (63.6 GB out of 64 GB). Now Update Statistics activity has been finished and all the queries respond very fast but the usage of physical memory remains 63.6GB only. The Windows Task Manager->Processes->sqlservr.exe shows it holds only 961,360 KB of Memory. Other processes also don't take much memory. It is a Production server - I can't restart. Hence please help me to free up space.ThanksBala

service pack on cluster

Posted: 18 Jun 2013 10:59 AM PDT

We have a sharepoint farm that uses a cluster for database server.The cluster has two nodes, one active the other is passive.The database server now is SQL 2008 SP1, and sharepoint is Sharepoint 2007(MOSS 2007) SP2.Now I would like to apply SQL server 2008 SP3 on the cluster - two nodes.I have experiences to apply Service pack, but this one has some complications. First it is a cluster, second it is a sharepoint cluster that we don't have much control of their databases.I never did this before, and we donot have a test environment.I did find a knowlegebase article 958734 in microsoft talking about failover cluster rolling patch but it sounds very complicated.We only have two nodes, so I don't feel comfortable with following the article:I asked the original person who installed the cluster, he mentioned the SQL instances are installed first on Node1, then add to Node2.here is what I plan to do after some research online:1. Apply the hotfix on pasive node N22. Reboot the passive node N23. Failover on SQL resource : the passive node become the active node4. Apply the hotfix on the passive node N15. Reboot the passive node N16. Actually I want to use N1 as active node, so failover to N1 again to make N1 active node.My question is :Is that all I need to do?For N2, what is exactly do on the internal update, will it only do SP for client since it is an added node?Anyone has similar setup for sharepoint database, can you share some experience of apply sp on the the nodes? What I need to pay attention to?Actually we don't care too much about down time, so any way that can help do the SP safely should work for us.Thanks,

uninstall service pack

Posted: 18 Jun 2013 10:41 AM PDT

Any one knows how to uninstall a service pack of SQL server 2008 Sp3?Thanks

Detect missing db FULL backup and backup that db

Posted: 18 Jun 2013 10:52 AM PDT

I have a problem I am trying to solve. I would like to run a query of some sort that will compare a list of active databases on an instance of SQL and compare it to the backup history. If a full backup exists for that db, then ignore it. However, if a FULL backup does NOT exist for that db, then perform a FULL backup of that db.So, in simpler form:If the database has a FULL backup, do nothingIf the database does NOT have a FULL backup, take a FULL backup of that db.Any help would be appreciated. I can find several excellent scripts here that will check to see if a db has a backup...but nothing that will allow me to compare and then perform a backup. I'm not sure how to put all of it together.Thanks!

creating an instance of the com component with clsid {aa40d1d6-caef-4a56-b9bb-d0d3dc976ba2} from the IClassFactory failed due to the following error : c001f011

Posted: 24 Jul 2011 03:40 PM PDT

Hi Folks,i tried to edit a job step from ssms and am getting the following error.i've attached the screenshot of the error, please kindly help me how to fix this error..TIA..
Posted by at No comments:

[Articles] SQL Server Should Work for Us

[Articles] SQL Server Should Work for Us

SQL Server Should Work for Us

Posted: 18 Jun 2013 11:00 PM PDT

SQL Server is a great platform, but there are problems in places. Steve Jones thinks that fixing some issues might be a good investment for Microsoft.
Posted by at No comments:

[SQL 2012] Creating Snippets in 2012

[SQL 2012] Creating Snippets in 2012

Creating Snippets in 2012

Posted: 19 Jun 2013 02:45 AM PDT

Hi SSC,I'm a big fan of using snippets to reduce typing the same things over and over again. For 2008 I had [url=http://www.ssmstoolspack.com/]a piece of freeware[/url] which worked really nicely for snippets, but for 2012, it's no longer free. However with the integration of SQL into visual studio, snippets are now built in to the IDE. The problem is, I have no idea how to use them.I've found articles which show how to reference the folder in which they reside and how to put them into the query window, but what I haven't been able to find is how to actually write the XML which constitutes a snippet. The best I've been able to do is try to reverse engineer existing snippets, but that only goes so far. Does anyone have a good resource or explanation of the schema of a snippet XML document so I can build my own quicker and more flexibly? NOTE: This is about the best example I've been able to find for the purposes of re-engineering[code="xml"]<?xml version="1.0" encoding="utf-8" ?><CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet"> <CodeSnippet Format="1.0.0"> <Header> <Title>Template - Insert</Title> <Description>Demo for Insert Code Snippet.</Description> <Author> Ken O. Bonn </Author> <SnippetTypes> <SnippetType>Expansion</SnippetType> </SnippetTypes> </Header> <Snippet> <Declarations> <Literal> <ID>DatabaseName</ID> <ToolTip>Name of database.</ToolTip> <Default></Default> </Literal> </Declarations> <Code Language="SQL"> <![CDATA[ ------------------------------------------------------ -- FILENAME: Template-Expansion.snippet -- PURPOSE: Template for Expansion (Insert) snippets. -- AUTHOR: Ken O. Bonn -- DATE: May 15, 2013 ------------------------------------------------------ SET NOCOUNT ON; EXEC SP_HELPDB $DatabaseName$; ]]> </Code> </Snippet> </CodeSnippet></CodeSnippets>[/code]

Restoring a database everynight

Posted: 19 Jun 2013 01:10 AM PDT

Hello all, I was wondering if you could assit with the following please.I am in the process of a data warehouse project and one of our suppliers who hosts our data will be uploading a .BAK file of the database to an SFTP server that we have set up.I want to automate the restore of the .BAK file directly from the SFTP (which I'm fine with as the SFTP is hosted internally and I can restore from a unc path).The code I will use for this is as below - USE masterRESTORE DATABASE [Oneserve_Stage] FROM DISK = N'\\server\oneserve_DW\ExportFromOneserve\MHS_DataCut_PROD.BAK' WITH FILE = 1, REPLACE, RECOVERYWhat I want to then do once it has restored is move the .BAK file to another location - call it server2 and delete any exsisting .BAK files that may exsist there already.Is there a way I can automate this all in SSIS at all as a project that I can then schedule?Would that be the best way to do it?

Build multi subnet active / active cluster SQL 2012

Posted: 18 Jun 2013 11:03 PM PDT

Hi,my company had offices in 4 different geographical location and want to build Multi subnet cluster with active / active, to make database available for all location to update centrally. Our intention is to connect cluster database through local active instance to get performance.1. I like to know is it possible to build active / active multi subnet cluster ?2. If not what could be the best solution in sql to resolve this ? We Implemented replication but they create data conflict. regardsSachin

Limiting the number of databases on an instance.

Posted: 18 Jun 2013 08:49 PM PDT

Good day all,I would like to know if it is possible to limit the number of databases on an instance and if yes, how can I do it? I thank you in advance for your assistance.

SSIS 2012 Package Won't Run as SQL Server Agent Job

Posted: 06 Jun 2013 10:04 AM PDT

Hello, I'm new to the forum so sorry in advance for any etiquette I break :)I have an SSIS 2012 package that has a OLE DB data source connecting to an Access Database. The package runs fine when i run it from SQL Server Data Tools. I then deploy it to an SSIS Catalog and I can run the package fine from there. Next I add it as the only step in an SQL Server Agent Job and I get 4 consistent error messages."failed validation and returned validation status "VS_ISBROKEN"""One or more component failed validation""There were errors during task validation""An error occurred executing the provided SQL command: "select * from 'zqryExportTesting'". Object reference not set to an instance of an object.I've tries everything i can find. I've set the package encryption to be EncryptSensativeWithPassword instead of the default EncryptSensativeWithUserID. I've tried the 32 vs 64 bit runtime (I'm using 32 in the scenarios where it works). I've set the SQL Server Agent services to log on as the same user I am currently logged on to the server as. I've set up a proxy with the same user I am logged on to the servers as. I'm pretty sure i've tried every combination i've researched and still got nothing. I'm sure there is a simple setting or trick I'm missing.Thanks a TON in advance for any help you can provide.-Nick

Adding parameter to SqlCommand hides query from the profiler

Posted: 18 Jun 2013 10:25 AM PDT

I am using the SQL profiler to look at queries coming into my sql server. I have noticed that adding actual parameters to my query object in Ado.net causes the query not to show up in the profiler.For example, the code below works just fine, but the profiler can't see it. If I comment out the line where I add the parameter, I'll get an error, but the profiler is also able to see the query. What is happening here? Is this a bug? Is there a setting I am missing on the profiler? Currently I have it set to just look for "BatchStarting" events. [code="other"]using (var connection = new SqlConnection("Data Source=x2;Initial Catalog=dummy;Integrated Security=True;")) { connection.Open(); var command = new SqlCommand( "SELECT COUNT(*) FROM dummyTable WHERE dummyName LIKE @myparam", connection); command.Parameters.AddWithValue("myparam", "Fred"); Console.WriteLine(command.ExecuteScalar()); }[/code]

project parameters

Posted: 18 Jun 2013 09:53 AM PDT

How do I write values back to the project parameters in SSIS 2012?
Posted by at No comments:

[T-SQL] How to group by date in a date range

[T-SQL] How to group by date in a date range

How to group by date in a date range

Posted: 18 Jun 2013 10:45 PM PDT

Hello all,I wonder if somebody could point me in the right direction...I have following table:[code="sql"]SET DATEFORMAT DMY;SET NOCOUNT ON;DECLARE @t TABLE (ITEM varchar(10), QTY INT, CREATED DATETIME, ENDS DATETIME,USERID INT)INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 1',1,'27-05-2013 09:30:00',NULL,1INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 3',1,'28-05-2013 14:50:00',NULL,1INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 2',1,'27-05-2013 09:45:00',NULL,2INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 4',1,'28-05-2013 11:45:00',NULL,2INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 5',1,'28-05-2013 13:17:00',NULL,2INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 3',1,'28-05-2013 10:10:00',NULL,3INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 4',1,'28-05-2013 11:45:00', '30-06-2013 11:45:00',3INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 1',1,'28-05-2013 13:17:00',NULL,4INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 3',1,'28-05-2013 10:10:00','30-06-2013 11:45:00',4INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 5',1,'28-05-2013 10:10:00','30-06-2013 11:45:00',4SELECT * FROM @t [/code]It contains a list of items (ITEM1 - ITEM5) that is assigned to a user (USERID) once every day if that day < than ENDS of item (= this is discontinuation date for item and user) or if ENDS is NULL.For a given date range filter I need to group the quantities of items by date. e.g. for filter 01-06-2013 - 03-06-2013 the desired outcome would be[code="sql"]SET DATEFORMAT DMY;SET NOCOUNT ON;DECLARE @t TABLE (ASSIGNED DATETIME, ITEM varchar(10), QTY int)INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM1',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM2',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM3',3INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM4',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM5',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM1',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM2',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM3',3INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM4',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM5',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM1',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM2',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM3',3INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM4',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM5',1SELECT * FROM @t [/code]while if date range would be 01-07-2013 - 03-07-2013, desired outcome would be like this (ignoring the items discontinued on 30-06-2013):[code="sql"]SET DATEFORMAT DMY;SET NOCOUNT ON;DECLARE @t TABLE (ASSIGNED DATETIME, ITEM varchar(10), QTY int)INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-07-2013','ITEM1',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-07-2013','ITEM2',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-07-2013','ITEM3',2 -- one item discontinued on 30-06-2013INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-07-2013','ITEM4',1 -- one item discontinued on 30-06-2013-- ITEM5 missing completely for 01-07-2013 as discontinued on 30-06-2013INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-07-2013','ITEM1',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-07-2013','ITEM2',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-07-2013','ITEM3',2 -- one item discontinued on 30-06-2013INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-07-2013','ITEM4',1 -- one item discontinued on 30-06-2013-- ITEM5 missing completely for 02-07-2013 as discontinued on 30-06-2013INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-07-2013','ITEM1',2INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-07-2013','ITEM2',1INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-07-2013','ITEM3',2 -- one item discontinued on 30-06-2013INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-07-2013','ITEM4',1 -- one item discontinued on 30-06-2013-- ITEM5 missing completely for 03-07-2013 as discontinued on 30-06-2013SELECT * FROM @t [/code] How would I achieve these results? My main problem is how to group by date in selected range.Many thanks for any hints.

How can I display a block value count of 8 repeating

Posted: 19 Jun 2013 12:53 AM PDT

Hi AllThanks in advance for any advice.I need to print address data in blocks of 8 for the labels we are usingThe data sets returned could be any number of rows what I want to do is add a column that gives a values like thisRows returns values in new column1 - 8 19 - 16 217 – 24 3The new column value would be the page number as we need to do odd even to print on the back of the labels within SSRS 2008 body I would like to do this within the tsql I have tried ((ROW_NUMBER() OVER (ORDER BY oddpages.id_num)) / 9) + 1 AS pagenumber, but that gives strange results as the first count is correct but the 2nd give one extraRow 17 should be the start of page 3Row_ID page number1 12 13 14 15 16 17 18 19 210 211 212 213 214 215 216 217 218 319 320 321 322 323 324 325 326 327 4ThanksSimon

How To return Year & Month in this format: 2013-06

Posted: 18 Jun 2013 09:57 PM PDT

Hi All, I need to return the minimum date from my table and manipulate it so it starts from the first of the monthe.g 2012-06+'-'+'01+ and use this as the minimum date: '2012-06-01- instead of the 22ndSelect MIN(dtePostedToWebsiteDate) --Here's the minimum date >>> 2012-06-22 09:07:42.413FROM dtlVacancyPostAuditHow do I achieve this?Thanks Teee

PIVOT on VIEW for "special data"

Posted: 18 Jun 2013 09:13 PM PDT

Hi Guys,I'm in need of a solution that takes data from a primary table and also values from a separate "specialised data" table for each record. For example, I have a item "Stock1" which has the following specific set of attributes:SpecialField1SpecialField2Next, "Stock2" has the following specific set of attributes:SpecialField1SpecialField2SpecialField3and finally, "Stock3" has the following specific set of attributes:SpecialField3SpecialField4All these "SpecialFieldX" records are defined in a reference table and are able to be associated (only once) to any stock. My code below creates and populates the table necessary:[code="sql"]CREATE TABLE #ReferenceCodes( [ReferenceGroupCode] [varchar](20) NOT NULL, [ReferenceCode] [varchar](50) NOT NULL, [ReferenceName] [varchar](50) NOT NULL, [Translation] [varchar](100) NULL) ON [PRIMARY]GOINSERT INTO #ReferenceCodes(ReferenceGroupCode,ReferenceCode,ReferenceName,Translation)SELECT 'SPECIAL_DATA','SpecialField1','Stores special field 1','E'UNIONSELECT 'SPECIAL_DATA','SpecialField2','Stores special field 2','E'UNIONSELECT 'SPECIAL_DATA','SpecialField3','Stores special field 3','F'UNIONSELECT 'SPECIAL_DATA','SpecialField4','Stores special field 4','D'CREATE TABLE #StockSpecialisedData( [StockCode] [varchar](20) NOT NULL, [AttributeName] [varchar](40) NOT NULL, [AttributeClass] [varchar](30) NOT NULL, [AttributeValue] [sql_variant] NOT NULL) ON [PRIMARY]CREATE TABLE #Stocks ( [StockCode] [varchar](20) NOT NULL ,[StockName] [varchar](50) NOT NULL)INSERT INTO #Stocks(StockCode, StockName)SELECT 'Stock1','Stock 1'UNIONSELECT 'Stock2','Stock 2'UNIONSELECT 'Stock3','Stock 3'UNIONSELECT 'Stock4','Stock 4'INSERT INTO #StockSpecialisedData(StockCode,AttributeName,AttributeClass,AttributeValue)SELECT 'Stock1','SpecialField1','GROUP1','2012-03-01'UNIONSELECT 'Stock1','SpecialField2','GROUP1','1000'UNIONSELECT 'Stock2','SpecialField1','GROUP1','2013-01-18'UNIONSELECT 'Stock2','SpecialField2','GROUP1','0.256'UNIONSELECT 'Stock3','SpecialField3','GROUP1','330'UNIONSELECT 'Stock3','SpecialField4','GROUP2','1'UNIONSELECT 'Stock4','SpecialField3','GROUP1','365'UNIONSELECT 'Stock4','SpecialField4','GROUP2','0'DROP TABLE #StocksDROP TABLE #ReferenceCodesDROP TABLE #StockSpecialisedData[/code]What I would like to be able to do is create a view that pivots the "Specialised Attributes" and returns all the stock-level data, as well as all the specialised data. From my limited knowledge, I'd assume it would need to SELECT from #ReferenceCodes, then LEFT JOIN onto #StockSpecialisedData as I'd like to have all columns even if there's no relating row in #StockSpecialisedData.Is this possible using a view?As always, thanks in advance!Kevin.

Why is this Query Taking a long time?

Posted: 18 Jun 2013 05:58 AM PDT

[size="4"]This query seems to take forever. Is there a way I can speed it up?[/size] SELECT cat.Category1 , cat.Category2 , cat.Category3 , tblItem.GPItemNumber AS [Item Number] , tblItem.ItemNumber AS [Regional Item Number] , GlbOrItm.QUANTITY AS [Quantity] , GlbOr.OrderNumber AS [Order Number] , GlbOr.Created AS [Order Date] , ( tblUserProfile.FirstName + ' ' + tblUserProfile.LastName ) AS [Ordered By] , GlbOr.GPLOCNCODE , GlbOr.GPCSTPONBR AS LocationID , GlbOr.ShipContact , GlbOr.ShipAddress1 , GlbOr.ShipAddress2 , GlbOr.ShipAddress3 , GlbOr.ShipCity , GlbOr.ShipState , GlbOr.ShipZipCode , GlbOr.ShipCountry , GlbOr.ShipPhone , tTH3.TypeHierarchyDesc AS Channel , tTH2.TypeHierarchyDesc AS ChannelType , tTH1.TypeHierarchyDesc AS LocationType , tLH3.LocationHierarchyDesc AS Region , tLH2.LocationHierarchyDesc AS SubRegion , tLH1.LocationHierarchyDesc AS Market [b] FROM OPENQUERY (OrderSvr,'Select * From Commerce.dbo.GlobalOrders with (NoLock)') AS GlbOr JOIN OPENQUERY(ORDERSvr,'Select * From Commerce.dbo.GlobalOrderItems with (NoLock)') AS GlbOrItm ON GlbOrItm.fk_GlobalOrderID = GlbOr.GlobalOrderID JOIN tblItem WITH (NOLOCK) ON tblItem.GPItemNumber = GlbOrItm.GPItemNmbr LEFT JOIN (SELECT fkItemId ,aih1.itemhierarchydesc + '/' + aih2.itemhierarchydesc AS 'Category1' , bih1.itemhierarchydesc + '/' + bih2.itemhierarchydesc AS 'Category2' , cih1.itemhierarchydesc + '/' + cih2.itemhierarchydesc AS 'Category3'[/b] [b] FROM (SELECT fkItemID, fkItemHierarchyid, ROW_NUMBER() OVER (PARTITION BY fkitemid ORDER BY fkitemhierarchyid DESC ) AS 'Row' FROM tblitemcategory WITH (NOLOCK) ) S PIVOT ( MAX(fkitemhierarchyid) FOR Row IN ([1],[2],[3])) P LEFT JOIN tblItemHierarchy aih2 WITH (NOLOCK)ON aih2.ItemHierarchyID = [1] LEFT JOIN tblitemhierarchy aih1 WITH (NOLOCK)ON aih1.itemhierarchyid = aih2.parentid LEFT JOIN tblItemHierarchy bih2 WITH (NOLOCK)ON bih2.ItemHierarchyID = [2] LEFT JOIN tblitemhierarchy bih1 WITH (NOLOCK)ON bih1.itemhierarchyid = bih2.parentid LEFT JOIN tblItemHierarchy cih2 WITH (NOLOCK)ON cih2.ItemHierarchyID = [3] LEFT JOIN tblitemhierarchy cih1 WITH (NOLOCK)ON cih1.itemhierarchyid = cih2.parentid ) AS cat ON cat.fkitemid = tblItem.itemid LEFT OUTER JOIN tblUserProfile WITH (NOLOCK)ON GlbOr.UserID = tblUserProfile.fkUserID JOIN tblLocation AS tL WITH (NOLOCK)ON GlbOr.GPCSTPONBR = CAST(tL.LocationID AS VARCHAR(20)) JOIN tblTypeHierarchy AS tTH1 WITH (NOLOCK) ON tL.fkTypeHierarchyID = tTH1.TypeHierarchyID JOIN tblTypeHierarchy AS tTH2 WITH (NOLOCK) ON tTH1.ParentID = tTH2.TypeHierarchyID JOIN tblTypeHierarchy AS tTH3 WITH (NOLOCK) ON tTH2.ParentID = tTH3.TypeHierarchyID JOIN tblLocationHierarchy AS tLH1 WITH (NOLOCK) ON tL.fkLocationHierarchyID = tLH1.LocationHierarchyID JOIN tblLocationHierarchy AS tLH2 WITH (NOLOCK) ON tLH1.ParentID = tLH2.LocationHierarchyID JOIN tblLocationHierarchy AS tLH3 WITH (NOLOCK) ON tLH2.ParentID = tLH3.LocationHierarchyID[/b] WHERE GlbOr.Created >= DATEADD(day, -365, @EndDate) AND GlbOr.Created <= @EndDate AND (LEFT(GlbOr.OrderNumber, 5) = 'CINOR' OR LEFT(GlbOr.OrderNumber, 5) = 'CINSK') ORDER BY Category1 , Category2 , GlbOrItm.GPItemnmbr

Need assistance with a query

Posted: 18 Jun 2013 04:54 AM PDT

Is there a way to structure a single query to select all the unique values from field 1 of table A, and the unique values from field 2 of Table A and then select count(*) for all of the resulting combinations of field 1 and field 2 values?Thanks in advance.
Posted by at No comments:

[SQL Server 2008 issues] Memory Pressure Errors

[SQL Server 2008 issues] Memory Pressure Errors

Memory Pressure Errors

Posted: 18 Jun 2013 07:00 PM PDT

Hi,Our SQL server services got restarted last night due to memory pressure. Looking forward for your help in diagnosing the issue and finding a resolution.[u]About our environment:[/u]SQL 2008 SP1 on Windows 2003 R2 SP2 (VM) with 6GB RAM. Its running on a cluster (active/passive). The cluster service had restarted SQL services on the same active node after the incident.[u]Event Viewer Logs:[/u]1. There is insufficient system memory in resource pool 'internal' to run this query.2. SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. 3. [sqsrvres] ODBC sqldriverconnect failed4. [sqsrvres] CheckServiceAlive: Service is deadThe cluster service had restarted the SQL service after these errors.Attached are the SQL Server error log and the results of queries (taken from msdn blogs) related to VAS size. Upon examining the logs and query results, it seems the server experienced MTL pressure and the procedure cache is consuming more MTL memory. Is the diagnosis correct, and if yes, what would be the resolution for this issue?Thanks

ROLES

Posted: 18 Jun 2013 06:09 PM PDT

To run sp's what type of role we assign to user?

pivot query

Posted: 18 Jun 2013 12:27 PM PDT

hi all,I have a table like below:ID LOCATION STATUS MONTH MEASURE 1 PARIS OPEN JAN 25 2 PARIS CLOSED JAN 30 3 LONDON OPEN JAN 45 4 LONDON CLOSED JAN 50 5 PARIS OPEN FEB 27 6 PARIS CLOSED FEB 31 7 LONDON OPEN FEB 50 8 LONDON CLOSED FEB 61How do I pull these numbers in a data set as seen below:JAN 25 30 45 50FEB 27 31 50 61namely,month| measure for LOC1,STATUS1| measure for LOC1,STATUS2| measure for LOC2,STATUS1|measure for LOC2,STATUS2I believe I've done it before with a PIVOT clause but I am not sure if I have the PIVOT option at my disposal (not sure what version is the SQL Server in production).Thanks,kowalsky

PrimaryKey and Unique Clustered Index

Posted: 18 Jun 2013 03:00 PM PDT

Hi, Have a small doubt - what's the difference between the below 2 SQL statements.1. Create Table T1 (Col1 Int PrimaryKey, Col2 Int)2. Create Table T2 (Col1 Int, Col2 Int)- Create Unique Clustered Index T2(Col1)Question - in the above 2 created tables, what's the difference between T1-Col1 and T2-Col1. Do they both behave same when created in the db or are they different?Regards,Sai Viswanath

transport encoding

Posted: 18 Jun 2013 04:26 PM PDT

Hi,I need to implement transport encoding.if anybody worked on or working please provide me guidance.Thanks,Sreenivasa Chary.T

user self services

Posted: 18 Jun 2013 04:25 PM PDT

Hi,I need to implement user self services for administrative tasks.if anybody worked on or working please provide me guidance.Thanks,Sreenivasa Chary.T

integration of key management

Posted: 18 Jun 2013 04:23 PM PDT

Hi,i need to work on integration of key management.As per my knowledge we need use EKM and need to manage keys.for that we need additional hardware or appliance.please help me how to implement Integration of key management.Thanks,Sreenivasa Chary

merge case when

Posted: 17 Jun 2013 09:23 PM PDT

i have this syntaxcase when convert(varchar(10),t.[Timein],108) >= convert(varchar(10),t4.ltime,108) and spendtime is not null then 'Late' else '' end remarkscase when (540- Time_Minutes) >= 120 then 'HALF DAY' else '' end HALFDAYi want to merge this two syntax and show in one column name remarks

Different plan for a query In APP and SQL

Posted: 17 Jun 2013 09:26 PM PDT

HiI execute a query from web App and I get the select statement from sql profiler.I run exactly the statement without any changes directly in sql right away at that time,but I get 2 different plan in sql and in sql profiler.SQL and App are behaving diffrent.Developer team uses linq in App and the statement in sql profiler is in sp_executesql format.Why I get 2 different plan for a query in App and SQL?

Dynamically choose source and destination

Posted: 13 Jun 2013 06:45 AM PDT

Hey Gurus, I have just started working with SSIS and have following task at hand and I feel lost. Can any of you please help me?Currently, I have a 2 different data flow tasks that do the same thing: Accept data from a SQL Store proc, put it to flat file destination and archive it. One of them is stored procedure giving us revenue data and the other gives us accounts data. Now, I need to combine both of these different data flows as a single data flow using parameters / variables for picking up appropriate stored procedure based on whether user wants to see account data or revenue data and then, out it in the correct file and file location (Files and file locations are different for revenue and accounts data).I do understand I should use a for-each loop maybe. However, I really need assistance in getting started with how to set up variables (system / user defined) and how can I go about it. I have attached a sample data flow diagram of how I need it. Can you please pass me some tips / ideas. Please note that am a novice is SSIS and just started. Thanks in advance!

Recommendation - Rebuild Index /Partition

Posted: 18 Jun 2013 06:42 AM PDT

I have SQL 2008R2( Enterprise edition). All my tables are partitioned. I need some advice and possibly a script for index maitenance. If my indices are fragmented should i:i) Just rebuild the indices or ii) Rebuild each individual partition

Will View Increase the Performance of a query?

Posted: 17 Jun 2013 08:55 PM PDT

Hi All,I just wanted to know whether retrieving data from a view will be faster than directly querying from table.Say suppose if a view is created by joining a number of fact tables(Records greater than 100 million) with dimensional tables.Will selecting the records be faster than querying from the actual tables.Request your views on the same.

SQL

Posted: 18 Jun 2013 04:43 AM PDT

how do you set something up like codered where there are numbers within a database and then it makes calles to cutomers that are with in the database what type of other software etc is needed.

Large INSERT INTO SELECT ....

Posted: 18 Jun 2013 02:30 AM PDT

Hi SSC,Question for you, when a user runs an INSERT INTO ... SELECT * FROM ... statement I've noticed that IOReads take place before really any decent amount of IOWrites take place.What is SQL Server doing behind the scene? Is it loading the entire set of data pages from source table into memory before writing? I could see that being a problem if your buffer pool isn't larger than the table itself on disk...Thanks in advance for the insight!

Permission to run only certain SQL jobs in 2008

Posted: 18 Jun 2013 01:11 AM PDT

I have an AD group Group1 that needs access to the SQL jobs ( only certain ones) so that any users within that group can run/stop/view history of the job .- The users in this group are not sysadmin- None of the jobs are owned by the users.Does SQLAgentUserRole solve this? How do I give access to specific jobs but not the all? Any help would be greatly appreciated. Thanks

Import action stopping in middle?

Posted: 18 Jun 2013 12:21 AM PDT

Hi,Import action stopping in middle due to as below errors, pl suggestion me what could be issues? it may be invalid character in oracle dataTotal - 879212 count in oracle data, during sql side imported rows count 400234 this stage automatically stopped..Source- oracle 9iDestination - SQL[code="other"]Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification". (SQL Server Import and Export Wizard) Error 0xc020901c: Data Flow Task 1: There was an error with input column "RESULT" (119) on input "Destination Input" (86). The column status returned was: "Conversion failed because the data value overflowed the specified type.". (SQL Server Import and Export Wizard) Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (86)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (86)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard) Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - SEZLIMS5" (73) failed with error code 0xC0209029 while processing input "Destination Input" (86). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard) Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. (SQL Server Import and Export Wizard) Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Query" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)[/code]thanksananda

how to make trigger

Posted: 17 Jun 2013 10:40 PM PDT

[size="1"]CREATE TABLE [dbo].[patient]([pid] [nvarchar](50) NOT NULL,[name] [varchar](50) NOT NULL,[email] [varchar](50) NOT NULL,[password] [varchar](50) NOT NULL,[createdon] [datetime] NOT NULL,[modifiedon] [datetime] NOT NULL,[rowstate] [tinyint] NOT NULL,[Address] [varchar](100) NULL,[dob] [datetime] NULL,[phone] [varchar](12) NULL,CONSTRAINT [PK_patient] PRIMARY KEY CLUSTERED ([pid] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])table2CREATE TABLE [dbo].[pstatus]([pstatus] [varchar](50) NOT NULL,[docstatus] [varchar](50) NOT NULL,[disease] [varchar](50) NOT NULL,[bedno] [varchar](3) NOT NULL,[drugs] [varchar](50) NULL) ON [PRIMARY]new table CREATE TABLE [dbo].[searchp]([pid] [nvarchar](50) NOT NULL,[name] [varchar](50) NOT NULL,[docstatus] [varchar](50) NOT NULL,[disease] [varchar](50) NOT NULL,[bedno] [varchar](3) NOT NULL,[drugs] [varchar](50) NULL,[docname] [varchar](50) NOT NULL) ON [PRIMARY][/size]i want to create a trigger when the values are inserted into the first 2 tables the new table will be automatically updatedthanks in advance

Utility that converts JET to T-SQL

Posted: 17 Jun 2013 11:38 PM PDT

I have been looking for a tool that converts JET SQL to T-SQL...I see plenty of references to SSMA for Access 2013 and Access upsizing wizard for older versions, but if i read them correctly, they are for database conversions but don't translate between the SQL dialects, and I am looking for something that will translate Access JET into 2008 T-SQL.I have been doing it manually, but it is a total mind numbing drag and was hoping someone else has crossed that river already.thanks very much for your help

.ldf deleted and now cant attach database

Posted: 10 Jan 2012 09:58 PM PST

Hi All,I had deleted my .ldf as it was getting far too large! and then when i went back to use that database i recieve the following error when trying to create a new database with the existing .mdfMsg 5173, Level 16, State 1, Line 1One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.Log file 'H:\Logs\360MetaVerse_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.Msg 1813, Level 16, State 2, Line 1Could not open new database '360MetaVerse'. CREATE DATABASE is aborted.Thanks in advance

can anyone give me tips how to use powershell with respect to sql server 2008

Posted: 17 Jun 2013 10:01 PM PDT

can anyone give me tips how to use powershell with respect to sql server 2008

Auto Populate Dimention Usage

Posted: 17 Jun 2013 09:27 PM PDT

HiI am creating a cube (first one ish) and i have messed around with it a bit and now my Dimention Usage tab is not as detailed as it should be.By that i mean prior to me messing around with the cub i had a lonbg list of dimentions and each measure that they were related to, now i just have a single row per dimention and no related measures.looking ar my data source and cub structure it looks like the links between the tables are there.Is there a way of automatically populating these dimention usage links from the links between the fact and measure tables?I may well have not explained myself very well or there might be a more visual way of explaining myself so thanks for being patient.Thanks in advance

sql service startup account

Posted: 12 Jun 2013 05:18 AM PDT

Hi Team,I have sql service account which does not have sa rights but have all required domain rights and there is seperate account for agent service and this account has sa rights.When backup job runs it does not delete the old backup files.When I grant sa rights to sql service account it does.So my question is does sql service account always need 'sa' rights?
Posted by at No comments:

Tuesday, June 18, 2013

[SQL Server] Creating Table for Pivot Table

[SQL Server] Creating Table for Pivot Table

Creating Table for Pivot Table

Posted: 18 Jun 2013 03:33 AM PDT

Hello,I need to create a pivot table, and I think I need a new column to get the pivot to work. Basically, what I have is data with customer#, customername and dist. Each customer can have more than 1 distributor, and I want my data to be arranged like: customer#, customername, dist1, dist2, dist3, etc (as many as they have).I think I need to add a number column that will count each dist for each customer. But how do I do this?Thanks!Mary

Separating comma seperated values

Posted: 18 Jun 2013 08:06 AM PDT

I would like to pass a column to a function, and have it unioned together with my other data in it's own row... for example given the data:CommaColumn ValueColumn1 ValueColumn2----------- ------------ -------------ABC,123 1 2XYZ, 789 2 3I would like to write something like:SELECT Split(CommaColumn,',') As SplitValue, ValueColumn1, ValueColumn2 FROM MyTableAnd get backSplitValue ValueColumn1 ValueColumn2---------- ------------ ------------ABC 1 2123 1 2XYZ 2 3789 2 3Is this possible, or has anyone done this before?

having trouble granting role to user

Posted: 18 Jun 2013 07:17 AM PDT

I've submitted these SQL commands with no problem:CREATE ROLE testing; -- create Role "testing"GRANT CREATE TABLE TO testing; -- grant CREATE TABLE privilege to ROLE "testing"Now I want to grant role "testing" to user "test_user".But when I try GRANT testing TO test_user;or GRANT ROLE testing TO test_user;I get Incorrect syntax near 'testing'.When I try EXEC sp_addrolemember testing, test_user;I get User or role 'test_user' does not exist in this database.even though I HAD already created a new login for test_user in Object Explorer | < database engine > | Security | Logins (right-click)I did a search on SQL how to GRANT ROLE to userbut couldn't find an answer that worked for me. Someone even suggested GRANT testing ON test_user TO AdventureWorksDW;but again I got Incorrect syntax near 'testing'.Incidentally, just to be sure, I tried all this using an Administrator level Windows user account and an Administrator level SQL Server account.Also incidentally, I'm running SQL Server 2008 with Advanced Services under Windows XP Pro.Any suggestions? Thanks for any help anyone can give.

Trying to join 3 tables from different servers

Posted: 18 Jun 2013 02:23 AM PDT

I have been trying to join 3 different tables from 2 different servers and I am having some success but have reached a wall.Query (figured I show the actual)Select * FROM [VisNetic Mailflow].[dbo].[Tickets] [t] left join [VisNetic Mailflow].[dbo].[TicketHistory] [th] on [t].[ticketid] = [th].[ticketid] left join [VisNetic Mailflow].[dbo].[Agents] [a] on [th].[AgentID] = [a].[AgentID] left join [VisNetic Mailflow].[dbo].[TicketBoxes] [tb] on [t].[TicketBoxID] = [tb].[TicketBoxID] left join [VisNetic Mailflow].[dbo].[TicketStates] [ts] on [t].[TicketStateID] = [ts].[TicketStateID] left join [VisNetic Mailflow].[dbo].[TicketActions] [ta] on [th].[TicketActionID] = [ta].[TicketActionID] left join [VisNetic Mailflow].[dbo].[AgentGroupings] [ag] on [th].[AgentID] = [ag].[AgentID] left join [VisNetic Mailflow].[dbo].[Groups] [g]on [g].[GroupID] = [ag].[GroupID] left join [VisNetic Mailflow].[dbo].[OutboundMessages] [om] on [t].[TicketID] = [om].[TicketID] left join [PDO_Live].[dbo].[ACCOUNT] [ac] on [t].[Contacts] = [ac].[STR_EMAIL_ADDRESS] left join [PDO_Live].[dbo].[vwAPPLICATION_SUMMARY] [vwapp] on [t].[Contacts] = [vwapp].[STR_EMAIL_ADDRESS] left join [I3_IC].[dbo].[CallDetail] [cd] on [t].[Contacts] = [cd].[RemoteNumberFmt] where [t].[DateCreated] between '05-01-2013' and '05-02-2013' and [th].[TicketBoxID] = '16' Order by [t].[DateCreated]and then take and join with [Server2].[db3].[arrival].**The end result would be to show where the unique incident lead to the 1st call(HH:mm:ss) after the incident took place.when I execute the query I get the error *** Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation. ***

how to copy for a file in sub directories basing on the file name in ssis

Posted: 18 Jun 2013 02:57 AM PDT

How to copy a particular file from sub folders basing on the file name. I have multiple sub folders and the file which i am looking for may present in the sub folder level or it may be present in another sub folders under the present sub folder. can any of you guys help me in this

Move a 2008 database to a new machine on 2008R2

Posted: 17 Jun 2013 09:45 PM PDT

I was supposed to be migrating some 2008 (SP3) databases from old physical machines to new virtual machines. I was just going to do a straightforward backup, move file, restore, etc. etc.At the last minute I've been told the new machines need to be running 2008R2, not 2008. As is usual where I work it's all seat of the pants stuff, and has to be done NOW. Help! What's the sequence of events here? (I've been Googling but I'm getting a lot of answers about how to restore an R2 database to 2008, but not the other way around.) I can't upgrade the current databases to R2, only the destination ones, which is what is complicating things. I'm sorry if this is really simple; I've been ill for a week with a raging temperature and I can't think straight. Any help would be gratefully received.
Posted by at 1 comment:
Subscribe to: Comments (Atom)

Search This Blog