I was recently given a du-duping task which was much more difficult than I anticipated and taxed my SQL brain to its limits. I thought of using a CTE to do this but all of the examples I could find for deleting records with a CTE wouldn’t have worked in my situation. Essentially the table [...]
Just a quick post showing how to add a Postgres database server as a Linked Server in Microsoft SQL Server. Install the psqlODBC driver for Windows. Control Panel > Administrative Tools > Data Sources (ODBC). Click the "System DSN" tab and click the Add button. Choose "PostreSQL ANSI" and click Finish. Configure the data source by [...]
If you ever encounter this SQL Server error when selecting from a view then somebody has probably dropped columns from the base table. Here’s a quick run through of the problem. ?View Code TSQLCREATE TABLE dbo.Contact ( Id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, DOB DATETIME [...]
Today I received the following SQL Server error which I had never encountered before. Msg 4429, Level 16, State 1, Line 1 View or function ‘Table_1′ contains a self-reference. Views or functions cannot reference themselves directly or indirectly. Msg 4413, Level 16, State 1, Line 1 Could not use view or function ‘Test.Table_1′ because of [...]
Chuck Boyce Jr (blog | twitter) recently commented on a limitation of the script from my post Discover SQL Servers with Powershell. The script does require that the SQLBrowser service is running for discovery to occur which may be a major issue for some. Here’s an alternative method that does not have this limitation. All [...]
I’m currently building a lot of SSIS packages that are primarily date driven. Many of these involve periods of 100 days, to several years, so I wanted to automate the testing of these packages. I’d previously automated the testing of stored procedures over date ranges but wanted a solution for testing the system as a [...]
With Powershell and SMO you can easily discover SQL Server instances running on your network in just a few lines of code. ?View Code POWERSHELL[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null; $smoObj = [Microsoft.SqlServer.Management.Smo.SmoApplication]; # This gets the sql servers available $sql = $smoObj::EnumAvailableSqlServers($false) foreach($sqlserver in $sql) { Write-Host -ForegroundColor Green "Discovered sql server: " $sqlserver.Name; } [...]
Previously I wrote an article about fulltext searching with MySQL and thought I’d redo the same article but for SQL Server users. Depending of which side of the database wars you’re on SQL Server has either a more advanced or complicated way of doing things. Here’s a very brief introduction to the fulltext search features [...]
Asking for database documentation in many tech shops will result in blank stares. Other places do see the value of but it forever remains on the to-do list. There are a few commercial products available hoping to help with this; SchemaToDoc – http://www.schematodoc.com/ SqlSpec – http://www.elsasoft.org/ SQL Doc – http://www.red-gate.com/products/SQL_Doc/index.htm I’m not convinced of their [...]
Being able to know the setup and configuration of your SQL Servers is important for many IT Professionals. Powershell, combined with SMO, makes this task easy. SMO exposes a lot of properties allowing you to easily retrieve things like Processor & RAM Information, Service Pack Level, Operating System information, Collation Settings, number of Databases, and [...]
