Saturday, March 30, 2013

[SQL Server] monitoring a table

[SQL Server] monitoring a table


monitoring a table

Posted: 29 Mar 2013 10:33 PM PDT

I want to monitor a table continuously to see whether its updating or not if its updated i want too know how many rows it update and i wan to store them in a tabletime updated, no of rows

[MS SQL Server] Sql server logins

[MS SQL Server] Sql server logins


Sql server logins

Posted: 29 Mar 2013 05:31 AM PDT

Hi,can somebody help me with How to find out all user's permission, roles for all the databases?Thankstina

Version not chaning in Change Tracking in SQL Server 2008

Posted: 29 Mar 2013 06:07 AM PDT

Hi,When i Inserted few record in a table where CHange Tracking is enable, has generated all different Version Number for column SYS_CHANGE_VERSION and same for column SYS_CHANGE_CREATION_VERSION(For Insert SQL).Whereas in another table(In another DB), the 4,000,000 records only 50 distinct SYS_CHANGE_VERSION generated. So it was like a group of records have same version number.I was under impression that new version will be created each time if there is change is happening(insert,update,delete) in a table.Please guide..Anupam

[T-SQL] MERGE TSQL in SQL SERVER 2008

[T-SQL] MERGE TSQL in SQL SERVER 2008


MERGE TSQL in SQL SERVER 2008

Posted: 29 Mar 2013 06:34 PM PDT

Hi ALL,Need one help/suggestion in tsql using MERGE in sql server 2008.I'm using the below code to do upsert in one of our table. [code="sql"]MERGE [dbo].[Dly_Target_Comp] AS DSTUSING [dbo].[Dly_Target_Comp_Temp] AS SRCON (SRC.[DateKey] = DST.[DateKey]ANDSRC.[Wghtd_WI_Key] = DST.[Wghtd_WI_Key])WHEN NOT MATCHED THENINSERT ([DateKey], [Wghtd_WI_Key], [Item_Cd], [Metric_Desc], [Volume])VALUES (SRC.[DateKey], SRC.[Wghtd_WI_Key], SRC.[Item_Cd], SRC.[Metric_Desc], SRC.[Volume])WHEN MATCHED AND ( ISNULL(DST.[Item_Cd],'') <> ISNULL(SRC.[Item_Cd],'') OR ISNULL(DST.[Metric_Desc],'') <> ISNULL(SRC.[Metric_Desc],'') OR ISNULL(DST.[Volume],'') <> ISNULL(SRC.[Volume],'') )THEN UPDATE SET DST.[Item_Cd] = SRC.[Item_Cd] ,DST.[Metric_Desc] = SRC.[Metric_Desc] ,DST.[Volume] = SRC.[Volume];[/code]Now the requirement has changed, instead of doing update when there is a MATCH i need to do insert with the help of a new column called 'Projection_Date' . So there will same records but with different projection date.But while trying to insert when matched, it says insert not allowed in 'when matched' clause.Please advice.Thanks in advanceSam

i need the max length value of every column in every table in a database

Posted: 29 Mar 2013 07:34 AM PDT

I'm creating some dynamic sql to loop through each table and column and basically generate some dynamis sql that will get the max length of each column in each table in a database. Then store it locally into a table like the below.create table TableColLengths(id int identity(1,1), tablename varchar(255), colName varchar(255), MaxColLength bigint)I'm going to write it myself however didnt know if somewone has already done it so i dont have to go though the hassle.

harnessing sp_msforeachdb to return one result set

Posted: 29 Mar 2013 06:16 AM PDT

I found a nifty but unsupported stored procedure that loops through all databases on your server and allows you do to things to each database. I would like it to just return a list of db_users for each database. I tried executing as follows but I get a seperate result set for each loop:[code="plain"]execute sp_msforeachdb 'use[?]; SELECT name, type, type_desc FROM sys.database_principals '[/code]Can someone show how to get only one result set listing all databases with corresponding users, type, and type_desc in one result set?

please help with cursor syntax

Posted: 29 Mar 2013 03:56 AM PDT

I have a simple cursor and I need help fixing syntax.(I know that in this scenario I am using cursor properly).The purpose of the cursor (copied below) is just to output the names of all databases on my server. Later I will use dynamic sql (not copied below) to display the respective metrics (user_scans, user_lookups) for each database from sys.dm_db_index_usage_stats.For the cursor part I'm getting the following error:Msg 16915, Level 16, State 1, Line 4A cursor with the name 'getDatabaseName' already exists.Msg 137, Level 15, State 2, Line 3Must declare the scalar variable "@DatabaseName".Msg 137, Level 15, State 2, Line 6Must declare the scalar variable "@DatabaseName".Msg 137, Level 15, State 2, Line 8Must declare the scalar variable "@DatabaseName".my cursor script[code="plain"]use MiguelsAppDECLARE @DatabaseName varchar(max)DECLARE getDatabaseName CURSOR FOR SELECT name, database_id, create_dateFROM sys.databases where name not in ('master', 'tempdb', 'model', 'msdb');GOOPEN getDatabaseNameFETCH NEXTFROM getDatabaseName INTO @DatabaseNameWHILE @@FETCH_STATUS = 0BEGINprint @DatabaseNameFETCH NEXT FROM getDatabaseNameINTO @DatabaseNameENDclose getDatabaseNamedeallocate getDatabaseName[/code]Will someone please tell me where my syntax is wrong so it will run?

How to select data in a particular format

Posted: 29 Mar 2013 04:40 AM PDT

Greetings all.I have data in a view that when selected from looks like this:CertYear CertCode Quantity-------- --------- --------2008 HI-Master 22008 HI-Instr 12008 HI-Train 12008 HI-Master 32008 HI-Train 12009 HI-Master 32009 HI-Instr 4There are a few other fields that are in this view, but I left them out because they're not really relevant to this question (apart from simply knowing that there are more fields in this view, which is why the data that I have above looks redundant in nature, but isn't). I've also left out more rows (such as year 2010 and later). [b]What I need: [/b]Using a single select statement (I am not allowed to do this using a stored procedure), I would like to return a record set from this view that looks like the following:CertYear QtyMaster QtyInstr QtyTrain-------- ---------- -------- --------2008 7 2 42009 3 4 3I have a query that I've constructed which gets me part way there, but not quite. What I have is this:[code="sql"]select v.CertYear, v.CertCode , case when v.CertCode = 'HI-Master' then sum(v.Quantity) else 0 end as QtyMaster , case when v.CertCode = 'HI-Instr' then sum(v.Quantity) else 0 end as QtyInstr , case when v.CertCode = 'HI-Train' then sum(v.Quantity) else 0 end as QtyTrainfrom dbo.vCerts vgroup by v.CertYear, v.CertCodeorder by v.CertYear asc, v.CertCode asc[/code]But what this select yields (like I said, it's not quite what I want) is this:CertYear CertCode QtyMaster QtyInstr QtyTrain-------- --------- ---------- -------- --------2008 HI-Master 7 0 02008 HI-Instr 0 2 02008 HI-Train 0 0 42009 HI-Master 3 0 02009 HI-Instr 0 4 02009 HI-Train 0 0 3I tried remove the CertCode from the GroupBy and the select (except not from the case statement itself), because I thought that might get me to the record set that I want, but instead I get an error that says "Column 'dbo.vCerts.CertCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."Thanks in advance for any help on this, and sorry for the complexity involved!edit: Sorry for the data that is hard to read. I'm not sure how to get whitespacing to persist on this site (it appears to be mostly ripped out).

Alter Column with Identity

Posted: 18 Aug 2010 06:14 PM PDT

Hi All,The following one is the Table definition.After inserting some rows ...i want to add identity property to Owner_id.So please give me alter statement.CREATE TABLE [dbo].[Testowners]([owner_id] [int] NULL,[owname] [varchar](15) NULL)INSERT INTO Testowners Select 1,'asas'Select * from Testowners

Generate all possible number combinations for a provided list of numbers

Posted: 29 Mar 2013 01:17 AM PDT

Hi Experts,I want to get "all possible number combinations" for provided list of numbers.As an example, if I have 4 numbers: [b]1, 2, 3 and 4 [/b]For these numbers, I want to generate following numbers [which should be in order, so as to get unique list]:11,21,31,41,2,31,2,41,3,41,2,3,421,2 (for 2,1) [which is duplicated]2,32,41,2,3 (for 2,1,3) [which is duplicated]1,2,4 (for 2,1,4) [which is duplicated]1,2,3,4 (for 2,1,3,4 ) [which is duplicated]31,3 (for 3,1) [which is duplicated]2,3 (for 3,2) [which is duplicated]3,41,2,3 (for 3,1,2) [which is duplicated]1,3,4 (for 3,1,4) [which is duplicated]1,2,3,4 (for 3,1,2,4) [which is duplicated]41,4 (for 4,1) [which is duplicated]2,4 (for 4,2) [which is duplicated]3,4 (for 4,3) [which is duplicated]1,2,4 (for 4,1,2) [which is duplicated]1,3,4 (for 4,1,3) [which is duplicated]2,3,4 (for 4,2,3)1,2,3,4 (for 4,1,2,3) [which is duplicated]Therefore unique list of possible number combinations (which are ordered) for 1,2,3 & 4 is:1; 2; 3; 4;(1,2); (1,3); (1,4); (2,3); (2,4); (3,4);(1,2,3); (1,2,4); (1,3,4); (2,3,4)(1,2,3,4)Could this be achieved using sql? Thanks much for your help!Thanks

[SQL Server 2008 issues] APP-101 Exam Questions Free Download

[SQL Server 2008 issues] APP-101 Exam Questions Free Download


APP-101 Exam Questions Free Download

Posted: 29 Mar 2013 07:29 PM PDT

AppSense, the leading provider of user virtualization technology, has been elevated to a Managed Independent Software Vendor (ISV) Partner status by Microsoft Corp. The company was previously a High Potential Partner since 2009. Prior to joining exam1pass AppSense, Hoelzl spent four APP-101 exam years with Parallels as vice-president and general manager EMEA, helping the company achieve a market-leading position in desktop virtualisation.Most companies redesigning exam1pass their desktop architecture to take advantage of new, more efficient technologies such as Windows 7 and Microsoft APP-101 exam Application Virtualization (App-V) find that user-related challenges can become an obstacle to deployment.Hoelzl's goals are to communicate to the local market the benefits of user virtualisation and grow revenue in the region with a focus on the exam1pass largest 500 companies. We are pleased to see AppSense extend Microsoft's User State Virtualization to deliver APP-101 exam enhanced capabilities that help customers manage their employees' data and settings across physical and virtual environments.http://www.exam1pass.com/APP-101-exam.html

How to copy All data from sp_helpdb to some other table

Posted: 29 Mar 2013 03:00 PM PDT

Hi All,I am trying to copy All data from sp_helpdb to some other table. I am not finding any solution that how to do this.Can someone help on this.

Multiple order by in a result set

Posted: 28 Mar 2013 08:54 PM PDT

I have a specific requirment where a table contains a column called 'Empno' with the values 12, 34, 3, 78, 65, 109, 8. I want first three values(12, 34, 3) should print as it is. rest of the values should come in ascending order. expected out come should be as 12, 34, 3, 8,65,78,109. I tried using rownumber() with case statement in order by clause. but couldnt help?any ideas please?

SQL Query on Staging Table in SSIS 2008

Posted: 29 Mar 2013 02:34 PM PDT

Hi.I am doing ETL and after transformation (Multiple table joins etc) has been done, I got the table shown in the picture attached. Now finally I want to perform some simple queries on this transformed table to filter out records. How I can perform sql query on this table ? After performing queries I will load data to the destination table. Please help me.I will be very thankful to you.Regards

Assistance with this query

Posted: 25 Mar 2013 10:01 AM PDT

I'm a bit new to SQL, and I'm attempting my first real project. Currently, my setup is: SQL Server 2008 R2 with a linked SQL Server 2000.A couple points that I'm having issues comprehending:[li]Each record in the "employee" table has it's own ACTIONDATE, which allows more than one record for the same person, so getting one result for one person is appearing difficult. EMPLOYEEIDs are unique to the user, so how would I word the query to grab the most recent ACTIONDATE for each employeeid?[/li][li]Multiple joins are causing my queries to run for over 3 minutes. Each table has 100k records, minimum. How would I optimize a query with multiple joins with big tables? Or is there a better way to grab this data?[/li]I'm attempting to make a hierarchy from a SQL Server that houses are workforce software and place it into my SQL Server 2008 R2 to be utilized in another application. I want to grab all active employees (ENDDATE IS NULL) with the most recent ACTIONDATE of each active EMPLOYEEID.The query I'm attempting to run is:[code="sql"]SELECT DISTINCT employee.EMPLOYEEID, employee.FIRSTNAME, employee.LASTNAME, employee.STARTDATE, employee.EMAILADDRESS, org.ORGANIZATIONID, job.JOBTITLEIDFROM linkedServer.linkedDB.dbo.AUEMPLOYEE as employee RIGHT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEORGANIZATION as org ON employee.employeeid = org.employeeid RIGHT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEJOBTITLE as job ON employee.employeeid = job.employeeidWHERE employee.ENDDATE IS NULL[/code]

GO (batch separator)

Posted: 29 Mar 2013 10:38 AM PDT

If i copy/paste the output of the dynamic query and run it -then it works fine but when i run it using within dynamic sql using EXEC then it give me the following error:Incorrect syntax near 'GO'.Msg 111, Level 15, State 1, Line 8'CREATE VIEW' must be the first statement in a query batch.Msg 102, Level 15, State 1, Line 128What is the work around this?Thanks.

SQL2K8R2 Computed Column formula error

Posted: 29 Mar 2013 10:51 AM PDT

Hello,I was going to create a computed column based of course upon the values in three other columns. Of the four (the fourth being the computed column) involved 3 are defined as time(7). The fourth is tinyint. The formula is quite simple. (A*B)+C = Answer.SoColumnA time(7)ColumnB tinyintColumnC time(7)ColumnAnswer time(7)My formula is like this:(([ColumnA] * [ColumnB]) + [ColumnC])The error...time is incompatible with tinyint.So am I going to have to move this to a udf and even then not sure what I would CAST / CONVERT to accomplish the multiplication??Pointers would be greatly appreciatedJBSo to add to this I have written the following in SSMS:DECLARE @A time(0)DECLARE @B tinyintDECLARE @C time(0)SET @A = 0:1:30'--Well this won't do because I need everything in seconds to do the math sooo...DECLARE @Aseconds intSET @Aseconds = (DATEPART(HOUR, @A) * 3600) + (DATEPART(MINUTE, @A) * 60) + (DATEPART(SECOND, @A))SELECT @Aseconds * @B--then convert it back to time to add @CSELECT CONVERT(varchar, DATEADD(ms, @Aseconds * 1000, 0), 114) --Now dateadd each part really?? should I do this in .NET instead?

How to preserve global temporary table data

Posted: 29 Mar 2013 06:05 AM PDT

Hi guys,I would like to know if anyway to preserve global temporary table data till some one explicitly drop it. I have created one global ##tmp table and inserted data into it in one procedure (proc1) and selecting the data from another procedure (proc2). If i execute the proc2 immediately after proc1 finished.. there is no issue but when i execute the same proc2 after few min later (for say around 15/20 min) i found that the global tmp table has been dropped!!!! how to achieve the result without creating any physical table!!!Thanks for your help....

SSIS question on CSV Import

Posted: 29 Mar 2013 08:46 AM PDT

Many people may have faced this problem and I searched many solutions but somehow I could not hit the right one. Sorry for repeatative question.Here is my data in csv file and I am trying to import into sql server table but it does not import the way it should.field1,field2,field3,field4,field5,field6n1,n2,n3,stringWithout Quotes,"String in Quotes with ""words"" and coma , in the string",some moren1,n2,n3,"stringWith Quotes","String in Quotes with coma , in thestring",some moreI need output[u]Record 1:[/u]Field1: n1 Field2: n2Field3: n3 Field4: stringWithout QuotesField5: String in Quotes with "words" and coma , in the stringField6: some more[u]Record 2:[/u]Field1: n1Field2: n2Field3: n3Field4: stringWith QuotesField5: String in Quotes with coma , in thestringField6: some moreI tried to cleanup with Regex in script task ,mentioned in some other blog but it did not work and I am not expert to change RegEx to get desired output. Can anyone please help to find solution? Regex should work but any other solution is also welcome.Thanks.

Need help with this query... ER diagram attached

Posted: 29 Mar 2013 08:37 AM PDT

HiCan some one please help with this query:[b]List all Customers with multiple Meters.[/b]I'm a complete newb so forgive me if this is an easy problem to solve..Customers – minimum 20 recordsMeters – min. 30 recordsMeter Readings – min. 100 readingsInvoices – 1 per Meter ReadingI have tried the following query with no success... select *from CustomersLEFT OUTER JOIN Meters ON Customers.idCustomers = Meters.Customers_idCustomerswhere Customers.idCustomers = Customers.idCustomers;I have also attempted SELECT CASE queriesER diagram is attachedThanks

Check Constraint Question - Situations Where a Child Record Should Be Limited to One Occurrance

Posted: 29 Mar 2013 05:01 AM PDT

One of my databases has a table for Project Dates. Some of the milestone dates can occur multiple times such as 'Project Review'. Others can only occur once, 'Project Handover'. Is there a way to create a check constraint whereby the dates that should only exist once, can be limited to existing only once while the others can exist multiple times?I do have an external table that manages the various dates and assigns a key to each date.Table: DateCategoriesId (Primary Key)Description[Other Fields]Table: Project DatesId (Primary Key)DateCategoryId (Foreign Key)Milestone Date

Parse CC in String

Posted: 29 Mar 2013 05:40 AM PDT

Hi Guys,I have a table with a column which is VARCHAR. This column has text and potentially Credit Card # and also has couple of dates. What I need to do is find all the records that may potentially have CC# in this column... I was thinking of using something like[code="sql"]WHERE patindex('%[0-9][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -]%',Memo) > 0[/code]Is there a better way to look for numbers in the string that may potentially be CC#. It is fine if some of them are not CC#. Maybe someone has already worked on a function like this where you find CC# from a string... Any help is appreciated. I am not looking for CLR function though as I have to do this using Query Analyzer.Thanks,Laura

Several Service Broker queues keep auto-disabling, not sure how to debug it

Posted: 29 Mar 2013 12:36 AM PDT

I have five queues that are automatically disabling themselves after being re-enabled. I've been running broker for about two years without such a problem, not sure where to begin troubleshooting. The only errors in sys.transmission_queue say "One or more messages could not be delivered to the local service targeted by this dialog."Can anyone help point me in the right direction?

How to avoid Deadlock while deleting data

Posted: 29 Mar 2013 04:18 AM PDT

The X_table has around 25K rocords (52MB in size), the application deletes records from this table, when it deletes the deadlock occurs, spid 1 hold X lock while deleting and spid 2 request for S lock until the spid 1 completes its delete and vice versa, which leads to deadlock.Below is the simple delete statement.exec sp_executesql N'delete from X_table where internal_name = @tableName',N'@tableName nvarchar(21)',@tableName=N'Tbl_Deleteme'I've attached deadlock graph and estimated plan for delete as well for reference. Could you please share your thoughts to mitigate this deadlock issue?Thanks in advance...

club to columns into one

Posted: 29 Mar 2013 02:25 AM PDT

The type and task column are combined into one table task keeping in mind the order they have to come in.current tableProc seq type task1 1 a1 this is 1 2 b2 supposed 2 1 a1 to 2 2 b2 be a 2 3 c3 test db2 4 c3 thank youdesired tableProc seq task1 1 a11 1 this is 1 2 b21 2 supposed 2 1 a12 1 to 2 2 b22 2 be a 2 3 c32 3 test db2 3 thank you

Joining two column to One

Posted: 29 Mar 2013 02:58 AM PDT

This current table has to be converted to the @temp_outOrder would be like this Proc --> Type --> Individual task for that typethe order of the @temp_out task col is defined like Order by type is base on the seq col then the each Type will have the list of task below it[code="sql"]/*Current Table*/Declare @temp Table ([Proc] INT,Seq INT,Type VARCHAR(2),Task VARCHAR(20))INSERT INTO @TEMPSELECT '1','1','a1','this is ' UNION ALLSELECT '1','2','b2','supposed ' UNION ALLSELECT '2','1','a1','to ' UNION ALLSELECT '2','2','b2','be a ' UNION ALLSELECT '2','3','c3','test db' UNION ALLSELECT '2','4','c3','thank you'SELECT * FROM @TEMP/*Desired Table */Declare @temp_out Table ([Proc] INT,Seq INT,Task VARCHAR(20))INSERT INTO @temp_outselect '1','1','a1' unionselect '1','1','this is ' unionselect '1','2','b2' unionselect '1','2','supposed ' unionselect '2','1','a1' unionselect '2','1','to ' unionselect '2','2','b2' unionselect '2','2','be a ' unionselect '2','3','c3' unionselect '2','3','test db' unionselect '2','3','thank you'SELECT * FROM @temp_out[/code]

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

Replication issue, blocked by another process and invalid article

Posted: 29 Mar 2013 02:22 AM PDT

Hello Allwell I have this weird issue, this is the layout3 servers, a publisher a distributor and a subscriptorall using sql server 2008 r2on the publisher there are two replications out of one databasethe first one always works fine, the second is failing while validating the snap view for two articles with these errors:[0%] The synchronization view for article 'table1' is invalid.[0%] The synchronization view for article 'table2' is invalid.Server MYSERVER, Level 13, State 51, Procedure sp_MSrepl_reinitsubscription, Line 603Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Source: MSSQLServer, Error number: 1205)now, on that server I do not see any blockingand the replication agent just keeps trying and tryin until it finish,if I restart the replication it will work just fine the first time and then the issue is backI removed the articles from that and create a new replication with only those two moving the data to a new database on the subscriber and the error continuesI already restarted all the services and nothing changes, no idea what to do nowmy issue is exactly the same that is happening here:http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/21206c41-a83e-4a40-805c-3825e4535595/but it was not solved....Please please help me, I really need this to start working

Curious: performance difference when using function in a where clause.

Posted: 21 Mar 2013 11:12 PM PDT

Hello all,This is just something I noticed and am [b]wondering[/b] about (I think this is an unexpected effect), there is [b]no problem[/b] to solve.I get a performance difference when I use a function in a where clause. See the code, the performance numbers are below the code.Sorry I was not able to isolate the problem and give definitions and data to reproduce this.Please read the code.[code="sql"]------------------------------------------------------------------------------------select * from client_order where -- ABS(ClientNr) = 12345 [b]ClientNr = 12345[/b] and startdttm = '2012-03-16'----(1 row(s) affected)----Table 'Encounter'. [b]Scan count 30, logical reads 62, physical reads 61[/b], read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.---- SQL Server Execution Times:---- CPU time = 0 ms, elapsed time = 765 ms.-- Estimated execution plan: Estimated Subtree Cost : [b]0.106408[/b]-- Actuel execution plan: Estimated Subtree Cost : [b]0.106408[/b]------------------------------------------------------------------------------------------------------------------------------select * from client_order where [b]ABS(ClientNr) = 12345[/b] -- ClientNr = 12345 and startdttm = '2012-03-16'----(1 row(s) affected)----Table 'client_order'. [b]Scan count 1, logical reads 4, physical reads 4[/b], read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.---- SQL Server Execution Times:---- CPU time = 0 ms, elapsed time = 49 ms.-- Estimated execution plan: Estimated Subtree Cost :[b] 0.0183334[/b]-- Actuel execution plan: Estimated Subtree Cost : [b]0.0183334[/b]------------------------------------------------------------------------------------[/code]Thanks for your time and attention,ben brugman

addinga Func to an SP?

Posted: 29 Mar 2013 12:33 AM PDT

HiFirst time doing this and not sure how(Even if its the proper way)I created the function GetforperiodALTER FUNCTION [dbo].[GetForPeriod] ( @StartDate datetime, @EndDate datetime) RETURNS TABLE RETURN SELECT Date FROM Auxiliary.Calendar WHERE date BETWEEN @StartDate AND @EndDatewhich returns the dates for the periodI want to add this function to an SP that looks at appointment dates , the reason is, I want to setup a matrix where if an appointment does not fall on a date I still want to display the date so its a clean.... say for January, 1 thru 31 I want to use @startdate and @Enddate as parameters....Make sense?ThanksJoe

ER diagram and related query!

Posted: 28 Mar 2013 11:04 PM PDT

HiI am getting 1000 rows returned from this Query: (see attached ER diagram)select *from Customers,Meters, MeterReadings, Invoice where Customers.idCustomers = 2;Obviously that's wrong!! so i'm thinking that maybe the ER diagram is set up wrong... for example, I should get two metersIDs back for customerID 2, with the associated readings and invoices but thats not the case, Where am i going wrong??Customers – minimum 20 recordsMeters – min. 30 recordsMeter Readings – min. 100 readingsInvoices – 1 per Meter ReadingThe query is:For any specific Customer list each of their Meters, Meter Readings and InvoicesIf you haven't guessed already I'm a complete newbHope you can help... even if it's just to tell me that the ER diagram is set up correctly!! If need be I can attach the data that I used to populate the tablesThanks

XML Index

Posted: 29 Mar 2013 12:35 AM PDT

We had a report that was using XML column and timing out. I added an XML Index and the report started to work. Last weekend we had a release and the size of the XML columns has been measured as as much as 10 times bigger. We now experience slowness and some blocking. Would the changes in that index that occur when the XML gets inserted, Updated or deleted be so determintal that I should remove the XML Index on that column?

Query running slow

Posted: 28 Mar 2013 07:47 PM PDT

Hi,Sql queries were running slow and these are triggering from They are supposed to run for 5-10 sec, but now they are taking 2 min.I can see SOS_scheduler in last wait column of sysprocesses.We have found fragmentation and rebuild the indexes. but after some time, the users were reporting the problem again.Can anyone advise how to proceed?

Display Every three rows has diffrent color repeatedly using tablex report in ssrs

Posted: 28 Mar 2013 09:58 PM PDT

Hi All,I have one requirement like report display 25000,client want to display row1=green,row2=blue,row3=red,row4=green,row5=blue,row6=red,row7=green.........can any one give me how to resolve the issue with details.Thanks,Vivekanand Kola

validating values

Posted: 28 Mar 2013 09:08 PM PDT

Hi,I have a screen from which some columns values like VoucherNo, Denomination, Quantity etc. I have to matched those values from my database tables that those values exists or not. If they exists then I have to stored those values in a separate table and if not matched then I have to store in a separate table.can you plz tell me how can I achieve this?How can I matched the value?

Friday, March 29, 2013

[how to] get rank in marks database in mysql

[how to] get rank in marks database in mysql


get rank in marks database in mysql

Posted: 29 Mar 2013 06:55 PM PDT

I have a Student database having one table EXAM.

id    1  2  3  4  5  marks 7 10  7 10  9         

Question is how to give rank like

+------+  | rank |  +------+  |    3 |  |    1 |  |    3 |  |    1 |  |    2 |  +------+  

I have used a Query:-

SELECT id, marks, FIND_IN_SET( marks, (  SELECT GROUP_CONCAT( marks  ORDER BY marks DESC )   FROM exam )  ) AS rank  FROM exam;  

but it gives rank

+------+  | rank |  +------+  |    4 |  |    1 |  |    4 |  |    1 |  |    3 |  +------+  

What should I do??

Recreate Indexes on 1 billion record table

Posted: 29 Mar 2013 03:55 PM PDT

I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partiton this table on a new Clustered index with date column. I have just enough space (150gb) in the ldf file to grow incase of index rebuild ( with tempdb OFF). Please help me with an efficent way to rebuild the indexes on new Partition function/scheme. Which of the two approaches would be an efficent and less resource consuming way ( OR recommend any other approach)

1.

  • Drop existing CIX and drop all N-CIX's
  • Create CIX on PScheme
  • Create aligned/non-aligned indexes on PScheme

2.

  • Drop only CIX
  • Create CIX on PScheme
  • Create aligned/non-aligned indexes (with DROP_EXISTING) ON

Thanks

MySQL not allowing text columns?

Posted: 29 Mar 2013 01:30 PM PDT

I'm using MySQL 5.5.27. I currently have a BLOB column, that I want to convert to text to allow fulltext searching. The ALTER TABLE command to create a text column is successful, but the ALTER TABLE table ADD FULLTEXT INDEX index (column ASC) to create a fulltext index returns an error, saying I cannot create a fulltext index on that column.

When I go back and look at the specs for the table, it appears as BLOB, instead of text.

I'm using MySQLWorkbench, but I've generally been running the scripts by hand, instead of using the wizard.

I have tried setting a character encoding for the entire schema (none existed before) thinking that this would be needed for text columns, but it didn't help.

How to create view in SQLite using INSERT INTO?

Posted: 29 Mar 2013 12:33 PM PDT

I'm trying to create view that will summarize data in two tables. Each table have several columns, but they both have NAME and AREA columns. And I want these two columns to be united correspondingly in view. I tried to create view using following query:

CREATE VIEW summary AS  INSERT INTO (SELECT Name, SUM(Area) FROM table1 GROUP BY Name)  SELECT *  FROM (SELECT Name, SUM(Area) FROM table2 GROUP BY Name)  

But I get the error: SQL error: near "INSERT": syntax error. Actually I tried different querys involving INSERT INTO and it seems that CREATE VIEW will not work with INSERT INTO, and INSERT INTO does not accept subquery - only existing table (even temporal) would do.

How to rewrite CREATE VIEW statement to achieve my goal?

Role of Database Administrators

Posted: 29 Mar 2013 10:46 AM PDT

I am currently working as an application developer on a project which involves the creation of a new SQL Server database. I am actively working with a group of DBAs who will play some role in the creation and maintenence of this database.

I want to understand what role the DBA normally plays in this type of scenario. It seems the DBAs consider everything to be the responsibility of the application team. I designed the schema and indexes, generated the DDL, and am currently in the process of performance testing the database. Are these tasks normally performed by development?

Also, it has been suggested that development should be responsible for the creation and maintenence of the database user accounts. We would be responsible for periodically changing the passwords on the production databases. Is this the norm? It seems to me that it should be done by the DBAs.

How to migrate SQL Server to MySQL

Posted: 29 Mar 2013 03:42 PM PDT

I'm trying to migrate a SQL Server db to MySQL, and I'm having a tough time. I've tried:

  • MySQLWorkbench -- migration tool fails because my login only shows views in SQL Server, not the tables themselves, and so I can't get past the "Schemata Selection" screen.

  • sqlcmd and bcp -- both fail because they can't export csv properly. They don't quote strings with commas in them. Plus, they don't create table schemas in MySQL.

  • SQLyog -- just fails. Creates a MySQL table from a MS SQL view ok, but doesn't import the data. Also takes a really long time on small tables, and I have to import millions of rows.

Anyone have suggestions?

SSIS Visual Studio 2008 reference variable scope in expression builder

Posted: 29 Mar 2013 09:10 AM PDT

When creating an SSIS package in Visual Studio 2008, in the expression builder, I know I can reference a variable by using @[User::FilePath] for example but this only works if you are evaluating the expression for an object that is within the same scope of the variable that you are referencing.

Is it possible to reference the SCOPE of the variable such as @[User:PackageScope:FilePath] or @[User:SQLTaskScope:FilePath] for example so that I can call a variable that is in a different scope then the object I am building the expression for?

How can I check for SQL Server performance problems using T-SQL? [closed]

Posted: 29 Mar 2013 08:59 AM PDT

I know how to use the Profiler, Performance Monitor, and Activity Monitor. I want to find a way to troubleshoot performance using T-SQL. How can I check for Microsoft SQL Server performance problems using T-SQL?

How to create column

Posted: 29 Mar 2013 09:16 AM PDT

I am new to ssrs report designing can any one help me in this

I have two question

1) How to create a column to input user data and after that I need to perform some operations on data enter by the user which i need to display in another column

2)how to make round a decimal to nearest integer ex 6.03 to 6 and 6.52 to 7

SQL Server 2005 Unexpected Disk Usage during Backups

Posted: 29 Mar 2013 10:30 AM PDT

On our SQL Server 2005 SP4 server we have a maintenance plan in place which performs a full backup of selected databases on a nightly basis. The databases are configured in Simple recovery mode and the maintenance plan cleans up backups that are older than 3 days.

The maintenance plan specifies that the backups be stored on the J:\ drive of our server and everything appears to work properly.

Our issue is that around the time the backup step of the maintenance plan finishes we have noticed that the K:\ drive on our server loses free disk space. This will continue until the drive is almost completely full and then the next backup will free up 50-100gb and it will repeat the cycle of taking up disk space and then freeing up the space.

We have used WinDirStat many times to try and find the file that are taking up the space on our K: drive and are unable to locate them. The disk usage appears to be directly associated with the SQL Server maintenance plan that runs.

Is there a way to verify that the backups are not accessing the K:\drive? I can't find any references to the K:\ drive in the SQL Server logs.

Thank you in advance for any assistance you can provide.

Josh

How to undo DROP TABLE statement?

Posted: 29 Mar 2013 01:06 PM PDT

I've accidentally executed DROP TABLE statement on a wrong table. I have backups that are several days old that I can use to recover table and most of the data but not all.

I know that SQL Server can't rollback already committed transaction but I was wondering if there are any alternative ways to get the rest of the data back. Any help such as some uncommon sql scrpts or anything like that would be greatly appreciated.

How to use array variable in query in PostgreSQL

Posted: 29 Mar 2013 10:02 AM PDT

Create table t1 ( xcheck varchar[], name text );    CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])    RETURNS record AS    DECLARE xrc as record;      execute 'select name from t1 where xcheck @> ''' || xch :: varchar[] || ''';' into xrc;   return xrc;  END;  

In table t1 having array value and text. I am calling this(fn_acgroup) function with array as its parameter value. In that function, I am checking the array value with passed array if it matches then I will return the name. In that case I get error as operator does not exist: text || character varying[]. Am I doing anything wrong? How can I solve this problem?

event scheduler not called

Posted: 29 Mar 2013 07:22 PM PDT

I had created one event scheduler which looks like this

mysql> show create event event1      -> ;  +--------+----------+-----------+--------------------------------------------------------------------------------------------+  | Event  | sql_mode | time_zone | Create Event  | character_set_client | collation_connection | Database Collation |  +--------+----------+-----------+-----------------------------------------------------------------------+----------------------+----------------------+    | event1 |          | SYSTEM    | CREATE DEFINER=`root`@`localhost` EVENT `event1` ON SCHEDULE EVERY 1 MONTH STARTS '2013-02-02 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN   update samp set col1 =col1  + 1; END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |  +--------+----------+-----------+-----------------------------------------------------------------------+----------------------+----------------------+---------  -----------+  1 row in set (0.00 sec)  

This events has not called on 1st of month. So i tried show processlist\g to find it is runnung or not; it showed following o/p

mysql> show processlist\g;  +-----+-----------------+-----------------+---------------+---------+------+---     | Id  | User            | Host            | db            | Command | Time | State                       | Info             |  +-----+-----------------+-----------------+---------------+---------+------+---  | 136 | event_scheduler | localhost       | NULL          | Daemon  | 1855 | Waiting for next activation | NULL    |  

so NULL in db col means that no DB is assigned to it?

Please help me to solve it.

Automatic database file creation based on database file size in SQL Server 2008 R2

Posted: 29 Mar 2013 07:53 AM PDT

Now i am using SQL Server 2008 R2 database for data logging. The name of the database profile is MASTERDB. That profile has one data file called MASTERDB.mdf and one log file called MASTERDB_log.ldf. In this database data are logging in every seconds. Here i wants to maintain
the data file size as 4GB. If the size excited the limit then i wants to create one new data file under the same profile. Is there any possible options available in SQL Server 2008 R2?.

Specify Server for DBMS_Scheduler Job in Policy Managed RAC

Posted: 29 Mar 2013 07:18 PM PDT

A unit test requires a dbms_scheduler job to run on the same RAC node as the unit test is being run from. I know that with an Admin managed database this could be done by creating a service that limited the available instances and then using that service in a job class the job uses. My question is, how can this be done in 11.2 with policy management?

Pools can be created that have only a single server in them and databases can be assigned to multiple pools, but as I understand it, a server can only be assigned to a single pool. Therefore, a service can't be created that uses a single server and still have other services that use a pool defined with multiple servers including that one.

I also know that services can be created as either SINGLETON or UNIFORM, but since SIGNLETON doesn't provide for allowed servers or even preferred servers, I'm not sure how this would help.

Surely I am missing something that makes this all possible.

multi-master to single-slave replication at table level with PostgreSQL or MySQL

Posted: 29 Mar 2013 09:57 AM PDT

Here is my scenario

Master1 hosting DB1  Master2 hosting DB2  ...  MasterN hosting DBN    replicate to:    Slave1 hosting DB1,DB2... DBN  

I've read similar questions and they recommend to start different instances at Slave1 and simply do MasterN-Slave1(instanceN) replication, as instructed here:

Single slave - multiple master MySQL replication

That would be piece of cake, but running different instances might be a waste of resources.

I really want to achieve this with an single DBMS instance at Slave1, and if possible with PostgreSQL; but can try with MySQL if there is a solution for this.

Any help is appreciated.

Does fast bcp replicate in sybase sql statement replication?

Posted: 29 Mar 2013 11:57 AM PDT

Is it possible to replicate fast bcp using sql statement replication in sybase?

What are the performance implications of creating an index in MySQL?

Posted: 29 Mar 2013 12:57 PM PDT

The MySQL reference guide doesn't (from what I see) detail the implications of creating an index; by this I mean the performance implications and any locks it may take on the table or columns against which is building the index.

If anyone could give me an idea as to what the implications are, it would be appreciated; what would be appreciated even more is a link to some documentation where I can answer this question for myself!

Regards,

Chris :D

SQL server enterprise vs standard

Posted: 29 Mar 2013 07:57 AM PDT

I am studying the difference between SQL server enterprise and standard. The one feature that I find beneficial from this page is:

http://www.microsoft.com/sqlserver/en/us/editions.aspx

Advanced High Availability (Multiple, Active Secondaries; Multi-site, Geo-Clustering)

How exactly does it ensure advanced high availability? Does anybody have any details?

Trouble setting up Nagios to monitor Oracle services

Posted: 29 Mar 2013 10:57 AM PDT

I've got an install of Nagios XI that doesn't seem to want to talk to any of my Oracle services here. I've pulled out the monitoring command and am running it manually, after setting ORACLE_HOME and LD_LIBDRARY_PATH of course, but it keeps generating the following error:

/usr/local/nagios # libexec/check_oracle_health --connect "oracle-server:1551" --username user --password "pass" --name OFFDB1 --mode tablespace-can-allocate-next --warning 20 --critical 30  CRITICAL - cannot connect to oracle-server:1551. ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA (DBD ERROR: OCIServerAttach)  

I'm still fairly new to Oracle, but my googlings seem to indicate that SERVICE_NAME should match the GLOBAL_DBNAME in listener.ora, which is OFFDB1. Do I need to do something else here like modify the connect string?

As a note, there are multiple instances of oracle sharing the target box, but each seems to be intalled to separate partitions and are running their own listeners or various ports.

innodb changing row format doesn't decrease table size?

Posted: 29 Mar 2013 07:57 PM PDT

We're currently using MySQL with innodb and we have some large tables that are compact in row format. When I change the row format to compressed we are still seeing the same size for the table. Anyone know the reason for this?

Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?

Posted: 29 Mar 2013 02:57 PM PDT

I have a table that has a large number of rows in it.

The primary key (an auto-incrementing integer) is, by default, indexed.

While waiting for a row count to be returned I did an EXPLAIN in another window and the the results were as follows:

mysql> SELECT COUNT(1) FROM `gauge_data`;  +----------+  | COUNT(1) |  +----------+  | 25453476 |  +----------+  1 row in set (2 min 36.20 sec)      mysql> EXPLAIN SELECT COUNT(1) FROM `gauge_data`;  +----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+  | id | select_type | table      | type  | possible_keys | key             | key_len | ref  | rows     | Extra       |  +----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+  |  1 | SIMPLE      | gauge_data | index | NULL          | gauge_data_FI_1 | 5       | NULL | 24596487 | Using index |  +----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+  1 row in set (0.13 sec)  

Since the primary key is guaranteed to be unique, can I just take the number of rows from the EXPLAIN and use that as the row count of the table?

BTW, I believe the difference in numbers is due to the fact that more data is continually being added to this table.

Database setup/design for multiple services

Posted: 29 Mar 2013 01:57 PM PDT

I am working on a new MySQL database that will store orders/customer information for around 15 different services. 7 of the services are similar in regards to the information that is stored with a few of the 7 services requiring an additional piece of information. There other 7-8 services are similar to each other as well but not as similar the the first 7. So my question is how should I break this down into a MySQL database?

Should each service have its own table? If this is true, what is the best way to link tables? Any advice is greatly appreciated!

Also, here is a sample of the type of info that will be stored:

enter image description here

Automate daily backup of database

Posted: 29 Mar 2013 10:46 AM PDT

I am trying to create automate/daily backup of database in SQL Server 2008 R2. I did bit of research and I get to know that as I am using Express edition; which doesn't come with Maintenance Plan, I can't create database backup automate/daily basis. So, the only possibilities are I have to use either TSQL or create Job. I don't have much understanding of SQL Job, so I am left with T-SQL only.

Can anyone explain me how to do automate backup of database in T-SQL using stored procedure or is there any other options I have to do that.

Regards.

What is connection time?

Posted: 29 Mar 2013 08:10 PM PDT

We are currently trialling ManageEngine SQLDBManager Plus. On the dashboard for a server it shows connection time in milliseconds (currently 15ms).

Can anyone tell me what Connection Time represents and/or what I could use this metric for?

We are monitoring a MS SQL Server 2005 database if that makes any difference.

Is there a quickish, straighforward way to measure Transactions Per Second on SQL Server 2000?

Posted: 29 Mar 2013 08:08 PM PDT

The sys.dm_os_performance_counters DMV introduced in SQL Server 2005 makes this task somewhat trivial. However, my Google-Fu has been blocked in finding a way to do this in SQL Server 2000.

How can I measure transactions per second in SQL Server 2000?

oracle format specifiers: to_number vs to_char

Posted: 29 Mar 2013 07:21 PM PDT

SQL> select TO_NUMBER(123.56,'999.9') from dual;  select TO_NUMBER(123.56,'999.9') from dual                   *  ERROR at line 1:  ORA-01722: invalid number      SQL> select TO_CHAR(123.56,'999.9') from dual;    TO_CHA  ------   123.6    SQL>  

I am having a hard time in understanding the nuances of Oracle SQL. For example, have a look at the two queries above.

Why does first query fail but second one succeed?

How do I find the median value of a column in MySQL?

Posted: 29 Mar 2013 05:49 PM PDT

I can only imagine doing this with two database queries. The first finds the number of rows in the database, and the second selects with an ORDER BY on the column I am interested in and LIMIT X, 1 where X is half the number of rows.

Is there a simple way to do this with only one query?

Right now I am using averages in my calculations, but I think the mean would be better; there is no upper bound to the values and they are bounded from below by 0.


EDIT: yes, I meant to say 'median' but was having some brain error & searched for 'mean'. I have now found the answer over at stackoverflow

Search This Blog