21 Apr 09 MSSQL: Joining another server in a select statment

I’ve often wondered if I could have a select statement that retrieved data from two databases at a time. I just got a SQL Server tip in the mail this morning that walks you through the steps. It’s awesome! Does everything and more. The keyword is OPENROWSET.

Here’s the link:
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1353663_mem1,00.html

Example from above article:
SELECT e1.EmployeeID, e2.FirstName, e2.LastName, e1.JobTitle
FROM OPENROWSET(
'SQLNCLI',
'Server=SqlSrv1;Trusted_Connection=yes;',
'SELECT EmployeeID, FirstName, LastName, JobTitle
FROM AdventureWorks.HumanResources.vEmployee'
) AS e1
INNER JOIN OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'C:\Data\Employees.mdb'; 'admin';' ',
'SELECT EmployeeID, FirstName, LastName, JobTitle
FROM Employees'
) AS e2
ON e1.EmployeeID = e2.EmployeeID
ORDER BY e2.LastName, e2.FirstName

Here’s the MSDN link on OPENROWSET.
http://msdn.microsoft.com/en-us/library/ms190312.aspx

Tags: sql

02 Jan 09 MSSQL Large Sub Query Impossibility

I recently had to query to find out how many records from one table were not in another really large table. When you do a select statement like this in MSSQL it overflows and gives you a response of 0. Not very helpful:

select count(*) from Table1 where Field Not In (Select Field from Table2)

All other ideas failing, I finally resorted to writing a query using a temp table variable:
set nocount on;
declare @Tables table (
PK int IDENTITY(1,1),
Field1 varchar(100), Field2 varchar(100)
);
insert into @Tables(Field1 , Field2 )

select t1.Field, t2.Field from Table1 t1
left outer join Table2 t2 on
t1.Field = t2.Field;

set nocount off;
select count(*) as Count, Field1 from @Tables
where Field2 is Null
Group by Field1
Having Count(*) < 2;

Talk about complicated! If you have any other better ways of doing this – please let me know!

30 Apr 08 SQL String Functions

Simple-Talk.com came out with a great article titled SQL String User Function Workbench

It has some great sql functions that I’ve spent hours looking for in the past (or had to write myself):

• Split function
• SplitLines
• Within (not from python)
• EndsWith
• StartsWith
• Contains (not from python)
• Join
• Parts (not from python)
• Partition
• RPartition

Anyway, I bookmarked it to go back to in the future.

12 Jun 07 MSSQL Table Size for all tables

As far as I could tell there was no easy way to query MSSQL to get a list of all of its tables and their respective sizes. I wrote a sql script that does this for you.

It’s below:

declare @RowCount int, @tablename varchar(100)
declare @Tables table (
PK int IDENTITY(1,1),
tablename varchar(100),
processed bit
)
INSERT into @Tables (tablename)
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'dt%' order by TABLE_NAME asc

declare @Space table (
name varchar(100), rows nvarchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100)
)
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = 1
WHILE (@RowCount <> 0)
BEGIN
insert into @Space exec sp_spaceused @tablename
update @Tables set processed = 1 where tablename = @tablename
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = @@RowCount
END

update @Space set data = replace(data, ' KB', '')
update @Space set data = convert(int, data)/1000
update @Space set data = data + ' MB'
update @Space set reserved = replace(reserved, ' KB', '')
update @Space set reserved = convert(int, reserved)/1000
update @Space set reserved = reserved + ' MB'

select * from @Space order by convert(int, replace(data, ' MB', '')) desc

06 Oct 06 @@Identity Best Practices (MS SQL)

I just ran across this post that was referenced from an article written on MSDN.

For the past 2-3 years I’ve been working under the idea that @@Identity would return the identity of the most recently added row in the table you were just working with. Not so – it will give you the identity of the most recently added row in the entire database! I believe, however, that @@identity is limited to working within the connection to the database. For instance, if you have 2 scripts and they both add a row to the database, they would be using separate connections to the database and thus wouldn’t be confused. If your database uses triggers and your trigger adds a row in another table, @@identity will return the identity of that row, not the one you want.

The alternative offered in the article is to use scope_identity(). It acts the same way, only without this little idiosyncrasy.

08 Sep 06 Choosing MSSQL Data Types

This may not interest many of you, but I found a great article on designing database tables in Microsoft SQL Server 2000. Before reading, I had a general understanding of all the types, but I didn’t have any base for choosing which type to use where.

You may want to check it out:

http://www.mssqlcity.com/Articles/General/choose_data_type.htm

20 Jun 06 Intellisense for SQL – Free

SQL Prompt is a great program put out by Red Gate.

They just released a deal where they are giving it away for free until September 1st 2006 (That’s when they will release a new version of the product.)

I downloaded it and tried it out, and it works great – very handy to have when you are dealing with tables with lots of fields, etc. It works with Microsoft Query Analyzer, SQL Server 2005 Management Studio, Visual Studio 2005, Visual Studio .NET 2003, SQL Server 2000 Enterprise Manager, UltraEdit32.

I personally tried it out with Microsoft Query Analyzer.

Here’s the link to the download page: SQL Prompt

18 Apr 06 MSSQL to MDB

It’s fairly easy to take an MDB database and convert it to SQL Server, but how about taking an SQL Server database and downloading it into an MDB file (Microsoft Access)?

This article has some code to do just that: http://www.codeproject.com/useritems/SQL_to_Access_Data_Export.asp

03 Apr 06 Update using an inner join (MSSQL)

Just searched the net trying to figure out how to do an update using an inner join. I figured it was possible, and I was right. Here’s how to do it:

UPDATE Table1
SET Table1.Title = Table2.Title
FROM Table1, Table2
where Table1.ID = Table2.ID

If you are using aliases – for instance if you are updating a table you are joining to itself, use this code:

UPDATE Table1Alias
SET Table1Alias.Title = Table2Alias.Title
FROM Table1 as Table1Alias, Table2 as Table2Alias
where Table1Alias.ID = Table2Alias.ID

Another scenario you may encounter is where you want to update a table, but use an inner join to further filter the rows you want to update.

Here’s an example of how to do this:

UPDATE Table1 Set Field1 = Value
FROM Table1 as t1
INNER JOIN Table2 as t2 on t1.Field2 = t2.Field2
WHERE Field3 = Value

That seems too simple, but it eluded me for a long time.

03 Jan 06 Executing Stored Procedures within Stored Procedures (sprocs)

Is it possible to execute a stored procedured within another stored procedure? According to this thread, it appears so. I’ll have to try it out.

Within a stored procedure, you can execute a stored procedure via EXEC OwnerName.StoredProcedureName. Check SQL Server Books Online for details on executing stored procedures. If the stored procedure returns a result set, then you can:

CREATE TABLE #Temp…
INSERT INTO #Temp…
EXEC dbo.SP1 @parm1 = 10
…
DROP TABLE #Temp

Tara Kizer
aka tduggan