Tuesday, November 20, 2012
Decisions decisions..
So I inherited some SSRS reports that need some updating - mostly appearance stuff.. the boss wants the reports to all have a similar look and feel.. BUT yesterday in the staff meeting it turns out he also wants me to convert the PROC datasources all into table valued functions.. because they're cooler.. (which, well, they are, but anyway..)
So I open up the source of the proc and inside I find a whole bunch of #temp tables. The original author filled a series of #temp tables and them hooked them all together with unions at the end. So I am now faced with a decision - do I convert these #temp tables into:
- table variables
- views
or
- subqueries
Table variables - I like doing this because it's the most similar. I don't like doing this because I have to figure out what the column datatypes all are in order to declare the table variable, and that's just tedious.
Views - These are nicely reusable! BUT I highly doubt they will ever get reused, and it creates a bunch of otherwise useless objects cluttering up the list of views.
Subqueries - grab the query and stick it in parenthesis where the #temp table is currently called. Gets the job done, no risk of changing the results, quick and easy, I like it. Downside is .. well, it feels kinda cheesy, plus there's a chance that one of the #temp tables may be called more than once, and a there's a few of them.
I'm not an "elegant code" kind of guy, I prefer to get the job done as quickly as possible and move on. Variables have the lowest impact with the fewest chance of the unexpected. Subqueries are risky but super quick implementation if everything goes right. Views would be quick too.. probably the quickest. Clutter is less of a concern to me personally than time is, but there are other DBA's and developers here and I don't have the slightest idea how someone else might react.. making objects in a database feels like bumping somebody's shoulder in a hallway on accident.
Overall though, I think views is the way to go. They're super quick both to create and to implement into the rest of the new table function, PLUS I may end up being able to reuse one later, which would be a great bonus. Views it is
Wednesday, August 29, 2012
Here's some SQL code to change any date into the date for a particular day of that week.
I'm making a report in which the business wants to see values aggregated by week-ending date (aka Friday). So all the dates have to be change dinto the friday of that week. The DATEADD formula below will do that.
DECLARE @sunday AS SMALLDATETIME
SET @sunday = '8/26/2012'
DECLARE @friday AS SMALLDATETIME
SET @friday = '8/31/2012'
DECLARE @saturday AS SMALLDATETIME
SET @saturday = '9/1/2012'
SELECT
DATEADD(day, (DATEPART(dw, @sunday)-6)*-1, @sunday),
DATEADD(day, (DATEPART(dw, @friday)-6)*-1, @friday),
DATEADD(day, (DATEPART(dw, @saturday)-6)*-1, @saturday)
Monday, April 30, 2012
I updated my previous entry as a stored procedure with error catching and rollback.
usage:
EXEC dbo.usp_CloneDBData @srcdb, @dstdb -- Source and Destination database names respectively. This version does not work using linked servers, but could easily be modified to do so.
CREATE PROCEDURE dbo.usp_CloneDBData -- table schemas must be identical
@srcdb AS VARCHAR(1000),
@dstdb AS VARCHAR(1000)
AS
--DECLARE @srcdb AS VARCHAR(1000)
--DECLARE @dstdb AS VARCHAR(1000)
--SET @srcdb = '[_test1]'
--SET @dstdb = '_test2'
DECLARE @allsql AS NVARCHAR(4000)
SET @srcdb = '[' + REPLACE(REPLACE(@srcdb,'[',''),']','') + ']'
SET @dstdb = '[' + REPLACE(REPLACE(@dstdb,'[',''),']','') + ']'
IF (@srcdb = @dstdb)
BEGIN
RAISERROR ('Source and Destination Databases cannot be the same', 16, 1)
RETURN
END
SET @allsql = '
-- truncate and refill from an identical table in another database
EXEC '+@dstdb+'.dbo.sp_msforeachtable ''ALTER TABLE ? NOCHECK CONSTRAINT all''
DECLARE cur CURSOR FOR
SELECT table_name, table_schema
FROM '+@srcdb+'.INFORMATION_SCHEMA.tables tab
WHERE table_type = ''BASE TABLE''
OPEN cur
DECLARE @tablename VARCHAR(255) ,@tableschema VARCHAR(255)
FETCH NEXT FROM cur INTO @tablename, @tableschema
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
IF ( @@FETCH_STATUS <> -2 )
DECLARE @sqltxt NVARCHAR(4000)
DECLARE @columnlist VARCHAR(4000)
Select @columnlist = ISNULL(Stuff(
(
Select '', '' + C.COLUMN_NAME
From [_Test1].INFORMATION_SCHEMA.COLUMNS As C
Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
And C.TABLE_NAME = T.TABLE_NAME
Order By C.ORDINAL_POSITION
For Xml Path('''')
), 1, 2, '''') ,'''')
From [_Test1].INFORMATION_SCHEMA.TABLES As T
WHERE T.TABLE_NAME = @tablename
SET @sqltxt = N''''
EXEC dbo.sp_executesql @sqltxt
SET @sqltxt = N''
use '+@dstdb+';
DELETE ['' + @tableschema + ''].['' + @tablename + ''];
IF ((SELECT OBJECTPROPERTY( OBJECT_ID(N''''['' + @tableschema + ''].['' + @tablename + '']''''), ''''TableHasIdentity'''')) = 1) SET IDENTITY_INSERT ['' + @tableschema + ''].['' + @tablename + ''] ON;
INSERT INTO ['' + @tableschema + ''].['' + @tablename + ''] (''+@columnlist+'') SELECT ''+@columnlist+'' FROM [_Test1].['' + @tableschema + ''].['' + @tablename + ''];
IF ((SELECT OBJECTPROPERTY( OBJECT_ID(N''''['' + @tableschema + ''].['' + @tablename + '']''''), ''''TableHasIdentity'''')) = 1) SET IDENTITY_INSERT ['' + @tableschema + ''].['' + @tablename + ''] OFF;
''
EXEC dbo.sp_executesql @sqltxt
FETCH NEXT FROM cur INTO @tablename, @tableschema
END
CLOSE cur
DEALLOCATE cur
exec '+@dstdb+'.dbo.sp_msforeachtable ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all''
'
BEGIN TRY
BEGIN TRAN
EXEC dbo.sp_executesql @allsql
COMMIT TRAN
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
--DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY()
--,@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
1 -- State.
);
ROLLBACK TRAN
END CATCH
Thursday, April 26, 2012
SQL Server TSQL script to clone a database's tables and data
the following script will create two dissimilar databases for demonstration purposes, insert different data into them, identify the schema differences, drop the tables that are different out of the destination database, create and execute scripts to create all missing tables, truncate the tables in the destination database, then insert all data from all tables in the source into the tables in the destination.
to do a find replace: _test1 is the source, _test2 is the destination
you do not need the last 6 select statements or anything above the comment "-- find any schema differences between tables and drop"
USE master
go
CREATE DATABASE [_Test1]
go
CREATE DATABASE [_Test2]
go
USE [_Test1]
go
CREATE TABLE tbl1
(
col1 INT NULL,
col2 VARCHAR(1) NULL,
col3 bit NULL,
col4 float NULL,
col5 NUMERIC(18,5) NULL
)
GO
CREATE TABLE tbl2
(
col1 INT NULL,
col2 INT NULL,
col3 INT NULL,
col4 INT NULL,
col5 INT NULL
)
GO
CREATE TABLE tbl3
(
col1 INT NULL,
col2 INT NULL,
col3 INT NULL,
col4 INT NULL,
col5 INT NULL
)
GO
INSERT dbo.tbl1 (col1,col2,col3,col4,col5) VALUES (1,2,3,4,5)
INSERT dbo.tbl2 (col1,col2,col3,col4,col5) VALUES (1,2,3,4,5)
INSERT dbo.tbl3 (col1,col2,col3,col4,col5) VALUES (1,2,3,4,5)
go
USE [_Test2]
go
CREATE TABLE tbl1
(
col1 INT NULL,
col2 VARCHAR(1) NULL,
col3 bit NULL,
col4 float NULL,
col5 NUMERIC(18,5) NULL
)
GO
CREATE TABLE tbl2
(
col1 INT NULL,
col2 INT NULL,
col3 INT NULL,
col4 INT NULL
)
GO
----CREATE TABLE tbl3
----(
---- col1 INT NULL,
---- col2 INT NULL,
---- col3 INT NULL,
---- col4 INT NULL,
---- col5 INT NULL
----)
----GO
INSERT dbo.tbl1 (col1,col2,col3,col4,col5) VALUES (1,2,3,4,6)
INSERT dbo.tbl1 (col1,col2,col3,col4,col5) VALUES (1,2,3,4,7)
go
-- tables are different
SELECT * FROM [_test1].dbo.tbl1
SELECT * FROM [_test2].dbo.tbl1
SELECT * FROM [_test1].dbo.tbl2
SELECT * FROM [_test2].dbo.tbl2
SELECT * FROM [_test1].dbo.tbl3
--SELECT * FROM [_test2].dbo.tbl3 -- this one produces an error. the table does not exist
go
USE [_Test1]
go
-- find any schema differences between tables and drop
DECLARE @sqltxt NVARCHAR(4000)
SELECT @sqltxt = STUFF((
SELECT DISTINCT ';DROP TABLE [_Test2].[' + t2.TABLE_SCHEMA + '].[' + t2.TABLE_NAME + ']'
FROM [_Test1].information_schema.COLUMNS c1
LEFT JOIN [_Test2].information_schema.tables t2 ON c1.TABLE_SCHEMA = t2.TABLE_SCHEMA AND c1.TABLE_NAME = t2.TABLE_NAME
FULL OUTER JOIN [_Test2].information_schema.COLUMNS c2 ON
c1.TABLE_SCHEMA = c2.TABLE_SCHEMA
AND c1.TABLE_NAME = c2.TABLE_NAME
AND c1.COLUMN_NAME = c2.COLUMN_NAME
WHERE
( ISNULL(c1.COLUMN_NAME,'') != ISNULL(c2.COLUMN_NAME,'') or
ISNULL(c1.IS_NULLABLE,'') != ISNULL(c2.IS_NULLABLE,'') or
ISNULL(c1.DATA_TYPE,'') != ISNULL(c2.DATA_TYPE,'') or
ISNULL(c1.NUMERIC_PRECISION,99) != ISNULL(c2.NUMERIC_PRECISION,99) or
ISNULL(c1.NUMERIC_SCALE,-1) != ISNULL(c2.NUMERIC_SCALE,-1) or
ISNULL(c1.CHARACTER_MAXIMUM_LENGTH,-1) != ISNULL(c2.CHARACTER_MAXIMUM_LENGTH,-1) or
ISNULL(c1.COLUMN_DEFAULT,'') != ISNULL(c2.COLUMN_DEFAULT,'') )
AND t2.TABLE_NAME IS NOT NULL
FOR XML PATH('')), 1, 1, '')
EXECUTE [_Test2].dbo.sp_executesql @sqltxt
go
-- generate script to create any missing tables
DECLARE @sqltxt NVARCHAR(4000)
SELECT @sqltxt = STUFF((
select ';create table [_Test2].[' + so.TABLE_SCHEMA + '].[' + so.TABLE_NAME + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.TABLE_NAME + ' ADD CONSTRAINT ' + tc.Constraint_Name + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from INFORMATION_SCHEMA.tables so
JOIN (
SELECT t1.TABLE_SCHEMA, t1.TABLE_NAME
FROM [_Test1].information_schema.tables t1
LEFT JOIN [_Test2].information_schema.tables t2 ON t1.TABLE_SCHEMA = t2.TABLE_SCHEMA AND t1.TABLE_NAME = t2.TABLE_NAME
WHERE t2.TABLE_NAME IS NULL
) list ON so.TABLE_SCHEMA = list.TABLE_SCHEMA AND so.TABLE_NAME = list.TABLE_NAME
cross apply
(SELECT
'['+column_name+'] ' +
data_type + case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'decimal' then '(' + cast(numeric_precision_radix as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
when 'numeric' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
case when exists (
select id from syscolumns
where object_name(id)=so.TABLE_NAME
and name=column_name
and columnproperty(id,name,'IsIdentity') = 1
) then
'IDENTITY(' +
cast(ident_seed(so.TABLE_NAME) as varchar) + ',' +
cast(ident_incr(so.TABLE_NAME) as varchar) + ')'
else ''
end + ' ' +
(case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +
case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', '
from information_schema.columns where table_name = so.TABLE_NAME
order by ordinal_position
FOR XML PATH('')) o (list)
left join
information_schema.table_constraints tc
on tc.Table_name = so.TABLE_NAME
AND tc.Constraint_Type = 'PRIMARY KEY'
cross apply
(select '[' + Column_Name + '], '
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH('')) j (list)
FOR XML PATH('')), 1, 1, '')
EXECUTE [_Test2].dbo.sp_executesql @sqltxt
go
-- truncate and refill from an identical table in another database
DECLARE cur CURSOR FOR
SELECT table_name, table_schema
FROM [_Test1].INFORMATION_SCHEMA.tables tab
WHERE table_type = 'BASE TABLE'
OPEN cur
DECLARE @tablename VARCHAR(255) ,@tableschema VARCHAR(255)
FETCH NEXT FROM cur INTO @tablename, @tableschema
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
IF ( @@FETCH_STATUS <> -2 )
DECLARE @sqltxt NVARCHAR(255)
SET @sqltxt = N'TRUNCATE TABLE [_Test2].[' + @tableschema + '].[' + @tablename + ']; INSERT INTO [_Test2].[' + @tableschema + '].[' + @tablename + '] SELECT * FROM [_Test1].[' + @tableschema + '].[' + @tablename + ']'
EXEC dbo.sp_executesql @sqltxt
FETCH NEXT FROM cur INTO @tablename, @tableschema
END
CLOSE cur
DEALLOCATE cur
go
--tables ARE identical
SELECT * FROM [_test1].dbo.tbl1
SELECT * FROM [_test2].dbo.tbl1
SELECT * FROM [_test1].dbo.tbl2
SELECT * FROM [_test2].dbo.tbl2
SELECT * FROM [_test1].dbo.tbl3
SELECT * FROM [_test2].dbo.tbl3
Tuesday, March 13, 2012
C# parser for RDL files when using with ReportViewer control
The ASP.NET ReportViewer webcontrol is intended to be used with an RDLC file, which is a stripped down version of an RDL file. RDL files are for using with SSRS reporting services -- that is, where you keep the report on a SQL Server hosted website and can run the reports right there on the website. But if you want to embed an SSRS report into your ASP.NET application (I am doing in a few MVC applications, which is tricky too, but I never posted a blog entry about how to do it), anyway, if you want to include an SSRS report in your application, you use the ReportViewer control and when it loads you basically forcefeed it your connection and your parameters and all that. Well, that annoys me, since the RDL files already have the connection strings and parameters in them, it seems to me like you should be able to just use what's embedded in the report, so I'm working on building my own dynamic report viewer. The first step is to parse the report and get the connection string and parameters out of it. In order to efficiently transfer the parameters and the connection string as a unit, I made two classes. One holds parameter info, the other holds connection string info PLUS a collection of parameters.
//Parameter Class
public class rptParam
{
public String ParameterName { get; set; }
public String Datatype { get; set; }
public String Prompt { get; set; }
}
//Report Attributes Class
public class rptAttributes
{
public List Parameters { get; set; }
public String DataSourceName { get; set; }
public String CommandText { get; set; }
}
Then you can use this code wherever you want, it's uncommented, my apologies, but it's pretty straightforward anyway:
private rptAttributes ReadFile(string filename)
{
rptAttributes rptAtt = new rptAttributes();
List l = new List();
// Create an XmlReader
using (XmlReader reader = XmlTextReader.Create(filename))
{
reader.ReadToFollowing("DataSourceName");
rptAtt.DataSourceName = "DataSourceName: " + reader.ReadElementContentAsString();
reader.ReadToFollowing("CommandText");
rptAtt.CommandText="CommandText: " + reader.ReadElementContentAsString();
string temp = "";
reader.ReadToFollowing("ReportParameter");
reader.MoveToAttribute("Name");
temp = reader.Value;
reader.ReadToFollowing("DataType");
if (reader.NodeType != XmlNodeType.None)
{
rptParam rp = new rptParam();
rp.ParameterName = temp;
rp.Datatype = reader.ReadElementContentAsString();
reader.ReadToFollowing("Prompt");
rp.Prompt = reader.ReadElementContentAsString();
l.Add(rp);
}
rptAtt.Parameters = l;
}
return rptAtt;
}
Saturday, February 4, 2012
Glimpses of Skiff: My Defense of Neuro-Diversity
Glimpses of Skiff: My Defense of Neuro-Diversity: I was running late as usual. I rushed into the meeting room, slightly out of breath, and found the closest chair. I sat back and obeserved...
Friday, February 3, 2012
SQLComplete is a great product
I have been using SQL Complete by dbForge for a couple years now, and I have to say - it is FANTASTIC! It speeds up my development time by at least double, and just generally takes the tedium out of typing in big scripts or debugging long old code written by others.
Download the free version here, tell 'em I sent ya!
http://www.devart.com/dbforge/sql/sqlcomplete/
Subscribe to:
Posts (Atom)