Problem Getting Output Paramter
May 15, 2008
I keep running a sproc that sets an output paramter, but when I try to retrieve it, it says it is empty Dim con As New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("QuotesDataBase").ConnectionString)
Dim command As New SqlCommand("quotes_Quotes_Search", con)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter() With {.ParameterName = "query", .DbType = DbType.String, .Direction = ParameterDirection.Input, .Value = query})
command.Parameters.Add(New SqlParameter() With {.ParameterName = "startRowIndex", .DbType = DbType.Int32, .Direction = ParameterDirection.Input, .Value = startRowIndex})
command.Parameters.Add(New SqlParameter() With {.ParameterName = "maximumRows", .DbType = DbType.Int32, .Direction = ParameterDirection.Input, .Value = maximumRows})
Dim param As New SqlParameter
param.ParameterName = "TotalResults"
param.Direction = ParameterDirection.Output
param.DbType = DbType.Int32
command.Parameters.Add(param)
command.Connection = con
con.Open()
Dim reader As SqlDataReader = command.ExecuteReader
totalResults = param.Value
View 4 Replies
ADVERTISEMENT
Dec 27, 2007
I have created this sp to and i cant figure out how to use the top 3 as a paramter. I want to be able to use any # like top1,2,3,4,5 etc. I not sure how to compose the syntax i have my sp below.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_moveBackupTables]
@serverName varchar(50) --Parameter
AS
BEGIN
declare @tableName varchar(50) --Variable
--Declare Cursor
DECLARE backup_Cursor CURSOR FOR
select name from adventureworksdw.dbo.sysobjects
where name like 'MyUsers_backup_%' and xtype = 'U'
and name not in(select top 3 name from adventureworksdw.dbo.sysobjects
where name like 'MyUsers_backup_%' and xtype = 'U' order by name desc)
OPEN backup_Cursor
--Move to initial record in the cursor and set variable(s) to result values
FETCH NEXT FROM backup_Cursor
INTO @tableName
--Loop through cursor records
WHILE @@FETCH_STATUS = 0
BEGIN
--dynamically build create table
Declare @SQL varchar(2000)
Set @SQL = 'CREATE TABLE ' + @serverName + '.dbo.' + @tableName + '(
[Id] [numeric](18, 0) NOT NULL,
[Field1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Field2] [numeric](18, 0) NOT NULL,
[Field3] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Field4] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Field5] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_1' + @tableName + '] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'
EXEC(@SQL)
--Insert values into new dynamically created table
Declare @backupTableRecords varchar(1000)
Set @backupTableRecords = 'Insert into ' + @serverName + '.dbo.' + @tableName + ' SELECT * FROM AdventureWorksDW.dbo.'+ @tableName
exec(@backupTableRecords)
--Drop old table
Declare @dropTable varchar(200)
set @dropTable = 'drop table adventureworksdw.dbo.' + @tableName
exec(@dropTable)
--Move to the next record in the Cursor and set variable(s) value(s)
FETCH NEXT FROM backup_Cursor INTO @tableName
END
CLOSE backup_Cursor
DEALLOCATE backup_Cursor
END
View 2 Replies
View Related
Jan 29, 2008
Hi,
I want to know how to add another row to paramter lists which from a stored procedure in the report. Is it possible? I don't want to use Multi-value becaue User only have one select.
Thanks,
View 3 Replies
View Related
Apr 21, 2006
I am executing a stored procedure from within other procedure with EXEC SPname Command. I have read that we should use sp_ExecuteSql system stored procedure in place of EXEC command because it will catch the execution plan whereas executing a statement or a stored procedure will not catch the execution plan. Now i am trying to execute my stored procedure as
Execute Sp_ExecuteSql @parameterName from within another stored procedure
where @paramter is an integer (but internet says that sp_executesql only accepts nvarchar/ntext datatype).So i am not able to really execute my stored procedure with sp_ExecuteSql. Am i missing out something..is there some prodedure to do this task???
ANY HELP WILL BE GREATLY APPRECIATED.
THANKS
View 5 Replies
View Related
Oct 30, 2007
Hi all,
I have a stored procedure that one of the select parameters is a NCHAR (250)
I need to select and RTRIM() is, and add 3 spaces
I've tried many options, but each time I get the full trimmed version of the column
This is what I've tried:
SELECT (RTRIM(c.customer_name) + space(3)) as CustomerName
SELECT (RTRIM(c.customer_name) + ' ') as CustomerName
SELECT (RTRIM(c.customer_name) + replicate(' ',3)) as CustomerName
Any suggestions?
Cheers
View 8 Replies
View Related
Jun 15, 2006
Hi all,
I'm stuck here trying to use the system variable ExecutionInstanceGUID.
During a first DataFlowTask I store the ExecutionInstanceGUID in a Table.
Further in my Control Flow I want to get all rows with the current InstanceGuid, my query is pretty simple:
SELECT ColumnA
FROM D_Data
WHERE InstanceGUID = ?
In the parameter mapping collection I have added a parameter which maps to System::ExecutionInstanceGUID
When I execute my package I have the following error:
"Message: component "OLE_SRC_SData" (1282) failed the pre-execute phase and returned error code 0xC0010001."
I tried to map the parameter to another variable and it works properly, I don't see what's wrong using ExecutionInstanceGUID like this.
Any help will be appreciated.
Sébastien.
View 3 Replies
View Related
Sep 5, 2000
Hi
I have a problem in validating the user ..if the user is not a valid user
it shouldn't execute the stored procedure ..but it should quit the SP and
return a message to the frontend application.The SP look something like this..
when I execute the below SP I get the following ERROR
Server: Msg 3902, Level 16, State 1, Line 0
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Not a Valid USER
Server: Msg 3902, Level 16, State 1, Procedure test, Line 118
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Not a Valid USER
HERE IS THE SP For REFERENCE
CREATE PROCEDURE test
@userid varchar(25),
@revenue_date datetime,
@veropt numeric(25)
AS
set nocount on
DECLARE @version NUMERIC(25)
DECLARE @stg_version NUMERIC(25)
DECLARE @record_count NUMERIC(25)
DECLARE @temp_Count NUMERIC(25)
DECLARE @Stg_Count NUMERIC(25)
DECLARE @Count_Temp_Exep NUMERIC(25)
DECLARE @Temp VARCHAR(20)
DECLARE C1 CURSOR FOR
SELECT userid FROM CEB_REVENUE_RECOG_USERID
OPEN C1
FETCH NEXT FROM C1 INTO @Temp
WHILE @@fetch_status = 0
BEGIN
IF @Temp <> @Userid
PRINT 'Not a Valid USER'
---IF THE USER IS NOT A VALID USER IT SHOULD RETURN A MESSAGE TO A FRONTEND APPLICATION SAYING THAT HE IS NOT A VALID USER
--AND IT SHOULD NOT EXECUTE FURTHER CODE
ELSE
BEGIN TRANSACTION one
if exists (select * from sysobjects where id = object_id(N'[dbo].[CEB_REVENUE_RECOG_TEMP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CEB_REVENUE_RECOG_TEMP]
CREATE TABLE [dbo].[CEB_REVENUE_RECOG_TEMP] (
[VERSION] [numeric](15, 0) NOT NULL ,
[STAGING_VERSION] [numeric](15, 0) NOT NULL,
[OPPORTUNITY_ID] [varchar] (15) NULL ,
[CURRENT_GRADE] [varchar] (15) NULL ,
[AMOUNT] [real] NULL ,
[NEGOTIATED_AMOUNT] [real] NULL ,
[ANTICIPATED_AMOUNT] [real] NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[REC_STATUS] [varchar] (1) NULL,
[PRIOR_YTD] [real] NULL ,
[MTD_REVENUE] [real] NULL ,
[YTD_REVENUE] [real] NULL ,
[DEFERRED] [real] NULL ,
[DAY1] [numeric](5,0) NULL,
[DAY2] [numeric](5,0) NULL,
[DAY3] [numeric](5,0) NULL,
[percentage] [numeric](5,0) NULL,
[YTDDAYS] [numeric](5,0) NULL
)
SELECT @version = max(version) from CEB_REVENUE_RECOG_AUDIT
SELECT @Stg_Count = Count(*) FROM CEB_STG_REVENUE_RECOG
IF @version IS NULL
BEGIN
SELECT @version = 1
END
ELSE
BEGIN
SELECT @version = @version +1
END
IF @veropt = 0
BEGIN
SELECT @stg_version = max(staging_version) from CEB_STG_REVENUE_RECOG_AUDIT
IF @stg_version IS NULL
BEGIN
SELECT @stg_version = 1
END
ELSE
BEGIN
SELECT @stg_version = @stg_version +1
END
INSERT INTO CEB_REVENUE_RECOG_AUDIT
(date, USERID, version, staging_version, starttime,REVENUE_DATE,STATUS)
values
(getdate(), @USERID, @version,@stg_version,getdate(),@revenue_date,'P')
INSERT INTO CEB_STG_REVENUE_RECOG_AUDIT (date, USERID, staging_version, starttime) values(getdate(), @USERID, @stg_version, getdate())
---DELETE FROM CEB_STG_REVENUE_RECOG_CEB
---EXEC 'Stored Procedure of Carlos'
SELECT @record_Count = count(*) From CEB_STG_REVENUE_RECOG
INSERT INTO CEB_STG_REVENUE_RECOG_HISTORICAL
SELECT @stg_version,OPPORTUNITY_ID,OPPORTUNITY_NAME, INSTITUTION_ID, INSTITUTION_NAME, ELVIS_ID,PROGRAM_ID,PRODUCT, OPPORTUNITY_TYPE,
OPPORTUNITY_STATUS, CURRENT_GRADE,MEMBERSHIP_YEAR,NEGOTIATED_AMOUNT,NE GOTIATED_DATE,ANTICIPATED_AMOUNT,START_DATE,
END_DATE,RENEWAL_MONTH,JOIN_DATE,MEMBERSHIP_PERIOD ,ORIG_NEG_AMOUNT,PREV_NEG_AMOUNT,EST_AMOUNT, PR_SAL_LAST_NAME,
PR_SAL_FST_NAME,PR_SAL_MID_NAME,CREATION_TIMESTAMP ,LAST_UPD_BY,REC_STATUS
FROM CEB_STG_REVENUE_RECOG
INSERT INTO CEB_REVENUE_RECOG_TEMP(VERSION,STAGING_VERSION, OPPORTUNITY_ID,CURRENT_GRADE,NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,START_DATE,END_DATE)
SELECT @version,@stg_version, OPPORTUNITY_ID,CURRENT_GRADE,NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,START_DATE, END_DATE
FROM CEB_STG_REVENUE_RECOG
--DELETE FROM CEB_STG_REVENUE_RECOG_CEB
UPDATE CEB_STG_REVENUE_RECOG_AUDIT SET ENDTIME = getdate(), RECORD_COUNT = @RECORD_COUNT
WHERE STAGING_VERSION = @stg_version
END
ELSE
BEGIN
INSERT INTO CEB_REVENUE_RECOG_AUDIT
(date, USERID, version, staging_version, starttime,REVENUE_DATE,STATUS)
values (getdate(), @USERID, @version,@stg_version,getdate(),@revenue_date,'P')
INSERT INTO CEB_REVENUE_RECOG_TEMP(VERSION, STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRADE, NEGOTIATED_AMOUNT,
ANTICIPATED_AMOUNT,START_DATE,END_DATE)
SELECT @version,@stg_version, OPPORTUNITY_ID,CURRENT_GRADE,NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,START_DATE, END_DATE
FROM CEB_STG_REVENUE_RECOG_HISTORICAL WHERE STAGING_VERSION = @veropt
SELECT @record_count = COUNT(*) FROM CEB_REVENUE_RECOG_TEMP
UPDATE CEB_REVENUE_RECOG_AUDIT set ENDTIME = getdate(), STATUS = 'C' where VERSION = @version
END
COMMIT TRANSACTION one
BEGIN TRANSACTION two
UPDATE CEB_REVENUE_RECOG_TEMP SET PRIOR_YTD = 0, YTD_REVENUE=0, MTD_REVENUE=0, AMOUNT = 0, deferred=0
UPDATE CEB_REVENUE_RECOG_TEMP SET REC_STATUS = 'B' WHERE LEN(END_DATE)>=10
UPDATE CEB_REVENUE_RECOG_TEMP SET REC_STATUS = 'P', END_DATE = start_date + 364 WHERE (LEN(END_DATE) = 0 OR END_DATE IS NULL)
UPDATE CEB_REVENUE_RECOG_TEMP SET CEB_REVENUE_RECOG_TEMP.PERCENTAGE = CEB_REVENUE_RECOG_GRADES.percentage
from CEB_REVENUE_RECOG_TEMP INNER JOIN CEB_REVENUE_RECOG_GRADES
ON CEB_REVENUE_RECOG_TEMP.CURRENT_GRADE = CEB_REVENUE_RECOG_GRADES.GRADE
UPDATE CEB_REVENUE_RECOG_TEMP SET CEB_REVENUE_RECOG_TEMP.PERCENTAGE = 100
WHERE CEB_REVENUE_RECOG_TEMP.percentage IS NULL
UPDATE CEB_REVENUE_RECOG_TEMP SET amount = CEB_REVENUE_RECOG_TEMP. ANTICIPATED_AMOUNT * percentage / 100
from CEB_REVENUE_RECOG_TEMP WHERE REC_STATUS = 'P'
UPDATE CEB_REVENUE_RECOG_TEMP SET amount = negotiated_AMOUNT
from CEB_REVENUE_RECOG_TEMP WHERE REC_STATUS = 'B'
/*--TODAY'S ADDITION--*/
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION, OPPORTUNITY_ID,CURRENT_GRADE, NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE,EN D_DATE,'06',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE OPPORTUNITY_ID IS NULL OR OPPORTUNITY_ID = ' '
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE OPPORTUNITY_ID IS NULL OR OPPORTUNITY_ID = ' '
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION, OPPORTUNITY_ID, CURRENT_GRADE, NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT, START_DATE, END_DATE, '02',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date > @REVENUE_DATE
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date >@REVENUE_DATE
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION, OPPORTUNITY_ID,CURRENT_GRADE, NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE,EN D_DATE,'03',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date IS NULL
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date IS NULL
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRA DE,NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE ,END_DATE, '01',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date > End_Date
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date > End_Date
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRA DE,NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE ,END_DATE, '04',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE AMOUNT < 0
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE AMOUNT < 0
INSERT INTO CEB_REVENUE_RECOG_YTD(OPPORTUNITY_ID, MYTD_REVENUE, YTDDAYS)
SELECT OPPORTUNITY_ID, 0 ,0
FROM CEB_REVENUE_RECOG_TEMP
WHERE OPPORTUNITY_ID
NOT IN (SELECT OPPORTUNITY_ID FROM CEB_REVENUE_RECOG_YTD)
UPDATE CEB_REVENUE_RECOG_TEMP SET PRIOR_YTD = CEB_REVENUE_RECOG_YTD.MYTD_REVENUE, YTDDAYS = CEB_REVENUE_RECOG_YTD.YTDDAYS
FROM CEB_REVENUE_RECOG_TEMP inner join CEB_REVENUE_RECOG_YTD
ON CEB_REVENUE_RECOG_TEMP.OPPORTUNITY_ID = CEB_REVENUE_RECOG_YTD.OPPORTUNITY_ID
UPDATE CEB_REVENUE_RECOG_TEMP SET day1 = datediff(day,start_date,@revenue_date)+1 - YTDDAYS, day2 = datediff(day,start_date,END_DATE) + 1 - YTDDAYS
UPDATE CEB_REVENUE_RECOG_TEMP SET DAY3 = DAY1
UPDATE CEB_REVENUE_RECOG_TEMP SET DAY3 = DAY2 WHERE DAY1 > DAY2
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRA DE,NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE ,END_DATE, '05',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE day2 = 0
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE day2 = 0
UPDATE CEB_REVENUE_RECOG_TEMP SET mtd_revenue = Day3 * (amount - PRIOR_YTD) / day2
UPDATE CEB_REVENUE_RECOG_TEMP SET mtd_revenue = amount - prior_ytd WHERE amount < prior_ytd
INSERT INTO CEB_REVENUE_RECOG_TEMP(VERSION,STAGING_VERSION,OPP ORTUNITY_ID,CURRENT_GRADE,MTD_REVENUE, NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,
START_DATE,END_DATE,REC_STATUS,AMOUNT,YTDDAYS,DAY2 ,PRIOR_YTD)
SELECT @version, @stg_version,CEB_REVENUE_RECOG_EXCEPTION.OPPORTUNI TY_ID,CURRENT_GRADE, (CEB_REVENUE_RECOG_EXCEPTION.AMOUNT-CEB_REVENUE_RECOG_YTD.MYTD_REVENUE),
NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE, END_DATE,REC_STATUS,AMOUNT,CEB_REVENUE_RECOG_YTD.Y TDDAYS,0,MYTD_REVENUE
FROM CEB_REVENUE_RECOG_EXCEPTION INNER JOIN CEB_REVENUE_RECOG_YTD
ON CEB_REVENUE_RECOG_EXCEPTION.OPPORTUNITY_ID = CEB_REVENUE_RECOG_YTD.OPPORTUNITY_ID
WHERE CEB_REVENUE_RECOG_EXCEPTION.VERSION = @version AND CODE = '05' AND
(CEB_REVENUE_RECOG_YTD.MYTD_REVENUE - CEB_REVENUE_RECOG_EXCEPTION.AMOUNT) <> 0
SELECT @Temp_Count = COUNT(*) FROM CEB_REVENUE_RECOG_TEMP
/*--TODAYS ADDITION--*/
UPDATE CEB_REVENUE_RECOG_TEMP SET NEGOTIATED_AMOUNT = 0, ANTICIPATED_AMOUNT = 0 WHERE NEGOTIATED_AMOUNT IS NULL AND ANTICIPATED_AMOUNT IS NULL
UPDATE CEB_REVENUE_RECOG_TEMP SET ytd_revenue = mtd_revenue + PRIOR_YTD, DEFERRED = AMOUNT - mtd_revenue - PRIOR_YTD
UPDATE CEB_REVENUE_RECOG_TEMP SET DEFERRED = 0 WHERE YTD_REVENUE> =amount
INSERT INTO CEB_REVENUE_RECOG_HISTORICAL(VERSION,STAGING_VERSI ON,OPPORTUNITY_ID,CURRENT_GRADE,AMOUNT,NEGOTIATED_ AMOUNT,ANTICIPATED_AMOUNT,
START_DATE,END_DATE,REC_STATUS,REVENUE_DATE,PRIOR_ YTD,MTD_REVENUE,YTD_REVENUE,DEFERRED,REMAINING_DAY S,DAYS_IN_PERIOD,PERCENTAGE,ADJUSTMENT_FLAG)
SELECT VERSION,STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRA DE, AMOUNT,NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,START_DATE,END_DATE,
REC_STATUS,@revenue_date,PRIOR_YTD, MTD_REVENUE, YTD_REVENUE,DEFERRED,DAY2,0,100,'A'
FROM CEB_REVENUE_RECOG_TEMP
--DELETE FROM CEB_REVENUE_RECOG_TEMP
UPDATE CEB_REVENUE_RECOG_AUDIT SET ENDTIME = getdate(), RECORD_COUNT = @RECORD_COUNT, STATUS = 'C' WHERE VERSION = @version
UPDATE CEB_REVENUE_RECOG_YTD SET MYTD_REVENUE = MYTD_REVENUE + CEB_REVENUE_RECOG_TEMP.mtd_revenue,
CEB_REVENUE_RECOG_YTD.YTDDAYS = CEB_REVENUE_RECOG_YTD.YTDDAYS + CEB_REVENUE_RECOG_TEMP.DAY3
FROM CEB_REVENUE_RECOG_YTD INNER JOIN CEB_REVENUE_RECOG_TEMP
ON CEB_REVENUE_RECOG_YTD.OPPORTUNITY_ID = CEB_REVENUE_RECOG_TEMP.OPPORTUNITY_ID
WHERE CEB_REVENUE_RECOG_TEMP.VERSION = @version
/*--INSERTING RECORD INTO EXCEPTION SUMMARY----*/
/*--TODAYS ADDITION--*/
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION_SUMMARY VALUES(@version,'00','Total Records in Temp',@Temp_Count)
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION_SUMMARY
SELECT @version,CEB_REVENUE_RECOG_EXCEPTION.CODE,DESCRIPT ION ,COUNT(CEB_REVENUE_RECOG_EXCEPTION.CODE) as RECORD_COUNT
FROM CEB_REVENUE_RECOG_EXCEPTION INNER JOIN CEB_REVENUE_RECOG_EXCEPTION_CODES ON
CEB_REVENUE_RECOG_EXCEPTION.CODE = CEB_REVENUE_RECOG_EXCEPTION_CODES.CODE
WHERE VERSION = @version GROUP BY CEB_REVENUE_RECOG_EXCEPTION.CODE,DESCRIPTION
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION_SUMMARY VALUES(@version,'91','Total Records in Staging',@Stg_Count)
SELECT @Count_Temp_Exep = SUM(record_count) from CEB_REVENUE_RECOG_EXCEPTION_SUMMARY
where code < '90' and VERSION = @version
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION_SUMMARY VALUES(@version,'92','Record Count',@Count_Temp_Exep)
INSERT INTO CEB_REVENUE_RECOG_CLOSED_PERIOD VALUES(@revenue_date)
UPDATE CEB_REVENUE_RECOG_AUDIT SET Status = 'X' WHERE VERSION = @version
COMMIT TRANSACTION two
FETCH NEXT from C1 INTO @Temp
end
CLOSE C1
DEALLOCATE C1
THANKS
VENU
View 2 Replies
View Related
Jan 26, 2007
I want to use an input parameter as my filename, but I get a synataxerror message. Howerve, when I hard code the filename the proc compilessuccessfully.Thanks for any help. I'm using SQL Server 2005LTR_90,LTI_ELIG_pct,LTI_REC_pct,LOW_SALARY,HIGH_SALARY FROM OPENDATASOURCE("Microsoft.Jet.OLEDB.4.0","Data Source=C:inetpubwwwrootORC_Beta_Companies"' + @infilename +'"Extended Properties=Excel 8.0")...[summary_data$]Syntax message:Msg 102, Level 15, State 1, ProcedureimportExcelSpreadSheetIntoeNavigator_DataORC, Line 244Incorrect syntax near 'Microsoft.Jet.OLEDB.4.0'.
View 2 Replies
View Related
Apr 20, 2007
Hi,
I'm trying to pass multiple values to a single parameter from a report to a second report. For instance I want to pass the values a user selected in the original report, such as the countries a user select under a Country filter, and once the second report is called, I want that report to filter on those same countries, right now I can only pass one of the values selected to the second report. If someone can let me know if this is possible it'd be much appreciated, thanks in advance.
View 1 Replies
View Related
Apr 30, 2008
I was trying to write an expression someting like this.
(CASE WHEN (GroupVar2 IN('CBank','DTC', 'EDirect')) THEN GroupVar2 ELSE 'InstLend' END) AS COALESCE(GroupVar2,'Total') AS GroupVar2
In GroupVar2 column, following values are available;
CBank
DTC
EDirect
InstLend
Inst-Load
I use this for a parameter in my report. I want to consider inst-Load as the same as InstLend. In drop down menu , I should see only InstLend. When I select it, I should get summation of InstLend and Inst-Load.
Also I should see 'Total' as one of the available value. So when I select total it should give me summation of all of above.
Can anyone help me to write this corretly?
Thanks
View 1 Replies
View Related
Mar 5, 2008
SELECT ArticleID, ArticleTitle, ArticleDate, Published, PublishDate, PublishEndDateFROM UserArticlesWHERE (ArticleDate >= PublishDate) AND (ArticleDate <= PublishEndDate)
When I use the above on a GridView I get nothing. Using SQL manager it works great.
I don't know how to pass the value of the ArticleDate field as a default parameter or I think that's what I don't know how to do.
I am trying to create a app that I can set the dates an article will appear on a page and then go away depending on the date.
Thanks for any help!
View 1 Replies
View Related
Aug 4, 2007
Hi All:
I am trying to execute a dynamic sql, the dynamic sql makes use of an inbound paramter defined as varchar.
When I try to execute it fails, because it does not plavce the inbound paramter in quotes.
Any help would be appreciated.
In the bound search as an eaxmple can be" 'NY'
@P_SEARCH_VALUE='NY'
SET @V_SQL_FILTER = N' WHERE STATE = '+@P_SEARCH_VALUE
SET @V_SQL=@V_BASE_SQL+@V_SQL_FILTER
EXEC sp_executesql @V_SQL
Here is the v_sql out put:
SELECT TOP 100 * FROM V$ZIPCODE_LOOKUP_ALL WHERE STATE = NY
As you can see the sql will fail because the NY is not in quotes.
I tried using '@P_SEARCH_VALUE''' and other forms but could not get it work.
View 6 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
