Re: TOP 1 in select statement with different results. - Database Discussion Boards

dario_dee19-Jun-07 7:17

19-Jun-07 7:17

Using vb in an ActiveX part of a dts package I have created an excel sheet but now I need to add borders, lines, colors, etc.

Does anyone know how to do this?

All I could find is how to do a grid and a couple other things.

Set cell = oSheet.Range("A1:A6")
cell.Font.Bold = True
cell.ColumnWidth = 17.43
cell.borders.LineStyle = 10

Any help would be great.

Thanks

TOP 1 in select statement with different results.

VK-Cadec19-Jun-07 5:03

VK-Cadec

19-Jun-07 5:03

Hi,

Are there any issues using TOP clause in temp tables in stored procedure?

We are experiencing some difficulties for which we are unable to debug.

We are receiving two sets of different results, for same input parameters. When we run stored proc the first time versus when it is run 5 minutes later, the results are not consistent.

We are assuming using the TOP 1 clause may have some issues.

Thanks for your time.

Re: TOP 1 in select statement with different results.

andyharman19-Jun-07 6:36

andyharman

19-Jun-07 6:36

Do you have an "order by" clause on your select statement?

Re: TOP 1 in select statement with different results.

VK-Cadec19-Jun-07 6:53

VK-Cadec

19-Jun-07 6:53

Yes.

Re: TOP 1 in select statement with different results.

andyharman19-Jun-07 7:04

andyharman

19-Jun-07 7:04

Can you post the code for the stored procedure to this forum?

There are no issues with doing a "top 1" on temporary tables - apart from the fact the SQL-Server may possibly use a different query-plan each time you run it. However, your "order by" clause should have fixed that.

Is it possible that your temporary table somehow contains different data for each of the runs? I have had this caused by an update or delete statement using different query plans for different runs.

Re: TOP 1 in select statement with different results.

VK-Cadec19-Jun-07 7:17

VK-Cadec

19-Jun-07 7:17

Here you go.
--------------
select top 1 @lastdrive = t1.datetimestamp
from #e t1
inner join #r r1 on r1.routestart <= t1.datetimestamp and
r1.routeend >= t1.datetimestamp
where t1.eventnum = 1108 and
t1.datetimestamp >= @stopstart and t1.datetimestamp <= @stopend and
r1.routedatekey = @routedatekey
and t1.driverkey = r1.driverkey
and r1.driverkey = @driverkey
order by t1.datetimestamp desc
--------------

This is run within a cursor.

Also - does it make a difference if we used something like -- select @lastdrive = max (t1.datetimestamp) -- instead?

Thanks.
Vani

Re: TOP 1 in select statement with different results.

andyharman19-Jun-07 7:30

andyharman

19-Jun-07 7:30

I would use the @lastdrive = max(t1.datetimestamp) method. It may allow you to remove the cursor from your code.

Re: TOP 1 in select statement with different results.

Mike Dimmick20-Jun-07 1:28

Mike Dimmick

20-Jun-07 1:28

You need to use ## for a global temporary table. A temporary table whose name begins with a single # is a local temporary table and is local to the connection.

SQL Server 2005 documentation says:

"There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server."

Stability. What an interesting concept. -- Chris Maunder

mysql update

WhiteGirl2319-Jun-07 3:20

WhiteGirl23

19-Jun-07 3:20

Hi good people.

I'm trying to update a table in mysql.
Here is the code:

For Each file12 In files<br />
<br />
                array12 = System.IO.Path.GetFileNameWithoutExtension(file12).Trim<br />
<br />
<br />
                Dim FileSize As UInt32<br />
                Dim rawData() As Byte<br />
                Dim fs As FileStream<br />
                fs = New FileStream(cale12 & "\" & array12 & ".buf", FileMode.Open, FileAccess.Read)<br />
                FileSize = fs.Length<br />
<br />
                rawData = New Byte(FileSize) {}<br />
                fs.Read(rawData, 0, FileSize)<br />
                fs.Close()<br />
<br />
                par.ParameterName = "?BinData"<br />
<br />
                cmd.CommandText = "update notiz set Bindata=" & par.ParameterName & " where Nr=" & Chr(39) & array12 & Chr(39)<br />
<br />
                par.Value = rawData<br />
<br />
                cmd.Parameters.Add(par)<br />
                cmd.ExecuteNonQuery()

It works for the first field but when I try to update the second field I have the error:

Parameter '?BinData' has already been defined.

Please help.

Thanks

Re: mysql update

GoodID19-Jun-07 19:52

GoodID

19-Jun-07 19:52

Hi,

I think the problem is because you create parameter name on every cycle.
Why don't you try to create parameter name only once at first cycle

Re: mysql update

WhiteGirl2319-Jun-07 21:02

WhiteGirl23

19-Jun-07 21:02

Hi,
Thanks for the idea.
It works.

Regards.

Determining Size of Records In SQL Server Table

tjg5_9919-Jun-07 3:07

tjg5_99

19-Jun-07 3:07

Is there a utility or simple way to determine the size (number of bytes) of records in a table?

I'm trying to determine what I would save in disk space if certain records were removed/archived out of a SQL Server 2000 DB.

I understand that record size depends on contents (VARCHAR(30) vs CHAR(30)).

Thanks.

Re: Determining Size of Records In SQL Server Table

andyharman19-Jun-07 6:56

andyharman

19-Jun-07 6:56

How about running:

exec sp_spaceused 'MyTableName'

That gives you the number of rows, and the amount of space taken-up by the data and indexes.

Re: Determining Size of Records In SQL Server Table

tjg5_9919-Jun-07 8:05

tjg5_99

19-Jun-07 8:05

That's exactly what I needed, Andy. Thank you.

Help with report creation

Seryoga19-Jun-07 0:40

Seryoga

19-Jun-07 0:40

There is a code which displays results of SQL-query in dataGridView:

dataGridView1.DataSource = bindingSource1;<br />
dataAdapter = new OleDbDataAdapter("SQL-query", connectionString);<br />
OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);<br />
DataTable table = new DataTable();<br />
dataAdapter.Fill(table);<br />
bindingSource1.DataSource = table;

Whether probably in a similar way to display results of SQL in the table in reportViewe?

Thanks.

Returning Value from Stored Procedures

M. J. Jaya Chitra18-Jun-07 23:22

M. J. Jaya Chitra

18-Jun-07 23:22

I planned to update a set of values in a stored procedure and what the procedure will return if there is an error and how can i get it from C#

Best Regards,
M. J. Jaya Chitra

Re: Returning Value from Stored Procedures

vimal_yet18-Jun-07 23:46

vimal_yet

18-Jun-07 23:46

If you are using c# dot net catch a "SqlException" . In SQL procedure you can use RaisError function.

Regards,
Vimal

If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt

Re: Returning Value from Stored Procedures

Xandip19-Jun-07 19:19

Xandip

19-Jun-07 19:19

if <error_occurs> then
begin
RaisError('error....',16,1)
end Smile | :)

The name is Sandeep

Re: Returning Value from Stored Procedures

M. J. Jaya Chitra19-Jun-07 19:29

M. J. Jaya Chitra

19-Jun-07 19:29

Thank you

Best Regards,
M. J. Jaya Chitra

How to find nth record in a table using sqlserver.

Sophia Rekhi18-Jun-07 21:31

Sophia Rekhi

18-Jun-07 21:31

Hi all,

i want to know How to find exact record in my table.

ex : in a table having 10 records.
i want 5 th record. with out knowing details containg in a table how can i find it. only know table name.

pls help me.

Re: How to find nth record in a table using sqlserver.

iprasad00718-Jun-07 22:12

iprasad007

18-Jun-07 22:12

Here u get the 5th record

SELECT TOP 1 FName
FROM
(
SELECT TOP 5 FName
FROM Names
ORDER BY FName Desc
)

Re: How to find nth record in a table using sqlserver.

Sophia Rekhi18-Jun-07 23:39

Sophia Rekhi

18-Jun-07 23:39

hi Prasad,

Thanks for u r valuable solution, its working by making some modifications.
this is working properly

SELECT TOP 1 sal
FROM prs where sal in (SELECT TOP 4 sal
FROM prs
ORDER BY sal )order by sal desc

all the best

Re: How to find nth record in a table using sqlserver.

Krish - KP18-Jun-07 22:42

Krish - KP

18-Jun-07 22:42

Are you trying to get nth Max or Min value
or trying to fetch specific row by number.

Regards
KP

Setting default value to a column

M. J. Jaya Chitra18-Jun-07 20:01

M. J. Jaya Chitra

18-Jun-07 20:01

How can i set the default value to the column while designing the table in SQL Express 2005 GUI

Best Regards,
M. J. Jaya Chitra

Re: Setting default value to a column

Sylvester george18-Jun-07 20:10

Sylvester george

18-Jun-07 20:10

[Created] [datetime] NOT NULL CONSTRAINT [DF_PurchaserTransactionMaster_Created] DEFAULT (getdate())

[Total] [decimal](12, 2) NOT NULL CONSTRAINT [DF_PurchaserTransactionMaster_Total] DEFAULT (0)

Regards,
Sylvester G
sylvester_g_m@yahoo.com

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.