xp cmdshell: code

Showing posts with label code. Show all posts

Friday, March 30, 2012

Problem scheduling job

I have a script that I wrote (please pardon my less than beautiful code) to
dump some aggregate data into a table for reporting purposes. It populates
a
few temp tables, then inserts into the destination table.
This script works fine in QA, but when I try to run it from a SA Job it
fails with the following error:
Line 12: Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170).
NOTE: The step was retried the requested number of times (1) without
succeeding. The step failed.
Here's my script:
--begin script
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status
INTO #NotStarted
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 1
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberNotStarted, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #NSTotals
FROM #NotStarted
GROUP BY owner
ORDER BY OWNER ASC
----
--
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status
INTO #InProgress
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 3
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberInProgress,CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #ProgTotals
FROM #InProgress
GROUP BY owner
ORDER BY OWNER ASC
----
-
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status,
CONVERT(char(10), T.followup_date, 101)AS FUDate
INTO #Overdue
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE T.status = '3'
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberOverdue, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #ODTotals
FROM #Overdue
WHERE FUDate < GETDATE()
GROUP BY owner
ORDER BY OWNER ASC
----
-
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status,
CONVERT(char(10), T.completion_date, 101) AS CompletedDate
INTO #Completed
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 2
--att tasks completed in the last 24 hours
AND T.completion_date >= DATEADD(hh, -24,GETDATE())-- or enter a specific
date like this '20050127'
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberCompleted, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #CompTotals
FROM #Completed
GROUP BY owner
ORDER BY OWNER ASC
----
---
--insert records into permanent table for reports
INSERT INTO task_totals_collector
SELECT UM.user_id AS Employee, NS.NumberNotStarted AS NotStarted,
PT.NumberInProgress AS InProgress,CT.NumberCompleted AS Completed,
CONVERT(char(10), GETDATE(), 101)AS CalcDate, OT.NumberOverdue AS Overdue
FROM user_mstr UM
LEFT JOIN #NSTotals NS
ON UM.user_id=NS.owner
LEFT JOIN #ProgTotals PT ON
UM.user_id=PT.owner
LEFT JOIN #CompTotals CT ON
UM.user_id=CT.owner
LEFT JOIN #ODTotals OT ON
UM.user_id=OT.owner
ORDER BY UM.user_id ASC
--end of script
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.workthin.com"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
> --att tasks completed in the last 24 hours
> AND T.completion_date >= DATEADD(hh, -24,GETDATE())-- or enter a specific
> date like this '20050127'
Patrick,
The only thing I can see that might be causing that error is this part; I'm
not sure if this is just wrapped because of the news client, though... You
should double-check that to make sure. I can't find the string '20050128'
in your code anywhere... Do you have some dynamic SQL that's not posted?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||No, I think it's getting this from GETDATE(). The database I'm working with
has a lot of dates stored as CHAR(8), which drives me nuts, as I can ORDER B
Y
on these columns. In my query I CONVERT them to DATETIME, but they're still
formatted as 'YYYYMMDD'.
"Adam Machanic" wrote:

> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
> Patrick,
> The only thing I can see that might be causing that error is this part; I'
m
> not sure if this is just wrapped because of the news client, though... You
> should double-check that to make sure. I can't find the string '20050128'
> in your code anywhere... Do you have some dynamic SQL that's not posted?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||One thing I can suggest is to put brackets ( [ ] ) around all your table and
column names as you have a lot of reserved words as object names.
Andrew J. Kelly SQL MVP
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
>I have a script that I wrote (please pardon my less than beautiful code) to
> dump some aggregate data into a table for reporting purposes. It
> populates a
> few temp tables, then inserts into the destination table.
> This script works fine in QA, but when I try to run it from a SA Job it
> fails with the following error:
> Line 12: Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170).
> NOTE: The step was retried the requested number of times (1) without
> succeeding. The step failed.
>
> Here's my script:
> --begin script
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner,
> TU.createdate,T.status
> INTO #NotStarted
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE t.status = 1
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberNotStarted, CONVERT(char(10),
> GETDATE(),
> 101)AS [DATE]
> INTO #NSTotals
> FROM #NotStarted
> GROUP BY owner
> ORDER BY OWNER ASC
> ----
--
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner,
> TU.createdate,T.status
> INTO #InProgress
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE t.status = 3
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberInProgress,CONVERT(char(10),
> GETDATE(),
> 101)AS [DATE]
> INTO #ProgTotals
> FROM #InProgress
> GROUP BY owner
> ORDER BY OWNER ASC
> ----
--
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner,
> TU.createdate,T.status,
> CONVERT(char(10), T.followup_date, 101)AS FUDate
> INTO #Overdue
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE T.status = '3'
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberOverdue, CONVERT(char(10), GETDATE(),
> 101)AS [DATE]
> INTO #ODTotals
> FROM #Overdue
> WHERE FUDate < GETDATE()
> GROUP BY owner
> ORDER BY OWNER ASC
> ----
--
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner,
> TU.createdate,T.status,
> CONVERT(char(10), T.completion_date, 101) AS CompletedDate
> INTO #Completed
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE t.status = 2
> --att tasks completed in the last 24 hours
> AND T.completion_date >= DATEADD(hh, -24,GETDATE())-- or enter a specific
> date like this '20050127'
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberCompleted, CONVERT(char(10),
> GETDATE(),
> 101)AS [DATE]
> INTO #CompTotals
> FROM #Completed
> GROUP BY owner
> ORDER BY OWNER ASC
> ----
---
> --insert records into permanent table for reports
> INSERT INTO task_totals_collector
> SELECT UM.user_id AS Employee, NS.NumberNotStarted AS NotStarted,
> PT.NumberInProgress AS InProgress,CT.NumberCompleted AS Completed,
> CONVERT(char(10), GETDATE(), 101)AS CalcDate, OT.NumberOverdue AS Overdue
> FROM user_mstr UM
> LEFT JOIN #NSTotals NS
> ON UM.user_id=NS.owner
> LEFT JOIN #ProgTotals PT ON
> UM.user_id=PT.owner
> LEFT JOIN #CompTotals CT ON
> UM.user_id=CT.owner
> LEFT JOIN #ODTotals OT ON
> UM.user_id=OT.owner
> ORDER BY UM.user_id ASC
> --end of script
>
>
> Patrick Rouse
> Microsoft MVP - Terminal Server
> http://www.workthin.com|||I put brackets around the tables named user_id, and then ran just the first
section (up to the first --), which fails at the first GETDATE with
this error:
Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170). The step
failed.
Like I said, it works fine in QA, just not in a job. :(
"Andrew J. Kelly" wrote:

> One thing I can suggest is to put brackets ( [ ] ) around all your table a
nd
> column names as you have a lot of reserved words as object names.
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
>
>|||Patrick,
I haven't followed the whole thread but do you actually have "GO" in your
SQL step. "GO" is a batch terminator used by QA and is not part of T-SQL.
So, you would want to take it out.
-oj
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:5B11E86A-08E5-4027-926E-84AE2A3AFBEA@.microsoft.com...
>I put brackets around the tables named user_id, and then ran just the first
> section (up to the first --), which fails at the first GETDATE with
> this error:
> Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170). The step
> failed.
> Like I said, it works fine in QA, just not in a job. :(
> "Andrew J. Kelly" wrote:
>|||Good to know, but I removed the USE DatabaseName & GO statements and still
get the same error.
"Patrick Rouse" wrote:
> No, I think it's getting this from GETDATE(). The database I'm working wi
th
> has a lot of dates stored as CHAR(8), which drives me nuts, as I can ORDER
BY
> on these columns. In my query I CONVERT them to DATETIME, but they're stil
l
> formatted as 'YYYYMMDD'.
> "Adam Machanic" wrote:
>|||First off as OJ mentions , do you have the GO's in the actual code? How you
know it is the GETDATE() that is failing or are you assuming because it is a
date type value in the error message? Here is the first block from your
original post:
<<<<<
--begin script
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status
INTO #NotStarted
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 1
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberNotStarted, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #NSTotals
FROM #NotStarted
GROUP BY owner
ORDER BY OWNER ASC
I can't tell from this post but it looks like you don't have a space between
the end of the CONVERT and the AS. What happens if you set the date to a
variable and use the variable in the select instead?
Andrew J. Kelly SQL MVP
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:5B11E86A-08E5-4027-926E-84AE2A3AFBEA@.microsoft.com...
>I put brackets around the tables named user_id, and then ran just the first
> section (up to the first --), which fails at the first GETDATE with
> this error:
> Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170). The step
> failed.
> Like I said, it works fine in QA, just not in a job. :(
> "Andrew J. Kelly" wrote:
>|||Patrick,
Make it easier on us if you would script out the job and post it here. ;-)
(you can use EM to do that).
There's probably a silly syntax error.
-oj
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:58F52F80-E014-4CF7-8868-9A919AA4EBEA@.microsoft.com...
> Good to know, but I removed the USE DatabaseName & GO statements and still
> get the same error.
>|||I assume it's the get date, because that returns the date listed in the erro
r
I posted, and the error states on "line 12" which is where GETDATE is
specified.
"Andrew J. Kelly" wrote:

> First off as OJ mentions , do you have the GO's in the actual code? How y
ou
> know it is the GETDATE() that is failing or are you assuming because it is
a
> date type value in the error message? Here is the first block from your
> original post:
> <<<<<
> --begin script
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.statu
s
> INTO #NotStarted
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE t.status = 1
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberNotStarted, CONVERT(char(10), GETDATE(
),
> 101)AS [DATE]
> INTO #NSTotals
> FROM #NotStarted
> GROUP BY owner
> ORDER BY OWNER ASC
> I can't tell from this post but it looks like you don't have a space betwe
en
> the end of the CONVERT and the AS. What happens if you set the date to a
> variable and use the variable in the select instead?
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:5B11E86A-08E5-4027-926E-84AE2A3AFBEA@.microsoft.com...
>
>sql

Problem returning OUTPUT in stored procedure

Hi, I have this output, @.RegisterFlag int OUTPUT

and I have a transaction going on, so my code (I just put some relevant code here) is:

1BEGIN TRAN2 SELECT @.getDealername = OrgNameFROM OrgWHERE OrgName = @.DealerName3If @.getDealernameisnull45ELSE6 BEGIN7 set @.RegisterFlag = 28ROLLBACK TRAN9 RETURN10 END1112COMMIT TRAN13set @.RegisterFlag = 1

The problem I am facing now is I couldn't get @.RegisterFlag = 2 return back to my asp.net code when it reached line 7, instead I got this error mesg:

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

How do I fix this? Many big thanks.

Hi, it's ok already. I realize that the problem is I have both transaction code, one in asp.net and another one in stored procedure. After taken out the one in asp.net, it works now. Thanks.

Monday, March 26, 2012

Problem rendering PDF with Diagram in code...

Hi!
I have the problem that I can not render a PDF file containing a diagram via
code. My function is essentially:
Private Sub SaveAs()
... Some initalization here...
Try
results = rs.Render(selItem.Path, format, historyID, deviceInfo, param,
credentials, showHide, encoding, mimetype, reportHistoyrParameters,
warnings, streamIDs)
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
Catch exception As Exception
HandleException(exception)
End Try
End If
End Sub
When I use a format other than PDF (e.g. Excel or MHTML) everything works
fine and I am able to retrieve the Report with the diagram included, no
Problem. On the other hand when I am rendering a PDF-File without any
Diagrams, everything works fine as well. Only when I try to render a report
that includes a Diagram in PDF, an exception is thrown. The text says
something like: "Retrieval of contents with invalid configuration for
contentstype: text/html - SOAP expects text/xml" (Sorry for not giving the
accurate error message - I am using the German version which displays German
error texts).
Thank you for your help and have a nice day
FrankHi!
There is something I like to add to my initial posting. The same error (or
some similar error) occurs when I try to render the report with the diagram
from the Report Manager Web Interface. Here the Error is that the Server
Application is not avialable (an error I get from within my code too from
time to time).
Have a nice day and thank you
Frank
"Frank Geisler" <frank_geisler@.geislers.net> schrieb im Newsbeitrag
news:O1uv2p3VEHA.1164@.tk2msftngp13.phx.gbl...
> Hi!
> I have the problem that I can not render a PDF file containing a diagram
via
> code. My function is essentially:
> Private Sub SaveAs()
> ... Some initalization here...
> Try
> results = rs.Render(selItem.Path, format, historyID, deviceInfo, param,
> credentials, showHide, encoding, mimetype, reportHistoyrParameters,
> warnings, streamIDs)
> Dim stream As FileStream = File.OpenWrite(fileName)
> stream.Write(results, 0, results.Length)
> stream.Close()
> Catch exception As Exception
> HandleException(exception)
> End Try
> End If
> End Sub
> When I use a format other than PDF (e.g. Excel or MHTML) everything works
> fine and I am able to retrieve the Report with the diagram included, no
> Problem. On the other hand when I am rendering a PDF-File without any
> Diagrams, everything works fine as well. Only when I try to render a
report
> that includes a Diagram in PDF, an exception is thrown. The text says
> something like: "Retrieval of contents with invalid configuration for
> contentstype: text/html - SOAP expects text/xml" (Sorry for not giving the
> accurate error message - I am using the German version which displays
German
> error texts).
> Thank you for your help and have a nice day
> Frank
>|||When you say diagram, do you mean an image using the image control? Are you
setting the correct MIME type for the image?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Geisler" <frank_geisler@.geislers.net> wrote in message
news:%23Ppg%23t3VEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Hi!
> There is something I like to add to my initial posting. The same error (or
> some similar error) occurs when I try to render the report with the
diagram
> from the Report Manager Web Interface. Here the Error is that the Server
> Application is not avialable (an error I get from within my code too from
> time to time).
> Have a nice day and thank you
> Frank
>
> "Frank Geisler" <frank_geisler@.geislers.net> schrieb im Newsbeitrag
> news:O1uv2p3VEHA.1164@.tk2msftngp13.phx.gbl...
> > Hi!
> >
> > I have the problem that I can not render a PDF file containing a diagram
> via
> > code. My function is essentially:
> >
> > Private Sub SaveAs()
> > ... Some initalization here...
> >
> > Try
> > results = rs.Render(selItem.Path, format, historyID, deviceInfo,
param,
> > credentials, showHide, encoding, mimetype, reportHistoyrParameters,
> > warnings, streamIDs)
> >
> > Dim stream As FileStream = File.OpenWrite(fileName)
> > stream.Write(results, 0, results.Length)
> > stream.Close()
> > Catch exception As Exception
> > HandleException(exception)
> > End Try
> > End If
> > End Sub
> >
> > When I use a format other than PDF (e.g. Excel or MHTML) everything
works
> > fine and I am able to retrieve the Report with the diagram included, no
> > Problem. On the other hand when I am rendering a PDF-File without any
> > Diagrams, everything works fine as well. Only when I try to render a
> report
> > that includes a Diagram in PDF, an exception is thrown. The text says
> > something like: "Retrieval of contents with invalid configuration for
> > contentstype: text/html - SOAP expects text/xml" (Sorry for not giving
the
> > accurate error message - I am using the German version which displays
> German
> > error texts).
> >
> > Thank you for your help and have a nice day
> >
> > Frank
> >
> >
>|||Hi Brian!
There is another idea that pops to my mind. I have two collegues who do not
have difficulties to render reports with charts in PDF. Both of them have
Adobe Acrobat 6 installed. Is this a prerequisite? If I understand the
architecture of RS right, this should not be a prerequisit. As I understand
it PDF rendering is done autnomously by the Report Server without any
additional software. Or am I wrong?
Have a nice day
Frank|||You are correct. You do not have to have PDF installed to export a report
using the PDF render.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Geisler" <frank_geisler@.geislers.net> wrote in message
news:OahSp$DWEHA.2952@.TK2MSFTNGP09.phx.gbl...
> Hi Brian!
> There is another idea that pops to my mind. I have two collegues who do
not
> have difficulties to render reports with charts in PDF. Both of them have
> Adobe Acrobat 6 installed. Is this a prerequisite? If I understand the
> architecture of RS right, this should not be a prerequisit. As I
understand
> it PDF rendering is done autnomously by the Report Server without any
> additional software. Or am I wrong?
> Have a nice day
> Frank
>|||Hi Bruce!
Thank you for your quick response. That is what I thought too. But of course
this does not solve my initial problem: Reports with charts can not be
renderd to PDF in my environment here. Today I have installed SP1 but this
did not fix my problem. Reports with charts can still not be rendered to
PDF. What shall I do? Where can I start to search the error?
Thank you and have a nice day
Frank
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> schrieb im
Newsbeitrag news:O4lGN5JWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> You are correct. You do not have to have PDF installed to export a report
> using the PDF render.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> news:OahSp$DWEHA.2952@.TK2MSFTNGP09.phx.gbl...
> > Hi Brian!
> >
> > There is another idea that pops to my mind. I have two collegues who do
> not
> > have difficulties to render reports with charts in PDF. Both of them
have
> > Adobe Acrobat 6 installed. Is this a prerequisite? If I understand the
> > architecture of RS right, this should not be a prerequisit. As I
> understand
> > it PDF rendering is done autnomously by the Report Server without any
> > additional software. Or am I wrong?
> >
> > Have a nice day
> >
> > Frank
> >
> >
>|||Would you send me a zip of the most recent RS*.log files under C:\Program Files\Microsoft SQL
Server?
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Geisler" <frank_geisler@.geislers.net> wrote in message
news:u77G7cSWEHA.2168@.TK2MSFTNGP10.phx.gbl...
> Hi Bruce!
> Thank you for your quick response. That is what I thought too. But of course
> this does not solve my initial problem: Reports with charts can not be
> renderd to PDF in my environment here. Today I have installed SP1 but this
> did not fix my problem. Reports with charts can still not be rendered to
> PDF. What shall I do? Where can I start to search the error?
> Thank you and have a nice day
> Frank
>
> "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> schrieb im
> Newsbeitrag news:O4lGN5JWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> > You are correct. You do not have to have PDF installed to export a report
> > using the PDF render.
> >
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> > news:OahSp$DWEHA.2952@.TK2MSFTNGP09.phx.gbl...
> > > Hi Brian!
> > >
> > > There is another idea that pops to my mind. I have two collegues who do
> > not
> > > have difficulties to render reports with charts in PDF. Both of them
> have
> > > Adobe Acrobat 6 installed. Is this a prerequisite? If I understand the
> > > architecture of RS right, this should not be a prerequisit. As I
> > understand
> > > it PDF rendering is done autnomously by the Report Server without any
> > > additional software. Or am I wrong?
> > >
> > > Have a nice day
> > >
> > > Frank
> > >
> > >
> >
> >
>|||Hi Donovan!
I have done the following:
I have used the Report Manager to view the Sample-Report Employee Sales
Summery of the Adventureworks Example and here I have selected one of the
employees to get a report with some nice charts on it. Then I tried to
export it (within Report Manager) to PDF. The accordings log-entires in the
log file (ReportServer_*.log) are:
aspnet_wp!library!acc!06/25/2004-10:06:31:: i INFO: Call to GetPermissions:/
aspnet_wp!library!acc!06/25/2004-10:06:32:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!acc!06/25/2004-10:06:50:: i INFO: Call to
GetPermissions:/SampleReports
aspnet_wp!library!acc!06/25/2004-10:06:50:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!194!06/25/2004-10:06:54:: i INFO: Call to
GetPermissions:/SampleReports/Employee Sales Summary
aspnet_wp!library!194!06/25/2004-10:06:56:: i INFO: Call to
GetSystemPermissions
aspnet_wp!library!194!06/25/2004-10:07:20:: i INFO: Call to RenderFirst(
'/SampleReports/Employee Sales Summary' )
aspnet_wp!library!194!06/25/2004-10:07:38:: i INFO: Initializing
EnableExecutionLogging to 'True' as specified in Server system properties.
aspnet_wp!webserver!194!06/25/2004-10:07:39:: i INFO: Processed report.
Report='/SampleReports/Employee Sales Summary', Stream=''
aspnet_wp!chunks!acc!06/25/2004-10:07:41:: i INFO: ###
GetReportChunk('C_6_S', 1), chunk was not found!
this=23b56e3b-a705-495b-ae16-8793896cf8d5
aspnet_wp!webserver!194!06/25/2004-10:07:42:: i INFO: Processed report.
Report='/SampleReports/Employee Sales Summary',
Stream='2ddd32ba-c0d3-4855-ab72-909781cf5f79'
aspnet_wp!webserver!acc!06/25/2004-10:07:42:: i INFO: Processed report.
Report='/SampleReports/Employee Sales Summary', Stream='C_6_S'
aspnet_wp!chunks!194!06/25/2004-10:07:42:: i INFO: ###
GetReportChunk('C_20_S', 1), chunk was not found!
this=23b56e3b-a705-495b-ae16-8793896cf8d5
aspnet_wp!webserver!194!06/25/2004-10:07:43:: i INFO: Processed report.
Report='/SampleReports/Employee Sales Summary', Stream='C_20_S'
aspnet_wp!library!194!06/25/2004-10:07:49:: i INFO: Call to RenderNext(
'/SampleReports/Employee Sales Summary' )
aspnet_wp!chunks!194!06/25/2004-10:07:50:: i INFO: ###
GetReportChunk('RenderingInfo_PDF', 2), chunk was not found!
this=23b56e3b-a705-495b-ae16-8793896cf8d5
I hope this helps a lillte bit. Thank you very much for you effort.
Have a nice day
Frank
"Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> schrieb im
Newsbeitrag news:eiGHltgWEHA.2716@.tk2msftngp13.phx.gbl...
> Would you send me a zip of the most recent RS*.log files under C:\Program
Files\Microsoft SQL
> Server?
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> news:u77G7cSWEHA.2168@.TK2MSFTNGP10.phx.gbl...
> > Hi Bruce!
> >
> > Thank you for your quick response. That is what I thought too. But of
course
> > this does not solve my initial problem: Reports with charts can not be
> > renderd to PDF in my environment here. Today I have installed SP1 but
this
> > did not fix my problem. Reports with charts can still not be rendered
to
> > PDF. What shall I do? Where can I start to search the error?
> >
> > Thank you and have a nice day
> >
> > Frank
> >
> >
> > "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> schrieb im
> > Newsbeitrag news:O4lGN5JWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> > > You are correct. You do not have to have PDF installed to export a
report
> > > using the PDF render.
> > >
> > > --
> > > Bruce Johnson [MSFT]
> > > Microsoft SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> > > news:OahSp$DWEHA.2952@.TK2MSFTNGP09.phx.gbl...
> > > > Hi Brian!
> > > >
> > > > There is another idea that pops to my mind. I have two collegues who
do
> > > not
> > > > have difficulties to render reports with charts in PDF. Both of them
> > have
> > > > Adobe Acrobat 6 installed. Is this a prerequisite? If I understand
the
> > > > architecture of RS right, this should not be a prerequisit. As I
> > > understand
> > > > it PDF rendering is done autnomously by the Report Server without
any
> > > > additional software. Or am I wrong?
> > > >
> > > > Have a nice day
> > > >
> > > > Frank
> > > >
> > > >
> > >
> > >
> >
> >
>|||Did you install SP1 yet? I know we fixed some 'Chunk not found' errors.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Geisler" <frank_geisler@.geislers.net> wrote in message
news:eRDg9poWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> Hi Donovan!
> I have done the following:
> I have used the Report Manager to view the Sample-Report Employee Sales
> Summery of the Adventureworks Example and here I have selected one of the
> employees to get a report with some nice charts on it. Then I tried to
> export it (within Report Manager) to PDF. The accordings log-entires in
> the
> log file (ReportServer_*.log) are:
> aspnet_wp!library!acc!06/25/2004-10:06:31:: i INFO: Call to
> GetPermissions:/
> aspnet_wp!library!acc!06/25/2004-10:06:32:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!acc!06/25/2004-10:06:50:: i INFO: Call to
> GetPermissions:/SampleReports
> aspnet_wp!library!acc!06/25/2004-10:06:50:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!194!06/25/2004-10:06:54:: i INFO: Call to
> GetPermissions:/SampleReports/Employee Sales Summary
> aspnet_wp!library!194!06/25/2004-10:06:56:: i INFO: Call to
> GetSystemPermissions
> aspnet_wp!library!194!06/25/2004-10:07:20:: i INFO: Call to RenderFirst(
> '/SampleReports/Employee Sales Summary' )
> aspnet_wp!library!194!06/25/2004-10:07:38:: i INFO: Initializing
> EnableExecutionLogging to 'True' as specified in Server system
> properties.
> aspnet_wp!webserver!194!06/25/2004-10:07:39:: i INFO: Processed report.
> Report='/SampleReports/Employee Sales Summary', Stream=''
> aspnet_wp!chunks!acc!06/25/2004-10:07:41:: i INFO: ###
> GetReportChunk('C_6_S', 1), chunk was not found!
> this=23b56e3b-a705-495b-ae16-8793896cf8d5
> aspnet_wp!webserver!194!06/25/2004-10:07:42:: i INFO: Processed report.
> Report='/SampleReports/Employee Sales Summary',
> Stream='2ddd32ba-c0d3-4855-ab72-909781cf5f79'
> aspnet_wp!webserver!acc!06/25/2004-10:07:42:: i INFO: Processed report.
> Report='/SampleReports/Employee Sales Summary', Stream='C_6_S'
> aspnet_wp!chunks!194!06/25/2004-10:07:42:: i INFO: ###
> GetReportChunk('C_20_S', 1), chunk was not found!
> this=23b56e3b-a705-495b-ae16-8793896cf8d5
> aspnet_wp!webserver!194!06/25/2004-10:07:43:: i INFO: Processed report.
> Report='/SampleReports/Employee Sales Summary', Stream='C_20_S'
> aspnet_wp!library!194!06/25/2004-10:07:49:: i INFO: Call to RenderNext(
> '/SampleReports/Employee Sales Summary' )
> aspnet_wp!chunks!194!06/25/2004-10:07:50:: i INFO: ###
> GetReportChunk('RenderingInfo_PDF', 2), chunk was not found!
> this=23b56e3b-a705-495b-ae16-8793896cf8d5
> I hope this helps a lillte bit. Thank you very much for you effort.
> Have a nice day
> Frank
>
> "Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> schrieb im
> Newsbeitrag news:eiGHltgWEHA.2716@.tk2msftngp13.phx.gbl...
>> Would you send me a zip of the most recent RS*.log files under C:\Program
> Files\Microsoft SQL
>> Server?
>> --
>> Thanks.
>> Donovan R. Smith
>> Software Test Lead
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
>> news:u77G7cSWEHA.2168@.TK2MSFTNGP10.phx.gbl...
>> > Hi Bruce!
>> >
>> > Thank you for your quick response. That is what I thought too. But of
> course
>> > this does not solve my initial problem: Reports with charts can not be
>> > renderd to PDF in my environment here. Today I have installed SP1 but
> this
>> > did not fix my problem. Reports with charts can still not be rendered
> to
>> > PDF. What shall I do? Where can I start to search the error?
>> >
>> > Thank you and have a nice day
>> >
>> > Frank
>> >
>> >
>> > "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> schrieb im
>> > Newsbeitrag news:O4lGN5JWEHA.1012@.TK2MSFTNGP09.phx.gbl...
>> > > You are correct. You do not have to have PDF installed to export a
> report
>> > > using the PDF render.
>> > >
>> > > --
>> > > Bruce Johnson [MSFT]
>> > > Microsoft SQL Server Reporting Services
>> > >
>> > > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> > >
>> > >
>> > > "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
>> > > news:OahSp$DWEHA.2952@.TK2MSFTNGP09.phx.gbl...
>> > > > Hi Brian!
>> > > >
>> > > > There is another idea that pops to my mind. I have two collegues
>> > > > who
> do
>> > > not
>> > > > have difficulties to render reports with charts in PDF. Both of
>> > > > them
>> > have
>> > > > Adobe Acrobat 6 installed. Is this a prerequisite? If I understand
> the
>> > > > architecture of RS right, this should not be a prerequisit. As I
>> > > understand
>> > > > it PDF rendering is done autnomously by the Report Server without
> any
>> > > > additional software. Or am I wrong?
>> > > >
>> > > > Have a nice day
>> > > >
>> > > > Frank
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>|||Hi Brian!
Yes I installed SP1 and hoped that the error went away. Unfortunately it did
not. I use the german version of Reporting Services and also the german SP1.
Have a nice day
Frank
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> schrieb im
Newsbeitrag news:OSebB7rWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> Did you install SP1 yet? I know we fixed some 'Chunk not found' errors.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> news:eRDg9poWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> > Hi Donovan!
> >
> > I have done the following:
> >
> > I have used the Report Manager to view the Sample-Report Employee Sales
> > Summery of the Adventureworks Example and here I have selected one of
the
> > employees to get a report with some nice charts on it. Then I tried to
> > export it (within Report Manager) to PDF. The accordings log-entires in
> > the
> > log file (ReportServer_*.log) are:
> >
> > aspnet_wp!library!acc!06/25/2004-10:06:31:: i INFO: Call to
> > GetPermissions:/
> > aspnet_wp!library!acc!06/25/2004-10:06:32:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!acc!06/25/2004-10:06:50:: i INFO: Call to
> > GetPermissions:/SampleReports
> > aspnet_wp!library!acc!06/25/2004-10:06:50:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!194!06/25/2004-10:06:54:: i INFO: Call to
> > GetPermissions:/SampleReports/Employee Sales Summary
> > aspnet_wp!library!194!06/25/2004-10:06:56:: i INFO: Call to
> > GetSystemPermissions
> > aspnet_wp!library!194!06/25/2004-10:07:20:: i INFO: Call to RenderFirst(
> > '/SampleReports/Employee Sales Summary' )
> > aspnet_wp!library!194!06/25/2004-10:07:38:: i INFO: Initializing
> > EnableExecutionLogging to 'True' as specified in Server system
> > properties.
> > aspnet_wp!webserver!194!06/25/2004-10:07:39:: i INFO: Processed report.
> > Report='/SampleReports/Employee Sales Summary', Stream=''
> > aspnet_wp!chunks!acc!06/25/2004-10:07:41:: i INFO: ###
> > GetReportChunk('C_6_S', 1), chunk was not found!
> > this=23b56e3b-a705-495b-ae16-8793896cf8d5
> > aspnet_wp!webserver!194!06/25/2004-10:07:42:: i INFO: Processed report.
> > Report='/SampleReports/Employee Sales Summary',
> > Stream='2ddd32ba-c0d3-4855-ab72-909781cf5f79'
> > aspnet_wp!webserver!acc!06/25/2004-10:07:42:: i INFO: Processed report.
> > Report='/SampleReports/Employee Sales Summary', Stream='C_6_S'
> > aspnet_wp!chunks!194!06/25/2004-10:07:42:: i INFO: ###
> > GetReportChunk('C_20_S', 1), chunk was not found!
> > this=23b56e3b-a705-495b-ae16-8793896cf8d5
> > aspnet_wp!webserver!194!06/25/2004-10:07:43:: i INFO: Processed report.
> > Report='/SampleReports/Employee Sales Summary', Stream='C_20_S'
> > aspnet_wp!library!194!06/25/2004-10:07:49:: i INFO: Call to RenderNext(
> > '/SampleReports/Employee Sales Summary' )
> > aspnet_wp!chunks!194!06/25/2004-10:07:50:: i INFO: ###
> > GetReportChunk('RenderingInfo_PDF', 2), chunk was not found!
> > this=23b56e3b-a705-495b-ae16-8793896cf8d5
> >
> > I hope this helps a lillte bit. Thank you very much for you effort.
> >
> > Have a nice day
> >
> > Frank
> >
> >
> > "Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> schrieb im
> > Newsbeitrag news:eiGHltgWEHA.2716@.tk2msftngp13.phx.gbl...
> >> Would you send me a zip of the most recent RS*.log files under
C:\Program
> > Files\Microsoft SQL
> >> Server?
> >>
> >> --
> >> Thanks.
> >>
> >> Donovan R. Smith
> >> Software Test Lead
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >> "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> >> news:u77G7cSWEHA.2168@.TK2MSFTNGP10.phx.gbl...
> >> > Hi Bruce!
> >> >
> >> > Thank you for your quick response. That is what I thought too. But of
> > course
> >> > this does not solve my initial problem: Reports with charts can not
be
> >> > renderd to PDF in my environment here. Today I have installed SP1 but
> > this
> >> > did not fix my problem. Reports with charts can still not be
rendered
> > to
> >> > PDF. What shall I do? Where can I start to search the error?
> >> >
> >> > Thank you and have a nice day
> >> >
> >> > Frank
> >> >
> >> >
> >> > "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> schrieb im
> >> > Newsbeitrag news:O4lGN5JWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> >> > > You are correct. You do not have to have PDF installed to export a
> > report
> >> > > using the PDF render.
> >> > >
> >> > > --
> >> > > Bruce Johnson [MSFT]
> >> > > Microsoft SQL Server Reporting Services
> >> > >
> >> > > This posting is provided "AS IS" with no warranties, and confers no
> >> > rights.
> >> > >
> >> > >
> >> > > "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> >> > > news:OahSp$DWEHA.2952@.TK2MSFTNGP09.phx.gbl...
> >> > > > Hi Brian!
> >> > > >
> >> > > > There is another idea that pops to my mind. I have two collegues
> >> > > > who
> > do
> >> > > not
> >> > > > have difficulties to render reports with charts in PDF. Both of
> >> > > > them
> >> > have
> >> > > > Adobe Acrobat 6 installed. Is this a prerequisite? If I
understand
> > the
> >> > > > architecture of RS right, this should not be a prerequisit. As I
> >> > > understand
> >> > > > it PDF rendering is done autnomously by the Report Server without
> > any
> >> > > > additional software. Or am I wrong?
> >> > > >
> >> > > > Have a nice day
> >> > > >
> >> > > > Frank
> >> > > >
> >> > > >
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>|||Hi Brian!
Another thought comes to my mind. How can I determine if the update
succeded?
Have a nice day
Frank|||When you go to the http://server/reportserver, you should see version
8.00.878.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Geisler" <frank_geisler@.geislers.net> wrote in message
news:eq62ROPXEHA.1036@.TK2MSFTNGP10.phx.gbl...
> Hi Brian!
> Another thought comes to my mind. How can I determine if the update
> succeded?
> Have a nice day
> Frank
>|||Hi Brian!
Thank you very much. I have found the page and there is indeed Version
8.00.878.00. So my Reporting Services Server is up to date. I wonder what is
wrong here with my stuff because I heared from different people that they
could perfectly render PDFs with Charts. Maybe I should start up from
scratch and install the whole Reporting Services Server (including OS and
SQL-Server) again. What do you think?
Have a nice day
Frank
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> schrieb im
Newsbeitrag news:ef0V7nZXEHA.3892@.TK2MSFTNGP09.phx.gbl...
> When you go to the http://server/reportserver, you should see version
> 8.00.878.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Frank Geisler" <frank_geisler@.geislers.net> wrote in message
> news:eq62ROPXEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > Hi Brian!
> >
> > Another thought comes to my mind. How can I determine if the update
> > succeded?
> >
> > Have a nice day
> >
> > Frank
> >
> >
>

Wednesday, March 21, 2012

Problem performing a join on a function in a SQL query

Hello,

Can someone explain why this code contains the following error:

Msg 4104, Level 16, State 1, Line 2

The multi-part identifier "TheTable.StartValue" could not be bound.

CREATE FUNCTION MyFunction(@.StartValue int)

RETURNS @.MyTable TABLE

(

NextValue int NOT NULL

)

BEGIN

INSERT INTO @.MyTable(NextValue)

VALUES (@.StartValue + 1)

INSERT INTO @.MyTable(NextValue)

VALUES (@.StartValue + 2)

RETURN

END

CREATE TABLE TheTable

(

StartValue int NOT NULL

)

INSERT INTO TheTable(StartValue)

VALUES (10)

INSERT INTO TheTable(StartValue)

VALUES (20)

SELECT *

FROM TheTable CROSS JOIN

MyFunction(TheTable.StartValue)

You can′t do that per row. The logic is quite simple that you presented here, what about doing

SELECT StartValue, StartValue+1,StartValue+2
From SomeTable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

In SQL Server 2000, this is not possible. However, in 2005, you can use the CROSS APPLY join operator:

SELECT *
FROM TheTable
CROSS APPLY MyFunction(TheTable.StartValue)

Interesting function. If you don't mind, could you share the purpose?

|||

Hi,

you cannot use Table's Column as a parameter to the function. Only variables or Static Literals can be passed as an argument to the function

|||

Thanks for your reply.

I wrote that function as an example of what I was trying to do. I have a vertical bar delimited column (eg. this|is|my|column). I used a CLR function to get all the values. I then run a aggregate of these values based on another field in another table. So the output should be something like this.

this: 2
is: 4
my: 0
column: 1

I can't do a straight aggregate because the "my" values above would be omitted.

|||

Thanks for your reply.

this: 2
is: 4
my: 0
column: 1

I can't do a straight aggregate because the "my" values above would be omitted.

Problem Passing Text/NText to sp_xml_preparedocument

I'm stuck, this (the code below) should work, but it doesn't. Why? Is there
another way to do this that I should use instead?
Declare @.Hand1 as int
Declare @.ptrval varbinary(16)
Declare @.Length integer
Select @.ptrval = TEXTPTR(DEV_409.Doc.DocTxt),
@.Length = DataLength(DEV_409.Doc.DocTxt)
from DEV_409.Doc where DEV_409.Doc.DocID = 11320
/* NOTE: dbo.tbl_Documents.DocTxt is a field of data type of TEXT containing
a well formed XML document. */
/* The following statement fails with the error message <<Incorrect syntax
near the keyword 'READTEXT'.>> */
Exec sp_xml_preparedocument @.Hand1 OUTPUT, READTEXT DEV_409.Doc.DocTxt
@.ptrval 0 @.LengthHow can I parse XML from a text column using sp_xml_preparedocument?
http://www.sqlxml.org/faqs.aspx?faq=42
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ed Lippert" <lippert@.mn.rr.com> wrote in message
news:OI7PEAsBEHA.3348@.TK2MSFTNGP11.phx.gbl...
> I'm stuck, this (the code below) should work, but it doesn't. Why? Is
there
> another way to do this that I should use instead?
> Declare @.Hand1 as int
> Declare @.ptrval varbinary(16)
> Declare @.Length integer
> Select @.ptrval = TEXTPTR(DEV_409.Doc.DocTxt),
> @.Length = DataLength(DEV_409.Doc.DocTxt)
> from DEV_409.Doc where DEV_409.Doc.DocID = 11320
> /* NOTE: dbo.tbl_Documents.DocTxt is a field of data type of TEXT
containing
> a well formed XML document. */
> /* The following statement fails with the error message <<Incorrect syntax
> near the keyword 'READTEXT'.>> */
> Exec sp_xml_preparedocument @.Hand1 OUTPUT, READTEXT DEV_409.Doc.DocTxt
> @.ptrval 0 @.Length
>|||The code you referenced is a poor kludge. While it may work in some cases, e
ven with obvious modifications it is not a universal solution (and won't wor
k in my specific appliocation) and is really unelegant.
There's got to be a better solution. MS states that the sp_xml_preparedocume
nt proc will accept text and ntext, so they must have had a way to accomplis
h it, if for no other reason than they had to test it.|||Hello Ed,
Thank you for posting in the community.
I understand you'd like to retrieve the Text/NText data with READTEXT
and pass the result as the second "xmltext" parameter of the
sp_xml_preparedocument.
BOL says:
[xmltext]
Is the original XML document. The MSXML parser parses this XML
document. xmltext is a text (char, nchar, varchar, nvarchar, text, or ntext
)
parameter. The default value is NULL, in which case an internal
representation of an empty XML document is created.
We can see that [xmltext] is a text parameter, which can be text or ntex
t data
type. However, it cannot be a batch statement "READTEXT DEV_
409.Doc.DocTxt @.ptrval 0 @.Length". Hence, SQL Server will issue a
Incorrect syntax error.
In my view, Jasper has lighted you on the right way. The solution to this
problem is to execute the statement dynamically. If the text length is short
er
than 8000 characters, you can use the following method directly.
----
--
CREATE PROC textproc
@.atext TEXT
AS
DECLARE @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.atext
EXEC sp_xml_removedocument @.hdoc
GO
-- Sample XML document
EXEC textproc '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-
04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-
16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
----
--
If it goes beyond 8000 characters, you need to kludge the string and
execute it for batch processing. Other than this, there is an open DCR to
enhance the functionality of the SP_XML_PREPAREDOCUMENT to allow
for the functionality that you are looking for.
Let us know if you have further questions. Thanks again for participating in
our community.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Problem passing parameter from VB.Net 2005 to crystal - code included

I am trying to pass a few parameters to crystal from vb.net 2005. Here is what I have, and it is not working. Everything else on the report works fine. Any ideas?

It is asking me to enter the value in a new window when the report loads..."You can provide a single value for this parameter. Enter the value you want to include........."

Dim strReportPath1 As String = "MainSummary"
Dim strConnection As String = "Data Source=localhost;Integrated Security=SSPI;" & _
"Initial Catalog=Carc2;"
Dim Connection As New SqlConnection(strConnection)
Dim strSQL As String = "My Select statement is here...too long to show in code"
Dim DA As New SqlDataAdapter(strSQL, Connection)
Dim DS As New DataSet

DA.Fill(DS, "SummaryInfo")

Dim strReportPath As String = Application.StartupPath & "\" & strReportPath1 & ".rpt"

If Not IO.File.Exists(strReportPath) Then
Throw (New Exception("Unable to locate report file:" & vbCrLf & strReportPath))
End If

If frmSummaryRpt.RadioShip.Checked = True Then
cr.SetParameterValue("ShipDate", frmSummaryRpt.txtStart.Text & " - " & frmSummaryRpt.txtEnd.Text)
ElseIf frmSummaryRpt.RadioShip.Checked = False Then
cr.SetParameterValue("ShipDate", "No ShipDate Selected")
End If

cr.Load(strReportPath)
cr.SetDataSource(DS.Tables("SummaryInfo"))This is resolved. The problem was that my if statements were before the report was loaded. I moved the area that they occured and it worked finesql

Problem Ordering XML using ADO.Net

I am converting some legacy ado code to ado.net. This function uses
'for xml explict' and returns the string representation of the xml. I
add a root node (<sales> ) around that string and load into an xml
document. It would return the following:
<sales>
<transaction>
<terms/>
<terms/>
</transaction>
<transaction>
<terms/>
<terms/>
</transaction>
</sales>
My new code looks like this, it uses the exact same SQL query, The
variable 'RootNode' is passed in and is set to 'sales':
Command = New SqlCommand
Command.CommandText = SQL & ", XMLDATA"
Command.CommandType = CommandType.Text
Command.Connection = Connection
Try
xrReader = Command.ExecuteXmlReader()
Try
Dim ds As DataSet = New DataSet
ds.ReadXml(xrReader, XmlReadMode.Fragment)
ds.DataSetName = RootNode.ToString
Dim DataDoc As XmlDataDocument = New
XmlDataDocument(ds)
strReturn = DataDoc.InnerXml
Catch ex As Exception
strReturn = "</" & RootNode & ">"
End Try
Catch ex As Exception
SaveEvent("CommonADO", "Application", ex.Message,
EventLogEntryType.Error, "EMK3Common.dll")
End Try
This function returns strReturn and looks like the following:
<sales>
<transaction/>
<transaction/>
<terms/>
<terms/>
<terms/>
<terms/>
</sales>
As you can see it does not return the same value. I'm hoping someone
can see what I've done wrong and suggest a fix that will return the
same thing as my old ado dll.
Thanks for your help.Can you post the SQL, i.e. whatever 'SQL' is in the
statement below
Command.CommandText = SQL & ", XMLDATA"
Chances are the ORDER BY clause is incorrect or
missing.|||The SQL is posted below. It returns the data in the correct format
when running it through query analyzer and has worked correctly for the
past two years. The problem seems to be in the conversion from dataset
to xml. Thanks for looking at this.
SELECT 1 AS Tag, NULL AS Parent, lc.TransactionID AS
[transaction!1!TransactionID], lc.ContractID AS
[transaction!1!ContractID],
lc.ContractBeginDate AS
[transaction!1!ContractBeginDate], lc.ContractEndDate AS
[transaction!1!ContractEndDate],
lc.TerminationDate AS
[transaction!1!TerminationDate], lc.LeaseID AS [transaction!1!LeaseID],
lc.NetEstimatedMCFsDay AS
[transaction!1!NetEstimatedMCFsDay], lc.BTUValue AS
[transaction!1!BTUValue],
lc.ServiceType AS [transaction!1!ServiceType],
lc.EstimatedVolumeSell AS [transaction!1!EstimatedVolumeSell],
lc.EstimatedVolumeSell2 AS
[transaction!1!EstimatedVolumeSell2], lc.AutomaticExtensionType AS
[transaction!1!AutomaticExtensionType],
lc.AutomaticExtensionInstr AS
[transaction!1!AutomaticExtensionInstr],
lc.ContractInstr AS
[transaction!1!ContractInstr], lc.EFP AS [transaction!1!EFP],
lc.ConfirmationNumber AS
[transaction!1!ConfirmationNumber], lc.AnalysisDate AS
[transaction!1!AnalysisDate],
lc.ConfirmationDate AS
[transaction!1!ConfirmationDate], lc.BuyerOperationsName AS
[transaction!1!BuyerOperationsName],
lc.BuyerOperationsPhone AS
[transaction!1!BuyerOperationsPhone], lc.BuyerOperationsFax AS
[transaction!1!BuyerOperationsFax],
lc.InvoiceVolumeBasedOn AS
[transaction!1!InvoiceVolumeBasedOn], c.ContractType AS
[transaction!1!ContractType],
l.LeaseType AS [transaction!1!LeaseType],
c.LocalBuyerID AS [transaction!1!LocalBuyerID], c.ProducerID AS
[transaction!1!ProducerID],
c.ContractNo AS [transaction!1!ContractNo],
lc.TransactionID AS [terms!2!TransactionID], NULL AS [terms!2!TermsID],
NULL AS [terms!2!BidValue], NULL
AS [terms!2!BidPercent], NULL AS
[terms!2!PriceBasis], NULL AS [terms!2!NYMEXTriggerRights], NULL AS
[terms!2!NYMEXTriggerParam], NULL
AS [terms!2!NYMEXTriggerDeadline], NULL AS
[terms!2!IndexType1], NULL AS [terms!2!IndexPosting1], NULL AS
[terms!2!TermsBeginDate], NULL
AS [terms!2!TermsEndDate], NULL AS
[terms!2!PercentofProceedsType], NULL AS [terms!2!PercentofProceeds],
NULL
AS [terms!2!NYMEXTriggerPrice], NULL AS
[terms!2!NYMEXTriggerOn], NULL AS [terms!2!NYMEXPriceBasis], NULL
AS [terms!2!AOCalculationType], NULL AS
[terms!2!AOPriceScheduleID1], NULL AS [terms!2!AOPriceScheduleID2],
NULL
AS [terms!2!AOPriceScheduleID3], NULL AS
[terms!2!VolumeCriteria], NULL AS [terms!2!VolumeMin], NULL AS
[terms!2!VolumeMax], NULL
AS [terms!2!VolumeUnit]
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
GROUP BY lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo
UNION
SELECT 2, 1, lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo,
lct.TransactionID, lct.TermsID, lct.BidValue,
lct.BidPercent, lct.PriceBasis,
lct.NYMEXTriggerRights, lct.NYMEXTriggerParam,
lct.NYMEXTriggerDeadline, lct.IndexType1, lct.IndexPosting1,
lct.TermsBeginDate, lct.TermsEndDate,
lct.PercentofProceedsType, lct.PercentofProceeds,
lct.NYMEXTriggerPrice, lct.NYMEXTriggerOn,
lct.NYMEXPriceBasis, lct.AOCalculationType,
lct.AOPriceScheduleID1, lct.AOPriceScheduleID2, lct.AOPriceScheduleID3,
lct.VolumeCriteria,
lct.VolumeMin, lct.VolumeMax, lct.VolumeUnit
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID) INNER JOIN
gmm_contracttransactionterms lct ON (lc.SellerID
= lct.SellerID AND lc.ContractID = lct.ContractID AND lc.TransactionID
= lct.TransactionID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
ORDER BY [transaction!1!LeaseID], [transaction!1!TransactionID],
[terms!2!TransactionID], [terms!2!TermsBeginDate]|||The SQL is posted below. It returns the data in the correct format
when running it through query analyzer and has worked correctly for the
past two years. The problem seems to be in the conversion from dataset
to xml. Thanks for looking at this.
SELECT 1 AS Tag, NULL AS Parent, lc.TransactionID AS
[transaction!1!TransactionID], lc.ContractID AS
[transaction!1!ContractID],
lc.ContractBeginDate AS
[transaction!1!ContractBeginDate], lc.ContractEndDate AS
[transaction!1!ContractEndDate],
lc.TerminationDate AS
[transaction!1!TerminationDate], lc.LeaseID AS [transaction!1!LeaseID],
lc.NetEstimatedMCFsDay AS
[transaction!1!NetEstimatedMCFsDay], lc.BTUValue AS
[transaction!1!BTUValue],
lc.ServiceType AS [transaction!1!ServiceType],
lc.EstimatedVolumeSell AS [transaction!1!EstimatedVolumeSell],
lc.EstimatedVolumeSell2 AS
[transaction!1!EstimatedVolumeSell2], lc.AutomaticExtensionType AS
[transaction!1!AutomaticExtensionType],
lc.AutomaticExtensionInstr AS
[transaction!1!AutomaticExtensionInstr],
lc.ContractInstr AS
[transaction!1!ContractInstr], lc.EFP AS [transaction!1!EFP],
lc.ConfirmationNumber AS
[transaction!1!ConfirmationNumber], lc.AnalysisDate AS
[transaction!1!AnalysisDate],
lc.ConfirmationDate AS
[transaction!1!ConfirmationDate], lc.BuyerOperationsName AS
[transaction!1!BuyerOperationsName],
lc.BuyerOperationsPhone AS
[transaction!1!BuyerOperationsPhone], lc.BuyerOperationsFax AS
[transaction!1!BuyerOperationsFax],
lc.InvoiceVolumeBasedOn AS
[transaction!1!InvoiceVolumeBasedOn], c.ContractType AS
[transaction!1!ContractType],
l.LeaseType AS [transaction!1!LeaseType],
c.LocalBuyerID AS [transaction!1!LocalBuyerID], c.ProducerID AS
[transaction!1!ProducerID],
c.ContractNo AS [transaction!1!ContractNo],
lc.TransactionID AS [terms!2!TransactionID], NULL AS [terms!2!TermsID],
NULL AS [terms!2!BidValue], NULL
AS [terms!2!BidPercent], NULL AS
[terms!2!PriceBasis], NULL AS [terms!2!NYMEXTriggerRights], NULL AS
[terms!2!NYMEXTriggerParam], NULL
AS [terms!2!NYMEXTriggerDeadline], NULL AS
[terms!2!IndexType1], NULL AS [terms!2!IndexPosting1], NULL AS
[terms!2!TermsBeginDate], NULL
AS [terms!2!TermsEndDate], NULL AS
[terms!2!PercentofProceedsType], NULL AS [terms!2!PercentofProceeds],
NULL
AS [terms!2!NYMEXTriggerPrice], NULL AS
[terms!2!NYMEXTriggerOn], NULL AS [terms!2!NYMEXPriceBasis], NULL
AS [terms!2!AOCalculationType], NULL AS
[terms!2!AOPriceScheduleID1], NULL AS [terms!2!AOPriceScheduleID2],
NULL
AS [terms!2!AOPriceScheduleID3], NULL AS
[terms!2!VolumeCriteria], NULL AS [terms!2!VolumeMin], NULL AS
[terms!2!VolumeMax], NULL
AS [terms!2!VolumeUnit]
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
GROUP BY lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo
UNION
SELECT 2, 1, lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo,
lct.TransactionID, lct.TermsID, lct.BidValue,
lct.BidPercent, lct.PriceBasis,
lct.NYMEXTriggerRights, lct.NYMEXTriggerParam,
lct.NYMEXTriggerDeadline, lct.IndexType1, lct.IndexPosting1,
lct.TermsBeginDate, lct.TermsEndDate,
lct.PercentofProceedsType, lct.PercentofProceeds,
lct.NYMEXTriggerPrice, lct.NYMEXTriggerOn,
lct.NYMEXPriceBasis, lct.AOCalculationType,
lct.AOPriceScheduleID1, lct.AOPriceScheduleID2, lct.AOPriceScheduleID3,
lct.VolumeCriteria,
lct.VolumeMin, lct.VolumeMax, lct.VolumeUnit
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID) INNER JOIN
gmm_contracttransactionterms lct ON (lc.SellerID
= lct.SellerID AND lc.ContractID = lct.ContractID AND lc.TransactionID
= lct.TransactionID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
ORDER BY [transaction!1!LeaseID], [transaction!1!TransactionID],
[terms!2!TransactionID], [terms!2!TermsBeginDate] FOR XML EXPLICIT|||Your SQL looks okay. Suggest you post to an ADO.NET newsgroup.|||I've had another look at this and think that
problem is that the relationship between transaction
and terms isn't coming through from the query. One
solution is to add it yourself by supplying a new
DataRelation object
After
ds.DataSetName = RootNode.ToString
add this (this is C#, but should translate to VB.NET easily)
DataColumn parentCol =
ds.Tables["transaction"].Columns["TransactionID"];
DataColumn childCol = ds.Tables["terms"].Columns["TransactionID"];
DataRelation TransactionTerms = new DataRelation("TransactionTerms",
parentCol, childCol);
ds.Relations.Add(TransactionTerms);
TransactionTerms.Nested = true;

Problem Oracle with \ and and

Hello for all, I'm a new user and my English isn't very good .. sorry!
I have a problem with an INSERT in Oracle, the code tha I inserd is:

INSERT INTO Schema VALUES(schema_seq.nextval,C:\Documents and Settings\Laura\Desktop\Tesi\File_OGaggi)

the error is:

Error: ORA-00911: invalid character

[Executed: 13/11/03 10.31.25 GMT ] [Execution: 0/ms]

I think that the problems are '\' and the word 'and'. Someone know what can I do? Thank you, by by,

Laura.It seems to me that You have quoted Your string with the wrong character ()

You must use ' instead.sql

Tuesday, March 20, 2012

problem on sending message

few weeks ago I found an example of conversation using service broker.
I used the following code:

USE Test GO -- First, we need to create a message type. Note that our message type is -- very simple and allowed any type of content CREATE MESSAGE TYPE HelloMessage VALIDATION = NONE GO -- Once the message type has been created, we need to create a contract -- that specifies who can send what types of messages CREATE CONTRACT HelloContract (HelloMessage SENT BY INITIATOR) select * from sys.service_contracts GO -- The communication is between two endpoints. Thus, we need two queues to -- hold messages CREATE QUEUE [SenderQueue] with status = on select * from sys.service_queues --CREATE QUEUE ReceiverQueue Create QUEUE [ReceiverQueue] with status = on select * from sys.service_queues GO -- Create the required services and bind them to be above created queues CREATE SERVICE [Sender] ON QUEUE [SenderQueue] (HelloContract) CREATE SERVICE [Receiver] ON QUEUE [ReceiverQueue] (HelloContract) GO

Then I try to send a message:

DECLARE @.conversationHandle UNIQUEIDENTIFIER DECLARE @.message NVARCHAR(100) BEGIN BEGIN DIALOG @.conversationHandle FROM SERVICE Sender TO SERVICE 'Receiver' ON CONTRACT HelloContract -- Send a message on the conversation SET @.message = N'Hello, World'; SEND ON CONVERSATION @.conversationHandle MESSAGE TYPE HelloMessage (@.message) END

Then I read the message in the ReceiverQueue

RECEIVE message_body FROM dbo.receiverqueue
I get no messages, can you help me to discover why?

A master key has probably not been setup for that database. See the documentation on CREATE MASTER KEY -- http://msdn2.microsoft.com/en-us/library/ms174382.aspx.

Also, when messages cannot be delivered immediately, they are placed in sys.transmission_queue.

-mike

|||

BEGIN DIALOG @.conversationHandle
FROM SERVICE Sender
TO SERVICE 'Receiver'
ON CONTRACT HelloContract
with encryption = off

|||Thanks for helping!

Problem on creating stored procedure

The code is as below:

--Drop procedures if they exsit
if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_PagedItems]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PagedItems]
GO

if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_PagedItemsByTime]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PagedItemsByTime]
GO

if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_selectedEventMessage]') AND OBJECTPROPERTY(id,N'IsProcedure')=1)
drop procedure [dbo].[sp_selectedEventMessage]
GO

--Definitions of procedures
USE LanDeskDB
GO

CREATE PROCEDURE sp_PagedItems
(
@.QueryVARCHAR(1000),
@.Pageint,
@.RecsPerPageint,
@.startDateVARCHAR(100),
@.endDateVARCHAR(100),
@.allTimeint,
@.flagint
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(2000)
DECLARE @.Order VARCHAR(200)
DECLARE @.TotalBIGINT

CREATE TABLE #TempTable
(
TempTableID BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
EventLogID BIGINT ,
EventDateTime datetime,
MachineID INT ,
TypeID INT ,
SessionID INT ,
SourceID INT ,
MessageID BIGINT ,
UserID INT,
CategoryNumber INT,
EventID INT
)
IF (@.flag = 1)
BEGIN
SET @.Order = 'ORDER BY EventDateTime'
END
IF (@.flag = 2)
BEGIN
SET @.Order = 'ORDER BY SessionID,EventDateTime'
END
IF (@.flag = 3)
BEGIN
SET @.Order = 'ORDER BY TypeID,EventDateTime'
END
IF (@.flag = 4)
BEGIN
SET @.Order = 'ORDER BY CategoryNumber,EventDateTime'
END
IF(@.allTime <> 1)
BEGIN
IF(LEN(@.Query)>1)
BEGIN
SET @.Query = @.Query+'AND EventDateTime>='''+@.startDate+''' AND EventDateTime <= '''+@.endDate+''''
END
ELSE
BEGIN
SET @.Query = 'WHERE EventDateTime>='''+@.startDate+''' AND EventDateTime <= '''+@.endDate+''''
END
END
SET @.SQL=
'INSERT INTO #TempTable (EventLogID,EventDateTime,MachineID,TypeID,SessionID,SourceID,MessageID,UserID,CategoryNumber,EventID)'+
'SELECT EventlogID,EventDateTime,MachineID,TypeID,SessionID,SourceID,MessageID,UserID,CategoryNumber,EventID FROM EventLog '+
@.Query+' '+@.Order
EXEC (@.SQL)

CREATE TABLE #TempTableTwo
(
TempTableTwoID BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
TempTableID BIGINT,
EventLogID BIGINT ,
EventDateTime datetime,
MachineID INT ,
TypeID INT ,
SessionID INT ,
SourceID INT ,
MessageID BIGINT ,
UserID INT,
CategoryNumber INT,
EventID INT,
)

DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = @.Page * @.RecsPerPage+1

INSERT #TempTableTwo

SELECT * FROM #TempTable T
WHERE T.TempTableID >@.FirstRec AND T.TempTableID < @.LastRec

SELECT TempT.EventLogID AS EventLogID,TempT.EventDateTime AS EventDateTime,
Ma.MachineName AS MachineName,Se.SessionName AS SessionName,Ty.TypeName AS TypeName,
TempT.CategoryNumber AS CategoryNumber,Us.UserName AS UserName,So.SourceName AS SourceName,
TempT.EventID AS EventID
FROM #TempTableTwo TempT,Machines Ma,Types Ty,Sessions Se,Sources So,Users Us
WHERE TempT.MachineID = Ma.MachineID AND TempT.TypeID = Ty.TypeID AND TempT.SessionID = Se.SessionID
AND TempT.SourceID = So.SourceID AND TempT.UserID = Us.UserID

SELECT COUNT(*) FROM #TempTable

DROP TABLE #TempTable
DROP TABLE #TempTableTwo
SET NOCOUNT OFF
END
GO

CREATE PROCEDURE sp_PagedItemsByTime
(
@.QueryVARCHAR(1000),
@.Page int,
@.RecsPerPage int,
@.startDateVARCHAR(100),
@.endDateVARCHAR(100),
@.allTime int
)
AS
BEGIN
EXEC sp_PagedItems @.Query,@.Page,@.RecsPerPage,@.startDate,@.endDate,@.allTime,1
END
GO

CREATE PROCEDURE sp_selectedEventMessage
(
@.EventLogID int
)
AS

BEGIN

SELECT Ma.MachineName,Ev.EventDateTime,Se.SessionName,Ty.TypeName,So.SourceName,Me.MessageDescription
FROM EventLog Ev,Sessions Se,Types Ty,Sources So,Messages Me,Machines Ma
WHERE Ev.EventLogID = @.EventLogID AND Ev.SessionID = Se.SessionID AND Ma.MachineID = Ev.MachineID
AND Ev.TypeID = Ty.TypeID AND Ev.SourceID = So.SourceID AND Ev.MessageID = Me.MessageID

END

I got the error messge as
Server: Msg 2714, Level 16, State 5, Procedure sp_PagedItems, Line 107
There is already an object named 'sp_PagedItems' in the database.
Server: Msg 2714, Level 16, State 5, Procedure sp_PagedItemsByTime, Line 13
There is already an object named 'sp_PagedItemsByTime' in the database.
Server: Msg 2714, Level 16, State 5, Procedure sp_selectedEventMessage, Line 12
There is already an object named 'sp_selectedEventMessage' in the database.

But I already delete those procedures before I create them. Could anyone give some suggestion?Change this master.dbo.sysobjects to LanDeskDB.dbo.sysobjects in your 3 EXISTS statements in the top of your code and it will work fine for you.

Terri|||Thank you very much. It works now.

Problem on collation on SQL Server Express with Windows Mobile 5.0

I am now writing application to connect SQL Server Express in Windows Mobile 5.0.

While running the code, I got error "PlatformNotSupportedException". I realized that it is a problem on different locale on the PDA and the SQL Server. So I tried to re-install the SQL Server for another collation, which is Latin1_General_CI_AI. I have also set the collation to Latin1_General_CI_AI at database-level.

Unfortunately, in the Visual Studio Debugger, I found that the error message is

mscorlib.dll!System.Globalization.CultureInfo.CultureInfo(int culture = 3076, bool useUserOverride = true) + 0xc8 bytes

where the 3076 means Chinese (Hong Kong SAR, PRC) locale from MSDN.

Seems to me that I cannot really change the collation in this Express Edition.

How can I solve it?

Thanks

Hi Billy,

You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer.

Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum.

Mike

|||

Thanks Mike!

"You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer."

-> Yes, I understand that I cannot change Server Setting for Collation after installation. So I DID remove the whole SQL Server Express, and then install it again. At that moment, I selected "SQL_Latin1_General_CP1_CI_AI", that's also what I can see from the SQL Server Management window. It is the same for the database collation setting, which was set to "SQL_Latin1_General_CP1_CI_AI"

So, the problem comes that the actual running in the debugger shows a different collation (Chinese Hong Kong) while it run sqlclient code in the PDA. => That is for sure a contradiction with the server setting. So, what's the problem on this case and how to solve it?

"Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum."

I am using SQL Express in a local PC. And I try to write app using VB on PDA which connects to the PC SQL server.

|||

Hi Billy,

What code are you running to show the collation? Please provide a sample. Do you see similar results if you take the PDA out of the scenario and run code directly on the machine where SQL is installed?

Mike

|||

Hi Mike,

Well, below is the code I run.

Dim sqlConnection1 AsNew SqlConnection("Data Source=BILLY\SQLEXPRESS;Initial Catalog=rfidcps;Persist Security Info=True;User ID=*****; Password=******;")

Dim cmd AsNew SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "SELECT * FROM Vehicle WHERE MainTagID = '" & "434" & "'"

Dim i AsInteger = 0

cmd.CommandType = CommandType.Text

cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()

And, the error comes out from executing "reader = cmd.ExecuteReader()", which shows something like this

System.PlatformNotSupportedException was unhandled
Message="PlatformNotSupportedException"
StackTrace:
at System.Globalization.CultureInfo..ctor()
at System.Globalization.CultureInfo..ctor()
at System.Data.SqlClient.TdsParser.GetCodePage()
at System.Data.SqlClient.TdsParser.ProcessEnvChange()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.SqlInternalConnection.Login()
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor()
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at DeviceApplication1.rfidcpsDataSetTableAdapters.VehicleTableAdapter.Fill()
at DeviceApplication1.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at DeviceApplication1.Form1.Main()

I asked similar question here and I found the collation requested by the server is actually not the one I set to the server

I tried to disconnect connection with the PDA and the server and it showed error in "sqlConnection1.Open()", which was absolutely the right things.

So, I drawed conclusion that the PDA can open the connection to SQL server but just failed to run "reader = cmd.ExecuteReader()"

Thanks for your help~

Best regards,

Billy

|||

I'm running this by a few folks I know to see if they have any ideas.

Mike

Problem on collation on SQL Server Express with Windows Mobile 5.0

I am now writing application to connect SQL Server Express in Windows Mobile 5.0.

Unfortunately, in the Visual Studio Debugger, I found that the error message is

mscorlib.dll!System.Globalization.CultureInfo.CultureInfo(int culture = 3076, bool useUserOverride = true) + 0xc8 bytes

where the 3076 means Chinese (Hong Kong SAR, PRC) locale from MSDN.

Seems to me that I cannot really change the collation in this Express Edition.

How can I solve it?

Thanks

Hi Billy,

Mike

|||

Thanks Mike!

I am using SQL Express in a local PC. And I try to write app using VB on PDA which connects to the PC SQL server.

|||

Hi Billy,

Mike

|||

Hi Mike,

Well, below is the code I run.

Dim sqlConnection1 As New SqlConnection("Data Source=BILLY\SQLEXPRESS;Initial Catalog=rfidcps;Persist Security Info=True;User ID=*****; Password=******;")

Dim cmd As New SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "SELECT * FROM Vehicle WHERE MainTagID = '" & "434" & "'"

Dim i As Integer = 0

cmd.CommandType = CommandType.Text

cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()

And, the error comes out from executing "reader = cmd.ExecuteReader()", which shows something like this

I asked similar question here and I found the collation requested by the server is actually not the one I set to the server

I tried to disconnect connection with the PDA and the server and it showed error in "sqlConnection1.Open()", which was absolutely the right things.

So, I drawed conclusion that the PDA can open the connection to SQL server but just failed to run "reader = cmd.ExecuteReader()"

Thanks for your help~

Best regards,

Billy

|||

I'm running this by a few folks I know to see if they have any ideas.

Mike

Monday, February 20, 2012

Problem inserting sql query into database float datatype field using SQL Transaction

I have this problem of inserting my query into database field. My code is as of below.

The @.AVERAGESCORE parameter is derived from

Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0)

and the value returned is (averagescore.toString("0.00"))

However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem?

Try Dim iAs Integer For i = 0To arraySql.Count - 1 myCommand =New SqlCommandDim consolidatedobjitemAs ConsolidatedObjItem = arraySql(i) myCommand.CommandText = sqlStr myCommand.Connection = myConnection myCommand.Transaction = myTransWith myCommand.Parameters .Add(New SqlParameter("@.AVERAGESCORE", consolidatedobjitem.getaveragescore))End With myCommand.ExecuteNonQuery()Next myTrans.Commit() myConnection.Close()Catch exAs Exception Console.Write(ex.Message) myTrans.Rollback() myConnection.Close()End Try

In your code I did not where you creat the transaction. I use the code below, it works fine. I guess you shoul put the code in to a try catch block. if you want to use it

Dim averageAs Decimal = 1.23Dim mycommandAs SqlCommand =New SqlCommand()Dim myconnAs SqlConnection =New SqlConnection(ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ConnectionString) mycommand.CommandText ="Insert into Table1 values(1, @.Average)" myconn.Open() mycommand.Connection = myconnDim transAs SqlTransaction = myconn.BeginTransaction("new transaction") mycommand.Transaction = trans mycommand.Parameters.Add(New SqlParameter("@.Average", average)) mycommand.ExecuteNonQuery() mycommand.Transaction.Commit() mycommand.Connection.Close()

Hope this help|||

Thanks for replying. My problem lies with the equation I got my averagescore from.

Dim averagescoreAs Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0)average = average.toString("0.00)average =Decimal.Round(average, 2)average = Convert.toSingle(average)average = Math.round(average, 2)

I've in vain to convert the average score to a value which has 2 decimal places using various methods.

The value that I've gotten is 1.68. But I believe the value that is being inserted into the database is not that as I have

various errors. This is because I have tried hardcoding the inserted value like you did and it works.

Is there any other way to resolve this problem?

|||

The error that I've got most of the times are,

System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 18 ("@.AVERAGESCORE"): The supplied value is not a valid instance of data type real. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

Hope this gives you a clearer idea, my guess is probably a rounding off error, but I just do not have any ideas left. I have even tried changing the database field datatype to decimal(10,2) to no effect.

|||

You could use SQL Server aggregate function Average with Decimal so you can set precision and scale. Try the link below for T-SQL Average aggregate function. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms173454.aspx

|||I afraid the AVG aggregate function woudn't help much in my case. Thanks anyway.|||

Can you print the type of averagescore. It might be become a decimal number after those manipulations, I do not think decimal can fit into float in sql server.

Hope this help

alienated:
The error that I've got most of the times are,
System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 18 ("@.AVERAGESCORE"): The supplied value is not a valid instance of data type real. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
Hope this gives you a clearer idea, my guess is probably a rounding off error, but I just do not have any ideas left. I have even tried changing the database field datatype to decimal(10,2) to no effect.

|||Your error message is saying you are passing the wrong data type to the data protocol Tabular Data Stream. You have to convert Float to Decimal before passing it to SQL Server because there is only implicit conversion between Decimal and Numeric and Float and Real, any data passing between both pairs must be explict conversion. The reason is you can set precision and scale with the former and not the later. Hope this helps.

Problem inserting integers and date in a sql server 2005 datatable row and selecting it af

Hi,

I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers).

I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr).

I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table.

I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds).

A where clause seems to be needed but I don't know the syntax or don't find the right function

I use the following code to insert the row :

command.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime, 40, "LDate"));

command.Parameters[6].Value = DateTime.Today.ToString();

command.ExecuteNonQuery();

and the following code to get the row back (to put in arraylist):

"SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())"

WHERE LDate = '" + DateTime.Today.ToString() + "'"

Which is the correct syntax? Is there a better way to insert and select based on the date?

I don't get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn't looped for rows I noticed while debugging).

Today's date is in the database but isn't found by my tsql code I think.

Any help would be greatly appreciated!

Grtz

Pascal

Instead of

command.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime, 40, "LDate"));

command.Parameters[6].Value = DateTime.Today.ToString();

command.ExecuteNonQuery();
use

command.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime));

However you could use GetDate() (server local time) or GetUtcDate() (server time in GMT)
The select could be

"SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE LDate DATEADD(day, -1, GetDate()) ANDBETWEEN GETDATE()"

will give you a 24hr windows of records.

|||

thx Tatworth for your quick reply

Tested it quickly but getting this sqlexception: "An expression of non-boolean type specified in a context where a condition is expected, near 'DATEADD' "

Don't have the time to investigate what the select code exactly does and how it works but will do tomorrow and keep you informed!

|||Should have been

"SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE LDateBETWEEN DATEADD(day, -1, GetDate()) ANDGETDATE()"

The between go in the wrong place!

|||

YES! It worked... THANKS

BUT: do not understand why this needs to be put like this and we can't simply use the GetDate() function

When I follow the logic of the functions and comparison one would think it will return the day of yesterday?

|||

>BUT: do not understand why this needs to be put like this and we can't simply use the GetDate() function

Let me run getDate on my Server
SELECT GetDate(), DATEADD(day, -1, GETDATE())

2007-05-05 09:59:11.280 2007-05-04 09:59:11.280

It returns not just the date but the time as well, thus the BETWEEN will select all records with date/time from 09:59 yesterday to 09:59 today.

Friday, March 30, 2012

Problem scheduling job

Problem returning OUTPUT in stored procedure

Monday, March 26, 2012

Problem rendering PDF with Diagram in code...

Wednesday, March 21, 2012

Problem performing a join on a function in a SQL query

Problem Passing Text/NText to sp_xml_preparedocument

Problem passing parameter from VB.Net 2005 to crystal - code included

Problem Ordering XML using ADO.Net

Problem Oracle with \ and and

Tuesday, March 20, 2012

problem on sending message

Problem on creating stored procedure

Problem on collation on SQL Server Express with Windows Mobile 5.0

Problem on collation on SQL Server Express with Windows Mobile 5.0

Monday, February 20, 2012

Problem inserting sql query into database float datatype field using SQL Transaction

Problem inserting integers and date in a sql server 2005 datatable row and selecting it af

xp cmdshell

Blog Archive

About Me