Ok I have a created a stored procedure that gets a list of email addresses on
Thurdays and will write them to an EXCEL file, the logic decent and works
however when the statement that fetches the data is run I keep getting the
"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable these
options and then reissue your query."
I tried setting them in the query statement itself but it still fails, I tried
setting them before running the stored proc and that does not work either. I
even tried EXEC them alone before firing the main query. Anybody have any
suggestions?
CREATE PROCEDURE GetEhotline AS
DECLARE
@MONTH int,
@DAY int,
@YEAR int,
@LASTDAY datetime,
@CHECKDATE datetime,
@DOW int,
@LASTTHUR datetime,
@colList varchar(200),
@charInd int,
@tsqlStmt varchar(1000),
@NumOfColumns tinyint,
@query varchar(1000),
@dosStmt varchar(200),
@fileName varchar(200),
@filenameend varchar(8),
@tsqlStmtNULL varchar(30),
@tsqlStmtWARN varchar(30)
--SELECT @CHECKDATE = convert(varchar(10),getdate(),101)
SET @filenameend = CONVERT(varchar(10),getdate(),112)
SET @CHECKDATE = '1/20/2005' --TEST
SELECT @DOW = DATEPART(weekday,@CHECKDATE)
-- First check which day of the week it is. Thursday is the 5th day of the week
IF @DOW = 5
BEGIN
-- Next check to see if it is the last thursday of the month
SELECT @lastday = convert(varchar(10),(SELECT
dbo.LastMonthDay(@CHECKDATE)),101)
SELECT CONVERT(varchar(2),
DATEPART(m,@lastday))+'/'+CONVERT(varchar(2),mon_day)+'/'+
CONVERT(varchar(4),DATEPART(yyyy,@lastday)) AS dateofmonth
INTO #tmp_month_dates
FROM mon
WHERE mon_day <= DATEPART(d,@lastday);
SELECT @LASTTHUR= CONVERT(varchar(10),max(cast(dateofmonth as
datetime)),101)
FROM #tmp_month_dates
WHERE DATEPART(weekday,dateofmonth) =5
IF @LASTTHUR = @CHECKDATE
BEGIN
PRINT 'Today is The Last Thursday'
DROP TABLE #tmp_month_dates;
END
ELSE
BEGIN
--PRINT 'Today is Thursday but not the last Thurday of
the Month'
--- GET "HTML" SUBSCRIBERS
SET @NumOfColumns =1
SET @charInd=0
SET @colList = 'A'
WHILE @charInd < @NumOfColumns - 1
BEGIN
SET @charInd = @charInd + 1
SET @colList = @colList + ',' +
char(65 + @charInd)
END
-- Create an Empty Excel file as the target file name
by copying the template Empty excel File
SET @fileName = 'g:\temp\HTML_'[EMAIL
PROTECTED]'.xls'
SET @dosStmt = ' copy g:\temp\empty.xls ' +
@fileName
EXEC master..xp_cmdshell @dosStmt
-- Create a "temporary" linked server to that
file in order to "Export" Data
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
@fileName,
NULL,
'Excel 5.0'
-- Construct a T-SQL statement that will actually
export the query results to the Table in the target linked server
SET @query ='SELECT email
FROM thisDatabase.dbo.peopleroleup(nolock)
WHERE email like '+ '"[EMAIL PROTECTED]"' +
' AND dontshowonline = '+'"N"'+
' AND personid IN(
SELECT personid
FROM thisDatabase.dbo.people(nolock)
WHERE lookup001id = 2 --html
AND personid IN (
SELECT personid
FROM persondem
WHERE expdate >
getdate()
));'
SET @tsqlStmt ='Insert ExcelSource...[ExcelTable$] ' +
' ( ' + @colList + ' ) '+ @query
--PRINT @tsqlStmt
-- execute dynamically the TSQL statement
SET @tsqlStmtNULL ='SET ANSI_NULLS ON'
SET @tsqlStmtWARN = 'SET ANSI_WARNINGS ON'
-- THIS IS WHERE I AM HAVING THE PROBLEM TRYING TO SET THE ANSI_NULLS AND
WARNINGS
EXEC (@tsqlStmtNULL)
EXEC (@tsqlStmtWARN)
EXEC (@tsqlStmt)
-- drop the linked server
EXEC sp_dropserver 'ExcelSource'
DROP TABLE #tmp_month_dates;
END
END
ELSE
BEGIN
PRINT 'Today is not Thursday'
END
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware: a new and convenient web-based time tracking application. Start
tracking and documenting hours spent on a project or with a client with Logware
today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191570
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
