Error Creating Function

I want to calculate a string, so I have created this Function, but its
giving error any idea please ?
Best Regards,
Luqman
Create function eval
(@.Mycalcstring varchar(50))
returns decimal(12,2)
as
begin
declare @.myString varchar(50)
select @.mystring=exec('select '+@.Mycalcstring)
return(@.myString)
endHi
I am not sure why you don't use cast/convert directly, although this would
depend on the quality of what you are passing to the function.
Create function eval (@.Mycalcstring varchar(50))
returns decimal(12,2)
as
begin
return(CAST(@.Mycalcstring AS decimal(12,2)))
end
SELECT dbo.eval('12.2')
John
"Luqman" wrote:

> I want to calculate a string, so I have created this Function, but its
> giving error any idea please ?
> Best Regards,
> Luqman
> Create function eval
> (@.Mycalcstring varchar(50))
> returns decimal(12,2)
> as
> begin
> declare @.myString varchar(50)
> select @.mystring=exec('select '+@.Mycalcstring)
> return(@.myString)
> end
>
>|||Say, I want to calculate this string : '(10-5)*7/2'
select cast('(10-5)*7/2' AS decimal(12,2))
will give error :
Error converting data type varchar to numeric.
But if I use:
Select (10-5)*7/2 ' It will return 17 which is Correct Result.
So, please advise whats wrong with my function.
Best Regards,
Luqman
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:CD3C2647-4BAF-4499-A6B9-06D275405010@.microsoft.com...
> Hi
> I am not sure why you don't use cast/convert directly, although this would
> depend on the quality of what you are passing to the function.
> Create function eval (@.Mycalcstring varchar(50))
> returns decimal(12,2)
> as
> begin
> return(CAST(@.Mycalcstring AS decimal(12,2)))
> end
> SELECT dbo.eval('12.2')
> John
> "Luqman" wrote:
>|||Luqman wrote:
> Say, I want to calculate this string : '(10-5)*7/2'
> select cast('(10-5)*7/2' AS decimal(12,2))
> will give error :
> Error converting data type varchar to numeric.
> But if I use:
> Select (10-5)*7/2 ' It will return 17 which is Correct Result.
> So, please advise whats wrong with my function.
> Best Regards,
> Luqman
Dont use the ' because they indicate a character column.
SELECT CAST(((10-5)*7/2) AS DECIMAL(12,2))|||Such calculations are stored in a Table with Varchar Type, so I need to
calculate their values, and as such I need this function.
Following lines will work, but how can I put the select result in Variable.
declare @.myString as varchar(50)
declare @.myValue as Decimal(12,2)
set @.myString='(10-5)/2'
exec ('select ' + @.myvalue) 'This works
set @.myValue=exec ('select ' + @.myvalue) 'This does not work
Best Regards,
Luqman
"Jo Segers" <jo.segers@.alro.be> wrote in message
news:eo04ybPDFHA.512@.TK2MSFTNGP15.phx.gbl...
> Luqman wrote:
> Dont use the ' because they indicate a character column.
> SELECT CAST(((10-5)*7/2) AS DECIMAL(12,2))|||declare @.myString as nvarchar(50)
declare @.myValue as decimal(15,2)
set @.myString='(10-5)/2'
set @.myString = 'select @.myValue = ' + @.myString
exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
print @.myValue
hth,
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:uH2gmyPDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Such calculations are stored in a Table with Varchar Type, so I need to
> calculate their values, and as such I need this function.
> Following lines will work, but how can I put the select result in
Variable.
> declare @.myString as varchar(50)
> declare @.myValue as Decimal(12,2)
> set @.myString='(10-5)/2'
> exec ('select ' + @.myvalue) 'This works
> set @.myValue=exec ('select ' + @.myvalue) 'This does not work
> Best Regards,
> Luqman
>
>
> "Jo Segers" <jo.segers@.alro.be> wrote in message
> news:eo04ybPDFHA.512@.TK2MSFTNGP15.phx.gbl...
>|||avnrao wrote:
> declare @.myString as nvarchar(50)
> declare @.myValue as decimal(15,2)
> set @.myString='(10-5)/2'
> set @.myString = 'select @.myValue = ' + @.myString
> exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
> print @.myValue
> hth,
>
Or if you realy want your 2 decimals:
declare @.myString as nvarchar(50)
declare @.myValue as decimal(15,2)
set @.myString='(10-5)/2'
set @.myString = 'select @.myValue = 1.0*' + @.myString
exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
print @.myValue|||Hi,
I have created following function in master database and when select it with
:
Select dbo.eval('10-5')
Error occured:
Server: Msg 557, Level 16, State 2, Procedure eval, Line 9
Only functions and extended stored procedures can be executed from within a
function.
Any idea please?
create function eval
(@.myString as varchar(50))
returns decimal(12,2)
as
begin
declare @.myValue as decimal(15,2)
set @.myString='(10-5)/2'
set @.myString = 'select @.myValue = ' + @.myString
exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
return(@.myValue)
end
"avnrao" <avn@.newsgroups.com> wrote in message
news:u8v6gEQDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> declare @.myString as nvarchar(50)
> declare @.myValue as decimal(15,2)
> set @.myString='(10-5)/2'
> set @.myString = 'select @.myValue = ' + @.myString
> exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
> print @.myValue
> hth,
> --
> Av.
> http://dotnetjunkies.com/WebLog/avnrao
> http://www28.brinkster.com/avdotnet
>
> "Luqman" <pearlsoft@.cyber.net.pk> wrote in message
> news:uH2gmyPDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Variable.
>|||You cannot run dynamic SQL from within a function. EXEC and
sp_executesql are not allowed. Either use an SP or, better still IMO,
do it in some external code. I don't understand why you would want to
do this in a database.
Also, I wouldn't recommend putting functions or other objects in Master
on a production system.
David Portas
SQL Server MVP
--|||Hi,
I just want to create a function which should calculate a String, and return
the result, someone recommended this way ?
If you have better solution, please adv.
Best Regards,
Luqman
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107775757.602219.248630@.o13g2000cwo.googlegroups.com...
> You cannot run dynamic SQL from within a function. EXEC and
> sp_executesql are not allowed. Either use an SP or, better still IMO,
> do it in some external code. I don't understand why you would want to
> do this in a database.
> Also, I wouldn't recommend putting functions or other objects in Master
> on a production system.
> --
> David Portas
> SQL Server MVP
> --
>

Error Connecting to Database (SQL Server 2005)!

Hello all,
I'm encountering an issue with regards to the default REMOTE LOGIN
settings to my database instance on SQL Server 2005.
The message I am recieving is as follows:
"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: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server
Please check your database settings and credentials and confirm that
the server is reachable."
To my knowledge, I am able to see my database running, and that I have
already set up the DAC Surface Area Configuration, and ensured that
both "Named Pipes and TCP/IP protocols" have been enabled.
Whats wrong here guys...am I missing something?
Mohd Al Junaibi wrote:
> Hello all,
> I'm encountering an issue with regards to the default REMOTE LOGIN
> settings to my database instance on SQL Server 2005.
> The message I am recieving is as follows:
> "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: Named Pipes Provider, error: 40 - Could not
> open a connection to SQL Server
> Please check your database settings and credentials and confirm that
> the server is reachable."
>
> To my knowledge, I am able to see my database running, and that I have
> already set up the DAC Surface Area Configuration, and ensured that
> both "Named Pipes and TCP/IP protocols" have been enabled.
> Whats wrong here guys...am I missing something?
>
Did you enable Remote Connections in the Surface Area Configuration tool?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Error Connecting to Database (SQL Server 2005)!

Hello all,
I'm encountering an issue with regards to the default REMOTE LOGIN
settings to my database instance on SQL Server 2005.
The message I am recieving is as follows:
"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: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server
Please check your database settings and credentials and confirm that
the server is reachable."
To my knowledge, I am able to see my database running, and that I have
already set up the DAC Surface Area Configuration, and ensured that
both "Named Pipes and TCP/IP protocols" have been enabled.
Whats wrong here guys...am I missing something?Mohd Al Junaibi wrote:
> Hello all,
> I'm encountering an issue with regards to the default REMOTE LOGIN
> settings to my database instance on SQL Server 2005.
> The message I am recieving is as follows:
> "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: Named Pipes Provider, error: 40 - Could not
> open a connection to SQL Server
> Please check your database settings and credentials and confirm that
> the server is reachable."
>
> To my knowledge, I am able to see my database running, and that I have
> already set up the DAC Surface Area Configuration, and ensured that
> both "Named Pipes and TCP/IP protocols" have been enabled.
> Whats wrong here guys...am I missing something?
>
Did you enable Remote Connections in the Surface Area Configuration tool?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yep...used the Surface Area Configuration and enabled remote logins
with Named Pipes and Support.
But just so that I didn't do anything wrong..what would be the
essential steps here (if you were in my shoes)..?|||Mohd Al Junaibi wrote:
> Yep...used the Surface Area Configuration and enabled remote logins
> with Named Pipes and Support.
> But just so that I didn't do anything wrong..what would be the
> essential steps here (if you were in my shoes)..?
>
Is SQL running on your local workstation, or on a remote server? Can
you ping that remote server, via hostname and via IP address? Is there
a firewall involved?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, both MSSQL Service is running, and the agent is on as well. I
don't recall having the firewall running.
I'm beginning to think it has to do with credentials.
I'm running a command to create my WEBFORMS on a virtual directory,
it's experimental and called "Blinq" from Microsoft.
Anyways...I'll try to check things here..
thanks a million

Error Connecting to Database (SQL Server 2005)!

Hello all,
I'm encountering an issue with regards to the default REMOTE LOGIN
settings to my database instance on SQL Server 2005.
The message I am recieving is as follows:
"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: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server
Please check your database settings and credentials and confirm that
the server is reachable."
To my knowledge, I am able to see my database running, and that I have
already set up the DAC Surface Area Configuration, and ensured that
both "Named Pipes and TCP/IP protocols" have been enabled.
Whats wrong here guys...am I missing something?Mohd Al Junaibi wrote:
> Hello all,
> I'm encountering an issue with regards to the default REMOTE LOGIN
> settings to my database instance on SQL Server 2005.
> The message I am recieving is as follows:
> "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: Named Pipes Provider, error: 40 - Could not
> open a connection to SQL Server
> Please check your database settings and credentials and confirm that
> the server is reachable."
>
> To my knowledge, I am able to see my database running, and that I have
> already set up the DAC Surface Area Configuration, and ensured that
> both "Named Pipes and TCP/IP protocols" have been enabled.
> Whats wrong here guys...am I missing something?
>
Did you enable Remote Connections in the Surface Area Configuration tool?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yep...used the Surface Area Configuration and enabled remote logins
with Named Pipes and Support.
But just so that I didn't do anything wrong..what would be the
essential steps here (if you were in my shoes)..?|||Mohd Al Junaibi wrote:
> Yep...used the Surface Area Configuration and enabled remote logins
> with Named Pipes and Support.
> But just so that I didn't do anything wrong..what would be the
> essential steps here (if you were in my shoes)..?
>
Is SQL running on your local workstation, or on a remote server? Can
you ping that remote server, via hostname and via IP address? Is there
a firewall involved?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, both MSSQL Service is running, and the agent is on as well. I
don't recall having the firewall running.
I'm beginning to think it has to do with credentials.
I'm running a command to create my WEBFORMS on a virtual directory,
it's experimental and called "Blinq" from Microsoft.
Anyways...I'll try to check things here..
thanks a million