Using Declared Variable As Passphrase Slows Query

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 !

---

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 !

---

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 !

---

Local Variable Slows Down SP (?)
Hi guys I am sitting and testing som variants of this simple SP, and I have an question that I couldent answer with google or any thread in this forum.

Perhaps I am doing something really easy completly wrong here.

Why does the local variables in the first code segment slow down the overall execution of the procedure?
Dont mind the logic why I have them there are only testing som things out.

If i declare two variables the same way:
DECLARE @v INT
SET @v = 100

When I use it in a WHERE CLAUSE:
...WHERE [V] BETWEEN @v AND @x)
Is there any different then
...WHERE [V] BETWEEN 100 AND 200)

Cant figure this out, why does it hurt the performance so bad? As a C# guy its the same thing ?

Thanks in advance
/Johan

Slow

ALTER PROCEDURE [dbo].[spStudio_Get_Cdr]
@beginDate DATETIME = null,
@endDate DATETIME = null,
@beginTime INT,
@endTime INT,
@subscribers VARCHAR(MAX),
@exchanges VARCHAR(MAX) = '1:',
@beginDateValue int,
@endDateValue int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @s INT
SET @s = @beginDateValue
DECLARE @e INT
SET @e = @endDateValue
print @s
print @e

DECLARE @exch TABLE(Item Varchar(50))
INSERT INTO @exch
SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item


DECLARE @subs TABLE(Item Varchar(19))
INSERT INTO @subs
SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item

SELECT [id]
,[Abandon]
,[Bcap]
,[BlId]
,[CallChg]
,[CallIdentifier]
,[ChgInfo]
,[ClId]
,[CustNo]
,[Digits]
,[DigitType]
,[Dnis1]
,[Dnis2]
,[Duration]
,[FgDani]
,[HoundredHourDuration]
,[Name]
,[NameId]
,[Npi]
,[OrigAuxId]
,[OrigId]
,[OrigMin]
,[Origten0]
,[RecNo]
,[RecType]
,[Redir]
,[TerId]
,[TermAuxId]
,[TermMin]
,[Termten0]
,[Timestamp]
,[Ton]
,[Tta]
,[Twt]
,[Level]
FROM
[dbo].[Cdr] AS C
WHERE
(C.[DateValue] BETWEEN @s AND @e)
AND
(C.[TimeValue] BETWEEN @beginTime AND @endTime)
AND
EXISTS(SELECT [Item] FROM @exch WHERE [Item] = C.[Level])
AND
(EXISTS(SELECT [Item] FROM @subs WHERE [Item] = C.[OrigId] OR [Item] = C.[TerId]))

END



Fast

ALTER PROCEDURE [dbo].[spStudio_Get_Cdr]
@beginDate DATETIME = null,
@endDate DATETIME = null,
@beginTime INT,
@endTime INT,
@subscribers VARCHAR(MAX),
@exchanges VARCHAR(MAX) = '1:',
@beginDateValue int,
@endDateValue int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @exch TABLE(Item Varchar(50))
INSERT INTO @exch
SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item


DECLARE @subs TABLE(Item Varchar(19))
INSERT INTO @subs
SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item

SELECT [id]
,[Abandon]
,[Bcap]
,[BlId]
,[CallChg]
,[CallIdentifier]
,[ChgInfo]
,[ClId]
,[CustNo]
,[Digits]
,[DigitType]
,[Dnis1]
,[Dnis2]
,[Duration]
,[FgDani]
,[HoundredHourDuration]
,[Name]
,[NameId]
,[Npi]
,[OrigAuxId]
,[OrigId]
,[OrigMin]
,[Origten0]
,[RecNo]
,[RecType]
,[Redir]
,[TerId]
,[TermAuxId]
,[TermMin]
,[Termten0]
,[Timestamp]
,[Ton]
,[Tta]
,[Twt]
,[Level]
FROM
[dbo].[Cdr] AS C
WHERE
(C.[DateValue] BETWEEN @beginDateValue AND @endDateValue)
AND
(C.[TimeValue] BETWEEN @beginTime AND @endTime)
AND
EXISTS(SELECT [Item] FROM @exch WHERE [Item] = C.[Level])
AND
(EXISTS(SELECT [Item] FROM @subs WHERE [Item] = C.[OrigId] OR [Item] = C.[TerId]))

END

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 !

---

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 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 !

---

How To Call A Variable Declared In Parent Package
 

I have declared a variable XYZ in Parent package
Similarly I have declared ABC in Child package and have done the configuration.
I have assigned some value to XYZ
How to get the value in Child Package.

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 !

---

Help! Cygwin X Slows Down Query Analyzer
Hi,I use Xorg's XWin in rootless mode on cygwin on XP Pro for quite a bitof my work. Unfortunately it seems to slow down SQL Query Analyzer's UIterribly. If I exit out of X and/or kill it, the UI reverts back tonormal ie responds as it normally would. The slowdown usually happensif I stop using Query ANalyzer for sometime and then try to get back toit by clicking on the window.Has anybody else seen this problem?Is there a way around it?AM

View Replies !

---

Encryption By Passphrase
I am investigating methods of encrypting data in a large number of databases. It seems to me that encrypting by passphrase would be optimum because it would prevent me from having to create all those database and symmetric keys on the various databases (there wil lbe dozens of them). Can anyone give me any advice as whether or not using encryption by passphrase is a good secure method of encrypting sensitive data in a large-scale production environment?  I would most likely create a unique passphrase for each database and pass it to a stored procedure as an imput parameter.

 

Thanks,

 

Al

View Replies !

---

Connect To .DataSource=&&"....&&" Using Authetication (userid &&amp; Passphrase)!
Hi,

My school is runnnig SQL Server 2005 and i want to connect to it using authetication (userid & passphrase). As i coded the windows application i used localhost as my own computer and IntegratedSecurity shown below. I'm new in C# and SQL. I would appriciate if someone could help me out.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Personregister
{
    class DBManager
    {
        SqlConnection conn;
        SqlConnectionStringBuilder builder;

        public DBManager()
        {
         builder = new SqlConnectionStringBuilder();
         builder.DataSource="localhost";
         builder.InitialCatalog = "Personregister";
         builder.IntegratedSecurity = true;
         conn = new SqlConnection(builder.ToString());

        }
      // more code fallows ......
    }
}

Best Regards,

View Replies !

---

SQL Server 2000 SP4 Slows Down Dts Job
Hi peeps,

We have just upgraded to Service Pack 4 on our SQL Server 2000.
We have had a DTS job that normally takes about four hours to complete(this dts job has been ok for the last three years).
However, after applying SP4, this DTS job now takes over 8 hours to complete.

There are no other processes running on the box and the box is a high end Dell machine with 8 Gig of RAM.

Any advice on this would be greatly appreciated.

Bal

View Replies !

---

SQL Server 2005 Slows Down
Hi MSDN PPL!

I have an SQL Server 2005 instance that slows down over time, she almost grinds to a halt. The data is being exposed via an ASP.Net 2.0 web interface. The web application gets slower and slower over a matter of days. If I restart the SQL Server process she comes back to life and starts serving as it should - nice and snappy.

The web application does not perform much writing to the DB, 90% of the time its just reading. The DB server is worked hard by a console application that produces data each day. This console app runs for about 30 minutes during which there is a lot of reading, processing and writing back to the DB as fast as the hardware will allow. Its this massive workload that is slowing the DB server.

This seems to be related to the amount of memory that SQL Server is using. When looking at Task Manager I can see that sqlservr.exe is using 1,878,904K, this figure continues to rise while the console app runs. I have seen it over 2 GB. When the console app finishes the memory is still allocated and performance is slow. This continues to get worse after a few days of processing.

The machine's specs are:

* Windows Server 2003 R2 Standard
* SQL Server 2005 Standard 9.00.3054.00
* Twin 3.2Ghz Xeons
* 3.5 Gb RAM

I plan to apply "Cumulative hotfix package (build 3152) for SQL Server 2005 Service Pack 2" in a blind hope to solve the problem.

Any suggestions?

Sorry if this is in the wrong place guys, couldn't find a general performance topic. Please move accordingly.

Thanks,
Matt.

View Replies !

---

ADO.NET 2.0 Slows SP Execution By Large Factor
I'm hoping someone will be able to point me in the right direction for solving this problem as i've come a bit stuck.
The Sql Server 2005 Stored Procedure runs in about 3 secs for a small table when run from SQL Management Studio (starting with dbcc freeproccache before execution) but times out when run through ADO.NET on .NET (45 sec timeout).
I've made sure the connection was closed prior to opening and executing the adapter. I'm a bit stuck as where to check next though. 
Any ideas greatfully received, Thanks

View Replies !

---

Text Column Slows Down Select
Hi All,We're running SQL Server 2000, SP3.I have a stored procedure that consists of a single Select statement.It selects a bunch of columns one of which is a column of data typeTEXT.SP takes 30 sec to run which causes timeouts on the Front End.When I comment out the Text column from the select it only takes 1Sec.Is there anything I can do about it? I know I can't index a Textcolumn. It's also not used in the where clause, so no need forFull-Text Search.But we absolutely have to have it in the Select clause.Thanks for the help in advance.~Narine

View Replies !

---

System Dumping Log On TempDB Slows Server
Anyone figured out a way not to reduce the server to a crawl when it goes to dump the transaction log on TempDB?

For some reason it slows the whole server down until it is finished....

View Replies !

---

T-SQL Select Statement Slows Down When Insert Into Is Put In Front
Hi
 
We have a t-sql statement in a SP that generates on average between 50 €“ 60 rows of data, pretty small! The statement references a View, some tables and temporary # table which has been created in the SP.
 
Everything works a treat and runs sub second until you put a Insert Into in front of the above statement scenario. The SP then takes a about a minute to run which happens to be about the same amount of time to generate all the data in the View.
 
I have not attached T-Sql statement at this stage as it runs ok without the Insert Into but would be happy to post  it if need be.
 
Anybody else ever had this problem?
 
We are using SQL Server 2005 SP2 64 bit.
 
Art99
 

View Replies !

---

Report Performance Slows Down As SP Is Run Multiple Times.
Hi all,
In my project, I have a website and through that, I run my reports. But the reports take a lot of time to render. When I checked the profiler, it showed that the SP for the report is run around 4-5 times. Due to this, the report rendering takes a lot of time.
 
When, I ran the SP with the same set of Parameters in Query Analyser, it ran in around 18 seconds. But when I ran the report from web interface, it took around 3 minutes to completely show the data. And the SP has been run 5 times.
 
I am having serious problems with Report's performance because of this. Many a times, report just times out. I have set the timeout as 10 minutes. And because the Sp is run 5 times, the report times out, if there is huge amount of data.
 
Any help would be appreciated.
 
Thanks in advance.
Swati

View Replies !

---

Will Change In Index Slows Down Queries In SQL Server2000 Sp4.
 

Hi
 
Will change in index  slows down queries in SQL Server2000 sp4.
 
Please help me

View Replies !

---

Large Transaction Log Dramatically Slows Down Processing Any Idea Why?
While working on the data migration in the last 6 weeks or so, I've gotten some amazing results in performance differences due to the size of the transaction log file.
Initially, the database was created (accidentally by typo) with a transaction log file size of 3 Gig. It should have been 300 Meg. We had
been running migration with this database for about two months. In particular, there was a SQL->SQL migration of the shopping carts that consistently took 5 hours.
This was consistent across 3 different machines, with the more powerful machine being about 20% faster.

On the last round of migration testing, we rebuilt the database with the corrected 300 Meg transaction log file size. When I ran the shopping cart migration, it took 17 minutes. I couldn't believe the difference, so I tested a variety of things to confirm, and sure enough: When the only difference is the size of the transaction log file, a 5 hour process was reduced to 17 minutes! (the data was validated as well).

All else was held equal - the tables, indexes, stored procedures, hard drive space (with the exception of the log file size difference, but there was
ample room on the hard drive in either case). I even took the 17 minute version and grew the transaction log to 3gig, after which it degraged to 5 hours.

We've discussed this here, and we can't explain it. If anyone has any insight, please share it with us.

Many thanks in advance,

Tom

View Replies !

---

SQL Integration Services Data Flow Task Slows Down
We are using an OLE DB Source for the Data Flow Source and OLE DB Destination for the Data Flow Destination.  The amount of data being moved is about 30 million rows, and it is gather using a sql command.  There is not other transformations in between straight from one to another.  The flow starts amazingly fast but after 5 million rows it slows considerably.  Wondered if anyone has experienced anything similar with large loads.

View Replies !

---

SQL Server 2005 Slows Down After A Large Number Of Queries
Hi,

We are running SQL Server 2005 Ent Edition with SP2 on  a Windows 2003 Ent. Server SP2 with Intel E6600 Dual core CPU and 4GB of RAM. We have an C# application which perform a large number of calculation that run in a loop. The application first load transactions that needs to be updated and then goes to each one of the rows, query another table get some values and update the transaction.

I have set a limit of 2GB of RAM for SQL server and when I run the application, it performs 5 records update (the process described above) per second. After roughly 10,000 records, the application slows down to about 1 record per second. I have tried to examine the activity monitor however I can't find anything that might indicate what's causing this.

I have read that there are some known issues with Hyper-Threaded CPUs however since my CPU is Dual-core, I do not know if the issue applies to those CPUs too and I have no one to disable one core in the bios.

The only thing that I have noticed is that if I change the Max Degree of Parallelism when the server slows down (I.e. From 0 to 1 and then back to 0), the server speeds up for another 10,000 records update and then slows down. Does anyone has an idea of what's causing it? What does the property change do that make the server speed up again?

If there is no solution for this problem, does anyone know if there is a stored procedure or anything else than can be used programmatically to speed up the server when it slows down? (This is not the optimal solution however I will use it as a workaround)

Any advice will be greatly appreciated.

Thanks,
Joe

View Replies !

---

SSIS Data Flow Task Slows During Bulk Insert
I have an SSIS Package which is designed to import log files.  Basically, it loops through a directory, parses text from the log files, and dumps it to the database.  The issue I'm having is not with the package reading the files, but when it attempts to write the information to the db.  What I'm seeing is that it will hit a file, read 3000 some lines, convert them (using the Data Conversion component), and then "hang" when it tries to write it to the db.

 

I've run the SQL Server Profiler, and had originally thought that the issue had to do with the collation.  I was seeing every char column with the word "collate" next to it.  On the other hand, while looking at  the Windows performance monitor, I see that the disk queue is maxed at 100% for about a minute after importing just one log file.

 

I'm not sure if this is due to the size of the db, and having to update a clustered index, or not.

 

The machine where this is all taking place has 2 arrays- both RAID 10.  Each array is 600 GB, and consists of 8 disks.  The SSIS package is being executed locally using BIDS.

 

Your help is appreciated!

 

 

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 !

---

Can A Calc'd Query Column Be Compared Against A Multi Value Variable Without A Nested Query?
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 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 !

---

Substringing Declared Varables In SQL Server
I need to be able to update a row of data in a table based upon the first character of a char(50) field.

if char(1) of employee_Job_class = "X" then update field Class_description = "Temporary"

Anyone have any suggestions ??

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 !

---

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:&nbsp;
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
<br />
LabData-Conc:
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
<br />
Average values: &nbsp;
<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 !

---

Query Using Variable - DB2
Hi all,
Sorry if this is the wrong place to ask but I have tried a few places with no joy.
In SQL when querying using a variable I can use @ symbol.
ie:
SELECT COUNT(*) FROM dbo.Persons WHERE SocialSecurityNumber = @SocialSecurityNumber
Then in say visual web developer this would prompt me to enter the variable for @SocialSecurityNumber
This all works perfect.
My issue is when I query a DB2 DB directly say
SELECT COUNT(*)
FROM DB2.table
WHERE (MMITNO = @MMITNO)
I get ther error "Column @MMITNO does not exist"
My question is. What symbol do I use to represent variable in DB2.
In SQL it is @ ..I think these symbols are known as sigils
Please Help
Ray..

View Replies !

---

Using A Variable For A Column Name In A Query
Is it possible to use a column name variable in a Select Statement for a column name?For example I have a dropdown with FName,LName,Phone and a text box. The user can select the field to search and the criteria will go into the text box. The problem is the select doesn't like a variable for the field name. I have tried both a standard variable and a Case statement (see below). This is being used in a Stored Procedure with MSSQL. The actual select is much more complicated than this but it gets the point across. Thanks for your help in advance@Field as varchar( 50),@Value as varchar (50)SELECT *FROM customersWHERE @Field = @ValueORSELECT *FROM customersWHERE      CASE WHEN @Field = 'Fname' THEN Fname = @Value END,     CASE WHEN @Field  = 'Lname' THEN Lname = @Value END,     CASE WHEN @Field  = 'Phone' THEN Phone = @Value END;

View Replies !

---

Sql Query Variable Problem
Hello


I have a sql statement that i want to assign a variable to a field. Can anybody tell me how to do it.



queryString = "select [problemdescr] from [Problemlist] where [problemnumber] = abc "



what i want is to assign the value of the variable abc to problemnumber. I have tried to use @ to solve the problem, but i couldn't.


any help

View Replies !

---