I have fought against the poodle with his gory, deadly paws;
I have faced the fearsome kitten, wild and bony,
And somehow I've evaded the enormous chomping jaws
Of the frighteningly ferocious Shetland pony.
My triumph o'er the rabbit is now sung throughout the land,
And men still speak in whispers of the day
When, attacked by twelve mosquitoes, with my one unwounded hand,
I killed nine of them and drove the rest away.
I have faced the housefly in his lair, I have stalked the ladybug
And the caterpillar, grim and fierce and hairy;
That trophy there is bumblebee, and this, my favourite rug,
Has been fashioned from the hide of a canary.
I have dove into the ocean to do combat with a shrimp,
I have dared the hen to come on out and fight;
I have battled with the butterfly (that's why I have this limp),
And I slew a monstrous grubworm just last night.
But this evening I must sally forth to meet the savage moth,
And if I don't come back in time for tea,
You shall know that I fell gallantly, as gallantly I fought
So please be gentle when you speak of me.
Show me the boy who never threw
A stone at someone's cat;
Or never hurled a snowball swift
At someone's high silk hat.
Who never ran away from school,
To seek the swimming hole;
Or slyly from a neighbor's yard
Green apples never stole.
Show me the boy who never broke
A pane of window glass;
Who never disobeyed the sign
That says: "Keep off the grass."
Who never did a thousand things,
That grieve us sore fo tell;
And I'll show you a little boy
Who must be far from well.
This took me a couple verses to realize it was a prayer about a dog.
Master, this is Thy Servant. He is rising eight weeks old.
He is mainly Head and Tummy. His legs are uncontrolled.
But Thou hast forgiven his ugliness, and settled him on Thy knee . . .
Art Thou content with Thy Servant? He is very comfy with Thee.
Master, behold a Sinner? He hath committed a wrong.
He hath defiled Thy Premises through being kept in too long.
Wherefore his nose has been rubbed in the dirt, and his self-respect has been bruiséd.
Master, pardon Thy Sinner, and see he is properly looséd.
Master — again Thy Sinner! This that was once Thy Shoe,
He hath found and taken and carried aside, as fitting matter to chew.
Now there is neither blacking nor tongue, and the Housemaid has us in tow.
Master, remember Thy Servant is young, and tell her to let him go!
Master, extol Thy Servant! He hath met a most Worthy Foe!
There has been fighting all over the Shop — and into the Shop also!
Till cruel umbrellas parted the strife (or I might have been choking him yet).
But Thy Servant has had the Time of his Life — and now shall we call on the vet?
Master, behold Thy Servant! Strange children came to play,
And because they fought to caress him, Thy Servant wentedst away.
But now that the Little Beasts have gone, he has returned to see
(Brushed — with his Sunday collar on) what they left over from tea.
Master, pity Thy Servant! He is deaf and three parts blind,
He cannot catch Thy Commandments. He cannot read Thy Mind.
Oh, leave him not in his loneliness; nor make him that kitten’s scorn.
He hath had none other God than Thee since the year that he was born!
Lord, look down on Thy Servant! Bad things have come to pass,
There is no heat in the midday sun, nor health in the wayside grass.
His bones are full of an old disease — his torments run and increase.
Lord, make haste with Thy Lightnings and grant him a, quick release!
One evening at dusk as Noah stood on his Ark,
Putting green oil in starboard side lamp,
His wife came along and said, 'Noah, summat's wrong,
Our cabin is getting quite damp.
Noah said, 'Is that so?' Then he went down below,
And found it were right what she'd said,
For there on the floor quite a puddle he saw,
It was slopping around under t' bed.
Said he, 'There's an 'ole in the bottom somewhere,
We must find it before we retire.'
Then he thowt for a bit, and he said 'Aye, that's it,
A bloodhound is what we require.'
Se he went and fetched bloodhound from place where it lay,
'Tween the skunk and the polecat it were,
And as things there below, were a trifle so-so,
It were glad of a breath of fresh air.
They followed the sound as it went sniffing round,
'Til at last they located the leak,
'Twere a small hole in the side, about two inches wide,
Where a swordfish had poked in its beak.
And by gum! how the wet squirted in through that hole,
Well, young Shem who at sums was expert,
Worked it out on his slate that it came at the rate,
Of per gallon, per second, per squirt.
The bloodhound tried hard to keep water in check,
By lapping it up with his tongue,
But it came in so fast through that hole, that at last,
He shoved in his nose for a bung.
The poor faithful hound, he were very near drowned,
They dragged him away none too soon,
For the stream as it rose, pushed its way up his nose,
And blew him up like a balloon.
And then Mrs Noah shoved her elbow in t'hole,
And said,' Eh! it's stopped I believe,'
But they found very soon as she'd altered her tune,
For the water had got up her sleeve.
When she saw as her elbow weren't doing much good,
She said to Noah, 'I've an idea,
You sit on the leak and by t'end of the week,
There's no knowing, the weather may clear.'
Noah didn't think much to this notion, at all,
But reckoned he'd give it a try,
On the 'ole down he flopped, and the leaking all stopped,
And all… except him, was quite dry.
They took him his breakfast and dinner and tea,
As day after day there he sat,
'Til the rain was all passed and they landed at last,
On top side of Mount Ararat.
And that is how Noah got them all safe ashore,
But ever since then, strange to tell,
Them as helped save the Ark has all carried a mark,
Aye, and all their descendants as well.
That's why dog has a cold nose, and ladies cold elbows,
You'll also find if you enquire,
That's why a man takes his coat tails in hand,
And stands with his back to the fire.
There are strange things done in the midnight sun
By the men who moil for gold;
The Arctic trails have their secret tales
That would make your blood run cold;
The Northern Lights have seen queer sights,
But the queerest they ever did see
Was that night on the marge of Lake Lebarge
I cremated Sam McGee.
Now Sam McGee was from Tennessee, where the cotton blooms and blows.
Why he left his home in the South to roam 'round the Pole, God only knows.
He was always cold, but the land of gold seemed to hold him like a spell;
Though he'd often say in his homely way that "he'd sooner live in hell."
On a Christmas Day we were mushing our way over the Dawson trail.
Talk of your cold! through the parka's fold it stabbed like a driven nail.
If our eyes we'd close, then the lashes froze till sometimes we couldn't see;
It wasn't much fun, but the only one to whimper was Sam McGee.
And that very night, as we lay packed tight in our robes beneath the snow,
And the dogs were fed, and the stars o'erhead were dancing heel and toe,
He turned to me, and "Cap," says he, "I'll cash in this trip, I guess;
And if I do, I'm asking that you won't refuse my last request."
Well, he seemed so low that I couldn't say no; then he says with a sort of moan:
"It's the cursèd cold, and it's got right hold, till I'm chilled clean through to the bone.
Yet 'tain't being dead — it's my awful dread of the icy grave that pains;
So I want you to swear that, foul or fair, you'll cremate my last remains."
A pal's last need is a thing to heed, so I swore I would not fail;
And we started on at the streak of dawn; but God! he looked ghastly pale.
He crouched on the sleigh, and he raved all day of his home in Tennessee;
And before nightfall a corpse was all that was left of Sam McGee.
There wasn't a breath in that land of death, and I hurried, horror-driven,
With a corpse half hid that I couldn't get rid, because of a promise given;
It was lashed to the sleigh, and it seemed to say: "You may tax your brawn and brains,
But you promised true, and it's up to you, to cremate those last remains."
Now a promise made is a debt unpaid, and the trail has its own stern code.
In the days to come, though my lips were dumb, in my heart how I cursed that load.
In the long, long night, by the lone firelight, while the huskies, round in a ring,
Howled out their woes to the homeless snows — Oh God! how I loathed the thing.
And every day that quiet clay seemed to heavy and heavier grow;
And on I went, though the dogs were spent and the grub was getting low;
The trail was bad, and I felt half mad, but I swore I would not give in;
And I'd often sing to the hateful thing, and it hearkened with a grin.
Till I came to the marge of Lake Lebarge, and a derelict there lay;
It was jammed in the ice, but I saw in a trice it was called the "Alice May."
And I looked at it, and I thought a bit, and I looked at my frozen chum;
Then "Here," said I, with a sudden cry, "is my cre-ma-tor-eum."
Some planks I tore from the cabin floor, and I lit the boiler fire;
Some coal I found that was lying around, and I heaped the fuel higher;
The flames just soared, and the furnace roared — such a blaze you seldom see;
And I burrowed a hole in the glowing coal, and I stuffed in Sam McGee.
Then I made a hike, for I didn't like to hear him sizzle so;
And the heavens scowled, and the huskies howled, and the wind began to blow.
It was icy cold, but the hot sweat rolled down my cheeks, and I don't know why;
And the greasy smoke in an inky cloak went streaking down the sky.
I do not know how long in the snow I wrestled with grisly fear;
But the stars came out and they danced about ere again I ventured near;
I was sick with dread, but I bravely said: "I'll just take a peep inside.
I guess he's cooked, and it's time I looked"; ... then the door I opened wide.
And there sat Sam, looking cool and calm, in the heart of the furnace roar;
And he wore a smile you could see a mile, and said: "Please close that door.
It's fine in here, but I greatly fear, you'll let in the cold and storm —
Since I left Plumtree, down in Tennessee, it's the first time I've been warm."
There are strange things done in the midnight sun
By the men who moil for gold;
The Arctic trails have their secret tales
That would make your blood run cold;
The Northern Lights have seen queer sights,
But the queerest they ever did see
Was that night on the marge of Lake Lebarge
I cremated Sam McGee.
"Who stuffed that white owl?" No one spoke in the shop,
The barber was busy, and he couldn't stop;
The customers, waiting their turns, were all reading
The "Daily," the "Herald," the "Post," little heeding
The young man who blurted out such a blunt question;
Not one raised a head, or even made a suggestion;
And the barber kept on shaving.
"Don't you see, Mr. Brown,"
Cried the youth, with a frown,
"How wrong the whole thing is,
How preposterous each wing is,
How flattened the head is, how jammed down the neck is --
In short, the whole owl, what an ignorant wreck 't is!
I make no apology;
I've learned owl-eology.
I've passed days and nights in a hundred collections,
And cannot be blinded to any deflections
Arising from unskilful fingers that fail
To stuff a bird right, from his beak to his tail.
Mister Brown! Mr. Brown!
Do take that bird down,
Or you'll soon be the laughingstock all over town!"
And the barber kept on shaving.
"I've studied owls,
And other night-fowls,
And I tell you
What I know to be true;
An owl cannot roost
With his limbs so unloosed;
No owl in this world
Ever had his claws curled,
Ever had his legs slanted,
Ever had his bill canted,
Ever had his neck screwed
Into that attitude.
He cant do it, because
'Tis against all bird-laws.
Anatomy teaches,
Ornithology preaches,
An owl has a toe
That can't turn out so!
I've made the white owl my study for years,
And to see such a job almost moves me to tears!
Mr. Brown, I'm amazed
You should be so gone crazed
As to put up a bird
In that posture absurd!
To look at that owl really brings on a dizziness;
The man who stuffed him don't half know his business!"
And the barber kept shaving.
"Examine those eyes
I'm filled with surprise
Taxidermists should pass
Off on you such poor glass;
So unnatural they seem
They'd make Audubon scream,
And John Burroughs laugh
To encounter such chaff.
Do take that bird down;
Have him stuffed again, Brown!"
And the barber kept on shaving!
"With some sawdust and bark
I could stuff in the dark
An owl better than that.
I could make an old hat
Look more like an owl
Than that horrid fowl,
Stuck up there so stiff like a side of coarse leather.
In fact, about him there's not one natural feather."
Just then, with a wink and a sly normal lurch,
The owl, very gravely, got down from his perch,
Walked around, and regarded his fault-finding critic
(Who thought he was stuffed) with a glance analytic,
And then fairly hooted, as if he should say:
"Your learning's at fault this time, anyway:
Don't waste it again on a live bird, I pray.
I'm an owl; you're another. Sir Critic, good day!"
And the barber kept on shaving.
/*
The C.R.U.D. of Excel
=====================
Phil and I have teamed up on this workbench, which demonstrates how to
create, read, update and delete information in Excel using T-SQL, from
SQL Server. As always, the workbench is structured so that it can be
pasted into Query Analyser and SSMS, and the individual examples
executed - you can download the .sql from the "Code Download" link above,
load it up and start experimenting!
Contents
========
Creating Excel spreadsheets via ADODB
Manipulating Excel data via a linked server
Synchronising the Spreadsheet with SQL Server Tables
Manipulating Excel data using OPENDATASOURCE and OPENROWSET functions
Creating Excel spreadsheets using sp_MakeWebTask
OLE Automation
We start by showing you how to create an Excel Spreadsheet from
SQL Server in TSQL(Transact SQL), create a worksheet, attach to it as
a linked server, write to it, read from it, update it as if it was an
ordinary SQL Server Database table, and then synchronise the data in
the worksheet with SQL Server. We also illustrate the use of OPENQUERY,
OPENDATASOURCE and OPENROWSET.
To create the Excel spreadsheet, we show how to attach to an ADODB
source from SQL Server and execute SQL against that source. We then
show you an alternative 'quick cheat' way (using sp_makewebtask) to
create and populate an excel spreadsheet from Transact SQL.
If you need more control over the Excel Spreadsheet that you are
creating, we then show you how to do it via OLE automation. This will
enable you to do anything you can do via keystrokes, and allow you
to generate full Excel reports with pivot tables and Graphs.
Using this technique, you should be able to populate the data, or
place data in particular calls or ranges. You can even do 'macro
substitutions'
A word of caution before you start. If you have your security wide
open, it is not just you who would be able to write out data as
a spreadsheet. An intruder would be able to do it with that list
of passwords or credit-card numbers. In a production system,
this sort of operation needs to be properly ring-fenced. We tend
to create a job queue and have a special user, with the apporpriate
permissions, on the Task Scheduler, to do anything that involves
OLE automation or xp_CMDShell. Security precautions can get quite
complex, but they are outside the scope of the article.
Some of what we illustrate can be done using DTS or SSIS.
Unfortunately, these are outside the scope of this article. In
fact, transferring data between Excel and SQL Server can be done
in a surprising variety of ways and it would be fun one day to
try to list them all
First we need some simple test data
*/
CREATE TABLE ##CambridgePubs
(Pubname VARCHAR(40),
Address VARCHAR(80),
Postcode VARCHAR(8))
INSERT INTO ##CambridgePubs (PubName, Address, Postcode)
SELECT 'Bees In The Wall','36 North Road,
Whittlesford, Cambridge','CB2 4NZ'
INSERT INTO ##CambridgePubs (PubName, Address, Postcode)
SELECT 'Blackamoors Head','205 Victoria Road,
Cambridge','CB4 3LF'
INSERT INTO ##CambridgePubs (PubName, Address, Postcode)
SELECT 'Blue Lion','2 Horningsea Road,
Fen Ditton, Cambridge','CB5 8SZ'
INSERT INTO ##CambridgePubs (PubName, Address, Postcode)
SELECT 'Cambridge Blue','85-87 Gwydir Street,
Cambridge','CB1 2LG'
INSERT INTO ##CambridgePubs (PubName, Address, Postcode)
SELECT 'Champion Of The Thames','68 King Street,
Cambridge','CB1 1LN'
INSERT INTO ##CambridgePubs (PubName, Address, Postcode)
SELECT 'Cross Keys','77 Ermine Street,
Caxton, Cambridge','CB3 8PQ'
INSERT INTO ##CambridgePubs (PubName, Address, Postcode)
SELECT 'Crown Inn','11 High Street,
Linton, Cambridge','CB1 6HS'
INSERT INTO ##CambridgePubs (PubName, Address, Postcode)
SELECT 'Devonshire Arms','1 Devonshire Road,
Cambridge','CB1 2BH'
INSERT INTO ##CambridgePubs (PubName, Address, Postcode)
SELECT 'Duke Of Argyle','90 Argyle Street,
Cambridge','CB1 3LS'
INSERT INTO ##CambridgePubs (PubName, Address, Postcode)
SELECT 'Duke Of Wellington','49 Alms Hill,
Bourn, Cambridge','CB3 7SH'
INSERT INTO ##CambridgePubs (PubName, Address, Postcode)
SELECT 'Eagle Public House','Benet Street,
Cambridge','CB2 3QN'
/*And so on. (The full import file is in the ZIP, as is the Excel
file!)
Create the table and then execute the contents of CambridgePubs.SQL
Creating Excel spreadsheets via ADODB
-----------------------------------------
First, we need to create the spreadsheet with the correct headings
(PubName, Address, PostCode)
There are two possible ways one might do this. The most obvious way
is using the CREATE statement to create the worksheet and define the
columns, but there seems to be no way of doing this by linking the
excel FILE, unless the Excel file already exists. We need a utility
stored procedure to get at ADODB in order to create databases and
execute DDL and SQL against it. */
CREATE PROCEDURE spExecute_ADODB_SQL
@DDL VARCHAR(2000),
@DataSource VARCHAR(100),
@Worksheet VARCHAR(100)=NULL,
@ConnectionString VARCHAR(255)
= 'Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=%DataSource;
Extended Properties=Excel 8.0'
AS
DECLARE
@objExcel INT,
@hr INT,
@command VARCHAR(255),
@strErrorMessage VARCHAR(255),
@objErrorObject INT,
@objConnection INT,
@bucket INT
SELECT @ConnectionString
=REPLACE (@ConnectionString, '%DataSource', @DataSource)
IF @Worksheet IS NOT NULL
SELECT @DDL=REPLACE(@DDL,'%worksheet',@Worksheet)
SELECT @strErrorMessage='Making ADODB connection ',
@objErrorObject=NULL
EXEC @hr=sp_OACreate 'ADODB.Connection', @objconnection OUT
IF @hr=0
SELECT @strErrorMessage='Assigning ConnectionString property "'
+ @ConnectionString + '"',
@objErrorObject=@objconnection
IF @hr=0 EXEC @hr=sp_OASetProperty @objconnection,
'ConnectionString', @ConnectionString
IF @hr=0 SELECT @strErrorMessage
='Opening Connection to XLS, for file Create or Append'
IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Open'
IF @hr=0 SELECT @strErrorMessage
='Executing DDL "'+@DDL+'"'
IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Execute',
@Bucket out , @DDL
IF @hr<>0
BEGIN
DECLARE
@Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @objErrorObject, @source output,
@Description output,@Helpfile output,@HelpID output
SELECT @strErrorMessage='Error whilst '
+COALESCE(@strErrorMessage,'doing something')+', '
+COALESCE(@Description,'')
RAISERROR (@strErrorMessage,16,1)
END
EXEC @hr=sp_OADestroy @objconnection
GO
--------------------------------------
/* Now we have it, it is easy */
spExecute_ADODB_SQL @DDL='Create table CambridgePubs
(Pubname Text, Address Text, Postcode Text)',
@DataSource ='C:\CambridgePubs.xls'
--the excel file will have been created on the Database server of the
-- database you currently have a connection to
--We could now insert data into the spreadsheet, if we wanted
spExecute_ADODB_SQL @DDL='insert into CambridgePubs
(Pubname,Address,Postcode)
values (''The Bird in Hand'',
''23, Marshall Road, Cambridge CB4 2DQ'',
''CB4 2DQ'')',
@DataSource ='C:\CambridgePubs.xls'
--you could drop it again!
spExecute_ADODB_SQL @DDL='drop table CambridgePubs',
@DataSource ='c:\CambridgePubs.xls'
/* Manipulating Excel data via a linked server
----------------------------------------------
We can now link to the created excel file as follows */
EXEC sp_addlinkedserver 'CambridgePubDatabase',
@srvproduct = '',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'C:\CambridgePubs.xls',
@provstr = 'Excel 8.0;'
GO
EXEC sp_addlinkedsrvlogin 'CambridgePubDatabase', 'false'
GO
--to drop the link, we do this!
--EXEC sp_dropserver 'CambridgePubDatabase', 'droplogins'
-- Get the spreadsheet data via OpenQuery
SELECT * FROM OPENQUERY
(CambridgePubDatabase, 'select * from [CambridgePubs]')
GO
--or more simply, do this
SELECT * FROM CambridgePubDatabase...CambridgePubs
--so now we can insert our data into the Excel Spreadsheet
INSERT INTO CambridgePubDatabase...CambridgePubs
(Pubname, Address, postcode)
SELECT Pubname, Address, postcode FROM ##CambridgePubs
/*Synchronizing the Spreadsheet with SQL Server tables
------------------------------------------------------
As we are directly manipulating the Excel data in the worksheet as if
it was a table we can do joins.
What about synchronising the table after editing the excel spreadsheet?
To try this out, you'll need to delete, alter and insert a few rows
from the excel spreadsheet, remembering to close it after you've done it
*/
--Firstly, we'll delete any rows from ##CambridgePubs
-- that do not exist in the Excel spreadsheet
DELETE FROM ##CambridgePubs
FROM ##CambridgePubs c
LEFT OUTER JOIN CambridgePubDatabase...CambridgePubs ex
ON c.address LIKE ex.address
AND c.pubname LIKE ex.pubname
AND c.postcode LIKE ex.postcode
WHERE ex.pubname IS NULL
-- then we insert into #CambridgePubs any rows in the spreadsheet
-- that don't exist in #CambridgePubs
INSERT INTO ##CambridgePubs (Pubname,Address,Postcode)
SELECT ex.Pubname,ex.Address,ex.Postcode
FROM CambridgePubDatabase...CambridgePubs ex
LEFT OUTER JOIN ##CambridgePubs c
ON c.address LIKE ex.address
AND c.pubname LIKE ex.pubname
AND c.postcode LIKE ex.postcode
WHERE c.pubname IS NULL
--all done (reverse syncronisation would be similar)
/*Manipulating Excel data using OPENDATASOURCE and OPENROWSET
-------------------------------------------------------------
If you don't want to do the linking, you can also read the data like
this*/
SELECT *
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\CambridgePubs.xls";
Extended properties=Excel 8.0')...CambridgePubs
--and write to it
UPDATE OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0',
'Data Source="C:\CambridgePubs.xls";
extended Properties=Excel 8.0')...CambridgePubs
SET Address='St. Kilda Road, Cambridge'
WHERE Pubname = 'Jenny Wren'
INSERT INTO OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0',
'Data Source="C:\CambridgePubs.xls";
extended Properties=Excel 8.0')...CambridgePubs
(Pubname,Address,Postcode )
SELECT 'The St George','65 Cavendish Road','CB2 4RT'
--You can read and write toExcel Sheet using OpenRowSet,
--if the mood takes you
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\CambridgePubs.xls', 'Select * from CambridgePubs')
UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\CambridgePubs.xls',
'Select * from CambridgePubs')
SET Address='34 Glemsford Road' WHERE Address = '65 Cavendish Road'
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\CambridgePubs.xls',
'Select * from CambridgePubs')
(Pubname, Address, Postcode)
SELECT 'The Bull', 'Antioch Road','CB2 5TY'
/*Creating Excel Spreadsheets using sp_makewebtask
--------------------------------------------------
Instead of creating the Excel spreadsheet with OLEDB One can use the
sp_makewebtask
Users must have SELECT permissions to run a specified query and
CREATE PROCEDURE permissions in the database in which the query will run.
The SQL Server account must have permissions to write the generated HTML
document to the specified location. Only members of the sysadmin server
role can impersonate other users.
*/
sp_makewebtask @outputfile = 'c:\CambridgePubsHTML2.xls',
@query = 'Select * from ##CambridgePubs',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='Cambridge Pubs',
@dbname ='MyDatabaseName'
/* This is fine for distributing information from databases but no good
if you subsequently want to open it via ODBC.*/
/*OLE Automation
----------------
So far, so good. However, we really want rather more than this. When
we create an excel file for a business report, we want the data and we
also want nice formatting, defined ranges, sums, calculated fields
and pretty graphs. If we do financial reporting, we want a pivot table
and so on in order to allow a degree of data mining by the recipient.
A different approach is required.
We can, of course, use Excel to extract the data from the database.
However, in this example, we'll create a spreadsheet, write the data
into it, fit the columns nicely and define a range around the data
*/ ALTER PROCEDURE [dbo].[spDMOExportToExcel] (
@SourceServer VARCHAR(30),
@SourceUID VARCHAR(30)=NULL,
@SourcePWD VARCHAR(30)=NULL,
@QueryText VARCHAR(200),
@filename VARCHAR(100),
@WorksheetName VARCHAR(100)='Worksheet',
@RangeName VARCHAR(80)='MyRangeName'
)
AS
DECLARE @objServer INT,
@objQueryResults INT,
@objCurrentResultSet INT,
@objExcel INT,
@objWorkBooks INT,
@objWorkBook INT,
@objWorkSheet INT,
@objRange INT,
@hr INT,
@Columns INT,
@Rows INT,
@Output INT,
@currentColumn INT,
@currentRow INT,
@ResultSetRow INT,
@off_Column INT,
@off_Row INT,
@command VARCHAR(255),
@ColumnName VARCHAR(255),
@value VARCHAR(255),
@strErrorMessage VARCHAR(255),
@objErrorObject INT,
@Alphabet VARCHAR(27)
SELECT @Alphabet='ABCDEFGHIJKLMNOPQRSTUVWXYZ'
IF @QueryText IS NULL
BEGIN
RAISERROR ('A query string is required for spDMOExportToExcel',16,1)
RETURN 1
END
-- Sets the server to the local server
IF @SourceServer IS NULL SELECT @SourceServer = @@servername
SET NOCOUNT ON
SELECT @strErrorMessage = 'instantiating the DMO',
@objErrorObject=@objServer
EXEC @hr= sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
IF @SourcePWD IS NULL OR @SourceUID IS NULL
BEGIN
--use a trusted connection
IF @hr=0 SELECT @strErrorMessage=
'Setting login to windows authentication on '
+@SourceServer, @objErrorObject=@objServer
IF @hr=0 EXEC @hr=sp_OASetProperty @objServer, 'LoginSecure', 1
IF @hr=0 SELECT @strErrorMessage=
'logging in to the requested server using windows authentication on '
+@SourceServer
IF @SourceUID IS NULL AND @hr=0 EXEC @hr=sp_OAMethod @objServer,
'Connect', NULL, @SourceServer
IF @SourceUID IS NOT NULL AND @hr=0
EXEC @hr=sp_OAMethod
@objServer, 'Connect', NULL, @SourceServer ,@SourceUID
END
ELSE
BEGIN
IF @hr=0
SELECT @strErrorMessage = 'Connecting to '''+@SourceServer+
''' with user ID '''+@SourceUID+'''',
@objErrorObject=@objServer
IF @hr=0
EXEC @hr=sp_OAMethod @objServer, 'Connect', NULL,
@SourceServer, @SourceUID, @SourcePWD
END
--now we execute the query
IF @hr=0 SELECT @strErrorMessage='executing the query "'
+@querytext+'", on '+@SourceServer,
@objErrorObject=@objServer,
@command = 'ExecuteWithResults("' + @QueryText + '")'
IF @hr=0
EXEC @hr=sp_OAMethod @objServer, @command, @objQueryResults OUT
IF @hr=0
SELECT @strErrorMessage='getting the first result set for "'
+@querytext+'", on '+@SourceServer,
@objErrorObject=@objQueryResults
IF @hr=0 EXEC @hr=sp_OAMethod
@objQueryResults, 'CurrentResultSet', @objCurrentResultSet OUT
IF @hr=0
SELECT @strErrorMessage='getting the rows and columns "'
+@querytext+'", on '+@SourceServer
IF @hr=0
EXEC @hr=sp_OAMethod @objQueryResults, 'Columns', @Columns OUT
IF @hr=0
EXEC @hr=sp_OAMethod @objQueryResults, 'Rows', @Rows OUT
--so now we have the queryresults. We start up Excel
IF @hr=0
SELECT @strErrorMessage='Creating the Excel Application, on '
+@SourceServer, @objErrorObject=@objExcel
IF @hr=0
EXEC @hr=sp_OACreate 'Excel.Application', @objExcel OUT
IF @hr=0 SELECT @strErrorMessage='Getting the WorkBooks object '
IF @hr=0
EXEC @hr=sp_OAGetProperty @objExcel, 'WorkBooks',
@objWorkBooks OUT
--create a workbook
IF @hr=0
SELECT @strErrorMessage='Adding a workbook ',
@objErrorObject=@objWorkBooks
IF @hr=0
EXEC @hr=sp_OAGetProperty @objWorkBooks, 'Add', @objWorkBook OUT
--and a worksheet
IF @hr=0
SELECT @strErrorMessage='Adding a worksheet ',
@objErrorObject=@objWorkBook
IF @hr=0
EXEC @hr=sp_OAGetProperty @objWorkBook, 'worksheets.Add',
@objWorkSheet OUT
IF @hr=0
SELECT @strErrorMessage='Naming a worksheet as "'
+@WorksheetName+'"', @objErrorObject=@objWorkBook
IF @hr=0
EXEC @hr=sp_OASetProperty @objWorkSheet, 'name', @WorksheetName
SELECT @currentRow = 1
--so let's write out the column headings
SELECT @currentColumn = 1
WHILE (@currentColumn <= @Columns AND @hr=0)
BEGIN
IF @hr=0
SELECT @strErrorMessage='getting column heading '
+LTRIM(STR(@currentcolumn)) ,
@objErrorObject=@objQueryResults,
@Command='ColumnName('
+CONVERT(VARCHAR(3),@currentColumn)+')'
IF @hr=0 EXEC @hr=sp_OAGetProperty @objQueryResults,
@command, @ColumnName OUT
IF @hr=0
SELECT @strErrorMessage='assigning the column heading '+
+ LTRIM(STR(@currentColumn))
+ ' from the query string',
@objErrorObject=@objExcel,
@command='Cells('+LTRIM(STR(@currentRow)) +', '
+ LTRIM(STR(@CurrentColumn))+').value'
IF @hr=0
EXEC @hr=sp_OASetProperty @objExcel, @command, @ColumnName
SELECT @currentColumn = @currentColumn + 1
END
--format the headings in Bold nicely
IF @hr=0
SELECT @strErrorMessage='formatting the column headings in bold ',
@objErrorObject=@objWorkSheet,
@command='Range("A1:'
+SUBSTRING(@alphabet,@currentColumn/26,1)
+SUBSTRING(@alphabet,@currentColumn % 26,1)
+'1'+'").font.bold'
IF @hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 1
--now we write out the data
SELECT @currentRow = 2
WHILE (@currentRow <= @Rows+1 AND @hr=0)
BEGIN
SELECT @currentColumn = 1
WHILE (@currentColumn <= @Columns AND @hr=0)
BEGIN
IF @hr=0
SELECT
@strErrorMessage=
'getting the value from the query string'
+ LTRIM(STR(@currentRow)) +','
+ LTRIM(STR(@currentRow))+')',
@objErrorObject=@objQueryResults,
@ResultSetRow=@CurrentRow-1
IF @hr=0
EXEC @hr=sp_OAMethod @objQueryResults, 'GetColumnString',
@value OUT, @ResultSetRow, @currentColumn
IF @hr=0
SELECT @strErrorMessage=
'assigning the value from the query string'
+ LTRIM(STR(@CurrentRow-1)) +', '
+ LTRIM(STR(@currentcolumn))+')' ,
@objErrorObject=@objExcel,
@command='Cells('+STR(@currentRow) +', '
+ STR(@CurrentColumn)+').value'
IF @hr=0
EXEC @hr=sp_OASetProperty @objExcel, @command, @value
SELECT @currentColumn = @currentColumn + 1
END
SELECT @currentRow = @currentRow + 1
END
--define the name range
--Cells(1, 1).Resize(10, 5).Name = "TheData"
IF @hr=0 SELECT @strErrorMessage='assigning a name to a range '
+ LTRIM(STR(@CurrentRow-1)) +', '
+ LTRIM(STR(@currentcolumn-1))+')' ,
@objErrorObject=@objExcel,
@command='Cells(1, 1).Resize('+STR(@currentRow-1) +', '
+ STR(@CurrentColumn-1)+').Name'
IF @hr=0 EXEC @hr=sp_OASetProperty @objExcel, @command, @RangeName
--Now autofilt the columns we've written to
IF @hr=0 SELECT @strErrorMessage='Auto-fit the columns ',
@objErrorObject=@objWorkSheet,
@command='Columns("A:'
+SUBSTRING(@alphabet,(@Columns / 26),1)
+SUBSTRING(@alphabet,(@Columns % 26),1)+
'").autofit'
IF @hr=0 --insert into @bucket(bucket)
EXEC @hr=sp_OAMethod @objWorkSheet, @command, @output out
IF @hr=0 SELECT @command ='del "' + @filename + '"'
IF @hr=0 EXECUTE master..xp_cmdshell @Command, no_output
IF @hr=0
SELECT @strErrorMessage='Saving the workbook as "'+@filename+'"',
@objErrorObject=@objRange,
@command = 'SaveAs("' + @filename + '")'
IF @hr=0 EXEC @hr=sp_OAMethod @objWorkBook, @command
IF @hr=0 SELECT @strErrorMessage='closing Excel ',
@objErrorObject=@objExcel
EXEC @hr=sp_OAMethod @objWorkBook, 'Close'
EXEC sp_OAMethod @objExcel, 'Close'
IF @hr<>0
BEGIN
DECLARE
@Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,
@Helpfile output,@HelpID output
SELECT @hr, @source, @Description,@Helpfile,@HelpID output
SELECT @strErrorMessage='Error whilst '
+COALESCE(@strErrorMessage,'doing something')
+', '+COALESCE(@Description,'')
RAISERROR (@strErrorMessage,16,1)
END
EXEC sp_OADestroy @objServer
EXEC sp_OADestroy @objQueryResults
EXEC sp_OADestroy @objCurrentResultSet
EXEC sp_OADestroy @objExcel
EXEC sp_OADestroy @objWorkBookks
EXEC sp_OADestroy @objWorkBook
EXEC sp_OADestroy @objRange
RETURN @hr
GO
--Now we can create our pubs spreadsheet, and can do it from any of
--our servers
--
spDMOExportToExcel @SourceServer='MyServer',
@SourceUID= 'MyUserID',
@SourcePWD = 'MyPassword',
@QueryText = 'use MyDatabase
select Pubname, Address, Postcode from ##CambridgePubs',
@filename = 'C:\MyPubDatabase.xls',
@WorksheetName='MyFavouritePubs',
@RangeName ='MyRangeName'
--or if you are using integrated security!
spDMOExportToExcel @SourceServer='MyServer',
@QueryText = 'use MyDatabase
select Pubname, Address, Postcode from ##CambridgePubs',
@filename = 'C:\MyPubDatabase.xls',
@WorksheetName='MyFavouritePubs',
@RangeName ='MyRangeName'
/* Although this is a very handy stored procedure, you'll probably need
to modify and add to it for particular purposes.
We use the DMO method because we like to dump build data into Excel
spreadsheets e.g. users, logins, Job Histories. However, an ADODB
version is very simple to do and can be made much faster for reads and
writes.
We have just inserted values, but you can insert formulae and formatting
(numberformat) and create or change borders. You can, in fact, manipulate
the spreadsheet in any way you like. When we do this, we record macros in Excel
and then convert these macros to TSQL! Using the above example, it should
be simple*/
Using Information_Schema to get Identity/PK/NonPK/All columns, FK dependencies and concatenate columns
----------------------------------------------------------
-- Grab list of tables
----------------------------------------------------------
if object_id( 'tempdb..#tmpTables' ) is not null drop table #tmpTables
create table #tmpTables
(
ordinal int identity(1,1),
table_catalog sysname,
table_schema sysname,
table_name sysname,
Sequence int default(1),
SequenceNbr int NULL,
HasIdentityCol bit default(0)
)
insert into #tmpTables ( table_catalog, table_schema, table_name )
select
t.table_catalog,
t.table_schema,
t.table_name
from information_schema.tables t (nolock)
----------------------------------------------------------
-- Load temp tables with table/column/constraint data from information_schema views
----------------------------------------------------------
if object_id( 'tempdb..#tmpColumns' ) is not null drop table #tmpColumns
select i.* into #tmpColumns from information_schema.columns i join #tmpTables t on i.table_catalog = t.table_catalog and i.table_schema = t.table_schema and i.table_name = t.table_name
create clustered index ix_tmpColumns_csn on #tmpColumns ( table_catalog, table_schema, table_name, column_name )
if object_id( 'tempdb..#tmpTblConstraints' ) is not null drop table #tmpTblConstraints
select i.* into #tmpTblConstraints from information_schema.table_constraints i join #tmpTables t on i.table_catalog = t.table_catalog and i.table_schema = t.table_schema and i.table_name = t.table_name
create clustered index ix_tmpTblConstraints_csn on #tmpTblConstraints ( table_catalog, table_schema, table_name )
create index ix_tmpTblConstraints_c on #tmpTblConstraints ( constraint_catalog, constraint_schema, constraint_name )
if object_id( 'tempdb..#tmpKeyColUsage' ) is not null drop table #tmpKeyColUsage
select i.* into #tmpKeyColUsage from information_schema.key_column_usage i join #tmpTables t on i.table_catalog = t.table_catalog and i.table_schema = t.table_schema and i.table_name = t.table_name
create clustered index ix_tmpKeyColUsage_csn on #tmpKeyColUsage ( table_catalog, table_schema, table_name, column_name )
if object_id( 'tempdb..#tmpRefConstraints' ) is not null drop table #tmpRefConstraints
select i.* into #tmpRefConstraints
from information_schema.referential_constraints i
join #tmpTblConstraints c
on ( i.constraint_catalog = c.constraint_catalog
and i.constraint_schema = c.constraint_schema
and i.constraint_name = c.constraint_name )
or ( i.unique_constraint_catalog = c.constraint_catalog
and i.unique_constraint_schema = c.constraint_schema
and i.unique_constraint_name = c.constraint_name )
create clustered index ix_tmpRefConstraints_c on #tmpRefConstraints ( unique_constraint_catalog, unique_constraint_schema, unique_constraint_name, constraint_catalog, constraint_schema, constraint_name )
----------------------------------------------------------
-- Check for the existance of LastUpdatedBy, UpdateDate and Identity columns
----------------------------------------------------------
update #tmpTables
set
HasIdentityCol = case when i.column_name is not null then 1 else 0 end
from #tmpTables t (nolock)
left join #tmpColumns i (nolock)
on t.table_catalog = i.table_catalog
and t.table_schema = i.table_schema
and t.table_name = i.table_name
and columnproperty( object_id( i.table_name ), i.column_name, 'IsIdentity' ) = 1
----------------------------------------------------------
-- Update load sequence based on dependencies
----------------------------------------------------------
while( @@rowcount > 0 )
update #tmpTables
set Sequence = s.Sequence + 1
from #tmpTables fkt
join (
select
table_catalog = fk.table_catalog,
table_schema = fk.table_schema,
table_name = fk.table_name,
Sequence = max( pkt.Sequence )
from #tmpTblConstraints fk (nolock)
join #tmpRefConstraints r (nolock)
on r.constraint_catalog = fk.constraint_catalog
and r.constraint_schema = fk.constraint_schema
and r.constraint_name = fk.constraint_name
join #tmpTblConstraints pk (nolock)
on r.unique_constraint_catalog = pk.constraint_catalog
and r.unique_constraint_schema = pk.constraint_schema
and r.unique_constraint_name = pk.constraint_name
and pk.constraint_type = 'primary key'
join #tmpTables pkt
on pk.table_catalog = pkt.table_catalog
and pk.table_schema = pkt.table_schema
and pk.table_name = pkt.table_name
where fk.constraint_type = 'foreign key'
and not
( fk.table_catalog = pk.table_catalog
and fk.table_schema = pk.table_schema
and fk.table_name = pk.table_name
)
group by fk.table_catalog, fk.table_schema, fk.table_name
) s
on fkt.table_catalog = s.table_catalog
and fkt.table_schema = s.table_schema
and fkt.table_name = s.table_name
and fkt.Sequence = s.Sequence
update #tmpTables
set SequenceNbr = s.SequenceNbr
from #tmpTables t
join (
select
table_catalog,
table_schema,
table_name,
SequenceNbr = rank() over( order by Sequence, table_catalog, table_schema, table_name )
from #tmpTables
) s on t.table_name = s.table_name
----------------------------------------------------------
-- Load temp tables with concattenated PK column data
----------------------------------------------------------
if object_id( 'tempdb..#tmpPKCols' ) is not null drop table #tmpPKCols
;with
pkcols
as
(
select
c.table_catalog, c.table_schema, c.table_name, c.column_name,
ordinal = rank() over ( partition by c.table_name order by c.table_name, c.ordinal_position ),
reverse_ordinal = rank() over ( partition by c.table_name order by c.table_name, c.ordinal_position desc )
from #tmpColumns c (nolock)
join #tmpKeyColUsage kc (nolock)
on c.table_catalog = kc.table_catalog
and c.table_schema = kc.table_schema
and c.table_name = kc.table_name
and c.column_name = kc.column_name
join #tmpTblConstraints tc (nolock)
on kc.table_catalog = tc.table_catalog
and kc.table_schema = tc.table_schema
and kc.table_name = tc.table_name
and kc.constraint_name = tc.constraint_name
join #tmpTables t (nolock)
on tc.table_catalog = t.table_catalog
and tc.table_schema = t.table_schema
and tc.table_name = t.table_name
where tc.constraint_type = 'primary key'
)
select * into #tmpPKCols from pkcols order by ordinal
if object_id( 'tempdb..#tmpConcatPKCols' ) is not null drop table #tmpConcatPKCols
;with concatpkcols
as
(
select
c.table_catalog, c.table_schema, c.table_name, c.column_name, c.ordinal, c.reverse_ordinal,
JoinOnPKCols = convert( varchar(max), '{source_alias_1}' + c.column_name + ' = {source_alias_2}' + c.column_name ),
ConcatPKCols = convert( varchar(max), 'coalesce( convert( varchar, ' + c.column_name + ' ), '''''''' )' ),
AllPKCols = convert( varchar(max), '{source_alias}' + c.column_name )
from #tmpPKCols c
where c.ordinal = 1
union all
select
c.table_catalog, c.table_schema, c.table_name, c.column_name, c.ordinal, c.reverse_ordinal,
JoinOnPKCols = convert( varchar(max), cpc.JoinOnPKCols + '{wrap}{source_alias_1}' + c.column_name + ' = {source_alias_2}' + c.column_name ),
ConcatPKCols = convert( varchar(max), cpc.ConcatPKCols + '{wrap}+ char(1) + coalesce( convert( varchar, ' + c.column_name + ' ), '''''''' )' ),
AllPKCols = convert( varchar(max), cpc.AllPKCols + '{wrap}{source_alias}' + c.column_name )
from #tmpPKCols c
join concatpkcols cpc
on c.table_catalog = cpc.table_catalog
and c.table_schema = cpc.table_schema
and c.table_name = cpc.table_name
and c.ordinal = cpc.ordinal + 1
)
select * into #tmpConcatPKCols from concatpkcols where reverse_ordinal = 1
----------------------------------------------------------
-- Load temp tables with concattenated Non-PK column data
----------------------------------------------------------
if object_id( 'tempdb..#tmpNonPKCols' ) is not null drop table #tmpNonPKCols
;with nonpkcols
as
(
select
c.table_catalog, c.table_schema, c.table_name, c.column_name,
ordinal = rank() over ( partition by c.table_name order by c.table_name, c.ordinal_position ),
reverse_ordinal = rank() over ( partition by c.table_name order by c.table_name, c.ordinal_position desc )
from #tmpColumns c (nolock)
join #tmpTables t (nolock)
on c.table_catalog = t.table_catalog
and c.table_schema = t.table_schema
and c.table_name = t.table_name
left join #tmpPKCols pk
on c.table_catalog = pk.table_catalog
and c.table_schema = pk.table_schema
and c.table_name = pk.table_name
and c.column_name = pk.column_name
where pk.column_name is null and c.data_type not in ( 'text', 'image' )
)
select * into #tmpNonPKCols from nonpkcols order by ordinal
if object_id( 'tempdb..#tmpConcatNonPKCols' ) is not null drop table #tmpConcatNonPKCols
;with concatnonpkcols
as
(
select
c.table_catalog, c.table_schema, c.table_name, c.column_name, c.ordinal, c.reverse_ordinal,
DiffOnNonPKCols = convert( varchar(max), 'where nullif( {source_alias_1}' + c.column_name + ', {source_alias_2}' + c.column_name + ' ) is not null or nullif( {source_alias_2}' + c.column_name + ', {source_alias_1}' + c.column_name + ' ) is not null' ),
UpdateNonPKCols = convert( varchar(max), c.column_name + ' = a.' + c.column_name ),
JoinOnNonPKCols = convert( varchar(max), '{source_alias_1}' + c.column_name + ' = {source_alias_2}' + c.column_name ),
ConcatNonPKCols = convert( varchar(max), 'coalesce( convert( varchar, ' + c.column_name + ' ) , '''''''' )' ),
AllNonPKCols = convert( varchar(max), '{source_alias}' + c.column_name )
from #tmpNonPKCols c
where c.ordinal = 1
union all
select
c.table_catalog, c.table_schema, c.table_name, c.column_name, c.ordinal, c.reverse_ordinal,
DiffOnNonPKCols = convert( varchar(max), cnpc.DiffOnNonPKCols + '{wrap} or nullif( {source_alias_1}' + c.column_name + ', {source_alias_2}' + c.column_name + ' ) is not null or nullif( {source_alias_2}' + c.column_name + ', {source_alias_1}' + c.column_name + ' ) is not null' ),
UpdateNonPKCols = convert( varchar(max), cnpc.UpdateNonPKCols + '{wrap}' + c.column_name + ' = a.' + c.column_name ),
JoinOnNonPKCols = convert( varchar(max), cnpc.JoinOnNonPKCols + '{wrap}{source_alias_1}' + c.column_name + ' = {source_alias_2}' + c.column_name ),
ConcatNonPKCols = convert( varchar(max), cnpc.ConcatNonPKCols + '{wrap}+ char(1) + coalesce( convert( varchar, ' + c.column_name + ' ), '''''''' )' ),
AllNonPKCols = convert( varchar(max), cnpc.AllNonPKCols + '{wrap}{source_alias}' + c.column_name )
from #tmpNonPKCols c
join concatnonpkcols cnpc
on c.table_catalog = cnpc.table_catalog
and c.table_schema = cnpc.table_schema
and c.table_name = cnpc.table_name
and c.ordinal = cnpc.ordinal + 1
)
select * into #tmpConcatNonPKCols from concatnonpkcols where reverse_ordinal = 1
----------------------------------------------------------
-- Load temp tables with concattenated All column data
----------------------------------------------------------
if object_id( 'tempdb..#tmpAllCols' ) is not null drop table #tmpAllCols
;with allcols
as
(
select
c.table_catalog, c.table_schema, c.table_name, c.column_name,
ordinal = rank() over ( partition by c.table_name order by c.table_name, c.ordinal_position ),
reverse_ordinal = rank() over ( partition by c.table_name order by c.table_name, c.ordinal_position desc )
from #tmpColumns c (nolock)
join #tmpTables t (nolock)
on c.table_catalog = t.table_catalog
and c.table_schema = t.table_schema
and c.table_name = t.table_name
)
select * into #tmpAllCols from allcols
if object_id( 'tempdb..#tmpConcatAllCols' ) is not null drop table #tmpConcatAllCols
;with concatallcols
as
(
select
c.table_catalog, c.table_schema, c.table_name, c.column_name, c.ordinal, c.reverse_ordinal,
AllCols = convert( varchar(max), '{source_alias}' + c.column_name )
from #tmpAllCols c
where c.ordinal = 1
union all
select
c.table_catalog, c.table_schema, c.table_name, c.column_name, c.ordinal, c.reverse_ordinal,
AllCols = convert( varchar(max), cac.AllCols + '{wrap}{source_alias}' + c.column_name )
from #tmpAllCols c
join concatallcols cac
on c.table_catalog = cac.table_catalog
and c.table_schema = cac.table_schema
and c.table_name = cac.table_name
and c.ordinal = cac.ordinal + 1
)
select * into #tmpConcatAllCols from concatallcols where reverse_ordinal = 1
select * from #tmpTables
select * from #tmpColumns
select * from #tmpTblConstraints
select * from #tmpKeyColUsage
select * from #tmpRefConstraints
select * from #tmpPKCols
select * from #tmpConcatPKCols
select * from #tmpNonPKCols
select * from #tmpConcatNonPKCols
select * from #tmpAllCols
select * from #tmpConcatAllCols
On this site you will find all kinds of (administrative) scripting related information:
- several scripting languages: batch files, JScript, KiXtart, Perl, PowerShell, Rexx, VBScript ...
- some language reference material
- regular expressions
- lots of sample scripts
- scripting techniques and best practices
- undocumented features, tricks and work-arounds
- links to more scripting info
- commands for unattended (silent) installations
- downloadable tools and utilities
- titles of relevant books on scripting
- and more...
CREATE TRIGGER tr_SalesHistory ON SalesHistory
FOR UPDATE
AS
BEGIN
DECLARE @FldsUpdated XML, @ColumnsUpdated VARBINARY(100)
SET @ColumnsUpdated = COLUMNS_UPDATED()
SET @FldsUpdated =
(
SELECT COLUMN_NAME AS Name
FROM TRS.INFORMATION_SCHEMA.COLUMNS Field
WHERE TABLE_NAME = 'SalesHistory'
AND sys.fn_IsBitSetInBitmask
(
@ColumnsUpdated,
COLUMNPROPERTY
(
OBJECT_ID( TABLE_SCHEMA + '.' + TABLE_NAME ),
COLUMN_NAME,
'ColumnID'
)
) <> 0
FOR XML AUTO, ROOT('Fields')
)
INSERT INTO SalesHistoryAudit
(
SaleID,
Product,
SaleDate,
SalePrice,
ColumnsUpdated
)
SELECT
SaleID,
Product,
SaleDate,
SalePrice,
@FldsUpdated
FROM INSERTED
END
GO
Offer you free and easy to use online file converter that supports a wide range of file types. It can convert almost any type of documents, archives, spreadsheets, audio and video files from one format to another. This service is absolutely free, and there's no need to download any software!
SUPPORTED FILE FORMATS
- RAR to TAR, ZIP, TGZ, TAR.GZ
- TAR to RAR, ZIP, TGZ, TAR.GZ
- TGZ to TAR, RAR, ZIP
- TAR.GZ to TAR, RAR, ZIP
- ZIP to TAR, RAR, TGZ, TAR.GZ
- DOCX to DOC, ODT, RTF, SWX, TXT, HTML, XHTML, PDF, PDB, ZIP
- DOC to ODT, RTF, SWX, TXT, HTML, XHTML, PDF, PDB, ZIP
- ODT to DOC, RTF, SWX, TXT, HTML, XHTML, PDF, PDB, ZIP
- RTF to ODT, DOC, SWX, TXT, HTML, XHTML, PDF, PDB, ZIP
- SXW to ODT, RTF, DOC, TXT, HTML, XHTML, PDF, PDB, ZIP
- TXT to ODT, RTF, SWX, DOC, HTML, XHTML, PDF, PDB, ZIP
- ODS to xls, CSV, RTF, PDF, HTML, ZIP
- XLS to ODS, CSV, PDF, HTML, ZIP
- XLSX to XLS, ODS, CSV, PDF, HTML, ZIP
- ODP to PPT, PDF, SWF
- PPT to ODP, PDF, SWF
- BMP to GIF, JPG, PNG, TIF, ZIP, PDF
- GIF to BMP, JPG, PNG, TIF, PDF
- JPG to GIF, BMP, PNG, TIF, PDF
- PNG to GIF, JPG, BMP, TIF, PDF
- TIF to GIF, JPG, PNG, BMP, ZIP, PDF
- AAC to WAV, MP3, OGG, M4A, FLAC, AU, WMA, AMR
- AMR to WAV, MP3, OGG, WMA, AAC, FLAC, AU, M4A
- AU to WAV, MP3, OGG, WMA, AAC, FLAC, AMR, M4A
- FLAC to WAV, MP3, OGG, M4A, AAC, AU, WMA, AMR
- M4A to WAV, MP3, OGG, WMA, AAC, FLAC, AU, AMR
- MP3 to WAV, OGG, AAC, M4A, FLAC, AU, WMA, AMR
- OGG to WAV, MP3, AAC, M4A, FLAC, AU, WMA, AMR
- WAV to MP3, OGG, AAC, M4A, FLAC, AU, WMA, AMR
- WMA to WAV, MP3, OGG, M4A, AAC, FLAC, AU, AMR
- MKA to WAV, MP3, OGG, M4A, AAC, FLAC, AU, AMR, WMA
- 3GP to AVI, MOV, WMV, M4V
- AMV to 3GP, FLV, MP4, MPEG, AVI, VOB, MOV, MKV, ASF, M4V, WMV
- ASF to 3GP, FLV, MP4, MPEG, AVI, VOB, WMV, MOV, AVI, M4V
- AVI to 3GP, FLV, MP4, MPEG, VOB, WMV, MOV, MKV, ASF, M4V
- FLV to 3GP, AVI, MP4, MPEG, VOB, WMV, MOV, MKV, ASF, M4V
- MKV to 3GP, FLV, MP4, MPEG, AVI, VOB, WMV, MOV, ASF, M4V
- MOV to 3GP, FLV, MP4, MPEG, AVI, VOB, WMV, MKV, ASF, M4V
- M4V to 3GP, FLV, MP4, MPEG, AVI, VOB, MOV, MKV, ASF, WMV
- MP4 to FLV, 3GP, AVI, MPEG, VOB, WMV, MOV, MKV, ASF, M4V
- MPEG to AVI, 3GP, MP4, FLV, VOB, WMV, MOV, MKV, ASF, M4V
- MPG to AVI, 3GP, MP4, FLV, VOB, WMV, MOV, MKV, ASF, M4V
- VOB to 3GP, FLV, MP4, MPEG, AVI, WMV, MOV, MKV, ASF, M4V
- WMV to 3GP, FLV, MP4, MPEG, AVI, VOB, MOV, MKV, ASF, M4V
- EPS to GIF, JPG, PNG
- PSD to GIF, JPG, PNG
- ODG to PDF, JPG, SWF, PNG
Dear Human,
By now you know what it's like to be a Zombie. To be hunted. To be hated. You fought to survive in a world that didn't make any sense.But now you're getting used to it. Your senses have sharpened. You finally begin to understand.
W...
create function dbo.ufsFormat
(
@Date datetime,
@Format varchar(80)
)
returns nvarchar(80)
/*
RFC822 RFC822
Mmmths as 1–12 M
Mmmths as 01–12 Mm
Mmmths as Jan–Dec Mmm
Mmmths as January–December Mmmm
Mmmths as the first letter of the Mmmth Mmmmm
Days as 1–31 D
Days as 01–31 Dd
Days as Sun–Sat Ddd
Days as Sunday–Saturday Dddd
Years as 00–99 Yy
Years as 1900–9999 Yyyy
=
To display Use this code
Hours as 0–23 H
Hours as 00–23 Hh
Minutes as 0–59 M
Minutes as 00–59 Mm
Seconds as 0–59 S
Seconds as 00–59 Ss
Hours as 4 AM h AM/PM
Time as 4:36 PM h:mm AM/PM
Time as 4:36:03 P h:mm:ss A/P
Quarter of the year Qq
Day of the year Dy
Week of the year Ww
AM and PM If the format contains an AM or PM,
the hour is based on the 12-hour clock,
where \\"AM\\" or \\"PM\\"
Test Suite:
set nocount on
--just check that the date comes out correctly in different languages
declare @ii int, @iiMax int, @Name nvarchar(40)
declare @Languages table ( MyID int identity(1,1), Name nvarchar(40) )
insert into @Languages( Name ) select name from master..syslanguages
select @ii = min( MyID ), @iiMax = max( MyID ) from @Languages
while @ii <= @iiMax
begin
select @Name = name from @Languages where MyID = @ii
set language @Name
select @Name, dbo.ufsFormat( getDate(),'dddd dd mmmm yyyy hh:mm:ss:ms am/pm' )
select @ii = @ii + 1
end
set language us_english
select convert( varchar(50), null ) as Format, convert( varchar(50), null ) as Date where 1 = 0 union all
select 'mm/dd/yy', dbo.ufsFormat( getDate(), 'mm/dd/yy' ) union all
select 'mm/dd/yyyy', dbo.ufsFormat( getDate(), 'mm/dd/yyyy' ) union all
select 'yy.mm.dd', dbo.ufsFormat( getDate(), 'yy.mm.dd' ) union all
select 'dd/mm/yy', dbo.ufsFormat( getDate(), 'dd/mm/yy' ) union all
select 'dd.mm.yy', dbo.ufsFormat( getDate(), 'dd.mm.yy' ) union all
select 'dd-mm-yy', dbo.ufsFormat( getDate(), 'dd-mm-yy' ) union all
select 'dd Mmm yy', dbo.ufsFormat( getDate(), 'dd Mmm yy' ) union all
select 'Mmm dd, yy', dbo.ufsFormat( getDate(), 'Mmm dd, yy' ) union all
select 'yyyy.mm.dd', dbo.ufsFormat( getDate(), 'yyyy.mm.dd' ) union all
select 'hh:mm:ss', dbo.ufsFormat( getDate(), 'hh:mm:ss' ) union all
select 'dd/mm/yyyy', dbo.ufsFormat( getDate(), 'dd/mm/yyyy' ) union all
select 'dd.mm.yyyy', dbo.ufsFormat( getDate(), 'dd.mm.yyyy' ) union all
select 'dd-mm-yyyy', dbo.ufsFormat( getDate(), 'dd-mm-yyyy' ) union all
select 'dd Mmm yyyy', dbo.ufsFormat( getDate(), 'dd Mmm yyyy' ) union all
select 'Mmm dd, yyyy', dbo.ufsFormat( getDate(), 'Mmm dd, yyyy' ) union all
select 'Mmmm dd yyyy hh:mm:ss:ms AM/PM', dbo.ufsFormat( getDate(), 'Mmmm dd yyyy hh:mm:ss:ms AM/PM' ) union all
select 'Mmm dd yyyy hh:mi:ss:mmm AM/PM', dbo.ufsFormat( getDate(), 'Mmm dd yyyy hh:mi:ss:mmm AM/PM' ) union all
select 'Mmm dd yy hh:mm:ss:ms AM/PM', dbo.ufsFormat( getDate(), 'Mmm dd yy hh:mm:ss:ms AM/PM' ) union all
select 'mm-dd-yy', dbo.ufsFormat( getDate(), 'mm-dd-yy' ) union all
select 'mm-dd-yyyy', dbo.ufsFormat( getDate(), 'mm-dd-yyyy' ) union all
select 'yy/mm/dd', dbo.ufsFormat( getDate(), 'yy/mm/dd' ) union all
select 'yyyy/mm/dd', dbo.ufsFormat( getDate(), 'yyyy/mm/dd' ) union all
select 'yymmdd', dbo.ufsFormat( getDate(), 'yymmdd' ) union all
select 'yyyymmdd', dbo.ufsFormat( getDate(), 'yyyymmdd' ) union all
select 'dd Mmm yy hh:mm:ss:Ms', dbo.ufsFormat( getDate(), 'dd Mmm yy hh:mm:ss:Ms' ) union all
select 'dd Mmm yyyy hh:mm:ss:Ms', dbo.ufsFormat( getDate(), 'dd Mmm yyyy hh:mm:ss:Ms' ) union all
select 'hh:mm:ss:Ms', dbo.ufsFormat( getDate(), 'hh:mm:ss:Ms' ) union all
select 'yyyy-mm-dd hh:mm:ss', dbo.ufsFormat( getDate(), 'yyyy-mm-dd hh:mm:ss' ) union all
select 'yyyy-mm-dd hh:mm:ss.Ms', dbo.ufsFormat( getDate(), 'yyyy-mm-dd hh:mm:ss.Ms' ) union all
select 'yyyy-mm-ddThh:mm:ss.Ms', dbo.ufsFormat( getDate(), 'yyyy-mm-ddThh:mm:ss.Ms' ) union all
select 'dd Mmm yyyy hh:mm:ss:ms AM/PM', dbo.ufsFormat( getDate(), 'dd Mmm yyyy hh:mm:ss:ms AM/PM' ) union all
select 'dd/mm/yy hh:mm:ss:ms AM/PM', dbo.ufsFormat( getDate(), 'dd/mm/yy hh:mm:ss:ms AM/PM' ) union all
select 'RFC822', dbo.ufsFormat( getDate(), 'RFC822' ) union all
select 't\he dy \da\y, t\he ww week of t\he year', dbo.ufsFormat( getDate(), 't\he dy \da\y, t\he ww week of t\he year' ) union all
select 'dddd dd mmmm yyyy h am/pm', dbo.ufsFormat( getDate(), 'dddd dd mmmm yyyy h am/pm' ) union all
select 'dddd dd mmmm yyyy h:m:s am/pm', dbo.ufsFormat( getDate(), 'dddd dd mmmm yyyy h:m:s am/pm' ) union all
select 'dddd dd mmmm yyyy hh:mm:ss:ms am/pm', dbo.ufsFormat( getDate(), 'dddd dd mmmm yyyy hh:mm:ss:ms am/pm' ) union all
select 'dddd dd mmmm yyyy h:m:s:ms am/pm', dbo.ufsFormat( getDate(), 'dddd dd mmmm yyyy h:m:s:ms am/pm' ) union all
select 'dd mmmmm yyyy', dbo.ufsFormat( getDate(), 'dd mmmmm yyyy' )
select 'yyyy.mm.dd.hh.mm.ss.ms', dbo.ufsFormat( getDate(), 'yyyy.mm.dd.hh.mm.ss.ms' )
*/
as
begin
declare @Dateformat int, @ReturnedDate varchar(80), @TwelveHourClock int, @Before int, @pos int, @Escape int
select
@ReturnedDate = 'error! unrecognised format ' + @Format,
@DateFormat =
case @Format
when 'mmm dd yyyy hh:mm AM/PM' then 100
when 'mm/dd/yy' then 1
when 'mm/dd/yyyy' then 101
when 'yy.mm.dd' then 2
when 'dd/mm/yy' then 3
when 'dd.mm.yy' then 4
when 'dd-mm-yy' then 5
when 'dd Mmm yy' then 6
when 'Mmm dd, yy' then 7
when 'hh:mm:ss' then 8
when 'yyyy.mm.dd' then 102
when 'dd/mm/yyyy' then 103
when 'dd.mm.yyyy' then 104
when 'dd-mm-yyyy' then 105
when 'dd Mmm yyyy' then 106
when 'Mmm dd, yyyy' then 107
when 'Mmm dd yyyy hh:mm:ss:ms AM/PM' then 9
when 'Mmm dd yyyy hh:mi:ss:mmm AM/PM' then 9
when 'Mmm dd yy hh:mm:ss:ms AM/PM' then 109
when 'mm-dd-yy' then 10
when 'mm-dd-yyyy' then 110
when 'yy/mm/dd' then 11
when 'yyyy/mm/dd' then 111
when 'yymmdd' then 12
when 'yyyymmdd' then 112
when 'dd Mmm yyyy hh:mm:ss:Ms' then 113
when 'hh:mm:ss:Ms' then 14
when 'yyyy-mm-dd hh:mm:ss' then 120
when 'yyyy-mm-dd hh:mm:ss.Ms' then 121
when 'yyyy-mm-ddThh:mm:ss.Ms' then 126
when 'dd Mmm yyyy hh:mm:ss:ms AM/PM' then 130
when 'dd/mm/yy hh:mm:ss:ms AM/PM' then 131
when 'RFC822' then -2
when 'dd Mmm yyyy hh:mm' then -4
else -1
end
select @ReturnedDate = 'error! unrecognised format ' + @Format + convert( varchar, @DateFormat )
-- standard formats
if @DateFormat >= 0
return convert( varchar, @Date, @DateFormat )
-- RFC822 format
if @DateFormat = -2
return left( datename( dw, @Date ), 3 ) + ', ' + stuff( convert( nvarchar, @Date, 113 ), 21, 4,' GMT' )
-- European day format with minutes
if @DateFormat = -4
return convert( char(17), @Date, 113 )
select @Before = len( @Format )
select @Format = replace( replace( replace( @Format,'AM/PM', '#' ), 'AM', '#' ), 'PM', '#' )
select @TwelveHourClock =
case
when @Before > len( @Format )
then 109
else 113
end,
@ReturnedDate = ''
while (1=1)
begin
select @pos = patindex( '%[yqmidwhs:#]%', @Format + ' ' )
if @pos = 0 -- no more date format strings
begin
select @ReturnedDate = @ReturnedDate + @Format
break
end
if @pos > 1 -- some stuff to pass through first
begin
select @escape = charindex( '\', @Format + '\' )
--is it a literal character that is escaped?
if @escape < @pos
begin
select @ReturnedDate = @ReturnedDate + substring( @Format, 1, @escape - 1 ) + substring( @Format, @escape + 1, 1 )
select @Format = rtrim( substring( @Format, @Escape + 2, 80 ) )
continue
end
select @ReturnedDate = @ReturnedDate + substring( @Format, 1, @pos - 1 )
select @Format = rtrim( substring( @Format, @pos, 80 ) )
end
select @pos = patindex( '%[^yqmidwhs:#]%', @Format + ' ' ) --get the end
select @ReturnedDate = @ReturnedDate +
case substring( @Format, 1, @pos - 1 )
when 'M' then convert( varchar(2), datepart( month, @Date ) ) --Mmmths as 1–12
when 'Mm' then convert( char(2), @Date, 101) --Mmmths as 01–12
when 'Mmm' then convert( char(3), datename( month, @Date ) ) --Mmmths as Jan–Dec
when 'Mmmm' then datename( month, @Date ) --Mmmths as January–December
when 'Mmmmm' then convert( char(1), datename( month, @Date ) ) --Mmmths as the first letter of the Mmmth
when 'D' then convert( varchar(2), datepart( day, @Date ) ) --Days as 1–31
when 'Dd' then convert( char(2), @date, 103 ) --Days as 01–31
when 'Ddd' then convert( char(3), datename( weekday, @Date ) ) --Days as Sun–Sat
when 'Dddd' then datename( weekday, @Date ) --Days as Sunday–Saturday
when 'Yy' then convert( char(2), @Date, 12 ) --Years as 00–99
when 'Yyyy' then datename( year, @Date ) --Years as 1900–9999
when 'hh:mm:ss' then substring( convert( char(30), @date, @TwelveHourClock ), 13, 8 )
when 'hh:mm:ss:ms' then substring( convert( char(30), @date, @TwelveHourClock ), 13, 12 )
when 'h:mm:ss' then substring( convert( char(30), @date, @TwelveHourClock ), 13, 8 )
when 'hh:mi:ss:mmm' then substring( convert( char(30), @date, @TwelveHourClock ), 13, 12 ) --the SQL Server BOL syntax, for compatibility
when 'h:mm:ss:ms' then substring( convert( char(30), @date, @TwelveHourClock ), 13, 12 )
when 'H:m:s' then substring( replace( ':' + substring( convert( char(30), @Date, @TwelveHourClock ), 13, 8 ), ':0', ':' ), 2, 30 )
when 'H:m:s:ms' then substring( replace( ':' + substring( convert( char(30), @Date, @TwelveHourClock ), 13, 12 ), ':0', ':' ), 2, 30 )
when 'hh' then replace( substring( convert( char(30), @Date, @TwelveHourClock ), 13, 2 ), ' ', '0' ) --Hours as 00–23
when 'h' then ltrim( substring( convert( char(30), @Date, @TwelveHourClock ), 13, 2 ) ) --Hours as 0–23
when 'Mi' then datename( minute, @date ) --Minutes as 00–59
when 'mn' then datename( minute, @date )
when 'm' then convert( varchar(2), datepart( minute, @date ) )
when 'ss' then datename( second,@date) --Seconds as 0–59
when 'S' then convert( varchar(2), datepart( second, @date ) ) --Seconds as 0–59
when 'ms' then datename( millisecond, @date ) --AM/PM
when 'mmm' then datename( millisecond, @date )
when 'dy' then datename( dy, @date )
when 'qq' then datename( qq, @date )
when 'ww' then datename( ww, @date )
when '#' then reverse( substring( reverse( convert( char(26), @date, 109 ) ), 1, 2 ) )
else substring( @Format, 1, @pos - 1 )
end
select @Format = rtrim( substring( @Format, @pos, 80 ) )
end
return @ReturnedDate
end
- Convenient
- Now you can carry your favorite computer programs along with all of your bookmarks, settings, email and more with you. Use them on any Windows computer. All without leaving any personal data behind.
- Open
- PortableApps.com provides a truly open platform that works with any hardware you like (USB flash drive, iPod, portable hard drive, etc). It's open source built around an open format that any hardware vendor or software developer can use.
- Free
- The PortableApps.com Suite and Platform is free. It contains no spyware. There are no advertisements. It isn't a limited or trial version. There is no additional hardware or software to buy. You don't even have to give out your email address. It's 100% free to use, free to copy and free to share.
This new PoSh version of the script supports everything from the original script plus other features like allowing a restore to be automated/initiated from any machine (as opposed to having to be on the server itself), restoring from one instance to another instance, simply outputting a script of the restore statement(s) and/or execute the restore, removing the dependency on using things like xp_cmdshell, supports SQL 2005 and 2008, exclude differential and/or log backups if you like, and a few others as well. On the PoSh side, this script supports all the major considerations any good PoSh script should such as:
- Can be dot-sourced into a script
- Can be invoked from a script (i.e. &restore-sqldb)
- Fully supports pipeline processing for SMO Database objects and/or any object that can be string-expanded to a database name
- Friendly usage output (run the script with a single '-?' parameter)
- Debug and Verbose optional output
- I don't support a -whatIf directly, but you get this by basically excluding the -execute switch (you'll get a restore script output)
If you haven't read the original script post, I'd encourage you to take a look at it quickly (the text of the blog post, not necessarily the script) to give you an idea of what can be done with the script and hence this script - some of the functionality included allows for things like:
- Restore a database with nothing more for information than what database and what instance it resides on
- By default will pull restore information from the msdb database for the instance being restored from. This will basically query the appropriate backup meta-data tables for backup information on the database in question and build the restore statement(s) from that data appropriately including proper ordering, grouping of media sets/families, etc.
- Can specify '-paths' that support wildcards and can include 1 or more locations to backup files for the database in question - the backup files will be:
- Investigated for proper ordering of restore sequence
- Expanded (if they are backup set files containing multiple backups) appropriately
- Grouped correctly if part of a media family/set (i.e. if you use a backup statement with multiple output files)
- Can now restore from one instance to a totally different instance (obviously you need to be able to connect to each and have appropriate privileges to do so)
- Use the '-fromInstance' parameter to specify where to restore from
- Use the '-toInstance' parameter to specify where to restore to
- Can specify a new location to move log file(s) to during the restore - this will build the appropriate 'with move...' statement(s) into the restore script to move log files to the specified location(s)
- o You do not need to know anything about where the log file(s) already existed within the backup
- Can specify new location(s) to move data file(s) to during the restore - this will build the appropriate 'with move...' statement(s) into the restore script to move data files to the specified location(s)
- You do not necessarily need to include the same number of new locations as existing locations - if there are more data files than new locations, the script will simply round-robin the data files among the new locations
- You do not need to know anything about where the data file(s) already existing within the backup
- You can specify a '-stopAt' value that will mimic the 'STOPAT' statement within the restore
- You can choose to ignore differential backups and/or log backups - by default the script uses all possible backups, this provides some flexibility
- You can perform a page restore that will pull pages to be restored automatically from the msdb.dbo.suspect_pages table
- You can perform a restore of only specific files or filegroups - simply include the appropriate logical filenames and/or filegroup names in the '-files' and '-filegroups' parameters
- If you don't want to incur the overhead of a restore headeronly/filelist only operation and you write backups with a timestamp, you can specify the '-timeStampInFileNames' option and the script will shred each filename for a timestamp value that will act as the ordering/grouping values instead of performing a restore headeronly/filelistonly operation on each
- Can restore the database with a new name via the '-newDbName' parameter
- Support for liteSpeed syntax via the '-liteSpeed' switch
- Checksum support via the '-checksum' switch
- And much more...(just like on TV)
For those of you familiar with PoSh arguments, you realize you don't have to necessarily include the entire name of a script parameter, just enough of it so the PoSh engine can distinguish it from the other parameter names - this will allow you to short-hand things like the '-toInstance' parameter to just '-to', or the '-fromInstance' to just '-from', or the '-dbName' parameter to just '-db', or just '-lite' for liteSpeed vs. the full '-liteSpeed', or...well, you get the picture.
For detailed usage scenarios and some examples, just PoSh Restore-SqlDb.ps1 -?.
Enjoy!
CREATE FUNCTION dbo.UDF_ParseAlphaNumChars
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
END
SET @string = LTRIM(RTRIM(REPLACE(@string,' ','')))
RETURN @string
END
GO
—-Test
SELECT dbo.UDF_ParseAlphaNumChars('ABC”_I+{D[]}4|:e;””5,<.F>/?6')
GO
What is Woot and who's behind it?
Woot.com is an online store and community that focuses on selling cool stuff cheap. It started as an employee-store slash market-testing type of place for an electronics distributor, but it's taken on a life of its own. We anticipate profitability by 2043 – by then we should be retired; someone smarter might take over and jack up the prices. Until then, we're still the lovable scamps we've always been. But don't take our word for it: see what the online community has to say at this Wikipedia article.
Maddie substituted in on the Eastlake Bruins team for the Mid-Season Tournament. After trailing 0-6 going into the last inning of the Championship game agains the Kirkland Nationals, the Bruins came from behind to win 7-6.
Click here to see the article in the Sammamish Review. Article found on page 24.
Millions of songs. Find yours. Build your own playlists from other people's music. See my favorites in the player on the right-side of blog page.
Fast, secure and free downloads from the largest Open Source applications and software directory.
Welcome to isoHunt, the most comprehensive BitTorrent search engine. With cross-referenced trackers data you won't find anywhere else, all updated to the hour. This is your all-in-one P2P files search engine and community.
Some of my favorite channels include:
Don't forget to check-out Veronica's Blog too:
Do you know what the difference is between using SET and SELECT when assigning varaibles in T-SQL? Well, there are some differences. I came across a great article by Narayana Vyas Kondreddi from the UK that describes the difference between the two.
The article is well worth the read, but here are the main points:
- SET is the ANSI standard for variable assignment, SELECT is not.
- SET can only assign one variable at a time, SELECT can make multiple assignments at once.
- If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)
- When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from it's previous value)
- As far as speed differences - there are no direct differences between SET and SELECT. However SELECT's ability to make multiple assignments in one shot does give it a slight speed advantage over SET.
Take a look at the complete article to see Vyas' complete tests to get the entire picture.
The "mercy rule" kicked in after 5 innings tonight. Maddie hit a double early on and triple in the top of the 5th, but the big news was the 10-0 shut-out she pitched. She had them chasing balls all around the strike zone and finding nothing but air. Lots of great defensive plays by the whole team: squeeze plays, catcher throwing clear to 2nd base to pick-off runners trying to steal and many others. It was definitely their best game of the year.
Pronounced
[
ree
-
uh
l-
tahym
,
reel
-
]
–noun
Fishing
.
| of or pertaining to the period in which a fisherman must leave to go fishing. |
–adjective
Computers
.
| of or pertaining to applications in which the computer must respond as rapidly as required by the user or necessitated by the process being controlled. |