November 27, 2009 at 1:15 pm
I've got the following script:(i know it is pretty)
--THIS SCRIPT ONLY WORKS WITH TABLE NAMES 11 CHARACTERS LONG
-- Emails Record Counts for tables in TBdata
-- LP, 4/23/07
-- Added CSLSAMP1FIN, CSLSAMP1SEF, and CSLSAMP1RTL tables
-- JR, 6/11/07
-- NOTIFICATIONSCRIPTVERSION_AllTables.sql
-- added CSLREQDTSNA
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
DECLARE @TEXTBODY VARCHAR(8000)
DECLARE @TEXTSUBJECT VARCHAR(250)
DECLARE @START INT
DECLARE @END INT
DECLARE @TABLE VARCHAR(1000)
DECLARE @TABLENAMES VARCHAR(2000)
DECLARE @textTab char(4)
DECLARE @textNewline char(2)
DECLARE @COUNT INT
DECLARE @BUFFER INT
DECLARE @mailReturn int
USE TBDATA
SET @COUNT = 0
SET @TABLENAMES = 'APLHIST1SNA,APLHIST2SNA,APLHIST3SNA,APLVEND1SNA,ARLCUST1CSI,ARLCUST1SNA,ARLCUST5SNA,ARLCUST9SNA,ARLHIST3SNA,ARLOPEN1RTL,ARLOPEN1SNA,ARLSREP1SNA,ARLTERM1SNA,CSLCONSNSNA,CSLLABR1CSI,CSLPROS1CSI,CSLREQDTSNA,CSLSAMP1CSI,CSLSAMP1FIN,CSLSAMP1SEF,CSLSAMP1RTL,CSLSHPTBCSI,CSLSHPTCCSI,CSLYARN1SNA,GALAROP1SNA,GALARRP1SNA,GILITEM1SNA,GILPBRK1SNA,GILPROD1SNA,GILYARN1SNA,GLLACCT1RTL,GLLACCT1SNA,GLLDETL1RTL,GLLDETL1SNA,GOLCONT1SNA,GOLHIST1SNA,GOLHIST2SNA,GOLHISTGSNA,GOLLUSSQSNA,GOLORDR1SNA,GOLORDR2SNA,GOLORDRGSNA,ICLITEM1ACU,ICLITEM1CSI,LTLCTWG6CSI,LTLLABR2CSI,LTLLABR2FIN,LTLLOTS1CSI,LTLMAST1CSI,LTLNDCD1CSI,LTLNDCR1CSI,LTLNDCR2CSI,LTLROLL1CSI,LTLSLIT1CSI,LTLSLIT1FIN,LTLTRAN1CSI,LTLTRAN1FIN,LTLRTDT1CSI,LTLRTDT1FIN,MFLRTDT1CSI,MFLRTDT1FIN,MFLRTHD1CSI,MFLRTHD1FIN,MFLWODT1CSI,MFLWOHD1CSI,OPLORDR1ACU,OPLORDR1CSI,OPLORDR2ACU,OPLORDR2CSI,S4LORDR3ACU,S4LORDR3CSI,SCLYEAR1SNA,STLCUEX1CSI,STLCUEX1SNA,STLCURR1CSI,STLCURR1SNA,STLMRKT1SNA,STLSSBU1SNA,CSLLUSSQSNA,S4LDCRC1SNA,MFLWODT1FIN,MFLWOHD1FIN,MFLCRUP1CSI,MFLITEM1CSI,OPLHIST1CSI,OPLHIST2CSI,S4LHIST3CSI,S4LORDR3FIN,S4LORDR3SEF,OPLORDR2FIN,OPLORDR2SEF,S4LORDR3FIN,S4LORDR3SEF,OPLORDR2RTL,S4LORDR3RTL'
SET @textTab = CHAR(9)
SET @textNewline = CHAR(13) + CHAR(10)
-- CREATE THE SUBJECT OF THE EMAIL
SET @TEXTSUBJECT = 'TBData TBred Record Counts'
--HEADER FOR TEXTBODY
SET @TEXTBODY ='TBDATA TBRED RECORD COUNTS' + @textNewline + 'Table Name' + @textTab + 'Row Count' + @textNewline
SET @START = 1
SET @END = 11
--Generate a record count for CURRENT TABLE
WHILE @COUNT < 95
BEGIN
CREATE TABLE #tCount (myCount INT)
SET @TABLE = SUBSTRING(@TABLENAMES,@START,@END)
EXECUTE( 'INSERT INTO #tCount SELECT COUNT(*) myCount FROM ' + @TABLE )
SELECT @BUFFER = myCount FROM #tCount
DROP TABLE #tCount
--GENERATE TEXTBODY
SET @TEXTBODY = @TEXTBODY + @TABLE + @textTab + cast(@BUFFER as varchar(8000)) + @textNewline
SET @START = @START + 12
SET @COUNT = @COUNT + 1
END
--SET THE RECIPENTS, GENERATE AND SEND OFF THE EMAIL
EXEC @mailReturn = master..xp_sendmail
@recipients = 'email@addressGoHere.com',
@message = @textBody,
@subject = @textSubject
I need to save the results to a file as i can't move the server to an new domain and the old exchange server doesn't exist anymore. Any help would be greatly appreciated.
November 27, 2009 at 2:10 pm
I have used the OPENROWSET command to export data from a 2000 DB to a CSV file using this code:
--OPENROWSET to Text file From DB table to text file.
--Text file must have a first line of comma separated field names
--Use Notepad to creat the text file, and save on the server.
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=D:\MSSQL\;HDR=Yes;', 'SELECT * FROM Test.txt')
SELECT * FROM your table/view name
D:\MSSQL\;HDR=Yes; is the path to the Text file.
If your code extracts the data to a tempory table, or to a view and then use the OPENROWSET function as shown above, or you could consider the BCP (Bulk Copy Program). The BCP T-SQL is explained in Books On Line.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply