|
Rewrite query as
SELECT distinct( COALESEC(P.firstname,P.firstname,'') + ' ' + COALESEC(P.middlename,P.middlename,'') + ' ' + COALESEC(P.lastname,P.lastname,'')) AS UserName ,P.Id as UserId,G.Id,G.Fkgroupid FROM c_Groupmember G,c_Person P where P.Id=G.FKUserid and G.FKgroupId=" + Id + " and P.Status=0 order by P.firstName asc
for more information see COALESCE function documentation.
Intelligence is measured by common sense not by how many scholarly books you read.
|
|
|
|
|
it is showing an error
Msg 195, Level 15, State 10, Line 1
'COALESEC' is not a recognized built-in function name.
|
|
|
|
|
ya.i got .it is 'COALESCE'. Not 'COALESEC'
|
|
|
|
|
anujose wrote: ?.in query itself i need to get all the usernames
Better way of working on queries will be putting them up in a stored proc, to avoid sql injection attacks.[^]
|
|
|
|
|
Try SET CONCAT_NULL_YIELDS_NULL OFF before you begin the query. Works in 2000, not usre about 2005.
|
|
|
|
|
I posted this question before but I'm not sure it was fully understood:
I'm using MSSQL7, NT authentication and application roles so only my application can access the data. Also, other applications (like Excel) can not access the data and read it. So far, so good...
Yet, I noticed that if I try to access the SQL Server from another SQL Server on the network, it is allowed to see the list of tables, SP, etc. It is not allowed to open the table, but the Import/Export wizard is working and will allow retrieving data from the secured tables.
If I change to MSSQL authentication, any user will be able to access the data from my application and I don't want that either.
Unless I'm missing something, this is a big problem, especially today where any VPN connection with valid user name and password can actually log in to the domain and therefore connect to the database via SQL Server.
By the way, the server still must allow access to users via applications so logins must exist. I just don't want other SQL servers on the network to be able to connect to and import/export, view table and SP, etc.
Any ideas?
|
|
|
|
|
you're saying that if a user connects to your DB via the SQL Server tools, they have access to stuff that the role they log in under should not see ?
Christian Graus - Microsoft MVP - C++
"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 )
|
|
|
|
|
Correct...
It seems as if any user that can log in to SQL, can use SQL Server to see the list of table, list of SP and import/export data even though their user role does not allow SELECT, UPDATE, INSERT or EXECUTE.
|
|
|
|
|
Hi,
In a table with a primary key and a foreign key like this.
I-------I-------I-------I
I pkey I fkey I name I
I-------I-------I-------I
I 1 I 1 I ITEM I
I-------I-------I-------I
I 2 I 1 I ITEM2 I
I-------i-------I-------I
If we were to run a select query against this table that returns just one row. Would be any performance benefit if we have this select ?:
SELECT * FROM The_Table WHERE pkey=2 AND fkey=1
over when we have this select ?:
SELECT * FROM The_Table WHERE pkey=2
|
|
|
|
|
As the primary key is unique (a condition of being a primary key is that it uniquely identifies the row) then I wouldn't see any need for any additional information in the WHERE clause.
Whether it has any benefit or not depends on how you set up the table. If you go with the traditional defaults then I don't see it having any additional benefit. But I doubt it would have any negative effect either.
|
|
|
|
|
If we define an index on the foreign key field , will be any performance gain ?
|
|
|
|
|
devboycpp wrote: If we define an index on the foreign key field , will be any performance gain ?
In this case, I doubt it. The WHERE clause already contains a reference to a column that is guaranteed to be unique (i.e. the Primary Key) and is most likely to be indexed (unless it was removed) so will go directly to that row. Foreign keys are not normally unique.
Also, adding an index can make performance worse for INSERT, UPDATE and DELETE operations.
|
|
|
|
|
select empid,empname,valid bit null
into #temp
from table
I know this is wrong. What I need is to add a new column which is not in table [valid] and assign that column as bit type and value as null.
Can you please help?
Thanks
|
|
|
|
|
A BIT column is not nullable.
Paul Marfleet
|
|
|
|
|
Please help me this problem, thanks.
Thao
|
|
|
|
|
I'm sorry, I'm sure English is not your first language, and I don't want you to think you're under attack for that. But, your question makes no sense. Your english doesn't have to be perfect, we're very forgiving, but if we don't understand, we just can't hope to help.
Christian Graus - Microsoft MVP - C++
"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 was able to run ssis Installation successfully. In summary of last step, is it possible to take out the author and log file information or rewrite them with other information?
|
|
|
|
|
hi all,
i want to write a generic Insert & update procedure.It should work with different tables having diffrerent datatypes.suppose let say table1 may have col1 int,col2 varchar... and let say table2 may have col1 varchar,col2 char..like that..and so on..
generally till now wat iam doing is that iam writting different stored procedure for different tables..
Now i want to write One Insert & update procedure in my project...and for all the tables in the project i want to use only that procedure.
Is it Possible? If yes kindly help me ..on resolving this issue..
Thanks & regards
suman
|
|
|
|
|
You can. But it would most likely end up using dynamic SQL and you might as well just fire that against the database instead.
|
|
|
|
|
hi
thanks for u reply..
can u help me in writing the query for that requirement.If u have any thing about the dynamic sql and like generic procedures ..please help me by giving some sample query.
|
|
|
|
|
What's the difference between @a and @@a type parameters
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
You pass in parameters that have @. @@ relates to internal functions and values inside SQL Server.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
@a is a user defined parameter where as @@<param-name> such as @@Identity is a system defined parameter.
Intelligence is measured by common sense not by how many scholarly books you read.
|
|
|
|
|
hi
@ stands for local variable
@@ Stands for System variables
eg:
declare @name as nvarchar
set @name='hello'
print @name
select @@VERSION
Returns the date, version, and processor type for the current installation of Microsoft® SQL Server
|
|
|
|
|
how can i use the for loop in stored procedure
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
