Parameters in cursor error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Parameters in cursor error

Author

Topic

barry
Starting Member

14 Posts

Posted - 2006-07-12 : 17:12:04

CREATE PROCEDURE rpt_TEST_ReportsParams

@dtFrom datetime,
@dtTo datetime

AS

Set @dtTo = dateadd(d,+1,@dtTo)
Set NoCount on
truncate table sntxfers..DailyReportRuns
declare @dbName varchar(50),
@SQL nvarchar(2000)
declare db_cursor cursor for

SELECT DISTINCT
dbName =
case when strDatabase = 'sci' then 'sciprod' else strDatabase end
FROM clientmaster..DatabaseContext
WHERE (strSchemaVersion > N'3') and strDatabase <> 'EEMSuiteNJ'

Open db_cursor
fetch next from db_cursor into @dbName
while @@fetch_status = 0
begin

Set @SQL = ''
Set @SQL = @SQL + 'Insert sntxfers..DailyReportRuns'
Set @SQL = @SQL + ' SELECT dtCreated, idUserName, strSpecifics, ''' + @dbName + ''' as db,'
Set @SQL = @SQL + ' c.strName, c.strDescription'
Set @SQL = @SQL + ' FROM ' + @dbName + '..EventHistoryLog '
Set @SQL = @SQL + ' cross join ' + @dbName + '..client c'
Set @SQL = @SQL + ' WHERE (strSource = ''CPageBase.RedirectToReport'')'
Set @SQL = @SQL + ' and dtCreated between convert(varchar(12),'+ @dtFrom +' , 101)and convert(varchar(12),'+ @dtTo +', 101)'

EXEC(@SQL)

fetch next from db_cursor into @dbName
end
close db_cursor
deallocate db_cursor

select * , ReportName = substring(strSpecifics, charindex('is attempting to run report', strSpecifics) + 29,
(
charindex(', on database,', strSpecifics) - (charindex('is attempting to run report', strSpecifics) + 29)
)
)
from sntxfers..DailyReportRuns
order by dtCreated desc

I'm using Crystal report to display the users from each "team"
use of the reports; anyway I keep getting the same error when trying to setup this report in Crystal I get the error "Conversion failed when converting datetime from character string." Not sure what's wrong with my code because I declare the @dtFrom & @dtTo as datetime and Crystal ask for the parameters as datetime and when I give it dates for both the error occurs. I'm thinking it must be in the cursor part in the @SQL but could use some help.

I went to the circus lastnight

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-12 : 17:23:43

Try this instead of your dtCreated part of the WHERE clause:
dtCreated >= DATEADD(Day, DATEDIFF(Day, 0, @dtFrom), 0)
AND @dtFrom < DATEADD(Day, DATEDIFF(Day, 0, @dtTo, 0)

This doesn't do any conversions to varchar.

Tara Kizer
aka tduggan

barry
Starting Member

14 Posts

Posted - 2006-07-12 : 17:44:59

Set @SQL = @SQL + ' and dtCreated >= DATEADD(Day, DATEDIFF(Day, 0,' + @dtFrom +'), 0)AND @dtFrom < DATEADD(Day, DATEDIFF(Day, 0,' + @dtTo +', 0)'

Same error

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-12 : 17:52:12

Run the stored procedure in Query Analyzer using the same parameters that you are passing in via Crystal Reports. Does it work there?

Also, what values are you passing?

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources