Set Value For Variable In A Declared Cursor

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql

View Replies !

---

T-SQL Cursor Variable..
Hello All,Is there a T-SQL equivalent of the PL/SQL cursor rowtype datatype ?Thanks in advance

View Replies !

---

Cursor/variable Help
I'm not sure about this one so if someone could help I'd appreciate this.

As shown below I've declared a variable name1 to be used in a while statement substituting for an object name in a select statement (2000 SP3a)
and throwing the shown error. Are variables allowed to be used to substitute for object names or is there another problem? Thanks.

View Replies !

---

How To Use Variable In A Cursor
if i have the cursor

cursor:

cursor for
select name
from sysobjects
where type='P' AND category='0'

and wants a variable before sysobjects which equals a table name, so itll be:

cursor for
select name
from @variable..sysobjects
where type='P' AND category='0'

how do i include a variable within a cursor statement

View Replies !

---

Bind Variable In CURSOR
SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.I have a stored procedure which is not working the way I think itshould be.I have a CURSOR which has a variable in the WHERE clause:DECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameIt won't return anything, even when I verify that @dbname has a valueand if I run the query in Query Analyzer with the value, it returnsrows:SELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = 'Archive'ORDER BY tablenameDB_Rpt_Fragmentation11575791622006-03-29 09:52:11.7772006-03-2909:52:11.823DtsAdtStdArchive_DataSourceType5175768822006-03-2909:52:11.8702006-03-29 09:52:11.887DtsADTstdArchiveNotUsed3575763122006-03-29 09:52:11.8872006-03-2909:52:12.103I've taken out most of the guts for simplicity, but here's what I'vegot:--CREATE TABLE dbo.db_ind--(--db_ind_tkintIDENTITY,-- id int NULL,-- tablename sysname NOT NULL,-- indid int NULL,-- indexname sysname NOT NULL,-- shcontig1dt datetime NULL,-- defragdt datetime NULL,-- shcontig2dt datetime NULL,-- reindexdt datetime NULL--)ALTER PROCEDURE IDR(@hours int)AS--SET NOCOUNT ON--SET ANSI_WARNINGS OFFDECLARE @tabname varchar(100),@indname varchar(100),@dbname varchar(50),@vsql varchar(1000),@v_hours varchar(4),@shcontig1dtdatetime,@shcontig2dtdatetime,@defragdtdatetime,@reindexdtdatetime,@idint,@indidint,@rundbcursorint,@runtabcursorint,@runindcursorintDECLARE get_dbs CURSOR local fast_forward FORSELECT dbnameFROM db_jobsWHERE idrdate < getdate() - 4or idrdate is nullORDER BY dbnameDECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameDECLARE get_inds CURSOR local fast_forward FORSELECT indid, indexname, defragdt, reindexdtFROM db_indWHERE dbname = @dbnameAND tablename = @tabnameORDER BY indexnameOPEN get_dbsFETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0SELECT @v_hours = CONVERT(varchar,@hours)--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====WHILE @rundbcursor = 1BEGIN -- db whilePRINT '============================='PRINT @dbnamePRINT '============================='--================================================== ================================================== =====--================================================== ================================================== =====OPEN get_tabsFETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0BEGINPRINT 'table: ' + @tabnameSELECT @runtabcursor = 1endELSEBEGINPRINT 'not getting any tables! '-- <<<<< THIS IS WHERE IT HITSSELECT @runtabcursor = 0endWHILE @runtabcursor = 1BEGINPRINT @dbnamePRINT @tabname--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'Index:' + @dbname + '.' + @tabname + '.' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END-- 1st loop through indexesCLOSE get_inds--================================================== ================================================== =====--==========PRINT 'db.tab: ' + @dbname + '.' + @tabname--==========--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'dbname: ' + @dbnamePRINT 'tabname: ' + @tabnamePRINT 'indname: ' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END -- 2nd loop through indexesCLOSE get_inds--================================================== ================================================== =====FETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0SELECT @runtabcursor = 1ELSESELECT @runtabcursor = 0END-- loop through tablesCLOSE get_tabs--================================================== ================================================== =====--================================================== ================================================== =====PRINT 'Index Maintenence complete. Job report in[DB_Rpt_Fragmentation]'PRINT ''FETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0END -- loop through databasesCLOSE get_dbsdeallocate get_dbsdeallocate get_tabsdeallocate get_inds--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====GOAnd this is what I'm getting:=============================Archive=============================(0 row(s) affected)not getting any tables!Index Maintenence complete. Job report in [DB_Rpt_Fragmentation]......etc.Am I missing something obvious?Thank you for any help you can provide!!

View Replies !

---

Using Cursor Variable As A Tablename
I am currently using a cursor to scroll through sysobjects to extract table names and then extracting relevant column names from syscolumns.

I then need to run the following script:

declare Detail_Cursor cursor for
select @colname, max(len(@colname)) from @table

The message I receive is "must declare variable @table".

Immediately prior to this script I printed out the result of @table which works fine so the variable obviously is declared and populated.

Can anyone let me know what I'm doing wrong or how I can get the same result.

Thanks

View Replies !

---

Cursor Variable Problem
My code below is supposed to pull the destination and then work out 2 calculations per month on each destination. The results returns the string '@destination' instead of the value from the table and also the month value is returned completely wrong. My main problem though is the getting the correct destination to return. If anybody has any pointers I would be very grateful. This is driving me mad!!
Thanks a lot in advance

Here some sample code:


DECLARE @Destination char(50)
DECLARE AVGCallsToDest INSENSITIVE scroll CURSOR

FOR
SELECT destination from table1

OPEN AVGCallsToDest
FETCH First FROM AVGCallsToDest
INTO @Destination

WHILE (@@FETCH_STATUS = 0 )
BEGIN

exec("Insert into NewTable(Card, [Month], NumberOfCalls, AverageLength, Destination)
select 'Phone Card' as 'Card', DATEPART(mm,adetdate) as 'Month',count(*) as 'NumberOfCalls',
avg(bdur)/60 as 'AverageLength', "" +@Destination+ "" as 'Destination'
from table1 (nolock)
whereDATEPART(mm,adetdate) = 5 and
DATEPART(yyyy,adetdate) = DATEPART(yyyy,getdate()-1)
group by DATEPART(mm,adetdate),bdur
order by Month")


FETCHNext FROM AVGCallsToDest
INTO@Destination
End
CLOSE AVGCallsToDest
DEALLOCATE AVGCallsToDest

View Replies !

---

How To Iterate Through Table Variable Without Using Cursor
Hi,
I need a small help. In my stored procedure, i create a table variable and fill it with records based on my query. The ID field within the table is not continous and can have any value in increasing order .e.g. The ID sequence may be like 20, 33, 34, 59, 78, 79... I want to iterate through each record within the table but without using a Cursor. I want to use a loop for this purpose. There are many articles pointing out how to iterate through records in a table variable but that requires the IDs to be continous which is not possible in my case. Can anyone help me solve this problem...
Any help is appreciated...

View Replies !

---

Variable As Column In Cursor Select
I can't seem to get a cursor to work when I'm passing in a variable for a column name of the select statement. For example:

declare @col varchar(50)

set @col = 'Temperature'

declare notifycurs cursor scroll for
select @col from Table

Obviously this won't work correctly (since the result will simply be 'Temperature' instead of the actual float value for temperature). I tried to use quotes for the entire statement with an EXEC
(ie. exec('select '+@col+' from Table' )
but that gave me an error.

Is there a way to pass in a variable for a column name for a curor select statement????

View Replies !

---

Variable As Field Name In CURSOR FOR UPDATE
I'm trying something like:

UPDATE tbl SET @varFieldName = @varValue

The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd.

I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then.

To get the field name, I :

SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars'

Thanks,
Carl

View Replies !

---

Adding Variable To SELECT For CURSOR
I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable.
The following fails to parse:

Declare Cursor1 Cursor
For
'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name'
Open cursor1

That doesn't work, I've also tried using an Execute() statement, no luck there either. Any ideas or suggestions are greatly appreciated.

View Replies !

---

Possible To Fetch Next From Cursor Into Table Variable?
 

Hello,
 
I have searched the net for an answer but could not find one. When I declare a table variable
and then try to insert fetched row into the table variable like:
 



Code Snippet
declare @table table (col1 nvarchar(50), col2 nvarchar(50))
declare curs for
select * from sometable
open curs
fetch next from curs into @table
 
 


 
 
it does not work. any help would be great.
 
thnx

View Replies !

---

How To Read The Rows In A Cursor Variable
Hi,

I have a dynamic query that returns its values in a cursor variable.
How do I read each row from this cursor in a loop ?
Eg.:
use AdventureWorks
go
DECLARE @sqlnvarchar(4000),
@paramsnvarchar(4000),
@tables_cursorcursor,
@db_namenvarchar(50),
@table_namenvarchar(4000),
@schema_namenvarchar(50);

set @db_name = 'AdventureWorks';
set @schema_name = 'Production';
set @table_name = 'BillOfMaterials, Product';
set @sql =
' select a.name table_name ' +
' from ' + @db_name + '.sys.tables a join ' + @db_name + '.sys.schemas b ' +
' on (a.schema_id = b.schema_id) ' +
' where b.name= @schema_name1 ' +
' and @table_name1 is null ' +
' order by 1; '
SELECT @params = N' @table_name1 nvarchar(3000) ,' +
N' @schema_name1 nvarchar(100) ,' +
N' @cursor cursor output'

EXEC sp_executesql @sql, @params, @table_name,@schema_name , @tables_cursor OUTPUT

View Replies !

---

Storing The Result Into A Variable W/o Cursor
Hello All,

A select statement returns one column with more than one rows.Is there a way to store it in sql Variable without using a Cursor.

Thank you in advance

View Replies !

---

Variable Assignment In Cursor Declaration
Hi,

here is the code segment below;
...
DECLARE find_dates CURSOR FOR
SELECT @SQL = 'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC'
EXEC (@SQL)

but it gives error, variable assignment is not allowed in a cursor declaration. I need to use dynamic SQL , the only way to access all the dbs and their tables inside. Please help.

thanks

View Replies !

---

How To Include Variable In CURSOR SQL Filter Clause?
After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim

View Replies !

---

Output Of Select Stament Into Variable Within Cursor
Within a cursor that I am building I would like to execute a select statement built from a varchar variable as such:

SELECT @BuildDBPageUsed = 'SELECT sum(reserved) FROM ' + @DatabaseName + '.dbo.sysindexes WHERE segment <> 2'

Next I execute the statement, which is now in the variable @BuildDBPageUsed. Such as:

EXEC (@BuildDBPageUsed)

The resulting output I need to set to another variable @DBPageUsed which is integer. I have been unsuccessful in finding the correct set of commands to do this. How should I build the command to input the results of the EXEC (@BuildDBPageUsed) into the integer variable @DBPageUsed?

*Thanks* for any help in this matter.
Brad

View Replies !

---

In Cursor: Help To Perform Proper Comparison Using Variable
Hi All,

I failed to find record when using variable in cursor in WHERE clause:

ID is uniqueidentifier field in the table
DECLARE @EncounterID uniqueidentifier
........
WHERE ID = @EncounterID -> this does not work, though @EncounterID is set properly and can see its value in debugger

WHERE ID = 'E3AE2C5B-06F2-4A3C-A3A4-7D6CC43DE012' -> this works fine and record found

Tried to CAST(@EncounterID as char(40)) but still no luck.

I would greatly appreciate any advise hot to make it working.

Thank you very much in advance

Roman

View Replies !

---

Question About Assigning Cursor Variable Data Types...
Hi,
I'm just curious about something...when assigning a datatype to a cursor variable in ORACLE, you can use the keyword TYPE to automatically grap the datatype from the associated column (or use ROWTYPE to create a cursor variable to represent an entire row in a table). It's so much easier and if the datatype of one of the cursor columns is altered, the associated cursor variable assumes the new datatype.

Is there a way to do this in SQL Server 7.0 ?

many thanks :)

View Replies !

---

Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list


I have difficulty combining a select statement with a where clause stored in a variable inside a cursor

The users select the mail content and frequency of delivery and i deliver the mail

I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.

Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work

For example

DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3

I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary

How do I combine the select statement with the where clause?

Help me...I need help urgently

View Replies !

---

Dynamic Query, Local Cursor Variable And Global Cursors
Hi all.



I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.



So, first things first: let me explain what I need to do. I am
designing a web application that will allow users to consult info
available in a SQL2000 database. The user will enter the search
criterea, and hopefully the web page will show matching results.



The problem is the results shown aren't available per se in the DB, I
need to process the data a bit. I decided to do so on the SQL Server
side, though the use of cursors. So, when a user defines his search
criteria, I run a stored procedure that begins by building a dynamic
sql query and creating a cursor for it. I used a global cursor in order
to do so. It looked something like this:



SET @sqlQuery = ... (build the dinamic sql query)

SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

OPEN myCursor

FETCH NEXT FROM myCursor INTO ...

CLOSE myCursor

DEALLOCATE myCursor



This works fine, if there's only one instance of the
stored procedure running at a time. Should another user connect to the
site and run a search while someone's at it, it'll fail due to the
atempt to create a cursor with the same name.



My first thought was to make the cursor name unique, which led me to:

...

SET @cursorName = 'myCursor' + @uniqueUserID

SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

...



The problem with this is that I can't do a FETCH NEXT FROM @cursorName since
@cursorName is a char variable holding the cursor name, and not a
cursor variable. So to enforce this unique name method the only option
I have is to keep creating dynamic sql queries and exucting them. And
this makes the sp a bitch to develop and maintain, and I'm guessing it
doesn't make it very performant.



So I moved on to my second idea: local cursor variables. The problem with
this is that if I create a local cursor variable by executing a dynamic
query, I can't extract it from the EXEC (or sp_executesql) context, as
it offers no output variable.


I guess my concrete questions are:


Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?



Anybody sees another way arround this?Thanks in advance,

Carlos

View Replies !

---

Variable In CURSOR Sql Statement (was &"Please Help Me&")
Hi All,

What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for
the @Where. (I tried with DEBUGGING option of Query Analyzer also).

=============================================
SET @Where = ''
if IsNull(@Input1,NULL) <> NULL
Set @Where = @Where + " AND studentid='" + @input1 +"'"

if isnull(@Input2,NULL) <> NULL
Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"

DECLARE curs1 CURSOR SCROLL
FOR SELECT
firstname
FROM
school
WHERE
school ='ABC' + @where
=============================================
Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ?

Thanks !

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 !

---

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 !

---

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 !

---

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

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 !

---