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,
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
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
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
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
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
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.
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
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 RelatedHi,
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.
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!
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.
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
HI...
I need to do a simple task but it's difficult to a newbie on ssis..
i have two tables...
first one has an identity column and the second has fk to the first...
to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!
i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"
here is the script
INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)
what's the problem ??
Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?
Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)
Hi,
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)
TIA
Code Snippet
GO
CREATE TABLE [dbo].[Tokens](
[TokenID] [int] IDENTITY(1,1) NOT NULL,
[Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED
(
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens]
(
[Token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[SplitTokenList]
(
@TokenList varchar(max)
)
RETURNS
@ParsedList table
(
Token varchar(255)
)
AS
BEGIN
DECLARE @Token varchar(50), @Pos int
SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
SET @Pos = CHARINDEX(',', @TokenList , 1)
IF REPLACE(@TokenList , ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
IF @Token <> ''
BEGIN
INSERT INTO @ParsedList (Token)
VALUES (@Token) --Use Appropriate conversion
END
SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
SET @Pos = CHARINDEX(',', @TokenList, 1)
END
END
RETURN
END
GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
@TokenList varchar(max)
AS
BEGIN
SET NOCOUNT ON;
create table #Tokens (TokenID int default 0, Token varchar(50))
create clustered index Tind on #T (Token)
DECLARE @NewTokens table
(
TokenID int default 0,
Token varchar(50)
)
--Split ID's into a table
INSERT INTO #Tokens(Token)
SELECT Token FROM SplitTokenList(@TokenList)
BEGIN TRY
BEGIN TRANSACTION
--get ID's for any existing tokens
UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0)
FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token)
OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens
SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old
SELECT TokenID, Token FROM #Tokens
WHERE TokenID <> 0
UNION
SELECT TokenID, Token FROM @Tokens
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @er nvarchar(max)
SET @er = ERROR_MESSAGE();
RAISERROR(@er, 14,1);
ROLLBACK TRAN
END CATCH;
END
GO
Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
Hi All,
I have seen a few other people have this error.
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
I want to allow visitors to filter a list of events to show only those belonging to categories selected from a checklist.
Here is an approach I am trying:
TABLE Events(EventID int, Categories varchar(200))
EventID Catetories
--------------------------
1 ‘6,8,9’
2 ‘2,3’
PROCEDURE ListFilteredEvents
@FilterList varchar(200) -- contains ‘3,5’
AS
SELECT EventID FROM Events
WHERE (any value in Categories) IN @FilterList
Result:
EventID
----------
2
How can I select all records where any value in the Categories column
matches a value in @FilterList. In this example, record 2 would be
selected since it belongs to category 3, which is also in @FilterList.
I’ve looked at the table of numbers approach, which works when
selecting records where a column value is in the parameter list, but I
can’t see how to make this work when the column itself also contains a
comma delimited list.
Can someone suggest an approach?
Any examples would be greatly appreciated!
Gary
Amol writes "I wish to write a UDF that will concatenate values from each row of a column for a select query. for each invocation of the UDF it should check the callee query to see if its the next row of the current query and concatenate the value or if its a new query start from null.
eg. : select list(name) from employee
will return a "single" list of all names in the employee table as a comma delimited string.
TIA, Amol."
I have tilted and need some help.
I have a table with columns:
ArtNo, WareNo, State + a few others
Sample data:
111, 1, 3
111, 2, null
111, 4, 10
222, 1, 8
222, 2, 3
222, 1, 3
and so on
Each artNo is in several WareNo and has a state
Each WareNo has several ArtNo
I want to run through the table and get * for each combination of ArtNo&WareNo that does not have any post in the table with State = 3.
Best regards
Inno
Hello everybody,
I am new to the whole reporting World and I was wondering if their is a solution for my problem. I searched for one and a half hour and I am getting desperate.
I need to repeat a report. So I thought: I can use a list for that. But when I placed all my objects in that list and linked it to a data source it gives an error at every object. I had this problem before and I made a work around so I did not solve it. I need a list in a list or a matrix in a list. I get the following errors:
Error 1 [rsDataRegionInDetailList] The list €˜list3€™ is in a list that has no group expressions defined for it. To use a data region in a list, the list must have group expressions. c:projectencitrix estcitrixusagedb
eportCitrixUsage.rdl 0 0
Error 2 [rsFieldReference] The Value expression for the textbox €˜textbox16€™ refers to the field €˜USERNAME€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope. c:projectencitrix estcitrixusagedb
eportCitrixUsage.rdl 0 0
I hope you can help me, thanks in advance!
Yours,
Stef
I have done queries before where you have something like this:
Select name
From people
Where uid in (Select UID from employees where salary > 500000)
as an example.
I have to use stored procedures, I can't connect directly...
I have a datatable of records that I select from a database. In the database table there is a flag 'checkedOut' (bit field) wich I want to set to true (1) for each of these records.
Since I have to use stored procedures I think I am limited to passing in parameters. I wrote a function using a stringbuilder to concatonate the key fields into a comma delimited list "1, 55, 98" etc. and thought to use it as the clause in the 'in' sample ie:Update ServiceRequests set
CheckedOut = 1
Where UID in (@UIDList)
I have tried a few different things, none works.
Any ideas how you might accomplish this with stored procedures?
The records are passed to a thread to be dealt with and it's possible the next time it runs it will pick up the same records for processing if the first thread isn't through. I needed the 'CheckedOut' to ensure I don't accidentally do that.
Thanks.
Hello All,
I need to download the table containing the list of the database in sql server 2005, where can this be downloaded
thankx
Bibek
How can I get a list of the last payment for each client?
SELECT ClientID,PaymentDate,PaymentAmount FROM tblClients INNER JOIN tblPayments ON tblPayments.ClientID = tblClients.ClientID
HI,
say I have a list from an sql statement (results list)
this list contains 10 items
In another table, in one particular column - there is a match for one of these items from the initial list.
SO... this may be the list
_____________________
itemnumber
1
2
3
4
5
6
7
8
9
10
------------------------------
in the other table there is a match...
but just for one item on that list.
____________________
othertablefield
11
13
14
3 <------ match
99
78
----------------------------
How do I find that match with my sql statement?
Hi,
Is there anything equivalent to Oracle's Select * from tab in MS SQL.
i need to give list all table in my database plaese give me qurity?
thanks a lot
Hi friends,
How I can get list of tables and list of fields within those tables in SQL server.
Thnak a lot.
How to obtain the list of the tables of a base by un script
View 1 Replies View RelatedConverting Sybase to SQL Server !
On the Sql Server's database I did :
-- test list function with cursor
alter proc proc_list(@key varchar(255), @retlistvalues varchar(8000) output)
as
declare @incrcurs2 cursor
declare @value varchar(255)
declare @listvalues varchar(8000)
set @incrcurs2 = cursor scroll dynamic
for
select t.title_Id from publishers p, titles t where p.pub_id = t.pub_idand p.pub_id = @key
order by t.pub_id
open @incrcurs2
fetch next from @incrcurs2 into @value
select @listvalues = convert(varchar(255),@value)
WHILE @@FETCH_STATUS = 0
begin
select @listvalues = @listvalues + ',' + convert(varchar(255),@value)
fetch next from @incrcurs2 into @value
end
CLOSE @incrcurs2
DEALLOCATE @incrcurs2
select @retlistvalues = @listvalues
go
declare @listvalues varchar(8000)
execute proc_list '1389',@listvalues output
print @listvalues
With Sybase, easily :
Select t.pub_id, list(t.title_Id) from publishers p, titles t where p.pub_id = t.pub_id
And I 've got all the titles in a comma-separated string by publisher !!!!
So, with SQL Server, I suppose that if I want the result by publisher, I must imbricate two cursors !!!
Perhaps I dream
Or can somebody see a better solution ????
Thanks
Axel Thimonier