Output Of Sp_helptext Without Coumn Name

Dec 24, 2001

sp_helptext 'sp_myst_proc'
produce

Text ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create procedure sp_myst_proc --- 1995/11/28 15:48
as
declare @spidlowint
.....

how to remove column heading from output ?

I am putting output to file so I don't want ....
Text ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

in my file

thanks for any tip .

View 2 Replies

Float Coumn Having Value -1.#IND

Mar 20, 2008

I had database with "-1.#IND" (Indefinite /infinite) values in float columns.

Is there anyway I can insert -1#IND value into float column using some insert query and query analyzer? I am using SQL Server 2000.

I want to insert this value to replicate the issue..just for testing. I am also wondring how my database got these values.

Thanks in Advance!

View 1 Replies View Related

Find Same Coumn In Other Tables

Jun 9, 2008

Is there any way i can find some certain fields in the whole database? like i know a column called Type_ID in table Sys_Type, how can i know there are same Type_ID column in other tables? Thanks in advance.

View 2 Replies View Related

Is There Sp_helptext For Tables

May 14, 2007

Hello Everybody, Please help me out:

Is there a system stored procedure for retrieving the sql statement that created a table.

I know i can use sp_helptext for views etc; i want the equivalent for tables.

sp_columns is not adequate either.

please help! thanks in advance;)

View 2 Replies View Related

Dose Sp_helptext Loss Something?

Aug 8, 2007

I am using sp_helptext to generate script of stored procedure. However someone said sp_helptext loss something if stored procedure is big. That means the script is not completed. Is it true?

Thanks

ZYT

View 6 Replies View Related

Calling Sp_helptext From ASPNET

Jul 23, 2005

Hi,I am executing a call to "sp_helptext" from a web service, in order toobtain the text of a stored procedure. The call itself seems to be ok buteventually it throws an exception saying that the specified object does notexist in the database - which is a false statement, i've checked carefully.I probably do not have enough rights to access something from the ASPNETaccount.Do i have to login as "sa"? Is this the only solution available?Bogdan.

View 1 Replies View Related

Sp_helptext In Select Statement

Oct 5, 2006

Hi,

I need to get one trigger script and save it into a table or files....anyway

this works well

exec sp_helptext 'triggername'

But, how can I save this rows into a table?

because I cannot execute this

Insert into mytables select sp_helptext 'triggername'

Can I work around this?

cheers,

View 4 Replies View Related

Sp_helptext Equivalent For Tables?

May 14, 2007

Hello Everybody, Please help me out:

Is there a system stored procedure for generating the sql statement that would create an existing table? For example, tblCustomers ("create table tblCustomers ( .....)")

I know i can use sp_helptext for views etc; i desire the equivalent for tables.

sp_columns is not adequate either.

please help! thanks in advance.

View 8 Replies View Related

Which Database Object Is Updating One Table's Coumn?

Jun 6, 2006

Hi all,

We have sql sever 2000 as our backend database system. on the front, we have vb medical application screen for users to do the data entry.

for 7th screen, it is assoicated with table Case_Custom. user enter data througth 7th screen to populate Case_Custom table.

There is one column called Case_Custom.CaseID ( a foreign key to to parent table Cases) was populated automatically instead of user input.

However, I could not find which stored procedure, views or trigger or application code to populate this field? I have queried INFORMATION_SCHEMA.ROUTINES, dbo.syscomments and did not find anything updating Case_Custom.CaseID.

I have contacted our app vendor and have got any answer from them yet.

Any ideas?

Thanks,

Jane

View 1 Replies View Related

How To Select A Word Substring In A Coumn Header From Right To Left

Oct 10, 2007

I have an application providing me with multiple headers which I havemergerd into one big header (below), this header my not always be thesame but I need to be able to extract a periodstart and periodend fromit. The periodstart will always be the third substring from the end(or 3rd from right) and the periodend will always be the firstsubstring from the end (or 1st from the right).How can I extract the periodstart and periodend?E.g:- Header'Jensen Alpha TR UKN GBP BM: Caut Mgd BM (50% FTAllSh 50% ML � BroadMkt) RF DEF:RFI 3Y 31/08/2004 To 31/08/2007'I currently have the sql: convert(Datetime,(dbo.FDHGetWord(@FullHeader, 20)) ,103) but this only works in thisinstance, I need to use someting like the RIGHT function or REVERSEfunction but I can't get the sql right.Can someone please help!????

View 1 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

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 2 Replies View Related

Transact SQL :: Generic Store Procedure Call Without Output Parameters But Catching Output

Sep 21, 2015

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]...

View 3 Replies View Related

Output And Error Output Write The Same Table At The Same Time, Stall The Process.

Aug 30, 2006

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 4 Replies View Related

Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation

Dec 8, 2006

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 9 Replies View Related

Query Produces Jumbled Output / Output Not In Sequence

Jul 23, 2005

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 Related

PrimeOutput : Difference Between 'Output' And 'output Buffer'

Aug 12, 2005

When 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 Related

SQL OUTPUT

Sep 14, 2007

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 1 Replies View Related

XML Output.

Mar 10, 2006

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 3 Replies View Related

Get Output From One SP In To Another SP

Oct 11, 2007

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 11 Replies View Related

DTS To Output .csv

May 20, 2004

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 2 Replies View Related

How To Get This Output?

Apr 25, 2008

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 4 Replies View Related

Output

Nov 15, 2007

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 5 Replies View Related

Different Output

Nov 20, 2007

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 2 Replies View Related

Output To Log

Jan 25, 2008

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 2 Replies View Related

XML Output

Jul 20, 2005

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 Related

OUTPUT - Help With Please

Aug 29, 2007

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 7 Replies View Related

Output One Row

Oct 9, 2006

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 3 Replies View Related

Pdf Only Output !

Jul 11, 2007

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 6 Replies View Related

Output In Xml

May 14, 2007

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 5 Replies View Related

Getting Value From Output Parameter

Aug 2, 2006

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 Related

Output Parameter?

Aug 25, 2006

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 1 Replies View Related

How Can I Get And Use An OUTPUT Parameter

Jan 31, 2007

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 6 Replies View Related

2 Tables, 1 Output?

Apr 18, 2007

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 5 Replies View Related