Saturday, July 27, 2013

[SQL Server 2008 issues] Convert Excel Formula to SQL code

[SQL Server 2008 issues] Convert Excel Formula to SQL code

Convert Excel Formula to SQL code

Posted: 26 Jul 2013 10:23 AM PDT

How do I convert this excel formula to SQL code?=IF(T2<R2,T2*O2,MIN(T2-R2,S2-R2)*O2)Thanks,Helal

How to do mathmetical calcualtaion in SQL

Posted: 26 Jul 2013 04:41 PM PDT

Dear friends,need your help- calculation - round to closest integer:(((Monthly Revenue field - Monthly Cost field )/Monthly Revenue field) * 100)--> how to do this please?thanksDJ

Query taking non-consistent time to execute

Posted: 22 Jul 2013 08:19 PM PDT

I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again executed with in 11 sec.Then i tried removing cast used in query and when executed it ran just in 8 seconds.But when i again the original query(with cast) it takes less time.I checked in several computers running the query.But in some cases i get output just in 1 or 2 seconds also.So same query takes different time to execute in different pc and in same pc also time required is not consistent.a So there is no consistency with the time taken to execute.So i am not able to find why this happens?Below is the query i used to test[code="sql"]SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]FROM [company].dbo.[customer]WHERE ( Charindex('9000413237',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0 ) [/code]Here customer table has about 30 columns.In that 'Customer name' is of datatype varchar(255),'Sl_No' is of int,'Id' is of int and 'Phone no' is of varchar(255).

Cannot start in Single User Mode

Posted: 26 Jul 2013 08:58 AM PDT

For a disaster recovery test we are being required to restore our master database to an alternate server.Several servers have worked fine.I have used SQL Server Configuration Manager and changed the startup parameters to Single Mode.When I use SQLCMD to restore the database I am plagued by an error stating there is basically another use logged in.I have started SQL Server from the command prompt as well (-m and -c options to watch for logins (I changed SQL Server security to log successful logins) and I see nothing.Any suggestions. I have googled the heck out of this and not coming up with anything else to try.

Affecting index rebuild on mirroring wchich in turn creates blocking and timeouts.

Posted: 26 Jul 2013 08:36 AM PDT

Hi all. Here is my situation.We have a 24 x 7 production system which is mirrored with high safety mode, and every night we have a job to defragment all indexes (at nights it slightly less accessed than during the day). Cumulative size of all principal databases is 2 TB+.Due to large amount of indexes rebuilt our mirroring does not keep up with rebuilding causing in turn mirroring delays. Since it is in high safety mode, it holds blocks on resources waiting for each transaction to acknowledgement from the mirror side, creating many blockings and timeouts.Does anybody experience same problems? What would be best solution to it ? Does changing mirroring from high safety to high performance mode solves this problem? What implications it can cause?Our SQL Server version is 2008 R2, and we use mirroring mostly for disaster recovery; we have failover clustering for high availability.Thanks

Attach/Detach Database message in sql server errorlog

Posted: 26 Jul 2013 01:18 AM PDT

Is there a way to automatic log entry in sql server error log when a database is attached or detached or created or dropped. thnx

Get the version and service pack details of all sql server instances

Posted: 26 Jul 2013 06:48 AM PDT

Hi,I'm trying to get the version and service pack details from all the sql server instances. I have a table which contains the list of sql servers....i just need the output in this formatexample :Instancename ServicePack VersionDBATest 10.0.4000.0 SP2this table has the list of serversselect instanceName from dbo.MonitoredSQLServersBelow scrpit gives the above outputselect SERVERPROPERTY('servername') Instancename,SERVERPROPERTY('productversion') ServicePack, SERVERPROPERTY('productlevel') VersionPlease let me know how to get the details for all the servers, i tried using the cursor...but i'm not much into developing...so any help would be greatly appreciated.Thanks in advace

Information about Jobs

Posted: 25 Jul 2013 10:10 PM PDT

Any script which will provide the below details of the JOBS??? as i have more than 100 jobs :w00t:1. Job Name:2. Start time of Job3. Max time taken by each job to run successfully.4. Server resources consumed by each jobPlease advice

Table partition/rebuild issues

Posted: 26 Jul 2013 12:26 AM PDT

Hi all,I have the following scenario on my 6 TB database (it's a mobile billing database) on SQL Server 2008 R2 SP2 (with Cumulative Update #5):- Huge amount of data inserted every day, at every time on a single table- Table partitioned on a DATETIME column with a range of 15 minutes- Daily maintenance to merge the oldest partitions (it's only necessary to keep data from the last 60 days) and add the future partitions- On this daily maintenance, I also rebuild the indexes partitions with fragmentation greater than 30%In order to complete this daily maintenance, I execute some steps to avoid locks and deadlocks:- Kill all sessions that are inserting data and/or querying this particular table- Disable the users which inserts and query- Execute the steps to add/remove partitions- Rebuild indexes partitions- Enable usersI applied the Cumulative Update # 5 patch , which says that the deadlocks on adding/removing partitions should be fixed (http://support.microsoft.com/kb/2713501/en-us). So, after that I removed the first and forth steps, so process can keep on inserting and users, querying. Well, it didn't work. The scenario occurred with deadlocks and locking on the rebuild.I sort of "workarounded" the deadlock issue by setting the DEADLOCK_PRIORITY to HIGH, but when we rebuild the index partitions, even if I switch the fragmented partition to an auxiliary table, execute the rebuild on the index partition on this auxiliary table and then switch back the partition to the original table, it also locks the users and the inserting and querying (and the rebuild).Is there anything I should do to fix this issue, so the users are not disabled? Any thoughts?Thanks

Single Column Encryption On Sensitive Data-Unabale to use Join

Posted: 26 Jul 2013 01:29 AM PDT

Hi All,My issue is regarding Single Column Encryption on Primary Key Column , what happens if this Primary Key or Unique Key column contains sensitive data, and also used for joins. between two tables.?what are the options to encrypt this data , so that they can remain same , even in encrypted state and can be used for joins.e.g.in table A there are two columns.Member_Id(PK) City1234 London6789 Paris7777 NewYorkin table B there are two columnsMember_Id(FK) Bank_account1234 RBC1234 ScotiaBank7777 BOBWhen I applied single column encryption, to save these sensitive data, it worked perfactly, but, it gives me different encrypted number for same Member_Id(both in table A and B) everytime.e.g.1234(pk) -> AES_128 -> HASDHAS829092)(jsadkljdhnasd1234(fk) -> AES_128 -> NCBASJEUHHKLS893298()JSLHAso I can not use this fields to join this tables any more,!!!!is there any option to solve this.?I already used "DataMasking" , which works fine.Now, I also want to try with encryption too., Please help me..Thanks..

Function to return the fiscal month from a date. Fiscal year begins in October

Posted: 26 Jul 2013 03:59 AM PDT

I need a function that would return the fiscal month number from a date. Our fiscal year starts in October.Something like...declare @Date datetimeif month(@Date)>9 then month(@Date) -9 else month(@Date) + 3

oledb destination on error continue for next row

Posted: 26 Jul 2013 01:42 AM PDT

Hi everybodyI need some advice about this:I have a DataFlowTask, wich contains a OleDbSource connected to a sql server table and a OleDbDestination also connected to sql server table. The Ole DB Source will extract around 3 millions records. The Ole DB Destination is replicated 2 times. The first ole db destination is configured for "Maximum insert commit size" to 100; "Row per batch" to 10; a "Error output" configured to "Ole Db Destination Input" --> "Redirect row"; then "Ole Db Destination error output" connected to the next "Ole DB Destination", which the unique difference is "Maximum insert commit size" to 1; "Row per batch" to 1.For some reason some rows have some error, I need the next correct row continues for inserting, but capturing the error for previous row error. At this moment the ole DB Destination is broken when comes the first rows with error.Some suggestionsThanks

SQL Server 2008 - how to upgrade to R2

Posted: 25 Jul 2013 09:25 PM PDT

Hi,I'm looking for some guidance on upgrading SQL Server 2008 version 10.1.2531.0 up to R2 which I understand will be 10.5.xxxI have tried running the installer for R2 SP2 and R2 SP1 but the installer prevents the update saying [quote]The version of SQL Server instance MSSQLSERVER does not match the version expected by the SQL Server update. The installed SQL Server product version is 10.1.2531.0, and the expected SQL Server version is 10.50.1600.1.[/quote]What is the next step? Clearly R2 SP1 is too much of a jump but I really have struggled to find any download in between.Thanks

Find and update the Schema changes of a table?

Posted: 26 Jul 2013 12:02 AM PDT

Hi Friends, I have around 25 tables where i am frequently using to put the data from live server to my local . The schema's of the live database gets changed frequently. I mean we add one or more columns evertime. When i use my SSIS package , to pull the data from live server to my local, it is messing up. I have to drop the database and recreate it with new schemas and update the package as well. So what i am looking for isJust Curious..1. How do we create a script to update the schema ? I know there is Red Gate tool which does this comparision of the schemas. But is there a way to create some SQL statements to update the schema? (because I can't buy the red gate software. price is not affordable :-))2. How can we avoid the SSIS package getting this error? You know if we have added some fields in the table, then i have to update the package as well....and then using it.Any suggestions would be really appreciated. Sorry in case , if my questions are meaning less...

Log Shipping - Point In Time Recovery

Posted: 25 Jul 2013 10:33 PM PDT

HiWe have Log Shipping as one of our DR solutions. Basically, we take a TL backup every 15 minutes to our DR server, we keep a maximum of 3 days worth of TL backups, which we're looking to extend to 10 days, so that we can roll the Production database back further back in time.What is the best method In restoring the Production database to a specific point in time? Would I need to apply the backup of the night before, then apply the TL up until the required restore time?

BAI, BAI2 bank files

Posted: 02 Nov 2011 10:47 PM PDT

Hi,Does anyone have a script to load and convert these files into SQL Server tables to construct regular, readable rows of data that could be exported to an Excel or CSV?Thanks

Page level compression - Indices/Partitions/Heap

Posted: 23 Jul 2013 04:49 AM PDT

I have read a lot of articles on compression but i couldn't find a definitive answer for these:i) if a table has no clustered index and has non-clustered indices, do i need to compress the table first and then the nc indices?ii)if a table has clustered index and has non-clustered indices, would just compressing the clustered index also compress the nc index?iii) When compressing the clustered index, can this be with ONLINE=ON option?

Change replication order in Transaction Replication

Posted: 25 Jul 2013 08:25 PM PDT

Hello,I'am running into a small problem concerning transactional replication.The database we are trying to replicate has different views which use user defined stored procedures and functions.When we setup transactional replication and select tables, views, sp's and functions the replication failes with an error certain functions are not available for the view it is creating on the subscriber.When I create a new subscription with only the Tables, SP's and functions and replicate that it works fine. When I add the views to the publication, create a new snapshot and reinitialize the subscription it all works as well.It seems when you setup replication the order in which it creates articles is Tables -> Views -> SP's -> Functions.Is there a way to change this order into Functions -> SP's -> Views -> Tables so we won't run into error messages that certain functions are not available to create the view on the subscriber?Thanks for the help!

Active Users

Posted: 25 Jul 2013 08:34 PM PDT

Hi,How to Find out Active users in SQLServer by T-SQL Statement

memory utilisation

Posted: 25 Jul 2013 08:30 PM PDT

How to check memory utilisation of single query ?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog