declaring cursors

I need to declare and initialise a cursor using variable field names and tables. However, an error occurs when I use the fetch next into

Server: Msg 16924, Level 16, State 1, Line 98
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

If I use the second (commented out) statement which hard codes the fields the whole script works fine.

Can anyone point out where I'm going wrong. The cursor appears to be built but I cannot use it.

Thanks
Paul

Declare Keys_cursor CURSOR FOR Select + @.LastNameField + ', ' +
@.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' +
@.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

--Declare Keys_cursor CURSOR FOR Select surname,gender,post_code,forename,title,add1,add2, add3 from test_credit_data

OPEN Keys_cursor

FETCH NEXT FROM Keys_cursor
INTO @.LastName,@.Gender, @.PostCode, @.FirstName, @.Title, @.Add1, @.Add2, @.Add3I don't see any error by this help u, you can try to probe ur parametirized select whit statement EXECUTE like this:

EXECUTE Select + @.LastNameField + ', ' + @.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' + @.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

so you'll can know if your statement is correct.

bye,
Maritzita

Originally posted by plineham
I need to declare and initialise a cursor using variable field names and tables. However, an error occurs when I use the fetch next into

Server: Msg 16924, Level 16, State 1, Line 98
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

If I use the second (commented out) statement which hard codes the fields the whole script works fine.

Can anyone point out where I'm going wrong. The cursor appears to be built but I cannot use it.

Thanks
Paul

Declare Keys_cursor CURSOR FOR Select + @.LastNameField + ', ' +
@.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' +
@.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

--Declare Keys_cursor CURSOR FOR Select surname,gender,post_code,forename,title,add1,add2, add3 from test_credit_data

OPEN Keys_cursor

FETCH NEXT FROM Keys_cursor
INTO @.LastName,@.Gender, @.PostCode, @.FirstName, @.Title, @.Add1, @.Add2, @.Add3|||To do this I need to change the sting to put quotes around the 'Select ' of the statement If I do this the SQL works fine

Thanks for your help|||Great...cursors are bad enough, noe we have dynamicx sql cursors...

what are you trying to do?

I hope it's an admin function...

(as opposed to an applcation function)

I mean you still have to code the fetch, and you still have to KNOW what you're working with, and you'll still have to reference the variables in the sproc...

so...

why bother?|||We have about 30 different databases which all have keys built on them in the same way. When component data is changed the keys need rebuilding.

If we use ADO and VB or C to move through a recordset to update these keys time can become a serious problem. Building the keys within SQL would be even more complicated. I decided to then write an ActiveX dll which was referenced from an SQL function. This was paramatized and would return the key I wanted to update into the database.

However, and I apologize for the life story but I have been unable to think of a quicker way to complete this process, building six keys and updating was obviuosly extremelly inefficient. Therefore I wanted to build all six from the ActiveX Dll, update the record for all keys in the same procedure. This again worked fine for one table but a generic script for all the tables was the obvious next step, hence the need for dynamic table and field names.

I have attached the script as is if anyone wants to have a look. Laugh at how badly I've done but still give me a better solution

Otherwise Brett Kaiser I'll see you behind the bike sheds after school if you've still got a problem with me|||You dude..no problem...just trying to help you..

What do you mean by building keys?

And this..

Building the keys within SQL would be even more complicated.

Doesn't make sense to me...

mostly because I don't understand what building keys means...

In any event...I hope it's working for you...

AND it's FRIDAY...yeeeeHaaaaaaa

Now if I can find my lost shaker of salt, I'd be in business..|||It was friday but it is now monday. Aaaaaaa not good at all.

The key I mean is essentially a string containing key components of an address

for instance on every record we have key which contains the first seven alpha characters of the surname and the postcode. This enables us to match records within databases based on this key.

To build these keys we have simple VB code - which is as you'd expect a series of mid, case and character matching to build the key. Not all the keys are this simple.

I cannot even imagine how I could do this in SQL. It is unfortunately not a simple case of using substring.

The solution at the moment is to open a recordset and move through the data updating the keys but this is obviously slow when updating 100000 records in a database containing 3 million.

Thanks for your help anyway|||What are the rules to build a "key" and how do you update it?

Sounds like a job for a user defined function.

If you can do it in VB, you can do it in T-SQL (Well except for Arrays, but youcan fake that out too)

Can you post some VB code?|||Find attached the classes we use to build some of the keys we use for address manipulation.

If you know a way to build all these keys into separate fields through SQL I will be very grateful

decimal datatpe with less than or equal

Hi All,
I have a field whose datatype is decimal.
I am trying to see if the value is less than or equal to zero. I am
getting Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Here is what I was doing
select field1=
CASE WHEN table2.MaxLabrHrs<=0.00
THEN table1.field2* table2.LaborAmount
end
I am not sending DDL hoping that it will be easy fix.
Problem is here when I put : table2.MaxLabrHrs<=0.00
Field MaxLabrHrs is decimal datatype (9,2).
If I put only less than then it works.
Thanks a million in advance.
Best regards,
mamun>> I am not sending DDL hoping that it will be easy fix.
It may not be. The error might be to generated from another section of the
code. It might be worthwhile to check the datatypes & data in the field2
column as well as the LaborAmount column.
Anith|||I don't think it's that column that's giving the error.
What types are the "field2" and "LaborAmount" columns you mention?
It's most likely that some row where MaxLabrHrs=0 has an unconvertible
varchar column.
And it would be easier to fix with DDL, btw ;)
microsoft.public.dotnet.languages.vb wrote:

>Hi All,
>I have a field whose datatype is decimal.
>I am trying to see if the value is less than or equal to zero. I am
>getting Server: Msg 8114, Level 16, State 5, Line 1
>Error converting data type varchar to numeric.
>Here is what I was doing
>select field1=
>CASE WHEN table2.MaxLabrHrs<=0.00
>THEN table1.field2* table2.LaborAmount
>end
>
>I am not sending DDL hoping that it will be easy fix.
>Problem is here when I put : table2.MaxLabrHrs<=0.00
>Field MaxLabrHrs is decimal datatype (9,2).
>If I put only less than then it works.
>Thanks a million in advance.
>Best regards,
>mamun
>
>|||Thanks a ton.
Yes, the field2 in the table1 is numeric.
Is there anything I can do without changing the numeric datatype in the
table1?
If not then I will change that to decimal.
Thanks again,
best regards,
mamun|||NUMERIC & DECIMAL are well compatible and so that is a non-issue. You might
want to check the data in all involved columns. Also check the datatype of
the LaborAmount as well.
If you still find it a problem, consider posting the table DDLs & some
sample data.
Anith|||The following codes were written from Mr. Anith Sen's help probably two
years ago (or more). Then it became necessity to use another field
MaxLabrHrs in the criteria table.
This is the code I am using:
select V_LABCST=
CASE WHEN t2.LaborIndicator = 'R' AND t2.LaborAmount > 0.00 and
t2.MaxLabrHrs <=0.00
THEN t1.V_APPLBRHRS * t2.LaborAmount
WHEN t2.LaborIndicator = 'R' and (t2.LaborAmount=0.00 or
t2.LaborAmount='' or t2.LaborAmount is null) and t2.MaxLabrHrs <=0.00
THEN t1.V_APPLBRHRS * t3.Labor_Rate
WHEN t2.LaborIndicator = 'R' and t2.MaxLabrHrs>0.00
THEN CASE
WHEN t1.V_APPLBRHRS<t2.MaxLabrHrs THEN
t1.V_APPLBRHRS * t3.Labor_Rate
WHEN t1.V_APPLBRHRS>t2.MaxLabrHrs THEN
t2.MaxLabrHrs * t3.Labor_Rate
END
END
from Filtered_Data_Hold t1
JOIN Criteria t2
ON t1.Supplier_ID = t2.SupplierID
join Claims t3 on t1.Claim_Number=t3.Claim_Number
where t1.Claim_Number='1I23456'
CREATE TABLE [dbo].[Criteria] (
[Supplier_ID] [varchar] (10) NOT NULL ,
[LaborIndicator] [char] (2) ,
[LaborAmount] [decimal](8, 2) NULL ,
[MaxLabrHrs] [decimal](9, 2) NOT NULL
)
Insert into criteria('AX812', 'R', 67.00 , .66)
CREATE TABLE [dbo].[Filtered_Data_Hold] (
[Claim_Number] [char] (9) NOT NULL ,
[Supplier_ID] [char] (7) NULL ,
[Req_Lab_Hrs] [numeric](6, 1) NOT NULL ,
[V_APPLBRHRS] [numeric](6, 1) NOT NULL ,
[V_LABCST] [numeric](10, 2) NOT NULL
) ON [PRIMARY]
GO
insert Filtered_Data_Hold('1I23456', 'AX812', 1.0, 1.0, 67.00)
CREATE TABLE [dbo].[Claims] (
[Claim_Number] [char] (9) NOT NULL ,
[VAppLabr_Hrs] [numeric](6, 1) NOT NULL
)
Insert Claims ('1I23456', 1.0)
I want the result to be 67.00*.66=44.22 (when there is max labor hours
in the criteria table greater than 0.00, I want to multiply max labour
hours with the labor rate).
Thanks a ton for your help.
best regards,
mamun|||comparing t2.LaborAmount to the empty string is the problem
(t2.LaborAmount=0.00 or
-->>>> t2.LaborAmount=''
or t2.LaborAmount is null)
microsoft.public.dotnet.languages.vb wrote:

>The following codes were written from Mr. Anith Sen's help probably two
>years ago (or more). Then it became necessity to use another field
>MaxLabrHrs in the criteria table.
>This is the code I am using:
>select V_LABCST=
>CASE WHEN t2.LaborIndicator = 'R' AND t2.LaborAmount > 0.00 and
>t2.MaxLabrHrs <=0.00
> THEN t1.V_APPLBRHRS * t2.LaborAmount
> WHEN t2.LaborIndicator = 'R' and (t2.LaborAmount=0.00 or
>t2.LaborAmount='' or t2.LaborAmount is null) and t2.MaxLabrHrs <=0.00
> THEN t1.V_APPLBRHRS * t3.Labor_Rate
> WHEN t2.LaborIndicator = 'R' and t2.MaxLabrHrs>0.00
> THEN CASE
> WHEN t1.V_APPLBRHRS<t2.MaxLabrHrs THEN
> t1.V_APPLBRHRS * t3.Labor_Rate
> WHEN t1.V_APPLBRHRS>t2.MaxLabrHrs THEN
> t2.MaxLabrHrs * t3.Labor_Rate
> END
> END
>from Filtered_Data_Hold t1
> JOIN Criteria t2
> ON t1.Supplier_ID = t2.SupplierID
>join Claims t3 on t1.Claim_Number=t3.Claim_Number
>where t1.Claim_Number='1I23456'
>
>CREATE TABLE [dbo].[Criteria] (
> [Supplier_ID] [varchar] (10) NOT NULL ,
> [LaborIndicator] [char] (2) ,
> [LaborAmount] [decimal](8, 2) NULL ,
> [MaxLabrHrs] [decimal](9, 2) NOT NULL
> )
>Insert into criteria('AX812', 'R', 67.00 , .66)
>
>CREATE TABLE [dbo].[Filtered_Data_Hold] (
> [Claim_Number] [char] (9) NOT NULL ,
> [Supplier_ID] [char] (7) NULL ,
> [Req_Lab_Hrs] [numeric](6, 1) NOT NULL ,
> [V_APPLBRHRS] [numeric](6, 1) NOT NULL ,
> [V_LABCST] [numeric](10, 2) NOT NULL
> ) ON [PRIMARY]
>GO
>insert Filtered_Data_Hold('1I23456', 'AX812', 1.0, 1.0, 67.00)
>
>CREATE TABLE [dbo].[Claims] (
> [Claim_Number] [char] (9) NOT NULL ,
> [VAppLabr_Hrs] [numeric](6, 1) NOT NULL
> )
>Insert Claims ('1I23456', 1.0)
>
>
>I want the result to be 67.00*.66=44.22 (when there is max labor hours
>in the criteria table greater than 0.00, I want to multiply max labour
>hours with the labor rate).
>
>Thanks a ton for your help.
>best regards,
>mamun
>
>

Debugging my sprock

From this msg im guessing that SQLE.DLL nee to be installed on my SQLServer box, is that correct? Where would i find this dll?

Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger on APOLLO-ClintonSQL (Error = 0x800401f3). Ensure that client-side components, such as SQLLE.DLL, are installed and registered on PUR-CO-Clinton2. Debugging disabled for connection 59.Start here (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp).

-PatP

Debugging "Stored procedure in T-SQL Debugger"

For 2 days already Stored procedures on SQL Server don`t work.
When debugging:
"ODBC: Msg 0, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot load the DLL
mssdi98.dll, or one of the DLLs it references. Reason: 126(The specified
module could not be found.)."
Other object in database (tables,views) work OK.
What is going on?
Vladimir
Did you read the troubleshooting section in Books Online? I searched for below string and found the section:
"Cannot load the DLL"
Make sure you use the updated Books Online:
http://www.microsoft.com/SQL/techinf...00/default.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Majstor" <majstorv@.hotmail-removethis-.com> wrote in message news:O5q2UF0FEHA.1012@.TK2MSFTNGP11.phx.gbl...
> For 2 days already Stored procedures on SQL Server don`t work.
> When debugging:
> "ODBC: Msg 0, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot load the DLL
> mssdi98.dll, or one of the DLLs it references. Reason: 126(The specified
> module could not be found.)."
> Other object in database (tables,views) work OK.
> What is going on?
> Vladimir
>

Debugger

I am trying to use the debugger for a stored procedure, each time I use it I
get the following error
<<ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.>>
I have tried using the debugger on the simplest of stored procedures and
this error happens without fail every time.
Any ideas?
Ta
Paul
Paul,
For Debugger to work, SQL Server service account should be configured to
start using domain account.
Thanks
Shri.DBA
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F65FA3AE-8CC5-450C-9F03-260300AFEEBD@.microsoft.com...
> I am trying to use the debugger for a stored procedure, each time I use it
I
> get the following error
> <<ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
SQL
> Server is terminating this process.>>
> I have tried using the debugger on the simplest of stored procedures and
> this error happens without fail every time.
> Any ideas?
> Ta
> Paul

Debugger

I am trying to use the debugger for a stored procedure, each time I use it I
get the following error
<<ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.>>
I have tried using the debugger on the simplest of stored procedures and
this error happens without fail every time.
Any ideas?
Ta
PaulPaul,
For Debugger to work, SQL Server service account should be configured to
start using domain account.
Thanks
Shri.DBA
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F65FA3AE-8CC5-450C-9F03-260300AFEEBD@.microsoft.com...
> I am trying to use the debugger for a stored procedure, each time I use it
I
> get the following error
> <<ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
SQL
> Server is terminating this process.>>
> I have tried using the debugger on the simplest of stored procedures and
> this error happens without fail every time.
> Any ideas?
> Ta
> Paul

Debugger

I am trying to use the debugger for a stored procedure, each time I use it I
get the following error
<<ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionH
andler:
Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQ
L
Server is terminating this process.>>
I have tried using the debugger on the simplest of stored procedures and
this error happens without fail every time.
Any ideas?
Ta
PaulPaul,
For Debugger to work, SQL Server service account should be configured to
start using domain account.
Thanks
Shri.DBA
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F65FA3AE-8CC5-450C-9F03-260300AFEEBD@.microsoft.com...
> I am trying to use the debugger for a stored procedure, each time I use it
I
> get the following error
> <<ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptio
nHandler:
> Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
SQL
> Server is terminating this process.>>
> I have tried using the debugger on the simplest of stored procedures and
> this error happens without fail every time.
> Any ideas?
> Ta
> Paul