|
|
I am creating a dynamic pivot-table with web - sql-server 2008
When the pivot-table is created i want to rename columnname.
I am execute a stored procedure, sp_rename..... from the web-page.
Web-page - select variablesnames
Web-page - variablesname --> short columnname
SQl-server - create pivot table (dynamic sql, short columnnames - 4000 char limit)
Web-page/Sql-server - rename short columnnames to variablesnames
The problem is the permission to execute sp_rename .
How/where can I adjust permission for the the "webuser" in sql-server ?
Thanks in advance
Clas
|
|
|
|
|
I am assuming that you need this table only for display on your form, in which case why don't you rename the columns in the UI! Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have 2 tables in my data base..
1. User_Group table
2. Function_Group table
Example:
Function_Group table :
Groupid Applicationid Functionid
LCUser1 LCDocMgmt LC01
LCUser2 LCDocMgmt LC02
LCUser1 LCDocMgmt LC03
LCUser3 LCDocMgmt LC04
LCUser3 LCDocMgmt LC05
LCUser2 LCDocMgmt LC06
LCUser2 LCDocMgmt LC08
LCUser1 LCDocMgmt LC09
User_Group table:
Userid Groupid
900410 LCUser1
900846 LCUser1
900411 LCUser3
Now i use the following query to retrieve the Groupid for a particular Userid :
Select ug.groupid from User_Group ug, Function_Group fg where fg.ApplicationID='LCDocMgmt' and fg.GroupID = ug.GroupID and ug.UserID='900846'
The result that i get on executing the above query is:
groupid
LCUser1
LCUser1
LCUser1
Actually i Dont require repetition of records. If there is 2 distinct records i just want the 2 distinct records without repetition.. can any one plz help me..
Thanx in advance....
Regards,
Tash
|
|
|
|
|
<<tash18>> wrote: distinct records
You answered your own question.
Select DISTINCT ug.groupid from User_Group ug, Function_Group fg where fg.ApplicationID='LCDocMgmt' and fg.GroupID = ug.GroupID and ug.UserID='900846'
|
|
|
|
|
Thanx alot... That was really a Dumb question from my side i knew that... Anyways a million thanx..
Regards,
Tash
|
|
|
|
|
No problem. I also don't think it was particularly dumb compared to the general par.
|
|
|
|
|
If I do something like:
Update Field1=something1, Field2=something2
Where Field3=123;
I always index Field3, but I'm not sure if I need to index Field1 and Field2 too?
Thanks!
|
|
|
|
|
AFAIK you're query is not selecting based on field1 or field2, so it would not benefit from indexing those fields. Future queries obviously may or may not benefit from indexing.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.
|
|
|
|
|
Thank you. Just double checking.
|
|
|
|
|
The other argument is that if you indexed field1 & field2, you would require more data writes. Both the main table data would be updated and the index data would be updated. Even worse, because you update data that is indexed you could cause all kinds of index reorganization when the value of field1 changes from A to Z.
My 2 cents.
|
|
|
|
|
That makes sense. Good info, ty.
|
|
|
|
|
Hello,
I need to learn about SSIS (Microsoft SQL Server Integration Services) interface with .NET and stored procedures. I am looking for a book to learn from but also use as a reference book. I have Knight's 24-Hour Trainer but it is not exactly what I want.
Thanks
djj
|
|
|
|
|
Hello,
i need help for a stored procedure that allows me to concat string fields from a table column when they have the same field names.
i did it when the fields were int and it worked perfectly
code snippet.
SELECT X.FieldName,SUM(CAST(Y.Value AS float))
FROM L INNER JOIN
C ON L.Id = C.Id INNER JOIN
B ON L.Id = B.LocalityId INNER JOIN
FormInstances ON B.FormInstanceId = FormInstances.Id INNER JOIN
Y ON FormInstances.Id = Y.FormInstanceId INNER JOIN
X ON Y.FieldId = X.Id
WHERE (C.AId = @aid) AND (FormInstances.FormId = 6)
GROUP BY X.FieldName
this got me the sum of each field in Y based on X.fieldname.
now i want to do the same when the values in Y are not texts and not numbers. i tried conctenation(+) but was not successful with.
thanks in advance.
|
|
|
|
|
I do not understand what you want. However keep in mind that if you concatenate a null value the result will be null.
'John' + NULL + 'Doe' = NULL
Try using the ISNULL function.
Also you cannot mix types
'John' + 1.0
will give an error. You need to make all compatible types.
Good Luck
djj
|
|
|
|
|
Thanks so much for the reply.
1.all the data types are verified to be strings(text)
2. i want to concatenate the strings by grouping them on a fieldid.
when i pick an id i want to concat all values corresponding to the id.
(this is the structure.
i have table A which contains list of towns.
table B contains list of regions.
table C contains list of attributes of the towns
table D contains the values of the attributes
the task is to provide a summary for towns under a region. grouped
under the various attributes.
for Region A i have Attribute A: then the concatenated values for
all attributes A for each town.
thanks once again.
|
|
|
|
|
Do you really want to concatenate columns or just create a table?
-- Table
SELECT A.Town, B.Region, C.Attribute AS AttribCode, D.Attribute
FROM TableA A
INNER JOIN TableB B
ON A.ID = B.ID
INNER JOIN TableC C ........
--or
-- Concatenate
SELECT A.Town + ISNULL(B.Region, '') + ISNULL(C.Attribute, '') + ISNULL(D.Attribute, '') AS TownInfo
FROM TableA A
INNER JOIN TableB B
ON A.ID = B.ID
INNER JOIN TableC C ........
If you have a one to many relationship and want the say multiple attributes for a given town you will need to Google that as it has been covered in various places.
I hope this helps,
djj
|
|
|
|
|
Hi! Is there an ORM that provides streaming support? What I mean by streaming is that the users can see the rows/objects being loaded one-by-one/partially (Think of SQL Management Studio when the query result is thousands of record). I can't implement paging since our data changes very fast.
Background:
- DB contains >250 million of data (but I will limit the result set to 10K max)
- Query can take minutes even hours
- Can't change DB architecture
Thanks
|
|
|
|
|
pebrian27 wrote: but I will limit the result set to 10K max
Why, how useful is 10k records in a UI, why not limit to 100 or 50 or something the user can work with.
pebrian27 wrote: Query can take minutes even hours
This does not make sense when servicing a UI. A select statement that takes hours, ridiculous, your design is screwed.
If the query takes hours the user has got bored and gone home. Change you design to data dump, output the data via a job/SSIS package and make it available in a shared folder. We extended SSRS to do just this sort of work, anything more than 1k rows is a lousy design for a UI and a report.
[edit] Checked your profile, web developer, so use an FTP/download scenario[/edit]Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Our users are also technical people and they need ~10k records.
Also, the users get to create the queries (ad-hoc queries). My current ORM is Nhibernate so they use HQL which is very similar to SQL.
Mycroft Holmes wrote: pebrian27 wrote:
Query can take minutes even hours
This does not make sense when servicing a UI. A select statement that takes hours, ridiculous, your design is screwed.
Oops... Queries will only take hours if I don't do the 10k limit. Currently, querying 500 records takes 2 mins. Thats why I want streaming support so that if they get bored, they can press cancel but still get partial result.
Our systems will be upgraded after 8 months.
|
|
|
|
|
Nhibernate isn't the cause of the slow query and there are no joins in the queries
|
|
|
|
|
500 records in 2 mins with no joins is very very slow. It would appear that either your server is useless or the query optimizer is unable to use indexes and is doing a table scan of all 250million records. Have you looked at the query execution plan to see if there are indexes that you can add?
|
|
|
|
|
This is probably easy...I am just brain dead...I am using Ms Sql 2008, and given the following two tables:
Table1
---------------
Field1 Field2
3 55
3 55
4 55
5 55
3 66
3 66
3 77
4 77
3 88
Table2
---------------
Field1 Field2
3 9
4 10
5 11
Table1 and Table2 are linked together by the Field1's...I get a parameter passed in like "9" for Table2's Field2...how do I return all of the records from Table1 of just the 66's and the one record of 88?
|
|
|
|
|
SELECT Field2 FROM TABLE1<br />
WHERE Field1 = (SELECT Field1 FROM Table2 WHERE Field2 = 9)<br />
AND Field2 IN(66,88) I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Thank you, but I don't want to hard code the 66 and 88 as those values I constantly changing. I just wanted those records as they don't have other linking fields of the 4 and 5 for example.
|
|
|
|