laptopcomputersonsalenewrev-blog

[laptopcomputersonsalenewrev-blog]

MSDE enable TCP/IP or Named Pipes

When you inherit a new server sometimes you find that you can't connect to the server, to fix that you may need to simply enable the protocol via Sql Server Network Utility (svrnetcn) that is listening For MSDE.In Windows, click Start and Run. Enter svrnetcn and click OK. Under the General tab, verify that the correct instance for the server is displayed in the Instance(s) on this server box. Highlight your desired protocol and click Enable (double clicking the name also moves the protocol to the enabled protocols box). Click OK.Restart the Sql Server InstanceIn Windows, click Start and Run.enter services.mscLocate the MSSQLSERVER instance you modified in the Sql Server Network Utility and Restart the service.You may wish to ensure that your users are not logged on or at least notified of this change as it will kick them out of the application

[laptopcomputersonsalenewrev-blog]

SSIS SOS!

If you have been regretting making the move from Sql Server 2000 to Sql Server 2005, you will be in complete shock and awe when you find that you no longer have DTS to move your data around. The good news is that in Sql Server 2005 you get Sql Server Integration Services (SSIS) a full feature ETL product for Sql Server 2005. If you want to move things moving quickly take a look at the Sql Server 2005 Import / Export Wizard short article which outlines just how easy shuffling your data around can really be.

[laptopcomputersonsalenewrev-blog]

Comma delimited Lists

Take the post about finding columns and incorporate it with this post from a college of mine. http://www.artfulsoftware.com/infotree/tip.php?id=753 Now you're off and running turning lists to comma lists, in corporate that with the list table function I posted sometime back, and you can go backwards and forwards!

[laptopcomputersonsalenewrev-blog]

Find me a column...

Forgive the fact that the following SQL is not formatted, but this topic came up today on a list that I frequent, which was how to get all the fieldnames of a view (or table etc). this dandy little select statement will gather just that and all you need to really parse it for is your table name or column name... maybe you want to know how many times you named something "EntryTime" this is an easy way to find all that information at a glance. This is a Sql Server 2005 version (If you need it I can post a Sql Server 2000 version as well.) SELECT t.name AS TableName, sc.name AS SchemaName, c.name AS ColumnName, c.column_id AS ColumnID, c.precision AS , types.name AS TypeName, basetypes.name AS BaseTypeName, st.name AS TypeSchemaName, CASE WHEN c.max_length>=0 AND basetypes.name IN (N'nchar', N'nvarchar') THEN c.max_length/2 ELSE c.max_length END AS Length, c.scale AS Scale, CONVERT(bit, c.is_identity) AS , CONVERT(bit, c.is_computed) AS , CONVERT(bit, ISNULL(ic.is_not_for_replication,0)) AS NotForReplication, CAST(ISNULL(ic.seed_value,0) AS DECIMAL(38)) AS IdentitySeed, CAST(ISNULL(ic.last_value,0) AS DECIMAL(38)) AS IdentityCurrent, ISNULL(ic.increment_value,0) AS IdentityIncrement, CONVERT(bit, c.is_nullable) AS AllowNulls, c.default_object_id AS DefaultTextID, ds.name AS DefaultName, c.default_object_id AS defaultid, dsc.name AS DefaultSchemaName, CONVERT(bit, CASE ds.parent_object_id WHEN 0 THEN 1 ELSE 0 END) AS IsBoundDefault, rs.name AS RuleName, rsc.name AS RuleSchemaName, CONVERT(bit, CASE WHEN fi.language_id IS NULL THEN 0 ELSE 1 END) AS FullTextIndexed, cc.definition AS ComputedText, c.is_rowguidcol AS IsRowGuidCol, c.collation_name AS Collation, fi.language_id AS FullTextLanguage, fisc.name AS FullTextTypeColumn, c.is_xml_document AS XMLDocument, xscs.name AS XMLCollection, xsc.name AS XMLCollectionSchemaName, t.type AS type, CONVERT(bit, ISNULL(cc.is_persisted, 0)) AS FROM sys.all_columns c INNER JOIN sys.all_objects t WITH (NOLOCK) ON c.object_id=t.object_id LEFT JOIN sys.schemas sc WITH (NOLOCK) ON t.schema_id=sc.schema_id LEFT JOIN sys.types basetypes WITH (NOLOCK) ON c.system_type_id=basetypes.system_type_id AND basetypes.system_type_id=basetypes.user_type_id LEFT JOIN sys.types types WITH (NOLOCK) ON c.user_type_id=types.user_type_id LEFT JOIN sys.schemas st WITH (NOLOCK) ON st.schema_id=types.schema_id LEFT JOIN sys.identity_columns ic WITH (NOLOCK) ON ic.object_id=c.object_id AND ic.column_id=c.column_id LEFT JOIN sys.all_objects ds WITH (NOLOCK) ON ds.object_id=c.default_object_id LEFT JOIN sys.all_objects rs WITH (NOLOCK) ON rs.object_id=c.rule_object_id LEFT JOIN sys.fulltext_index_columns fi WITH (NOLOCK) ON fi.object_id=c.object_id AND fi.column_id=c.column_id LEFT JOIN sys.all_columns fisc WITH (NOLOCK) ON fi.type_column_id = fisc.column_id AND fisc.object_id=c.object_id LEFT JOIN sys.computed_columns cc WITH (NOLOCK) ON cc.object_id=c.object_id AND cc.column_id=c.column_id LEFT JOIN sys.schemas dsc WITH (NOLOCK) ON dsc.schema_id=ds.schema_id LEFT JOIN sys.schemas rsc WITH (NOLOCK) ON rsc.schema_id=rs.schema_id LEFT JOIN sys.xml_schema_collections xscs WITH (NOLOCK) ON xscs.xml_collection_id=c.xml_collection_id LEFT JOIN sys.schemas xsc WITH (NOLOCK) ON xsc.schema_id=xscs.schema_id WHERE t.type IN ('U', 'FT', 'TF', 'IF', 'S', 'TT', 'V') ORDER BY c.object_id, c.column_id

[laptopcomputersonsalenewrev-blog]

Turbo Charge Your SSMS

The other day a co-worker mentioned one of his biggest pet peeves about Sql's new IDE. Of course he likes the old IDE, but what he detested the most was the slow bloat time it took to get his SSMS loaded on the screen. When we timed the boot time for his Enterprise Manager to load it took nearly 7 seconds. Not bad, but then proceeded to tell me how much longer SSMS (Sql Server Management Studio) took to load. I guessed that it could possibly be about 10 seconds (only 3 more seconds) but I was shocked to see it load in nearly 25 seconds. it was nearly 3x less productive just loading the thing before you could connect to do anything. I couldn't believe that it was nearly that slow on his computer, I had never timed it on my pc. So when I returned I had to try it. Again the load time was nearly 20 seconds on my machine (about 18 seconds to be exact). I was not entirely pleased with my results but thankfully Google provided the answer, one quick search for "Turbo charge my SSMS" yielded the following result which brings that load time to that of Enterprise Manager about 6-7 when you are prompted with the server you want to connect to. Speed up your Sql Server Management studio The solution is rather simple. First get rid of the splash screen (unless you really like it.) by adding the /nosplash parameter to your shortcut. next the real jewel, Go into Internet Explorer's Tools>Options>Advance Tab and look for the Security section, uncheck the following two settings: o Check for publisher's certificate revocation o Check for server certificate revocation* once you have completed these tasks you will notice what appears to be a TurboCharged SSMS!

[laptopcomputersonsalenewrev-blog]

One sandwich short of a picnic

I had an issue at work the other day, something that seems kind of odd to me. You see in Sql Server 2000 you could setup a view with an order by. Of course this required a TOP 100 PERCENT to be specified at the top of the query. You could then take that view and base new views off of it and all results would be ordered by the original view. In Sql Server 2005 (SP1 or even SP2) everything changed, now you could no longer get the correct order by if you coupled it w/ the TOP 100 PERCENT in your view, but then again, if you do not use a the TOP keyword you can't save your view. The solution to how to get around the issue is in the following blog article. http://executioniseverything.blogspot.com/2007/01/order-by-in-views-sql-server-2005.html A proposed solution has been placed on the MSDN site so you can vote on it. It seems the proposal is to do away with the ability to create ordered by views which technically go against the rules of rational theory.... https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249248

[laptopcomputersonsalenewrev-blog]

Watch those servers

If you're an active DBA, you'll find that your mornings are spent reviewing failed jobs and checking to see what else needs watching. At my company we were fortunate to purchase Idera's Diagnostic Manager. It is a good tool for reviewing your databases at a quick glance and identify any areas that need attention, ie. Space constratins, failed jobs and other critical processes. Not everyone has the ability to go out and spend thousands of dollars on a monitoring solution. That's why I really liked the following article by David Bird over at Sql Server Central (one of my favorite Sql Sites) titled SQL Overview IV - DBA's Morning Review (http://www.sqlservercentral.com/articles/Administration/63042/). In it he describes a set of reports that you can use to review your servers quickly during the morning and spend more time fixing problems than reviewing things. I Plan to implement many of these solutions in my own environment and see how well it stacks up to our paid solution. I will report back and let you all know!

[laptopcomputersonsalenewrev-blog]

Change your Recovery Model

On occasion you may be tasked with switching your recovery model for a database. This task is very trivial and you may already know how to do this via Sql Server Enterprise Manager or even Management Studio. But you can do this via Tsql as well (as with all things sql server related) The syntax is basically ALTER DATABASE Northwinds SET RECOVERY SIMPLE The recovery Models available are, Simple, BulkLogged and Full. The Simple Recovery model is as you guessed the basic recovery model which pretty much allows you to recover your database from a full backup. The simple recovery model is ideal for test and development databases where data loss is not generally a concern. You may wish never to use this model in a production environment unless the business is willing to loose up all data changes since the last Full or Differential backup. In this model you do not have to maintain the Transaction log as nothing is generally written to it. The BulkLogged Recovery model will log every event except those that are Bulk transactons. This has almost the same recoverability as a Full Recovery model. With this recovery model you can do full, differential and log backups, you are also capable of restoring up to the minute so long as your last change was not a bulk logged operation, in which case those changes will remain lost. You will need to schedule a regular backup for your transaction log for those operations that are not Bulk transactions. The Full Recovery model offers the highest amount of protection against data loss. This model allows you to recover any piece of data up to the minute (prividing you have the last backup). You can do full, differential and log backups. You can restore any of the backups for returning to your point in time. Such as a FULL backup followed by a Differential and finally your set of Log backups until the point in time when the failure occurred. In this recovery model you will want to schedule a regular log backup for your transaction, otherwise your database log files will grow and grow.

[laptopcomputersonsalenewrev-blog]

Defrag or don't defrag that is the question!

Back in 2005 I wrote a quick 'n' dirty post (really) about how to maintain indexes. That applied to Sql Server 2000 well enough but did not even touch what was happening with Sql Server 2005. Instead of writing a separate article on 2005, which now come to think about it, I may want to. I instead opted to update my older post, and it now reads more like an article. I've included the link below so that it's quick / easier to find. In this article I also posted up my own defrag monitor script, which should be helpful in locating defraged indexes and that way you can easily defragment only the ones that matter. Good Luck! http://sqlthis.blogspot.com/2005/04/defrag-those-indexes-maintenance.html

[laptopcomputersonsalenewrev-blog]

How many rows are there?

This question comes up quite a bit when DBAs are faced with space issues... the simple solution most people say is to do a quick SELECT COUNT(*) FROM , but that ends up being such a costly solution and heavy I/O cost if you have a lot of tables or a lot of data. Fortunately you can obtain the number of rows without having to run such a heave I/O intensive operation. In Sql Server 2000 the command looks like this: Select OBJECT_NAME(ID), rows From sysindexes Where id = OBJECT_ID(TableName) AND indid < 2 While I found that the number of rows will differ slightly it is close enough to make needed judgment calls based on row count. (I.E. defragging, partitioning, archiving, etc.) In Sql Server 2005 the command looks like this: SELECT OBJECT_NAME(object_id) TableName, rows FROM sys.partitions WHERE OBJECT_NAME(object_id) = TableName note, be sure to replace TableName with your name in order to locate the rowcount for the individual table. You can alternatively modify the Where clause to be by Rows > x so you can find tables over a specified number of rows. Also, if there are more than one index you may end up yielding several responses as the entries in both the partition table and the sysindexes table count the rows in ... you guessed it the index.

[laptopcomputersonsalenewrev-blog]

We don't care about no stinking buffers

While you work on your newest indexes / table design it is often necessary to clean up the cache on Sql Server to ensure that your changes are actually making a difference. Typically to clear the cache you'll want to run, DBCC DROPCLEANBUFFERS. If you are also executing your test via sprocs you'll want to clear that cache as well to do that just run, DBCC FREEPROCCACHE. This ensures that you are always starting from the same point at each run. If you do not clear the cache between tests you can't be sure that your time results are accurate.

[laptopcomputersonsalenewrev-blog]

Managing Table Fragmentation - Sql Server 2000

Performance.... it's an intangible and it seems that no matter how fast and how optimized you set your system up, it seems that there is always new information telling you what you should be doing as well. It seems that there really is no end on to what you can do with software, and when you think you've reached the end of that intangible, then there are all the sorts of things you can do in the hardware world to further push the performance limits. Don't get me wrong, I dig being able to find these tidbits... these jewels of information. It just seems sometimes that you've completed implementing something when now new information, could have helped you out before you started planning. So, What do you do when you continue to manage more and more Sql Servers in your environment? I started out with 8 unhealthy servers. I kept on them and have been able to kick the developers square in the butt!* Part of the butt kicking allowed me to set new rules in place. I've managed to get my developers to work their ideas on their own TEST servers, well server is a very liberal term, we have Sql Server Dev edition running on a separate desktop system under their desk, and does allow them to perform similar performing queries against similar data, if they require more data, I simply restore a copy of the db to their test environment. *disclaimer: no actual butts were kicked in the actual story, the emphasis on kicking is written to enhance the fictional portion of the story. /* Object: Stored Procedure dbo.sp_dba_DBREINDEX version 1 Script Author: Francisco Tapia, Purpose: REINDEX all indexes in a given table or database. Based off the work from Robert Davis http://www.databasejournal.com/img/sp_dba_DefragIndexes.sql */ USE MASTER if exists (select * from sysobjects where id = object_id('dbo.sp_dba_DBREINDEX') and sysstat & 0xf = 4) drop procedure dbo.sp_dba_DBREINDEX GO CREATE PROCEDURE sp_dba_DBREINDEX ( @Table sysname = Null, -- Table in which to defrag the indexes @ShowDetail bit = 0 ) AS If (Not Exists (Select 1 From sysobjects with(nolock) where id = object_id(@Table) And xtype = 'U') And @Table Is Not Null) Or @Table = '?' Begin Print '''' + @Table + ''' is not a valid table object in this database.' + char(10) Print 'Proper syntax:' Print 'Exec dbo.sp_dba_DBREINDEX ' + char(10) + space(5) + '@Table = { | ''?'' }' + char(10) Print 'table_name' Print space(5) + 'Is the table for which to defragment an index. Table names must conform to the rules for identifiers.' Print space(5) + 'Table_name is optional, procedure will run for all tables in database if parameter not specified.' Print '''?''' Print space(5) + 'This help message will be displayed.' End Else Begin If @ShowDetail = 0 BEGIN IF @Table IS NULL BEGIN EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'', '''', 0) WITH NO_INFOMSGS' END ELSE BEGIN DBCC DBREINDEX (@Table, '', 0) WITH NO_INFOMSGS END END Else BEGIN IF @Table IS NULL BEGIN PRINT 'Initiating DBReIndex for All Tables' EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'', '''', 0) ' END ELSE BEGIN PRINT 'Initiating DBReIndex for ' + @Table DBCC DBREINDEX (@Table, '', 0) END END END

[laptopcomputersonsalenewrev-blog]

How should I store an IP address in SQL Server?

I had the mis-fortune of working with a database where the developers decided to store the IP address as an integer. I believe they did this in order to help improve the performance of their database, or storage, or possibly both. The front end dealt with all the conversion of IP to INT and vice versa. When I needed to make a little bit of enhancements because the front end was just literally timing out at the amount of data that it needed. I decided to change how I retrieved some of my reports, and thus run into the following link, in where the author shares two functions to aid in the conversion of IP to INT and INT to IP. The article is quite good and even has a few numbers on what the performance is on each style of how one might choose to store the IP address information. How should I store an IP address in SQL Server? I know my new years resolution was going to be broken the moment I published the first article of the year :D HA!. I do have an article on working with indexes and hopefully it should be quite good. Stay tuned!

[laptopcomputersonsalenewrev-blog]

Happy New Year!

If this is your first time visiting my blog, welcome! For those who are returning, welcome back! SqlThis is aimed at being a quick "Reference" site. In general I do not write a whole lot of fluff about the topics at hand, just the absolute necessary to help understand the script/example and that's it, in some ways SqlThis is a repository of Tsql Links, but often times when I've run into unique solutions or solutions that I want to revisit, I will post them here too, so it's sort of a mesh of links and articles. I also get tips and tricks from fellow DBAs in the community that will provide me w/ Articles or Links. I hope this year to get a new tip/trick at least once a month. I've been posting to this blog for almost 2 years now. Next month will actually be my 2 year anniversary. And while other sites give away Ipods and Laptops (google microsoft laptop giveaway). I will be providing something much more coveted... ... more tsql code :)

[laptopcomputersonsalenewrev-blog]

Limit your responses , please.

When you are faced with request from users who will ask things like... i want to know the top 2 machines of every model type that have active leads, you may find yourself baffled and stunned to find that the Select TOP n does very little to help you out. The following article address the issue completely whether you're a sql developer or NOT. For myself it was a new look at existing solutions that we had employed all which were cumbersome and tedious to maintain, the solutions in the article describe the best approach which is easily extensible and flexible. Limit Groups by Number Using Transact-SQL or MS Access

[laptopcomputersonsalenewrev-blog]

Mail Call!

Email, You use it, your colleges use it, even your systems use it. It's a part of everyday business. If you are a Sql Developer you have probably figured out how to implement email already, Often times I've seen many DBA's and Developers implement it from outside of SQL Server in rather ingenious ways... This article outlines how to setup Sql Server Mail in Sql Server 2000 and 2005 (2008 is the same as Sql Server 2005). By bringing mail inside of your server you can now send reports, alerts and other needed information based on the triggers and alerts that matter to you most. Making execution calls to xp_cmdshell to an opensource program (Blat) and sp_send_dbmail calls for Sql Server 2005 help leverage reporting from Sql Server. Sending E-mail from SQL Server 200X

[laptopcomputersonsalenewrev-blog]

UTO! Unidentified Table Object

It's been a while since I've updated the blog, but I Did want to mention that I am working on a neat article, which focuses on my passion for performance. Stay tuned for the latest details... in the mean time, have you ever been stuck with someone else's database? Or how'bout a vendor database where someone needs you to extend a task. Well finding the stored procedures is relatively simple. Remember just go through profiler, run the process and you can monitor which stored procedures are called sometimes this also provides you some feedback on which tables are being accessed. Other times you may need to report on some of this information, so you may need to search the database on where they decided to store such information. I extended my own version of Narayana's searchalltables procedure, in this new version you'll notice that you get to also search text (and ntext) fields along with only a single while loop. Check out the latest script and article here: Searching Unidentified Tables and Columns for Specific Content