Monday, October 30, 2006
Linked Servers for data from another database
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
EXEC sp_addlinkedserver
@srvproduct= 'SQL Server'
,@server= 'srvname'
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'srvname'
, @useself = 'FALSE'
, @rmtuser = 'username'
, @rmtpassword = 'password'
GO
Monday, September 25, 2006
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!
Thursday, August 24, 2006
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).
Friday, August 18, 2006
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
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
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.
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.
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.
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.
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.
Thursday, August 17, 2006
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',
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',
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?
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?
Wednesday, August 16, 2006
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
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
Wednesday, August 09, 2006
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.
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.
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.
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.
