Cart data row

I have a SQL table with summary data. I want to display a pie chart for fields in each row and a table that displays values from the corresponding row.

I have a table and pie chart defined. However the pie chart only displays for the first row in the table. The table repeats ok for each row in the table. I have tried placing a rectangle on the report and putting the chart and the table in the rectangle but it still works the same. The table and pie chart use the same dataset.

I have read about nesting data regions but I cannot see how to make a link between the table and pie chart.

You need to add a grouping (on RowNumber() for example) to the chart in order to display each row in the dataset. The difference between chart and table is that table displays details when there's no group expressions, while chart also shows groups and aggregated data.

carriage return in a text field

We are in the process of updating a Text column.
How can we update the row and column and add a carriage return to the
existing text?A carriage return character is an ASCII 13 character. So if you wanted
foo
bar
then you would say
select 'foo' + char(13) + 'bar'
However, in the Windows world, carriage returns are normally paired with
line feed characters (Unix apps typically just have the CR with no LF).
A line feed character is ASCII 10. So a CR/LF pair would be
select 'foo' + char(13) + char(10) + 'bar'
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
wnfisba wrote:

>We are in the process of updating a Text column.
>How can we update the row and column and add a carriage return to the
>existing text?
>

Capturing results from SPROC

I need to execute a SPROC from another SPROC that returns 1 row with three fields. How do I capture the data out of those?

Have sproc #1 create a #Temp table,

then have sproc #2 populate that #Temp table.

The data will then be available in sproc #1.

|||If the stored procedure will always return 1 or less rows another good alternative is to have the stored procedure return three OUTPUT parameters instead of a row with three columns.|||

Here you can use either Insert Into ...Exec or OUTPUT param.

Code Snippet

--Option 1: If you are not autorized to change the Procedure code.

Create Proc #TestProc1

as

Select 1 Col1, 'Test data' Col2, Getdate() Col3

Go

Create Table #TempData (

Col1 int,

Col2 Varchar(100),

Col3 Datetime

)

Insert Into #TempData

Exec #TestProc1

Select * from #TempData

Code Snippet

--Option 2: Best Fit

Create Proc #TestProc2(

@.Col1 as Int OUTPUT,

@.Col2 as Varchar(100) OUTPUT,

@.Col3 as DateTime OUTPUT

)

as

Select

@.Col1 = 1

,@.Col2 = 'Test data'

,@.Col3 = Getdate()

Go

Declare @.Col1 as Int,

@.Col2 as Varchar(100) ,

@.Col3 as DateTime

Exec #TestProc2 @.Col1 OUTPUT, @.Col2 OUTPUT, @.Col3 OUTPUT

Select @.Col1, @.Col2, @.Col3

Capture System.IO rows from incoming file and insert into table

Ok, I'm not quite sure how to approach this one. This is a VB.NET console app in which I want to capture each row and throw it into a table. The reason being, they want a report on what was processed...which I'll be able to do easily in Reporting Services 2005 once this crap is in a table where it should be.

1) What should I use to do this, dataset? I want to use stored procedures also, not inline SQL

Function here takes an incoming file, and splits it up into separate files. I want to insert each row that is succesfully split

Public Sub ProcessFiles(ByVal sIncomingfile As String, ByVal sOutputDirectory As String)

If sIncomingfile <> "" And sOutputDirectory <> "" Then

Dim f As New Security.Permissions.FileIOPermission(Security.Permissions.PermissionState.None)
f.AllLocalFiles = Security.Permissions.FileIOPermissionAccess.Read

Dim file As New IO.FileInfo(sIncomingfile)
Dim filefs As IO.FileStream = Nothing
If file.Exists Then
Try
filefs = New IO.FileStream(file.FullName, IO.FileMode.Open) 'Place: 1
Catch ex As Exception
SendEmail("Incoming .mnt or .naf Filename Invalid or not found", "Place: 1")
Application.Exit()
End Try
End If

Dim reader As New IO.StreamReader(filefs)
Dim counter As Integer = 0

Dim CurrentFS As IO.FileStream
Dim CurrentWriter As IO.StreamWriter
Dim extension As String = IO.Path.GetExtension(file.FullName)

If extension = ".mnt" Then
While Not reader.Peek < 0
Dim Line As String = reader.ReadLine
If IsNumeric(Line.Substring(0, 1)) Then
Dim Parts() As String = Line.Split(" "c) ' split row into parts
If Parts(0).Length = 8 Then ' if first part is 8 then know we hit another header so cut and then write to file
counter += 1
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(59, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create)
CurrentWriter = New IO.StreamWriter(CurrentFS)
End If

If Not CurrentWriter Is Nothing Then
CurrentWriter.WriteLine(Line)
End If

End If
End While

If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()

MoveFilesFTP(sOutputDirectory, "mnt")

ElseIf extension = ".naf" Then
While Not reader.Peek < 0
Dim Line As String = reader.ReadLine
If Not IsNumeric(Line.Substring(0, 1)) Then ' if first part is not a number, then we know it's a header so split the file
counter += 1
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(6, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create)
CurrentWriter = New IO.StreamWriter(CurrentFS)
End If

If Not CurrentWriter Is Nothing Then
CurrentWriter.WriteLine(Line)
End If

End While

If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()

MoveFilesFTP(sOutputDirectory, "naf")
End If
Else
'input file not valid
SendEmail("Incoming .mnt or .naf Filename Invalid", "Place: 1")
End If
End Sub

You don't need a console application to import the data into SQL Server if you are in SQL Server 2000 you need a DTS package and in SQL Server 2005 you need an Integration services package. The only important thing to note is SQL Server being a RDBMS(relational database management systems) sees a text file as having Null values so you import your data into a Temp table then do INSERT INTO your destination table. Try the link below for sample DTS and Integration services code. Hope this helps.

http://www.sqlis.com/

|||

Put this somewhere at the top:

Dim conn as new sqlconnection("Your connect string")
conn.open
Dim cmd1 as new sqlcommand("INSERT INTO ProcessedFiles(Filename) VALUES (@.Filename) SELECT SCOPE_@.IDENTITY()")

cmd1.parameters.add("@.Filename",sqldbtype.varchar)

dim cmd2 as new sqlcommand("INSERT INTO ProcessedLines(FileID,LineNum,LineData) VALUES (@.FileID,@.LineNum,@.LineData)",conn)

cmd2.parameters.add("@.FileID",sqldbtype.int32)

cmd2.parameters.add("@.LineNum",sqldbtype.int32)

cmd2.parameters.add("@.LineData",sqldbtype.varchar)

Then after you get the filename in your code:

cmd1.parameters("@.Filename").value={Your filename variable}

cmd2.parameters("@.FileID").value = cmd1.executescaler

Then after you read a line of data from the file:

cmd2.parameters.add("@.LineNum").value=counter

cmd2.parameters.add("@.LineData").value=line

cmd2.executenonquery

And at the end of your program:

conn.close

of course, this assumes you have a table named processedfiles that has a Filename column, as well as an identity field. I would put a ProcessedDate field in there too, that defaults to GetUTCDate(). And a table named ProcessedLines that has three columns (FileID,LineNum,LineData).

Is that what you were looking for?

Cant Use Distinct here!

Hi All!!!
I'd Like to know how can I select one only row for each reference... I will post here the SQL clause
Note that I am working on Access

SELECT Badges.BadgeReference, Visitors.VisitorName, EventVisitors.VisitorCheckOutDate, EventVisitors.Event, EventVisitors.VisitorBadge, Visitors.VisitorState
FROM (Visitors INNER JOIN Badges ON Visitors.VisitorReference = Badges.BadgeReference) INNER JOIN EventVisitors ON (Visitors.Visitor_ID = EventVisitors.Visitor) AND (Badges.Badge_ID = EventVisitors.VisitorBadge)
WHERE (((Badges.BadgeReference) Is Not Null) AND ((EventVisitors.VisitorCheckOutDate) Is Not Null) AND ((Visitors.VisitorState)=0))
ORDER BY EventVisitors.VisitorCheckOutDate DESC;

It returns to me all the time the badge was out in each event... I want it to show the last event that each BadgeReference was retrieved...

Thanks in advance!SELECT Badges.BadgeReference
, Visitors.VisitorName
, EV.VisitorCheckOutDate
, EV.Event
, EV.VisitorBadge
, Visitors.VisitorState
FROM (
Visitors
INNER
JOIN Badges
ON Visitors.VisitorReference
= Badges.BadgeReference
)
INNER
JOIN EventVisitors as EV
ON Visitors.Visitor_ID = EV.Visitor
AND Badges.Badge_ID = EV.VisitorBadge
WHERE Badges.BadgeReference Is Not Null
AND EV.VisitorCheckOutDate
= ( select max(VisitorCheckOutDate)
from EventVisitors
where VisitorBadge = EV.VisitorBadge )
AND Visitors.VisitorState = 0
ORDER
BY EV.VisitorCheckOutDate DESC|||Thank you very very very very and a lots of many manys Much!!!!!

You have saved my day... You sure are pretty good at this ;)

See ya

Keep up that good work that you do :)

Cant use cursor with SP

Hi

I have a SP and in it I call another SP which returns one row in one column, I need to concatenate the value (varchar) to the query in the first SP.
I tried to use cursor with FAST_FORWARD to fetch the result and concatenate it, but I get an error, here is what I tried:

DECLARE Cur CURSOR FAST_FORWARD
FOR SP_Something @.SomeValue

So is it possible to use cursor on SP ? And if it's possible so how ?

Thanks,

Inon.I don't think so...why not put the result set into a table variable?|||I don't think so...why not put the result set into a table variable?

This also don't work, I get an error when I try to INSERT the result from the inner SP to a table var... so is THIS option possible ??

I just want to add that I have several ways to make this work, but I'm trying to be efficient, this is why I want to use an inner SP and not just make the process in the same big SP, I can also make changes in the code (PHP) but I'm trying to make it work this way.

Thanks,

Inon.|||doooh

Yup...can't do that...but you can do

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE PROC mySproc99
AS
SELECT OrderId FROM Orders
GO

CREATE TABLE #myTemp99(OrderId int)

INSERT INTO #myTemp99(OrderId) EXEC mySproc99

SELECT * FROM #myTemp99
GO

SET NOCOUNT ON
DROP TABLE #myTemp99
DROP PROC mySproc99
GO|||doooh

Yup...can't do that...but you can do

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE PROC mySproc99
AS
SELECT OrderId FROM Orders
GO

CREATE TABLE #myTemp99(OrderId int)

INSERT INTO #myTemp99(OrderId) EXEC mySproc99

SELECT * FROM #myTemp99
GO

SET NOCOUNT ON
DROP TABLE #myTemp99
DROP PROC mySproc99
GO

Hmmm... there goes the efficient part... :)

Inon.|||Well...

I don't think (I hate when that happens) that I would ever use effecient and cursor in the same sentence...

Unless it was like

"I wish the developer wrote effecient code instead of using a cursor"

:D|||Well...

I don't think (I hate when that happens) that I would ever use effecient and cursor in the same sentence...

Unless it was like

"I wish the developer wrote effecient code instead of using a cursor"

:D

I agree, didn't mean to express discontent of your solution BTW...

You see, the DB is for a web page, that SP will be called many times.

Thanks for the help,

Inon.|||I'm not sure what you're trying to do, but it looks like you want to use a SP to return a varchar that contains a query that you then want to execute - am I right ?

If so, try making the SP a function, viz:

create function dbo.some_func (@.inp_value char(?))
returns varchar(100)
as
begin
declare @.temp_var varchar(100)

set @.temp_var = 'select * from ' + @.inp_value
return @.temp_var

end

then in your calling sp:

select @.mysql = some_func(@.parm)
execute(@.mysql)

HTH|||I'm not sure what you're trying to do, but it looks like you want to use a SP to return a varchar that contains a query that you then want to execute - am I right ?

Almost right, the inner SP returns only a string that will be concatenate to the query in the outer SP, meaning, the inner SP returns only a part of a query, not entire query.

I have a SP that returns a result, I want to use this result in another SP, I didn't want to run both queries because (and correct me if I'm wrong) I know that for optimal performance it's not recommended to run two separated queries on two table in one SP, because the optimizer will confuse with the best execution plans for each query...

Anyway, this is what I finally did, ran both in one SP... is it as bad as I think ?? And should I just make some process in the code and just run two separated SPs? I wanted to make it in one connection session since it will run many many times and by using one SP I cut traffic (for this option only of course) in 50%...

So what do you say is better? One SP with two queries or two SP's with two connections? (But each has its own optimal execution plan).

Thanks,

Inon.