[SQL Server 2000 Driver for JDBC]Must declare the variable '@P7where'

1) I'm using MS Sql Server 2000 JDBC driver at WSAD5.1.1.
2) When I try to update the database in my DAO by using:
ps.executeUpdate();
3) Server throws the exception:
java.sql.SQLException: [Microsoft][SQL Server 2000 Driver for
JDBC][SQL Server]Must declare the variable '@.P7where'.
Anything is wrong with the MS JDBC driver with IBM WSAD? shall I set
something somewhere?
Experts, please help!
Show the JDBC code that creates the statement, sets the parameters, and calls the execute().
nauna wrote:
> 1) I'm using MS Sql Server 2000 JDBC driver at WSAD5.1.1.
> 2) When I try to update the database in my DAO by using:
> ps.executeUpdate();
> 3) Server throws the exception:
> java.sql.SQLException: [Microsoft][SQL Server 2000 Driver for
> JDBC][SQL Server]Must declare the variable '@.P7where'.
> Anything is wrong with the MS JDBC driver with IBM WSAD? shall I set
> something somewhere?
> Experts, please help!

[Execute SQL Task] Error: An error occurred while assigning a value to variable...

hi chaps

i m getting the following ERROR:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "JDETimezone": "Unable to find column Timezone in the result set.".

i know what the problem is i.e. no row is returned then what is the problem

here you are.... i want to it work... strange... ok i explain...

actully i have some processign to do with variable JDETimezone even no row is returned.... can u tell me the alternative to do the follwing task...

I want to retrieve a record from some table and do some processing and if no row is present or returned then i want to do seperate processing.... can ne one help me out ?

regards,

Anas

That error usually occurs when you either don't have a column in your select statement named "Timezone", or you didn't name the resultset Timezone. You may want to check your settings.

If you want to do separate processing when no rows are returned then in the Error Flow redirect the rows instead of Failing or Ignoring them.

|||

thanx for your reply

but i sure I have both, column name Timezone in Select statement as well as in Resultset...

problem is that it doesnt return any row and causes error...Sean you are right I can use Error flow but the problem that i dont want any of my task to fail becuase i m doing error handling by Task failure event which then email the failure information to admin group

does ne one have solution of my problem?

regards,

Anas

|||

One way to implement different processing logic based on the result set row count is by having a Execute SQL task that counts the total number of rows returned by a query and assign the count to a variable which can later be used to determine the control flow.

See http://msdn2.microsoft.com/en-US/library/ms140153.aspx

[Execute SQL Task] Error: An error occurred while assigning a value to variable

hello
I have a problem with Sql task
when sql task tried to assing a value to my variable I have this error ""La valeur n'est pas comprise dans la plage attendue."
I'm using ODBC connexion for a csv file

someone can help me ?

thanks
It might help if you translated the error message to english.|||

we can translate as " the value is not included in the correct space " or something like that

|||

Take a look at this - it explains how to set up the Execute SQL task for retrieving values.

http://www.sqlis.com/58.aspx

[?] User's global variables

Hello,
Is it possible to define a global variable within a report? It has to be
populated in one DataSet and accessed from other DataSets.
Thanks,
Lucy.Related previous post:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=d0f4d02b-b032-47e8-8318-169052421237.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:uad4evlaEHA.716@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Is it possible to define a global variable within a report? It has to be
> populated in one DataSet and accessed from other DataSets.
> Thanks,
> Lucy.
>|||Probably not ;-(
How can I populate such variable (declared in custom code) in a DataSet?
Thanks,
Lucy.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:%23gh%23zcnaEHA.2056@.TK2MSFTNGP12.phx.gbl...
> Related previous post:
>
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=d0f4d02b-b032-47e8-8318-169052421237.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:uad4evlaEHA.716@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > Is it possible to define a global variable within a report? It has to be
> > populated in one DataSet and accessed from other DataSets.
> >
> > Thanks,
> > Lucy.
> >
> >
>

[.NET and SQL Server 2000] Explicit order Insert

My current project, which I am programming in .NET, requires me to insert a
variable number of rows, which make up a set, in a specific order. A
collection of sets that are inserted one after the other is a batch. Rows in
each set MUST be kept together, and sets in each batch MUST be kept together.
What is the best way to implement this, both on the .NET side and also the
SQL Server side? Should I lock the table from inserts and updates (updating
won't be a problem, but inserting will be) before I start inserting rows? How
would I implement a system that would roll back all the inserts that have
occured in that batch if an error occurs?
Thank you very much,
Yohan MacDonaghLooking more into it, it looks like the best way is to use the DataSet and
DataAdapter objects in .NET.
Can anyone answer this, however: when a datasource is being updated via a
DataAdapter, is the table locked from inserts during the update?
"Yohan" wrote:
> My current project, which I am programming in .NET, requires me to insert a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows in
> each set MUST be kept together, and sets in each batch MUST be kept together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates (updating
> won't be a problem, but inserting will be) before I start inserting rows? How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
> Thank you very much,
> Yohan MacDonagh|||"Yohan" <Yohan@.discussions.microsoft.com> wrote in message
news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> My current project, which I am programming in .NET, requires me to insert
> a
> variable number of rows, which make up a set, in a specific order. A
> collection of sets that are inserted one after the other is a batch. Rows
> in
> each set MUST be kept together, and sets in each batch MUST be kept
> together.
> What is the best way to implement this, both on the .NET side and also the
> SQL Server side? Should I lock the table from inserts and updates
> (updating
> won't be a problem, but inserting will be) before I start inserting rows?
> How
> would I implement a system that would roll back all the inserts that have
> occured in that batch if an error occurs?
A subset of rows must be defined in terms of shared column values. So give
each row a BatchID and a SetID.
David|||No such thing as an ordered INSERT. What you need to do is add a batch
number or othetr identifier to tell you what batches belong together.
--
David Portas
SQL Server MVP
--|||Unfortuantly, I cannot. I am limited by an existing (and very old) data
schema. There are no relationships. Each property of an object in .NET is a
new row in this schema (very weird, I know).
"David Browne" wrote:
> "Yohan" <Yohan@.discussions.microsoft.com> wrote in message
> news:A0C46B5C-6B07-46F1-9203-D3B4EDDD341F@.microsoft.com...
> > My current project, which I am programming in .NET, requires me to insert
> > a
> > variable number of rows, which make up a set, in a specific order. A
> > collection of sets that are inserted one after the other is a batch. Rows
> > in
> > each set MUST be kept together, and sets in each batch MUST be kept
> > together.
> >
> > What is the best way to implement this, both on the .NET side and also the
> > SQL Server side? Should I lock the table from inserts and updates
> > (updating
> > won't be a problem, but inserting will be) before I start inserting rows?
> > How
> > would I implement a system that would roll back all the inserts that have
> > occured in that batch if an error occurs?
> A subset of rows must be defined in terms of shared column values. So give
> each row a BatchID and a SetID.
> David
>
>|||In that case please explain what you mean by a batch being "kept
together". Are you referring to an IDENTITY column here? Please post
DDL and sample data so that we can understand the problem:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--

@variable in SELECT ... WHERE ... IN clause

Is there a way to create a query that can be like:

DECLARE @.group_id_list varchar(100)

SET @.group_id_list='100,101,150'

SELECT * FROM abc WHERE abc_id IN (@.group_id_list)

I get the error "Syntax error converting the varchar value '100,101,150' to a column of data type int.

Do I need to resort to a dynamic SQL statement?

You do.|||Not if you don't want to. In many cases I prefer to use a udf that I've created that takes a comma-delimited varchar and returns a table. Then you can either join on the table to limit your results, or you can use IN (SELECT id FROM Split(@.param,DEFAULT) alias1) in your where clause. The second param into my Split UDF is what the separator is (default is comma).|||

Thank you. I actually ended up doing something similar (never thought of using a UDF):

DECLARE @.groups TABLE (group_id int)

I ran a while loop inserting the values into the @.groups table, then used:

SELECT * FROM abc WHERE abc_id IN (SELECT group_id FROM @.groups)

I don't know if it's efficient, but my dynamic SQL statement was going to exceed 14k in length!

@local variable (newbie)

I am trying to run a query.

Declare ColNames_Csr Cursor
-- Open Cursor that contains all column names
for
Select Column_Name from Information_Schema.columns where Table_Name = @.TableName

Open ColNames_csr

Fetch Next From ColNames_csr into @.FieldName

While ...

Begin
.
.
.
Select @.FieldValue = (Select @.FieldName from Contacts)
Print @.FieldValue
.
.
.
end

@.SomeValue always returns the FieldName (EMail) not @.FieldName (name@.somewhere.com).

I am trying to roll through all the columns and see what the value is for @.fieldname

What am i missing?

Thanks
LJOriginally posted by LittleJonny
I am trying to run a query.

Declare ColNames_Csr Cursor
-- Open Cursor that contains all column names
for
Select Column_Name from Information_Schema.columns where Table_Name = @.TableName

Open ColNames_csr

Fetch Next From ColNames_csr into @.FieldName

While ...

Begin
.
.
.
Select @.FieldValue = (Select @.FieldName from Contacts)
Print @.FieldValue
.
.
.
end

@.SomeValue always returns the FieldName (EMail) not @.FieldName (name@.somewhere.com).

I am trying to roll through all the columns and see what the value is for @.fieldname

What am i missing?

Thanks
LJ

Query below returns value of @.FieldName - no field in table. Think about dynamic query ...

(Select @.FieldName from Contacts)|||Dynamic Query? Im not sure I understand.

@.FieldName = 'Name'

Select @.FieldValue = (Select @.FieldName from Contacts where contactid = 32)

Print @.FieldValue

Always Prinst "Name"

How do I get the data in Contacts.@.FieldName?

Thanks
LJ|||Originally posted by LittleJonny
Dynamic Query? Im not sure I understand.

@.FieldName = 'Name'

Select @.FieldValue = (Select @.FieldName from Contacts where contactid = 32)

Print @.FieldValue

Always Prinst "Name"

How do I get the data in Contacts.@.FieldName?

Thanks
LJ

Dynamic query is something like this:

create table test(id int, code varchar(10))
go
insert test values(1,'A')
insert test values(2,'B')
insert test values(3,'C')
go
create proc retvalue(@.sql varchar(8000),@.result varchar(50) output)
as
declare @.res varchar(50)
create table #tmp(res varchar(50))
insert #tmp exec(@.sql)
select @.result=res from #tmp
return
go
declare @.res varchar(50),@.sql varchar(8000),@.field varchar(50)
set @.field='code'
set @.sql='select '+@.field+' from test'
exec retvalue @.sql,@.res output
select @.res

@@sqlstatus error

Is @.@.sqlstatus not a global variable. Why am I getting this error.

Code Snippet

declare @.obj_type varchar(50);

declare @.obj_name varchar(50);

declare @.schema varchar(100);

select @.schema = 'new';

declare schema_object cursor for

select obj.type, obj.name

from sys.objects obj join sys.schemas s on (s.schema_id = obj.schema_id)

where s.name = @.schema

open schema_object

fetch schema_object into @.obj_type, @.obj_name;

while (@.@.sqlstatus = 0)

begin

fetch schema_object into @.obj_type, @.obj_name;

end

close schema_object

Msg 137, Level 15, State 2, Line 14

Must declare the scalar variable "@.@.sqlstatus".

I think u must use @.@.Fetch_Status instead of @.@.sqlstatus but I'm not sure about that

just give a try

@@servername

Hi,
The variable @.@.servername is not returning Machine
Name..is there a way to update the @.@.servername to reflect
actual machine name...
JohnHi,
Execute the below command:-
sp_addserver 'servername',local
Before adding the server check the server name using
sp_helpserver
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
> Hi,
> The variable @.@.servername is not returning Machine
> Name..is there a way to update the @.@.servername to reflect
> actual machine name...
> John|||Hi.
Thanks for your Input.
John
>--Original Message--
>Hi,
>Execute the below command:-
>sp_addserver 'servername',local
>Before adding the server check the server name using
>sp_helpserver
>Thanks
>Hari
>MCDBA
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
reflect[vbcol=seagreen]
>
>.
>|||Alternatively, if you are using SQL Server 2000, you can use the
SERVERPROPERTY().
So,
select SERVERPROPERTY('machineName'), SERVERPROPERTY('serverName')) will
give you the machine name and the instance name respectively
Al
"Hari Prasad" wrote:

> Hi,
> Execute the below command:-
> sp_addserver 'servername',local
> Before adding the server check the server name using
> sp_helpserver
> Thanks
> Hari
> MCDBA
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
>
>

@@servername

Hi,
The variable @.@.servername is not returning Machine
Name..is there a way to update the @.@.servername to reflect
actual machine name...
John
Hi,
Execute the below command:-
sp_addserver 'servername',local
Before adding the server check the server name using
sp_helpserver
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
> Hi,
> The variable @.@.servername is not returning Machine
> Name..is there a way to update the @.@.servername to reflect
> actual machine name...
> John
|||Hi.
Thanks for your Input.
John[vbcol=seagreen]
>--Original Message--
>Hi,
>Execute the below command:-
>sp_addserver 'servername',local
>Before adding the server check the server name using
>sp_helpserver
>Thanks
>Hari
>MCDBA
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
reflect
>
>.
>
|||Alternatively, if you are using SQL Server 2000, you can use the
SERVERPROPERTY().
So,
select SERVERPROPERTY('machineName'), SERVERPROPERTY('serverName')) will
give you the machine name and the instance name respectively
Al
"Hari Prasad" wrote:

> Hi,
> Execute the below command:-
> sp_addserver 'servername',local
> Before adding the server check the server name using
> sp_helpserver
> Thanks
> Hari
> MCDBA
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
>
>

@@servername

Hi,
The variable @.@.servername is not returning Machine
Name..is there a way to update the @.@.servername to reflect
actual machine name...
JohnHi,
Execute the below command:-
sp_addserver 'servername',local
Before adding the server check the server name using
sp_helpserver
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
> Hi,
> The variable @.@.servername is not returning Machine
> Name..is there a way to update the @.@.servername to reflect
> actual machine name...
> John|||Hi.
Thanks for your Input.
John
>--Original Message--
>Hi,
>Execute the below command:-
>sp_addserver 'servername',local
>Before adding the server check the server name using
>sp_helpserver
>Thanks
>Hari
>MCDBA
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
>> Hi,
>> The variable @.@.servername is not returning Machine
>> Name..is there a way to update the @.@.servername to
reflect
>> actual machine name...
>> John
>
>.
>|||Alternatively, if you are using SQL Server 2000, you can use the
SERVERPROPERTY().
So,
select SERVERPROPERTY('machineName'), SERVERPROPERTY('serverName')) will
give you the machine name and the instance name respectively
Al
"Hari Prasad" wrote:
> Hi,
> Execute the below command:-
> sp_addserver 'servername',local
> Before adding the server check the server name using
> sp_helpserver
> Thanks
> Hari
> MCDBA
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:67a601c4837b$29ce52a0$a301280a@.phx.gbl...
> > Hi,
> >
> > The variable @.@.servername is not returning Machine
> > Name..is there a way to update the @.@.servername to reflect
> > actual machine name...
> >
> > John
>
>

@@RowCount output param

The following stored procedure sets a value for the @.@.RowCount global variable.

How do I make use of it in the Data Access Layer?

When I set the SPROC as the source for the object, the value numberRows does not appear to be an option. In the end I just want to set the value of @.@.RowCount to a Label.Text

What should I do?

ALTER PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime@.selectDatedatetime ,@.selectCountryInt ASSELECT DISTINCT confignameFROM ModelRequestsJOIN CC_host.dbo.usr_cmcAs t2ON t2.user_id = ModelRequests.usernameJOIN CountriesONCountries.Country_Short = t2.countryWHERE RequestDateTime >= @.selectDateand RequestDateTime <dateadd(dd,1, @.selectDate)AND configname <>''AND interfacenameLIKE'%DOWNLOAD%'AND result = 0AND Country_ID = @.selectCountryORDER BY confignameSELECT@.@.RowCountAs numberRowsGO

try this:

declare @.MyRowCounter int
select @.MyRowCounter = @.@.RowCount as numberRows

|||

This will work as written, but what you get back is an answer set with 1 row and 1 column, you would use a datareader to read it.

A perhaps better -- or at least clearer -- way is to store the number in an output variable. Then, when you set up the call to execute the proc you declare the parameter direction as OUTPUT (this link has an example --http://www.msdner.com/forum/thread445973.html)

ALTER PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime
@.selectDatedatetime
,@.selectCountryInt

,@.numberRows int OUTPUT
AS

SELECT DISTINCT confignameFROM ModelRequestsJOIN
CC_host.dbo.usr_cmcAs t2ON
t2.user_id = ModelRequests.usernameJOIN
CountriesON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @.selectDateand RequestDateTime <dateadd(dd,1, @.selectDate)
AND configname <>''AND interfacenameLIKE'%DOWNLOAD%'AND result = 0AND Country_ID = @.selectCountry
ORDER BY configname
SELECT @.numberRows=@.@.rowcount