| 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? |
