When you use variables in update statement and update the columns with the values of variables which keep on changing for each row , you can simulate many things that are done using Loop or a Cursor declare @temp table ( product_name varchar ( 100 ),...( read more)
Two UK SQL Server user group dates to flag up for anyone with an interest in BI:
- Integrating Data Mining into your BI Solution, with Allan Mitchell, on June 4th via Live Meeting
http://www.sqlserverfaq.com/events/177/Inte...Full Article.
If you want to know the last Backup/Restore date of database, use the following --Last Backup date of database --Method 1 In Management Studio,Expand database,Right Click on the database and click properties Refer Last Batabase Backup which is availalbe...( read more)
It may be very useful if we have feature to take backup or restore a table I have posted my suggestion here Table level Backup/Restore What do you think of it?...( read more)
One of the users asked this question in the SQL forum I have data in the text as follows AccountNo: 00234543 AccountName: Kickser City: Chicago Country:USA AccountNo: 00234543 AccountName: Annis City: Seatle Country:USA AccountNo: 12234456 AccountName...( read more)
I do quite a lot of work in Switzerland: I lived in Basel for three years and still have a lot of friends and business contacts there as a result. I'll be there next week, in fact, and while I'm there I'll be speaking at the May meeting of the Swiss PASS chapter in Zurich. I'll be doing the same session I did at PASS Europe last week (so no prep time needed, luckily) on 'Designing Effective Aggregations in SSAS 2008'. All the details are here:
John Paul Cook has posted in his blog to show the list of tables that have no Referential Integrity. What if you want to know list of tables that have no primary and foreign keys? Here is the query select table_name from INFORMATION_SCHEMA.TABLES where...( read more)
Sometimes when you import data from other system , you may want to clean up data by removing unwanted characters You can use Replace function . Suppose you want to remove the character ^ from the string declare @str varchar ( 100 ) set @str = 'test...( read more)
In this post , I showed a way to generate 8 characters random password If you have table with data and add a column for password and want to fill that column with random passwords , you can use this approach. I have seen the usage of a function that generates...(read more)
So, yet another SQLBits finished, and judging by the feedback we got it was the best yet. Certainly we had our highest ever attendance and the sponsors all seemed very happy (which is important because, after all, they're the ones paying for it); we've also had a number of very kind emails thanking us too. Now for a few months of peace and quiet before we start planning for the next one, which I suppose will be sometime in the autumn. Any suggestions on w...Full Article.
Hmm, you wait for years for commercial tools for monitoring Analysis Services (the only one I'd ever seen before was Companion for Analysis Services from SQLMinds) and then two come out at once. One of these tools I'll be blogging about towards the end of this week, hopefully - I've had a sneak preview and it looks very cool - but today I found out the following from SQL Solutions:
- OLAP...Full Article.
In this post splitting-csv-to-columns I showed how to split delimited data into multiple columns
Simon in his post 1 Use IN ...Full Article.
In the post Import/Export to Excel, I showed how to export data to EXCEL
The problem that most users find it is it wont include column names in the file and file should exists already with headings
This procedure would solve that problem
If you want to know the datatype or length of a column , you can query on the system table syscolumns or system view INFORMATION_SCHEMA.COLUMNS But you dont need to query on system objects if you use SQL_VARIANT_PROPERTY function Example SELECT TOP 1...(read more)
read more)
How do I view the script of the DDL triggers? The answer is not sp_helptext 'DDL trigger' Because DDL triggers are defined at database / server level and not at any table level So they are not stored in a system objects like sys.objects and the...(read more)
read more)
read more)
read more)
read more)
This is used to set the number of rows affected for DML Declare @test table ( number int ) set rowcount 1 insert into @test select 11 union all select 10 set rowcount 0 select number from @test The above inserts only one row to the table variable @test...(read more)
read more)
Boyan Penev recently blogged about the virtues of setting default parameters and he's right: it's a good idea to set parameter defaults. However I was with a customer yesterday who were waiting for a really long time to even open the Query Parameters dialog in the SSRS query designer. I was a bit curious as to why this was, so I ran a Profiler trace. What I found was then when I opened t...Full Article.
Source Click Here.
Source Click Here.
Source Click Here.
-
Source Click Here.
Source Click Here.
Source Click Here.
Source Click Here.
Source Click Here.
Source Click Here.
Source Click Here.
-
Source Click Here.
-
Source Click Here.
read more)
BI Survey. It being that time of year, I got the latest edition - the BI Survey 8 - last week along with an email encouraging me to blog about it, and who am I to refuse a request to blog about something as fascinating as this? It's a whopping 489 pages long so I can't even begin to summarise it, but there a...Full Article.
BI Survey. It being that time of year, I got the latest edition - the BI Survey 8 - last week along with an email encouraging me to blog about it, and who am I to refuse a request to blog about something as fascinating as this? It's a whopping 489 pages long so I can't even begin to summarise it, but there a...Full Article.
Here's something that had me stumped for quite a long time earlier this week.
I'm doing some performance tuning work for a customer at the moment, and following my usual approach I took a test query, cleared the cache, ran it and noted how long it ran for and what I saw in Profiler and MDX Studio. However this time I saw some strange things happening: I'd run the same query on a cold cache more than once and the query times would differ by anything up to a minute; even more confusing, the ...Full Article.
Earlier this week Greg Galloway sent me an email about some new code he'd added to the Analysis Services Stored Procedure Project to clear the Windows system file cache:
http://asstoredprocedures.codeplex.com/Wiki/View.aspx?title=FileSystemCacheFull Article.
Microsoft SQL Server Community Projects & Samples
SQL Server 2008 product samples are available for each project below or you can download all the samples together (except AdventureWorks), or select from the menu below for specific projects.Microsoft SQL Server Community Projects & Samples
SQL Server 2008 product samples are available for each project below or you can download all the samples together (except AdventureWorks), or select from the menu below for specific projects. read more)I'm doing some work with Marco Russo and Alberto Ferrari at the moment, and for the benefit of any Italian (or Italian-speaking) readers of this blog I thought I'd mention that they are involved in organising a SQL Server conference near Milan. You can find out more and register here:
http://www.s...Full Article.
On Monday Microsoft announced "SQL Server Fast Track", a set of reference architectures for data warehousing with SQL Server. This has all been blogged very well by others, so if you're interested in finding out more I suggest you read Peter Koller:
htt...Full Article.
How do I use CodePlex to download samples and sample databases?
- To download the entire set of SQL Server product samples, click on the releases tab for this portal project, then click on the appropriate installer for your computer.
- To focus on samples for a particular technology on the appropriate Product or Community link in the sample table on the home portal page. After clicking on that link you can:
- Down...Full Article.
I do not understand where the AdventureWorks2008 database has gone, last time when i installed the codeplex adventureworks database, a database named "AdventureWorks2008" was made. When i reinstalled the sample databases, it is gone! I'm reading a sql tutorial that uses the AdventureWorks2008 database extensively, so please anybody tell me where to get it? Full text search and filestream are enabled....Full Article.
Sometimes when you import data from other system, you may need to clean the data by removing unwanted data. Consider you import data where you want to keep only those which are purely alphanumerals. Here is a way to find out declare @table table ( data...(read more)
an approach to implement drilldown on charts with Reporting Services when Analysis Services is used as the data source, and it got me thinking about ways to implement drilldown in Reporting Services in general. There are two standard methods used to do this that are widely known about:
- The first can be described as "fetc...Full Article.
Registration for SQLBits IV (the UK's - and perhaps the world's - largest free SQL Server tech conference), which will be taking place on March 28th in Manchester is now open:
http://www.sqlbits.com/
We've got four tracks of top-notch presentations including some very strong BI sessions. I'll be speaking, and among other speakers we've got SSIS-superstar Jamie 'twoFull Article.
Last November, at the PASS Summit in Seattle, I presented a session on building a monitoring solution for Analysis Services, Integration Services and Reporting Services which seemed to go down pretty well. I was lucky in that the SQLCat team presented a very similar session, although just covering Analysis Services, the next day - so at least I got to present first! Anyway, I see that they've just got round to publishing their material on this subject here:
http://www.sqlbits.com/information/PublicSessions.aspx
and choose the sessions you'd like to see.
Boyan Penev recently blogged about the virtues of setting default parameters and he's right: it's a good idea to set parameter defaults. However I was with a customer yesterday who were waiting for a really long time to even open the Query Parameters dialog in the SSRS query designer. I was a bit curious as to why this was, so I ran a Profiler trace. What I found was then when I opened t...Full Article.
If you build a dimension directly off a fact table (a fact dimension), when you add it to your cube and set the relationship between that dimension and the fact table you have a choice about the type of relationship you can use: you can use either a Fact relationship type or a Regular relationship type. I've always used Fact relationships but never known whether there was anything special about them - there was in some of the early Yukon betas but the functionality got dropped before RTM.
Microsoft SQL Server Community Projects & Samples
SQL Server 2008 product samples are available for each project below or you can download all the samples together (except AdventureWorks), or select from the menu below for specific projects.Microsoft SQL Server Community Projects & Samples
SQL Server 2008 product samples are available for each project below or you can download all the samples together (except AdventureWorks), or select from the menu below for specific projects.Microsoft SQL Server Community Projects & Samples
SQL Server 2008 product samples are available for each project below or you can download all the samples together (except AdventureWorks), or select from the menu below for specific projects. read more)Source Click Here.
What's new for a DBA in SQL Server 2008; Nuggets and networking Cost Free Register Via UK SQL Server User Group Address Leeds Metropolitan University Directions to Event What's new for a DBA in SQL Server 2008? There are many great new features in SQL Server 2008. In this presentation Martin will demonstrate some of his favourite features that will be of interest to DBA including the new Auditing feature, Transparent Data Encryption, Change Data Capture, Resource Governor, Compression features, Performance...(read more)
Source Click Here.
UK SQL Server User Group (Edinburgh, 29th Jan) - Rob Carrol Microsoft PFE (Upgrading to SQL 2008) and Martin Bell (Resource Governor demo)
Upgrading to SQL Server 2008 - Rob Carrol PFE; Resource Governor demo; SQL Nuggets and networking. Cost Free Register Via UK SQL Server User Group Address Microsoft Office at Waverley Gate, Edinburgh Directions to Event Scottish Area SQL Server User Group Meeting, Edinburgh - Thursday 29th January Upgrading to SQL Server 2008 - Rob Carrol As a Premier Field Support Engineer for Microsoft, Rob is heavily involved with clients who are in the process or looking to upgrade to SQL Server 2008. In this...(read more)
Source Click Here.
NTFS fragmentation degrades SAN performance? Well it does for insane fragmentation....(read more)
Source Click Here.
A lot of developers have SQL Server and its different services running on their PC or notebook to develop or test BI solutions. Unfortunately this slows down your system quite a lot. To speed things up when not using SQL Server, I used to stop and start each service manual quite often.
Recently I found out that it's possible to start and stop all services at once with a simple batch file. It now only takes a couple of seconds instead of a few minutes and some annoying steps.
Copy/paste the following in a .txt file and rename it to .bat to make it a batch file, execute it by double clicking the file.
START SCRIPT:
NET START "SQL Server Agent (MsSqlServer)"
NET START "MsSqlServer"
NET START "MsSqlServerOlapService"
NET START "ReportServer"
NET START "SQL Server Integration Services"
STOP SCRIPT:
NET STOP "SQL Server Agent (MsSqlServer)"
NET STOP "MsSqlServer"
NET STOP "MsSqlServerOlapService"
NET STOP "ReportServer"
NET STOP "SQL Server Integration Services"
Source Click Here.
I'd recently built a two-node failover cluster for a customer using Windows Server 2008 and it's working really well. The customer wanted to extend the cluster by adding a third node and I came across a problem with the Full Text Search service following the SQL Server installation.
The new node was introduced to the failover cluster - no problem. I'd re-run SQL Server setup.exe on the new node for each of the three instances, then run Service Pack 2, plus CU7 to synchronise the SQL binaries on the new node with the existing nodes. I'd adjusted the Possible Owners and Preferred Owners to reflect the intended failover actions. Next I wanted to test failover of each instance to the new node to validate SQL Server started and there were no problems.
Everything worked fine, except FullText Search - which wouldn't start, the following appears in the Application Log:
The SQL Server FullText Search (SQLInst2) service depends the following service: NTLMSSP. This service might not be installed.
There is a mention of this problem in the following KB article, although the resolution is to install SP2 for SQL 2005 - which had already succeeded.
List of known issues when you install SQL Server 2005 on Windows Server 2008
After some further research and testing - I changed the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\msftesql$SQLInst2\DependOnService
The DependOnService showed two values RPCSS, NTMLSSP on the new node, but the same key only showed RPCSS on the existing 2 nodes (where the instances failover without problem). Removing NTLMLSSP from this registry key for all three FullText Search instances on the new node and restarting the server resolved the problem - and the instances now failover.
Regards,
Justin Langford
Source Click Here.
I was searching for a particular document for SQL 2008 and thought I'd list the links to what I found, there's some especially good white papers in Technet, I can recommend the T-SQL enhancements and the indexed view white papers especially: SQL Server 2008 White Papers http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx Technet SQL 2008 White Papers http://technet.microsoft.com/en-us/library/bb418496.aspx A list of blogs and other sites, sadly no Grumpy Old DBA http://msdn.microsoft...(read more)
Source Click Here.
Donald(Farmer) has posted his new years resolutions for his speaking engagements. http://www.beyeblogs.com/donaldfarmer/archive/2009/01/you_say_you_wan.php They're great.
I totally agree with the reduction of slides. My presentations last year had few slides and is something I'm going to commit to more in 2009. I remember seeing Mike Taulty presenting at a usergroup meeting and he had slides that moved rather than being static. I'm going to commit to moving the slides I do use, from text ones, to graphical ones.
Donald says he's going to stop using analogies. I love analogies so not sure I'll do this but ensuring they are relevant is something that one needs to do.
-
Source Click Here.
When users migrate from SQL Server 2000 to SQL Server 2005, not all databases are set to Compatibility Level 90 which is required to make use of the newly available functions There are many methods to know the Compatibility Level of a database using queries...(read more)
Source Click Here.
OK, this is rather short notice I know but the UK SQL Server User Group is hosting a BI evening event next week, on January 15th at Microsoft's HQ in Reading:
http://sqlserverfaq.com/default.aspx?item=event&itemid=139
Since this is the rumoured date for Microsoft laying off 15% of its staff, you might be advised to wear some wellies in case you step in a puddle of blood on the floor. Hopefully all the BI folks will be ok though...
Anyway if you follow the link you'll see that I'm one of the speakers: I'll be repeating the session I did at PASS last year on building a monitoring solution for Integration Services, Analysis Services and Reporting Services. Also speaking will be Allan Mitchell, on his new pet hobby of data mining. Hope to see you there!
Source Click Here.
Now public on Erland Sommarskog's website. Enjoy! Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!...(read more)
Source Click Here.
This is a very exciting/confusing/glooming period for SQLServer Developers/DBAs
On one hand there is the 'cloud' which will take our precious boxes away from us and we won't be needed to manage it anymore.
On the other hand is an avalanche of technologies bundled with every release of SQL Server.
Back in the 2000 days all you really needed to know was
VB/VBA/VBS
DTS
T-SQL
MDX
SSAS
DOS
WSH
Profiler/QA and other tools
I can just see the skills required for a SQL Server DBA these days
VB/VBA/VBS
C#
SSIS
DTS
Powershell
T-SQL
MDX
SSAS
SSRS
SSNS
LINQ
Entity Framework
SSDS
SSMS/ Profiler/QA/BI Studio and other tools
With Kilimanjaro this list will only get longer.
Soon your IT shop will look like a hospital.
You need some SSIS with C#? Talk to DBA A
You need some Powershell automation with SMO? Talk to DBA B since DBA A doesn't know Powershell
You need some DTS? Talk to DBA C because DBA A and DBA B started to use SQL Server with version 2005 (9) and don't know anything about DTS
Is there really one super DBA who knows all these technologies? I don't think so; it is time to start specializing. Besides the stuff you absolutely need to know like T-SQL, the internals and maintenance, you will have to pick your direction.
So I ask you the reader: what brings the future for the SQL Server DBA?
Source Click Here.
If you live in the greater Boston area you might be interested in knowing that the New England SQL Server Users Group and Southern New England SQL Server Users Group have been quietly working for the past several weeks to create a SQL Server oriented Code Camp, using the PASS Community Connection framework. Following is the blurb we came up with to describe the event:
It's all about the data! Today's applications are increasing data-dependent and whether you are a developer, DBA, or BI professional, you need to know how to work a variety of data across a range of products. This free, full-day Code Camp-style event will feature three concurrent tracks throughout the day to satisfy any tech professional who is interested in the latest and greatest tips and techniques for working with SQL Server 2005 and 2008, the Microsoft BI toolset, and all of the new .NET data access technologies, including LINQ and the Entity Framework.
Interested in attending? Click here to join us! Register soon; space is limited and the event is starting to fill up. You can find a current list of the sessions that are planned for the day by clicking here. I'll post again once we have a full time table worked out.
Like all Code Camp style events, this one has an open call for speakers, which we'll leave open until early next week. If you're interested in presenting, please submit an abstract or two! The more speakers the better for this kind of community event. For instructions on how to do that, please see Chris Bowen's blog post announcing the Data Camp.
Please let me know if you have any questions. We're looking forward to a great event, and hope to see you there!
Source Click Here.
When I wrote the 2005 book, I promised that I would take the time to respond to reviews as much as possible. From the good ones if I didn't really believe that the person read the book (there were a few I have seen, but not too many people tend to write good reviews that clearly haven't read the book unless they are paid to.) In fact, I am constantly surprised with good reviews, mostly because it is so difficult to write a book. I just spent an hour yesterday tracking down 20+ typos that the Chinese translator found.
I will also comment on the less good ones that are posted by people who clearly don't like the material. You aren't required to like the book, and when I am writing it I purposefully will ruffle the feathers of certain groups of people (particularly the ones who don't believe normalization matters!) In fact, I think that the 2005 book was significantly better than the first primarily because of a few reviews that smacked me around and made me realize some of the errors in judgment that I made. My favorite review of the 2005 book was a private review that was scathing in a few important points that I applied to the 2008 book.
What burns my feathers is bad reviews that have no information whatsoever. I mean, take this review of the book on Amazon (http://www.amazon.com/review/R29BAGXMZMIC7Y/ref=cm_cr_rdp_perm):
Waste time to reading this book.,
"Since nobody publish a review for this book, I would like to say something. I have to say: Don't read it. It just waste your time."
Now, I won't make any kind of fun of the reviewers grammar, as it is very likely that he is not a native English speaker. But if I were to review a remake of Bio-Dome where Pauly Shore spoke with an English accent in Shakespearean-style rhyme and stopped watching the film 5 minutes inI could say more than this about than "it will waste your time." I will also admit that there are probably a million people that would plunk down a tenner and happily watch this movie (and not just because movie theater popcorn is so much better than you can make at home.) Part of a good book review is to identify the type of reader. If this person is a DBA, this might be that he read the first chapter and said "bleh," then fair enough. If he read the words "database" and thought that there would be dating tips, well, yeah, it would be a waste of time.
I might not be able to speak for every author out there, but I am sure that almost any author loves to get honest feedback on their work, particularly us who write trade literature. I want to target what people want to read, and with new editions likely for new versions of SQL Server, it is important to provide new/fresh/useful upgrades to the material. The best place to determine what to add are reviews: public, private , whatever, as long as they are from people who have read the material and know what they were reading.
As always, if you want to contact me, go to the contact form on my website: http://drsql.org/contactus.aspx (you can contact me anoymously there too) or email me at louis@drsql.org. Just please give honest feedback about what you have seen, not just post a review because no one has published a review yet.
Source Click Here.
It's hard for me to believe it, but I've been blogging about SQL Server for over four years now. For those of you who've been reading my blog all of these years, thank you. And if you've commented or added a few thoughts over the years, I thank you even more! It's hard to get up the courage and carve out the time to post in the first place, so adding a comment is always a nice validation that the blog is both being read and offering something of value.
I also want to say a special word of thanks to my friend Michele Crocket and the team at SQL Server Magazine for giving me the opportunity to blog there starting way back in December of 2004. If you're curious, take a look at my first (rather uneventful) blog post here. Over the years, I've written an average of two blog posts per week. But, for some reason, my favorite blog posts have little to do with a specific SQL Server tip or piece of practical advice. My favorite blog posts usually tend to be off-topic articles like the gathering of the first PASS board of directors, my series of posts about getting into the book writing business, and the shower that tried to kill me.
As we begin the new year, there's a lot to look forward to with many new innovations in the technology, SQL Server, that is the hub of our professional life. Drop me a note if you think of something you'd like to see get special attention as I blog. And, once again, thanks for reading my blog as it now enters its fifth year.
-Kev
Source Click Here.
The DBA cannot be sure that the sensitive data that is flowing to the database has been sufficiently protected. If sensitive data is compromised while it is in storage there is no doubt that the DBA will be the first questioned.
Source Click Here.
Session submission is now open for SQLBits IV. It will be in Manchester on the Saturday 28th March 2009. We are planning on having more sessions than before so if you submit a session you will have more chance of being chosen. To submit a session go to http://www.sqlbits.com/information/SessionSubmission.aspx ( you will need to register on the site to be able to submit a session)
Source Click Here.
Its shocking how many people don't even do the basics of ensuring your data is protected.
I've just read this article http://itknowledgeexchange.techtarget.com/sql-server/mirroring-isnt-a-backup-solution/ about a company that ran JournalSpace.com that relied on RAID to protect itself. NO BACKUPS.
Shocking. I was asking thother day about spares for raid arrays, if you are bothered about spares I would hope you have all your backups sorted as well.
I remember I was told that when the World Trade Centers were bombed in 1993 many companies went out of business because they didn't have backups of their data.
Just imagine if your company didn't have any of its data, it would be crippled, as happened with JournalSpace.com
So remember take backups and put them offsite.
-
Source Click Here.
This is a great list of demo tips from Buck . I would agree with Bill that you need to change the foreground hihghlighting to change
I would also add that I find I often end up in Notepad and the command prompt so you need to set the font size in those as well.
Fortunately for the command prompt you can have a Demo Command prompt that is configured with big text.
Then there is the use of zoomit. I haven't actually used it but I know I should, especially for those areas that you can't increase the font size. Although changing your font resolution to large sccale fonts 120 dpi can help, although I've seen dialogs look odd because of this.
Finally I would add the use of a multi desktop tool that means you only have one app on one desktop. This makes things look a lot smarter and provides an easy way to flip between the different applications.
I'm working on a process for saving settings and being able to easily restore them, because changing the font sizes can be a pain.
-
Source Click Here.
Subscribe to:
Posts (Atom)