i hav a table lik this
bank amount status
----------------------------
hdfc 1000 credit
icici 2000 credit
hdfc 500 debit
icici 1000 debit
i need to get the output like diz
bank credit debit
--------------------------------
hdfc 1000 500
icici 2000 1000
how can i get this output????
Thanks
While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.
For example,the output of the Aggregate Transformation may be 960216 ,but the
'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.
I'm sure the Group By of the Aggregate Transformation is right!
But ,when I set the "keyscale" property of the transformation,the results match!
In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.
Thanks for your advice.
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 :Â @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 :Â @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 :Â @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutputÂ
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
Hi
I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end.....
The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time.
What can I do to avoid such situation?
Thanks in advance,
JD
I am working on an OLAP modeled database.
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn
View 5 Replies View RelatedWhen overriding the PrimeOutput method in a custom component, you get as parameters the outputIDs and the output buffers (of type PipelineBuffer). using the outputIDs you can get IDTSOutput90 outputs.
View 5 Replies View RelatedHi there,Is it possible for me to do an insert
INSERT (some values) OUTPUT INSERTED.ID AS @ID
Grab ID in my code behind and create a session with it so i can redirect to another page and use the session to access the same record?
Or maybe
INSERT (some values)
SELECT @@IDENTITY
then grab that in my code behind and create a session?
I have been googling for a couple of hours and can't find a solution that i can follow or anyone saying you cant do it.
Any help much appreciated.
Thanks
OK, I'm starting up a project that requires an XML output format, which I haven't played around with before. My plan is to load the data into a schema which matches the XML format, and the run my queries with the FOR XML AUTO, ELEMENTS options.
So first, any comments on the plan of attack?
Second, how do I get the XML output in a clean enough format to read in, say, either Visual Studio or even Internet Explorer? The output comes out as a solid block, with no identing, which I guess the display utilities can deal with, but it also contains carriage returns and an odd header at the top (XML_F52E2B61-18A1-11d1-B105-00805F49916B), and the ubiquitous separation dashes (-------------------------------), and I'm having to manually fix these issues before I can display the XML in any reasonable format.
Any tips/tricks on dealing with XML are appreciated.
Hi I have an SP OmgångsVal
ALTER PROCEDURE OmgångsVal
@SexVal nvarchar,
@Sasong int
AS
BEGIN
SET NOCOUNT ON;
SELECT MAX(Omgang) AS [Omgång]
FROM Resultat
WHERE @SexVal = Lag And @Sasong = Säsong
END
And want [Omgång] as input in next SP
DECLARE
@SexVal nvarchar,
@Omgång INT,
@Sasong int
SET @SexVal='A'
SET @Sasong=20072008
EXEC Ubc90OmgångsVal @SexVal,@Sasong
BEGIN
SELECT Match.MatchId, Match.matchdate AS MatchStart, Team.team AS Hemma, Team1.team AS Borta, Match.score, Match.vsscore
FROM Match INNER JOIN
Team ON Match.team = Team.TeamId INNER JOIN
(SELECT TeamId, team, GroupId
FROM Team AS Team_1) AS Team1 ON Match.vsteam = Team1.TeamId
WHERE Match.Omgång = @Omgång
But i get error:
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '@Omgång'.
B Regards
Gert
I am selecting the following fields from my table and drop them into my .csv, the problem is that if i select all the fields which I commented out, and click on define columns, populate from source, execute, then on the Destination Tab, none of my columns are selected, it is blank, please help, here is my table
select
trad_type,
reference,
principal,
book,
strategy,
cpty,
buy_sell,
Quantity,
ident_type,
ext_ident,
sec_name,
price,
price_divisor,
traded_net_ind,
trade_ccy,
trade_ldt,
value_date,
commission,
exchange_fee,
other_fees
gross_consid,
net_consid,
sett_ccy,
trad_sett_ccy_xrate,
/*trad_sett_ccy_xrate_mdv_ind
trad_inst_ccy_xrate
trad_inst_ccy_xrate_mdv_ind
*/
pb,
acct,
inst_class
/*cont_desc
pl_book_ccy_xrate
Id*/
from Table1
ho to make this output in one line of this
set head off;
set pagesize 0;
set linesize 200;
set feedback off;
set termout off;
set pause off;
spool /bistari/dw/sds2/rosrita/fahmi/ogbs01.txt;
select distinct
ptt.marketing_ptt_num||'|'||
exchange.exchange_ident_name||'|'||
exchange.exchange_ident_code||'|'||
tel_profile.service_num||'|'||
customer_profile.customer_name,
tel_profile.apartment_num||'|'||
tel_profile.lot_num||'|'||
tel_profile.house_num||'|'||
tel_profile.floor_level_code||'|'||
tel_profile.building_name||'|'||
tel_profile.street_num_code||'|'||
tel_profile.street_type||'|'||
tel_profile.street_name||'|'||
tel_profile.postal_code||'|'||
tel_profile.section_name||'|'||
tel_profile.city_name||'|'||
tel_profile.state_code||'|'||
tel_profile.serv_class_code||'|'||
customer_profile.customer_segment_code||'|'
from
temp_ogbs_fahmi,
tel_profile,ptt,exchange,customer_accounts,customer_profile
where
temp_ogbs_fahmi.service_num = tel_profile.service_num and
temp_ogbs_fahmi.connect_date = tel_profile.connect_date and
tel_profile.ptt_num = ptt.network_ptt_num(+) and
tel_profile.exchange_ident_code = exchange.exchange_ident_code(+) and
tel_profile.account_num = customer_accounts.account_num(+) and
customer_accounts.customer_id = customer_profile.customer_id(+);
set head off;
set pagesize 0;
set linesize 200;
set feedback off;
set termout off;
set pause off;
spool /bistari/dw/sds2/rosrita/fahmi/ogbz02.txt;
select distinct
ptt.marketing_ptt_num||'|'||
exchange.exchange_ident_name||'|'||
exchange.exchange_ident_code||'|'||
tel_profile.service_num||'|'||
customer_profile.customer_name||'|'||
tel_profile.apartment_num||'|'||
tel_profile.lot_num||'|'||
tel_profile.house_num||'|'||
tel_profile.floor_level_code||'|'||
tel_profile.building_name||'|'||
tel_profile.street_num_code||'|'||
tel_profile.street_type||'|'||
tel_profile.street_name||'|'||
tel_profile.postal_code||'|'||
tel_profile.section_name||'|'||
tel_profile.city_name||'|'||
tel_profile.state_code||'|'||
tel_profile.serv_class_code||'|'||
customer_profile.customer_segment_code||'|'
from
temp_ogbz_fahmi,
tel_profile,ptt,exchange,customer_accounts,customer_profile
where
temp_ogbz_fahmi.service_num = tel_profile.service_num and
temp_ogbz_fahmi.connect_date = tel_profile.connect_date and
tel_profile.ptt_num = ptt.network_ptt_num(+) and
tel_profile.exchange_ident_code = exchange.exchange_ident_code(+) and
tel_profile.account_num = customer_accounts.account_num(+) and
customer_accounts.customer_id = customer_profile.customer_id(+);
exit;
output
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
XXXXX XXXXXXXXX XXXXXXXXXX XXXXX XXXX XXXX XXXXXX
slipzst@yahoo.com
hi all,
i've inserted one row like this
insert into e1 select 'phani',18000,'15-oct-2007'
i got the out put like this
phani180002007-10-15 00:00:00.000
how can i get the same output as i've given?
thankyou very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
I have a simple process that goes through a directory and deletes files older than some number of days. I determine the full filename in a script task by setting a variable and then I use File System Task to do the delete. My goal is to output the file name to a log file. Nothing fancy, just output text (file name) to the default log provider so it can be logged if logging in enabled.
If anyone has any clues on how to do this that'd be great. If you need more information, let me know.
Cheers!
I've Proc1 and Proc2, the output of Proc2 can be in XML or thru anormal Select statement, depending upon the input parameter specified(@xmflag). The default of @xmflag = 0 which means non-XML output. NowProc1 is calling Proc2 and inserting the output of Proc2 in a temptable without specifing the @xmflag parameter. The message coming is"The FOR XML clause is not allowed in a INSERT statement". I don'tunderstand why this msg is coming when the Select statement FOR XML isnot executing.Anybody knows the reason?Thanks in advance.Subodh
View 4 Replies View RelatedI am using a dynamic t-sql string in proc1 to execute proc2, which returns an int variable named @Fatal_Error back to proc1.
When I execute proc2 I use the syntax:
EXEC @SQL @Params
@SQL is the Proc Name (varchar) and @Params is the parameter string (nvarchar).
If I include the @Fatal_Error variable in the dynamic creation of the @Params string the returning value from Proc2 is unable to convert int to nvarchar.
I have declared @Fatal_Error in proc1 as int and tried to add to the end of my dynamic t-sql EXEC but I still get 'Cannot convert int to nvarchar' .
Please help - I'm beginning to pull out hair! :-)
Thanks!
Here' s the syntax I tried when just passing it at the end of the EXEC call:
EXEC @SQL @Param_List = @Fatal_Error
AND I also tried:
EXEC @SQL @Param_List + ' '+@Fatal_Error+' '
Can I read an entire file and output just one row?
I have a file that contains information about several databases. I need to read in the file and then insert into a new table the appropriate values. Example:
The file:
database1 10 GB used
database2 20 GB used
database3 30 GB used
Insert into table
Date database1 used database2 used database3 used
I can't seem to figure out how to insert only one row. Can someone help me? Does this make sense?
Thanks
Hi everybody,
i have a small requirement.
when i view my report in the report verver,i can see different output type that i can export my report, No my requrement is , i need to see only PDF only output type to export to the user ! no Excel or any other type in the viewer ?
how do i do this task using report server 2005 ?
any idea of doing this
regards
suis
Hi!
I have a field with xml stored as a CLOB.
How can I view it in reporting services?
I want the + and - so the user can hide or expand tags. Is it possible?
Thanks
//C
I have an SQL INSERT statement with an output parameter called @CusRef. I have been trying to store this in a session variable, however all i am able to store is the reference to the parameter object. (The returned value stored in the parameter is an Integer)Session("CustomerReference") = DataConn.InsertParameters.Item("CusRef")I cant seem to find a value field or a method to get the value from a parameter. I have also tried using CType(DataConn.InsertParameters.Item("CusRef"), Integer) but it cant convert the Parameter type to an Integer.Please help,ThanksGareth
View 1 Replies View RelatedA SQL Server 2005 DB table named "Users" has the following columns:
ID - int (IDENTITY)FirstName - varchar(50)LastName - varchar(50)UserID - varchar(20)Password - varchar(20)
Before inserting a new record in the DB table, ASP.NET first checks whether the UserID supplied by the new record already exists or not. If it exists, the new record shouldn't be inserted in the DB table & the user should be shown a message saying UserID already exists. To do this, ASP.NET uses a stored procedure. If the value returned by the stored procedure is 1, it means that the UserID already exists. If the value returned by the stored procedure is 0, then the new record should be inserted in the DB table. This is how I have framed the stored procedure:
CREATE PROCEDURE RegUsers @FName varchar(50), @LName varchar(50), @UserID varchar(50), @Password varchar(50), @return_value int OUTPUTAS IF EXISTS(SELECT UserID FROM Users WHERE UserID=@UserID) BEGIN SET @return_value=1 END ELSE BEGIN SET @return_value=0 INSERT INTO Users VALUES (@FName,@LName,@UserID,@Password) END
& this is how I am invoking the stored procedure from the ASPX page:
<script runat="server"> Sub btnSubmit(ByVal obj As Object, ByVal ea As EventArgs) Dim sqlCmd As SqlCommand Dim sqlConn As SqlConnection
sqlConn = New SqlConnection("Data Source=MyDBSQLEXPRESS;Initial Catalog=DB;Integrated Security=True") sqlCmd = New SqlCommand("RegUsers", sqlConn) sqlCmd.CommandType = CommandType.StoredProcedure
With sqlCmd Parameters.Add("@return_value", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue Parameters.AddWithValue("@FName", txtFName.Text) Parameters.AddWithValue("@LName", txtLName.Text) Parameters.AddWithValue("@UserID", txtUserID.Text) Parameters.AddWithValue("@Password", txtPassword.Text) End With
sqlConn.Open() sqlCmd.ExecuteNonQuery()
If (sqlCmd.Parameters(0).Value = 1) Then lblMessage.Text = "UserID Already Exists!" ElseIf (sqlCmd.Parameters(0).Value = 0) Then lblMessage.Text = "Thanks For Registering!" End If
sqlConn.Close() End Sub</script><form runat="server"><%-- the 4 TextBoxes come here --></form>
When I execute the above ASPX code, if the UserID I entered already exists in the DB table, then ASPX generates the following error:
Procedure or Function 'RegisterUsers' expects parameter '@return_value', which was not supplied.
pointing to the line
sqlCmd.ExecuteNonQuery()
Can someone please point out where am I going wrong?
Here is what I have so far, I can get a number added to the table running my sproc from management studio. But how do I get it out as it is being intserted and then use it in my code?ALTER PROCEDURE [dbo].[NumberCounter]
-- Add the parameters for the stored procedure here
@InsertDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO tblNumberCounter (InsertDate) Values (@InsertDate);Select IDENT_CURRENT('tblNumberCounter')
END
Public Sub SubmitAdd_Click(ByVal Sender As System.Object, ByVal E As System.EventArgs)
Dim Con As SqlConnection
Dim StrInsert As String
Dim cmdInsert As SqlCommand
Dim myNewReceiptNumber As Integer
Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
'Add row to receipt table, then get it for ReceiptNumberText field.
cmdInsert = New SqlCommand
cmdInsert.CommandText = "NumberCounter"
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Connection = New SqlConnection(ConnectStr)
cmdInsert.Parameters.AddWithValue("@InsertDate", System.DateTime.Today.ToShortDateString())
Try
Con.Open()
myNewReceiptNumber = cmdInsert.ExecuteScalar()
'Response.Write(myNewReceiptNumber)
Catch objException As SqlException
Dim objError As SqlError
For Each objError In objException.Errors
Response.Write(objError.Message)
Next
Finally
Con.Close()
End Try
End Sub
Hi there. I have a SQL database with 2 tables as follows:
OptionListTableid (int)username(nvarchar(50))option1(bit)option2(bit)option3(bit)
DescriptionTableid(int)name(nvarchar(50))description(text)
What I would like to do is look up in the OptionListTable to see which options are true for a given user, and make some sort of repeater or datalist with the description and name in DescriptionTable. I'm really not sure what the best way to do this is, as i'm fairly new to SQL so if anyone has any good ideas, i'd be all ears...
cheers, eh!
Hi all
In my application i have to upload a excel file.After that it will retrive tha data from database depending upon the data in excel sheet.So now the problem is that i am using a stored procedure for this and in that procedurei have taken output parameter to catch any type of error.For example suppose in my excel sheet i have 3 columns.let it Group Code,Employee No and Employee Code.So incase it will not find the employee number so using the output parameter i am throwing the error message.So here we are getting two return types.one is the selected data and another is the error message.So how to handle these two thing in asp.net.Usong a dataset i can only retrive the selected data.But how to get the errror message as well as the data.So please help me.
Can anyone see in this stored procedure code and my post sub why the stored procedure is not getting the @ReceiptNumber?
@ReceiptNumber int OUTPUTASBEGININSERT INTO tblNumberCounter (InsertDate)Values (GETDATE())SET @ReceiptNumber=SCOPE_IDENTITY()INSERT INTO tblReceipts (pl_ID,client_ID,PaymentDate,Fund_ID,TenderTypeID,AmountPaid,ReceiptNumber,DateEntered,EnteredBy) SELECT PL.Pl_ID, RS.client_id, PL.pmtDate, RS.rec_fund_id, RS.rec_tendertypeid, RS.rec_amount, @ReceiptNumber, RS.DateEntered, RS.EnteredByFROM tblRec_setup AS RS INNER JOIN tblPayments AS PL ON RS.rec_id = PL.rec_id Sub Post() Dim cmdInsert1 As SqlCommand Dim tr As SqlTransaction = Nothing Dim ConnectStr As String = _ ConfigurationManager.ConnectionStrings("2ConnectionString").ConnectionString Dim conn As New SqlConnection(ConnectStr) cmdInsert1 = New SqlCommand cmdInsert1.CommandType = CommandType.StoredProcedure cmdInsert1.CommandText = "BatchMonthly" 'Get a new receipt number and add it. Dim InsertedIntegerRN As New SqlParameter("@ReceiptNumber", SqlDbType.Int) InsertedIntegerRN.Direction = ParameterDirection.Output cmdInsert1.Parameters.Add(InsertedIntegerRN) 'Try conn.Open() tr = conn.BeginTransaction() cmdInsert1.Transaction = tr cmdInsert1.Connection = conn cmdInsert1.ExecuteScalar() tr.Commit() 'Catch objException As SqlException tr.Rollback() 'Dim ex As SqlError 'Response.Write(ex.Message) 'Finally conn.Close() 'End Try End Sub
Hi everyone, can anyone help me please
I have a stored procedure that works quite well and returns one of the following codes, 1 2 3 or 4 which is dependant on business criteria. This sp works well and satsisfies all the criteria etc.
I am calling this SP in asp.net (c#) like this
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["blah"].ConnectionString)) { int? intSQLCheck = null;
con.Open(); SqlCommand cmd = new SqlCommand("check_values", con); cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@param_clothing_id", this.DropDownList2.SelectedValue.ToString())); cmd.Parameters.Add(new SqlParameter("@empNo", this.DropDownList1.SelectedValue.ToString())); cmd.Parameters.Add(new SqlParameter("@createdBy", this.txtEmpNo.Text.ToString())); cmd.Parameters.Add(new SqlParameter("@price", this.txtPrice.Text)); cmd.Parameters.Add(new SqlParameter("@qty", this.txtQty.Text)); //cmd.Parameters.Add(new SqlParameter("@returnValue", 0)); cmd.Parameters.Add(new SqlParameter("@returnVal", 99)); cmd.Parameters["@returnVal"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
What I would like to do know is use a variable intSqlCheck to hold the return value from the SP (and then do some processing of business logic using the variable)
Any ideas anyone please
Hi, I have a form with a detailsview and a sqldatasource. The sqldatasource uses a stored procedure as the select command. This stored procedure requires 2 input parameters and returns 3 output parameters. A button on the form is enabled or disabled depending on the value of the output parameters. When the page loads, I get a "Object reference not set to an instance of an object" message, caused by the null value of an output parameter. SQL Profiler shows that the input parameters are correct, and if I run the stored procedure directly, it's okay. Can anybody tell me why I wouldn't get the output parameters? Thanks,Neil
View 1 Replies View RelatedHi!I have a database (Sql) which has a table in it named news_tbl, within this table I have a column called newsDate which is filled with a timestamp in the format dd/mm/yyyy 00:00:00. The date shows when the news item was created but the time is not used hence the reason its always 00:00:00.
I have an archive page which allows the user to view the news articles by the month they were posted. I have an SQL statement just which looks like this:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:news2007aConnectionString %>"
SelectCommand="Select Distinct LEFT(DATENAME(month, newsDate), 3) As TheMonth, DateName(month,newsDate) As TheFullMonth, DateName(year,newsDate) As TheYear, DatePart(month, newsDate) As TheOrder From news_tbl Order By TheMonth ASC, TheOrder DESC">
</asp:SqlDataSource>
and the output just now is
April 2008
December 2007
February 2008
January 2008
March 2008
November 2007
October 2007
September 2007
I would like the output to be the latest month first i.e. April 2008 then March 2008 then February 2008 and so on. I have tried a couple of different SQL statements but can't get the output to appear in the order I want.
Can anyone suggest where I am going wrong. Thanks,
O.
Hi
I have an SP, which queries a table and correspondingly outputs an ntext column. Before executing this SP. I used "save results as " option under Query analyzer tool bar. So that, when ever i execute this SP I can store the value under a file. The query is executing fine , but it is truncating the output of this ntext value.
Does any body knowsfaced this issue?
Iam using sql 2000
Thanks!
Santhosh
I have a stored procedure with an output bit variable. When I set this variable to 0, I get an output of 0. When I set this variable to 1, I get an output of -1. What is going on?
View 9 Replies View RelatedHi I want to make a Function in my User class that adds new members into the db. What I want to do is add the users details in using a stored procedure and input parameters and then return a parameter indicating their userid value and set it to a variable.
My userid column in the my db is auto incrementing with a seed of 1 and a step value of 1.
How could I create an output parameter that would return their new user id and then how would i set it to an integer variable.
Thanks
I keep getting an error stating my stored proc expects "@ret_value" and it's not declared. Even once it is declared, I'm not sure if my proc is going to return the value properly...been working on this half a day.
params put on new line for readability...
Private Sub update_DB()
Dim intResult, ret_value As Integer
ret_value = 0
intResult = SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings("connString"), CommandType.StoredProcedure, "Media_Tracking_add_MarketingID",
New System.Data.SqlClient.SqlParameter("@strMarketingID", txtMarketingID.Text),
New System.Data.SqlClient.SqlParameter("@strAdCampaignName", txtAdCampaignName.Text),
New System.Data.SqlClient.SqlParameter("@strCompany", ddlCompany.SelectedItem.Value),
New System.Data.SqlClient.SqlParameter("@strCampaignType", ddlCampaignType.SelectedItem.Value),
New System.Data.SqlClient.SqlParameter("@ret_value", ret_value, ParameterDirection.Output))
lblSuccess.Text = "RETURN = " & ret_value
End Sub
S Proc
CREATE PROCEDURE [dbo].[media_tracking_add_MarketingID]
@strMarketingID nvarchar(50),
@strAdCampaignName nvarchar(50),
@strCompany nvarchar(3),
@strCampaignType nvarchar(50),
@ret_value int OUTPUT
AS
INSERT INTO media_tracking_Marketing_IDs(MarketingID,AdCampaignName,company,status,CampaignType)
values (@strMarketingID,@strAdCampaignName,@strCompany,'1',@strCampaignType);
set @ret_value = scope_identity();
return @ret_value;
GO
I am trying to find a way how to query following data from a table that looks like this:
ColA ColB ColC
1 2 3
Output should look like this:
ColA
1
2
3