Database

SQL Query Problems [modified]

6-Jul-06 20:29

Although I've not had a chance to use it yet, isn't that what the new XML datatype is for?

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

teejayem6-Jul-06 14:23

teejayem

6-Jul-06 14:23

I'm having a couple of problems with SQL Queries. I am running a Query

SELECT * from [DATABASE] WHERE DES='My'

And i get this error: The data types text and varchar are incompatible in the equal to operator.

What data type Should i set my column to so i can make it searchable?

Don't be overcome by evil, but overcome evil with good

-- modified at 20:23 Thursday 6th July, 2006

Re: SQL Query Problems

teejayem6-Jul-06 15:08

teejayem

6-Jul-06 15:08

Nevermind i got it. i changed it to varchar(max) and it worked fine.

Don't be overcome by evil, but overcome evil with good

Typed DataRow and query with more then 1 table

SlavaFromUkraine6-Jul-06 12:09

SlavaFromUkraine

6-Jul-06 12:09

Hi,

I have 2 tables tbl_Member and tbl_Community, they are related by primary/foreign keys.
I access them using SPs. So I created TableAdapters for SPs which return rows from these tables (only). It generated typed MemberRow and ComunityRow for me.

Now I have created SP which joins these 2 tables and returns me all columns from tbl_Member and one from tbl_Community.

I cannot add this SP as a query to MemberTableAdapter which I use to get records from tbl_Membe because this new field (from tbl_Community) does not fit into the existing XML schema.

How can I use this SP ? I would probably need more queries which return columns from joined tables.

How do you "typify" such queries ? It seems like the same problem would have arised if I created custom business objects and not used typed DataSets, I could have Member and Community objects but not something in between.

Thanks a lot,

Slava

No of Databases

Uma Kameswari6-Jul-06 1:25

Uma Kameswari

6-Jul-06 1:25

Hi,
How to find the number of databases in Oracle database?
Regards,
Uma

Re: No of Databases

mr_lasseter6-Jul-06 16:42

mr_lasseter

6-Jul-06 16:42

What are you trying to do?

how to resolve nesting level error in SQL

karimkamal5-Jul-06 17:43

karimkamal

5-Jul-06 17:43

simply i created the following stored procedure and it gives me the following error:
Server: Msg 217, Level 16, State 1, Procedure SelectRecipient, Line 6
Maximum stored procedure, function, trigger, or view nesting level exceeded

the code is te following:

CREATE PROCEDURE SelectRecipient
AS
DECLARE @mail nvarchar(4000)
Select Mail FROM Member
select Mail from Member
exec @mail = SelectRecipient
select @mail
GO

please i need to know how to resolve the problem and how to retrieve the @mail value in my C# application

karim kamal

Colin Angus Mackay5-Jul-06 20:34

5-Jul-06 20:34

You have infinite recursion. The stored procedure calls itself and there is no exit condition.

karimkamal5-Jul-06 20:45

karimkamal

5-Jul-06 20:45

please help by sending the correct syntax for this procedure i tried to set an exit condition in many ways but in vain

Colin Angus Mackay5-Jul-06 22:50

5-Jul-06 22:50

Your code:

CREATE PROCEDURE SelectRecipient
AS
DECLARE @mail nvarchar(4000)
Select Mail FROM Member
select Mail from Member
exec @mail = SelectRecipient
select @mail
GO

I'm not sure what you are trying to do. It looks like you only want one value, but you are not passing in any parameters so there is nothing to filter the result set.

Suggested change:

CREATE PROCEDURE SelectRecipient
AS
SELECT Mail from Member
GO

From C#

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandTypeStoredProcedure;
cmd.CommandText = "SelectRecipient";
cmd.Connection = myConnection;
SqlReader reader = cmd.ExecuteReader()
while(reader.Read())
{
    string mail = reader.GetString(0);
    // Do something with the result
}

The above will retrieve all mail in the table. (Which I don't think is what you want - but it is the best I can help you with given the information you have supplied)

karimkamal6-Jul-06 5:22

karimkamal

6-Jul-06 5:22

Dear Colin, really i appreciate ur effort with me ,this solution in fact exactly is what i want to do , my fault which i deiscovered lately is i'm trying to send multiple values with one parameter,u r code works with me very well now, with all my thanks, bless u

karim kamal ( developers are dime a dozen but good developers are weighted by GOLD)

Frank Kerrigan5-Jul-06 22:48

Frank Kerrigan

5-Jul-06 22:48

This what you are trying to do

CREATE PROCEDURE SelectRecipient
@Recipient varchar(40)
AS
DECLARE @mail nvarchar(4000)

SET @mail = ( Select Mail FROM Member
Where Member = @Recipient )
select @mail
GO

run this as

exec SelectRecipient 'fkerrigan'

will produce

frank@nospam.com

Look where you want to go not where you don't want to crash.

Bikers Bible

Colin Angus Mackay5-Jul-06 23:06

5-Jul-06 23:06

Ah.... That makes more sense.

But you can optimise it a wee bit more:

CREATE PROCEDURE SelectRecipient
@Recipient varchar(40)
AS
Select Mail FROM Member
Where Member = @Recipient
GO

CLR Stored Procedures and Deploying them to a Client [modified]

Frank Kerrigan6-Jul-06 1:47

Frank Kerrigan

6-Jul-06 1:47

Yes you are quite correct (as always). But I was trying to stick to the orginal code and theme.

Look where you want to go not where you don't want to crash.

Bikers Bible

Drew McGhie5-Jul-06 7:56

Drew McGhie

5-Jul-06 7:56

About 5 months ago when I was designing a database for a client, a co-worker wrote a number of CLR stored procedures that augment functionality of my database. The database was delivered to the client, and we have since found out that one of the stored procedures has a habit of throwing a DivideByZero exception in ceratin cases. I have fixed the code and redeployed it locally, but I'm having problems understanding how I get it to the client when I don't have direct deployment access to their SQL server. Do I need to copy a .dll and manually move it to the client, or is there a way I need to set things up(even if only temporarily) so that I can deploy the new CLR code to the remove server?

Thanks in advance.

-- modified at 14:14 Wednesday 5th July, 2006
Forgot to mention, I've generated scripts using these instructions: http://msdn2.microsoft.com/en-us/library/ms345099.aspx[^], but they refuse to let me drop and then add the CLR's (as I do with other items). I'm using all of the defaults in the wizard, is there something I can check to remove the constraint check briefly so I can remove something and add a new version in its place?

Alternately, can I alter the statement that is being generated?
ALTER ASSEMBLY [Assembly Name]
ADD FILE FROM (huge hex code)
AS N'filename.cs'

Should the Add be modify?

Re: CLR Stored Procedures and Deploying them to a Client

Drew McGhie5-Jul-06 8:56

Drew McGhie

5-Jul-06 8:56

Figured out my issue. Since I had a number of stored procedures and functions, I needed to perform a massive drop and add (drop sp, drop fun, drop assem, add assem, add fun, add sp), but that appears to have successfully transfered them over. The msdn link above is really helpful for generating those files, but I think they probably need to include a paragraph or so about dealing with dependencies.

sql

Tara145-Jul-06 4:22

Tara14

5-Jul-06 4:22

Hi,

I have a table like this:


Param           Cat           Payee          Amt1        Amt2          
AS               x1             y1            23          34   
AS               x1             y2            43          53   
BJ               x1             y1            53          33

I want to select row 1 and row 3.

For just row1, I write,
“SELECT * FROM Tab1 WHERE Param=’AS’ AND Cat=’x1’ AND Payee=’y1’”

But how do write the sql if I want both the 1st row and the 3rd row. They both differ in only the Param name.

Please can you tell me.

Thanks,
Tara

Fortitudine Vincimus!

Colin Angus Mackay5-Jul-06 5:10

5-Jul-06 5:10

TheinstruTara wrote:
But how do write the sql if I want both the 1st row and the 3rd row. They both differ in only the Param name.

Your table should have a primary key. What is the primary key?

Frank Kerrigan5-Jul-06 5:56

Frank Kerrigan

5-Jul-06 5:56

try

SELECT * from Table1 where Payee = 'y1'

or

SELECT Top 2 * from Table1 ORDER BY Amt2

if depends on what you want to filter by.

Look where you want to go not where you don't want to crash.

Bikers Bible

Tara145-Jul-06 6:56

Tara14

5-Jul-06 6:56

I have to calculate the difference between the amounts in the 1st row and that in the 3rd row.

I understand what a primary key is, but my database does not require it, or so I think!!

Nevermind. I did it differently. Got the 1st row, collected the amounts in an array, then got the 2nd row and did the calculation.

If there is a better approach to doing the calculation, I would be glad to know.

Thanks,
Tara

Fortitudine Vincimus!

Re: sql [modified]

Colin Angus Mackay5-Jul-06 9:29

5-Jul-06 9:29

TheinstruTara wrote:
I have to calculate the difference between the amounts in the 1st row and that in the 3rd row.

First a wee bit about databases. They deal with sets of information, using set based queries. In a set there is no concept of order, although many database systems can impose a concept of order by using the ORDER BY clause in SQL. So, as far as the datbase system is concerned there is no 1st and 3rd row.

TheinstruTara wrote:
I understand what a primary key is, but my database does not require it, or so I think!!

If you want to refer to a specific row you need a primary key. If you understand what a primary key is you should know that is will always uniquely refer to a row.

You mention a calculation, but you never mentioned this before. If you are looking for a better way to do a calculation then you need to let us know what the calculation is in order for us to help you with that.

Tara145-Jul-06 10:36

Tara14

5-Jul-06 10:36

Thankyou so much for your help.

I am coding a program in vc++ and I use MS Access as the database. We need to know a little bit to sql to interact with the database. I just mentioned 1st row and 3 row as examples.

In my program I do not know which row it actually is. Therefore, even if there is a primary key assosiated with a row, its of now use to my program.

I was trying something like this:

SELECT * FROM Tab1 WHERE param='AS' AND param='BJ' AND cat='x1' AND payee='y1'

But the sql was not accepted.

Fortitudine Vincimus!

Colin Angus Mackay5-Jul-06 12:17