Problem with SQL string using MS Access and OleDbConnection (ASP .NET)

The string concatination below works in the Query builder built-in to MS Access, but when I try it as an OleDbCommand it doesn't work.

SELECT ID, LastName + ', ' + FirstName AS Names FROM AgentNames;

Is there any other ways to return a combination like this using an OleDbCommand?

Thanks,

GrierOriginally posted by grier_allen
The string concatination below works in the Query builder built-in to MS Access, but when I try it as an OleDbCommand it doesn't work.

SELECT ID, LastName + ', ' + FirstName AS Names FROM AgentNames;

Is there any other ways to return a combination like this using an OleDbCommand?

Thanks,

Grier

Shot in the dark here but try [LastName + ',' + FirstName] as Names. If not, I dont know why that won't work.

Problem with SQL server 2000

Hi I have a table called "Member" as given below..

familyID memberID firstName
--- --- -------
0 7 Stuart
0 5 Kasey
0 1 Sally
0 2 Cooper
1 9 Rosemary
2 3 Lisa
2 6 Stephanie
3 4 mandy
3 8 Fisher

I want to create a view, storedProcedure or a Function (whatever is
possible in SQL Server 2000) that returns data that looks something
like this:

familyID member1 member2 member3 member4 (columns can go to..
memberN )
--- ---- ---- -----

0 Stuart Kasey Sally Cooper
1 Rosemary
2 Lisa Stephanie
3 Mandy Fisher

Any help would be greatly appreciated..Rex wrote:

Quote:

Originally Posted by

Hi I have a table called "Member" as given below..
>
familyID memberID firstName
--- --- -------
0 7 Stuart
0 5 Kasey
0 1 Sally
0 2 Cooper
1 9 Rosemary
2 3 Lisa
2 6 Stephanie
3 4 mandy
3 8 Fisher
>
>
I want to create a view, storedProcedure or a Function (whatever is
possible in SQL Server 2000) that returns data that looks something
like this:
>
>
familyID member1 member2 member3 member4 (columns can go to..
memberN )
--- ---- ---- -----
>
0 Stuart Kasey Sally Cooper
1 Rosemary
2 Lisa Stephanie
3 Mandy Fisher

Untested:

select familyID,
max(case when memberCount = 1 then firstName) member1,
max(case when memberCount = 2 then firstName) member2,
-- etc.
from (select familyID,
(select count(*)
from Member m2
where m2.familyID = m.familyID
and m2.memberID <= m.memberID) memberCount
from Member m) MemberCounts
group by familyID|||Rex (rakeshv01@.gmail.com) writes:

Quote:

Originally Posted by

Hi I have a table called "Member" as given below..
>
familyID memberID firstName
--- --- -------
0 7 Stuart
0 5 Kasey
0 1 Sally
0 2 Cooper
1 9 Rosemary
2 3 Lisa
2 6 Stephanie
3 4 mandy
3 8 Fisher
>
>
I want to create a view, storedProcedure or a Function (whatever is
possible in SQL Server 2000) that returns data that looks something
like this:
>
>
familyID member1 member2 member3 member4 (columns can go to..
memberN )
--- ---- ---- -----

If you can set an upper limit of, say, 20, members, you can use the
query that Ed posted. If you need the result set to be dynamic, you
will need to use dynamic SQL, and it is not that fun. Or you invest
in the third-party tool RAC, which is good at this. http://www.rac4sql.net.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Problem with SQL query

Hi All,
I have the following table suppliers and product.
The Supplier table have two columns Supplier_id and Supplier_name.
Below is my data in the supplier table:
Supplier_Id
Supplier_Name
2
New Orleans Cajun Delights
3
Grandma Kelly's homestead
16
Bigfoot Breweries
19
New England Seafood Cannery
5
New Mexico Seafood
4
Indian Spices
My Products table have 3 columns Product_id, Product_name and supplier_id
I have the following data in my products table:
Product_Id
Product_Name
Supplier_Id
4
Chef Anton's Cajun Seasoning
2
5
Chef Anton's Gumbo Mix
2
65
Louisiana Fiery Hot Pepper Sauce
2
66
Louisiana Hot Spice Okra
2
6
Grandma's Boysenberry Spread
3
7
Uncle Bob's Organic Dried Pears
3
8
Northwood's canaberry sauce
3
34
Sasquach Ale
16
35
Steeleye Stout
16
67
Laughing Lumberjack Lager
16
40
Boston Crab Meat
19
41
Jack's New England Clam Chowder
19
75
Chicago Pizza
NULL
22
Indian Hot Sauce
NULL
I want to find the supplier_name of the supplier who is supplying maximum
products.
Can anybody help me with the query?
Thanks,
VinitaWhy not got for the top 5-- though you can for the top 1.
SELECT top 5 supplier_name, count(*)
FROM suppliers,
product
WHERE suppliers.supplier_id = product.supplier_id
group by supplier_name
order by count(*) DESC
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
news:OnhH2Oo6DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Hi All,
> I have the following table suppliers and product.
> The Supplier table have two columns Supplier_id and Supplier_name.
> Below is my data in the supplier table:
>
> Supplier_Id
> Supplier_Name
> 2
> New Orleans Cajun Delights
> 3
> Grandma Kelly's homestead
> 16
> Bigfoot Breweries
> 19
> New England Seafood Cannery
> 5
> New Mexico Seafood
> 4
> Indian Spices
>
>
> My Products table have 3 columns Product_id, Product_name and supplier_id
> I have the following data in my products table:
>
> Product_Id
> Product_Name
> Supplier_Id
> 4
> Chef Anton's Cajun Seasoning
> 2
> 5
> Chef Anton's Gumbo Mix
> 2
> 65
> Louisiana Fiery Hot Pepper Sauce
> 2
> 66
> Louisiana Hot Spice Okra
> 2
> 6
> Grandma's Boysenberry Spread
> 3
> 7
> Uncle Bob's Organic Dried Pears
> 3
> 8
> Northwood's canaberry sauce
> 3
> 34
> Sasquach Ale
> 16
> 35
> Steeleye Stout
> 16
> 67
> Laughing Lumberjack Lager
> 16
> 40
> Boston Crab Meat
> 19
> 41
> Jack's New England Clam Chowder
> 19
> 75
> Chicago Pizza
> NULL
> 22
> Indian Hot Sauce
> NULL
>
>
> I want to find the supplier_name of the supplier who is supplying maximum
> products.
> Can anybody help me with the query?
> Thanks,
> Vinita
>

---

|||Thanks a zillion.
It worked
"Andy Svendsen" <andymcdba1@.NOMORESPAM.yahoo.com> wrote in message
news:uin4tno6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> Why not got for the top 5-- though you can for the top 1.
> SELECT top 5 supplier_name, count(*)
> FROM suppliers,
> product
> WHERE suppliers.supplier_id = product.supplier_id
> group by supplier_name
> order by count(*) DESC
> --
> ****************************************
***************************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@.NOMORESPAM.yahoo.com
> Please remove NOMORESPAM before replying.
> Always keep your antivirus and Microsoft software
> up to date with the latest definitions and product updates.
> Be suspicious of every email attachment, I will never send
> or post anything other than the text of a http:// link nor
> post the link directly to a file for downloading.
> This posting is provided "as is" with no warranties
> and confers no rights.
> ****************************************
***************************
> "Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
> news:OnhH2Oo6DHA.1636@.TK2MSFTNGP12.phx.gbl...
supplier_id
maximum
>

Problem with sp_OACreate

The sp_OACreate fails at nights it works fine at daytime, I checked the
Event Log, and saw below error message for every databases I have.
18278 :
Database log truncated: Database: DBNAME.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:523AA016-9F7B-4CDA-A3DA-809E79084527@.microsoft.com...
> Hi
> You may want to try turning it off for a period and see if the problem
> persists. You need to asses the risk of doing this, such as
> http://support.microsoft.com/defaul...kb;en-us;309422
> John
> "Erdal Akbulut" wrote:
>
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_oa-oz_3enj.asp[/ur
l]
any
ActiveX
Windows
ActiveXHi
I don't think that has anything to do with the error! You may want to check
if there are any scheduled virus scans at the time your error occurs.
John
"Erdal Akbulut" wrote:

> The sp_OACreate fails at nights it works fine at daytime, I checked the
> Event Log, and saw below error message for every databases I have.
> 18278 :
> Database log truncated: Database: DBNAME.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:523AA016-9F7B-4CDA-A3DA-809E79084527@.microsoft.com...
> http://msdn.microsoft.com/library/d...
a-oz_3enj.asp
> any
> ActiveX
> Windows
> ActiveX
>
>|||Hi,
We have a wly scheduled Virus Scan on that machine.sp_OACreate fails more
than once in a w
Thanks,
Erdal
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:24CDDF90-D6DF-4BA7-A02D-84B6F5A5308A@.microsoft.com...
> Hi
> I don't think that has anything to do with the error! You may want to
check
> if there are any scheduled virus scans at the time your error occurs.
> John
> "Erdal Akbulut" wrote:
>
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_oa-oz_3enj.asp[/ur
l]
there
using|||Hi
You may be better migrating to CDO.
John
"Erdal Akbulut" wrote:

> Hi,
> We have a wly scheduled Virus Scan on that machine.sp_OACreate fails mo
re
> than once in a w
> Thanks,
> Erdal
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:24CDDF90-D6DF-4BA7-A02D-84B6F5A5308A@.microsoft.com...
> check
> http://msdn.microsoft.com/library/d...
a-oz_3enj.asp
> there
> using
>
>|||John,
I am not sure if I can use CDOSYS.dll instead of CDONOTS.dll in my VB6
application. Actualy The Server is Win2003 and there was no CDONTS.dll I
copied and registered that dll.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0FBF1B85-A949-45A5-B2FE-B9FB780A4C25@.microsoft.com...
> Hi
> You may be better migrating to CDO.
> John
> "Erdal Akbulut" wrote:
>
more
the
problem
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_oa-oz_3enj.asp[/ur
l]
VB
moved to
create|||Hi
I am still not sure why it runs during the day and not at night using the
same process, therefore to eliminate any incorrect configuration of
cdonts.dll it would be safer to use the native cdo. If that fails then the
issue is probably external to CDO.
John
"Erdal Akbulut" wrote:

> John,
> I am not sure if I can use CDOSYS.dll instead of CDONOTS.dll in my VB6
> application. Actualy The Server is Win2003 and there was no CDONTS.dll I
> copied and registered that dll.
> Thanks.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:0FBF1B85-A949-45A5-B2FE-B9FB780A4C25@.microsoft.com...
> more
> the
> problem
> http://msdn.microsoft.com/library/d...
a-oz_3enj.asp
> VB
> moved to
> create
>
>

Problem with sp_executesql

I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below:
execute sp_executesql N'SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE @.au_lname
',
N'@.au_lname nVarChar',
@.au_lname = N'%Cas%'

but It return all rows regardless of changing condition to any value.

But if i don't use sp_executesql like below:

SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE N'%Cas%'

It's correct!

Can anyone tell me why?

ThanksChange your code as follows:

N'@.au_lname nVarChar', -->>> N'@.au_lname nVarChar(5)',|||Thank you very much for feedback!

problem with SP to return last @@Identity

I need to retun the last inserted Identity value. - The SP below has a synta
x
error on the Set line. How can I fix it?
create procedure stp_GetIdentity
@.e int output
as
SET NOCOUNT ON
Set @.e = Select @.@.Identity
return
go
Thanks,
Rich"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E3FF5947-5ED7-4E0A-BCD7-486D27C51375@.microsoft.com...
>I need to retun the last inserted Identity value. - The SP below has a
>syntax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich
Set @.e = @.@.Identity
or
Select @.e = @.@.Identity
Option 1 is preferred for a single assignment.
Also look up scope_identity() in BOL.|||The offending line should be
SELECT @.e = @.@.IDENTITY
however, why create a stored procedure to get @.@.IDENTITY, when you can just
retrieve its value within a batch using SELECT @.@.IDENTITY?
"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syn
tax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich|||create procedure stp_GetIdentity
@.e int output
as
SET NOCOUNT ON
Select @.e = @.@.Identity
return
go
--This seems to work
"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syn
tax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich|||Thanks. From what I understand Scope_Identity works within a specified scop
e
which I interpret to mean if you insert a row into tbl1 which contains 10
rows in one procedure and also insert a row into tbl2 which contains 700 row
s
in another procedure and you only want to return the Identity value in tbl1
you could use Scope_Identity.
May I ask how Scope_Identity would be implemented in my SP to return the
Identity value of the last inserted row into tbl1?
?
"Raymond D'Anjou" wrote:

> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:E3FF5947-5ED7-4E0A-BCD7-486D27C51375@.microsoft.com...
> Set @.e = @.@.Identity
> or
> Select @.e = @.@.Identity
> Option 1 is preferred for a single assignment.
> Also look up scope_identity() in BOL.
>
>|||"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6E159771-9CB3-45ED-95D1-7EF10E72DFFA@.microsoft.com...
> Thanks. From what I understand Scope_Identity works within a specified
> scope
> which I interpret to mean if you insert a row into tbl1 which contains 10
> rows in one procedure and also insert a row into tbl2 which contains 700
> rows
> in another procedure and you only want to return the Identity value in
> tbl1
> you could use Scope_Identity.
> May I ask how Scope_Identity would be implemented in my SP to return the
> Identity value of the last inserted row into tbl1?
>
set @.a = scope_identity()
scope_identity() has another advantage.
If you have a trigger on a table that inserts a row into another table with
an identity column.
@.@.identity in your stored procedure will return the ID of the last insert,
that is, the one in your trigger.
scope_identity() will return the ID you want.sql

Problem with simple subquery in SQL2005 AND SQL2000.

When I use the simple query with a subquery shown below, this is the error message I get in SQL 2000 AND SQL 2005

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

And here is the query I use:

SELECT docSections.SectionID,

(SELECT docSectionText.colText FROM docSectionText

WHERE (docSections.SectionID = docSectionText.SectionID)

AND (docSectionText.colOrdinal = 1)) AS SecTitle

FROM docSections

Can anyone please let me know what I do wrong here.

Thanks

Gerhard

I can tell you why you get the error. But, without understanding your schema and requirements, I can not give you a solution for what you are trying to do.

The problem is that when you have a subquery in your SELECT it can only return 1 row per row. So, your subquery must be returning multiple rows.

To check try the following queries and see what it returns.

-- This should show you the sectionid that have multiple rows with colOrdinal = 1
SELECT SECTIONID, count(coltext) as rowcount
FROM docSections
WHERE docSectionText.colOrdinal = 1
GROUP BY SectionID
HAVING count(coltext) > 1

-- This should checks if perhaps the identified sections have multiple rows
--but all with same coltext. If first returns rows, but this doesn't,
--then you can add distinct to solve your problems
SELECT SECTIONID, count(distinct coltext) as rowcount
FROM docSections
WHERE docSectionText.colOrdinal = 1
GROUP BY SectionID
HAVING count(distinct coltext) > 1

HTH

|||

Thank You very much.

You were correct. I had 2 doubles in my table.

Gerhard

Problem with rollback statement

Hi,

I have written a store procedure which inserts data into two tables. What I want do is to rollback transaction if the second insert fails. Below is a code.

Does anyone see my error?

Thanks,

poc1010

Create proc AddProducts

@.dcint=null,
@.pcint=null,
@.imagepathvarchar(50)=null,
@.typevarchar(2)=null,
@.descriptionvarchar(1000)=null,
@.gendervarchar(8)=null,
@.productidint=null,
@.pccodevarchar(2)=null,
@.weightvarchar(80)=null,
@.pricemoney=null,
@.activevarchar(1)=null

as

declare @.errorsave int
set @.errorsave=0
declare @.dg int

Begin transaction

insert productdescription(
designercategory,
productcategory,
imagepath,
type,
[description],
gender)
values(@.dc,
@.pc,
@.imagepath,
@.type,
@.description,
@.gender)

if @.@.error <> 0
set @.errorsave=@.@.error

set @.dg = @.@.identity

begin
insert Products(
productid,
designergroup,
designercategory,
productcategory,
pccode,
weight,
price,
active)
values(@.productid,
@.dg,
@.dc,
@.pc,
@.pccode,
@.weight,
@.price,
@.active)

if @.@.error <> 0
set @.errorsave=@.@.error
end

if @.errorsave <> 0
begin
print 'Insert into Products tables failed'
rollback transaction
return -5--Insert into Products tables failed
end

commit transaction
print 'Success'
return 0 --SuccessYou have begins and ends in useless spots. Whats the acual error message?|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin
 declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin
 declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||None of you guys used ELSE. Your BEGIN/END's are a little whacked out. Honestly, I'm against returning in mid procedure if it's not necessary. You can easily follow through the entire procedure using an ELSE, then returning a specified value.|||Pierre,

Thanks for your example. I saw what I was doing wrong. Works great.

Thank you for your help.

poc1010|||That's just personal taste Lee. No real argument either way. Not in this case.|||You're right. That's why I said that I prefer the other way. Didn't say you were wrong, because it works fine.

Problem with Reporting Service SQL 2000 and Sybase

Hi,
The connection to Sybase with OLEDB and ODBC have the same problem when the
query have parameter. The query below don´t execute:
select prod_cod,cod_empresa from estatistica.dbo.zomba
where vida = @.vida
OLEDB error: "An error occurred while executing the query. The given type
name was unrecognized"
versions used: 02.70.0016 and 02.70.0042 (provided by Sybase support)
ODBC error: "Error [HY000][DataDirect][ODBC Sybase wire protocol driver][SQL
Server] must declare variable @.vida"
versions used: 04.10.0049 (provided by Sybase support)
Someone have the same problem?
Thanks,
LandryI work extensively with Sybase. The issue you are seeing is that query
variables for ODBC have to be a ? (unnamed parameter). I suggest you stick
with the ODBC driver. I have had issues with OLEDB.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landry" <landry@.dsai.com.br.NEWS> wrote in message
news:em6ndG7RFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> The connection to Sybase with OLEDB and ODBC have the same problem when
> the
> query have parameter. The query below don´t execute:
> select prod_cod,cod_empresa from estatistica.dbo.zomba
> where vida = @.vida
> OLEDB error: "An error occurred while executing the query. The given type
> name was unrecognized"
> versions used: 02.70.0016 and 02.70.0042 (provided by Sybase support)
> ODBC error: "Error [HY000][DataDirect][ODBC Sybase wire protocol
> driver][SQL
> Server] must declare variable @.vida"
> versions used: 04.10.0049 (provided by Sybase support)
> Someone have the same problem?
> Thanks,
> Landry
>
>|||Hi,
Thanks.
I test ? with OLEDB and ODBC and have the same error!
select prod_cod,cod_empresa from estatistica.dbo.zomba
where vida = ?
I test Stored Procedures with parameters and have the same error.
Landry
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> escreveu na mensagem
news:u%23sw%23m7RFHA.3704@.TK2MSFTNGP12.phx.gbl...
>I work extensively with Sybase. The issue you are seeing is that query
>variables for ODBC have to be a ? (unnamed parameter). I suggest you stick
>with the ODBC driver. I have had issues with OLEDB.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Landry" <landry@.dsai.com.br.NEWS> wrote in message
> news:em6ndG7RFHA.688@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> The connection to Sybase with OLEDB and ODBC have the same problem when
>> the
>> query have parameter. The query below don´t execute:
>> select prod_cod,cod_empresa from estatistica.dbo.zomba
>> where vida = @.vida
>> OLEDB error: "An error occurred while executing the query. The given
>> type
>> name was unrecognized"
>> versions used: 02.70.0016 and 02.70.0042 (provided by Sybase support)
>> ODBC error: "Error [HY000][DataDirect][ODBC Sybase wire protocol
>> driver][SQL
>> Server] must declare variable @.vida"
>> versions used: 04.10.0049 (provided by Sybase support)
>> Someone have the same problem?
>> Thanks,
>> Landry
>>
>>
>|||The best thing to do is as you are doing, first get a query and then move on
to stored procedures. That is the data type of vida?
Also, when are you getting this error? From the data tab clicking on the ! ?
Or from the preview?
Let's concentrate on ODBC. What error do you get with ODBC (it can't be the
same as before because at that point you had this error: must declare
variable @.vida).
I do all my queries from the generic query window. Try that (the button is
to the right of the ... to switch to generic query designer).
Also, what version of Sysbase. I am using 12.5.2 client and have used that
against both an 11.x (I don't remember the exact version) and 12.5.1
servers.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landry" <landry@.dsai.com.br.NEWS> wrote in message
news:e4V1j7BSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Thanks.
> I test ? with OLEDB and ODBC and have the same error!
> select prod_cod,cod_empresa from estatistica.dbo.zomba
> where vida = ?
> I test Stored Procedures with parameters and have the same error.
> Landry
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> escreveu na mensagem
> news:u%23sw%23m7RFHA.3704@.TK2MSFTNGP12.phx.gbl...
>>I work extensively with Sybase. The issue you are seeing is that query
>>variables for ODBC have to be a ? (unnamed parameter). I suggest you stick
>>with the ODBC driver. I have had issues with OLEDB.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Landry" <landry@.dsai.com.br.NEWS> wrote in message
>> news:em6ndG7RFHA.688@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> The connection to Sybase with OLEDB and ODBC have the same problem when
>> the
>> query have parameter. The query below don´t execute:
>> select prod_cod,cod_empresa from estatistica.dbo.zomba
>> where vida = @.vida
>> OLEDB error: "An error occurred while executing the query. The given
>> type
>> name was unrecognized"
>> versions used: 02.70.0016 and 02.70.0042 (provided by Sybase support)
>> ODBC error: "Error [HY000][DataDirect][ODBC Sybase wire protocol
>> driver][SQL
>> Server] must declare variable @.vida"
>> versions used: 04.10.0049 (provided by Sybase support)
>> Someone have the same problem?
>> Thanks,
>> Landry
>>
>>
>>
>|||Hi Bruce,
Thanks, the problem is ODBC version, now work fine with cliente version
12.5.3, the most recent.
The Sybase suport will analyze the OLEDB to correct the error.
Thanks,
Landry
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> escreveu na mensagem
news:urhgPMCSFHA.3336@.TK2MSFTNGP09.phx.gbl...
> The best thing to do is as you are doing, first get a query and then move
> on to stored procedures. That is the data type of vida?
> Also, when are you getting this error? From the data tab clicking on the !
> ? Or from the preview?
> Let's concentrate on ODBC. What error do you get with ODBC (it can't be
> the same as before because at that point you had this error: must declare
> variable @.vida).
> I do all my queries from the generic query window. Try that (the button is
> to the right of the ... to switch to generic query designer).
> Also, what version of Sysbase. I am using 12.5.2 client and have used that
> against both an 11.x (I don't remember the exact version) and 12.5.1
> servers.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Landry" <landry@.dsai.com.br.NEWS> wrote in message
> news:e4V1j7BSFHA.1348@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> Thanks.
>> I test ? with OLEDB and ODBC and have the same error!
>> select prod_cod,cod_empresa from estatistica.dbo.zomba
>> where vida = ?
>> I test Stored Procedures with parameters and have the same error.
>> Landry
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> escreveu na mensagem
>> news:u%23sw%23m7RFHA.3704@.TK2MSFTNGP12.phx.gbl...
>>I work extensively with Sybase. The issue you are seeing is that query
>>variables for ODBC have to be a ? (unnamed parameter). I suggest you
>>stick with the ODBC driver. I have had issues with OLEDB.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Landry" <landry@.dsai.com.br.NEWS> wrote in message
>> news:em6ndG7RFHA.688@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> The connection to Sybase with OLEDB and ODBC have the same problem when
>> the
>> query have parameter. The query below don´t execute:
>> select prod_cod,cod_empresa from estatistica.dbo.zomba
>> where vida = @.vida
>> OLEDB error: "An error occurred while executing the query. The given
>> type
>> name was unrecognized"
>> versions used: 02.70.0016 and 02.70.0042 (provided by Sybase support)
>> ODBC error: "Error [HY000][DataDirect][ODBC Sybase wire protocol
>> driver][SQL
>> Server] must declare variable @.vida"
>> versions used: 04.10.0049 (provided by Sybase support)
>> Someone have the same problem?
>> Thanks,
>> Landry
>>
>>
>>
>>
>

Problem with replace function

Hi,
Please find the below scenario
Original Table
course branch_exist
BY0
UI1
PO1
LI0
MK1
select REPLACE(branch_exist,1,'yes') as branch from university;
displays
course branch_exist
BY0
UIYes
POYes
LI0
MKYes
What i need is
course branch_exist
BYNo
UIYes
POYes
LINo
MKYes
Sql-Server replace function only accepts three arguments, any
suggestions will be greatly welcomed!
Hello,
Use CASE statement...
Thanks
Hari
"meendar" <askjavaprogrammers@.gmail.com> wrote in message
news:1176120159.024299.245830@.l77g2000hsb.googlegr oups.com...
> Hi,
> Please find the below scenario
> Original Table
> course branch_exist
> BY 0
> UI 1
> PO 1
> LI 0
> MK 1
>
> select REPLACE(branch_exist,1,'yes') as branch from university;
> displays
> course branch_exist
> BY 0
> UI Yes
> PO Yes
> LI 0
> MK Yes
>
> What i need is
>
> course branch_exist
> BY No
> UI Yes
> PO Yes
> LI No
> MK Yes
>
> Sql-Server replace function only accepts three arguments, any
> suggestions will be greatly welcomed!
>

Problem with replace function

Hi,

Please find the below scenario

Original Table

course branch_exist
BY0
UI1
PO1
LI0
MK1

select REPLACE(branch_exist,1,'yes') as branch from university;

displays

course branch_exist
BY0
UIYes
POYes
LI0
MKYes

What i need is

course branch_exist
BYNo
UIYes
POYes
LINo
MKYes

Sql-Server replace function only accepts three arguments, any
suggestions will be greatly welcomed!What i need is:

Quote:

Originally Posted by

course branch_exist
BY No
UI Yes
PO Yes
LI No
MK Yes

select
CAST branch_exist
WHEN 1 THEN 'yes'
WHEN 0 'no'
ELSE '?' END as branch
from university;

--
Tom
http://kbupdate.info/ | http://suppline.com/|||Oops, typo happens. Right version is:

select
CASE branch_exist
WHEN 1 THEN 'yes'
WHEN 0 THEN 'no'
ELSE '?'
END as branch
from university;

--
Tom
http://kbupdate.info/ | http://suppline.com/|||On Apr 9, 5:37 pm, "kb" <a...@.kbupdate.infowrote:

Quote:

Originally Posted by

Oops, typo happens. Right version is:
>
select
CASE branch_exist
WHEN 1 THEN 'yes'
WHEN 0 THEN 'no'
ELSE '?'
END as branch
from university;
>
--
Tomhttp://kbupdate.info/|http://suppline.com/

Hi Kb,

Thanks you !

Problem with replace function

Hi,
Please find the below scenario
Original Table
course branch_exist
BY 0
UI 1
PO 1
LI 0
MK 1
select REPLACE(branch_exist,1,'yes') as branch from university;
displays
course branch_exist
BY 0
UI Yes
PO Yes
LI 0
MK Yes
What i need is
course branch_exist
BY No
UI Yes
PO Yes
LI No
MK Yes
Sql-Server replace function only accepts three arguments, any
suggestions will be greatly welcomed!Hello,
Use CASE statement...
Thanks
Hari
"meendar" <askjavaprogrammers@.gmail.com> wrote in message
news:1176120159.024299.245830@.l77g2000hsb.googlegroups.com...
> Hi,
> Please find the below scenario
> Original Table
> course branch_exist
> BY 0
> UI 1
> PO 1
> LI 0
> MK 1
>
> select REPLACE(branch_exist,1,'yes') as branch from university;
> displays
> course branch_exist
> BY 0
> UI Yes
> PO Yes
> LI 0
> MK Yes
>
> What i need is
>
> course branch_exist
> BY No
> UI Yes
> PO Yes
> LI No
> MK Yes
>
> Sql-Server replace function only accepts three arguments, any
> suggestions will be greatly welcomed!
>

Problem with replace function

Hi,
Please find the below scenario
Original Table
course branch_exist
BY 0
UI 1
PO 1
LI 0
MK 1
select REPLACE(branch_exist,1,'yes') as branch from university;
displays
course branch_exist
BY 0
UI Yes
PO Yes
LI 0
MK Yes
What i need is
course branch_exist
BY No
UI Yes
PO Yes
LI No
MK Yes
Sql-Server replace function only accepts three arguments, any
suggestions will be greatly welcomed!Hello,
Use CASE statement...
Thanks
Hari
"meendar" <askjavaprogrammers@.gmail.com> wrote in message
news:1176120159.024299.245830@.l77g2000hsb.googlegroups.com...
> Hi,
> Please find the below scenario
> Original Table
> course branch_exist
> BY 0
> UI 1
> PO 1
> LI 0
> MK 1
>
> select REPLACE(branch_exist,1,'yes') as branch from university;
> displays
> course branch_exist
> BY 0
> UI Yes
> PO Yes
> LI 0
> MK Yes
>
> What i need is
>
> course branch_exist
> BY No
> UI Yes
> PO Yes
> LI No
> MK Yes
>
> Sql-Server replace function only accepts three arguments, any
> suggestions will be greatly welcomed!
>

Problem with ProClarity analystics 6 for SQL Server 2005 reporting services

Hi, all here,

Thank you very much for your kind attention.

I'v got a problem with Microsoft ProClarity for SQL Server 2005 reporting services as below:

Created a 3-D effect reports (which are views in ProClarity professional ) and exported it into SQL Server 2005 reporting services, but the result in SQL Server 2005 reporting serivces is totally different, no 3-D result, and also the color of the report has totally changed, for better result in SQL Server 2005, I have to manually modify the reporting file in reporting services.

Another problem is: the views created in ProClarity always change colors (the colors I think are aweful) after exported into SQL Server 2005 reporting services.

Can any expert for that give me any adives for that? What can we try to solve this problem?

Thanks a lot in advance for any guidance and advices for that.

With best regards,

Yours sincerely,

I recommend http://www.proclarity.com/services/support.asp for these problems.

Regards

Thomas Ivarsson

|||

Thanks a lot.

With best regards,

Problem with ProClarity analystics 6 for SQL Server 2005 reporting services

Hi, all here,

Thank you very much for your kind attention.

I'v got a problem with Microsoft ProClarity for SQL Server 2005 reporting services as below:

Created a 3-D effect reports (which are views in ProClarity professional ) and exported it into SQL Server 2005 reporting services, but the result in SQL Server 2005 reporting serivces is totally different, no 3-D result, and also the color of the report has totally changed, for better result in SQL Server 2005, I have to manually modify the reporting file in reporting services.

Another problem is: the views created in ProClarity always change colors (the colors I think are aweful) after exported into SQL Server 2005 reporting services.

Can any expert for that give me any adives for that? What can we try to solve this problem?

Thanks a lot in advance for any guidance and advices for that.

With best regards,

Yours sincerely,

I recommend http://www.proclarity.com/services/support.asp for these problems.

Regards

Thomas Ivarsson

|||

Thanks a lot.

With best regards,

Problem with parameterized SELECT statement

I'm trying to use a parameterized SELECT statement, but I must not have it right - the code below gives this compile error: System.Data.SqlClient.SqlException: Must declare the scalar variable "@.UserID".

string

strUserID = (string)Session["UserID"];string strSelectRatings ="SELECT [CommentID], [GameID], [UserID], [Rating], LEFT(Comment,40) as Comment FROM [Comments] WHERE [UserID] = @.UserID";SqlConnection myConnection =newSqlConnection("...");SqlCommand myCommand =newSqlCommand(strSelectRatings, myConnection);

myCommand.Parameters.Add(

"@.UserID", strUserID);

MySqlDataSource.SelectCommand = strSelectRatings;

GridView1.DataBind();

Add the parameters to your SqlDataSource object

MySqlDataSource.SelectParameters.Add("@.UserID", strUserID);

problem with OUTPUT params in Stored procedure

Hi all!
Running the code below in SQL-analyzeer (or through dbExpress) results in NULL.
As one might guess I would like the result to be 1. What is wrong? I.e, why
wont the result of the SP come back to the caller?

CREATE PROCEDURE test
@.val INTEGER OUT
AS
SELECT @.val = 1
GO

DECLARE @.val INTEGER
EXEC test @.val
SELECT @.valEXEC test @.val OUTPUT

Simon

problem with output parameter stored procedure

My stored procedure below compiled - not sure if it is even correct though.
I have to get the sum of a totalpaid column from one table and get the sum o
f
a totalpaid column from a second table. I need to return the difference of
these sums.
---
CREATE PROCEDURE [stp_SumDiffTotalPaid]
@.SumDiff decimal output
AS
declare @.a decimal, @.b decimal
Select @.a = sum(pd_totl_amt) from tblncanschd
Select @.b = sum(totalpaid) from tblncalnonschedemipaid
Set @.SumDiff = @.a - @.b
Return
GO
----
-
Here is how I call my sp from query analyzer:
declare @.a decimal
stp_sumdiffTotalpaid, @.sumdiff = @.a output
This is not working. Any suggestions appreciated how to get this to work or
if there is a simpler way to do this.
Thanks,
RichI figured it out
Declare @.a
Excec stp_sumdiffTotalpaid @.a output
or
Excec stp_sumdiffTotalpaid @.sumdiff = @.a output
print @.a
I was missing Execute
I guess I don't need the comma after the sp either.
"Rich" wrote:

> My stored procedure below compiled - not sure if it is even correct though
.
> I have to get the sum of a totalpaid column from one table and get the sum
of
> a totalpaid column from a second table. I need to return the difference o
f
> these sums.
> ---
> CREATE PROCEDURE [stp_SumDiffTotalPaid]
> @.SumDiff decimal output
> AS
> declare @.a decimal, @.b decimal
> Select @.a = sum(pd_totl_amt) from tblncanschd
> Select @.b = sum(totalpaid) from tblncalnonschedemipaid
> Set @.SumDiff = @.a - @.b
> Return
> GO
> ----
--
> Here is how I call my sp from query analyzer:
> declare @.a decimal
> stp_sumdiffTotalpaid, @.sumdiff = @.a output
> This is not working. Any suggestions appreciated how to get this to work
or
> if there is a simpler way to do this.
> Thanks,
> Rich