Logparser issue with SQL Server 2005 Report Pack for IIS

Hi, I don't know if it is the right place to post this question, I am trying
to populate IISLOG DB with IIS log file using the following expression
(provided in the package DOC)
LogParser "SELECT date, time,
strcat(strcat(strcat(TO_STRING(TO_TIMESTAMP(date, time), 'hh'),':00-'),
TO_STRING(TO_TIMESTAMP(date, time), 'hh')),':59') AS Hour,
TO_Int(TO_String(date,'MM')) as Month, TO_Int(TO_String(date,'yyyy')) as
year,c-ip, cs-username, s-sitename, s-computername, s-ip, s-port, cs-method,
cs-uri-stem, cs-uri-query, sc-status, sc-substatus, sc-win32-status,
sc-bytes, cs-bytes, time-taken, cs-version, cs-host, cs(User-Agent),
cs(Cookie), cs(Referer) FROM d:\tracing\Tracingex061025.log TO
GlobalIISLogWF4"
-i:IISW3C -o:SQL -server:(local) -database:IISLog -createtable:ON -e:10
In the command line window, I wait for a while, then I get 3 lines
indicating Statistics without inserting any row in the table that has been
created which is "GlobalIISLogWF4"
I re-run the commande and launched SQL 2005 profiler,
I see the following events
***************************
RPC:Starting
RPC:Completed
in the text I see only
exec sp_cursor 180150003,65536
***************************
but absolutly no inserts
It is a win2k3R2 64bit, sql server 2005 64 bit and logparser the 2 versions
32 bit and 64 bit.
ThanksHello Eliassal,
I would like to know that if you have enable the IIS to log the cs(Cookie),
cs(Referer) event.
Also, what about using the following Command?
LogParser "SELECT date, time,
strcat(strcat(strcat(TO_STRING(TO_TIMESTAMP(date, time), 'hh'),':00-'),
TO_STRING(TO_TIMESTAMP(date, time), 'hh')),':59') AS Hour,
TO_Int(TO_String(date,'MM')) as Month, TO_Int(TO_String(date,'yyyy')) as
year,c-ip, cs-username, s-sitename, s-computername, s-ip, s-port,
cs-method,
cs-uri-stem, cs-uri-query, sc-status, sc-substatus, sc-win32-status,
sc-bytes, cs-bytes, time-taken, cs-version, cs-host, [cs(User-Agent)],
[cs(Cookie)], [cs(Referer)] FROM d:\tracing\Tracingex061025.log TO
GlobalIISLogWF4"
-i:IISW3C -o:SQL -server:(local) -database:IISLog -createtable:ON -e:10
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||HI, yes both are enabled. Only"Host" is not enabled.
I ran the sql you suggested, no inserts as well, the ouput was as follows
Statistics:
--
Elements processed: 422762
Elements output: 422762
Execution time: 22.00 seconds
Any idea? Thanks for your help
"Wei Lu [MSFT]" wrote:
> Hello Eliassal,
> I would like to know that if you have enable the IIS to log the cs(Cookie),
> cs(Referer) event.
> Also, what about using the following Command?
> LogParser "SELECT date, time,
> strcat(strcat(strcat(TO_STRING(TO_TIMESTAMP(date, time), 'hh'),':00-'),
> TO_STRING(TO_TIMESTAMP(date, time), 'hh')),':59') AS Hour,
> TO_Int(TO_String(date,'MM')) as Month, TO_Int(TO_String(date,'yyyy')) as
> year,c-ip, cs-username, s-sitename, s-computername, s-ip, s-port,
> cs-method,
> cs-uri-stem, cs-uri-query, sc-status, sc-substatus, sc-win32-status,
> sc-bytes, cs-bytes, time-taken, cs-version, cs-host, [cs(User-Agent)],
> [cs(Cookie)], [cs(Referer)] FROM d:\tracing\Tracingex061025.log TO
> GlobalIISLogWF4"
> -i:IISW3C -o:SQL -server:(local) -database:IISLog -createtable:ON -e:10
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>|||Hello Eliassal,
You could reduce the column, and check which column will cause this issue.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi, I launched a full sql 2005 profiler, In the session I HAVE THE FOLLOWING
ERROR
***********************************************************
exec sp_cursor 180150003, 65536
Error: 16902, Severity: 16, State: 33
sp_cursor: The value of the parameter 'optype' is invalid.
***********************************************************
I tried to execute the script by taking off 4 fields each time, no way
always the same error
The problem is we don't have the sql Insert phrase to execute in SSMS to be
able to see the error and don't no the internal of loparser of hiow it
inserts.
I am really going desperate. I need to demo the reporting to my director
next week
"Wei Lu [MSFT]" wrote:
> Hello Eliassal,
> You could reduce the column, and check which column will cause this issue.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Eliassal,
I searched our internal database and found that this error may caused by
the log file format is not correct.
You may try to get the IISLog from other machines and try it again.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||What is strange is that when I run the command against any other machine
(32bit) and not 64bit the upload works fine
The log files come from a win2k 32 bit machines. My sql2005 64bit is on a
win2k3 R2
"Wei Lu [MSFT]" wrote:
> Hello Eliassal,
> I searched our internal database and found that this error may caused by
> the log file format is not correct.
> You may try to get the IISLog from other machines and try it again.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Eliassal,
Please note the managed newsgroups are staffed weekdays by Microsoft
Support professionals. Our goal is to provide a one business day response
to all posts.
Since this issue is critical, I would like to suggest you to use the
following workaround:
1. Use the Logparser to import the IIS log to a SQL Server 2005 32bit
machine.
2. Use the SSIS to import from the 32 bit machine to the 64bit SQL Server.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi, I setup another machine everything in 64bit and logparser works fine. So
I think it is something wrong with either the setup of sql or the OS.
However, I have a new issue now for displaying the results in Reporting
services. I have loaded 10 days worth of IIS log (5 million rows). Whenever I
try to launch any report, IN IE I have the message "Report is being
generated" and wait for almost 30 to 45 minutes then I get a timeout. I know
the table is provided with no optimization (indexes, non clustered....)
I launched SQKL profiler and found that in different SP there is a lot of
complex sql statements
Any idea or is there sombody who has optimized the table or the whole DB
Thanks in advance
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Eliassal,
I would like to suggest you use the Report Cache to improve the report
performence.
Also, I suggest you to reduce the record number in your dataset.
Please note, our service does not provide support on Resource Kit Utilities.
For more detailed information, please refer our team blog:
http://blogs.msdn.com/msdnts/archive/2006/11/08/msdn-service-introduction.as
px
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.