|
In SQL Server, you have the CASE statement to do this:
SELECT CASE IsConsultant WHEN -1 THEN 'Yes' ELSE '' END AS Consultant
|
|
|
|
|
Thanks for that. Its something the book I've got didn't touch upon.
The FoZ
|
|
|
|
|
I'm developing a reporting system with SQL Server Reporting Services 2005.
I have created a user ("ru3") at my host server. Now, I want him to be just like administrator user, except he wont see one of the reports.
But, when I click on a field to make a drillthrough report, I get 'rsAccessDenied'.
"The permissions granted to user 'ru3' are insufficient for performing this operation. (rsAccessDenied) "
|
|
|
|
|
Hi,
I have quotes that are translated in many different languages (English, French, Arabic, etc), I want to design a database that holds these quotes, each quote has an ID.
which design is better:
1. having one table with columns (ID, English, French, Arabic, ...)
OR:
2. Having many tables like English(ID, Text), French(ID, text), ... ?
Personally I see the first design more consistent and better in saving database space, am I right or wrong? and why?
BTW: this will be a part of an extremely busy website, thousands of database requests per minute, so performance is critical.
Thanks 
And ever has it been that love knows not its own depth until the hour of separation
|
|
|
|
|
Hi,
if the normal usage is having an ID and needing the text in a single language, I would
go for the "one table per language" approach. It would make it easier to add/remove
a language, and expect it to be better performance wise.
|
|
|
|
|
There are obvious tradeoffs between the two approaches and which one is better will depend on your usage. Since you said this will be an "extremely busy website, thousands of database requests per minute, so performance is critical.", then you need to look at caching the data so you don't have to continually make database hits for the same quote/language combination.
To answer the question about which one is more consistent and better conserving space...they are both consistent (since they both follow a pattern) however the first approach is more normalized so you won't have the repeated data contained in each table.
Scott Dorman Microsoft® MVP - Visual C# | MCPD
President - Tampa Bay IASA
Hey, hey, hey. Don't be mean. We don't have to be mean because, remember, no matter where you go, there you are. - Buckaroo Banzai
[ Forum Guidelines][ Articles][ Blog]
|
|
|
|
|
Thanks for your comment.
I really don't care much about space, but I care extremely about speed, I need my database requests to be served fast, no matter what the size is.
Could you please make a comment about this because I really need an extremely efficient design
Thanks
Mohammad
And ever has it been that love knows not its own depth until the hour of separation
|
|
|
|
|
Mohammad A Gdeisat wrote: I really don't care much about space, but I care extremely about speed, I need my database requests to be served fast, no matter what the size is.
Again, some of this will depend on the caching mechanisms being used. The best way to achieve performance is going to be the use of an index on the ID field and stored procedures to lookup/retrieve the quote text. On the front-end website you always want to use the ID and the users culture. The drawback is that somewhere along the call chain from the app to the database you are going to need to decide which table needs to be queried (or column needs to be retrieved) based on the culture. This means a logic block in the code or in a stored procedure.
Your requirements (at least for this part) seem simple enough, create and test both methods and measure performance. It won't be exact, but you'll get an idea of relative performance between the two. The biggest factor no matter what is going to be using the ID for everything, having a good index, and caching.
Scott Dorman Microsoft® MVP - Visual C# | MCPD
President - Tampa Bay IASA
Hey, hey, hey. Don't be mean. We don't have to be mean because, remember, no matter where you go, there you are. - Buckaroo Banzai
[ Forum Guidelines][ Articles][ Blog]
|
|
|
|
|
If you are using SQL Server 2005 have a look at partioned tables. You can then have 1 physical table partitioned by lanaguage across serveral physical disks. I haven't used it for languages, but I have used it very sucessfully to split data by time periods (such as year) with very good performance gains on large databases.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I want to Refer two tables in fuzzy lookup component.I have to map incoming Columns with more than one reference table's column. can anyone tell how can i achieve this?
Thanks in advance
shsri
|
|
|
|
|
Hi
I have a table with columns like
Table1:
1.ID as int
2.TableID as int
3.ColumnName Varchar(50)
4.ColumnValue Varchar(50)
Here TableID is not unique.I want to create a new table.
Each ColumnName value of Table1 should become Column of New
Table.
Each ColumnValue should become the row value of corresponding
column.
Table1 values:
ID TableID ColumnNAme ColumnValue
1 1 FirstNAme Javio
2 1 LastName Choprakhush
3 1 DateOfBirth 13/09/1986
4 1 Phone 6222226779
And i want to create a table like this
FirstName LastNAme DateOfBirth Phone
JAvio Choprashush 13/09/1986 6222226779
Can anyone Help ME?
Thanks in advance
sri
|
|
|
|
|
This sould put you on the right track
create table UserArea(Country varchar(20))
insert into UserAreaselect 'India'
union all
select 'USA'
union all
select 'India'
union all
select 'UK'
DECLARE @SQL nvarchar(4000)
SET @SQL=''
SELECT @SQL= @SQL +'SUM(CASE WHEN Country=''' + a.Country + ''' THEN 1 ELSE 0 END) AS [' + a.Country + '],'
FROM (select distinct Country from UserArea) as a
select @SQL = left(@SQL,len(@SQL)-1)
SET @SQL='SELECT ' + @SQL + ' FROM UserArea'EXEC(@SQL)
drop table UserArea
Bob
Ashfield Consultants Ltd
|
|
|
|
|
You can try using PIVOT if this is SQL2005.
|
|
|
|
|
Hi All,
While calling the SP which updates the TABLE(SQL SERVER 2000) thru Stored Proc,
i'm getting an error "x parameter was not supplied".
x is the datetime value. I ll pass the system time in that parameter.
I'm facing this problem only for the Datetime Values.
No probs with the code as everything works fine in my system where i developed this app. But after deploying in the client machine i'm facing this prob.
Dont ask me how this is happening, but its happening.
Can anyone help me in this issue.
--Raam
|
|
|
|
|
Without the stored proc code its hard to be certain, but I suspect your proc expects parameter X, something lke
create proc t1 (@X datetime)
as
.....
when you execute the proc it expects @X to be supplied.
If it was defined as
create proc t1 (@X datetme = getdate())
as
.....
you would not need to pass @X unless you wanted some other date.
Hop this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Bob,
But how come its working on the other system.
--Raam
|
|
|
|
|
Well, with absolutley no code to look at, its a best bet. Post your proc & vb code and someone may be able to help
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks a lot BOB,
That was the problem due to the difference in the time between these two machines...
I changed the format... then it works...
--Raam
|
|
|
|
|
Pleased your are sorted
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi everyone,
I've been trying to make the XML Schema of my SQL Server Database and currently I've found this to generate an XSD schema,
create table Person
(
Age int not NULL check( Age > 0) ,
Height numeric(10,2) not NULL check( Height > 5),
Gender varchar(5) not null check( Gender in (’M', ‘F’, ‘O’)),
BirthDate datetime null,
)
DECLARE @schema xml
SET @schema = (SELECT * FROM Person FOR XML AUTO, ELEMENTS, XMLSCHEMA(’PersonSchema’))
select @schema
OUTPUT:
But the problem is that this is generating the schema of only one table where as I want to generate the schema of the whole Database, Can u suggest some quick solution for that?
Thanks in advance
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
Hi Everyone,
I am developing an asp.net c# application. I use this environment
MySql 4.1
Driver 5.2
Provider MySql.Data.MySqlClient
Visual Studio 2008 Framework 3.5
Execute this request with a DbCommand doest work;
StringBuilder oStringBuilder = new StringBuilder();
oStringBuilder.Append("LOCK TABLES <table> WRITE;");
oStringBuilder.Append("SET @maxid = IFNULL((SELECT max(<table>.ID)+1 FROM <table>), 0);");
oStringBuilder.Append("INSERT INTO <table>(ID, USERID, DTCREATED, DTEDITED");
oStringBuilder.Append("VALUES (@maxid , {0}, SYSDATE(), NULL);");
oStringBuilder.Append("UNLOCK TABLES;");
DbCommand cmd = cnx.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = szSQL;
cmd.ExecuteNonQuery();
MySql.Data.MySqlClient.MySqlException: Parameter '@maxid' must be defined.
à MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlStream stream, String parmName)
à MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlStream stream)
à MySql.Data.MySqlClient.Statement.BindParameters()
à MySql.Data.MySqlClient.Statement.Execute()
Please help me regarding this. The best elaborated answer with some examples would be appreciated.
Thanks in Advance,
B. Jourdain
modified on Friday, June 20, 2008 2:44 PM
|
|
|
|
|
hi every body
i hava a problem with exception handling when i execute my stored procedure which is
/////////////////////////////////////////////////////////////////////////////////
ALTER Procedure [dbo].[PaginRec]
(
@PageNum int,
@NumOfRec int
)
as
begin try
begin with DIVRES as (
select Row_Number() over(order by Responsible desc)
as row, Responsible , Division
from Bulist)
select Responsible,Division
from DIVRES
where row between
(@PageNum - 1) * @NumOfRec + 1 and @PageNum*@NumOfRec
end
end try
begin catch
select 'Please enter the right number of parameters'
end catch
///////////////////////////////////////////////////////////////////////////////////
PaginRec
(press F5 without any input parameter)
it gives an error msg like
Msg 201, Level 16, State 4, Procedure PaginRec, Line 0
Procedure or Function 'PaginRec' expects parameter '@PageNum', which was not supplied.
my question is that how can i catch this exception and display my own msg like
Please enter the right number of Parameters or whatever it is.
thanx in adv
learner
|
|
|
|
|
This exception occurs before your stored procedure is called, So you don't have control over it from the stored procedure itself.
You can handle it from the calling application or make all SP parameters have default values equal to NULLs, and check the values in the stored procedure. If they are NULLs then you can raiserror[^]
|
|
|
|
|
thanx for ur expalaination
actually someone already told me that set varaibles as null
but i wasnt satisfied any way now i got it.
and one thing more if u have any helpfull example for try catch block cos lot of simplest form is availabel on different sites but i m luking for effective one.
fnx
learner
|
|
|
|
|
Hey, i have a table with several columns Q1 to Q9, ID, datetime, ID is the same for a given group of data. Each column Q1 to Q9 can have up to 12(-2 - 9) values in it, and 10 to hundreds of thousands of rows per ID(dont ask it does make sense i just dont feel like explaining it). and i'm trying to make a report in CR 2008 but i cant get it generate graphs of all the occurances of values in all 9 columns w/out 9 graphs. so the only solution i can think of is to change the columns in to rows and make the differnt possible values columns like
Question -2s -1s 0s 1s 2s ...9s
Q1 3 0 0 2 1 (a count of the number of times each value appears)
i'm managed it with this, long but simple, and suprisingly speedy
SELECT 'Q1' AS Question,
COUNT(Q1) - COUNT(NULLIF(Q1,-2)) AS '-2s',
COUNT(Q1) - COUNT(NULLIF(Q1,-1)) AS '-1s',
COUNT(Q1) - COUNT(NULLIF(Q1,0)) AS '0s',
COUNT(Q1) - COUNT(NULLIF(Q1,1)) AS '1s',
COUNT(Q1) - COUNT(NULLIF(Q1,2)) AS '2s',
COUNT(Q1) - COUNT(NULLIF(Q1,3)) AS '3s',
COUNT(Q1) - COUNT(NULLIF(Q1,4)) AS '4s',
COUNT(Q1) - COUNT(NULLIF(Q1,5)) AS '5s',
COUNT(Q1) - COUNT(NULLIF(Q1,6)) AS '6s',
COUNT(Q1) - COUNT(NULLIF(Q1,7)) AS '7s',
COUNT(Q1) - COUNT(NULLIF(Q1,8)) AS '8s',
COUNT(Q1) - COUNT(NULLIF(Q1,9)) AS '9s'
FROM table
UNION
-- repeat query/union for all 9 Q columns
personally i think this way sucks, and there has to be a better one, but i'm at a loss as to what it is. Any help would be appreciated, oh and because i'll know very little about the data before running the report and it will be accessed from a web app i dont think a temp table would work very well either, and it wouldnt be as simplistic.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
