Database

Re: query in sql server 2005

6-Jun-08 3:41

select col...
from table1
inner join table 2 on ...
inner join table3 on ...

Or in other words just like inner joining any number of tables.

Bob
Ashfield Consultants Ltd

trilokharry6-Jun-08 4:49

trilokharry

6-Jun-08 4:49

thanks

You get the best out of others when you give the best of yourself.

creating function

meghamaharshi6-Jun-08 2:08

6-Jun-08 2:08

syntax to create function on remote server(sql server 2000)

this is my function:-

Create Function ReturnAmount(@CMID int) Returns Decimal
as
BEGIN
declare @Return decimal
select @Return =sum(PD_PaymentAmount) from Payment_Details where
PD_IsRefund=1 and PD_PaymentType=1 and PD_CMID=@CMID
return @Return
END

but on run error occurs:-
Incorrect syntax near 'Function'.
A RETURN statement with a return value cannot be used in this context.

Ashfield6-Jun-08 2:24

6-Jun-08 2:24

Have yuo got anything in the script before it? Create Function must be the first thing in the script. Also you do not want return @Return, you just want RETURN

Hope this helps

Bob
Ashfield Consultants Ltd

meghamaharshi6-Jun-08 2:34

6-Jun-08 2:34

No do not have any script before it i have tried with only "Return" too but still giving errors:-

Incorrect syntax near 'Function'.
Must declare the variable '@CMID'.

Ashfield6-Jun-08 3:02

6-Jun-08 3:02

Create Function ReturnAmount(@CMID int) Returns Decimal
as
BEGIN
declare @Return decimal
select @Return =sum(PD_PaymentAmount) from Payment_Details where 
PD_IsRefund=1 and PD_PaymentType=1 and PD_CMID=@CMID
return (@Return)
END

should do it.

Bob
Ashfield Consultants Ltd

meghamaharshi6-Jun-08 3:07

6-Jun-08 3:07

i have used but same errors

Ashfield6-Jun-08 3:39

6-Jun-08 3:39

Well I'm at a loss then. It works for me on both SQL Server 2000 and 2005. Are you sure when you copied it none of the line breaks got lost?

Bob
Ashfield Consultants Ltd

meghamaharshi6-Jun-08 19:38

VIA Provider error in SQL Server 2005

6-Jun-08 19:38

ya sure no lost of lines its working on local server but not on remote server

taherjaorawala6-Jun-08 1:29

taherjaorawala

6-Jun-08 1:29

Dear Friends I have installed SQl Server 2005 on windows xp sp2 but when i add SQL Native Client in ODBC i get the following message

SQLState: '8001'
SQL Server Error: 126
[Microsoft][SQL Native Client]VIA Provider: The specified module could not be found.

Connection Failed:
SQLState: 'HYT00'
SQL Server Error: 0
[Microsoft][SQL Native Client]Login timeout expired

Connection Failed:
SQLState: '8001'
SQL Server Error: 126
[Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

TESTS FAILED!

Please guide me as to what can be done to solve the problem

i am not able to connect through the application developed in vb.net.

thnxs.

Re: VIA Provider error in SQL Server 2005

Mike Dimmick8-Jun-08 7:12

Mike Dimmick

8-Jun-08 7:12

You probably don't have the VIA (Virtual Interface Adapter) hardware required to use the VIA protocol. You can force the network library to be used by specifying "Network Library" in the connection string - for example, TCP/IP is 'dbmssocn'.

If you're using VB.NET to connect to SQL Server you should use the SqlConnection class and its family of associated classes, not ODBC.

DoEvents: Generating unexpected recursion since 1991

Re: VIA Provider error in SQL Server 2005

taherjaorawala8-Jun-08 22:12

taherjaorawala

8-Jun-08 22:12

Thanks,

but sql is not configured in ODBC Manager so I cannot connect to sql server. So how should i configure sql in odbc manager.

and when i tried to configure it it was giving me the error as quoted in the query.

How should i solve it.

SQL 2005 Reporting Services

Member 43822326-Jun-08 0:55

Member 4382232

6-Jun-08 0:55

Hi,

I do not know if this is the right place to post my question. Please refer me to the appropriate place if this is not it.

I set up a report server and created some rdl reports using .net. I published them successfully, now I would like to setup the subscriptions. When I read through the msdn and other articles on how to configure the report server for e-mail delivery, I did everything except for this:

"Verify that the Report Server Windows service has Send As permissions on the SMTP server." Can anyone please tell me how do I do this.

Thanx

Cannot insert duplicate key row in object

Ballita5-Jun-08 19:03

Ballita

5-Jun-08 19:03

In my current application I am getting an error "Cannot insert duplicate key row in object with unique index .\r\n51062\r\nThe statement has been terminated." while trying to run the command sqlCommand.ExecuteNonQuery();

Please advice me if anyone has any idea on this.

Thanks & Regards
Mishra

Re: Cannot insert duplicate key row in object

Alsvha5-Jun-08 19:17

Alsvha

5-Jun-08 19:17

Well, the error message seems pretty clear to me.
You are trying to insert rows which have a duplicated key in your table/database.

Avoid doing that, or remove/change the key if you need duplicated values.

---------------------------
Blogging about SQL, Technology and many other things

Re: Cannot insert duplicate key row in object

Blue_Boy5-Jun-08 20:40

Blue_Boy

5-Jun-08 20:40

You allready got answer from error message.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

Magic & SQL server

E_Gold5-Jun-08 9:58

E_Gold

5-Jun-08 9:58

hi how can i transfer database from Magic to SQL server ?

thanks

Re: Magic & SQL server

Marek Grzenkowicz6-Jun-08 23:42

Marek Grzenkowicz

6-Jun-08 23:42

Maybe I am the only one who doesn't know it, but what is 'Magic'?

How to use Distinct from the Following Query

Vimalsoft(Pty) Ltd5-Jun-08 8:20

Vimalsoft(Pty) Ltd

5-Jun-08 8:20

i have the Following Query, i would like to know how can i use Distinct from this Query based on Property_ID Field, but i dont the order of my Field to Change, because there are Duplicates.

SELECT REC_NO,LIS_KEY,FUNC_KEY,UNIT_NO,RIGHTS_ZONING,USE_CODE,OWNER,RATEABILITY,
	   EXCLUSION,MARKET_VALUE,EFFECTIVE_DATE,CATEGORY_CODE,SUPPLE_NUM,AREA,PROPERTY_ID,
	   OLD_MARKET_VALUE,VALUATION_ID,REASON,SORT_DATE
	   FROM SDE.EXPORT_OITPS_GV_TEST
GROUP BY REC_NO,LIS_KEY,FUNC_KEY,UNIT_NO,RIGHTS_ZONING,USE_CODE,OWNER,RATEABILITY,
	   EXCLUSION,MARKET_VALUE,EFFECTIVE_DATE,CATEGORY_CODE,SUPPLE_NUM,AREA,PROPERTY_ID,
	   OLD_MARKET_VALUE,VALUATION_ID,REASON,SORT_DATE

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: How to use Distinct from the Following Query

Rutvik Dave5-Jun-08 11:04

Rutvik Dave

5-Jun-08 11:04

Your question is not clear but you can Try this

SELECT REC_NO,LIS_KEY,FUNC_KEY,UNIT_NO,RIGHTS_ZONING,USE_CODE,OWNER,RATEABILITY, EXCLUSION,MARKET_VALUE,EFFECTIVE_DATE,CATEGORY_CODE,SUPPLE_NUM,AREA,min(PROPERTY_ID), OLD_MARKET_VALUE,VALUATION_ID,REASON,SORT_DATE
FROM SDE.EXPORT_OITPS_GV_TEST
GROUP BY PROPERTY_ID
Order By min(PROPERTY_ID)

or this

SELECT distinct REC_NO,LIS_KEY,FUNC_KEY,UNIT_NO,RIGHTS_ZONING,USE_CODE,OWNER,RATEABILITY, EXCLUSION,MARKET_VALUE,EFFECTIVE_DATE,CATEGORY_CODE,SUPPLE_NUM,AREA,PROPERTY_ID, OLD_MARKET_VALUE,VALUATION_ID,REASON,SORT_DATE
FROM SDE.EXPORT_OITPS_GV_TEST
Order By PROPERTY_ID

A Database Design Problem - Catalogue

Cristoff5-Jun-08 1:46

Cristoff

5-Jun-08 1:46

I'll try to describe my problem. I'm trying to make a program that will display online the articles that are on stock in our company. All these things have completely different properties, so they cannot be put in the same table.

Just to illustrate: Let's say I'm trying to combine apples, oranges, sports cars and rockets. First ones have sorts, cars have horse powers and gas engines and rockets... it doesn't matter. All these are articles/products.

I my case I'm talking about resistors, capacitors, CPU s, micro controllers, LCDs, aluminum boxes, light diodes, electronic units, etc. As you see all these should belong to different product categories and each category in a separate table.

And also this is my very database first project and it turned out to be much more complex than I've anticipated initially. I've searched a lot of DB examples but I cannot find any similar scenario.

Please give me an idea how to approach this problem! Is it possible to use the standard approach - product table - categories tables or such relation is impossible?
The only idea I have so far is to create different tables for each category and to keep them separated and the link between them to be on the application level and not on the SQL database level.

WTF | :WTF:

Thank you for reading all that! Blush | :O

Mike Dimmick5-Jun-08 1:58

Mike Dimmick

5-Jun-08 1:58

I think you're falling into the trap of trying to model the 'real world'. In software systems we don't model all the properties of the real-world objects we deal with, only those properties that are significant to the problem we're trying to solve.

In a retail system only a few things are significant: an identifier to uniquely identify this product, (commonly) a more user-friendly description, the price we plan to sell it at, and the level of stock we have. If you need to categorise, put a 'category' indicator on the product table.

Generally you shouldn't split up a logical collection of items into multiple tables. It becomes very hard to join them back together. If for any reason you actually do need to, for example if one category is queried much more heavily than others and it would benefit from being placed on a different disk, consider the database's partitioning support (if any). I believe it was possible to simulate partitioning efficiently in SQL Server 2000 but can't remember the details.

If you need to store additional information that won't be uniform across all items, consider nullable columns and using NULL where the value doesn't apply, or what I call 'extension tables' which are associated with the main, common, table in a one-to-one relationship, which stores only the extended information about this type of item.

DoEvents: Generating unexpected recursion since 1991

Cristoff5-Jun-08 2:52

Cristoff

5-Jun-08 2:52

Thank you! What I have in mind for example is the product browser of the website http://www.farnell.com This site is huge. Mine will be very small with not that many items (compared to Farnell) and with few users.

I have difficulty to accept the idea with NULLable columns yet, because because I might end with dozens and dozens if not hundreds of columns.

Thank you for the remark. A friend of mine also told me: "Forget about that OOP way of thinking". I'll think about my problem a little while longer.

ChandraRam5-Jun-08 2:04

ChandraRam

5-Jun-08 2:04

You could have one table of Categories and another of CategoryProperties (multiple rows for each Category, containing CategoryId, Property Name and Value) - will that work for you?