|
Hi All,
I want to check if the user exists on a database with domain and user registration "XXXCXXX-XXX\webxxxxxqa_iispool", if doesn't exists create one, if it exists then give user the roles to access the application db_owner or public on few databases on the server using user login properties by using the script.
I know we can do this by using Management Studio, is there any way to write it all as a script to run it when we have multiple databases. Any help is appreciated, link, suggestion or code snippet anything. Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Anything you can do in SSMS can be done by script, you could put a stored proc in the Master database and run it from there. When you are in SSMS there is an icon to create a script on most actions, use this to jump start you requirements.
Most results from a google search of the operation you need will give you sample scripts that you can use, try these[^].
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
indian143 wrote: ... give user the roles to access the application db_owner ...
That's a very bad idea. The user should only have the permissions required by your application.
I assume your application doesn't need to modify the structure of tables, views, stored procedures, etc.? And it doesn't need to modify the users and permissions? So why give it those permissions?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I want to manage 10 major tables with 100 millions record each, each row have about 20 fields/colums, so how to manage these 10 interlinked tables.
Can MySql handle such large data?
|
|
|
|
|
|
Member 12069869 wrote: Can MySql handle such large data?
Yes. Of course that doesn't mean your application can. Nor that you can't mess it up by failing to design correctly.
Member 12069869 wrote: each row have about 20 fields/colums
Things that matter
- What is the maximum, average and variance for the row size (your data not db overhead.)
- What is the realistic growth rate? Realistic means that, not pie in the sky sales/marketing claims.
- What will the system be querying on most? Unless your growth rate is very big this is more relevant.
- Can data be aged? Thus will the above queries be mostly for the last month, or last three months versus spanning the entire data store
|
|
|
|
|
Well, this started as a basic request for all records, then I ran into a size limit with the web service, to I added pagination to the call. Next I could not project directly into a list(of, so I took the statement out
subscribers.ToList().Select(Function(cs) New ws_subscribers_list With
Now I just get 1 record returned. I don't have experience with IQueryable so I'm not sure how to proceed on this.
Public Shared Function load_subscribers( _
ByVal pPageIndex As Integer,
ByVal pPageSize As Integer,
ByRef pResults As List(Of ws_subscribers_list)) As Integer
Dim pValue As Integer = 0
Dim context As New hx5Context()
Dim subscribers As IQueryable(Of CRM_SUBSCRIBERS) = context.crm_Subscribers
'Set the Sort Order of the request
subscribers = subscribers.OrderBy(Function(m) m.Name)
'Get a Single Page of Data
If (pPageIndex = 1) Then
subscribers = subscribers.Take(pPageSize)
Else
subscribers = subscribers.Skip((pPageIndex - 1) * pPageSize).Take(pPageSize)
End If
pResults = _
subscribers.Select(Function(cs) New ws_subscribers_list With
{
.SubscriberID = cs.SubscriberID,
.CustomerID = cs.CustomerID,
.Name = cs.Name,
.EmailAddress = cs.EmailAddress,
.Source = cs.Source,
.Subscribe = cs.Subscribe,
.DateCreated = cs.DateCreated,
.Status = cs.Status
}
).ToList()
pValue = pResults.Count()
Return pValue
End Function
|
|
|
|
|
Check how many records are in your table, and what value has been passed for the pPageSize parameter.
I'd be inclined to have the function return the List(Of T) directly, rather than using a ByRef parameter. The caller can always access the list's .Count property if they want to know how many records have been returned.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I forgot that IQueryable just returns a query, that is not executed until you use it.
I ended up splitting it into 2 with the Page index because I didn't know how to write the expression in 1 shot. Not sure if it's kosher, but feedback will tell me I guess.
Thanks Richard! 
Public Shared Function load_subscribers( _
ByVal pPageIndex As Integer,
ByVal pPageSize As Integer,
ByRef pResults As List(Of ws_subscribers_list)) As Integer
Dim pValue As Integer = 0
Dim context As New hx5Context()
If (pPageIndex = 1) Then
pResults = _
(
From cs In context.crm_Subscribers
Order By cs.Name
Take (pPageSize)
Select New ws_subscribers_list With
{
.SubscriberID = cs.SubscriberID,
.CustomerID = cs.CustomerID,
.Name = cs.Name,
.EmailAddress = cs.EmailAddress,
.Source = cs.Source,
.Subscribe = cs.Subscribe,
.DateCreated = cs.DateCreated,
.Status = cs.Status
}
).ToList()
Else
pResults = _
(
From cs In context.crm_Subscribers
Order By cs.Name
Skip ((pPageIndex - 1) * pPageSize)
Take (pPageSize)
Select New ws_subscribers_list With
{
.SubscriberID = cs.SubscriberID,
.CustomerID = cs.CustomerID,
.Name = cs.Name,
.EmailAddress = cs.EmailAddress,
.Source = cs.Source,
.Subscribe = cs.Subscribe,
.DateCreated = cs.DateCreated,
.Status = cs.Status
}
).ToList()
End If
pValue = pResults.Count()
Return pValue
End Function
|
|
|
|
|
@GroupId int,
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@RegionCode varchar(30) = NULL
AS
BEGIN
DECLARE @AgencyTable TABLE (GpoId int, OfcId int, Total_Bills BIGINT, Bill_Value BIGINT, Commission BIGINT, Total_Amount BIGINT)
SET @EndDate = convert(datetime, convert(Varchar(12), @EndDate, 106) + ' 23:59:59PM')
INSERT @AgencyTable (GpoId, OfcId, Total_Bills, Bill_Value, Commission, Total_Amount)
SELECT bil.GroupId, Bil.SubOfficeId , isnull(COUNT(Bil.ConsumerNumber),0)
,ISNULL(SUM(Bil.C_Amount),0)
,ISNULL(SUM(Bil.Commission),0)
,ISNULL(SUM(Bil.C_Amount),0) - ISNULL(SUM(Bil.Commission),0)
FROM BillTxnSO as Bil inner join pp_offices ofc On bil.GroupId = ofc.Group_Id and bil.SubOfficeId = ofc.OfficeCode
Where bil.GroupId = @GroupId AND TransDate BETWEEN @StartDate AND @EndDate
Group by bil.GroupId, Bil.SubOfficeId
SELECT ofc.OfficeName as SubOffice_Name , isnull(gpo.Total_Bills,0)as Total_Bills , isnull(gpo.Bill_Value,0)as Bill_Value , isnull(gpo.Commission,0) as Commission, isnull(gpo.Total_Amount,0) as Total_Amount
FROM @AgencyTable gpo
INNER JOIN pp_offices ofc On ofc.Group_Id = gpo.GpoId and gpo.OfcId = ofc.OfficeCode
ORDER BY ofc.OfficeName
END
|
|
|
|
|
|
I need to wrap a stored procedure within the transaction and this stored procedure spans through 6 to 7 tables. So, there are high chances of dead lock in our application as we have more updates on tables. What is the best approach to handle transaction here?
|
|
|
|
|
Deadlocks are caused when a thread tries to access a piece of information which is currently held by another thread. Why don't you execute on procedure and keep the rest of the threads on wait.
In many ways, a Mutex may help you overcome the deadlock. You can, this way, handle what happens when there are multiple threads using the resources. I would still recommend, that you try to execute "One SQL Procedure" at a time. Keep the rest of the threads (or requests) on hold.
https://colinlegg.wordpress.com/2014/05/06/enforcing-mutex-on-a-sql-server-database/[^]
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
Afzaal Ahmad Zeeshan wrote: Deadlocks are caused when a thread tries to access a piece of information which is currently held by another thread.
Almost.
A deadlock[^] occurs when two or more competing threads are waiting for each other to release a lock.
For example:
- Thread 1 locks X;
- Thread 2 locks Y;
- Thread 1 tries to acquire a lock on Y - waits for thread 2 to release;
- Thread 2 tries to acquire a lock on X - waits for thread 1 to release;
The DBMS can usually detect the deadlock, and will kill the thread which has done the least work.
NB: There's nothing wrong with trying to access something locked by another thread, so long as the current thread doesn't currently hold any locks which would prevent that thread from finishing.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I created a procedure to stop users from submitting all invoices having zero value. But the challenge am having now is to create exception in this procedure so that three authorized users can submit all such zero invoices. Please see below the created procedure:
CREATE OR REPLACE PROCEDURE SIMS_ORN_LIVE.EV_INV_ITEM_VALUE_CHECK_SIMS(P_INVH_SYS_ID NUMBER )
AS
CURSOR C1 IS
SELECT Sum(INVI_FC_VAL) FROM OT_INVOICE_ITEM
WHERE INVI_INVH_SYS_ID=P_INVH_SYS_ID;
M_VAL NUMBER;
BEGIN
IF C1%ISOPEN THEN
CLOSE C1;
END IF;
OPEN C1;
FETCH C1 INTO M_VAL;
CLOSE C1;
IF Nvl(M_VAL,0)=0 THEN
RAISE_APPLICATION('OP',585844 ,'YOU ARE NOT AUTHORIZED TO SUBMIT INVOICES WITH PROMO ITEMS ONLY, KINDLY CONTACT ED-OPS, MR. LAWRENCE, OR MRS NWABUIKE. ',' ',' ',' ',' ',' ',' ',' ');
END IF;
END ;
/
|
|
|
|
|
This sort of business rule should be in your application not the database. Wrong tool for the job.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I have a Constraint as below, I need to keep the same Index but remove this Unique Constraint, can anybody please help me? Any help like a link, suggestion or code snippet, anything helps.
Thanks in advance.
ADD CONSTRAINT TableName_UniqueConst UNIQUE (ShortName, Name, SecondaryName, Address1, Address2, Address3);
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hello,
I Am beginer in asp.net and Sql server , i want to do a query to calculate student grade point and grade using the condition bellow.
WHEN EXAMS BETWEEN 75 AND 100 THEN 'A'
WHEN EXAMS BETWEEN 70 AND 74 THEN 'AB'
WHEN EXAMS BETWEEN 65 AND 69 THEN 'B'
WHEN EXAMS BETWEEN 60 AND 64 THEN 'BC'
WHEN EXAMS BETWEEN 55 AND 59 THEN 'C'
WHEN EXAMS BETWEEN 50 AND 54 THEN 'CD'
WHEN EXAMS BETWEEN 45 AND 49 THEN 'D'
WHEN EXAMS BETWEEN 40 AND 44 THEN 'E'
WHEN EXAMS BETWEEN 40 AND 0 THEN 'F'
please helo me with this issue than you .
|
|
|
|
|
Aliyu Usman wrote: please helo me with this issue
Sure what have you tried so far.
Hint
Select from the results table and group by the result values or use a CASE statement and then group by the grade.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hello,
bellow is the query i have tried
SELECT
ADMISSION_NO,
FULL_NAME,
EXAMS,
CASE
WHEN EXAMS BETWEEN 75 AND 100 THEN 'A'
WHEN EXAMS BETWEEN 70 AND 74 THEN 'AB'
WHEN EXAMS BETWEEN 65 AND 69 THEN 'B'
WHEN EXAMS BETWEEN 60 AND 64 THEN 'BC'
WHEN EXAMS BETWEEN 55 AND 59 THEN 'C'
WHEN EXAMS BETWEEN 50 AND 54 THEN 'CD'
WHEN EXAMS BETWEEN 45 AND 49 THEN 'D'
WHEN EXAMS BETWEEN 40 AND 44 THEN 'E'
WHEN EXAMS BETWEEN 40 AND 0 THEN 'F'
END AS GP
FROM R_Upload_2
|
|
|
|
|
And what is the problem?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Aliyu Usman wrote: WHEN EXAMS BETWEEN 40 AND 0 THEN 'F'
You need to reverse this so that you have:
BETWEEN 0 AND 40
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 24-Nov-15 8:23am.
|
|
|
|
|
I have created a CLR which uses the newtonsoft json parser.
I need to deploy the CLR. I have created an assembly for the parser dependency:
CREATE ASSEMBLY [Newtonsoft.Json] FROM 'C:\share\Release\Newtonsoft.Json.dll' WITH PERMISSION_SET = UNSAFE
The above statement throws an error:
Assembly 'Newtonsoft.Json' references assembly 'system.runtime.serialization, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(failed to retrieve text for this error. Reason: 15105)). Please load the referenced assembly into the current database and retry your request.
I am using version 7.xx of the parser.
I am targeting .NET 3.5
What is the proper procedure for deploying the CLR to sql server 2008 r2?
|
|
|
|
|
hi
i have this table:
id | Name | Age
==================
1 | AAA | 22
1 | AAA | 22
2 | BBB | 33
2 | BBB | 33
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
i need to delete from this table all the duplicate records
and leave only one record.
the table will looks like this:
id | Name | Age
==================
1 | AAA | 22
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
i work with sqlCE for Mobile
thanks
|
|
|
|
|
This is very much a solved problem, the minimum of research [^] would get you plenty of examples
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
