Hi,
actually a sever has a parallelism of 4 I would like to set the parallelism for a specific user to 2 without changing the code of the users application.
Is this possible.
As far as I understand with plan guides you just provide sql statements.
Need I to find all queries from the user, and add plan guides for all the queries, or tis there a more elegant way to do it.
br
fari
Hi,
I have several reports for users to view on our Intranet. After installation of SQL 2005 SP2 patch, I cannot delete user or user's authority from Report in Properties Tab. An error message was shown on the status bar. It indicated that JavaScript Error: 'Return' statement outside of function. Seems something wrong with the 'Delete' funciton in SQL 2005 after update. The other functions worked fine. Could you point me out how to fix it or need to install any updates / hotfix. Thanks a lot!
Regards,
Kenneth Lai
Programmer
Error Pic
Message Box
I'm new to SQL Server.
I've an online Database on a server. I've my own user name like "AKR". I want to create every table under that user. But what happens is whenever I create a table online it goes under "DBO" user name. How to avoid that?
Thanks
How can i add a windows user to sql server to access specific database with read and write permissions.
Thank you very much.
Greetings all
My question is I want to be able to create a report specific to each individual user. More specifically I want to be able to create one report and once the user logs in with the appropriate credentials, I want the report to populate based on those credentials. Another important aspect is that each individual only sees their data. So once again I need to report to populate from the database based on the users logon credentails
any suggestions??
thanks in advance
KM
Hi,
I want to receive a mail when a specific user connects.
How can this be done best? Raise an alert? How?
Btw, the 'Login auditing' is set to 'Failed logins only'.
Suggestions are very welcome!
Cheers,
E
Hi:
I like to know how I may store some user-specific data at the beginning of a session and retrieve the same data later during the session in a SQL command. For those of you familiar with ORACLE, I am trying to mimic the SET_CLIENT_INFO and GET_CLIENT_INFO functions of ORACLE's DBMS_APPLICATION_INFO package on SQL Server.
Any suggestions?
thanks,
Peter
Hi,
I want to receive a mail when a specific user connects.
How can this be done best? Raise an alert? How?
Btw, the 'Login auditing' is set to 'Failed logins only'.
Suggestions are very welcome!
Cheers,
E
i am using sql server 2008R2 and i want to restrict my application user to view only the data(ships) related to him only.
We have database and many vessels with many owners, basically wants the owner could only view his owned vessels not the vessel owned by another owner.
I'm trying to get specific permissions per user on each SQL database, would you let me know if it could be achieved e.g on an object level?
View 5 Replies View RelatedIn my application (infopath browser forms), First I get current user name thru System.Environment.UserName, after that, i want to check whether this user has access (read & write) permission to certain database, How do I do that?
One way I tried before is to get all the users and stored the names in an array for comparing in my program, but can not figure out the SQL command (to get the database users), if there it is, please kindly tell me.
Or if I can just pass the current user to check?
Thanks in advance.
Hello Everyone,
I have an web application where the users has to run SQL scheduled jobs from the webpage. How to assign permission to a specific user to run specific jobs without making them a member of a Sysadmin role?
Any ideas you all smart people?
Thanks in advance!
Jannat.
I need to do some operations on a table when any user dont work with it.How do i do it?
Do i lock the table? and is it possible?(I dont want to deny permission from a user because cause error)
Can i do it with a open transaction ?
I created a new login and then created a new user [COM] in DB with default schema pointing to [COM]
I created then schema [COM] WITH AUTHORIZATION [COM]
I want this [COM] user to have all permissions it needs on [COM] schema only. How do I do that? When I try to create table [Com].Table it gives me permission denied.
What am I missing?
I need to do some operations on a table when any user dont work with it.How do i do it?Do i lock the table? and is it possible?
View 8 Replies View RelatedI have a user who needs access to views like(dbo.viewnameabc1,dbo.viewnameabc2 and so on...) dbo.viewnameabc* and anytime the user creates the view he already have the permission to view those views....
View 3 Replies View RelatedI know you can change the max degree parallelism server wide, but can you do it on the fly for one query? I know... trust the query processor but when I turn it off for this one sp, my query goes from 3 seconds to 0 and I got this ex-MS guy in here telling me there is a way, but he does not remember how.
I want him to simplify the sp or have his project's DBA do it, and I even offered to take a hack but.... you know.
Does anyone know about sqlserver's Parallelism.
a query without parallelism takes much less time as the one with parallelism, in my case it's 6 times faster without parallelism. If that's the true.
What do we need parallelism for?
Any ideas
Thanks
I have a function that returns a table of information aboutresidential properties. The main input is a property type anda location in grid coordinates. Because I want to get only acertain number of properties, ordered by distance from thelocation, I get the properties from a cursor ordered by distance,and stop when the number is reached. (Not really possible todetermine the distance analytically in advance.) The cursor alsoinvolves joins to a table of grid coordinates vs. postcodes (theproperties are identified mainly by postcode), and to a tablethat maps the input property type into what types to search for.Opening the cursor typically results in the creation of six toeight parallel threads, and takes approx 1 second, which is abouthalf of the total time for the function.Recently the main property table grew from 4 million to 6.5million records, and suddenly the parallelism is lost. Takingthe identical code and executing it as a script gives parallelism.Turning it into a SP that inserts into a #temp table and thenselects * from that table as the last statement also givesparallelism. But when it's in the form of a function, there isonly one thread -- and the execution time has gone from ~2 secto ~8 sec. I updated the statistics on the table, but stillno parallelism.I could turn it into a SP easily enough, but that would involvea change to the C++ program that calls it, which takes a whileto get through the pipeline. In the meantime, is there some wayto induce the optimizer to use parallelism? It used to.
View 3 Replies View Relatedhi,i've set 'max degree of parallelism' to 1 because some sql request hanged.Now when i connect, how can i set the parallelism to 4 for a session.Is there a command like this :'alter session set max degree of parallelism 4' ?ThanksPaul
View 6 Replies View RelatedIf SQL Server is designed for multi processor systems, how can runninga query in parallel make such a dramatic difference to performance ?We have a reasonably simple query which brings in data from a few nonecomplex views. If we run it on our 2x2.4Ghz Xeon server it takes 6minutes plus to run. If we run this on the same server withOPTION(MAXDOP 1) at the end of the same query it takes less than asecond.Examining the execution plan, the only difference I have been able tosee is that parallelism is taking up 96% of the run time when usingtwo processors. This drops when using the one so a sort takes up thevast majority of the time for the query to run.OK, so running in parallel should mean that it's run in various partsand then 'joined up' later for performance gains, but how can it getit so wrong (timewise) ?If this is the case, will I see a significant difference changing ourserver to use a single processor, which seems completely the wrongapproach (or should I do this on each query in each app - eek) ?Do we have a problem that we don't know about that causes it to takethis long ?What can we do ? Ideally, using both processors would seem to bepreferrable.
View 2 Replies View RelatedHi,
I would just like to confirm something with you guys...
Am I correct in saying that you dont need multiple connections to the same DB in a SSIS package in order to achieve parallel processing across multiple SQL tasks. In other words, I have 2 SQL tasks executing different stored procedures on the same DB that I want to run in parallel. They should be able to share one connection and still process in parallel, correct?
With that in mind, would the processing be faster if they each had their own connection?
Thanks in advance.
after running query at first time working all processes
but later 2-3 sec. working only one
SQL 2005
Hewlett Packard DL580 (16 processes)
What is ideas?
I have SQL Server Version:-
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
This is just an UAT server which has OS and hardware detail below:-
OS :- Windows Server 2008 R2 Standard
SP:- SP1
Processor :- Intel(R) Xeon(R) CPU X5650 @2.67GHz 2.66 GHz
RAM : - 4 GB
Bit - 64 bit
I want to set the value to max degree of parallelism, what value should i configure for the same?
Below is the snap property of SQL instance >> Processor
We're experiencing a large number of deadlocks since we began runningSQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyperthreading intel processors. We don't have the same problem on Dell6650's w/o the hyper threading. If I turn off the parallel queryprocessing option the deadlocks stop. I've tried all of the suggestionsfrom the Microsoft Knowledge Base under the following link -http://support.microsoft.com/?kbid=837983The only suggestion that actually yielded results was turning offparallel query processing but I don't want to give up what should be aperformance advantage if it wasn't for the deadlocks. Query tuning andindex tuning hasn't helped. Any suggestions? I haven't applied SP4yet. I'm wondering if anyone has seen the same problem resolved withSP4.*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies View RelatedHi,I have a sql 2000 server with 8 processors, server settings are asdefault. I read on Technet that it is good practise to remove thehighest no. processors from being used for parallelism, correspondingto the no. of NICs in the server. One of our 3rd party developers hasrecommended only allowing one processor to be used as there is aperformance hit by the server working out which processor to use. Doesanyone have a definitive answer to this? I suspect he's wrong but I'dlike some hard evidence if possible, thanks.Kev.
View 3 Replies View RelatedHi,
Is it possible to achieve partition parallelism in SSIS? What I am asking is, In DataStage, if I load some data like 'data reader -> trans1 -> trans2 -> destination' (and assume that I have 4 nodes configured), the tool divides the data into 4 different datasets and executes the package as 4 instances. This way the data load is very fast. Is it possible in SSIS?
Of course we can divide the dataset and load them thru multiple instances? But then dividing the dataset will differ for every load and so we need to modify the package all the time. Even if we divide the dataset, I am not sure 4 instances will run in 4 different nodes or in a same node? So anybody has any idea about it?
Thanks.
Hi,
In my package I have a source, a script component to make some changes to that and a destination. To speed up the process, within a data flow, I have created 6 copies of the above components and running them in parallel. Each source takes different set of data. I have divided the data using the record no such that, each set will read 1million records.
Now, my question is, though each pipleline is supposed to process exactly 1million records, they are not running at the same speed. For example, 1 pipeline completes processing all 1million records whereas another pipeline processed only 250000 records in that time. I don't see any reason for why one should run slow while another is running fast considering that both are doing the same thing?
Do you have any idea about this?
Thanks.
I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.
1 Has the current user been granted login access to the trusted SQL Server?
2 Has the current user been granted access to a specific database?
3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?
Thanks,
Sean
Hi,
I need some urgent help on a conceptual question.
Requirement:
I have a simple package with one dataflow task. In that I need to read from a sql table and for every row in that table loop through n times and generate new output rows based on certain conditions (which are best evaluated in custom script as they are rather complex). Hence, if I have 100 rows in the table as my input, I may end up with 100*n rows as output.
My Design:
To implement this I have used an OLE DB Source which outputs to a Script Transform (ST). In the ST I intend to loop through in custom code and generate new rows using the .AddRow feature when I need new rows. This ST then feeds into another OLE DB Destination which writes the data to the table. Simple!
I am using the default buffer settings. All I have tweaked is the Synchronous... property on the script transform (otherwise I do not get to the Output0Buffer within the script!).
Problem:
I wish to do as much as possible in parallel. So I would expect the OLE DB Source to provide more than one row at a time to the script transform and that should process more than one input row simultaneously. It seems the script componenet is serializing input, so it seems to take one row at a time from the OLE DB source, loop through and process in the script transform).
AM I RIGHT IN THINKING THAT THE SCRIPT TRANSFORM IS EXECUTING THE INPUT IN A SEQUENTIAL MANNER?
CAN I PARALLELISE THIS?
If so, how?
Thanks,
Tirthankar
I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?