Early today I went across an information about "Denali" being the codename for SQL Server 2011. I first ready it thru Mary-Jo Foley (http://blogs.zdnet.com/microsoft/?p=5288), source of information I've used before to know some news from Microsoft.
I also did some searches to find more about Denali and I found: http://redmondmag.com/articles/2010/02/16/microsoft-to-release-sql-server-sps.aspx and http://news.softpedia.com/news/Introducing-Microsoft-Codename-Denali-the-Great-One-135006.shtml.
The curious thing is that all the original sources pointed by the articles doesn't show the Denali name anymore. A post from Dan Jones (http://blogs.msdn.com/dtjones/default.aspx) published on 02/14/2010 isn't there and the last post we see is from 02/08/2010.
Maybe someone told us the name before the it was meant to? I know that the MVP Summit is being held now at Seattle and it seems to me a good moment to talk about Denali, so probably the MVPs are listening about SQL Server 2011 under NDA… Yes, I'm jealous, I want to know about details and plans!
Well, the doubt about the codename for the next major release of SQL Server says, maybe is Denali, but to make sure I'll be following it very close.
[]s
Luciano Caixeta Moreira - {Luti}
Chief Innovation Officer
Sr. Nimbus Serviços em Tecnologia Ltda
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
Wednesday, February 17, 2010
Thursday, January 21, 2010
A late introduction
Hello everyone.
My name is Luciano Caixeta Moreira and I live in Brasília, Brazil’s capital. This is my blog and I would like to talk a little bit about me before start putting some SQL/.NET stuff.
I have been working with Microsoft technologies since 2000 (wow - ten years, time is running fast), focused on SQL Server and the development platform. My last job was at Microsoft, where I spend 3 years and a half acting as a SQL Server Premier Field Engineer and as a Developer Evangelist.
Late on March/2009 I quitted MS to start my own company with some associates, a company called Sr. Nimbus, focused on specialized consulting/training on the .NET dev platform + SQL Server and, what gave the name for the company, cloud computing.
I’m also a certified professional and you can see my transcript below.
TranscriptID: 700199
Sharing code: MSCertbyLuti
http://www.microsoft.com/learning/mcp/transcripts
In this blog I’ll approach many subjects of my interest and I hope you you’ll enjoy it. In time you will have a chance to know a little bit more about me.
My e-mail is luciano.moreira@srnimbus.com.br
Luciano Caixeta Moreira - {Luti}
Chief Innovation Officer
Sr. Nimbus Serviços em Tecnologia Ltda
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
My name is Luciano Caixeta Moreira and I live in Brasília, Brazil’s capital. This is my blog and I would like to talk a little bit about me before start putting some SQL/.NET stuff.
I have been working with Microsoft technologies since 2000 (wow - ten years, time is running fast), focused on SQL Server and the development platform. My last job was at Microsoft, where I spend 3 years and a half acting as a SQL Server Premier Field Engineer and as a Developer Evangelist.
Late on March/2009 I quitted MS to start my own company with some associates, a company called Sr. Nimbus, focused on specialized consulting/training on the .NET dev platform + SQL Server and, what gave the name for the company, cloud computing.
I’m also a certified professional and you can see my transcript below.
TranscriptID: 700199
Sharing code: MSCertbyLuti
http://www.microsoft.com/learning/mcp/transcripts
In this blog I’ll approach many subjects of my interest and I hope you you’ll enjoy it. In time you will have a chance to know a little bit more about me.
My e-mail is luciano.moreira@srnimbus.com.br
Luciano Caixeta Moreira - {Luti}
Chief Innovation Officer
Sr. Nimbus Serviços em Tecnologia Ltda
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
Tuesday, November 24, 2009
Script to generate views based on your tables
Hello everyone.
Today I were in a customer and had to do a very manual and repetitive task: create many views with different names from the source tables, but with all the fields defined in the table schema.
What is the reason for that? We´re creating a temporary environment where I´m putting a lot of data and we´ll expose an "interface" using views, that the (power) business user will use to create queries and reports. So this abstraction layer, that in the present moment will reflect almost all tables, will be used to avoid some future rework and clashes between both sides (in case the table structure changes), and to make easier to deal with security.
Now that you´re aware of the context let´s see what I had come up with... I could simply write 50 views with all the fields, but it would take a long time, so I created a quick script that will generate the code I need.
To create that I used a temporary table with N records containing schema and existing tables names, besides a column with the name of the view I´ll create. Using this table in a CTE, I leveraged the CROSS APPLY operator to generate a string based on sys.schemas, sys.columns and sys.objects, creating a comma delimited enumeration of columns based on a XML trick.
Here is the T-SQL code using AdventureWorks2008 for you to play, and maybe, it will be useful in the future.
USE AdventureWorks2008
go
WITH TableView AS
(SELECT UsrSchema, UsrTable, UsrView
FROM ( VALUES
('Sales', 'SalesOrderHeader', 'SalesTemp'),
('Sales', 'SalesOrderDetail', 'SalesDetailsTemp'),
('Production', 'Product', 'ProductTemp'))
AS T(UsrSchema, UsrTable, UsrView))
SELECT
ViewsCode.Instruction
FROM TableView
CROSS APPLY
(SELECT
'
IF OBJECT_ID(''vw_'+ TableView.UsrView +''') IS NOT NULL
DROP VIEW dbo.[vw_'+ TableView.UsrView +']
go
CREATE VIEW dbo.vw_' + TableView.UsrView + '
WITH SCHEMABINDING
AS
SELECT ' +
STUFF(
(SELECT N', ' + QUOTENAME(SC.name) AS [text()]
FROM SYS.columns AS SC
INNER JOIN sys.objects AS SO
ON SO.object_id = SC.object_id
INNER JOIN sys.schemas AS SS
ON SO.schema_id = SS.schema_id
WHERE SO.type = 'U'
AND SO.name = TableView.UsrTable
AND SS.name = TableView.UsrSchema
FOR XML PATH('')), 1, 2, N'') + '
FROM ' + TableView.UsrSchema + '.' + TableView.UsrTable + '
go'
AS Instruction) AS ViewsCode
go
Here is the code generated by running the query...
IF OBJECT_ID('vw_SalesTemp') IS NOT NULL
DROP VIEW dbo.[vw_SalesTemp]
go
CREATE VIEW dbo.vw_SalesTemp
WITH SCHEMABINDING
AS
SELECT [SalesOrderID], [RevisionNumber], [OrderDate], [DueDate], [ShipDate], [Status], [OnlineOrderFlag], [SalesOrderNumber], [PurchaseOrderNumber], [AccountNumber], [CustomerID], [SalesPersonID], [TerritoryID], [BillToAddressID], [ShipToAddressID], [ShipMethodID], [CreditCardID], [CreditCardApprovalCode], [CurrencyRateID], [SubTotal], [TaxAmt], [Freight], [TotalDue], [Comment], [rowguid], [ModifiedDate]
FROM Sales.SalesOrderHeader
go
IF OBJECT_ID('vw_SalesDetailsTemp') IS NOT NULL
DROP VIEW dbo.[vw_SalesDetailsTemp]
go
CREATE VIEW dbo.vw_SalesDetailsTemp
WITH SCHEMABINDING
AS
SELECT [SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], [LineTotal], [rowguid], [ModifiedDate]
FROM Sales.SalesOrderDetail
go
IF OBJECT_ID('vw_ProductTemp') IS NOT NULL
DROP VIEW dbo.[vw_ProductTemp]
go
CREATE VIEW dbo.vw_ProductTemp
WITH SCHEMABINDING
AS
SELECT [ProductID], [Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], [SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], [ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], [SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate]
FROM Production.Product
go
Note that this T-SQL is easy and simple to be changed, so If I need to filter out uniqueidentifier columns or those that names start with 'Id%', adding where clauses would do the trick.
This is a quick post, but I hope it became useful for someone, or at least the idea behind it...
You can download the source code here. (Please note that the file contains content in Portuguese and English).
See ya.
Luti
Today I were in a customer and had to do a very manual and repetitive task: create many views with different names from the source tables, but with all the fields defined in the table schema.
What is the reason for that? We´re creating a temporary environment where I´m putting a lot of data and we´ll expose an "interface" using views, that the (power) business user will use to create queries and reports. So this abstraction layer, that in the present moment will reflect almost all tables, will be used to avoid some future rework and clashes between both sides (in case the table structure changes), and to make easier to deal with security.
Now that you´re aware of the context let´s see what I had come up with... I could simply write 50 views with all the fields, but it would take a long time, so I created a quick script that will generate the code I need.
To create that I used a temporary table with N records containing schema and existing tables names, besides a column with the name of the view I´ll create. Using this table in a CTE, I leveraged the CROSS APPLY operator to generate a string based on sys.schemas, sys.columns and sys.objects, creating a comma delimited enumeration of columns based on a XML trick.
Here is the T-SQL code using AdventureWorks2008 for you to play, and maybe, it will be useful in the future.
USE AdventureWorks2008
go
WITH TableView AS
(SELECT UsrSchema, UsrTable, UsrView
FROM ( VALUES
('Sales', 'SalesOrderHeader', 'SalesTemp'),
('Sales', 'SalesOrderDetail', 'SalesDetailsTemp'),
('Production', 'Product', 'ProductTemp'))
AS T(UsrSchema, UsrTable, UsrView))
SELECT
ViewsCode.Instruction
FROM TableView
CROSS APPLY
(SELECT
'
IF OBJECT_ID(''vw_'+ TableView.UsrView +''') IS NOT NULL
DROP VIEW dbo.[vw_'+ TableView.UsrView +']
go
CREATE VIEW dbo.vw_' + TableView.UsrView + '
WITH SCHEMABINDING
AS
SELECT ' +
STUFF(
(SELECT N', ' + QUOTENAME(SC.name) AS [text()]
FROM SYS.columns AS SC
INNER JOIN sys.objects AS SO
ON SO.object_id = SC.object_id
INNER JOIN sys.schemas AS SS
ON SO.schema_id = SS.schema_id
WHERE SO.type = 'U'
AND SO.name = TableView.UsrTable
AND SS.name = TableView.UsrSchema
FOR XML PATH('')), 1, 2, N'') + '
FROM ' + TableView.UsrSchema + '.' + TableView.UsrTable + '
go'
AS Instruction) AS ViewsCode
go
Here is the code generated by running the query...
IF OBJECT_ID('vw_SalesTemp') IS NOT NULL
DROP VIEW dbo.[vw_SalesTemp]
go
CREATE VIEW dbo.vw_SalesTemp
WITH SCHEMABINDING
AS
SELECT [SalesOrderID], [RevisionNumber], [OrderDate], [DueDate], [ShipDate], [Status], [OnlineOrderFlag], [SalesOrderNumber], [PurchaseOrderNumber], [AccountNumber], [CustomerID], [SalesPersonID], [TerritoryID], [BillToAddressID], [ShipToAddressID], [ShipMethodID], [CreditCardID], [CreditCardApprovalCode], [CurrencyRateID], [SubTotal], [TaxAmt], [Freight], [TotalDue], [Comment], [rowguid], [ModifiedDate]
FROM Sales.SalesOrderHeader
go
IF OBJECT_ID('vw_SalesDetailsTemp') IS NOT NULL
DROP VIEW dbo.[vw_SalesDetailsTemp]
go
CREATE VIEW dbo.vw_SalesDetailsTemp
WITH SCHEMABINDING
AS
SELECT [SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], [LineTotal], [rowguid], [ModifiedDate]
FROM Sales.SalesOrderDetail
go
IF OBJECT_ID('vw_ProductTemp') IS NOT NULL
DROP VIEW dbo.[vw_ProductTemp]
go
CREATE VIEW dbo.vw_ProductTemp
WITH SCHEMABINDING
AS
SELECT [ProductID], [Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], [SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], [ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], [SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate]
FROM Production.Product
go
Note that this T-SQL is easy and simple to be changed, so If I need to filter out uniqueidentifier columns or those that names start with 'Id%', adding where clauses would do the trick.
This is a quick post, but I hope it became useful for someone, or at least the idea behind it...
You can download the source code here. (Please note that the file contains content in Portuguese and English).
See ya.
Luti
Subscribe to:
Posts (Atom)
