Site Sections
Posts
83
Comments
600
Trackbacks
40
June 2006 Entries
How do I create a file with a header and trailer?

...was recently asked at SQLTeam

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68461

This is what I would do

USE Northwind
GO

CREATE VIEW EXPORT_ORDERS
AS
SELECT 1 AS ROW_ORDER,
  'HEADER '
 + CONVERT(char(25),GetDate()) AS Data_Line
UNION ALL
SELECT 2 AS ROW_ORDER,
   COALESCE(CONVERT(char(15),OrderID),'')
 + COALESCE(CustomerID,'')
 + COALESCE(CONVERT(char(15),EmployeeID),'')
 + COALESCE(CONVERT(char(25),OrderDate),'')
 + COALESCE(CONVERT(char(25),RequiredDate),'')
 + COALESCE(CONVERT(char(25),ShippedDate),'')
 + COALESCE(CONVERT(char(15),ShipVia),'')
 + COALESCE(CONVERT(char(15),Freight),'')
 + COALESCE(CONVERT(char(80),ShipName),'')
 + COALESCE(CONVERT(char(120),ShipAddress),'')
 + COALESCE(CONVERT(char(30),ShipCity),'')
 + COALESCE(CONVERT(char(30),ShipRegion),'')
 + COALESCE(CONVERT(char(20),ShipPostalCode),'')
 + COALESCE(CONVERT(char(30),ShipCountry),'') AS Data_Line
  FROM Orders
UNION ALL
SELECT 3 AS ROW_ORDER,
  'TRAILER '
 + CONVERT(char(25),GetDate())
 + CONVERT(char(15),COUNT(*)) AS Data_Line
  FROM Orders
GO

SELECT Data_Line FROM EXPORT_ORDERS ORDER BY ROW_ORDER

EXEC master..xp_cmdshell 'bcp Northwind.dbo.EXPORT_ORDERS out C:\Orders.txt -S<servername> -c -T'
GO

DROP VIEW EXPORT_ORDERS
GO

posted @ Thursday, June 29, 2006 1:03 PM | Feedback (2)
OSQL Doesn't work like Query Analyzer ( Or Query Analyser for that matter...)

What?  Huh?  This can't be...can it?  Well this person seemed to think so:

http://www.dbforums.com/showthread.php?t=1219537

I didn't think that this was possible...and, well, it isn't...so I am posting this for future reference....and to start blogging all things SQL again

USE Northwind
GO

CREATE TABLE myTable99(Col1 int)
GO

INSERT INTO myTable99(Col1)
SELECT 1    UNION ALL
SELECT 2    UNION ALL
SELECT 3    UNION ALL
SELECT null UNION ALL
SELECT null UNION ALL
SELECT null
GO

CREATE PROC mySproc99 @Rows int OUTPUT AS
 SELECT @Rows = COUNT(*) FROM myTable99 WHERE Col1 IS NULL
GO

DECLARE @Rows int
EXEC mySproc99 @Rows OUTPUT
SELECT @Rows

DECLARE @cmd varchar(8000), @sql varchar(8000)
SELECT @sql = 'DECLARE @Rows int EXEC mySproc99 @Rows OUTPUT SELECT @Rows'
SELECT @cmd = 'osql -U sa -P <password>-S <servername> -d Northwind -Q "' + @sql + '"'
EXEC master..xp_cmdshell @cmd
GO

DROP PROC mySproc99
DROP TABLE myTable99
GO

posted @ Monday, June 26, 2006 12:57 PM | Feedback (6)
Sidekick? What the hell is a sidekick?

At first I thought it was a Suzuki...

http://www.edmunds.com/new/suzuki/index.html?mktcat=makemodel-suzuki&kw=suzuki+sidekick&mktid=ga471651

I was like, Damn they left their suzuki in a taxi...wait, let me read that again..So this is what it is

http://www.amazon.com/gp/product/B0000A0AZC/103-5355719-0187859?v=glance&n=301185

In my opinion, people are going too crazy with these things...don't they know they are becoming automoton slaves?

In any event Graz posted this link in the corral.  I'd figure I'd add to the mayhem

http://www.evanwashere.com/StolenSidekick/

Another voice of reason

http://impulse100.net/StolenSidekick/viewtopic.php?t=47

EDIT: 6/13/2006  Ok, who wouldn't have guessed where this was going

“WHAT THE HELL?    So she SOLD our Sidekick...  After we had contacted her.  So either that's why she needed the money, to buy it BACK, or she was faking all along and never intended to give it back.  And now she is going to SUE me after selling someone else's property? “

I should start a prognastication business.  I guessing he would like his anonymity back right about now.

EDIT: 6/13/2006 13:30:  OK, this even gets better.  The reason they were so frantic to get the phone back is because they were in communication with the Russian consulat trying to get her sister a visa so she could come to the united states so she could be the maid of honor.  As she is getting married THIS WEEKEND.  ?????  GIVE ME A BREAK.  They planned this within 1 week of the wedding.  This has all the truth of “I did not have sexual relations with that woman....“  Oh please...make the insanity stop.

EDIT: 6/14/2006:  This is great:

” I received a call in the middle of the night saying that the family was INDEED pursuing a harassment case against me.  Whether or not they have a case is irrelevant.  Anyone can sue someone now a days for anything.  So, I removed the contents of this website. I am protecting myself as ANY of you would do for yourself.   Now... I don't want you to see this as caving in or admitting guilt. “

Sure...maybe seeing in the error of your ways now...and followed by:

“I am leaving the forum link up for people to discuss.  Please remember that this is a public forum.  The owner of the forum, the moderators, and I are NOT responsible for content posted on it.  Please make sure to read the rules before you post.“

Well, if he's not responsible, then who is?  And the following is showing the realization of his actions:

Thank you for all your support guys.  I did this to wrong a right.  I hope I don't get crucified for trying to be moral in an immoral world.

An Immoral world?  Who is this guy?  Does he really think that the world is on his case?  The ONLY blog that I've seen speaking out against his actions is here.  The rest has been the world “backing“ his insane quest with mob rule justice.  I hope he get's taken to the cleaners.  Oh and BTW, I believe the phrase is “to Right a wrong“.  What a moron.

 

 

 

 

 

posted @ Thursday, June 08, 2006 4:18 PM | Feedback (13)
Article Categories
Archives
Post Categories
Image Galleries
Misc
SQL Server