|
Hi,
Currently we are shifted on Oracle from MsSQL 2005. I want to convert a stored procedure which will take dynamic query in string format and execute it. Here is MsSQL equivalant:
<code>CREATE Proc [dbo].[GetCars]
@YearOfManf Int,
@PriceFrom Decimal,
@PriceTo Decimal
As
Begin
Declare @sql nVarchar(2000)
set @sql=''
If(@YearOfManf is not null)
set @sql=@sql+' And ManufactureYear='+Convert(Varchar(4),@YearOfManf)+''
If(@PriceFrom Is Not Null and @PriceTo Is Not Null)
set @sql=@sql+' And ExpectedPrice Between '+Convert(Varchar(10),@PriceFrom)+' And '+Convert(Varchar(10),@PriceTo)+''
IF(@PriceFrom Is Not Null and @PriceTo Is Null)
SET @sql=@sql+' And ExpectedPrice &gt;= '+Convert(Varchar(10),@PriceFrom)+''
set @sql='SELECT
CarID,
ManufactureYear
ExpectedPrice
FROM
CarMaster
WHERE Visible = 1 AND Deleted = 0 '
+ @sql +'
GROUP BY
ID, ManufactureYear, ExpectedPrice'
exec sp_executesql @sql
End</code>
Is anybody knw how to convert this line: <b>exec sp_executesql @sql</b>
Thanx in advance...
-Abhijit
|
|
|
|
|
Hi,
In Oracle use either EXECUTE IMMEDIATE statement or DBMS_SQL-package for dynamic sql.
Mika
|
|
|
|
|
hi all,
i use following statement to Get all SQL server instance on my local network
Dim instance As System.Data.Sql.SqlDataSourceEnumerator = System.Data.Sql.SqlDataSourceEnumerator.Instance()<br />
Dim table1 As System.Data.DataTable = instance.GetDataSources()<br />
For Each row As DataRow In table1.Rows<br />
cboServer.Items.Add(row.Item(0).ToString)<br />
Next
Above code Add all the Server in combo box which is in local network. Now i select a Server and try to connect to server as following
Dim MainServer As Server = New Server(cboServer.Text.Trim & "\SQLEXPRESS")<br />
With MainServer.ConnectionContext<br />
.LoginSecure = True<br />
.Connect()<br />
End with
but above line give error "Failed to connect to server MACHINENAME\SQLEXPRESS."
Detail of error(Inner Exception) show "{"Login failed for user 'MACHINENAME\Guest'."}"
MACHINENAME is name of server
I try lot of googling. But nothing is helpful.
Can any body help me that how can i resolve this error or How can i enable the Guest account on Server so it is connected.
|
|
|
|
|
It's obvious to the point where it glares right at you.
The windows account MACHINENAME\Guest does NOT have login privileges for the SQL Server instance. You either have to run the application as a different user or have to give login rights to MACHINENAME\Guest in SQL Server Security permissions.
If you have a SQL Server username and password, use that to connect.
And.
If you're not in a domain, Windows Authentication would not work if you're trying to connect to a SQL Server instance in another machine in the network.
sunil goyalG wrote: How can i enable the Guest account on Server so it is connected.
Ask your administrator to do it. Or if you have access right to that machine and you're an admin, the Guest account can be enabled using the System Management MMC. You'd also need to add Guest as a login in SQL Server. For that, use Management Studio --> Security --> Logins to do that.
|
|
|
|
|
SimulationofSai wrote: You either have to run the application as a different user or have to give login rights to MACHINENAME\Guest in SQL Server Security permissions.
How can i give right to MACHINENAME\Guest in SQL Server Security permissions ?
What action i performed after (Management Studio --> Security --> Logins) ? .WIndows XP is running on both computer(Local and Remote)
|
|
|
|
|
sunil goyalG wrote: What action i performed after
In my opinion, the first action you've got to perform is buy a good book on SQL Server and read it.
|
|
|
|
|
Yaa. I feel this thing . I have no idea, that what is the role of Security (Logins, Server Roles and Credentials). Presently i simply want to test that how to connect to a server so i ask this stupid question.
|
|
|
|
|
sir , I done this thing. Now I want to do this thing in programmatically. Let us explain me
1. Enumerate all the server which is on local Network(This task is already done)
2. Connect to a specific server which is on network .
Task 1 is completed .Now i want to connect to a server. which is on the network.Problem is that how can i connect to Server without login info ? if this thing is not possible then how can i create guest account on selected server so i can connect.
|
|
|
|
|
sunil goyalG wrote: Problem is that how can i connect to Server without login info ?
Depending on configuration you can try trusted connection.
sunil goyalG wrote: how can i create guest account on selected server so i can connect.
You can't. Thats called hacking
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i want to combine like and in operator in sql query...
any one help me.................
modified on Monday, August 4, 2008 7:01 AM
|
|
|
|
|
Whats difficult? Show us your code and someone will point out your error.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
suppose u have a table with employee details..
there is a column address. which consist city name too.
now i want to search employee detail which live in city1 or city2 or city3
now here also a problem ' how many cities user want to search . this is not decide..
now sir , the ball is in your court... face the problem..
good luck...
|
|
|
|
|
Firstly, you asked how to combine like and in - thats not what you are asking really.
As I said, its easy enough. This is the sort of 'problem' faced daily, and there are several ways of doing it.
Anudeep Jaiswal wrote: now sir , the ball is in your court... face the problem..
No, the ball is in YOUR court. Post your attempt and I will point out your errors, but I will not write your code for you - its you being paid to do it, not me.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Anudeep Jaiswal wrote: now sir , the ball is in your court... face the problem..
A little courtesy might go a long way in getting answers to your questions on CP 
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
|
HOW TO CREATE NEW DATABASE USING MS-SQL SERVER 2005 AND PLEASE ALSO
GUIDEME ABOUT THE INSTALLATION PROCESS.
nilesh kumar saraf
|
|
|
|
|
Hi Nilesh,
You can do so with the use of Data Adapter and Dataset. for details you can get sample code in C# programming by wrox publication. and of course the MSDN.
|
|
|
|
|
The statement is Simple
Create Database MyDatabase
But am worried about your post, because it tells me that you did not read any book about SQL. I suggest you get a Book in Introduction to Database Design in SQL .
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
|
|
|
|
|
This is exactly the type of question that should not be answered here. You have not taken the time to do any research on the topic and expect the nice folks here to do your job for you. Forget about it.
Don't waste our time.
|
|
|
|
|
hi folks,
A SP in sql server is not returning a string variable as output variable ..can any1 let me know how to return a string variable from SP in sql server?A function returns a string variable whereas a SP doesnt return.y?
for e.g I have created a SP
create procedure SP_abc(
@a INT
@b out VARCHAR(10)
)
as
begin
set @b = 'ABCD'
end
How to get the value of b in the front end?
T.Balaji
|
|
|
|
|
Error 1 DO NOT PREFIX YOUR PROCEDURES WITH SP
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Error 1 DO NOT PREFIX YOUR PROCEDURES WITH SP it forces the compiler to go through ALL the system procedures (sp)
Having said that try changing out to output, the only thing I can see that may be a problem.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi
Try this
create procedure SP_abc(
@a INT,
@b VARCHAR(10) OUTPUT
)
as
begin
set @b = 'ABCD'
end
Hope it helps
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
modified on Monday, August 4, 2008 3:29 AM
|
|
|
|
|
That is unlikely to even compile. There is a missing comma between the second and third line.
|
|
|
|
|
Thanks i Fixed it
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
