SQLServerCentral / SQL Server 2005 / Development

Android Development

iamsql1 — Sun, 17 Feb 2013 04:28:52 GMT

Hi, i am newbie to Andorid development .. suggest me good material to this journeythanks

Best code for generating sequence numbers

jaffar.yelavalli — Thu, 12 Nov 2009 10:36:59 GMT

Hi All, We have a requirement where we need to generate IDs from a table.For each kind of ID we have a row defined with a name.ex Table: IDName next_valueShipID 1PackingID 1What would be best code sequence where we would end up with no deadlocks, blockings or duplicates IDs.Tweaking of code I am looking for is: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN select @nextvalue = next_value from table_num_scheme where name = 'ShipID' update table_num_scheme set next_value = (next_value + 1) where name = 'ShipID' COMMIT TRAN GRAB_SHP_NBR SET TRANSACTION ISOLATION LEVEL READ COMMITTED Any other suggestions to generate the sequence numbers from the same row.

Pulling Filename from Windows directory into table

SQLSeTTeR — Tue, 21 Jul 2009 17:56:59 GMT

What is the best way to go about checking a windows directory and inserting file names into a SQL table? Looking to create an control table for incoming files. Dates, rowcounts, etc. Thanks-tjm

stored procedures in a database

sandhyarao49 — Sat, 09 Aug 2008 22:11:39 GMT

Hi to all,I have a small doubt.If we want to know the see all the tables in a database we normally use sp_tables.I want to see all the stored procedures ina database. how we can see all the stored procedures in a database.Thanks,sandhya

I need to create an XML file populated by fields data(eg: SQL Server DB table)

vsmeruga — Tue, 14 May 2013 05:29:10 GMT

I have the following XMLSchema template:<?xml version="1.0" encoding="UTF-8"?><DataMarketsFeed xmlns="http://www.test.co.uk/XMLSchema/DataMarketsFeed-v1-2" xmlns:cmn="http://www.test.co.uk/XMLSchema/DataFeedCommon-v1-2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><DataFeedHeader xmlns="http://www.test.co.uk/XMLSchema/DataFeedCommon-v1-2"><FeedTargetSchemaVersion>1.2</FeedTargetSchemaVersion><Submitter><SubmittingFirm>123456</SubmittingFirm></Submitter><ReportDetails><ReportCreationDate>2009-03-12</ReportCreationDate><ReportIdentifier>March09</ReportIdentifier></ReportDetails></DataFeedHeader><DataMarketsFeedMsg><CoreItemsMkt xmlns="http://www.Test.co.uk/XMLSchema/DataMarketsFeed-v1-2"><FirmDataRef>123789</FirmDataRef><TransRef>123789</TransRef><Cancellation>false</Cancellation><Status>N</Status></CoreItemsMkt><Transaction><ReportingFirmId><ReportingFirmCode type="R">123456</ReportingFirmCode></ReportingFirmId><TradingDate>2009-02-27</TradingDate><TradingTime>08:02:00</TradingTime><BuySell>B</BuySell><TradingCapacity>P</TradingCapacity><InstrumentIdentification> <InstrumentIdentificationCode type="A">R</InstrumentIdentificationCode> </InstrumentIdentification><MaturityExerciseDeliveryDate>2009-03-31</MaturityExerciseDeliveryDate><TypeOfDerivative>F</TypeOfDerivative><PriceMultiplier>10</PriceMultiplier><UnitPrice>3836</UnitPrice><PriceNotation>GBP</PriceNotation><Quantity>3.00</Quantity><CounterpartyOne><CounterpartyCode type="I">QWERTYU</CounterpartyCode></CounterpartyOne><CounterpartyTwo> <CounterpartyCode type="I">ASB00037</CounterpartyCode> </CounterpartyTwo><VenueIdentificationTypeCode>M</VenueIdentificationTypeCode> <UnitPriceTypeCode>C</UnitPriceTypeCode> </Transaction></DataMarketsFeedMsg> </DataMarketsFeed><CoreItemsMkt xmlns="http://www.Test.co.uk/XMLSchema/DataMarketsFeed-v1-2"><FirmDataRef>123789</FirmDataRef><TransRef>123789</TransRef><Cancellation>false</Cancellation><Status>N</Status></CoreItemsMkt><Transaction><ReportingFirmId><ReportingFirmCode type="R">123456</ReportingFirmCode></ReportingFirmId><TradingDate>2009-02-27</TradingDate><TradingTime>08:02:00</TradingTime><BuySell>B</BuySell><TradingCapacity>P</TradingCapacity><InstrumentIdentification> <InstrumentIdentificationCode type="A">R</InstrumentIdentificationCode> </InstrumentIdentification><MaturityExerciseDeliveryDate>2009-03-31</MaturityExerciseDeliveryDate><TypeOfDerivative>F</TypeOfDerivative><PriceMultiplier>10</PriceMultiplier><UnitPrice>3836</UnitPrice><PriceNotation>GBP</PriceNotation><Quantity>3.00</Quantity><CounterpartyOne><CounterpartyCode type="I">QWERTYU</CounterpartyCode></CounterpartyOne><CounterpartyTwo> <CounterpartyCode type="I">ASB00037</CounterpartyCode> </CounterpartyTwo><VenueIdentificationTypeCode>M</VenueIdentificationTypeCode> <UnitPriceTypeCode>C</UnitPriceTypeCode> </Transaction></DataMarketsFeedMsg> </DataMarketsFeed>Can some one help me with a SQL Server 2008 - SQL Query to generate the above format ?. Attached the XML output file format as well.

SSIS package run error

vsmeruga — Wed, 15 May 2013 03:34:16 GMT

Hi AllI am trying to run a package which reads data from Oracle using Select Statement. used "Oracle provider for OLE DB" and got below error message. SSIS package "ClearVision.dtsx" starting.Information: 0x4004300A at Clear Vision Extract, SSIS.Pipeline: Validation phase is beginning.Information: 0x4004300A at Clear Vision Extract, SSIS.Pipeline: Validation phase is beginning.Information: 0x40043006 at Clear Vision Extract, SSIS.Pipeline: Prepare for Execute phase is beginning.Information: 0x40043007 at Clear Vision Extract, SSIS.Pipeline: Pre-Execute phase is beginning.Information: 0x4004300C at Clear Vision Extract, SSIS.Pipeline: Execute phase is beginning.Error: 0xC0202009 at Clear Vision Extract, OLE DB Source [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4B.An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E4B Description: "Accessor is not a parameter accessor.".Error: 0xC0047038 at Clear Vision Extract, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.Information: 0x40043008 at Clear Vision Extract, SSIS.Pipeline: Post Execute phase is beginning.Information: 0x4004300B at Clear Vision Extract, SSIS.Pipeline: "component "OLE DB Destination" (16)" wrote 0 rows.Information: 0x40043009 at Clear Vision Extract, SSIS.Pipeline: Cleanup phase is beginning.Task failed: Clear Vision ExtractWarning: 0x80019002 at ClearVision: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.SSIS package "ClearVision.dtsx" finished: Failure.Can some one help me to resolve the error.

Alternative to using cursor

javib — Fri, 10 May 2013 12:27:20 GMT

Hi,I have a table that I need to update based on the following conditions.If the inventory type is physical, use that date as the physical inventory date.Otherwise, use the date of the last physical inventory taken before this one. I'm currently using a cursor for this, but the performance of my query suffers because of it.Below are the table and queries I'm using to update it.Is there anything else I can use that's faster?Table[code="sql"]create table #inv (Rep_LName nvarchar (50),Rep_FName nvarchar (50),Rep_ID nvarchar (50),Rep_Email nvarchar (100),Rep_Status nvarchar (50),Rep_BU nvarchar (50),Sales_Force nvarchar (50),Territory nvarchar (50),Sample_Eligibility nvarchar (50),DM_Name nvarchar (100),Phys_Inv_Date datetime,Last_Reconciled datetime,Inv_Type nvarchar(50),Days_Since_Last_inv int)[/code]Queries:[code="sql"]update i set i.Inv_Type = h.inventory_type from #inv iinner join inv_header h on i.rep_id = h.rep_id and h.Call_date = (select Max(Call_Date) from inv_header i2 where i2.rep_id = i.rep_id)where i.inv_type is nulldeclare Inventory_info cursor for select distinct rep_ID, call_date, inventory_typefrom inv_headerwhere rep_id in (select rep_id from #inv)Order by rep_ID, call_date desc, inventory_type descdeclare @call_date datetimedeclare @rep_ID nvarchar(50)declare @inventory_type nvarchar(50)declare @ls_Sql as nvarchar(max)declare @param as nvarchar(max)select @ls_Sql=''select @param=''-- open cursoropen Inventory_infofetch next from Inventory_info into @rep_ID, @call_date, @inventory_typewhile (@@fetch_status = 0)begin --use parameterized dynamic sql SET @param='@rep_ID nvarchar(50),@call_date DATETIME,@inventory_type nvarchar(50)' SET @ls_Sql='update #inv set Phys_Inv_Date = case when inv_type = ''physical'' and @inventory_type = ''physical'' then @call_date else b.call_date end from #inv a INNER JOIN (select top 1 call_date, rep_id from inv_header where call_date <= @call_date and rep_id = @rep_id and inventory_type = ''physical'' order by call_date desc) b ON a.rep_id = b.rep_id WHERE Phys_Inv_Date IS NULL' --pass parameter to dynamic query exec sp_executesql @ls_Sql,@param,@rep_ID,@call_date,@inventory_type fetch next from Inventory_info into @rep_ID, @call_date, @inventory_typeendclose Inventory_infodeallocate Inventory_info[/code]

sql

asranantha — Mon, 09 Apr 2012 22:42:28 GMT

1)can we call sp into another sp if yes plz give small example?if no plz tell me reason?2)can we call functionn into another function if yes plz give small example?if no plz tell me reason?3)can we call sp in function?if yes plz give small exmp?if no plz tell me reason?4)can we call function in sp?if yes plz give small examp?if no plz tell me reason?

Needed Help

mail.harish.kala — Fri, 10 May 2013 01:10:59 GMT

declare @LinkedServer varchar(15)declare @DatabaseName varchar(20)declare @SQL varchar(250)set @LinkedServer = '[SURE]'set @DatabaseName = 'master'--select * from @LinkedServer.@DatabaseName.sys.databasesset @SQL = 'select * from '+@LinkedServer+'.'+@DatabaseName+'.sys.databases'print @SQLexec @SQL---- Resultselect * from [SURE].master.sys.databasesMsg 203, Level 16, State 2, Line 10The name 'select * from [SURE].master.sys.databases' is not a valid identifier.Working Perfectly:select * from[SURE].master.sys.databases

Creating XML file using Excel Data

A Little Help Please — Fri, 18 Sep 2009 08:20:57 GMT

I need to create an XML file populated by data from a CSV file.I have the following XMLSchema template:[code="xml"]1.1[b]123456[/b][b]IIBS[/b][b]2009-03-12[/b][b]March09[/b][b]123789[/b][b]123789[/b]falseN[b]123456[/b][b]2009-02-27[/b][b]08:02:00[/b]BP[b]Stock[/b]I[b]2009-03-31[/b]F[b]10[/b]3836[b]GBP[/b][b]3[/b][b]QWERTYU[/b]XXXX [/code]All the data highlighted in [b]BOLD[/b] above will come from an excel (csv) file.At the moment as a work around im using a MS Word Mail Merge, this is not ideal, can his be done using a SSIS package?

Difference between varchar(max) and varchar(8000)

jchandramouli — Sun, 01 Feb 2009 23:26:07 GMT

Hi,whats the actual difference between varchar(max) and varchar(8000). Is the difference applies same for nvarchar(max) and nvarchar(8000).Thanks in advance.

In clause is giving problem in dynamic sql.

abhas — Fri, 03 May 2013 00:02:49 GMT

Hi All,I am trying to run below simple query. I am trying to pass comma separated string into my query but its not working. if i pass single value then it works fine. Pklease guide.Declare @DayName varchar(100)--set @DayName = 'Monday'Set @DayName = '''Monday''' +','+ '''Sunday''' +','+' ''Friday'''select * from tbldays where weekday in(@DayName)select @DayNamethanksAbhas.

not able to insert data for selected day.

abhas — Fri, 03 May 2013 06:38:27 GMT

Hi All, My aim is that, i want to insert data into table for selected day only among the selected range of date. I am able to select particular days data in temporary table but while inserting into database table it is not inserting properly. I have written below SP by taking reference from this forum only but data is not going properly. somewhere i am doing mistake.CREATE PROCEDURE [dbo].[Usp_InsertStudWeekly] @StudID INT, @StartDate datetime, @EndDate datetime, @StartTime varchar(20), @EndTime varchar(20), @DayName varchar(100) , @flag bit ASBEGINSET NOCOUNT ON; IF OBJECT_ID('TempDB..#test','U') IS NOT NULL drop table #testcreate table #test (AdvisorID int, startdate datetime, enddate datetime, starttime datetime, endtime datetime,flag bit )insert into #testSELECT @StudID ,@StartDate, @EndDate, @StartTime, @EndTime,0 --select 1, '2012-01-03 00:00:00', '2012-01-06 00:00:00', '2012-01-03 07:00:00', '2012-01-03 08:00:00'INSERT INTO dbo.tblStudentselect StudID, dateadd(day, b.n-1, startdate) as startdate ,Tstart = RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, starttime)), 0),7) , TsEnd = RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, a.N*15, starttime)), 0),7) --, datename(dw,dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, starttime))) ,0 as flag from #test cross join Tally A cross join Tally Bwhere a.N >= 1 and a.N <= datediff(mi, starttime, endtime)/15 and b.N >= 1 and b.N <= DATEDIFF(day, startdate,enddate) + 1 and datename(dw,dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, starttime))) in (@DayName) END--exec Usp_InsertStudWeekly 1,'2013-05-01 00:00:00', '2013-05-31 00:00:00', '2013-05-01 10:00:00', '2013-05-31 14:00:00','Monday',false

CURSOR - running very slow

npyata — Thu, 02 May 2013 05:20:53 GMT

this is completed

Encrypted Stored Procedures

ningaraju.n — Mon, 04 Jan 2010 07:25:11 GMT

Hi Friends,Can you please get the query to list all the Encrypted Stored Procedures in an databaseThanks In Advance

Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Alkesh Khedle — Mon, 17 Mar 2008 01:26:50 GMT

Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction even using with(nolock) option in select queryHI got the above error in production site during high load .When SQL Server2005 used one select query with some joins in it and all table with nolock hint. I am looking for some best solutions to avoid those errors .

Rows Value into Columns

patelmohamad — Tue, 12 Feb 2013 03:16:05 GMT

hi Guys, Need your help to sort out the Query, i have fix columns Field1 to Field10, and Variant Rows may be 1 ,5 ,10 , 20 etc... i want the RowIndex as ColumnName eg [1] ,[2] ,[3] ,[4] etc, and Respective Row and Gate1_Name(Column data) ,Gate2_Name ,Gate3_Name in RowIndex([1]) ,RowIndex([2]) ,RowIndex([3]) Column ,[code="other"]IF NOT OBJECT_ID('tempdb..#RMC_BDCBPW_ReturnTable') IS NULL DROP TABLE #RMC_BDCBPW_ReturnTable SELECT IDENTITY(BIGINT ,1 ,1) AS RowIndex,CONVERT(VARCHAR(22) ,GETDATE() ,114) AS Gate1_Name,CONVERT(VARCHAR(22) ,GETDATE() ,114) AS Gate2_Name,CONVERT(VARCHAR(22) ,GETDATE() ,114) AS Gate3_Name,CONVERT(VARCHAR(22) ,GETDATE() ,114) AS Gate4_Name,CONVERT(VARCHAR(22) ,GETDATE() ,114) AS Gate5_NameINTO #RMC_BDCBPW_ReturnTableTRUNCATE TABLE #RMC_BDCBPW_ReturnTableINSERT INTO #RMC_BDCBPW_ReturnTable(Gate1_Name ,Gate2_Name ,Gate3_Name ,Gate4_Name ,Gate5_Name)SELECT CONVERT( VARCHAR(20) ,GETDATE() + 1 ,121) AS Gate1_Name,CONVERT( VARCHAR(20) ,GETDATE() + 2 ,121) AS Gate2_Name,CONVERT( VARCHAR(20) ,GETDATE() + 3 ,121) AS Gate3_Name,CONVERT( VARCHAR(20) ,GETDATE() + 4 ,121) AS Gate4_Name,CONVERT( VARCHAR(20) ,GETDATE() + 5 ,121) AS Gate5_NameWAITFOR DELAY '00:00:00.700'GO 10-- Execute the Query for Ten timesSELECT * FROM #RMC_BDCBPW_ReturnTableSELECT Tab1.FieldName AS [1] ,Tab2.FieldName AS [2] ,Tab3.FieldName AS [3]FROM(SELECT 1 AS RowNo ,Gate1_Name AS FieldName FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1UNION SELECT 2 AS RowNo ,Gate2_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1UNION SELECT 3 AS RowNo ,Gate3_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1UNION SELECT 4 AS RowNo ,Gate4_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1UNION SELECT 5 AS RowNo ,Gate5_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1) AS Tab1INNER JOIN(SELECT 1 AS RowNo ,Gate1_Name AS FieldName FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2UNION SELECT 2 AS RowNo ,Gate2_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2UNION SELECT 3 AS RowNo ,Gate3_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2UNION SELECT 4 AS RowNo ,Gate4_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2UNION SELECT 5 AS RowNo ,Gate5_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2) AS Tab2ON Tab2.RowNo = Tab1.RowNoINNER JOIN(SELECT 1 AS RowNo ,Gate1_Name AS FieldName FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3UNION SELECT 2 AS RowNo ,Gate2_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3UNION SELECT 3 AS RowNo ,Gate3_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3UNION SELECT 4 AS RowNo ,Gate4_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3UNION SELECT 5 AS RowNo ,Gate5_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3) AS Tab3ON Tab3.RowNo = Tab1.RowNo--Etc....[/code]Output i need described in Attachment(s) Thanks

Suppressing output from a stored procedure.

David McKinney — Wed, 22 Nov 2006 01:24:00 GMT

Hi,

I'm calling DTExec from a stored procedure, using xp_cmdshell (trust me!).

It returns (selects) lots of status information which I'm not interested in, and which is causing problems as it's getting sent back to the web client - which doesn't like it.

(Even when the reporting switch is set to N - for No Reporting)

The only method I've found to 'swallow' the output is by exec-ing into a table which I then drop.

Is there a better way to suppress the output?

Thanks,

Dave McKinney.

select @cmd ='C:\"Program Files (x86)"\"Microsoft SQL Server"\90\DTS\Binn\DTExec.exe /DTS "' + @PackageName + '" /SERVER "' + @MachineName + '" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING N'

CREATE TABLE #output ( Line VARCHAR(8000) )

INSERT #output exec master..xp_cmdshell @cmd

drop table #output

No catalog entry found for partition ID

Bhuvnesh — Mon, 01 Jun 2009 22:56:40 GMT

I get this error message when I try to execute a stored procedure. No catalog entry found for partition ID 8646911294669324288 in database 2. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.If I run the SQL code inside the SPROC it works fine. I have tried recreating, renaming, different database, everything, always the same response (but different partition ID number).DBCC CheckDB never shows any issues.Please Help

Problem with trigger

sd_xxx_sd — Wed, 24 Apr 2013 19:33:47 GMT

Hi Everyone, I have a problem with triggers. Trigger must be fired and update COLUMN_A in TABLE2 after insert COLUMN_B in TABLE 1 . Have anyone some idea ? I'm new in SQL Server and don't know much...

Counting NULL values in a Pivot table

mdonahue — Wed, 08 Apr 2009 08:46:27 GMT

I have the following query:select type, istatus, count(logical_name) from dbo.devicem1 group by type, istatus order by type, istatusThe possible values of istatus are:ARCHIVED, AVAILABLE, BUILD, OPERATIONAL, ORDERED, RECEIVED, REQUESTED, Retired, TEST or the value can be NULL.I am turning this into a Pivot table query and have the following working at this point:select type as 'TYPE', [ARCHIVED] as 'ARCHIVED', [AVAILABLE] as 'AVAILABLE', [BUILD] as 'BUILD', [OPERATIONAL] as 'OPERATIONAL', [ORDERED] as 'ORDERED', [RECEIVED] as 'RECEIVED', [REQUESTED] as 'REQUESTED', [Retired] as 'Retired', [TEST] as 'TEST', [ARCHIVED]+[AVAILABLE]+[BUILD]+[OPERATIONAL]+[ORDERED]+[RECEIVED]+[REQUESTED]+[Retired]+[TEST] as TOTALSfrom (select type, istatus, logical_name from dbo.devicem1 group by type, istatus, logical_name) ppivot (count(logical_name) for istatus in ([ARCHIVED],[AVAILABLE],[BUILD],[OPERATIONAL],[ORDERED],[RECEIVED],[REQUESTED],[Retired],[TEST]) ) as DevicePivotorder by typeWith the first basic query, I get a count back for the number of logical_names there are where istatus is NULL within a type. With the Pivot table query, I have not been able to replicate retrieving the count for the NULL values. I am fairly new to this level of SQL coding. Can anyone suggest how I can retrieve the NULL count in the second query?Thanks.

Column with Yesterday values

ArjunaReddy — Wed, 24 Apr 2013 06:56:58 GMT

Hi,Please help me out with the below query.We are calculating Exposure column (Hilighted in the query) based on current date and my requirement now is to add a new column next to it with yesterdays values (Currentdate-1). Please help me out.SQLServer 2008 R2-----------------------------SELECT --bi.cobdate, fact.batchid, fact.mastergroup, fact.counterparty, fact.counterpartyname, fact.parentcounterpartyname AS ParentCounterparty --If No Parent, then show the original CP as Parent , fact.portfoliolevelcd AS AggregationScheme, fact.portfolionodevalue AggregationNode, ptycc.anzccr AS CCR, ptycc.securityindicator AS SI, fact.limittimeband, fact.limitstartdt AS [Start_Date], fact.limitenddt AS [End_Date], COALESCE(ext.currency, fact.limitcurrency) AS LocalCurrency, fact.limitcurrency AS LimitCCY, COALESCE(fun.currency, fun1.limitcurrency) LocalExchnagerate, fun1.limitcurrency AS LimitExchnagerate, ( fun1.curvepointvalue / fun.curvepointvalue ) * CONVERT(FLOAT, fact.limitamount) AS Limit --,fun.CurvePointValue LocalCurrency , fun1.curvepointvalue Limitcurrency, [b] pfe.riskvalue AS Exposure [i][u][/u][/i][/b] --,pfe.RiskValue2 as "ExposureT-1" , ( ( fun1.curvepointvalue / fun.curvepointvalue ) * CONVERT(FLOAT, fact.limitamount) - pfe.riskvalue ) AS Availability, ( CASE WHEN ( ( fun1.curvepointvalue / fun.curvepointvalue ) * CONVERT(FLOAT, fact.limitamount) ) IS NULL OR pfe.riskvalue IS NULL THEN 0 WHEN ( ( fun1.curvepointvalue / fun.curvepointvalue ) * CONVERT(FLOAT, fact.limitamount) ) = 0 AND pfe.riskvalue > 0 THEN 1 WHEN ( ( fun1.curvepointvalue / fun.curvepointvalue ) * CONVERT(FLOAT, fact.limitamount) ) = 0 AND pfe.riskvalue = 0 THEN 0 ELSE Cast(( Isnull(pfe.riskvalue, 0) / ( ( fun1.curvepointvalue / fun.curvepointvalue ) * CONVERT( FLOAT, fact.limitamount) ) ) AS FLOAT) END ) AS Utilisation, ptycc.creditcontrolpoint AS ControlPoint, fact.[CollateralAgreementCd] CollateralApplied, fact.[NettingAgreementCd] NettingApplied, fact.israzor FROM dw.[factlimitutilizationbyportfolio] FACT (nolock) INNER JOIN (SELECT br.batchrunid BatchID, bi.businessdate CobDate, bi.batchinstanceid, br.startdatetime AS ReportingDate FROM logging.batchrun br (nolock) INNER JOIN logging.batchinstance bi (nolock) ON br.batchinstanceid = bi.batchinstanceid) BI ON fact.batchid = Bi.batchid INNER JOIN dw.partycreditcontrol ptycc (nolock) ON fact.counterpartyid = ptycc.partyid AND ( ptycc.effstartdate <= bi.cobdate AND ptycc.effenddate > bi.cobdate ) INNER JOIN dw.portfolio port (nolock) ON fact.portfolioid = port.portfolioid AND port.providersystemcd = 'Razor' AND port.portfoliolevelcd = 'Customer Asset Group' AND port.effstartdate <= bi.cobdate AND port.effenddate > bi.cobdate LEFT JOIN dw.portfoliobridge bport (nolock) ON ( bport.tgtportfolioid = fact.portfolioid AND bport.tgtprovidercd = 'Razor' AND bport.effstartdate <= bi.cobdate AND bport.effenddate > bi.cobdate ) LEFT JOIN (SELECT t.portfolioid, t.limittimeband, Max(t.maxexposure) AS RiskValue, t.batchid FROM dw.factlimitutilizationbyportfolio t (nolock) INNER JOIN dw.riskmeasuredefinition rmd (nolock) ON t.riskmeasuredefinitionid = rmd.riskmeasureid AND rmd.riskmeasurename = 'PFE_LC' AND t.isfact = 1 GROUP BY t.portfolioid, t.limittimeband, t.batchid) pfe ON bport.srcportfolioid = pfe.portfolioid AND bport.srcprovidercd = 'CRE' AND fact.limittimeband = pfe.limittimeband AND fact.batchid = PFE.batchid LEFT JOIN (SELECT DISTINCT portfolioid, currency, runid FROM extract.razorportfoliotraderelation)ext ON fact.batchid = ext.runid AND PFE.portfolioid = ext.portfolioid LEFT JOIN (SELECT cpt.curvepointvalue, cdt.observationdt, cid.curveidentifier currency FROM dw.curveidentifier cid (nolock) INNER JOIN dw.curvedata cdt (nolock) ON cid.curveid = cdt.curveid INNER JOIN dw.curvepoint cpt (nolock) ON cdt.curvedataid = cpt.curvedataid WHERE cid.curvetype = 'Exchange' AND cid.curvedomain = 'QuIC' AND cid.islatest = 1 AND cdt.islatest = 1 AND cpt.islatest = 1 AND cdt.ccycd = 'USD') fun ON fun.observationdt = bi.cobdate AND fun.currency = ext.currency LEFT JOIN (SELECT cpt.curvepointvalue, cdt.observationdt, cid.curveidentifier LimitCurrency FROM dw.curveidentifier cid (nolock) INNER JOIN dw.curvedata cdt (nolock) ON cid.curveid = cdt.curveid INNER JOIN dw.curvepoint cpt (nolock) ON cdt.curvedataid = cpt.curvedataid WHERE cid.curvetype = 'Exchange' AND cid.curvedomain = 'QuIC' AND cid.islatest = 1 AND cdt.islatest = 1 AND cpt.islatest = 1 AND cdt.ccycd = 'USD') fun1 ON fun1.observationdt = bi.cobdate AND fun1.limitcurrency = fact.limitcurrency WHERE isfact = 0-----------------------------Thanks,Nagarjun.

Automatic Update from Excel to SQL

Eshika — Thu, 16 Sep 2010 10:09:39 GMT

I need to load the data from excel to a table in SQL Server 2008 and automate the updates.ie., If there is a new record or and update to the existing record in the source excel it should be automatically update the SQL Table.Is it possible, if yes how ??Any ideas or suggestions would be helpful.Please help.Swetha

Unexpected result of appending records to a table in SQL 05

msheeha2 — Thu, 18 Apr 2013 17:32:56 GMT

Can someone explain this result?I have a table in SQL 05, lets call it [Pool] with the columns [PoolID] int Identity(1,1) not null,[AccountID] nchar(8) PK not null, [A], [B], [C]...This was created by someone else some time ago and relates to other tables by [PoolID].There are 670 records in this table, and [PoolID] is the same as the record number.So I needed to append new records to this table - I had a table, call it [New], with 661 records to append, and I knew that 606 of them were duplicate [AccountID]s. So what I wanted was to add the 55 non-duplicate records and have their [PoolID] values increment from 671 to 725.I wrote my query like this:Insert Into [Pool] ([AccountID], [A], [B], [C]...)Select [AccountID], [A], [B], [C]...From [New]Where (Not Exists (Select [AccountID] From [Pool] Where ([New].[AccountID] = [AccountID])))So the 55 non-duplicate records were appended to [Pool] like expected, but here's the strange part - the [PoolID] values are 1332 to 1386?I was expecting the [PoolID] column to act like an Access AutoNumber column - why did the [PoolID] jump from 670 to 1332?

How to write these values down a column

Dominic_godfrey — Wed, 17 Apr 2013 08:48:56 GMT

Hi,I've been trying to think of a way to change the appearance of the following data.It currently looks like thisClaimKey ARevDate ARowNum CauseCode123456789 20120101 1 Fault123456789 20120201 2 NULL123456789 20120301 3 NULL123456789 20120401 4 NULL123456789 20120501 5 Non-Fault123456789 20120601 6 NULL123456789 20120701 7 NULLFor the purposes of what I want to do after, I need the data to look like thisClaimKey ARevDate ARowNum CauseCode123456789 20120101 1 Fault123456789 20120201 2 Fault123456789 20120301 3 Fault123456789 20120401 4 Fault123456789 20120501 5 Non-Fault123456789 20120601 6 Non-Fault123456789 20120701 7 Non-FaultSo it is basically writing the cause code down the cilumn untill there is a different one.Any help would be much appreciated, it appears to be easy but i dont seem to be able to do it.ThanksDominic

Query tuning

m.praki — Wed, 30 Jan 2013 17:12:31 GMT

Hi,I had a performance issue in our stored procedure. Later I found out that the below simple query took 36 secs. SELECT EMP_NOFROM EMPWHERE EMP_STATUS !='01'AND EMP_NO NOT IN ( SELECT EMP_NO FROM EMP WHERE EMP_STAT NOT IN ('01','02') AND [YEAR] > 2009)AND [YEAR] < 2009Then,I have fixed the performance issue by loading the subquery results into a temp table and used the temp table results in place of subquery. Please see below. Now it just took 4 secs to process but really I don't know what was the issue in the above query and how it got fixed once I've loaded into a temp table. [u][b]Can someone please explain why the above query took 36 secs and the below one just took 4 secs ?[/b][/u]INSERT INTO #TEMPSELECT EMP_NOFROM EMPWHERE EMP_STAT NOT IN ('01','02')AND [YEAR] > 2009GOSELECT EMP_NOFROM EMPWHERE EMP_STATUS !='01'AND EMP_NO NOT IN ( SELECT EMP_NO FROM #TEMP)AND [YEAR] < 2009

can I use while loop for only one column?

abhas — Fri, 12 Apr 2013 01:45:17 GMT

Hi All,Please help me on below scenario.I am having application where user selects startdate and enddate from his application(front end). but if he selects Startdate is 2013/01/01 and EndDate as 2013/01/31. Then ihave to insert in the database as effetivedate which is calculated column based on startdate and enddate and will display all dates inbetween selected date. so in the above case,i want to insert effectiveDate as 2013/01/012013/01/02....2013/01/31please help.

split single row into four rows.

abhas — Tue, 09 Apr 2013 00:29:56 GMT

Hi All,I am having a table which contains 4 field as below.stuid,studname,startdate,starttime,endtime.now if starttime is 7am and endtime is 8am then i want to display data in the same table in the interval of 15 mins.example if the user select 7am as starttime and 9am as endtime then 8 rows wolud be populated as below.Studentid Studentname effectivedate starttime endtime1 john 2012/23/03 7am 7.15am1 john 2012/23/03 7.15am 7.30am1 john 2012/23/03 7.30am 7.45am1 john 2012/23/03 7.45am 8am1 john 2012/23/03 8am 8.15am1 john 2012/23/03 8.15am 8.30am1 john 2012/23/03 8.30am 8.45am1 john 2012/23/03 8.45am 9amThanksAbhas.

Calculation

Sql Student-446896 — Tue, 15 Jan 2013 09:46:14 GMT

TableX-------Name Chg Amount PrevAmtABC 1 100 0ABC 2 300 100I want to dynamically calculate ChangeAmt from the above table for the Name "ABC".Example: Between Chg 1 and Chg2, the Amount increased by 200.

guidence concerning pivot table

bryan van ritter — Fri, 29 Mar 2013 13:40:58 GMT

Hi,i have a table which looks like this:custid item date amount1 3 1-1-2013 11 2 1-1-2013 22 4 1-2-2013 22 4 1-2-2013 1and i want to make it look like this: date item1 item2 item3 1-1-2013 custid 1 2 0 11 3 1 11 4 1 1 1-2-2013 custid 2 5 1 12 1 1 12 4 1 1can anyone help me with some guidence on thisthanx in advance,regards,bryan

Split strings alternative to XML

MackF — Wed, 27 Mar 2013 14:36:58 GMT

Hi guys,For some performance considerations, we would like to try alternative to the code below.Anyone could help on how to get this job done using CTE, temp table or I don not know...Actually, I am not a dev guy at all ::-)Thanks,MackCREATE FUNCTION [dbo].[fn_Split](@data NVARCHAR(MAX), @delimiter NVARCHAR(5))RETURNS @t TABLE (Idx int identity (1,1), data NVARCHAR(max))ASBEGIN DECLARE @textXML XML; SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML); INSERT INTO @t(data) SELECT T.split.value('.', 'nvarchar(max)') AS data FROM @textXML.nodes('/d') T(split) RETURN

SSIS 2012,Ecel 2010 excel source error connection manager

vamsinamburu — Mon, 25 Mar 2013 13:51:00 GMT

Hi all,-----------------------------------------------------------------------------------------Could not retrieve the table information for the connection manager 'Excel Connection Manager'.Failed to connect to the source using the connection manager 'Excel Connection Manager'----------------------------------------------------------------------------------------- I am using VS 2010, MS office 2010 & windows 7 64-bit machine. Though i installed the Access db engine 2010 redistributable_x64, i couldn't find these drivers in the list of providers in oledb source.Also, i tried changing the run 64 bit runtime to false, but still the problem persists. Any help will be appreciated. Thanks in advance.

Select in a view for more result

mhmdsow — Sat, 23 Mar 2013 07:12:33 GMT

hy !There is my viewmvtDtate mvtMouvementDebCred TypemvtMouvementDebCred1/1/2013 154 000 D2/1/2013 210 000 C3/1/2013 654 012 D4/1/2013 213 000 564 CAfter more essay : And I want a result as this : the mvtMDebCred type D in a Columm and the same for the type C mvtDtate mvtMDebCred mvtMDeb mvtMCred TypemvtMDebCred1/1/2013 154 000 154 000 ----------- D2/1/2013 210 000 ----------- 210 000 C3/1/2013 654 012 654 012 ---------- D4/1/2013 213 000 ----------- 213 000 564 Cthanks ........

Self Join Brings Too Many Records

javib — Fri, 22 Mar 2013 10:08:24 GMT

I have this query to express a set of business rules. To get the information I need, I tried joining the table on itself but that brings back many more records than are actually in the table. The table only has about 1700 records in it but I'm getting these results:[code="plain"]rep_id Single Practitioner00718264U 883600729486S 9600726313T 7501078482M 7501005121V 7100603023J 68[/code]Below is the query I've tried. What am I doing wrong?[code="sql"]SELECT DISTINCT a.rep_id, count(*) AS 'Single Practitioner'FROM SE_Violation_Detection a inner join SE_Violation_Detection b ON a.rep_id = b.rep_id and a.hcp_cid = b.hcp_cidgroup by a.rep_id, a.rep_namehaving count(*) >= 2ORDER BY count(*) DESC[/code]

what is alternative for *= in sqlserver 2012

sram24_mca — Wed, 20 Mar 2013 01:53:08 GMT

Recently we have migrated our projects to SQLSERVER 2012 , some of the queries has *= [syntax in where condition]now we need to convert this syntax to SQLSERVER 2012 compatible. Can anyone has any idea to resolve the problem. Because *= will not support in SQLSERVER 2012.Regards,Ram

Pivot Query- need help

sammm — Mon, 22 Feb 2010 12:52:24 GMT

HI All,I am trying to convert rows to columns using Pivot function. Here is my caseSELECT Month(a.createddate) as month,count(statusid) as count,b.codefrom ServiceRequest a,StdActivity bwhere a.createddate between '2009-06-01 00:00:00.000' and '2009-12-30 23:59:59.999'--and statusid=10 and a.SRSetnumber like '%DELI%'and a.statusid=b.StdActivityIDgroup by Month(a.createddate),a.statusid ,b.codeorder by Month(a.createddate) ,a.statusid,b.codeResult Set :month count code6 8425 DELIVERY6 20 CANCELLEDi want to use pivot function and convert the result set to month delivery cancelled6 8425 6 20Any help would be greatly appreciated. Writing a reporting query and got stuck up with this .I havent used the pivot before and the help topics is little bit confusing to me.Thanks

Pivot

Amit Lohia — Mon, 22 Jun 2009 17:56:03 GMT

Guys,I have a table as InvoiceNumber, BillName1, Amit 2, Amit3, BBB4, AmitThe BillName can be same or different.I need the output as follow1 2 3 4Amit Amit BBB AmitAny idea how can we do this?

I am trying to enter a text file in to a database with customer delimiters

SQLArnold — Thu, 14 Mar 2013 03:18:44 GMT

Hello All,I posted this in SSIS and did not get any replies. I hope this is the place I need to post this question.I am working at a small health plan and I am trying to parse a 834 flat file data into a table using SSIS, SQL Server or VS. The file can be very large at times. One of my problems is the data is separated by { and then abbreviated words to inform you of what type of data is coming next. Example ~AMT{After ~AMT{ will be the copay amount for the member.834 Benefit Enrollments and MaintenanceFunctional Group ID: BE~INS - Insured Benefit OR MEMBER LEVEL DETAIL~REF - Reference Information: The REF segment is required to link the dependent(s) to the subscriber.~DTP - Date or Time or Period~NM1 - Individual or Organizational Name: Either NM1 or N1 will be included depending on whether an individual or organization is being specified.~PER - Administrative Communications Contact~N3 - Party Location~N4 - Geographic Location~DMG - Demographic Information~LS - Loop HeaderEC - Employment Class~LS - Loop Header~LX - Transaction Set Line Number~N1 - Party Identification~REF - Reference Information~AMT - Monetary Amount Information{C1 - CO-PAYMENT AMOUNT~COB - COORDINATION OF BENEFITS~LX - Transaction Set Line Number~N1 - Party Identification~DTP - Date or Time or Period: REPORTING CATEGORY DATE~LE - Loop Trailer: ADDITIONAL REPORTING CATEGORIES LOOP TERMINATION~SE - Transaction Set Trailer: SE is the last segment of each transaction set.Here is an edited segment of what I am trying to enter into a table.~INS{Y{18{001{43{A{C{{AC~REF{0F{A00000000~DTP{000{D8{yyyymmdd~NM1{IN{1{LastName{FirstName{Initial~PER{IP{{HP{phonenumber{TE{phonenumber~N3{MONTECITO{streetaddress~N4{city {state{zipcode{{CY{13~DMG{D8{DateOfBirth{GenderMorF{I{H~LS{2700~LX{1~N1{75{ACTION CODE~REF{ZZ{AC~LX{2~N1{75{RENEWAL DATE~DTP{007{D8{DOB~LE{2700the total length of the enrollment segments is 60,000 characters so entering it into a table is not possible or not that I could do without cutting the file in half.I would like to enter the data as such.ActionCode 001 -- means maintenancePlanid A00000000DOB mmddyyyyetc.but I would be okay with col1 INScol2 ycol3 18col4 001col5 43col6 acol7 c col8 acetc.I would appreciate any help or assistance, I have searched Google and have not found a good solution.Thanks in advance and have a great day!Cheers!

how to store text file content into sql server 2005 database

pkuchaliya — Tue, 12 Mar 2013 22:21:36 GMT

Hi,i have to design an application to read all text files from a folder and store in sql server 2005.each file has an average size of 1 MB. User can search and view all data by User Interface.I wish to enquirer if it is good practice to store text file content in tables.text file data is in alphanumeric and continuous format.Please suggest me how can i do this and is there any other way to do it.Thanks.

Performce SP

srimkumarp-720356 — Thu, 28 Feb 2013 06:55:07 GMT

Hi ,One of SP is having below code and it is running more than 2 hrs. This job is daily schedule.Table_B is truncate and load from souce systems on daily basis.Number of rows in Table b is morethan 33000000.On TableA 3 non-clustered indexes are already created.UPDATE STATISTICS dbo.Table_AUPDATE STATISTICS dbo.Table_B Insert into Table_A (columns...)Select columns... from Table_B B1LEFT JOIN Table_A A1 (NOLOCK) ON B1.[Column1] = A1.[Column1] AND A1.[SDate] IS NULL WHERE A1.[Column1] IS NULL udpate Table_a..........................I am not sure where I need to debug this issue. While running sp I ran the Profiler on database and ran the DB Tunning Advisor also. But I got only one recommandation on one column on Table A. This column is already tide with non-clustered index . Can any one please advice.. Thanks..