Database

Re: How to order Integer field as a string ??

20-Feb-07 3:46

Unusual requirement! Order by Convert(varchar(10), ID)

kindman_nb20-Feb-07 5:07

kindman_nb

20-Feb-07 5:07

thanks my friend ,,
your methos also is good

jooooo

SQL server does not exists or access denied.

Aswanikumarj20-Feb-07 3:06

Aswanikumarj

20-Feb-07 3:06

We have sql server with the name production1 (IPID:100.102.33.33) ,from asp.net iam able to connect properly ,yesterday we changed the ip Id of this system(production1) from that time if iam trying to connect from asp.net code it is giving error sqlserver does not exists access denied.

Iam able to connect in query analyzer for the same sql server by using same userid,password.

Iam able to connect in vb.net for the same sql server with same connection string which iam using in asp.net

Iam unable to connect in asp.net error sqlserver does not exists access denied.

Please help me is there any problem with asp user or what?

Thanks

Re: SQL server does not exists or access denied.

Krish - KP20-Feb-07 17:11

Krish - KP

20-Feb-07 17:11

i had similar problem, unable to connect to sqlserver from query analyser or other application (cilent & server diff machines)

after creating an Alias to server machine using "Client Network Utility" of SQL Server able to crack this problem.

Hope this will solve your problem also

Re: SQL server does not exists or access denied.

Aswanikumarj28-Feb-07 6:02

Aswanikumarj

28-Feb-07 6:02

Hi,

I tried like that also but no luck.

Thanks,
J.ASwani kumar

Average of all columns

Sam Heller20-Feb-07 1:55

Re: Average of all columns

20-Feb-07 1:55

I have a table where each row list's the product id and then 9 individual statistical percentage value relevant to that product. I need to create a final column to average all percentages into one overall score as well.

Any suggestions on how to do this?

The main select part of this code is below. Thanks in advance

SELECT ProductID, <br />
	-- Calculate Speed percentage<br />
	CONVERT(decimal(18, 2), @BestSpeed / (SELECT AVG(Finalised) FROM Leads WHERE Finalised IS NOT NULL AND ProductID = P.ProductID) * 100) As [Speed],<br />
	--Actual speed value<br />
	(SELECT AVG(Finalised) FROM Leads WHERE Finalised IS NOT NULL AND ProductID = P.ProductID) As [Actual Speed],<br />
	-- Calculate APR value percentage<br />
	CONVERT(decimal(18, 2), @BestAPR / (SELECT AVG(APR) FROM Leads WHERE APR IS NOT NULL AND ProductID = P.ProductID) * 100) As [APR],<br />
	-- Actual APR value<br />
	(SELECT AVG(APR) FROM Leads WHERE APR IS NOT NULL AND ProductID = P.ProductID) As [Actual APR],<br />
	-- Calculate Broker Fee percentage<br />
	CONVERT(decimal(18, 2), @BestBrokerFee / (SELECT AVG(BrokerFee) FROM Leads WHERE BrokerFee IS NOT NULL AND ProductID = P.ProductID) * 100) As [BrokerFee],<br />
	-- Actual APR value<br />
	(SELECT AVG(BrokerFee) FROM Leads WHERE BrokerFee IS NOT NULL AND ProductID = P.ProductID) As [Actual BrokerFee],

andyharman20-Feb-07 3:44

Re: Average of all columns

20-Feb-07 3:44

Try

select A.*,
 ([Speed] + [Average Speed] + ##Others##) / 9 AS FinalAverage
from (
  ##Your SQL goes here
) A
order by ProductId

Your original SQL is used as an "inline view". The average is the total of your 9 columns, divided by nine.

Regards
Andy

Sam Heller20-Feb-07 5:09

Re: Average of all columns

20-Feb-07 5:09

I don't think I made myself completely clear. I do not have a table in my database with the statistics in them. I have a large query that is building these stats up as I go, for each row. What I want to do is average up these created values once the row is has all but finished. This should be the final column.

The code your provided does the correc thing but I need to know how to get the values from the row I am creating, rather than from a table in the database?

Any ideas?

Thanks

andyharman20-Feb-07 22:40

SQL Server 2000 Installation problem on Windows server 2003

20-Feb-07 22:40

If you insert you original select statement where I indicated then you will find the the resultset contains all of your columns (thats the A.* bit), followed by your new average.

Gulfraz Khan20-Feb-07 1:51

Gulfraz Khan

20-Feb-07 1:51

i am trying to install sql server 2000 standard Edition on Windows server 2003 but at the configuration time during the installation, Configration error message is shown pointing to "C:\windows\sqlstp.log". when I open this file it contains the following messages in the bottom. and setup exits.

Sql Server Log

##############################################################################

Starting Service ...

SQL_Latin1_General_CP1_CI_AS

-m -Q -T4022 -T3659

Connecting to Server ...

driver={sql server};server=NICOPDB;UID=sa;PWD=;database=master

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

driver={sql server};server=NICOPDB;UID=sa;PWD=;database=master

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

driver={sql server};server=NICOPDB;UID=sa;PWD=;database=master

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

SQL Server configuration failed.

###############################################################################

Re: SQL Server 2000 Installation problem on Windows server 2003

Smart_Boy13-Mar-07 19:14

Smart_Boy

13-Mar-07 19:14

Install MDAC 2.8 and then install SQL Server...

Regards,
Smart Boy
Mumbai,
(INDIA)

Re: SQL Server 2000 Installation problem on Windows server 2003

Gulfraz Khan14-Mar-07 5:49

Gulfraz Khan

14-Mar-07 5:49

Thank You for reply. I will try it out

Null values

gauthee20-Feb-07 0:33

gauthee

20-Feb-07 0:33

hi,

i want to insert few values in a table of which some should be null.
how can i assign the null value at the code level itsellf?
example i have few variables and one of the variable should be a null value and this variable will be later passed as a input parameter to a sp

Gautham

andyharman20-Feb-07 0:35

20-Feb-07 0:35

set @MyVar = NULL

gauthee20-Feb-07 0:45

gauthee

20-Feb-07 0:45

for example i have parameter name param1 for a sp and now normally we have to assign a value to this param1 like param1.value="some value" here i want to give a null value

Hope iam clear!!

Gautham

Colin Angus Mackay20-Feb-07 0:55

Colin Angus Mackay

20-Feb-07 0:55

Use DBNull.Value in .NET to pass NULL to the database.

Upcoming events:
* Glasgow: Geek Dinner (5th March)
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)

My: Website | Blog | Photos

gauthee20-Feb-07 1:20

gauthee

20-Feb-07 1:20

Thanks!

Gautham

Datatype?

Sam Heller19-Feb-07 22:35

19-Feb-07 22:35

SELECT ProductID,(@BestSpeed / (SELECT AVG(Finalised) FROM Leads WHERE Finalised IS NOT NULL AND ProductID = P.ProductID) * 100) As [Speed],<br />
FROM Products P

The following code retrieves an integer, Product ID, and another field, Speed. How do I know what datatype speed is and how can i change it? It returns a percentage value but currently I am getting, for example :45.5621301775148. Presumably this is a float. I would prefer say Decimal(18,2)

Thanks

Re: Datatype?

Krish - KP19-Feb-07 23:00

Krish - KP

19-Feb-07 23:00

can us Convert function

SELECT ProductID, CONVERT(decimal(18, 2), (@BestSpeed / (SELECT AVG(Finalised) FROM Leads WHERE Finalised IS NOT NULL AND ProductID = P.ProductID) * 100)) As [Speed],
FROM Products P

i've used same on strings for converting to number / date
but not for numeric

Re: Datatype?

Sam Heller20-Feb-07 1:52

sql server doesnt allow remote connections![.NET2 sqlserv05exp]

20-Feb-07 1:52

CONVERT(decimal(18, 2), @BestAPR / (SELECT AVG(APR) FROM Leads WHERE APR IS NOT NULL AND ProductID = P.ProductID) * 100) As [APR]

The following seems to work fine. Thanks

giddy_guitarist19-Feb-07 21:43

giddy_guitarist

19-Feb-07 21:43

hi ,

well thats what JIT says everytime i run an sql server 05 express sample on my pc.Somehow the code that i write for the dbs i've made runs fine. But every sample ive got , including samples from MSDN give me that error.

"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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

Could someone perhaps tell me how to enable remote connections!? I'm quite a newbie with sqlserver.

thanks
Gideon

Re: sql server doesnt allow remote connections![.NET2 sqlserv05exp]

Colin Angus Mackay19-Feb-07 23:55

Colin Angus Mackay

19-Feb-07 23:55

giddy_guitarist wrote:
well thats what JIT says everytime i run an sql server 05 express sample on my pc

If you refer to the SQL Server using some form of network naming convention it will attempt to connect with TCP/IP which is, for security, turned off by default.

e.g. The connection string says: Server=localhost it will attempt to connect via IP address 127.0.0.1 (the loopback adapter)

Try Server=(local) as that will attempt to use a (I think) shared memory connection which doesn't go through any network adapter.

Upcoming events:
* Glasgow: Geek Dinner (5th March)
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)

My: Website | Blog | Photos

Re: sql server doesnt allow remote connections![.NET2 sqlserv05exp]

andyharman20-Feb-07 0:08