Category Archives: Sql

Here's a list of some books I am recommending.

http://www.simple-talk.com/prettifier/

Why are these so hard to find on the Microsoft site? Too many products?!

SQL Server 2005 SP2 download.

-- Open a text file using OPENROWSET doesn't allow No header

-- Open a text file using OPENDATASOURCE does. See schema.ini

SELECT     * 
FROM   OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=E:\SQL\;Extended Properties="Text;HDR=No;FMT=Delimited"')...[My Listing 1#txt]

bcp "SELECT [OrderID] AS [OrderID], char(34) + [CustomerID]+ char(34) AS [CustomerID], [EmployeeID] AS [EmployeeID], [OrderDate] AS [OrderDate], [RequiredDate] AS [RequiredDate], [ShippedDate] AS [ShippedDate], [ShipVia] AS [ShipVia], [Freight] AS [Freight], char(34) + [ShipName]+ char(34) AS [ShipName], char(34) + [ShipAddress]+ char(34) AS [ShipAddress], char(34) + [ShipCity]+ char(34) AS [ShipCity], char(34) + [ShipRegion]+ char(34) AS [ShipRegion], char(34) + [ShipPostalCode]+ char(34) AS [ShipPostalCode], char(34) + [ShipCountry]+ char(34) AS [ShipCountry] FROM Northwind.dbo.Orders"queryout "E:\MSSQL\REPORTS\Orders20081106.txt"

–SSQLDEV –T –t "," –c 
— 
— Check that the results has the Comma delimited Quoted text that you want.
— 
— Now query this in Query Analyzer to aid in importing it into SQL.

SELECT     * 
FROM   OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=E:\MSSQL\REPORTS\;Extended Properties="Text;HDR=No;FMT=Delimited"')…[Orders20081106#txt]

NOTE: REPLACE the .txt WITH #txt AS per OPENDATASOURCE usage.

create table #myTable( bar bit)
go
insert into #mytable values (1)
insert into #mytable values (1)
insert into #mytable values (NULL)
insert into #mytable values (NULL)
go
select distinct bar from #mytable
go
select  bar, count(*) from #mytable group by bar
go 

RESULTS: 
bar  
---- 
NULL
   1 


bar              
---- ----------- 
NULL           2 
   1           2 

Looks like both count/report NULLs correctly.

A few interesting things to note about this graphic.

1. The query is an INSERT into myVIEW
2. It is using tempdb to process the INSERT. I think it is because I had to add a SELECT * FROM inserted trigger to even get INSERTs to work. In addition I had to add a WHERE clause matching the partioned tables CHECK constraint and explicitly insert to the appropriate table.
3. I thought tempdb needed to have the data files all the same size to be utilized. So here we can see the files which are the same size are used, the bigger one is not.

ps. This is a test. The files will be the same size after the server is restarted.

Example:

EXEC [Northwind]..sp_columns_rowset N'Products', N'dbo', NULL 

Try this procedure as well.

CREATE  PROCEDURE dbo.rp_ScriptTableColumnsList (
 @vcDatabaseName varchar(128),
 @vcTable varchar(128),
 @bFormatted bit,
 @bQuotedNames bit,
 @bColumnListOnly bit,
 @vcOUTSQL varchar(8000) OUTPUT
)
AS
/*
** Procedure
 dbo.rp_ScriptTableColumnsList  


** Description
 Useful utility to script out columns in varying forms for use in calling procedures.
 Can run for any table in any database by creating a dynamic script, and existing script
  in database parameter in use. 

** Parameters 

** Requirements 

** Usage 

DECLARE @RC int
DECLARE @vcDatabaseName varchar(128)
DECLARE @vcTable varchar(128)
DECLARE @bFormatted bit
DECLARE @bQuotedNames bit
DECLARE @bColumnListOnly bit
DECLARE @vcOUTSQL varchar(8000)
SELECT 
 @vcDatabaseName = 'pubs', 
 @vcTable = 'authors', 
 @bFormatted = 1, 
 @bQuotedNames = 1, 
 @bColumnListOnly = 1
EXEC @RC = [master_admin].[dbo].[rp_ScriptTableColumnsList] 
 @vcDatabaseName, @vcTable, @bFormatted, @bQuotedNames, @bColumnListOnly, @vcOUTSQL OUTPUT 
PRINT @vcOUTSQL 

** Changes
  2008-04-21 John Perry Created 

*/
set nocount on 

DECLARE  
 @vcSQL varchar(8000),
 @vcSQLSource varchar(8000),
 @vcFormatting varchar(2),
 @vcSELECT varchar(10) 

IF @bFormatted = 1
 SET @vcFormatting = char(10)+ char(9)
ELSE
 SET @vcFormatting = ''
 
IF @bColumnListOnly = 1
 SET @vcSELECT = CASE
  WHEN @bFormatted = 0 THEN '' ELSE CHAR(9) END
ELSE
 SET @vcSELECT = 'SELECT ' + @vcFormatting 

CREATE TABLE #tmpColumns (
 [name] [sysname] NOT NULL ,
 [id] [int] NOT NULL ,
 [length] [smallint] NOT NULL ,
 [colid] [smallint] NOT NULL ,
 [colorder] [smallint] NOT NULL 
)  

SET @vcSQLSource = 'USE ' + @vcDatabaseName  + '
 INSERT INTO #tmpColumns
 SELECT c.name, c.id, c.length, c.colid, c.colorder
 FROM  dbo.syscolumns c
 JOIN dbo.sysobjects o
 ON o.id = c.id
  WHERE o.name = ''' + @vcTable + '''
 ORDER BY
  colorder' 

-- Add the columns to the local temp table for the database in USE (within query).
EXEC (@vcSQLSource) 

--
-- Create initial list of columns and query from results in temp table.
--
SELECT @vcSQL =
 COALESCE(@vcSQL + ', ' + @vcFormatting , @vcSELECT ) + 
 CASE 
 WHEN @bQuotedNames = 1 THEN
  QUOTENAME(name)
 ELSE
  name
 END
FROM #tmpColumns 

-- Clean up.
DROP TABLE #tmpColumns 

--
-- Complete and format the SQL statement with the TABLE FROM statement, as needed.
--
IF @bColumnListOnly = 0
 SET @vcSQL = @vcSQL + 
  CASE
  WHEN @bFormatted = 0 THEN SPACE(1) ELSE CHAR(10) END
  + 'FROM ' + @vcDatabaseName + '.dbo.' + @vcTable 

IF @bFormatted = 0
 SET @vcSQL = REPLACE(@vcSQL, @vcFormatting, '') 

--
-- Final return of requested SQL string.
--
SET @vcOUTSQL = LTRIM(@vcSQL) 
GO