[SQL Server 2008 issues] How to update Flag based on Maximum Amount

---

• How to update Flag based on Maximum Amount
• SQl 2008 full RAM Utilizing
• keep result in table valued data type .
• Does Transactional Replication have any affect on normal Transaction Log Backups?
• Primary server down
• Starting SSIS jobs from BMC Control-M
• distribution agent fails
• select only some rows and then the rest
• Is overpunch amenable to cross apply?
• Need Rank function to filter the records
• Saving / Retrieving Queries / Views
• Job finished with errors

How to update Flag based on Maximum Amount Posted: 07 Aug 2013 10:00 PM PDT Create Table #Temp(Lnno Varchar(15),Custcode varchar(10),Flag varchar(10),Amount Int,Amount_Flag varchar(1))Insert Into #TempValues ('1','A1','Cust',1000,''),('1','A2','CoAp',500,''),('1','A3','CoAp',100,''),('1','A4','CoAp',2000,''),('2','B1','Cust',1000,''),('2','B2','CoAp',1000,''),('2','B3','CoAp',1000,''),('2','B4','CoAp',1000,''),('3','C1','Cust',0,''),('3','C2','CoAp',1000,''),('3','C3','CoAp',1000,''),('3','C4','CoAp',5000,'')Select * from #Temp/*Hi,I have this data where it has Lnno,Custcode,Amount and Flag.My requirement is,I has to Update Amount_Flag as Y,to maximum Amount for that Lnno.1)Now in case Lnno = 1,there is one Cust and three CoAp ,So Amount_Flag should be updated as Y to Custcode 'A4',since it has maximum amount.2)In Case Lnno = 2,Amount is same for all,so in this case Amount_Flag should be Updated to Flag = 'Cust',that is the priority should be given to Cust.3)In Case Lnno = 3,Cust has amount 0,but two CoAp has Amount same that is 1000,so Amount_Flag should be updated to any one of the record.Please Help me.Thanks in Advance!!*/

SQl 2008 full RAM Utilizing Posted: 11 Aug 2013 05:18 AM PDT Hello ,Using Sql 2008 R2 , have 16 Gb RAM . Did Sql will use whole memory ;if no another services using that memory .how we can control the memory use of SQL ?ThanksSatheesh

keep result in table valued data type . Posted: 11 Aug 2013 06:37 PM PDT Hi,i want to keep result in table valued data type the result which is returned by function please tell me how to do it.

Does Transactional Replication have any affect on normal Transaction Log Backups? Posted: 11 Aug 2013 12:33 PM PDT Pretty basic question, but haven't touched replication in a long time. Does Transactional Replication have any affect on normal Transaction Log Backups and your recoverability in using them?

Primary server down Posted: 11 Aug 2013 04:56 PM PDT How can we make synchronise of data when the primary server down in log shipping and we have no witness server ?

Starting SSIS jobs from BMC Control-M Posted: 11 Aug 2013 05:05 PM PDT Our outsourcing partner is trying to setup a way to start SSIS jobs from BMC Control-M. There seams to be a problem doing that because the SSIS job returns control before the SSIS job has finnished. Is there a way to work around that problem?Control-M is a central scheduler, its installed on the SQL Server batch server so Connection between Control-M and the server is no problem.

distribution agent fails Posted: 11 Aug 2013 05:05 PM PDT Hi , i did not configure replication as of now , in one of the server configured transactional replication.Distribution agent fails on the primary server what is the impact of the data when distribution agent fails?

select only some rows and then the rest Posted: 11 Aug 2013 03:51 PM PDT Hi Professionals.I am stuck on a way around a query and wonder if it is possible.I want to always select the first 3 rows in the exact order from my table and then the rest of the rows in now particular order. The table is created dynmaically so it will not always be the same columns in the first 3.I have tried[code]select softwaremanufacturer,productname,productversion, * from newtable[/code]but it shows those columns listed and then those columns listed again plus the rest like so[code]Microsoft Corporation Office Access 2003 11.x Microsoft Corporation Office Access 2003 11.0.SP3 (jp) 22/04/2005 30/04/2008 11.xMicrosoft Corporation Office Access 2003 11.x Microsoft Corporation Office Access 2003 NULL 22/04/2005 30/04/2008 11.x[/code]is there a way around this so it selects just the softwaremanufacturer,productname,productversion oncehope this makes sensethanks in advance

Is overpunch amenable to cross apply? Posted: 11 Aug 2013 01:18 PM PDT I have a file that needs to have its contents converted to money from overpunch characters. I started on an approach, but my conscience is nagging me that it is a dumb approach. My instinct is that this needs to be a function, but then I'd have to write that over 40x, so that doesn't sound to bright either.Another idea is to use an overpunch table, but joining on the end of the substring also didn't sound like a good idea to me either. the gist of the problem is like so;select NDCNumber,cast( case when right(ingredientcost,1)='{' then substring(ingredientcost, 1,len(ingredientcost)-1)+'0' when right(ingredientcost,1)='A' then substring(ingredientcost, 1,len(ingredientcost)-1)+'1' when right(ingredientcost,1)='B' then substring(ingredientcost, 1,len(ingredientcost)-1)+'2' when right(ingredientcost,1)='C' then substring(ingredientcost, 1,len(ingredientcost)-1)+'3'when right(ingredientcost,1)='D' then substring(ingredientcost, 1,len(ingredientcost)-1)+'4'when right(ingredientcost,1)='E' then substring(ingredientcost, 1,len(ingredientcost)-1)+'5'when right(ingredientcost,1)='F' then substring(ingredientcost, 1,len(ingredientcost)-1)+'6'when right(ingredientcost,1)='G' then substring(ingredientcost, 1,len(ingredientcost)-1)+'7'when right(ingredientcost,1)='H' then substring(ingredientcost, 1,len(ingredientcost)-1)+'8'when right(ingredientcost,1)='I' then substring(ingredientcost, 1,len(ingredientcost)-1)+'9'when right(ingredientcost,1)='J' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='K' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='L' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='M' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='N' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='O' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='P' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='Q' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='R' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='}' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'end as money)/100 IngredientCost...into #ESIStage From ESILandedso, the column named ingredientcost should be the parameter to the function, so it can be @ColumnName, but then, what kind of function? returns table? I don't think it should return a table because I need to work on selected columns. As above, I should be able to join to it, but how to do that for only 40 out of 200 columns? Can you suggest how can I organize the work to get the overpunches in over forty columns to be updated to numbers to fix each affected column?iow, I think I have disqualified select dbo.fnFixOverpunch(IngredientCost)IngredientCostdbo.fnFixOverpunch(AWP)AWPdbo.fnFixOverpunch(DiscountAmount)DiscountAmountinto #ESIStage from ESILandedandselect * from ESILanded a join overpunch b onsubstring(....all the columns that need to be fixed??)= b.overpunchCharacter is there a way to use cross apply to reference the columns that need to be fixed as well as the other columns that need to be brought along with them (there are over 200 columns in the import, the overpunch columns are different money attributes ), or do I need to split the work somehow so I can use cross apply on the money columns and plain old select into for the other columns, then join them up again (this idea also means managing six columns that determine uniqueness, NDCNumber, DateFilled, Refill, Member, Pharmacy & Prescriber)my gut tells me writing the same thing a zillion times is a nonstarter, but my head cannot feature how to do this with cross apply, which I think is what it really needs.Hope this is clear, and the design alternatives understandable. thanks in advance for any guidance you can provide

Need Rank function to filter the records Posted: 11 Aug 2013 03:04 PM PDT Hi [code]CREATE TABLE #Temp( Lvl int ,Level1_pk_id int ,Level2_pk_id int ,Level3_pk_id int )INSERT INTO #TempSELECT 1,11,null,null UNION ALLSELECT 2,11,22,null UNION ALLSELECT 2,11,23,null UNION ALLSELECT 3,11,22,33 UNION ALLSELECT 3,11,22,34 UNION ALLSELECT 1,12,null,nullHere , Need to select the last records for each level1,level2,level3 comibation.Out should be as below select * from #TempLvl Level1_pk_id Level2_pk_id Level3_pk_id2 11 23 NULL3 11 22 333 11 22 341 12 NULL NULL[/code]Logic: Level1_pk_id =11 has 2 Level2_pk_ids 22 and 23. 22 has value for Level3_pk_id. so result for 11 and 22 combination is (11 22 33) and (11 22 34) will be the final result.11 and 23 does not have level3 so 11 and 23 should be come in result12 also does not have any child levels , so 12 should be in result set.1,11,null,null -- records should not come in final result set as it has child levels 2,11,22,null -- aslo should not come in final result set as it has child levels Thanks!

Saving / Retrieving Queries / Views Posted: 11 Aug 2013 04:03 AM PDT Just starting out, i can run queries, then i'm prompted to save them, but I don't know how to retrieve them. Also, after running a query, how can I save that 'extrtacted table' to work with it later, thxf

Job finished with errors Posted: 11 Aug 2013 12:54 AM PDT Hi all,I have a very strange case (at least for me). Job which was running smoothly for a long time (like 2 years) about 30-40 min now takes hours or even days if ever succeeded.First the errors was on Linked servers ( there is a query which selects from 2 remote sources). I run this query manually without any problem.But now I am getting this error:DBCC execution completed. If DBCC printed error messages, contact your system administrator.Msg 2767, Level 16, State 1, Procedure sp_table_statistics2_rowset, Line 105Could not locate statistics '...' in the system catalogs.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Each time it complains on different columns. Those columns belong to remote tables and not even in a select list.I run update statistics on all of remote tables and it didn't help at all. I am still getting the same error. I do not understand where DBCC execution comes from. SP doesn't have it, I checked. Is it some setting can be changed to avoid it?Thanks.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 - General / SQL Server 2008 To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] Provided Servcies / Budgeted Servcies

---

Provided Servcies / Budgeted Servcies Posted: 11 Aug 2013 04:14 AM PDT Hi all. I posted a similar post yesterday but I don't think I articulated what I am trying to accomplish well - I am a SQL server newbie. I need calculate (1) the total amount of each WAIVER_SERVICE that was budgeted, (2) the total amount for each wavier service that was provided and (3) the % Budget Used. Grouped by WAIVER_SERVICES and Month/Year.The output I'm looking for should look like this:[code="sql"]CLT_NBR WAIVER_SERVICES Month/Year Amount_Services_Provided Amount_Budgeted %_Budget_Used 10178 2 1 2013 1000 1100 90% 10178 2 2 2013 950 1000 95% 10178 2 3 2013 759 650 115% 10178 4 11 2012 600 1000 60%10178 4 9 2013 100 100 100%[/code]If a client was budgeted a service and did not receive it for a month a 0 value is needed and I need to display 0's for months with no data.Below are 3 temp tables which are examples of the original 3 tables used for the two programs. The tables are [b]B2H_DSP[/b], [b]B2H_SummaryForms[/b] and [b]B2H_RATE [/b]. Note the [b]B2H_Rate[/b] table is a reference table which pulls the cost of the wavier_services. [b]B2H_DSP TABLE[/b][code="sql"]CREATE TABLE B2H_DSP ( BGT_NBR int, CLT_NBR int, WAIVER_SERVICES int, WAIVER_PROGRAM int, START_DT date, END_DT date, BILLABLE_UNITS_OF_SERVICE int, UNITS_PER_MONTH int, RATE_PER_UNIT_OF_SERVICE int, PROJECTED_COST_PER_MONTH int, NUMBER_OF_MONTHS_OF_SERVICES int, PROJECTED_COST_PER_12_MONTHS int); INSERT INTO B2H_DSP VALUES (1, 10178, 9, 2, '7/21/2011', '7/21/2012', 5, 16, 12.7, 203.2, 12, 2633.47),(1, 10178, 10, 2, '7/21/2011', '7/21/2012', 5, 4, 19.55, 78.2, 12, 1013.47),(1, 10178, 11, 2, '7/21/2011', '7/21/2012', 5, 4, 19.55, 78.2, 12, 1013.47),(1, 10178, 5, 2, '7/21/2011', '7/21/2012', 5, 28, 12.64, 353.92, 12, 4586.8),(1, 10178, 2, 2, '7/21/2011', '7/21/2012', 5, 8, 13.23, 105.84, 12, 1371.69),(1, 10178, 3, 2, '7/21/2011', '7/21/2012', 5, 28, 13.23, 370.44, 12, 4800.9),(1, 10178, 8, 2, '7/21/2011', '7/21/2012', 6, 16, 13.23, 211.68, 12, 2743.37),(1, 10178, 1, 1, '7/21/2011', '7/21/2012', 1, 1, 2040, 2040, 12, 24480),(406, 10178, 1, 1, '7/21/2012', '7/21/2013', 1, 1, 2040, 2040, 12, 24480),(406, 10178, 2, 2, '7/21/2012', '7/21/2013', 5, 8, 13.23, 105.84, 12, 1371.69),(406, 10178, 3, 2, '7/21/2012', '7/21/2013', 5, 28, 13.23, 370.44, 12, 4800.9),(406, 10178, 5, 2, '7/21/2012', '7/21/2013', 5, 20, 19.45, 389, 12, 5041.44),(406, 10178, 8, 2, '7/21/2012', '7/21/2013', 6, 16, 13.23, 211.68, 12, 2743.37),(406, 10178, 9, 2, '7/21/2012', '7/21/2013', 5, 16, 19.55, 312.8, 12, 4053.89),(406, 10178, 10, 2, '7/21/2012', '7/21/2013', 5, 8, 19.55, 156.4, 12, 2026.94),(406, 10178, 11, 2, '7/21/2012', '7/21/2013', 5, 8, 19.55, 156.4, 12, 2026.94),(933, 10178, 1, 1, '7/21/2013', '7/21/2014', 1, 1, 2040, 2040, 12, 24480),(933, 10178, 2, 2, '7/21/2013', '7/21/2014', 5, 8, 13.23, 105.84, 12, 1371.69),(933, 10178, 5, 2, '7/21/2013', '7/21/2014', 5, 16, 19.45, 311.2, 12, 4033.15),(933, 10178, 10, 2, '7/21/2013', '7/21/2014', 5, 4, 19.55, 78.2, 12, 1013.47),(933, 10178, 11, 2, '7/21/2013', '7/21/2014', 5, 4, 19.55, 78.2, 12, 1013.47),(933, 10178, 3, 2, '7/21/2013', '7/21/2014', 5, 18, 13.23, 238.14, 12, 3086.29),(933, 10178, 8, 2, '7/21/2013', '7/21/2014', 3, 3, 236.7, 710.1, 12, 9202.9),(933, 10178, 9, 2, '7/21/2013', '7/21/2014', 5, 18, 19.55, 351.9, 12, 4560.62),(480, 12014, 1, 1, '9/21/2011', '9/21/2012', 1, 1, 2040, 2040, 12, 24480),(480, 12014, 1, 1, '9/21/2011', '10/21/2011', 1, 1, 2038, 2038, 1, 2038),(480, 12014, 2, 2, '9/21/2011', '9/21/2012', 5, 8, 8.6, 68.8, 12, 891.65),(480, 12014, 3, 2, '9/21/2011', '9/21/2012', 5, 8, 13.23, 105.84, 12, 1371.69),(480, 12014, 5, 2, '9/21/2011', '9/21/2012', 5, 8, 19.45, 155.6, 12, 2016.58),(480, 12014, 8, 2, '9/21/2011', '9/21/2012', 3, 2, 236.7, 473.4, 12, 6135.26),(480, 12014, 9, 2, '9/21/2011', '9/21/2012', 5, 4, 19.55, 78.2, 12, 1013.47),(480, 12014, 10, 2, '9/21/2011', '9/21/2012', 5, 4, 19.55, 78.2, 12, 1013.47),(480, 12014, 11, 2, '9/21/2011', '9/21/2012', 5, 4, 19.55, 78.2, 12, 1013.47),(481, 12014, 1, 1, '9/21/2012', '9/21/2013', 1, 1, 2040, 2040, 12, 24480),(481, 12014, 2, 2, '9/21/2012', '9/21/2013', 5, 8, 8.6, 68.8, 12, 891.65),(481, 12014, 3, 2, '9/21/2012', '9/21/2013', 5, 8, 13.23, 105.84, 12, 1371.69),(481, 12014, 5, 2, '9/21/2012', '9/21/2013', 5, 8, 19.45, 155.6, 12, 2016.58),(481, 12014, 8, 2, '9/21/2012', '9/21/2013', 3, 2, 236.7, 473.4, 12, 6135.26),(481, 12014, 9, 2, '9/21/2012', '9/21/2013', 5, 4, 19.55, 78.2, 12, 1013.47),(481, 12014, 10, 2, '9/21/2012', '9/21/2013', 5, 4, 19.55, 78.2, 12, 1013.47),(481, 12014, 11, 2, '9/21/2012', '9/21/2013', 5, 4, 19.55, 78.2, 12, 1013.47)[/code][b]B2H_SummaryForms TABLE[/b][code="sql"]CREATE TABLE B2H_SummaryForms ( CLT_NBR int, DOSStart date, DOSEnd date, BillableUnits int, RateCode int ); INSERT INTO B2H_SummaryForms VALUES (12014, '3/2/2012', '3/2/2012', 10, 1312),(12014, '3/30/2012', '3/30/2012', 4, 1312),(10178, '4/19/2012', '4/19/2012', 4, 1312),(12014, '5/9/2012', '5/9/2012', 0, 1300),(12014, '5/21/2012', '5/21/2012', 1, 1300),(10178, '6/1/2012', '6/1/2012', 0, 1300),(10178, '5/10/2012', '5/10/2012', 4, 1312),(12014, '6/19/2012', '6/19/2012', 2, 1319),(10178, '6/26/2012', '6/26/2012', 2, 1312),(12014, '7/27/2012', '7/27/2012', 1, 1300),(10178, '8/8/2012', '8/8/2012', 1, 1300),(12014, '7/13/2012', '7/13/2012', 0, 1300),(12014, '7/1/2012', '7/1/2012', 8, 1312),(10178, '7/2/2012', '7/2/2012', 0, 1300),(10178, '5/8/2012', '5/8/2012', 0, 1300),(12014, '5/10/2012', '5/10/2012', 4, 1312),(10178, '5/8/2012', '5/8/2012', 0, 1300),(10178, '4/2/2012', '4/2/2012', 0, 1300),(10178, '3/8/2012', '3/8/2012', 1, 1312),(10178, '5/10/2012', '5/10/2012', 14, 1319),(12014, '4/10/2012', '4/10/2012', 1, 1300),(12014, '2/29/2012', '2/29/2012', 2, 1312),(12014, '3/5/2012', '3/5/2012', 0, 1300),(10178, '2/7/2012', '2/7/2012', 0, 1300),(10178, '2/15/2012', '2/15/2012', 1, 1300),(10178, '2/29/2012', '2/29/2012', 4, 1312),(10178, '3/8/2012', '3/8/2012', 0, 1300),(10178, '5/10/2012', '5/10/2012', 4, 1312),(12014, '3/2/2012', '3/2/2012', 10, 1312),(10178, '3/15/2012', '3/15/2012', 1, 1300),(12014,'3/30/2012', '3/30/2012', 1, 1300),(10178, '4/4/2012', '4/4/2012', 4, 1312),(12014, '4/5/2012', '4/5/2012', 0, 1300),(10178, '4/19/2012', '4/19/2012', 10, 1319),(10178, '4/10/2012', '4/10/2012', 1, 1300),(10178, '5/17/2012', '5/17/2012', 1, 1300),(10178, '6/6/2012', '6/6/2012', 1, 1300),(12014, '6/19/2012', '6/19/2012', 2, 1312),(12014, '6/19/2012', '6/19/2012', 2, 1319),(10178, '7/23/2012', '7/23/2012', 1, 1300),(12014, '8/20/2012', '8/20/2012', 0, 1300),(10178, '8/25/2012', '8/25/2012', 28, 1309),(10178, '9/5/2012', '9/5/2012', 0, 1300),(10178, '8/25/2012', '8/25/2012', 55, 1309),(12014, '9/17/2012', '9/17/2012', 1, 1300),(12014, '9/14/2012', '9/14/2012', 0, 1300),(12014, '9/1/2012', '9/1/2012', 1, 1318),(12014, '8/31/2012', '8/31/2012', 1, 1300),(10178, '8/1/2012', '8/1/2012', 0, 1300),(12014, '5/10/2012', '5/10/2012', 4, 1312),(12014, '6/19/2012', '6/19/2012', 2, 1312),(12014, '6/19/2012', '6/20/2012', 1, 1300),(12014, '5/10/2012', '5/10/2012', 4, 1312),(12014, '6/11/2012', '6/11/2012', 0, 1300),(12014, '4/4/2012', '4/4/2012', 2, 1312),(12014, '2/22/2012', '2/22/2012', 1, 1300),(12014, '3/13/2012', '3/13/2012', 4, 1312),(12014, '3/20/2012', '3/20/2012', 1, 1312),(12014, '2/7/2012', '2/7/2012', 0, 1300),(12014, '9/15/2012', '9/15/2012', 1, 1318),(12014, '9/21/2012', '9/21/2012', 4, 1312),(10178, '9/13/2012', '9/13/2012', 1, 1300),(12014, '9/22/2012', '9/22/2012', 1, 1318),(10178, '1/8/2013', '1/8/2013', 1, 1300),(12014, '9/21/2012', '9/21/2012', 4, 1312),(12014, '10/5/2012', '10/5/2012', 0, 1300),(12014, '10/8/2012', '10/8/2012', 1, 1300),(10178, '10/10/2012', '10/10/2012', 1, 1300),(10178, '10/2/2012', '10/2/2012', 0, 1300),(12014, '10/6/2012', '10/6/2012', 16, 1308),(10178, '10/19/2012', '10/19/2012', 1, 1318),(10178, '10/20/2012', '10/20/2012', 16, 1319),(10178, '10/20/2012', '10/20/2012', 16, 1319),(10178, '10/19/2012', '10/19/2012', 1, 1318),(10178, '11/5/2012', '11/5/2012', 0, 1300),(12014, '11/6/2012', '11/6/2012', 0, 1300),(12014, '9/28/2012', '9/28/2012', 16, 1308),(12014, '10/27/2012', '10/27/2012', 16, 1308),(12014, '11/9/2012', '11/9/2012', 1, 1300),(12014, '10/15/2012', '10/15/2012', 4, 1312),(12014, '10/6/2012', '10/6/2012', 16, 1308),(12014, '9/28/2012', '9/28/2012', 16, 1308),(12014, '10/27/2012', '10/27/2012', 16, 1308),(12014, '11/10/2012', '11/10/2012', 16, 1308),(10178, '11/12/2012', '11/12/2012', 1, 1300),(12014, '11/10/2012', '11/10/2012', 16, 1308),(12014, '11/17/2012', '11/17/2012', 12, 1308),(12014, '10/15/2012', '10/15/2012', 4, 1312),(12014, '11/16/2012', '11/16/2012', 4, 1312),(12014, '11/24/2012', '11/24/2012', 16, 1308),(12014, '11/24/2012', '11/24/2012', 1, 1307),(12014, '11/17/2012', '11/17/2012', 12, 1308),(12014, '11/24/2012', '11/24/2012', 16, 1308),(12014, '12/1/2012', '12/1/2012', 16, 1308),(12014, '11/24/2012', '11/24/2012', 1, 1307),(10178, '12/3/2012', '12/3/2012', 0, 1300),(12014, '11/24/2012', '11/24/2012', 16, 1308),(12014, '12/1/2012', '12/1/2012', 16, 1308),(12014, '11/24/2012', '11/24/2012', 1, 1307),(12014, '12/17/2012', '12/17/2012', 0, 1300),(10178, '12/10/2012', '12/10/2012', 1, 1300),(10178, '12/10/2012', '12/19/2012', 1, 1300),(12014, '11/24/2012', '11/24/2012', 1, 1307),(12014, '12/21/2012', '12/21/2012', 1, 1300),(12014, '12/22/2012', '12/22/2012', 16, 1308),(12014, '11/24/2012', '11/24/2012', 1, 1307),(12014, '1/5/2013', '1/5/2013', 1, 1318),(10178, '1/8/2013', '1/8/2013', 1, 1300),(10178, '1/7/2013', '1/7/2013', 0, 1300),(10178, '1/7/2013', '1/7/2013', 0, 1300),(10178, '5/8/2012', '5/8/2012', 0, 1300),(12014, '1/23/2013', '1/23/2013', 0, 1300),(12014, '1/25/2013', '1/25/2013', 1, 1300),(10178, '2/4/2013', '2/4/2013', 0, 1300),(12014, '2/2/2013', '2/2/2013', 1, 1318),(12014,'2/11/2013', '2/11/2013', 0, 1300),(12014, '2/19/2013', '2/19/2013', 1, 1300),(10178, '2/19/2013', '2/19/2013', 1, 1300),(12014,'2/23/2013', '2/23/2013', 8, 1308),(10178, '3/4/2013', '3/4/2013', 0, 1300),(12014, '3/9/2013', '3/9/2013', 1, 1318),(12014, '3/9/2013', '3/9/2013', 1, 1318),(12014, '3/7/2013', '3/7/2013', 0, 1300),(12014, '3/15/2013', '3/15/2013', 1, 1300),(10178, '3/12/2013', '3/12/2013', 8, 1306),(10178, '3/13/2013', '3/13/2013', 3, 1312),(10178, '3/12/2013', '3/12/2013', 1, 1300),(10178, '3/21/2013', '3/21/2013', 2, 1308),(10178, '3/21/2013', '3/21/2013', 4, 1319),(10178, '3/20/2013', '3/20/2013', 2, 1312),(10178, '3/23/2013', '3/23/2013', 1, 1318),(12014, '4/2/2013', '4/2/2013', 0, 1300),(10178, '4/2/2013', '4/2/2013', 0, 1300),(12014, '4/6/2013', '4/6/2013', 1, 1318),(10178, '4/5/2013', '4/5/2013', 1, 1300),(10178, '4/10/2013', '4/10/2013', 4, 1312),(10178, '4/11/2013', '4/11/2013', 6, 1308),(12014, '4/22/2013', '4/22/2013', 1, 1300),(10178, '5/2/2013', '5/2/2013', 0, 1300),(10178, '5/2/2013', '5/2/2013', 6, 1319),(10178, '5/4/2013', '5/4/2013', 1, 1318),(10178, '5/16/2013', '5/16/2013', 1, 1300),(10178, '5/14/2013', '5/14/2013', 6, 1312),(12014, '5/17/2013', '5/17/2013', 0, 1300),(12014, '5/20/2013', '5/20/2013', 1, 1300),(10178, '6/6/2013', '6/6/2013', 0, 1300),(10178, '6/10/2013', '6/10/2013', 1, 1300),(10178, '6/1/2013', '6/1/2013', 1, 1318),(12014, '6/7/2013', '6/7/2013', 0, 1300),(12014, '6/17/2013', '6/17/2013', 1, 1300),(12014, '6/19/2013', '6/19/2013', 4, 1308),(12014, '6/29/2013', '6/29/2013', 1, 1318),(12014, '6/29/2013', '6/29/2013', 8, 1308),(10178, '7/10/2013', '7/10/2013', 0, 1300),(12014, '7/11/2013', '7/11/2013', 0, 1300),(12014, '7/3/2013', '7/3/2013', 4, 1308),(12014, '7/13/2013', '7/13/2013', 1, 1318),(10178, '7/24/2013', '7/24/2013', 1, 1300)[/code][b]B2H_RATE [/b] [code="sql"]CREATE TABLE B2H_RATE ( RATE_CODE int, WAIVER_SERVICES date, RATE_AMOUNT date, BILLABLE_UNIT int);(1300, 1, 2040, 1),(1301, 1, 2038, 1),(1302, 1, 1020, 2),(1303, 1, 1020, 2),(1304, 1, 2040, 1),(1305, 1, 2040, 1),(1306, 2, 13.23, 5),(1307, 2, 8.6, 5),,(1308, 3, 13.23, 5),(1309, 3, 8.6, 5),(1310, 4, 77, 4),(1311, 4, 40.85, 4),(1312, 5, 19.45, 5),(1313, 5, 12.64, 5),(1314, 6, 55.68, 4),(1315, 6, 30.19, 4),(1316, 7, 70, 4),(1317, 8, 13.23, 6),(1318, 8, 236.7, 3),(1319, 9, 19.55, 5),(1320, 9, 12.7, 5),(1321, 10, 19.55, 5),(1322, 11, 19.55, 5),(1323, 12, 17.59, 6),(1324, 12, 304.5, 3),(1327, 1, 2040, 1),(1328, 1, 2038, 1),(1329, 1, 1020, 2),(1330, 1, 1020, 2)(1331, 1, 2040, 1),(1332, 1, 2040, 1),(1333, 2, 13.23, 5),(1334, 2, 8.6, 5),(1335, 3, 13.23, 5),(1336, 3, 8.6, 5),(1337, 4, 77, 4),(1338, 4, 40.85, 4),(1339, 5, 19.45, 5),(1340, 5, 12.64, 5),(1341, 6, 55.68, 4)(1342, 6, 30.19, 4),(1343, 7, 70, 4),(1344, 8, 13.23, 6),(1345, 8, 236.7, 3),(1346, 9, 19.55, 5),(1347, 9, 12.7, 5),(1348, 10, 19.55, 5),(1349, 11, 19.55, 5),(1350, 12, 17.59, 6),(1351, 12, 304.5, 3),(1354, 1, 2040, 1),(1355, 1, 2038, 1),(1356, 1, 1020, 2),(1357, 1, 1020, 2),(1358, 1, 2040, 1),(1359, 1, 2040, 1),(1360, 2, 13.23, 5),(1361, 2, 8.6, 5),(1362, 3, 13.23, 5),(1363, 3, 8.6, 5),(1364, 4, 77, 4),(1365, 4, 40.85, 4),(1366, 5, 19.45, 5),(1367, 5, 12.64, 5),(1368, 6, 55.68, 4),(1369, 6, 30.19, 4),(1370, 7, 70, 4),(1371, 8, 17.3, 6),(1372, 8, 309.42, 3),(1373, 9, 19.55, 5),(1374, 9, 12.7, 5),(1375, 10, 19.55, 5),(1376, 11, 19.55, 5),(1377, 12, 23.94, 6),(1378, 12, 414.48, 3)[/code]This is what I have so far. I've been trying but I can't figure out how to get the output above. [b]Budgeted [/b][code="sql"]SELECT ChildID, WAIVER_SERVICES, START_DT, END_DT, RATE_PER_UNIT_OF_SERVICE, PROJECTED_COST_PER_MONTHFROM [ECMS_BACKUP].[dbo].[B2H_DSP]where WAIVER_PROGRAM=2[/code]The budgets can be renewed once a year So the Start_Dt and End_dt are always a year long. This makes it tricky to break down the budget amount by month. [b]Services Provided[/b][code="sql"]select a.CLT_NBR, b.WAIVER_SERVICES, month(a.DOSStart) as Month, YEAR(a.DOSStart) as Year, sum(b.RATE_AMOUNT)as totalfrom [ECMS_BACKUP].[dbo].[B2H_SummaryForms]aleft JOIN ECMS_BACKUP.dbo.B2H_RATE b ON a.RateCode=b.RATE_CODEwhere DOSStart>='2013-01-01' AND DOSStart<'2013-06-30' /*I selected a 6th month time frame for services provided */group by ChildID, month(a.DOSStart), YEAR(a.DOSStart),WAIVER_SERVICESorder by childid, month(a.DOSStart), YEAR(a.DOSStart),WAIVER_SERVICES[/code]I hope I was able to articulate this better. Any assistance is appreciated. Thanks. :-D

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[how to] LEFT vs CHARINDEX for matching the start of a string in T-SQL

---

• LEFT vs CHARINDEX for matching the start of a string in T-SQL
• Why doesn't `1` fit into a decimal(4, 4) column?
• Disable multiple statements in MSSQL Server?
• Handling or preventing conflicts in a multi-user system
• Online database for a C# application
• How important are transaction log VLFs for performance?
• script to automate an online mysql database dump, download and restore to other local db
• Creating groups and members
• trigger that modify values of firing quary
• How to index for a query with independent range conditions?
• Should I add a column or 1-1 table if property is usualy null
• Create Language plperl - Error: could not load library plperl.dll
• Is PostgreSQL appropriate for processing this large but simple dataset?
• how to explain indexes
• Moving away from CmdExec steps in job without losing logging detail (SQL Server 2008)
• Order table Database design
• InnoDB Failure of some kind
• MySQL is running but not working
• Mistake during Oracle 11g PITR
• "Arithmetic overflow" when initializing SQL Server 2012 replication from backup
• PLSQL : DBMS Jobs - parallelization?
• Can't connect to SQL Server Windows 7
• Alternative tools to export Oracle database to SQL Server?
• MySQL auto increment problem with deleting rows / archive table
• Empty LONGTEXT or NULL?
• Best embed/reference/field strategy for many inserts (mongo)

LEFT vs CHARINDEX for matching the start of a string in T-SQL Posted: 11 Aug 2013 08:02 PM PDT I want to match the beginning of a string in a WHERE clause. While I recognise that so often database-performance is dependant on the underlying data structure, is there a best-practice for doing this? Is there one option which always outperforms the rest? I want to avoid LIKE as I assume it will be less efficient than LEFT or CHARINDEX. As far as I can tell, my choices are below. Other suggestions welcome: DECLARE @MyField as varchar(10) SET @MyField = 'HELLOWORLD' SELECT 1 WHERE @MyField LIKE 'HELLO%' SELECT 2 WHERE LEFT(@MyField, 5) = 'HELLO' SELECT 3 WHERE CHARINDEX('HELLO', @MyField) = 1 Kind Regards, Jase.

Why doesn't `1` fit into a decimal(4, 4) column? Posted: 11 Aug 2013 07:04 PM PDT I have a decimal(4, 4) column in MS SQL Server 2008 R2. If I understand correctly, that means: Precision of 4, ie up to four digits can be stored after the decimal place Scale of 4, ie a a total of four digits can be stored When I run an update command to set the column to 1 (update myTable set myDecimalColumn=1), I get this error: Arithmetic overflow error converting numeric to data type numeric. I don't understand how that is true. 1 has no digits after the decimal and is only one digit long.

Disable multiple statements in MSSQL Server? Posted: 11 Aug 2013 06:42 PM PDT I'm running a web app backed by ASPX and MSSQL server, now I want to disable multiple statements execution, e.g select 1,2,3; select 1,2,3 So, can I disable that feature, or do I need to modify the connection string to make it work?

Handling or preventing conflicts in a multi-user system Posted: 11 Aug 2013 04:20 PM PDT I have a web application that is accessed by multiple users from different locations worldwide. Let's say I have an "edit" form for a specific document and two (or more) users are editing the document at the same time. I know that both of the users can edit the document and submit it, but user1 will not see that user2 made changes to the document and might submit an older version of it. I was thinking about adding some kind of a lock, but then, if user1 only opened the document for edit, but never changed anything (and kept it open) user2 will never be able to edit it. Therefore, I was thinking to add a Timeout for the editor, but then user1 might time out before he finished doing his changes to the document (let's say, went out for launch). The question is, how would one prevent the document from being edited from one user while the other changes it?

Online database for a C# application Posted: 11 Aug 2013 06:55 PM PDT I know it might be a wrong place to ask this question, but I couldn't find a better one. Should I disrupt the famous StackExchange balance, I'm truly sorry. I am writing a C# app for myself. I want to run it on different computers and share a database between those copies of the app. The trouble I've is: which database to use. And how? What I mean is, I thought I could simply connect to my hosting account's MySQL DB, but it turned out they don't support connections from the outside, so I looked for MySQL database providers and I didn't find anything interesting. Everything is either too expensive, or looks like a hosting account. Are there any DB-only providers for clients like me? And which database, if not MySQL, should I utilize for such a project?

How important are transaction log VLFs for performance? Posted: 11 Aug 2013 06:00 PM PDT How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?

script to automate an online mysql database dump, download and restore to other local db Posted: 11 Aug 2013 06:21 PM PDT I wish to find any script/software to automate the following steps; connect to an online mysql database and create dump/compressed sql.zip download the created dump/sql.zip restore the same to other localhost mysql db for offline use Windows Server Admin/Root access to online server is available. Local admin/root is available. Has anyone done this before? Regards, Webix

Creating groups and members Posted: 11 Aug 2013 06:13 PM PDT Is this a good idea to manage members of a group like so: *Assuming members can belong to one group and no other *Simplified model Group: name someid User: name ForeignKey(Group) state #integer 0-no group 1-waiting for approval from a group admin 2-belong to an group Something like facebook groups

trigger that modify values of firing quary Posted: 11 Aug 2013 07:47 AM PDT I need to write a postgresql trigger that fires BEFORE INSERT ON a specific table. This trigger should retrieve on database some values that are unknown at the moment of creation and execution of insert query add, it to firing query and then execute query with all fields. i was thinking something like: CREATE FUNCTION myFunction() RETURNS trigger AS $myTrigger$ DECLARE myVar VARCHAR(20); BEGIN SELECT field1 FROM anotherTable WHERE field2 = NEW.code INTO myVar; NEW.unknownField = myVar; RETURN NULL; END; $myTrigger$ LANGUAGE plpgsql; CREATE TRIGGER myTrigger BEFORE INSERT ON myTable EXECUTE PROCEDURE myFunction(); could this trigger works? (i'm not at home now, so i can't try if it's ok! )

How to index for a query with independent range conditions? Posted: 11 Aug 2013 01:21 PM PDT What is the best way to index a table that I query using independent range conditions? E.g. WHERE X < ? and Y > ?

Should I add a column or 1-1 table if property is usualy null Posted: 11 Aug 2013 07:19 AM PDT There's a new unique property we need to add, which is relevant only to a specific subset (less than 1%). We thought it's better to add a new table which will be 1-1, than to add a new column which will have Nulls 99% of the time. Is a new table the preffered way?

Create Language plperl - Error: could not load library plperl.dll Posted: 11 Aug 2013 08:03 PM PDT When I create language plperl , I get error: ERROR: could not load library "C:/Program Files/PostgreSQL/9.1/lib/plperl.dll": The specified module could not be found. But in my computer, "plperl.dll" file is exist in "C:/Program Files/PostgreSQL/9.1/lib/..." folder ( I can not post illustrative image, this forum require >= 10 reputations) And if I select * pg_pltemplate, I get: -[ RECORD 4 ]-+------------------------- tmplname | plperl tmpltrusted | t tmpldbacreate | t tmplhandler | plperl_call_handler tmplinline | plperl_inline_handler tmplvalidator | plperl_validator tmpllibrary | $libdir/plperl

Is PostgreSQL appropriate for processing this large but simple dataset? Posted: 11 Aug 2013 11:53 AM PDT I have a dataset I'm not sure how to store. The structure is simple: 30 numeric attributes identified by about 15 billion x, y, and t values. We're expecting ~17k t values and maybe 90k x/y combinations, though it could be some combination that gives us 20 million records in the end. The processing involves retrieving 1-10 columns for each x and y pair and storing various calculated numeric values. Are we nearing/passing the limit of fast response times for Postgres with this many rows? The processing is all done in-house by one person and shouldn't need to happen more than a couple dozen times as we settle on what summaries we want. So we're not worried about a high number of writes or connections, strict security, making updates to existing records, table relations, losing data because of network connection issues. Basically, we're not concerned about the kinds of things I understand the ACID part of a RDBMS like Postgres brings to the table. But we also don't need to replicate or distribute the data, high availability, change the schema on the fly, or manage an unusual number of writes (say a dozen for each of the 90k x/y pairs)- the kinds of things I understand NoSQL DBs offer. So I guess the real issue is read-speed out of Postgres for a table of this size and the real question is whether there's a better storage mechanism for what we need to accomplish. Does the answer change if we have 40 billion records? 60?

how to explain indexes Posted: 11 Aug 2013 12:39 PM PDT This question is about teaching: When I learned databases, we talked about indexes being like the card catalog in the town library. We had at least an author index, a title index, then the Dewey decimal number was the access path to the book, which is like the whole record in the database. Books? What, on paper? Index cards in drawers? The youngsters don't really know what I'm talking about, so how do we best explain it nowadays? (Feel free to enjoy my lawn, just please recognize the difference between the grass and the astroturf at the prep school, ok?)

Moving away from CmdExec steps in job without losing logging detail (SQL Server 2008) Posted: 11 Aug 2013 06:40 PM PDT My department currently relies heavily on CmdExec steps in SQL Server Jobs, called using a SQL Server Login. For example osql /e /n /S SERVER01 /U USER /P PA$$$ /d DATABASE_01 /i "\\LOCATION\OF\SQL\SCRIPT\scriptfile.sql" /o "\\LOCATION\OF\SQL\LOG\scriptfile.log" For a lot of reasons (not least security and the expectation that this method will become obsolete) I'd like us to move away from this model and replace with a mixture of stored procedures and SSIS packages. I've proposed this several times and I'm always told that we can only move in this direction if we can recreate the detail of logging possible by the method above, which our department has come to rely on quite heavily. The method above outputs the contents of the script fed in along with any server messages including all rowcounts and error messages, in context. This does admittedly mean that errors that wouldn't count as errors for SQL Server are far more easily picked up - Unexpectedly low row counts etc. You could put in as much error handling as you liked and never get something as easy to follow as the logging from this method, so I can see this side of the argument. To sell my boss on a move away from cmdexec steps I'd like to find a method of recreating something like the output of the logging that cmdexec currently gives us - perhaps to SQL tables rather than files (although either would be fine) - in SSIS packages and stored procedures. I'm familiar with using raiserror for error handling - I use this extensively in stored procedures - but the task here is not just to catch SQL errors, but to catch all that cmdexec logging catches and ideally in context of the script being executed... Short of writing a stored procedure and executing it after every step in every script I can't see a way of doing this. Also I'm keen to hear about best practice approaches to logging for people who don't use cmdexec steps. I've learnt SQL in this department and this is the only way I've ever known of doing things. Increasingly for my own development I try to write stored procs with error handling. And if I come across issues that don't throw errors, I do my troubleshooting by rerunning the code interractively on a dev server so I can monitor the step by step information. This is less convenient than the full logging we get from cmdexec but more secure. Do other people do more than this? Is there a better way?

Order table Database design Posted: 11 Aug 2013 06:59 AM PDT I'm in need of some help with the database design of order table of a shopping cart for online food ordering system. I have a design below. Tables about which I'm Concerned are order_details and order. Basically what is going to happen is user selects foods from restaurant menus and will place order, User details are stored in the user table (which is not shown in the image). This is my first attempt, I was never good at Database. So please take it easy if you fill that the question is way too low for this site. And my questions are: Are the tables good enough? Do I need to add any more fields? And one more question I need to store multiple addresses of users(user can have multiple shipping address) how can I handle this? http://i.stack.imgur.com/HCYBi.jpg Note The item_price table in the image is not i use. Please ignore that table while answering. Thanks

InnoDB Failure of some kind Posted: 11 Aug 2013 01:59 PM PDT I have MySQL 5.5 installed. I tried to install Joolma but it failed. I went into their sql and replace EGNINE=InnoDB with MyISAM and the install worked. InnoDB is listed under SHOW ENGINES; Any idea what the cause is or how to fix this so other InnoDB sites can be used? I had these errors: MySQL ERROR No: 1030 MysqL Error: Got error -1 from storage engine

MySQL is running but not working Posted: 11 Aug 2013 03:59 PM PDT In an attempt to tune MySQL to make it work with a recent installation of Drupal I had to modify the MySQL settings on my server. After modifying the configuration file for MySQL (/etc/my.cnf) MySQL stopped working. After some attempts I make it start again but now all my php/MySQL webistes are not being able to connect to their DBs. Here is why is so confusing: If I check a phpinfo.php on any given site, the MySQL info is there phpmyadmin runs just fine I can run mysql from SSH using root but I see that mysql activity is reduced, look: [root@server mysql]# mysqladmin processlist +-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+ | 7 | eximstats | localhost | eximstats | Sleep | 30 | | | | 103 | DELAYED | localhost | eximstats | Delayed insert | 30 | Waiting for INSERT | | | 104 | DELAYED | localhost | eximstats | Delayed insert | 149 | Waiting for INSERT | | | 105 | DELAYED | localhost | eximstats | Delayed insert | 149 | Waiting for INSERT | | | 119 | root | localhost | | Query | 0 | | show processlist | +-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+ My websites using MySQL almost all say: Error establishing a database connection Another say: Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'website_USER'@'localhost' (using password: NO) This is my current my.cnf: [mysqld] #datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock #pid-file=/var/lib/mysql/mysqld.pid #skip-bdb #skip-innodb #err-log=/var/log/mysql.log #bind-address = server.escalehost.com log-bin = /var/lib/mysql/log-bin #innodb_buffer_pool_size=2M #innodb_additional_mem_pool_size=500K #innodb_log_buffer_size=500K #innodb_thread_concurrency=2 #max_connections=125 #table_cache=2500 #thread_cache_size=100 #thread_stack=250K #wait_timeout=10 #join_buffer=5M #myisam_sort_buffer_size=15M #query_cache_size=15M #read_rnd_buffer_size=5M max_allowed_packet = 64M #open_files_limit=8602 #[client] #port = 3306 #socket=/var/lib/mysql/mysql.sock #[mysql.server] #user=mysql #basedir=/var/lib [mysqld_safe] #socket=/var/lib/mysql/mysql.sock #err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid I commented most of it to return it to its simplest version... How can I make the web side to connect to mysql?

Mistake during Oracle 11g PITR Posted: 11 Aug 2013 11:59 AM PDT I tried using set time until.. and mis-typed the date. Can anyone help me understand how to get my backups into a manageable state? After the accidental recover, most of my backupset disappeared. I recovered them and used 'catalog recovery area' .. and they're listed in 'list backupset'. But something still isn't right. When I do a PITR now, I get messages that my dbf files aren't available and... the 'list backupset' seems to show backupsets. But they are listed differently than the files which weren't included in the 'bad' recovery. Gists with the error and the list of backupsets are here https://gist.github.com/akinsgre/5561254

"Arithmetic overflow" when initializing SQL Server 2012 replication from backup Posted: 11 Aug 2013 04:59 AM PDT I'm initializing SQL Server replication from a backup, by following instructions from here: http://www.mssqltips.com/sqlservertip/2386/initialize-sql-server-replication-using-a-database-backup/ ...but, when I execute USE MyDatabase GO EXEC sp_addsubscription @publication = MyDatabasePublication, @subscriber = 'AMAZONA-XXXXXXX', @destination_db = MyDatabase, @sync_type = 'initialize with backup', @backupdevicetype ='disk', @backupdevicename = 'D:\Temp\MyDatabasepublication.bak' I get the following error: Msg 8115, Level 16, State 2, Procedure sp_MSsetupnosyncsubscriptionwithlsn, Line 237 Arithmetic overflow error converting expression to data type nvarchar. Any idea why, or at least where can I find this stored procedure to troubleshoot further? SQL Server 2012, Standard Edition. UPDATE: It looks like that the problem is caused by the fact that database was created using SQL Server 2008R2 and then attached here. Anyway, still need a solution for it.

PLSQL : DBMS Jobs - parallelization? Posted: 11 Aug 2013 03:30 PM PDT I am looping over a cursor and executing a stored procedure; how do I parallelize this process through dbms.jobs? for rec in select column from table1 loop execute stored_procedure( rec.column ); end loop;

Can't connect to SQL Server Windows 7 Posted: 11 Aug 2013 09:59 AM PDT TITLE: Connect to Server Cannot connect to localhost. ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476 I keep getting this error when trying to connect to sql server. I also ran a repair and I got this error. Could this be the problem and do you have any idea how I can repair it. I have looked all over Google and tried quite a few solutions with no luck. I am Using SQL Server 2008 R2 Developer Edition

Alternative tools to export Oracle database to SQL Server? Posted: 11 Aug 2013 08:59 AM PDT I've got an Oracle database that I need to export (schema and data) to SQL Server. I am trying the Microsoft SQL Server Migration Assistant for Oracle, but it is horribly slow, grossly inefficient and very un-user-friendly, e.g. I was having problems connecting to the SQL Server DB during data migration - but it still spent ~5 minutes preparing all the data before attempting a connection to SQL Server, then when it failed, the 5 minutes of preparatory work were wasted. Right now, I'm just trying to connect to another Oracle DB using this tool, I left it overnight and came back this morning, and it's still stuck on 19% of "Loading objects..." And this is on a machine with a good 18GB RAM, of which maybe 8.5 GB currently in use. Task Manager shows me that SSMAforOracle.exe is using 0 CPU, 0 PF Delta, and no change whatsoever in memory usage. In other words: frozen stiff. Absolutely intolerable. Are there any other tools out there that can migrate an Oracle DB to SQL Server a little more efficiently?

MySQL auto increment problem with deleting rows / archive table Posted: 11 Aug 2013 05:59 AM PDT A hosted server is running "maintenance" each weekend. I am not privy to the details. In a database on this server there is a MyISAM table. This table never holds more than 1000 rows and usually much less. It is MyISAM so that the auto increment does not reset (and with so few rows it really doesn't matter). Rows are regluarly deleted from this table and moved to an archive table (1M rows). The problem is lately the auto increment has "rolled back" slightly after each maintenance. Is there any easy way to verify the auto increment of the insert table by reading the max id from both the insert and the archive table? I'd rather not verify before each insert unless that is the only solution. Here are the basic table layouts: CREATE TABLE x ( xid int(10) unsigned NOT NULL AUTO_INCREMENT, //snip PRIMARY KEY (xid) ) ENGINE=MyISAM AUTO_INCREMENT=124 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE xhistory ( xid int(10) unsigned NOT NULL DEFAULT '0', //snip PRIMARY KEY (xid) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Far from perfect workaround: (this was somewhat urgent, I had to manually update over 100 rows) select xid from xhistory where x=? Check if just inserted row in x exists in history. If it does: select greatest(max(x.xid),max(xhistory.xid)) as newval from x,xhistory Find a new id. INSERT INTO x SELECT * FROM x AS iv WHERE iv.xid=? ON DUPLICATE KEY UPDATE xid=? And update our row with this id.

Empty LONGTEXT or NULL? Posted: 11 Aug 2013 10:59 AM PDT In my MySQL DB I have one field called html_contents that contains all the html to be shown in a webpage. Obviously the html could be huge, and certaintly bigger than 64KB, therefore I decided to use LONGTEXT rather than VARCHAR. When the field is not set or left empty would you say it is better (alias more efficient for the DB) to set it to NULL or to empty string? I read this: When to use NULL and when to use an empty string? but it talks about empty strings in general (probably small strings and not LONGTEXT). I was wondering if with LONGTEXT is a different story, whether it saves a lot of space or execution time to use NULL instead of leaving empty LONGTEXT around.

Best embed/reference/field strategy for many inserts (mongo) Posted: 11 Aug 2013 07:59 AM PDT I'm building a gaming backend with Mongo, and have some issues on how to best design the schema to maximize performance and database size. My models: User Match -ReferenceMany (User) -ReferenceMany (Score) Score -ReferenceOne (Match) -ReferenceOne (User) It takes 2 users to start a match. Say we get 10.000 users, and all play one match each day against another user, we get 5000 games a day. Each match has three turns, which gives 6 scores, so for 5000 games we get 30,000 scores. These scores can be inserted simultaneously (in each game), so I have to make sure one user doesn't overwrite another users score. I believe I've solved that by having the scores in their own collection, and embedding them on the match, like so: $score = new Score(); $score->setUser($user); $score->setScore($playerScore); $score->setGame($game); $score->setMatch($match); // Save score. $dm->persist($score); // Add score to match. $match->addScores($score); My issue with this approach though is that it takes 6 queries just to insert a score (there are some validating queries before the above code). With 30.000 scores a day, that's a whole lotta queries. My initial thought was to just add the scores to the match as an array, but what will happen the if two users submit at the exakt same time, updating the same match? I'd be really interested in hearing some opinions on this. Let me know if I need to clarify anything. I'm using Symfony2 with Doctrine ODM if that helps.

You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL 2012] Pencentage

---

Pencentage Posted: 10 Aug 2013 09:23 PM PDT I have 3 tables:Student table(StudentNumber,Name,Contact,Address,DateOfBirth,YearEnrolled,Year Of Study)Absence table(SubjectCode,StudentNumber, DateOfAbsence)Subject table(SubjectCode, Subject Name,Description)I would need to display StudentNumber, Name, SubjectCode and the pecentatage of Date of Absence)taking that there are 10 lessonHow can i disply the above.

You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[T-SQL] Stumped on Query

---

Stumped on Query Posted: 10 Aug 2013 12:45 PM PDT I have two tables with the following columns:[b]Table 1[/b]customer_id123456[b]Table 2[/b]id, customer_id, product, date1, 1, A, 1/1/20132, 1, A, 5/1/20133, 1, A, 6/1/20134, 1, C, 6/1/20135, 2, B, 7/1/20136, 3, C, 8/1/20137, 3, A, 9/1/2013[b]What I need is the following: [/b]I need to find the maximum date for each customer from Table 2. If there is a tie for the maximum date (2 or more), I need to display all the records that are tied. Also if there is more than one record I need to add an instance field to the results that will count up the number of instances (1, 2, 3, etc). If there is only one returned result from Table 2 for the customer, the instance would default to 1.[b]Results table[/b]customer_id, date, product, instance1, 6/1/2013, A, 11, 6/1/2013, C, 22, 7/1/2013, B, 13, 8/1/2013, A, 1I hope this is clear. I cannot do the max function it appears because it only shows one record and I cannot figure out how to get the instance column.Thank you in advance and let me know if I can help to clear anything up that may not be clear here.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server 2008 issues] SQL to do calc on different rows

---

• SQL to do calc on different rows
• subqueries
• Job hangs when owner is a Windows login
• Full and transaction log backup chain breakup
• What to do when temdb is full ?.
• Having problem with memory in sql server
• how to find views from remote servers

SQL to do calc on different rows Posted: 10 Aug 2013 05:05 AM PDT Hi, allIs there any anylytical function in ss so I can achive result like belowo doing calculation on values from different rows? Thanks all for help, I'm sure it should be, I think there is one in oracle ,but I could not find in ss.Thanks allMario[code="other"]select * into #t1 from (select 'alpha' c1, 100 c2 unionselect 'bravo' c1, 120 c2 unionselect 'charlie' c1, 230 c2 unionselect 'delta' c1, 360 c2 unionselect 'whiskey' c1, 500 c2 ) b--delta1 = row2.c2 - row1.c1--delta2 = row2.c3 - row1.c2 etc..c1 | c2 | delta |alpha | 100 | 20 |bravo | 120 | 110 |charlie | 230 | 130 |delta | 360 | 140 |whiskey | 500 | 0 |[/code]

subqueries Posted: 10 Aug 2013 03:53 PM PDT hi i have done a subqueries but but it is not displaying any records.select SubjectCode,SubjectName,Descriptionfrom SubjectWhere SubjectCode In(select SubjectCodefrom Absencewhere SubjectCode IN (select SubjectCodefrom AbsenceWhere SubjectCode like 'BS%' and LessonDate = '20130305')); Anyone can help me.

Job hangs when owner is a Windows login Posted: 09 Aug 2013 10:00 PM PDT We have a shrink job from maintenance plan. When the owner is sa, job gets completed within 2 min. But if the owner is changed to a windows login, it continue running. Any idea why this happen?I guess, this might be permission issue. The windows account might be permission issue on the drives where the database files are located. Please advice.

Full and transaction log backup chain breakup Posted: 10 Aug 2013 06:27 AM PDT Hi ,let us suppose i take full backup f1 and now suppose i take 3 transaction log backup t1,t2,t3 .Now i can restore this transaction log backup provided i had restored full backup f1. But suppose before T3 i take full backup F2 then i cannot restore T3 untill F2 is restored.But why this doesn`t happens in log shipping .I mean in log shipping even if we take full backups and if u have all transaction log backup`s provided there where no breakup`s in log backup`s chain, then to log shipping continues and it doesnt required to apply latest full backup on server(dr server)

What to do when temdb is full ?. Posted: 10 Aug 2013 03:42 AM PDT Experts,Tempdb is almost full.No open trans in any of the DBNo blockings.What are things that we need to check and what's the solution. ?Thanks in advance.Smith.

Having problem with memory in sql server Posted: 10 Aug 2013 05:43 AM PDT Hi friends at one of our client side we have 4 sql server instances one default and 3 named installed on a single physical server box. It has 32GD ram. 8,8,8,and 4 GB of memory is allocated to sql server.Sometimes memory consumption reaches upto 30/31 gb and i am not able to determine what is eating out memeory.It is not queires because i had use sysprocess and always got random query.Plz help to find out what is consuming memory.Plz tell me where to look for it.what to look for it.which queries/dmvs to use

how to find views from remote servers Posted: 10 Aug 2013 03:22 AM PDT Hi all,we had performance trouble with a view from a remote server. A simple change in the CREATE query solved this.My questions are: * is there a way to list all views that use a server in their query?* " " to see which impact each of these view has on server performance?Sorry for my vague questions, I am happy for any hintBest RegardsArthur

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 - General / SQL Server 2008 To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610