Thursday, October 3, 2013

[SQL Server 2008 issues] Checkpoint vs Log Backup

[SQL Server 2008 issues] Checkpoint vs Log Backup

Checkpoint vs Log Backup

Posted: 02 Oct 2013 07:07 PM PDT

Checkpoint and log backup both have feature to "truncate the log". Checkpoint primarily handles by sql server internally but backup log doesnt.Checkpoint flushes the dirty pages to disk.ANy other differences ?

Select add new row opening balance

Posted: 02 Oct 2013 06:44 PM PDT

Dear Expert,I have a query problem, there are 4 tables (with left join)filtered by year and period and also an account code.This is the query :select a.fyear,a.aperiod,c.trx_amt,e.acct_cd,e.active_status,ISNULL((select openingBalance=SUM(a.trx_amt)from gl_pendjnls aleft join gl_jlhdr b on a.idxjlhdr = b.idxleft join v_acctperiod c on b.idxperiod = c.idxright join v_gl_chart d on a.idxcoa = d.idxwhere c.fyear < = 2013 and c.aperiod < 10and d.acct_cd = e.acct_cdgroup by d.acct_cd ),0) as openingBalance,ISNULL((select closingBalance=SUM(a.trx_amt) + c.trx_amtfrom gl_pendjnls aleft join gl_jlhdr b on a.idxjlhdr = b.idxleft join v_acctperiod f on b.idxperiod = f.idxleft join v_gl_chart d on a.idxcoa = d.idxwhere f.fyear < = 2013 and f.aperiod < 10and d.acct_cd = e.acct_cdgroup by d.acct_cd ),0) as closingBalancefrom v_acctperiod aleft join gl_jlhdr b on a.idx = b.idxperiodleft join gl_pendjnls c on b.idx = c.idxjlhdrleft join v_gl_chart e on c.idxcoa = e.idxwhere e.active_status = 'Y'and a.fyear = 2013 and a.aperiod = 10and e.acct_cd = '111-01-201'The Result and Result that i want is : http://i44.tinypic.com/oju9t.jpg[url=http://i44.tinypic.com/oju9t.jpg][/url]if i change the a.fyear to = 2013 and a.period = 11 and remove the e.acct_cd = '111-01-201'there is no data,all i want it has data, a new row that consist the acct_cd from every period that have no data, so the NEW row data consist only acct_cd from the last period opening balance, openingbalance = closingbalance from opening balance the last periodis anybody can help?. i really appriciated for your attention.Thanks,Musa

SSIS Transfer SQL Server Objects Task - Permissions Issue

Posted: 02 Oct 2013 02:36 AM PDT

I am attempting to create an SSIS package that will copy data from selected tables on one server to another. The problem I am running into is that when I try to get the list of tables, I receive the following error: "Property DefaultSchema is not available for Database '[XXXX]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. "I don't have write permissions to either server. However, I can run a SQL Job calling the SSIS package which does have the required permissions. I do not receive an error if I choose "All Tables" but I do not want to copy all of the tables. Any ideas on how to get around this? Elevated permissions are not an option. I really don't want to manually create Data Flow source and destinations for each table.Thanks for any help in advance!

select decimals after calculations, weird display

Posted: 02 Oct 2013 12:05 PM PDT

Hi, I can't get why I have that strange results, I need to keep value of dividing in a column let say 1/35, it's displays as 0 no matter what kind of convert/cast I using,however if I add 35.0 like for a1 then it works.Internally all this results are columns in intermediate temp tables derived as a result of sum() or count(), no any no any special formatting done. c1, c2 alwayse int, but I need very presise result in "a" column. Later I will use "a" in ssrs .[code="other"]select SUM(1) c1, SUM(35) c2 into #tselect c1, c2, CAST(1/35.0 as decimal(8,6)) a1, CAST(1/35 as decimal(8,6)) a2, CAST(c1/c2 as decimal(8,6)) a3 from #t c1 c2 a1 a2 a31 35 0.028571 0.000000 0.000000[/code]TxMario

which one is better for High Avaliability

Posted: 02 Oct 2013 03:28 PM PDT

Hi everyone.. log shipping, Replication,Mirroring which one is better for High avaliability? and in now a days companies which one prefer? plz give reply....

Flat File not moving all data

Posted: 02 Oct 2013 06:10 AM PDT

Hi,Im using flat file (.csv) to sql 2008, which has only 40 rows but 39 rows get transfered 1 row missing.Also i see and warning sign as below[Flat File Source [1]] Warning: There is a partial row at the end of the file.Anyone have any clue about it.Shaun..

Sending Email Using Database

Posted: 02 Oct 2013 02:57 PM PDT

Dear,I require that after processing some transactions, need to send email with necessary information to the respective email addresses.I am using SQL Server 2008 R2. How can I do it?Please any guideline/resource must help me.Regards,Akbar

Sum prescription amounts for an individual by given a start date and number of days supplied

Posted: 02 Oct 2013 09:32 AM PDT

I am trying to develop a query to determine the amount of a drug that an individual has had for every day during a quarter. On some days, there are no drugs prescribed, for others, there may be overlap and I need a total amount (meaning, strength for each summed for a day). The number of drugs, strengths, daysupply etc. can vary. Here's some data:[code="sql"] create table #MemberInfo (ProgramName varchar(255), DateFilled datetime, DaySupply integer, MemberID varchar(255), Strength integer, Tradename varchar(255)); insert into #MemberInfo Values ('InsureCo', '20130612', 30, 'MEM001', 10, 'Sedative') , ('InsureCo', '20130429', 30, 'MEM001', 20, 'Sedative') , ('InsureCo', '20130401', 30, 'MEM001', 20, 'Sedative') , ('InsureCo', '20130529', 30, 'MEM001', 30, 'Sedative')[/code] I really have no idea what the best approach might be to add up the amount of drugs taken on a given day during a quarter. I'd like to avoid using cursors if I can. I was thinking about creating a temp table with all the days for a quarter and then somehow joining those dates to every day a drug is taken (i.e., DateFilled + every subsequent day up to DaySupply). Once I get to the point where I have the dates and amounts for every drug in a quarter, I could group by day and get a sum of strength for each day. I also need to be able to get the average amount taken over a quarter. Additional Requirements: 1. I have a start date and a number of days. I'd like to create a row for each member for every day they have a prescription (and do the same for all of their prescriptions). I would then sum the strength of all the drugs for each day. If it helps any, all of the drugs will be of the same class, and strength is going to be equivalent doses, meaning that I can sum them up. 2. For reporting, I need to be able to count consecutive days that the amount is greater than some cutoff (let's say 100). That's why I'm trying to get amount per day.[code="other"] Desired output MemberID Date SumStrength MEM001 2013-04-29 40 MEM001 2013-04-30 40 MEM001 2013-05-01 20 ETC FOR EVERY DAY FOR THIS MEMBER MEM002 2013-04-01 60 MEM002 2013-04-02 40 ETC FOR EVERY DAY FOR THIS MEMBER[/code]

t-sql is bigger

Posted: 02 Oct 2013 12:05 AM PDT

Guys, I currently have a database of 80 giga bytes. did a shrink log file to 1 mega byte. after it started backup diff and log, but the log file grew unexpectedly, and even when not being used by any application, ie only test ... as a log file can grow as fast if my database is not linked to any application? every 30min i've 170mb for each log file... that is stranger...

Archive Database in SQL 2008r2

Posted: 02 Oct 2013 06:44 AM PDT

Hello All,How to archive the entire database in SQL Server 2008r2Thanks,Santosh

Performance When dropping multiple tables

Posted: 01 Oct 2013 11:31 PM PDT

Hi,The software we use often creates a lot of temporary tables that need to be deleted from time to time.Depending on the usage, this can range from hundreds per month to thousands per week.These temporary tables are all created with names that meet certain conventions, as such, they're quite easy to identify in the database.Normally, when I've been dropping these tables, I've done it within a cursor.The cursor identifies the names of the tables in the database, and then the loop process drops each table one at a time.I'm often conscious of the performance overhead of using a cursor, so have been looking an alternative process for this.As such, I've tried a new Drop Table command, which comma separates each of the table names and then passes to a single Drop Table command.[code="sql"]declare @DropObjectList varchar(max)SELECT @DropObjectList = COALESCE(@DropObjectList+',' ,'') + TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESwhere TABLE_NAME like 'TEMP[_]TABLE[_]%'exec ('drop table ' + @DropObjectList)[/code]I've tried doing some basic performance testing on test data, and the results are inconclusive.It appears to me that the Drop Table command has a higher overhead than looping through the cursor.Therefore the performance isn't particularly different whether I run through a cursor or not.Does anyone have any ideas about the fastest way to drop multiple tables whose names meet a certain naming convention, and whether there should be a performance difference whether a cursor is used or not?Thank youSteve

Insert bulk failed due to a schema change of the target table.

Posted: 10 Jul 2013 07:21 AM PDT

Hello Expert.First time I used sqlbulkcopy its performance is impressive, however my application threw a SQL exception after processed about 500,000 records. The error stated "Insert bulk failed due to a schema change of the target table." which I don't understand why, data being inserted into the same table, and why it processed 500,000 records with out issue, but not the rest which I cant understand.Here is the chunk of VB.Net code that does the bulk copy. Notice that MyDestTable has a couple more fields that I didn't want them to be mapped. all fields in the MySourceDataTable table are specified in the mapping.Dim sqlBulk As New SqlBulkCopy(_connectionString) sqlBulk.DestinationTableName = MyDestTable sqlBulk.ColumnMappings.Add("DataOwnerID", "DataOwnerID") sqlBulk.ColumnMappings.Add("ObjectID", "ObjectID") sqlBulk.ColumnMappings.Add("ObjectTypeID", "ObjectTypeID") sqlBulk.ColumnMappings.Add("PropertyName", "PropertyName") sqlBulk.ColumnMappings.Add("Pvalue", "Pvalue") sqlBulk.ColumnMappings.Add("UpdateIDRefreshed", "UpdateIDRefreshed") sqlBulk.ColumnMappings.Add("UpdateIDModified", "UpdateIDModified") sqlBulk.ColumnMappings.Add("Attribute", "Attribute") sqlBulk.ColumnMappings.Add("POrder", "POrder") sqlBulk.WriteToServer(MySourceDataTable) sqlBulk.Close()Anyone run into this type of error while using SQLBulk copy? I did lot of googling but didn't find anything points to the right direction.Thanks in advanced.

SQL Server 2008 R2 Hyper-v 2012 live migration

Posted: 02 Oct 2013 01:26 AM PDT

Hi all,I would like to know if anybody has tried a live SQL Server 2008 R2 VM live migration on hyper-V 2012? And whether you have had any issues? I have found quite a lot of conflicting comments on the MS forums and it seems to depend on what features of SQL server are being used to whether it will be compatible or not. Thanks is advance.

sqlcmd using ctrl-A

Posted: 02 Oct 2013 12:33 AM PDT

I need to be able to export the data from a table to a text file using sqlcmd. I need to be able to use Ctrl-A as a column separator. Is this doable, and if so, how? I am assuming the this is a value used by the -s option.I need to use a batch file to run the sqlcmd utility.

Change value for allow_anonymous & immediate_sync for Replication

Posted: 01 Oct 2013 08:32 PM PDT

Hi,is there any good reason, why this both values should be set to true. I had to change this values many times.

Count Of Level in parent table

Posted: 01 Oct 2013 08:34 PM PDT

hello all.i have a parent table:subjectid subjectname parentid organizationid4058 x -1 5764059 x1 4058 5764060 x2 4058 5764061 x3 4058 5764062 x11 4059 5764063 x111 4062 576now i want to have count of level of my table?i want a query wich teturns 3 in this example
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog