Showing posts with label calling. Show all posts
Showing posts with label calling. Show all posts

Thursday, March 22, 2012

Error 8120 when selecting MAX(...)

Hi Folks,

I have a very strange problem with the MSSQL JDBC drivers (SP1).
I am calling executeQuery() on a very simple query:

SELECT MAX(fulltext_id) FROM contractor

I am getting an error 8120 (column is not contained in an aggregate-- see stack trace below). It thinks that 'id' is part of the column list, yet it is not.

Now, the strange thing is, when I run SQL Profiler, I see this line:

exec sp_cursoropen @.P1 output, N'SELECT MAX(fulltext_id) , * FROM contractor', @.P2 output, @.P3 output, @.P4 output

It seems that someone (the driver?) added a comma and a "*" after the MAX(fulltext_id) clause! What gives?

Thanks much,
Steve

===

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Column 'contractor.id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest. openCursor(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.execute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQuery Internal(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQuery (Unknown Source)I don't know why the Java is throwing that * in there, but that is definitely what is causing the problem. That is not a valid SQL statement.

blindman|||Originally posted by blindman
I don't know why the Java is throwing that * in there, but that is definitely what is causing the problem. That is not a valid SQL statement.

blindman

Actually I found what was wrong. It appears I was opening the ResultSet as an updatable cursor. The SQL Server driver apparently tacks on a "*" to make the query select all rows (so any row in the query can be updated?)

I am still kinda confused, but opening the cursor as read-only fixed the problem... :?|||Originally posted by sehugg
Actually I found what was wrong. It appears I was opening the ResultSet as an updatable cursor. The SQL Server driver apparently tacks on a "*" to make the query select all rows (so any row in the query can be updated?)

I am still kinda confused, but opening the cursor as read-only fixed the problem... :?

Obviously a bug in the JDBC driver.

Friday, February 24, 2012

Error 2908 when calling sqlncli for install package on Vista

I'm trying to run an install on Vista (the install runs fine on every other
platform)
I have a Custom Action to run the sqlncli.msi install from within our
install.
If I try to run sqlncli.msi on its own then it run correctly.
I am running our install using the option "Run as administrator"
However when the sqlncli starts running I get an error 2908 which is
something about not being able to update the registry.
Any ideas what I need to do to get this install to work in Vista ?
Hi,
This is a Visual Studio Setup setup project issue. I noticed that you had
post the same issue in the community microsoft.public.platformsdk.msi.
Other support engineer there will assist you with this issue further.
Please post back under that post.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||This has nothing to do with Visual Studio (yes I know there are lots of hits
if you do a search for 2908)
This is on a CLEAN install of Vista and I'm trying to create a package for
my application which needs the sqlncli
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:W3nxNPglHHA.3352@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> This is a Visual Studio Setup setup project issue. I noticed that you had
> post the same issue in the community microsoft.public.platformsdk.msi.
> Other support engineer there will assist you with this issue further.
> Please post back under that post.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
|||Hi Michael,
Thanks for your response.
I noticed that if you separately run sqlncli.msi, it would succeed, but if
you run it from your setup program, it would fail. We think that it is a
Windows setup issue on Vista and my colleague Lina had started to assist
you on your post at the community microsoft.public.platformsdk.msi. Please
feel free to post back under that post and she would work with you for
further assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Error 2908 when calling sqlncli for install package on Vista

I'm trying to run an install on Vista (the install runs fine on every other
platform)
I have a Custom Action to run the sqlncli.msi install from within our
install.
If I try to run sqlncli.msi on its own then it run correctly.
I am running our install using the option "Run as administrator"
However when the sqlncli starts running I get an error 2908 which is
something about not being able to update the registry.
Any ideas what I need to do to get this install to work in Vista ?Hi,
This is a Visual Studio Setup setup project issue. I noticed that you had
post the same issue in the community microsoft.public.platformsdk.msi.
Other support engineer there will assist you with this issue further.
Please post back under that post.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||This has nothing to do with Visual Studio (yes I know there are lots of hits
if you do a search for 2908)
This is on a CLEAN install of Vista and I'm trying to create a package for
my application which needs the sqlncli
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:W3nxNPglHHA.3352@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> This is a Visual Studio Setup setup project issue. I noticed that you had
> post the same issue in the community microsoft.public.platformsdk.msi.
> Other support engineer there will assist you with this issue further.
> Please post back under that post.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hi Michael,
Thanks for your response.
I noticed that if you separately run sqlncli.msi, it would succeed, but if
you run it from your setup program, it would fail. We think that it is a
Windows setup issue on Vista and my colleague Lina had started to assist
you on your post at the community microsoft.public.platformsdk.msi. Please
feel free to post back under that post and she would work with you for
further assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Sunday, February 19, 2012

error 241: Syntax error converting datetime from character string

Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:

CREATE proc CP_Cashbox @.mID varchar,@.startdate datetime,@.enddate datetime
as
set dateformat dmy
go
declare @.startdate as varchar
declare @.enddate as varchar

--print "query aan het uitvoeren"

select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@.mID' and njdate between '@.startdate' and '@.enddate'
GO

Thanx in front
CyaOriginally posted by Wimmo
Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:

CREATE proc CP_Cashbox @.mID varchar,@.startdate datetime,@.enddate datetime
as
set dateformat dmy
go
declare @.startdate as varchar
declare @.enddate as varchar

--print "query aan het uitvoeren"

select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@.mID' and njdate between '@.startdate' and '@.enddate'
GO

Thanx in front
Cya

Simple ... just run this command

sp_helptext cp_cashbox

and you will understand.|||Thanx, i tried what you said but it returns the sp code,
which i edited to

CREATE proc CP_Cashbox @.mID varchar,@.startdate datetime,@.enddate datetime
as


select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@.mID' and njdate between '@.startdate' and '@.enddate'

but when i exec it now it returns with the same error as before

the exec command i use is:

exec CP_cashbox @.mID = '10001', @.startdate = '01-01-2000', @.enddate = '01-01-2004'
go

can you tell me what i do wrong?

Thanx
Regards Wim|||Originally posted by Wimmo
Thanx, i tried what you said but it returns the sp code,
which i edited to

CREATE proc CP_Cashbox @.mID varchar,@.startdate datetime,@.enddate datetime
as


select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@.mID' and njdate between '@.startdate' and '@.enddate'

but when i exec it now it returns with the same error as before

the exec command i use is:

exec CP_cashbox @.mID = '10001', @.startdate = '01-01-2000', @.enddate = '01-01-2004'
go

can you tell me what i do wrong?

Thanx
Regards Wim

Sorry ... should have seen it first time around

select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = @.mID and njdate between @.startdate and @.enddate|||thanx that solves the error,
but the strangest thing happened,
when i exec the sp wit the next code:

exec CP_cashbox @.mID = '10001',@.startdate ='01-01-2000',@.enddate = '01-01-2004'

it returns with a Null value

now when i make a query with the same values it returns the correct value. the query i use is :

select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '10001' and njdate between '01-01-2000' and '01-01-2004'

What am i doing wrong

Thanx for your precious time
Greet Wim|||try this :

select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = @.mID and convert(varchar,njdate,121) between convert(varchar,@.startdate,121) and convert(varchar,@.enddate,121)|||Originally posted by Enigma
try this :

select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = @.mID and convert(varchar,njdate,121) between convert(varchar,@.startdate,121) and convert(varchar,@.enddate,121)


it returns the annoying NULL value again|||Can you paste the DDL of the table ...

CREATE proc CP_Cashbox @.mID varchar(x),@.startdate datetime,@.enddate datetime

where x is the width of the machine_id column in your table|||Originally posted by Enigma
Can you paste the DDL of the table ...

CREATE proc CP_Cashbox @.mID varchar(x),@.startdate datetime,@.enddate datetime

where x is the width of the machine_id column in your table

What is DDL?
the with of the machin_id column is 10|||So .. this should work for you

CREATE proc CP_Cashbox
@.mID varchar(10),
@.startdate datetime,
@.enddate datetime
as
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where
machineID = @.mID
and convert(varchar,njdate,121)
between convert(varchar,@.startdate,121)
and convert(varchar,@.enddate,121)|||Thanx alot man i inserted the with of the machine_id column and now it is working. Thanx to your help i really lose some stress today.

Greet Wim:) :) :cool:|||DBA's dont take stress .. they are some of the coolest persons I have seen ... i personally havent seen a dba fuming in public.|||You are completely right, and i am kinda new to this stuf so i am in the proces of becoming on of the coolest!|||Any time you need help you can rely on some of the best dba's i ve known on this site ... especially ... Brett Kaiser, blindman, rnealjr and satya

i myself rely a lot on this site in emergencies|||Originally posted by Enigma
Any time you need help you can rely on some of the best dba's i ve known on this site ... especially ... Brett Kaiser, blindman, rnealjr and satya

i myself rely a lot on this site in emergencies

Thanx for the tip, i think i gonna need some more help in the near future.