You can use an XMLA Script like the following (replacing “{YOUR SSAS DB ID HERE}” with the valid Database ID:
<ClearCache xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>{YOUR SSAS DB ID HERE}</DatabaseID>
</Object>
</ClearCache>
The nice thing about this approach is you can test a specific statement’s execution by prefacing it with this command.
There was a bug fix dealing with parallelism errors in fixpack 3a, however that is not the reason to set MAXDOP to 1 in this scenario. When you look at parallel coordination, regardless of whether it’s in a clustered environment, you need to determine whether its appropriate for your workload. Where OLTP workloads are concerned, the negatives tend to outweigh the positives where parallelism is concerned because SQL Server “hogs” processors which can inhibit concurrency. Conversely, OLAP and other reporting workloads can benefit from parallelism because the goal is to process large amounts of data in a reasonable amount of time (so the extra processing power is a good thing and concurrency tends to be less of an issue). What type of workload do you have?
Furthermore, you need supporting information before you start changing the MAXDOP setting. Perform some tests to determine whether you start seeing wait events being generated that point to parallel coordination waits (the CXPACKET event for example). You might even start to see blocking and/or timeouts occur because of CPU bottlenecks. Quest offers both Spotlight on SQL Server and Performance Analysis for SQL Server which can both help you understand the impact of adjusting parallelism for your instance; Spotlight can show you the real-time impact on throughput and Performance Analysis can show you resource consumption graphs, baselines and workload trends for historical analysis.
SQL Server Management Studio will allow you to “Hide system objects in Object Explorer” and also provides the ability to create filters on the objects listed, however the filters cannot be persisted through a restart. Have you considered using Quest’s TOAD for SQL Server? You could create a custom object browser using .NET or any of a number of programming languages, but with the slew of productivity and analysis tools built right into TOAD (you can download a free trial at ToadSoft.com) I think it’s your best bet for the most customizable, most helpful tool.
Itzik Ben Gan does a great job of going through the benefits of and use cases for common table expressions in the SQL Server Manazine InstantDoc #42072. The article’s definitely worth a read as it goes into great detail and even provides a test your skills section at the end.
Q: I need a query that will tell me all objects that a user account has access to in each database on the server. How can I find out what roles a user belongs to and what objects they have permissions to?
Kevin Kline says: You need to get familiar with the free SQL Server Script Library that is available at http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true
However, since you specifically are looking for permission information be sure to check www.sqlsecurity.com for great (and free) scripts related to all things security.
Q: I need to do an INSERT INTO in one table from a SELECT, but one of the columns of the inserted table is a field that needs to be incremented by one - depending of a key of a “father” related file. For example, I have master-detail files, in each master register I need to begin counting from 1 up to the number of records inside of a MASTER file, when the KEY of master file is changed the child file needs to restart counting from 1 again, how I do the SELECT of this column?
Bryan Oliver says:
You should probably put an IDENTITY attribute on the column of the inserted table. Then you can simple ignore that column in your INSERT statement.
If, on the other hand, you are not allowed to do this for some reason, try this:
UPDATE my_table
SET a = my_col1,
SET b = my_col2,
SET c = ((SELECT MAX(my_val) FROM master_table) + 1) ,
SET d = my_col3
FROM master_table
JOIN my_table.my_val = master_table.my_val
If you are inserting data from some other source to a table with an identity column and you need to ensure you retain the indentity values, you can temporarily allow inserts to the indentity column. Without doing so explicitly you will receive an error if you attempt to insert a value into the indentity column. For example, if I have a table named MYTABLE and I want to allow inserts into it’s identity column, I can execute the following:
set identity_insert mytable on
Once you execute the command you will be able to insert values into the table’s identity column. This will stay in effect in until you turn it off by executing the following:
set identity_insert mytable off
Be aware that at any time, only a single table in a session can have the identity_insert set to on. If you attempt to enable this for a table and another table already has this enabled, you will receive an error and will not be able to do so until you first turn this off for the other table. Also, if the value used for the indentity is larger than the current identity value then the new value will be used for the identity seed for the column.
WHERE ….
I hope that helps.
I was able to post this on my blog on SQLBlog.com as well. Rod Colledge, out of Oz (that would be Australia), put together a great aggregator for SQL Server related news, whitepapers, articles, etc. If you want to check it out, go to his site at www.sqlcrunch.com - pretty cool.
You can create your procedure with and IF clause based on the parameter value, and then execute the appropriate statement using sp_executesql, EXEC or one of two distinct procedures based on the value of the parameter. Perhaps…
CREATE PROCEDURE choose_stmt(@stmt_nbr int,@other_info char(10)=NULL)
AS
BEGIN
IF @stmt_nbr != 1
EXEC sp_executesql N'SELECT a...';
ELSE
BEGIN
EXEC sp_executesql N'SELECT b...';
...do something with @other_info
ENDRETURN 0;
END
What you’re looking for is the READPAST table hint. READPAST tells the query to read the rows not blocked by a transaction. Read more on how READPAST works here.
Theoretically you could use SQL Server 2005’s READ COMMITTED SNAPSHOT isolation level, however you’d best be sure your TempDB is configured to handle the extra load…Read more on SNAPSHOT isolation here.
Basically, to use an overly simplified dataset, you have the following condition:
ID Parent
01 02
02 03
03 01
Here, 02 is the parent of 01, 03 is the parent of 02, and 01 is he parent of 03 even though it’s also the child of 02. If you write a recursive join, you’ll get the information you want. Get information on recursive joins here.