Tuesday, April 23, 2013

[SQL Server] Handling SQL Server Errors



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



SQLTeam.com Articles via RSS


SQLTeam.com Weblog via RSS



.

sqlteam.com

Posted by at No comments:
Labels: ,

[SQL Server] Create an SSIS package without tab's in the destination flat file..

[SQL Server] Create an SSIS package without tab's in the destination flat file..

Create an SSIS package without tab's in the destination flat file..

Posted: 23 Apr 2013 11:08 AM PDT

Hi Everyone, Am new to this form am sorry if am rude.I have to create an SSIS package by taking data from the flat file which consists of spaces and tab for example: 102 rtrtr yyutut juuy 6565 in which after 102, 3 blank 3 spaces and 1 tab. rtrtr as 4 spaces and 1 tab between rtrtr and yyutut. In my destination flat file i must get spaces but not tab.Thanks in Advance.

Query Help with Date and Time

Posted: 23 Apr 2013 03:18 AM PDT

HII am selecting from a table and setting a condition in the "Where" clause on a column that has a time and date stamp. When I use set the condition for a 20 min threshold for the time stamp I get data (which is expected)WHERE c.CTS_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND c.CTS_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) and a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) or a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE())But when I remove the 20 min threshold from the clause I get no data at all but I should get more data. Can anyone see what i am missing?WHERE c.CTS_LAST_SAVED_DATE = DATEADD(MINUTE,0,GETDATE()) and a.APP_LAST_SAVED_DATE = DATEADD(MINUTE,0,GETDATE()) or c.CTS_LAST_SAVED_DATE = DATEADD(MINUTE,0,GETDATE())I even tried removing the reference to time altogether and still get no data.WHERE c.CTS_LAST_SAVED_DATE = GETDATE() and a.APP_LAST_SAVED_DATE = GETDATE() or c.CTS_LAST_SAVED_DATE = GETDATE()Doug

backup permissions??

Posted: 23 Apr 2013 02:03 AM PDT

We have SQLExpress on one of our production servers. A lot of the information is sensistive so our server team had to lock it down pretty good. I had batch jobs running the nightly backups which were cut off because I needed to add the NetworkService account to the backups folder in the directory we chose. After I did that the Task scheduler is able to run the backup...here's where I'm a little confused... If I log onto the instance in ssms using the backup login I created and run a backup script to the backup dir... it runs fine and the backup is placed in the right spot. If I run the job from task scheduler or just the batch I created (which is using the same exact script as I am using in ssms) the backup runs fine, processes the same amount of pages as in ssms but the backup size is about half of what it is from ssms? Am I just missing permissions somewhere?

can any body explain normalization and types?/

Posted: 22 Apr 2013 06:50 PM PDT

can any body explain normalization and types?

Count instances across fields by date?

Posted: 22 Apr 2013 10:12 PM PDT

I need to search a table and count instances of a particular string across multiple fields by date, I've used the following Dynamic SP in the past to look through fields and was hoping I could adapt it. I've tried different ways to change it to Count with no success, or am I barking up the wrong tree and this isn't the way to do it? I would like to keep it Dynamic so I don't have to specify field names as I may use this on other tables.[code]declare @TmpName as varchar (2000)declare @TmpName1 as varchar (2000)declare @sdate as varchar (20)declare @svar as varchar (2000)declare @CMD Nvarchar(100)declare @Status1 as varchar(2000)declare @sSearchterm as varchar(50)set @sSearchterm = '%something%'set @sDate = '29/Apr/2013'DECLARE CUR1 CURSOR FAST_FORWARD FORselect COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'resource' OPEN CUR1 FETCH NEXT FROM CUR1 INTO @TmpName WHILE (@@FETCH_STATUS = 0) BEGIN SET @CMD = 'SELECT @Rtn = [' + @TmpName + '] from [resource]' + ' where [date] = ''' + @sDate + '''' exec sp_executesql @CMD,N'@Rtn varchar (200) out',@Status1 out print @Status1 FETCH NEXT FROM CUR1 INTO @TmpName END CLOSE CUR1 DEALLOCATE CUR1[/code]

Unable to run SSIS package built on 2005 under 2008

Posted: 22 Apr 2013 08:20 PM PDT

I hope Im on the right forum for this, if not please point this out to me.I have migrated a website from one server to another.On this new webserver SQL server express 2008 is installed.The website on the frontend works fine.However, the backend of the website contains some functionality with which one can upload a .csv-file to import some data into the database by running an SSIS package.Since the migration of the website to the new server this results in the following exception:"Retrieving the COM class factory for component with CLSID {BA785E28-3D7B-47AE-A4F9-4784F61B598A} failed due to the following error: 80040154"The cause of this problem, according to numerous sources, is the fact that this package was built under SQL Server 2005 and that its missing some Class with the above classGuid.Apparantly these were all renamed in SQL Server 2008.I have installed SQL Server 2005 on the said webserver, but the error persists. It seems that the .dll needed still isnt installed with SQL Server 2005.Also i have created a database running on SQL Server 2005 and created a second version of the website which uses that database. But still this does not resolve the problem.Can somebody please help me?Thanks in advance.
Posted by at No comments:

[SQL Server] Using Dynamic SQL in Stored Procedures



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



SQLTeam.com Articles via RSS


SQLTeam.com Weblog via RSS



.

sqlteam.com

Posted by at No comments:
Labels: ,

[MS SQL Server] NO. of instances

[MS SQL Server] NO. of instances

NO. of instances

Posted: 22 Apr 2013 10:07 PM PDT

can any one suggest me that how to find out the No. of instances in a server using query

weird dbid from sys.dm_exec_query_stats

Posted: 22 Apr 2013 06:53 PM PDT

Hi.If I execute the sys.dm_exec_query_stats I get back dbids like f.e. 32512 which I can't resolve with db_name. Which database is referenced here?

Excessive sp_executesql usage

Posted: 23 Apr 2013 01:34 AM PDT

My devs seem to use sp_execute even for the execution of SPs. Is there any downside to this dynamic model?

Multi-server Administration - Execute in master

Posted: 23 Apr 2013 01:20 AM PDT

Dear experts,I have implemented a multi-server environment. There is one master server (MSX) and three target servers (TSX).But I have a job that I want to execute in all four servers, either in the targets and in the master. Is there a way to do it? Obvisously I know that I can create a job in the Local Jobs, but then there will be two different jobs.Thanks in advance,

SQL Server 2008 (10.50.1600)

Posted: 23 Apr 2013 12:26 AM PDT

Good afternoonI have a database sitting on a SQL Server 2008 installation (10.50.1600). I have a need to take a backup copy of this database and restore it to a different SQL Server 2008 installation (10.0.5512), but when I try to do so using the Restore Database wizard I get the following error:[quote]System.Data.SqlClient.SqlError: The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.5512. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)[/quote]Is there a standard upgrade path that would get me from 10.00.5512 to 10.50.1600, and if so can someone tell me what it is? Alternatively, is it possible to back the database up in such a way as to be able to use that backup on the 10.0.5512 box?TIA

Capturing workload for analysis

Posted: 22 Apr 2013 07:04 PM PDT

Hi.I'd like to capture all queries executed against my database to be able to make the right index need decisions.I'm familar with Profiler and SQL Trace and also saw the possiblity to use extended events, but the optimal solution would be something that aggregates me the queries and writes out the repeatedly requeries only once, so I get an aggregated view.Would it be enough to analyse the sql_handles from the sys.dm_exec_query_stats_cache or is there maybe a DMV for that?I need to capture the workload for a long period of time, because same actions happen f.e. only monthly...Could anybody help me how I could realize it to get an overview over all queries for a long time, which aggregates the same queries and let's me order after tables or other measures?

CDC Capture Performance issues

Posted: 22 Apr 2013 05:04 PM PDT

Hi All,I have database which is 1.6 TB and enabled CDC on it with retention of 8 days. Over the week end we received huge delta volume in the form of Updates in base tables. So, out CDC is running 36 hours behind the real time. How would i figure out what is the appropriate retention period for CDC on that Database. Is there any thumb rule?
Posted by at No comments:

[Articles] Accelerating Your Career

[Articles] Accelerating Your Career

Accelerating Your Career

Posted: 22 Apr 2013 11:00 PM PDT

There are any number of ways to help your career move forward. Steve Jones talks about one way you might not have thought about today.
Posted by at No comments:

[SQL 2012] trends, alerting and solution help

[SQL 2012] trends, alerting and solution help

trends, alerting and solution help

Posted: 23 Apr 2013 01:49 AM PDT

hopefully i will be able to get across what it is i am trying to achieve and someone can then point me in the right direction.i have developed a database and SSIS package which is used to process and store monitoring information about a data centre. one of the items monitored is disc space. what i now need to do is trend out the disc usage so that then i can predict when certain disc usage threshold will be met. the place i am stuck is how to deliver this/report this to the team who requires this information. Any help on possible solutions would be grand. I'm not expecting someone to do the work for me as i am proficient in most of the SQL Server stack. its just this has me stumped because i have never worked with trends before.ThanksTUpdate i have tried excel 2013 using poverpivot and the trends do not appear to be persistent within the charts and when displayed its hard to work out the date the threshold is reached..

Show OLAP properties in Excel

Posted: 22 Apr 2013 11:18 PM PDT

Hi guys, using a tabular model, how do I get excel to show the OLAP property fielsd? When right clicking on a dim value and selecting 'show properties in report'any ideas?

SQL 2012 editions Question

Posted: 22 Apr 2013 11:52 AM PDT

HiWe are looking at moving to SQL2012 from 2008 standard edition. We are weighing up whether to go for the standard or BI editions, The only thing that really impacts on the decision is the use of Power pivot and publishing the spread sheets into SharePoint. Does the Standard version of SQL 2012 allow us to do this?ThanksNigel

PWAIT_MD_RELATION_CACHE and MD_LAZYCACHE_RWLOCK wait types

Posted: 22 Apr 2013 04:03 AM PDT

I'm hoping someone can give me some more information on either of these two wait types PWAIT_MD_RELATION_CACHE and MD_LAZYCACHE_RWLOCK. We started seeing these happen on queries that were accessing a table with 1,000 records, most of the waits were over a couple minutes. No blocking was occurring at the time, and other queries against the database were able to complete successfully. From doing a bit of research I was able to find this description on MSDN for PWAIT_MD_RELATION_CACHE: "Occurs during internal synchronization in metadata on table or index." This made me believe the issues was revolving around a large insert, update or delete that was occurring during that time, or manipulation of an index on the table. No records had been inserted or updated in the last 3 days, the indices were last rebuild 2 days prior to the issue. I've been unable to find anything in relation to MD_LAZYCACHE_RWLOCK.We are currently running Microsoft SQL Server 11.0.2100.60.Any help or ideas would be appreciated.
Posted by at No comments:

[T-SQL] Merge Statement over a linked server

[T-SQL] Merge Statement over a linked server

Merge Statement over a linked server

Posted: 24 Nov 2012 07:42 AM PST

Does anyone know a way of running a MERGE statement across 2 tables over a linked server?Getting the message "The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables."

Select statement using a max

Posted: 23 Apr 2013 01:04 AM PDT

Hi i hope someone can help mw out I have the following selct statement that is working fine Select Substring(Name,9,10) 'Desk' ,Convert(Int,BchCode) As 'bchCode' ,CallDate ,Max(CallTime) 'LastCall' ,Min(CallTime) 'FirstCall' ,Count(SummaryFlag) 'Outbound Attempts' from tbl_OutboundCallDataWhere CallDate between @startdate and @enddate And SummaryFlag In ('W') Group By bchCode,CallDate,NameI have been asked to add in the duration off the Last call (exists in tbl_OutboundCallData and call CallDuration) and im struggling if i just add in the duration then i have to add is into the group by element and this is returning all the records of duration and not just the one related to the Last CallHope that makes senseany help would be gratefully appreciated as im going mad trying to get this orted Thanks

UPDATE statement help

Posted: 23 Apr 2013 12:43 AM PDT

Morning guys,I need a lil help with an update query...I have a working table that has all these columns in it...two of the columns are CustomerID and IDAlias...When importing into the table it loads everything except the IDAlias column...I have this other working table that has CustomerID and IDAlias in it...This is where the alias' are populated...The thing is: I need to populate the first working table with the alias' for each CustomerID using the other working table that has the alias for each customerID...The statement that I have been doing :UPDATE TableASET IDAlias = (SELECT Alias FROM TableB INNER JOIN TableA ON CustomerID = CustomerID)I keep getting subquery returned more than 1 value and I understand that error...Im just confused on how to populate that alias field...

Difference between cursor and While loop

Posted: 22 Apr 2013 03:44 PM PDT

Hi all, Can any one explain me What is the difference between Cursor and While Loop? I have read about two concepts but end up with confusion..Which one gives better performance??

Order By in Sql Server

Posted: 22 Apr 2013 08:56 PM PDT

Hi All,In a table i want to apply order by. My database is 20-20 and table is errorreport where I have only 4 columns (rpt_id, error_id, file_id, errorlino). I have written the below query and it is working fineselect * from errorreportwhere file_id=605 order by error_id descthe result is like belowrpt_id error_id file_id errorlino46 95 605 147 46 605 248 41 605 349 30 605 4But I don't want this. I want the list like belowrpt_id error_id file_id errorlino46 [b] 95[/b] 605 147 [b]30 [/b] 605 248 [b]41[/b] 605 349 [b]46 [/b] 605 4I want to set the asc or desc order. Is it possible to do in SQL?Please help!

Distinct Query Help

Posted: 22 Apr 2013 11:41 AM PDT

I HAVE DATA LIKE THIS ID DATE PID 691935 2012-05-11 15:32:09.377 00071015523691935 2012-05-11 00:00:00.000 00093063801691935 2012-05-11 15:34:37.147 00093103993691935 2012-09-19 11:27:55.420 00093715310691935 2012-11-16 15:28:21.843 00093715410691935 2013-03-08 15:19:53.013 00093720210691935 2013-03-08 15:19:22.867 00093721401691935 2012-07-13 00:00:00.000 00247035330691935 2012-07-13 15:53:21.343 00247035430691935 2013-03-14 13:50:01.803 00247181304691935 2013-03-14 00:00:00.000 00247196500691935 2012-10-12 00:00:00.000 00456132100691935 2012-05-11 15:32:36.580 51079099720691935 2012-05-11 15:31:38.957 53489046910691935 2012-10-12 13:51:39.530 63739013701 HOW I CAN use this logic here in the above dataI ID,PID of MAX(DATE) The end result should beID DATE PID691935 2013-03-14 13:50:01.803 00247181304 Here is my codeSelct DISTINCTID,MAX(DATE) MAX_DATE,PIDFROM MytableGroup by ID,PID I am not getting what I want. Any help would be great appreciate.

T-SQL Rename Multiple Tables

Posted: 22 Apr 2013 01:09 PM PDT

I am looking to rename a lot of tables using a wildcard,for example, the table names are:OrderItemOrderItemsOrderItemssI would like them to be renamed to:OrderItem2OrderItems2OrderItemss2Can this be done using a wildcard so I don't have to specify the full table name? Thanks.

Interview Questions

Posted: 22 Apr 2013 02:54 AM PDT

Hi I am facing following interview questions. table i am having one column .That column records like following SATHEESHKUMARARUN[b]rahul[/b][b]s[/b]URYAS[b]elvi[/b]Now i want following results.whever i am using lower case letter.please help merahulsURYASelvi
Posted by at No comments:
Subscribe to: Comments (Atom)

Search This Blog