Saturday, September 28, 2013

[SQL Server 2008 issues] Need to migrate from oracle to sql server

[SQL Server 2008 issues] Need to migrate from oracle to sql server

Need to migrate from oracle to sql server

Posted: 27 Sep 2013 03:10 AM PDT

HI All,We have requirement to migrate a database from oracle to sql server 2008 .What is best method to do the migration?We are Planing to use SSMA tool for migration.Can any one tell me what are basic pre-requests and steps to follow?THanks in advance............

switch operation in data partitioning

Posted: 27 Sep 2013 12:13 AM PDT

How switch operation works in data partitioning internally.is it logical or physical operation.can it be done online.

Prevent backups on C:\ drive?

Posted: 26 Sep 2013 11:39 PM PDT

Can anyone - off the top of their heads - think of a way of preventing backups being taken to the C:\ drive?I've run a very quick google and didn't spot anything obvious. Nothing foolproof that doesn't risk interfering with other operations strikes me. This may be a slight case of FridayAfternoonHead which will leave me with egg on my face for asking, but I'll take that If you've got something, you can point and laugh, it's OK. :-)

Empty disk - what to do? (performance)

Posted: 27 Sep 2013 04:49 AM PDT

Hi guys,So a server has been performing badly for the last month+ & last night I created some missing indexes which improved the performance a lot. However I noticed that it had 2*RAID5 disks (both 10k rpm i think...they're 10k something) with the following split:RAID5 disk #1: software, pagefile, .mdfs, .ldfsRAID5 disk #2: nothingI guess it was added a while ago for the database ("Data" in the disk virtual name) but nothing got moved. So I was looking for opinions of what you'd suggest moving? I guess it's simply a matter of .mdf or .ldf? Or you'd move tempdb across too with the .ldfs?The performance boost of yesterday has pretty much given me free-reign on the server to experiment so other things I was planning to do:1. increase the cost of parallelism threshold from the default (cxpacket is the 2nd highest stat after the index change, after some wait type similar to IO_NETWORK).2. Initialise IFI3. Add extra .mdfs for tempdp (currently 1*4gb mdf)4. possibly add a couple more indexes depending on the results of my trace earlier today.I really want to see what effect the isolation level would have on how the wait types vary too although I'm not sure if they'd give the go ahead for that test (won't be this weekend at least) since I'd probably need to restore from backup if it proved ineffective...although for a largely OLAP system it should be better?The db is 2005 SEThanks for any feedback on my ideas :o // testing in prod since 2013

Conditional Formatting in SSRS Based on a Range of Values

Posted: 20 Sep 2013 08:07 AM PDT

I am building a backup status report in SSRS 2008. Here is a screenshot of what I have so far:[img]http://skreebydba.files.wordpress.com/2013/09/backupstatus.png[/img]I want to change the font color of the Backup Status to red if the status is FAILED using this conditional logic:=IIF(Fields!backupstatus.Value <> "SUCCESS", "Red", "Black")What I want to do now is change the font color of the Instance Group value to red if any of the Backup Status values in that group are FAILED. So in the screenshot above, DEVSQL08 should be displaying in red as well in the left-hand column.Any assistance would be appreciated.Thanks,Frankblog [url=http://skreebydba.com]skreebydba.com[/url]twitter [url=https://twitter.com/skreebydba]@skreebydba[/url]

Need to redirect .net app to another sql server

Posted: 27 Sep 2013 01:56 AM PDT

Hi,I'm dealing with a situation where one of our developers is compiling a component with the connection string to a SQL Server instance included in the compile. Is there a way to redirect the connection from the compiled connection to another SQL Server instance. Connection parameters including database and security remain the same. Basically we're looking for a situation where the compiled component can retain the same connection information, but have it actually connect to ServerB rather than ServerA.

convert string to date SSIS 2008 R2

Posted: 19 Sep 2013 03:41 AM PDT

hi all,I never found the SSIS 2008 R2 section so I am posting this here since it really is of somewhat general interest:how do I convert a date in string format into a string that can be converted to a date.If my string were YYYY-MM-DD HH:MM:SS.000 the conversion goes well.I know how to convert YYYYMMDD into the format above, however my date strings are M/D/YYYY and they are not even padded.Do you know how to parse M/D/YYYY and get YYYY-MM-DD HH:MM:SS.000 with the syntax of SSIS?I tried ISNULL(DATE) ? NULL(DT_DBDATE) : (LEN(DATE) == 0 ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(TRIM(DATE),FINDSTRING("/",DATE,2),4) + "-" + SUBSTRING(TRIM(DATE),1,FINDSTRING("/",DATE,1) - 1) + "-" + SUBSTRING(TRIM(DATE),FINDSTRING("/",DATE,1),FINDSTRING("/",DATE,2) - 1)))but there's something wrong and I can't find the mistake,Thanks,kowlasky

Time out occurred while waiting for buffer latch -- type 2. Need Help.

Posted: 10 Apr 2012 07:38 PM PDT

Hello,I hope somebody can help me, I am getting the followng error on my SQL Server 2008 R2 64bit machine.Time out occurred while waiting for buffer latch -- type 2, bp 00000007BEFFE000, page 1:1396234, stat 0x4c00309, database id: 7, allocation unit id: 72057594080591872, task 0x0000000007A31828 : 1, waittime 300, flags 0x1a, owning task 0x0000000004231AC8. Continuing to wait.This error follows a stack dump, I believe the transaction logs were full at the time this happened. If I look at sp_who2, I notice that there is a lot of Suspended DELETE commands for the database , there are about 20 under the same SPID number as well as 5 others under another SPID number. Should I kill these processes?I have tried running a DBCC CheckDB for this database but it fails, saying it could not get exclusive access and failed to take a snapshot.Not sure what to do here, google is coming up with similar messages, but they do not apply. Any help or advice would be very appreciated.UPDATE: I notice there is a spid number in the BlkBY column in SP_WHO2 and it appears to be blocking several others, is it safe to kill the blocking spid? The spid also appears 3 times in the SPID column and is a suspended DELETE command.Regards,D.

Using Alias in Where

Posted: 26 Sep 2013 08:41 PM PDT

Good DayI would like to restrict my records to distances that are under a certain amount (I handle this later with coding) but the problem is setting the limit in my SQL. I keep getting "Invalid Column Name" error for Distance.This is my original code:[code="sql"]Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,(SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) As Distance From Member WITH(NOLOCK) INNER JOIN FoodType WITH(NOLOCK) ON (Member.FoodTypeID = FoodType.FoodTypeID) Where Zoom >10AND Distance < 20000 AND MAct='Full' Order By Distance ASC[/code]I also tried:[code="sql"]Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,(SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) As Distance From Member WITH(NOLOCK) INNER JOIN FoodType WITH(NOLOCK) ON (Member.FoodTypeID = FoodType.FoodTypeID) Where Zoom >10AND Cast((SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) as int) < 20000 AND MAct='Full' Order By Distance ASC[/code]And I tried:[code="sql"]Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,(SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) As Distance From Member WITH(NOLOCK) INNER JOIN FoodType WITH(NOLOCK) ON (Member.FoodTypeID = FoodType.FoodTypeID) Where Zoom >10AND (SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) < 20000 AND MAct='Full' Order By Distance ASC[/code]Thank youGreatness... Live It!!

query issue

Posted: 27 Sep 2013 02:44 AM PDT

HiI have written a query and the output for the query isRun Date Fiscal Year Posting Period Port Fuel Oil Price Gas Oil Price20061031 2006 10 FOS NULL 578.520061031 2006 10 FOS 275 NULL20061031 2006 10 FUJ NULL 592.520061031 2006 10 FUJ 276 NULL20061031 2006 10 GEN 273 NULL20061031 2006 10 GEN NULL 568.520061031 2006 10 HOU 262 NULL20061031 2006 10 HOU NULL 537.520061031 2006 10 NYK 306 NULL20061031 2006 10 ROT NULL 472.5Query is select top 10 CONVERT( varchar(28),observation_dt,112) AS [Run Date],cast(year(applicable_dt)as varchar(28))as [Fiscal Year],cast(month(applicable_dt)as varchar(28)) as [Posting Period],cast(price_reference_cd as varchar(28)) as Port, (case r.price_type_cd when'FUEL OIL'then cast(quote_val as varchar(28)) END)as [Fuel Oil Price],(case r.price_type_cd when'GAS OIL'then cast(quote_val as varchar(28)) END)as [Gas Oil Price]from OSS_MARKET_PRICE_DAILY o inner join rd_market_price ron o.market_price_seq=r.market_price_seqwhere r.price_type_cd in ('FUEL OIL','GAS OIL') group by observation_dt,applicable_dt,price_reference_cd,quote_val,price_type_cd order by 1,2,3,4I want to get the values as Run Date Fiscal Year Posting Period Port Fuel Oil Price Gas Oil Price20130605 2013 9 HOU 599.985 574.1920130605 2013 10 HLS 597.975 573.519920130605 2013 11 SIN 596.3 572.8520130605 2013 12 STE 594.625 572.1820130605 2014 1 TRI 592.615 597.30520130605 2014 2 YOS 590.605 597.30520130605 2014 3 FOS 590.605 597.305Can any one pls let me know what to modify to get both prices values i one lineRegardsNaveen

Considerations for location of Reporting Services databases

Posted: 20 Sep 2013 08:30 AM PDT

Hello experts,I'm working on designing a SQL Server 2008 R2 Reporting Services (SSRS) topology, and a couple of my colleagues asked to see if SSRS can have its databases installed remotely, that is, separately from the other SSRS components. It looks like this is possible, but the question was also raised as to whether we could install it on our main OLTP db server. 1. My instinct tells me that this wouldn't be advisable for performance reasons, but is this feasible depending on the expected load for the SSRS installation?2. Also, does it cost more in licenses to have SSRS host its own databases, or is the cost of an SSRS database instance for the ReportServer and ReportServerTempDB databases included in the license for the main OLTP db server that doesn't have SSRS on it? For example, according to this page, scaling out SSRS seems to increase the cost dramatically.[url=http://www.networkworld.com/community/node/43349]http://www.networkworld.com/community/node/43349[/url]3. That same article also suggests that it is best to separate the web server for SSRS as well, meaning at least one more server for that component. Is this really the necessary best practice across the board, or can it depend on the size of the expected user base of SSRS users (in our case, something like 20-30 users)?Thanks for any help - I am in the middle of reading up on the related documents myself (for example here [url=technet.microsoft.com/en-us/library/ms157293(v=sql.105).aspx]technet.microsoft.com/en-us/library/ms157293(v=sql.105).aspx[/url] and here [url=http://technet.microsoft.com/en-us/library/cc966418.aspx]http://technet.microsoft.com/en-us/library/cc966418.aspx[/url]), but thought I would put this question out there in case someone happens to provide information while I'm researching.Thanks again.- webrunner

MSDTC config in cluster..

Posted: 18 Sep 2013 09:02 PM PDT

Hi All,Windows server 2008 r2I have to install sql 2008 r2 cluster for i need a help for msdtc config?Steps that i have followed:1)MSDTC configuration:i have configured msdtc instance in seprate group.2)install sql clusternow my dout is do we need to map the sql instance with MSDTC instance or not.?Please reply its urgent...

Restore backup error

Posted: 26 Sep 2013 09:57 PM PDT

Hi All,How are you doing with error messages like this one:Msg 3242, Level 16, State 2, Line 1The file on device 'D:\Backups\database.bak' is not a valid Microsoft Tape Format backup set.Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.I got a backup from a client but cannot restore it.I tried with CONTINUE_AFTER_ERROR as well, but same.Actually [code="sql"]RESTORE VERIFYONLY FROM DISK = N'D:\Backups\database.bak'[/code] gives out the same error.

rename MSDTC

Posted: 26 Sep 2013 10:03 PM PDT

Hi All,How to rename MSDTC in cluster 2008?Thanks

Importing from a improperly formatted excel file

Posted: 25 Sep 2013 06:39 AM PDT

Any guidance would be appreciated. Despite my protestations I have been tasked to import from an excel file. The file is not what I would call a proper data file. The data will start in cell D10 and end in cell Kx where x is the number of data rows in the file.What are my options here. My first thought would be to use SSIS and dump say cell A1 through K10000 ( assuming I would never have more than 10000 rows) into a staging table and parse the data out.Anyone been down this road...?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog