Using ExecutionInstanceGUID As Query Paramter

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


ADVERTISEMENT

Passing ExecutionInstanceGuid To Sp Changes Value

Dec 18, 2007

I am passing System::ExecutionInstanceGuid to a stored procudure using the Execute SQL task (OLEDB connection). When it is being passed as a GUID (through the parameter mapping screen), the wrong value gets passed to the procedure. If i change the Parameter Data Type to VARCHAR, it passes the correct value.

This is also true for the PackageId.

Has anyone seen this?

Thanks,
Abe

View 4 Replies View Related

System::ExecutionInstanceGUID &&<&&> Logging ExecutionId ????

Nov 15, 2007

Any help in understanding and working around this odd behavior is greatly appreciated!

I have the default SQL database logging enabled (to sysdtslog90).
I start my package with an ExecuteSQL task that writes most of the system variables to an execution log.
Included is the value of the System::ExecutionInstanceGUID variable (which I thought should match the standard SSIS logging executionid value).

I have an error handling ExecuteSQL task that updates my execution log with an end time and the task that failed.
This is right out of the ProjectREAL audit approach.

Problem with the Error ExecuteSQL update is that it is trying to match the SSIS log but cannot.
This is the SQL being used:
select top 1 @failureTask = source
from dbo.sysdtslog90
where executionid = @executionGuid
and (upper(event) = 'ONERROR')
and upper(source) <> @packageName
order by endtime desc

When I enable OnInformation event logging something interesting appears.
The ExecutionGUID changes during validation of my one Data Flow Task in the package YET the system variable does not get updated with the new value!!!!

Log Excerpt:
1 OnInformation DFT Load Customer Test B94FDA4C-46D9-4193-BD87-12CFCB31EA1A Validation phase is beginning.
2 OnInformation DFT Load Customer Test 282F7D2E-329A-49DB-90B6-838053114940 Validation phase is beginning.
3 PackageStart AuditSample 282F7D2E-329A-49DB-90B6-838053114940 Beginning of package execution.
4 OnPreExecute AuditSample 282F7D2E-329A-49DB-90B6-838053114940
5 OnPreExecute SCR Set Correct Execution GUID 282F7D2E-329A-49DB-90B6-838053114940
6 OnVariableValueChanged AuditSample 282F7D2E-329A-49DB-90B6-838053114940 ExecutionGUID
7 OnInformation SCR Set Correct Execution GUID 282F7D2E-329A-49DB-90B6-838053114940 System Variable - Package Name AuditSample Start Time 11/14/2007 8:56:26 PM ExecutionGuid b94fda4c-46d9-4193-bd87-12cfcb31ea1a


Please also reference http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=420279&SiteID=1

View 10 Replies View Related

Two Log Files Generated Using C:\ + @[System::ExecutionInstanceGUID] + .log

Jan 12, 2007

Why are two log files generated when using a Text Log Provider with a Connection String property set to the following expression?

"c:\" + @[System::ExecutionInstanceGUID] + ".log"

I've set up package logging with a view to getting a unique, and linkable log. However, two files are always generated, no matter what the DelayValidation settings may be on the connection manager, file, and package tasks.

View 4 Replies View Related

Pass A Paramter

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

How To Add Another Row To Paramter List Which From

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

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

Passing Paramter To Sp_ExecuteSql

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

Add Spaces To Select Paramter

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

Oupput Paramter From A Stored Procedure

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

Input Paramter As Part Of OPENDATASOURCE

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

Passing Multiple Values To A Paramter

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

Adding Total To Paramter Drop Down List

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

Custom Parameter For Select Paramters - How To Use The Value Of Another Field As The Paramter Default.

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

Issue With Executing Dynamic Sql With Inbound Paramter As Varchar Striong

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

Query Runs Fine In Query Analyser But Not The Query Debugger

Dec 19, 2003

I'm running a query, actually its an insert that works when using the TSQL below.

However when I try to use the debugger to step through and using the exact same values as those below I get the following error:

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

Its Killing me because everything else works, but this. Can somebody help.


DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,

@BeenRead NVARCHAR(10),-- = NULL

@FK_UserIDList NVARCHAR(4000)-- = NULL


--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'

SET @FK_UserIDList = '1,2,3'


--AS

--SET NOCOUNT ON


SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN TRANSACTION

SET @Date = GETDATE()

-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)

SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,

@Job_Date,
@Start,
@Finish

-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY


-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList

-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT

COMMIT TRANSACTION

--------------------------------------------------
GO

View 1 Replies View Related

Query Diff Results From Ent Manager Query And Query Analizer

May 28, 2008

ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)

SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007

but in query analizer I get the result of

12/31/2006

Why the different dates

View 4 Replies View Related

[Query] - Query Designer Encountered A Query Error:Unspecified Error

Jan 22, 2001

Hi,

I get this error dialog when I try to open all the rows of any table from Enterprise manager..

Any help would be really appreciated..

Thanks,
-Srini.

View 1 Replies View Related

Error: 8624 Internal Query Processor Error: The Query Processor Could Not Produce A Query Plan.

May 24, 2007

SQL Server 2005 9.0.3161 on Win 2k3 R2



I receive the following error:



"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."



I have traced this to an insert statement that executes as part of a stored procedure.



INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)

VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)



There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.



Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).

View 5 Replies View Related

Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!

Mar 28, 2007

Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
 SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC

 Here is the page source
 
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="&#9;SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches&#13;&#10;&#9;FROM dbo.MAKES INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID&#13;&#10;&#9;WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )&#13;&#10;&#9;GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID&#13;&#10;&#9;HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2&#13;&#10;&#9;ORDER BY count(*) DESC&#13;&#10;">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
 AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
 Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
 Please help!
 
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
 

View 4 Replies View Related

SQL Server 2012 :: Adding Count To Query Without Duplicating Original Select Query

Aug 5, 2014

I have the following code.

SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,

[code]...

However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.

View 2 Replies View Related

Opening Up Odbc Data Source In The Query Query Inside Of The Server Manager

Jun 15, 2007

I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.



Thanks in advance



Daniel Buchanan.



If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.

View 1 Replies View Related

Master Data Services :: Error - Query Processor Could Not Produce A Query Plan

Jul 19, 2015

We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T

• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues

• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"

• We are using the last version of Excel Add-in

• We try to reinstall the MDS feature

• If I backup/restore MDS database to other server it works

• We updated to SQL 2012 SP2 + CU4 but the error persisted ...

Looking at the MDSTraceLog we are routed to the this msg

SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28

At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped

** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
   at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
   at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)

[code]....

View 3 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

Query Fails With Invalid Column Name But Succeed As Sub-query With Unexpected Results

Sep 22, 2015

-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"

set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo

[code]....

This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?

View 2 Replies View Related

Why The Query Works In Query Analyser And Doesn't Work In Reporting Service ?

Apr 30, 2007



Hello everybody,



I'm developing a report using the following structure :



declare @sql as nvarchar(4000)

declare @where as nvarchar(2000)



set @sql = 'select ....'



If <conditional1>

begin

set @where = 'some where'

end



If <conditional2>

begin

set @where = 'some where'

end



set @sql = @sql + @where



exec(@sql)



I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.



I realize that when I cut off the if clauses, then it works at Reporting services.

Does anybody know what is happening?

Why the query works in query analyser and doesn't work in Reporting Service ?



Thanks,



Maurício

View 2 Replies View Related

DB Engine :: Multiple Execution Of Query Pattern Generates Same Query Plan

Oct 6, 2015

SQL Server 2012 Performance Dashboard Main advices me this:

Since the application is from a vendor and I have no control over its code, how can improve this sitation?

View 3 Replies View Related

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 Replies View Related

Can A Calc'd Query Column Be Compared Against A Multi Value Variable Without A Nested Query?

Nov 15, 2007

do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like

SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)

View 1 Replies View Related

SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements

Oct 30, 2015

When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.

1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?

<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>

2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?

View 0 Replies View Related

Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?

Mar 25, 2008

Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:


USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[SalesByCategory]

@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'

AS

IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'

BEGIN

SELECT @OrdYear = '1998'

END

SELECT ProductName,

TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)

FROM [Order Details] OD, Orders O, Products P, Categories C

WHERE OD.OrderID = O.OrderID

AND OD.ProductID = P.ProductID

AND P.CategoryID = C.CategoryID

AND C.CategoryName = @CategoryName

AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear

GROUP BY ProductName

ORDER BY ProductName

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:

Imports System.Collections.Generic

Imports System.ComponentModel

Imports System.Drawing

Imports System.Text

Imports System.Windows.Forms

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.Common

Imports System.Diagnostics

Public Class ConnectionPoolingForm

Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

'Force app to be available for SqlClient perf counting

Using cn As New SqlConnection()

End Using

InitializeMinSize()

InitializePerfCounters()

End Sub

Sub InitializeMinSize()

Me.MinimumSize = Me.Size

End Sub

Dim _SelectedConnection As DbConnection = Nothing

Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged

_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub DisableAllButtons()

btnAdd.Enabled = False

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

btnClearAllPools.Enabled = False

End Sub

Sub EnableOrDisableButtons(ByVal cn As DbConnection)

btnAdd.Enabled = True

If cn Is Nothing Then

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

Else

Dim connectionState As ConnectionState = cn.State

btnOpen.Enabled = (connectionState = connectionState.Closed)

btnQuery.Enabled = (connectionState = connectionState.Open)

btnClose.Enabled = btnQuery.Enabled

btnRemove.Enabled = True

If Not (TryCast(cn, SqlConnection) Is Nothing) Then

btnClearPool.Enabled = True

End If

End If

btnClearAllPools.Enabled = True

End Sub

Sub StartWaitUI()

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

End Sub

Sub EndWaitUI()

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub SetStatus(ByVal NewStatus As String)

RefreshPerfCounters()

Me.statusStrip.Items(0).Text = NewStatus

End Sub

Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click

Dim strConn As String = txtConnectionString.Text

Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()

Try

bldr.ConnectionString = strConn

Catch ex As Exception

MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)

Return

End Try

Dim dlg As New ConnectionStringBuilderDialog()

If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then

txtConnectionString.Text = dlg.ConnectionString

SetStatus("Ready")

Else

SetStatus("Operation cancelled")

End If

End Sub

Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click

Dim blnError As Boolean = False

Dim strErrorMessage As String = ""

Dim strErrorCaption As String = "Connection attempt failed"

StartWaitUI()

Try

Dim cn As DbConnection = _ProviderFactory.CreateConnection()

cn.ConnectionString = txtConnectionString.Text

cn.Open()

lstConnections.SelectedIndex = lstConnections.Items.Add(cn)

Catch ex As Exception

blnError = True

strErrorMessage = ex.Message

End Try

EndWaitUI()

If blnError Then

SetStatus(strErrorCaption)

MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

Else

SetStatus("Connection opened succesfully")

End If

End Sub

Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click

StartWaitUI()

Try

_SelectedConnection.Open()

EnableOrDisableButtons(_SelectedConnection)

SetStatus("Connection opened succesfully")

EndWaitUI()

Catch ex As Exception

EndWaitUI()

Dim strErrorCaption As String = "Connection attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub

Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click

Dim queryDialog As New QueryDialog()

If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

Try

Dim cmd As DbCommand = _SelectedConnection.CreateCommand()

cmd.CommandText = queryDialog.txtQuery.Text

Using rdr As DbDataReader = cmd.ExecuteReader()

If rdr.HasRows Then

Dim resultsForm As New QueryResultsForm()

resultsForm.ShowResults(cmd.CommandText, rdr)

SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))

Else

SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))

End If

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Using

Catch ex As Exception

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

Dim strErrorCaption As String = "Query attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

Else

SetStatus("Operation cancelled")

End If

End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.

Thanks in advance,
Scott Chang

View 4 Replies View Related

SP To Perform Query Based On Multiple Rows From Another Query's Result Set

Nov 7, 2007

I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w.  one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users             SELECT u.userName,u.userID                 FROM  tableU u                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID                WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
                FROM  tableU u
                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
                WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!    

View 1 Replies View Related

Easy SQL Question. How To Display Query Results In Query Analyzer

Feb 12, 2008

When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare     @SniierId as   uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare    @SniierAlias  as nvarchar(50)declare    @AlwaysShowEditButton  as bitdeclare     @SniierName  as  nvarchar (128)/* Check access for Sniier */SELECT TOP 1       @SniierName      = Sniiers.SniierName,        @SniierAlias    = Sniiers.SniierAlias,        @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved