Problem Returning A Declared Variable
when I run this sproc all I get out of it is "the commands completed successfully" and doesn't return the value. If anyone can point out where the error is I would really appreciate it. Thanks
Code:
Create Procedure LookupLeagueIdByUserName(@userName as varchar(40) = '') as
begin
if (@userName = '')
raiserror('LookupLeagueIdByUserName: Missing parameters', 16,1)
else
begin
Declare @leagueId int
Set @leagueId = -1
--Check if the username belong to a player
Select @leagueId = leagueId From Users u
inner join players p on p.userId = u.userId
inner join teams t on p.teamId = t.teamId
where u.userName = @userName
if (@leagueId > 0)
begin
return @leagueId
end
else
begin
--Check if the username belong to a teamUser
Select @leagueId = leagueId From Users u
inner join teamUsers tu on tu.userId = u.userId
inner join teams t on tu.teamId = t.teamId
where u.userName = @userName
if (@leagueId > 0)
begin
return @leagueId
end
else
begin
--Check if the username belong to a leagueUser
Select @leagueId = leagueId From Users u
inner join leagueUsers lu on lu.userId = u.userId
where u.userName = @userName
if (@leagueId > 0)
begin
return @leagueId
end
else
begin
--username is not in db or is an admin user
return -1
end
end
end
end
end
return
-- when I run this I get no results returned
LookupLeagueIdByUserName 'chris'
View Complete Forum Thread with Replies
Related Forum Messages:
Is It Possible To Use Twice Declared. Variable Names- KILL And After Declared. Variable
is it possible to use twice declared. Variable names- declared. Variable and after KILL and use the same declared. Variable like DECLARE @StartDate datetime KILL @StartDate datetime (remove from memory) use after with the same name i have 2 big stored PROCEDURE i need to put one after one and psss only 1 Variable name to the second stored PROCEDURE like this i don't get this error The variable name '@Start_Date' has already been declared. Variable names must be unique within a query batch or stored procedure. Msg 134, Level 15, State 1, Line 146 The variable name '@End_Date' has already been declared. Variable names must be unique within a query batch or stored procedure. i use like KILL @endDate ?? KILL @StartDate ?? TNX
View Replies !
Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task: DECLARE @DV INT; SET @DV = (SELECT MAX(DateValue) FROM tblTG); DECLARE @PV INT; SET @PV = @DV - 1; I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this: DECLARE @DV INT; SET @DV = ?; DECLARE @PV INT; SET @PV = @DV - 1; I have almost 50 references to these parameters in the query so a substitution would be helpful. Dan
View Replies !
Set Value For Variable In A Declared Cursor
Hi, I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue. ------------------------------------------------------------------------ DECLARE @str_var VARCHAR(10) DECLARE @field_val VARCHAR(10) DECLARE c1 CURSOR LOCAL FOR SELECT field1 FROM tableA WHERE field1 = @str_var WHILE (Sometime TRUE) BEGIN .... SET @str_var = 'set to some values, eg. ABC123, XYZ123' OPEN c1 FETCH c1 INTO @field_val WHILE (@@fetch_status != -1) BEGIN PRINT @field_val ... FETCH c1 INTO @field_val END CLOSE c1 END DEALLOCATE c1 ---------------------------------------------------------------------- Thanks a lots, Vincent
View Replies !
Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before. select @var = oldValue declare testCursor cursor for select someColumns from someTable where someColumn = @var select @var = newValue open testCursor fetch next from testCursor into @someColumns Thank's in advance. Mirko.
View Replies !
Obtaining Collation Length Of Declared Variable Within SP
Morning All,Can I have some help with this one please, I am having to make a fixed length text file based on information from the DBDeclare @EDIString varchar(MAX)Declare @RecordType varchar(2)Declare @RegistrationMark varchar(7)Declare @Model_Chassis varchar(11)Declare @LocationCode Varchar(4)Declare @MovementDate varchar(8)Declare @IMSAccountCode varchar(5)Declare @MovementType varchar(8)Declare @NotUsed1 Varchar(28)Declare @NotUsed2 varchar(7)Select @RecordType = RecordType, @RegistrationMark = RegistrationMark, @Model_Chassis = Model_And_Chassis, @LocationCode = LocationCode, @MovementDate = MovementDate, @IMSAccountCode = IMSAccountCode, @Movementtype = MovementTypeCode from Fiat_OutBoundOnce I have selected the information from the DB I need to ensure that each field is the correct length. I therefore want to pass the variable and the length of the variable into a function to return the correct length.So if location Code = 'AB' this needs to be four characters long so want to pass it into a function and return 'AB 'As I need to do this for 70+ variables is there an easy way to obtain the length of the collation for the variable?regardsTom
View Replies !
Select Declared Variable With Case Statements
I am trying to gather counts for table imports made for files from friday - sunday and create a variable that will be the body of an email. I'd like to use either a case statement or a while statement since the query is the same but the values must be collected for each day (friday, saturday and sunday) and will all be included in the same email. I have declared all variables appropriately but I will leave that section of the code out. Select @ifiledate = iFileDate from tblTelemark where iFileDate = CASE WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-3, 101), '/','') THEN Select @countfri1 = Count(*) from tbl1 Select @countfri2 = Count(*) from tbl2 Select @countfri3 = Count(*) from tbl3 Select @countfri4 = Count(*) from tbl4 WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-2, 101), '/','') THEN Select @countsat1 = Count(*) from tbl1 Select @countsat2 = Count(*) from tbl2 Select @countsat3 = Count(*) from tbl3 Select @countsat4 = Count(*) from tbl4 WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-1, 101), '/','') THEN Select @countsun1 = Count(*) from tbl1 Select @countsun2 = Count(*) from tbl2 Select @countsun3 = Count(*) from tbl3 Select @countsun4 = Count(*) from tbl4 END Is there a way to do what this that works???
View Replies !
Using Declared Variable As Passphrase Slows Query
I have two tables - gift_cards and history - each related by a field called "card_number". This field is encrypted in the history table but not in the gift_cards table. Let's say the passphrase is 'mypassphrase'. The following query takes about 1 second to execute with a fairly large amount of data in both tables: SELECT max([history].[date_of_wash]) AS LastUse FROM gift_cards AS gc LEFT JOIN history ON gc.card_number=CAST(DecryptByPassPhrase('mypassphrase', HISTORY.CARD_NUMBER) AS VARCHAR(50)) GROUP BY gc.card_number When I use a declared variable to contain the passphrase, the same query takes over 40 seconds. For example, declare @vchPassphrase as nvarchar(20) select @vchPassphrase = 'mypassphrase' SELECT max([history].[date_of_wash]) AS LastUse FROM gift_cards AS gc LEFT JOIN history ON gc.card_number=CAST(DecryptByPassPhrase(@vchPassphrase, HISTORY.CARD_NUMBER) AS VARCHAR(50)) GROUP BY gc.card_number This query is part of a stored procedure and, for security reasons, I can't embed the passphrase in it. Can anyone explain the discrepancy between execution times and suggest a way to make the second query execute faster? Thanks, SJonesy
View Replies !
Stored Procedure Using A Declared Variable In Insert Query (inline Or Using EXEC)
Hello, I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below: I want to be able to do this (with or without the EXEC) : ------------------------------------------------------------------------------------ DECLARE @NewTableNameOut as varchar(100) Set @NewTableNameOut = 'TableToInsertInto' EXEC( Insert Into @NewTableNameOut Select * From tableToSelectFrom ) ------------------------------------------------------------------------------------ I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure. I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above. ------------------------------------------------------------------------------------ DECLARE @NewTableNameOut as varchar(100) Set @NewTableNameOut = 'TableToInsertInto' EXEC( 'Insert Into ' + @NewTableNameOut + ' ' + 'Select * From tableToSelectFrom' ) ------------------------------------------------------------------------------------ It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string. Is this possible at all? If you need more info please let me know.
View Replies !
Update Statement Using Declared Variable Produces Unexpected Results On SQL Server 2005
We are getting unexpected results from the following update statement when it is executed on SQL Server 2005. The strange thing is that we get duplicated values for QM_UID (although when run under SQL Server 2000 we don't get duplicated values) Can anyone explain why this happens or suggest another way of populating this column without using a cursor or adding a temporary autoincrement column and copying the values over? declare @NextID int; set @NextID = 1; update tmp set QM_UID=@NextID, @NextID = @NextID + 1; select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID QM_UID count(*) 25 2 26 3 27 4 28 4 29 4 30 4 31 4 32 4 33 4 34 4 35 5 36 4 37 4 38 4 39 4 40 3 ... --- Script to replicate problem -- NB: The number of rows that must be added to tmp before this problem will occur is machine dependant -- 100000 rows is sufficient on one of our servers but another (faster) server doesn't show the error -- at 100000 rows but does at 1000000 rows. -- Create a table CREATE TABLE tmp ( [QM_ADD_OP] [char](6) DEFAULT '' NOT NULL, [QM_ADD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_ADD_TIME] [int] DEFAULT -1 NOT NULL, [QM_EDIT_OP] [char](6) DEFAULT '' NOT NULL, [QM_EDIT_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_EDIT_TIME] [int] DEFAULT -1 NOT NULL, [QM_LOCK_OP] [char](6) DEFAULT '' NOT NULL, [QM_QUOTE_JOB] [smallint] DEFAULT 0 NOT NULL, [QM_QUOTE_NUM] [char](12) DEFAULT '' NOT NULL, [QM_JOB_NUM] [char](12) DEFAULT '' NOT NULL, [QM_PRJ_NUM] [char](12) DEFAULT '' NOT NULL, [QM_NUMBER] [char](12) DEFAULT '' NOT NULL, [QM_REV_NUM] [char](6) DEFAULT '' NOT NULL, [QM_REV_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REV_TIME] [int] DEFAULT -1 NOT NULL, [QM_REV_OPR] [char](6) DEFAULT '' NOT NULL, [QM_STYLE_CODE] [char](4) DEFAULT '' NOT NULL, [QM_REP_JOB_NUM] [char](12) DEFAULT '' NOT NULL, [QM_REP_COLUMN] [smallint] DEFAULT 0 NOT NULL, [QM_REP_PART] [char](6) DEFAULT '' NOT NULL, [QM_REP_MODEL] [smallint] DEFAULT 0 NOT NULL, [QM_REP_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_MODEL_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_RUN_NUM] [int] DEFAULT 0 NOT NULL, [QM_SOURCE_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_SOURCE_VAR] [smallint] DEFAULT 0 NOT NULL, [QM_SOURCE_QTY] [char](12) DEFAULT '' NOT NULL, [QM_SOURCE_PART] [char](6) DEFAULT '' NOT NULL, [QM_SOURCE_MODEL] [smallint] DEFAULT 0 NOT NULL, [QM_ORIG_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_ORIG_VAR] [smallint] DEFAULT 0 NOT NULL, [QM_ORIG_QTY] [char](12) DEFAULT '' NOT NULL, [QM_ORIG_PART] [char](6) DEFAULT '' NOT NULL, [QM_COPY_JOB] [char](12) DEFAULT '' NOT NULL, [QM_COPY_COLUMN] [smallint] DEFAULT 0 NOT NULL, [QM_COPY_J_PART] [char](6) DEFAULT '' NOT NULL, [QM_COPY_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_COPY_VAR] [smallint] DEFAULT 0 NOT NULL, [QM_COPY_QTY] [char](12) DEFAULT '' NOT NULL, [QM_COPY_Q_PART] [char](6) DEFAULT '' NOT NULL, [QM_JOINT_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_QUOTE_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_JOB_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_LIVE_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_USER_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_DEL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_IS_CONVERTED] [smallint] DEFAULT 0 NOT NULL, [QM_PRINTED] [smallint] DEFAULT 0 NOT NULL, [QM_COPY_RATES] [smallint] DEFAULT 0 NOT NULL, [QM_IMPORT_UPDATE] [smallint] DEFAULT 0 NOT NULL, [QM_CRED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_CRED_TIME] [int] DEFAULT -1 NOT NULL, [QM_CRED_AMT] numeric(26,8) DEFAULT 0 NOT NULL, [QM_CRED_OP] [char](6) DEFAULT '' NOT NULL, [QM_HELD] [smallint] DEFAULT 0 NOT NULL, [QM_PROOF] [char](12) DEFAULT '' NOT NULL, [QM_DELIV_METHOD] [char](12) DEFAULT '' NOT NULL, [QM_ART_METHOD] [char](12) DEFAULT '' NOT NULL, [QM_DES_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_REC_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REC_TIME] [int] DEFAULT -1 NOT NULL, [QM_OWN_OP] [char](6) DEFAULT '' NOT NULL, [QM_RESP_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_RESP_TIME] [int] DEFAULT -1 NOT NULL, [QM_RESP_OP] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_1] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_2] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_3] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_4] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_5] [char](6) DEFAULT '' NOT NULL, [QM_RECONTACT] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REQ_FLAG] [smallint] DEFAULT 0 NOT NULL, [QM_ORIG_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_ORIG_TIME] [int] DEFAULT -1 NOT NULL, [QM_PREF_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_PREF_TIME] [int] DEFAULT -1 NOT NULL, [QM_LATE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_LATE_TIME] [int] DEFAULT -1 NOT NULL, [QM_TITLE] [char](72) DEFAULT '' NOT NULL, [QM_DELIV_CODE] [char](12) DEFAULT '' NOT NULL, [QM_CLT_SPEC] [char](12) DEFAULT '' NOT NULL, [QM_TAX_REF] [char](22) DEFAULT '' NOT NULL, [QM_CONTACT] [char](36) DEFAULT '' NOT NULL, [QM_PHONE] [char](22) DEFAULT '' NOT NULL, [QM_FAX] [char](22) DEFAULT '' NOT NULL, [QM_ORDER] [char](20) DEFAULT '' NOT NULL, [QM_ORDER_CFM] [smallint] DEFAULT 0 NOT NULL, [QM_ORDER_REL] [char](6) DEFAULT '' NOT NULL, [QM_REP] [char](12) DEFAULT '' NOT NULL, [QM_REP_1] [char](12) DEFAULT '' NOT NULL, [QM_REP_2] [char](12) DEFAULT '' NOT NULL, [QM_REP_3] [char](12) DEFAULT '' NOT NULL, [QM_REP_4] [char](12) DEFAULT '' NOT NULL, [QM_REP_5] [char](12) DEFAULT '' NOT NULL, [QM_COORDINATOR] [char](12) DEFAULT '' NOT NULL, [QM_PRIORITY] [smallint] DEFAULT 0 NOT NULL, [QM_TYPE_CODE] [char](12) DEFAULT '' NOT NULL, [QM_GRADE] [smallint] DEFAULT 0 NOT NULL, [QM_FIN_SIZE_CODE] [char](12) DEFAULT '' NOT NULL, [QM_FIN_WID] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_LEN] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_DEP] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_GUSS] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_GSM] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_UNIT] [char](12) DEFAULT '' NOT NULL, [QM_ORIENT] [smallint] DEFAULT 0 NOT NULL, [QM_PROD_CODE] [char](22) DEFAULT '' NOT NULL, [QM_FIN_GOOD] [char](22) DEFAULT '' NOT NULL, [QM_CUST_CODE] [char](12) DEFAULT '' NOT NULL, [QM_CUST_CODE_1] [char](12) DEFAULT '' NOT NULL, [QM_CUST_CODE_2] [char](12) DEFAULT '' NOT NULL, [QM_CUST_PROS] [smallint] DEFAULT 0 NOT NULL, [QM_REQD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REQD_TIME] [int] DEFAULT -1 NOT NULL, [QM_FOLIO] [char](12) DEFAULT '' NOT NULL, [QM_FOLIO_1] [char](12) DEFAULT '' NOT NULL, [QM_FOLIO_2] [char](12) DEFAULT '' NOT NULL, [QM_PACK_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_USAGE] numeric(26,8) DEFAULT 0 NOT NULL, [QM_REORDER] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_EACH_WGT] numeric(26,8) DEFAULT 0 NOT NULL, [QM_WGT_UNIT] [char](12) DEFAULT '' NOT NULL, [QM_RFQ_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_RFQ_TIME] [int] DEFAULT -1 NOT NULL, [QM_RFQ_OPR] [char](6) DEFAULT '' NOT NULL, [QM_SALES_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_SALES_SRC] [char](12) DEFAULT '' NOT NULL, [QM_SALES_RSN] [char](12) DEFAULT '' NOT NULL, [QM_PROFILE] [char](12) DEFAULT '' NOT NULL, [QM_JOB_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_PREV_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_JOB_UNIT] [char](12) DEFAULT '' NOT NULL, [QM_PO_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_PO_TIME] [int] DEFAULT -1 NOT NULL, [QM_PO_OP] [char](6) DEFAULT '' NOT NULL, [QM_DLY_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_DLY_TIME] [int] DEFAULT -1 NOT NULL, [QM_QTY_DESP] numeric(26,8) DEFAULT 0 NOT NULL, [QM_TOTAL_DLY] [int] DEFAULT 0 NOT NULL, [QM_SCHED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_SCHED_TIME] [int] DEFAULT -1 NOT NULL, [QM_CLOSE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_CLOSE_TIME] [int] DEFAULT -1 NOT NULL, [QM_INV_NUM] [char](12) DEFAULT '' NOT NULL, [QM_PACK_NUM] [char](12) DEFAULT '' NOT NULL, [QM_DOWN_LOAD] [smallint] DEFAULT 0 NOT NULL, [QM_TRACK_CODE] [char](4) DEFAULT '' NOT NULL, [QM_TAX_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_TAX_CODE] [char](6) DEFAULT '' NOT NULL, [QM_CURR] [char](6) DEFAULT '' NOT NULL, [QM_EXCH_RATE] numeric(18,8) DEFAULT 0 NOT NULL, [QM_UNIT_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_UNIT_FLAG] [smallint] DEFAULT 0 NOT NULL, [QM_RUNON_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_SPEC_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_CHARGEABLE] [smallint] DEFAULT 0 NOT NULL, [QM_NC_REASON] [char](22) DEFAULT '' NOT NULL, [QM_CUST_MKUP] numeric(18,8) DEFAULT 0 NOT NULL, [QM_JOB_MKUP] numeric(18,8) DEFAULT 0 NOT NULL, [QM_BROKERAGE] numeric(18,8) DEFAULT 0 NOT NULL, [QM_CUST_DISC] numeric(18,8) DEFAULT 0 NOT NULL, [QM_INVOKED_BTNS] [int] DEFAULT 0 NOT NULL, [QM_IMPORTED] [smallint] DEFAULT 0 NOT NULL, [QM_IMPORT_RECALC] [smallint] DEFAULT 0 NOT NULL, [QM_IMPORT_CONVERT] [smallint] DEFAULT 0 NOT NULL, [QM_BRANCH] [char](6) DEFAULT '' NOT NULL, [QM_CODE] [char](36) DEFAULT '' NOT NULL, [QM_TEMPLATE] [smallint] DEFAULT 0 NOT NULL, [QM_REPEAT_PERIOD] [int] DEFAULT 0 NOT NULL, [QM_REOPEN_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_CAT_OPTION] [char](16) DEFAULT '' NOT NULL, [QM_UNIT_ID] [char](10) DEFAULT '' NOT NULL, [QM_PROD_BRANCH] [char](6) DEFAULT '' NOT NULL, [QM_UID] [int] DEFAULT 0 NOT NULL, [QM_AVAIL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_AVAIL_TIME] [int] DEFAULT -1 NOT NULL ) ON [PRIMARY] GO -- Create an index on the table CREATE unique INDEX [QM_NUMBER_ORDER] ON tmp([QM_QUOTE_JOB], [QM_NUMBER]) ON [PRIMARY] GO -- Populate the table declare @Counter as int SET NOCOUNT ON set @Counter = 1 while @Counter < 100000 begin insert into tmp (QM_ADD_TIME, QM_NUMBER) values (1,@Counter); set @Counter = @Counter + 1 end GO -- Update QM_UID to a sequential value declare @NextID int; set @NextID = 1; update tmp set QM_UID=@NextID, @NextID = @NextID + 1; -- Find rows with a duplicate QM_UID (there should be no duplicate) select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID --drop table tmp -- output from select @@VERSION -- Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
View Replies !
Returning Variable From Dynamic Query
Hello All! I need to do the following in a trigger (using SQL7). I need to check the values of columns (dynamic list of columns) to check previous and current values (based on an update/insert). I can create temp tables in a trigger in SQL2K (but unfortunatley I have to use SQL7 and it doesn't support it). I am trying to do something like: THIS WORKS: declare @test select @test = count(*) from authors select @test THIS DOES NOT WORK: declare @test select @test = exec('select count(*) from authors') select @test How can I accomplish this in a trigger (the query needs to be built dynamically). Thanks! Sunish mailto::sunisha@bellatlantic.net
View Replies !
Stored Parameter Not Returning Variable
/******** Doesn't matter, solved the problem. It was finding a null value in @DBNAME *********/ If anyone could help me with this it would be very appreciated as I pulling my hair out. This stored procedure is to check each 'site' for more than one admin. If there is more than one admin then it is to append a string variable, @SITES_LIST, with the cursor's current variables. Code: alter procedure [dbo].[LOG_MOD_REPORT_ZERO_ADMINS] --@LIST VARCHAR(7000) OUTPUT AS BEGIN TRY BEGIN TRANSACTION DECLARE @SITES_LIST VARCHAR (7000) --HOLD THE SITE DETAILS FOR THE TEXT FILE SELECT @SITES_LIST = 'SITE_ID,PRACTICE_NAME,DBNAME' + CHAR(10) +CHAR(13) --HEADINGS FOR REPORT DECLARE C CURSOR FOR SELECT ID, PRACTICE_NAME, DBNAME FROM SITE_DETAILS OPEN C DECLARE @SITE_ID int DECLARE @PRACTICE_NAME VARCHAR (50) DECLARE @DBNAME VARCHAR (64) FETCH NEXT FROM C INTO @SITE_ID, @PRACTICE_NAME, @DBNAME WHILE @@FETCH_STATUS = 0 BEGIN --print @SITE_ID --PRINT @PRACTICE_NAME if (SELECT COUNT(ID) FROM PCS_ROLE WHERE PCS_ROLE_NAME IN ('System Administrator', 'Caldicott Lead') AND MAIN_SITE_ID = @SITE_ID) >= 0 Begin -- PRINT 'Entered if statement' SET @SITES_LIST = (@SITES_LIST + convert(varchar,@SITE_ID) + ',' + @PRACTICE_NAME + ',' + @DBNAME + CHAR(10) +CHAR(13)) end Print @SITES_LIST FETCH NEXT FROM C INTO @SITE_ID, @PRACTICE_NAME, @DBNAME END CLOSE C DEALLOCATE C COMMIT TRANSACTION END TRY BEGIN CATCH CLOSE C DEALLOCATE C IF @@TRANCOUNT > 0 ROLLBACK SELECT ERROR_MESSAGE() as ERRORMESSAGE; END CATCH Using print statements I know the cursor is getting the site_details with no problems and entering the if statement, and the select statement does work, so I am guessing it is not setting the variable @SITE_LIST properly. I can append to site list if I just add a string to it, and it prints successfully. However, when I try to perform @SITE_LIST = @SITE_LIST + variables the @SITE_LIST variable is set to '' first, so all i get is the appended variables as print output. Anyone know how to append to a variable like this or concatenate it without resetting itself?! Thanks for any future help, Mike
View Replies !
Fatal Exception Returning Table Variable From UDF
I am repeatedly getting Fatal Exceptions when returning a table variable from a user-defined function. The function code is as shown below. Is anyone else having this problem and know how to fix it? Thanks, BCS CREATE function fn_Split (@Text varchar(8000),@Delim char(1)=',') returns @array table (idx smallint NOT NULL,value varchar(8000) NOT NULL) as begin declare @idx smallint, @value varchar(8000) declare @p smallint, @q smallint declare @textlen smallint if @delim = '{tab}' begin set @delim=char(9) end set @idx=0 set @p=0 set @text=ltrim(@text) set @textlen = len(@text) while @p < @textlen begin set @idx=@idx+1 set @q=@p+1 set @p = charindex(@delim,@text,@q) if @p=0 set @p=@textlen+1 set @value=ltrim(substring(@text,@q,@p-@q)) insert into @array (idx,value) values(@idx,@value) end return end
View Replies !
Returning VARCHAR(MAX) Result Set To A String Variable
Hi, I have a stored procedure that returns a value of data type VARCHAR(MAX) and inserts this into a package variable of type System.Object called XMLA_PartitionScript. XMLA_PartitionScript contains XMLA code to create a partition in an AS 2005 database. I want to pass the variable to an Analysis Services Execute DDL Task object as the Source in the task, but before I do this I have to convert it back to a string. Has anyone any ideas on how to do this, as the size of XMLA_PartitionScript is too large to be held in a string (hence I had to pass the code into a System.Object variable in the first place). I found the following blog that suggested this approach, but I can't manage to convert the variable from an Object datatype to a string datatype: http://blogs.conchango.com/stevewright/archive/2006/01/20/2686.aspx Any help appreciated... PaulOR
View Replies !
Error Returning Variable From Dynamic Stored Procedure
I am trying to return a variable from a dynamic SP - declare @v_outvarchar(400) ... execute ("select @v_out=" + @v_column + " from " + @v_table_name + " where " + @v_key_sql) I get the following error: Server: Msg 137, Level 15, State 1, Line 1 Must declare the variable '@v_out'. Any idea how to fix this? Carl
View Replies !
Getting SqlDbType Not Declared
How do I dim SqlDbType in my code?Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TrainUserConnectionString").ConnectionString) Dim cmd As New Data.SqlClient.SqlCommandWith cmd .Connection = conn .CommandType = Data.CommandType.StoredProcedure .CommandText = "UpdateTopicscmd.Parameters.Add("@classificationID", SqlDBType.Int)cmd.Parameters.Add("@TitleID", SqlDBType.Int) conn.Open() For Each item As ListItem In CheckBoxList1.Items If item.Selected Thencmd.Parameters("@classificationID").Value = item.Valuecmd.Parameters("@TitleID").Value = DropDownList1.SelectedValue cmd.ExecuteNonQuery() End If Next conn.Close() End WithEnd Sub End Class
View Replies !
SqlStatementSourceType Is Not Declared
I have simple SSIS package with only ScriptTask. Script was copied from MSDN: Code Snippet Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask ... Public Sub Main() Dim pkg As Package = New Package() Dim exec1 As Executable = pkg.Executables.Add("STOCK:SQLTask") Dim th As TaskHost th = CType(exec1, TaskHost) Dim myVar As Variable = pkg.Variables.Add("myVar", False, "User", 100) th.Properties("SqlStatementSourceType").SetValue(th, SqlStatementSourceType.Variable) ... Last line shows error - 'SqlStatementSourceType' is not declared. Well, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask is imported so SqlStatementSourceType should be visible. I don't see microsoft.sqlserver.sqltask.dll in task's references and can't add it because Add Reference doesn't show this DLL. Could you please forward me in right direction? Thank you, Alexander
View Replies !
Type 'adCmdStoredProc' Is Not Declared
I am trying to set up a "cmd.CommandType = adCmdStoredProc" but Ireceive the error "Type 'adCmdStoredProc' is not declared". What do Ineed to do to declare it? I am using MSDE with SQLDataAdapter. I amtrying to exceute a stored procedure from within my VB .NET 2003 code.Thanks.JH
View Replies !
Using Declared Variables In SQL INSERT Statement.
I am new to scripting in general and I've run into an issue when attempting to write a VB variable to a database table in SQL Express. I am trying to record the value of the variable to the db, but it does not appear that the value is being passed to SQL. If I hard code the values in the SQL statement it works fine. Can someone explain what I'm doing wrong accomplish this? My code is below. Thanks in advance. file.aspx <asp:SqlDataSource ID="SqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>" SelectCommand="SELECT * FROM [Table]" InsertCommand="INSERT INTO [Table] (field1, field2) VALUES (& variable1 &, & variable2 &);" > </asp:SqlDataSource> file.aspx.vb Protected Sub Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button.Click Dim variable1 As String = FileUpload.FileName Dim variable2 As String = Date.Now Dim path As String = Server.MapPath("~/directory/) If FileUpload.HasFile = True Then Try SqlDataSource.Insert() FileUpload.PostedFile.SaveAs(path & _ FileUpload.FileName) End Try End If End Sub
View Replies !
Script Component Name Ouput0Buffer Is Not Declared???
Doing a simple test with a script component in a DataFlow to transform some data from a flat file. I have new columns under the default Ouput 0 .. however in my code when I try this, I get the above error. Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Output0Buffer.AddRow() End Sub There will of course be a lot more code in the Script Component, but not clear on why I can't reference it.
View Replies !
Stored Procedure Using A Declared Cursor
I need to write a stored procedure using T-SQL to declare a cursor for containing id(staff_no), names and specialism of all doctors that have specialism, The contents of the cursor then are to be displayed using a loop and print statement to give a formatted display of the output of each record within the cursor. The doctors table has the following columns with specialism allowing NULL values doctor ( staff_no CHAR(3), doctor_name CHAR(12), position CHAR(15), specialism CHAR(15), PRIMARY KEY(staff_no) ) Any help would be greatly appreciated.
View Replies !
Go And Goto In One Sql Script Gives Error Label Not Declared
Hi,I have a problem:I am writing an update script for a database and want to check for theversion and Goto the wright update script.So I read the version from a table and if it match I want to "GotoVersionxxx"Where Versionxxx: is set in the script with the right update script.Whenever I have some script which need Go commands I get error in theoutput thatA GOTO statement references the label 'Versionxxx' but the label hasnot been declared.But the label is set in the script by 'Versionxxx:'Is there a way I can solve this easily?Thanks in advance
View Replies !
Can Varbinary And Varchar Lengths Be Declared Dynamically?
I just learned that bit masking a varbinary column can increase it's length in bytes unnecessarily. For example, I ran the following... declare @v1 varbinary(max) set @v1 = 0x0100AB select len(@v1) set @v1 = @v1 | (len(@v1) - 2) * 256 select master.dbo.fn_varbintohexstr(@v1) select len(@v1) ...and get 3 0x00000000000101ab 8 This messes up a plan I had for varbinary column use. So I quickly tried the following to look for strategies to deal with this unwanted growth...thought being that while I'm passed a varbinary(max), operating on a varbinary that matches its passed length would avoid the unwanted growth after bit masking.... declare @i int set @i = 5 declare @v2 varbinary(@i) ...but got errors. So I suppose I can use a combo of the len, substring etc functions to correct the situation after bit masking but would like to know if the more elegant approach of dynamically sizing a varbinary is possible in t-sql, or if perhaps there is a way to prevent the unwanted growth during bit masking.
View Replies !
How Do I Asign A Textbox In A Rdlc Report A Declared Value?
Hi all.. I developed a local report to be viewed using the "Report Viewer" control. The report is attached to an object data source. All works perfectly, now I want to display a declared value (from the form containing the report viewer) in a textbox. Like: Dim NofDays as string Me.ReportViewer1.LocalReport.textbox6.text = NofDays I ve tried a lot of options like using the report paramaters but I cannot get it to work. Does aneyone have a clue? Thankzzzzzz Juststar
View Replies !
Error: Table Schema Changed After The Cursor Is Declared
Hi, I have a package which loads data from one sql server table to another. I am loading 15million records in that. Earlier I tested that package with smaller data (less than a million) and it worked fine. So, I put it in production to load that 15 million records. But strangely after loading over 1.5million records, the job aborted with error at destination. The log says 'the table schema has changed after the cursor is declared'. But there is no change made in both destination as well as source. In my package I am using a 'OLEDB Source' to read data from a SQL Server table, using 'Script component' making some changes and loading data to a sql server table using 'OLE DB Destination'. Both source and destination are in same server, but under different schema. Do you have any idea about the problem? Thanks.
View Replies !
Scope Of Objects Declared In A Custom Code Segment
Hi, I am trying to get around totaling problens with RS. I want to aggregate totals from a table group and display it in the table footer. After a lot of trial and error it seems the custom code is instanced based on report scope. For instance I have the following custom code; public shared runtotal as double=0 Function GT( ByVal fieldval As Double) As Double runtotal=runtotal+fieldval GT=runtotal End Function I call the function from a group row with the expression =Code.GT(sum(Fields!Amount)) I then call the function from the table footer with the expession =Code.GT(0) The report group with my dataset renders two rows of values (10,000 and 18,000). The function called on the first instance of the group shows 10,000. That is correct The function called on the second instance of the group shows 28,000. That is correct (i.e. 10,000+18,000) The function called on the footer shows zero. That is incorrect. It should be 28,000. (i.e. 10,000+18,000+0) As much as I can figure there is two instances of the variable runtotal. One for the table group and one for the table footer. Anyone, please help if you have a solution or insight. Thanks.
View Replies !
Trouble Porting A Trivially Simple Function - With Declared Variables
Here is one such function:CREATE FUNCTION my_max_market_date () RETURNS datetimeBEGINDECLARE @mmmd AS datetime;SELECT max(h_market_date) INTO @mmmd FROM holdings_tmp;RETURN @mmmd;ENDOne change I had to make, relative to what I had working in MySQL, wasto insert 'AS' between my variable and its type. Without 'AS', MS SQLinsisted in telling me that datetime is not valid for a cursor; and Iam not using a cursor here. The purpose of this function is tosimplify a number of SQL statements that depend on obtaining the mostrecent datetime value in column h_market_date in the holdings_tmptable.The present problem is that MS SQL doesn't seem to want to allow me toplace that value in my variable '@mmmd'. I could do this easily inMySQL. Why is MS SQL giving me grief over something that should be sosimple. I have not yet found anything in the documentation for SELECTthat could explain what's wrong here. :-(Any ideas?ThanksTed
View Replies !
A SqlDataReader Is Returning An Int, When It Should Be Returning A Tinyint
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this: SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89 The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1. Why?
View Replies !
SqlDataSource, DataView, CType Function && Page_Load-Compilation ErrorBC30451: Name 'SqlDataSource3' Is Not Declared.
Hi all, In my VWD 2005 Express, I created a website "AverageTCE" that had Default.aspx, Default.aspx.vb and App_Code (see the attached code) for configurating a direct SqlDataSource connection to the dbo.Table "LabData" of my SQL Server 2005 Express "SQLEXPRESS" via SqlDataSource, DataView, CType Function and the Page_Load procedure. I executed the website "AverageTCE" and I got Compilation ErrorBC30451: Name 'SqlDataSource3' is not declared: Server Error in '/AverageTCE' Application. Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC30451: Name 'SqlDataSource3' is not declared.Source Error: Line 8: <DataObjectMethod(DataObjectMethodType.Select)> _ Line 9: Public Shared Function SelectedConcentration() As ConcDB Line 10: Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView) Line 11: dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'" Line 12: Source File: C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005WebSitesAverageTCEApp_CodeConcDB.vb Line: 10 //////////--Default.aspx--////////////////////////// <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>SQL DataSource</title> </head> <body> <form id="form1" runat="server"> <div> Average TCE<br /> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2" DataTextField="SampleID" DataValueField="SampleID"> </asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString2 %>" SelectCommand="SELECT [SampleID] FROM [LabData]"></asp:SqlDataSource> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SampleID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="SampleID" HeaderText="SampleID" ReadOnly="True" SortExpression="SampleID" /> <asp:BoundField DataField="SampleName" HeaderText="SampleName" SortExpression="SampleName" /> <asp:BoundField DataField="AnalyteName" HeaderText="AnalyteName" SortExpression="AnalyteName" /> <asp:BoundField DataField="Concentration" HeaderText="Concentration" SortExpression="Concentration" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="ddlLabData" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString %>" SelectCommand="SELECT * FROM [LabData] WHERE ([SampleID] = @SampleID)"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" DefaultValue="3" Name="SampleID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <br /> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString3 %>" SelectCommand="SELECT * FROM [LabData]"></asp:SqlDataSource> <br /> <br /> LabData-Analyte: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <br /> LabData-Conc: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> <br /> Average values: <asp:Label ID="Label1" runat="server" Text="lblAverageValue"></asp:Label><br /> <br /> <br /> <br /> </div> </form> </body> </html> ///////////--Default.aspx.vb--//////////////////////////////// Partial Class _Default Inherits System.Web.UI.Page End Class ////////////////--App_Code/ConcDB.vb--////////////////////// Imports Microsoft.VisualBasic Imports System.ComponentModel Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes <DataObject(True)> Public Class ConcDB <DataObjectMethod(DataObjectMethodType.Select)> _ Public Shared Function SelectedConcentration() As ConcDB Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView) dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'" Dim dvRow As DataRowView = dvConcDB(0) Dim ConcDB As New ConcDB ConcDB.SelectedConcentration = CDec(0)("Concentration") Return ConcDB End Function Call AverageValue (Conc1) Public Shared Function AverageValue(ByVal Conc1 As Decimal) Dim AverageConc As Decimal AverageConc = (Conc1 + 22.0) / 2 Return AverageConc End Function End Class ************************************************************** I have 2 questions to ask: 1) How can I fix this Compilation Error BC30451: Name 'SqlDataSource3' is not declared? 2) I just read MSDN Visual Studio 2005 Technical Article "Data Access in ASP.NET 2.0" and I saw the following thing: Types of Data Sources: SqlDataSouirce: The configuration of a SqlDataSoure is more complex then that of the AccessDataSource, and is intended for enterprise applications that require the features provided by a true database management system (DBMS). I am using the website application in VWD 2005 Express to do the task of extracting data values from the Tables of SQL Server 2005 Express via .NET Framwork, ASP.NET 2.0 and VB 2005 programming. Can VWD 2005 Express be configured to SQL Server 2005 Express (SQLEXPESS) for the SqlDataSource connection and do the data-extraction task via DataView, CType Function and the Page-Load procedure? Please help, respond and answer the above-mentiopned 2 questions. Many Thanks, Scott Chang
View Replies !
Common Table Expression (CTE) T-SQL Errors:Invalid Object Name 'ProductItemPrices'.The Variablename '@TopEmp' Has Been Declared
Hi all, I copied the following code from a tutorial book and executed it in my SQL Server Management Studio Express (SSMSE): --CET.sql-- USE AdventureWorks GO --Use column value from a table pointed at by a foreign key WITH ProductItemPrices AS ( SELECT ProductID, AVG(LineTotal) 'AvgPrice' FROM Sales.SalesOrderDetail GROUP BY ProductID ) SELECT p.Name, pp.AvgPrice FROM ProductItemPrices pp JOIN Production.Product p ON pp.ProductID = p.ProductID ORDER BY p.Name SELECT * FROM ProductItemPrices GO --Display rows from SalesOrderDetail table with a LineTotal --value greater than the average for all Linetotal values with --the same ProductID value WITH ProductItemPrices AS ( SELECT ProductID, AVG(LineTotal) 'AvgPrice' FROM Sales.SalesOrderDetail GROUP BY ProductID ) SELECT TOP 29 sd.SalesOrderID, sd.ProductID, sd.LineTotal, pp.AvgPrice FROM Sales.SalesOrderDetail sd JOIN ProductItemPrices pp ON pp.ProductID = sd.ProductID WHERE sd.LineTotal > pp.AvgPrice ORDER BY sd.SalesOrderID, sd.ProductID --Return EmployeeID along with first and last name of employees --not reporting to any other employee SELECT e.EmployeeID, c.FirstName, c.LastName JOIN HumanResources.Employee e ON e.ContactID = c.ContactID JOIN HumanResources.EmployeeDepartmentHistory d ON d.EmployeeID = e.EmployeeID JOIN HumanResources.Department dn ON dn.DepartmentID = d.DepartmentID) JOIN Empcte a ON e.ManagerID = a.empid) --Order and display result set from CTE SELECT * Hi all, I copied the following T-SQL code from a tutorial book and executed it in my SQL Server Management Studio Express (SSMSE): --CTE.sql-- USE AdventureWorks GO --Use column value from a table pointed at by a foreign key WITH ProductItemPrices AS ( SELECT ProductID, AVG(LineTotal) 'AvgPrice' FROM Sales.SalesOrderDetail GROUP BY ProductID ) SELECT p.Name, pp.AvgPrice FROM ProductItemPrices pp JOIN Production.Product p ON pp.ProductID = p.ProductID ORDER BY p.Name SELECT * FROM ProductItemPrices GO --Display rows from SalesOrderDetail table with a LineTotal --value greater than the average for all Linetotal values with --the same ProductID value WITH ProductItemPrices AS ( SELECT ProductID, AVG(LineTotal) 'AvgPrice' FROM Sales.SalesOrderDetail GROUP BY ProductID ) SELECT TOP 29 sd.SalesOrderID, sd.ProductID, sd.LineTotal, pp.AvgPrice FROM Sales.SalesOrderDetail sd JOIN ProductItemPrices pp ON pp.ProductID = sd.ProductID WHERE sd.LineTotal > pp.AvgPrice ORDER BY sd.SalesOrderID, sd.ProductID --Return EmployeeID along with first and last name of employees --not reporting to any other employee SELECT e.EmployeeID, c.FirstName, c.LastName FROM HumanResources.Employee e JOIN Person.Contact c ON e.ContactID = c.ContactID where ManagerID IS NULL --Specify top level EmployeeID for direct reports DECLARE @TopEmp as int SET @TopEmp = 109; --Names and departments for direct reports to --EmployeeID = @TopEmp; calculate employee name WITH Empcte(empid, empname, mgrid, dName, lvl) AS ( -- Anchor row SELECT e.EmployeeID, REPLACE(c.FirstName + ' ' + ISNULL(c.MiddleName, '') + ' ' + c.LastName, ' ', ' ') 'Employee name', e.ManagerID, dn.Name, 0 FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID JOIN HumanResources.EmployeeDepartmentHistory d ON d.EmployeeID = e.EmployeeID JOIN HumanResources.Department dn ON dn.DepartmentID = d.DepartmentID WHERE e.EmployeeID = @TopEmp UNION ALL -- Recursive rows SELECT e.EmployeeID, REPLACE(c.FirstName + ' ' + ISNULL(c.MiddleName, '') + ' ' + c.LastName, ' ', ' ') 'Employee name', e.ManagerID, dn.Name, a.lvl+1 FROM (Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID JOIN HumanResources.EmployeeDepartmentHistory d ON d.EmployeeID = e.EmployeeID JOIN HumanResources.Department dn ON dn.DepartmentID = d.DepartmentID) JOIN Empcte a ON e.ManagerID = a.empid) --Order and display result set from CTE SELECT * FROM Empcte WHERE lvl <= 1 ORDER BY lvl, mgrid, empid --Alternate statement using MAXRECURSION; --must position immediately after Empcte to work SELECT * FROM Empcte OPTION (MAXRECURSION 1) ==================================== This is Part 1 (due to the length of input > 50000 characters). Scott Chang
View Replies !
Stored Procedure Returning 2 Result Sets - How Do I Stop The Procedure From Returning The First?
I hvae a stored procedure that has this at the end of it: BEGIN EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, '' END SET NOCOUNT OFF SELECT Something FROM Something Joins….. Where Something = Something now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure. What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
View Replies !
Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker I get the following error can you please explain this problem to me so I can over come the issue. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153 Cursor is not open. here is the stored procedure: Alter PROCEDURE [dbo].[sp_MSforeachsp] @command1 nvarchar(2000) , @replacechar nchar(1) = N'?' , @command2 nvarchar(2000) = null , @command3 nvarchar(2000) = null , @whereand nvarchar(2000) = null , @precommand nvarchar(2000) = null , @postcommand nvarchar(2000) = null AS /* This procedure belongs in the "master" database so it is acessible to all databases */ /* This proc returns one or more rows for each stored procedure */ /* @precommand and @postcommand may be used to force a single result set via a temp table. */ declare @retval int if (@precommand is not null) EXECUTE(@precommand) /* Create the select */ EXECUTE(N'declare hCForEachTable cursor global for SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''PROCEDURE'' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 ' + @whereand) select @retval = @@error if (@retval = 0) EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0 if (@retval = 0 and @postcommand is not null) EXECUTE(@postcommand) RETURN @retval GO example useage: EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]" GO
View Replies !
SSIS Script Task Alters Package Variable, But Variable Does Not Change.
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value. I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
View Replies !
SSIS Error Reading XML Loaded Into Variable With XML Source Using XML File From Variable.
Hi I am getting the following error when trying to extract data using XML source " Error: 0xC02090D0 at Data Flow Task - Load XML data to database, XML Source - Load XML data from variable [15893]: The component "XML Source - Load XML data from variable" (15893) was unable to read the XML data." What I have done is read XML data from file and stripped out DTD contents using XSLT transformation in an XML task. The XML file is loaded into a string variable called XMLProduct. Next I have a XML task to validate the variable contents against the XSD, which works. Then I am trying to load the data using XML Source within a Data Flow task, which is when the error occurs. If before I enter the Data Flow and use a script task to read the content of XMLProduct variable and save that to a file then point XML Source task to the file it works ok. I need to iterate through a whole bunch of XML files so reading it into a variable would be the preferred option if it worked of course. Any help would be hugely appreciated. Edit --- I have saved the output from the XSLT transformation XML Task to file and not a variable then read that in using the XML source task and it worked. It would be nice to get the variable method working though as it would save having to create another file. It is a work around at the moment though.
View Replies !
SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes. Here are the task steps. [Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step. [Execute SQL Task] - Log an entry to a table indicating that the import has started. [Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works. [Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile. If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS. If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post. Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction. CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate] /* The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate. If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset. Otherwise it returns a FALSE value in the IsNewFile column. Example: exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0 */ @ProcessName varchar(50) , @FileCreateDate datetime , @IsNewFile bit OUTPUT AS SET NOCOUNT ON --DECLARE @IsNewFile bit DECLARE @CreateDateInTable datetime SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName) BEGIN -- The process exists in tbl_ImportFileCreateDate. Compare the create dates. IF (@FileCreateDate > @CreateDateInTable) BEGIN -- This is a newer file date. Update the table and set @IsNewFile to TRUE. UPDATE tbl_ImportFileCreateDate SET FileCreateDate = @FileCreateDate WHERE ProcessName = @ProcessName SET @IsNewFile = 1 END ELSE BEGIN -- The file date is the same or older. SET @IsNewFile = 0 END END ELSE BEGIN -- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE. INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate) VALUES (@ProcessName, @FileCreateDate) SET @IsNewFile = 1 END SELECT @IsNewFile The relevant Global Variables in the package are defined as follows: Name : Scope : Date Type : Value FileCreateDate : (Package Name) : DateType : 1/1/2000 IsNewFile : (Package Name) : Boolean : False Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings. General Name = Compare Last File Create Date Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate. TimeOut = 0 CodePage = 1252 ResultSet = None ConnectionType = OLE DB Connection = MyServerDataBase SQLSourceType = Direct input IsQueryStoredProcedure = False BypassPrepare = True I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate. SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output Parameter Mapping Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1 Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1 Result Set is empty. Expressions is empty. When I run this in debug mode with this SQL statement ... exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the following error message appears. SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning. Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Compare Last File Create Date Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "MyPackage.dtsx" finished: Failure. When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure. When I run this in debug mode with this SQL statement ... exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message. SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning. Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Compare Last File Create Date Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "MyPackage.dtsx" finished: Failure. The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives? The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing. Thanks for your help.
View Replies !
Compare The Value Of A Variable With Previous Variable From A Function ,reset The Counter When Val Changes
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer. Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2) It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2. Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks! Here is the code of the function in the book: /* ** fn_FindReports.sql ** ** This multi-statement table-valued user-defined ** function takes an EmplyeeID number as its parameter ** and provides information about all employees who ** report to that person. */ USE ClassNorthwind GO /* ** As a multi-statement table-valued user-defined ** function it starts with the function name, ** input parameter definition and defines the output ** table. */ CREATE FUNCTION fn_FindReports (@InEmployeeID char(5)) RETURNS @reports TABLE (EmployeeID char(5) PRIMARY KEY, Name nvarchar(40) NOT NULL, Title nvarchar(30), MgrEmployeeID int, processed tinyint default 0) -- Returns a result set that lists all the employees who -- report to a given employee directly or indirectly AS BEGIN DECLARE @RowsAdded int -- Initialize @reports with direct reports of the given employee INSERT @reports SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0 FROM EMPLOYEES WHERE ReportsTo = @InEmployeeID SET @RowsAdded = @@rowcount -- While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN -- Mark all employee records whose direct reports are going to be -- found in this iteration UPDATE @reports SET processed = 1 WHERE processed = 0 -- Insert employees who report to employees marked 1 INSERT @reports SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0 FROM employees e, @reports r WHERE e.ReportsTo = r.EmployeeID AND r.processed = 1 SET @RowsAdded = @@rowcount -- Mark all employee records whose direct reports have been -- found in this iteration UPDATE @reports SET processed = 2 WHERE processed = 1 END RETURN -- Provides the value of @reports as the result END GO
View Replies !
Debug Error - Object Variable Or With Block Variable Not Set -
I keep getting this debug error, see my code below, I have gone thru it time and time agian and do not see where the problem is. I have checked and have no NULL values that I'm trying to write back. ~~~~~~~~~~~ Error: System.NullReferenceException was unhandled by user code Message="Object variable or With block variable not set." Source="Microsoft.VisualBasic" ~~~~~~~~~~~~ My Code Dim DBConn As SqlConnection Dim DBAdd As New SqlCommand Dim strConnect As String = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString DBConn = New SqlConnection(strConnect) DBAdd.CommandText = "INSERT INTO D12_MIS (" _ & "CSJ, EST_DATE, RECORD_LOCK_FLAG, EST_CREATE_BY_NAME, EST_REVIEW_BY_NAME, m2_1, m2_2_date, m2_3_date, m2_4_date, m2_5, m3_1a, m3_1b, m3_2a, m3_2b, m3_3a, m3_3b" _ & ") values (" _ & "'" & Replace(vbCSJ.Text, "'", "''") _ & "', " _ & "'" & Replace(tmp1Date, "'", "''") _ & "', " _ & "'" & Replace(tmpRecordLock, "'", "''") _ & "', " _ & "'" & Replace(CheckedCreator, "'", "''") _ & "', " _ & "'" & Replace(CheckedReviewer, "'", "''") _ & "', " _ & "'" & Replace(vb2_1, "'", "''") _ & "', " _ & "'" & Replace(tmp2Date, "'", "''") _ & "', " _ & "'" & Replace(tmp3Date, "'", "''") _ & "', " _ & "'" & Replace(tmp4Date, "'", "''") _ & "', " _ & "'" & Replace(vb2_5, "'", "''") _ & "', " _ & "'" & Replace(vb3_1a, "'", "''") _ & "', " _ & "'" & Replace(vb3_1b, "'", "''") _ & "', " _ & "'" & Replace(vb3_2a, "'", "''") _ & "', " _ & "'" & Replace(vb3_2b, "'", "''") _ & "', " _ & "'" & Replace(vb3_3a, "'", "''") _ & "', " _ & "'" & Replace(vb3_3b, "'", "''") _ & "')" DBAdd.Connection = DBConn DBAdd.Connection.Open() DBAdd.ExecuteNonQuery() DBAdd.Connection.Close()
View Replies !
How To Match The Date Variable And Character Variable
Hello, I run the DTS to copy data from Progress to SQL Server. How can match/convert the date variables to check. The field p-date as format 'mm-dd-year' . It has the value of 10/09/2001. The field s-date as varchar format. It has the value 2001-10-09. How can use the where condition ( Select ...... WHERE p-date = s-date.) Thanks
View Replies !
Variable Indirection: Choosing Variable At Runtime
Hello! I'm using SQL Server 2000. I have a variable which contains the name of another variable scoped in my stored procedure. I need to get the value of that other variable, namely: DECLARE @operation VARCHAR(3) DECLARE @parameterValue VARCHAR(50) SELECT @operation='DIS' CREATE table #myTable(value VARCHAR(20)) INSERT into #myTable values('@operation') SELECT top 1 @parameterValue = value from #myTable -- Now @parameterValue is assigned the string '@operation' -- Here I need some way to retrieve the value of the @operation variable I declared before (in fact -- another process writes into the table I retrieved the value from), in this case 'DIS' DROP TABLE #myTable I've tried several ways, but didn't succeed yet! Please tell me there's a way to solve my problem!!! Thank you very much in advance!
View Replies !
The Formal Parameter &&"@ReportingId&&" Was Not Declared As An OUTPUT Parameter, But...what Is This?
After running my ssis pkg for some time with no problems I saw the following error come up, probably during execution of a stored procedure ... An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The formal parameter "@ReportingId" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.". I see some references to this on the web (even this one) but so far they seem like deadends. Doe anybody know what this means? Below is a summary of how the variable is used in the stored proc. The sp is declared with 21 input params only, none of them is @ReportingId. It declares @ReportingId as a bigint variable and uses it in the beginning of the proc as follows... Code Snippet select @ReportingId = max(ReportingId) from [dbo].[GuidToReportingid] where Guid = @UniqueIdentifier and EffectiveEndDate is null if @ReportingId is null BEGIN insert into [dbo].[GuidToReportingId] select @UniqueIdentifier,getdate(),null,getdate() set @ReportingId = scope_identity() END ELSE BEGIN select @rowcount = count(*) from [dbo].[someTable] where ReportingId = @ReportingId and...lots of other conditions ...later as part of an else it does the following... Code Snippet if @rowcount > 0 and @joinsMatch = 1 begin set @insertFlag = 0 end else begin update [dbo].[GuidToReportingId] set EffectiveEndDate = getdate() where ReportingId = @ReportingId insert into [dbo].[GuidToReportingId] select @UniqueIdentifier,getdate(),null,getdate() set @ReportingId = scope_identity() end ...and before the return it's value is inserted to different tables.
View Replies !
Foreach From Variable Using 2 Dim Array Variable
Is there any way to use a 2 dimensional array of strings as the Variable Enumerator for the "Foreach From Variable Enumerator". I am trying to copy a collection of files from folder A to folder B. In a script, I would populate, let us say, an array of (2,10), for 10 files, with one column representing the source file and other column representing the target column task. Then I would like to set this string array variable as the "Variable Enumerator" for the "Foreach From Variable Enumerator" and use file system tasks in the foreach loop to perform the tasks. The problem is that the "Foreach From Variable Enumerator" does not let me choose an index, but passes only one index 0, so, I will only be able to pass just one column. How do I let the foreach enumerator let me choose an index. The other foreach enumerators, foreach item and ADO give me the option to select index. I would like the same functionality in the foreach variable. Note: I cannot use the "For Each File" enumerator, since the files are to be selected by a script only. Thanks for the help.
View Replies !
SQL Variable And IS Variable In Execute SQL Task
Hi, I have an Execute SQL Task (OLE DB Connnection Manager) with a SQL script in it. In this script I use several SQL variables (@my_variable). I would like to assign an IS variable ([User::My_Variable]) to one of my SQL variables on this script. Example: DECLARE @my_variable int , <several_others> SET @my_variable = ? <do_some_stuff> Of course, I also set up the parameter mapping. However, it seems this is not possible. Assigning a variable using a ? only seems to work in simple T-SQL statements. I have several reasons for wanting to do this: - the script uses several variables, several times. Not all SQL variables are assigned via IS variables. - For reading and mainenance purposes, I prefer to pass the variable only once. Otherwise every time the script changes u need to keep track of all questionmarks and their order. - Passing the variable once also makes it easier to design the script outside IS using Management Studio. - This script only does preparation for the actual ETL, so I prefer to keep it in one task instead of taking it apart to several consecutive Execute SQL Tasks. - I prefer to use the OLE DB connection manager because it's a de facto standard here. Could anyone help me out with the following questions: - Is the above possible? - If so, how? - If not, why not? - If not, what would be the best way around this problem? Thanx in advance, Pipo
View Replies !
|
TRACKER
