Tuesday, October 1, 2013

[SQL Server 2008 issues] deleting all data\logins\views

[SQL Server 2008 issues] deleting all data\logins\views

deleting all data\logins\views

Posted: 30 Sep 2013 02:55 AM PDT

What is the best way to remove all data/views/logons from a database ?The scenario is this: We have a database called 'X' to hold out client data and to interact with our in house applications. Each customer has their own server with database 'X' installed. The database on each of the servers is the same format, that is, it has the same table structures/stored procs.I have to create a new database 'X' on a new server for a new customer - so I have restored one of our current clients database 'X' backups to the new server and now have the database but obviously with data that i need to get rid of completely whilst keeping the table structures, constraints, seeding values (if any present ). Also this has restored incorrect users under the Security\Users folder of the database. Do i just manually delete these ?I have used the following to disable referential integrity, truncate tables, and then renebale referential integrity[code="sql"]- disable referential integrityEXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' GO EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' GO - enable referential integrity again EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' GO[/code]If anybody can offer any advice of the above topic I would to hear from you ! :-)

Send SQL Server Dashboard reports via database mail

Posted: 13 Sep 2012 05:57 AM PDT

Hi All,Is this is possible to enable a job, which sends us the dashboard reports of a server thru mail ?Thanks in advance

SQL to list the headers in a table?

Posted: 22 Nov 2011 10:44 AM PST

Does anyone know offhand the SQL to list all the headers in a specified table?Thank you!

Error Message: BACKUP failed to complete the command BACKUP LOG msdb

Posted: 30 Sep 2013 02:20 AM PDT

Hi,I am getting following error message daily into my error log.I am running FULL Backup, Diff Backup an T-Log Backup.This message is every morning early I see and looks like during same time my T-log backup job also running.I have also checked that MSDB is in SIMPLE recovery mode and it's not part of my T-Log back up, it's only part of FULL BAckup - weekly.ERROR:BACKUP failed to complete the command BACKUP LOG msdb. Check the backup application log for detailed messages.

SSIS source file with time in the name

Posted: 26 Sep 2013 06:18 AM PDT

Hi everyone.I have been tasked with building some SSIS packages to load data from a csv into the database each day. There are four file sources. Three of them are very simple because the file name is source_date.csv. The file name is predictable so creating an expression that get the current day's file is easy. One of the files is source_date_time. Because the time stamp is always a few seconds different, I don't know how to create an expression to get the file by name and load it.What do people do when they need to have their package go out and grab the day's file if there is a time stamp in the name leading to an unpredictable file name?

Can you please let me know the link to download the tool named as - DBHammer?

Posted: 30 Sep 2013 06:08 AM PDT

Can you please let me know the link to download the tool named as - DBHammer?

Transfer Data

Posted: 30 Sep 2013 04:51 AM PDT

Howdy everybody,What is the best way to transfer 20GB in 3.122 km(three thousand kilometers) distance?1. Backup direct to target2. Backup local and copy to target3. Data Import/Export....what yours suggest??

How to DEFINE REGEX for CSV FILE

Posted: 30 Sep 2013 03:29 AM PDT

Im transfering data from csv file and it is fixed format and only date changes. so i want to split the date and name to endter into table20130930_Get_MY File Name.csvSo in table should enter like belowdate_name :20130930file name: Get_MY File Nameanyone have idea.regardsShuan..

How to run multiple stored procedures in parallel?

Posted: 30 Sep 2013 01:56 AM PDT

I have a stored procedure which runs in database DB1This stored procedure will have other stored procedures inside it which should run in parallel.Create procedure db1.proc1AS use db2call proc2use db3 call proc3 use db4 call proc4 Is there a way to run this stored procedure in parallel and even if the execution of call proc2 fails other two should run?Thanks

Trouble with nested CTE

Posted: 30 Sep 2013 03:27 AM PDT

I need to create a query that is comprised of subqueries, I am using a CTE, but one of the subqueries also is using a CTE...Can you nest CTE like this??[code="plain"]WITH CTE_Results AS(SELECT CASE WHEN HISTORY >= 2 AND ELA >= 4 AND MATH >= 4 AND SCIENCE >= 3 AND FL >= 3 AND VA >= 1 AND Prep >= 0 THEN 'Yes' ELSE 'No' END AS [On Target?] FROM ( SELECT COUNT(CASE WHEN CRS.U1 = 'A' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS HISTORY, COUNT(CASE WHEN CRS.U1 = 'B' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS ELA, COUNT(CASE WHEN CRS.U1 = 'C' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS MATH, COUNT(CASE WHEN CRS.U1 = 'D' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS SCIENCE, COUNT(CASE WHEN CRS.U1 = 'E' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS FL, COUNT(CASE WHEN CRS.U1 = 'F' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS VA, COUNT(CASE WHEN CRS.U1 = 'G' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS Prep FROM dbo.CRS INNER JOIN dbo.HIS ON CRS.CN = HIS.CN INNER JOIN dbo.STU ON HIS.PID = STU.ID WHERE STU.ID = 4068968) AS derivedUNION ALLWITH cteSource(CN, U1)AS ( SELECT r.CN, r.U1 FROM dbo.SSS AS s INNER JOIN dbo.STU AS t ON t.SN = s.SN INNER JOIN dbo.CRS AS r ON r.CN = s.CN WHERE t.ID = 4068968 UNION ALL SELECT r.CN, r.U1 FROM dbo.HIS AS i INNER JOIN dbo.CRS AS r ON r.CN = i.CN WHERE i.PID = 4068968)SELECT CASE WHEN p.A >= 6 AND p.B >= 6 AND p.C >= 6 AND p.D >= 6 AND p.E >= 6 AND p.F >= 6 AND p.G >= 6 THEN 'Yes' ELSE 'No' END AS [On Target?]FROM cteSource AS sPIVOT ( COUNT(s.CN) FOR s.U1 IN ([A], [B], [C], [D], [E], [F], [G]) ) AS p;SELECT CONVERT(VARCHAR(5),SUM(CASE WHEN [On Target?] = 'Yes' THEN 1 ELSE 0 END)) + '/2'FROM CTE_Results[/code]

listing of months for a particular year

Posted: 26 Sep 2013 12:52 AM PDT

Dear All,Hope you are doing fine. What am I trying to do is get a stock count for each item per month for a particular year. I am trying to do a report on that.Table1: list all Purchase OrdersPoNum Date P001 2013-01-01 P002 2013-02-01 P003 2013-02-10P004 2013-03-01Table2: list items for each PoNumPoNum ItemRef QtyP001 I0001 10P001 I0002 5P002 I0003 15P003 I0003 20P004 I0003 5is it possible to have something like that?Year 2013Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec I000110 0 0 0 0 0 0 0 0 0 0 0I0025 0 0 0 0 0 0 0 0 0 0 0I0030 35 5 0 0 0 0 0 0 0 0 0Basically, I will need each item's qty per month for a year.I came up with something. But I have the item's qty for the month the items are in only. How do I get it 0 for the other month?I was reading on creating a CTE to list the months of the year. But I am stuck.Please advise.Thanks,Ashley

Passing external sproc params to internal variables - Explanation?

Posted: 30 Sep 2013 02:11 AM PDT

Sometimes I find the reason behind a poorly performing stored procedure lies in passing the external parameters to internal variables and then using those internal variables instead of referencing the external parameters.Can anyone explain why this is happening, or point me to some microsoft technet article on the subject? I need to explain this to the rest of my team, but I don't know exactly what's going on. I just know of the problem and solution. hah!

NOdeA is crashed ..on ctive active cluster.

Posted: 30 Sep 2013 02:56 AM PDT

Hi,We have active active sql 2008 r2 running on Win 2008 r2 cluster.Instance_A is running on Node1 Instance_b is runing on NOde2Due to Os issue , NOdeA Crashed and Wintel Team want to re-install the O.S and cluster on NODEA only.While building the SQL server in initial stage we have followed the below way:For Instance A, we have ran the Main setup on NodeA and then added node on NOdeBFor Inctanse B, we have ran Main setup on NodeB and then Added Node on NodeANOw Node A ,is down both sql i running on Node B.If Node A is re-building then ,how to add this node to sql instance ?As per sql prospect how we have to do installation and what are steps we need to follow.Please help me. It is very urgentYour help much appreciated...Thank you in advance....

SQL Server Database Backup Monitoring

Posted: 29 Sep 2013 11:53 PM PDT

Hi SQL Masters,Greetings!Do you have a script that will determine if the backup failed/succeeded?Sample output:Database Name Status Log Date--------------- ---------- --------------AdventureWorks2008 FAILED 20130125AdventureWorks2008 SUCCEEDED 20130126Something like that.....Any ideas SQL Masters?Thank you and Best Regards,

NUMA

Posted: 26 Jun 2012 09:46 PM PDT

In preparation for my MCITP exam tomorrow I have been reading through the books and just crossing the t's and dotting the i's. One thing which I just want to get right in my head is NUMA as I had a feeling that I had the right answers then changed them to wrong answers so this is more of a confirmation than an actual question.1. SQL ignores NUMA when Hard-NUMA is <=4 CPUs and at least 1 node has only 1 CPU.So.....1 physical proc with 4 cores, then SQL will ignore NUMA2 physical procs with 2 cores, ignore4 physical procs with 1 core ignoreAnything other than the above then SQL will use Hard-NUMA as long as its not interleaved memory configured[i]This is the one mainly confusing me[/i]2. Use typical use for Soft-NUMA is when there is no Hard-NUMA, but can be used to split Hard-NUMA into more NUMA nodes3. Soft-NUMA doesnt provide memory to CPU affinity4. Soft-NUMA can increase performance in relation to I/O as each NUMA node creates a new I/O path and new LazyWriter thread5. Instead of doing point 4, you could CPU affinity instead to spread workload across multiple CPU's6. Use SSCM to configure port to NUMA affinityAnd now to cross my fingers and hope tomorrow is a good day.

Using CASE Condition for Prefixing a field from a table

Posted: 30 Sep 2013 12:38 AM PDT

Hi All,I want to retain the same Employee Code when an employee is rehired and prefix with A,B,C based on no of times the employee has been rehired with CASE statementCASE WHEN LEFT(EMPLOYEECODE,1) = 'Z' THEN 'A'+EMPLOYEECODE ELSE CASE WHEN ISNUMERIC(LEFT(EMPLOYEECODE,1)) = 1 THEN 'A'+EMPLOYEECODE ELSE CHAR(ASCII(LEFT(EMPLOYEECODE,1))+1)+SUBSTRING(EMPLOYEECODE,2,99) END ENDand it is working fine with these parameters : Employee Code is 'A10010' then its returning B10010 and when it is 10010 it is returning A10010 which is correct but the challenge comes when the employee code is Z10010 then it should return AA10010 not AZ10010 ....how can i do that?? help

TSQL Recomile Problems when using subqueries

Posted: 29 Sep 2013 10:02 PM PDT

Good Day AllI have found a problem with a Simple TSQL script that is returning incorrect results due to a schema change that didn't cause the old plan to recompile.I will just post how to duplicate the problem and hopefully people can tell me if it is intended to function this way.You have an Instance of SQL, on that instance you have 2 Databases, TestDB1 and TestDB2.You have a table in each of these databases that are exactly the same (At the start).You have a query to select an ID that is in TestDB1 where the values exist in [TestDB2]. eg. Select AccountID from TestDB1.dbo.Table1 where AccountID in (Select AccountID from TestDB2.dbo.Table2)The above query runs and the expected results are returned.. all is wellThen someone changes the TestDB2.dbo.Table2 Column name changes from AccountID to AccountIDArchive (No idea why it changed but it does)At this point i would assume the schema change should invalidate the plan so that if you run the original query with the AccountID being specified in the subquery it would result in a error.. column cannot be found.If you run the subquery on its own, it does return an error but if you run the Original query Select AccountID from TestDB1.dbo.Table1 where AccountID in (Select AccountID from TestDB2.dbo.Table2)It does not give you an error, and runs successfullyObviously the Origional queries subquery had to be changed to AccountIDArchive to accommodate for the change but i was surprised to see it still using the old plan and that an invalid TSQL code could execute "successfully", or generating a working plan.Using option recompile with the origional query does not cause the query to error out but if you do change the subquery to a new invalid column name it does error outAny opinions to prevent this or explanation as to why this is happening would help :-)Regards
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog