SqlInk

Linked Servers for data from another database

2006-10-30T19:15:00.000+05:30

To select data from another server using MS SQL Server, you have to add it as a linked server, using the following stored procedures.

EXEC sp_addlinkedserver
@srvproduct= 'SQL Server'
,@server= 'srvname'
GO

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'srvname'
, @useself = 'FALSE'
, @rmtuser = 'username'
, @rmtpassword = 'password'
GO

2006-09-25T09:05:00.000+05:30

Migrating data from MS Access to SQL Server is quite simple using the Windows Clipboard. Open the MS Access table and select all the rows. In SQL Server, remove any conflicting rows and disable the identity value generation for all columns, if you'd like to maintain the same ID numbers. Then, open the table, click on the left of the new row and paste!

2006-08-24T19:46:00.000+05:30

Installing Visio 2003 after SQL Server 2005 causes problems with Maintenance Plans (when creating manually, without the Maintenance Plan Wizard) due to an older XML DLL file. The solution is to install Service Pack 2 for Visio 2003 (not to be confused with the Office 2003 Service Pack 2).

2006-08-18T15:44:00.000+05:30

I should've posted this link in my last post, but it didn't cross my mind. Check out:
http://www.sqlteam.com/item.asp?ItemID=8003

I also read recently that when using @@IDENTITY to get the value that was inserted in an identity column, you may get an incorrect value if the table has a trigger defined. That's when SCOPE_IDENTITY is a better choice.

IDENT_CURRENT can be used to get the last identity that was inserted into a table, irrespective of the connection. For more, browse over to:
http://www.sqlteam.com/item.asp?ItemID=319

2006-08-18T15:36:00.000+05:30

Today we came up with this really interesting scenario. We've got a table that has just one column, and that column happens to be an identity column. Try whatever you will, a regular INSERT statement will not work.

You cannot do any of the following:
INSERT INTO table_name() VALUES()
INSERT INTO table_name(colA) VALUES(default)
INSERT INTO table_name(colA) VALUES(null)
INSERT INTO table_name(colA) VALUES(1)

...so, any guesses?

To solve this issue, you've got to use INSERT with the DEFAULT VALUES clause as follows:
INSERT INTO table_name DEFAULT VALUES

To enable the insertion of explicit values in an identity column, you can set IDENTITY_INSERT to ON.

2006-08-18T13:35:00.000+05:30

I can't really think of what I should be doing as a DBA, apart from backup, now that all the database tables have been created.

Since I'm in a development environment and have just 6 users, I've been using activity monitor and there isn't really much activity here.

Also, I have no idea when I should be using which, so here's a little poem:

Roses are red,
Violets are blue,
the DBA hasn't a clue,
Whether to use a function or a view.

2006-08-18T10:57:00.000+05:30

Today, I was asked to create a table containing all the U.S. states and state codes. At first, I thought I would have to type it all, but then realised that they installed MS Office for me yesterday. I used MS Word to (automagically) convert the text into a table, saved it in MS Excel. Then, using Microsoft Business Intelligence Development Studio, I imported the data into an MS SQL Server database. I just love SQL Server for that.

2006-08-17T18:32:00.000+05:30

When programmers have coding & naming standards, database administrators should have them too. Well, that's what I'm trying to come up with.

For Naming

Naming items with initial caps and then capitalizing individual words within the name and eliminating spaces is a pretty good way to name database objects. Underscores and hyphens should be avoided because developers would find that harder to type than regular alphabets. With MS SQL Server, this naming convention is pretty good but for Oracle, it would be a bit of a hassle since SQL * Plus isn't case sensitive (anything you type is treated as upper case) unless you use quotes (single for CHAR, VARCHAR, TEXT, ...; double for database object names).

Abbreviations such as ID (for Identity or Identifier) should again follow the rule above - Initial caps followed by lower case, like so: "Id"

Underscores can be used if they really do enhance readability (such as for single-letter abbreviations), but only for non-key columns, since they don't have to be typed by developers very frequently.

For Coding

Just as with developers, commenting and indenting play an important role. You wouldn't want to be the guy reading through hundreds of uncommented and unindented lines, so why should you put anyone else in that position?

Final Thoughts

Standard and Policies are often suggestions (unless they state that they are regulatory) and you should use your discretion or tailor your own set of standards and policies. Either way, they should be consistent with those of the people working with you rather than treating it as a matter of 'style',

2006-08-17T14:26:00.000+05:30

As a DBA for a development environment, particularly one without any feathers to show for experience, you would probably have to deal with several different DBMSes. Although I prefer to work with Oracle, I was assigned to a team that uses Microsoft SQL Server 2005. This version is quite user-friendly compared to the previous versions, as long as you follow the step-by-step guides; if you stray from it, it's likely you'll be wondering why something didn't work as the error messages can be quite cryptic.

One of the things that Oracle can do for you within a development environment is help in finding unused objects. The MONITORING USAGE clause and the V$OBJECT_USAGE view are what you would be using. Once you are done, don't forget to use NOMONITORING USAGE, else performance will take a hit.

To do the same on SQL Server, you would probably have to devise some method of your own. To track write operations, you can use Triggers to update another table. For read operations, if you use Stored Procedures or Functions, you could perhaps modify the code. If that isn't an option, you would probably have to rely on the SQL Server Profiler and 3rd party utilities.

When moving from Oracle to SQL Server, you could probably take some time off and work with T-SQL as it is different from PL/SQL. As long as you remember that this is new ground you're covering, you'll find it fun and easy to write T-SQL.

Note: PostgreSQL is a really good alternative to Oracle. You'll have to perform intensive tests as it isn't suitable for a production environment but PL/pgSQL is so close to PL/SQL that porting code shouldn't be too much of a hassle. But why PostgreSQL when Oracle has announced that Oracle 10g Express Edition will be available for free?

2006-08-16T16:09:00.000+05:30

Today, I had to prepare a document containing all the tables and their column data types. At first, I was going to do it by hand, but then thought I could use the system tables to get it working.

The following is the T-SQL that I wrote to automate the process for me. I just copied the output and pasted it into MS Word.

USE PaHub
GO

DECLARE c1 CURSOR FOR SELECT [name], [object_id] FROM sys.all_objects WHERE type_desc='USER_TABLE'
DECLARE @tabname VARCHAR(200), @tabid VARCHAR(200)
OPEN c1

FETCH NEXT FROM c1 INTO @tabname, @tabid
WHILE (@@FETCH_STATUS=0)
BEGIN
--PRINT @tabname + ' ' + @tabid
PRINT 'Table: ' + @tabname
DECLARE c2 CURSOR FOR
SELECT [name], [system_type_id], CASE [system_type_id]
WHEN 167 THEN '(' + CAST( max_length AS VARCHAR(10)) + ')'
ELSE ''
END
AS tempsizetxt
FROM sys.all_columns WHERE object_id=@tabid
BEGIN
OPEN c2
DECLARE @colname VARCHAR(200), @coltype INT, @typename VARCHAR(200), @typesizetxt VARCHAR(200)
FETCH NEXT FROM c2 INTO @colname, @coltype, @typesizetxt
WHILE (@@FETCH_STATUS=0)
BEGIN
SELECT @typename=UPPER([name]) FROM sys.types WHERE system_type_id=@coltype
PRINT ' ' + @colname + ' ' + @typename + @typesizetxt
FETCH NEXT FROM c2 INTO @colname, @coltype, @typesizetxt
END
CLOSE c2
DEALLOCATE c2
END
PRINT '---'
FETCH NEXT FROM c1 INTO @tabname, @tabid
END

CLOSE c1
DEALLOCATE c1

2006-08-09T17:26:00.000+05:30

After reading about SQL Server 2005's Try-Catch block, I used the syntax:

BEGIN CATCH TRAN_ABORT
{ sql_statement | statement_block }
END CATCH

Apparently, the TRAN_ABORT keyword has been removed... either that or it exists in a different form because it shows up as a syntax error; removing the TRAN_ABORT gives me the desired output.

2006-08-09T12:27:00.000+05:30

Just today, someone from the Network computing team changed the hostname on my SQL Server and I spent a while trying to figure out why the clients couldn't connect. I got a:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)
Error Number: 53

I spent about half an hour, looking up the settings in Configuration Manager and even did a restart (much like we do with Windows) but it still didn't work. I then decided to check the hostname after staring at various elements of the login dialog box when I finally got to the root of the problem.

It's more of a bad practice on part of the network computing guy who came by at night and switched names without documentation or notification of the change.

2006-08-08T17:48:00.001+05:30

I installed SQL Server 2005 Express (the one included with Visual Studio 2005 Professional) and for getting Visual Studio to connect to SQL Server, I've had to manually enter the named pipe (it took me a while to figure that out). Perhaps Microsoft ought to get the two products to communicate via Shared memory or TCP/IP.

Either way, I'm not too good with Names Pipes... never really knew any product that used it till I came across SQL Server so I thought it must've been something that has been put out of service like NetBIOS.

2006-08-08T17:48:00.000+05:30

When I installed Oracle 9i and Forms 6i on my notebook PC, an old Compaq Presario 2132EA that has been retired in favor of the new IBM Thinkpad T60, Forms 6i installed the Oracle 8i client and wouldn't run on Windows XP SP2 - it would crash whenever I tried to connect to the database. Perhaps there's some compatibility issue there.

Has anyone else had similar problems?

2006-08-08T17:34:00.000+05:30

Moving from Oracle 9i to MS SQL Server 2005 was cool, as an administrator, as I didn't have any query syntax to memorize and everything works via a simple GUI. SQL Server Management Studio is like the Oracle Database Configuration Assistant and Oracle Enterprise Manager combined into one. I suppose Oracle has added a whole lot of features into Oracle Enterprise Manager with the release of 10g, so it's unfair to compare Oracle 9i to MS SQL Server 2005, a newer product... comparing Oracle 9i to MS SQL Server 2000 and Oracle 10g to MS SQL Server 2005 is not something I can do since I've never used Oracle 10g and haven't spent much time with SQL Server 2000.

I just performed Transaction Log Shipping, Backup & Recovery using only the GUI!

2006-08-08T17:28:00.000+05:30

Greetings!

SQL Ink is a blog about databases and databases is what I shall blog about... sounds a little silly, but sounds very dramatic when said in a Shakespeare-like tone.

I've had an OCP for Oracle database administration and an OCA for Oracle PL/SQL development but haven't used Oracle ever since I got certified so here I am, rediscovering my certification roots. I'm also venturing into the use of MS SQL Server.

Being a trainee DBA is ultra cool since we get plenty of time to catch up with reading. However, we don't always have access to databases - I have access to MS SQL Server 2005 on my workstation at work, and access to Oracle 9i on my (old) notebook PC at home. Either way, I think I ought to dedicate more time and that's when I thought a blog would help me concentrate on getting things right and as a kind of a database administrator's journal.

That's what is blog is all about; it will probably evolve into something else, but nothing forseeable right now. Until my next blog, I'd like to wish everyone a happy blogging experience and a good bright shiny nice day.