| Edwin Daniel's profileMicrosoft technologies a...BlogLists |  Tools  Help |
|
August 26 Are you using the correct instance? - when developers and administrators don't talk the same languageIt took a couple of days to find out why an application was not running fine. The vendors suggested to perform some procedures to restore the database. After the restoration, the web application connecting to the database no longer works. The application throws a "database does not exist" error. The vendor's support engineer guided me through the necessary tasks to determine whether the database exists or not. I checked the DNS name, instance name and database name. Everything looked fine to me. They stored the connection string property in the registry so the application retrieves this value during run time. What I noticed was, the DNS name and the instance name are totally different...which is causing the error. What I did was, modified the connection string property stored in the registry to connect to the correct instance. When I did that, the application worked just fine.
One thing to note. The database infrastructure is meant to be transparent to the application for security reasons. So it's the role of both the DBA and the developers to make sure that they are referring to the same server/instance to minimize confusion. August 24 List all SQL Server databases (not using SMO or DMO) using WMII've been doing development for almost 7 years now and I get myself up-to-date with the latest technology. I saw a post on my friend's blog which uses C# and SQL-SMO and a few lines of code. Beats me, in my current work, there's no way for me to run .NET apps (whether it's v1.1 or 2.0) on our environment as I don't do development work. So what do I do? I turn to what is always available in any Windows platform - VBScript. Although it's a bit odd for me to go back to VBScript after doing C# (and a few .NET 3.0 apps), I had to make use of what's readily available. Here's a sample script on how to retrieve all the databases running in SQL Server 2000 using VBScript
Lazy Administrator - NET START optionI am tasked to do patching on one server this week. My process is to always look at what services are running on the server before applying the patch so that when there is a need to reboot the server, I can double-check whether the services are running after the reboot. I didn't like the idea of manually looking at the Services applet and checking each service. Good thing there is the NET START command. This lists the Windows services that are currently started. So I ran this command before applying the patches, copied the result to a text file for reference, applied the patch, rebooted the server and checked whether the services running after the reboot match those in the list I created earlier August 21 Microsoft Robotics StudioThis is something that caught my attention. I am a mechanical engineering graduate and specialized in robotics and mechatronics. The reason I left that particular industry is because of the fact that while it is a very highly-technical field integrating different areas (electrical, mechanical, electrinics and programming), businesses still look at how to increase revenue and cut down cost. That's why a lot of multinational manufacturing companies set up plants in third-world countries because they not only help the country's economy by providing employment but labor is a lot cheaper in the long run compared to automation. Plus, the technology needed to really work on robotics and mechatronics is only available to those who can afford it (I can't but I was sponsored by a German firm to study).
I was surprised to see Microsoft going into this field of automation. Back then, we programmed PLCs and robotic hardware in STL, ladder diagrams and other proprietary language which was written in C or C++ to communicate with various hardware. And the proprietary software available back then doesn't even run on the latest version of Microsoft Windows. We were already using Windows 2000 but the software can only run on DOS or Windows 95/98. Now, Microsoft has ventured into this field to make robotics available to just about anybody with a PC. The only thing that a lot of people fascinated about robotics is that, it takes more than simulation to make things work in a 3-dimensional workspace. That's where my specialization comes in - mechanical engineering.
I just downloaded and installed Microsoft Robotics Studio from the Microsoft Download Center and started playing around with it. I can't wait to play with hardware-and-software-integration concepts again. Just like the good old days. August 20 On a Saturday late night...Windows Update not workingWaiting on weekends is one thing that my work requires. We need to make sure that there are no or at least a few users accessing the servers before we even do maintenance. One of the task that I need to do this particular weekend is do patching for servers. My team lead sent me an email that a particular server does not proceed to Windows Update. So I checked the server and logged on to http://windowsupdate.microsoft.com and to my surprise, there is no progress. I checked the Automatic Updates and BITS services and they both are running. I also checked the WindowsUpdate.log in the %systemroot% folder to find out more information. I found out that Windows Update has been retried for this server a couple of times within the day and with no luck. Thanks to an Australian MVP, I made it work. Outlined below is what I did based on his recommendation.
After that, Automatic Updates went on smoothly and I was able to download all the critical patches from the Microsoft Windows Update site. August 15 ROBOCOPY for file replicationI was tasked to do file replication between two servers located across countries. The source folders have to be the same as the destination in any way - file attributes, NTFS permissions, shared folder permissions, etc. I was asked to check out ROBOCOY a robust Windows file copy utility. This is a part of the Windows Resource Kit Tools which can be downloaded from the Microsoft Download Center. The utility comes with a documentation that explains how you can use it for copying files. Plus, it works even by simply copying the EXE file and executing a script that calls this utility. One thing I like about this is that it copies NTFS permissions as well. To understand this a lot better, let's take a look at the syntax.
Running this command with the /? switch gives you all the possible switches and functions to use with ROBOCOPY. As an example, this is what I used in my options: ROBOCOPY D:\ \\server\profiles\<user>\backup /COPY:DATSO /MIR The /COPY option with the corresponding flags copies the files together with the file information as specified by the following switches: D – file Data. But I still have a problem - it doesn't copy share attributes and shared folder permissions. So even if the files are being replicated, I still have to create shares and assign shared folder permissions. I am currently working on an addition to to my file replication command using ROBOCOPY by creating a script to check whether the folders I have copied are already shared and if not, will assign shares and permissions on them. CSVDE...when you're too lazy to go through each AD accountA friend of mine asked me if there is an easy way to extract user accounts in Active Directory without going through Active Directory Users and Computers MMC. He is doing some auditing as part of their security requirements. Well, being the lazy guy that I am, I always look for ways to automate repetitive tasks. So I searched for the tool that can do this. CSVDE is a command line utility that can be used to import and export data from Active Directory using files that store data in the CSV (comma-separated value) format. Since this comes with Windows Server 2003, I tried playing around with this utility, extracting only user information from Active Directory. After being able to extract the records, I simply imported the output file in Excel for ease of data manipulation like filtering and sorting.
For more details on this utility, check out
August 11 Embedding single quotes in T-SQL statementsIf you're doing string manipulation in T-SQL, you'll frequently stumble upon tasks that require you to embed single quotes in your queries. The problem is, the single quote is what is used to enclose strings in T-SQL. In order to do this, you use a two-single quote approach. An example of this is printing a sentence with an apostrophe (the same as a single-quote character) . The syntax below displays the sentence
PRINT 'This is the man''' + 's choice'
Notice the use of a concatenation operator to separate the first part of the sentence and the one that comes after the single-quote and the use of a two-single quote approach. This simpy means that every time you have a single-quote character included in your string, you have to do string concatenation to accomodate the insertion of a single-qoute in your string.
PRINT 'COMMENT: I guess programming wasn''' + 't really meant to be as easy as writing in English'
 Transfering logins and passwords between SQL Server 2000 instancesI am working on a log shipping plan for 2 clustered SQL Server 2000 instances located in different countries. I have already finished creating the database maintenance plans when I found out that the logins between the 2 servers are not synchronized. This will be a big problem should there be a disaster and we need to setup the standby server to be the primary server. The first thing I did was to create a script that will create a script (that's how lazy I am) to assign the databases to their corresponding users based on the settings made on the primary server. I want the 2 servers to be exactly the same. To generate this script, this is the script I used
USE master
SET NOCOUNT OFF DECLARE @strSQL nvarchar(50) --variable for dynamic SQL statement DECLARE @strLogin nvarchar(50) --variable for login name to assign as the database owner (Windows or SQL account) DECLARE @databaseName nvarchar(255) --variable for database name - we will iterate thru all databases and change the owner to this specified account DECLARE MyCursor CURSOR FOR --used for cursor allocation SELECT name, suser_sname(sid) as databaseOwner FROM master.dbo.sysdatabases WHERE (dbid>6) OPEN MyCursor Fetch Next From MyCursor Into @databaseName, @strLogin While @@Fetch_Status = 0 BEGIN SET @strSQL ='USE ' + @databaseName + ' EXEC sp_changedbowner ''' + @strLogin + '''' PRINT @strSQL Fetch Next From MyCursor Into @databaseName, @strLogin END Close MyCursor Deallocate MyCursor This script (which I will be executing on the primary server) will generate a script (which I will execute on the standby server) to assisgn the databases to their corresponding owners. But before I execute this script, I need to do a logins transfer. In order to do this, I need to follow the procedures outlined in this Microsoft knowledgebase. Hopefully, everything works. I need to create a test environment to implement these. August 10 Why my database maintenance plans are failingI had a database maintenance plan that performs a backup of the transaction log for all of my databases. Funny, though, that I keep getting a job failure for this specific maintenance plan. And so I investigated. I checked the maintenance plan to see what databasea are included in the backup plan, the reports that were generated by the backup job. The report displays everything as fine...except for 2 databases which are not being included in the backup plan. The report simply states that the backup job for these 2 databases were not done. Upon careful inspection, I found out that the trunc. log on chkpt. option for these 2 databases were set to TRUE. This means that every time a checkpoint runs, the transaction log is truncated, which causes the transaction log backup job to fail. I don't know who set this option to these 2 databases, but I'm practically sure this is the one causing the backup job failure since the backup job for these 2 databases form a part of a bigger backup job. So even if all, except these 2 databases, were successfully backed up, it is still considered a failure. What I did was to set this option to FALSE using the sp_dboption stored procedure. After waiting for an hour (since the transaction log backups were scheduled on an hourly basis), the backup job failure disappeared. Log Parser 2.2 for WindowsI was trying to find a way to extract event logs from servers and store them as records in a database for archiving and analysis. I was able to create a very simple script which can do the trick. The thing is, I need to format the output file for proper importing to the database. And this is where I spent a lot of time. Fortunately, Microsoft has provided a tool called Log Parser for Windows. You can download this tool from the Microsoft download center but it is not supported by Microsoft. So I installed Log Parser and was surprised at what it can do. It can read event logs, SQL Server Logs, Security Logs, IIS logs, etc. You can use SQL-like queries to extract these information and format them in a way you want - like preformatted for saving in a database or presented in a graphical report. For more information on Log Parser, visit www.logparser.com - the so-called unofficial support site for Log Parser Installing SQL Server 2005 Everywhere..a matter of restartingJust to try out my O2 XDA II mini, I decided to create a very simple database driven application using Visual Studio 2005 and SQL Server 2005 Everywhere Edition. Just for the record, SQL Server 2005 Everywhere Edition is just the re-branded SQL Server 2005 Mobile Edition. A lot of people got confused with this (like me for instance) but there is really nothing new to it except some licensing stuff and new capabilities http://www.microsoft.com/sql/CTP_sqlserver2005everywhereedition.mspx
Since I am using the CTP, I know I would be having some difficulties using the product. But nothing I cannot manage as I already got a feel of how it is to use Beta products. I was able to create my database and application but what took quite some time is deployment. I keep getting this Can't find PInvoke sqlceme30.dll error. I followed everything that the SQL Server 2005 Everywhere team recommended in installing SQL on a device but no luck. Finally, I had one thing in mind which I haven't done yet...and that is to do a soft reset. Most of the time, we treat a device differently from a workstation. But this is one thing I just realized - the device is just another Windows machine. After doing a soft reset, my deployment went on smoothly. Now I need to get back to working on my app. |
|
|
