Saturday, May 18, 2013

[SQL Server 2008 issues] Format the Amount

[SQL Server 2008 issues] Format the Amount

Format the Amount

Posted: 17 May 2013 05:02 PM PDT

Dear,My client requires to add a hyphen(-) in lieu of comma(,) in the field of amount. But client requires only one hyphen(-) in the amount. For example, Total price is 10,000,000 but new requirement is 10-000000.Say if I execute "select amount from mytable". The result is '10-000000'. Here I need to do some formatting. But I have no idea.Please help me to sort out the problem.Rgds,Akbar

Maintenance Plan backup failing for one database only

Posted: 17 May 2013 03:47 AM PDT

I have a maintenance plan which is set to backup all databases on the server to a network path D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups. In this path, it creates a folder by the name of the database it is backing up, and then puts the .bak file in there. So for e.g. I have: D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups\db1\DB1.bak, D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups\db2\db2.bak D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups\db3\db3.bak etc. The maintenance plan has been running successfully with no issues for a few months. Recently, I created a new database "db10". The maintenance plan is unable to backup the database. It is able to create the folder with the database name, so I do see D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups\db10 but there is no .bak file in there. The error is cut & pasted exactly as below : Executing the query "BACKUP DATABASE [db10 ] TO DISK = N'D:\\Prog..." failed with the following error: "Cannot open backup device 'D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\SQLBackups\\db10 \\db10.bak'. Operating system error 3(The system cannot find the path specified.).BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.It seems to be a permissions/path issue, but I need some pointers on how to proceed and where to look. thanks!

space issue

Posted: 16 May 2013 09:02 PM PDT

We donot have space in database ,and the disk is fulled because of mdf's . how to release space on the drive

Find date gaps

Posted: 17 May 2013 03:32 AM PDT

Hello everyone,I need to find a way to fill gaps in case a week is missing, example each record has Begin_Date and End_Date, and some records can have multiple Begin_Date and End_Date but they should be continuation of the previous week. Example :Row_Nbd Account_Nbr Account_Name Account_Desc Begin_Date End_Date1 1 test1 chk 1/1/2012 3/1/20122 1 test1 chk 3/2/2012 6/30/20123 1 test chk 8/2/2012 12/31/2012as you can see there us a missing date range right above row_nbr 3 which (7/1/2012 to 8/1/2012) how can find the missing week?Thanks

Update values for dynamic columns

Posted: 17 May 2013 09:42 AM PDT

I've been looking at dynamic SQL update statements and have been trying to tailor one specific to what I need.I have a table that needs updating, one row for each route. There are 2 values that need updatedfor each route, however, the columns that need updating are different for each record that needs updating.I have a query which can pull the 2 column names that need updating for each route/record.I have the part about setting the update statement to a string, (which i hope is right):[code="sql"] DECLARE @sql NVARCHAR(500) SELECT @sql = 'UPDATE #Summary SET [' + @StartTime + '] = 0 , [' + @EndTime + '] = 0 FROM #Summary WHERE route = @route' EXEC sp_executesql @sql[/code]At this point, is it just a matter of setting up a loop to take the values (route, startime, endtime) from table 1 and put them in a table variable to loop through and set variables for those values, which can be used in the update statement above?This is what i have:[code="sql"]DECLARE @Route INT, @Starttime VARCHAR(5), @EndTime VARCHAR(5) DECLARE @RouteLunches TABLE (pk SMALLINT Primary Key IDENTITY(1,1) ,Route INT ,LunchStart VARCHAR(5) ,LunchEnd VARCHAR(5) )BEGIN INSERT @RouteLunches SELECT route ,LunchStart ,LunchEnd FROM [View_Route_Scoring]rs WHERE LDate = 20130401 AND rs.ProviderID = 10 END --***************************************************DECLARE @counter INT ,@numrows INT,@maxrows INT SET @Counter = 1SET @numrows = (SELECT Count(*) FROM @RouteLunches)SET @maxrows = (SELECT MAX(pk) FROM @RouteLunches)--*************************************************** WHILE (@counter <= @maxrows) BEGIN SELECT @Route = (SELECT Route FROM @RouteLunches WHERE pk = @counter) SELECT @Starttime = (SELECT LunchStart FROM @RouteLunches WHERE pk = @counter) SELECT @EndTime = (SELECT LunchEnd FROM @RouteLunches WHERE pk = @counter) BEGIN DECLARE @sql NVARCHAR(500) SELECT @sql = 'UPDATE #Summary SET [' + @StartTime + '] = 0 , [' + @EndTime + '] = 0 WHERE route = [' + @Route + ']' EXEC sp_executesql @sql END SET @counter = @counter + 1 END [/code]Does the syntax look about right?I'm getting an error:Conversion failed when converting the varchar value 'UPDATE #Summary SET [07:30] = 0 , [07:45] = 0 WHERE route = [' to data type int.What did I miss?

Best way to find certain records.

Posted: 17 May 2013 08:48 AM PDT

I have to find certain data from a table based on date range & and activity.So, if a records is 60 days old for a certain activity and do not fall within 90 & 120 days for other activity and do not have anything less than 60 days as create date, we should delete it.The script is to crete table & data is as below.So from the given example we have to delete only client id 5.[code="sql"]Create table TestPurgeClient(ClientId int,ActivityId int,CreateDate Datetime)--select * from TestPurgeClient--Allinsert into TestPurgeClient values(1,72, '2013-03-18 00:00:00.000')insert into TestPurgeClient values(1,33, '2013-02-16 00:00:00.000')insert into TestPurgeClient values(1,34,'2013-01-17 00:00:00.000')-- 60 day + Currentinsert into TestPurgeClient values(2,72, '2013-03-18 00:00:00.000')insert into TestPurgeClient values(2,72,GETDATE())--60 + 90 dayinsert into TestPurgeClient values(3,72, '2013-03-18 00:00:00.000')insert into TestPurgeClient values(3,33, '2013-02-16 00:00:00.000')--All + currentinsert into TestPurgeClient values(4,72, '2013-03-18 00:00:00.000')insert into TestPurgeClient values(4,33, '2013-02-16 00:00:00.000')insert into TestPurgeClient values(4,34,'2013-01-17 00:00:00.000')insert into TestPurgeClient values(4,34,GETDATE())--60 Day Onlyinsert into TestPurgeClient values(5,72, '2013-03-18 00:00:00.000')[/code]

query joining multiple tables getting duplicates- how to stop

Posted: 17 May 2013 02:58 AM PDT

I'm joining several tables and when I add the last one I get duplicate results. How can I get just one for each? Thanks for any ideas.select a.field, b.field, c.fieldfrom atblname as a inner join btblname as b on a.id = b.parent_idleft outer join ctblname as c on a.id = c.parent_idThere are more than one result when joining tbl a and c, but I'm getting a reult for each of them for all results from joining a and b.

Understanding complex SQL scripts

Posted: 17 May 2013 04:56 AM PDT

Hi guys. I am hoping for some general tips on how you get to the point of feeling comfortable running provided SQL Server scripts on my databases. Any kind of code review practices would be helpful.When we update our production systems with SQL Server scripts provided by application developers, I always try to scan through them to understand as best as I can what they are doing so that I feel confident that nothing is going on that seems wrong.How much time do you generally spend reviewing scripts before running them?Sometimes the scripts are huge and complex and I don't always follow what they are doing. Is getting an estimated execution plan before running them a good way to get a feel for what will happen?Clicking the parse button catches typos in code but does not alert me if an object exists. For example if DBName_Test was not changed to DBNAME_prod on accident and then the script fails and we have to restore from backup and start all over again.Are there any good tools for examining and understanding SQL scripts before running them? Any suggestions for avoiding issues would be valuable.Thanks much,Howard

SSIS format Excel worksheet with script task

Posted: 17 May 2013 05:31 AM PDT

I'm exporting to an Excel file and then formatting the spreadsheet. Using SQL2008R2. When I run the package from the designer it runs successfully and formats the spreadsheet correctly.But after execution, I still see Excel running in background.I have an application.Quit(). Am I doing something wrong?Here's my script Task code:[code="vb"] Public Sub Main() ' ' Add your code here Dim oBook As Object Dim oSheet As Object Dim oXLS As Object Dim oFile As Object oFile = "\\MyHouse\MyFilePath\MyFile.xls" oXLS = CreateObject("Excel.Application") With oXLS oBook = .Workbooks.Open(oFile) End With oSheet = oBook.Worksheets("KHS_Case_Master") With oSheet .Range("KHS_Case_Master").Font.Name = "Arial" .Range("KHS_Case_Master").Font.Size = 10 .Range("A1", "FZ1").Font.Bold = True End With oSheet.Activate() With oBook .save() End With oXLS.Quit() oXLS = Nothing oBook = Nothing oSheet = Nothing oFile = Nothing ' Dts.TaskResult = ScriptResults.Success End Sub[/code]

Trigger worked in 2008, but not in 2008 R2

Posted: 17 May 2013 04:37 AM PDT

We recently upgraded from 2008 to 2008 R2. The triggers worked in 2008, but after upgrading to 2008 R2, the triggers throw errors in the program. This did not happen in the past. The error that is received:Error Code: 14607Error Message: SQLSTATE = 42000Microsoft SQL Native Clientprofile name is not valid

SQL Server 2008 - Automatic Job Doesn't run

Posted: 16 May 2013 11:51 PM PDT

Hi all,All the jobs were running fine except for the past two days. I don't know what happened. Two days before my server date was changed automatically(from 2013 to 2032) after that it seems to be not working.Kindly help me . I am not able to find exact reason also.Thanks,Devanand.

Interview Questions i am not able to answer

Posted: 17 May 2013 03:44 AM PDT

1. what type of pages are used in bulk log recovery model.2. able to ping the server. but getting access denied error, what might be the reason3. temp db is growing in size what might be the reason4. what is open transaction5. does sql starts if model database is corrupted. if so how to bring it to normal operation.

orders over more than one partition question.

Posted: 17 May 2013 02:05 AM PDT

Situation, I do not have the power to change the situation, but want to understand the situation.Orders normaly belong to single clients, the database is partioned on clients, so most orders also follow the same partitioning.Except for orders which belong to multiple clients, they can be spread over multiple partitions.So a general template for accessing orders is:Create a temptable with the orders and their partioning in the table. With any access now the table is used in the where clauses.This code is used for all sitations, also for the situations when there is only a single partition for the order (far over 98 percent of the time), so there is always a temptable which holds that order for that single partition (a single row in the temptable).This also works similar for orderlines, ordercomments, orderstates etc.How does partitioning handle this situation?(The code works, that is not the problem).Is there an issue with this code?Can the optimizer handle this kind of code efficiently?Thanks for remarks, suggestions and your time,Ben Brugman

Block reporting from a database

Posted: 16 May 2013 11:54 PM PDT

We have a production OLTP database and a copy of that database to be used for reporting, since this is a internal application that all users access, all users have access to both databases. I am having a problem that certain users are mining the data using Crystal Reports and Office Products and causing extensive blocking that is in effect halting the use of the application. Marketing attempts to tell the reporting users to use the reporting database have been ineffective. Is there a way I can block access to the prod database from all applications except SSMS and the expected application?Thank you,

Parent child relationship in separate tables

Posted: 16 May 2013 10:59 PM PDT

Hello everyone,We are having data with parent child relationship. But they are spread across two tables due to our Business Logic. So overall the data is of following typeParentTableId | Name-----------1 | Parent12 | Parent23 | Parent3ChildTableId | ParentId | Name--------------------1 | 1 | Child1of12 | 1 | Child2of13 | 2 | Child1of24 | 3 | Child1of3Now, I want to create an SQL Query that returns me data in following type.ID | ParentId | Name--------------------1 | NULL | Parent12 | 1 | Child1of13 | 1 | Child2of14 | NULL | Parent25 | 4 | Child1of26 | NULL | Parent37 | 6 | Child1of3It would be very helpful for me if someone can help please.Thanks,K

Check values whic are not Decimals

Posted: 16 May 2013 11:50 PM PDT

HI,I have a column which is defined as Decimal(23,10).I want to get the values from the column which are not decimals.Kindly provide a solution ,

Hour not showing correctly

Posted: 16 May 2013 10:02 PM PDT

--Question 4b selectHistory_Type_,TimeClicked,left(right(TimeClicked,11),2) as HourTimeClicked,count(left(right(TimeClicked,11),2)) as CountOfHourTimeClickedfrom clicktracking_ --with (nolock)inner join members_ on clicktracking_.MemberID_ = Members_.MemberID_group by History_Type_,timeClicked,left(right(TimeClicked,11),2)OPN 2011-09-24 18:38:00 01 69OPN180 2013-04-02 12:41:00 01 1OPN90 2013-04-11 18:18:00 01 1OPN180 2011-09-26 13:08:00 01 13OPN90 2011-07-05 11:01:00 01 17the hour is always incorrectly 01 which is wrongplease can someone help?

What TOOLS do you include in your production SQL builds?

Posted: 14 May 2013 07:43 AM PDT

Where I work, once a server is "in production" I can't make *any* changes like installing software, not even stored procedures, or a DBA database. Nothing. (not without going through a terrible approval process, and then the answer is almost always NO.)But what I can do is include such software/scripts when I write my build documents. No one actually ever reads them, so that's how I can sneak in basically whatever I want. The document is approved, I build the production server, and presto! My tools are included. (I can still run ad-hoc SQL queries, by many of the more powerful tools are SPs or Jobs, etc)So here are some of the "tools" I do/will include in all future builds. Do y'all do something similar? [u]What do you include in your builds?[/u][b]Who is Active?[/b]Who is Active? is a comprehensive server activity stored procedure based on the SQL Server 2005 and 2008 dynamic management views (DMVs). Think of it as sp_who2 on a hefty dose of anabolic steroids...... [url=http://sqlblog.com/files/10/default.aspx]http://sqlblog.com/files/10/default.aspx[/url][b]SQL Server 2008 Diagnostic Information Queries[/b][url=https://sqlserverperformance.wordpress.com/]https://sqlserverperformance.wordpress.com/[/url][b]Detect Worst Performing SQL Queries[/b]...how to detect worst performing sql queries which is slowing down Microsoft SQL Server...[url=http://www.sqlfeatures.com/2012/01/29/detect-worst-performing-sql-queries/]http://www.sqlfeatures.com/2012/01/29/detect-worst-performing-sql-queries/...[/url][b]A Better sp_who2 using DMVs (sp_who3)[/b]The following code generates the same information found in sp_who2, along with some additional troubleshooting information. [url=http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3]http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3[/url][b]Ola Hallengren - SQL Server Backup, Integrity Check, and Index and Statistics Maintenance[/b]The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.[url=http://ola.hallengren.com/]http://ola.hallengren.com/[/url] [b]Microsoft® SQL Server® 2012 Performance Dashboard Reports [/b] (Google the 2005 version if you need it, which I think works with 2008)The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio. [url=https://www.microsoft.com/en-us/download/details.aspx?id=29063]https://www.microsoft.com/en-us/download/details.aspx?id=29063[/url]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog