housekeeping Blobs. [modified] - Database Discussion Boards

Re: Creation of table

SHatchard9-Jul-07 2:02

SHatchard

9-Jul-07 2:02

I am not sure exactly what you mean, could you please clarify your exact requirement?

Re: Creation of table

sam#9-Jul-07 2:05

sam#

9-Jul-07 2:05

SELECT * INTO NewTable FROM OldTable

Re: Creation of table

Krish - KP9-Jul-07 2:11

Krish - KP

9-Jul-07 2:11

SELECT * INTO <newtbl from="" <oldtbl="">
This creates a copy of <oldtbl> to <newtbl>

INSERT INTO <tablea> (<col1>, <col2>) SELECT <col1>, <col2> FROM <tableb>
Inserts rows fetched by select statement into <tablea>

Regards
KP

Backup or restore

jaics2629-Jul-07 0:46

jaics262

9-Jul-07 0:46

How to write script to take backup or restore database(when i run a prticular file it automatically backup or restore database)

Re: Backup or restore

Paul Conrad13-Jul-07 11:54

Paul Conrad

13-Jul-07 11:54

http://www.google.com[^] around for it.

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Problem inserting data in SQL Server 2000

Mubashir Javaid8-Jul-07 22:12

Mubashir Javaid

8-Jul-07 22:12

Hi,
I have faced a problem while inserting data into the sql server 2000.
First I have made a database and related Store procedure in SQL Server 2005.
When I run a store procedure in SQL Server 2005, it works fine.
But I have generated SQL script from SQL Server 2005 and run in Server 2000 (after some changes in script).
Tables and store procedure are created in SQL Server 2000. Then I made a database user and allow it all permissions (Set this user as dbo).
When I insert data into the sql server 2000 table it runs the store procedure but no insertion will take place

Re: Problem inserting data in SQL Server 2000

Rajesh Mascon8-Jul-07 23:22

Rajesh Mascon

8-Jul-07 23:22

May I see the full script with storred procedure which you have ran on sql server 2000 db

housekeeping Blobs. [modified]

gloffa8-Jul-07 21:39

gloffa

8-Jul-07 21:39

Hi,

MSSQL 2005, I want to delete blobs on a regular basisto preserve diskspace.
The blobs has various meta-data but I want to delete them based on time of
creation (date-time) and type. My concern is to equally disperse the deletion
so no type ends up being empty, but rather delete a few of this type and some
in that type ... and so on.

Any kind of help regarding this would be appriciated.

This CTE takes 100 from every Category

WITH tempdata AS (SELECT ROW_NUMBER() OVER (PARTITION BY Category<br />
ORDER BY Age DESC) AS rownum, ID FROM Imageblob)<br />
DELETE FROM tempdata WHERE rownum > 1000;

But how do i use it with a grouping selection so I can choose
to delete from the category with the most rows.

Select count(Age)<br />
From imageblobs<br />
Group by Catebogry

that gives me each row per category but I want to use it with the first CTE
and also order them.

Any suggestions/code/help/hints are much appritiated´
My t-sql knowledge is rubbish

Cheers,

/Andreas

-- modified at 12:27 Monday 9th July, 2007

SQL Server 2000 connection problem

Deteroc8-Jul-07 21:31

Deteroc

8-Jul-07 21:31

Hi, all!
I have the following problem.
I have an application in vb 2005 and a database in SQL server 2000. The instance of SQL server is installed on one machine in the network, however the application should work on several machines within this network.
The question is do I need to have separate instances of Sql server on each of the machines with the same database attached, or I need to connect to a single SQL server, in that case what will be the connection string? The name of the computer in the network and name of the server, or only the name of the server?
Could you please suggest me the sample connection string, I should use in my case.

Thanks beforehand,
D.

Re: SQL Server 2000 connection problem

Nouman Bhatti8-Jul-07 21:57

Nouman Bhatti

8-Jul-07 21:57

You don't need to install sql server 2000 instance on each and every system.
just install it on one system in the lan or workgroup
and in your query string give the name of that system

for example i had installed the sql on "test" system so the query string will be

"Server=test;Database=your_db_name;uid=sa;"

if u have a password for sa account then also include it.

display stored procedure format...........,

Member 38798818-Jul-07 21:16

Member 3879881

8-Jul-07 21:16

Hi i am using one procedure to get a month all dates...,
But each and every dates creating separate,separate column name...,
But i want display those all dates under come in same column name...,
Means:
when i execute my procedure i am getting o/p like:

Startdate
2007-06-01

Startdate
2007-06-02

startdate
2007-06-03
.
.
.
startdate
2007-06-30

But i want following format:

Startdate
2007-06-01
2007-06-02
.
.
.
2007-06-30

This is the procedure:

DECLARE @startDate datetime
declare @nextdate datetime
DECLARE @endDate datetime

SET @startDate = CAST( CAST( 2007 AS varchar(4) ) + '0101' AS datetime )

SET @startDate = DATEADD( m, 6 - 1, @startDate )

set @enddate=dateadd(m,1,@startdate)
set @enddate=dateadd(d,-1,@enddate)

select @startdate as startdate

while @startdate < @enddate
begin

SET @nextdate = DATEADD( d, 0, @startdate )
set @startdate=dateadd(d,1,@nextdate)
select @startdate as startdate

end

Advance Thanks,

Regards,
Magi

Re: display stored procedure format...........,

Pete O'Hanlon8-Jul-07 23:06

Pete O'Hanlon

8-Jul-07 23:06

Please - don't repost variants of the same question. You've been given all the information you need to have a go at answering this yourself. See my answer from earlier today as a basis.

Please visit http://www.readytogiveup.com/ and do something special today.
Deja View - the feeling that you've seen this post before.

Re: display stored procedure format...........,

Member 38798819-Jul-07 0:05

Member 3879881

9-Jul-07 0:05

Ya i didnt see that...,
Anyway thanks for ur post...,
Keep in touch

Regards,
Magi

Re: display stored procedure format...........,

Pete O'Hanlon9-Jul-07 0:28

Pete O'Hanlon

9-Jul-07 0:28

No problems.

Please visit http://www.readytogiveup.com/ and do something special today.
Deja View - the feeling that you've seen this post before.

Exporting and Importing

M. J. Jaya Chitra8-Jul-07 21:04

M. J. Jaya Chitra

8-Jul-07 21:04

How can i export or import tables and objects from one database to another in Microsoft SQL Server Management Studio Express 2005.

Best Regards,
M. J. Jaya Chitra

Re: Exporting and Importing

Harini N K9-Jul-07 19:21

Harini N K

9-Jul-07 19:21

Hi
Right click on the database ... click Tasks -> Emport / Import Data.

For more information, check this link

Exporting and Importing[^]

Harini

Problem Insert data into MySQL using ASP.NET

drexler_kk8-Jul-07 17:24

drexler_kk

8-Jul-07 17:24

I am a beginner here for ASP.NET. I can't manage to insert data using ASP.NET. I have successful using the same connection string to do SELECT statement,but I'm wondering how to do INSERT statement to MySQL database. Please give me some solution here.

This is my code:

Imports System.Data.Odbc

Dim strConnString As String = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;option=3;Database=MyDatabase;Uid=root;Pwd=Pass;"
Dim ObjConn As New OdbcConnection(strConnString)
Dim ObjRead As OdbcDataReader
Dim ObjComm As OdbcCommand

Try

ObjComm = New OdbcCommand("INSERT INTO member_1(Member_ID,Username,Gender,Email,Contact_No,Address,Password)VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "','" & TextBox7.Text & "'", ObjConn)

If (TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or TextBox4.Text = "" Or TextBox5.Text = "" Or TextBox6.Text = "" Or TextBox7.Text = "") Then

Else
ObjConn.Open()
ObjComm.ExecuteNonQuery()
ObjConn.Close()
End If

Catch ex As Exception
Console.WriteLine(e.ToString())
End Try

Anyone can give me some solution here?

Thank you for readings.

Regards
Drex

Re: Problem Insert data into MySQL using ASP.NET

N a v a n e e t h8-Jul-07 21:33

N a v a n e e t h

8-Jul-07 21:33

Print your query on page. Copy that and paste in mysql query analyzer. That will give you the error

My Website | Ask smart questions

Re: Problem Insert data into MySQL using ASP.NET

drexler_kk8-Jul-07 22:21

drexler_kk

8-Jul-07 22:21

Thank you so much. I found my problem now. Its the Query Error. Thanks for your solution.

Regards
Drex

Binding a datagridview to a binding source

steve_rm8-Jul-07 12:13

steve_rm

8-Jul-07 12:13

Hello,

I am using a datagridview and have bound it to a bindingsource.

In the datagridview i have a combo box column which gives the user a choice of either, ordered, pending, delivered.

However, In this specific grid I just want to display the pending orders. But if I click on the status and select delivered, I want the grid to display "delivered", Until I click save. However, what is happening then I click on the combo box and select delivered it will automatically refresh and not display delivered, only the pending orders. The customer would like to see all the orders that have had their status changed before saving and then displaying all the pending orders again.

The binding source is filtered to display only the orders pending when I select an order from the combo box.

My idea is to select an order from the combox box and display all the pending orders for that order number. The user can then select the status to delivered, if they want to. Then click save to save the changes. The save works fine. But the delivered row will disappear when the combo box loses focus.

 Private Sub cboOrdersPending_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboOrdersPending.SelectionChangeCommitted<br />
        Try<br />
            Me.bsOrderDetails.Filter = String.Format("OrderID = '{0}' AND Status = '{1}'", Me.cboOrdersPending.SelectedValue, "Pending")<br />
            Me.dgvPendingOrders.DataSource = Me.bsPendingOrders<br />
<br />
        Catch ex As Exception<br />
            MsgBox(ex.Message)<br />
        End Try<br />
    End Sub

The code for filling the binding source is as follows:

'Fill all pending orders - these are orders that have been ordered but have not bee delivered<br />
    Private Sub FillPendingOrders()<br />
        Try<br />
            Me.TA_OrderDetails_dsCodeRed1.Fill(Me.DsAddComponetAndEquipment.OrderDetails)<br />
            Me.bsPendingOrders.Filter = String.Format("Status = '{0}'", "Pending")<br />
<br />
            Me.dgvPendingOrders.AutoGenerateColumns = False<br />
            Me.dgvPendingOrders.DataSource = Me.bsPendingOrders<br />
<br />
            'Me.HidePendingOrdersColumn()<br />
        Catch ex As Exception<br />
            MsgBox(ex.Message)<br />
        End Try<br />
    End Sub

Can someone please tell me if i have done something wrong with my code and explanation.

Many thanks for any help,

Steve

Select by date

Sam Heller8-Jul-07 5:39

Sam Heller

8-Jul-07 5:39

Hi,

I am trying to get results where the parameter that is passed is a date. I am only passing the date itself and not the time although the field in the datebase holds both. How can I retreve date specific results and ignore the time.

I currently have

	<br />
WHERE E.Start = @Date

But it seems to be passing @Date with the time 00:00:00 at the end which obviously makes the wrong, if any, results comeback.

Any ideas appreciated. Thanks

Re: Select by date

Mike Dimmick8-Jul-07 11:49

Mike Dimmick

8-Jul-07 11:49

SQL Server does not have a data type which only holds date information, only ones which hold date and time information.

In practice if you want any time on a specific day you have to say:

WHERE E.Start >= @Date AND E.Start < DATEADD( d, 1, @Date )

Stability. What an interesting concept. -- Chris Maunder

Re: Select by date

Sam Heller8-Jul-07 11:51

Sam Heller

8-Jul-07 11:51

Yeah I was stupid enough to post without looking three post down where the answer was already written. I came up with something pretty much like you suggested

<br />
DECLARE @FromDate datetime<br />
DECLARE @ToDate datetime<br />
<br />
SET @FromDate = (CAST(@Date AS datetime ))<br />
SET @ToDate = DATEADD( D, 1, (DATEADD(mi, -1, @FromDate )) )<br />
<br />
	SELECT	E.ID As EventID,<br />
			ET.ID As TypeID,<br />
			E.Title As EventTitle,<br />
			V.ID As VenueID,<br />
			V.[Name] As VenueName,<br />
			E.Description As EventDescription,<br />
			E.Start As EventStart,<br />
			E.Finish As EventFinish<br />
	FROM Events E<br />
	INNER JOIN Venues V<br />
	ON E.VenueID = V.ID<br />
	INNER JOIN EventTypes ET<br />
	ON E.TypeID = ET.ID<br />
	WHERE E.Start >= @FromDate<br />
	AND E.Start <= @ToDate<br />
	AND V.CityID = @CityID

Thanks anyways

Re: Select by date

Thomas Chester9-Jul-07 7:53

Thomas Chester

9-Jul-07 7:53

You have two options of rephrasing your WHERE clause. Each solution assumes that both @Date and E.Start are declard as DATETIME:

(1) WHERE E.Start BETWEEN @Date AND DATEADD(d, 1, @Date)

This will look for E.Start values between midnight on @Date and midnight on @Date + 1 day

(2)

WHERE DAY(E.Start)   = DAY(@Date) <br />
            AND MONTH(E.Start) = MONTH(@Date) <br />
            AND YEAR(E.Start)  = YEAR(@Date)

This will explicitly compare the month, day, and year components of each field and only flag those where all three are the same.

Use ' one quotation make error with sql statmetn ..

kindman_nb8-Jul-07 3:47

kindman_nb

8-Jul-07 3:47

hi ...
see the next Sql statment
" Select * from TableN where Dept='Human Resources' "
this statment has no error but if I use one quotation for eg , Dept='Human's Resources' .. it's make error

jooooo

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