|
U can use if condition to test the argument
and write 2 select commands like:
if(@order=1)
select .... order by asc
else
select .... order by desc
Do U really need this or I am wrong somewhere?
Regards,
Arun Kumar.A
|
|
|
|
|
Yes this works, the problem is that for two large blocks of select statements, I should copy the whole select block and paste it into 'else' section of if statement just because I wanna sort it descendingly, it's a little annoying, isn't it?
It would be nice if there was a way to enclose the ASC/DESC section of our sql, something like this:
select * from books order by title
if(@order=1)
asc
else
desc
|
|
|
|
|
select * from books order by
case when(@order=1) then title end, asc
case when(@order=2) then title end, desc
Hope, this helps U.
Regards,
Arun Kumar.A
|
|
|
|
|
I have tried this with the folloing example by changing @a to 1 or 0 alternatively:
It works fine.
declare @a int
set @a=1
select * from tableName
order by case
when @a=0 then columnName
end ,
case
when @a=1 then columnName
end desc
But the problem is we have to design the conditons in such a way that only one of the conditions must evaluate to true.
Change the value "0" to "1" in the above code.
It will throw U an error.
So, please make sure to check the above problem.
Regards,
Arun Kumar.A
|
|
|
|
|
Thank you, this seems to help solve the problem we have,
I just didn't understand what the need of ',' is after the first when statement here:
when @a=0 then columnName
end ,
why sould we use ',' when we have shown the end of WHEN block with an 'end' keyword? I also tried this and it worked:
when @a=0 then columnName
end asc,
Maybe I'm just not familiar enough with CASE WHEN statement syntax in sql! shame on me!
Just one more question, Is there also a way to select different columns conditionaly too? (I mean something like the second solution you offered, just by enclosing a specific part of our select statement)
|
|
|
|
|
Maysam Mahfouzi wrote: Is there also a way to select different columns conditionaly too?
Yes, using simple case we can do that.
Click here
R U asking this?
Or do U want to sort by different columns based on condition?
Regards,
Arun Kumar.A
|
|
|
|
|
The trouble with this approach is that SQL Server generates and caches an execution plan the first time you run a stored procedure, then reuses that execution plan every time until it hasn't been used for long enough to be discarded. Only on the next execution will it then be recompiled.
If you have one of these amorphous stored procedures, where the behaviour is very different - effectively using different columns in the WHERE or ORDER BY clauses - the cached execution plan for one set of parameters can be very wrong for a different set. This translates into bad and unpredictable performance. You can force compilation of the execution plan every time by specifying WITH RECOMPILE, but obviously you incur the overhead of computing the execution plan every time it's executed.
It may well be better to use dynamic SQL explicitly from the client. You can still prevent SQL injection by using replaceable parameters in your SQL query text, and this will actually improve execution plan compilation times as SQL Server prefers retaining execution plans from parameterized versus non-parameterized queries (SQL Server caches all computed execution plans unless specifically told not to, but gives greater weight to stored procedures over parameterized text over non-parameterized text, so the more structured queries are retained longer).
You do lose the access control benefits of stored procedures as the user account used to query the database must be given SELECT access to the tables being queried (rather than just GRANTing access to EXEC the stored procedure).
|
|
|
|
|
Or, you can build the string within the proc, and get the same result without the loss of access control ?
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I want to make a column in a certain table ,this column consists of 3 chars an 6 autoincrement numbers like "abc-123456" .Furthermore this column is a primary key
how can i do this?
thanks
Mohammed Sobhi
|
|
|
|
|
Create a stored procedure to insert rows into the table.
In the stored procedure extract the last 6 characters from the ID column
and find the maxmimum number.
Add 1 to that number and insert it into the table along with the
other arguments.
Regards,
Arun Kumar.A
|
|
|
|
|
your db will perform better if your keys are numbers instead of strings. However, as someone else said, you can do this, but you can't make it autoincrement, you need to increment it yourself.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
hi all,
i use following two string to connect database located at remote sql server (mean on internet)
sql connection string
SqlConn = "Data Source=xxx.xxx.xx.xx\server;Network Library=DBMSSOCN;Initial Catalog=databaseName;User ID=Databaseuser ; Password=DatabasePassword;"
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)”
OLEDB connection string
oledbConn = "provider=SQLOLEDB; "Data Source=xxx.xxx.xx.xx\server;Network Library=DBMSSOCN;Initial Catalog=databaseName;User ID=Databaseuser; Password=DatabasePassword;"
error:
“[DBNETLIB][ConnectionOpen (Connect())]Specified SQL server not found”
I already have done the following steps:
1. Enable the TCP/IP protocol using the Surface Area Configuration Utility
2. the TCP/IP protocol is enabled in the SQL Server Configuration Utility
3. the SQL Server browser is started.
4. SQL Server and SQL Server Browser are exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall.
i dont know where i m wrong. Please Help Me
any help will be appriciated. thanks
Rupesh Kumar Swami
|
|
|
|
|
Is there only one sql server instance on that server. If that is true then your connection string is wrong. You just need to be in the IP address not IP address/server. If you do have multiple sql server instances you may need to set the one you want to use to a static port and then add that port to your connection string. If you need more details on this let me know.
Hope that helps.
Ben
|
|
|
|
|
hi ben,
according to ur specification, i remove the server name from my connection string. but now this time, i face new error which is following
when i use following SQLConnection string (vb.net)
SqlConn = " Data source=xx.xx.xx.xx; Network Library=DBMSSOCN; Initial catalog=DatabaseName;uid=user ID;pwd=Password;"
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: TCP Provider, error: 0 - A socket operation was attempted to an unreachable host.)
when i use following SQLConnection string (vb.net)
SqlConn = " Data source=http://xx.xx.xx.xx; Network Library=DBMSSOCN; Initial catalog=DatabaseName;uid=user ID;pwd=Password;"
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: TCP Provider, error: 0 - No such host is known.)
and when i use oledbconnection string (vb.net)
oledbConn = "provider=SQLOLEDB; Data source=xx.xx.xx.xx; Network Library=DBMSSOCN; Initial catalog=DatabaseName;uid=user ID;pwd=Password;"
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
and when i use oledbconnection string (vb.net)
oledbConn = "provider=SQLOLEDB; Data source=http://xx.xx.xx.xx; Network Library=DBMSSOCN; Initial catalog=DatabaseName;uid=user ID;pwd=Password;"
[DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.
operating system-XP sp2
Thanks
Rupesh Kumar Swami
-- modified at 7:29 Tuesday 1st May, 2007
|
|
|
|
|
Can you ping the ip address?
Ben
|
|
|
|
|
hi ben,
i can ping the IP address
i don't understand what is the problem ?
thanks
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
|
|
|
|
|
Do you have multiple sql servers running on that server?
Ben
|
|
|
|
|
hi ben
there r only single instance.
thanks
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
|
|
|
|
|
Can you successfully create an odbc connection to it?
Go to Control Panel -> Administrative tools -> Data sources (ODBC)
Click the add button. Then scroll down to the sql server and click finish.
Next type in the server name, click next and select sql authentication. Put in your username and password. click next. If you get an error, you have a problem with your user name and password perhaps. If you get to the next screen select your database. If you don't see your database in the drop down your user doesn't have rights to that database. If you get to the next screen you should be able to test the connection. If the test works then you should be able to use the same server, database, user and password to make your connection work in your program.
Hope that helps.
Ben
|
|
|
|
|
hi ben ,
according to your specification i follow steps as following
pressing finish button after selecting Sqlsever from datasource list ,there r new window appear. I type ip address of remote server(in textbox related to server) and click next. after that i select SQL Server authentication and put username & password and clicking on next button give error.
here i m sure that user name & password is correct.
any idea where i m wrong.Thanks
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
|
|
|
|
|
If you can't create an odbc connection, something is wrong. Try using the sql server name. there must be some issue with the name / ip of the sql server and the username and password. Once you can get it working with an odbc connection you can use the same values to get it working in your app.
Ben
|
|
|
|
|
hi ben ,
i also try with ip address & server name (not only with server name) but problem not solved.
if u have any other idea ,please inform me
Thanks for your support.
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
|
|
|
|
|
I guess you could try using the query sql tool to connect to the sql server with that user name and password, but I am pretty sure you will get the same results. For some reason your sql server name or IP or username/password is not correct. Until you figure that out you will not be able to connect.
Ben
|
|
|
|
|
Please will someone recommend some good, detailed resources on SQL Server 2005 security. I am going to select and purchase a book on the subject next week, on which I would also appreciate recommendations, but I need to achieve some tasks in the mean time, so I would like some on-line resources I can read now.
I would like to learn more about asynchronous keys and associated logins, how to grant unsafe assembly permission to a database owner[1], more about 'alias' users such as 'dbo', the relationship between the machine administrator group and SQL server, owner ship or rights over schemas vs actual objects etc. and even basic secutiry such as logins vs. users.
|
|
|
|
|
I don't know books about SQL Security, however i know some sites that talk about SQL Injection, which can be very very destructive.
Links, you will need to search into the blogs the related topics to SQL Injection:
FR3DC3RV BLOG
RONALD BLOG
Good luck
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
