Showing posts with label sample. Show all posts
Showing posts with label sample. Show all posts

Friday, March 30, 2012

Help opening .MDF file

I'm trying to follow the article

http://aspnet.4guysfromrolla.com/articles/031506-1.aspx. It gives a sample to download at the bottom which I did. I'm trying to take a look at the Stored Proc that were created in the PagingData.mdf. The only thing included in the App_Data folder is PagingData.mdf file. How can I open the DB or attach it to my SQL2005 server? There is no .ldf file included in thesample so the attaching is failing.

You have two possible options connect the MDF to Management Studio and see if you can access everything or Backup and Restore the database SQL Server will receate everything including stored procs and LDF. Hope this helps.

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

|||

You can attach the MDF file to the SQL Server 2005 by using the simple command

SP_ATTACH_DB <Give your DB Name, any name is fine>,<Location of the DB file>

and programatically you can connect to the MDF file using code as described in

http://ambarishganguly.blogspot.com/2006/10/sql-server-express-xcopy.html

Hope this helps.

Thanks

Ambarish

sql

Monday, March 26, 2012

help on query

I need advice on how to approach this. To simplify with a sample, below is a
list of various calculations based from ITEMS table:
ITEM_NO FIELD_CALCULATION
123 cost*qty
111 cost-discount
222 sales*discount
333 cost-freight
etc...
There are 20 additional line items with different calculations.

From my SP, how do I incorporate the above to say:
SELECT ??
INTO #tmp1
FROM GL_ACCOUNT GL,
ITEMS I
WHERE GL.ITEM_NO = I.ITEM_NO

TIA!
BobHi

You could try something like:

SELECT I.Item_No,
CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
WHEN 111 THEN GL.cost-GL.discount
WHEN 222 THEN GL.sales*GL.discount
WHEN 333 THEN GL.cost-GL.freight
END AS [Calculation]
FROM GL_ACCOUNT GL,
JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO

John

"B" <no_spam@.no_spam.com> wrote in message
news:ZrydnROKGqA3psrfRVn-uQ@.rcn.net...
>I need advice on how to approach this. To simplify with a sample, below is
>a
> list of various calculations based from ITEMS table:
> ITEM_NO FIELD_CALCULATION
> 123 cost*qty
> 111 cost-discount
> 222 sales*discount
> 333 cost-freight
> etc...
> There are 20 additional line items with different calculations.
> From my SP, how do I incorporate the above to say:
> SELECT ??
> INTO #tmp1
> FROM GL_ACCOUNT GL,
> ITEMS I
> WHERE GL.ITEM_NO = I.ITEM_NO
> TIA!
> Bob
>|||I was hoping to use the table created as a source without having to hardcode
since it will be used by other SP.

Thank you for your time.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:42578d55$0$26738$db0fefd9@.news.zen.co.uk...
> Hi
> You could try something like:
> SELECT I.Item_No,
> CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
> WHEN 111 THEN GL.cost-GL.discount
> WHEN 222 THEN GL.sales*GL.discount
> WHEN 333 THEN GL.cost-GL.freight
> END AS [Calculation]
> FROM GL_ACCOUNT GL,
> JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO
> John
> "B" <no_spam@.no_spam.com> wrote in message
> news:ZrydnROKGqA3psrfRVn-uQ@.rcn.net...
> >I need advice on how to approach this. To simplify with a sample, below
is
> >a
> > list of various calculations based from ITEMS table:
> > ITEM_NO FIELD_CALCULATION
> > 123 cost*qty
> > 111 cost-discount
> > 222 sales*discount
> > 333 cost-freight
> > etc...
> > There are 20 additional line items with different calculations.
> > From my SP, how do I incorporate the above to say:
> > SELECT ??
> > INTO #tmp1
> > FROM GL_ACCOUNT GL,
> > ITEMS I
> > WHERE GL.ITEM_NO = I.ITEM_NO
> > TIA!
> > Bob|||Hi

Creating a view would overcome that problem.

John

"B" <no_spam@.no_spam.com> wrote in message
news:JMSdnZXT2LmwK8rfRVn-iQ@.rcn.net...
>I was hoping to use the table created as a source without having to
>hardcode
> since it will be used by other SP.
> Thank you for your time.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:42578d55$0$26738$db0fefd9@.news.zen.co.uk...
>> Hi
>>
>> You could try something like:
>>
>> SELECT I.Item_No,
>> CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
>> WHEN 111 THEN GL.cost-GL.discount
>> WHEN 222 THEN GL.sales*GL.discount
>> WHEN 333 THEN GL.cost-GL.freight
>> END AS [Calculation]
>> FROM GL_ACCOUNT GL,
>> JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO
>>
>> John
>>
>> "B" <no_spam@.no_spam.com> wrote in message
>> news:ZrydnROKGqA3psrfRVn-uQ@.rcn.net...
>> >I need advice on how to approach this. To simplify with a sample, below
> is
>> >a
>> > list of various calculations based from ITEMS table:
>> > ITEM_NO FIELD_CALCULATION
>> > 123 cost*qty
>> > 111 cost-discount
>> > 222 sales*discount
>> > 333 cost-freight
>> > etc...
>> > There are 20 additional line items with different calculations.
>>> > From my SP, how do I incorporate the above to say:
>> > SELECT ??
>> > INTO #tmp1
>> > FROM GL_ACCOUNT GL,
>> > ITEMS I
>> > WHERE GL.ITEM_NO = I.ITEM_NO
>>> > TIA!
>> > Bob
>>>>>>>
>>|||Have you ever had a software engineering course? Probably not, because
this approach is a violaiton of the principle of cohesion and it
confuses data and code.

Monday, March 19, 2012

Help needed with insert Statement

Hi,

I am trying to insert the follows rows to my production database... and this the sample data

RowPlan PART_ID FUND_ID TOT_ACT1 TOT_ACT2 Number Num1170925 129602759 19765P471 BB4928.47 CT0.00 DV26.30 GL153.75 TF0.00 WD0.00 OT0.00 EB5108.52 205.0110 24.04 206.0720 24.79 2170925 129602759 35472P406 BB2663.64 CT325.00 DV87.46 GL26.42 TF530.92 WD0.00 OT0.00 EB3633.44 189.0450 14.09 254.6210 14.27 3170925 129602759 LOAN BB1506.88 CT0.00 DV25.48 GL0.00 TF-530.92 WD0.00 OT0.00 EB1001.44 1506.88 1.00 1001.44 1.00 4170925 148603737 19765L587 BB25.14 CT0.00 DV0.46 GL-0.45 TF0.00 WD0.00 OT0.00 EB25.15 5.3830 4.67 5.4790 4.59 5170925 148603737 19765P471 BB7.48 CT0.00 DV0.05 GL0.23 TF0.00 WD0.00 OT0.00 EB7.76 0.3110 24.04 0.3130 24.79 6170925 148603737 35472P208 BB12.53 CT0.00 DV0.28 GL0.09 TF0.00 WD0.00 OT0.00 EB12.90 0.9360 13.39 0.9570 13.48 7170925 148603737 35472P604 BB7.48 CT0.00 DV0.24 GL0.15 TF0.00 WD0.00 OT0.00 EB7.87 0.4720 15.85 0.4870 16.16 8170925 148603737 315805549 BB29.72 CT0.00 DV0.00 GL2.15 TF0.00 WD0.00 OT0.00 EB31.87 1.5320 19.40 1.5320 20.80 9170925 148603737 197199102 BB5.00 CT0.00 DV0.06 GL0.27 TF0.00 WD0.00 OT0.00 EB5.33 0.1650 30.32 0.1670 31.94

So the number of rows in this table is 1007 right now my insert query inserts all the data but excepts LOAN and i want Loans inserted in a seperate column in my production dataabse but thats not happening so can some one pls take a look at this query and see whats wrong... My query is as follows

1INSERT INTO Statements..ParticipantPlanFundBalances12(3PlanId,4ParticipantId,5PeriodId,6FundId,7 Loans,8--PortfolioId,9Act1,10TotAct1,11Act2,12TotAct2,13Act3,14TotAct3,15Act4,16TotAct4,17Act5,18TotAct5,19Act6,20TotAct6,21Act7,22TotAct7,23Act8,24TotAct8,25Act9,26TotAct9,27Act10,28TotAct10,29Act11,30TotAct11,31Act12,32TotAct12,33Act13,34TotAct13,35Act14,36TotAct14,37Act15,38TotAct15,39Act16,40TotAct16,41Act17,42TotAct17,43Act18,44TotAct18,45Act19,46TotAct19,47Act20,48TotAct20,49OpeningUnits,50OPricePerUnit,51ClosingUnits,52CPricePerUnit,53AllocationPercent54)55SELECT56cp.PlanId,57p.ParticipantId,58@.PeriodId,59CaseWhen a.FUND_ID <>'LOAN'Then f.FundIdELSE 0END,60CASEWhen a.FUND_ID ='LOAN'Then'LOAN'END as Loanfunds,61--planinfo.PortfolioId,62CaseWHEN a.ACT_ID1 ='BB'Then 1END,63a.TOT_ACT1,64CaseWHEN a.ACT_ID2 ='CT'Then 2END,65a.TOT_ACT2,66CASEWhen a.ACT_ID3 ='DV'then 3END,67a.TOT_ACT3,68CASEWhen a.ACT_ID4 ='GL'Then 4End,69a.TOT_ACT4,70CAseWhen a.ACT_ID5 ='TF'THEN 5END,71 a.TOT_ACT5,72CASEWhen a.ACT_ID6 ='WD'THEN 6END,73a.TOT_ACT6,74CASEWHEN a.ACT_ID7 ='OT'THEN 7END,75a.TOT_ACT7,76CASEWhen a.ACT_ID8 ='EB'THEN 8END,77a.TOT_ACT8,78a.ACT_ID9,79a.TOT_ACT9,80a.ACT_ID10,81a.TOT_ACT10,82a.ACT_ID11,83a.TOT_ACT11,84a.ACT_ID12,85a.TOT_ACT12,86a.ACT_ID13,87a.TOT_ACT13,88a.ACT_ID14,89a.TOT_ACT14,90a.ACT_ID15,91a.TOT_ACT15,92a.ACT_ID16,93a.TOT_ACT16,94a.ACT_ID17,95a.TOT_ACT17,96a.ACT_ID18,97a.TOT_ACT18,98a.ACT_ID19,99a.TOT_ACT19,100a.ACT_ID20,101a.TOT_ACT20,102a.UNIT_OP,103a.PRICE_OP,104a.UNIT_CL,105a.PRICE_CL,106IsNull(i.ALLOC_PER1,'0.00')107FROM108ASDBF a109110--Derive the unique Plan Id111INNERJOIN Statements..ClientPlan cp112ONa.PLAN_NUM = cp.ClientPlanId113AND114cp.ClientId = @.ClientId115--Derive the unique ParticipantId from the Participant table116INNERJOIN Statements..Participant p117ONa.PART_ID = p.PartId118-- Derive the unique fund id from the Fund Table119INNERJOIN Statements..Fund f120ONa.FUND_ID = f.Cusip121OR122a.FUND_ID = f.Ticker123OR124a.FUND_ID = f.ClientFundId125LeftOuter JOIN INVSRC i126ONa.FUND_ID = i.INV_ID127AND128a.PLAN_NUM = i.Plan_Number129AND130a.PART_ID = i.PART_ID131--Get the unique portfolio name ffor the PArticipant Funds..132WHERE133--Ignore rows that failed the scrub.134a.Import = 1135AND136--Import only those that are not already in the ParticipantPlanFundBalances table137NOT EXISTS (138SELECT *139FROM140Statements..ParticipantPlanFundBalances1 pfb141WHERE142pfb.PlanId = cp.PlanId143AND144pfb.ParticipantId = p.ParticipantId145AND146pfb.PeriodId = @.PeriodId147AND148pfb.FundId = f.FundId149)

any help is appreciated.

Regards

Karen

Is there any error msg? Also can you explain this:

INNERJOIN Statements..Fund f ON a.FUND_ID = f.Cusip OR a.FUND_ID = f.Ticker ORa.FUND_ID = f.ClientFundId

|||

Thanks for your answer, no i am not getting any error message

INNERJOIN Statements..Fund f ON a.FUND_ID = f.Cusip OR a.FUND_ID = f.Ticker ORa.FUND_ID = f.ClientFundId
and this one means... i am getting the fundId from that table and inserting it to the PlanFundbalances..

for example in the sample data i have provided.. FUND_ID can be a 5 letter word saying DODGX,(Ticker) or some alphanumeric data whose length is and ClientFund(what ever the client wants and not in our database)

So fUND_ID 19765P471 will have a fundId of 15 or whatever..

But the word Loan isnt there in the Statements..Fund f table

Hope this helps.

Regards

Karen

|||

The way to debug would be to selectively comment out lines.. comment out the INSERT INTO line and just run the SELECT part. Start with the first ASDBF JOIN with ClientPlan and see if you get results. Then include the join with Participant and see if you get expected results..keep including each of the tables and see which part of the query is throwing you off. Otherwise there's really no way for us to tell what the issue is.. unless we see some sample data from each of the tables in the query and expected data into the final table...

|||

This is the first 10 rows of my final table..

1178241875271041NULLNULL1425.320020.000030.0000417.400050.000060.000070.00008442.720000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.486050.12008.486052.170025.002178241875276204NULLNULL1120.090020.000034.040042.100050.000060.000070.00008126.230000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.323022.56005.498022.96000.0031782418752710302NULLNULL1119.590020.000031.6900410.410050.000060.000070.00008131.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.328014.36008.436015.610010.0041782418752711010NULLNULL1125.060020.000030.330048.830050.000060.000070.00008134.220000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00004.344028.79004.355030.820010.0051782418752711024NULLNULL1126.850020.000030.7700410.070050.000060.000070.00008137.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00003.003042.24003.020045.590010.0061782418752712040NULLNULL1121.380020.000030.0000410.520050.000060.000070.00008131.900000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.340022.73005.340024.700010.0071782418752714449NULLNULL1123.490020.000030.000049.800050.000060.000070.00008133.290000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00006.402019.29006.402020.820010.0081782418752714463NULLNULL1685.230020.000032.21004-75.820050.000060.000070.00008611.620000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000029.384023.320029.490020.740025.009178241875473493NULLNULL14320.20002110.0000349.35004-82.23005-210.800060.000070.000084186.520000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000443.55209.7400438.38009.550010.0010178241875473504NULLNULL14650.94002110.00003207.8800432.47005-648.680060.000070.000084352.610000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000305.180015.2400284.113015.320010.00

Suppose if PartID 18752 had loans i want the information to be displayed like in Line number 9

1178241875271041NULLNULL1425.320020.000030.0000417.400050.000060.000070.00008442.720000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.486050.12008.486052.170025.002178241875276204NULLNULL1120.090020.000034.040042.100050.000060.000070.00008126.230000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.323022.56005.498022.96000.0031782418752710302NULLNULL1119.590020.000031.6900410.410050.000060.000070.00008131.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00008.328014.36008.436015.610010.0041782418752711010NULLNULL1125.060020.000030.330048.830050.000060.000070.00008134.220000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00004.344028.79004.355030.820010.0051782418752711024NULLNULL1126.850020.000030.7700410.070050.000060.000070.00008137.690000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00003.003042.24003.020045.590010.0061782418752712040NULLNULL1121.380020.000030.0000410.520050.000060.000070.00008131.900000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00005.340022.73005.340024.700010.0071782418752714449NULLNULL1123.490020.000030.000049.800050.000060.000070.00008133.290000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.00006.402019.29006.402020.820010.0081782418752714463NULLNULL1685.230020.000032.21004-75.820050.000060.000070.00008611.620000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000029.384023.320029.490020.740025.00917824 18752 7 0 LOAN other columns10178241875473493NULLNULL14320.20002110.0000349.35004-82.23005-210.800060.000070.000084186.520000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000443.55209.7400438.38009.550010.0011178241875473504NULLNULL14650.94002110.00003207.8800432.47005-648.680060.000070.000084352.610000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.0000305.180015.2400284.113015.320010.00

I will try debugging the sproc and see what i can acheive

Regards,

Karen

|||

ndinikar hit it spot on. Are there any records in the ASDBF table that have a FUND_ID ='LOAN'?

If not, that's your problem.

If yes, then one of the joins you have is filtering them out.

|||

Yes i do have 18 rows of Data where FUND_ID = 'LOAN'

|||

after debugging it...

When i include this Join

JOIN Statements..Fund f

ON a.FUND_ID= f.Cusip

OR

a.FUND_ID= f.Ticker

OR

a.FUND_ID= f.ClientFundId

i am getting a problem and i solved it by giving

LeftOuterJOIN Statements..Fund f

ON a.FUND_ID= f.Cusip

OR

a.FUND_ID= f.Ticker

OR

a.FUND_ID= f.ClientFundId

Thanks a lot...

Regards

Karen