Output In Notepad
hi friends,
Can any one tell how to save the O/P to notepad.@tmp variable output need to be stored in notepad
Create procedure testing
as
begin
set nocount on
--open cursor
print @tmp
--close cursor
set nocount off
View Complete Forum Thread with Replies
Related Forum Messages:
SQL Server To C++ Or Notepad
Basically I'm working on a Web page in ASP.NET, we the nhave our SQL server database, and inside we have C++ which needs to be able to go through incoming files and perform some operations, as well as enter it into the SQL database, and retrieve information from it. But we haven't the foggiest as how to go about doing thing, our C++ programmer doesn't know how to set up a connection from SQL to his code. IS there A) a program that will convert the SQL data into a text file for him to read from with C++. B) A GOOD book that he can pick up that will help bridge the gap between his knowledge and the knowledge required. So far google hasn't represented much luck. Any advice pertaining to some way to allow our C++ guy to learn/or enable him to read data and insert data into a SQL server would be GREATLY appreciated.
View Replies !
Trigger To Insert Data Into A Notepad
Good Day All I want to create a trigger that will send records INSERTED into a notepad. Can you please help with the syntax CREATE TRIGGER Test ON [Table1] FOR INSERT NOT FOR REPLICATION AS /*Instead of Inserting into another table, I WANT TO INSERT DATA INTO A NOTE PAD WITH 3 VARIABLES */
View Replies !
Error When Importing Text File - Saving In Notepad First Works
I'm trying to import a tab separated text file into sql server 2005 using the import guide. But when running the job I get the error message Error 0xc02020c5: Data Flow Task: Data conversion failed while converting column "Column 19" (67) to column "Column 19" (404). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.". (SQL Server Import and Export Wizard) The column 19 which reported a problem contains this information: ?searchroot=/_gen_/txt/&template=std.htm&use=prospect&intref=1_26067 However what is mysterious is that if I open the file in notepad or Excel and resave it again the job runs perfectly. This is not a way we could make it work later on since it's an automatic job that will run each night on a new text file. The text file is sent from Norway to Sweden - and I use ANSI latin 1 when importing. The column has datatype DT_STR with a width of 500. I use Locale Swedish and when I save in Notepad it is saved in ANSI, I use Windows XP Swedish version.
View Replies !
How Can I Create A DTS Package To Update A Table With Data From A Notepad File ?
We are creating a process which will create a notepad file with a set of Unique key which we use to update the database. Am trying to create a DTS package to accomplish this...but it seems to be a trivial task. I can use Transform data task to lookup the text file but i need to write an update query which will update some fields of existing records. Am not sure how can i map fields of Text file to the Database table field. If any1 can help then it will be great
View Replies !
Output And Error Output Write The Same Table At The Same Time, Stall The Process.
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
View Replies !
Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation
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.
View Replies !
Query Produces Jumbled Output / Output Not In Sequence
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 Replies !
Number Of ROWS Of &&"Output Of Aggregate Transformation&&" Sometimes Doesn't Match The Output From T-SQL Query
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.
View Replies !
Screen Output Vs Print Output
I would like the screen output for my reports to have the same page breaks as the print output, is this possible?? For example, I have a long detail report that our managers like to print out, however they only want to print a section of the report. So they sort on a certain column (ie. current balance) and then they find the range that they want and hit the print button, but they specify a certain page range based on page that they see on the screen. The problem is that the printed pages have different page breaks so they get the wrong information. Has anyone been able to match up the page breaks on the screen with what is printed?? I have suggested sorting then exporting to PDF or Excel but it seems that is not a viable solution for the allmighty managers. Hopefully someone can help me out, i can provide more detail as needed.
View Replies !
SQL OUTPUT
Hi 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
View Replies !
DTS Output
I need to get an error message from a stored procedure executed from within a DTS package. The DTS currently imports an Excel file to the upload table, then the stored procedure is called which validates the excel data and separates the good records from the bad. I need to be able to return a value from this procedure that I can pick up from my C# Code. I know how to output values from a regular SP but not when its called as part of a DTS package. Any help is much appreciated, Its really started to bug me now! Thanks in advance
View Replies !
XML Output
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 Replies !
XML Output.
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.
View Replies !
Get Output From One SP In To Another SP
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
View Replies !
DTS To Output .csv
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
View Replies !
Cannot Get OUTPUT From SP :o(
Hi, I am new to T-SQL and would like some help with this stored procedure. Basically, I am trying to check a table to see if a record already exists based on 2 parametres. If the record exists I would like to just output the ID from that record. If the record does not exist I would like to insert a new record outputting the ID. The code seems to work except it does not output the ID when a new record has been created. Can anyone see the problem? Code: CREATE PROCEDURE dbo.prInsertAssmtAttempt ( @AssmtInstID int, @AttemptNo int, @LastUpdateBy int, @AuditTypeID int, @AuditIP varchar(16), @AssmtAttemptID int OUTPUT ) AS DECLARE @Exist int SELECT * FROM tblAssmtAttempt WHERE tblAssmtAttempt.AssmtInstID = @AssmtInstID AND tblAssmtAttempt.AttemptNo = @AttemptNo SET @Exist = @@ROWCOUNT IF @Exist = 0 BEGIN SELECT @AssmtAttemptID = ISNULL(MAX(AssmtAttemptID),0) + 1 FROM tblAssmtAttempt INSERT INTO tblAssmtAttempt (AssmtAttemptID, AssmtInstID, AttemptNo, CreatedDate, LastUpdateBy, LastUpdated, AuditVersion, AuditCurrent, AuditTypeID, AuditIP) VALUES (@AssmtAttemptID, @AssmtInstID, @AttemptNo, GetDate(), @LastUpdateBy, GetDate(), 1, 1, @AuditTypeID, @AuditIP) END SELECT @AssmtAttemptID = AssmtAttemptID FROM tblAssmtAttempt WHERE tblAssmtAttempt.AssmtInstID = @AssmtInstID AND tblAssmtAttempt.AttemptNo = @AttemptNo GO Any help is much appreciated! Thanks, Chezza
View Replies !
Output One Row
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
View Replies !
Pdf Only Output !
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
View Replies !
Output In Xml
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
View Replies !
OUTPUT - Help With Please
I 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+' '
View Replies !
Different Output
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.
View Replies !
Output
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
View Replies !
How To Get This Output?
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
View Replies !
Output To Log
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!
View Replies !
Output Bit Returns -1 ? What?
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 Replies !
Output Is Getting Truncated
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
View Replies !
How To Order The Output.
Hi!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.
View Replies !
SQL Output Parameters
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 Replies !
Output Parameters In SQL Sp's
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
View Replies !
Why Am I Not Getting My OUTPUT Parameter
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
View Replies !
Using Output Paramater
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.
View Replies !
2 Tables, 1 Output?
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!
View Replies !
How Can I Get And Use An OUTPUT Parameter
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
View Replies !
Output Parameter?
A 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?
View Replies !
Getting Value From Output Parameter
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 Replies !
Output Parameter
Hello!Can anybody tell me how can I retreive the value of an output parameter from an stored procedure using an SqlDataSource control?Thank you
View Replies !
Output Parameter
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
View Replies !
Can This Be Done With An Output Parameter?
Hi 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
View Replies !
Sp_who2 Output
Thanks, folks, for taking time to help!In query analyzer and profiler, there seem to be system processes thatare generating high cpu time and disk io. Well, I guess the short ofit is that I am having a problem interpreting what I am seeing so thatI can take the next step in corrective action. There are also severaldomain controlled logins that appear to be on the same host name, atthe same time--these are laptops, not terminal servers--why would thesystem be reporting such a thing?Here is a sample of the output from sp_who2:SPIDSTATUSLOGINHOSTNAMEBLKBYDBNAMECOMMANDCPU TIMEDISKIOLASTBATCHPROGRAMNAME1BACKGROUND sa . . NULLLAZY WRITER75006/3/2005 5:142sleeping sa . . NULLLOG WRITER1025006/3/2005 5:143BACKGROUND sa . . masterSIGNAL HANDLER1606/3/2005 5:144BACKGROUND sa . . NULLLOCK MONITOR20306/3/2005 5:145BACKGROUND sa . . masterTASK MANAGER01516/3/2005 5:147sleeping sa . . NULLCHECKPOINTSLEEP243822396/3/2005 5:1410BACKGROUND sa . . masterTASK MANAGER0256/3/2005 5:1411BACKGROUND sa . . masterTASK MANAGER01636/3/2005 5:1412BACKGROUND sa . . masterTASK MANAGER0706/3/2005 5:1413BACKGROUND sa . . masterTASK MANAGER01556/3/2005 5:1451sleeping saMyServer . msdbAWAITINGCOMMAND683371386/8/2005 13:00SQLAgent - Generic Refresher52sleeping saMyServer . msdbAWAITINGCOMMAND1746266/8/2005 13:00SQLAgent - Alert Engine53sleeping DomainUser1LT1 . VPNMasterAWAITING COMMAND1687546/8/2005 12:31Crystal Reports54sleeping saMyServer . VPNMasterAWAITING COMMAND377296/8/2005 10:49MS SQLEM55RUNNABLE DomainUser2MyServer .masterSELECT 86006/8/2005 12:40SQL Profiler56RUNNABLE DomainUser2LT2 . VPNSELECT34496/8/2005 12:50Microsoft® Access57sleeping DomainUser3LT2 . VPNMasterAWAITING COMMAND110296/8/2005 12:52Microsoft® Access58sleeping DomainUser1LT1 .masterAWAITING COMMAND1172156/8/2005 9:31Crystal Reports59sleeping DomainUser4LT2 . VPNMasterAWAITING COMMAND1516/8/2005 12:50Microsoft® Access62sleeping DomainUser5LT2 . VPNMasterAWAITING COMMAND3226/8/2005 12:52Microsoft® Access63sleeping DomainUser6LT2 . VPNMasterAWAITING COMMAND50106/8/2005 12:52Microsoft® Access64sleeping DomainUser6LT2 . VPNAWAITINGCOMMAND101606/8/2005 12:52Microsoft® Access65sleeping DomainUser6LT2 . VPNMasterAWAITING COMMAND47496/8/2005 12:52Microsoft® Access66sleeping DomainUser3LT2 . VPNAWAITINGCOMMAND20306/8/2005 12:50Microsoft® Access67sleeping DomainUser6LT2 . VPNAWAITINGCOMMAND24906/8/2005 12:52Microsoft® Access68RUNNABLE saMyServer . masterSELECTINTO 62126/8/2005 12:59SQL Query AnalyzerThanks,Eric
View Replies !
Why XML Output Is Truncated
Hi,I am trying to generate an XML output form the database. I amexecutingselect * from Request_vw for xml autoThe output data is being truncated. is there a limit on the outputresult of an XML query?Thanks
View Replies !
Output Parameters From Row With Max Id
I am trying to select multiple values from the row with the max id,and output them within my stored procedure. How would I combine thefollowing two rows so that I only need to use "from mytable whereIDnum = (select (max(IDnum)) from mytable))" once. I have at least 8other outputs I will be setting and would like this to work moreefficiently than setting each individually... if possible.set @outmtd = (select outmtd from mytable where IDnum = (select(max(IDnum)) from mytable))set @outytd = (select outytd from mytable where IDnum = (select(max(IDnum)) from mytable))Thanks for any help you can giveMatt
View Replies !
Formatted Output
Hi,Is there a way to get stored procedure texts out in a formatted waylike defncopy in Sybase????? The way they print it out reallysucks............RegardsSubhas
View Replies !
Most Recent Output
hello,I have a table which contains the audit time field. This audit time fieldrecords the current date and time when a record is inserted.I would like to output the most recent top 500 rows.thank you.
View Replies !
Formatting Output
Hi, I am running a script which inserts certain rows into a table and at the end of the execution, I do a select statement to show the inserted data as output in the results pane and it is showing as truncated...How can I show the full results.. Here is my script to show the results. ---------- SET NOCOUNT ON DECLARE @errorCount INT SELECT @errorCount = COUNT(*) FROM error_report WHERE id != - 2 ANDid != - 5 IF @errorCount = 0 BEGIN INSERT INTO error_report VALUES( '' , - 1 , 'No error found.' ) END INSERT INTO error_report VALUES( '' , - 2 , 'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR) ) GO SELECT table_name + ' table has bad data at id = ' + CAST(CONVERT(VARCHAR(23), id) AS VARCHAR) + ' (' + CAST(reason AS VARCHAR) + ')'FROM error_report WHERE id > 0 SELECT table_name + ' table has bad data (' + CAST(reason AS VARCHAR) + ')' FROM error_report WHERE id = 0 SELECT reason FROM error_report WHERE id = - 1 SELECT '' SELECT reason FROM error_report WHERE id = - 2 SET NOCOUNT OFF GO ------------------------- The Results in the bottom pane looks like this below ------------------ NODETABLE table has bad data (There are 2 duplicate subclass) Propertytable table has bad data (at Parentid = 2000000859 and p) Propertytable table has bad data (at Parentid = 10122 and proper) ------------------- But, when I do a select, they are like this below ---- NODETABLE0There are 2 duplicate subclass name: Specification Propertytable0at Parentid = 2000000859 and propertyid = 721 Propertytable0at Parentid = 10122 and propertyid = 9
View Replies !
|
TRACKER
