Showing posts with label messages. Show all posts
Showing posts with label messages. Show all posts

Wednesday, March 28, 2012

No help from the error messages

I am writing a tracking system. There is a table in the Sql Server 2000 database that contains a column for the user's ntid, the page they visited, the date of the last visit, a column each to track hits for the current year and a previous year column (basically for archiveing and reporting purposes), and 12 columns for hits per month (obviously, one per column). To record a hit, my unit determined we would only track one hit per day, so basically, there are 3 possible outcomes I needed to account for :

1) A user had never hit the page before, so I need to record the user's ID, the page they hit for the first time (since it won't exist yet), increment the year counter for that user on that page, and then determine what month column counter should be incremented as well.

2) A user had hit the page before, but not on this same day, so I need to update the row for that user on that page, changing the last visit field to reflect the current date, and icnrementing the appropriate counters.

3) A user had hit the page already on the same day, so basically, nothing should be changed whatsoever. No action should be taken.

I wrote a stored procedure to attempt to accomplish that logic, and though it's probably not very pretty, I was surprised at how few errors I got on my first Syntax check. Here's the stored procedure :

CREATE PROCEDURE sp_hitMe
@.ntid varchar(10),
@.page varchar(50),
@.thisHit datetime
AS
SET NOCOUNT ON

DECLARE @.tempDate datetime
DECLARE @.yearCount int
DECLARE @.monthCount int
DECLARE @.inMonth varchar(20)
DECLARE @.monthColumn varchar(10)
SET @.inMonth = DATENAME(mm, @.thisHit)
SET @.monthColumn =
CASE
WHEN @.inMonth = 'January' THEN 'hitsInJan'
WHEN @.inMonth = 'February' THEN 'hitsInFeb'
WHEN @.inMonth = 'March' THEN 'hitsInMar'
WHEN @.inMonth = 'April' THEN 'hitsInApr'
WHEN @.inMonth = 'May' THEN 'hitsInMay'
WHEN @.inMonth = 'June' THEN 'hitsInJun'
WHEN @.inMonth = 'July' THEN 'hitsInJul'
WHEN @.inMonth = 'August' THEN 'hitsInAug'
WHEN @.inMonth = 'September' THEN 'hitsInSep'
WHEN @.inMonth = 'October' THEN 'hitsInOct'
WHEN @.inMonth = 'November' THEN 'hitsInNov'
WHEN @.inMonth = 'December' THEN 'hitsInDec'
END

DECLARE @.insString varchar(500)
DECLARE @.updString varchar(500)
SET @.insString = 'INSERT INTO tblTracking (ntid, page, lastVisit, hitsThisYear, ' + @.monthColumn + ') VALUES (' + @.ntid + ', ' + @.page + ', ' + @.thisHit + ', 1, 1)'

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
end
else
begin
DECLARE @.theColumn varchar (100)
SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Exec @.theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else
begin
Exec @.insString
end
GO

And to my surprise, the only 3 errors I got were :

Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 39
Incorrect syntax near the keyword 'end'.
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 45
Incorrect syntax near the keyword 'Exec'.
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 50
Incorrect syntax near the keyword 'end'.

However, these are of course so vague as to be useless to me. What's wrong with the procedure? What have I missed?

At around line 33 I changed the --Do Nothing so that it actually declares something that is never used...

begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
DECLARE @.theColumn1 varchar (100)
end
else

An alternative is finding out how to do something like an if not exists()

As far as the Exec, why don't you make @.theColumn a temporary table instead, and work with it that way? I don't know about using variables as tables, but you can do this easily with a temporary table.

|||

LD50:

At around line 33 I changed the --Do Nothing so that it actually declares something that is never used...

begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
DECLARE @.theColumn1 varchar (100)
end
else

An alternative is finding out how to do something like an if not exists()

As far as the Exec, why don't you make @.theColumn a temporary table instead, and work with it that way? I don't know about using variables as tables, but you can do this easily with a temporary table.

That do nothing must actually have been a slightly larger error, because when I changed it to declare a small variable (basically a bit, just to avoid too much memory usage), 2 of the errors went away. I'm still having ti gripe at me about the Exec (@.theColumn) line. I don't think in this case a temporary table will do the trick only because I'm basically only trying to get one value, which is the current value of the month counter. Any other ideas?

|||

I'm not quite sure what you're trying to do, or what the column names in tbltracking are, so you'll have to modify this a bit. But it will compile.

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!

DECLARE @.theColumn1 varchar (100)
end
else
begin
Create Table #theColumn (theColumn varchar (100))
Insert into #theColumn
Select monthname from tblTracking Where ntid = @.ntid and page = @.page
--SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Select Count(*) from #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else

|||

LD50:

An alternative is finding out how to do something like an if not exists()

That's exactly how you do it. IF NOT EXISTS(SELECT ...)

|||

Oh, THANK you! I did have to slightly modify what you put in there, but you had the gist of it, so that's awesome. I would go ahead and mark an answer here, and I probably will since you answered that concern, but I was testing the logic, and trying to input the same record twice, which should have simply done nothing, but instead it inserted a duplicate record. Any idea why the logic failed me there?

|||

well why don't you paste up your new code and we'll let you know...

A good troubleshooting tip is to add some select statements in each part of the if. That way you can run it and see which if statement is firing and/or see if the columns in the table contain the data you expect.

Example:

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
select * from tblTracking where lastVisit = @.thisHit
DECLARE @.theColumn1 varchar (100)
end
else
begin

select * from tblTracking

Create Table #theColumn (theColumn varchar (100))
Insert into #theColumn
Select monthname from tblTracking Where ntid = @.ntid and page = @.page
--SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Select Count(*) from #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else
begin
select * from tblTracking
Exec @.insString
end

|||

I also noticed that your if is illogical

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)

should be:

if exists(select * from tblTracking where ntid = @.ntid and page = @.page)

|||

LD50:

I also noticed that your if is illogical

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)

should be:

if exists(select * from tblTracking where ntid = @.ntid and page = @.page)

Actually, I noticed that myself and had already corrected it... *sheepish shrug*

OK, well I've added in a SELECT statement for the 'do-nothing' branch of logic and changed the two Exec statements to Print statements so I don't get a ton of duplicate records. But the logic is still somehow faulty, as I'm using the Query Analyzer to test it, and trying to input the same record that already exists in the table still prints out the INSERT string, instead of the do-nothing SELECT statement. Here's what I have now, see if you can help me figure out what the problem is...

CREATE PROCEDURE sp_hitMe
@.ntid varchar(10),
@.page varchar(50),
@.thisHit smalldatetime
AS
SET NOCOUNT ON

DECLARE @.yearCount int
DECLARE @.monthCount int
DECLARE @.inMonth varchar(20)
DECLARE @.monthColumn varchar(10)
SET @.inMonth = DATENAME(mm, @.thisHit)
SET @.monthColumn =
CASE
WHEN @.inMonth = 'January' THEN 'hitsInJan'
WHEN @.inMonth = 'February' THEN 'hitsInFeb'
WHEN @.inMonth = 'March' THEN 'hitsInMar'
WHEN @.inMonth = 'April' THEN 'hitsInApr'
WHEN @.inMonth = 'May' THEN 'hitsInMay'
WHEN @.inMonth = 'June' THEN 'hitsInJun'
WHEN @.inMonth = 'July' THEN 'hitsInJul'
WHEN @.inMonth = 'August' THEN 'hitsInAug'
WHEN @.inMonth = 'September' THEN 'hitsInSep'
WHEN @.inMonth = 'October' THEN 'hitsInOct'
WHEN @.inMonth = 'November' THEN 'hitsInNov'
WHEN @.inMonth = 'December' THEN 'hitsInDec'
END

DECLARE @.insString varchar(500)
DECLARE @.updString varchar(500)
SET @.insString = 'INSERT INTO tblTracking(ntid, page, lastVisit, hitsThisYear, ' + @.monthColumn + ') VALUES (''' + @.ntid + ''','' ' + @.page + ''', ''' + Convert(varchar(15), @.thisHit, 101) + ''', ''1'', ''1'')'

if exists(select * from tblTracking where ntid = @.ntid and page = @.page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
DECLARE @.bs bit
SELECT * FROM tblTracking where lastVisit = @.thisHit
end
else
begin
DECLARE @.theString varchar (100)
SET @.theString = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND page = @.page'
CREATE TABLE #theColumn (theColumn varchar (100))
INSERT INTO #theColumn
Exec @.theString
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND page = @.page) + 1
SET @.monthCount = (SELECT theColumn FROM #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + Convert(varchar(15), @.thisHit, 101) + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND page = @.page'
Print @.updString
end
end
else
begin
Print (@.insString)
end
GO

|||

Woo! Nevermind, I have finally figured it out. Turns out I needed to use LIKEs instead of =s when I was comparing the varchar values, and I needed to throw in some CASTs to turn some of the ints into varchars for the UPDATE string. If anyone cares to see the corrected, working code, I will post it, but it is working now.

Thanks for the help!

Wednesday, March 21, 2012

No attachments in subscription mail messages

We have reportingservices installed on the sql box. It's a W2K SP4 server
with sql2000 SP3a.
We have deployed several reports. All reports we have deployed after
installing SP1 do not contain an attached report (PDF, TIFF, Excel, CSV) but
when the report is rendered in the body of the message or when we view it in
the viewer there are no problems at all.
Reports deployed before installing SP1 are functioning correctly.
The following message we find in the logfiles:
aspnet_wp!library!c48!03/08/2005-14:29:41:: i INFO: Call to RenderFirst(
'/GMA_Test' )
aspnet_wp!library!c48!03/08/2005-14:29:51:: i INFO: Initializing
EnableExecutionLogging to 'True' as specified in Server system properties.
aspnet_wp!webserver!c48!03/08/2005-14:29:51:: i INFO: Processed report.
Report='/GMA_Test', Stream=''
aspnet_wp!library!c48!03/08/2005-14:29:52:: i INFO: Call to RenderNext(
'/GMA_Test' )
aspnet_wp!chunks!c48!03/08/2005-14:29:52:: i INFO: ###
GetReportChunk('RenderingInfo_HTML4.0', 2), chunk was not found!
this=8b4f21e9-99c6-45e0-a642-91bf34101b25
aspnet_wp!cache!c48!03/08/2005-14:29:52:: i INFO: Session live: /GMA_Test
aspnet_wp!webserver!c48!03/08/2005-14:29:52:: i INFO: Processed report.
Report='/GMA_Test', Stream=''
aspnet_wp!webserver!c48!03/08/2005-14:29:52:: i INFO: Processed report.
Report='/GMA_Test', Stream='524d8435-4afe-4a86-a02e-1bc9b4cb7f9d'
We have absolutly no clue!
Please help!Odd. Try http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
I've sent posts by MSFT stating that hotfix fixes more than Lotus Notes
attachment issues.
--
Adrian M.
MCP
"Stefan Cremers" <s.cremers@.fritom.com> wrote in message
news:e%23jZcUJJFHA.3500@.TK2MSFTNGP14.phx.gbl...
> We have reportingservices installed on the sql box. It's a W2K SP4 server
> with sql2000 SP3a.
> We have deployed several reports. All reports we have deployed after
> installing SP1 do not contain an attached report (PDF, TIFF, Excel, CSV)
> but when the report is rendered in the body of the message or when we view
> it in the viewer there are no problems at all.
> Reports deployed before installing SP1 are functioning correctly.
> The following message we find in the logfiles:
> aspnet_wp!library!c48!03/08/2005-14:29:41:: i INFO: Call to RenderFirst(
> '/GMA_Test' )
> aspnet_wp!library!c48!03/08/2005-14:29:51:: i INFO: Initializing
> EnableExecutionLogging to 'True' as specified in Server system
> properties.
> aspnet_wp!webserver!c48!03/08/2005-14:29:51:: i INFO: Processed report.
> Report='/GMA_Test', Stream=''
> aspnet_wp!library!c48!03/08/2005-14:29:52:: i INFO: Call to RenderNext(
> '/GMA_Test' )
> aspnet_wp!chunks!c48!03/08/2005-14:29:52:: i INFO: ###
> GetReportChunk('RenderingInfo_HTML4.0', 2), chunk was not found!
> this=8b4f21e9-99c6-45e0-a642-91bf34101b25
> aspnet_wp!cache!c48!03/08/2005-14:29:52:: i INFO: Session live: /GMA_Test
> aspnet_wp!webserver!c48!03/08/2005-14:29:52:: i INFO: Processed report.
> Report='/GMA_Test', Stream=''
> aspnet_wp!webserver!c48!03/08/2005-14:29:52:: i INFO: Processed report.
> Report='/GMA_Test', Stream='524d8435-4afe-4a86-a02e-1bc9b4cb7f9d'
>
> We have absolutly no clue!
> Please help!
>|||Adrian,
Thanks for your help.
I installed the hotfix but nithing chanced. Our problem still exists.
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:uARmooKJFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Odd. Try http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
> I've sent posts by MSFT stating that hotfix fixes more than Lotus Notes
> attachment issues.
> --
> Adrian M.
> MCP
>|||I have seen this similar issue. In my case I can send a link but if I send
the report nothing gets sent. I gave up and decided to wait for SP2 (this
month) and see if that fixes the problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stefan Cremers" <s.cremers@.fritom.com> wrote in message
news:OubUtTOJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Adrian,
> Thanks for your help.
> I installed the hotfix but nithing chanced. Our problem still exists.
>
> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> news:uARmooKJFHA.1476@.TK2MSFTNGP09.phx.gbl...
> > Odd. Try http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
> >
> > I've sent posts by MSFT stating that hotfix fixes more than Lotus Notes
> > attachment issues.
> >
> > --
> > Adrian M.
> > MCP
> >
>|||This is odd. In my web app I programmatically create subscriptions that
include the report as an attachment and it works fine.
--
Adrian M.
MCP
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uILWefOJFHA.3500@.TK2MSFTNGP14.phx.gbl...
>I have seen this similar issue. In my case I can send a link but if I send
> the report nothing gets sent. I gave up and decided to wait for SP2 (this
> month) and see if that fixes the problem.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Stefan Cremers" <s.cremers@.fritom.com> wrote in message
> news:OubUtTOJFHA.732@.TK2MSFTNGP12.phx.gbl...
>> Adrian,
>> Thanks for your help.
>> I installed the hotfix but nithing chanced. Our problem still exists.
>>
>> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
>> news:uARmooKJFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> > Odd. Try http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
>> >
>> > I've sent posts by MSFT stating that hotfix fixes more than Lotus Notes
>> > attachment issues.
>> >
>> > --
>> > Adrian M.
>> > MCP
>> >
>|||One thing I was told (but it was more than I wanted to do) was that the most
reliable method is to have the subscription be put in a directory for pickup
by your mail service. Might be something you would want to try.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:uAA4RzOJFHA.580@.TK2MSFTNGP15.phx.gbl...
> This is odd. In my web app I programmatically create subscriptions that
> include the report as an attachment and it works fine.
> --
> Adrian M.
> MCP
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uILWefOJFHA.3500@.TK2MSFTNGP14.phx.gbl...
> >I have seen this similar issue. In my case I can send a link but if I
send
> > the report nothing gets sent. I gave up and decided to wait for SP2
(this
> > month) and see if that fixes the problem.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Stefan Cremers" <s.cremers@.fritom.com> wrote in message
> > news:OubUtTOJFHA.732@.TK2MSFTNGP12.phx.gbl...
> >> Adrian,
> >>
> >> Thanks for your help.
> >>
> >> I installed the hotfix but nithing chanced. Our problem still exists.
> >>
> >>
> >>
> >> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> >> news:uARmooKJFHA.1476@.TK2MSFTNGP09.phx.gbl...
> >> > Odd. Try
http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
> >> >
> >> > I've sent posts by MSFT stating that hotfix fixes more than Lotus
Notes
> >> > attachment issues.
> >> >
> >> > --
> >> > Adrian M.
> >> > MCP
> >> >
> >>
> >
> >
>|||Here are some other things to check:
- Make sure the firewall (if one is being used) is set up to allow the MIME
type of the exported report to go out (PDF, Excel, etc). On most firewalls
you have to set up specific firewall rules to allow this (at least we had
to)
- On the SMTP server, make sure the 'Limit message size to (KB)' checkbox on
the SMTP Virtual Server property sheet Messages tab, is unchecked or specify
a sufficiently large enough value in the textbox. The default is is 2048KB.
If the default is checked and a report is bigger than this, the email will
not go with the attachment.
--
Adrian M.
MCP
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eEI2b4OJFHA.3916@.TK2MSFTNGP14.phx.gbl...
> One thing I was told (but it was more than I wanted to do) was that the
> most
> reliable method is to have the subscription be put in a directory for
> pickup
> by your mail service. Might be something you would want to try.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> news:uAA4RzOJFHA.580@.TK2MSFTNGP15.phx.gbl...
>> This is odd. In my web app I programmatically create subscriptions that
>> include the report as an attachment and it works fine.
>> --
>> Adrian M.
>> MCP
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:uILWefOJFHA.3500@.TK2MSFTNGP14.phx.gbl...
>> >I have seen this similar issue. In my case I can send a link but if I
> send
>> > the report nothing gets sent. I gave up and decided to wait for SP2
> (this
>> > month) and see if that fixes the problem.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Stefan Cremers" <s.cremers@.fritom.com> wrote in message
>> > news:OubUtTOJFHA.732@.TK2MSFTNGP12.phx.gbl...
>> >> Adrian,
>> >>
>> >> Thanks for your help.
>> >>
>> >> I installed the hotfix but nithing chanced. Our problem still exists.
>> >>
>> >>
>> >>
>> >> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
>> >> news:uARmooKJFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> >> > Odd. Try
> http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
>> >> >
>> >> > I've sent posts by MSFT stating that hotfix fixes more than Lotus
> Notes
>> >> > attachment issues.
>> >> >
>> >> > --
>> >> > Adrian M.
>> >> > MCP
>> >> >
>> >>
>> >
>> >
>>
>